GLRN_RECONSQ_VW(SQL View) |
Index Back |
|---|---|
GL My Recon by status Query VwGL Account Reconciliation My Recon by status Query View |
| SELECT COUNT(*) , E.DESCR , A.GLRN_SET , A.GLRN_RULE , A.BUSINESS_UNIT , A.AS_OF_DATE , A.SEQUENCE_NBR_9 , A.ACCOUNT , A.ALTACCT , A.DEPTID , %Sql(FS_CF_LIST1, A.) ,A.PROJECT_ID ,A.BOOK_CODE , A.DUE_DATE ,A.CALC_NET_AMT ,A.CURRENCY_CD , A.GLRN_REASON_CD , B.USERID , C.ATTRIBUTE_ID ,A.GLRN_UNIT_OF_RECON ,N.DESCR60 FROM PS_GLRN_RECON_TBL A , PS_GLRN_RECON_ASGN B , PS_GLRN_ATTRIB_TBL C , PS_GLRN_STATUS_TBL E , PS_GLRN_RECONS_TBL F , PS_GLRN_RECSTA_TBL M ,PS_GLRN_UOR_TBL N WHERE ( ( A.GLRN_SET = B.GLRN_SET AND A.GLRN_RULE = B.GLRN_RULE AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AS_OF_DATE = B.AS_OF_DATE AND A.SEQUENCE_NBR_9 = B.SEQUENCE_NBR_9 AND A.GLRN_RULE = C.GLRN_RULE AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_ATTRIB_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= %CurrentDateIn) AND C.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND E.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND A.AS_OF_DATE <= F.AS_OF_DATE AND F.GLACT_USERDSP_OPT = 'S' AND F.OPRID = M.OPRID AND E.GLRN_RECON_STATUS = M.GLRN_RECON_STATUS AND A.GLRN_UNIT_OF_RECON = N.GLRN_UNIT_OF_RECON)) GROUP BY E.DESCR , A.GLRN_SET , A.GLRN_RULE , A.BUSINESS_UNIT , A.AS_OF_DATE , A.SEQUENCE_NBR_9 , A.ACCOUNT , A.ALTACCT , A.DEPTID , %Sql(FS_CF_LIST1, A.) ,A.PROJECT_ID ,A.BOOK_CODE , A.DUE_DATE ,A.CALC_NET_AMT ,A.CURRENCY_CD , A.GLRN_REASON_CD , B.USERID , C.ATTRIBUTE_ID ,A.GLRN_UNIT_OF_RECON ,N.DESCR60 UNION SELECT COUNT(*) , J.DESCR , G.GLRN_SET , G.GLRN_RULE , G.BUSINESS_UNIT , G.AS_OF_DATE , G.SEQUENCE_NBR_9 , G.ACCOUNT , G.ALTACCT , G.DEPTID , %Sql(FS_CF_LIST1, G.) ,G.PROJECT_ID ,G.BOOK_CODE , G.DUE_DATE ,G.CALC_NET_AMT ,G.CURRENCY_CD , G.GLRN_REASON_CD , H.USERID , I.ATTRIBUTE_ID ,G.GLRN_UNIT_OF_RECON ,L.DESCR60 FROM PS_GLRN_RECON_TBL G , PS_GLRN_RECON_ASGN H , PS_GLRN_ATTRIB_TBL I , PS_GLRN_STATUS_TBL J , PS_GLRN_RECONS_TBL K ,PS_GLRN_UOR_TBL L WHERE ( G.GLRN_SET = H.GLRN_SET AND G.GLRN_RULE = H.GLRN_RULE AND G.BUSINESS_UNIT = H.BUSINESS_UNIT AND G.AS_OF_DATE = H.AS_OF_DATE AND G.SEQUENCE_NBR_9 = H.SEQUENCE_NBR_9 AND G.GLRN_RULE = I.GLRN_RULE AND I.EFFDT = ( SELECT MAX(I_ED.EFFDT) FROM PS_GLRN_ATTRIB_TBL I_ED WHERE I.SETID = I_ED.SETID AND I.GLRN_RULE = I_ED.GLRN_RULE AND I_ED.EFFDT <= %CurrentDateIn) AND J.GLRN_RECON_STATUS = G.GLRN_RECON_STATUS AND I.SETID = ( SELECT L.SETID FROM PS_SET_CNTRL_TBL L WHERE L.SETCNTRLVALUE = G.BUSINESS_UNIT) AND H.USERID = K.OPRID AND G.AS_OF_DATE <= K.AS_OF_DATE AND K.GLACT_USERDSP_OPT = 'A' AND G.GLRN_UNIT_OF_RECON = L.GLRN_UNIT_OF_RECON) GROUP BY J.DESCR , G.GLRN_SET , G.GLRN_RULE , G.BUSINESS_UNIT , G.AS_OF_DATE , G.SEQUENCE_NBR_9 , G.ACCOUNT , G.ALTACCT , G.DEPTID , %Sql(FS_CF_LIST1, G.) ,G.PROJECT_ID ,G.BOOK_CODE , G.DUE_DATE ,G.CALC_NET_AMT ,G.CURRENCY_CD , G.GLRN_REASON_CD , H.USERID , I.ATTRIBUTE_ID ,G.GLRN_UNIT_OF_RECON ,L.DESCR60 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | GLRN_COUNT | Number(10,0) | DECIMAL(10) NOT NULL | Count for Account Reconciliation |
| 2 | GLRN_STATUS | Character(30) | VARCHAR2(30) NOT NULL | Account Reconciliation Status |
| 3 | GLRN_SET | Character(20) | VARCHAR2(20) NOT NULL | Account Reconciliation Set |
| 4 | GLRN_RULE | Character(20) | VARCHAR2(20) NOT NULL | Account Reconciliation Rule ID |
| 5 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 6 | AS_OF_DATE | Date(10) | DATE | As of Date |
| 7 | SEQUENCE_NBR_9 | Number(9,0) | DECIMAL(9) NOT NULL | Sequence |
| 8 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL | Account |
| 9 | ALTACCT | Character(10) | VARCHAR2(10) NOT NULL | Alternate Account |
| 10 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 11 |  OPERATING_UNIT |
Character(8) | VARCHAR2(8) NOT NULL |
Operating Unit ChartField
Prompt Table: OPER_UNIT_TBL |
| 12 | PRODUCT |
Character(6) | VARCHAR2(6) NOT NULL |
Product ChartField
Prompt Table: PRODUCT_TBL |
| 13 | FUND_CODE |
Character(5) | VARCHAR2(5) NOT NULL |
Fund Code
Prompt Table: FUND_TBL |
| 14 | CLASS_FLD |
Character(5) | VARCHAR2(5) NOT NULL |
Class Field
Prompt Table: CLASS_CF_TBL |
| 15 | PROGRAM_CODE |
Character(5) | VARCHAR2(5) NOT NULL |
Program Code ChartField
Prompt Table: PROGRAM_TBL |
| 16 | BUDGET_REF |
Character(8) | VARCHAR2(8) NOT NULL |
Budget Reference
Prompt Table: BUD_REF_TBL |
| 17 | AFFILIATE |
Character(5) | VARCHAR2(5) NOT NULL |
Affiliate
Prompt Table: AFFILIATE_VW |
| 18 | AFFILIATE_INTRA1 |
Character(10) | VARCHAR2(10) NOT NULL |
IntraUnit Affiliate1
Prompt Table: %EDIT_INTRA01 |
| 19 | AFFILIATE_INTRA2 |
Character(10) | VARCHAR2(10) NOT NULL |
Operating Unit Affiliate
Prompt Table: %EDIT_INTRA02 |
| 20 | CHARTFIELD1 |
Character(10) | VARCHAR2(10) NOT NULL |
Expansion chartfield 1
Prompt Table: CHARTFIELD1_TBL |
| 21 | CHARTFIELD2 |
Character(10) | VARCHAR2(10) NOT NULL |
Expansion Chartfield 2
Prompt Table: CHARTFIELD2_TBL |
| 22 | CHARTFIELD3 |
Character(10) | VARCHAR2(10) NOT NULL |
Expansion Chartfield 3
Prompt Table: CHARTFIELD3_TBL |
| 23 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 24 | BOOK_CODE | Character(4) | VARCHAR2(4) NOT NULL | Book Code |
| 25 | DUE_DATE | Date(10) | DATE | Delivery Due Date from PO |
| 26 | CALC_NET_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Calculated net amount |
| 27 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 28 | GLRN_REASON_CD | Character(2) | VARCHAR2(2) NOT NULL | Status Change reason code |
| 29 | USERID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID |
| 30 | ATTRIBUTE_ID | Character(20) | VARCHAR2(20) NOT NULL | Attribute ID |
| 31 | GLRN_UNIT_OF_RECON | Character(2) | VARCHAR2(2) NOT NULL | 2-Char Unit of Reconciliation |
| 32 | GLRN_ASSGNMNT_LVL | Character(60) | VARCHAR2(60) NOT NULL | Assignment Level for Account Reconciliation |