GLRN_RECONDQ_VW

(SQL View)
 Index Back

GL My Recon by Due Date

GL Account Reconciliation My Recon by Due Date

SELECT COUNT(*) ,E.OPRID ,CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END, A.DUE_DATE-D.AS_OF_DATE , C.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 , A.GLRN_UNIT_OF_RECON , F.DESCR60 FROM PS_GLRN_RECON_TBL A , PS_GLRN_RULE_TBL B , PS_GLRN_STATUS_TBL C , PS_GLRN_RECOND_TBL D , PS_GLRN_RECDUT_TBL E , PS_GLRN_UOR_TBL F WHERE ( ( A.GLRN_RULE = B.GLRN_RULE AND B.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_RULE_TBL C_ED WHERE B.SETID = C_ED.SETID AND B.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= D.AS_OF_DATE AND B.EFF_STATUS = 'A') AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND C.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND D.GLACT_USERDSP_OPT = 'S' AND D.OPRID = E.OPRID AND C.GLRN_RECON_STATUS = E.GLRN_RECON_STATUS AND A.GLRN_UNIT_OF_RECON = F.GLRN_UNIT_OF_RECON)) GROUP BY E.OPRID,A.DUE_DATE-D.AS_OF_DATE , C.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 , A.GLRN_UNIT_OF_RECON , F.DESCR60 UNION ALL SELECT COUNT(*) ,D.OPRID ,CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END, A.DUE_DATE-D.AS_OF_DATE , C.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 , A.GLRN_UNIT_OF_RECON , F.DESCR60 FROM PS_GLRN_RECON_TBL A , PS_GLRN_RULE_TBL B , PS_GLRN_STATUS_TBL C , PS_GLRN_RECOND_TBL D , PS_GLRN_UOR_TBL F WHERE ( ( A.GLRN_RULE = B.GLRN_RULE AND B.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_RULE_TBL C_ED WHERE B.SETID = C_ED.SETID AND B.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= D.AS_OF_DATE AND B.EFF_STATUS = 'A') AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND C.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND D.GLACT_USERDSP_OPT = 'A' AND A.GLRN_UNIT_OF_RECON = F.GLRN_UNIT_OF_RECON)) GROUP BY D.OPRID,A.DUE_DATE-D.AS_OF_DATE , C.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 , A.GLRN_UNIT_OF_RECON , F.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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 GLRN_DUE_DATE Character(60) VARCHAR2(60) NOT NULL Due Date for Account Reconciliation
4 GLRN_DUE_DAYS Number(3,0) SMALLINT NOT NULL Number of days relative to Period End day.
5 GLRN_STATUS Character(30) VARCHAR2(30) NOT NULL Account Reconciliation Status
6 GLRN_SET Character(20) VARCHAR2(20) NOT NULL Account Reconciliation Set
7 GLRN_RULE Character(20) VARCHAR2(20) NOT NULL Account Reconciliation Rule ID
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 AS_OF_DATE Date(10) DATE As of Date
10 SEQUENCE_NBR_9 Number(9,0) DECIMAL(9) NOT NULL Sequence
11 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
12 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
14 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPER_UNIT_TBL

15 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_TBL

16 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_TBL

17 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASS_CF_TBL

18 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_TBL

19 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_TBL

20 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

21 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

22 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

23 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CHARTFIELD1_TBL

24 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CHARTFIELD2_TBL

25 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CHARTFIELD3_TBL

26 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
27 BOOK_CODE Character(4) VARCHAR2(4) NOT NULL Book Code
28 DUE_DATE Date(10) DATE Delivery Due Date from PO
29 CALC_NET_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated net amount
30 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
31 GLRN_REASON_CD Character(2) VARCHAR2(2) NOT NULL Status Change reason code
32 GLRN_UNIT_OF_RECON Character(2) VARCHAR2(2) NOT NULL 2-Char Unit of Reconciliation
33 GLRN_ASSGNMNT_LVL Character(60) VARCHAR2(60) NOT NULL Assignment Level for Account Reconciliation