1 PACKAGE BODY Cs_Csxsvods_Check_pkg AS
2 /*$Header: cssvcceb.pls 115.3 99/07/16 09:02:30 porting ship $*/
3
4 PROCEDURE Service_Check_Overlap (p_overlap_flag IN OUT VARCHAR2,
5 p_inventory_item_id NUMBER,
6 p_manu_org_id NUMBER,
7 p_customer_product_id NUMBER,
8 p_start_date DATE,
9 p_end_date DATE) IS
10 CURSOR check_overlap IS
11 select 'Y'
12 from cs_cp_services
13 where service_inventory_item_id+0 = p_inventory_item_id
14 And service_manufacturing_org_id= p_manu_org_id
15 And customer_product_id = p_customer_product_id
16 And start_date_active <> end_date_active
17 and ( (p_start_date <= end_date_active
18 and start_date_active <= p_start_date)
19 OR (start_date_active <= p_end_date
20 and p_start_date <= start_date_active)
21 OR (start_date_active >= p_start_date
22 and end_date_active <= p_end_date)
23 OR (start_date_active <= p_start_date
24 and end_date_active >= p_end_date));
25 BEGIN
26 OPEN check_overlap;
27
28 FETCH check_overlap
29 INTO p_overlap_flag;
30
31 IF check_overlap%NOTFOUND THEN
32 p_overlap_flag := 'N';
33 END IF;
34
35 CLOSE check_overlap;
36 END Service_Check_Overlap;
37
38
39
40 /* Procedure to check whether the current service is being processed in Order Entry */
41
42 PROCEDURE service_check_duplicate(p_duplicate_flag IN OUT VARCHAR2,
43 p_inventory_item_id NUMBER,
44 p_customer_product_id NUMBER) IS
45
46 CURSOR check_duplicate IS
47 select 'Y'
48 from so_lines_interface
49 where inventory_item_id+0 = p_inventory_item_id
50 AND customer_product_id = p_customer_product_id;
51
52 BEGIN
53 OPEN check_duplicate;
54
55 FETCH check_duplicate into p_duplicate_flag;
56
57 IF check_duplicate%NOTFOUND THEN
58 p_duplicate_flag := 'N';
59 END IF;
60
61 CLOSE check_duplicate;
62 END Service_Check_Duplicate;
63
64
65 PROCEDURE service_check_duplicate_soline(p_duplicate_flag IN OUT VARCHAR2,
66 p_inventory_item_id NUMBER,
67 p_customer_product_id NUMBER) IS
68
69
70 CURSOR check_duplicate IS
71 select 'Y'
72 from so_lines sol,
73 so_headers soh
74 where inventory_item_id+0 = p_inventory_item_id
75 AND customer_product_id = p_customer_product_id
76 AND nvl(soh.cancelled_flag,'N') = 'N'
77 AND nvl(sol.open_flag,'N') = 'Y';
78 BEGIN
79 OPEN check_duplicate;
80
81 FETCH check_duplicate into p_duplicate_flag;
82
83 IF check_duplicate%NOTFOUND THEN
84 p_duplicate_flag := 'N';
85 END IF;
86
87 CLOSE check_duplicate;
88 END Service_Check_duplicate_soline;
89
90
91
92
93 /** This procedure is used to check whether or not a customer is
94 eligible for service. This is used in the form Order Service,
95 CSXSVODS and in renew service.
96 **/
97
98 PROCEDURE CS_Check_Service_ELigibility (
99 p_cp_eligibility IN OUT VARCHAR2,
100 p_ord_serv_inv_item_id IN NUMBER,
101 p_control_manu_org_id IN NUMBER,
102 p_cp_inventory_item_id IN NUMBER,
103 p_cp_customer_id IN NUMBER,
104 p_cp_revision IN VARCHAR2,
105 p_order_reneW_date IN DATE) IS
106 /* Cursor 1 : Check if a record is defined at all in the service availability
107 setup form*/
108 CURSOR service_available IS
109 SELECT 'x'
110 FROM cs_service_availability serv
111 WHERE serv.service_inventory_item_id = p_ord_serv_inv_item_id
112 AND serv.service_manufacturing_org_id = p_control_manu_org_id ;
113
114 /* Cursor 2 : Check if a record is defined for service availability */
115 CURSOR check_available IS
116 SELECT 'x'
117 FROM cs_service_availability avail
118 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
119 AND avail.service_manufacturing_org_id = p_control_manu_org_id
120 AND NVL(avail.inventory_item_id, p_Cp_inventory_item_id)
121 = p_cp_inventory_item_id
122 AND NVL(avail.customer_id, p_cp_customer_id)
123 = p_cp_customer_id
124 AND (NVL(p_cp_revision,'-999')
125 BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
126 AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
127 AND (p_order_renew_date
128 BETWEEN NVL(avail.start_date_active, p_order_renew_date)
129 AND NVL(avail.end_date_active, p_order_renew_date))
130 AND avail.service_available_flag = 'Y';
131
132 /* Cursor 3 : Check if a record is defined for service restricted */
133 CURSOR check_restricted IS
134 SELECT 'x'
135 FROM cs_service_availability avail
136 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
137 AND avail.service_manufacturing_org_id = p_control_manu_org_id
138 AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
139 = p_cp_inventory_item_id
140 AND NVL(avail.customer_id, p_cp_customer_id)
141 = p_cp_customer_id
142 AND (NVL(p_cp_revision,'-999')
143 BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
144 AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
145 AND (p_order_renew_date
146 BETWEEN NVL(avail.start_date_active, p_order_renew_date)
147 AND NVL(avail.end_date_active, p_order_renew_date))
148 AND avail.service_available_flag = 'N';
149
150 /* Cursor 4 : Check if any record is defined for service restricted */
151 CURSOR check_any_restricted IS
152 SELECT 'x'
153 FROM cs_service_availability avail
154 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
155 AND avail.service_manufacturing_org_id = p_control_manu_org_id
156 AND avail.service_available_flag = 'N';
157
158 /* Cursor 5 : Check if any record is defined for service available */
159 CURSOR check_any_available IS
160 SELECT 'x'
161 FROM cs_service_availability avail
162 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
163 AND avail.service_manufacturing_org_id = p_control_manu_org_id
164 AND avail.service_available_flag = 'Y';
165
166 /* Cursor 6 : Check if a record is defined for service restricted for
167 the same customer/product/revision (no date check) */
168 CURSOR check_same_restricted IS
169 SELECT 'x'
170 FROM cs_service_availability avail
171 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
172 AND avail.service_manufacturing_org_id = p_control_manu_org_id
173 AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
174 = p_cp_inventory_item_id
175 AND NVL(avail.customer_id, p_cp_customer_id)
176 = p_cp_customer_id
177 AND (NVL(p_cp_revision,'-999')
178 BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
179 AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
180 AND avail.service_available_flag = 'N';
181
182
183 /* Cursor 6 : Check if a record is defined for service available for
184 the same customer/product/revision (no date check) */
185 CURSOR check_same_available IS
186 SELECT 'x'
187 FROM cs_service_availability avail
188 WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
189 AND avail.service_manufacturing_org_id = p_control_manu_org_id
190 AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
191 = p_cp_inventory_item_id
192 AND NVL(avail.customer_id, p_cp_customer_id)
193 = p_cp_customer_id
194 AND (NVL(p_cp_revision,'-999')
195 BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
196 AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
197 AND avail.service_available_flag = 'Y';
198
199
200 BEGIN
201
202 OPEN service_available;
203 FETCH service_available
204 INTO p_cp_eligibility;
205 IF service_available%NOTFOUND THEN
206 p_cp_eligibility := 'Y';
207 CLOSE service_available;
208 ELSE
209 CLOSE service_available;
210 OPEN check_restricted;
211 FETCH check_restricted
212 INTO p_cp_eligibility;
213 IF check_restricted%FOUND THEN
214 p_cp_eligibility := 'N';
215 CLOSE check_restricted;
216 ELSE
217 CLOSE check_restricted;
218 OPEN check_available;
219 FETCH check_available
220 INTO p_cp_eligibility;
221 IF check_available%FOUND THEN
222 p_cp_eligibility := 'Y';
223 CLOSE check_available;
224 ELSE
225 CLOSE check_available;
226 OPEN check_any_restricted ;
227 FETCH check_any_restricted
228 INTO p_cp_eligibility;
229 IF check_any_restricted%NOTFOUND THEN
230 p_cp_eligibility := 'N';
231 CLOSE check_any_restricted;
232 ELSE
233 CLOSE check_any_restricted;
234 OPEN check_any_available;
235 FETCH check_any_available
236 INTO p_cp_eligibility;
237 IF check_any_available%NOTFOUND THEN
238 p_cp_eligibility := 'N';
239 CLOSE check_any_available;
240 ELSE
241 CLOSE check_any_available;
242 OPEN check_same_restricted;
243 FETCH check_same_restricted
244 INTO p_cp_eligibility;
245 IF check_same_restricted%NOTFOUND THEN
246 p_cp_eligibility := 'N';
247 CLOSE check_same_restricted;
248 ELSE
249 CLOSE check_same_restricted;
250 OPEN check_same_available;
251 FETCH check_same_available
252 INTO p_cp_eligibility;
253 IF check_same_available%NOTFOUND THEN
254 p_cp_eligibility := 'Y';
255 ELSE
256 p_cp_eligibility := 'N';
257 END IF; /* End check same avai. */
258 CLOSE check_same_available;
259 END IF; /* End check same restr. */
260 END IF; /* End check any avail. */
261 END IF; /* End check any restr. */
262 END IF; /* End check avail */
263 END IF ; /* End check restr. */
264 END IF;
265 END CS_Check_Service_Eligibility;
266
267
268 /*********************************************************************
269 This procedure checks to see whether or not the combination of
270 service, price list and uom code exist in SO_PRICE_LIST_LINES.
271 If the combination does not exist, the client displays a warning.
272 It is called from the header block (orders) of Order Service and
273 from the lines block (CP) for each line selected of Renew Service.
274 **********************************************************************/
275
276 PROCEDURE Check_Price_List(check_value IN OUT VARCHAR2,
277 p_price_list_id IN NUMBER,
278 service_inv_item_id IN NUMBER,
279 uom_code IN VARCHAR2) IS
280
281 CURSOR get_price_list IS
282 SELECT 'Y'
283 FROM so_price_list_lines SOPL
284 WHERE SOPL.price_list_id = p_price_list_id
285 AND SOPL.inventory_item_id = service_inv_item_id
286 AND SOPL.unit_code = uom_code ;
287 BEGIN
288
289 OPEN get_price_list ;
290 FETCH get_price_list
291 INTO check_value ;
292 CLOSE get_price_list ;
293
294
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 Check_value := 'N';
298 WHEN OTHERS THEN
299 Check_value := 'N';
300
301 END Check_Price_List;
302
303 /*********************************************************************
304 This procedure calculates the service duration of a new service or
305 renewed service in terms of the uom code specified. The service
306 duration is first calculated in terms of the number of days (as
307 per the day_uom_code) by taking the diff. between the start and end
308 dates. Thereafter, the number of days is converted to the specified
309 unit. If the value of the duration gets rounded in the conversion
310 process (e.g. 11 hours gets rounded to 24 hours), the rounded flag
311 is set so that the client can display a warning message.
312 **********************************************************************/
313
314
315 PROCEDURE Calculate_Service_duration (Service_duration IN OUT NUMBER,
316 Service_Start_Date IN DATE,
317 Service_End_Date IN DATE,
318 Inventory_Item_ID IN NUMBER,
319 Period_Code IN VARCHAR2,
320 Day_UOM_Code IN VARCHAR2 ,
321 Rounded_Flag IN OUT VARCHAR2,
322 Order_Duration IN NUMBER) IS
323 Duration_Days NUMBER;
324 BEGIN
325 /* If the duration quantity is not a whole number, round it off
326 to the nearest whole number */
327 IF (Service_End_Date IS NOT NULL) AND
328 (Service_Start_Date IS NOT NULL) AND
329 (Order_Duration IS NULL) THEN
330
331 SELECT DECODE((TO_NUMBER(TO_DATE(Service_End_Date,'DD-MON-RR')
332 - TO_DATE(Service_Start_Date,'DD-MON-RR'))
333 ) ,0,1,
334 (TO_NUMBER(TO_DATE(Service_End_Date,'DD-MON-RR')
335 - TO_DATE(Service_Start_Date,'DD-MON-RR')))
336 )
337 INTO Duration_Days
338 FROM sys.dual;
339 Service_duration := inv_convert.inv_um_convert(inventory_item_id,
340 8,
341 from_quantity => Duration_Days,
342 from_unit => day_uom_code,
343 to_unit => period_code,
344 from_name => '',
345 to_name => '');
346
347 ELSE
348 /* First convert the duration to days - day_uom_code - only if the
349 specified unit is not day_uom_code */
350 Duration_Days := Order_Duration ;
351
352
353 IF (period_code <> day_uom_code) THEN
354 Service_duration := inv_convert.inv_um_convert(inventory_item_id,
355 8,
356 from_quantity => Duration_Days,
357 from_unit => period_codE,
358 to_unit => day_uom_code,
359 from_name => '',
360 to_name => '');
361
362
363 /* Now convert the day duration to the uom specified */
364 IF (Service_Duration < 1) THEN
365 Duration_Days := 1 ;
366 ELSE
367 Duration_Days := service_Duration ;
368 END IF;
369 Service_duration := inv_convert.inv_um_convert(inventory_item_id,
370 8,
371 from_quantity => Duration_Days,
372 from_unit => day_uom_code,
373 to_unit => period_code,
374 from_name => '',
375 to_name => '');
376 ELSE
377 Service_duration := Order_duration;
378 END IF;
379 END IF;
380
381 /* If the diff. between start date and end date is not a whole
382 number, set the rounded flag so that the user can receive a
383 warning. Also, if the start and end dates are the same,
384 set the rounded flag to give the user a warning. */
385
386 IF (Order_Duration <> Service_Duration) AND
387 (day_uom_code <> period_code) THEN
388 rounded_flag := 'Y';
389 END IF;
390 --DBMS_OUTPUT.PUT_LINE('FLAG : ' || rounded_flag);
391 END Calculate_Service_Duration ;
392
393 PROCEDURE create_cust_interact_new_ord(control_user_id IN NUMBER,
394 parent_interaction_id IN VARCHAR2,
395 cp_last_update_login IN NUMBER,
396 cp_bill_to_contact_id IN NUMBER,
397 order_customer_id IN NUMBER,
398 return_status OUT VARCHAR2,
399 return_msg OUT VARCHAR2) IS
400 l_ret_status VARCHAR2(1);
401 l_msg_count NUMBER;
402 l_msg_data VARCHAR2(1000);
403 l_interaction_id NUMBER;
404 l_employee_id NUMBER;
405 -- l_return_error VARCHAR2;
406 -- l_return_unexp_error VARCHAR2;
407
408
409 BEGIN
410
411 SELECT employee_id
412 INTO l_employee_id
413 FROM FND_USER
414 WHERE user_id = control_user_id;
415
416 -- l_return_error := cs_response_center_pkg.GET_ERROR_CONSTANT('G_RET_STS_ERROR');
417 -- l_return_unexp_error := cs_response_center_pkg.GET_ERROR_CONSTANT('G_RET_STS_UNEXP_ERROR');
418
419 return_status := NULL;
420 return_msg := NULL;
421 IF order_customer_id IS NOT NULL THEN
422
423 CS_Interaction_PVT.Create_Interaction
424 (p_api_version => 1.0,
425 p_init_msg_list => FND_API.G_TRUE,
426 p_commit => FND_API.G_FALSE,
427 p_validation_level => CS_INTERACTION_PVT.G_VALID_LEVEL_INT,
428 x_return_status => l_ret_status,
429 x_msg_count => l_msg_count,
430 x_msg_data => l_msg_data,
431 p_resp_appl_id => NULL,
432 p_resp_id => NULL,
433 p_user_id => control_user_id,
434 p_login_id => cp_last_update_login,
435 p_org_id => FND_PROFILE.Value('ORG_ID'),
436 p_customer_id => order_customer_id,
437 p_contact_id => cp_bill_to_contact_id,
438 p_contact_lastname => NULL,
439 p_contact_firstname => NULL,
440 p_phone_area_code => NULL,
441 p_phone_number => NULL,
442 p_phone_extension => NULL,
443 p_fax_area_code => NULL,
444 p_fax_number => NULL,
445 p_email_address => NULL,
446 p_interaction_type_code => 'SRV_ORD',
447 p_interaction_category_code => 'CS',
448 p_interaction_method_code => 'SYSTEM',
449 p_interaction_date => SYSDATE,
450 p_interaction_document_code => NULL,
451 p_source_document_id => NULL,
452 p_source_document_name => NULL,
453 p_reference_form => NULL,
454 p_source_document_status => NULL,
455 p_employee_id => l_employee_id,
456 p_public_flag => NULL,
457 p_follow_up_action => NULL,
458 p_notes => NULL,
459 p_parent_interaction_id => parent_interaction_id,
460 p_attribute1 => NULL,
461 p_attribute2 => NULL,
462 p_attribute3 => NULL,
463 p_attribute4 => NULL,
464 p_attribute5 => NULL,
465 p_attribute6 => NULL,
466 p_attribute7 => NULL,
467 p_attribute8 => NULL,
468 p_attribute9 => NULL,
469 p_attribute10 => NULL,
470 p_attribute11 => NULL,
471 p_attribute12 => NULL,
472 p_attribute13 => NULL,
473 p_attribute14 => NULL,
474 p_attribute15 => NULL,
475 p_attribute_category => NULL,
476 x_interaction_id => l_interaction_id);
477 return_status := l_ret_status;
478 return_msg := l_msg_data;
479 END IF;
480
481 IF (l_ret_status = FND_API.G_RET_STS_ERROR OR
482 l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
483 -- 1 meaning error, 0 meaning OK
484 return_status := '1';
485 END IF;
486
487
488 END create_cust_interact_new_ord;
489
490
491 PROCEDURE create_cust_interact_renew(control_user_id IN NUMBER,
492 cp_cp_service_id IN NUMBER,
493 parent_interaction_id IN VARCHAR2,
494 cp_last_update_login IN NUMBER,
495 cp_bill_to_contact_id IN NUMBER,
496 cp_customer_id IN NUMBER,
497 return_status OUT VARCHAR2,
498 return_msg OUT VARCHAR2) IS
499
500 l_ret_status VARCHAR2(1);
501 l_msg_count NUMBER;
502 l_msg_data VARCHAR2(1000);
503 l_interaction_id NUMBER;
504 l_customer_id NUMBER;
505 l_employee_id NUMBER;
506 -- l_return_error VARCHAR2;
507 -- l_return_unexp_error VARCHAR2;
508
509
510 BEGIN
511
512 SELECT employee_id
513 INTO l_employee_id
514 FROM FND_USER
515 WHERE user_id = control_user_id;
516
517
518
519 -- l_return_error := cs_response_center_pkg.GET_ERROR_CONSTANT('G_RET_STS_ERROR');
520 -- l_return_unexp_error := cs_response_center_pkg.GET_ERROR_CONSTANT('G_RET_STS_UNEXP_ERROR');
521
522 return_status := NULL;
523 return_msg := NULL;
524 IF cp_customer_id IS NOT NULL THEN
525
526 CS_Interaction_PVT.Create_Interaction
527 (p_api_version => 1.0,
528 p_init_msg_list => FND_API.G_TRUE,
529 p_commit => FND_API.G_FALSE,
530 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
531 x_return_status => l_ret_status,
532 x_msg_count => l_msg_count,
533 x_msg_data => l_msg_data,
534 p_resp_appl_id => NULL,
535 p_resp_id => NULL,
536 p_user_id => control_user_id,
537 p_login_id => cp_last_update_login,
538 p_org_id => FND_PROFILE.Value('ORG_ID'),
539 p_customer_id => cp_customer_id,
540 p_contact_id => cp_bill_to_contact_id,
541 p_contact_lastname => NULL,
542 p_contact_firstname => NULL,
543 p_phone_area_code => NULL,
544 p_phone_number => NULL,
545 p_phone_extension => NULL,
546 p_fax_area_code => NULL,
547 p_fax_number => NULL,
548 p_email_address => NULL,
549 p_interaction_type_code => 'SRV_REN',
550 p_interaction_category_code => 'CS',
551 p_interaction_method_code => 'SYSTEM',
552 p_interaction_date => SYSDATE,
553 p_interaction_document_code => NULL,
554 p_source_document_id => NULL,
555 p_source_document_name => NULL,
556 p_reference_form => NULL,
557 p_source_document_status => NULL,
558 p_employee_id => l_employee_id,
559 p_public_flag => NULL,
560 p_follow_up_action => NULL,
561 p_notes => NULL,
562 p_parent_interaction_id => parent_interaction_id,
563 p_attribute1 => NULL,
564 p_attribute2 => NULL,
565 p_attribute3 => NULL,
566 p_attribute4 => NULL,
567 p_attribute5 => NULL,
568 p_attribute6 => NULL,
569 p_attribute7 => NULL,
570 p_attribute8 => NULL,
571 p_attribute9 => NULL,
572 p_attribute10 => NULL,
573 p_attribute11 => NULL,
574 p_attribute12 => NULL,
575 p_attribute13 => NULL,
576 p_attribute14 => NULL,
577 p_attribute15 => NULL,
578 p_attribute_category => NULL,
579 x_interaction_id => l_interaction_id);
580 return_status := l_ret_status;
581 return_msg := l_msg_data;
582 END IF;
583
584 IF (l_ret_status = FND_API.G_RET_STS_ERROR OR
585 l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
586 -- 1 meaning error, 0 meaning OK
587 return_status := '1';
588 END IF;
589
590
591
592 END create_cust_interact_renew;
593
594
595 END Cs_Csxsvods_Check_Pkg;
[Home] [Help]