[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_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.
17-8-09 anuragar Bug 8566495 Changes for E&C enchancement.
25-9-09 anuragar Bug 8942843 Passing attributes pertaining to PAWFCISC only
01-12-2009 anuragar Bug 8855304: Forward port for Bug#8673347 : Passes email_address to createAdhocRole.
changes tagged by 8673347
23-Nov-10 chajha Bug 9689141: Added code to set the Owner for 'PA Issue and Change Workflow' items.
27-DEc-10 chajha Bug 9682267: Modified the code so that the action will not get closed when sign off is
requested and the owner doesn't sign's off.
13-Jan-11 SPOKANAT Bug 10269493: Changed expiration date for adhoc roles to sysdate+15
24 MAY 2011 NISINHA Bug#12553990 Added join on language
01 JUN 2011 SVMOHAMM Bug#12553936 Added new attribures to workflow
14 JUN 2011 SVMOHAMM Bug#12588071 Modified the logic not to populate attribute for action close.
29 JUN 2011 SVMOHAMM Bug#12703270 Added the logic to bypass the TASK_NAME setting for actions.
27 Aug 2011 SVMOHAMM Bug#12859815 Added the logic to avoid duplicate FYI closed notifications for action close.
28 Aug 2011 SVMOHAMM Bug#12860002 populated ACTION_CLOSURE_COMMENT workflow attribute.
27 Oct 2011 JJKUMAR Bug#12839389 Added the logic in procedure CLOSE_CI_ACTION that if a user closes
an action that has sign-off required = Y, but they do not sign off then...
a) the action should stay open
b) the parent control item should not have a reduced number of actions
c) a closed notification should not be sent (as the action is not closed).
d) Closure comments should bot be added
21-Mar-2012 SVMOHAMM Bug#13683760 Added changes for approval process modification.
=============================================================================*/
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 ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps, pa_control_items ci
where ci.ci_id = p_ci_id and ci.ci_type_id = pc.ci_type_id
and ps.project_status_code=pc.project_status_code
and ps.project_system_status_code =pc.project_system_status_code
and ps.project_system_status_code = 'CI_WORKING' and rownum = 1;
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 ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps, pa_control_items ci
where ci.ci_id = p_ci_id and ci.ci_type_id = pc.ci_type_id
and ps.project_status_code=pc.project_status_code
and ps.project_system_status_code =pc.project_system_status_code
and ps.project_system_status_code = 'CI_WORKING' and rownum = 1;
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,
pci.description description,
pci.creation_date creation_date,
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 pct.language = userenv('lang') /*Bug#12553990*/
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,email_address
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;
select ppe.name,
ppe.element_number
from pa_proj_elements ppe,
pa_control_items ci
where
ppe.proj_element_id = ci.object_id
and ppe.project_id = ci.project_id
and ci.object_type = 'PA_TASKS'
and ci_id = p_ci_id ;
select user_name
from fnd_user
where customer_id = p_cus_id;
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;
SELECT status_code into l_action_status_code
from pa_ci_actions
where ci_action_id = l_ci_action_id;
--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 => l_action_status_code,
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);
select ppe.element_number task_number,ppe.name task_name,ppe.description description,
pvsch.scheduled_start_date start_date,pvsch.scheduled_finish_date finish_date,
hz.party_name approver_name
from pa_proj_elements ppe,pa_proj_element_versions ppev,
pa_proj_elem_ver_schedule pvsch,fnd_user fu, hz_parties hz
where ppe.proj_element_id = ppev.proj_element_id
and pvsch.element_version_id = ppev.element_version_id
and ppe.task_approver_id=fu.user_id
and fu.person_party_id=hz.party_id
and ppe.proj_element_id in
(
Select distinct task_id from
pa_resource_assignments pra where
budget_version_id in (
select budget_version_id from pa_budget_versions where ci_id =document_id)
and exists (select 1
from pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_object_relationships por
where ppe.proj_element_id = pra.task_id
and ppe.project_id = pra.project_id
and ppe.link_task_flag = 'Y'
and ppe.type_id = 1
and ppev.proj_element_id = ppe.proj_element_id
and por.object_id_to1 = ppev.element_version_id
and por.object_type_to = 'PA_TASKS'
and por.relationship_type = 'S'
and ppev.financial_task_flag = 'Y')
and not exists (select 1 from pa_tasks where task_id = pra.task_id and project_id = pra.project_id)
);