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

Anupam Chaudhari

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

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