ECC Contract Lifecycle Management, Requisitions, SQL9

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, SQL9 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 ,
                            prh.segment1    requisition_no ,
                            prh.description req_description ,
                            org.NAME        operating_unit ,
                            prh.amendment_reason ,
                            flv2.language ,
                            prh.last_update_date ,
                            prh.creation_date ,
                            Extract(year FROM prh.creation_date) creation_year ,
                            To_char(prh.creation_date,'Month')   creation_month ,
                            prh.revision_num ,
                            prh.note_to_authorizer ,
                            prh.conformed_header_id ,
                            flv2.meaning             authorization_status ,
                            prh.authorization_status authorization_status_code ,
                            CASE
                                   WHEN (
                                                 prh.revision_num > 0 ) THEN 'PR Amendment'
                                   WHEN (
                                                 prh.clm_mipr_type IS NOT NULL) THEN 'MIPR'
                                   WHEN (
                                                 prh.clm_mipr_type IS NOT NULL
                                          AND    prh.revision_num > 0 ) THEN 'MIPR Amendment'
                                   WHEN (
                                                 par_flag IS NULL) THEN 'PR'
                            END    req_type ,
                            po_icx_clm_ecc_util_pvt.Get_current_approver_list(prh.requisition_header_id) current_approver ,
                            prh.clm_mipr_type ,
                            prh.amendment_type ,
                            prh.amendment_status ,
                            prh.par_draft_id ,
                            Nvl(prh.par_flag,'N') par_flag ,
                            prh.org_id ,
                            prh.approved_date                                                                          approved_date ,
                            (select sum( decode ( order_type_lookup_code
                                       , 'FIXED PRICE' , amount
                                       , 'RATE'        , amount
                                       ,quantity*unit_price
                                       )) FROM   po_requisition_lines_all
                                          WHERE  requisition_header_id = prh.requisition_header_id and
                                                  nvl(cancel_flag,'N') <> 'Y' and
                                                  nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N') requisition_amount ,
                            nvl(prh.CLM_MIPR_ACKNOWLEDGED_FLAG, 'N') MIPR_ACC_STAT,
                            prl.requisition_line_id,
                            prl.line_num_display,
                            prl.item_description,
                            prl.requisition_line_number,
                            prl.quantity,
                            prl.unit_price,
                            prl.line_amount,
                            prl.deliver_to_location_id,
                            prl.clinslinicon,
                            prl.clinslin,
                            prl.category_id,
                            prl.category,
                            prl.requestor,
                            prl.line_assignment_number,
                            prl.informational,
                            prl.rate_type,
                            prl.rate_date,
                            prl.rate,
                            prl.line_status_code,
                            prl.clm_option_from_date,
                            prl.need_by_date,
                            prl.justification,
                            prl.option_base_line_no,
                            prl.pcard_flag,
                            prl.supplier,
                            prl.site,
                            prl.clm_period_perf_start_date,
                            prl.clm_period_perf_end_date,
                            prl.clm_option_num,
                            prl.clm_option_to_date,
                            prl.option_line,
                            prl.option_up_for_exercising,
                            prl.item,
                            prl.options_exercised,
                            prl.quantity_delivered,
                            prl.quantity_received,
                            prl.quantity_billed,
                            prl.par_line_id,
                            prl.line_location_id,
                            prl.requests_in_pool,
                            prl.auction_header_id,
                            prl.award_no,
                            prl.is_liquidatable,
                            prl.FUNDS_LIQUIDATABLE,
                            prl.clm_cancel_flag,
                            CASE
                                   WHEN prl.order_type_lookup_code = 'QUANTITY' THEN
                                          (
                                                 SELECT meaning
                                                 FROM   fnd_lookup_values_vl
                                                 WHERE  lookup_type = 'PO_FEDERAL_CONTRACT_TYPES_QTY'
                                                 AND    lookup_code = prl.contract_type_code)
                                   ELSE
                                         (
                                         SELECT meaning
                                         FROM   fnd_lookup_values_vl
                                         WHERE  lookup_type = 'PO_FEDERAL_CONTRACT_TYPES_AMT'
                                         AND    lookup_code = prl.contract_type_code)
                            END contract_type,
                            (SELECT meaning
                                   FROM   fnd_lookup_values_vl
                                   WHERE  lookup_type = 'PO_FEDERAL_COST_CONSTRAINTS'
                                   AND    lookup_code = prl.cost_constraint_code) cost_constraint,
 
                                   po_icx_clm_ecc_util_pvt.get_pr_line_status_disp(prl.line_status_code) AS line_status,
                            CASE
                                   WHEN prl.auction_header_id IS NOT NULL THEN
                                (
                                SELECT document_number
                                FROM   pon_auction_headers_all
                                WHERE  auction_header_id= prl.auction_header_id)
                                || ', '
                                ||
                               (
                                SELECT pap.line_num_display
                                FROM   pon_auction_item_prices_all pap,
                                      pon_backing_requisitions pbr
                                WHERE  pap.auction_header_id= prl.auction_header_id
                                AND    pbr.auction_header_id=prl.auction_header_id
                                AND    pbr.requisition_line_id = prl.requisition_line_id
                                AND    pap.line_number = pbr.line_number)
                              ELSE NULL  END AS  solicitation_no,
                              hrt.location_code deliver_to_location,
                              prd.distribution_id,
                            prd.distribution_num distribution_no,
                            prd.acrn,
                            prd.gl_encumbered_date encumbered_date,
                            prd.project_id,
                            prd.funded_value,
                            prd.quantity_funded,
                            prd.expenditure_type,
                            prd.expenditure_item_date,
                            CASE
                                   WHEN prd.encumbered_flag = 'Y' THEN fnd_message.get_string('PO','PO_DIALOG_YES_LABEL')
                                   WHEN nvl(prd.encumbered_flag,'N') ='N' THEN fnd_message.get_string('PO','PO_DIALOG_NO_LABEL')
                                   ELSE ''
                            END reserved,
                            decode(prd.project_id,NULL,NULL, (SELECT pa.NAME
                                                                        FROM   pa_projects_all pa
                                                                        WHERE  pa.project_id = prd.project_id
                                                                        AND    rownum=1)) AS project,
                            decode(prd.project_id,NULL,NULL,
                            (
                                   SELECT pa.segment1
                                   FROM   pa_projects_all pa
                                   WHERE  pa.project_id = prd.project_id
                                   AND    rownum=1)) AS project_number ,
                            decode(prd.task_id,NULL,NULL,
                            (
                                   SELECT task.task_name
                                   FROM   pa_tasks task
                                   WHERE  task.task_id = prd.task_id
                                   AND    rownum=1)) AS TASK,
                              decode(nvl(prl.matching_basis, 'QUANTITY'), 'AMOUNT', NULL, nvl(muom.unit_of_measure_tl, prl.unit_meas_lookup_code))   AS unit_of_measure,
                              CASE
                                   WHEN nvl(prl.fund_source_not_known,'N') = 'Y' THEN 'Fund Source Not Known'
                                   WHEN nvl(prd.partial_funded_flag, 'N') = 'Y' THEN 'Partially Funded'
                                   ELSE 'Fully Funded'
                            END fund_status,
                            decode(prd.expenditure_organization_id,NULL,NULL,
                            (
                                   SELECT org.NAME
                                   FROM   hr_all_organization_units_tl exp_org
                                   WHERE  exp_org.organization_id = prd.expenditure_organization_id
                                   AND    org.language= exp_org.language
                                   AND    rownum=1)) AS expenditure_org,
                            decode(prd.code_combination_id,null,null, (select gl.concatenated_segments
                                                                       from gl_code_combinations_kfv gl
                                                                       where gls.chart_of_accounts_id = gl.chart_of_accounts_id
                                                                      and prd.code_combination_id = gl.code_combination_id)) charge_account
 
                     FROM   po_requisition_headers_all prh
                            ,po_session_gt gt
                            ,hr_all_organization_units_tl org ,
                            fnd_lookup_values FLV2 ,
                            financials_system_params_all FSP ,
                            gl_sets_of_books GLS,
 
                            (SELECT prl.requisition_line_id,
                            prl.line_num_display,
                            prl.item_description,
                            prh.segment1      || ' , '|| prl.line_num_display  requisition_line_number,
                            decode(nvl(prl.matching_basis, 'QUANTITY'), 'AMOUNT', to_number(NULL), prl.quantity)                        AS quantity,
                            decode(nvl(prl.matching_basis, 'QUANTITY'), 'AMOUNT', to_number(NULL), prl.unit_price)                     AS unit_price,
                            decode(nvl(prl.order_type_lookup_code, 'QUANTITY'), 'FIXED PRICE', prl.amount, 'RATE', prl.amount, round((prl.quantity * prl.unit_price),2)) AS line_amount,
                            prl.deliver_to_location_id,
                            decode(prl.group_line_id,NULL,'/OA_MEDIA/forwarded_status.gif','/OA_MEDIA/notstartedind_status.gif') clinslinicon,
                            decode(prl.group_line_id,NULL,'CLIN','SLIN')                                                         clinslin,
                            prl.category_id,
                            mc.concatenated_segments AS category,
                            per.full_name            AS requestor,
                            prl.assignment_number       line_assignment_number,
                            prl.clm_info_flag           informational,
                            prl.contract_type AS contract_type_code,
                            prl.cost_constraint AS cost_constraint_code,
                            prl.order_type_lookup_code,
                            prl.rate_type,
                            prl.rate_date,
                            prl.rate,
                            prl.matching_basis,
                            po_icx_clm_ecc_util_pvt.get_pr_line_status_code(prl.requisition_line_id)    AS line_status_code,
                            prl.clm_option_from_date,
                            prl.need_by_date,
                            prl.justification,
                            base.line_num_display option_base_line_no,
                            prl.pcard_flag,
                            prl.suggested_vendor_name     supplier,
                            prl.suggested_vendor_location site,
                            prl.clm_period_perf_start_date,
                            prl.clm_period_perf_end_date,
                            prl.clm_option_num,
                            prl.clm_option_to_date,
                            prl.unit_meas_lookup_code,
                            decode(prl.clm_option_indicator,'O','Y','N') option_line,
                            CASE
                                   WHEN prl.clm_option_indicator = 'O'
                                   AND    nvl (prl.clm_option_exercised,'N') = 'Y'
                                   and    nvl(prl.cancel_flag, 'N') = 'N' THEN 'Exercised'
                                   WHEN prl.clm_option_indicator = 'O'
                                   AND    nvl (prl.clm_option_exercised,'N') = 'N'
                                   AND    (
                                                 nvl (prl.clm_option_from_date,sysdate) <= sysdate
                                          AND    nvl(prl.clm_option_to_date,sysdate) >= sysdate)
                                   and    nvl(prl.cancel_flag, 'N') = 'N' THEN 'Yes'
                                   ELSE 'No'
                            END option_up_for_exercising ,
                            CASE
                                   WHEN msi.segment1 IS NOT NULL
                                   AND    prl.item_revision IS NOT NULL THEN msi.segment1
                                                 || ' , '
                                                 || prl.item_revision
                                   WHEN msi.segment1 IS NOT NULL
                                   AND    prl.item_revision IS NULL THEN msi.segment1
                                   ELSE ''
                            END item,
                            CASE
                                   WHEN prh.authorization_status = 'APPROVED' THEN round(nvl(prl.need_by_date,prl.clm_period_perf_start_date)- prh.approved_date,0)
                            END                                                                   submitted_within,
                            round(nvl(prl.need_by_date,prl.clm_period_perf_start_date)-sysdate,0) pop_within,
                            ''                                                                    assigned_within,
                            CASE
                                   WHEN prl.clm_option_indicator = 'O'
                                   AND    prl.clm_exercised_date <= prl.clm_option_to_date THEN fnd_message.get_string('PO','PO_DIALOG_YES_LABEL')
                                   WHEN prl.clm_option_indicator = 'O'
                                   AND    prl.clm_exercised_date > prl.clm_option_to_date THEN fnd_message.get_string('PO','PO_DIALOG_NO_LABEL')
                                   ELSE ''
                            END options_exercised,
                            ''  quantity_delivered,
                            ''  quantity_received,
                            ''  quantity_billed,
                            prl.fund_source_not_known,
                            prl.par_line_id,
                            prl.line_location_id,
                            CASE
                                   WHEN nvl(prl.reqs_in_pool_flag,'N') = 'Y' THEN fnd_message.get_string('PO','PO_DIALOG_YES_LABEL')
                                   WHEN nvl(prl.reqs_in_pool_flag,'N') = 'N' THEN fnd_message.get_string('PO','PO_DIALOG_NO_LABEL')
                            END requests_in_pool,
                            (  SELECT max(pbr.auction_header_id)
                                   FROM   pon_backing_requisitions pbr
                                   WHERE  pbr.requisition_line_id=prl.requisition_line_id) AS auction_header_id,
 
                            po_icx_clm_ecc_util_pvt.get_line_award_number(prl.requisition_line_id)    award_no ,
                         por_liquidate_reqs.IS_LIQUIDATABLE(prl.requisition_line_id) as is_liquidatable ,
                         decode(por_liquidate_reqs.IS_LIQUIDATABLE(prl.requisition_line_id), 'Y', por_liquidate_reqs.GET_FUNDS_LIQUIDATABLE(prl.requisition_line_id), '0' ) as FUNDS_LIQUIDATABLE,
			                   nvl(prl.cancel_flag,'N' )clm_cancel_flag
                     FROM   po_requisition_lines_all prl,
                            po_requisition_lines_all base,
                            po_requisition_headers_all prh,
                            mtl_categories_kfv mc,
                            per_all_people_f per,
                            mtl_system_items msi
 
                     WHERE
                            prh.requisition_header_id = prl.requisition_header_id
                     AND    prl.clm_base_line_num = base.requisition_line_id(+)
                     AND    prl.category_id = mc.category_id(+)
                     AND    prl.to_person_id = per.person_id(+)
                     AND    per.effective_start_date(+) <= trunc(sysdate)
                     AND    per.effective_end_date(+) >= trunc(sysdate)
                     AND    msi.inventory_item_id(+) = prl.item_id
                     AND    msi.organization_id(+) = prl.org_id ) prl,
 
                     hr_locations_all hrt,
                     mtl_units_of_measure muom,
                     po_req_distributions_all prd
 
                     WHERE  prh.requisition_header_id = gt.index_num1
                     AND    gt.KEY=3218981
                     AND    prh.org_id = org.organization_id
                     AND    org.language IN ('US')
                     AND    flv2.lookup_type = 'AUTHORIZATION STATUS'
                     AND    flv2.lookup_code = Nvl(prh.authorization_status,'INCOMPLETE')
                     AND    fsp.org_id= prh.org_id
                     AND    fsp.set_of_books_id = gls.set_of_books_id
                     AND    prl.requisition_line_id(+)=gt.index_num2
                     AND    hrt.location_id (+) = prl.deliver_to_location_id
                     AND    muom.unit_of_measure(+) = prl.unit_meas_lookup_code
                     AND    Nvl(muom.LANGUAGE,org.LANGUAGE)  = org.language
                     AND    prd.distribution_id(+) = gt.num3
                     )
 
                      PIVOT (
                      max(operating_unit) AS operating_unit,
                      max(authorization_status) AS authorization_status,
                      max(line_status) AS line_status,
                      max(contract_type) AS contract_type,
                      max(unit_of_measure) AS unit_of_measure,
                      max(deliver_to_location) AS deliver_to_location,
                      max(expenditure_org) AS expenditure_org,
		      max(fund_status) AS fund_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
Blitz Report™