Return To Vendor Summay-GST

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
  • October 25, 2017 | 20 views