CS_CT_ACT_VOU2P

(SQL View)
 Index Back

Contract Activity - Voucher

Voucher Contract Activity for Parent Node 2/19/2008 RMK F-AHAYT-S60K7 Modified view text to join version_nbr between po_line and cntrct_line

SELECT E.SETID , E.CNTRCT_ID , E.CNTRCT_LINE_NBR , E.CNTRCT_ID , MAX(D.VENDOR_SETID) , MAX(D.VENDOR_ID) , 'VOU' , MAX(A.CURRENCY_CD) , SUM(A.MERCHANDISE_AMT) , MAX(D.CNTRCT_PROC_OPT) , MAX(D.CNTRCT_STATUS) , SUM(%Round(%DecDiv(A.MERCHANDISE_AMT , %DecDiv(C.CNTRCT_RATE_DIV , C.CNTRCT_RATE_MULT)) , 3)) , 0 FROM %Table(VOUCHER_LINE) A , %Table(VOUCHER) B , %Table(PO_LINE) C , %Table(CNTRCT_HDR) D , %Table(CNTRCT_LINE) E WHERE %Join(COMMON_KEYS, VOUCHER_LINE A, VOUCHER B) AND B.ENTRY_STATUS <> 'X' AND A.BUSINESS_UNIT_PO = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND A.LINE_NBR = C.LINE_NBR AND C.CNTRCT_SETID = E.SETID AND C.CNTRCT_ID = E.CNTRCT_ID AND C.CNTRCT_LINE_NBR = E.CNTRCT_LINE_NBR AND C.VERSION_NBR = E.VERSION_NBR AND %Join(COMMON_KEYS, CNTRCT_HDR D, CNTRCT_LINE E) GROUP BY E.SETID, E.CNTRCT_ID, E.CNTRCT_LINE_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
3 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
4 CS_CNTRCT_ID_PR Character(30) VARCHAR2(30) NOT NULL Parent Contract ID
5 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
6 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
7 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
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
9 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
10 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
11 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled
12 MERCH_AMT_CNTRCT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount In Contract Currency
13 AMT_LINE_RELEASED Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Line Amt Released