The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
status_code
INTO
x_dimension_status
FROM
pa_adw_dimension_status
WHERE
dimension_code = x_dimension_code;
SELECT
TASK_ID,
TOP_TASK_ID,
TASK_NUMBER,
TASK_NAME,
DESCRIPTION,
CARRYING_OUT_ORGANIZATION_ID,
SERVICE_TYPE_CODE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_LOWEST_TASKS_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
SELECT
TOP_TASK_ID,
PROJECT_ID,
TASK_NUMBER,
TASK_NAME,
DESCRIPTION,
CARRYING_OUT_ORGANIZATION_ID,
SERVICE_TYPE_CODE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_TOP_TASKS_V
WHERE
ADW_NOTIFY_FLAG IN ('Y','S');
UPDATE
PA_LOWEST_TASKS_IT
SET
TOP_TASK_ID = LOWEST_TASKS_R.TOP_TASK_ID,
TASK_NUMBER = LOWEST_TASKS_R.TASK_NUMBER,
TASK_NAME = LOWEST_TASKS_R.TASK_NAME,
CARRYING_OUT_ORGANIZATION_ID = LOWEST_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
SERVICE_TYPE_CODE = LOWEST_TASKS_R.SERVICE_TYPE_CODE,
DESCRIPTION = LOWEST_TASKS_R.DESCRIPTION,
USER_COL1 = LOWEST_TASKS_R.USER_COL1,
USER_COL2 = LOWEST_TASKS_R.USER_COL2,
USER_COL3 = LOWEST_TASKS_R.USER_COL3,
USER_COL4 = LOWEST_TASKS_R.USER_COL4,
USER_COL5 = LOWEST_TASKS_R.USER_COL5,
USER_COL6 = LOWEST_TASKS_R.USER_COL6,
USER_COL7 = LOWEST_TASKS_R.USER_COL7,
USER_COL8 = LOWEST_TASKS_R.USER_COL8,
USER_COL9 = LOWEST_TASKS_R.USER_COL9,
USER_COL10 = LOWEST_TASKS_R.USER_COL10,
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 = LOWEST_TASKS_R.TASK_ID;
-- 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_LOWEST_TASKS_IT
(
TASK_ID,
TOP_TASK_ID,
TASK_NUMBER,
TASK_NAME,
CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SERVICE_TYPE_CODE,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
LOWEST_TASKS_R.TASK_ID,
LOWEST_TASKS_R.TOP_TASK_ID,
LOWEST_TASKS_R.TASK_NUMBER,
LOWEST_TASKS_R.TASK_NAME,
LOWEST_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
LOWEST_TASKS_R.SERVICE_TYPE_CODE,
LOWEST_TASKS_R.DESCRIPTION,
LOWEST_TASKS_R.USER_COL1,
LOWEST_TASKS_R.USER_COL2,
LOWEST_TASKS_R.USER_COL3,
LOWEST_TASKS_R.USER_COL4,
LOWEST_TASKS_R.USER_COL5,
LOWEST_TASKS_R.USER_COL6,
LOWEST_TASKS_R.USER_COL7,
LOWEST_TASKS_R.USER_COL8,
LOWEST_TASKS_R.USER_COL9,
LOWEST_TASKS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_TASKS
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
TASK_ID = LOWEST_TASKS_R.TASK_ID;
UPDATE
PA_TOP_TASKS_IT
SET
PROJECT_ID = TOP_TASKS_R.PROJECT_ID,
TASK_NUMBER = TOP_TASKS_R.TASK_NUMBER,
TASK_NAME = TOP_TASKS_R.TASK_NAME,
CARRYING_OUT_ORGANIZATION_ID = TOP_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
SERVICE_TYPE_CODE = TOP_TASKS_R.SERVICE_TYPE_CODE,
DESCRIPTION = TOP_TASKS_R.DESCRIPTION,
USER_COL1 = TOP_TASKS_R.USER_COL1,
USER_COL2 = TOP_TASKS_R.USER_COL2,
USER_COL3 = TOP_TASKS_R.USER_COL3,
USER_COL4 = TOP_TASKS_R.USER_COL4,
USER_COL5 = TOP_TASKS_R.USER_COL5,
USER_COL6 = TOP_TASKS_R.USER_COL6,
USER_COL7 = TOP_TASKS_R.USER_COL7,
USER_COL8 = TOP_TASKS_R.USER_COL8,
USER_COL9 = TOP_TASKS_R.USER_COL9,
USER_COL10 = TOP_TASKS_R.USER_COL10,
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
TOP_TASK_ID = TOP_TASKS_R.TOP_TASK_ID;
-- 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_TOP_TASKS_IT
(
TOP_TASK_ID,
PROJECT_ID,
TASK_NUMBER,
TASK_NAME,
CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SERVICE_TYPE_CODE,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
TOP_TASKS_R.TOP_TASK_ID,
TOP_TASKS_R.PROJECT_ID,
TOP_TASKS_R.TASK_NUMBER,
TOP_TASKS_R.TASK_NAME,
TOP_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
TOP_TASKS_R.SERVICE_TYPE_CODE,
TOP_TASKS_R.DESCRIPTION,
TOP_TASKS_R.USER_COL1,
TOP_TASKS_R.USER_COL2,
TOP_TASKS_R.USER_COL3,
TOP_TASKS_R.USER_COL4,
TOP_TASKS_R.USER_COL5,
TOP_TASKS_R.USER_COL6,
TOP_TASKS_R.USER_COL7,
TOP_TASKS_R.USER_COL8,
TOP_TASKS_R.USER_COL9,
TOP_TASKS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_TASKS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
TOP_TASK_ID = TOP_TASKS_R.TOP_TASK_ID;
UPDATE
PA_TASKS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
SELECT
PROJECT_ID,
PROJECT_TYPE,
NAME,
SEGMENT1,
CARRYING_OUT_ORGANIZATION_ID,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_PROJECTS_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_PROJECTS_IT
SET
PROJECT_TYPE = PROJECTS_R.PROJECT_TYPE,
SEGMENT1 = PROJECTS_R.SEGMENT1,
NAME = PROJECTS_R.NAME,
CARRYING_OUT_ORGANIZATION_ID = PROJECTS_R.CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
DESCRIPTION = PROJECTS_R.DESCRIPTION,
USER_COL1 = PROJECTS_R.USER_COL1,
USER_COL2 = PROJECTS_R.USER_COL2,
USER_COL3 = PROJECTS_R.USER_COL3,
USER_COL4 = PROJECTS_R.USER_COL4,
USER_COL5 = PROJECTS_R.USER_COL5,
USER_COL6 = PROJECTS_R.USER_COL6,
USER_COL7 = PROJECTS_R.USER_COL7,
USER_COL8 = PROJECTS_R.USER_COL8,
USER_COL9 = PROJECTS_R.USER_COL9,
USER_COL10 = PROJECTS_R.USER_COL10,
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 = PROJECTS_R.PROJECT_ID;
-- 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_PROJECTS_IT
(
PROJECT_ID,
PROJECT_TYPE,
SEGMENT1,
NAME,
CARRYING_OUT_ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
PROJECTS_R.PROJECT_ID,
PROJECTS_R.PROJECT_TYPE,
PROJECTS_R.SEGMENT1,
PROJECTS_R.NAME,
PROJECTS_R.CARRYING_OUT_ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
PROJECTS_R.DESCRIPTION,
PROJECTS_R.USER_COL1,
PROJECTS_R.USER_COL2,
PROJECTS_R.USER_COL3,
PROJECTS_R.USER_COL4,
PROJECTS_R.USER_COL5,
PROJECTS_R.USER_COL6,
PROJECTS_R.USER_COL7,
PROJECTS_R.USER_COL8,
PROJECTS_R.USER_COL9,
PROJECTS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_PROJECTS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
PROJECT_ID = PROJECTS_R.PROJECT_ID;
SELECT
PROJECT_TYPE,
DESCRIPTION,
ALL_PROJECT_TYPES,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_PRJ_TYPES_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_PRJ_TYPES_IT
SET
ALL_PROJECT_TYPES = PROJECT_TYPES_R.ALL_PROJECT_TYPES,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
DESCRIPTION = PROJECT_TYPES_R.DESCRIPTION,
USER_COL1 = PROJECT_TYPES_R.USER_COL1,
USER_COL2 = PROJECT_TYPES_R.USER_COL2,
USER_COL3 = PROJECT_TYPES_R.USER_COL3,
USER_COL4 = PROJECT_TYPES_R.USER_COL4,
USER_COL5 = PROJECT_TYPES_R.USER_COL5,
USER_COL6 = PROJECT_TYPES_R.USER_COL6,
USER_COL7 = PROJECT_TYPES_R.USER_COL7,
USER_COL8 = PROJECT_TYPES_R.USER_COL8,
USER_COL9 = PROJECT_TYPES_R.USER_COL9,
USER_COL10 = PROJECT_TYPES_R.USER_COL10,
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_TYPE = PROJECT_TYPES_R.PROJECT_TYPE;
-- 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_TYPES_IT
(
PROJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DESCRIPTION,
ALL_PROJECT_TYPES,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
PROJECT_TYPES_R.PROJECT_TYPE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
PROJECT_TYPES_R.DESCRIPTION,
PROJECT_TYPES_R.ALL_PROJECT_TYPES,
PROJECT_TYPES_R.USER_COL1,
PROJECT_TYPES_R.USER_COL2,
PROJECT_TYPES_R.USER_COL3,
PROJECT_TYPES_R.USER_COL4,
PROJECT_TYPES_R.USER_COL5,
PROJECT_TYPES_R.USER_COL6,
PROJECT_TYPES_R.USER_COL7,
PROJECT_TYPES_R.USER_COL8,
PROJECT_TYPES_R.USER_COL9,
PROJECT_TYPES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_PROJECT_TYPES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
PROJECT_TYPE = PROJECT_TYPES_R.PROJECT_TYPE;
SELECT COUNT(*)
INTO x_count
FROM PA_ALL_PRJ_TYPES_IT;
INSERT INTO PA_ALL_PRJ_TYPES_IT
(
ALL_PROJECT_TYPES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
SELECT DISTINCT
ALL_PROJECT_TYPES,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
FROM PA_ADW_PRJ_TYPES_V;
SELECT
EXPENDITURE_TYPE,
ALL_EXPENDITURE_TYPES,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY_CODE,
UNIT_OF_MEASURE,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_EXP_TYPES_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_EXP_TYPES_IT
SET
ALL_EXPENDITURE_TYPES = EXPENDITURE_TYPES_R.ALL_EXPENDITURE_TYPES,
EXPENDITURE_CATEGORY = EXPENDITURE_TYPES_R.EXPENDITURE_CATEGORY,
REVENUE_CATEGORY_CODE = EXPENDITURE_TYPES_R.REVENUE_CATEGORY_CODE,
UNIT_OF_MEASURE = EXPENDITURE_TYPES_R.UNIT_OF_MEASURE,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
DESCRIPTION = EXPENDITURE_TYPES_R.DESCRIPTION,
USER_COL1 = EXPENDITURE_TYPES_R.USER_COL1,
USER_COL2 = EXPENDITURE_TYPES_R.USER_COL2,
USER_COL3 = EXPENDITURE_TYPES_R.USER_COL3,
USER_COL4 = EXPENDITURE_TYPES_R.USER_COL4,
USER_COL5 = EXPENDITURE_TYPES_R.USER_COL5,
USER_COL6 = EXPENDITURE_TYPES_R.USER_COL6,
USER_COL7 = EXPENDITURE_TYPES_R.USER_COL7,
USER_COL8 = EXPENDITURE_TYPES_R.USER_COL8,
USER_COL9 = EXPENDITURE_TYPES_R.USER_COL9,
USER_COL10 = EXPENDITURE_TYPES_R.USER_COL10,
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
EXPENDITURE_TYPE = EXPENDITURE_TYPES_R.EXPENDITURE_TYPE;
-- 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_EXP_TYPES_IT
(
EXPENDITURE_TYPE,
ALL_EXPENDITURE_TYPES,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY_CODE,
UNIT_OF_MEASURE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
EXPENDITURE_TYPES_R.EXPENDITURE_TYPE,
EXPENDITURE_TYPES_R.ALL_EXPENDITURE_TYPES,
EXPENDITURE_TYPES_R.EXPENDITURE_CATEGORY,
EXPENDITURE_TYPES_R.REVENUE_CATEGORY_CODE,
EXPENDITURE_TYPES_R.UNIT_OF_MEASURE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
EXPENDITURE_TYPES_R.DESCRIPTION,
EXPENDITURE_TYPES_R.USER_COL1,
EXPENDITURE_TYPES_R.USER_COL2,
EXPENDITURE_TYPES_R.USER_COL3,
EXPENDITURE_TYPES_R.USER_COL4,
EXPENDITURE_TYPES_R.USER_COL5,
EXPENDITURE_TYPES_R.USER_COL6,
EXPENDITURE_TYPES_R.USER_COL7,
EXPENDITURE_TYPES_R.USER_COL8,
EXPENDITURE_TYPES_R.USER_COL9,
EXPENDITURE_TYPES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_EXPENDITURE_TYPES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
EXPENDITURE_TYPE = EXPENDITURE_TYPES_R.EXPENDITURE_TYPE;
SELECT COUNT(*)
INTO x_count
From PA_ALL_EXP_TYPES_IT;
INSERT INTO PA_ALL_EXP_TYPES_IT
(
ALL_EXPENDITURE_TYPES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
SELECT DISTINCT
ALL_EXPENDITURE_TYPES,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
FROM PA_ADW_EXP_TYPES_V;
SELECT
PROJECT_ID,
CLASS_CATEGORY,
CLASS_CODE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_PRJ_CLASSES_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_PRJ_CLASSES_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = PROJECT_CLASSES_R.USER_COL1,
USER_COL2 = PROJECT_CLASSES_R.USER_COL2,
USER_COL3 = PROJECT_CLASSES_R.USER_COL3,
USER_COL4 = PROJECT_CLASSES_R.USER_COL4,
USER_COL5 = PROJECT_CLASSES_R.USER_COL5,
USER_COL6 = PROJECT_CLASSES_R.USER_COL6,
USER_COL7 = PROJECT_CLASSES_R.USER_COL7,
USER_COL8 = PROJECT_CLASSES_R.USER_COL8,
USER_COL9 = PROJECT_CLASSES_R.USER_COL9,
USER_COL10 = PROJECT_CLASSES_R.USER_COL10,
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 = PROJECT_CLASSES_R.PROJECT_ID
AND CLASS_CATEGORY = PROJECT_CLASSES_R.CLASS_CATEGORY
AND CLASS_CODE = PROJECT_CLASSES_R.CLASS_CODE;
-- 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_CLASSES_IT
(
PROJECT_ID,
CLASS_CATEGORY,
CLASS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
PROJECT_CLASSES_R.PROJECT_ID,
PROJECT_CLASSES_R.CLASS_CATEGORY,
PROJECT_CLASSES_R.CLASS_CODE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
PROJECT_CLASSES_R.USER_COL1,
PROJECT_CLASSES_R.USER_COL2,
PROJECT_CLASSES_R.USER_COL3,
PROJECT_CLASSES_R.USER_COL4,
PROJECT_CLASSES_R.USER_COL5,
PROJECT_CLASSES_R.USER_COL6,
PROJECT_CLASSES_R.USER_COL7,
PROJECT_CLASSES_R.USER_COL8,
PROJECT_CLASSES_R.USER_COL9,
PROJECT_CLASSES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_PROJECT_CLASSES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
PROJECT_ID = PROJECT_CLASSES_R.PROJECT_ID
AND CLASS_CATEGORY = PROJECT_CLASSES_R.CLASS_CATEGORY
AND CLASS_CODE = PROJECT_CLASSES_R.CLASS_CODE;
SELECT
CLASS_CATEGORY,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_CLASS_CATGS_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_CLASS_CATGS_IT
SET
DESCRIPTION = CLASS_CATEGORIES_R.DESCRIPTION,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = CLASS_CATEGORIES_R.USER_COL1,
USER_COL2 = CLASS_CATEGORIES_R.USER_COL2,
USER_COL3 = CLASS_CATEGORIES_R.USER_COL3,
USER_COL4 = CLASS_CATEGORIES_R.USER_COL4,
USER_COL5 = CLASS_CATEGORIES_R.USER_COL5,
USER_COL6 = CLASS_CATEGORIES_R.USER_COL6,
USER_COL7 = CLASS_CATEGORIES_R.USER_COL7,
USER_COL8 = CLASS_CATEGORIES_R.USER_COL8,
USER_COL9 = CLASS_CATEGORIES_R.USER_COL9,
USER_COL10 = CLASS_CATEGORIES_R.USER_COL10,
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
CLASS_CATEGORY = CLASS_CATEGORIES_R.CLASS_CATEGORY;
-- 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_CLASS_CATGS_IT
(
CLASS_CATEGORY,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
CLASS_CATEGORIES_R.CLASS_CATEGORY,
CLASS_CATEGORIES_R.DESCRIPTION,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
CLASS_CATEGORIES_R.USER_COL1,
CLASS_CATEGORIES_R.USER_COL2,
CLASS_CATEGORIES_R.USER_COL3,
CLASS_CATEGORIES_R.USER_COL4,
CLASS_CATEGORIES_R.USER_COL5,
CLASS_CATEGORIES_R.USER_COL6,
CLASS_CATEGORIES_R.USER_COL7,
CLASS_CATEGORIES_R.USER_COL8,
CLASS_CATEGORIES_R.USER_COL9,
CLASS_CATEGORIES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_CLASS_CATEGORIES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
CLASS_CATEGORY = CLASS_CATEGORIES_R.CLASS_CATEGORY;
SELECT
CLASS_CATEGORY,
CLASS_CODE,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_CLASS_CODES_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_CLASS_CODES_IT
SET
DESCRIPTION = CLASS_CODES_R.DESCRIPTION,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = CLASS_CODES_R.USER_COL1,
USER_COL2 = CLASS_CODES_R.USER_COL2,
USER_COL3 = CLASS_CODES_R.USER_COL3,
USER_COL4 = CLASS_CODES_R.USER_COL4,
USER_COL5 = CLASS_CODES_R.USER_COL5,
USER_COL6 = CLASS_CODES_R.USER_COL6,
USER_COL7 = CLASS_CODES_R.USER_COL7,
USER_COL8 = CLASS_CODES_R.USER_COL8,
USER_COL9 = CLASS_CODES_R.USER_COL9,
USER_COL10 = CLASS_CODES_R.USER_COL10,
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
CLASS_CATEGORY = CLASS_CODES_R.CLASS_CATEGORY
AND CLASS_CODE = CLASS_CODES_R.CLASS_CODE;
-- 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_CLASS_CODES_IT
(
CLASS_CATEGORY,
CLASS_CODE,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
CLASS_CODES_R.CLASS_CATEGORY,
CLASS_CODES_R.CLASS_CODE,
CLASS_CODES_R.DESCRIPTION,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
CLASS_CODES_R.USER_COL1,
CLASS_CODES_R.USER_COL2,
CLASS_CODES_R.USER_COL3,
CLASS_CODES_R.USER_COL4,
CLASS_CODES_R.USER_COL5,
CLASS_CODES_R.USER_COL6,
CLASS_CODES_R.USER_COL7,
CLASS_CODES_R.USER_COL8,
CLASS_CODES_R.USER_COL9,
CLASS_CODES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_CLASS_CODES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
CLASS_CATEGORY = CLASS_CODES_R.CLASS_CATEGORY
AND CLASS_CODE = CLASS_CODES_R.CLASS_CODE;
SELECT
RESOURCE_LIST_MEMBER_ID,
PARENT_MEMBER_ID,
NAME,
ALIAS,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_LOWEST_RLMEM_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
SELECT
RESOURCE_LIST_MEMBER_ID,
RESOURCE_LIST_ID,
NAME,
ALIAS,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_TOP_RLMEM_V
WHERE
ADW_NOTIFY_FLAG IN ('Y','S');
UPDATE
PA_LOWEST_RLMEM_IT
SET
PARENT_MEMBER_ID = LOWEST_RES_MEMBERS_R.PARENT_MEMBER_ID,
NAME = LOWEST_RES_MEMBERS_R.NAME,
ALIAS = LOWEST_RES_MEMBERS_R.ALIAS,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = LOWEST_RES_MEMBERS_R.USER_COL1,
USER_COL2 = LOWEST_RES_MEMBERS_R.USER_COL2,
USER_COL3 = LOWEST_RES_MEMBERS_R.USER_COL3,
USER_COL4 = LOWEST_RES_MEMBERS_R.USER_COL4,
USER_COL5 = LOWEST_RES_MEMBERS_R.USER_COL5,
USER_COL6 = LOWEST_RES_MEMBERS_R.USER_COL6,
USER_COL7 = LOWEST_RES_MEMBERS_R.USER_COL7,
USER_COL8 = LOWEST_RES_MEMBERS_R.USER_COL8,
USER_COL9 = LOWEST_RES_MEMBERS_R.USER_COL9,
USER_COL10 = LOWEST_RES_MEMBERS_R.USER_COL10,
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
RESOURCE_LIST_MEMBER_ID = LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
-- 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_LOWEST_RLMEM_IT
(
RESOURCE_LIST_MEMBER_ID,
PARENT_MEMBER_ID,
NAME,
ALIAS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID,
LOWEST_RES_MEMBERS_R.PARENT_MEMBER_ID,
LOWEST_RES_MEMBERS_R.NAME,
LOWEST_RES_MEMBERS_R.ALIAS,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
LOWEST_RES_MEMBERS_R.USER_COL1,
LOWEST_RES_MEMBERS_R.USER_COL2,
LOWEST_RES_MEMBERS_R.USER_COL3,
LOWEST_RES_MEMBERS_R.USER_COL4,
LOWEST_RES_MEMBERS_R.USER_COL5,
LOWEST_RES_MEMBERS_R.USER_COL6,
LOWEST_RES_MEMBERS_R.USER_COL7,
LOWEST_RES_MEMBERS_R.USER_COL8,
LOWEST_RES_MEMBERS_R.USER_COL9,
LOWEST_RES_MEMBERS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_RESOURCE_LIST_MEMBERS
SET
ADW_NOTIFY_FLAG = 'S'
WHERE
RESOURCE_LIST_MEMBER_ID = LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
UPDATE
PA_TOP_RLMEM_IT
SET
RESOURCE_LIST_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_ID,
NAME = TOP_RES_MEMBERS_R.NAME,
ALIAS = TOP_RES_MEMBERS_R.ALIAS,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = TOP_RES_MEMBERS_R.USER_COL1,
USER_COL2 = TOP_RES_MEMBERS_R.USER_COL2,
USER_COL3 = TOP_RES_MEMBERS_R.USER_COL3,
USER_COL4 = TOP_RES_MEMBERS_R.USER_COL4,
USER_COL5 = TOP_RES_MEMBERS_R.USER_COL5,
USER_COL6 = TOP_RES_MEMBERS_R.USER_COL6,
USER_COL7 = TOP_RES_MEMBERS_R.USER_COL7,
USER_COL8 = TOP_RES_MEMBERS_R.USER_COL8,
USER_COL9 = TOP_RES_MEMBERS_R.USER_COL9,
USER_COL10 = TOP_RES_MEMBERS_R.USER_COL10,
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
RESOURCE_LIST_MEMBER_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
-- 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_TOP_RLMEM_IT
(
RESOURCE_LIST_MEMBER_ID,
RESOURCE_LIST_ID,
NAME,
ALIAS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID,
TOP_RES_MEMBERS_R.RESOURCE_LIST_ID,
TOP_RES_MEMBERS_R.NAME,
TOP_RES_MEMBERS_R.ALIAS,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
TOP_RES_MEMBERS_R.USER_COL1,
TOP_RES_MEMBERS_R.USER_COL2,
TOP_RES_MEMBERS_R.USER_COL3,
TOP_RES_MEMBERS_R.USER_COL4,
TOP_RES_MEMBERS_R.USER_COL5,
TOP_RES_MEMBERS_R.USER_COL6,
TOP_RES_MEMBERS_R.USER_COL7,
TOP_RES_MEMBERS_R.USER_COL8,
TOP_RES_MEMBERS_R.USER_COL9,
TOP_RES_MEMBERS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_RESOURCE_LIST_MEMBERS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
RESOURCE_LIST_MEMBER_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
UPDATE
PA_RESOURCE_LIST_MEMBERS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
ADW_NOTIFY_FLAG = 'S';
SELECT
RESOURCE_LIST_ID,
NAME,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_RES_LISTS_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_RES_LISTS_IT
SET
NAME = RESOURCE_LISTS_R.NAME,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
DESCRIPTION = RESOURCE_LISTS_R.DESCRIPTION,
USER_COL1 = RESOURCE_LISTS_R.USER_COL1,
USER_COL2 = RESOURCE_LISTS_R.USER_COL2,
USER_COL3 = RESOURCE_LISTS_R.USER_COL3,
USER_COL4 = RESOURCE_LISTS_R.USER_COL4,
USER_COL5 = RESOURCE_LISTS_R.USER_COL5,
USER_COL6 = RESOURCE_LISTS_R.USER_COL6,
USER_COL7 = RESOURCE_LISTS_R.USER_COL7,
USER_COL8 = RESOURCE_LISTS_R.USER_COL8,
USER_COL9 = RESOURCE_LISTS_R.USER_COL9,
USER_COL10 = RESOURCE_LISTS_R.USER_COL10,
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
RESOURCE_LIST_ID = RESOURCE_LISTS_R.RESOURCE_LIST_ID;
-- 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_RES_LISTS_IT
(
RESOURCE_LIST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
NAME,
DESCRIPTION,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE,
BUSINESS_GROUP_ID
)
VALUES
(
RESOURCE_LISTS_R.RESOURCE_LIST_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
RESOURCE_LISTS_R.NAME,
RESOURCE_LISTS_R.DESCRIPTION,
RESOURCE_LISTS_R.USER_COL1,
RESOURCE_LISTS_R.USER_COL2,
RESOURCE_LISTS_R.USER_COL3,
RESOURCE_LISTS_R.USER_COL4,
RESOURCE_LISTS_R.USER_COL5,
RESOURCE_LISTS_R.USER_COL6,
RESOURCE_LISTS_R.USER_COL7,
RESOURCE_LISTS_R.USER_COL8,
RESOURCE_LISTS_R.USER_COL9,
RESOURCE_LISTS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P',
PA_UTILS.BUSINESS_GROUP_ID
);
UPDATE
PA_RESOURCE_LISTS
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
RESOURCE_LIST_ID = RESOURCE_LISTS_R.RESOURCE_LIST_ID;
SELECT
BUDGET_TYPE_CODE,
BUDGET_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
ADW_NOTIFY_FLAG
FROM
PA_ADW_BGT_TYPES_V
WHERE
ADW_NOTIFY_FLAG = 'Y';
UPDATE
PA_BGT_TYPES_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
BUDGET_TYPE = BUDGET_TYPES_R.BUDGET_TYPE,
USER_COL1 = BUDGET_TYPES_R.USER_COL1,
USER_COL2 = BUDGET_TYPES_R.USER_COL2,
USER_COL3 = BUDGET_TYPES_R.USER_COL3,
USER_COL4 = BUDGET_TYPES_R.USER_COL4,
USER_COL5 = BUDGET_TYPES_R.USER_COL5,
USER_COL6 = BUDGET_TYPES_R.USER_COL6,
USER_COL7 = BUDGET_TYPES_R.USER_COL7,
USER_COL8 = BUDGET_TYPES_R.USER_COL8,
USER_COL9 = BUDGET_TYPES_R.USER_COL9,
USER_COL10 = BUDGET_TYPES_R.USER_COL10,
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
BUDGET_TYPE_CODE = BUDGET_TYPES_R.BUDGET_TYPE_CODE;
-- 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_BGT_TYPES_IT
(
BUDGET_TYPE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
BUDGET_TYPE,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
BUDGET_TYPES_R.BUDGET_TYPE_CODE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
BUDGET_TYPES_R.BUDGET_TYPE,
BUDGET_TYPES_R.USER_COL1,
BUDGET_TYPES_R.USER_COL2,
BUDGET_TYPES_R.USER_COL3,
BUDGET_TYPES_R.USER_COL4,
BUDGET_TYPES_R.USER_COL5,
BUDGET_TYPES_R.USER_COL6,
BUDGET_TYPES_R.USER_COL7,
BUDGET_TYPES_R.USER_COL8,
BUDGET_TYPES_R.USER_COL9,
BUDGET_TYPES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_BUDGET_TYPES
SET
ADW_NOTIFY_FLAG = 'N'
WHERE
BUDGET_TYPE_CODE = BUDGET_TYPES_R.BUDGET_TYPE_CODE;
SELECT
PA_PERIOD_KEY,
PA_PERIOD,
PA_PERIOD_START_DATE,
PA_PERIOD_END_DATE,
PA_PERIOD_END_DATE - PA_PERIOD_START_DATE + 1 TIMESPAN,
GL_PERIOD,
FINANCIAL_QUARTER,
FINANCIAL_YEAR,
ALL_FINANCIAL_YEARS,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_PERIODS_V;
SELECT DISTINCT
GL_PERIOD,
GL_PERIOD_START_DATE,
GL_PERIOD_END_DATE,
GL_PERIOD_END_DATE - GL_PERIOD_START_DATE + 1 TIMESPAN,
FINANCIAL_QUARTER
FROM
PA_ADW_PERIODS_V;
SELECT DISTINCT
FINANCIAL_QUARTER,
FINANCIAL_YEAR
FROM
PA_ADW_PERIODS_V;
SELECT DISTINCT
FINANCIAL_YEAR,
ALL_FINANCIAL_YEARS
FROM
PA_ADW_PERIODS_V;
UPDATE
PA_PERIODS_IT
SET
PA_PERIOD = PERIODS_R.PA_PERIOD,
START_DATE = PERIODS_R.PA_PERIOD_START_DATE,
END_DATE = PERIODS_R.PA_PERIOD_END_DATE,
TIMESPAN = PERIODS_R.TIMESPAN,
GL_PERIOD = PERIODS_R.GL_PERIOD,
FINANCIAL_QUARTER = PERIODS_R.FINANCIAL_QUARTER,
FINANCIAL_YEAR = PERIODS_R.FINANCIAL_YEAR,
ALL_FINANCIAL_YEARS = PERIODS_R.ALL_FINANCIAL_YEARS,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
USER_COL1 = PERIODS_R.USER_COL1,
USER_COL2 = PERIODS_R.USER_COL2,
USER_COL3 = PERIODS_R.USER_COL3,
USER_COL4 = PERIODS_R.USER_COL4,
USER_COL5 = PERIODS_R.USER_COL5,
USER_COL6 = PERIODS_R.USER_COL6,
USER_COL7 = PERIODS_R.USER_COL7,
USER_COL8 = PERIODS_R.USER_COL8,
USER_COL9 = PERIODS_R.USER_COL9,
USER_COL10 = PERIODS_R.USER_COL10,
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
PA_PERIOD_KEY = PERIODS_R.PA_PERIOD_KEY;
-- 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_PERIODS_IT
(
PA_PERIOD_KEY,
PA_PERIOD,
START_DATE,
END_DATE,
TIMESPAN,
GL_PERIOD,
FINANCIAL_QUARTER,
FINANCIAL_YEAR,
ALL_FINANCIAL_YEARS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
PERIODS_R.PA_PERIOD_KEY,
PERIODS_R.PA_PERIOD,
PERIODS_R.PA_PERIOD_START_DATE,
PERIODS_R.PA_PERIOD_END_DATE,
PERIODS_R.TIMESPAN,
PERIODS_R.GL_PERIOD,
PERIODS_R.FINANCIAL_QUARTER,
PERIODS_R.FINANCIAL_YEAR,
PERIODS_R.ALL_FINANCIAL_YEARS,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
PERIODS_R.USER_COL1,
PERIODS_R.USER_COL2,
PERIODS_R.USER_COL3,
PERIODS_R.USER_COL4,
PERIODS_R.USER_COL5,
PERIODS_R.USER_COL6,
PERIODS_R.USER_COL7,
PERIODS_R.USER_COL8,
PERIODS_R.USER_COL9,
PERIODS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_GL_PERIODS_IT
SET
START_DATE = PERIODS_GL.GL_PERIOD_START_DATE,
END_DATE = PERIODS_GL.GL_PERIOD_END_DATE,
TIMESPAN = PERIODS_GL.TIMESPAN,
FINANCIAL_QUARTER = PERIODS_GL.FINANCIAL_QUARTER,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
GL_PERIOD = PERIODS_GL.GL_PERIOD;
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_GL_PERIODS_IT
(
GL_PERIOD,
START_DATE,
END_DATE,
TIMESPAN,
FINANCIAL_QUARTER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
PERIODS_GL.GL_PERIOD,
PERIODS_GL.GL_PERIOD_START_DATE,
PERIODS_GL.GL_PERIOD_END_DATE,
PERIODS_GL.TIMESPAN,
PERIODS_GL.FINANCIAL_QUARTER,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
SELECT
MIN(GL_PERIOD_START_DATE),
MAX(GL_PERIOD_END_DATE)
INTO
x_start_date,
x_end_date
FROM
PA_ADW_PERIODS_V
WHERE
FINANCIAL_QUARTER = fin_qtr_r.FINANCIAL_QUARTER ;
UPDATE
PA_FINANCIAL_QTRS_IT
SET
START_DATE = x_start_date,
END_DATE = x_end_date ,
TIMESPAN = x_timespan,
FINANCIAL_YEAR = fin_qtr_r.FINANCIAL_YEAR,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
FINANCIAL_QUARTER = fin_qtr_r.FINANCIAL_QUARTER;
-- No row was updated, So Insert a new row into the interface table
Insert Into PA_FINANCIAL_QTRS_IT
(
FINANCIAL_QUARTER,
START_DATE,
END_DATE,
TIMESPAN,
FINANCIAL_YEAR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
fin_qtr_r.FINANCIAL_QUARTER,
x_start_date,
x_end_date,
x_timespan,
fin_qtr_r.FINANCIAL_YEAR,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
SELECT
MIN(GL_PERIOD_START_DATE),
MAX(GL_PERIOD_END_DATE)
INTO
x_start_date,
x_end_date
FROM
PA_ADW_PERIODS_V
WHERE
FINANCIAL_YEAR = fin_year_r.FINANCIAL_YEAR ;
UPDATE
PA_FINANCIAL_YRS_IT
SET
START_DATE = x_start_date,
END_DATE = x_end_date ,
TIMESPAN = x_timespan,
ALL_FINANCIAL_YEARS = fin_year_r.ALL_FINANCIAL_YEARS,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
FINANCIAL_YEAR = fin_year_r.FINANCIAL_YEAR;
-- No row was updated, So Insert a new row into the interface table
Insert Into PA_FINANCIAL_YRS_IT
(
FINANCIAL_YEAR,
START_DATE,
END_DATE,
TIMESPAN,
ALL_FINANCIAL_YEARS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
Values
(
fin_year_r.FINANCIAL_YEAR,
x_start_date,
x_end_date,
x_timespan,
fin_year_r.ALL_FINANCIAL_YEARS,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
SELECT
COUNT(*)
INTO x_count
FROM
PA_ALL_FINANCIAL_YRS_IT;
INSERT INTO PA_ALL_FINANCIAL_YRS_IT
(
ALL_FINANCIAL_YEARS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
SELECT DISTINCT
ALL_FINANCIAL_YEARS,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
FROM
PA_ADW_PERIODS_V;
SELECT
SERVICE_TYPE_CODE,
SERVICE_TYPE,
ALL_SERVICE_TYPES,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_SRVC_TYPES_V;
UPDATE
PA_SRVC_TYPES_IT
SET
ALL_SERVICE_TYPES = SERVICE_TYPES_R.ALL_SERVICE_TYPES,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
SERVICE_TYPE = SERVICE_TYPES_R.SERVICE_TYPE,
USER_COL1 = SERVICE_TYPES_R.USER_COL1,
USER_COL2 = SERVICE_TYPES_R.USER_COL2,
USER_COL3 = SERVICE_TYPES_R.USER_COL3,
USER_COL4 = SERVICE_TYPES_R.USER_COL4,
USER_COL5 = SERVICE_TYPES_R.USER_COL5,
USER_COL6 = SERVICE_TYPES_R.USER_COL6,
USER_COL7 = SERVICE_TYPES_R.USER_COL7,
USER_COL8 = SERVICE_TYPES_R.USER_COL8,
USER_COL9 = SERVICE_TYPES_R.USER_COL9,
USER_COL10 = SERVICE_TYPES_R.USER_COL10,
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
SERVICE_TYPE_CODE = SERVICE_TYPES_R.SERVICE_TYPE_CODE;
-- 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_SRVC_TYPES_IT
(
SERVICE_TYPE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SERVICE_TYPE,
ALL_SERVICE_TYPES,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
SERVICE_TYPES_R.SERVICE_TYPE_CODE,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
SERVICE_TYPES_R.SERVICE_TYPE,
SERVICE_TYPES_R.ALL_SERVICE_TYPES,
SERVICE_TYPES_R.USER_COL1,
SERVICE_TYPES_R.USER_COL2,
SERVICE_TYPES_R.USER_COL3,
SERVICE_TYPES_R.USER_COL4,
SERVICE_TYPES_R.USER_COL5,
SERVICE_TYPES_R.USER_COL6,
SERVICE_TYPES_R.USER_COL7,
SERVICE_TYPES_R.USER_COL8,
SERVICE_TYPES_R.USER_COL9,
SERVICE_TYPES_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
SELECT count(*)
into x_count
FROM PA_ALL_SRVC_TYPES_IT;
INSERT INTO PA_ALL_SRVC_TYPES_IT
(
ALL_SERVICE_TYPES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
SELECT DISTINCT
ALL_SERVICE_TYPES,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
FROM PA_ADW_SRVC_TYPES_V;
SELECT
ORGANIZATION_ID,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_ORGS_V;
SELECT distinct
BUSINESS_GROUP
FROM
PA_ADW_ORGS_V;
SELECT distinct
BUSINESS_GROUP
FROM
PA_ADW_ORGS_V;
SELECT
ORGANIZATION_ID,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_ORGS_V;
SELECT
ORGANIZATION_ID,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_ORGS_V;
SELECT
ORGANIZATION_ID,
ORGANIZATION,
LEGAL_ENTITY,
SET_OF_BOOK,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10
FROM
PA_ADW_OPER_UNITS_V;
SELECT distinct
LEGAL_ENTITY,
SET_OF_BOOK
FROM
PA_ADW_OPER_UNITS_V;
SELECT distinct
SET_OF_BOOK
FROM
PA_ADW_OPER_UNITS_V;
UPDATE
PA_PRJ_ORGS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
ORGANIZATION = project_org_r.ORGANIZATION,
BUSINESS_GROUP = project_org_r.BUSINESS_GROUP,
USER_COL1 = project_org_r.USER_COL1,
USER_COL2 = project_org_r.USER_COL2,
USER_COL3 = project_org_r.USER_COL3,
USER_COL4 = project_org_r.USER_COL4,
USER_COL5 = project_org_r.USER_COL5,
USER_COL6 = project_org_r.USER_COL6,
USER_COL7 = project_org_r.USER_COL7,
USER_COL8 = project_org_r.USER_COL8,
USER_COL9 = project_org_r.USER_COL9,
USER_COL10 = project_org_r.USER_COL10,
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
ORGANIZATION_ID = project_org_r.ORGANIZATION_ID;
-- 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_ORGS_IT
(
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
project_org_r.ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
project_org_r.ORGANIZATION,
project_org_r.BUSINESS_GROUP,
project_org_r.USER_COL1,
project_org_r.USER_COL2,
project_org_r.USER_COL3,
project_org_r.USER_COL4,
project_org_r.USER_COL5,
project_org_r.USER_COL6,
project_org_r.USER_COL7,
project_org_r.USER_COL8,
project_org_r.USER_COL9,
project_org_r.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_PRJ_BUSINESS_GRPS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
BUSINESS_GROUP = project_bsns_grp_r.BUSINESS_GROUP;
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_PRJ_BUSINESS_GRPS_IT
(
business_group,
last_update_date,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
project_bsns_grp_r.business_group,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_ORGS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
ORGANIZATION = ORGANIZATIONS_R.ORGANIZATION,
BUSINESS_GROUP = ORGANIZATIONS_R.BUSINESS_GROUP,
USER_COL1 = ORGANIZATIONS_R.USER_COL1,
USER_COL2 = ORGANIZATIONS_R.USER_COL2,
USER_COL3 = ORGANIZATIONS_R.USER_COL3,
USER_COL4 = ORGANIZATIONS_R.USER_COL4,
USER_COL5 = ORGANIZATIONS_R.USER_COL5,
USER_COL6 = ORGANIZATIONS_R.USER_COL6,
USER_COL7 = ORGANIZATIONS_R.USER_COL7,
USER_COL8 = ORGANIZATIONS_R.USER_COL8,
USER_COL9 = ORGANIZATIONS_R.USER_COL9,
USER_COL10 = ORGANIZATIONS_R.USER_COL10,
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
ORGANIZATION_ID = ORGANIZATIONS_R.ORGANIZATION_ID;
-- 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_ORGS_IT
(
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
ORGANIZATIONS_R.ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
ORGANIZATIONS_R.ORGANIZATION,
ORGANIZATIONS_R.BUSINESS_GROUP,
ORGANIZATIONS_R.USER_COL1,
ORGANIZATIONS_R.USER_COL2,
ORGANIZATIONS_R.USER_COL3,
ORGANIZATIONS_R.USER_COL4,
ORGANIZATIONS_R.USER_COL5,
ORGANIZATIONS_R.USER_COL6,
ORGANIZATIONS_R.USER_COL7,
ORGANIZATIONS_R.USER_COL8,
ORGANIZATIONS_R.USER_COL9,
ORGANIZATIONS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_EXP_ORGS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
ORGANIZATION = exp_org_r.ORGANIZATION,
BUSINESS_GROUP = exp_org_r.BUSINESS_GROUP,
USER_COL1 = exp_org_r.USER_COL1,
USER_COL2 = exp_org_r.USER_COL2,
USER_COL3 = exp_org_r.USER_COL3,
USER_COL4 = exp_org_r.USER_COL4,
USER_COL5 = exp_org_r.USER_COL5,
USER_COL6 = exp_org_r.USER_COL6,
USER_COL7 = exp_org_r.USER_COL7,
USER_COL8 = exp_org_r.USER_COL8,
USER_COL9 = exp_org_r.USER_COL9,
USER_COL10 = exp_org_r.USER_COL10,
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
ORGANIZATION_ID = exp_org_r.ORGANIZATION_ID;
-- 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_EXP_ORGS_IT
(
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORGANIZATION,
BUSINESS_GROUP,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
exp_org_r.ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
exp_org_r.ORGANIZATION,
exp_org_r.BUSINESS_GROUP,
exp_org_r.USER_COL1,
exp_org_r.USER_COL2,
exp_org_r.USER_COL3,
exp_org_r.USER_COL4,
exp_org_r.USER_COL5,
exp_org_r.USER_COL6,
exp_org_r.USER_COL7,
exp_org_r.USER_COL8,
exp_org_r.USER_COL9,
exp_org_r.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_EXP_BUSINESS_GRPS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
BUSINESS_GROUP = exp_bsns_grp_r.BUSINESS_GROUP;
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_EXP_BUSINESS_GRPS_IT
(
BUSINESS_GROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
exp_bsns_grp_r.business_group,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_OPER_UNITS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
ORGANIZATION = OPERATING_UNITS_R.ORGANIZATION,
LEGAL_ENTITY = OPERATING_UNITS_R.LEGAL_ENTITY,
SET_OF_BOOK = OPERATING_UNITS_R.SET_OF_BOOK,
USER_COL1 = OPERATING_UNITS_R.USER_COL1,
USER_COL2 = OPERATING_UNITS_R.USER_COL2,
USER_COL3 = OPERATING_UNITS_R.USER_COL3,
USER_COL4 = OPERATING_UNITS_R.USER_COL4,
USER_COL5 = OPERATING_UNITS_R.USER_COL5,
USER_COL6 = OPERATING_UNITS_R.USER_COL6,
USER_COL7 = OPERATING_UNITS_R.USER_COL7,
USER_COL8 = OPERATING_UNITS_R.USER_COL8,
USER_COL9 = OPERATING_UNITS_R.USER_COL9,
USER_COL10 = OPERATING_UNITS_R.USER_COL10,
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
ORGANIZATION_ID = OPERATING_UNITS_R.ORGANIZATION_ID;
-- 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_OPER_UNITS_IT
(
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORGANIZATION,
LEGAL_ENTITY,
SET_OF_BOOK,
USER_COL1,
USER_COL2,
USER_COL3,
USER_COL4,
USER_COL5,
USER_COL6,
USER_COL7,
USER_COL8,
USER_COL9,
USER_COL10,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
OPERATING_UNITS_R.ORGANIZATION_ID,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
OPERATING_UNITS_R.ORGANIZATION,
OPERATING_UNITS_R.LEGAL_ENTITY,
OPERATING_UNITS_R.SET_OF_BOOK,
OPERATING_UNITS_R.USER_COL1,
OPERATING_UNITS_R.USER_COL2,
OPERATING_UNITS_R.USER_COL3,
OPERATING_UNITS_R.USER_COL4,
OPERATING_UNITS_R.USER_COL5,
OPERATING_UNITS_R.USER_COL6,
OPERATING_UNITS_R.USER_COL7,
OPERATING_UNITS_R.USER_COL8,
OPERATING_UNITS_R.USER_COL9,
OPERATING_UNITS_R.USER_COL10,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_SET_OF_BOOKS_IT
SET
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
SET_OF_BOOK = set_of_book_r.SET_OF_BOOK ;
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_SET_OF_BOOKS_IT
(
SET_OF_BOOK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
SET_OF_BOOK_R.SET_OF_BOOK,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);
UPDATE
PA_LEGAL_ENTITY_IT
SET
SET_OF_BOOK = legal_entity_r.SET_OF_BOOK,
LAST_UPDATE_DATE = TRUNC(SYSDATE),
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
CREATION_DATE = TRUNC(SYSDATE),
CREATED_BY = X_CREATED_BY,
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
LEGAL_ENTITY = legal_entity_r.LEGAL_ENTITY ;
-- No row was updated, So Insert a new row into the interface table
INSERT INTO PA_LEGAL_ENTITY_IT
(
LEGAL_ENTITY,
SET_OF_BOOK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_CODE
)
VALUES
(
LEGAL_ENTITY_R.LEGAL_ENTITY,
LEGAL_ENTITY_R.SET_OF_BOOK,
TRUNC(SYSDATE),
X_LAST_UPDATED_BY,
TRUNC(SYSDATE),
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
TRUNC(SYSDATE),
'P'
);