ECC Genealogy and Trace, Purchase Orders

➜ Back to Library
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
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 
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
 LOV
Download
Related Blitz Reports
ECC Process Genealogy and Trace, Purchase Orders ECC Procurement, Agreements ECC Procurement, Purchase Orders GL Account Analysis (Distributions) GL Account Analysis PO Headers and Lines PO Receiving Transactions Pipeline XXT OF: PO Headers and Lines XXSUB PO Headers and Lines
Blitz Report™

Blitz Report™ provides multiple benefits: