'RENTALS' OR prl.attribute7 IS NULL) AND pa.project_id = NVL (:p_project_id, pa.project_id) AND pv.vendor_id = NVL (:p_vendor_id, pv.vendor_id) AND ph.po_header_id = NVL (:p_header_id, ph.po_header_id) AND ph.revision_num = NVL (:p_rev_num, ph.revision_num) GROUP BY ph.segment1,ph.authorization_status ,ph.REVISED_DATE,ph.revision_num,TRUNC (ph.creation_date),pl.line_num,pa.segment1,ph.attribute_category, pl.note_to_vendor, pl.cancel_flag, pv.vendor_name,pv.segment1,pvs.address_line1,pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name, --pvs.country, pl.line_type_id, papf.full_name, pvs.state, hr2.location_code,pl.unit_meas_lookup_code,pl.quantity,pl.unit_price, --(pl.quantity * pl.unit_price) DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*pl.unit_price) ,pl.item_description,ph.attribute1,ph.attribute9,ph.currency_code, pl.supplier_ref_number,pvs.phone,pvs.fax,hr.address_line_1,hr.address_line_2,hr.address_line_3, --ft.nls_territory, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY'),hou.name,hr2.address_line_1,hr2.address_line_2,hr2.address_line_3, hr2.country,hr2.region_1,hr.location_code|| ','|| plc1.description|| ','|| plc2.description|| ',' || plc3.description|| ','|| ph.fob_lookup_code,ph.po_header_id,ph.org_id,NVL(pl.contract_id,pl.from_header_id),hou.location_id,ph.agent_id,pl.vendor_product_num, pl.item_id,pll.ship_to_organization_id,ph.bill_to_location_id,pda.req_distribution_id,prl.requisition_line_id, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID), TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID), prl.attribute4,pda.task_id,pda.expenditure_type ,pl.po_line_id,prl.line_num, -- TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type), CASE WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 END,pl.from_header_id, TKMPOPRINT.cf_addressformula(ph.ORG_ID) , TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) , TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) , TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) , TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_task_noformula(pda.TASK_ID) , TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) , TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) , TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) , TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) , TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) , TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) , TKMPOPRINT.cf_buyerformula(ph.AGENT_ID), TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID),ph.note_to_vendor , TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id), TKMpoprint.CF_Created_byFormula(ph.po_header_id) UNION SELECT ph.segment1 po_number, decode(ph.authorization_status,'APPROVED',NULL,'Draft') watermark, TO_CHAR(ph.REVISED_DATE,'DD-MM-YYYY') revised_date,ph.note_to_vendor NTV, ph.revision_num rev_num, ph.po_header_id hea_id, null line_num, DECODE(ph.attribute_category,'UK Procurement',ph.attribute1,null) NOTE_TO_VENDOR, pl.note_to_vendor pl_note_to_vendor, TRUNC (ph.creation_date) po_date, NVL (:p_print_price, 'Y') print_price, pa.segment1 job_number, pv.vendor_name, pv.segment1 vendor_no, pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name territory, --pvs.country, --decode(pl.line_type_id,4,pl.line_num,null) po_line_num, pl.line_num po_line_num, pl.cancel_flag, decode(pl.line_type_id,4,papf.full_name,null) contractor_name, pvs.state, hr2.location_code "Destination", pl.unit_meas_lookup_code "UOM", pl.quantity, pl.unit_price, --(pl.quantity * pl.unit_price) po_value, DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*pl.unit_price) po_value, 1 plunitprice, pl.item_description "Description", ph.attribute1 "Inquiry Num", ph.attribute9 "Checked By", NULL requisition_raised_by, ph.currency_code po_currency, pl.supplier_ref_number, pvs.phone, pvs.fax, hr.address_line_1, hr.address_line_2, hr.address_line_3, --ft.nls_territory country, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY') need_by_date, hou.name, hr2.address_line_1, hr2.address_line_2, hr2.address_line_3, hr2.country, hr2.region_1, hr.location_code || ',' || plc1.description || ',' || plc2.description || ',' || plc3.description || ',' || ph.fob_lookup_code "Delivery POint", hou.organization_id org_id, NVL(pl.contract_id,pl.from_header_id) contract_id, hou.location_id, ph.agent_id, pl.vendor_product_num, pl.item_id, pll.ship_to_organization_id, ph.bill_to_location_id, pda.req_distribution_id, prl.requisition_line_id req_line_id, prl.attribute4, prl.line_num CP_req_line_num, pda.task_id, pda.expenditure_type, pl.po_line_id p_line_id, CASE WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 END EQUIPMENT_ID,pl.from_header_id, NULL linetext, TKMPOPRINT.cf_addressformula(ph.ORG_ID) CF_address, TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) CF_VAT, TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) CF_EUN, TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) CF_invoice_to, TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) CF_doc_id_exist, TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) CF_Approver, TKMPOPRINT.cf_task_noformula(pda.TASK_ID) CF_task_no, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) CF_repair_item, TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) CF_req_no, TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) CF_item_num, TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) CF_final_destinetion, TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) CF_del_add, TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) CF_contract_no, TKMPOPRINT.cf_buyerformula(ph.AGENT_ID) CF_buyer, TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID) CF_HDR_ATTH, TKMpoprint.CF_HEADER_ATT1Formula(ph.PO_HEADER_ID) CF_HDR_ATTH1, TKMpoprint.CF_HEADER_ATT2Formula(ph.PO_HEADER_ID) CF_HDR_ATTH2, TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id) CF_AMOUNT_IN_WORDS, TKMpoprint.CF_Created_byFormula(ph.po_header_id) CF_CREATED_BY FROM apps.po_headers_archive_all ph, apps.po_distributions_archive_all pda, apps.po_lines_archive_all pl, (SELECT attribute10, po_header_id, revision_num FROM apps.po_headers_all WHERE po_header_id = :p_header_id) poh, apps.pa_projects_all pa, apps.po_vendors pv, apps.po_vendor_sites_all pvs, apps.hr_locations hr, apps.po_line_locations_all pll, apps.fnd_territories_vl ft, apps.hr_locations hr2, apps.po_lookup_codes plc1, apps.po_lookup_codes plc2, apps.po_lookup_codes plc3, apps.ap_terms ats, apps.hr_organization_units hou, apps.hr_operating_units ho, apps.po_req_distributions_all prd, apps.po_requisition_lines_all prl, apps.per_all_people_f papf WHERE ph.po_header_id = pda.po_header_id AND ph.po_header_id = pl.po_header_id(+) --and nvl(ph.cancel_flag,'N')!='Y' --and nvl(pl.cancel_flag,'N')!='Y' AND pda.po_line_id = pl.po_line_id AND (pda.project_id = pa.project_id OR (poh.attribute10 = pa.segment1 AND ph.org_id = pa.org_id)) AND poh.po_header_id = ph.po_header_id AND pvs.org_id = ph.org_id AND pv.vendor_id = ph.vendor_id AND pl.po_line_id = pll.po_line_id and pl.attribute14 = papf.person_id(+) AND ph.vendor_site_id = pvs.vendor_site_id --AND hr.location_id = pvs.ship_to_location_id --AND hr2.location_id = pvs.ship_to_location_id AND hr.location_id = ph.ship_to_location_id AND hr2.location_id = ph.ship_to_location_id AND pll.line_location_id = pda.line_location_id AND ft.territory_code(+) = pvs.country AND ph.terms_id(+) = ats.term_id AND ph.org_id = hou.organization_id and ph.org_id = ho.organization_id AND plc1.lookup_type(+) = 'FOB' AND plc1.lookup_code(+) = ph.fob_lookup_code AND plc2.lookup_type(+) = 'FREIGHT TERMS' AND plc2.lookup_code(+) = ph.freight_terms_lookup_code AND plc3.lookup_type(+) = 'PAY ON CODE' AND plc3.lookup_code(+) = ph.pay_on_code AND ph.org_id = pl.org_id AND ph.org_id = pda.org_id AND ph.type_lookup_code IN ('STANDARD', 'CONTRACT', 'PLANNED', 'BLANKET') AND pda.req_distribution_id=prd.distribution_id(+) AND prd.requisition_line_id=prl.requisition_line_id(+) AND (prl.attribute7<>'RENTALS' OR prl.attribute7 IS NULL) AND pa.project_id = NVL (:p_project_id, pa.project_id) AND pv.vendor_id = NVL (:p_vendor_id, pv.vendor_id) AND ph.po_header_id = NVL (:p_header_id, ph.po_header_id) AND ph.revision_num = NVL (:p_rev_num, poh.revision_num) AND ph.revision_num = :p_rev_num AND ph.revision_num <> poh.revision_num -- AND pl.revision_num <> poh.revision_num AND ph.revision_num = pl.revision_num and ph.revision_num = pda.revision_num GROUP BY ph.segment1,ph.authorization_status ,ph.REVISED_DATE,ph.revision_num,TRUNC (ph.creation_date),pa.segment1,pl.line_num,pv.vendor_name,pv.segment1, ph.attribute_category,pl.note_to_vendor,pl.cancel_flag, pvs.address_line1,pvs.address_line2,pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name, --pvs.country, pl.line_type_id, papf.full_name, pvs.state,hr2.location_code, pl.unit_meas_lookup_code,pl.quantity,pl.unit_price,--(pl.quantity * pl.unit_price), DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*pl.unit_price), pl.item_description,pl.latest_external_flag,ph.attribute1,ph.attribute9,ph.currency_code,pl.supplier_ref_number, pvs.phone,pvs.fax,hr.address_line_1,hr.address_line_2,hr.address_line_3, --ft.nls_territory, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY'), hou.name,hr2.address_line_1,hr2.address_line_2,hr2.address_line_3,hr2.country,hr2.region_1, hr.location_code|| ','|| plc1.description|| ','|| plc2.description|| ','|| plc3.description|| ',' || ph.fob_lookup_code,ph.po_header_id,hou.organization_id,NVL(pl.contract_id,pl.from_header_id),hou.location_id,ph.agent_id,pl.vendor_product_num, pl.item_id,pll.ship_to_organization_id,ph.bill_to_location_id,pda.req_distribution_id,prl.requisition_line_id, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID), TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID), prl.attribute4,pda.task_id,pda.expenditure_type ,pl.po_line_id,ph.ORG_ID,prl.line_num, -- TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type), CASE WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 END,pl.from_header_id, TKMPOPRINT.cf_addressformula(ph.ORG_ID) , TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) , TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) , TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) , TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_task_noformula(pda.TASK_ID) , TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) , TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) , TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) , TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) , TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) , TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) , TKMPOPRINT.cf_buyerformula(ph.AGENT_ID), TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID),ph.note_to_vendor, TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id), TKMpoprint.CF_Created_byFormula(ph.po_header_id) UNION SELECT ph.segment1 po_number, decode(ph.authorization_status,'APPROVED',NULL,'Draft') watermark, TO_CHAR(ph.REVISED_DATE,'DD-MM-YYYY') revised_date,ph.note_to_vendor NTV, ph.revision_num rev_num, ph.po_header_id hea_id, null line_num, DECODE(ph.attribute_category,'UK Procurement',ph.attribute1,null) NOTE_TO_VENDOR, NULL pl_note_to_vendor, TRUNC (ph.creation_date) po_date, NVL (:p_print_price, 'Y') print_price, pa.segment1 job_number, pv.vendor_name, pv.segment1 vendor_no, pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name territory, --pvs.country, null po_line_num, null cancel_flag, null contractor_name, pvs.state, hr2.location_code "Destination", pl.unit_meas_lookup_code "UOM", sum(pl.quantity), --to_number(prl.attribute10) unit_price, DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price) unit_price, --(pl.quantity * pl.unit_price) po_value, --SUM(DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*prl.attribute10)) po_value, SUM(DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*pl.unit_price)) PO_VALUE, pl.unit_price plunitprice, pl.item_description "Description", ph.attribute1 "Inquiry Num", ph.attribute9 "Checked By", NULL requisition_raised_by, ph.currency_code po_currency, pl.supplier_ref_number, pvs.phone, pvs.fax, hr.address_line_1, hr.address_line_2, hr.address_line_3, --ft.nls_territory country, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY') need_by_date, hou.name, hr2.address_line_1, hr2.address_line_2, hr2.address_line_3, hr2.country, hr2.region_1, ( hr.location_code || ',' || plc1.description || ',' || plc2.description || ',' || plc3.description || ',' || ph.fob_lookup_code) "Delivery POint", ph.org_id, NVL(pl.contract_id,pl.from_header_id) contract_id, hou.location_id, ph.agent_id, pl.vendor_product_num, pl.item_id, pll.ship_to_organization_id, ph.bill_to_location_id, NULL, NULL, prl.attribute4, --prl.line_num CP_req_line_num, null CP_req_line_num, pda.task_id, pda.expenditure_type, NULL, -- CASE -- WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 -- WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 -- END --EQUIPMENT_ID, null EQUIPMENT_ID, pl.from_header_id, TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')) linetext, -- TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)) linetext, -- line_text_test(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')) linetext, TKMPOPRINT.cf_addressformula(ph.ORG_ID) CF_address, TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) CF_VAT, TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) CF_EUN, TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) CF_invoice_to, TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) CF_doc_id_exist, TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) CF_Approver, TKMPOPRINT.cf_task_noformula(pda.TASK_ID) CF_task_no, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) CF_repair_item, --TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) CF_req_no, null CF_req_no, TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) CF_item_num, TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) CF_final_destinetion, TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) CF_del_add, TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) CF_contract_no, TKMPOPRINT.cf_buyerformula(ph.AGENT_ID) CF_buyer, TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID) CF_HDR_ATTH, TKMpoprint.CF_HEADER_ATT1Formula(ph.PO_HEADER_ID) CF_HDR_ATTH1, TKMpoprint.CF_HEADER_ATT2Formula(ph.PO_HEADER_ID) CF_HDR_ATTH2, TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id) CF_AMOUNT_IN_WORDS, TKMpoprint.CF_Created_byFormula(ph.po_header_id) CF_CREATED_BY FROM apps.po_headers_all ph, apps.po_distributions_all pda, apps.po_lines_all pl, apps.pa_projects_all pa, apps.po_vendors pv, apps.po_vendor_sites_all pvs, apps.hr_locations hr, apps.po_line_locations_all pll, apps.fnd_territories_vl ft, apps.hr_locations hr2, apps.po_lookup_codes plc1, apps.po_lookup_codes plc2, apps.po_lookup_codes plc3, apps.ap_terms ats, apps.hr_organization_units hou, apps.hr_operating_units ho, apps.po_req_distributions_all prd, apps.po_requisition_lines_all prl WHERE 1=1 AND ph.po_header_id = pda.po_header_id AND ph.po_header_id = pl.po_header_id --and nvl(ph.cancel_flag,'N')!='Y' --and nvl(pl.cancel_flag,'N')!='Y' AND pda.po_line_id = pl.po_line_id AND (pda.project_id = pa.project_id OR (ph.attribute10 = pa.segment1 AND ph.org_id = pa.org_id)) AND pvs.org_id = ph.org_id AND pv.vendor_id = ph.vendor_id AND pl.po_line_id = pll.po_line_id AND ph.vendor_site_id = pvs.vendor_site_id --AND hr.location_id = pvs.ship_to_location_id --AND hr2.location_id = pvs.ship_to_location_id AND hr.location_id = ph.ship_to_location_id AND hr2.location_id = ph.ship_to_location_id AND pll.line_location_id = pda.line_location_id AND ft.territory_code(+) = pvs.country AND ph.terms_id(+) = ats.term_id AND ph.org_id = hou.organization_id and ph.org_id = ho.organization_id AND plc1.lookup_type(+) = 'FOB' AND plc1.lookup_code(+) = ph.fob_lookup_code AND plc2.lookup_type(+) = 'FREIGHT TERMS' AND plc2.lookup_code(+) = ph.freight_terms_lookup_code AND plc3.lookup_type(+) = 'PAY ON CODE' AND plc3.lookup_code(+) = ph.pay_on_code AND ph.org_id = pl.org_id AND ph.org_id = pda.org_id AND pda.req_distribution_id=prd.distribution_id(+) AND prd.requisition_line_id=prl.requisition_line_id(+) AND pa.project_id = NVL (:p_project_id, pa.project_id) AND pv.vendor_id = NVL (:p_vendor_id, pv.vendor_id) AND ph.po_header_id = NVL (:p_header_id, ph.po_header_id) AND ph.revision_num = NVL (:p_rev_num, ph.revision_num) AND prl.attribute7='RENTALS' GROUP BY ph.segment1,ph.authorization_status ,ph.REVISED_DATE,ph.revision_num,TRUNC (ph.creation_date),pa.segment1,ph.attribute_category,--pl.note_to_vendor , pv.vendor_name,pv.segment1,pvs.address_line1,pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name, --pvs.country, --pvs.country, pvs.state, hr2.location_code,pl.unit_meas_lookup_code, --(pl.quantity * pl.unit_price) pl.item_description,ph.attribute1,ph.attribute9,ph.currency_code, pl.supplier_ref_number,pvs.phone,pvs.fax,hr.address_line_1,hr.address_line_2,hr.address_line_3, --ft.nls_territory, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY'),hou.name,hr2.address_line_1,hr2.address_line_2,hr2.address_line_3, hr2.country,hr2.region_1,hr.location_code|| ','|| plc1.description|| ','|| plc2.description|| ',' || plc3.description|| ','|| ph.fob_lookup_code,ph.po_header_id,ph.org_id,NVL(pl.contract_id,pl.from_header_id),hou.location_id,ph.agent_id,pl.vendor_product_num, pl.item_id,pll.ship_to_organization_id,ph.bill_to_location_id,prl.attribute4,pda.task_id,pda.expenditure_type ,prl.attribute10,pa.project_id,pl.unit_price, -- prl.attribute14, -- prl.REQUISITION_LINE_ID, -- pda.REQ_DISTRIBUTION_ID, --prl.line_num, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID), TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID), -- TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)), -- CASE -- WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 -- WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 -- END, pl.from_header_id, TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')), -- TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)) , -- line_text_test(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')), TKMPOPRINT.cf_addressformula(ph.ORG_ID) , TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) , TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) , TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) , TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) , TKMPOPRINT.cf_task_noformula(pda.TASK_ID) , TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) , --TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) , TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) , TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) , TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) , TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) , TKMPOPRINT.cf_buyerformula(ph.AGENT_ID), TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID),ph.note_to_vendor, TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id), TKMpoprint.CF_Created_byFormula(ph.po_header_id) UNION SELECT ph.segment1 po_number, decode(ph.authorization_status,'APPROVED',NULL,'Draft') watermark, TO_CHAR(ph.REVISED_DATE,'DD-MM-YYYY') revised_date,ph.note_to_vendor NTV, ph.revision_num rev_num, ph.po_header_id hea_id, NULL line_num, DECODE(ph.attribute_category,'UK Procurement',ph.attribute1,null) NOTE_TO_VENDOR, NULL pl_note_to_vendor, TRUNC (ph.creation_date) po_date, NVL (:p_print_price, 'Y') print_price, pa.segment1 job_number, pv.vendor_name, pv.segment1 vendor_no, pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name territory, --pvs.country, null po_line_num, null cancel_flag, null contractor_name, pvs.state, hr2.location_code "Destination", pl.unit_meas_lookup_code "UOM", SUM(pl.quantity), --to_number(prl.attribute10) unit_price, DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price) unit_price, --(pl.quantity * pl.unit_price) po_value, --SUM(DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*prl.attribute10)) po_value, SUM(DECODE(pl.quantity,null,pda.amount_ordered,(pda.quantity_ordered-pda.quantity_cancelled)*pl.unit_price)) PO_VALUE, pl.unit_price plunitprice, pl.item_description "Description", ph.attribute1 "Inquiry Num", ph.attribute9 "Checked By", NULL requisition_raised_by, ph.currency_code po_currency, pl.supplier_ref_number, pvs.phone, pvs.fax, hr.address_line_1, hr.address_line_2, hr.address_line_3, --ft.nls_territory country, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY') need_by_date, hou.name, hr2.address_line_1, hr2.address_line_2, hr2.address_line_3, hr2.country, hr2.region_1, hr.location_code || ',' || plc1.description || ',' || plc2.description || ',' || plc3.description || ',' || ph.fob_lookup_code "Delivery POint", hou.organization_id org_id, NVL(pl.contract_id,pl.from_header_id) contract_id, hou.location_id, ph.agent_id, pl.vendor_product_num, pl.item_id, pll.ship_to_organization_id, ph.bill_to_location_id, NULL, NULL, prl.attribute4, null CP_req_line_num, pda.task_id, pda.expenditure_type, NULL, -- CASE -- WHEN prl.attribute15 LIKE 'Rental%' THEN prl.attribute14 -- WHEN prl.attribute15 LIKE 'Repair%' THEN prl.attribute7 -- END -- EQUIPMENT_ID, null EQUIPMENT_ID, pl.from_header_id, TKMPOPRINT.line_text1(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,:p_rev_num,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')) linetext, -- TKMPOPRINT.line_text1(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,:p_rev_num,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)) linetext, TKMPOPRINT.cf_addressformula(ph.ORG_ID) CF_address, TKMPOPRINT.cf_vatformula(ho.default_legal_context_id) CF_VAT, TKMPOPRINT.cf_eunformula(ho.default_legal_context_id) CF_EUN, TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID) CF_invoice_to, TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID) CF_doc_id_exist, TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID) CF_Approver, TKMPOPRINT.cf_task_noformula(pda.TASK_ID) CF_task_no, TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID) CF_repair_item, --TKMPOPRINT.cf_req_noformula(pda.REQ_DISTRIBUTION_ID) CF_req_no, null CF_req_no, TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID) CF_item_num, TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4) CF_final_destinetion, TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID) CF_del_add, TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)) CF_contract_no, TKMPOPRINT.cf_buyerformula(ph.AGENT_ID) CF_buyer, TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID) CF_HDR_ATTH, TKMpoprint.CF_HEADER_ATT1Formula(ph.PO_HEADER_ID) CF_HDR_ATTH1, TKMpoprint.CF_HEADER_ATT2Formula(ph.PO_HEADER_ID) CF_HDR_ATTH2, TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id) CF_AMOUNT_IN_WORDS, TKMpoprint.CF_Created_byFormula(ph.po_header_id) CF_CREATED_BY FROM apps.po_headers_archive_all ph, apps.po_distributions_archive_all pda, apps.po_lines_archive_all pl, (SELECT attribute10, po_header_id, revision_num FROM apps.po_headers_all WHERE po_header_id = :p_header_id) poh, apps.pa_projects_all pa, apps.po_vendors pv, apps.po_vendor_sites_all pvs, apps.hr_locations hr, apps.po_line_locations_archive_all pll, apps.fnd_territories_vl ft, apps.hr_locations hr2, apps.po_lookup_codes plc1, apps.po_lookup_codes plc2, apps.po_lookup_codes plc3, apps.ap_terms ats, apps.hr_organization_units hou, apps.hr_operating_units ho, apps.po_req_distributions_all prd, apps.po_requisition_lines_all prl WHERE ph.po_header_id = pda.po_header_id AND ph.po_header_id = pl.po_header_id --and nvl(ph.cancel_flag,'N')!='Y' --and nvl(pl.cancel_flag,'N')!='Y' AND pda.po_line_id = pl.po_line_id AND (pda.project_id = pa.project_id OR (poh.attribute10 = pa.segment1 AND ph.org_id = pa.org_id)) AND poh.po_header_id = ph.po_header_id AND pvs.org_id = ph.org_id AND pv.vendor_id = ph.vendor_id AND pl.po_line_id = pll.po_line_id AND ph.vendor_site_id = pvs.vendor_site_id --AND hr.location_id = pvs.ship_to_location_id --AND hr2.location_id = pvs.ship_to_location_id AND hr.location_id = ph.ship_to_location_id AND hr2.location_id = ph.ship_to_location_id AND pll.line_location_id = pda.line_location_id AND ft.territory_code(+) = pvs.country AND ph.terms_id(+) = ats.term_id AND ph.org_id = hou.organization_id and ph.org_id = ho.organization_id AND plc1.lookup_type(+) = 'FOB' AND plc1.lookup_code(+) = ph.fob_lookup_code AND plc2.lookup_type(+) = 'FREIGHT TERMS' AND plc2.lookup_code(+) = ph.freight_terms_lookup_code AND plc3.lookup_type(+) = 'PAY ON CODE' AND plc3.lookup_code(+) = ph.pay_on_code AND ph.org_id = pl.org_id AND ph.org_id = pda.org_id AND ph.type_lookup_code IN ('STANDARD', 'CONTRACT', 'PLANNED', 'BLANKET') AND pda.req_distribution_id=prd.distribution_id(+) AND prd.requisition_line_id=prl.requisition_line_id(+) AND pa.project_id = NVL (:p_project_id, pa.project_id) AND pv.vendor_id = NVL (:p_vendor_id, pv.vendor_id) AND ph.po_header_id = NVL (:p_header_id, ph.po_header_id) --AND ph.revision_num = NVL (:p_rev_num, poh.revision_num) AND ph.revision_num = :p_rev_num AND pl.revision_num = :p_rev_num and pda.revision_num = :p_rev_num AND ph.revision_num <> poh.revision_num AND pl.revision_num <> poh.revision_num AND ph.revision_num = pl.revision_num AND prl.attribute7='RENTALS' GROUP BY ph.segment1, ph.authorization_status, ph.REVISED_DATE, ph.note_to_vendor , ph.revision_num, ph.po_header_id, TRUNC (ph.creation_date), NVL (:p_print_price, 'Y'), pa.segment1,--pl.note_to_vendor , pv.vendor_name, pv.segment1, ph.attribute_category, pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.address_line4, pvs.zip, ft.territory_short_name, --pvs.country, pvs.state, hr2.location_code, pl.unit_meas_lookup_code, DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price), pl.unit_price, pl.item_description, ph.attribute1, ph.attribute9, ph.currency_code, pl.supplier_ref_number, pvs.phone, pvs.fax, hr.address_line_1, hr.address_line_2, hr.address_line_3, --ft.nls_territory, to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'DD-MM-YYYY'), hou.name, hr2.address_line_1, hr2.address_line_2, hr2.address_line_3, hr2.country, hr2.region_1, hr.location_code || ',' || plc1.description || ',' || plc2.description || ',' || plc3.description || ',' || ph.fob_lookup_code, hou.organization_id, NVL(pl.contract_id,pl.from_header_id), hou.location_id, ph.agent_id, pl.vendor_product_num, pl.item_id, pll.ship_to_organization_id, ph.bill_to_location_id, prl.attribute4, pda.task_id, pda.expenditure_type, pl.from_header_id, --TKMPOPRINT.line_text(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)), -- TKMPOPRINT.LINE_TEXT1(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,:p_rev_num,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price)), TKMPOPRINT.line_text1(ph.po_header_id,pl.item_id,pa.project_id,pda.task_id,pda.expenditure_type,:p_rev_num,DECODE(pl.unit_price,0,to_number(prl.attribute10),pl.unit_price),to_char(decode(pl.line_type_id,4,pl.start_date,pll.need_by_date),'dd-mm-yyyy')) , TKMPOPRINT.cf_addressformula(ph.ORG_ID), TKMPOPRINT.cf_vatformula(ho.default_legal_context_id), TKMPOPRINT.cf_eunformula(ho.default_legal_context_id), TKMPOPRINT.cf_invoice_toformula(ph.BILL_TO_LOCATION_ID), TKMPOPRINT.cf_doc_id_existformula(ph.PO_HEADER_ID), TKMPOPRINT.cf_approverformula(ph.PO_HEADER_ID), TKMPOPRINT.cf_task_noformula(pda.TASK_ID), TKMPOPRINT.cf_repair_itemformula(prl.REQUISITION_LINE_ID), TKMPOPRINT.cf_item_numformula(pl.ITEM_ID, pll.SHIP_TO_ORGANIZATION_ID), TKMPOPRINT.cf_final_destinetionformula(prl.ATTRIBUTE4), TKMPOPRINT.cf_del_toformula(pH.SHIP_TO_LOCATION_ID), TKMPOPRINT.cf_contract_noformula(NVL(pl.contract_id,pl.from_header_id)), TKMPOPRINT.cf_buyerformula(ph.AGENT_ID), TKMpoprint.CF_HEADER_ATTFormula(ph.PO_HEADER_ID), TKMpoprint.CF_POPRINT_AMOUNTFormula(ph.po_header_id), TKMpoprint.CF_Created_byFormula(ph.po_header_id) order by po_line_num,CP_req_line_num,linetext ]]> ',CHR (10)),'

','') ,'',NULL),'',NULL),'',NULL),'',NULL),'

',NULL),'

',NULL),'

',NULL),' ',NULL),'

',NULL),'

',NULL),'

',NULL),'',NULL),'',NULL),'&','&') article_text ,kart.document_id FROM APPS.okc_articles_all aa ,APPS.okc_article_versions av ,APPS.okc_k_articles_b kart ,APPS.okc_terms_alternate_art_v a WHERE aa.article_id = av.article_id AND kart.sav_sae_id = aa.article_id AND kart.document_id = :hea_id and kart.article_version_id = av.ARTICLE_VERSION_ID AND kart.document_type = 'PO_STANDARD' AND nvl(kart.amendment_operation_code,'AAA')<>'DELETED' AND a.alt_article_id = kart.sav_sae_id AND a.karticles_id = kart.id and av.article_status = 'APPROVED' order by kart.display_sequence ASC ]]>