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