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;
=============================================
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;
