cancel
Showing results for 
Search instead for 
Did you mean: 

[ASK] BP - Manage Prev Recon

Former Member
0 Kudos
62

Dear All,

Need help here

i already search everywhere in google, but i couldn't find the answer

where can i find the table for that store Origin No in Recon Detail?

i look in OITR & ITR1 but i coundn't find the field

Thx,

Daniel

Accepted Solutions (1)

Accepted Solutions (1)

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

The Origin No. field in the JDT1 table is the baseref field.

If you want the baseref field value in the reconciliation table, please check:

SrcObjAbs field in the ITR1 table.

Hope it will help.

Kind Regards,

Jitin

SAP Business One Forum Team

Former Member
0 Kudos

thx..

but i still cant find connection from my invoice & payment / Credit Memo..

can you help with query for A/P Invoice, Right Click, Applied Transaction?

zal_parchem2
Active Contributor
0 Kudos

Welcome to the forum Ravendma - I see you are new...

You seem to be a bit scattered here in what your request is and maybe a suggestion...

What exact fields are you looking for in your SQL results? Just list them out and you will probably get a few more replies than what you currently have...the more details, reasons for your request, and examples of what you are looking for will get you more help...

Are you looking for something along these lines for the AP side???

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLG-BKDepositwithARInvoicesfrom+Lockbox

Regards - Zal

Former Member
0 Kudos

thx for the warm welcome

sorry if my question is confusing you.

i want to make Open AP Report, so i need to get the Field for DocNum for the Outgoing Payment & AP Credit Memo. When i join table OPCH, ITR1 & JDT1 i couldnt get DocNum for Outgoing Payment & AP Credit Memo, it keeps coming back to AP Invoice Doc NUM

Edited by: ravendma on Apr 21, 2011 11:46 AM

zal_parchem2
Active Contributor
0 Kudos

OK Ravendma - that gives us a bit more to go on - here you go...try this...it shows the detail from the JDT1 table and it balances to the penny when I run my AP Aging Report. You might need to add some more fields for the types of transactioins your company does, but these are the only transactions my Customer uses and are the transactions you specifically requested...

Regards - Zal

--E-PU Accts Payable Register Ver 1 ZP 2011 04 21

--DESCRIPTION: SQL lists Accts Payable Open Items from General Ledger. Results should equal the AP Aging Report.

--AUTHOR(s):

--Version 1 Zal Parchem 21 April 2011

SELECT

T1.CardCode AS 'Vend Code',

T1.CardName AS 'Vend Name',

T0.TransType AS 'Trans Type',

CASE

WHEN T0.TransType = 18 THEN 'AP Invoice'

WHEN T0.TransType = 19 THEN 'AP Credit Memo'

WHEN T0.TransType = 46 THEN 'Outgoing Payment'

ELSE 'Error'

END AS 'Transaction',

--T2.BaseRef AS 'Doc Numb',

CASE

WHEN T0.TransType = 18 THEN T3.DocNum

WHEN T0.TransType = 19 THEN T4.DocNum

WHEN T0.TransType = 46 THEN T5.DocNum

ELSE 99999

END AS 'DocNum',

T2.RefDate AS 'Posting Date',

T2.TransId AS 'JE Numb',

T0.Debit AS 'Debit Dlrs',

T0.Credit AS 'Credit Dlrs'

FROM

JDT1 T0

LEFT OUTER JOIN OCRD T1

ON T0.ShortName = T1.CardCode

LEFT OUTER JOIN OJDT T2

ON T0.TransId = T2.TransId

LEFT OUTER JOIN OPCH T3

ON T0.BaseRef = T3.DocNum

LEFT OUTER JOIN ORPC T4

ON T0.BaseRef = T4.DocNum

LEFT OUTER JOIN OVPM T5

ON T0.BaseRef = T5.DocNum

WHERE

T0.ShortName = T1.CardCode

AND (T0.TransType = 18

OR T0.TransType = 19

OR T0.TransType = 46)

AND (T3.DocStatus = 'O'

OR T4.DocStatus = 'O')

ORDER BY

T1.CardName,

T0.RefDAte

FOR BROWSE

Answers (0)