select
-- task summary :
b.TASK_NUMBER,
t.TASK_TITLE,
b.INITIATED_BY,
b.INITIATED_DATE,
b.COMPLETED_BY,
b.COMPLETED_DATE,
b.STATUS_CODE,
b.OUTCOME_CODE,
hb.version,
hb.from_user
from FND_BPM_TASK_B b, FND_BPM_TASK_TL t,
FND_BPM_TASK_HISTORY_B hb
where b.domain = 'ICDomain'
--and b.task_number = 200381
and b.task_definition_name = 'DisputeNotificationTask'
and b.task_id = t.task_id
and t.language = USERENV('LANG')
and t.task_id=hb.task_id
order by b.task_number,hb.version desc
SELECT TO_CHAR(PPA.PROJECT_ID) PROJECT_ID
,TO_CHAR(PPA.SEGMENT1) PROJECT_NUMBER
,PPA.NAME PROJECT_NAME
,PPA.DESCRIPTION
,PPA.START_DATE
,PPA.COMPLETION_DATE
,PPA.CLOSED_DATE
,PTV.TASK_NUMBER
,TO_CHAR(PTV.TASK_ID) TASK_ID
,TO_CHAR(PPA.CARRYING_OUT_ORGANIZATION_ID) ORGANIZATION_ID
,HRU.NAME ORGANIZATION_NAME
,PPT.PROJECT_TYPE PROJECT_TYPE
,PPA.GROUP_SPACE_TEMPLATE_NAME
,PPA.PM_PROJECT_REFERENCE
,XLE.NAME "LEGAL ENTITY"
,PPA.ACTUAL_START_DATE "PROJECT START DATE"
,PPA.ACTUAL_FINISH_DATE "PROJECT FINISH DATE"
,PPA.PROJECT_STATUS_CODE
,PTV.PM_TASK_REFERENCE "SOURCE TASK REFERENCE"
,PTV.DESCRIPTION "TASK DESCRIPTION"
,PTV1.TASK_NUMBER "PARENT TASK NUMBER"
,PTC.TXN_CTRL_REFERENCE
,PTC.CHARGEABLE_FLAG
,PTC.BILLABLE_FLAG
,PTC.CAPITALIZABLE_FLAG
,PET.EXPENDITURE_TYPE_NAME
,PEC.EXPENDITURE_CATEGORY_NAME
,PCT.CLASS_CODE
,PCC.CLASS_CATEGORY
FROM
PJF_PROJECTS_ALL_VL PPA
,PJF_TASKS_V PTV
,PJF_TASKS_V PTV1
,HR_ORGANIZATION_UNITS HRU
,PJF_PROJECT_TYPES_TL PPT
,XLE_ENTITY_PROFILES XLE
,PJC_TRANSACTION_CONTROLS PTC
,PJF_EXP_TYPES_TL PET
,PJF_EXP_CATEGORIES_TL PEC
,PJF_CLASS_CATEGORIES_TL PCC
,PJF_CLASS_CODES_TL PCT
,PJF_PROJECT_CLASSES PPC
WHERE 1=1
AND PPA.PROJECT_ID=PTV.PROJECT_ID
AND PTV1.PARENT_TASK_ID(+) = PTV.TASK_ID
AND PPA.CARRYING_OUT_ORGANIZATION_ID=HRU.ORGANIZATION_ID(+)
AND PPT.PROJECT_TYPE_ID=PPA.PROJECT_TYPE_ID
AND PPA.SEGMENT1='158901-230'
AND XLE.LEGAL_ENTITY_ID = PPA.LEGAL_ENTITY_ID
AND PPA.PROJECT_ID=PTC.PROJECT_ID(+)
AND PTC.TASK_ID(+) = PTV.TASK_ID
AND PET.EXPENDITURE_TYPE_ID = PTC.EXPENDITURE_TYPE_ID(+)
AND PET.LANGUAGE ='US'
AND NVL(PEC.LANGUAGE,'US') ='US'
AND PTC.EXPENDITURE_CATEGORY_ID = PEC.EXPENDITURE_CATEGORY_ID(+)
AND PPC.CLASS_CATEGORY_ID = PCC.CLASS_CATEGORY_ID(+)
AND PPC.PROJECT_ID(+) = PPA.PROJECT_ID
AND PPC.CLASS_CODE_ID = PCT.CLASS_CODE_ID(+)
ORDER BY TO_CHAR(PPA.PROJECT_ID)
