Query for PO Blanket details along with receipt and indian tax details

SELECT QUANTITY_BILLED,SHIP_TO_LOCATION,BILL_TO_LOCATION,RECEIPT_QUNT,QUANTITY_RECEIVED,RECEIPT_NUM,
ORGANIZATION_NAME,SEGMENT1,PO_QTY,PO_VAL,QUANTITY_COMMITTED,
CLOSED_CODE,CREATION_DATE,UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,CURRENCY_CODE,QUANTITY,LINE_NUM,STATUS,VENDOR_NAME,VENDOR_SITE_CODE,ITEM_NAME,ITEM_DESCRIPTION,DESTINATION_TYPE_CODE,CONCATENATED_SEGMENTS,IGST_AMT,SGST_AMT,
CGST_AMT,NON_GST_AMT,NVL((IGST_AMT+SGST_AMT+CGST_AMT+NON_GST_AMT),0)TOTAL_TAX,nvl((PO_VAL+IGST_AMT+SGST_AMT+CGST_AMT+NON_GST_AMT),0)TOTAL_AMT  FROM ( SELECT pll.QUANTITY_BILLED,
(select LOCATION from hz_cust_site_uses_all where site_use_id=ph.SHIP_TO_LOCATION_ID
)SHIP_TO_LOCATION,(select LOCATION from hz_cust_site_uses_all where site_use_id=ph.BILL_TO_LOCATION_ID
)BILL_TO_LOCATION,(select rt.quantity
                            from apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.transaction_type = 'RECEIVE'
                               -- AND rsh.receipt_num='20051'
                                AND pl.po_line_id=rsl.po_line_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                               -- and
                               -- group by rt.po_line_id,rt.ORGANIZATION_ID
                               )RECEIPT_QUNT,(select RSL.QUANTITY_RECEIVED
                            from apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.transaction_type = 'RECEIVE'
                               -- AND rsh.receipt_num='20051'
                                AND pl.po_line_id=rsl.po_line_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                               -- and
                               -- group by rt.po_line_id,rt.ORGANIZATION_ID
                               )QUANTITY_RECEIVED, (select RSh.receipt_num
                            from apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.transaction_type = 'RECEIVE'
                               -- AND rsh.receipt_num='20051'
                                AND pl.po_line_id=rsl.po_line_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                               -- and
                               -- group by rt.po_line_id,rt.ORGANIZATION_ID
                               )receipt_num,(select ORGANIZATION_NAME from org_organization_definitions where organization_id=pll.SHIP_TO_ORGANIZATION_ID)ORGANIZATION_NAME,
ph.segment1, (SELECT SUM (quantity_committed)
                         FROM po_lines_all
                        WHERE po_header_id = ph.po_header_id) po_qty,nvl((pll.quantity*unit_price),0) po_val,
         pl.quantity_committed, pll.closed_code, ph.creation_date,
         pl.unit_meas_lookup_code, pl.unit_price, currency_code, pll.quantity,
         pl.line_num, po_headers_sv3.get_po_status (ph.po_header_id) status,
         (SELECT vendor_name
            FROM po_vendors
           WHERE vendor_id = ph.vendor_id) vendor_name, (SELECT vendor_site_code
            FROM po_vendor_sites_All
           WHERE vendor_site_id = ph.vendor_site_id) vendor_site_code,
         (SELECT segment1
            FROM mtl_system_items_b
           WHERE inventory_item_id = pl.item_id
             AND organization_id = pll.SHIP_TO_ORGANIZATION_ID) item_name,
         pl.item_description, pda.destination_type_code, concatenated_segments,
               NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                              --and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type = 'RECEIVE'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'IGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                             -- and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type='RETURN TO VENDOR'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'IGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id),0)IGST_AMT,
                              NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                              --and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type = 'RECEIVE'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'SGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                             -- and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type='RETURN TO VENDOR'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'SGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id),0)SGST_AMT,
                               NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                              --and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type = 'RECEIVE'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'CGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                             -- and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type='RETURN TO VENDOR'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE LIKE 'CGST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id),0)CGST_AMT,
                               NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                              --and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type = 'RECEIVE'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE NOT LIKE '%GST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
                                       FROM jai_tax_lines ,
                                       apps.rcv_transactions rt,
                                apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl
                            where rsh.shipment_header_id = rt.shipment_header_id
                                AND rsl.shipment_header_id=rsh.shipment_header_id
                                AND rsl.po_header_id=rt.po_header_id
                                AND rsl.po_line_id=rt.po_line_id
                                AND pl.po_line_id=rsl.po_line_id
                                AND rsl.po_line_location_id=rt.po_line_location_id
                                AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
                                AND rt.transaction_type = 'RECEIVE'
                             -- and rsh.receipt_num='20051'
                          -- WHERE 1= 1
                           AND trx_type='RETURN TO VENDOR'
                     AND entity_code = 'RCV_TRANSACTION'
                     AND TAX_RATE_CODE NOT LIKE '%GST%'
                            AND trx_id = rsh.shipment_header_id
                            AND trx_line_id= rsl.shipment_line_id),0)NON_GST_AMT
    FROM po_headers_all ph,
         po_lines_all pl,
         po_line_locations_all pll,
         po_distributions_all pda,
         gl_code_combinations_kfv gcc,
         po_releases_all pra/*,  ( SELECT DISTINCT j.trx_id, j.trx_line_id, J.DET_FACTOR_ID, -- TAX_RATE_CODE,
                        nvl(TAX_RATE_PERCENTAGE,0) TAX_RATE_PERCENTAGE,
                        nvl((SELECT sum(unround_tax_amt_tax_curr)
                            FROM jai_tax_lines
                           WHERE 1= 1
                             AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
                             AND TAX_RATE_CODE LIKE 'IGST%'),0) IGST_TAX_AMT,
                         nvl((SELECT sum(unround_tax_amt_tax_curr)         
                            FROM jai_tax_lines
                           WHERE 1= 1
                             AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
                             AND TAX_RATE_CODE NOT LIKE 'IGST%' and TAX_RATE_CODE LIKE 'SGST%'),0) SGST_TAX_AMT,
                         nvl((SELECT sum(unround_tax_amt_tax_curr)
                            FROM jai_tax_lines
                           WHERE 1= 1
                             AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
                             AND TAX_RATE_CODE NOT LIKE 'IGST%' and TAX_RATE_CODE LIKE 'CGST%'),0) CGST_TAX_AMT,
                        nvl((select sum(nvl(unround_tax_amt_tax_curr,0))
                        from jai_tax_lines where tax_rate_code not like '%GST%'
                        AND trx_id = j.trx_id AND trx_line_id = j.trx_line_id and DET_FACTOR_ID = j.DET_FACTOR_ID),0) NON_GST_TAX_AMT,
                         j.recoverable_flag
                    FROM jai_tax_lines j
                    WHERE 1= 1
                 --   &LP_CREATION_DATE1
--                     AND trunc(trx_date) between :P_FDATE and :P_TDATE
                     AND j.trx_type = 'RECEIVE'
                     AND j.entity_code = 'RCV_TRANSACTION'
                     AND J.TAX_RATE_CODE LIKE '%GST%'
                     )TAX,rcv_shipment_lines rsl*/
   WHERE 1 = 1
     AND pll.po_header_id = ph.po_header_id
     AND pll.po_line_id = pl.po_line_id
     AND ph.po_header_id = pl.po_header_id
     AND pda.po_header_id = ph.po_header_id
     AND pda.po_line_id = pl.po_line_id
     AND pda.line_location_id = pll.line_location_id
    -- AND ph.segment1 = '2189689'
     AND gcc.code_combination_id = pda.code_combination_id
     AND ph.po_header_id = pra.po_header_id
     AND ph.type_lookup_code IN ('BLANKET')
      and ph.vendor_id=NVL(:p_vendor_id,ph.vendor_id)
 and ph.po_header_id=NVL(:p_po_header, ph.po_header_id)
--AND TRUNC(PH.CREATION_DATE) BETWEEN :P_ORIG_FROM_DT AND :P_ORIG_TO_DT
&lp_creation_date
     AND pll.po_release_id = pra.po_release_id
ORDER BY line_num)
where 1=1
&LP_PENDING_QTY;




---Dhiraj Jha