CNTRCT_ACT_PMT1 (SQL View)	Index Back
Contract Activity - Payment 07/24/2009 PKL 1905919001 Ensured that contract activity report show current version number

CNTRCT_ACT_PMT1

(SQL View)

Contract Activity - Payment

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

SELECT DISTINCT D.VENDOR_SETID , D.VENDOR_ID , A.SETID , A.CNTRCT_ID , A.VERSION_NBR , 0 , A.BUSINESS_UNIT , A.VOUCHER_ID , B.PYMNT_CNT , 'PMT' , C.PYMNT_DT , C.CURRENCY_PYMNT , B.PAID_AMT , 'PYMNT_STATUS' , C.PYMNT_STATUS , D.CNTRCT_PROC_OPT , D.CNTRCT_STATUS FROM PS_CNTRCT_EVT_VCHR A , PS_PYMNT_VCHR_XREF B , PS_PAYMENT_TBL C , PS_CNTRCT_HDR D WHERE %Join(COMMON_KEYS, CNTRCT_EVT_VCHR A, PYMNT_VCHR_XREF B) AND C.BANK_SETID = B.BANK_SETID AND C.BANK_CD = B.BANK_CD AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY AND C.PYMNT_ID = B.PYMNT_ID AND %Join(COMMON_KEYS, CNTRCT_EVT_VCHR A, CNTRCT_HDR D)

# 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

#	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