ECC Contract Lifecycle Management, Solicitations, SQL2

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Line Number, Auction Line Id, Is Line Exists, Clin Slin, Line Number Display, Sol Line No, Amd Line No, Exhibit Number, Item Num Rev ...
Imported from Enterprise Command Center
Dataset Key: po-clm-solicitations
Query Procedure: po_pon_ecc_util_pvt.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesSol
select
x.*
from
(
select * from (SELECT  gt.char1 ECC_SPEC_ID,  paip.line_number, paip.auction_header_id||'-'||paip.line_number auction_line_id,
        'Y' IS_LINE_EXISTS,
 CASE
 WHEN Nvl(paip.group_line_id,0)>0 THEN  'OA_MEDIA/notstartedind_status.gif'
 WHEN paip.exhibit_number is not null THEN  'OA_MEDIA/tree_installedbase.gif'
 ELSE 'OA_MEDIA/forwarded_status.gif'
 end
AS clin_slin,
paip.line_num_display line_number_display,
(SELECT ah.document_number FROM pon_auction_headers_all ah
              WHERE ah.auction_header_id=pah.AUCTION_HEADER_ID_ORIG_AMEND)||', '|| paip.line_num_display as SOL_LINE_NO,
CASE WHEN Nvl(pah.amendment_number,0)>= 1 and pah.AUCTION_STATUS='DRAFT' THEN
                pah.document_number||', '||paip.line_num_display
              ELSE
                NULL
             END   as AMD_LINE_NO,
(SELECT lt.line_type
             FROM
             po_line_types_tl lt
             WHERE
             paip.line_type_id = lt.line_type_id
             AND lt.language  = hrou.LANGUAGE ) as line_type,
paip.exhibit_number,
PO_PON_ECC_UTIL_PVT.get_lookup_meaning(paip.EXHIBIT_NUMBER,'PO_CLM_EXHIBIT_NUMBER',201,hrou.language)
 AS EXHIBIT,
decode(nvl(paip.clm_info_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',hrou.language),PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',hrou.language)) informational,
paip.item_number || nvl2(paip.item_revision, ', ', '') || paip.item_revision || jobs.name item_num_rev,
po_pon_ecc_util_pvt.get_item_description(paip.auction_header_id,paip.line_number,hrou.language)
 item_description,
paip.category_name as category,
(SELECT units.UNIT_OF_MEASURE_TL
               FROM mtl_units_of_measure_tl units
               WHERE
               paip.uom_code = units.uom_code
               AND units.language  = hrou.LANGUAGE) AS UOM,
DECODE(paip.purchase_basis,'GOODS',paip.QUANTITY,null)  AS QUANTITY,
nvl(paip.clm_amount,0) line_amount,
paip.clm_contract_type,
po_pon_ecc_util_pvt.get_lookup_meaning(paip.clm_contract_type,DECODE(paip.purchase_basis,'GOODS','PO_FEDERAL_CONTRACT_TYPES_QTY','PO_FEDERAL_CONTRACT_TYPES_AMT'
),201,hrou.language)     contract_type,
paip.clm_idc_type,
po_pon_ecc_util_pvt.get_lookup_meaning(paip.clm_idc_type,'PO_FEDERAL_IDC_TYPES',201,hrou.language)          idc_type,
paip.clm_cost_constraint,
po_pon_ecc_util_pvt.get_lookup_meaning(paip.clm_cost_constraint,'PO_FEDERAL_COST_CONSTRAINTS',201,hrou.language)   cost_constraint,
paip.clm_amount extended_price,
paip.need_by_start_date pop_start_date,
paip.need_by_date pop_end_date,
paip.clm_need_by_date need_by_date,
CASE
WHEN paip.clm_option_indicator IS NULL OR exhibit_number IS NOT NULL THEN ''
WHEN paip.clm_option_indicator = 'B' THEN ''
ELSE (SELECT  paipa1.line_num_display FROM pon_auction_item_prices_all paipa1
                 WHERE paipa1.auction_header_id=paip.auction_header_id
                 AND paipa1.line_number=paip.clm_base_line_num)
END  as BASE_LINE_NUM,
paip.CLM_OPTION_NUM OPTION_NUM,
paip.CLM_OPTION_FROM_DATE OPTION_FROM_DATE,
paip.CLM_OPTION_TO_DATE OPTION_TO_DATE,
CASE
WHEN paip.clm_option_indicator IS NULL OR exhibit_number IS NOT NULL THEN ''
WHEN paip.clm_option_indicator = 'B' THEN ''
ELSE PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',hrou.language)
END AS clm_option,
paip.CLM_UNIT_PRICE,
hrl.location_code SHIP_TO_LOCATION,
Decode(
                                   (
                                          SELECT count(*)
                                          FROM   pon_backing_requisitions pbr
                                          WHERE  pbr.auction_header_id = paip.auction_header_id
                                          AND    pbr.line_number = paip.line_number),0, NULL, 1,
                                          (select prh.segment1||' , '|| prl.line_num_display
                                          FROM     pon_backing_requisitions pbr,
                                       po_requisition_lines_all prl,
                                       po_requisition_headers_all prh
                              WHERE    pbr.requisition_line_id=prl.requisition_line_id
                              AND      pbr.auction_header_id=paip.auction_header_id
                              AND      prh.requisition_header_id=pbr.requisition_header_id
                              AND      pbr.line_number=paip.line_number ),
                     '['||(
                              SELECT   Listagg('"'||prh.segment1
                                                || ' , '
                                                || prl.line_num_display||'"' ,',') within GROUP (ORDER BY pbr.auction_header_id) AS requisition_lined
                              FROM     pon_backing_requisitions pbr,
                                       po_requisition_lines_all prl,
                                       po_requisition_headers_all prh
                              WHERE    pbr.requisition_line_id=prl.requisition_line_id
                              AND      pbr.auction_header_id=paip.auction_header_id
                              AND      prh.requisition_header_id=pbr.requisition_header_id
                              AND      pbr.line_number=paip.line_number
                              GROUP BY pbr.auction_header_id)||']')  AS requisition_number,
CASE
WHEN
(SELECT count(*)  FROM pon_backing_requisitions pbr WHERE pbr.auction_header_id = paip.auction_header_id and pbr.line_number = paip.line_number)=0 THEN
NULL
WHEN
(SELECT count(*)  FROM pon_backing_requisitions pbr WHERE pbr.auction_header_id = paip.auction_header_id and pbr.line_number = paip.line_number)=1 THEN
(SELECT pbr.requisition_number ||' , '|| prl.line_num_display FROM pon_backing_requisitions pbr,po_requisition_lines_all prl
WHERE pbr.auction_header_id = paip.auction_header_id and pbr.line_number = paip.line_number and pbr.requisition_line_id=prl.requisition_line_id)
ELSE
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_AUCTS_MULTIPLE_X', '396',hrou.language)
END AS REQUISITION_NO ,hrou.language language 
FROM
pon_auction_item_prices_all paip,
             pon_auction_headers_all pah,
             hr_all_organization_units_tl hrou,
             per_jobs_vl jobs,
             financials_system_params_all fsp,
              hr_locations hrl,
             po_session_gt gt
 
WHERE
 paip.auction_header_id = gt.num1
and pah.auction_header_id=paip.auction_header_id
and hrou.organization_id (+) = pah.org_id
AND hrou.LANGUAGE in ( 'US')
and paip.line_number = gt.num2(+)
and paip.job_id = jobs.job_id(+)
and paip.org_id = fsp.org_id(+)
AND hrl.location_id(+) = paip.ship_to_location_id
AND gt.key = 3197942 )   PIVOT ( max(EXHIBIT) as EXHIBIT,
                              max(item_description) as item_description,
                              max(UOM) as UOM,
                              max(line_type) as line_type,
                              max(contract_type) as contract_type,
                              max(idc_Type) as idc_Type,
                              max(cost_constraint) as cost_constraint,
                              max(informational) as informational,
                              max(clm_option) as clm_option
                              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