ECC Contract Lifecycle Management, Solicitation Responses, SQL1

Description
Columns: Ecc Spec Id, Bid View Action, Auction Header Id, Solicitation Number, Offer Line No, Title, Bid Number, Solicitation Currency, Bid Supplier Contact, Bid Supplier Contact Id ...
Imported from ECC
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 = 3127025
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"))