Introduction
The below query fetches the data of purchase order wherein those items present in purchase order needs to be returned to Supplier because of some damage caused to goods etc..
SELECT rch.shipment_header_id,TRUNC(RCH.Creation_Date)Rec_Date, rcl.shipment_line_id,
RCH.receipt_num,RCT.organization_id,
RCH.Attribute4 DC_No,RCH.Attribute6 DC_Date,NVL(RCL.QUANTITY_SHIPPED,0)Recd_Qty ,
(RCT.Quantity)Returned_qty,(RCT.Transaction_date)Return_date ,
RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,
–Lpad(RCT.ATTRIBUTE7,4,0)Del_Slip_no,
RCT.ATTRIBUTE7 Del_Slip_no,
RCL.item_id,RCL.item_Description||’ ‘||hsnn.template_name item_Description, B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) Vendor_Code ,
(RCT.Attribute5)No_Of_Pack,(RCT.ATTRIBUTE6)Qty_Per_Pack ,MTR.Description Reason,
SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip,(
SELECT jprl.registration_number
FROM jai_party_regs jpr, ja.jai_party_reg_lines jprl
where 1 = 1
and party_id = SIT.vendor_id
and party_site_id = SIT.vendor_site_id
and jpr.party_reg_id = jprl.party_reg_id
and jprl.regime_id=(SELECT jrc.regime_id
FROM JA.jai_regimes jrc
WHERE jrc.regime_code = ‘GST’
)
)”GST Registration Number”,
RCT.PO_UNIT_PRICE, sum(jtla.ACTUAL_TAX_RATE) “GST%”, (RCT.Quantity)*RCT.PO_UNIT_PRICE “RTV Value”, sum(UNROUND_TAX_AMT_TAX_CURR) “Tax Amount”
FROM rcv_shipment_headers RCH,
rcv_shipment_lines RCL,
rcv_transactions RCT,
mtl_system_items B,
HR_ORGANIZATION_UNITS_V HR ,
po_vendors VEN,
po_vendor_sites_all SIT ,
mtl_transaction_reasons MTR,
jai_item_templ_hdr jith,
jai_item_templ_hdr hsnn,
jai_tax_lines_all jtla
WHERE RCH.shipment_header_id = RCL.shipment_header_id
AND RCT.shipment_header_id = RCL.shipment_header_id
AND RCH.shipment_header_id = RCT.shipment_header_id
AND RCL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND TRUNC(RCT.CREATION_DATE) BETWEEN :P_FROM_DT AND :P_TO_DATE
AND RCT.ORGANIZATION_ID = :P_ORG_ID
–AND RCT.ATTRIBUTE7 BETWEEN NVL(:P_DC_NUM ,RCT.ATTRIBUTE7) AND NVL(:P_DC_NUM1 ,RCT.ATTRIBUTE7)
AND RCL.item_id = B.Inventory_Item_id
AND RCT.transaction_type = ‘RETURN TO VENDOR’
AND RCL.TO_ORGANIZATION_ID =B.ORGANIZATION_ID
AND RCT.VENDOR_ID = VEN.vendor_id
AND RCT.VENDOR_ID = SIT.vendor_id
AND rct.vendor_site_id=sit.vendor_site_id
AND RCH.receipt_source_code =’VENDOR’
AND RCT.organization_id = HR.Organization_id
AND RCH.SHIP_TO_ORG_ID= HR.Organization_id
AND RCT.reason_id = MTR.Reason_id (+)
AND sit.inactive_date IS NULL
and jith.inventory_item_id(+) = B.Inventory_Item_id
and jith.entity_type_code = ‘ITEM_TEMPL_ASGN’
and jith.entity_id = hsnn.template_hdr_id
and hsnn.entity_type_code = ‘ITEM_TEMPL’
–and jith.organization_id = RCH.SHIP_TO_ORG_ID
and jtla.entity_code = ‘RCV_TRANSACTION’
–and rch.shipment_header_id = rct.shipment_header_id
–AND jtla.organization_id = rch.ship_to_org_id
–and jtla.trx_number = rch.receipt_num
AND jtla.trx_loc_line_id(+) = rct.transaction_id
AND jtla.trx_id(+) = rct.shipment_header_id
AND jtla.trx_line_id(+) = rct.shipment_line_id
GROUP BY SIT.vendor_id, hsnn.template_name, RCT.PO_UNIT_PRICE,
rch.shipment_header_id,TRUNC(RCH.Creation_Date),
RCH.receipt_num, RCT.organization_id,
RCH.Attribute4 ,RCH.Attribute6,(RCT.Transaction_date),
(RCT.Attribute5) ,(RCT.ATTRIBUTE6) ,RCT.ATTRIBUTE7,MTR.Description ,
RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,
RCL.item_id,RCL.item_Description,B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) ,
SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip, RCT.Quantity, RCL.QUANTITY_SHIPPED, rcl.shipment_line_id
ORDER BY RCH.receipt_num
Got any queries?
Do drop a note by writing us at Doyen.ebiz@staging.doyensys.com or use the comment section below to ask your questions.
