[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.assignment_id,
ppa.assignment_name,
ppa.assignment_effort,
ppa.additional_information,
ppa.description,
ppa.note_to_approver,
ppa.project_id,
ppa.resource_id,
ppa.start_date,
ppa.end_date,
ppa.status_code,
ppa.apprvl_status_code,
ppa.pending_approval_flag,
ppa.assignment_type,
ppa.revenue_bill_rate,
ppa.revenue_currency_code,
ppa.bill_rate_override,
ppa.bill_rate_curr_override,
ppa.markup_percent_override,
ppa.fcst_tp_amount_type_name,
ppa.tp_rate_override,
ppa.tp_currency_override,
ppa.tp_calc_base_code_override,
ppa.tp_percent_applied_override,
ppa.work_type_name,
ppa.transfer_price_rate, -- Added for bug 3051110
ppa.transfer_pr_rate_curr
FROM pa_project_assignments_v ppa
WHERE assignment_id = p_assignment_id;
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
AND plks.lookup_code = p_override_basis_code;
SELECT ps.wf_success_status_code,
ps.wf_failure_status_code,
ps.project_status_name
FROM pa_project_statuses ps
WHERE project_status_code = c_status_code;
SELECT per.full_name resource_name,
rta.person_id resource_person_id,
rta.resource_id resource_id,
hou.name resource_organization_name,
assign.supervisor_id manager_id
FROM per_people_f per,
per_assignments_f assign,
hr_all_organization_units hou,
pa_resource_txn_attributes rta
WHERE rta.resource_id = l_resource_id
AND rta.person_id = per.person_id
AND rta.person_id = assign.person_id
AND assign.primary_flag = 'Y'
AND assign.assignment_type in ('E','C')
AND hou.organization_id = assign.organization_id
AND p_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
AND p_start_date BETWEEN per.effective_start_date AND per.effective_end_date /* 2983985 - Added this condition */
;
SELECT pap.project_id project_id,
pap.name name,
pap.segment1 segment1,
pap.carrying_out_organization_id carrying_out_organization_id,
pap.location_id,
hr.name organization_name,
NVL(pt.administrative_flag,'N') admin_flag
FROM pa_projects_all pap,
hr_all_organization_units hr,
pa_project_types_all pt
WHERE pap.project_id = l_project_id
AND pap.carrying_out_organization_id =
hr.organization_id
AND pap.org_id = pt.org_id -- Added for Bug 5389093
AND pt.project_type = pap.project_type;
SELECT assignment_effort prev_effort,
(trunc(end_date) -
(trunc(start_date)+1)) prev_duration
FROM pa_assignments_history
WHERE assignment_id = l_assignment_id
AND nvl(last_approved_flag,'N') = 'Y';
SELECT assignment_effort prev_effort,
(trunc(end_date) - trunc(start_date) + 1) prev_duration
FROM pa_assignments_history
WHERE assignment_id = l_assignment_id
AND nvl(last_approved_flag,'N') = 'Y';
insert_into_temp ('msgs in stack inside start_workflow is',
fnd_msg_pub.count_msg);
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'ASSIGNMENT_APPROVAL'
,p_item_type => p_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(l_projects_rec.project_id)
,p_entity_key2 => to_char(p_assignment_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT ps.wf_success_status_code,
ps.wf_failure_status_code,
ps.workflow_item_type,
ps.workflow_process,
ps.project_system_status_code ,
ps.enable_wf_flag
FROM pa_project_statuses ps
WHERE project_status_code = p_status_code;
SELECT NVL(pending_approval_flag,'N')
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
pa_schedule_pvt.update_asgn_wf_success (
P_ASSIGNMENT_ID => l_assignment_id
,P_IS_NEW_ASSIGNMENT_FLAG => l_is_new_assignment_flag
,P_SCH_EXCEPTION_ID => l_schedule_exception_id
,P_SUCCESS_STATUS_CODE => l_asgmt_success_status_code
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data );
pa_schedule_pvt.update_asgn_wf_failure
(p_assignment_id => l_assignment_id
,p_is_new_assignment_flag => l_is_new_assignment_flag
,p_sch_exception_id => l_schedule_exception_id
,p_failure_status_code => l_asgmt_failure_status_code
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
UPDATE pa_wf_ntf_performers
SET current_approver_flag =
(DECODE(user_name,l_approver_user_name,'Y','N'))
WHERE item_type = itemtype
AND item_key = itemkey
AND object_id1 = l_assignment_id;
UPDATE pa_project_assignments
SET pending_approval_flag = l_pending_approval_flag,
record_version_number = record_version_number + 1
WHERE assignment_id = p_assignment_id;
SELECT record_version_number FROM
pa_project_assignments
WHERE assignment_id = p_assignment_id;
pa_assignment_approval_pvt.update_approval_status
(p_assignment_id => p_assignment_id,
p_action_code => p_result_type,
p_record_version_number => l_record_version_number,
x_apprvl_status_code => l_apprvl_status_code,
x_change_id => l_change_id,
x_record_version_number => l_out_record_version_number,
x_return_status => l_return_status ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT USER_NAME
FROM pa_wf_ntf_performers
WHERE user_name = l_wf_context_user
AND wf_type_code = 'ASSIGNMENT_APPROVAL'
AND item_type = itemtype
AND item_key = itemkey;
SELECT object_id1, object_id2
FROM pa_wf_ntf_performers
WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
AND item_type = itemtype
AND item_key = itemkey
AND current_approver_flag = 'Y';
UPDATE pa_wf_ntf_performers
SET approver_comments = substr(l_comment,1,255)
WHERE item_type = itemtype
AND item_key = itemkey
AND object_id1 = l_assignment_id
AND user_name = l_approver_user_Name;
INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
OBJECT_ID1, OBJECT_ID2, USER_NAME,
USER_TYPE, CURRENT_APPROVER_FLAG)
VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
l_object_id1, l_object_id2, l_wf_context_user,
'REASSIGNEE', 'Y');
UPDATE pa_wf_ntf_performers
SET current_approver_flag = (DECODE(user_name,l_wf_context_user,'Y','N'))
WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
AND item_type = itemtype
AND item_key = itemkey
AND current_approver_flag = 'Y';
INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
OBJECT_ID1, OBJECT_ID2, USER_NAME,
USER_TYPE, CURRENT_APPROVER_FLAG)
VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
1, 2, l_wf_context_user,
'REASSIGNEE', 'Y');
SELECT item_type, item_key
FROM wf_item_activity_statuses
WHERE notification_id = document_id;
INSERT INTO pa_wf_ntf_performers (
Wf_Type_Code,Item_Type,
Item_Key,object_id1,
object_id2,User_Name,User_Type,
Routing_Order,Current_Approver_flag
)
VALUES
( p_wf_type_code,
p_item_type,
p_item_key,
p_object_id1,
p_object_id2,
p_in_performers_tbl(i).User_Name,
p_in_performers_tbl(i).Type,
p_in_performers_tbl(i).Routing_Order,
p_current_approver_flag );
SELECT User_Name , User_Type FROM pa_wf_ntf_performers
WHERE wf_type_code = p_wf_type_code
AND item_type = p_item_type
AND item_key = p_item_key
AND routing_order = p_routing_order
AND object_id1 = p_object_id1;
SELECT user_Name,approver_comments
FROM pa_wf_ntf_performers
WHERE item_type = p_apprvl_item_type
AND item_key = p_apprvl_item_key
AND object_id1 = p_assignment_id
AND approver_comments IS NOT NULL;
SELECT customer_id FROM pa_project_customers
WHERE project_id = p_project_id;
SELECT ppc.customer_id,rac.customer_name
FROM pa_project_customers ppc,
ra_customers rac
WHERE ppc.project_id = p_project_id
AND rac.customer_id = ppc.customer_id ;
SELECT customer_name
FROM ra_customers
WHERE customer_id = l_customer_id;
SELECT substrb(party.party_name,1,50) customer_name
FROM hz_parties party, hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = l_customer_id
and cust_acct.party_id = party.party_id ;
PROCEDURE Delete_Assignment_WF_Records (p_assignment_id IN pa_project_assignments.assignment_id%TYPE,
p_project_id IN pa_project_assignments.project_id%TYPE)
IS
/*CURSOR get_project_id IS
SELECT project_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
DELETE FROM pa_wf_processes
WHERE entity_key1 = to_char(p_project_id)
AND entity_key2 = to_char(p_assignment_id)
AND wf_type_code = 'ASSIGNMENT_APPROVAL';
DELETE FROM pa_wf_ntf_performers
WHERE wf_type_code in ('ASSIGNMENT_APPROVAL','REJECTION_FYI','APPROVAL_FYI','CANCELLATION_FYI')
AND object_id1 = p_assignment_id;
END Delete_Assignment_WF_Records;
,p_update_info_doc IN VARCHAR2
,p_forwarded_from IN VARCHAR2
,p_note_to_approvers IN VARCHAR2);
,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_project_name IN VARCHAR2
,p_project_number IN VARCHAR2
,p_project_manager IN VARCHAR2
,p_project_org IN VARCHAR2
,p_project_cus IN VARCHAR2
,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --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
IS
l_itemkey VARCHAR2(30);
log_message('Value of updated info doc:' || p_update_info_doc);
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
SELECT user_name
INTO l_ntfy_apprvl_recipient_name
FROM pa_wf_ntf_performers
WHERE group_id = p_group_id
AND approver_group_id = p_approver_group_id
AND routing_order = p_routing_order
AND rownum = 1;
SELECT count(object_id1)
INTO l_number_of_assignments
FROM pa_wf_ntf_performers pf,
pa_project_assignments asmt
WHERE pf.group_id = p_group_id
AND pf.approver_group_id = p_approver_group_id
AND pf.routing_order = p_routing_order
AND pf.object_id1 = asmt.assignment_id
AND asmt.apprvl_status_code <> PA_ASSIGNMENT_APPROVAL_PUB.g_rejected;
, aname => 'UPDATED_INFO_DOC'
, documentid => p_update_info_doc );
, aname => 'UPDATED_INFO_DOC'
, documentid => p_update_info_doc );
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
,p_item_type => l_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(p_group_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
SELECT assignment_effort prev_effort,
(trunc(end_date) -
(trunc(start_date)+1)) prev_duration
FROM pa_assignments_history
WHERE assignment_id = l_assignment_id
AND nvl(last_approved_flag,'N') = 'Y';
SELECT
ppa.assignment_name,
ppa.assignment_effort,
ppa.additional_information,
ppa.description,
ppa.start_date,
ppa.end_date,
ppa.apprvl_status_code,
ppa.revenue_bill_rate,
ppa.revenue_currency_code,
ppa.bill_rate_override,
ppa.bill_rate_curr_override,
ppa.markup_percent_override,
ppa.fcst_tp_amount_type_name,
ppa.tp_rate_override,
ppa.tp_currency_override,
ppa.tp_calc_base_code_override,
ppa.tp_percent_applied_override,
ppa.work_type_name,
ppa.transfer_price_rate, -- Added for bug 3051110
ppa.transfer_pr_rate_curr
FROM pa_project_assignments_v ppa
WHERE assignment_id = p_assignment_id;
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
AND plks.lookup_code = p_override_basis_code;
SELECT res.resource_name,
hr.name
FROM pa_resources_denorm res,
hr_all_organization_units hr
WHERE res.resource_id = l_resource_id
AND hr.organization_id = res.resource_organization_id
AND l_start_date between resource_effective_start_date and resource_effective_end_date;
SELECT per.full_name resource_name,
hou.name
FROM per_people_f per,
per_assignments_f assign,
hr_all_organization_units hou,
pa_resource_txn_attributes rta
WHERE rta.resource_id = l_resource_id
AND rta.person_id = per.person_id
AND rta.person_id = assign.person_id
AND assign.primary_flag = 'Y'
AND assign.assignment_type in ('E','C')
AND hou.organization_id = assign.organization_id
AND p_start_date BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND p_start_date BETWEEN per.effective_start_date
AND per.effective_end_date;
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id = p_assignment_id;
SELECT resource_id
INTO l_resource_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT apprvl_status_code
INTO l_aprvl_status
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT res.person_id
INTO l_ntfy_apprvl_rect_person_id
FROM pa_resources_denorm res
WHERE res.resource_id = l_resource_id
AND rownum = 1;
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
,p_item_type => l_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(p_assignment_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_num_apr_asgns IN NUMBER
,p_num_rej_asgns IN NUMBER
,p_project_name IN VARCHAR2
,p_project_number IN VARCHAR2
,p_project_manager IN VARCHAR2
,p_project_org IN VARCHAR2
,p_project_cus IN VARCHAR2
,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
,p_notified_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
IS
l_assignment_id NUMBER;
l_selected_roles_url VARCHAR2(600);
l_selected_roles2_url VARCHAR2(600);
l_selected_resources_url VARCHAR2(600);
l_selected_resources2_url VARCHAR2(600);
SELECT
ppa.project_id,
ppa.assignment_name,
ppa.assignment_effort,
ppa.additional_information,
ppa.description,
ppa.start_date,
ppa.end_date,
ppa.revenue_bill_rate,
ppa.revenue_currency_code,
ppa.bill_rate_override,
ppa.bill_rate_curr_override,
ppa.markup_percent_override,
ppa.fcst_tp_amount_type_name,
ppa.tp_rate_override,
ppa.tp_currency_override,
ppa.tp_calc_base_code_override,
ppa.tp_percent_applied_override,
ppa.work_type_name,
hr.name
FROM pa_project_assignments_v ppa,
pa_resources_denorm res,
hr_all_organization_units hr
WHERE ppa.assignment_id = l_assignment_id
AND res.resource_id = ppa.resource_id
AND ppa.start_date BETWEEN res.resource_effective_start_date
AND res.resource_effective_end_date
AND hr.organization_id = res.resource_organization_id;
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
AND plks.lookup_code = p_override_basis_code;
SELECT object_id1
INTO l_assignment_id
FROM pa_wf_ntf_performers
WHERE group_id = p_group_id
AND rownum = 1;
l_selected_roles_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275' ||
'&akRegionCode=PA_SELECTED_ROLES_LAYOUT&paCallingPage=WF_Notifications' ||
'&paProjectId=' || l_assignments_rec.project_id || '&paNotifiedId=' ||
p_notified_id || '&paGroupId=' || p_group_id || '&paApprovalStatus=Approved&addBreadCrumb=RP';
l_selected_roles2_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275' ||
'&akRegionCode=PA_SELECTED_ROLES_LAYOUT&paCallingPage=WF_Notifications' ||
'&paProjectId=' || l_assignments_rec.project_id || '&paNotifiedId=' ||
p_notified_id || '&paGroupId=' || p_group_id || '&paApprovalStatus=Rejected&addBreadCrumb=RP';
l_selected_resources_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275' ||
'&akRegionCode=PA_SELECTED_RESOURCES_LAYOUT&paCallingPage=WF_Notifications' ||
'&paProjectId=' || l_assignments_rec.project_id || '&paNotifiedId=' ||
p_notified_id || '&paGroupId=' || p_group_id || '&paApprovalStatus=Approved&addBreadCrumb=RP';
l_selected_resources2_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275' ||
'&akRegionCode=PA_SELECTED_RESOURCES_LAYOUT&paCallingPage=WF_Notifications' ||
'&paProjectId=' || l_assignments_rec.project_id || '&paNotifiedId=' ||
p_notified_id || '&paGroupId=' || p_group_id || '&paApprovalStatus=Rejected&addBreadCrumb=RP';
, aname => 'SELECTED_RESOURCES_URL'
, avalue => l_selected_resources_url);
, aname => 'SELECTED_RESOURCES2_URL'
, avalue => l_selected_resources2_url);
ELSIF (p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE )
THEN
wf_engine.SetItemAttrDocument
( itemtype => l_wf_item_type
, itemkey => l_itemkey
, aname => 'UPDATED_INFO_DOC'
, documentid => p_update_info_doc );
, aname => 'SELECTED_ROLES_URL'
, avalue => l_selected_roles_url);
, aname => 'SELECTED_ROLES2_URL'
, avalue => l_selected_roles2_url);
,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_num_apr_asgns IN NUMBER
,p_num_rej_asgns IN NUMBER
,p_project_name IN VARCHAR2
,p_project_number IN VARCHAR2
,p_project_manager IN VARCHAR2
,p_project_org IN VARCHAR2
,p_project_cus IN VARCHAR2
,p_submitter_user_name IN VARCHAR2
,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --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
IS
l_itemkey VARCHAR2(30);
SELECT distinct res.manager_id
FROM pa_resources_denorm res,
pa_project_assignments asgn,
pa_wf_ntf_performers ntf,
fnd_user fnd
WHERE ntf.group_id = p_group_id
AND asgn.assignment_id = l_assignment_id
AND asgn.assignment_id = ntf.object_id1
AND res.resource_id = asgn.resource_id
AND asgn.start_date BETWEEN res.resource_effective_start_date
AND res.resource_effective_end_date
AND res.manager_id = fnd.employee_id
AND fnd.user_name <> ntf.user_name;
SELECT distinct per.person_id as staffing_mgr_id
FROM fnd_grants fg,
fnd_objects fob,
fnd_user fnd,
pa_resources_denorm res,
/* Commenting this for performance tuning Bug#2499051
(select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id
from dual) temp, */
pa_project_assignments asgn,
pa_wf_ntf_performers ntf,
wf_roles wf,
per_all_people_f per
WHERE ntf.group_id = p_group_id
AND asgn.assignment_id = l_assignment_id
AND asgn.assignment_id = ntf.object_id1
AND res.resource_id = asgn.resource_id
AND asgn.start_date BETWEEN res.resource_effective_start_date
AND res.resource_effective_end_date
--AND fnd.employee_id = to_number(substr(fg.grantee_key,instr(fg.grantee_key,':')+1))
AND fnd.employee_id = per.person_id
AND per.party_id = wf.orig_system_id
AND sysdate between per.effective_start_date and per.effective_end_date
AND wf.orig_system = 'HZ_PARTY'
AND fg.grantee_key = wf.name
AND fnd.user_name <> ntf.user_name
AND fg.instance_pk1_value = TO_CHAR(res.resource_organization_id)
AND fg.instance_type = 'INSTANCE'
AND fg.object_id = fob.object_id
AND fob.obj_name = 'ORGANIZATION'
AND fg.menu_id = pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') /* temp.menu_id commented for bug#2499051 */
AND fg.grantee_type = 'USER'
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
AND trunc(NVL(fg.END_DATE, SYSDATE+1));
SELECT distinct user_name,
user_type
FROM pa_wf_ntf_performers
WHERE wf_type_code = 'MASS_APPROVAL_FYI'
AND group_id = p_group_id;
SELECT NVL(pt.administrative_flag,'N') admin_flag
INTO l_admin_project
FROM pa_projects_all pap,
pa_project_types_all pt
WHERE pap.project_id = p_project_id
AND pt.project_type = pap.project_type
AND pap.org_id = pt.org_id;
SELECT apprvl_status_code
INTO l_aprvl_status_code
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id_tbl(i);
log_message('Insert in loop:' || j);
INSERT INTO pa_wf_ntf_performers(
WF_TYPE_CODE
,ITEM_TYPE
,ITEM_KEY
,OBJECT_ID1
,GROUP_ID
,USER_NAME
,USER_TYPE
,ROUTING_ORDER )
VALUES ('MASS_APPROVAL_FYI'
,'-1'
,'-1'
,l_assignment_id
,p_group_id
,l_approval_nf_rects_rec.user_name
,l_approval_nf_rects_rec.type
,l_approval_nf_rects_rec.routing_order
);
log_message('After inserting manager records');
DELETE
FROM pa_wf_ntf_performers
WHERE wf_type_code = 'MASS_APPROVAL_FYI'
AND group_id = p_group_id;
ELSIF (p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE )
THEN
l_wf_process := 'PA_MASS_APRVL_UPD_FP';
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
SELECT employee_id
INTO l_notified_id
FROM fnd_user
WHERE user_name = l_recipients_tbl(k)
AND rownum = 1;
SELECT count(*)
INTO l_num_apr_asgns
FROM pa_res_aprvl_roles_v ar
WHERE ar.notified_id = l_notified_id
AND ar.group_id = p_group_id
AND ar.approval_status = 'ASGMT_APPRVL_APPROVED';
SELECT count(*)
INTO l_num_rej_asgns
FROM pa_res_aprvl_roles_v ar
WHERE ar.notified_id = l_notified_id
AND ar.group_id = p_group_id
AND ar.approval_status = 'ASGMT_APPRVL_REJECTED';
,p_update_info_doc => p_update_info_doc
,p_num_apr_asgns => p_num_apr_asgns
,p_num_rej_asgns => p_num_rej_asgns
,p_project_name => p_project_name
,p_project_number => p_project_number
,p_project_manager => p_project_manager
,p_project_org => p_project_org
,p_project_cus => p_project_cus
,p_conflict_group_id => p_conflict_group_id
,p_notified_id => l_notified_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count );
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
,p_item_type => l_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(p_group_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
l_update_info_doc VARCHAR2(2000);
l_update_info_doc := wf_engine.getItemAttrDocument
( itemtype => itemtype
, itemkey => itemkey
, aname => 'UPDATED_INFO_DOC' );
,p_update_info_doc => l_update_info_doc
,p_forwarded_from => l_forwarded_from
,p_note_to_approvers => l_note_to_approvers);
SELECT record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT approval_status
FROM pa_wf_ntf_performers
WHERE group_id = p_group_id
AND object_id1 = p_assignment_id
AND rownum = 1;
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row (
p_assignment_id => p_assignment_id
,p_record_version_number => l_record_version_number
,p_apprvl_status_code => l_apprvl_status_code
,x_return_status => l_return_status );
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id = p_assignment_id;
SELECT user_name
FROM pa_wf_ntf_performers ntf
WHERE ntf.group_id = p_group_id
AND ntf.routing_order = p_routing_order + 1
AND ntf.object_id1 = c_assignment_id;
SELECT record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SAVEPOINT UPDATE_APPROVAL_STATUS;
log_message('Before calling Update approval status');
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
( p_assignment_id => p_assignment_id
,p_action_code => p_action_code
,p_note_to_approver => null
,p_record_version_number => l_record_version_number1
,x_apprvl_status_code => l_aprvl_status_code
,x_change_id => l_change_id
,x_record_version_number => l_record_version_number2
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
log_message('After calling Update approval status');
ROLLBACK TO UPDATE_APPROVAL_STATUS;
SELECT user_id
INTO l_submitter_user_id
FROM fnd_user
WHERE user_name = p_submitter_user_name;
SELECT resource_id
INTO l_resource_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
ROLLBACK TO UPDATE_APPROVAL_STATUS;
,p_update_info_doc IN VARCHAR2
,p_forwarded_from IN VARCHAR2
,p_note_to_approvers IN VARCHAR2)
IS
l_approval_status pa_project_assignments.apprvl_status_code%TYPE;
SELECT distinct user_name
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = p_group_id
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
AND ntf.routing_order = p_routing_order + 1; */
SELECT distinct ntf.user_name
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn,
pa_wf_ntf_performers ntf1
WHERE ntf.group_id = p_group_id
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
AND ntf.routing_order = p_routing_order + 1
AND ntf1.group_id = p_group_id
AND ntf1.object_id1 = asgn.assignment_id
AND ntf1.routing_order = p_routing_order
AND ntf1.approver_group_id = p_approver_group_id;
SELECT 'Y'
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = p_group_id
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
SELECT pap.project_id project_id,
pap.name name,
pap.segment1 segment1,
pap.carrying_out_organization_id carrying_out_organization_id,
pap.location_id,
hr.name organization_name,
NVL(pt.administrative_flag,'N') admin_flag
FROM pa_projects_all pap,
hr_all_organization_units hr,
pa_project_types_all pt
WHERE pap.project_id = l_project_id
AND pap.carrying_out_organization_id = hr.organization_id
AND pap.org_id = pt.org_id -- Added for Bug 5389093
AND pt.project_type = pap.project_type;
l_update_info_doc VARCHAR2(2000);
SELECT 'Y'
FROM PA_REPORTING_EXCEPTIONS
WHERE user_id = l_submitter_user_id
AND context = PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1
AND sub_context = 'MASS_APPROVAL'
AND source_identifier1 = 'PAWFAAP'
AND source_identifier2 = p_group_id;
SELECT PA_WF_NTF_PERFORMERS_S.nextval
INTO l_approver_group_id
FROM dual;
UPDATE pa_wf_ntf_performers
SET approver_group_id = l_approver_group_id
WHERE group_id = p_group_id
AND user_name = rec.user_name
AND routing_order = p_routing_order + 1; */
UPDATE pa_wf_ntf_performers
SET approver_group_id = l_approver_group_id
WHERE group_id = p_group_id
AND user_name = rec.user_name
AND routing_order = p_routing_order + 1
AND object_id1 = p_assignment_id_tbl(k)
AND p_approval_status_tbl(k) = PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
SELECT ntf.object_id1
BULK COLLECT INTO l_assignment_id_tbl
FROM pa_wf_ntf_performers ntf
WHERE ntf.group_id = p_group_id
AND ntf.routing_order = 1;
SELECT apprvl_status_code
INTO l_approval_status
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id_tbl(i);
,p_update_info_doc => l_update_info_doc
,p_num_apr_asgns => l_num_apr_asgns
,p_num_rej_asgns => l_num_rej_asgns
,p_project_name => l_projects_rec.name
,p_project_number => l_projects_rec.segment1
,p_project_manager => l_project_manager_name
,p_project_org => l_projects_rec.organization_name
,p_project_cus => l_customer_name
,p_submitter_user_name => p_submitter_user_name
,p_conflict_group_id => p_conflict_group_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT user_id
INTO l_submitter_user_id
FROM fnd_user
WHERE user_name = p_submitter_user_name;
,p_update_info_doc => l_update_info_doc
,p_num_apr_asgns => l_num_apr_asgns
,p_num_rej_asgns => l_num_rej_asgns
,p_project_name => l_projects_rec.name
,p_project_number => l_projects_rec.segment1
,p_project_manager => l_project_manager_name
,p_project_org => l_projects_rec.organization_name
,p_project_cus => l_customer_name
,p_submitter_user_name => p_submitter_user_name
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_num_apr_asgns IN NUMBER
,p_num_rej_asgns IN NUMBER
,p_project_name IN VARCHAR2
,p_project_number IN VARCHAR2
,p_project_manager IN VARCHAR2
,p_project_org IN VARCHAR2
,p_project_cus IN VARCHAR2
,p_submitter_user_name IN VARCHAR2
,p_assignment_id IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --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
IS
l_itemkey VARCHAR2(30);
SELECT
ppa.project_id,
ppa.assignment_name,
ppa.assignment_effort,
ppa.additional_information,
ppa.description,
ppa.start_date,
ppa.end_date,
ppa.revenue_bill_rate,
ppa.revenue_currency_code,
ppa.bill_rate_override,
ppa.bill_rate_curr_override,
ppa.markup_percent_override,
ppa.fcst_tp_amount_type_name,
ppa.tp_rate_override,
ppa.tp_currency_override,
ppa.tp_calc_base_code_override,
ppa.tp_percent_applied_override,
ppa.work_type_name,
hr.name
FROM pa_project_assignments_v ppa,
pa_resources_denorm res,
hr_all_organization_units hr
WHERE assignment_id = l_assignment_id
AND res.resource_id = ppa.resource_id
AND ppa.start_date BETWEEN res.resource_effective_start_date
AND res.resource_effective_end_date
AND hr.organization_id = res.resource_organization_id;
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
AND plks.lookup_code = p_override_basis_code;
SELECT count( distinct ( attribute2))
FROM PA_REPORTING_EXCEPTIONS
WHERE context = PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1
AND sub_context = 'MASS_APPROVAL'
AND source_identifier1 = 'PAWFAAP'
AND source_identifier2 = p_group_id;
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
ELSIF (p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE )
THEN
l_wf_process := 'PA_MASS_APRVL_SUB_UPD';
SELECT object_id1
INTO l_assignment_id
FROM pa_wf_ntf_performers
WHERE group_id = p_group_id
AND rownum = 1;
ELSIF (p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS OR
p_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE )
THEN
wf_engine.SetItemAttrDocument
( itemtype => l_wf_item_type
, itemkey => l_itemkey
, aname => 'UPDATED_INFO_DOC'
, documentid => p_update_info_doc );
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
,p_item_type => l_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(p_group_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
l_update_info_doc VARCHAR2(2000);
SELECT 'Y'
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.approver_group_id = l_approver_group_id --uncommented this line for Bug#5662785
AND ntf.routing_order = l_routing_order
AND ntf.user_name = l_ntfy_apprvl_recip_name -- added for bug 5488496
AND ntf.object_id1 = asgn.assignment_id
AND ntf.object_id2 <> 100
AND ( asgn.apprvl_status_code <> PA_ASSIGNMENT_APPROVAL_PUB.g_rejected OR
asgn.apprvl_status_code <> PA_ASSIGNMENT_APPROVAL_PUB.g_approved );
SELECT ntf.object_id1
BULK COLLECT INTO l_assignment_id_tbl
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.approver_group_id = l_approver_group_id
AND ntf.routing_order = l_routing_order
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
l_update_info_doc := wf_engine.getItemAttrDocument
( itemtype => itemtype
, itemkey => itemkey
, aname => 'UPDATED_INFO_DOC' );
,p_update_info_doc => l_update_info_doc
,p_forwarded_from => l_forwarded_from
,p_note_to_approvers => l_note_to_approvers);
SELECT ntf.object_id1
BULK COLLECT INTO l_assignment_id_tbl
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.routing_order = 1
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
SELECT user_id
INTO l_submitter_user_id
FROM fnd_user
WHERE user_name = l_submitter_uname;
SELECT resource_id
INTO l_resource_id
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id_tbl(i);