[Home] [Help]
PACKAGE BODY: APPS.RLM_COMP_SCH_TO_DEMAND_SV
Source
1 PACKAGE BODY RLM_COMP_SCH_TO_DEMAND_SV as
2 /* $Header: RLMCOMDB.pls 120.1 2005/07/17 18:25:15 rlanka noship $*/
3 /*=============================================================================
4
5 PROCEDURE NAME: proc_comp_sch_to_demand
6
7 ==============================================================================*/
8
9 PROCEDURE proc_comp_sch_to_demand
10 (p_schedule_type IN VARCHAR2,
11 p_header_id IN NUMBER :=NULL,
12 p_Customer_name_from IN VARCHAR2 :=NULL,
13 p_customer_name_to IN varchar2 :=NULL,
14 p_ship_from_org_id IN NUMBER :=NULL,
15 p_ship_to IN NUMBER :=NULL,
16 p_tp_code_from IN VARCHAR2 :=NULL,
17 p_tp_code_to IN VARCHAR2 :=NULL,
18 p_tp_location_from IN VARCHAR2 :=NULL,
19 p_tp_location_to IN VARCHAR2 :=NULL,
20 p_process_date_from IN VARCHAR2 :=NULL,
21 p_process_date_to IN VARCHAR2 :=NULL,
22 p_issue_date_from IN VARCHAR2 :=NULL,
23 p_issue_date_to IN VARCHAR2 :=NULL,
24 p_request_date_from IN VARCHAR2 :=NULL,
25 p_request_date_to IN VARCHAR2 :=NULL,
26 p_customer_item_from IN VARCHAR2 :=NULL,
27 p_customer_item_to IN VARCHAR2 :=NULL,
28 p_internal_item_from IN VARCHAR2 :=NULL,
29 p_internal_item_to IN VARCHAR2 :=NULL,
30 p_demand_type IN VARCHAR2 :=NULL
31 )
32 IS
33
34 TYPE t_match_attributes IS RECORD (
35 Attribute_Name VARCHAR2(80),
36 within_key VARCHAR2(20),
37 across_key VARCHAR2(20)
38 );
39
40 TYPE tab_match_attribute IS TABLE OF t_match_attributes INDEX BY BINARY_INTEGER;
41
42 l_match_attribute tab_match_attribute ;
43 l_Count NUMBER := 1;
44 l_Buckets NUMBER := 1;
45 l_WholeNumber BOOLEAN :=TRUE;
46 l_LastDayQuarter DATE;
47 l_WeeksInQuarter NUMBER := 12;
48 l_customer_id NUMBER := -9999;
49 l_ship_from NUMBER := -9999;
50 l_ship_to NUMBER := -9999;
51 l_customer_item_id number := -9999;
52 l_terms_rec RLM_SETUP_TERMS_SV.setup_terms_rec_typ;
53 l_return_message varchar2(32767);
54 l_return_status Boolean;
55 l_NULL varchar2(32767);
56 l_ship_del_pattern VARCHAR2(240);
57 l_week_name varchar2(150) DEFAULT NULL;
58 l_frozen_day_from number;
59 l_frozen_day_to number;
60 l_firm_day_from number;
61 l_firm_day_to number;
62 l_forecast_day_from number;
63 l_forecast_day_to number;
64 v_item_detail_quantity number ;
65 l_frozen_flag varchar2(1);
66 l_inputrec rlm_ship_delivery_pattern_sv.t_InputRec;
67
68 CURSOR csr_sdp(p_ship_del_pattern IN VARCHAR2) IS
69 SELECT description
70 FROM rlm_ship_delivery_codes
71 WHERE ship_delivery_rule_name = p_ship_del_pattern;
72
73
74 CURSOR cur_comp_sch_to_demand
75 IS
76 SELECT schheaders.header_id header_id,
77 schheaders.customer_id customer_id,
78 hzparties.party_name customer_name,
79 schheaders.schedule_type schedule_type ,
80 schheaders.sched_generation_date sched_generation_date,
81 schheaders.schedule_source schedule_source,
82 schheaders.ece_tp_translator_code ece_tp_translator_code,
83 schheaders.ece_tp_location_code_ext ece_tp_location_code_ext,
84 schheaders.schedule_reference_num schedule_reference_num,
85 schheaders.schedule_purpose schedule_purpose,
86 schheaders.sched_horizon_start_date sched_horizon_start_date,
87 schheaders.sched_horizon_end_date sched_horizon_end_date,
88 schheaders.last_update_date last_update_date,
89 schheaders.creation_date creation_date,
90 schlines.ship_from_org_id ship_from_org_id,
91 schlines.ship_to_org_id ship_to_org_id,
92 schlines.ship_to_address_id ship_to_address_id,
93 schlines.inventory_item_id inventory_item_id,
94 schlines.customer_item_id customer_item_id,
95 invitems.segment1 inventory_item_number,
96 cusitems.customer_item_number customer_item_number,
97 invitems.description inventory_item_desc,
98 cusitems.customer_item_desc customer_item_desc,
99 schlines.start_date_time start_date_time,
100 schlines.end_date_time end_date_time,
101 schlines.line_id line_id ,
102 schlines.order_header_id order_header_id,
103 schlines.item_detail_quantity item_detail_quantity,
104 schlines.uom_code uom_code,
105 schlines.item_detail_subtype item_detail_subtype,
106 schlines.item_detail_type item_detail_type,
107 schlines.qty_type_code qty_type_code,
108 schlines.date_type_code date_type_code,
109 schlines.customer_job customer_job,
110 schlines.cust_production_line cust_production_line,
111 schlines.cust_production_seq_num cust_production_seq_num,
112 schlines.cust_model_serial_number cust_model_serial_number,
113 schlines.cust_po_number cust_po_number,
114 schlines.customer_item_revision customer_item_revision,
115 schlines.Customer_docK_Code Customer_docK_Code,
116 schlines.industry_attribute1 record_year,
117 schlines.industry_attribute2 customer_request_date,
118 schlines.industry_attribute4 pull_signal_ref_num,
119 schlines.industry_attribute5 pull_signal_start_serial_num ,
120 schlines.industry_attribute6 pull_signal_end_serial_num
121
122 --
123 FROM rlm_schedule_headers schheaders,
124 rlm_schedule_lines_all schlines,
125 mtl_customer_items cusitems,
126 mtl_system_items_b invitems,
127 hz_parties hzparties ,
128 hz_cust_accounts hzcustacc
129 --
130 WHERE schheaders.ORG_ID = schlines.ORG_ID
131 AND schheaders.schedule_type = P_SCHEDULE_TYPE
132 AND schlines.item_detail_subtype IN ('1','2','4','5')
133 AND ((p_demand_type = 'O'AND schlines.item_detail_type = '2') OR
134 (p_demand_type = 'F' AND schlines.item_detail_type = '1') OR
135 (p_demand_type = 'B' AND schlines.item_detail_type IN ('0', '1', '2')))
136 AND hzparties.party_id = hzcustacc.party_id
137 AND hzcustacc.cust_account_id = schheaders.customer_id
138 AND cusitems.customer_id = schheaders.customer_id
139 AND schlines.customer_item_id = cusitems.customer_item_id
140 AND schlines.inventory_item_id = invitems.inventory_item_id
141 AND schlines.ship_from_org_id = invitems.organization_id
142 AND schlines.qty_type_code <> 'CUMULATIVE'
143 AND schlines.process_status = 5
144 AND schheaders.Process_status IN (5,7)
145 AND schheaders.edi_test_indicator <> 'T'
146 AND schheaders.header_id = schlines.header_id
147 AND ((p_customer_name_from IS NULL) oR (hzparties.party_name >=p_customer_name_from and
148 hzparties.party_name <=p_customer_name_to))
149 AND ((p_header_id IS NULL) OR (schheaders.header_id = p_header_id))
150 AND ((p_ship_from_org_id IS NULL) OR (schlines.ship_from_org_id = p_ship_from_org_id))
151 AND ((p_ship_to IS NULL) OR (schlines.ship_to_address_id = p_ship_to))
152 AND ((p_tp_code_from IS NULL) OR (schheaders.ece_tp_translator_code >= p_tp_code_from
153 AND schheaders.ece_tp_translator_code <= p_tp_code_to))
154 AND ((p_tp_location_from IS NULL)OR (schheaders.ece_tp_location_code_ext >=p_tp_location_from
155 AND schheaders.ece_tp_location_code_ext <= p_tp_location_to))
156 AND ((p_process_date_from IS NULL) OR
157 (schheaders.creation_date >= to_date(p_process_date_from,'yyyy/MM/DD HH24:MI:SS')
158 AND schheaders.creation_date <= to_date(p_process_date_to,'yyyy/MM/DD HH24:MI:SS')))
159 AND ((p_issue_date_from IS NULL) OR
160 (schheaders.sched_generation_date >= to_date(p_issue_date_from,'yyyy/MM/DD HH24:MI:SS')
161 AND schheaders.sched_generation_date <=to_date(p_issue_date_to,'yyyy/MM/DD HH24:MI:SS')))
162 AND ((p_request_date_from IS NULL) OR
163 (schlines.start_date_time >= to_date(p_request_date_from,'yyyy/MM/DD HH24:MI:SS')
164 AND schlines.start_date_time <= to_date(p_request_date_to,'yyyy/MM/DD HH24:MI:SS')))
165 AND ((p_customer_item_from IS NULL) OR (cusitems.customer_item_number >= p_customer_item_from
166 AND cusitems.customer_item_number <= p_customer_item_to))
167 AND ((p_internal_item_from IS NULL) OR (invitems.segment1 >= p_internal_item_from
168 AND invitems.segment1 <= p_internal_item_to))
169 AND schlines.customer_item_id NOT IN (
170 SELECT corr_schlines.customer_item_id
171 --
172 FROM rlm_schedule_headers corr_schheaders,
173 rlm_schedule_lines_all corr_schlines,
174 mtl_customer_items corr_cusitems,
175 mtl_system_items_b corr_invitems,
176 hz_parties corr_hzparties,
177 hz_cust_accounts corr_hzcustacc
178 --
179 WHERE corr_schheaders.ORG_ID = corr_schlines.ORG_ID
180 AND corr_schheaders.customer_id = schheaders.customer_id
181 AND corr_schheaders.schedule_type = schheaders.schedule_type
182 AND corr_schheaders.last_update_date > Schheaders.last_update_date
183 AND corr_hzparties.party_id = corr_hzcustacc.party_id
184 AND corr_hzcustacc.cust_account_id = corr_schheaders.customer_id
185 AND corr_cusitems.customer_id = corr_schheaders.customer_id
186 AND corr_schlines.customer_item_id = corr_cusitems.customer_item_id
187 AND corr_schlines.inventory_item_id = corr_invitems.inventory_item_id
188 AND corr_schlines.ship_from_org_id = corr_invitems.organization_id
189 AND ((p_demand_type = 'O' AND corr_schlines.item_detail_type = '2') OR
190 (p_demand_type = 'F' AND corr_schlines.item_detail_type = '1') OR
191 (p_demand_type = 'B' AND corr_schlines.item_detail_type IN ('0', '1', '2')))
192 AND corr_schlines.qty_type_code <> 'CUMULATIVE'
193 AND corr_schlines.item_detail_subtype IN ('1','2','4','5')
194 AND corr_schheaders.Process_status IN (5, 7)
195 AND corr_schlines.process_status = 5
196 AND corr_schheaders.edi_test_indicator <> 'T'
197 AND corr_schlines.header_id = corr_schheaders.header_id
198 AND ((p_header_id IS NULL) OR (corr_schheaders.header_id = p_header_id))
199 AND ((p_customer_name_from IS NULL) oR (corr_hzparties.party_name >=p_customer_name_from and
200 corr_hzparties.party_name <=p_customer_name_to))
201 AND ((p_ship_from_org_id IS NULL) OR (corr_schlines.ship_from_org_id = p_ship_from_org_id))
202 AND ((p_ship_to IS NULL) OR (corr_schlines.ship_to_address_id = p_ship_to))
203 AND ((p_tp_code_from IS NULL) OR (corr_schheaders.ece_tp_translator_code >=
204 p_tp_code_from AND corr_schheaders.ece_tp_translator_code <= p_tp_code_to))
205 AND ((p_tp_location_from IS NULL) OR (corr_schheaders.ece_tp_location_code_ext >=
206 p_tp_location_from AND corr_schheaders.ece_tp_location_code_ext <= p_tp_location_to))
207 AND ((p_process_date_from IS NULL) OR
208 (corr_schheaders.creation_date >=to_date(p_process_date_from,'yyyy/MM/DD HH24:MI:SS')
209 AND corr_schheaders.creation_date <=to_date(p_process_date_to,'yyyy/MM/DD HH24:MI:SS')))
210 AND ((p_issue_date_from IS NULL) OR
211 (corr_schheaders.sched_generation_date >= to_date(p_issue_date_from,'yyyy/MM/DD HH24:MI:SS')
212 AND corr_schheaders.sched_generation_date <= to_date(p_issue_date_to,'yyyy/MM/DD HH24:MI:SS')))
213 AND ((p_request_date_from IS NULL) OR
214 (corr_schlines.start_date_time >= to_date(p_request_date_from,'yyyy/MM/DD HH24:MI:SS')
215 AND corr_schlines.start_date_time <= to_date(p_request_date_to,'yyyy/MM/DD HH24:MI:SS')))
216 AND ((p_customer_item_from IS NULL) OR (corr_cusitems.customer_item_number >= p_customer_item_from
217 AND corr_cusitems.customer_item_number <= p_customer_item_to))
218 AND ((p_internal_item_from IS NULL) OR (corr_invitems.segment1 >= p_internal_item_from
219 AND corr_invitems.segment1 <= p_internal_item_to))
220
221 ) ORDER BY schheaders.customer_id, ship_from_org_id, ship_to_org_id, schlines.customer_item_id;
222
223 /*=============================================================================
224
225 PROCEDURE NAME: populate_within_across
226
227 ==============================================================================*/
228
229 PROCEDURE populate_within_across
230 IS
231 l_Iterations NUMBER ;
232 l_match_Within_key VARCHAR2(20) :=NULL;
233 l_match_Across_key VARCHAR2(20) :=NULL;
234 l_Within_key VARCHAR2(20) :=NULL;
235 l_Across_key VARCHAR2(20) :=NULL;
236 l_attribute_name varchar2(80) :=NULL;
237 l_attribute_found VARCHAR2(1) :='N';
238
239 BEGIN
240
241 l_match_Within_key := replace(l_terms_rec.match_within_key, '0');
242 l_match_across_key := replace(l_terms_rec.match_across_key, '0');
243
244 FOR i in 1..nvl(length(l_match_Within_key),0)
245 LOOP
246
247 l_within_key := substr(l_match_Within_key,i,1);
248
249 BEGIN
250 SELECT meaning
251 INTO l_attribute_name
252 FROM FND_LOOKUP_VALUES_VL
253 WHERE LOOKUP_TYPE = 'RLM_OPTIONAL_MATCH_ATTRIBUTES'
254 AND ENABLED_FLAG = 'Y'
255 AND SUBSTR(LOOKUP_CODE, INSTR(LOOKUP_CODE, ',') + 1) = l_Within_key ;
256 EXCEPTION
257 WHEN OTHERS THEN
258 l_attribute_name := null;
259 END;
260
261 l_match_attribute(i).Attribute_Name := l_attribute_name;
262 l_match_attribute(i).within_key := l_within_key;
263
264 END LOOP;
265
266 FOR i in 1..nvl(length(l_match_across_key),0)
267 LOOP
268
269 l_Across_key := substr(l_match_across_key, i, 1);
270
271 IF l_match_attribute.count > 0 THEN
272 FOR j in 1..l_match_attribute.count
273 LOOP
274
275 IF l_Across_key = l_match_attribute(j).within_key THEN
276 l_match_attribute(j).across_key := l_Across_key;
277 l_attribute_found := 'Y';
278 EXIT;
279 END IF;
280 END LOOP;
281 END IF;
282
283 IF NOT (l_attribute_found = 'Y') THEN
284
285 BEGIN
286 SELECT meaning
287 INTO l_attribute_name
288 FROM FND_LOOKUP_VALUES_VL
289 WHERE LOOKUP_TYPE = 'RLM_OPTIONAL_MATCH_ATTRIBUTES'
290 AND ENABLED_FLAG = 'Y'
291 AND SUBSTR(LOOKUP_CODE, INSTR(LOOKUP_CODE, ',') + 1) = l_across_key ;
292 EXCEPTION
293 WHEN OTHERS THEN
294 l_attribute_name := null;
295 END;
296
297 l_match_attribute(l_match_attribute.count+1).Attribute_name := l_attribute_name;
298 l_match_attribute(l_match_attribute.count).across_key := l_Across_key;
299
300 END IF;
301
302 l_attribute_found := 'N';
303
304 END LOOP;
305
306 END populate_within_across;
307 ------------------------------------------------------------------------------------
308
309 /*=============================================================================
310
311 PROCEDURE NAME: insert_rlm_attributes
312
313 ==============================================================================*/
314
315 PROCEDURE insert_rlm_attributes ( P_CUSTOMER_ID NUMBER,
316 P_SHIP_FROM_ORG_ID NUMBER,
317 P_SHIP_TO_ORG_ID NUMBER,
318 P_SHIP_TO_ADDRESS_ID NUMBER,
319 P_CUSTOMER_ITEM_ID NUMBER,
320 P_CUSTOMER_ITEM_NUMBER VARCHAR2,
321 P_CUSTOMER_ITEM_DESC VARCHAR2,
322 P_FROZEN_DAY_FROM NUMBER,
323 P_FROZEN_DAY_TO NUMBER,
324 P_FIRM_DAY_FROM NUMBER,
325 P_FIRM_DAY_TO NUMBER,
326 P_FORECAST_DAY_FROM NUMBER,
327 P_FORECAST_DAY_TO NUMBER,
328 P_INTRANSIT_TIME NUMBER,
329 P_TIME_UOM_CODE VARCHAR2,
330 P_SHIP_DELIVERY_PATTERN VARCHAR2,
331 P_ROUND_TO_STANDARD_PACK NUMBER,
332 P_INVENTORY_ITEM_ID NUMBER,
333 P_INVENTORY_ITEM_NUMBER VARCHAR2,
334 P_INVENTORY_ITEM_DESC VARCHAR2,
335 P_CUM_CONTROL_CODE VARCHAR2,
336 P_CUM_ORG_LEVEL_CODE VARCHAR2,
337 P_CUM_SHIPMENT_RULE_CODE VARCHAR2,
338 P_CUM_YESTERD_TIME_CUTOFF NUMBER,
339 P_UNSHIP_FIRM_CUTOFF_DAYS NUMBER,
340 P_UNSHIPPED_FIRM_DISP_CD VARCHAR2,
341 P_INTRANSIT_CALC_BASIS VARCHAR2,
342 P_SCH_LINE_ID NUMBER,
343 P_SCH_HEADER_ID NUMBER,
344 P_FROZEN_FLAG VARCHAR2,
345 P_EXCLUDE_NON_WORKDAYS_FLAG VARCHAR2
346 )
347 IS
348 BEGIN
349
350 INSERT INTO RLM_MATCH_SETUP_TEMP (CUSTOMER_ID ,
351 SHIP_FROM_ORG_ID ,
352 SHIP_TO_ORG_ID ,
353 SHIP_TO_ADDRESS_ID ,
354 CUSTOMER_ITEM_ID ,
355 CUSTOMER_ITEM_NUMBER ,
356 CUSTOMER_ITEM_DESC ,
357 FROZEN_DAY_FROM ,
358 FROZEN_DAY_TO ,
359 FIRM_DAY_FROM ,
360 FIRM_DAY_TO ,
361 FORECAST_DAY_FROM ,
362 FORECAST_DAY_TO ,
363 INTRANSIT_TIME ,
364 TIME_UOM_CODE ,
365 SHIP_DELIVERY_PATTERN ,
366 ROUND_TO_STANDARD_PACK ,
367 INVENTORY_ITEM_ID ,
368 INVENTORY_ITEM_NUMBER ,
369 INVENTORY_ITEM_DESC ,
370 CUM_CONTROL_CODE ,
371 CUM_ORG_LEVEL_CODE ,
372 CUM_SHIPMENT_RULE_CODE ,
373 CUM_YESTERD_TIME_CUTOFF,
374 UNSHIP_FIRM_CUTOFF_DAYS,
375 UNSHIPPED_FIRM_DISP_CD ,
376 INTRANSIT_CALC_BASIS ,
377 SCH_LINE_ID ,
378 SCH_HEADER_ID ,
379 FROZEN_FLAG ,
380 EXCLUDE_NON_WORKDAYS_FLAG
381 )
382 VALUES (
383 P_CUSTOMER_ID ,
384 P_SHIP_FROM_ORG_ID ,
385 P_SHIP_TO_ORG_ID ,
386 P_SHIP_TO_ADDRESS_ID ,
387 P_CUSTOMER_ITEM_ID ,
388 P_CUSTOMER_ITEM_NUMBER ,
389 P_CUSTOMER_ITEM_DESC ,
390 P_FROZEN_DAY_FROM ,
391 P_FROZEN_DAY_TO ,
392 P_FIRM_DAY_FROM ,
393 P_FIRM_DAY_TO ,
394 P_FORECAST_DAY_FROM ,
395 P_FORECAST_DAY_TO ,
396 P_INTRANSIT_TIME ,
397 P_TIME_UOM_CODE ,
398 P_SHIP_DELIVERY_PATTERN ,
399 P_ROUND_TO_STANDARD_PACK ,
400 P_INVENTORY_ITEM_ID ,
401 P_INVENTORY_ITEM_NUMBER ,
402 P_INVENTORY_ITEM_DESC ,
403 P_CUM_CONTROL_CODE ,
404 P_CUM_ORG_LEVEL_CODE ,
405 P_CUM_SHIPMENT_RULE_CODE ,
406 P_CUM_YESTERD_TIME_CUTOFF ,
407 P_UNSHIP_FIRM_CUTOFF_DAYS ,
408 P_UNSHIPPED_FIRM_DISP_CD ,
409 P_INTRANSIT_CALC_BASIS ,
410 P_SCH_LINE_ID ,
411 P_SCH_HEADER_ID ,
412 P_FROZEN_FLAG ,
413 P_EXCLUDE_NON_WORKDAYS_FLAG);
414
415 END insert_rlm_attributes;
416 ------------------------------------------------------------------------------------
417
418 /*=============================================================================
419
420 PROCEDURE NAME: insert_rlm_comp_sch_to_demand
421 ==============================================================================*/
422
423
424 PROCEDURE insert_rlm_comp_sch_to_demand( P_SCH_HEADER_ID NUMBER,
425 P_CUSTOMER_ID NUMBER,
426 P_SCHEDULE_TYPE VARCHAR2,
427 P_SCHED_HORIZON_END_DATE DATE,
428 P_SCHED_HORIZON_START_DATE DATE,
429 P_SCHEDULE_SOURCE VARCHAR2 ,
430 P_SCHEDULE_PURPOSE VARCHAR2,
431 P_SCHEDULE_REFERENCE_NUM VARCHAR2,
432 P_SCHED_GENERATION_DATE DATE,
433 P_ECE_TP_LOCATION_CODE_EXT VARCHAR2,
434 P_ECE_TP_TRANSLATOR_CODE VARCHAR2,
435 P_LAST_UPDATE_DATE DATE ,
436 P_CREATION_DATE DATE,
437 P_SCH_LINE_ID NUMBER,
438 P_CUSTOMER_ITEM_ID NUMBER,
439 P_DATE_TYPE_CODE VARCHAR2,
440 P_EDI_TEST_INDICATOR VARCHAR2,
441 P_INVENTORY_ITEM_ID NUMBER,
442 P_ITEM_DETAIL_SUBTYPE VARCHAR2,
443 P_ITEM_DETAIL_TYPE VARCHAR2,
444 P_QTY_TYPE_CODE VARCHAR2,
445 P_START_DATE_TIME DATE,
446 P_END_DATE_TIME DATE,
447 P_ITEM_DETAIL_QUANTITY NUMBER,
448 P_UOM_CODE VARCHAR2,
449 P_SHIP_FROM_ORG_ID NUMBER,
450 P_SHIP_TO_ORG_ID NUMBER,
451 P_SHIP_TO_ADDRESS_ID NUMBER,
452 P_INTMED_SHIP_TO_ORG_ID NUMBER,
453 P_CUSTOMER_DOCK_CODE VARCHAR2,
454 P_CUSTOMER_JOB VARCHAR2,
455 P_CUST_MODEL_SERIAL_NUMBER VARCHAR2,
456 P_CUST_PRODUCTION_LINE VARCHAR2,
457 P_CUST_PRODUCTION_SEQ_NUM VARCHAR2,
458 P_WEEK_START_DATE DATE,
459 P_WEEK_SCHEDULE_QTY NUMBER,
460 P_WEEK_END_DATE DATE,
461 P_ORDER_HEADER_ID NUMBER,
462 P_CUST_PO_NUMBER VARCHAR2,
463 P_CUSTOMER_ITEM_REVISION VARCHAR2,
464 P_PULL_SIGNAL_START_SERIAL_NUM VARCHAR2,
465 P_PULL_SIGNAL_END_SERIAL_NUM VARCHAR2 ,
466 P_PULL_SIGNAL_REF_NUM VARCHAR2,
467 P_CUSTOMER_REQUEST_DATE VARCHAR2,
468 P_RECORD_YEAR VARCHAR2,
469 P_WEEK_NAME VARCHAR2 ,
470 P_CUSTOMER_NAME VARCHAR2)
471 IS
472
473 BEGIN
474
475 INSERT INTO rlm_comp_sched_to_demand_temp (
476 SCH_HEADER_ID ,
477 CUSTOMER_ID,
478 SCHEDULE_TYPE ,
479 SCHED_HORIZON_END_DATE,
480 SCHED_HORIZON_START_DATE,
481 SCHEDULE_SOURCE ,
482 SCHEDULE_PURPOSE,
483 SCHEDULE_REFERENCE_NUM,
484 SCHED_GENERATION_DATE,
485 ECE_TP_LOCATION_CODE_EXT,
486 ECE_TP_TRANSLATOR_CODE,
487 LAST_UPDATE_DATE,
488 CREATION_DATE,
489 SCH_LINE_ID ,
490 CUSTOMER_ITEM_ID,
491 DATE_TYPE_CODE ,
492 EDI_TEST_INDICATOR,
493 INVENTORY_ITEM_ID,
494 ITEM_DETAIL_SUBTYPE,
495 ITEM_DETAIL_TYPE,
496 QTY_TYPE_CODE ,
497 START_DATE_TIME,
498 END_DATE_TIME,
499 ITEM_DETAIL_QUANTITY,
500 UOM_CODE ,
501 SHIP_FROM_ORG_ID,
502 SHIP_TO_ORG_ID ,
503 SHIP_TO_ADDRESS_ID ,
504 INTMED_SHIP_TO_ORG_ID,
505 CUSTOMER_DOCK_CODE ,
506 CUSTOMER_JOB ,
507 CUST_MODEL_SERIAL_NUMBER,
508 CUST_PRODUCTION_LINE,
509 CUST_PRODUCTION_SEQ_NUM,
510 WEEK_START_DATE,
511 WEEK_SCHEDULE_QTY,
512 WEEK_END_DATE,
513 ORDER_HEADER_ID,
514 CUST_PO_NUMBER,
515 CUSTOMER_ITEM_REVISION,
516 PULL_SIGNAL_START_SERIAL_NUM,
517 PULL_SIGNAL_END_SERIAL_NUM,
518 PULL_SIGNAL_REF_NUM,
519 CUSTOMER_REQUEST_DATE,
520 RECORD_YEAR,
521 WEEK_NAME,
522 CUSTOMER_NAME )
523 VALUES (P_SCH_HEADER_ID ,
524 P_CUSTOMER_ID ,
525 P_SCHEDULE_TYPE ,
526 P_SCHED_HORIZON_END_DATE ,
527 P_SCHED_HORIZON_START_DATE ,
528 P_SCHEDULE_SOURCE ,
529 P_SCHEDULE_PURPOSE ,
530 P_SCHEDULE_REFERENCE_NUM ,
531 P_SCHED_GENERATION_DATE ,
532 P_ECE_TP_LOCATION_CODE_EXT ,
533 P_ECE_TP_TRANSLATOR_CODE ,
534 P_LAST_UPDATE_DATE ,
535 P_CREATION_DATE ,
536 P_SCH_LINE_ID ,
537 P_CUSTOMER_ITEM_ID ,
538 P_DATE_TYPE_CODE ,
539 P_EDI_TEST_INDICATOR ,
540 P_INVENTORY_ITEM_ID ,
541 P_ITEM_DETAIL_SUBTYPE ,
542 P_ITEM_DETAIL_TYPE ,
543 P_QTY_TYPE_CODE ,
544 P_START_DATE_TIME ,
545 P_END_DATE_TIME ,
546 P_ITEM_DETAIL_QUANTITY ,
547 P_UOM_CODE ,
548 P_SHIP_FROM_ORG_ID ,
549 P_SHIP_TO_ORG_ID ,
550 P_SHIP_TO_ADDRESS_ID ,
551 P_INTMED_SHIP_TO_ORG_ID ,
552 P_CUSTOMER_DOCK_CODE ,
553 P_CUSTOMER_JOB ,
554 P_CUST_MODEL_SERIAL_NUMBER ,
555 P_CUST_PRODUCTION_LINE ,
556 P_CUST_PRODUCTION_SEQ_NUM ,
557 P_WEEK_START_DATE ,
558 P_WEEK_SCHEDULE_QTY ,
559 P_WEEK_END_DATE ,
560 P_ORDER_HEADER_ID ,
561 P_CUST_PO_NUMBER ,
562 P_CUSTOMER_ITEM_REVISION ,
563 P_PULL_SIGNAL_START_SERIAL_NUM ,
564 P_PULL_SIGNAL_END_SERIAL_NUM ,
565 P_PULL_SIGNAL_REF_NUM ,
566 P_CUSTOMER_REQUEST_DATE ,
567 P_RECORD_YEAR ,
568 P_WEEK_NAME ,
569 P_CUSTOMER_NAME ) ;
570 END insert_rlm_comp_sch_to_demand;
571 --------------------------------------------------------------------------------------
572
573 --
574 -- Main Procedure begins here
575 --
576
577 BEGIN
578 --
579 FOR v_cur_comp_sch_to_demand IN cur_comp_sch_to_demand LOOP
580
581 IF (l_customer_id = -9999 AND l_ship_from = -9999 AND l_ship_to = -9999 AND l_customer_item_id = -9999) OR
582 (l_customer_id <> v_cur_comp_sch_to_demand.customer_id
583 OR l_ship_from <> v_cur_comp_sch_to_demand.ship_from_org_id
584 OR l_ship_to <> v_cur_comp_sch_to_demand.ship_to_address_id
585 OR l_customer_item_id <> v_cur_comp_sch_to_demand.customer_item_id)
586 THEN
587
588 RLM_TPA_SV.get_setup_terms ( v_cur_comp_sch_to_demand.ship_from_org_id,
589 v_cur_comp_sch_to_demand.customer_id ,
590 v_cur_comp_sch_to_demand.ship_to_address_id,
591 v_cur_comp_sch_to_demand.customer_item_id,
592 l_NULL,
593 l_terms_rec ,
594 l_return_message ,
595 l_return_status
596 );
597
598 OPEN csr_sdp(l_terms_rec.ship_delivery_rule_name);
599 FETCH csr_sdp INTO l_ship_del_pattern;
600 CLOSE csr_sdp;
601
602 IF P_SCHEDULE_TYPE ='SHIPPING' THEN
603
604 l_frozen_day_from := l_terms_rec.shp_frozen_day_from;
605 l_frozen_day_to := l_terms_rec.shp_frozen_day_to ;
606 l_firm_day_from := l_terms_rec.shp_firm_day_from;
607 l_firm_day_to := l_terms_rec.shp_firm_day_to ;
608 l_forecast_day_from := l_terms_rec.shp_forecast_day_from;
609 l_forecast_day_to := l_terms_rec.shp_forecast_day_to;
610 l_frozen_flag := l_terms_rec.shp_frozen_flag ;
611
612 ELSIF P_SCHEDULE_TYPE ='SEQUENCED' THEN
613
614 l_frozen_day_from := l_terms_rec.seq_frozen_day_from;
615 l_frozen_day_to := l_terms_rec.seq_frozen_day_to;
616 l_firm_day_from := l_terms_rec.seq_firm_day_from;
617 l_firm_day_to := l_terms_rec.seq_firm_day_to;
618 l_forecast_day_from := l_terms_rec.seq_forecast_day_from;
619 l_forecast_day_to := l_terms_rec.seq_forecast_day_to;
620 l_frozen_flag := l_terms_rec.seq_frozen_flag;
621
622 ELSE
623
624 l_frozen_day_from := l_terms_rec.pln_frozen_day_from;
625 l_frozen_day_to := l_terms_rec.pln_frozen_day_to;
626 l_firm_day_from := l_terms_rec.pln_firm_day_from ;
627 l_firm_day_to := l_terms_rec.pln_firm_day_to;
628 l_forecast_day_from := l_terms_rec.pln_forecast_day_from ;
629 l_forecast_day_to := l_terms_rec.pln_forecast_day_to;
630 l_frozen_flag := l_terms_rec.pln_frozen_flag ;
631
632 END IF ;
633
634 insert_rlm_attributes ( v_cur_comp_sch_to_demand.customer_id,
635 v_cur_comp_sch_to_demand.ship_from_org_id,
636 v_cur_comp_sch_to_demand.ship_to_org_id,
637 v_cur_comp_sch_to_demand.ship_to_address_id,
638 v_cur_comp_sch_to_demand.customer_item_id,
639 v_cur_comp_sch_to_demand.customer_item_number,
640 v_cur_comp_sch_to_demand.customer_item_desc,
641 l_frozen_day_from,
642 l_frozen_day_to,
643 l_firm_day_from,
644 l_firm_day_to,
645 l_forecast_day_from,
646 l_forecast_day_to,
647 l_terms_rec.intransit_time,
648 l_terms_rec.time_uom_code,
649 l_ship_del_pattern,
650 l_terms_rec.std_pack_qty,
651 v_cur_comp_sch_to_demand.inventory_item_id,
652 v_cur_comp_sch_to_demand.inventory_item_number,
653 v_cur_comp_sch_to_demand.inventory_item_desc,
654 NULL,
655 NULL,
656 NULL,
657 NULL,
658 l_terms_rec.unship_firm_cutoff_days,
659 l_terms_rec.unshipped_firm_disp_cd,
660 l_terms_rec.intransit_calc_basis,
661 v_cur_comp_sch_to_demand.line_id,
662 v_cur_comp_sch_to_demand.header_id,
663 l_frozen_flag,
664 l_terms_rec.exclude_non_workdays_flag);
665 populate_within_across;
666
667 DECLARE
668
669 v_within_key VARCHAR2(100) ;
670 v_across_key VARCHAR2(100) ;
671 v_attribute_name VARCHAR2(100) ;
672
673 BEGIN
674
675 FOR i in 1..l_match_attribute.count
676
677 LOOP
678 IF l_match_attribute.count > 0 THEN
679 v_within_key := l_match_attribute(i).Within_key;
680 v_across_key := l_match_attribute(i).across_key;
681 v_attribute_name := l_match_attribute(i).attribute_name;
682 ELSE
683 v_within_key := NULL;
684 v_across_key := NULL;
685 v_attribute_name := NULL;
686 END IF;
687
688 INSERT INTO RLM_SEQ_MATCH_TEMP (CUSTOMER_ID ,
689 SHIP_FROM_ORG_ID ,
690 SHIP_TO_ORG_ID,
691 SHIP_TO_ADDRESS_ID,
692 CUSTOMER_ITEM_ID,
693 MATCH_WITHIN ,
694 MATCH_ACROSS,
695 MEANING
696 )
697 VALUES
698 ( v_cur_comp_sch_to_demand.customer_id,
699 v_cur_comp_sch_to_demand.ship_from_org_id,
700 v_cur_comp_sch_to_demand.ship_to_org_id,
701 v_cur_comp_sch_to_demand.ship_to_address_id,
702 v_cur_comp_sch_to_demand.customer_item_id,
703 v_within_key,
704 v_across_key,
705 v_attribute_name);
706 END LOOP;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 NULL;
711 END;
712
713 l_match_attribute.delete;
714
715 l_customer_id := v_cur_comp_sch_to_demand.customer_id;
716 l_ship_from := v_cur_comp_sch_to_demand.ship_from_org_id;
717 l_ship_to := v_cur_comp_sch_to_demand.ship_to_address_id;
718 l_customer_item_id := v_cur_comp_sch_to_demand.customer_item_id;
719 l_NULL := NULL ;
720
721 END IF; -- New combination of customer_id, ship_from_org_id, ship_to_address_id, customer_item_id
722
723 IF v_cur_comp_sch_to_demand.schedule_type <> 'SEQUENCED' THEN
724
725 -- For Quarterly demand
726
727 IF v_cur_comp_sch_to_demand.item_detail_subtype = g_quarter THEN
728
729 IF (MOD(v_cur_comp_sch_to_demand.item_detail_quantity,1)>0) THEN
730 l_WholeNumber := FALSE;
731 END IF;
732
733 l_LastDayQuarter := last_day(ADD_MONTHS(v_cur_comp_sch_to_demand.start_date_time,2));
734
735 l_WeeksInQuarter := TRUNC((l_LastDayQuarter - v_cur_comp_sch_to_demand.start_date_time)/7 );
736
737 FOR week IN 0..l_WeeksInQuarter-1 LOOP
738
739 l_week_name := get_week_name( week+1);
740
741 l_inputrec.primaryquantity := v_cur_comp_sch_to_demand.item_detail_quantity;
742
743 v_item_detail_quantity := RLM_TPA_SV.get_weekly_quantity(
744 l_wholenumber,
745 week+1,
746 l_inputrec,
747 l_WeeksInQuarter) ;
748
749 insert_rlm_comp_sch_to_demand(
750 v_cur_comp_sch_to_demand.header_id ,
751 v_cur_comp_sch_to_demand.customer_id ,
752 v_cur_comp_sch_to_demand.schedule_type ,
753 v_cur_comp_sch_to_demand.sched_horizon_end_date,
754 v_cur_comp_sch_to_demand.sched_horizon_start_date,
755 v_cur_comp_sch_to_demand.schedule_source ,
756 v_cur_comp_sch_to_demand.schedule_purpose,
757 v_cur_comp_sch_to_demand.schedule_reference_num,
758 v_cur_comp_sch_to_demand.sched_generation_date,
759 v_cur_comp_sch_to_demand.ece_tp_location_code_ext,
760 v_cur_comp_sch_to_demand.ece_tp_translator_code,
761 v_cur_comp_sch_to_demand.last_update_date,
762 v_cur_comp_sch_to_demand.creation_date,
763 v_cur_comp_sch_to_demand.line_id ,
764 v_cur_comp_sch_to_demand.customer_item_id,
765 v_cur_comp_sch_to_demand.date_type_code ,
766 NULL , ---edi_test_indicator,
767 v_cur_comp_sch_to_demand.inventory_item_id,
768 v_cur_comp_sch_to_demand.item_detail_subtype,
769 v_cur_comp_sch_to_demand.item_detail_type,
770 v_cur_comp_sch_to_demand.qty_type_code ,
771 v_cur_comp_sch_to_demand.start_date_time + 7*week,
772 v_cur_comp_sch_to_demand.end_date_time,
773 v_item_detail_quantity ,
774 v_cur_comp_sch_to_demand.uom_code ,
775 v_cur_comp_sch_to_demand.ship_from_org_id,
776 v_cur_comp_sch_to_demand.ship_to_org_id ,
777 v_cur_comp_sch_to_demand.ship_to_address_id,
778 NULL ,--- intmed_ship_to_org_id,
779 v_cur_comp_sch_to_demand.customer_dock_code ,
780 v_cur_comp_sch_to_demand.customer_job ,
781 v_cur_comp_sch_to_demand.cust_model_serial_number,
782 v_cur_comp_sch_to_demand.cust_production_line,
783 v_cur_comp_sch_to_demand.cust_production_seq_num,
784 get_monday_date(v_cur_comp_sch_to_demand.start_date_time + 7*week),
785 NULL ,
786 NULL ,
787 v_cur_comp_sch_to_demand.order_header_id,
788 v_cur_comp_sch_to_demand.cust_po_number,
789 v_cur_comp_sch_to_demand.customer_item_revision,
790 v_cur_comp_sch_to_demand.pull_signal_start_serial_num,
791 v_cur_comp_sch_to_demand.pull_signal_end_serial_num,
792 v_cur_comp_sch_to_demand.pull_signal_ref_num,
793 v_cur_comp_sch_to_demand.customer_request_date,
794 v_cur_comp_sch_to_demand.record_year,
795 l_week_name,
796 v_cur_comp_sch_to_demand.customer_name
797 ) ;
798
799 END LOOP;
800
801 l_week_name := NULL ;
802
803 -- For Monthly demand
804
805 ELSIF v_cur_comp_sch_to_demand.item_detail_subtype = g_month THEN
806
807 IF (MOD(v_cur_comp_sch_to_demand .item_detail_quantity, 1) > 0) THEN
808 l_WholeNumber := FALSE;
809 END IF;
810
811 FOR DAY IN 0..3 LOOP
812
813 l_week_name := get_week_name(DAY+1 );
814
815 l_inputrec.primaryquantity := v_cur_comp_sch_to_demand.item_detail_quantity;
816
817 v_item_detail_quantity := RLM_TPA_SV.get_weekly_quantity(
818 l_wholenumber,
819 day+1,
820 l_inputrec,
821 4);
822
823 insert_rlm_comp_sch_to_demand
824
825 (v_cur_comp_sch_to_demand.header_id ,
826 v_cur_comp_sch_to_demand.customer_id ,
827 v_cur_comp_sch_to_demand.schedule_type ,
828 v_cur_comp_sch_to_demand.sched_horizon_end_date,
829 v_cur_comp_sch_to_demand.sched_horizon_start_date,
830 v_cur_comp_sch_to_demand.schedule_source ,
831 v_cur_comp_sch_to_demand.schedule_purpose,
832 v_cur_comp_sch_to_demand.schedule_reference_num,
833 v_cur_comp_sch_to_demand.sched_generation_date,
834 v_cur_comp_sch_to_demand.ece_tp_location_code_ext,
835 v_cur_comp_sch_to_demand.ece_tp_translator_code,
836 v_cur_comp_sch_to_demand.last_update_date,
837 v_cur_comp_sch_to_demand.creation_date,
838 v_cur_comp_sch_to_demand.line_id ,
839 v_cur_comp_sch_to_demand.customer_item_id,
840 v_cur_comp_sch_to_demand.date_type_code ,
841 NULL , ---edi_test_indicator,
842 v_cur_comp_sch_to_demand.inventory_item_id,
843 v_cur_comp_sch_to_demand.item_detail_subtype,
844 v_cur_comp_sch_to_demand.item_detail_type,
845 v_cur_comp_sch_to_demand.qty_type_code ,
846 v_cur_comp_sch_to_demand.start_date_time + 7*day,
847 v_cur_comp_sch_to_demand.end_date_time,
848 v_item_detail_quantity,
849 v_cur_comp_sch_to_demand.uom_code ,
850 v_cur_comp_sch_to_demand.ship_from_org_id,
851 v_cur_comp_sch_to_demand.ship_to_org_id,
852 v_cur_comp_sch_to_demand.ship_to_address_id,
853 NULL ,--- intmed_ship_to_org_id,
854 v_cur_comp_sch_to_demand.customer_dock_code ,
855 v_cur_comp_sch_to_demand.customer_job ,
856 v_cur_comp_sch_to_demand.cust_model_serial_number,
857 v_cur_comp_sch_to_demand.cust_production_line,
858 v_cur_comp_sch_to_demand.cust_production_seq_num,
859 get_monday_date(v_cur_comp_sch_to_demand.start_date_time + 7 * day),
860 NULL , ---week_schedule_qty
861 NULL , -- week_end_date
862 v_cur_comp_sch_to_demand.order_header_id,
863 v_cur_comp_sch_to_demand.cust_po_number,
864 v_cur_comp_sch_to_demand.customer_item_revision,
865 v_cur_comp_sch_to_demand.pull_signal_start_serial_num,
866 v_cur_comp_sch_to_demand.pull_signal_end_serial_num,
867 v_cur_comp_sch_to_demand.pull_signal_ref_num,
868 v_cur_comp_sch_to_demand.customer_request_date,
869 v_cur_comp_sch_to_demand.record_year,
870 l_week_name,
871 v_cur_comp_sch_to_demand.customer_name
872 ) ;
873 END LOOP;
874
875 l_week_name := NULL ;
876
877 -- For flexible demand
878
879 ELSIF v_cur_comp_sch_to_demand.item_detail_subtype = g_flexible THEN
880
881 IF (MOD(v_cur_comp_sch_to_demand.ITEM_DETAIL_QUANTITY,1)>0) THEN
882 l_WholeNumber := FALSE;
883 END IF;
884
885 WHILE ((v_cur_comp_sch_to_demand.start_date_time+(7*l_Buckets)) <= v_cur_comp_sch_to_demand.end_date_time )
886 LOOP
887
888 l_Buckets := l_Buckets + 1;
889
890 END LOOP;
891
892 FOR l_Count IN 1..l_buckets
893 LOOP
894
895 l_week_name := get_week_name(l_count);
896
897 l_inputrec.primaryquantity := v_cur_comp_sch_to_demand.item_detail_quantity ;
898
899 v_item_detail_quantity := RLM_TPA_SV.get_weekly_quantity(
900 l_wholenumber,
901 l_count,
902 l_inputrec,
903 l_buckets);
904
905 insert_rlm_comp_sch_to_demand (v_cur_comp_sch_to_demand.header_id ,
906 v_cur_comp_sch_to_demand.customer_id ,
907 v_cur_comp_sch_to_demand.schedule_type ,
908 v_cur_comp_sch_to_demand.sched_horizon_end_date,
909 v_cur_comp_sch_to_demand.sched_horizon_start_date,
910 v_cur_comp_sch_to_demand.schedule_source ,
911 v_cur_comp_sch_to_demand.schedule_purpose,
912 v_cur_comp_sch_to_demand.schedule_reference_num,
913 v_cur_comp_sch_to_demand.sched_generation_date,
914 v_cur_comp_sch_to_demand.ece_tp_location_code_ext,
915 v_cur_comp_sch_to_demand.ece_tp_translator_code,
916 v_cur_comp_sch_to_demand.last_update_date,
917 v_cur_comp_sch_to_demand.creation_date,
918 v_cur_comp_sch_to_demand.line_id ,
919 v_cur_comp_sch_to_demand.customer_item_id,
920 v_cur_comp_sch_to_demand.date_type_code ,
921 NULL , ---edi_test_indicator,
922 v_cur_comp_sch_to_demand.inventory_item_id,
923 v_cur_comp_sch_to_demand.item_detail_subtype,
924 v_cur_comp_sch_to_demand.item_detail_type,
925 v_cur_comp_sch_to_demand.qty_type_code ,
926 v_cur_comp_sch_to_demand.start_date_time + 7*(l_Count-1),
927 v_cur_comp_sch_to_demand.end_date_time,
928 v_item_detail_quantity ,
929 v_cur_comp_sch_to_demand.uom_code ,
930 v_cur_comp_sch_to_demand.ship_from_org_id,
931 v_cur_comp_sch_to_demand.ship_to_org_id ,
932 v_cur_comp_sch_to_demand.ship_to_address_id,
933 NULL,
934 v_cur_comp_sch_to_demand.customer_dock_code ,
935 v_cur_comp_sch_to_demand.customer_job ,
936 v_cur_comp_sch_to_demand.cust_model_serial_number,
937 v_cur_comp_sch_to_demand.cust_production_line,
938 v_cur_comp_sch_to_demand.cust_production_seq_num,
939 get_monday_date(v_cur_comp_sch_to_demand.start_date_time + 7*(l_Count-1)),
940 NULL ,
941 NULL ,
942 v_cur_comp_sch_to_demand.order_header_id,
943 v_cur_comp_sch_to_demand.cust_po_number,
944 v_cur_comp_sch_to_demand.customer_item_revision,
945 v_cur_comp_sch_to_demand.pull_signal_start_serial_num,
946 v_cur_comp_sch_to_demand.pull_signal_end_serial_num,
947 v_cur_comp_sch_to_demand.pull_signal_ref_num,
948 v_cur_comp_sch_to_demand.customer_request_date,
949 v_cur_comp_sch_to_demand.record_year,
950 l_week_name,
951 v_cur_comp_sch_to_demand.customer_name
952 ) ;
953 END LOOP;
954 l_week_name := NULL;
955 -- For Daily and weekly demand
956 --
957 ELSE
958
959 insert_rlm_comp_sch_to_demand(
960 v_cur_comp_sch_to_demand.header_id ,
961 v_cur_comp_sch_to_demand.customer_id ,
962 v_cur_comp_sch_to_demand.schedule_type ,
963 v_cur_comp_sch_to_demand.sched_horizon_end_date,
964 v_cur_comp_sch_to_demand.sched_horizon_start_date,
965 v_cur_comp_sch_to_demand.schedule_source ,
966 v_cur_comp_sch_to_demand.schedule_purpose,
967 v_cur_comp_sch_to_demand.schedule_reference_num,
968 v_cur_comp_sch_to_demand.sched_generation_date,
969 v_cur_comp_sch_to_demand.ece_tp_location_code_ext,
970 v_cur_comp_sch_to_demand.ece_tp_translator_code,
971 v_cur_comp_sch_to_demand.last_update_date,
972 v_cur_comp_sch_to_demand.creation_date,
973 v_cur_comp_sch_to_demand.line_id ,
974 v_cur_comp_sch_to_demand.customer_item_id,
975 v_cur_comp_sch_to_demand.date_type_code ,
976 NULL , ---edi_test_indicator,
977 v_cur_comp_sch_to_demand.inventory_item_id,
978 v_cur_comp_sch_to_demand.item_detail_subtype,
979 v_cur_comp_sch_to_demand.item_detail_type,
980 v_cur_comp_sch_to_demand.qty_type_code ,
981 v_cur_comp_sch_to_demand.start_date_time,
982 v_cur_comp_sch_to_demand.end_date_time,
983 v_cur_comp_sch_to_demand.item_detail_quantity,
984 v_cur_comp_sch_to_demand.uom_code ,
985 v_cur_comp_sch_to_demand.ship_from_org_id,
986 v_cur_comp_sch_to_demand.ship_to_org_id ,
987 v_cur_comp_sch_to_demand.ship_to_address_id,
988 NULL ,--- intmed_ship_to_org_id,
989 v_cur_comp_sch_to_demand.customer_dock_code ,
990 v_cur_comp_sch_to_demand.customer_job ,
991 v_cur_comp_sch_to_demand.cust_model_serial_number,
992 v_cur_comp_sch_to_demand.cust_production_line,
993 v_cur_comp_sch_to_demand.cust_production_seq_num,
994 get_monday_date(v_cur_comp_sch_to_demand .start_date_time),
995 NULL ,
996 NULL ,
997 v_cur_comp_sch_to_demand.order_header_id,
998 v_cur_comp_sch_to_demand.cust_po_number,
999 v_cur_comp_sch_to_demand.customer_item_revision,
1000 v_cur_comp_sch_to_demand.pull_signal_start_serial_num,
1001 v_cur_comp_sch_to_demand.pull_signal_end_serial_num,
1002 v_cur_comp_sch_to_demand.pull_signal_ref_num,
1003 v_cur_comp_sch_to_demand.customer_request_date,
1004 v_cur_comp_sch_to_demand.record_year,
1005 NULL,
1006 v_cur_comp_sch_to_demand.customer_name
1007 ) ;
1008
1009
1010 END IF; -- Demand Type
1011
1012 -- Sequence schedule
1013
1014 ELSE -- schedule type
1015
1016 insert_rlm_comp_sch_to_demand (
1017 v_cur_comp_sch_to_demand.header_id ,
1018 v_cur_comp_sch_to_demand.customer_id ,
1019 v_cur_comp_sch_to_demand.schedule_type ,
1020 v_cur_comp_sch_to_demand.sched_horizon_end_date,
1021 v_cur_comp_sch_to_demand.sched_horizon_start_date,
1022 v_cur_comp_sch_to_demand.schedule_source ,
1023 v_cur_comp_sch_to_demand.schedule_purpose,
1024 v_cur_comp_sch_to_demand.schedule_reference_num,
1025 v_cur_comp_sch_to_demand.sched_generation_date,
1026 v_cur_comp_sch_to_demand.ece_tp_location_code_ext,
1027 v_cur_comp_sch_to_demand.ece_tp_translator_code,
1028 v_cur_comp_sch_to_demand.last_update_date,
1029 v_cur_comp_sch_to_demand.creation_date,
1030 v_cur_comp_sch_to_demand.line_id ,
1031 v_cur_comp_sch_to_demand.customer_item_id,
1032 v_cur_comp_sch_to_demand.date_type_code ,
1033 NULL ,
1034 v_cur_comp_sch_to_demand.inventory_item_id,
1035 v_cur_comp_sch_to_demand.item_detail_subtype,
1036 v_cur_comp_sch_to_demand.item_detail_type,
1037 v_cur_comp_sch_to_demand.qty_type_code ,
1038 v_cur_comp_sch_to_demand.start_date_time,
1039 v_cur_comp_sch_to_demand.end_date_time,
1040 v_cur_comp_sch_to_demand.item_detail_quantity,
1041 v_cur_comp_sch_to_demand.uom_code ,
1042 v_cur_comp_sch_to_demand.ship_from_org_id,
1043 v_cur_comp_sch_to_demand.ship_to_org_id ,
1044 v_cur_comp_sch_to_demand.ship_to_address_id,
1045 NULL ,--- intmed_ship_to_org_id,
1046 v_cur_comp_sch_to_demand.customer_dock_code ,
1047 v_cur_comp_sch_to_demand.customer_job ,
1048 v_cur_comp_sch_to_demand.cust_model_serial_number,
1049 v_cur_comp_sch_to_demand.cust_production_line,
1050 v_cur_comp_sch_to_demand.cust_production_seq_num,
1051 sysdate,
1052 NULL ,
1053 NULL ,
1054 v_cur_comp_sch_to_demand.order_header_id,
1055 v_cur_comp_sch_to_demand.cust_po_number,
1056 v_cur_comp_sch_to_demand.customer_item_revision,
1057 v_cur_comp_sch_to_demand.pull_signal_start_serial_num,
1058 v_cur_comp_sch_to_demand.pull_signal_end_serial_num,
1059 v_cur_comp_sch_to_demand.pull_signal_ref_num,
1060 v_cur_comp_sch_to_demand.customer_request_date,
1061 v_cur_comp_sch_to_demand.record_year,
1062 NULL,
1063 v_cur_comp_sch_to_demand.customer_name
1064 ) ;
1065 END IF ;
1066
1067 END LOOP ; -- Outer loop
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 NULL;
1072
1073 END proc_comp_sch_to_demand;
1074
1075 /*=============================================================================
1076
1077 FUNCTION NAME: get_monday_date
1078
1079 ==============================================================================*/
1080
1081 FUNCTION get_monday_date (p_date DATE) RETURN DATE
1082 IS
1083
1084 l_date date;
1085 l_p_date date;
1086
1087 BEGIN
1088 l_p_date := trunc(p_date);
1089
1090 SELECT l_p_date-decode(to_char(l_p_date,'D'),1,6,3,1,4,2,5,3,6,4,7,5,0)
1091 INTO l_date
1092 FROM dual;
1093
1094 RETURN l_date;
1095
1096 END get_monday_date ;
1097
1098 /*=============================================================================
1099
1100 FUNCTION NAME: get_week_name
1101
1102 ==============================================================================*/
1103
1104 Function get_week_name(v_week_number in NUMBER) RETURN VARCHAR2 is
1105 v_week_name Varchar2(100) ;
1106 BEGIN
1107
1108 IF v_week_number = 1 then
1109 fnd_message.set_name('RLM','RLM_FIRST_WEEK');
1110 v_week_name :=fnd_message.get;
1111 ELSIF
1112 v_week_number = 2 then
1113 fnd_message.set_name('RLM','RLM_SECOND_WEEK');
1114 v_week_name :=fnd_message.get;
1115 ELSIF
1116 v_week_number = 3 then
1117 fnd_message.set_name('RLM','RLM_THIRD_WEEK');
1118 v_week_name :=fnd_message.get;
1119 ELSIF
1120 v_week_number = 4 then
1121 fnd_message.set_name('RLM','RLM_FOURTH_WEEK');
1122 v_week_name :=fnd_message.get;
1123 ELSIF
1124 v_week_number = 5 then
1125 fnd_message.set_name('RLM','RLM_FIFTH_WEEK');
1126 v_week_name :=fnd_message.get;
1127 ELSIF
1128 v_week_number = 6 then
1129 fnd_message.set_name('RLM','RLM_SIXTH_WEEK');
1130 v_week_name :=fnd_message.get;
1131 ELSIF
1132 v_week_number = 7 then
1133 fnd_message.set_name('RLM','RLM_SEVENTH_WEEK');
1134 v_week_name :=fnd_message.get;
1135 ELSIF
1136 v_week_number = 8 then
1137 fnd_message.set_name('RLM','RLM_EIGHTH_WEEK');
1138 v_week_name :=fnd_message.get;
1139 ELSIF
1140 v_week_number = 9 then
1141 fnd_message.set_name('RLM','RLM_NINTH_WEEK');
1142 v_week_name :=fnd_message.get;
1143 ELSIF
1144 v_week_number = 10 then
1145 fnd_message.set_name('RLM','RLM_TENTH_WEEK');
1146 v_week_name :=fnd_message.get;
1147 ELSIF
1148 v_week_number = 11 then
1149 fnd_message.set_name('RLM','RLM_ELEVENTH_WEEK');
1150 v_week_name :=fnd_message.get;
1151 ELSIF
1152 v_week_number = 12 then
1153 fnd_message.set_name('RLM','RLM_TWELFTH_WEEK');
1154 v_week_name :=fnd_message.get;
1155 ELSIF
1156 v_week_number = 13 then
1157 fnd_message.set_name('RLM','RLM_THIRTEENTH_WEEK');
1158 v_week_name :=fnd_message.get;
1159 ELSE
1160 RETURN(NULL);
1161 END IF ;
1162 RETURN (v_week_name);
1163 END get_week_name;
1164
1165
1166 END RLM_COMP_SCH_TO_DEMAND_SV;