[Home] [Help]
PACKAGE BODY: APPS.MSC_X_RECEIVE_CAPACITY_PKG
Source
1 PACKAGE BODY MSC_X_RECEIVE_CAPACITY_PKG AS
2 /* $Header: MSCXRSCB.pls 120.5 2006/05/19 02:19:58 shwmathu noship $ */
3
4 G_DAY CONSTANT INTEGER := 1;
5 G_WEEK CONSTANT INTEGER := 2;
6 G_MONTH CONSTANT INTEGER := 3;
7 G_CAL_INSTANCE_ID number;
8
9 CURSOR receive_capacity_c(p_horizon_start_date In DATE,
10 p_horizon_end_date In DATE,
11 p_abc_class In VARCHAR2,
12 p_item_id In NUMBER,
13 p_planner In VARCHAR2,
14 p_cal_sr_instance_id IN NUMBER,
15 p_sce_supplier_id IN NUMBER,
16 p_sce_supplier_site_id In NUMBER)
17 IS
18 SELECT distinct tp.sr_instance_id,
19 sd.publisher_id,
20 sd.publisher_name,
21 sd.publisher_site_id,
22 sd.publisher_site_name,
23 sd.customer_name,
24 sd.customer_id,
25 sd.customer_site_name,
26 sd.customer_site_id,
27 sd.inventory_item_id,
28 sd.item_name,
29 sd.tp_quantity,
30 sd.tp_uom_code,
31 sd.receipt_date,
32 sd.bucket_type,
33 sd.receipt_date,
34 sd.receipt_date,
35 mis.organization_id,
36 tp2.organization_code,
37 nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
38 --mis.DELIVERY_CALENDAR_CODE,
39 map2.tp_key, -- supplier_aps_id
40 map3.tp_key, -- supplier_site aps id
41 item1.ROUNDING_CONTROL_TYPE
42 FROM msc_sup_dem_entries_v sd,
43 msc_trading_partner_maps map1,
44 msc_trading_partners tp,
45 msc_system_items item,
46 msc_company_relationships cr,
47 msc_trading_partner_maps map2,
48 msc_trading_partner_maps map3,
49 msc_item_suppliers mis,
50 msc_trading_partners tp2,
51 msc_system_items item1
52 WHERE sd.plan_id = -1
53 AND sd.publisher_order_type = SUPPLY_COMMIT
54 AND sd.bucket_Type = G_DAY
55 AND map1.tp_key = tp.partner_id
56 AND map1.map_type = 2 -- company
57 AND map1.company_key = sd.customer_site_id
58 AND sd.customer_id = 1 --OEM
59 AND tp.partner_type = 3
60 AND tp.company_id is null
61 AND item.plan_id = -1
62 AND sd.inventory_item_id = item.inventory_item_id
63 AND item.sr_instance_id = tp.sr_instance_id
64 AND item.organization_id = tp.sr_tp_id
65 --AND sd.quantity > 0
66 AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
67 AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
68 AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
69 AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
70 AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
71 AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
72 and nvl(trunc(p_horizon_end_date),sd.receipt_date)
73 and cr.object_id = sd.publisher_id
74 and cr.subject_id = sd.customer_id
75 and cr.relationship_type = 2
76 and map2.map_type = 1
77 and map2.company_key = cr.relationship_id
78 and map3.map_type = 3
79 and map3.company_key = sd.publisher_site_id
80 and mis.plan_id = item.plan_id
81 and mis.sr_instance_id = item.sr_instance_id
82 and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
83 and mis.SUPPLIER_ID = map2.tp_key
84 and mis.SUPPLIER_SITE_ID = map3.tp_key
85 and mis.using_organization_id = -1
86 and mis.sr_instance_id = tp2.sr_instance_id
87 and mis.organization_id = tp2.sr_tp_id
88 and tp2.partner_type = 3
89 and item1.plan_id = mis.plan_id
90 and item1.sr_instance_id = mis.sr_instance_id
91 and item1.organization_id = mis.organization_id
92 and item1.inventory_item_id = mis.inventory_item_id
93 UNION
94 SELECT distinct tp.sr_instance_id,
95 sd.publisher_id,
96 sd.publisher_name,
97 sd.publisher_site_id,
98 sd.publisher_site_name,
99 sd.customer_name,
100 sd.customer_id,
101 sd.customer_site_name,
102 sd.customer_site_id,
103 sd.inventory_item_id,
104 sd.item_name,
105 sd.tp_quantity,
106 sd.tp_uom_code,
107 sd.receipt_date,
108 sd.bucket_type,
109 mcd.week_start_date,
110 mcd.next_date-1,
111 mis.organization_id,
112 tp2.organization_code,
113 nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
114 --mis.DELIVERY_CALENDAR_CODE,
115 map2.tp_key,
116 map3.tp_key,
117 item1.ROUNDING_CONTROL_TYPE
118 FROM msc_sup_dem_entries_v sd,
119 msc_trading_partner_maps map1,
120 msc_trading_partners tp,
121 msc_system_items item,
122 MSC_CAL_WEEK_START_DATES mcd,
123 msc_company_relationships cr,
124 msc_trading_partner_maps map2,
125 msc_trading_partner_maps map3,
126 msc_item_suppliers mis,
127 msc_trading_partners tp2,
128 msc_system_items item1
129 WHERE sd.plan_id = -1
130 AND sd.publisher_order_type = SUPPLY_COMMIT
131 AND sd.bucket_Type = G_WEEK
132 AND map1.tp_key = tp.partner_id
133 AND map1.map_type = 2 -- company
134 AND map1.company_key = sd.customer_site_id
135 AND sd.customer_id = 1 --OEM
136 AND tp.partner_type = 3
137 AND tp.company_id is null
138 AND item.plan_id = -1
139 AND sd.inventory_item_id = item.inventory_item_id
140 AND item.sr_instance_id = tp.sr_instance_id
141 AND item.organization_id = tp.sr_tp_id
142 --AND sd.quantity > 0
143 AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
144 AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
145 AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
146 AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
147 AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
148 AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
149 and nvl(trunc(p_horizon_end_date),sd.receipt_date)
150 AND mcd.sr_instance_id = decode(mis.delivery_calendar_code,null,p_cal_sr_instance_id
151 ,tp.sr_instance_id)
152 AND mcd.CALENDAR_CODE = nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR)
153 AND mcd.EXCEPTION_SET_ID = -1
154 AND to_char(sd.receipt_date,'J') between to_char(mcd.WEEK_START_DATE,'J')
155 and to_char(mcd.NEXT_DATE,'J')
156 AND to_char(sd.receipt_date,'J') < TO_CHAR(mcd.NEXT_DATE,'J')
157 and cr.object_id = sd.publisher_id
158 and cr.subject_id = sd.customer_id
159 and cr.relationship_type = 2
160 and map2.map_type = 1
161 and map2.company_key = cr.relationship_id
162 and map3.map_type = 3
163 and map3.company_key = sd.publisher_site_id
164 and mis.plan_id = -1
165 and mis.sr_instance_id = item.sr_instance_id
166 and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
167 and mis.SUPPLIER_ID = map2.tp_key
168 and mis.SUPPLIER_SITE_ID = map3.tp_key
169 and mis.using_organization_id = -1
170 and mis.sr_instance_id = tp2.sr_instance_id
171 and mis.organization_id = tp2.sr_tp_id
172 and tp2.partner_type = 3
173 and item1.plan_id = mis.plan_id
174 and item1.sr_instance_id = mis.sr_instance_id
175 and item1.organization_id = mis.organization_id
176 and item1.inventory_item_id = mis.inventory_item_id
177 UNION
178 SELECT distinct tp.sr_instance_id,
179 sd.publisher_id,
180 sd.publisher_name,
181 sd.publisher_site_id,
182 sd.publisher_site_name,
183 sd.customer_name,
184 sd.customer_id,
185 sd.customer_site_name,
186 sd.customer_site_id,
187 sd.inventory_item_id,
188 sd.item_name,
189 sd.tp_quantity,
190 sd.tp_uom_code,
191 sd.receipt_date,
192 sd.bucket_type,
193 mpd.period_start_date,
194 mpd.next_Date-1,
195 mis.organization_id,
196 tp2.organization_code,
197 nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
198 --mis.DELIVERY_CALENDAR_CODE,
199 map2.tp_key,
200 map3.tp_key,
201 item1.ROUNDING_CONTROL_TYPE
202 FROM msc_sup_dem_entries_v sd,
203 msc_trading_partner_maps map1,
204 msc_trading_partners tp,
205 msc_system_items item,
206 MSC_PERIOD_START_DATES mpd,
207 msc_company_relationships cr,
208 msc_trading_partner_maps map2,
209 msc_trading_partner_maps map3,
210 msc_item_suppliers mis,
211 msc_trading_partners tp2,
212 msc_system_items item1
213 WHERE sd.plan_id = -1
214 AND sd.publisher_order_type = SUPPLY_COMMIT
215 AND sd.bucket_Type = G_MONTH
216 AND map1.tp_key = tp.partner_id
217 AND map1.map_type = 2 -- company
218 AND map1.company_key = sd.customer_site_id
219 AND sd.customer_id = 1 --OEM
220 AND tp.partner_type = 3
221 AND tp.company_id is null
222 AND item.plan_id = -1
223 AND sd.inventory_item_id = item.inventory_item_id
224 AND item.sr_instance_id = tp.sr_instance_id
225 AND item.organization_id = tp.sr_tp_id
226 --AND sd.quantity > 0
227 AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
228 AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
229 AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
230 AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
231 AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
232 AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
233 and nvl(trunc(p_horizon_end_date),sd.receipt_date)
234 AND mpd.sr_instance_id = decode(mis.delivery_calendar_code,null,p_cal_sr_instance_id
235 ,tp.sr_instance_id)
236 AND mpd.CALENDAR_CODE = nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR)
237 AND mpd.EXCEPTION_SET_ID = -1
238 AND to_char(sd.receipt_date,'J') between to_char(mpd.PERIOD_START_DATE,'J')
239 and to_char(mpd.NEXT_DATE,'J')
240 AND to_char(sd.receipt_date,'J') < TO_CHAR(mpd.NEXT_DATE,'J')
241 and cr.object_id = sd.publisher_id
242 and cr.subject_id = sd.customer_id
243 and cr.relationship_type = 2
244 and map2.map_type = 1
245 and map2.company_key = cr.relationship_id
246 and map3.map_type = 3
247 and map3.company_key = sd.publisher_site_id
248 and mis.plan_id = -1
249 and mis.sr_instance_id = item.sr_instance_id
250 --and mis.ORGANIZATION_ID = item.ORGANIZATION_ID
251 and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
252 and mis.SUPPLIER_ID = map2.tp_key
253 and mis.SUPPLIER_SITE_ID = map3.tp_key
254 and mis.using_organization_id = -1
255 and mis.sr_instance_id = tp2.sr_instance_id
256 and mis.organization_id = tp2.sr_tp_id
257 and tp2.partner_type = 3
258 and item1.plan_id = mis.plan_id
259 and item1.sr_instance_id = mis.sr_instance_id
260 and item1.organization_id = mis.organization_id
261 and item1.inventory_item_id = mis.inventory_item_id
262 ;
263
264 CURSOR sum_receive_capacity_c(p_horizon_start_date IN DATE,
265 p_horizon_end_date IN DATE,
266 p_abc_class IN VARCHAR2,
267 p_item_id IN NUMBER,
268 p_planner IN VARCHAR2,
269 p_sce_supplier_id IN NUMBER,
270 p_sce_supplier_site_id IN NUMBER,
271 p_bucket_type IN NUMBER) IS
272 SELECT sum(sd.tp_quantity)
273 FROM msc_sup_dem_entries_v sd,
274 msc_trading_partner_maps map1,
275 msc_trading_partners tp,
276 msc_system_items item
277 WHERE sd.plan_id = -1
278 AND sd.publisher_order_type = SUPPLY_COMMIT
279 AND map1.tp_key = tp.partner_id
280 AND map1.map_type = 2 -- company
281 AND map1.company_key = sd.customer_site_id
282 AND sd.customer_id = 1 --OEM
283 AND tp.partner_type = 3
284 AND tp.company_id is null
285 AND item.plan_id = -1
286 AND sd.inventory_item_id = item.inventory_item_id
287 AND item.sr_instance_id = tp.sr_instance_id
288 AND item.organization_id = tp.sr_tp_id
289 --AND sd.quantity > 0
290 AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
291 AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
292 AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
293 AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
294 AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
295 AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
296 and nvl(trunc(p_horizon_end_date),sd.receipt_date)
297 AND sd.bucket_Type = p_bucket_type;
298
299 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
300 IS
301 BEGIN
302 IF ( G_MSC_CP_DEBUG= '1' OR G_MSC_CP_DEBUG = '2') THEN
303 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
304 END IF;
305 EXCEPTION
306 WHEN OTHERS THEN
307 RETURN;
308 END LOG_MESSAGE;
309
310 PROCEDURE receive_capacity(p_errbuf OUT NOCOPY VARCHAR2,
311 p_retcode OUT NOCOPY VARCHAR2,
312 p_horizon_start_date In Varchar2,
313 p_horizon_end_date In Varchar2,
314 p_abc_class In Varchar2,
315 p_item_id In Number,
316 p_planner In Varchar2,
317 p_supplier_id IN number,
318 p_supplier_site_id In Number,
319 p_mps_designator_id IN Number,
320 p_overwrite in Number,
321 p_spread in number) IS
322
323 l_sr_instance_id msc_trading_partners.sr_instance_id%type;
324 l_sce_supplier_id msc_sup_dem_entries.publisher_id%type;
325 l_sce_supplier_site_id msc_sup_dem_entries.publisher_site_id%type;
326 l_sce_supplier_name msc_sup_dem_entries.publisher_name%type;
327 l_sce_supplier_site_name msc_sup_dem_entries.publisher_site_name%type;
328 l_sce_customer_id msc_sup_dem_entries.customer_id%type;
329 l_sce_customer_site_id msc_sup_dem_entries.customer_site_id%type;
330 l_sce_customer_name msc_sup_dem_entries.customer_name%type;
331 l_sce_customer_site_name msc_sup_dem_entries.customer_site_name%type;
332 l_sce_company_id msc_companies.company_id%type;
333 l_map_supplier_id msc_sup_dem_entries.publisher_id%type;
334 l_map_supplier_site_id msc_sup_dem_entries.publisher_site_id%type;
335 l_item_id msc_sup_dem_entries.inventory_item_id%type;
336 l_item_name msc_items.item_name%type;
337 l_uom_code msc_sup_dem_entries.uom_code%type;
338 l_receipt_date msc_sup_dem_entries.receipt_date%type;
339 l_sr_tp_id msc_trading_partners.sr_tp_id%type;
340 l_modeled_supplier_id msc_trading_partners.modeled_supplier_id%type;
341 l_modeled_supplier_site_id msc_trading_partners.modeled_supplier_site_id%type;
342 l_calendar_code msc_trading_partners.calendar_code%type;
343 l_aps_organization_id msc_item_suppliers.organization_id%type;
344 l_aps_supplier_id msc_item_suppliers.supplier_id%type;
345 l_aps_supplier_site_id msc_item_suppliers.supplier_site_id%type;
346 l_rounding_control number;
347 l_bucket_type msc_sup_dem_entries.bucket_type%type;
348 l_org_code msc_trading_partners.organization_code%type;
349 l_plan_id Number := -1; --ods load
350 l_collected_flag Number := 3; --means from destination
351 l_capacity Number;
352 l_err_text Varchar2(2000);
353 l_refresh_number Number;
354 l_total_record_fetch Number := 0;
355 insert_row_count Number := 0;
356 l_exist Number := 0;
357 l_err_msg Varchar2(1000);
358
359 ITEM_NOT_FOUND EXCEPTION;
360 SUPPLIER_NOT_FOUND EXCEPTION;
361 SUPPLIER_SITE_NOT_FOUND EXCEPTION;
362 CAPACITY_IS_NULL EXCEPTION;
363 START_DATE_IS_NULL EXCEPTION;
364 BUCKET_TYPE_IS_NULL EXCEPTION;
365 ITEM_SUPPLIER_NOT_FOUND EXCEPTION;
366
367 v_row_status CONSTANT NUMBER := 10;
368 v_sr_instance_id CONSTANT NUMBER := -1;
369
370 lv_start_date DATE;
371 lv_end_date DATE;
372
373 l_horizon_start date; --canonical date
374 l_horizon_end date; --canonical date
375
376 /*------------------------------------------------------------------
377 Get all the organization for that instance
378 ASCP does not deal with Supplier Capacity definition at the Organization
379 level , as you know we apply the supplier capacity across all Orgs in
380 the instance.
381
382 Cursor get_org_c(p_sr_instance_id IN Number) IS
383 SELECT distinct sr_tp_id, organization_code
384 FROM msc_trading_partners
385 WHERE sr_instance_id = p_sr_instance_id
386 AND partner_type = 3
387 AND company_id is null; -- if type is 3 (org) then use sr_tp_id
388
389
390
391 cursor get_global_asl (p_sr_instance_id In Number,
392 p_aps_supplier_id In Number,
393 p_aps_supplier_site_id In Number,
394 p_item_id In Number) IS
395 SELECT distinct mis.organization_id, tp.organization_code
396 FROM msc_item_suppliers mis, msc_trading_partners tp
397 WHERE plan_id = -1
398 AND mis.sr_instance_id = p_sr_instance_id
399 AND mis.supplier_id = p_aps_supplier_id
400 AND mis.supplier_site_id = p_aps_supplier_site_id
401 AND mis.inventory_item_id = p_item_id
402 AND mis.using_organization_id = -1
403 AND tp.sr_instance_id = mis.sr_instance_id
404 AND tp.sr_tp_id = mis.organization_id;
405
406 ----------------------------------------------------------------------*/
407
408 BEGIN
409
410 LOG_MESSAGE('*****************************************************');
411 fnd_message.set_name('MSC','MSC_PUB_INPUT_DATE_RANGE');
412 LOG_MESSAGE( fnd_message.get || ' ' || p_horizon_start_date || ' ' || p_horizon_end_date);
413 fnd_message.set_name('MSC','MSC_PUB_INPUT_SUPPLIER');
414 LOG_MESSAGE( 'Supplier id: ' || p_supplier_id || ' Supplier site id: ' || p_supplier_site_id);
415 fnd_message.set_name('MSC','MSC_PUB_INPUT_ITEM');
416 LOG_MESSAGE( fnd_message.get || ' '|| p_item_id);
417
418 --LOG_MESSAGE( 'Input calendar code: ' || p_calendar_code);
419
420 /*----------------------------------------------------------
421 get the refresh number
422 ----------------------------------------------------------*/
423 select msc_collection_s.nextval
424 into l_refresh_number
425 from dual;
426
427 /*----------------------------------------------------------
428 get the instance id for the Default Calendar
429 Added this code for bug# 3431898
430 ----------------------------------------------------------*/
431 if (G_MSC_X_DEF_CALENDAR is not null) then
432
433 begin
434 select sr_instance_id
435 into G_CAL_INSTANCE_ID
436 from msc_calendar_dates
437 where calendar_code = G_MSC_X_DEF_CALENDAR
438 and rownum = 1;
439 LOG_MESSAGE( 'Calendar from Profile: ' || G_MSC_X_DEF_CALENDAR);
440 LOG_MESSAGE( 'Instance id : ' || G_CAL_INSTANCE_ID);
441 exception
442 when others then
443 LOG_MESSAGE( 'Error in getting sr_instance_id for Calendar.');
444 G_CAL_INSTANCE_ID := -1;
445 end;
446
447 end if;
448
449 /*-------------------------------------------------------
450 Map the supplier_id in ASCP to the supplier_id in SCE
451 and make sure the supplier has a relationship in SCE
452 as supplier
453 ---------------------------------------------------------*/
454 --LOG_MESSAGE('Start map the supplier_id');
455 BEGIN
456 SELECT distinct c.company_id
457 INTO l_map_supplier_id
458 FROM msc_trading_partner_maps map1,
459 msc_company_relationships rel,
460 msc_companies c,
461 msc_trading_partners tp
462 WHERE rel.relationship_type =2 AND --supplier
463 rel.object_id = c.company_id AND
464 rel.subject_id = 1 AND --other company (OEM)
465 rel.relationship_id = map1.company_key AND
466 map1.tp_key = tp.partner_id AND
467 map1.map_type = 1 AND --company
468 tp.partner_id = p_supplier_id AND
469 tp.partner_type = 1;
470 EXCEPTION
471 WHEN no_data_found then
472 LOG_MESSAGE('No data found when map the supplier_id');
473 l_map_supplier_id := null;
474 when others then
475 LOG_MESSAGE('Error in map supplier_id' || sqlerrm);
476 l_map_supplier_id := null;
477 END;
478
479 LOG_MESSAGE('company_id is : ' || l_map_supplier_id );
480 /*------------------------------------------------------------------------
481 Map the supplier_site_id in ASCP to the supplier_site_id in SCE
482 ----------------------------------------------------------------------*/
483 --LOG_MESSAGE('Start map the supplier_site_id');
484 BEGIN
485 SELECT distinct s.company_site_id
486 INTO l_map_supplier_site_id
487 FROM msc_trading_partner_maps map1,
488 msc_companies c,
489 msc_company_sites s,
490 msc_trading_partners tp,
491 msc_trading_partner_sites tps
492 WHERE map1.map_type = 3 AND
493 map1.tp_key = tps.partner_site_id AND
494 tps.partner_site_id = p_supplier_site_id AND
495 tp.partner_id = tps.partner_id AND
496 tp.partner_type = 1 AND
497 map1.company_key = s.company_site_id AND
498 s.company_id = c.company_id;
499 EXCEPTION
500 WHEN no_data_found then
501 LOG_MESSAGE('No data found when map the supplier_site_id');
502 l_map_supplier_site_id := null;
503 when others then
504 LOG_MESSAGE('Error in map supplier_site_id' || sqlerrm);
505 l_map_supplier_site_id := null;
506 END;
507
508 LOG_MESSAGE('company site id is : ' || l_map_supplier_site_id );
509
510
511 --------------------------------------------------------------------------
512 -- set the standard date as canonical date
513 --------------------------------------------------------------------------
514 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start_date);
515 l_horizon_end := fnd_date.canonical_to_date(p_horizon_end_date);
516
517
518 /*-----------------------------------------------------------
519 Start receive supply commit
520 ------------------------------------------------------------*/
521
522 LOG_MESSAGE('Start the receive capacity cursor');
523 LOG_MESSAGE('p_horizon_end_date : ' || p_horizon_end_date );
524 LOG_MESSAGE('p_abc_class : ' || p_abc_class );
525 LOG_MESSAGE('p_item_id : ' || p_item_id );
526 LOG_MESSAGE('p_planner : ' || p_planner );
527 LOG_MESSAGE('p_supplier_id : '||p_supplier_id);
528 LOG_MESSAGE('p_supplier_site_id : '||p_supplier_site_id);
529 LOG_MESSAGE('l_map_supplier_id : ' || l_map_supplier_id );
530 LOG_MESSAGE('l_map_supplier_site_id : ' || l_map_supplier_site_id );
531 LOG_MESSAGE('********************************************************');
532
533 --bug 4859926
534
535 IF ((p_overwrite = 1) OR (p_horizon_start_date is null and p_horizon_end_date is null))
536 THEN
537 begin
538
539 DELETE msc_supplier_capacities
540 WHERE plan_id = -1
541 AND inventory_item_id = nvl(p_item_id , inventory_item_id)
542 AND supplier_id = nvl(p_supplier_id , supplier_id)
543 AND supplier_site_id = nvl(p_supplier_site_id , supplier_site_id)
544 AND using_organization_id = -1 ;
545
546 LOG_MESSAGE( 'Delete all record ' ||sql%rowcount);
547 exception
548 WHEN no_data_found then
549 LOG_MESSAGE('No record to be deleted in msc_supplier_capacities.');
550 WHEN OTHERS THEN
551 log_message('Error while deleting records for overwrite_all option.');
552 end;
553
554 ELSIF (p_overwrite = 2) THEN
555
556 /*--------------------------------------------------------------------------
557 deleting capacity between the horizon dates and leave the 2 ends
558 ---------------------------------------------------------------------------*/
559 begin
560 DELETE msc_supplier_capacities
561 WHERE plan_id = -1
562 AND inventory_item_id = nvl(p_item_id , inventory_item_id)
563 AND supplier_id = nvl(p_supplier_id , supplier_id)
564 AND supplier_site_id = nvl(p_supplier_site_id , supplier_site_id)
565 AND using_organization_id = -1
566 AND (((from_date between nvl(l_horizon_start,from_date) and nvl(l_horizon_end,from_date))
567 AND (to_date between nvl(l_horizon_start,to_date) and nvl(l_horizon_end,to_date)))
568 OR (collected_flag in (1, 2)));
569 -- bug 5208105
570
571 LOG_MESSAGE('Delete based on horizon_date ' ||sql%rowcount);
572
573 exception
574 WHEN no_data_found then
575 LOG_MESSAGE('No record to be deleted in msc_supplier_capacities for overwrite_all option.');
576 WHEN OTHERS THEN
577 log_message('Error while deleting records for overwrite_all option.'||sqlcode);
578 log_message(sqlerrm);
579 end;
580
581 END IF;
582
583 ------
584
585 open receive_capacity_c(l_horizon_start,
586 l_horizon_end,
587 p_abc_class,
588 p_item_id,
589 p_planner,
590 G_CAL_INSTANCE_ID,
591 l_map_supplier_id,
592 l_map_supplier_site_id);
593
594 LOOP
595 FETCH receive_capacity_c into
596 l_sr_instance_id,
597 l_sce_supplier_id,
598 l_sce_supplier_name,
599 l_sce_supplier_site_id,
600 l_sce_supplier_site_name,
601 l_sce_customer_name,
602 l_sce_customer_id,
603 l_sce_customer_site_name,
604 l_sce_customer_site_id,
605 l_item_id,
606 l_item_name,
607 l_capacity,
608 l_uom_code,
609 l_receipt_date,
610 l_bucket_type,
611 lv_start_date,
612 lv_end_date,
613 l_aps_organization_id,
614 l_org_code,
615 l_calendar_code,
616 l_aps_supplier_id,
617 l_aps_supplier_site_id,
618 l_rounding_control;
619
620 LOG_MESSAGE('=======================================================');
621 LOG_MESSAGE('l_sr_instance_id : ' || l_sr_instance_id );
622 LOG_MESSAGE('l_sce_supplier_id : ' || l_sce_supplier_id );
623 LOG_MESSAGE('l_sce_supplier_name : ' || l_sce_supplier_name );
624 LOG_MESSAGE('l_sce_supplier_site_id : ' || l_sce_supplier_site_id );
625 LOG_MESSAGE('l_sce_supplier_site_name : ' || l_sce_supplier_site_name );
626 LOG_MESSAGE('l_sce_customer_name : ' || l_sce_customer_name );
627 LOG_MESSAGE('l_sce_customer_id : ' || l_sce_customer_site_name );
628 LOG_MESSAGE('l_sce_customer_site_id : ' || l_sce_customer_site_id );
629 LOG_MESSAGE('l_item_id : ' || l_item_id );
630 LOG_MESSAGE('l_item_name : ' || l_item_name );
631 LOG_MESSAGE('l_capacity : ' || l_capacity );
632 LOG_MESSAGE('l_bucket_type : ' || l_bucket_type );
633 LOG_MESSAGE('l_receipt_date : ' || l_receipt_date );
634 LOG_MESSAGE('lv_start_date : ' || lv_start_date );
635 LOG_MESSAGE('lv_end_date : ' || lv_end_date );
636 LOG_MESSAGE('l_calendar_code : ' || l_calendar_code );
637 LOG_MESSAGE('l_org_code : ' || l_org_code );
638 LOG_MESSAGE('l_aps_supplier_id : ' || l_aps_supplier_id );
639 LOG_MESSAGE('l_aps_supplier_site_id : ' || l_aps_supplier_site_id );
640 LOG_MESSAGE('l_aps_organization_id : '||l_aps_organization_id);
641 LOG_MESSAGE('=======================================================');
642
643 exit when receive_capacity_c%NOTFOUND;
644
645 begin
646 /***********************************************************************************
647
648 IF (p_supplier_id is null ) THEN
649 begin
650 SELECT distinct tp.partner_id
651 INTO l_aps_supplier_id
652 FROM msc_trading_partner_maps map1,
653 msc_company_relationships rel,
654 msc_item_suppliers sup,
655 msc_trading_partners tp
656 WHERE rel.relationship_type =2 AND --supplier
657 rel.object_id = l_sce_supplier_id AND
658 rel.subject_id = 1 AND --other company (OEM)
659 rel.relationship_id = map1.company_key AND
660 map1.tp_key = tp.partner_id AND
661 map1.map_type = 1 AND --company
662 sup.plan_id = -1 AND
663 sup.inventory_item_id = l_item_id AND
664 tp.partner_id = sup.supplier_id AND
665 tp.partner_type = 1;
666 exception
667 when no_data_found then
668 --LOG_MESSAGE('supplier not found in sce map ');
669 --raise SUPPLIER_NOT_FOUND;
670 null;
671 end;
672 ELSE
673 l_aps_supplier_id := p_supplier_id;
674 END IF;
675
676 LOG_MESSAGE('APS supplier ' || l_aps_supplier_id);
677 LOG_MESSAGE('Sce supplier site id ' || l_sce_supplier_site_id);
678
679
680 IF (p_supplier_site_id is null) THEN
681 begin
682 SELECT distinct tps.partner_site_id
683 INTO l_aps_supplier_site_id
684 FROM msc_trading_partner_maps map1,
685 msc_companies c,
686 msc_company_sites s,
687 msc_trading_partners tp,
688 msc_trading_partner_sites tps
689 WHERE map1.map_type = 3 AND
690 map1.tp_key = tps.partner_site_id AND
691 tp.partner_id = tps.partner_id AND
692 map1.company_key = s.company_site_id AND
693 s.company_site_id = l_sce_supplier_site_id AND
694 s.company_id = c.company_id AND
695 c.company_id = l_sce_supplier_id AND
696 nvl(tp.company_id,1) = 1;
697 exception
698 when no_data_found then
699 --LOG_MESSAGE('supplier site not found in sce map ');
700 --raise SUPPLIER_SITE_NOT_FOUND;
701 null;
702 end;
703 ELSE
704 l_aps_supplier_site_id := p_supplier_site_id;
705 END IF;
706
707 LOG_MESSAGE('APS supplier site ' || l_aps_supplier_site_id);
708 ***********************************************************************************/
709 -- Added for bug # 4560149
710
711 IF (p_supplier_id is null ) THEN
712 BEGIN
713 SELECT distinct c.company_id
714 INTO l_map_supplier_id
715 FROM msc_trading_partner_maps map1,
716 msc_company_relationships rel,
717 msc_companies c,
718 msc_trading_partners tp
719 WHERE rel.relationship_type =2 AND --supplier
720 rel.object_id = c.company_id AND
721 rel.subject_id = 1 AND --other company (OEM)
722 rel.relationship_id = map1.company_key AND
723 map1.tp_key = tp.partner_id AND
724 map1.map_type = 1 AND --company
725 tp.partner_id = l_aps_supplier_id AND
726 tp.partner_type = 1;
727
728 log_message('Here l_map_supplier_id : '||l_map_supplier_id) ;
729 exception
730 when no_data_found then
731 LOG_MESSAGE('supplier not found in sce map ');
732 when others then
733 LOG_MESSAGE('Error : '||sqlerrm);
734
735 end;
736 END IF;
737
738 IF (p_supplier_site_id is null ) THEN
739 Begin
740 SELECT distinct s.company_site_id
741 INTO l_map_supplier_site_id
742 FROM msc_trading_partner_maps map1,
743 msc_companies c,
744 msc_company_sites s,
745 msc_trading_partners tp,
746 msc_trading_partner_sites tps
747 WHERE map1.map_type = 3 AND
748 map1.tp_key = tps.partner_site_id AND
749 tps.partner_site_id = l_aps_supplier_site_id AND
750 tp.partner_id = tps.partner_id AND
751 tp.partner_type = 1 AND
752 map1.company_key = s.company_site_id AND
753 s.company_id = c.company_id;
754
755 log_message('Here l_map_supplier_site_id : '||l_map_supplier_site_id) ;
756 exception
757 when no_data_found then
758 LOG_MESSAGE('supplier site not found in sce map ');
759 when others then
760 LOG_MESSAGE('Error : '||sqlerrm);
761
762 end;
763 END IF;
764
765 IF l_capacity is null then
766 raise CAPACITY_IS_NULL;
767 END IF;
768
769 /*IF l_receipt_date is null then
770 raise START_DATE_IS_NULL;
771 END IF; */
772
773 IF l_bucket_type is null then
774 raise BUCKET_TYPE_IS_NULL;
775 END IF;
776
777 /*----------------------------------------------------------------------
778 Start populate the capacity
779 ------------------------------------------------------------------------*/
780
781 /***********************************************************************************
782 OPEN get_global_asl (l_sr_instance_id,
783 l_aps_supplier_id,
784 l_aps_supplier_site_id,
785 l_item_id);
786 LOOP
787 FETCH get_global_asl into l_aps_organization_id, l_org_code;
788 exit when get_global_asl%NOTFOUND;
789
790 LOG_MESSAGE('************************************************************');
791 LOG_MESSAGE('Process record: ' || 'Item Name: ' ||
792 l_item_name || ' Org: ' || l_org_code || ' Supplier: ' ||
793 l_sce_supplier_name ||' Date: ' || l_receipt_date ||
794 ' Bucket ' || l_bucket_type || ' Capacity: ' || l_capacity );
795
796 ***********************************************************************************/
797
798 IF (l_bucket_type = G_DAY) THEN
799 l_capacity := l_capacity;
800
801 log_message('Bkt Day, l_capacity : '||l_capacity);
802 ELSE
803
804 open sum_receive_capacity_c(lv_start_date,
805 lv_end_date,
806 p_abc_class,
807 l_item_id,
808 p_planner,
809 l_map_supplier_id,
810 l_map_supplier_site_id,
811 l_bucket_type);
812
813 fetch sum_receive_capacity_c into l_capacity;
814 close sum_receive_capacity_c;
815
816 log_message('Inside Cursor sum_receive_capacity_c; l_capacity : '||l_capacity);
817
818 END IF;
819
820 Calculate_Capacity(l_sr_instance_id,
821 l_aps_organization_id,
822 l_aps_supplier_id,
823 l_aps_supplier_site_id,
824 p_mps_designator_id,
825 l_item_id,
826 l_receipt_date,
827 l_capacity,
828 l_bucket_type,
829 l_calendar_code,
830 l_refresh_number,
831 lv_start_date,
832 lv_end_date,
833 l_horizon_start,
834 l_horizon_end,
835 p_overwrite,
836 p_abc_class,
837 p_planner,
838 l_map_supplier_id,
839 l_map_supplier_site_id,
840 l_rounding_control,
841 p_spread);
842
843 /***********************************************************************************
844 END LOOP;
845
846 CLOSE get_global_asl;
847
848 ***********************************************************************************/
849 EXCEPTION
850 WHEN ITEM_NOT_FOUND THEN
851
852 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
853 l_err_msg := fnd_message.get;
854
855 fnd_message.set_name('MSC','MSC_PUB_ITEM_NOT_FOUND');
856 FND_MESSAGE.SET_TOKEN('ITEM', l_item_name);
857 LOG_MESSAGE('Item not found');
858 l_err_text := l_err_msg || ' ' || 'Item Name ' || l_item_name || ' ' ||
859 ' Org: ' || l_org_code || ' ' ||
860 ' Supplier: ' || l_sce_supplier_name || ' ' ||
861 ' Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
862 ' Capacity: ' || l_capacity || ' ' ||
863 ' Start Date: ' || l_receipt_date || '->' || fnd_message.get;
864 LOG_MESSAGE( l_err_text);
865
866 WHEN SUPPLIER_NOT_FOUND THEN
867 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
868 l_err_msg := fnd_message.get;
869
870 fnd_message.set_name('MSC','MSC_PUB_SUPPLIER_NOT_FOUND');
871 FND_MESSAGE.SET_TOKEN('SUPPLIER', l_sce_supplier_name);
872 LOG_MESSAGE('supplier not found');
873 l_err_text := l_err_msg || ' ' || 'Item Name: ' || l_item_name || ' ' ||
874 'Org: ' || l_org_code || ' ' ||
875 'Supplier: ' || l_sce_supplier_name || ' ' ||
876 'Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
877 'Capacity: ' || l_capacity || ' ' ||
878 'Start Date: ' || l_receipt_date || '->' || fnd_message.get;
879 LOG_MESSAGE( l_err_text);
880
881 WHEN SUPPLIER_SITE_NOT_FOUND THEN
882 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
883 l_err_msg := fnd_message.get;
884
885 fnd_message.set_name('MSC','MSC_PUB_SUP_SITE_NOT_FOUND');
886 FND_MESSAGE.SET_TOKEN('SUPPLIER_SITE', l_sce_supplier_site_name);
887 LOG_MESSAGE('supplier site not found');
888 l_err_text := l_err_msg || ' ' || 'Item Name ' || l_item_name || ' ' ||
889 'Org: ' || l_org_code || ' ' ||
890 'Supplier: ' || l_sce_supplier_name || ' ' ||
891 'Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
892 'Capacity: ' || l_capacity || ' ' ||
893 'Start Date: ' || l_receipt_date || '->' || fnd_message.get;
894 LOG_MESSAGE( l_err_text);
895
896 WHEN CAPACITY_IS_NULL THEN
897 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
898 l_err_msg := fnd_message.get;
899
900 fnd_message.set_name('MSC','MSC_PUB_CAPACITY_IS_NULL');
901 LOG_MESSAGE('Null capacity');
902 l_err_text := l_err_msg || ' ' || 'Item Name ' || l_item_name || ' ' ||
903 'Org: ' || l_org_code || ' ' ||
904 'Supplier: ' || l_sce_supplier_name || ' ' ||
905 'Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
906 'Capacity: ' || l_capacity || ' ' ||
907 'Start Date: ' || l_receipt_date || '->' || fnd_message.get;
908 LOG_MESSAGE( l_err_text);
909
910 WHEN START_DATE_IS_NULL THEN
911 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
912 l_err_msg := fnd_message.get;
913
914 fnd_message.set_name('MSC','MSC_PUB_START_DATE_IS_NULL');
915 LOG_MESSAGE('start date is null');
916 l_err_text := l_err_msg || ' ' || 'Item Name ' || l_item_name || ' ' ||
917 'Org: ' || l_org_code || ' ' ||
918 'Supplier: ' || l_sce_supplier_name || ' ' ||
919 'Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
920 'Capacity: ' || l_capacity || '-' ||
921 'Start Date: ' || l_receipt_date || '->' || fnd_message.get;
922 LOG_MESSAGE( l_err_text);
923
924 WHEN BUCKET_TYPE_IS_NULL THEN
925
926 fnd_message.set_name('MSC','MSC_PUB_RECORD_FAIL');
927 l_err_msg := fnd_message.get;
928
929 fnd_message.set_name('MSC','MSC_PUB_BUCKET_TYPE_IS_NULL');
930 LOG_MESSAGE('bucket type is null');
931 l_err_text := l_err_msg || ' ' || 'Item Name ' || l_item_name || ' ' ||
932 'Org: ' || l_org_code || ' ' ||
933 'Supplier: ' || l_sce_supplier_name || ' ' ||
934 'Supplier Site: ' || l_sce_supplier_site_name || ' ' ||
935 'Capacity: ' || l_capacity || ' ' ||
936 'Start Date: ' || l_receipt_date || '->' || fnd_message.get;
937 LOG_MESSAGE( l_err_text);
938
939 END;
940
941 l_total_record_fetch := l_total_record_fetch + 1;
942
943 END LOOP;
944
945 CLOSE receive_capacity_c;
946
947 fnd_message.set_name('MSC','MSC_PUB_TOTAL_RECORD_FETCHED');
948 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get || ' ' || l_total_record_fetch);
949 /*---------------------------------------------------------------------------
950 | Now update back the current data that are receiving from exchange
951 -------------------------------------------------------------------------*/
952 begin
953 update msc_supplier_capacities
954 set last_update_login = null
955 where plan_id = -1
956 and last_update_login = -999;
957
958 update msc_supplies
959 set last_update_login = null
960 where plan_id = -1
961 and order_type = 5
962 and last_update_login = -999;
963
964 exception
965 when others then
966 LOG_MESSAGE( sqlerrm);
967 end;
968
969 -- launch ASCP engine with default constrained plan
970 IF ( FND_PROFILE.VALUE('MSC_DEFAULT_CONST_PLAN') IS NOT NULL
971 ) THEN
972 BEGIN
973 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Launching ASCP engine with default constrained plan');
974 MSC_X_CP_FLOW.Start_ASCP_Engine_WF
975 ( p_constrained_plan_flag => 1 -- launch with constrained plan
976 );
977 EXCEPTION
978 WHEN OTHERS THEN
979 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_CP_FLOW.Start_ASCP_Engine_WF');
980 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
981 END;
982 ELSE
983 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Please set up default constrained plan');
984 END IF;
985
986 EXCEPTION
987 when others then
988 p_errbuf := sqlerrm;
989 p_retcode := '2'; --other error
990 LOG_MESSAGE( sqlerrm);
991 LOG_MESSAGE('Error in main program ' || sqlerrm);
992 return;
993 END receive_capacity;
994
995
996 ------------------------------------------------------------------------
997 --CALCULATE_CAPACITY
998 ------------------------------------------------------------------------
999 PROCEDURE Calculate_Capacity(p_sr_instance_id IN Number,
1000 p_organization_id IN Number,
1001 p_supplier_id IN Number,
1002 p_supplier_site_id IN Number,
1003 p_mps_designator_id IN Number,
1004 p_item_id IN Number,
1005 p_receipt_date IN date,
1006 p_capacity IN Number,
1007 p_bucket_type IN Number,
1008 p_calendar_code IN varchar2,
1009 p_refresh_number IN NUmber,
1010 p_lv_start_date IN Date,
1011 p_lv_end_date IN Date,
1012 p_horizon_start_date In Date,
1013 p_horizon_end_date In Date,
1014 p_overwrite In Number,
1015 p_abc_class in varchar2,
1016 p_planner IN varchar2,
1017 p_map_supplier_id IN number,
1018 p_map_supplier_site_id IN number,
1019 p_rounding_control IN number,
1020 p_spread IN Number) IS
1021
1022 l_quantity Number;
1023 l_from_date Date;
1024 l_to_date Date;
1025 l_next_work_date Date;
1026 l_end_date Date;
1027 l_num_day Number;
1028 i Number;
1029 l_mod_org_id Number := -1;
1030 l_mod_org_code msc_trading_partners.organization_code%type;
1031 l_cal_sr_instance_id Number;
1032 l_cal_org_id Number;
1033 l_calendar_code msc_trading_partners.calendar_code%type;
1034
1035 lv_avg number;
1036 lv_diff number;
1037 lv_new_x number;
1038
1039 l_week number;
1040 l_month varchar2(100);
1041 l_year varchar2(100);
1042 lv_sr_instance_id number;
1043
1044
1045 BEGIN
1046
1047 if ( p_calendar_code = G_MSC_X_DEF_CALENDAR) then
1048 /* if the calendar is from profile, use the calendar's sr_instance_id
1049 for calendar routines */
1050 lv_sr_instance_id := G_CAL_INSTANCE_ID;
1051 else
1052 lv_sr_instance_id := p_sr_instance_id;
1053 end if;
1054 /*--------------------------------------------------------------------
1055 check if the supplier is modeled as org
1056 --------------------------------------------------------------------*/
1057 BEGIN
1058 SELECT distinct tp.sr_tp_id, tp.organization_code
1059 INTO l_mod_org_id, l_mod_org_code
1060 FROM msc_trading_partners tp
1061 WHERE tp.sr_instance_id = p_sr_instance_id AND
1062 tp.modeled_supplier_id = p_supplier_id AND
1063 tp.modeled_supplier_site_id = p_supplier_site_id AND
1064 tp.partner_type = 3 AND
1065 tp.company_id is null;
1066 EXCEPTION
1067 WHEN no_data_found then
1068 l_mod_org_id := -1;
1069
1070 END;
1071 --LOG_MESSAGE('Is supplier modeled as ORG:' || l_mod_org_id);
1072
1073 LOG_MESSAGE('p_spread :' || p_spread);
1074 IF (p_spread = 1) THEN
1075
1076 l_end_date := last_day(p_receipt_date);
1077
1078 ------------------------------------------------------------------------------
1079 -- Bug# 2678523
1080 -- Need to calculate the capacity with calendar code
1081 -- Reason: Example weekly bucket. If we bring in 7-0 calendar code capacity,
1082 -- when running a plan based on a 5-2 calendar code, then will be missing 2 days
1083 -- capacities in PWB.
1084 -- Solution: A new parameter for calendar code is introduced. This LOV is a list
1085 -- of the calendar_code from msc_calendar_dates. The default calendar code for
1086 -- this LOV is based on a production plan (from msc_designator where production = 1)
1087 -- If user not to choose any calendar code, or no calendar code is found, then
1088 -- we use the default 7-0 calendar.
1089 ---------------------------------------------------------------------------------
1090
1091 IF p_bucket_type = G_MONTH THEN --monthly bucket
1092 l_num_day := MSC_CALENDAR.calendar_days_between
1093 (lv_sr_instance_id,
1094 p_calendar_code,
1095 1, --using '1' because need to find #days
1096 greatest(p_lv_start_date,sysdate),
1097 p_lv_end_date);
1098
1099 ELSIF p_bucket_type = G_WEEK then --weekly
1100 l_num_day := MSC_CALENDAR.calendar_days_between
1101 (lv_sr_instance_id,
1102 p_calendar_code,
1103 1, --using '1' because need to find #days
1104 greatest(p_lv_start_date,sysdate),
1105 p_lv_end_date);
1106 ELSIF p_bucket_type = G_DAY then --day
1107 l_num_day := 1;
1108 END IF;
1109
1110 LOG_MESSAGE('Total capacity : ' || p_capacity ||' to be spread on : '|| l_num_day || ' days.');
1111
1112 l_from_date := greatest(p_lv_start_date,sysdate);
1113
1114 lv_avg := p_capacity/l_num_day;
1115 lv_diff := 0;
1116
1117 FOR i IN 1..l_num_day LOOP
1118
1119 if (p_rounding_control = SYS_YES) then
1120 /* if the Item Attribute Rounding flag is yes,
1121 the qty should be whole number
1122 */
1123
1124 l_next_work_date := MSC_CALENDAR.calendar_next_work_day(lv_sr_instance_id,
1125 p_calendar_code,
1126 1,
1127 l_from_date);
1128
1129 l_from_date := l_next_work_date;
1130
1131 lv_new_x := lv_avg + lv_diff;
1132
1133 l_quantity := round(lv_new_x);
1134
1135 lv_diff := lv_new_x - l_quantity;
1136 else
1137
1138 IF (instr( p_capacity/l_num_day, '.') = 0 ) THEN
1139 l_quantity := substr( p_capacity/l_num_day, 1, length(p_capacity/l_num_day));
1140 ELSE
1141 l_quantity := substr( p_capacity/l_num_day, 1, instr(p_capacity/l_num_day, '.') + 2);
1142 END IF;
1143
1144 end if;
1145
1146 -- LOG_MESSAGE(' l_from_date : = '|| l_from_date);
1147 -- LOG_MESSAGE(' l_next_work_date : = '|| l_next_work_date);
1148
1149 populate_Capacity(p_sr_instance_id,
1150 p_organization_id,
1151 p_supplier_id,
1152 p_supplier_site_id,
1153 p_item_id,
1154 l_from_date,
1155 l_quantity,
1156 p_refresh_number,
1157 p_lv_start_date,
1158 p_lv_end_date,
1159 p_horizon_start_date,
1160 p_horizon_end_date,
1161 p_overwrite
1162 );
1163
1164
1165 IF (l_mod_org_id <> -1 and l_mod_org_id = p_organization_id) THEN
1166 LOG_MESSAGE('Here in load supply schedule');
1167 Load_Supply_Schedule(p_sr_instance_id,
1168 p_organization_id,
1169 p_supplier_id,
1170 p_supplier_site_id,
1171 l_mod_org_id,
1172 l_mod_org_code,
1173 p_mps_designator_id,
1174 p_item_id,
1175 l_from_date,
1176 l_quantity,
1177 p_refresh_number
1178 );
1179 END IF;
1180
1181 l_from_date := l_from_date + 1;
1182 END LOOP;
1183
1184 /*-----------------------------------------------------------------------------
1185 will not spread the supplier capacity into daily capacity.
1186 bring in the exact data from Collaborative planning
1187 Note: For monthly bucket and if the receipt date falls in the same month/year
1188 of the sysdate, use sysdate
1189 For weekly and if the receipt date falls in the same week/month/year
1190 of the sysdate, use sysdate
1191 For daily, as the receipt date
1192 ----------------------------------------------------------------------------*/
1193
1194 ELSIF (p_spread = 2) THEN
1195
1196 l_from_date := p_receipt_date;
1197 l_quantity := p_capacity;
1198
1199 select to_char(sysdate, 'MON') into l_month from dual;
1200 select to_char(sysdate, 'YYYY') into l_year from dual;
1201 select to_char(sysdate, 'W') into l_week from dual;
1202
1203
1204 --log_message('p_receipt_date : ' ||p_receipt_date);
1205 IF p_bucket_type = 3 THEN
1206 --select last_day(l_from_date) into l_to_date from dual;
1207
1208 IF (to_char(p_receipt_date, 'MON') = l_month and
1209 to_char(p_receipt_date, 'YYYY') = l_year ) THEN
1210 l_from_date := sysdate + 1;
1211 END IF;
1212
1213 ELSIF p_bucket_type = 2 THEN
1214
1215 --select p_receipt_date + 6 into l_to_date from dual;
1216
1217 IF (to_char(p_receipt_date, 'MON') = l_month and
1218 to_char(p_receipt_date, 'YYYY') = l_year and
1219 to_char(p_receipt_date, 'W') = l_week) THEN
1220 l_from_date := sysdate + 1;
1221 END IF;
1222
1223 END IF;
1224
1225 --log_message('l_from_date : ' ||l_from_date);
1226 l_next_work_date := MSC_CALENDAR.calendar_next_work_day
1227 (lv_sr_instance_id,
1228 p_calendar_code,
1229 1,
1230 l_from_date);
1231
1232 -- log_message('l_next_work_date : ' ||l_next_work_date);
1233 l_from_date := l_next_work_date;
1234
1235
1236 populate_Capacity(p_sr_instance_id,
1237 p_organization_id,
1238 p_supplier_id,
1239 p_supplier_site_id,
1240 p_item_id,
1241 l_from_date,
1242 l_quantity,
1243 p_refresh_number,
1244 p_lv_start_date,
1245 p_lv_end_date,
1246 p_horizon_start_date,
1247 p_horizon_end_date,
1248 p_overwrite
1249 );
1250
1251 IF (l_mod_org_id <> -1 and l_mod_org_id = p_organization_id) THEN
1252 --dbms_output.put_line('Here in load supply schedule');
1253 Load_Supply_Schedule(p_sr_instance_id,
1254 p_organization_id,
1255 p_supplier_id,
1256 p_supplier_site_id,
1257 l_mod_org_id,
1258 l_mod_org_code,
1259 p_mps_designator_id,
1260 p_item_id,
1261 l_from_date,
1262 l_quantity,
1263 p_refresh_number
1264 );
1265 END IF;
1266 END IF;
1267 EXCEPTION
1268
1269 WHEN others THEN
1270 LOG_MESSAGE('Error in calcuate capacity ' || sqlerrm);
1271 LOG_MESSAGE( 'Calculate_capacity ' || sqlerrm);
1272 END CALCULATE_CAPACITY;
1273
1274
1275 ------------------------------------------------------------------------------------
1276 --POPULATE_CAPACITY
1277 -----------------------------------------------------------------------------------
1278 PROCEDURE Populate_Capacity(p_sr_instance_id IN Number,
1279 p_organization_id IN Number,
1280 p_supplier_id IN Number,
1281 p_supplier_site_id IN Number,
1282 p_item_id IN Number,
1283 p_date IN Date,
1284 p_capacity IN Number,
1285 p_refresh_number In Number,
1286 p_lv_start_date IN Date,
1287 p_lv_end_date IN Date,
1288 p_horizon_start_date in Date,
1289 p_horizon_end_date in Date,
1290 p_overwrite In Number) IS
1291
1292 /*-----------------------------------------------------------------------------
1293 if the overwrite is only horizon specific, then need to maintain
1294 the capacity not in the horizon range
1295 ----------------------------------------------------------------------------*/
1296 cursor get_existing_capacity (p_sr_instance_id In Number,
1297 p_organization_id In Number,
1298 p_supplier_id In Number,
1299 p_supplier_site_id In Number,
1300 p_item_id In Number,
1301 p_date In Date,
1302 p_horizon_start_date In Date,
1303 p_horizon_end_date in Date) IS
1304 SELECT transaction_id,from_date, to_date, capacity
1305 FROM msc_supplier_capacities
1306 WHERE plan_id = -1
1307 AND sr_instance_id = p_sr_instance_id
1308 AND organization_id = p_organization_id
1309 AND inventory_item_id = p_item_id
1310 AND supplier_id = p_supplier_id
1311 AND supplier_site_id = p_supplier_site_id
1312 AND using_organization_id = -1
1313 AND nvl(last_update_login,-1) <> -999
1314 AND from_date <= nvl(p_horizon_end_date, from_date)
1315 AND nvl(to_date,p_horizon_start_date) >= nvl(p_horizon_start_date,to_date)
1316 UNION
1317 /*-----------------------------------------------------------------------
1318 this statement will take care where p_horizon_start_date is null
1319 and to_date is null
1320 -----------------------------------------------------------------------*/
1321 SELECT transaction_id,from_date, to_date, capacity
1322 FROM msc_supplier_capacities
1323 WHERE plan_id = -1
1324 AND sr_instance_id = p_sr_instance_id
1325 AND organization_id = p_organization_id
1326 AND inventory_item_id = p_item_id
1327 AND supplier_id = p_supplier_id
1328 AND supplier_site_id = p_supplier_site_id
1329 AND using_organization_id = -1
1330 AND nvl(last_update_login,-1) <> -999
1331 AND from_date <= nvl(p_horizon_start_date, from_date)
1332 AND nvl(to_date,p_horizon_end_date) >= nvl(p_horizon_end_date,to_date)
1333 ORDER BY transaction_id;
1334
1335
1336 l_from_date date;
1337 l_to_date date;
1338 l_original_capacity Number;
1339 l_exist Number := 0;
1340 l_trx_id Number;
1341
1342 BEGIN
1343
1344
1345 IF (p_overwrite = 2) THEN
1346
1347 /*-------------------------------------------------------------------------
1348 Getting the 2 ends of the horizon date range then
1349 updating the 2 ends capacities
1350
1351 ---------------------------------------------------------------------------*/
1352
1353 open get_existing_capacity (p_sr_instance_id,
1354 p_organization_id,
1355 p_supplier_id,
1356 p_supplier_site_id,
1357 p_item_id,
1358 p_date,
1359 p_horizon_start_date,
1360 p_horizon_end_date);
1361 loop
1362 fetch get_existing_capacity into l_trx_id, l_from_date, l_to_date, l_original_capacity;
1363 exit when get_existing_capacity%NOTFOUND;
1364
1365 --dbms_output.put_line('From dt ' || l_from_date || ' to dt ' || l_to_date);
1366
1367 /*----------------------------------------------------------
1368 -- there is end date for the existing capacity in ASCP
1369 ------------------------------------------------------------*/
1370
1371 IF ( l_to_date is not null ) THEN
1372 IF (p_horizon_start_date is not null) THEN
1373 IF (l_from_date < p_horizon_start_date ) then
1374 /*-------------------------------------------------------------------------
1375 IF l_from_date < p_horizon_start_date -- update the from_date,
1376 then insert later when l_to_date > p_horizon_end_date
1377 Also works for p_horizon_end_date is null -- just update the to_date
1378 -------------------------------------------------------------------------*/
1379
1380 UPDATE msc_supplier_capacities
1381 set to_date = p_horizon_start_date -1
1382 WHERE plan_id = -1
1383 and transaction_id = l_trx_id;
1384
1385 IF (l_to_date > p_horizon_end_date and p_horizon_end_date is not null) THEN
1386 --dbms_output.put_line('INSERT ' || l_to_date || ' cap ' || l_original_capacity);
1387 insert_capacity(p_sr_instance_id,
1388 p_organization_id,
1389 p_supplier_id,
1390 p_supplier_site_id,
1391 p_item_id,
1392 p_horizon_end_date + 1,
1393 l_to_date,
1394 l_original_capacity,
1395 p_refresh_number);
1396
1397 END IF;
1398
1399 ELSIF (l_from_date >= p_horizon_start_date and
1400 l_to_date > p_horizon_end_date and
1401 p_horizon_end_date is not null) THEN
1402 UPDATE msc_supplier_capacities
1403 set from_date = p_horizon_end_date + 1
1404 WHERE plan_id = -1
1405 and transaction_id = l_trx_id;
1406 END IF;
1407 END IF;
1408 IF (p_horizon_start_date is null and
1409 l_to_date > p_horizon_end_date ) THEN
1410 UPDATE msc_supplier_capacities
1411 set from_date = p_horizon_end_date + 1
1412 WHERE plan_id = -1
1413 and transaction_id = l_trx_id;
1414 END IF;
1415 END IF;
1416
1417 /*------------------------------------------------------
1418 This will take care if there is no end date for the
1419 existing capacity
1420 -------------------------------------------------------*/
1421 IF (l_to_date is null ) THEN
1422
1423 -- dbms_output.put_line('HN from dt ' || l_from_date || ' to dt ' || l_to_date || ' Trx ' || l_trx_id);
1424 IF (p_horizon_end_date is not null) THEN
1425 /*-------------------------------------------------------------------------
1426 IF l_from_date >= p_horizon_start_date -- just update from_date
1427 IF l_from_date < p_horizon_start_date -- update the from_date, then insert later
1428 IF l_from_date = p_horizon_end_date -- just update the from_date
1429 Also works for p_horizon_start_date is null -- just update the from_date
1430 -------------------------------------------------------------------------*/
1431 --dbms_output.put_line('HZ ' || p_horizon_start_date || p_horizon_end_date);
1432 UPDATE msc_supplier_capacities
1433 set from_date = p_horizon_end_date + 1,
1434 to_date = null
1435 WHERE plan_id = -1
1436 and transaction_id = l_trx_id;
1437
1438
1439 IF (l_from_date < p_horizon_start_date and p_horizon_start_date is not null ) THEN
1440 insert_capacity (p_sr_instance_id,
1441 p_organization_id,
1442 p_supplier_id,
1443 p_supplier_site_id,
1444 p_item_id,
1445 l_from_date,
1446 p_horizon_start_date - 1,
1447 l_original_capacity,
1448 p_refresh_number);
1449 END IF;
1450
1451 ELSIF (p_horizon_end_date is null ) THEN
1452 IF (l_from_date >= p_horizon_start_date) THEN
1453 /*---------------------------------------------------------
1454 IF p_horizon_end_date is null and l_to_date is null and
1455 l_from_date is within the p_horizon_start_date, the trx
1456 should be overwrite
1457 --------------------------------------------------------*/
1458 delete msc_supplier_capacities
1459 where transaction_id = l_trx_id;
1460 ELSIF (l_from_date < p_horizon_start_date ) THEN
1461 UPDATE msc_supplier_capacities
1462 set to_date = p_horizon_start_date -1
1463 where plan_id = -1
1464 and transaction_id = l_trx_id;
1465 END IF;
1466
1467 END IF;
1468 END IF;
1469 end loop;
1470 close get_existing_capacity;
1471 END IF;
1472
1473 insert_capacity(p_sr_instance_id,
1474 p_organization_id,
1475 p_supplier_id,
1476 p_supplier_site_id,
1477 p_item_id,
1478 p_date,
1479 p_date,
1480 p_capacity,
1481 p_refresh_number);
1482
1483
1484 exception
1485 when others then
1486 --dbms_output.put_line('error in populate_capacity ' || sqlerrm);
1487 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in populate_capacity' || sqlerrm);
1488 END POPULATE_CAPACITY;
1489
1490 ------------------------------------------------------------------------------------
1491 --INSERT_CAPACITY
1492 ------------------------------------------------------------------------------------
1493 PROCEDURE Insert_Capacity(p_sr_instance_id IN Number,
1494 p_organization_id IN Number,
1495 p_supplier_id IN Number,
1496 p_supplier_site_id IN Number,
1497 p_item_id IN Number,
1498 p_from_date IN Date,
1499 p_to_date IN Date,
1500 p_capacity IN Number,
1501 p_refresh_number In Number) IS
1502
1503 l_nextid Number;
1504 l_user_id Number := fnd_global.user_id;
1505 l_refresh_number Number;
1506 l_exist number := 0;
1507 BEGIN
1508
1509
1510 --===========================================================================
1511 -- If the key date is not the working and is forward/backward to the
1512 -- working date, need to accumulate the capacity for the same date for the
1513 -- same run
1514 --===========================================================================
1515 begin
1516 select count(*)
1517 into l_exist
1518 FROM msc_supplier_capacities
1519 WHERE plan_id = -1
1520 AND sr_instance_id = p_sr_instance_id
1521 AND organization_id = p_organization_id
1522 AND inventory_item_id = p_item_id
1523 AND supplier_id = p_supplier_id
1524 AND supplier_site_id = p_supplier_site_id
1525 AND from_date = p_from_date
1526 AND to_date = p_to_date
1527 AND using_organization_id = -1
1528 AND nvl(last_update_login,-1) = -999;
1529 exception
1530 when no_data_found then
1531 l_exist := 0;
1532 when others then
1533 l_exist := 0;
1534 end;
1535
1536 IF (l_exist > 0) THEN
1537 --LOG_MESSAGE( 'update qty ' || p_capacity);
1538 UPDATE msc_supplier_capacities
1539 set capacity = capacity + p_capacity
1540 WHERE plan_id = -1
1541 AND sr_instance_id = p_sr_instance_id
1542 AND organization_id = p_organization_id
1543 AND inventory_item_id = p_item_id
1544 AND supplier_id = p_supplier_id
1545 AND supplier_site_id = p_supplier_site_id
1546 AND from_date = p_from_date
1547 AND to_date = p_to_date
1548 AND using_organization_id = -1
1549 AND nvl(last_update_login,-1) = -999;
1550
1551 ELSIF (l_exist = 0) THEN
1552
1553
1554 LOG_MESSAGE('insert capacity');
1555 select msc_supplier_capacities_s.nextval
1556 into l_nextid
1557 from dual;
1558 BEGIN
1559 insert into msc_supplier_capacities (
1560 transaction_id,
1561 plan_id,
1562 organization_id,
1563 sr_instance_id,
1564 supplier_id,
1565 supplier_site_id,
1566 inventory_item_id,
1567 from_date,
1568 to_date,
1569 capacity,
1570 using_organization_id,
1571 refresh_number,
1572 last_update_date,
1573 last_updated_by,
1574 creation_date,
1575 created_by,
1576 status,
1577 applied,
1578 collected_flag,
1579 last_update_login)
1580 values (
1581 l_nextid,
1582 -1, --plan_id
1583 p_organization_id, --organization_id,
1584 p_sr_instance_id,
1585 p_supplier_id,
1586 p_supplier_site_id, --p_supplier_site_id,
1587 p_item_id,
1588 p_from_date, --from_date,
1589 p_to_date, --to_date
1590 p_capacity, --capacity,
1591 -1, --using_organization_id,
1592 p_refresh_number, --refresh_number,
1593 sysdate, --last_update_date,
1594 l_user_id, --last_updated_by,
1595 sysdate, --creation_date,
1596 l_user_id, --created_by,
1597 null, --status
1598 null, --applied
1599 3, --1, --collected_flag
1600 -999); -- to distinguish the data from receive from exchange
1601 END;
1602 END IF;
1603
1604 EXCEPTION
1605
1606 WHEN others THEN
1607 LOG_MESSAGE('Error in insert capacity ' || sqlerrm);
1608 LOG_MESSAGE( 'Insert_capacity' || sqlerrm);
1609 null;
1610 END INSERT_CAPACITY;
1611
1612
1613 ------------------------------------------------------------------------------------
1614 --UPDATE_CAPACITY
1615 -----------------------------------------------------------------------------------
1616 PROCEDURE Update_Capacity(p_sr_instance_id IN Number,
1617 p_organization_id IN Number,
1618 p_supplier_id IN Number,
1619 p_supplier_site_id IN Number,
1620 p_item_id IN Number,
1621 p_from_date IN Date,
1622 p_to_date IN Date,
1623 p_capacity IN Number,
1624 p_transaction_id IN Number) IS
1625 l_exist Number := 0;
1626
1627 BEGIN
1628 begin
1629 SELECT 1 into l_exist from dual
1630 WHERE exists (SELECT 1
1631 from msc_supplier_capacities
1632 where plan_id = -1
1633 and sr_instance_id = p_sr_instance_id
1634 and transaction_id = p_transaction_id
1635 and inventory_item_id = p_item_id
1636 and organization_id = p_organization_id
1637 and supplier_id = p_supplier_id
1638 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
1639 and trunc(from_date) = trunc(p_from_date)
1640 and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
1641 and nvl(last_update_login,-1) <> -999);
1642 exception
1643 when no_data_found then
1644 l_exist := 0;
1645 end;
1646
1647 IF (l_exist = 1 ) THEN
1648
1649 LOG_MESSAGE('update sc and override the collected/manually data');
1650 update msc_supplier_capacities
1651 set capacity = p_capacity,
1652 from_date = p_from_date,
1653 to_date = p_to_date,
1654 collected_flag = 3, -- bug 5208105
1655 last_update_login = -999
1656 where plan_id = -1
1657 and sr_instance_id = p_sr_instance_id
1658 and transaction_id = p_transaction_id
1659 and inventory_item_id = p_item_id
1660 and organization_id = p_organization_id
1661 and supplier_id = p_supplier_id
1662 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
1663 and trunc(from_date) = trunc(p_from_date)
1664 and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
1665 and nvl(last_update_login,-1) <> -999;
1666 ELSE
1667
1668 LOG_MESSAGE('do not override the current data receiving from exchange');
1669 update msc_supplier_capacities
1670 set capacity = capacity + p_capacity,
1671 from_date = p_from_date,
1672 to_date = p_to_date,
1673 collected_flag = 3, -- bug 5208105
1674 last_update_login = -999
1675 where plan_id = -1
1676 and sr_instance_id = p_sr_instance_id
1677 and transaction_id = p_transaction_id
1678 and inventory_item_id = p_item_id
1679 and organization_id = p_organization_id
1680 and supplier_id = p_supplier_id
1681 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
1682 and trunc(from_date) = trunc(p_from_date)
1683 and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
1684 and last_update_login = -999;
1685 END IF;
1686
1687 EXCEPTION
1688
1689 WHEN others
1690 THEN
1691 LOG_MESSAGE('Error in update capacity' || sqlerrm);
1692 LOG_MESSAGE( sqlerrm);
1693
1694 END UPDATE_CAPACITY;
1695
1696 ------------------------------------------------------------------------
1697 -- LOAD_CAPACITY
1698 -------------------------------------------------------------------------
1699 PROCEDURE Load_Supply_Schedule(p_sr_instance_id IN Number,
1700 p_organization_id IN Number,
1701 p_supplier_id IN Number,
1702 p_supplier_site_id IN Number,
1703 p_mod_org_id In Number,
1704 p_mod_org_code IN Varchar2,
1705 p_mps_designator_id In Number,
1706 p_item_id IN Number,
1707 p_date IN Date,
1708 p_capacity IN Number,
1709 p_refresh_number IN Number) IS
1710
1711 l_mps_designator_id Number := -99;
1712 l_mps_designator msc_designators.designator%type;
1713 l_exist Number := 0;
1714
1715
1716 BEGIN
1717 LOG_MESSAGE('Load supply Schedule');
1718
1719
1720 IF (p_mps_designator_id is NULL) THEN
1721 --bug# 2470463 to change the name from SCE to CP
1722 SELECT 'CP' ||
1723 substr(p_mod_org_code,1,instr(p_mod_org_code,':')-1) ||
1724 '-' || substr(p_mod_org_code,instr(p_mod_org_code,':')+1,7)
1725 INTO l_mps_designator
1726 FROM dual;
1727
1728 begin
1729 SELECT designator_id, designator
1730 INTO l_mps_designator_id, l_mps_designator
1731 FROM msc_designators
1732 WHERE sr_instance_id = p_sr_instance_id
1733 AND organization_id = p_mod_org_id
1734 AND designator_type = 2
1735 AND designator = l_mps_designator;
1736
1737 fnd_message.set_name('MSC','MSC_PUB_MPS_DESIGNATOR');
1738 LOG_MESSAGE( fnd_message.get || ' ' || l_mps_designator);
1739 LOG_MESSAGE('Existing MPS designator ' || l_mps_designator);
1740 exception
1741 when no_data_found then
1742 l_mps_designator_id := null;
1743 end;
1744
1745 IF (l_mps_designator_id is null ) THEN
1746 Insert_MPS_Designator(p_sr_instance_id,
1747 p_organization_id,
1748 p_supplier_id,
1749 p_supplier_site_id,
1750 l_mps_designator,
1751 p_refresh_number,
1752 l_mps_designator_id
1753 );
1754
1755 END IF;
1756 ELSE
1757 begin
1758 select designator
1759 into l_mps_designator
1760 from msc_designators
1761 where designator_id = p_mps_designator_id;
1762 exception
1763 when no_data_found then
1764 l_mps_designator_id := null;
1765 end;
1766 l_mps_designator_id := p_mps_designator_id;
1767 fnd_message.set_name('MSC','MSC_PUB_MPS_DESIGNATOR');
1768 LOG_MESSAGE( fnd_message.get || ' ' || l_mps_designator);
1769 LOG_MESSAGE('Existing MPS designator ' || l_mps_designator);
1770 END IF;
1771 Insert_Supply_Schedule(p_sr_instance_id,
1772 p_organization_id,
1773 p_supplier_id,
1774 p_supplier_site_id,
1775 l_mps_designator_id,
1776 p_item_id,
1777 p_date,
1778 p_capacity,
1779 p_refresh_number);
1780
1781
1782
1783
1784 EXCEPTION
1785
1786 WHEN others THEN
1787 LOG_MESSAGE('Error in Load Supply schedule ' || sqlerrm);
1788 LOG_MESSAGE( sqlerrm);
1789 END LOAD_SUPPLY_SCHEDULE;
1790
1791
1792 ------------------------------------------------------------------------------------
1793 --INSERT_MPS_DESIGNATOR
1794 -----------------------------------------------------------------------------------
1795 PROCEDURE Insert_MPS_Designator(p_sr_instance_id IN Number,
1796 p_organization_id IN Number,
1797 p_supplier_id In Number,
1798 p_supplier_site_id In Number,
1799 p_mps_designator IN Varchar2,
1800 p_refresh_number IN Number,
1801 p_mps_designator_id OUT NOCOPY Number) IS
1802
1803 l_nextid Number;
1804 l_user_id Number := fnd_global.user_id;
1805 l_refresh_number Number;
1806 l_supplier_name msc_trading_partners.partner_name%type;
1807 l_supplier_site_name msc_trading_partner_sites.tp_site_code%type;
1808 l_desc msc_designators.description%type;
1809 BEGIN
1810
1811
1812 LOG_MESSAGE('insert mps designator');
1813 select msc_designators_s.nextval
1814 into p_mps_designator_id
1815 from dual;
1816 LOG_MESSAGE('MPS Designator ' || p_mps_designator);
1817
1818 begin
1819 select partner_name
1820 into l_supplier_name
1821 from msc_trading_partners
1822 where partner_id = p_supplier_id
1823 and partner_type = 1;
1824
1825 select tp_site_code
1826 into l_supplier_site_name
1827 from msc_trading_partner_sites
1828 where partner_id = p_supplier_id
1829 and partner_site_id = p_supplier_site_id;
1830
1831 exception
1832 when no_data_found then
1833 null;
1834 when others then
1835 null;
1836 LOG_MESSAGE('Error in mps ' || sqlerrm);
1837 end;
1838
1839 l_desc := l_supplier_name ||',' || l_supplier_site_name;
1840
1841 fnd_message.set_name('MSC','MSC_PUB_MPS_DESIGNATOR');
1842 LOG_MESSAGE( fnd_message.get || ' ' || p_mps_designator || ' for supplier ' || l_desc);
1843
1844
1845 insert into msc_designators (
1846 designator_id,
1847 designator,
1848 organization_id,
1849 sr_instance_id,
1850 designator_type,
1851 mps_relief,
1852 inventory_atp_flag,
1853 description,
1854 organization_selection,
1855 production,
1856 disable_date,
1857 refresh_number,
1858 last_update_date,
1859 last_updated_by,
1860 creation_date,
1861 created_by,
1862 collected_flag)
1863 values (
1864 p_mps_designator_id,
1865 p_mps_designator,
1866 p_organization_id,
1867 p_sr_instance_id,
1868 2,
1869 2,
1870 2,
1871 substr(l_desc,1,50),
1872 1,
1873 2,
1874 null,
1875 p_refresh_number,
1876 sysdate,
1877 l_user_id,
1878 sysdate,
1879 l_user_id,
1880 null);
1881
1882 commit;
1883 EXCEPTION
1884
1885 WHEN others THEN
1886 LOG_MESSAGE('Error in insert mps designator ' || sqlerrm);
1887 LOG_MESSAGE( sqlerrm);
1888 END INSERT_MPS_DESIGNATOR;
1889
1890 ------------------------------------------------------------------------------------
1891 --INSERT_SUPPLY_SCHEDULE
1892 -----------------------------------------------------------------------------------
1893 PROCEDURE Insert_Supply_schedule(p_sr_instance_id IN Number,
1894 p_organization_id IN Number,
1895 p_supplier_id IN Number,
1896 p_supplier_site_id IN Number,
1897 p_mps_designator_id IN Number,
1898 p_item_id IN Number,
1899 p_date IN Date,
1900 p_capacity IN Number,
1901 p_refresh_number IN Number) IS
1902
1903 l_nextid Number;
1904 l_user_id Number := fnd_global.user_id;
1905 l_refresh_number Number;
1906 BEGIN
1907
1908 LOG_MESSAGE('Clean up the supplies first');
1909 begin
1910 DELETE msc_supplies
1911 WHERE plan_id = -1
1912 AND sr_instance_id = p_sr_instance_id
1913 AND organization_id = p_organization_id
1914 AND schedule_designator_id = p_mps_designator_id
1915 AND inventory_item_id = p_item_id
1916 AND order_type = 5
1917 AND nvl(last_update_login,-1) <> -999;
1918 exception
1919 when others then
1920 null;
1921 end;
1922
1923
1924 LOG_MESSAGE('insert msc supplies');
1925 select msc_supplies_s.nextval
1926 into l_nextid
1927 from dual;
1928
1929 insert into msc_supplies (plan_id,
1930 transaction_id,
1931 organization_id,
1932 sr_instance_id,
1933 inventory_item_id,
1934 schedule_designator_id,
1935 new_schedule_date,
1936 order_type,
1937 --supplier_id,
1938 --supplier_site_id,
1939 new_order_quantity,
1940 firm_planned_type,
1941 refresh_number,
1942 last_update_date,
1943 last_updated_by,
1944 creation_date,
1945 created_by,
1946 last_update_login)
1947 values (-1,
1948 l_nextid,
1949 p_organization_id, --organization_id,
1950 p_sr_instance_id,
1951 p_item_id,
1952 p_mps_designator_id,
1953 p_date,
1954 5, --planned order
1955 --p_supplier_id,
1956 --p_supplier_site_id, --p_supplier_site_id,
1957 p_capacity, --capacity,
1958 2, --firm planned type
1959 p_refresh_number, --refresh_number,
1960 sysdate, --last_update_date,
1961 l_user_id, --last_updated_by,
1962 sysdate, --creation_date,
1963 l_user_id,
1964 -999);
1965
1966 EXCEPTION
1967
1968 WHEN others THEN
1969 LOG_MESSAGE('Error in insert supply schedule ' || sqlerrm);
1970 LOG_MESSAGE( sqlerrm);
1971 END INSERT_SUPPLY_SCHEDULE;
1972
1973
1974
1975 END MSC_X_RECEIVE_CAPACITY_PKG ;