PY_TD1_APPR_VW(SQL View) |
Index Back |
|---|---|
Upd TD1 Approval ViewRecord to be used as the base for the Component for Updatable TD1 Approval |
| SELECT B.EMPLID , B.COMPANY , B.PROVINCE , T.SESSION_ID_TD1 , T.SEQ_NBR ,T.PY_PDF_FORM_ID_CAN , N.NAME_DISPLAY ,B.CIT_NET_CLAIM_AMT ,T.CIT_NET_CLAIM_AMT ,B.CIT_PRESCR_AREA ,T.CIT_PRESCR_AREA ,0 ,0 ,B.NON_INDEX_AMT ,T.NON_INDEX_AMT ,0 ,0 ,0 ,0 ,' ' ,' ' ,B2.SPECIAL_CIT_STATUS ,T.SPECIAL_CIT_STATUS ,B.CIT_TAX_CREDITS ,T.CIT_TAX_CREDITS ,' ' ,' ' ,0 ,0 ,B.CIT_FACTOR_Y ,T.CIT_FACTOR_Y , W1.EOAWTHREAD_STATUS FROM PS_CAN_TAX_PRVDATA B ,PS_CAN_TAX_DATA B2 , PS_PY_TD1_STG_CAN T , PS_NAMES N , PS_PY_TD1_APPR_XRF W1 WHERE B.COMPANY=B2.COMPANY AND B.EMPLID=B2.EMPLID AND B.EFFDT=B2.EFFDT AND B.COMPANY = T.COMPANY AND B.EMPLID = T.EMPLID AND T.STATE = B.PROVINCE AND T.COMPANY = W1.COMPANY AND T.EMPLID = W1.EMPLID AND T.STATE = W1.STATE AND T.SESSION_ID_TD1 = W1.SESSION_ID_TD1 AND T.SEQ_NBR = W1.SEQ_NBR AND B.EMPLID = N.EMPLID AND ((B.EFFDT=( SELECT MAX(EFFDT) FROM PS_CAN_TAX_DATA B1 WHERE B1.EMPLID=B.EMPLID AND B1.COMPANY=B.COMPANY AND B1.EFFDT<=%CurrentDateIn)) OR (B.EFFDT=( SELECT MIN(EFFDT) FROM PS_CAN_TAX_DATA B1 WHERE B1.EMPLID=B.EMPLID AND B1.COMPANY=B.COMPANY AND B1.EFFDT>%CurrentDateIn))) AND N.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID=N.EMPLID AND N1.NAME_TYPE=N.NAME_TYPE AND N1.EFFDT<=%CurrentDateIn) AND N.NAME_TYPE = 'PRI' AND W1.EOAWTHREAD_STATUS = 'P' UNION ALL SELECT A.EMPLID , A.COMPANY , '$C' , S.SESSION_ID_TD1 , S.SEQ_NBR ,S.PY_PDF_FORM_ID_CAN , M.NAME_DISPLAY ,A.CIT_NET_CLAIM_AMT ,S.CIT_NET_CLAIM_AMT ,A.CIT_PRESCR_AREA ,S.CIT_PRESCR_AREA ,A.CIT_ADDL_AMT ,S.CIT_ADDL_AMT ,A.TD1_ADJUST ,S.TD1_ADJUST ,A.CIT_ANNUAL_COMM ,S.CIT_ANNUAL_COMM ,A.CIT_ANNUAL_EXP ,S.CIT_ANNUAL_EXP ,A.STATUS_INDIAN ,S.STATUS_INDIAN ,A.SPECIAL_CIT_STATUS ,S.SPECIAL_CIT_STATUS ,0 ,0 ,' ' ,' ' ,0 ,0 ,0 ,0 , W.EOAWTHREAD_STATUS FROM PS_CAN_TAX_DATA A , PS_PY_TD1_STG_CAN S , PS_NAMES M , PS_PY_TD1_APPR_XRF W WHERE A.COMPANY = S.COMPANY AND A.EMPLID = S.EMPLID AND S.STATE = '$C' AND S.COMPANY = W.COMPANY AND S.EMPLID = W.EMPLID AND S.STATE = W.STATE AND S.SESSION_ID_TD1 = W.SESSION_ID_TD1 AND S.SEQ_NBR = W.SEQ_NBR AND A.EMPLID = M.EMPLID AND ((A.EFFDT=( SELECT MAX(EFFDT) FROM PS_CAN_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT<=%CurrentDateIn)) OR (A.EFFDT=( SELECT MIN(EFFDT) FROM PS_CAN_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT>%CurrentDateIn))) AND M.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES M1 WHERE M1.EMPLID=M.EMPLID AND M1.NAME_TYPE=M.NAME_TYPE AND M1.EFFDT<=%CurrentDateIn) AND M.NAME_TYPE = 'PRI' AND W.EOAWTHREAD_STATUS = 'P' UNION ALL SELECT A.EMPLID , A.COMPANY , 'QC' , S.SESSION_ID_TD1 , S.SEQ_NBR ,S.PY_PDF_FORM_ID_CAN , M.NAME_DISPLAY ,A.QIT_NET_CLAIM_AMT ,S.CIT_NET_CLAIM_AMT ,A.QIT_PRESCR_AREA ,S.CIT_PRESCR_AREA ,A.QIT_ADDL_AMT ,S.CIT_ADDL_AMT ,A.NON_INDEX_AMT ,S.NON_INDEX_AMT ,A.QIT_ANNUAL_COMM ,S.CIT_ANNUAL_COMM ,A.QIT_ANNUAL_EXP ,S.CIT_ANNUAL_EXP ,' ' ,' ' ,A.SPECIAL_CIT_STATUS ,S.SPECIAL_CIT_STATUS ,0 ,0 ,A.SPECIAL_QIT_STATUS ,S.SPECIAL_QIT_STATUS ,A.QIT_OTHER_DEDUCTS ,S.QIT_OTHER_DEDUCTS ,0 ,0 , W.EOAWTHREAD_STATUS FROM PS_CAN_TAX_DATA A , PS_PY_TD1_STG_CAN S , PS_NAMES M , PS_PY_TD1_APPR_XRF W WHERE A.COMPANY = S.COMPANY AND A.EMPLID = S.EMPLID AND S.STATE = 'QC' AND S.COMPANY = W.COMPANY AND S.EMPLID = W.EMPLID AND S.STATE = W.STATE AND S.SESSION_ID_TD1 = W.SESSION_ID_TD1 AND S.SEQ_NBR = W.SEQ_NBR AND A.EMPLID = M.EMPLID AND ((A.EFFDT=( SELECT MAX(EFFDT) FROM PS_CAN_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT<=%CurrentDateIn)) OR (A.EFFDT=( SELECT MIN(EFFDT) FROM PS_CAN_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT>%CurrentDateIn))) AND M.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES M1 WHERE M1.EMPLID=M.EMPLID AND M1.NAME_TYPE=M.NAME_TYPE AND M1.EFFDT<=%CurrentDateIn) AND M.NAME_TYPE = 'PRI' AND W.EOAWTHREAD_STATUS = 'P' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 3 | STATE |
Character(6) | VARCHAR2(6) NOT NULL | State |
| 4 | SESSION_ID_TD1 |
Character(36) | VARCHAR2(36) NOT NULL | Session Id |
| 5 | SEQ_NBR |
Number(15,0) | DECIMAL(15) NOT NULL | Sequence Number |
| 6 | PY_PDF_FORM_ID_CAN | Character(10) | VARCHAR2(10) NOT NULL | Unique identifier for the PDF form used for updatable TD1 PDF. |
| 7 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 8 | PRV_NET_CLAIM_AMT | Number(10,2) | DECIMAL(9,2) NOT NULL | Net Claim Amount |
| 9 | FWT_NET_CLAIM_AMT | Number(10,2) | DECIMAL(9,2) NOT NULL | Net Claim Amount |
| 10 | PRV_PRESCR_AREA | Number(10,2) | DECIMAL(9,2) NOT NULL | Prescribed Area |
| 11 | FWT_PRESCR_AREA | Number(10,2) | DECIMAL(9,2) NOT NULL | Prescribed Area |
| 12 | PRV_CIT_ADDL_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | CIT Additional Amount |
| 13 | FWT_CIT_ADDL_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | CIT Additional Amount |
| 14 | PRV_NON_INDEX_AMT | Number(10,2) | DECIMAL(9,2) NOT NULL | Non Index Amount |
| 15 | FWT_NON_INDEX_AMT | Number(10,2) | DECIMAL(9,2) NOT NULL | Non Index Amount |
| 16 | PRV_CIT_ANL_COMM | Number(10,2) | DECIMAL(9,2) NOT NULL | CIT Annual Commission |
| 17 | FWT_CIT_ANL_COMM | Number(10,2) | DECIMAL(9,2) NOT NULL | CIT Additional Commission |
| 18 | PRV_CIT_ANNUAL_EXP | Number(10,2) | DECIMAL(9,2) NOT NULL | Annual Expense |
| 19 | FWT_CIT_ANNUAL_EXP | Number(10,2) | DECIMAL(9,2) NOT NULL | CIT Annual Expense |
| 20 | PRV_STATUS_INDIAN | Character(1) | VARCHAR2(1) NOT NULL | Status Indian |
| 21 | FWT_STATUS_INDIAN | Character(1) | VARCHAR2(1) NOT NULL | Status Indian |
| 22 | PRV_SPL_CIT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Special CIT Status
E=Exempt (not subject to CIT) G=Maintain gross N=None |
| 23 | FWT_SPL_CIT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Specail CIT Status
E=Exempt (not subject to CIT) G=Maintain gross N=None |
| 24 | PRV_CIT_TAX_CRDTS | Number(10,2) | DECIMAL(9,2) NOT NULL | Previous CIT Tax credits |
| 25 | CUR_CIT_TAX_CRDTS | Number(10,2) | DECIMAL(9,2) NOT NULL | Current CIT Tax credits |
| 26 | PRV_SPL_QIT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Special CIT Status
E=Exempt (not subject to QIT) G=Maintain gross N=None |
| 27 | CUR_SPL_QIT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Special QIT Status
E=Exempt (not subject to QIT) G=Maintain gross N=None |
| 28 | PRV_QIT_OTH_DED | Number(10,2) | DECIMAL(9,2) NOT NULL | Previous QIT Other deductions |
| 29 | CUR_QIT_OTH_DED | Number(10,2) | DECIMAL(9,2) NOT NULL | Current QIT Other Deductions |
| 30 | PRV_CIT_FACTOR_Y | Number(10,2) | DECIMAL(9,2) NOT NULL | Previous CIT Factor value |
| 31 | CUR_CIT_FACTOR_Y | Number(10,2) | DECIMAL(9,2) NOT NULL | Current CIT Factor Value |
| 32 | EOAWTHREAD_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Thread Status
A=Approved C=Complete D=Denied E=Pending Denial H=Hard Deny I=Initial N=Not Active P=Pending S=Awaiting Further Approvals T=Terminated W=Canceled X=Suspended/Pending Denial |