1 PACKAGE BODY MSC_X_HZ_UPDATE AS
2 /* $Header: MSCXHZUB.pls 120.2 2005/07/28 03:38:28 pragarwa noship $ */
3
4
5 /**
6 * The foll procedure has to update msc_sup_dem_entries with the
7 * updated values from the msc_hz_ui_lines table.
8 * The proc has to do the foll.
9 * - using the query id - select the rows that have the quantities updated
10 * (where qty_bucket(X) <> old_qty(X) )
11 * - for each of the delta values - select the corresponding
12 * transactionids from msc_sup_dem_entries.
13 * order by quantity and createion date.
14 * - update the delta appropriately
15 */
16
17 module CONSTANT VARCHAR2(27) := 'msc.plsql.MSC_X_HZ_UPDATE.' ;
18 DECPLACES CONSTANT NUMBER := 6;
19
20
21 Procedure update_supdem_entries( arg_err_msg OUT NOCOPY VARCHAR2,
22 arg_query_id IN NUMBER
23 )
24 IS
25
26 -- define the types
27 TYPE num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
28 TYPE string IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
29 TYPE big_string IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
30 TYPE small_string IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
31 TYPE small_string1 IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
32 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
33
34 WEEK_BUCKET CONSTANT NUMBER(1) := 2;
35 MONTH_BUCKET CONSTANT NUMBER(1) := 3;
36
37 temp NUMBER;
38
39
40 proceed number(1) := 0;
41 insert_rec number(1) := 0;
42
43 -- define the variables needed
44 v_pub_id number;
45 v_pub_name varchar2(250);
46 v_user_id NUMBER;
47
48 v_item_id num;
49 v_order_type num;
50 v_qty1 num;
51 v_qty2 num;
52 v_qty3 num;
53 v_qty4 num;
54 v_qty5 num;
55 v_qty6 num;
56 v_qty7 num;
57 v_qty8 num;
58 v_qty9 num;
59 v_qty10 num;
60 v_qty11 num;
61 v_qty12 num;
62 v_qty13 num;
63 v_qty14 num;
64 v_qty15 num;
65 v_qty16 num;
66 v_qty17 num;
67 v_qty18 num;
68 v_qty19 num;
69 v_qty20 num;
70 v_qty21 num;
71 v_qty22 num;
72 v_qty23 num;
73 v_qty24 num;
74 v_qty25 num;
75 v_qty26 num;
76 v_qty27 num;
77 v_qty28 num;
78 v_qty29 num;
79 v_qty30 num;
80 v_qty31 num;
81 v_qty32 num;
82 v_qty33 num;
83 v_qty34 num;
84 v_qty35 num;
85 v_qty36 num;
86
87 v_old_qty1 num;
88 v_old_qty2 num;
89 v_old_qty3 num;
90 v_old_qty4 num;
91 v_old_qty5 num;
92 v_old_qty6 num;
93 v_old_qty7 num;
94 v_old_qty8 num;
95 v_old_qty9 num;
96 v_old_qty10 num;
97 v_old_qty11 num;
98 v_old_qty12 num;
99 v_old_qty13 num;
100 v_old_qty14 num;
101 v_old_qty15 num;
102 v_old_qty16 num;
103 v_old_qty17 num;
104 v_old_qty18 num;
105 v_old_qty19 num;
106 v_old_qty20 num;
107 v_old_qty21 num;
108 v_old_qty22 num;
109 v_old_qty23 num;
110 v_old_qty24 num;
111 v_old_qty25 num;
112 v_old_qty26 num;
113 v_old_qty27 num;
114 v_old_qty28 num;
115 v_old_qty29 num;
116 v_old_qty30 num;
117 v_old_qty31 num;
118 v_old_qty32 num;
119 v_old_qty33 num;
120 v_old_qty34 num;
121 v_old_qty35 num;
122 v_old_qty36 num;
123 v_pub_site_id num;
124
125 v_item_name string;
126 v_sup_name string;
127 v_cust_name string;
128 v_sup_site small_string;
129 v_cust_site small_string;
130 v_uom small_string;
131 v_order_desc small_string;
132 v_pub_site string;
133 v_bucket_type num;
134
135 v_item_desc string ;
136 v_owner_item string;
137 v_sup_item string;
138 v_cust_item string ;
139 v_owner_item_desc string;
140 v_sup_item_desc string;
141 v_cust_item_desc string;
142 v_bucket_desc small_string;
143
144 -- even though the foll are stored in a varchar2 field - for orgs these will be numbers.
145 v_sup_id num;
146 v_cust_id num;
147 v_sup_site_id num;
148 v_cust_site_id num;
149
150 v_bkt1 date;
151 v_bkt2 date;
152 v_bkt3 date;
153 v_bkt4 date;
154 v_bkt5 date;
155 v_bkt6 date;
156 v_bkt7 date;
157 v_bkt8 date;
158 v_bkt9 date;
159 v_bkt10 date;
160 v_bkt11 date;
161 v_bkt12 date;
162 v_bkt13 date;
163 v_bkt14 date;
164 v_bkt15 date;
165 v_bkt16 date;
166 v_bkt17 date;
167 v_bkt18 date;
168 v_bkt19 date;
169 v_bkt20 date;
170 v_bkt21 date;
171 v_bkt22 date;
172 v_bkt23 date;
173 v_bkt24 date;
174 v_bkt25 date;
175 v_bkt26 date;
176 v_bkt27 date;
177 v_bkt28 date;
178 v_bkt29 date;
179 v_bkt30 date;
180 v_bkt31 date;
181 v_bkt32 date;
182 v_bkt33 date;
183 v_bkt34 date;
184 v_bkt35 date;
185 v_bkt36 date;
186 v_last_bkt date;
187
188 v_trans_id num;
189 v_p_qty num ;
190 v_p_uom small_string ;
191 v_uom_code small_string;
192 v_tp_uom small_string;
193 v_item num;
194
195 -- pl/sql tables required for update
196 v_upd_trans_id num;
197 v_upd_p_qty num ;
198 v_upd_qty num ;
199 v_upd_tp_qty num;
200
201
202 -- pl/sql tables required for insert.
203 v_i_item_id num;
204 v_i_cust_id num;
205 v_i_sup_id num;
206 v_i_pub_site_id num;
207 v_i_cust_site_id num;
208 v_i_sup_site_id num;
209 v_i_p_qty num;
210 v_i_order_type num;
211 v_i_bucket num;
212 v_i_tp_qty num;
213 v_i_qty num;
214 v_i_ship_from_id num;
215 v_i_ship_from_site_id num;
216 v_i_ship_to_id num;
217 v_i_ship_to_site_id num;
218
219 v_i_item string;
220 v_i_item_desc string;
221 v_i_owner_item string;
222 v_i_cust_item string;
223 v_i_sup_item string;
224 v_i_cust string;
225 v_i_sup string;
226 v_i_cust_site small_string;
227 v_i_sup_site small_string;
228 v_i_pub_site_name small_string;
229 v_i_p_uom small_string;
230 v_i_order small_string;
231 v_i_bucket_desc small_string;
232 v_i_owner_item_desc string;
233 v_i_cust_item_desc string;
234 v_i_sup_item_desc string;
235 v_i_tp_uom small_string;
236 v_i_uom_code small_string;
237 v_i_ship_from string;
238 v_i_ship_to string;
239 v_i_ship_from_site small_string;
240 v_i_ship_to_site small_string;
241
242
243 v_i_date calendar_date;
244
245 k number;
246 i number;
247
248
249 -- cursor to fetch the transaction ids from msc_sup_dem_entries;
250 -- using primary_quantity for orde by as for publisher we always display the primary qty on the hz view
251 -- and only publisher can edit the records on HZ View.
252 CURSOR c_transids (arg_item IN NUMBER, arg_order_type IN NUMBER, arg_cust IN VARCHAR2,
253 arg_sup IN VARCHAR2, arg_cust_site IN VARCHAR2, arg_sup_site IN VARCHAR2,
254 arg_start IN DATE, arg_end IN DATE)
255 IS
256 SELECT transaction_id,primary_quantity,primary_uom,uom_code,tp_uom_code,inventory_item_id
257 FROM msc_sup_dem_entries_ui_v
258 WHERE inventory_item_id = arg_item
259 AND customer_name = arg_cust
260 AND supplier_name = arg_sup
261 AND customer_site_name= arg_cust_site
262 AND supplier_site_name = arg_sup_site
263 AND publisher_order_type = arg_order_type
264 AND key_date >= arg_start
265 AND key_date < arg_end
266 ORDER BY primary_quantity, creation_date;
267
268 /**
269 * The folowing procedure adds the required updatable columns to the
270 * appropriate pl/sql srtuctures.
271 */
272 PROCEDURE set_for_update(arg_new_qty in number,
273 arg_old_qty in number,
274 arg_start IN DATE,
275 arg_end IN DATE,
276 arg_pos IN NUMBER)
277 IS
278 k number;
279 l number;
280 i number := arg_pos;
281
282 v_delta number;
283 BEGIN
284 --dbms_output.put_line('pkaligot : 89 : in set_for_update with cnt :' || arg_pos );
285 insert_rec := 1;
286 -- add to update structure the changed qty.
287 open c_transids (v_item_id(i), v_order_type(i), v_cust_name(i), v_sup_name(i),
288 v_cust_site(i), v_sup_site(i), arg_start, arg_end) ;
289
290 fetch c_transids bulk collect into
291 v_trans_id,v_p_qty,v_p_uom,v_uom_code,v_tp_uom, v_item ;
292 close c_transids;
293
294 IF v_trans_id.COUNT > 0 THEN
295
296 k := v_upd_trans_id.COUNT + 1;
297 l := v_trans_id.FIRST ;
298 insert_rec := 0;
299
300 v_delta := (arg_new_qty - arg_old_qty) ;
301 v_delta := -1 * v_delta ;
302
303 -- loop through the result till the delta = 0.
304
305 for l in v_trans_id.FIRST..v_trans_id.LAST loop
306
307 k := v_upd_trans_id.COUNT + 1;
308
309 v_upd_trans_id(k) := v_trans_id(l);
310
311 IF v_p_qty(l) < v_delta THEN
312 v_upd_p_qty(k) := 0;
313 v_upd_qty(k) := 0;
314 v_upd_tp_qty(k) := 0;
315
316 ELSE
317 v_upd_p_qty(k) := v_p_qty(l) - v_delta;
318
319 v_upd_qty(k) := MSC_SCE_LOADS_PKG.get_quantity(v_upd_p_qty(k),
320 v_p_uom(l),
321 v_uom_code(l),
322 v_item(l) );
323
324 v_upd_tp_qty(k) := MSC_SCE_LOADS_PKG.get_quantity(v_upd_p_qty(k),
325 v_p_uom(l),
326 v_tp_uom(l),
327 v_item(l) );
328
329
330 END IF;
331
332 v_delta := v_delta - v_p_qty(l) ;
333
334 IF v_delta <= 0 THEN
335 EXIT;
336 END IF;
337
338 end loop;
339
340 END IF;
341
342 EXCEPTION
343 when others then
344 proceed := 1;
345 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
346 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'set_for_update', SQLERRM);
347 end if;
348
349 arg_err_msg := 'MSC_X_HZ_UPDATE@set_for_update ' || SQLERRM;
350 END set_for_update;
351
352
353 /**
354 * The folowing procedure adds the required insert new columns to the
355 * appropriate pl/sql srtuctures.
356 */
357 PROCEDURE set_for_insert(arg_pos IN NUMBER, arg_start IN DATE, arg_qty IN NUMBER)
358 IS
359 cnt number;
360 v_date date;
361 v_start varchar2(20);
362 l_item_desc VARCHAR2(240);
363 l_primary_uom VARCHAR2(80);
364 l_tp_uom VARCHAR2(80);
365
366 CURSOR publisher_is_supplier
367 (p_i_item_id NUMBER,
368 p_i_cust_site_id IN NUMBER,
369 p_pub_id IN NUMBER,
370 p_i_pub_site_id IN NUMBER) IS
371 SELECT mis.uom_code
372 FROM msc_item_suppliers mis,
373 msc_trading_partner_maps map,
374 msc_trading_partners mtp,
375 msc_trading_partner_maps map1,
376 msc_company_relationships rel,
377 msc_trading_partner_maps map2
378 WHERE mis.inventory_item_id = p_i_item_id
379 AND mis.plan_id = -1
380 AND mis.organization_id = mtp.sr_tp_id
381 AND mis.sr_instance_id = mtp.sr_instance_id
382 AND mtp.partner_id = map.tp_key
383 AND map.map_type = 2
384 AND map.company_key = p_i_cust_site_id
385 AND mis.supplier_id = map1.tp_key
386 AND map1.company_key = rel.relationship_id
387 AND map1.map_type = 1
388 AND rel.relationship_type = 2
389 AND rel.subject_id = 1
390 AND rel.object_id = p_pub_id
391 AND mis.supplier_site_id = map2.tp_key
392 AND map2.map_type = 3
393 AND map2.company_key = p_i_pub_site_id
394 ORDER BY mis.using_organization_id DESC;
395
396 CURSOR supplier_item_c
397 (p_item_id NUMBER,
398 p_pub_site_id NUMBER,
399 p_supp_id NUMBER,
400 p_supp_site_id NUMBER) IS
401 SELECT mis.uom_code
402 FROM msc_item_suppliers mis,
403 msc_trading_partner_maps map,
404 msc_trading_partner_maps map1,
405 msc_trading_partner_maps map2,
406 msc_trading_partners mtp,
407 msc_company_relationships r
408 where mis.inventory_item_id = p_item_id and
409 mis.plan_id = -1 and
410 mis.organization_id = mtp.sr_tp_id and
411 mis.sr_instance_id = mtp.sr_instance_id and
412 mtp.partner_id = map2.tp_key and
413 mtp.partner_type = 3 and
414 map2.company_key = p_pub_site_id and
415 map2.map_type = 2 and
416 mis.supplier_id = map.tp_key and
417 mis.supplier_site_id = map1.tp_key and
418 map.map_type = 1 and
419 map.company_key = r.relationship_id and
420 r.relationship_type = 2 and
421 r.subject_id = 1 and
422 r.object_id = p_supp_id and
423 map1.map_type = 3 and
424 map1.company_key = p_supp_site_id
425 order by mis.using_organization_id desc;
426
427 BEGIN
428 cnt := v_i_item_id.COUNT + 1;
429 ----dbms_output.put_line('pkaligot : 90 : in set_for_insert with cnt :' || arg_pos );
430
431 v_start := to_char(arg_start, 'DD/MM/YYYY HH24:MI:SS');
432
433 -- make the calculations for the exact date here
434 -- the date should be the first beginning after the start and before the end.
435
436 if v_bucket_type(arg_pos) = WEEK_BUCKET then
437 SELECT cal.prior_date
438 INTO v_date
439 FROM msc_calendar_dates cal, msc_cal_week_start_dates wsd
440 WHERE cal.calendar_code = wsd.calendar_code
441 AND wsd.calendar_code = 'CP-Mon-70'
442 AND wsd.exception_set_id = -1
443 AND wsd.sr_instance_id = 0
444 AND wsd.next_date > TRUNC(TO_DATE(v_start, 'DD/MM/YYYY HH24:MI:SS'))
445 AND wsd.week_start_date <= TRUNC(TO_DATE(v_start, 'DD/MM/YYYY HH24:MI:SS'))
446 AND cal.exception_set_id = wsd.exception_set_id
447 AND cal.sr_instance_id = wsd.sr_instance_id
448 AND cal.calendar_date = wsd.week_start_date ;
449
450 elsif v_bucket_type(arg_pos) = MONTH_BUCKET then
451
452 SELECT cal.next_date
453 INTO v_date
454 FROM msc_calendar_dates cal, msc_period_start_dates psd
455 WHERE psd.calendar_code = 'CP-Mon-70' AND
456 psd.exception_set_id = -1 AND
457 psd.sr_instance_id = 0 AND
458 psd.next_date > TRUNC(TO_DATE(v_start, 'DD/MM/YYYY HH24:MI:SS')) AND
459 psd.period_start_date <= TRUNC(TO_DATE(v_start, 'DD/MM/YYYY HH24:MI:SS')) AND
460 cal.calendar_code = psd.calendar_code AND
461 cal.exception_set_id = psd.exception_set_id AND
462 cal.sr_instance_id = psd.sr_instance_id AND
463 cal.calendar_date = psd.period_start_date ;
464 else
465 v_date := arg_start;
466
467 end if;
468
469 v_i_date(cnt) := v_date;
470 v_i_item_id(cnt) := v_item_id(arg_pos);
471 v_i_item(cnt) := v_item_name(arg_pos);
472 v_i_item_desc(cnt) := v_item_desc(arg_pos);
473
474 IF v_i_item_desc(cnt) is NULL THEN
475 BEGIN
476 SELECT description
477 INTO l_item_desc
478 FROM msc_items
479 WHERE inventory_item_id = v_i_item_id(cnt);
480 EXCEPTION
481 WHEN OTHERS THEN
482 l_item_desc := NULL;
483 END;
484 v_i_item_desc(cnt) := l_item_desc;
485 END IF;
486
487 v_i_owner_item(cnt) := v_owner_item(arg_pos);
488 v_i_owner_item_desc(cnt) := v_owner_item_desc(arg_pos);
489 v_i_cust_item(cnt) := v_cust_item(arg_pos);
490 v_i_sup_item(cnt) := v_sup_item(arg_pos);
491 v_i_cust_item_desc(cnt) := v_cust_item_desc(arg_pos);
492 v_i_sup_item_desc(cnt) := v_sup_item_desc(arg_pos);
493
494 v_i_pub_site_name(cnt) := v_pub_site(arg_pos);
495 v_i_pub_site_id(cnt) := v_pub_site_id(arg_pos);
496
497 v_i_cust_id(cnt) := v_cust_id(arg_pos);
498 v_i_cust(cnt) := v_cust_name(arg_pos);
499 v_i_cust_site_id(cnt) := v_cust_site_id(arg_pos);
500 v_i_cust_site(cnt) := v_cust_site(arg_pos);
501
502 v_i_sup_id(cnt) := v_sup_id(arg_pos);
503 v_i_sup(cnt) := v_sup_name(arg_pos);
504 v_i_sup_site_id(cnt) := v_sup_site_id(arg_pos);
505 v_i_sup_site(cnt) := v_sup_site(arg_pos);
506
507
508 v_i_order_type(cnt) := v_order_type(arg_pos);
509 v_i_order(cnt) := nvl(MSC_X_HZ_PLAN.get_lookup_name('MSC_X_ORDER_TYPE',v_i_order_type(cnt)),'NA') ;
510 v_i_bucket(cnt) := v_bucket_type(arg_pos);
511 v_i_bucket_desc(cnt) := nvl(MSC_X_HZ_PLAN.get_lookup_name('MSC_X_BUCKET_TYPE',v_i_bucket(cnt)),'NA');
512
513
514 IF v_i_order_type(cnt) IN (2,3) THEN
515 v_i_ship_from_id(cnt) := v_sup_id(arg_pos);
516 v_i_ship_from_site_id(cnt) := v_sup_site_id(arg_pos);
517 v_i_ship_from(cnt) := v_sup_name(arg_pos);
518 v_i_ship_from_site(cnt) := v_sup_site(arg_pos);
519 v_i_ship_to_id(cnt) := v_cust_id(arg_pos);
520 v_i_ship_to_site_id(cnt) := v_cust_site_id(arg_pos);
521 v_i_ship_to(cnt) := v_cust_name(arg_pos);
522 v_i_ship_to_site(cnt) := v_cust_site(arg_pos);
523 ELSE
524 v_i_ship_from_id(cnt) := NULL;
525 v_i_ship_from_site_id(cnt) := NULL;
526 v_i_ship_from(cnt) := NULL;
527 v_i_ship_from_site(cnt) := NULL;
528 v_i_ship_to_id(cnt) := NULL;
529 v_i_ship_to_site_id(cnt) := NULL;
530 v_i_ship_to(cnt) := NULL;
531 v_i_ship_to_site(cnt) := NULL;
532 END IF;
533
534
535 v_i_p_qty(cnt) := arg_qty;
536
537 --Set the loaded uom
538 v_i_uom_code(cnt) := v_uom(arg_pos);
539
540 --Obtain the primary uom
541 IF (v_pub_id = 1) THEN
542 BEGIN
543 SELECT msi.uom_code
544 INTO l_primary_uom
545 FROM msc_system_items msi,
546 msc_trading_partners part,
547 msc_trading_partner_maps map
548 WHERE msi.inventory_item_id = v_i_item_id(cnt)
549 AND msi.organization_id = part.sr_tp_id
550 AND msi.sr_instance_id = part.sr_instance_id
551 AND msi.plan_id = -1
552 AND part.partner_id = map.tp_key
553 AND map.company_key = v_i_pub_site_id(cnt)
554 AND map.map_type = 2
555 AND Nvl(part.company_id,1) = v_pub_id;
556 EXCEPTION
557 WHEN OTHERS THEN
558 l_primary_uom := v_uom(arg_pos);
559 END;
560 -- If publisher is not the OEM and order type is Supply Commit
561 -- Fetch uom from the ASL, since publisher is usually the supplier.
562 ELSIF (v_pub_id <> 1) AND (v_i_order_type(cnt) = 3) THEN
563 OPEN publisher_is_supplier(v_i_item_id(cnt),
564 v_i_cust_site_id(cnt),
565 v_pub_id,
566 v_i_pub_site_id(cnt));
567 FETCH publisher_is_supplier
568 INTO l_primary_uom;
569
573 BEGIN
570 IF (publisher_is_supplier%notfound or l_primary_uom is null )THEN
571 CLOSE publisher_is_supplier;
572 --If the ASL is not present, derive the primary uom from the oem
574 SELECT msi.uom_code
575 INTO l_primary_uom
576 FROM msc_system_items msi,
577 msc_trading_partners part,
578 msc_trading_partner_maps map
579 WHERE msi.inventory_item_id = v_i_item_id(cnt)
580 AND msi.organization_id = part.sr_tp_id
581 AND msi.sr_instance_id = part.sr_instance_id
582 AND msi.plan_id = -1
583 AND part.partner_id = map.tp_key
584 AND map.map_type = 2
585 AND map.company_key = Decode(v_i_sup_id(cnt),
586 v_pub_id, v_i_cust_site_id(cnt),
587 v_i_sup_site_id(cnt))
588 AND Nvl(part.company_id,1) = Decode(v_i_sup_id(cnt),
589 v_pub_id, v_i_cust_id(cnt),
590 v_i_sup_id(cnt));
591 EXCEPTION
592 WHEN OTHERS THEN
593 l_primary_uom := v_uom(arg_pos);
594 END;
595 END IF;
596 IF publisher_is_supplier%ISOPEN THEN
597 CLOSE publisher_is_supplier;
598 END IF;
599 --If the publisher is not the OEM and the order type is order forecast
600 --obtain the uom from MSC_ITEM_CUSTOMERS, since the publisher is usually
601 --the customer
602 ELSIF (v_pub_id <> 1) AND (v_i_order_type(cnt) = 2) THEN
603 BEGIN
604 SELECT mic.uom_code
605 INTO l_primary_uom
606 FROM msc_item_customers mic,
607 msc_trading_partner_maps map,
608 msc_trading_partner_maps map1,
609 msc_company_relationships r
610 WHERE mic.inventory_item_id = v_i_item_id(cnt)
611 AND mic.plan_id = -1
612 AND mic.customer_id = map.tp_key
613 AND mic.customer_site_id = map1.tp_key
614 AND map.map_type = 1
615 AND map.company_key = r.relationship_id
616 AND r.relationship_type = 1
617 AND r.subject_id = 1
618 AND r.object_id = v_pub_id
619 AND map1.map_type = 3
620 AND map1.company_key = v_i_pub_site_id(cnt);
621 EXCEPTION
622 WHEN OTHERS THEN
623 --If the uom is not available from MIC, derive it from the OEM
624 BEGIN
625 SELECT msi.uom_code
626 INTO l_primary_uom
627 FROM msc_system_items msi,
628 msc_trading_partners part,
629 msc_trading_partner_maps map
630 WHERE msi.inventory_item_id = v_i_item_id(cnt)
631 AND msi.organization_id = part.sr_tp_id
632 AND msi.sr_instance_id = part.sr_instance_id
633 AND msi.plan_id = -1
634 AND part.partner_id = map.tp_key
635 AND map.map_type = 2
636 AND map.company_key = Decode(v_i_sup_id(cnt),
637 v_pub_id, v_i_cust_site_id(cnt),
638 v_i_sup_site_id(cnt))
639 AND Nvl(part.company_id,1) = Decode(v_i_sup_id(cnt),
640 v_pub_id, v_i_cust_id(cnt),
641 v_i_sup_id(cnt));
642 EXCEPTION
643 WHEN OTHERS THEN
644 l_primary_uom := v_uom(arg_pos);
645 END;
646 END;
647 END IF;
648
649 v_i_p_uom(cnt) := l_primary_uom;
650
651 --Obtain the tp uom
652 IF (v_pub_id = v_i_sup_id(cnt)) THEN
653 --Customer is the tp
654 IF (v_i_cust_id(cnt) = 1) THEN
655 BEGIN
656 SELECT msi.uom_code
657 INTO l_tp_uom
658 FROM msc_system_items msi,
659 msc_trading_partners part,
660 msc_trading_partner_maps map
661 WHERE msi.inventory_item_id = v_i_item_id(cnt)
662 AND msi.organization_id = part.sr_tp_id
663 AND msi.sr_instance_id = part.sr_instance_id
664 AND msi.plan_id = -1
665 AND part.partner_id = map.tp_key
666 AND map.map_type = 2
667 AND map.company_key = v_i_cust_site_id(cnt)
668 AND Nvl(part.company_id,1) = v_i_cust_id(cnt);
669 EXCEPTION
670 WHEN OTHERS THEN
671 l_tp_uom := v_i_p_uom(cnt);
672 END;
673 ELSE
674 BEGIN
675 SELECT mic.uom_code
676 INTO l_tp_uom
677 FROM msc_item_customers mic,
678 msc_trading_partner_maps map,
679 msc_trading_partner_maps map1,
680 msc_company_relationships r
681 WHERE mic.inventory_item_id = v_i_item_id(cnt)
682 AND mic.plan_id = -1
683 AND mic.customer_id = map.tp_key
684 AND mic.customer_site_id = map1.tp_key
685 AND map.map_type = 1
686 AND map.company_key = r.relationship_id
687 AND r.relationship_type = 1
688 AND r.subject_id = 1
689 AND r.object_id = v_i_cust_id(cnt)
690 AND map1.map_type = 3
691 AND map1.company_key = v_i_cust_site_id(cnt);
692 EXCEPTION
693 WHEN OTHERS THEN
694 l_tp_uom := v_i_p_uom(cnt);
695 END;
696
697 END IF;
698
699 ELSIF (v_pub_id = v_i_cust_id(cnt)) THEN
700 --Supplier is the tp
701 IF (v_i_sup_id(cnt) = 1) THEN
702 BEGIN
703 SELECT msi.uom_code
704 INTO l_tp_uom
705 FROM msc_system_items msi,
706 msc_trading_partners part,
707 msc_trading_partner_maps map
708 WHERE msi.inventory_item_id = v_i_item_id(cnt)
709 AND msi.organization_id = part.sr_tp_id
710 AND msi.sr_instance_id = part.sr_instance_id
711 AND msi.plan_id = -1
715 AND Nvl(part.company_id,1) = v_i_sup_id(cnt);
712 AND part.partner_id = map.tp_key
713 AND map.map_type = 2
714 AND map.company_key = v_i_sup_site_id(cnt)
716 EXCEPTION
717 WHEN OTHERS THEN
718 l_tp_uom := v_i_p_uom(cnt);
719 END;
720 ELSE
721 OPEN supplier_item_c(v_i_item_id(cnt),
722 v_i_pub_site_id(cnt),
723 v_i_sup_id(cnt),
724 v_i_sup_site_id(cnt));
725
726 FETCH supplier_item_c INTO l_tp_uom;
727
728 IF supplier_item_c%notfound THEN
729 l_tp_uom := v_i_p_uom(cnt);
730 END IF;
731 END IF;
732
733 END IF;
734
735 v_i_tp_uom(cnt) := l_tp_uom;
736
737 v_i_tp_qty(cnt) := MSC_SCE_LOADS_PKG.get_quantity(v_i_p_qty(cnt),
738 v_i_p_uom(cnt),
739 v_i_tp_uom(cnt),
740 v_i_item_id(cnt) );
741
742 v_i_qty(cnt) := MSC_SCE_LOADS_PKG.get_quantity(v_i_p_qty(cnt),
743 v_i_p_uom(cnt),
744 v_i_uom_code(cnt),
745 v_i_item_id(cnt) );
746
747 EXCEPTION
748 when others then
749 proceed := 1;
750 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
751 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'set_for_insert', SQLERRM);
752 end if;
753 arg_err_msg := 'MSC_X_HZ_UPDATE@set_for_insert ' || SQLERRM;
754 --dbms_output.put_line('pkaligot : 50 : arg_err_msg :' || arg_err_msg );
755
756 END set_for_insert;
757
758
759 BEGIN
760
761 SELECT sys_context('MSC','COMPANY_ID'), sys_context('MSC','COMPANY_NAME')
762 INTO v_pub_id, v_pub_name
763 FROM DUAL;
764
765 -- select the date bkts .
766 SELECT BUCKET1,nvl(BUCKET2,LAST_BUCKET),nvl(BUCKET3,LAST_BUCKET),nvl(BUCKET4,LAST_BUCKET),
767 nvl(BUCKET5,LAST_BUCKET),nvl(BUCKET6,LAST_BUCKET),nvl(BUCKET7,LAST_BUCKET),
768 nvl(BUCKET8,LAST_BUCKET),nvl(BUCKET9,LAST_BUCKET),nvl(BUCKET10,LAST_BUCKET),
769 nvl(BUCKET11,LAST_BUCKET),nvl(BUCKET12,LAST_BUCKET),nvl(BUCKET13,LAST_BUCKET),
770 nvl(BUCKET14,LAST_BUCKET),nvl(BUCKET15,LAST_BUCKET),nvl(BUCKET16,LAST_BUCKET),
771 nvl(BUCKET17,LAST_BUCKET),nvl(BUCKET18,LAST_BUCKET),nvl(BUCKET19,LAST_BUCKET),
772 nvl(BUCKET20,LAST_BUCKET),nvl(BUCKET21,LAST_BUCKET),nvl(BUCKET22,LAST_BUCKET),
773 nvl(BUCKET23,LAST_BUCKET),nvl(BUCKET24,LAST_BUCKET),nvl(BUCKET25,LAST_BUCKET),
774 nvl(BUCKET26,LAST_BUCKET),nvl(BUCKET27,LAST_BUCKET),nvl(BUCKET28,LAST_BUCKET),
775 nvl(BUCKET29,LAST_BUCKET),nvl(BUCKET30,LAST_BUCKET),nvl(BUCKET31,LAST_BUCKET),
776 nvl(BUCKET32,LAST_BUCKET),nvl(BUCKET33,LAST_BUCKET),nvl(BUCKET34,LAST_BUCKET),
777 nvl(BUCKET35,LAST_BUCKET),nvl(BUCKET36,LAST_BUCKET),LAST_BUCKET
778 INTO v_bkt1,v_bkt2,v_bkt3,v_bkt4,v_bkt5,v_bkt6,v_bkt7,v_bkt8,v_bkt9,v_bkt10,
779 v_bkt11,v_bkt12,v_bkt13,v_bkt14,v_bkt15,v_bkt16,v_bkt17,v_bkt18,v_bkt19,
780 v_bkt20,v_bkt21,v_bkt22,v_bkt23,v_bkt24,v_bkt25,v_bkt26,v_bkt27,v_bkt28,
781 v_bkt29,v_bkt30,v_bkt31,v_bkt32,v_bkt33,v_bkt34,v_bkt35,v_bkt36,v_last_bkt
782 FROM msc_hz_ui_headers
783 WHERE query_id = arg_query_id ;
784
785
786 -- select the changed quantities.
787 SELECT inventory_item_id,item_name,supplier_id,customer_id,supplier_site_id,
788 customer_site_id,supplier_name,customer_name,supplier_org_code,customer_org_code,
789 order_type,order_type_desc,uom,owner_item,sup_item,cust_item,item_description,
790 owner_item_desc,sup_item_desc,cust_item_desc,from_org_code,bucket_type,
791 qty_bucket1,qty_bucket2,qty_bucket3,qty_bucket4,qty_bucket5,qty_bucket6,qty_bucket7,
792 qty_bucket8,qty_bucket9,qty_bucket10,qty_bucket11,qty_bucket12,qty_bucket13,
793 qty_bucket14,qty_bucket15,qty_bucket16,qty_bucket17,qty_bucket18,qty_bucket19,
794 qty_bucket20,qty_bucket21,qty_bucket22,qty_bucket23,qty_bucket24,qty_bucket25,
795 qty_bucket26,qty_bucket27,qty_bucket28,qty_bucket29,qty_bucket30,qty_bucket31,
796 qty_bucket32,qty_bucket33,qty_bucket34,qty_bucket35,qty_bucket36,
797 old_qty1,old_qty2,old_qty3,old_qty4,old_qty5,old_qty6,old_qty7,old_qty8,
798 old_qty9,old_qty10,old_qty11,old_qty12,old_qty13,old_qty14,old_qty15,
799 old_qty16,old_qty17,old_qty18,old_qty19,old_qty20,old_qty21,old_qty22,
800 old_qty23,old_qty24,old_qty25,old_qty26,old_qty27,old_qty28,old_qty29,
801 old_qty30,old_qty31,old_qty32,old_qty33,old_qty34,old_qty35,old_qty36,
802 publisher_site_id
803 BULK COLLECT INTO
804 v_item_id, v_item_name, v_sup_id, v_cust_id, v_sup_site_id, v_cust_site_id,
805 v_sup_name,v_cust_name,v_sup_site,v_cust_site,v_order_type,v_order_desc,v_uom,
806 v_owner_item,v_sup_item,v_cust_item,v_item_desc,v_owner_item_desc,v_sup_item_desc,
807 v_cust_item_desc,v_pub_site,v_bucket_type,v_qty1,v_qty2,v_qty3,v_qty4,
808 v_qty5,v_qty6,v_qty7,v_qty8,v_qty9,v_qty10,v_qty11,v_qty12,v_qty13,v_qty14,v_qty15,
809 v_qty16,v_qty17,v_qty18,v_qty19,v_qty20,v_qty21,v_qty22,v_qty23,v_qty24,v_qty25,v_qty26,
813 v_old_qty16,v_old_qty17,v_old_qty18,v_old_qty19,v_old_qty20,v_old_qty21,v_old_qty22,
810 v_qty27,v_qty28,v_qty29,v_qty30,v_qty31,v_qty32,v_qty33,v_qty34,v_qty35,v_qty36,
811 v_old_qty1,v_old_qty2,v_old_qty3,v_old_qty4,v_old_qty5,v_old_qty6,v_old_qty7,v_old_qty8,
812 v_old_qty9,v_old_qty10,v_old_qty11,v_old_qty12,v_old_qty13,v_old_qty14,v_old_qty15,
814 v_old_qty23,v_old_qty24,v_old_qty25,v_old_qty26,v_old_qty27,v_old_qty28,v_old_qty29,
815 v_old_qty30,v_old_qty31,v_old_qty32,v_old_qty33,v_old_qty34,v_old_qty35,v_old_qty36,
816 v_pub_site_id
817 FROM msc_hz_ui_lines
818 WHERE query_id = arg_query_id
819 AND editable_flag = 0
820 AND ( ((nvl(qty_bucket1,0) - nvl(old_qty1,0)) <> 0 AND qty_bucket1 is not null) OR ((nvl(qty_bucket2,0) - nvl(old_qty2,0)) <> 0 AND qty_bucket2 is not null) OR
821 ((nvl(qty_bucket3,0) - nvl(old_qty3,0)) <> 0 AND qty_bucket3 is not null) OR ((nvl(qty_bucket4,0) - nvl(old_qty4,0)) <> 0 AND qty_bucket4 is not null) OR
822 ((nvl(qty_bucket5,0) - nvl(old_qty5,0)) <> 0 AND qty_bucket5 is not null) OR ((nvl(qty_bucket6,0) - nvl(old_qty6,0)) <> 0 AND qty_bucket6 is not null) OR
823 ((nvl(qty_bucket7,0) - nvl(old_qty7,0)) <> 0 AND qty_bucket7 is not null) OR ((nvl(qty_bucket8,0) - nvl(old_qty8,0)) <> 0 AND qty_bucket8 is not null) OR
824 ((nvl(qty_bucket9,0) - nvl(old_qty9,0)) <> 0 AND qty_bucket9 is not null) OR ((nvl(qty_bucket10,0) - nvl(old_qty10,0)) <> 0 AND qty_bucket10 is not null) OR
825 ((nvl(qty_bucket11,0) - nvl(old_qty11,0)) <> 0 AND qty_bucket11 is not null) OR ((nvl(qty_bucket12,0) - nvl(old_qty12,0)) <> 0 AND qty_bucket12 is not null) OR
826 ((nvl(qty_bucket13,0) - nvl(old_qty13,0)) <> 0 AND qty_bucket13 is not null) OR ((nvl(qty_bucket14,0) - nvl(old_qty14,0)) <> 0 AND qty_bucket14 is not null) OR
827 ((nvl(qty_bucket15,0) - nvl(old_qty15,0)) <> 0 AND qty_bucket15 is not null) OR ((nvl(qty_bucket16,0) - nvl(old_qty16,0)) <> 0 AND qty_bucket16 is not null) OR
828 ((nvl(qty_bucket17,0) - nvl(old_qty17,0)) <> 0 AND qty_bucket17 is not null) OR ((nvl(qty_bucket18,0) - nvl(old_qty18,0)) <> 0 AND qty_bucket18 is not null) OR
829 ((nvl(qty_bucket19,0) - nvl(old_qty19,0)) <> 0 AND qty_bucket19 is not null) OR ((nvl(qty_bucket20,0) - nvl(old_qty20,0)) <> 0 AND qty_bucket20 is not null) OR
830 ((nvl(qty_bucket21,0) - nvl(old_qty21,0)) <> 0 AND qty_bucket21 is not null) OR ((nvl(qty_bucket22,0) - nvl(old_qty22,0)) <> 0 AND qty_bucket22 is not null) OR
831 ((nvl(qty_bucket23,0) - nvl(old_qty23,0)) <> 0 AND qty_bucket23 is not null) OR ((nvl(qty_bucket24,0) - nvl(old_qty24,0)) <> 0 AND qty_bucket24 is not null) OR
832 ((nvl(qty_bucket25,0) - nvl(old_qty25,0)) <> 0 AND qty_bucket25 is not null) OR ((nvl(qty_bucket26,0) - nvl(old_qty26,0)) <> 0 AND qty_bucket26 is not null) OR
833 ((nvl(qty_bucket27,0) - nvl(old_qty27,0)) <> 0 AND qty_bucket27 is not null) OR ((nvl(qty_bucket28,0) - nvl(old_qty28,0)) <> 0 AND qty_bucket28 is not null) OR
834 ((nvl(qty_bucket29,0) - nvl(old_qty29,0)) <> 0 AND qty_bucket29 is not null) OR ((nvl(qty_bucket30,0) - nvl(old_qty30,0)) <> 0 AND qty_bucket30 is not null) OR
835 ((nvl(qty_bucket31,0) - nvl(old_qty31,0)) <> 0 AND qty_bucket31 is not null) OR ((nvl(qty_bucket32,0) - nvl(old_qty32,0)) <> 0 AND qty_bucket32 is not null) OR
836 ((nvl(qty_bucket33,0) - nvl(old_qty33,0)) <> 0 AND qty_bucket33 is not null) OR ((nvl(qty_bucket34,0) - nvl(old_qty34,0)) <> 0 AND qty_bucket34 is not null) OR
837 ((nvl(qty_bucket35,0) - nvl(old_qty35,0)) <> 0 AND qty_bucket35 is not null) OR ((nvl(qty_bucket36,0) - nvl(old_qty36,0)) <> 0 AND qty_bucket36 is not null)
838 ) ;
839
840
841 --dbms_output.put_line(' pkaligot : before entering the insert/update block' );
842 --dbms_output.put_line(' pkaligot : 98 : arg_query_id :' || arg_query_id );
843 --dbms_output.put_line(' pkaligot : 99 : v_item_id.COUNT :' || v_item_id.COUNT );
844
845 if v_item_id is not null AND v_item_id.COUNT > 0 then
846
847 --dbms_output.put_line(' pkaligot : output 100' );
848
849 FOR i in v_item_id.FIRST..v_item_id.LAST LOOP
850 -- get the corresponding dates - start and end of bucket
851
852 -- here need to go through each and every row in every quantity bucket
853 -- and then make the change.
854 -- one way to do this
855 -- get all the transid (along with key_date) required for that row
856 -- while processing the row - check the date of each transaction
857 -- to figure if it is in that bucket.
858 -- another way is to go row wise and then col wise for each bucket col.
859 -- NOTE : if trans id was not found while seting the update array and the old qnty is 0,
860 -- then it is a new record.
861
862 --dbms_output.put_line(' pkaligot : output 101' );
863
864
865 if v_old_qty1(i) IS NOT NULL AND v_qty1(i) <> v_old_qty1(i) then
866 set_for_update(v_qty1(i), v_old_qty1(i), v_bkt1, v_bkt2, i );
867 --dbms_output.put_line(' pkaligot 1 : insert_rec: ' || insert_rec);
868 if insert_rec = 1 AND v_old_qty1(i) = 0 then
869 set_for_insert(i,v_bkt1,v_qty1(i)) ;
870 --dbms_output.put_line(' pkaligot : in qty1 : insert_rec');
871 insert_rec := 0;
872 end if;
873 elsif v_old_qty1(i) IS NULL AND v_qty1(i) IS NOT NULL then -- new record.
874 -- the new records for insert added to insert array
875 set_for_insert(i,v_bkt1,v_qty1(i)) ;
876 end if;
877
878 if v_old_qty2(i) IS NOT NULL AND v_qty2(i) <> v_old_qty2(i) then
882 insert_rec := 0;
879 set_for_update(v_qty2(i), v_old_qty2(i), v_bkt2, v_bkt3, i );
880 if insert_rec = 1 AND v_old_qty2(i) = 0 then
881 set_for_insert(i,v_bkt2,v_qty2(i)) ;
883 end if;
884 --dbms_output.put_line(' pkaligot : in qty2 update block' );
885 elsif v_old_qty2(i) IS NULL AND v_qty2(i) IS NOT NULL then -- new record.
886 set_for_insert(i,v_bkt2,v_qty2(i)) ;
887 --dbms_output.put_line(' pkaligot : in qty2 insert block' );
888 end if;
889
890 if v_old_qty3(i) IS NOT NULL AND v_qty3(i) <> v_old_qty3(i) then
891 set_for_update(v_qty3(i), v_old_qty3(i), v_bkt3, v_bkt4, i );
892 if insert_rec = 1 AND v_old_qty3(i) = 0 then
893 set_for_insert(i,v_bkt3,v_qty3(i)) ;
894 insert_rec := 0;
895 end if;
896 --dbms_output.put_line(' pkaligot : in qty3 update block' );
897 elsif v_old_qty3(i) IS NULL AND v_qty3(i) IS NOT NULL then -- new record.
898 set_for_insert(i,v_bkt3,v_qty3(i)) ;
899 --dbms_output.put_line(' pkaligot : in qty3 insert block' );
900 end if;
901
902 if v_old_qty4(i) IS NOT NULL AND v_qty4(i) <> v_old_qty4(i) then
903 set_for_update(v_qty4(i), v_old_qty4(i),v_bkt4, v_bkt5,i );
904 if insert_rec = 1 AND v_old_qty4(i) = 0 then
905 set_for_insert(i,v_bkt4,v_qty4(i)) ;
906 insert_rec := 0;
907 end if;
908 elsif v_old_qty4(i) IS NULL AND v_qty4(i) IS NOT NULL then -- new record.
909 set_for_insert(i,v_bkt4,v_qty4(i)) ;
910 end if;
911
912 if v_old_qty5(i) IS NOT NULL AND v_qty5(i) <> v_old_qty5(i) then
913 set_for_update(v_qty5(i), v_old_qty5(i), v_bkt5, v_bkt6,i );
914 if insert_rec = 1 AND v_old_qty5(i) = 0 then
915 set_for_insert(i,v_bkt5,v_qty5(i)) ;
916 insert_rec := 0;
917 end if;
918 elsif v_old_qty5(i) IS NULL AND v_qty5(i) IS NOT NULL then -- new record.
919 set_for_insert(i,v_bkt5,v_qty5(i)) ;
920 end if;
921
922 if v_old_qty6(i) IS NOT NULL AND v_qty6(i) <> v_old_qty6(i) then
923 set_for_update(v_qty6(i), v_old_qty6(i), v_bkt6, v_bkt7,i );
924 if insert_rec = 1 AND v_old_qty6(i) = 0 then
925 set_for_insert(i,v_bkt6,v_qty6(i)) ;
926 insert_rec := 0;
927 end if;
928 elsif v_old_qty6(i) IS NULL AND v_qty6(i) IS NOT NULL then -- new record.
929 set_for_insert(i,v_bkt6,v_qty6(i)) ;
930 end if;
931
932 if v_old_qty7(i) IS NOT NULL AND v_qty7(i) <> v_old_qty7(i) then
933 set_for_update(v_qty7(i), v_old_qty7(i),v_bkt7, v_bkt8, i );
934 if insert_rec = 1 AND v_old_qty7(i) = 0 then
935 set_for_insert(i,v_bkt7,v_qty7(i)) ;
936 insert_rec := 0;
937 end if;
938 elsif v_old_qty7(i) IS NULL AND v_qty7(i) IS NOT NULL then -- new record.
939 set_for_insert(i,v_bkt7,v_qty7(i)) ;
940 end if;
941
942 if v_old_qty8(i) IS NOT NULL AND v_qty8(i) <> v_old_qty8(i) then
943 set_for_update(v_qty8(i), v_old_qty8(i),v_bkt8, v_bkt9, i );
944 if insert_rec = 1 AND v_old_qty8(i) = 0 then
945 set_for_insert(i,v_bkt8,v_qty8(i)) ;
946 insert_rec := 0;
947 end if;
948 elsif v_old_qty8(i) IS NULL AND v_qty8(i) IS NOT NULL then -- new record.
949 set_for_insert(i,v_bkt8,v_qty8(i)) ;
950 end if;
951
952 if v_old_qty9(i) IS NOT NULL AND v_qty9(i) <> v_old_qty9(i) then
953 set_for_update(v_qty9(i), v_old_qty9(i),v_bkt9, v_bkt10, i );
954 if insert_rec = 1 AND v_old_qty9(i) = 0 then
955 set_for_insert(i,v_bkt9,v_qty9(i)) ;
956 insert_rec := 0;
957 end if;
958 elsif v_old_qty9(i) IS NULL AND v_qty9(i) IS NOT NULL then -- new record.
959 set_for_insert(i,v_bkt9,v_qty9(i)) ;
960 end if;
961
962 if v_old_qty10(i) IS NOT NULL AND v_qty10(i) <> v_old_qty10(i) then
963 set_for_update(v_qty10(i), v_old_qty10(i),v_bkt10, v_bkt11, i );
964 if insert_rec = 1 AND v_old_qty10(i) = 0 then
965 set_for_insert(i,v_bkt10,v_qty10(i)) ;
966 insert_rec := 0;
967 end if;
968 elsif v_old_qty10(i) IS NULL AND v_qty10(i) IS NOT NULL then -- new record.
969 set_for_insert(i,v_bkt10,v_qty10(i)) ;
970 end if;
971
972 if v_old_qty11(i) IS NOT NULL AND v_qty11(i) <> v_old_qty11(i) then
973 set_for_update(v_qty11(i), v_old_qty11(i),v_bkt11, v_bkt12,i );
974 if insert_rec = 1 AND v_old_qty11(i) = 0 then
975 set_for_insert(i,v_bkt11,v_qty11(i)) ;
976 insert_rec := 0;
977 end if;
978 elsif v_old_qty11(i) IS NULL AND v_qty11(i) IS NOT NULL then -- new record.
979 set_for_insert(i,v_bkt11,v_qty11(i)) ;
980 end if;
981
982 if v_old_qty12(i) IS NOT NULL AND v_qty12(i) <> v_old_qty12(i) then
986 insert_rec := 0;
983 set_for_update(v_qty12(i), v_old_qty12(i),v_bkt12, v_bkt13,i );
984 if insert_rec = 1 AND v_old_qty12(i) = 0 then
985 set_for_insert(i,v_bkt12,v_qty12(i)) ;
987 end if;
988 elsif v_old_qty12(i) IS NULL AND v_qty12(i) IS NOT NULL then -- new record.
989 set_for_insert(i,v_bkt12,v_qty12(i)) ;
990 end if;
991
992 if v_old_qty13(i) IS NOT NULL AND v_qty13(i) <> v_old_qty13(i) then
993 set_for_update(v_qty13(i), v_old_qty13(i),v_bkt13, v_bkt14,i );
994 if insert_rec = 1 AND v_old_qty13(i) = 0 then
995 set_for_insert(i,v_bkt13,v_qty13(i)) ;
996 insert_rec := 0;
997 end if;
998 elsif v_old_qty13(i) IS NULL AND v_qty13(i) IS NOT NULL then -- new record.
999 set_for_insert(i,v_bkt13,v_qty13(i)) ;
1000 end if;
1001
1002 if v_old_qty14(i) IS NOT NULL AND v_qty14(i) <> v_old_qty14(i) then
1003 set_for_update(v_qty14(i), v_old_qty14(i),v_bkt14, v_bkt15,i );
1004 if insert_rec = 1 AND v_old_qty14(i) = 0 then
1005 set_for_insert(i,v_bkt14,v_qty14(i)) ;
1006 insert_rec := 0;
1007 end if;
1008 elsif v_old_qty14(i) IS NULL AND v_qty14(i) IS NOT NULL then -- new record.
1009 set_for_insert(i,v_bkt14,v_qty14(i)) ;
1010 end if;
1011
1012 if v_old_qty15(i) IS NOT NULL AND v_qty15(i) <> v_old_qty15(i) then
1013 set_for_update(v_qty15(i), v_old_qty15(i), v_bkt15, v_bkt16, i );
1014 if insert_rec = 1 AND v_old_qty15(i) = 0 then
1015 set_for_insert(i,v_bkt15,v_qty15(i)) ;
1016 insert_rec := 0;
1017 end if;
1018 elsif v_old_qty15(i) IS NULL AND v_qty15(i) IS NOT NULL then -- new record.
1019 set_for_insert(i,v_bkt15,v_qty15(i)) ;
1020 end if;
1021
1022 if v_old_qty16(i) IS NOT NULL AND v_qty16(i) <> v_old_qty16(i) then
1023 set_for_update(v_qty16(i), v_old_qty16(i), v_bkt16, v_bkt17,i );
1024 if insert_rec = 1 AND v_old_qty16(i) = 0 then
1025 set_for_insert(i,v_bkt16,v_qty16(i)) ;
1026 insert_rec := 0;
1027 end if;
1028 elsif v_old_qty16(i) IS NULL AND v_qty16(i) IS NOT NULL then -- new record.
1029 set_for_insert(i,v_bkt16,v_qty16(i)) ;
1030
1031 end if;
1032
1033 if v_old_qty17(i) IS NOT NULL AND v_qty17(i) <> v_old_qty17(i) then
1034 set_for_update(v_qty17(i), v_old_qty17(i), v_bkt17, v_bkt18, i );
1035 if insert_rec = 1 AND v_old_qty17(i) = 0 then
1036 set_for_insert(i,v_bkt17,v_qty17(i)) ;
1037 insert_rec := 0;
1038 end if;
1039
1040 elsif v_old_qty17(i) IS NULL AND v_qty17(i) IS NOT NULL then -- new record.
1041 set_for_insert(i,v_bkt17,v_qty17(i)) ;
1042
1043 end if;
1044
1045 if v_old_qty18(i) IS NOT NULL AND v_qty18(i) <> v_old_qty18(i) then
1046 set_for_update(v_qty18(i), v_old_qty18(i), v_bkt18, v_bkt19,i );
1047 if insert_rec = 1 AND v_old_qty18(i) = 0 then
1048 set_for_insert(i,v_bkt18,v_qty18(i)) ;
1049 insert_rec := 0;
1050 end if;
1051
1052 elsif v_old_qty18(i) IS NULL AND v_qty18(i) IS NOT NULL then -- new record.
1053 set_for_insert(i,v_bkt18,v_qty18(i)) ;
1054
1055 end if;
1056
1057 if v_old_qty19(i) IS NOT NULL AND v_qty19(i) <> v_old_qty19(i) then
1058 set_for_update(v_qty19(i), v_old_qty19(i), v_bkt19, v_bkt20, i );
1059 if insert_rec = 1 AND v_old_qty19(i) = 0 then
1060 set_for_insert(i,v_bkt19,v_qty19(i)) ;
1061 insert_rec := 0;
1062 end if;
1063
1064 elsif v_old_qty19(i) IS NULL AND v_qty19(i) IS NOT NULL then -- new record.
1065 set_for_insert(i,v_bkt19,v_qty19(i)) ;
1066
1067 end if;
1068
1069 if v_old_qty20(i) IS NOT NULL AND v_qty20(i) <> v_old_qty20(i) then
1070 set_for_update(v_qty20(i), v_old_qty20(i), v_bkt20, v_bkt21, i );
1071 if insert_rec = 1 AND v_old_qty20(i) = 0 then
1072 set_for_insert(i,v_bkt20,v_qty20(i)) ;
1073 insert_rec := 0;
1074 end if;
1075
1076 elsif v_old_qty20(i) IS NULL AND v_qty20(i) IS NOT NULL then -- new record.
1077 set_for_insert(i,v_bkt20,v_qty20(i)) ;
1078
1079 end if;
1080
1081 if v_old_qty21(i) IS NOT NULL AND v_qty21(i) <> v_old_qty21(i) then
1082 set_for_update(v_qty21(i), v_old_qty21(i), v_bkt21, v_bkt22, i );
1083 if insert_rec = 1 AND v_old_qty21(i) = 0 then
1084 set_for_insert(i,v_bkt21,v_qty21(i)) ;
1085 insert_rec := 0;
1086 end if;
1087
1088 elsif v_old_qty21(i) IS NULL AND v_qty21(i) IS NOT NULL then -- new record.
1089 set_for_insert(i,v_bkt21,v_qty21(i)) ;
1090
1091 end if;
1092
1093 if v_old_qty22(i) IS NOT NULL AND v_qty22(i) <> v_old_qty22(i) then
1094 set_for_update(v_qty22(i), v_old_qty22(i), v_bkt22, v_bkt23, i );
1098 end if;
1095 if insert_rec = 1 AND v_old_qty22(i) = 0 then
1096 set_for_insert(i,v_bkt22,v_qty22(i)) ;
1097 insert_rec := 0;
1099
1100 elsif v_old_qty22(i) IS NULL AND v_qty22(i) IS NOT NULL then -- new record.
1101 set_for_insert(i,v_bkt22,v_qty22(i)) ;
1102
1103 end if;
1104
1105 if v_old_qty23(i) IS NOT NULL AND v_qty23(i) <> v_old_qty23(i) then
1106 set_for_update(v_qty23(i), v_old_qty23(i), v_bkt23, v_bkt24, i );
1107 if insert_rec = 1 AND v_old_qty23(i) = 0 then
1108 set_for_insert(i,v_bkt23,v_qty23(i)) ;
1109 insert_rec := 0;
1110 end if;
1111
1112 elsif v_old_qty23(i) IS NULL AND v_qty23(i) IS NOT NULL then -- new record.
1113 set_for_insert(i,v_bkt23,v_qty23(i)) ;
1114
1115 end if;
1116
1117 if v_old_qty24(i) IS NOT NULL AND v_qty24(i) <> v_old_qty24(i) then
1118 set_for_update(v_qty24(i), v_old_qty24(i), v_bkt24, v_bkt25, i );
1119 if insert_rec = 1 AND v_old_qty24(i) = 0 then
1120 set_for_insert(i,v_bkt24,v_qty24(i)) ;
1121 insert_rec := 0;
1122 end if;
1123
1124 elsif v_old_qty24(i) IS NULL AND v_qty24(i) IS NOT NULL then -- new record.
1125 set_for_insert(i,v_bkt24,v_qty24(i)) ;
1126
1127 end if;
1128
1129 if v_old_qty25(i) IS NOT NULL AND v_qty25(i) <> v_old_qty25(i) then
1130 set_for_update(v_qty25(i), v_old_qty25(i), v_bkt25, v_bkt26, i );
1131 if insert_rec = 1 AND v_old_qty25(i) = 0 then
1132 set_for_insert(i,v_bkt25,v_qty25(i)) ;
1133 insert_rec := 0;
1134 end if;
1135
1136 elsif v_old_qty25(i) IS NULL AND v_qty25(i) IS NOT NULL then -- new record.
1137 set_for_insert(i,v_bkt25,v_qty25(i)) ;
1138
1139 end if;
1140
1141 if v_old_qty26(i) IS NOT NULL AND v_qty26(i) <> v_old_qty26(i) then
1142 set_for_update(v_qty26(i), v_old_qty26(i), v_bkt26, v_bkt27, i );
1143 if insert_rec = 1 AND v_old_qty26(i) = 0 then
1144 set_for_insert(i,v_bkt26,v_qty26(i)) ;
1145 insert_rec := 0;
1146 end if;
1147
1148 elsif v_old_qty26(i) IS NULL AND v_qty26(i) IS NOT NULL then -- new record.
1149 set_for_insert(i,v_bkt26,v_qty26(i)) ;
1150
1151 end if;
1152
1153 if v_old_qty27(i) IS NOT NULL AND v_qty27(i) <> v_old_qty27(i) then
1154 set_for_update(v_qty27(i), v_old_qty27(i), v_bkt27, v_bkt28, i );
1155 if insert_rec = 1 AND v_old_qty27(i) = 0 then
1156 set_for_insert(i,v_bkt27,v_qty27(i)) ;
1157 insert_rec := 0;
1158 end if;
1159
1160 elsif v_old_qty27(i) IS NULL AND v_qty27(i) IS NOT NULL then -- new record.
1161 set_for_insert(i,v_bkt27,v_qty27(i)) ;
1162
1163 end if;
1164
1165 if v_old_qty28(i) IS NOT NULL AND v_qty28(i) <> v_old_qty28(i) then
1166 set_for_update(v_qty28(i), v_old_qty28(i), v_bkt28, v_bkt29, i );
1167 if insert_rec = 1 AND v_old_qty28(i) = 0 then
1168 set_for_insert(i,v_bkt28,v_qty28(i)) ;
1169 insert_rec := 0;
1170 end if;
1171
1172 elsif v_old_qty28(i) IS NULL AND v_qty28(i) IS NOT NULL then -- new record.
1173 set_for_insert(i,v_bkt28,v_qty28(i)) ;
1174
1175 end if;
1176
1177 if v_old_qty29(i) IS NOT NULL AND v_qty29(i) <> v_old_qty29(i) then
1178 set_for_update(v_qty29(i), v_old_qty29(i), v_bkt29, v_bkt30, i );
1179 if insert_rec = 1 AND v_old_qty29(i) = 0 then
1180 set_for_insert(i,v_bkt29,v_qty29(i)) ;
1181 insert_rec := 0;
1182 end if;
1183
1184 elsif v_old_qty29(i) IS NULL AND v_qty29(i) IS NOT NULL then -- new record.
1185 set_for_insert(i,v_bkt29,v_qty29(i)) ;
1186
1187 end if;
1188
1189 if v_old_qty30(i) IS NOT NULL AND v_qty30(i) <> v_old_qty30(i) then
1190 set_for_update(v_qty30(i), v_old_qty30(i),v_bkt30, v_bkt31, i );
1191 if insert_rec = 1 AND v_old_qty30(i) = 0 then
1192 set_for_insert(i,v_bkt30,v_qty30(i)) ;
1193 insert_rec := 0;
1194 end if;
1195
1196 elsif v_old_qty30(i) IS NULL AND v_qty30(i) IS NOT NULL then -- new record.
1197 set_for_insert(i,v_bkt30,v_qty30(i)) ;
1198
1199 end if;
1200
1201 if v_old_qty31(i) IS NOT NULL AND v_qty31(i) <> v_old_qty31(i) then
1202 set_for_update(v_qty31(i), v_old_qty31(i),v_bkt31, v_bkt32,i );
1203 if insert_rec = 1 AND v_old_qty31(i) = 0 then
1204 set_for_insert(i,v_bkt31,v_qty31(i)) ;
1205 insert_rec := 0;
1206 end if;
1207
1208 elsif v_old_qty31(i) IS NULL AND v_qty31(i) IS NOT NULL then -- new record.
1209 set_for_insert(i,v_bkt31,v_qty31(i)) ;
1210
1211 end if;
1212
1213 if v_old_qty32(i) IS NOT NULL AND v_qty32(i) <> v_old_qty32(i) then
1214 set_for_update(v_qty32(i), v_old_qty32(i), v_bkt32, v_bkt33,i );
1218 end if;
1215 if insert_rec = 1 AND v_old_qty32(i) = 0 then
1216 set_for_insert(i,v_bkt32,v_qty32(i)) ;
1217 insert_rec := 0;
1219
1220 elsif v_old_qty32(i) IS NULL AND v_qty32(i) IS NOT NULL then -- new record.
1221 set_for_insert(i,v_bkt32,v_qty32(i)) ;
1222
1223 end if;
1224
1225 if v_old_qty33(i) IS NOT NULL AND v_qty33(i) <> v_old_qty33(i) then
1226 set_for_update(v_qty33(i), v_old_qty33(i), v_bkt33, v_bkt34, i );
1227 if insert_rec = 1 AND v_old_qty33(i) = 0 then
1228 set_for_insert(i,v_bkt33,v_qty33(i)) ;
1229 insert_rec := 0;
1230 end if;
1231
1232 elsif v_old_qty33(i) IS NULL AND v_qty33(i) IS NOT NULL then -- new record.
1233 set_for_insert(i,v_bkt33,v_qty33(i)) ;
1234
1235 end if;
1236
1237 if v_old_qty34(i) IS NOT NULL AND v_qty34(i) <> v_old_qty34(i) then
1238 set_for_update(v_qty34(i), v_old_qty34(i), v_bkt34, v_bkt35, i );
1239 if insert_rec = 1 AND v_old_qty34(i) = 0 then
1240 set_for_insert(i,v_bkt34,v_qty34(i)) ;
1241 insert_rec := 0;
1242 end if;
1243
1244 elsif v_old_qty34(i) IS NULL AND v_qty34(i) IS NOT NULL then -- new record.
1245 set_for_insert(i,v_bkt34,v_qty34(i)) ;
1246
1247 end if;
1248
1249 if v_old_qty35(i) IS NOT NULL AND v_qty35(i) <> v_old_qty35(i) then
1250 set_for_update(v_qty35(i), v_old_qty35(i), v_bkt35, v_bkt36, i );
1251 if insert_rec = 1 AND v_old_qty35(i) = 0 then
1252 set_for_insert(i,v_bkt35,v_qty35(i)) ;
1253 insert_rec := 0;
1254 end if;
1255
1256 elsif v_old_qty35(i) IS NULL AND v_qty35(i) IS NOT NULL then -- new record.
1257 set_for_insert(i,v_bkt35,v_qty35(i)) ;
1258
1259 end if;
1260
1261 if v_old_qty36(i) IS NOT NULL AND v_qty36(i) <> v_old_qty36(i) then
1262 set_for_update(v_qty36(i), v_old_qty36(i), v_bkt36, v_last_bkt, i );
1263 if insert_rec = 1 AND v_old_qty36(i) = 0 then
1264 set_for_insert(i,v_bkt36,v_qty36(i)) ;
1265 insert_rec := 0;
1266 end if;
1267
1268 elsif v_old_qty36(i) IS NULL AND v_qty36(i) IS NOT NULL then -- new record.
1269 set_for_insert(i,v_bkt36,v_qty36(i)) ;
1270
1271 end if;
1272 -- get the transactionids.
1273 -- if none found then it is a new record.
1274
1275 END LOOP;
1276
1277 end if;
1278
1279
1280
1281 BEGIN
1282 SELECT fnd_global.user_id
1283 INTO v_user_id
1284 FROM dual;
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 v_user_id := -1;
1288 END;
1289
1290 --dbms_output.put_line(' pkaligot : 199 : before update sup dem entries block: trans count: ' || v_upd_trans_id.COUNT);
1291 if v_upd_trans_id is not null AND v_upd_trans_id.COUNT > 0 and proceed = 0 then
1292 --dbms_output.put_line(' pkaligot : 200 : update sup dem entries block: trans count: ' || v_upd_trans_id.COUNT );
1293 -- update into msc_sup_dem_entries;
1294 BEGIN
1295 FORALL i in v_upd_trans_id.FIRST..v_upd_trans_id.LAST
1296
1297 update msc_sup_dem_entries
1298 set primary_quantity = round(v_upd_p_qty(i),DECPLACES),
1299 quantity = round(v_upd_qty(i),DECPLACES),
1300 tp_quantity = round(v_upd_tp_qty(i),DECPLACES),
1301 last_refresh_number = MSC_CL_REFRESH_S.NEXTVAL,
1302 last_updated_by = v_user_id,
1303 last_update_date = sysdate
1304 where transaction_id = v_upd_trans_id(i);
1305
1306 commit;
1307 EXCEPTION
1308 when others then
1309 proceed := 1;
1310 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1311 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'update_supdem_entries', 'update ' || SQLERRM);
1312 end if;
1313 arg_err_msg := 'MSC_X_HZ_UPDATE@update ' || SQLERRM;
1314 END;
1315 end if;
1316
1317 -- insert into msc_sup_dem_entries.
1318
1319 --dbms_output.put_line(' pkaligot : 200 : v_i_item.COUNT:' || v_i_item.COUNT );
1320 --dbms_output.put_line(' pkaligot : 201 : proceed :' || proceed );
1321
1322 if v_i_item is not null AND v_i_item.COUNT > 0 and proceed = 0 then
1323 --dbms_output.put_line(' pkaligot : 202 : v_i_item.COUNT:' || v_i_item.COUNT );
1324 ----dbms_output.put_line(' pkaligot : 202 : quantity:' || v_i_qty(0) );
1325
1326 BEGIN
1327
1328 FORALL i in v_i_item_id.FIRST..v_i_item_id.LAST
1329
1330 insert into msc_sup_dem_entries
1331 (transaction_id,inventory_item_id,item_name,item_description,
1332 owner_item_name,customer_item_name,supplier_item_name,
1333 owner_item_description,customer_item_description,supplier_item_description,
1334 publisher_id,publisher_name,publisher_site_name,
1335 customer_id,customer_site_id,
1336 customer_name,customer_site_name,
1337 supplier_id,supplier_site_id,
1338 supplier_name,supplier_site_name,
1339 publisher_order_type,publisher_order_type_desc,
1340 bucket_type,bucket_type_desc,primary_uom,tp_uom_code,uom_code,
1341 primary_quantity,tp_quantity,quantity,
1342 key_date,ship_date,receipt_date,last_refresh_number,plan_id,
1343 publisher_site_id,created_by,creation_date,last_updated_by,last_update_date,
1344 ship_from_party_name,ship_from_party_id,ship_from_party_site_name,ship_from_party_site_id,
1345 ship_to_party_name,ship_to_party_id,ship_to_party_site_name,ship_to_party_site_id,
1346 sr_instance_id)
1347 values
1348 (MSC_SUP_DEM_ENTRIES_S.nextval,v_i_item_id(i),v_i_item(i),Nvl(v_i_item_desc(i),v_i_owner_item_desc(i)),
1349 v_i_owner_item(i),v_i_cust_item(i),v_i_sup_item(i),
1350 v_i_owner_item_desc(i),v_i_cust_item_desc(i),v_i_sup_item_desc(i),
1351 v_pub_id,v_pub_name,v_i_pub_site_name(i),
1352 v_i_cust_id(i),v_i_cust_site_id(i),
1353 v_i_cust(i),v_i_cust_site(i),
1354 v_i_sup_id(i),v_i_sup_site_id(i),
1355 v_i_sup(i),v_i_sup_site(i),
1356 v_i_order_type(i),v_i_order(i),
1357 v_i_bucket(i),v_i_bucket_desc(i),v_i_p_uom(i),v_i_tp_uom(i),v_i_p_uom(i),
1358 round(v_i_p_qty(i),DECPLACES),round(v_i_tp_qty(i),DECPLACES),round(v_i_qty(i),DECPLACES),
1359 v_i_date(i),v_i_date(i),v_i_date(i),MSC_CL_REFRESH_S.nextval,-1,
1360 v_i_pub_site_id(i),v_user_id,sysdate,v_user_id,sysdate,
1361 v_i_ship_from(i), v_i_ship_from_id(i), v_i_ship_from_site(i), v_i_ship_from_site_id(i),
1362 v_i_ship_to(i), v_i_ship_to_id(i), v_i_ship_to_site(i), v_i_ship_to_site_id(i), -1);
1363
1364 EXCEPTION
1365 when others then
1366 proceed := 1;
1367 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1368 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'update_supdem_entries', 'insert ' || SQLERRM);
1369 end if;
1370 arg_err_msg := 'MSC_X_HZ_UPDATE@insert ' || SQLERRM;
1371 --dbms_output.put_line(' pkaligot : 203 : ' || arg_err_msg );
1372
1373 END;
1374 end if;
1375
1376 if proceed = 1 then
1377 arg_err_msg := 'Error while updating';
1378 end if;
1379
1380
1381 EXCEPTION
1382 WHEN NO_DATA_FOUND THEN
1383 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1384 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'update_supdem_entries',SQLERRM);
1385 end if;
1386 arg_err_msg := SQLERRM;
1387
1388 WHEN OTHERS THEN
1389 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1390 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'update_supdem_entries',SQLERRM);
1391 end if;
1392 arg_err_msg := SQLERRM;
1393
1394 END;
1395
1396 END MSC_X_HZ_UPDATE;
[Home] [Help]