Find Payables Orphan Accounting Events

For orphan processed non-budgetary events the following query can be run.

SELECT DISTINCT 'EVENT', xe.EVENT_ID,xe.EVENT_STATUS_CODE,xe.PROCESS_STATUS_CODE,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xe.event_date,
xe.entity_id,
xe.event_type_code,
xe.budgetary_control_flag,
xe.upg_batch_id,
'Y'
FROM xla_events xe,
xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xe.application_id = 200
AND xe.event_status_code = 'P'
AND xah.application_id = 200
AND xah.event_id = xe.event_id
AND xte.entity_id=xe.entity_id
AND xte.application_id=200
AND nvl(xe.budgetary_control_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND xe.event_type_code NOT IN ('MANUAL', 'REVERSAL')
AND (xe.upg_batch_id IS NULL OR xe.upg_batch_id = -9999);

For unprocessed non-budgetary events run the query below, it should not have any rows other than those updated to N in Step 3:

SELECT DISTINCT 'EVENT', xe.EVENT_ID,xe.EVENT_STATUS_CODE,xe.PROCESS_STATUS_CODE,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xe.event_date,
xe.entity_id,
xe.event_type_code,
xe.budgetary_control_flag,
xe.upg_batch_id,
'Y'
FROM xla_events xe,
xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xe.application_id = xte.application_id
AND xe.event_status_code <> 'P'
and xe.event_id = xah.event_id (+)
and xah.application_id(+) = xe.application_id
AND xte.entity_id=xe.entity_id
AND xte.application_id=200
AND nvl(xe.budgetary_control_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND xe.event_type_code not in ('MANUAL', 'REVERSAL')
AND (xe.upg_batch_id is NULL or xe.upg_batch_id = -9999)
AND NOT EXISTS
(SELECT 'No final accounted headers'
FROM xla_ae_headers xah
WHERE xah.event_id = xe.event_id
AND xah.application_id=200
AND xah.entity_id = xte.entity_id
AND xah.accounting_entry_status_code='F'
AND xah.gl_transfer_status_code='Y')
UNION
SELECT DISTINCT 'HEADER', xah.EVENT_ID,null,null,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xah.accounting_date,
xah.entity_id,
xah.event_type_code,
null,
xah.upg_batch_id,
'Y'
FROM xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xah.application_id = xte.application_id
AND xte.entity_id=xah.entity_id
AND xte.application_id=200
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT ' Event for this header does not exist'
FROM xla_events xe
WHERE xe.application_id = xah.application_id
AND xe.event_id = xah.event_id)
AND xah.event_type_code not in ('MANUAL', 'REVERSAL')
AND (xah.upg_batch_id is NULL or xah.upg_batch_id = -9999)
AND nvl(xah.gl_transfer_status_code, 'X') <>'Y'
AND nvl(xah.accounting_entry_status_code, 'X') <> 'F';

Subledger Accounting in R12

Concept behind Subledger Accounting

13th May 2008, 05:50 pm

Before going into any detail, let me take you into accounting world for a brief moment. Fundamentally accounting is based on two methods : Cash Basis or Accrual Basis.

Accrual Basis Accounting

Under the accrual basis accounting, revenues and expenses are recognized as follows:

AR:

•  Revenue recognition: Revenue is recognized when both of the following conditions are met:
      a. Revenue is earned.
      b. Revenue is realized or realizable.
•  Revenue is earned when products are delivered or services are provided.
• Realized means cash is received.
• Realizable means it is reasonable to expect that cash will be received in the future.

AP:

• Expense recognition: Expense is recognized in the period in which related revenue is recognized (Matching Principle).

Cash Basis Accounting

Under the cash basis accounting, revenues and expenses are recognized as follows:

AR:

• Revenue recognition: Revenue is recognized when cash is received.

AP:

• Expense recognition: Expense is recognized when cash is paid.

 Timing differences in recognizing revenues and expenses:

1. Accrued Revenue: Revenue is recognized before cash is received.
2. Accrued Expense: Expense is recognized before cash is paid.
3. Deferred Revenue: Revenue is recognized after cash is received.
4. Deferred Expense: Expense is recognized after cash is paid.

                 Options in 11i                            To                                Options in 12

Till 11i the only way we represent this accounting method is by choosing accounting method in Payables Options in AP and System Options in AR. But in R12 you can see in that these options are gone from the system options of AP and AR. That is where subledger accounting comes in.

Part of the global release concept in R12, accounting methods have to be much more flexible and generation of accounting entries should be configurable.

As we know accounting is the end product of transctions and financial statements are end products of accounting. Also there is a need to seperate transaction from accounting. An accounting clerk who creates an invoice has nothing to do what accounting is behind that transaction. It is the duty of the management to decide accounting behind this transaction.  

Subledger Accounting is taking us in that direction.

Purpose of Subledger Accounting
The end product of Subledger Accounting Setups is a Subledger Accounting Method that can be assigned to one or more ledgers in GL. All accounting in different subledger applications is subject to the rules defined in this accounting method.

In 11i, as mentioned earlier, the only way to choose accounting method we chose is AR and AP system options setup (Cash Vs Accrual). We used start in GL setting up the Set of books and then define the organization information like Legal Entity and Operating units and so on. And then define these accounting methods for each operating unit. As you can see operations and accounting are so closely meshed with each other. But in R12 it is not the same. In this release it is now configurable in Subledger Accounting setups taking this away from system options of individual products.

Demystifying subledger accounting setups

Out of the box, Oracle seeds accounting rules for all applications. If you are satisfied with the Oracle’s seeded rules, there is no need to change any setup and you can use those existing rules (Accounting Method for Accrual is Standard Accrual and for Cash is Standard Cash). This screenshot here shows you the difference between the Accrual Basis of accounting and Cash Basis of Accounting. As you can see here, per rules, there is no accounting created when invoice is created under cash basis (no revenue is recognized until cash is received) but accounting is created when cash is realized. Invoice is accounted as soon it is completed under Accrual Method. This is configurable here where as in 11i we did not have a choice!.

If you choose this accounting method, accounting works exactly the way it works in previous releases.

Subledger Accounting as a gatekeeper of Reconciliation

       R11i Transfer to GL                                                                   R12 Transfer to GL

Starting R12 all accounting entries are generated and passed through subledger accounting application instead of directly going to GL. Hence reconciliation is already done between source to Subledger Accounting and Subledger Accounting to GL, reducing huge amount of time spent on reconciliation. Since these entries have to flow through the subledger accounting application, there is a need to map the source application accounting entries to subledger accounting. That is key for the setups.

Mapping a transaction to Subledger Accounting Setup

                                        AR Invoice Accounting

Let us take a simple example. Whenever you create an AR Invoice following accounting takes place.  

                             

                             Invoice Accounting in AR

Taking a step back and thinking through, this transaction is happening in AR for the Invoice Creation event….                                             

                                            Subledger Accounting Setup Model

Now we map the source (AR Invoices) to Subledger Accounting as shown here. So to conclude

Journal Line Types are nothing but accounting line types (Receivable or Revenue).

Event Classes identify a transaction type (Invoice Vs Credit Memo).

These two are assembled using Accounting Derivation Rules and Sources.

All these together make up Application Accounting Definition for Receivables. 

Different Application Accounting Definitions together make up a Subledger Accounting Method.

This method can be attached to one or more Ledgers.

How SLA derives all the rules required for Create Accounting?

How SLA derives all the rules required for Create Accounting?

13th January 2010, 09:11 pm

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?

We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.

This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.

Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C';

DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);

FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);

BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ’0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ’0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;

R12 Queries SLA tables

R12 Queries SLA tables

To get the sum from the SLA tables.

QR13 - Sum from the SLA Tables

select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151 <>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1 <>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code


R12.B) To get the sum from the GL tables

QR14 - Sum from the GL Tables

select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <>
group by l.code_combination_id, k.concatenated_segments, currency_code

R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.

QR15 - To get the difference from SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011 -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code

Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB

QR16 - Get the XTB Difference

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code

The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.

QR17 - Get the difference from the SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id



QR18 - Get the difference from the XTB table

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id


If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.

QR19 - SLA query considering the SLA Manual entries

select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id


QR20 - GL query for amounts per Group ID

select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;

R12 SLA - Subledger Accounting Setup

R12 SLA - Subledger Accounting 
Please find first in the series of articles on R12 Subledger Accounting .
This article is Part-1 of Subledger Accounting.

Why SLA and what does it do?Oracle Sub Ledger accounting (SLA) is accounting hub in Oracle Application Release 12 (R12). It is used to derive all attributes required to account a transaction in Oracle General Ledger. In R12, SLA is used to derive the very basic accounting attributes like entered amount, accounted amount, Date, Currency code etc and the complex attributes like Ledger, Code Combination ID, Periods etc. After deriving these accounting attributes the transactions are then interfaced to GL from SLA. Thus in R12 no sub ledgers (AP, PO, PA etc) interfaces the transactions directly to GL, but all the transactions are interfaced to GL in following 2 steps: 
1. Sub ledgers interface the data to SLA.
2. SLA derives the accounting information and interfaces the data to GL. 

SLA gives the flexibility to manage the entire accounting rule at one place, which acts as a single source of truth for GL. 

Note: There is no separate responsibility to access SLA setup or the view the transactions generated by SLA. Rather we can access SLA setup and review accounted transactions with extended menus attached to each sub ledger module. 

Menu to access the SLA accounted transactions. 



Menu to access the SLA Setup. 
 


How does SLA works? 1. Register sub ledger transactions in SLA. After validating / approving / costing the transaction in the respective module, the sub ledger calls SLA API to create a reference of the validated transaction in SLA. This reference is known as EVENT. Events are created by calling the public API “xla_events_pub_pkg.create_events” provided by SLA. It is up to the sub ledgers on how to call the API. For example Oracle Projects call this API from concurrent program “PRC: Generate Cost Accounting Events” and Oracle Payables calls this API while user creates accounting for the Invoice. 

While calling xla_events_pub_pkg.create_events, oracle passes a unique id and event class (Will discuss in next step). Unique ID can be an invoice id or a po_distribution id or an expenditure_item_id etc. As soon as the sub ledger generates event in SLA, SLA returns unique event_id. This event_id will then act as a reference to all the accounting entries generated by the SLA. Once event is successfully created in SLA, means that the transaction is registered in SLA for accounting. 

Taking the example of Oracle Projects in 11i where after costing the transaction user need to run the ‘PRC: Interface Cost to General Ledger’ followed by ‘Journal Import’ followed by ‘PRC: Tieback process’. But in R12 user only need to run “PRC: Generate Cost Accounting Events” which will register events in SLA and thereafter SLA will take care of accounting the transaction and interfacing it to GL. There is no tieback process in R12, as there is one to one reference of event id between SLA and sub ledger tables. 


2. How does SLA understand whether unique id is invoice id or a po_distribution id or an expenditure_item_id as SLA uses same table to store all the identifier? In step 1 we discussed that while creating the event we also need to pass event class. This event class is used to distinguish between the types of transaction passed for processing. To understand this better we will go thru the seeded oracle information. 


Navigation: 

Responsibility: Projects, Vision Services (USA) 
Menu: Setup > Subledger Accounting > Accounting Methods Builder > Events > Event Modal 


This screen shows the hierarchical structure of different transactions that can be interfaced to SLA. Because the above screen shot is from Oracle Projects responsibility thus it shows only the projects related transactions. In the entity screen we see only those transactions that can be interfaced to the GL, that’s why we do not see Invoice as one of the entity as Invoices are not directly interfaced to GL from PA but they are routed thru AR. 

Identifiers are the unique ID that is passed to SLA from sub ledgers. Per the screenshot Oracle is passing expenditure_Item_id for entity ‘EXPENDITURE’. “Identifier Column” field under Identifier window tells what column in SLA table should store expenditure_item_id. The identifier columns that can be used are SOURCE_ID_INT_1 to 4, SOURCE_ID_DATE_1 to 4, SOURCE_ID_CHAR_1 to 4 these values and columns are present in table XLA_TRANSACTION_ENTITIES. 


Event Class window displays the different kind of expenditure transactions that can be interfaced to GL. This level of hierarchy is known as Event class, which is further classified into Event Types. In PA we have different event types like Labor Cost, Misc Cost, Usage Cost, Supplier Cost etc. Further we could classify Supplier Cost as Expense Report and Invoices as Oracle Projects can interfaces only these 2 transactions from AP. 




3. Based on the identifiers and event class, how SLA creates accounting lines? After registering the event in SLA, we can create accounting entries by running executable XDODTEXE. This executable is provided by SLA and is used by all the sub ledgers with different concurrent program names. Around 160 concurrent programs are uses the same executable for example in Projects it is used with name “PRC: Create Accounting”. This executable does the following: 
a. Gather information from base tables in sub ledgers. 
b. Derive the accounting attributes based on the data fetched from sub ledgers. 
c. Derive code combination id based on the business rules. 
d. Create journal lines based on the seeded Journal definition. 
e. Create lines in XLA_AE_HEADERS and XLA_AE_LINES. 

XLA Links to Other Modules

CREATE OR REPLACE PROCEDURE APPS.XX_GL_MV_DRILL_REPORT (errbuf VARCHAR2, retcode NUMBER)
AS
--START_DATE DATE;
CURSOR LEG_ID IS (SELECT '2027' LEDGER_ID FROM DUAL
                  UNION
                  SELECT '2062' LEDGER_ID FROM DUAL);

/*SELECT GSB.LEDGER_ID
FROM GL_LEDGERS GSB
WHERE EXISTS(SELECT 1 FROM GL_BALANCES GB WHERE GB.LEDGER_ID=GSB.LEDGER_ID));
*/
BEGIN

   -----------------*********DELETE******--------------
         EXECUTE IMMEDIATE 'TRUNCATE TABLE XX_GL_MV_REPORT';
         COMMIT;

   -----------------*********FLAG 1******--------------




FOR GL_LED_ID IN LEG_ID
LOOP
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '1' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, acr.doc_sequence_value, amcd.gl_date,
                   acr.receipt_number trx_number, acr.cash_receipt_id,
                   rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, acr.receipt_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   NVL (acr.comments, acr.reversal_comments) narration,
                   rc.customer_id, rc.customer_number, hcsu.site_use_id,
                   hcsu.site_use_code, acr.status check_status,
                   acr.reversal_date check_void_date, cc.account_type,
                   NULL reference1, jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ar_distributions_all ada,
                   ar_misc_cash_distributions_all amcd,
                   ar_cash_receipts_all acr,
                   hr_operating_units hou,
                   fnd_user fu,
                   hz_cust_site_uses_all hcsu,
                   ar_customers rc,
                   DUAL
             WHERE 1 = 1
               AND xlajed.source_distribution_id_num_1 = ada.line_id
               AND ada.source_table = 'MCD'
               AND ada.source_id = amcd.misc_cash_distribution_id
               AND amcd.cash_receipt_id = acr.cash_receipt_id
               AND acr.org_id = hou.organization_id
               AND acr.last_updated_by = fu.user_id(+)
               AND acr.pay_from_customer = rc.customer_id(+)
               AND acr.customer_site_use_id = hcsu.site_use_id(+)
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               AND jeh.je_category = 'Misc Receipts'
               AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
               --AND JEL.EFFECTIVE_DATE BETWEEN AND SYSDATE



);

fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 1');

   -----------------*********FLAG 2******--------------
           INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '2' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, NULL org_name,
                   NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
                   adj.doc_sequence_value, adj.gl_date,
                   rct.trx_number trx_number, rct.customer_trx_id,
                   rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, jel.effective_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   adj.comments narration, rc.customer_id, rc.customer_number,
                   hcsu.site_use_id, hcsu.site_use_code, NULL check_status,
                   NULL check_void_date, cc.account_type, NULL reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ar_distributions_all ada,
                   ar_adjustments_all adj,
                   ra_customer_trx_all rct,
                   hz_cust_site_uses_all hcsu,
                   hr_operating_units hou,
                   ar_customers rc,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND xlajed.source_distribution_id_num_1 = ada.line_id
               AND ada.source_table = 'ADJ'
               AND ada.source_id = adj.adjustment_id
               AND adj.org_id = hou.organization_id
               AND adj.last_updated_by = fu.user_id(+)
               AND rct.customer_trx_id = adj.customer_trx_id
               AND rct.bill_to_customer_id = rc.customer_id(+)
               AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               AND jeh.je_category = 'Adjustment'
               AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------


);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 2');

  -----------------*********FLAG 4******--------------
         INSERT INTO XX_GL_MV_REPORT
         (SELECT flag, code_combination_id, SOURCE, set_of_books_id, sob, CATEGORY,
          period, batch_name, header_name, description, currency,
          sob_currency, entered_debit, entered_credit, accounted_debit,
          accounted_credit, org_name, user_name, gl_voucher_number,
          posted_date, sl_voucher_number, gl_date, trx_number,
          cash_receipt_id, cust_supp_name, effective_date, receipt_date,
          gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7,
          gl_seg8, gl_seg9, gl_seg10, gl_seg11, gl_seg12, gl_name_seg1,
          gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5,
          gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9,
          gl_name_seg10, gl_name_seg11, gl_name_seg12, exp_emp_no, project_no,
          trx_batch_name, status, narration, customer_id, customer_number,
          site_use_id, site_use_code, check_status, check_void_date,
          account_type, reference1, creation_date, je_header_id,
          accrual_rev_je_header_id, accrual_rev_period_name, dummy,
          payment_status, invoice_type_code, je_line_num, gl_fbt, ap_fbt
         FROM (SELECT '4' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id set_of_books_id, gsb.NAME sob,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code currency, gsb.currency_code sob_currency,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credit, hou.NAME org_name,
                  INITCAP (fu.description) user_name,
                  jeh.doc_sequence_value gl_voucher_number, jeh.posted_date,
                  acr.doc_sequence_value sl_voucher_number,
                  jeh.posted_date gl_date,
                                          --acrh.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL dummy, NULL payment_status, NULL invoice_type_code,
                  jel.je_line_num, jel.attribute3 gl_fbt, NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_cash_receipt_history_all acrh,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND jeh.je_header_id = 1825109
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'CRH'
              --AND ada.source_type = 'CASH'
              AND ada.source_type IN ('CASH', 'BANK_CHARGES')
              AND ada.source_id = acrh.cash_receipt_history_id
              AND acrh.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN
                --       ('Receipts', 'Credit Memos', 'Misc Receipts')
              AND jeh.je_category IN
                     ('Receipts',
                      'Credit Memos',
                      'Misc Receipts',
                      'Debit Memos'
                     )
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND acrh.org_id = hou.organization_id
              --AND acrh.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
           UNION
           SELECT '4' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date, acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                                          --ara.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND jeh.je_header_id = 1825109
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'RA'
              --AND ada.source_type NOT IN ('UNID')
              AND ada.source_id = ara.receivable_application_id
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN ('Receipts', 'Credit Memos')
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              --AND ara.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
--AND cc.gl_seg2 != '417001'
           UNION
           SELECT DISTINCT '4' flag, jel.code_combination_id,
                           NVL (src.user_je_source_name, '**********') SOURCE,
                           gsb.ledger_id, gsb.NAME,
                           NVL (cat.user_je_category_name,
                                '**********'
                               ) CATEGORY,
                           jel.period_name period_name, jeb.NAME batch_name,
                           jeh.NAME header_name, jel.description description,
                           jeh.currency_code,
                           gsb.currency_code sob_currency_code,
                           xlajel.entered_dr entered_debit,
                           xlajel.entered_cr entered_credit,
                           xlajel.accounted_dr accounted_debit,
                           xlajel.accounted_cr accounted_credits,
                           hou.NAME org_name,
                           INITCAP (fu.description) user_name,
                           jeh.doc_sequence_value, jeh.posted_date,
                           rct.doc_sequence_value, jeh.posted_date gl_date,
                           rct.trx_number trx_number, rct.customer_trx_id,
                           rc.customer_name cust_supp_name,
                           xlajel.accounting_date effective_date,
                           rct.trx_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3,
                           cc.gl_seg4, cc.gl_seg5, cc.gl_seg6, cc.gl_seg7,
                           cc.gl_seg8, cc.gl_seg9, cc.gl_seg10, cc.gl_seg11,
                           cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                           cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                           cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                           cc.gl_name_seg9, cc.gl_name_seg10,
                           cc.gl_name_seg11, cc.gl_name_seg12,
                           NULL exp_emp_no, NULL project_no,
                           NULL trx_batch_name, jeh.status,
                           rct.comments narration, rc.customer_id,
                           rc.customer_number, hcsu.site_use_id,
                           hcsu.site_use_code, rct.status_trx check_status,
                           NULL check_void_date, cc.account_type,
                           NULL reference1, jel.creation_date,
                           jeh.je_header_id, jeh.accrual_rev_je_header_id,
                           jeh.accrual_rev_period_name, NULL, NULL, NULL,
                           jel.je_line_num, jel.attribute3 gl_fbt,
                           NULL ap_fbt
                      FROM disc_ccid_dsc_mv cc,
                           gl_ledger_names_v gsb,
                           gl_je_lines jel,
                           gl_je_headers jeh,
                           gl_import_references gir,
                           gl_je_batches jeb,
                           gl_je_categories cat,
                           gl_je_sources src,
                           xla_ae_lines xlajel,
                           xla_distribution_links xlajed,
                           ar_distributions_all ada,
                           ar_receivable_applications_all ara,
                           ra_customer_trx_all rct,
                           hr_operating_units hou,
                           fnd_user fu,
                           hz_cust_site_uses_all hcsu,
                           ar_customers rc,
                           DUAL
                     WHERE 1 = 1
                       --AND jeh.je_header_id = 1825109
                       AND xlajel.ae_header_id = xlajed.ae_header_id
                       AND xlajel.ae_line_num = xlajed.ae_line_num
                       AND xlajed.source_distribution_id_num_1 = ada.line_id
                       AND ada.source_table = 'RA'
                       AND ada.source_id = ara.receivable_application_id
                       AND ara.customer_trx_id = rct.customer_trx_id
                       AND gir.je_header_id = jeh.je_header_id
                       AND gir.je_line_num = jel.je_line_num
                       AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
                       AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
                       AND jel.code_combination_id = cc.code_combination_id
                       AND jel.status || '' = 'P'
                       AND (   xlajel.accounted_dr != 0
                            OR xlajel.accounted_cr != 0
                           )
                       AND jeh.je_header_id = jel.je_header_id
                       AND jeh.actual_flag = 'A'
                       AND jeb.je_batch_id = jeh.je_batch_id
                       AND jeh.ledger_id = gsb.ledger_id
                       AND jeb.average_journal_flag = 'N'
                       AND src.je_source_name = jeh.je_source
                       AND cat.je_category_name = jeh.je_category
                       AND jeh.je_source = 'Receivables'
                       AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
                       AND xlajed.source_distribution_type =
                                                        'AR_DISTRIBUTIONS_ALL'
                       AND ara.org_id = hou.organization_id
                       AND jeh.last_updated_by = fu.user_id(+)
                       AND rct.bill_to_customer_id = rc.customer_id(+)
                       AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
           UNION ALL
           SELECT DISTINCT '4' flag, jel.code_combination_id,
                           NVL (src.user_je_source_name, '**********') SOURCE,
                           gsb.ledger_id set_of_books_id, gsb.NAME sob,
                           NVL (cat.user_je_category_name,
                                '**********'
                               ) CATEGORY,
                           jel.period_name period, jeb.NAME batch_name,
                           jeh.NAME header_name, jel.description description,
                           jeh.currency_code currency,
                           gsb.currency_code sob_currency,
                           xlajel.entered_dr entered_debit,
                           xlajel.entered_cr entered_credit,
                           xlajel.accounted_dr accounted_debit,
                           xlajel.accounted_cr accounted_credit,
                           NULL org_name, INITCAP (fu.description) user_name,
                           jeh.doc_sequence_value gl_voucher_number,
                           jeh.posted_date, NULL sl_voucher_number,
                           jeh.posted_date gl_date, NULL trx_number,
                           NULL cash_receipt_id, NULL cust_supp_name,
                           xlajel.accounting_date effective_date,
                           NULL receipt_date, cc.gl_seg1, cc.gl_seg2,
                           cc.gl_seg3, cc.gl_seg4, cc.gl_seg5, cc.gl_seg6,
                           cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                           cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                           cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                           cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                           cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                           cc.gl_name_seg11, cc.gl_name_seg12,
                           NULL exp_emp_no, NULL project_no,
                           NULL trx_batch_name, jeh.status, NULL narration,
                           NULL customer_id, NULL customer_number,
                           NULL site_use_id, NULL site_use_code,
                           NULL check_status, NULL check_void_date,
                           cc.account_type, NULL reference1,
                           jel.creation_date, jeh.je_header_id,
                           jeh.accrual_rev_je_header_id,
                           jeh.accrual_rev_period_name, NULL dummy,
                           NULL payment_status, NULL invoice_type_code,
                           jel.je_line_num, jel.attribute3 gl_fbt,
                           NULL ap_fbt
                      FROM disc_ccid_dsc_mv cc,
                           gl_ledger_names_v gsb,
                           gl_je_lines jel,
                           gl_je_headers jeh,
                           gl_import_references gir,
                           gl_je_batches jeb,
                           gl_je_categories cat,
                           gl_je_sources src,
                           xla_ae_lines xlajel,
                           xla_distribution_links xlajed,
                           fnd_user fu,
                           DUAL
                     WHERE 1 = 1
                       AND xlajel.ae_header_id = xlajed.ae_header_id
                       AND xlajel.ae_line_num = xlajed.ae_line_num
                       AND gir.je_header_id = jeh.je_header_id
                       AND gir.je_line_num = jel.je_line_num
                       AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
                       AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
                       AND jel.code_combination_id = cc.code_combination_id
                       AND jel.status || '' = 'P'
                       AND (   xlajel.accounted_dr != 0
                            OR xlajel.accounted_cr != 0
                           )
                       AND jeh.je_header_id = jel.je_header_id
                       AND jeh.actual_flag = 'A'
                       AND jeb.je_batch_id = jeh.je_batch_id
                       AND jeh.ledger_id = gsb.ledger_id
                       AND jeb.average_journal_flag = 'N'
                       AND src.je_source_name = jeh.je_source
                       AND cat.je_category_name = jeh.je_category
                       AND jeh.je_source = 'Receivables'
                       AND jeh.je_category IN
                              ('Receipts',
                               'Credit Memos',
                               'Misc Receipts',
                               'Debit Memos'
                              )
                       AND xlajed.source_distribution_type = 'XLA_MANUAL'
                       AND jeh.last_updated_by = fu.user_id(+))
                       WHERE set_of_books_id=GL_LED_ID.LEDGER_ID-------------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 4');
  -----------------*********FLAG 6******--------------
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '6' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, NULL org_name,
                   NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
                   NULL, rctlgd.gl_date, rct.trx_number trx_number,
                   rct.customer_trx_id, rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, jel.effective_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   rel_acctg_pkg.get_ar_invoice_detail
                                               (jel.ledger_id,
                                                rct.customer_trx_id
                                               ) narration,
                   rc.customer_id, rc.customer_number, hcsu.site_use_id,
                   hcsu.site_use_code, NULL check_status,
                   NULL check_void_date, cc.account_type, NULL reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ra_cust_trx_line_gl_dist_all rctlgd,
                   ra_customer_trx_all rct,
                   hz_cust_site_uses_all hcsu,
                   ar_customers rc,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajed.source_distribution_id_num_1 =
                                               rctlgd.cust_trx_line_gl_dist_id
               AND rctlgd.customer_trx_id = rct.customer_trx_id
               AND rct.bill_to_customer_id = rc.customer_id(+)
               AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
               AND rctlgd.last_updated_by = fu.user_id(+)
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               --AND jeh.je_category IN
               --             ('Sales Invoices', 'Chargebacks', 'Credit Memos')
               AND jeh.je_category IN
                            ('Sales Invoices', 'Chargebacks', 'Credit Memos', 'Debit Memos')
               AND xlajed.source_distribution_type =
                                                'RA_CUST_TRX_LINE_GL_DIST_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 6');
  -----------------*********FLAG 10******--------------
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '10' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
                   ai.invoice_num trx_number, ai.invoice_id,
                   pov.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ai.invoice_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   ai.description narration, pov.vendor_id,
                   pov.segment1 vendor_number, povs.vendor_site_id,
                   povs.vendor_site_code, NULL check_status,
                   NULL check_void_date, cc.account_type,
                   DECODE (ai.attribute_category,
                           'RL Invoice Header', ai.attribute10,
                           NULL
                          ) reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL,
                   ap_invoices_pkg.get_approval_status
                                  (ai.invoice_id,
                                   ai.invoice_amount,
                                   ai.payment_status_flag,
                                   ai.invoice_type_lookup_code
                                  ) payment_status,
                   ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
                   --,aid.attribute6 AP_FBT
                   , null ap_fbt
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_invoice_distributions_all aid,
                   ap_invoices_all ai,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND aid.invoice_id = ai.invoice_id
               AND xlajed.source_distribution_id_num_1 =
                                                   aid.invoice_distribution_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category = 'Purchase Invoices'
               AND xlajed.source_distribution_type = 'AP_INV_DIST'
               AND ai.org_id = hou.organization_id
               AND ai.last_updated_by = fu.user_id(+)
               AND ai.vendor_id = pov.vendor_id(+)
               AND ai.vendor_site_id = povs.vendor_site_id(+)
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 10');
  -----------------*********FLAG 11******--------------
         INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '11' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
                   ai.invoice_num trx_number, ai.invoice_id,
                   pov.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ai.invoice_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   ai.description narration, pov.vendor_id,
                   pov.segment1 vendor_number, povs.vendor_site_id,
                   povs.vendor_site_code, NULL check_status,
                   NULL check_void_date, cc.account_type,
                   DECODE (ai.attribute_category,
                           'RL Invoice Header', ai.attribute10,
                           NULL
                          ) reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL,
                   ap_invoices_pkg.get_approval_status
                                  (ai.invoice_id,
                                   ai.invoice_amount,
                                   ai.payment_status_flag,
                                   ai.invoice_type_lookup_code
                                  ) payment_status,
                   ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
                   --,aid.attribute6 AP_FBT
                   , null ap_fbt
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_prepay_app_dists apad,
                   ap_invoice_distributions_all aid,
                   ap_invoices_all ai,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND aid.invoice_id = ai.invoice_id
               AND apad.prepay_app_distribution_id =
                                                   aid.invoice_distribution_id
               AND xlajed.source_distribution_id_num_1 =
                                                       apad.prepay_app_dist_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category = 'Purchase Invoices'
               AND xlajed.source_distribution_type = 'AP_PREPAY'
               AND ai.org_id = hou.organization_id
               AND ai.last_updated_by = fu.user_id(+)
               AND ai.vendor_id = pov.vendor_id(+)
               AND ai.vendor_site_id = povs.vendor_site_id(+)
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 11');
  -----------------*********FLAG 12******--------------
         INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '12' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ac.doc_sequence_value,
                   appha.accounting_date gl_date,
                   TO_CHAR (ac.check_number) trx_number, ac.check_id,
                   ac.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ac.check_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   rel_acctg_pkg.get_payment_detail (jel.ledger_id,
                                                     ac.check_id,
                                                     'CHECK_ID',
                                                     'DESCR'
                                                    ) narration,
                   ac.vendor_id, pov.segment1 vendor_number,
                   povs.vendor_site_id, povs.vendor_site_code,
                   ac.status_lookup_code check_status,
                   void_date check_void_date, cc.account_type,
                   NULL reference1, jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_payment_hist_dists apphd,
                   ap_payment_history_all appha,
                   ap_checks_all ac,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu
             WHERE 1 = 1
               AND apphd.payment_history_id = appha.payment_history_id
               AND xlajed.source_distribution_type = 'AP_PMT_DIST'
               AND APPHA.TRANSACTION_TYPE=
                                            DECODE((SELECT 1
                                            FROM ap_payment_history_all AX ,
                                            ap_payment_history_all BS
                                            WHERE AX.CHECK_ID=APPHA.CHECK_ID
                                            AND AX.REV_PMT_HIST_ID=BS.PAYMENT_HISTORY_ID
                                            AND AX.TRANSACTION_TYPE='PAYMENT CANCELLED'),'1','PAYMENT CREATED',APPHA.TRANSACTION_TYPE)
               AND xlajed.source_distribution_id_num_1 =apphd.payment_hist_dist_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category IN ('Payments', 'Reconciled Payments')
               AND appha.check_id = ac.check_id
               AND ac.org_id = hou.organization_id
               AND ac.last_updated_by = fu.user_id(+)
               AND ac.vendor_id = pov.vendor_id(+)
               AND ac.vendor_site_id = povs.vendor_site_id(+)
               --AND CC.GL_SEG1=421
               --AND CC.GL_SEG2='515001'
               --AND JEH.JE_HEADER_ID=2213108
                AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 12');
 -----------------*********FLAG 13******--------------
         INSERT INTO XX_GL_MV_REPORT
(SELECT '13' flag, jel.code_combination_id,
          NVL (src.user_je_source_name, '**********') SOURCE, gsb.ledger_id,
          gsb.NAME, NVL (cat.user_je_category_name, '**********') CATEGORY,
          jel.period_name period_name, jeb.NAME batch_name,
          jeh.NAME header_name, jel.description description,
          jeh.currency_code, gsb.currency_code sob_currency_code,
          entered_dr entered_debit, entered_cr entered_credit,
          accounted_dr accounted_debit, accounted_cr accounted_credits,
          NULL org_name, INITCAP (fu.description) user_name,
          jeh.doc_sequence_value, jeh.posted_date,
          CASE
             WHEN jeh.je_source = 'Payables'
             AND jeh.je_category = 'Payments'
             AND SUBSTR (jel.description, 1, 1) BETWEEN '0' AND '9'
                THEN TO_NUMBER (jel.description)
             ELSE NULL
          END subledger_voucher_number,
          NULL subledger_gl_date, NULL trx_number, NULL trx_id,
          NULL cust_supp_name, jel.effective_date effective_date,
          jel.effective_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4,
          cc.gl_seg5, cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
          cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
          cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
          cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8, cc.gl_name_seg9,
          cc.gl_name_seg10, cc.gl_name_seg11, cc.gl_name_seg12,
          NULL exp_emp_no, NULL project_no, NULL trx_batch_name, jeh.status,
          jel.description narration, NULL vendor_id, NULL vendor_number,
          NULL vendor_site_id, NULL vendor_site_code, NULL check_status,
          NULL check_void_date, cc.account_type, NULL reference1,
          jel.creation_date, jeh.je_header_id, jeh.accrual_rev_je_header_id,
          NULL, NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
     FROM disc_ccid_dsc_mv cc,
          gl_ledger_names_v gsb,
          gl_je_lines jel,
          gl_je_headers jeh,
          gl_je_batches jeb,
          gl_je_categories cat,
          gl_je_sources src,
          fnd_user fu
    WHERE 1 = 1
      AND jel.code_combination_id = cc.code_combination_id
      AND jel.status || '' = 'P'
      AND (accounted_dr != 0 OR accounted_cr != 0)
      AND jeh.je_header_id = jel.je_header_id
      AND jeh.actual_flag = 'A'
      AND jeb.je_batch_id = jeh.je_batch_id
      AND jeh.ledger_id = gsb.ledger_id
      AND jeb.average_journal_flag = 'N'
      AND src.je_source_name = jeh.je_source
      AND cat.je_category_name = jeh.je_category
      AND (   jeh.je_source NOT IN ('Receivables', 'Payables')
           OR (       jeh.je_source IN ('Receivables', 'Payables')
                  AND NOT EXISTS (
                           SELECT 'X'
                             FROM gl_import_references gir
                            WHERE 1 = 1
                                  AND gir.je_header_id = jeh.je_header_id)
               OR (    jeh.je_source IN ('Receivables', 'Payables')
                   AND EXISTS (
                          SELECT 'X'
                            FROM gl_import_references gir
                           WHERE 1 = 1
                             AND gir.je_header_id = jeh.je_header_id
                             AND NOT EXISTS (
                                    SELECT 'X'
                                      FROM xla_ae_lines xlajel
                                     WHERE 1 = 1
                                       AND xlajel.gl_sl_link_id =
                                                             gir.gl_sl_link_id))
                  )
              )
          )
      AND jeh.last_updated_by = fu.user_id(+)
      AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

----------------------UNAPP EXCEPTION CASE-----------------
         INSERT INTO XX_GL_MV_REPORT
           (SELECT DISTINCT '19' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits,
                   hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date,
                  acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                  acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND xlajel.Ae_Header_Id=1283555
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 IN (50710,50711)
              AND xlajed.source_distribution_id_num_1=ADA.LINE_ID
              AND ada.source_table = 'RA'
              --AND ada.source_type NOT IN ('UNID')
              AND ara.receivable_application_id IN (3862)
              AND ARA.STATUS='UNAPP'
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN ('Receipts', 'Credit Memos')
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              --AND ara.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
              AND CC.GL_SEG1='421'-------------
              AND CC.GL_SEG2='416001'
              AND JEH.je_header_id='1692150'
              AND GSB.ledger_id=GL_LED_ID.LEDGER_ID);


----------------------DATA FIX CASE---
         INSERT INTO XX_GL_MV_REPORT
(SELECT
'16' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME HEADER_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR accounted_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
NULL user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
NULL trx_number,
NULL INVOICE_ID,
NULL VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
NULL NVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
GJL.DESCRIPTION narration,
NULL VENDOR_ID,
NULL vendor_number,
NULL VENDOR_SITE_ID,
NULL VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
NULL reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
NULL payment_status,
NULL invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT
XDL_IN.APPLICATION_ID,
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN) AX,
XLA_AE_LINES XAL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_CODE_COMBINATIONS GCC,
disc_ccid_dsc_mv cc
WHERE AX.AE_HEADER_ID IN (2123151,2123149,2123150,2123149,1917207,1917206,1917209,1917205,1917208,1917210,1917211)
AND AX.SOURCE_DISTRIBUTION_TYPE='XLA_MANUAL'
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID);

--------------------------EXCEPTION FOR JOURNAL IS A PART OF MANUAL ENTERY---------------
         INSERT INTO XX_GL_MV_REPORT
              (
SELECT
'17' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME batch_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR entered_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
INITCAP(fu.description) user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
APA.INVOICE_NUM trx_number,
APA.INVOICE_ID,
ASUP.VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
APA.INVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
APA.DESCRIPTION narration,
APA.VENDOR_ID,
ASUP.SEGMENT1 vendor_number,
ASA.VENDOR_SITE_ID,
ASA.VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
DECODE (APA.attribute_category,
       'RL Invoice Header', APA.attribute10,
       NULL
      ) reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
ap_invoices_pkg.get_approval_status
              (APA.invoice_id,
               APA.invoice_amount,
               APA.payment_status_flag,
               APA.invoice_type_lookup_code
              ) payment_status,
APA.invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT XDL_IN.APPLICATION_ID,----------------
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN )AX, -----------------
XLA_AE_LINES XAL,
AP_INVOICES_ALL APA ,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
fnd_user fu,
AP_SUPPLIERS ASUP,
disc_ccid_dsc_mv cc,
AP_SUPPLIER_SITES_ALL ASA,
DUAL
WHERE AX.AE_HEADER_ID IN (1478392,1478991,1724120,1726874,1726873,1728823,1728878)
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND (XAL.accounted_dr != 0 OR XAL.accounted_cr != 0)
AND AX.ALLOC_TO_SOURCE_ID_NUM_1=APA.INVOICE_ID
AND XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND APA.LAST_UPDATED_BY=fu.user_id(+)
AND ASUP.VENDOR_ID=APA.VENDOR_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND ASA.VENDOR_SITE_ID=APA.VENDOR_SITE_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID
);
--------------------FOR ACCOUNT 754001
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '15' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date, acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                                          --ara.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'RA'
              AND ada.source_id = ara.receivable_application_id
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
              AND CC.GL_SEG2='754001'
              AND CC.GL_SEG1=(SELECT DECODE(GL_LED_ID.LEDGER_ID,'2027','421','431') FROM DUAL)
              );



COMMIT;
fnd_file.put_line (fnd_file.LOG,'LOAD SUCCSSFULLY FOR-'||GL_LED_ID.LEDGER_ID);
END LOOP;
fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY');
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'ERROR WHILE LOADING DATA');
END;
/