R12 ap to gl link

SELECT   c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num,
         te.source_id_int_1, te.application_id, te.entity_id, h.je_source,
         h.je_category, i.gl_date, s.vendor_name, s.segment1 AS supplier_no,
         l.event_class_code AS event_class, i.invoice_id,
         ad.invoice_distribution_id, i.invoice_num AS transaction_number,
         i.invoice_date, INITCAP (jl.description) description,
         jl.accounted_dr AS debit, jl.accounted_cr AS credit,
         NVL (jl.accounted_dr, 0), NVL (jl.accounted_cr, 0) net_amount
    FROM apps.gl_je_headers h,
         apps.gl_je_lines jl,
         apps.gl_code_combinations c,
         apps.gl_import_references r,
         apps.xla_ae_lines al,
         apps.xla_ae_headers ah,
         apps.xla_distribution_links l,
         apps.ap_invoices_all i,
         apps.ap_invoice_distributions_all ad,
         apps.ap_suppliers s,
         apps.xla_events e,
         apps.xla_transaction_entities te
   WHERE ad.accounting_date BETWEEN :startdate AND :enddate
     AND c.code_combination_id = 6429
       AND ad.line_type_lookup_code = ‘item’
     AND jl.je_header_id = h.je_header_id
     AND jl.code_combination_id = c.code_combination_id
     AND al.gl_sl_link_id = r.gl_sl_link_id
     AND al.ae_header_id = ah.ae_header_id
     AND al.application_id = ah.application_id
     AND ah.application_id = e.application_id
     AND ah.event_id = e.event_id
     AND e.application_id = te.application_id(+)
     AND e.entity_id = te.entity_id(+)
     AND r.je_header_id = jl.je_header_id
     AND r.je_line_num = jl.je_line_num
     AND l.ae_header_id = al.ae_header_id
     AND l.ae_line_num = al.ae_line_num
     AND l.applied_to_source_id_num_1 = i.invoice_id
     AND l.source_distribution_id_num_1 = ad.invoice_distribution_id
     AND ad.invoice_id = i.invoice_id
     AND i.vendor_id = s.vendor_id
ORDER BY i.gl_date DESC

Possible joins in XLA tables

Joins in XLA tables :-

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process


– Applies accounting rules
– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah
XLA_AE_LINES xal

XLA_EVENTS xae
XLA_TRANSACTION_ENTITIES xte
XLA_DISTRIBUTION_LINKS xdlGL_IMPORT_REFERENCES gir
Below are the possible joins between these XLA Tables
xah.ae_header_id = xal.ae_header_id
xah.entity_id = xte.entity_id

xae.entity_id = xte.entity_id

xah.event_id = xlae.event_id
xah.ae_header_id = xdl.ae_header_id
xah.ae_line_num = xdl.ae_line_num
xah.event_id = xdl.event_id
xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xae.application_id = xte.application_id

xah.application_id = xae.application_id
xah.application_id = (Different value based on Module)

xte.entity_code =
'TRANSACTIONS' or
'RECEIPTS' or
'ADJUSTMENTS' or
'PURCHASE_ORDER' or
'AP_INVOICES' or'AP_PAYMENTS' or
'MTL_ACCOUNTING_EVENTS' or
'WIP_ACCOUNTING_EVENTS'
xte.source_id_int_1 =
'INVOICE_ID' or
'CHECK_ID' or
'CUSTOMER_TRX_ID' or

'TRANSACTION_ID'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types:-

 

xdl.source_distribution_type = 'AP_PMT_DIST '

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

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

xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

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

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
----------------------------------------------------------------------------------------------------xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

----------------------------------------------------------------------------------------------------xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

----------------------------------------------------------------------------------------------------xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

----------------------------------------------------------------------------------------------------xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

R12 – Banks – Creation / Step by Step

Responsibility – Cash Management Super User
Navigation – Setup – Banks – Banks

Clicking on this opens an OAF page

Click on Create – opens the following.

Enter the necessary details required and click Save and Next.

Update the Bank Addresses by clicking on Create

Enter the information and click on Finish

Click on Save and Next

Click on Create Contact .
Enter the necessary information required.


Click Apply once the details are entered.

Click on Finish.  The bank details are here in this screen.

Bank is Created Successfully. Now we will see how to create a Branch underneath the Bank which we just Created.
Click on Create Branch icon

Click on Continue from the below screen

In the below screen enter the Branch Details as shown

Click Save and Next.

Click on Create button in the above screen to add the Branch’s address

Enter the address and then click Apply.

Click Save and Next

Click on Create Contact button to add the Contact Details


Click on Apply

Click on Finish.

To Create Account – click on the Icon below…

Click on Create button from the below screen

Enter the details in the screen below

Click on Continue

Enter the necessary details in the screen above

Click on Next

Click on Save and Next

Apex Validations:

                                                                         Apex Validations:

Read only :

SELECT PARTY_NAME  FROM OGS_CM_PARTIES
WHERE PARTY_ID = :P42_PARTY_ID

Date :

if TO_DATE(:P42_START_DATE,'DD-MON-YYYY') <=TO_DATE(:P42_END_DATE,'DD-MON-YYYY' ) or :P42_END_DATE is null then
   return true;
else
   return false;
end if;

TO_DATE(:P2_HIRE_DATE,'DD-MM-YYYY') < SYSDATE

Read only :

PL/SQL Function body ret bol:

IF :P22_EMPLOYEE_NUMBER IS NOT NULL THEN
   RETURN TRUE;
ELSE
   RETURN FALSE;
END IF;