SSF_SS_CONTR_VW

(SQL View)
 Index Back

Self Service Contract View

This view identifies all contracts associated with a business unit that belongs to an institution set.

SELECT B.INSTITUTION_SET , A.BUSINESS_UNIT , A.CONTRACT_NUM , C.INSTITUTION , A.STRM , A.TP_STATUS , A.ITEM_TYPE , A.PMT_ITEM_TYPE , A.ACCOUNT_TYPE_SF , A.TP_CON_LAST_COR_DT , A.TP_CON_BILL_DT , A.TP_DUE_DATE_DAYS , A.TP_CON_NUM_PAYMENT , A.TP_CON_ACCT_TYPE , A.TP_CON_BILL_CYCLE , A.TP_CON_PLAN_FEE , A.ITEM_TYPE_GROUP , A.TP_CON_PAY_CREDIT , A.TP_CON_PAY_CRE_TYP , A.PAY_PLAN_TYPE , A.TP_ASSESS_TYPE , A.TP_ASSESS_MNTH_INT , A.TP_ASSESS_FLAT_PER , A.TP_ASSESS_AMT , A.TP_ASSESS_FEE_SPLT , A.TP_ASSESS_ITEM_TYP , A.DESCR , A.CURRENCY_CD , A.DUE_DT , A.INTEREST_CALC_MTHD , A.INTEREST_RATE , A.INTEREST_ITEM_TYPE , A.ITEM_TYPE_GRP_OFF , A.MAX_TERM_AMT , A.MAX_AMT , A.TERM_FROM , A.TERM_TO , A.TERM_FROM_OFF , A.TERM_TO_OFF , A.ADD_CHRGS_DT , A.ADJ_CHRGS_DT , A.ADD_OFF_DT , A.ADJ_OFF_DT , A.ANTIC_AID_TRM_FROM , A.ANTIC_AID_TERM_TO , A.ANTIC_AID_DT , A.CALC_REAL_TIME_FLG , A.LUMP_SUM_AMT , A.PLAN_TYPE_SF , A.TP_MAX_AMOUNT , A.SSF_SELF_SERVICE , A.SSF_SS_START_DATE , A.SSF_SS_END_DATE , A.STDNT_GROUP , A.SSF_ALLOW_INSTALL , A.SSF_ALLOW_AMOUNT , A.SERVICE_IMPACT , A.SSF_LIMIT_AMT , A.MINIMUM_AMT , A.SSF_DESCR_LONG , 0 FROM PS_INSTITUTION_SET B , PS_ISET_BU_DTL D , PS_TP_CONTRACT A , PS_BUS_UNIT_TBL_SF C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.INSTITUTION_SET = D.INSTITUTION_SET AND B.SSF_SS_PMT_PLAN = 'Y' AND A.SSF_SELF_SERVICE = 'Y' AND A.SSF_SS_START_DATE <= %CurrentDateIn AND A.SSF_SS_END_DATE >= %CurrentDateIn AND A.TP_CON_LAST_COR_DT >= %CurrentDateIn AND A.TP_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION_SET Character(5) VARCHAR2(5) NOT NULL Institution Set

Prompt Table: INSTITUTION_SET

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_SF_NONVW

3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
4 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
5 STRM Character(4) VARCHAR2(4) NOT NULL Term

Prompt Table: TERM_VAL_TBL

6 TP_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Active
C=Cancelled
H=Hold
I=Inactive
U=Not Posted

Default Value: A

7 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type

Prompt Table: SP_ITEMCH_NONVW
Set Control Field: INSTITUTION

8 PMT_ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Payment Item Type

Prompt Table: %EDITTABLE

9 ACCOUNT_TYPE_SF Character(3) VARCHAR2(3) NOT NULL Account Type

Prompt Table: ACCT_TYP_PMT_VW

10 TP_CON_LAST_COR_DT Date(10) DATE Last Date to Enter Corrections
11 TP_CON_BILL_DT Date(10) DATE First Billing Date

Default Value: %date

12 TP_DUE_DATE_DAYS Number(3,0) SMALLINT NOT NULL This field is used to calculate the due date using the billing date and adding this field
13 TP_CON_NUM_PAYMENT Number(3,0) SMALLINT NOT NULL Number Of Payments
14 TP_CON_ACCT_TYPE Character(3) VARCHAR2(3) NOT NULL Administrative Account Type

Prompt Table: ACCT_TYP_TBL_SF

15 TP_CON_BILL_CYCLE Character(1) VARCHAR2(1) NOT NULL Billing Cycle
B=Bi-Monthly
M=Monthly
O=One Time
U=User Selected
W=Weekly
16 TP_CON_PLAN_FEE Signed Number(12,2) DECIMAL(10,2) NOT NULL Plan Fee Amount
17 ITEM_TYPE_GROUP Character(10) VARCHAR2(10) NOT NULL Item Type Group

Prompt Table: ITEM_GROUP_TBL

18 TP_CON_PAY_CREDIT Character(12) VARCHAR2(12) NOT NULL Payment Credit

Prompt Table: SP_ITYPPY_NONVW

19 TP_CON_PAY_CRE_TYP Character(3) VARCHAR2(3) NOT NULL Credit Account Type

Prompt Table: ACCT_TYP_TBL_SF

20 PAY_PLAN_TYPE Character(1) VARCHAR2(1) NOT NULL Payment Plan Type
B=Create Billing Entries
C=Credit Original Account
21 TP_ASSESS_TYPE Character(1) VARCHAR2(1) NOT NULL This field will be used by the institution to determine the type of charge for payment plans.
1=Payment Plan Fee
2=Percentage of Payment Plan
3=Monthly Interest Rate
22 TP_ASSESS_MNTH_INT Number(10,6) DECIMAL(9,6) NOT NULL This field will be used by the institution to assess monthly interest for payment plans.
23 TP_ASSESS_FLAT_PER Number(10,6) DECIMAL(9,6) NOT NULL This field will be used by the institution to charge a flat percentage assessment interest rate for payment plans.
24 TP_ASSESS_AMT Signed Number(16,2) DECIMAL(14,2) NOT NULL This field will be used by the institution to charge fees for payment plans. The field will contain the dollar amount of the flat fee charged.
25 TP_ASSESS_FEE_SPLT Character(1) VARCHAR2(1) NOT NULL This field will be used by the institution to split assessment fees for payment plans.
1=Add to first payment
2=Add to last payment
3=Split over all payments
26 TP_ASSESS_ITEM_TYP Character(12) VARCHAR2(12) NOT NULL This field will be used by the institution to charge fees for payment plans. The field will contain the item type that will be used to track the assessment interest/fees.

Prompt Table: SP_ITEMCH_NONVW
Set Control Field: INSTITUTION

27 DESCR Character(30) VARCHAR2(30) NOT NULL Description
28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: BUS_UNIT_TBL_SF.BASE_CURRENCY

Prompt Table: CURRENCY_CD_TBL

29 DUE_DT Date(10) DATE Expected Birth Date
30 INTEREST_CALC_MTHD Character(1) VARCHAR2(1) NOT NULL Interest Calculation Method
M=Calculated Monthly
Y=Calculated Yearly
31 INTEREST_RATE Number(16,6) DECIMAL(15,6) NOT NULL Interest Rate
32 INTEREST_ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Interest Item Type

Prompt Table: SP_ITYPPC_NONVW

33 ITEM_TYPE_GRP_OFF Character(10) VARCHAR2(10) NOT NULL Offset Item Type Group

Prompt Table: ITEM_GROUP_TBL

34 MAX_TERM_AMT Number(15,2) DECIMAL(14,2) NOT NULL Max Term Amount
35 MAX_AMT Signed Number(14,5) DECIMAL(12,5) NOT NULL Maximum Amount
36 TERM_FROM Character(4) VARCHAR2(4) NOT NULL Term From

Prompt Table: TERM_VAL_TBL

37 TERM_TO Character(4) VARCHAR2(4) NOT NULL Term To

Prompt Table: TERM_VAL_TBL

38 TERM_FROM_OFF Character(4) VARCHAR2(4) NOT NULL Offset Term From

Prompt Table: TERM_VAL_TBL

39 TERM_TO_OFF Character(4) VARCHAR2(4) NOT NULL Offset Term To

Prompt Table: TERM_VAL_TBL

40 ADD_CHRGS_DT Date(10) DATE Charge Add Date
41 ADJ_CHRGS_DT Date(10) DATE Charge Adjust Date
42 ADD_OFF_DT Date(10) DATE Offset Add Chrg Date
43 ADJ_OFF_DT Date(10) DATE Offset Adjust Chrg Date
44 ANTIC_AID_TRM_FROM Character(4) VARCHAR2(4) NOT NULL Antic Aid Term From

Prompt Table: TERM_VAL_TBL

45 ANTIC_AID_TERM_TO Character(4) VARCHAR2(4) NOT NULL Antic Aid Term To

Prompt Table: TERM_VAL_TBL

46 ANTIC_AID_DT Date(10) DATE Antic Aid DT
47 CALC_REAL_TIME_FLG Character(1) VARCHAR2(1) NOT NULL Calc. Real Time Flag

Y/N Table Edit

Default Value: N

48 LUMP_SUM_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Lump Sum Amt
49 PLAN_TYPE_SF Character(1) VARCHAR2(1) NOT NULL Plan Type
1=Existing Charges Only
2=Lump Sum
3=Calculated

Default Value: 1

50 TP_MAX_AMOUNT Signed Number(17,2) DECIMAL(15,2) NOT NULL Maximum Amount
51 SSF_SELF_SERVICE Character(1) VARCHAR2(1) NOT NULL Allow Self Service Enrollment

Y/N Table Edit

Default Value: N

52 SSF_SS_START_DATE Date(10) DATE Self Service Start Date
53 SSF_SS_END_DATE Date(10) DATE Self Service End Date
54 STDNT_GROUP Character(4) VARCHAR2(4) NOT NULL Student Group

Prompt Table: STDNT_GROUP_TBL

55 SSF_ALLOW_INSTALL Character(1) VARCHAR2(1) NOT NULL Allow Change in number of installments

Y/N Table Edit

Default Value: N

56 SSF_ALLOW_AMOUNT Character(1) VARCHAR2(1) NOT NULL Allow Change in Amount

Y/N Table Edit

Default Value: N

57 SERVICE_IMPACT Character(5) VARCHAR2(5) NOT NULL Service Impact

Prompt Table: SERVICE_IMPACT

58 SSF_LIMIT_AMT Character(1) VARCHAR2(1) NOT NULL Limit Plan to Existing Charges

Y/N Table Edit

Default Value: N

59 MINIMUM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Mininum Transaction Amount
60 SSF_DESCR_LONG Long Character CLOB Description
61 SSF_ELIG_CHARGES Signed Number(18,2) DECIMAL(16,2) NOT NULL Eligible Charges