CA_TXN_RCR_VW

(SQL View)
 Index Back

SELECT TXN.CA_TXN_ID , LNK.CA_RCR_ID , TXN.FROM_DT , TXN.TO_DT , TXN.GL_DISTRIB_STATUS , TXN.BI_DISTRIB_STATUS , CRM.SETID_AGR , CRM.AGREEMENT_CODE , CRM.AGR_RENEWAL_NUM , CRM.AGREEMENT_LINE , CRM.BUSINESS_UNIT_RF , CRM.SO_ID , CRM.CASE_ID , CRM.BUSINESS_UNIT_RO , CRM.CAPTURE_ID , CRM.INST_PROD_ID , CRM.CA_TXN_TYPE , CRM.CA_TXN_SUB_TYPE , CRM.SERVICE_TYPE_CD , CRM.SERVICE_ID , CRM.PRODUCT_ID , CRM.RC_SOURCE , CRM.CASE_TYPE , CRM.EXP_TYPE , CRM.COST_CAT_CD , CRM.INV_ITEM_GROUP , CRM.INV_PROD_FAM_CD , TXN.AMOUNT , TXN.CURRENCY_CD FROM PS_CA_TXN_TBL TXN , PS_CA_TXN_CRM CRM , PS_CA_RCR_LNK LNK , PS_CA_RCR_HDR HDR WHERE TXN.CA_TXN_ID = CRM.CA_TXN_ID AND TXN.CA_TXN_ID = LNK.CA_TXN_ID AND LNK.CA_RCR_ID = HDR.CA_RCR_ID AND HDR.IN_USE_FLAG = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CA_TXN_ID Number(12,0) DECIMAL(12) NOT NULL Transaction ID
2 CA_RCR_ID Number(12,0) DECIMAL(12) NOT NULL Recurring Header ID
3 FROM_DT Date(10) DATE From Date
4 TO_DT Date(10) DATE To Date
5 GL_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal
6 BI_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Distribution Status
D=Distributed
I=Ignore
N=Not Distributed
P=Priced
U=Unbillable/Non-billable
W=Billing Worksheet
7 SETID_AGR Character(5) VARCHAR2(5) NOT NULL Agreement SetID
8 AGREEMENT_CODE Character(30) VARCHAR2(30) NOT NULL This field represents the agreement code (name) for an agreement
9 AGR_RENEWAL_NUM Number(4,0) SMALLINT NOT NULL This field represents the agreement renewal number (agreements begin at a value of one and the number is incremented with every renewal)
10 AGREEMENT_LINE Character(10) VARCHAR2(10) NOT NULL This field records the value for the agreement line number
11 BUSINESS_UNIT_RF Character(5) VARCHAR2(5) NOT NULL Field Services Business Unit
12 SO_ID Character(15) VARCHAR2(15) NOT NULL Service Order ID
13 CASE_ID Number(15,0) DECIMAL(15) NOT NULL Case ID. This is a unique ID assigned to a case when it is saved. Unique ID is ensured by getting the next value from the auto-numbering table and updating the table.
14 BUSINESS_UNIT_RO Character(5) VARCHAR2(5) NOT NULL Order Capture Business Unit
15 CAPTURE_ID Character(20) VARCHAR2(20) NOT NULL Order Capture ID
16 INST_PROD_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the installed product ID (key field)
17 CA_TXN_TYPE Character(3) VARCHAR2(3) NOT NULL CRM Transaction Type
01=Agreement
02=Service Order
03=Case
04=Order Capture
18 CA_TXN_SUB_TYPE Character(3) VARCHAR2(3) NOT NULL CRM Transaction Subtype
01=Fee
02=Uplift/Discount
03=Material
04=Labor
05=Expense
06=Onetime Fee
19 SERVICE_TYPE_CD Character(8) VARCHAR2(8) NOT NULL User-definable code to classify the type of service.
20 SERVICE_ID Character(20) VARCHAR2(20) NOT NULL Service ID
21 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
22 RC_SOURCE Character(5) VARCHAR2(5) NOT NULL Case Source How this case origianated
23 CASE_TYPE Character(5) VARCHAR2(5) NOT NULL Case Type
24 EXP_TYPE Character(3) VARCHAR2(3) NOT NULL This field is used to store the type of expense.
AIR=Airfare
ENT=Entertainment
FDB=Food - Breakfast
FDD=Food - Dinner
FDL=Food - Lunch
GAS=Gas
LOD=Lodging
MIL=Mileage
MIS=Miscellaneous
PAR=Parking
PHO=Phone
REN=Rental Car
TOL=Tolls
25 COST_CAT_CD Character(6) VARCHAR2(6) NOT NULL Code used to define cost categories.
26 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group
27 INV_PROD_FAM_CD Character(10) VARCHAR2(10) NOT NULL Family
28 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
29 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code