ECC Procurement, Purchase Order Metrics

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Invoice Id, Line Location Id, Hold Id, Release Lookup Code, On Hold Flag, Doc Authorized User Ids, Security Level Code, Org Id ...
Imported from Enterprise Command Center
Dataset Key: po-pcc-orders-metric
Query Procedure: po_pcc_orders_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
select
x.*
from
(
select aha.INVOICE_ID  ||'-'|| aha.LINE_LOCATION_ID ||'-'||REPLACE(aha.HOLD_LOOKUP_CODE,' ','_') ECC_SPEC_ID,
aha.invoice_id, aha.line_location_id, aha.HOLD_ID, aha.release_lookup_code, nvl(aha.release_lookup_code,'Yes') as on_hold_flag,
CASE WHEN pdt.SECURITY_LEVEL_CODE='PUBLIC' OR pdt.SECURITY_LEVEL_CODE='PURCHASING' THEN
          NULL
     ELSE
          po_pcc_agreements_util_pvt.get_doc_authorized_userIds('POPA', poh.TYPE_LOOKUP_CODE, poh.AUTHORIZATION_STATUS,
          poh.org_id,poh.AGENT_ID, poh.PO_HEADER_ID) END as DOC_AUTHORIZED_USER_IDS,
pdt.SECURITY_LEVEL_CODE,
poh.org_id ORG_ID
  from ap_holds_all aha,po_line_locations_all po_lineloc,po_headers_all poh,PO_DOCUMENT_TYPES_ALL pdt,
  po_doc_style_headers doc_style
  where aha.line_location_id = po_lineloc.line_location_id
  AND aha.hold_lookup_code in
       (select hold_lookup_code
        from ap_hold_codes
        where hold_type = 'MATCHING HOLD REASON'
        and NVL(inactive_date, trunc(sysdate) + 1) >= trunc(sysdate))
  AND pdt.org_id = poh.org_id
  AND pdt.DOCUMENT_TYPE_CODE = 'PO'
  AND pdt.DOCUMENT_SUBTYPE = poh.TYPE_LOOKUP_CODE
  AND po_lineloc.po_header_id = poh.po_header_id
  and poh.type_lookup_code IN ('STANDARD')
  AND doc_style.style_id = poh.style_id
  AND nvl(doc_style.clm_flag,'N') = 'N' AND aha.creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'),aha.creation_date)
) 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