CHAPTER 5: Oracle Apps Order to cash(O2C) Cycle

CHAPTER 5: Oracle Apps Order to cash(O2C) Cycle:--


Back end Tables names for receipt:


1. ar_cash_receipts_all


2.ar_adjustments_all


3.ar_payments_schedules_all


4.ar_receivable_applications_all -- intermediatery table between invoice (ra_customer_trx_all)and receipt(ra_cash_receipts)


Link between invoice and receipt ?


SELECT trx_number, amount, receipt_number, receipt_date


FROM ar_cash_receipts_all acr,


ra_customer_trx_all rcta,


ar_receivable_applications_all araa


WHERE AND rcta.customer_trx_id = araa.applied_customer_trx_id


AND acr.cash_receipt_id = araa.cash_receipt_id


and acr.receipt_number = '23674519';





Note: The intermediary table used to combine the invoice and receipt


tables is called ar_receivable_applications_all. Where can you discover the balance amount?


SELECT rcta.trx_number, amount,


receipt_number,


receipt_date, apsa.due_date,


apsa.amount_due_original,


apsa.amount_due_remaining


FROM ar_cash_receipts_all acr,


ra_customer_trx_all rcta,


ar_receivable_applications_all araa,


ar_payment_schedules_all apsa


WHERE rcta.customer_trx_id = araa.applied_customer_trx_id


AND acr.cash_receipt_id = araa.cash_receipt_id


AND rcta.customer_trx_id = apsa.customer_trx_id


And acr.receipt_number = '23674519';





Create accounting


Dr- cash


Cr- receivable


Journal import – it will create journal entries


Last step in O2C FLOW


STEP7: Transfer journal entries to gl:---


Transfer to GL:


Transfer of Invoice to GL:--


------------------


Choose and submit the "Transfer Journal Entries to GL" program under receivables management.


Give the following param.


Enter today's date as the end date.


publishing summary: no; import journal: yes; click "OK."

it will launch the following concurrent programs:--


only one--journal import program (Contains batch number->check in GL>Journal)


once all the programs are completed


take batch number from “journal import program” FROM front-end

The journal entries are still not published in GL.


access the GL responsibility page. USA general ledger vision operations


go to Journal -> Inquiry


select * from GL_JE_BATCHES> to get the batch number from back end


from front-end you will get the batch number ,take batch number from “journal import program” FROM front-end


give BATCH NAME= Receivables A 38254227457687 AND click on find

The journal entries are still not published in GL.


access the GL responsibility page. USA general ledger vision operations


go to Journal -> Inquiry


select * from GL_JE_BATCHES>to get the batch number from back end


from front-end you will get the batch number ,take batch number from “journal import program” FROM front-end


give BATCH NAME= Receivables A 38254227457687 AND click on find


Now post the above journal:---


Go to GL>Journals>Post


give BATCH NAME= Receivables A 38254227457687 AND click on find


Select the required batch and Click on post button as shown below


After post button, it will launch the program called Posting:Single Ledger.


To verify go to GL> Inquiry-> journal to check whether the journal is posted or not


Give the batch name and find, it will show posted.


select * from GL_BALANCES where ledger_id=1 and period_name=’Oct-20’


back end tables:---


-------------


1.gl_je_headers_all


2.gl_je_lines


3.gl_je_batches


4.gl_balances-- after posting only records will be found here .


5.gl_import_references.


AR SLA GL Link drill down query:--


SELECT ctt.cust_trx_type_id, lgd.set_of_books_id, lgd.amount gl_posted_amount,


ctl.extended_amount ar_trx_amount, ctl.description inv_item_desc,


gcc.segment1


|| '.'


|| gcc.segment2


|| '.'


|| gcc.segment3


|| '.'


|| gcc.segment4


|| '.'


|| gcc.segment5


|| '.'


|| gcc.segment6


|| '.'


|| gcc.segment7


|| '.'


|| gcc.segment8 gl_account,


gp.period_name, cta.trx_number trx_number,


ctl.interface_line_attribute1 sales_order_number,


ll.line_number sales_order_line_number, ctt.NAME transaction_type,


xdl.unrounded_accounted_cr,


xdl.unrounded_accounted_dr, xdl.unrounded_entered_cr,


xdl.unrounded_entered_dr, xal.gl_sl_link_id,


h.je_category journal_category, h.je_source journal_source,


l.accounted_cr je_lines_accounted_cr,


l.accounted_dr je_lines_accounted_dr,


l.description je_lines_description, l.entered_cr je_lines_entered_cr,


l.entered_dr je_lines_entered_dr, h.NAME journal_name,


b.NAME journal_batch_name, l.je_line_num journal_line_number


FROM ra_cust_trx_line_gl_dist_all lgd,


ra_customer_trx_lines_all ctl,


gl_code_combinations gcc,


gl_periods gp,


oe_order_headers_all h,


oe_order_lines_all ll,


ra_customer_trx_all cta,


ra_cust_trx_types_all ctt, -- XLA Tables


xla_distribution_links xdl,


xla_ae_lines xal,


xla_ae_headers xah,


xla_events xle,


xla_transaction_entities xte, -- GL Tables


gl_code_combinations_kfv glcc,


gl_import_references gir,


gl_je_headers h,


gl_je_lines l,


gl_je_batches b


WHERE lgd.customer_trx_line_id = ctl.customer_trx_line_id


AND lgd.code_combination_id = gcc.code_combination_id


-- AND lgd.gl_posted_date BETWEEN '15-DEC-2015' AND '16-DEC-2015'


-- AND lgd.org_id = 204


AND gp.period_name = 'OCT-20'


AND lgd.gl_posted_date BETWEEN gp.start_date AND gp.end_date


AND cta.cust_trx_type_id = ctt.cust_trx_type_id


AND cta.customer_trx_id = lgd.customer_trx_id


AND ctl.interface_line_attribute1 = TO_CHAR (h.order_number)


AND ctl.interface_line_attribute6 = TO_CHAR (ll.line_id)


AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'


AND lgd.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1


AND lgd.code_combination_id = xal.code_combination_id


AND xal.code_combination_id = glcc.code_combination_id


AND xdl.event_id = xle.event_id


AND xdl.event_id = xah.event_id


AND xdl.ae_header_id = xah.ae_header_id


AND xdl.ae_header_id = xal.ae_header_id


AND xah.entity_id = xte.entity_id(+)


AND xal.gl_sl_link_id = gir.gl_sl_link_id


AND xal.gl_sl_link_table = gir.gl_sl_link_table


AND h.je_batch_id = b.je_batch_id


AND l.je_header_id = h.je_header_id


AND l.je_line_num = gir.je_line_num


AND l.je_header_id = gir.je_header_id


AND cta.trx_number = '10037506';