[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
from psp_report_errors
where request_id = l_request_id
and (l_retry_request_id = -1
OR retry_request_id = l_retry_request_id)
and message_level = 'E'; --- count fatal errors that require
select count(*)
from psp_eff_reports
where status_code IN ('N', 'A')
and request_id = l_request_id;
select request_id
from pay_payroll_actions
where pactid = payroll_action_id;
SELECT paa.request_id
FROM fnd_concurrent_requests fcr,
pay_payroll_actions paa
WHERE fcr.request_id = l_request_id
AND paa.payroll_action_id = TO_NUMBER(fcr.argument2);
SELECT fcp.concurrent_program_name
FROM fnd_concurrent_programs fcp,
fnd_concurrent_requests fcr
WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = l_request_id;
SELECT request_id
FROM psp_report_templates_h
WHERE payroll_action_id = pactid;
psp_xmlgen.update_er_error_details (p_request_id => l_request_id,
p_retry_request_id => NULL,
p_return_status => l_return_status);
psp_xmlgen.update_er_error_details (p_request_id => l_original_request_id,
p_retry_request_id => l_request_id,
p_return_status => l_return_status);
/* Following statement added to purge psp_selected_persons_t in dinit code, when run is successful * /
DELETE from psp_selected_persons_t WHERE request_id=l_request_id and not exists
(select 1 from pay_payroll_actions where payroll_action_id =pactid and action_status
='E') ;
select 1
from psp_report_templates_h
where request_id = l_request_id
and approval_type = 'PRE';
update psp_report_templates_h
set initiator_accept_flag = 'Y'
where request_id = l_request_id;
update psp_eff_reports
set status_code = 'A',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
where request_id = l_request_id;
DELETE FROM psp_selected_persons_t
WHERE request_id=l_request_id;
update psp_report_templates_h
set initiator_accept_flag = 'N'
where request_id = l_request_id;
select MANUAL_ENTRY_OVERRIDE_FLAG
from psp_report_templates_h where request_id = p_request_id;
select wf_role_name,
psp_wf_item_key_s.nextval
from (select era.wf_role_name
from psp_eff_report_approvals era,
psp_eff_reports er,
psp_eff_report_details erd
where erd.effort_report_id = er.effort_report_id
and erd.effort_report_detail_id = era.effort_report_detail_id
and era.approval_status = 'P'
and era.approver_order_num = 1
and er.request_id = p_request_id
and er.status_code = 'N'
group by wf_role_name);
update psp_eff_report_approvals
set wf_item_key = wf_ikey_array(k)
where wf_role_name = wf_rname_array(k)
and effort_report_detail_id in
(select erd.effort_report_detail_id
from psp_eff_reports er,
psp_eff_report_details erd
where erd.effort_report_id = er.effort_report_id
and er.request_id = l_request_id
and er.status_code = 'N');
wf_ikey_array.delete;
delete psp_eff_report_approvals
where effort_report_detail_id in
(select effort_report_detail_id
from psp_eff_report_details
where effort_report_id in
(select effort_report_id
from psp_eff_reports
where request_id = l_request_id));
delete psp_eff_report_details
where effort_report_id in
(select effort_report_id
from psp_eff_reports
where request_id = l_request_id);
delete psp_eff_reports
where request_id = l_request_id;
delete psp_report_errors
where request_id = l_request_id;
DELETE fnd_lobs fl
WHERE fl.file_id IN (SELECT fdl.media_id
FROM fnd_attached_documents fad,
fnd_documents_vl fdl
WHERE fad.pk1_value = itemkey
AND fdl.document_id = fad.document_id
AND fad.entity_name = 'ERDETAILS');
DELETE fnd_lobs fl
WHERE fl.file_id IN (SELECT fdl.media_id
FROM fnd_attached_documents fad,
fnd_documents_vl fdl
WHERE fad.pk1_value IN (SELECT wf_item_key
FROM psp_eff_report_approvals pera
WHERE pera.effort_report_detail_id IN (SELECT perd.effort_report_detail_id
FROM psp_eff_report_details perd
WHERE perd.effort_report_id IN (SELECT per.effort_report_id
FROM psp_eff_reports per
WHERE per.request_id = l_request_id)))
AND fdl.document_id = fad.document_id
AND fad.entity_name = 'ERDETAILS');
DELETE FROM psp_selected_persons_t
WHERE request_id=l_request_id;
select wf_role_name
from psp_eff_report_approvals
where wf_item_key = itemkey
and rownum = 1
and approver_order_num = 1;
select distinct erd.ame_transaction_id
from psp_eff_report_details erd
where erd.effort_report_detail_id in
(select era.effort_report_detail_id
from psp_eff_report_approvals era
where wf_item_key = itemkey
and approval_status = 'A');
select wf_role_name,
wf_role_display_name
from psp_eff_report_approvals
where wf_item_key = itemkey
and approver_order_num = p_approver_order_num
and approval_status = 'P'
and rownum = 1;
select approval_type
from psp_Report_templates_h
where request_id = l_request_id;
select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
select apps.fnd_global.resp_id into l_resp_id from dual;
select apps.fnd_global.user_id into l_user from dual;
update psp_eff_reports er
set er.status_code = 'A',
er.last_update_date = sysdate,
er.last_update_login = fnd_global.login_id,
er.last_updated_by = fnd_global.user_id
where er.status_code = 'N'
and er.effort_report_id in
(select erd.effort_report_id
from psp_eff_report_details erd,
psp_eff_report_approvals era
where era.effort_report_detail_id = erd.effort_report_detail_id
and era.wf_item_key = itemkey)
and not exists
(select 1
from psp_eff_report_approvals era,
psp_eff_report_details erd
where era.effort_report_detail_id = erd.effort_report_detail_id
and erd.effort_report_id = er.effort_report_id
and era.approval_status <> 'A');
insert into psp_eff_report_approvals
(effort_report_approval_id,
effort_report_detail_id,
wf_role_name,
wf_orig_system_id,
wf_orig_system,
approver_order_num,
approval_status,
wf_item_key,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
wf_role_display_name,
object_version_number)
select psp_eff_report_approvals_s.nextval,
erd.effort_report_detail_id,
l_next_approver(1).name,
l_next_approver(1).orig_system_id,
l_next_approver(1).orig_system,
l_approver_order_num,
nvl(l_next_approver(1).approval_status,'P'),
itemkey,
sysdate,
l_user_id,
l_login_id,
sysdate,
l_user_id,
l_next_approver(1).display_name,
1
from psp_eff_report_details erd
where erd.ame_transaction_id = ame_txn_id_array(i)
and erd.effort_report_id in
(select er.effort_report_id
from psp_eff_reports er
where er.request_id = l_request_id
and er.status_code = 'N');
update psp_eff_report_approvals A1
set (A1.actual_cost_share, A1.overwritten_effort_percent, comments,
/* Add DF Columns for Hospital Effort report */
pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
eff_information11, eff_information12, eff_information13, eff_information14, eff_information15) =
(select A2.actual_cost_share, A2.overwritten_effort_percent, comments,
/* Add DF Columns for Hospital Effort report */
pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
eff_information11, eff_information12, eff_information13, eff_information14, eff_information15
from psp_eff_report_approvals A2
where A1.effort_report_detail_id = A2.effort_report_detail_id
/* Bug 5235725: Replacing l_next_approver(1).approver_order_number with l_approver_order_num.
In case of reassign a notification function ame_api2.getNextApprovers4 resturns nextApproversOut.approver_order_number with an
incremented value while in our system (Effort rporting) we do not increment the approver_order_number in case of reassign */
-- and A2.approver_order_num = l_next_approver(1).approver_order_number -1)
and A2.approver_order_num = l_approver_order_num -1)
-- where A1.approver_order_num = l_next_approver(1).approver_order_number and
where A1.approver_order_num = l_approver_order_num and
A1.effort_report_detail_id in
(select erd.effort_report_detail_id
from psp_eff_reports er,
psp_eff_report_details erd
where er.request_id = l_request_id
and er.effort_report_id = erd.effort_report_id
and erd.ame_transaction_id = ame_txn_id_array(i)
and er.status_code = 'N');
hr_utility.trace(' ER workflow -> update count= '||sql%rowcount);
update psp_eff_reports er
set er.status_code = 'A',
er.last_update_date = sysdate,
er.last_update_login = fnd_global.login_id,
er.last_updated_by = fnd_global.user_id
where er.status_code = 'N'
and er.effort_report_id in
(select erd.effort_report_id
from psp_eff_report_details erd,
psp_eff_report_approvals era
where era.effort_report_detail_id = erd.effort_report_detail_id
and era.wf_item_key = itemkey)
and not exists
(select 1
from psp_eff_report_approvals era,
psp_eff_report_details erd
where era.effort_report_detail_id = erd.effort_report_detail_id
and erd.effort_report_id = er.effort_report_id
and era.approval_status <> 'A');
psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
p_return_status => l_return_status);
select effort_report_id
from psp_eff_reports
where effort_report_id in
(select effort_report_id
from psp_eff_report_details
where effort_report_detail_id in
(select effort_report_detail_id
from psp_eff_report_approvals
where wf_item_key = itemkey
and wf_role_name = l_rname));
select distinct era.wf_role_name
from psp_eff_reports er,
psp_eff_report_details erd,
psp_eff_report_approvals era,
fnd_user fu -- Bug 6641216
where era.effort_report_detail_id = erd.effort_report_detail_id
and erd.effort_report_id = er.effort_report_id
and era.approval_status in ( 'A','P')
and er.request_id = l_request_id
and er.effort_report_id in
(select effort_report_id
from psp_eff_reports
where effort_report_id in
(select effort_report_id
from psp_eff_report_details
where effort_report_detail_id in
(select effort_report_detail_id
from psp_eff_report_approvals
where wf_item_key = itemkey
and wf_role_name = l_rname)))
and era.wf_role_name = fu.user_name -- Bug 6641216
and trunc(sysdate) between trunc(fu.start_date) and nvl(trunc(fu.end_date),trunc(sysdate)) -- Bug 6641216
union
select name
from wf_roles
where orig_system = 'PER'
and orig_system_id in
(select initiator_person_id
from psp_report_templates_h
where request_id = l_request_id);
select distinct ame_transaction_id
from psp_eff_report_details erd,
psp_eff_report_approvals era
where erd.effort_report_detail_id = era.effort_report_detail_id
and era.wf_item_key = itemkey
and era.approval_status = 'P'
and era.wf_role_name = l_rname
and era.wf_orig_system_id = l_orig_system_id
and era.wf_orig_system = l_orig_system;
select wf_role_name
from psp_eff_report_approvals
where wf_item_key = itemkey
and approver_order_num = p_approver_order_num
and approval_status = 'P'
and rownum = 1;
select orig_system_id,
orig_system
into l_orig_system_id,
l_orig_system
from wf_roles
where name = l_rname;
ame_api2.updateapprovalstatus(applicationidin => 8403,
transactiontypein => 'PSP-ER-APPROVAL',
transactionidin => l_txn_id,
approverin => approver_rec);
update psp_eff_report_approvals era
set era.approval_status = 'S',
era.response_date = sysdate,
era.last_update_date = sysdate,
era.last_update_login = fnd_global.login_id,
era.last_updated_by = fnd_global.user_id
where era.wf_item_key = itemkey
and era.wf_role_name = l_rname
and exists
( select erd.effort_report_detail_id
from psp_eff_report_details erd,
psp_eff_reports er
where er.effort_report_id = erd.effort_report_id
and erd.effort_report_detail_id = era.effort_report_detail_id
and er.status_code = 'S' );
update psp_eff_report_approvals era
set era.approval_status = 'R',
era.response_date = sysdate,
era.last_update_date = sysdate,
era.last_update_login = fnd_global.login_id,
era.last_updated_by = fnd_global.user_id
where era.wf_item_key = itemkey
and era.wf_role_name = l_rname
and not exists
( select erd.effort_report_detail_id
from psp_eff_report_details erd,
psp_eff_reports er
where er.effort_report_id = erd.effort_report_id
and erd.effort_report_detail_id = era.effort_report_detail_id
and er.status_code = 'S' );
update psp_eff_reports
set status_code = 'R',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
where effort_report_id = l_effort_report_id;
select wf_orig_system_id,
wf_orig_system
from psp_eff_report_approvals
where wf_item_key = itemkey
and approval_status = 'P'
and wf_role_name = l_rname;
select distinct ame_transaction_id
from psp_eff_report_details erd,
psp_eff_report_approvals era
where erd.effort_report_detail_id = era.effort_report_detail_id
and era.wf_item_key = itemkey
and era.approval_status = 'A'
and era.wf_role_name = l_rname
and era.wf_orig_system_id = l_orig_system_id
and era.wf_orig_system = l_orig_system;
select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
select apps.fnd_global.resp_id into l_resp_id from dual;
select apps.fnd_global.user_id into l_user from dual;
update psp_eff_report_approvals era
set era.approval_status = 'S',
era.response_date = SYSDATE,
era.last_update_date = sysdate,
era.last_update_login = fnd_global.login_id,
era.last_updated_by = fnd_global.user_id
where era.wf_item_key = itemkey
and era.wf_role_name = l_rname
and era.wf_orig_system_id = l_orig_system_id
and era.wf_orig_system = l_orig_system
and era.approval_status = 'P'
and exists
( select erd.effort_report_detail_id
from psp_eff_report_details erd,
psp_eff_reports er
where er.effort_report_id = erd.effort_report_id
and erd.effort_report_detail_id = era.effort_report_detail_id
and er.status_code = 'S' );
update psp_eff_report_approvals era
set era.approval_status = 'A',
era.response_date = SYSDATE,
era.last_update_date = sysdate,
era.last_update_login = fnd_global.login_id,
era.last_updated_by = fnd_global.user_id
where era.wf_item_key = itemkey
and era.wf_role_name = l_rname
and era.wf_orig_system_id = l_orig_system_id
and era.wf_orig_system = l_orig_system
and era.approval_status = 'P'
and not exists
( select erd.effort_report_detail_id
from psp_eff_report_details erd,
psp_eff_reports er
where er.effort_report_id = erd.effort_report_id
and erd.effort_report_detail_id = era.effort_report_detail_id
and er.status_code = 'S' );
ame_api2.updateapprovalstatus(applicationidin => 8403,
transactiontypein => 'PSP-ER-APPROVAL',
transactionidin => l_txn_id,
approverin => approver_rec);
psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
p_return_status => l_return_status);
select wf_orig_system_id,
wf_orig_system
from psp_eff_report_approvals
where wf_item_key = itemkey
and approval_status = 'P'
and wf_role_name = l_rname;
select distinct ame_transaction_id
from psp_eff_report_details erd,
psp_eff_report_approvals era
where erd.effort_report_detail_id = era.effort_report_detail_id
and era.wf_item_key = itemkey
and era.approval_status = 'P'
and era.wf_role_name = l_rname2
and era.wf_orig_system_id = l_orig_system_id2
and era.wf_orig_system = l_orig_system2;
select orig_system,
orig_system_id,
display_name
from wf_roles
where name = l_rname2;
SELECT distinct prth.hundred_pcent_eff_at_per_asg, per.start_date,
per.end_date, per.person_id, perd.assignment_id, per.full_name, perd.assignment_number
FROM psp_report_templates_h prth,
psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals prea
WHERE prth.request_id = per.request_id
AND per.effort_report_id = perd.effort_report_id
AND perd.effort_report_detail_id = prea.effort_report_detail_id
AND per.request_id = l_request_id
AND prea.wf_role_name = l_wf_role_name
AND prea.wf_item_key = l_wf_item_key
AND prea.approver_order_num = l_approver_order_num;
SELECT 1
FROM psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals prea
WHERE per.effort_report_id = perd.effort_report_id
AND perd.effort_report_detail_id = prea.effort_report_detail_id
AND per.person_id = l_person_id
AND per.start_date = l_start_date
AND per.end_date = l_end_date
AND prea.wf_role_name <> l_wf_role_name
AND approver_order_num = l_approver_order_num
AND prea.approval_status ='P'
AND per.status_code IN ('N','A');
SELECT 1
FROM psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals prea
WHERE per.effort_report_id = perd.effort_report_id
AND perd.effort_report_detail_id = prea.effort_report_detail_id
AND perd.assignment_id = l_assignment_id
AND per.start_date = l_start_date
AND per.end_date = l_end_date
AND prea.wf_role_name <> l_wf_role_name
AND approver_order_num = l_approver_order_num
AND prea.approval_status = 'P'
AND per.status_code IN ('N','A');
SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
FROM psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals prea
WHERE per.effort_report_id = perd.effort_report_id
AND perd.effort_report_detail_id = prea.effort_report_detail_id
AND per.person_id = l_person_id
AND per.start_date = l_start_date
AND per.end_date = l_end_date
AND approver_order_num = l_approver_order_num
AND prea.approval_status IN ('P','A')
AND per.status_code IN ('N','A');
SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
FROM psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals prea
WHERE per.effort_report_id = perd.effort_report_id
AND perd.effort_report_detail_id = prea.effort_report_detail_id
AND perd.assignment_id = l_assignment_id
AND per.start_date = l_start_date
AND per.end_date = l_end_date
AND approver_order_num = l_approver_order_num
AND prea.approval_status IN ('P','A')
AND per.status_code IN ('N','A');
update psp_eff_report_approvals
set wf_role_name = l_rname2,
wf_role_display_name = l_role_display_name2,
wf_orig_system = l_orig_system2,
wf_orig_system_id = l_orig_system_id2
where wf_item_key = itemkey
and wf_role_name = l_rname
and wf_orig_system_id = l_orig_system_id
and wf_orig_system = l_orig_system
and approval_status = 'P';
update fnd_attached_documents
set pk1_value = itemkey||l_rname2
where pk1_value = itemkey||l_rname;
hr_utility.trace('Transfer mode CALLING UPDATEAME txn_id ='||l_txn_id);
ame_api2.updateapprovalstatus(applicationidin => 8403,
transactiontypein => 'PSP-ER-APPROVAL',
transactionidin => l_txn_id,
approverin => approver_rec,
forwardeein => forward_rec);
update psp_eff_report_approvals set NOTIFICATION_ID = l_nid
where WF_ITEM_KEY = itemkey
AND WF_ROLE_NAME = l_rname;
SELECT 1
FROM psp_report_errors
WHERE pdf_request_id = l_pdf_request_id;
select person_id
from psp_eff_reports
where effort_report_id in
(select effort_report_id
from psp_eff_report_details
where request_id = l_request_id
and effort_report_detail_id in
(select effort_report_detail_id
from psp_eff_report_approvals
where wf_item_key = itemkey
and wf_role_name = l_rname));*/
insert into psp_report_errors
(error_sequence_id, request_id, message_level, source_id,
error_message, retry_request_id, pdf_request_id)
values (psp_report_errors_s.nextval,
l_request_id, 'E',l_person_id,
'PDF Generation Failed, please see the Concurrent process log',
l_retry_request_id, l_pdf_request_id);
SELECT template_name,
preview_effort_report_flag,
notification_reminder_in_days,
fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_2)) start_date,
fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_3)) end_date
FROM psp_report_templates_h prth
WHERE prth.request_id = p_request_id;
SELECT set_of_books_id
FROM psp_report_templates_h
WHERE request_id = p_request_id;
SELECT prt.template_name,
TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), l_icx_date_format) start_date,
TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), l_icx_date_format) end_date,
-- xtt.template_name report_layout,
flv1.meaning sort_option1,
flv2.meaning order_by1,
flv3.meaning sort_option2,
flv4.meaning order_by2,
flv5.meaning sort_option3,
flv6.meaning order_by3,
flv7.meaning sort_option4,
flv8.meaning order_by4
FROM psp_report_templates_h prth,
xdo_templates_tl xtt,
psp_report_templates prt,
(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv1,
(select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv2,
(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv3,
(select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv4,
(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv5,
(select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv6,
(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv7,
(select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv8
WHERE prth.request_id = p_request_id
AND prt.template_id = prth.template_id
AND flv1.lookup_code = prth.parameter_value_5
-- AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
AND flv2.lookup_code = prth.parameter_value_6
AND flv3.lookup_code = prth.parameter_value_7
-- AND flv4.lookup_type = 'PSP_ORDERING_CRITERIA'
AND flv4.lookup_code = prth.parameter_value_8
AND flv5.lookup_code (+) = prth.parameter_value_9
-- AND NVL(flv6.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
AND flv6.lookup_code (+) = prth.parameter_value_10
AND flv7.lookup_code (+) = prth.parameter_value_11
-- AND NVL(flv8.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
AND flv8.lookup_code (+) = prth.parameter_value_12
AND xtt.template_code = prth.report_template_code
AND xtt.application_short_name = 'PSP';
SELECT payroll_action_id
FROM pay_payroll_actions
WHERE request_id = p_request_id;
l_emp_matching_selection NUMBER;
CURSOR emp_matching_selection_cur IS
SELECT COUNT(DISTINCT person_id) -- Modified count(*) to count(distinct person_id) for bug fix 4429787
FROM psp_selected_persons_t
WHERE request_id = p_request_id;
select psp_wf_item_key_s.nextval
into l_wf_itemkey
from dual;
update psp_report_templates_h
set INITIATOR_WF_ITEM_KEY = l_wf_itemkey
where request_id = p_request_id;
OPEN emp_matching_selection_cur;
FETCH emp_matching_selection_cur INTO l_emp_matching_selection;
CLOSE emp_matching_selection_cur;
aname => 'EMP_MATCHING_SELECTION',
avalue => l_emp_matching_selection);
select initiator_accept_flag
from psp_report_templates_h
where request_id = l_request_id;
select distinct wf.name
from wf_roles wf,
psp_report_template_details_h temp
where temp.request_id = l_request_id
and wf.orig_system = 'PER'
and to_char(wf.orig_system_id) = temp.criteria_value1
and temp.criteria_lookup_type = 'PSP_SELECTION_CRITERIA'
and temp.criteria_lookup_code = 'FRP';
select count(*)
from psp_eff_reports
where status_code = 'A'
and request_id = l_request_id;
update psp_report_templates_h
set final_recip_notified_flag = 'Y'
where request_id = l_request_id;
procedure update_receiver(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) is
-- Bug 7135471 starts
l_request_id integer;
select approval_type
from psp_report_templates_h
where request_id = l_request_id;
select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
select apps.fnd_global.resp_id into l_resp_id from dual;
select apps.fnd_global.user_id into l_user from dual;
delete from fnd_lobs
where file_id in(select media_id from fnd_documents_vl
where document_id in(select document_id from fnd_attached_documents
where pk1_value = itemkey));
delete from fnd_attached_documents
where pk1_value = itemkey;
end update_receiver;
procedure update_approver(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) is
l_resp_appl_id number; -- 10185794
select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
select apps.fnd_global.resp_id into l_resp_id from dual;
select apps.fnd_global.user_id into l_user from dual;
end update_approver;
select approval_type
from psp_report_templates_h
where request_id = l_request_id;
PROCEDURE update_initiator_message (itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
result OUT nocopy varchar2) IS
l_message_name fnd_new_messages.message_name%TYPE;
wf_core.context('PSP_EFFORT_REPORTS', 'update_initiator_message', itemtype, itemkey, to_char(actid), funcmode);
END update_initiator_message;
SELECT preview_effort_report_flag
FROM psp_report_templates_h prth
WHERE prth.request_id = l_request_id;
select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
select apps.fnd_global.resp_id into l_resp_id from dual;
select apps.fnd_global.user_id into l_user from dual;
select 'Y'
into l_dummy
from wf_item_attribute_values
where item_type = p_item_type
and item_key = p_item_key
and name = p_name;