[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id = x_resource_list_assignment_id
AND prm.resource_list_id = x_resource_list_id
AND prm.expenditure_type = x_expenditure_type
AND prm.organization_id = x_organization_id
AND prm.person_id = x_person_id
AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id = x_resource_list_assignment_id
AND prm.resource_list_id = x_resource_list_id
AND prm.expenditure_type = x_expenditure_type
AND prm.organization_id = x_organization_id
AND prm.person_id IS NULL
AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id = x_resource_list_assignment_id
AND prm.resource_list_id = x_resource_list_id
AND prm.event_type = x_event_type
AND prm.organization_id = x_organization_id
AND prm.revenue_category = x_revenue_category
AND prm.event_type_classification = x_event_type_classification;
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm,
pa_resource_list_assignments parla
WHERE
prm.resource_list_id = x_resource_list_id
AND prm.expenditure_type = x_expenditure_type
AND prm.organization_id = x_organization_id
AND prm.person_id = x_person_id
AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X')
AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
AND rownum < 2;
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm,
pa_resource_list_assignments parla
WHERE
prm.resource_list_id = x_resource_list_id
AND prm.expenditure_type = x_expenditure_type
AND prm.organization_id = x_organization_id
AND prm.person_id IS NULL
AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X')
AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
AND rownum < 2;
SELECT
resource_list_member_id,
resource_id
INTO
x_resource_list_member_id,
x_resource_id
FROM
pa_resource_maps prm,
pa_resource_list_assignments parla
WHERE
prm.resource_list_id = x_resource_list_id
AND prm.event_type = x_event_type
AND prm.organization_id = x_organization_id
AND prm.revenue_category = x_revenue_category
AND prm.event_type_classification = x_event_type_classification
AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
AND rownum < 2;
PROCEDURE delete_res_maps_on_asgn_id
(x_resource_list_assignment_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
tot_recs_processed number(15):=0;
DELETE
pa_resource_maps where rownum <= pa_proj_accum_main.x_commit_size;
DELETE
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id = x_resource_list_assignment_id
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
END delete_res_maps_on_asgn_id;
PROCEDURE delete_res_maps_on_prj_id
(x_project_id IN NUMBER,
x_resource_list_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
tot_recs_processed number(15):=0;
DELETE
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id IN
( SELECT
resource_list_assignment_id
FROM
pa_resource_list_assignments
WHERE project_id = x_project_id
AND resource_list_id = NVL(x_resource_list_id,resource_list_id)
)
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
END delete_res_maps_on_prj_id;
INSERT INTO pa_resource_maps
(resource_list_id,
resource_list_assignment_id,
resource_list_member_id,
resource_id,
person_id,
job_id,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource,
expenditure_category,
revenue_category,
non_labor_resource_org_id,
event_type_classification,
system_linkage_function,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id)
VALUES
(x_resource_list_id,
x_resource_list_assignment_id,
x_resource_list_member_id,
x_resource_id,
x_person_id,
x_job_id,
x_organization_id,
x_vendor_id,
x_expenditure_type,
x_event_type,
x_non_labor_resource,
x_expenditure_category,
x_revenue_category,
x_non_labor_resource_org_id,
x_event_type_classification,
x_system_linkage_function,
SYSDATE,
x_created_by,
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id);
UPDATE
pa_resource_list_assignments
SET
resource_list_changed_flag ='N'
WHERE
resource_list_assignment_id = x_resource_list_assignment_id;
SELECT
NVL(resource_list_changed_flag,'N')
INTO
x_resource_list_changed_flag
FROM
pa_resource_list_assignments
WHERE
resource_list_assignment_id = x_resource_list_assignment_id;
SELECT
rank
INTO
x_rank
FROM
pa_resource_format_ranks
WHERE
resource_format_id = x_resource_format_id
AND txn_class_code = x_txn_class_code;
SELECT
rt.resource_type_code
INTO
x_group_resource_type_code
FROM
pa_resource_types rt,
pa_resource_lists_all_bg rl
WHERE
rl.resource_list_id = x_resource_list_id
and nvl(rl.migration_code,'-99') <> 'N'
AND rl.group_resource_type_id = rt.resource_type_id
;
INSERT INTO pa_resource_accum_details
(resource_list_id,
resource_list_assignment_id,
resource_list_member_id,
resource_id,
txn_accum_id,
project_id,
task_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id)
SELECT
x_resource_list_id,
x_resource_list_assignment_id,
x_resource_list_member_id,
x_resource_id,
x_txn_accum_id,
x_project_id,
x_task_id,
SYSDATE,
x_created_by,
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id
FROM
sys.dual
WHERE NOT EXISTS
(SELECT
'Yes'
FROM
pa_resource_accum_details rad
WHERE
resource_list_id = x_resource_list_id
AND resource_list_assignment_id = x_resource_list_assignment_id
/*
AND resource_list_member_id = x_resource_list_member_id
AND resource_id = x_resource_id
*/
AND txn_accum_id = x_txn_accum_id
AND project_id = x_project_id
AND task_id = x_task_id
);
PROCEDURE delete_resource_accum_details
(x_resource_list_assignment_id IN NUMBER,
x_resource_list_id IN NUMBER,
x_project_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
tot_recs_processed number(15):=0;
DELETE
pa_resource_accum_details
WHERE
resource_list_assignment_id =
NVL(x_resource_list_assignment_id,resource_list_assignment_id)
AND project_id = x_project_id
and rownum <= pa_proj_accum_main.x_commit_size;
DELETE
pa_resource_accum_details
WHERE
resource_list_assignment_id =
NVL(x_resource_list_assignment_id,resource_list_assignment_id)
AND resource_list_id = x_resource_list_id
AND project_id = x_project_id
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
END delete_resource_accum_details;
SELECT
rla.resource_list_assignment_id,
rl.resource_list_id,
rlm.resource_list_member_id,
rlm.resource_id,
rlm.member_level,
rta.person_id,
rta.job_id,
rta.organization_id,
rta.vendor_id,
rta.expenditure_type,
rta.event_type,
rta.non_labor_resource,
rta.expenditure_category,
rta.revenue_category,
rta.non_labor_resource_org_id,
rta.event_type_classification,
rta.system_linkage_function,
rta.resource_format_id,
rt.resource_type_code
, rl.job_group_id
FROM
pa_resource_lists_all_bg rl,
pa_resource_list_members rlm,
pa_resource_txn_attributes rta,
pa_resources r,
pa_resource_types rt,
pa_resource_list_assignments rla
WHERE
rlm.resource_list_id = rl.resource_list_id
AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
and nvl(rl.migration_code,'-99') <> 'N'
and nvl(rlm.migration_code,'-99') <> 'N'
AND NVL(rlm.parent_member_id,0) = 0
AND rlm.enabled_flag = 'Y'
AND rlm.resource_id = rta.resource_id(+) --- rta may not available for resource
AND r.resource_id = rlm.resource_id
AND rt.resource_type_id = r.resource_type_id
AND rla.resource_list_id = rl.resource_list_id
AND rla.project_id = x_project_id
AND NOT EXISTS
( SELECT
'Yes'
FROM
pa_resource_list_members rlmc
WHERE
rlmc.parent_member_id = rlm.resource_list_member_id
and nvl(rlmc.migration_code,'-99') <> 'N'
AND rlmc.enabled_flag = 'Y'
)
UNION
SELECT
rla.resource_list_assignment_id,
rl.resource_list_id,
rlmc.resource_list_member_id,
rlmc.resource_id,
rlmc.member_level,
NVL(rtac.person_id,rtap.person_id),
NVL(rtac.job_id,rtap.job_id),
NVL(rtac.organization_id,rtap.organization_id),
NVL(rtac.vendor_id,rtap.vendor_id),
NVL(rtac.expenditure_type,rtap.expenditure_type),
NVL(rtac.event_type,rtap.event_type),
NVL(rtac.non_labor_resource,rtap.non_labor_resource),
NVL(rtac.expenditure_category,rtap.expenditure_category),
NVL(rtac.revenue_category,rtap.revenue_category),
NVL(rtac.non_labor_resource_org_id,rtap.non_labor_resource_org_id),
NVL(rtac.event_type_classification,rtap.event_type_classification),
NVL(rtac.system_linkage_function,rtap.system_linkage_function),
rtac.resource_format_id,
rtc.resource_type_code
, rl.job_group_id
FROM
pa_resource_lists_all_bg rl,
pa_resource_list_members rlmc,
pa_resource_list_members rlmp,
pa_resource_txn_attributes rtac,
pa_resource_txn_attributes rtap,
pa_resources rc,
pa_resource_types rtc,
pa_resource_list_assignments rla
WHERE
rlmc.resource_list_id = rl.resource_list_id
and nvl(rl.migration_code,'-99') <> 'N'
and nvl(rlmc.migration_code,'-99') <> 'N'
and nvl(rlmp.migration_code,'-99') <> 'N'
AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
AND rlmc.enabled_flag = 'Y'
AND rlmc.resource_id = rtac.resource_id(+) --- rta may not available for resource
AND rlmc.parent_member_id = rlmp.resource_list_member_id
AND rlmp.enabled_flag = 'Y'
AND rlmp.resource_id = rtap.resource_id(+) --- rta may not available for resource
AND rc.resource_id = rlmc.resource_id
AND rtc.resource_type_id = rc.resource_type_id
AND rla.resource_list_id = rl.resource_list_id
AND rla.project_id = x_project_id
/* The next order by is very impotant.
Ordering the resource by resource_list_assignment_id, resource_list_id */
ORDER BY 1,2;
SELECT
pta.txn_accum_id,
pta.project_id,
pta.task_id,
pta.person_id,
pta.job_id,
pta.organization_id,
pta.vendor_id,
pta.expenditure_type,
pta.event_type,
pta.non_labor_resource,
pta.expenditure_category,
pta.revenue_category,
pta.non_labor_resource_org_id,
pta.event_type_classification,
pta.system_linkage_function
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND ((pta.actual_cost_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.actual_cost_rollup_flag,
pta.actual_cost_rollup_flag))
OR
(pta.revenue_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.revenue_rollup_flag,
pta.revenue_rollup_flag))
OR
(pta.cmt_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.cmt_rollup_flag,
pta.cmt_rollup_flag)))
AND EXISTS
( SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
);
delete_res_maps_on_asgn_id(current_rl_assignment_id,x_err_stage,x_err_code);
SELECT prla.resource_list_id
,prla.resource_list_assignment_id
FROM pa_resource_list_assignments prla,
pa_resource_lists_all_bg res
WHERE prla.resource_list_id = nvl(x_res_list_id,prla.resource_list_id)
AND prla.project_id = x_project_id
AND res.resource_list_id = prla.resource_list_id
AND NVL(res.MIGRATION_CODE,'-99') <> 'N';
result in NULL insert into PA_RESOURCE_ACCUM_DETAILS. This cursor is called
only during NULL insert exception (resource_id and resource_list_member_id can
be NULL if MAP_TXNS has failed to derive the same) */
CURSOR C2 IS
SELECT resource_id,
resource_list_member_id,
person_id,
job_id,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource,
expenditure_category,
revenue_category,
non_labor_resource_org_id,
event_type_classification,
system_linkage_function,
system_reference1 txn_accum_id,
system_reference2 project_id,
system_reference3 task_id
FROM PA_MAPPABLE_TXNS_TMP pmt
WHERE NOT EXISTS
(SELECT 'Yes'
FROM pa_resource_accum_details rad
WHERE resource_list_id = l_resource_list_id
AND resource_list_assignment_id = l_resource_list_assignment_id
AND txn_accum_id = pmt.system_reference1
AND project_id = pmt.system_reference2
AND task_id = pmt.system_reference3)
AND (pmt.resource_list_member_id is null OR
pmt.resource_id is null);
delete from pa_mappable_txns_tmp;
x_err_stage := 'Inserting into pa_mappable_txns_tmp';
/* Bug 5552602/ 5571792: Split the insert based on x_mode = I (Incremental-Update Process) or F (Full-Refresh process)*/
If nvl(x_mode,'F') = 'I' then
INSERT INTO PA_MAPPABLE_TXNS_TMP (
txn_id,
person_id,
job_id,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource,
expenditure_category,
revenue_category,
non_labor_resource_org_id,
event_type_classification,
system_linkage_function,
project_role_id,
resource_list_id,
system_reference1,
system_reference2,
system_reference3
)
SELECT
pa_mappable_txns_tmp_s.NEXTVAL,
pta.person_id,
pta.job_id,
pta.organization_id,
pta.vendor_id,
pta.expenditure_type,
pta.event_type,
pta.non_labor_resource,
pta.expenditure_category,
pta.revenue_category,
pta.non_labor_resource_org_id,
pta.event_type_classification,
pta.system_linkage_function,
NULL, /* Project role id is not there on pa_txn_accum */
l_resource_list_id,
pta.txn_accum_id, /* To identify our records back */
pta.project_id, /* This will avoid joining to pa_txn_accum again during insertion */
pta.task_id /* pa_resource_accum_details table */
FROM pa_txn_accum pta
WHERE pta.project_id = x_project_id
AND ((pta.actual_cost_rollup_flag = 'Y') OR
(pta.revenue_rollup_flag = 'Y') OR
(pta.cmt_rollup_flag = 'Y') )
/* 5571792 AND ((pta.actual_cost_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.actual_cost_rollup_flag,
pta.actual_cost_rollup_flag)) OR
(pta.revenue_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.revenue_rollup_flag,
pta.revenue_rollup_flag)) OR
(pta.cmt_rollup_flag = DECODE(x_mode,'I','Y',
'F',pta.cmt_rollup_flag,
pta.cmt_rollup_flag))) 5571792 */
AND EXISTS
(SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
/* following not exists will be valid even in case of refresh ( x_mode = 'F' )
because from refresh process we call map_txns only after we have
deleted records from pa_resource_accum_details table */
AND NOT EXISTS
(SELECT 'Yes'
FROM pa_resource_accum_details prad
WHERE prad.txn_accum_id = pta.txn_accum_id
AND resource_list_id = l_resource_list_id
AND resource_list_assignment_id = l_resource_list_assignment_id
)
);
INSERT INTO PA_MAPPABLE_TXNS_TMP (
txn_id,
person_id,
job_id,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource,
expenditure_category,
revenue_category,
non_labor_resource_org_id,
event_type_classification,
system_linkage_function,
project_role_id,
resource_list_id,
system_reference1,
system_reference2,
system_reference3
)
SELECT
pa_mappable_txns_tmp_s.NEXTVAL,
pta.person_id,
pta.job_id,
pta.organization_id,
pta.vendor_id,
pta.expenditure_type,
pta.event_type,
pta.non_labor_resource,
pta.expenditure_category,
pta.revenue_category,
pta.non_labor_resource_org_id,
pta.event_type_classification,
pta.system_linkage_function,
NULL, /* Project role id is not there on pa_txn_accum */
l_resource_list_id,
pta.txn_accum_id, /* To identify our records back */
pta.project_id, /* This will avoid joining to pa_txn_accum again during insertion */
pta.task_id /* pa_resource_accum_details table */
FROM pa_txn_accum pta
WHERE pta.project_id = x_project_id
AND EXISTS
(SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
/* following not exists will be valid even in case of refresh ( x_mode = 'F' )
because from refresh process we call map_txns only after we have
deleted records from pa_resource_accum_details table */
AND NOT EXISTS
(SELECT 'Yes'
FROM pa_resource_accum_details prad
WHERE prad.txn_accum_id = pta.txn_accum_id
AND resource_list_id = l_resource_list_id
AND resource_list_assignment_id = l_resource_list_assignment_id
)
);
pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount|| ' rows in pa_mappable_txns_tmp ');*/
pa_debug.debug('map_txns: ' || 'Inserted ' || l_rowcount || ' rows in pa_mappable_txns_tmp ');
null_insert EXCEPTION;
PRAGMA EXCEPTION_INIT(null_insert,-1400);
x_err_stage := 'Inserting into pa_resource_accum_details';
INSERT INTO pa_resource_accum_details
(resource_list_id,
resource_list_assignment_id,
resource_list_member_id,
resource_id,
txn_accum_id,
project_id,
task_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id)
SELECT
l_resource_list_id,
l_resource_list_assignment_id,
pmt.resource_list_member_id,
pmt.resource_id,
pmt.system_reference1 txn_accum_id,
pmt.system_reference2 project_id,
pmt.system_reference3 task_id,
SYSDATE,
x_created_by, /* Global who columns initialized in spec of the package */
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id
FROM PA_MAPPABLE_TXNS_TMP pmt
WHERE NOT EXISTS
(SELECT 'Yes'
FROM pa_resource_accum_details rad
WHERE resource_list_id = l_resource_list_id
AND resource_list_assignment_id = l_resource_list_assignment_id
AND txn_accum_id = pmt.system_reference1
AND project_id = pmt.system_reference2
AND task_id = pmt.system_reference3
);
WHEN NULL_INSERT THEN
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
pa_debug.debug('map_txns: ' || 'Trying to insert null into PA_RESORCE_ACCUM_DETAILS');
pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount || ' rows into PA_RESOURCE_ACCUM_DETAILS');
This process will update the RESOURCE_LIST_MEMBER_ID and RESOURCE_ID in
table PA_MAPPABLE_TXNS_TMP table. Following needs to be done before a call
to this API is made.
PA_MAPPABLE_TXNS_TMP table should have been populated for a single RESOURCE_LIST
with all the transaction attributes with TXN_ID populated with a unique id.
Populate SYSTEM_REFERENCE1-5 columns are populated with unique identifiers
which will be used by the calling program after the completion of the
currenct process, i.e., after assigning the resources to the transactions.
*/
PROCEDURE new_map_txns
(x_resource_list_id IN NUMBER,
x_error_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_error_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_unclassified_rlm_id NUMBER;
1.1 Insert all parents in resource list and their attributes into
PA_RESOURCE_LIST_PARENTS_TMP temp table. Currently oracle projects allows
resource list to be grouped only by organization, expenditure_category
and revenue_category. Hence PA_RESOURCE_LIST_PARENTS_TMP table has only
these three attributes.
1.2 Now assign parents to each transaction in PA_MAPPABLE_TXNS_TMP table. This can
be done by matching organization, expenditure_category or revenue_category
of the txn with that in PA_RESOURCE_LIST_PARENTS_TMP.
1.3 At this point if parent could not be assigned then the txn should be
assigned to list level unclassified resource.
1.4 Now insert all possible child level resources in PA_TEMP_RES_MAPS_TMP table and
their ranks in this table. This is done by matching all attributes of
transactions with corresponding attribute of the child resource.
1.5 Fetch all the resources with highest (lowest in magnitude) rank in pl/sql
tables and update PA_MAPPABLE_TXNS_TMP table with the resource id.
1.6 At this stage if no resource is assigned to any txn but parent is assigned
then assign parent level unclassified resource to these transactions.
1.7 This is possible that a parent does not have any child under it. In such case
it may not have any unclassified member under it also. In such case parent is
the resource that should be assigned to the txn.
2. In case resource list not categorized then
2.1 Insert all possible child level resources in PA_TEMP_RES_MAPS_TMP table and their
ranks in this table. This is done by matching all attributes of transactions
with corresponding attribute of the child resource.
2.2 Same as 1.5
2.3 If no resource is assigned to any txn then list level unclassified resource
should be assigned to the txn.
*/
/* This cursor is used for processing in step 1.5 mentioned above */
CURSOR C1 IS
SELECT txn_id
,resource_list_member_id
,resource_id
FROM pa_temp_res_maps_tmp
ORDER BY txn_id, rank; /* ORDER BY is important and should not be changed */
/* This cursor selects parent level unclassified members for the transactions.
This cursor is used to achieve point 1.6 mentioned above.
*/
CURSOR C2 IS
SELECT pmt.txn_id
,prlm.resource_list_member_id
,prlm.resource_id
FROM pa_mappable_txns_tmp pmt
,pa_resource_list_members prlm
WHERE pmt.resource_list_member_id is null /* A resource is not already assigned */
AND pmt.parent_member_id is not null /* But a parent is assigned */
AND pmt.parent_member_id = prlm.parent_member_id
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.resource_type_code = 'UNCLASSIFIED';
'Selecting group_resource_type_id ' ||
'and list level unclassified member';
/* The following select should always return just one row, i.e.,
one and only one list level unclassified resource should be
present. With debug mode set to Yes, if the error stage is
the one above, its an abnormal case of a list level unclassified
resource not being present */
SELECT prl.group_resource_type_id
, prlm.resource_list_member_id
, prlm.resource_id
, prl.job_group_id
, nvl(prl.uncategorized_flag,'N')
INTO l_group_resource_type_id
, l_unclassified_rlm_id
, l_unclassified_res_id
, l_rl_job_group_id
, l_uncategorized_flag
FROM pa_resource_lists_all_bg prl
,pa_resource_list_members prlm
WHERE prl.resource_list_id = x_resource_list_id
AND prl.resource_list_id = prlm.resource_list_id
and nvl(prl.migration_code,'-99') <> 'N'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.parent_member_id is NULL
AND prlm.resource_type_code = decode(Nvl(prl.uncategorized_flag,'N'),
'Y','UNCATEGORIZED','UNCLASSIFIED');
UPDATE pa_mappable_txns_tmp PMT
SET resource_list_member_id = l_unclassified_rlm_id
,resource_id = l_unclassified_res_id
WHERE PMT.resource_list_id = x_resource_list_id;
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
update_parents_mem_id(x_res_list_id => x_resource_list_id,
x_err_stage => x_error_stage,
x_err_code => x_error_code);
/* Point 1.3 : If PARENT_MEMBER_ID is NULL even after the above update, then
those txns will be mapped to resource level UNCLASSIFIED resource.
Doing this update at this level will avoid selection of these records later
and will improve the process throughput also.
*/
UPDATE pa_mappable_txns_tmp PMT
SET resource_list_member_id = l_unclassified_rlm_id
,resource_id = l_unclassified_res_id
WHERE pmt.parent_member_id is null;
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows with list level unclassified resource ');
/* following bulk update logic is irrespective of whether resource list is categorized or not */
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
pa_debug.debug ('new_map_txns: ' || 'Update PA_MAPPABLE_TXNS_TMP table with resources assigned');
/* Bulk update has very consistent time for updations. It takes precisely 30 secs for 50000
updates. The time does not vary whether we do bulk updates in batches of 200 records or in
batches of 50000 records. Hence as per guidelines by performance team we are taking batch
size of 200 (PL/SQL size should not increase this limit)
*/
x_error_stage := 'Initializing plsql tables';
/* Point 1.5 and 2.2 : Just update PA_MAPPABLE_TXNS_TMP with records
in plsql table and handle for every 200 records */
l_prev_txn_id := NULL;
UPDATE pa_mappable_txns_tmp
SET resource_list_member_id = l_resource_member_id_tbl(i)
,resource_id = l_resource_id_tbl(i)
WHERE txn_id = l_txn_id_tbl(i);
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
UPDATE pa_mappable_txns_tmp
SET resource_list_member_id = l_resource_member_id_tbl(i)
,resource_id = l_resource_id_tbl(i)
WHERE txn_id = l_txn_id_tbl(i);
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
pa_debug.debug ('new_map_txns: ' || 'Bulk update done');
x_error_stage := 'Now update with parent level unclassified resource';
/* Select txns with PARENT_MEMBER_ID populated and RESOURCE_LIST_MEMBER_ID not
populated. These are to be mapped to resource parent level UNCLASSIFIED
resources. Refer comments of C2 rec for comments on this loop */
/* Using bulk collect logic here */
x_error_stage := 'Opening cursor c2';
x_error_stage := 'Doing bulk update of pa_mappable_txns_tmp from data fetched from c2';
UPDATE pa_mappable_txns_tmp
SET resource_list_member_id = l_resource_member_id_tbl(i)
,resource_id = l_resource_id_tbl(i)
WHERE txn_id = l_txn_id_tbl(i);
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
UPDATE pa_mappable_txns_tmp pmt
set resource_list_member_id = parent_member_id
,resource_id = parent_resource_id
WHERE pmt.resource_list_member_id is null
AND pmt.parent_member_id is not null;
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
/* update all txns with resource list level unclassified resource */
x_error_stage := 'Updating unassigned txns to list level unclassified';
UPDATE pa_mappable_txns_tmp PMT
SET resource_list_member_id = l_unclassified_rlm_id
,resource_id = l_unclassified_res_id
WHERE pmt.resource_list_member_id is null;
pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
l_txn_id_tbl.delete;
l_resource_member_id_tbl.delete;
l_resource_id_tbl.delete;
PROCEDURE update_parents_mem_id
(x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
BEGIN
x_err_stage := 'Resource list is grouped : Deleting from resource list parents table';
pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
delete from pa_resource_list_parents_tmp;
x_err_stage := 'Inserting into pa_resource_list_parents_tmp table';
pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
INSERT INTO pa_resource_list_parents_tmp
(resource_list_id
,resource_list_member_id
,resource_id
,organization_id
,expenditure_category
,revenue_category
)
( SELECT
prlm.resource_list_id
,prlm.resource_list_member_id
,prlm.resource_id
,prlm.organization_id
,prlm.expenditure_category
,prlm.revenue_category
FROM pa_resource_list_members prlm
WHERE prlm.parent_member_id is null
AND prlm.resource_list_id = x_res_list_id
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
);
pa_debug.debug('update_parents_mem_id: ' || 'Inserted ' || sql%rowcount || ' rows into pa_resource_list_parents_tmp');
one single update will do for resource lists grouped by any of these three
*/
/* Point 1.2 */
x_err_stage := 'Updating the parent member details in PA_MAPPABLE_TXNS_TMP';
pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
UPDATE pa_mappable_txns_tmp PMT
SET (parent_member_id, parent_resource_id) =
(SELECT resource_list_member_id, resource_id
FROM pa_resource_list_parents_tmp PRLP
WHERE (pmt.expenditure_category = prlp.expenditure_category
OR pmt.organization_id = prlp.organization_id
OR pmt.revenue_category = prlp.revenue_category)
AND pmt.resource_list_id = prlp.resource_list_id);
pa_debug.debug('update_parents_mem_id: ' || 'Updated ' || sql%rowcount || ' rows in pa_mappable_txns_tmp with parent member details');
pa_debug.debug('Procedure Update_Parents_Mem_Id' || x_err_stage || ' error code = ' || x_err_code);
END update_parents_mem_id;
/* This cursor select distinct resource types defined in the resource list at
child level. This cursor is used in point 1.4 in order to fire only those
inserts for which resources are defined in the list.
*/
CURSOR C3 IS
SELECT DISTINCT resource_type_code
FROM pa_resource_list_members
WHERE resource_list_id = x_res_list_id
and nvl(migration_code,'-99') <> 'N'
AND parent_member_id is not null;
DELETE FROM pa_temp_res_maps_tmp;
/* Point 1.4 : In following statements we will insert records into temp table
pa_temp_res_maps_tmp table. As one transaction can belong to multiple resources
all such resources with txn_id and rank will be inserted into this table.
Later only those resources will be picked up which have highest rank
(lowest in magnitude).
Since resource list is grouped then we use the parent_member_id info already
stamped on PA_MAPPABLE_TXNS_TMP table else we do not.
*/
FOR C3REC in C3 LOOP
IF C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' THEN
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
/* During prototyping it has been found that these 8 nuclear inserts work much faster than
one insert having 8 conditions. The one single insert with all 8 conditions combined
took hours to come back while these 8 inserts did the same job in few seconds.
These inserts are not modified to dynamic inserts because of performance reasons.
*/
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id,
prfr.rank
FROM pa_mappable_txns_tmp temp
,pa_resource_list_members prlm
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.person_id = temp.person_id
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.parent_member_id is not null
AND prlm.person_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id,
prfr.rank
FROM pa_mappable_txns_tmp temp
,pa_resource_list_members prlm
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.parent_member_id is not null
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
AND prlm.job_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.organization_id = temp.organization_id
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.parent_member_id is not null
AND prlm.enabled_flag = 'Y'
AND prlm.organization_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.revenue_category = temp.revenue_category
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.parent_member_id is not null
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
AND prlm.revenue_category is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.vendor_id = temp.vendor_id
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.parent_member_id is not null
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
AND prlm.vendor_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.expenditure_type = temp.expenditure_type
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.expenditure_type is not null
AND prlm.parent_member_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.expenditure_category = temp.expenditure_category
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.expenditure_category is not null
AND prlm.parent_member_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.event_type = temp.event_type
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.parent_member_id is not null
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
AND prlm.event_type is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE temp.parent_member_id = prlm.parent_member_id
AND prlm.project_role_id = temp.project_role_id
AND prlm.resource_format_id = prfr.resource_format_id
and prlm.resource_list_id = x_res_list_id
AND prlm.parent_member_id is not null
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.enabled_flag = 'Y'
AND prlm.project_role_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
/* This cursor select distinct resource types defined in the resource list at
child level. This cursor is used in point 2.1 in order to fire only those
inserts for which resources are defined in the list.
*/
CURSOR C3 IS
SELECT DISTINCT resource_type_code
FROM pa_resource_list_members
WHERE resource_list_id = x_res_list_id
and nvl(migration_code,'-99') <> 'N';
DELETE FROM pa_temp_res_maps_tmp;
/* Point 2.1 : In following statements we will insert records into temp table
pa_temp_res_maps_tmp table. As one transaction can belong to multiple resources
all such resources with txn_id and rank will be inserted into this table.
Later only those resources will be picked up which have highest rank
(lowest in magnitude).
*/
/* The only difference in the INSERTs for categorized and uncategorized resource
lists, is the TEMP.PARENT_MEMBER_ID = PRLM.PARENT_MEMBER_ID condition.
Uncategorized resource lists will not have the PARENT_MEMBER_ID populated
*/
FOR C3REC in C3 LOOP
IF C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' THEN
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.person_id = temp.person_id
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.person_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.job_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.organization_id = temp.organization_id
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.organization_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.revenue_category = temp.revenue_category
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.revenue_category is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.vendor_id = temp.vendor_id
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.vendor_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.expenditure_type = temp.expenditure_type
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.expenditure_type is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.expenditure_category = temp.expenditure_category
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.expenditure_category is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.event_type = temp.event_type
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.event_type is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
INSERT INTO pa_temp_res_maps_tmp
(SELECT txn_id
, prlm.resource_list_member_id
, prlm.resource_id
, prfr.rank
FROM pa_mappable_txns_tmp TEMP
,pa_resource_list_members PRLM
,pa_resource_format_ranks prfr
WHERE prlm.project_role_id = temp.project_role_id
AND prlm.resource_format_id = prfr.resource_format_id
AND temp.resource_list_id = prlm.resource_list_id
and prlm.resource_list_id = x_res_list_id
AND prlm.enabled_flag = 'Y'
and nvl(prlm.migration_code,'-99') <> 'N'
AND prlm.project_role_id is not null
AND temp.resource_list_member_id is null /* resource is not already determined */
AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');