Report linking Delivery , Receipts , Purchase Return & Sales Return Entry

[Former Member]

We have Service Contract for Supplying Inventory to Our Indirect Customer.

For Example:We have signed service contract with IBM whose customer is Bank of India, now once Bank of India logs any complaint call to IBM for faulty part, IBM ask us to supply new part from our inventory to Bank of India and collect faulty part from Bank of India, after inward of faulty part from bank of India we then send same faulty part to IBM against which IBM send us the new part hence the cycle gets completed and our inventory regain its level after four transaction.

Each of Inventory in this transaction is managed serially

To cater to this business scenario, we prepared Delivery Transaction against Bank of India(Bank of India is defined as customer)(For Serial no abcd1234) and prepared sales return entry of faulty from Bank of India (For serial no pqrs6789), then prepared goods return entry (For Serial no pqrs6789) against IBM (IBM is defined as vendor) and in last prepared GRPO entry for inward of Inventory from IBM.

In this scenario any transaction can be initiated first.

Across all this transaction one No (RMA no a UDF) is common same for all transaction

So to have complete tracking of this we want to linked all this transaction i.e for one RMA no report should show all transaction linked in single row including serial no of item.

Satish

[Former Member · Accepted solution]

Hi!

Do u want any query report ?

What r the fields ? Do u have any sample query for the same ?

[Former Member · Accepted solution]

hi,

Yes i do need a query report pls create UDF at your end call " RMANO"

there are lot of column in that Report, Format is given below it should come in single row.

Report Format:

Parameter:

From date to To Date:-

RMA No ** Doc Type** Posting Date*Document NumberBP Reference NoCustomer/Vendor CodeCustomer/Vendor NameDocument StatusItem NoItem DescriptionQuantitydirectionSerial NumberManufacturer Serial NoLot Number*

Doc Type** Posting Date*Document NumberBP Reference NoCustomer/Vendor CodeCustomer/Vendor NameDocument StatusItem NoItem DescriptionQuantitydirectionSerial NumberManufacturer Serial NoLot Number*

Doc Type** Posting Date*Document NumberBP Reference NoCustomer/Vendor CodeCustomer/Vendor NameDocument StatusItem NoItem DescriptionQuantitydirectionSerial NumberManufacturer Serial NoLot Number*

Doc Type** Posting Date*Document NumberBP Reference NoCustomer/Vendor CodeCustomer/Vendor NameDocument StatusItem NoItem DescriptionQuantitydirectionSerial NumberManufacturer Serial NoLot Number*

All the field i.e doc type doc date and doc num will be different for different transaction so they have shown four times in above report.

Pls revert any details u need.

[Former Member · Accepted solution]

Hi

Pls find the below query which i have used

{select * from (select 'GRN' as doc_type,a.docdate,a.docnum,a.u_rmano,a.numatcard,a.cardcode,a.cardname,a.docstatus,c.itemcode,c.itemname,

c.whscode,b.quantity,status,case when c.direction=0 then 'Inward' when c.direction=1 then 'Outward' end as direction ,d.intrserial,d.suppSerial,d.Batchid

from opdn a inner join pdn1 b on a.docentry=b.docentry

inner join (select * from sri1 where basetype=20) c

on a.docnum=c.basenum and b.itemcode=c.itemcode

inner join (select * from osri where basetype=20) d

on a.docnum=d.basenum and b.itemcode=d.itemcode

where not a.u_rmano is null) grn

left outer join

(select 'DC' as doc_type,a.docdate,a.docnum,a.u_rmano,a.numatcard,a.cardcode,a.cardname,a.docstatus,c.itemcode,c.itemname,

c.whscode,b.quantity,status,case when c.direction=0 then 'Inward' when c.direction=1 then 'Outward' end as direction ,d.intrserial,d.suppSerial,d.Batchid

from odln a inner join dln1 b on a.docentry=b.docentry

inner join (select * from sri1 where basetype=15) c

on a.docnum=c.basenum and b.itemcode=c.itemcode

inner join (select * from osri where basetype=20 or basetype=58 or basetype=16 or basetype=67 or basetype=15 ) d

on c.sysSerial=d.sysSerial and c.itemcode=d.itemcode

where not a.u_rmano is null) dc

on grn.u_rmano=dc.u_rmano

left outer join

(select 'SR' as doc_type,a.docdate,a.docnum,a.u_rmano,a.numatcard,a.cardcode,a.cardname,a.docstatus,c.itemcode,c.itemname,

c.whscode,b.quantity as srqty,status,case when c.direction=0 then 'Inward' when c.direction=1 then 'Outward' end as direction ,d.intrserial,d.suppSerial,d.Batchid,1 as [quantity]

from ordn a inner join rdn1 b on a.docentry=b.docentry

inner join (select * from sri1 where basetype=16) c

on a.docnum=c.basenum and b.itemcode=c.itemcode

inner join (select * from osri where basetype=20 or basetype=58 or basetype=16 or basetype=67 or basetype=15 ) d

on c.sysSerial=d.sysSerial and c.itemcode=d.itemcode

where not a.u_rmano is null

) sr

on grn.u_rmano=sr.u_rmano

left outer join

(select 'PR' as doc_type,a.docdate,a.docnum,a.u_rmano,a.numatcard,a.cardcode,a.cardname,a.docstatus,c.itemcode,c.itemname,

c.whscode,b.quantity as prQty,status,case when c.direction=0 then 'Inward' when c.direction=1 then 'Outward' end as direction ,d.intrserial,d.suppSerial,d.Batchid,1 as [quantity]

from orpd a inner join rpd1 b on a.docentry=b.docentry

inner join (select * from sri1 where basetype=21) c

on a.docnum=c.basenum and b.itemcode=c.itemcode

inner join (select * from osri where basetype=20 or basetype=58 or basetype=16 or basetype=67 or basetype=15 ) d

on c.sysSerial=d.sysSerial and c.itemcode=d.itemcode

where not a.u_rmano is null) pr

on grn.u_rmano=pr.u_rmano}

[Former Member · Accepted solution]

Hi!

Don't use sub querys, it will slow down the execution.

This is only a example, write query like this

select 'GRPO' as DocType,
Docnum as GDocnum,
0 as IDocnum,
itemcode,
sum(quantity),
max(serialno),
max(lotno),

from OPDN P0,PDN1 P1
where
P0.Docentry=P1.Docentry
Group by
Docnum ,itemcode

Union All

select 'GRPO' as DocType,
0 as GDocnum,
Docnum as IDocnum,
itemcode,
sum(quantity),
max(serialno),
max(lotno),

from OPCH P0,PCH1 P1
where
P0.Docentry=P1.Docentry
Group by
Docnum ,itemcode

[Former Member · Accepted solution]

hi

I have not write this query even when the execution is slow its not the issue but the main issue is with this query is

it gives misleading result on following condition

This report fails on following condition ,

i) When any of four transaction (DC,GRN,SR,PR) contains more than one quantity and more than one item against one RMA no, let suppose there is one item in transaction with 2 qty then report will show 8 similar rows against this transaction as in our database we have RMA No=u2019111u2019 against which there are 15 qty in transaction so in report its showing 3375 rows which is misleading

ii) When there are more than four transaction against one RMA no i.e there may be two delivery document (or any transaction ) exist against one RMA no.

So if you can help with the logic of the query it will very helpful.