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 COPY OF: GL Account Analysis PO Headers and Lines PO Receiving Transactions Pipeline XXT OF: PO Headers and Lines
Blitz Report™

Blitz Report™ provides multiple benefits: