ECC Procurement, Agreements
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-pcc-agreements
Query Procedure: po_pcc_agreements_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Dataset Key: po-pcc-agreements
Query Procedure: po_pcc_agreements_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Run
ECC Procurement, Agreements 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.*, agr_header.po_header_id agreement, agr_header.segment1 agreement_number, agr_header.revision_num agr_revision, /* po_pcc_orders_util_pvt.get_po_status(agr_header.po_header_id,houtl.language) status, */ (SELECT plc_sta.meaning FROM fnd_lookup_values plc_sta WHERE plc_sta.lookup_code = decode(agr_header.approved_flag, 'R', agr_header.approved_flag,nvl(agr_header.authorization_status,'INCOMPLETE')) and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE') AND plc_sta.LANGUAGE = houtl.language) || decode(agr_header.closed_code, 'OPEN', '', '', '', ', '||decode(nvl(agr_header.closed_code, 'OPEN'), 'OPEN', NULL, (SELECT plc_clo.meaning FROM fnd_lookup_values plc_clo WHERE plc_clo.lookup_code = nvl(agr_header.closed_code, 'OPEN') and plc_clo.lookup_type = 'DOCUMENT STATE' AND plc_clo.LANGUAGE = houtl.language) )) || decode(nvl(agr_header.cancel_flag, 'N'), 'N', '', '', '', ', '||decode(agr_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(agr_header.frozen_flag, 'N'), 'N', '', '', '', ', '||decode(agr_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(agr_header.user_hold_flag, 'N'), 'N', '', '', '', ', '||decode(agr_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)) status, ap_supplier.vendor_name supplier, ( SELECT apst.vendor_site_code FROM ap_supplier_sites_all apst WHERE apst.vendor_site_id = agr_header.vendor_site_id ) supplier_site, ( SELECT pvc.first_name || ' ' || pvc.last_name FROM po_vendor_contacts pvc WHERE pvc.vendor_contact_id = agr_header.vendor_contact_id AND pvc.vendor_site_id = agr_header.vendor_site_id ) agr_supplier_contact, agr_header.blanket_total_amount agr_agreed_amount, agr_header.amount_limit agr_amount_limit, gt.num4 agr_amount_released, gt.char2 functional_currency_code, agr_header.currency_code currency, ( SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = agr_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 ) buyer_name, agr_header.start_date effective_from, agr_header.end_date effective_to, agr_header.creation_date creation_date, agr_header.approved_date approval_date, agr_header.submit_date submit_date, (SELECT at.name FROM AP_TERMS at WHERE at.term_id = agr_header.terms_id ) agr_payment_term, agr_header.org_id org_id, houtl.name Operating_Unit, gt.num5 as received_amount, gt.num6 as invoiced_amount, gt.num7 as shipped_amount, houtl.language, ( CASE WHEN ( nvl(agr_header.authorization_status,'INCOMPLETE') IN ( 'INCOMPLETE', 'REQUIRES REAPPROVAL', 'REJECTED' ) ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_DRAFT_STATUS', '201',houtl.language) WHEN ( agr_header.authorization_status IN ( 'IN PROCESS', 'PRE-APPROVED' ) OR ((nvl(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(agr_header.pending_signature_flag,'N') = 'Y' and nvl(agr_header.acceptance_required_flag, 'N') <> 'P' and not exists (select 1 from PO_ACCEPTANCES where po_header_id = agr_header.po_header_id and revision_num = agr_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(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(agr_header.acceptance_required_flag,'N') IN ('Y','D') and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=agr_header.po_header_id AND poa.revision_num=agr_header.revision_num))) ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_INPROC_STATUS', '201',houtl.language) WHEN ( agr_header.closed_code IN ( 'CLOSED', 'FINALLY CLOSED' ) OR agr_header.cancel_flag LIKE 'Y' /*OR agr_header.end_date <= SYSDATE OR gt.num4 = agr_header.amount_limit*/ ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_CLOSED_STATUS', '201',houtl.language) WHEN agr_header.po_header_id IS NOT NULL THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_OPEN_STATUS', '201',houtl.language) END ) GROUP_STATUS, ( CASE WHEN agr_header.closed_code = 'FINALLY CLOSED' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_FINALLY_CLOSED', '201',houtl.language) WHEN agr_header.closed_code = 'CLOSED' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CLOSED', '201',houtl.language) WHEN agr_header.cancel_flag = 'Y' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CANCELED', '201',houtl.language) WHEN agr_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(agr_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(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(agr_header.pending_signature_flag,'N') = 'Y' and nvl(agr_header.acceptance_required_flag, 'N') <> 'P' and not exists (select 1 from PO_ACCEPTANCES where po_header_id = agr_header.po_header_id and revision_num = agr_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_agreements_util_pvt.get_fnd_message ('PO_PCC_REQ_SIGN', '201',houtl.language) WHEN nvl(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(agr_header.acceptance_required_flag,'N') IN ('Y','D') and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=agr_header.po_header_id AND poa.revision_num=agr_header.revision_num) THEN po_pcc_agreements_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 = agr_header.po_header_id AND failed_funds_lookup_code LIKE 'F%' ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_PEND_FUNDS', '201',houtl.language) WHEN agr_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 agr_header.authorization_status IN ( 'IN PROCESS' ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_IN_PROCESS', '201',houtl.language) WHEN agr_header.user_hold_flag = 'Y' THEN 'On Hold' WHEN EXISTS ( SELECT 1 FROM po_change_requests WHERE document_header_id = agr_header.po_header_id AND request_status = 'PENDING' ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_PEND_CHG_REQ', '201',houtl.language) WHEN agr_header.end_date <= SYSDATE THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_END_DATE_REACHED', '201',houtl.language) WHEN gt.num4 = agr_header.amount_limit THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_AMT_LIMIT_REACHED', '201',houtl.language) WHEN SYSDATE >= agr_header.end_date - 30 THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_APPROACH_END_DATE', '201',houtl.language) WHEN gt.num4 >= ( agr_header.amount_limit * 0.9 ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_APPROACH_AMT_LIMIT', '201',houtl.language) WHEN agr_header.frozen_flag = 'Y' THEN po_pcc_agreements_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 = 'PA_HEADER' AND entity_id = agr_header.po_header_id ) THEN 'Overdue Notes Exist'*/ ELSE ( SELECT flv.meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'AUTHORIZATION STATUS' AND flv.lookup_code = agr_header.authorization_status AND flv.language= houtl.language ) END ) SUB_STATUS, /*( SELECT next_action_type FROM ( SELECT fnd_look.meaning next_action_type, entity_id FROM po_buyer_actions pba, fnd_lookup_values fnd_look WHERE entity_type = 'PA_HEADER' AND status = 'OPEN' AND fnd_look.lookup_type = 'PO_BUYER_PA_HEADER_ACTIONS' AND fnd_look.lookup_code = pba.action_type and fnd_look.LANGUAGE= houtl.language ORDER BY target_date ) WHERE entity_id = agr_header.po_header_id AND ROWNUM = 1 )*/ null as NEXT_ACTION_TYPE, DECODE(gt.char5,'Y', ( SELECT polc.meaning FROM fnd_lookup_values polc WHERE polc.lookup_type = 'DOCUMENT STATE' AND polc.lookup_code = 'RESERVED' AND polc.language= houtl.language ), ( SELECT polc.meaning FROM fnd_lookup_values polc WHERE polc.lookup_type = 'DOCUMENT STATE' AND polc.lookup_code = 'NOT RESERVED' AND polc.language= houtl.language ) ) reservation_status, extract(YEAR from agr_header.creation_date) CREATION_YEAR, to_char(agr_header.creation_date,'Month') creation_month, (CASE WHEN agr_header.authorization_status = 'APPROVED' AND Nvl(agr_header.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND Nvl(agr_header.cancel_flag, 'N') = 'N' AND Nvl(agr_header.frozen_flag, 'N') = 'N' AND ((gt.num4 >= ( agr_header.amount_limit * 0.9 )) ) OR (gt.num4 = agr_header.amount_limit) THEN 'Yes' ELSE 'No' END ) AGREEMENTS_TO_WATCH, ( CASE WHEN agr_header.authorization_status = 'APPROVED' AND Nvl(agr_header.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND Nvl(agr_header.cancel_flag, 'N') = 'N' AND Nvl(agr_header.frozen_flag, 'N') = 'N' AND ((SYSDATE >= agr_header.end_date - 30) OR (agr_header.end_date <= SYSDATE)) THEN 'Yes' ELSE 'No' END ) as EXPIRING_AGREEMENTS, doc_style.style_name AGR_STYLE , CASE WHEN (agr_header.currency_code IS NULL OR gt.char2 = agr_header.currency_code) THEN agr_header.blanket_total_amount ELSE nvl(agr_header.rate,1)*agr_header.blanket_total_amount END AS amount_agreed_func, CASE WHEN ( agr_header.currency_code IS NULL OR gt.char2 = agr_header.currency_code) THEN gt.num4 ELSE nvl(agr_header.rate,1)*gt.num4 END AS released_amount_func, agr_header.authorization_status, decode(agr_header.type_lookup_code,'CONTRACT',po_pcc_agreements_util_pvt.get_fnd_message ('PO_CONTRACT_AGREEMENT', '201',houtl.language), po_pcc_agreements_util_pvt.get_fnd_message ('PO_BLANKET_PO', '201',houtl.language)) document_type, case when agr_header.blanket_total_amount is not null and agr_header.blanket_total_amount >0 then (gt.num4/agr_header.blanket_total_amount)*100 else null end as utilization_percentage, gt.char3 DOC_AUTHORIZED_USER_IDS , GT.CHAR4 SECURITY_LEVEL_CODE, agr_header.type_lookup_code, agr_header.vendor_id supplier_id, DFV_L.*, agr_line.po_line_id agreement_line, agr_line.line_num line_number, ( SELECT line_type FROM po_line_types_tl lt WHERE lt.line_type_id = agr_line.line_type_id AND lt.language = houtl.language ) line_type, mtl_sys_item.concatenated_segments item, agr_line.item_description, mtl_cat_kfv.concatenated_segments category, agr_line.unit_meas_lookup_code uom, agr_line.unit_price unit_price, DECODE( NVL(agr_line.closed_code, 'OPEN'), 'OPEN', po_pcc_agreements_util_pvt.get_fnd_message('PO_OPEN', '201',houtl.language), 'CLOSED FOR RECEIVING', po_pcc_agreements_util_pvt.get_fnd_message('PO_STATUS_CLOSED', '201',houtl.language), po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CLOSED', '201',houtl.language)) line_status , agr_attr_values.lead_time lead_time, (SELECT Count(1) FROM po_line_locations_all pll WHERE pll.po_line_id = agr_line.po_line_id ) no_price_breaks, (SELECT document_number FROM pon_auction_headers_all paha WHERE paha.auction_header_id= agr_line.auction_header_id) negotiation, /* (SELECT LISTAGG(poh.segment1,'|') WITHIN GROUP (ORDER BY nvl(pla.from_header_id,pla.contract_id)) AS order_num FROM po_lines_all pla, po_headers_all poh WHERE nvl(pla.from_header_id,pla.contract_id) = agr_header.po_header_id AND poh.po_header_id=pla.po_header_id GROUP BY nvl(pla.from_header_id,pla.contract_id))*/ null AS ORDER_num, po_order_lines.Released_Quantity, po_order_lines.Shipped_Quantity, po_order_lines.Received_Quantity, po_order_lines.Rejected_Quantity, po_order_lines.Invoiced_Quantity, po_line_rel_amt.line_released_amount , po_order_lines.line_received_amount, po_order_lines.line_invoiced_amount, po_order_lines.line_shipped_amount, ( SELECT DECODE(flv.lookup_code,'APPROVED', (CASE WHEN agr_header.closed_code = 'CLOSED' THEN 'PO_STATUS_CLOSED' WHEN agr_header.closed_code = 'FINALLY CLOSED' THEN 'PO_STATUS_FINALLY_CLOSED' WHEN nvl(agr_header.cancel_flag,'N') = 'Y' THEN 'PO_STATUS_CANCELED' WHEN nvl(agr_header.frozen_flag,'N') = 'Y' THEN 'PO_STATUS_FROZEN' WHEN nvl(agr_header.user_hold_flag,'N') = 'Y' THEN 'PO_STATUS_ON_HOLD' ELSE flv.lookup_code END),flv.lookup_code) FROM fnd_lookup_values_vl flv WHERE nvl(agr_header.authorization_status,'INCOMPLETE') = flv.lookup_code AND flv.lookup_type = 'AUTHORIZATION STATUS' AND agr_header.po_header_id IS NOT NULL ) agreement_status_code, agr_line.vendor_product_num SUPPLIER_ITEM_NUM, deliverable.deliverable_id okc_deliverable_id , deliverable.deliverable_name okc_deliverable_name, (SELECT deliverabletypes_tl.name FROM okc_deliverable_types_tl deliverabletypes_tl WHERE deliverabletypes_tl.deliverable_type_code=deliverable.deliverable_type AND deliverabletypes_tl.LANGUAGE = houtl.LANGUAGE) okc_deliverable_type, (SELECT busdoc_tl.name FROM okc_bus_doc_types_tl busdoc_tl WHERE deliverable.business_document_type = busdoc_tl.document_type AND busdoc_tl.language = houtl.LANGUAGE) AS okc_business_document_type, deliverable.business_document_type okc_doc_type_code, deliverable.business_document_number okc_business_doc_number, (select name from okc_resp_parties_vl okc_resp_party where okc_resp_party.resp_party_code=deliverable.responsible_party and okc_resp_party.document_type_class = 'PO') responsible_party, (CASE WHEN deliverable.responsible_party = 'INTERNAL_ORG' THEN (SELECT EMPLOYEE.FULL_NAME CONTACT_NAME FROM PER_ALL_PEOPLE_F EMPLOYEE WHERE EMPLOYEE.EFFECTIVE_START_DATE = EMPLOYEE.START_DATE AND EMPLOYEE.PERSON_ID = deliverable.INTERNAL_PARTY_CONTACT_ID AND ROWNUM <2) ELSE (select hpcontact.party_name FROM hz_parties hpcontact WHERE deliverable.external_party_contact_id = hpcontact.party_id) END ) okc_party_contact, deliverable.actual_due_date due_date, (select meaning from fnd_lookup_values fnd_lookup where fnd_lookup.lookup_code = deliverable.deliverable_status and fnd_lookup.lookup_type = 'OKC_DELIVERABLE_STATUS' AND fnd_lookup.language = houtl.language ) deliverable_status, DECODE(nvl(agr_header.cancel_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',houtl.language), PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',houtl.language)) CANCEL_FLAG, DECODE(nvl(agr_header.acceptance_required_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',houtl.language), PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',houtl.language)) acceptance_required_flag FROM po_headers_all agr_header, po_doc_style_headers doc_style, ap_suppliers ap_supplier, hr_all_organization_units_tl houtl, po_session_gt gt, ( select "ROW_ID" "'PO_ORD_H_ROW_ID'","CONTEXT" "'PO_ORD_H_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_ORD_H_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_HEADERS_ALL ) ) DFV_H, po_lines_all agr_line, po_attribute_values agr_attr_values, mtl_categories_kfv mtl_cat_kfv, mtl_system_items_b_kfv mtl_sys_item, financials_system_params_all fsp, ( select "ROW_ID" "'PO_ORD_L_ROW_ID'","CONTEXT" "'PO_ORD_L_CONTEXT'","ATTRIBUTE_13" "'PO_ORD_L_ATTRIBUTE_13'","ATTRIBUTE_3" "'PO_ORD_L_ATTRIBUTE_3'","ORDER_SEQUENCE" "'PO_ORD_L_ORDER_SEQUENCE'","CONCATENATED_SEGMENTS" "'PO_ORD_L_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",ATTRIBUTE13 "ATTRIBUTE_13",ATTRIBUTE3 "ATTRIBUTE_3",(DECODE(ATTRIBUTE_CATEGORY,'2052',ATTRIBUTE1,NULL)) "ORDER_SEQUENCE",ATTRIBUTE13||'.'||ATTRIBUTE3||'.'||(DECODE(ATTRIBUTE_CATEGORY,'2052',ATTRIBUTE_CATEGORY||'.'||ATTRIBUTE1,NULL)) "CONCATENATED_SEGMENTS" from PO_LINES_ALL ) ) DFV_L, okc_deliverables deliverable, (SELECT po_line.from_line_id AS order_from_line_id, Sum(plla.quantity) Released_Quantity, Sum(plla.quantity_shipped) Shipped_Quantity, Sum(plla.quantity_received) Received_Quantity, Sum(plla.quantity_rejected) Rejected_Quantity, Sum(plla.quantity_billed ) Invoiced_Quantity, Sum(DECODE(plla.matching_basis,'AMOUNT',nvl(plla.amount_received, 0), Nvl(po_line.unit_price*plla.quantity_received, 0))) line_received_amount, Sum(DECODE(plla.matching_basis,'AMOUNT',nvl(plla.amount_billed, 0), Nvl(po_line.unit_price*plla.quantity_billed, 0))) line_invoiced_amount, Sum(DECODE(plla.matching_basis,'AMOUNT',nvl(plla.amount_shipped, 0), Nvl(po_line.unit_price*plla.quantity_shipped, 0))) line_shipped_amount FROM po_lines_all po_line, po_line_locations_all plla WHERE po_line.creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS') ,po_line.creation_date) and plla.po_line_id=po_line.po_line_id AND Nvl(plla.consigned_flag,'N') <> 'Y' GROUP BY po_line.from_line_id ) po_order_lines, (SELECT pol.from_line_id, sum ( |