ECC Contract Lifecycle Management, Solicitations, SQL2

Description
Categories: Enterprise Command Center
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
Run ECC Contract Lifecycle Management, Solicitations, SQL2 and other Oracle EBS reports with Blitz Report™ on our demo environment
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 = 3218985 )   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
Blitz Report™