PO_TAX_REPORT

SELECT   DISTINCT
           ph.segment1,
           PH.ORG_ID,
           pv.segment1 "Vendor Code ",
           ph.po_header_id,
           pll.ship_to_organization_id,
           pv.vendor_name "VENDOR NAME ",
           pvs.address_line1 "Supplier_Address1",
           pvs.address_line2 "Supplier_Address2",
           pvs.city "CITY ",
           pvs.state "STATE ",
           pvs.zip "PIN CODE ",
           (pvc.first_name || pvc.last_name) "Supplier's  Contact Person ",
           pvs.phone "Mobile No. / Phone No",
           pvc.fax "Fax no. ",
           pvc.email_address "EMAIL ID ",
           (ph.segment1 || '/' || ph.revision_num) "PO/REV No.",
           ph.creation_date "Date of PO ",
           (prel.release_num || '/' || ph.revision_num) "Rel/REV No.",
           ph.attribute1 "Your Ref ",
           ph.currency_code "Currency ",
           ppf.full_name "Buyer Name ",
           UPPER (h.address_line_1) OU_Address1,
           UPPER (h.address_line_2) OU_Address2,
           (SELECT   a.segment1
              FROM   po_requisition_headers_all a,
                     po_requisition_lines_all b,
                     po_req_distributions_all c
             WHERE   a.requisition_header_id(+) = b.requisition_header_id
                     AND b.requisition_line_id(+) = c.requisition_line_id
                     AND c.distribution_id IN
                              (SELECT   B.req_distribution_id
                                 FROM   PO_HEADERS_ALL A,
                                        PO_DISTRIBUTIONS_ALL B
                                WHERE       A.PO_HEADER_ID = PH.PO_HEADER_ID
                                        AND A.PO_HEADER_ID = B.PO_HEADER_ID
                                        AND ROWNUM = 1
                                        AND B.req_distribution_id IS NOT NULL)
                     AND ROWNUM = 1)
              "PR No.",
           h.town_or_city "CITY /STATE",
           h.telephone_number_1 "PHONE No./ Fax no.",
           ph.NOTE_TO_VENDOR,
           --           term.name,
           FLV1.MEANING Delivery_Terms,
           FLV2.MEANING Freight,
           PH.SHIP_VIA_LOOKUP_CODE Carrier,
           -----------------------------------------Payment_terms, test elango
           term.name "PAYMENT TERMS",
           ppf.first_name,
           CMN.cst_reg_no "CST No",
           CMN.vat_reg_no "VAT/ TIN No.",
           cmn.TAN_NO,
           CMN.ec_code "ECC No.",
           CMN.PAN_NO "PAN_NO",
           hou.NAME "operating unit",
           ph.segment4,
           ph.segment5,
           (SELECT   TAX.tax_name
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type IN ('Excise', 'ADDITIONAL_CVD')
                     AND ROWNUM = 1)
              "Excise Tax",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   JAI_PO_TAXES A
             WHERE   A.po_header_id = PH.PO_HEADER_ID
                     AND A.tax_type IN ('Excise', 'ADDITIONAL_CVD'))
              "Excise Duty",
           (SELECT   TAX.tax_name
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE   A.po_header_id = PH.po_header_id AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type IN
                              ('EXCISE_EDUCATION_CESS',
                               'SERVICE_EDUCATION_CESS',
                               'CUSTOMS_EDUCATION_CESS',
                               'CVD_EDUCATION_CESS')
                     AND ROWNUM = 1)
              "EDUCATION Tax",
           (SELECT   SUM (A.tax_amount)
              FROM   jai_po_taxes A
             WHERE   A.po_header_id = PH.po_header_id
                     AND A.tax_type IN
                              ('EXCISE_EDUCATION_CESS',
                               'SERVICE_EDUCATION_CESS',
                               'CUSTOMS_EDUCATION_CESS',
                               'CVD_EDUCATION_CESS'))
              "EDUCATION_CESS",
           (SELECT   TAX.TAX_NAME
              FROM   jai_po_taxes A, JAI_CMN_TAXES_ALL TAX
             WHERE   A.po_header_id = PH.po_header_id AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type IN
                              ('SERVICE_SH_EDU_CESS',
                               'CVD_SH_EDU_CESS',
                               'EXCISE_SH_EDU_CESS',
                               'CUSTOMS_SH_EDU_CESS')
                     AND ROWNUM = 1)
              "Higher Education Tax ",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   jai_po_taxes A
             WHERE   A.po_header_id = PH.po_header_id
                     AND A.tax_type IN
                              ('SERVICE_SH_EDU_CESS',
                               'CVD_SH_EDU_CESS',
                               'EXCISE_SH_EDU_CESS',
                               'CUSTOMS_SH_EDU_CESS'))
              "Higher Education Cess ",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   JAI_PO_TAXES A
             WHERE   A.po_header_id = PH.PO_HEADER_ID
                     AND A.tax_type IN ('VALUE ADDED TAX', 'CST'))
              "SALES TAX/VAT/cst ",
           (SELECT   TAX.TAX_NAME
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type IN ('VALUE ADDED TAX', 'CST')
                     AND ROWNUM = 1)
              "SALES TAX/VAT NAME ",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   JAI_PO_TAXES A
             WHERE   A.po_header_id = PH.PO_HEADER_ID
                     AND A.tax_type = 'Service')
              "service tax  ",
           (SELECT   TAX.TAX_NAME
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type = 'Service'
                     AND ROWNUM = 1)
              "service tax NAME  ",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   JAI_PO_TAXES A
             WHERE   A.po_header_id = PH.PO_HEADER_ID
                     AND A.tax_type = 'Freight')
              "TRANSPORTATION CHARGES  ",
           (SELECT   TAX.TAX_NAME
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type = 'Freight'
                     AND ROWNUM = 1)
              "TRANSPORTATION CHARGES NAME ",
           (SELECT   TAX.tax_name
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type = 'Other'
                     AND tax.TAX_NAME NOT LIKE '%NCL%'       --'%P'||'&'||'F%'
                     AND ROWNUM = 1)
              "PackForwardTax",
           (SELECT   SUM (A.TAX_AMOUNT)
              FROM   JAI_PO_TAXES A, JAI_CMN_TAXES_ALL TAX
             WHERE       A.po_header_id = PH.PO_HEADER_ID
                     AND A.TAX_ID = TAX.TAX_ID
                     AND A.tax_type = 'Other'
                     AND tax.TAX_NAME NOT LIKE '%NCL%')              --ncl, nc
              "PackForwardAmt",
           UPPER(   h.ADDRESS_LINE_1
                 || ''
                 || h.ADDRESS_LINE_2
                 || ' '
                 || h.ADDRESS_LINE_3
                 || ' '
                 || h.COUNTRY
                 || ''
                 || h.POSTAL_CODE
                 || ''
                 || h.REGION_1
                 || ''
                 || h.REGION_2
                 || ''
                 || h.REGION_3
                 || ''
                 || h.TELEPHONE_NUMBER_1
                 || ''
                 || h.TELEPHONE_NUMBER_2
                 || ''
                 || h.TELEPHONE_NUMBER_2
                 || ''
                 || h.LOC_INFORMATION13
                 || ''
                 || h.LOC_INFORMATION14
                 || ''
                 || h.LOC_INFORMATION15)
              "ADDRESS" 
FROM   po_distributions_all pd,
           po_line_locations_all pll,
           po_lines_all pl,
           po_headers_all ph,
           ap_terms_tl term,
           JAI_CMN_VENDOR_SITES pts,
           po_releases_all prel,
           ap_suppliers pv,
           ap_supplier_sites_all pvs,
           ap_supplier_contacts pvc,
           hr_locations_all h,
           hr_all_organization_units hu,
           hr_operating_units hou,
           per_all_people_f ppf,
           mtl_system_items_b msi,
           jai_cmn_inventory_orgs cmn,
           FND_LOOKUP_VALUES FLV1,
           FND_LOOKUP_VALUES FLV2
   WHERE       ph.po_header_id = pl.po_header_id
           AND pd.line_location_id = pll.line_location_id
           AND pll.po_line_id = pl.po_line_id
           AND term.term_id = ph.terms_id
           AND prel.po_release_id(+) = pd.po_release_id
           AND ppf.person_id(+) = ph.agent_id
           AND h.location_id = pll.ship_to_location_id
           AND hou.organization_id = ph.org_id
           AND pv.vendor_id(+) = ph.vendor_id
           AND pvs.vendor_site_id(+) = ph.vendor_site_id
           AND ph.vendor_contact_id = pvc.vendor_contact_id(+)
           AND pts.vendor_site_id(+) = pvs.vendor_site_id
           AND msi.inventory_item_id(+) = pl.item_id
           AND h.LOCATION_ID = cmn.LOCATION_ID(+)
           AND HU.ORGANIZATION_ID(+) = CMN.ORGANIZATION_ID  --------  test elango
           AND hu.LOCATION_ID(+) = cmn.LOCATION_ID
           AND FLV1.LOOKUP_CODE(+) = PH.FOB_LOOKUP_CODE
           AND FLV2.LOOKUP_CODE(+) = PH.FREIGHT_TERMS_LOOKUP_CODE
           AND ph.segment1 >= :from_po
           AND ph.segment1 <= :to_po
           AND (ph.AUTHORIZATION_STATUS =
                   NVL (:P_PO_STATUS, ph.AUTHORIZATION_STATUS)
                OR ph.AUTHORIZATION_STATUS IS NULL)
             &p_release_no
           AND ppf.person_id = NVL (:P_PERSON_ID, ppf.person_id)
          AND FLV1.LOOKUP_TYPE(+) = 'FOB'
           AND FLV2.LOOKUP_TYPE(+) = 'FREIGHT TERMS'
           AND ph.org_id = fnd_profile.VALUE ('ORG_ID')
GROUP BY   pv.segment1,
           ph.po_header_id,
           pv.vendor_name,
           pvs.address_line1,
           pvs.address_line2,
           pvs.city,
           pvs.state,
           pvs.zip,
           pvc.first_name,
           pvc.last_name,
           pvs.phone,
           pvc.fax,
           pvc.email_address,
           ph.segment1,
           ph.revision_num,
           ph.creation_date,
           ph.NOTE_TO_VENDOR,
           prel.release_num,
           ph.revision_num,
           ph.attribute1,
           ph.currency_code,
           ppf.full_name,
           h.address_line_1,
           h.address_line_2,
           h.location_code,
           h.town_or_city,
           h.telephone_number_1,
           pll.tax_name,
           ph.fob_lookup_code,
           ph.freight_terms_lookup_code,
           term.name,
           ppf.first_name,
           CMN.cst_reg_no,
           CMN.vat_reg_no,
           CMN.ec_code,
           CMN.PAN_NO,
           hou.NAME,
           ph.segment4,
           ph.segment5,
           pll.ship_to_organization_id,
           pll.quantity,
           pll.need_by_date,
           cmn.TAN_NO,
           (   h.ADDRESS_LINE_1
            || ''
            || h.ADDRESS_LINE_2
            || ' '
            || h.ADDRESS_LINE_3
            || ' '
            || h.COUNTRY
            || ''
            || h.POSTAL_CODE
            || ''
            || h.REGION_1
            || ''
            || h.REGION_2
            || ''
            || h.REGION_3
            || ''
            || h.TELEPHONE_NUMBER_1
            || ''
            || h.TELEPHONE_NUMBER_2
            || ''
            || h.TELEPHONE_NUMBER_2
            || ''
            || h.LOC_INFORMATION13
            || ''
            || h.LOC_INFORMATION14
            || ''
            || h.LOC_INFORMATION15),
           FLV1.MEANING,
           FLV2.MEANING,
           PH.SHIP_VIA_LOOKUP_CODE,
           pl.po_line_id,
           PH.ORG_ID
ORDER BY   TO_NUMBER (ph.segment1)


---------------------  line -------------
SELECT   msi.segment1 "Item code ",
         pl.item_description "Item Description",
         pl.unit_meas_lookup_code "UOM",
         pll.quantity "Shipment Qty ",
         pll.need_by_date "Need By date ",
         pl.unit_price "Unit Basic Price",
         (pl.unit_price * pll.QUANTITY) "Total Basic Price",
         pl.po_header_id,
         msi.organization_id,
         pll.need_by_date,
         :Currency_
  FROM   po_lines_all pl, po_line_locations_all pll, mtl_system_items_b msi
 WHERE       pl.po_line_id = pll.po_line_id
         AND pl.item_id = msi.inventory_item_id
         AND pll.po_line_id = pl.po_line_id(+)
         and msi.organization_id=pll.SHIP_TO_ORGANIZATION_ID
         --AND pl.po_header_id               = ph.po_header_id(+)
         AND msi.inventory_item_id(+) = pl.item_id
         AND PL.CLOSED_CODE not in 'CLOSED'
         AND pll.quantity IS NOT NULL