PV_PRC_DSCRP_VW

(SQL View)
 Index Back

Contract Price Discrepancy


SELECT D.BUSINESS_UNIT ,D.DOC_ID ,D.DOC_TYPE_PUR ,D.LINE_NBR ,D.SCHED_LINE_NBR ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RH.REQ_DT WHEN 'PO' THEN PH.PO_DT END ,D.TRANSACTION_PRICE ,D.PRICE_INITIAL ,D.CURRENCY_CD ,D.CNTRCT_SETID ,D.CNTRCT_ID ,D.VERSION_NBR ,D.CNTRCT_LINE_NBR ,D.PRICE_ADJUSTED ,D.CURRENCY_CD_CNTR ,D.VENDOR_SETID ,D.VENDOR_ID ,' ' ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.DESCR254_MIXED WHEN 'PO' THEN PL.DESCR254_MIXED END ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.ITM_SETID WHEN 'PO' THEN PL.ITM_SETID END ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.CATEGORY_ID WHEN 'PO' THEN PL.CATEGORY_ID END ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.MFG_ITM_ID WHEN 'PO' THEN PL.MFG_ITM_ID END ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.MFG_ID WHEN 'PO' THEN PL.MFG_ID END ,CASE D.DOC_TYPE_PUR WHEN 'REQ' THEN RL.ITM_ID_VNDR WHEN 'PO' THEN PL.ITM_ID_VNDR END ,D.DISCRP_MOD_DTTM FROM PS_PUR_PRICE_DSCRP D LEFT OUTER JOIN PS_REQ_HDR RH ON D.BUSINESS_UNIT = RH.BUSINESS_UNIT AND D.DOC_TYPE_PUR = 'REQ' AND D.DOC_ID = RH.REQ_ID LEFT OUTER JOIN PS_REQ_LINE RL ON RL.BUSINESS_UNIT = RH.BUSINESS_UNIT AND RL.REQ_ID = RH.REQ_ID AND D.BUSINESS_UNIT = RL.BUSINESS_UNIT AND D.DOC_ID = RL.REQ_ID AND D.LINE_NBR = RL.LINE_NBR LEFT OUTER JOIN PS_PO_HDR PH ON D.BUSINESS_UNIT = PH.BUSINESS_UNIT AND D.DOC_TYPE_PUR = 'PO' AND D.DOC_ID = PH.PO_ID LEFT OUTER JOIN PS_PO_LINE PL ON PL.BUSINESS_UNIT = PH.BUSINESS_UNIT AND PL.PO_ID = PH.PO_ID AND D.BUSINESS_UNIT = PL.BUSINESS_UNIT AND D.DOC_ID = PL.PO_ID AND D.LINE_NBR = PL.LINE_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DOC_ID Character(50) VARCHAR2(50) NOT NULL ID
3 DOC_TYPE_PUR Character(3) VARCHAR2(3) NOT NULL Document Type
PO=Purchase Order
REQ=Requisition
4 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
5 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
6 DOC_DATE Date(10) DATE Document Date
7 TRANSACTION_PRICE Signed Number(28,3) DECIMAL(26,3) NOT NULL The preferred business price for a given deal transaction.
8 PRICE_INITIAL Signed Number(17,5) DECIMAL(15,5) NOT NULL Punchout Price
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
10 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
11 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
12 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number

Default Value: 1

13 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
14 PRICE_ADJUSTED Signed Number(17,5) DECIMAL(15,5) NOT NULL Adjusted Price
15 CURRENCY_CD_CNTR Character(3) VARCHAR2(3) NOT NULL Contract Base Currency
16 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
17 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
18 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
19 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
20 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
21 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID

Prompt Table: ITM_CAT_VW

22 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID

Prompt Table: %EDIT_MFGITM_ID

23 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID

Prompt Table: MANUFACTURER

24 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
25 DISCRP_MOD_DTTM DateTime(26) TIMESTAMP Price Discrepancy TimeStamp