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

參考資料

arrow
arrow
    全站熱搜

    Somebaby 發表在 痞客邦 留言(0) 人氣()