[Home] [Help]
PACKAGE BODY: APPS.CS_CHG_AUTO_SUB_CON_PKG
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_Chg_Auto_Sub_CON_PKG';
1 PACKAGE BODY CS_Chg_Auto_Sub_CON_PKG as
2 /* $Header: csxvasub.pls 120.9.12010000.4 2010/01/11 10:59:21 sshilpam ship $ */
3 /*********** Global Variables ********************************/
5 /***************************************************************/
6 -- *******************************************************
7 -- Start of Comments
8 -- *******************************************************
9 -- Procedure Name: Main_Procedure
10 -- Type : Private
11 -- Purpose : This is the main procedure of the concurrent program.
12 -- Pre-Req :
13 -- Parameters:
14 -- OUT :
15 -- Errbuf OUT VARCHAR2 This is for returning error messages
16 -- Standard out parameter for a concurrent program.
17 -- Retcode OUT NUMBER This is an out parameter to return error
18 -- code to the concurrent program.
19 -- Standard out parameter for a concurrent program.
23 RETCODE OUT NOCOPY NUMBER) IS
20 -- retcode = 0 success, 1 = warning, 2=error.
21 --
22 PROCEDURE Main_Procedure(ERRBUF OUT NOCOPY VARCHAR2,
24
25 lx_msg_data VARCHAR2(2000);
26 lx_msg_count NUMBER;
27 lx_return_status VARCHAR2(1);
28 conc_status BOOLEAN;
29
30 BEGIN
31
32 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'***************************************************************************************************');
33 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Starting Concurrent Program for autosubmitting Charge Lines: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
34 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'***************************************************************************************************');
35
36 --added for r12 to populate temporary tables
37 MO_GLOBAL.INIT('CS_CHARGES') ;
38
39 Auto_Submit_Chg_Lines(p_api_version => 1.0,
40 p_init_msg_list => fnd_api.g_false,
41 p_commit => fnd_api.g_false,
42 x_return_status => lx_return_status,
43 x_msg_count => lx_msg_count,
44 x_msg_data => lx_msg_data);
45
46 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS)
47 THEN
48 conc_status := fnd_concurrent.set_completion_status('WARNING','Warning');
49 END IF;
50
51 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'*****************************************************************************************************');
52 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Completed Concurrent Program for autosubmitting Charge Lines: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
53 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'*****************************************************************************************************');
54
55 END Main_Procedure;
56
57 /*--
58 -- *******************************************************
59 -- Start of Comments
60 -- *******************************************************
61 -- Procedure Name: Auto_Submit_Chg_Lines
62 -- Type : Private
63 -- Purpose : This procedure is for identifying lines for Autosubmission.
64 -- It is intended for use by the owning module only.
65 -- Pre-Req :
66 -- Parameters:
67 -- p_api_version IN NUMBER Required
68 -- p_init_msg_list IN VARCHAR2 Optional
69 -- p_commit IN VARCHAR2 Optional
70 -- x_return_status OUT VARCHAR2
71 -- x_msg_data OUT VARCHAR2
72 --
73 == Modification History:
74 ==
75 == Date Name Desc
76 == ---------- --------- ---------------------------------------------
77 == 05-May-2008 BKANIMOZ Bug Fix for 6995001.Modified the Where Clause
78 == l_auto_submit_mode = 'WHEN_ALL_TASKS_FINAL'
79 ==
80 == 04-May-2009 GASANKAR Bug Fix 7692111 : Added the condition AND ced.order_line_id IS NULL
81 == in autosubmit_cv cursors.
82 ========================================================================*/
83 PROCEDURE Auto_Submit_Chg_Lines(
84 p_api_version IN NUMBER,
85 p_init_msg_list IN VARCHAR2,
86 p_commit IN VARCHAR2,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2) IS
90
91 -- Created a dummy cursor so that can create strong ref cursors instead of weak.
92 -- This cursor will not be opened in the api.
93 --
94 CURSOR AutosubmitTyp IS
95 SELECT inc.incident_id,
96 inc.incident_number,
97 trunc(inc.incident_date) incident_date,
98 inc.incident_type_id,
99 edt.estimate_detail_id,
100 hzp.party_name,
101 edt.bill_to_party_id,
102 edt.currency_code,
103 edt.list_price,
104 edt.quantity_required,
105 edt.selling_price,
106 nvl(edt.contract_discount_amount,0) contract_discount_amount,
107 edt.after_warranty_cost
108 FROM cs_estimate_details edt,
109 cs_incidents_all_b inc,
110 hz_parties hzp
111 WHERE edt.incident_id = inc.incident_id
112 AND edt.bill_to_party_id = hzp.party_id;
113
114
115 TYPE AutosubmitCurTyp IS REF CURSOR RETURN AutosubmitTyp%ROWTYPE;
116 autosubmit_cv AutosubmitCurTyp;
117
118 TYPE t_auto_submit_lines_tab IS TABLE OF AutosubmitTyp%ROWTYPE
119 INDEX BY BINARY_INTEGER;
120
121 AutosubmitTAB t_auto_submit_lines_tab;
122 --
123 -- Charge Lines Cursor for Total Service Requests and Incident_Type.
124 CURSOR Cs_Chg_Sr_Total(p_incident_id NUMBER) IS
125 -- Total for a Service request,Estimates,Actuals and Incident_Type.
126 SELECT ced.incident_id,
127 ciab.incident_number,
128 ciab.incident_type_id,
129 cit.name incident_type,
130 nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
131 ciab.creation_date,
132 ced.currency_code,
133 sum(ced.after_warranty_cost) Total_Charges
134 FROM cs_incidents_all_b ciab,
135 cs_incident_types cit,
136 cs_estimate_details ced
137 WHERE ciab.incident_id = ced.incident_id
138 AND ciab.incident_type_id = cit.incident_type_id
139 AND ced.charge_line_type IN ('ACTUAL','IN_PROGRESS')
140 AND ced.incident_id = p_incident_id
144 INDEX BY BINARY_INTEGER;
141 GROUP BY ced.currency_code,ced.incident_id,ciab.incident_number,ciab.incident_date,ciab.creation_date,ciab.incident_type_id,cit.name;
142
143 TYPE t_chg_sr_tot_tab IS TABLE OF Cs_Chg_sr_Total%rowtype
145
146 ChgSrTotTAB t_chg_sr_tot_tab;
147
148 -- Charge Lines cursor for Estimate and Actual Totals.
149 --
150 CURSOR Cs_Chg_Est_Act_Tot(p_incident_id NUMBER) IS
151 SELECT sum(decode(edt.charge_line_type,'ESTIMATE',edt.after_warranty_cost, NULL)) Estimates,
152 sum(decode(edt.charge_line_type,'ESTIMATE', NULL, edt.after_warranty_cost)) Actuals,
153 edt.currency_code,
154 inc.incident_number,
155 inc.incident_date,
156 inc.incident_id
157 FROM cs_estimate_details edt,
158 cs_incidents_all_b inc
159 WHERE edt.incident_id = p_incident_id
160 AND inc.incident_id = edt.incident_id
161 GROUP BY currency_code,inc.incident_id,inc.incident_number,inc.incident_date;
162
163 TYPE t_chg_est_Act_tot_tab IS TABLE OF Cs_Chg_Est_Act_Tot%rowtype
164 INDEX BY BINARY_INTEGER;
165
166 ChgEstActTotTAB t_chg_est_Act_tot_tab;
167 --
168 --
169 CURSOR cs_chg_restriction_rules IS
170 SELECT restriction_id,
171 restriction_type,
172 condition,
173 value_object_id,
174 value_amount,
175 currency_code,
176 trunc(start_date_active) start_date_active,
177 trunc(end_date_active) end_date_active
178 FROM cs_chg_sub_restrictions
179 ORDER BY restriction_type;
180
181 TYPE t_restriction_rules_tab IS TABLE OF cs_chg_restriction_rules%rowtype
182 INDEX BY BINARY_INTEGER;
183
184 RestrulesTAB t_restriction_rules_tab;
185 --
186 -- Added for bug:3475786
187 -- Cursor for deriving no_charge_flag
188 CURSOR cs_charge_flags(p_estimate_detail_id number) IS
189 SELECT nvl(edt.no_charge_flag,'N') chg_no_charge_flag,
190 nvl(tt.no_charge_flag,'N') txn_no_charge_flag
191 FROM cs_estimate_details edt,
192 cs_transaction_types tt
193 WHERE edt.estimate_detail_id = p_estimate_detail_id
194 AND tt.transaction_type_id = edt.transaction_type_id;
195 --
196 l_chg_no_charge_flag VARCHAR2(1);
197 l_txn_no_charge_flag VARCHAR2(1);
198 --
199 -- Define Local Variables
200 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Submit_Chg_Lines' ;
201 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
202 l_api_version CONSTANT NUMBER := 1.0 ;
203 --
204 --
205 i NUMBER := 0;
206 j NUMBER := 0;
207 k NUMBER := 0;
208 L NUMBER := 0;
209 N NUMBER := 0;
210 A NUMBER := 0;
211 l_sr_restriction VARCHAR2(100);
212 l_chg_line_restriction VARCHAR2(100);
213 l_incident_id NUMBER := -999;
214 l_auto_submit_mode VARCHAR2(30);
215 l_restriction_qualify_flag VARCHAR2(1) := 'N';
216 l_line_restriction_flag VARCHAR2(1) := 'N';
217 l_last_rec_flag VARCHAR2(1) := 'N';
218 last_rec NUMBER := -999;
219 --
220 rest_count NUMBER;
221 --
222 l_actual VARCHAR2(30);
223 l_Actual_Percent NUMBER;
224 l_estimate VARCHAR2(30);
225 l_currency_code VARCHAR2(30) := NULL;
226 --
227 --
228 l_msg_index_out NUMBER;
229 --
230 --
231 /*** Variables for logging messages ****/
232 l_rest1 VARCHAR2(250);
233 l_rest2 VARCHAR2(250);
234 l_rest3 VARCHAR2(250);
235
236
237 --new enh for simplex
238 l_check_debrief_status VARCHAR2(1) := 'N';
239 l_found VARCHAR2(1) := 'N';
240
241 BEGIN
242
243 -- Initialize API return status to success
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 -- Standard call to check for call compatibility
247 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END IF;
250
251 -- Initialize message list if p_init_msg_list is set to TRUE
252 IF FND_API.To_Boolean(p_init_msg_list) THEN
253 FND_MSG_PUB.Initialize;
254 END IF;
255
256 l_auto_submit_mode := fnd_profile.value('CS_CHG_AUTO_SUBMIT_MODE');
257 l_check_debrief_status := fnd_profile.value('CS_CHG_CHECK_DEBRIEF_STATUS'); --new enh
258
259 IF l_check_debrief_status IS NULL THEN
260 l_check_debrief_status := 'N';
261 END IF;
262
263
264
265 -- Validate autosubmit mode profile.
266 -- Auto_Submit_Mode can contain one of the following values:
267 -- 'AS_AVAILABLE','WHEN_ALL_TASKS_FINAL', 'WHEN_SERVICE_REQUEST_FINAL'.
268 BEGIN
269 IF l_auto_submit_mode IS NULL THEN
270 FND_MSG_PUB.Initialize;
271 FND_MESSAGE.Set_Name('CS','CS_CHG_DEFINE_PROFILE_OPTION');
272 FND_MESSAGE.Set_Token('PROFILE_OPTION','CS_CHG_AUTO_SUBMIT_MODE');
273 FND_MSG_PUB.Add;
274 -- x_return_status := FND_API.G_RET_STS_ERROR;
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 EXCEPTION
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280 FND_MSG_PUB.get(p_encoded => 'F',
281 p_data=>x_msg_data,
282 p_msg_index_out=> l_msg_index_out);
283
284 -- Recording exceptions in the log file.
285 FND_FILE.put_line(FND_FILE.LOG,x_msg_data);
286
287 END;
288
289
293 /* Open cursor variable. */
290 /* Querying the right select statement into the cursor variable */
291 IF l_auto_submit_mode = 'AS_AVAILABLE' THEN
292 IF NOT autosubmit_cv%ISOPEN THEN
294 OPEN autosubmit_cv FOR SELECT ciab.incident_id,
295 ciab.incident_number,
296 nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
297 ciab.incident_type_id,
298 ced.estimate_detail_id,
299 hzp.party_name,
300 ced.bill_to_party_id,
301 ced.currency_code,
302 ced.list_price,
303 ced.quantity_required,
304 ced.selling_price,
305 nvl(ced.contract_discount_amount,0) contract_discount_amount,
306 ced.after_warranty_cost
307 FROM cs_incidents_all_b ciab,
308 cs_estimate_details ced,
309 hz_parties hzp
310 WHERE ciab.incident_id = ced.incident_id
311 AND ced.bill_to_party_id = hzp.party_id
312 AND ced.line_submitted = 'N'
313 AND ced.order_line_id IS NULL --bug 7692111
314 AND ced.charge_line_type = 'ACTUAL'
315 AND ced.source_code = 'SD'
316 AND ced.original_source_code = 'SR'
317 AND ced.interface_to_oe_flag = 'Y'
318 ORDER BY ciab.incident_id;
319 END IF;
320 ELSIF l_auto_submit_mode = 'WHEN_ALL_TASKS_FINAL' THEN
321
322 IF l_check_debrief_status = 'N' THEN
323 IF NOT autosubmit_cv%ISOPEN THEN
324 /* Open cursor variable. */
325 OPEN autosubmit_cv FOR SELECT ciab.incident_id,
326 ciab.incident_number,
327 nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
328 ciab.incident_type_id,
329 ced.estimate_detail_id,
330 hzp.party_name,
331 ced.bill_to_party_id,
332 ced.currency_code,
333 ced.list_price,
334 ced.quantity_required,
335 ced.selling_price,
336 nvl(ced.contract_discount_amount,0) contract_discount_amount,
337 ced.after_warranty_cost
338 FROM cs_incidents_all_b ciab,
339 cs_estimate_details ced,
340 hz_parties hzp
341 WHERE ciab.incident_id = ced.incident_id
342 AND ced.bill_to_party_id = hzp.party_id
343 AND ced.line_submitted = 'N'
344 AND ced.charge_line_type = 'ACTUAL'
345 AND ced.order_line_id IS NULL --bug 7692111
346 AND ced.source_code = 'SD'
347 AND ced.original_source_code = 'SR'
348 AND ced.interface_to_oe_flag = 'Y'
349 AND ciab.incident_id NOT IN (SELECT jtv.source_object_id
350 FROM jtf_tasks_vl jtv,
351 jtf_task_statuses_b jts
352 -- jtf_task_assignments jta,
353 -- csf_debrief_headers cdh
354 WHERE jtv.source_object_id = ciab.incident_id
355 -- AND jta.task_id = jtv.task_id
356 AND jtv.source_object_type_code = 'SR'
357 -- checking for closed tasks.
358 AND jtv.task_status_id = jts.task_status_id
359 AND nvl(jts.closed_flag,'N') = 'N')
360 --AND cdh.task_assignment_id = jta.task_assignment_id
361 --AND cdh.processed_flag = 'COMPLETED')
362 ORDER BY ciab.incident_id;
363 END IF;
364
365 ELSIF l_check_debrief_status = 'Y' THEN -- new enh for simplex
366 IF NOT autosubmit_cv%ISOPEN THEN
367 OPEN autosubmit_cv FOR SELECT ciab.incident_id,
368 ciab.incident_number,
369 nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
370 ciab.incident_type_id,
371 ced.estimate_detail_id,
372 hzp.party_name,
376 ced.quantity_required,
373 ced.bill_to_party_id,
374 ced.currency_code,
375 ced.list_price,
377 ced.selling_price,
378 nvl(ced.contract_discount_amount,0) contract_discount_amount,
379 ced.after_warranty_cost
380 FROM cs_incidents_all_b ciab,
381 cs_estimate_details ced,
382 hz_parties hzp
383 WHERE ciab.incident_id = ced.incident_id
384 AND ced.bill_to_party_id = hzp.party_id
385 AND ced.line_submitted = 'N'
386 AND ced.order_line_id IS NULL --bug 7647091
387 AND ced.charge_line_type = 'ACTUAL'
388 AND ced.source_code = 'SD'
389 AND ced.original_source_code = 'SR'
390 AND ced.interface_to_oe_flag = 'Y'
391 AND ciab.incident_id IN (SELECT jtv.source_object_id
392 FROM jtf_tasks_vl jtv,
393 jtf_task_statuses_b jts,
394 jtf_task_assignments jta,
395 csf_debrief_headers cdh
396 WHERE jtv.source_object_id = ciab.incident_id
397 AND jta.task_id = jtv.task_id
398 AND jtv.source_object_type_code = 'SR'
399 -- checking for closed tasks.
400 AND jtv.task_status_id = jts.task_status_id
401 AND nvl(jts.closed_flag,'N') = 'Y'
402 AND cdh.task_assignment_id = jta.task_assignment_id)
403 ORDER BY ciab.incident_id;
404 END IF;
405 END IF;
406
407 ELSIF l_auto_submit_mode = 'WHEN_SERVICE_REQUEST_FINAL' THEN
408 IF NOT autosubmit_cv%ISOPEN THEN
409 /* Open cursor variable. */
410 OPEN autosubmit_cv FOR SELECT ciab.incident_id,
411 ciab.incident_number,
412 nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
413 ciab.incident_type_id,
414 ced.estimate_detail_id,
415 hzp.party_name,
416 ced.bill_to_party_id,
417 ced.currency_code,
418 ced.list_price,
419 ced.quantity_required,
420 ced.selling_price,
421 nvl(ced.contract_discount_amount,0) contract_discount_amount,
422 ced.after_warranty_cost
423 FROM cs_incidents_all_b ciab,
424 hz_parties hzp,
425 cs_estimate_details ced
426 WHERE ciab.incident_id = ced.incident_id
427 AND ced.bill_to_party_id = hzp.party_id
428 AND ciab.status_flag = 'C'
429 AND ced.line_submitted = 'N'
430 AND ced.order_line_id IS NULL --bug 7692111
431 AND ced.charge_line_type = 'ACTUAL'
432 AND ced.source_code = 'SD'
433 AND ced.original_source_code = 'SR'
434 AND ced.interface_to_oe_flag = 'Y'
435 ORDER BY ciab.incident_id;
436 END IF;
437 END IF; -- End of autosubmit mode.
438
439 -- Open restrictions cursor and store it into a table.
440 --
441 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
442 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Start of Restrictions');
443 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
444
445 OPEN cs_chg_restriction_rules;
446 LOOP
447 j := j+1;
448 -- Fetch all the restriction rules.
449 FETCH cs_chg_restriction_rules
450 INTO RestrulesTab(j);
451 EXIT WHEN cs_chg_restriction_rules%NOTFOUND;
452
453
454 l_rest1 := ('Restriction:' || RestrulesTab(j).restriction_type || ' '|| 'Currency:' || RestrulesTab(j).currency_code);
455 l_rest2 := ('Amount:' || RestrulesTab(j).Value_Amount || ' '|| 'Value_Object_Id:' || RestrulesTab(j).Value_Object_Id);
456 l_rest3 := ('Start_Date:' || RestrulesTab(j).Start_Date_Active || ' '|| 'End_Date: ' || RestrulesTab(j).End_Date_Active);
457
458 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_rest1 || l_rest2 || l_rest3);
459
460 END LOOP;
464 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'End of Restrictions');
461 CLOSE cs_chg_restriction_rules;
462
463 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '***********************************');
465 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '***********************************');
466
467 --
468 -- Fetch all charge lines into a table
469 LOOP
470 i := i+1;
471
472 -- Fetch all eligible lines to be auto submitted.
473 FETCH autosubmit_cv
474 INTO AutosubmitTAB(i);
475 IF autosubmit_cv%found then
476 l_found := 'Y';
477 else
478 l_found := 'N';
479 End IF;
480 EXIT WHEN autosubmit_cv%NOTFOUND;
481
482 -- Calling Update_Charge_Details to clear existing messages before
483 -- logging new ones.
484 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
485 AutosubmitTAB(i).incident_number,
486 NULL,
487 NULL,
488 NULL,
489 'N',
490 'CLEAR',
491 x_return_status,
492 x_msg_data);
493 --
494 --
495 END LOOP;
496 CLOSE autosubmit_cv;
497
498 i := AutosubmitTAB.FIRST;
499 --
500 LOOP
501
502 -- dbms_output.put_line('Value Of First Incident_Id' || AutosubmitTAB(i).incident_id);
503 -- dbms_output.put_line('Value Of counter i ' || i);
504 -- dbms_output.put_line('Restriction qualify flag' || l_restriction_qualify_flag);
505
506
507 -- new enh for simplex
508
509
510 IF l_check_debrief_status = 'Y' THEN
511
512 Check_Debrief_Status(p_incident_id => AutosubmitTAB(i).incident_id,
513 p_incident_number => AutosubmitTAB(i).incident_number,
514 p_estimate_detail_id => AutosubmitTab(i).estimate_detail_id,
515 p_currency_code => AutosubmitTAB(i).currency_code,
516 x_restriction_qualify_flag => l_restriction_qualify_flag,
517 x_return_status => x_return_status,
518 x_msg_data => x_msg_data);
519 END IF;
520
521 -- end new enh for simplex
522
523
524 /*********************** LINE LEVEL RESTRICTIONS ********************/
525
526 -- dbms_output.put_line('Restriction_count' || RestrulesTab.count);
527 rest_count := RestrulesTab.count;
528 IF rest_count > 0 then
529
530 j := RestrulesTab.FIRST;
531 LOOP
532
533
534 -- If there is only one sr, then make sure that line level restrictions
535 -- are excecuted before sr level restrictions.
536 --
537
538 IF AutosubmitTAB.NEXT(i) IS NULL THEN
539 last_rec := AutosubmitTAB.COUNT;
540
541 if last_rec <> -999 and
542 last_rec = AutosubmitTAB.LAST then
543 l_line_restriction_flag := 'Y';
544 end if;
545 END IF;
546
547
548 -- Beginning of line level restrictions.
549 --
550 -- dbms_output.put_line('Beginnig of Line_Level_restriction' || RestrulesTab(j).restriction_type);
551 -- dbms_output.put_line('Restriction_count' || RestrulesTab.count);
552
553 IF RestrulesTab(j).restriction_type = ('CHARGE_LINE_AMOUNT') THEN
554
555 -- dbms_output.put_line('inside Charge_line_amt');
556 -- dbms_output.put_line('Charge_line_amt condition'|| RestrulesTab(j).condition);
557
558 IF RestrulesTab(j).condition = '>' THEN
559 if (AutosubmitTAB(i).after_warranty_cost > RestrulesTab(j).value_amount and
560 AutosubmitTAB(i).currency_code = RestrulesTab(j).currency_code and
561 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
562 or RestrulesTab(j).start_date_active IS NULL) and
563 (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active
564 or RestrulesTab(j).end_date_active IS NULL))) then
565
566
567
568 FND_MSG_PUB.Initialize;
569 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LINE_AMT_RESTRICTION');
570 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
571 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
572 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
573 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
574 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
575 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
576 FND_MSG_PUB.Add;
577
578 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
579 AutosubmitTAB(i).incident_number,
580 AutosubmitTab(i).estimate_detail_id,
581 AutosubmitTAB(i).currency_code,
585 x_return_status,
582 'CS_CHG_LINE_AMT_RESTRICTION',
583 'N',
584 'LINE',
586 x_msg_data);
587
588 l_restriction_qualify_flag := 'Y';
589
590 end if;
591
592 -- dbms_output.put_line('inside Charge_line_amt before = ');
593
594 ELSIF RestrulesTab(j).condition = '=' THEN
595 if (AutosubmitTAB(i).after_warranty_cost = RestrulesTab(j).value_amount and
596 AutosubmitTAB(i).currency_code = RestrulesTab(j).currency_code and
597 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
598 or RestrulesTab(j).start_date_active IS NULL) and
599 (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active
600 or RestrulesTab(j).end_date_active IS NULL))) then
601
602
603 FND_MSG_PUB.Initialize;
604 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LINE_AMT_RESTRICTION');
605 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
606 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
607 -- FND_MESSAGE.SET_TOKEN('SR', chgSrTotTAB(k).incident_number, TRUE);
608 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
609 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
610 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
611 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
612 FND_MSG_PUB.Add;
613
614 -- Call Update_Charge_Lines.
615 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
616 AutosubmitTAB(i).incident_number,
617 AutosubmitTab(i).estimate_detail_id,
618 AutosubmitTAB(i).currency_code,
619 'CS_CHG_LINE_AMT_RESTRICTION',
620 'N',
621 'LINE',
622 x_return_status,
623 x_msg_data);
624
625 l_restriction_qualify_flag := 'Y';
626
627 end if;
628
629 -- dbms_output.put_line('Before Restriction Condition ' || RestrulesTab(j).condition);
630
631 ELSIF RestrulesTab(j).condition = '<' THEN
632
633 if AutosubmitTAB(i).after_warranty_cost < RestrulesTab(j).value_amount and
634 AutosubmitTAB(i).currency_code = RestrulesTab(j).currency_code and
635 (AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
636 or RestrulesTab(j).start_date_active IS NULL) and (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active
637 or RestrulesTab(j).end_date_active IS NULL) then
638
639 -- dbms_output.put_line('After Restriction Condition ' || RestrulesTab(j).condition);
640
641 FND_MSG_PUB.Initialize;
642 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LINE_AMT_RESTRICTION');
643 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
644 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
645 -- FND_MESSAGE.SET_TOKEN('SR', chgSrTotTAB(k).incident_number, TRUE);
646 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
647 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
648 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
649 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
650 FND_MSG_PUB.Add;
651
652 -- Call Update_Charge_Lines.
653 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
654 AutosubmitTAB(i).incident_number,
655 AutosubmitTab(i).estimate_detail_id,
656 AutosubmitTAB(i).currency_code,
657 'CS_CHG_LINE_AMT_RESTRICTION',
658 'N',
659 'LINE',
660 x_return_status,
661 x_msg_data);
662
663 l_restriction_qualify_flag := 'Y';
664
665 end if;
666
667 ELSIF RestrulesTab(j).condition = '<=' THEN
668 if (AutosubmitTAB(i).after_warranty_cost <= RestrulesTab(j).value_amount and
672 (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active
669 AutosubmitTAB(i).currency_code = RestrulesTab(j).currency_code and
670 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
671 or RestrulesTab(j).start_date_active IS NULL) and
673 or RestrulesTab(j).end_date_active IS NULL))) then
674
675 FND_MSG_PUB.Initialize;
676 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LINE_AMT_RESTRICTION');
677 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
678 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
679 -- FND_MESSAGE.SET_TOKEN('SR', chgSrTotTAB(k).incident_number, TRUE);
680 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
681 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
682 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
683 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
684 FND_MSG_PUB.Add;
685
686 -- Call Update_Charge_Lines.
687 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
688 AutosubmitTAB(i).incident_number,
689 AutosubmitTab(i).estimate_detail_id,
690 AutosubmitTAB(i).currency_code,
691 'CS_CHG_LINE_AMT_RESTRICTION',
692 'N',
693 'LINE',
694 x_return_status,
695 x_msg_data);
696
697 l_restriction_qualify_flag := 'Y';
698
699 end if;
700
701 ELSIF RestrulesTab(j).condition = '>=' THEN
702 if (AutosubmitTAB(i).after_warranty_cost >= RestrulesTab(j).value_amount and
703 AutosubmitTAB(i).currency_code = RestrulesTab(j).currency_code and
704 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
705 or RestrulesTab(j).start_date_active IS NULL) and
706 (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active
707 or RestrulesTab(j).end_date_active IS NULL))) then
708
709
710 FND_MSG_PUB.Initialize;
711 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LINE_AMT_RESTRICTION');
712 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
713 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
714 -- FND_MESSAGE.SET_TOKEN('SR', chgSrTotTAB(k).incident_number, TRUE);
715 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
716 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
717 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
718 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
719 FND_MSG_PUB.Add;
720
721 -- Call Update_Charge_Lines.
722 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
723 AutosubmitTAB(i).incident_number,
724 AutosubmitTab(i).estimate_detail_id,
725 AutosubmitTAB(i).currency_code,
726 'CS_CHG_LINE_AMT_RESTRICTION',
727 'N',
728 'LINE',
729 x_return_status,
730 x_msg_data);
731
732 l_restriction_qualify_flag := 'Y';
733
734 end if;
735
736 END IF; -- Charge_line_amt Condition Endif
737
738 ELSIF RestrulesTab(j).restriction_type = ('BILL_TO_CUSTOMER') THEN
739 if (AutosubmitTAB(i).bill_to_party_id = RestrulesTab(j).value_object_id) and
740 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active)
741 or (RestrulesTab(j).start_date_active IS NULL)) and
742 ((RestrulesTab(j).end_date_active IS NULL) or
743 (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active)) then
744
745 FND_MSG_PUB.Initialize;
746 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_BILL_TO_CT_RESTRICTION');
747 FND_MESSAGE.SET_TOKEN('BILL_TO_CUSTOMER_ID',AutosubmitTAB(i).bill_to_party_id);
748 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
749 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
750 FND_MESSAGE.SET_TOKEN('BILL_TO_CUSTOMER_NAME', AutosubmitTAB(i).party_name);
754 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
751 FND_MSG_PUB.Add;
752
753 -- Call Update_Charge_Lines.
755 AutosubmitTAB(i).incident_number,
756 AutosubmitTab(i).estimate_detail_id,
757 AutosubmitTAB(i).currency_code,
758 'CS_CHG_BILL_TO_CT_RESTRICTION',
759 'N',
760 'LINE',
761 x_return_status,
762 x_msg_data);
763
764 l_restriction_qualify_flag := 'Y';
765 end if;
766
767 ELSIF (RestrulesTab(j).restriction_type = ('EXCLUDE_IF_MANUALLY_OVERRIDDEN') and
768 ((AutosubmitTAB(i).incident_date >= RestrulesTab(j).start_date_active
769 or RestrulesTab(j).start_date_active IS NULL) and (AutosubmitTAB(i).incident_date <= RestrulesTab(j).end_date_active or RestrulesTab(j).end_date_active IS NULL))) then
770 --
771 --
772 OPEN cs_charge_flags(AutosubmitTAB(i).estimate_detail_id);
773 FETCH cs_charge_flags
774 INTO l_chg_no_charge_flag,
775 l_txn_no_charge_flag;
776 CLOSE cs_charge_flags;
777 --
778 --
779 if ((AutosubmitTAB(i).after_warranty_cost <> ((AutosubmitTAB(i).selling_price * AutosubmitTAB(i).quantity_required - AutosubmitTAB(i).contract_discount_amount)) and
780 l_chg_no_charge_flag = 'N' and
781 l_txn_no_charge_flag = 'N') OR
782 (AutosubmitTAB(i).after_warranty_cost <> ((AutosubmitTAB(i).selling_price * AutosubmitTAB(i).quantity_required - AutosubmitTAB(i).contract_discount_amount)) and
783 l_chg_no_charge_flag = 'N' and
784 l_txn_no_charge_flag = 'Y') OR
785 (AutosubmitTAB(i).after_warranty_cost <> ((AutosubmitTAB(i).selling_price * AutosubmitTAB(i).quantity_required - AutosubmitTAB(i).contract_discount_amount)) and
786 l_chg_no_charge_flag = 'Y' and
787 l_txn_no_charge_flag = 'N')) then
788
789
790 FND_MSG_PUB.Initialize;
791 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_AMT_OVERIDE_RESTRICTION');
792 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',AutosubmitTAB(i).currency_code);
793 FND_MESSAGE.SET_TOKEN('AFTER_WARRANTY_COST',AutosubmitTAB(i).after_warranty_cost);
794 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
795 FND_MSG_PUB.Add;
796 -- Need to verify if these are required.
797 -- Confirmed with krasimir that this is not required.
798 /* FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition,TRUE);
799 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).condition,TRUE);
800 FND_MESSAGE.SET_TOKEN('CURRENCY', AutosubmitTAB(i).party_name,TRUE); */
801
802
803 -- Call Update_Charge_Lines.
804
805 Update_Charge_Lines(AutosubmitTAB(i).incident_id,
806 AutosubmitTAB(i).incident_number,
807 AutosubmitTab(i).estimate_detail_id,
808 AutosubmitTAB(i).currency_code,
809 'CS_CHG_AMT_OVERIDE_RESTRICTION',
810 'N',
811 'LINE',
812 x_return_status,
813 x_msg_data);
814
815 l_restriction_qualify_flag := 'Y';
816 end if;
817
818 END IF; -- End If for line level restriction types.
819
820 EXIT WHEN j = RestrulesTab.LAST;
821 j := RestrulesTab.NEXT(j);
822
823 END LOOP; -- restrictions End Loop
824 -- dbms_output.put_line('Restriction_Qualify_Flag in the line end' || l_restriction_qualify_flag);
825
826 /****************************** END OF LINE LEVEL RESTRICTIONS ***********************************/
827
828 /***************************** SR LEVEL RESTRICTIONS **********************************/
829
830 -- Assigning the next index value to L
831 -- and verify if the value exists.
832
833 -- dbms_output.put_line('Restriction Count' || RestrulesTab.count);
834 -- dbms_output.put_line('Beginning of SR level restriction');
835 -- dbms_output.put_line('restriction_qualify_flag' || l_restriction_qualify_flag);
836
837 L := AutosubmitTAB.NEXT(i);
838 IF AutosubmitTAB.EXISTS(L) THEN
839 L := AutosubmitTAB.NEXT(i);
840 ELSE
841 L := i;
842 l_last_rec_flag := 'Y';
843 END IF;
844
845 --
846 IF (AutosubmitTAB(i).incident_id <> AutosubmitTAB(L).incident_id
847 OR l_line_restriction_flag = 'Y'
848 OR l_last_rec_flag = 'Y' ) AND
852 l_incident_id := AutosubmitTAB(i).incident_id;
849 (l_restriction_qualify_flag = 'N' OR
850 l_restriction_qualify_flag = 'Y') THEN
851
853
854
855 -- dbms_output.put_line('Value Of l_Incident_Id in the SR level restriction' || AutosubmitTAB(i).incident_id);
856
857 OPEN Cs_Chg_Sr_Total(l_incident_id);
858 LOOP
859 k := k+1;
860 FETCH Cs_Chg_Sr_Total
861 INTO ChgSrTotTAB(k);
862 EXIT WHEN Cs_Chg_Sr_Total%NOTFOUND;
863
864 j := RestrulesTab.FIRST;
865
866 LOOP
867
868 -- dbms_output.put_line('Restriction_type before Total_Sr' || RestrulesTab(j).restriction_type);
869
870 /*** Call the SR level restrictions **/
871 IF RestrulesTab(j).restriction_type = 'TOTAL_SERVICE_REQUEST_CHARGES' THEN
872 IF RestrulesTab(j).condition = '>' then
873 if chgSrTotTAB(k).Total_Charges > RestrulesTab(j).value_amount and
874 chgSrTotTAB(k).currency_code = RestrulesTab(j).currency_code and
875 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
876 or RestrulesTab(j).start_date_active IS NULL) and
877 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
878 or RestrulesTab(j).end_date_active IS NULL)) then
879
880 FND_MSG_PUB.Initialize;
881 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TOTAL_CHRG_RESTRICTION');
882 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',chgSrTotTAB(k).currency_code);
883 FND_MESSAGE.SET_TOKEN('TOTAL_AMOUNT',chgSrTotTAB(k).Total_Charges);
884 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', chgSrTotTAB(k).incident_number);
885 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
886 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
887 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
888 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
889 FND_MSG_PUB.Add;
890
891 -- Call Update_Charge_Lines.
892 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
893 ChgSrTotTAB(k).incident_number,
894 NULL,
895 RestrulesTab(j).currency_code,
896 'CS_CHG_TOTAL_CHRG_RESTRICTION',
897 'N',
898 'HEADER',
899 x_return_status,
900 x_msg_data);
901
902 l_restriction_qualify_flag := 'Y';
903
904 end if;
905
906 ELSIF RestrulesTab(j).condition = '=' then
907 if chgSrTotTAB(k).Total_Charges = RestrulesTab(j).value_amount and
908 chgSrTotTAB(k).currency_code = RestrulesTab(j).currency_code and
909 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
910 or RestrulesTab(j).start_date_active IS NULL) and
911 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
912 or RestrulesTab(j).end_date_active IS NULL)) then
913
914
915 FND_MSG_PUB.Initialize;
916 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TOTAL_CHRG_RESTRICTION');
917 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',chgSrTotTAB(k).currency_code);
918 FND_MESSAGE.SET_TOKEN('TOTAL_AMOUNT',chgSrTotTAB(k).Total_Charges);
919 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', chgSrTotTAB(k).incident_number);
920 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
921 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
922 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
923 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
924 FND_MSG_PUB.Add;
925
926
927 -- Call Update_Charge_Lines.
928 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
929 ChgSrTotTAB(k).incident_number,
930 NULL,
931 RestrulesTab(j).currency_code,
932 'CS_CHG_TOTAL_CHRG_RESTRICTION',
933 'N',
934 'HEADER',
935 x_return_status,
936 x_msg_data);
937
938 l_restriction_qualify_flag := 'Y';
939
940
941 end if;
942
943 ELSIF RestrulesTab(j).condition = '<' then
944 if chgSrTotTAB(k).Total_Charges < RestrulesTab(j).value_amount and
945 chgSrTotTAB(k).currency_code = RestrulesTab(j).currency_code and
946 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
947 or RestrulesTab(j).start_date_active IS NULL) and
951
948 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
949 or RestrulesTab(j).end_date_active IS NULL)) then
950
952 FND_MSG_PUB.Initialize;
953 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TOTAL_CHRG_RESTRICTION');
954 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',chgSrTotTAB(k).currency_code);
955 FND_MESSAGE.SET_TOKEN('TOTAL_AMOUNT',chgSrTotTAB(k).Total_Charges);
956 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', chgSrTotTAB(k).incident_number);
957 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
958 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
959 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
960 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
961 FND_MSG_PUB.Add;
962
963
964 -- Call Update_Charge_Lines.
965 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
966 ChgSrTotTAB(k).incident_number,
967 NULL,
968 RestrulesTab(j).currency_code,
969 'CS_CHG_TOTAL_CHRG_RESTRICTION',
970 'N',
971 'HEADER',
972 x_return_status,
973 x_msg_data);
974
975
976 l_restriction_qualify_flag := 'Y';
977
978 end if;
979
980 ELSIF RestrulesTab(j).condition = '<=' then
981 if chgSrTotTAB(k).Total_Charges <= RestrulesTab(j).value_amount and
982 chgSrTotTAB(k).currency_code = RestrulesTab(j).currency_code and
983 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
984 or RestrulesTab(j).start_date_active IS NULL) and
985 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
986 or RestrulesTab(j).end_date_active IS NULL)) then
987
988
989
990 FND_MSG_PUB.Initialize;
991 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TOTAL_CHRG_RESTRICTION');
992 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',chgSrTotTAB(k).currency_code);
993 FND_MESSAGE.SET_TOKEN('TOTAL_AMOUNT',chgSrTotTAB(k).Total_Charges);
994 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', chgSrTotTAB(k).incident_number);
995 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
996 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
997 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
998 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
999 FND_MSG_PUB.Add;
1000
1001
1002 -- Call Update_Charge_Lines.
1003 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
1004 ChgSrTotTAB(k).incident_number,
1005 NULL,
1006 RestrulesTab(j).currency_code,
1007 'CS_CHG_TOTAL_CHRG_RESTRICTION',
1008 'N',
1009 'HEADER',
1010 x_return_status,
1011 x_msg_data);
1012
1013
1014 l_restriction_qualify_flag := 'Y';
1015
1016
1017 end if;
1018
1019 ELSIF RestrulesTab(j).condition = '>=' then
1020 if chgSrTotTAB(k).Total_Charges >= RestrulesTab(j).value_amount and
1021 chgSrTotTAB(k).currency_code = RestrulesTab(j).currency_code and
1022 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
1023 or RestrulesTab(j).start_date_active IS NULL) and
1024 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
1025 or RestrulesTab(j).end_date_active IS NULL)) then
1026
1027
1028 FND_MSG_PUB.Initialize;
1029 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TOTAL_CHRG_RESTRICTION');
1030 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',chgSrTotTAB(k).currency_code);
1031 FND_MESSAGE.SET_TOKEN('TOTAL_AMOUNT',chgSrTotTAB(k).Total_Charges);
1032 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', chgSrTotTAB(k).incident_number);
1033 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
1034 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
1035 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
1036 FND_MESSAGE.SET_TOKEN('CURRENCY', RestrulesTab(j).currency_code);
1037 FND_MSG_PUB.Add;
1038
1039 -- Call Update_Charge_Lines.
1040 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
1041 ChgSrTotTAB(k).incident_number,
1042 NULL,
1046 'HEADER',
1043 RestrulesTab(j).currency_code,
1044 'CS_CHG_TOTAL_CHRG_RESTRICTION',
1045 'N',
1047 x_return_status,
1048 x_msg_data);
1049
1050
1051 l_restriction_qualify_flag := 'Y';
1052
1053 end if;
1054 END IF; --Endif for TOTAL_SERVICE_REQUEST_CHARGES condition.
1055 END IF; -- Endif for total_service_request.
1056
1057 IF RestrulesTab(j).restriction_type = 'SERVICE_REQUEST_TYPE' THEN
1058 if RestrulesTab(j).value_object_id = chgSrTotTab(k).incident_type_id and
1059 ((chgSrTotTab(k).incident_date >= RestrulesTab(j).start_date_active
1060 or RestrulesTab(j).start_date_active IS NULL) and
1061 (chgSrTotTab(k).incident_date <= RestrulesTab(j).end_date_active
1062 or RestrulesTab(j).end_date_active IS NULL)) then
1063
1064
1065
1066 FND_MSG_PUB.Initialize;
1067 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_SR_TYPE_RESTRICTION');
1068 -- Need to verify, if we need to show status in the message.
1069 -- FND_MESSAGE.SET_TOKEN('STATUS',chgSrTotTAB(k).currency_code, TRUE);
1070 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
1071 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
1072 FND_MESSAGE.SET_TOKEN('INCIDENT_TYPE', chgSrTotTAB(k).incident_type);
1073 FND_MSG_PUB.Add;
1074
1075
1076 -- Call Update_Charge_Lines.
1077 Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
1078 ChgSrTotTAB(k).incident_number,
1079 NULL,
1080 RestrulesTab(j).currency_code,
1081 'CS_CHG_SR_TYPE_RESTRICTION',
1082 'N',
1083 'HEADER',
1084 x_return_status,
1085 x_msg_data);
1086
1087 l_restriction_qualify_flag := 'Y';
1088
1089
1090 end if;
1091 END IF; -- Restrictions EndIf.
1092
1093
1094 EXIT WHEN j = RestrulesTab.LAST;
1095 j := RestrulesTab.NEXT(j);
1096
1097
1098 END LOOP; -- Restrictions EndLoop.
1099
1100 END LOOP; --Total Sr EndLoop.
1101 CLOSE Cs_Chg_Sr_Total;
1102
1103 --
1104 -- 'Actuals Exceed Estimates restriction Type'.
1105 j := RestrulesTab.FIRST;
1106 LOOP
1107
1108
1109 IF RestrulesTab(j).restriction_type = ('ACTUALS_EXCEED_ESTIMATES') THEN
1110 --
1111 -- Get the Actual and Estimate Value for the SR.
1112 -- bug fix:3542151
1113 n := 0;
1114 OPEN Cs_Chg_Est_Act_Tot(l_incident_id);
1115 n := n + 1;
1116 LOOP
1117 FETCH Cs_Chg_Est_Act_Tot
1118 INTO ChgEstActTotTAB(n);
1119 EXIT WHEN Cs_Chg_Est_Act_Tot%NOTFOUND;
1120 END LOOP;
1121 CLOSE Cs_Chg_Est_Act_Tot;
1122
1123 n := ChgEstActTotTAB.FIRST;
1124 LOOP
1125
1126 IF ChgEstActTotTAB.EXISTS(n) THEN
1127 IF ChgEstActTotTAB(n).Estimates IS NOT NULL and
1128 ChgEstActTotTAB(n).Actuals IS NOT NULL THEN
1129
1130 l_Actual_Percent := ((ChgEstActTotTAB(n).Actuals - ChgEstActTotTAB(n).Estimates)/ChgEstActTotTAB(n).Actuals)*100;
1131
1132 IF RestrulesTab(j).condition = '>' then
1133 if l_Actual_Percent > RestrulesTab(j).value_amount and
1134 ((ChgEstActTotTAB(n).incident_date >= RestrulesTab(j).start_date_active
1135 or RestrulesTab(j).start_date_active IS NULL) and
1136 (ChgEstActTotTAB(n).incident_date <= RestrulesTab(j).end_date_active
1137 or RestrulesTab(j).end_date_active IS NULL)) then
1138
1139
1140 FND_MSG_PUB.Initialize;
1141 FND_MESSAGE.SET_NAME('CS','CS_CHG_A_EXCEED_ET_RESTRICTION');
1142 FND_MESSAGE.SET_TOKEN('ACTUALS',ChgEstActTotTAB(n).Actuals);
1143 FND_MESSAGE.SET_TOKEN('ESTIMATES',ChgEstActTotTAB(n).Estimates);
1144 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER', ChgEstActTotTAB(n).incident_number);
1145 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE', ChgEstActTotTAB(n).currency_code);
1146 FND_MESSAGE.SET_TOKEN('RESTRICTION_TYPE', RestrulesTab(j).restriction_type);
1147 FND_MESSAGE.SET_TOKEN('CONDITION', RestrulesTab(j).condition);
1148 FND_MESSAGE.SET_TOKEN('VALUE_AMOUNT', RestrulesTab(j).value_amount);
1149 FND_MSG_PUB.Add;
1150
1151
1152 -- Call Update_Charge_Lines.
1153 Update_Charge_Lines(ChgEstActTotTAB(n).incident_id,
1157 'CS_CHG_A_EXCEED_ET_RESTRICTION',
1154 ChgEstActTotTAB(n).incident_number,
1155 NULL,
1156 ChgEstActTotTAB(n).currency_code,
1158 'N',
1159 'HEADER',
1160 x_return_status,
1161 x_msg_data);
1162
1163 l_restriction_qualify_flag := 'Y';
1164
1165
1166 end if;
1167
1168 END IF; -- End of Condition for Actuals_exceed_estimates
1169 END IF; -- actuals and estimates endif
1170 END IF;
1171
1172 EXIT WHEN n = ChgEstActTotTAB.LAST;
1173 n := ChgEstActTotTAB.NEXT(n);
1174 END LOOP;
1175
1176 END IF; -- Restrictions end if.
1177
1178 EXIT WHEN j = RestrulesTab.LAST;
1179 j := RestrulesTab.NEXT(j);
1180 END LOOP; -- Restrictions Loop
1181
1182
1183 -- dbms_output.put_line('Calling submit_charge_lines');
1184 -- dbms_output.put_line('restriction_flag' || l_restriction_qualify_flag);
1185
1186 --
1187 IF l_restriction_qualify_flag = 'N' then
1188 -- Call submit order API here for an incident_id.
1189 Submit_Charge_Lines(p_incident_id => l_incident_id,
1190 x_return_status => x_return_status,
1191 x_msg_count => x_msg_count,
1192 x_msg_data => x_msg_data);
1193 END IF;
1194
1195 END IF; -- End If check the incident_id value.
1196
1197
1198 /*********** END OF SR LEVEL RESTRICTIONS *************/
1199
1200 -- Need to reset the restriction_qualify_flag if SR number changes.
1201 --
1202 L := AutosubmitTAB.NEXT(i);
1203 IF L IS NULL THEN
1204 L := AutosubmitTAB.LAST;
1205 END IF;
1206
1207 IF AutosubmitTAB(i).incident_id <> AutosubmitTAB(L).incident_id THEN
1208 -- Reset the restriction qualify flag
1209 l_restriction_qualify_flag := 'N';
1210 END IF;
1211
1212
1213
1214 ELSIF rest_count = 0 then
1215
1216 -- dbms_output.put_line('Value of incident_id ' || AutosubmitTAB(i).incident_id);
1217
1218 Submit_Charge_Lines(p_incident_id => AutosubmitTAB(i).incident_id,
1219 x_return_status => x_return_status,
1220 x_msg_count => x_msg_count,
1221 x_msg_data => x_msg_data);
1222
1223 -- dbms_output.put_line('completed submission');
1224
1225 END IF;
1226 --
1227 --
1228 EXIT WHEN i = AutosubmitTAB.LAST;
1229 i := AutosubmitTAB.NEXT(i);
1230 END LOOP; --Cs_Chg_Auto_Submit_Lines End Loop
1231
1232
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 IF l_found = 'N' THEN
1237 FND_FILE.put_line(FND_FILE.LOG,'There are no eligible Charge lines available for submission to Order Management');
1238 ELSE
1239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1240 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
1241 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Auto_submit_charge_Lines');
1242 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
1243 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
1244 FND_MSG_PUB.add;
1245 FND_MSG_PUB.get(p_encoded => 'F',
1246 p_data=>x_msg_data,
1247 p_msg_index_out=> l_msg_index_out);
1248
1249 -- Recording exceptions in the log file.
1250 FND_FILE.put_line(FND_FILE.LOG,x_msg_data);
1251 END IF;
1252
1253 END Auto_Submit_Chg_Lines;
1254 --
1255 --
1256 -- *******************************************************
1257 -- Start of Comments
1258 -- *******************************************************
1259 -- Procedure Name: Update_Charge_Lines
1260 -- Type : Private
1261 -- Purpose : This procedure is for updating charge lines with appropriate
1262 -- restriction message.
1263 -- Pre-Req :
1264 -- Parameters:
1265 -- IN :
1266 -- p_incident_id IN NUMBER
1267 -- p_estimate_detail_id IN NUMBER
1268 -- p_currency_code IN VARCHAR2
1269 -- p_restriction_message IN VARCHAR2
1270 -- p_line_submitted IN VARCHAR2
1271 -- p_restriction_type IN VARCHAR2
1272 -- x_return_status OUT VARCHAR2
1273 -- x_msg_data OUT VARCHAR2
1274 -- ***************************************************************************************
1275 -- 1 | SSHILPAM | Bug 5697830: Default the line_submitted to 'N' only for the corresponding line
1276 -- | | and not to all the lines under the service request.
1277 -- 2 | GASANKAR | Bug Fix 7692111 : Added the condition AND ced.order_line_id IS NULL
1278 -- in autosubmit_cv cursors.
1279 -- ****************************************************************************************
1280
1281 PROCEDURE Update_Charge_Lines(p_incident_id NUMBER,
1282 p_incident_number VARCHAR2,
1283 p_estimate_detail_id NUMBER,
1284 p_currency_code VARCHAR2,
1285 p_submit_restriction_message VARCHAR2,
1286 p_line_submitted VARCHAR2,
1290 ) IS
1287 p_restriction_type VARCHAR2,
1288 x_return_status OUT NOCOPY VARCHAR2,
1289 x_msg_data OUT NOCOPY VARCHAR2
1291
1292 -- Number of Charge Lines
1293 -- Only actual charge lines are stamped with the restriction message
1294 -- Bug fix:3608980
1295 CURSOR Charge_Line_Count(p_incident_id NUMBER,p_currency_code VARCHAR2) IS
1296 SELECT estimate_detail_id
1297 FROM cs_estimate_details
1298 WHERE incident_id = p_incident_id
1299 AND charge_line_type = 'ACTUAL'
1300 AND source_code = 'SD'
1301 AND original_source_code = 'SR'
1302 AND currency_code = nvl(p_currency_code,currency_code)
1303 AND line_submitted = 'N'
1304 AND order_line_id IS NULL ; --bug 7692111
1305
1306
1307 TYPE t_charge_count_tab IS TABLE OF Charge_Line_Count%rowtype
1308 INDEX BY BINARY_INTEGER;
1309
1310 chglnctTAB t_charge_count_tab;
1311
1312 lx_msg_data VARCHAR2(2000);
1313 t NUMBER :=0;
1314 l_msg_index_out NUMBER;
1315
1316 BEGIN
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318
1319 IF p_submit_restriction_message IS NOT NULL THEN
1320
1321 FND_MSG_PUB.get(p_encoded => 'F',
1322 p_data=>lx_msg_data,
1323 p_msg_index_out=> l_msg_index_out);
1324
1325 -- Recording the message for concurrent program output.
1326 FND_FILE.PUT_LINE(FND_FILE.output,'Service Request Number:' || p_incident_number);
1327 FND_FILE.PUT_LINE(FND_FILE.output, lx_msg_data);
1328
1329
1330 IF p_restriction_type = 'LINE' THEN
1331
1332 UPDATE CS_ESTIMATE_DETAILS
1333 SET submit_restriction_message = (submit_restriction_message || lx_msg_data),
1334 line_submitted = p_line_submitted,
1335 last_update_date = sysdate, -- bug 8838622
1336 last_update_login = fnd_global.login_id, -- bug 8838622
1337 last_updated_by = fnd_global.user_id -- bug 8838622
1338 WHERE Estimate_Detail_Id = p_estimate_detail_id
1339 AND incident_id = p_incident_id;
1340
1341
1342 ELSIF p_restriction_type = 'HEADER' THEN
1343
1344 OPEN Charge_Line_Count(p_incident_id,p_currency_code);
1345 LOOP
1346 t := t +1;
1347
1348 FETCH Charge_Line_Count
1349 INTO chglnctTAB(t);
1350 EXIT WHEN Charge_Line_Count%NOTFOUND;
1351
1352
1353 UPDATE CS_ESTIMATE_DETAILS
1354 SET submit_restriction_message = (submit_restriction_message || lx_msg_data),
1355 line_submitted = p_line_submitted,
1356 last_update_date = sysdate, -- bug 8838622
1357 last_update_login = fnd_global.login_id, -- bug 8838622
1358 last_updated_by = fnd_global.user_id -- bug 8838622
1359 WHERE incident_id = p_incident_id
1360 AND estimate_detail_id = chglnctTAB(t).estimate_detail_id;
1361
1362 END LOOP;
1363 CLOSE Charge_Line_Count;
1364
1365 END IF; --restriction_type.
1366
1367 ELSIF p_submit_restriction_message IS NULL THEN
1368
1369 IF p_restriction_type = 'CLEAR' THEN
1370 OPEN Charge_Line_Count(p_incident_id,p_currency_code);
1371 LOOP
1372 t := t +1;
1373
1374 FETCH Charge_Line_Count
1375 INTO chglnctTAB(t);
1376 EXIT WHEN Charge_Line_Count%NOTFOUND;
1377
1378 UPDATE CS_ESTIMATE_DETAILS
1379 SET submit_restriction_message = NULL,
1380 line_submitted = p_line_submitted,
1381 last_update_date = sysdate, -- bug 8838622
1382 last_update_login = fnd_global.login_id, -- bug 8838622
1383 last_updated_by = fnd_global.user_id -- bug 8838622
1384 WHERE incident_id = p_incident_id
1385 AND estimate_detail_id = chglnctTAB(t).estimate_detail_id; -- For bug 5697830
1386
1387 COMMIT;
1388 END LOOP;
1389 CLOSE Charge_Line_Count;
1390 END IF; -- restriction_type.
1391 END IF; -- submit_error_message.
1392
1393 COMMIT;
1394
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1398 -- ROLLBACK TO CS_Chg_Auto_Submission_PVT;
1399 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
1400 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Update_Charge_Lines');
1401 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
1402 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
1403 FND_MSG_PUB.add;
1404 FND_MSG_PUB.get(p_encoded => 'F',
1405 p_data=>x_msg_data,
1406 p_msg_index_out=> l_msg_index_out);
1407
1408 -- Recording exceptions in the log file.
1409 FND_FILE.put_line(FND_FILE.LOG,x_msg_data);
1410
1411
1412 END Update_Charge_Lines;
1413
1414 -- Procedure Submit_Charge_Lines.
1415 -- Parameters:
1416 -- p_incident_id IN NUMBER Required
1417 -- OUT:
1418 -- x_return_status OUT NOCOPY VARCHAR2
1419 -- x_msg_count OUT NOCOPY NUMBER
1420 -- x_msg_data OUT NOCOPY VARCHAR2
1421 --
1422 PROCEDURE Submit_Charge_Lines(p_incident_id IN NUMBER,
1426
1423 x_return_status OUT NOCOPY VARCHAR2,
1424 x_msg_count OUT NOCOPY NUMBER,
1425 x_msg_data OUT NOCOPY VARCHAR2) IS
1427 CURSOR SR_VAL(p_incident_id number) IS
1428 select inc.customer_id,inc.account_id,inc.incident_number
1429 from cs_incidents_all_b inc
1430 where inc.incident_id = p_incident_id;
1431
1432 l_account_id NUMBER;
1433 l_party_id NUMBER;
1434 l_incident_number VARCHAR2(30);
1435 l_msg_index_out NUMBER;
1436
1437 BEGIN
1438
1439 IF p_incident_id IS NOT NULL THEN
1440
1441 OPEN SR_VAL(p_incident_id);
1442 FETCH SR_VAL
1443 INTO l_party_id,l_account_id,l_incident_number;
1444 CLOSE SR_VAL;
1445
1446 END IF;
1447
1448
1449 -- Recording the message for concurrent program output.
1450 FND_FILE.PUT_LINE(FND_FILE.output,'Service Request Number:' || l_incident_number);
1451 --
1452 -- dbms_output.put_line('Calling submit_order');
1453
1454 CS_Charge_Create_Order_PUB.Submit_Order(
1455 p_api_version => 1.0,
1456 p_init_msg_list => 'T',
1457 p_commit => 'T',
1458 p_validation_level => NULL,
1459 p_incident_id => p_incident_id,
1460 p_party_id => l_party_id,
1461 p_account_id => l_account_id,
1462 p_book_order_flag => NULL,
1463 p_submit_source => 'FS',
1464 p_submit_from_system => 'AUTO_SUBMISSION',
1465 x_return_status => x_return_status,
1466 x_msg_count => x_msg_count,
1467 x_msg_data => x_msg_data);
1468
1469
1470
1471 IF x_return_status <> 'S' THEN
1472 IF (FND_MSG_PUB.Count_Msg > 0) THEN
1473 FOR i in 1..FND_MSG_PUB.Count_Msg
1474 LOOP
1475 FND_MSG_PUB.Get(p_msg_index => i,
1476 p_encoded => 'F',
1477 p_data => x_msg_data,
1478 p_msg_index_out => l_msg_index_out );
1479
1480 -- logging messages returned by the submit order API.
1481 fnd_file.put_line(FND_FILE.OUTPUT,x_msg_data);
1482
1483 END LOOP;
1484 END IF;
1485 END IF;
1486 --
1487 -- This is added to change the restriction flag value after the SR has been processed.
1488 --
1489 -- l_restriction_flag := ' ';
1490
1491
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1495 -- ROLLBACK TO CS_Chg_Auto_Submission_PVT;
1496 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
1497 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Update_Charge_Lines');
1498 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
1499 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
1500 FND_MSG_PUB.add;
1501 FND_MSG_PUB.get(p_encoded => 'F',
1502 p_data=>x_msg_data,
1503 p_msg_index_out=> l_msg_index_out);
1504
1505 -- Recording exceptions in the log file.
1506 FND_FILE.put_line(FND_FILE.LOG,x_msg_data);
1507
1508 END Submit_Charge_Lines;
1509
1510
1511
1512 -- new enh for simplex
1513
1514
1515 -- *******************************************************
1516 -- Start of Comments
1517 -- *******************************************************
1518 -- Procedure Name: Check_Debrief_Status
1519 -- Type : Private
1520 -- Purpose : This procedure is to verify the debrief status before submitting to OM
1521 -- Pre-Req :
1522 -- Parameters:
1523 -- IN :
1524 -- p_incident_id IN NUMBER
1525 -- p_estimate_detail_id IN NUMBER
1526 -- p_currency_code IN VARCHAR2
1527 -- p_incident_number IN VARCHAR2
1528 -- x_return_status OUT VARCHAR2
1529 -- x_msg_data OUT VARCHAR2
1530
1531
1532 PROCEDURE Check_Debrief_Status(p_incident_id NUMBER,
1533 p_incident_number VARCHAR2,
1534 p_estimate_detail_id NUMBER,
1535 p_currency_code VARCHAR2,
1536 x_restriction_qualify_flag OUT NOCOPY VARCHAR2,
1537 x_return_status OUT NOCOPY VARCHAR2,
1538 x_msg_data OUT NOCOPY VARCHAR2
1539 ) IS
1540
1541
1542
1543 l_return_status VARCHAR2(1);
1544 l_msg_count NUMBER;
1545 l_msg_data VARCHAR2(4000);
1546 lx_msg_index_out number;
1547 l_deb_status CSF_DEBRIEF_UPDATE_PKG.debrief_status_tbl_type;
1548 l_count_db NUMBER;
1549 l_count_ui NUMBER;
1550 lv_index BINARY_INTEGER;
1551 l_debrief_status VARCHAR2(1);
1552 conc_status BOOLEAN;
1553 l_msg_index_out NUMBER;
1554 lx_msg_count NUMBER;
1555
1556
1557 BEGIN
1558 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
1559 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'verifying debrief status ..');
1560 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
1561
1562
1563 x_return_status := FND_API.G_RET_STS_SUCCESS;
1564 x_restriction_qualify_flag := 'N';
1565
1566 csf_debrief_update_pkg.debrief_status_check(
1570 x_debrief_status => l_deb_status,
1567 p_incident_id => p_incident_id,
1568 p_api_version => 1.0,
1569 p_validation_level => 0,
1571 x_return_status => x_return_status,
1572 x_msg_count => l_msg_count,
1573 x_msg_data => l_msg_data );
1574
1575
1576
1577 IF x_return_status = 'E' THEN
1578
1579 IF l_deb_status.COUNT > 0 THEN
1580 lv_index := l_deb_status.FIRST;
1581 FOR lv_temp IN 1..l_deb_status.COUNT LOOP
1582
1583 IF l_deb_status(lv_index).debrief_status = 'P' THEN
1584
1585 FND_MSG_PUB.Initialize;
1586 FND_MESSAGE.SET_NAME( 'CS','CS_CHG_DEBRIEF_PENDING');
1587 FND_MSG_PUB.Add;
1588
1589 FND_MSG_PUB.get(p_encoded => 'F',
1590 p_data=>l_msg_data,
1591 p_msg_index_out=> l_msg_index_out);
1592
1593 FND_FILE.put_line(FND_FILE.LOG,'Service Request Number:' || p_incident_number);
1594 FND_FILE.put_line(FND_FILE.LOG,'Estimate Detail ID:' || p_estimate_detail_id);
1595 FND_FILE.put_line(FND_FILE.LOG,l_msg_data);
1596 conc_status := fnd_concurrent.set_completion_status('WARNING','Warning');
1597
1598 Update_Charge_Lines(p_incident_id,
1599 p_incident_number,
1600 p_estimate_detail_id,
1601 p_currency_code,
1602 'CS_CHG_DEBRIEF_PENDING',
1603 'N',
1604 'HEADER',
1605 x_return_status,
1606 x_msg_data);
1607
1608 x_restriction_qualify_flag := 'Y';
1609
1610
1611 ELSIF l_deb_status(lv_index).debrief_status = 'E' THEN
1612 FND_MSG_PUB.Initialize;
1613 FND_MESSAGE.SET_NAME( 'CS','CS_CHG_DEBRIEF_ERRORS');
1614 FND_MSG_PUB.Add;
1615 FND_MSG_PUB.get(p_encoded => 'F',
1616 p_data=>l_msg_data,
1617 p_msg_index_out=> l_msg_index_out);
1618
1619 FND_FILE.put_line(FND_FILE.LOG,'Service Request Number:' || p_incident_number);
1620 FND_FILE.put_line(FND_FILE.LOG,'Estimate Detail ID:' || p_estimate_detail_id);
1621 FND_FILE.put_line(FND_FILE.LOG,l_msg_data);
1622 conc_status := fnd_concurrent.set_completion_status('WARNING','Warning');
1623
1624
1625 Update_Charge_Lines(p_incident_id,
1626 p_incident_number,
1627 p_estimate_detail_id,
1628 p_currency_code,
1629 'CS_CHG_DEBRIEF_ERRORS',
1630 'N',
1631 'HEADER',
1632 x_return_status,
1633 x_msg_data);
1634 x_restriction_qualify_flag := 'Y';
1635
1636 ELSE
1637 NULL; -- neither E or P
1638 END IF; --for E or P status
1639
1640
1641 EXIT WHEN lv_index = l_deb_status.LAST ;
1642 lv_index := l_deb_status.NEXT(lv_index);
1643 END LOOP;
1644 ELSE
1645
1646 null; --If count is zero, no records.
1647 END IF;
1648 ELSIF x_return_status = 'U' THEN
1649 IF (FND_MSG_PUB.Count_Msg > 0) THEN
1650 FOR i in 1..FND_MSG_PUB.Count_Msg
1651 LOOP
1652 FND_MSG_PUB.Get(p_msg_index => i,
1653 p_encoded => 'F',
1654 p_data => x_msg_data,
1655 p_msg_index_out => l_msg_index_out );
1656
1657 -- logging messages returned by the submit order API.
1658 fnd_file.put_line(FND_FILE.OUTPUT,x_msg_data);
1659
1660 END LOOP;
1661 END IF;
1662
1663
1664 END IF; --for return status. we will do nothing if return_status is other than E or U.
1665
1666 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
1667 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exiting Debrief_Status_Check');
1668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*********************************');
1669
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1673 -- ROLLBACK TO CS_Chg_Auto_Submission_PVT;
1674 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
1675 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Check_Debrief_Status');
1676 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
1677 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
1678 FND_MSG_PUB.add;
1679 FND_MSG_PUB.get(p_encoded => 'F',
1680 p_data=>x_msg_data,
1681 p_msg_index_out=> l_msg_index_out);
1682
1683 -- Recording exceptions in the log file.
1684 FND_FILE.put_line(FND_FILE.LOG,x_msg_data);
1685
1686
1687 END Check_Debrief_Status;
1688
1689 -- end of new procedure for new enh for simplex
1690
1694
1691
1692
1693
1695
1696 END CS_Chg_Auto_Sub_CON_PKG;