[Home] [Help]
PACKAGE BODY: APPS.RLM_SETUP_TERMS_SV
Source
1 PACKAGE BODY RLM_SETUP_TERMS_SV AS
2 /* $Header: RLMSETTB.pls 120.1 2006/02/13 14:13:23 rlanka noship $ */
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 > SYSDATE);
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 > SYSDATE);
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,
427 x_terms_rec.cum_shipment_rule_code,
424 x_terms_rec.customer_id,
425 x_terms_rec.cum_control_code,
426 x_terms_rec.cum_org_level_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 <= sysdate THEN
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;
559 x_terms_rec.calc_cum_flag := 'Y';
556 --
557 END IF;
558 --
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
707
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 > SYSDATE);
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 > SYSDATE);
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,
861 x_terms_rec.supplier_shp_calendar_cd,
858 x_terms_rec.cum_yesterd_time_cutoff,
859 x_terms_rec.cust_assign_supplier_cd,
860 x_terms_rec.customer_rcv_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 <= sysdate THEN
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
989 x_terms_rec.match_within_key := RLM_CORE_SV.get_default_key;
986 x_terms_rec.match_within_key := v_match_within_key;
987 ELSE
988 /* By default, match_within_key is ABCDEFG */
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 --
1123 e_inactive_record EXCEPTION;
1120 v_ship_to_address_id NUMBER DEFAULT -1;
1121 v_ship_from_org_id NUMBER DEFAULT -1;
1122 v_customer_item_id NUMBER;
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 > SYSDATE);
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 > SYSDATE);
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,
1275 tp_attribute2,
1272 attribute14,
1273 attribute15,
1274 tp_attribute1,
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),
1409 cum_shipment_rule_code,
1406 schedule_hierarchy_code,
1407 unshipped_firm_disp_cd,
1408 unship_firm_cutoff_days,
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 INTO x_terms_rec.cum_control_code,
1417 x_terms_rec.critical_attribute_key,
1418 x_terms_rec.match_within_key,
1419 x_terms_rec.match_across_key,
1420 x_terms_rec.schedule_hierarchy_code,
1421 x_terms_rec.unshipped_firm_disp_cd,
1422 x_terms_rec.unship_firm_cutoff_days,
1423 x_terms_rec.cum_shipment_rule_code,
1424 x_terms_rec.cum_org_level_code,
1425 x_terms_rec.cum_yesterd_time_cutoff,
1426 x_terms_rec.customer_rcv_calendar_cd,
1427 x_terms_rec.supplier_shp_calendar_cd,
1428 x_terms_rec.cust_assign_supplier_cd,
1429 x_terms_rec.intransit_calc_basis
1430 FROM RLM_CUST_SHIPTO_TERMS
1431 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1432 AND CUSTOMER_ID = x_customer_id
1433 AND ADDRESS_ID = x_ship_to_address_id;
1434 --
1435 IF (l_debug <> -1) THEN
1436 rlm_core_sv.dlog(C_DEBUG, 'Picked up exceptional terms at address level');
1437 rlm_core_sv.dlog(C_DEBUG, 'After Item Level Select ');
1438 END IF;
1439 --
1440 /* By default, inactive_date is NULL */
1441 IF x_terms_rec.inactive_date is NOT NULL THEN
1442 IF x_terms_rec.inactive_date <= sysdate THEN
1443 raise e_inactive_record;
1444 END IF;
1445 END IF;
1446 --
1447 -- 4129188
1448 --
1449 IF (x_terms_rec.match_within_key IS NULL OR
1450 x_terms_rec.match_across_key IS NULL) THEN
1451 --{
1452 OPEN c_optional_match_cust(v_ship_from_org_id, x_customer_id);
1453 FETCH c_optional_match_cust INTO v_match_within_key, v_match_across_key;
1454 CLOSE c_optional_match_cust;
1455 --
1456 IF (l_debug <> -1) THEN
1457 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Within', v_match_within_key);
1458 rlm_core_sv.dlog(C_DEBUG, 'Cust. Level Match Across', v_match_across_key);
1459 END IF;
1460 --
1461 IF (x_terms_rec.match_within_key IS NULL) THEN
1462 --
1463 IF v_match_within_key IS NOT NULL THEN
1464 x_terms_rec.match_within_key := v_match_within_key;
1465 ELSE
1466 x_terms_rec.match_within_key := RLM_CORE_SV.get_default_key;
1467 END IF;
1468 --
1469 END IF;
1470 --
1471 IF (x_terms_rec.match_across_key IS NULL) THEN
1472 --
1473 IF v_match_across_key IS NOT NULL THEN
1474 x_terms_rec.match_across_key := v_match_across_key;
1475 ELSE
1476 x_terms_rec.match_across_key := RLM_CORE_SV.get_default_key;
1477 END IF;
1478 --
1479 END IF;
1480 --}
1481 END IF;
1482 --
1483 x_terms_definition_level := 'ADDRESS_ITEM';
1484 x_return_status := TRUE;
1485 --
1486 IF (l_debug <> -1) THEN
1487 rlm_core_sv.dpop(C_SDEBUG);
1488 END IF;
1489 --
1490 EXCEPTION
1491 --
1492 WHEN TOO_MANY_ROWS THEN
1493 --
1494 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_MULTIPLE_ROWS';
1495 rlm_message_sv.get_msg_text(
1496 x_message_name => x_terms_rec.msg_name,
1497 x_text => x_return_message);
1498 --
1499 x_terms_definition_level := NULL;
1500 --
1501 x_return_status := FALSE;
1502 --
1503 IF (l_debug <> -1) THEN
1504 rlm_core_sv.dlog(C_DEBUG, 'Too Many Rows ');
1505 rlm_core_sv.dpop(C_SDEBUG, 'There are more than one record of RLM Setup Terms at the CUSTOMER_ITEM level');
1506 END IF;
1507
1508
1509 WHEN e_no_default THEN
1510 --
1511 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_NO_DEFAULT';
1512 rlm_message_sv.get_msg_text(
1513 x_message_name => x_terms_rec.msg_name,
1514 x_text => x_return_message,
1515 x_token1 => 'CUST',
1516 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id),
1517 x_token2 => 'ST',
1518 x_value2 => RLM_CORE_SV.get_ship_to(x_ship_to_address_id),
1519 x_token3 => 'CI',
1520 x_value3 => RLM_CORE_SV.get_item_number(x_customer_item_id));
1521 --
1522 --x_terms_definition_level := NULL;
1523 --x_return_status := FALSE;
1524 --
1525 -- Bug 4888849 : Query next level of terms
1526 --
1527 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1528 x_customer_id,
1529 x_ship_to_address_id,
1530 x_customer_item_id,
1531 x_terms_definition_level,
1532 x_terms_rec,
1533 x_return_message,
1534 x_return_status);
1535 --
1536 IF (l_debug <> -1) THEN
1537 rlm_core_sv.dpop(C_SDEBUG);
1538 END IF;
1539 --
1540 WHEN e_inactive_record THEN
1541 --
1542 IF (l_debug <> -1) THEN
1543 rlm_core_sv.dlog(C_DEBUG, 'Inactive Record ');
1544 rlm_core_sv.dlog(C_DEBUG, 'Populating Rec Addres ');
1545 END IF;
1546 --
1550 x_customer_item_id,
1547 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1548 x_customer_id,
1549 x_ship_to_address_id,
1551 x_terms_definition_level,
1552 x_terms_rec,
1553 x_return_message,
1554 x_return_status);
1555 --
1556 IF (l_debug <> -1) THEN
1557 rlm_core_sv.dpop(C_SDEBUG, 'The RLM Setup Terms record at the CUSTOMER_ITEM level has been inactivated');
1558 END IF;
1559 --
1560 WHEN NO_DATA_FOUND THEN
1561 --
1562 IF (l_debug <> -1) THEN
1563 rlm_core_sv.dlog(C_DEBUG, 'No data found');
1564 END IF;
1565 --
1566 RLM_TPA_SV.populate_record_add(x_ship_from_org_id,
1567 x_customer_id,
1568 x_ship_to_address_id,
1569 x_customer_item_id,
1570 x_terms_definition_level,
1571 x_terms_rec,
1572 x_return_message,
1573 x_return_status);
1574 --
1575 IF (l_debug <> -1) THEN
1576 rlm_core_sv.dpop(C_SDEBUG, 'No data found');
1577 END IF;
1578 --
1579 WHEN OTHERS THEN
1580 --
1581 x_terms_definition_level := NULL;
1582 x_return_status := FALSE;
1583 --
1584 IF (l_debug <> -1) THEN
1585 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
1586 rlm_core_sv.dpop(C_SDEBUG);
1587 END IF;
1588 --
1589 RAISE;
1590
1591 END populate_record_item;
1592
1593 /*=============================================================================
1594
1595 PROCEDURE NAME: populate_record_cust_item
1596
1597 =============================================================================*/
1598
1599 PROCEDURE populate_record_cust_item (
1600 x_ship_from_org_id IN NUMBER,
1601 x_customer_id IN NUMBER,
1602 x_ship_to_address_id IN NUMBER,
1603 x_customer_item_id IN NUMBER,
1604 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
1605 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
1606 x_return_message IN OUT NOCOPY VARCHAR2,
1607 x_return_status OUT NOCOPY BOOLEAN)
1608 IS
1609 --
1610 v_ship_to_address_id NUMBER DEFAULT -1;
1611 v_ship_from_org_id NUMBER DEFAULT -1;
1612 v_customer_item_id NUMBER;
1613 e_inactive_record EXCEPTION;
1614 v_level VARCHAR2(30) DEFAULT NULL;
1615 e_no_default EXCEPTION;
1616 --
1617 BEGIN
1618 --
1619 IF (l_debug <> -1) THEN
1620 rlm_core_sv.dpush(C_SDEBUG, 'populate_record_cust_item');
1621 --global_atp
1622 rlm_core_sv.dlog(C_DEBUG, 'x_ship_from_org_id', x_ship_from_org_id );
1623 rlm_core_sv.dlog(C_DEBUG, 'x_customer_id', x_customer_id );
1624 rlm_core_sv.dlog(C_DEBUG, 'x_ship_to_address_id', x_ship_to_address_id );
1625 rlm_core_sv.dlog(C_DEBUG, 'x_customer_item_id', x_customer_item_id );
1626 rlm_core_sv.dlog(C_DEBUG, 'Before Item Level Select ');
1627 END IF;
1628 --
1629 v_ship_from_org_id := x_ship_from_org_id;
1630
1631 -- Before selecting the terms, check if the ship_from_org_id is null */
1632 -- global_atp
1633 IF x_ship_from_org_id IS NULL THEN
1634 --
1635 BEGIN
1636
1637 SELECT ship_from_org_id
1638 INTO v_ship_from_org_id
1639 FROM rlm_cust_item_terms
1640 WHERE customer_id = x_customer_id
1641 AND address_id IS NULL
1642 AND customer_item_id = x_customer_item_id
1643 AND (inactive_date IS NULL OR inactive_date > SYSDATE);
1644
1645 -- Proceed
1646 IF (l_debug <> -1) THEN
1647 rlm_core_sv.dlog(C_DEBUG, 'v_ship_from_org_id', v_ship_from_org_id );
1648 END IF;
1649
1650 EXCEPTION
1651 WHEN TOO_MANY_ROWS THEN
1652 -- Get the default
1653 BEGIN
1654 SELECT ship_from_org_id
1655 INTO v_ship_from_org_id
1656 FROM rlm_cust_item_terms
1657 WHERE customer_id = x_customer_id
1658 AND address_id IS NULL
1659 AND customer_item_id = x_customer_item_id
1660 AND NVL(default_ship_from,'N') = 'Y'
1661 AND (inactive_date IS NULL OR inactive_date > SYSDATE);
1662
1663 -- Proceed
1664 IF (l_debug <> -1) THEN
1665 rlm_core_sv.dlog(C_DEBUG, 'Default v_ship_from_org_id', v_ship_from_org_id );
1666 END IF;
1667
1668 EXCEPTION
1669 WHEN NO_DATA_FOUND THEN
1670 RAISE e_no_default;
1671 END;
1672
1673 WHEN NO_DATA_FOUND THEN
1674 RAISE;
1675
1676 WHEN e_no_default THEN
1677 RAISE;
1678
1679 WHEN OTHERS THEN
1680 RAISE;
1681
1682 END;
1683 --
1684 END IF;
1685
1686 -- Find Customer Item terms
1687 --
1688 --
1689 v_level := 'REGULAR_TERMS';
1690 --
1694 agreement_id,
1691 SELECT ship_from_org_id,
1692 address_id,
1693 header_id,
1695 agreement_name,
1696 future_agreement_id,
1697 future_agreement_name,
1698 round_to_std_pack_flag,
1699 ship_delivery_rule_name,
1700 ship_method,
1701 intransit_time,
1702 time_uom_code,
1703 std_pack_qty,
1704 price_list_id,
1705 use_edi_sdp_code_flag,
1706 pln_firm_day_to,
1707 pln_firm_day_from,
1708 pln_forecast_day_from,
1709 pln_forecast_day_to,
1710 pln_frozen_day_to,
1711 pln_frozen_day_from,
1712 seq_firm_day_from,
1713 seq_firm_day_to,
1714 seq_forecast_day_to,
1715 seq_forecast_day_from,
1716 seq_frozen_day_from,
1717 seq_frozen_day_to,
1718 shp_firm_day_from,
1719 shp_firm_day_to,
1720 shp_frozen_day_from,
1721 shp_frozen_day_to,
1722 shp_forecast_day_from,
1723 shp_forecast_day_to,
1724 pln_mrp_forecast_day_from,
1725 pln_mrp_forecast_day_to,
1726 shp_mrp_forecast_day_from,
1727 shp_mrp_forecast_day_to,
1728 seq_mrp_forecast_day_from,
1729 seq_mrp_forecast_day_to,
1730 demand_tolerance_above,
1731 demand_tolerance_below,
1732 customer_contact_id,
1733 freight_code,
1734 supplier_contact_id,
1735 attribute_category,
1736 tp_attribute_category,
1737 attribute1,
1738 attribute2,
1739 attribute4,
1740 attribute5,
1741 attribute6,
1742 attribute7,
1743 attribute8,
1744 attribute9,
1745 attribute10,
1746 attribute11,
1747 attribute12,
1748 attribute13,
1749 attribute14,
1750 attribute15,
1751 tp_attribute1,
1752 tp_attribute2,
1753 tp_attribute3,
1754 tp_attribute4,
1755 tp_attribute5,
1756 tp_attribute6,
1757 tp_attribute7,
1758 tp_attribute8,
1759 tp_attribute9,
1760 tp_attribute10,
1761 tp_attribute11,
1762 tp_attribute12,
1763 tp_attribute13,
1764 tp_attribute14,
1765 tp_attribute15,
1766 cust_item_terms_id,
1767 customer_item_id,
1768 calc_cum_flag,
1769 cust_item_status_code,
1770 inactive_date,
1771 pln_frozen_flag,
1772 shp_frozen_flag,
1773 seq_frozen_flag,
1774 issue_warning_drop_parts_flag,
1775 blanket_number,
1776 release_rule,
1777 release_time_frame,
1778 release_time_frame_uom,
1779 exclude_non_workdays_flag
1780 INTO x_terms_rec.ship_from_org_id,
1781 x_terms_rec.address_id,
1782 x_terms_rec.header_id,
1783 x_terms_rec.agreement_Id,
1784 x_terms_rec.agreement_name,
1785 x_terms_rec.future_agreement_id,
1786 x_terms_rec.future_agreement_name,
1787 x_terms_rec.round_to_std_pack_flag,
1788 x_terms_rec.ship_delivery_rule_name,
1789 x_terms_rec.ship_method,
1790 x_terms_rec.intransit_time,
1791 x_terms_rec.time_uom_code,
1792 x_terms_rec.std_pack_qty,
1793 x_terms_rec.price_list_id,
1794 x_terms_rec.use_edi_sdp_code_flag,
1795 x_terms_rec.pln_firm_day_to,
1796 x_terms_rec.pln_firm_day_from,
1797 x_terms_rec.pln_forecast_day_from,
1798 x_terms_rec.pln_forecast_day_to,
1799 x_terms_rec.pln_frozen_day_to,
1800 x_terms_rec.pln_frozen_day_from,
1801 x_terms_rec.seq_firm_day_from,
1802 x_terms_rec.seq_firm_day_to,
1803 x_terms_rec.seq_forecast_day_to,
1804 x_terms_rec.seq_forecast_day_from,
1805 x_terms_rec.seq_frozen_day_from,
1806 x_terms_rec.seq_frozen_day_to,
1807 x_terms_rec.shp_firm_day_from,
1808 x_terms_rec.shp_firm_day_to,
1809 x_terms_rec.shp_frozen_day_from,
1810 x_terms_rec.shp_frozen_day_to,
1811 x_terms_rec.shp_forecast_day_from,
1812 x_terms_rec.shp_forecast_day_to,
1813 x_terms_rec.pln_mrp_forecast_day_from,
1814 x_terms_rec.pln_mrp_forecast_day_to,
1815 x_terms_rec.shp_mrp_forecast_day_from,
1816 x_terms_rec.shp_mrp_forecast_day_to,
1817 x_terms_rec.seq_mrp_forecast_day_from,
1818 x_terms_rec.seq_mrp_forecast_day_to,
1819 x_terms_rec.demand_tolerance_above,
1820 x_terms_rec.demand_tolerance_below,
1821 x_terms_rec.customer_contact_id,
1822 x_terms_rec.freight_code,
1823 x_terms_rec.supplier_contact_id,
1824 x_terms_rec.attribute_category,
1825 x_terms_rec.tp_attribute_category,
1826 x_terms_rec.attribute1,
1827 x_terms_rec.attribute2,
1828 x_terms_rec.attribute4,
1829 x_terms_rec.attribute5,
1830 x_terms_rec.attribute6,
1831 x_terms_rec.attribute7,
1832 x_terms_rec.attribute8,
1833 x_terms_rec.attribute9,
1834 x_terms_rec.attribute10,
1838 x_terms_rec.attribute14,
1835 x_terms_rec.attribute11,
1836 x_terms_rec.attribute12,
1837 x_terms_rec.attribute13,
1839 x_terms_rec.attribute15,
1840 x_terms_rec.tp_attribute1,
1841 x_terms_rec.tp_attribute2,
1842 x_terms_rec.tp_attribute3,
1843 x_terms_rec.tp_attribute4,
1844 x_terms_rec.tp_attribute5,
1845 x_terms_rec.tp_attribute6,
1846 x_terms_rec.tp_attribute7,
1847 x_terms_rec.tp_attribute8,
1848 x_terms_rec.tp_attribute9,
1849 x_terms_rec.tp_attribute10,
1850 x_terms_rec.tp_attribute11,
1851 x_terms_rec.tp_attribute12,
1852 x_terms_rec.tp_attribute13,
1853 x_terms_rec.tp_attribute14,
1854 x_terms_rec.tp_attribute15,
1855 x_terms_rec.cust_item_terms_id,
1856 x_terms_rec.customer_item_id,
1857 x_terms_rec.calc_cum_flag,
1858 x_terms_rec.cust_item_status_code,
1859 x_terms_rec.inactive_date,
1860 x_terms_rec.pln_frozen_flag,
1861 x_terms_rec.shp_frozen_flag,
1862 x_terms_rec.seq_frozen_flag,
1863 x_terms_rec.issue_warning_drop_parts_flag,
1864 x_terms_rec.blanket_number,
1865 x_terms_rec.release_rule,
1866 x_terms_rec.release_time_frame,
1867 x_terms_rec.release_time_frame_uom,
1868 x_terms_rec.exclude_non_workdays_flag
1869 FROM RLM_CUST_ITEM_TERMS
1870 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1871 AND CUSTOMER_ID = x_customer_id
1872 AND ADDRESS_ID IS NULL
1873 AND CUSTOMER_ITEM_ID = x_customer_item_id;
1874 --
1875 v_level := 'EXCEPTIONAL_TERMS';
1876 --
1877 -- Select exceptional terms
1878 --
1879 SELECT cum_control_code,
1880 critical_attribute_key,
1881 NVL(match_within_key, rlm_core_sv.get_default_key),
1882 NVL(match_across_key, rlm_core_sv.get_default_key),
1883 schedule_hierarchy_code,
1884 unshipped_firm_disp_cd,
1885 unship_firm_cutoff_days,
1886 cum_shipment_rule_code,
1887 cum_org_level_code,
1888 cum_yesterd_time_cutoff,
1889 customer_rcv_calendar_cd,
1890 supplier_shp_calendar_cd,
1891 cust_assign_supplier_cd,
1892 intransit_calc_basis
1893 INTO
1894 x_terms_rec.cum_control_code,
1895 x_terms_rec.critical_attribute_key,
1896 x_terms_rec.match_within_key,
1897 x_terms_rec.match_across_key,
1898 x_terms_rec.schedule_hierarchy_code,
1899 x_terms_rec.unshipped_firm_disp_cd,
1900 x_terms_rec.unship_firm_cutoff_days,
1901 x_terms_rec.cum_shipment_rule_code,
1902 x_terms_rec.cum_org_level_code,
1903 x_terms_rec.cum_yesterd_time_cutoff,
1904 x_terms_rec.customer_rcv_calendar_cd,
1905 x_terms_rec.supplier_shp_calendar_cd,
1906 x_terms_rec.cust_assign_supplier_cd,
1907 x_terms_rec.intransit_calc_basis
1908 FROM RLM_CUST_SHIPTO_TERMS
1909 WHERE SHIP_FROM_ORG_ID = v_ship_from_org_id
1910 AND CUSTOMER_ID = x_customer_id
1911 AND ADDRESS_ID IS NULL;
1912 --
1913 IF (l_debug <> -1) THEN
1914 rlm_core_sv.dlog(C_DEBUG, 'Picked up exceptional terms at Customer level');
1915 rlm_core_sv.dlog(C_DEBUG, 'After Item Level Select ');
1916 END IF;
1917 --
1918 /* By default, inactive_date is NULL */
1919 IF x_terms_rec.inactive_date is NOT NULL THEN
1920 IF x_terms_rec.inactive_date <= sysdate THEN
1921 raise e_inactive_record;
1922 END IF;
1923 END IF;
1924 --
1925 IF x_terms_rec.match_within_key is NULL THEN
1926 x_terms_rec.match_within_key := rlm_core_sv.get_default_key;
1927 END IF;
1928 --
1929 IF x_terms_rec.match_across_key is NULL THEN
1930 x_terms_rec.match_across_key := rlm_core_sv.get_default_key;
1931 END IF;
1932 --
1933 x_terms_definition_level := 'CUSTOMER_ITEM';
1934 x_return_status := TRUE;
1935 --
1936 IF (l_debug <> -1) THEN
1937 rlm_core_sv.dpop(C_SDEBUG);
1938 END IF;
1939 --
1940 EXCEPTION
1941 --
1942 WHEN TOO_MANY_ROWS THEN
1943 --
1944 IF (l_debug <> -1) THEN
1945 rlm_core_sv.dlog(C_DEBUG, 'Too Many Rows ');
1946 END IF;
1947 --
1948 x_terms_rec.msg_name := 'RLM_SETUP_ITEM_MULTIPLE_ROWS';
1949 rlm_message_sv.get_msg_text(
1950 x_message_name => x_terms_rec.msg_name,
1951 x_text => x_return_message);
1952 --
1953 x_terms_definition_level := NULL;
1954 --
1955 x_return_status := FALSE;
1956 --
1957 IF (l_debug <> -1) THEN
1958 rlm_core_sv.dpop(C_SDEBUG, 'There are more than one record of RLM Setup Terms at the CUSTOMER_ITEM level');
1959 END IF;
1960 --
1961
1962 WHEN e_no_default THEN
1963 --
1964 IF (l_debug <> -1) THEN
1965 rlm_core_sv.dlog(C_DEBUG, 'No Default');
1966 END IF;
1967 --
1968 x_terms_rec.msg_name := 'RLM_SETUP_CITEM_NO_DEFAULT';
1969 rlm_message_sv.get_msg_text(
1970 x_message_name => x_terms_rec.msg_name,
1971 x_text => x_return_message,
1972 x_token1 => 'CUST',
1973 x_value1 => RLM_CORE_SV.get_customer_name(x_customer_id),
1974 x_token2 => 'CI',
1978 --x_return_status := FALSE;
1975 x_value2 => RLM_CORE_SV.get_item_number(x_customer_item_id));
1976 --
1977 --x_terms_definition_level := NULL;
1979 --
1980 -- Bug 4888849 : Query next level of setup terms
1981 --
1982 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
1983 x_customer_id,
1984 x_terms_definition_level,
1985 x_terms_rec,
1986 x_return_message,
1987 x_return_status);
1988 --
1989 IF (l_debug <> -1) THEN
1990 rlm_core_sv.dpop(C_SDEBUG);
1991 END IF;
1992
1993
1994 WHEN e_inactive_record THEN
1995 --
1996 IF (l_debug <> -1) THEN
1997 rlm_core_sv.dlog(C_DEBUG, 'Inactive Record ');
1998 rlm_core_sv.dlog(C_DEBUG, 'Populating Rec Customer ');
1999 END IF;
2000 --
2001 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
2002 x_customer_id,
2003 x_terms_definition_level,
2004 x_terms_rec,
2005 x_return_message,
2006 x_return_status);
2007 --
2008 IF (l_debug <> -1) THEN
2009 rlm_core_sv.dpop(C_SDEBUG, 'The RLM Setup Terms record at the CUSTOMER_ITEM level has been inactivated');
2010 END IF;
2011 --
2012 WHEN NO_DATA_FOUND THEN
2013 --
2014 IF (l_debug <> -1) THEN
2015 rlm_core_sv.dlog(C_DEBUG, 'No data found');
2016 END IF;
2017 --
2018 RLM_TPA_SV.populate_record_cust(x_ship_from_org_id,
2019 x_customer_id,
2020 x_terms_definition_level,
2021 x_terms_rec,
2022 x_return_message,
2023 x_return_status);
2024 --
2025 IF (l_debug <> -1) THEN
2026 rlm_core_sv.dpop(C_SDEBUG, 'No data found');
2027 END IF;
2028 --
2029 WHEN OTHERS THEN
2030 --
2031 x_terms_definition_level := NULL;
2032 x_return_status := FALSE;
2033 --
2034 IF (l_debug <> -1) THEN
2035 rlm_core_sv.dlog(C_DEBUG, 'SQL Error', SQLERRM);
2036 rlm_core_sv.dpop(C_SDEBUG);
2037 END IF;
2038 --
2039 RAISE;
2040
2041 END populate_record_cust_item;
2042
2043
2044 /*=============================================================================
2045 PROCEDURE NAME: GetTPContext
2046
2047 DESCRIPTION: This procedure returns the tpcontext
2048
2049 PARAMETERS: x_customer_id IN NUMBER DEFAULT NULL
2050 x_ship_to_address_id IN NUMBER DEFAULT NULL
2051 x_customer_number OUT NOCOPY VARCHAR2
2052 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
2056
2053 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
2054 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
2055 x_tp_group_code OUT NOCOPY VARCHAR2
2057 ============================================================================*/
2058
2059
2060 PROCEDURE GetTPContext(
2061 x_customer_id IN NUMBER,
2062 x_ship_to_address_id IN NUMBER,
2063 x_customer_number OUT NOCOPY VARCHAR2,
2064 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2065 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
2066 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2067 x_tp_group_code OUT NOCOPY VARCHAR2)
2068 IS
2069
2070 BEGIN
2071 --
2072 IF (l_debug <> -1) THEN
2073 rlm_core_sv.dpush(C_DEBUG, 'GetTPContext');
2074 END IF;
2075
2076 IF x_customer_id IS NOT NULL THEN
2077
2078 IF x_ship_to_address_id IS NOT NULL THEN
2079 -- Following query is changed as per TCA obsolescence project.
2080 SELECT acct_site.ece_tp_location_code,
2081 ETG.tp_group_code
2082 INTO x_ship_to_ece_locn_code,
2083 x_tp_group_code
2084 FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2085 ece_tp_headers ETH,
2086 ece_tp_group ETG
2087 WHERE ACCT_SITE.CUST_ACCOUNT_ID = x_customer_id
2088 AND ACCT_SITE.CUST_ACCT_SITE_ID = x_ship_to_address_id
2089 AND ETH.tp_header_id = acct_site.tp_header_id
2090 AND ETG.tp_group_id = ETH.tp_group_id;
2091 END IF;
2092
2093 -- Following query is changed as per TCA obsolescence project.
2094 SELECT account_number
2095 INTO x_customer_number
2096 FROM HZ_CUST_ACCOUNTS CUST_ACCT
2097 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_customer_id;
2098
2099 END IF;
2100
2101 IF (l_debug <> -1) THEN
2102 rlm_core_sv.dlog(C_DEBUG, 'customer_number', x_customer_number);
2103 rlm_core_sv.dlog(C_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2104 rlm_core_sv.dlog(C_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2105 rlm_core_sv.dlog(C_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2106 rlm_core_sv.dlog(C_DEBUG, 'x_tp_group_code',x_tp_group_code);
2107 rlm_core_sv.dpop(C_DEBUG, 'Successful');
2108 END IF;
2109
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN
2112 --
2113 x_customer_number:=null;
2114 x_ship_to_ece_locn_code:=null;
2115 x_bill_to_ece_locn_code:=null;
2116 x_inter_ship_to_ece_locn_code:=null;
2117 x_tp_group_code:=null;
2118 --
2119 IF (l_debug <> -1) THEN
2120 rlm_core_sv.dlog(C_DEBUG, 'customer_number', x_customer_number);
2121 rlm_core_sv.dlog(C_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2122 rlm_core_sv.dlog(C_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2123 rlm_core_sv.dlog(C_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2124 rlm_core_sv.dlog(C_DEBUG, 'x_tp_group_code',x_tp_group_code);
2125 rlm_core_sv.dpop(C_DEBUG);
2126 END IF;
2127 --
2128 WHEN OTHERS THEN
2129 --
2130 IF (l_debug <> -1) THEN
2131 rlm_core_sv.dlog(C_SDEBUG, 'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2132 rlm_core_sv.dpop(C_DEBUG);
2133 END IF;
2134 --
2135 RAISE;
2136
2137 END GetTPContext;
2138
2139
2140 END rlm_setup_terms_sv;