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
