ECC Procurement, Purchase Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-pcc-orders
Query Procedure: po_pcc_orders_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Dataset Key: po-pcc-orders
Query Procedure: po_pcc_orders_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Run
ECC Procurement, Purchase Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( SELECT /*+ leading(gt) */ gt.char1 ecc_spec_id, DFV_H.*, po_header.po_header_id, po_header.segment1 order_number, po_header.revision_num po_revision, /*po_pcc_orders_util_pvt.get_po_status(po_header.po_header_id,houtl.language) po_status,*/ (SELECT plc_sta.meaning FROM fnd_lookup_values plc_sta WHERE plc_sta.lookup_code = decode(po_header.approved_flag, 'R', po_header.approved_flag,nvl(po_header.authorization_status,'INCOMPLETE')) and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE') AND plc_sta.LANGUAGE = houtl.language) || decode(po_header.closed_code, 'OPEN', '', '', '', ', '||decode(nvl(po_header.closed_code, 'OPEN'), 'OPEN', NULL, (SELECT plc_clo.meaning FROM fnd_lookup_values plc_clo WHERE plc_clo.lookup_code = nvl(po_header.closed_code, 'OPEN') and plc_clo.lookup_type = 'DOCUMENT STATE' AND plc_clo.LANGUAGE = houtl.language) )) || decode(nvl(po_header.cancel_flag, 'N'), 'N', '', '', '', ', '||decode(po_header.cancel_flag,'Y', (SELECT plc_can.meaning FROM fnd_lookup_values plc_can WHERE plc_can.lookup_code ='CANCELLED' AND plc_can.lookup_type = 'DOCUMENT STATE' AND plc_can.LANGUAGE = houtl.language ), NULL)) || decode(nvl(po_header.frozen_flag, 'N'), 'N', '', '', '', ', '||decode(po_header.frozen_flag,'Y', (SELECT plc_fro.meaning FROM fnd_lookup_values plc_fro WHERE plc_fro.lookup_code ='FROZEN' AND plc_fro.lookup_type = 'DOCUMENT STATE' AND plc_fro.LANGUAGE = houtl.language ), NULL)) || decode(nvl(po_header.user_hold_flag, 'N'), 'N', '', '', '', ', '||decode(po_header.user_hold_flag,'Y', (SELECT plc_hld.meaning FROM fnd_lookup_values plc_hld WHERE plc_hld.lookup_code ='ON HOLD' AND plc_hld.lookup_type = 'DOCUMENT STATE' AND plc_hld.LANGUAGE = houtl.language ), NULL)) || decode(Nvl(gt.char5,'N'), 'N', '', '', '', ', '||(SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'DOCUMENT STATE' AND flv.lookup_code = 'RESERVED' AND flv.LANGUAGE = houtl.language)) po_status, /*po_headers_sv3.get_po_status(po_header.po_header_id) po_status,*/ doc_style.style_name po_document_style, ( SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = po_header.agent_id AND emp.effective_start_date = ( SELECT MAX(c.effective_start_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id) AND rownum < 2 ) po_buyer_name, ap_supplier.vendor_name po_supplier_name, po_header.vendor_id po_supplier_id, ( SELECT apst.vendor_site_code FROM ap_supplier_sites_all apst WHERE apst.vendor_site_id = po_header.vendor_site_id ) po_supplier_site, ( SELECT pvc.first_name || ' ' || pvc.last_name FROM po_vendor_contacts pvc WHERE pvc.vendor_contact_id = po_header.vendor_contact_id AND pvc.vendor_site_id = po_header.vendor_site_id ) po_supplier_contact, po_header.currency_code po_currency, ( CASE WHEN po_header.closed_code = 'FINALLY CLOSED' THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_FINALLY_CLOSED' ,'201',houtl.language) WHEN po_header.closed_code = 'CLOSED' THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CLOSED','201',houtl. language) WHEN po_header.cancel_flag = 'Y' THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CANCELED','201',houtl.language ) WHEN po_header.user_hold_flag = 'Y' THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_ON_HOLD','201',houtl.language ) WHEN po_header.authorization_status IN ( 'REJECTED' ) THEN ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'AUTHORIZATION STATUS' AND flv.lookup_code = 'REJECTED' AND flv.language = houtl.language ) WHEN nvl(po_header.authorization_status,'INCOMPLETE') IN ( 'INCOMPLETE' ) THEN ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'AUTHORIZATION STATUS' AND flv.lookup_code = 'INCOMPLETE' AND flv.language = houtl.language ) WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(po_header.pending_signature_flag,'N') = 'Y' and nvl(po_header.acceptance_required_flag, 'N') <> 'P' and not exists (select 1 from PO_ACCEPTANCES where po_header_id = po_header.po_header_id and revision_num = po_header.revision_num and po_release_id is null and po_line_location_id is null and accepting_party = 'S' and accepted_flag = 'Y' and signature_flag = 'Y' ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_REQ_SIGN','201',houtl.language) WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(po_header.acceptance_required_flag,'N') IN ('Y','D') and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=po_header.po_header_id AND poa.revision_num=po_header.revision_num) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_SUPP_ACK','201',houtl.language) /* WHEN EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.po_header_id = po_header.po_header_id AND failed_funds_lookup_code like 'F%') THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_FUNDS','201',houtl.language) */ WHEN po_header.authorization_status IN ( 'PRE-APPROVED' ) THEN ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'AUTHORIZATION STATUS' AND flv.lookup_code = 'PRE-APPROVED' AND flv.language = houtl.language ) WHEN EXISTS ( SELECT 1 FROM po_change_requests WHERE document_header_id = po_header.po_header_id AND request_status = 'PENDING' ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_CHG_REQ','201',houtl.language) WHEN po_header.authorization_status IN ( 'IN PROCESS' ) THEN ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'AUTHORIZATION STATUS' AND flv.lookup_code = 'IN PROCESS' AND flv.language = houtl.language ) WHEN po_header.authorization_status = 'APPROVED' AND EXISTS ( SELECT 1 FROM po_line_locations_all plla WHERE plla.need_by_date IS NOT NULL AND plla.po_header_id = po_header.po_header_id AND nvl(plla.promised_date,plla.need_by_date) < SYSDATE AND plla.po_header_id = po_header.po_header_id AND ( nvl( (plla.quantity - plla.quantity_cancelled),0) - nvl(plla.quantity_received,0) ) > 0 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_OVERDUE_SHIP','201',houtl.language) WHEN EXISTS ( SELECT 1 FROM po_line_locations_all plla WHERE plla.po_header_id = po_header.po_header_id AND plla.quantity_rejected > 0 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_REJECTIONS','201',houtl.language) WHEN po_header.authorization_status = 'APPROVED' AND EXISTS ( SELECT 1 FROM po_lines_all pla, po_lines_all agrla, po_line_locations_all plla, po_attribute_values pav WHERE plla.po_line_id = pla.po_line_id AND pla.from_line_id = agrla.po_line_id (+) AND agrla.po_line_id = pav.po_line_id (+) AND plla.po_header_id = po_header.po_header_id AND nvl(plla.promised_date,plla.need_by_date) - SYSDATE < pav.lead_time ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_DELAY_ANTICIPATE','201',houtl.language) WHEN po_header.frozen_flag = 'Y' THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_FROZEN','201',houtl.language ) /*WHEN exists (select 1 from po_buyer_actions WHERE Trunc(Nvl(completion_date,SYSDATE ))- Trunc(TARGET_DATE)> 0 and status <> 'CLOSED' and entity_type = 'PO_HEADER' and entity_id = po_header.po_header_id) THEN 'Overdue Notes Exist' */ WHEN EXISTS( SELECT 1 FROM po_acceptances pac WHERE pac.accepted_flag = 'N' AND pac.po_header_id = po_header.po_header_id) THEN ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE lookup_type = 'AUTHORIZATION STATUS' AND lookup_code = 'REJECTED' AND flv.language = houtl.language ) ELSE ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE lookup_type = 'AUTHORIZATION STATUS' AND lookup_code = po_header.authorization_status AND flv.language = houtl.language ) END ) po_sub_status, po_header.creation_date po_creation_date, po_header.approved_date po_approved_date, po_header.submit_date po_submit_date, houtl.name po_ou_name, ( CASE WHEN EXISTS ( SELECT 1 FROM po_change_requests WHERE document_header_id = po_header.po_header_id AND request_status = 'PENDING' ) THEN 'Yes' ELSE 'No' END ) po_open_change_requests, ( SELECT apt.name FROM ap_terms apt WHERE apt.term_id = po_header.terms_id ) po_payment_terms, houtl.language, gt.num6 po_ordered_amount, gt.num7 po_received_amount, gt.num8 po_invoiced_amount, DECODE(po_header.type_lookup_code,'STANDARD', (CASE WHEN nvl(po_header.authorization_status,'INCOMPLETE') IN( 'REQUIRES REAPPROVAL' ) OR EXISTS( SELECT 1 FROM po_change_requests WHERE document_header_id = po_header.po_header_id AND request_status = 'PENDING' ) THEN 'Yes' ELSE 'No' END),NULL) orders_to_watch, po_header.comments po_description, DECODE(gt.char5,'Y', ( SELECT polc.displayed_field FROM po_lookup_codes polc WHERE polc.lookup_type = 'DOCUMENT STATE' AND polc.lookup_code = 'RESERVED' ), ( SELECT polc.displayed_field FROM po_lookup_codes polc WHERE polc.lookup_type = 'DOCUMENT STATE' AND polc.lookup_code = 'NOT RESERVED' ) ) po_reservation_status, ( CASE WHEN ( po_header.closed_code IN ( 'CLOSED', 'FINALLY CLOSED' ) OR po_header.cancel_flag LIKE 'Y' ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_CLOSED_STATUS','201' ,houtl.language) WHEN ( nvl(po_header.authorization_status,'INCOMPLETE') IN ( 'INCOMPLETE', 'REQUIRES REAPPROVAL', 'REJECTED' ) ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_DRAFT_STATUS','201',houtl.language) WHEN ( po_header.authorization_status IN ( 'IN PROCESS', 'PRE-APPROVED' ) OR ((nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(po_header.pending_signature_flag,'N') = 'Y' and nvl(po_header.acceptance_required_flag, 'N') <> 'P' and not exists (select 1 from PO_ACCEPTANCES where po_header_id = po_header.po_header_id and revision_num = po_header.revision_num and po_release_id is null and po_line_location_id is null and accepting_party = 'S' and accepted_flag = 'Y' and signature_flag = 'Y' )) or (nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(po_header.acceptance_required_flag,'N') IN ('Y','D') and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=po_header.po_header_id AND poa.revision_num=po_header.revision_num))) ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_INPROC_STATUS','201',houtl.language) ELSE po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_OPEN_STATUS','201',houtl.language) END ) po_group_status, CASE WHEN ( po_header.currency_code IS NULL OR gt.char2 = po_header.currency_code ) THEN gt.num6 ELSE nvl(po_header.rate,1) * gt.num6 END AS ordered_amount_func, CASE WHEN ( po_header.currency_code IS NULL OR gt.char2 = po_header.currency_code ) THEN gt.num7 ELSE nvl(po_header.rate,1) * gt.num7 END AS received_amount_func, CASE WHEN ( po_header.currency_code IS NULL OR gt.char2 = po_header.currency_code ) THEN gt.num8 ELSE nvl(po_header.rate,1) * gt.num8 END AS invoiced_amount_func, gt.num9 AS cycle_time, gt.char3 doc_authorized_user_ids, gt.char4 security_level_code, po_header.org_id org_id, DFV_L.*, po_line.po_line_id po_line_id, po_line.line_num po_line_num, ( SELECT line_type FROM po_line_types_tl lt WHERE lt.line_type_id = po_line.line_type_id AND lt.language = houtl.language ) po_line_type, ( SELECT mtl_sys_item.concatenated_segments FROM mtl_system_items_b_kfv mtl_sys_item WHERE nvl(nvl(mtl_sys_item.organization_id,fsp.inventory_organization_id),-99) = nvl(fsp.inventory_organization_id ,-99) AND po_line.item_id = mtl_sys_item.inventory_item_id (+) ) po_item_number, po_line.item_description po_item_description, po_mtl_cat.concatenated_segments po_item_category, po_line.quantity po_line_quantity, po_line.unit_meas_lookup_code po_uom, po_line.unit_price po_line_price, DECODE(nvl(po_line.closed_code,'OPEN'),'OPEN',po_pcc_orders_util_pvt.get_fnd_message('PO_OPEN','201',houtl.language) ,'CLOSED FOR RECEIVING',po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CLOSED','201',houtl.language),po_pcc_orders_util_pvt .get_fnd_message('PO_STATUS_CLOSED','201',houtl.language) ) po_line_status, gt.char2 po_func_currency, ( SELECT neg_header.document_number FROM pon_auction_headers_all neg_header WHERE po_line.auction_header_id = neg_header.auction_header_id ) neg_number, DECODE(po_line.matching_basis,'AMOUNT',po_line.amount, (po_line.quantity * nvl(po_line.unit_price,0) ) ) po_line_amount , po_line.note_to_vendor note_to_supplier, ( SELECT segment1 FROM po_headers_all WHERE po_header_id = nvl(po_line.from_header_id,po_line.contract_id) ) agreement, nvl(po_line.from_header_id,po_line.contract_id) source_header_id, po_line.from_line_id source_line_id, DFV_S.*, po_lineloc.line_location_id po_lineloc_id, po_lineloc.shipment_num po_shipment_num, po_lineloc.quantity po_ship_ordered_quantity, /*( SELECT location_code FROM po_ship_to_loc_org_v WHERE location_id = po_lineloc.ship_to_location_id ) po_ship_to_location,*/ (CASE WHEN po_lineloc.DROP_SHIP_FLAG = 'Y' then (select address1||'-'||city from hz_locations where location_id = po_lineloc.ship_to_location_id) else (select location_code from hr_locations_all where location_id = po_lineloc.ship_to_location_id) end) po_ship_to_location, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount,0),nvl(po_lineloc.price_override * (po_lineloc.quantity - po_lineloc.quantity_cancelled),0) ) shipment_amount, po_lineloc.quantity_received po_ship_received_quantity, po_lineloc.quantity_rejected po_ship_rejected_quantity, po_lineloc.quantity_billed po_ship_invoiced_quantity, po_lineloc.quantity_cancelled po_ship_cancelled_quantity, po_lineloc.quantity_shipped po_ship_shipped_quantity, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount,0),nvl(po_lineloc.price_override * (po_lineloc.quantity - po_lineloc.quantity_cancelled),0) ) po_ship_ordered_amount, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_shipped,0),nvl(po_lineloc.price_override * po_lineloc .quantity_shipped,0) ) po_ship_shipped_amount, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_received,0),nvl(po_lineloc.price_override * po_lineloc .quantity_received,0) ) po_ship_received_amount, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_rejected,0),nvl(po_lineloc.price_override * po_lineloc .quantity_rejected,0) ) po_ship_rejected_amount, DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_billed,0),nvl(po_lineloc.price_override * po_lineloc .quantity_billed,0) ) po_ship_invoiced_amount, po_lineloc.price_override po_unit_price, po_lineloc.need_by_date po_need_by_date, po_lineloc.promised_date po_promised_date, ( CASE WHEN ( nvl(po_lineloc.promised_date,po_lineloc.need_by_date) IS NOT NULL AND nvl(po_lineloc.promised_date,po_lineloc.need_by_date) < SYSDATE ) AND po_lineloc.approved_flag = 'Y' AND ( nvl( (po_lineloc.quantity - po_lineloc.quantity_cancelled),0) - nvl(po_lineloc.quantity_received,0) ) > 0 THEN 'Y' ELSE 'N' END ) overdue_flag, ( SELECT nvl(plla.promised_date,plla.need_by_date) FROM po_line_locations_all plla WHERE plla.line_location_id = po_lineloc.line_location_id AND ( nvl(plla.promised_date,plla.need_by_date) IS NOT NULL ) AND plla.approved_flag = 'Y' AND DECODE(plla.matching_basis,'QUANTITY', (nvl( (plla.quantity - plla.quantity_cancelled),0) - nvl(plla.quantity_received ,0) ), (nvl( (plla.amount - plla.amount_cancelled),0) - nvl(plla.amount_received,0) ) ) > 0 ) need_by_date_open_shipment, invoice_holds.invoice_on_hold invoice_on_hold, DFV_D.*, po_dist.po_distribution_id po_dist_id, po_dist.distribution_num po_dist_num, ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'DESTINATION TYPE' AND flv.lookup_code = po_dist.destination_type_code AND flv.language = houtl.language ) po_dist_destination_type, ( SELECT concatenated_segments FROM gl_code_combinations_kfv gl_code_kfv WHERE code_combination_id = po_dist.code_combination_id ) po_dist_charge_account, pa_project.name project_name, pa_project.segment1 project_number, pa_task.task_name task_name, pa_task.task_number task_number, po_dist.expenditure_type po_expenditure_type, houtl.name PO_EXPENDITURE_ORG, po_dist.expenditure_item_date po_expenditure_item_date, ( SELECT full_name FROM hr_employees hr_emp WHERE hr_emp.employee_id = po_dist.deliver_to_person_id and rownum <2 ) po_dist_requester, nvl( ( SELECT req_header.segment1 |