-- 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
No comments:
Post a Comment