[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECTS_MAINT_PUB
Source
1 PACKAGE BODY PA_PROJECTS_MAINT_PUB AS
2 /* $Header: PARMPRPB.pls 120.8.12020000.2 2012/07/19 09:58:18 admarath ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECTS_MAINT_PUB';
4
5 -- API name : create_project
6 -- Type : Public
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- p_api_version IN NUMBER Optional Default = 1.0
10 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
11 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
13 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
14 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
15 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
16 -- p_orig_project_id IN NUMBER Required
17 -- p_project_name IN VARCHAR2 Required
18 -- p_project_number IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
19 -- p_description IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
20 -- p_project_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
21 -- p_project_status_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
22 -- p_project_status_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
23 -- p_distribution_rule IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
24 -- p_public_sector_flag IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
25 -- p_carrying_out_organization_id IN NUMBER Optional
26 -- Default = FND_API.G_MISS_NUM
27 -- p_organization_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
28 -- p_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
29 -- p_completion_date IN DATE Optional Default = FND_API.G_MISS_DATE
30 -- p_probability_member_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
31 -- p_probability_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
32 -- p_project_value IN NUMBER Optional Default = FND_API.G_MISS_NUM
33 -- p_expected_approval_date IN DATE Optional Default = FND_API.G_MISS_DATE
34 -- p_team_template_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
35 -- p_team_template_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
36 -- p_country_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
37 -- p_country_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
38 -- p_region IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
39 -- p_city IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
40 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
41 -- p_customer_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
42 -- p_agreement_currency IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
43 -- p_agreement_currency_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
44 -- p_agreement_amount IN NUMBER Optional Default = FND_API.G_MISS_NUM
45 -- p_agreement_org_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
46 -- p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
47 -- p_opp_value_currency_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
48 -- p_bill_to_customer_id IN NUMBER := NULL ,
49 -- p_ship_to_customer_id IN NUMBER := NULL ,
50 -- p_long_name IN VARCHAR2 Optional Default = NULL
51 -- p_project_id OUT NUMBER Required
52 -- p_new_project_number OUT VARCHAR2 Required
53 -- x_return_status OUT VARCHAR2 Required
54 -- x_msg_count OUT NUMBER Required
55 -- x_msg_data OUT VARCHAR2 Required
56 --
57 -- History
58 --
59 -- 18-AUG-2000 -- Sakthi/William - Created.
60 --
61 --
62 PROCEDURE CREATE_PROJECT
63 (
64 p_api_version IN NUMBER := 1.0,
65 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
66 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
67 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
68 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
69 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
70 p_debug_mode IN VARCHAR2 := 'N' ,
71 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
72 p_orig_project_id IN NUMBER ,
73 p_project_name IN VARCHAR2 ,
74 p_project_number IN VARCHAR2 := FND_API.G_MISS_CHAR ,
75 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
76 p_project_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
77 p_project_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
78 p_project_status_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
79 p_distribution_rule IN VARCHAR2 := FND_API.G_MISS_CHAR ,
80 p_public_sector_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
81 p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
82 p_organization_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
83 p_start_date IN DATE := FND_API.G_MISS_DATE ,
84 p_completion_date IN DATE := FND_API.G_MISS_DATE ,
85 p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
86 p_probability_percentage IN NUMBER := FND_API.G_MISS_NUM ,
87 p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
88 p_expected_approval_date IN DATE := FND_API.G_MISS_DATE ,
89 p_team_template_id IN NUMBER := FND_API.G_MISS_NUM ,
90 p_team_template_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
91 p_country_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
92 p_country_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
93 p_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
94 p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
95 p_customer_id IN NUMBER := FND_API.G_MISS_NUM ,
96 p_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
97 p_agreement_currency IN VARCHAR2 := FND_API.G_MISS_CHAR ,
98 p_agreement_currency_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
99 p_agreement_amount IN NUMBER := FND_API.G_MISS_NUM ,
100 p_agreement_org_id IN NUMBER := FND_API.G_MISS_NUM ,
101 p_agreement_org_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
102 p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
103 p_opp_value_currency_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
104 p_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
105 p_template_flag IN VARCHAR2 := 'N',
106 p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
107 /*Customer Account Relationships*/
108 p_bill_to_customer_id IN NUMBER := NULL ,
109 p_ship_to_customer_id IN NUMBER := NULL ,
110 /*Customer Account Relationships*/
111 p_bill_to_customer_name IN VARCHAR2 := NULL , /* Bug2977891*/
112 p_ship_to_customer_name IN VARCHAR2 := NULL , /* Bug2977891*/
113 -- anlee
114 -- Project Long Name changes
115 p_long_name IN VARCHAR2 DEFAULT NULL ,
116 -- end of changes
117 p_project_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
118 p_new_project_number OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
119 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
120 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
121 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
122 IS
123
124 l_api_name CONSTANT VARCHAR(30) := 'create_project';
125 l_api_version CONSTANT NUMBER := 1.0;
126
127 -- 4363092 TCA changes, replaced RA views with HZ tables
128 /*
129 l_customer_id ra_customers.customer_id%TYPE;
130 l_bill_to_customer_id ra_customers.customer_id%TYPE; -- Bug 2977891
131 l_ship_to_customer_id ra_customers.customer_id%TYPE; -- Bug 2977891
132 */
133
134 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
135 l_bill_to_customer_id hz_cust_accounts.cust_account_id%TYPE; -- Bug 2977891
136 l_ship_to_customer_id hz_cust_accounts.cust_account_id%TYPE; -- Bug 2977891
137 -- 4363092 end
138
139 l_organization_id hr_organization_units.organization_id%TYPE;
140 l_project_status_code pa_project_statuses.project_status_code%TYPE;
141 l_project_id pa_projects.project_id%TYPE;
142 l_project_type pa_projects_all.project_type%TYPE;
143 l_project_number_out pa_projects.segment1%TYPE;
144 l_probability_member_id pa_probability_members.probability_member_id%TYPE;
145 l_team_template_id pa_team_templates.team_template_id%TYPE;
146 l_country_code pa_locations.country_code%TYPE;
147 l_return_status VARCHAR2(1);
148 l_error_msg_code VARCHAR2(250);
149 l_msg_count NUMBER;
150 l_msg_data VARCHAR2(2000);
151 l_err_code VARCHAR2(2000);
152 l_err_stage VARCHAR2(2000);
153 l_err_stack VARCHAR2(2000);
154 l_data VARCHAR2(2000);
155 l_msg_index_out NUMBER;
156 l_relationship_type VARCHAR2(30);
157 l_new_project_number VARCHAR2(30);
158 l_agreement_currency FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
159 l_agreement_org_id pa_organizations_project_v.organization_id%TYPE;
160 l_opp_value_currency_code FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
161
162 CURSOR l_project_csr (c_project_id NUMBER)
163 Is
164 Select project_type
165 From pa_projects_all
166 Where project_id = c_project_id;
167
168 BEGIN
169
170 -- Standard call to check for call compatibility
171
172 if (p_debug_mode = 'Y') then
173 pa_debug.debug('Create_Project PUB : Checking the api version number.');
174 end if;
175
176 --dbms_output.put_line('Starts here PA_PROJECTS_MAINT_PUB.CREATE_PROJECT ... ');
177
178 IF p_commit = FND_API.G_TRUE THEN
179 SAVEPOINT prm_create_project;
180 END IF;
181
182 --dbms_output.put_line('Before FND_API.COMPATIBLE_API_CALL ... ');
183
184 if NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
185 p_api_version,
186 l_api_name,
187 g_pkg_name)
188 then
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 end if;
191
192 -- Initialize the message stack if required
193
194 if (p_debug_mode = 'Y') then
195 pa_debug.debug('CREATE_PROJECT PUB : Initializing message stack.');
196 end if;
197
198 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.CREATE_PROJECT');
199
200 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
201 fnd_msg_pub.initialize;
202 end if;
203
204 -- dbms_output.put_line('After initializing the stack');
205
206 x_return_status := 'S';
207
208 --dbms_output.put_line('Before p_carrying_out_organization_id ... ');
209
210 IF (p_carrying_out_organization_id is not null AND
211 p_carrying_out_organization_id <> FND_API.G_MISS_NUM) OR
212 (p_organization_name is not null AND
213 p_organization_name <> FND_API.G_MISS_CHAR)
214 THEN
215 if (p_debug_mode = 'Y') then
216 pa_debug.debug('Create_Project PUB : Checking Carrying out organization');
217 end if;
218 pa_hr_org_utils.Check_OrgName_Or_Id
219 ( p_organization_id => p_carrying_out_organization_id
220 ,p_organization_name => p_organization_name
221 ,p_check_id_flag => 'A'
222 ,x_organization_id => l_organization_id
223 ,x_return_status => l_return_status
224 ,x_error_msg_code => l_error_msg_code);
225
226 --dbms_output.put_line('AFTER check org ... '||l_return_status);
227
228 IF l_return_status = FND_API.G_RET_STS_ERROR
229 THEN
230 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
231 p_msg_name => l_error_msg_code);
232
233 x_msg_data := l_error_msg_code;
234 x_return_status := 'E';
235 END IF;
236
237 END IF;
238
239 --dbms_output.put_line('Before p_project_status_code ... ');
240
241 IF (p_project_status_code is not null AND
242 p_project_status_code <> FND_API.G_MISS_CHAR ) OR
243 (p_project_status_name is not null AND
244 p_project_status_name <> FND_API.G_MISS_CHAR)
245 THEN
246
247 --dbms_output.put_line('IN p_project_status_code ... ');
248
249 if (p_debug_mode = 'Y') then
250 pa_debug.debug('Create_Project PUB : Checking Project status');
251 end if;
252
253 PA_PROJECTS_MAINT_UTILS.CHECK_PROJECT_STATUS_OR_ID
254 (p_project_status_code => p_project_status_code
255 ,p_project_status_name => p_project_status_name
256 ,p_check_id_flag => 'A'
257 ,x_project_status_code => l_project_status_code
258 ,x_return_status => l_return_status
259 ,x_error_msg_code => l_error_msg_code);
260 --dbms_output.put_line('AFTER check project status ... '||l_return_status);
261 IF l_return_status = FND_API.G_RET_STS_ERROR
262 THEN
263 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
264 p_msg_name => l_error_msg_code);
265 x_msg_data := l_error_msg_code;
266 x_return_status := 'E';
267 END IF;
268 END IF;
269
270 --dbms_output.put_line('Before probability member call ... ');
271
272 OPEN l_project_csr (p_project_id);
273 FETCH l_project_csr INTO l_project_type;
274 CLOSE l_project_csr;
275
276 l_probability_member_id := p_probability_member_id;
277
278 IF (p_probability_member_id is not null AND
279 P_probability_member_id <> FND_API.G_MISS_NUM ) OR
280 (p_probability_percentage is not null AND
281 p_probability_percentage <> FND_API.G_MISS_NUM)
282 THEN
283 if (p_debug_mode = 'Y') then
284 pa_debug.debug('Create_Project PUB : Checking Probability code');
285 end if;
286 PA_PROJECTS_MAINT_UTILS.CHECK_PROBABILITY_CODE_OR_ID
287 ( p_probability_member_id => p_probability_member_id
288 ,p_probability_percentage => p_probability_percentage
289 ,p_project_type => l_project_type
290 ,p_check_id_flag => 'Y'
291 ,x_probability_member_id => l_probability_member_id
292 ,x_return_status => l_return_status
293 ,x_error_msg_code => l_error_msg_code);
294
295 --dbms_output.put_line('AFTER check probablity ... '||l_return_status);
296
297 IF l_return_status = FND_API.G_RET_STS_ERROR
298 THEN
299 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
300 p_msg_name => l_error_msg_code);
301 x_msg_data := l_error_msg_code;
302 x_return_status := 'E';
303 END IF;
304 END IF;
305
306 --dbms_output.put_line('Before customer call ...'||to_char(p_customer_id));
307
308 l_customer_id := p_customer_id; --bug 2783257
309
310 IF (p_customer_id is not null
311 AND p_customer_id <> FND_API.G_MISS_NUM) OR
312 (p_customer_name is not null AND p_customer_name <> FND_API.G_MISS_CHAR)
313 THEN
314 if (p_debug_mode = 'Y') then
315 pa_debug.debug('Create_Project PUB : Checking Customer code');
316 end if;
317 --dbms_output.put_line('Before check customer or customer call ... ');
318 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
319 ( p_customer_id => p_customer_id
320 ,p_customer_name => p_customer_name
321 ,p_check_id_flag => 'A'
322 ,x_customer_id => l_customer_id
323 ,x_return_status => l_return_status
324 ,x_error_msg_code => l_error_msg_code);
325
326 --dbms_output.put_line('AFTER check customer or customer call ... '||l_return_status);
327 IF l_return_status <> 'S'
328 THEN
329 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
330 p_msg_name => l_error_msg_code);
331 x_msg_data := l_error_msg_code;
332 x_return_status := 'E';
333 END IF;
334
335 END IF;
336
337 /* Bug2977891 Begin*/
338 l_bill_to_customer_id := p_bill_to_customer_id;
339 IF (p_bill_to_customer_id is not null
340 AND p_bill_to_customer_id <> FND_API.G_MISS_NUM) OR
341 (p_bill_to_customer_name is not null AND p_bill_to_customer_name <> FND_API.G_MISS_CHAR)
342 THEN
343 if (p_debug_mode = 'Y') then
344 pa_debug.debug('Create_Project PUB : Checking Bill Customer');
345 end if;
346
347 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
348 ( p_customer_id => p_bill_to_customer_id
349 ,p_customer_name => p_bill_to_customer_name
350 ,p_check_id_flag => 'A'
351 ,x_customer_id => l_bill_to_customer_id
352 ,x_return_status => l_return_status
353 ,x_error_msg_code => l_error_msg_code);
354
355 IF l_return_status <> 'S'
356 THEN
357 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
358 p_msg_name => l_error_msg_code||'_BILL');
359 x_msg_data := l_error_msg_code;
360 x_return_status := 'E';
361 END IF;
362
363 END IF;
364
365 l_ship_to_customer_id := p_ship_to_customer_id;
366 IF (p_ship_to_customer_id is not null
367 AND p_ship_to_customer_id <> FND_API.G_MISS_NUM) OR
368 (p_ship_to_customer_name is not null AND p_ship_to_customer_name <> FND_API.G_MISS_CHAR)
369 THEN
370 if (p_debug_mode = 'Y') then
371 pa_debug.debug('Create_Project PUB : Checking Ship Customer');
372 end if;
373
374 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
375 ( p_customer_id => p_ship_to_customer_id
376 ,p_customer_name => p_ship_to_customer_name
377 ,p_check_id_flag => 'A'
378 ,x_customer_id => l_ship_to_customer_id
379 ,x_return_status => l_return_status
380 ,x_error_msg_code => l_error_msg_code);
381
382 IF l_return_status <> 'S'
383 THEN
384 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
385 p_msg_name => l_error_msg_code||'_SHIP');
386 x_msg_data := l_error_msg_code;
387 x_return_status := 'E';
388 END IF;
389
390 END IF;
391 /* Bug2977891 End*/
392
393 --dbms_output.put_line('Before check team template call ... ');
394 if (p_team_template_id is not null AND p_team_template_id <> FND_API.G_MISS_NUM) OR
395 (p_team_template_name is not null AND p_team_template_name <> FND_API.G_MISS_CHAR)
396 then
397 if (p_debug_mode = 'Y') then
398 pa_debug.debug('Create_Project PUB : Checking team template ID');
399 end if;
400
401 PA_TEAM_TEMPLATES_UTILS.CHECK_TEAM_TEMPLATE_NAME_OR_ID
402 ( p_team_template_id => p_team_template_id
403 ,p_team_template_name => p_team_template_name
404 ,p_check_id_flag => 'A'
405 ,x_team_template_id => l_team_template_id
406 ,x_return_status => l_return_status
407 ,x_error_message_code => l_error_msg_code);
408
409 if l_return_status <> 'S' then
410 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
411 p_msg_name => l_error_msg_code);
412 x_msg_data := l_error_msg_code;
413 x_return_status := 'E';
414 end if;
415 end if;
416
417 --dbms_output.put_line('Before check country code call ... ');
418 if (p_country_code is not null AND p_country_code <> FND_API.G_MISS_CHAR) OR
419 (p_country_name is not null AND p_country_name <> FND_API.G_MISS_CHAR)
420 then
421 if (p_debug_mode = 'Y') then
422 pa_debug.debug('Create_Project PUB : Checking country code');
423 end if;
424
425 PA_LOCATION_UTILS.CHECK_COUNTRY_NAME_OR_CODE
426 ( p_country_code => p_country_code
427 ,p_country_name => p_country_name
428 ,p_check_id_flag => 'A'
429 ,x_country_code => l_country_code
430 ,x_return_status => l_return_status
431 ,x_error_message_code => l_error_msg_code);
432
433 if l_return_status <> 'S' then
434 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
435 p_msg_name => l_error_msg_code);
436 x_msg_data := l_error_msg_code;
437 x_return_status := 'E';
438 end if;
439 end if;
440
441 --dbms_output.put_line('Before check agreement currency call ... ');
442 if (p_agreement_currency is not null AND p_agreement_currency <> FND_API.G_MISS_CHAR) OR
443 (p_agreement_currency_name is not null AND p_agreement_currency_name <> FND_API.G_MISS_CHAR)
444 then
445 if (p_debug_mode = 'Y') then
446 pa_debug.debug('Create_Project PUB : Checking agreement currency');
447 end if;
448
449 PA_PROJECTS_MAINT_UTILS.CHECK_CURRENCY_NAME_OR_CODE
450 ( p_agreement_currency => p_agreement_currency
451 ,p_agreement_currency_name => p_agreement_currency_name
452 ,p_check_id_flag => 'Y'
453 ,x_agreement_currency => l_agreement_currency
454 ,x_return_status => l_return_status
455 ,x_error_msg_code => l_error_msg_code);
456
457 if l_return_status <> 'S' then
458 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
459 p_msg_name => l_error_msg_code);
460 x_msg_data := l_error_msg_code;
461 x_return_status := 'E';
462 end if;
463 end if;
464
465 if (p_opp_value_currency_code is not null AND p_opp_value_currency_code <> FND_API.G_MISS_CHAR) OR
466 (p_opp_value_currency_name is not null AND p_opp_value_currency_name <> FND_API.G_MISS_CHAR
467 )
468 then
469 if (p_debug_mode = 'Y') then
470 pa_debug.debug('Create_Project PUB : Checking Opportunity Value currency');
471 end if;
472
473 PA_PROJECTS_MAINT_UTILS.CHECK_CURRENCY_NAME_OR_CODE
474 ( p_agreement_currency => p_opp_value_currency_code
475 ,p_agreement_currency_name => p_opp_value_currency_name
476 ,p_check_id_flag => 'Y'
477 ,x_agreement_currency => l_opp_value_currency_code
478 ,x_return_status => l_return_status
479 ,x_error_msg_code => l_error_msg_code);
480
481 if l_return_status <> 'S' then
482 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
483 p_msg_name => l_error_msg_code);
484 x_msg_data := l_error_msg_code;
485 x_return_status := 'E';
486 end if;
487 end if;
488
489
490 --dbms_output.put_line('Before check agreement org call ... ');
491 if (p_agreement_org_id is not null AND p_agreement_org_id <> FND_API.G_MISS_NUM) OR
492 (p_agreement_org_name is not null AND p_agreement_org_name <> FND_API.G_MISS_CHAR)
493 then
494 if (p_debug_mode = 'Y') then
495 pa_debug.debug('Create_Project PUB : Checking agreement org');
496 end if;
497
498 PA_PROJECTS_MAINT_UTILS.CHECK_AGREEMENT_ORG_NAME_OR_ID
499 ( p_agreement_org_id => p_agreement_org_id
500 ,p_agreement_org_name => p_agreement_org_name
501 ,p_check_id_flag => 'Y'
502 ,x_agreement_org_id => l_agreement_org_id
503 ,x_return_status => l_return_status
504 ,x_error_msg_code => l_error_msg_code);
505
506 if l_return_status <> 'S' then
507 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
508 p_msg_name => l_error_msg_code);
509 x_msg_data := l_error_msg_code;
510 x_return_status := 'E';
511 end if;
512 end if;
513
514
515 if (p_debug_mode = 'Y') then
516 pa_debug.debug('Create_Project PUB : checking message count');
517 end if;
518
519 l_msg_count := FND_MSG_PUB.count_msg;
520 If l_msg_count > 0 THEN
521 x_msg_count := l_msg_count;
522 If l_msg_count = 1 THEN
523 pa_interface_utils_pub.get_messages
524 (p_encoded => FND_API.G_TRUE ,
525 p_msg_index => 1,
526 p_msg_count => l_msg_count ,
527 p_msg_data => l_msg_data,
528 p_data => l_data,
529 p_msg_index_out => l_msg_index_out );
530 x_msg_data := l_data;
531 End if;
532 RAISE FND_API.G_EXC_ERROR;
533 End if;
534
535 /*
536 IF l_msg_count = 1 THEN
537 x_msg_count := l_msg_count;
538 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
539 p_encoded => FND_API.G_TRUE);
540 ELSE
541 x_msg_count := l_msg_count;
542 END IF;
543 if l_msg_count > 0 THEN
544 x_return_status := 'E';
545 RAISE FND_API.G_EXC_ERROR;
546 end if;
547 */
548
549 --dbms_output.put_line('Starts here PA_PROJECTS_MAINT_PVT.CREATE_PROJECT ... ');
550
551 /* For Bug 2731449 modified p_bill_to_customer_id to l_bill_to_customer_id
552 and p_ship_to_customer_id to l_ship_to_customer_id */
553
554 if (p_debug_mode = 'Y') then
555 pa_debug.debug('Create_Project PUB : Calling private api Create_project');
556 end if;
557
558 PA_PROJECTS_MAINT_PVT.CREATE_PROJECT
559 (
560 p_commit => FND_API.G_FALSE,
561 p_validate_only => p_validate_only,
562 p_validation_level => p_validation_level,
563 p_calling_module => p_calling_module,
564 p_debug_mode => p_debug_mode,
565 p_max_msg_count => p_max_msg_count,
566 p_orig_project_id => p_orig_project_id,
567 p_project_name => p_project_name,
568 p_project_number => p_project_number,
569 p_description => p_description,
570 p_project_type => p_project_type,
571 p_project_status_code => l_project_status_code,
572 p_distribution_rule => p_distribution_rule,
573 p_public_sector_flag => p_public_sector_flag,
574 p_carrying_out_organization_id => l_organization_id,
575 p_start_date => p_start_date,
576 p_completion_date => p_completion_date,
577 p_probability_member_id => p_probability_member_id,
578 p_project_value => p_project_value,
579 p_expected_approval_date => p_expected_approval_date,
580 p_team_template_id => l_team_template_id,
581 p_country_code => l_country_code,
582 p_region => p_region,
583 p_city => p_city,
584 p_customer_id => l_customer_id,
585 p_agreement_currency => l_agreement_currency,
586 p_agreement_amount => p_agreement_amount,
587 p_agreement_org_id => l_agreement_org_id,
588 p_opp_value_currency_code => l_opp_value_currency_code ,
589 p_priority_code => p_priority_code,
590 p_template_flag => p_template_flag,
591 p_security_level => p_security_level,
592 --Customer Account Relationship Changes
593 p_bill_to_customer_id => l_bill_to_customer_id, /* For Bug 2731449 */
594 p_ship_to_customer_id => l_ship_to_customer_id, /* For Bug 2731449 */
595 --Customer Account Relationship Changes
596 -- anlee
597 -- Project Long Name changes
598 p_long_name => p_long_name,
599 -- End of changes
600 p_project_id => l_project_id,
601 p_new_project_number => l_new_project_number,
602 x_return_status => l_return_status,
603 x_msg_count => l_msg_count,
604 x_msg_data => l_msg_data);
605
606 if (p_debug_mode = 'Y') then
607 pa_debug.debug('Create_Project PUB : checking message count');
608 end if;
609 --dbms_output.put_line('After PRIVATE API call ... '||l_return_status);
610
611 l_msg_count := FND_MSG_PUB.count_msg;
612 If l_msg_count > 0 THEN
613 x_msg_count := l_msg_count;
614 If l_msg_count = 1 THEN
615 pa_interface_utils_pub.get_messages
616 (p_encoded => FND_API.G_TRUE ,
617 p_msg_index => 1,
618 p_msg_count => l_msg_count ,
619 p_msg_data => l_msg_data,
620 p_data => l_data,
621 p_msg_index_out => l_msg_index_out );
622 x_msg_data := l_data;
623 End if;
624 RAISE FND_API.G_EXC_ERROR;
625 End if;
626 /*
627 l_msg_count := FND_MSG_PUB.count_msg;
628
629 IF l_msg_count = 1 THEN
630 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
631 p_encoded => FND_API.G_TRUE);
632 x_msg_count := l_msg_count;
633 ELSE
634 x_msg_count := l_msg_count;
635 END IF;
636
637 IF l_msg_count > 0 THEN
638 x_return_status := 'E';
639 RAISE FND_API.G_EXC_ERROR;
640 END IF;
641 */
642 p_project_id := l_project_id;
643 p_new_project_number := l_new_project_number;
644 x_return_status := 'S';
645
646 --dbms_output.put_line('Created ProjectID in PUBLIC API : '||to_char(l_project_id)||l_new_project_number);
647
648 IF FND_API.TO_BOOLEAN(P_COMMIT)
649 THEN
650 COMMIT WORK;
651 END IF;
652
653 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
654 IF p_commit = FND_API.G_TRUE THEN
655 ROLLBACK TO prm_create_project;
656 END IF;
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
659 p_procedure_name => 'CREATE_PROJECT',
660 p_error_text => SUBSTRB(SQLERRM,1,240));
661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
662
663 WHEN FND_API.G_EXC_ERROR THEN
664 IF p_commit = FND_API.G_TRUE THEN
665 ROLLBACK TO prm_create_project;
666 END IF;
667 x_return_status := 'E';
668
669 WHEN OTHERS THEN
670 IF p_commit = FND_API.G_TRUE THEN
671 ROLLBACK TO prm_create_project;
672 END IF;
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
675 p_procedure_name => 'CREATE_PROJECT',
676 p_error_text => SUBSTRB(SQLERRM,1,240));
677 raise;
678
679 END CREATE_PROJECT;
680
681 -- API name : create_customer
682 -- Type : Public
683 -- Pre-reqs : None.
684 -- Parameters :
685 -- p_api_version IN NUMBER Optional Default = 1.0
686 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
687 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
688 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
689 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
690 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
691 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
692 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
693 -- p_project_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
694 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
695 -- p_customer_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
696 -- p_relationship_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
697 -- x_return_status OUT VARCHAR2
698 -- x_msg_count OUT NUMBER
699 -- x_msg_data OUT VARCHAR2
700 --
701 -- History
702 --
703 -- 18-AUG-2000 -- Sakthi/William - Created.
704 --
705 --
706 PROCEDURE CREATE_CUSTOMER
707 (
708 p_api_version IN NUMBER := 1.0 ,
709 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
710 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
711 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
712 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
713 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
714 p_debug_mode IN VARCHAR2 := 'N' ,
715 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
716 p_project_id IN NUMBER := FND_API.G_MISS_NUM ,
717 p_customer_id IN NUMBER := FND_API.G_MISS_NUM ,
718 p_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
719 p_relationship_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
720 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
721 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
722 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
723 IS
724
725 l_api_name CONSTANT VARCHAR(30) := 'create_customer';
726 l_api_version CONSTANT NUMBER := 1.0;
727
728 -- 4363092 TCA changes, replaced RA views with HZ tables
729 /*
730 l_customer_id ra_customers.customer_id%TYPE;
731 */
732
733 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
734 -- 4363092 end
735
736 l_return_status VARCHAR2(1);
737 l_error_msg_code VARCHAR2(250);
738 l_err_code NUMBER;
739 l_err_stage VARCHAR2(250);
740 l_err_stack VARCHAR2(250);
741 l_msg_count NUMBER;
742 l_msg_data VARCHAR2(250);
743 l_msg_index_out NUMBER;
744 l_data VARCHAR2(250);
745
746 BEGIN
747
748 --dbms_output.put_line('INSIDE PROCEDURE create_customer call ... ');
749
750 IF p_commit = FND_API.G_TRUE
751 THEN
752 SAVEPOINT create_customer;
753 END IF;
754
755 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.CREATE_CUSTOMER');
756
757 if (p_debug_mode = 'Y') then
758 pa_debug.debug('Create_Customer PUB : checking API compatibility');
759 end if;
760 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
761 p_api_version,
762 l_api_name,
763 g_pkg_name)
764 THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 x_return_status := FND_API.G_RET_STS_SUCCESS;
769
770 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE))
771 THEN
772 FND_MSG_PUB.initialize;
773 END IF;
774
775 x_return_status := 'S';
776
777 --dbms_output.put_line('INSIDE PROCEDURE before p_customer_id check call ... ');
778
779 IF (p_customer_id is not null AND
780 P_customer_id <> FND_API.G_MISS_NUM ) OR
781 (p_customer_name is not null AND
782 p_customer_name <> FND_API.G_MISS_CHAR )
783 THEN
784 if (p_debug_mode = 'Y') then
785 pa_debug.debug('Create_Customer PUB : checking Customer code');
786 end if;
787 --dbms_output.put_line('Before check customer or id call ... ');
788 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
789 ( p_customer_id => p_customer_id
790 ,p_customer_name => p_customer_name
791 ,p_check_id_flag => 'A'
792 ,x_customer_id => l_customer_id
793 ,x_return_status => l_return_status
794 ,x_error_msg_code => l_error_msg_code);
795
796 IF l_return_status <> 'S'
797 THEN
798 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
799 p_msg_name => l_error_msg_code);
800 x_msg_data := l_error_msg_code;
801 x_return_status := 'E';
802 END IF;
803 -- ELSE
804 -- PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
805 -- p_msg_name => 'PA_PROJ_CUST_ID_NOT_PASSED');
806 -- x_msg_data := 'PA_PROJ_CUST_ID_NOT_PASSED';
807 END IF;
808
809 l_return_status := FND_API.G_MISS_CHAR;
810 l_error_msg_code := FND_API.G_MISS_CHAR;
811
812 if (p_debug_mode = 'Y') then
813 pa_debug.debug('Create_Customer PUB : calling Create_customer private API');
814 end if;
815
816 PA_PROJECTS_MAINT_PVT.CREATE_CUSTOMER
817 ( p_commit => FND_API.G_FALSE ,
818 p_validate_only => p_validate_only ,
819 p_validation_level => p_validation_level ,
820 p_debug_mode => p_debug_mode ,
821 p_max_msg_count => p_max_msg_count,
822 p_project_id => p_project_id ,
823 p_customer_id => l_customer_id ,
824 p_relationship_type => p_relationship_type,
825 x_return_status => l_return_status ,
826 x_msg_count => l_msg_count ,
827 x_msg_data => l_msg_data) ;
828
829 l_msg_count := FND_MSG_PUB.count_msg;
830 If l_msg_count > 0 THEN
831 x_msg_count := l_msg_count;
832 If l_msg_count = 1 THEN
833 pa_interface_utils_pub.get_messages
834 (p_encoded => FND_API.G_TRUE ,
835 p_msg_index => 1,
836 p_msg_count => l_msg_count ,
837 p_msg_data => l_msg_data,
838 p_data => l_data,
839 p_msg_index_out => l_msg_index_out );
840 x_msg_data := l_data;
841 End if;
842 RAISE FND_API.G_EXC_ERROR;
843 End if;
844 /*
845 l_msg_count := FND_MSG_PUB.count_msg;
846
847 IF l_msg_count = 1 THEN
848 x_msg_count := l_msg_count;
849 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
850 p_encoded => FND_API.G_TRUE);
851 ELSE
852 x_msg_count := l_msg_count;
853 END IF;
854
855 if (p_debug_mode = 'Y') then
856 pa_debug.debug('Create_Customer PUB : Checking Message Count');
857 end if;
858
859 IF l_msg_count > 0 THEN
860 x_return_status := 'E';
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863 */
864 IF FND_API.TO_BOOLEAN(P_COMMIT)
865 THEN
866 COMMIT WORK;
867 END IF;
868
869 --dbms_output.put_line('SUCCESSFULLY CREATED CUSTOMER ... ');
870
871 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 IF p_commit = FND_API.G_TRUE THEN
873 ROLLBACK TO create_customer;
874 END IF;
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
876 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
877 p_procedure_name => 'CREATE_CUSTOMER',
878 p_error_text => SUBSTRB(SQLERRM,1,240));
879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880
881 WHEN FND_API.G_EXC_ERROR THEN
882 IF p_commit = FND_API.G_TRUE THEN
883 ROLLBACK TO create_customer;
884 END IF;
885 x_return_status := 'E';
886
887 WHEN OTHERS THEN
888 IF p_commit = FND_API.G_TRUE THEN
889 ROLLBACK TO create_customer;
890 END IF;
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
892 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
893 p_procedure_name => 'CREATE_CUSTOMER',
894 p_error_text => SUBSTRB(SQLERRM,1,240));
895 raise;
896
897 END CREATE_CUSTOMER;
898
899 -- API name : Update_project_basic_info
900 -- Type : Public
901 -- Pre-reqs : None.
902 -- Parameters :
903 -- p_api_version IN NUMBER Optional Default = 1.0
904 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
905 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
906 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
907 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
908 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
909 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
910 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
911 -- p_project_id IN NUMBER Required
912 -- p_project_name IN VARCHAR2 Required
913 -- p_project_number IN VARCHAR2 Required
914 -- p_project_type IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
915 -- p_description IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
916 -- p_project_status_code IN VARCHAR2 Required
917 -- p_public_sector_flag IN VARCHAR2 Required
918 -- p_carrying_out_organization_id IN NUMBER Required Default = FND_API.G_MISS_NUM
919 -- p_organization_name IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
920 -- p_start_date IN DATE Required
921 -- p_completion_date IN DATE Optional Default = FND_API.G_MISS_DATE
922 -- p_territory_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
923 -- p_country IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
924 -- p_location_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
925 -- p_state_region IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
926 -- p_city IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
927 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
928 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
929 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
930 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
931 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
932 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
933 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
934 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
935 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
936 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
937 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
938 -- p_record_version_number IN NUMBER Required
939 -- p_recalculate_flag IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
940 -- p_target_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
941 -- p_target_finish_dateIN DATE Optional Default = FND_API.G_MISS_DATE
942 -- p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
943 -- p_long_name IN VARCHAR2 Optional Default = NULL
944 -- x_return_status OUT VARCHAR2 Required
945 -- x_msg_count OUT NUMBER Required
946 -- x_msg_data OUT VARCHAR2 Required
947 --
948 -- History
949 --
950 -- 18-AUG-2000 -- Sakthi/William - Created.
951 --
952 --
953 PROCEDURE UPDATE_PROJECT_BASIC_INFO
954 (
955 p_api_version IN NUMBER := 1.0 ,
956 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
957 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
958 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
959 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
960 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
961 p_debug_mode IN VARCHAR2 := 'N' ,
962 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
963 p_project_id IN NUMBER ,
964 p_project_name IN VARCHAR2 ,
965 p_project_number IN VARCHAR2 ,
966 p_project_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
967 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
968 p_project_status_code IN VARCHAR2 ,
969 p_public_sector_flag IN VARCHAR2 ,
970 p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
971 p_organization_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
972 p_start_date IN DATE ,
973 p_completion_date IN DATE := FND_API.G_MISS_DATE ,
974 p_territory_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
975 p_country IN VARCHAR2 := FND_API.G_MISS_CHAR ,
976 p_location_id IN NUMBER := FND_API.G_MISS_NUM ,
977 p_state_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
978 p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
979 p_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
980 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
981 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
982 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
983 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
984 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
985 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
986 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
987 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
988 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
989 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
990 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
991 p_record_version_number IN NUMBER ,
992 p_recalculate_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
993 -- anlee
994 -- Date changes
995 p_target_start_date IN DATE := FND_API.G_MISS_DATE ,
996 p_target_finish_date IN DATE := FND_API.G_MISS_DATE ,
997 -- End of changes
998 p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
999 -- anlee
1000 -- Project Long Name changes
1001 p_long_name IN VARCHAR2 DEFAULT NULL ,
1002 -- end of changes
1003 p_funding_approval_status IN VARCHAR2 DEFAULT NULL , -- added for 4055319
1004 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1005 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1006 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1007 IS
1008
1009 l_api_name CONSTANT VARCHAR(30) := 'update_project_basic_info';
1010 l_api_version CONSTANT NUMBER := 1.0;
1011 l_dummy VARCHAR2(1) := 'N';
1012
1013 l_carrying_out_organization_id NUMBER;
1014 l_msg_index_out NUMBER;
1015 l_msg_count NUMBER;
1016 l_return_status VARCHAR2(1);
1017 l_error_msg_code VARCHAR2(250);
1018 l_msg_data VARCHAR2(250);
1019 l_err_code VARCHAR2(250);
1020 l_err_stage VARCHAR2(250);
1021 l_err_stack VARCHAR2(250);
1022 l_data VARCHAR2(250);
1023
1024 BEGIN
1025
1026 --dbms_output.put_line('Starts here PA_PROJECTS_MAINT_PUB.CREATE_PROJECT ... ');
1027
1028 IF p_commit = FND_API.G_TRUE THEN
1029 SAVEPOINT update_project_basic_info;
1030 END IF;
1031
1032 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_basic_info');
1033
1034 --dbms_output.put_line('Starts here FND_API.COMPATIBLE_API_CALL ... ');
1035
1036 if (p_debug_mode = 'Y') then
1037 pa_debug.debug('Update_project_basic_info PUB : Checking API compatibility');
1038 end if;
1039
1040 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1041 p_api_version,
1042 l_api_name,
1043 g_pkg_name)
1044 THEN
1045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1046 END IF;
1047
1048 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1049 fnd_msg_pub.initialize;
1050 end if;
1051
1052 x_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054 -- Check original project id
1055
1056 --dbms_output.put_line('Check original project id ... ');
1057
1058 if (p_project_id is null) then
1059 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1060 p_msg_name => 'PA_NO_PROJECT_ID');
1061 x_msg_data := 'PA_NO_PROJECT_ID';
1062 x_return_status := 'E';
1063 --dbms_output.put_line('p_project_id is null RETURN STATUS : '||x_return_status);
1064 end if;
1065
1066 --dbms_output.put_line(' after project null');
1067 -- write program logic from here
1068
1069 --CARRYING_OUT_ORGANIZATION_ID
1070
1071 IF (p_carrying_out_organization_id is not null AND
1072 p_carrying_out_organization_id <> FND_API.G_MISS_NUM) OR
1073 (p_organization_name is not null AND
1074 p_organization_name <> FND_API.G_MISS_CHAR)
1075 THEN
1076 if (p_debug_mode = 'Y') then
1077 pa_debug.debug('Update_project_basic_info PUB : Checking Carrying out org');
1078 end if;
1079 --dbms_output.put_line(' before check_org name or_id');
1080 pa_hr_org_utils.Check_OrgName_Or_Id
1081 (p_organization_id => p_carrying_out_organization_id
1082 ,p_organization_name => p_organization_name
1083 ,p_check_id_flag => 'A'
1084 ,x_organization_id => l_carrying_out_organization_id
1085 ,x_return_status => l_return_status
1086 ,x_error_msg_code => l_error_msg_code);
1087 IF l_return_status = FND_API.G_RET_STS_ERROR
1088 THEN
1089 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1090 p_msg_name => l_error_msg_code);
1091 x_msg_data := l_error_msg_code;
1092 x_return_status := 'E';
1093 END IF;
1094
1095 END IF;
1096
1097 --dbms_output.put_line(' after check_org name or_id');
1098
1099 l_msg_count := FND_MSG_PUB.count_msg;
1100 If l_msg_count > 0 THEN
1101 x_msg_count := l_msg_count;
1102 If l_msg_count = 1 THEN
1103 pa_interface_utils_pub.get_messages
1104 (p_encoded => FND_API.G_TRUE ,
1105 p_msg_index => 1,
1106 p_msg_count => l_msg_count ,
1107 p_msg_data => l_msg_data,
1108 p_data => l_data,
1109 p_msg_index_out => l_msg_index_out );
1110 x_msg_data := l_data;
1111 End if;
1112 RAISE FND_API.G_EXC_ERROR;
1113 End if;
1114 /*
1115 l_msg_count := FND_MSG_PUB.count_msg;
1116
1117 IF l_msg_count = 1 THEN
1118 x_msg_count := l_msg_count;
1119 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
1120 p_encoded => FND_API.G_TRUE);
1121 ELSE
1122 x_msg_count := l_msg_count;
1123 END IF;
1124
1125 if (p_debug_mode = 'Y') then
1126 pa_debug.debug('Update_project_basic_info PUB : Checking message count');
1127 end if;
1128
1129 IF l_msg_count > 0 THEN
1130 x_return_status := 'E';
1131 RAISE FND_API.G_EXC_ERROR;
1132 END IF;
1133 */
1134 --dbms_output.put_line(' location **** '||to_char(p_location_id));
1135 if (p_debug_mode = 'Y') then
1136 pa_debug.debug('Update_project_basic_info PUB : Calling private API update_project_basic_info');
1137 end if;
1138 PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_BASIC_INFO
1139 (
1140 p_commit => FND_API.G_FALSE,
1141 p_validate_only => p_validate_only,
1142 p_validation_level => p_validation_level,
1143 p_calling_module => p_calling_module, -- added for bug 7204572
1144 p_debug_mode => p_debug_mode,
1145 p_max_msg_count => p_max_msg_count,
1146 p_project_id => p_project_id,
1147 p_project_name => p_project_name,
1148 p_project_number => p_project_number,
1149 p_project_type => p_project_type,
1150 p_description => p_description,
1151 p_project_status_code => p_project_status_code,
1152 p_public_sector_flag => p_public_sector_flag,
1153 p_carrying_out_organization_id => l_carrying_out_organization_id,
1154 p_start_date => p_start_date,
1155 p_completion_date => p_completion_date,
1156 p_territory_code => p_territory_code,
1157 p_country => p_country,
1158 p_location_id => p_location_id,
1159 p_state_region => p_state_region,
1160 p_city => p_city,
1161 p_priority_code => p_priority_code,
1162 p_attribute_category => p_attribute_category,
1163 p_attribute1 => p_attribute1,
1164 p_attribute2 => p_attribute2,
1165 p_attribute3 => p_attribute3,
1166 p_attribute4 => p_attribute4,
1167 p_attribute5 => p_attribute5,
1168 p_attribute6 => p_attribute6,
1169 p_attribute7 => p_attribute7,
1170 p_attribute8 => p_attribute8,
1171 p_attribute9 => p_attribute9,
1172 p_attribute10 => p_attribute10,
1173 p_record_version_number => p_record_version_number,
1174 p_recalculate_flag => p_recalculate_flag,
1175 -- anlee
1176 -- Dates changes
1177 p_target_start_date => p_target_start_date,
1178 p_target_finish_date => p_target_finish_date,
1179 -- End of changes
1180 p_security_level => p_security_level,
1181 -- anlee
1182 -- Project Long Name changes
1183 p_long_name => p_long_name,
1184 -- End of changes
1185 p_funding_approval_status => p_funding_approval_status, -- 4055319
1186 x_return_status => l_return_status,
1187 x_msg_count => l_msg_count,
1188 x_msg_data => l_msg_data);
1189
1190 if (p_debug_mode = 'Y') then
1191 pa_debug.debug('Update_project_basic_info PUB : Checking message count');
1192 end if;
1193 l_msg_count := FND_MSG_PUB.count_msg;
1194 If l_msg_count > 0 THEN
1195 x_msg_count := l_msg_count;
1196 If l_msg_count = 1 THEN
1197 pa_interface_utils_pub.get_messages
1198 (p_encoded => FND_API.G_TRUE ,
1199 p_msg_index => 1,
1200 p_msg_count => l_msg_count ,
1201 p_msg_data => l_msg_data,
1202 p_data => l_data,
1203 p_msg_index_out => l_msg_index_out );
1204 x_msg_data := l_data;
1205 End if;
1206 RAISE FND_API.G_EXC_ERROR;
1207 End if;
1208 /*
1209 l_msg_count := FND_MSG_PUB.count_msg;
1210
1211 IF l_msg_count = 1 THEN
1212 x_msg_count := l_msg_count;
1213 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
1214 p_encoded => FND_API.G_TRUE);
1215 ELSE
1216 x_msg_count := l_msg_count;
1217 END IF;
1218
1219 IF l_msg_count > 0 THEN
1220 x_return_status := 'E';
1221 RAISE FND_API.G_EXC_ERROR;
1222 END IF;
1223 */
1224 x_return_status := FND_API.G_RET_STS_SUCCESS;
1225
1226 if p_commit = FND_API.G_TRUE then
1227 commit work;
1228 end if;
1229
1230 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1231 IF p_commit = FND_API.G_TRUE THEN
1232 ROLLBACK TO update_project_basic_info;
1233 END IF;
1234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1236 p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
1237 p_error_text => SUBSTRB(SQLERRM,1,240));
1238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1239
1240 WHEN FND_API.G_EXC_ERROR THEN
1241 IF p_commit = FND_API.G_TRUE THEN
1242 ROLLBACK TO update_project_basic_info;
1243 END IF;
1244 x_return_status := 'E';
1245
1246 WHEN OTHERS THEN
1247 IF p_commit = FND_API.G_TRUE THEN
1248 ROLLBACK TO update_project_basic_info;
1249 END IF;
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1252 p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
1253 p_error_text => SUBSTRB(SQLERRM,1,240));
1254 raise;
1255
1256 END UPDATE_PROJECT_BASIC_INFO;
1257
1258 -- API name : Update_project_additional_info
1259 -- Type : Public
1260 -- Pre-reqs : None.
1261 -- Parameters :
1262 -- p_api_version IN NUMBER Optional Default = 1.0
1263 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1264 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1265 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
1266 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1267 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1268 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1269 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1270 -- p_project_id IN NUMBER Required
1271 -- p_calendar_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1272 -- p_calendar_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1273 -- p_work_type_id IN NUMBER Required
1274 -- p_role_list_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1275 -- p_cost_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1276 -- p_bill_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1277 -- p_record_version_number IN NUMBER Required
1278 -- p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1279 -- p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1280 -- x_return_status OUT VARCHAR2 Required
1281 -- x_msg_count OUT NUMBER Required
1282 -- x_msg_data OUT VARCHAR2 Required
1283 --
1284 -- History
1285 --
1286 -- 18-AUG-2000 -- Sakthi/William - Created.
1287 -- 01-MAr-2004 -- Rajnish - Added for FPM-Relationship changes
1288 --
1289 --
1290 PROCEDURE UPDATE_PROJECT_ADDITIONAL_INFO
1291 (p_api_version IN NUMBER := 1.0 ,
1292 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1293 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1294 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1295 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1296 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1297 p_debug_mode IN VARCHAR2 := 'N',
1298 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
1299 p_project_id IN NUMBER ,
1300 p_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
1301 p_calendar_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1302 p_work_type_id IN NUMBER ,
1303 p_role_list_id IN NUMBER := FND_API.G_MISS_NUM ,
1304 p_cost_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
1305 p_bill_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
1306 p_split_cost_from_wokplan_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1307 p_split_cost_from_bill_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1308 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1309 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1310 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1311 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1312 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1313 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1314 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1315 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1316 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1317 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1318 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1319 p_record_version_number IN NUMBER ,
1320 p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1321 p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1322 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1323 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1324 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1325 IS
1326
1327 l_api_name CONSTANT VARCHAR(30) := 'update_project_additional_info';
1328 l_api_version CONSTANT NUMBER := 1.0;
1329 l_calendar_id NUMBER;
1330 l_cost_job_group_id NUMBER;
1331 l_return_status VARCHAR2(250);
1332 l_error_msg_code VARCHAR2(250);
1333 l_msg_count NUMBER;
1334 l_msg_data VARCHAR2(250);
1335 l_err_code VARCHAR2(250);
1336 l_err_stage VARCHAR2(250);
1337 l_err_stack VARCHAR2(250);
1338 l_data VARCHAR2(250);
1339 l_msg_index_out NUMBER;
1340 l_dummy VARCHAR2(1);
1341 BEGIN
1342
1343 --dbms_output.put_line('ADDITIONAL INFORMATION .,.. ');
1344
1345 IF p_commit = FND_API.G_TRUE THEN
1346 SAVEPOINT update_project_additional_info;
1347 END IF;
1348
1349 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_additional_info');
1350
1351 if (p_debug_mode = 'Y') then
1352 pa_debug.debug('Update_project_Additional_info PUB : Checking API Compatibility');
1353 end if;
1354 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1355 p_api_version,
1356 l_api_name,
1357 g_pkg_name)
1358 THEN
1359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1360 END IF;
1361
1362 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1363 fnd_msg_pub.initialize;
1364 end if;
1365
1366 x_return_status := FND_API.G_RET_STS_SUCCESS;
1367
1368 --dbms_output.put_line('Before PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_ADDITIONAL_INFO ');
1369 -- write your program logic from here
1370
1371 IF (p_calendar_id is not null AND
1372 p_calendar_id <> FND_API.G_MISS_NUM) OR
1373 (p_calendar_name is not null AND
1374 p_calendar_name <> FND_API.G_MISS_CHAR)
1375 THEN
1376 if (p_debug_mode = 'Y') then
1377 pa_debug.debug('Update_Project_additional_info PUB : Checking calendar');
1378 end if;
1379 PA_CALENDAR_UTILS.CHECK_CALENDAR_NAME_OR_ID
1380 ( p_calendar_id => p_calendar_id
1381 ,p_calendar_name => p_calendar_name
1382 ,p_check_id_flag => 'A'
1383 ,x_calendar_id => l_calendar_id
1384 ,x_return_status => l_return_status
1385 ,x_error_message_code => l_error_msg_code);
1386
1387 --dbms_output.put_line('AFTER check org ... '||l_return_status);
1388
1389 IF l_return_status = FND_API.G_RET_STS_ERROR
1390 THEN
1391 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1392 p_msg_name => l_error_msg_code);
1393 x_msg_data := l_error_msg_code;
1394 x_return_status := 'E';
1395 END IF;
1396
1397 END IF;
1398
1399 --dbms_output.put_line('Before p_project_status_code ... ');
1400
1401 l_cost_job_group_id := p_cost_job_group_id;
1402 IF l_cost_job_Group_id is null
1403 THEN
1404 l_cost_job_Group_id := PA_PROJECTS_MAINT_UTILS.GET_COST_JOB_GROUP_ID;
1405 END IF;
1406
1407 l_msg_count := FND_MSG_PUB.count_msg;
1408 If l_msg_count > 0 THEN
1409 x_msg_count := l_msg_count;
1410 If l_msg_count = 1 THEN
1411 pa_interface_utils_pub.get_messages
1412 (p_encoded => FND_API.G_TRUE ,
1413 p_msg_index => 1,
1414 p_msg_count => l_msg_count ,
1415 p_msg_data => l_msg_data,
1416 p_data => l_data,
1417 p_msg_index_out => l_msg_index_out );
1418 x_msg_data := l_data;
1419 End if;
1420 RAISE FND_API.G_EXC_ERROR;
1421 End if;
1422 /*
1423 l_msg_count := FND_MSG_PUB.count_msg;
1424
1425 IF l_msg_count = 1 THEN
1426 x_msg_count := l_msg_count;
1427 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
1428 p_encoded => FND_API.G_TRUE);
1429
1430 ELSE
1431 x_msg_count := l_msg_count;
1432 END IF;
1433
1434 if l_msg_count > 0 THEN
1435 x_return_status := 'E';
1436 RAISE FND_API.G_EXC_ERROR;
1437 end if;
1438 */
1439 if (p_debug_mode = 'Y') then
1440 pa_debug.debug('Update_project_Additional_info PUB : Calling private API Update_project_additional_info ');
1441 end if;
1442
1443 --dbms_output.put_line('Starts here PA_PROJECTS_MAINT_PVT.CREATE_PROJECT ... ');
1444
1445 PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_ADDITIONAL_INFO
1446 (
1447 p_commit => FND_API.G_FALSE,
1448 p_validate_only => p_validate_only,
1449 p_validation_level => p_validation_level,
1450 p_calling_module => p_calling_module,
1451 p_debug_mode => p_debug_mode,
1452 p_max_msg_count => p_max_msg_count,
1453 p_project_id => p_project_id,
1454 p_calendar_id => l_calendar_id,
1455 p_work_type_id => p_work_type_id,
1456 p_role_list_id => p_role_list_id,
1457 p_cost_job_group_id => l_cost_job_group_id,
1458 p_bill_job_group_id => p_bill_job_group_id,
1459 p_split_cost_from_wokplan_flag => p_split_cost_from_wokplan_flag,
1460 p_split_cost_from_bill_flag => p_split_cost_from_bill_flag,
1461 p_attribute_category => p_attribute_category,
1462 p_attribute1 => p_attribute1,
1463 p_attribute2 => p_attribute2,
1464 p_attribute3 => p_attribute3,
1465 p_attribute4 => p_attribute4,
1466 p_attribute5 => p_attribute5,
1467 p_attribute6 => p_attribute6,
1468 p_attribute7 => p_attribute7,
1469 p_attribute8 => p_attribute8,
1470 p_attribute9 => p_attribute9,
1471 p_attribute10 => p_attribute10,
1472 p_record_version_number => p_record_version_number,
1473 p_sys_program_flag => p_sys_program_flag,
1474 p_allow_multi_prog_rollup => p_allow_multi_prog_rollup,
1475 x_return_status => l_return_status,
1476 x_msg_count => l_msg_count,
1477 x_msg_data => l_msg_data);
1478
1479 if (p_debug_mode = 'Y') then
1480 pa_debug.debug('Update_project_Additional_info PUB : Checking message count');
1481 end if;
1482 l_msg_count := FND_MSG_PUB.count_msg;
1483 If l_msg_count > 0 THEN
1484 x_msg_count := l_msg_count;
1485 If l_msg_count = 1 THEN
1486 pa_interface_utils_pub.get_messages
1487 (p_encoded => FND_API.G_TRUE ,
1488 p_msg_index => 1,
1489 p_msg_count => l_msg_count ,
1490 p_msg_data => l_msg_data,
1491 p_data => l_data,
1492 p_msg_index_out => l_msg_index_out );
1493 x_msg_data := l_data;
1494 End if;
1495 RAISE FND_API.G_EXC_ERROR;
1496 End if;
1497 /*
1498 l_msg_count := FND_MSG_PUB.count_msg;
1499
1500 IF l_msg_count = 1 THEN
1501 x_msg_count := l_msg_count;
1502 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
1503 p_encoded => FND_API.G_TRUE);
1504 ELSE
1505 x_msg_count := l_msg_count;
1506 END IF;
1507
1508 IF l_msg_count > 0 THEN
1509 x_return_status := 'E';
1510 RAISE FND_API.G_EXC_ERROR;
1511 END IF;
1512 */
1513 IF FND_API.TO_BOOLEAN(P_COMMIT)
1514 THEN
1515 COMMIT WORK;
1516 END IF;
1517
1518 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 IF p_commit = FND_API.G_TRUE THEN
1520 ROLLBACK TO update_project_additional_info;
1521 END IF;
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1524 p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
1525 p_error_text => SUBSTRB(SQLERRM,1,240));
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527
1528 WHEN FND_API.G_EXC_ERROR THEN
1529 IF p_commit = FND_API.G_TRUE THEN
1530 ROLLBACK TO update_project_additional_info;
1531 END IF;
1532 x_return_status := 'E';
1533
1534 WHEN OTHERS THEN
1535 -- dbms_output.put_line('additional info'||sqlerrm);
1536 IF p_commit = FND_API.G_TRUE THEN
1537 ROLLBACK TO update_project_additional_info;
1538 END IF;
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1541 p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
1542 p_error_text => SUBSTRB(SQLERRM,1,240));
1543 raise;
1544
1545 END UPDATE_PROJECT_ADDITIONAL_INFO;
1546
1547 -- API name : Update_project_pipeline_info
1548 -- Type : Public
1549 -- Pre-reqs : None.
1550 -- Parameters :
1551 -- p_api_version IN NUMBER Optional Default = 1.0
1552 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1553 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1554 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
1555 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1556 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1557 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1558 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1559 -- p_project_id IN NUMBER Required
1560 -- p_probability_member_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1561 -- p_probability_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
1562 -- p_project_value IN NUMBER Optional Default = FND_API.G_MISS_NUM
1563 -- p_opportunity_value IN NUMBER Optional Defautl = FND_API.G_MISS_NUM
1564 -- p_opp_value_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1565 -- p_expected_approval_date IN DATE Optional Default = NULL
1566 -- p_record_version_number IN NUMBER Required
1567 -- x_return_status OUT VARCHAR2 Required
1568 -- x_msg_count OUT NUMBER Required
1569 -- x_msg_data OUT VARCHAR2 Required
1570 --
1571 -- History
1572 --
1573 -- 18-AUG-2000 -- Sakthi/William - Created.
1574 --
1575 --
1576 PROCEDURE UPDATE_PROJECT_PIPELINE_INFO
1577 (p_api_version IN NUMBER := 1.0 ,
1578 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1579 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1580 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1581 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1582 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1583 p_debug_mode IN VARCHAR2 := 'N',
1584 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
1585 p_project_id IN NUMBER ,
1586 p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
1587 p_probability_percentage IN NUMBER := FND_API.G_MISS_NUM ,
1588 p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
1589 p_opportunity_value IN NUMBER := FND_API.G_MISS_NUM ,
1590 p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1591 p_expected_approval_date IN DATE := NULL ,
1592 p_record_version_number IN NUMBER ,
1593 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1594 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1595 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1596 IS
1597
1598 l_api_name CONSTANT VARCHAR(30) := 'update_project_pipeline_info';
1599 l_api_version CONSTANT NUMBER := 1.0;
1600
1601 l_return_status VARCHAR2(250);
1602 l_error_msg_code VARCHAR2(250);
1603 l_msg_count NUMBER;
1604 l_msg_data VARCHAR2(250);
1605 l_err_code VARCHAR2(250);
1606 l_err_stage VARCHAR2(250);
1607 l_err_stack VARCHAR2(250);
1608 l_data VARCHAR2(250);
1609 l_msg_index_out NUMBER;
1610 l_dummy VARCHAR2(1);
1611 l_probability_member_id pa_probability_members.probability_member_id%TYPE;
1612 l_project_type pa_projects_all.project_type%TYPE;
1613 l_expected_approval_date DATE;
1614 l_opportunity_value NUMBER;
1615 l_opp_value_currency_code pa_project_opp_attrs.opp_value_currency_code%TYPE;
1616
1617 CURSOR c1
1618 IS
1619 Select pr.project_type, pr.probability_member_id, pr.expected_approval_date,
1620 op.opportunity_value, op.opp_value_currency_code
1621 From pa_projects_all pr, pa_project_opp_attrs op
1622 Where pr.project_id = op.project_id
1623 AND pr.project_id = p_project_id;
1624
1625 v_c1 c1%ROWTYPE;
1626
1627 BEGIN
1628
1629 IF p_commit = FND_API.G_TRUE THEN
1630 SAVEPOINT update_project_pipeline_info;
1631 END IF;
1632
1633 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_pipeline_info');
1634
1635 -- Start: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1636 if (p_debug_mode = 'Y') then
1637 PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
1638 ,x_msg => 'Beginning of update_project_pipeline_info'
1639 ,x_log_level => 3);
1640 end if;
1641 -- End: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1642
1643 if (p_debug_mode = 'Y') then
1644 pa_debug.debug('Update_project_pipeline_info PUB : Checking API Compatibility');
1645 end if;
1646 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1647 p_api_version,
1648 l_api_name,
1649 g_pkg_name)
1650 THEN
1651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1652 END IF;
1653
1654 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1655 fnd_msg_pub.initialize;
1656 end if;
1657
1658 x_return_status := FND_API.G_RET_STS_SUCCESS;
1659
1660 OPEN c1;
1661 FETCH c1 INTO v_c1;
1662 CLOSE c1;
1663
1664 -- Assign the local variables with the parameters passed in.
1665 l_probability_member_id := p_probability_member_id;
1666 l_opportunity_value := p_opportunity_value;
1667 l_opp_value_currency_code := p_opp_value_currency_code;
1668 l_expected_approval_date := p_expected_approval_date;
1669
1670 -- 2375403: Assign correct value to l_project_type.
1671 l_project_type := v_c1.project_type;
1672
1673 IF (p_probability_member_id is not null AND
1674 P_probability_member_id <> FND_API.G_MISS_NUM ) OR
1675 (p_probability_percentage is not null AND
1676 p_probability_percentage <> FND_API.G_MISS_NUM)
1677 THEN
1678 if (p_debug_mode = 'Y') then
1679 pa_debug.debug('Create_Project PUB : Checking Probability code');
1680 end if;
1681 PA_PROJECTS_MAINT_UTILS.CHECK_PROBABILITY_CODE_OR_ID
1682 ( p_probability_member_id => p_probability_member_id
1683 ,p_probability_percentage => p_probability_percentage
1684 ,p_project_type => l_project_type
1685 ,p_check_id_flag => 'A'
1686 ,x_probability_member_id => l_probability_member_id
1687 ,x_return_status => l_return_status
1688 ,x_error_msg_code => l_error_msg_code);
1689
1690 -- Start: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1691 if (p_debug_mode = 'Y') then
1692 PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
1693 ,x_msg => 'AFTER check probablity ... '||l_return_status
1694 ,x_log_level => 3);
1695 end if;
1696 -- End: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1697
1698 IF l_return_status = FND_API.G_RET_STS_ERROR
1699 THEN
1700 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1701 p_msg_name => l_error_msg_code);
1702 x_msg_data := l_error_msg_code;
1703 x_return_status := 'E';
1704 END IF;
1705 END IF;
1706
1707 if (p_debug_mode = 'Y') then
1708 pa_debug.debug('Update_project_pipeline_info PUB : Checking message count');
1709 end if;
1710 l_msg_count := FND_MSG_PUB.count_msg;
1711 If l_msg_count > 0 THEN
1712 x_msg_count := l_msg_count;
1713 If l_msg_count = 1 THEN
1714 pa_interface_utils_pub.get_messages
1715 (p_encoded => FND_API.G_TRUE ,
1716 p_msg_index => 1,
1717 p_msg_count => l_msg_count ,
1718 p_msg_data => l_msg_data,
1719 p_data => l_data,
1720 p_msg_index_out => l_msg_index_out );
1721 x_msg_data := l_data;
1722 End if;
1723 RAISE FND_API.G_EXC_ERROR;
1724 End if;
1725
1726 -- write your program logic from here
1727
1728 if (p_debug_mode = 'Y') then
1729 pa_debug.debug('Update_project_pipeline_info PUB : Calling private API update_project_pipeline_info');
1730 end if;
1731
1732 -- Start: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1733 if (p_debug_mode = 'Y') then
1734 PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
1735 ,x_msg => 'Before calling PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO'
1736 ,x_log_level => 3);
1737 end if;
1738 -- End: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1739
1740 PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO (
1741 p_commit => FND_API.G_FALSE,
1742 p_validate_only => p_validate_only,
1743 p_validation_level => p_validation_level,
1744 p_calling_module => p_calling_module,
1745 p_debug_mode => p_debug_mode,
1746 p_max_msg_count => p_max_msg_count,
1747 p_project_id => p_project_id,
1748 p_probability_member_id => l_probability_member_id,
1749 p_project_value => p_project_value,
1750 p_expected_approval_date => l_expected_approval_date,
1751 p_record_version_number => p_record_version_number,
1752 x_return_status => l_return_status,
1753 x_msg_count => l_msg_count,
1754 x_msg_data => l_msg_data);
1755
1756 -- Start: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1757 if (p_debug_mode = 'Y') then
1758 PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
1759 ,x_msg => 'After calling PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO'
1760 ,x_log_level => 3);
1761 end if;
1762 -- End: Bug 4469624 - check for p_debug_mode before call to PA_DEBUG.WRITE
1763
1764
1765 -- Call PA_OPPORTUNITY_MGT_PVT.modify_project_attributes to update and convert
1766 -- opportunity values.
1767 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1768 PA_OPPORTUNITY_MGT_PVT.modify_project_attributes (
1769 p_project_id => p_project_id,
1770 p_opportunity_value => l_opportunity_value,
1771 p_opp_value_currency_code=> l_opp_value_currency_code,
1772 p_expected_approval_date => l_expected_approval_date,
1773 p_update_project => 'Y',
1774 x_return_status => l_return_status,
1775 x_msg_count => l_msg_count,
1776 x_msg_data => l_msg_data);
1777 END IF;
1778
1779 if (p_debug_mode = 'Y') then
1780 pa_debug.debug('Update_project_pipeline_info PUB : Checking message count');
1781 end if;
1782 l_msg_count := FND_MSG_PUB.count_msg;
1783 If l_msg_count > 0 THEN
1784 x_msg_count := l_msg_count;
1785 If l_msg_count = 1 THEN
1786 pa_interface_utils_pub.get_messages
1787 (p_encoded => FND_API.G_TRUE ,
1788 p_msg_index => 1,
1789 p_msg_count => l_msg_count ,
1790 p_msg_data => l_msg_data,
1791 p_data => l_data,
1792 p_msg_index_out => l_msg_index_out );
1793 x_msg_data := l_data;
1794 End if;
1795 RAISE FND_API.G_EXC_ERROR;
1796 End if;
1797 /*
1798 l_msg_count := FND_MSG_PUB.count_msg;
1799
1800 IF l_msg_count = 1 THEN
1801 x_msg_count := l_msg_count;
1802 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
1803 p_encoded => FND_API.G_TRUE);
1804
1805 ELSE
1806 x_msg_count := l_msg_count;
1807 END IF;
1808
1809 IF l_msg_count > 0 THEN
1810 x_return_status := 'E';
1811 RAISE FND_API.G_EXC_ERROR;
1812 END IF;
1813 */
1814
1815 IF FND_API.TO_BOOLEAN(P_COMMIT)
1816 THEN
1817 COMMIT WORK;
1818 END IF;
1819
1820 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1821 IF p_commit = FND_API.G_TRUE THEN
1822 ROLLBACK TO update_project_pipeline_info;
1823 END IF;
1824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1825 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1826 p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
1827 p_error_text => SUBSTRB(SQLERRM,1,240));
1828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1829
1830 WHEN FND_API.G_EXC_ERROR THEN
1831 IF p_commit = FND_API.G_TRUE THEN
1832 ROLLBACK TO update_project_pipeline_info;
1833 END IF;
1834 x_return_status := 'E';
1835
1836 WHEN OTHERS THEN
1837 IF p_commit = FND_API.G_TRUE THEN
1838 ROLLBACK TO update_project_pipeline_info;
1839 END IF;
1840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1841 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1842 p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
1843 p_error_text => SUBSTRB(SQLERRM,1,240));
1844 raise;
1845
1846 END UPDATE_PROJECT_PIPELINE_INFO;
1847
1848 -- API name : Create_classifications
1849 -- Type : Public
1850 -- Pre-reqs : None.
1851 -- Parameters :
1852 -- p_api_version IN NUMBER Optional Default = 1.0
1853 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1854 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1855 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
1856 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1857 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1858 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1859 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1860 -- p_object_id IN NUMBER Required
1861 -- p_object_type IN VARCHAR2 Required
1862 -- p_class_category IN VARCHAR2 Required
1863 -- p_class_code IN VARCHAR2 Required
1864 -- p_code_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
1865 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1866 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1867 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1868 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1869 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1870 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1871 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1872 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1873 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1874 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1875 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1876 -- p_attribute11 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1877 -- p_attribute12 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1878 -- p_attribute13 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1879 -- p_attribute14 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1880 -- p_attribute15 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1881 -- x_return_status OUT VARCHAR2 Required
1882 -- x_msg_count OUT NUMBER Required
1883 -- x_msg_data OUT VARCHAR2 Required
1884 --
1885 -- History
1886 --
1887 -- 18-AUG-2000 -- Sakthi/William - Created.
1888 --
1889 --
1890 PROCEDURE CREATE_CLASSIFICATIONS
1891 (p_api_version IN NUMBER := 1.0 ,
1892 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1893 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1894 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1895 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1896 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1897 p_debug_mode IN VARCHAR2 := 'N',
1898 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
1899 p_object_id IN NUMBER,
1900 p_object_type IN VARCHAR2,
1901 p_class_category IN VARCHAR2 ,
1902 p_class_code IN VARCHAR2 ,
1903 p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
1904 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1905 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1906 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1907 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1908 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1909 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1910 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1911 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1912 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1913 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1914 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1915 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1916 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1917 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1918 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1919 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1920 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1921 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1922 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1923 IS
1924
1925 l_dummy VARCHAR2(1);
1926 l_created_from_project_id pa_projects.created_from_project_id%TYPE;
1927 l_api_name CONSTANT VARCHAR(30) := 'create_classifications';
1928 l_api_version CONSTANT NUMBER := 1.0;
1929 l_project_id pa_projects.project_id%TYPE;
1930 l_row_id VARCHAR2(250);
1931 l_return_status VARCHAR2(250);
1932 l_error_msg_code VARCHAR2(250);
1933 l_msg_count NUMBER;
1934 l_msg_data VARCHAR2(250);
1935 l_err_code VARCHAR2(250);
1936 l_err_stage VARCHAR2(250);
1937 l_err_stack VARCHAR2(250);
1938 l_data VARCHAR2(250);
1939 l_msg_index_out NUMBER;
1940 BEGIN
1941
1942 IF p_commit = FND_API.G_TRUE THEN
1943 SAVEPOINT create_classifications;
1944 END IF;
1945
1946 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.create_classifications');
1947
1948 if (p_debug_mode = 'Y') then
1949 pa_debug.debug('Create_classifications PUB : Checking API compatibility');
1950 end if;
1951 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1952 p_api_version,
1953 l_api_name,
1954 g_pkg_name)
1955 THEN
1956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1957 END IF;
1958
1959 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1960 fnd_msg_pub.initialize;
1961 end if;
1962
1963 x_return_status := FND_API.G_RET_STS_SUCCESS;
1964
1965 -- write your program logic here
1966
1967 if (p_debug_mode = 'Y') then
1968 pa_debug.debug('Create_classifications PUB : Calling Private API Create_classifications');
1969 end if;
1970
1971 -- Checking Input Parameters
1972
1973 if (p_debug_mode = 'Y') then
1974 pa_debug.debug('Create_classifications PUB: Checking Class Cateogory ');
1975 end if;
1976
1977 BEGIN
1978
1979 SELECT 'x' INTO l_dummy
1980 FROM pa_class_categories
1981 WHERE CLASS_CATEGORY = p_class_category;
1982
1983 EXCEPTION WHEN NO_DATA_FOUND THEN
1984 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1985 p_msg_name => 'PA_AL_INVALID_CLASS_CATG_PROJE');
1986 x_msg_data := 'PA_AL_INVALID_CLASS_CATG_PROJE';
1987 x_return_status := 'E' ;
1988 WHEN OTHERS THEN
1989 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1990 p_procedure_name => 'CREATE_CLASSIFICATIONS',
1991 p_error_text => SUBSTRB(SQLERRM,1,240));
1992 raise;
1993 END;
1994
1995 if (p_debug_mode = 'Y') then
1996 pa_debug.debug('Create_classifications PUB: Checking Class Category / Codes Pair');
1997 end if;
1998
1999 -- Validate that the Class category / code pair is valid
2000 -- Bug 14150391 - Added condition for END DATED CLASS CODES starts here
2001 BEGIN
2002
2003 SELECT 'x' INTO l_dummy
2004 FROM pa_class_codes
2005 WHERE CLASS_CODE = p_class_code and
2006 CLASS_CATEGORY = p_class_category and
2007 trunc(sysdate) between trunc(start_date_active) and nvl(trunc(end_date_active), trunc(sysdate));
2008
2009 EXCEPTION WHEN NO_DATA_FOUND THEN
2010 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2011 p_msg_name => 'PA_CLASS_CATG_CODE_INVALID');
2012 x_msg_data := 'PA_CLASS_CATG_CODE_INVALID';
2013 x_return_status := 'E' ;
2014 WHEN OTHERS THEN
2015 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2016 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2017 p_error_text => SUBSTRB(SQLERRM,1,240));
2018 raise;
2019 END;
2020 -- Bug 14150391 - Added condition for END DATED CLASS CODES Ends here
2021 l_msg_count := FND_MSG_PUB.count_msg;
2022 If l_msg_count > 0 THEN
2023 x_msg_count := l_msg_count;
2024 If l_msg_count = 1 THEN
2025 pa_interface_utils_pub.get_messages
2026 (p_encoded => FND_API.G_TRUE ,
2027 p_msg_index => 1,
2028 p_msg_count => l_msg_count ,
2029 p_msg_data => l_msg_data,
2030 p_data => l_data,
2031 p_msg_index_out => l_msg_index_out );
2032 x_msg_data := l_data;
2033 End if;
2034 RAISE FND_API.G_EXC_ERROR;
2035 End if;
2036
2037 /*
2038 l_msg_count := FND_MSG_PUB.count_msg;
2039
2040 IF l_msg_count = 1 THEN
2041 x_msg_count := l_msg_count;
2042 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2043 p_encoded => FND_API.G_TRUE);
2044
2045 ELSE
2046 x_msg_count := l_msg_count;
2047 END IF;
2048
2049 if l_msg_count > 0 THEN
2050 x_return_status := 'E';
2051 RAISE FND_API.G_EXC_ERROR;
2052 end if;
2053 */
2054 PA_PROJECTS_MAINT_PVT.CREATE_CLASSIFICATIONS
2055 (
2056 p_commit => FND_API.G_FALSE,
2057 p_validate_only => p_validate_only,
2058 p_validation_level => p_validation_level,
2059 p_calling_module => p_calling_module,
2060 p_debug_mode => p_debug_mode,
2061 p_max_msg_count => p_max_msg_count,
2062 p_object_id => p_object_id,
2063 p_object_type => p_object_type,
2064 p_class_category => p_class_category,
2065 p_class_code => p_class_code,
2066 p_code_percentage => p_code_percentage,
2067 p_attribute_category => p_attribute_category,
2068 p_attribute1 => p_attribute1,
2069 p_attribute2 => p_attribute2,
2070 p_attribute3 => p_attribute3,
2071 p_attribute4 => p_attribute4,
2072 p_attribute5 => p_attribute5,
2073 p_attribute6 => p_attribute6,
2074 p_attribute7 => p_attribute7,
2075 p_attribute8 => p_attribute8,
2076 p_attribute9 => p_attribute9,
2077 p_attribute10 => p_attribute10,
2078 p_attribute11 => p_attribute11,
2079 p_attribute12 => p_attribute12,
2080 p_attribute13 => p_attribute13,
2081 p_attribute14 => p_attribute14,
2082 p_attribute15 => p_attribute15,
2083 x_return_status => l_return_status,
2084 x_msg_count => l_msg_count,
2085 x_msg_data => l_msg_data);
2086
2087 l_msg_count := FND_MSG_PUB.count_msg;
2088 If l_msg_count > 0 THEN
2089 x_msg_count := l_msg_count;
2090 If l_msg_count = 1 THEN
2091 pa_interface_utils_pub.get_messages
2092 (p_encoded => FND_API.G_TRUE ,
2093 p_msg_index => 1,
2094 p_msg_count => l_msg_count ,
2095 p_msg_data => l_msg_data,
2096 p_data => l_data,
2097 p_msg_index_out => l_msg_index_out );
2098 x_msg_data := l_data;
2099 End if;
2100 RAISE FND_API.G_EXC_ERROR;
2101 End if;
2102 /*
2103 l_msg_count := FND_MSG_PUB.count_msg;
2104
2105 IF l_msg_count = 1 THEN
2106 x_msg_count := l_msg_count;
2107 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2108 p_encoded => FND_API.G_TRUE);
2109 ELSE
2110 x_msg_count := l_msg_count;
2111 END IF;
2112
2113 --dbms_output.put_line('Before PA_PROJECTS_MAINT_PVT.CREATE_CLASSIFICATIONS '||to_char(l_msg_count));
2114
2115 if (p_debug_mode = 'Y') then
2116 pa_debug.debug('Create_classifications PUB : Checking message count');
2117 end if;
2118
2119 --dbms_output.put_line('Before PA_PROJECTS_MAINT_PVT. '||l_data);
2120
2121 IF l_msg_count > 0 THEN
2122 x_return_status := 'E';
2123 RAISE FND_API.G_EXC_ERROR;
2124 END IF;
2125 */
2126 IF FND_API.TO_BOOLEAN(P_COMMIT)
2127 THEN
2128 COMMIT WORK;
2129 END IF;
2130
2131 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2132 IF p_commit = FND_API.G_TRUE THEN
2133 ROLLBACK TO create_classifications;
2134 END IF;
2135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2136 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2137 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2138 p_error_text => SUBSTRB(SQLERRM,1,240));
2139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2140
2141 WHEN FND_API.G_EXC_ERROR THEN
2142 IF p_commit = FND_API.G_TRUE THEN
2143 ROLLBACK TO create_classifications;
2144 END IF;
2145 x_return_status := 'E';
2146
2147 WHEN OTHERS THEN
2148 IF p_commit = FND_API.G_TRUE THEN
2149 ROLLBACK TO CREATE_CLASSIFICATIONS;
2150 END IF;
2151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2152 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2153 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2154 p_error_text => SUBSTRB(SQLERRM,1,240));
2155 raise;
2156
2157 END CREATE_CLASSIFICATIONS;
2158
2159
2160 -- API name : Update_classifications
2161 -- Type : Public
2162 -- Pre-reqs : None.
2163 -- Parameters :
2164 -- p_api_version IN NUMBER Optional Default = 1.0
2165 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2166 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2167 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2168 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2169 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2170 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2171 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2172 -- p_object_id IN NUMBER Required
2173 -- p_object_type IN VARCHAR2 Required
2174 -- p_class_category IN VARCHAR2 Required
2175 -- p_class_code IN VARCHAR2 Required
2176 -- p_code_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
2177 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2178 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2179 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2180 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2181 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2182 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2183 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2184 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2185 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2186 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2187 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2188 -- p_attribute11 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2189 -- p_attribute12 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2190 -- p_attribute13 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2191 -- p_attribute14 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2192 -- p_attribute15 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2193 -- p_rowid IN VARCHAR2
2194 -- p_record_version_number IN NUMBER
2195 -- x_return_status OUT VARCHAR2 Required
2196 -- x_msg_count OUT NUMBER Required
2197 -- x_msg_data OUT VARCHAR2 Required
2198 --
2199 -- History
2200 --
2201 -- 12-OCT-2001 -- anlee created
2202 --
2203 --
2204 PROCEDURE UPDATE_CLASSIFICATIONS
2205 (p_api_version IN NUMBER := 1.0 ,
2206 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2207 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2208 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2209 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2210 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2211 p_debug_mode IN VARCHAR2 := 'N',
2212 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2213 p_object_id IN NUMBER,
2214 p_object_type IN VARCHAR2,
2215 p_class_category IN VARCHAR2 ,
2216 p_class_code IN VARCHAR2 ,
2217 p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
2218 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2219 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2220 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2221 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2222 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2223 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2224 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2225 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2226 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2227 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2228 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2229 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2230 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2231 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2232 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2233 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2234 p_rowid IN VARCHAR2 ,
2235 p_record_version_number IN NUMBER ,
2236 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2237 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2238 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2239 IS
2240
2241 l_dummy VARCHAR2(1);
2242 l_created_from_project_id pa_projects.created_from_project_id%TYPE;
2243 l_api_name CONSTANT VARCHAR(30) := 'create_classifications';
2244 l_api_version CONSTANT NUMBER := 1.0;
2245 l_project_id pa_projects.project_id%TYPE;
2246 l_row_id VARCHAR2(250);
2247 l_return_status VARCHAR2(250);
2248 l_error_msg_code VARCHAR2(250);
2249 l_msg_count NUMBER;
2250 l_msg_data VARCHAR2(250);
2251 l_err_code VARCHAR2(250);
2252 l_err_stage VARCHAR2(250);
2253 l_err_stack VARCHAR2(250);
2254 l_data VARCHAR2(250);
2255 l_msg_index_out NUMBER;
2256 BEGIN
2257
2258 IF p_commit = FND_API.G_TRUE THEN
2259 SAVEPOINT update_classifications;
2260 END IF;
2261
2262 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_classifications');
2263
2264 if (p_debug_mode = 'Y') then
2265 pa_debug.debug('Update_classifications PUB : Checking API compatibility');
2266 end if;
2267 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2268 p_api_version,
2269 l_api_name,
2270 g_pkg_name)
2271 THEN
2272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2273 END IF;
2274
2275 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2276 fnd_msg_pub.initialize;
2277 end if;
2278
2279 x_return_status := FND_API.G_RET_STS_SUCCESS;
2280
2281 -- write your program logic here
2282
2283 if (p_debug_mode = 'Y') then
2284 pa_debug.debug('Update_classifications PUB : Calling Private API Update_classifications');
2285 end if;
2286
2287 -- Checking Input Parameters
2288
2289 if (p_debug_mode = 'Y') then
2290 pa_debug.debug('Update_classifications PUB: Checking Class Cateogory ');
2291 end if;
2292
2293 BEGIN
2294
2295 SELECT 'x' INTO l_dummy
2296 FROM pa_class_categories
2297 WHERE CLASS_CATEGORY = p_class_category;
2298
2299 EXCEPTION WHEN NO_DATA_FOUND THEN
2300 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2301 p_msg_name => 'PA_AL_INVALID_CLASS_CATG_PROJE');
2302 x_msg_data := 'PA_AL_INVALID_CLASS_CATG_PROJE';
2303 x_return_status := 'E' ;
2304 WHEN OTHERS THEN
2305 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2306 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2307 p_error_text => SUBSTRB(SQLERRM,1,240));
2308 raise;
2309 END;
2310
2311 if (p_debug_mode = 'Y') then
2312 pa_debug.debug('Update_classifications PUB: Checking Class Category / Codes Pair');
2313 end if;
2314
2315 -- Validate that the Class category / code pair is valid
2316 -- Bug 14150391 - Added condition for END DATED CLASS CODES starts here
2317 BEGIN
2318
2319 SELECT 'x' INTO l_dummy
2320 FROM pa_class_codes
2321 WHERE CLASS_CODE = p_class_code and
2322 CLASS_CATEGORY = p_class_category and
2323 trunc(sysdate) between trunc(start_date_active) and nvl(trunc(end_date_active), trunc(sysdate));
2324
2325 EXCEPTION WHEN NO_DATA_FOUND THEN
2326 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2327 p_msg_name => 'PA_CLASS_CATG_CODE_INVALID');
2328 x_msg_data := 'PA_CLASS_CATG_CODE_INVALID';
2329 x_return_status := 'E' ;
2330 WHEN OTHERS THEN
2331 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2332 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2333 p_error_text => SUBSTRB(SQLERRM,1,240));
2334 raise;
2335 END;
2336 -- Bug 14150391 - Added condition for END DATED CLASS CODES Ends here
2337 l_msg_count := FND_MSG_PUB.count_msg;
2338 If l_msg_count > 0 THEN
2339 x_msg_count := l_msg_count;
2340 If l_msg_count = 1 THEN
2341 pa_interface_utils_pub.get_messages
2342 (p_encoded => FND_API.G_TRUE ,
2343 p_msg_index => 1,
2344 p_msg_count => l_msg_count ,
2345 p_msg_data => l_msg_data,
2346 p_data => l_data,
2347 p_msg_index_out => l_msg_index_out );
2348 x_msg_data := l_data;
2349 End if;
2350 RAISE FND_API.G_EXC_ERROR;
2351 End if;
2352
2353
2354 PA_PROJECTS_MAINT_PVT.UPDATE_CLASSIFICATIONS
2355 (
2356 p_commit => FND_API.G_FALSE,
2357 p_validate_only => p_validate_only,
2358 p_validation_level => p_validation_level,
2359 p_calling_module => p_calling_module,
2360 p_debug_mode => p_debug_mode,
2361 p_max_msg_count => p_max_msg_count,
2362 p_object_id => p_object_id,
2363 p_object_type => p_object_type,
2364 p_class_category => p_class_category,
2365 p_class_code => p_class_code,
2366 p_code_percentage => p_code_percentage,
2367 p_attribute_category => p_attribute_category,
2368 p_attribute1 => p_attribute1,
2369 p_attribute2 => p_attribute2,
2370 p_attribute3 => p_attribute3,
2371 p_attribute4 => p_attribute4,
2372 p_attribute5 => p_attribute5,
2373 p_attribute6 => p_attribute6,
2374 p_attribute7 => p_attribute7,
2375 p_attribute8 => p_attribute8,
2376 p_attribute9 => p_attribute9,
2377 p_attribute10 => p_attribute10,
2378 p_attribute11 => p_attribute11,
2379 p_attribute12 => p_attribute12,
2380 p_attribute13 => p_attribute13,
2381 p_attribute14 => p_attribute14,
2382 p_attribute15 => p_attribute15,
2383 p_rowid => p_rowid,
2384 p_record_version_number => p_record_version_number,
2385 x_return_status => l_return_status,
2386 x_msg_count => l_msg_count,
2387 x_msg_data => l_msg_data);
2388
2389 l_msg_count := FND_MSG_PUB.count_msg;
2390 If l_msg_count > 0 THEN
2391 x_msg_count := l_msg_count;
2392 If l_msg_count = 1 THEN
2393 pa_interface_utils_pub.get_messages
2394 (p_encoded => FND_API.G_TRUE ,
2395 p_msg_index => 1,
2396 p_msg_count => l_msg_count ,
2397 p_msg_data => l_msg_data,
2398 p_data => l_data,
2399 p_msg_index_out => l_msg_index_out );
2400 x_msg_data := l_data;
2401 End if;
2402 RAISE FND_API.G_EXC_ERROR;
2403 End if;
2404
2405 IF FND_API.TO_BOOLEAN(P_COMMIT)
2406 THEN
2407 COMMIT WORK;
2408 END IF;
2409
2410 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2411 IF p_commit = FND_API.G_TRUE THEN
2412 ROLLBACK TO update_classifications;
2413 END IF;
2414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2415 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2416 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2417 p_error_text => SUBSTRB(SQLERRM,1,240));
2418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2419
2420 WHEN FND_API.G_EXC_ERROR THEN
2421 IF p_commit = FND_API.G_TRUE THEN
2422 ROLLBACK TO update_classifications;
2423 END IF;
2424 x_return_status := 'E';
2425
2426 WHEN OTHERS THEN
2427 IF p_commit = FND_API.G_TRUE THEN
2428 ROLLBACK TO UPDATE_CLASSIFICATIONS;
2429 END IF;
2430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2431 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2432 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2433 p_error_text => SUBSTRB(SQLERRM,1,240));
2434 raise;
2435
2436 END UPDATE_CLASSIFICATIONS;
2437
2438
2439
2440 -- API name : delete_classifications
2441 -- Type : Public
2442 -- Pre-reqs : None.
2443 -- Parameters :
2444 -- p_api_version IN NUMBER Optional Default = 1.0
2445 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2446 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2447 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2448 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2449 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2450 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2451 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2452 -- p_object_id IN NUMBER Required
2453 -- p_object_type IN VARCHAR2 Required
2454 -- p_class_category IN VARCHAR2 Required
2455 -- p_class_code IN VARCHAR2 Required
2456 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
2457 -- x_return_status OUT VARCHAR2 Required
2458 -- x_msg_count OUT NUMBER Required
2459 -- x_msg_data OUT VARCHAR2 Required
2460 --
2461 -- History
2462 --
2463 -- 18-AUG-2000 -- Sakthi/William - Created.
2464 --
2465 --
2466 PROCEDURE DELETE_CLASSIFICATIONS
2467 (p_api_version IN NUMBER := 1.0 ,
2468 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2469 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2470 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2471 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2472 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2473 p_debug_mode IN VARCHAR2 := 'N',
2474 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2475 p_object_id IN NUMBER ,
2476 p_object_type IN VARCHAR2 ,
2477 p_class_category IN VARCHAR2 ,
2478 p_class_code IN VARCHAR2 ,
2479 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM ,
2480 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2481 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2482 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2483 IS
2484 l_api_name CONSTANT VARCHAR(30) := 'delete_classifications';
2485 l_api_version CONSTANT NUMBER := 1.0;
2486 l_project_id pa_projects.project_id%TYPE;
2487 l_dummy VARCHAR2(1);
2488 l_delete_flag VARCHAR2(1) := 'N';
2489 l_return_status VARCHAR2(250);
2490 l_error_msg_code VARCHAR2(250);
2491 l_msg_count NUMBER;
2492 l_msg_data VARCHAR2(250);
2493 l_err_code VARCHAR2(250);
2494 l_err_stage VARCHAR2(250);
2495 l_err_stack VARCHAR2(250);
2496 l_data VARCHAR2(250);
2497 l_msg_index_out NUMBER;
2498 l_row_id VARCHAR2(250);
2499 BEGIN
2500
2501 -- Please all debug message into this procedure.
2502
2503 IF p_commit = FND_API.G_TRUE THEN
2504 SAVEPOINT delete_classifications;
2505 END IF;
2506
2507 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.delete_classifications');
2508
2509 if (p_debug_mode = 'Y') then
2510 pa_debug.debug('delete_classifications PUB : Checking API Compatibility');
2511 end if;
2512 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2513 p_api_version,
2514 l_api_name,
2515 g_pkg_name)
2516 THEN
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519
2520 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2521 fnd_msg_pub.initialize;
2522 end if;
2523
2524 x_return_status := FND_API.G_RET_STS_SUCCESS;
2525
2526 -- write program logic here
2527
2528 if (p_debug_mode = 'Y') then
2529 pa_debug.debug('delete_classifications PUB : Calling private API delete_classifcations');
2530 end if;
2531 PA_PROJECTS_MAINT_PVT.DELETE_CLASSIFICATIONS
2532 (
2533 p_commit => FND_API.G_FALSE,
2534 p_validate_only => p_validate_only,
2535 p_validation_level => p_validation_level,
2536 p_calling_module => p_calling_module,
2537 p_debug_mode => p_debug_mode,
2538 p_max_msg_count => p_max_msg_count,
2539 p_object_id => p_object_id,
2540 p_object_type => p_object_type,
2541 p_class_category => p_class_category,
2542 p_class_code => p_class_code,
2543 p_record_version_number => p_record_version_number,
2544 x_return_status => l_return_status,
2545 x_msg_count => l_msg_count,
2546 x_msg_data => l_msg_data);
2547
2548 if (p_debug_mode = 'Y') then
2549 pa_debug.debug('delete_classifications PUB : Checking message count');
2550 end if;
2551 l_msg_count := FND_MSG_PUB.count_msg;
2552 If l_msg_count > 0 THEN
2553 x_msg_count := l_msg_count;
2554 If l_msg_count = 1 THEN
2555 pa_interface_utils_pub.get_messages
2556 (p_encoded => FND_API.G_TRUE ,
2557 p_msg_index => 1,
2558 p_msg_count => l_msg_count ,
2559 p_msg_data => l_msg_data,
2560 p_data => l_data,
2561 p_msg_index_out => l_msg_index_out );
2562 x_msg_data := l_data;
2563 End if;
2564 RAISE FND_API.G_EXC_ERROR;
2565 End if;
2566 /*
2567 l_msg_count := FND_MSG_PUB.count_msg;
2568
2569 IF l_msg_count = 1 THEN
2570 x_msg_count := l_msg_count;
2571 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2572 p_encoded => FND_API.G_TRUE);
2573 ELSE
2574 x_msg_count := l_msg_count;
2575 END IF;
2576
2577 IF l_msg_count > 0 THEN
2578 x_return_status := 'E';
2579 RAISE FND_API.G_EXC_ERROR;
2580 END IF;
2581 */
2582 IF FND_API.TO_BOOLEAN(P_COMMIT)
2583 THEN
2584 COMMIT WORK;
2585 END IF;
2586
2587 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2588 IF p_commit = FND_API.G_TRUE THEN
2589 ROLLBACK TO delete_classifications;
2590 END IF;
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2593 p_procedure_name => 'DELETE_CLASSIFICATIONS',
2594 p_error_text => SUBSTRB(SQLERRM,1,240));
2595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2596
2597 WHEN FND_API.G_EXC_ERROR THEN
2598 IF p_commit = FND_API.G_TRUE THEN
2599 ROLLBACK TO delete_classifications;
2600 END IF;
2601 x_return_status := 'E';
2602
2603 WHEN OTHERS THEN
2604 IF p_commit = FND_API.G_TRUE THEN
2605 ROLLBACK TO delete_classifications;
2606 END IF;
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2609 p_procedure_name => 'DELETE_CLASSIFICATIONS',
2610 p_error_text => SUBSTRB(SQLERRM,1,240));
2611 raise;
2612
2613 END DELETE_CLASSIFICATIONS;
2614
2615
2616 -- API name : Update_project_staffing_info
2617 -- Type : Public
2618 -- Pre-reqs : None.
2619 -- Parameters :
2620 -- p_api_version IN NUMBER Optional Default = 1.0
2621 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2622 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2623 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2624 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2625 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2626 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2627 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2628 -- p_project_id IN NUMBER Required
2629 -- p_comp_match_weighting IN pa_projects_all.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
2630 -- p_avail_match_weighting IN pa_projects_all.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
2631 -- p_job_level_match_weighting IN pa_projects_all.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
2632 -- p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM
2633 -- p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR
2634 -- p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2635 -- p_search_exp_org_struct_ver_id IN pa_projects_all.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
2636 -- p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
2637 -- p_search_exp_start_org_id IN pa_projects_all.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
2638 -- p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2639 -- p_search_min_candidate_score IN pa_projects_all.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
2640 -- p_enable_auto_cand_nom_flag IN pa_projects_all.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
2641 -- p_record_version_number IN NUMBER Required
2642 -- x_return_status OUT VARCHAR2 Required
2643 -- x_msg_count OUT NUMBER Required
2644 -- x_msg_data OUT VARCHAR2 Required
2645 --
2646 -- History
2647 --
2648 -- 28-SEP-2001 -- hyau - Created.
2649 --
2650 --
2651 PROCEDURE UPDATE_PROJECT_STAFFING_INFO
2652 (p_api_version IN NUMBER := 1.0 ,
2653 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2654 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2655 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2656 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2657 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2658 p_debug_mode IN VARCHAR2 := 'N' ,
2659 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2660 p_project_id IN NUMBER ,
2661 p_comp_match_weighting IN pa_projects_all.COMPETENCE_MATCH_WT%TYPE := FND_API.G_MISS_NUM,
2662 p_avail_match_weighting IN pa_projects_all.availability_match_wt%TYPE := FND_API.G_MISS_NUM,
2663 p_job_level_match_weighting IN pa_projects_all.job_level_match_wt%TYPE := FND_API.G_MISS_NUM,
2664 p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM,
2665 p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR,
2666 p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR,
2667 p_search_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE := FND_API.G_MISS_NUM,
2668 p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR,
2669 p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE := FND_API.G_MISS_NUM,
2670 p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR,
2671 p_search_min_candidate_score IN pa_projects_all.min_cand_score_reqd_for_nom%TYPE := FND_API.G_MISS_NUM,
2672 p_enable_auto_cand_nom_flag IN pa_projects_all.enable_automated_search%TYPE := FND_API.G_MISS_CHAR,
2673 p_record_version_number IN NUMBER ,
2674 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2675 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2676 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2677 IS
2678
2679
2680 l_api_name CONSTANT VARCHAR(30) := 'update_project_staffing_info';
2681 l_api_version CONSTANT NUMBER := 1.0;
2682
2683 l_return_status VARCHAR2(250);
2684 l_error_message_code fnd_new_messages.message_name%TYPE;
2685 l_msg_count NUMBER;
2686 l_msg_data VARCHAR2(250);
2687 l_err_code VARCHAR2(250);
2688 l_err_stage VARCHAR2(250);
2689 l_err_stack VARCHAR2(250);
2690 l_data VARCHAR2(250);
2691 l_msg_index_out NUMBER;
2692 l_dummy VARCHAR2(1);
2693
2694 l_search_exp_org_struct_ver_id pa_projects_all.search_org_hier_id%TYPE;
2695 l_search_country_code pa_projects_all.search_country_code%TYPE;
2696 l_search_exp_start_org_id pa_projects_all.search_starting_org_id%TYPE;
2697
2698 BEGIN
2699
2700 IF p_commit = FND_API.G_TRUE THEN
2701 SAVEPOINT update_project_staffing_info;
2702 END IF;
2703
2704 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_staffing_info');
2705
2706 if (p_debug_mode = 'Y') then
2707 pa_debug.debug('Update_project_staffing_info PUB : Checking API Compatibility');
2708 end if;
2709 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2710 p_api_version,
2711 l_api_name,
2712 g_pkg_name)
2713 THEN
2714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2715 END IF;
2716
2717 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2718 fnd_msg_pub.initialize;
2719 end if;
2720
2721 x_return_status := FND_API.G_RET_STS_SUCCESS;
2722
2723 --
2724 -- Validate Candidate Score Match Weightings
2725 --
2726 IF (p_comp_match_weighting <> FND_API.G_MISS_NUM AND p_comp_match_weighting IS NOT NULL) OR
2727 (p_avail_match_weighting <> FND_API.G_MISS_NUM AND p_avail_match_weighting IS NOT NULL) OR
2728 (p_job_level_match_weighting <> FND_API.G_MISS_NUM AND p_job_level_match_weighting IS NOT NULL) THEN
2729
2730 IF p_comp_match_weighting < 0 OR
2731 p_comp_match_weighting > 100 OR
2732 p_avail_match_weighting < 0 OR
2733 p_avail_match_weighting > 100 OR
2734 p_job_level_match_weighting < 0 OR
2735 p_job_level_match_weighting > 100 THEN
2736
2737 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2738 ,p_msg_name => 'PA_INVALID_MATCH_WEIGHTING');
2739 x_msg_data := 'PA_INVALID_MATCH_WEIGHTING';
2740 x_return_status := FND_API.G_RET_STS_ERROR;
2741
2742 END IF;
2743
2744 END IF;
2745
2746 --
2747 -- Validate Search Minimum Availiability
2748 --
2749 IF p_search_min_availability <> FND_API.G_MISS_NUM AND p_search_min_availability IS NOT NULL THEN
2750
2751 IF p_search_min_availability < 0 OR
2752 p_search_min_availability > 100 THEN
2753
2754 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2755 ,p_msg_name => 'PA_MIN_AVAIL_INVALID');
2756 x_msg_data := 'PA_MIN_AVAIL_INVALID';
2757 x_return_status := FND_API.G_RET_STS_ERROR;
2758 END IF;
2759 END IF;
2760
2761
2762
2763 --
2764 -- Validate Search Min Candidate Score
2765 --
2766 IF p_search_min_candidate_score <> FND_API.G_MISS_NUM AND p_search_min_candidate_score IS NOT NULL THEN
2767
2768 IF p_search_min_candidate_score < 0 OR
2769 p_search_min_candidate_score > 100 THEN
2770
2771 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2772 ,p_msg_name => 'PA_MIN_CAN_SCORE_INVALID');
2773 x_msg_data := 'PA_MIN_CAN_SCORE_INVALID';
2774 x_return_status := FND_API.G_RET_STS_ERROR;
2775 END IF;
2776 END IF;
2777
2778
2779 l_msg_count := FND_MSG_PUB.count_msg;
2780 If l_msg_count > 0 THEN
2781 x_msg_count := l_msg_count;
2782 If l_msg_count = 1 THEN
2783 pa_interface_utils_pub.get_messages
2784 (p_encoded => FND_API.G_TRUE ,
2785 p_msg_index => 1,
2786 p_msg_count => l_msg_count ,
2787 p_msg_data => l_msg_data,
2788 p_data => l_data,
2789 p_msg_index_out => l_msg_index_out );
2790 x_msg_data := l_data;
2791 End if;
2792 RAISE FND_API.G_EXC_ERROR;
2793 End if;
2794
2795
2796 --
2797 -- Validate Search Org_Hierarchy
2798 --
2799 IF (p_search_exp_org_struct_ver_id <> FND_API.G_MISS_NUM AND p_search_exp_org_struct_ver_id IS NOT NULL) OR (p_search_exp_org_hier_name <> FND_API.G_MISS_CHAR AND p_search_exp_org_hier_name IS NOT NULL ) THEN
2800
2801 PA_HR_ORG_UTILS.Check_OrgHierName_Or_Id (p_org_hierarchy_version_id => p_search_exp_org_struct_ver_id,
2802 p_org_hierarchy_name => p_search_exp_org_hier_name,
2803 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2804 x_org_hierarchy_version_id => l_search_exp_org_struct_ver_id,
2805 x_return_status => l_return_status,
2806 x_error_msg_code => l_error_message_code);
2807
2808
2809 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2810 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2811 ,p_msg_name => l_error_message_code );
2812 x_msg_data := l_error_message_code;
2813 x_return_status := FND_API.G_RET_STS_ERROR;
2814 ELSE
2815
2816 l_return_status := FND_API.G_MISS_CHAR;
2817 l_error_message_code := FND_API.G_MISS_CHAR;
2818
2819 -- check if the org hierarchy is of the correct type, i.e EXPENDITURES
2820 PA_ORG_UTILS.Check_OrgHierarchy_Type(
2821 p_org_structure_version_id => l_search_exp_org_struct_ver_id,
2822 p_org_structure_type => 'EXPENDITURES',
2823 x_return_status => l_return_status,
2824 x_error_message_code => l_error_message_code);
2825
2826
2827 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2828 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2829 ,p_msg_name => l_error_message_code );
2830
2831 END IF;
2832
2833 END IF;
2834 l_return_status := FND_API.G_MISS_CHAR;
2835 l_error_message_code := FND_API.G_MISS_CHAR;
2836
2837
2838
2839 END IF;
2840
2841 --
2842 -- Validate Search Start Org
2843 --
2844 IF (p_search_exp_start_org_id <> FND_API.G_MISS_NUM AND p_search_exp_start_org_id IS NOT NULL) OR (p_search_exp_start_org_name <> FND_API.G_MISS_CHAR and p_search_exp_start_org_name IS NOT NULL) THEN
2845 PA_HR_ORG_UTILS.Check_OrgName_Or_Id (p_organization_id => p_search_exp_start_org_id,
2846 p_organization_name => p_search_exp_start_org_name,
2847 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2848 x_organization_id => l_search_exp_start_org_id,
2849 x_return_status => l_return_status,
2850 x_error_msg_code => l_error_message_code);
2851
2852
2853 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2854 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2855 ,p_msg_name => l_error_message_code );
2856 x_msg_data := l_error_message_code;
2857 x_return_status := FND_API.G_RET_STS_ERROR;
2858
2859 ELSE
2860
2861 l_return_status := FND_API.G_MISS_CHAR;
2862 l_error_message_code := FND_API.G_MISS_CHAR;
2863
2864 -- check if the starting org is of the correct type i.e.EXPENDITURES
2865 PA_ORG_UTILS.Check_Org_Type(
2866 p_organization_id => l_search_exp_start_org_id,
2867 p_org_structure_type => 'EXPENDITURES',
2868 x_return_status => l_return_status,
2869 x_error_message_code => l_error_message_code);
2870
2871 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2872 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2873 ,p_msg_name => l_error_message_code );
2874 x_msg_data := l_error_message_code;
2875 x_return_status := FND_API.G_RET_STS_ERROR;
2876 END IF;
2877
2878 END IF;
2879 l_return_status := FND_API.G_MISS_CHAR;
2880 l_error_message_code := FND_API.G_MISS_CHAR;
2881
2882 END IF;
2883
2884 --
2885 -- Validate if Search Start Org is in the Search Org Hierarchy
2886 --
2887 IF (p_search_exp_start_org_id <> FND_API.G_MISS_NUM AND p_search_exp_start_org_id IS NOT NULL) OR (p_search_exp_start_org_name <> FND_API.G_MISS_CHAR and p_search_exp_start_org_name IS NOT NULL) THEN
2888 IF p_search_exp_start_org_id IS NOT NULL AND p_search_exp_org_struct_ver_id IS NOT NULL THEN
2889
2890 PA_ORG_UTILS.Check_Org_In_OrgHierarchy(
2891 p_organization_id => l_search_exp_start_org_id,
2892 p_org_structure_version_id => l_search_exp_org_struct_ver_id,
2893 p_org_structure_type => 'EXPENDITURES',
2894 x_return_status => l_return_status,
2895 x_error_message_code => l_error_message_code);
2896
2897 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2898 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2899 ,p_msg_name => l_error_message_code );
2900 x_msg_data := l_error_message_code;
2901 x_return_status := FND_API.G_RET_STS_ERROR;
2902
2903 END IF;
2904
2905 END IF;
2906
2907 END IF;
2908
2909 --
2910 -- Validate Search Country
2911 --
2912 IF (p_search_country_code <> FND_API.G_MISS_CHAR AND p_search_country_code IS NOT NULL) OR (p_search_country_name <> FND_API.G_MISS_CHAR AND p_search_country_name IS NOT NULL) THEN
2913 PA_LOCATION_UTILS.Check_Country_Name_Or_Code(p_country_code => p_search_country_code,
2914 p_country_name => p_search_country_name,
2915 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2916 x_country_code => l_search_country_code,
2917 x_return_status => l_return_status,
2918 x_error_message_code => l_error_message_code);
2919
2920 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2921 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2922 ,p_msg_name => l_error_message_code );
2923 x_msg_data := l_error_message_code;
2924 x_return_status := FND_API.G_RET_STS_ERROR;
2925 END IF;
2926 END IF;
2927
2928 l_msg_count := FND_MSG_PUB.count_msg;
2929 If l_msg_count > 0 THEN
2930 x_msg_count := l_msg_count;
2931 If l_msg_count = 1 THEN
2932 pa_interface_utils_pub.get_messages
2933 (p_encoded => FND_API.G_TRUE ,
2934 p_msg_index => 1,
2935 p_msg_count => l_msg_count ,
2936 p_msg_data => l_msg_data,
2937 p_data => l_data,
2938 p_msg_index_out => l_msg_index_out );
2939 x_msg_data := l_data;
2940 End if;
2941 RAISE FND_API.G_EXC_ERROR;
2942 End if;
2943
2944
2945 if (p_debug_mode = 'Y') then
2946 pa_debug.debug('update_project_staffing_info PUB : Calling private API update_project_staffing_info');
2947 end if;
2948
2949
2950 PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_STAFFING_INFO
2951 (
2952 p_commit => FND_API.G_FALSE,
2953 p_validate_only => p_validate_only,
2954 p_validation_level => p_validation_level,
2955 p_calling_module => p_calling_module,
2956 p_debug_mode => p_debug_mode,
2957 p_max_msg_count => p_max_msg_count,
2958 p_project_id => p_project_id,
2959 p_comp_match_weighting => p_comp_match_weighting,
2960 p_avail_match_weighting => p_avail_match_weighting,
2961 p_job_level_match_weighting => p_job_level_match_weighting,
2962 p_search_min_availability => p_search_min_availability,
2963 p_search_country_code => l_search_country_code,
2964 p_search_exp_org_struct_ver_id => l_search_exp_org_struct_ver_id,
2965 p_search_exp_start_org_id => l_search_exp_start_org_id,
2966 p_search_min_candidate_score => p_search_min_candidate_score,
2967 p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag,
2968 p_record_version_number => p_record_version_number,
2969 x_return_status => l_return_status,
2970 x_msg_count => l_msg_count,
2971 x_msg_data => l_msg_data);
2972
2973 if (p_debug_mode = 'Y') then
2974 pa_debug.debug('update_project_staffing_info PUB : Checking message count');
2975 end if;
2976 l_msg_count := FND_MSG_PUB.count_msg;
2977 If l_msg_count > 0 THEN
2978 x_msg_count := l_msg_count;
2979 If l_msg_count = 1 THEN
2980 pa_interface_utils_pub.get_messages
2981 (p_encoded => FND_API.G_TRUE ,
2982 p_msg_index => 1,
2983 p_msg_count => l_msg_count ,
2984 p_msg_data => l_msg_data,
2985 p_data => l_data,
2986 p_msg_index_out => l_msg_index_out );
2987 x_msg_data := l_data;
2988 End if;
2989 RAISE FND_API.G_EXC_ERROR;
2990 End if;
2991
2992 IF FND_API.TO_BOOLEAN(P_COMMIT)
2993 THEN
2994 COMMIT WORK;
2995 END IF;
2996
2997 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2998 IF p_commit = FND_API.G_TRUE THEN
2999 ROLLBACK TO update_project_staffing_info;
3000 END IF;
3001 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3002 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
3003 p_error_text => SUBSTRB(SQLERRM,1,240));
3004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3005
3006 WHEN FND_API.G_EXC_ERROR THEN
3007 IF p_commit = FND_API.G_TRUE THEN
3008 ROLLBACK TO update_project_staffing_info;
3009 END IF;
3010 x_return_status := FND_API.G_RET_STS_ERROR;
3011
3012 WHEN OTHERS THEN
3013 IF p_commit = FND_API.G_TRUE THEN
3014 ROLLBACK TO update_project_staffing_info;
3015 END IF;
3016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3017 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3018 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
3019 p_error_text => SUBSTRB(SQLERRM,1,240));
3020 raise;
3021
3022
3023 END UPDATE_PROJECT_STAFFING_INFO;
3024
3025
3026 -- API name : Create_Org_Role
3027 -- Type : Public
3028 -- Pre-reqs : None.
3029 -- Parameters :
3030 -- p_api_version IN NUMBER Required Default = 1.0
3031 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
3032 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
3033 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
3034 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3035 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
3036 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
3037 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
3038 -- p_object_id IN NUMBER Required
3039 -- p_object_type IN VARCHAR2 Required
3040 -- p_project_role_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
3041 -- p_project_role_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3042 -- p_resource_type_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3043 -- p_resource_source_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3044 -- p_resource_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
3045 -- p_start_date_active IN DATE Required
3046 -- p_scheduled_flag IN VARCHAR2 Optional Default = 'N'
3047 -- p_project_id IN NUMBER Required
3048 -- p_project_end_date IN DATE Optional Default = FND_API.G_MISS_DATE
3049 -- p_end_date_active IN OUT DATE Required
3050 -- x_return_status OUT VARCHAR2 Required
3051 -- x_msg_count OUT NUMBER Required
3052 -- x_msg_data OUT VARCHAR2 Required
3053
3054 PROCEDURE CREATE_ORG_ROLE
3055 ( p_api_version IN NUMBER := 1.0
3056 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3057 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3058 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3059 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3060 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3061 ,p_debug_mode IN VARCHAR2 := 'N'
3062 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
3063 ,p_object_id IN NUMBER
3064 ,p_object_type IN VARCHAR2
3065 ,p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR
3066 ,p_project_role_id IN NUMBER := FND_API.G_MISS_NUM
3067 ,p_resource_type_id IN NUMBER := FND_API.G_MISS_NUM
3068 ,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
3069 ,p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR
3070 ,p_start_date_active IN DATE
3071 ,p_scheduled_flag IN VARCHAR2 := 'N'
3072 ,p_project_id IN NUMBER
3073 ,p_project_end_date IN DATE := FND_API.G_MISS_DATE
3074 ,p_end_date_active IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
3075 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3076 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3077 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3078 )
3079 IS
3080 l_api_name CONSTANT VARCHAR(30) := 'Create_Org_Role';
3081 l_api_version CONSTANT NUMBER := 1.0;
3082
3083 l_customer_id NUMBER;
3084 l_record_version_number NUMBER;
3085 l_project_party_id NUMBER;
3086 l_start_date_active DATE;
3087 l_resource_source_id NUMBER;
3088 l_resource_type_id NUMBER;
3089 l_new_project_party_id NUMBER;
3090 l_resource_id NUMBER;
3091 l_wf_type VARCHAR2(2000);
3092 l_wf_item_type VARCHAR2(2000);
3093 l_wf_process VARCHAR2(2000);
3094 l_assignment_id NUMBER;
3095 l_return_status VARCHAR2(1);
3096 l_error_message_code VARCHAR2(250);
3097 l_msg_count NUMBER;
3098 l_msg_data VARCHAR2(250);
3099 l_data VARCHAR2(250);
3100 l_msg_index_out NUMBER;
3101 l_role_class VARCHAR2(30);
3102 l_project_role_id NUMBER;
3103
3104 CURSOR l_check_existing_customers_csr(c_party_id NUMBER)
3105 IS
3106 SELECT ppc.customer_id, ppp.record_version_number, ppc.project_party_id
3107 FROM PA_PROJECT_CUSTOMERS ppc,
3108 PA_PROJECT_PARTIES ppp
3109 WHERE ppc.project_id = p_project_id
3110 AND ppc.project_party_id = ppp.project_party_id
3111 AND ppp.resource_source_id = c_party_id
3112 AND ppp.resource_type_id = 112;
3113
3114 CURSOR l_get_role_class_csr(c_project_role_id NUMBER)
3115 IS
3116 SELECT role_party_class
3117 FROM pa_project_role_types_b /* Bug#2788814-Changed pa_project_role_types_vl to pa_project_role_types_b */
3118 WHERE project_role_id = c_project_role_id;
3119
3120 BEGIN
3121 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.Create_Org_Role');
3122
3123 if (p_debug_mode = 'Y') then
3124 pa_debug.debug('PA_PROJECTS_MAINT_PUB.Create_Org_Role BEGIN');
3125 end if;
3126
3127 if p_commit = FND_API.G_TRUE then
3128 savepoint create_org_role;
3129 end if;
3130
3131 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3132 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3133 end if;
3134
3135 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
3136 FND_MSG_PUB.initialize;
3137 end if;
3138
3139 if (p_debug_mode = 'Y') then
3140 pa_debug.debug('Check if the role to be created is in CUSTOMER role class...');
3141 end if;
3142
3143 l_role_class := NULL;
3144
3145 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
3146 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
3147 else
3148 l_project_role_id := p_project_role_id;
3149 end if;
3150
3151 OPEN l_get_role_class_csr(l_project_role_id);
3152 FETCH l_get_role_class_csr INTO l_role_class;
3153 CLOSE l_get_role_class_csr;
3154
3155 if l_role_class = 'CUSTOMER' then
3156
3157 if (p_debug_mode = 'Y') then
3158 pa_debug.debug('Name to ID conversion for the party...');
3159 end if;
3160
3161 if p_start_date_active is null or p_start_date_active = FND_API.G_MISS_DATE then
3162 l_start_date_active := trunc(sysdate);
3163 else
3164 l_start_date_active := p_start_date_active;
3165 end if;
3166
3167 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id ( p_resource_id => p_resource_source_id
3168 ,p_resource_type_id => p_resource_type_id
3169 ,p_resource_name => p_resource_name
3170 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
3171 ,p_date => l_start_date_active
3172 ,x_resource_id => l_resource_source_id
3173 ,x_resource_type_id => l_resource_type_id
3174 ,x_return_status => l_return_status
3175 ,x_error_message_code => l_error_message_code);
3176
3177 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3178 fnd_message.set_name('PA',l_error_message_code);
3179 fnd_msg_pub.add;
3180 end if;
3181
3182 l_msg_count := FND_MSG_PUB.count_msg;
3183 if l_msg_count > 0 then
3184 x_msg_count := l_msg_count;
3185 if x_msg_count = 1 then
3186 pa_interface_utils_pub.get_messages
3187 (p_encoded => FND_API.G_TRUE,
3188 p_msg_index => 1,
3189 p_msg_count => l_msg_count,
3190 p_msg_data => l_msg_data,
3191 p_data => l_data,
3192 p_msg_index_out => l_msg_index_out);
3193 x_msg_data := l_data;
3194 end if;
3195 raise FND_API.G_EXC_ERROR;
3196 end if;
3197
3198 if (p_debug_mode = 'Y') then
3199 pa_debug.debug('Check if there is an existing customer for this org...');
3200 end if;
3201
3202 l_customer_id := null;
3203 OPEN l_check_existing_customers_csr(l_resource_source_id);
3204 FETCH l_check_existing_customers_csr INTO l_customer_id, l_record_version_number, l_project_party_id;
3205 CLOSE l_check_existing_customers_csr;
3206
3207 if l_customer_id is not null then
3208
3209 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
3210 -- Update the project_party_id col in pa_project_customers to null
3211 UPDATE pa_project_customers
3212 SET project_party_id = null
3213 WHERE project_id = p_project_id
3214 AND customer_id = l_customer_id;
3215 end if;
3216
3217 -- Delete the existing role
3218 PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY
3219 ( p_init_msg_list => p_init_msg_list
3220 ,p_validate_only => p_validate_only
3221 ,p_validation_level => p_validation_level
3222 ,p_debug_mode => p_debug_mode
3223 ,p_calling_module => p_calling_module
3224 ,p_record_version_number => l_record_version_number
3225 ,p_project_id => p_project_id
3226 ,p_project_party_id => l_project_party_id
3227 ,x_return_status => l_return_status
3228 ,x_msg_count => l_msg_count
3229 ,x_msg_data => l_msg_data );
3230
3231 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3232 x_msg_count := FND_MSG_PUB.count_msg;
3233 if x_msg_count = 1 then
3234 pa_interface_utils_pub.get_messages
3235 (p_encoded => FND_API.G_TRUE,
3236 p_msg_index => 1,
3237 p_msg_count => l_msg_count,
3238 p_msg_data => l_msg_data,
3239 p_data => l_data,
3240 p_msg_index_out => l_msg_index_out);
3241 x_msg_data := l_data;
3242 end if;
3243 raise FND_API.G_EXC_ERROR;
3244 end if;
3245
3246 end if;
3247 end if;
3248
3249 -- Now create the org role
3250 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
3251 ( p_validate_only => p_validate_only
3252 ,p_init_msg_list => p_init_msg_list
3253 ,p_validation_level => p_validation_level
3254 ,p_debug_mode => p_debug_mode
3255 ,p_calling_module => p_calling_module
3256 ,p_object_id => p_object_id
3257 ,p_object_type => p_object_type
3258 ,p_project_role_type => p_project_role_type
3259 ,p_project_role_id => p_project_role_id
3260 ,p_resource_type_id => p_resource_type_id
3261 ,p_resource_source_id => p_resource_source_id
3262 ,p_resource_name => p_resource_name
3263 ,p_start_date_active => p_start_date_active
3264 ,p_scheduled_flag => p_scheduled_flag
3265 ,p_project_id => p_project_id
3266 ,p_project_end_date => p_project_end_date
3267 ,p_end_date_active => p_end_date_active
3268 ,x_project_party_id => l_new_project_party_id
3269 ,x_resource_id => l_resource_id
3270 ,x_wf_type => l_wf_type
3271 ,x_wf_item_type => l_wf_item_type
3272 ,x_wf_process => l_wf_process
3273 ,x_assignment_id => l_assignment_id
3274 ,x_return_status => l_return_status
3275 ,x_msg_count => l_msg_count
3276 ,x_msg_data => l_msg_data );
3277
3278 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3279 x_msg_count := FND_MSG_PUB.count_msg;
3280 if x_msg_count = 1 then
3281 pa_interface_utils_pub.get_messages
3282 (p_encoded => FND_API.G_TRUE,
3283 p_msg_index => 1,
3284 p_msg_count => l_msg_count,
3285 p_msg_data => l_msg_data,
3286 p_data => l_data,
3287 p_msg_index_out => l_msg_index_out);
3288 x_msg_data := l_data;
3289 end if;
3290 raise FND_API.G_EXC_ERROR;
3291 end if;
3292
3293 -- Now update the customer record with the new project_party_id
3294 if l_role_class = 'CUSTOMER' then
3295 if l_customer_id is not null then
3296 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
3297 -- Update the project_party_id col in pa_project_customers to null
3298 UPDATE pa_project_customers
3299 SET project_party_id = l_new_project_party_id
3300 WHERE project_id = p_project_id
3301 AND customer_id = l_customer_id;
3302 end if;
3303 end if;
3304 end if;
3305
3306 x_return_status := FND_API.G_RET_STS_SUCCESS;
3307
3308 if p_commit = FND_API.G_TRUE then
3309 commit work;
3310 end if;
3311
3312 if (p_debug_mode = 'Y') then
3313 pa_debug.debug('PA_PROJECTS_MAINT_PUB.Create_Org_Role END');
3314 end if;
3315
3316 EXCEPTION
3317 when FND_API.G_EXC_ERROR then
3318 if p_commit = FND_API.G_TRUE then
3319 rollback to create_org_role;
3320 end if;
3321 x_return_status := FND_API.G_RET_STS_ERROR;
3322 when FND_API.G_EXC_UNEXPECTED_ERROR then
3323 if p_commit = FND_API.G_TRUE then
3324 rollback to create_org_role;
3325 end if;
3326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3327 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3328 p_procedure_name => 'Create_Org_Role',
3329 p_error_text => SUBSTRB(SQLERRM,1,240));
3330 when OTHERS then
3331 if p_commit = FND_API.G_TRUE then
3332 rollback to create_org_role;
3333 end if;
3334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3335 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3336 p_procedure_name => 'Create_Org_Role',
3337 p_error_text => SUBSTRB(SQLERRM,1,240));
3338 raise;
3339 END CREATE_ORG_ROLE;
3340
3341 -- Procedure : project_status_change
3342 -- Type : PUBLIC
3343 -- Purpose : This a wrapper API for calling handle_project_status_change and is used from the
3344 -- Project Change Status Page in self-service
3345 -- Note :
3346 -- Parameters Type Required Description and Purpose
3347 -- --------------------------- ------ -------- --------------------------------------------------------
3348 -- p_project_id NUMBER Y The project id
3349 -- p_new_status_code VARCHAR2 Y The new project status
3350 -- p_comment VARCAHR2 N Comment enterd while changing project status
3351 PROCEDURE project_status_change ( p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3352 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3353 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3354 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3355 ,p_debug_mode IN VARCHAR2 := 'N'
3356 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3357 ,p_project_id IN NUMBER
3358 ,p_new_status_code IN VARCHAR2
3359 ,p_comment IN VARCHAR2
3360 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3361 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3362 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3363 ) IS
3364 CURSOR cur_get_project_details IS
3365 SELECT project_status_code
3366 ,project_type
3367 ,start_date
3368 ,closed_date
3369 ,public_sector_flag
3370 ,attribute_category
3371 ,attribute1
3372 ,attribute2
3373 ,attribute3
3374 ,attribute4
3375 ,attribute5
3376 ,attribute6
3377 ,attribute7
3378 ,attribute8
3379 ,attribute9
3380 ,attribute10
3381 ,pm_product_code
3382 ,segment1 -- 3671408 added column to retrieve project number value
3383 FROM pa_projects_all
3384 WHERE project_id = p_project_id;
3385
3386 CURSOR cur_get_project_status_code( c_project_id IN NUMBER ) IS
3387 SELECT project_status_code
3388 FROM pa_projects_all
3389 WHERE project_id = c_project_id;
3390
3391 CURSOR cur_get_system_status(c_status_code IN VARCHAR2) IS
3392 SELECT pps.project_system_status_code
3393 FROM pa_project_statuses pps
3394 WHERE pps.project_status_code = nvl(c_status_code,' ');
3395
3396 Invalid_Arg_Exc EXCEPTION;
3397 l_rec_project_details cur_get_project_details%ROWTYPE;
3398
3399 l_old_project_status VARCHAR2(30);
3400 l_old_sys_status VARCHAR2(30);
3401 l_verify_ok_flag VARCHAR2(1);
3402 l_warnings_only_flag VARCHAR2(1);
3403 l_wf_enabled_flag VARCHAR2(1);
3404 l_err_stage VARCHAR2(2000);
3405 l_err_stack VARCHAR2(2000);
3406 l_err_code VARCHAR2(250);
3407 l_wf_status_code VARCHAR2(30);
3408
3409 -- Declared for 4364878
3410 l_new_sys_status VARCHAR2(30);
3411
3412 l_debug_mode VARCHAR2(1);
3413 l_msg_count NUMBER;
3414 l_msg_data VARCHAR2(2000);
3415 l_data VARCHAR2(2000);
3416 l_msg_index_out NUMBER;
3417 l_calling_module VARCHAR2(50); -- for BUG# 6661144
3418 l_abort_flag VARCHAR2(1) :='N'; -- for BUG# 6661144
3419 l_cant_update_status VARCHAR2(1) :='N';
3420
3421 l_debug_level2 CONSTANT NUMBER := 2;
3422 l_debug_level3 CONSTANT NUMBER := 3;
3423 l_debug_level4 CONSTANT NUMBER := 4;
3424 l_debug_level5 CONSTANT NUMBER := 5;
3425 BEGIN
3426
3427 x_msg_count := 0;
3428 x_return_status := FND_API.G_RET_STS_SUCCESS;
3429 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3430
3431 IF (p_commit = FND_API.G_TRUE) THEN
3432 SAVEPOINT project_status_change;
3433 END IF;
3434
3435 IF p_init_msg_list = FND_API.G_TRUE THEN
3436 FND_MSG_PUB.initialize;
3437 END IF;
3438
3439 IF l_debug_mode = 'Y' THEN
3440 PA_DEBUG.set_curr_function( p_function => 'project_status_change',
3441 p_debug_mode => l_debug_mode );
3442 END IF;
3443
3444 IF l_debug_mode = 'Y' THEN
3445 Pa_Debug.g_err_stage:= 'Printing Input parameters';
3446 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3447 l_debug_level3);
3448
3449 Pa_Debug.WRITE(G_PKG_NAME,'p_project_id'||':'||p_project_id,
3450 l_debug_level3);
3451
3452 Pa_Debug.WRITE(G_PKG_NAME,'p_new_status_code'||':'||p_new_status_code,
3453 l_debug_level3);
3454 END IF;
3455
3456 IF l_debug_mode = 'Y' THEN
3457 Pa_Debug.g_err_stage:= 'Validating Input parameters';
3458 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3459 l_debug_level3);
3460 END IF;
3461
3462 IF ( ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM ) AND
3463 ( p_new_status_code IS NULL OR p_new_status_code = FND_API.G_MISS_CHAR )
3464 )
3465 THEN
3466 IF l_debug_mode = 'Y' THEN
3467 Pa_Debug.g_err_stage:= 'PA_PROJECTS_MAINT_PUB : project_status_change :
3468 p_project_id, p_new_status_code are NULL';
3469 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3470 l_debug_level3);
3471 END IF;
3472 RAISE Invalid_Arg_Exc;
3473 END IF;
3474
3475 l_calling_module := p_calling_module;
3476 IF (p_calling_module = 'SSO_ABORT') THEN -- for BUG# 6661144
3477 l_calling_module := 'SELF_SERVICE';
3478 l_abort_flag := 'Y';
3479 END IF;
3480
3481 IF ( (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM ) AND
3482 (p_new_status_code IS NOT NULL AND p_new_status_code <> FND_API.G_MISS_CHAR )
3483 ) THEN
3484
3485 --Fetch the (old) project status before changing it
3486 OPEN cur_get_project_status_code(p_project_id);
3487 FETCH cur_get_project_status_code INTO l_old_project_status;
3488 CLOSE cur_get_project_status_code;
3489
3490 --Fetch the (old) project system status before changing it
3491 OPEN cur_get_system_status(l_old_project_status);
3492 FETCH cur_get_system_status INTO l_old_sys_status;
3493 CLOSE cur_get_system_status;
3494
3495 OPEN cur_get_project_details;
3496 FETCH cur_get_project_details INTO l_rec_project_details;
3497 CLOSE cur_get_project_details;
3498
3499 /* <**A**> Start Bug 4364878 */
3500 OPEN cur_get_system_status(p_new_status_code);
3501 FETCH cur_get_system_status INTO l_new_sys_status;
3502 CLOSE cur_get_system_status;
3503 /* <**A**> End Bug 4364878 */
3504
3505 -- Bug 12809532 : Check if Control Action is defined for 'Update Project Status'
3506 BEGIN
3507 SELECT 'Y' INTO l_cant_update_status
3508 FROM pa_pm_product_control_rules pc,
3509 pa_pm_control_actions pa
3510 WHERE pa.action = 'UPDATE_PROJECT_STATUS'
3511 AND pa.control_rule_id = pc.control_rule_id
3512 AND pc.pm_product_code = l_rec_project_details.pm_product_code
3513 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
3514 AND TRUNC(NVL(end_date_active,SYSDATE));
3515 EXCEPTION
3516 WHEN NO_DATA_FOUND THEN
3517 NULL;
3518 END;
3519
3520 IF l_cant_update_status = 'Y' THEN
3521 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3522 p_msg_name => 'PA_PR_PM_FIELD_CANNOT_CHANGE');
3523 RAISE FND_API.G_EXC_ERROR;
3524 END IF;
3525 -- Bug 12809532 : End of changes
3526
3527 -- Bug 4565023: Begin
3528 IF (l_debug_mode = 'Y') THEN
3529 Pa_Debug.WRITE(G_PKG_NAME,'Calling ALLOW_STATUS_CHANGE', l_debug_level3);
3530 END IF;
3531
3532 IF (PA_PROJECT_STUS_UTILS.ALLOW_STATUS_CHANGE(l_old_project_status,p_new_status_code) = 'N') THEN
3533 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3534 p_msg_name => 'PA_STATUS_CANT_CHANGE');
3535 RAISE FND_API.G_EXC_ERROR;
3536 END IF;
3537 -- Bug 4565023: End
3538
3539 IF (l_debug_mode = 'Y') THEN
3540 Pa_Debug.WRITE(G_PKG_NAME,'Calling HANDLE_PROJECT_STATUS_CHANGE', l_debug_level3);
3541 END IF;
3542
3543 --The following API call will verify the projects status change
3544 PA_PROJECT_STUS_UTILS.HANDLE_PROJECT_STATUS_CHANGE
3545 ( x_calling_module => L_calling_module
3546 ,X_project_id => p_project_id
3547 ,X_old_proj_status_code => l_rec_project_details.project_status_code
3548 ,X_new_proj_status_code => p_new_status_code
3549 ,X_project_type => l_rec_project_details.project_type
3550 ,X_project_start_date => l_rec_project_details.start_date
3551 ,X_project_end_date => l_rec_project_details.closed_date
3552 ,X_public_sector_flag => l_rec_project_details.public_sector_flag
3553 ,X_attribute_category => l_rec_project_details.attribute_category
3554 ,X_attribute1 => l_rec_project_details.attribute1
3555 ,X_attribute2 => l_rec_project_details.attribute2
3556 ,X_attribute3 => l_rec_project_details.attribute3
3557 ,X_attribute4 => l_rec_project_details.attribute4
3558 ,X_attribute5 => l_rec_project_details.attribute5
3559 ,X_attribute6 => l_rec_project_details.attribute6
3560 ,X_attribute7 => l_rec_project_details.attribute7
3561 ,X_attribute8 => l_rec_project_details.attribute8
3562 ,X_attribute9 => l_rec_project_details.attribute9
3563 ,X_attribute10 => l_rec_project_details.attribute10
3564 ,X_pm_product_code => l_rec_project_details.pm_product_code
3565 ,x_init_msg => 'N'
3566 ,x_verify_ok_flag => l_verify_ok_flag
3567 ,x_wf_enabled_flag => l_wf_enabled_flag
3568 ,X_err_stage => l_err_stage
3569 ,X_err_stack => l_err_stack
3570 ,x_err_msg_count => l_msg_count
3571 ,x_warnings_only_flag => l_warnings_only_flag );
3572
3573 IF (l_debug_mode = 'Y') THEN
3574 Pa_Debug.WRITE(G_PKG_NAME,'l_verify_ok_flag ='||l_verify_ok_flag||' l_wf_enabled_flag ='||l_wf_enabled_flag,
3575 l_debug_level3);
3576 END IF;
3577
3578 IF l_verify_ok_flag <> 'Y' THEN
3579 x_msg_data := l_err_stack||'-> '||l_err_stage;
3580 RAISE FND_API.G_EXC_ERROR;
3581 ELSIF l_verify_ok_flag = 'Y' THEN
3582 -- Moved this block so that the comment is available in the table before the
3583 -- workflow is initiated (Bug 6720288)
3584 DECLARE
3585 l_rowid VARCHAR2(255);
3586 l_obj_status_change_id NUMBER;
3587 BEGIN
3588 SELECT pa_obj_status_changes_s.NEXTVAL INTO l_obj_status_change_id
3589 FROM dual;
3590 /* 4364878 : Moved this code to Block tagged with <**A**>
3591 OPEN cur_get_system_status(p_new_status_code);
3592 FETCH cur_get_system_status INTO l_new_sys_status;
3593 CLOSE cur_get_system_status;
3594 Moved this code to Block tagged with <**A**> */
3595
3596 IF (l_debug_mode = 'Y') THEN
3597 pa_debug.debug('Calling PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW');
3598 END IF;
3599
3600 --For inserting status change comment into the status history table
3601 PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
3602 ( X_ROWID => l_rowid,
3603 X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
3604 X_OBJECT_TYPE => 'PA_PROJECTS',
3605 X_OBJECT_ID => p_project_id,
3606 X_STATUS_TYPE => 'PROJECT',
3607 X_NEW_PROJECT_STATUS_CODE => p_new_status_code,
3608 X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
3609 X_OLD_PROJECT_STATUS_CODE => l_old_project_status,
3610 X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sys_status,
3611 X_CHANGE_COMMENT => p_comment,
3612 X_LAST_UPDATED_BY => fnd_global.user_id,
3613 X_CREATED_BY => fnd_global.user_id,
3614 X_CREATION_DATE => sysdate,
3615 X_LAST_UPDATE_DATE => sysdate,
3616 X_LAST_UPDATE_LOGIN => fnd_global.user_id );
3617
3618 IF (l_debug_mode = 'Y') THEN
3619 pa_debug.debug('Updated PA_PROJECTS_ALL for status history');
3620 END IF;
3621
3622 /*Stubbed Out Auto Initiate Demand On Project Approval Functionality
3623 Bug 3819086 */
3624 /* Bug 3611598 Start of Changes by avaithia on 05-May-2004
3625 Included Call to wrapper API to automatically Initiate Demand when the Project Status
3626 is changed to 'Approved' (provided workflow is not enabled)*/
3627 /*Start of Commenting Bug 3819086 */
3628 /*IF nvl(l_new_sys_status,'-99') = 'APPROVED' AND nvl(l_wf_enabled_flag,'N') = 'N' THEN
3629 PA_ACTIONS_PUB.RUN_ACTION_CONC_PROCESS_WRP
3630 (p_project_id => p_project_id
3631 -- 3671408 added IN parameter, passing retrieve project_number
3632 ,p_project_number => l_rec_project_details.segment1
3633 ,x_return_status => x_return_status
3634 ,x_msg_count => x_msg_count
3635 ,x_msg_data => x_msg_data
3636 );
3637 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3638 RAISE FND_API.G_EXC_ERROR ;
3639 END IF;
3640 END IF;*/
3641 /* End of Changes for Bug 3611598 */
3642 /*End of Commenting Bug 3819086 */
3643
3644 END;
3645
3646 SELECT wf_status_code INTO l_wf_status_code
3647 FROM PA_PROJECTS_ALL
3648 WHERE project_id = p_project_id;
3649
3650 IF (l_debug_mode = 'Y') THEN
3651 Pa_Debug.WRITE(G_PKG_NAME,'l_wf_status_code ='||l_wf_status_code,
3652 l_debug_level3);
3653 END IF;
3654
3655 IF (l_abort_flag='Y') THEN -- for BUG# 6661144
3656
3657 -- 4364878 : if new system status corresponds to CLOSED
3658 -- Then Populate the closed date as sysdate
3659
3660 IF l_new_sys_status = 'CLOSED' THEN
3661 UPDATE pa_projects_all
3662 SET wf_status_code = NULL
3663 ,project_status_code = p_new_status_code
3664 ,closed_date = sysdate
3665 ,last_update_date = sysdate
3666 ,last_updated_by = fnd_global.user_id
3667 ,last_update_login = fnd_global.login_id
3668 WHERE project_id = p_project_id;
3669 ELSE
3670
3671 UPDATE pa_projects_all
3672 SET wf_status_code = NULL
3673 ,project_status_code = p_new_status_code --Added for bug 3818322
3674 /*4364878 : Included update of who columns also*/
3675 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3676 ,last_update_date = sysdate
3677 ,last_updated_by = fnd_global.user_id
3678 ,last_update_login = fnd_global.login_id
3679 WHERE project_id = p_project_id;
3680 END IF;
3681
3682 --Start the workflow for the project
3683
3684
3685
3686 ELSIF l_wf_status_code IS NULL THEN -- Made ELSEIF against the existing IF, for BUG# 6661144
3687 IF l_wf_enabled_flag = 'Y' THEN
3688 -- Update wf status in pa_projects_all
3689
3690 -- 4364878 : if new system status corresponds to CLOSED
3691 -- Then Populate the closed date as sysdate
3692
3693 If l_new_sys_status = 'CLOSED' THEN
3694 UPDATE pa_projects_all
3695 SET wf_status_code = 'IN_ROUTE'
3696 ,project_status_code = p_new_status_code
3697 ,closed_date = sysdate
3698 ,last_update_date = sysdate
3699 ,last_updated_by = fnd_global.user_id
3700 ,last_update_login = fnd_global.login_id
3701 WHERE project_id = p_project_id;
3702 ELSE
3703
3704 UPDATE pa_projects_all
3705 SET wf_status_code = 'IN_ROUTE'
3706 ,project_status_code = p_new_status_code --Added for bug 3818322
3707 /*4364878 : Included update of who columns also*/
3708 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3709 ,last_update_date = sysdate
3710 ,last_updated_by = fnd_global.user_id
3711 ,last_update_login = fnd_global.login_id
3712 WHERE project_id = p_project_id;
3713 END IF;
3714
3715 --Start the workflow for the project
3716 pa_project_wf.start_project_wf
3717 ( p_project_id
3718 , l_err_stack
3719 , l_err_stage
3720 , l_err_code );
3721
3722 IF l_err_code <> 0 THEN
3723 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3724 p_msg_name => l_err_stage);
3725 x_msg_data := l_err_stack||'-> '||l_err_stage;
3726 RAISE FND_API.G_EXC_ERROR;
3727 END IF;
3728 END IF;
3729 ELSE
3730 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3731 p_msg_name => 'PA_STATUS_CANT_CHANGE');
3732 RAISE FND_API.G_EXC_ERROR;
3733 END IF;
3734
3735 --Uncommented the foll. code and added IF condition for bug 3940861
3736 --(had been commented for 3818322)
3737 IF l_wf_enabled_flag = 'N' AND l_abort_flag='N' THEN -- Added the AND condition for BUG# 6661144
3738
3739 -- 4364878 : if new system status corresponds to CLOSED
3740 -- Then Populate the closed date as sysdate
3741 If l_new_sys_status = 'CLOSED' THEN
3742
3743 UPDATE pa_projects_all
3744 SET project_status_code = p_new_status_code
3745 ,closed_date = sysdate
3746 ,last_update_date = sysdate
3747 ,last_updated_by = fnd_global.user_id
3748 ,last_update_login = fnd_global.login_id
3749 WHERE project_id = p_project_id ;
3750
3751 Else
3752
3753 UPDATE pa_projects_all
3754 SET project_status_code = p_new_status_code
3755 /*4364878 : Included update of who columns also*/
3756 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3757 ,last_update_date = sysdate
3758 ,last_updated_by = fnd_global.user_id
3759 ,last_update_login = fnd_global.login_id
3760 WHERE project_id = p_project_id ;
3761
3762 End if;
3763 END IF;
3764 END IF;
3765
3766
3767 END IF;--IF p_project_id IS NOT NULL
3768
3769 EXCEPTION
3770
3771 WHEN FND_API.G_EXC_ERROR THEN
3772
3773 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3774
3775 IF p_commit = FND_API.G_TRUE THEN
3776 ROLLBACK TO project_status_change;
3777 END IF;
3778
3779 l_msg_count := Fnd_Msg_Pub.count_msg;
3780 IF l_msg_count = 1 AND x_msg_data IS NULL
3781 THEN
3782 Pa_Interface_Utils_Pub.get_messages
3783 ( p_encoded => Fnd_Api.G_FALSE
3784 , p_msg_index => 1
3785 , p_msg_count => l_msg_count
3786 , p_msg_data => l_msg_data
3787 , p_data => l_data
3788 , p_msg_index_out => l_msg_index_out);
3789 x_msg_data := l_data;
3790 x_msg_count := l_msg_count;
3791 ELSE
3792 x_msg_count := l_msg_count;
3793 END IF;
3794
3795 IF l_debug_mode = 'Y' THEN
3796 Pa_Debug.reset_curr_function;
3797 END IF;
3798
3799 WHEN Invalid_Arg_Exc THEN
3800 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3801 x_msg_count := 1;
3802 x_msg_data := ' PA_PROJECTS_MAINT_PUB : project_status_change : NULL parameters passed';
3803
3804 IF p_commit = FND_API.G_TRUE THEN
3805 ROLLBACK TO project_status_change;
3806 END IF;
3807
3808 Fnd_Msg_Pub.add_exc_msg
3809 ( p_pkg_name => 'PA_PROJECTS_MAINT_PUB'
3810 , p_procedure_name => 'project_status_change'
3811 , p_error_text => x_msg_data);
3812
3813 IF l_debug_mode = 'Y' THEN
3814 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3815 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3816 l_debug_level5);
3817 Pa_Debug.reset_curr_function;
3818 END IF;
3819 RAISE;
3820
3821 WHEN OTHERS THEN
3822 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3823 x_msg_count := 1;
3824 x_msg_data := SQLERRM;
3825
3826 IF p_commit = FND_API.G_TRUE THEN
3827 ROLLBACK TO project_status_change;
3828 END IF;
3829
3830 Fnd_Msg_Pub.add_exc_msg
3831 ( p_pkg_name => 'PA_PROJECTS_MAINT_PUB'
3832 , p_procedure_name => 'project_status_change'
3833 , p_error_text => x_msg_data);
3834
3835 IF l_debug_mode = 'Y' THEN
3836 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3837 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3838 l_debug_level5);
3839 Pa_Debug.reset_curr_function;
3840 END IF;
3841 RAISE;
3842
3843 END project_status_change;
3844
3845
3846 END PA_PROJECTS_MAINT_PUB;