1 PACKAGE BODY pa_mass_asgmt_trx AS
2 -- $Header: PARMATXB.pls 120.3.12000000.2 2007/09/11 06:12:54 admarath ship $
3
4 PROCEDURE start_mass_asgmt_trx_wf
5 (p_mode IN VARCHAR2
6 ,p_action IN VARCHAR2
7 ,p_resource_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
8 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
9 ,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
10 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
11 ,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
12 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
13 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
14 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
15 ,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
16 ,p_role_list_id IN pa_role_lists.role_list_id%TYPE := FND_API.G_MISS_NUM
17 ,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
18 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
19 ,p_append_description_flag IN VARCHAR2 := 'N'
20 ,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
21 ,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
22 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
23 ,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
24 ,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
25 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
26 ,p_append_information_flag IN VARCHAR2 := 'N'
27 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
28 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
29 ,p_calendar_type IN pa_project_assignments.calendar_type%TYPE := FND_API.G_MISS_CHAR
30 ,p_calendar_id IN pa_project_assignments.calendar_id%TYPE := FND_API.G_MISS_NUM
31 ,p_resource_calendar_percent IN pa_project_assignments.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
32 ,p_project_name IN pa_projects_all.name%TYPE := FND_API.G_MISS_CHAR
33 ,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR
34 ,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
35 ,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
36 ,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
37 ,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
38 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
39 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
40 ,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
41 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
42 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
43 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
44 ,p_revenue_currency_code IN pa_project_assignments.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
45 ,p_revenue_bill_rate IN pa_project_assignments.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
46 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
47 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
48 ,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
49 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
50 ,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
51 ,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
52 ,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
53 ,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
54 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
55 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
56 ,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
57 ,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
58 ,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
59 ,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
60 ,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
61 ,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
62 ,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
63 ,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
64 ,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
65 ,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
66 ,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
67 ,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
68 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM --FP.L Development
69 ,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR --FP.L Development
70 ,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
71 ,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
72 ,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
73 ,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
74 ,p_exception_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
75 ,p_change_start_date IN DATE := FND_API.G_MISS_DATE
76 ,p_change_end_date IN DATE := FND_API.G_MISS_DATE
77 ,p_change_rqmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
78 ,p_change_asgmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
79 ,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
80 ,p_change_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
81 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
82 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
83 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
84 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
85 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
86 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
87 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
88 ,p_non_working_day_flag IN VARCHAR2 := 'N'
89 ,p_change_hours_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
90 ,p_hrs_per_day IN NUMBER := FND_API.G_MISS_NUM
91 ,p_calendar_percent IN NUMBER := FND_API.G_MISS_NUM
92 ,p_change_calendar_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
93 ,p_change_calendar_name IN VARCHAR2 := FND_API.G_MISS_CHAR
94 ,p_change_calendar_id IN NUMBER := FND_API.G_MISS_NUM
95 ,p_duration_shift_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
96 ,p_duration_shift_unit_code IN VARCHAR2 := FND_API.G_MISS_CHAR
97 ,p_num_of_shift IN NUMBER := FND_API.G_MISS_NUM
98 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
99 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := pa_empty_varchar2_240_tbl
100 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
101 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := pa_empty_varchar2_240_tbl
102 ,p_appr_over_auth_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
103 ,p_note_to_all_approvers IN VARCHAR2 := FND_API.G_MISS_CHAR
104 ,p_competence_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
105 ,p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := pa_empty_varchar2_240_tbl
106 ,p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type := pa_empty_varchar2_30_tbl
107 ,p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type := pa_empty_num_tbl
108 ,p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := pa_empty_varchar2_1_tbl
109 ,p_resolve_con_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
110 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
111 )
112 IS
113
114 CURSOR csr_get_tp_amt_type_name (p_tp_amt_type IN VARCHAR2) IS
115 SELECT plks.meaning
116 FROM pa_lookups plks
117 WHERE plks.lookup_type = 'TP_AMOUNT_TYPE'
118 AND plks.lookup_code = p_tp_amt_type;
119
120 l_item_type VARCHAR2(8) := 'PARMATRX';
121 l_item_key NUMBER;
122 l_save_threshold NUMBER;
123 l_text_attr_name_tbl Wf_Engine.NameTabTyp;
124 l_text_attr_value_tbl Wf_Engine.TextTabTyp;
125 l_set_text_attr_name_tbl Wf_Engine.NameTabTyp;
126 l_set_text_attr_value_tbl Wf_Engine.TextTabTyp;
127 l_num_attr_name_tbl Wf_Engine.NameTabTyp;
128 l_num_attr_value_tbl Wf_Engine.NumTabTyp;
129 l_set_num_attr_name_tbl Wf_Engine.NameTabTyp;
130 l_set_num_attr_value_tbl Wf_Engine.NumTabTyp;
131 l_date_attr_name_tbl Wf_Engine.NameTabTyp;
132 l_date_attr_value_tbl Wf_Engine.DateTabTyp;
133 l_set_date_attr_name_tbl Wf_Engine.NameTabTyp;
134 l_set_date_attr_value_tbl Wf_Engine.DateTabTyp;
135 l_err_code fnd_new_messages.message_name%TYPE;
136 l_err_stage VARCHAR2(2000);
137 l_err_stack VARCHAR2(2000);
138 l_object_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
139 l_approver1_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
140 l_approver1_name_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
141 l_approver2_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
142 l_approver2_name_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
143 l_project_id pa_projects_all.project_id%TYPE;
144 l_fcst_tp_amount_type_name VARCHAR2(80);
145 ----Added following folloeing for bug 6199871 ------------------------
146 l_apprvl_status_code PA_PROJECT_ASSIGNMENTS.APPRVL_STATUS_CODE%TYPE;
147 l_change_id NUMBER;
148 l_record_version_number NUMBER;
149 l_return_status VARCHAR2(1);
150 l_msg_count NUMBER;
151 l_msg_data VARCHAR2(2000);
152 ------------------------------------------------------------------------
153
154 BEGIN
155
156 --initialize return status to Success
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159 -- Setting thresold value to run the process in background
160 l_save_threshold := wf_engine.threshold;
161
162 IF wf_engine.threshold < 0 THEN
163 wf_engine.threshold := l_save_threshold;
164 END IF;
165 wf_engine.threshold := -1;
166
167 --get item key
168 SELECT pa_mass_asgmt_trx_wf_s.nextval
169 INTO l_item_key
170 FROM dual;
171
172 ---------------------------------------------------------------------------
173 -- Bug Ref # 6199871
174 -- Changing Approval Status to Submitted for each Assignment
175 -- Record version Id is passes as 1,as it will be populated inside
176 -- the API to the correct one which is queried from
177 -- 'pa_project_assignments' for the Given Assignment id.
178 ---------------------------------------------------------------------------
179 IF p_assignment_id_tbl.COUNT > 0 THEN
180 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
181 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(
182 p_assignment_id => p_assignment_id_tbl(i)
183 ,p_action_code => p_action
184 ,p_note_to_approver => p_note_to_all_approvers
185 ,p_record_version_number=> 1
186 ,x_apprvl_status_code => l_apprvl_status_code
187 ,x_change_id => l_change_id
188 ,x_record_version_number=> l_record_version_number
189 ,x_return_status => l_return_status
190 ,x_msg_count => l_msg_count
191 ,x_msg_data => l_msg_data);
192
193 END LOOP;
194 END IF;
195 --------------------------------------------------------------------------
196
197 -- Creating the work flow process
198 WF_ENGINE.CreateProcess( itemtype => l_item_type,
199 itemkey => l_item_key,
200 process => 'PA_MASS_ASGMT_TRX_WF') ;
201
202 --if project id is not passed (could only happen when adding a delivery or
203 --an admin assignment from the resource context) then get the project id
204 --from the project number. Project number is required.
205 IF p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM THEN
206 SELECT project_id INTO l_project_id
207 FROM pa_projects_all
208 WHERE segment1 = p_project_number;
209 ELSE
210 l_project_id := p_project_id;
211 END IF;
212
213 --insert a record for this wf into the pa_wf_processes table.
214 PA_WORKFLOW_UTILS.Insert_WF_Processes
215 (p_wf_type_code => p_mode
216 ,p_item_type => l_item_type
217 ,p_item_key => to_char(l_item_key)
218 ,p_entity_key1 => to_char(l_project_id)
219 ,p_description => p_mode
220 ,p_err_code => l_err_code
221 ,p_err_stage => l_err_stage
222 ,p_err_stack => l_err_stack);
223
224
225 --store all attributes in text, number, or date plsql tables depending on their
226 --datatype. One table for the names of the attributes, and one for the value.
227 --the name/value tables will be used to dynamically create the workflow item attributes.
228
229 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'MODE';
230 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_mode;
231
232 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'ACTION';
233 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_action;
234
235 --if this is a mass assignment then assign the resource id tbl to the object id tbl.
236 --otherwise assign the assignment id tbl to the object di tbl.
237 IF p_mode = G_MASS_ASGMT AND p_resource_id_tbl.COUNT > 0 THEN
238
239 l_object_id_tbl := p_resource_id_tbl;
240
241 ELSIF p_assignment_id_tbl.COUNT > 0 THEN
242
243 l_object_id_tbl := p_assignment_id_tbl;
244
245 END IF;
246
247 --if this is SAVE action then there are no approvers, so
248 --need to extend these plsql tables so that the bulk insert
249 --below will not fail
250 --if it is in SAVE_AND_SUBMIT or SUBMIT action then assign
251 --the approver tables IN parameters to the local variables.
252 IF p_action = G_SAVE THEN
253 l_approver1_id_tbl.EXTEND(l_object_id_tbl.COUNT);
254 l_approver2_id_tbl.EXTEND(l_object_id_tbl.COUNT);
255 l_approver1_name_tbl.EXTEND(l_object_id_tbl.COUNT);
256 l_approver2_name_tbl.EXTEND(l_object_id_tbl.COUNT);
257 ELSE
258 l_approver1_id_tbl := p_approver1_id_tbl;
259 l_approver1_name_tbl := p_approver1_name_tbl;
260 l_approver2_id_tbl := p_approver2_id_tbl;
261 l_approver2_name_tbl := p_approver2_name_tbl;
262 END IF;
263
264 --if there are any object ids then insert the wf process details.
265 IF l_object_id_tbl.COUNT > 0 THEN
266 FORALL i IN l_object_id_tbl.FIRST .. l_object_id_tbl.LAST
267 INSERT INTO pa_wf_process_details(wf_type_code,
268 item_type,
269 item_key,
270 object_id1,
271 process_status_code,
272 source_attribute1,
273 source_attribute2,
274 source_attribute3,
275 source_attribute4,
276 last_update_date,
277 last_updated_by,
278 creation_date,
279 created_by,
280 last_update_login
281 )
282 VALUES
283 (p_mode,
284 l_item_type,
285 to_char(l_item_key),
286 l_object_id_tbl(i),
287 'P',
288 decode(p_action, G_SAVE, NULL, to_char(l_approver1_id_tbl(i))),
289 decode(p_action, G_SAVE, NULL, l_approver1_name_tbl(i)),
290 decode(p_action, G_SAVE, NULL, to_char(l_approver2_id_tbl(i))),
291 decode(p_action, G_SAVE, NULL, l_approver2_name_tbl(i)),
292 sysdate,
293 fnd_global.user_id,
294 sysdate,
295 fnd_global.user_id,
296 fnd_global.login_id
297 );
298
299 END IF;
300
301 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'NUMBER_OF_RESOURCES';
302 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_object_id_tbl.COUNT;
303
304 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'NUMBER_OF_ASSIGNMENTS';
305 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_object_id_tbl.COUNT;
306
307 -- p_project_id should be used here instead of l_project_id because
308 -- p_project_id = null case must be handled in Create_Assignment API to
309 -- get additional default attributes from project when the project
310 -- number lov is not used.
311 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'PROJECT_ID';
312 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_project_id;
313
314 --store the following attributes in the name/value plsql tables if mode
315 --is Mass Assignment or Mass Update Basic Information.
316 IF p_mode = G_MASS_ASGMT or p_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
317
318 --this attribute is not dynamically created because it will be displayed
319 --in the notification so it must be created as a workflow attribute at
320 --design time.
321 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_NAME';
322 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_assignment_name;
323
324 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_TYPE';
325 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_assignment_type;
326
327 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'STATUS_CODE';
328 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_status_code;
329
330 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'MULTIPLE_STATUS_FLAG';
331 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_multiple_status_flag;
332
333 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'STAFFING_PRIORITY_CODE';
334 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_staffing_priority_code;
335
336 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'PROJECT_ROLE_ID';
337 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_project_role_id;
338
339 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'ROLE_LIST_ID';
340 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_role_list_id;
341
342 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'WORK_TYPE_ID';
343 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_work_type_id;
344
345 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'WORK_TYPE_NAME';
346 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_work_type_name;
347
348 -- FP.L Development: Passing Staffing Owner Id
349 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'STAFFING_OWNER_PERSON_ID';
350 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_staffing_owner_person_id;
351
352 -- FP.L Development: Passing Staffing Owner Name
353 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'STAFFING_OWNER_NAME';
354 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_staffing_owner_name;
355
356 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'PROJECT_SUBTEAM_ID';
357 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_project_subteam_id;
358
359 --this attribute is not dynamically created because it will be displayed
360 --in the notification so it must be created as a workflow attribute at
361 --design time.
362 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'DESCRIPTION';
363 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_description;
364
365 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'APPEND_DESCRIPTION_FLAG';
366 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_append_description_flag;
367
368 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'START_DATE';
369 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := p_start_date;
370
371 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'END_DATE';
372 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := p_end_date;
373
374 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXTENSION_POSSIBLE';
375 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_extension_possible;
376
377 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'MIN_RESOURCE_JOB_LEVEL';
378 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_min_resource_job_level;
379
380 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'MAX_RESOURCE_JOB_LEVEL';
381 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_max_resource_job_level;
382
383 --this attribute is not dynamically created because it will be displayed
384 --in the notification so it must be created as a workflow attribute at
385 --design time.
386 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ADDITIONAL_INFORMATION';
387 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_additional_information;
388
389 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'APPEND_INFORMATION_FLAG';
390 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_append_information_flag;
391
392 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'LOCATION_ID';
393 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_location_id;
394
395 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CALENDAR_TYPE';
396 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_calendar_type;
397
398 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'CALENDAR_ID';
399 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_calendar_id;
400
401 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'RESOURCE_CALENDAR_PERCENT';
402 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_resource_calendar_percent;
403
404 --this attribute is not dynamically created because it will be displayed
405 --in the notification so it must be created as a workflow attribute at
406 --design time.
407 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NAME';
408 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_project_name;
409
410 --this attribute is not dynamically created because it will be displayed
411 --in the notification so it must be created as a workflow attribute at
412 --design time.
413 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NUMBER';
414 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_project_number;
415
416 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_SUBTEAM_NAME';
417 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_project_subteam_name;
418
419 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_STATUS_NAME';
420 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_project_status_name;
421
422 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'STAFFING_PRIORITY_NAME';
423 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_staffing_priority_name;
424
425 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_ROLE_NAME';
426 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_project_role_name;
427
428 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'LOCATION_CITY';
429 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_location_city;
430
431 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'LOCATION_REGION';
432 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_location_region;
433
434 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'LOCATION_COUNTRY_NAME';
435 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_location_country_name;
436
437 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'LOCATION_COUNTRY_CODE';
438 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_location_country_code;
439
440 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CALENDAR_NAME';
441 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_calendar_name;
442
443 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXPENSE_OWNER';
444 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_expense_owner;
445
446 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'EXPENSE_LIMIT';
447 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_expense_limit;
448
449 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXPENSE_LIMIT_CURRENCY_CODE';
450 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_expense_limit_currency_code;
451
452 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'COMP_MATCH_WEIGHTING';
453 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_comp_match_weighting;
454
455 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'AVAIL_MATCH_WEIGHTING';
456 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_avail_match_weighting;
457
458 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'JOB_LEVEL_MATCH_WEIGHTING';
459 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_job_level_match_weighting;
460
461 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SEARCH_MIN_AVAILABILITY';
462 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_search_min_availability;
463
464 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'SEARCH_COUNTRY_CODE';
465 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_search_country_code;
466
467 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'SEARCH_COUNTRY_NAME';
468 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_search_country_name;
469
470 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SEARCH_EXP_ORG_STRUCT_VER_ID';
471 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_search_exp_org_struct_ver_id;
472
473 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'SEARCH_EXP_ORG_HIER_NAME';
474 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_search_exp_org_hier_name;
475
476 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SEARCH_EXP_START_ORG_ID';
477 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_search_exp_start_org_id;
478
479 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'SEARCH_EXP_START_ORG_NAME';
480 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_search_exp_start_org_name;
481
482 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SEARCH_MIN_CANDIDATE_SCORE';
483 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_search_min_candidate_score;
484
485 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'ENABLE_AUTO_CAND_NOM_FLAG';
486 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_enable_auto_cand_nom_flag;
487
488 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE';
489 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := p_revenue_bill_rate;
490 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE_CURR';
491 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_revenue_currency_code;
492
493 IF p_fcst_tp_amount_type IS NOT NULL THEN
494 open csr_get_tp_amt_type_name(p_fcst_tp_amount_type);
495 fetch csr_get_tp_amt_type_name into l_fcst_tp_amount_type_name;
496 close csr_get_tp_amt_type_name;
497 END IF;
498
499 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_AMT_TYPE_NAME';
500 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_fcst_tp_amount_type_name;
501
502 --store the following attributes in the name/value plsql tables if mode
503 --is Mass Update Forecast Items
504 ELSIF p_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
505
506 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'WORK_TYPE_ID';
507 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_work_type_id;
508
509 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'WORK_TYPE_NAME';
510 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := p_work_type_name;
511
512 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'FCST_TP_AMOUNT_TYPE';
513 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_fcst_tp_amount_type;
514
515 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'FCST_JOB_ID';
516 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_fcst_job_id;
517
518 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'FCST_JOB_GROUP_ID';
519 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_fcst_job_group_id;
520
521 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'EXPENDITURE_ORG_ID';
522 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_expenditure_org_id;
523
524 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'EXPENDITURE_ORGANIZATION_ID';
525 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_expenditure_organization_id;
526
527 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXPENDITURE_TYPE_CLASS';
528 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_expenditure_type_class;
529
530 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXPENDITURE_TYPE';
531 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_expenditure_type;
532
533 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'FCST_JOB_NAME';
534 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_fcst_job_name;
535
536 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'FCST_JOB_GROUP_NAME';
537 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_fcst_job_group_name;
538
539 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXPENDITURE_ORG_NAME';
540 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_expenditure_org_name;
541
542 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXP_ORGANIZATION_NAME';
543 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_exp_organization_name;
544
545 --store the following attributes in the name/value plsql tables if mode
546 --is Mass Update Schedule
547 ELSIF p_mode = G_MASS_UPDATE_SCHEDULE THEN
548
549 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXCEPTION_TYPE_CODE';
550 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_exception_type_code;
551
552 l_date_attr_name_tbl(l_date_attr_name_tbl.COUNT+1) := 'CHANGE_START_DATE';
553 l_date_attr_value_tbl(l_date_attr_value_tbl.COUNT+1) := p_change_start_date;
554
555 l_date_attr_name_tbl(l_date_attr_name_tbl.COUNT+1) := 'CHANGE_END_DATE';
556 l_date_attr_value_tbl(l_date_attr_value_tbl.COUNT+1) := p_change_end_date;
557
558 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CHANGE_RQMT_STATUS_CODE';
559 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_change_rqmt_status_code;
560
561 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CHANGE_ASGMT_STATUS_CODE';
562 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_change_asgmt_status_code;
563
564 IF (p_exception_type_code = 'CHANGE_WORK_PATTERN') THEN
565 IF (p_change_start_date_tbl.COUNT > 0) THEN
566 FOR j IN p_change_start_date_tbl.FIRST .. p_change_end_date_tbl.LAST LOOP
567
568 l_date_attr_name_tbl(l_date_attr_name_tbl.COUNT+1) := 'CHANGE_START_DATE'||j;
569 l_date_attr_value_tbl(l_date_attr_value_tbl.COUNT+1) := p_change_start_date_tbl(j);
570
571 l_date_attr_name_tbl(l_date_attr_name_tbl.COUNT+1) := 'CHANGE_END_DATE'||j;
572 l_date_attr_value_tbl(l_date_attr_value_tbl.COUNT+1) := p_change_end_date_tbl(j);
573
574 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'MONDAY_HOURS'||j;
575 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_monday_hours_tbl(j);
576
577 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'TUESDAY_HOURS'||j;
578 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_tuesday_hours_tbl(j);
579
580 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'WEDNESDAY_HOURS'||j;
581 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_wednesday_hours_tbl(j);
582
583 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'THURSDAY_HOURS'||j;
584 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_thursday_hours_tbl(j);
585
586 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'FRIDAY_HOURS'||j;
587 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_friday_hours_tbl(j);
588
589 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SATURDAY_HOURS'||j;
590 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_saturday_hours_tbl(j);
591
592 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SUNDAY_HOURS'||j;
593 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_sunday_hours_tbl(j);
594 END LOOP;
595 END IF;
596 END IF;
597
598 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUM_OF_SCH_PERIODS';
599 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_change_start_date_tbl.COUNT;
600 -- END IF;
601
602 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'NON_WORKING_DAY_FLAG';
603 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_non_working_day_flag;
604
605 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CHANGE_HOURS_TYPE_CODE';
606 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_change_hours_type_code;
607
608 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'HRS_PER_DAY';
609 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_hrs_per_day;
610
611 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'CALENDAR_PERCENT';
612 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_calendar_percent;
613
614 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CHANGE_CALENDAR_TYPE_CODE';
615 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_change_calendar_type_code;
616
617 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'CHANGE_CALENDAR_NAME';
618 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_change_calendar_name;
619
620 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'CHANGE_CALENDAR_ID';
621 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_change_calendar_id;
622
623 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'DURATION_SHIFT_TYPE_CODE';
624 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_duration_shift_type_code;
625
626 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'DURATION_SHIFT_UNIT_CODE';
627 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_duration_shift_unit_code;
628
629 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUM_OF_SHIFT';
630 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_num_of_shift;
631
632 --store the following attributes in the name/value plsql tables if mode
633 --is Mass Update Competencies
634 ELSIF p_mode = G_MASS_UPDATE_COMPETENCIES THEN
635
636 IF p_competence_id_tbl.COUNT > 0 THEN
637 FOR i IN p_competence_id_tbl.FIRST .. p_competence_id_tbl.LAST LOOP
638 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'COMPETENCE_ID'||i;
639 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_competence_id_tbl(i);
640 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'COMPETENCE_NAME'||i;
641 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_competence_name_tbl(i);
642 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'COMPETENCE_ALIAS'||i;
643 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_competence_alias_tbl(i);
644 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'RATING_LEVEL_ID'||i;
645 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_rating_level_id_tbl(i);
646 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'MANDATORY_FLAG'||i;
647 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_mandatory_flag_tbl(i);
648 END LOOP;
649 END IF;
650
651 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUMBER_OF_COMPETENCIES';
652 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := p_competence_id_tbl.COUNT;
653
654 END IF;
655
656 --store the following attributes in the name/value plsql tables if mode
657 --is Mass Submit for Approval, or if action is save and SUBMIT - which
658 --means the assignment will be submitted for approval after the create/update
659 --is performed.
660 IF p_mode = G_MASS_SUBMIT_FOR_APPROVAL OR p_action = G_SAVE_AND_SUBMIT THEN
661
662 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'APPR_OVER_AUTH_FLAG';
663 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_appr_over_auth_flag;
664
665 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'NOTE_TO_ALL_APPROVERS';
666 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_note_to_all_approvers;
667
668 END IF;
669
670 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'RESOLVE_CONFLICT_ACTION_CODE';
671 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := p_resolve_con_action_code;
672
673 --set the item attribute for the Submitter - this is who will receive the notification.
674 --this attribute is not dynamically created because this attribute is the
675 --notification performer - and the performer attribute must be included as a
676 --workflow item attribute at design time.
677 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'SUBMITTER_USER_NAME';
678 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := FND_GLOBAL.user_name;
679
680 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SUBMITTER_USER_ID';
681 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := FND_GLOBAL.user_id;
682
683 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SUBMITTER_RESP_ID';
684 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := FND_GLOBAL.resp_id;
685
686 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'SUBMITTER_RESP_APPL_ID';
687 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := FND_GLOBAL.resp_appl_id;
688
689 --dynamically create and set the Text item attributes
690 WF_ENGINE.AddItemAttrTextArray(itemtype => l_item_type,
691 itemkey => l_item_key,
692 aname => l_text_attr_name_tbl,
693 avalue => l_text_attr_value_tbl);
694
695 --dynamically create and set the Number item attributes
696 WF_ENGINE.AddItemAttrNumberArray(itemtype => l_item_type,
697 itemkey => l_item_key,
698 aname => l_num_attr_name_tbl,
699 avalue => l_num_attr_value_tbl);
700
701 --dynamically create and set the Date item attributes
702 WF_ENGINE.AddItemAttrDateArray(itemtype => l_item_type,
703 itemkey => l_item_key,
704 aname => l_date_attr_name_tbl,
705 avalue => l_date_attr_value_tbl);
706
707 --SET the Text item attributes (these attributes were created at design time)
708 WF_ENGINE.SetItemAttrTextArray(itemtype => l_item_type,
709 itemkey => l_item_key,
710 aname => l_set_text_attr_name_tbl,
711 avalue => l_set_text_attr_value_tbl);
712
713 --SET the Number item attributes (these attributes were created at design time)
714 WF_ENGINE.SetItemAttrNumberArray(itemtype => l_item_type,
715 itemkey => l_item_key,
716 aname => l_set_num_attr_name_tbl,
717 avalue => l_set_num_attr_value_tbl);
718
719 --SET the Date item attributes
720 WF_ENGINE.SetItemAttrDateArray(itemtype => l_item_type,
721 itemkey => l_item_key,
722 aname => l_set_date_attr_name_tbl,
723 avalue => l_set_date_attr_value_tbl);
724
725
726 -- start the workflow process
727 WF_ENGINE.StartProcess( itemtype => l_item_type,
728 itemkey => l_item_key);
729
730 --Setting the original value
731 wf_engine.threshold := l_save_threshold;
732
733 --if this is a mass update then set the mass_wf_in_progress_flag to 'Y'
734 --in pa_project_assignments. This is a bulk update for all assignments being updated.
735 IF p_mode <> G_MASS_ASGMT THEN
736
737 FORALL i in p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST
738 UPDATE pa_project_assignments
739 SET mass_wf_in_progress_flag = 'Y'
740 WHERE assignment_id = p_assignment_id_tbl(i);
741
742 END IF;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746
747 --Setting the original value
748 wf_engine.threshold := l_save_threshold;
749
750 -- Set the excetption Message and the stack
751 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'pa_mass_asgmt_trx.start_mass_asgmt_trx_wf'
752 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
753 --
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755 RAISE;
756
757 END start_mass_asgmt_trx_wf;
758
759 PROCEDURE mass_asgmt_trx_wf
760 (p_item_type IN VARCHAR2,
761 p_item_key IN VARCHAR2,
762 p_actid IN NUMBER,
763 p_funcmode IN VARCHAR2,
764 p_result OUT NOCOPY VARCHAR2) -- 4537865
765
766 IS
767
768 l_mode VARCHAR2(30);
769 l_action VARCHAR2(30);
770 l_assignment_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
771 l_resource_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
772 l_object_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
773 l_success_assignment_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
774 l_asgmt_overcom_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
775 l_project_id pa_project_assignments.project_id%TYPE;
776 l_start_date pa_project_assignments.start_date%TYPE;
777 l_end_date pa_project_assignments.end_date%TYPE;
778 l_project_name pa_projects_all.name%TYPE;
779 l_project_number pa_projects_all.segment1%TYPE;
780 l_success_assignments NUMBER := 0;
781 l_failure_assignments NUMBER := 0;
782 l_text_attr_name_tbl Wf_Engine.NameTabTyp;
783 l_text_attr_value_tbl Wf_Engine.TextTabTyp;
784 l_num_attr_name_tbl Wf_Engine.NameTabTyp;
785 l_num_attr_value_tbl Wf_Engine.NumTabTyp;
786 l_add_num_attr_name_tbl Wf_Engine.NameTabTyp;
787 l_add_num_attr_value_tbl Wf_Engine.NumTabTyp;
788 l_project_organization pa_project_lists_v.CARRYING_OUT_ORGANIZATION_NAME%TYPE;
789 l_project_customer pa_project_lists_v.customer_name%TYPE;
790 l_project_manager pa_project_lists_v.person_name%TYPE;
791 l_err_code VARCHAR2(2000);
792 l_err_stage VARCHAR2(2000);
793 l_err_stack VARCHAR2(2000);
794 l_return_status VARCHAR2(1);
795 l_msg_count NUMBER;
796 l_msg_data fnd_new_messages.message_text%TYPE;
797 l_success_assignments_link VARCHAR2(2000);
798 l_failure_assignments_link VARCHAR2(2000);
799 l_assignment_duration NUMBER;
800 l_result VARCHAR2(2000);
801 l_number_of_resources NUMBER;
802 l_number_of_assignments NUMBER;
803 l_any_success_assignment_id NUMBER;
804 l_assignment_effort NUMBER;
805 l_first_assignment_id NUMBER;
806 l_document VARCHAR2(32767);
807 l_conflict_group_id NUMBER;
808 l_overcommitment_flag VARCHAR2(1);
809 l_resolve_con_action_code pa_lookups.meaning%TYPE;
810 l_view_conflicts_link VARCHAR2(2000);
811 l_conf_asgmt_count NUMBER;
812 l_message_name fnd_new_messages.message_name%TYPE;
813 l_view_conf_action_text fnd_new_messages.message_text%TYPE;
814 l_calling_page VARCHAR2(30);
815
816 TYPE success_failure_count_tbl IS TABLE OF NUMBER
817 INDEX BY BINARY_INTEGER;
818 l_success_failure_count_tbl success_failure_count_tbl;
819
820 BEGIN
821
822 --initialize the message sta.
823 FND_MSG_PUB.initialize;
824
825 IF (p_funcmode = 'RUN') THEN
826
827 --Initialize the p_result to NONE - no notification will be sent.
828 p_result := 'NONE';
829
830 --bulk collect the pending resource or assignment ids for this mass transaction.
831 SELECT object_id1 BULK COLLECT INTO l_object_id_tbl
832 FROM pa_wf_process_details
833 WHERE item_type = p_item_type
834 AND item_key = p_item_key
835 AND process_status_code = 'P';
836
837 l_mode := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
838 itemkey => p_item_key,
839 aname => 'MODE');
840
841 --if this is a mass assignment then assign the resource id tbl to the object id tbl.
842 --otherwise assign the assignment id tbl to the object id tbl.
843 IF l_mode = G_MASS_ASGMT THEN
844
845 l_resource_id_tbl := l_object_id_tbl;
846
847 ELSE
848
849 l_assignment_id_tbl := l_object_id_tbl;
850
851 END IF;
852
853 --during a mass asgmt/update, the assignments created/updated will be
854 --committed if successful/rolled back if any errors - ONE AT A TIME.
855 --no commits are allowed in the workflow transaction, so call this
856 --API which will start an autonomous transaction.
857 --pa_wf_process_details.process_status_code will be updated inside
858 --the autonomous transaction
859 PA_MASS_ASGMT_TRX.mass_asgmt_autonomous_trx
860 (p_item_type => p_item_type,
861 p_item_key => p_item_key,
862 p_actid => p_actid,
863 p_funcmode => p_funcmode,
864 p_resource_id_tbl => l_resource_id_tbl,
865 p_assignment_id_tbl => l_assignment_id_tbl,
866 x_mode => l_mode,
867 x_action => l_action,
868 x_start_date => l_start_date,
869 x_end_date => l_end_date,
870 x_project_id => l_project_id,
871 x_document => l_document);
872
873 --get the number of success/failure assignments
874 SELECT sum(decode(process_status_code, 'S', 1,0)),
875 sum(decode(process_status_code, 'A', 1, 'E', 1, 0))
876 INTO l_success_assignments,
877 l_failure_assignments
878 FROM pa_wf_process_details
879 WHERE item_type = p_item_type
880 AND item_key = p_item_key;
881
882 --if the mass trx is submitted for approval and any assignments were
883 --successfully created / updated then do the following to
884 --check overcommitment.
885 IF l_action <> G_SAVE AND l_success_assignments > 0 THEN
886
887 l_resolve_con_action_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
888 itemkey => p_item_key,
889 aname => 'RESOLVE_CONFLICT_ACTION_CODE');
890
891
892 PA_SCHEDULE_PVT.Check_Overcommitment_Mult
893 (
894 p_item_type => p_item_type,
895 p_item_key => p_item_key,
896 p_resolve_conflict_action_code => l_resolve_con_action_code,
897 p_conflict_group_id => NULL,
898 x_overcommitment_flag => l_overcommitment_flag,
899 x_conflict_group_id => l_conflict_group_id,
900 x_return_status => l_return_status,
901 x_msg_count => l_msg_count,
902 x_msg_data => l_msg_data);
903
904 l_add_num_attr_name_tbl(l_add_num_attr_name_tbl.COUNT+1) := 'CONFLICT_GROUP_ID';
905 l_add_num_attr_value_tbl(l_add_num_attr_value_tbl.COUNT+1) := l_conflict_group_id;
906
907 END IF;
908
909 --store the num success assignment in the plsql table which will be used
910 --to bulk set the workflow item attributes to be displayed in the notification.
911 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUM_SUCCESS_ASSIGNMENTS';
912 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := l_success_assignments;
913
914 --if any assignments were processed successfully...
915 IF l_success_assignments > 0 THEN
916
917 --if this is a mass assign then get the assignment effort and
918 --store it in the name/value plsql tables to be displayed in the
919 --notification.
920 --this can ONLY be done if there was at least one successfully created assignment
921 --as the assignment needs to be in the database for assignment effort to
922 --be calculated.
923 IF l_mode = G_MASS_ASGMT THEN
924
925 SELECT assignment_id INTO l_any_success_assignment_id
926 FROM pa_mass_txn_asgmt_success_v
927 WHERE item_type = p_item_type
928 AND item_key= p_item_key
929 AND ROWNUM = 1;
930
931 l_assignment_effort := PA_SCHEDULE_UTILS.get_num_hours(
932 p_project_id => l_project_id,
933 p_assignment_id => l_any_success_assignment_id);
934
935 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_EFFORT';
936 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := l_assignment_effort;
937
938 END IF;
939
940 END IF;
941
942 --store the num failure assignments in the plsql table which will be used
943 --to bulk set the workflow item attributes to be displayed in the notification.
944 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUM_FAILURE_ASSIGNMENTS';
945 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := l_failure_assignments;
946
947 --link to view errors page from the notification
948 l_failure_assignments_link := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275'||'&'||'akRegionCode=PA_ERROR_LAYOUT'||'&'||'paProjectId='||l_project_id||'&'||'paSrcType1=MASS_ASSIGNMENT_TRANSACTION'
949 ||'&'||'paSrcType2='||l_mode||'&'||'paSrcId1='||PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE||'&'||'paSrcId2='||PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY||'&'||'addBreadCrumb=RP';
950
951 --store the failure assignments link the the name/value plsql tables.
952 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'FAILURE_ASSIGNMENTS_LINK';
953 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_failure_assignments_link;
954
955 --if there were any assignments not processed successfully...
956 IF l_failure_assignments > 0 THEN
957 --if there were any assignment failures then set the p_result
958 --accordingly based on the mode, either create or update.
959 --Currently a notification is only sent to the submitter if there are any
960 --errors -so a notification will be sent if p_result is either of the following.
961 IF l_mode = G_MASS_ASGMT THEN
962 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
963 p_result := 'CREATE_ASGMT_FAIL_OC';
964 ELSE
965 p_result := 'CREATE_ASGMT_FAILURE';
966 END IF;
967 ELSIF l_mode = G_MASS_SUBMIT_FOR_APPROVAL THEN
968 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
969 p_result := 'SUBMIT_ASGMT_FAILURE_OC';
970 ELSE
971 p_result := 'SUBMIT_ASSIGNMENT_FAILURE';
972 END IF;
973 ELSE
974 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
975 p_result := 'UPDATE_ASGMT_FAIL_OC';
976 ELSE
977 p_result := 'UPDATE_ASGMT_FAILURE';
978 END IF;
979 END IF;
980
981 --if there were no assignment failures then set the p_result
982 --accordingly based on the mode, either create or update.
983 ELSE
984 IF l_mode = G_MASS_ASGMT THEN
985 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
986 p_result := 'CREATE_ASGMT_SUCCESS_OC';
987 ELSE
988 p_result := 'CREATE_ASGMT_SUCCESS';
989 END IF;
990 ELSIF l_mode = G_MASS_SUBMIT_FOR_APPROVAL THEN
991 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
992 p_result := 'SUBMIT_ASGMT_SUCCESS_OC';
993 ELSE
994 p_result := 'SUBMIT_ASSIGNMENT_SUCCESS';
995 END IF;
996 ELSE
997 IF l_overcommitment_flag = 'Y' AND l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
998 p_result := 'UPDATE_ASGMT_SUCCESS_OC';
999 ELSE
1000 p_result := 'UPDATE_ASGMT_SUCCESS';
1001 END IF;
1002 END IF;
1003
1004 END IF;
1005
1006 --if this is a mass assign then calculate the assignment duration and
1007 --store in the name/value plsql tables.
1008 IF l_mode = G_MASS_ASGMT THEN
1009
1010 l_assignment_duration := l_end_date - l_start_date +1;
1011
1012 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_DURATION';
1013 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := l_assignment_duration;
1014
1015 END IF;
1016
1017 --if project id was not passed to the start wf API then get the project
1018 --id from the assignment. When creating an assignment project id will always
1019 --be passed - it could only be an update case. We want to show the project info
1020 --in the notification even if none of the updates were successful - so get the
1021 --first assignment id and use it to get the project id.
1022 --l_success_assignment_id_tbl only has successful assignments
1023
1024 IF l_project_id IS NULL or l_project_id = FND_API.G_MISS_NUM THEN
1025
1026 SELECT project_id
1027 INTO l_project_id
1028 FROM pa_project_assignments
1029 WHERE assignment_id = l_assignment_id_tbl(1);
1030
1031 END IF;
1032
1033 --get the project attributes to be displayed in the notification.
1034 -- Commented for SQL ID 14910543 Bug 4918687
1035 -- SELECT proj.name,
1036 -- proj.segment1,
1037 -- proj.CARRYING_OUT_ORGANIZATION_NAME,
1038 -- proj.person_name,
1039 -- proj.customer_name
1040 -- INTO l_project_name,
1041 -- l_project_number,
1042 -- l_project_organization,
1043 -- l_project_manager,
1044 -- l_project_customer
1045 -- FROM pa_project_lists_v proj
1046 -- WHERE project_id = l_project_id;
1047
1048 -- Added for Perf fix 4918687 SQL ID 14910543
1049 SELECT proj.name,
1050 proj.segment1,
1051 pa_resource_utils.get_organization_name(proj.carrying_out_organization_id) CARRYING_OUT_ORGANIZATION_NAME ,
1052 pa_project_parties_utils.get_project_manager_name(proj.project_id) person_name,
1053 pa_projects_maint_utils.get_primary_customer_name(proj.project_id) customer_name
1054 INTO l_project_name,
1055 l_project_number,
1056 l_project_organization,
1057 l_project_manager,
1058 l_project_customer
1059 FROM pa_projects_all proj
1060 WHERE project_id = l_project_id;
1061
1062 --store the project attributes in the name/value plsql tables.
1063 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_NAME';
1064 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_project_name;
1065
1066 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_NUMBER';
1067 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_project_number;
1068
1069 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_ORGANIZATION';
1070 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_project_organization;
1071
1072 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_MANAGER';
1073 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_project_manager;
1074
1075 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'PROJECT_CUSTOMER';
1076 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_project_customer;
1077
1078 PA_SCHEDULE_PVT.Get_Conflicting_Asgmt_Count(p_conflict_group_id => l_conflict_group_id,
1079 x_assignment_count => l_conf_asgmt_count,
1080 x_return_status => l_return_status,
1081 x_msg_count => l_msg_count,
1082 x_msg_data => l_msg_data);
1083
1084 l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'NUM_OVERCOMMIT';
1085 l_num_attr_value_tbl(l_num_attr_value_tbl.COUNT+1) := l_conf_asgmt_count;
1086
1087 IF l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
1088 IF l_mode = G_MASS_ASGMT THEN
1089 l_calling_page := 'MassAsgmtCreateSubmitNotif';
1090 ELSIF l_mode = G_MASS_SUBMIT_FOR_APPROVAL THEN
1091 l_calling_page := 'MassAsgmtSubmitOnlyNotif';
1092 ELSE
1093 l_calling_page := 'MassAsgmtUpdateSubmitNotif';
1094 END IF;
1095 ELSE
1096 IF l_mode = G_MASS_ASGMT THEN
1097 l_calling_page := 'MassAsgmtCreateFYINotif';
1098 ELSIF l_mode = G_MASS_SUBMIT_FOR_APPROVAL THEN
1099 l_calling_page := 'MassAsgmtSubmitOnlyFYINotif';
1100 ELSE
1101 l_calling_page := 'MassAsgmtUpdateFYINotif';
1102 END IF;
1103 END IF;
1104
1105 l_view_conflicts_link := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275'||'&'||'akRegionCode=PA_VIEW_CONFLICTS_LAYOUT'||'&'||'paProjectId='||l_project_id||'&'||
1106 'paConflictGroupId='||l_conflict_group_id||'&'||'paCallingPage='||l_calling_page||'&'||'paItemType=PARMATRX'||'&'||'paItemKey='||p_item_key||'&'||'addBreadCrumb=RP';
1107
1108 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'VIEW_CONFLICTS_LINK';
1109 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_view_conflicts_link;
1110
1111 IF l_resolve_con_action_code = 'NOTIFY_IF_CONFLICT' THEN
1112
1113 l_message_name := 'PA_NOTIFY_IF_CONFLICT';
1114
1115 BEGIN
1116
1117 /* 2708879 - Added two conditions for application id and language code for the query from fnd_new_messages below */
1118
1119 SELECT message_text INTO l_view_conf_action_text
1120 FROM fnd_new_messages
1121 WHERE message_name = l_message_name
1122 and application_id = 275
1123 and language_code = userenv('LANG');
1124
1125 EXCEPTION
1126 WHEN NO_DATA_FOUND THEN
1127 l_view_conf_action_text := l_message_name;
1128 END;
1129
1130 l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'VIEW_CONFLICTS_ACTION_TEXT';
1131 l_text_attr_value_tbl(l_text_attr_value_tbl.COUNT+1) := l_view_conf_action_text;
1132
1133 END IF;
1134
1135 --dynamically create and set the Number item attributes
1136 WF_ENGINE.AddItemAttrNumberArray(itemtype => p_item_type,
1137 itemkey => p_item_key,
1138 aname => l_add_num_attr_name_tbl,
1139 avalue => l_add_num_attr_value_tbl);
1140 --set the wf item attributes.
1141 WF_ENGINE.SetItemAttrTextArray(itemtype => p_item_type,
1142 itemkey => p_item_key,
1143 aname => l_text_attr_name_tbl,
1144 avalue => l_text_attr_value_tbl);
1145
1146 WF_ENGINE.SetItemAttrNumberArray(itemtype => p_item_type,
1147 itemkey => p_item_key,
1148 aname => l_num_attr_name_tbl,
1149 avalue => l_num_attr_value_tbl);
1150
1151 --the document type is used to dynamically create the Updated Information section
1152 --of the mass update wf notification.
1153 --see the mass_asgmt_autonomous_trx API for more information.
1154 --also refer to the Oracle Workflow documentation for information
1155 --regarding Document types.
1156 WF_ENGINE.SetItemAttrDocument(itemtype => p_item_type,
1157 itemkey => p_item_key,
1158 aname => 'UPDATED_INFORMATION_DOCUMENT',
1159 documentid => 'plsql:PA_MASS_ASGMT_TRX.Display_Updated_Attributes/' ||l_document);
1160
1161 END IF; --p_funcmode = 'RUN'
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165
1166 -- 4537865 : Need to ask Rajnish whether p_result to be set as NONE
1167 p_result := 'NONE' ; -- In case of Unexpected Error,No Notification will be sent. Simply Exception will be RAISED.
1168
1169 -- Included as per discussion with Rajnish : 4537865
1170 Wf_Core.Context('pa_mass_asgmt_trx','start_mass_asgmt_trx_wf',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
1171
1172 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'pa_mass_asgmt_trx.start_mass_asgmt_trx_wf'
1173 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1174 RAISE;
1175
1176 END mass_asgmt_trx_wf;
1177
1178 PROCEDURE mass_asgmt_autonomous_trx
1179 (p_item_type IN VARCHAR2,
1180 p_item_key IN VARCHAR2,
1181 p_actid IN NUMBER,
1182 p_funcmode IN VARCHAR2,
1183 p_resource_id_tbl IN SYSTEM.pa_num_tbl_type,
1184 p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type,
1185 x_mode OUT NOCOPY VARCHAR2, -- 4537865
1186 x_action OUT NOCOPY VARCHAR2, -- 4537865
1187 x_start_date OUT NOCOPY DATE, -- 4537865
1188 x_end_date OUT NOCOPY DATE, -- 4537865
1189 x_project_id OUT NOCOPY NUMBER, -- 4537865
1190 x_document OUT NOCOPY VARCHAR2) -- 4537865
1191 IS
1192
1193 -- Commented for Perf fix 4918687 SQL ID 14910597
1194 --cursor csr_get_tp_amt_type (p_asg_id NUMBER) IS
1195 --SELECT fcst_tp_amount_type_name
1196 --FROM pa_project_assignments_v
1197 --WHERE assignment_id = p_asg_id;
1198
1199 -- Added for Perf fix 4918687 SQL ID 14910597
1200 cursor csr_get_tp_amt_type (p_asg_id NUMBER) IS
1201 SELECT lkup.meaning fcst_tp_amount_type_name
1202 FROM pa_project_assignments asgn, pa_lookups lkup
1203 WHERE lkup.lookup_type(+) = 'TP_AMOUNT_TYPE'
1204 AND asgn.fcst_tp_amount_type = lkup.lookup_code(+)
1205 AND asgn.assignment_id = p_asg_id;
1206
1207 --this must be an autonomous transaction as no commits are allowed in
1208 --the workflow transaction itself.
1209 PRAGMA AUTONOMOUS_TRANSACTION;
1210
1211 l_mode VARCHAR2(30);
1212 l_action VARCHAR2(30);
1213 l_success_assignment_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1214 l_single_obj_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1215 l_object_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1216 l_assignment_name pa_project_assignments.assignment_name%TYPE;
1217 l_wf_assignment_type pa_project_assignments.assignment_type%TYPE;
1218 l_assignment_type pa_project_assignments.assignment_type%TYPE;
1219 l_status_code pa_project_assignments.status_code%TYPE;
1220 l_multiple_status_flag pa_project_assignments.multiple_status_flag%TYPE;
1221 l_staffing_priority_code pa_project_assignments.staffing_priority_code%TYPE;
1222 l_project_id NUMBER;
1223 l_project_role_id NUMBER;
1224 l_role_list_id NUMBER;
1225 l_project_subteam_id NUMBER;
1226 l_description pa_project_assignments.description%TYPE;
1227 l_append_description_flag VARCHAR2(1);
1228 l_start_date pa_project_assignments.start_date%TYPE;
1229 l_end_date pa_project_assignments.end_date%TYPE;
1230 l_extension_possible pa_project_assignments.extension_possible%TYPE;
1231 l_min_resource_job_level pa_project_assignments.min_resource_job_level%TYPE;
1232 l_max_resource_job_level pa_project_assignments.max_resource_job_level%TYPE;
1233 l_additional_information pa_project_assignments.additional_information%TYPE;
1234 l_append_information_flag VARCHAR2(1);
1235 l_location_id NUMBER;
1236 l_work_type_id NUMBER;
1237 l_calendar_type pa_project_assignments.calendar_type%TYPE;
1238 l_calendar_id NUMBER;
1239 l_resource_calendar_percent pa_project_assignments.resource_calendar_percent%TYPE;
1240 l_project_name pa_projects_all.name%TYPE;
1241 l_project_number pa_projects_all.segment1%TYPE;
1242 l_project_subteam_name pa_project_subteams.name%TYPE;
1243 l_project_status_name pa_project_statuses.project_status_name%TYPE;
1244 l_staffing_priority_name pa_lookups.meaning%TYPE;
1245 l_project_role_name pa_project_role_types.meaning%TYPE;
1246 l_location_city pa_locations.city%TYPE;
1247 l_location_region pa_locations.region%TYPE;
1248 l_location_country_name fnd_territories_tl.territory_short_name%TYPE;
1249 l_location_country_code pa_locations.country_code%TYPE;
1250 l_calendar_name jtf_calendars_tl.calendar_name%TYPE;
1251 l_work_type_name pa_work_types_vl.name%TYPE;
1252 l_tp_amt_type_name pa_project_assignments_v.fcst_tp_amount_type_name%TYPE;
1253 l_expense_owner pa_project_assignments.expense_owner%TYPE;
1254 l_expense_limit pa_project_assignments.expense_limit%TYPE;
1255 l_expense_limit_currency_code pa_project_assignments.expense_limit_currency_code%TYPE;
1256 l_comp_match_weighting pa_project_assignments.competence_match_weighting%TYPE;
1257 l_avail_match_weighting pa_project_assignments.availability_match_weighting%TYPE;
1258 l_job_level_match_weighting pa_project_assignments.job_level_match_weighting%TYPE;
1259 l_search_min_availability pa_project_assignments.search_min_availability%TYPE;
1260 l_search_country_code pa_project_assignments.search_country_code%TYPE;
1261 l_search_country_name fnd_territories_vl.territory_short_name%TYPE;
1262 l_search_exp_org_struct_ver_id pa_project_assignments.search_exp_org_struct_ver_id%TYPE;
1263 l_search_exp_org_hier_name per_organization_structures.name%TYPE;
1264 l_search_exp_start_org_id pa_project_assignments.search_exp_start_org_id%TYPE;
1265 l_search_exp_start_org_name hr_organization_units.name%TYPE;
1266 l_search_min_candidate_score pa_project_assignments.search_min_candidate_score%TYPE;
1267 l_enable_auto_cand_nom_flag pa_project_assignments.enable_auto_cand_nom_flag%TYPE;
1268 l_enable_auto_cand_nom_meaning fnd_lookups.meaning%TYPE;
1269 l_fcst_tp_amount_type pa_project_assignments.fcst_tp_amount_type%TYPE;
1270 l_fcst_job_id NUMBER;
1271 l_fcst_job_group_id NUMBER;
1272 l_expenditure_org_id NUMBER;
1273 l_expenditure_organization_id NUMBER;
1274 l_expenditure_type_class pa_project_assignments.expenditure_type_class%TYPE;
1275 l_expenditure_type pa_project_assignments.expenditure_type%TYPE;
1276 l_fcst_job_name per_jobs.name%TYPE;
1277 l_fcst_job_group_name per_job_groups.displayed_name%TYPE;
1278 l_expenditure_org_name per_organization_units.name%TYPE;
1279 l_exp_organization_name per_organization_units.name%TYPE;
1280 l_exception_type_code VARCHAR2(30);
1281 l_change_start_date DATE;
1282 l_change_end_date DATE;
1283 l_change_rqmt_status_code VARCHAR2(30);
1284 l_change_asgmt_status_code VARCHAR2(30);
1285 l_change_start_date_tbl SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1286 l_change_end_date_tbl SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1287 l_monday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1288 l_tuesday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1289 l_wednesday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1290 l_thursday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1291 l_friday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1292 l_saturday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1293 l_sunday_hours_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1294 l_num_of_sch_periods NUMBER;
1295 l_non_working_day_flag VARCHAR2(1);
1296 l_change_hours_type_code VARCHAR2(30);
1297 l_hrs_per_day NUMBER;
1298 l_calendar_percent NUMBER;
1299 l_change_calendar_type_code VARCHAR2(30);
1300 l_change_calendar_name VARCHAR2(50);
1301 l_change_calendar_id NUMBER;
1302 l_duration_shift_type_code VARCHAR2(30);
1303 l_duration_shift_unit_code VARCHAR2(30);
1304 l_num_of_shift NUMBER;
1305 l_success_assignments NUMBER;
1306 l_failure_assignments NUMBER;
1307 l_success_asgmt_name_tbl Wf_Engine.NameTabTyp;
1308 l_success_asgmt_val_tbl Wf_Engine.NumTabTyp;
1309 l_project_organization pa_project_lists_v.carrying_out_organization_name%TYPE;
1310 l_project_customer pa_project_lists_v.customer_name%TYPE;
1311 l_project_manager pa_project_lists_v.person_name%TYPE;
1312 l_err_code VARCHAR2(2000);
1313 l_err_stage VARCHAR2(2000);
1314 l_err_stack VARCHAR2(2000);
1315 l_return_status VARCHAR2(1);
1316 l_msg_count NUMBER;
1317 l_msg_data fnd_new_messages.message_text%TYPE;
1318 l_number_of_resources NUMBER;
1319 l_number_of_assignments NUMBER;
1320 l_number_of_competencies NUMBER;
1321 l_success_assignments_link VARCHAR2(2000);
1322 l_failure_assignments_link VARCHAR2(2000);
1323 l_assignment_duration NUMBER;
1324 l_competence_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1325 l_competence_alias_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
1326 l_competence_name_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
1327 l_rating_level_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1328 l_mandatory_flag_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type() ;
1329 l_extension_possible_meaning fnd_lookups.meaning%TYPE;
1330 l_mandatory_flag_meaning fnd_lookups.meaning%TYPE;
1331 l_expense_owner_meaning pa_lookups.meaning%TYPE;
1332 l_fcst_tp_amount_type_meaning pa_lookups.meaning%TYPE;
1333 l_rating_level per_rating_levels_v.step_value%TYPE;
1334 l_submitter_user_id NUMBER;
1335 l_submitter_resp_id NUMBER;
1336 l_submitter_resp_appl_id NUMBER;
1337 l_calendar_display VARCHAR2(60);
1338 -- FP.L Development
1339 l_staffing_owner_person_id pa_project_assignments.staffing_owner_person_id%TYPE;
1340 l_staffing_owner_name per_people_f.full_name%TYPE;
1341
1342 BEGIN
1343
1344 --set the check ID flag to 'N'
1345 --this will avoid validation in the value-id conversion APIs if the
1346 --id is passed in - it must be valid.
1347 PA_STARTUP.G_Check_ID_Flag := 'N';
1348
1349 --retrieve the number of assignments attribute
1350 l_submitter_user_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1351 itemkey => p_item_key,
1352 aname => 'SUBMITTER_USER_ID');
1353
1354 l_submitter_resp_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1355 itemkey => p_item_key,
1356 aname => 'SUBMITTER_RESP_ID');
1357
1358 l_submitter_resp_appl_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1359 itemkey => p_item_key,
1360 aname => 'SUBMITTER_RESP_APPL_ID');
1361
1362 FND_GLOBAL.Apps_Initialize ( user_id => l_submitter_user_id
1363 , resp_id => l_submitter_resp_id
1364 , resp_appl_id => l_submitter_resp_appl_id
1365 );
1366
1367 --set globals to be used by APIs called by this API.
1368 PA_MASS_ASGMT_TRX.G_SUBMITTER_USER_ID := l_submitter_user_id;
1369 PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY := p_item_key;
1370
1371 --get the following wf item attributes regardless of the mode
1372 l_mode := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1373 itemkey => p_item_key,
1374 aname => 'MODE');
1375 x_mode := l_mode;
1376
1377 l_action := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1378 itemkey => p_item_key,
1379 aname => 'ACTION');
1380 x_action := l_action;
1381
1382
1383 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1384 itemkey => p_item_key,
1385 aname => 'PROJECT_ID');
1386 x_project_id := l_project_id;
1387
1388
1389
1390
1391 --if the mode is mass assignment or mass update basic info then get the
1392 --following attributes.
1393 IF l_mode = G_MASS_ASGMT OR l_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
1394
1395 l_assignment_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1396 itemkey => p_item_key,
1397 aname => 'ASSIGNMENT_NAME');
1398
1399 --if the attribute has been updated then
1400 --append to x_document to display the Updated Information region in the wf notification
1401 IF l_assignment_name <> FND_API.G_MISS_CHAR AND l_assignment_name IS NOT NULL THEN
1402 x_document := x_document||get_translated_attr_name('ASSIGNMENT_NAME')||' - '||l_assignment_name||'<BR>';
1403 END IF;
1404
1405 l_wf_assignment_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1406 itemkey => p_item_key,
1407 aname => 'ASSIGNMENT_TYPE');
1408
1409 l_status_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1410 itemkey => p_item_key,
1411 aname => 'STATUS_CODE');
1412
1413 l_project_status_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1414 itemkey => p_item_key,
1415 aname => 'PROJECT_STATUS_NAME');
1416
1417 l_multiple_status_flag := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1418 itemkey => p_item_key,
1419 aname => 'MULTIPLE_STATUS_FLAG');
1420
1421 l_work_type_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1422 itemkey => p_item_key,
1423 aname => 'WORK_TYPE_ID');
1424
1425 l_work_type_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1426 itemkey => p_item_key,
1427 aname => 'WORK_TYPE_NAME');
1428
1429 l_staffing_priority_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1430 itemkey => p_item_key,
1431 aname => 'STAFFING_PRIORITY_CODE');
1432
1433 l_staffing_priority_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1434 itemkey => p_item_key,
1435 aname => 'STAFFING_PRIORITY_NAME');
1436
1437 --if the attribute has been updated then
1438 --append to x_document to display the Updated Information region in the wf notification
1439 --get the name as we will only have the code as the field is a poplist on the page
1440 IF l_staffing_priority_code <> FND_API.G_MISS_CHAR AND l_staffing_priority_code IS NOT NULL THEN
1441 IF l_staffing_priority_name = FND_API.G_MISS_CHAR OR l_staffing_priority_name IS NULL THEN
1442 SELECT meaning INTO l_staffing_priority_name
1443 FROM pa_lookups
1444 WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
1445 AND lookup_code = l_staffing_priority_code;
1446 END IF;
1447 x_document := x_document||get_translated_attr_name('STAFFING_PRIORITY_NAME')||' - '||l_staffing_priority_name||'<BR>';
1448 END IF;
1449
1450 l_project_role_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1451 itemkey => p_item_key,
1452 aname => 'PROJECT_ROLE_ID');
1453
1454 l_project_role_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1455 itemkey => p_item_key,
1456 aname => 'PROJECT_ROLE_NAME');
1457
1458 l_role_list_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1459 itemkey => p_item_key,
1460 aname => 'ROLE_LIST_ID');
1461
1462 l_project_subteam_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1463 itemkey => p_item_key,
1464 aname => 'PROJECT_SUBTEAM_ID');
1465
1466 l_project_subteam_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1467 itemkey => p_item_key,
1468 aname => 'PROJECT_SUBTEAM_NAME');
1469
1470 --if the attribute has been updated then
1471 --append to x_document to display the Updated Information region in the wf notification
1472 --get the name as we will only have the code as the field is a poplist on the page
1473 IF l_project_subteam_id <> FND_API.G_MISS_NUM and l_project_subteam_id IS NOT NULL THEN
1474 IF l_project_subteam_name = FND_API.G_MISS_CHAR OR l_project_subteam_name IS NULL THEN
1475 SELECT name INTO l_project_subteam_name
1476 FROM pa_project_subteams
1477 WHERE project_subteam_id = l_project_subteam_id;
1478 END IF;
1479 x_document := x_document||get_translated_attr_name('PROJECT_SUBTEAM_NAME')||' - '||l_project_subteam_name||'<BR>';
1480 END IF;
1481
1482 l_extension_possible := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1483 itemkey => p_item_key,
1484 aname => 'EXTENSION_POSSIBLE');
1485
1486
1487 --if the attribute has been updated then
1488 --append to x_document to display the Updated Information region in the wf notification
1489 --get the meaning as we will not have it as the field is a poplist on the page
1490 IF l_extension_possible <> FND_API.G_MISS_CHAR AND l_extension_possible IS NOT NULL THEN
1491 SELECT meaning INTO l_extension_possible_meaning
1492 FROM fnd_lookups
1493 WHERE lookup_type='YES_NO'
1494 AND lookup_code = l_extension_possible;
1495 x_document := x_document||get_translated_attr_name('EXTENSION_POSSIBLE')||' - '||l_extension_possible_meaning||'<BR>';
1496 END IF;
1497
1498 l_min_resource_job_level := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1499 itemkey => p_item_key,
1500 aname => 'MIN_RESOURCE_JOB_LEVEL');
1501
1502 --if the attribute has been updated then
1503 --append to x_document to display the Updated Information region in the wf notification
1504 IF l_min_resource_job_level <> FND_API.G_MISS_NUM AND l_min_resource_job_level IS NOT NULL THEN
1505 x_document := x_document||get_translated_attr_name('MIN_RESOURCE_JOB_LEVEL')||' - '||l_min_resource_job_level||'<BR>';
1506 END IF;
1507
1508 l_max_resource_job_level := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1509 itemkey => p_item_key,
1510 aname => 'MAX_RESOURCE_JOB_LEVEL');
1511
1512 --if the attribute has been updated then
1513 --append to x_document to display the Updated Information region in the wf notification
1514 IF l_max_resource_job_level <> FND_API.G_MISS_NUM AND l_max_resource_job_level IS NOT NULL THEN
1515 x_document := x_document||get_translated_attr_name('MAX_RESOURCE_JOB_LEVEL')||' - '||l_max_resource_job_level||'<BR>';
1516 END IF;
1517
1518 -- FP.L Development
1519 l_staffing_owner_person_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1520 itemkey => p_item_key,
1521 aname => 'STAFFING_OWNER_PERSON_ID');
1522
1523 l_staffing_owner_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1524 itemkey => p_item_key,
1525 aname => 'STAFFING_OWNER_NAME');
1526
1527 --if the attribute has been updated then
1528 --append to x_document to display the Updated Information region in the wf notification
1529 IF l_staffing_owner_name <> FND_API.G_MISS_CHAR AND l_staffing_owner_name IS NOT NULL THEN
1530 x_document := x_document||get_translated_attr_name('STAFFING_OWNER')||' - '||l_staffing_owner_name||'<BR>';
1531 END IF;
1532
1533 l_description := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1534 itemkey => p_item_key,
1535 aname => 'DESCRIPTION');
1536
1537 --if the attribute has been updated then
1538 --append to x_document to display the Updated Information region in the wf notification
1539 IF l_description <> FND_API.G_MISS_CHAR AND l_description IS NOT NULL THEN
1540 x_document := x_document||get_translated_attr_name('DESCRIPTION')||' - '||l_description||'<BR>';
1541 END IF;
1542
1543 l_append_description_flag := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1544 itemkey => p_item_key,
1545 aname => 'APPEND_DESCRIPTION_FLAG');
1546
1547 l_additional_information := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1548 itemkey => p_item_key,
1549 aname => 'ADDITIONAL_INFORMATION');
1550
1551 --if the attribute has been updated then
1552 --append to x_document to display the Updated Information region in the wf notification
1553 IF l_additional_information <> FND_API.G_MISS_CHAR AND l_additional_information IS NOT NULL THEN
1554 x_document := x_document||get_translated_attr_name('ADDITIONAL_INFORMATION')||' - '||l_additional_information||'<BR>';
1555 END IF;
1556
1557 l_append_information_flag := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1558 itemkey => p_item_key,
1559 aname => 'APPEND_INFORMATION_FLAG');
1560
1561 l_start_date := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1562 itemkey => p_item_key,
1563 aname => 'START_DATE');
1564 x_start_date := l_start_date;
1565
1566 --if the attribute has been updated then
1567 --append to x_document to display the Updated Information region in the wf notification
1568 IF l_start_date <> FND_API.G_MISS_DATE AND l_start_date IS NOT NULL THEN
1569 x_document := x_document||get_translated_attr_name('START_DATE')||' - '||l_start_date||'<BR>';
1570 END IF;
1571
1572 l_end_date := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1573 itemkey => p_item_key,
1574 aname => 'END_DATE');
1575
1576 x_end_date := l_end_date;
1577
1578 --if the attribute has been updated then
1579 --append to x_document to display the Updated Information region in the wf notification
1580 IF l_end_date <> FND_API.G_MISS_DATE AND l_end_date IS NOT NULL THEN
1581 x_document := x_document||get_translated_attr_name('END_DATE')||' - '||l_end_date||'<BR>';
1582 END IF;
1583
1584 l_location_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1585 itemkey => p_item_key,
1586 aname => 'LOCATION_ID');
1587
1588 l_location_city := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1589 itemkey => p_item_key,
1590 aname => 'LOCATION_CITY');
1591
1592 --if the attribute has been updated then
1593 --append to x_document to display the Updated Information region in the wf notification
1594 IF l_location_city <> FND_API.G_MISS_CHAR AND l_location_city IS NOT NULL THEN
1595 x_document := x_document||get_translated_attr_name('LOCATION_CITY')||' - '||l_location_city||'<BR>';
1596 END IF;
1597
1598 l_location_region := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1599 itemkey => p_item_key,
1600 aname => 'LOCATION_REGION');
1601
1602 --if the attribute has been updated then
1603 --append to x_document to display the Updated Information region in the wf notification
1604 IF l_location_region <> FND_API.G_MISS_CHAR AND l_location_region IS NOT NULL THEN
1605 x_document := x_document||get_translated_attr_name('LOCATION_REGION')||' - '||l_location_region||'<BR>';
1606 END IF;
1607
1608 l_location_country_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1609 itemkey => p_item_key,
1610 aname => 'LOCATION_COUNTRY_NAME');
1611
1612 --if the attribute has been updated then
1613 --append to x_document to display the Updated Information region in the wf notification
1614 IF l_location_country_name <> FND_API.G_MISS_CHAR AND l_location_country_name IS NOT NULL THEN
1615 x_document := x_document||get_translated_attr_name('LOCATION_COUNTRY_NAME')||' - '||l_location_country_name||'<BR>';
1616 END IF;
1617
1618 l_location_country_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1619 itemkey => p_item_key,
1620 aname => 'LOCATION_COUNTRY_CODE');
1621
1622 l_calendar_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1623 itemkey => p_item_key,
1624 aname => 'CALENDAR_TYPE');
1625
1626 l_calendar_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1627 itemkey => p_item_key,
1628 aname => 'CALENDAR_ID');
1629
1630 l_resource_calendar_percent := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1631 itemkey => p_item_key,
1632 aname => 'RESOURCE_CALENDAR_PERCENT');
1633
1634 l_project_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1635 itemkey => p_item_key,
1636 aname => 'PROJECT_NAME');
1637
1638 l_project_number := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1639 itemkey => p_item_key,
1640 aname => 'PROJECT_NUMBER');
1641
1642 l_calendar_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1643 itemkey => p_item_key,
1644 aname => 'CALENDAR_NAME');
1645
1646 l_expense_owner := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1647 itemkey => p_item_key,
1648 aname => 'EXPENSE_OWNER');
1649
1650 --if the attribute has been updated then
1651 --append to x_document to display the Updated Information region in the wf notification
1652 --get the meaning as we will not have it as the field is a poplist on the page
1653 IF l_expense_owner <> FND_API.G_MISS_CHAR AND l_expense_owner IS NOT NULL THEN
1654 SELECT meaning INTO l_expense_owner_meaning
1655 FROM pa_lookups
1656 WHERE lookup_type = 'EXPENSE_OWNER_TYPE'
1657 AND lookup_code = l_expense_owner;
1658 x_document := x_document||get_translated_attr_name('EXPENSE_OWNER')||' - '||l_expense_owner_meaning||'<BR>';
1659 END IF;
1660
1661 l_expense_limit := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1662 itemkey => p_item_key,
1663 aname => 'EXPENSE_LIMIT');
1664
1665 --if the attribute has been updated then
1666 --append to x_document to display the Updated Information region in the wf notification
1667 IF l_expense_limit <> FND_API.G_MISS_NUM AND l_expense_limit IS NOT NULL THEN
1668 x_document := x_document||get_translated_attr_name('EXPENSE_LIMIT')||' - '||l_expense_limit||'<BR>';
1669 END IF;
1670
1671 l_expense_limit_currency_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1672 itemkey => p_item_key,
1673 aname => 'EXPENSE_LIMIT_CURRENCY_CODE');
1674
1675 l_comp_match_weighting := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1676 itemkey => p_item_key,
1677 aname => 'COMP_MATCH_WEIGHTING');
1678
1679 --if the attribute has been updated then
1680 --append to x_document to display the Updated Information region in the wf notification
1681 IF l_comp_match_weighting <> FND_API.G_MISS_NUM AND l_comp_match_weighting IS NOT NULL THEN
1682 x_document := x_document||get_translated_attr_name('COMP_MATCH_WEIGHTING')||' - '||l_comp_match_weighting||'<BR>';
1683 END IF;
1684
1685 l_avail_match_weighting := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1686 itemkey => p_item_key,
1687 aname => 'AVAIL_MATCH_WEIGHTING');
1688
1689 --if the attribute has been updated then
1690 --append to x_document to display the Updated Information region in the wf notification
1691 IF l_avail_match_weighting <> FND_API.G_MISS_NUM AND l_avail_match_weighting IS NOT NULL THEN
1692 x_document := x_document||get_translated_attr_name('AVAIL_MATCH_WEIGHTING')||' - '||l_avail_match_weighting||'<BR>';
1693 END IF;
1694
1695 l_job_level_match_weighting := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1696 itemkey => p_item_key,
1697 aname => 'JOB_LEVEL_MATCH_WEIGHTING');
1698
1699 --if the attribute has been updated then
1700 --append to x_document to display the Updated Information region in the wf notification
1701 IF l_job_level_match_weighting <> FND_API.G_MISS_NUM AND l_job_level_match_weighting IS NOT NULL THEN
1702 x_document := x_document||get_translated_attr_name('JOB_LEVEL_MATCH_WEIGHTING')||' - '||l_job_level_match_weighting||'<BR>';
1703 END IF;
1704
1705 l_search_min_availability := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1706 itemkey => p_item_key,
1707 aname => 'SEARCH_MIN_AVAILABILITY');
1708
1709 --if the attribute has been updated then
1710 --append to x_document to display the Updated Information region in the wf notification
1711 IF l_search_min_availability <> FND_API.G_MISS_NUM AND l_search_min_availability IS NOT NULL THEN
1712 x_document := x_document||get_translated_attr_name('SEARCH_MIN_AVAILABILITY')||' - '||l_search_min_availability||'<BR>';
1713 END IF;
1714
1715 l_search_country_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1716 itemkey => p_item_key,
1717 aname => 'SEARCH_COUNTRY_CODE');
1718
1719 l_search_country_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1720 itemkey => p_item_key,
1721 aname => 'SEARCH_COUNTRY_NAME');
1722
1723 --if the attribute has been updated then
1724 --append to x_document to display the Updated Information region in the wf notification
1725 IF l_search_country_name <> FND_API.G_MISS_CHAR AND l_search_country_name IS NOT NULL THEN
1726 x_document := x_document||get_translated_attr_name('LOCATION_COUNTRY_NAME')||' - '||l_search_country_name||'<BR>';
1727 END IF;
1728
1729 l_search_exp_org_struct_ver_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1730 itemkey => p_item_key,
1731 aname => 'SEARCH_EXP_ORG_STRUCT_VER_ID');
1732
1733 l_search_exp_org_hier_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1734 itemkey => p_item_key,
1735 aname => 'SEARCH_EXP_ORG_HIER_NAME');
1736
1737 --if the attribute has been updated then
1738 --append to x_document to display the Updated Information region in the wf notification
1739 IF l_search_exp_org_hier_name <> FND_API.G_MISS_CHAR AND l_search_exp_org_hier_name IS NOT NULL THEN
1740 x_document := x_document||get_translated_attr_name('SEARCH_EXP_ORG_HIER_NAME')||' - '||l_search_exp_org_hier_name||'<BR>';
1741 END IF;
1742
1743 l_search_exp_start_org_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1744 itemkey => p_item_key,
1745 aname => 'SEARCH_EXP_START_ORG_ID');
1746
1747 l_search_exp_start_org_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1748 itemkey => p_item_key,
1749 aname => 'SEARCH_EXP_START_ORG_NAME');
1750
1751 --if the attribute has been updated then
1752 --append to x_document to display the Updated Information region in the wf notification
1753 IF l_search_exp_start_org_name <> FND_API.G_MISS_CHAR AND l_search_exp_start_org_name IS NOT NULL THEN
1754 x_document := x_document||get_translated_attr_name('SEARCH_EXP_START_ORG_NAME')||' - '||l_search_exp_start_org_name||'<BR>';
1755 END IF;
1756
1757 l_search_min_candidate_score := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1758 itemkey => p_item_key,
1759 aname => 'SEARCH_MIN_CANDIDATE_SCORE');
1760
1761 --if the attribute has been updated then
1762 --append to x_document to display the Updated Information region in the wf notification
1763 IF l_search_min_candidate_score <> FND_API.G_MISS_NUM AND l_search_min_candidate_score IS NOT NULL THEN
1764 x_document := x_document||get_translated_attr_name('SEARCH_MIN_CANDIDATE_SCORE')||' - '||l_search_min_candidate_score||'<BR>';
1765 END IF;
1766
1767 L_ENABLE_AUTO_CAND_NOM_FLAG := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1768 itemkey => p_item_key,
1769 aname => 'ENABLE_AUTO_CAND_NOM_FLAG');
1770
1771 --if the attribute has been updated then
1772 --append to x_document to display the Updated Information region in the wf notification
1773 IF L_ENABLE_AUTO_CAND_NOM_FLAG <> FND_API.G_MISS_CHAR AND L_ENABLE_AUTO_CAND_NOM_FLAG IS NOT NULL THEN
1774 SELECT meaning INTO L_ENABLE_AUTO_CAND_NOM_MEANING
1775 FROM fnd_lookups
1776 WHERE lookup_type='YES_NO'
1777 AND lookup_code = L_ENABLE_AUTO_CAND_NOM_FLAG;
1778 x_document := x_document||get_translated_attr_name('ENABLE_AUTO_CAND_NOM_FLAG')||' - '||L_ENABLE_AUTO_CAND_NOM_MEANING||'<BR>';
1779 END IF;
1780
1781 ELSIF l_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
1782
1783 l_work_type_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1784 itemkey => p_item_key,
1785 aname => 'WORK_TYPE_ID');
1786
1787 l_work_type_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1788 itemkey => p_item_key,
1789 aname => 'WORK_TYPE_NAME');
1790
1791 --if the attribute has been updated then
1792 --append to x_document to display the Updated Information region in the wf notification
1793 --get the name as we will not have it as the field is a poplist on the page
1794 IF l_work_type_id <> FND_API.G_MISS_NUM AND l_work_type_id IS NOT NULL THEN
1795 IF l_work_type_name = FND_API.G_MISS_CHAR OR l_work_type_name IS NULL THEN
1796 SELECT name INTO l_work_type_name
1797 FROM pa_work_types_vl
1798 WHERE work_type_id = l_work_type_id;
1799 END IF;
1800 x_document := x_document||get_translated_attr_name('WORK_TYPE_NAME')||' - '||l_work_type_name||'<BR>';
1801 END IF;
1802
1803 l_fcst_tp_amount_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1804 itemkey => p_item_key,
1805 aname => 'FCST_TP_AMOUNT_TYPE');
1806
1807 --if the attribute has been updated then
1808 --append to x_document to display the Updated Information region in the wf notification
1809 --get the meaning as we will not have it as the field is a poplist on the page
1810 IF l_fcst_tp_amount_type <> FND_API.G_MISS_CHAR AND l_fcst_tp_amount_type IS NOT NULL THEN
1811 SELECT meaning INTO l_fcst_tp_amount_type_meaning
1812 FROM pa_lookups
1813 WHERE lookup_type = 'TP_AMOUNT_TYPE'
1814 AND lookup_code = l_fcst_tp_amount_type;
1815 x_document := x_document||get_translated_attr_name('FCST_TP_AMOUNT_TYPE')||' - '||l_fcst_tp_amount_type_meaning||'<BR>';
1816 END IF;
1817
1818 l_fcst_job_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1819 itemkey => p_item_key,
1820 aname => 'FCST_JOB_ID');
1821
1822 l_fcst_job_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1823 itemkey => p_item_key,
1824 aname => 'FCST_JOB_NAME');
1825
1826 --if the attribute has been updated then
1827 --append to x_document to display the Updated Information region in the wf notification
1828 IF l_fcst_job_name <> FND_API.G_MISS_CHAR AND l_fcst_job_name IS NOT NULL THEN
1829 x_document := x_document||get_translated_attr_name('FCST_JOB_NAME')||' - '||l_fcst_job_name||'<BR>';
1830 END IF;
1831
1832 l_fcst_job_group_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1833 itemkey => p_item_key,
1834 aname => 'FCST_JOB_GROUP_ID');
1835
1836 l_fcst_job_group_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1837 itemkey => p_item_key,
1838 aname => 'FCST_JOB_GROUP_NAME');
1839
1840 --if the attribute has been updated then
1841 --append to x_document to display the Updated Information region in the wf notification
1842 IF l_fcst_job_group_name <> FND_API.G_MISS_CHAR AND l_fcst_job_group_name IS NOT NULL THEN
1843 x_document := x_document||get_translated_attr_name('FCST_JOB_GROUP_NAME')||' - '||l_fcst_job_group_name||'<BR>';
1844 END IF;
1845
1846 l_expenditure_org_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1847 itemkey => p_item_key,
1848 aname => 'EXPENDITURE_ORG_ID');
1849
1850 l_expenditure_org_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1851 itemkey => p_item_key,
1852 aname => 'EXPENDITURE_ORG_NAME');
1853
1854 --if the attribute has been updated then
1855 --append to x_document to display the Updated Information region in the wf notification
1856 IF l_expenditure_org_name <> FND_API.G_MISS_CHAR AND l_expenditure_org_name IS NOT NULL THEN
1857 x_document := x_document||get_translated_attr_name('EXPENDITURE_ORG_NAME')||' - '||l_expenditure_org_name||'<BR>';
1858 END IF;
1859
1860 l_expenditure_organization_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1861 itemkey => p_item_key,
1862 aname => 'EXPENDITURE_ORGANIZATION_ID');
1863
1864 l_exp_organization_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1865 itemkey => p_item_key,
1866 aname => 'EXP_ORGANIZATION_NAME');
1867
1868 --if the attribute has been updated then
1869 --append to x_document to display the Updated Information region in the wf notification
1870 IF l_exp_organization_name <> FND_API.G_MISS_CHAR AND l_exp_organization_name IS NOT NULL THEN
1871 x_document := x_document||get_translated_attr_name('EXP_ORGANIZATION_NAME')||' - '||l_exp_organization_name||'<BR>';
1872 END IF;
1873
1874 l_expenditure_type_class := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1875 itemkey => p_item_key,
1876 aname => 'EXPENDITURE_TYPE_CLASS');
1877
1878 l_expenditure_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1879 itemkey => p_item_key,
1880 aname => 'EXPENDITURE_TYPE');
1881
1882 --if the attribute has been updated then
1883 --append to x_document to display the Updated Information region in the wf notification
1884 IF l_expenditure_type <> FND_API.G_MISS_CHAR AND l_expenditure_type IS NOT NULL THEN
1885 x_document := x_document||get_translated_attr_name('EXPENDITURE_TYPE')||' - '||l_expenditure_type||'<BR>';
1886 END IF;
1887
1888
1889 ELSIF l_mode = G_MASS_UPDATE_SCHEDULE THEN
1890
1891
1892 l_exception_type_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1893 itemkey => p_item_key,
1894 aname => 'EXCEPTION_TYPE_CODE');
1895
1896 l_change_start_date := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1897 itemkey => p_item_key,
1898 aname => 'CHANGE_START_DATE');
1899
1900 IF l_exception_type_code = 'CHANGE_DURATION' THEN
1901 --if the attribute has been updated then
1902 --append to x_document to display the Updated Information region in the wf notification
1903 IF l_change_start_date <> FND_API.G_MISS_DATE THEN
1904 x_document := x_document||get_translated_attr_name('START_DATE')||' - '||l_change_start_date||'<BR>';
1905 END IF;
1906 END IF;
1907
1908 l_change_end_date := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1909 itemkey => p_item_key,
1910 aname => 'CHANGE_END_DATE');
1911
1912 IF l_exception_type_code = 'CHANGE_DURATION' THEN
1913 --if the attribute has been updated then
1914 --append to x_document to display the Updated Information region in the wf notification
1915 IF l_change_end_date <> FND_API.G_MISS_DATE THEN
1916 x_document := x_document||get_translated_attr_name('END_DATE')||' - '||l_change_end_date||'<BR>';
1917 END IF;
1918 END IF;
1919
1920 l_change_rqmt_status_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1921 itemkey => p_item_key,
1922 aname => 'CHANGE_RQMT_STATUS_CODE');
1923
1924 IF l_change_rqmt_status_code <> FND_API.G_MISS_CHAR THEN
1925
1926 SELECT project_status_name INTO l_project_status_name
1927 FROM pa_project_statuses
1928 WHERE project_status_code = l_change_rqmt_status_code
1929 AND status_type='OPEN_ASGMT' ;
1930
1931 x_document := x_document||get_translated_attr_name('STATUS_NAME')||' - '||l_project_status_name||'<BR>';
1932
1933 END IF;
1934
1935 l_change_asgmt_status_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
1936 itemkey => p_item_key,
1937 aname => 'CHANGE_ASGMT_STATUS_CODE');
1938
1939 --if the attribute has been updated then
1940 --append to x_document to display the Updated Information region in the wf notification
1941 --get the name as we will only have the code as the field is a poplist on the page
1942 IF l_change_asgmt_status_code <> FND_API.G_MISS_CHAR THEN
1943 SELECT project_status_name INTO l_project_status_name
1944 FROM pa_project_statuses
1945 WHERE project_status_code = l_change_asgmt_status_code
1946 AND status_type='STAFFED_ASGMT' ;
1947 x_document := x_document||get_translated_attr_name('STATUS_NAME')||' - '||l_project_status_name||'<BR>';
1948 END IF;
1949
1950 -- Multi-period Work Pattern Updates
1951 l_num_of_sch_periods := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1952 itemkey => p_item_key,
1953 aname => 'NUM_OF_SCH_PERIODS');
1954 IF l_num_of_sch_periods > 0 THEN
1955
1956 l_change_start_date_tbl.EXTEND(l_num_of_sch_periods);
1957 l_change_end_date_tbl.EXTEND(l_num_of_sch_periods);
1958 l_monday_hours_tbl.EXTEND(l_num_of_sch_periods);
1959 l_tuesday_hours_tbl.EXTEND(l_num_of_sch_periods);
1960 l_wednesday_hours_tbl.EXTEND(l_num_of_sch_periods);
1961 l_thursday_hours_tbl.EXTEND(l_num_of_sch_periods);
1962 l_friday_hours_tbl.EXTEND(l_num_of_sch_periods);
1963 l_saturday_hours_tbl.EXTEND(l_num_of_sch_periods);
1964 l_sunday_hours_tbl.EXTEND(l_num_of_sch_periods);
1965
1966 FOR j IN 1 .. l_num_of_sch_periods LOOP
1967
1968 l_change_start_date_tbl(j) := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1969 itemkey => p_item_key,
1970 aname => 'CHANGE_START_DATE'||j);
1971
1972 --if the attribute has been updated then
1973 --append to x_document to display the Updated Information region in the wf notification
1974 IF l_change_start_date_tbl(j) <> FND_API.G_MISS_DATE THEN
1975 x_document := x_document||get_translated_attr_name('START_DATE')||' - '||l_change_start_date_tbl(j)||'<BR>';
1976 END IF;
1977
1978 l_change_end_date_tbl(j) := WF_ENGINE.GetItemAttrDate(itemtype => p_item_type,
1979 itemkey => p_item_key,
1980 aname => 'CHANGE_END_DATE'||j);
1981
1982 --if the attribute has been updated then
1983 --append to x_document to display the Updated Information region in the wf notification
1984 IF l_change_end_date_tbl(j) <> FND_API.G_MISS_DATE THEN
1985 x_document := x_document||get_translated_attr_name('END_DATE')||' - '||l_change_end_date_tbl(j)||'<BR>';
1986 END IF;
1987
1988 l_monday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1989 itemkey => p_item_key,
1990 aname => 'MONDAY_HOURS'||j);
1991
1992 --if the attribute has been updated then
1993 --append to x_document to display the Updated Information region in the wf notification
1994 IF l_monday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_monday_hours_tbl(j) IS NOT NULL THEN
1995 x_document := x_document||get_translated_attr_name('MONDAY_HOURS')||' - '||l_monday_hours_tbl(j)||'<BR>';
1996 END IF;
1997
1998 l_tuesday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1999 itemkey => p_item_key,
2000 aname => 'TUESDAY_HOURS'||j);
2001
2002 --if the attribute has been updated then
2003 --append to x_document to display the Updated Information region in the wf notification
2004 IF l_tuesday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_tuesday_hours_tbl(j) IS NOT NULL THEN
2005 x_document := x_document||get_translated_attr_name('TUESDAY_HOURS')||' - '||l_tuesday_hours_tbl(j)||'<BR>';
2006 END IF;
2007
2008 l_wednesday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2009 itemkey => p_item_key,
2010 aname => 'WEDNESDAY_HOURS'||j);
2011
2012 --if the attribute has been updated then
2013 --append to x_document to display the Updated Information region in the wf notification
2014 IF l_wednesday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_wednesday_hours_tbl(j) IS NOT NULL THEN
2015 x_document := x_document||get_translated_attr_name('WEDNESDAY_HOURS')||' - '||l_wednesday_hours_tbl(j)||'<BR>';
2016 END IF;
2017
2018 l_thursday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2019 itemkey => p_item_key,
2020 aname => 'THURSDAY_HOURS'||j);
2021
2022 --if the attribute has been updated then
2023 --append to x_document to display the Updated Information region in the wf notification
2024 IF l_thursday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_thursday_hours_tbl(j) IS NOT NULL THEN
2025 x_document := x_document||get_translated_attr_name('THURSDAY_HOURS')||' - '||l_thursday_hours_tbl(j)||'<BR>';
2026 END IF;
2027
2028 l_friday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2029 itemkey => p_item_key,
2030 aname => 'FRIDAY_HOURS'||j);
2031
2032 --if the attribute has been updated then
2033 --append to x_document to display the Updated Information region in the wf notification
2034 IF l_friday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_friday_hours_tbl(j) IS NOT NULL THEN
2035 x_document := x_document||get_translated_attr_name('FRIDAY_HOURS')||' - '||l_friday_hours_tbl(j)||'<BR>';
2036 END IF;
2037
2038 l_saturday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2039 itemkey => p_item_key,
2040 aname => 'SATURDAY_HOURS'||j);
2041
2042 --if the attribute has been updated then
2043 --append to x_document to display the Updated Information region in the wf notification
2044 IF l_saturday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_saturday_hours_tbl(j) IS NOT NULL THEN
2045 x_document := x_document||get_translated_attr_name('SATURDAY_HOURS')||' - '||l_saturday_hours_tbl(j)||'<BR>';
2046 END IF;
2047
2048 l_sunday_hours_tbl(j) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2049 itemkey => p_item_key,
2050 aname => 'SUNDAY_HOURS'||j);
2051
2052 --if the attribute has been updated then
2053 --append to x_document to display the Updated Information region in the wf notification
2054 IF l_sunday_hours_tbl(j) <> FND_API.G_MISS_NUM AND l_sunday_hours_tbl(j) IS NOT NULL THEN
2055 x_document := x_document||get_translated_attr_name('SUNDAY_HOURS')||' - '||l_sunday_hours_tbl(j)||'<BR>';
2056 END IF;
2057
2058 END LOOP;
2059 END IF; -- End if for muti-period work pattern updates
2060
2061 l_non_working_day_flag := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2062 itemkey => p_item_key,
2063 aname => 'NON_WORKING_DAY_FLAG');
2064
2065 l_change_hours_type_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2066 itemkey => p_item_key,
2067 aname => 'CHANGE_HOURS_TYPE_CODE');
2068
2069 l_hrs_per_day := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2070 itemkey => p_item_key,
2071 aname => 'HRS_PER_DAY');
2072
2073 --if the attribute has been updated then
2074 --append to x_document to display the Updated Information region in the wf notification
2075 IF l_hrs_per_day <> FND_API.G_MISS_NUM AND l_hrs_per_day IS NOT NULL THEN
2076 x_document := x_document||get_translated_attr_name('HRS_PER_DAY')||' - '||l_hrs_per_day||'<BR>';
2077 END IF;
2078
2079 l_change_calendar_type_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2080 itemkey => p_item_key,
2081 aname => 'CHANGE_CALENDAR_TYPE_CODE');
2082
2083 l_change_calendar_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2084 itemkey => p_item_key,
2085 aname => 'CHANGE_CALENDAR_NAME');
2086
2087 IF l_change_calendar_name <> FND_API.G_MISS_CHAR and l_change_calendar_name IS NOT NULL THEN
2088 l_calendar_display := ': '||l_change_calendar_name;
2089 END IF;
2090
2091 l_change_calendar_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2092 itemkey => p_item_key,
2093 aname => 'CHANGE_CALENDAR_ID');
2094
2095 l_calendar_percent := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2096 itemkey => p_item_key,
2097 aname => 'CALENDAR_PERCENT');
2098
2099 --if the attribute has been updated then
2100 --append to x_document to display the Updated Information region in the wf notification
2101 IF l_calendar_percent <> FND_API.G_MISS_NUM AND l_calendar_percent IS NOT NULL THEN
2102 x_document := x_document||get_translated_attr_name('CALENDAR_PERCENT')||': '||get_translated_attr_name(l_change_calendar_type_code||'_CALENDAR')||l_calendar_display||' - '||l_calendar_percent||'<BR>';
2103 END IF;
2104
2105
2106 l_duration_shift_type_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2107 itemkey => p_item_key,
2108 aname => 'DURATION_SHIFT_TYPE_CODE');
2109
2110 l_duration_shift_unit_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2111 itemkey => p_item_key,
2112 aname => 'DURATION_SHIFT_UNIT_CODE');
2113
2114 l_num_of_shift := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2115 itemkey => p_item_key,
2116 aname => 'NUM_OF_SHIFT');
2117
2118 IF l_num_of_shift <> FND_API.G_MISS_NUM AND l_num_of_shift IS NOT NULL THEN
2119 x_document := x_document||get_translated_attr_name('NUM_OF_SHIFT')||' '||l_num_of_shift||' '||get_translated_attr_name(l_duration_shift_unit_code)||' '||get_translated_attr_name(l_duration_shift_type_code)||'<BR>';
2120 END IF;
2121
2122 ELSIF l_mode = G_MASS_UPDATE_COMPETENCIES THEN
2123
2124 l_number_of_competencies := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2125 itemkey => p_item_key,
2126 aname => 'NUMBER_OF_COMPETENCIES');
2127
2128 IF l_number_of_competencies > 0 THEN
2129
2130 l_competence_id_tbl.EXTEND(l_number_of_competencies);
2131 l_competence_name_tbl.EXTEND(l_number_of_competencies);
2132 l_competence_alias_tbl.EXTEND(l_number_of_competencies);
2133 l_rating_level_id_tbl.EXTEND(l_number_of_competencies);
2134 l_mandatory_flag_tbl.EXTEND(l_number_of_competencies);
2135
2136 FOR i IN 1 .. l_number_of_competencies LOOP
2137
2138 l_competence_id_tbl(i) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2139 itemkey => p_item_key,
2140 aname => 'COMPETENCE_ID'||i);
2141
2142 l_competence_name_tbl(i) := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2143 itemkey => p_item_key,
2144 aname => 'COMPETENCE_NAME'||i);
2145
2146 l_competence_alias_tbl(i) := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2147 itemkey => p_item_key,
2148 aname => 'COMPETENCE_ALIAS'||i);
2149
2150 l_rating_level_id_tbl(i) := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2151 itemkey => p_item_key,
2152 aname => 'RATING_LEVEL_ID'||i);
2153
2154 --if rating level id is not null then get the actual rating level
2155 --in order to display on the wf notification.
2156 IF l_rating_level_id_tbl(i) <> FND_API.G_MISS_NUM AND l_rating_level_id_tbl(i) IS NOT NULL THEN
2157 SELECT step_value INTO l_rating_level
2158 FROM per_rating_levels_v
2159 WHERE rating_level_id = l_rating_level_id_tbl(i);
2160 END IF;
2161
2162 l_mandatory_flag_tbl(i) := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2163 itemkey => p_item_key,
2164 aname => 'MANDATORY_FLAG'||i);
2165
2166 --if mandatory_flag is not null then get the meaning
2167 --in order to display on the wf notification.
2168 IF l_mandatory_flag_tbl(i) <> FND_API.G_MISS_CHAR AND l_mandatory_flag_tbl(i) IS NOT NULL THEN
2169 SELECT meaning INTO l_mandatory_flag_meaning
2170 FROM fnd_lookups
2171 WHERE lookup_type = 'YES_NO'
2172 AND lookup_code = l_mandatory_flag_tbl(i);
2173 END IF;
2174
2175 --for updated competencies append to x_document to display
2176 --the Updated Information region in the wf notification
2177 x_document := x_document||get_translated_attr_name('COMPETENCE_NAME')||' - '||l_competence_name_tbl(i)||', '||get_translated_attr_name('COMPETENCE_ALIAS')||' - '
2178 ||l_competence_alias_tbl(i)||', '||get_translated_attr_name('PROFICIENCY_LEVEL')||' - '||l_rating_level||', '||get_translated_attr_name('MANDATORY_FLAG')||' - '||l_mandatory_flag_meaning||'<BR>';
2179
2180 END LOOP;
2181
2182 END IF;
2183
2184 END IF;
2185
2186 --if this is a mass assignment then assign the resource id tbl to the object id tbl.
2187 --otherwise assign the assignment id tbl to the object id tbl.
2188 IF l_mode = G_MASS_ASGMT THEN
2189 l_object_id_tbl := p_resource_id_tbl;
2190 ELSE
2191 l_object_id_tbl := p_assignment_id_tbl;
2192 END IF;
2193
2194 --if this is not a mass submit then call the apis to create/update assignments.
2195 IF l_mode <> G_MASS_SUBMIT_FOR_APPROVAL THEN
2196
2197 IF l_object_id_tbl.COUNT > 0 THEN
2198
2199 --loop through the objects ids and call the api one resource/assignment at a time.
2200 --it is done this way because a requirement came at the late stages that the
2201 --process should be re-runnable in case of unexpected error. In order to only have
2202 --an impact on this API, it is done in this way.
2203 FOR i IN l_object_id_tbl.FIRST .. l_object_id_tbl.LAST LOOP
2204
2205 -- Bug 2402193: reset the l_assignment_type
2206 l_assignment_type := l_wf_assignment_type;
2207 --store the object id being processed in l_single_obj_id_tbl
2208 l_single_obj_id_tbl := SYSTEM.pa_num_tbl_type(l_object_id_tbl(i));
2209
2210 --call mass create assignments API if mode is mass asgmt
2211
2212 IF l_mode = G_MASS_ASGMT THEN
2213
2214 PA_ASSIGNMENTS_PUB.Mass_Create_Assignments
2215 (p_asgn_creation_mode => 'MASS',
2216 p_assignment_name => l_assignment_name ,
2217 p_assignment_type => l_assignment_type ,
2218 p_multiple_status_flag => l_multiple_status_flag,
2219 p_status_code => l_status_code ,
2220 p_staffing_priority_code => l_staffing_priority_code ,
2221 p_project_id => l_project_id ,
2222 p_project_role_id => l_project_role_id ,
2223 p_role_list_id => l_role_list_id ,
2224 p_resource_id_tbl => l_single_obj_id_tbl,
2225 p_project_subteam_id => l_project_subteam_id ,
2226 p_description => l_description ,
2227 p_start_date => l_start_date ,
2228 p_end_date => l_end_date ,
2229 p_extension_possible => l_extension_possible ,
2230 p_min_resource_job_level => l_min_resource_job_level ,
2231 p_max_resource_job_level => l_max_resource_job_level ,
2232 p_additional_information => l_additional_information ,
2233 p_location_id => l_location_id ,
2234 p_work_type_id => l_work_type_id ,
2235 p_calendar_type => l_calendar_type ,
2236 p_calendar_id => l_calendar_id ,
2237 p_resource_calendar_percent => l_resource_calendar_percent ,
2238 p_project_name => l_project_name ,
2239 p_project_number => l_project_number ,
2240 p_project_subteam_name => l_project_subteam_name ,
2241 p_project_status_name => l_project_status_name ,
2242 p_staffing_priority_name => l_staffing_priority_name ,
2243 p_project_role_name => l_project_role_name ,
2244 p_location_city => l_location_city ,
2245 p_location_region => l_location_region ,
2246 p_location_country_name => l_location_country_name ,
2247 p_location_country_code => l_location_country_code ,
2248 p_calendar_name => l_calendar_name ,
2249 p_work_type_name => l_work_type_name ,
2250 p_init_msg_list => FND_API.G_TRUE ,
2251 p_commit => FND_API.G_TRUE ,
2252 p_validate_only => FND_API.G_FALSE ,
2253 x_success_assignment_id_tbl => l_success_assignment_id_tbl,
2254 x_return_status => l_return_status,
2255 x_msg_count => l_msg_count,
2256 x_msg_data => l_msg_data
2257 );
2258
2259
2260
2261 IF l_success_assignment_id_tbl(1) IS NOT NULL THEN
2262
2263 OPEN csr_get_tp_amt_type(l_success_assignment_id_tbl(1));
2264 FETCH csr_get_tp_amt_type into l_tp_amt_type_name;
2265 CLOSE csr_get_tp_amt_type;
2266
2267 END IF;
2268
2269 WF_ENGINE.SetItemAttrText(itemtype => p_item_type,
2270 itemkey => p_item_key,
2271 aname => 'TP_AMT_TYPE_NAME',
2272 avalue => l_tp_amt_type_name);
2273
2274 --if the assignment was created successfully and the assignment will
2275 --be submitted for approval, then set the mass wf in progress flag
2276 --to 'Y' on the newly created assignment in order to prevent updates
2277 --until the approval process is complete.
2278 IF l_success_assignment_id_tbl(1) IS NOT NULL AND l_action <> G_SAVE THEN
2279
2280 UPDATE pa_project_assignments
2281 SET mass_wf_in_progress_flag = 'Y'
2282 WHERE assignment_id = l_success_assignment_id_tbl(1);
2283
2284 END IF;
2285
2286 --call mass upate assignments api if mode is mass update basic info
2287 ELSIF l_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
2288
2289 PA_ASSIGNMENTS_PUB.Mass_Update_Assignments
2290 ( p_update_mode => l_mode,
2291 p_assignment_id_tbl => l_single_obj_id_tbl,
2292 p_assignment_type => l_assignment_type ,
2293 p_assignment_name => l_assignment_name ,
2294 p_staffing_priority_code => l_staffing_priority_code ,
2295 p_project_id => l_project_id ,
2296 p_project_subteam_id => l_project_subteam_id ,
2297 p_append_description_flag => l_append_description_flag ,
2298 p_description => l_description ,
2299 p_extension_possible => l_extension_possible ,
2300 p_min_resource_job_level => l_min_resource_job_level ,
2301 p_max_resource_job_level => l_max_resource_job_level ,
2302 p_append_information_flag => l_append_information_flag ,
2303 p_additional_information => l_additional_information ,
2304 p_location_id => l_location_id ,
2305 p_expense_owner => l_expense_owner ,
2306 p_expense_limit => l_expense_limit ,
2307 p_expense_limit_currency_code => l_expense_limit_currency_code ,
2308 p_project_subteam_name => l_project_subteam_name ,
2309 p_staffing_priority_name => l_staffing_priority_name ,
2310 p_location_city => l_location_city ,
2311 p_location_region => l_location_region ,
2312 p_location_country_name => l_location_country_name ,
2313 p_location_country_code => l_location_country_code ,
2314 p_comp_match_weighting => l_comp_match_weighting,
2315 p_avail_match_weighting => l_avail_match_weighting,
2316 p_job_level_match_weighting => l_job_level_match_weighting,
2317 p_search_min_availability => l_search_min_availability,
2318 p_search_country_code => l_search_country_code,
2319 p_search_country_name => l_search_country_name,
2320 p_search_exp_org_struct_ver_id => l_search_exp_org_struct_ver_id,
2321 p_search_exp_org_hier_name => l_search_exp_org_hier_name,
2322 p_search_exp_start_org_id => l_search_exp_start_org_id,
2323 p_search_exp_start_org_name => l_search_exp_start_org_name,
2324 p_search_min_candidate_score => l_search_min_candidate_score,
2325 p_enable_auto_cand_nom_flag => l_enable_auto_cand_nom_flag,
2326 p_staffing_owner_person_id => l_staffing_owner_person_id,
2327 p_staffing_owner_name => l_staffing_owner_name,
2328 p_commit => FND_API.G_TRUE ,
2329 p_validate_only => FND_API.G_FALSE ,
2330 x_success_assignment_id_tbl => l_success_assignment_id_tbl,
2331 x_return_status => l_return_status,
2332 x_msg_count => l_msg_count,
2333 x_msg_data => l_msg_data);
2334
2335 --call mass process competencies api if mode is mass update competencies
2336 ELSIF l_mode = G_MASS_UPDATE_COMPETENCIES THEN
2337
2338 PA_COMPETENCE_PUB.Mass_Process_Competences
2339 (p_project_id => l_project_id ,
2340 p_assignment_tbl => l_single_obj_id_tbl ,
2341 p_competence_id_tbl => l_competence_id_tbl ,
2342 p_competence_name_tbl => l_competence_name_tbl,
2343 p_competence_alias_tbl => l_competence_alias_tbl,
2344 p_rating_level_id_tbl => l_rating_level_id_tbl,
2345 p_mandatory_flag_tbl => l_mandatory_flag_tbl,
2346 p_commit => FND_API.G_TRUE ,
2347 p_validate_only => FND_API.G_FALSE ,
2348 x_success_assignment_id_tbl => l_success_assignment_id_tbl,
2349 x_return_status => l_return_status,
2350 x_msg_count => l_msg_count,
2351 x_msg_data => l_msg_data);
2352
2353 --call mass update assignments api if mode is mass update forecast items
2354 ELSIF l_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
2355
2356 PA_ASSIGNMENTS_PUB.Mass_Update_Assignments
2357 ( p_update_mode => l_mode,
2358 p_assignment_id_tbl => l_single_obj_id_tbl ,
2359 p_project_id => l_project_id,
2360 p_fcst_tp_amount_type => l_fcst_tp_amount_type ,
2361 p_fcst_job_id => l_fcst_job_id ,
2362 p_fcst_job_group_id => l_fcst_job_group_id ,
2363 p_expenditure_org_id => l_expenditure_org_id ,
2364 p_expenditure_organization_id => l_expenditure_organization_id ,
2365 p_expenditure_type_class => l_expenditure_type_class ,
2366 p_expenditure_type => l_expenditure_type ,
2367 p_work_type_name => l_work_type_name ,
2368 p_work_type_id => l_work_type_id ,
2369 p_fcst_job_name => l_fcst_job_name ,
2370 p_fcst_job_group_name => l_fcst_job_group_name ,
2371 p_expenditure_org_name => l_expenditure_org_name ,
2372 p_exp_organization_name => l_exp_organization_name ,
2373 p_commit => FND_API.G_TRUE ,
2374 p_validate_only => FND_API.G_FALSE ,
2375 x_success_assignment_id_tbl => l_success_assignment_id_tbl,
2376 x_return_status => l_return_status,
2377 x_msg_count => l_msg_count,
2378 x_msg_data => l_msg_data);
2379
2380 --call mass update schedule api if mode is mass update schedule
2381 ELSIF l_mode = G_MASS_UPDATE_SCHEDULE THEN
2382
2383 PA_SCHEDULE_PUB.mass_update_schedule
2384 ( p_project_id => l_project_id ,
2385 p_exception_type_code => l_exception_type_code ,
2386 p_assignment_id_array => l_single_obj_id_tbl ,
2387 p_change_start_date => l_change_start_date ,
2388 p_change_end_date => l_change_end_date ,
2389 p_change_rqmt_status_code => l_change_rqmt_status_code ,
2390 p_change_asgmt_status_code => l_change_asgmt_status_code ,
2391 p_change_start_date_tbl => l_change_start_date_tbl,
2392 p_change_end_date_tbl => l_change_end_date_tbl,
2393 p_monday_hours_tbl => l_monday_hours_tbl ,
2394 p_tuesday_hours_tbl => l_tuesday_hours_tbl ,
2395 p_wednesday_hours_tbl => l_wednesday_hours_tbl ,
2396 p_thursday_hours_tbl => l_thursday_hours_tbl ,
2397 p_friday_hours_tbl => l_friday_hours_tbl ,
2398 p_saturday_hours_tbl => l_saturday_hours_tbl ,
2399 p_sunday_hours_tbl => l_sunday_hours_tbl ,
2400 p_non_working_day_flag => l_non_working_day_flag ,
2401 p_change_hours_type_code => l_change_hours_type_code ,
2402 p_hrs_per_day => l_hrs_per_day ,
2403 p_calendar_percent => l_calendar_percent ,
2404 p_change_calendar_type_code => l_change_calendar_type_code ,
2405 p_change_calendar_name => l_change_calendar_name ,
2406 p_change_calendar_id => l_change_calendar_id ,
2407 p_duration_shift_type_code => l_duration_shift_type_code ,
2408 p_duration_shift_unit_code => l_duration_shift_unit_code ,
2409 p_number_of_shift => l_num_of_shift ,
2410 p_commit => FND_API.G_TRUE ,
2411 x_success_assignment_id_tbl => l_success_assignment_id_tbl,
2412 x_return_status => l_return_status,
2413 x_msg_count => l_msg_count,
2414 x_msg_data => l_msg_data);
2415
2416 END IF;
2417
2418 --set the mass_wf_in_progress_flag to 'N' if the assignments will not be submitted
2419 --for approval and this is not a mass assignment. (If it is a mass assignment
2420 --not is not submitted then the flag will not already be set to 'Y'
2421 IF (l_assignment_type IS NULL OR l_assignment_type = FND_API.G_MISS_CHAR) THEN
2422 SELECT assignment_type INTO l_assignment_type
2423 FROM pa_project_assignments
2424 WHERE assignment_id = l_single_obj_id_tbl(1);
2425 END IF;
2426 IF (l_action = G_SAVE OR l_success_assignment_id_tbl(1) IS NULL OR l_assignment_type = 'OPEN_ASSIGNMENT') AND l_mode <> G_MASS_ASGMT THEN
2427
2428 UPDATE pa_project_assignments
2429 SET mass_wf_in_progress_flag = 'N'
2430 WHERE assignment_id = l_single_obj_id_tbl(1);
2431
2432 END IF;
2433
2434 --set pa_wf_process_details.process_status_code.
2435 --if the assignment was created/updated successfully then l_success_assignment_id_tbl(1)
2436 --will be not null - so set process_status_code to 'S'
2437 --otherwise the assignemnt was not created/updated successfully due to a validation
2438 --error so set process_status_code to 'E'.
2439 UPDATE pa_wf_process_details
2440 SET process_status_code = decode(l_success_assignment_id_tbl(1), NULL, 'E', 'S'),
2441 object_id2 = decode(l_mode, G_MASS_ASGMT, l_success_assignment_id_tbl(1), NULL)
2442 WHERE item_type = p_item_type
2443 AND item_key = p_item_key
2444 AND object_id1 = l_single_obj_id_tbl(1);
2445
2446 --need to commit here in this autonomous transaction in case of
2447 --an unexpected error later on. This will make the process
2448 --rerunnable from the point of the unexpected error. Only
2449 --items with a process_status_code = 'P' will be picked up to
2450 --process - items already processed will not be picked up again.
2451 --the actual API called above will commit OR rollback the actual trx data
2452 --as p_commit is passed as True.
2453 COMMIT;
2454
2455 END LOOP;
2456 END IF;
2457
2458 --if l_mode is mass submit for approval then set everything to S.
2459 ELSIF l_object_id_tbl.COUNT > 0 THEN
2460
2461 FORALL i IN l_object_id_tbl.FIRST .. l_object_id_tbl.LAST
2462 UPDATE pa_wf_process_details
2463 SET process_status_code = 'S'
2464 WHERE item_type = p_item_type
2465 AND item_key = p_item_key
2466 AND object_id1 = l_object_id_tbl(i);
2467 COMMIT;
2468 END IF;
2469
2470 -- Bug 2513254
2471 -- Get the project_id from project_number and return the project_id
2472 -- This handles the case when user performs Mass Assignment Creation
2473 -- (Add Delivery/Admin Assignment) by entering the project number
2474 -- without using the LOV
2475 IF l_mode = G_MASS_ASGMT AND (l_project_id IS NULL OR l_project_id = FND_API.G_MISS_NUM)
2476 AND l_project_number IS NOT NULL AND l_project_number <> FND_API.G_MISS_CHAR THEN
2477
2478 select project_id into l_project_id
2479 from pa_projects_all
2480 where segment1 = l_project_number;
2481
2482 x_project_id := l_project_id;
2483
2484 END IF;
2485
2486
2487 EXCEPTION
2488 WHEN OTHERS THEN
2489 --need to end the autonomous transaction.
2490
2491 -- 4537865 : RESET OUT PARAMS
2492
2493 x_mode := NULL ;
2494 x_action := NULL ;
2495 x_start_date := NULL ;
2496 x_end_date := NULL ;
2497 x_project_id := NULL ;
2498 x_document := 'An Unexpected error has occured - ' || SUBSTRB(SQLERRM,1,240) ;
2499
2500 WF_CORE.CONTEXT('pa_mass_asgmt_trx','mass_asgmt_autonomous_trx' ,p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2501 -- End : 4537865
2502
2503 ROLLBACK;
2504 RAISE;
2505
2506 END mass_asgmt_autonomous_trx;
2507
2508
2509 PROCEDURE Start_Mass_Apprvl_WF_If_Req
2510 (p_item_type IN VARCHAR2,
2511 p_item_key IN VARCHAR2,
2512 p_actid IN NUMBER,
2513 p_funcmode IN VARCHAR2,
2514 p_result OUT NOCOPY VARCHAR2) -- 4537865
2515 IS
2516
2517 l_mode VARCHAR2(30);
2518 l_action VARCHAR2(30);
2519 l_approver1_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2520 l_approver2_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2521 l_approver1_name_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
2522 l_approver2_name_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
2523 l_appr_over_auth_flag VARCHAR2(1);
2524 l_note_to_all_approvers VARCHAR2(2000);
2525 l_conflict_group_id NUMBER;
2526 l_return_status VARCHAR2(1);
2527 l_msg_count NUMBER;
2528 l_msg_data fnd_new_messages.message_text%TYPE;
2529 l_assignment_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2530 l_document VARCHAR2(32767);
2531 l_project_id pa_projects_all.project_id%TYPE;
2532 l_submitter_user_id NUMBER;
2533 l_num_success_assignments NUMBER;
2534
2535
2536 BEGIN
2537
2538 --get the mode and action for this wf process
2539 l_mode := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2540 itemkey => p_item_key,
2541 aname => 'MODE');
2542
2543 l_action := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2544 itemkey => p_item_key,
2545 aname => 'ACTION');
2546
2547 l_num_success_assignments := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2548 itemkey => p_item_key,
2549 aname => 'NUM_SUCCESS_ASSIGNMENTS');
2550
2551 --if the assignments in this process are to be submitted then
2552 IF (l_mode = G_MASS_SUBMIT_FOR_APPROVAL OR l_action = G_SAVE_AND_SUBMIT) AND
2553 l_num_success_assignments > 0 THEN
2554
2555 --get the conflict group id
2556 --this is used to filter out the assignments that the user chose to
2557 --cancel or revert due to overcommitment and not to pass them on to
2558 --be submitted.
2559 l_conflict_group_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2560 itemkey => p_item_key,
2561 aname => 'CONFLICT_GROUP_ID');
2562
2563
2564 --select the assignments that were successfully processed that were
2565 --not selected to be canceled or reverted.
2566 SELECT asgmt.assignment_id,
2567 wf.source_attribute1,
2568 wf.source_attribute2,
2569 wf.source_attribute3,
2570 wf.source_attribute4
2571 BULK COLLECT INTO l_assignment_id_tbl,
2572 l_approver1_id_tbl,
2573 l_approver1_name_tbl,
2574 l_approver2_id_tbl,
2575 l_approver2_name_tbl
2576 FROM pa_wf_process_details wf,
2577 pa_project_assignments asgmt
2578 WHERE wf.item_type = p_item_type
2579 AND wf.item_key = p_item_key
2580 AND wf.process_status_code = 'S'
2581 AND decode(l_mode, G_MASS_ASGMT, object_id2, object_id1) = asgmt.assignment_id
2582 AND asgmt.assignment_type <> 'OPEN_ASSIGNMENT'
2583 AND asgmt.assignment_id NOT IN (
2584 SELECT distinct assignment_id
2585 FROM pa_assignment_conflict_hist
2586 WHERE conflict_group_id = l_conflict_group_id
2587 AND resolve_conflicts_action_code IN ('CANCEL_TXN_ITEM', 'REVERT_TXN_ITEM'))
2588 ;
2589
2590
2591 --if there are any assignment ids to be submitted for approval then get
2592 --the approval attributes.
2593 IF l_assignment_id_tbl.COUNT > 0 THEN
2594
2595
2596 l_appr_over_auth_flag := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2597 itemkey => p_item_key,
2598 aname => 'APPR_OVER_AUTH_FLAG');
2599
2600 l_note_to_all_approvers := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2601 itemkey => p_item_key,
2602 aname => 'NOTE_TO_ALL_APPROVERS');
2603
2604 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2605 itemkey => p_item_key,
2606 aname => 'PROJECT_ID');
2607
2608 l_document := WF_ENGINE.GetItemAttrDocument(itemtype => p_item_type,
2609 itemkey => p_item_key,
2610 aname => ' UPDATED_INFORMATION_DOCUMENT',
2611 ignore_notfound => TRUE);
2612
2613 l_submitter_user_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2614 itemkey => p_item_key,
2615 aname => 'SUBMITTER_USER_ID');
2616
2617 --call the submit for approval API.
2618 PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval
2619 (p_project_id => l_project_id,
2620 p_mode => l_mode,
2621 p_assignment_id_tbl => l_assignment_id_tbl,
2622 p_approver1_id_tbl => l_approver1_id_tbl,
2623 p_approver1_name_tbl => l_approver1_name_tbl,
2624 p_approver2_id_tbl => l_approver2_id_tbl,
2625 p_approver2_name_tbl => l_approver2_name_tbl,
2626 p_overriding_authority_flag => l_appr_over_auth_flag,
2627 p_note_to_all_approvers => l_note_to_all_approvers,
2628 p_conflict_group_id => l_conflict_group_id,
2629 p_update_info_doc => l_document,
2630 p_submitter_user_id => l_submitter_user_id,
2631 x_return_status => l_return_status,
2632 x_msg_count => l_msg_count,
2633 x_msg_data => l_msg_data);
2634
2635 END IF;
2636
2637 END IF;
2638 -- 4537865 : Included EXCEPTION BLOCK
2639 EXCEPTION
2640 WHEN OTHERS THEN
2641 -- I havent reset value of p_result as this param is not assigned value anywhere in this API.
2642 -- The Workflow function (Start Apprvl WF If Required) doesnt expect any result type
2643
2644 -- Included as per discussion with Rajnish : 4537865
2645 Wf_Core.Context('pa_mass_asgmt_trx','Start_Mass_Apprvl_WF_If_Req',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2646 RAISE ;
2647 END Start_Mass_Apprvl_WF_If_Req;
2648
2649
2650 PROCEDURE Revert_Cancel_Overcom_Items
2651 (p_item_type IN VARCHAR2,
2652 p_item_key IN VARCHAR2,
2653 p_actid IN NUMBER,
2654 p_funcmode IN VARCHAR2,
2655 p_result OUT NOCOPY VARCHAR2) IS -- 4537865
2656
2657 l_conflict_group_id NUMBER;
2658 l_return_status VARCHAR2(1);
2659 l_msg_count NUMBER;
2660 l_msg_data fnd_new_messages.message_text%TYPE;
2661
2662 BEGIN
2663
2664 --from the View Conflicts page from the notification to the submitter the
2665 --submitter may choose to cancel or revert certain assignments.
2666 --this API is called after the user has taken action on conflicts and
2667 --closed the notification in order to cancel or revert those assignments.
2668 l_conflict_group_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2669 itemkey => p_item_key,
2670 aname => 'CONFLICT_GROUP_ID');
2671
2672 --set mass_wf_in_progress_flag = 'N' for those assignments to be canceled
2673 --or reverted.
2674 UPDATE pa_project_assignments
2675 SET mass_wf_in_progress_flag = 'N'
2676 WHERE assignment_id IN
2677 (SELECT distinct assignment_id
2678 FROM pa_assignment_conflict_hist
2679 WHERE conflict_group_id = l_conflict_group_id
2680 AND resolve_conflicts_action_code IN ('CANCEL_TXN_ITEM', 'REVERT_TXN_ITEM'));
2681
2682
2683 --call APIs to revert / cancel assignments based on the conflict group id.
2684 PA_SCHEDULE_PVT.Revert_Overcom_Txn_Items
2685 (p_conflict_group_id => l_conflict_group_id,
2686 x_return_status => l_return_status,
2687 x_msg_count => l_msg_count,
2688 x_msg_data => l_msg_data);
2689
2690 PA_SCHEDULE_PVT.Cancel_Overcom_Txn_Items
2691 (p_conflict_group_id => l_conflict_group_id,
2692 x_return_status => l_return_status,
2693 x_msg_count => l_msg_count,
2694 x_msg_data => l_msg_data);
2695
2696 -- 4537865 : Included EXCEPTION BLOCK
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 -- I havent reset value of p_result as this param is not assigned value anywhere in this API.
2700 -- The Workflow function (Revert/Cancel Overcom Items) doesnt expect any result type
2701
2702 -- Included as per discussion with Rajnish : 4537865
2703 Wf_Core.Context('pa_mass_asgmt_trx','Revert_Cancel_Overcom_Items',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2704 RAISE;
2705 END Revert_Cancel_Overcom_Items;
2706
2707 --this function is not currently used.
2708 FUNCTION Is_Asgmt_Revert_Or_Cancel(p_conflict_group_id IN NUMBER,
2709 p_assignment_id IN pa_project_assignments.assignment_id%TYPE)
2710 RETURN BOOLEAN IS
2711
2712 l_resolve_con_action_code pa_lookups.meaning%TYPE;
2713
2714 CURSOR check_asgmt_revert_or_cancel IS
2715 SELECT resolve_conflicts_action_code
2716 FROM pa_assignment_conflict_hist
2717 WHERE conflict_group_id = p_conflict_group_id
2718 AND assignment_id = p_assignment_id;
2719
2720 BEGIN
2721
2722 OPEN check_asgmt_revert_or_cancel;
2723
2724 FETCH check_asgmt_revert_or_cancel INTO l_resolve_con_action_code;
2725
2726 CLOSE check_asgmt_revert_or_cancel;
2727
2728 IF l_resolve_con_action_code = 'CANCEL_TXN_ITEM' OR l_resolve_con_action_code = 'REVERT_TXN_ITEM' THEN
2729
2730 RETURN TRUE;
2731
2732 ELSE
2733
2734 RETURN FALSE;
2735
2736 END IF;
2737
2738 END;
2739
2740 --this API is used as a workflow process post-notification function.
2741 --if the submitter has chosen to be notified in case of conflicts then the
2742 --user must navigate to the view conflicts page from the notification and
2743 --take action on conflicts before closing the notification.
2744 --this API will return an error as p_result if the user has not taken action
2745 --on the conflicts and the user will not be able to close the notification.
2746 PROCEDURE check_action_on_conflicts
2747 (p_item_type IN VARCHAR2,
2748 p_item_key IN VARCHAR2,
2749 p_actid IN NUMBER,
2750 p_funcmode IN VARCHAR2,
2751 p_result OUT NOCOPY VARCHAR2) -- 4537865
2752 IS
2753
2754 l_conflict_group_id NUMBER;
2755 l_action_taken VARCHAR2(1);
2756 l_return_status VARCHAR2(1);
2757 l_msg_count NUMBER;
2758 l_msg_data VARCHAR2(2000);
2759
2760 BEGIN
2761
2762 IF p_funcmode = 'RESPOND' THEN
2763
2764 l_conflict_group_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2765 itemkey => p_item_key,
2766 aname => 'CONFLICT_GROUP_ID');
2767
2768 PA_SCHEDULE_PVT.Has_Action_Taken_On_Conflicts(p_conflict_group_id => l_conflict_group_id,
2769 x_action_taken => l_action_taken,
2770 x_return_status => l_return_status,
2771 x_msg_count => l_msg_count,
2772 x_msg_data => l_msg_data);
2773
2774
2775 IF l_action_taken = 'N' THEN
2776
2777 -- p_result := 'ERROR:PA_NO_ACTION_ON_CONFLICTS';
2778 -- Bug 2134157 - show the message text and not the name. WF does
2779 -- not do the apps error message translation from name to text.
2780 p_result := 'ERROR:' ||
2781 fnd_message.get_string('PA','PA_NO_ACTION_ON_CONFLICTS');
2782
2783 END IF;
2784
2785 END IF;
2786
2787 -- 4537865 : Included EXCEPTION BLOCK
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 -- p_result := 'NONE' ; No need for populating this value - Verified with the WF Function
2791 -- Included as per discussion with Rajnish : 4537865
2792 Wf_Core.Context('pa_mass_asgmt_trx','check_action_on_conflicts',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2793 RAISE ;
2794 END check_action_on_conflicts;
2795
2796 --this API is called in case response required workflow notifications time out.
2797 --the mass_wf_in_progress_flag is set to 'N' for all assignments in this transaction.
2798 PROCEDURE Cancel_Mass_Trx_WF
2799 (p_item_type IN VARCHAR2,
2800 p_item_key IN VARCHAR2,
2801 p_actid IN NUMBER,
2802 p_funcmode IN VARCHAR2,
2803 p_result OUT NOCOPY VARCHAR2) -- 4537865
2804 IS
2805
2806 l_mode VARCHAR2(30);
2807
2808 BEGIN
2809
2810 l_mode := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2811 itemkey => p_item_key,
2812 aname => 'MODE');
2813
2814
2815 UPDATE pa_project_assignments
2816 SET mass_wf_in_progress_flag = 'N'
2817 WHERE assignment_id IN
2818 (SELECT decode(l_mode, G_MASS_ASGMT, object_id2, object_id1)
2819 FROM pa_wf_process_details
2820 WHERE item_type = p_item_type
2821 AND item_key = p_item_key);
2822
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 --4537865 : Start
2826 -- I havent reset value of p_result as this param is not assigned value anywhere in this API.
2827 -- The Workflow function (Cancel Mass Transaction) doesnt expect any result type
2828
2829 -- Included as per discussion with Rajnish : 4537865
2830 Wf_Core.Context('pa_mass_asgmt_trx','Cancel_Mass_Trx_WF',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2831 -- 4537865 : End
2832 RAISE;
2833
2834 END;
2835
2836 --if there is an unexpected error then the sysadmin can choose to abort or retry
2837 --the unprocessed items. If he chooses to abort then this API is called in
2838 --order to set pa_wf_process_details.process_status_code to 'A'.
2839
2840 PROCEDURE Abort_Remaining_Trx
2841 (p_item_type IN VARCHAR2,
2842 p_item_key IN VARCHAR2,
2843 p_actid IN NUMBER,
2844 p_funcmode IN VARCHAR2,
2845 p_result OUT NOCOPY VARCHAR2) --4537865
2846
2847 IS
2848
2849 l_mode VARCHAR2(30);
2850 l_error_item_type VARCHAR2(30);
2851 l_error_item_key NUMBER;
2852 l_submitter_user_id NUMBER;
2853 l_assignment_id pa_project_assignments.assignment_id%TYPE;
2854 l_resource_id pa_resources_denorm.resource_id%TYPE;
2855 l_return_status VARCHAR2(1);
2856 l_project_id pa_projects_all.project_id%TYPE;
2857 TYPE object_id_tbl IS TABLE OF NUMBER
2858 INDEX BY BINARY_INTEGER;
2859 l_object_id1_tbl object_id_tbl;
2860
2861
2862 BEGIN
2863
2864 --aborting a wf happens when there is an unexpected error and
2865 --the sysadmin chooses to abort the workflow.
2866 --but we commit 1 at a time, so some of the transactions
2867 --may be complete - so we are only aborting the ones that are not yet processed.
2868
2869 --get the following wf attributes for the process that errored out.
2870 l_error_item_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2871 itemkey => p_item_key,
2872 aname => 'ERROR_ITEM_TYPE');
2873
2874 l_error_item_key := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2875 itemkey => p_item_key,
2876 aname => 'ERROR_ITEM_KEY');
2877
2878 l_mode := WF_ENGINE.GetItemAttrText(itemtype => l_error_item_type,
2879 itemkey => l_error_item_key,
2880 aname => 'MODE');
2881
2882 l_submitter_user_id := WF_ENGINE.GetItemAttrText(itemtype => l_error_item_type,
2883 itemkey => l_error_item_key,
2884 aname => 'SUBMITTER_USER_ID');
2885
2886 l_project_id := WF_ENGINE.GetItemAttrText(itemtype => l_error_item_type,
2887 itemkey => l_error_item_key,
2888 aname => 'PROJECT_ID');
2889
2890 --set process_status_code to 'A' for the pending items.
2891 UPDATE pa_wf_process_details
2892 SET process_status_code = 'A' --'A' for aborted
2893 WHERE item_type = l_error_item_type
2894 AND item_key = l_error_item_key
2895 AND process_status_code = 'P'; --'P' for pending
2896
2897 --set mass_wf_in_progress_flag='N' for the aborted items.
2898 UPDATE pa_project_assignments
2899 SET mass_wf_in_progress_flag = 'N'
2900 WHERE assignment_id IN
2901 (SELECT decode(l_mode, G_MASS_ASGMT, object_id2, object_id1)
2902 FROM pa_wf_process_details
2903 WHERE item_type = l_error_item_type
2904 AND item_key = l_error_item_key
2905 AND process_status_code = 'A');
2906
2907 --get the object id (will be either resource id or assignment id)
2908 --for the aborted transactions.
2909 SELECT object_id1 BULK COLLECT INTO l_object_id1_tbl
2910 FROM pa_wf_process_details
2911 WHERE item_type = l_error_item_type
2912 AND item_key = l_error_item_key
2913 AND process_status_code = 'A';
2914
2915 --if there are any aborted items then add a message to the stack
2916 --saying that those items were aborted by the sysadmin.
2917 IF l_object_id1_tbl.COUNT > 0 THEN
2918
2919 FOR i IN l_object_id1_tbl.FIRST .. l_object_id1_tbl.LAST LOOP
2920
2921 FND_MSG_PUB.initialize;
2922
2923 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2924 ,p_msg_name => 'PA_PROCESS_ABORTED_SYSADMIN');
2925
2926 IF l_mode = G_MASS_ASGMT THEN
2927 l_resource_id := l_object_id1_tbl(i);
2928 l_assignment_id := NULL;
2929 ELSE
2930 l_resource_id := NULL;
2931 l_assignment_id := l_object_id1_tbl(i);
2932 END IF;
2933
2934 PA_MESSAGE_UTILS.save_messages(p_user_id => l_submitter_user_id,
2935 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
2936 p_source_type2 => l_mode,
2937 p_source_identifier1 => l_error_item_type,
2938 p_source_identifier2 => l_error_item_key,
2939 p_context1 => l_project_id,
2940 p_context2 => l_assignment_id,
2941 p_context3 => l_resource_id,
2942 p_commit => FND_API.G_FALSE,
2943 x_return_status => l_return_status);
2944
2945 END LOOP;
2946
2947 END IF;
2948
2949
2950 EXCEPTION
2951 WHEN OTHERS THEN
2952 --4537865 : Start
2953 -- I havent reset value of p_result as this param is not assigned value anywhere in this API.
2954 -- The Workflow function (Abort Remaining Transactions) doesnt expect any result type
2955
2956 -- Included as per discussion with Rajnish : 4537865
2957 Wf_Core.Context('pa_mass_asgmt_trx','Abort_Remaining_Trx',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
2958 -- 4537865 : End
2959
2960 RAISE;
2961
2962 END;
2963
2964 PROCEDURE Set_Submitter_User_Name
2965 (p_item_type IN VARCHAR2,
2966 p_item_key IN VARCHAR2,
2967 p_actid IN NUMBER,
2968 p_funcmode IN VARCHAR2,
2969 p_result OUT NOCOPY VARCHAR2) --4537865
2970 IS
2971
2972 l_error_item_type VARCHAR2(30);
2973 l_error_item_key NUMBER;
2974 l_submitter_user_name wf_users.name%TYPE; --VARCHAR2(240); Changed for bug 3267790
2975
2976 BEGIN
2977
2978
2979 --get the following wf attributes for the process that errored out.
2980 l_error_item_type := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2981 itemkey => p_item_key,
2982 aname => 'ERROR_ITEM_TYPE');
2983
2984 l_error_item_key := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
2985 itemkey => p_item_key,
2986 aname => 'ERROR_ITEM_KEY');
2987
2988 l_submitter_user_name := WF_ENGINE.GetItemAttrText(itemtype => l_error_item_type,
2989 itemkey => l_error_item_key,
2990 aname => 'SUBMITTER_USER_NAME');
2991
2992 --SET the Text item attributes (these attributes were created at design time)
2993 WF_ENGINE.SetItemAttrText(itemtype => p_item_type,
2994 itemkey => p_item_key,
2995 aname => 'SUBMITTER_USER_NAME',
2996 avalue => l_submitter_user_name);
2997 --4537865 :Included Exception block
2998 EXCEPTION
2999 WHEN OTHERS THEN
3000 -- I havent reset value of p_result as this param is not assigned value anywhere in this API.
3001 -- The Workflow function (Set Submitter User Name) doesnt expect any result type
3002
3003 -- Included as per discussion with Rajnish : 4537865
3004 Wf_Core.Context('pa_mass_asgmt_trx','Set_Submitter_User_Name',p_item_type,p_item_key,to_char(p_actid),p_funcmode);
3005 RAISE;
3006
3007 END;
3008
3009
3010 PROCEDURE Display_Updated_Attributes(document_id IN VARCHAR2,
3011 display_type IN VARCHAR2,
3012 document IN OUT NOCOPY VARCHAR2, --4537865
3013 document_type IN OUT NOCOPY VARCHAR2) --4537865
3014
3015 IS
3016
3017 --4537865
3018 l_original_document_type VARCHAR2(200);
3019 BEGIN
3020
3021 --this API will be called by workflow in order to show the Updated Information section
3022 --of the workflow notification. The document_id passed to the API was created dynamically
3023 --during the mass_asgmt_autonomous_trx API and then set as the value of the
3024 --UPDATED_INFORMATION_DOCUMENT workflow attribute.
3025 --See the mass_asgmt_autonomous_trx API above for details.
3026 --a Document wf type is used instead of plain text because plain text has a limit of 4K while
3027 --Document has a limit of 32K.
3028
3029 l_original_document_type := document_type ;
3030
3031 --set the document out parameter = to the document_id IN parameter.
3032 document := document_id;
3033
3034 --4537865
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037
3038 document := 'An Unexpected Error has occured - ' || SUBSTRB(SQLERRM,1,240) ;
3039 document_type := l_original_document_type ;
3040
3041 WF_CORE.CONTEXT('pa_mass_asgmt_trx','Display_Updated_Attributes' );
3042 RAISE ;
3043 END;
3044
3045 FUNCTION get_translated_attr_name (p_lookup_code IN VARCHAR2)
3046 RETURN VARCHAR2
3047 IS
3048
3049 l_meaning VARCHAR2(2000);
3050
3051 BEGIN
3052
3053 --get the translated updated attribute name from lookups.
3054 --this will be displayed in the Updated Information section of the mass update
3055 --workflow notification.
3056 SELECT meaning INTO l_meaning
3057 FROM pa_lookups
3058 WHERE lookup_type = 'MASS_ASSIGNMENT_UPDATE_ATTR'
3059 AND lookup_code = p_lookup_code;
3060
3061 RETURN l_meaning;
3062
3063 EXCEPTION
3064 WHEN NO_DATA_FOUND THEN
3065 RETURN p_lookup_code;
3066 WHEN OTHERS THEN
3067 RAISE;
3068
3069 END;
3070
3071
3072 END;
3073
[Home] [Help]