Wednesday, 25 October 2017

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

1 comment:

  1. Hi admin.... This is really informative post & useful for me.Thank you so much for sharing amazing post. CFA Audit
    Warehouse Audit
    Vendor Reconciliation
    GST Helpdesk

    ReplyDelete