AR Category we have in GL
- Trade Receipts
- Misc Receipts
- Adjustments
- Sales Invoices
- Debit Memos
- Chargebacks
- Credit Memos
- CM Applications
Link between GL to AR
When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Refrence columns.
Adjustments
- REFERENCE21 :posting_control_id
- REFERENCE22 :adjustment_id
- REFERENCE23 :line_id
- REFERENCE24 :trx_number
- REFERENCE25 :adjustment_number
- REFERENCE26 :cust_trx_type
- REFERENCE27 :bill_to_customer_id
- REFERENCE28 :ADJ
- REFERENCE29 :source_type prefixed by 'ADJ'
- REFERENCE30 :AR_ADJUSTMENTS
Transactions
- REFERENCE21 :posting_control_id
- REFERENCE22 :customer_trx_id
- REFERENCE23 :cust_trx_line_gl_dist_id
- REFERENCE24 :trx_number
- REFERENCE25 :cust.account_number
- REFERENCE26 :CUSTOMER
- REFERENCE27 :bill_to_customer_id
- REFERENCE28 :type(CM/DM/CB/INV)
- REFERENCE29 :type||account_class
- REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST
Applications
- REFERENCE21 :posting_control_id
- REFERENCE22 :cash_receipt_id||receivable_application_id for CASH /receivable_application_id for CM
- REFERENCE23 :line_id
- REFERENCE24 :receipt_number for CASH / trx_number for CM
- REFERENCE25 :trx_number if status = ¿APP¿ / NULL for unapplied records
- REFERENCE26 :cust_trx_type
- REFERENCE27 :pay_from_customer for CASH / bill_to_customer_id for CM
- REFERENCE28 :application_type (TRADE or CCURR for CASH / CMAPP for CM)
- REFERENCE29 :application_type||source_type
- REFERENCE30 :AR_RECEIVABLE_APPLICATIONS
Bills Receivable
- REFERENCE21 :posting_control_id
- REFERENCE22 :transaction_history_id
- REFERENCE23 :line_id
- REFERENCE24 :trx_number
- REFERENCE25 :customer_Trx_id
- REFERENCE26 :cust_trx_type
- REFERENCE27 :drawee_id
- REFERENCE28 :cust_trx_type
- REFERENCE29 :BR_||source_type
- REFERENCE30 :AR_TRANSACTION_HISTORY
Key Tables
In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.
交易檔 | 分錄 | 類型 | Source Type |
RA_CUSTOMER_TRX_ALL | RA_CUST_TRX_LINE_GL_DIST_ALL | TRANSACTIONS ACCOUNTING | |
AR_ADJUSTMENTS_ALL | AR_DISTRIBUTIONS_ALL | ADJUSTMENTS ACCOUNTING | |
AR_CASH_RECEIPT_HISTORY_ALL | AR_DISTRIBUTIONS_ALL | RECEITPS ACCOUNTING | |
AR_RECEIVABLE_APPLICATIONS_ALL | AR_DISTRIBUTIONS_ALL | RECEIPT APPLICATIONS ACCOUNTING | EXCH_LOSS REC |
AR_MISC_CASH_DISTRIBUTIONS_ALL | AR_DISTRIBUTIONS_ALL | MISC RECEIPTS ACCOUNTING |
Query for Subledger Transfer to GL
SELECT gjjlv.period_name "Period Name"
, gjb.NAME "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
, glcc.concatenated_segments "Accounts"
, NVL (gjjlv.line_entered_dr, 0) "Entered Debit"
, NVL (gjjlv.line_entered_cr, 0) "Entered Credit"
, NVL (gjjlv.line_accounted_dr, 0) "Accounted Debit"
, NVL (gjjlv.line_accounted_cr, 0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.NAME "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, ra.customer_name "Trx Reference"
, gjh.status "Posting Status"
, TRUNC (gjh.date_created) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE gjh.period_name ='JUL-09'
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
留言列表