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