ECC Contract Lifecycle Management, Requisitions, SQL11

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.