CT_CORE_FIN_AR_ACCTG_DISTRB
Purpose of Query
AR Accounting Distribution - Query will pull posted item transaction details for the Business Unit and accounting dates from user prompts.
Folder
AR
Tables
Top Level-CTW_POSTED_ITM - Posted Item Trans. Rpt Tbl
Union1-CTW_DIR_JRNL_PY - Direct Jrnl Pay Reporting Tbl
Note: A Union query combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The two queries must have the same number of columns and compatible data types to unite.
Prompts
BUSINESS_UNIT - AR Business Unit =:1
ACCOUNTING_DT – not less than :2
ACCOUNTING_DT not greater than :3
Criteria
GL_DISTRIB_STATUS - Distribution Status=D
DEP_POST_STATUS - Posting Status=C
ACCOUNT not like 1%
Note: Runtime prompts are included as criteria.
Sample Data:
Fields
Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text1 / A.BUSINESS_UNIT - Business Unit / Char5 / Bus Unit
2 / A.DEPOSIT_BU - Deposit Unit / Char5 / Unit
3 / A.DEPOSIT_ID - Deposit ID / Char15 / Deposit ID
4 / A.CUST_ID - Customer ID / Char15 / Customer
5 / A.ITEM - Item ID / Char30 / Item ID
6 / A.ITEM_LINE - Item Line / Num6.0 / Line
7 / A.ITEM_SEQ_NUM - Item Sequence Number / Num3.0 / Sequence
8 / A.LEDGER_GROUP - Ledger Group / Char10 / Ledger Grp
9 / A.LEDGER - Ledger / Char10 / Ledger
10 / A.DST_SEQ_NUM - Distribution Sequence / Num6.0 / Line
11 / A.CT_AGNCY_LOC_CD - Agency Location Code / Char10 / Ag Loc Code
12 / A.CT_DEPOS_ENTR_NAME - Deposit Enter by Oper ID Name / Char30 / DEP OPID Name
13 / A.DEPOSIT_TYPE - Deposit Type / Char1 / N / Type
14 / A.BANK_ACCT_KEY - Bank Account / Char4 / Account
15 / A.BANK_ACCOUNT_NUM - Bank Account # / Char35 / Account #
16 / A.CONTROL_AMT - Control Total / SNm25.3 / Control
17 / A.PAYMENT_AMT - Payment Amount / SNm25.3 / Amount
18 / A.PAYMENT_ID - Payment ID / Char15 / Payment ID
19 / A.NAME1 - Name 1 / Char50 / Name
20 / A.BAL_AMT - Customer Balance / SNm25.3 / Balance
21 / A.POST_DT - Posted Date / Date / Posted
22 / A.ASOF_DT - As Of Date / Date / As Of
23 / A.GROUP_BU - Group Unit / Char5 / Group Unit
24 / A.ITEM_STATUS - Item Status / Char1 / N / Status
25 / A.JRNL_LN_REF - Journal Line Reference / Char10 / Ref
26 / A.LINE_DESCR - Journal Line Description / Char30 / Line Descr
27 / A.ACCOUNTING_DT - Accounting Date / Date / 2 / Acctg Date
28 / A.ACCOUNT - Account / Char10 / Account
29 / A.CT_GL_ACC_DESCR - GL Account Description / Char30 / GL Acct Descr
30 / A.DEPTID - Department / Char10 / 3 / DeptID
31 / A.CT_DEPT_DESCR - Department Description / Char30 / Deptid Descr
32 / A.FUND_CODE - Fund Code / Char5 / Fund
33 / A.CT_FUND_DESCR - Fund Code Description / Char30 / Fund Descr
34 / A.CT_SID - SID / Char5 / SID
35 / A.CT_SID_DESCR - SID Description / Char30 / SID Description
36 / A.PROGRAM_CODE - Program Code / Char5 / Program
37 / A.CT_PRGRM_DESCR - Program Code Description / Char30 / Program Descr
38 / A.PROJECT_ID - Project / Char15 / Project
39 / A.CT_PROJCT_DESCR - Project ID Description / Char30 / Project Descr
40 / A.CHARTFIELD1 - ChartField 1 / Char10 / ChartField 1
41 / A.CT_CHRTF1_DESCR - Chartfield One Description / Char30 / Chrtfld1 Descr
42 / A.CHARTFIELD2 - ChartField 2 / Char10 / ChartField 2
43 / A.CT_CHRTF2_DESCR - Chartfield Two Description / Char30 / ChrtFld2 Descr
44 / A.MONETARY_AMOUNT - Monetary Amount / SNm25.3 / Amount
45 / A.JOURNAL_ID - Journal ID / Char10 / Journal ID
46 / A.JOURNAL_DATE - Journal Date / Date / Date
47 / A.FISCAL_YEAR - Fiscal Year / Num4.0 / Year
48 / A.FISCAL_YEAR - Fiscal Year / Num4.0 / Year
49 / A.GL_DISTRIB_STATUS - Distribution Status / Char1 / N / Status
50 / A.BUDGET_DT - Budget Date / Date / Budg Dt