CA_RECON_FF_AVW

(SQL View)
 Index Back

Contracts Fixed Amt. Acctg Dis

Contracts Fixed Amount Accounting Distribution.

( SELECT DET.BUNDLE_OFFERING ,DST.CONTRACT_NUM , DST.CONTRACT_LINE_NUM , 'REV' AS CA_DISTRIBUTION , DST.EFFDT , DST.CA_PERCENTAGE , DST.AMOUNT AS GROSS_AMT_REV , 0 AS GROSS_AMT , DST.CURRENCY_CD , DST.BUSINESS_UNIT_GL , DST.DST_ID , DST.ACCOUNT , DST.ALTACCT , DST.DEPTID ,%subrec(CFS16_AN_SBR,DST) ,%subrec(PC_CF1_N_SBR,DST) , DST.STATISTICS_CODE , DST.COMBO_VALID_FLG FROM PS_CA_DETAIL_DST DST , PS_CA_DETAIL DET WHERE DST.CONTRACT_NUM = DET.CONTRACT_NUM AND DST.CONTRACT_LINE_NUM = DET.CONTRACT_LINE_NUM AND DET.PRICING_STRUCTURE = 'AMT' AND DST.EFFDT = ( SELECT MAX(DST1.EFFDT) FROM PS_CA_DETAIL_DST DST1 WHERE DST.CONTRACT_NUM = DST1.CONTRACT_NUM AND DST.CONTRACT_LINE_NUM = DST1.CONTRACT_LINE_NUM AND DST1.EFFDT <= %CurrentDateIn) UNION ALL SELECT DET.BUNDLE_OFFERING ,DFR.CONTRACT_NUM , DFR.CONTRACT_LINE_NUM , 'DFR' AS CA_DISTRIBUTION , DFR.EFFDT , DFR.CA_PERCENTAGE , DFR.AMOUNT AS GROSS_AMT_REV , 0 AS GROSS_AMT , DFR.CURRENCY_CD , DFR.BUSINESS_UNIT_GL , DFR.DST_ID , DFR.ACCOUNT , DFR.ALTACCT , DFR.DEPTID ,%subrec(CFS16_AN_SBR,DFR) ,%subrec(PC_CF1_N_SBR,DFR) , DFR.STATISTICS_CODE , DFR.COMBO_VALID_FLG FROM PS_CA_DETAIL_DFR DFR , PS_CA_DETAIL DET WHERE DFR.CONTRACT_NUM = DET.CONTRACT_NUM AND DFR.CONTRACT_LINE_NUM = DET.CONTRACT_LINE_NUM AND DET.PRICING_STRUCTURE = 'AMT' AND DFR.EFFDT = ( SELECT MAX(DFR1.EFFDT) FROM PS_CA_DETAIL_DFR DFR1 WHERE DFR.CONTRACT_NUM = DFR1.CONTRACT_NUM AND DFR.CONTRACT_LINE_NUM = DFR1.CONTRACT_LINE_NUM AND DFR1.EFFDT <=%CurrentDateIn) UNION ALL SELECT DET.BUNDLE_OFFERING ,UAR.CONTRACT_NUM , UAR.CONTRACT_LINE_NUM , 'UAR' AS CA_DISTRIBUTION , UAR.EFFDT , UAR.CA_PERCENTAGE , DET.GROSS_AMT_REV AS GROSS_AMT_REV , DET.GROSS_AMT AS GROSS_AMT , DET.CURRENCY_CD , UAR.BUSINESS_UNIT_GL , UAR.DST_ID , UAR.ACCOUNT , UAR.ALTACCT , UAR.DEPTID ,%subrec(CFS16_AN_SBR,UAR) ,%subrec(PC_CF1_N_SBR,UAR) , UAR.STATISTICS_CODE , UAR.COMBO_VALID_FLG FROM PS_CA_DETAIL_UAR UAR , PS_CA_DETAIL DET WHERE UAR.CONTRACT_NUM = DET.CONTRACT_NUM AND UAR.CONTRACT_LINE_NUM = DET.CONTRACT_LINE_NUM AND DET.PRICING_STRUCTURE = 'AMT' AND UAR.EFFDT = ( SELECT MAX(UAR1.EFFDT) FROM PS_CA_DETAIL_UAR UAR1 WHERE UAR.CONTRACT_NUM = UAR1.CONTRACT_NUM AND UAR.CONTRACT_LINE_NUM = UAR1.CONTRACT_LINE_NUM AND UAR1.EFFDT <= %CurrentDateIn) ) ORDER BY BUNDLE_OFFERING,CONTRACT_NUM,CONTRACT_LINE_NUM,CA_DISTRIBUTION

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUNDLE_OFFERING Character(20) VARCHAR2(20) NOT NULL Offering ID
2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

Prompt Table: CA_CONTR_HDR

3 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
4 CA_DISTRIBUTION Character(3) VARCHAR2(3) NOT NULL Distribution
DFR=Contract Liability
REV=Revenue
UAR=Contract Asset
5 EFFDT Date(10) DATE Effective Date
6 CA_PERCENTAGE Signed Number(13,8) DECIMAL(11,8) NOT NULL Percentage
7 GROSS_AMT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Revenue Amount
8 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
10 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
11 DST_ID Character(10) VARCHAR2(10) NOT NULL Distribution Code
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: %EDIT_OPER_UNIT

16 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: %EDIT_PRODUCT

17 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: %EDIT_FUND_CODE

18 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: %EDIT_CLASS_FLD

19 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: %EDIT_PROG_CODE

20 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: %EDIT_BUD_REF

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: %EDIT_CHARTFLD1

25 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: %EDIT_CHARTFLD2

26 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: %EDIT_CHARTFLD3

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 COMBO_VALID_FLG Character(1) VARCHAR2(1) NOT NULL ChartField Combinatn Valid Flg
?=Validation in Progress
V=Valid ChartField Combination
X=Invalid ChartField Combo