how to fetch project cost distribution which not accounted & post in FA module against asset.

Question

Hi Friends,

I have requirement to get all project cost distribution line record which in not accounted in fixed asset module against the project asset.

In other word, I want to fetch the Project costing record of project asset along with fixed asset transaction line but i dont want record which is accounted and posted in FA module.

Query I developed but it is failed with time out error, Individual code part of main query and not in query execute within 3-5 second, but in combine it failed.

SELECT DISTINCT

pclp.class_code

,pro.SEGMENT1

,prot.NAME,(select project_status_name from pjf_project_Statuses_tl where project_Status_code=pro. PROJECT_STATUS_CODE) project_status

,cdl.PROJFUNC_RAW_COST amount,tas.TASK_NUMBER

,tas.TASK_NAME

,tas.BILLABLE_FLAG

, trunc(XAL.ACCOUNTING_DATE) accounting_date

, trunc(GJH.POSTED_DATE) posting_date

,PEI.EXPENDITURE_ITEM_ID

,gcc.Segment1 BU,gcc.Segment2 ACCOUNT

,gcc.Segment3 COST_CENTER,gcc.Segment4 PRODUCT

,gcc.Segment5 PROJECT,gcc.Segment6 IC

,gcc.Segment7 SYSTEM

,gcc.Segment8 RESERVE

,decode(gcc.ACCOUNT_TYPE,'A','Asset','R','Revenue','E','Expense','L','Liability','O','Owners equity') ACCOUNT_TYPE

,pro.ORG_ID ORG_ID

,tas.attribute2 FA_FUNCTION

,to_char((select ATTRIBUTE1_DATE from PJF_PROJ_ELEMENTS_B TASK

where TASK.PROJ_ELEMENT_ID =tas.task_id

AND TASK.Project_id=pro.project_id

and TASK.OBJECT_TYPE = 'PJF_TASKS' ),'dd.mm.yyyy') TECHNICAL_COMPLETION_DATE

,(SELECT PAPF.FULL_NAME

FROM per_person_names_f papf,

PJF_LATESTPROJECTMANAGER_V PPP,

PJF_PROJECTS_ALL_B PA,

PJF_PROJ_ROLE_TYPES_TL ppt

WHERE 1=1

AND papf.person_id = ppp.RESOURCE_SOURCE_ID

AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date) and TRUNC (papf.effective_end_date)

AND PA.PROJECT_ID = ppp.Project_Id

AND papf.NAME_TYPE='GLOBAL'

and ppt.project_role_id=ppp.project_role_id

AND upper(ppt.PROJECT_ROLE_NAME) = upper('PROJECT MANAGER')

AND PPP.PROJECT_ID = pro.PROJECT_ID ) PEOJECT_MANAGER

-- ,PAL.TRANSFER_STATUS_CODE

,CDL.LINE_NUM

-- ,PAL.PROJECT_ASSET_LINE_ID

,TAS.PROJECT_ID

,TAS.TASK_ID

-- ,PALD.PROJECT_ASSET_LINE_DETAIL_ID

,PPA.FA_ASSET_ID

-- ,FAi.INVOICE_TRANSACTION_ID_IN

-- ,fai.asset_invoice_id

-- ,ADJ.SOURCE_LINE_ID

-- ,ADJ.ADJUSTMENT_LINE_ID

FROMPJC_COST_DIST_LINES_ALL CDL

,PJC_EXP_ITEMS_ALL PEI

,PJF_TASKS_V tas

,PJF_PROJECTS_ALL_B pro

,PJF_PROJECTS_ALL_TL prot

,PJF_PROJECT_TYPES_TL PPT

,HR_ALL_ORGANIZATION_UNITS p_org

,PJF_CLASS_CODES_TL pclp

,PJF_CLASS_CATEGORIES_TL pcct

,PJF_PROJECT_CLASSES ppc

,PJC_PRJ_ASSET_LN_DETS PALD

,PJC_PRJ_ASSET_LNS_ALL PAL

,PJC_PRJ_ASSETS_ALL PPA

,FA_ADJUSTMENTS fadj

,GL_CODE_COMBINATIONS gcc,xla_distribution_links xda

,xla_ae_lines xal

,gl_import_references GIR

,gl_je_headers gjh

WHERE 1=1

AND PPT.PROJECT_TYPE in ('CAPEX','DISCOUNT')

AND PEI.CAPITALIZABLE_FLAG='Y'

AND tas.PROJECT_ID = pro.PROJECT_ID

AND PEI.PROJECT_ID = tas.PROJECT_ID

AND PEI.TASK_ID = tas.TASK_ID

AND PPT.PROJECT_TYPE_ID = pro.PROJECT_TYPE_ID

AND PEI.PROJECT_ID = CDL.PROJECT_ID

AND PEI.TASK_ID = CDL.TASK_ID

AND PEI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID

AND CDL.EXPENDITURE_ITEM_ID = PALD.EXPENDITURE_ITEM_ID (+)

AND CDL.LINE_NUM = PALD.LINE_NUM (+)

AND PALD.PROJECT_ASSET_LINE_DETAIL_ID = PAL.PROJECT_ASSET_LINE_DETAIL_ID (+)

AND TAS.PROJECT_ID = PAL.PROJECT_ID (+)

AND TAS.TASK_ID = PAL.TASK_ID (+)

AND PAL.PROJECT_ASSET_ID = PPA.PROJECT_ASSET_ID (+)

AND TAS.PROJECT_ID = PPA.PROJECT_ID (+)

and ppa.fa_asset_id = fadj.asset_id (+)

AND fadj.ADJUSTMENT_TYPE (+) = 'COST CLEARING'

-- and tas.task_id = fai.task_id (+)

-- and tas.project_id = fai.project_id (+)

AND p_org.ORGANIZATION_ID(+) = pro.CARRYING_OUT_ORGANIZATION_ID

AND pro.project_id=prot.PROJECT_ID

AND pclp.class_code_id (+)=ppc.class_code_id

AND ppc.class_category_id (+) =pcct.class_category_id

AND ppc.project_id (+) =pro.project_id

AND pcct.class_category (+) = 'Program Area'

AND XDA.APPLICATION_ID = 10036

AND XDA.SOURCE_DISTRIBUTION_TYPE = 'R'

AND XDA.EVENT_CLASS_CODE IN ('MISC_COST','MISC_COST_ADJ')

AND cdl.expenditure_item_id = xda.source_distribution_id_num_1(+)

And cdl.acct_event_id = xda.event_id(+)

AND cdl.line_num = xda.source_distribution_id_num_2(+)

AND ( xda.rounding_class_code NOT LIKE '%CLEARING'

OR xda.rounding_class_code IS NULL)

AND XAL.APPLICATION_ID = 10036

AND xda.ae_header_id = xal.ae_header_id(+)

AND xda.ae_line_num = xal.ae_line_num(+)

and gcc.code_combination_id =NVL (NVL (cdl.raw_cost_dr_ccid, cdl.raw_cost_cr_ccid), xal.code_combination_id)

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID (+)

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE (+)

AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID

AND NVL(fadj.ADJUSTMENT_LINE_ID,0) NOT IN (SELECT distinct ADJ.ADJUSTMENT_LINE_ID

FROM fa_transaction_headers fth

,FA_ADJUSTMENTS adj

,xla_distribution_links xdl

,xla_ae_headers xah

WHERE 1=1

AND FTH.ASSET_ID = fadj.ASSET_ID --914001 --336001

AND FTH.ASSET_ID = adj.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID

AND ADJ.SOURCE_TYPE_CODE IN ('ADDITION','ADJUSTMENT')

and xdl.APPLICATION_ID in (10036 )

and xdl.EVENT_TYPE_CODE in ('MISC_COST_DIST','MISC_COST_DIST_ADJ')

AND FTH.TRANSACTION_HEADER_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND ADJ.ADJUSTMENT_LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_2

and xdl.event_id = xah.event_id

and xdl.ae_header_id = xah.ae_header_id

and xah.gl_transfer_status_code = 'Y'

and ADJ.SOURCE_LINE_ID is not null

-- AND pro.segment1 IN ( 'SYPUC2','SYPUC1')

union all

SELECT distinct ADJ.ADJUSTMENT_LINE_ID

FROM fa_transaction_headers fth

,FA_ADJUSTMENTS adj

,xla_distribution_links xdl

,xla_ae_headers xah

WHERE 1=1

AND FTH.ASSET_ID = fadj.ASSET_ID --914001 --336001

AND FTH.ASSET_ID = adj.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID

AND ADJ.SOURCE_TYPE_CODE IN ('ADDITION','ADJUSTMENT')

and xdl.APPLICATION_ID in (140 )

and xdl.SOURCE_DISTRIBUTION_TYPE in ('TRX')

AND FTH.TRANSACTION_HEADER_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND ADJ.ADJUSTMENT_LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_2

and xdl.event_id = xah.event_id

and xdl.ae_header_id = xah.ae_header_id

and xah.gl_transfer_status_code = 'Y'

and ADJ.SOURCE_LINE_ID is not null

)

AND pro.segment1 IN ( 'SYPUC2','SYPUC1')

and (trunc(GJH.POSTED_DATE) <= trunc(SYSDATE) OR trunc(GJH.POSTED_DATE) is null)

ORDER BY pro.SEGMENT1, tas.TASK_NUMBER

Thanks in advance…

JohnW-Oracle · Accepted Answer

Hi @Anupam Chaudhari,

The 'accounted' requirement is the same for many functional areas and it will not be extracted from the source until it is accounted. You could use OTBI to queries on the source side or create a customization for the same on the FDI side.

Regards,

John