VIEW: APPS.PA_STATUS_RSRC_EI_BASE_V

Source

View Text - Preformatted

SELECT RAD.PROJECT_ID , RAD.TASK_ID , RAD.RESOURCE_LIST_MEMBER_ID , TA.TXN_ACCUM_ID , TA.PA_PERIOD , pa_per.start_date , pa_per.end_date , TAD.EXPENDITURE_ITEM_ID , CDL.QUANTITY , CDL.AMOUNT , CDL.BURDENED_COST , DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.QUANTITY,0),0) BILLABLE_QUANTITY , DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.AMOUNT,0),0) BILLABLE_RAW_COST , DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.BURDENED_COST,0),0) BILLABLE_BURDENED_COST , 0 , CDL.DENOM_RAW_COST , CDL.DENOM_BURDENED_COST , CDL.ACCT_RAW_COST , CDL.ACCT_BURDENED_COST FROM PA_RESOURCE_ACCUM_DETAILS RAD , PA_TXN_ACCUM TA , PA_COST_DISTRIBUTION_LINES_ALL CDL , PA_TXN_ACCUM_DETAILS TAD , PA_PERIODS PA_PER WHERE TAD.TXN_ACCUM_ID = RAD.TXN_ACCUM_ID AND TA.TXN_ACCUM_ID = TAD.TXN_ACCUM_ID AND TAD.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND TAD.LINE_TYPE = 'C' AND TAD.LINE_NUM = CDL.LINE_NUM AND RAD.PROJECT_ID = PA_STATUS.GETPROJID AND RAD.TASK_ID = PA_STATUS.GETTASKID AND RAD.RESOURCE_LIST_MEMBER_ID = PA_STATUS.GETRSRCMEMBERID AND ta.pa_period = pa_per.period_name AND pa_per.start_date >= nvl(PA_STATUS.GetStartDate, pa_per.start_date) AND pa_per.start_date <= nvl(PA_STATUS.GetEndDate,pa_per.start_date) UNION ALL SELECT /*EI REVENUE FROM RDLS */ RAD.PROJECT_ID ,RAD.TASK_ID ,RAD.RESOURCE_LIST_MEMBER_ID ,TA.TXN_ACCUM_ID ,TA.PA_PERIOD , pa_per.start_date , pa_per.end_date ,TAD.EXPENDITURE_ITEM_ID ,0 ,0 ,0 ,0 ,0 ,0 ,RDL.AMOUNT ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) FROM PA_RESOURCE_ACCUM_DETAILS RAD , PA_TXN_ACCUM TA , PA_CUST_REV_DIST_LINES_ALL RDL , PA_TXN_ACCUM_DETAILS TAD , PA_PERIODS PA_PER WHERE TAD.TXN_ACCUM_ID = RAD.TXN_ACCUM_ID AND TA.TXN_ACCUM_ID = TAD.TXN_ACCUM_ID AND TAD.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID AND TAD.LINE_TYPE = 'R' AND RDL.LINE_NUM = TAD.LINE_NUM AND RAD.PROJECT_ID = PA_STATUS.GETPROJID AND RAD.TASK_ID = PA_STATUS.GETTASKID AND RAD.RESOURCE_LIST_MEMBER_ID = PA_STATUS.GETRSRCMEMBERID AND ta.pa_period = pa_per.period_name AND pa_per.start_date >= nvl(PA_STATUS.GetStartDate, pa_per.start_date) AND pa_per.start_date <= nvl(PA_STATUS.GetEndDate,pa_per.start_date)

View Text - HTML Formatted

SELECT RAD.PROJECT_ID
, RAD.TASK_ID
, RAD.RESOURCE_LIST_MEMBER_ID
, TA.TXN_ACCUM_ID
, TA.PA_PERIOD
, PA_PER.START_DATE
, PA_PER.END_DATE
, TAD.EXPENDITURE_ITEM_ID
, CDL.QUANTITY
, CDL.AMOUNT
, CDL.BURDENED_COST
, DECODE(CDL.BILLABLE_FLAG
, 'Y'
, NVL(CDL.QUANTITY
, 0)
, 0) BILLABLE_QUANTITY
, DECODE(CDL.BILLABLE_FLAG
, 'Y'
, NVL(CDL.AMOUNT
, 0)
, 0) BILLABLE_RAW_COST
, DECODE(CDL.BILLABLE_FLAG
, 'Y'
, NVL(CDL.BURDENED_COST
, 0)
, 0) BILLABLE_BURDENED_COST
, 0
, CDL.DENOM_RAW_COST
, CDL.DENOM_BURDENED_COST
, CDL.ACCT_RAW_COST
, CDL.ACCT_BURDENED_COST
FROM PA_RESOURCE_ACCUM_DETAILS RAD
, PA_TXN_ACCUM TA
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_TXN_ACCUM_DETAILS TAD
, PA_PERIODS PA_PER
WHERE TAD.TXN_ACCUM_ID = RAD.TXN_ACCUM_ID
AND TA.TXN_ACCUM_ID = TAD.TXN_ACCUM_ID
AND TAD.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND TAD.LINE_TYPE = 'C'
AND TAD.LINE_NUM = CDL.LINE_NUM
AND RAD.PROJECT_ID = PA_STATUS.GETPROJID
AND RAD.TASK_ID = PA_STATUS.GETTASKID
AND RAD.RESOURCE_LIST_MEMBER_ID = PA_STATUS.GETRSRCMEMBERID
AND TA.PA_PERIOD = PA_PER.PERIOD_NAME
AND PA_PER.START_DATE >= NVL(PA_STATUS.GETSTARTDATE
, PA_PER.START_DATE)
AND PA_PER.START_DATE <= NVL(PA_STATUS.GETENDDATE
, PA_PER.START_DATE) UNION ALL SELECT /*EI REVENUE
FROM RDLS */ RAD.PROJECT_ID
, RAD.TASK_ID
, RAD.RESOURCE_LIST_MEMBER_ID
, TA.TXN_ACCUM_ID
, TA.PA_PERIOD
, PA_PER.START_DATE
, PA_PER.END_DATE
, TAD.EXPENDITURE_ITEM_ID
, 0
, 0
, 0
, 0
, 0
, 0
, RDL.AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM PA_RESOURCE_ACCUM_DETAILS RAD
, PA_TXN_ACCUM TA
, PA_CUST_REV_DIST_LINES_ALL RDL
, PA_TXN_ACCUM_DETAILS TAD
, PA_PERIODS PA_PER
WHERE TAD.TXN_ACCUM_ID = RAD.TXN_ACCUM_ID
AND TA.TXN_ACCUM_ID = TAD.TXN_ACCUM_ID
AND TAD.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID
AND TAD.LINE_TYPE = 'R'
AND RDL.LINE_NUM = TAD.LINE_NUM
AND RAD.PROJECT_ID = PA_STATUS.GETPROJID
AND RAD.TASK_ID = PA_STATUS.GETTASKID
AND RAD.RESOURCE_LIST_MEMBER_ID = PA_STATUS.GETRSRCMEMBERID
AND TA.PA_PERIOD = PA_PER.PERIOD_NAME
AND PA_PER.START_DATE >= NVL(PA_STATUS.GETSTARTDATE
, PA_PER.START_DATE)
AND PA_PER.START_DATE <= NVL(PA_STATUS.GETENDDATE
, PA_PER.START_DATE)