[Home] [Help]
PACKAGE BODY: APPS.RLM_BLANKET_SV
Source
1 PACKAGE BODY RLM_BLANKET_SV as
2 /*$Header: RLMDPBOB.pls 120.6.12010000.2 2008/08/08 13:28:41 suppal ship $*/
3 --
4 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
5 g_line_id NUMBER := NULL; --Bugfix 6884912
6 g_req_flag NUMBER := 0; --Bugfix 6884912
7 --
8
9 /*============================================================================
10
11 PROCEDURE DeriveRSO
12
13 ==============================================================================*/
14 PROCEDURE DeriveRSO(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
15 x_Group_rec IN RLM_DP_SV.t_Group_rec,
16 x_return_status OUT NOCOPY NUMBER) IS
17 --
18 CURSOR c_lines IS
19 SELECT *
20 FROM rlm_interface_lines_all
21 WHERE header_id = x_Sched_rec.header_id AND
22 ship_from_org_id = x_Group_rec.ship_from_org_id AND
23 ship_to_address_id = x_Group_rec.ship_to_address_id AND
24 customer_item_id = x_Group_rec.customer_item_id AND
25 blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
26 item_detail_type IN (k_FIRM, k_PAST_DUE_FIRM, k_FORECAST) AND
27 process_status <> rlm_core_sv.k_PS_PROCESSED
28 ORDER BY request_date;
29 --
30 v_rso_hdr_id NUMBER;
31 v_count NUMBER;
32 v_start_date DATE;
33 v_end_date DATE;
34 v_maxend_date DATE;
35 v_minstart_date DATE;
36 l_index NUMBER;
37 --4302492
38 v_req_date DATE;
39 v_fence_days NUMBER;
40 v_RFFlag VARCHAR2(1) := 'N';
41 l_Group_rec RLM_DP_SV.t_Group_rec;
42 --
43 BEGIN
44 --
45 IF (l_debug <> -1) THEN
46 rlm_core_sv.dpush(C_SDEBUG, 'DeriveRSO');
47 rlm_core_sv.dlog(C_DEBUG, 'Ship From Org', x_Group_Rec.ship_from_org_id);
48 rlm_core_sv.dlog(C_DEBUG, 'Ship To Address ID', x_Group_rec.ship_to_address_id);
49 rlm_core_sv.dlog(C_DEBUG, 'Customer Item Id', x_Group_rec.customer_item_id);
50 rlm_core_sv.dlog(C_DEBUG, 'Blanket Number', x_Group_rec.setup_terms_rec.blanket_number);
51 rlm_core_sv.dlog(C_DEBUG, 'Release Rule', x_Group_rec.setup_terms_rec.release_rule);
52 rlm_core_sv.dlog(C_DEBUG, 'Release Time Frame', x_Group_rec.setup_terms_rec.release_time_frame);
53 END IF;
54 --
55 x_return_status := rlm_core_sv.k_PROC_SUCCESS;
56 --
57 g_LineIdTab.DELETE;
58 g_RSOIdTab.DELETE;
59 l_index := 1;
60 g_line_id := NULL; --Bugfix 6884912
61 --
62 v_start_date := k_DNULL;
63 v_end_date := k_DNULL;
64 --
65 FOR c_lines_rec IN c_lines LOOP
66 --
67 IF (l_debug <> -1) THEN
68 rlm_core_sv.dlog(C_DEBUG, '====================================');
69 rlm_core_sv.dlog(C_DEBUG, 'Processing line', c_lines_rec.line_id);
70 rlm_core_sv.dlog(C_DEBUG, 'Request Date', c_lines_rec.request_date);
71 rlm_core_sv.dlog(C_DEBUG, 'Item Detail Type', c_lines_rec.item_Detail_type);
72 END IF;
73 --
74 g_line_id := c_lines_rec.line_id; --Bugfix 6884912
75 -- 4302492 :start
76 IF x_Sched_rec.schedule_source <> 'MANUAL' THEN
77 --{
78 IF x_Sched_rec.Schedule_type = k_PLANNING THEN
79 v_RFFlag := x_Group_rec.setup_terms_rec.pln_frozen_flag;
80 ELSIF x_Sched_rec.Schedule_type = k_SHIPPING THEN
81 v_RFFlag := x_Group_rec.setup_terms_rec.shp_frozen_flag;
82 ELSIF x_Sched_rec.Schedule_type = k_SEQUENCED THEN
83 v_RFFlag := x_Group_rec.setup_terms_rec.seq_frozen_flag;
84 END IF;
85 --
86 IF (l_debug <> -1) THEN
87 rlm_core_sv.dlog(C_DEBUG, 'v_RFFlag is:', v_RFFlag);
88 rlm_core_sv.dlog(C_DEBUG, 'before calling CalFenceDays');
89 END IF;
90 --
91 CalFenceDays(x_Sched_rec,x_Group_rec,v_fence_days);
92 --
93 IF (l_debug <> -1) THEN
94 rlm_core_sv.dlog(C_DEBUG, 'v_fence_days is:', v_fence_days);
95 END IF;
96 --
97 l_Group_rec:= x_group_rec;
98 l_Group_rec.frozen_days := v_fence_days;
99 --
100 IF v_RFFlag = 'Y' AND RLM_RD_SV.IsFrozen(TRUNC(SYSDATE),l_Group_rec,c_lines_rec.request_date)THEN
101 v_req_date := TRUNC(SYSDATE) + nvl(v_fence_days,0) ; --Bugfix 6485729
102 ELSE
103 v_req_date := c_lines_rec.request_date;
104 END IF;
105 --}
106 ELSE -- if schedule is MANUAL
107 v_req_date := c_lines_rec.request_date;
108 END IF;
109 -- 4302492 :end
110
111 --4302492 :Use v_req_date instead of c_lines_rec.request_date
112 IF NOT (TRUNC(v_req_date) >= TRUNC(v_start_date) AND
113 TRUNC(v_req_date) < TRUNC(v_end_date)+1)
114 THEN
115 --
116 QueryRSO(x_Sched_rec.customer_id, v_req_date,
117 c_lines_rec.customer_item_id, x_Group_rec, v_rso_hdr_id,
118 v_start_date, v_end_date, v_maxend_date, v_minstart_date);
119 --
120 END IF;
121 --
122 IF (l_debug <> -1) THEN
123 rlm_core_sv.dlog(C_DEBUG, 'v_rso_hdr_id', v_rso_hdr_id);
124 rlm_core_sv.dlog(C_DEBUG, 'v_start_date', v_start_date);
125 rlm_core_sv.dlog(C_DEBUG, 'v_end_date', v_end_date);
126 rlm_core_sv.dlog(C_DEBUG, 'v_maxend_date', v_maxend_date);
127 rlm_core_sv.dlog(C_DEBUG, 'v_minstart_date', v_minstart_date);
128 END IF;
129 --
130 -- Bug 4901148 : QueryRSO() returns one of the following values
131 -- * A valid RSO Header ID, corresponding to a record in oe_order_headers
132 -- * -1 if the RSO Header is closed
133 -- * -99 if it found an orphan RSO in rlm_blanket_rso table.
134 -- * NULL if no RSO encompasses the request date of the line.
135 --
136 IF (v_rso_hdr_id IS NOT NULL) THEN
137 --{
138 IF (v_rso_hdr_id = -1) THEN
139 --{
140 IF (l_debug <> -1) THEN
141 rlm_core_sv.dlog(C_DEBUG, 'Recreating RSO, since DSP found a closed one');
142 END IF;
143 --
144 RLM_TPA_SV.CreateRSOHeader(x_Sched_rec, x_Group_rec, v_rso_hdr_id);
145 RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date,
146 v_end_date);
147 --}
148 ELSIF (v_rso_hdr_id = -99) THEN
149 --{
150 IF (l_debug <> -1) THEN
151 rlm_core_sv.dlog(C_DEBUG, 'Orphan record in RLM table, so recreate RSO and log exception');
152 END IF;
153 --
154 rlm_message_sv.app_error(
155 x_ExceptionLevel => rlm_message_sv.k_warn_level,
156 x_MessageName => 'RLM_ORPHAN_RSO_FOUND',
157 x_InterfaceHeaderId => x_Sched_rec.header_id,
158 x_InterfaceLineId => c_lines_rec.line_id,
159 x_ScheduleHeaderId => x_Sched_rec.schedule_header_id,
160 x_ScheduleLineId => NULL,
161 x_Token1 => 'BLANKET',
162 x_value1 => x_Group_rec.setup_terms_rec.blanket_number,
163 x_Token2 => 'START_DATE',
164 x_Value2 => v_start_date,
165 x_Token3 => 'END_DATE',
166 x_Value3 => v_end_date);
167 --
168 RLM_TPA_SV.CreateRSOHeader(x_Sched_rec, x_Group_rec, v_rso_hdr_id);
169 RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date,
170 v_end_date);
171 --}
172 END IF;
173 --
174 ELSIF v_rso_hdr_id IS NULL THEN
175 --{
176 --4302492 : Use v_req_date instead of c_lines_rec.request_date
177 --
178 IF TRUNC(v_req_date) <= TRUNC(v_minstart_date) THEN
179 --{
180 LOOP
181 --
182 CalcPriorEffectDates(x_Group_rec, v_req_date,
183 v_start_date, v_end_date, v_minstart_date);
184 RLM_TPA_SV.CreateRSOHeader(x_Sched_rec, x_Group_rec, v_rso_hdr_id);
185 RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date, v_end_date);
186 --
187 EXIT WHEN (TRUNC(v_req_date) >= TRUNC(v_start_date) AND
188 TRUNC(v_req_date) < TRUNC(v_end_date)+1);
189 --
190 END LOOP;
191 --}
192 ELSIF TRUNC(v_req_date) >= TRUNC(v_maxend_date) THEN
193 --{
194 LOOP
195 --
196 --4302492 :Use v_req_date instead of c_lines_rec.request_date
197 --
198 CalcEffectiveDates(x_Group_rec, v_req_date,
199 v_start_date, v_end_date, v_maxend_date);
200 --Bugfix 6884912 Start
201 g_req_flag := 0;
202 IF c_lines_rec.request_date > v_end_date THEN
203 g_req_flag := 1;
204 END IF;
205 --Bugfix 6884912 End
206
207 RLM_TPA_SV.CreateRSOHeader(x_Sched_rec, x_Group_rec, v_rso_hdr_id);
208 RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date, v_end_date);
209 --
210 EXIT WHEN (TRUNC(v_req_date) >= TRUNC(v_start_date) AND
211 TRUNC(v_req_date) < TRUNC(v_end_date)+1);
212 --
213 END LOOP;
214 --}
215 END IF;
216 --}
217 END IF; /* v_rso_hdr_id is null */
218 --
219 c_lines_rec.order_header_id := v_rso_hdr_id;
220 --
221 g_LineIdTab(l_index) := c_lines_rec.line_id;
222 g_RSOIdTab(l_index) := v_rso_hdr_id;
223 --
224 IF (l_debug <> -1) THEN
225 rlm_core_sv.dlog(C_DEBUG, 'Line id', g_LineIdTab(l_index));
226 rlm_core_sv.dlog(C_DEBUG, 'RSO Id', g_RSOIdTab(l_index));
227 END IF;
228 --
229 l_index := l_index + 1;
230 --
231 END LOOP;
232 --
233 --Bug Fix 4254471 Added parameter to procedure
234 UpdateLinesWithRSO(x_Sched_rec.schedule_header_id);
235 --
236 IF (l_debug <> -1) THEN
237 rlm_core_sv.dpop(C_SDEBUG);
238 END IF;
239 --
240 EXCEPTION
241 --
242 WHEN e_RSOCreationError THEN
243 --
244 x_return_Status := rlm_core_sv.k_PROC_ERROR;
245 --
246 IF (l_debug <> -1) THEN
247 rlm_core_sv.dpop(C_SDEBUG, 'e_RSOCreationError');
248 END IF;
249 --
250 WHEN OTHERS THEN
251 x_return_Status := RLM_CORE_SV.k_PROC_ERROR;
252 rlm_message_sv.sql_error('RLM_BLANKET_SV.DeriveRSO', '040');
253 --
254 IF (l_debug <> -1) THEN
255 rlm_core_sv.dlog(C_DEBUG, 'When others of DeriveRSO');
256 rlm_core_sv.dpop(C_SDEBUG,'DeriveRSO EXCEPTION: '||SUBSTR(SQLERRM,1,200));
257 END IF;
258 --
259 END DeriveRSO;
260
261
262
263 /*============================================================================
264
265 PROCEDURE QueryRSO
266
267 ==============================================================================*/
268 PROCEDURE QueryRSO(p_customer_id IN NUMBER,
269 p_request_date IN DATE,
270 p_cust_item_id IN NUMBER,
271 x_Group_rec IN RLM_DP_SV.t_Group_rec,
272 x_rso_hdr_id OUT NOCOPY NUMBER,
273 x_start_date OUT NOCOPY DATE,
274 x_end_date OUT NOCOPY DATE,
275 x_maxend_date OUT NOCOPY DATE,
276 x_minstart_date OUT NOCOPY DATE) IS
277 --
278 CURSOR rlm_rso_pi IS
279 SELECT max(decode(oe.open_flag, 'Y', rso_hdr_id, 'N', -1, -99)),
280 effective_start_date, effective_end_date
281 FROM RLM_BLANKET_RSO rlm, OE_ORDER_HEADERS oe
282 WHERE customer_id = p_customer_id AND
283 rlm.blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
284 customer_item_id = p_cust_item_id AND
285 rlm.rso_hdr_id = oe.header_id(+)
286 GROUP BY effective_start_date, effective_end_date
287 ORDER BY effective_start_date, effective_end_date; --Bugfix 6759544
288 --
289 CURSOR rlm_rso_ai IS
290 SELECT max(decode(oe.open_flag, 'Y', rso_hdr_id, 'N', -1, -99)),
291 effective_start_date, effective_end_date
292 FROM RLM_BLANKET_RSO rlm, OE_ORDER_HEADERS oe
293 WHERE customer_id = p_customer_id AND
294 rlm.blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
295 rlm.customer_item_id = k_NNULL AND
296 rlm.rso_hdr_id = oe.header_id(+)
297 GROUP BY effective_start_date, effective_end_date
298 ORDER BY effective_start_date, effective_end_date; --Bugfix 6759544
299 --
300 v_start_date DATE;
301 v_end_date DATE;
302 v_maxend_date DATE;
303 v_minstart_date DATE;
304 v_rsohdr_id NUMBER;
305 v_rel_rule VARCHAR2(3);
306 v_first BOOLEAN;
307 --
308 BEGIN
309 --
310 IF (l_debug <> -1) THEN
311 rlm_core_sv.dpush(C_SDEBUG, 'QueryRSO');
312 END IF;
313 --
314 v_rel_rule := x_Group_rec.setup_terms_rec.release_rule;
315 --
316 IF (l_debug <> -1) THEN
317 rlm_core_sv.dlog(C_DEBUG, 'p_customer_id', p_customer_id);
318 rlm_core_sv.dlog(C_DEBUG, 'p_blanket_number', x_Group_rec.setup_terms_rec.blanket_number);
319 rlm_core_sv.dlog(C_DEBUG, 'p_request_date', p_request_date);
320 rlm_core_sv.dlog(C_DEBUG, 'p_cust_item_id', p_cust_item_id);
321 rlm_core_sv.dlog(C_DEBUG, 'Release Creation Rule', v_rel_rule);
322 rlm_core_sv.dlog(C_DEBUG, 'Release Time Frame', x_Group_rec.setup_terms_rec.release_time_frame);
323 END IF;
324 --
325 v_first := TRUE;
326 --
327 IF v_rel_rule = 'PI' THEN
328 --
329 OPEN rlm_rso_pi;
330 FETCH rlm_rso_pi INTO v_rsohdr_id, v_start_date, v_end_date;
331 --
332 WHILE rlm_rso_pi%FOUND LOOP
333 --
334 IF (TRUNC(p_request_date) >= TRUNC(v_start_date) AND
335 TRUNC(p_request_date) < TRUNC(v_end_date) + 1) THEN
336 --
337 IF (l_debug <> -1) THEN
338 rlm_core_sv.dlog(C_DEBUG, 'Inside PI if');
339 END IF;
340 --
341 x_rso_hdr_id := v_rsohdr_id;
342 x_start_date := v_start_date;
343 x_end_date := v_end_date;
344 --
345 EXIT;
346 --
347 END IF;
348 --
349 IF v_first THEN
350 v_minstart_date := v_start_date;
351 v_first := FALSE;
352 END IF;
353 --
354 v_maxend_date := v_end_date;
355 FETCH rlm_rso_pi INTO v_rsohdr_id, v_start_date, v_end_date;
356 --
357 END LOOP;
358 --
359 CLOSE rlm_rso_pi;
360 --
361 ELSE
362 --
363 OPEN rlm_rso_ai;
364 FETCH rlm_rso_ai INTO v_rsohdr_id, v_start_date, v_end_date;
365 --
366 WHILE rlm_rso_ai%FOUND LOOP
367 --
368 IF (TRUNC(p_request_date) >= v_start_date AND
369 TRUNC(p_request_date) < TRUNC(v_end_date)+1) THEN
370 --
371 IF (l_debug <> -1) THEN
372 rlm_core_sv.dlog(C_DEBUG, 'Inside AI if');
373 END IF;
374 --
375 x_rso_hdr_id := v_rsohdr_id;
376 x_start_date := v_start_date;
377 x_end_date := v_end_date;
378 --
379 EXIT;
380 --
381 END IF;
382 --
383 IF v_first THEN
384 v_minstart_date := v_start_date;
385 v_first := FALSE;
386 END IF;
387 --
388 v_maxend_date := v_end_date;
389 FETCH rlm_rso_ai INTO v_rsohdr_id, v_start_date, v_end_date;
390 --
391 END LOOP;
392 --
393 CLOSE rlm_rso_ai;
394 --
395 END IF;
396 --
397 x_start_date := NVL(v_start_date, k_DNULL);
398 x_end_date := NVL(v_end_date, k_DNULL);
399 --
400 x_maxend_date := NVL(v_maxend_date, k_DNULL);
401 x_minstart_date := NVL(v_minstart_date, k_DNULL);
402 --
403 IF (l_debug <> -1) THEN
404 rlm_core_sv.dpop(C_SDEBUG, x_rso_hdr_id);
405 END IF;
406 --
407 EXCEPTION
408 --
409 WHEN OTHERS THEN
410 --
411 IF (l_debug <> -1) THEN
412 rlm_core_sv.dpop(C_SDEBUG,'QueryRSO EXCEPTION: '||SUBSTR(SQLERRM,1,200));
413 END IF;
414 --
415 RAISE;
416 --
417 END QueryRSO;
418
419
420 /*============================================================================
421
422 PROCEDURE CalcEffectiveDates
423
424 ==============================================================================*/
425 PROCEDURE CalcEffectiveDates(x_Group_rec IN RLM_DP_SV.t_Group_rec,
426 p_request_date IN DATE,
427 x_start_date OUT NOCOPY DATE,
428 x_end_date OUT NOCOPY DATE,
429 x_maxend_date IN OUT NOCOPY DATE) IS
430 --
431 BEGIN
432 --
433 IF (l_debug <> -1) THEN
434 rlm_core_sv.dpush(C_SDEBUG, 'CalcEffectiveDates');
435 END IF;
436 --
437 IF x_maxend_date = k_DNULL THEN
438 --
439 IF (l_debug <> -1) THEN
440 rlm_core_sv.dlog(C_DEBUG, 'This will the first entry of its type in the RLM_RSO table');
441 END IF;
442 --
443 IF to_char(p_request_date, 'D') = g_SundayDOW THEN
444 --
445 x_end_date := p_request_date;
446 x_start_date := x_end_date - (x_Group_rec.setup_terms_rec.release_time_frame * 7) + 1;
447 --
448 ELSE
449 --
450 x_start_date := p_request_date - (to_number(to_char(p_request_date, 'D')) - g_MondayDOW);
451 x_end_date := x_start_date + (x_Group_rec.setup_terms_rec.release_time_frame * 7) - 1;
452 --
453 END IF;
454 --
455 ELSE
456 --
457 IF (l_debug <> -1) THEN
458 rlm_core_sv.dlog(C_DEBUG, 'Using context of existing RSOs');
459 rlm_core_sv.dlog(C_DEBUG, 'End effective date of last RSO', x_maxend_date);
460 END IF;
461 --
462 x_start_date := x_maxend_date + 1;
463 x_end_date := x_start_date + (x_Group_rec.setup_terms_rec.release_time_frame * 7) - 1;
464 --
465 END IF;
466 --
467 x_maxend_date := x_end_date;
468 --
469 IF (l_debug <> -1) THEN
470 rlm_core_sv.dlog(C_DEBUG, 'Effective start date of new RSO', x_start_date);
471 rlm_core_sv.dlog(C_DEBUG, 'Effective end date of new RSO', x_end_date);
472 rlm_core_sv.dlog(C_DEBUG, 'Max Effec. end date', x_maxend_date);
473 rlm_core_sv.dpop(C_SDEBUG);
474 END IF;
475 --
476 EXCEPTION
477 --
478 WHEN OTHERS THEN
479 --
480 IF (l_debug <> -1) THEN
481 rlm_core_sv.dpop(C_SDEBUG,'CalcEffectiveDates EXCEPTION: '||SUBSTR(SQLERRM,1,200));
482 END IF;
483 --
484 RAISE;
485 --
486 END CalcEffectiveDates;
487
488
489
490 /*============================================================================
491
492 PROCEDURE CalcPriorEffectDates
493
494 ==============================================================================*/
495 PROCEDURE CalcPriorEffectDates(x_Group_rec IN RLM_DP_SV.t_Group_rec,
496 p_request_date IN DATE,
497 x_start_date OUT NOCOPY DATE,
498 x_end_date OUT NOCOPY DATE,
499 x_minstart_date IN OUT NOCOPY DATE) IS
500 BEGIN
501 --
502 IF (l_debug <> -1) THEN
503 rlm_core_sv.dpush(C_SDEBUG, 'CalcPriorEffectDates');
504 rlm_core_sv.dlog(C_DEBUG, 'x_minstart_date', x_minstart_date);
505 END IF;
506 --
507 x_end_date := x_minstart_date - 1;
508 x_start_date := x_end_date - (7 * x_Group_rec.setup_terms_rec.release_time_frame) + 1;
509 x_minstart_date := x_start_date;
510 --
511 IF (l_debug <> -1) THEN
512 rlm_core_sv.dlog(C_DEBUG, 'x_start_date', x_start_date);
513 rlm_core_sv.dlog(C_DEBUG, 'x_end_date', x_end_date);
514 rlm_core_sv.dlog(C_DEBUG, 'New x_minstart_date', x_minstart_date);
515 rlm_core_sv.dpop(C_SDEBUG);
516 END IF;
517 --
518 EXCEPTION
519 --
520 WHEN OTHERS THEN
521 --
522 IF (l_debug <> -1) THEN
523 rlm_core_sv.dpop(C_SDEBUG,'CalcPriorEffectDates EXCEPTION: '||SUBSTR(SQLERRM,1,200));
524 END IF;
525 --
526 RAISE;
527 --
528 END CalcPriorEffectDates;
529
530
531 /*============================================================================
532
533 PROCEDURE InsertRSO
534
535 ==============================================================================*/
536 PROCEDURE InsertRSO(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
537 x_Group_rec IN RLM_DP_SV.t_Group_rec,
538 p_rso_hdr_id IN NUMBER,
539 p_start_date IN DATE,
540 p_end_date IN DATE) IS
541 --
542 v_customer_item_id NUMBER;
543 --
544 BEGIN
545 --
546 IF (l_debug <> -1) THEN
547 rlm_core_sv.dpush(C_SDEBUG, 'InsertRSO');
548 rlm_core_sv.dlog(C_DEBUG, 'Customer id', x_Sched_rec.customer_id);
549 rlm_core_sv.dlog(C_DEBUG, 'Blanket Number', x_Group_rec.setup_terms_rec.blanket_number);
550 rlm_core_sv.dlog(C_DEBUG, 'Release Rule', x_Group_rec.setup_terms_rec.release_rule);
551 rlm_core_sv.dlog(C_DEBUG, 'p_rso_hdr_id', p_rso_hdr_id);
552 rlm_core_sv.dlog(C_DEBUG, 'Customer Item Id', x_Group_rec.customer_item_id);
553 rlm_core_sv.dlog(C_DEBUG, 'p_start_date', p_start_date);
554 rlm_core_sv.dlog(C_DEBUG, 'p_end_date', p_end_date);
555 END IF;
556 --
557 IF x_Group_rec.setup_terms_rec.release_rule = 'AI' THEN
558 v_customer_item_id := k_NNULL;
559 ELSE
560 v_customer_item_id := x_Group_rec.customer_item_id;
561 END IF;
562 --
563 IF (l_debug <> -1) THEN
564 rlm_core_sv.dlog(C_DEBUG, 'v_customer_item_id', v_customer_item_id);
565 END IF;
566 --
567 INSERT INTO RLM_BLANKET_RSO
568 (
569 customer_id, blanket_number, rso_hdr_id,
570 customer_item_id, effective_start_date, effective_end_date
571 )
572 VALUES
573 (
574 x_Sched_rec.customer_id, x_Group_rec.setup_terms_rec.blanket_number,
575 p_rso_hdr_id, v_customer_item_id, TRUNC(p_start_date), TRUNC(p_end_date)
576 );
577 --
578 IF (l_debug <> -1) THEN
579 rlm_core_sv.dpop(C_SDEBUG);
580 END IF;
581 --
582 EXCEPTION
583 --
584 WHEN OTHERS THEN
585 --
586 IF (l_debug <> -1) THEN
587 rlm_core_sv.dpop(C_SDEBUG,'InsertRSO EXCEPTION: '||SUBSTR(SQLERRM,1,200));
588 END IF;
589 --
590 RAISE;
591 --
592 END InsertRSO;
593
594
595 /*============================================================================
596
597 PROCEDURE CreateRSOHeader
598
599 ==============================================================================*/
600 PROCEDURE CreateRSOHeader(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
601 x_Group_rec IN RLM_DP_SV.t_Group_rec,
602 x_rso_hdr_id OUT NOCOPY NUMBER) IS
603 --
604 l_oe_header_rec oe_order_pub.header_rec_type;
605 l_oe_header_val_rec oe_order_pub.header_val_rec_type;
606 l_oe_header_adj_tbl oe_order_pub.header_adj_tbl_type;
607 l_oe_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
608 l_oe_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
609 l_oe_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
610 l_oe_line_tbl oe_order_pub.line_tbl_type;
611 l_oe_line_val_tbl oe_order_pub.line_val_tbl_type;
612 l_oe_header_out_rec oe_order_pub.header_rec_type;
613 l_oe_header_val_out_rec oe_order_pub.header_val_rec_type;
614 l_oe_header_adj_out_tbl oe_order_pub.header_adj_tbl_type;
615 l_oe_header_adj_val_out_tbl oe_order_pub.header_adj_val_tbl_type;
616 l_oe_Header_price_Att_out_tbl oe_order_pub.Header_Price_Att_Tbl_Type;
617 l_oe_Header_Adj_Att_out_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;
618 l_oe_Header_Adj_Assoc_out_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;
619 l_oe_header_scredit_out_tbl oe_order_pub.header_scredit_tbl_type;
620 l_oe_hdr_scdt_val_out_tbl oe_order_pub.header_scredit_val_tbl_type;
621 l_oe_line_out_tbl oe_order_pub.line_tbl_type;
622 l_oe_line_val_out_tbl oe_order_pub.line_val_tbl_type;
623 l_oe_line_adj_out_tbl oe_order_pub.line_adj_tbl_type;
624 l_oe_line_adj_val_out_tbl oe_order_pub.line_adj_val_tbl_type;
625 l_oe_Line_price_Att_out_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
626 l_oe_Line_Adj_Att_out_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
627 l_oe_Line_Adj_Assoc_out_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
628 l_oe_line_scredit_out_tbl oe_order_pub.line_scredit_tbl_type;
629 l_oe_line_scredit_val_out_tbl oe_order_pub.line_scredit_val_tbl_type;
630 l_oe_lot_serial_out_tbl oe_order_pub.Lot_Serial_Tbl_Type;
631 l_oe_lot_serial_val_out_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type;
632 l_action_request_tbl OE_Order_PUB.request_tbl_type;
633 l_action_request_tbl_out OE_Order_PUB.request_tbl_type;
634 l_oe_header_rec_out oe_order_pub.header_rec_type;
635 l_return_status Varchar2(30);
636 x_msg_count number;
637 x_msg_data Varchar2(2000);
638 x_msg_index number;
639 --
640 v_progress VARCHAR2(3) := '010';
641 v_FileName VARCHAR2(2000);
642 --
643 x_token FND_NEW_MESSAGES.TYPE%TYPE;
644 x_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
645 --
646 BEGIN
647 --
648 IF (l_debug <> -1) THEN
649 rlm_core_sv.dpush(C_SDEBUG, 'CreateRSOHeader');
650 rlm_core_sv.dlog(C_DEBUG, 'Blanket Number', x_Group_rec.setup_terms_rec.blanket_number);
651 rlm_core_sv.dlog(C_DEBUG, 'Customer id', x_Sched_rec.customer_id);
652 rlm_core_sv.dlog(C_DEBUG, 'Org ID', x_Sched_rec.org_id);
653 END IF;
654 --
655 l_oe_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
656 l_oe_header_rec.operation := OE_Globals.G_OPR_CREATE;
657 l_oe_header_rec.blanket_number := x_Group_rec.setup_terms_rec.blanket_number;
658 l_oe_header_rec.sold_to_org_id := x_Sched_rec.customer_id;
659 l_oe_header_rec.org_id := x_Sched_rec.org_id;
660 --
661 l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
662 l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
663 --
664 OE_Order_Grp.Process_order
665 ( p_api_version_number => 1.0
666 , p_init_msg_list => FND_API.G_TRUE
667 , p_return_values => FND_API.G_FALSE
668 , p_commit => FND_API.G_FALSE
669 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
670 , p_control_rec => OE_GLOBALS.G_MISS_CONTROL_REC
671 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
672 , x_return_status => l_return_status
673 , x_msg_count => x_msg_count
674 , x_msg_data => x_msg_data
675 , p_header_rec => l_oe_header_rec
676 , p_Action_Request_tbl => l_action_request_tbl
677 , x_header_rec => l_oe_header_rec_out
678 , x_header_val_rec => l_oe_header_val_rec
679 , x_Header_Adj_tbl => l_oe_header_adj_tbl
680 , x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
681 , x_Header_price_Att_tbl => l_oe_Header_price_Att_out_tbl
682 , x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_out_tbl
683 , x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_out_tbl
684 , x_Header_Scredit_tbl => l_oe_header_scredit_out_tbl
685 , x_Header_Scredit_val_tbl => l_oe_hdr_scdt_val_out_tbl
686 , x_line_tbl => l_oe_line_out_tbl
687 , x_line_val_tbl => l_oe_line_val_out_tbl
688 , x_Line_Adj_tbl => l_oe_line_adj_out_tbl
689 , x_Line_Adj_val_tbl => l_oe_line_adj_val_out_tbl
690 , x_Line_price_Att_tbl => l_oe_Line_price_Att_out_tbl
691 , x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_out_tbl
692 , x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_out_tbl
693 , x_Line_Scredit_tbl => l_oe_line_scredit_out_tbl
694 , x_Line_Scredit_val_tbl => l_oe_line_scredit_val_out_tbl
695 , x_Lot_Serial_tbl => l_oe_lot_serial_out_tbl
696 , x_Lot_Serial_val_tbl => l_oe_lot_serial_val_out_tbl
697 , x_action_request_tbl => l_action_request_tbl_out
698 );
699 --
700 IF (l_debug <> -1) THEN
701 rlm_core_sv.dlog(C_DEBUG,'Process Order return Status',l_return_Status);
702 rlm_core_sv.dlog(C_DEBUG,'Process Order Error Count',x_msg_count);
703 rlm_core_sv.dlog(C_DEBUG,'Process Order Error Message',x_msg_data);
704 END IF;
705 --
706 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
707 l_return_status = FND_API.G_RET_STS_ERROR) THEN
708 --
709 RAISE e_RSOCreationError;
710 --
711 ELSE
712 --
713 x_token := 'INFO';
714 x_msg_name := 'RLM_RSO_CREATION_INFO';
715 --
716 RLM_TPA_SV.InsertOMMessages(x_Sched_rec, x_Group_rec,
717 x_msg_count, rlm_message_sv.k_INFO_LEVEL,
718 x_token, x_msg_name);
719 --
720 END IF;
721 --
722 IF (l_debug <> -1) THEN
723 rlm_core_sv.dlog(C_DEBUG, 'Sales order header id ',
724 l_oe_header_rec_out.header_id);
725 rlm_core_sv.dlog(C_DEBUG, 'Sales order number',
726 l_oe_header_rec_out.order_number);
727 END IF;
728 --
729 x_rso_hdr_id := l_oe_header_rec_out.header_id;
730 --
731 IF (l_debug <> -1) THEN
732 rlm_core_sv.dpop(C_SDEBUG);
733 END IF;
734 --
735 EXCEPTION
736 --
737 WHEN e_RSOCreationError THEN
738 --
739 x_token := 'ERROR';
740 x_msg_name := 'RLM_RSO_CREATION_ERROR';
741 --
742 RLM_TPA_SV.InsertOMMessages(x_Sched_rec, x_Group_rec,
743 x_msg_count, rlm_message_sv.k_ERROR_LEVEL,
744 x_token, x_msg_name);
745 --
746 IF (l_debug <> -1) THEN
747 rlm_core_sv.dlog(C_DEBUG, 'RSO Creation Error: ' || x_msg_data);
748 rlm_core_sv.dpop(C_SDEBUG, 'e_RSOCreationError');
749 END IF;
750 --
751 RAISE e_RSOCreationError;
752 --
753 WHEN OTHERS THEN
754 rlm_message_sv.sql_error('rlm_blanket_sv.CreateRSOHeader', v_progress);
755 --
756 IF (l_debug <> -1) THEN
757 rlm_core_sv.dpop(C_SDEBUG, 'CreateRSOHeader');
758 END IF;
759 --
760 RAISE;
761 --
762 END CreateRSOHeader;
763
764 /*============================================================================
765
766 PROCEDURE UpdateLinesWithRSO
767
768 ==============================================================================*/
769 --Bug Fix 4254471 Added parameter to procedure
770 PROCEDURE UpdateLinesWithRSO(x_header_id IN NUMBER) IS
771 --
772 v_progress VARCHAR2(3) := '020';
773 i NUMBER;
774 --
775 BEGIN
776 --
777 IF (l_debug <> -1) THEN
778 rlm_core_sv.dpush(C_SDEBUG, 'UpdateLinesWithRSO');
779 rlm_core_sv.dlog(C_DEBUG, 'x_header_id', x_header_id);
780 rlm_core_sv.dlog(C_DEBUG, '# of lines', g_LineIdTab.COUNT);
781 rlm_core_sv.dlog(C_DEBUG, '# of RSOs', g_RSOIdTab.COUNT);
782 END IF;
783 --
784 FORALL i IN 1..g_LineIdTab.COUNT
785 --
786 UPDATE rlm_interface_lines_all
787 SET order_header_id = g_RSOIdTab(i)
788 WHERE line_id = g_LineIdTab(i);
789 --
790 IF (l_debug <> -1) THEN
791 rlm_core_sv.dlog(C_DEBUG, '# of interface lines updated', SQL%ROWCOUNT);
792 END IF;
793 --
794 FORALL i IN 1..g_LineIdTab.COUNT
795 --
796 UPDATE rlm_schedule_lines
797 SET order_header_id = g_RSOIdTab(i)
798 WHERE interface_line_id = g_LineIdTab(i)
799 AND header_id = x_header_id; /* 4254471 */
800 --
801 IF (l_debug <> -1) THEN
802 rlm_core_sv.dlog(C_DEBUG, '# of schedule lines updated', SQL%ROWCOUNT);
803 rlm_core_sv.dpop(C_SDEBUG);
804 END IF;
805 --
806 EXCEPTION
807 --
808 WHEN OTHERS THEN
809 --
810 rlm_message_sv.sql_error('rlm_blanket_sv.UpdateLinesWithRSO', v_Progress);
811 --
812 IF (l_debug <> -1) THEN
813 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
814 END IF;
815 --
816 raise;
817
818 END UpdateLinesWithRSO;
819
820
821 /*============================================================================
822
823 PROCEDURE InsertOMMessages
824
825 ==============================================================================*/
826 PROCEDURE InsertOMMessages(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
827 x_Group_rec IN RLM_DP_SV.t_Group_rec,
828 x_msg_count IN NUMBER,
829 x_msg_level IN VARCHAR2,
830 x_token IN VARCHAR2,
831 x_msg_name IN VARCHAR2) IS
832 --
833 x_msg VARCHAR2(4000);
834 v_interface_line_id NUMBER;
835 v_schedule_header_id NUMBER;
836 v_order_header_id NUMBER;
837 v_request_date VARCHAR2(150);
838 l_entity_code VARCHAR2(30);
839 l_entity_ref VARCHAR2(50);
840 l_entity_id NUMBER;
841 l_header_id NUMBER;
842 l_line_id NUMBER;
843 l_order_source_id NUMBER;
844 l_orig_sys_document_ref VARCHAR2(50);
845 l_orig_sys_line_ref VARCHAR2(50);
846 l_orig_sys_shipment_ref VARCHAR2(50);
847 l_change_sequence VARCHAR2(50);
848 l_source_document_type_id NUMBER;
849 l_source_document_id NUMBER;
850 l_source_document_line_id NUMBER;
851 l_attribute_code VARCHAR2(30);
852 l_constraint_id NUMBER;
853 l_process_activity NUMBER;
854 l_transaction_id NUMBER;
855 l_notification_flag VARCHAR2(1) := 'N' ;
856 l_type VARCHAR2(30) ;
857 l_msg_level VARCHAR2(10); --4129069
858 --
859 v_Progress VARCHAR2(3) := '030';
860 v_InterfaceLineId NUMBER;
861 --
862 BEGIN
863 --
864 IF (l_debug <> -1) THEN
865 rlm_core_sv.dpush(C_SDEBUG, 'InsertOMMessages');
866 END IF;
867 --
868 IF x_msg_count >0 THEN
869 --
870 FOR I in 1..x_msg_count LOOP
871 --
872 x_msg := oe_msg_pub.get(p_msg_index => I,
873 p_encoded => 'F');
874 --
875 IF (l_debug <> -1) THEN
876 rlm_core_sv.dlog(C_DEBUG,'Message Found', substr(x_msg,1,200));
877 END IF;
878 --
879 oe_msg_pub.Get_msg_context(
880 p_msg_index => I
881 ,x_entity_code => l_entity_code
882 ,x_entity_ref => l_entity_ref
883 ,x_entity_id => l_entity_id
884 ,x_header_id => l_header_id
885 ,x_line_id => l_line_id
886 ,x_order_source_id => l_order_source_id
887 ,x_orig_sys_document_ref => l_orig_sys_document_ref
888 ,x_orig_sys_line_ref => l_orig_sys_line_ref
889 ,x_orig_sys_shipment_ref => l_orig_sys_shipment_ref
890 ,x_change_sequence => l_change_sequence
891 ,x_source_document_type_id => l_source_document_type_id
892 ,x_source_document_id => l_source_document_id
893 ,x_source_document_line_id => l_source_document_line_id
894 ,x_attribute_code => l_attribute_code
895 ,x_constraint_id => l_constraint_id
896 ,x_process_activity => l_process_activity
897 ,x_notification_flag => l_notification_flag
898 ,x_type => l_type
899 );
900 --
901 IF (l_debug <> -1) THEN
902 rlm_core_sv.dlog(C_DEBUG,'l_header_id', l_header_id);
903 rlm_core_sv.dlog(C_DEBUG,'x_msg_level', x_msg_level);
904 rlm_core_sv.dlog(C_DEBUG,'x_msg_name', x_msg_name);
905 rlm_core_sv.dlog(C_DEBUG,'Industry Att15', x_Group_rec.industry_attribute15);
906 rlm_core_sv.dlog(C_DEBUG,'x_ShipToAddressId',x_Group_rec.ship_to_address_id);
907 rlm_core_sv.dlog(C_DEBUG,'x_CustomerItemId',x_Group_rec.customer_item_id);
908 rlm_core_sv.dlog(C_DEBUG,'x_InventoryItemId',x_Group_rec.inventory_item_id);
909 END IF;
910 --
911 SELECT line_id
912 INTO v_InterfaceLineId
913 FROM rlm_interface_lines
914 WHERE header_id = x_Sched_rec.header_id
915 AND ship_from_org_id = x_Group_rec.ship_from_org_id
916 AND ship_to_address_id = x_Group_rec.ship_to_address_id
917 AND customer_item_id = x_Group_rec.customer_item_id
918 AND line_id = g_line_id; --Bugfix 6884912
919 -- AND rownum = 1; --Bugfix 6884912
920 --
921
922 -- Bug 4129069 : Set the message level depending on the seeded error
923 -- type only if Process Order API returned Error Status.
924
925 IF x_msg_name = 'RLM_RSO_CREATION_ERROR' THEN
926 IF (l_type = 'ERROR') THEN
927 l_msg_level := x_msg_level;
928 ELSE
929 l_msg_level := rlm_message_sv.k_INFO_LEVEL;
930 END IF;
931 ELSE
932 l_msg_level := x_msg_level;
933 END IF;
934 --
935 IF (l_debug <> -1) THEN
936 rlm_core_sv.dlog(C_DEBUG, 'l_msg_level', l_msg_level);
937 END IF;
938 --
939 --Bugfix 6884912 Start
940 IF g_req_flag = 1 THEN
941 rlm_message_sv.app_error(
942 x_ExceptionLevel => l_msg_level,
943 x_MessageName => x_msg_name,
944 x_InterfaceHeaderId => x_Sched_rec.header_id,
945 x_InterfaceLineId => v_InterfaceLineId,
946 x_ScheduleHeaderId => NULL,
947 x_ScheduleLineId => NULL,
948 x_OrderHeaderId => l_header_id,
949 x_OrderLineId => NULL,
950 x_GroupInfo => TRUE,
951 x_ShipFromOrgId => x_Group_rec.industry_attribute15,
952 x_ShipToAddressId => x_Group_rec.ship_to_address_id,
953 x_CustomerItemId => x_Group_rec.customer_item_id,
954 x_InventoryItemId => x_Group_rec.inventory_item_id,
955 x_Token1 => x_token,
956 x_value1 => substr(x_msg,1,200),
957 x_Token2 => 'BLANKET_NUMBER',
958 x_value2 => x_Group_rec.setup_terms_rec.blanket_number,
959 x_Token3 => 'ORDER_NUMBER',
960 x_Value3 => RLM_VALIDATEDEMAND_SV.GetOrderNumber(l_header_id));
961
962 ELSE
963 -- bug fix 4198330
964 rlm_message_sv.app_error(
965 x_ExceptionLevel => l_msg_level,
966 x_MessageName => x_msg_name,
967 x_InterfaceHeaderId => x_Sched_rec.header_id,
968 x_InterfaceLineId => v_InterfaceLineId,
969 x_ScheduleHeaderId => NULL,
970 x_ScheduleLineId => NULL,
971 x_OrderHeaderId => l_header_id,
972 x_OrderLineId => NULL,
973 x_ShipFromOrgId => x_Group_rec.industry_attribute15,
974 x_ShipToAddressId => x_Group_rec.ship_to_address_id,
975 x_CustomerItemId => x_Group_rec.customer_item_id,
976 x_InventoryItemId => x_Group_rec.inventory_item_id,
977 x_Token1 => x_token,
978 x_value1 => substr(x_msg,1,200),
979 x_Token2 => 'BLANKET_NUMBER',
980 x_value2 => x_Group_rec.setup_terms_rec.blanket_number,
981 x_Token3 => 'ORDER_NUMBER',
982 x_Value3 => RLM_VALIDATEDEMAND_SV.GetOrderNumber(l_header_id));
983 END IF;
984 --
985 --Bugfix 6884912 End
986 END LOOP;
987 --
988 END IF;
989 --
990 IF (l_debug <> -1) THEN
991 rlm_core_sv.dpop(C_SDEBUG);
992 END IF;
993 --
994 EXCEPTION
995 --
996 WHEN OTHERS THEN
997 --
998 rlm_message_sv.sql_error('rlm_blanket_sv.InsertOMMessages', v_Progress);
999 --
1000 IF (l_debug <> -1) THEN
1001 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1002 END IF;
1003 --
1004 raise;
1005 --
1006 END InsertOMMessages;
1007
1008 /*===========================================================================
1009
1010 FUNCTION NAME: GetTPContext
1011
1012 ===========================================================================*/
1013 PROCEDURE GetTPContext( x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1014 x_Group_rec IN rlm_dp_sv.t_Group_rec,
1015 x_customer_number OUT NOCOPY VARCHAR2,
1016 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1017 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
1018 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1019 x_tp_group_code OUT NOCOPY VARCHAR2)
1020 IS
1021 --
1022 v_Progress VARCHAR2(3) := '010';
1023 --
1024 BEGIN
1025 --
1026 IF (l_debug <> -1) THEN
1027 rlm_core_sv.dpush(C_SDEBUG,'GetTPContext');
1028 rlm_core_sv.dlog(C_DEBUG,'customer_id', x_Sched_rec.customer_id);
1029 rlm_core_sv.dlog(C_DEBUG,'x_sched_rec.ece_tp_translator_code',
1030 x_sched_rec.ece_tp_translator_code);
1031 rlm_core_sv.dlog(C_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1032 x_sched_rec.ece_tp_location_code_ext);
1033 rlm_core_sv.dlog(C_DEBUG,'x_group_rec.ship_to_address_id',
1034 x_group_rec.ship_to_address_id);
1035 END IF;
1036 --
1037 IF x_sched_rec.ECE_TP_LOCATION_CODE_EXT is NOT NULL THEN
1038
1039 -- Following query is changed as per TCA obsolescence project.
1040 SELECT ETG.TP_GROUP_CODE
1041 INTO x_tp_group_code
1042 FROM ECE_TP_GROUP ETG,
1043 ECE_TP_HEADERS ETH,
1044 HZ_CUST_ACCT_SITES ACCT_SITE
1045 WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
1046 and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
1047 and ACCT_SITE.CUST_ACCOUNT_ID = x_sched_rec.CUSTOMER_ID
1048 and ACCT_SITE.ECE_TP_LOCATION_CODE = x_Sched_rec.ECE_TP_LOCATION_CODE_EXT;
1049
1050 ELSE
1051 x_tp_group_code := x_Sched_rec.ECE_TP_TRANSLATOR_CODE;
1052 END IF;
1053 --
1054 BEGIN
1055 --
1056 -- Following query is changed as per TCA obsolescence project.
1057 SELECT ece_tp_location_code
1058 INTO x_ship_to_ece_locn_code
1059 FROM HZ_CUST_ACCT_SITES
1060 WHERE CUST_ACCT_SITE_ID = x_group_rec.ship_to_address_id;
1061 --
1062 EXCEPTION
1063 WHEN NO_DATA_FOUND THEN
1064 x_ship_to_ece_locn_code := NULL;
1065 END;
1066
1067 --
1068 -- BUG 2204888 : Since we do not group by bill_to anymore, we would not
1069 -- have the bill_to in x_group_rec. Code has been removed as a part of
1070 -- TCA OBSOLESCENCE PROJECT.
1071 --
1072 --
1073 IF x_sched_rec.customer_id is NOT NULL THEN
1074 --
1075 -- Following query is changed as per TCA obsolescence project.
1076 SELECT account_number
1077 INTO x_customer_number
1078 FROM HZ_CUST_ACCOUNTS CUST_ACCT
1079 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_sched_rec.customer_id;
1080 --
1081 END IF;
1082 --
1083 IF (l_debug <> -1) THEN
1084 rlm_core_sv.dlog(C_DEBUG, 'customer_number', x_customer_number);
1085 rlm_core_sv.dlog(C_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
1086 rlm_core_sv.dlog(C_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
1087 rlm_core_sv.dlog(C_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
1088 rlm_core_sv.dlog(C_DEBUG, 'x_tp_group_code',x_tp_group_code);
1089 rlm_core_sv.dpop(C_SDEBUG);
1090 END IF;
1091 --
1092 EXCEPTION
1093 --
1094 WHEN NO_DATA_FOUND THEN
1095 --
1096 x_customer_number := NULL;
1097 --
1098 IF (l_debug <> -1) THEN
1099 rlm_core_sv.dlog(C_DEBUG, 'No data found for' , x_sched_rec.customer_id);
1100 rlm_core_sv.dpop(C_SDEBUG);
1101 END IF;
1102 --
1103 WHEN OTHERS THEN
1104 --
1105 rlm_message_sv.sql_error('rlm_blanket_sv.GetTPContext',v_Progress);
1106 --
1107 IF (l_debug <> -1) THEN
1108 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1109 END IF;
1110 --
1111 raise;
1112
1113 END GetTPContext;
1114
1115 --4302492
1116 /*============================================================================
1117
1118 PROCEDURE CalFenceDays
1119
1120 ==============================================================================*/
1121
1122 PROCEDURE CalFenceDays(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1123 x_Group_rec IN rlm_dp_sv.t_Group_rec,
1124 x_fence_days OUT NOCOPY NUMBER)
1125 IS
1126 --
1127 v_FrozenFenceDayFrom NUMBER;
1128 v_FrozenFenceDayTo NUMBER;
1129 v_FrozenFenceDays NUMBER := NULL;
1130 --
1131 BEGIN
1132 --
1133 IF (l_debug <> -1) THEN
1134 rlm_core_sv.dpush(C_DEBUG,'CalFenceDays');
1135 rlm_core_sv.dlog(C_DEBUG,'x_roll_forward_flag', x_Group_rec.roll_forward_frozen_flag);
1136 END IF;
1137 --
1138 IF x_Sched_rec.Schedule_type = k_PLANNING THEN
1139 --
1140 IF (l_debug <> -1) THEN
1141 rlm_core_sv.dlog(C_DEBUG,'PLANNING');
1142 END IF;
1143 --
1144 v_FrozenFenceDayFrom := x_Group_rec.setup_terms_rec.pln_frozen_day_from;
1145 v_FrozenFenceDayTo := x_Group_rec.setup_terms_rec.pln_frozen_day_to;
1146 --
1147 ELSIF x_Sched_rec.Schedule_type = k_SHIPPING THEN
1148 --
1149 IF (l_debug <> -1) THEN
1150 rlm_core_sv.dlog(C_DEBUG,'SHIPPING');
1151 END IF;
1152 --
1153 v_FrozenFenceDayFrom := x_Group_rec.setup_terms_rec.shp_frozen_day_from;
1154 v_FrozenFenceDayTo := x_Group_rec.setup_terms_rec.shp_frozen_day_to;
1155 --
1156 ELSIF x_Sched_rec.Schedule_type = k_SEQUENCED THEN
1157 --
1158 IF (l_debug <> -1) THEN
1159 rlm_core_sv.dlog(C_DEBUG,'SEQUENCED');
1160 END IF;
1161 --
1162 v_FrozenFenceDayFrom := x_Group_rec.setup_terms_rec.seq_frozen_day_from;
1163 v_FrozenFenceDayTo := x_Group_rec.setup_terms_rec.seq_frozen_day_to;
1164 --
1165 END IF;
1166 --
1167 IF v_FrozenFenceDayFrom IS NOT NULL THEN
1168 v_FrozenFenceDays := v_FrozenFenceDayTo - v_FrozenFenceDayFrom + 1;
1169 ELSE
1170 v_FrozenFenceDays := NULL;
1171 END IF;
1172 --
1173 x_fence_days :=v_FrozenFenceDays;
1174 --
1175 IF (l_debug <> -1) THEN
1176 rlm_core_sv.dpop(C_SDEBUG);
1177 END IF;
1178 --
1179 END CalFenceDays;
1180
1181
1182 END RLM_BLANKET_SV;