General Ledger - Sub ledger - Project Accounting Drill Down
Well This Is Definitely one of the Complicated One!
Not very Often though but you have a Project which involves Project accounting, and you are working on Reports / or Customization which need an integration form Oracle Projects, General Ledger and Sub-ledger Accounting, you need a very good understanding of how various Database tables of these Different Modules Link with each other.
B Below is a query which can be leveraged to Drill Down or Drill up for the data between
GENERAL LEDGER
SUB-LEDGER ACCOUNTING
PROJECT ACCOUNTING
select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,PA.PROJECT_ID,PA.SEGMENT1,PA.NAME,PA.PROJECT_TYPE,PT.TASK_ID,PT.TASK_NUMBER,PT.TASK_NAME,PCD.EXPENDITURE_ITEM_ID,
PE.EXPENDITURE_ITEM_DATE ,
PE.TRANSACTION_SOURCE,
PE.EXPENDITURE_ITEM_DATE ,PE.EXPENDITURE_ITEM_DATE
,888
,PCD.*
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.PA_COST_DISTRIBUTION_LINES_ALL PCD,
APPS.PA_PROJECTS_ALL PA,
APPS.PA_TASKS PT,
APPS.PA_EXPENDITURE_ITEMS_ALL PE,
APPS.PA_TRANSACTION_SOURCES PTS
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
AND PCD.EXPENDITURE_ITEM_ID = PE.EXPENDITURE_ITEM_ID
AND PE.TRANSACTION_SOURCE = PTS.TRANSACTION_SOURCE
--AND PE.TRANSACTION_SOURCE LIKE 'PA%'
AND PCD.PROJECT_ID = PA.PROJECT_ID
AND PCD.TASK_ID = PT.TASK_ID
AND PA.PROJECT_ID = PT.PROJECT_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')
Well This Is Definitely one of the Complicated One!
Not very Often though but you have a Project which involves Project accounting, and you are working on Reports / or Customization which need an integration form Oracle Projects, General Ledger and Sub-ledger Accounting, you need a very good understanding of how various Database tables of these Different Modules Link with each other.
B Below is a query which can be leveraged to Drill Down or Drill up for the data between
GENERAL LEDGER
SUB-LEDGER ACCOUNTING
PROJECT ACCOUNTING
select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,PA.PROJECT_ID,PA.SEGMENT1,PA.NAME,PA.PROJECT_TYPE,PT.TASK_ID,PT.TASK_NUMBER,PT.TASK_NAME,PCD.EXPENDITURE_ITEM_ID,
PE.EXPENDITURE_ITEM_DATE ,
PE.TRANSACTION_SOURCE,
PE.EXPENDITURE_ITEM_DATE ,PE.EXPENDITURE_ITEM_DATE
,888
,PCD.*
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.PA_COST_DISTRIBUTION_LINES_ALL PCD,
APPS.PA_PROJECTS_ALL PA,
APPS.PA_TASKS PT,
APPS.PA_EXPENDITURE_ITEMS_ALL PE,
APPS.PA_TRANSACTION_SOURCES PTS
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
AND PCD.EXPENDITURE_ITEM_ID = PE.EXPENDITURE_ITEM_ID
AND PE.TRANSACTION_SOURCE = PTS.TRANSACTION_SOURCE
--AND PE.TRANSACTION_SOURCE LIKE 'PA%'
AND PCD.PROJECT_ID = PA.PROJECT_ID
AND PCD.TASK_ID = PT.TASK_ID
AND PA.PROJECT_ID = PT.PROJECT_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')
