SELECT distinct pha.segment1 "Po Number",
pha.creation_date "Date",
pha.approved_date "Approved Date",
(pla.unit_price* pla.quantity) "Amount w/o Tax",
jpt.tax_amount "Tax Amount",
jpll.total_amount "Total Amount",
pla.line_num "Line Num",
pv.vendor_name"Vendor Name",
jrt.tax_name"Tax Name",
DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
hou.name "Organization Name"
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_vendors pv,
JAI_PO_LINE_LOCATIONS jpll,
hr_operating_units hou,
JAI_RCV_TAX_V jrt,
jai_po_taxes jpt,
PO_LOOKUP_CODES POLC1,
PO_LOOKUP_CODES POLC2
WHERE pha.po_header_id=pla.po_header_id
AND plla.po_header_id=pha.po_header_id
AND pv.vendor_id=pha.vendor_id
AND plla.po_line_id=pla.po_line_id
AND pha.segment1=:P_PONUM --'2012102020109'
AND jpll.po_header_id=pha.po_header_id
AND jpll.po_line_id=plla.po_line_id
AND pha.org_id= hou.organization_id
AND jrt.vendor_id=pha.vendor_id
AND jrt.tax_id= jpt.tax_id
AND jpt.po_header_id=pha.po_header_id
AND jpt.po_line_id=pla.po_line_id
AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
pha.creation_date,
pha.approved_date,
jpt.tax_amount ,
jpll.total_amount,
pla.line_num ,
pv.vendor_name,
jrt.tax_name,
DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
hou.name*/
pha.creation_date "Date",
pha.approved_date "Approved Date",
(pla.unit_price* pla.quantity) "Amount w/o Tax",
jpt.tax_amount "Tax Amount",
jpll.total_amount "Total Amount",
pla.line_num "Line Num",
pv.vendor_name"Vendor Name",
jrt.tax_name"Tax Name",
DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
hou.name "Organization Name"
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_vendors pv,
JAI_PO_LINE_LOCATIONS jpll,
hr_operating_units hou,
JAI_RCV_TAX_V jrt,
jai_po_taxes jpt,
PO_LOOKUP_CODES POLC1,
PO_LOOKUP_CODES POLC2
WHERE pha.po_header_id=pla.po_header_id
AND plla.po_header_id=pha.po_header_id
AND pv.vendor_id=pha.vendor_id
AND plla.po_line_id=pla.po_line_id
AND pha.segment1=:P_PONUM --'2012102020109'
AND jpll.po_header_id=pha.po_header_id
AND jpll.po_line_id=plla.po_line_id
AND pha.org_id= hou.organization_id
AND jrt.vendor_id=pha.vendor_id
AND jrt.tax_id= jpt.tax_id
AND jpt.po_header_id=pha.po_header_id
AND jpt.po_line_id=pla.po_line_id
AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
pha.creation_date,
pha.approved_date,
jpt.tax_amount ,
jpll.total_amount,
pla.line_num ,
pv.vendor_name,
jrt.tax_name,
DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
hou.name*/
No comments:
Post a Comment