CA_AP_SC_DDI_VW

(SQL View)

SELECT DISTINCT A.CONTRACT_NUM , B.CONTRACT_LINE_NUM , B.ACCT_PLAN_ID , E.EVENT_NUM , E.ACCOUNTING_DT , E.AMOUNT ,E.CA_PER_COMPLETE , F.DST_SEQ_NUM , F.DST_ID , F.BUSINESS_UNIT_GL , G.SEQ_NUM , F.ACCOUNT , F.ALTACCT , F.DEPTID ,%subrec(CF16_AN_SBR,F) ,%subrec(PC_CF1_N_SBR,F) , F.STATISTICS_CODE , F.CA_PERCENTAGE , G.AMOUNT , B.GROSS_AMT , B.CURRENCY_CD , G.DISC_SUR_INDICATOR FROM PS_CA_CONTR_HDR A , PS_CA_DETAIL B , PS_CA_ACCTPLAN D , PS_CA_AP_EVENT E , PS_CA_DETAIL_DFR F , PS_CA_LN_REDUCTS G WHERE A.CONTRACT_NUM = B.CONTRACT_NUM AND B.CONTRACT_NUM = D.CONTRACT_NUM AND D.CONTRACT_NUM = E.CONTRACT_NUM AND D.ACCT_PLAN_ID = E.ACCT_PLAN_ID AND B.ACCT_PLAN_ID = D.ACCT_PLAN_ID AND A.CONTRACT_NUM = F.CONTRACT_NUM AND B.CONTRACT_LINE_NUM = F.CONTRACT_LINE_NUM AND A.CONTRACT_NUM = G.CONTRACT_NUM AND F.CONTRACT_LINE_NUM = G.CONTRACT_LINE_NUM AND (E.AP_EVENT_STATUS = 'RDY' OR E.AP_EVENT_STATUS = 'NEW') AND F.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CA_DETAIL_DFR Z WHERE Z.CONTRACT_NUM = F.CONTRACT_NUM AND Z.CONTRACT_LINE_NUM = F.CONTRACT_LINE_NUM AND Z.EFFDT <= E.ACCOUNTING_DT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

2 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num

3 ACCT_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Revenue Recognition Plan

4 EVENT_NUM Number(3,0) SMALLINT NOT NULL Event Number

5 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

6 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field

7 CA_PER_COMPLETE Number(12,8) DECIMAL(11,8) NOT NULL Percent Complete

8 DST_SEQ_NUM Number(6,0) INTEGER NOT NULL Distribution Sequence

9 DST_ID Character(10) VARCHAR2(10) NOT NULL Distribution Code

10 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

11 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

12 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

13 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account

14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

15 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
Prompt Table: OPERUNT_BUGL_VW

16 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
Prompt Table: PRODUCT_BUGL_VW

17 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
Prompt Table: FUND_BUGL_VW

18 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
Prompt Table: CLASSCF_BUGL_VW

19 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
Prompt Table: PROGRAM_BUGL_VW

20 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
Prompt Table: BUD_REF_BUGL_VW

21 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
Prompt Table: AFFILIATE_VW

22 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
Prompt Table: %EDIT_INTRA01

23 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
Prompt Table: %EDIT_INTRA02

24 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
Prompt Table: CF1_BUGL_VW

25 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
Prompt Table: CF2_BUGL_VW

26 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
Prompt Table: CF3_BUGL_VW

27 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
Prompt Table: %EDIT_BU_PC

28 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
Prompt Table: %EDIT_PROJECT

29 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
Prompt Table: %EDIT_ACTIVITY

30 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

31 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

32 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

33 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

34 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code

35 CA_PERCENTAGE Signed Number(13,8) DECIMAL(11,8) NOT NULL Percentage

36 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount

37 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount

38 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

39 DISC_SUR_INDICATOR Character(1) VARCHAR2(1) NOT NULL Discount/Surcharge
D=Discount
I=Installment Surcharge
S=Surcharge

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract
2	CONTRACT_LINE_NUM	Number(3,0)	SMALLINT NOT NULL	Contract Line Num
3	ACCT_PLAN_ID	Character(10)	VARCHAR2(10) NOT NULL	Revenue Recognition Plan
4	EVENT_NUM	Number(3,0)	SMALLINT NOT NULL	Event Number
5	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
6	AMOUNT_1	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount Work Field
7	CA_PER_COMPLETE	Number(12,8)	DECIMAL(11,8) NOT NULL	Percent Complete
8	DST_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Distribution Sequence
9	DST_ID	Character(10)	VARCHAR2(10) NOT NULL	Distribution Code
10	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
11	SEQ_NUM	Number(3,0)	SMALLINT NOT NULL	Sequence
12	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
13	ALTACCT	Character(10)	VARCHAR2(10) NOT NULL	Alternate Account
14	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
15	OPERATING_UNIT	Character(8)	VARCHAR2(8) NOT NULL	Operating Unit ChartField Prompt Table: OPERUNT_BUGL_VW
16	PRODUCT	Character(6)	VARCHAR2(6) NOT NULL	Product ChartField Prompt Table: PRODUCT_BUGL_VW
17	FUND_CODE	Character(5)	VARCHAR2(5) NOT NULL	Fund Code Prompt Table: FUND_BUGL_VW
18	CLASS_FLD	Character(5)	VARCHAR2(5) NOT NULL	Class Field Prompt Table: CLASSCF_BUGL_VW
19	PROGRAM_CODE	Character(5)	VARCHAR2(5) NOT NULL	Program Code ChartField Prompt Table: PROGRAM_BUGL_VW
20	BUDGET_REF	Character(8)	VARCHAR2(8) NOT NULL	Budget Reference Prompt Table: BUD_REF_BUGL_VW
21	AFFILIATE	Character(5)	VARCHAR2(5) NOT NULL	Affiliate Prompt Table: AFFILIATE_VW
22	AFFILIATE_INTRA1	Character(10)	VARCHAR2(10) NOT NULL	IntraUnit Affiliate1 Prompt Table: %EDIT_INTRA01
23	AFFILIATE_INTRA2	Character(10)	VARCHAR2(10) NOT NULL	Operating Unit Affiliate Prompt Table: %EDIT_INTRA02
24	CHARTFIELD1	Character(10)	VARCHAR2(10) NOT NULL	Expansion chartfield 1 Prompt Table: CF1_BUGL_VW
25	CHARTFIELD2	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 2 Prompt Table: CF2_BUGL_VW
26	CHARTFIELD3	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 3 Prompt Table: CF3_BUGL_VW
27	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	PC Business Unit Prompt Table: %EDIT_BU_PC
28	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField Prompt Table: %EDIT_PROJECT
29	ACTIVITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Activity ID Prompt Table: %EDIT_ACTIVITY
30	RESOURCE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Source Type Prompt Table: PROJ_RESTYPE_FS Set Control Field: BUSINESS_UNIT_PC
31	RESOURCE_CATEGORY	Character(5)	VARCHAR2(5) NOT NULL	Category Prompt Table: %EDIT_RES_CAT Set Control Field: BUSINESS_UNIT_PC
32	RESOURCE_SUB_CAT	Character(5)	VARCHAR2(5) NOT NULL	Subcategory Prompt Table: %EDIT_RES_SUB Set Control Field: BUSINESS_UNIT_PC
33	ANALYSIS_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Analysis Type Prompt Table: %EDIT_ANALYSIS Set Control Field: BUSINESS_UNIT_PC
34	STATISTICS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Statistics Code
35	CA_PERCENTAGE	Signed Number(13,8)	DECIMAL(11,8) NOT NULL	Percentage
36	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
37	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
38	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
39	DISC_SUR_INDICATOR	Character(1)	VARCHAR2(1) NOT NULL	Discount/Surcharge D=Discount I=Installment Surcharge S=Surcharge