Saturday, April 2, 2016

R12 Self Assessment Tax in Oracle Accounts Payables

What is Self Assessment Tax:

A Self-Assessed Tax is a tax calculated and remitted for a transaction, where tax was not levied by the supplier but is deemed as due and therefore needs to be paid by the purchaser. In such cases the purchaser is responsible for calculating and remitting the tax to the Tax authority. Self-Assessment is also known as Use Tax or reverse charges in certain tax regimes.

As there is no Self-Assessment Tax amount on the reports it need to be included in all AP Invoices related reports.

●If goods purchased from a seller who is not registered for a tax then,
●To ensure that tax is not evaded on that transactions, it is buyers responsibility to calculate and remit the tax to relevant authority.
●Known as “Self Assessed Tax” or “Use Tax”

In AP unless the tax is self-assessed, we have to always pay it to the supplier. The logic is as entire amount is payable to supplier ultimately his account should get credited. Therefore this accounting entry is passed.
Expense A/c Dr. (item account) -----XXX
Tax Expense A/c Dr. (either item acc or the tax expense acc )--- YY
To, Supplier Account (Supplier account code combination) ----XXX
To, Supplier Account (tax amount but in Supplier account code combination) ----YY

However if it would have been self-assessed then since tax is payable separately to tax authority it has to be accounted for liability separately and then the entry would have:

Expense A/c Dr. (item account) -----XXX
Tax Expense A/c Dr. (either item acc or the tax expense acc ) ---YY
To, Supplier Account (Supplier account code combination) ----XXX
To, Tax Liability (Tax liability account code combination ) ----YY

How Do I Determine if I Should Self Assess?

Each taxing jurisdiction and authority defines their own criteria for self assessment.
If the regulations governing your business dictate that you self assess then EBusiness
Tax establishes the need to self assess a tax based upon the registration status of the Legal Establishment purchasing the goods or services and the Registration Status of the Supplier.
Conceptually the following is normally true: If goods purchased from a unregistered seller, to ensure that tax is not evaded on that transactions, it is buyers responsibility to calculate and remit the tax to relevant authority.

Self Assessment Tax  Pros and Cons:

Pros :-
  • Easy one time set up required.
  • Correctly driven by suppliers registration status.
  • Automatic tax calculation. No Manual intervention needed
  • Automatic accounting of tax liability.
  • Recommended approach if customer is using rules model. 
  • It is possible to check the “Self Assessed” flag on the fly in the tax details tab. 
  • Users can run “Account Analysis Report” to get details of  Self Assessed Tax.


Cons :-
  • Can be used only if customer intends to configure tax rules.
  • If a suppliers have multiple registrations spread across many jurisdictions then set up might become complicated.   

Release 12 replaces the concept of a Use tax with self assessed taxes for fresh regimes. If you are using an upgraded regime you can retain the old use tax solution until you desire to replace the old regime.

The 11i way:
1) When a tax code with tax type as use was associated in AP invoice workbench , then the details of that tax would get displayed in the USE TAX LIABILITY REPORT.
Tax distribution would not get created for this tax rate.
2) The total of that report would determine the tax liability and user would create 2 accounting entries One for accruing tax liability and other for settlement.


The R12 way :
1)Users configure SELF ASSESSED TAX which is the R12 equivalent of USE TAX.
2) The self assessed tax information can be viewed on the AP invoice workbench if user navigates to Tax Details
3) The accounting of self assessed tax will automatically happen in the TAX LIABILITY account defined at the TAX RATE level. User will still have to create accounting for settlement.
4) If a user wants to ascertain the use / self assessed tax liability then he just has to take the total of
liability side of the above mentioned account. Debit side will have details of tax recoverable ( If any). Details can be obtained if the user extracts the SLA account analysis report.


Use Tax:

11i Functionality:

  • When a tax code with tax type as “Use” was attached to a  AP invoice, then the details of that tax would not be visible on invoice but  displayed only in the “Use Tax Liability Report.”
  • Tax distribution (accounting) would not get created for this tax rate.
  • The total of Use Tax Liability Report would determine the use tax liability 
  • User would then have to create 2 accounting entries - One for accruing tax liability and other for settlement. 

R12 Functionality:

  • Only supported for upgraded customer. 
  • Post upgrade users can continue using Use Tax in the exact same manner as in 11i.
  • When a tax code with tax type as USE is associated in AP invoice at lines level, then the details of that use tax would Not be visible on invoice but  displayed only in the Use Tax Liability Report.  
  • Tax distribution would not get created for this tax rate.
  • The total of that report would determine the tax liability.
  • User would have to create 2 accounting entries - One for accruing tax liability and other for settlement. 
  • Use taxes will be migrated with tax type as “Use”

Use Tax Pros and Cons

Pros :-
Easy set up in 11i with seamless upgrade. 
Use Tax Liability report displays the details of the calculated use tax .
Customers using Use Tax in 11i, can use the same functionality post upgrade.

Cons :-
Supported only for backward compatibility. New Use tax creation not allowed. 
It is not driven by the supplier registration status. Tax will be calculated as Use tax only if that tax code is manually entered or defaulted on the transaction.  
No automatic accounting. Based on the Use Tax liability report, users have to pass two accounting entries – One for liability and one for payment. 
No details displayed on the Invoice. 


R12 EBS Query:

select
distinct
i.invoice_num,
i.invoice_type_lookup_code,
i.invoice_amount,
al.line_number,
al.description,
al.org_id,
al.amount,
ID.DISTRIBUTION_LINE_NUMBER,
ID.AMOUNT "Distribution Amount",
i.ACCTS_PAY_CODE_COMBINATION_ID  Dr_account_id,
gcc.CONCATENATED_SEGMENTS Dr_Account,
ID.DIST_CODE_COMBINATION_ID  Cr_account_id,
gcc1.CONCATENATED_SEGMENTS  Cr_Account,
i.self_assessed_tax_amount
from
apps.AP_INVOICES_all i,
apps.AP_INVOICE_LINES_all al,
apps.AP_INVOICE_DISTRIBUTIONS_ALL ID,
apps.GL_CODE_COMBINATIONS_KFV gcc,
apps.gl_code_combinations_kfv gcc1
where i.org_id=al.org_id
and i.invoice_id=al.invoice_id
AND ID.INVOICE_ID=al.invoice_id
and al.line_number=ID.INVOICE_LINE_NUMBER
and ID.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and i.ACCTS_PAY_CODE_COMBINATION_ID = gcc1.CODE_COMBINATION_ID
and i.invoice_num in ('62379')
order by ID.DISTRIBUTION_LINE_NUMBER;

Reference Support Note#948414.1


 

R12 GL Code Combination and Description and GL Queries

GL Code Combination and Description Query:

select
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5
 ACCOUNT,
A1.DESCRIPTION||'-'||A2.DESCRIPTION ||'-'|| A3.DESCRIPTION||'-'||A4.DESCRIPTION ||'-'|| A5.DESCRIPTION  DECSCRIPTION
from
fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
fnd_flex_values_vl A4,
fnd_flex_values_vl A5,
gl_code_combinations gcc
WHERE
a1.flex_value=gcc.segment1
And a1.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT1' )
And a2.flex_value=gcc.segment2
And a2.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT2' )
And a3.flex_value=gcc.segment3
And a3.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT3' )                        
And a4.flex_value=gcc.segment4
And a4.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT4' )
And a5.flex_value=gcc.segment5
And a5.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT5' );

-------Account Combination and Description --------

select gcc.CONCATENATED_SEGMENTS,
GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations_kfv gcc
WHERE gcc.CHART_OF_ACCOUNTS_ID=50576;


------Account Segment and Description----------------

SELECT gl_flexfields_pkg.get_description_sql
       (
        chart_of_accounts_id,--- chart of account id
        1,----- Position of segment
        segment1 ---- Segment value
        )
FROM gl_code_combinations;

------- GL Account Balances-------------------
select
glc.segment5,
ffv.description,
sum((begin_balance_dr) - (begin_balance_cr)) as opening_amount,
sum(period_net_dr_beq) as debit,
sum(period_net_cr_beq) as credit,
--sum(period_net_dr) as Net_Debit,
--sum(period_net_cr) as Net_Credit,
sum((begin_balance_dr) - (begin_balance_cr)) + sum(period_net_dr) - sum(period_net_cr) as closing_amount
from gl_balances gb, gl_code_combinations glc, APPS.FND_FLEX_VALUES_VL FFV
where gb.ledger_id = 2364 --ledger name for US
and gb.period_name = 'Oct-15'
and gb.code_combination_id = glc.code_combination_id
and glc.segment5 = ffv.flex_value
and gb.actual_flag = 'A'
and gb.currency_code = 'USD'
group by glc.segment5, ffv.description
order by segment5, sum((begin_balance_dr) - (begin_balance_cr)) + sum(period_net_dr) - sum(period_net_cr) desc;



----Query to fetch GL data and drill down to payables source data like invoice , po and payment details-----

SELECT 
distinct
gll.Name "LEDGER NAME",
gjh.je_source "JOURNAL SOURCE",
gjh.je_category "JOURNAL CATEGORY",
gjh.period_name "GL PERIOD",
gjh.Name "JOURNAL NAME",
gjh.CURRENCY_CODE,
gjl.description "JOURNAL DESCRIPTION",
gjh.default_effective_date "EFFECTIVE GL DATE",
DECODE (gjh.actual_flag, 'A', 'Actual','B', 'Budget','E', 
'Encumbrance') Balance_Type,
DECODE (gjl.status,  'P', 'Posted',  'U', 'Unposted',  gjl.status) Batch_Status,
gjh.posted_date,
gcc.segment1 COMPANY,
gcc.segment2 BUSINESS_UNIT,
gcc.segment3 PROFIT_CENTER,
gcc.segment4 DEPARTMENT,
gcc.segment5 ACCOUNT,
gcc.segment6 INTERCOMPANY,
gcc.segment7 FUTURE_1,
gcc.segment8 FUTURE_2,
nvl(gjh.running_total_accounted_dr,0) JE_TOTAL_DR,
nvl(gjh.running_total_accounted_cr,0) JE_TOTAL_CR, 
gjh.JE_HEADER_ID,
gjl.JE_LINE_NUM,
nvl(gjl.ENTERED_DR,0) "JOURNAL DEBIT AMOUNT",
nvl(gjl.ENTERED_CR,0) "JOURNAL CREDIT AMOUNT",
XAL.accounting_class_code "ACCOUNTING CLASS",
nvl(XAL.ACCOUNTED_DR,0) "ACCOUNTED DEBIT",
nvl(XAL.ACCOUNTED_CR,0) "ACCOUNTED CREDIT",
xte.transaction_number AP_Invoice_Number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') Invoice_date,
poh.SEGMENT1 PO_NUMBER,
apl.line_number,
apl.Amount Invoice_Amount,
apl.unit_price,
apl.quantity_invoiced,
apl.line_type_lookup_code,
aca.check_number,
aip.amount Payment_Amount,
nvl(aip.DISCOUNT_TAKEN,0)  Payment_Discount_Taken,
aca.check_date,
aca.Amount Total_Check_Amount,
aca.payment_method_code,
apl.description,
xte.ENTITY_CODE,
pv.vendor_name Vendor_name,
pv.segment1 Vendor_number,
gjh.default_effective_date gl_date
FROM 
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
apps.gl_ledgers gll,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
gl_code_combinations gcc,
apps.ap_suppliers pv,
gl_period_statuses ps,
apps.AP_INVOICE_LINES_ALL apl,
apps.PO_HEADERS_ALL poh,
apps.AP_CHECKS_ALL aca,
apps.AP_INVOICE_PAYMENTS_ALL aip
WHERE     
gll.ledger_id in (2364) ------(2403, 2383,2364)
AND gll.ledger_id=gjh.ledger_id
AND gjh.je_source = 'Payables'
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_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 xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = pv.vendor_id(+)
AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND ps.application_id = 101
AND xte.SOURCE_ID_INT_1=apl.INVOICE_ID
AND apl.INVOICE_ID=aip.INVOICE_ID
AND gjl.period_name = ps.period_name
AND apl.po_header_id=poh.po_header_id
AND aca.CHECK_ID = aip.CHECK_ID
AND gjh.je_header_id=3129318
AND ps.ledger_id = gjh.LEDGER_ID
AND ps.period_Name=:P_Period_Name
AND gcc.segment5=:P_AP_Account
order by gjh.JE_HEADER_ID,gjl.JE_LINE_NUM, INVOICE_DATE, PO_NUMBER, LINE_NUMBER;




GL Real Time Query with subledger Data.

GL Real Time SQL Query with Subledger Data (Drill Down)
=============================================
GL Journal Details with Project and Task  details:

SELECT
distinct
gll.Name "LEDGER NAME",
gjh.je_source "JOURNAL SOURCE",
gjh.je_category "JOURNAL CATEGORY",
gjh.period_name "GL PERIOD",
gjh.Name "JOURNAL NAME",
gjh.CURRENCY_CODE,
gjl.description "JOURNAL DESCRIPTION",
gjh.default_effective_date "EFFECTIVE GL DATE",
DECODE (gjh.actual_flag, 'A', 'Actual','B', 'Budget','E',
'Encumbrance') Balance_Type,
DECODE (gjl.status,  'P', 'Posted',  'U', 'Unposted',  gjl.status) Batch_Status,
gjh.posted_date,
gcc.segment1 COMPANY,
gcc.segment2 BUSINESS_UNIT,
gcc.segment3 PROFIT_CENTER,
gcc.segment4 DEPARTMENT,
gcc.segment5 ACCOUNT,
gcc.segment6 INTERCOMPANY,
gcc.segment7 FUTURE_1,
gcc.segment8 FUTURE_2,
nvl(gjh.running_total_accounted_dr,0) JE_TOTAL_DR,
nvl(gjh.running_total_accounted_cr,0) JE_TOTAL_CR,
gjh.JE_HEADER_ID,
gjl.JE_LINE_NUM,
nvl(gjl.ENTERED_DR,0) "JOURNAL DEBIT AMOUNT",
nvl(gjl.ENTERED_CR,0) "JOURNAL CREDIT AMOUNT",
XAL.accounting_class_code "ACCOUNTING CLASS",
nvl(XAL.ACCOUNTED_DR,0) "ACCOUNTED DEBIT",
nvl(XAL.ACCOUNTED_CR,0) "ACCOUNTED CREDIT",
xte.transaction_number Transaction_Number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') Trx_date,
pa.Segment1 "Project Number",
pa.name "Project Name",
pa.project_type "Project Type",
pt.TASK_NUMBER
FROM
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
apps.gl_ledgers gll,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
gl_code_combinations gcc,
apps.ap_suppliers pv,
gl_period_statuses ps,
PA_EXPENDITURE_ITEMS_ALL pei,
apps.pa_projects_all pa,
apps.pa_tasks pt
WHERE  
gll.ledger_id in (2364)
and pei.project_id=pa.PROJECT_ID
and pei.task_id=pt.task_id
and pa.org_id=pei.org_id
AND gll.ledger_id=gjh.ledger_id
AND gjh.je_source in ('Project Accounting')
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_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 xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND XTE.SOURCE_ID_INT_1 = pei.EXPENDITURE_ITEM_ID    ----- this is the link between XLE and Projects------
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = pv.vendor_id(+)
AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND ps.application_id = 101
AND gjl.period_name = ps.period_name
AND ps.ledger_id = gjh.LEDGER_ID
AND ps.period_Name='Dec-15'
--and pei.expenditure_item_id=1486026
and gjh.JE_HEADER_ID=3144609;   ----3147198;
--order by gjh.JE_HEADER_ID,gjl.JE_LINE_NUM;

--- GL Journal for Source Project Accounting----

SELECT DISTINCT gll.ledger_id, gljeh.period_name "Fiscal Period",
                gll.NAME "Company Name", gljeh.NAME "Journal Name ",
                gljeh.je_source "Accounting Document Source",
                gljeb.NAME "Journal batch name", gljel.accounted_dr,
                gljel.accounted_cr, glcc.segment1, glcc.segment2,
                glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6,
                glcc.segment7, glcc.segment8
           FROM gl_je_headers gljeh,
                gl_je_batches gljeb,
                gl_je_lines gljel,
                gl_code_combinations glcc,
                gl_ledgers gll
          WHERE gll.ledger_id = gljeh.ledger_id
            AND gljeh.je_batch_id = gljeb.je_batch_id
            AND gljeh.je_header_id = gljel.je_header_id
            AND gljel.code_combination_id = glcc.code_combination_id
            AND gll.ledger_id = gljeh.ledger_id
            AND gll.ledger_id = 2364
            AND gljeh.period_name  IN 'Jan-16'
            AND gljeh.je_source = 'Project Accounting'
       ORDER BY gljeh.period_name, gljeh.NAME DESC;

--- GL Drill down to Projects including Accounting------

select
gcc.SEGMENT5 Account,
gcc.segment1 Company,
SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0) +
(NVL(gb.period_net_Dr,0) - NVL(gb.period_net_cr,0))) Jan2015_bal,
pa.segment1 "Project Number",
pa.name "Project Name",
pa.description "Project description",
pa.project_type,
pt.TASK_NUMBER,
pt.task_name
from
apps.pa_projects_all pa,
apps.pa_tasks pt,
apps.pa_cost_distribution_lines_all cdl,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcc1,
gl_balances gb
where
pa.org_id in (1792)
and pt.project_id=pa.project_id
and cdl.project_id=pa.project_id
and cdl.task_id=pt.TASK_ID
and cdl.DR_CODE_COMBINATION_ID=gcc.code_combination_id
and cdl.DR_CODE_COMBINATION_ID=gcc1.code_combination_id
and gb.code_combination_id = gcc.code_combination_id
and gcc.segment5 in ('150990','151990','190190','190191','190192')
and gcc.segment1 in (1031)
AND gb.ledger_id             = 2364 -- Enter the Ledger
AND gb.Actual_flag           = 'A'
AND gb.period_name           = 'Jan-16' --Enter the Period
GROUP BY
gcc.segment5,
gcc.segment1,
pa.segment1 ,
pa.name ,
pa.description ,
pa.project_type,
pt.task_number,
pt.task_name,
cdl.DR_CODE_COMBINATION_ID
;

-----Project Batch Expenses Query ----------------

select distinct
peg.Org_id,
peg.expenditure_group "Expenditure Batch",
peg.DESCRIPTION,
peg.expenditure_group_status_code,
fu.USER_NAME "Created By",
peg.CREATION_DATE,
hr.name "Operating Unit",
he.FULL_NAME "Employee Name",
he.EMPLOYEE_NUM "Employee Number",
pea.INCURRED_BY_ORGANIZATION_ID,
hr1.name "Organization",
peg.CONTROL_TOTAL_AMOUNT,
peg.expenditure_ending_date,
pei.EXPENDITURE_ITEM_DATE,
pei.EXPENDITURE_TYPE,
pei.UNIT_OF_MEASURE,
peg.EXPENDITURE_ENDING_DATE,
pei.QUANTITY,
pec.EXPENDITURE_COMMENT,
pa.Segment1 "Project Number",
pa.name "Project Name",
pa.project_type "Project Type",
pt.TASK_NUMBER,
pa.START_DATE "Project Start date",
pa.COMPLETION_DATE "Project End date",
pei.RAW_COST,
pei.BURDEN_COST,
pei.JOB_ID,
pea.expenditure_status_code,
pea.EXPENDITURE_CLASS_CODE,
pei.UNIT_OF_MEASURE,
peg.transaction_source,
pea.ENTERED_BY_PERSON_ID,
pea.VENDOR_ID,
pea.USER_BATCH_NAME,
pei.EXPENDITURE_ITEM_ID,
pei.EXPENDITURE_ID,
cdl.gl_date,
cdl.pa_date,
cdl.AMOUNT,
cdl.line_num,
gcc.concatenated_segments  Dr_Account,
gcc1.CONCATENATED_SEGMENTS  Cr_Account
from
apps.PA_EXPENDITURE_GROUPS_ALL peg,
apps.PA_EXPENDITURES_ALL pea ,
apps.PA_EXPENDITURE_ITEMS_all pei,
apps.PA_EXPENDITURE_COMMENTS pec,
apps.fnd_user fu,
apps.hr_all_organization_units hr,
apps.hr_all_organization_units hr1,
apps.hr_employees he,
apps.pa_projects_all pa,
apps.pa_tasks pt,
apps.pa_cost_distribution_lines_all cdl,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcc1
where
peg.org_id=pea.ORG_ID
and pei.org_id=pea.ORG_ID
and cdl.org_id=pea.org_id
and pea.expenditure_group=peg.expenditure_group
and peg.EXPENDITURE_ENDING_DATE=pei.EXPENDITURE_ITEM_DATE
and pei.expenditure_item_id=pec.EXPENDITURE_ITEM_ID(+)
and pea.EXPENDITURE_ID=pei.EXPENDITURE_ID
and cdl.EXPENDITURE_ITEM_ID=pei.EXPENDITURE_ITEM_ID
and fu.user_id = peg.CREATED_BY
and hr.ORGANIZATION_ID=pei.org_id
and fu.employee_id=he.EMPLOYEE_ID
and pea.INCURRED_BY_ORGANIZATION_ID =hr1.ORGANIZATION_ID
and pei.project_id=pa.PROJECT_ID
and pei.task_id=pt.task_id
and pa.org_id=pei.org_id
and cdl.DR_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and cdl.CR_code_combination_id=gcc1.code_combination_id
and peg.SYSTEM_LINKAGE_FUNCTION=pei.SYSTEM_LINKAGE_FUNCTION
--and peg.org_id in (1792)
--and pea.expenditure_id=978814
and pei.EXPENDITURE_ITEM_ID='1530784'
--and peg.creation_date like  '%12-JAN-16%'
--and peg.expenditure_group like '%RAE Dec 15 Projects Accrual%'
order by peg.CREATION_DATE,pei.EXPENDITURE_ITEM_DATE;