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