[GL] Receiving Journal Transfer to GL by Journal Import

[GL] Receiving Journal Transfer to GL by Journal Import

Found a good article on Receiving (Purchasing) Journal transfer to GL by Journal Import.

Related SQL:
SELECT *
FROM apps.gl_interface
WHERE user_je_source_name = 'Purchasing'
AND user_je_category_name = 'Receiving'

In R11i for Inventory Destination Transactions, the Receiving Transaction Processor Debits and Credits the Receiving Account and Inventory AP Accrual Account respectively in GL_INTERFACE and RCV_RECEIVING_SUB_LEDGER. These Transactions in GL_INTERFACE are identified with User_je_source_name ='Purchasing' and user_je_category_name 'Receiving'. In RCV_RECEIVING_SUB_LEDGER these transactions are identified by column accrual_method_flag ='O' 
In R11i for Period End Transactions ie Transactions with accrue_on_receipt_flag ='N' in PO_DISTRIBUTIONS_ALL, no accounting transactions are generated on receipt of goods. The Receipt_Accrual_Period-End program generates the Accounting Entries in GL_INTERFACE and RCV_RECEIVING_SUB_LEDGER simultaneously by Debiting Charge Account and Crediting Expense AP Accrual Account. In GL_INTERFACE the Transactions are identified by column names user_je_source_name='Purchasing' and user_je_category_name ='Acccrual'. In RCV_RECEIVING_SUB_LEDGER the Transactions are identified by column Accrual_Method_flag ='P'. 

In R11i both On Line Transactions and Period End Transactions are moved from GL_INTERFACE to GL_IMPORT_REFERENCES,GL_JE_HEADERS and GL_JE_LINES by JOURNAL IMPORT. 

In R12 GL_INTEFACE TABLE is no longer directly used for Receiving Transactions. The Receiving Transaction Processor only populates the RCV_RECEIVING_SUB_LEDGER with Inventory Destination Transactions. The Receipt_Accrual_Period-End program also only populates the RCV_RECEIVING_SUB_LEDGER with Expense Destination Period End Accruals.

Create Accounting-Receiving Concurrent program from Cost Mangement Responsibility transfers the journals in RCV_RECEIVING_SUB_LEDGER to SLA Tables. Once the accounting entries are created in receiving subledger for the receiving transactions, Create Accounting process gets started depending on the profile option CST:Receiving Accounting option and the encumbrance set up. 

On submitting this program, it spawns the Accounting program which will do the validations and picks the data from table rcv_receiving_sub_ledger and process it according to the accrual method set for the POs 

Depending on the accrual method and the value for profile SLA:Disable journal import, this spawned program "Accounting program" inserts the data into gl_interface first and then calls the Journal import program and posting program and populates the accounting events in subledger table xla_ae_headers and entries in xla_ae_lines and xla_distribution_links. 
If there is any problem with the Journal import, the data will get rolled back from gl_interface. Once the Journal import is completed, data will get populated in gl_import_references,gl_je_headers and gl_je_lines.

[AR] Customer - Types & Address

[AR] Customer - Types & Address

Oracle has 2 customer types, Person or Organization.

Person is mainly use for retails business whereas Organization is used for company that has company as target customer.

Customer is differenciated by Party Number and Customer Number. If Customer TypeisPerson, Party Number refers to Person Number, if Customer Type is Organization, Party Number refers to Organization Number.

Party Number and Customer Number are cross OUs and organizations, this means the same customer just need to be defined once. In different OUs, the Party Number andCustomer Number will be shared and it will be differenciated by Customer Address.

[PO] R12 Purchasing Options

[PO] R12 Purchasing Options

Setup -> Organizations -> Purchasing Options

In R11i, it is devided into 5 sections, "Receipt Accounting", "Control", "Default", "InternalRequisition", "Numbering" and "Tax Defaults".

In R12, (this form was converted to JSP format), only 4 sections available (some of the fields been merged), "Document Control", "Document Defaults ", "Receipt Accounting" and "Document Numbering".

New fields in R12:

Document Control

Security Hierarchy

Maximum Attachment Size

Email Attachment Filename

Enforce Supplier Hold 

RFQ Required (from "Default" tab in R11i)

Document Defaults

Internal Requisition Order Type (from "Internal Requisition" tab in R11i)

Internal Requisition Order Source (from "Internal Requisition" tab in R11i)

Tax Defaults was removed from Purchasing Options. Need to confirm if it has been permanently removed or moved to other form.

[PO] Where to Setup Invoice Matching Option?

[PO] Where to Setup Invoice Matching Option?

Setup -> Financial Option, Supply - Entry tab, Invoice Matching Option field.

Supply -> Sites, Control tab, Invoice Matching Option field.

(A Setting At Any Level Will Override The Settings Above It)

2 type of options available, Purchase Order / Receipt

SQL Queries for checking Profile Option Values

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’

SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro1.user_profile_option_name like ('%Ledger%') and  pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */ and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2;

How to join GL tables with XLA (SubLedger Accounting) tables

How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id)                                   => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                                  => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)                      => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id) 
XLA_AE_HEADERS (application_id, event_id)                     => XLA_EVENTS (application_id, event_id)   
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id) 

The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.

The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

[Keywords: gl_import_references, xla_ae_lines, xla_ae_headers, xla_events, drilldown, SLA, XLA]