AR80015_VW

(SQL View)
 Index Back

Transaction Detail By Account

This View is used for generating Transaction Detail Statutory report in Account Receivables for Accounts

SELECT A.SHOW_FOREIGN_AMT , A.FROM_PERIOD , A.PERIOD_TO , A.ACCT_FR , A.ACCT_TO , A.CUST_ID_FRM , A.CUST_ID_TO , B.RPTG_BEGIN_BALANCE , B.BUSINESS_UNIT , D.DESCR , B.LEDGER , B.BASE_CURRENCY , B.FISCAL_YEAR , B.ACCOUNT , F.DESCR , B.CUST_SETID , B.CUST_ID , G.NAME1 , B.ACCOUNTING_PERIOD , C.ACCOUNTING_DT , C.JRNL_TYPE , C.JRNL_CODE , C.DOC_TYPE , C.DOC_SEQ_NBR , C.DOC_SEQ_DATE , C.FOREIGN_CURRENCY , C.RT_TYPE , C.RATE_DIV , C.RATE_MULT , C.MONETARY_AMT_DR , C.MONETARY_AMT_CR , C.FOREIGN_AMT_DR , C.FOREIGN_AMT_CR , C.RPTG_REFERENCE , A.OPRID , B.RPTG_BALANCE_SEQ , 'D' , C.RPTG_DETAIL_SEQ , A.DET_SUM_SW , A.BUSINESS_UNIT , A.RUN_CNTL_ID FROM PS_RUN_CNTL_STAT A , PS_RPTG_BALANCE B , PS_RPTG_DETAIL_TBL C , PS_BUS_UNIT_TBL_FS D , PS_GL_ACCOUNT_TBL F , PS_CUSTOMER G , PS_RUN_GL_BALBU O WHERE B.RPTG_APPL_ID IN ('AR','BI') AND B.BUSINESS_UNIT = O.BUSINESS_UNIT AND B.FISCAL_YEAR = A.FISCAL_YEAR AND B.CUST_ID BETWEEN A.CUST_ID_FRM AND A.CUST_ID_TO AND B.LEDGER = A.LEDGER AND B.ACCOUNT BETWEEN A.ACCT_FR AND A.ACCT_TO AND B.ACCOUNTING_PERIOD BETWEEN A.FROM_PERIOD AND A.PERIOD_TO AND B.RPTG_APPL_ID = C.RPTG_APPL_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.FISCAL_YEAR = C.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD AND B.RPTG_BALANCE_SEQ = C.RPTG_BALANCE_SEQ AND D.BUSINESS_UNIT = B.BUSINESS_UNIT AND F.SETID = ( SELECT E.SETID FROM PS_SET_CNTRL_REC E WHERE E.RECNAME = 'GL_ACCOUNT_TBL' AND E.SETCNTRLVALUE = C.BUSINESS_UNIT) AND F.ACCOUNT = C.ACCOUNT AND F.EFFDT = ( SELECT MAX(F_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL F_ED WHERE F.SETID = F_ED.SETID AND F.ACCOUNT = F_ED.ACCOUNT AND F_ED.EFFDT <= %CurrentDateIn) AND G.CUST_ID = B.CUST_ID AND A.OPRID = O.OPRID AND A.RUN_CNTL_ID = O.RUN_CNTL_ID AND G.SETID = B.CUST_SETID UNION ALL SELECT H.SHOW_FOREIGN_AMT , H.FROM_PERIOD , H.PERIOD_TO , H.ACCT_FR , H.ACCT_TO , H.CUST_ID_FRM , H.CUST_ID_TO , I.RPTG_BEGIN_BALANCE , I.BUSINESS_UNIT , K.DESCR , I.LEDGER , I.BASE_CURRENCY , I.FISCAL_YEAR , I.ACCOUNT , L.DESCR , I.CUST_SETID , I.CUST_ID , N.NAME1 , I.ACCOUNTING_PERIOD , H.ACCT_FR_DT , H.JRNL_TP_FR , H.JRNL_CD_FR , H.JRNL_CD_TO , '' , H.ACCT_TO_DT , H.LANGUAGE_CD , H.VENDOR_SETID , 0 , 0 , 0 , 0 , 0 , 0 , '' , H.OPRID , I.RPTG_BALANCE_SEQ , 'B' , 0 , H.DET_SUM_SW , H.BUSINESS_UNIT , H.RUN_CNTL_ID FROM PS_RUN_CNTL_STAT H , PS_RPTG_BALANCE I , PS_BUS_UNIT_TBL_FS K , PS_GL_ACCOUNT_TBL L , PS_CUSTOMER N , PS_RUN_GL_BALBU P WHERE I.RPTG_APPL_ID IN ('AR','BI') AND I.BUSINESS_UNIT = P.BUSINESS_UNIT AND I.CUST_ID BETWEEN H.CUST_ID_FRM AND H.CUST_ID_TO AND H.LEDGER = I.LEDGER AND I.FISCAL_YEAR = H.FISCAL_YEAR AND I.ACCOUNTING_PERIOD BETWEEN H.FROM_PERIOD AND H.PERIOD_TO AND I.ACCOUNT BETWEEN H.ACCT_FR AND H.ACCT_TO AND EXISTS ( SELECT J.ACCOUNT FROM PS_RPTG_DETAIL_TBL J WHERE J.BUSINESS_UNIT = I.BUSINESS_UNIT AND J.FISCAL_YEAR = I.FISCAL_YEAR AND J.ACCOUNTING_PERIOD = I.ACCOUNTING_PERIOD AND J.LEDGER = I.LEDGER AND J.ACCOUNT = I.ACCOUNT AND J.CUST_SETID = I.CUST_SETID AND J.CUST_ID = I.CUST_ID) AND K.BUSINESS_UNIT = I.BUSINESS_UNIT AND L.ACCOUNT = I.ACCOUNT AND L.SETID = ( SELECT M.SETID FROM PS_SET_CNTRL_REC M WHERE M.RECNAME = 'GL_ACCOUNT_TBL' AND M.SETCNTRLVALUE = I.BUSINESS_UNIT) AND L.EFFDT = ( SELECT MAX(L_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL L_ED WHERE L.SETID = L_ED.SETID AND L.ACCOUNT = L_ED.ACCOUNT AND L_ED.EFFDT <= %CurrentDateIn) AND N.CUST_ID = I.CUST_ID AND H.OPRID = P.OPRID AND H.RUN_CNTL_ID = P.RUN_CNTL_ID AND N.SETID = I.CUST_SETID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SHOW_FOREIGN_AMT Character(1) VARCHAR2(1) NOT NULL Show Foreign Currency Amount
2 FROM_PERIOD Number(3,0) SMALLINT NOT NULL From Period
3 PERIOD_TO Number(3,0) SMALLINT NOT NULL To Period
4 ACCT_FR Character(10) VARCHAR2(10) NOT NULL From
5 ACCT_TO Character(10) VARCHAR2(10) NOT NULL To
6 CUST_ID_FRM Character(15) VARCHAR2(15) NOT NULL From Customer ID
7 CUST_ID_TO Character(15) VARCHAR2(15) NOT NULL To Customer ID
8 RPTG_BEGIN_BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Reporting Beginning Balance
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
11 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
12 BASE_CURRENCY Character(3) VARCHAR2(3) NOT NULL "Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "
13 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
14 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
15 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
16 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
17 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
18 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
19 ACCOUNTING_PERIOD Number(3,0) SMALLINT NOT NULL Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
20 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
21 JRNL_TYPE Character(2) VARCHAR2(2) NOT NULL Journal Type
22 JRNL_CODE Character(8) VARCHAR2(8) NOT NULL Journal Code
23 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
24 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
25 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.
26 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
27 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
28 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
29 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
30 MONETARY_AMT_DR Signed Number(28,3) DECIMAL(26,3) NOT NULL Base DR Amount
31 MONETARY_AMT_CR Signed Number(28,3) DECIMAL(26,3) NOT NULL Base CR Amount
32 FOREIGN_AMT_DR Signed Number(28,3) DECIMAL(26,3) NOT NULL Debit Amount
33 FOREIGN_AMT_CR Signed Number(28,3) DECIMAL(26,3) NOT NULL Credit Amount
34 RPTG_REFERENCE Character(50) VARCHAR2(50) NOT NULL Reporting Source Reference
35 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
36 RPTG_BALANCE_SEQ Signed Number(19,0) DECIMAL(18) NOT NULL Reporting Balance Line
37 EXP6_1 Character(1) VARCHAR2(1) NOT NULL Dummy field
38 RPTG_DETAIL_SEQ Number(6,0) INTEGER NOT NULL Reporting Detail Line
39 DET_SUM_SW Character(1) VARCHAR2(1) NOT NULL Detail or Summary
D=Detail
S=Summary
40 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit
41 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID