[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECTS_MAINT_PUB
Source
1 PACKAGE BODY PA_PROJECTS_MAINT_PUB AS
2 /* $Header: PARMPRPB.pls 120.4.12010000.4 2008/09/22 07:17:12 kjai 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 BEGIN
2001
2002 SELECT 'x' INTO l_dummy
2003 FROM pa_class_codes
2004 WHERE CLASS_CODE = p_class_code and
2005 CLASS_CATEGORY = p_class_category;
2006
2007 EXCEPTION WHEN NO_DATA_FOUND THEN
2008 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2009 p_msg_name => 'PA_CLASS_CATG_CODE_INVALID');
2010 x_msg_data := 'PA_CLASS_CATG_CODE_INVALID';
2011 x_return_status := 'E' ;
2012 WHEN OTHERS THEN
2013 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2014 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2015 p_error_text => SUBSTRB(SQLERRM,1,240));
2016 raise;
2017 END;
2018
2019 l_msg_count := FND_MSG_PUB.count_msg;
2020 If l_msg_count > 0 THEN
2021 x_msg_count := l_msg_count;
2022 If l_msg_count = 1 THEN
2023 pa_interface_utils_pub.get_messages
2024 (p_encoded => FND_API.G_TRUE ,
2025 p_msg_index => 1,
2026 p_msg_count => l_msg_count ,
2027 p_msg_data => l_msg_data,
2028 p_data => l_data,
2029 p_msg_index_out => l_msg_index_out );
2030 x_msg_data := l_data;
2031 End if;
2032 RAISE FND_API.G_EXC_ERROR;
2033 End if;
2034
2035 /*
2036 l_msg_count := FND_MSG_PUB.count_msg;
2037
2038 IF l_msg_count = 1 THEN
2039 x_msg_count := l_msg_count;
2040 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2041 p_encoded => FND_API.G_TRUE);
2042
2043 ELSE
2044 x_msg_count := l_msg_count;
2045 END IF;
2046
2047 if l_msg_count > 0 THEN
2048 x_return_status := 'E';
2049 RAISE FND_API.G_EXC_ERROR;
2050 end if;
2051 */
2052 PA_PROJECTS_MAINT_PVT.CREATE_CLASSIFICATIONS
2053 (
2054 p_commit => FND_API.G_FALSE,
2055 p_validate_only => p_validate_only,
2056 p_validation_level => p_validation_level,
2057 p_calling_module => p_calling_module,
2058 p_debug_mode => p_debug_mode,
2059 p_max_msg_count => p_max_msg_count,
2060 p_object_id => p_object_id,
2061 p_object_type => p_object_type,
2062 p_class_category => p_class_category,
2063 p_class_code => p_class_code,
2064 p_code_percentage => p_code_percentage,
2065 p_attribute_category => p_attribute_category,
2066 p_attribute1 => p_attribute1,
2067 p_attribute2 => p_attribute2,
2068 p_attribute3 => p_attribute3,
2069 p_attribute4 => p_attribute4,
2070 p_attribute5 => p_attribute5,
2071 p_attribute6 => p_attribute6,
2072 p_attribute7 => p_attribute7,
2073 p_attribute8 => p_attribute8,
2074 p_attribute9 => p_attribute9,
2075 p_attribute10 => p_attribute10,
2076 p_attribute11 => p_attribute11,
2077 p_attribute12 => p_attribute12,
2078 p_attribute13 => p_attribute13,
2079 p_attribute14 => p_attribute14,
2080 p_attribute15 => p_attribute15,
2081 x_return_status => l_return_status,
2082 x_msg_count => l_msg_count,
2083 x_msg_data => l_msg_data);
2084
2085 l_msg_count := FND_MSG_PUB.count_msg;
2086 If l_msg_count > 0 THEN
2087 x_msg_count := l_msg_count;
2088 If l_msg_count = 1 THEN
2089 pa_interface_utils_pub.get_messages
2090 (p_encoded => FND_API.G_TRUE ,
2091 p_msg_index => 1,
2092 p_msg_count => l_msg_count ,
2093 p_msg_data => l_msg_data,
2094 p_data => l_data,
2095 p_msg_index_out => l_msg_index_out );
2096 x_msg_data := l_data;
2097 End if;
2098 RAISE FND_API.G_EXC_ERROR;
2099 End if;
2100 /*
2101 l_msg_count := FND_MSG_PUB.count_msg;
2102
2103 IF l_msg_count = 1 THEN
2104 x_msg_count := l_msg_count;
2105 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2106 p_encoded => FND_API.G_TRUE);
2107 ELSE
2108 x_msg_count := l_msg_count;
2109 END IF;
2110
2111 --dbms_output.put_line('Before PA_PROJECTS_MAINT_PVT.CREATE_CLASSIFICATIONS '||to_char(l_msg_count));
2112
2113 if (p_debug_mode = 'Y') then
2114 pa_debug.debug('Create_classifications PUB : Checking message count');
2115 end if;
2116
2117 --dbms_output.put_line('Before PA_PROJECTS_MAINT_PVT. '||l_data);
2118
2119 IF l_msg_count > 0 THEN
2120 x_return_status := 'E';
2121 RAISE FND_API.G_EXC_ERROR;
2122 END IF;
2123 */
2124 IF FND_API.TO_BOOLEAN(P_COMMIT)
2125 THEN
2126 COMMIT WORK;
2127 END IF;
2128
2129 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2130 IF p_commit = FND_API.G_TRUE THEN
2131 ROLLBACK TO create_classifications;
2132 END IF;
2133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2134 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2135 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2136 p_error_text => SUBSTRB(SQLERRM,1,240));
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2138
2139 WHEN FND_API.G_EXC_ERROR THEN
2140 IF p_commit = FND_API.G_TRUE THEN
2141 ROLLBACK TO create_classifications;
2142 END IF;
2143 x_return_status := 'E';
2144
2145 WHEN OTHERS THEN
2146 IF p_commit = FND_API.G_TRUE THEN
2147 ROLLBACK TO CREATE_CLASSIFICATIONS;
2148 END IF;
2149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2150 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2151 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2152 p_error_text => SUBSTRB(SQLERRM,1,240));
2153 raise;
2154
2155 END CREATE_CLASSIFICATIONS;
2156
2157
2158 -- API name : Update_classifications
2159 -- Type : Public
2160 -- Pre-reqs : None.
2161 -- Parameters :
2162 -- p_api_version IN NUMBER Optional Default = 1.0
2163 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2164 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2165 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2166 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2167 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2168 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2169 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2170 -- p_object_id IN NUMBER Required
2171 -- p_object_type IN VARCHAR2 Required
2172 -- p_class_category IN VARCHAR2 Required
2173 -- p_class_code IN VARCHAR2 Required
2174 -- p_code_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
2175 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2176 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2177 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2178 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2179 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2180 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2181 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2182 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2183 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2184 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2185 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2186 -- p_attribute11 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2187 -- p_attribute12 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2188 -- p_attribute13 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2189 -- p_attribute14 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2190 -- p_attribute15 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2191 -- p_rowid IN VARCHAR2
2192 -- p_record_version_number IN NUMBER
2193 -- x_return_status OUT VARCHAR2 Required
2194 -- x_msg_count OUT NUMBER Required
2195 -- x_msg_data OUT VARCHAR2 Required
2196 --
2197 -- History
2198 --
2199 -- 12-OCT-2001 -- anlee created
2200 --
2201 --
2202 PROCEDURE UPDATE_CLASSIFICATIONS
2203 (p_api_version IN NUMBER := 1.0 ,
2204 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2205 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2206 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2207 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2208 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2209 p_debug_mode IN VARCHAR2 := 'N',
2210 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2211 p_object_id IN NUMBER,
2212 p_object_type IN VARCHAR2,
2213 p_class_category IN VARCHAR2 ,
2214 p_class_code IN VARCHAR2 ,
2215 p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
2216 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2217 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2218 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2219 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2220 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2221 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2222 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2223 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2224 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2225 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2226 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2227 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2228 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2229 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2230 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2231 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2232 p_rowid IN VARCHAR2 ,
2233 p_record_version_number IN NUMBER ,
2234 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2235 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2236 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2237 IS
2238
2239 l_dummy VARCHAR2(1);
2240 l_created_from_project_id pa_projects.created_from_project_id%TYPE;
2241 l_api_name CONSTANT VARCHAR(30) := 'create_classifications';
2242 l_api_version CONSTANT NUMBER := 1.0;
2243 l_project_id pa_projects.project_id%TYPE;
2244 l_row_id VARCHAR2(250);
2245 l_return_status VARCHAR2(250);
2246 l_error_msg_code VARCHAR2(250);
2247 l_msg_count NUMBER;
2248 l_msg_data VARCHAR2(250);
2249 l_err_code VARCHAR2(250);
2250 l_err_stage VARCHAR2(250);
2251 l_err_stack VARCHAR2(250);
2252 l_data VARCHAR2(250);
2253 l_msg_index_out NUMBER;
2254 BEGIN
2255
2256 IF p_commit = FND_API.G_TRUE THEN
2257 SAVEPOINT update_classifications;
2258 END IF;
2259
2260 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_classifications');
2261
2262 if (p_debug_mode = 'Y') then
2263 pa_debug.debug('Update_classifications PUB : Checking API compatibility');
2264 end if;
2265 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2266 p_api_version,
2267 l_api_name,
2268 g_pkg_name)
2269 THEN
2270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2271 END IF;
2272
2273 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2274 fnd_msg_pub.initialize;
2275 end if;
2276
2277 x_return_status := FND_API.G_RET_STS_SUCCESS;
2278
2279 -- write your program logic here
2280
2281 if (p_debug_mode = 'Y') then
2282 pa_debug.debug('Update_classifications PUB : Calling Private API Update_classifications');
2283 end if;
2284
2285 -- Checking Input Parameters
2286
2287 if (p_debug_mode = 'Y') then
2288 pa_debug.debug('Update_classifications PUB: Checking Class Cateogory ');
2289 end if;
2290
2291 BEGIN
2292
2293 SELECT 'x' INTO l_dummy
2294 FROM pa_class_categories
2295 WHERE CLASS_CATEGORY = p_class_category;
2296
2297 EXCEPTION WHEN NO_DATA_FOUND THEN
2298 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2299 p_msg_name => 'PA_AL_INVALID_CLASS_CATG_PROJE');
2300 x_msg_data := 'PA_AL_INVALID_CLASS_CATG_PROJE';
2301 x_return_status := 'E' ;
2302 WHEN OTHERS THEN
2303 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2304 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2305 p_error_text => SUBSTRB(SQLERRM,1,240));
2306 raise;
2307 END;
2308
2309 if (p_debug_mode = 'Y') then
2310 pa_debug.debug('Update_classifications PUB: Checking Class Category / Codes Pair');
2311 end if;
2312
2313 -- Validate that the Class category / code pair is valid
2314 BEGIN
2315
2316 SELECT 'x' INTO l_dummy
2317 FROM pa_class_codes
2318 WHERE CLASS_CODE = p_class_code and
2319 CLASS_CATEGORY = p_class_category;
2320
2321 EXCEPTION WHEN NO_DATA_FOUND THEN
2322 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2323 p_msg_name => 'PA_CLASS_CATG_CODE_INVALID');
2324 x_msg_data := 'PA_CLASS_CATG_CODE_INVALID';
2325 x_return_status := 'E' ;
2326 WHEN OTHERS THEN
2327 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2328 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2329 p_error_text => SUBSTRB(SQLERRM,1,240));
2330 raise;
2331 END;
2332
2333 l_msg_count := FND_MSG_PUB.count_msg;
2334 If l_msg_count > 0 THEN
2335 x_msg_count := l_msg_count;
2336 If l_msg_count = 1 THEN
2337 pa_interface_utils_pub.get_messages
2338 (p_encoded => FND_API.G_TRUE ,
2339 p_msg_index => 1,
2340 p_msg_count => l_msg_count ,
2341 p_msg_data => l_msg_data,
2342 p_data => l_data,
2343 p_msg_index_out => l_msg_index_out );
2344 x_msg_data := l_data;
2345 End if;
2346 RAISE FND_API.G_EXC_ERROR;
2347 End if;
2348
2349
2350 PA_PROJECTS_MAINT_PVT.UPDATE_CLASSIFICATIONS
2351 (
2352 p_commit => FND_API.G_FALSE,
2353 p_validate_only => p_validate_only,
2354 p_validation_level => p_validation_level,
2355 p_calling_module => p_calling_module,
2356 p_debug_mode => p_debug_mode,
2357 p_max_msg_count => p_max_msg_count,
2358 p_object_id => p_object_id,
2359 p_object_type => p_object_type,
2360 p_class_category => p_class_category,
2361 p_class_code => p_class_code,
2362 p_code_percentage => p_code_percentage,
2363 p_attribute_category => p_attribute_category,
2364 p_attribute1 => p_attribute1,
2365 p_attribute2 => p_attribute2,
2366 p_attribute3 => p_attribute3,
2367 p_attribute4 => p_attribute4,
2368 p_attribute5 => p_attribute5,
2369 p_attribute6 => p_attribute6,
2370 p_attribute7 => p_attribute7,
2371 p_attribute8 => p_attribute8,
2372 p_attribute9 => p_attribute9,
2373 p_attribute10 => p_attribute10,
2374 p_attribute11 => p_attribute11,
2375 p_attribute12 => p_attribute12,
2376 p_attribute13 => p_attribute13,
2377 p_attribute14 => p_attribute14,
2378 p_attribute15 => p_attribute15,
2379 p_rowid => p_rowid,
2380 p_record_version_number => p_record_version_number,
2381 x_return_status => l_return_status,
2382 x_msg_count => l_msg_count,
2383 x_msg_data => l_msg_data);
2384
2385 l_msg_count := FND_MSG_PUB.count_msg;
2386 If l_msg_count > 0 THEN
2387 x_msg_count := l_msg_count;
2388 If l_msg_count = 1 THEN
2389 pa_interface_utils_pub.get_messages
2390 (p_encoded => FND_API.G_TRUE ,
2391 p_msg_index => 1,
2392 p_msg_count => l_msg_count ,
2393 p_msg_data => l_msg_data,
2394 p_data => l_data,
2395 p_msg_index_out => l_msg_index_out );
2396 x_msg_data := l_data;
2397 End if;
2398 RAISE FND_API.G_EXC_ERROR;
2399 End if;
2400
2401 IF FND_API.TO_BOOLEAN(P_COMMIT)
2402 THEN
2403 COMMIT WORK;
2404 END IF;
2405
2406 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2407 IF p_commit = FND_API.G_TRUE THEN
2408 ROLLBACK TO update_classifications;
2409 END IF;
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2412 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2413 p_error_text => SUBSTRB(SQLERRM,1,240));
2414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2415
2416 WHEN FND_API.G_EXC_ERROR THEN
2417 IF p_commit = FND_API.G_TRUE THEN
2418 ROLLBACK TO update_classifications;
2419 END IF;
2420 x_return_status := 'E';
2421
2422 WHEN OTHERS THEN
2423 IF p_commit = FND_API.G_TRUE THEN
2424 ROLLBACK TO UPDATE_CLASSIFICATIONS;
2425 END IF;
2426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2427 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2428 p_procedure_name => 'UPDATE_CLASSIFICATIONS',
2429 p_error_text => SUBSTRB(SQLERRM,1,240));
2430 raise;
2431
2432 END UPDATE_CLASSIFICATIONS;
2433
2434
2435
2436 -- API name : delete_classifications
2437 -- Type : Public
2438 -- Pre-reqs : None.
2439 -- Parameters :
2440 -- p_api_version IN NUMBER Optional Default = 1.0
2441 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2442 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2443 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2444 -- validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2445 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2446 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2447 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2448 -- p_object_id IN NUMBER Required
2449 -- p_object_type IN VARCHAR2 Required
2450 -- p_class_category IN VARCHAR2 Required
2451 -- p_class_code IN VARCHAR2 Required
2452 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
2453 -- x_return_status OUT VARCHAR2 Required
2454 -- x_msg_count OUT NUMBER Required
2455 -- x_msg_data OUT VARCHAR2 Required
2456 --
2457 -- History
2458 --
2459 -- 18-AUG-2000 -- Sakthi/William - Created.
2460 --
2461 --
2462 PROCEDURE DELETE_CLASSIFICATIONS
2463 (p_api_version IN NUMBER := 1.0 ,
2464 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2465 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2466 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2467 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2468 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2469 p_debug_mode IN VARCHAR2 := 'N',
2470 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2471 p_object_id IN NUMBER ,
2472 p_object_type IN VARCHAR2 ,
2473 p_class_category IN VARCHAR2 ,
2474 p_class_code IN VARCHAR2 ,
2475 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM ,
2476 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2477 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2478 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2479 IS
2480 l_api_name CONSTANT VARCHAR(30) := 'delete_classifications';
2481 l_api_version CONSTANT NUMBER := 1.0;
2482 l_project_id pa_projects.project_id%TYPE;
2483 l_dummy VARCHAR2(1);
2484 l_delete_flag VARCHAR2(1) := 'N';
2485 l_return_status VARCHAR2(250);
2486 l_error_msg_code VARCHAR2(250);
2487 l_msg_count NUMBER;
2488 l_msg_data VARCHAR2(250);
2489 l_err_code VARCHAR2(250);
2490 l_err_stage VARCHAR2(250);
2491 l_err_stack VARCHAR2(250);
2492 l_data VARCHAR2(250);
2493 l_msg_index_out NUMBER;
2494 l_row_id VARCHAR2(250);
2495 BEGIN
2496
2497 -- Please all debug message into this procedure.
2498
2499 IF p_commit = FND_API.G_TRUE THEN
2500 SAVEPOINT delete_classifications;
2501 END IF;
2502
2503 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.delete_classifications');
2504
2505 if (p_debug_mode = 'Y') then
2506 pa_debug.debug('delete_classifications PUB : Checking API Compatibility');
2507 end if;
2508 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2509 p_api_version,
2510 l_api_name,
2511 g_pkg_name)
2512 THEN
2513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514 END IF;
2515
2516 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2517 fnd_msg_pub.initialize;
2518 end if;
2519
2520 x_return_status := FND_API.G_RET_STS_SUCCESS;
2521
2522 -- write program logic here
2523
2524 if (p_debug_mode = 'Y') then
2525 pa_debug.debug('delete_classifications PUB : Calling private API delete_classifcations');
2526 end if;
2527 PA_PROJECTS_MAINT_PVT.DELETE_CLASSIFICATIONS
2528 (
2529 p_commit => FND_API.G_FALSE,
2530 p_validate_only => p_validate_only,
2531 p_validation_level => p_validation_level,
2532 p_calling_module => p_calling_module,
2533 p_debug_mode => p_debug_mode,
2534 p_max_msg_count => p_max_msg_count,
2535 p_object_id => p_object_id,
2536 p_object_type => p_object_type,
2537 p_class_category => p_class_category,
2538 p_class_code => p_class_code,
2539 p_record_version_number => p_record_version_number,
2540 x_return_status => l_return_status,
2541 x_msg_count => l_msg_count,
2542 x_msg_data => l_msg_data);
2543
2544 if (p_debug_mode = 'Y') then
2545 pa_debug.debug('delete_classifications PUB : Checking message count');
2546 end if;
2547 l_msg_count := FND_MSG_PUB.count_msg;
2548 If l_msg_count > 0 THEN
2549 x_msg_count := l_msg_count;
2550 If l_msg_count = 1 THEN
2551 pa_interface_utils_pub.get_messages
2552 (p_encoded => FND_API.G_TRUE ,
2553 p_msg_index => 1,
2554 p_msg_count => l_msg_count ,
2555 p_msg_data => l_msg_data,
2556 p_data => l_data,
2557 p_msg_index_out => l_msg_index_out );
2558 x_msg_data := l_data;
2559 End if;
2560 RAISE FND_API.G_EXC_ERROR;
2561 End if;
2562 /*
2563 l_msg_count := FND_MSG_PUB.count_msg;
2564
2565 IF l_msg_count = 1 THEN
2566 x_msg_count := l_msg_count;
2567 x_msg_data := FND_MSG_PUB.get(p_msg_index => 1,
2568 p_encoded => FND_API.G_TRUE);
2569 ELSE
2570 x_msg_count := l_msg_count;
2571 END IF;
2572
2573 IF l_msg_count > 0 THEN
2574 x_return_status := 'E';
2575 RAISE FND_API.G_EXC_ERROR;
2576 END IF;
2577 */
2578 IF FND_API.TO_BOOLEAN(P_COMMIT)
2579 THEN
2580 COMMIT WORK;
2581 END IF;
2582
2583 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2584 IF p_commit = FND_API.G_TRUE THEN
2585 ROLLBACK TO delete_classifications;
2586 END IF;
2587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2588 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2589 p_procedure_name => 'DELETE_CLASSIFICATIONS',
2590 p_error_text => SUBSTRB(SQLERRM,1,240));
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592
2593 WHEN FND_API.G_EXC_ERROR THEN
2594 IF p_commit = FND_API.G_TRUE THEN
2595 ROLLBACK TO delete_classifications;
2596 END IF;
2597 x_return_status := 'E';
2598
2599 WHEN OTHERS THEN
2600 IF p_commit = FND_API.G_TRUE THEN
2601 ROLLBACK TO delete_classifications;
2602 END IF;
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2604 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2605 p_procedure_name => 'DELETE_CLASSIFICATIONS',
2606 p_error_text => SUBSTRB(SQLERRM,1,240));
2607 raise;
2608
2609 END DELETE_CLASSIFICATIONS;
2610
2611
2612 -- API name : Update_project_staffing_info
2613 -- Type : Public
2614 -- Pre-reqs : None.
2615 -- Parameters :
2616 -- p_api_version IN NUMBER Optional Default = 1.0
2617 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2618 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2619 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2620 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2621 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2622 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2623 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2624 -- p_project_id IN NUMBER Required
2625 -- p_comp_match_weighting IN pa_projects_all.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
2626 -- p_avail_match_weighting IN pa_projects_all.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
2627 -- p_job_level_match_weighting IN pa_projects_all.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
2628 -- p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM
2629 -- p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR
2630 -- p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2631 -- p_search_exp_org_struct_ver_id IN pa_projects_all.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
2632 -- p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
2633 -- p_search_exp_start_org_id IN pa_projects_all.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
2634 -- p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2635 -- p_search_min_candidate_score IN pa_projects_all.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
2636 -- p_enable_auto_cand_nom_flag IN pa_projects_all.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
2637 -- p_record_version_number IN NUMBER Required
2638 -- x_return_status OUT VARCHAR2 Required
2639 -- x_msg_count OUT NUMBER Required
2640 -- x_msg_data OUT VARCHAR2 Required
2641 --
2642 -- History
2643 --
2644 -- 28-SEP-2001 -- hyau - Created.
2645 --
2646 --
2647 PROCEDURE UPDATE_PROJECT_STAFFING_INFO
2648 (p_api_version IN NUMBER := 1.0 ,
2649 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2650 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2651 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2652 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2653 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2654 p_debug_mode IN VARCHAR2 := 'N' ,
2655 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2656 p_project_id IN NUMBER ,
2657 p_comp_match_weighting IN pa_projects_all.COMPETENCE_MATCH_WT%TYPE := FND_API.G_MISS_NUM,
2658 p_avail_match_weighting IN pa_projects_all.availability_match_wt%TYPE := FND_API.G_MISS_NUM,
2659 p_job_level_match_weighting IN pa_projects_all.job_level_match_wt%TYPE := FND_API.G_MISS_NUM,
2660 p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM,
2661 p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR,
2662 p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR,
2663 p_search_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE := FND_API.G_MISS_NUM,
2664 p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR,
2665 p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE := FND_API.G_MISS_NUM,
2666 p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR,
2667 p_search_min_candidate_score IN pa_projects_all.min_cand_score_reqd_for_nom%TYPE := FND_API.G_MISS_NUM,
2668 p_enable_auto_cand_nom_flag IN pa_projects_all.enable_automated_search%TYPE := FND_API.G_MISS_CHAR,
2669 p_record_version_number IN NUMBER ,
2670 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2671 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2672 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2673 IS
2674
2675
2676 l_api_name CONSTANT VARCHAR(30) := 'update_project_staffing_info';
2677 l_api_version CONSTANT NUMBER := 1.0;
2678
2679 l_return_status VARCHAR2(250);
2680 l_error_message_code fnd_new_messages.message_name%TYPE;
2681 l_msg_count NUMBER;
2682 l_msg_data VARCHAR2(250);
2683 l_err_code VARCHAR2(250);
2684 l_err_stage VARCHAR2(250);
2685 l_err_stack VARCHAR2(250);
2686 l_data VARCHAR2(250);
2687 l_msg_index_out NUMBER;
2688 l_dummy VARCHAR2(1);
2689
2690 l_search_exp_org_struct_ver_id pa_projects_all.search_org_hier_id%TYPE;
2691 l_search_country_code pa_projects_all.search_country_code%TYPE;
2692 l_search_exp_start_org_id pa_projects_all.search_starting_org_id%TYPE;
2693
2694 BEGIN
2695
2696 IF p_commit = FND_API.G_TRUE THEN
2697 SAVEPOINT update_project_staffing_info;
2698 END IF;
2699
2700 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_staffing_info');
2701
2702 if (p_debug_mode = 'Y') then
2703 pa_debug.debug('Update_project_staffing_info PUB : Checking API Compatibility');
2704 end if;
2705 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2706 p_api_version,
2707 l_api_name,
2708 g_pkg_name)
2709 THEN
2710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2711 END IF;
2712
2713 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
2714 fnd_msg_pub.initialize;
2715 end if;
2716
2717 x_return_status := FND_API.G_RET_STS_SUCCESS;
2718
2719 --
2720 -- Validate Candidate Score Match Weightings
2721 --
2722 IF (p_comp_match_weighting <> FND_API.G_MISS_NUM AND p_comp_match_weighting IS NOT NULL) OR
2723 (p_avail_match_weighting <> FND_API.G_MISS_NUM AND p_avail_match_weighting IS NOT NULL) OR
2724 (p_job_level_match_weighting <> FND_API.G_MISS_NUM AND p_job_level_match_weighting IS NOT NULL) THEN
2725
2726 IF p_comp_match_weighting < 0 OR
2727 p_comp_match_weighting > 100 OR
2728 p_avail_match_weighting < 0 OR
2729 p_avail_match_weighting > 100 OR
2730 p_job_level_match_weighting < 0 OR
2731 p_job_level_match_weighting > 100 THEN
2732
2733 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2734 ,p_msg_name => 'PA_INVALID_MATCH_WEIGHTING');
2735 x_msg_data := 'PA_INVALID_MATCH_WEIGHTING';
2736 x_return_status := FND_API.G_RET_STS_ERROR;
2737
2738 END IF;
2739
2740 END IF;
2741
2742 --
2743 -- Validate Search Minimum Availiability
2744 --
2745 IF p_search_min_availability <> FND_API.G_MISS_NUM AND p_search_min_availability IS NOT NULL THEN
2746
2747 IF p_search_min_availability < 0 OR
2748 p_search_min_availability > 100 THEN
2749
2750 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2751 ,p_msg_name => 'PA_MIN_AVAIL_INVALID');
2752 x_msg_data := 'PA_MIN_AVAIL_INVALID';
2753 x_return_status := FND_API.G_RET_STS_ERROR;
2754 END IF;
2755 END IF;
2756
2757
2758
2759 --
2760 -- Validate Search Min Candidate Score
2761 --
2762 IF p_search_min_candidate_score <> FND_API.G_MISS_NUM AND p_search_min_candidate_score IS NOT NULL THEN
2763
2764 IF p_search_min_candidate_score < 0 OR
2765 p_search_min_candidate_score > 100 THEN
2766
2767 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2768 ,p_msg_name => 'PA_MIN_CAN_SCORE_INVALID');
2769 x_msg_data := 'PA_MIN_CAN_SCORE_INVALID';
2770 x_return_status := FND_API.G_RET_STS_ERROR;
2771 END IF;
2772 END IF;
2773
2774
2775 l_msg_count := FND_MSG_PUB.count_msg;
2776 If l_msg_count > 0 THEN
2777 x_msg_count := l_msg_count;
2778 If l_msg_count = 1 THEN
2779 pa_interface_utils_pub.get_messages
2780 (p_encoded => FND_API.G_TRUE ,
2781 p_msg_index => 1,
2782 p_msg_count => l_msg_count ,
2783 p_msg_data => l_msg_data,
2784 p_data => l_data,
2785 p_msg_index_out => l_msg_index_out );
2786 x_msg_data := l_data;
2787 End if;
2788 RAISE FND_API.G_EXC_ERROR;
2789 End if;
2790
2791
2792 --
2793 -- Validate Search Org_Hierarchy
2794 --
2795 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
2796
2797 PA_HR_ORG_UTILS.Check_OrgHierName_Or_Id (p_org_hierarchy_version_id => p_search_exp_org_struct_ver_id,
2798 p_org_hierarchy_name => p_search_exp_org_hier_name,
2799 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2800 x_org_hierarchy_version_id => l_search_exp_org_struct_ver_id,
2801 x_return_status => l_return_status,
2802 x_error_msg_code => l_error_message_code);
2803
2804
2805 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2806 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2807 ,p_msg_name => l_error_message_code );
2808 x_msg_data := l_error_message_code;
2809 x_return_status := FND_API.G_RET_STS_ERROR;
2810 ELSE
2811
2812 l_return_status := FND_API.G_MISS_CHAR;
2813 l_error_message_code := FND_API.G_MISS_CHAR;
2814
2815 -- check if the org hierarchy is of the correct type, i.e EXPENDITURES
2816 PA_ORG_UTILS.Check_OrgHierarchy_Type(
2817 p_org_structure_version_id => l_search_exp_org_struct_ver_id,
2818 p_org_structure_type => 'EXPENDITURES',
2819 x_return_status => l_return_status,
2820 x_error_message_code => l_error_message_code);
2821
2822
2823 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2824 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2825 ,p_msg_name => l_error_message_code );
2826
2827 END IF;
2828
2829 END IF;
2830 l_return_status := FND_API.G_MISS_CHAR;
2831 l_error_message_code := FND_API.G_MISS_CHAR;
2832
2833
2834
2835 END IF;
2836
2837 --
2838 -- Validate Search Start Org
2839 --
2840 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
2841 PA_HR_ORG_UTILS.Check_OrgName_Or_Id (p_organization_id => p_search_exp_start_org_id,
2842 p_organization_name => p_search_exp_start_org_name,
2843 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2844 x_organization_id => l_search_exp_start_org_id,
2845 x_return_status => l_return_status,
2846 x_error_msg_code => l_error_message_code);
2847
2848
2849 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2850 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2851 ,p_msg_name => l_error_message_code );
2852 x_msg_data := l_error_message_code;
2853 x_return_status := FND_API.G_RET_STS_ERROR;
2854
2855 ELSE
2856
2857 l_return_status := FND_API.G_MISS_CHAR;
2858 l_error_message_code := FND_API.G_MISS_CHAR;
2859
2860 -- check if the starting org is of the correct type i.e.EXPENDITURES
2861 PA_ORG_UTILS.Check_Org_Type(
2862 p_organization_id => l_search_exp_start_org_id,
2863 p_org_structure_type => 'EXPENDITURES',
2864 x_return_status => l_return_status,
2865 x_error_message_code => l_error_message_code);
2866
2867 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2868 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2869 ,p_msg_name => l_error_message_code );
2870 x_msg_data := l_error_message_code;
2871 x_return_status := FND_API.G_RET_STS_ERROR;
2872 END IF;
2873
2874 END IF;
2875 l_return_status := FND_API.G_MISS_CHAR;
2876 l_error_message_code := FND_API.G_MISS_CHAR;
2877
2878 END IF;
2879
2880 --
2881 -- Validate if Search Start Org is in the Search Org Hierarchy
2882 --
2883 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
2884 IF p_search_exp_start_org_id IS NOT NULL AND p_search_exp_org_struct_ver_id IS NOT NULL THEN
2885
2886 PA_ORG_UTILS.Check_Org_In_OrgHierarchy(
2887 p_organization_id => l_search_exp_start_org_id,
2888 p_org_structure_version_id => l_search_exp_org_struct_ver_id,
2889 p_org_structure_type => 'EXPENDITURES',
2890 x_return_status => l_return_status,
2891 x_error_message_code => l_error_message_code);
2892
2893 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2894 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2895 ,p_msg_name => l_error_message_code );
2896 x_msg_data := l_error_message_code;
2897 x_return_status := FND_API.G_RET_STS_ERROR;
2898
2899 END IF;
2900
2901 END IF;
2902
2903 END IF;
2904
2905 --
2906 -- Validate Search Country
2907 --
2908 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
2909 PA_LOCATION_UTILS.Check_Country_Name_Or_Code(p_country_code => p_search_country_code,
2910 p_country_name => p_search_country_name,
2911 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
2912 x_country_code => l_search_country_code,
2913 x_return_status => l_return_status,
2914 x_error_message_code => l_error_message_code);
2915
2916 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2917 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2918 ,p_msg_name => l_error_message_code );
2919 x_msg_data := l_error_message_code;
2920 x_return_status := FND_API.G_RET_STS_ERROR;
2921 END IF;
2922 END IF;
2923
2924 l_msg_count := FND_MSG_PUB.count_msg;
2925 If l_msg_count > 0 THEN
2926 x_msg_count := l_msg_count;
2927 If l_msg_count = 1 THEN
2928 pa_interface_utils_pub.get_messages
2929 (p_encoded => FND_API.G_TRUE ,
2930 p_msg_index => 1,
2931 p_msg_count => l_msg_count ,
2932 p_msg_data => l_msg_data,
2933 p_data => l_data,
2934 p_msg_index_out => l_msg_index_out );
2935 x_msg_data := l_data;
2936 End if;
2937 RAISE FND_API.G_EXC_ERROR;
2938 End if;
2939
2940
2941 if (p_debug_mode = 'Y') then
2942 pa_debug.debug('update_project_staffing_info PUB : Calling private API update_project_staffing_info');
2943 end if;
2944
2945
2946 PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_STAFFING_INFO
2947 (
2948 p_commit => FND_API.G_FALSE,
2949 p_validate_only => p_validate_only,
2950 p_validation_level => p_validation_level,
2951 p_calling_module => p_calling_module,
2952 p_debug_mode => p_debug_mode,
2953 p_max_msg_count => p_max_msg_count,
2954 p_project_id => p_project_id,
2955 p_comp_match_weighting => p_comp_match_weighting,
2956 p_avail_match_weighting => p_avail_match_weighting,
2957 p_job_level_match_weighting => p_job_level_match_weighting,
2958 p_search_min_availability => p_search_min_availability,
2959 p_search_country_code => l_search_country_code,
2960 p_search_exp_org_struct_ver_id => l_search_exp_org_struct_ver_id,
2961 p_search_exp_start_org_id => l_search_exp_start_org_id,
2962 p_search_min_candidate_score => p_search_min_candidate_score,
2963 p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag,
2964 p_record_version_number => p_record_version_number,
2965 x_return_status => l_return_status,
2966 x_msg_count => l_msg_count,
2967 x_msg_data => l_msg_data);
2968
2969 if (p_debug_mode = 'Y') then
2970 pa_debug.debug('update_project_staffing_info PUB : Checking message count');
2971 end if;
2972 l_msg_count := FND_MSG_PUB.count_msg;
2973 If l_msg_count > 0 THEN
2974 x_msg_count := l_msg_count;
2975 If l_msg_count = 1 THEN
2976 pa_interface_utils_pub.get_messages
2977 (p_encoded => FND_API.G_TRUE ,
2978 p_msg_index => 1,
2979 p_msg_count => l_msg_count ,
2980 p_msg_data => l_msg_data,
2981 p_data => l_data,
2982 p_msg_index_out => l_msg_index_out );
2983 x_msg_data := l_data;
2984 End if;
2985 RAISE FND_API.G_EXC_ERROR;
2986 End if;
2987
2988 IF FND_API.TO_BOOLEAN(P_COMMIT)
2989 THEN
2990 COMMIT WORK;
2991 END IF;
2992
2993 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2994 IF p_commit = FND_API.G_TRUE THEN
2995 ROLLBACK TO update_project_staffing_info;
2996 END IF;
2997 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2998 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
2999 p_error_text => SUBSTRB(SQLERRM,1,240));
3000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3001
3002 WHEN FND_API.G_EXC_ERROR THEN
3003 IF p_commit = FND_API.G_TRUE THEN
3004 ROLLBACK TO update_project_staffing_info;
3005 END IF;
3006 x_return_status := FND_API.G_RET_STS_ERROR;
3007
3008 WHEN OTHERS THEN
3009 IF p_commit = FND_API.G_TRUE THEN
3010 ROLLBACK TO update_project_staffing_info;
3011 END IF;
3012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3013 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3014 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
3015 p_error_text => SUBSTRB(SQLERRM,1,240));
3016 raise;
3017
3018
3019 END UPDATE_PROJECT_STAFFING_INFO;
3020
3021
3022 -- API name : Create_Org_Role
3023 -- Type : Public
3024 -- Pre-reqs : None.
3025 -- Parameters :
3026 -- p_api_version IN NUMBER Required Default = 1.0
3027 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
3028 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
3029 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
3030 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3031 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
3032 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
3033 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
3034 -- p_object_id IN NUMBER Required
3035 -- p_object_type IN VARCHAR2 Required
3036 -- p_project_role_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
3037 -- p_project_role_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3038 -- p_resource_type_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3039 -- p_resource_source_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
3040 -- p_resource_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
3041 -- p_start_date_active IN DATE Required
3042 -- p_scheduled_flag IN VARCHAR2 Optional Default = 'N'
3043 -- p_project_id IN NUMBER Required
3044 -- p_project_end_date IN DATE Optional Default = FND_API.G_MISS_DATE
3045 -- p_end_date_active IN OUT DATE Required
3046 -- x_return_status OUT VARCHAR2 Required
3047 -- x_msg_count OUT NUMBER Required
3048 -- x_msg_data OUT VARCHAR2 Required
3049
3050 PROCEDURE CREATE_ORG_ROLE
3051 ( p_api_version IN NUMBER := 1.0
3052 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3053 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3054 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3055 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3056 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3057 ,p_debug_mode IN VARCHAR2 := 'N'
3058 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
3059 ,p_object_id IN NUMBER
3060 ,p_object_type IN VARCHAR2
3061 ,p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR
3062 ,p_project_role_id IN NUMBER := FND_API.G_MISS_NUM
3063 ,p_resource_type_id IN NUMBER := FND_API.G_MISS_NUM
3064 ,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
3065 ,p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR
3066 ,p_start_date_active IN DATE
3067 ,p_scheduled_flag IN VARCHAR2 := 'N'
3068 ,p_project_id IN NUMBER
3069 ,p_project_end_date IN DATE := FND_API.G_MISS_DATE
3070 ,p_end_date_active IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
3071 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3072 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3073 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3074 )
3075 IS
3076 l_api_name CONSTANT VARCHAR(30) := 'Create_Org_Role';
3077 l_api_version CONSTANT NUMBER := 1.0;
3078
3079 l_customer_id NUMBER;
3080 l_record_version_number NUMBER;
3081 l_project_party_id NUMBER;
3082 l_start_date_active DATE;
3083 l_resource_source_id NUMBER;
3084 l_resource_type_id NUMBER;
3085 l_new_project_party_id NUMBER;
3086 l_resource_id NUMBER;
3087 l_wf_type VARCHAR2(2000);
3088 l_wf_item_type VARCHAR2(2000);
3089 l_wf_process VARCHAR2(2000);
3090 l_assignment_id NUMBER;
3091 l_return_status VARCHAR2(1);
3092 l_error_message_code VARCHAR2(250);
3093 l_msg_count NUMBER;
3094 l_msg_data VARCHAR2(250);
3095 l_data VARCHAR2(250);
3096 l_msg_index_out NUMBER;
3097 l_role_class VARCHAR2(30);
3098 l_project_role_id NUMBER;
3099
3100 CURSOR l_check_existing_customers_csr(c_party_id NUMBER)
3101 IS
3102 SELECT ppc.customer_id, ppp.record_version_number, ppc.project_party_id
3103 FROM PA_PROJECT_CUSTOMERS ppc,
3104 PA_PROJECT_PARTIES ppp
3105 WHERE ppc.project_id = p_project_id
3106 AND ppc.project_party_id = ppp.project_party_id
3107 AND ppp.resource_source_id = c_party_id
3108 AND ppp.resource_type_id = 112;
3109
3110 CURSOR l_get_role_class_csr(c_project_role_id NUMBER)
3111 IS
3112 SELECT role_party_class
3113 FROM pa_project_role_types_b /* Bug#2788814-Changed pa_project_role_types_vl to pa_project_role_types_b */
3114 WHERE project_role_id = c_project_role_id;
3115
3116 BEGIN
3117 pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.Create_Org_Role');
3118
3119 if (p_debug_mode = 'Y') then
3120 pa_debug.debug('PA_PROJECTS_MAINT_PUB.Create_Org_Role BEGIN');
3121 end if;
3122
3123 if p_commit = FND_API.G_TRUE then
3124 savepoint create_org_role;
3125 end if;
3126
3127 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3128 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3129 end if;
3130
3131 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
3132 FND_MSG_PUB.initialize;
3133 end if;
3134
3135 if (p_debug_mode = 'Y') then
3136 pa_debug.debug('Check if the role to be created is in CUSTOMER role class...');
3137 end if;
3138
3139 l_role_class := NULL;
3140
3141 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
3142 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
3143 else
3144 l_project_role_id := p_project_role_id;
3145 end if;
3146
3147 OPEN l_get_role_class_csr(l_project_role_id);
3148 FETCH l_get_role_class_csr INTO l_role_class;
3149 CLOSE l_get_role_class_csr;
3150
3151 if l_role_class = 'CUSTOMER' then
3152
3153 if (p_debug_mode = 'Y') then
3154 pa_debug.debug('Name to ID conversion for the party...');
3155 end if;
3156
3157 if p_start_date_active is null or p_start_date_active = FND_API.G_MISS_DATE then
3158 l_start_date_active := trunc(sysdate);
3159 else
3160 l_start_date_active := p_start_date_active;
3161 end if;
3162
3163 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id ( p_resource_id => p_resource_source_id
3164 ,p_resource_type_id => p_resource_type_id
3165 ,p_resource_name => p_resource_name
3166 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
3167 ,p_date => l_start_date_active
3168 ,x_resource_id => l_resource_source_id
3169 ,x_resource_type_id => l_resource_type_id
3170 ,x_return_status => l_return_status
3171 ,x_error_message_code => l_error_message_code);
3172
3173 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3174 fnd_message.set_name('PA',l_error_message_code);
3175 fnd_msg_pub.add;
3176 end if;
3177
3178 l_msg_count := FND_MSG_PUB.count_msg;
3179 if l_msg_count > 0 then
3180 x_msg_count := l_msg_count;
3181 if x_msg_count = 1 then
3182 pa_interface_utils_pub.get_messages
3183 (p_encoded => FND_API.G_TRUE,
3184 p_msg_index => 1,
3185 p_msg_count => l_msg_count,
3186 p_msg_data => l_msg_data,
3187 p_data => l_data,
3188 p_msg_index_out => l_msg_index_out);
3189 x_msg_data := l_data;
3190 end if;
3191 raise FND_API.G_EXC_ERROR;
3192 end if;
3193
3194 if (p_debug_mode = 'Y') then
3195 pa_debug.debug('Check if there is an existing customer for this org...');
3196 end if;
3197
3198 l_customer_id := null;
3199 OPEN l_check_existing_customers_csr(l_resource_source_id);
3200 FETCH l_check_existing_customers_csr INTO l_customer_id, l_record_version_number, l_project_party_id;
3201 CLOSE l_check_existing_customers_csr;
3202
3203 if l_customer_id is not null then
3204
3205 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
3206 -- Update the project_party_id col in pa_project_customers to null
3207 UPDATE pa_project_customers
3208 SET project_party_id = null
3209 WHERE project_id = p_project_id
3210 AND customer_id = l_customer_id;
3211 end if;
3212
3213 -- Delete the existing role
3214 PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY
3215 ( p_init_msg_list => p_init_msg_list
3216 ,p_validate_only => p_validate_only
3217 ,p_validation_level => p_validation_level
3218 ,p_debug_mode => p_debug_mode
3219 ,p_calling_module => p_calling_module
3220 ,p_record_version_number => l_record_version_number
3221 ,p_project_id => p_project_id
3222 ,p_project_party_id => l_project_party_id
3223 ,x_return_status => l_return_status
3224 ,x_msg_count => l_msg_count
3225 ,x_msg_data => l_msg_data );
3226
3227 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3228 x_msg_count := FND_MSG_PUB.count_msg;
3229 if x_msg_count = 1 then
3230 pa_interface_utils_pub.get_messages
3231 (p_encoded => FND_API.G_TRUE,
3232 p_msg_index => 1,
3233 p_msg_count => l_msg_count,
3234 p_msg_data => l_msg_data,
3235 p_data => l_data,
3236 p_msg_index_out => l_msg_index_out);
3237 x_msg_data := l_data;
3238 end if;
3239 raise FND_API.G_EXC_ERROR;
3240 end if;
3241
3242 end if;
3243 end if;
3244
3245 -- Now create the org role
3246 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
3247 ( p_validate_only => p_validate_only
3248 ,p_init_msg_list => p_init_msg_list
3249 ,p_validation_level => p_validation_level
3250 ,p_debug_mode => p_debug_mode
3251 ,p_calling_module => p_calling_module
3252 ,p_object_id => p_object_id
3253 ,p_object_type => p_object_type
3254 ,p_project_role_type => p_project_role_type
3255 ,p_project_role_id => p_project_role_id
3256 ,p_resource_type_id => p_resource_type_id
3257 ,p_resource_source_id => p_resource_source_id
3258 ,p_resource_name => p_resource_name
3259 ,p_start_date_active => p_start_date_active
3260 ,p_scheduled_flag => p_scheduled_flag
3261 ,p_project_id => p_project_id
3262 ,p_project_end_date => p_project_end_date
3263 ,p_end_date_active => p_end_date_active
3264 ,x_project_party_id => l_new_project_party_id
3265 ,x_resource_id => l_resource_id
3266 ,x_wf_type => l_wf_type
3267 ,x_wf_item_type => l_wf_item_type
3268 ,x_wf_process => l_wf_process
3269 ,x_assignment_id => l_assignment_id
3270 ,x_return_status => l_return_status
3271 ,x_msg_count => l_msg_count
3272 ,x_msg_data => l_msg_data );
3273
3274 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3275 x_msg_count := FND_MSG_PUB.count_msg;
3276 if x_msg_count = 1 then
3277 pa_interface_utils_pub.get_messages
3278 (p_encoded => FND_API.G_TRUE,
3279 p_msg_index => 1,
3280 p_msg_count => l_msg_count,
3281 p_msg_data => l_msg_data,
3282 p_data => l_data,
3283 p_msg_index_out => l_msg_index_out);
3284 x_msg_data := l_data;
3285 end if;
3286 raise FND_API.G_EXC_ERROR;
3287 end if;
3288
3289 -- Now update the customer record with the new project_party_id
3290 if l_role_class = 'CUSTOMER' then
3291 if l_customer_id is not null then
3292 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
3293 -- Update the project_party_id col in pa_project_customers to null
3294 UPDATE pa_project_customers
3295 SET project_party_id = l_new_project_party_id
3296 WHERE project_id = p_project_id
3297 AND customer_id = l_customer_id;
3298 end if;
3299 end if;
3300 end if;
3301
3302 x_return_status := FND_API.G_RET_STS_SUCCESS;
3303
3304 if p_commit = FND_API.G_TRUE then
3305 commit work;
3306 end if;
3307
3308 if (p_debug_mode = 'Y') then
3309 pa_debug.debug('PA_PROJECTS_MAINT_PUB.Create_Org_Role END');
3310 end if;
3311
3312 EXCEPTION
3313 when FND_API.G_EXC_ERROR then
3314 if p_commit = FND_API.G_TRUE then
3315 rollback to create_org_role;
3316 end if;
3317 x_return_status := FND_API.G_RET_STS_ERROR;
3318 when FND_API.G_EXC_UNEXPECTED_ERROR then
3319 if p_commit = FND_API.G_TRUE then
3320 rollback to create_org_role;
3321 end if;
3322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3323 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3324 p_procedure_name => 'Create_Org_Role',
3325 p_error_text => SUBSTRB(SQLERRM,1,240));
3326 when OTHERS then
3327 if p_commit = FND_API.G_TRUE then
3328 rollback to create_org_role;
3329 end if;
3330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3332 p_procedure_name => 'Create_Org_Role',
3333 p_error_text => SUBSTRB(SQLERRM,1,240));
3334 raise;
3335 END CREATE_ORG_ROLE;
3336
3337 -- Procedure : project_status_change
3338 -- Type : PUBLIC
3339 -- Purpose : This a wrapper API for calling handle_project_status_change and is used from the
3340 -- Project Change Status Page in self-service
3341 -- Note :
3342 -- Parameters Type Required Description and Purpose
3343 -- --------------------------- ------ -------- --------------------------------------------------------
3344 -- p_project_id NUMBER Y The project id
3345 -- p_new_status_code VARCHAR2 Y The new project status
3346 -- p_comment VARCAHR2 N Comment enterd while changing project status
3347 PROCEDURE project_status_change ( p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3348 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3349 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3350 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3351 ,p_debug_mode IN VARCHAR2 := 'N'
3352 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3353 ,p_project_id IN NUMBER
3354 ,p_new_status_code IN VARCHAR2
3355 ,p_comment IN VARCHAR2
3356 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3357 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3358 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3359 ) IS
3360 CURSOR cur_get_project_details IS
3361 SELECT project_status_code
3362 ,project_type
3363 ,start_date
3364 ,closed_date
3365 ,public_sector_flag
3366 ,attribute_category
3367 ,attribute1
3368 ,attribute2
3369 ,attribute3
3370 ,attribute4
3371 ,attribute5
3372 ,attribute6
3373 ,attribute7
3374 ,attribute8
3375 ,attribute9
3376 ,attribute10
3377 ,pm_product_code
3378 ,segment1 -- 3671408 added column to retrieve project number value
3379 FROM pa_projects_all
3380 WHERE project_id = p_project_id;
3381
3382 CURSOR cur_get_project_status_code( c_project_id IN NUMBER ) IS
3383 SELECT project_status_code
3384 FROM pa_projects_all
3385 WHERE project_id = c_project_id;
3386
3387 CURSOR cur_get_system_status(c_status_code IN VARCHAR2) IS
3388 SELECT pps.project_system_status_code
3389 FROM pa_project_statuses pps
3390 WHERE pps.project_status_code = nvl(c_status_code,' ');
3391
3392 Invalid_Arg_Exc EXCEPTION;
3393 l_rec_project_details cur_get_project_details%ROWTYPE;
3394
3395 l_old_project_status VARCHAR2(30);
3396 l_old_sys_status VARCHAR2(30);
3397 l_verify_ok_flag VARCHAR2(1);
3398 l_warnings_only_flag VARCHAR2(1);
3399 l_wf_enabled_flag VARCHAR2(1);
3400 l_err_stage VARCHAR2(2000);
3401 l_err_stack VARCHAR2(2000);
3402 l_err_code VARCHAR2(250);
3403 l_wf_status_code VARCHAR2(30);
3404
3405 -- Declared for 4364878
3406 l_new_sys_status VARCHAR2(30);
3407
3408 l_debug_mode VARCHAR2(1);
3409 l_msg_count NUMBER;
3410 l_msg_data VARCHAR2(2000);
3411 l_data VARCHAR2(2000);
3412 l_msg_index_out NUMBER;
3413 l_calling_module VARCHAR2(50); -- for BUG# 6661144
3414 l_abort_flag VARCHAR2(1) :='N'; -- for BUG# 6661144
3415
3416 l_debug_level2 CONSTANT NUMBER := 2;
3417 l_debug_level3 CONSTANT NUMBER := 3;
3418 l_debug_level4 CONSTANT NUMBER := 4;
3419 l_debug_level5 CONSTANT NUMBER := 5;
3420 BEGIN
3421
3422 x_msg_count := 0;
3423 x_return_status := FND_API.G_RET_STS_SUCCESS;
3424 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3425
3426 IF (p_commit = FND_API.G_TRUE) THEN
3427 SAVEPOINT project_status_change;
3428 END IF;
3429
3430 IF p_init_msg_list = FND_API.G_TRUE THEN
3431 FND_MSG_PUB.initialize;
3432 END IF;
3433
3434 IF l_debug_mode = 'Y' THEN
3435 PA_DEBUG.set_curr_function( p_function => 'project_status_change',
3436 p_debug_mode => l_debug_mode );
3437 END IF;
3438
3439 IF l_debug_mode = 'Y' THEN
3440 Pa_Debug.g_err_stage:= 'Printing Input parameters';
3441 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3442 l_debug_level3);
3443
3444 Pa_Debug.WRITE(G_PKG_NAME,'p_project_id'||':'||p_project_id,
3445 l_debug_level3);
3446
3447 Pa_Debug.WRITE(G_PKG_NAME,'p_new_status_code'||':'||p_new_status_code,
3448 l_debug_level3);
3449 END IF;
3450
3451 IF l_debug_mode = 'Y' THEN
3452 Pa_Debug.g_err_stage:= 'Validating Input parameters';
3453 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3454 l_debug_level3);
3455 END IF;
3456
3457 IF ( ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM ) AND
3458 ( p_new_status_code IS NULL OR p_new_status_code = FND_API.G_MISS_CHAR )
3459 )
3460 THEN
3461 IF l_debug_mode = 'Y' THEN
3462 Pa_Debug.g_err_stage:= 'PA_PROJECTS_MAINT_PUB : project_status_change :
3463 p_project_id, p_new_status_code are NULL';
3464 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3465 l_debug_level3);
3466 END IF;
3467 RAISE Invalid_Arg_Exc;
3468 END IF;
3469
3470 l_calling_module := p_calling_module;
3471 IF (p_calling_module = 'SSO_ABORT') THEN -- for BUG# 6661144
3472 l_calling_module := 'SELF_SERVICE';
3473 l_abort_flag := 'Y';
3474 END IF;
3475
3476 IF ( (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM ) AND
3477 (p_new_status_code IS NOT NULL AND p_new_status_code <> FND_API.G_MISS_CHAR )
3478 ) THEN
3479
3480 --Fetch the (old) project status before changing it
3481 OPEN cur_get_project_status_code(p_project_id);
3482 FETCH cur_get_project_status_code INTO l_old_project_status;
3483 CLOSE cur_get_project_status_code;
3484
3485 --Fetch the (old) project system status before changing it
3486 OPEN cur_get_system_status(l_old_project_status);
3487 FETCH cur_get_system_status INTO l_old_sys_status;
3488 CLOSE cur_get_system_status;
3489
3490 OPEN cur_get_project_details;
3491 FETCH cur_get_project_details INTO l_rec_project_details;
3492 CLOSE cur_get_project_details;
3493
3494 /* <**A**> Start Bug 4364878 */
3495 OPEN cur_get_system_status(p_new_status_code);
3496 FETCH cur_get_system_status INTO l_new_sys_status;
3497 CLOSE cur_get_system_status;
3498 /* <**A**> End Bug 4364878 */
3499
3500 -- Bug 4565023: Begin
3501 IF (l_debug_mode = 'Y') THEN
3502 Pa_Debug.WRITE(G_PKG_NAME,'Calling ALLOW_STATUS_CHANGE', l_debug_level3);
3503 END IF;
3504
3505 IF (PA_PROJECT_STUS_UTILS.ALLOW_STATUS_CHANGE(l_old_project_status,p_new_status_code) = 'N') THEN
3506 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3507 p_msg_name => 'PA_STATUS_CANT_CHANGE');
3508 RAISE FND_API.G_EXC_ERROR;
3509 END IF;
3510 -- Bug 4565023: End
3511
3512 IF (l_debug_mode = 'Y') THEN
3513 Pa_Debug.WRITE(G_PKG_NAME,'Calling HANDLE_PROJECT_STATUS_CHANGE', l_debug_level3);
3514 END IF;
3515
3516 --The following API call will verify the projects status change
3517 PA_PROJECT_STUS_UTILS.HANDLE_PROJECT_STATUS_CHANGE
3518 ( x_calling_module => L_calling_module
3519 ,X_project_id => p_project_id
3520 ,X_old_proj_status_code => l_rec_project_details.project_status_code
3521 ,X_new_proj_status_code => p_new_status_code
3522 ,X_project_type => l_rec_project_details.project_type
3523 ,X_project_start_date => l_rec_project_details.start_date
3524 ,X_project_end_date => l_rec_project_details.closed_date
3525 ,X_public_sector_flag => l_rec_project_details.public_sector_flag
3526 ,X_attribute_category => l_rec_project_details.attribute_category
3527 ,X_attribute1 => l_rec_project_details.attribute1
3528 ,X_attribute2 => l_rec_project_details.attribute2
3529 ,X_attribute3 => l_rec_project_details.attribute3
3530 ,X_attribute4 => l_rec_project_details.attribute4
3531 ,X_attribute5 => l_rec_project_details.attribute5
3532 ,X_attribute6 => l_rec_project_details.attribute6
3533 ,X_attribute7 => l_rec_project_details.attribute7
3534 ,X_attribute8 => l_rec_project_details.attribute8
3535 ,X_attribute9 => l_rec_project_details.attribute9
3536 ,X_attribute10 => l_rec_project_details.attribute10
3537 ,X_pm_product_code => l_rec_project_details.pm_product_code
3538 ,x_init_msg => 'N'
3539 ,x_verify_ok_flag => l_verify_ok_flag
3540 ,x_wf_enabled_flag => l_wf_enabled_flag
3541 ,X_err_stage => l_err_stage
3542 ,X_err_stack => l_err_stack
3543 ,x_err_msg_count => l_msg_count
3544 ,x_warnings_only_flag => l_warnings_only_flag );
3545
3546 IF (l_debug_mode = 'Y') THEN
3547 Pa_Debug.WRITE(G_PKG_NAME,'l_verify_ok_flag ='||l_verify_ok_flag||' l_wf_enabled_flag ='||l_wf_enabled_flag,
3548 l_debug_level3);
3549 END IF;
3550
3551 IF l_verify_ok_flag <> 'Y' THEN
3552 x_msg_data := l_err_stack||'-> '||l_err_stage;
3553 RAISE FND_API.G_EXC_ERROR;
3554 ELSIF l_verify_ok_flag = 'Y' THEN
3555 -- Moved this block so that the comment is available in the table before the
3556 -- workflow is initiated (Bug 6720288)
3557 DECLARE
3558 l_rowid VARCHAR2(255);
3559 l_obj_status_change_id NUMBER;
3560 BEGIN
3561 SELECT pa_obj_status_changes_s.NEXTVAL INTO l_obj_status_change_id
3562 FROM dual;
3563 /* 4364878 : Moved this code to Block tagged with <**A**>
3564 OPEN cur_get_system_status(p_new_status_code);
3565 FETCH cur_get_system_status INTO l_new_sys_status;
3566 CLOSE cur_get_system_status;
3567 Moved this code to Block tagged with <**A**> */
3568
3569 IF (l_debug_mode = 'Y') THEN
3570 pa_debug.debug('Calling PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW');
3571 END IF;
3572
3573 --For inserting status change comment into the status history table
3574 PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
3575 ( X_ROWID => l_rowid,
3576 X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
3577 X_OBJECT_TYPE => 'PA_PROJECTS',
3578 X_OBJECT_ID => p_project_id,
3579 X_STATUS_TYPE => 'PROJECT',
3580 X_NEW_PROJECT_STATUS_CODE => p_new_status_code,
3581 X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
3582 X_OLD_PROJECT_STATUS_CODE => l_old_project_status,
3583 X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sys_status,
3584 X_CHANGE_COMMENT => p_comment,
3585 X_LAST_UPDATED_BY => fnd_global.user_id,
3586 X_CREATED_BY => fnd_global.user_id,
3587 X_CREATION_DATE => sysdate,
3588 X_LAST_UPDATE_DATE => sysdate,
3589 X_LAST_UPDATE_LOGIN => fnd_global.user_id );
3590
3591 IF (l_debug_mode = 'Y') THEN
3592 pa_debug.debug('Updated PA_PROJECTS_ALL for status history');
3593 END IF;
3594
3595 /*Stubbed Out Auto Initiate Demand On Project Approval Functionality
3596 Bug 3819086 */
3597 /* Bug 3611598 Start of Changes by avaithia on 05-May-2004
3598 Included Call to wrapper API to automatically Initiate Demand when the Project Status
3599 is changed to 'Approved' (provided workflow is not enabled)*/
3600 /*Start of Commenting Bug 3819086 */
3601 /*IF nvl(l_new_sys_status,'-99') = 'APPROVED' AND nvl(l_wf_enabled_flag,'N') = 'N' THEN
3602 PA_ACTIONS_PUB.RUN_ACTION_CONC_PROCESS_WRP
3603 (p_project_id => p_project_id
3604 -- 3671408 added IN parameter, passing retrieve project_number
3605 ,p_project_number => l_rec_project_details.segment1
3606 ,x_return_status => x_return_status
3607 ,x_msg_count => x_msg_count
3608 ,x_msg_data => x_msg_data
3609 );
3610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3611 RAISE FND_API.G_EXC_ERROR ;
3612 END IF;
3613 END IF;*/
3614 /* End of Changes for Bug 3611598 */
3615 /*End of Commenting Bug 3819086 */
3616
3617 END;
3618
3619 SELECT wf_status_code INTO l_wf_status_code
3620 FROM PA_PROJECTS_ALL
3621 WHERE project_id = p_project_id;
3622
3623 IF (l_debug_mode = 'Y') THEN
3624 Pa_Debug.WRITE(G_PKG_NAME,'l_wf_status_code ='||l_wf_status_code,
3625 l_debug_level3);
3626 END IF;
3627
3628 IF (l_abort_flag='Y') THEN -- for BUG# 6661144
3629
3630 -- 4364878 : if new system status corresponds to CLOSED
3631 -- Then Populate the closed date as sysdate
3632
3633 IF l_new_sys_status = 'CLOSED' THEN
3634 UPDATE pa_projects_all
3635 SET wf_status_code = NULL
3636 ,project_status_code = p_new_status_code
3637 ,closed_date = sysdate
3638 ,last_update_date = sysdate
3639 ,last_updated_by = fnd_global.user_id
3640 ,last_update_login = fnd_global.login_id
3641 WHERE project_id = p_project_id;
3642 ELSE
3643
3644 UPDATE pa_projects_all
3645 SET wf_status_code = NULL
3646 ,project_status_code = p_new_status_code --Added for bug 3818322
3647 /*4364878 : Included update of who columns also*/
3648 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3649 ,last_update_date = sysdate
3650 ,last_updated_by = fnd_global.user_id
3651 ,last_update_login = fnd_global.login_id
3652 WHERE project_id = p_project_id;
3653 END IF;
3654
3655 --Start the workflow for the project
3656
3657
3658
3659 ELSIF l_wf_status_code IS NULL THEN -- Made ELSEIF against the existing IF, for BUG# 6661144
3660 IF l_wf_enabled_flag = 'Y' THEN
3661 -- Update wf status in pa_projects_all
3662
3663 -- 4364878 : if new system status corresponds to CLOSED
3664 -- Then Populate the closed date as sysdate
3665
3666 If l_new_sys_status = 'CLOSED' THEN
3667 UPDATE pa_projects_all
3668 SET wf_status_code = 'IN_ROUTE'
3669 ,project_status_code = p_new_status_code
3670 ,closed_date = sysdate
3671 ,last_update_date = sysdate
3672 ,last_updated_by = fnd_global.user_id
3673 ,last_update_login = fnd_global.login_id
3674 WHERE project_id = p_project_id;
3675 ELSE
3676
3677 UPDATE pa_projects_all
3678 SET wf_status_code = 'IN_ROUTE'
3679 ,project_status_code = p_new_status_code --Added for bug 3818322
3680 /*4364878 : Included update of who columns also*/
3681 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3682 ,last_update_date = sysdate
3683 ,last_updated_by = fnd_global.user_id
3684 ,last_update_login = fnd_global.login_id
3685 WHERE project_id = p_project_id;
3686 END IF;
3687
3688 --Start the workflow for the project
3689 pa_project_wf.start_project_wf
3690 ( p_project_id
3691 , l_err_stack
3692 , l_err_stage
3693 , l_err_code );
3694
3695 IF l_err_code <> 0 THEN
3696 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3697 p_msg_name => l_err_stage);
3698 x_msg_data := l_err_stack||'-> '||l_err_stage;
3699 RAISE FND_API.G_EXC_ERROR;
3700 END IF;
3701 END IF;
3702 ELSE
3703 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3704 p_msg_name => 'PA_STATUS_CANT_CHANGE');
3705 RAISE FND_API.G_EXC_ERROR;
3706 END IF;
3707
3708 --Uncommented the foll. code and added IF condition for bug 3940861
3709 --(had been commented for 3818322)
3710 IF l_wf_enabled_flag = 'N' AND l_abort_flag='N' THEN -- Added the AND condition for BUG# 6661144
3711
3712 -- 4364878 : if new system status corresponds to CLOSED
3713 -- Then Populate the closed date as sysdate
3714 If l_new_sys_status = 'CLOSED' THEN
3715
3716 UPDATE pa_projects_all
3717 SET project_status_code = p_new_status_code
3718 ,closed_date = sysdate
3719 ,last_update_date = sysdate
3720 ,last_updated_by = fnd_global.user_id
3721 ,last_update_login = fnd_global.login_id
3722 WHERE project_id = p_project_id ;
3723
3724 Else
3725
3726 UPDATE pa_projects_all
3727 SET project_status_code = p_new_status_code
3728 /*4364878 : Included update of who columns also*/
3729 ,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
3730 ,last_update_date = sysdate
3731 ,last_updated_by = fnd_global.user_id
3732 ,last_update_login = fnd_global.login_id
3733 WHERE project_id = p_project_id ;
3734
3735 End if;
3736 END IF;
3737 END IF;
3738
3739
3740 END IF;--IF p_project_id IS NOT NULL
3741
3742 EXCEPTION
3743
3744 WHEN FND_API.G_EXC_ERROR THEN
3745
3746 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3747
3748 IF p_commit = FND_API.G_TRUE THEN
3749 ROLLBACK TO project_status_change;
3750 END IF;
3751
3752 l_msg_count := Fnd_Msg_Pub.count_msg;
3753 IF l_msg_count = 1 AND x_msg_data IS NULL
3754 THEN
3755 Pa_Interface_Utils_Pub.get_messages
3756 ( p_encoded => Fnd_Api.G_FALSE
3757 , p_msg_index => 1
3758 , p_msg_count => l_msg_count
3759 , p_msg_data => l_msg_data
3760 , p_data => l_data
3761 , p_msg_index_out => l_msg_index_out);
3762 x_msg_data := l_data;
3763 x_msg_count := l_msg_count;
3764 ELSE
3765 x_msg_count := l_msg_count;
3766 END IF;
3767
3768 IF l_debug_mode = 'Y' THEN
3769 Pa_Debug.reset_curr_function;
3770 END IF;
3771
3772 WHEN Invalid_Arg_Exc THEN
3773 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3774 x_msg_count := 1;
3775 x_msg_data := ' PA_PROJECTS_MAINT_PUB : project_status_change : NULL parameters passed';
3776
3777 IF p_commit = FND_API.G_TRUE THEN
3778 ROLLBACK TO project_status_change;
3779 END IF;
3780
3781 Fnd_Msg_Pub.add_exc_msg
3782 ( p_pkg_name => 'PA_PROJECTS_MAINT_PUB'
3783 , p_procedure_name => 'project_status_change'
3784 , p_error_text => x_msg_data);
3785
3786 IF l_debug_mode = 'Y' THEN
3787 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3788 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3789 l_debug_level5);
3790 Pa_Debug.reset_curr_function;
3791 END IF;
3792 RAISE;
3793
3794 WHEN OTHERS THEN
3795 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3796 x_msg_count := 1;
3797 x_msg_data := SQLERRM;
3798
3799 IF p_commit = FND_API.G_TRUE THEN
3800 ROLLBACK TO project_status_change;
3801 END IF;
3802
3803 Fnd_Msg_Pub.add_exc_msg
3804 ( p_pkg_name => 'PA_PROJECTS_MAINT_PUB'
3805 , p_procedure_name => 'project_status_change'
3806 , p_error_text => x_msg_data);
3807
3808 IF l_debug_mode = 'Y' THEN
3809 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3810 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
3811 l_debug_level5);
3812 Pa_Debug.reset_curr_function;
3813 END IF;
3814 RAISE;
3815
3816 END project_status_change;
3817
3818
3819 END PA_PROJECTS_MAINT_PUB;