PA_SP_TAXTYPWRK

(SQL View)
 Index Back

Service Purchase Integration

This is the record defining the schema for the Service Purchase integration message used to send data to AR.

SELECT E.EMPLID , E.BENEFIT_PLAN , E.SP_ID ,E.ESTIMATE_NBR , I.INSTALL_NBR ,P.SEQUENCE_NBR , P.PYMT_DATE , P.EACCT_TRAN_TYPE ,P.ORIG_AMT , P.PYMT_AMT -P.ORIG_AMT ,P.PYMT_AMT ,0 ,0 FROM PS_PA_SP_TRAN_EST E , PS_PA_SP_TRAN_PYMT P ,PS_PA_SP_TRAN_INST I WHERE E.EST_SELECTED = 'Y' AND P.EMPLID = E.EMPLID AND P.BENEFIT_PLAN = E.BENEFIT_PLAN AND P.SP_ID = E.SP_ID AND P.ESTIMATE_NBR = E.ESTIMATE_NBR AND P.PYMT_METHOD = 'L' AND I.EMPLID = P.EMPLID AND I.BENEFIT_PLAN = P.BENEFIT_PLAN AND I.SP_ID = P.SP_ID AND I.ESTIMATE_NBR = P.ESTIMATE_NBR AND I.SEQUENCE_NBR = P.SEQUENCE_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan
3 SP_ID Number(11,0) DECIMAL(11) NOT NULL Service Purchase ID

Prompt Table: PA_SP_TRAN

4 ESTIMATE_NBR Number(2,0) SMALLINT NOT NULL Estimate Number
5 INSTALL_NBR Number(2,0) SMALLINT NOT NULL Installation Number for Service Purchase Installment Payments.
6 SEQUENCE_NBR Number(2,0) SMALLINT NOT NULL Sequence Number
7 DUE_DT Date(10) DATE Expected Birth Date
8 EACCT_TRAN_TYPE Character(1) VARCHAR2(1) NOT NULL Tax Type of Pension amounts.
O=Post-Tax
R=Pre-Tax
9 ORIG_AMT Number(12,2) DECIMAL(11,2) NOT NULL Original Amount
10 ADJ_AMOUNT Signed Number(13,2) DECIMAL(11,2) NOT NULL Adjusted Amount
11 MOD_AMT Number(12,2) DECIMAL(11,2) NOT NULL Modified Amount
12 PAYMENT_TOTAL Number(12,2) DECIMAL(11,2) NOT NULL Payments To Date
13 AVAIL_TO_CHANGE Signed Number(13,2) DECIMAL(11,2) NOT NULL Available To Change