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