ECC Genealogy and Trace, Purchase Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: gnt-potransactions
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Dataset Key: gnt-potransactions
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
Run
ECC Genealogy and Trace, Purchase Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( select ecc_spec_id, ponumber, po_header_id, vendor_name, vendor_site_code, org_id, po_qa_results, oucode, lotgenobjid, sergenobjid, txnorgid, transaction_date, transaction_quantity, transaction_uom, transaction_action_id, transaction_type_id, revision, serial_transaction_id, transaction_type_name, comments, revision_num, type_lookup_code, buyer, currency_code, po_contact, po_status, language, node_type, po_amount, security_level_code, user_hold_flag, CASE WHEN security_level_code='PUBLIC' OR SECURITY_LEVEL_CODE='PURCHASING' THEN NULL ELSE po_pcc_agreements_util_pvt.get_doc_authorized_userIds('POPA', TYPE_LOOKUP_CODE, AUTHORIZATION_STATUS, org_id, AGENT_ID, PO_HEADER_ID) END AS doc_authorized_user_ids from ( SELECT /*+ leading(pt) no_merge(pt) */ to_char(pt.dataset_pk_id||'-'||pt.lotgenobjid||'-'||pt.sergenobjid) ecc_spec_id, pha.segment1 ponumber, pt.po_id po_header_id, aps.vendor_name, apss.vendor_site_code, pha.org_id Org_id, /* action_lkp.meaning */ 'QA' PO_QA_RESULTS, /* PO_PCC_AGREEMENTS_UTIL_PVT.get_doc_authorized_userIds ('POPA',pha.TYPE_LOOKUP_CODE, pha.AUTHORIZATION_STATUS, pha.org_id, pha.AGENT_ID, pha.PO_HEADER_ID) DOC_AUTHORIZED_USER_IDS, */ h.name oucode, pt.lotgenobjid, pt.sergenobjid, pt.serorgid txnorgid, pt.transaction_date, pt.transaction_quantity, pt.transaction_uom, pt.transaction_action_id, pt.transaction_type_id, pt.revision, pt.serial_transaction_id, (SELECT transaction_type_name FROM mtl_transaction_types mtt WHERE mtt.transaction_type_id = pt.transaction_type_id) transaction_type_name, pha.comments, pha.revision_num, type_lookup_code, (SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = pha.agent_id AND (( emp.effective_end_date = ( SELECT MAX(c.effective_end_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id ) )) ) Buyer, currency_code, hp.person_last_name|| ', '|| hp.person_first_name po_contact, lkp2.meaning|| ', '|| lkp1.meaning po_status, lkp1.language language, nodetype_lkp.meaning node_type, (SELECT SUM(round(decode(pll.quantity, NULL,(pll.amount - nvl(pll.amount_cancelled, 0)),(pll.quantity - nvl(pll.quantity_cancelled , 0)) * nvl(pll.price_override, 0)) / nvl(fc.minimum_accountable_unit, 1), decode(fc.minimum_accountable_unit, NULL, fc .precision, 0)) * nvl(fc.minimum_accountable_unit, 1)) FROM po_line_locations_all pll, fnd_currencies fc WHERE pll.po_header_id = pha.po_header_id AND fc.currency_code = pha.currency_code AND ( pll.shipment_type IN ('STANDARD','PLANNED') OR ( pll.shipment_type = 'BLANKET' AND nvl(pll.consigned_flag, 'N') <> 'Y' )) ) po_amount, ( select pdt.SECURITY_LEVEL_CODE from PO_DOCUMENT_TYPES_ALL pdt where pdt.org_id = pha.org_id AND pdt.DOCUMENT_TYPE_CODE in ('PO','PA') AND pdt.DOCUMENT_SUBTYPE = pha.TYPE_LOOKUP_CODE ) SECURITY_LEVEL_CODE, decode(pha.user_hold_flag,'Y','FALSE','TRUE') user_hold_flag, pha.AGENT_ID, pha.authorization_status FROM po_headers_all pha, ap_suppliers aps, ap_supplier_sites_all apss, ap_supplier_contacts ascon, hz_parties hp, ( SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mtln.transaction_source_id po_id, (select mln.gen_object_id from mtl_lot_numbers mln where mln.lot_number =mtln.lot_number and mln.organization_id = mtln.organization_id and mln.inventory_item_id =mtln.inventory_item_id) lotgenobjid, to_number(NULL) sergenobjid, mtln.organization_id serorgid, mtln.transaction_date transaction_date, abs(mtln.transaction_quantity) transaction_quantity, to_char(1||'-'|| to_char(mtln.transaction_id)) dataset_pk_id, mmt.transaction_uom transaction_uom, mmt.transaction_action_id, mmt.transaction_type_id, mtln.serial_transaction_id, mmt.revision FROM mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln WHERE mmt.transaction_id = mtln.transaction_id AND mmt.transaction_source_type_id = 1 ANd mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:49','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:49','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mmt.transaction_source_id po_id, to_number(NULL) lotgenobjid, (select msn.gen_object_id from mtl_serial_numbers msn where msn.serial_number = mut.serial_number and msn.inventory_item_id =mut.inventory_item_id) sergenobjid, mmt.organization_id serorgid, mmt.transaction_date transaction_date, 1 transaction_quantity, to_char(2||'-'||to_char(mmt.transaction_id)) dataset_pk_id, mmt.transaction_uom transaction_uom, mmt.transaction_action_id, mmt.transaction_type_id, to_number(NULL) serial_transaction_id, mmt.revision FROM mtl_material_transactions mmt, mtl_unit_transactions mut WHERE mmt.transaction_id = mut.transaction_id AND mmt.transaction_source_type_id = 1 AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:49','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:49','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) ) pt, ( SELECT ml1.meaning,ml1.lookup_code,ml1.language FROM fnd_lookup_values ml1 WHERE ml1.lookup_type = 'DOCUMENT STATE' AND ml1.language in ('US') ) lkp1, ( SELECT ml1.meaning,ml1.lookup_code,ml1.language FROM fnd_lookup_values ml1 WHERE ml1.lookup_type IN ('PO APPROVAL','DOCUMENT STATE') AND ml1.language in ('US') ) lkp2 , ( select meaning , language FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE' AND lookup_code = '8' AND language in ('US') ) nodetype_lkp /* , ( select meaning , language FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE' AND lookup_code = '1' ) action_lkp */ ,hr_all_organization_units_tl h ,hr_organization_information ho WHERE pha.po_header_id = pt.po_id AND pha.vendor_id = aps.vendor_id (+) AND pha.vendor_site_id = apss.vendor_site_id (+) AND pha.vendor_contact_id = ascon.vendor_contact_id (+) AND ascon.per_party_id = hp.party_id (+) AND decode(pha.approved_flag, 'R', pha.approved_flag, nvl(pha.authorization_status, 'INCOMPLETE')) = lkp2.lookup_code AND CASE WHEN pha.cancel_flag = 'Y' THEN 'CANCELLED' WHEN pha.closed_code <> 'OPEN' THEN pha.closed_code WHEN pha.frozen_flag = 'Y' THEN 'FROZEN' WHEN pha.user_hold_flag = 'Y' THEN 'ON HOLD' ELSE 'OPEN' END = lkp1.lookup_code AND lkp1.language = lkp2.language AND nodetype_lkp.language = lkp1.language AND ho.organization_id = h.organization_id AND ho.org_information_context = 'CLASS' AND ho.org_information1 = 'OPERATING_UNIT' AND ho.org_information2 = 'Y' AND h.organization_id = pha.org_id AND h.language = lkp1.language /* AND action_lkp.language = lkp1.language */ AND lkp1.language in ('US'))) PIVOT ( MAX ( po_status ) AS po_status , MAX( node_type ) AS node_type , MAX( oucode ) AS oucode /*, MAX(PO_QA_RESULTS) AS PO_QA_RESULTS */ FOR language IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |