[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.resource_list_member_id
into p_resource_list_member_id
from pa_resource_list_members a,
pa_resources b,
pa_resource_types c
where c.resource_type_code = 'UNCLASSIFIED'
and b.resource_type_id = c.resource_type_id
and a.resource_id = b.resource_id
and a.resource_list_id = p_resource_list_id
and a.parent_member_id is null
and a.enabled_flag='Y'
and NVL(a.migration_code,'M') ='M'; --Bug 3626671
Select prlm.resource_list_member_id
into p_parent_rlmi
from pa_resource_list_members prlm,
pa_resources pr
where pr.resource_type_id = p_group_resource_type_id
and pr.name = p_name
and prlm.resource_id = pr.resource_id
and prlm.resource_list_id = p_resource_list_id
and prlm.enabled_flag='Y'
and NVL(prlm.migration_code,'M') ='M'; --Bug 3626671
select distinct c.resource_type_code resource_type_code
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
or
(x_group_resource_type_id = 0 and c.parent_member_id is null)
)
and c.enabled_flag='Y'
and c.resource_type_code <> 'UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M';
select distinct a.resource_type_code resource_type_code
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
or
(x_group_resource_type_id = 0 and c.parent_member_id is null)
)
and c.enabled_flag='Y'
and a.resource_type_code <> 'UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M'; --Bug 3626671
select c.resource_list_member_id,
b.name,
a.resource_type_code,
b.resource_type_id,
c.parent_member_id
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and c.enabled_flag='Y'
and a.resource_type_code='UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M'; --Bug 3626671
select c.resource_list_member_id,
b.name,
a.resource_type_code,
b.resource_type_id,
c.parent_member_id
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and c.enabled_flag='Y'
and a.resource_type_code=p_resource_type_code
and b.name = p_name
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
select resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members
where resource_list_id = x_resource_list_id
and NVL(migration_code,'M') ='M'; -- Bug 3626671
l_stage := 'Delete resource type Table';
resource_type_tab.delete;
select prl.group_resource_type_id
into x_group_resource_type_id
from pa_resource_lists prl
where prl.resource_list_id = x_resource_list_id ;
select prt.resource_type_code
into x_group_resource_type_name
from pa_resource_types prt
where prt.resource_type_id = x_group_resource_type_id;
Select expenditure_category,
revenue_category_code
into l_expenditure_category,
l_revenue_category
from pa_expenditure_types
where expenditure_type = x_expenditure_type;
Select name
into l_organization_name
from hr_all_organization_units -- Bug 4732065
where organization_id = x_expenditure_org_id;
select substrb(papf.full_name,1,100)
into l_person_name
from per_all_people_f papf,
pa_expenditure_items_all peia,
pa_expenditures_all pea
where peia.expenditure_item_id = x_document_header_id
and pea.expenditure_id = peia.expenditure_id
and papf.person_id = pea.incurred_by_person_id
and trunc(peia.expenditure_item_date) between trunc(papf.effective_start_date) and trunc(nvl(effective_end_Date,sysdate));
select substrb(papf.full_name,1,100)
into l_person_name
from per_all_people_f papf,
gms_encumbrance_items_all geia,
gms_encumbrances_all gea
where geia.encumbrance_item_id = x_document_header_id
and gea.encumbrance_id = geia.encumbrance_id
and papf.person_id = gea.incurred_by_person_id
and trunc(geia.encumbrance_item_date) between trunc(papf.effective_start_date) and trunc(nvl(effective_end_Date,sysdate));
select substrb(pj.name,1,100)
into l_job_title
from per_jobs pj,
pa_expenditure_items_all peia
where peia.expenditure_item_id = x_document_header_id
and pj.job_id = peia.job_id;
select substrb(pj.name,1,100)
into l_job_title
from per_jobs pj,
gms_encumbrance_items_all geia
where geia.encumbrance_item_id = x_document_header_id
and pj.job_id = geia.job_id;
select DISTINCT line.vendor_id
into l_vendor_id
from po_requisition_lines line,
po_requisition_headers_all req
where req.requisition_header_id = x_document_header_id
and line.requisition_header_id = req.requisition_header_id;
select head.vendor_id
into l_vendor_id
from po_headers_all head
where head.po_header_id = x_document_header_id;
select head.vendor_id
into l_vendor_id
from ap_invoices_all head
where head.invoice_id = x_document_header_id;
select system_reference1
into l_vendor_id
from pa_cost_distribution_lines_all
where expenditure_item_id = x_document_header_id
and line_num = x_document_distribution_id
and system_reference1 is not null
and system_reference2 is not null
and system_reference3 is not null;
Select vendor_name
into l_vendor_name
from po_vendors
where vendor_id = l_vendor_id;
Select revenue_category_code
into l_revenue_category
from pa_event_types
where event_type = x_event_type;
Select expenditure_category,
revenue_category_code
into l_expenditure_category,
l_revenue_category
from pa_expenditure_types
where expenditure_type = x_expenditure_type;
Select expenditure_category,
revenue_category_code
into l_expenditure_category,
l_revenue_category
from pa_expenditure_types
where expenditure_type = x_expenditure_type;
Select revenue_category_code
into l_revenue_category
from pa_event_types
where event_type = x_event_type;
Select name
into l_organization_name
from hr_all_organization_units -- Bug 4732065
where organization_id = x_expenditure_org_id;
select c.resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and c.enabled_flag = 'Y'
and c.alias = 'Unclassified'
and c.parent_member_id is NULL
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
select c.resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and c.enabled_flag = 'Y'
and c.alias = 'Unclassified'
and c.parent_member_id = l_parent_rlmi
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
select distinct c.resource_type_code resource_type_code
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
or
(x_group_resource_type_id = 0 and c.parent_member_id is null)
)
and c.enabled_flag='Y'
and c.resource_type_code <> 'UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M';
select distinct a.resource_type_code resource_type_code
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
or
(x_group_resource_type_id = 0 and c.parent_member_id is null)
)
and c.enabled_flag='Y'
and a.resource_type_code <> 'UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
select c.resource_list_member_id,
b.name,
a.resource_type_code,
b.resource_type_id,
c.parent_member_id
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and c.enabled_flag='Y'
and a.resource_type_code='UNCLASSIFIED'
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
select c.resource_list_member_id,
b.name,
a.resource_type_code,
b.resource_type_id,
c.parent_member_id
from pa_resource_types a,
pa_resources b,
pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and b.resource_id = c.resource_id
and a.resource_type_id = b.resource_type_id
and c.enabled_flag='Y'
and a.resource_type_code=p_resource_type_code
and b.name = p_name
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
select resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members
where resource_list_id = x_resource_list_id
and NVL(migration_code,'M') ='M'; -- Bug 3626671
l_stage := 'Delete resource type Table';
resource_type_tab.delete;
select prl.group_resource_type_id
into x_group_resource_type_id
from pa_resource_lists prl
where prl.resource_list_id = x_resource_list_id;
select prt.resource_type_code
into x_group_resource_type_name
from pa_resource_types prt
where prt.resource_type_id = x_group_resource_type_id;
Select name
into l_organization_name
from hr_organization_units
where organization_id = x_expenditure_org_id;
select substrb(papf.full_name,1,100)
into l_person_name
from per_all_people_f papf,
pa_resources pr,
pa_resource_types prt
where papf.person_id = x_person_id
and papf.full_name = pr.name
and prt.resource_type_id = pr.resource_type_id
and prt.resource_type_code = 'EMPLOYEE'
and rownum=1 ;
select substrb(pj.name,1,100)
into l_job_title
from per_jobs pj
where pj.job_id = x_job_id;
Select vendor_name
into l_vendor_name
from po_vendors
where vendor_id = x_vendor_id;
Select revenue_category_code
into l_revenue_category
from pa_event_types
where event_type = x_event_type;
Select name
into l_organization_name
from hr_organization_units
where organization_id = x_expenditure_org_id;
select c.resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and c.enabled_flag = 'Y'
and c.alias = 'Unclassified'
and c.parent_member_id is NULL
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
select c.resource_list_member_id
into x_resource_list_member_id
from pa_resource_list_members c
where c.resource_list_id = x_resource_list_id
and c.enabled_flag = 'Y'
and c.alias = 'Unclassified'
and c.parent_member_id = l_parent_rlmi
and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
select prm.resource_list_member_id
into x_resource_list_member_id
from pa_resource_maps prm,
pa_resource_list_assignments prla
where prla.resource_list_assignment_id = prm.resource_list_assignment_id
and prla.project_id = x_project_id
and prm.resource_list_id = x_res_list_id
and prm.organization_id = x_organization_id
and prm.expenditure_category = x_expenditure_category
and prm.system_linkage_function = x_system_linkage
and nvl(prm.job_id,-1) = nvl(x_job_id,-1)
and nvl(prm.person_id,-1) = nvl(x_person_id,-1)
and nvl(prm.vendor_id,-1) = nvl(x_vendor_id ,-1)
and nvl(prm.expenditure_type,'X') = nvl(x_expenditure_type,'X')
and nvl(prm.non_labor_resource,'X') = nvl(x_non_labor_resource,'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 rownum = 1;
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;
PROCEDURE delete_res_maps_on_asgn_id
(x_resource_list_assignment_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
DELETE
pa_resource_maps;
DELETE
pa_resource_maps prm
WHERE
prm.resource_list_assignment_id = x_resource_list_assignment_id;
pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
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,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 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)
);
pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
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 rl
WHERE
rl.resource_list_id = x_resource_list_id
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
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,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code :=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;
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;
pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
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
FROM
pa_resource_lists 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(rlm.parent_member_id,0) = 0
--AND rlm.enabled_flag = 'Y' Bug Fix 1370475
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 nvl(rta.expenditure_type,0)=nvl(x_exp_type,nvl(rta.expenditure_type,0))
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,'M') ='M' -- Bug 3626671
--AND rlmc.enabled_flag = 'Y' Bug Fix 1370475
)
AND NVL(rl.migration_code,'M') ='M' -- Bug 3626671
AND NVL(rlm.migration_code,'M') ='M' -- Bug 3626671
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
FROM
pa_resource_lists 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 rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
--AND rlmc.enabled_flag = 'Y' --Bug Fix 1370475
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' --Bug Fix 1370475
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
AND nvl(rtac.expenditure_type,0)=nvl(x_exp_type,nvl(rtac.expenditure_type,0))
AND NVL(rl.migration_code,'M') ='M' -- Bug 3626671
AND NVL(rlmc.migration_code,'M') ='M' -- Bug 3626671
AND NVL(rlmp.migration_code,'M') ='M' -- Bug 3626671
/* The next order by is very important.
Ordering the resource by resource_list_assignment_id, resource_list_id */
ORDER BY 1,2;
-- delete all the old maps for this resource list assignments
-- for all the transactions
delete_res_maps_on_asgn_id(current_rl_assignment_id,x_err_stage,x_err_code);