CREATE OR REPLACE VIEW apps.xx_po_details_v (authorization_status,
org_id,
org_name,
organization_id,
ship_plant,
doc_type,
sub_doc_type,
document_no,
revision_num,
supplier_name,
currency_code,
quantity,
unit_price,
doc_value,
tax_amount,
total_value,
payment_trem,
SOURCE,
cat,
po_header_id,
po_line_id,
item_id,
release_num,
revoverable_taxes,
nonrevoverable_taxes,
need_by_date,
location_code,
avg_cost,
line_type,
special_remark,
line_num,
item_description
)
AS
SELECT a.authorization_status, a.org_id,
(SELECT xx.NAME
FROM hr_operating_units xx
WHERE xx.organization_id = a.org_id) org_name,
c.ship_to_organization_id organization_id,
(SELECT xx.organization_code
FROM mtl_parameters xx
WHERE xx.organization_id = c.ship_to_organization_id) ship_plant,
a.type_lookup_code || ' PO' doc_type,
CASE
WHEN (SELECT segment1
FROM mtl_categories_b xx
WHERE xx.category_id = b.category_id) LIKE
'GEN%'
OR (SELECT segment1
FROM mtl_categories_b xx
WHERE xx.category_id = b.category_id) LIKE 'SER%'
THEN 'INDIRECT'
ELSE 'DIRECT'
END sub_doc_type,
a.segment1 document_no, a.revision_num,
(SELECT xx.vendor_name
FROM ap_suppliers xx
WHERE xx.vendor_id = a.vendor_id) supplier_name, a.currency_code,
c.quantity, b.unit_price, c.quantity * b.unit_price doc_value,
d.tax_amount,
c.quantity * b.unit_price + NVL (d.tax_amount, 0) total_value,
(SELECT xx.NAME
FROM ap_terms xx
WHERE xx.term_id = a.terms_id) payment_trem, a.attribute1 SOURCE,
(SELECT segment1
FROM mtl_categories_b xx
WHERE xx.category_id = b.category_id) cat, a.po_header_id,
b.po_line_id, b.item_id,
(SELECT MAX (x.release_num)
FROM po.po_releases_all x
WHERE x.po_header_id = a.po_header_id) release_num,
(SELECT SUM (xx.tax_amount)
FROM jai_po_taxes xx
WHERE xx.line_location_id = c.line_location_id
AND xx.modvat_flag = 'Y') revoverable_taxes,
(SELECT SUM (xx.tax_amount)
FROM jai_po_taxes xx
WHERE xx.line_location_id =
c.line_location_id
AND xx.modvat_flag = 'N') nonrevoverable_taxes,
c.need_by_date,
(SELECT xx.location_code
FROM hr_locations_all xx
WHERE xx.location_id = c.ship_to_location_id) location_code,
(SELECT SUM (xx.item_cost)
FROM cst_item_costs xx
WHERE xx.inventory_item_id = b.item_id
AND xx.organization_id = c.ship_to_organization_id
AND xx.cost_type_id = 2) avg_cost,
DECODE (b.line_type_id,
1, 'Goods',
1020, 'Services',
'OSP'
) line_type,
a.attribute3
|| a.attribute4
|| a.attribute5
|| a.attribute6
|| a.attribute7
|| a.attribute8
|| a.attribute9
|| a.attribute10 special_remark,
b.line_num, b.item_description
FROM po_headers_all a,
po_lines_all b,
po_line_locations_all c,
jai_po_line_locations d
WHERE a.po_header_id = b.po_header_id
AND b.po_line_id = c.po_line_id
AND c.line_location_id = d.line_location_id(+)
AND NVL (a.authorization_status, 'INCOMPLETE') NOT IN ('APPROVED', 'IN PROCESS')
AND NVL (a.closed_code, 'OPEN') = 'OPEN'
AND NVL (d.tax_amount, 0) != 0
AND NVL ((SELECT segment1
FROM mtl_categories_b xx
WHERE xx.category_id = b.category_id), '00') NOT LIKE '00%'
AND a.creation_date > SYSDATE - 300;
