AR Recipts to GL SQL including XLA

Many times we might need to get data for AR Receipts and link them to GL via the XLA

Below SQL can be used to get that information and also get details of Receipt applications too

select ard.source_id,gjh.period_name,gjh.name journal,gjh.JE_CATEGORY,gjh.JE_SOURCE,gcc.concatenated_segments--xdl.*

          ,gcc.SEGMENT2 sbu,gcc.segment5 natural_account,gcc.segment6 sub_account,gcc.SEGMENT8 region,

          gjl.accounted_dr Debit_amount,gjl.accounted_cr credit_amount

          ,xdl.SOURCE_DISTRIBUTION_TYPE,xal.ACCOUNTING_CLASS_CODE,

          ard.SOURCE_ID, ard.SOURCE_TABLE, ard.SOURCE_TYPE,

     gjl.DESCRIPTION,

     (select party_name 

        from apps.hz_cust_accounts hca,

             apps.hz_parties hp

       where hca.cust_account_id = acr.PAY_FROM_CUSTOMER      

         and hca.party_id = hp.party_id) customer_name,

        gjh.POSTED_DATE     ,

        null trx_number,

        arm.name receipt_method,

        null transaction_type,

        null purchase_order

from apps.gl_je_headers gjh,

     apps.gl_je_lines gjl,

     apps.xla_ae_lines xal,

     apps.xla_distribution_links xdl,

     apps.xla_ae_headers xah,

     apps.gl_code_combinations_kfv gcc,

     apps.AR_DISTRIBUTIONS_ALL ard,

     apps.AR_CASH_RECEIPT_HISTORY_ALL acrh,

     apps.ar_cash_receipts_all acr,

     apps.ar_receipt_methods arm

where 1=1

AND gjl.code_combination_id = gcc.code_combination_id

and gjh.ledger_id = gjl.ledger_id

and gjh.je_header_id = gjl.je_header_id

and gjl.gl_sl_link_id = xal.gl_sl_link_id

and xal.ae_header_id = xdl.ae_header_id

and xal.ae_line_num = xdl.ae_line_num

and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'

and xah.ae_header_id = xal.ae_header_id

and  ARD.LINE_ID = XDL.source_distribution_id_num_1

--AND ard.source_type = 'CRH'

and ard.SOURCE_ID  = acrh.CASH_RECEIPT_HISTORY_ID

and acr.cash_receipt_id = acrh.cash_receipt_id

and acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)

UNION

select ard.source_id,gjh.period_name,gjh.name journal,gjh.JE_CATEGORY,gjh.JE_SOURCE,gcc.concatenated_segments--xdl.*

          ,gcc.SEGMENT2 sbu,gcc.segment5 natural_account,gcc.segment6 sub_account,gcc.SEGMENT8 region,

          gjl.accounted_dr Debit_amount,gjl.accounted_cr credit_amount

          ,xdl.SOURCE_DISTRIBUTION_TYPE,xal.ACCOUNTING_CLASS_CODE,

          ard.SOURCE_ID, ard.SOURCE_TABLE, ard.SOURCE_TYPE,

     gjl.DESCRIPTION,

     (select party_name 

        from apps.hz_cust_accounts hca,

             apps.hz_parties hp

       where hca.cust_account_id = acr.PAY_FROM_CUSTOMER      

         and hca.party_id = hp.party_id) customer_name,

        gjh.POSTED_DATE     ,

        rcta.trx_number,

        arm.name receipt_method,

        (select name from apps.ra_cust_trx_types_all where cust_trx_type_id = rcta.cust_trx_type_id) transaction_type,

        rcta.purchase_order

from apps.gl_je_headers gjh,

     apps.gl_je_lines gjl,

     apps.xla_ae_lines xal,

     apps.xla_distribution_links xdl,

     apps.xla_ae_headers xah,

     apps.gl_code_combinations_kfv gcc

     ,apps.AR_DISTRIBUTIONS_ALL ard,

     AR_RECEIVABLE_APPLICATIONS_ALL ara,

     apps.ar_cash_receipts_all acr,

     apps.ra_customer_trx_all rcta,

     apps.ar_receipt_methods arm

where 1=1

AND gjl.code_combination_id = gcc.code_combination_id

and gjh.ledger_id = gjl.ledger_id

and gjh.je_header_id = gjl.je_header_id

and gjl.gl_sl_link_id = xal.gl_sl_link_id

and xal.ae_header_id = xdl.ae_header_id

and xal.ae_line_num = xdl.ae_line_num

and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'

and xah.ae_header_id = xal.ae_header_id

and  ARD.LINE_ID = XDL.source_distribution_id_num_1

--AND ard.source_type = 'RA'

and ard.SOURCE_ID  = ara.RECEIVABLE_APPLICATION_ID

and ara.CASH_RECEIPT_ID = acr.CASH_RECEIPT_ID

and ara.APPLIED_CUSTOMER_TRX_ID = rcta.customer_trx_id(+)

and acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)

Order Line Not Invoiced

When a Sales Order is Created It may happen that you expect to see and invoice for the Order once the lines are closed but Invoices dont appear there can be a number of reasons for it :

1. The item on the Order lines is not enabled for invoicing , check the Invoice Enabled Flag on the Shipping Warehouse , IVO and Item Master
2. The order is an Internal Sales Order , these orders are not invoiced
3.Configuration items are not invoiceable
4. If line flow does not invluce the Invoice Interface Activity then the invoice invoice_interface_status_code will not be set and remain null

One of the above scenario should be valid and your issue should get resolved 

Journal Batch Posting Error

When Posting a Journal Batch you might encounter an Error where the Posting program fails .
In such cases first thing is to check the OUTPUT and LOG file for potential messages displayed by Oracle.

One of the errors which can occur is : ORA-00001: unique constraint (GL.GL_JE_HEADERS_U2) violated

If you see the table gl_je_headers there is an unique index on Batch Id and Journal Name.

For the same batch you will see that the Journals have a unique name but when doing posting Oracle checks for the First 78 characters of the Journal Name and Batch Id Uniquenss , if the condition is violated we will get the above error.

To resolve the error open the Journals in the batch update the names by adding Unique numbers at the beinging , save your work and Post again

Issue will be resolved.

Multiple Organization Access Control (MOAC)

One of the biggest changes available to users in migrating from 11i to R12 is Oracle MOAC Structure,

In 11i in a given Legal entity is a Business had 2 Operating units A and B , then if a User wishes to see the information attached to Operating unit A and the he needs to use the responsibility associated to the Operating unit A , and similar is the case for B

In R12 the scenario is different , Oracle has made available the feature of Security Profiles based on which the user from one responsibility only can have access to data across Multiple Operating units in a given legal entity.

thus in the above Scenario the user can access the data of both the operating units using the same responsibility and will not need to change the responsibility for every operation,

This feature is enabled by the use of Security profiles which we will discuss soon