Query to get Negotiation , Bid , Requestions (Bid Analysis)

 -- Bid Analysis Details

with lookup_values as ( 

    select lookup_type, 

           lookup_code, 

           meaning, 

           view_application_id

    from   fnd_lookup_values_vl flvl

    where  flvl.lookup_type in ('ZX_TRL_TRANSFERRED_STATUS','PON_AWARD_STATUS','YES_NO')

    and    flvl.enabled_flag='Y'

    and    sysdate between nvl(flvl.start_date_active,to_date('01/01/1951','DD/MM/YYYY')) and nvl(flvl.end_date_active,to_date('31/12/4712','DD/MM/YYYY')))

SELECT 

fabu.bu_name business_unit,

paha.document_number negotiation_number,

paha.auction_title negotiation_title,

padb.negotiation_display_name negotiation_type,

-- (select flv.meaning

-- from  fnd_lookups flv

-- where flv.lookup_type = 'PON_AUCTION_STATUS'

-- and paha.auction_status = flv.lookup_code

-- ) Negotiation_Status,

paha.auction_status Negotiation_Status,

--(select style_name from pon_negotiation_styles_tl where style_id=paha.style_id and LANGUAGE = 'US' )as negotiation_style,

pnst.style_name as negotiation_style,

paha.currency_code Negotiation_currency,

--TO_CHAR(paha.creation_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Creation_Date,

paha.creation_date as Negotiation_Creation_Date,

--TO_CHAR(paha.open_bidding_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Open_Date,

paha.open_bidding_date as Negotiation_Open_Date,

--TO_CHAR(paha.close_bidding_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Closed_Date,

paha.close_bidding_date as Negotiation_Closed_Date,

abs(paha.close_bidding_date - sysdate) Negotiation_Time_remaining,

(SELECT DISPLAY_NAME 

FROM per_person_names_f_v  

WHERE person_id = pha.agent_id

AND name_type='GLOBAL'

AND SYSDATE BETWEEN effective_start_date AND effective_end_date)Buyer_Name,

pbh.bid_number,

ps.vendor_name supplier,

pssv.vendor_site_code Supplier_Site,

(SELECT pscv.party_name 

  FROM poz_supplier_contacts_v pscv 

 WHERE 1=1

 AND pha.vendor_contact_id =pscv.vendor_contact_id

 AND ps.vendor_id = pscv.vendor_id

)supplier_contact,

paha.MAX_BID_COLOR_SEQUENCE_ID responses,

(select meaning 

 from   lookup_values 

 where  lookup_type='ZX_TRL_TRANSFERRED_STATUS'

 and    lookup_code=pbh.poq_transfer_status) response_status,

paip.target_price,

paip.requisition_number,

paha.requested_by requestor,

prla.amount requisition_line_amount,

pbh.overall_rank,

--TO_CHAR(pbav.date_value, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') Date_of_Response,

pbav.date_value as Date_of_Response,

pbav.created_by response_entered_by,

pbh.submit_stage negotiation_stage,

(select meaning 

 from   lookup_values 

 where  lookup_type='YES_NO'

 and    view_application_id=0

 and    lookup_code=paip.no_award) award,

(select meaning 

 from   lookup_values 

 where  lookup_type='PON_AWARD_STATUS'

 and    lookup_code=paip.award_status ) award_status,

null award_status_description,

--TO_CHAR(paha.award_complete_date, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') award_completion_date,

paha.award_complete_date as award_completion_date,

--TO_CHAR(paha.award_approval_date, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') award_approved_date,

paha.award_approval_date as award_approved_date,

pha.segment1 purchase_order,

pha.document_status purchase_order_status,

paha.total_savings_amount Savings,

((paha.approval_amount-paha.total_savings_amount)*100/paha.total_savings_amount) savings_percentage,

ppa.segment1 project_number,

(SELECT task_number FROM pjf_tasks_v ptv WHERE ptv.task_id = pda.pjc_task_id)task_number,

(SELECT task_name FROM pjf_tasks_v ptv WHERE ptv.task_id = pda.pjc_task_id)task_name,

abs(TRUNC(paha.award_approval_date) - TRUNC(paha.award_complete_date)) negotiation_award_cycle_days,

abs(TRUNC(paha.open_bidding_date) - TRUNC(paha.close_bidding_date))  negotiation_bidding_cycle_days,

ABS(trunc(paha.publish_date) - trunc(paha.close_bidding_date)) as Negotiation_publication_to_completion_dates,

ABS(TRUNC(paha.open_bidding_date)-TRUNC(paha.close_bidding_date))as negotiation_bidding_cycle

FROM 

pon_bid_headers              pbh,

pon_auction_headers_all      paha,

pon_auction_item_prices      paip,

poz_suppliers_v              ps,

poz_supplier_sites_v         pssv,

po_headers_all               pha,

po_distributions_all         pda,

por_req_distributions_all     prda,

por_requisition_lines_all     prla,

por_requisition_headers_all   prha,

pon_bid_po_numbers           pnpo,

pjf_projects_all_vl          ppa,

fun_all_business_units_v     fabu,

PON_DOCTYPE_STYLES_VL padb,

pon_bid_attribute_values     pbav,

pon_negotiation_styles_tl pnst

WHERE 1=1

AND pnst.style_id=paha.style_id

AND pnst.LANGUAGE = 'US'

--AND paha.document_number='ITNEG10030,1'

AND pbh.bid_status <> 'DRAFT'

AND paha.auction_header_id = pbh.auction_header_id

AND paha.auction_header_id = paip.auction_header_id

AND ps.vendor_id = pbh.vendor_id

AND pssv.vendor_site_id = pbh.vendor_site_id

AND pha.po_header_id = pda.po_header_id(+)

AND pda.req_distribution_id = prda.distribution_id(+)

AND prda.requisition_line_id = prla.requisition_line_id(+)

AND prla.requisition_header_id = prha.requisition_header_id(+)

AND pnpo.po_header_id = pha.po_header_id(+)

AND pnpo.auction_header_id(+) = pbh.auction_header_id

AND pda.pjc_project_id = ppa.project_id(+)

AND paha.PRC_BU_ID = fabu.bu_id

AND padb.doctype_id = paha.doctype_id

AND PADB.negotiation_display_name ='Auction'

--AND padb.LANGUAGE='US'

AND PADB.enabled_flag='Y'

AND paha.STYLE_ID=padb.STYLE_ID

AND paha.auction_header_id = pbav.auction_header_id(+)

AND (fabu.bu_id IN (:p_bu_id) OR COALESCE(:p_bu_id, NULL) IS NULL)--

AND (paha.auction_header_id IN (:p_auction_header_id) OR COALESCE(:p_auction_header_id, NULL) IS NULL)--

--AND (padb.doctype_id IN (:p_negotiation_type) OR COALESCE(:p_negotiation_type, NULL) IS NULL)--

AND (ps.vendor_id IN (:p_vendor_id) OR COALESCE(:p_vendor_id, NULL) IS NULL)--

AND (ppa.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)--

AND (pha.agent_id IN (:p_buyer_id) OR COALESCE(:p_buyer_id, NULL) IS NULL)--

AND (prha.requisition_header_id  IN (:p_requisition_header_id) OR COALESCE(:p_requisition_header_id, NULL) IS NULL)

AND (paha.requested_by IN (:p_requestor) OR COALESCE(:p_requestor, NULL) IS NULL)

AND (pbh.bid_number IN (:p_bid_num) OR COALESCE(:p_bid_num, NULL) IS NULL)

AND (paha.auction_status IN (:p_neg_status) OR COALESCE(:p_neg_status, NULL) IS NULL)

AND (paha.award_status IN (:p_award_status) OR COALESCE(:p_award_status, NULL) IS NULL)

AND TRUNC(paha.award_complete_date) BETWEEN NVL(:p_dt_from,paha.award_complete_date) AND NVL(:p_dt_to,paha.award_complete_date)

Query to get Negotiation , Bid and RFQ Details (RFQ Details Buyer )

 -- RFQ DETAILS

WITH LOOKUP_VALUES AS ( 

    SELECT LOOKUP_TYPE, 

           LOOKUP_CODE, 

           MEANING,

           VIEW_APPLICATION_ID

    FROM   FND_LOOKUP_VALUES_VL FLVL

    WHERE  FLVL.LOOKUP_TYPE IN ('PON_AUCTION_STATUS','PON_AWARD_STATUS','YES_NO','PON_CONTRACT_TYPE')

    AND    FLVL.ENABLED_FLAG='Y'

    AND    SYSDATE BETWEEN NVL(FLVL.START_DATE_ACTIVE,TO_DATE('01/01/1951','DD/MM/YYYY')) AND NVL(FLVL.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY')))

SELECT 

      BUSINESS_UNIT,

  RFQ_NUMBER,

  NEGOTIATION_TITLE,

  NEGOTIATION_SYNOPSIS,

  NEGOTIATION_TYPE,

  NEGOTIATION_STYLE,

      NEGOTIATION_STATUS,

      NEGOTIATION_CURRENCY,

  OUTCOME_DOCUMENT_TYPE,

      NEGOTIATION_CREATION_DATE,

  NEGOTIATION_PREVIEW_DATE,

  NEGOTIATION_OPEN_DATE,

  TIME_REMAINING,

      NEGOTIATION_CLOSE_DATE ,

  BUYER, 

      AWARD,

      AWARD_DATE,

      AWARD_NUMBER,

      AWARD_STATUS,

      AWARD_APPROVED_DATE,

  AWARDED_SUPPLIER,

  PROJECT_NUMBER,

  PROJECT_NAME,

  PROJECT_MANAGER,

  RELATED_REQUISITION_NUMBER,   

  ISSUED_PO_NUMBER,  

      COUNT(TOTAL_NO_OF_BIDS) AS TOTAL_NO_OF_BIDS,   

  COUNT(NUMBER_OF_AWARDS) NUMBER_OF_AWARDS,

  COUNT(TOTAL_ISSUED_RFQ_SUPPLIERS) AS TOTAL_ISSUED_RFQ_SUPPLIERS,

  COUNT(TOTAL_ISSUED_POS) AS TOTAL_ISSUED_POS ,

  COUNT(TOTAL_NUMBER_OF_SUPPLIERS_INVITED) AS TOTAL_NUMBER_OF_SUPPLIERS_INVITED,

      COUNT(TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED) AS TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED,

  NEGOTIATION_PUBLISH_DATE,

  NEGOTIATION_CYCLE_TIME,

  NEGOTIATION_AWARDCYCLE_TIME,

  NEGOTIATION_BIDDINGCYCLE_TIME,

  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS,

  BUYER_ENTITY,

  REQUESTER

  --SUPPLIER

  

  

FROM 

( 

SELECT DISTINCT

FBU.BU_NAME                                                                                     AS  BUSINESS_UNIT,

PAH.DOCUMENT_NUMBER                                                                             AS  RFQ_NUMBER,

PAH.AUCTION_TITLE                                                                               AS  NEGOTIATION_TITLE,

PAH.SYNOPSIS                                                                                    AS  NEGOTIATION_SYNOPSIS,

PADV.NAME                                                                                       AS NEGOTIATION_TYPE,

PNSV.STYLE_NAME                                                                                 AS NEGOTIATION_STYLE,

PAH.AUCTION_STATUS   AS NEGOTIATION_STATUS, -- COULDN'T FIND LOOKUP ENTRY FOR THIS... 

PAH.CURRENCY_CODE                                                                               AS NEGOTIATION_CURRENCY,

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='PON_CONTRACT_TYPE'

 AND    LOOKUP_CODE=PAH.OUTCOME_STATUS ) AS OUTCOME_DOCUMENT_TYPE,

PAH.CREATION_DATE                                                                               AS NEGOTIATION_CREATION_DATE,

PAH.VIEW_BY_DATE                                                                                AS NEGOTIATION_PREVIEW_DATE,

PAH.OPEN_BIDDING_DATE                                                                           AS NEGOTIATION_OPEN_DATE,    

 ABS(TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED)-TRUNC(SYSDATE))  AS TIME_REMAINING,    

PAH.CLOSE_DATE_PAUSE_ADJUSTED                                                                          AS      NEGOTIATION_CLOSE_DATE,

(SELECT DISTINCT PPNF.DISPLAY_NAME     

FROM PER_PERSON_NAMES_F PPNF,

PON_BUYER_ACTIVITIES PBA

WHERE 

PBA.AUCTION_HEADER_ID = PAH.AUCTION_HEADER_ID

AND PBA.ACTIVITY_OWNER_ID = PPNF.PERSON_ID

AND PPNF.NAME_TYPE='GLOBAL'

AND PBA.ACTIVITY_CODE = 'NEW_COLLAB_TEAM_ASSIGNMENT'

AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE)                                              AS BUYER, 

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='YES_NO'

 AND    VIEW_APPLICATION_ID=0

 AND    LOOKUP_CODE=PAIP.NO_AWARD ) AS AWARD,

PAH.AWARD_DATE                                                                                 AS   AWARD_DATE,

PAH.SOURCE_DOC_NUMBER                                                                                       AS       AWARD_NUMBER,

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='PON_AWARD_STATUS'

 AND    LOOKUP_CODE=PAH.AWARD_STATUS ) AS AWARD_STATUS,

PAH.AWARD_APPROVAL_DATE                                                                                    AS             AWARD_APPROVED_DATE, 

CASE WHEN PAH.AWARD_STATUS='AWARDED' THEN PSV.VENDOR_NAME ELSE NULL END                        AS AWARDED_SUPPLIER,

PPV.SEGMENT1                                                                                               AS PROJECT_NUMBER,

PPV.NAME                                                                                                   AS PROJECT_NAME,

(SELECT PPNF.DISPLAY_NAME     

FROM   PJF_PROJECT_MEMBERS_V PPMV, 

       PJF_PROJ_ROLE_TYPES_VL PPRTV,

       PER_PERSON_NAMES_F PPNF                                                  

WHERE  PPNF.PERSON_ID=PPMV.PERSON_ID

AND    PPRTV.PROJECT_ROLE_ID=PPMV.PROJECT_ROLE_ID

AND    PPRTV.PROJECT_ROLE_NAME='PROJECT MANAGER'

AND    PPNF.NAME_TYPE='GLOBAL'

AND    PPMV.PROJECT_ID = PPV.PROJECT_ID

AND    SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND    SYSDATE BETWEEN PPMV.START_DATE_ACTIVE AND NVL(PPMV.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY'))

AND    SYSDATE BETWEEN PPRTV.START_DATE_ACTIVE AND NVL(PPRTV.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY'))

)                                                                              AS PROJECT_MANAGER,

PRHA.REQUISITION_NUMBER                                                                                            AS RELATED_REQUISITION_NUMBER,                                                                                                                                                      

POH.SEGMENT1                                                                                                        AS   ISSUED_PO_NUMBER,

PBH.BID_NUMBER                                                                                                 AS TOTAL_NO_OF_BIDS,                                                                         

PAIP.AWARDED_QUANTITY                                                                                                    AS NUMBER_OF_AWARDS,

PAIP.RECOMMENDED_AWARD_AMOUNT                                                                              AS TOTAL_ISSUED_RFQ_SUPPLIERS,

POH.SEGMENT1                                                                                                         AS TOTAL_ISSUED_POS,

PSV.VENDOR_ID                                                                             AS TOTAL_NUMBER_OF_SUPPLIERS_INVITED,

PBH.ORIGINAL_BID_NUMBER                                                                     AS TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED,

PAH.PUBLISH_DATE                                                                                 AS NEGOTIATION_PUBLISH_DATE,

ABS(TRUNC(PAH.CREATION_DATE)-TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED))                                  AS NEGOTIATION_CYCLE_TIME,

ABS(TRUNC(PAH.AWARD_APPROVAL_DATE)-TRUNC(PAH.AWARD_COMPLETE_DATE))                                  AS NEGOTIATION_AWARDCYCLE_TIME,

ABS(TRUNC(PAH.OPEN_BIDDING_DATE)-TRUNC(PAH.CLOSE_BIDDING_DATE))                                 AS NEGOTIATION_BIDDINGCYCLE_TIME,

ABS(TRUNC(PAH.PUBLISH_DATE)-TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED))                               AS  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS, 

XLE.NAME                                                                                               AS BUYER_ENTITY,

(SELECT PPNF.DISPLAY_NAME     

FROM PER_PERSON_NAMES_F PPNF

WHERE PPNF.PERSON_ID=PRLA.REQUESTER_ID 

AND PPNF.NAME_TYPE='GLOBAL'

AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE)                                               AS REQUESTER 

--PSV.VENDOR_NAME AS                                                                                                SUPPLIER

FROM 

PON_AUCTION_HEADERS_ALL PAH,

FUN_NAMES_BUSINESS_UNITS_V FBU ,

PON_BID_HEADERS PBH ,

POZ_SUPPLIERS_V PSV  ,

 PON_AUC_DOCTYPES_VL PADV ,

 PON_NEGOTIATION_STYLES_VL PNSV ,

 PO_HEADERS_ALL POH ,

 PO_DISTRIBUTIONS_ALL         PDA,

PJF_PROJECTS_ALL_VL PPV ,

PON_BID_PO_NUMBERS PBPN  ,

PON_AUCTION_ITEM_PRICES PAIP ,

POR_REQUISITION_LINES_ALL PRLA ,

POR_REQUISITION_HEADERS_ALL PRHA ,

  XLE_ENTITY_PROFILES XLE 

 

--PO_LINES_ALL PLA,  

WHERE 1=1

AND PAH.PRC_BU_ID = FBU.BU_ID

AND PAH.AUCTION_HEADER_ID = PBH.AUCTION_HEADER_ID(+)

AND PBH.VENDOR_ID = PSV.VENDOR_ID(+) 

AND PAH.DOCTYPE_ID  = PADV.DOCTYPE_ID(+) 

AND PADV.NAME='RFQ'  

AND PAH.STYLE_ID=PNSV.STYLE_ID(+) 

AND PAH.AUCTION_HEADER_ID=POH.PO_HEADER_ID(+)

AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID(+)

AND PDA.PJC_PROJECT_ID = PPV.PROJECT_ID(+) 

--AND PAH.PROJECT_ID = PPV.PROJECT_ID(+)

AND PAH.AUCTION_HEADER_ID = PBPN.AUCTION_HEADER_ID(+) 

AND PAH.AUCTION_HEADER_ID(+) = PAIP.AUCTION_HEADER_ID

AND PAH.AUCTION_HEADER_ID = PRLA.AUCTION_HEADER_ID(+)

AND PRLA.BID_NUMBER (+) = PBH.BID_NUMBER 

AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)

AND PRLA.PO_HEADER_ID = POH.PO_HEADER_ID(+) 

AND PBPN.PO_HEADER_ID = POH.PO_HEADER_ID(+)

AND POH.SOLDTO_LE_ID = XLE.LEGAL_ENTITY_ID(+) 

--AND POH.PO_HEADER_ID=PLA.PO_HEADER_ID

--AND PBH.BID_NUMBER = PAIP.BEST_BID_BID_NUMBER(+)

--AND PAH.DOCUMENT_NUMBER ='ITNEG10021,1'

AND TRUNC(NVL(PAH.CREATION_DATE,SYSDATE)) BETWEEN NVL(:P_RFQ_CREATION_DATE_FROM,TRUNC(SYSDATE-999)) AND NVL(:P_RFQ_CREATION_DATE_TO,TRUNC(SYSDATE+999))

AND (FBU.BU_ID IN (:P_BUSINESS_UNIT_ID) OR COALESCE(:P_BUSINESS_UNIT_ID,NULL) IS NULL)

AND (PSV.VENDOR_ID IN (:P_VENDOR_ID) OR COALESCE(:P_VENDOR_ID,NULL) IS NULL)

AND (PPV.PROJECT_ID IN (:P_PROJECT_ID) OR COALESCE (:P_PROJECT_ID,NULL) IS NULL) 

AND (PAH.AUCTION_HEADER_ID IN (:P_AUCTION_HEADER_ID) OR COALESCE(:P_AUCTION_HEADER_ID,NULL) IS NULL)

AND (PRHA.REQUISITION_HEADER_ID IN (:P_REQUISITION_HEADER_ID) OR COALESCE(:P_REQUISITION_HEADER_ID,NULL) IS NULL)

AND  (XLE.LEGAL_ENTITY_ID IN (:P_LEGAL_ENTITY_ID) OR COALESCE (:P_LEGAL_ENTITY_ID,NULL) IS NULL)

)

WHERE 1=1

AND (PROJECT_MANAGER IN (:P_PROJECT_MANAGER_SOURCE_ID) OR COALESCE (:P_PROJECT_MANAGER_SOURCE_ID,NULL) IS NULL)

AND (BUYER IN (:P_ACTIVITY_OWNER_ID) OR COALESCE (:P_ACTIVITY_OWNER_ID,NULL) IS NULL)

GROUP BY

      BUSINESS_UNIT,

  RFQ_NUMBER,

  NEGOTIATION_TITLE,

  NEGOTIATION_SYNOPSIS,

  NEGOTIATION_TYPE,

  NEGOTIATION_STYLE,

      NEGOTIATION_STATUS,

      NEGOTIATION_CURRENCY,

  OUTCOME_DOCUMENT_TYPE,

      NEGOTIATION_CREATION_DATE,

  NEGOTIATION_PREVIEW_DATE,

  NEGOTIATION_OPEN_DATE,

  TIME_REMAINING,

      NEGOTIATION_CLOSE_DATE,

  BUYER, 

      AWARD,

      AWARD_DATE,      

      AWARD_NUMBER,

      AWARD_STATUS,

      AWARD_APPROVED_DATE,

  AWARDED_SUPPLIER,

  PROJECT_NUMBER,

  PROJECT_NAME,

  PROJECT_MANAGER,

  RELATED_REQUISITION_NUMBER,   

  ISSUED_PO_NUMBER,  

  NEGOTIATION_PUBLISH_DATE,

  NEGOTIATION_CYCLE_TIME,

  NEGOTIATION_AWARDCYCLE_TIME,

  NEGOTIATION_BIDDINGCYCLE_TIME,

  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS,

  BUYER_ENTITY,

  REQUESTER

  --SUPPLIER

ORDER BY

  BUSINESS_UNIT,

  RFQ_NUMBER