[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
31/05/04 sukhanna Removing the covering select clause in the
cursor definiton of get_name
16/06/04 sulkumar Bug 3629793: Commented code containing
summary_version_number.
27/08/04 sanantha Bug 3787169. call the api modify_wf_clob_content
06/09/04 smekala Bug 3848024. Stopping notifications to end dated users
28/09/04 smekala Bug 3905748 Closing the cursors.
09/02/05 rvelusam Bug 4165780 Changed attribute 'FORWARD_TO' to
FORWARD_TO_USERNAME_RESPONSE and changed the value set
for REPORT_APPROVER_USER_NAME.
08/05/05 raluthra Bug 4527617. Replaced fnd_user.customer_id with
fnd_user.person_party_id for R12 ATG Mandate fix.
08/05/05 raluthra Bug 4358517: Changed the definition of
l_org local variable from VARCHAR2(60) to
pa_project_lists_v.carrying_out_organization_name%TYPE
08/09/05 raluthra Bug 4565156. Added code for Manual NOCOPY Changes
for usage of same variable for In and Out parameter.
06/02/06 posingha Bug 4940945 Changed the query to base tables instead of view
to improve performance.
31/03/06 posingha Bug 5027098 Added code to set the 'From' role attribute
value for notifications.
19/04/06 sukhanna Bug 5173760. Did changes for swan UI. Changed these color codes
replaced #cccc99 with #cfe0f1
Replaced #336699 with #3c3c3c
Replaced #f7f7e7 with #f2f2f5
26/06/06 sukhanna Bug 5357187. Did changes for swan UI. Changed these color codes
replaced #cccc99 with #cfe0f1
Replaced #336699 with #3c3c3c
Replaced #f7f7e7 with #f2f2f5
26/04/07 vvjoshi Bug#5962401: Modified the expiration date for adhoc roles in
CreateAdhocRole procedure call.
=============================================================================*/
G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
SELECT MAX(item_key)
FROM pa_wf_processes, pa_progress_report_vers pprv
WHERE item_type = p_item_type
AND description = p_process_name
AND pprv.version_id = p_version_id
AND entity_key1 = pprv.object_id
AND pprv.object_type = 'PA_PROJECTS'
AND wf_type_code = 'Progress Report'
AND entity_key2 = p_version_id;
SELECT pa_workflow_itemkey_s.nextval
INTO l_item_key
from dual;
SELECT report_status_code
FROM pa_progress_report_vers
WHERE version_id = l_version_id;
SELECT 'Y'
FROM dual
WHERE exists
(SELECT * FROM pa_progress_report_vers pprv, pa_object_page_layouts popl
WHERE pprv.version_id = l_version_id
AND pprv.object_id = popl.object_id
AND pprv.object_type = popl.object_type
AND popl.approval_required = 'A');
SELECT employee_id
FROM fnd_user
WHERE user_id = l_submitter_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_reported_by_id;
,x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
, x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
, x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_item_key VARCHAR2(200);
SELECT MAX(item_key)
FROM pa_wf_processes
WHERE item_type = p_item_type
AND description = p_process_name
AND entity_key1 = l_project_id
AND wf_type_code = 'Progress Report'
;
SELECT
pplv. customer_name,
pplv.person_name,
pplv.carrying_out_organization_name,
pplv.name || '(' || pplv.segment1 || ')',
prt.name,
pplv.project_id
FROM pa_project_lists_v pplv, pa_object_page_layouts popl, pa_report_types prt
WHERE pplv.project_id = popl.object_id
AND popl.object_page_layout_id = p_object_id
AND popl.object_type = 'PA_PROJECTS'
and nvl(popl.report_type_id, 1) = prt.report_type_id
;
SELECT
report_type_id,
reporting_cycle_id,
report_offset_days,
effective_from
FROM pa_object_page_layouts
WHERE
object_page_layout_id = p_object_id
and
page_type_code = 'PPR';
SELECT To_char(pa_workflow_itemkey_s.NEXTVAL)
INTO l_item_key
from dual;
-- insert into pa_wf_process table
--debug_msg_s1 ('after workflow 1: isnertwf processes' || p_item_type);
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'Progress Report'
,p_item_type => p_item_type
,p_item_key => l_item_key
,p_entity_key1 => p_object_id
,p_entity_key2 => l_project_id
,p_description => p_process_name
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
, x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
)
IS
l_object_type VARCHAR2(30);
SELECT object_id, reminder_days, next_reporting_date
FROM pa_object_page_layouts
WHERE object_page_layout_id = l_object_id
AND object_type = 'PA_PROJECTS';
SELECT papf.full_name person_name,
papf.email_address
FROM
fnd_user fu,per_all_people_f papf
where fu.employee_id = papf.person_id
AND fu.user_name = l_user_name
AND trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1);
, x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
)
IS
l_object_type VARCHAR2(30);
SELECT object_id, reminder_days, next_reporting_date
FROM pa_object_page_layouts
WHERE object_page_layout_id = l_object_id
AND object_type = 'PA_PROJECTS';
select distinct
fu.user_id,
fu.user_name,
papf.email_address,
papf.full_name person_name
from
fnd_user fu,per_all_people_f papf
where
fu.employee_id = l_approver_id
and papf.person_id = fu.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
;
select distinct
fu.user_id,
fu.user_name,
papf.email_address,
papf.full_name person_name
from
fnd_user fu,per_all_people_f papf
where
fu.person_party_id = l_approver_id -- Bug 4527617. Replaced customer_id with person_party_id.
and papf.person_id = fu.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
;
select distinct
fu.user_id,
fu.user_name,
papf.email_address,
papf.full_name person_name,
ppp.resource_source_id
from
pa_project_parties ppp,
fnd_user fu,per_all_people_f papf
where ppp.project_id = l_project_id
and ppp.project_role_id = To_number(l_role_id)
and ppp.project_id = ppp.object_id
AND fu.employee_id = ppp.resource_source_id
and papf.person_id = fu.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
and trunc(sysdate) between ppp.START_DATE_active
and nvl(ppp.END_DATE_active, sysdate+1);
select distinct
fu.user_id,
fu.user_name,
p2.email_address,
p2.full_name person_name
from
pa_proj_parties_prog_ev_v ppp,
fnd_user fu,per_all_people_f p2,
per_assignments_f p1
where ppp.project_id = l_project_id
and ppp.project_role_id = 1
and ppp.resource_source_id = p1.person_id
and p1.primary_flag='Y'
and p1.Assignment_type in ('E', 'C')
and p1.supervisor_id = p2.person_id
and p1.supervisor_id = fu.employee_id
and trunc(sysdate)
between p1.EFFECTIVE_START_DATE
and p1.effective_end_date -- Removed nvl for bug 2911451
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
and trunc(sysdate) between ppp.START_DATE_active
and nvl(ppp.END_DATE_active, sysdate+1)
and exists ( select 1 from per_assignments_f p3
where p3.person_id = p1.supervisor_id
and p3.primary_flag='Y'
and p3.Assignment_type in ('E', 'C')
and trunc(sysdate) between p3.EFFECTIVE_START_DATE and Nvl(p3.effective_end_date, Sysdate + 1));
SELECT action_attribute1
FROM pa_action_set_lines
WHERE action_set_line_id = l_action_set_id;
SELECT approver_source_id, approver_source_type
FROM pa_progress_report_setup_v
WHERE object_page_layout_id = l_object_page_layout_id;
SELECT user_id FROM
fnd_user
WHERE user_name = l_forward_to
and trunc(sysdate) between start_date and nvl(end_date, sysdate); /* Bug#3848024 */
-- select party_name from
--(
select hp.party_name
from fnd_user fu,
hz_parties hp
where fu.user_name = l_forward_to --fnd_global.user_id
and fu.employee_id is null
and fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
union
select hp.party_name
from fnd_user fu,
hz_parties hp
where fu.user_name = l_forward_to--fnd_global.user_id
and fu.employee_id is not null
and 'PER:' || fu.employee_id = hp.orig_system_reference;
select wfl.lookup_code result_code
from wf_lookups wfl,
wf_activities wfa,
wf_process_activities wfpa,
wf_items wfi
where wfl.lookup_type = wfa.result_type
and wfa.name = wfpa.activity_name
and wfi.begin_date >= wfa.begin_date
and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
and wfpa.activity_item_type = wfa.item_type
and wfpa.instance_id = actid
and wfi.item_key = itemkey
and wfi.item_type = itemtype;
select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
from pa_progress_report_vals prval, pa_progress_report_vers prver
where prval.version_id = document_id
and prver.version_id = document_id
and region_code = 'PA_PROGRESS_GENERAL_TOP';
SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
pa_progress_reports_v pprv,
pa_lookups pl,
pa_project_parties ppp,
per_all_people_f papf
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS'
and papf.person_id = ppp.resource_source_id
AND pprv.reported_by = ppp.resource_id
AND ppp.object_id = pprv.object_id
and ppp.object_type = pprv.object_type
and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
SELECT pprv.*, pl.meaning progress_status FROM
pa_progress_report_vers pprv,
pa_lookups pl
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS';*/
select usr.user_id, usr.person_party_id, usr.user_name,papf.email_address,papf.full_name person_name -- Bug 4527617. Replaced customer_id with person_party_id.
from per_all_people_f papf,
fnd_user usr
WHERE
papf.person_id = usr.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
AND usr.user_id = l_reported_by_id;
document := document || 'Last Updated By ';
Line: 2130
document := document || gri.last_updated_by || ' ';
Line: 2186
select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
from pa_progress_report_vals prval, pa_progress_report_vers prver
where prval.version_id = document_id
and prver.version_id = document_id
and region_code = 'PA_PROGRESS_GENERAL_TOP';
Line: 2193
SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
pa_progress_reports_v pprv,
pa_lookups pl,
pa_project_parties ppp,
per_all_people_f papf
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS'
and papf.person_id = ppp.resource_source_id
AND pprv.reported_by = ppp.resource_id
AND ppp.object_id = pprv.object_id
and ppp.object_type = pprv.object_type
and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
Line: 2208
SELECT pprv.*, pl.meaning progress_status FROM
pa_progress_report_vers pprv,
pa_lookups pl
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS';*/
Line: 2217
select usr.user_id, usr.customer_id, usr.user_name,papf.email_address,papf.full_name person_name
from per_all_people_f papf,
fnd_user usr
WHERE
papf.person_id = usr.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
AND usr.user_id = l_reported_by_id;
Line: 2265
document := document || 'Last Updated By ';
Line: 2267
document := document || gri.last_updated_by || ' ';
Line: 2336
select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
from pa_progress_report_vals prval, pa_progress_report_vers prver
where prval.version_id = document_id
and prver.version_id = document_id
and region_code = 'PA_PROGRESS_GENERAL_TOP';
Line: 2343
SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
pa_progress_report_vers pprv,
pa_lookups pl,
pa_project_parties ppp,
per_all_people_f papf
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS'
and papf.person_id = ppp.resource_source_id
AND pprv.reported_by = ppp.resource_id
AND ppp.object_id = pprv.object_id
and ppp.object_type = pprv.object_type
and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
Line: 2359
select max(item_type), max(item_key) from pa_wf_processes,
pa_progress_report_vers pprv
where wf_type_code = 'Progress Report'
and entity_key2= document_id
AND entity_key1 = pprv.object_id
AND pprv.object_type = 'PA_PROJECTS'
AND pprv.version_id = document_id;
Line: 2369
SELECT pprv.*, pl.meaning progress_status FROM
pa_progress_report_vers pprv,
pa_lookups pl
where lookup_type like 'PROGRESS_SYSTEM_STATUS'
AND pl.lookup_code = pprv.progress_status_code
and pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS';*/
Line: 2379
select usr.user_id, usr.customer_id, usr.user_name,papf.email_address,papf.full_name person_name
from per_all_people_f papf,
fnd_user usr
WHERE
papf.person_id = usr.employee_id
and trunc(sysdate)
between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
AND usr.user_id = l_reported_by_id;
Line: 2489
SELECT
/* Changed the query to base tables pa_projects_all and hr_all_organization_units instead of view
pa_project_lists_v to improve performance. Bug :4940945 */
ppa.name,
PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID) customer_name,
--project_type,
--to_char(project_value) as project_value,
--psi_projfunc_currency_code as project_currency_code,
--person_id,
PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME(PPA.PROJECT_ID) person_name,
ppa.segment1,
--start_date,
--completion_date,
hou.name carrying_out_organization_name
--project_status_name,
--description
FROM pa_projects_all ppa, hr_all_organization_units hou
WHERE ppa.project_id = l_project_id
and ppa.CARRYING_OUT_ORGANIZATION_ID = hou.ORGANIZATION_ID
and rownum = 1;
Line: 2618
/* SELECT pprv.report_content FROM
pa_progress_report_vers pprv
where
pprv.version_id = document_id
AND pprv.object_type = 'PA_PROJECTS'*/
select PAGE_CONTENT from PA_PAGE_CONTENTS
where object_Type = 'PA_PROGRESS_REPORTS'
and pk1_value = document_id;