[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
SUM(PTXN.USER_COL6) USER_COL6,
SUM(PTXN.USER_COL7) USER_COL7,
SUM(PTXN.USER_COL8) USER_COL8,
SUM(PTXN.USER_COL9) USER_COL9,
SUM(PTXN.USER_COL10) USER_COL10,
SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
PTXN.UNIT_OF_MEASURE
FROM
PA_ADW_R_ACT_CMT_V PTXN,
PA_ADW_LOWEST_TASKS_V PT
WHERE
PTXN.TASK_ID = PT.TASK_ID
AND PTXN.PROJECT_ID = x_project_id
-- Exclude top tasks
AND PT.TASK_ID <> PT.TOP_TASK_ID
AND PTXN.RES_ADW_NOTIFY_FLAG = 'S'
GROUP BY
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
PTXN.UNIT_OF_MEASURE;
SELECT
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
SUM(PTXN.USER_COL6) USER_COL6,
SUM(PTXN.USER_COL7) USER_COL7,
SUM(PTXN.USER_COL8) USER_COL8,
SUM(PTXN.USER_COL9) USER_COL9,
SUM(PTXN.USER_COL10) USER_COL10,
SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
PTXN.UNIT_OF_MEASURE
FROM
PA_ADW_R_ACT_CMT_V PTXN,
PA_ADW_TOP_TASKS_V PT
WHERE
PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
AND PTXN.PROJECT_ID = x_project_id
AND PTXN.RES_ADW_NOTIFY_FLAG = 'S'
GROUP BY
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
PTXN.UNIT_OF_MEASURE;
SELECT
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
SUM(USER_COL6) USER_COL6,
SUM(USER_COL7) USER_COL7,
SUM(USER_COL8) USER_COL8,
SUM(USER_COL9) USER_COL9,
SUM(USER_COL10) USER_COL10,
SUM(ACCUME_REVENUE) ACCUME_REVENUE,
SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE
FROM
PA_ADW_R_ACT_CMT_V
WHERE
RES_ADW_NOTIFY_FLAG = 'S'
AND PROJECT_ID = x_project_id
GROUP BY
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
UNIT_OF_MEASURE;
SELECT
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
SUM(PTXN.USER_COL6) USER_COL6,
SUM(PTXN.USER_COL7) USER_COL7,
SUM(PTXN.USER_COL8) USER_COL8,
SUM(PTXN.USER_COL9) USER_COL9,
SUM(PTXN.USER_COL10) USER_COL10,
SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
PTXN.UNIT_OF_MEASURE
FROM
PA_ADW_R_ST_ACT_CMT_V PTXN,
PA_ADW_LOWEST_TASKS_V PT
WHERE
PTXN.TASK_ID = PT.TASK_ID
AND PTXN.PROJECT_ID = x_project_id
-- Exclude top tasks
AND PT.TASK_ID <> PT.TOP_TASK_ID
AND (PTXN.TSK_ADW_NOTIFY_FLAG = 'S' OR PTXN.TSK_ADW_NOTIFY_FLAG = 'P')
GROUP BY
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
PTXN.UNIT_OF_MEASURE;
SELECT
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
SUM(PTXN.USER_COL6) USER_COL6,
SUM(PTXN.USER_COL7) USER_COL7,
SUM(PTXN.USER_COL8) USER_COL8,
SUM(PTXN.USER_COL9) USER_COL9,
SUM(PTXN.USER_COL10) USER_COL10,
SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
PTXN.UNIT_OF_MEASURE
FROM
PA_ADW_R_ST_ACT_CMT_V PTXN,
PA_ADW_TOP_TASKS_V PT
WHERE
PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
AND PTXN.PROJECT_ID = x_project_id
AND (PTXN.TSK_ADW_NOTIFY_FLAG = 'S' OR PTXN.TSK_ADW_NOTIFY_FLAG = 'P')
GROUP BY
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
PTXN.EXPENSE_ORGANIZATION_ID,
PTXN.OWNER_ORGANIZATION_ID,
PTXN.RESOURCE_LIST_MEMBER_ID,
PTXN.SERVICE_TYPE_CODE,
PTXN.EXPENDITURE_TYPE,
PTXN.USER_COL1,
PTXN.USER_COL2,
PTXN.USER_COL3,
PTXN.USER_COL4,
PTXN.USER_COL5,
PTXN.UNIT_OF_MEASURE;
SELECT
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
SUM(USER_COL6) USER_COL6,
SUM(USER_COL7) USER_COL7,
SUM(USER_COL8) USER_COL8,
SUM(USER_COL9) USER_COL9,
SUM(USER_COL10) USER_COL10,
SUM(ACCUME_REVENUE) ACCUME_REVENUE,
SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE
FROM
PA_ADW_R_ST_ACT_CMT_V
WHERE
(TSK_ADW_NOTIFY_FLAG = 'S' OR TSK_ADW_NOTIFY_FLAG = 'P')
AND PROJECT_ID = x_project_id
GROUP BY
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
UNIT_OF_MEASURE;
SELECT
TASK_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
SUM(USER_COL6) USER_COL6,
SUM(USER_COL7) USER_COL7,
SUM(USER_COL8) USER_COL8,
SUM(USER_COL9) USER_COL9,
SUM(USER_COL10) USER_COL10,
SUM(ACCUME_REVENUE) ACCUME_REVENUE,
SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE
FROM
PA_ADW_ACT_CMT_V
WHERE
PROJECT_ID = x_project_id
AND (TASK_ID,
PA_PERIOD_KEY,
NVL(EXPENSE_ORGANIZATION_ID,-99),
NVL(OWNER_ORGANIZATION_ID,-99),
NVL(RESOURCE_LIST_MEMBER_ID,-99),
NVL(SERVICE_TYPE_CODE,'X'),
NVL(EXPENDITURE_TYPE,'X'),
NVL(USER_COL1,'X'),
NVL(USER_COL2,'X'),
NVL(USER_COL3,'X'),
NVL(USER_COL4,'X'),
NVL(USER_COL5,'X'),
NVL(UNIT_OF_MEASURE,'X'))
IN
(SELECT
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X')
FROM
PA_ADW_ACT_CMT_V PTXN,
PA_ADW_LOWEST_TASKS_V PT
WHERE
PTXN.TASK_ID = PT.TASK_ID
AND PTXN.PROJECT_ID = x_project_id
-- Exclude the tasks which are top tasks
AND PT.TASK_ID <> PT.TOP_TASK_ID
AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
GROUP BY
PTXN.TASK_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X'))
GROUP BY
TASK_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
UNIT_OF_MEASURE;
SELECT
TOP_TASK_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
SUM(USER_COL6) USER_COL6,
SUM(USER_COL7) USER_COL7,
SUM(USER_COL8) USER_COL8,
SUM(USER_COL9) USER_COL9,
SUM(USER_COL10) USER_COL10,
SUM(ACCUME_REVENUE) ACCUME_REVENUE,
SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE
FROM
PA_ADW_ACT_CMT_V
WHERE
PROJECT_ID = x_project_id
AND (TOP_TASK_ID,
PA_PERIOD_KEY,
NVL(EXPENSE_ORGANIZATION_ID,-99),
NVL(OWNER_ORGANIZATION_ID,-99),
NVL(RESOURCE_LIST_MEMBER_ID,-99),
NVL(SERVICE_TYPE_CODE,'X'),
NVL(EXPENDITURE_TYPE,'X'),
NVL(USER_COL1,'X'),
NVL(USER_COL2,'X'),
NVL(USER_COL3,'X'),
NVL(USER_COL4,'X'),
NVL(USER_COL5,'X'),
NVL(UNIT_OF_MEASURE,'X'))
IN
(SELECT
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X')
FROM
PA_ADW_ACT_CMT_V PTXN,
PA_ADW_TOP_TASKS_V PT
WHERE
PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
AND PTXN.PROJECT_ID = x_project_id
AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
GROUP BY
PTXN.TOP_TASK_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X'))
GROUP BY
TOP_TASK_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
UNIT_OF_MEASURE;
SELECT
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
SUM(USER_COL6) USER_COL6,
SUM(USER_COL7) USER_COL7,
SUM(USER_COL8) USER_COL8,
SUM(USER_COL9) USER_COL9,
SUM(USER_COL10) USER_COL10,
SUM(ACCUME_REVENUE) ACCUME_REVENUE,
SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE
FROM
PA_ADW_ACT_CMT_V
WHERE
PROJECT_ID = x_project_id
AND (PROJECT_ID,
PA_PERIOD_KEY,
NVL(EXPENSE_ORGANIZATION_ID,-99),
NVL(OWNER_ORGANIZATION_ID,-99),
NVL(RESOURCE_LIST_MEMBER_ID,-99),
NVL(SERVICE_TYPE_CODE,'X'),
NVL(EXPENDITURE_TYPE,'X'),
NVL(USER_COL1,'X'),
NVL(USER_COL2,'X'),
NVL(USER_COL3,'X'),
NVL(USER_COL4,'X'),
NVL(USER_COL5,'X'),
NVL(UNIT_OF_MEASURE,'X'))
IN
(SELECT
PTXN.PROJECT_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X')
FROM
PA_ADW_ACT_CMT_V PTXN
WHERE
PTXN.PROJECT_ID = x_project_id
AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
GROUP BY
PTXN.PROJECT_ID,
PTXN.PA_PERIOD_KEY,
NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
NVL(PTXN.SERVICE_TYPE_CODE,'X'),
NVL(PTXN.EXPENDITURE_TYPE,'X'),
NVL(PTXN.USER_COL1,'X'),
NVL(PTXN.USER_COL2,'X'),
NVL(PTXN.USER_COL3,'X'),
NVL(PTXN.USER_COL4,'X'),
NVL(PTXN.USER_COL5,'X'),
NVL(PTXN.UNIT_OF_MEASURE,'X'))
GROUP BY
PROJECT_ID,
PA_PERIOD_KEY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
UNIT_OF_MEASURE;
SELECT
PROJECT_ID,
SEGMENT1
FROM
PA_ADW_PROJECTS_V
WHERE segment1 BETWEEN NVL(x_project_num_from,segment1)
AND NVL(x_project_num_to,segment1);
UPDATE
PA_TASK_HISTORY PTH
SET
ADW_NOTIFY_FLAG = 'Y'
WHERE
PROJECT_ID = sel_prjs_r.project_id
AND TASK_HISTORY_ID IN
( SELECT MAX(TASK_HISTORY_ID)
FROM PA_TASK_HISTORY PTHL
WHERE PTHL.TASK_ID = PTH.TASK_ID
);
UPDATE PA_ADW_INTERFACED_TASKS PTH
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
ADW_INTERFACE_FLAG = 'Y'
AND PROJECT_ID = sel_prjs_r.project_id
AND EXISTS
( SELECT 'Yes'
FROM PA_TASK_HISTORY PTHL
WHERE PTHL.TASK_ID = PTH.TASK_ID
AND (
PTHL.SERVICE_TYPE_CODE <> PTH.SERVICE_TYPE_CODE or
PTHL.CARRYING_OUT_ORGANIZATION_ID <> PTH.CARRYING_OUT_ORGANIZATION_ID -- fix for bug 1233570, created by DMPOTAPO
)
AND PTHL.TASK_HISTORY_ID > PTH.TASK_HISTORY_ID
)
AND NOT EXISTS
( SELECT 'Yes'
FROM PA_OLD_RES_ACCUM_DTLS
WHERE PROJECT_ID = sel_prjs_r.project_id
AND TASK_ID = PTH.TASK_ID
AND ADW_NOTIFY_FLAG = 'Y'
);
UPDATE PA_ADW_INTERFACED_TASKS PTH
SET
ADW_NOTIFY_FLAG = 'P'
WHERE
ADW_INTERFACE_FLAG = 'Y'
AND ADW_NOTIFY_FLAG <> 'S'
AND PROJECT_ID = sel_prjs_r.project_id
AND EXISTS
( SELECT 'Yes'
FROM PA_ADW_INTERFACED_TASKS PTHL
WHERE PTHL.TASK_ID <> PTH.TASK_ID
AND (
PTHL.SERVICE_TYPE_CODE = PTH.SERVICE_TYPE_CODE or
PTHL.CARRYING_OUT_ORGANIZATION_ID = PTH.CARRYING_OUT_ORGANIZATION_ID -- fix for bug 1233570, created by DMPOTAPO
)
AND PTHL.PROJECT_ID = sel_prjs_r.project_id
AND PTHL.ADW_NOTIFY_FLAG = 'S'
)
AND NOT EXISTS
( SELECT 'Yes'
FROM PA_OLD_RES_ACCUM_DTLS
WHERE PROJECT_ID = sel_prjs_r.project_id
AND TASK_ID = PTH.TASK_ID
AND ADW_NOTIFY_FLAG = 'Y'
);
UPDATE PA_ADW_INTERFACED_TASKS PTH
SET
ADW_NOTIFY_FLAG = 'P'
WHERE
ADW_INTERFACE_FLAG = 'Y'
AND ADW_NOTIFY_FLAG <> 'S'
AND PROJECT_ID = sel_prjs_r.project_id
AND EXISTS
( SELECT 'Yes'
FROM PA_TASK_HISTORY PTHL
WHERE PTHL.TASK_ID = PTH.TOP_TASK_ID
AND PTHL.ADW_NOTIFY_FLAG = 'S'
)
AND NOT EXISTS
( SELECT 'Yes'
FROM PA_OLD_RES_ACCUM_DTLS
WHERE PROJECT_ID = sel_prjs_r.project_id
AND TASK_ID = PTH.TASK_ID
AND ADW_NOTIFY_FLAG = 'Y'
);
UPDATE
PA_OLD_RES_ACCUM_DTLS
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
PROJECT_ID = sel_prjs_r.project_id
AND ADW_NOTIFY_FLAG = 'Y';
update_tasks_act_cmt
(ref_lowest_act_cmts_r.task_id,
ref_lowest_act_cmts_r.pa_period_key,
ref_lowest_act_cmts_r.expense_organization_id,
ref_lowest_act_cmts_r.owner_organization_id,
ref_lowest_act_cmts_r.resource_list_member_id,
ref_lowest_act_cmts_r.service_type_code,
ref_lowest_act_cmts_r.expenditure_type,
ref_lowest_act_cmts_r.user_col1,
ref_lowest_act_cmts_r.user_col2,
ref_lowest_act_cmts_r.user_col3,
ref_lowest_act_cmts_r.user_col4,
ref_lowest_act_cmts_r.user_col5,
ref_lowest_act_cmts_r.user_col6,
ref_lowest_act_cmts_r.user_col7,
ref_lowest_act_cmts_r.user_col8,
ref_lowest_act_cmts_r.user_col9,
ref_lowest_act_cmts_r.user_col10,
ref_lowest_act_cmts_r.accume_revenue,
ref_lowest_act_cmts_r.accume_raw_cost,
ref_lowest_act_cmts_r.accume_burdened_cost,
ref_lowest_act_cmts_r.accume_quantity,
ref_lowest_act_cmts_r.accume_labor_hours,
ref_lowest_act_cmts_r.accume_billable_raw_cost,
ref_lowest_act_cmts_r.accume_billable_burdened_cost,
ref_lowest_act_cmts_r.accume_billable_quantity,
ref_lowest_act_cmts_r.accume_billable_labor_hours,
ref_lowest_act_cmts_r.accume_cmt_raw_cost,
ref_lowest_act_cmts_r.accume_cmt_burdened_cost,
ref_lowest_act_cmts_r.accume_cmt_quantity,
ref_lowest_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_act_cmt
(ref_top_act_cmts_r.top_task_id,
ref_top_act_cmts_r.pa_period_key,
ref_top_act_cmts_r.expense_organization_id,
ref_top_act_cmts_r.owner_organization_id,
ref_top_act_cmts_r.resource_list_member_id,
ref_top_act_cmts_r.service_type_code,
ref_top_act_cmts_r.expenditure_type,
ref_top_act_cmts_r.user_col1,
ref_top_act_cmts_r.user_col2,
ref_top_act_cmts_r.user_col3,
ref_top_act_cmts_r.user_col4,
ref_top_act_cmts_r.user_col5,
ref_top_act_cmts_r.user_col6,
ref_top_act_cmts_r.user_col7,
ref_top_act_cmts_r.user_col8,
ref_top_act_cmts_r.user_col9,
ref_top_act_cmts_r.user_col10,
ref_top_act_cmts_r.accume_revenue,
ref_top_act_cmts_r.accume_raw_cost,
ref_top_act_cmts_r.accume_burdened_cost,
ref_top_act_cmts_r.accume_quantity,
ref_top_act_cmts_r.accume_labor_hours,
ref_top_act_cmts_r.accume_billable_raw_cost,
ref_top_act_cmts_r.accume_billable_burdened_cost,
ref_top_act_cmts_r.accume_billable_quantity,
ref_top_act_cmts_r.accume_billable_labor_hours,
ref_top_act_cmts_r.accume_cmt_raw_cost,
ref_top_act_cmts_r.accume_cmt_burdened_cost,
ref_top_act_cmts_r.accume_cmt_quantity,
ref_top_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_prj_act_cmt
(ref_prj_act_cmts_r.project_id,
ref_prj_act_cmts_r.pa_period_key,
ref_prj_act_cmts_r.expense_organization_id,
ref_prj_act_cmts_r.owner_organization_id,
ref_prj_act_cmts_r.resource_list_member_id,
ref_prj_act_cmts_r.service_type_code,
ref_prj_act_cmts_r.expenditure_type,
ref_prj_act_cmts_r.user_col1,
ref_prj_act_cmts_r.user_col2,
ref_prj_act_cmts_r.user_col3,
ref_prj_act_cmts_r.user_col4,
ref_prj_act_cmts_r.user_col5,
ref_prj_act_cmts_r.user_col6,
ref_prj_act_cmts_r.user_col7,
ref_prj_act_cmts_r.user_col8,
ref_prj_act_cmts_r.user_col9,
ref_prj_act_cmts_r.user_col10,
ref_prj_act_cmts_r.accume_revenue,
ref_prj_act_cmts_r.accume_raw_cost,
ref_prj_act_cmts_r.accume_burdened_cost,
ref_prj_act_cmts_r.accume_quantity,
ref_prj_act_cmts_r.accume_labor_hours,
ref_prj_act_cmts_r.accume_billable_raw_cost,
ref_prj_act_cmts_r.accume_billable_burdened_cost,
ref_prj_act_cmts_r.accume_billable_quantity,
ref_prj_act_cmts_r.accume_billable_labor_hours,
ref_prj_act_cmts_r.accume_cmt_raw_cost,
ref_prj_act_cmts_r.accume_cmt_burdened_cost,
ref_prj_act_cmts_r.accume_cmt_quantity,
ref_prj_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
UPDATE
PA_OLD_RES_ACCUM_DTLS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
update_tasks_act_cmt
(ref_lowest_ser_type_act_cmts_r.task_id,
ref_lowest_ser_type_act_cmts_r.pa_period_key,
ref_lowest_ser_type_act_cmts_r.expense_organization_id,
ref_lowest_ser_type_act_cmts_r.owner_organization_id,
ref_lowest_ser_type_act_cmts_r.resource_list_member_id,
ref_lowest_ser_type_act_cmts_r.service_type_code,
ref_lowest_ser_type_act_cmts_r.expenditure_type,
ref_lowest_ser_type_act_cmts_r.user_col1,
ref_lowest_ser_type_act_cmts_r.user_col2,
ref_lowest_ser_type_act_cmts_r.user_col3,
ref_lowest_ser_type_act_cmts_r.user_col4,
ref_lowest_ser_type_act_cmts_r.user_col5,
ref_lowest_ser_type_act_cmts_r.user_col6,
ref_lowest_ser_type_act_cmts_r.user_col7,
ref_lowest_ser_type_act_cmts_r.user_col8,
ref_lowest_ser_type_act_cmts_r.user_col9,
ref_lowest_ser_type_act_cmts_r.user_col10,
ref_lowest_ser_type_act_cmts_r.accume_revenue,
ref_lowest_ser_type_act_cmts_r.accume_raw_cost,
ref_lowest_ser_type_act_cmts_r.accume_burdened_cost,
ref_lowest_ser_type_act_cmts_r.accume_quantity,
ref_lowest_ser_type_act_cmts_r.accume_labor_hours,
ref_lowest_ser_type_act_cmts_r.accume_billable_raw_cost,
ref_lowest_ser_type_act_cmts_r.accume_billable_burdened_cost,
ref_lowest_ser_type_act_cmts_r.accume_billable_quantity,
ref_lowest_ser_type_act_cmts_r.accume_billable_labor_hours,
ref_lowest_ser_type_act_cmts_r.accume_cmt_raw_cost,
ref_lowest_ser_type_act_cmts_r.accume_cmt_burdened_cost,
ref_lowest_ser_type_act_cmts_r.accume_cmt_quantity,
ref_lowest_ser_type_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_act_cmt
(ref_top_ser_type_act_cmts_r.top_task_id,
ref_top_ser_type_act_cmts_r.pa_period_key,
ref_top_ser_type_act_cmts_r.expense_organization_id,
ref_top_ser_type_act_cmts_r.owner_organization_id,
ref_top_ser_type_act_cmts_r.resource_list_member_id,
ref_top_ser_type_act_cmts_r.service_type_code,
ref_top_ser_type_act_cmts_r.expenditure_type,
ref_top_ser_type_act_cmts_r.user_col1,
ref_top_ser_type_act_cmts_r.user_col2,
ref_top_ser_type_act_cmts_r.user_col3,
ref_top_ser_type_act_cmts_r.user_col4,
ref_top_ser_type_act_cmts_r.user_col5,
ref_top_ser_type_act_cmts_r.user_col6,
ref_top_ser_type_act_cmts_r.user_col7,
ref_top_ser_type_act_cmts_r.user_col8,
ref_top_ser_type_act_cmts_r.user_col9,
ref_top_ser_type_act_cmts_r.user_col10,
ref_top_ser_type_act_cmts_r.accume_revenue,
ref_top_ser_type_act_cmts_r.accume_raw_cost,
ref_top_ser_type_act_cmts_r.accume_burdened_cost,
ref_top_ser_type_act_cmts_r.accume_quantity,
ref_top_ser_type_act_cmts_r.accume_labor_hours,
ref_top_ser_type_act_cmts_r.accume_billable_raw_cost,
ref_top_ser_type_act_cmts_r.accume_billable_burdened_cost,
ref_top_ser_type_act_cmts_r.accume_billable_quantity,
ref_top_ser_type_act_cmts_r.accume_billable_labor_hours,
ref_top_ser_type_act_cmts_r.accume_cmt_raw_cost,
ref_top_ser_type_act_cmts_r.accume_cmt_burdened_cost,
ref_top_ser_type_act_cmts_r.accume_cmt_quantity,
ref_top_ser_type_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_prj_act_cmt
(ref_prj_ser_type_act_cmts_r.project_id,
ref_prj_ser_type_act_cmts_r.pa_period_key,
ref_prj_ser_type_act_cmts_r.expense_organization_id,
ref_prj_ser_type_act_cmts_r.owner_organization_id,
ref_prj_ser_type_act_cmts_r.resource_list_member_id,
ref_prj_ser_type_act_cmts_r.service_type_code,
ref_prj_ser_type_act_cmts_r.expenditure_type,
ref_prj_ser_type_act_cmts_r.user_col1,
ref_prj_ser_type_act_cmts_r.user_col2,
ref_prj_ser_type_act_cmts_r.user_col3,
ref_prj_ser_type_act_cmts_r.user_col4,
ref_prj_ser_type_act_cmts_r.user_col5,
ref_prj_ser_type_act_cmts_r.user_col6,
ref_prj_ser_type_act_cmts_r.user_col7,
ref_prj_ser_type_act_cmts_r.user_col8,
ref_prj_ser_type_act_cmts_r.user_col9,
ref_prj_ser_type_act_cmts_r.user_col10,
ref_prj_ser_type_act_cmts_r.accume_revenue,
ref_prj_ser_type_act_cmts_r.accume_raw_cost,
ref_prj_ser_type_act_cmts_r.accume_burdened_cost,
ref_prj_ser_type_act_cmts_r.accume_quantity,
ref_prj_ser_type_act_cmts_r.accume_labor_hours,
ref_prj_ser_type_act_cmts_r.accume_billable_raw_cost,
ref_prj_ser_type_act_cmts_r.accume_billable_burdened_cost,
ref_prj_ser_type_act_cmts_r.accume_billable_quantity,
ref_prj_ser_type_act_cmts_r.accume_billable_labor_hours,
ref_prj_ser_type_act_cmts_r.accume_cmt_raw_cost,
ref_prj_ser_type_act_cmts_r.accume_cmt_burdened_cost,
ref_prj_ser_type_act_cmts_r.accume_cmt_quantity,
ref_prj_ser_type_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
UPDATE PA_TXN_ACCUM PTA
SET ADW_NOTIFY_FLAG = 'Y'
WHERE
TASK_ID IN
(SELECT TASK_ID
FROM PA_TASK_HISTORY PTH
WHERE PTH.TASK_ID = PTA.TASK_ID
AND PTH.ADW_NOTIFY_FLAG IN ('S','P')
);
UPDATE
PA_TASK_HISTORY
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
UPDATE
PA_TASK_HISTORY
SET
ADW_NOTIFY_FLAG = 'Y'
WHERE
ADW_NOTIFY_FLAG = 'P';
UPDATE
PA_RESOURCE_ACCUM_DETAILS PRAD
SET
PRAD.ADW_NOTIFY_FLAG = 'S'
WHERE
PRAD.PROJECT_ID = SEL_PRJS_R.PROJECT_ID
AND PRAD.ADW_NOTIFY_FLAG = 'Y'
AND EXISTS
( SELECT 'Yes'
FROM
PA_ADW_RES_LISTS_V PRL
WHERE PRAD.RESOURCE_LIST_ID = PRL.RESOURCE_LIST_ID
);
UPDATE
PA_TXN_ACCUM
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
PROJECT_ID = sel_prjs_r.project_id
AND ADW_NOTIFY_FLAG = 'Y';
update_tasks_act_cmt
(lowest_act_cmts_r.task_id,
lowest_act_cmts_r.pa_period_key,
lowest_act_cmts_r.expense_organization_id,
lowest_act_cmts_r.owner_organization_id,
lowest_act_cmts_r.resource_list_member_id,
lowest_act_cmts_r.service_type_code,
lowest_act_cmts_r.expenditure_type,
lowest_act_cmts_r.user_col1,
lowest_act_cmts_r.user_col2,
lowest_act_cmts_r.user_col3,
lowest_act_cmts_r.user_col4,
lowest_act_cmts_r.user_col5,
lowest_act_cmts_r.user_col6,
lowest_act_cmts_r.user_col7,
lowest_act_cmts_r.user_col8,
lowest_act_cmts_r.user_col9,
lowest_act_cmts_r.user_col10,
lowest_act_cmts_r.accume_revenue,
lowest_act_cmts_r.accume_raw_cost,
lowest_act_cmts_r.accume_burdened_cost,
lowest_act_cmts_r.accume_quantity,
lowest_act_cmts_r.accume_labor_hours,
lowest_act_cmts_r.accume_billable_raw_cost,
lowest_act_cmts_r.accume_billable_burdened_cost,
lowest_act_cmts_r.accume_billable_quantity,
lowest_act_cmts_r.accume_billable_labor_hours,
lowest_act_cmts_r.accume_cmt_raw_cost,
lowest_act_cmts_r.accume_cmt_burdened_cost,
lowest_act_cmts_r.accume_cmt_quantity,
lowest_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_act_cmt
(top_act_cmts_r.top_task_id,
top_act_cmts_r.pa_period_key,
top_act_cmts_r.expense_organization_id,
top_act_cmts_r.owner_organization_id,
top_act_cmts_r.resource_list_member_id,
top_act_cmts_r.service_type_code,
top_act_cmts_r.expenditure_type,
top_act_cmts_r.user_col1,
top_act_cmts_r.user_col2,
top_act_cmts_r.user_col3,
top_act_cmts_r.user_col4,
top_act_cmts_r.user_col5,
top_act_cmts_r.user_col6,
top_act_cmts_r.user_col7,
top_act_cmts_r.user_col8,
top_act_cmts_r.user_col9,
top_act_cmts_r.user_col10,
top_act_cmts_r.accume_revenue,
top_act_cmts_r.accume_raw_cost,
top_act_cmts_r.accume_burdened_cost,
top_act_cmts_r.accume_quantity,
top_act_cmts_r.accume_labor_hours,
top_act_cmts_r.accume_billable_raw_cost,
top_act_cmts_r.accume_billable_burdened_cost,
top_act_cmts_r.accume_billable_quantity,
top_act_cmts_r.accume_billable_labor_hours,
top_act_cmts_r.accume_cmt_raw_cost,
top_act_cmts_r.accume_cmt_burdened_cost,
top_act_cmts_r.accume_cmt_quantity,
top_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_prj_act_cmt
(prj_act_cmts_r.project_id,
prj_act_cmts_r.pa_period_key,
prj_act_cmts_r.expense_organization_id,
prj_act_cmts_r.owner_organization_id,
prj_act_cmts_r.resource_list_member_id,
prj_act_cmts_r.service_type_code,
prj_act_cmts_r.expenditure_type,
prj_act_cmts_r.user_col1,
prj_act_cmts_r.user_col2,
prj_act_cmts_r.user_col3,
prj_act_cmts_r.user_col4,
prj_act_cmts_r.user_col5,
prj_act_cmts_r.user_col6,
prj_act_cmts_r.user_col7,
prj_act_cmts_r.user_col8,
prj_act_cmts_r.user_col9,
prj_act_cmts_r.user_col10,
prj_act_cmts_r.accume_revenue,
prj_act_cmts_r.accume_raw_cost,
prj_act_cmts_r.accume_burdened_cost,
prj_act_cmts_r.accume_quantity,
prj_act_cmts_r.accume_labor_hours,
prj_act_cmts_r.accume_billable_raw_cost,
prj_act_cmts_r.accume_billable_burdened_cost,
prj_act_cmts_r.accume_billable_quantity,
prj_act_cmts_r.accume_billable_labor_hours,
prj_act_cmts_r.accume_cmt_raw_cost,
prj_act_cmts_r.accume_cmt_burdened_cost,
prj_act_cmts_r.accume_cmt_quantity,
prj_act_cmts_r.unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
UPDATE
PA_TASK_HISTORY PTH
SET
ADW_INTERFACE_FLAG = 'Y'
WHERE
ADW_NOTIFY_FLAG = 'Y'
AND PTH.TASK_ID IN
(SELECT TASK_ID
FROM PA_RESOURCE_ACCUM_DETAILS
WHERE ADW_NOTIFY_FLAG = 'S'
UNION
SELECT TASK_ID
FROM PA_TXN_ACCUM
WHERE ADW_NOTIFY_FLAG = 'S'
);
UPDATE
PA_TASK_HISTORY PTH
SET
ADW_INTERFACE_FLAG = 'Y'
WHERE
ADW_NOTIFY_FLAG = 'Y'
AND PTH.TASK_ID IN
(SELECT TOP_TASK_ID FROM PA_TASK_HISTORY PTHT
WHERE
PTHT.ADW_NOTIFY_FLAG = 'Y'
AND PTHT.TASK_ID IN
(SELECT TASK_ID
FROM PA_RESOURCE_ACCUM_DETAILS
WHERE ADW_NOTIFY_FLAG = 'S'
UNION
SELECT TASK_ID
FROM PA_TXN_ACCUM
WHERE ADW_NOTIFY_FLAG = 'S'
)
);
UPDATE
PA_RESOURCE_ACCUM_DETAILS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
UPDATE
PA_TXN_ACCUM
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
UPDATE PA_TASK_HISTORY PTH
SET ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'Y';
PROCEDURE update_tasks_act_cmt
(x_task_id IN NUMBER,
x_pa_period_key IN VARCHAR2,
x_expense_organization_id IN NUMBER,
x_owner_organization_id IN NUMBER,
x_resource_list_member_id IN NUMBER,
x_service_type_code IN VARCHAR2,
x_expenditure_type IN VARCHAR2,
x_user_col1 IN VARCHAR2,
x_user_col2 IN VARCHAR2,
x_user_col3 IN VARCHAR2,
x_user_col4 IN VARCHAR2,
x_user_col5 IN VARCHAR2,
x_user_col6 IN VARCHAR2,
x_user_col7 IN VARCHAR2,
x_user_col8 IN VARCHAR2,
x_user_col9 IN VARCHAR2,
x_user_col10 IN VARCHAR2,
x_accume_revenue IN NUMBER,
x_accume_raw_cost IN NUMBER,
x_accume_burdened_cost IN NUMBER,
x_accume_quantity IN NUMBER,
x_accume_labor_hours IN NUMBER,
x_accume_billable_raw_cost IN NUMBER,
x_acc_billable_burdened_cost IN NUMBER,
x_accume_billable_quantity IN NUMBER,
x_accume_billable_labor_hours IN NUMBER,
x_accume_cmt_raw_cost IN NUMBER,
x_accume_cmt_burdened_cost IN NUMBER,
x_accume_cmt_quantity IN NUMBER,
x_unit_of_measure IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
x_old_err_stack VARCHAR2(1024);
x_err_stack := x_err_stack || '-> update_tasks_act_cmt';
UPDATE
PA_TSK_ACT_CMT_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL6 = X_USER_COL6,
USER_COL7 = X_USER_COL7,
USER_COL8 = X_USER_COL8,
USER_COL9 = X_USER_COL9,
USER_COL10 = X_USER_COL10,
ACCUME_REVENUE = X_ACCUME_REVENUE,
ACCUME_RAW_COST = X_ACCUME_RAW_COST,
ACCUME_BURDENED_COST = X_ACCUME_BURDENED_COST,
ACCUME_QUANTITY = X_ACCUME_QUANTITY,
ACCUME_LABOR_HOURS = X_ACCUME_LABOR_HOURS,
ACCUME_BILLABLE_RAW_COST = X_ACCUME_BILLABLE_RAW_COST,
ACCUME_BILLABLE_BURDENED_COST = X_ACC_BILLABLE_BURDENED_COST,
ACCUME_BILLABLE_QUANTITY = X_ACCUME_BILLABLE_QUANTITY,
ACCUME_BILLABLE_LABOR_HOURS = X_ACCUME_BILLABLE_LABOR_HOURS,
ACCUME_CMT_RAW_COST = X_ACCUME_CMT_RAW_COST,
ACCUME_CMT_BURDENED_COST = X_ACCUME_CMT_BURDENED_COST,
ACCUME_CMT_QUANTITY = X_ACCUME_CMT_QUANTITY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
STATUS_CODE = 'P'
WHERE
TASK_ID = X_TASK_ID
AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
AND NVL(EXPENSE_ORGANIZATION_ID,-99) = NVL(X_EXPENSE_ORGANIZATION_ID,-99)
AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
AND NVL(UNIT_OF_MEASURE,'X') = NVL(X_UNIT_OF_MEASURE,'X');
-- Check If Any row was updated
IF (SQL%ROWCOUNT = 0) THEN
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_TSK_ACT_CMT_IT
(
TASK_ACT_CMT_KEY,
TASK_ID,
PA_PERIOD_KEY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ACCUME_REVENUE,
ACCUME_RAW_COST,
ACCUME_BURDENED_COST,
ACCUME_QUANTITY,
ACCUME_LABOR_HOURS,
ACCUME_BILLABLE_RAW_COST,
ACCUME_BILLABLE_BURDENED_COST,
ACCUME_BILLABLE_QUANTITY,
ACCUME_BILLABLE_LABOR_HOURS,
ACCUME_CMT_RAW_COST,
ACCUME_CMT_BURDENED_COST,
ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
X_TASK_ID || '-' || X_PA_PERIOD_KEY || '-' || --
NVL(X_EXPENSE_ORGANIZATION_ID,-99) || '-' || --|
NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
NVL(X_USER_COL1,'X')|| '-' || --|
NVL(X_USER_COL2,'X')|| '-' || --|
NVL(X_USER_COL3,'X')|| '-' || --|
NVL(X_USER_COL4,'X')|| '-' || --|
NVL(X_USER_COL5,'X'), --
X_TASK_ID,
X_PA_PERIOD_KEY,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_EXPENSE_ORGANIZATION_ID,
X_OWNER_ORGANIZATION_ID,
X_RESOURCE_LIST_MEMBER_ID,
X_SERVICE_TYPE_CODE,
X_EXPENDITURE_TYPE,
X_USER_COL1,
X_USER_COL2,
X_USER_COL3,
X_USER_COL4,
X_USER_COL5,
X_USER_COL6,
X_USER_COL7,
X_USER_COL8,
X_USER_COL9,
X_USER_COL10,
X_ACCUME_REVENUE,
X_ACCUME_RAW_COST,
X_ACCUME_BURDENED_COST,
X_ACCUME_QUANTITY,
X_ACCUME_LABOR_HOURS,
X_ACCUME_BILLABLE_RAW_COST,
X_ACC_BILLABLE_BURDENED_COST,
X_ACCUME_BILLABLE_QUANTITY,
X_ACCUME_BILLABLE_LABOR_HOURS,
X_ACCUME_CMT_RAW_COST,
X_ACCUME_CMT_BURDENED_COST,
X_ACCUME_CMT_QUANTITY,
X_UNIT_OF_MEASURE,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
END update_tasks_act_cmt;
PROCEDURE update_prj_act_cmt
(x_project_id IN NUMBER,
x_pa_period_key IN VARCHAR2,
x_expense_organization_id IN NUMBER,
x_owner_organization_id IN NUMBER,
x_resource_list_member_id IN NUMBER,
x_service_type_code IN VARCHAR2,
x_expenditure_type IN VARCHAR2,
x_user_col1 IN VARCHAR2,
x_user_col2 IN VARCHAR2,
x_user_col3 IN VARCHAR2,
x_user_col4 IN VARCHAR2,
x_user_col5 IN VARCHAR2,
x_user_col6 IN VARCHAR2,
x_user_col7 IN VARCHAR2,
x_user_col8 IN VARCHAR2,
x_user_col9 IN VARCHAR2,
x_user_col10 IN VARCHAR2,
x_accume_revenue IN NUMBER,
x_accume_raw_cost IN NUMBER,
x_accume_burdened_cost IN NUMBER,
x_accume_quantity IN NUMBER,
x_accume_labor_hours IN NUMBER,
x_accume_billable_raw_cost IN NUMBER,
x_acc_billable_burdened_cost IN NUMBER,
x_accume_billable_quantity IN NUMBER,
x_accume_billable_labor_hours IN NUMBER,
x_accume_cmt_raw_cost IN NUMBER,
x_accume_cmt_burdened_cost IN NUMBER,
x_accume_cmt_quantity IN NUMBER,
x_unit_of_measure IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
x_old_err_stack VARCHAR2(1024);
x_err_stack := x_err_stack || '-> update_prj_act_cmt';
UPDATE
PA_PRJ_ACT_CMT_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL6 = X_USER_COL6,
USER_COL7 = X_USER_COL7,
USER_COL8 = X_USER_COL8,
USER_COL9 = X_USER_COL9,
USER_COL10 = X_USER_COL10,
ACCUME_REVENUE = X_ACCUME_REVENUE,
ACCUME_RAW_COST = X_ACCUME_RAW_COST,
ACCUME_BURDENED_COST = X_ACCUME_BURDENED_COST,
ACCUME_QUANTITY = X_ACCUME_QUANTITY,
ACCUME_LABOR_HOURS = X_ACCUME_LABOR_HOURS,
ACCUME_BILLABLE_RAW_COST = X_ACCUME_BILLABLE_RAW_COST,
ACCUME_BILLABLE_BURDENED_COST = X_ACC_BILLABLE_BURDENED_COST,
ACCUME_BILLABLE_QUANTITY = X_ACCUME_BILLABLE_QUANTITY,
ACCUME_BILLABLE_LABOR_HOURS = X_ACCUME_BILLABLE_LABOR_HOURS,
ACCUME_CMT_RAW_COST = X_ACCUME_CMT_RAW_COST,
ACCUME_CMT_BURDENED_COST = X_ACCUME_CMT_BURDENED_COST,
ACCUME_CMT_QUANTITY = X_ACCUME_CMT_QUANTITY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
STATUS_CODE = 'P'
WHERE
PROJECT_ID = X_PROJECT_ID
AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
AND NVL(EXPENSE_ORGANIZATION_ID,-99) = NVL(X_EXPENSE_ORGANIZATION_ID,-99)
AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
AND NVL(UNIT_OF_MEASURE,'X') = NVL(X_UNIT_OF_MEASURE,'X');
-- Check If Any row was updated
IF (SQL%ROWCOUNT = 0) THEN
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_PRJ_ACT_CMT_IT
(
PRJ_ACT_CMT_KEY,
PROJECT_ID,
PA_PERIOD_KEY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
EXPENSE_ORGANIZATION_ID,
OWNER_ORGANIZATION_ID,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ACCUME_REVENUE,
ACCUME_RAW_COST,
ACCUME_BURDENED_COST,
ACCUME_QUANTITY,
ACCUME_LABOR_HOURS,
ACCUME_BILLABLE_RAW_COST,
ACCUME_BILLABLE_BURDENED_COST,
ACCUME_BILLABLE_QUANTITY,
ACCUME_BILLABLE_LABOR_HOURS,
ACCUME_CMT_RAW_COST,
ACCUME_CMT_BURDENED_COST,
ACCUME_CMT_QUANTITY,
UNIT_OF_MEASURE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
X_PROJECT_ID || '-' || X_PA_PERIOD_KEY || '-' || --
NVL(X_EXPENSE_ORGANIZATION_ID,-99) || '-' || --|
NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
NVL(X_USER_COL1,'X')|| '-' || --|
NVL(X_USER_COL2,'X')|| '-' || --|
NVL(X_USER_COL3,'X')|| '-' || --|
NVL(X_USER_COL4,'X')|| '-' || --|
NVL(X_USER_COL5,'X'), --
X_PROJECT_ID,
X_PA_PERIOD_KEY,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_EXPENSE_ORGANIZATION_ID,
X_OWNER_ORGANIZATION_ID,
X_RESOURCE_LIST_MEMBER_ID,
X_SERVICE_TYPE_CODE,
X_EXPENDITURE_TYPE,
X_USER_COL1,
X_USER_COL2,
X_USER_COL3,
X_USER_COL4,
X_USER_COL5,
X_USER_COL6,
X_USER_COL7,
X_USER_COL8,
X_USER_COL9,
X_USER_COL10,
X_ACCUME_REVENUE,
X_ACCUME_RAW_COST,
X_ACCUME_BURDENED_COST,
X_ACCUME_QUANTITY,
X_ACCUME_LABOR_HOURS,
X_ACCUME_BILLABLE_RAW_COST,
X_ACC_BILLABLE_BURDENED_COST,
X_ACCUME_BILLABLE_QUANTITY,
X_ACCUME_BILLABLE_LABOR_HOURS,
X_ACCUME_CMT_RAW_COST,
X_ACCUME_CMT_BURDENED_COST,
X_ACCUME_CMT_QUANTITY,
X_UNIT_OF_MEASURE,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
END update_prj_act_cmt;
SELECT
PBGT.TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_R_BGT_LINES_V PBGT,
PA_ADW_LOWEST_TASKS_V PT
WHERE
PBGT.TASK_ID = PT.TASK_ID
AND PBGT.PROJECT_ID = x_project_id
-- Exclude the tasks which are top tasks
AND PT.TASK_ID <> PT.TOP_TASK_ID
AND PBGT.ADW_NOTIFY_FLAG = 'R'
GROUP BY
PBGT.TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PBGT.TOP_TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_R_BGT_LINES_V PBGT,
PA_ADW_TOP_TASKS_V PT
WHERE
PBGT.TOP_TASK_ID = PT.TOP_TASK_ID
AND PBGT.PROJECT_ID = x_project_id
AND PBGT.ADW_NOTIFY_FLAG = 'R'
GROUP BY
PBGT.TOP_TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PBGT.PROJECT_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_R_BGT_LINES_V PBGT,
PA_ADW_PROJECTS_V PP
WHERE
PBGT.PROJECT_ID = PP.PROJECT_ID
AND PBGT.PROJECT_ID = x_project_id
AND PBGT.ADW_NOTIFY_FLAG = 'R'
GROUP BY
PBGT.PROJECT_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PBGT.TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_BGT_LINES_V PBGT,
PA_ADW_LOWEST_TASKS_V PT
WHERE
PBGT.TASK_ID = PT.TASK_ID
-- Exclude the tasks which are top tasks
AND PT.TASK_ID <> PT.TOP_TASK_ID
AND PBGT.PROJECT_ID = x_project_id
AND PBGT.ADW_NOTIFY_FLAG = 'S'
GROUP BY
PBGT.TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PBGT.TOP_TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_BGT_LINES_V PBGT,
PA_ADW_TOP_TASKS_V PT
WHERE
PBGT.TOP_TASK_ID = PT.TOP_TASK_ID
AND PBGT.PROJECT_ID = x_project_id
AND PBGT.ADW_NOTIFY_FLAG = 'S'
GROUP BY
PBGT.TOP_TASK_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PBGT.PROJECT_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
SUM(PBGT.USER_COL6) USER_COL6,
SUM(PBGT.USER_COL7) USER_COL7,
SUM(PBGT.USER_COL8) USER_COL8,
SUM(PBGT.USER_COL9) USER_COL9,
SUM(PBGT.USER_COL10) USER_COL10,
SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
PBGT.BGT_UNIT_OF_MEASURE
FROM
PA_ADW_BGT_LINES_V PBGT,
PA_ADW_PROJECTS_V PP
WHERE
PBGT.PROJECT_ID = PP.PROJECT_ID
AND PBGT.PROJECT_ID = x_project_id
AND PBGT.ADW_NOTIFY_FLAG = 'S'
GROUP BY
PBGT.PROJECT_ID,
PBGT.PA_PERIOD_KEY,
PBGT.BUDGET_TYPE_CODE,
PBGT.RESOURCE_LIST_MEMBER_ID,
PBGT.SERVICE_TYPE_CODE,
PBGT.OWNER_ORGANIZATION_ID,
PBGT.EXPENDITURE_TYPE,
PBGT.USER_COL1,
PBGT.USER_COL2,
PBGT.USER_COL3,
PBGT.USER_COL4,
PBGT.USER_COL5,
PBGT.BGT_UNIT_OF_MEASURE;
SELECT
PROJECT_ID,
SEGMENT1
FROM
PA_ADW_PROJECTS_V
WHERE segment1 BETWEEN NVL(x_project_num_from,segment1)
AND NVL(x_project_num_to,segment1);
UPDATE
PA_BUDGET_VERSIONS PBV
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
PROJECT_ID = SEL_PRJS_R.PROJECT_ID
AND CURRENT_FLAG = 'Y'
AND ADW_NOTIFY_FLAG = 'Y'
AND EXISTS
( SELECT 'Yes'
FROM
PA_ADW_BGT_TYPES_V PBT
WHERE PBT.BUDGET_TYPE_CODE = PBV.BUDGET_TYPE_CODE
);
UPDATE
PA_BUDGET_VERSIONS
SET
ADW_NOTIFY_FLAG = 'R'
WHERE
PROJECT_ID = sel_prjs_r.project_id
AND (BUDGET_TYPE_CODE,VERSION_NUMBER) IN
( SELECT
BUDGET_TYPE_CODE,
MAX(VERSION_NUMBER)
FROM
PA_BUDGET_VERSIONS OB
WHERE
OB.PROJECT_ID = sel_prjs_r.project_id
AND BUDGET_TYPE_CODE IN
( SELECT
BUDGET_TYPE_CODE
FROM
PA_ADW_BGT_TYPES_V
)
AND OB.ADW_NOTIFY_FLAG = 'N'
AND EXISTS
-- Check if a new budget was baselined, since the time the this budget was sent
( SELECT
'YES'
FROM
PA_BUDGET_VERSIONS NB
WHERE
NB.PROJECT_ID = sel_prjs_r.project_id
AND NB.BUDGET_TYPE_CODE = OB.BUDGET_TYPE_CODE
AND NB.ADW_NOTIFY_FLAG = 'S'
)
GROUP BY
OB.BUDGET_TYPE_CODE
);
update_tasks_budgets
(ref_lowest_budgets_r.task_id,
ref_lowest_budgets_r.pa_period_key,
ref_lowest_budgets_r.budget_type_code,
ref_lowest_budgets_r.resource_list_member_id,
ref_lowest_budgets_r.service_type_code,
ref_lowest_budgets_r.owner_organization_id,
ref_lowest_budgets_r.expenditure_type,
ref_lowest_budgets_r.user_col1,
ref_lowest_budgets_r.user_col2,
ref_lowest_budgets_r.user_col3,
ref_lowest_budgets_r.user_col4,
ref_lowest_budgets_r.user_col5,
ref_lowest_budgets_r.user_col6,
ref_lowest_budgets_r.user_col7,
ref_lowest_budgets_r.user_col8,
ref_lowest_budgets_r.user_col9,
ref_lowest_budgets_r.user_col10,
ref_lowest_budgets_r.bgt_revenue,
ref_lowest_budgets_r.bgt_raw_cost,
ref_lowest_budgets_r.bgt_burdened_cost,
ref_lowest_budgets_r.bgt_quantity,
ref_lowest_budgets_r.bgt_labor_quantity,
ref_lowest_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_budgets
(ref_top_budgets_r.top_task_id,
ref_top_budgets_r.pa_period_key,
ref_top_budgets_r.budget_type_code,
ref_top_budgets_r.resource_list_member_id,
ref_top_budgets_r.service_type_code,
ref_top_budgets_r.owner_organization_id,
ref_top_budgets_r.expenditure_type,
ref_top_budgets_r.user_col1,
ref_top_budgets_r.user_col2,
ref_top_budgets_r.user_col3,
ref_top_budgets_r.user_col4,
ref_top_budgets_r.user_col5,
ref_top_budgets_r.user_col6,
ref_top_budgets_r.user_col7,
ref_top_budgets_r.user_col8,
ref_top_budgets_r.user_col9,
ref_top_budgets_r.user_col10,
ref_top_budgets_r.bgt_revenue,
ref_top_budgets_r.bgt_raw_cost,
ref_top_budgets_r.bgt_burdened_cost,
ref_top_budgets_r.bgt_quantity,
ref_top_budgets_r.bgt_labor_quantity,
ref_top_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_prj_budgets
(ref_prj_budgets_r.project_id,
ref_prj_budgets_r.pa_period_key,
ref_prj_budgets_r.budget_type_code,
ref_prj_budgets_r.resource_list_member_id,
ref_prj_budgets_r.service_type_code,
ref_prj_budgets_r.owner_organization_id,
ref_prj_budgets_r.expenditure_type,
ref_prj_budgets_r.user_col1,
ref_prj_budgets_r.user_col2,
ref_prj_budgets_r.user_col3,
ref_prj_budgets_r.user_col4,
ref_prj_budgets_r.user_col5,
ref_prj_budgets_r.user_col6,
ref_prj_budgets_r.user_col7,
ref_prj_budgets_r.user_col8,
ref_prj_budgets_r.user_col9,
ref_prj_budgets_r.user_col10,
ref_prj_budgets_r.bgt_revenue,
ref_prj_budgets_r.bgt_raw_cost,
ref_prj_budgets_r.bgt_burdened_cost,
ref_prj_budgets_r.bgt_quantity,
ref_prj_budgets_r.bgt_labor_quantity,
ref_prj_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_budgets
(lowest_budgets_r.task_id,
lowest_budgets_r.pa_period_key,
lowest_budgets_r.budget_type_code,
lowest_budgets_r.resource_list_member_id,
lowest_budgets_r.service_type_code,
lowest_budgets_r.owner_organization_id,
lowest_budgets_r.expenditure_type,
lowest_budgets_r.user_col1,
lowest_budgets_r.user_col2,
lowest_budgets_r.user_col3,
lowest_budgets_r.user_col4,
lowest_budgets_r.user_col5,
lowest_budgets_r.user_col6,
lowest_budgets_r.user_col7,
lowest_budgets_r.user_col8,
lowest_budgets_r.user_col9,
lowest_budgets_r.user_col10,
lowest_budgets_r.bgt_revenue,
lowest_budgets_r.bgt_raw_cost,
lowest_budgets_r.bgt_burdened_cost,
lowest_budgets_r.bgt_quantity,
lowest_budgets_r.bgt_labor_quantity,
lowest_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_tasks_budgets
(top_budgets_r.top_task_id,
top_budgets_r.pa_period_key,
top_budgets_r.budget_type_code,
top_budgets_r.resource_list_member_id,
top_budgets_r.service_type_code,
top_budgets_r.owner_organization_id,
top_budgets_r.expenditure_type,
top_budgets_r.user_col1,
top_budgets_r.user_col2,
top_budgets_r.user_col3,
top_budgets_r.user_col4,
top_budgets_r.user_col5,
top_budgets_r.user_col6,
top_budgets_r.user_col7,
top_budgets_r.user_col8,
top_budgets_r.user_col9,
top_budgets_r.user_col10,
top_budgets_r.bgt_revenue,
top_budgets_r.bgt_raw_cost,
top_budgets_r.bgt_burdened_cost,
top_budgets_r.bgt_quantity,
top_budgets_r.bgt_labor_quantity,
top_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
update_prj_budgets
(prj_budgets_r.project_id,
prj_budgets_r.pa_period_key,
prj_budgets_r.budget_type_code,
prj_budgets_r.resource_list_member_id,
prj_budgets_r.service_type_code,
prj_budgets_r.owner_organization_id,
prj_budgets_r.expenditure_type,
prj_budgets_r.user_col1,
prj_budgets_r.user_col2,
prj_budgets_r.user_col3,
prj_budgets_r.user_col4,
prj_budgets_r.user_col5,
prj_budgets_r.user_col6,
prj_budgets_r.user_col7,
prj_budgets_r.user_col8,
prj_budgets_r.user_col9,
prj_budgets_r.user_col10,
prj_budgets_r.bgt_revenue,
prj_budgets_r.bgt_raw_cost,
prj_budgets_r.bgt_burdened_cost,
prj_budgets_r.bgt_quantity,
prj_budgets_r.bgt_labor_quantity,
prj_budgets_r.bgt_unit_of_measure,
x_err_stage,
x_err_stack,
x_err_code);
UPDATE
PA_BUDGET_VERSIONS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG IN ('S','R');
PROCEDURE update_tasks_budgets
(x_task_id IN NUMBER,
x_pa_period_key IN VARCHAR2,
x_budget_type_code IN VARCHAR2,
x_resource_list_member_id IN NUMBER,
x_service_type_code IN VARCHAR2,
x_owner_organization_id IN NUMBER,
x_expenditure_type IN VARCHAR2,
x_user_col1 IN VARCHAR2,
x_user_col2 IN VARCHAR2,
x_user_col3 IN VARCHAR2,
x_user_col4 IN VARCHAR2,
x_user_col5 IN VARCHAR2,
x_user_col6 IN VARCHAR2,
x_user_col7 IN VARCHAR2,
x_user_col8 IN VARCHAR2,
x_user_col9 IN VARCHAR2,
x_user_col10 IN VARCHAR2,
x_bgt_revenue IN NUMBER,
x_bgt_raw_cost IN NUMBER,
x_bgt_burdened_cost IN NUMBER,
x_bgt_quantity IN NUMBER,
x_bgt_labor_quantity IN NUMBER,
x_bgt_unit_of_measure IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
x_old_err_stack VARCHAR2(1024);
x_err_stack := x_err_stack || '-> update_tasks_budgets';
UPDATE
PA_TSK_BGT_LINES_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL6 = X_USER_COL6,
USER_COL7 = X_USER_COL7,
USER_COL8 = X_USER_COL8,
USER_COL9 = X_USER_COL9,
USER_COL10 = X_USER_COL10,
BGT_REVENUE = X_BGT_REVENUE,
BGT_RAW_COST = X_BGT_RAW_COST,
BGT_BURDENED_COST = X_BGT_BURDENED_COST,
BGT_QUANTITY = X_BGT_QUANTITY,
BGT_LABOR_QUANTITY = X_BGT_LABOR_QUANTITY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
STATUS_CODE = 'P'
WHERE
TASK_ID = X_TASK_ID
AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
AND BUDGET_TYPE_CODE = X_BUDGET_TYPE_CODE
AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
AND NVL(BGT_UNIT_OF_MEASURE,'X') = NVL(X_BGT_UNIT_OF_MEASURE,'X');
-- Check If Any row was updated
IF (SQL%ROWCOUNT = 0) THEN
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_TSK_BGT_LINES_IT
(
TASK_BUDGET_LINE_KEY,
TASK_ID,
PA_PERIOD_KEY,
BUDGET_TYPE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
OWNER_ORGANIZATION_ID,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
BGT_REVENUE,
BGT_RAW_COST,
BGT_BURDENED_COST,
BGT_QUANTITY,
BGT_LABOR_QUANTITY,
BGT_UNIT_OF_MEASURE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
X_TASK_ID || '-' || X_PA_PERIOD_KEY || '-' || --
NVL(X_BUDGET_TYPE_CODE,'X') || '-' || --|
NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
NVL(X_USER_COL1,'X')|| '-' || --|
NVL(X_USER_COL2,'X')|| '-' || --|
NVL(X_USER_COL3,'X')|| '-' || --|
NVL(X_USER_COL4,'X')|| '-' || --|
NVL(X_USER_COL5,'X'), --
X_TASK_ID,
X_PA_PERIOD_KEY,
X_BUDGET_TYPE_CODE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_RESOURCE_LIST_MEMBER_ID,
X_SERVICE_TYPE_CODE,
X_OWNER_ORGANIZATION_ID,
X_EXPENDITURE_TYPE,
X_USER_COL1,
X_USER_COL2,
X_USER_COL3,
X_USER_COL4,
X_USER_COL5,
X_USER_COL6,
X_USER_COL7,
X_USER_COL8,
X_USER_COL9,
X_USER_COL10,
X_BGT_REVENUE,
X_BGT_RAW_COST,
X_BGT_BURDENED_COST,
X_BGT_QUANTITY,
X_BGT_LABOR_QUANTITY,
X_BGT_UNIT_OF_MEASURE,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
END update_tasks_budgets;
PROCEDURE update_prj_budgets
(x_project_id IN NUMBER,
x_pa_period_key IN VARCHAR2,
x_budget_type_code IN VARCHAR2,
x_resource_list_member_id IN NUMBER,
x_service_type_code IN VARCHAR2,
x_owner_organization_id IN NUMBER,
x_expenditure_type IN VARCHAR2,
x_user_col1 IN VARCHAR2,
x_user_col2 IN VARCHAR2,
x_user_col3 IN VARCHAR2,
x_user_col4 IN VARCHAR2,
x_user_col5 IN VARCHAR2,
x_user_col6 IN VARCHAR2,
x_user_col7 IN VARCHAR2,
x_user_col8 IN VARCHAR2,
x_user_col9 IN VARCHAR2,
x_user_col10 IN VARCHAR2,
x_bgt_revenue IN NUMBER,
x_bgt_raw_cost IN NUMBER,
x_bgt_burdened_cost IN NUMBER,
x_bgt_quantity IN NUMBER,
x_bgt_labor_quantity IN NUMBER,
x_bgt_unit_of_measure IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
x_old_err_stack VARCHAR2(1024);
x_err_stack := x_err_stack || '-> update_prj_budgets';
UPDATE
PA_PRJ_BGT_LINES_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL6 = X_USER_COL6,
USER_COL7 = X_USER_COL7,
USER_COL8 = X_USER_COL8,
USER_COL9 = X_USER_COL9,
USER_COL10 = X_USER_COL10,
BGT_REVENUE = X_BGT_REVENUE,
BGT_RAW_COST = X_BGT_RAW_COST,
BGT_BURDENED_COST = X_BGT_BURDENED_COST,
BGT_QUANTITY = X_BGT_QUANTITY,
BGT_LABOR_QUANTITY = X_BGT_LABOR_QUANTITY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
STATUS_CODE = 'P'
WHERE
PROJECT_ID = X_PROJECT_ID
AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
AND BUDGET_TYPE_CODE = X_BUDGET_TYPE_CODE
AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
AND NVL(BGT_UNIT_OF_MEASURE,'X') = NVL(X_BGT_UNIT_OF_MEASURE,'X');
-- Check If Any row was updated
IF (SQL%ROWCOUNT = 0) THEN
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_PRJ_BGT_LINES_IT
(
PRJ_BUDGET_LINE_KEY,
PROJECT_ID,
PA_PERIOD_KEY,
BUDGET_TYPE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
RESOURCE_LIST_MEMBER_ID,
SERVICE_TYPE_CODE,
OWNER_ORGANIZATION_ID,
EXPENDITURE_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
BGT_REVENUE,
BGT_RAW_COST,
BGT_BURDENED_COST,
BGT_QUANTITY,
BGT_LABOR_QUANTITY,
BGT_UNIT_OF_MEASURE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
X_PROJECT_ID || '-' || X_PA_PERIOD_KEY || '-' || --
NVL(X_BUDGET_TYPE_CODE,'X') || '-' || --|
NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
NVL(X_USER_COL1,'X')|| '-' || --|
NVL(X_USER_COL2,'X')|| '-' || --|
NVL(X_USER_COL3,'X')|| '-' || --|
NVL(X_USER_COL4,'X')|| '-' || --|
NVL(X_USER_COL5,'X'), --
X_PROJECT_ID,
X_PA_PERIOD_KEY,
X_BUDGET_TYPE_CODE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_RESOURCE_LIST_MEMBER_ID,
X_SERVICE_TYPE_CODE,
X_OWNER_ORGANIZATION_ID,
X_EXPENDITURE_TYPE,
X_USER_COL1,
X_USER_COL2,
X_USER_COL3,
X_USER_COL4,
X_USER_COL5,
X_USER_COL6,
X_USER_COL7,
X_USER_COL8,
X_USER_COL9,
X_USER_COL10,
X_BGT_REVENUE,
X_BGT_RAW_COST,
X_BGT_BURDENED_COST,
X_BGT_QUANTITY,
X_BGT_LABOR_QUANTITY,
X_BGT_UNIT_OF_MEASURE,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
END update_prj_budgets;