CNTRCT_ACT_RTV1

(SQL View)
 Index Back

Contract Activity - RTV

07/24/2009 PKL 1905919001 Ensured that contract activity report show current version number

SELECT F.VENDOR_SETID , F.VENDOR_ID , A.SETID , A.CNTRCT_ID , A.VERSION_NBR , A.CNTRCT_LINE_NBR , D.BUSINESS_UNIT , D.RTV_ID , D.RTV_LN_NBR , 'RTV' , MAX(E.ENTERED_DT) , MAX(D.CURRENCY_CD) , SUM(%Round(%DecMult(D.PRICE_PO, D.QTY_LN_RETRN_VUOM), 3)) , 'RTV_STATUS' , MAX(E.RTV_STATUS) , MAX(F.CNTRCT_PROC_OPT) , MAX(F.CNTRCT_STATUS) FROM PS_CNTRCT_EVT_PO A , PS_PO_LINE B , PS_RECV_LN_SHIP C , PS_RTV_LN D , PS_RTV_HDR E , PS_CNTRCT_HDR F WHERE %Join(COMMON_KEYS, CNTRCT_EVT_PO A, PO_LINE B) AND B.CANCEL_STATUS <> 'X' AND C.BUSINESS_UNIT_PO = B.BUSINESS_UNIT AND C.PO_ID = B.PO_ID AND C.LINE_NBR = B.LINE_NBR AND C.RECV_SHIP_STATUS <> 'X' AND D.BUSINESS_UNIT_RECV = C.BUSINESS_UNIT AND D.RECEIVER_ID = C.RECEIVER_ID AND D.RECV_LN_NBR = C.RECV_LN_NBR AND D.RECV_SHIP_SEQ_NBR = C.RECV_SHIP_SEQ_NBR AND D.RTV_LN_STATUS <> 'X' AND %Join(COMMON_KEYS, RTV_LN D, RTV_HDR E) AND %Join(COMMON_KEYS, CNTRCT_EVT_PO A, CNTRCT_HDR F) GROUP BY F.VENDOR_SETID , F.VENDOR_ID , A.SETID , A.CNTRCT_ID, A.VERSION_NBR , A.CNTRCT_LINE_NBR , D.BUSINESS_UNIT , D.RTV_ID , D.RTV_LN_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
5 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
6 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
8 CNT_ACTRPT_ID Character(15) VARCHAR2(15) NOT NULL Used as the Document ID for the Contract Activity Report.
9 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
10 PRCR_DOC_TYPE Character(3) VARCHAR2(3) NOT NULL Document Type
CNT=Contract
IUR=Inter Unit Receipt
MSR=MSR
PMT=Payment
PO=Purchase Order
RCV=Receipt
REQ=Requisition
RFQ=Request For Quote
RTV=Return To Vendor (Supplier)
SSE=Strategic Sourcing Event
VOU=Voucher
WO=Work Order
11 DOCSTAT_DATE Date(10) DATE Document Date
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
14 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
15 STATUS_TEXT Character(4) VARCHAR2(4) NOT NULL Status Text
16 CNTRCT_PROC_OPT Character(4) VARCHAR2(4) NOT NULL Contract Process Option
AP=Recurring Voucher
BPO=Release to Single PO Only
DST=Distributor
GN=General Contract
GRPM=Group Multi Supplier
GRPS=Group Single Supplier
MFG=Manufacturer
PADV=Prepaid Voucher w/ Advance PO
PO=Purchase Order
PPAY=Prepaid Voucher
RPOV=Recurring PO Voucher
SPP=Special Purpose
17 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled