ECC Contract Lifecycle Management, Solicitation Responses

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-clm-sol-offers
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, Solicitation Responses and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select * from (SELECT
        gt.char1 as ECC_SPEC_ID,
        'VIEW' BID_VIEW_ACTION,
        PBD.AUCTION_HEADER_ID,
        PAH.DOCUMENT_NUMBER SOLICITATION_NUMBER,
        pbd.BID_NUMBER||', '||bl.LINE_NUM_DISPLAY OFFER_LINE_NO,
        PAH.AUCTION_TITLE TITLE,
        PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pbd.surrog_bid_created_contact_id,12,fl2.language) buyer,
      	PBD.BID_NUMBER AS BID_NUMBER,
        FL2.MEANING AS BID_STATUS,
        pah.currency_code  solicitation_currency,
        PBD.TRADING_PARTNER_CONTACT_NAME AS BID_SUPPLIER_CONTACT,
        PBD.TRADING_PARTNER_CONTACT_ID AS BID_SUPPLIER_CONTACT_ID,
        PBD.TRADING_PARTNER_NAME AS BID_SUPPLIER_NAME,
        PBD.VENDOR_SITE_CODE  AS BID_VENDOR_SITE_CODE,
        PO_VENDOR_SITES_ALL.DUNS_NUMBER,
        PO_VENDOR_SITES_ALL.cage_code cage_code,
        PBD.BID_CURRENCY_CODE offer_currency,
        PBD.SURROG_BID_RECEIPT_DATE  Offer_Received_Time,
        PBD.BID_EXPIRATION_DATE Offer_Valid_Until,
        bl.LINE_NUM_DISPLAY,
        DECODE(NVL(msi.allow_item_desc_update_flag, 'Y'), 'Y' ,BL.ITEM_DESCRIPTION ,'N' , msit.description) AS BIDLINE_ITEM_DESC,
        paip.item_number || nvl2(paip.item_revision, ', ', '') || paip.item_revision  item,
        decode(BL.CLM_INFO_FLAG,'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',fl2.language),PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',fl2.language)) Informational,
        DECODE(paip.purchase_basis,'GOODS',BL.QUANTITY,null)   AS OFFER_QUANTITY,
        DECODE(paip.purchase_basis,'GOODS',BL.PRICE,null) AS OFFER_PRICE,
        DECODE(paip.purchase_basis,'GOODS',(BL.QUANTITY*BL.PRICE),BL.PRICE) AS AMOUNT,
        BL.PROMISED_DATE AS PROMISED_DATE,
        BL.PROMISE_POP_START_DATE,
        BL.PROMISE_POP_END_DATE,
        decode(POHA1.TYPE_LOOKUP_CODE,'STANDARD',BL.ORDER_NUMBER,null) AS BIDLINE_ORDER_NUMBER,
        BID_LINE_REF.LINK_TYPE,
        decode(BID_LINE_REF.LINK_TYPE,'ALTERNATE',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',fl2.language),null) BIDLINE_LINK_TYPE,
        /*DECODE(BID_LINE_REF.LINK_TYPE , NULL,DECODE(BL.AUCTION_LINE_NUMBER,-1,FL3.MEANING,NULL),FL3.MEANING) AS BIDLINE_LINK_TYPE,*/
        fl2.language,
        bl.clm_idc_type,
        po_pon_ecc_util_pvt.get_lookup_meaning(bl.clm_idc_type,'PO_FEDERAL_IDC_TYPES',201,fl2.language)          idc_type,
        bl.clm_cost_constraint,
        po_pon_ecc_util_pvt.get_lookup_meaning(bl.clm_cost_constraint,'PO_FEDERAL_COST_CONSTRAINTS',201,fl2.language)   cost_constraint,
        po_pon_ecc_util_pvt.get_lookup_meaning(bl.clm_contract_type,DECODE(paip.purchase_basis,'GOODS','PO_FEDERAL_CONTRACT_TYPES_QTY','PO_FEDERAL_CONTRACT_TYPES_AMT'
),201,fl2.language) contract_type,
        (SELECT units.UNIT_OF_MEASURE_TL
               FROM mtl_units_of_measure_tl units
               WHERE
               bl.UOM = units.uom_code
               AND units.language  = fl2.LANGUAGE) as  unit,
               PBD.IDV_HEADER_ID,
               PBD.EXTERNAL_IDV,
               POHA.segment1 internal_idv,
               paip.CLM_NEED_BY_DATE,
               PO_PON_ECC_UTIL_PVT.get_lookup_meaning(paip.EXHIBIT_NUMBER,'PO_CLM_EXHIBIT_NUMBER',201,fl2.language)
 AS EXHIBIT,
 CASE
WHEN BL.clm_option_indicator IS NULL OR paip.exhibit_number IS NOT NULL THEN ''
WHEN BL.clm_option_indicator = 'B' THEN ''
ELSE PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',fl2.language)
END AS CLM_OPTION,
(select loc.LOCATION_CODE from hr_locations_all_tl loc
where paip.ship_to_location_id = loc.location_id
and  loc.language=fl2.language)  as SHIP_TO,
decode(nvl(bl.HAS_USER_BID_FLAG, 'N'),null,'Y') as OFFERED,
pah.org_id,
pah.SECURITY_LEVEL_CODE,
             (SELECT
                LISTAGG(pntm.user_id,'|') WITHIN GROUP  (ORDER BY pntm.auction_header_id) AS NEG_TEAM_MEMBERS
                  FROM
                  pon_neg_team_members pntm
                WHERE
                  pntm.auction_header_id=pah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS,
decode(POHA1.TYPE_LOOKUP_CODE,'BLANKET',BL.ORDER_NUMBER,'CONTRACT',BL.ORDER_NUMBER,null) IDV
 
 FROM  	PON_BID_HEADERS PBD,
        PON_BID_ITEM_PRICES BL,
        PON_AUCTION_HEADERS_ALL PAH,
        pon_auction_item_prices_all paip,
        FND_LOOKUP_VALUES FL2,
        FND_LOOKUP_VALUES FL3,
        PO_HEADERS_ALL POHA,
        PO_HEADERS_ALL POHA1,
        ap_supplier_SITES_ALL PO_VENDOR_SITES_ALL,
        MTL_SYSTEM_ITEMS_TL msit ,
        mtl_system_items_kfv msi,
        FINANCIALS_SYSTEM_PARAMS_ALL fsp,
        po_session_gt gt,
         (SELECT PBF.AUCTION_HEADER_ID,PBF.BID_NUMBER,PBF.LINE_NUMBER,
                MAX(PBF.AUCTION_LINE_NUMBER) AS AUCTION_LINE_NUMBER ,
                MAX(PBF.LINK_TYPE) AS LINK_TYPE,
                MAX(PBF.LINK_DONE_BY) AS LINK_DONE_BY
        FROM PON_BID_ITEM_REFERENCES PBF
        GROUP BY PBF.AUCTION_HEADER_ID,PBF.BID_NUMBER,PBF.LINE_NUMBER) BID_LINE_REF
WHERE	  PBD.bid_number= gt.num2
AND     BL.line_number = gt.num3 (+)
AND     gt.key = 3218983
AND     PAH.AUCTION_HEADER_ID=PBD.AUCTION_HEADER_ID
AND     PBD.AUCTION_HEADER_ID = BL.AUCTION_HEADER_ID
AND   	PBD.BID_NUMBER = BL.BID_NUMBER
AND     BL.BID_NUMBER = BID_LINE_REF.BID_NUMBER(+)
AND     BL.AUCTION_HEADER_ID = BID_LINE_REF.AUCTION_HEADER_ID(+)
AND     BL.LINE_NUMBER = BID_LINE_REF.LINE_NUMBER(+)
and     BL.AUCTION_HEADER_ID= PAIP.AUCTION_HEADER_ID(+)
and     BL.AUCTION_LINE_NUMBER=PAIP.LINE_NUMBER(+)
and     pbd.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+)
AND   	FL2.LOOKUP_TYPE = 'PON_BID_STATUS'
AND   	FL2.LOOKUP_CODE = PBD.BID_STATUS
AND     FL2.LANGUAGE in ( 'US')
AND     FL3.LOOKUP_TYPE(+) = 'PON_UNSOL_LINK_TYPE'
AND	    FL3.LOOKUP_CODE(+) = NVL(BID_LINE_REF.LINK_TYPE,'ADDITIONAL')
AND     nvl(FL3.LANGUAGE,FL2.LANGUAGE) =  FL2.LANGUAGE
AND paip.ORG_ID = fsp.ORG_ID(+)
AND msit.INVENTORY_ITEM_ID(+)    = paip.ITEM_ID
AND NVL(msit.organization_id,fsp.inventory_organization_id) = fsp.inventory_organization_id
AND msi.INVENTORY_ITEM_ID(+)   = paip.ITEM_ID
AND NVL(msi.organization_id,fsp.inventory_organization_id)  = fsp.inventory_organization_id
AND nvl(msit.language,FL2.LANGUAGE)    =   FL2.LANGUAGE
AND     pbd.IDV_HEADER_ID = POHA.PO_HEADER_ID(+)
AND     BL.PO_HEADER_ID = POHA1.PO_HEADER_ID(+)
) PIVOT (max(buyer) as buyer,
                         max(BID_STATUS) as BID_STATUS,
                         max(BIDLINE_LINK_TYPE) as BIDLINE_LINK_TYPE,
                         max(idc_Type) as idc_Type,
                         max(cost_constraint) as cost_constraint,
                         max(unit) as unit,
                         max(contract_type) as contract_type,
                         max(EXHIBIT) as EXHIBIT,
                         max(ship_to) as ship_to,
                         max(CLM_OPTION) as CLM_OPTION,
                         max(informational) as informational,
                         max(BIDLINE_ITEM_DESC) as BIDLINE_ITEM_DESC
                         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