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_notification_list & get_name
23/06/04 rasinha Bug# 3691192 FP M Changes
Added three procedures namely CLOSE_CI_ACTION,KEEP_OPEN and CANCEL_NOTIF_AND_ABORT_WF.
CLOSE_CI_ACTION and KEEP_OPEN are called from the PAWFCIAC workflow funtions.
CLOSE_CI_ACTION closes an Action without signing it off,
KEEP_OPEN keeps the action open and registers any comment given by the user and
CANCEL_NOTIF_AND_ABORT_WF cancels any open notification for an action and also aborts the workflow.
Also added some item attributes in the workflow PAWFCIAC.
30-07-04 rasinha Modified the file for Bug# 3802238.Captured the Sign-off flag value from workflow Notification
and updated the ci action in CI_CLOSE_ACTION_PROCEDURE.
18-08-04 mumohan Bug#3838957: Added the condition to exclude the end dated users in the cursor
get_notification_list, is_user_valid and get_name.
27/08/04 sanantha Bug 3787169. call the api modify_wf_clob_content
before passing the clob to workflow
24-09-04 rasinha Bug 3877985. Modified the file update the who columns in the procedure
CLOSE_CI_ACTION and KEEP_OPEN with action assignee and not by fnd_global.user_id.
01-12-04 sukhanna Bug 3974641. Replacing PA_CI_CI_REVIEW_LAYOUT AK region name with the xml file name CiCiReviewPG.
10-Dec-04 rasinha Bug 4049901. Modified the procedure to set the role_display_name for
Action Assignee notification to the full name of the action assignee.
03-Aug-05 raluthra Bug 4527617. Replaced the usage of fnd_user.
customer_id with fnd_user.person_party_id
for R12 ATG Mandate.
10-Aug-05 rasinha Bug# 4527911:
1)Added the procedure close_notification to close an open action notification.
2)Modified the KEEP_OPEN procedure to avoid adding comments if it is already being done.
3)Modifed the procedure CANCEL_NOTIF_AND_ABORT_WF to find out open notification for an action
and canel the notification. This is called when an action is cancelled.
08-Sep-05 raluthra Bug 4565156. Added code for Manual NOCOPY Changes
for usage of same variable for In and Out parameter.
01-Feb-06 vgottimu Bug 4923945. Changed the cursor cur_ci_status_n_owner query,
pa_ci_list_v is replaced with the base table pa_control_items and
included table hz_parties to get the owner name.
26-Apr-07 vvjoshi Bug#5962401:Modified set_workflow_attributes procedure to set expiration date for adhoc role.
25-Jun-07 rballamu Bug#6053648:Modified change_status_approved to initialize the application context.
=============================================================================*/
G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
SELECT MAX(item_key)
FROM pa_wf_processes
WHERE item_type = p_item_type
AND description = p_process_name
AND entity_key2 = p_ci_id
AND entity_key1 = l_project_id
AND wf_type_code = 'Control Item';
SELECT project_id
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT 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 => 'Control Item'
,p_item_type => p_item_type
,p_item_key => l_item_key
,p_entity_key1 => l_project_id
,p_entity_key2 => p_ci_id
,p_description => p_process_name
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT wf_failure_status_code
FROM pa_project_statuses ps,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and ci.status_code = ps.project_status_code
and ps.status_type = 'CONTROL_ITEM'
and ps.workflow_item_type = p_item_type
and ps.enable_wf_flag = 'Y'
and ps.wf_failure_status_code is NOT NULL;
SELECT wf_success_status_code
FROM pa_project_statuses ps,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and ci.status_code = ps.project_status_code
and ps.status_type = 'CONTROL_ITEM'
and ps.workflow_item_type = p_item_type
and ps.enable_wf_flag = 'Y'
and ps.wf_success_status_code is NOT NULL;
select user_id
from fnd_user
where user_name = p_user_name;
SELECT user_name
FROM fnd_user
WHERE user_id = FND_GLOBAL.user_id;
SELECT status_code
FROM pa_control_items
WHERE ci_id = l_ci_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#3838957 */
-- 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.
and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
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 trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
and 'PER:' || fu.employee_id = hp.orig_system_reference;
/* 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_CONTROL_ITEMS'
and pk1_value = document_id;
SELECT project_id
FROM pa_control_items
WHERE ci_id = c_ci_id;
SELECT pa_workflow_itemkey_s.nextval
INTO l_item_key
from dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => l_wf_type_code
,p_item_type => p_item_type
,p_item_key => l_item_key
,p_entity_key1 => l_entity_key1
,p_entity_key2 => l_entity_key2
,p_description => p_process_name
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
l_last_updated pa_ci_actions_v.last_update_date%TYPE;
SELECT
pci.project_id,
pci.date_required,
pct.name ci_type_name,
pct.short_name ci_type_sn,
pci.ci_number,
pci.owner_id,
summary,
priority_code,
pcc.class_code classification,
pci.record_version_number record_version_number,
pl.meaning ci_type_class,
pcb.ci_type_class_code
FROM pa_control_items pci,
pa_ci_types_tl pct,
pa_ci_types_b pcb,
pa_lookups pl,
pa_class_codes pcc
WHERE ci_id = p_ci_id
and pci.ci_type_id = pct.ci_type_id
and pl.lookup_code = pcb.ci_type_class_code
AND pcb.ci_type_id = pct.ci_type_id
and pl.lookup_type = 'PA_CI_TYPE_CLASSES'
AND pcc.class_code_id = pci.classification_code_id;
SELECT
customer_name,
person_name,
carrying_out_organization_name
FROM pa_project_lists_v
WHERE project_id = l_project_id;
select ci_action_number,
date_required,
create_name,
comment_text,
assign_party_id,
create_party_id,
status_code,
cancel_comment,
record_version_number,
action_type_code,
action_type,
last_update_date,
status_meaning,
sign_off_required_flag,
sign_off_required_flag_meaning--FP.M.IB1 Sanity
from pa_ci_actions_v
where ci_action_id = c_action_id;
select pci.status_code,
pps.project_status_name,
pci.owner_id,
hzp.party_name
from pa_control_items pci,
pa_project_statuses pps,
hz_parties hzp
where pci.ci_id = p_ci_id AND
pci.status_code=pps.project_status_code AND
hzp.party_id = pci.owner_id;
select fu.user_name, hp.party_name, hp.email_address
from fnd_user fu,
hz_parties hp
where
fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
and hp.party_id = c_owner_id
and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
union
select fu.user_name, hp.party_name, hp.email_address
from
fnd_user fu,
hz_parties hp,
per_all_people_f papf
where
fu.employee_id = Substr(hp.orig_system_reference, 5, Length(hp.orig_system_reference))
AND 'PER:' = Substr(hp.orig_system_reference,1,4)
and hp.party_id = c_owner_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) /* Bug#3838957 */
and papf.person_id = fu.employee_id;
Select
lkp.meaning
from
pa_lookups lkp
where
lkp.lookup_type = c_lookup_type
and lkp.lookup_code = c_lookup_code;
select comment_text
from pa_ci_comments
where ci_comment_id =
(select max(ci_comment_id) from pa_ci_comments where ci_id = c_ci_id and type_code = 'CHANGE_OWNER');
select PA_UTILS.get_party_id(created_by) party_id
from pa_ci_actions
start with ci_action_id = c_action_id
connect by prior source_ci_action_id = ci_action_id;
select full_name
from per_all_people_f
where party_id= p_party_id
and sysdate between nvl(effective_start_date,sysdate) and nvl(effective_end_date,sysdate)
and rownum=1;
l_last_updated,
l_action_status_meaning,
l_sign_off_req_flag,
l_sign_off_req_meaning; --FP.M.IB1 Sanity
, 'LAST_UPDATE_DATE'
, l_last_updated);
select rowid
from pa_ci_actions
where ci_action_id = p_ci_action_id
and record_version_number = p_record_version_number
for update;
select ci_id, type_code, assigned_to, date_required,
sign_off_required_flag, source_ci_action_id, created_by, creation_date
from pa_ci_actions
where ci_action_id = p_ci_action_id;
select record_version_number
from pa_control_items
where ci_id = l_ci_id;
select user_id
from fnd_user
where person_party_id = p_party_id
and sysdate between trunc(start_date) and nvl(trunc(end_date),sysdate)
and rownum = 1;
--Fetching the fnd user_id for the action asignee to update who columns
OPEN get_fnd_usr( l_assign_party_id);
PA_CI_ACTIONS_PKG.UPDATE_ROW(
P_CI_ACTION_ID => l_ci_action_id,
P_CI_ID => l_ci_id,
P_STATUS_CODE => 'CI_ACTION_CLOSED',
P_TYPE_CODE => l_type_code,
P_ASSIGNED_TO => l_assigned_to,
P_DATE_REQUIRED => l_date_required,
P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
P_DATE_CLOSED => sysdate,
P_SIGN_OFF_FLAG => l_user_sign_off,
P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
P_LAST_UPDATED_BY => l_fnd_usr_id, --Modified for bug# 3877985
P_CREATED_BY => l_created_by,
P_CREATION_DATE => l_creation_date,
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATE_LOGIN => l_fnd_usr_id, --Modified for bug# 3877985
P_RECORD_VERSION_NUMBER => l_record_version_number);
p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
p_last_update_login => l_fnd_usr_id, --Added for bug# 3877985
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => FND_API.g_false,
p_validate_only => FND_API.g_true,
p_max_msg_count => FND_API.g_miss_num,
p_ci_id =>l_CI_ID,
p_num_of_actions => -1,
p_record_version_number =>l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
p_last_update_login => l_fnd_usr_id --Added for bug# 3877985
);
select ci_id, type_code, assigned_to, date_required,
sign_off_required_flag, source_ci_action_id, created_by, creation_date
from pa_ci_actions
where ci_action_id = p_ci_action_id;
select user_id
from fnd_user
where person_party_id = p_party_id
and sysdate between trunc(start_date) and nvl(trunc(end_date),sysdate)
and rownum = 1;
cursor is_comment_inserted_cur(p_ci_action_id NUMBER) IS
select 1
from pa_ci_comments
where ci_action_id = p_ci_action_id
and type_code='UNSOLICITED';
select record_version_number
from pa_control_items
where ci_id = l_ci_id;
--Fetching the fnd user_id for the action asignee to update who columns
OPEN get_fnd_usr( l_assign_party_id);
In case 2 the user comment will already be inserted in PA_CI_COMMENTS from the application, so no need to insert it again.
IF there is already a line in pa_ci_comments for the action with type_code UNSOLICITED it means that the comment is already
inserted.*/
OPEN is_comment_inserted_cur(l_ci_action_id);
FETCH is_comment_inserted_cur into l_num_var;
IF is_comment_inserted_cur%NOTFOUND THEN
CLOSE is_comment_inserted_cur;
p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
p_last_update_login => l_fnd_usr_id, --Added for bug# 3877985
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
CLOSE is_comment_inserted_cur;
select wfi.notification_id,
wfi.item_type,
wfi.item_key
from pa_wf_processes pwp,
wf_item_activity_statuses_v wfi
where pwp.entity_key2=p_action_id
and pwp.item_type='PAWFCIAC'
and wfi.item_type = pwp.item_type
and wfi.item_key = pwp.item_key
and wfi.activity_type_code='NOTICE'
and wfi.activity_status_code='NOTIFIED';
that also is updated in the notification.
2) If on take action page the Action is kept open then the notification is
closed with result 'AAA_KEEP_OPEN'.If user provides a comment or sign-off
then that also is updated in the notification.
3) If on take action page the Action is reassigned then the notification is
closed with result 'BBB_CLOSE'
***********************************************************************************/
PROCEDURE close_notification(
p_item_type in VARCHAR2,
p_item_key in VARCHAR2,
p_nid in NUMBER,
p_action in VARCHAR2,
p_sign_off_flag in VARCHAR2,
p_response in VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_sign_off_requested VARCHAR2(1);