1 package body PA_CONTROL_API_PVT as
2 /*$Header: PACIAMVB.pls 120.2 2010/03/24 11:06:16 jravisha ship $*/
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.PA_CONTROL_API_PVT';
5
6
7
8 api_error Exception;
9
10 l_debug_mode VARCHAR2(1);
11 l_debug_level3 CONSTANT NUMBER := 3;
12
13
14
15
16 CURSOR Check_Valid_CI (c_Ci_Id NUMBER) IS
17 SELECT ci_id
18 FROM pa_control_items
19 WHERE ci_id = c_Ci_Id;
20
21 /*
22 Cursor Get_CI_Data.
23 To get the PROJECT_ID, STATUS_CODE, CI_TYPE_CLASS_CODE
24 and RECORD_VERSION_NUMBER for the given Ci_Id.
25 */
26 CURSOR Get_CI_Data (c_Ci_Id NUMBER) IS
27 SELECT ci.project_id,
28 s.project_system_status_code,
29 cib.ci_type_class_code,
30 ci.record_version_number
31 FROM pa_control_items ci,
32 pa_ci_types_b cib,
33 (select project_status_code,
34 project_system_status_code
35 from pa_project_statuses
36 where status_type = 'CONTROL_ITEM') s
37 WHERE ci.ci_id = c_Ci_Id
38 AND ci.ci_type_id = cib.ci_type_id
39 AND ci.status_code = s.project_status_code;
40
41 /*
42 Cursor Check_Workflow_On_CI.
43 To check whether Workflow is running on the Ci_Id that
44 is passed in.
45 */
46 CURSOR Check_Workflow_On_CI (c_Ci_Id NUMBER) IS
47 SELECT ci.ci_id
48 FROM pa_project_statuses pps, pa_control_items ci
49 WHERE pps.status_type = 'CONTROL_ITEM'
50 AND pps.project_status_code = ci.status_code
51 AND pps.enable_wf_flag = 'Y'
52 AND pps.wf_success_status_code is not null
53 AND pps.wf_failure_status_code is not null
54 AND ci.ci_id = c_Ci_Id;
55
56
57 /*The update_impacts procedure will be called from Add_<impact_type>_impact and
58 update_<impact_type>_impact to create the impact, to update the details of impact and also
59 to implement the impact.*/
60 Procedure update_impacts (
61 p_ci_id IN NUMBER := G_PA_MISS_NUM,
62 x_ci_impact_id OUT NOCOPY NUMBER,
63 p_impact_type_code IN VARCHAR2 := G_PA_MISS_CHAR,
64 p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR,
65 p_mode IN VARCHAR2,
66 p_commit IN VARCHAR2 := FND_API.G_FALSE,
67 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
68 p_api_version_number IN NUMBER ,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 )
73 IS
74
75 /*Cursor for possible ci_impacts. */
76 cursor c_impact_types
77 is
78 select distinct
79 pci.ci_id
80 --pci.ci_type_id,
81 --pl.lookup_code impact_type_code,
82 --pl.predefined_flag,
83 --pl.meaning impact_name,
84 --pci.project_id project_id
85 from
86 pa_control_items pci,
87 pa_ci_impact_type_usage pcit,
88 pa_lookups pl
89 where pci.ci_type_id = pcit.ci_type_id
90 and pl.lookup_type = 'PA_CI_IMPACT_TYPES'
91 and decode(pcit.impact_type_code, 'FINPLAN_COST', 'FINPLAN', 'FINPLAN_REVENUE', 'FINPLAN', pcit.impact_type_code) = pl.lookup_code
92 and pl.enabled_flag = 'Y'
93 and Pa_ci_impacts_util.is_render_true(pl.lookup_code, pci.project_id) = 'Y'
94 and ci_id = p_ci_id
95 and impact_type_code = p_impact_type_code;
96
97
98 /*Cursor to check whether the ci_impact_id exits or not.*/
99 cursor c_check_impact_exists
100 is
101 select ci_impact_id ,status_code,description
102 from
103 pa_ci_impacts
104 where ci_id = p_ci_id
105 and impact_type_code = p_impact_type_code;
106
107
108 --Declaring local Variables
109
110 l_status_code pa_ci_impacts.status_code%TYPE := null;
111 l_ci_id pa_control_items.ci_id%TYPE := null;
112 l_impact_type_code pa_ci_impacts.impact_type_code%TYPE;
113 l_project_id pa_control_items.project_id%TYPE;
114 l_ci_type_id pa_ci_types_b.ci_type_id%TYPE;
115 l_cr_status_code pa_control_items.status_code%type;
116 l_ci_type_class_code pa_ci_types_b.ci_type_class_Code%TYPE;
117 l_ci_impact_id pa_ci_impacts.ci_impact_id%TYPE;
118 l_impact_description pa_ci_impacts.description%type;
119 l_impact_description1 pa_ci_impacts.description%type;
120
121
122
123 has_access VARCHAR2(1); --for security check
124 l_mode VARCHAR2(20) := 'INSERT'; --used to call the insert or update method.
125 l_context VARCHAR2(40) := null;
126 l_error_msg VARCHAR2(200);
127 temp VARCHAR2(1);
128 l_version_number NUMBER(15);
129 l_data VARCHAR2(2000);
130 l_msg_data VARCHAR2(2000);
131 l_msg_index_out NUMBER;
132 l_module_name VARCHAR2(200):='PA_CONTROL_API_PVT.UPDATE_IMPACTS';
133 l_msg_count NUMBER := 0;
134
135
136 c_check_impact_exists_rec c_check_impact_exists %rowtype;
137
138 BEGIN
139
140
141 -- initialize the return status to success
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE',fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
144 if l_debug_mode = 'Y' then
145 pa_debug.set_curr_function(p_function =>l_module_name,p_debug_mode => l_debug_mode);
146 end if;
147
148
149 if l_debug_mode = 'Y' then
150 pa_debug.g_err_stage:= 'Start of Update Impacts PVT method.';
151 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
152 end if;
153
154 l_ci_id := p_ci_id;
155 /*Get the control item details- this also checks for Ci_id is Valid or not*/
156 open Get_CI_Data(p_ci_id);
157 fetch Get_CI_Data into l_project_id,l_cr_status_code,l_ci_type_class_code,l_version_number;
158 if Get_CI_Data%notfound then
159 PA_UTILS.Add_Message( p_app_short_name => 'PA'
160 ,p_msg_name => 'PA_CI_INV_CI_ID');
161 if l_debug_mode = 'Y' then
162 pa_debug.g_err_stage:= 'The ci_id is invalid';
163 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
164 end if;
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 close Get_CI_Data;
167 raise FND_API.G_EXC_ERROR;
168 else
169 close Get_CI_Data;
170 end if;
171
172 if l_debug_mode = 'Y' then
173 pa_debug.g_err_stage:= 'After checking whether the CI_ID is valid or not.';
174 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
175 end if;
176
177 /* If the control items is ISSUE, then it doesnot have the impact*/
178 if l_ci_type_class_code = 'ISSUE' then
179 PA_UTILS.add_Message( p_app_short_name => 'PA'
180 ,p_msg_name => 'PA_CI_INV_CI_ID');
181 if l_debug_mode = 'Y' then
182 pa_debug.g_err_stage:= 'The given CI_ID is an ISSUE, does not contain any impact';
183 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
184 end if;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 raise FND_API.G_EXC_ERROR ;
187 end if;
188
189 /*Checking whether given impact_type_code exists in the possible impacts types
190 These possible impacts types are enabled during the creation of control type.*/
191 open c_impact_types;
192 fetch c_impact_types into l_ci_id;
193 if c_impact_types%notfound then
194 PA_UTILS.add_Message( p_app_short_name => 'PA'
195 ,p_msg_name => 'PA_CI_IMPACT_CODE_INVALID');
196 if l_debug_mode = 'Y' then
197 pa_debug.g_err_stage:= 'the ci_imapct_type_code is invalid, This is impact is not enabled for the control item type.';
198 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
199 end if;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 close c_impact_types;
202 raise FND_API.G_EXC_ERROR ;
203 else
204 close c_impact_types;
205 end if;
206
207 if l_debug_mode = 'Y' then
208 pa_debug.g_err_stage:= 'After checking the possible impact type codes for the given ci_id.';
209 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
210 end if;
211
212 /*Check whether the impact exists are or not,if it already exists and the mode is 'INSERT'
213 then it is an error and do the same for update for does not exist case*/
214 open c_check_impact_exists;
215 fetch c_check_impact_exists into c_check_impact_exists_rec ;
216 if c_check_impact_exists%found then
217 l_ci_impact_id := c_check_impact_exists_rec.ci_impact_id;
218 l_impact_description1 := c_check_impact_exists_rec.description;
219 if p_mode = 'INSERT' then
220 PA_UTILS.add_Message( p_app_short_name => 'PA'
221 ,p_msg_name => 'PA_CI_IMPACT_EXIST');
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 if l_debug_mode = 'Y' then
224 pa_debug.g_err_stage:= 'Cannot be inserted as the record already exists for the given impact type code';
225 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
226 end if;
227 close c_check_impact_exists;
228 raise FND_API.G_EXC_ERROR;
229 else
230 close c_check_impact_exists;
231 end if;
232 else
233 if p_mode = 'UPDATE' then
234 PA_UTILS.add_Message( p_app_short_name => 'PA'
235 ,p_msg_name => 'PA_NO_CI_IMPACT');
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 if l_debug_mode = 'Y' then
238 pa_debug.g_err_stage:= 'Cannot update the impact, as there is no record exists';
239 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
240 end if;
241 close c_check_impact_exists;
242 raise FND_API.G_EXC_ERROR;
243 else
244 close c_check_impact_exists;
245 end if;
246 end if; --end for if c_check_impact_exists
247
248
249 if l_debug_mode = 'Y' then
250 pa_debug.g_err_stage:= 'Security Checking starts.....';
251 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
252 end if;
253
254 /*Security check for the CI_ID UpdateAccess*/
255 if 'T' <> Pa_ci_security_pkg.check_update_access (p_ci_id) then
256 PA_UTILS.add_Message( p_app_short_name => 'PA'
257 ,p_msg_name => 'PA_CI_NO_UPDATE_ACCESS');
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 if l_debug_mode = 'Y' then
260 pa_debug.g_err_stage:= 'the CI_ID does not have the update access';
261 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
262 end if;
263 raise FND_API.G_EXC_ERROR ;
264 end if;
265 if l_debug_mode = 'Y' then
266 pa_debug.g_err_stage:= 'After call to pa_ci_security_pkg.check_update_acces';
267 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
268 end if;
269
270
271 /*Check for the impact security -- coded as in the UI impact_icon is enabled and disabled based on ControlitemListVO query */
272 if ('EDIT'<> pa_ci_impacts_util.get_update_impact_mode(l_ci_id, l_cr_status_code)) then
273 PA_UTILS.add_Message( p_app_short_name => 'PA'
274 ,p_msg_name => 'PA_CI_NO_IMPACT_UPDATE_ACCESS');
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 if l_debug_mode = 'Y' then
277 pa_debug.g_err_stage:= 'the CI_ID does not have the update Impact access';
278 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
279 end if;
280 raise FND_API.G_EXC_ERROR ;
281 end if;
282
283 if l_debug_mode = 'Y' then
284 pa_debug.g_err_stage:= 'After call to pa_ci_impacts_util.get_update_impact_mode, to check for the update access';
285 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
286 end if;
287
288
289 /*The control item will not be updatable if the current status has approval workflow attached. */
290 OPEN Check_Workflow_On_CI(p_ci_id);
291 FETCH Check_Workflow_On_CI INTO l_ci_id;
292 if Check_Workflow_On_CI%found then
293 --IF (l_enable_wf_flag = 'Y' AND l_wf_success_status_code IS NOT NULL AND l_wf_failure_status_code IS NOT NULL) THEN
294 PA_UTILS.ADD_MESSAGE(p_app_short_name =>'PA'
295 ,p_msg_name =>'PA_CI_APPROVAL_WORKFLOW');
296 if l_debug_mode = 'Y' then
297 pa_debug.g_err_stage:= 'Approval workflow is attached';
298 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
299 end if;
300 close Check_Workflow_On_CI;
301 raise FND_API.G_EXC_ERROR ;
302 else
303 close Check_Workflow_On_CI;
304 end if;
305
306 if l_debug_mode = 'Y' then
307 pa_debug.g_err_stage:= 'After checking for Approval Workflow security check';
308 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
309 end if;
310
311 /* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE
312 is allowed on the current status of the issue. */
313 has_access := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_cr_status_code, 'CONTROL_ITEM_ALLOW_UPDATE',p_ci_id);
314 if has_access <> 'Y' then
315 PA_UTILS.add_Message( p_app_short_name => 'PA'
316 ,p_msg_name => 'PA_CI_NO_ALLOW_UPDATE');
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 if l_debug_mode = 'Y' then
319 pa_debug.g_err_stage:= 'the CI_ID does not have the update access for the current status';
320 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
321 end if;
322 raise FND_API.G_EXC_ERROR ;
323 end if;
324 if l_debug_mode = 'Y' then
325 pa_debug.g_err_stage:= 'After checking for CONTROL_ITEM_ALLOW_UPDATE for the current status';
326 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
327 end if;
328
329
330 l_status_code := 'CI_IMPACT_PENDING'; --always be pending as we are not implementing the impact.
331
332 if l_debug_mode = 'Y' then
333 pa_debug.g_err_stage:= 'Calling the '||p_mode||' API for impacts';
334 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
335 end if;
336
337 if p_mode = 'INSERT' and x_return_status = FND_API.G_RET_STS_SUCCESS then
338
339 /*Validating the p_impact_description with the PA_INTERFACE_UTILS_PUB.G_PA_MISS_XXX */
340 if p_impact_description = G_PA_MISS_CHAR or p_impact_description is null then
341 l_impact_description := null;
342 elsif p_impact_description <> G_PA_MISS_CHAR and p_impact_description is not null then
343 l_impact_description := p_impact_description;
344 end if;
345
346 /*Directly calling the private method in PA_CI_IMPACTS_PVT*/
347 PA_CI_IMPACTS_pvt.create_ci_impact(
348 p_api_version => p_api_version_number,
349 p_init_msg_list => FND_API.G_FALSE,
350 p_commit => p_commit,
351 p_validate_only => 'F',
352 p_max_msg_count => NULL,
353 p_ci_id => p_ci_id,
354 p_impact_type_code => p_impact_type_code,
355 p_status_code => l_status_code,
356 p_description => l_impact_description,
357 p_implementation_date => NULL,
358 p_implemented_by => NULL,
359 p_implementation_comment => NULL,
360 p_impacted_task_id => NULL,
361 x_ci_impact_id => x_ci_impact_id,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data
365 );
366
367 elsif p_mode = 'UPDATE' and x_return_status = FND_API.G_RET_STS_SUCCESS then
368
369 /*Validating the imapct description*/
370 if p_impact_description = G_PA_MISS_CHAR then
371 l_impact_description := l_impact_description1;
372 else
373 l_impact_description := p_impact_description;
374 end if;
375
376
377 /*calling directly the PVT method in PA_CI_IMPACTS_pvt*/
378 PA_CI_IMPACTS_pvt.update_ci_impact(
379 p_api_version => p_api_version_number,
380 p_init_msg_list => FND_API.G_FALSE,
381 p_commit => p_commit,
382 p_validate_only => 'F',
383 p_max_msg_count => null,
384 p_ci_impact_id => l_ci_impact_id,
385 p_ci_id => p_ci_id,
386 p_impact_type_code => p_impact_type_code,
387 p_status_code => l_status_code,
388 p_description => l_impact_description,
389 p_implementation_date => null,
390 p_implemented_by => null,
391 p_impby_name => null,
392 p_impby_type_id => null,
393 p_implementation_comment => null,
394 p_record_version_number => null,
395 p_impacted_task_id => null,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data
399 );
400 x_ci_impact_id := 0; --setting the ci_impact_id = 0 in update impact.
401 end if; /*enf for if l_mode*/
402
403 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
404 raise FND_API.G_EXC_ERROR;
405 end if;
406
407 --Reset the stack
408 if l_debug_mode = 'Y' then
409 pa_debug.reset_curr_function;
410 end if;
411
412 exception
413
414 when FND_API.G_EXC_ERROR then
415 x_ci_impact_id := null;
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 l_msg_count := fnd_msg_pub.count_msg;
418 if l_msg_count = 1 then
419 pa_interface_utils_pub.get_messages
420 (p_encoded => fnd_api.g_false,
421 p_msg_index => 1,
422 p_msg_count => l_msg_count ,
423 p_msg_data => l_msg_data ,
424 p_data => l_data,
425 p_msg_index_out => l_msg_index_out );
426 x_msg_data := l_data;
427 x_msg_count := l_msg_count;
428 else
429 x_msg_count := l_msg_count;
430 end if;
431 --Reset the stack
432 if l_debug_mode = 'Y' then
433 pa_debug.reset_curr_function;
434 end if;
435
436
437 --raise the exception
438 raise;
439
440 when others then
441 x_ci_impact_id := null;
442 x_return_status := fnd_api.g_ret_sts_unexp_error;
443 fnd_msg_pub.add_exc_msg ( p_pkg_name => 'PA_CONTROL_API_PVT',
444 p_procedure_name => 'update_impacts',
445 p_error_text => substrb(sqlerrm,1,240));
446 fnd_msg_pub.count_and_get(p_count => x_msg_count,
447 p_data => x_msg_data);
448 --Reset the stack
449 if l_debug_mode = 'Y' then
450 pa_debug.reset_curr_function;
451 end if;
452
453 --raise the exception
454 raise;
455
456 END update_impacts;
457
458
459
460
461 /*This Procedure will be called from PA_CONTROL_API_PUB.add_supplier_impact procedure
462 to insert the details of the supplier
463 You can also call this procedure independently by passing necessary info.
464 */
465
466 Procedure add_supplier_details (
467 p_ci_id IN NUMBER := G_PA_MISS_NUM,
468 p_ci_impact_id IN NUMBER ,
469 p_supplier_det_tbl IN PA_CONTROL_API_PUB.SUPP_DET_TBL_TYPE,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_msg_data OUT NOCOPY VARCHAR2
473 )
474
475 IS
476
477 -- declaring local variables
478 l_vendor_id po_vendors.vendor_id%TYPE;
479 l_supplier_name po_vendors.vendor_name%TYPE;
480 l_po_number po_headers_all.segment1%TYPE;
481 l_header_id po_headers_all.po_header_id%TYPE;
482 l_line_id po_lines_all.po_line_id%TYPE;
483 l_line_num po_lines_all.line_num%TYPE;
484 l_currency_code fnd_currencies_vl.currency_code%type;
485 l_currency_code1 fnd_currencies_vl.currency_code%type;
486 l_currency_name fnd_currencies_vl.Name%TYPE;
487 l_project_id pa_control_items.project_id%TYPE;
488 l_ci_type_id pa_control_items.ci_type_id%type;
489 l_change_description pa_ci_supplier_details.change_description%type;
490 l_ci_impact_id pa_ci_impacts.ci_impact_id%type := NULL;
491 l_calling_mode VARCHAR2(30);
492 l_po_line_Amount NUMBER ;
493 l_ou_name VARCHAR2(30);
494 x_rowid ROWID;
495 x_ci_transaction_id NUMBER(15);
496 l_error_msg_code varchar2(100);
497 l_org_id po_headers_all.org_id%TYPE := null;
498 l_data VARCHAR2(2000);
499 l_msg_data VARCHAR2(2000);
500 l_msg_index_out NUMBER;
501 l_module_name VARCHAR2(200) := 'PA_CONTROL_API_PVT.add_supplier_details';
502 l_msg_count NUMBER := 0;
503 l_record_status VARCHAR2(20) := 'NEW';
504
505
506
507 --query for ref cursor. The where clause will be added depending upon the change_type.
508 l_check_vendor_query VARCHAR2(2000);
509
510 Type cur_vendor_id IS REF CURSOR;
511 c_cur_vendor_id cur_vendor_id;
512
513
514 /*Cursor to check the po_number is valid or not*/
515 cursor c_check_po_number(c_po_number VARCHAR2, c_project_id NUMBER, c_org_id number) is
516 select distinct po.segment1 PoNumber
517 ,po.po_header_id PoHeaderId
518 ,po.vendor_id PoVendorId
519 ,v.vendor_name PoSupplierName
520 ,po.currency_code PoCurrency
521 ,po.org_id Poorgid
522 ,substr(pa_expenditures_utils.getorgtlname(po.org_id),1,30) PoOuname
523 ,pod.project_id PoProjectId
524 From po_headers_all po
525 ,po_vendors v
526 ,po_distributions_all pod
527 Where po.vendor_id = v.vendor_id
528 and NVL(po.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
529 and pod.po_header_id = po.po_header_id
530 and (( po.org_id = c_org_id and c_org_id is NOT NULL ) or c_org_id is NULL )
531 and pod.project_id = c_project_id
532 and po.segment1 = c_po_number;
533
534
535 /*Cursor to check the po_header_id is valid or not*/
536 cursor c_check_po_header_id(c_po_header_id VARCHAR2, c_project_id NUMBER,c_org_id number) is
537 select distinct po.segment1 PoNumber
538 ,po.po_header_id PoHeaderId
539 ,po.vendor_id PoVendorId
540 ,v.vendor_name PoSupplierName
541 ,po.currency_code PoCurrency
542 ,po.org_id Poorgid
543 ,substr(pa_expenditures_utils.getorgtlname(po.org_id),1,30) PoOuname
544 ,pod.project_id PoProjectId
545 From po_headers_all po
546 ,po_vendors v
547 ,po_distributions_all pod
548 Where po.vendor_id = v.vendor_id
549 and NVL(po.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
550 and pod.po_header_id = po.po_header_id
551 and (( po.org_id = c_org_id and c_org_id is NOT NULL ) or c_org_id is NULL )
552 and pod.project_id = c_project_id
553 and po.po_header_id = c_po_header_id;
554
555
556 /*cursor to check the po_line_number is valid or not*/
557 cursor c_check_Doc_number(c_po_number VARCHAR2,c_po_line_num NUMBER, c_project_id NUMBER)
558 is
559 select pol.po_line_id PoLineId
560 ,pol.line_num PoLineNum
561 ,(pol.quantity * pol.unit_price) PolineAmount
562 ,poh.po_header_id Poheaderid
563 ,poh.segment1 Ponumber
564 ,pod.project_id Projectid
565 ,substr(pa_expenditures_utils.getorgtlname(pod.org_id),1,30) Ouname
566 ,pod.po_distribution_id Podistid
567 From po_lines_all pol
568 ,po_headers_all poh
569 ,po_distributions_all pod
570 Where poh.po_header_id = pol.po_header_id
571 and pod.po_header_id = pol.po_header_id
572 and pod.po_line_id = pol.po_line_id
573 and NVL(poh.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
574 AND NVL(POL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
575 AND ( NVL(POL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG,'N') = 'Y' ) --Bug 9462109: CLM enhancement
576 and pod.project_id = c_project_id
577 and poh.segment1 = c_po_number --ponumber is passed
578 and pol.line_num = c_po_line_num;
579
580 /*cursor to check the po_line_id is valid or not*/
581 cursor c_check_po_line_id(c_po_number VARCHAR2,c_po_line_id NUMBER, c_project_id NUMBER)
582 is
583 select pol.po_line_id PoLineId
584 ,pol.line_num PoLineNum
585 ,(pol.quantity * pol.unit_price) PolineAmount
586 ,poh.po_header_id Poheaderid
587 ,poh.segment1 Ponumber
588 ,pod.project_id Projectid
589 ,substr(pa_expenditures_utils.getorgtlname(pod.org_id),1,30) Ouname
590 ,pod.po_distribution_id Podistid
591 From po_lines_all pol
592 ,po_headers_all poh
593 ,po_distributions_all pod
594 Where poh.po_header_id = pol.po_header_id
595 and pod.po_header_id = pol.po_header_id
596 and pod.po_line_id = pol.po_line_id
597 AND NVL(POL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
598 AND ( NVL(POL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG,'N') = 'Y' ) --Bug 9462109: CLM enhancement
599 and NVL(poh.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
600 and pod.project_id = c_project_id
601 and poh.segment1 = c_po_number --ponumber is passed
602 and pol.po_line_id = c_po_line_id;
603
604
605
606 /*cursor to check the validity of currency_code*/
607 cursor c_check_currency_code(c_currency varchar2) is
608 select currency_code CurrencyCode
609 ,Name CurrencyName
610 from fnd_currencies_vl
611 where enabled_flag = 'Y'
612 and trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
613 and nvl(end_date_active,trunc(sysdate))
614 and currency_code = c_currency;
615
616 /*cursor to fetch the ci_type_id and project_id data*/
617 cursor c_get_project_id(c_ci_id number)
618 is
619 select pci.project_id
620 from
621 pa_control_items pci
622 where pci.ci_id = c_ci_id;
623
624
625 /*Cursor to check ci_id validity*/
626 /*cursor c_get_ci_type_id is
627 select ci_type_id from pa_control_items a , pa_ci_impacts b
628 where a.ci_id = b.ci_id
629 and a.ci_id = p_ci_id
630 and b.IMPACT_TYPE_CODE = 'SUPPLIER';*/
631
632 --Declaring record types for both cursors c_check_po_number and c_check_Doc_number.
633 c_check_po_number_rec c_check_po_number%ROWTYPE;
634 c_check_Doc_number_rec c_check_Doc_number%ROWTYPE;
635 c_check_po_line_id_rec c_check_po_line_id%ROWTYPE;
636 c_check_po_header_id_rec c_check_po_header_id%ROWTYPE;
637
638 /*parameter variable declaraion */
639
640 p_change_type pa_ci_supplier_details.change_type%type;
641 p_change_description pa_ci_supplier_details.change_description%type ;
642 p_vendor_id NUMBER;
643 p_po_header_id NUMBER;
644 p_po_number varchar2(40);
645 p_po_line_id number;
646 p_po_line_num number;
647 p_currency pa_ci_supplier_details.CURRENCY_CODE%type;
648 p_change_amount number;
649
650
651
652 BEGIN
653
654 -- initialize the return status to success
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657 l_debug_mode :=NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.resp_id, 275, null, null), 'N');
658 if l_debug_mode = 'Y' then
659 pa_debug.set_curr_function(p_function=>l_module_name,p_debug_mode => l_debug_mode);
660 end if;
661
662
663 if l_debug_mode = 'Y' then
664 pa_debug.g_err_stage:= ' Starting the PA_CONTROL_API_PVT.add_supplier_details ';
665 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
666 end if;
667
668 l_org_id := FND_GLOBAL.org_id;
669
670 for i in p_supplier_det_tbl.FIRST .. p_supplier_det_tbl.LAST loop
671
672 /*get the details of supplier from table type p_supplier_det_tbl*/
673 p_change_type := p_supplier_det_tbl(i).change_type;
674 p_change_description := p_supplier_det_tbl(i).change_description;
675 p_vendor_id := p_supplier_det_tbl(i).vendor_id;
676 p_po_header_id := p_supplier_det_tbl(i).po_header_id;
677 p_po_number := p_supplier_det_tbl(i).po_number;
678 p_po_line_id := p_supplier_det_tbl(i).po_line_id;
679 p_po_line_num := p_supplier_det_tbl(i).po_line_num;
680 p_currency := p_supplier_det_tbl(i).currency;
681 p_change_amount := p_supplier_det_tbl(i).change_amount;
682
683 if l_debug_mode = 'Y' then
684 pa_debug.g_err_stage:= 'In Loop Record number '|| i ||' Values are :';
685 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
686 pa_debug.g_err_stage:= 'p_change_type['||i||']='||p_change_type||
687 'p_change_description['||i||']='||p_change_description||
688 'p_vendor_id['||i||']='||p_vendor_id||
689 'p_po_header_id['||i||']='||p_po_header_id||
690 'p_po_number['||i||']='||p_po_number||
691 'p_po_line_id['||i||']='||p_po_line_id||
692 'p_po_line_num['||i||']='||p_po_line_num||
693 'p_currency['||i||']='||p_currency||
694 'p_change_amount['||i||']='||p_change_amount;
695 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
696 end if;
697
698 /*assigning local variables to default value null*/
699 l_vendor_id :=null;
700 l_supplier_name :=null;
701 l_po_number :=null;
702 l_header_id :=null;
703 l_line_id :=null;
704 l_line_num :=null;
705 l_currency_code :=null;
706 l_currency_code1 :=null;
707 l_currency_name :=null;
708 l_project_id :=null;
709 l_ci_type_id :=null;
710 l_change_description :=null;
711 l_calling_mode := 'VALIDATEANDINSERT';
712 l_po_line_Amount :=null;
713 l_ou_name :=null;
714 x_rowid :=null;
715 x_ci_transaction_id :=null;
716 l_error_msg_code := null;
717 l_check_vendor_query := 'select v.vendor_id SupplierId ,v.vendor_name SupplierName From po_vendors v ';
718
719 /*Check the ci_id and P_CHANGE_TYPE (should be either CREATE or UPDATE) are valid or not*/
720 if p_ci_id is null or p_ci_id = G_PA_MISS_NUM
721 or p_change_type is null or p_change_type = G_PA_MISS_CHAR then
722 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
723 p_msg_name => 'PA_CI_ID_CHANGE_TYPE_NULL',
724 p_token1 => 'NUMBER',
725 p_value1 => i);
726 if l_debug_mode = 'Y' then
727 pa_debug.g_err_stage:= ' The ci_id is null or Change Type is null';
728 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
729 end if;
730 x_return_status := FND_API.G_RET_STS_ERROR;
731 elsif p_change_type is not null and (p_change_type <> 'CREATE' and p_change_type <> 'UPDATE') then
732 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
733 p_msg_name => 'PA_CHANGE_TYPE_INVALID',
734 p_token1 => 'NUMBER',
735 p_value1 => i);
736 if l_debug_mode = 'Y' then
737 pa_debug.g_err_stage:= ':The Change Type is invalid';
738 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
739 end if;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 end if; --end for if p_ci_id is null or p_change_type is null
742
743 /*Checking if the change type is CREATE then po details should be null */
744 If p_change_type is NOT NULL and p_change_type = 'CREATE'
745 and ( (p_po_number is NOT NULL and p_po_number <>G_PA_MISS_CHAR)
746 or (p_po_header_id is NOT NULL and p_po_header_id <>G_PA_MISS_NUM)
747 or (p_po_line_num is not null and p_po_line_num <> G_PA_MISS_NUM)
748 or (p_po_line_id is not null and p_po_line_id <> G_PA_MISS_NUM) )
749 then
750 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
751 p_msg_name => 'PA_CHANGE_TYPE_INVALID',
752 p_token1 => 'NUMBER',
753 p_value1 => i);
754 if l_debug_mode = 'Y' then
755 pa_debug.g_err_stage:= 'Invalid Change Type (CREATE)- po number should be null';
756 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
757 end if;
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 /*if change_type is UPDATE then user must pass PO details*/
760 elsif p_change_type is NOT NULL and p_change_type = 'UPDATE'
761 and ( (p_po_number is NULL or p_po_number = G_PA_MISS_CHAR)
762 and (p_po_header_id is NULL or p_po_header_id = G_PA_MISS_NUM)
763 and (p_po_line_num is null or p_po_line_num = G_PA_MISS_NUM)
764 and (p_po_line_id is null or p_po_line_id = G_PA_MISS_NUM) )
765 then
766 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
767 p_msg_name => 'PA_CHANGE_TYPE_INVALID',
768 p_token1 => 'NUMBER',
769 p_value1 => i);
770 if l_debug_mode = 'Y' then
771 pa_debug.g_err_stage:= 'Invalid Change Type (CREATE)- po number should be null';
772 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
773 end if;
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 end if;
776
777 if l_debug_mode = 'Y' then
778 pa_debug.g_err_stage:= 'After validating the Change Type and ci_id.'||
779 'Change Type is :'||p_change_type|| ' ; Ci_id is :'||p_ci_id;
780 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
781 end if;
782 /*get the ci_type_id and project_id, this should not raise an exception becoz ci_id is already validated in public method.*/
783 if p_ci_id is not null and p_ci_id <> G_PA_MISS_NUM then
784 open c_get_project_id(p_ci_id);
785 fetch c_get_project_id into l_project_id;
786 if c_get_project_id%notfound then
787 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
788 p_msg_name => 'PA_CI_INVALID_CI_ID',
789 p_token1 => 'NUMBER',
790 p_value1 => i);
791 if l_debug_mode = 'Y' then
792 pa_debug.g_err_stage:= 'Invalid Control Item id.' ;
793 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
794 end if;
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 end if;
797 close c_get_project_id;
798 end if;
799
800 if l_debug_mode = 'Y' then
801 pa_debug.g_err_stage:= 'The project id is :'||l_project_id;
802 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
803 end if;
804
805
806
807 /*if vendor id is not null then check whether it is valid or not,
808 depending on the change_type, execute the cursor query*/
809 if p_vendor_id is not null and p_vendor_id <> G_PA_MISS_NUM then
810 if p_change_type = 'UPDATE' then
811 l_check_vendor_query := l_check_vendor_query || ' where EXISTS (select * from po_distributions_all pod ,po_headers_all poh where pod.po_header_id = poh.po_header_id and pod.project_id = '||
812 l_project_id ||' and poh.vendor_id = v.vendor_id and v.vendor_id = '''||p_vendor_id ||''') ';
813 else
814 l_check_vendor_query := l_check_vendor_query ||
815 ' where v.vendor_id = '''||p_vendor_id ||'''';
816 end if;
817
818 if l_debug_mode = 'Y' then
819 pa_debug.g_err_stage:= 'The l_check_vendor_query is ['||p_change_type||'] : '||l_check_vendor_query;
820 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
821 end if;
822
823 open c_cur_vendor_id for l_check_vendor_query;
824 fetch c_cur_vendor_id into l_vendor_id , l_supplier_name;
825 if c_cur_vendor_id%notfound then
826 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
827 p_msg_name => 'PA_VENDOR_ID_INVALID',
828 p_token1 => 'NUMBER',
829 p_value1 => i);
830 if l_debug_mode = 'Y' then
831 pa_debug.g_err_stage:= 'Invalid Vedor ID';
832 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
833 end if;
834 x_return_status := FND_API.G_RET_STS_ERROR;
835 close c_cur_vendor_id;
836
837 else
838 close c_cur_vendor_id;
839 end if;
840 elsif (p_vendor_id is null or p_vendor_id = G_PA_MISS_NUM )-- end for if p_vendor_id
841 and p_change_type = 'CREATE' then
842 /*for create change type vendor id should not be null
843 But where as in UPDATE we can derive the vendor id from the given header id or po_line id details */
844 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
845 p_msg_name => 'PA_VENDOR_ID_NULL',
846 p_token1 => 'NUMBER',
847 p_value1 => i);
848 if l_debug_mode = 'Y' then
849 pa_debug.g_err_stage:= 'Vedor ID is null';
850 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
851 end if;
852 x_return_status := FND_API.G_RET_STS_ERROR;
853 end if;
854
855
856 /* check for the po_number valid or not
857 if po_number is null and po_line_number is not null then raise exception
858 bcoz po_line_num is dependent on po_number*/
859 if p_po_header_id is not null and p_po_header_id <> G_PA_MISS_NUM then
860 open c_check_po_header_id(p_po_header_id,l_project_id,l_org_id);
861 fetch c_check_po_header_id into c_check_po_header_id_rec;
862 if c_check_po_header_id%notfound then
863 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
864 p_msg_name => 'PA_PO_HEADER_ID_INVALID',
865 p_token1 => 'NUMBER',
866 p_value1 => i);
867 if l_debug_mode = 'Y' then
868 pa_debug.g_err_stage:= 'The header id is not valid';
869 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
870 end if;
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 close c_check_po_header_id;
873
874 else
875 l_po_number := c_check_po_header_id_rec.PoNumber;
876 l_supplier_name := c_check_po_header_id_rec.PoSupplierName;
877 l_vendor_id := c_check_po_header_id_rec.PoVendorId;
878 l_currency_code := c_check_po_header_id_rec.PoCurrency;
879 l_header_id := p_po_header_id;
880 if l_debug_mode = 'Y' then
881 pa_debug.g_err_stage:= 'The values after validating p_po_header_id are'||
882 'l_po_number['||l_po_number||'],l_supplier_name['||l_supplier_name||'],l_vendor_id['||
883 l_vendor_id||'],l_currency_code['||l_currency_code||']';
884 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
885 end if;
886 close c_check_po_header_id;
887 if p_vendor_id is not null and p_vendor_id <> G_PA_MISS_NUM then
888 if l_vendor_id <> p_vendor_id then
889 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
890 p_msg_name => 'PA_VENDOR_ID_INVALID',
891 p_token1 => 'NUMBER',
892 p_value1 => i);
893 if l_debug_mode = 'Y' then
894 pa_debug.g_err_stage:= 'The Vendor id is not valid';
895 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
896 end if;
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 end if;
899 end if;
900 end if;
901
902 elsif p_po_number is not null and p_po_number <> G_PA_MISS_CHAR then
903 open c_check_po_number(p_po_number,l_project_id,l_org_id);
904 fetch c_check_po_number into c_check_po_number_rec;
905 if c_check_po_number%notfound then
906 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
907 p_msg_name => 'PA_PO_NUMBER_INVALID',
908 p_token1 => 'NUMBER',
909 p_value1 => i);
910 if l_debug_mode = 'Y' then
911 pa_debug.g_err_stage:= 'The po_number is not valid';
912 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
913 end if;
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 close c_check_po_number;
916
917 else
918 l_po_number := c_check_po_number_rec.PoNumber;
919 l_supplier_name := c_check_po_number_rec.PoSupplierName;
920 l_vendor_id := c_check_po_number_rec.PoVendorId;
921 l_currency_code := c_check_po_number_rec.PoCurrency;
922 l_header_id := c_check_po_number_rec.PoHeaderId;
923 if l_debug_mode = 'Y' then
924 pa_debug.g_err_stage:= 'The values after validating p_po_number are'||
925 'l_po_number['||l_po_number||'],l_supplier_name['||l_supplier_name||'],l_vendor_id['||
926 l_vendor_id||'],l_currency_code['||l_currency_code||']';
927 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
928 end if;
929 close c_check_po_number;
930 if p_vendor_id is not null then
931 if l_vendor_id <> p_vendor_id then
932 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
933 p_msg_name => 'PA_VENDOR_ID_INVALID',
934 p_token1 => 'NUMBER',
935 p_value1 => i);
936 if l_debug_mode = 'Y' then
937 pa_debug.g_err_stage:= 'The Vendor ID is not valid';
938 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
939 end if;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941
942 end if;
943 end if;
944 end if;
945 else
946 if (p_po_line_num is not null and p_po_line_num <> G_PA_MISS_NUM)
947 or (p_po_line_id is not null and p_po_line_id <> G_PA_MISS_NUM) then
948 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
949 p_msg_name => 'PA_HEAD_ID_PO_NUM_IS_NULL',
950 p_token1 => 'NUMBER',
951 p_value1 => i);
952 if l_debug_mode = 'Y' then
953 pa_debug.g_err_stage:= 'The PO header id or Po Number is null';
954 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
955 end if;
956 x_return_status := FND_API.G_RET_STS_ERROR;
957
958 end if;
959
960 end if;
961
962 /* check for po_line_id validity execute the cursor only if the l_po_number is not null
963 this value is been assigned from the cursors c_check_po_number or c_check_po_header_id.
964 Becuase this cursor takes l_po_number as parameter.if Both parameters line id
965 and line number then set the error flag*/
966 if l_po_number is not null and p_po_line_id is not null
967 and p_po_line_id <> G_PA_MISS_NUM then
968 open c_check_po_line_id(l_po_number,p_po_line_id,l_project_id);
969 fetch c_check_po_line_id into c_check_po_line_id_rec;
970 if c_check_po_line_id%notfound then
971 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
972 p_msg_name => 'PA_PO_LINE_ID_INVALID',
973 p_token1 => 'NUMBER',
974 p_value1 => i);
975 if l_debug_mode = 'Y' then
976 pa_debug.g_err_stage:= 'Po Line id is invalid';
977 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
978 end if;
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 close c_check_po_line_id;
981
982 else
983 l_line_num := c_check_po_line_id_rec.PoLineNum;
984 l_po_number := c_check_po_line_id_rec.Ponumber;
985 l_po_line_Amount := c_check_po_line_id_rec.PolineAmount;
986 l_line_id := p_po_line_id;
987 close c_check_po_line_id;
988 end if;
989 elsif l_po_number is not null and p_po_line_num is not null and p_po_line_num<>G_PA_MISS_NUM then
990 open c_check_Doc_number(l_po_number,p_po_line_num,l_project_id);
991 fetch c_check_Doc_number into c_check_Doc_number_rec;
992 if c_check_Doc_number%notfound then
993 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
994 p_msg_name => 'PA_PO_LINE_NUMBER_INVALID',
995 p_token1 => 'NUMBER',
996 p_value1 => i);
997 if l_debug_mode = 'Y' then
998 pa_debug.g_err_stage:= 'Po Line number is invalid';
999 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1000 end if;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 close c_check_Doc_number;
1003
1004 else
1005 l_line_num := c_check_Doc_number_rec.PoLineNum;
1006 l_po_number := c_check_Doc_number_rec.Ponumber;
1007 l_po_line_Amount := c_check_Doc_number_rec.PolineAmount;
1008 l_line_id := c_check_Doc_number_rec.PoLineID;
1009 close c_check_Doc_number;
1010 end if;
1011 elsif l_po_number is not null and
1012 ((p_po_line_num is null or p_po_line_num = G_PA_MISS_NUM)
1013 or (p_po_line_id is null or p_po_line_id = G_PA_MISS_NUM)) then
1014 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1015 p_msg_name => 'PA_PO_LINE_ID_LINE_NUM_NULL',
1016 p_token1 => 'NUMBER',
1017 p_value1 => i);
1018 if l_debug_mode = 'Y' then
1019 pa_debug.g_err_stage:= 'Po Line number is invalid';
1020 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1021 end if;
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 end if;
1024
1025
1026 if l_debug_mode = 'Y' then
1027 pa_debug.g_err_stage:= 'Validated the po_line_id or doc number and the values are :'||
1028 'l_line_num['||l_line_num||'],l_po_number['||l_po_number||'],l_po_line_Amount['||
1029 l_po_line_Amount||']';
1030 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1031 end if;
1032
1033
1034
1035 /* if the l_currency_code already exists then store it in other local variable*/
1036 if l_currency_code is not null then --got it from c_check_po_number cursor.
1037 l_currency_code1 := l_currency_code;
1038 end if;
1039
1040 /* Check the currency code is valid or not*/
1041 if p_currency is not null and p_currency <> G_PA_MISS_CHAR then
1042 open c_check_currency_code(p_currency);
1043 fetch c_check_currency_code into l_currency_code, l_currency_name;
1044 if c_check_currency_code%found then
1045 if l_currency_code1 is not null and l_currency_code1 <> l_currency_code then
1046 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1047 p_msg_name => 'PA_CURRENCY_CODE_INVALID',
1048 p_token1 => 'NUMBER',
1049 p_value1 => i);
1050 if l_debug_mode = 'Y' then
1051 pa_debug.g_err_stage:= 'Currency Code is invalid';
1052 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1053 end if;
1054 x_return_status := FND_API.G_RET_STS_ERROR;
1055 close c_check_currency_code;
1056
1057 else
1058 close c_check_currency_code;
1059 end if;
1060 else
1061 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1062 p_msg_name => 'PA_CURRENCY_CODE_INVALID',
1063 p_token1 => 'NUMBER',
1064 p_value1 => i);
1065 if l_debug_mode = 'Y' then
1066 pa_debug.g_err_stage:= 'Currency Code is invalid';
1067 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1068 end if;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 close c_check_currency_code;
1071 end if;
1072 elsif (p_currency is null or p_currency = G_PA_MISS_CHAR)
1073 and l_currency_code1 is null
1074 and l_currency_code is null then
1075 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1076 p_msg_name => 'PA_CURRENCY_CODE_NULL',
1077 p_token1 => 'NUMBER',
1078 p_value1 => i);
1079 if l_debug_mode = 'Y' then
1080 pa_debug.g_err_stage:= ' Currency Code is invalid';
1081 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1082 end if;
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 end if;
1085
1086
1087 if l_debug_mode = 'Y' then
1088 pa_debug.g_err_stage:= ' Currency Code is Validated';
1089 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1090 end if;
1091
1092 if (p_change_amount is null or p_change_amount = G_PA_MISS_NUM ) and l_po_line_Amount is null then
1093 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1094 p_msg_name => 'PA_SUPP_AMOUNT_NULL',
1095 p_token1 => 'NUMBER',
1096 p_value1 => i);
1097 if l_debug_mode = 'Y' then
1098 pa_debug.g_err_stage:= 'Change amount cannot be null';
1099 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1100 end if;
1101 x_return_status := FND_API.G_RET_STS_ERROR;
1102
1103 elsif p_change_amount is not null and p_change_amount <> G_PA_MISS_NUM then
1104 l_po_line_Amount := p_change_amount;
1105 end if;
1106
1107 /*setting the descrition value*/
1108 if p_change_description is null or p_change_description = G_PA_MISS_CHAR then
1109 l_change_description := null;
1110 else
1111 l_change_description := p_change_description;
1112 end if;
1113
1114
1115 if l_debug_mode = 'Y' then
1116 pa_debug.g_err_stage:= 'Before calling the PA_CI_SUPPLIER_UTILS.validateSI to validate and insert';
1117 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1118 end if;
1119 /*Call the procedure to insert the details*/
1120 if x_return_status = FND_API.G_RET_STS_SUCCESS then
1121 /* PA_CI_SUPPLIER_UTILS.validateSI(
1122 p_ROWID => x_rowid
1123 ,p_RECORD_STATUS => l_record_status
1124 ,p_CI_ID => p_ci_id
1125 ,p_CI_TYPE_ID => l_ci_type_id
1126 ,p_CI_IMPACT_ID => p_ci_impact_id
1127 ,P_CALLING_MODE => l_calling_mode
1128 ,P_ORG_ID => l_org_id
1129 ,p_VENDOR_NAME => l_supplier_name
1130 ,p_PO_NUMBER => l_po_number
1131 ,p_PO_LINE_NUM => l_line_num
1132 ,p_ADJUSTED_TRANSACTION_ID => null
1133 ,p_CURRENCY_CODE => l_currency_code
1134 ,p_CHANGE_AMOUNT => l_po_line_Amount
1135 ,p_CHANGE_TYPE => p_change_type
1136 ,p_CHANGE_DESCRIPTION => l_change_description
1137 ,p_CI_TRANSACTION_ID => x_ci_transaction_id
1138 ,x_return_status => x_return_status
1139 ,x_msg_data => x_msg_data
1140 ,x_msg_count => x_msg_count
1141 );*/
1142 PA_CI_SUPPLIER_PKG.insert_row (
1143 x_rowid => x_rowid
1144 ,x_ci_transaction_id => x_ci_transaction_id
1145 ,p_CI_TYPE_ID => l_ci_type_id --passed as null as from UI also this is getting stamped as null
1146 ,p_CI_ID => p_ci_id
1147 ,p_CI_IMPACT_ID => l_ci_impact_id --p_ci_impact_id Passing null value as from UI also this is getting stamped as null
1148 ,p_VENDOR_ID => l_vendor_id
1149 ,p_PO_HEADER_ID => l_header_id
1150 ,p_PO_LINE_ID => l_line_id
1151 ,p_ADJUSTED_TRANSACTION_ID => null --passed as null as from UI
1152 ,p_CURRENCY_CODE => l_currency_code
1153 ,p_CHANGE_AMOUNT => l_po_line_Amount
1154 ,p_CHANGE_TYPE => p_CHANGE_TYPE
1155 ,p_CHANGE_DESCRIPTION => l_change_description
1156 ,p_CREATED_BY => FND_GLOBAL.user_id
1157 ,p_CREATION_DATE => trunc(sysdate)
1158 ,p_LAST_UPDATED_BY => FND_GLOBAL.user_id
1159 ,p_LAST_UPDATE_DATE => trunc(sysdate)
1160 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
1161 ,p_Task_Id => NULL
1162 ,p_Resource_List_Mem_Id => NULL
1163 ,p_From_Date => NULL
1164 ,p_To_Date => NULL
1165 ,p_Estimated_Cost => NULL
1166 ,p_Quoted_Cost => NULL
1167 ,p_Negotiated_Cost => NULL
1168 ,p_Burdened_cost => NULL
1169 ,p_revenue_override_rate => NULL
1170 ,p_audit_history_number => NULL
1171 ,p_current_audit_flag => 'Y'
1172 ,p_Original_supp_trans_id => NULL
1173 ,p_Source_supp_trans_id => NULL
1174 ,p_ci_status => null
1175 ,x_return_status => x_return_status
1176 ,x_error_msg_code => l_error_msg_code );
1177
1178 ELSIF l_error_msg_code is not null then
1179 raise FND_API.G_EXC_ERROR; --error while inserting the records raise
1180 end if;
1181 END LOOP;
1182 if x_return_status <> FND_API.G_RET_STS_SUCCESS or l_error_msg_code is not null then
1183 raise FND_API.G_EXC_ERROR;
1184 end if;
1185 --Reset the stack
1186 if l_debug_mode = 'Y' then
1187 pa_debug.reset_curr_function;
1188 end if;
1189
1190 Exception
1191
1192 when FND_API.G_EXC_ERROR then
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 l_msg_count := fnd_msg_pub.count_msg;
1195 x_rowid := null; --assign to the initial value
1196 x_ci_transaction_id := null; --assign to the initial value
1197 if l_msg_count = 1 then
1198 pa_interface_utils_pub.get_messages
1199 (p_encoded => fnd_api.g_false,
1200 p_msg_index => 1,
1201 p_msg_count => l_msg_count ,
1202 p_msg_data => l_msg_data ,
1203 p_data => l_data,
1204 p_msg_index_out => l_msg_index_out );
1205 x_msg_data := l_data;
1206 x_msg_count := l_msg_count;
1207 else
1208 x_msg_count := l_msg_count;
1209 end if;
1210 --Reset the stack
1211 if l_debug_mode = 'Y' then
1212 pa_debug.reset_curr_function;
1213 end if;
1214
1215 raise;
1216
1217 when others then
1218 x_return_status := fnd_api.g_ret_sts_unexp_error;
1219 x_rowid := null; --assign to the initial value
1220 x_ci_transaction_id := null; --assign to the initial value
1221 fnd_msg_pub.add_exc_msg ( p_pkg_name => 'PA_CONTROL_API_PVT',
1222 p_procedure_name => 'update_impacts',
1223 p_error_text => substrb(sqlerrm,1,240));
1224 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1225 p_data => x_msg_data);
1226 --Reset the stack
1227 if l_debug_mode = 'Y' then
1228 pa_debug.reset_curr_function;
1229 end if;
1230
1231 raise;
1232
1233 End add_supplier_details;
1234
1235
1236
1237
1238 PROCEDURE check_create_ci_allowed
1239 (
1240 p_project_id IN OUT NOCOPY NUMBER,
1241 p_project_name IN VARCHAR2 := null,
1242 p_project_number IN VARCHAR2 := null,
1243 p_ci_type_class_code IN VARCHAR2 := null,
1244 p_ci_type_id IN OUT NOCOPY NUMBER,
1245 x_ci_type_class_code OUT NOCOPY VARCHAR2,
1246 x_auto_number_flag OUT NOCOPY VARCHAR2,
1247 x_source_attrs_enabled_flag OUT NOCOPY VARCHAR2,
1248 x_return_status OUT NOCOPY VARCHAR2,
1249 x_msg_count OUT NOCOPY NUMBER,
1250 x_msg_data OUT NOCOPY VARCHAR2
1251 )
1252 is
1253
1254 cursor get_project_id_frm_num(p_project_number VARCHAR2) is
1255 select project_id
1256 from pa_projects_all
1257 where segment1 = p_project_number;
1258
1259 cursor get_project_id_frm_name(p_project_name VARCHAR2) is
1260 select project_id
1261 from pa_projects_all
1262 where name = p_project_name;
1263
1264 cursor validate_prj_id(p_project_id NUMBER) is
1265 select project_id
1266 from pa_projects_all
1267 where project_id = p_project_id;
1268
1269 cursor get_ci_type_attrs(p_ci_type_id NUMBER) is
1270 select ci_type_class_code,
1271 auto_number_flag,
1272 source_attrs_enabled_flag
1273 from pa_ci_types_b
1274 where ci_type_id = p_ci_type_id;
1275
1276 /*pa_ci_security_pkg.check_create_ci will check if the current project type and role played by user are allowed
1277 to create the control item of this type. Also it will check for project authority.*/
1278 cursor check_create_ci_priv(p_ci_type_id NUMBER, p_project_id NUMBER) is
1279 select 'Y'
1280 from pa_ci_types_vl
1281 where ci_type_id = p_ci_type_id
1282 and pa_ci_security_pkg.check_create_ci(p_ci_type_id, p_project_id)='T'
1283 and trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate);
1284
1285
1286 l_ci_type_attrs_rec get_ci_type_attrs%rowtype;
1287 l_ci_type_class_code pa_ci_types_b.ci_type_class_code%type;
1288 l_auto_number_flag pa_ci_types_b.auto_number_flag%type;
1289 l_source_attrs_enabled_flag pa_ci_types_b.source_attrs_enabled_flag%type;
1290
1291
1292 l_msg_count NUMBER := 0;
1293 l_data VARCHAR2(2000);
1294 l_msg_data VARCHAR2(2000);
1295 l_msg_index_out NUMBER;
1296 l_module_name VARCHAR2(200);
1297 l_check_create_ci VARCHAR2(1);
1298 l_project_id pa_projects_all.project_id%type;
1299 l_ini_proj_id pa_projects_all.project_id%type;
1300 l_ini_ci_type_id pa_ci_types_b.ci_type_id%type;
1301 l_any_err_occured_flg VARCHAR2(1);
1302
1303 begin
1304 -- initialize the return status to success
1305 x_return_status := fnd_api.g_ret_sts_success;
1306 x_msg_count := 0;
1307
1308 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
1309 l_module_name := 'check_create_ci_allowed' || g_module_name;
1310
1311 if l_debug_mode = 'Y' then
1312 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.check_create_ci_allowed', p_debug_mode => l_debug_mode);
1313 end if;
1314
1315 if l_debug_mode = 'Y' then
1316 pa_debug.write(l_module_name, 'start of check_create_ci_allowed', l_debug_level3);
1317 end if;
1318
1319 --Store the initial values of the in out parameteres so that these can be restored back if some exception occurs.
1320 l_ini_proj_id := p_project_id;
1321 l_ini_ci_type_id := p_ci_type_id;
1322
1323 --setting the err occured flag to N initially.
1324 l_any_err_occured_flg := 'N';
1325
1326 if (p_ci_type_id is null) then
1327 PA_UTILS.ADD_MESSAGE
1328 (p_app_short_name => 'PA',
1329 p_msg_name => 'PA_CI_TYPE_ID_MISSING');
1330 if l_debug_mode = 'Y' then
1331 pa_debug.g_err_stage:= 'ci_type_id is null';
1332 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1333 end if;
1334 l_any_err_occured_flg := 'Y';
1335 end if;
1336
1337 if(p_project_id is null and p_project_number is null and p_project_name is null) then
1338 PA_UTILS.ADD_MESSAGE
1339 (p_app_short_name => 'PA',
1340 p_msg_name => 'PA_CI_PROJECT_MISSING');
1341 if l_debug_mode = 'Y' then
1342 pa_debug.g_err_stage:= 'project id, project number, project name, all three cannot be null';
1343 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1344 end if;
1345 l_any_err_occured_flg := 'Y';
1346 end if;
1347
1348 if(p_ci_type_id is not null) then
1349 /* the passed p_ci_type_id gets validated and we derive the ci_type_attributes for the ci_type_id passed*/
1350 open get_ci_type_attrs(p_ci_type_id);
1351 fetch get_ci_type_attrs into l_ci_type_attrs_rec;
1352 if get_ci_type_attrs%NOTFOUND then
1353 PA_UTILS.ADD_MESSAGE
1354 (p_app_short_name => 'PA',
1355 p_msg_name => 'PA_CI_INVALID_TYPE_ID'); --existing msg.
1356 if l_debug_mode = 'Y' then
1357 pa_debug.g_err_stage:= 'Invalid ci_type_id passed';
1358 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1359 end if;
1360 close get_ci_type_attrs;
1361 l_any_err_occured_flg := 'Y';
1362 else
1363 x_ci_type_class_code := l_ci_type_attrs_rec.ci_type_class_code;
1364 x_auto_number_flag := l_ci_type_attrs_rec.auto_number_flag;
1365 x_source_attrs_enabled_flag := l_ci_type_attrs_rec.source_attrs_enabled_flag;
1366 close get_ci_type_attrs;
1367 /*now validate the class type code. validate that create_issue api can be used to create issues only and not
1368 change request or change order. And similarly create_change_request, create_change_order are used for their
1369 respective types*/
1370 if(p_ci_type_class_code <> l_ci_type_attrs_rec.ci_type_class_code) then
1371 PA_UTILS.ADD_MESSAGE
1372 (p_app_short_name => 'PA',
1373 p_msg_name => 'PA_CI_INV_API_USE'); --existing msg.
1374 if l_debug_mode = 'Y' then
1375 pa_debug.g_err_stage:= 'wrong usage of the api for the control item type';
1376 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1377 end if;
1378 l_any_err_occured_flg := 'Y';
1379 end if;--if(p_ci_type_class_code <> l_ci_type_attrs_rec.ci_type_class_code) then
1380 end if;
1381 end if;
1382
1383 /* Need to write the code to get the ci_type_id from the ci_type and pass back this ci_type_id*/
1384 /*Need to check a test case in the UI also*/
1385 if(p_project_id is not null) then
1386 /*a value has been passed for project id, we will validate this value*/
1387 open validate_prj_id(p_project_id);
1388 fetch validate_prj_id into l_project_id;
1389 if validate_prj_id%NOTFOUND then
1390 PA_UTILS.ADD_MESSAGE
1391 (p_app_short_name => 'PA',
1392 p_msg_name => 'PA_CI_NO_PROJECT_ID'); --exisitng message
1393 if l_debug_mode = 'Y' then
1394 pa_debug.g_err_stage:= 'Invalid project id passed';
1395 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1396 end if;
1397 close validate_prj_id;
1398 l_any_err_occured_flg := 'Y';
1399 else
1400 p_project_id := l_project_id;
1401 close validate_prj_id;
1402 end if;
1403 elsif(p_project_id is null and p_project_number is not null) then
1404 /*Derive the project id from project number*/
1405 open get_project_id_frm_num(p_project_number);
1406 fetch get_project_id_frm_num into l_project_id;
1407 if get_project_id_frm_num%NOTFOUND then
1408 PA_UTILS.ADD_MESSAGE
1409 (p_app_short_name => 'PA',
1410 p_msg_name => 'PA_CI_INV_PROJ_NUM');
1411 if l_debug_mode = 'Y' then
1412 pa_debug.g_err_stage:= 'Invalid project number passed';
1413 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1414 end if;
1415 close get_project_id_frm_num;
1416 l_any_err_occured_flg := 'Y';
1417 else
1418 p_project_id := l_project_id;
1419 close get_project_id_frm_num;
1420 end if;
1421 elsif(p_project_id is null and p_project_number is null and p_project_name is not null) then
1422 /*Derive the project id from project name*/
1423 open get_project_id_frm_name(p_project_name);
1424 fetch get_project_id_frm_name into l_project_id;
1425 if get_project_id_frm_name%NOTFOUND then
1426 PA_UTILS.ADD_MESSAGE
1427 (p_app_short_name => 'PA',
1428 p_msg_name => 'PA_CI_INV_PROJ_NAME');
1429 if l_debug_mode = 'Y' then
1430 pa_debug.g_err_stage:= 'Invalid project name passed';
1431 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1432 end if;
1433 close get_project_id_frm_name;
1434 l_any_err_occured_flg := 'Y';
1435 else
1436 p_project_id := l_project_id;
1437 close get_project_id_frm_name;
1438 end if;
1439 end if;--(p_project_id is not null) then
1440
1441 /*at this place in code both project id and ci type id would be valid*/
1442 /* check if the project type ,role for the current logged in user, project authority can create the
1443 control item for this control item type*/
1444 /* Added "and l_any_err_occured_flg <> 'Y'" in the if condition, so tht security check is only enforced if there were no errors
1445 before this point. Without this check Even if invalid project id or invalid ci type id would have been passed, then security check
1446 would have got applied for invalid project/citype id
1447 */
1448 if(p_project_id is not null and p_ci_type_id is not null and l_any_err_occured_flg <> 'Y') then
1449 open check_create_ci_priv(p_ci_type_id, p_project_id);
1450 fetch check_create_ci_priv into l_check_create_ci;
1451 if check_create_ci_priv%NOTFOUND then
1452 PA_UTILS.ADD_MESSAGE
1453 (p_app_short_name => 'PA',
1454 p_msg_name => 'PA_CI_NO_SECURITY');
1455 if l_debug_mode = 'Y' then
1456 pa_debug.g_err_stage:= 'Either the project type or role selected by you doesnt have privilge to create the control item';
1457 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1458 end if;
1459 close check_create_ci_priv;
1460 l_any_err_occured_flg := 'Y';
1461 else
1462 close check_create_ci_priv;
1463 end if;
1464 end if;
1465
1466 if( l_any_err_occured_flg = 'Y' ) then
1467 raise fnd_api.g_exc_error;
1468 end if;
1469
1470 if l_debug_mode = 'Y' then
1471 pa_debug.reset_curr_function;
1472 end if;
1473
1474 exception
1475 when fnd_api.g_exc_error then
1476 x_return_status := fnd_api.g_ret_sts_error;
1477 l_msg_count := fnd_msg_pub.count_msg;
1478 if l_msg_count = 1 then
1479 pa_interface_utils_pub.get_messages
1480 (p_encoded => fnd_api.g_false,
1481 p_msg_index => 1,
1482 p_msg_count => l_msg_count ,
1483 p_msg_data => l_msg_data ,
1484 p_data => l_data,
1485 p_msg_index_out => l_msg_index_out );
1486 x_msg_data := l_data;
1487 x_msg_count := l_msg_count;
1488 else
1489 x_msg_count := l_msg_count;
1490 end if;
1491
1492 /*Initialize the out variables back to null*/
1493 x_ci_type_class_code := null;
1494 x_auto_number_flag := null;
1495 x_source_attrs_enabled_flag := null;
1496 --in out parameters are set to their initial values.
1497 p_project_id := l_ini_proj_id;
1498 p_ci_type_id := l_ini_ci_type_id;
1499 --Reset the stack
1500 if l_debug_mode = 'Y' then
1501 pa_debug.reset_curr_function;
1502 end if;
1503 --raise the exception
1504 raise;
1505 when others then
1506 x_return_status := fnd_api.g_ret_sts_unexp_error;
1507 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
1508 p_procedure_name => 'check_create_ci_allowed',
1509 p_error_text => substrb(sqlerrm,1,240));
1510 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1511 p_data => x_msg_data);
1512 /*Initialize the out variables back to null*/
1513 x_ci_type_class_code := null;
1514 x_auto_number_flag := null;
1515 x_source_attrs_enabled_flag := null;
1516 --in out parameters are set to their initial values.
1517 p_project_id := l_ini_proj_id;
1518 p_ci_type_id := l_ini_ci_type_id;
1519 --Reset the stack
1520 if l_debug_mode = 'Y' then
1521 pa_debug.reset_curr_function;
1522 end if;
1523 --raise the exception
1524 raise;
1525 end check_create_ci_allowed;
1526
1527
1528 /*this procedure will validate the parameters and if all the paramters are valid will call the api's to
1529 create the control item*/
1530 PROCEDURE validate_param_and_create(
1531 p_orig_system_code IN VARCHAR2
1532 ,p_orig_system_reference IN VARCHAR2
1533 ,p_project_id IN NUMBER := null
1534 ,p_ci_type_id IN NUMBER := null
1535 ,p_auto_number_flag IN VARCHAR2 := null
1536 ,p_source_attrs_enabled_flag IN VARCHAR2 := null
1537 ,p_ci_type_class_code IN VARCHAR2 := null
1538 ,p_summary IN VARCHAR2
1539 ,p_ci_number IN VARCHAR2 := null
1540 ,p_description IN VARCHAR2 := null
1541 ,p_status_code IN VARCHAR2 := null
1542 ,p_status IN VARCHAR2 := null
1543 ,p_owner_id IN NUMBER := null
1544 ,p_highlighted_flag IN VARCHAR2 := 'N'
1545 ,p_progress_status_code IN VARCHAR2 := null
1546 ,p_progress_as_of_date IN DATE := null
1547 ,p_status_overview IN VARCHAR2 := null
1548 ,p_classification_code IN NUMBER
1549 ,p_reason_code IN NUMBER
1550 ,p_object_id IN NUMBER := null
1551 ,p_object_type IN VARCHAR2 := null
1552 ,p_date_required IN DATE := null
1553 ,p_date_closed IN DATE := null
1554 ,p_closed_by_id IN NUMBER := null
1555 ,p_resolution IN VARCHAR2 := null
1556 ,p_resolution_code IN NUMBER := null
1557 ,p_priority_code IN VARCHAR2 := null
1558 ,p_effort_level_code IN VARCHAR2 := null
1559 ,p_price IN NUMBER := null
1560 ,p_price_currency_code IN VARCHAR2 := null
1561 ,p_source_type_name IN VARCHAR2 := null
1562 ,p_source_type_code IN VARCHAR2 := null
1563 ,p_source_number IN VARCHAR2 := null
1564 ,p_source_comment IN VARCHAR2 := null
1565 ,p_source_date_received IN DATE := null
1566 ,p_source_organization IN VARCHAR2 := null
1567 ,p_source_person IN VARCHAR2 := null
1568 ,p_attribute_category IN VARCHAR2 := null
1569 ,p_attribute1 IN VARCHAR2 := null
1570 ,p_attribute2 IN VARCHAR2 := null
1571 ,p_attribute3 IN VARCHAR2 := null
1572 ,p_attribute4 IN VARCHAR2 := null
1573 ,p_attribute5 IN VARCHAR2 := null
1574 ,p_attribute6 IN VARCHAR2 := null
1575 ,p_attribute7 IN VARCHAR2 := null
1576 ,p_attribute8 IN VARCHAR2 := null
1577 ,p_attribute9 IN VARCHAR2 := null
1578 ,p_attribute10 IN VARCHAR2 := null
1579 ,p_attribute11 IN VARCHAR2 := null
1580 ,p_attribute12 IN VARCHAR2 := null
1581 ,p_attribute13 IN VARCHAR2 := null
1582 ,p_attribute14 IN VARCHAR2 := null
1583 ,p_attribute15 IN VARCHAR2 := null
1584 ,x_ci_id OUT NOCOPY NUMBER
1585 ,x_ci_number OUT NOCOPY NUMBER
1586 ,x_return_status OUT NOCOPY VARCHAR2
1587 ,x_msg_count OUT NOCOPY NUMBER
1588 ,x_msg_data OUT NOCOPY VARCHAR2
1589 )
1590 is
1591
1592 cursor get_prj_mgr_id(p_project_id NUMBER) is
1593 select hp.party_id ,ppf.full_name
1594 from pa_project_parties party,
1595 pa_project_role_types rtype,
1596 hz_parties hp,
1597 per_all_people_f ppf
1598 where party.project_id = p_project_id
1599 and party.project_role_id = rtype.project_role_id
1600 and rtype.project_role_type = 'PROJECT MANAGER'
1601 and party.resource_source_id = pa_project_parties_utils.get_project_manager(p_project_id)
1602 and hp.orig_system_reference = 'PER:'||party.resource_source_id
1603 and ppf.person_id = party.resource_source_id;
1604
1605 cursor get_strtng_sts_frm_sts_nme(p_ci_type_id number, p_status varchar2)
1606 is
1607 select pstat.project_status_code, pstat.project_status_name, pstat.project_system_status_code
1608 from
1609 pa_obj_status_lists obj,
1610 pa_status_lists list,
1611 pa_status_list_items item,
1612 pa_project_statuses pstat
1613 where obj.object_id = p_ci_type_id
1614 and obj.status_list_id = list.status_list_id
1615 and list.status_type = 'CONTROL_ITEM'
1616 and sysdate between list.start_date_active and nvl(list.end_date_active,sysdate)
1617 and list.status_list_id = item.status_list_id
1618 and pstat.project_status_code = item.project_status_code
1619 and pstat.project_system_status_code in ('CI_DRAFT','CI_WORKING')
1620 and pstat.project_status_name = p_status; /*where clause on status name*/
1621
1622 cursor get_strtng_sts_frm_sts_cde(p_ci_type_id number, p_status_code varchar2)
1623 is
1624 select pstat.project_status_code, pstat.project_status_name, pstat.project_system_status_code
1625 from
1626 pa_obj_status_lists obj,
1627 pa_status_lists list,
1628 pa_status_list_items item,
1629 pa_project_statuses pstat
1630 where obj.object_id = p_ci_type_id
1631 and obj.status_list_id = list.status_list_id
1632 and list.status_type = 'CONTROL_ITEM'
1633 and sysdate between list.start_date_active and nvl(list.end_date_active,sysdate)
1634 and list.status_list_id = item.status_list_id
1635 and pstat.project_status_code = item.project_status_code
1636 and pstat.project_system_status_code in ('CI_DRAFT','CI_WORKING')
1637 and pstat.project_status_code = p_status_code; /*where clause on status code*/
1638
1639 cursor validate_owner_id(p_project_id number, p_owner_id number)
1640 is
1641 select distinct resource_party_id,
1642 resource_source_name
1643 from PA_PROJECT_PARTIES_V
1644 where party_type <> 'ORGANIZATION'
1645 and project_id = p_project_id
1646 and resource_party_id = p_owner_id;
1647
1648 cursor validate_progress_sts_code(p_progress_status_code varchar2)
1649 is
1650 select project_status_code
1651 from pa_project_statuses
1652 where
1653 (trunc(sysdate) between nvl(start_date_active, trunc(sysdate)) and nvl(end_date_active, trunc(sysdate)))
1654 and status_type = 'PROGRESS'
1655 and project_status_code = p_progress_status_code;
1656
1657 cursor validate_clsfcation_code(p_classification_code number)
1658 is
1659 select cat.class_code_id code
1660 from pa_class_codes cat
1661 ,pa_ci_types_b typ
1662 where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
1663 and typ.ci_type_id = p_ci_type_id
1664 and cat.class_category = typ.classification_category
1665 and cat.class_code_id = p_classification_code;
1666
1667 cursor validate_reason_code(p_reason_code number)
1668 is
1669 select cat.class_code_id code
1670 from pa_class_codes cat
1671 ,pa_ci_types_b typ
1672 where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
1673 and typ.ci_type_id = p_ci_type_id
1674 and cat.class_category = typ.reason_category
1675 and cat.class_code_id = p_reason_code;
1676
1677 cursor validate_obj_id(p_project_id number, p_object_id number)
1678 is
1679 select proj_element_id
1680 from PA_FIN_LATEST_PUB_TASKS_V
1681 where project_id = p_project_id
1682 and proj_element_id = p_object_id;
1683
1684 l_user_id NUMBER := 0;
1685 l_party_id NUMBER := 0;
1686 l_resp_id NUMBER:= 0;
1687
1688 cursor validate_cls_by_id(p_project_id number, p_closed_by_id number)
1689 is
1690 select 'Y'
1691 from PA_PROJECT_PARTIES_V
1692 where party_type <> 'ORGANIZATION'
1693 and project_id = p_project_id
1694 and resource_party_id = p_closed_by_id --validating the p_closed_by_id
1695 and pa_ci_security_pkg.check_proj_auth_ci(p_project_id, l_user_id, l_resp_id) = 'T'; --validating the project authority here.
1696
1697 cursor validate_resolution_code(p_resolution_code number)
1698 is
1699 select cat.class_code_id code
1700 from pa_class_codes cat
1701 ,pa_ci_types_b typ
1702 where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
1703 and typ.ci_type_id= p_ci_type_id
1704 and cat.class_category=typ.resolution_category
1705 and cat.class_code_id = p_resolution_code;
1706
1707 cursor validate_priority_code(p_priority_code varchar2)
1708 is
1709 select lookup_code
1710 FROM pa_lookups
1711 WHERE lookup_type='PA_TASK_PRIORITY_CODE'
1712 and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
1713 and enabled_flag = 'Y'
1714 and (trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate)))
1715 and lookup_code = p_priority_code;
1716
1717 cursor validate_eff_lvl_code(p_effort_level_code varchar2)
1718 is
1719 select lookup_code
1720 FROM pa_lookups
1721 WHERE lookup_type='PA_CI_EFFORT_LEVELS'
1722 and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
1723 and enabled_flag = 'Y'
1724 and (trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate)))
1725 and lookup_code = p_effort_level_code;
1726
1727 cursor validate_prj_currency(p_price_currency_code varchar2)
1728 is
1729 select currency_code
1730 from fnd_currencies_vl
1731 where enabled_flag = 'Y'
1732 and trunc(sysdate) between nvl(start_date_active,trunc(sysdate)) and nvl(end_date_active,trunc(sysdate))
1733 and currency_code = p_price_currency_code;
1734
1735 validate_owner_id_rec validate_owner_id%rowtype;
1736 get_strtng_sts_frm_sts_nme_rec get_strtng_sts_frm_sts_nme%rowtype;
1737 get_strtng_sts_frm_sts_cde_rec get_strtng_sts_frm_sts_cde%rowtype;
1738 get_prj_mgr_id_rec get_prj_mgr_id%rowtype;
1739
1740 l_msg_count NUMBER := 0;
1741 l_data VARCHAR2(2000);
1742 l_msg_data VARCHAR2(2000);
1743 l_msg_index_out NUMBER;
1744 l_module_name VARCHAR2(200);
1745
1746
1747 l_any_err_occured_flg VARCHAR2(1);
1748
1749 l_summary pa_control_items.summary%type;
1750 l_description pa_control_items.description%type;
1751 l_status_code pa_project_statuses.project_status_code%type;
1752 l_status_name pa_project_statuses.project_status_name%type;
1753 l_system_status_code pa_project_statuses.project_system_status_code%type;
1754
1755 l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1756 l_ci_number_num NUMBER(15) := NULL;
1757 l_system_number_id NUMBER(15) := NULL;
1758
1759 l_owner_id per_all_people_f.party_id%type;
1760 l_owner_name per_all_people_f.full_name%type;
1761 l_progress_status_code pa_project_statuses.project_status_code%type;
1762 l_classification_code pa_control_items.classification_code_id%type;
1763 l_reason_code pa_control_items.reason_code_id%type;
1764 l_object_id pa_proj_elements.proj_element_id%type;
1765 l_date_closed pa_control_items.date_closed%type;
1766 l_closed_by_id pa_control_items.closed_by_id%type;
1767 l_valid_clsby_id VARCHAR2(1);
1768 l_resolution_code pa_control_items.resolution_code_id%type;
1769 l_resolution pa_control_items.resolution%type;
1770 l_effort_level_code pa_control_items.effort_level_code%type;
1771 l_priority_code pa_control_items.priority_code%type;
1772 l_price_currency_code pa_control_items.price_currency_code%type;
1773 l_price pa_control_items.price%type;
1774
1775 l_attribute_category pa_control_items.attribute_category%type;
1776 l_attribute1 pa_control_items.attribute1%type;
1777 l_attribute2 pa_control_items.attribute1%type;
1778 l_attribute3 pa_control_items.attribute1%type;
1779 l_attribute4 pa_control_items.attribute1%type;
1780 l_attribute5 pa_control_items.attribute1%type;
1781 l_attribute6 pa_control_items.attribute1%type;
1782 l_attribute7 pa_control_items.attribute1%type;
1783 l_attribute8 pa_control_items.attribute1%type;
1784 l_attribute9 pa_control_items.attribute1%type;
1785 l_attribute10 pa_control_items.attribute1%type;
1786 l_attribute11 pa_control_items.attribute1%type;
1787 l_attribute12 pa_control_items.attribute1%type;
1788 l_attribute13 pa_control_items.attribute1%type;
1789 l_attribute14 pa_control_items.attribute1%type;
1790 l_attribute15 pa_control_items.attribute1%type;
1791
1792
1793 l_source_type_name pa_lookups.meaning%type;
1794 l_source_type_code pa_control_items.source_type_code%type;
1795 l_source_number pa_control_items.source_number%type;
1796 l_source_comment pa_control_items.source_comment%type;
1797 l_source_date_received pa_control_items.source_date_received%type;
1798 l_source_organization pa_control_items.source_organization%type;
1799 l_source_person pa_control_items.source_person%type;
1800 l_ci_id pa_control_items.ci_id%type;
1801 begin
1802 -- initialize the return status to success
1803 x_return_status := fnd_api.g_ret_sts_success;
1804 x_msg_count := 0;
1805
1806 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
1807 l_module_name := 'validate_param_and_create' || g_module_name;
1808
1809 if l_debug_mode = 'Y' then
1810 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.validate_param_and_create', p_debug_mode => l_debug_mode);
1811 end if;
1812
1813 if l_debug_mode = 'Y' then
1814 pa_debug.write(l_module_name, 'start of validate_param_and_create', l_debug_level3);
1815 end if;
1816
1817 --setting the err occured flag to N initially.
1818 l_any_err_occured_flg := 'N';
1819
1820 --get the user id and the party id for the current logged in user.
1821 l_user_id := fnd_global.user_id;
1822 l_party_id := pa_control_items_utils.getpartyid(l_user_id);
1823 l_resp_id := fnd_global.resp_id;
1824
1825 if(p_summary is null) then
1826 PA_UTILS.ADD_MESSAGE
1827 (p_app_short_name => 'PA',
1828 p_msg_name => 'PA_CI_NO_SUMMARY'); --existing msg
1829 if l_debug_mode = 'Y' then
1830 pa_debug.g_err_stage:= 'Summary passed is null';
1831 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1832 end if;
1833 l_any_err_occured_flg := 'Y';
1834 end if;
1835
1836 l_summary := p_summary;
1837 l_description := p_description;
1838
1839 if (p_status_code is null and p_status is null) then
1840 /*get the default starting status*/
1841 l_status_code := PA_CONTROL_ITEMS_UTILS.Get_Initial_Ci_Status(p_ci_type_id);
1842 /*l_status_code would always be CI_WORKING here and below select would always give CI_WORKING
1843 for project_system_status_code. So */
1844 select project_system_status_code
1845 into l_system_status_code
1846 from pa_project_statuses
1847 where project_status_code = l_status_code
1848 and status_type = 'CONTROL_ITEM';
1849
1850 elsif(p_status_code is null and p_status is not null) then
1851
1852 /* the below cursor query will give only one record as status_type and project_status_name forms a
1853 unique index on pa_project_statuses table We derive p_status_code from */
1854 open get_strtng_sts_frm_sts_nme(p_ci_type_id , p_status);
1855 fetch get_strtng_sts_frm_sts_nme into get_strtng_sts_frm_sts_nme_rec;
1856 if (get_strtng_sts_frm_sts_nme%notfound) then
1857 PA_UTILS.Add_message ( p_app_short_name => 'PA'
1858 ,p_msg_name => 'PA_CI_INV_STATUS');
1859 if l_debug_mode = 'Y' then
1860 pa_debug.g_err_stage:= 'Invalid status passed';
1861 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1862 end if;
1863 l_any_err_occured_flg := 'Y';
1864 close get_strtng_sts_frm_sts_nme;
1865 else
1866 l_status_code := get_strtng_sts_frm_sts_nme_rec.project_status_code;
1867 l_status_name := get_strtng_sts_frm_sts_nme_rec.project_status_name;
1868 l_system_status_code := get_strtng_sts_frm_sts_nme_rec.project_system_status_code;
1869 close get_strtng_sts_frm_sts_nme;
1870 end if;
1871
1872 elsif(p_status_code is not null) then
1873 /*Validate the p_status_code*/
1874
1875 open get_strtng_sts_frm_sts_cde(p_ci_type_id, p_status_code);
1876 fetch get_strtng_sts_frm_sts_cde into get_strtng_sts_frm_sts_cde_rec;
1877 if (get_strtng_sts_frm_sts_cde%notfound) then
1878 PA_UTILS.Add_message ( p_app_short_name => 'PA'
1879 ,p_msg_name => 'PA_CI_INVALID_STATUS_CODE'); --this msg is already there
1880 if l_debug_mode = 'Y' then
1881 pa_debug.g_err_stage:= 'Invalid status code passed';
1882 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1883 end if;
1884 l_any_err_occured_flg := 'Y';
1885 close get_strtng_sts_frm_sts_cde;
1886 else
1887 l_status_code := get_strtng_sts_frm_sts_cde_rec.project_status_code;
1888 l_status_name := get_strtng_sts_frm_sts_cde_rec.project_status_name;
1889 l_system_status_code := get_strtng_sts_frm_sts_cde_rec.project_system_status_code;
1890 close get_strtng_sts_frm_sts_cde;
1891 end if;
1892 end if;
1893
1894 if(p_owner_id is null) then
1895 /*if owner id is not passed default it with the project manager id*/
1896 open get_prj_mgr_id(p_project_id);
1897 fetch get_prj_mgr_id into get_prj_mgr_id_rec;
1898 if (get_prj_mgr_id%notfound) then
1899 pa_utils.add_message( p_app_short_name => 'PA'
1900 ,p_msg_name => 'PA_CI_NO_MGR_ID'); --Need to check what this msg context shd be.
1901 if l_debug_mode = 'Y' then
1902 pa_debug.g_err_stage:= 'Could not find project manager';
1903 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1904 end if;
1905 close get_prj_mgr_id;
1906 l_any_err_occured_flg := 'Y';
1907 else
1908 l_owner_id := get_prj_mgr_id_rec.party_id;
1909 l_owner_name := get_prj_mgr_id_rec.full_name;
1910 close get_prj_mgr_id;
1911 end if;
1912 elsif(p_owner_id is not null) then --p_owner_id is null
1913 /* validate the passed owner id*/
1914
1915 open validate_owner_id(p_project_id, p_owner_id);
1916 fetch validate_owner_id into validate_owner_id_rec;
1917 if(validate_owner_id%notfound) then
1918 pa_utils.add_message( p_app_short_name => 'PA'
1919 ,p_msg_name => 'PA_CI_NO_OWNER'); --existing msg
1920 if l_debug_mode = 'Y' then
1921 pa_debug.g_err_stage:= 'Invalid owner id passed';
1922 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1923 end if;
1924 l_any_err_occured_flg := 'Y';
1925 close validate_owner_id;
1926 else
1927 l_owner_id := validate_owner_id_rec.resource_party_id;
1928 l_owner_name := validate_owner_id_rec.resource_source_name;
1929 close validate_owner_id;
1930 end if;
1931 end if; --p_owner_id is null
1932
1933 /* Validate the p_progress_status_code if it is not null else default the progress with the on_track*/
1934 if(p_progress_status_code is not null) then
1935 open validate_progress_sts_code(p_progress_status_code);
1936 fetch validate_progress_sts_code into l_progress_status_code;
1937 if (validate_progress_sts_code%notfound) then
1938 pa_utils.add_message( p_app_short_name => 'PA'
1939 ,p_msg_name => 'PA_CI_INV_PRG_CODE');
1940 if l_debug_mode = 'Y' then
1941 pa_debug.g_err_stage:= 'Invalid progress status code passed';
1942 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1943 end if;
1944 l_any_err_occured_flg := 'Y';
1945 close validate_progress_sts_code;
1946 else --if (validate_progress_sts_code%notfound) then
1947 close validate_progress_sts_code;
1948 end if;
1949 else
1950 /*else default the progress with the on_track*/
1951 l_progress_status_code := 'PROGRESS_STAT_ON_TRACK';
1952 end if;
1953
1954 /*Check for mandatory classification code and reason code*/
1955 l_reason_code := p_reason_code;
1956 if (p_classification_code is null or p_reason_code is null )
1957 then
1958 if (p_classification_code is null) then
1959 pa_utils.add_message( p_app_short_name => 'PA'
1960 ,p_msg_name => 'PA_CI_MIS_CLASSIFICATION_COD'); --msg is already there
1961 if l_debug_mode = 'Y' then
1962 pa_debug.g_err_stage:= 'classification_code is null';
1963 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1964 end if;
1965 l_any_err_occured_flg := 'Y';
1966 end if;
1967 if (p_reason_code is null) then
1968 pa_utils.add_message( p_app_short_name => 'PA'
1969 ,p_msg_name => 'PA_CI_MISS_REASON_CODE'); --msg is already there
1970 if l_debug_mode = 'Y' then
1971 pa_debug.g_err_stage:= 'reason_code is null';
1972 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1973 end if;
1974 l_any_err_occured_flg := 'Y';
1975 end if;
1976 end if;
1977
1978 /* validate the passed classification_code*/
1979 if(p_classification_code is not null) then
1980 open validate_clsfcation_code(p_classification_code);
1981 fetch validate_clsfcation_code into l_classification_code;
1982 if (validate_clsfcation_code%notfound) then
1983 pa_utils.add_message( p_app_short_name => 'PA'
1984 ,p_msg_name => 'PA_CI_CLASSIFICATION_INV');
1985 if l_debug_mode = 'Y' then
1986 pa_debug.g_err_stage:= 'Invalid classification code passed';
1987 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1988 end if;
1989 l_any_err_occured_flg := 'Y';
1990 close validate_clsfcation_code;
1991 else
1992 close validate_clsfcation_code;
1993 end if;
1994 end if;
1995
1996 /* validate the passed reason_code*/
1997 if(p_reason_code is not null) then
1998 open validate_reason_code(p_reason_code);
1999 fetch validate_reason_code into l_reason_code;
2000 if (validate_reason_code%notfound) then
2001 pa_utils.add_message( p_app_short_name => 'PA'
2002 ,p_msg_name => 'PA_CI_INV_REA_CODE');
2003 if l_debug_mode = 'Y' then
2004 pa_debug.g_err_stage:= 'Invalid reason code passed';
2005 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2006 end if;
2007 l_any_err_occured_flg := 'Y';
2008 close validate_reason_code;
2009 else
2010 close validate_reason_code;
2011 end if;
2012 end if;
2013
2014 /*Currently only PA_TASKS type of objects are supported*/
2015 if(p_object_type is not null and p_object_type <> 'PA_TASKS') then
2016 pa_utils.add_message( p_app_short_name => 'PA'
2017 ,p_msg_name => 'PA_CI_INV_OBJ_TYPE');
2018 if l_debug_mode = 'Y' then
2019 pa_debug.g_err_stage:= 'Invalid object type passed';
2020 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2021 end if;
2022 l_any_err_occured_flg := 'Y';
2023 end if;
2024
2025 /* Validate the object id passed*/
2026 if (p_object_id is not null) then
2027 open validate_obj_id(p_project_id, p_object_id);
2028 fetch validate_obj_id into l_object_id;
2029 if (validate_obj_id%notfound) then
2030 pa_utils.add_message( p_app_short_name => 'PA'
2031 ,p_msg_name => 'PA_CI_INV_OBJ_ID');
2032 if l_debug_mode = 'Y' then
2033 pa_debug.g_err_stage:= 'Invalid Object Id passed';
2034 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2035 end if;
2036 l_any_err_occured_flg := 'Y';
2037 close validate_obj_id;
2038 end if;
2039 close validate_obj_id;
2040 end if;
2041
2042
2043 /*validations for p_closed_date parameter. The date must be supplied for a item in closed status, for statuses other than closed
2044 we should ignore this value*/
2045 if( l_system_status_code = 'CI_CLOSED') then
2046 if(p_date_closed is null) then
2047 pa_utils.add_message( p_app_short_name => 'PA'
2048 ,p_msg_name => 'PA_CI_CLS_DATE_MISS');
2049 if l_debug_mode = 'Y' then
2050 pa_debug.g_err_stage:= 'For a control item in closed status, closed_date is missing';
2051 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2052 end if;
2053 l_any_err_occured_flg := 'Y';
2054 else
2055 l_date_closed := p_date_closed;
2056 end if;
2057 else
2058 /* for status other than closed ignore the closed date*/
2059 l_date_closed := null;
2060 end if;
2061
2062 /*validations for p_closed_by_id parameter. The p_closed_by_id must be supplied for a item in closed status
2063 and it must be a valid id(validte the closed_by_id passed). for statuses other than closed, ignore the p_closed_by_id.*/
2064
2065 if( l_system_status_code = 'CI_CLOSED') then
2066
2067 if(p_closed_by_id is null) then
2068 pa_utils.add_message( p_app_short_name => 'PA'
2069 ,p_msg_name => 'PA_CI_CLS_BYID_MISS');
2070 if l_debug_mode = 'Y' then
2071 pa_debug.g_err_stage:= 'For a control item in closed status, closed_by_id is missing';
2072 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2073 end if;
2074 l_any_err_occured_flg := 'Y';
2075 else--if(p_closed_by_id is null) then
2076 /*validate the p_closed_by_id. Also the persons with the project authority can close the item*/
2077 open validate_cls_by_id(p_project_id , p_closed_by_id);
2078 fetch validate_cls_by_id into l_valid_clsby_id;
2079 if(validate_cls_by_id%notfound) then
2080 pa_utils.add_message( p_app_short_name => 'PA'
2081 ,p_msg_name => 'PA_CI_INV_CLS_BYID');
2082 if l_debug_mode = 'Y' then
2083 pa_debug.g_err_stage:= 'inavlid closed_by_id is passed or user doesnt have project authority';
2084 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2085 end if;
2086 l_any_err_occured_flg := 'Y';
2087 close validate_cls_by_id;
2088 else
2089 l_closed_by_id := p_closed_by_id;
2090 close validate_cls_by_id;
2091 end if;--if(sql%notfound) then
2092 end if;--if(p_closed_by_id is null) then
2093
2094 else--( l_system_status_code = 'CI_CLOSED') then
2095 /* for status other than closed ignore the closed by id*/
2096 l_closed_by_id := null;
2097 end if;
2098
2099 /*
2100 Validate the resolution attribute.
2101 1)if resoltuion id passed then validate the id.
2102 2)check status. if status is sbumitted then resolution id and resoltuion both must be passed. hold it.
2103 3)also resolution can be enetered for any status .simply stamp the value in db for resoltuion irrespective of staus after validating
2104 it
2105 4) Also in UI we can have resolution with missing resoltuion category. Is it ok to have this.- Hold this.
2106 */
2107 if(p_resolution_code is not null) then
2108 open validate_resolution_code(p_resolution_code);
2109 fetch validate_resolution_code into l_resolution_code;
2110 if (validate_resolution_code%notfound) then
2111 pa_utils.add_message( p_app_short_name => 'PA'
2112 ,p_msg_name => 'PA_CI_INV_RES_CODE');
2113 if l_debug_mode = 'Y' then
2114 pa_debug.g_err_stage:= 'Invalid resolution code passed';
2115 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2116 end if;
2117 l_any_err_occured_flg := 'Y';
2118 close validate_resolution_code;
2119 end if;
2120 close validate_resolution_code;
2121 end if;
2122
2123 l_resolution := p_resolution;
2124
2125 /*Validate the priority level code*/
2126 if(p_priority_code is not null) then
2127 open validate_priority_code(p_priority_code);
2128 fetch validate_priority_code into l_priority_code;
2129 if (validate_priority_code%notfound) then
2130 pa_utils.add_message( p_app_short_name => 'PA'
2131 ,p_msg_name => 'PA_CI_INV_PRIO_CODE');
2132 if l_debug_mode = 'Y' then
2133 pa_debug.g_err_stage:= 'Invalid priority code passed';
2134 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2135 end if;
2136 l_any_err_occured_flg := 'Y';
2137 close validate_priority_code;
2138 end if;
2139 close validate_priority_code;
2140 end if;
2141
2142 /*Validate the effort level code*/
2143 if(p_effort_level_code is not null) then
2144 open validate_eff_lvl_code(p_effort_level_code);
2145 fetch validate_eff_lvl_code into l_effort_level_code;
2146 if (validate_eff_lvl_code%notfound) then
2147 pa_utils.add_message( p_app_short_name => 'PA'
2148 ,p_msg_name => 'PA_CI_INV_EFFR_CODE');
2149 if l_debug_mode = 'Y' then
2150 pa_debug.g_err_stage:= 'Invalid effort code passed';
2151 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2152 end if;
2153 l_any_err_occured_flg := 'Y';
2154 close validate_eff_lvl_code;
2155 end if;
2156 close validate_eff_lvl_code;
2157 end if;
2158
2159 /* if currency_code is null then default it with project currency else validate the passed value*/
2160 if (p_price_currency_code is null) then
2161 select projfunc_currency_code
2162 into l_price_currency_code
2163 from pa_projects_all
2164 where project_id = p_project_id;
2165 else
2166 open validate_prj_currency(p_price_currency_code);
2167 fetch validate_prj_currency into l_price_currency_code;
2168 if (validate_prj_currency%notfound) then
2169 pa_utils.add_message( p_app_short_name => 'PA'
2170 ,p_msg_name => 'PA_CI_INV_CURR_CODE');
2171 if l_debug_mode = 'Y' then
2172 pa_debug.g_err_stage:= 'Invalid currency code passed';
2173 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2174 end if;
2175 l_any_err_occured_flg := 'Y';
2176 close validate_prj_currency;
2177 end if;
2178 close validate_prj_currency;
2179 end if;
2180
2181 /*Needto check if we should have the validation that whenever price is passed a valid currency must be there for the control item*/
2182 l_price := p_price;
2183
2184 /* source attributes would only be acknowledged only if the enable source attributes flag was set at the control item type level*/
2185 /*need to write the code to validate the source type/dervive source type code from surce type name*/
2186 if(p_source_attrs_enabled_flag = 'Y') then
2187 l_source_type_name := p_source_type_name;
2188 l_source_type_code := p_source_type_code;
2189 l_source_number := p_source_number;
2190 l_source_comment := p_source_comment;
2191 l_source_date_received := p_source_date_received;
2192 l_source_organization := p_source_organization;
2193 l_source_person := p_source_person;
2194 end if;
2195
2196 l_attribute_category := p_attribute_category;
2197 l_attribute1 := p_attribute1;
2198 l_attribute2 := p_attribute2;
2199 l_attribute3 := p_attribute3;
2200 l_attribute4 := p_attribute4;
2201 l_attribute5 := p_attribute5;
2202 l_attribute6 := p_attribute6;
2203 l_attribute7 := p_attribute7;
2204 l_attribute8 := p_attribute8;
2205 l_attribute9 := p_attribute9;
2206 l_attribute10 := p_attribute10;
2207 l_attribute11 := p_attribute11;
2208 l_attribute12 := p_attribute12;
2209 l_attribute13 := p_attribute13;
2210 l_attribute14 := p_attribute14;
2211 l_attribute15 := p_attribute15;
2212
2213 /* if we reach here in code then all the attributes have been validated.
2214 So get the next value from the sequence which will be used as the ci_id*/
2215 select pa_control_items_s.nextval
2216 into l_ci_id
2217 from dual;
2218
2219 /* now generate the ci_number;*/
2220 if(l_status_code is not null) then
2221 begin
2222 /*ci_number would be generated only for the items with auto numbering and which are not in draft status*/
2223 if p_auto_number_flag = 'Y' and l_system_status_code <> 'CI_DRAFT' then
2224 loop
2225 pa_system_numbers_pkg.get_next_number (
2226 p_object1_pk1_value => p_project_id
2227 ,p_object1_type => 'PA_PROJECTS'
2228 ,p_object2_pk1_value => p_ci_type_id
2229 ,p_object2_type => p_ci_type_class_code
2230 ,x_system_number_id => l_system_number_id
2231 ,x_next_number => l_ci_number_num
2232 ,x_return_status => x_return_status
2233 ,x_msg_count => x_msg_count
2234 ,x_msg_data => x_msg_data);
2235
2236 if x_return_status <> FND_API.g_ret_sts_success then
2237 if l_debug_mode = 'Y' then
2238 pa_debug.g_err_stage:= 'Failed in pa_system_numbers_pkg.get_next_number';
2239 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2240 end if;
2241 raise api_error;
2242 end if;
2243 l_ci_number_char := TO_CHAR(l_ci_number_num);
2244
2245 -- call Client Extension here
2246 pa_ci_number_client_extn.get_next_number (
2247 p_object1_pk1_value => p_project_id
2248 ,p_object1_type => 'PA_PROJECTS'
2249 ,p_object2_pk1_value => p_ci_type_id
2250 ,p_object2_type => p_ci_type_class_code
2251 ,p_next_number => l_ci_number_char
2252 ,x_return_status => x_return_status
2253 ,x_msg_count => x_msg_count
2254 ,x_msg_data => x_msg_data);
2255
2256 exit when pa_control_items_pvt.ci_number_exists(p_project_id, l_ci_number_char
2257 ,p_ci_type_id) = FALSE;
2258 end loop;
2259 else --p_auto_number_flag = 'Y' and l_system_status_code <> 'CI_DRAFT'
2260 /*For manual numbering check if passed ci_number already exist.*/
2261 l_ci_number_char := p_ci_number;
2262 if pa_control_items_pvt.ci_number_exists(p_project_id, l_ci_number_char ,p_ci_type_id) = TRUE then
2263 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2264 ,p_msg_name => 'PA_CI_DUPLICATE_CI_NUMBER');--msg already there
2265 if l_debug_mode = 'Y' then
2266 pa_debug.g_err_stage:= 'Duplicate ci_number passed.';
2267 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2268 end if;
2269 l_any_err_occured_flg := 'Y';
2270 end if;
2271 end if;--p_auto_number_flag = 'Y' and l_system_status_code <> 'CI_DRAFT'
2272
2273 /* if auto numbering is enabled then l_ci_number_char wont be null here. For manual numbering checking if
2274 passed ci_number was null.*/
2275 if l_ci_number_char is null and l_system_status_code <> 'CI_DRAFT' then
2276 pa_utils.add_message( p_app_short_name => 'PA'
2277 ,p_msg_name => 'PA_CI_NO_CI_NUMBER'); --msg already there
2278 if l_debug_mode = 'Y' then
2279 pa_debug.g_err_stage:= 'Missing ci_number';
2280 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2281 end if;
2282 l_any_err_occured_flg := 'Y';
2283 end if;
2284 exception
2285 when api_error then
2286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2287 /*need to get input here as to how to handle the exception and populate the stack with appropriate messages.
2288 Set some other flag l_other_excp here and use tht flag to handle the unexpected case of exception handling*/
2289 l_any_err_occured_flg := 'Y';
2290 end; --for begin
2291 end if;--l_status_code is not null
2292
2293 if l_debug_mode = 'Y' then
2294 pa_debug.g_err_stage:= 'about to call the table handler';
2295 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2296 end if;
2297
2298 if (l_any_err_occured_flg is not null and l_any_err_occured_flg <> 'Y') then
2299 pa_control_items_pkg.insert_row (
2300 p_ci_type_id => p_ci_type_id,
2301 p_summary => l_summary,
2302 p_status_code => l_status_code,
2303 p_owner_id => l_owner_id,
2304 p_highlighted_flag => p_highlighted_flag,
2305 p_progress_status_code => l_progress_status_code,
2306 p_progress_as_of_date => nvl(p_progress_as_of_date,sysdate),
2307 p_classification_code => l_classification_code,
2308 p_reason_code => l_reason_code,
2309 p_project_id => p_project_id,
2310 p_last_modified_by_id => l_party_id,
2311 p_object_type => p_object_type,
2312 p_object_id => l_object_id,
2313 p_ci_number => l_ci_number_char,
2314 p_date_required => p_date_required,
2315 p_date_closed => l_date_closed,
2316 p_closed_by_id => l_closed_by_id,
2317 p_description => l_description,
2318 p_status_overview => p_status_overview,
2319 p_resolution => l_resolution,
2320 p_resolution_code => l_resolution_code,
2321 p_priority_code => l_priority_code,
2322 p_effort_level_code => l_effort_level_code,
2323 p_price => l_price,
2324 p_price_currency_code => l_price_currency_code,
2325 p_source_type_code => l_source_type_code,
2326 p_source_comment => l_source_comment,
2327 p_source_number => l_source_number,
2328 p_source_date_received => l_source_date_received,
2329 p_source_organization => l_source_organization,
2330 p_source_person => l_source_person,
2331 p_attribute_category => l_attribute_category,
2332 p_attribute1 => l_attribute1,
2333 p_attribute2 => l_attribute2,
2334 p_attribute3 => l_attribute3,
2335 p_attribute4 => l_attribute4,
2336 p_attribute5 => l_attribute5,
2337 p_attribute6 => l_attribute6,
2338 p_attribute7 => l_attribute7,
2339 p_attribute8 => l_attribute8,
2340 p_attribute9 => l_attribute9,
2341 p_attribute10 => l_attribute10,
2342 p_attribute11 => l_attribute11,
2343 p_attribute12 => l_attribute12,
2344 p_attribute13 => l_attribute13,
2345 p_attribute14 => l_attribute14,
2346 p_attribute15 => l_attribute15,
2347 px_ci_id => l_ci_id,
2348 x_return_status => x_return_status,
2349 x_msg_count => x_msg_count,
2350 x_msg_data => x_msg_data,
2351 p_orig_system_code => p_orig_system_code,
2352 p_orig_system_reference=> p_orig_system_reference
2353 );
2354 end if;
2355
2356 if (x_return_status <> fnd_api.g_ret_sts_success) then
2357 if l_debug_mode = 'Y' then
2358 pa_debug.g_err_stage:= 'Missing ci_number';
2359 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2360 end if;
2361 raise fnd_api.g_exc_unexpected_error;
2362 end if;
2363
2364 x_ci_id := l_ci_id;
2365 x_ci_number := l_ci_number_char;
2366
2367 if( l_any_err_occured_flg = 'Y' ) then
2368 raise fnd_api.g_exc_error;
2369 end if;
2370 /* set the out variables ci_id and ci_number if there were no exceptions for any of the attributes*/
2371 x_ci_id := l_ci_id;
2372 x_ci_number := l_ci_number_char;
2373
2374 --rest the stack;
2375 if l_debug_mode = 'Y' then
2376 pa_debug.reset_curr_function;
2377 end if;
2378
2379 exception
2380 when fnd_api.g_exc_unexpected_error then
2381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2382 FND_MSG_PUB.Count_And_Get(
2383 p_count => x_msg_count ,
2384 p_data => x_msg_data );
2385 /*Initialize the out variables back to null*/
2386 x_ci_id := null;
2387 x_ci_number := null;
2388 --rest the stack;
2389 if l_debug_mode = 'Y' then
2390 pa_debug.reset_curr_function;
2391 end if;
2392 --raise the exception
2393 raise;
2394 when fnd_api.g_exc_error then
2395 x_return_status := fnd_api.g_ret_sts_error;
2396 l_msg_count := fnd_msg_pub.count_msg;
2397 if l_msg_count = 1 then
2398 pa_interface_utils_pub.get_messages
2399 (p_encoded => fnd_api.g_false,
2400 p_msg_index => 1,
2401 p_msg_count => l_msg_count ,
2402 p_msg_data => l_msg_data ,
2403 p_data => l_data,
2404 p_msg_index_out => l_msg_index_out );
2405 x_msg_data := l_data;
2406 x_msg_count := l_msg_count;
2407 else
2408 x_msg_count := l_msg_count;
2409 end if;
2410
2411 /*Initialize the out variables back to null*/
2412 x_ci_id := null;
2413 x_ci_number := null;
2414
2415 --rest the stack;
2416 if l_debug_mode = 'Y' then
2417 pa_debug.reset_curr_function;
2418 end if;
2419 --raise the exception
2420 raise;
2421 when others then
2422 x_return_status := fnd_api.g_ret_sts_unexp_error;
2423 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
2424 p_procedure_name => 'validate_param_and_create',
2425 p_error_text => substrb(sqlerrm,1,240));
2426 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2427 p_data => x_msg_data);
2428
2429 /*Initialize the out variables back to null*/
2430 x_ci_id := null;
2431 x_ci_number := null;
2432
2433
2434 --Reset the stack
2435 if l_debug_mode = 'Y' then
2436 pa_debug.reset_curr_function;
2437 end if;
2438 --raise the exception
2439 raise;
2440 end validate_param_and_create;
2441
2442 procedure check_create_action_allow(
2443 p_ci_id IN NUMBER := null,
2444 x_project_id OUT NOCOPY NUMBER,
2445 x_return_status OUT NOCOPY VARCHAR2,
2446 x_msg_count OUT NOCOPY NUMBER,
2447 x_msg_data OUT NOCOPY VARCHAR2)
2448 IS
2449
2450 cursor check_valid_ci_id(p_ci_id number) is
2451 select ci_id, project_id
2452 from pa_control_items
2453 where ci_id = p_ci_id;
2454
2455
2456 l_msg_count NUMBER := 0;
2457 l_data VARCHAR2(2000);
2458 l_msg_data VARCHAR2(2000);
2459 l_msg_index_out NUMBER;
2460 l_module_name VARCHAR2(200);
2461 l_any_err_occured_flg VARCHAR2(1);
2462 l_create_action_flg VARCHAR2(1) := null;
2463 l_ci_id pa_control_items.ci_id%type;
2464 l_check_valid_ci_id_rec check_valid_ci_id%rowtype;
2465 BEGIN
2466
2467 -- initialize the return status to success
2468 x_return_status := fnd_api.g_ret_sts_success;
2469 x_msg_count := 0;
2470
2471 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
2472 l_module_name := 'check_create_action_allow' || g_module_name;
2473
2474 if l_debug_mode = 'Y' then
2475 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.check_create_action_allow', p_debug_mode => l_debug_mode);
2476 end if;
2477
2478 if l_debug_mode = 'Y' then
2479 pa_debug.write(l_module_name, 'start of check_create_action_allow', l_debug_level3);
2480 end if;
2481
2482 --Setting this flag to N initially.
2483 l_any_err_occured_flg := 'N';
2484
2485 /* check if p_ci_id is not passed/null*/
2486 if(p_ci_id is null) then
2487 pa_utils.add_message
2488 (p_app_short_name => 'PA',
2489 p_msg_name => 'PA_CI_MISS_CI_ID');
2490 if l_debug_mode = 'Y' then
2491 pa_debug.g_err_stage:= 'ci_id is missing';
2492 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2493 end if;
2494 l_any_err_occured_flg := 'Y';
2495 end if;-- if(p_ci_id is null) then
2496
2497 /*check if p_ci_id is a valid control item id*/
2498 if(p_ci_id is not null)then
2499 open check_valid_ci_id(p_ci_id);
2500 fetch check_valid_ci_id into l_check_valid_ci_id_rec; --l_ci_id;
2501 if(check_valid_ci_id%notfound) then
2502 /*invalid ci_id*/
2503 pa_utils.add_message
2504 (p_app_short_name => 'PA',
2505 p_msg_name => 'PA_CI_INV_CI_ID');
2506 if l_debug_mode = 'Y' then
2507 pa_debug.g_err_stage:= 'ci_id is invalid';
2508 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2509 end if;
2510 close check_valid_ci_id;
2511 l_any_err_occured_flg := 'Y';
2512 else
2513 l_ci_id := l_check_valid_ci_id_rec.ci_id;
2514 x_project_id := l_check_valid_ci_id_rec.project_id;
2515 close check_valid_ci_id;
2516 end if;--if(check_valid_ci_id%notfound) then
2517 end if;-- if(p_ci_id is not null)then
2518
2519 /*check whether the logged in user has privilge to create the action or not*/
2520 /*Need to revisit function used below. It cannot be used here. We dont want to use the status controls check
2521 from here*/
2522 if(l_ci_id is not null)--checking only if ci_id was valid and not null
2523 then
2524 l_create_action_flg := pa_ci_security_pkg.check_create_action(p_ci_id => l_ci_id, p_calling_context => 'AMG');
2525 end if;
2526
2527 if(l_create_action_flg is not null and l_create_action_flg = 'F') then
2528 /*user doesnt have privilige to create the action*/
2529 pa_utils.add_message
2530 (p_app_short_name => 'PA',
2531 p_msg_name => 'PA_CI_ACT_FLS_SEC');
2532 if l_debug_mode = 'Y' then
2533 pa_debug.g_err_stage:= 'user doesnt have security to create the action';
2534 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2535 end if;
2536 l_any_err_occured_flg := 'Y';
2537 end if;
2538
2539 if( l_any_err_occured_flg is not null and l_any_err_occured_flg = 'Y' ) then
2540 raise fnd_api.g_exc_error;
2541 end if;
2542
2543 --rest the stack;
2544 if l_debug_mode = 'Y' then
2545 pa_debug.reset_curr_function;
2546 end if;
2547
2548 Exception
2549 when fnd_api.g_exc_error then
2550 x_return_status := fnd_api.g_ret_sts_error;
2551 l_msg_count := fnd_msg_pub.count_msg;
2552 if l_msg_count = 1 then
2553 pa_interface_utils_pub.get_messages
2554 (p_encoded => fnd_api.g_false,
2555 p_msg_index => 1,
2556 p_msg_count => l_msg_count ,
2557 p_msg_data => l_msg_data ,
2558 p_data => l_data,
2559 p_msg_index_out => l_msg_index_out );
2560 x_msg_data := l_data;
2561 x_msg_count := l_msg_count;
2562 else
2563 x_msg_count := l_msg_count;
2564 end if;
2565 /*initializing the out variables to null*/
2566 x_project_id := null;
2567 /* no in/out paramters to be set to their initial values here*/
2568 --rest the stack;
2569 if l_debug_mode = 'Y' then
2570 pa_debug.reset_curr_function;
2571 end if;
2572 --raise the exception
2573 raise;
2574 when others then
2575 x_return_status := fnd_api.g_ret_sts_unexp_error;
2576 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
2577 p_procedure_name => 'check_create_action_allow',
2578 p_error_text => substrb(sqlerrm,1,240));
2579 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2580 p_data => x_msg_data);
2581 /*initializing the out variables to null*/
2582 x_project_id := null;
2583 /* no in/out paramters to be set to their initial values here*/
2584 --rest the stack;
2585 if l_debug_mode = 'Y' then
2586 pa_debug.reset_curr_function;
2587 end if;
2588 --raise the exception
2589 raise;
2590 END check_create_action_allow;
2591
2592 procedure validate_assignee_id(
2593 p_assignee_id IN NUMBER
2594 ,p_project_id IN NUMBER
2595 ,p_msg_token_num IN NUMBER DEFAULT NULL
2596 ,x_assignee_id OUT NOCOPY NUMBER
2597 ,x_return_status OUT NOCOPY VARCHAR2
2598 ,x_msg_count OUT NOCOPY NUMBER
2599 ,x_msg_data OUT NOCOPY VARCHAR2
2600 )
2601 is
2602 /*cursor to validate the action assignee id*/
2603 cursor chk_act_assgn_id(p_project_id number, p_assignee_id number) is
2604 SELECT p.resource_type_id,
2605 p.resource_source_id,
2606 p.name,
2607 p.organization_id,
2608 p.organization_name,
2609 p.object_id,
2610 p.object_type,
2611 l.meaning internal,
2612 pl.meaning person_type,
2613 p.employee_number,
2614 p.party_id
2615 FROM pa_people_lov_v p,
2616 fnd_lookups l,
2617 pa_lookups pl
2618 WHERE l.lookup_type='YES_NO'
2619 AND l.lookup_code=DECODE(p.resource_type_id, 101, 'Y', 'N')
2620 AND pl.lookup_type(+) = 'PA_PERSON_TYPE'
2621 AND pl.lookup_code(+) = p.person_type
2622 and ( p.object_type IS NULL OR (p.object_type='PA_PROJECTS' AND p.object_id = p_project_id))
2623 and p.party_id = p_assignee_id;
2624
2625 chk_act_assgn_id_rec chk_act_assgn_id%rowtype;
2626 l_msg_count NUMBER := 0;
2627 l_data VARCHAR2(2000);
2628 l_msg_data VARCHAR2(2000);
2629 l_msg_index_out NUMBER;
2630 l_module_name VARCHAR2(200);
2631
2632 BEGIN
2633 -- initialize the return status to success
2634 x_return_status := fnd_api.g_ret_sts_success;
2635 x_msg_count := 0;
2636
2637 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
2638 l_module_name := 'validate_assignee_id' || g_module_name;
2639
2640 if l_debug_mode = 'Y' then
2641 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.validate_assignee_id', p_debug_mode => l_debug_mode);
2642 end if;
2643
2644 if l_debug_mode = 'Y' then
2645 pa_debug.write(l_module_name, 'start of validate_assignee_id', l_debug_level3);
2646 end if;
2647
2648 if(p_assignee_id is not null) then
2649 /*Validate the passed action assinee id exists in the system*/
2650 open chk_act_assgn_id(p_project_id, p_assignee_id);
2651 fetch chk_act_assgn_id into chk_act_assgn_id_rec;
2652 if(chk_act_assgn_id%notfound) then
2653 x_return_status := fnd_api.g_ret_sts_error;
2654
2655 if(p_msg_token_num is not null) then
2656 /*if p_msg_token_num is passed then we are raising the parameterized msg which we need in create_action flow frm amg*/
2657 pa_utils.add_message(p_app_short_name => 'PA',
2658 p_msg_name => 'PA_CI_INV_ACT_ASSGN_CODE', -- We have this similar msg
2659 p_token1 => 'NUMBER', --PA_CI_ACTION_INVALID_ASSIGNEE
2660 p_value1 => p_msg_token_num); --but it doesnt have tokens
2661 else
2662 /*if p_msg_token_num is not passed then we are raising the msg without any tokens
2663 which we need in take action flow in amg. Note the two msg names are different.*/
2664 pa_utils.add_message(p_app_short_name => 'PA',
2665 p_msg_name => 'PA_CI_INV_ACT_ASSGN_CODE_NT');
2666 end if;--if(p_msg_token_num is not null) then
2667
2668 if (l_debug_mode = 'Y') then
2669 pa_debug.g_err_stage:= 'Invalid action assingee code passed';
2670 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2671 end if;
2672 close chk_act_assgn_id;
2673 raise fnd_api.g_exc_error;
2674 else--(chk_act_assgn_id%notfound) then
2675 x_assignee_id := chk_act_assgn_id_rec.party_id;
2676 close chk_act_assgn_id;
2677 end if;--if(chk_act_assgn_id%notfound) then
2678 else--if(p_assignee_id is not null) then
2679 /*Action Assignee is Missing for this action record.*/
2680 x_return_status := fnd_api.g_ret_sts_error;
2681
2682 if(p_msg_token_num is not null) then
2683 pa_utils.add_message(p_app_short_name => 'PA',
2684 p_msg_name => 'PA_CI_MISS_ASSGN_ID',
2685 p_token1 => 'NUMBER',
2686 p_value1 => p_msg_token_num);
2687 else--if(p_msg_token_num is not null)
2688 pa_utils.add_message(p_app_short_name => 'PA',
2689 p_msg_name => 'PA_CI_MISS_ASSGN_ID_NT');
2690 end if;--if(p_msg_token_num is not null)
2691
2692 if l_debug_mode = 'Y' then
2693 pa_debug.g_err_stage:= 'Action Assignee Id is missing';
2694 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2695 end if;
2696 raise fnd_api.g_exc_error;
2697 end if;--if(p_assignee_id is not null) then
2698 --rest the stack;
2699 if l_debug_mode = 'Y' then
2700 pa_debug.reset_curr_function;
2701 end if;
2702
2703 Exception
2704 when fnd_api.g_exc_error then
2705 x_return_status := fnd_api.g_ret_sts_error;
2706 l_msg_count := fnd_msg_pub.count_msg;
2707 if l_msg_count = 1 then
2708 pa_interface_utils_pub.get_messages
2709 (p_encoded => fnd_api.g_false,
2710 p_msg_index => 1,
2711 p_msg_count => l_msg_count ,
2712 p_msg_data => l_msg_data ,
2713 p_data => l_data,
2714 p_msg_index_out => l_msg_index_out );
2715 x_msg_data := l_data;
2716 x_msg_count := l_msg_count;
2717 else
2718 x_msg_count := l_msg_count;
2719 end if;
2720 /*inititalise the out variables to null here*/
2721 x_assignee_id := null;
2722 /* no in/out paramters to be set to their initial values here*/
2723 --reset the err stack
2724 if l_debug_mode = 'Y' then
2725 pa_debug.reset_curr_function;
2726 end if;
2727
2728 when others then
2729 x_return_status := fnd_api.g_ret_sts_unexp_error;
2730 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
2731 p_procedure_name => 'validate_assignee_id',
2732 p_error_text => substrb(sqlerrm,1,240));
2733 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2734 p_data => x_msg_data);
2735 /*inititalise the out variables to null here*/
2736 x_assignee_id := null;
2737 /* no in/out paramters to be set to their initial values here*/
2738 --reset the err stack
2739 if l_debug_mode = 'Y' then
2740 pa_debug.reset_curr_function;
2741 end if;
2742 /*dont raise this exception*/
2743 END validate_assignee_id;
2744
2745 procedure validate_action_attributes(
2746 p_ci_id IN NUMBER
2747 ,p_project_id IN NUMBER
2748 ,p_action_tbl IN pa_control_api_pub.ci_actions_in_tbl_type
2749 ,x_action_tbl OUT NOCOPY pa_control_api_pub.ci_actions_in_tbl_type
2750 ,x_return_status OUT NOCOPY VARCHAR2
2751 ,x_msg_count OUT NOCOPY NUMBER
2752 ,x_msg_data OUT NOCOPY VARCHAR2
2753 )
2754 IS
2755
2756 cursor chk_act_typ_code(p_action_type_code varchar2)
2757 is select lookup_code, meaning
2758 from pa_lookups
2759 where lookup_type ='PA_CI_ACTION_TYPES'
2760 and meaning = p_action_type_code;
2761
2762 cursor check_valid_src_ci_action_id(p_action_id number) is
2763 select ci_action_id, status_code
2764 from pa_ci_actions
2765 where source_ci_action_id = p_action_id;
2766
2767
2768 cursor chk_action_status_code(p_action_status VARCHAR2)
2769 is
2770 select project_status_code
2771 from pa_project_statuses
2772 where status_type = 'CI_ACTION'
2773 and project_status_name = p_action_status;
2774
2775 cursor act_sts_allw_for_ci_sts(p_ci_id NUMBER)
2776 is
2777 select pps.project_system_status_code
2778 from pa_project_statuses pps,
2779 pa_control_items pci
2780 where pps.status_type = 'CONTROL_ITEM'
2781 and pps.project_status_code = pci.status_code
2782 and pci.ci_id = p_ci_id;
2783
2784
2785 chk_act_typ_code_rec chk_act_typ_code%rowtype;
2786 -- chk_act_assgn_id_rec chk_act_assgn_id%rowtype;
2787
2788
2789 l_msg_count NUMBER := 0;
2790 l_data VARCHAR2(2000);
2791 l_msg_data VARCHAR2(2000);
2792 l_msg_index_out NUMBER;
2793 l_module_name VARCHAR2(200);
2794 l_any_err_occured_flg VARCHAR2(1);
2795 l_return_status VARCHAR2(1);
2796
2797 l_action_status_code pa_project_statuses.project_status_code%type;
2798 l_ci_status_code pa_control_items.status_code%type;
2799 --l_src_ci_action_id pa_ci_actions.ci_action_id%type;
2800 l_action_tbl pa_control_api_pub.ci_actions_in_tbl_type;
2801 chk_valid_src_ci_action_id_rec check_valid_src_ci_action_id%rowtype;
2802
2803 BEGIN
2804 -- initialize the return status to success
2805 x_return_status := fnd_api.g_ret_sts_success;
2806 x_msg_count := 0;
2807
2808 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
2809 l_module_name := 'validate_action_attributes' || g_module_name;
2810
2811 if l_debug_mode = 'Y' then
2812 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.validate_action_attributes', p_debug_mode => l_debug_mode);
2813 end if;
2814
2815 if l_debug_mode = 'Y' then
2816 pa_debug.write(l_module_name, 'start of validate_action_attributes', l_debug_level3);
2817 end if;
2818
2819 --Setting this flag to N initially outside the loop.
2820 l_any_err_occured_flg := 'N';
2821
2822 if(p_action_tbl.count > 0) then
2823
2824 For i in 1..p_action_tbl.count
2825 loop
2826
2827 l_action_tbl(i).action_type_code := p_action_tbl(i).action_type_code;
2828 l_action_tbl(i).assignee_id := p_action_tbl(i).assignee_id;
2829 l_action_tbl(i).date_required := p_action_tbl(i).date_required;
2830 l_action_tbl(i).request_text := p_action_tbl(i).request_text;
2831 l_action_tbl(i).action_status := p_action_tbl(i).action_status;
2832 l_action_tbl(i).source_ci_action_id := p_action_tbl(i).source_ci_action_id;
2833 l_action_tbl(i).closed_date := p_action_tbl(i).closed_date;
2834 l_action_tbl(i).sign_off_requested_flag := p_action_tbl(i).sign_off_requested_flag;
2835 l_action_tbl(i).signed_off := p_action_tbl(i).signed_off;
2836 l_action_tbl(i).start_wf := p_action_tbl(i).start_wf;
2837
2838 /*Validate the action_type_code. It can be one of update or review*/
2839 if(l_action_tbl(i).action_type_code is not null) then
2840 open chk_act_typ_code(l_action_tbl(i).action_type_code);
2841 fetch chk_act_typ_code into chk_act_typ_code_rec;
2842 if(chk_act_typ_code%notfound) then
2843 pa_utils.add_message(p_app_short_name => 'PA',
2844 p_msg_name => 'PA_CI_INV_ACT_CODE',
2845 p_token1 => 'NUMBER',
2846 p_value1 => i);
2847 if l_debug_mode = 'Y' then
2848 pa_debug.g_err_stage:= 'Invalid action code';
2849 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2850 end if;
2851 close chk_act_typ_code;
2852 l_any_err_occured_flg := 'Y';
2853 else--(chk_act_typ_code%notfound) then
2854 x_action_tbl(i).action_type_code := chk_act_typ_code_rec.lookup_code;
2855 close chk_act_typ_code;
2856 end if;--if(chk_act_typ_code%notfound) then
2857 else--(l_action_tbl(i).action_type_code is not null) then
2858 /*if action_type_code is not passed then we default the action type as review action*/
2859 x_action_tbl(i).action_type_code := 'REVIEW';
2860 end if;--(l_action_tbl(i).action_type_code is not null) then
2861
2862 /*validate the action_assignee id*/
2863 validate_assignee_id(
2864 p_assignee_id => l_action_tbl(i).assignee_id
2865 ,p_project_id => p_project_id
2866 ,p_msg_token_num => i --passing this value to show tokenized messages.
2867 ,x_assignee_id => x_action_tbl(i).assignee_id
2868 ,x_return_status => l_return_status
2869 ,x_msg_count => l_msg_count
2870 ,x_msg_data => l_msg_data
2871 );
2872
2873 if(l_return_status <> fnd_api.g_ret_sts_success) then
2874 /* we are not raising the exception here*/
2875 l_any_err_occured_flg := 'Y';
2876 end if;
2877
2878
2879
2880 /*Copy the other two attributes date and request to out table*/
2881 x_action_tbl(i).date_required := l_action_tbl(i).date_required;
2882 x_action_tbl(i).request_text := l_action_tbl(i).request_text;
2883
2884 /* sign off requested flag can only be Y or N. so when it is passed Y setting it to Y else
2885 setting it to N explicitly.although the varibale has been set to default value of N.
2886 Explicit initialization is must because we are not validating this flag for a value other than Y or N*/
2887 if(l_action_tbl(i).sign_off_requested_flag is not null and l_action_tbl(i).sign_off_requested_flag = 'Y') then
2888 x_action_tbl(i).sign_off_requested_flag := l_action_tbl(i).sign_off_requested_flag;
2889 else
2890 x_action_tbl(i).sign_off_requested_flag := 'N';
2891 end if;
2892
2893 /* Validate the status code for the action. It can be either of Closed/Canceled/Open*/
2894 if(l_action_tbl(i).action_status is not null) then
2895 open chk_action_status_code(l_action_tbl(i).action_status);
2896 fetch chk_action_status_code into l_action_status_code;
2897 if(chk_action_status_code%notfound) then
2898 pa_utils.add_message(p_app_short_name => 'PA',
2899 p_msg_name => 'PA_CI_INV_ACT_STS_CODE',
2900 p_token1 => 'NUMBER',
2901 p_value1 => i);
2902 if (l_debug_mode = 'Y') then
2903 pa_debug.g_err_stage:= 'Status code for the action is invalid';
2904 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2905 end if;
2906 close chk_action_status_code;
2907 l_any_err_occured_flg := 'Y';
2908 else--if(chk_action_status_code%notfound) then
2909
2910 /* Validate that this action status code is allowed for the control item status*/
2911 /* only control items in open/ draft status can have the open actions. A control item in any other status cannot
2912 have open actions*/
2913 /*get the control item status code*/
2914 open act_sts_allw_for_ci_sts(p_ci_id);
2915 fetch act_sts_allw_for_ci_sts into l_ci_status_code;
2916 /* the cursor shd always return a record here as ci_id is valid at this place in code unless the status types
2917 heve not been set up in the system for control items*/
2918 if(act_sts_allw_for_ci_sts%notfound) then
2919 if (l_debug_mode = 'Y') then
2920 pa_debug.g_err_stage:= 'Either the ci_id is invalid or statuses for control item have not been set up';
2921 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2922 end if;
2923 close act_sts_allw_for_ci_sts;
2924 else
2925 if( (l_ci_status_code = 'CI_APPROVED' or l_ci_status_code = 'CI_CANCELED' or l_ci_status_code = 'CI_CLOSED'
2926 or l_ci_status_code = 'CI_REJECTED' or l_ci_status_code = 'CI_SUBMITTED')
2927 and
2928 (l_action_status_code = 'CI_ACTION_OPEN') ) then
2929 /*raise the msg action status not valid for the control item status*/
2930 pa_utils.add_message(p_app_short_name => 'PA',
2931 p_msg_name => 'PA_CI_MISMATCH_ACT_CI_STS',
2932 p_token1 => 'NUMBER',
2933 p_value1 => i);
2934 if (l_debug_mode = 'Y') then
2935 pa_debug.g_err_stage := 'Action status is not valid for the control item status.';
2936 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2937 end if;
2938 l_any_err_occured_flg := 'Y';
2939 else
2940 x_action_tbl(i).action_status := l_action_status_code;
2941 end if;--if( (l_ci_status_code = 'CI_APPROVED' or l_ci_status_code = 'CI_CANCELED' or l_ci_status_code
2942 close act_sts_allw_for_ci_sts;
2943 end if;--if(act_sts_allw_for_ci_sts%notfound) then
2944
2945 close chk_action_status_code;
2946 end if;--if(chk_action_status_code%notfound) then
2947 else --if(l_action_tbl(i).action_status is not null) then
2948 /*action status is not passed. Raise the error msg.*/
2949 /* we can also default the action status to open if no status is passed. need to crosscheck this*/
2950 pa_utils.add_message(p_app_short_name => 'PA',
2951 p_msg_name => 'PA_CI_MISS_ACT_CI_STS',
2952 p_token1 => 'NUMBER',
2953 p_value1 => i);
2954 if (l_debug_mode = 'Y') then
2955 pa_debug.g_err_stage := 'Action status code is missing.';
2956 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2957 end if;
2958 l_any_err_occured_flg := 'Y';
2959 end if;--if(l_action_tbl(i).action_status is not null) then
2960
2961 /* signed_off flag can only be Y or N. so when it is passed Y setting it to Y else
2962 setting it to N explicitly.although the varibale has been set to default value of N.
2963 Explicit initialization is must because we are not validating this flag for a value other than Y or N*/
2964 if(l_action_tbl(i).signed_off is not null and l_action_tbl(i).signed_off = 'Y') then
2965 x_action_tbl(i).signed_off := l_action_tbl(i).signed_off;
2966 else
2967 x_action_tbl(i).signed_off := 'N';
2968 end if;
2969
2970 /* start_wf flag can only be Y or N. so when it is passed Y setting it to Y else
2971 setting it to N explicitly.although the varibale has been set to default value of N.
2972 Explicit initialization is must because we are not validating this flag for a value other than Y or N*/
2973 if(l_action_tbl(i).start_wf is not null and l_action_tbl(i).start_wf = 'Y') then
2974 x_action_tbl(i).start_wf := l_action_tbl(i).start_wf;
2975 else
2976 x_action_tbl(i).start_wf := 'N';
2977 end if;
2978
2979 /*Validate the source_ci_action_id*/
2980 if(l_action_tbl(i).source_ci_action_id is not null) then
2981 open check_valid_src_ci_action_id(l_action_tbl(i).source_ci_action_id);
2982 fetch check_valid_src_ci_action_id into chk_valid_src_ci_action_id_rec;
2983 if(check_valid_src_ci_action_id%notfound) then
2984 pa_utils.add_message(p_app_short_name => 'PA',
2985 p_msg_name => 'PA_CI_INV_SRC_CI_ID',
2986 p_token1 => 'NUMBER',
2987 p_value1 => i);
2988 if (l_debug_mode = 'Y') then
2989 pa_debug.g_err_stage := 'source_ci_action_id is invalid';
2990 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
2991 end if;
2992 l_any_err_occured_flg := 'Y';
2993 close check_valid_src_ci_action_id;
2994 else
2995 /*here the source_ci_action_id ia valid id*/
2996 /*we need to validate that the source_ci_action_id shd be closed action always. source action cannot be open/canceled*/
2997 if (chk_valid_src_ci_action_id_rec.status_code = 'CI_ACTION_CLOSED') then
2998 x_action_tbl(i).source_ci_action_id := chk_valid_src_ci_action_id_rec.ci_action_id; --l_src_ci_action_id;
2999 close check_valid_src_ci_action_id;
3000 else
3001 /*source_ci_action is not closed. Raise error msg that the source action shd always be closed*/
3002 pa_utils.add_message(p_app_short_name => 'PA',
3003 p_msg_name => 'PA_CI_INV_SRC_CI_ID_STS',
3004 p_token1 => 'NUMBER',
3005 p_value1 => i);
3006 if (l_debug_mode = 'Y') then
3007 pa_debug.g_err_stage := 'source_ci_action_id can only be a closed action';
3008 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3009 end if;
3010 l_any_err_occured_flg := 'Y';
3011 close check_valid_src_ci_action_id;
3012 end if;
3013 end if;
3014 else --if(l_action_tbl(i).source_ci_action_id is not null) then
3015 /*if source_ci_action_id is passed*/
3016 x_action_tbl(i).source_ci_action_id := null;
3017 end if;--if(l_action_tbl(i).source_ci_action_id is not null) then
3018
3019 /*Validate the date_closed. This is a must for closed/canceled actions*/
3020 if( (l_action_status_code = 'CI_ACTION_CLOSED'or l_action_status_code = 'CI_ACTION_CANCELED') ) then
3021 if (l_action_tbl(i).closed_date is null) then
3022 pa_utils.add_message(p_app_short_name => 'PA',
3023 p_msg_name => 'PA_CI_MISS_DATE_FOR_ACT_CODE',
3024 p_token1 => 'NUMBER',
3025 p_value1 => i);
3026 if (l_debug_mode = 'Y') then
3027 pa_debug.g_err_stage := 'closed_date is missing for a closed/canceled action';
3028 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3029 end if;
3030 l_any_err_occured_flg := 'Y';
3031 else --(l_action_tbl(i).closed_date is null) then
3032 x_action_tbl(i).closed_date := l_action_tbl(i).closed_date;
3033 end if;
3034 else --if( (l_action_status_code = 'CI_ACTION_CLOSED'or l_action_status_code = 'CI_ACTION_CANCELED') ) then
3035 /*for open actions ignore this date*/
3036 x_action_tbl(i).closed_date := null;
3037 end if;
3038
3039 end loop;--For i in 1..p_action_tbl.count
3040
3041 end if;-- if(p_action_tbl.count > 0) then
3042
3043 if( l_any_err_occured_flg is not null and l_any_err_occured_flg = 'Y' ) then
3044 raise fnd_api.g_exc_error;
3045 end if;
3046 --reset the err stack
3047 if l_debug_mode = 'Y' then
3048 pa_debug.reset_curr_function;
3049 end if;
3050
3051 Exception
3052 when fnd_api.g_exc_error then
3053 x_return_status := fnd_api.g_ret_sts_error;
3054 l_msg_count := fnd_msg_pub.count_msg;
3055 if l_msg_count = 1 then
3056 pa_interface_utils_pub.get_messages
3057 (p_encoded => fnd_api.g_false,
3058 p_msg_index => 1,
3059 p_msg_count => l_msg_count ,
3060 p_msg_data => l_msg_data ,
3061 p_data => l_data,
3062 p_msg_index_out => l_msg_index_out );
3063 x_msg_data := l_data;
3064 x_msg_count := l_msg_count;
3065 else
3066 x_msg_count := l_msg_count;
3067 end if;
3068 /*Not initializing the out table of records to null as it wont be used in the calling API if exception occurs*/
3069 /* no in/out paramters to be set to their initial values here*/
3070 --reset the err stack
3071 if l_debug_mode = 'Y' then
3072 pa_debug.reset_curr_function;
3073 end if;
3074 --raise the exception
3075 raise;
3076 when others then
3077 x_return_status := fnd_api.g_ret_sts_unexp_error;
3078 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
3079 p_procedure_name => 'validate_action_attributes',
3080 p_error_text => substrb(sqlerrm,1,240));
3081 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3082 p_data => x_msg_data);
3083 /*Not initializing the out table of records to null as it wont be used in the calling API if exception occurs*/
3084 /* no in/out paramters to be set to their initial values here*/
3085 --reset the err stack
3086 if l_debug_mode = 'Y' then
3087 pa_debug.reset_curr_function;
3088 end if;
3089 --raise the exception
3090 raise;
3091 END validate_action_attributes;
3092
3093 procedure create_action(
3094 p_action_tbl IN pa_control_api_pub.ci_actions_in_tbl_type
3095 ,p_ci_id IN NUMBER := null
3096 ,x_action_tbl OUT NOCOPY pa_control_api_pub.ci_actions_out_tbl_type
3097 ,x_return_status OUT NOCOPY VARCHAR2
3098 ,x_msg_count OUT NOCOPY NUMBER
3099 ,x_msg_data OUT NOCOPY VARCHAR2
3100 )
3101 IS
3102
3103 Cursor getRecordVersionNumber(p_ci_id number)
3104 is
3105 select record_version_number
3106 from pa_control_items
3107 where ci_id = p_ci_id;
3108
3109
3110 l_msg_count NUMBER := 0;
3111 l_data VARCHAR2(2000);
3112 l_msg_data VARCHAR2(2000);
3113 l_msg_index_out NUMBER;
3114 l_module_name VARCHAR2(200);
3115 l_any_err_occured_flg VARCHAR2(1);
3116 l_action_number pa_ci_actions.ci_action_number%type;
3117 l_ci_comment_id pa_ci_comments.ci_comment_id%type;
3118 l_type_code pa_ci_comments.type_code%type;
3119 l_ci_action_id pa_ci_actions.ci_action_id%type;
3120 l_ci_record_version_number pa_control_items.record_version_number%type;
3121 l_process_name varchar(100);
3122 l_item_key pa_wf_processes.item_key%TYPE;
3123 l_num_of_actions number;
3124 l_num_open_action pa_control_items.open_action_num%type;
3125 BEGIN
3126 -- initialize the return status to success
3127 x_return_status := fnd_api.g_ret_sts_success;
3128 x_msg_count := 0;
3129
3130 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
3131 l_module_name := 'create_action' || g_module_name;
3132
3133 if l_debug_mode = 'Y' then
3134 pa_debug.set_curr_function(p_function => 'pa_control_api_pvt.create_action', p_debug_mode => l_debug_mode);
3135 end if;
3136
3137 if l_debug_mode = 'Y' then
3138 pa_debug.write(l_module_name, 'start of create_action', l_debug_level3);
3139 end if;
3140
3141
3142 --Setting this flag to N initially.
3143 l_any_err_occured_flg := 'N';
3144
3145 /*get the record version number for the control item*/
3146 OPEN getRecordVersionNumber(p_ci_id);
3147 FETCH getRecordVersionNumber into l_ci_record_version_number;
3148 CLOSE getRecordVersionNumber;
3149
3150 /* initalizing the number of open action to zero outside the loop here
3151 this number would be incremented by one each time for every open action inside the loop*/
3152 l_num_open_action := 0;
3153
3154 For i in 1..p_action_tbl.count
3155 loop
3156 /*get the action number*/
3157 if (p_ci_id IS NOT NULL) then
3158 l_action_number := PA_CI_ACTIONS_UTIL.get_next_ci_action_number(p_ci_id);
3159 end if;
3160
3161 if l_debug_mode = 'Y' then
3162 pa_debug.g_err_stage := 'calling insert row to create the action';
3163 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3164 end if;
3165
3166 /*insert row in pa_ci_actions*/
3167 pa_ci_actions_pkg.insert_row(
3168 p_ci_action_id => l_ci_action_id, -- this is out paramter
3169 p_ci_id => p_ci_id,
3170 p_ci_action_number => l_action_number,
3171 p_status_code => p_action_tbl(i).action_status,
3172 p_type_code => p_action_tbl(i).action_type_code,
3173 p_assigned_to => p_action_tbl(i).assignee_id,
3174 p_date_required => p_action_tbl(i).date_required,
3175 p_sign_off_required_flag => p_action_tbl(i).sign_off_requested_flag,
3176 p_date_closed => p_action_tbl(i).closed_date,
3177 p_sign_off_flag => p_action_tbl(i).signed_off,
3178 p_source_ci_action_id => p_action_tbl(i).source_ci_action_id,
3179 p_last_updated_by => fnd_global.user_id,
3180 p_created_by => fnd_global.user_id,
3181 p_creation_date => sysdate,
3182 p_last_update_date => sysdate,
3183 p_last_update_login => fnd_global.login_id, --this shd not be user_id.
3184 p_record_version_number => 1);
3185
3186 /* now prepare the output table to store the output values*/
3187 x_action_tbl(i).action_id := l_ci_action_id;
3188 x_action_tbl(i).action_number := l_action_number;
3189
3190 /*set the type_code for the pa_ci_comments table before inserting comment in it*/
3191 if(p_action_tbl(i).action_status = 'CI_ACTION_CLOSED' or p_action_tbl(i).action_status = 'CI_ACTION_CANCELED') then
3192 l_type_code := 'CLOSURE';
3193 elsif(p_action_tbl(i).action_status = 'CI_ACTION_OPEN') then
3194 l_type_code := 'REQUESTOR';
3195 end if;
3196
3197 if l_debug_mode = 'Y' then
3198 pa_debug.g_err_stage := 'Inserting the comment for the action.';
3199 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3200 end if;
3201
3202 /*now call the add comment api*/
3203 pa_ci_comments_pkg.insert_row(
3204 p_ci_comment_id => l_ci_comment_id,
3205 p_ci_id => p_ci_id,
3206 p_type_code => l_type_code,
3207 p_comment_text => p_action_tbl(i).request_text,
3208 p_last_updated_by => fnd_global.user_id,
3209 p_created_by => fnd_global.user_id,
3210 p_creation_date => sysdate,
3211 p_last_update_date => sysdate,
3212 p_last_update_login => fnd_global.login_id, --this shd not be user_id
3213 p_ci_action_id => l_ci_action_id);
3214
3215
3216 if l_debug_mode = 'Y' then
3217 pa_debug.g_err_stage := 'Updating number of actions for a control item';
3218 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3219 end if;
3220 /*now update the number of actions in pa_control_items_table */
3221 if(p_action_tbl(i).action_status = 'CI_ACTION_OPEN') then
3222 l_num_open_action := l_num_open_action + 1;
3223 end if;
3224
3225 /* pa_control_items_pvt.update_number_of_actions (
3226 p_ci_id => p_ci_id,
3227 p_num_of_actions => 1,
3228 p_record_version_number => l_ci_record_version_number,
3229 x_num_of_actions => l_num_of_actions,
3230 x_return_status => x_return_status,
3231 x_msg_count => x_msg_count,
3232 x_msg_data => x_msg_data);*/
3233
3234 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3235 l_any_err_occured_flg := 'Y';
3236 end if;
3237
3238 if l_debug_mode = 'Y' then
3239 pa_debug.g_err_stage := 'calling insert row to create the action';
3240 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3241 end if;
3242 /*start the workflow notification only if start_wf flag is Y*/
3243 if(p_action_tbl(i).start_wf = 'Y') then
3244 if(p_action_tbl(i).action_status = 'CI_ACTION_OPEN') then
3245 -- Depending upon Sign-off required different processes have been created in the PA Issue and Change Action Workflow
3246 if (p_action_tbl(i).sign_off_requested_flag = 'Y' )then
3247 l_process_name := 'PA_CI_ACTION_ASMT_SIGN_OFF';
3248 else
3249 l_process_name := 'PA_CI_ACTION_ASMT_NO_SIGN_OFF';
3250 end if;
3251
3252 pa_control_items_workflow.start_notification_wf
3253 ( p_item_type => 'PAWFCIAC'
3254 ,p_process_name => l_process_name
3255 ,p_ci_id => p_ci_id
3256 ,p_action_id => l_ci_action_id
3257 ,x_item_key => l_item_key
3258 ,x_return_status => x_return_status
3259 ,x_msg_count => x_msg_count
3260 ,x_msg_data => x_msg_data );
3261
3262 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3263 l_any_err_occured_flg := 'Y';
3264 end if;
3265
3266 elsif( p_action_tbl(i).action_status = 'CI_ACTION_CLOSED') then
3267 /*need to check if we should send the notification while creating closed actions*/
3268 /*Most likely we shd not be sending this. Can be commented later*/
3269 pa_control_items_workflow.start_notification_wf
3270 ( p_item_type => 'PAWFCIAC'
3271 ,p_process_name => 'PA_CI_ACTION_CLOSE_FYI'
3272 ,p_ci_id => p_ci_id
3273 ,p_action_id => l_ci_action_id
3274 ,x_item_key => l_item_key
3275 ,x_return_status => x_return_status
3276 ,x_msg_count => x_msg_count
3277 ,x_msg_data => x_msg_data );
3278 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3279 l_any_err_occured_flg := 'Y';
3280 end if;
3281 end if;-- if(p_action_tbl(i).action_status = 'CI_ACTION_OPEN') then
3282 end if;--(p_action_tbl(i).start_wf = 'Y')
3283
3284 end loop;-- For i in 1..p_action_tbl.count
3285
3286 --if there were any open actions update the no of open action in pa_control_items
3287 --this has to be done outside the loop only once for all the open actions in table.
3288 if(l_num_open_action is not null and l_num_open_action > 0 ) then
3289 pa_control_items_pvt.update_number_of_actions (
3290 p_ci_id => p_ci_id,
3291 p_num_of_actions => l_num_open_action,
3292 p_record_version_number => l_ci_record_version_number,
3293 x_num_of_actions => l_num_of_actions,
3294 x_return_status => x_return_status,
3295 x_msg_count => x_msg_count,
3296 x_msg_data => x_msg_data);
3297 end if;
3298
3299 if( l_any_err_occured_flg is not null and l_any_err_occured_flg = 'Y' ) then
3300 raise fnd_api.g_exc_unexpected_error;
3301 end if;
3302
3303 --reset the err stack
3304 if l_debug_mode = 'Y' then
3305 pa_debug.reset_curr_function;
3306 end if;
3307
3308 Exception
3309 When fnd_api.g_exc_unexpected_error then
3310
3311 x_return_status := fnd_api.g_ret_sts_unexp_error;
3312 --do a rollback;
3313 FND_MSG_PUB.Count_And_Get(
3314 p_count => x_msg_count ,
3315 p_data => x_msg_data );
3316
3317 /*Not initializing the out table of records to null as it wont be used in the calling API if exception occurs*/
3318 /* no in/out paramters to be set to their initial values here*/
3319 --reset the err stack
3320 if l_debug_mode = 'Y' then
3321 pa_debug.reset_curr_function;
3322 end if;
3323 --raise the exception;
3324 raise;
3325 when others then
3326 x_return_status := fnd_api.g_ret_sts_unexp_error;
3327 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
3328 p_procedure_name => 'create_action',
3329 p_error_text => substrb(sqlerrm,1,240));
3330 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3331 p_data => x_msg_data);
3332 /*Not initializing the out table of records to null as it wont be used in the calling API if exception occurs*/
3333 /* no in/out paramters to be set to their initial values here*/
3334 --reset the err stack
3335 if l_debug_mode = 'Y' then
3336 pa_debug.reset_curr_function;
3337 end if;
3338 --raise the exception
3339 raise;
3340
3341 END create_action;
3342
3343 procedure validate_priv_and_action(
3344 p_ci_id IN NUMBER
3345 ,p_action_id IN NUMBER
3346 ,p_action_number IN NUMBER
3347 ,x_action_id OUT NOCOPY NUMBER
3348 ,x_assignee_id OUT NOCOPY NUMBER
3349 ,x_project_id OUT NOCOPY NUMBER
3350 ,x_return_status OUT NOCOPY VARCHAR2
3351 ,x_msg_count OUT NOCOPY NUMBER
3352 ,x_msg_data OUT NOCOPY VARCHAR2
3353 )
3354 is
3355
3356 cursor get_ci_action_id(p_ci_id number, p_action_number number)
3357 is
3358 select pca.ci_action_id, pca.assigned_to, pci.project_id
3359 from pa_ci_actions pca,
3360 pa_control_items pci
3361 where pca.ci_id = p_ci_id
3362 and pca.ci_action_number = p_action_number
3363 and pci.ci_id = p_ci_id;
3364
3365 cursor validate_ci_action_id(p_action_id number)
3366 is
3367 select pca.ci_action_id, pca.assigned_to, pci.project_id
3368 from pa_ci_actions pca,
3369 pa_control_items pci
3370 where pca.ci_action_id = p_action_id
3371 and pci.ci_id = pca.ci_id;
3372
3373 l_validate_ci_action_id_rec validate_ci_action_id%rowtype;
3374 l_get_ci_action_id_rec get_ci_action_id%rowtype;
3375
3376 l_msg_count NUMBER := 0;
3377 l_data VARCHAR2(2000);
3378 l_msg_data VARCHAR2(2000);
3379 l_msg_index_out NUMBER;
3380 l_module_name VARCHAR2(200);
3381 l_any_err_occured_flg VARCHAR2(1);
3382
3383
3384 begin
3385 -- initialize the return status to success
3386 x_return_status := fnd_api.g_ret_sts_success;
3387 x_msg_count := 0;
3388
3389 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
3390 l_module_name := 'validate_priv_and_action' || g_module_name;
3391
3392 if l_debug_mode = 'Y' then
3393 pa_debug.set_curr_function(p_function => 'pa_control_api_pub.validate_priv_and_action', p_debug_mode => l_debug_mode);
3394 end if;
3395
3396 if l_debug_mode = 'Y' then
3397 pa_debug.write(l_module_name, 'start of validate_priv_and_action', l_debug_level3);
3398 end if;
3399
3400 --Setting this flag to N initially.
3401 l_any_err_occured_flg := 'N';
3402
3403 /*check if the action_id, action_number, ci_id all three are missing*/
3404 if( (p_ci_id is null or p_ci_id = G_PA_MISS_NUM) AND
3405 (p_action_id is null or p_action_id = G_PA_MISS_NUM) AND
3406 (p_action_number is null or p_action_number = G_PA_MISS_NUM)
3407 ) then
3408 pa_utils.add_message(p_app_short_name => 'PA',
3409 p_msg_name => 'PA_CI_MISS_CIID_ACTID_ACTNUM');
3410 if (l_debug_mode = 'Y') then
3411 pa_debug.g_err_stage := 'all three action_id, ci_id, action_number cannot be missing';
3412 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3413 end if;
3414 /*raise the exception*/
3415 raise fnd_api.g_exc_error;
3416 end if;-- if( (p_ci_id is null or p_ci_id is = G_PA_MISS_NUM) AND
3417
3418 if (p_action_id is null or p_action_id = G_PA_MISS_NUM) then
3419
3420 /*here action_id is missing. So check if we can derive action_id from ci_id and action_number.*/
3421 if( (p_ci_id is not null and p_ci_id <> G_PA_MISS_NUM) AND
3422 (p_action_number is not null and p_action_number <> G_PA_MISS_NUM)
3423 ) then
3424 /*derive the ci_action_id*/
3425 open get_ci_action_id(p_ci_id, p_action_number);
3426 fetch get_ci_action_id into l_get_ci_action_id_rec;
3427 if(get_ci_action_id%notfound) then
3428 /*ci_id and action_number combination is invalid.raise the error message.*/
3429 pa_utils.add_message(p_app_short_name => 'PA',
3430 p_msg_name => 'PA_CI_INV_CIID_ACTNUM');
3431 if (l_debug_mode = 'Y') then
3432 pa_debug.g_err_stage := 'there is no action for passed action_number and ci_id';
3433 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3434 end if;
3435 close get_ci_action_id;
3436 raise fnd_api.g_exc_error;
3437 else--if(get_ci_action_id%notfound) then
3438 x_assignee_id := l_get_ci_action_id_rec.assigned_to;
3439 x_action_id := l_get_ci_action_id_rec.ci_action_id;
3440 x_project_id := l_get_ci_action_id_rec.project_id;
3441 close get_ci_action_id;
3442 end if; --if(get_ci_action_id%notfound) then
3443 else--if( (p_ci_id is not null and p_ci_id <> G_PA_MISS_NUM) AND
3444 /*one or both p_ci_id, p_action_number is missing here.*/
3445 pa_utils.add_message(p_app_short_name => 'PA',
3446 p_msg_name => 'PA_CI_INV_CIID_ACTNUM');
3447 if (l_debug_mode = 'Y') then
3448 pa_debug.g_err_stage := 'one or both ci_id or action_number is missing';
3449 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3450 end if;
3451 raise fnd_api.g_exc_error;
3452 end if;--if( (p_ci_id is not null and p_ci_id <> G_PA_MISS_NUM) AND
3453
3454 else --if (p_action_id is null or p_action_id is G_PA_MISS_NUM) then
3455 /*user has passed a value for action_id. Validate this value here.*/
3456 open validate_ci_action_id(p_action_id);
3457 fetch validate_ci_action_id into l_validate_ci_action_id_rec;
3458 if(validate_ci_action_id%notfound) then
3459 /*incorrect p_action_id is passed. there is no record for this action id*/
3460 pa_utils.add_message(p_app_short_name => 'PA',
3461 p_msg_name => 'PA_CI_INV_ACT_ID');
3462 if (l_debug_mode = 'Y') then
3463 pa_debug.g_err_stage := 'invalid action_id passed';
3464 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3465 end if;
3466 close validate_ci_action_id;
3467 raise fnd_api.g_exc_error;
3468 else--if(validate_ci_action_id%notfound) then
3469 x_assignee_id := l_validate_ci_action_id_rec.assigned_to;
3470 x_action_id := l_validate_ci_action_id_rec.ci_action_id;
3471 x_project_id := l_validate_ci_action_id_rec.project_id;
3472 close validate_ci_action_id;
3473 end if; --if(validate_ci_action_id%notfound) then
3474
3475 end if; -- if (p_action_id is null or p_action_id is G_PA_MISS_NUM)
3476
3477 --reset the error stack;
3478 if l_debug_mode = 'Y' then
3479 pa_debug.reset_curr_function;
3480 end if;
3481
3482 --do the exception handling;
3483 exception
3484 when fnd_api.g_exc_error then
3485 x_return_status := fnd_api.g_ret_sts_error;
3486 l_msg_count := fnd_msg_pub.count_msg;
3487 if l_msg_count = 1 then
3488 pa_interface_utils_pub.get_messages
3489 (p_encoded => fnd_api.g_false,
3490 p_msg_index => 1,
3491 p_msg_count => l_msg_count ,
3492 p_msg_data => l_msg_data ,
3493 p_data => l_data,
3494 p_msg_index_out => l_msg_index_out );
3495 x_msg_data := l_data;
3496 x_msg_count := l_msg_count;
3497 else
3498 x_msg_count := l_msg_count;
3499 end if;
3500
3501 /*Initialize the out variables back to null*/
3502 x_action_id := null;
3503 x_assignee_id := null;
3504 x_project_id := null;
3505 --no in out parameters to set to their initial values.
3506
3507 --reset the error stack;
3508 if l_debug_mode = 'Y' then
3509 pa_debug.reset_curr_function;
3510 end if;
3511 --raise the exception
3512 raise;
3513 when others then
3514 x_return_status := fnd_api.g_ret_sts_unexp_error;
3515 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_control_api_pvt',
3516 p_procedure_name => 'validate_priv_and_action',
3517 p_error_text => substrb(sqlerrm,1,240));
3518 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3519 p_data => x_msg_data);
3520
3521 /*Initialize the out variables back to null*/
3522 x_action_id := null;
3523 x_assignee_id := null;
3524 x_project_id := null;
3525 --no inout parameters to set to their initial values.
3526
3527 --reset the error stack;
3528 if l_debug_mode = 'Y' then
3529 pa_debug.reset_curr_function;
3530 end if;
3531 --raise the exception
3532 raise;
3533
3534 end validate_priv_and_action;
3535
3536
3537
3538 Procedure Delete_CI (
3539 p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
3540 , p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
3541 , p_Api_Version_Number IN NUMBER
3542 , p_Ci_Id IN NUMBER
3543 , x_Return_Status OUT NOCOPY VARCHAR2
3544 , x_Msg_Count OUT NOCOPY NUMBER
3545 , x_Msg_Data OUT NOCOPY VARCHAR2
3546 )
3547 IS
3548 -- Local Variables.
3549 l_StatusCode VARCHAR2(30);
3550 l_ProjectId NUMBER(15);
3551 l_CiTypeClassCode VARCHAR2(30);
3552 l_RecordVersionNumber NUMBER(15);
3553
3554 l_ViewAccess VARCHAR2(1);
3555 l_DeleteAllowed VARCHAR2(1);
3556
3557 l_module_name VARCHAR2(200);
3558 l_Msg_Count NUMBER := 0;
3559 l_Data VARCHAR2(2000);
3560 l_Msg_Data VARCHAR2(2000);
3561 l_Msg_Index_Out NUMBER;
3562 l_CiId PA_CONTROL_ITEMS.Ci_Id%TYPE;
3563 -- End: Local Variables.
3564 BEGIN
3565 l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.resp_id, 275, null, null), 'N');
3566 l_module_name := 'Delete_CI' || g_module_name;
3567
3568 if l_debug_mode = 'Y' then
3569 pa_debug.set_curr_function(p_function => 'pa_control_api_pub.Delete_CI', p_debug_mode => l_debug_mode);
3570 end if;
3571
3572 if l_debug_mode = 'Y' then
3573 pa_debug.write(l_module_name, 'start of Delete_CI', l_debug_level3);
3574 end if;
3575 -- Initialize the Error Stack.
3576 --PA_DEBUG.Init_Err_Stack ('PA_CONTROL_API_PVT.Delete_CI');
3577
3578 -- Initialize the Return Status to Success.
3579 x_Return_Status := FND_API.g_Ret_Sts_Success;
3580 x_Msg_Count := 0;
3581
3582 -- Clear the Global PL/SQL Message table.
3583 IF (FND_API.To_Boolean (p_Init_Msg_List)) THEN
3584 FND_MSG_PUB.Initialize;
3585 END IF;
3586
3587 -- If the Ci_Id that is passed in is NULL then report
3588 -- Error.
3589 IF (p_Ci_Id IS NULL) THEN
3590 -- Add message to the Error Stack that Ci_Id is NULL.
3591 PA_UTILS.Add_Message (
3592 p_App_Short_Name => 'PA'
3593 , p_Msg_Name => 'PA_CI_MISS_CI_ID'
3594 );
3595 if (l_debug_mode = 'Y') then
3596 pa_debug.g_err_stage := 'CI_ID is not passed';
3597 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3598 end if;
3599 -- Raise the Invalid Argument exception.
3600 RAISE FND_API.G_EXC_ERROR;
3601 END IF;
3602
3603 -- If the Ci_Id that is passed in does not exist then
3604 -- report Error.
3605 OPEN Check_Valid_CI (p_Ci_Id);
3606 FETCH Check_Valid_CI INTO l_CiId;
3607 IF (Check_Valid_CI%NOTFOUND) THEN
3608 -- Close the Cursor.
3609 CLOSE Check_Valid_CI;
3610
3611 -- Add message to the Error Stack that this Ci_Id is Invalid.
3612 PA_UTILS.Add_Message (
3613 p_App_Short_Name => 'PA'
3614 , p_Msg_Name => 'PA_CI_INV_CI_ID'
3615 );
3616 if (l_debug_mode = 'Y') then
3617 pa_debug.g_err_stage := 'invalid ci_id passed';
3618 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3619 end if;
3620 -- Raise the Invalid Argument exception.
3621 RAISE FND_API.G_EXC_ERROR;
3622 END IF;
3623 CLOSE Check_Valid_CI;
3624
3625 -- Open Cursor Get_CI_Data and Fetch the data into our local variables.
3626 OPEN Get_CI_Data (p_Ci_Id);
3627 FETCH Get_CI_Data INTO l_ProjectId, l_StatusCode, l_CiTypeClassCode, l_RecordVersionNumber;
3628
3629 -- If NO_DATA_FOUND then report Error.
3630 IF (Get_CI_Data%NOTFOUND) THEN
3631 -- Code to Report Error.
3632 CLOSE Get_CI_Data;
3633 if (l_debug_mode = 'Y') then
3634 pa_debug.g_err_stage := 'No data found in Get_CI_Data';
3635 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3636 end if;
3637 RAISE FND_API.G_EXC_ERROR;
3638 END IF;
3639 CLOSE Get_CI_Data;
3640
3641 -- Check whether Workflow is running on this Control Item or not.
3642 OPEN Check_Workflow_On_CI (p_Ci_Id);
3643 FETCH Check_Workflow_On_CI INTO l_CiId;
3644
3645 -- If Workflow is not running on this Control Item then proceed,
3646 -- else report Error.
3647 IF (Check_Workflow_On_CI%NOTFOUND) THEN
3648 CLOSE Check_Workflow_On_CI;
3649 -- If the User has View Access to this Control Item and
3650 -- delete is allowed on this Control Item then call the
3651 -- API to delete it.
3652 l_ViewAccess := PA_CI_SECURITY_PKG.Check_View_Access (p_Ci_Id, l_ProjectId, l_StatusCode, l_CiTypeClassCode);
3653 l_DeleteAllowed := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed ('CONTROL_ITEM', l_StatusCode, 'CONTROL_ITEM_ALLOW_DELETE', p_Ci_Id);
3654 IF (l_ViewAccess = 'T' AND l_DeleteAllowed = 'Y') THEN
3655 if (l_debug_mode = 'Y') then
3656 pa_debug.g_err_stage := 'Before Calling PA_CONTROL_ITEMS_PUB.Delete_Control_Item';
3657 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3658 end if;
3659 PA_CONTROL_ITEMS_PUB.Delete_Control_Item (
3660 p_Api_Version => p_Api_Version_Number
3661 , p_Init_Msg_List => 'F'
3662 , p_Commit => p_Commit
3663 , p_Validate_Only => 'F'
3664 , p_Ci_Id => p_Ci_Id
3665 , p_Record_Version_Number => l_RecordVersionNumber
3666 , x_Return_Status => x_Return_Status
3667 , x_Msg_Count => x_Msg_Count
3668 , x_Msg_Data => x_Msg_Data
3669 );
3670 ELSE
3671 -- Check if View Access was denied or not.
3672 IF (l_ViewAccess <> 'T') THEN
3673 -- Add message to the Error Stack that the user does not
3674 -- have the privilege to delete this Control Item.
3675 PA_UTILS.Add_Message (
3676 p_App_Short_Name => 'PA'
3677 , p_Msg_Name => 'PA_CI_NO_ALLOW_DELETE'
3678 );
3679 if (l_debug_mode = 'Y') then
3680 pa_debug.g_err_stage := 'User does not have the privilege to delete this Control Item';
3681 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3682 end if;
3683 END IF;
3684
3685 -- Check if delete was denied by Status Control or not.
3686 IF (l_DeleteAllowed <> 'Y') THEN
3687 -- Add message to the Error Stack that this Control Item
3688 -- cannot be deleted in its present status.
3689 PA_UTILS.Add_Message (
3690 p_App_Short_Name => 'PA'
3691 , p_Msg_Name => 'PA_CI_DELETE_NOT_ALLOWED'
3692 );
3693 if (l_debug_mode = 'Y') then
3694 pa_debug.g_err_stage := 'This control item cannot be deleted in its present status';
3695 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3696 end if;
3697 END IF;
3698
3699 -- Raise the Invalid Argument exception.
3700 RAISE FND_API.G_EXC_ERROR;
3701 END IF;
3702 ELSE
3703 -- Close the Cursor.
3704 CLOSE Check_Workflow_On_CI;
3705
3706 -- Add message to the Error Stack that this Ci_Id has Workflow
3707 -- attached.
3708 PA_UTILS.Add_Message (
3709 p_App_Short_Name => 'PA'
3710 , p_Msg_Name => 'PA_CI_APPROVAL_WORKFLOW'
3711 );
3712 if (l_debug_mode = 'Y') then
3713 pa_debug.g_err_stage := 'CI_ID has workflow Attached';
3714 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3715 end if;
3716 -- Raise the Invalid Argument exception.
3717 RAISE FND_API.G_EXC_ERROR;
3718 END IF;
3719
3720 --reset the error stack;
3721 if l_debug_mode = 'Y' then
3722 pa_debug.reset_curr_function;
3723 end if;
3724 -- If any exception then catch it.
3725 EXCEPTION
3726 WHEN FND_API.G_EXC_ERROR THEN
3727 -- Set the Return Status as Error.
3728 x_Return_Status := FND_API.g_Ret_Sts_Error;
3729 -- Get the Message Count.
3730 l_Msg_Count := FND_MSG_PUB.Count_Msg;
3731
3732 IF (l_Msg_Count = 1) THEN
3733 PA_INTERFACE_UTILS_PUB.Get_Messages (
3734 p_Encoded => FND_API.g_False
3735 , p_Msg_Index => 1
3736 , p_Msg_Count => l_Msg_Count
3737 , p_Msg_Data => l_Msg_Data
3738 , p_Data => l_Data
3739 , p_Msg_Index_Out => l_Msg_Index_Out
3740 );
3741 x_Msg_Data := l_Data;
3742 x_Msg_Count := l_Msg_Count;
3743 ELSE
3744 x_Msg_Count := l_Msg_Count;
3745 END IF;
3746
3747 --reset the error stack;
3748 if l_debug_mode = 'Y' then
3749 pa_debug.reset_curr_function;
3750 end if;
3751
3752 -- Raise the Exception.
3753 RAISE;
3754
3755 WHEN OTHERS THEN
3756 -- Set the Return Status as Error.
3757 x_Return_Status := FND_API.g_Ret_Sts_Unexp_Error;
3758
3759 -- Add the message that is reported in SQL Error.
3760 FND_MSG_PUB.Add_Exc_Msg (
3761 p_Pkg_Name => 'PA_CONTROL_API_PVT',
3762 p_Procedure_Name => 'Delete_CI',
3763 p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
3764 );
3765
3766 FND_MSG_PUB.Count_And_Get (
3767 p_Count => x_Msg_Count,
3768 p_Data => x_Msg_Data
3769 );
3770
3771 --reset the error stack;
3772 if l_debug_mode = 'Y' then
3773 pa_debug.reset_curr_function;
3774 end if;
3775
3776 -- Raise the Exception.
3777 RAISE;
3778 END Delete_CI;
3779
3780
3781 END PA_CONTROL_API_PVT;
[Home] [Help]