Product: | PA - Projects |
---|---|
Description: | |
Implementation/DBA Data: | APPS.PA_FP_OF_WEBADI_V |
SELECT PPDM.AMOUNT_SUBTYPE_ID
, PMVL.AMOUNT_TYPE_NAME
, OFE.ORG_ID
, HRORG1.NAME
, OFE.ORGANIZATION_ID
, HRORG2.NAME
, OFE.OTHER_ORG_ID
, HRORG3.NAME
, OFE.OTHER_ORGANIZATION_ID
, HRORG4.NAME
, PP.NAME
, DECODE(OFE.RESOURCE_ID
, -1
, PPA.ASSIGNMENT_NAME
, PR.NAME)
, PPDM.PERIOD_AMOUNT1
, PPDM.PERIOD_AMOUNT2
, PPDM.PERIOD_AMOUNT3
, PPDM.PERIOD_AMOUNT4
, PPDM.PERIOD_AMOUNT5
, PPDM.PERIOD_AMOUNT6
, PPDM.PERIOD_AMOUNT7
, PPDM.PERIOD_AMOUNT8
, PPDM.PERIOD_AMOUNT9
, PPDM.PERIOD_AMOUNT10
, PPDM.PERIOD_AMOUNT11
, PPDM.PERIOD_AMOUNT12
, PPDM.PERIOD_AMOUNT13
, PPDM.PERIOD_AMOUNT14
, PPDM.PERIOD_AMOUNT15
, PPDM.PERIOD_AMOUNT16
, PPDM.PERIOD_AMOUNT17
, PPDM.PERIOD_AMOUNT18
, PPDM.PERIOD_AMOUNT19
, PPDM.PERIOD_AMOUNT20
, PPDM.PERIOD_AMOUNT21
, PPDM.PERIOD_AMOUNT22
, PPDM.PERIOD_AMOUNT23
, PPDM.PERIOD_AMOUNT24
, PPDM.PERIOD_AMOUNT25
, PPDM.PERIOD_AMOUNT26
, PPDM.PERIOD_AMOUNT27
, PPDM.PERIOD_AMOUNT28
, PPDM.PERIOD_AMOUNT29
, PPDM.PERIOD_AMOUNT30
, PPDM.PERIOD_AMOUNT31
, PPDM.PERIOD_AMOUNT32
, PPDM.PERIOD_AMOUNT33
, PPDM.PERIOD_AMOUNT34
, PPDM.PERIOD_AMOUNT35
, PPDM.PERIOD_AMOUNT36
, PPDM.PERIOD_AMOUNT37
, PPDM.PERIOD_AMOUNT38
, PPDM.PERIOD_AMOUNT39
, PPDM.PERIOD_AMOUNT40
, PPDM.PERIOD_AMOUNT41
, PPDM.PERIOD_AMOUNT42
, PPDM.PERIOD_AMOUNT43
, PPDM.PERIOD_AMOUNT44
, PPDM.PERIOD_AMOUNT45
, PPDM.PERIOD_AMOUNT46
, PPDM.PERIOD_AMOUNT47
, PPDM.PERIOD_AMOUNT48
, PPDM.PERIOD_AMOUNT49
, PPDM.PERIOD_AMOUNT50
, PPDM.PERIOD_AMOUNT51
, PPDM.PERIOD_AMOUNT52
FROM PA_PROJ_PERIODS_DENORM PPDM
, HR_ALL_ORGANIZATION_UNITS HRORG1
, HR_ALL_ORGANIZATION_UNITS HRORG2
, HR_ALL_ORGANIZATION_UNITS HRORG3
, HR_ALL_ORGANIZATION_UNITS HRORG4
, PA_ORG_FCST_ELEMENTS OFE
, PA_PROJECTS_ALL PP
, PA_WORK_TYPES_B PWTB
, PA_PROJECT_ASSIGNMENTS PPA
, PA_RESOURCES PR
, PA_AMOUNT_TYPES_VL PMVL
WHERE OFE.BUDGET_VERSION_ID= PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND OFE.PROVIDER_RECEIVER_CODE= DECODE(PPDM.AMOUNT_SUBTYPE_CODE
, 'OWN_REVENUE'
, 'P'
, 'BORROWED_REVENUE'
, 'R'
, 'TP_REVENUE_IN'
, 'P'
, 'TP_REVENUE_OUT'
, 'R'
, 'OWN_PROJECT_COST'
, 'P'
, 'TP_COST_IN'
, 'R'
, 'TP_COST_OUT'
, 'P'
, 'LENT_RESOURCE_COST'
, 'P'
, 'UNASSIGNED_TIME_COST'
, 'P'
, OFE.PROVIDER_RECEIVER_CODE)
AND OFE.ORGANIZATION_ID= DECODE(PPDM.AMOUNT_SUBTYPE_CODE
, 'OWN_REVENUE'
, OFE.OTHER_ORGANIZATION_ID
, 'OWN_PROJECT_COST'
, OFE.OTHER_ORGANIZATION_ID
, OFE.ORGANIZATION_ID)
AND NVL(PWTB.UNASSIGNED_FLAG
, 'N')= DECODE(PPDM.AMOUNT_SUBTYPE_CODE
, 'UNASSIGNED_TIME_COST'
, 'Y'
, NVL(PWTB.UNASSIGNED_FLAG
, 'N'))
AND NVL(PWTB.UNASSIGNED_FLAG
, 'N')<> DECODE(PPDM.AMOUNT_SUBTYPE_CODE
, 'UNASSIGNED_TIME_COST'
, 'N'
, 'Y')
AND OFE.ORGANIZATION_ID<> DECODE(PPDM.AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_IN'
, OFE.OTHER_ORGANIZATION_ID
, 'TP_COST_OUT'
, OFE.OTHER_ORGANIZATION_ID
, 'LENT_RESOURCE_COST'
, OFE.OTHER_ORGANIZATION_ID
, -99)
AND PPDM.AMOUNT_TYPE_CODE= PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
AND PMVL.AMOUNT_TYPE_ID = PPDM.AMOUNT_SUBTYPE_ID
AND PP.PROJECT_ID= OFE.TXN_PROJECT_ID
AND PP.WORK_TYPE_ID = PWTB.WORK_TYPE_ID(+)
AND NVL(HRORG1.ORGANIZATION_ID
, -99) = NVL(OFE.ORG_ID
, -99)
AND NVL(HRORG2.ORGANIZATION_ID
, -99) = NVL(OFE.ORGANIZATION_ID
, -99)
AND NVL(HRORG3.ORGANIZATION_ID
, -99) = NVL(OFE.OTHER_ORG_ID
, -99)
AND NVL(HRORG4.ORGANIZATION_ID
, -99) = NVL(OFE.OTHER_ORGANIZATION_ID
, -99)
AND PPA.ASSIGNMENT_ID(+)=OFE.ASSIGNMENT_ID
AND PR.RESOURCE_ID(+)=OFE.RESOURCE_ID
AND PPDM.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PPDM.OBJECT_ID = OFE.FORECAST_ELEMENT_ID
AND PPDM.OBJECT_TYPE_CODE = 'FCST_ELEMENTS'
AND PPDM.CURRENCY_TYPE = 'PROJ_FUNCTIONAL' UNION SELECT PPDM.AMOUNT_SUBTYPE_ID
, PMVL.AMOUNT_TYPE_NAME
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, PP.ORG_ID
, HRORG1.NAME
, PP.CARRYING_OUT_ORGANIZATION_ID
, HRORG.NAME
, NULL
, NULL
, PPDM.PERIOD_AMOUNT1
, PPDM.PERIOD_AMOUNT2
, PPDM.PERIOD_AMOUNT3
, PPDM.PERIOD_AMOUNT4
, PPDM.PERIOD_AMOUNT5
, PPDM.PERIOD_AMOUNT6
, PPDM.PERIOD_AMOUNT7
, PPDM.PERIOD_AMOUNT8
, PPDM.PERIOD_AMOUNT9
, PPDM.PERIOD_AMOUNT10
, PPDM.PERIOD_AMOUNT11
, PPDM.PERIOD_AMOUNT12
, PPDM.PERIOD_AMOUNT13
, PPDM.PERIOD_AMOUNT14
, PPDM.PERIOD_AMOUNT15
, PPDM.PERIOD_AMOUNT16
, PPDM.PERIOD_AMOUNT17
, PPDM.PERIOD_AMOUNT18
, PPDM.PERIOD_AMOUNT19
, PPDM.PERIOD_AMOUNT20
, PPDM.PERIOD_AMOUNT21
, PPDM.PERIOD_AMOUNT22
, PPDM.PERIOD_AMOUNT23
, PPDM.PERIOD_AMOUNT24
, PPDM.PERIOD_AMOUNT25
, PPDM.PERIOD_AMOUNT26
, PPDM.PERIOD_AMOUNT27
, PPDM.PERIOD_AMOUNT28
, PPDM.PERIOD_AMOUNT29
, PPDM.PERIOD_AMOUNT30
, PPDM.PERIOD_AMOUNT31
, PPDM.PERIOD_AMOUNT32
, PPDM.PERIOD_AMOUNT33
, PPDM.PERIOD_AMOUNT34
, PPDM.PERIOD_AMOUNT35
, PPDM.PERIOD_AMOUNT36
, PPDM.PERIOD_AMOUNT37
, PPDM.PERIOD_AMOUNT38
, PPDM.PERIOD_AMOUNT39
, PPDM.PERIOD_AMOUNT40
, PPDM.PERIOD_AMOUNT41
, PPDM.PERIOD_AMOUNT42
, PPDM.PERIOD_AMOUNT43
, PPDM.PERIOD_AMOUNT44
, PPDM.PERIOD_AMOUNT45
, PPDM.PERIOD_AMOUNT46
, PPDM.PERIOD_AMOUNT47
, PPDM.PERIOD_AMOUNT48
, PPDM.PERIOD_AMOUNT49
, PPDM.PERIOD_AMOUNT50
, PPDM.PERIOD_AMOUNT51
, PPDM.PERIOD_AMOUNT52
FROM PA_PROJ_PERIODS_DENORM PPDM
, HR_ALL_ORGANIZATION_UNITS HRORG1
, HR_ALL_ORGANIZATION_UNITS HRORG
, PA_RESOURCE_ASSIGNMENTS PRA
, PA_PROJECTS_ALL PP
, PA_BUDGET_VERSIONS PBV
, PA_AMOUNT_TYPES_VL PMVL
WHERE PRA.BUDGET_VERSION_ID= PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PBV.BUDGET_VERSION_ID= PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PRA.PROJECT_ID = PBV.PROJECT_ID
AND PRA.TASK_ID=0
AND PPDM.AMOUNT_TYPE_CODE= PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
AND PPDM.AMOUNT_SUBTYPE_CODE LIKE '%ADJUSTMENTS%'
AND PMVL.AMOUNT_TYPE_ID = PPDM.AMOUNT_SUBTYPE_ID
AND PP.PROJECT_ID= PBV.PROJECT_ID
AND NVL(HRORG.ORGANIZATION_ID
, -99) = NVL(PP.CARRYING_OUT_ORGANIZATION_ID
, -99)
AND NVL(HRORG1.ORGANIZATION_ID
, -99) = NVL(PP.ORG_ID
, -99)
AND PPDM.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PPDM.OBJECT_ID = PRA.RESOURCE_ASSIGNMENT_ID
AND PPDM.OBJECT_TYPE_CODE = 'RES_ASSIGNMENT'
AND PPDM.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
AND PPDM.CURRENCY_TYPE = 'PROJ_FUNCTIONAL' ORDER BY 1
, 9