Pending Good Receipt Po

[Former Member]

Hi friends

i want your help regarding this matter. in our (SAP B1 system ) company we want a report of pending document (Goods Receipt Po,Goods Return) report

which is indicate as a balance in Goods in Transist accounts. that mean balance of GIT account should equal to pending Good Receipt Po Values. how can i get this report

( this can not do with document find level, because open Goods Receipt Po value not equal to GIT account balance)

thank you

saman

Edited by: saman_wm on Dec 9, 2011 3:16 PM

[Former Member · Accepted solution]

Try this,

SELECT T0.[DocNum], T0.[DocStatus], T1.[ItemCode], T1.[Dscription], T1.[Quantity], (T1.[OpenQty] * T1.[Price]) as 'PO B/O' FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[LineStatus] ='o'

[Former Member · Accepted solution]

Hi Jenkis

Thank you for reply . i want a report of pending Goods Receipt PO, but in your report it is for purchase order

saman

[Former Member]

Try:

SELECT T0.refdate,

Case when T0.TransType = 30 then 'Journal Entry'

when T0.TransType = 18 then 'AP Invoice'

when T0.TransType = 19 then 'AP CR Memo'

when T0.TransType = 20 then 'GRPO'

when T0.TransType = 21 then 'Goods return'

End as 'Trans Type',

T0.BaseRef as 'Doc No',

T0.transid as 'Trans ID',

t1.contraact as 'Offset Acct',

(T1.debit -T1.credit) as 'C/D(LC)'

from ojdt T0

inner join jdt1 T1 on T0.TransId = T1.TransId

where T0.refdate >= [%0\] AND T0.refdate <= [%1\]

and t1.contraact like '21041%'

[Former Member]

Hi Gordon

thank you for your help. it is use full to me but its not the requirement i want,

i want pending documents according to GIT balance for a selected period, that mean document name, docu number, item name, value ect

in your solution can you add base document date as a new column, if there are many base document how to indicate

all of numbers in report

saman

[Former Member]

It can be done but too complicated. It is a small project already. You need to figure it out.

If you only need GRPO without other types, I can give you solution.

[Former Member]

Hi Gordon

Thank You for your great help. ok please tell me your solution Relevant to GRPO & Goods Return

Some times I will be able to mange my requirement with it.

saman

[Former Member]

Try this for GRPO:

SELECT T0.refdate,

Case when T0.TransType = 30 then 'Journal Entry'

when T0.TransType = 18 then 'AP Invoice'

when T0.TransType = 19 then 'AP CR Memo'

when T0.TransType = 20 then 'GRPO'

when T0.TransType = 21 then 'Goods return'

End as 'Trans Type',

T0.BaseRef as 'Doc No',

T0.transid as 'Trans ID',

t2.AcctName as 'Offset Acct',

t3.Itemcode,

t3.quantity,

t3.lineTotal,

(T1.debit -T1.credit) as 'C/D(LC)'

from ojdt T0

inner join jdt1 T1 on T0.TransId = T1.TransId

inner join OACT T2 on T2.AcctCode=t1.contraact

LEFT JOIN PDN1 T3 ON T3.DocEntry = Cast(T1.Ref1 AS Integer)

where T0.refdate >= [%0\] AND T0.refdate <= [%1\]

and t1.contraact like '21041%'

[Former Member]

Hi Saman.......

Are you keeping any track for Goods in Transit till the supplier gets that material back to his warehouse?

I don't think you have mentioned here that you are keeping any track for GIT based on Goods return......

Please specify.....

Regards,

Rahul

[Former Member]

Hi Rahul

we use only Vendors Reference as a track in goods Receipt PO. No any long delay between GRN and A/P Invoice date

i want to know is there a posting date error with A/P invoice and what are the those document

saman

[Former Member]

Hi Saman.......

I mean to say do you have any tracking No. when your Supplier received material back.

Are you updating any reference no. in GRPO once you get the confirmation from your Supplier that he has received material?

If yes then you can get the GIT stock accounting very easily......

Regards,

Rahul

[Former Member]

Hi Rahul

We Do not have any Tracking No- for GIT

saman

[Former Member]

Hi Saman,

Is GIT in your posting Goods In Transit ?

Thanks,

Gordon

[Former Member]

Hi Gordon

You are correct, GIT mean Goods in transit accounts

saman

Edited by: saman_wm on Dec 9, 2011 3:25 PM

[Former Member]

In that case, you just need to query JDT1 and OJDT to get the detail.

[Former Member]

Hi Gordon ,

will you please send me the Query for that report

saman

[Former Member]

SELECT T0.DocNum, T0.DocStatus, T1.ItemCode, T1.Dscription, T1.Quantity,T1.OpenQty, (T1.OpenQty * T1.Price) as 'GRPO B/O' FROM OPdn T0 INNER JOIN pdn1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.LineStatus ='o'

[Former Member]

What is your Account code for this GIT?

[Former Member]

Hi Gordon,

my Account code is

21041 - Goods in Transist

Thank you

saman

[Former Member]

Have you checked system report for General Ledger for this account? What else do you need?

[Former Member]

Hi Gordon,

I checked system report, but it is not full fill my requirement

I want that report according to following parameters,

1-Date from

2-Date to

3-document Series

Report Results

Pending document -item wise with line value [which is directly effect to GIT accounts balance]

Thank you

saman

Edited by: saman_wm on Dec 12, 2011 6:49 AM

Edited by: saman_wm on Dec 12, 2011 6:51 AM

[Former Member]

Hi Gordon

Can You Send me a solution for Above requirement

Thank you

Saman