EX_ADV_RECON_VW(SQL View) |
Index Back |
|---|
| SELECT DISTINCT A.EMPLID ,A.ADVANCE_ID ,A.BALANCE ,A.CURRENCY_CD ,A.ADV_STATUS ,B.ADVANCE_OVERDUE ,A.BUSINESS_UNIT_GL ,A.ACCOUNTING_DT FROM PS_EX_ADV_HDR A , PS_BUS_UNIT_TBL_EX B WHERE A.ADV_STATUS = 'PD' AND A.BALANCE = 0 AND A.RECONCILE_DT IS NULL AND A.BUSINESS_UNIT_GL=B.BUSINESS_UNIT_GL AND NOT EXISTS ( SELECT 'X' FROM PS_EX_SHEET_ADV C , PS_EX_SHEET_HDR D WHERE C.EMPLID = A.EMPLID AND C.ADVANCE_ID = A.ADVANCE_ID AND D.SHEET_ID = C.SHEET_ID AND D.SHEET_STATUS <> 'PD' AND D.SHEET_STATUS <> 'CLS') AND NOT EXISTS ( SELECT 'X' FROM PS_EX_ADV_CHK E WHERE E.ADVANCE_ID = A.ADVANCE_ID) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | ADVANCE_ID |
Character(10) | VARCHAR2(10) NOT NULL | Advance ID |
| 3 | BALANCE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Balance |
| 4 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 5 | ADV_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Advance Status
APY=Approved for Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process PST=Past Due Balance RAP=Approved RCN=Reconciled RRT=Reroute Transaction SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation |
| 6 | ADVANCE_OVERDUE | Number(3,0) | SMALLINT NOT NULL | Advance Overdue in days |
| 7 | BUSINESS_UNIT_GL | Character(5) | VARCHAR2(5) NOT NULL | GL Business Unit |
| 8 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |