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