[Home] [Help]
PACKAGE BODY: APPS.RLM_SETUP_TERMS_SV
Source
1 PACKAGE BODY RLM_SETUP_TERMS_SV AS
2 /* $Header: RLMSETTB.pls 120.3 2011/05/24 11:50:21 sunilku ship $ */
3 /*======================== rlm_setup_terms_sv ==============================*/
4
5 --
6 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
7 --
8
9 /*============================================================================
10
11 PROCEDURE NAME: get_setup_terms
12
13 =============================================================================*/
14
15 PROCEDURE get_setup_terms (
16 x_ship_from_org_id IN NUMBER,
17 x_customer_id IN NUMBER,
18 x_ship_to_address_id IN NUMBER,
19 x_customer_item_id IN NUMBER,
20 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
21 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
22 x_return_message OUT NOCOPY VARCHAR2,
23 x_return_status OUT NOCOPY BOOLEAN)
24 IS
25 -- Exception to indicate that mandatory parameters can not be blank
26 -- regardless of terms_definition_level
27 e_null_mandatory EXCEPTION;
28
29 -- Exception to indicate that ship_to_address_id can not be blank
30 -- when terms_definition_level is ADDRESS
31 e_null_address EXCEPTION;
32
33 -- Exception to indicate that customer_item_id can not be blank
34 -- when terms_definition_level is CUSTOMER_ITEM_ID
35 e_null_customer_item EXCEPTION;
36
37 -- Exception to indicate that the input terms_definition_level
38 -- is invalid (other than CUSTOMER, ADDRESS, and CUSTOMER_ITEM)
39 e_invalid_terms_level EXCEPTION;
40
41 BEGIN
42 --
43 IF (l_debug <> -1) THEN
44 rlm_core_sv.dpush(C_SDEBUG, 'get_setup_terms');
45 rlm_core_sv.dlog(C_DEBUG, 'x_ship_from_org_id', x_ship_from_org_id);
46 rlm_core_sv.dlog(C_DEBUG, 'x_customer_id', x_customer_id);
47 rlm_core_sv.dlog(C_DEBUG, 'x_ship_to_address_id', x_ship_to_address_id);
48 rlm_core_sv.dlog(C_DEBUG, 'x_customer_item_id', x_customer_item_id);
49 END IF;
50 --
51 -- Mandatory parameters can not be null
52 -- global_atp: ship_from_org_id can be null
53 IF x_customer_id is NULL THEN
54 --
55 RAISE e_null_mandatory;
56 --
57 END IF;
58 --
59 IF (x_terms_definition_level is NULL) THEN
60 --
61 IF x_customer_item_id is NULL THEN
62 --
63 IF x_ship_to_address_id is NULL THEN
64 --
65 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
66 x_customer_id,
67 x_terms_definition_level,
68 x_terms_rec,
69 x_return_message,
70 x_return_status);
71 --
72 ELSE /*x_ship_to_address_id is NOT NULL*/
73 --
74 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
75 x_customer_id,
76 x_ship_to_address_id,
77 x_customer_item_id,
78 x_terms_definition_level,
79 x_terms_rec,
80 x_return_message,
81 x_return_status);
82 END IF;
83 ELSE /* x_customer_item_id is NOT NULL */
84 --
85 RLM_TPA_SV.populate_record_item(x_ship_from_org_id,
86 x_customer_id,
87 x_ship_to_address_id,
88 x_customer_item_id,
89 x_terms_definition_level,
90 x_terms_rec,
91 x_return_message,
92 x_return_status);
93 --
94 END IF;
95 /* Terms definition level, if known, can be supplied by calling
96 program explicitly at three levels */
97 ELSIF x_terms_definition_level = 'CUSTOMER' THEN
98 --
99 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
100 x_customer_id,
101 x_terms_definition_level,
102 x_terms_rec,
103 x_return_message,
104 x_return_status);
105 ELSIF x_terms_definition_level = 'ADDRESS' THEN
106 --
107 IF x_ship_to_address_id is NULL THEN
108 --
109 RAISE e_null_address;
110 --
111 END IF;
112 --
113 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
114 x_customer_id,
115 x_ship_to_address_id,
116 x_customer_item_id,
117 x_terms_definition_level,
118 x_terms_rec,
119 x_return_message,
120 x_return_status);
121 --
122 ELSIF x_terms_definition_level = 'ADDRESS_ITEM' THEN
123 --
124 IF x_customer_item_id is NULL THEN
125 --
126 RAISE e_null_customer_item;
127 --
128 END IF;
129 --
130 IF x_ship_to_address_id is NULL THEN
131 --
132 RAISE e_null_address;
133 --
134 END IF;
135 --
136 RLM_TPA_SV.populate_record_item(x_ship_from_org_id,
137 x_customer_id,
138 x_ship_to_address_id,
139 x_customer_item_id,
140 x_terms_definition_level,
141 x_terms_rec,
142 x_return_message,
143 x_return_status);
144 --
145 ELSIF x_terms_definition_level = 'CUSTOMER_ITEM' THEN
146 --
147 IF x_customer_item_id is NULL THEN
148 --
149 RAISE e_null_customer_item;
150 --
151 END IF;
152 --
153 RLM_TPA_SV.populate_record_cust_item(x_ship_from_org_id,
154 x_customer_id,
155 x_ship_to_address_id,
156 x_customer_item_id,
157 x_terms_definition_level,
158 x_terms_rec,
159 x_return_message,
160 x_return_status);
161 --
162 ELSE
163 --
164 RAISE e_invalid_terms_level;
165 --
166 END IF;
167 --
168 IF (l_debug <> -1) THEN
169 rlm_core_sv.dpop(C_SDEBUG);
170 END IF;
171 --
172 EXCEPTION
173 WHEN e_null_mandatory THEN
174 --
175 x_terms_rec.msg_name := 'RLM_SETUP_NULL_MANDATORY';
176 rlm_message_sv.get_msg_text(x_terms_rec.msg_name,
177 x_return_message);
178 --
179 x_return_status := FALSE;
180 --
181 IF (l_debug <> -1) THEN
182 rlm_core_sv.dpop(C_SDEBUG, 'Mandatory parameters can not be blank');
183 END IF;
184 --
185 WHEN e_null_address THEN
186 --
187 x_terms_rec.msg_name := 'RLM_SETUP_NULL_ADDRESS';
188 rlm_message_sv.get_msg_text(x_message_name => x_terms_rec.msg_name,
189 x_text => x_return_message,
190 x_token1 => 'SHIP_FROM_ORG_ID',
191 x_value1 => x_ship_from_org_id,
192 x_token2 => 'CUSTOMER_ID',
193 x_value2 => x_customer_id);
194 --
195 x_return_status := FALSE;
196 --
197 IF (l_debug <> -1) THEN
198 rlm_core_sv.dpop(C_SDEBUG, 'Ship_to_address_id is required when terms definition level is ADDRESS');
199 END IF;
200 --
201 WHEN e_null_customer_item THEN
202 --
203 x_terms_rec.msg_name := 'RLM_SETUP_NULL_CUSTOMER_ITEM';
204 rlm_message_sv.get_msg_text(
205 x_message_name => x_terms_rec.msg_name,
206 x_text => x_return_message);
207 --
208 x_return_status := FALSE;
209 --
210 IF (l_debug <> -1) THEN
211 rlm_core_sv.dpop(C_SDEBUG, 'Customer_item_id is required when terms definition level is CUSTOMER_ITEM');
212 END IF;
213 --
214 WHEN e_invalid_terms_level THEN
215 --
216 x_terms_rec.msg_name := 'RLM_SETUP_INVALID_TERMS_LEVEL';
217 rlm_message_sv.get_msg_text(
218 x_message_name => x_terms_rec.msg_name,
219 x_text => x_return_message,
220 x_token1 => 'TERMS_DEFINITION_LEVEL',
221 x_value1 => x_terms_definition_level);
222 --
223 x_return_status := FALSE;
224 --
225 IF (l_debug <> -1) THEN
226 rlm_core_sv.dpop(C_SDEBUG, 'The terms_definition_level has to be one of the following: CUSTOMER, ADDRESS, CUSTOMER_ITEM');
227 END IF;
228 --
229 WHEN OTHERS THEN
230 --
231 x_return_status := FALSE;
232 --
233 IF (l_debug <> -1) THEN
234 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
235 rlm_core_sv.dpop(C_SDEBUG);
236 END IF;
237 --
238 raise;
239 --
240 END get_setup_terms;
241
242 /*=============================================================================
243
244 PROCEDURE NAME: populate_record_cust
245
246 =============================================================================*/
247
248 PROCEDURE populate_record_cust (
249 x_ship_from_org_id IN NUMBER,
250 x_customer_id IN NUMBER,
251 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
252 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
253 x_return_message IN OUT NOCOPY VARCHAR2,
254 x_return_status OUT NOCOPY BOOLEAN)
255 IS
256 v_ship_from_org_id NUMBER DEFAULT -1;
257 e_inactive_record EXCEPTION;
258 e_no_default EXCEPTION;
259 BEGIN
260 --
261 IF (l_debug <> -1) THEN
262 rlm_core_sv.dpush(C_SDEBUG, 'populate_record_cust');
263 END IF;
264 --
265 v_ship_from_org_id := x_ship_from_org_id;
266 --
267 -- Before selecting the terms, check if the ship_from_org_id is null */
268 -- global_atp
269 IF x_ship_from_org_id IS NULL THEN
270 --
271 BEGIN
272
273 SELECT ship_from_org_id
274 INTO v_ship_from_org_id
275 FROM rlm_cust_shipto_terms
276 WHERE customer_id = x_customer_id
277 AND address_id IS NULL
278 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
279
280 -- Proceed
281 IF (l_debug <> -1) THEN
282 rlm_core_sv.dlog(C_DEBUG, 'v_ship_from_org_id', v_ship_from_org_id );
283 END IF;
284
285 EXCEPTION
286 WHEN TOO_MANY_ROWS THEN
287 -- Get the default
288 BEGIN
289 SELECT ship_from_org_id
290 INTO v_ship_from_org_id
291 FROM rlm_cust_shipto_terms
292 WHERE customer_id = x_customer_id
293 AND address_id IS NULL
294 AND NVL(default_ship_from,'N') = 'Y'
295 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
296
297 -- Proceed
298 IF (l_debug <> -1) THEN
299 rlm_core_sv.dlog(C_DEBUG, 'Default v_ship_from_org_id', v_ship_from_org_id );
300 END IF;
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 RAISE e_no_default;
305 END;
306
307 WHEN NO_DATA_FOUND THEN
308 RAISE;
309
310 WHEN e_no_default THEN
311 RAISE;
312
313 WHEN OTHERS THEN
314 RAISE;
315
316 END;
317 --
318 END IF;
319
320 SELECT cust_shipto_terms_id,
321 customer_id,
322 cum_control_code,
323 cum_org_level_code,
324 cum_shipment_rule_code,
325 cum_yesterd_time_cutoff,
326 cust_assign_supplier_cd,
327 customer_rcv_calendar_cd,
328 supplier_shp_calendar_cd,
329 unship_firm_cutoff_days,
330 unshipped_firm_disp_cd,
331 inactive_date,
332 critical_attribute_key,
333 schedule_hierarchy_code,
334 comments,
335 intransit_time,
336 time_uom_code,
337 ship_from_org_id,
338 address_id,
339 header_id,
340 agreement_id,
341 agreement_name,
342 future_agreement_id,
343 future_agreement_name,
344 round_to_std_pack_flag,
345 ship_delivery_rule_name,
346 ship_method,
347 std_pack_qty,
348 price_list_id,
349 use_edi_sdp_code_flag,
350 match_across_key,
351 match_within_key,
352 pln_firm_day_to,
353 pln_firm_day_from,
354 pln_forecast_day_from,
355 pln_forecast_day_to,
356 pln_frozen_day_to,
357 pln_frozen_day_from,
358 seq_firm_day_from,
359 seq_firm_day_to,
360 seq_forecast_day_to,
361 seq_forecast_day_from,
362 seq_frozen_day_from,
363 seq_frozen_day_to,
364 shp_firm_day_from,
365 shp_firm_day_to,
366 shp_frozen_day_from,
367 shp_frozen_day_to,
368 shp_forecast_day_from,
369 shp_forecast_day_to,
370 pln_mrp_forecast_day_from,
371 pln_mrp_forecast_day_to,
372 shp_mrp_forecast_day_from,
373 shp_mrp_forecast_day_to,
374 seq_mrp_forecast_day_from,
375 seq_mrp_forecast_day_to,
376 demand_tolerance_above,
377 demand_tolerance_below,
378 customer_contact_id,
379 freight_code,
380 supplier_contact_id,
381 attribute_category,
382 tp_attribute_category,
383 attribute1,
384 attribute2,
385 attribute4,
386 attribute5,
387 attribute6,
388 attribute7,
389 attribute8,
390 attribute9,
391 attribute10,
392 attribute11,
393 attribute12,
394 attribute13,
395 attribute14,
396 attribute15,
397 tp_attribute1,
398 tp_attribute2,
399 tp_attribute3,
400 tp_attribute4,
401 tp_attribute5,
402 tp_attribute6,
403 tp_attribute7,
404 tp_attribute8,
405 tp_attribute9,
406 tp_attribute10,
407 tp_attribute11,
408 tp_attribute12,
409 tp_attribute13,
410 tp_attribute14,
411 tp_attribute15,
412 intransit_calc_basis,
413 pln_frozen_flag,
414 shp_frozen_flag,
415 seq_frozen_flag,
416 issue_warning_drop_parts_flag,
417 blanket_number,
418 release_rule,
419 release_time_frame,
420 release_time_frame_uom,
421 exclude_non_workdays_flag,
422 disable_create_cum_key_flag
423 INTO x_terms_rec.cust_shipto_terms_id,
424 x_terms_rec.customer_id,
425 x_terms_rec.cum_control_code,
426 x_terms_rec.cum_org_level_code,
427 x_terms_rec.cum_shipment_rule_code,
428 x_terms_rec.cum_yesterd_time_cutoff,
429 x_terms_rec.cust_assign_supplier_cd,
430 x_terms_rec.customer_rcv_calendar_cd,
431 x_terms_rec.supplier_shp_calendar_cd,
432 x_terms_rec.unship_firm_cutoff_days,
433 x_terms_rec.unshipped_firm_disp_cd,
434 x_terms_rec.inactive_date,
435 x_terms_rec.critical_attribute_key,
436 x_terms_rec.schedule_hierarchy_code,
437 x_terms_rec.comments,
438 x_terms_rec.intransit_time,
439 x_terms_rec.time_uom_code,
440 x_terms_rec.ship_from_org_id,
441 x_terms_rec.address_id,
442 x_terms_rec.header_id,
443 x_terms_rec.agreement_id,
444 x_terms_rec.agreement_name,
445 x_terms_rec.future_agreement_id,
446 x_terms_rec.future_agreement_name,
447 x_terms_rec.round_to_std_pack_flag,
448 x_terms_rec.ship_delivery_rule_name,
449 x_terms_rec.ship_method,
450 x_terms_rec.std_pack_qty,
451 x_terms_rec.price_list_id,
452 x_terms_rec.use_edi_sdp_code_flag,
453 x_terms_rec.match_across_key,
454 x_terms_rec.match_within_key,
455 x_terms_rec.pln_firm_day_to,
456 x_terms_rec.pln_firm_day_from,
457 x_terms_rec.pln_forecast_day_from,
458 x_terms_rec.pln_forecast_day_to,
459 x_terms_rec.pln_frozen_day_to,
460 x_terms_rec.pln_frozen_day_from,
461 x_terms_rec.seq_firm_day_from,
462 x_terms_rec.seq_firm_day_to,
463 x_terms_rec.seq_forecast_day_to,
464 x_terms_rec.seq_forecast_day_from,
465 x_terms_rec.seq_frozen_day_from,
466 x_terms_rec.seq_frozen_day_to,
467 x_terms_rec.shp_firm_day_from,
468 x_terms_rec.shp_firm_day_to,
469 x_terms_rec.shp_frozen_day_from,
470 x_terms_rec.shp_frozen_day_to,
471 x_terms_rec.shp_forecast_day_from,
472 x_terms_rec.shp_forecast_day_to,
473 x_terms_rec.pln_mrp_forecast_day_from,
474 x_terms_rec.pln_mrp_forecast_day_to,
475 x_terms_rec.shp_mrp_forecast_day_from,
476 x_terms_rec.shp_mrp_forecast_day_to,
477 x_terms_rec.seq_mrp_forecast_day_from,
478 x_terms_rec.seq_mrp_forecast_day_to,
479 x_terms_rec.demand_tolerance_above,
480 x_terms_rec.demand_tolerance_below,
481 x_terms_rec.customer_contact_id,
482 x_terms_rec.freight_code,
483 x_terms_rec.supplier_contact_id,
484 x_terms_rec.attribute_category,
485 x_terms_rec.tp_attribute_category,
486 x_terms_rec.attribute1,
487 x_terms_rec.attribute2,
488 x_terms_rec.attribute4,
489 x_terms_rec.attribute5,
490 x_terms_rec.attribute6,
491 x_terms_rec.attribute7,
492 x_terms_rec.attribute8,
493 x_terms_rec.attribute9,
494 x_terms_rec.attribute10,
495 x_terms_rec.attribute11,
496 x_terms_rec.attribute12,
497 x_terms_rec.attribute13,
498 x_terms_rec.attribute14,
499 x_terms_rec.attribute15,
500 x_terms_rec.tp_attribute1,
501 x_terms_rec.tp_attribute2,
502 x_terms_rec.tp_attribute3,
503 x_terms_rec.tp_attribute4,
504 x_terms_rec.tp_attribute5,
505 x_terms_rec.tp_attribute6,
506 x_terms_rec.tp_attribute7,
507 x_terms_rec.tp_attribute8,
508 x_terms_rec.tp_attribute9,
509 x_terms_rec.tp_attribute10,
510 x_terms_rec.tp_attribute11,
511 x_terms_rec.tp_attribute12,
512 x_terms_rec.tp_attribute13,
513 x_terms_rec.tp_attribute14,
514 x_terms_rec.tp_attribute15,
515 x_terms_rec.intransit_calc_basis,
516 x_terms_rec.pln_frozen_flag,
517 x_terms_rec.shp_frozen_flag,
518 x_terms_rec.seq_frozen_flag,
519 x_terms_rec.issue_warning_drop_parts_flag,
520 x_terms_rec.blanket_number,
521 x_terms_rec.release_rule,
522 x_terms_rec.release_time_frame,
523 x_terms_rec.release_time_frame_uom,
524 x_terms_rec.exclude_non_workdays_flag,
525 x_terms_rec.disable_create_cum_key_flag
526 FROM RLM_CUST_SHIPTO_TERMS
527 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
528 AND CUSTOMER_ID = x_customer_id
529 AND ADDRESS_ID is NULL;
530
531 /* By default, inactive_date is NULL */
532 --
533 IF x_terms_rec.inactive_date is NOT NULL THEN
534 --
535 IF x_terms_rec.inactive_date <= nvl(rlm_dp_sv.g_dsp_start_time,sysdate) THEN --Bugfix 10053830
536 --
537 raise e_inactive_record;
538 --
539 END IF;
540 --
541 END IF;
542 --
543 /* By default, match_within_key is ABCDEFG */
544 --
545 IF x_terms_rec.match_within_key is NULL THEN
546 --
547 x_terms_rec.match_within_key := rlm_core_sv.get_default_key;
548 --
549 END IF;
550 --
551 /* By default, match_across_key is ABCDEFG */
552 --
553 IF x_terms_rec.match_across_key is NULL THEN
554 --
555 x_terms_rec.match_across_key := rlm_core_sv.get_default_key;
556 --
557 END IF;
558 --
559 x_terms_rec.calc_cum_flag := 'Y';
560 x_terms_definition_level := 'CUSTOMER';
561 x_return_status := TRUE;
562 --
563 IF (l_debug <> -1) THEN
564 rlm_core_sv.dpop(C_SDEBUG);
565 END IF;
566 --
567 EXCEPTION
568 --
569 WHEN TOO_MANY_ROWS THEN
570 --
571 x_terms_rec.msg_name := 'RLM_SETUP_CUST_MULTIPLE_ROWS';
572 rlm_message_sv.get_msg_text(
573 x_message_name => x_terms_rec.msg_name,
574 x_text => x_return_message,
575 x_token1 => 'CUSTOMER',
576 x_value1 => rlm_core_sv.get_customer_name(x_Customer_id));
577 --
578 x_terms_definition_level := NULL;
579 --
580 x_return_status := FALSE;
581 --
582 IF (l_debug <> -1) THEN
583 rlm_core_sv.dlog(C_DEBUG, 'Too Many Rows ');
584 rlm_core_sv.dpop(C_SDEBUG, 'There are more than one record of RLM Setup Terms at the CUSTOMER level');
585 END IF;
586 --
587 WHEN e_no_default THEN
588 --
589 x_terms_rec.msg_name := 'RLM_SETUP_CUST_NO_DEFAULT';
590 rlm_message_sv.get_msg_text(
591 x_message_name => x_terms_rec.msg_name,
592 x_text => x_return_message,
593 x_token1 => 'CUST',
594 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id));
595
596 --
597 x_terms_definition_level := NULL;
598 --
599 x_return_status := FALSE;
600 --
601 IF (l_debug <> -1) THEN
602 rlm_core_sv.dlog(C_DEBUG, 'No Default');
603 rlm_core_sv.dpop(C_SDEBUG);
604 END IF;
605
606 WHEN e_inactive_record THEN
607 --
608 x_terms_rec.msg_name := 'RLM_SETUP_CUST_INACTIVE_RECORD';
609 --
610 x_terms_definition_level := NULL;
611 --
612 x_return_status := FALSE;
613 --
614 IF (l_debug <> -1) THEN
615 rlm_core_sv.dpop(C_SDEBUG, 'The RLM Setup Terms record at the CUSTOMER level has been inactivated');
616 END IF;
617 --
618 WHEN NO_DATA_FOUND THEN
619 --
620 x_terms_rec.msg_name := 'RLM_SETUP_NO_DATA_FOUND';
621 rlm_message_sv.get_msg_text(
622 x_message_name => x_terms_rec.msg_name,
623 x_text => x_return_message,
624 x_token1 => 'SHIPFROM',
625 x_value1 => rlm_core_sv.get_ship_from(x_Ship_from_org_id),
626 x_token2 => 'CUSTOMER',
627 x_value2 => rlm_core_sv.get_customer_name(x_Customer_id));
628 --
629 x_terms_definition_level := NULL;
630 --
631 x_return_status := FALSE;
632 --
633 IF (l_debug <> -1) THEN
634 rlm_core_sv.dlog(C_DEBUG, 'No data found');
635 rlm_core_sv.dpop(C_SDEBUG, 'No data found');
636 END IF;
637 --
638 WHEN OTHERS THEN
639 --
640 x_return_status := FALSE;
641 --
642 IF (l_debug <> -1) THEN
643 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
644 rlm_core_sv.dpop(C_SDEBUG);
645 END IF;
646 --
647 raise;
648 --
649 END populate_record_cust;
650
651
652 /*=============================================================================
653
654 PROCEDURE NAME: populate_record_add
655
656 =============================================================================*/
657
658 PROCEDURE populate_record_add (
659 x_ship_from_org_id IN NUMBER,
660 x_customer_id IN NUMBER,
661 x_ship_to_address_id IN NUMBER,
662 x_customer_item_id IN NUMBER,
663 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
664 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
665 x_return_message IN OUT NOCOPY VARCHAR2,
666 x_return_status OUT NOCOPY BOOLEAN)
667 IS
668 --
669 v_ship_from_org_id NUMBER DEFAULT -1;
670 e_multiple_rows EXCEPTION;
671 e_inactive_record EXCEPTION;
672 e_no_default EXCEPTION;
673 --
674 -- 4129188
675 --
676 v_match_within_key VARCHAR2(240);
677 v_match_across_key VARCHAR2(240);
678 --
679 CURSOR c_optional_match_cust(p_shipFromOrgId NUMBER,
680 p_CustomerId NUMBER) IS
681 SELECT match_within_key, match_across_key
682 FROM rlm_cust_shipto_terms
683 WHERE ship_from_org_id = p_shipFromOrgId
684 AND customer_id = p_CustomerId
685 AND address_id IS NULL;
686 --
687 BEGIN
688 --
689 IF (l_debug <> -1) THEN
690 rlm_core_sv.dpush(C_SDEBUG, 'populate_record_add');
691 END IF;
692
693 v_ship_from_org_id := x_ship_from_org_id;
694
695 -- Before selecting the terms, check if the ship_from_org_id is null */
696 -- global_atp
697 IF x_ship_from_org_id IS NULL THEN
698 --
699 BEGIN
700
701 SELECT ship_from_org_id
702 INTO v_ship_from_org_id
703 FROM rlm_cust_shipto_terms
704 WHERE customer_id = x_customer_id
705 AND address_id = x_ship_to_address_id
706 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
707
708 -- Proceed
709 IF (l_debug <> -1) THEN
710 rlm_core_sv.dlog(C_DEBUG, 'v_ship_from_org_id', v_ship_from_org_id);
711 END IF;
712
713 EXCEPTION
714 WHEN TOO_MANY_ROWS THEN
715 -- Get the default
716 BEGIN
717 SELECT ship_from_org_id
718 INTO v_ship_from_org_id
719 FROM rlm_cust_shipto_terms
720 WHERE customer_id = x_customer_id
721 AND address_id = x_ship_to_address_id
722 AND NVL(default_ship_from,'N') = 'Y'
723 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
724
725 -- Proceed
726 IF (l_debug <> -1) THEN
727 rlm_core_sv.dlog(C_DEBUG, 'Default v_ship_from_org_id', v_ship_from_org_id);
728 END IF;
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 RAISE e_no_default;
733 END;
734
735 WHEN NO_DATA_FOUND THEN
736 RAISE;
737
738 WHEN e_no_default THEN
739 RAISE;
740
741 WHEN OTHERS THEN
742 RAISE;
743
744 END;
745 --
746 END IF;
747
748 SELECT cust_shipto_terms_id,
749 customer_id,
750 cum_control_code,
751 cum_org_level_code,
752 cum_shipment_rule_code,
753 cum_yesterd_time_cutoff,
754 cust_assign_supplier_cd,
755 customer_rcv_calendar_cd,
756 supplier_shp_calendar_cd,
757 unship_firm_cutoff_days,
758 unshipped_firm_disp_cd,
759 inactive_date,
760 critical_attribute_key,
761 schedule_hierarchy_code,
762 comments,
763 intransit_time,
764 time_uom_code,
765 ship_from_org_id,
766 address_id,
767 header_id,
768 agreement_id,
769 agreement_name,
770 future_agreement_id,
771 future_agreement_name,
772 round_to_std_pack_flag,
773 ship_delivery_rule_name,
774 ship_method,
775 std_pack_qty,
776 price_list_id,
777 use_edi_sdp_code_flag,
778 match_across_key,
779 match_within_key,
780 pln_firm_day_to,
781 pln_firm_day_from,
782 pln_forecast_day_from,
783 pln_forecast_day_to,
784 pln_frozen_day_to,
785 pln_frozen_day_from,
786 seq_firm_day_from,
787 seq_firm_day_to,
788 seq_forecast_day_to,
789 seq_forecast_day_from,
790 seq_frozen_day_from,
791 seq_frozen_day_to,
792 shp_firm_day_from,
793 shp_firm_day_to,
794 shp_frozen_day_from,
795 shp_frozen_day_to,
796 shp_forecast_day_from,
797 shp_forecast_day_to,
798 pln_mrp_forecast_day_from,
799 pln_mrp_forecast_day_to,
800 shp_mrp_forecast_day_from,
801 shp_mrp_forecast_day_to,
802 seq_mrp_forecast_day_from,
803 seq_mrp_forecast_day_to,
804 demand_tolerance_above,
805 demand_tolerance_below,
806 customer_contact_id,
807 freight_code,
808 supplier_contact_id,
809 attribute_category,
810 tp_attribute_category,
811 attribute1,
812 attribute2,
813 attribute4,
814 attribute5,
815 attribute6,
816 attribute7,
817 attribute8,
818 attribute9,
819 attribute10,
820 attribute11,
821 attribute12,
822 attribute13,
823 attribute14,
824 attribute15,
825 tp_attribute1,
826 tp_attribute2,
827 tp_attribute3,
828 tp_attribute4,
829 tp_attribute5,
830 tp_attribute6,
831 tp_attribute7,
832 tp_attribute8,
833 tp_attribute9,
834 tp_attribute10,
835 tp_attribute11,
836 tp_attribute12,
837 tp_attribute13,
838 tp_attribute14,
839 tp_attribute15,
840 intransit_calc_basis,
841 pln_frozen_flag,
842 shp_frozen_flag,
843 seq_frozen_flag,
844 issue_warning_drop_parts_flag,
845 blanket_number,
846 release_rule,
847 release_time_frame,
848 release_time_frame_uom,
849 exclude_non_workdays_flag,
850 disable_create_cum_key_flag
851 INTO x_terms_rec.cust_shipto_terms_id,
852 x_terms_rec.customer_id,
853 x_terms_rec.cum_control_code,
854 -- x_terms_rec.cum_current_record_year,
855 -- x_terms_rec.cum_previous_record_year,
856 x_terms_rec.cum_org_level_code,
857 x_terms_rec.cum_shipment_rule_code,
858 x_terms_rec.cum_yesterd_time_cutoff,
859 x_terms_rec.cust_assign_supplier_cd,
860 x_terms_rec.customer_rcv_calendar_cd,
861 x_terms_rec.supplier_shp_calendar_cd,
862 x_terms_rec.unship_firm_cutoff_days,
863 x_terms_rec.unshipped_firm_disp_cd,
864 x_terms_rec.inactive_date,
865 x_terms_rec.critical_attribute_key,
866 x_terms_rec.schedule_hierarchy_code,
867 x_terms_rec.comments,
868 x_terms_rec.intransit_time,
869 x_terms_rec.time_uom_code,
870 x_terms_rec.ship_from_org_id,
871 x_terms_rec.address_id,
872 x_terms_rec.header_id,
873 x_terms_rec.agreement_id,
874 x_terms_rec.agreement_name,
875 x_terms_rec.future_agreement_id,
876 x_terms_rec.future_agreement_name,
877 -- x_terms_rec.cum_current_start_date,
878 -- x_terms_rec.cum_previous_start_date,
879 x_terms_rec.round_to_std_pack_flag,
880 x_terms_rec.ship_delivery_rule_name,
881 x_terms_rec.ship_method,
882 x_terms_rec.std_pack_qty,
883 x_terms_rec.price_list_id,
884 x_terms_rec.use_edi_sdp_code_flag,
885 x_terms_rec.match_across_key,
886 x_terms_rec.match_within_key,
887 x_terms_rec.pln_firm_day_to,
888 x_terms_rec.pln_firm_day_from,
889 x_terms_rec.pln_forecast_day_from,
890 x_terms_rec.pln_forecast_day_to,
891 x_terms_rec.pln_frozen_day_to,
892 x_terms_rec.pln_frozen_day_from,
893 x_terms_rec.seq_firm_day_from,
894 x_terms_rec.seq_firm_day_to,
895 x_terms_rec.seq_forecast_day_to,
896 x_terms_rec.seq_forecast_day_from,
897 x_terms_rec.seq_frozen_day_from,
898 x_terms_rec.seq_frozen_day_to,
899 x_terms_rec.shp_firm_day_from,
900 x_terms_rec.shp_firm_day_to,
901 x_terms_rec.shp_frozen_day_from,
902 x_terms_rec.shp_frozen_day_to,
903 x_terms_rec.shp_forecast_day_from,
904 x_terms_rec.shp_forecast_day_to,
905 x_terms_rec.pln_mrp_forecast_day_from,
906 x_terms_rec.pln_mrp_forecast_day_to,
907 x_terms_rec.shp_mrp_forecast_day_from,
908 x_terms_rec.shp_mrp_forecast_day_to,
909 x_terms_rec.seq_mrp_forecast_day_from,
910 x_terms_rec.seq_mrp_forecast_day_to,
911 x_terms_rec.demand_tolerance_above,
912 x_terms_rec.demand_tolerance_below,
913 x_terms_rec.customer_contact_id,
914 x_terms_rec.freight_code,
915 x_terms_rec.supplier_contact_id,
916 x_terms_rec.attribute_category,
917 x_terms_rec.tp_attribute_category,
918 x_terms_rec.attribute1,
919 x_terms_rec.attribute2,
920 x_terms_rec.attribute4,
921 x_terms_rec.attribute5,
922 x_terms_rec.attribute6,
923 x_terms_rec.attribute7,
924 x_terms_rec.attribute8,
925 x_terms_rec.attribute9,
926 x_terms_rec.attribute10,
927 x_terms_rec.attribute11,
928 x_terms_rec.attribute12,
929 x_terms_rec.attribute13,
930 x_terms_rec.attribute14,
931 x_terms_rec.attribute15,
932 x_terms_rec.tp_attribute1,
933 x_terms_rec.tp_attribute2,
934 x_terms_rec.tp_attribute3,
935 x_terms_rec.tp_attribute4,
936 x_terms_rec.tp_attribute5,
937 x_terms_rec.tp_attribute6,
938 x_terms_rec.tp_attribute7,
939 x_terms_rec.tp_attribute8,
940 x_terms_rec.tp_attribute9,
941 x_terms_rec.tp_attribute10,
942 x_terms_rec.tp_attribute11,
943 x_terms_rec.tp_attribute12,
944 x_terms_rec.tp_attribute13,
945 x_terms_rec.tp_attribute14,
946 x_terms_rec.tp_attribute15,
947 x_terms_rec.intransit_calc_basis,
948 x_terms_rec.pln_frozen_flag,
949 x_terms_rec.shp_frozen_flag,
950 x_terms_rec.seq_frozen_flag,
951 x_terms_rec.issue_warning_drop_parts_flag,
952 x_terms_rec.blanket_number,
953 x_terms_rec.release_rule,
954 x_terms_rec.release_time_frame,
955 x_terms_rec.release_time_frame_uom,
956 x_terms_rec.exclude_non_workdays_flag,
957 x_terms_rec.disable_create_cum_key_flag
958 FROM RLM_CUST_SHIPTO_TERMS
959 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
960 AND CUSTOMER_ID = x_customer_id
961 AND ADDRESS_ID = x_ship_to_address_id;
962
963 /* By default, inactive_date is NULL */
964 IF x_terms_rec.inactive_date is NOT NULL THEN
965 IF x_terms_rec.inactive_date <= nvl(rlm_dp_sv.g_dsp_start_time,sysdate) THEN --Bugfix 10053830
966 raise e_inactive_record;
967 END IF;
968 END IF;
969 --
970 -- 4129188
971 --
972 IF (x_terms_rec.match_within_key IS NULL OR
973 x_terms_rec.match_across_key IS NULL) THEN
974 --{
975 OPEN c_optional_match_cust(v_ship_from_org_id, x_customer_id);
976 FETCH c_optional_match_cust INTO v_match_within_key, v_match_across_key;
977 CLOSE c_optional_match_cust;
978 --
979 IF (l_debug <> -1) THEN
980 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Within', v_match_within_key);
981 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Across', v_match_across_key);
982 END IF;
983 --
984 IF (x_terms_rec.match_within_key IS NULL) THEN
985 IF v_match_within_key IS NOT NULL THEN
986 x_terms_rec.match_within_key := v_match_within_key;
987 ELSE
988 /* By default, match_within_key is ABCDEFG */
989 x_terms_rec.match_within_key := RLM_CORE_SV.get_default_key;
990 END IF;
991 END IF;
992 --
993 IF (x_terms_rec.match_across_key IS NULL) THEN
994 IF v_match_across_key IS NOT NULL THEN
995 x_terms_rec.match_across_key := v_match_across_key;
996 ELSE
997 /* By default, match_across_key is ABCDEFG */
998 x_terms_rec.match_across_key := RLM_CORE_SV.get_default_key;
999 END IF;
1000 END IF;
1001 --}
1002 END IF;
1003 --
1004 x_terms_rec.calc_cum_flag := 'Y';
1005 x_terms_definition_level := 'ADDRESS';
1006 x_return_status := TRUE;
1007 --
1008 IF (l_debug <> -1) THEN
1009 rlm_core_sv.dpop(C_SDEBUG);
1010 END IF;
1011
1012 EXCEPTION
1013
1014 WHEN TOO_MANY_ROWS THEN
1015 --
1016 x_terms_rec.msg_name := 'RLM_SETUP_ADD_MULTIPLE_ROWS';
1017 rlm_message_sv.get_msg_text(
1018 x_message_name => x_terms_rec.msg_name,
1019 x_text => x_return_message);
1020 x_terms_definition_level := NULL;
1021 x_return_status := FALSE;
1022 --
1023 IF (l_debug <> -1) THEN
1024 rlm_core_sv.dlog(C_DEBUG, 'There are more than one record of RLM Setup Terms at the ADDRESS level');
1025 rlm_core_sv.dpop(C_SDEBUG);
1026 END IF;
1027 --
1028 WHEN e_no_default THEN
1029 --
1030 x_terms_rec.msg_name := 'RLM_SETUP_ADDRESS_NO_DEFAULT';
1031 rlm_message_sv.get_msg_text(
1032 x_message_name => x_terms_rec.msg_name,
1033 x_text => x_return_message,
1034 x_token1 => 'CUST',
1035 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id),
1036 x_token2 => 'ST',
1037 x_value2 => RLM_CORE_SV.get_ship_to(x_ship_to_address_id));
1038 --
1039 --x_terms_definition_level := NULL;
1040 --x_return_status := FALSE;
1041 --
1042 -- Bug 4888849 : Query next level of setup terms
1043 --
1044 RLM_TPA_SV.populate_record_cust_item(x_ship_from_org_id,
1045 x_customer_id,
1046 x_ship_to_address_id,
1047 x_customer_item_id,
1048 x_terms_definition_level,
1049 x_terms_rec,
1050 x_return_message,
1051 x_return_status);
1052 --
1053 IF (l_debug <> -1) THEN
1054 rlm_core_sv.dpop(C_SDEBUG);
1055 END IF;
1056 --
1057 WHEN e_inactive_record THEN
1058 --
1059 RLM_TPA_SV.populate_record_cust_item(x_ship_from_org_id,
1060 x_customer_id,
1061 x_ship_to_address_id,
1062 x_customer_item_id,
1063 x_terms_definition_level,
1064 x_terms_rec,
1065 x_return_message,
1066 x_return_status);
1067 --
1068 IF (l_debug <> -1) THEN
1069 rlm_core_sv.dlog(C_DEBUG, 'The RLM Setup Terms record at the ADDRESS level has been inactivated');
1070 rlm_core_sv.dpop(C_SDEBUG);
1071 END IF;
1072 --
1073 WHEN NO_DATA_FOUND THEN
1074 --
1075 RLM_TPA_SV.populate_record_cust_item(x_ship_from_org_id,
1076 x_customer_id,
1077 x_ship_to_address_id,
1078 x_customer_item_id,
1079 x_terms_definition_level,
1080 x_terms_rec,
1081 x_return_message,
1082 x_return_status);
1083 --
1084 IF (l_debug <> -1) THEN
1085 rlm_core_sv.dlog(C_DEBUG, 'No data found at ADDRESS level');
1086 rlm_core_sv.dpop(C_SDEBUG);
1087 END IF;
1088 --
1089 WHEN OTHERS THEN
1090 --
1091 x_terms_definition_level := NULL;
1092 x_return_status := FALSE;
1093 --
1094 IF (l_debug <> -1) THEN
1095 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
1096 rlm_core_sv.dpop(C_SDEBUG);
1097 END IF;
1098 --
1099 RAISE;
1100
1101 END populate_record_add;
1102
1103 /*=============================================================================
1104
1105 PROCEDURE NAME: populate_record_item
1106
1107 =============================================================================*/
1108
1109 PROCEDURE populate_record_item (
1110 x_ship_from_org_id IN NUMBER,
1111 x_customer_id IN NUMBER,
1112 x_ship_to_address_id IN NUMBER,
1113 x_customer_item_id IN NUMBER,
1114 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
1115 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
1116 x_return_message IN OUT NOCOPY VARCHAR2,
1117 x_return_status OUT NOCOPY BOOLEAN)
1118 IS
1119 --
1120 v_ship_to_address_id NUMBER DEFAULT -1;
1121 v_ship_from_org_id NUMBER DEFAULT -1;
1122 v_customer_item_id NUMBER;
1123 e_inactive_record EXCEPTION;
1124 v_level VARCHAR2(30) DEFAULT NULL;
1125 e_no_default EXCEPTION;
1126 --
1127 -- 4129188
1128 --
1129 v_match_within_key VARCHAR2(240);
1130 v_match_across_key VARCHAR2(240);
1131 --
1132 CURSOR c_optional_match_cust(p_shipFromOrgId NUMBER,
1133 p_CustomerId NUMBER) IS
1134 SELECT match_within_key, match_across_key
1135 FROM rlm_cust_shipto_terms
1136 WHERE ship_from_org_id = p_shipFromOrgId
1137 AND customer_id = p_CustomerId
1138 AND address_id IS NULL;
1139 --
1140 BEGIN
1141 --
1142 IF (l_debug <> -1) THEN
1143 rlm_core_sv.dpush(C_SDEBUG, 'populate_record_item');
1144 --global_atp
1145 rlm_core_sv.dlog(C_DEBUG, 'x_ship_from_org_id', x_ship_from_org_id );
1146 rlm_core_sv.dlog(C_DEBUG, 'x_customer_id', x_customer_id );
1147 rlm_core_sv.dlog(C_DEBUG, 'x_ship_to_address_id', x_ship_to_address_id );
1148 rlm_core_sv.dlog(C_DEBUG, 'x_customer_item_id', x_customer_item_id );
1149 rlm_core_sv.dlog(C_DEBUG, 'Before Item Level Select ');
1150 END IF;
1151
1152 v_ship_from_org_id := x_ship_from_org_id;
1153
1154 -- Before selecting the terms, check if the ship_from_org_id is null */
1155 -- global_atp
1156 IF x_ship_from_org_id IS NULL THEN
1157 --
1158 BEGIN
1159
1160 SELECT ship_from_org_id
1161 INTO v_ship_from_org_id
1162 FROM rlm_cust_item_terms
1163 WHERE customer_id = x_customer_id
1164 AND address_id = x_ship_to_address_id
1165 AND customer_item_id = x_customer_item_id
1166 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
1167
1168 -- Proceed
1169 IF (l_debug <> -1) THEN
1170 rlm_core_sv.dlog(C_DEBUG, 'v_ship_from_org_id', v_ship_from_org_id );
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN TOO_MANY_ROWS THEN
1175 -- Get the default
1176 BEGIN
1177 SELECT ship_from_org_id
1178 INTO v_ship_from_org_id
1179 FROM rlm_cust_item_terms
1180 WHERE customer_id = x_customer_id
1181 AND address_id = x_ship_to_address_id
1182 AND customer_item_id = x_customer_item_id
1183 AND NVL(default_ship_from,'N') = 'Y'
1184 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
1185
1186 -- Proceed
1187 IF (l_debug <> -1) THEN
1188 rlm_core_sv.dlog(C_DEBUG, 'Default v_ship_from_org_id', v_ship_from_org_id );
1189 END IF;
1190
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND THEN
1193 RAISE e_no_default;
1194 END;
1195
1196 WHEN NO_DATA_FOUND THEN
1197 RAISE;
1198
1199 WHEN e_no_default THEN
1200 RAISE;
1201
1202 WHEN OTHERS THEN
1203 RAISE;
1204
1205 END;
1206 --
1207 END IF;
1208 --
1209 -- Find Address Item terms
1210 --
1211 --
1212 v_level := 'REGULAR_TERMS';
1213 --
1214 SELECT ship_from_org_id,
1215 address_id,
1216 header_id,
1217 agreement_id,
1218 agreement_name,
1219 future_agreement_id,
1220 future_agreement_name,
1221 round_to_std_pack_flag,
1222 ship_delivery_rule_name,
1223 ship_method,
1224 intransit_time,
1225 time_uom_code,
1226 std_pack_qty,
1227 price_list_id,
1228 use_edi_sdp_code_flag,
1229 pln_firm_day_to,
1230 pln_firm_day_from,
1231 pln_forecast_day_from,
1232 pln_forecast_day_to,
1233 pln_frozen_day_to,
1234 pln_frozen_day_from,
1235 seq_firm_day_from,
1236 seq_firm_day_to,
1237 seq_forecast_day_to,
1238 seq_forecast_day_from,
1239 seq_frozen_day_from,
1240 seq_frozen_day_to,
1241 shp_firm_day_from,
1242 shp_firm_day_to,
1243 shp_frozen_day_from,
1244 shp_frozen_day_to,
1245 shp_forecast_day_from,
1246 shp_forecast_day_to,
1247 pln_mrp_forecast_day_from,
1248 pln_mrp_forecast_day_to,
1249 shp_mrp_forecast_day_from,
1250 shp_mrp_forecast_day_to,
1251 seq_mrp_forecast_day_from,
1252 seq_mrp_forecast_day_to,
1253 demand_tolerance_above,
1254 demand_tolerance_below,
1255 customer_contact_id,
1256 freight_code,
1257 supplier_contact_id,
1258 attribute_category,
1259 tp_attribute_category,
1260 attribute1,
1261 attribute2,
1262 attribute4,
1263 attribute5,
1264 attribute6,
1265 attribute7,
1266 attribute8,
1267 attribute9,
1268 attribute10,
1269 attribute11,
1270 attribute12,
1271 attribute13,
1272 attribute14,
1273 attribute15,
1274 tp_attribute1,
1275 tp_attribute2,
1276 tp_attribute3,
1277 tp_attribute4,
1278 tp_attribute5,
1279 tp_attribute6,
1280 tp_attribute7,
1281 tp_attribute8,
1282 tp_attribute9,
1283 tp_attribute10,
1284 tp_attribute11,
1285 tp_attribute12,
1286 tp_attribute13,
1287 tp_attribute14,
1288 tp_attribute15,
1289 cust_item_terms_id,
1290 customer_item_id,
1291 calc_cum_flag,
1292 cust_item_status_code,
1293 inactive_date,
1294 pln_frozen_flag,
1295 shp_frozen_flag,
1296 seq_frozen_flag,
1297 issue_warning_drop_parts_flag,
1298 blanket_number,
1299 release_rule,
1300 release_time_frame,
1301 release_time_frame_uom,
1302 exclude_non_workdays_flag
1303 INTO x_terms_rec.ship_from_org_id,
1304 x_terms_rec.address_id,
1305 x_terms_rec.header_id,
1306 x_terms_rec.agreement_id,
1307 x_terms_rec.agreement_name,
1308 x_terms_rec.future_agreement_id,
1309 x_terms_rec.future_agreement_name,
1310 x_terms_rec.round_to_std_pack_flag,
1311 x_terms_rec.ship_delivery_rule_name,
1312 x_terms_rec.ship_method,
1313 x_terms_rec.intransit_time,
1314 x_terms_rec.time_uom_code,
1315 x_terms_rec.std_pack_qty,
1316 x_terms_rec.price_list_id,
1317 x_terms_rec.use_edi_sdp_code_flag,
1318 x_terms_rec.pln_firm_day_to,
1319 x_terms_rec.pln_firm_day_from,
1320 x_terms_rec.pln_forecast_day_from,
1321 x_terms_rec.pln_forecast_day_to,
1322 x_terms_rec.pln_frozen_day_to,
1323 x_terms_rec.pln_frozen_day_from,
1324 x_terms_rec.seq_firm_day_from,
1325 x_terms_rec.seq_firm_day_to,
1326 x_terms_rec.seq_forecast_day_to,
1327 x_terms_rec.seq_forecast_day_from,
1328 x_terms_rec.seq_frozen_day_from,
1329 x_terms_rec.seq_frozen_day_to,
1330 x_terms_rec.shp_firm_day_from,
1331 x_terms_rec.shp_firm_day_to,
1332 x_terms_rec.shp_frozen_day_from,
1333 x_terms_rec.shp_frozen_day_to,
1334 x_terms_rec.shp_forecast_day_from,
1335 x_terms_rec.shp_forecast_day_to,
1336 x_terms_rec.pln_mrp_forecast_day_from,
1337 x_terms_rec.pln_mrp_forecast_day_to,
1338 x_terms_rec.shp_mrp_forecast_day_from,
1339 x_terms_rec.shp_mrp_forecast_day_to,
1340 x_terms_rec.seq_mrp_forecast_day_from,
1341 x_terms_rec.seq_mrp_forecast_day_to,
1342 x_terms_rec.demand_tolerance_above,
1343 x_terms_rec.demand_tolerance_below,
1344 x_terms_rec.customer_contact_id,
1345 x_terms_rec.freight_code,
1346 x_terms_rec.supplier_contact_id,
1347 x_terms_rec.attribute_category,
1348 x_terms_rec.tp_attribute_category,
1349 x_terms_rec.attribute1,
1350 x_terms_rec.attribute2,
1351 x_terms_rec.attribute4,
1352 x_terms_rec.attribute5,
1353 x_terms_rec.attribute6,
1354 x_terms_rec.attribute7,
1355 x_terms_rec.attribute8,
1356 x_terms_rec.attribute9,
1357 x_terms_rec.attribute10,
1358 x_terms_rec.attribute11,
1359 x_terms_rec.attribute12,
1360 x_terms_rec.attribute13,
1361 x_terms_rec.attribute14,
1362 x_terms_rec.attribute15,
1363 x_terms_rec.tp_attribute1,
1364 x_terms_rec.tp_attribute2,
1365 x_terms_rec.tp_attribute3,
1366 x_terms_rec.tp_attribute4,
1367 x_terms_rec.tp_attribute5,
1368 x_terms_rec.tp_attribute6,
1369 x_terms_rec.tp_attribute7,
1370 x_terms_rec.tp_attribute8,
1371 x_terms_rec.tp_attribute9,
1372 x_terms_rec.tp_attribute10,
1373 x_terms_rec.tp_attribute11,
1374 x_terms_rec.tp_attribute12,
1375 x_terms_rec.tp_attribute13,
1376 x_terms_rec.tp_attribute14,
1377 x_terms_rec.tp_attribute15,
1378 x_terms_rec.cust_item_terms_id,
1379 x_terms_rec.customer_item_id,
1380 x_terms_rec.calc_cum_flag,
1381 x_terms_rec.cust_item_status_code,
1382 x_terms_rec.inactive_date,
1383 x_terms_rec.pln_frozen_flag,
1384 x_terms_rec.shp_frozen_flag,
1385 x_terms_rec.seq_frozen_flag,
1386 x_terms_rec.issue_warning_drop_parts_flag,
1387 x_terms_rec.blanket_number,
1388 x_terms_rec.release_rule,
1389 x_terms_rec.release_time_frame,
1390 x_terms_rec.release_time_frame_uom,
1391 x_terms_rec.exclude_non_workdays_flag
1392 FROM RLM_CUST_ITEM_TERMS
1393 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1394 AND CUSTOMER_ID = x_customer_id
1395 AND ADDRESS_ID = x_ship_to_address_id
1396 AND CUSTOMER_ITEM_ID = x_customer_item_id;
1397 --
1398 v_level := 'EXCEPTIONAL_TERMS';
1399 --
1400 -- Select exceptional terms
1401 --
1402 SELECT cum_control_code,
1403 critical_attribute_key,
1404 NVL(match_within_key, rlm_core_sv.get_default_key),
1405 NVL(match_across_key, rlm_core_sv.get_default_key),
1406 schedule_hierarchy_code,
1407 unshipped_firm_disp_cd,
1408 unship_firm_cutoff_days,
1409 cum_shipment_rule_code,
1410 cum_org_level_code,
1411 cum_yesterd_time_cutoff,
1412 customer_rcv_calendar_cd,
1413 supplier_shp_calendar_cd,
1414 cust_assign_supplier_cd,
1415 intransit_calc_basis,
1416 disable_create_cum_key_flag --Bugfix 8506409
1417 INTO x_terms_rec.cum_control_code,
1418 x_terms_rec.critical_attribute_key,
1419 x_terms_rec.match_within_key,
1420 x_terms_rec.match_across_key,
1421 x_terms_rec.schedule_hierarchy_code,
1422 x_terms_rec.unshipped_firm_disp_cd,
1423 x_terms_rec.unship_firm_cutoff_days,
1424 x_terms_rec.cum_shipment_rule_code,
1425 x_terms_rec.cum_org_level_code,
1426 x_terms_rec.cum_yesterd_time_cutoff,
1427 x_terms_rec.customer_rcv_calendar_cd,
1428 x_terms_rec.supplier_shp_calendar_cd,
1429 x_terms_rec.cust_assign_supplier_cd,
1430 x_terms_rec.intransit_calc_basis,
1431 x_terms_rec.disable_create_cum_key_flag --Bugfix 8506409
1432 FROM RLM_CUST_SHIPTO_TERMS
1433 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1434 AND CUSTOMER_ID = x_customer_id
1435 AND ADDRESS_ID = x_ship_to_address_id;
1436 --
1437 IF (l_debug <> -1) THEN
1438 rlm_core_sv.dlog(C_DEBUG, 'Picked up exceptional terms at address level');
1439 rlm_core_sv.dlog(C_DEBUG, 'After Item Level Select ');
1440 END IF;
1441 --
1442 /* By default, inactive_date is NULL */
1443 IF x_terms_rec.inactive_date is NOT NULL THEN
1444 IF x_terms_rec.inactive_date <= nvl(rlm_dp_sv.g_dsp_start_time,sysdate) THEN --Bugfix 10053830
1445 raise e_inactive_record;
1446 END IF;
1447 END IF;
1448 --
1449 -- 4129188
1450 --
1451 IF (x_terms_rec.match_within_key IS NULL OR
1452 x_terms_rec.match_across_key IS NULL) THEN
1453 --{
1454 OPEN c_optional_match_cust(v_ship_from_org_id, x_customer_id);
1455 FETCH c_optional_match_cust INTO v_match_within_key, v_match_across_key;
1456 CLOSE c_optional_match_cust;
1457 --
1458 IF (l_debug <> -1) THEN
1459 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Within', v_match_within_key);
1460 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Across', v_match_across_key);
1461 END IF;
1462 --
1463 IF (x_terms_rec.match_within_key IS NULL) THEN
1464 --
1465 IF v_match_within_key IS NOT NULL THEN
1466 x_terms_rec.match_within_key := v_match_within_key;
1467 ELSE
1468 x_terms_rec.match_within_key := RLM_CORE_SV.get_default_key;
1469 END IF;
1470 --
1471 END IF;
1472 --
1473 IF (x_terms_rec.match_across_key IS NULL) THEN
1474 --
1475 IF v_match_across_key IS NOT NULL THEN
1476 x_terms_rec.match_across_key := v_match_across_key;
1477 ELSE
1478 x_terms_rec.match_across_key := RLM_CORE_SV.get_default_key;
1479 END IF;
1480 --
1481 END IF;
1482 --}
1483 END IF;
1484 --
1485 x_terms_definition_level := 'ADDRESS_ITEM';
1486 x_return_status := TRUE;
1487 --
1488 IF (l_debug <> -1) THEN
1489 rlm_core_sv.dpop(C_SDEBUG);
1490 END IF;
1491 --
1492 EXCEPTION
1493 --
1494 WHEN TOO_MANY_ROWS THEN
1495 --
1496 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_MULTIPLE_ROWS';
1497 rlm_message_sv.get_msg_text(
1498 x_message_name => x_terms_rec.msg_name,
1499 x_text => x_return_message);
1500 --
1501 x_terms_definition_level := NULL;
1502 --
1503 x_return_status := FALSE;
1504 --
1505 IF (l_debug <> -1) THEN
1506 rlm_core_sv.dlog(C_DEBUG, 'Too Many Rows ');
1507 rlm_core_sv.dpop(C_SDEBUG, 'There are more than one record of RLM Setup Terms at the CUSTOMER_ITEM level');
1508 END IF;
1509
1510
1511 WHEN e_no_default THEN
1512 --
1513 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_NO_DEFAULT';
1514 rlm_message_sv.get_msg_text(
1515 x_message_name => x_terms_rec.msg_name,
1516 x_text => x_return_message,
1517 x_token1 => 'CUST',
1518 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id),
1519 x_token2 => 'ST',
1520 x_value2 => RLM_CORE_SV.get_ship_to(x_ship_to_address_id),
1521 x_token3 => 'CI',
1522 x_value3 => RLM_CORE_SV.get_item_number(x_customer_item_id));
1523 --
1524 --x_terms_definition_level := NULL;
1525 --x_return_status := FALSE;
1526 --
1527 -- Bug 4888849 : Query next level of terms
1528 --
1529 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1530 x_customer_id,
1531 x_ship_to_address_id,
1532 x_customer_item_id,
1533 x_terms_definition_level,
1534 x_terms_rec,
1535 x_return_message,
1536 x_return_status);
1537 --
1538 IF (l_debug <> -1) THEN
1539 rlm_core_sv.dpop(C_SDEBUG);
1540 END IF;
1541 --
1542 WHEN e_inactive_record THEN
1543 --
1544 IF (l_debug <> -1) THEN
1545 rlm_core_sv.dlog(C_DEBUG, 'Inactive Record ');
1546 rlm_core_sv.dlog(C_DEBUG, 'Populating Rec Addres ');
1547 END IF;
1548 --
1549 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1550 x_customer_id,
1551 x_ship_to_address_id,
1552 x_customer_item_id,
1553 x_terms_definition_level,
1554 x_terms_rec,
1555 x_return_message,
1556 x_return_status);
1557 --
1558 IF (l_debug <> -1) THEN
1559 rlm_core_sv.dpop(C_SDEBUG, 'The RLM Setup Terms record at the CUSTOMER_ITEM level has been inactivated');
1560 END IF;
1561 --
1562 WHEN NO_DATA_FOUND THEN
1563 --
1564 IF (l_debug <> -1) THEN
1565 rlm_core_sv.dlog(C_DEBUG, 'No data found');
1566 END IF;
1567 --
1568 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1569 x_customer_id,
1570 x_ship_to_address_id,
1571 x_customer_item_id,
1572 x_terms_definition_level,
1573 x_terms_rec,
1574 x_return_message,
1575 x_return_status);
1576 --
1577 IF (l_debug <> -1) THEN
1578 rlm_core_sv.dpop(C_SDEBUG, 'No data found');
1579 END IF;
1580 --
1581 WHEN OTHERS THEN
1582 --
1583 x_terms_definition_level := NULL;
1584 x_return_status := FALSE;
1585 --
1586 IF (l_debug <> -1) THEN
1587 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
1588 rlm_core_sv.dpop(C_SDEBUG);
1589 END IF;
1590 --
1591 RAISE;
1592
1593 END populate_record_item;
1594
1595 /*=============================================================================
1596
1597 PROCEDURE NAME: populate_record_cust_item
1598
1599 =============================================================================*/
1600
1601 PROCEDURE populate_record_cust_item (
1602 x_ship_from_org_id IN NUMBER,
1603 x_customer_id IN NUMBER,
1604 x_ship_to_address_id IN NUMBER,
1605 x_customer_item_id IN NUMBER,
1606 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
1607 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
1608 x_return_message IN OUT NOCOPY VARCHAR2,
1609 x_return_status OUT NOCOPY BOOLEAN)
1610 IS
1611 --
1612 v_ship_to_address_id NUMBER DEFAULT -1;
1613 v_ship_from_org_id NUMBER DEFAULT -1;
1614 v_customer_item_id NUMBER;
1615 e_inactive_record EXCEPTION;
1616 v_level VARCHAR2(30) DEFAULT NULL;
1617 e_no_default EXCEPTION;
1618 --
1619 BEGIN
1620 --
1621 IF (l_debug <> -1) THEN
1622 rlm_core_sv.dpush(C_SDEBUG, 'populate_record_cust_item');
1623 --global_atp
1624 rlm_core_sv.dlog(C_DEBUG, 'x_ship_from_org_id', x_ship_from_org_id );
1625 rlm_core_sv.dlog(C_DEBUG, 'x_customer_id', x_customer_id );
1626 rlm_core_sv.dlog(C_DEBUG, 'x_ship_to_address_id', x_ship_to_address_id );
1627 rlm_core_sv.dlog(C_DEBUG, 'x_customer_item_id', x_customer_item_id );
1628 rlm_core_sv.dlog(C_DEBUG, 'Before Item Level Select ');
1629 END IF;
1630 --
1631 v_ship_from_org_id := x_ship_from_org_id;
1632
1633 -- Before selecting the terms, check if the ship_from_org_id is null */
1634 -- global_atp
1635 IF x_ship_from_org_id IS NULL THEN
1636 --
1637 BEGIN
1638
1639 SELECT ship_from_org_id
1640 INTO v_ship_from_org_id
1641 FROM rlm_cust_item_terms
1642 WHERE customer_id = x_customer_id
1643 AND address_id IS NULL
1644 AND customer_item_id = x_customer_item_id
1645 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
1646
1647 -- Proceed
1648 IF (l_debug <> -1) THEN
1649 rlm_core_sv.dlog(C_DEBUG, 'v_ship_from_org_id', v_ship_from_org_id );
1650 END IF;
1651
1652 EXCEPTION
1653 WHEN TOO_MANY_ROWS THEN
1654 -- Get the default
1655 BEGIN
1656 SELECT ship_from_org_id
1657 INTO v_ship_from_org_id
1658 FROM rlm_cust_item_terms
1659 WHERE customer_id = x_customer_id
1660 AND address_id IS NULL
1661 AND customer_item_id = x_customer_item_id
1662 AND NVL(default_ship_from,'N') = 'Y'
1663 AND (inactive_date IS NULL OR inactive_date > nvl(rlm_dp_sv.g_dsp_start_time,SYSDATE)); --Bugfix 10053830
1664
1665 -- Proceed
1666 IF (l_debug <> -1) THEN
1667 rlm_core_sv.dlog(C_DEBUG, 'Default v_ship_from_org_id', v_ship_from_org_id );
1668 END IF;
1669
1670 EXCEPTION
1671 WHEN NO_DATA_FOUND THEN
1672 RAISE e_no_default;
1673 END;
1674
1675 WHEN NO_DATA_FOUND THEN
1676 RAISE;
1677
1678 WHEN e_no_default THEN
1679 RAISE;
1680
1681 WHEN OTHERS THEN
1682 RAISE;
1683
1684 END;
1685 --
1686 END IF;
1687
1688 -- Find Customer Item terms
1689 --
1690 --
1691 v_level := 'REGULAR_TERMS';
1692 --
1693 SELECT ship_from_org_id,
1694 address_id,
1695 header_id,
1696 agreement_id,
1697 agreement_name,
1698 future_agreement_id,
1699 future_agreement_name,
1700 round_to_std_pack_flag,
1701 ship_delivery_rule_name,
1702 ship_method,
1703 intransit_time,
1704 time_uom_code,
1705 std_pack_qty,
1706 price_list_id,
1707 use_edi_sdp_code_flag,
1708 pln_firm_day_to,
1709 pln_firm_day_from,
1710 pln_forecast_day_from,
1711 pln_forecast_day_to,
1712 pln_frozen_day_to,
1713 pln_frozen_day_from,
1714 seq_firm_day_from,
1715 seq_firm_day_to,
1716 seq_forecast_day_to,
1717 seq_forecast_day_from,
1718 seq_frozen_day_from,
1719 seq_frozen_day_to,
1720 shp_firm_day_from,
1721 shp_firm_day_to,
1722 shp_frozen_day_from,
1723 shp_frozen_day_to,
1724 shp_forecast_day_from,
1725 shp_forecast_day_to,
1726 pln_mrp_forecast_day_from,
1727 pln_mrp_forecast_day_to,
1728 shp_mrp_forecast_day_from,
1729 shp_mrp_forecast_day_to,
1730 seq_mrp_forecast_day_from,
1731 seq_mrp_forecast_day_to,
1732 demand_tolerance_above,
1733 demand_tolerance_below,
1734 customer_contact_id,
1735 freight_code,
1736 supplier_contact_id,
1737 attribute_category,
1738 tp_attribute_category,
1739 attribute1,
1740 attribute2,
1741 attribute4,
1742 attribute5,
1743 attribute6,
1744 attribute7,
1745 attribute8,
1746 attribute9,
1747 attribute10,
1748 attribute11,
1749 attribute12,
1750 attribute13,
1751 attribute14,
1752 attribute15,
1753 tp_attribute1,
1754 tp_attribute2,
1755 tp_attribute3,
1756 tp_attribute4,
1757 tp_attribute5,
1758 tp_attribute6,
1759 tp_attribute7,
1760 tp_attribute8,
1761 tp_attribute9,
1762 tp_attribute10,
1763 tp_attribute11,
1764 tp_attribute12,
1765 tp_attribute13,
1766 tp_attribute14,
1767 tp_attribute15,
1768 cust_item_terms_id,
1769 customer_item_id,
1770 calc_cum_flag,
1771 cust_item_status_code,
1772 inactive_date,
1773 pln_frozen_flag,
1774 shp_frozen_flag,
1775 seq_frozen_flag,
1776 issue_warning_drop_parts_flag,
1777 blanket_number,
1778 release_rule,
1779 release_time_frame,
1780 release_time_frame_uom,
1781 exclude_non_workdays_flag
1782 INTO x_terms_rec.ship_from_org_id,
1783 x_terms_rec.address_id,
1784 x_terms_rec.header_id,
1785 x_terms_rec.agreement_Id,
1786 x_terms_rec.agreement_name,
1787 x_terms_rec.future_agreement_id,
1788 x_terms_rec.future_agreement_name,
1789 x_terms_rec.round_to_std_pack_flag,
1790 x_terms_rec.ship_delivery_rule_name,
1791 x_terms_rec.ship_method,
1792 x_terms_rec.intransit_time,
1793 x_terms_rec.time_uom_code,
1794 x_terms_rec.std_pack_qty,
1795 x_terms_rec.price_list_id,
1796 x_terms_rec.use_edi_sdp_code_flag,
1797 x_terms_rec.pln_firm_day_to,
1798 x_terms_rec.pln_firm_day_from,
1799 x_terms_rec.pln_forecast_day_from,
1800 x_terms_rec.pln_forecast_day_to,
1801 x_terms_rec.pln_frozen_day_to,
1802 x_terms_rec.pln_frozen_day_from,
1803 x_terms_rec.seq_firm_day_from,
1804 x_terms_rec.seq_firm_day_to,
1805 x_terms_rec.seq_forecast_day_to,
1806 x_terms_rec.seq_forecast_day_from,
1807 x_terms_rec.seq_frozen_day_from,
1808 x_terms_rec.seq_frozen_day_to,
1809 x_terms_rec.shp_firm_day_from,
1810 x_terms_rec.shp_firm_day_to,
1811 x_terms_rec.shp_frozen_day_from,
1812 x_terms_rec.shp_frozen_day_to,
1813 x_terms_rec.shp_forecast_day_from,
1814 x_terms_rec.shp_forecast_day_to,
1815 x_terms_rec.pln_mrp_forecast_day_from,
1816 x_terms_rec.pln_mrp_forecast_day_to,
1817 x_terms_rec.shp_mrp_forecast_day_from,
1818 x_terms_rec.shp_mrp_forecast_day_to,
1819 x_terms_rec.seq_mrp_forecast_day_from,
1820 x_terms_rec.seq_mrp_forecast_day_to,
1821 x_terms_rec.demand_tolerance_above,
1822 x_terms_rec.demand_tolerance_below,
1823 x_terms_rec.customer_contact_id,
1824 x_terms_rec.freight_code,
1825 x_terms_rec.supplier_contact_id,
1826 x_terms_rec.attribute_category,
1827 x_terms_rec.tp_attribute_category,
1828 x_terms_rec.attribute1,
1829 x_terms_rec.attribute2,
1830 x_terms_rec.attribute4,
1831 x_terms_rec.attribute5,
1832 x_terms_rec.attribute6,
1833 x_terms_rec.attribute7,
1834 x_terms_rec.attribute8,
1835 x_terms_rec.attribute9,
1836 x_terms_rec.attribute10,
1837 x_terms_rec.attribute11,
1838 x_terms_rec.attribute12,
1839 x_terms_rec.attribute13,
1840 x_terms_rec.attribute14,
1841 x_terms_rec.attribute15,
1842 x_terms_rec.tp_attribute1,
1843 x_terms_rec.tp_attribute2,
1844 x_terms_rec.tp_attribute3,
1845 x_terms_rec.tp_attribute4,
1846 x_terms_rec.tp_attribute5,
1847 x_terms_rec.tp_attribute6,
1848 x_terms_rec.tp_attribute7,
1849 x_terms_rec.tp_attribute8,
1850 x_terms_rec.tp_attribute9,
1851 x_terms_rec.tp_attribute10,
1852 x_terms_rec.tp_attribute11,
1853 x_terms_rec.tp_attribute12,
1854 x_terms_rec.tp_attribute13,
1855 x_terms_rec.tp_attribute14,
1856 x_terms_rec.tp_attribute15,
1857 x_terms_rec.cust_item_terms_id,
1858 x_terms_rec.customer_item_id,
1859 x_terms_rec.calc_cum_flag,
1860 x_terms_rec.cust_item_status_code,
1861 x_terms_rec.inactive_date,
1862 x_terms_rec.pln_frozen_flag,
1863 x_terms_rec.shp_frozen_flag,
1864 x_terms_rec.seq_frozen_flag,
1865 x_terms_rec.issue_warning_drop_parts_flag,
1866 x_terms_rec.blanket_number,
1867 x_terms_rec.release_rule,
1868 x_terms_rec.release_time_frame,
1869 x_terms_rec.release_time_frame_uom,
1870 x_terms_rec.exclude_non_workdays_flag
1871 FROM RLM_CUST_ITEM_TERMS
1872 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1873 AND CUSTOMER_ID = x_customer_id
1874 AND ADDRESS_ID IS NULL
1875 AND CUSTOMER_ITEM_ID = x_customer_item_id;
1876 --
1877 v_level := 'EXCEPTIONAL_TERMS';
1878 --
1879 -- Select exceptional terms
1880 --
1881 SELECT cum_control_code,
1882 critical_attribute_key,
1883 NVL(match_within_key, rlm_core_sv.get_default_key),
1884 NVL(match_across_key, rlm_core_sv.get_default_key),
1885 schedule_hierarchy_code,
1886 unshipped_firm_disp_cd,
1887 unship_firm_cutoff_days,
1888 cum_shipment_rule_code,
1889 cum_org_level_code,
1890 cum_yesterd_time_cutoff,
1891 customer_rcv_calendar_cd,
1892 supplier_shp_calendar_cd,
1893 cust_assign_supplier_cd,
1894 intransit_calc_basis,
1895 disable_create_cum_key_flag --Bugfix 8506409
1896 INTO
1897 x_terms_rec.cum_control_code,
1898 x_terms_rec.critical_attribute_key,
1899 x_terms_rec.match_within_key,
1900 x_terms_rec.match_across_key,
1901 x_terms_rec.schedule_hierarchy_code,
1902 x_terms_rec.unshipped_firm_disp_cd,
1903 x_terms_rec.unship_firm_cutoff_days,
1904 x_terms_rec.cum_shipment_rule_code,
1905 x_terms_rec.cum_org_level_code,
1906 x_terms_rec.cum_yesterd_time_cutoff,
1907 x_terms_rec.customer_rcv_calendar_cd,
1908 x_terms_rec.supplier_shp_calendar_cd,
1909 x_terms_rec.cust_assign_supplier_cd,
1910 x_terms_rec.intransit_calc_basis,
1911 x_terms_rec.disable_create_cum_key_flag --Bugfix 8506409
1912 FROM RLM_CUST_SHIPTO_TERMS
1913 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1914 AND CUSTOMER_ID = x_customer_id
1915 AND ADDRESS_ID IS NULL;
1916 --
1917 IF (l_debug <> -1) THEN
1918 rlm_core_sv.dlog(C_DEBUG, 'Picked up exceptional terms at Customer level');
1919 rlm_core_sv.dlog(C_DEBUG, 'After Item Level Select ');
1920 END IF;
1921 --
1922 /* By default, inactive_date is NULL */
1923 IF x_terms_rec.inactive_date is NOT NULL THEN
1924 IF x_terms_rec.inactive_date <= nvl(rlm_dp_sv.g_dsp_start_time,sysdate) THEN --Bugfix 10053830
1925 raise e_inactive_record;
1926 END IF;
1927 END IF;
1928 --
1929 IF x_terms_rec.match_within_key is NULL THEN
1930 x_terms_rec.match_within_key := rlm_core_sv.get_default_key;
1931 END IF;
1932 --
1933 IF x_terms_rec.match_across_key is NULL THEN
1934 x_terms_rec.match_across_key := rlm_core_sv.get_default_key;
1935 END IF;
1936 --
1937 x_terms_definition_level := 'CUSTOMER_ITEM';
1938 x_return_status := TRUE;
1939 --
1940 IF (l_debug <> -1) THEN
1941 rlm_core_sv.dpop(C_SDEBUG);
1942 END IF;
1943 --
1944 EXCEPTION
1945 --
1946 WHEN TOO_MANY_ROWS THEN
1947 --
1948 IF (l_debug <> -1) THEN
1949 rlm_core_sv.dlog(C_DEBUG, 'Too Many Rows ');
1950 END IF;
1951 --
1952 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_MULTIPLE_ROWS';
1953 rlm_message_sv.get_msg_text(
1954 x_message_name => x_terms_rec.msg_name,
1955 x_text => x_return_message);
1956 --
1957 x_terms_definition_level := NULL;
1958 --
1959 x_return_status := FALSE;
1960 --
1961 IF (l_debug <> -1) THEN
1962 rlm_core_sv.dpop(C_SDEBUG, 'There are more than one record of RLM Setup Terms at the CUSTOMER_ITEM level');
1963 END IF;
1964 --
1965
1966 WHEN e_no_default THEN
1967 --
1968 IF (l_debug <> -1) THEN
1969 rlm_core_sv.dlog(C_DEBUG, 'No Default');
1970 END IF;
1971 --
1972 x_terms_rec.msg_name := 'RLM_SETUP_CITEM_NO_DEFAULT';
1973 rlm_message_sv.get_msg_text(
1974 x_message_name => x_terms_rec.msg_name,
1975 x_text => x_return_message,
1976 x_token1 => 'CUST',
1977 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id),
1978 x_token2 => 'CI',
1979 x_value2 => RLM_CORE_SV.get_item_number(x_customer_item_id));
1980 --
1981 --x_terms_definition_level := NULL;
1982 --x_return_status := FALSE;
1983 --
1984 -- Bug 4888849 : Query next level of setup terms
1985 --
1986 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
1987 x_customer_id,
1988 x_terms_definition_level,
1989 x_terms_rec,
1990 x_return_message,
1991 x_return_status);
1992 --
1993 IF (l_debug <> -1) THEN
1994 rlm_core_sv.dpop(C_SDEBUG);
1995 END IF;
1996
1997
1998 WHEN e_inactive_record THEN
1999 --
2000 IF (l_debug <> -1) THEN
2001 rlm_core_sv.dlog(C_DEBUG, 'Inactive Record ');
2002 rlm_core_sv.dlog(C_DEBUG, 'Populating Rec Customer ');
2003 END IF;
2004 --
2005 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
2006 x_customer_id,
2007 x_terms_definition_level,
2008 x_terms_rec,
2009 x_return_message,
2010 x_return_status);
2011 --
2012 IF (l_debug <> -1) THEN
2013 rlm_core_sv.dpop(C_SDEBUG, 'The RLM Setup Terms record at the CUSTOMER_ITEM level has been inactivated');
2014 END IF;
2015 --
2016 WHEN NO_DATA_FOUND THEN
2017 --
2018 IF (l_debug <> -1) THEN
2019 rlm_core_sv.dlog(C_DEBUG, 'No data found');
2020 END IF;
2021 --
2022 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
2023 x_customer_id,
2024 x_terms_definition_level,
2025 x_terms_rec,
2026 x_return_message,
2027 x_return_status);
2028 --
2029 IF (l_debug <> -1) THEN
2030 rlm_core_sv.dpop(C_SDEBUG, 'No data found');
2031 END IF;
2032 --
2033 WHEN OTHERS THEN
2034 --
2035 x_terms_definition_level := NULL;
2036 x_return_status := FALSE;
2037 --
2038 IF (l_debug <> -1) THEN
2039 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
2040 rlm_core_sv.dpop(C_SDEBUG);
2041 END IF;
2042 --
2043 RAISE;
2044
2045 END populate_record_cust_item;
2046
2047
2048 /*=============================================================================
2049 PROCEDURE NAME: GetTPContext
2050
2051 DESCRIPTION: This procedure returns the tpcontext
2052
2053 PARAMETERS: x_customer_id IN NUMBER DEFAULT NULL
2054 x_ship_to_address_id IN NUMBER DEFAULT NULL
2055 x_customer_number OUT NOCOPY VARCHAR2
2056 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
2057 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
2058 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
2059 x_tp_group_code OUT NOCOPY VARCHAR2
2060
2061 ============================================================================*/
2062
2063
2064 PROCEDURE GetTPContext(
2065 x_customer_id IN NUMBER,
2066 x_ship_to_address_id IN NUMBER,
2067 x_customer_number OUT NOCOPY VARCHAR2,
2068 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2069 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
2070 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2071 x_tp_group_code OUT NOCOPY VARCHAR2)
2072 IS
2073
2074 BEGIN
2075 --
2076 IF (l_debug <> -1) THEN
2077 rlm_core_sv.dpush(C_DEBUG, 'GetTPContext');
2078 END IF;
2079
2080 IF x_customer_id IS NOT NULL THEN
2081
2082 IF x_ship_to_address_id IS NOT NULL THEN
2083 -- Following query is changed as per TCA obsolescence project.
2084 SELECT acct_site.ece_tp_location_code,
2085 ETG.tp_group_code
2086 INTO x_ship_to_ece_locn_code,
2087 x_tp_group_code
2088 FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2089 ece_tp_headers ETH,
2090 ece_tp_group ETG
2091 WHERE ACCT_SITE.CUST_ACCOUNT_ID = x_customer_id
2092 AND ACCT_SITE.CUST_ACCT_SITE_ID = x_ship_to_address_id
2093 AND ETH.tp_header_id = acct_site.tp_header_id
2094 AND ETG.tp_group_id = ETH.tp_group_id;
2095 END IF;
2096
2097 -- Following query is changed as per TCA obsolescence project.
2098 SELECT account_number
2099 INTO x_customer_number
2100 FROM HZ_CUST_ACCOUNTS CUST_ACCT
2101 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_customer_id;
2102
2103 END IF;
2104
2105 IF (l_debug <> -1) THEN
2106 rlm_core_sv.dlog(C_DEBUG, 'customer_number', x_customer_number);
2107 rlm_core_sv.dlog(C_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2108 rlm_core_sv.dlog(C_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2109 rlm_core_sv.dlog(C_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2110 rlm_core_sv.dlog(C_DEBUG, 'x_tp_group_code',x_tp_group_code);
2111 rlm_core_sv.dpop(C_DEBUG, 'Successful');
2112 END IF;
2113
2114 EXCEPTION
2115 WHEN NO_DATA_FOUND THEN
2116 --
2117 x_customer_number:=null;
2118 x_ship_to_ece_locn_code:=null;
2119 x_bill_to_ece_locn_code:=null;
2120 x_inter_ship_to_ece_locn_code:=null;
2121 x_tp_group_code:=null;
2122 --
2123 IF (l_debug <> -1) THEN
2124 rlm_core_sv.dlog(C_DEBUG, 'customer_number', x_customer_number);
2125 rlm_core_sv.dlog(C_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2126 rlm_core_sv.dlog(C_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2127 rlm_core_sv.dlog(C_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2128 rlm_core_sv.dlog(C_DEBUG, 'x_tp_group_code',x_tp_group_code);
2129 rlm_core_sv.dpop(C_DEBUG);
2130 END IF;
2131 --
2132 WHEN OTHERS THEN
2133 --
2134 IF (l_debug <> -1) THEN
2135 rlm_core_sv.dlog(C_SDEBUG, 'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2136 rlm_core_sv.dpop(C_DEBUG);
2137 END IF;
2138 --
2139 RAISE;
2140
2141 END GetTPContext;
2142
2143
2144 END rlm_setup_terms_sv;