ECC Contract Lifecycle Management, Requisitions, SQL12

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Requisitions Data Set
Dataset Key: po-clm-requisitions
Query Procedure: PO_ICX_CLM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesReq
Run ECC Contract Lifecycle Management, Requisitions, SQL12 and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM ( SELECT gt.char1 ecc_spec_id
         ,prh.requisition_header_id
         , pod.modification_number AWARD_MOD_NUMBER
         , nvl2( prh.par_draft_id, pod.justification, prh.description) REQ_DESCRIPTION
         , nvl2(prh.par_draft_id , pod.modification_number ||', ' || nvl(prl.line_num_display,prl.line_num)
                    , prh.segment1 ||', ' || nvl(prl.line_num_display,prl.line_num)) par_number
         , prl.par_draft_id PAR_DRAFT_ID
         , prl.par_line_id PAR_LINE_ID
         , pod.document_id PO_HEADER_ID
         , prh.org_id
         ,flv.meaning par_status
         ,flv.language
         FROM po_requisition_headers_all     prh,
              po_drafts pod,
              po_requisition_lines_all       prl,
              po_lines_draft_all              pld ,
              po_session_gt   gt ,
              fnd_lookup_values flv
         WHERE prl.requisition_header_id = prh.requisition_header_id
         AND prh.segment1 = to_char(pod.draft_id(+))
         AND prh.par_draft_id is not null
         AND (prh.revision_num is null OR prh.revision_num = -1)
         AND NVL(prh.authorization_status, 'INCOMPLETE') NOT IN  ('SYSTEM_SAVED', 'RETURNED')
         AND prl.par_line_id = pld.po_line_id(+)
         AND (prl.par_line_id is NULL OR pod.draft_id = pld.draft_id)
         AND gt.index_num1 = prh.requisition_header_id
         AND gt.num5 = pod.draft_id
         AND gt.index_num2 = prl.requisition_line_id
         AND     flv.lookup_type = 'DRAFT_STATUS'
         AND     flv.lookup_code = pod.status
         AND     flv.view_application_id = 201
         and     flv.language in ('US')
         AND gt.KEY = 3218981 ) PIVOT ( max(par_status) as par_status
                                             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