[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY pa_project_assignments_pkg AS
2 /*$Header: PARAPKGB.pls 120.1 2005/08/19 16:47:19 mwasowic noship $*/
3 --
4
5 PROCEDURE Insert_Row
6 ( p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
7 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
8 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
9 ,p_apprvl_status_code IN pa_project_assignments.apprvl_status_code%TYPE := FND_API.G_MISS_CHAR
10 ,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
11 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
12 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
13 ,p_assignment_template_id IN pa_project_assignments.assignment_template_id%TYPE := FND_API.G_MISS_NUM
14 ,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
15 ,p_resource_id IN pa_project_assignments.resource_id%TYPE := FND_API.G_MISS_NUM
16 ,p_project_party_id IN pa_project_assignments.project_party_id%TYPE := FND_API.G_MISS_NUM
17 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
18 ,p_note_to_approver IN pa_project_assignments.note_to_approver%TYPE := FND_API.G_MISS_CHAR
19 ,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
20 ,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
21 ,p_assignment_effort IN pa_project_assignments.assignment_effort%TYPE := FND_API.G_MISS_NUM
22 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
23 ,p_source_assignment_id IN pa_project_assignments.source_assignment_id%TYPE := FND_API.G_MISS_NUM
24 ,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
25 ,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
26 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
27 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
28 ,p_revenue_currency_code IN pa_project_assignments.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
29 ,p_revenue_bill_rate IN pa_project_assignments.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
30 ,p_markup_percent IN pa_project_assignments.markup_percent%TYPE := FND_API.G_MISS_NUM
31 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
32 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
33 ,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
34 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
35 ,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
36 ,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
37 ,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
38 ,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
39 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
40 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
41 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
42 ,p_calendar_type IN pa_project_assignments.calendar_type%TYPE := FND_API.G_MISS_CHAR
43 ,p_calendar_id IN pa_project_assignments.calendar_id%TYPE := FND_API.G_MISS_NUM
44 ,p_resource_calendar_percent IN pa_project_assignments.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
45 ,p_no_of_active_candidates IN pa_project_assignments.no_of_active_candidates%TYPE := FND_API.G_MISS_NUM
46 ,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
47 ,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
48 ,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
49 ,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
50 ,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
51 ,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
52 ,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
53 ,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
54 ,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
55 ,p_bill_rate_override IN pa_project_assignments.bill_rate_override%TYPE := FND_API.G_MISS_NUM
56 ,p_bill_rate_curr_override IN pa_project_assignments.bill_rate_curr_override%TYPE := FND_API.G_MISS_CHAR
57 ,p_markup_percent_override IN pa_project_assignments.markup_percent_override%TYPE := FND_API.G_MISS_NUM
58 ,p_discount_percentage IN pa_project_assignments.discount_percentage%TYPE := FND_API.G_MISS_NUM -- FP.L Development
59 ,p_rate_disc_reason_code IN pa_project_assignments.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR -- FP.L Development
60 ,p_tp_rate_override IN pa_project_assignments.tp_rate_override%TYPE := FND_API.G_MISS_NUM
61 ,p_tp_currency_override IN pa_project_assignments.tp_currency_override%TYPE := FND_API.G_MISS_CHAR
62 ,p_tp_calc_base_code_override IN pa_project_assignments.tp_calc_base_code_override%TYPE := FND_API.G_MISS_CHAR
63 ,p_tp_percent_applied_override IN pa_project_assignments.tp_percent_applied_override%TYPE := FND_API.G_MISS_NUM
64 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM -- FP.L Development
65 ,p_resource_list_member_id IN pa_project_assignments.resource_list_member_id%TYPE := FND_API.G_MISS_NUM -- FP.M Development
66 ,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
67 ,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
68 ,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
69 ,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
70 ,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
71 ,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
72 ,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
73 ,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
74 ,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
75 ,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
76 ,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
77 ,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
78 ,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
79 ,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
80 ,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
81 ,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
82 /* Added 2 columns for 3051110 */
83 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE := FND_API.G_MISS_NUM
84 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE := FND_API.G_MISS_CHAR
85 ,p_number_of_requirements IN NUMBER := 1
86 ,x_assignment_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
87 ,x_new_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
88 ,x_assignment_number OUT NOCOPY pa_project_assignments.assignment_number%TYPE --File.Sql.39 bug 4440895
89 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
90 )
91 IS
92
93 TYPE assignment_id IS TABLE OF pa_project_assignments.assignment_id%TYPE
94 INDEX BY BINARY_INTEGER;
95 l_assignment_id assignment_id;
96 TYPE assignment_name IS TABLE OF pa_project_assignments.assignment_name%TYPE
97 INDEX BY BINARY_INTEGER;
98 l_assignment_name assignment_name;
99 TYPE assignment_number IS TABLE OF pa_project_assignments.assignment_number%TYPE
100 INDEX BY BINARY_INTEGER;
101 l_assignment_number assignment_number;
102 TYPE assignment_type IS TABLE OF pa_project_assignments.assignment_type%TYPE
103 INDEX BY BINARY_INTEGER;
104 l_assignment_type assignment_type;
105 TYPE multiple_status_flag IS TABLE OF pa_project_assignments.multiple_status_flag%TYPE
106 INDEX BY BINARY_INTEGER;
107 l_multiple_status_flag multiple_status_flag;
108 TYPE apprvl_status_code IS TABLE OF pa_project_assignments.apprvl_status_code%TYPE
109 INDEX BY BINARY_INTEGER;
110 l_apprvl_status_code apprvl_status_code;
111 TYPE status_code IS TABLE OF pa_project_assignments.status_code%TYPE
112 INDEX BY BINARY_INTEGER;
113 l_status_code status_code;
114 TYPE staffing_priority_code IS TABLE OF pa_project_assignments.staffing_priority_code%TYPE
115 INDEX BY BINARY_INTEGER;
116 l_staffing_priority_code staffing_priority_code;
117 TYPE project_id IS TABLE OF pa_project_assignments.project_id%TYPE
118 INDEX BY BINARY_INTEGER;
119 l_project_id project_id;
120 TYPE assignment_template_id IS TABLE OF pa_project_assignments.assignment_template_id%TYPE
121 INDEX BY BINARY_INTEGER;
122 l_assignment_template_id assignment_template_id;
123 TYPE project_role_id IS TABLE OF pa_project_assignments.project_role_id%TYPE
124 INDEX BY BINARY_INTEGER;
125 l_project_role_id project_role_id;
126 TYPE resource_id IS TABLE OF pa_project_assignments.resource_id%TYPE
127 INDEX BY BINARY_INTEGER;
128 l_resource_id resource_id;
129 TYPE project_party_id IS TABLE OF pa_project_assignments.project_party_id%TYPE
130 INDEX BY BINARY_INTEGER;
131 l_project_party_id project_party_id;
132 TYPE description IS TABLE OF pa_project_assignments.description%TYPE
133 INDEX BY BINARY_INTEGER;
134 l_description description;
135 TYPE note_to_approver IS TABLE OF pa_project_assignments.note_to_approver%TYPE
136 INDEX BY BINARY_INTEGER;
137 l_note_to_approver note_to_approver;
138 TYPE start_date IS TABLE OF pa_project_assignments.start_date%TYPE
139 INDEX BY BINARY_INTEGER;
140 l_start_date start_date;
141 TYPE end_date IS TABLE OF pa_project_assignments.end_date%TYPE
142 INDEX BY BINARY_INTEGER;
143 l_end_date end_date;
144 TYPE assignment_effort IS TABLE OF pa_project_assignments.assignment_effort%TYPE
145 INDEX BY BINARY_INTEGER;
146 l_assignment_effort assignment_effort;
147 TYPE extension_possible IS TABLE OF pa_project_assignments.extension_possible%TYPE
148 INDEX BY BINARY_INTEGER;
149 l_extension_possible extension_possible;
150 TYPE source_assignment_id IS TABLE OF pa_project_assignments.source_assignment_id%TYPE
151 INDEX BY BINARY_INTEGER;
152 l_source_assignment_id source_assignment_id;
153 TYPE min_resource_job_level IS TABLE OF pa_project_assignments.min_resource_job_level%TYPE
154 INDEX BY BINARY_INTEGER;
155 l_min_resource_job_level min_resource_job_level;
156 TYPE max_resource_job_level IS TABLE OF pa_project_assignments.max_resource_job_level%TYPE
157 INDEX BY BINARY_INTEGER;
158 l_max_resource_job_level max_resource_job_level;
159 TYPE additional_information IS TABLE OF pa_project_assignments.additional_information%TYPE
160 INDEX BY BINARY_INTEGER;
161 l_additional_information additional_information;
162 TYPE work_type_id IS TABLE OF pa_project_assignments.work_type_id%TYPE
163 INDEX BY BINARY_INTEGER;
164 l_work_type_id work_type_id;
165 TYPE revenue_currency_code IS TABLE OF pa_project_assignments.revenue_currency_code%TYPE
166 INDEX BY BINARY_INTEGER;
167 l_revenue_currency_code revenue_currency_code;
168 TYPE revenue_bill_rate IS TABLE OF pa_project_assignments.revenue_bill_rate%TYPE
169 INDEX BY BINARY_INTEGER;
170 l_revenue_bill_rate revenue_bill_rate;
171
172 TYPE markup_percent IS TABLE OF pa_project_assignments.markup_percent%TYPE
173 INDEX BY BINARY_INTEGER;
174 l_markup_percent markup_percent;
175
176 TYPE expense_owner IS TABLE OF pa_project_assignments.expense_owner%TYPE
177 INDEX BY BINARY_INTEGER;
178 l_expense_owner expense_owner;
179 TYPE expense_limit IS TABLE OF pa_project_assignments.expense_limit%TYPE
180 INDEX BY BINARY_INTEGER;
181 l_expense_limit expense_limit;
182 TYPE expense_limit_currency_code IS TABLE OF pa_project_assignments.expense_limit_currency_code%TYPE
183 INDEX BY BINARY_INTEGER;
184 l_expense_limit_currency_code expense_limit_currency_code;
185 TYPE fcst_tp_amount_type IS TABLE OF pa_project_assignments.fcst_tp_amount_type%TYPE
186 INDEX BY BINARY_INTEGER;
187 l_fcst_tp_amount_type fcst_tp_amount_type;
188 TYPE fcst_job_id IS TABLE OF pa_project_assignments.fcst_job_id%TYPE
189 INDEX BY BINARY_INTEGER;
190 l_fcst_job_id fcst_job_id;
191 TYPE fcst_job_group_id IS TABLE OF pa_project_assignments.fcst_job_group_id%TYPE
192 INDEX BY BINARY_INTEGER;
193 l_fcst_job_group_id fcst_job_group_id;
194 TYPE expenditure_org_id IS TABLE OF pa_project_assignments.expenditure_org_id%TYPE
195 INDEX BY BINARY_INTEGER;
196 l_expenditure_org_id expenditure_org_id;
197 TYPE expenditure_organization_id IS TABLE OF pa_project_assignments.expenditure_organization_id%TYPE
198 INDEX BY BINARY_INTEGER;
199 l_expenditure_organization_id expenditure_organization_id;
200 TYPE expenditure_type_class IS TABLE OF pa_project_assignments.expenditure_type_class%TYPE
201 INDEX BY BINARY_INTEGER;
202 l_expenditure_type_class expenditure_type_class;
203 TYPE expenditure_type IS TABLE OF pa_project_assignments.expenditure_type%TYPE
204 INDEX BY BINARY_INTEGER;
205 l_expenditure_type expenditure_type;
206 TYPE location_id IS TABLE OF pa_project_assignments.location_id%TYPE
207 INDEX BY BINARY_INTEGER;
208 l_location_id location_id;
209 TYPE calendar_type IS TABLE OF pa_project_assignments.calendar_type%TYPE
210 INDEX BY BINARY_INTEGER;
211 l_calendar_type calendar_type;
212 TYPE calendar_id IS TABLE OF pa_project_assignments.calendar_id%TYPE
213 INDEX BY BINARY_INTEGER;
214 l_calendar_id calendar_id;
215 TYPE resource_calendar_percent IS TABLE OF pa_project_assignments.resource_calendar_percent%TYPE
216 INDEX BY BINARY_INTEGER;
217 l_resource_calendar_percent resource_calendar_percent;
218 TYPE no_of_active_candidates IS TABLE OF pa_project_assignments.no_of_active_candidates%TYPE
219 INDEX BY BINARY_INTEGER;
220 l_no_of_active_candidates no_of_active_candidates;
221
222 TYPE comp_match_weighting IS TABLE OF pa_project_assignments.competence_match_weighting%TYPE
223 INDEX BY BINARY_INTEGER;
224 l_comp_match_weighting comp_match_weighting;
225
226 TYPE avail_match_weighting IS TABLE OF pa_project_assignments.availability_match_weighting%TYPE
227 INDEX BY BINARY_INTEGER;
228 l_avail_match_weighting avail_match_weighting;
229
230 TYPE job_level_match_weighting IS TABLE OF pa_project_assignments.job_level_match_weighting%TYPE
231 INDEX BY BINARY_INTEGER;
232 l_job_level_match_weighting job_level_match_weighting;
233
234 TYPE search_min_availability IS TABLE OF pa_project_assignments.search_min_availability%TYPE
235 INDEX BY BINARY_INTEGER;
236 l_search_min_availability search_min_availability;
237
238 TYPE search_country_code IS TABLE OF pa_project_assignments.search_country_code%TYPE
239 INDEX BY BINARY_INTEGER;
240 l_search_country_code search_country_code;
241
242 TYPE search_exp_org_struct_ver_id IS TABLE OF pa_project_assignments.search_exp_org_struct_ver_id%TYPE
243 INDEX BY BINARY_INTEGER;
244 l_search_exp_org_struct_ver_id search_exp_org_struct_ver_id;
245
246 TYPE search_exp_start_org_id IS TABLE OF pa_project_assignments.search_exp_start_org_id%TYPE
247 INDEX BY BINARY_INTEGER;
248 l_search_exp_start_org_id search_exp_start_org_id;
249
250 TYPE search_min_candidate_score IS TABLE OF pa_project_assignments.search_min_candidate_score%TYPE
251 INDEX BY BINARY_INTEGER;
252 l_search_min_candidate_score search_min_candidate_score;
253
254 TYPE enable_auto_cand_nom_flag IS TABLE OF pa_project_assignments.enable_auto_cand_nom_flag%TYPE
255 INDEX BY BINARY_INTEGER;
256 l_enable_auto_cand_nom_flag enable_auto_cand_nom_flag;
257
258
259 TYPE bill_rate_override IS TABLE OF pa_project_assignments.bill_rate_override%TYPE
260 INDEX BY BINARY_INTEGER;
261 l_bill_rate_override bill_rate_override;
262
263
264 TYPE bill_rate_curr_override IS TABLE OF pa_project_assignments.bill_rate_curr_override%TYPE
265 INDEX BY BINARY_INTEGER;
266 l_bill_rate_curr_override bill_rate_curr_override;
267
268 TYPE markup_percent_override IS TABLE OF pa_project_assignments.markup_percent_override%TYPE
269 INDEX BY BINARY_INTEGER;
270 l_markup_percent_override markup_percent_override;
271
272 -- FP.L Development
273 TYPE discount_percentage IS TABLE OF pa_project_assignments.discount_percentage%TYPE
274 INDEX BY BINARY_INTEGER;
275 l_discount_percentage discount_percentage;
276
277 -- FP.L Development
278 TYPE rate_disc_reason_code IS TABLE OF pa_project_assignments.rate_disc_reason_code%TYPE
279 INDEX BY BINARY_INTEGER;
280 l_rate_disc_reason_code rate_disc_reason_code;
281
282 TYPE tp_rate_override IS TABLE OF pa_project_assignments.tp_rate_override%TYPE
283 INDEX BY BINARY_INTEGER;
284 l_tp_rate_override tp_rate_override;
285
286 TYPE tp_currency_override IS TABLE OF pa_project_assignments.tp_currency_override%TYPE
287 INDEX BY BINARY_INTEGER;
288 l_tp_currency_override tp_currency_override;
289
290 TYPE tp_calc_base_code_override IS TABLE OF pa_project_assignments.tp_calc_base_code_override%TYPE
291 INDEX BY BINARY_INTEGER;
292 l_tp_calc_base_code_override tp_calc_base_code_override;
293
294 TYPE tp_percent_applied_override IS TABLE OF pa_project_assignments.tp_percent_applied_override%TYPE
295 INDEX BY BINARY_INTEGER;
296 l_tp_percent_applied_override tp_percent_applied_override;
297
298 -- FP.L Development
299 TYPE staffing_owner_person_id IS TABLE OF pa_project_assignments.staffing_owner_person_id%TYPE
300 INDEX BY BINARY_INTEGER;
301 l_staffing_owner_person_id staffing_owner_person_id;
302
303 -- FP.M Development
304 TYPE resource_list_member_id IS TABLE OF pa_project_assignments.resource_list_member_id%TYPE
305 INDEX BY BINARY_INTEGER;
306 l_resource_list_member_id resource_list_member_id;
307
308 TYPE attribute_category IS TABLE OF pa_project_assignments.attribute_category%TYPE
309 INDEX BY BINARY_INTEGER;
310 l_attribute_category attribute_category;
311 TYPE attribute1 IS TABLE OF pa_project_assignments.attribute1%TYPE
312 INDEX BY BINARY_INTEGER;
313 l_attribute1 attribute1;
314 TYPE attribute2 IS TABLE OF pa_project_assignments.attribute2%TYPE
315 INDEX BY BINARY_INTEGER;
316 l_attribute2 attribute2;
317 TYPE attribute3 IS TABLE OF pa_project_assignments.attribute3%TYPE
318 INDEX BY BINARY_INTEGER;
319 l_attribute3 attribute3;
320 TYPE attribute4 IS TABLE OF pa_project_assignments.attribute4%TYPE
321 INDEX BY BINARY_INTEGER;
322 l_attribute4 attribute4;
323 TYPE attribute5 IS TABLE OF pa_project_assignments.attribute5%TYPE
324 INDEX BY BINARY_INTEGER;
325 l_attribute5 attribute5;
326 TYPE attribute6 IS TABLE OF pa_project_assignments.attribute6%TYPE
327 INDEX BY BINARY_INTEGER;
328 l_attribute6 attribute6;
329 TYPE attribute7 IS TABLE OF pa_project_assignments.attribute7%TYPE
330 INDEX BY BINARY_INTEGER;
331 l_attribute7 attribute7;
332 TYPE attribute8 IS TABLE OF pa_project_assignments.attribute8%TYPE
333 INDEX BY BINARY_INTEGER;
334 l_attribute8 attribute8;
335 TYPE attribute9 IS TABLE OF pa_project_assignments.attribute9%TYPE
336 INDEX BY BINARY_INTEGER;
337 l_attribute9 attribute9;
338 TYPE attribute10 IS TABLE OF pa_project_assignments.attribute10%TYPE
339 INDEX BY BINARY_INTEGER;
340 l_attribute10 attribute10;
341 TYPE attribute11 IS TABLE OF pa_project_assignments.attribute11%TYPE
342 INDEX BY BINARY_INTEGER;
343 l_attribute11 attribute11;
344 TYPE attribute12 IS TABLE OF pa_project_assignments.attribute12%TYPE
345 INDEX BY BINARY_INTEGER;
346 l_attribute12 attribute12;
347 TYPE attribute13 IS TABLE OF pa_project_assignments.attribute13%TYPE
348 INDEX BY BINARY_INTEGER;
349 l_attribute13 attribute13;
350 TYPE attribute14 IS TABLE OF pa_project_assignments.attribute14%TYPE
351 INDEX BY BINARY_INTEGER;
352 l_attribute14 attribute14;
353 TYPE attribute15 IS TABLE OF pa_project_assignments.attribute15%TYPE
354 INDEX BY BINARY_INTEGER;
355 l_attribute15 attribute15;
356 /* Added for bug 3051110 */
357 TYPE transfer_price_rate IS TABLE OF pa_project_assignments.transfer_price_rate%TYPE
358 INDEX BY BINARY_INTEGER;
359 l_transfer_price_rate transfer_price_rate;
360 TYPE transfer_pr_rate_curr IS TABLE OF pa_project_assignments.transfer_pr_rate_curr%TYPE
361 INDEX BY BINARY_INTEGER;
362 l_transfer_pr_rate_curr transfer_pr_rate_curr;
363 TYPE rowid_table IS TABLE OF ROWID
364 INDEX BY BINARY_INTEGER;
365 l_rowid rowid_table;
366
367 l_index NUMBER;
368 l_workflow_in_progress_flag pa_team_templates.workflow_in_progress_flag%TYPE;
369
370 CURSOR check_team_template_wf IS
371 SELECT workflow_in_progress_flag
372 FROM pa_team_templates
373 WHERE team_template_id = p_assignment_template_id;
374
375 BEGIN
376
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.DELETE;
380
381 --if this is a template requirement then check that worflow is not in progress
382 --on the parent team template. If it is in progress then no new template requirements
383 --can be created
384 IF (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
385 (p_assignment_template_id IS NOT NULL and p_assignment_template_id <>FND_API.G_MISS_NUM) THEN
386 OPEN check_team_template_wf;
387 FETCH check_team_template_wf INTO l_workflow_in_progress_flag;
388 CLOSE check_team_template_wf;
389
390 IF l_workflow_in_progress_flag='Y' THEN
391 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
392 ,p_msg_name => 'PA_NO_REQ_WF');
393 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 RETURN;
396 END IF;
397 END IF;
398
399 FOR l_index IN 1 .. p_number_of_requirements LOOP
400
401 l_assignment_name(l_index) := p_assignment_name;
402 l_assignment_type(l_index) := p_assignment_type;
403 l_multiple_status_flag(l_index) := p_multiple_status_flag;
404 l_apprvl_status_code(l_index) := p_apprvl_status_code;
405 l_status_code(l_index) := p_status_code;
406 l_staffing_priority_code(l_index) := p_staffing_priority_code;
407 l_project_id(l_index) := p_project_id;
408 l_assignment_template_id(l_index) := p_assignment_template_id;
409 l_project_role_id(l_index) := p_project_role_id;
410 l_resource_id(l_index) := p_resource_id;
411 l_project_party_id(l_index) := p_project_party_id;
412 l_description(l_index) := p_description;
413 l_note_to_approver(l_index) := p_note_to_approver;
414 l_start_date(l_index) := p_start_date;
415 l_end_date(l_index) := p_end_date;
416 l_assignment_effort(l_index) := p_assignment_effort;
417 l_extension_possible(l_index) := p_extension_possible;
418 l_source_assignment_id(l_index) := p_source_assignment_id;
419 l_min_resource_job_level(l_index) := p_min_resource_job_level;
420 l_max_resource_job_level(l_index) := p_max_resource_job_level;
421 l_additional_information(l_index) := p_additional_information;
422 l_work_type_id(l_index) := p_work_type_id;
423 l_revenue_currency_code(l_index) := p_revenue_currency_code;
424 l_revenue_bill_rate(l_index) := p_revenue_bill_rate;
425 l_markup_percent(l_index) := p_markup_percent;
426 l_expense_owner(l_index) := p_expense_owner;
427 l_expense_limit(l_index) := p_expense_limit;
428 l_expense_limit_currency_code(l_index) := p_expense_limit_currency_code;
429 l_fcst_tp_amount_type(l_index) := p_fcst_tp_amount_type;
430 l_fcst_job_id(l_index) := p_fcst_job_id;
431 l_fcst_job_group_id(l_index) := p_fcst_job_group_id;
432 l_expenditure_org_id(l_index) := p_expenditure_org_id;
433 l_expenditure_organization_id(l_index) := p_expenditure_organization_id;
434 l_expenditure_type_class(l_index) := p_expenditure_type_class;
435 l_expenditure_type(l_index) := p_expenditure_type;
436 l_location_id(l_index) := p_location_id;
437 l_calendar_type(l_index) := p_calendar_type;
438 l_calendar_id(l_index) := p_calendar_id;
439 l_resource_calendar_percent(l_index):= p_resource_calendar_percent;
440 l_no_of_active_candidates(l_index) := p_no_of_active_candidates;
441 l_comp_match_weighting(l_index) := p_comp_match_weighting;
442 l_avail_match_weighting(l_index) := p_avail_match_weighting;
443 l_job_level_match_weighting(l_index):= p_job_level_match_weighting;
444 l_search_min_availability(l_index) := p_search_min_availability;
445 l_search_country_code(l_index) := p_search_country_code;
446 l_search_exp_org_struct_ver_id(l_index) := p_search_exp_org_struct_ver_id;
447 l_search_exp_start_org_id(l_index) := p_search_exp_start_org_id;
448 l_search_min_candidate_score(l_index) := p_search_min_candidate_score;
449 l_enable_auto_cand_nom_flag(l_index) := p_enable_auto_cand_nom_flag;
450 l_attribute_category(l_index) := p_attribute_category;
451 l_attribute1(l_index) := p_attribute1;
452 l_attribute2(l_index) := p_attribute2;
453 l_attribute3(l_index) := p_attribute3;
454 l_attribute4(l_index) := p_attribute4;
455 l_attribute5(l_index) := p_attribute5;
456 l_attribute6(l_index) := p_attribute6;
457 l_attribute7(l_index) := p_attribute7;
458 l_attribute8(l_index) := p_attribute8;
459 l_attribute9(l_index) := p_attribute9;
460 l_attribute10(l_index) := p_attribute10;
461 l_attribute11(l_index) := p_attribute11;
462 l_attribute12(l_index) := p_attribute12;
463 l_attribute13(l_index) := p_attribute13;
464 l_attribute14(l_index) := p_attribute14;
465 l_attribute15(l_index) := p_attribute15;
466
467 l_bill_rate_override(l_index) := p_bill_rate_override;
468 l_bill_rate_curr_override(l_index) := p_bill_rate_curr_override;
469 l_markup_percent_override(l_index) := p_markup_percent_override;
470 l_discount_percentage(l_index) := p_discount_percentage;
471 l_rate_disc_reason_code(l_index) := p_rate_disc_reason_code;
472 l_tp_rate_override(l_index) := p_tp_rate_override;
473 l_tp_currency_override(l_index) := p_tp_currency_override;
474 l_tp_calc_base_code_override(l_index) := p_tp_calc_base_code_override;
475 l_tp_percent_applied_override(l_index) := p_tp_percent_applied_override;
476 l_staffing_owner_person_id(l_index) := p_staffing_owner_person_id;
477 l_resource_list_member_id(l_index) := p_resource_list_member_id;
478 /* Added for bug 3051110 */
479 l_transfer_price_rate(l_index) := p_transfer_price_rate;
480 l_transfer_pr_rate_curr(l_index) := p_transfer_pr_rate_curr;
481
482 END LOOP;
483
484 FORALL i IN 1 .. p_number_of_requirements
485
486 INSERT INTO pa_project_assignments
487 (assignment_id,
488 assignment_name,
489 assignment_type,
490 multiple_status_flag,
491 record_version_number,
492 apprvl_status_code,
493 status_code,
494 staffing_priority_code,
495 project_id,
496 assignment_template_id,
497 template_flag,
498 project_role_id,
499 project_party_id,
500 description,
501 note_to_approver,
502 start_date,
503 end_date,
504 resource_id,
505 assignment_effort,
506 extension_possible,
507 source_assignment_id,
508 min_resource_job_level,
509 max_resource_job_level,
510 assignment_number,
511 additional_information,
512 work_type_id,
513 revenue_currency_code,
514 revenue_bill_rate,
515 markup_percent,
516 expense_owner,
517 expense_limit,
518 expense_limit_currency_code,
519 fcst_tp_amount_type,
520 fcst_job_id,
521 fcst_job_group_id,
522 expenditure_org_id,
523 expenditure_organization_id,
524 expenditure_type_class,
525 expenditure_type,
526 location_id,
527 calendar_type,
528 calendar_id,
529 resource_calendar_percent,
530 no_of_active_candidates,
531 competence_match_weighting,
532 availability_match_weighting,
533 job_level_match_weighting,
534 search_min_availability,
535 search_country_code,
536 search_exp_org_struct_ver_id,
537 search_exp_start_org_id,
538 search_min_candidate_score,
539 enable_auto_cand_nom_flag,
540 staffing_owner_person_id,
541 resource_list_member_id,
542 attribute_category,
543 attribute1,
544 attribute2,
545 attribute3,
546 attribute4,
547 attribute5,
548 attribute6,
549 attribute7,
550 attribute8,
551 attribute9,
552 attribute10,
553 attribute11,
554 attribute12,
555 attribute13,
556 attribute14,
557 attribute15,
558 bill_rate_override,
559 bill_rate_curr_override,
560 markup_percent_override,
561 discount_percentage,
562 rate_disc_reason_code,
563 tp_rate_override,
564 tp_currency_override,
565 tp_calc_base_code_override,
566 tp_percent_applied_override,
567 creation_date,
568 created_by,
569 last_update_date,
570 last_updated_by,
571 last_update_login,
572 transfer_price_rate, --For bug 3051110
573 transfer_pr_rate_curr)
574 VALUES
575 ( pa_project_assignments_s.NEXTVAL,
576 DECODE(l_assignment_name(i), FND_API.G_MISS_CHAR, NULL, l_assignment_name(i)),
577 DECODE(l_assignment_type(i), FND_API.G_MISS_CHAR, NULL, l_assignment_type(i)),
578 DECODE(l_multiple_status_flag(i), FND_API.G_MISS_CHAR, NULL, l_multiple_status_flag(i)),
579 1,
580 DECODE(l_apprvl_status_code(i), FND_API.G_MISS_CHAR, NULL, l_apprvl_status_code(i)),
581 DECODE(l_status_code(i), FND_API.G_MISS_CHAR, NULL, l_status_code(i)),
582 DECODE(l_staffing_priority_code(i), FND_API.G_MISS_CHAR, NULL, l_staffing_priority_code(i)),
583 DECODE(l_project_id(i), FND_API.G_MISS_NUM, NULL, l_project_id(i)),
584 DECODE(l_assignment_template_id(i), FND_API.G_MISS_NUM, NULL, l_assignment_template_id(i)),
585 DECODE(l_project_id(i), FND_API.G_MISS_NUM, 'Y', DECODE(l_project_id(i), NULL, 'Y', 'N')),
586 DECODE(l_project_role_id(i), FND_API.G_MISS_NUM, NULL, l_project_role_id(i)),
587 DECODE(l_project_party_id(i), FND_API.G_MISS_NUM, NULL, l_project_party_id(i)),
588 DECODE(l_description(i), FND_API.G_MISS_CHAR, NULL, l_description(i)),
589 DECODE(l_note_to_approver(i), FND_API.G_MISS_CHAR, NULL, l_note_to_approver(i)),
590 DECODE(l_start_date(i), FND_API.G_MISS_DATE, NULL, l_start_date(i)),
591 DECODE(l_end_date(i), FND_API.G_MISS_DATE, NULL, l_end_date(i)),
592 DECODE(l_resource_id(i), FND_API.G_MISS_NUM, NULL, l_resource_id(i)),
593 DECODE(l_assignment_effort(i), FND_API.G_MISS_NUM, NULL, l_assignment_effort(i)),
594 DECODE(l_extension_possible(i), FND_API.G_MISS_CHAR, NULL, l_extension_possible(i)),
595 DECODE(l_source_assignment_id(i), FND_API.G_MISS_NUM, NULL, l_source_assignment_id(i)),
596 DECODE(l_min_resource_job_level(i), FND_API.G_MISS_NUM, NULL, l_min_resource_job_level(i)),
597 DECODE(l_max_resource_job_level(i), FND_API.G_MISS_NUM, NULL, l_max_resource_job_level(i)),
598 --DECODE(l_assignment_type(i), 'OPEN_ASSIGNMENT', pa_assignment_number_s.NEXTVAL, NULL),
599 pa_assignment_number_s.NEXTVAL,
600 DECODE(l_additional_information(i), FND_API.G_MISS_CHAR, NULL, l_additional_information(i)),
601 DECODE(l_work_type_id(i), FND_API.G_MISS_NUM, NULL, l_work_type_id(i)),
602 DECODE(l_revenue_currency_code(i), FND_API.G_MISS_CHAR, NULL, l_revenue_currency_code(i)),
603 DECODE(l_revenue_bill_rate(i), FND_API.G_MISS_NUM, NULL, l_revenue_bill_rate(i)),
604 DECODE(l_markup_percent(i), FND_API.G_MISS_NUM, NULL, l_markup_percent(i)),
605 DECODE(l_expense_owner(i), FND_API.G_MISS_CHAR, NULL, l_expense_owner(i)),
606 DECODE(l_expense_limit(i), FND_API.G_MISS_NUM, NULL, l_expense_limit(i)),
607 DECODE(l_expense_limit_currency_code(i), FND_API.G_MISS_CHAR, NULL, l_expense_limit_currency_code(i)),
608 DECODE(l_fcst_tp_amount_type(i), FND_API.G_MISS_CHAR, NULL, l_fcst_tp_amount_type(i)),
609 DECODE(l_fcst_job_id(i), FND_API.G_MISS_NUM, NULL, l_fcst_job_id(i)),
610 DECODE(l_fcst_job_group_id(i), FND_API.G_MISS_NUM, NULL, l_fcst_job_group_id(i)),
611 DECODE(l_expenditure_org_id(i), FND_API.G_MISS_NUM, NULL,l_expenditure_org_id(i)),
612 DECODE(l_expenditure_organization_id(i), FND_API.G_MISS_NUM, NULL,l_expenditure_organization_id(i)),
613 DECODE(l_expenditure_type_class(i), FND_API.G_MISS_CHAR, NULL,l_expenditure_type_class(i)),
614 DECODE(l_expenditure_type(i), FND_API.G_MISS_CHAR, NULL,l_expenditure_type(i)),
615 DECODE(l_location_id(i), FND_API.G_MISS_NUM, NULL, l_location_id(i)),
616 DECODE(l_calendar_type(i), FND_API.G_MISS_CHAR, NULL, l_calendar_type(i)),
617 DECODE(l_calendar_id(i), FND_API.G_MISS_NUM, NULL, l_calendar_id(i)),
618 DECODE(l_resource_calendar_percent(i), FND_API.G_MISS_NUM, NULL, l_resource_calendar_percent(i)),
619 DECODE(l_no_of_active_candidates(i), FND_API.G_MISS_NUM, NULL, l_no_of_active_candidates(i)),
620 DECODE(l_comp_match_weighting(i), FND_API.G_MISS_NUM, NULL, l_comp_match_weighting(i)),
621 DECODE(l_avail_match_weighting(i), FND_API.G_MISS_NUM, NULL, l_avail_match_weighting(i)),
622 DECODE(l_job_level_match_weighting(i), FND_API.G_MISS_NUM, NULL, l_job_level_match_weighting(i)),
623 DECODE(l_search_min_availability(i), FND_API.G_MISS_NUM, NULL, l_search_min_availability(i)),
624 DECODE(l_search_country_code(i), FND_API.G_MISS_CHAR, NULL, l_search_country_code(i)),
625 DECODE(l_search_exp_org_struct_ver_id(i), FND_API.G_MISS_NUM, NULL, l_search_exp_org_struct_ver_id(i)),
626 DECODE(l_search_exp_start_org_id(i), FND_API.G_MISS_NUM, NULL, l_search_exp_start_org_id(i)),
627 DECODE(l_search_min_candidate_score(i), FND_API.G_MISS_NUM, NULL, l_search_min_candidate_score(i)),
628 DECODE(l_enable_auto_cand_nom_flag(i), FND_API.G_MISS_CHAR, NULL, l_enable_auto_cand_nom_flag(i)),
629 DECODE(l_staffing_owner_person_id(i), FND_API.G_MISS_NUM, NULL, l_staffing_owner_person_id(i)),
630 DECODE(l_resource_list_member_id(i), FND_API.G_MISS_NUM, NULL, l_resource_list_member_id(i)),
631 DECODE(l_attribute_category(i), FND_API.G_MISS_CHAR, NULL, l_attribute_category(i)),
632 DECODE(l_attribute1(i), FND_API.G_MISS_CHAR, NULL, l_attribute1(i)),
633 DECODE(l_attribute2(i), FND_API.G_MISS_CHAR, NULL, l_attribute2(i)),
634 DECODE(l_attribute3(i), FND_API.G_MISS_CHAR, NULL, l_attribute3(i)),
635 DECODE(l_attribute4(i), FND_API.G_MISS_CHAR, NULL, l_attribute4(i)),
636 DECODE(l_attribute5(i), FND_API.G_MISS_CHAR, NULL, l_attribute5(i)),
637 DECODE(l_attribute6(i), FND_API.G_MISS_CHAR, NULL, l_attribute6(i)),
638 DECODE(l_attribute7(i), FND_API.G_MISS_CHAR, NULL, l_attribute7(i)),
639 DECODE(l_attribute8(i), FND_API.G_MISS_CHAR, NULL, l_attribute8(i)),
640 DECODE(l_attribute9(i), FND_API.G_MISS_CHAR, NULL, l_attribute9(i)),
641 DECODE(l_attribute10(i), FND_API.G_MISS_CHAR, NULL, l_attribute10(i)),
642 DECODE(l_attribute11(i), FND_API.G_MISS_CHAR, NULL, l_attribute11(i)),
643 DECODE(l_attribute12(i), FND_API.G_MISS_CHAR, NULL, l_attribute12(i)),
644 DECODE(l_attribute13(i), FND_API.G_MISS_CHAR, NULL, l_attribute13(i)),
645 DECODE(l_attribute14(i), FND_API.G_MISS_CHAR, NULL, l_attribute14(i)),
646 DECODE(l_attribute15(i), FND_API.G_MISS_CHAR, NULL, l_attribute15(i)),
647 DECODE(l_bill_rate_override(i), FND_API.G_MISS_NUM, NULL, l_bill_rate_override(i)),
648 DECODE(l_bill_rate_curr_override(i), FND_API.G_MISS_CHAR, NULL, l_bill_rate_curr_override(i)),
649 DECODE(l_markup_percent_override(i), FND_API.G_MISS_NUM, NULL, l_markup_percent_override(i)),
650 DECODE(l_discount_percentage(i), FND_API.G_MISS_NUM, NULL, l_discount_percentage(i)),
651 DECODE(l_rate_disc_reason_code(i), FND_API.G_MISS_CHAR, NULL, l_rate_disc_reason_code(i)),
652 DECODE(l_tp_rate_override(i), FND_API.G_MISS_NUM, NULL, l_tp_rate_override(i)),
653 DECODE(l_tp_currency_override(i), FND_API.G_MISS_CHAR, NULL, l_tp_currency_override(i)),
654 DECODE(l_tp_calc_base_code_override(i), FND_API.G_MISS_CHAR, NULL, l_tp_calc_base_code_override(i)),
655 DECODE(l_tp_percent_applied_override(i), FND_API.G_MISS_NUM, NULL, l_tp_percent_applied_override(i)),
656 sysdate,
657 fnd_global.user_id,
658 sysdate,
659 fnd_global.user_id,
660 fnd_global.login_id,
661 DECODE(l_transfer_price_rate(i), FND_API.G_MISS_NUM, NULL, l_transfer_price_rate(i)), -- Added for 3051110
662 DECODE(l_transfer_pr_rate_curr(i), FND_API.G_MISS_CHAR, NULL, l_transfer_pr_rate_curr(i))
663 )
664 RETURNING assignment_id, assignment_number, rowid BULK COLLECT INTO l_assignment_id, l_assignment_number, l_rowid;
665
666 --when creating MULTIPLE requirements we need to pass all the new assignment ids to the schedule API.
667 --I don't want to add a new OUT parameter b/c many other APIs are calling
668 --this table handler and the impact is too big at this time.
669 --so storing the pl/sql table of new assignments in a global.
670 --note that we only create multiple requirements, NOT assignments.
671 --can't bulk collect into a table of records - so need to loop through.
672 FOR i IN l_assignment_id.FIRST .. l_assignment_id.LAST LOOP
673 PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id := l_assignment_id(i);
674 END LOOP;
675 /*Commented the code for bug 3079906*/
676 --IF p_number_of_requirements = 1 THEN
677 --only return the new assignment id, number and assignment row id if we are creating only 1 requirement.
678 x_new_assignment_id := l_assignment_id(1);
679 x_assignment_number := l_assignment_number(1);
680 x_assignment_row_id := l_rowid(1);
681
682 -- END IF; -- number of requirements =1
683
684
685 EXCEPTION
686 WHEN OTHERS THEN -- catch the exceptins here
687 -- Set the exception Message and the stack
688 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_ASSIGNMENTS_PKG.Insert_Row'
689 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
691 RAISE;
692 END Insert_Row;
693
694
695 PROCEDURE Update_Row
696 ( p_assignment_row_id IN ROWID :=NULL
697 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE
698 ,p_record_version_number IN NUMBER := NULL
699 ,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
700 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
701 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
702 ,p_apprvl_status_code IN pa_project_assignments.apprvl_status_code%TYPE := FND_API.G_MISS_CHAR
703 ,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
704 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
705 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
706 ,p_assignment_template_id IN pa_project_assignments.assignment_template_id%TYPE := FND_API.G_MISS_NUM
707 ,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
708 ,p_resource_id IN pa_project_assignments.resource_id%TYPE := FND_API.G_MISS_NUM
709 ,p_project_party_id IN pa_project_assignments.project_party_id%TYPE := FND_API.G_MISS_NUM
710 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
711 ,p_note_to_approver IN pa_project_assignments.note_to_approver%TYPE := FND_API.G_MISS_CHAR
712 ,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
713 ,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
714 ,p_assignment_effort IN pa_project_assignments.assignment_effort%TYPE := FND_API.G_MISS_NUM
715 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
716 ,p_source_assignment_id IN pa_project_assignments.source_assignment_id%TYPE := FND_API.G_MISS_NUM
717 ,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
718 ,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
719 ,p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
720 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
721 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
722 ,p_revenue_currency_code IN pa_project_assignments.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
723 ,p_revenue_bill_rate IN pa_project_assignments.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
724 ,p_markup_percent IN pa_project_assignments.markup_percent%TYPE := FND_API.G_MISS_NUM
725 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
726 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
727 ,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
728 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
729 ,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
730 ,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
731 ,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
732 ,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
733 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
734 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
735 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
736 ,p_calendar_type IN pa_project_assignments.calendar_type%TYPE := FND_API.G_MISS_CHAR
737 ,p_calendar_id IN pa_project_assignments.calendar_id%TYPE := FND_API.G_MISS_NUM
738 ,p_resource_calendar_percent IN pa_project_assignments.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
739 ,p_pending_approval_flag IN pa_project_assignments.pending_approval_flag%TYPE := FND_API.G_MISS_CHAR
740 ,p_no_of_active_candidates IN pa_project_assignments.no_of_active_candidates%TYPE := FND_API.G_MISS_NUM
741 ,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
742 ,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
743 ,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
744 ,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
745 ,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
746 ,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
747 ,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
748 ,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
749 ,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
750 ,p_bill_rate_override IN pa_project_assignments.bill_rate_override%TYPE := FND_API.G_MISS_NUM
751 ,p_bill_rate_curr_override IN pa_project_assignments.bill_rate_curr_override%TYPE := FND_API.G_MISS_CHAR
752 ,p_markup_percent_override IN pa_project_assignments.markup_percent_override%TYPE := FND_API.G_MISS_NUM
753 ,p_discount_percentage IN pa_project_assignments.discount_percentage%TYPE := FND_API.G_MISS_NUM -- Bug 2590938
754 ,p_rate_disc_reason_code IN pa_project_assignments.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR -- Bug 2590938
755 ,p_tp_rate_override IN pa_project_assignments.tp_rate_override%TYPE := FND_API.G_MISS_NUM
756 ,p_tp_currency_override IN pa_project_assignments.tp_currency_override%TYPE := FND_API.G_MISS_CHAR
757 ,p_tp_calc_base_code_override IN pa_project_assignments.tp_calc_base_code_override%TYPE := FND_API.G_MISS_CHAR
758 ,p_tp_percent_applied_override IN pa_project_assignments.tp_percent_applied_override%TYPE := FND_API.G_MISS_NUM
759 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
760 ,p_resource_list_member_id IN pa_project_assignments.resource_list_member_id%TYPE := FND_API.G_MISS_NUM -- FP.M Development
761 ,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
762 ,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
763 ,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
764 ,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
765 ,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
766 ,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
767 ,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
768 ,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
769 ,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
770 ,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
771 ,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
772 ,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
773 ,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
774 ,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
775 ,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
776 ,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
777 /* Added 2 columns for 3051110 */
778 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE := FND_API.G_MISS_NUM
779 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE := FND_API.G_MISS_CHAR
780 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
781 )IS
782
783
784 l_row_id ROWID := p_assignment_row_id;
785 l_record_version_number NUMBER;
786 l_workflow_in_progress_flag pa_team_templates.workflow_in_progress_flag%TYPE;
787
788 TYPE assignment_id IS TABLE OF pa_project_assignments.assignment_id%TYPE
789 INDEX BY BINARY_INTEGER;
790 l_assignment_id assignment_id;
791 l_mass_wf_in_progress_flag pa_project_assignments.mass_wf_in_progress_flag%TYPE;
792
793 CURSOR get_row_id IS
794 SELECT rowid
795 FROM pa_project_assignments
796 WHERE assignment_id = p_assignment_id;
797
798 CURSOR check_team_template_wf IS
799 SELECT workflow_in_progress_flag
800 FROM pa_team_templates
801 WHERE team_template_id = p_assignment_template_id;
802
803 /*
804 -- do not have to check mass_wf_in_progress_flag again
805 -- right before insert
806 -- 1) for Mass, select checkboxes are disabled on Team page if wf
807 -- is pending for the role
808 -- 2) for Single, check is taken care of in PA_ASSIGNMENT_PUB
809 CURSOR check_project_assignment_wf IS
810 SELECT mass_wf_in_progress_flag
811 FROM pa_project_assignments
812 WHERE assignment_id = p_assignment_id;
813 */
814 BEGIN
815
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 --if this is a template requirement then check that worflow is not in progress
819 --on the parent team template. If it is in progress then no new template requirements
820 --can be created
821 IF (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
822 (p_assignment_template_id IS NOT NULL and p_assignment_template_id <>FND_API.G_MISS_NUM) THEN
823 OPEN check_team_template_wf;
824 FETCH check_team_template_wf INTO l_workflow_in_progress_flag;
825 CLOSE check_team_template_wf;
826
827 IF l_workflow_in_progress_flag='Y' THEN
828 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
829 ,p_msg_name => 'PA_NO_REQ_WF');
830 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 RETURN;
833 END IF;
834 END IF;
835
836 /*
837 -- do not have to check mass_wf_in_progress_flag again
838 -- right before insert
839 -- 1) for Mass, select checkboxes are disabled on Team page if wf
840 -- is pending for the role
841 -- 2) for Single, check is taken care of in PA_ASSIGNMENT_PUB
842
843 -- check that mass workflow for updating assignment is not in progress.
844 -- if mass workflow is in progress, cannot update the assignment
845 -- The p_assignment_id is null only when user is creating multiple
846 -- copies of requirement. Therefore, do not have to check if wf is in progress
847 -- because these are newly created requirements
848 IF p_assignment_id IS NOT NULL THEN
849 OPEN check_project_assignment_wf;
850 FETCH check_project_assignment_wf INTO l_mass_wf_in_progress_flag;
851 CLOSE check_project_assignment_wf;
852
853 IF l_mass_wf_in_progress_flag = 'Y' THEN
854
855 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
856 ,p_msg_name => 'PA_ASSIGNMENT_WF');
857 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 RETURN;
860 END IF;
861 END IF;
862 */
863 -- Increment the record version number by 1
864 l_record_version_number := p_record_version_number +1;
865
866 -- Copy the global assignment_id table into the local array
867 -- If global table is empty, then insert the passed in assignment_id into the local array
868 -- The global assignment_id table is not empty only when user is
869 -- creating multiple copies of requirement
870 IF PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT > 0 AND p_assignment_id IS NULL THEN
871 FOR i IN PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.FIRST .. PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.LAST LOOP
872 l_assignment_id(i) :=PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id;
873 END LOOP;
874 ELSE
875 l_assignment_id(1) := p_assignment_id;
876 END IF;
877
878 FORALL i IN l_assignment_id.FIRST .. l_assignment_id.LAST
879
880 UPDATE pa_project_assignments
881 SET assignment_name = DECODE(p_assignment_name, FND_API.G_MISS_CHAR, assignment_name, p_assignment_name),
882 assignment_type = DECODE(p_assignment_type, FND_API.G_MISS_CHAR, assignment_type, p_assignment_type),
883 record_version_number = DECODE(p_record_version_number, NULL, record_version_number, l_record_version_number),
884 multiple_status_flag = DECODE(p_multiple_status_flag, FND_API.G_MISS_CHAR, multiple_status_flag, p_multiple_status_flag),
885 apprvl_status_code = DECODE(p_apprvl_status_code, FND_API.G_MISS_CHAR, apprvl_status_code, p_apprvl_status_code),
886 status_code = DECODE(p_status_code, FND_API.G_MISS_CHAR, status_code, p_status_code),
887 staffing_priority_code = DECODE(p_staffing_priority_code, FND_API.G_MISS_CHAR, staffing_priority_code, p_staffing_priority_code),
888 project_id = DECODE(p_project_id, FND_API.G_MISS_NUM, project_id, p_project_id),
889 assignment_template_id = DECODE(p_assignment_template_id, FND_API.G_MISS_NUM, assignment_template_id, p_assignment_template_id),
890 project_role_id = DECODE(p_project_role_id, FND_API.G_MISS_NUM, project_role_id, p_project_role_id),
891 resource_id = DECODE(p_resource_id,FND_API.G_MISS_NUM,resource_id, p_resource_id),
892 project_party_id = DECODE(p_project_party_id, FND_API.G_MISS_NUM, project_party_id, p_project_party_id),
893 description = DECODE(p_description, FND_API.G_MISS_CHAR, description, p_description),
894 note_to_approver = DECODE(p_note_to_approver, FND_API.G_MISS_CHAR,note_to_approver , p_note_to_approver),
895 start_date = DECODE(p_start_date, FND_API.G_MISS_DATE, start_date, p_start_date),
896 end_date = DECODE(p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date),
897 assignment_effort = DECODE(p_assignment_effort, FND_API.G_MISS_NUM, assignment_effort, p_assignment_effort),
898 extension_possible = DECODE(p_extension_possible, FND_API.G_MISS_CHAR, extension_possible, p_extension_possible),
899 source_assignment_id = DECODE(p_source_assignment_id, FND_API.G_MISS_NUM, source_assignment_id, p_source_assignment_id),
900 min_resource_job_level = DECODE(p_min_resource_job_level, FND_API.G_MISS_NUM, min_resource_job_level, p_min_resource_job_level),
901 max_resource_job_level = DECODE(p_max_resource_job_level, FND_API.G_MISS_NUM, max_resource_job_level, p_max_resource_job_level),
902 assignment_number = DECODE(p_assignment_number, FND_API.G_MISS_NUM, assignment_number, p_assignment_number),
903 additional_information = DECODE(p_additional_information, FND_API.G_MISS_CHAR, additional_information, p_additional_information),
904 work_type_id = DECODE(p_work_type_id, FND_API.G_MISS_NUM, work_type_id, p_work_type_id),
905 revenue_currency_code = DECODE(p_revenue_currency_code, FND_API.G_MISS_CHAR, revenue_currency_code, p_revenue_currency_code),
906 revenue_bill_rate = DECODE(p_revenue_bill_rate, FND_API.G_MISS_NUM, revenue_bill_rate, p_revenue_bill_rate),
907 markup_percent = DECODE(p_markup_percent, FND_API.G_MISS_NUM, markup_percent, p_markup_percent),
908 expense_owner = DECODE(p_expense_owner, FND_API.G_MISS_CHAR, expense_owner, p_expense_owner),
909 expense_limit = DECODE(p_expense_limit, FND_API.G_MISS_NUM, expense_limit, p_expense_limit),
910 expense_limit_currency_code = DECODE(p_expense_limit_currency_code, FND_API.G_MISS_CHAR, expense_limit_currency_code, p_expense_limit_currency_code),
911 fcst_tp_amount_type = DECODE(p_fcst_tp_amount_type, FND_API.G_MISS_CHAR, fcst_tp_amount_type, p_fcst_tp_amount_type),
912 fcst_job_id = DECODE(p_fcst_job_id, FND_API.G_MISS_NUM, fcst_job_id, p_fcst_job_id),
913 fcst_job_group_id = DECODE(p_fcst_job_group_id, FND_API.G_MISS_NUM,fcst_job_group_id, p_fcst_job_group_id),
914 expenditure_org_id = DECODE(p_expenditure_org_id, FND_API.G_MISS_NUM,expenditure_org_id, p_expenditure_org_id),
915 expenditure_organization_id = DECODE(p_expenditure_organization_id, FND_API.G_MISS_NUM,expenditure_organization_id, p_expenditure_organization_id),
916 expenditure_type_class = DECODE(p_expenditure_type_class, FND_API.G_MISS_CHAR,expenditure_type_class, p_expenditure_type_class),
917 expenditure_type = DECODE(p_expenditure_type, FND_API.G_MISS_CHAR,expenditure_type, p_expenditure_type),
918 location_id = DECODE(p_location_id, FND_API.G_MISS_NUM, location_id, p_location_id),
919 calendar_type = DECODE(p_calendar_type, FND_API.G_MISS_CHAR, calendar_type, p_calendar_type),
920 calendar_id = DECODE(p_calendar_id, FND_API.G_MISS_NUM, calendar_id, p_calendar_id),
921 resource_calendar_percent = DECODE(p_resource_calendar_percent, FND_API.G_MISS_NUM, resource_calendar_percent, p_resource_calendar_percent),
922 pending_approval_flag = DECODE(p_pending_approval_flag, FND_API.G_MISS_CHAR, pending_approval_flag, p_pending_approval_flag),
923 no_of_active_candidates = DECODE(p_no_of_active_candidates, FND_API.G_MISS_NUM, no_of_active_candidates, p_no_of_active_candidates),
924 competence_match_weighting = DECODE(p_comp_match_weighting, FND_API.G_MISS_NUM, competence_match_weighting, p_comp_match_weighting),
925 availability_match_weighting= DECODE(p_avail_match_weighting, FND_API.G_MISS_NUM, availability_match_weighting, p_avail_match_weighting),
926 job_level_match_weighting = DECODE(p_job_level_match_weighting, FND_API.G_MISS_NUM, job_level_match_weighting, p_job_level_match_weighting),
927 search_min_availability = DECODE(p_search_min_availability, FND_API.G_MISS_NUM, search_min_availability, p_search_min_availability),
928 search_country_code = DECODE(p_search_country_code, FND_API.G_MISS_CHAR, search_country_code, p_search_country_code),
929 search_exp_org_struct_ver_id = DECODE(p_search_exp_org_struct_ver_id, FND_API.G_MISS_NUM, search_exp_org_struct_ver_id, p_search_exp_org_struct_ver_id),
930 search_exp_start_org_id = DECODE(p_search_exp_start_org_id, FND_API.G_MISS_NUM, search_exp_start_org_id, p_search_exp_start_org_id),
931 search_min_candidate_score = DECODE(p_search_min_candidate_score, FND_API.G_MISS_NUM, search_min_candidate_score, p_search_min_candidate_score),
932 enable_auto_cand_nom_flag = DECODE(p_enable_auto_cand_nom_flag, FND_API.G_MISS_CHAR, enable_auto_cand_nom_flag, p_enable_auto_cand_nom_flag),
933 bill_rate_override = DECODE(p_bill_rate_override, FND_API.G_MISS_NUM, bill_rate_override, p_bill_rate_override),
934 bill_rate_curr_override = DECODE(p_bill_rate_curr_override, FND_API.G_MISS_CHAR, bill_rate_curr_override, p_bill_rate_curr_override),
935 markup_percent_override = DECODE(p_markup_percent_override, FND_API.G_MISS_NUM, markup_percent_override, p_markup_percent_override),
936 discount_percentage = DECODE(p_discount_percentage, FND_API.G_MISS_NUM, discount_percentage, p_discount_percentage), -- Bug 2590938
937 rate_disc_reason_code = DECODE(p_rate_disc_reason_code, FND_API.G_MISS_CHAR, rate_disc_reason_code, p_rate_disc_reason_code), -- Bug 2590938
938 tp_rate_override = DECODE(p_tp_rate_override, FND_API.G_MISS_NUM, tp_rate_override, p_tp_rate_override),
939 tp_currency_override = DECODE(p_tp_currency_override, FND_API.G_MISS_CHAR, tp_currency_override, p_tp_currency_override),
940 tp_calc_base_code_override = DECODE(p_tp_calc_base_code_override, FND_API.G_MISS_CHAR,tp_calc_base_code_override, p_tp_calc_base_code_override),
941 tp_percent_applied_override = DECODE(p_tp_percent_applied_override, FND_API.G_MISS_NUM, tp_percent_applied_override, p_tp_percent_applied_override),
942 staffing_owner_person_id = DECODE(p_staffing_owner_person_id, FND_API.G_MISS_NUM, staffing_owner_person_id, p_staffing_owner_person_id),
943 resource_list_member_id = DECODE(p_resource_list_member_id, FND_API.G_MISS_NUM, resource_list_member_id, p_resource_list_member_id),
944 attribute_category = DECODE(p_attribute_category, FND_API.G_MISS_CHAR, attribute_category, p_attribute_category),
945 attribute1 = DECODE(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1),
946 attribute2 = DECODE(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2),
947 attribute3 = DECODE(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3),
948 attribute4 = DECODE(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4),
949 attribute5 = DECODE(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5),
950 attribute6 = DECODE(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6),
951 attribute7 = DECODE(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7),
952 attribute8 = DECODE(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8),
953 attribute9 = DECODE(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9),
954 attribute10 = DECODE(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10),
955 attribute11 = DECODE(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11),
956 attribute12 = DECODE(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12),
957 attribute13 = DECODE(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13),
958 attribute14 = DECODE(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14),
959 attribute15 = DECODE(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15),
960 last_update_date = sysdate,
961 last_updated_by = fnd_global.user_id,
962 last_update_login = fnd_global.login_id,
963 transfer_price_rate = DECODE(p_transfer_price_rate, FND_API.G_MISS_NUM, transfer_price_rate, p_transfer_price_rate), -- Added for 3051110
964 transfer_pr_rate_curr = DECODE(p_transfer_pr_rate_curr, FND_API.G_MISS_CHAR, transfer_pr_rate_curr, p_transfer_pr_rate_curr)
965 WHERE assignment_id = l_assignment_id(i)
966 AND nvl(p_record_version_number, record_version_number) = record_version_number;
967
968
969 IF (SQL%NOTFOUND) THEN
970
971 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
972 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
973 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
974 x_return_status := FND_API.G_RET_STS_ERROR;
975
976 END IF;
977
978
979 --
980 EXCEPTION
981 WHEN OTHERS THEN -- catch the exceptins here
982 -- Set the exception Message and the stack
983 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_ASSIGNMENTS_PKG.Update_Row'
984 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
986 RAISE;
987 --
988 END Update_Row;
989
990
991 PROCEDURE Delete_Row
992 ( p_assignment_row_id IN ROWID
993 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE
994 ,p_record_version_number IN NUMBER := NULL
995 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
996 )
997 IS
998
999 l_row_id ROWID;
1000 l_workflow_in_progress_flag pa_team_templates.workflow_in_progress_flag%TYPE;
1001 l_mass_wf_in_progress_flag pa_project_assignments.mass_wf_in_progress_flag%TYPE;
1002
1003 CURSOR check_team_template_wf IS
1004 SELECT tt.workflow_in_progress_flag
1005 FROM pa_project_assignments asgn,
1006 pa_team_templates tt
1007 WHERE asgn.assignment_id = p_assignment_id
1008 AND asgn.template_flag = 'Y'
1009 AND tt.team_template_id = asgn.assignment_template_id;
1010
1011 CURSOR check_project_assignment_wf IS
1012 SELECT mass_wf_in_progress_flag
1013 FROM pa_project_assignments
1014 WHERE assignment_id = p_assignment_id;
1015
1016 BEGIN
1017
1018 x_return_status := FND_API.G_RET_STS_SUCCESS;
1019
1020 --check if workflow is in progress for the parent team template.
1021 --if so, it can't be deleted.
1022 --if this is not a template requirement the cursor won't return any
1023 --rows and the delete API will continue.
1024 --we don't know if this is a template requirement (no project_id in the API) prior
1025 --to opening the cursor.
1026 OPEN check_team_template_wf;
1027 FETCH check_team_template_wf INTO l_workflow_in_progress_flag;
1028 CLOSE check_team_template_wf;
1029 IF l_workflow_in_progress_flag = 'Y' THEN
1030 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1031 ,p_msg_name => 'PA_NO_REQ_WF');
1032 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1033 x_return_status := FND_API.G_RET_STS_ERROR;
1034 RETURN;
1035 END IF;
1036
1037 -- check that mass workflow for updating assignment is not in progress.
1038 -- if mass workflow is in progress, cannot delete the assignment
1039 OPEN check_project_assignment_wf;
1040 FETCH check_project_assignment_wf INTO l_mass_wf_in_progress_flag;
1041 CLOSE check_project_assignment_wf;
1042
1043 IF l_mass_wf_in_progress_flag = 'Y' THEN
1044
1045 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
1046 ,p_msg_name => 'PA_ASSIGNMENT_WF');
1047 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 RETURN;
1050 END IF;
1051
1052 IF (p_assignment_id IS NOT NULL AND p_assignment_id <> FND_API.G_MISS_NUM) THEN
1053
1054 DELETE FROM pa_project_assignments
1055 WHERE assignment_id = p_assignment_id
1056 AND nvl(p_record_version_number, record_version_number) = record_version_number;
1057
1058 ELSIF (p_assignment_row_id IS NOT NULL) THEN
1059 DELETE FROM pa_project_assignments
1060 WHERE rowid = p_assignment_row_id
1061 AND nvl(p_record_version_number, record_version_number) = record_version_number;
1062
1063 END IF;
1064
1065 --
1066 IF (SQL%NOTFOUND) THEN
1067
1068 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1069 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
1070 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1071 x_return_status := FND_API.G_RET_STS_ERROR;
1072
1073 END IF;
1074 --
1075 --
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 -- Set the exception Message and the stack
1080 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_ASSIGNMENTS_PKG.Delete_Row'
1081 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1083 RAISE;
1084 END Delete_Row;
1085
1086
1087 PROCEDURE Lock_Row
1088 ( p_assignment_row_id IN ROWID
1089 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1090 ,p_record_version_number IN NUMBER := NULL
1091 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1092 )
1093 IS
1094
1095 l_row_id ROWID;
1096
1097 BEGIN
1098 -- Lock the Assignment;
1099 SELECT rowid INTO l_row_id
1100 FROM pa_project_assignments
1101 WHERE assignment_id = p_assignment_id
1102 OR rowid = p_assignment_row_id
1103 FOR UPDATE NOWAIT;
1104 --
1105 --
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 -- Set the exception Message and the stack
1109 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_ASSIGNMENTS_PKG.Lock_Row'
1110 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1112 RAISE;
1113
1114 END Lock_Row;
1115 --
1116 --
1117 END pa_project_assignments_pkg;