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)