[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_PUBLISH
Source
1 PACKAGE BODY MSC_CL_PUBLISH AS -- body
2 /* $Header: MSCXCPB.pls 120.11.12010000.2 2008/08/21 10:25:06 hbinjola ship $ */
3
4
5 --=================
6 -- Global variables
7 --=================
8 v_oh_refresh_number NUMBER;
9 v_supply_refresh_number NUMBER;
10 v_so_refresh_number NUMBER;
11 v_suprep_refresh_number NUMBER;
12
13 G_ASN_DESC varchar2(80);
14 G_PO_DESC varchar2(80);
15 G_REQ_DESC varchar2(80);
16
17 G_SHIP_CONTROL VARCHAR2(30);
18 G_ARRIVE_CONTROL VARCHAR2(30);
19
20 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
21 IS
22 BEGIN
23
24 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
25
26 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
27 --dbms_output.put_line( pBUFF);
28
29 ELSE
30
31 -- dbms_output.put_line( pBUFF);
32 null;
33
34 END IF;
35
36 END LOG_MESSAGE;
37
38 PROCEDURE LOG_DEBUG( pBUFF IN VARCHAR2)
39 IS
40 BEGIN
41
42 IF (fnd_global.conc_request_id > 0 AND (G_MSC_DEBUG = 'Y')) THEN
43
44 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
45
46 ELSE
47 NULL;
48 -- DBMS_OUTPUT.PUT_LINE( pBUFF);
49
50 END IF;
51
52 END LOG_DEBUG;
53
54
55 -- ==========================================================================================
56 -- PROCEDURE : INITIALIZE_REFRESH_NUM
57 --
58 -- This procedure initilizes refresh numbers foreach entity. This is required for contineous
59 -- collections.
60
61 -- Refresh numbers resolved in this procedure are used in each Cursors.
62 -- ==========================================================================================
63
64 PROCEDURE INITIALIZE_REFRESH_NUM (p_refresh_number NUMBER,
65 p_lrtype VARCHAR2 ,
66 p_po_enabled_flag NUMBER,
67 p_oh_enabled_flag NUMBER,
68 p_so_enabled_flag NUMBER,
69 p_asl_enabled_flag NUMBER,
70 p_sup_resp_flag NUMBER,
71 p_po_sn_flag NUMBER,
72 p_oh_sn_flag NUMBER,
73 p_so_sn_flag NUMBER,
74 p_suprep_sn_flag NUMBER) IS
75 BEGIN
76
77 -- ==============================
78 -- Initialize the refresh numbers
79 -- ==============================
80 v_oh_refresh_number := -1;
81 v_supply_refresh_number := -1;
82 v_so_refresh_number := -1;
83 v_suprep_refresh_number := -1;
84
85 IF (p_lrtype = 'T') THEN
86
87 IF (nvl(p_po_enabled_flag, SYS_NO) = SYS_YES AND
88 nvl(p_PO_SN_FLAG, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL
89 ) THEN
90
91 v_supply_refresh_number := p_refresh_number;
92
93 END IF;
94
95 IF (nvl(p_oh_enabled_flag, SYS_NO) = SYS_YES AND
96 nvl(p_OH_SN_FLAG, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL
97 ) THEN
98
99 v_oh_refresh_number := p_refresh_number;
100
101 END IF;
102
103 IF (nvl( p_so_enabled_flag, SYS_NO) = SYS_YES AND
104 nvl(p_SO_SN_FLAG, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL
105 ) THEN
106
107 v_so_refresh_number := p_refresh_number;
108
109 END IF;
110
111 IF (nvl( p_sup_resp_flag, SYS_NO) = SYS_YES AND
112 nvl(p_suprep_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL
113 ) THEN
114
115 v_suprep_refresh_number := p_refresh_number;
116
117 END IF;
118
119 LOG_DEBUG (' Refresh Number for supply entities :' ||v_supply_refresh_number);
120 LOG_DEBUG (' Refresh Number for onHand entities :' ||v_oh_refresh_number);
121 LOG_DEBUG (' Refresh Number for Sales Order entities :' ||v_so_refresh_number);
122 LOG_DEBUG (' Refresh Number for Supplier Responses :' ||v_suprep_refresh_number);
123
124 END IF; -- (v_lrtype = 'T')
125
126 END INITIALIZE_REFRESH_NUM ;
127
128 -- ===============================================
129 -- Procedure : PUBLISH
130 --
131 -- ===============================================
132 PROCEDURE PUBLISH (ERRBUF OUT NOCOPY VARCHAR2,
133 RETCODE OUT NOCOPY NUMBER,
134 p_sr_instance_id NUMBER,
135 p_user_id NUMBER,
136 p_po_enabled_flag NUMBER,
137 p_oh_enabled_flag NUMBER,
138 p_so_enabled_flag NUMBER,
139 p_asl_enabled_flag NUMBER,
140 p_sup_resp_flag NUMBER,
141 p_po_sn_flag NUMBER,
142 p_oh_sn_flag NUMBER,
143 p_so_sn_flag NUMBER,
144 p_suprep_sn_flag NUMBER) IS
145 v_refresh_number NUMBER;
146 v_apps_ver NUMBER;
147 v_lrtype varchar2(1);
148 v_so_lrtype varchar2(1);
149
150 v_sr_dblink VARCHAR2(128);
151
152 type varcharlist is table of varchar2(2);
153 t_status_code varcharlist := varcharlist();
154 t_ins_status_code varcharlist := varcharlist();
155 lv_sql_stmt varchar2(14000);
156 lv_sql_stmt1 varchar2(14000);
157 lv_sql_stmt2 varchar2(14000);
158 lv_sql_stmt3 varchar2(14000);
159 lv_sql_stmt4 varchar2(14000);
160
161 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type -Cursor variable
162 CUR_DELIVERY_ASN CurTyp;
163
164
165 G_NULL_STRING VARCHAR2(10) := '-234567';
166 G_PLAN_ID NUMBER := -1;
167 G_SR_INSTANCE_ID NUMBER := -1;
168 G_OEM_ID NUMBER := 1;
169 G_SR_OEM_ID NUMBER := -1;
170 G_UNALLOC_ONHAND NUMBER := 10;
171 G_ALLOC_ONHAND NUMBER := 9;
172 G_DAILY_BKT_TYPE NUMBER := 1;
173 G_MRP_ONHAND NUMBER := 18;
174 G_SUPPLIER NUMBER := 1;
175 G_CUSTOMER NUMBER := 2;
176 G_ORGANIZATION NUMBER := 3;
177 G_SITE_MAP_TYPE NUMBER := 3;
178 G_SALES_ORDER NUMBER := 14;
179 G_PO NUMBER := 13;
180 G_REQ NUMBER := 20;
181 G_ASN NUMBER := 15;
182 G_SHIP_RECEIPT NUMBER := 16;
183
184 /* PO-SHIP-DATE */
185 G_SHIP NUMBER := 1;
186 G_ARRIVAL NUMBER := 2;
187
188 /* If last collection method is Partial or Complete then
189 we need to pull all records.
190 else
191 we need to pull records where we find refresh_number = v_refresh_number
192
193 We will also need to build collections objects order to call
194 SCE Loads API.
195 */
196
197
198 CURSOR mscSupply(p_refresh_number NUMBER,
199 p_sr_instance_id NUMBER,
200 p_language_code VARCHAR2) IS
201 select --msc_sup_dem_entries_s.nextval transaction_id
202 G_SR_INSTANCE_ID sr_instance_id
203 ,G_PLAN_ID plan_id
204 ,decode(ms.order_type, 11, mcr.object_id , G_OEM_ID) publisher_id -- Bug 4395985
205 ,decode(ms.order_type, 11, mtpm1.company_key , mcsil.company_site_id) publisher_site_id
206 ,decode(ms.order_type, 11, mc1.company_name, mc.company_name) publisher_name
207 ,decode(ms.order_type, 11, mcs1.company_site_name, mcs.company_site_name) publisher_site_name
208 ,ms.inventory_item_id inventory_item_id
209 ,ms.new_order_quantity quantity
210 ,decode(ms.order_type, 1,13,
211 2,20,
212 8,16,
213 11,15) publisher_order_type
214 ,nvl(ms.new_dock_date, ( ms.new_schedule_date - nvl( mi.POSTPROCESSING_LEAD_TIME,0))) receipt_date
215 -- ,ms.new_schedule_date ship_date
216 ,mcr.object_id supplier_id
217 ,mc1.company_name supplier_name
218 ,mtpm1.company_key supplier_site_id
219 ,mcs1.company_site_name supplier_site_name
220 ,ms.purch_line_num Order_line_number
221 ,decode(instr(ms.order_number,'('),
222 0, ms.order_number,
223 substr(ms.order_number, 1, instr(ms.order_number,'(') - 1)) order_number
224 ,1 ship_to_party_id
225 ,mcsil.company_site_id ship_to_party_site_id
226 ,mc.company_name ship_to_party_name
227 ,mcs.company_site_name ship_to_party_site_name
228 ,mcr.object_id ship_from_party_id
229 ,mtpm1.company_key SHIP_FROM_PARTY_SITE_ID
230 ,mc1.company_name SHIP_FROM_PARTY_NAME
231 ,mcs1.company_site_name SHIP_FROM_PARTY_SITE_NAME
232 ,mi.item_name publisher_item_name
233 ,mi.description pub_item_description
234 ,mi.uom_code uom_code
235 ,flv.meaning publisher_order_type_desc
236 ,1 bucket_type
237 ,'Day' bucket_type_desc
238 ,'PUBLISH' comments
239 ,p_user_id created_by
240 ,ms.creation_date creation_date
241 ,p_user_id last_updated_by
242 ,ms.last_update_date last_update_date
243 ,decode(order_type, 1, ms.new_dock_date,
244 2, ms.new_dock_date,
245 8, ms.new_schedule_date,
246 11, ms.new_schedule_date) key_date
247 ,ms.supplier_id partner_id
248 ,ms.supplier_site_id partner_site_id
249 ,ms.sr_instance_id orig_sr_instance_id
250 ,ms.organization_id t_organization_id
251 ,decode(ms.order_type, 1, TRIM(substr(ms.order_number,instr(ms.order_number,'(')+1,instr(ms.order_number,'(',1,2)-2
252 - instr(ms.order_number,'('))) , decode(instr(ms.order_number,'('), 0, to_char(null),
253 substr(ms.order_number, instr(ms.order_number,'(')))) release_number
254 ,ms.NEW_ORDER_PLACEMENT_DATE order_placement_date
255 ,ms.vmi_flag
256 ,ms.acceptance_required_flag acceptance_required_flag
257 ,ms.need_by_date
258 ,ms.promised_date
259 , mi.base_item_id
260 , itm.item_name
261 , to_number(NULL) --- internal flag
262 ,mi.planner_code --Bug 4424426
263 from
264 msc_supplies ms
265 -- Table to get org equivalent company_site_id
266 ,msc_companies mc
267 ,msc_company_sites mcs
268 ,msc_company_site_id_lid mcsil
269 -- Tables to get Supplier's company_id
270 ,msc_trading_partner_maps mtpm
271 ,msc_company_relationships mcr
272 ,msc_companies mc1
273 -- Tables to get Supplier's company_site_id
274 ,msc_trading_partner_maps mtpm1
275 ,msc_company_sites mcs1
276 -- Table to get global item_id
277 , msc_system_items mi
278 , msc_items itm
279 -- Table to get order type description
280 ,fnd_lookup_values flv
281 where
282 ms.sr_instance_id = p_sr_instance_id
283 -- Get PO related transactions
284 and order_type IN (1,2,8,11)
285 -- Get only ODS records
286 and ms.plan_id = G_PLAN_ID
287 -- Join with msc_company_site_id_lid to get org equivalent company_site_id
288 and ms.organization_id = mcsil.sr_company_site_id
289 and ms.sr_instance_id = mcsil.sr_instance_id
290 and mcsil.partner_type = G_ORGANIZATION
291 and mcsil.sr_company_id = G_SR_OEM_ID
292 and mcsil.company_site_id = mcs.company_site_id
293 and mcs.company_id = mc.company_id
294 -- Make sure that only OEM's PO are published
295 and mcs.company_id = G_OEM_ID
296 -- Join with msc_system_items to get Item related information
297 and ms.inventory_item_id = mi.inventory_item_id
298 and ms.organization_id = mi.organization_id
299 and ms.sr_instance_id = mi.sr_instance_id
300 and ms.plan_id = mi.plan_id
301 and itm.inventory_item_id (+)= mi.base_item_id
302 and mi.inventory_planning_code <> 7 --- vmi
303 -- Get the Supplier's company_id
304 and ms.supplier_id = mtpm.tp_key
305 and mtpm.map_type = 1
306 and mtpm.company_key = mcr.relationship_id
307 and mcr.object_id = mc1.company_id
308 -- Get the supplier's company_site_id. Use Outer joint
309 -- with msc_trading_partner_maps since some order types
310 -- supplier site is optional
311 and nvl(ms.supplier_site_id, -99) = mtpm1.tp_key
312 and mtpm1.map_type = 3
313 and mtpm1.company_key = mcs1.company_site_id
314 -- Get the order type description
315 and decode(ms.order_type, 1,13, 2,20, 8,16, 11,15) = flv.lookup_code
316 -- and decode(ms.order_type, 1,13, 2,20, 8,16, 11,15, 12,16) = flv.lookup_code
317 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
318 and flv.language = p_language_code
319 -- Get the rows according to last collection metnod
320 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
321 , 'P', nvl(p_refresh_number, -1)
322 , 'I', p_refresh_number
323 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
324 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1)))
325 UNION ALL /* Get internal reqs for customer vmi enabled items in mod orgs */
326 select
327 G_SR_INSTANCE_ID sr_instance_id
328 ,G_PLAN_ID plan_id
329 ,G_OEM_ID publisher_id
330 ,mcs2.company_site_id publisher_site_id
331 ,mc2.company_name publisher_name
332 ,mcs2.company_site_name publisher_site_name
333 ,ms.inventory_item_id inventory_item_id
334 ,ms.new_order_quantity quantity
335 , 20 publisher_order_type
336 ,nvl(ms.new_dock_date, ( ms.new_schedule_date - nvl( mi.POSTPROCESSING_LEAD_TIME,0))) receipt_date
337 -- ,ms.new_schedule_date ship_date
338 , G_OEM_ID supplier_id
339 ,mc2.company_name supplier_name
340 ,mcs2.company_site_id supplier_site_id
341 ,mcs2.company_site_name supplier_site_name
342 ,ms.purch_line_num Order_line_number
343 ,decode(instr(ms.order_number,'('),
344 0, ms.order_number,
345 substr(ms.order_number, 1, instr(ms.order_number,'(') - 1)) order_number
346 ,mc.company_id ship_to_party_id
347 ,mcs.company_site_id ship_to_party_site_id
348 ,mc.company_name ship_to_party_name
349 ,mcs.company_site_name ship_to_party_site_name
350 ,G_OEM_ID ship_from_party_id
351 ,mcs2.company_site_id SHIP_FROM_PARTY_SITE_ID
352 ,mc2.company_name SHIP_FROM_PARTY_NAME
353 ,mcs2.company_site_name SHIP_FROM_PARTY_SITE_NAME
354 ,mi.item_name publisher_item_name
355 ,mi.description pub_item_description
356 ,mi.uom_code uom_code
357 ,flv.meaning publisher_order_type_desc
358 ,1 bucket_type
359 ,'Day' bucket_type_desc
360 ,'PUBLISH' comments
361 ,p_user_id created_by
362 ,ms.creation_date creation_date
363 ,p_user_id last_updated_by
364 ,ms.last_update_date last_update_date
365 ,ms.new_dock_date key_date
366 ,to_number(NULL) partner_id
367 ,to_number(NULL) partner_site_id
368 ,ms.sr_instance_id orig_sr_instance_id
369 ,ms.organization_id t_organization_id
370 ,decode(instr(order_number,'('),
371 0, to_char(null),
372 substr(order_number, instr(order_number,'('))) release_number
373 ,ms.NEW_ORDER_PLACEMENT_DATE order_placement_date
374 ,ms.vmi_flag
375 ,ms.acceptance_required_flag acceptance_required_flag
376 ,ms.need_by_date
377 ,ms.promised_date
378 ,to_number(null) -- base_item_id
379 ,to_char(null) -- base item_name
380 , SYS_YES
381 ,mi.planner_code --Bug 4424426
382 from
383 msc_supplies ms
384 , msc_trading_partners mtp
385 -- Table to get customer/customer site
386 ,msc_companies mc
387 ,msc_company_sites mcs
388 , msc_trading_partner_maps map1
389 -- Tables to get Supplier Site
390 ,msc_trading_partner_maps map2
391 , msc_trading_partners mtp2
392 ,msc_company_sites mcs2
393 , msc_companies mc2
394 -- Table to get global item_id
395 ,msc_system_items mi
396 -- Table to get order type description
397 ,fnd_lookup_values flv
398 where
399 ms.sr_instance_id = p_sr_instance_id
400 -- Get Internal Reqs
401 and ms.order_type = 2
402 and ms.plan_id = -1
403 and ms.source_organization_id is not null
404 and ms.source_organization_id <> ms.organization_id
405 and ms.supplier_id is null
406 and ms.supplier_site_id is null
407 ----Get only reqs in customer modelled orgs with vmi enabled
408 and ms.organization_id = mtp.sr_tp_id
409 and ms.sr_instance_id = mtp.sr_instance_id
410 and mtp.partner_type = 3
411 and mtp.modeled_customer_id is not null
412 and mtp.modeled_customer_site_id is not null
413 -- Get only ODS records
414 and ms.plan_id = G_PLAN_ID
415 -- Get the customer customer site
416 and mtp.modeled_customer_site_id = map1.tp_key
417 and map1.map_type = 3
418 and map1.company_key = mcs.company_site_id
419 and mc.company_id = mcs.company_id
420 -- Get the supplier site id
421 and ms.source_organization_id = mtp2.sr_tp_id
422 and ms.source_sr_instance_id = mtp2.sr_instance_id
423 and mtp2.partner_type = 3
424 and mtp2.partner_id = map2.tp_key
425 and map2.map_type = 2
426 and map2.company_key = mcs2.company_site_id
427 and mc2.company_id = mcs2.company_id
428 -- Join with msc_system_items to get Item related information
429 and ms.inventory_item_id = mi.inventory_item_id
430 and ms.organization_id = mi.organization_id
431 and ms.sr_instance_id = mi.sr_instance_id
432 and ms.plan_id = mi.plan_id
433 and mi.inventory_planning_code = 7 --- vmi
434 -- Get the order type description
435 and flv.lookup_code = decode(ms.order_type,2,20) -- Requisition
436 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
437 and flv.language = p_language_code
438 and flv.lookup_code = 20
439 -- Get the rows according to last collection metnod
440 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
441 , 'P', nvl(p_refresh_number, -1)
442 , 'I', p_refresh_number
443 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
444 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1)));
445
446 CURSOR allocOnhand(p_refresh_number NUMBER,
447 p_sr_instance_id NUMBER,
448 p_language_code VARCHAR2) IS
449 select
450 G_PLAN_ID PLAN_ID,
451 G_SR_INSTANCE_ID SR_INSTANCE_ID,
452 G_OEM_ID PUBLISHER_ID,
453 mcs.company_site_id PUBLISHER_SITE_ID,
454 mc.company_name PUBLISHER_NAME,
455 mcs.company_site_name PUBLISHER_SITE_NAME,
456 ms.inventory_item_id INVENTORY_ITEM_ID,
457 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
458 'PUBLISH' COMMENTS,
459 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
460 mc1.company_id SUPPLIER_ID,
461 mc1.company_name SUPPLIER_NAME,
462 mtpm.company_key SUPPLIER_SITE_ID,
463 mcs1.company_site_name SUPPLIER_SITE_NAME,
464 G_DAILY_BKT_TYPE BUCKET_TYPE,
465 mi.item_name PUBLISHER_ITEM_NAME,
466 mi.description PUB_ITEM_DESCRIPTIION ,
467 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
468 flv.meaning TP_ORDER_TYPE_DESC,
469 'Day' BUCKET_TYPE_DESC,
470 mi.uom_code UOM_CODE,
471 mi.uom_code PRIMARY_UOM,
472 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
473 mtps.partner_id PARTNER_ID,
474 mtps.partner_site_id PRATNER_SITE_ID,
475 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
476 ms.organization_id ORGANIZATION_ID
477 , ms.vmi_flag VMI_FLAG
478 ,G_SUPPLIER ALLOCATION_TYPE
479 , mi.base_item_id BASE_ITEM_ID
480 , itm.item_name BASE_ITEM_NAME
481 ,mi.planner_code PLANNER_CODE --Bug 4424426
482 from msc_supplies ms
483 --========================================
484 -- Tables to get Publisher's organization_id
485 --========================================
486 , msc_company_site_id_lid mcsil
487 , msc_company_sites mcs
488 , msc_companies mc
489 --========================================
490 -- Tables to get Supplier's information
491 --========================================
492 , msc_trading_partner_sites mtps
493 , msc_trading_partner_maps mtpm
494 , msc_company_sites mcs1
495 , msc_companies mc1
496 --========================================
497 -- Tables to get Item information
498 --========================================
499 , msc_system_items mi
500 , msc_items itm
501 --==================================
502 -- Tables to lookup type description
503 --==================================
504 , fnd_lookup_values flv
505 where
506 --============================================
507 -- Joins for getting Allocated On hand records
508 --============================================
509 ms.plan_id = G_PLAN_ID
510 and ms.sr_instance_id = p_sr_instance_id
511 and ms.order_type = G_MRP_ONHAND
512 and ms.planning_partner_site_id is not null
513 and ms.planning_tp_type = G_SUPPLIER
514 --==========================================
515 -- Joins to get Org equivalent company site.
516 --==========================================
517 and ms.organization_id = mcsil.sr_company_site_id
518 and ms.sr_instance_id = mcsil.sr_instance_id
519 and mcsil.company_site_id = mcs.company_site_id
520 and mcsil.partner_type = G_ORGANIZATION
521 and mcsil.sr_company_id = G_SR_OEM_ID
522 and mcs.company_id = mc.company_id
523 and mc.company_id = G_OEM_ID
524 --========================================
525 -- Joins to get supplier site information.
526 --========================================
527 and ms.planning_partner_site_id = mtps.partner_site_id
528 and mtps.partner_site_id = mtpm.tp_key
529 and mtpm.map_type = G_SITE_MAP_TYPE
530 and mtpm.company_key = mcs1.company_site_id
531 and mcs1.company_id = mc1.company_id
532 --========================================
533 -- Joins to get Item information.
534 --========================================
535 and ms.inventory_item_id = mi.inventory_item_id
536 and ms.organization_id = mi.organization_id
537 and ms.sr_instance_id = mi.sr_instance_id
538 and ms.plan_id = mi.plan_id
539 and itm.inventory_item_id (+)= mi.base_item_id
540 --=====================================
541 -- Joins to get Lookup Type description
542 --=====================================
543 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
544 and flv.lookup_code = G_ALLOC_ONHAND
545 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
546 and flv.language = p_language_code
547 --================================================
548 -- Net Change / Targetted / Complete refresh check
549 --================================================
550 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
551 , 'P', nvl(p_refresh_number, -1)
552 , 'I', p_refresh_number
553 , 'T', decode (p_oh_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
554 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
555 )
556
557 GROUP BY
558 G_PLAN_ID,
559 G_SR_INSTANCE_ID,
560 G_OEM_ID,
561 mcs.company_site_id,
562 mc.company_name,
563 mcs.company_site_name,
564 ms.inventory_item_id,
565 'PUBLISH',
566 G_ALLOC_ONHAND,
567 mc1.company_id,
568 mc1.company_name,
569 mtpm.company_key,
570 mcs1.company_site_name,
571 G_DAILY_BKT_TYPE ,
572 mi.item_name,
573 mi.description,
574 flv.meaning,
575 flv.meaning,
576 'Day',
577 mi.uom_code,
578 mi.uom_code,
579 mtps.partner_id,
580 mtps.partner_site_id,
581 ms.sr_instance_id,
582 ms.organization_id
583 , ms.vmi_flag
584 , G_SUPPLIER
585 , mi.base_item_id
586 , itm.item_name
587 ,mi.planner_code --Bug 4424426
588 UNION /* sbala: get on hand in modelled supplier orgs */
589 select
590 G_PLAN_ID PLAN_ID,
591 G_SR_INSTANCE_ID SR_INSTANCE_ID,
592 G_OEM_ID PUBLISHER_ID,
593 mcs.company_site_id PUBLISHER_SITE_ID,
594 mc.company_name PUBLISHER_NAME,
595 mcs.company_site_name PUBLISHER_SITE_NAME,
596 ms.inventory_item_id INVENTORY_ITEM_ID,
597 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
598 'PUBLISH' COMMENTS,
599 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
600 mc1.company_id SUPPLIER_ID,
601 mc1.company_name SUPPLIER_NAME,
602 mtpm.company_key SUPPLIER_SITE_ID,
603 mcs1.company_site_name SUPPLIER_SITE_NAME,
604 G_DAILY_BKT_TYPE BUCKET_TYPE,
605 mi.item_name PUBLISHER_ITEM_NAME,
606 mi.description PUB_ITEM_DESCRIPTIION ,
607 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
608 flv.meaning TP_ORDER_TYPE_DESC,
609 'Day' BUCKET_TYPE_DESC,
610 mi.uom_code UOM_CODE,
611 mi.uom_code PRIMARY_UOM,
612 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
613 mtps.partner_id PARTNER_ID,
614 mtps.partner_site_id PRATNER_SITE_ID,
615 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
616 ms.organization_id ORGANIZATION_ID,
617 ms.vmi_flag VMI_FLAG,
618 G_SUPPLIER ALLOCATION_TYPE,
619 mi.base_item_id BASE_ITEM_ID,
620 itm.item_name BASE_ITEM_NAME,
621 mi.planner_code PLANNER_CODE --Bug 4424426
622 from msc_supplies ms
623 --========================================
624 -- Tables to get Publisher's organization_id
625 --========================================
626 , msc_company_site_id_lid mcsil
627 , msc_company_sites mcs
628 , msc_companies mc
629 --========================================
630 -- Tables to get Supplier's information
631 --========================================
632 , msc_trading_partners mtp
633 , msc_trading_partner_sites mtps
634 , msc_trading_partner_maps mtpm
635 , msc_company_sites mcs1
636 , msc_companies mc1
637 --========================================
638 -- Tables to get Item information
639 --========================================
640 , msc_system_items mi
641 , msc_items itm
642 --==================================
643 -- Tables to lookup type description
644 --==================================
645 , fnd_lookup_values flv
646 where
647 --============================================
648 -- Joins for getting Allocated On hand records
649 --============================================
650 ms.plan_id = G_PLAN_ID
651 and ms.sr_instance_id = p_sr_instance_id
652 and ms.order_type = G_MRP_ONHAND
653 --==========================================
654 -- Joins to get Org equivalent company site.
655 --==========================================
656 and ms.organization_id = mcsil.sr_company_site_id
657 and ms.sr_instance_id = mcsil.sr_instance_id
658 and mcsil.company_site_id = mcs.company_site_id
659 and mcsil.partner_type = G_ORGANIZATION
660 and mcsil.sr_company_id = G_SR_OEM_ID
661 and mcs.company_id = mc.company_id
662 and mc.company_id = G_OEM_ID
663 --========================================
664 -- Joins to get supplier/supplier site information.
665 --========================================
666 and ms.organization_id = mtp.sr_tp_id /* sbala Added join to mtp */
667 and ms.sr_instance_id = mtp.sr_instance_id
668 and mtp.partner_type = G_ORGANIZATION
669 and mtp.modeled_supplier_id is not null
670 and mtp.modeled_supplier_site_id is not null
671 and mtps.partner_id = mtp.modeled_supplier_id
672 and mtps.partner_site_id = mtp.modeled_supplier_site_id
673 and mtpm.tp_key = mtp.modeled_supplier_site_id
674 and mtpm.map_type = G_SITE_MAP_TYPE
675 and mtpm.company_key = mcs1.company_site_id
676 and mcs1.company_id = mc1.company_id
677 --========================================
678 -- Joins to get Item information.
679 --========================================
680 and ms.inventory_item_id = mi.inventory_item_id
681 and ms.organization_id = mi.organization_id
682 and ms.sr_instance_id = mi.sr_instance_id
683 and ms.plan_id = mi.plan_id
684 and itm.inventory_item_id (+)= mi.base_item_id
685 --=====================================
686 -- Joins to get Lookup Type description
687 --=====================================
688 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
689 and flv.lookup_code = G_ALLOC_ONHAND
690 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
691 and flv.language =p_language_code
692 --================================================
693 -- Net Change / Targetted / Complete refresh check
694 --================================================
695 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
696 , 'P', nvl(p_refresh_number, -1)
697 , 'I', p_refresh_number
698 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
699 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
700 )
701
702 GROUP BY
703 G_PLAN_ID,
704 G_SR_INSTANCE_ID,
705 G_OEM_ID,
706 mcs.company_site_id,
707 mc.company_name,
708 mcs.company_site_name,
709 ms.inventory_item_id,
710 'PUBLISH',
711 G_ALLOC_ONHAND,
712 mc1.company_id,
713 mc1.company_name,
714 mtpm.company_key,
715 mcs1.company_site_name,
716 G_DAILY_BKT_TYPE ,
717 mi.item_name,
718 mi.description,
719 flv.meaning,
720 flv.meaning,
721 'Day',
722 mi.uom_code,
723 mi.uom_code,
724 mtps.partner_id,
725 mtps.partner_site_id,
726 ms.sr_instance_id,
727 ms.organization_id,
728 ms.vmi_flag,
729 G_SUPPLIER,
730 mi.base_item_id,
731 itm.item_name,
732 mi.planner_code --Bug 4424426
733 UNION /* sbala: Added for Select of Customer orgs */
734 select
735 G_PLAN_ID PLAN_ID,
736 G_SR_INSTANCE_ID SR_INSTANCE_ID,
737 G_OEM_ID PUBLISHER_ID,
738 mcs.company_site_id PUBLISHER_SITE_ID,
739 mc.company_name PUBLISHER_NAME,
740 mcs.company_site_name PUBLISHER_SITE_NAME,
741 ms.inventory_item_id INVENTORY_ITEM_ID,
742 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
743 'PUBLISH' COMMENTS,
744 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
745 mc1.company_id SUPPLIER_ID, /* sbala will go into customerid */
746 mc1.company_name SUPPLIER_NAME, /* sbala: CUSTOMER_NAME */
747 mtpm.company_key SUPPLIER_SITE_ID, /* sbala: CUSTOMER SITE ID */
748 mcs1.company_site_name SUPPLIER_SITE_NAME,/* sbala:CUSTOMERSITENAME */
749 G_DAILY_BKT_TYPE BUCKET_TYPE,
750 mi.item_name PUBLISHER_ITEM_NAME,
751 mi.description PUB_ITEM_DESCRIPTIION ,
752 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
753 flv.meaning TP_ORDER_TYPE_DESC,
754 'Day' BUCKET_TYPE_DESC,
755 mi.uom_code UOM_CODE,
756 mi.uom_code PRIMARY_UOM,
757 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
758 mtps.partner_id PARTNER_ID,
759 mtps.partner_site_id PRATNER_SITE_ID,
760 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
761 ms.organization_id ORGANIZATION_ID,
762 ms.vmi_flag VMI_FLAG,
763 G_CUSTOMER ALLOCATION_TYPE,
764 mi.base_item_id BASE_ITEM_ID,
765 itm.item_name BASE_ITEM_NAME,
766 mi.planner_code PLANNER_CODE --Bug 4424426
767 from msc_supplies ms
768 --========================================
769 -- Tables to get Publisher's organization_id
770 --========================================
771 , msc_company_site_id_lid mcsil
772 , msc_company_sites mcs
773 , msc_companies mc
774 --========================================
775 -- Tables to get Supplier's information
776 --========================================
777 , msc_trading_partners mtp
778 , msc_trading_partner_sites mtps
779 , msc_trading_partner_maps mtpm
780 , msc_company_sites mcs1
781 , msc_companies mc1
782 --========================================
783 -- Tables to get Item information
784 --========================================
785 , msc_system_items mi
786 , msc_items itm
787 --==================================
788 -- Tables to lookup type description
789 --==================================
790 , fnd_lookup_values flv
791 where
792 --============================================
793 -- Joins for getting Allocated On hand records
794 --============================================
795 ms.plan_id = G_PLAN_ID
796 and ms.sr_instance_id = p_sr_instance_id
797 and ms.order_type = G_MRP_ONHAND
798 --==========================================
799 -- Joins to get Org equivalent company site.
800 --==========================================
801 and ms.organization_id = mcsil.sr_company_site_id
802 and ms.sr_instance_id = mcsil.sr_instance_id
803 and mcsil.company_site_id = mcs.company_site_id
804 and mcsil.partner_type = G_ORGANIZATION
805 and mcsil.sr_company_id = G_SR_OEM_ID
806 and mcs.company_id = mc.company_id
807 and mc.company_id = G_OEM_ID
808 --========================================
809 -- Joins to get supplier/supplier site information.
810 --========================================
811 and ms.organization_id = mtp.sr_tp_id /* sbala Added join to mtp */
812 and ms.sr_instance_id = mtp.sr_instance_id
813 and mtp.partner_type = G_ORGANIZATION
814 and mtp.modeled_customer_id is not null
815 and mtp.modeled_customer_site_id is not null
816 and mtps.partner_id = mtp.modeled_customer_id
817 and mtps.partner_site_id = mtp.modeled_customer_site_id
818 and mtpm.tp_key = mtp.modeled_customer_site_id
819 and mtpm.map_type = G_SITE_MAP_TYPE
820 and mtpm.company_key = mcs1.company_site_id
821 and mcs1.company_id = mc1.company_id
822 --========================================
823 -- Joins to get Item information.
824 --========================================
825 and ms.inventory_item_id = mi.inventory_item_id
826 and ms.organization_id = mi.organization_id
827 and ms.sr_instance_id = mi.sr_instance_id
828 and ms.plan_id = mi.plan_id
829 and itm.inventory_item_id (+)= mi.base_item_id
830 --=====================================
831 -- Joins to get Lookup Type description
832 --=====================================
833 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
834 and flv.lookup_code = G_ALLOC_ONHAND
835 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
836 and flv.language = p_language_code
837 --================================================
838 -- Net Change / Targetted / Complete refresh check
839 --================================================
840 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
841 , 'P', nvl(p_refresh_number, -1)
842 , 'I', p_refresh_number
843 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
844 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
845 )
846
847 GROUP BY
848 G_PLAN_ID,
849 G_SR_INSTANCE_ID,
850 G_OEM_ID,
851 mcs.company_site_id,
852 mc.company_name,
853 mcs.company_site_name,
854 ms.inventory_item_id,
855 'PUBLISH',
856 G_ALLOC_ONHAND,
857 mc1.company_id,
858 mc1.company_name,
859 mtpm.company_key,
860 mcs1.company_site_name,
861 G_DAILY_BKT_TYPE,
862 mi.item_name,
863 mi.description,
864 flv.meaning,
865 flv.meaning,
866 'Day',
867 mi.uom_code,
868 mi.uom_code,
869 mtps.partner_id,
870 mtps.partner_site_id,
871 ms.sr_instance_id,
872 ms.organization_id,
873 ms.vmi_flag,
874 G_CUSTOMER,
875 mi.base_item_id,
876 itm.item_name,
877 mi.planner_code --Bug 4424426
878 ;
879
880 CURSOR allocOnhandNetChange(p_refresh_number NUMBER,
881 p_sr_instance_id NUMBER ,
882 p_language_code VARCHAR2) IS
883 select
884 G_PLAN_ID PLAN_ID,
885 G_SR_INSTANCE_ID SR_INSTANCE_ID,
886 G_OEM_ID PUBLISHER_ID,
887 mcs.company_site_id PUBLISHER_SITE_ID,
888 mc.company_name PUBLISHER_NAME,
889 mcs.company_site_name PUBLISHER_SITE_NAME,
890 ms.inventory_item_id INVENTORY_ITEM_ID,
891 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
892 'PUBLISH' COMMENTS,
893 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
894 mc1.company_id SUPPLIER_ID,
895 mc1.company_name SUPPLIER_NAME,
896 mtpm.company_key SUPPLIER_SITE_ID,
897 mcs1.company_site_name SUPPLIER_SITE_NAME,
898 G_DAILY_BKT_TYPE BUCKET_TYPE,
899 mi.item_name PUBLISHER_ITEM_NAME,
900 mi.description PUB_ITEM_DESCRIPTIION ,
901 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
902 flv.meaning TP_ORDER_TYPE_DESC,
903 'Day' BUCKET_TYPE_DESC,
904 mi.uom_code UOM_CODE,
905 mi.uom_code PRIMARY_UOM,
906 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
907 mtps.partner_id PARTNER_ID,
908 mtps.partner_site_id PRATNER_SITE_ID,
909 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
910 ms.organization_id ORGANIZATION_ID
911 , ms.vmi_flag VMI_FLAG
912 , G_SUPPLIER ALLOCATION_TYPE
913 , mi.base_item_id BASE_ITEM_ID
914 , itm.item_name BASE_ITEM_NAME
915 ,mi.planner_code PLANNER_CODE --Bug 4424426
916 from msc_supplies ms
917 --========================================
918 -- Tables to get Publisher's organization_id
919 --========================================
920 , msc_company_site_id_lid mcsil
921 , msc_company_sites mcs
922 , msc_companies mc
923 --========================================
924 -- Tables to get Supplier's information
925 --========================================
926 , msc_trading_partner_sites mtps
927 , msc_trading_partner_maps mtpm
928 , msc_company_sites mcs1
929 , msc_companies mc1
930 --========================================
931 -- Tables to get Item information
932 --========================================
933 , msc_system_items mi
934 , msc_items itm
935 --==================================
936 -- Tables to lookup type description
937 --==================================
938 , fnd_lookup_values flv
939 where
940 --============================================
941 -- Joins for getting Allocated On hand records
942 --============================================
943 ms.plan_id = G_PLAN_ID
944 and ms.sr_instance_id = p_sr_instance_id
945 and ms.order_type = G_MRP_ONHAND
946 and ms.planning_partner_site_id is not null
947 and ms.planning_tp_type = G_SUPPLIER
948 --==========================================
949 -- Joins to get Org equivalent company site.
950 --==========================================
951 and ms.organization_id = mcsil.sr_company_site_id
952 and ms.sr_instance_id = mcsil.sr_instance_id
953 and mcsil.company_site_id = mcs.company_site_id
954 and mcsil.partner_type = G_ORGANIZATION
955 and mcsil.sr_company_id = G_SR_OEM_ID
956 and mcs.company_id = mc.company_id
957 and mc.company_id = G_OEM_ID
958 --========================================
959 -- Joins to get supplier site information.
960 --========================================
961 and ms.planning_partner_site_id = mtps.partner_site_id
962 and mtps.partner_site_id = mtpm.tp_key
963 and mtpm.map_type = G_SITE_MAP_TYPE
964 and mtpm.company_key = mcs1.company_site_id
965 and mcs1.company_id = mc1.company_id
966 --========================================
967 -- Joins to get Item information.
968 --========================================
969 and ms.inventory_item_id = mi.inventory_item_id
970 and ms.organization_id = mi.organization_id
971 and ms.sr_instance_id = mi.sr_instance_id
972 and ms.plan_id = mi.plan_id
973 and itm.inventory_item_id (+)= mi.base_item_id
974 --=====================================
975 -- Joins to get Lookup Type description
976 --=====================================
977 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
978 and flv.lookup_code = G_ALLOC_ONHAND
979 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
980 and flv.language = p_language_code
981 and exists
982 (--==========================================
983 -- Local View to get Net change information.
984 --==========================================
985 select 1
986 from msc_supplies ms1
987 where plan_id = G_PLAN_ID
988 and sr_instance_id = p_sr_instance_id
989 --==============================================
990 -- Joins for getting net change Item information
991 --==============================================
992 and ms.plan_id = ms1.plan_id
993 and ms.sr_instance_id = ms1.sr_instance_id
994 and ms.organization_id = ms1.organization_id
995 and ms.inventory_item_id = ms1.inventory_item_id
996 and ms.planning_partner_site_id = ms1.planning_partner_site_id
997 and ms.planning_tp_type = ms1.planning_tp_type
998 and ms1.order_type = G_MRP_ONHAND
999 and ms1.planning_partner_site_id is not null
1000 and ms1.planning_tp_type = G_SUPPLIER
1001 and nvl(ms1.refresh_number, -1) = p_refresh_number
1002 )
1003 GROUP BY
1004 G_PLAN_ID,
1005 G_SR_INSTANCE_ID,
1006 G_OEM_ID,
1007 mcs.company_site_id,
1008 mc.company_name,
1009 mcs.company_site_name,
1010 ms.inventory_item_id,
1011 'PUBLISH',
1012 G_ALLOC_ONHAND,
1013 mc1.company_id,
1014 mc1.company_name,
1015 mtpm.company_key,
1016 mcs1.company_site_name,
1017 G_DAILY_BKT_TYPE ,
1018 mi.item_name,
1019 mi.description,
1020 flv.meaning,
1021 flv.meaning,
1022 'Day',
1023 mi.uom_code,
1024 mi.uom_code,
1025 mtps.partner_id,
1026 mtps.partner_site_id,
1027 ms.sr_instance_id,
1028 ms.organization_id
1029 , ms.vmi_flag
1030 , G_SUPPLIER
1031 , mi.base_item_id
1032 , itm.item_name
1033 ,mi.planner_code --Bug 4424426
1034 UNION /* sbala: Allocated on hand for modeled supplier records */
1035 select
1036 G_PLAN_ID PLAN_ID,
1037 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1038 G_OEM_ID PUBLISHER_ID,
1039 mcs.company_site_id PUBLISHER_SITE_ID,
1040 mc.company_name PUBLISHER_NAME,
1041 mcs.company_site_name PUBLISHER_SITE_NAME,
1042 ms.inventory_item_id INVENTORY_ITEM_ID,
1043 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
1044 'PUBLISH' COMMENTS,
1045 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
1046 mc1.company_id SUPPLIER_ID,
1047 mc1.company_name SUPPLIER_NAME,
1048 mtpm.company_key SUPPLIER_SITE_ID,
1049 mcs1.company_site_name SUPPLIER_SITE_NAME,
1050 G_DAILY_BKT_TYPE BUCKET_TYPE,
1051 mi.item_name PUBLISHER_ITEM_NAME,
1052 mi.description PUB_ITEM_DESCRIPTIION ,
1053 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1054 flv.meaning TP_ORDER_TYPE_DESC,
1055 'Day' BUCKET_TYPE_DESC,
1056 mi.uom_code UOM_CODE,
1057 mi.uom_code PRIMARY_UOM,
1058 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
1059 mtps.partner_id PARTNER_ID,
1060 mtps.partner_site_id PRATNER_SITE_ID,
1061 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
1062 ms.organization_id ORGANIZATION_ID,
1063 ms.vmi_flag VMI_FLAG,
1064 G_SUPPLIER ALLOCATION_TYPE,
1065 mi.base_item_id BASE_ITEM_ID,
1066 itm.item_name BASE_ITEM_NAME
1067 ,mi.planner_code PLANNER_CODE --Bug 4424426
1068 from msc_supplies ms
1069 --========================================
1070 -- Tables to get Publisher's organization_id
1071 --========================================
1072 , msc_company_site_id_lid mcsil
1073 , msc_company_sites mcs
1074 , msc_companies mc
1075
1076 --========================================
1077 -- Tables to get Supplier's information
1078 --========================================
1079 , msc_trading_partners mtp /* added sbala */
1080 , msc_trading_partner_sites mtps
1081 , msc_trading_partner_maps mtpm
1082 , msc_company_sites mcs1
1083 , msc_companies mc1
1084 --========================================
1085 -- Tables to get Item information
1086 --========================================
1087 , msc_system_items mi
1088 , msc_items itm
1089 --==================================
1090 -- Tables to lookup type description
1091 --==================================
1092 , fnd_lookup_values flv
1093 where
1094 --============================================
1095 -- Joins for getting Allocated On hand records
1096 --============================================
1097 ms.plan_id = G_PLAN_ID
1098 and ms.sr_instance_id = p_sr_instance_id
1099 and ms.order_type = G_MRP_ONHAND
1100 --==========================================
1101 -- Joins to get Org equivalent company site.
1102 --==========================================
1103 and ms.organization_id = mcsil.sr_company_site_id
1104 and ms.sr_instance_id = mcsil.sr_instance_id
1105 and mcsil.company_site_id = mcs.company_site_id
1106 and mcsil.partner_type = G_ORGANIZATION
1107 and mcsil.sr_company_id = G_SR_OEM_ID
1108 and mcs.company_id = mc.company_id
1109 and mc.company_id = G_OEM_ID
1110 --========================================
1111 -- Joins to get supplier site information.
1112 --========================================
1113 and ms.organization_id = mtp.sr_tp_id /* added joins to mtp sbala */
1114 and ms.sr_instance_id = mtp.sr_instance_id
1115 and mtp.partner_type = G_ORGANIZATION
1116 and mtp.modeled_supplier_id is not null
1117 and mtp.modeled_supplier_site_id is not null
1118 and mtps.partner_id = mtp.modeled_supplier_id /* added sbala */
1119 and mtps.partner_site_id = mtp.modeled_supplier_site_id
1120 and mtps.partner_site_id = mtpm.tp_key
1121 and mtpm.map_type = G_SITE_MAP_TYPE
1122 and mtpm.company_key = mcs1.company_site_id
1123 and mcs1.company_id = mc1.company_id
1124 --========================================
1125 -- Joins to get Item information.
1126 --========================================
1127 and ms.inventory_item_id = mi.inventory_item_id
1128 and ms.organization_id = mi.organization_id
1129 and ms.sr_instance_id = mi.sr_instance_id
1130 and ms.plan_id = mi.plan_id
1131 and itm.inventory_item_id (+)= mi.base_item_id
1132 --=====================================
1133 -- Joins to get Lookup Type description
1134 --=====================================
1135 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
1136 and flv.lookup_code = G_ALLOC_ONHAND
1137 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1138 and flv.language = p_language_code
1139 and exists
1140 (--==========================================
1141 -- Local View to get Net change information.
1142 --==========================================
1143 select 1
1144 from msc_supplies ms1,
1145 msc_trading_partners mtp2
1146 where plan_id = G_PLAN_ID /* Changes for modeled suppliers sbala */
1147 and ms1.sr_instance_id = p_sr_instance_id
1148 --==============================================
1149 -- Joins for getting net change Item information
1150 --==============================================
1151 and ms.plan_id = ms1.plan_id
1152 and ms.sr_instance_id = ms1.sr_instance_id
1153 and ms.organization_id = ms1.organization_id
1154 and ms.inventory_item_id = ms1.inventory_item_id
1155 ----and ms.planning_partner_site_id = X.planning_partner_site_id sbala
1156 ----and ms.planning_tp_type = X.planning_tp_type sbala
1157 and ms1.order_type = G_MRP_ONHAND
1158 and ms1.organization_id = mtp2.sr_tp_id
1159 and ms1.sr_instance_id = mtp2.sr_instance_id
1160 and mtp2.partner_type = G_ORGANIZATION
1161 and mtp2.modeled_supplier_id is not null
1162 and mtp2.modeled_supplier_site_id is not null
1163 and nvl(ms1.refresh_number, -1) = p_refresh_number
1164 )
1165 GROUP BY
1166 G_PLAN_ID,
1167 G_SR_INSTANCE_ID,
1168 G_OEM_ID,
1169 mcs.company_site_id,
1170 mc.company_name,
1171 mcs.company_site_name,
1172 ms.inventory_item_id,
1173 'PUBLISH',
1174 G_ALLOC_ONHAND,
1175 mc1.company_id,
1176 mc1.company_name,
1177 mtpm.company_key,
1178 mcs1.company_site_name,
1179 G_DAILY_BKT_TYPE ,
1180 mi.item_name,
1181 mi.description,
1182 flv.meaning,
1183 flv.meaning,
1184 'Day',
1185 mi.uom_code,
1186 mi.uom_code,
1187 mtps.partner_id,
1188 mtps.partner_site_id,
1189 ms.sr_instance_id,
1190 ms.organization_id,
1191 ms.vmi_flag,
1192 G_SUPPLIER,
1193 mi.base_item_id,
1194 itm.item_name
1195 ,mi.planner_code --Bug 4424426
1196 UNION /* sbala: Allocated on hand for modeled customer records */
1197 select
1198 G_PLAN_ID PLAN_ID,
1199 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1200 G_OEM_ID PUBLISHER_ID,
1201 mcs.company_site_id PUBLISHER_SITE_ID,
1202 mc.company_name PUBLISHER_NAME,
1203 mcs.company_site_name PUBLISHER_SITE_NAME,
1204 ms.inventory_item_id INVENTORY_ITEM_ID,
1205 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
1206 'PUBLISH' COMMENTS,
1207 G_ALLOC_ONHAND PUBLISHER_ORDER_TYPE,
1208 mc1.company_id SUPPLIER_ID, /* sbala CUSTOMERID */
1209 mc1.company_name SUPPLIER_NAME, /* sbala CUSTOMERNAME */
1210 mtpm.company_key SUPPLIER_SITE_ID, /* sbala CUSTOMER SITEID */
1211 mcs1.company_site_name SUPPLIER_SITE_NAME, /* sbala CUSTOMERSITENAME */
1212 G_DAILY_BKT_TYPE BUCKET_TYPE,
1213 mi.item_name PUBLISHER_ITEM_NAME,
1214 mi.description PUB_ITEM_DESCRIPTIION ,
1215 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1216 flv.meaning TP_ORDER_TYPE_DESC,
1217 'Day' BUCKET_TYPE_DESC,
1218 mi.uom_code UOM_CODE,
1219 mi.uom_code PRIMARY_UOM,
1220 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
1221 mtps.partner_id PARTNER_ID,
1222 mtps.partner_site_id PRATNER_SITE_ID,
1223 ms.sr_instance_id ORIG_SR_INSTANCE_ID,
1224 ms.organization_id ORGANIZATION_ID,
1225 ms.vmi_flag VMI_FLAG,
1226 G_CUSTOMER ALLOCATION_TYPE,
1227 mi.base_item_id BASE_ITEM_ID,
1228 itm.item_name BASE_ITEM_NAME
1229 ,mi.planner_code PLANNER_CODE --Bug 4424426
1230 from msc_supplies ms
1231 --========================================
1232 -- Tables to get Publisher's organization_id
1233 --========================================
1234 , msc_company_site_id_lid mcsil
1235 , msc_company_sites mcs
1236 , msc_companies mc
1237
1238 --========================================
1239 -- Tables to get Customer's information
1240 --========================================
1241 , msc_trading_partners mtp /* added sbala */
1242 , msc_trading_partner_sites mtps
1243 , msc_trading_partner_maps mtpm
1244 , msc_company_sites mcs1
1245 , msc_companies mc1
1246 --========================================
1247 -- Tables to get Item information
1248 --========================================
1249 , msc_system_items mi
1250 , msc_items itm
1251 --==================================
1252 -- Tables to lookup type description
1253 --==================================
1254 , fnd_lookup_values flv
1255 where
1256 --============================================
1257 -- Joins for getting Allocated On hand records
1258 --============================================
1259 ms.plan_id = G_PLAN_ID
1260 and ms.sr_instance_id = p_sr_instance_id
1261 and ms.order_type = G_MRP_ONHAND
1262 --==========================================
1263 -- Joins to get Org equivalent company site.
1264 --==========================================
1265 and ms.organization_id = mcsil.sr_company_site_id
1266 and ms.sr_instance_id = mcsil.sr_instance_id
1267 and mcsil.company_site_id = mcs.company_site_id
1268 and mcsil.partner_type = G_ORGANIZATION
1269 and mcsil.sr_company_id = G_SR_OEM_ID
1270 and mcs.company_id = mc.company_id
1271 and mc.company_id = G_OEM_ID
1272 --========================================
1273 -- Joins to get customer site information.
1274 --========================================
1275 and ms.organization_id = mtp.sr_tp_id /* added joins to mtp sbala */
1276 and ms.sr_instance_id = mtp.sr_instance_id
1277 and mtp.partner_type = G_ORGANIZATION
1278 and mtp.modeled_customer_id is not null
1279 and mtp.modeled_customer_site_id is not null
1280 and mtps.partner_id = mtp.modeled_customer_id /* added sbala */
1281 and mtps.partner_site_id = mtp.modeled_customer_site_id
1282 and mtps.partner_site_id = mtpm.tp_key
1283 and mtpm.map_type = G_SITE_MAP_TYPE
1284 and mtpm.company_key = mcs1.company_site_id
1285 and mcs1.company_id = mc1.company_id
1286 --========================================
1287 -- Joins to get Item information.
1288 --========================================
1289 and ms.inventory_item_id = mi.inventory_item_id
1290 and ms.organization_id = mi.organization_id
1291 and ms.sr_instance_id = mi.sr_instance_id
1292 and ms.plan_id = mi.plan_id
1293 and itm.inventory_item_id (+)= mi.base_item_id
1294 --=====================================
1295 -- Joins to get Lookup Type description
1296 --=====================================
1297 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_ALLOC_ONHAND)
1298 and flv.lookup_code = G_ALLOC_ONHAND
1299 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1300 and flv.language = p_language_code
1301 and exists
1302 --==========================================
1303 -- Local View to get Net change information.
1304 --==========================================
1305 (select 1
1306 from msc_supplies ms1,
1307 msc_trading_partners mtp2
1308 where plan_id = G_PLAN_ID /* Changes for modeled customers sbala */
1309 and ms1.sr_instance_id = p_sr_instance_id
1310 and ms1.order_type = G_MRP_ONHAND
1311 and ms1.organization_id = mtp2.sr_tp_id
1312 --==============================================
1313 -- Joins for getting net change Item information
1314 --==============================================
1315 and ms.plan_id = ms1.plan_id
1316 and ms.sr_instance_id = ms1.sr_instance_id
1317 and ms.organization_id = ms1.organization_id
1318 and ms.inventory_item_id = ms1.inventory_item_id
1319 ----and ms.planning_partner_site_id = X.planning_partner_site_id sbala
1320 ----and ms.planning_tp_type = X.planning_tp_type sbala
1321 and ms1.sr_instance_id = mtp2.sr_instance_id
1322 and mtp2.partner_type = G_ORGANIZATION
1323 and mtp2.modeled_customer_id is not null
1324 and mtp2.modeled_customer_site_id is not null
1325 and nvl(ms1.refresh_number, -1) = p_refresh_number
1326 )
1327 GROUP BY
1328 G_PLAN_ID,
1329 G_SR_INSTANCE_ID,
1330 G_OEM_ID,
1331 mcs.company_site_id,
1332 mc.company_name,
1333 mcs.company_site_name,
1334 ms.inventory_item_id,
1335 'PUBLISH',
1336 G_ALLOC_ONHAND,
1337 mc1.company_id,
1338 mc1.company_name,
1339 mtpm.company_key,
1340 mcs1.company_site_name,
1341 G_DAILY_BKT_TYPE ,
1342 mi.item_name,
1343 mi.description,
1344 flv.meaning,
1345 flv.meaning,
1346 'Day',
1347 mi.uom_code,
1348 mi.uom_code,
1349 mtps.partner_id,
1350 mtps.partner_site_id,
1351 ms.sr_instance_id,
1352 ms.organization_id,
1353 ms.vmi_flag,
1354 G_CUSTOMER,
1355 mi.base_item_id,
1356 itm.item_name
1357 ,mi.planner_code --Bug 4424426
1358 ;
1359
1360 CURSOR unallocOnhand(p_refresh_number NUMBER,
1361 p_sr_instance_id NUMBER,
1362 p_language_code VARCHAR2) IS
1363 select
1364 G_PLAN_ID PLAN_ID,
1365 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1366 G_OEM_ID PUBLISHER_ID,
1367 mcs.company_site_id PUBLISHER_SITE_ID,
1368 mc.company_name PUBLISHER_NAME,
1369 mcs.company_site_name PUBLISHER_SITE_NAME,
1370 ms.inventory_item_id INVENTORY_ITEM_ID,
1371 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
1372 'PUBLISH' COMMENTS,
1373 G_UNALLOC_ONHAND PUBLISHER_ORDER_TYPE,
1374 G_DAILY_BKT_TYPE BUCKET_TYPE,
1375 mi.item_name PUBLISHER_ITEM_NAME,
1376 mi.description PUB_ITEM_DESCRIPTIION ,
1377 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1378 flv.meaning TP_ORDER_TYPE_DESC,
1379 'Day' BUCKET_TYPE_DESC,
1380 mi.uom_code UOM_CODE,
1381 mi.uom_code PRIMARY_UOM,
1382 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
1383 mi.base_item_id BASE_ITEM_ID,
1384 itm.item_name BASE_ITEM_NAME
1385 ,mi.planner_code PLANNER_CODE --Bug 4424426
1386 FROM msc_company_site_id_lid mcsil,
1387 msc_company_sites mcs,
1388 msc_companies mc,
1389 msc_supplies ms,
1390 msc_system_items mi,
1391 msc_items itm,
1392 msc_trading_partners mtp,
1393 fnd_lookup_values flv
1394 WHERE
1395 ms.plan_id = G_PLAN_ID
1396 and ms.sr_instance_id = p_sr_instance_id
1397 and ms.order_type = G_MRP_ONHAND
1398 -- and ms.planning_partner_site_id is null
1399 and (ms.planning_tp_type IS NULL OR ms.planning_tp_type = 2)
1400 and ms.organization_id = mcsil.sr_company_site_id
1401 and ms.sr_instance_id = mcsil.sr_instance_id
1402 and mcsil.company_site_id = mcs.company_site_id
1403 and mcsil.partner_type = G_ORGANIZATION
1404 and mcsil.sr_company_id = G_SR_OEM_ID
1405 and mcs.company_id = mc.company_id
1406 and mc.company_id = G_OEM_ID
1407 and ms.inventory_item_id = mi.inventory_item_id
1408 and ms.organization_id = mi.organization_id
1409 and ms.sr_instance_id = mi.sr_instance_id
1410 and ms.plan_id = mi.plan_id
1411 and itm.inventory_item_id (+)= mi.base_item_id
1412 and ms.organization_id = mtp.sr_tp_id
1413 and ms.sr_instance_id = mtp.sr_instance_id
1414 and mtp.partner_type = 3
1415 and mtp.modeled_supplier_id is null
1416 and mtp.modeled_customer_id is null
1417 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_UNALLOC_ONHAND)
1418 and flv.lookup_code = G_UNALLOC_ONHAND
1419 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1420 and flv.language = p_language_code
1421 and nvl(ms.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
1422 , 'P', nvl(p_refresh_number, -1)
1423 , 'I', p_refresh_number
1424 , 'T', decode (p_oh_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
1425 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
1426 )
1427 GROUP BY
1428 G_PLAN_ID,
1429 G_SR_INSTANCE_ID,
1430 G_OEM_ID,
1431 mcs.company_site_id,
1432 mc.company_name,
1433 mcs.company_site_name,
1434 ms.inventory_item_id,
1435 'PUBLISH',
1436 G_UNALLOC_ONHAND,
1437 G_DAILY_BKT_TYPE ,
1438 mi.item_name,
1439 mi.description,
1440 flv.meaning,
1441 flv.meaning,
1442 'Day',
1443 mi.uom_code,
1444 mi.uom_code,
1445 mi.base_item_id,
1446 itm.item_name
1447 ,mi.planner_code ;--Bug 4424426
1448
1449 CURSOR unallocOnhandNetChange(p_refresh_number NUMBER,
1450 p_sr_instance_id NUMBER,
1451 p_language_code VARCHAR2) IS
1452 select
1453 G_PLAN_ID PLAN_ID,
1454 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1455 G_OEM_ID PUBLISHER_ID,
1456 mcs.company_site_id PUBLISHER_SITE_ID,
1457 mc.company_name PUBLISHER_NAME,
1458 mcs.company_site_name PUBLISHER_SITE_NAME,
1459 ms.inventory_item_id INVENTORY_ITEM_ID,
1460 SUM(nvl(ms.new_order_quantity,0)) QUANTITY,
1461 'PUBLISH' COMMENTS,
1462 G_UNALLOC_ONHAND PUBLISHER_ORDER_TYPE,
1463 G_DAILY_BKT_TYPE BUCKET_TYPE,
1464 mi.item_name PUBLISHER_ITEM_NAME,
1465 mi.description PUB_ITEM_DESCRIPTIION ,
1466 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1467 flv.meaning TP_ORDER_TYPE_DESC,
1468 'Day' BUCKET_TYPE_DESC,
1469 mi.uom_code UOM_CODE,
1470 mi.uom_code PRIMARY_UOM,
1471 SUM(nvl(ms.new_order_quantity,0)) PRIMARY_QUANTITY,
1472 mi.base_item_id BASE_ITEM_ID,
1473 itm.item_name BASE_ITEM_NAME
1474 ,mi.planner_code PLANNER_CODE --Bug 4424426
1475 FROM msc_company_site_id_lid mcsil,
1476 msc_company_sites mcs,
1477 msc_companies mc,
1478 msc_supplies ms,
1479 msc_system_items mi,
1480 msc_items itm,
1481 msc_trading_partners mtp,
1482 fnd_lookup_values flv
1483 WHERE
1484 --==============================================
1485 -- Joins for getting net change Item information
1486 --==============================================
1487 ms.plan_id = G_PLAN_ID
1488 and ms.sr_instance_id = p_sr_instance_id
1489 and ms.order_type = G_MRP_ONHAND
1490 and (ms.planning_tp_type IS NULL OR ms.planning_tp_type = 2)
1491 and ms.organization_id = mcsil.sr_company_site_id
1492 and ms.sr_instance_id = mcsil.sr_instance_id
1493 and mcsil.company_site_id = mcs.company_site_id
1494 and mcsil.partner_type = G_ORGANIZATION
1495 and mcsil.sr_company_id = G_SR_OEM_ID
1496 and mcs.company_id = mc.company_id
1497 and mc.company_id = G_OEM_ID
1498 and ms.inventory_item_id = mi.inventory_item_id
1499 and ms.organization_id = mi.organization_id
1500 and ms.sr_instance_id = mi.sr_instance_id
1501 and ms.plan_id = mi.plan_id
1502 and itm.inventory_item_id (+)= mi.base_item_id
1503 and ms.organization_id = mtp.sr_tp_id
1504 and ms.sr_instance_id = mtp.sr_instance_id
1505 and mtp.partner_type = 3
1506 and mtp.modeled_supplier_id is null
1507 and mtp.modeled_customer_id is null
1508 and flv.lookup_code = decode(ms.order_type,G_MRP_ONHAND,G_UNALLOC_ONHAND)
1509 and flv.lookup_code = G_UNALLOC_ONHAND
1510 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1511 and flv.language = p_language_code
1512 and exists
1513 --==========================================
1514 -- Local View to get Net change information.
1515 -- at Item - Organizatoin level.
1516 --==========================================
1517 (select 1
1518 from msc_supplies ms1
1519 where plan_id = G_PLAN_ID
1520 and sr_instance_id = p_sr_instance_id
1521 and ms1.order_type = G_MRP_ONHAND
1522 and ms.plan_id = ms1.plan_id
1523 and ms.sr_instance_id = ms1.sr_instance_id
1524 and ms.organization_id = ms1.organization_id
1525 and ms.inventory_item_id = ms1.inventory_item_id
1526 and (ms1.planning_tp_type IS NULL OR ms1.planning_tp_type = 2)
1527 and nvl(ms1.refresh_number, -1) = p_refresh_number
1528 )
1529 GROUP BY
1530 G_PLAN_ID,
1531 G_SR_INSTANCE_ID,
1532 G_OEM_ID,
1533 mcs.company_site_id,
1534 mc.company_name,
1535 mcs.company_site_name,
1536 ms.inventory_item_id,
1537 'PUBLISH',
1538 G_UNALLOC_ONHAND,
1539 G_DAILY_BKT_TYPE ,
1540 mi.item_name,
1541 mi.description,
1542 flv.meaning,
1543 flv.meaning,
1544 'Day',
1545 mi.uom_code,
1546 mi.uom_code,
1547 mi.base_item_id,
1548 itm.item_name
1549 ,mi.planner_code;--Bug 4424426
1550
1551 CURSOR salesOrders(p_refresh_number NUMBER,
1552 p_sr_instance_id NUMBER,
1553 p_language_code VARCHAR2) IS
1554
1555 select
1556 -- msc_sup_dem_entries_s.nextval TRANSACTION_ID,
1557 G_PLAN_ID PLAN_ID,
1558 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1559 G_OEM_ID PUBLISHER_ID,
1560 mcs.company_site_id PUBLISHER_SITE_ID,
1561 mc.company_name PUBLISHER_NAME,
1562 mcs.company_site_name PUBLISHER_SITE_NAME,
1563 mso.inventory_item_id INVENTORY_ITEM_ID,
1564 (nvl(mso.primary_uom_quantity,0) - nvl(mso.completed_quantity,0)) QUANTITY,
1565 'PUBLISH' COMMENTS,
1566 G_SALES_ORDER PUBLISHER_ORDER_TYPE,
1567 mc1.company_id CUSTOMER_ID,
1568 mc1.company_name CUSTOMER_NAME,
1569 mtpm.company_key CUSTOMER_SITE_ID,
1570 mcs1.company_site_name customer_site_name,
1571 G_DAILY_BKT_TYPE BUCKET_TYPE,
1572 mso.sales_order_number ORDER_NUMBER,
1573 null ORDER_LINE_NUMBER,
1574 mso.requirement_date ship_date,
1575 nvl(mso.schedule_arrival_date,mso.requirement_date) receipt_date,
1576 mso.promise_date original_promise_date,
1577 mi.item_name PUBLISHER_ITEM_NAME,
1578 mi.description PUB_ITEM_DESCRIPTIION ,
1579 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1580 flv.meaning TP_ORDER_TYPE_DESC,
1581 'Day' BUCKET_TYPE_DESC,
1582 mi.uom_code UOM_CODE,
1583 p_user_id CREATED_BY,
1584 mso.creation_date CREATION_DATE,
1585 mso.LAST_UPDATED_BY LAST_UPDATED_BY,
1586 mso.LAST_UPDATE_DATE LAST_UPDATE_DATE,
1587 decode(mso.order_date_type_code, G_SHIP, mso.requirement_date,
1588 G_ARRIVAL, mso.schedule_arrival_date,
1589 mso.requirement_date) key_date,
1590 decode(mso.order_date_type_code, G_SHIP,G_SHIP_CONTROL,
1591 G_ARRIVAL,G_ARRIVE_CONTROL,
1592 G_SHIP_CONTROL) shipping_control,
1593 mi.uom_code PRIMARY_UOM,
1594 (nvl(mso.primary_uom_quantity,0) - nvl(mso.completed_quantity,0)) PRIMARY_QUANTITY,
1595 mso.customer_id PARTNER_ID,
1596 mso.ship_to_site_use_id PARTNER_SITE_ID,
1597 mso.sr_instance_id ORIG_SR_INSTANCE_ID,
1598 mso.organization_id ORGANIZATION_ID,
1599 mi.base_item_id BASE_ITEM_ID,
1600 itm.item_name BASE_ITEM_NAME,
1601 to_char(NULL) END_ORDER_NUMBER,
1602 to_char(NULL) END_ORDER_RELEASE_NUMBER,
1603 to_char(NULL) END_ORDER_LINE_NUMBER,
1604 to_number(NULL) END_ORDER_PUBLISHER_ID,
1605 to_char(NULL) END_ORDER_PUBLISHER_NAME,
1606 to_number(NULL) END_ORDER_PUBLISHER_SITE_ID,
1607 to_char(NULL) END_ORDER_PUBLISHER_SITE_NAME,
1608 to_char(NULL) END_ORDER_TYPE,
1609 to_number(NULL) INTERNAL_FLAG,
1610 G_OEM_ID supplier_id,
1611 mcs.company_site_id supplier_site_id,
1612 mc.company_name supplier_name,
1613 mcs.company_site_name supplier_site_name
1614 ,mi.planner_code PLANNER_CODE --Bug 4424426
1615 from msc_sales_orders mso
1616 --========================================
1617 -- Tables to get Publisher's organization_id
1618 --========================================
1619 , msc_company_site_id_lid mcsil
1620 , msc_company_sites mcs
1621 , msc_companies mc
1622 , msc_trading_partners mtp
1623 --========================================
1624 -- Tables to get Customer and Customer Site information
1625 --========================================
1626 , msc_trading_partner_sites mtps
1627 , msc_trading_partner_maps mtpm
1628 , msc_company_sites mcs1
1629 , msc_companies mc1
1630 --========================================
1631 -- Tables to get Item information
1632 --========================================
1633 , msc_system_items mi
1634 , msc_items itm
1635 --==================================
1636 -- Tables to lookup type description
1637 --==================================
1638 , fnd_lookup_values flv
1639 where
1640 --============================================
1641 -- Joins for Sales Order records
1642 --============================================
1643 mso.sr_instance_id = p_sr_instance_id
1644 --==========================================
1645 -- Joins to get Org equivalent company site.
1646 --==========================================
1647 and mso.organization_id = mcsil.sr_company_site_id
1648 and mso.sr_instance_id = mcsil.sr_instance_id
1649 and mcsil.company_site_id = mcs.company_site_id
1650 and mcsil.sr_company_id = G_SR_OEM_ID
1651 and mcsil.partner_type = G_ORGANIZATION
1652 and mcs.company_id = mc.company_id
1653 and mc.company_id = G_OEM_ID
1654 and mso.organization_id = mtp.sr_tp_id
1655 and mso.sr_instance_id = mtp.sr_instance_id
1656 and mtp.partner_type = 3
1657 and mtp.modeled_supplier_id is NULL
1658 --=====================================================
1659 -- Joins to get Customer and Customer site information.
1660 --=====================================================
1661 and mso.ship_to_site_use_id = mtps.partner_site_id
1662 and mso.customer_id = mtps.partner_id
1663 and mtps.partner_site_id = mtpm.tp_key
1664 and mtpm.map_type = G_SITE_MAP_TYPE
1665 and mtpm.company_key = mcs1.company_site_id
1666 and mcs1.company_id = mc1.company_id
1667 --========================================
1668 -- Joins to get Item information.
1669 --========================================
1670 and mso.inventory_item_id = mi.inventory_item_id
1671 and mso.organization_id = mi.organization_id
1672 and mso.sr_instance_id = mi.sr_instance_id
1673 and mi.plan_id = G_PLAN_ID
1674 and itm.inventory_item_id (+)= mi.base_item_id
1675 --=====================================
1676 -- Joins to get Lookup Type description
1677 --=====================================
1678 and flv.lookup_code = decode(mso.demand_source_type,8,0,14)
1679 and flv.lookup_code = 14
1680 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1681 and flv.language = p_language_code
1682 --================================================
1683 -- Net Change / Targetted / Complete refresh check
1684 --================================================
1685 and nvl(mso.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
1686 , 'P', nvl(p_refresh_number, -1)
1687 , 'I', p_refresh_number
1688 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
1689 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
1690 )
1691 --=========================================================
1692 -- Consider only open Sales Order Lines,
1693 -- From Release 11i if completed quantity is populated then
1694 -- it's considered as closed Sales Order line.
1695 -- We will not bring over these records
1696 --====================-=====================================
1697 --Bug 4535374, added the code for handling R12
1698 and decode(v_apps_ver ,3, decode(v_lrtype,'I',0,mso.completed_quantity),4, decode(v_lrtype,'I',0,mso.completed_quantity), 0) = 0
1699 --============================================
1700 -- Consider lines on Sales Order only. We need
1701 -- not to bring Reservation Lines.
1702 --============================================
1703 and nvl(mso.reservation_type, -99) = 1
1704 and mso.demand_source_type <> 8 /* Ignore Internal Sales orders */
1705 UNION /* sales order in supplier modeled orgs (multi company plng) */
1706 select
1707 -- msc_sup_dem_entries_s.nextval TRANSACTION_ID,
1708 G_PLAN_ID PLAN_ID,
1709 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1710 G_OEM_ID PUBLISHER_ID,
1711 mcs_org.company_site_id PUBLISHER_SITE_ID,
1712 mc_org.company_name PUBLISHER_NAME,
1713 mcs_org.company_site_name PUBLISHER_SITE_NAME,
1714 mso.inventory_item_id INVENTORY_ITEM_ID,
1715 (nvl(mso.primary_uom_quantity,0) - nvl(mso.completed_quantity,0)) QUANTITY,
1716 'PUBLISH' COMMENTS,
1717 G_SALES_ORDER PUBLISHER_ORDER_TYPE,
1718 G_OEM_ID CUSTOMER_ID,
1719 mc.company_name CUSTOMER_NAME,
1720 mcs.company_site_id CUSTOMER_SITE_ID,
1721 mcs.company_site_name customer_site_name,
1722 G_DAILY_BKT_TYPE BUCKET_TYPE,
1723 mso.sales_order_number ORDER_NUMBER,
1724 null ORDER_LINE_NUMBER,
1725 mso.requirement_date ship_date,
1726 mso.schedule_arrival_date receipt_date,
1727 mso.promise_date original_promise_date,
1728 mi.item_name PUBLISHER_ITEM_NAME,
1729 mi.description PUB_ITEM_DESCRIPTIION ,
1730 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1731 flv.meaning TP_ORDER_TYPE_DESC,
1732 'Day' BUCKET_TYPE_DESC,
1733 mi.uom_code UOM_CODE,
1734 p_user_id CREATED_BY,
1735 mso.creation_date CREATION_DATE,
1736 mso.LAST_UPDATED_BY LAST_UPDATED_BY,
1737 mso.LAST_UPDATE_DATE LAST_UPDATE_DATE,
1738 decode(mso.order_date_type_code, G_SHIP, mso.requirement_date,
1739 G_ARRIVAL, mso.schedule_arrival_date,
1740 mso.requirement_date) key_date,
1741 decode(mso.order_date_type_code, G_SHIP,G_SHIP_CONTROL,
1742 G_ARRIVAL,G_ARRIVE_CONTROL,
1743 G_SHIP_CONTROL) shipping_control,
1744 mi.uom_code PRIMARY_UOM,
1745 (nvl(mso.primary_uom_quantity,0) - nvl(mso.completed_quantity,0)) PRIMARY_QUANTITY,
1746 -1 PARTNER_ID,
1747 -1 PARTNER_SITE_ID,
1748 mso.sr_instance_id ORIG_SR_INSTANCE_ID,
1749 mso.organization_id ORGANIZATION_ID,
1750 mi.base_item_id BASE_ITEM_ID,
1751 itm.item_name BASE_ITEM_NAME,
1752 decode(instr(ms.order_number,'('),
1753 0, ms.order_number,
1754 substr(ms.order_number, 1, instr(ms.order_number,'(') - 1))
1755 END_ORDER_NUMBER,
1756 decode(instr(order_number,'('),
1757 0, to_char(null),
1758 substr(order_number, instr(order_number,'(')))
1759 END_ORDER_RELEASE_NUMBER,
1760 to_char(ms.purch_line_num) END_ORDER_LINE_NUMBER,
1761 G_OEM_ID END_ORDER_PUBLISHER_ID,
1762 mc.company_name END_ORDER_PUBLISHER_NAME,
1763 mcs.company_site_id END_ORDER_PUBLISHER_SITE_ID,
1764 mcs.company_site_name END_ORDER_PUBLISHER_SITE_NAME,
1765 to_char(G_PO) END_ORDER_TYPE,
1766 to_number(NULL) INTERNAL_FLAG,
1767 mcs_modeled.company_id supplier_id,
1768 mcs_modeled.company_site_id supplier_site_id,
1769 mc_modeled.company_name supplier_name,
1770 mcs_modeled.company_site_name supplier_site_name
1771 ,mi.planner_code PLANNER_CODE --Bug 4424426
1772 from msc_sales_orders mso
1773 , msc_trading_partners mtp
1774 , msc_trading_partner_maps mtpm
1775 , msc_supplies ms
1776 , msc_trading_partners mtp2
1777 , msc_trading_partner_maps map2
1778 , msc_trading_partner_maps mtpm_org
1779 , msc_company_sites mcs_org
1780 , msc_companies mc_org
1781 , msc_company_sites mcs_modeled
1782 , msc_companies mc_modeled
1783 , msc_company_sites mcs
1784 , msc_companies mc
1785 , msc_system_items mi
1786 , msc_items itm
1787 , fnd_lookup_values flv
1788 where
1789 --============================================
1790 -- Joins for Sales Order records
1791 --============================================
1792 mso.sr_instance_id = p_sr_instance_id
1793 --==========================================
1794 --------------------------------------------------
1795 ----- Joins to get supplier info for modeled orgs
1796 ------------------------------------------------
1797 and mso.organization_id = mtp.sr_tp_id
1798 and mso.sr_instance_id = mtp.sr_instance_id
1799 and mtp.partner_type = 3
1800 and mtp.modeled_supplier_site_id = mtpm.tp_key
1801 and mtpm.map_type = 3
1802 and mtpm.company_key = mcs_modeled.company_site_id
1803 and mtpm_org.tp_key = mtp.partner_id
1804 and mtpm_org.map_type = 2
1805 and mtpm_org.company_key = mcs_org.company_site_id
1806 and mc_org.company_id = mcs_org.company_id
1807 and mcs_modeled.company_id = mc_modeled.company_id
1808 and mso.supply_id = ms.transaction_id
1809 and mso.sr_instance_id = ms.sr_instance_id
1810 and ms.organization_id = mtp2.sr_tp_id
1811 and ms.sr_instance_id = mtp2.sr_instance_id
1812 and mso.inventory_item_id = ms.inventory_item_id
1813 and mtp2.partner_type = 3
1814 and mtp2.partner_id = map2.tp_key
1815 and ms.plan_id = -1
1816 and map2.map_type = 2
1817 and map2.company_key = mcs.company_site_id
1818 and mcs.company_id = mc.company_id
1819 --========================================
1820 -- Joins to get Item information.
1821 --========================================
1822 and mso.inventory_item_id = mi.inventory_item_id
1823 and mso.organization_id = mi.organization_id
1824 and mso.sr_instance_id = mi.sr_instance_id
1825 and mi.plan_id = G_PLAN_ID
1826 and itm.inventory_item_id (+)= mi.base_item_id
1827 --=====================================
1828 -- Joins to get Lookup Type description
1829 --=====================================
1830 and flv.lookup_code = decode(mso.demand_source_type,8,0,14)
1831 and flv.lookup_code = 14
1832 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1833 and flv.language = p_language_code
1834 --================================================
1835 -- Net Change / Targetted / Complete refresh check
1836 --================================================
1837 and nvl(mso.refresh_number, -1) = decode(v_lrtype, 'C', nvl(p_refresh_number, -1)
1838 , 'P', nvl(p_refresh_number, -1)
1839 , 'I', p_refresh_number
1840 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
1841 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
1842 )
1843 --=========================================================
1844 -- Consider only open Sales Order Lines,
1845 -- From Release 11i if completed quantity is populated then
1846 -- it's considered as closed Sales Order line.
1847 -- We will not bring over these records
1848 --====================-=====================================
1849 --Bug 4535374, added the code for handling R12
1850 and decode(v_apps_ver ,3,decode(v_lrtype,'I',0,mso.completed_quantity),4,decode(v_lrtype,'I',0,mso.completed_quantity), 0) = 0
1851 --============================================
1852 -- Consider lines on Sales Order only. We need
1853 -- not to bring Reservation Lines.
1854 --============================================
1855 and nvl(mso.reservation_type, -99) = 1
1856 and mso.demand_source_type <> 8 /* Ignore Internal Sales orders */
1857 UNION ---Internal sales orders for customer facing VMI
1858 select
1859 G_PLAN_ID PLAN_ID,
1860 G_SR_INSTANCE_ID SR_INSTANCE_ID,
1861 G_OEM_ID PUBLISHER_ID,
1862 mcs.company_site_id PUBLISHER_SITE_ID,
1863 mc.company_name PUBLISHER_NAME,
1864 mcs.company_site_name PUBLISHER_SITE_NAME,
1865 mso.inventory_item_id INVENTORY_ITEM_ID,
1866 (nvl(mso.primary_uom_quantity,0) - nvl(mso.completed_quantity,0)) QUANTITY,
1867 'PUBLISH' COMMENTS,
1868 G_SALES_ORDER PUBLISHER_ORDER_TYPE,
1869 mc2.company_id CUSTOMER_ID,
1870 mc2.company_name CUSTOMER_NAME,
1871 mcs2.company_site_id CUSTOMER_SITE_ID,
1872 mcs2.company_site_name customer_site_name,
1873 G_DAILY_BKT_TYPE BUCKET_TYPE,
1874 mso.sales_order_number ORDER_NUMBER,
1875 null ORDER_LINE_NUMBER,
1876 mso.requirement_date ship_date,
1877 nvl(mso.schedule_arrival_date,mso.requirement_date) receipt_date,
1878 mso.promise_date original_promise_date,
1879 mi.item_name PUBLISHER_ITEM_NAME,
1880 mi.description PUB_ITEM_DESCRIPTIION ,
1881 flv.meaning PUBLISHER_ORDER_TYPE_DESC,
1882 flv.meaning TP_ORDER_TYPE_DESC,
1883 'Day' BUCKET_TYPE_DESC,
1884 mi.uom_code UOM_CODE,
1885 p_user_id CREATED_BY,
1886 mso.creation_date CREATION_DATE,
1887 mso.LAST_UPDATED_BY LAST_UPDATED_BY,
1888 mso.LAST_UPDATE_DATE LAST_UPDATE_DATE,
1889 decode(mso.order_date_type_code, G_SHIP, mso.requirement_date,
1890 G_ARRIVAL, mso.schedule_arrival_date,
1891 mso.requirement_date) key_date,
1892 decode(mso.order_date_type_code, G_SHIP,G_SHIP_CONTROL,
1893 G_ARRIVAL,G_ARRIVE_CONTROL,
1894 G_SHIP_CONTROL) shipping_control,
1895 mi.uom_code PRIMARY_UOM,
1896 (nvl(mso.primary_uom_quantity,0)
1897 - nvl(mso.completed_quantity,0)) PRIMARY_QUANTITY,
1898 mtp.modeled_customer_id PARTNER_ID,
1899 mtp.modeled_customer_site_id PARTNER_SITE_ID,
1900 mso.sr_instance_id ORIG_SR_INSTANCE_ID,
1901 mso.organization_id ORGANIZATION_ID,
1902 to_number(null) BASE_ITEM_ID,
1903 to_char(null) BASE_ITEM_NAME,
1904 decode(instr(ms.order_number,'('),
1905 0, ms.order_number,
1906 substr(ms.order_number, 1, instr(ms.order_number,'(')
1907 - 1)) END_ORDER_NUMBER,
1908 decode(instr(order_number,'('),
1909 0, to_char(null),
1910 substr(order_number, instr(order_number,'(')))
1911 END_ORDER_RELEASE_NUMBER,
1912 to_char(ms.purch_line_num) END_ORDER_LINE_NUMBER,
1913 mc2.company_id END_ORDER_PUBLISHER_ID,
1914 mc2.company_name END_ORDER_PUBLISHER_NAME,
1915 mcs2.company_site_id END_ORDER_PUBLISHER_SITE_ID,
1916 mcs2.company_site_name END_ORDER_PUBLISHER_SITE_NAME,
1917 to_char(G_REQ) END_ORDER_TYPE,
1918 SYS_YES INTERNAL_FLAG,
1919 G_OEM_ID SUPPLIER_ID,
1920 mcs.company_site_id SUPPLIER_SITE_ID,
1921 mc.company_name SUPPLIER_NAME,
1922 mcs.company_site_name SUPPLIER_SITE_NAME
1923 ,mi.planner_code PLANNER_CODE --Bug 4424426
1924 from msc_sales_orders mso,
1925 msc_trading_partners mtp,
1926 msc_trading_partner_maps map,
1927 msc_company_sites mcs,
1928 msc_companies mc,
1929 msc_supplies ms,
1930 msc_trading_partners mtp2,
1931 msc_trading_partner_maps map2,
1932 msc_company_sites mcs2,
1933 msc_companies mc2,
1934 msc_system_items mi,
1935 fnd_lookup_values flv
1936 where mso.sr_instance_id = p_sr_instance_id
1937 and mso.demand_source_type = 8 --- Internal Sales order
1938 and mso.organization_id = mtp.sr_tp_id
1939 and mso.sr_instance_id = mtp.sr_instance_id
1940 and mtp.partner_type = 3
1941 and mtp.partner_id = map.tp_key
1942 and map.map_type = 2
1943 and map.company_key = mcs.company_site_id
1944 and mc.company_id = mcs.company_id
1945 and ms.transaction_id = mso.supply_id
1946 and ms.organization_id = mtp2.sr_tp_id
1947 and ms.sr_instance_id = mtp2.sr_instance_id
1948 and ms.order_type = 2
1949 and ms.source_organization_id is not null
1950 and mtp2.partner_type = 3
1951 and mtp2.modeled_customer_id is not null
1952 and mtp2.modeled_customer_site_id is not null
1953 and mtp2.modeled_customer_site_id = map2.tp_key
1954 and map2.map_type = 3
1955 and map2.company_key = mcs2.company_site_id
1956 and mc2.company_id = mcs2.company_id
1957 and ms.inventory_item_id = mi.inventory_item_id
1958 and ms.organization_id = mi.organization_id
1959 and ms.sr_instance_id = mi.sr_instance_id
1960 and ms.plan_id = mi.plan_id
1961 and mi.plan_id = G_PLAN_ID
1962 and mi.inventory_planning_code = 7 -- vmi
1963 and flv.lookup_code = decode(mso.demand_source_type,8,14)
1964 and flv.lookup_code = 14
1965 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
1966 and flv.language = p_language_code
1967 and nvl(mso.refresh_number, -1) = decode(v_lrtype,
1968 'C', nvl(p_refresh_number, -1)
1969 , 'P', nvl(p_refresh_number, -1)
1970 , 'I', p_refresh_number
1971 , 'T', decode (p_po_sn_flag, G_AUTO_NET_COLL, p_refresh_number,
1972 G_AUTO_TAR_COLL, nvl(p_refresh_number, -1))
1973 )
1974 --Bug 4535374, added the code for handling R12
1975 and decode(v_apps_ver ,3,decode(v_lrtype,'I',0,mso.completed_quantity),4,decode(v_lrtype,'I',0,mso.completed_quantity), 0) = 0
1976 and nvl(mso.reservation_type, -99) = 1
1977 ;
1978
1979 cursor org IS
1980 select mtpm.company_key org_id
1981 from msc_trading_partners mtp,
1982 msc_trading_partner_maps mtpm,
1983 msc_instance_orgs mio,
1984 msc_coll_parameters mcp
1985 where mtp.sr_instance_id = p_sr_instance_id
1986 and mtp.partner_type = 3
1987 and mtp.partner_id = mtpm.tp_key
1988 and mtpm.map_type = 2
1989 and mio.sr_instance_id = mtp.sr_instance_id
1990 and mio.ORGANIZATION_ID = mtp.sr_tp_id
1991 and mcp.instance_id = mio.sr_instance_id
1992 and nvl(mcp.ORG_GROUP,'-999') = DECODE(nvl(mcp.org_group,'-999'), '-999', nvl(mcp.org_group,'-999')
1993 , mio.org_group);
1994
1995 t_sr_instance_id number_arr;
1996 t_plan_id number_arr ;
1997 t_internal_flag number_arr;
1998 t_end_ord_pub_name msc_sce_loads_pkg.publisherList;
1999 t_ins_end_ord_pub_name msc_sce_loads_pkg.publisherList := msc_sce_loads_pkg.publisherList();
2000 t_end_ord_pub_id msc_sce_loads_pkg.publishidList := msc_sce_loads_pkg.publishidList();
2001 t_ins_end_ord_pub_id msc_sce_loads_pkg.publishidList := msc_sce_loads_pkg.publishidList();
2002 t_end_ord_pub_site_name msc_sce_loads_pkg.pubsiteList;
2003 t_ins_end_ord_pub_site_name msc_sce_loads_pkg.pubsiteList := msc_sce_loads_pkg.pubsiteList();
2004 t_end_ord_pub_site_id msc_sce_loads_pkg.pubsiteidList;
2005 t_ins_end_ord_pub_site_id msc_sce_loads_pkg.pubsiteidList :=
2006 msc_sce_loads_pkg.pubsiteidList();
2007 t_end_pub_ord_type msc_sce_loads_pkg.ordertypeList;
2008 t_ins_end_pub_ord_type msc_sce_loads_pkg.ordertypeList := msc_sce_loads_pkg.ordertypeList();
2009 t_ins_end_ord_type_desc msc_sce_loads_pkg.otdescList := msc_sce_loads_pkg.otdescList();
2010 t_end_order_number msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2011 t_ins_end_ord_num msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2012 t_delivery_id msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2013 t_end_order_line_number msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2014 t_ins_end_ord_line_num msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2015 t_end_order_rel_number msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2016 t_ins_end_ord_rel_num msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2017 t_pub msc_sce_loads_pkg.publisherList := msc_sce_loads_pkg.publisherList();
2018 t_pub_id msc_sce_loads_pkg.publishidList := msc_sce_loads_pkg.publishidList();
2019 /* PS: added code to initialize the variables */
2020 t_pub_site msc_sce_loads_pkg.pubsiteList := msc_sce_loads_pkg.pubsiteList();
2021 t_pub_site_id msc_sce_loads_pkg.pubsiteidList := msc_sce_loads_pkg.pubsiteidList();
2022 t_supp msc_sce_loads_pkg.supplierList := msc_sce_loads_pkg.supplierList();
2023 t_supp_id msc_sce_loads_pkg.suppidList := msc_sce_loads_pkg.suppidList();
2024 t_supp_site msc_sce_loads_pkg.suppsiteList := msc_sce_loads_pkg.suppsiteList();
2025 t_supp_site_id msc_sce_loads_pkg.suppsiteidList := msc_sce_loads_pkg.suppsiteidList();
2026 t_customer_id number_arr;
2027 t_customer_name msc_sce_loads_pkg.supplierList;
2028 t_customer_site_id number_arr;
2029 t_customer_site_name msc_sce_loads_pkg.suppsiteList;
2030 t_shipfrom msc_sce_loads_pkg.shipfromList := msc_sce_loads_pkg.shipfromList();
2031 t_shipfrom_id msc_sce_loads_pkg.shipfromidList := msc_sce_loads_pkg.shipfromidList();
2032 t_shipfrom_site msc_sce_loads_pkg.shipfromsiteList := msc_sce_loads_pkg.shipfromsiteList();
2033 t_shipfrom_site_id msc_sce_loads_pkg.shipfromsidList := msc_sce_loads_pkg.shipfromsidList();
2034 t_shipfrom_addr msc_sce_loads_pkg.shipfromaddrList := msc_sce_loads_pkg.shipfromaddrList();
2035 t_shipto msc_sce_loads_pkg.shiptoList := msc_sce_loads_pkg.shiptoList();
2036 t_shipto_id msc_sce_loads_pkg.shiptoidList := msc_sce_loads_pkg.shiptoidList();
2037 t_shipto_site msc_sce_loads_pkg.shiptositeList := msc_sce_loads_pkg.shiptositeList();
2038 t_shipto_site_id msc_sce_loads_pkg.shiptosidList := msc_sce_loads_pkg.shiptosidList();
2039 t_order_type msc_sce_loads_pkg.ordertypeList;
2040 t_pub_order_type msc_sce_loads_pkg.ordertypeList;
2041 t_ot_desc msc_sce_loads_pkg.otdescList;
2042 t_tp_ot_desc msc_sce_loads_pkg.otdescList;
2043 t_bkt_type_desc msc_sce_loads_pkg.bktypedescList;
2044 t_bkt_type msc_sce_loads_pkg.bktypeList;
2045 t_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2046 t_item_id msc_sce_loads_pkg.itemidList := msc_sce_loads_pkg.itemidList();
2047 t_base_item_id msc_sce_loads_pkg.itemidList;
2048 t_base_item_name msc_sce_loads_pkg.itemList;
2049 t_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2050 t_pri_uom msc_sce_loads_pkg.uomList;
2051 t_category msc_sce_loads_pkg.categoryList;
2052 t_ord_num msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2053 t_line_num msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2054 t_new_sched_date msc_sce_loads_pkg.newschedList;
2055 t_new_dock_date msc_sce_loads_pkg.newschedList;
2056 t_ship_date msc_sce_loads_pkg.shipdateList := msc_sce_loads_pkg.shipdateList();
2057 t_receipt_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2058 t_new_ord_plac_date msc_sce_loads_pkg.newordplaceList;
2059 t_orig_prom_date msc_sce_loads_pkg.origpromList;
2060 t_req_date msc_sce_loads_pkg.reqdateList;
2061 t_uom msc_sce_loads_pkg.uomList := msc_sce_loads_pkg.uomList();
2062 t_quantity msc_sce_loads_pkg.qtyList := msc_sce_loads_pkg.qtyList();
2063 t_comments msc_sce_loads_pkg.commentList;
2064 t_created_by number_arr;
2065 t_creation_date msc_sce_loads_pkg.shipdateList;
2066 t_last_updated_by number_arr;
2067 t_last_update_date msc_sce_loads_pkg.shipdateList;
2068 t_transaction_id number_arr := number_arr();
2069 t_key_date msc_sce_loads_pkg.newschedList := msc_sce_loads_pkg.newschedList();
2070 t_promise_date msc_sce_loads_pkg.newschedList;
2071 t_primary_quantity number_arr;
2072 t_owner_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2073 t_customer_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2074 t_supplier_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2075 t_owner_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2076 t_cust_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2077 t_sup_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2078
2079 t_partner_id number_arr := number_arr();
2080 t_partner_site_id number_arr := number_arr();
2081 t_orig_sr_instance_id number_arr := number_arr();
2082 t_organization_id number_arr := number_arr();
2083 t_alloc_type number_arr := number_arr();
2084 t_tp_uom msc_sce_loads_pkg.uomList := msc_sce_loads_pkg.uomList();
2085 t_release_number msc_sce_loads_pkg.ordernumList;
2086 t_tp_quantity number_arr := number_arr();
2087 t_vmi_flag number_arr := number_arr();
2088 t_acceptance_required_flag acceptance_flags := acceptance_flags();
2089 t_need_by_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2090 t_promised_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2091 t_shipping_control shippingControlList := shippingControlList();
2092
2093 t_planner_code msc_sce_loads_pkg.plannerCode := msc_sce_loads_pkg.plannerCode(); --Bug 4424426
2094
2095 /* Variable for inserting records */
2096 t_ins_sr_instance_id number_arr := number_arr();
2097 t_ins_plan_id number_arr := number_arr();
2098 t_ins_pub msc_sce_loads_pkg.publisherList := msc_sce_loads_pkg.publisherList();
2099 t_ins_pub_id msc_sce_loads_pkg.publishidList := msc_sce_loads_pkg.publishidList();
2100 t_ins_pub_site msc_sce_loads_pkg.pubsiteList := msc_sce_loads_pkg.pubsiteList();
2101 t_ins_pub_site_id msc_sce_loads_pkg.pubsiteidList := msc_sce_loads_pkg.pubsiteidList();
2102 t_ins_supp msc_sce_loads_pkg.supplierList := msc_sce_loads_pkg.supplierList();
2103 t_ins_supp_id msc_sce_loads_pkg.suppidList := msc_sce_loads_pkg.suppidList();
2104 t_ins_supp_site msc_sce_loads_pkg.suppsiteList := msc_sce_loads_pkg.suppsiteList();
2105 t_ins_supp_site_id msc_sce_loads_pkg.suppsiteidList := msc_sce_loads_pkg.suppsiteidList();
2106 t_ins_customer_id number_arr := number_arr();
2107 t_ins_customer_name msc_sce_loads_pkg.supplierList := msc_sce_loads_pkg.supplierList();
2108 t_ins_customer_site_id number_arr := number_arr();
2109 t_ins_customer_site_name msc_sce_loads_pkg.supplierList := msc_sce_loads_pkg.supplierList();
2110 t_ins_shipfrom msc_sce_loads_pkg.shipfromList := msc_sce_loads_pkg.shipfromList();
2111 t_ins_shipfrom_id msc_sce_loads_pkg.shipfromidList := msc_sce_loads_pkg.shipfromidList();
2112 t_ins_shipfrom_site msc_sce_loads_pkg.shipfromsiteList := msc_sce_loads_pkg.shipfromsiteList();
2113 t_ins_shipfrom_site_id msc_sce_loads_pkg.shipfromsidList := msc_sce_loads_pkg.shipfromsidList();
2114 t_ins_shipfrom_addr msc_sce_loads_pkg.shipfromaddrList := msc_sce_loads_pkg.shipfromaddrList();
2115 t_ins_shipto msc_sce_loads_pkg.shiptoList := msc_sce_loads_pkg.shiptoList();
2116 t_ins_shipto_id msc_sce_loads_pkg.shiptoidList := msc_sce_loads_pkg.shiptoidList();
2117 t_ins_shipto_site msc_sce_loads_pkg.shiptositeList := msc_sce_loads_pkg.shiptositeList();
2118 t_ins_shipto_site_id msc_sce_loads_pkg.shiptosidList := msc_sce_loads_pkg.shiptosidList();
2119 t_ins_order_type msc_sce_loads_pkg.ordertypeList := msc_sce_loads_pkg.ordertypeList();
2120 t_ins_ot_desc msc_sce_loads_pkg.otdescList := msc_sce_loads_pkg.otdescList();
2121 t_ins_bkt_type_desc msc_sce_loads_pkg.bktypedescList := msc_sce_loads_pkg.bktypedescList();
2122 t_ins_bkt_type msc_sce_loads_pkg.bktypeList := msc_sce_loads_pkg.bktypeList();
2123 t_ins_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2124 t_ins_base_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2125 t_ins_item_id msc_sce_loads_pkg.itemidList := msc_sce_loads_pkg.itemidList();
2126 t_ins_base_item_id msc_sce_loads_pkg.itemidList := msc_sce_loads_pkg.itemidList();
2127 t_ins_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2128 t_ins_pri_uom msc_sce_loads_pkg.uomList := msc_sce_loads_pkg.uomList();
2129 t_ins_category msc_sce_loads_pkg.categoryList := msc_sce_loads_pkg.categoryList();
2130 t_ins_ord_num msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2131 t_ins_line_num msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2132 t_ins_new_sched_date msc_sce_loads_pkg.newschedList := msc_sce_loads_pkg.newschedList();
2133 t_ins_ship_date msc_sce_loads_pkg.shipdateList := msc_sce_loads_pkg.shipdateList();
2134 t_ins_receipt_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2135 t_ins_new_ord_plac_date msc_sce_loads_pkg.newordplaceList := msc_sce_loads_pkg.newordplaceList();
2136 t_ins_orig_prom_date msc_sce_loads_pkg.origpromList := msc_sce_loads_pkg.origpromList();
2137 t_ins_req_date msc_sce_loads_pkg.reqdateList := msc_sce_loads_pkg.reqdateList();
2138 t_ins_uom msc_sce_loads_pkg.uomList := msc_sce_loads_pkg.uomList();
2139 t_ins_quantity msc_sce_loads_pkg.qtyList := msc_sce_loads_pkg.qtyList();
2140 t_ins_comments msc_sce_loads_pkg.commentList := msc_sce_loads_pkg.commentList();
2141 t_ins_created_by number_arr := number_arr();
2142 t_ins_creation_date msc_sce_loads_pkg.shipdateList := msc_sce_loads_pkg.shipdateList();
2143 t_ins_last_updated_by number_arr := number_arr();
2144 t_ins_last_update_date msc_sce_loads_pkg.shipdateList := msc_sce_loads_pkg.shipdateList();
2145 t_ins_transaction_id number_arr := number_arr();
2146 t_ins_key_date msc_sce_loads_pkg.newschedList := msc_sce_loads_pkg.newschedList();
2147 t_ins_pub_order_type msc_sce_loads_pkg.ordertypeList := msc_sce_loads_pkg.ordertypeList();
2148 t_ins_new_dock_date msc_sce_loads_pkg.newschedList := msc_sce_loads_pkg.newschedList();
2149 t_ins_tp_ot_desc msc_sce_loads_pkg.otdescList := msc_sce_loads_pkg.otdescList();
2150 t_ins_primary_quantity number_arr := number_arr();
2151 t_ins_promise_date msc_sce_loads_pkg.newschedList := msc_sce_loads_pkg.newschedList();
2152 t_ins_owner_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2153 t_ins_customer_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2154 t_ins_supplier_item_name msc_sce_loads_pkg.itemList := msc_sce_loads_pkg.itemList();
2155 t_ins_owner_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2156 t_ins_cust_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2157 t_ins_sup_item_desc msc_sce_loads_pkg.itemdescList := msc_sce_loads_pkg.itemdescList();
2158 t_ins_tp_uom msc_sce_loads_pkg.uomList := msc_sce_loads_pkg.uomList();
2159 t_ins_tp_quantity number_arr := number_arr();
2160 t_ins_release_number msc_sce_loads_pkg.ordernumList := msc_sce_loads_pkg.ordernumList();
2161 t_ins_alloc_type number_arr := number_arr();
2162 t_ins_vmi_flag number_arr := number_arr();
2163 t_ins_acceptance_required_flag acceptance_flags := acceptance_flags();
2164 t_ins_need_by_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2165 t_ins_promised_date msc_sce_loads_pkg.receiptdateList := msc_sce_loads_pkg.receiptdateList();
2166 t_ins_delivery_id msc_sce_loads_pkg.linenumList := msc_sce_loads_pkg.linenumList();
2167 t_ins_internal_flag number_arr := number_arr();
2168 t_ins_shipping_control shippingControlList := shippingControlList();
2169 t_ins_shipping_control_code number_arr := number_arr();
2170 t_ins_planner_code msc_sce_loads_pkg.plannerCode := msc_sce_loads_pkg.plannerCode(); --Bug 4424426
2171
2172 a_supplier_update number_arr := number_arr();
2173 a_customer_update number_arr := number_arr();
2174 a_resultant_update number_arr := number_arr();
2175
2176 l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177 l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178 l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179 l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180 l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181 l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2182 l_lead_time NUMBER;
2183 l_tp_customer_id NUMBER;
2184 l_tp_customer_site_id NUMBER;
2185 l_location_id NUMBER;
2186 l_org_location_id NUMBER;
2187 l_session_id NUMBER;
2188 l_regions_return_status VARCHAR2(1);
2189 l_tp_uom MSC_SUP_DEM_ENTRIES.TP_UOM_CODE%TYPE;
2190 l_conversion_found BOOLEAN;
2191 l_conversion_rate NUMBER;
2192
2193 l_process_lead_time BOOLEAN;
2194 l_prev_lead_time NUMBER;
2195 l_prev_partner_id NUMBER;
2196 l_prev_partner_site_id NUMBER;
2197 l_prev_organization_id NUMBER;
2198
2199 l_sysdate DATE;
2200 i NUMBER :=0 ;
2201 j NUMBER :=0 ;
2202
2203 full_language VARCHAR2(80);
2204 l_language_code VARCHAR2(10);
2205
2206 l_shipping_ctrl_lktype VARCHAR2(30) := 'MSC_X_SHIPPING_CONTROL';
2207
2208 l_ship_lkcode NUMBER := 2;
2209 l_arrive_lkcode NUMBER := 1;
2210
2211
2212 l_asl_vmi_flag NUMBER;
2213
2214 a_ins_count number_arr := number_arr();
2215 v_in_org_str VARCHAR2(1024):='NULL';
2216 v_in_ot_str VARCHAR2(1024):= 'NULL';
2217 v_sql_stmt VARCHAR2(3000);
2218 a_post_status NUMBER;
2219 a_ack_return_status BOOLEAN;
2220
2221 CURSOR itemSuppliers (p_organization_id NUMBER,
2222 p_sr_instance_id NUMBER,
2223 p_item_id NUMBER,
2224 p_partner_id NUMBER,
2225 p_partner_site_id NUMBER) IS
2226 select supplier_item_name,
2227 nvl(mis.processing_lead_time, 0),
2228 mis.uom_code,
2229 nvl(mis.vmi_flag, 2),
2230 description
2231 from msc_item_suppliers mis
2232 where mis.plan_id = G_PLAN_ID
2233 and mis.organization_id = p_organization_id
2234 and mis.sr_instance_id = p_sr_instance_id
2235 and mis.inventory_item_id = p_item_id
2236 and mis.supplier_id = p_partner_id
2237 and nvl(mis.supplier_site_id, -99) = decode(mis.supplier_site_id,
2238 null, -99, p_partner_site_id)
2239 order by nvl(mis.supplier_site_id, -99), mis.using_organization_id desc;
2240
2241 BEGIN
2242 /* Display the parameters */
2243
2244 LOG_MESSAGE('Parameters');
2245 LOG_MESSAGE('==========');
2246 LOG_MESSAGE(' p_sr_instance_id :'||p_sr_instance_id);
2247 LOG_MESSAGE(' p_user_id :'||p_user_id);
2248 LOG_MESSAGE(' p_po_enabled_flag :'|| p_po_enabled_flag);
2249 LOG_MESSAGE(' p_oh_enabled_flag :'|| p_oh_enabled_flag);
2250 LOG_MESSAGE(' p_so_enabled_flag :'|| p_so_enabled_flag);
2251 LOG_MESSAGE(' p_asl_enabled_flag :'|| p_asl_enabled_flag);
2252 LOG_MESSAGE(' p_sup_resp_flag :'|| p_sup_resp_flag);
2253 LOG_MESSAGE(' p_po_sn_flag :'|| p_po_sn_flag);
2254 LOG_MESSAGE(' p_oh_sn_flag :'|| p_oh_sn_flag);
2255 LOG_MESSAGE(' p_so_sn_flag :'|| p_so_sn_flag);
2256 LOG_MESSAGE(' p_suprep_sn_flag :'|| p_suprep_sn_flag);
2257
2258 --======================
2259 -- Get the user language
2260 --======================
2261 /* BUG #3845796 :Using Applications Session Language in preference to ICX_LANGUAGE profile value */
2262
2263 l_language_code := USERENV('LANG');
2264
2265 IF(l_language_code is null) THEN
2266 full_language := fnd_profile.value('ICX_LANGUAGE');
2267
2268 IF full_language IS NOT NULL THEN
2269 BEGIN
2270 SELECT language_code
2271 INTO l_language_code
2272 FROM fnd_languages
2273 WHERE nls_language = full_language;
2274 EXCEPTION WHEN OTHERS THEN
2275 LOG_MESSAGE('Error while fetching user language');
2276 END;
2277 ELSE
2278 LOG_MESSAGE('Can not determine language using either the Applications Session or the
2279 ICX_LANGUAGE profile option so assigning default value as US ');
2280 l_language_code := 'US';
2281 END IF;
2282 END IF;
2283
2284 LOG_MESSAGE('The language Code :'||l_language_code);
2285
2286 execute immediate
2287 'select meaning from FND_LOOKUP_VALUES '
2288 || ' where LOOKUP_TYPE = ''MSC_X_ORDER_TYPE'' '
2289 || ' and LOOKUP_CODE = 15 '
2290 || ' and LANGUAGE = :l_language_code '
2291 into G_ASN_DESC
2292 USING l_language_code;
2293 LOG_DEBUG('The G_ASN_DESC :'||G_ASN_DESC);
2294
2295 execute immediate
2296 'select meaning from FND_LOOKUP_VALUES '
2297 || ' where LOOKUP_TYPE = ''MSC_X_ORDER_TYPE'' '
2298 || ' and LOOKUP_CODE = 13 '
2299 || ' and LANGUAGE = :l_language_code '
2300 into G_PO_DESC
2301 USING l_language_code;
2302 LOG_DEBUG('The G_PO_DESC :'||G_PO_DESC);
2303
2304 execute immediate
2305 'select meaning from FND_LOOKUP_VALUES '
2306 || ' where LOOKUP_TYPE = ''MSC_X_ORDER_TYPE'' '
2307 || ' and LOOKUP_CODE = 20 '
2308 || ' and LANGUAGE = :l_language_code '
2309 into G_REQ_DESC
2310 USING l_language_code;
2311 LOG_DEBUG('The G_REQ_DESC :'||G_REQ_DESC);
2312
2313 BEGIN
2314 select MEANING
2315 into G_SHIP_CONTROL
2316 from fnd_lookup_values
2317 where LOOKUP_TYPE = l_shipping_ctrl_lktype
2318 and LOOKUP_CODE =l_ship_lkcode
2319 and language = l_language_code;
2320 LOG_DEBUG('G_SHIP_CONTROL :'||G_SHIP_CONTROL);
2321
2322 select MEANING
2323 into G_ARRIVE_CONTROL
2324 from fnd_lookup_values
2325 where LOOKUP_TYPE = l_shipping_ctrl_lktype
2326 and LOOKUP_CODE =l_arrive_lkcode
2327 and language = l_language_code;
2328 LOG_DEBUG('G_ARRIVE_CONTROL :'||G_ARRIVE_CONTROL);
2329 EXCEPTION
2330 WHEN OTHERS THEN
2331 G_SHIP_CONTROL := 'Ship';
2332 G_ARRIVE_CONTROL := 'Arrival';
2333 END;
2334
2335 /* Get the current refresh number for the source instance */
2336 BEGIN
2337 select LCID,
2338 lrtype,
2339 so_lrtype,
2340 DECODE(mai.m2a_dblink,NULL,' ', '@' || m2a_dblink),
2341 apps_ver
2342 into v_refresh_number,
2343 v_lrtype,
2344 v_so_lrtype,
2345 v_sr_dblink,
2346 v_apps_ver
2347 from msc_apps_instances mai
2348 where mai.instance_id = p_sr_instance_id;
2349
2350 LOG_MESSAGE('Additional Information');
2351 LOG_MESSAGE('======================');
2352 LOG_MESSAGE(' Last Refresh Number :'||v_refresh_number);
2353 LOG_MESSAGE(' Last Refresh Type :'||v_lrtype);
2354 LOG_MESSAGE(' Last Sales Order Refresh Type :'||v_so_lrtype);
2355 EXCEPTION WHEN OTHERS THEN
2356 LOG_MESSAGE('Error while fetching last refresh number');
2357 LOG_MESSAGE(SQLERRM);
2358 RETCODE := G_ERROR;
2359 END;
2360
2361 /* LEG-COLL */
2362 -- ========================================
2363 -- Manipulate lrtype for legacy collections
2364 -- ========================================
2365 IF v_lrtype = 'L' THEN
2366 v_lrtype := 'I';
2367 LOG_MESSAGE('Legacy Collections -- Treating as Net Change collections');
2368 END IF;
2369
2370 -- ===========
2371 -- Get sysdate
2372 -- ===========
2373 BEGIN
2374 select sysdate into l_sysdate from dual;
2375 EXCEPTION WHEN OTHERS THEN
2376 LOG_MESSAGE('Error while fetching sysdate');
2377 LOG_MESSAGE(SQLERRM);
2378 RETCODE := G_ERROR;
2379 END;
2380
2381 -- ====================================================================
2382 -- Derive refresh_number (LCID) for each entity.
2383 -- Before contineous collections the refresh_number used to be
2384 -- same for all entities collected. In case of contineous collections
2385 -- we can collect some entities in targeted mode and some entities
2386 -- in net change mode. Therefore we can have different refresh numbers
2387 -- across the entities.
2388 -- ====================================================================
2389
2390 IF (v_lrtype = 'T') THEN
2391 INITIALIZE_REFRESH_NUM(v_refresh_number,
2392 v_lrtype,
2393 p_po_enabled_flag,
2394 p_oh_enabled_flag,
2395 p_so_enabled_flag,
2396 p_asl_enabled_flag,
2397 p_sup_resp_flag ,
2398 p_po_sn_flag,
2399 p_oh_sn_flag,
2400 p_so_sn_flag,
2401 p_suprep_sn_flag);
2402 ELSE
2403 v_supply_refresh_number := v_refresh_number;
2404 v_oh_refresh_number := v_refresh_number;
2405 v_so_refresh_number := v_refresh_number;
2406 v_suprep_refresh_number := v_refresh_number;
2407 END IF;
2408
2409 -- ===============================================================================
2410 -- If complete refresh then delete following records in msc_sup_dem_entries
2411 --
2412 -- 1. All PO Order Types (13,20,16,15) which belong to Publisher = "My Company"
2413 -- and publisher_site IN (All enaled inventory organizations for ERP instance)
2414 -- 2. All Allocated OH records (order type = G_ALLOC_ONHAND) which belong to
2415 -- Publisher = "My Company"
2416 -- and publisher_site IN (All enaled inventory organizations for ERP instance)
2417 -- 3. All Sales Order records which belong to Publisher = "My Company"
2418 -- and publisher_site IN (All enaled inventory organizations for ERP instance)
2419 -- ================================================================================
2420
2421 /* Get the enabled Orgs for instance */
2422
2423 /* Get the enabled Orgs for instance */
2424 IF v_in_org_str='NULL' THEN
2425
2426 FOR lc_ins_org IN org LOOP
2427
2428 IF org%rowcount = 1 THEN
2429 v_in_org_str:=' IN ('|| lc_ins_org.org_id;
2430 ELSE
2431 v_in_org_str := v_in_org_str||','||lc_ins_org.org_id;
2432 END IF;
2433
2434 END LOOP;
2435
2436 IF v_in_org_str<>'NULL' THEN
2437 v_in_org_str:= v_in_org_str || ')';
2438 ELSE
2439 v_in_org_str:= '= -9999';
2440 END IF;
2441
2442 END IF; -- If v_in_org_str='NULL'
2443
2444 IF ( v_lrtype = 'C' OR
2445 v_lrtype = 'P' ) THEN
2446
2447 /* Delete PO, OH and SO records if the current collection is of Complete refresh */
2448 IF v_lrtype = 'C' THEN
2449
2450 IF (v_so_lrtype = 'C') then
2451 /* bug:3584822 -- Delete S.O only if the Sales orders
2452 is YES in complete refresh */
2453 v_sql_stmt:=
2454 ' delete msc_sup_dem_entries msde'
2455 ||' where msde.publisher_id = 1'
2456 ||' and msde.publisher_site_id '||v_in_org_str
2457 ||' and msde.plan_id = -1 '
2458 ||' and msde.publisher_order_type IN '
2459 ||'( 9, 10, 13, 20, 14, 15, 16 '
2460 ||') ';
2461 ELSE
2462 v_sql_stmt:=
2463 ' delete msc_sup_dem_entries msde'
2464 ||' where msde.publisher_id = 1'
2465 ||' and msde.publisher_site_id '||v_in_org_str
2466 ||' and msde.plan_id = -1 '
2467 ||' and msde.publisher_order_type IN '
2468 ||'( 9, 10, 13, 20, 15, 16 '
2469 ||') ';
2470 END IF;
2471
2472 EXECUTE IMMEDIATE v_sql_stmt;
2473
2474 LOG_MESSAGE('Total Records for deletion in Complete Refresh : '||SQL%ROWCOUNT);
2475 COMMIT;
2476
2477 END IF;
2478
2479 IF ( v_lrtype = 'P' AND p_po_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2480
2481 v_sql_stmt:=
2482 ' delete msc_sup_dem_entries msde'
2483 ||' where msde.publisher_id = 1'
2484 ||' and msde.publisher_site_id '||v_in_org_str
2485 ||' and msde.plan_id = -1 '
2486 ||' and msde.publisher_order_type IN '
2487 ||'( 13, 20, 15, 16 '
2488 ||') ';
2489
2490 EXECUTE IMMEDIATE v_sql_stmt;
2491 LOG_MESSAGE('Total records(PO/REQ/ASN/REC) for deletion in Targeted Refresh : '||SQL%ROWCOUNT);
2492
2493 COMMIT;
2494
2495 END IF;
2496
2497 IF ( v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2498
2499 v_sql_stmt:=
2500 ' delete msc_sup_dem_entries msde'
2501 ||' where msde.publisher_id = 1'
2502 ||' and msde.publisher_site_id '||v_in_org_str
2503 ||' and msde.plan_id = -1 '
2504 ||' and msde.publisher_order_type IN '
2505 ||'( 9, 10 '
2506 ||') ';
2507
2508 EXECUTE IMMEDIATE v_sql_stmt;
2509 LOG_MESSAGE('Total records(Onhand) for deletion in Targeted Refresh : '||SQL%ROWCOUNT);
2510
2511 COMMIT;
2512
2513 END IF;
2514
2515 IF ( v_lrtype = 'P' AND p_so_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2516
2517 v_sql_stmt:=
2518 ' delete msc_sup_dem_entries msde'
2519 ||' where msde.publisher_id = 1'
2520 ||' and msde.publisher_site_id '||v_in_org_str
2521 ||' and msde.plan_id = -1 '
2522 ||' and msde.publisher_order_type IN '
2523 ||'( 14 '
2524 ||') ';
2525
2526 EXECUTE IMMEDIATE v_sql_stmt;
2527 LOG_MESSAGE('Total records(Sales Orders) for deletion in Targeted Refresh : '||SQL%ROWCOUNT);
2528
2529 COMMIT;
2530
2531 END IF;
2532
2533 ELSIF (v_lrtype = 'T') THEN
2534
2535 --=====================================================
2536 -- If it's Automatic collections then
2537 -- some entities can be refreshed in net change mode
2538 -- and some entities can be refreshed in targeted mode.
2539 --
2540 -- We will find out all entities refreshed in Targeted
2541 -- mode and delete those entities.
2542 -- ====================================================
2543
2544
2545 --=======================
2546 -- Initialize v_in_ot_str
2547 --=======================
2548 v_in_ot_str := 'NULL' ;
2549
2550 v_in_ot_str := 'IN ( -99 ';
2551
2552 IF (nvl(p_po_enabled_flag, SYS_NO) = SYS_YES AND nvl(p_po_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL) THEN
2553 v_in_ot_str := v_in_ot_str || ', '|| G_PO ||', '
2554 || G_REQ ||', '
2555 || G_ASN ||', '
2556 || G_SHIP_RECEIPT;
2557 END IF;
2558
2559 IF (nvl(p_oh_enabled_flag, SYS_NO) = SYS_YES AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL) THEN
2560
2561 v_in_ot_str := v_in_ot_str ||', '||G_ALLOC_ONHAND ||', '||G_UNALLOC_ONHAND ;
2562
2563 END IF;
2564
2565 IF (nvl(p_so_enabled_flag, SYS_NO) = SYS_YES AND nvl(p_so_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL) THEN
2566
2567 v_in_ot_str := v_in_ot_str ||', '||G_SALES_ORDER;
2568
2569 END IF;
2570
2571 v_in_ot_str := v_in_ot_str || ', -999)' ;
2572
2573 --================================
2574 -- Initialize and build v_sql_stmt
2575 --================================
2576
2577 BEGIN
2578
2579 v_sql_stmt := NULL;
2580
2581 v_sql_stmt:=
2582 ' delete msc_sup_dem_entries msde'
2583 ||' where msde.publisher_id = 1'
2584 ||' and msde.publisher_site_id '||v_in_org_str
2585 ||' and msde.plan_id = -1 '
2586 ||' and msde.publisher_order_type '||v_in_ot_str;
2587
2588
2589 EXECUTE IMMEDIATE v_sql_stmt;
2590
2591 COMMIT;
2592
2593 EXCEPTION WHEN OTHERS THEN
2594 LOG_MESSAGE('Error while deleting records from msc_sup_dem_entries in case of Automatic Collections');
2595 LOG_MESSAGE(SQLERRM);
2596
2597 RETCODE := G_ERROR;
2598 RETURN;
2599 END;
2600
2601 ELSIF (
2602 (v_lrtype = 'I' AND p_po_enabled_flag = MSC_CL_COLLECTION.SYS_YES))
2603 OR
2604 (v_lrtype = 'T' AND (nvl(p_po_enabled_flag, SYS_NO) = SYS_YES AND nvl(p_po_sn_flag, G_AUTO_NO_COLL) = G_AUTO_NET_COLL)
2605 ) THEN
2606
2607 -- =================================================================
2608 -- If PO data is collected in netchange mode then we want to delete
2609 -- following records from msc_sup_dem_entries
2610 -- Records with =>
2611 -- publisher_id = 1
2612 -- order_type = G_REQ
2613 -- order_number = null
2614 --
2615 -- These records are created using MOE. We will delete those.
2616 -- ================================================================
2617
2618 BEGIN
2619
2620 v_sql_stmt := NULL;
2621
2622 v_sql_stmt:=
2623 ' delete msc_sup_dem_entries msde'
2624 ||' where msde.publisher_id = 1'
2625 ||' and msde.publisher_site_id '||v_in_org_str
2626 ||' and msde.plan_id = -1 '
2627 ||' and msde.publisher_order_type = 20 '
2628 ||' and msde.order_number is NULL' ;
2629
2630 EXECUTE IMMEDIATE v_sql_stmt;
2631
2632 COMMIT;
2633 EXCEPTION WHEN OTHERS THEN
2634 LOG_MESSAGE('Error while deleting records from msc_sup_dem_entries created using MOE');
2635 LOG_MESSAGE(SQLERRM);
2636
2637 RETCODE := G_ERROR;
2638 RETURN;
2639
2640 END;
2641
2642 END IF; --v_lrtype = 'C' or v_lrtype = 'P' THEN
2643
2644
2645 /* Get the cursor data into collection objects */
2646 --=======================
2647 -- Collect Supply Records
2648 --=======================
2649
2650 IF ((v_lrtype = 'C') OR
2651 (v_lrtype = 'I') OR
2652 (v_lrtype = 'P' AND p_po_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
2653 (v_lrtype = 'T' AND nvl(p_po_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL )) THEN
2654
2655 OPEN mscSupply(v_supply_refresh_number,
2656 p_sr_instance_id,
2657 l_language_code);
2658
2659 BEGIN
2660 FETCH mscSupply BULK COLLECT INTO
2661 --t_transaction_id,
2662 t_sr_instance_id,
2663 t_plan_id,
2664 t_pub_id,
2665 t_pub_site_id,
2666 t_pub,
2667 t_pub_site,
2668 t_item_id,
2669 t_quantity,
2670 t_order_type,
2671 t_receipt_date,
2672 -- t_ship_date,
2673 t_supp_id,
2674 t_supp,
2675 t_supp_site_id,
2676 t_supp_site,
2677 t_line_num,
2678 t_ord_num,
2679 t_shipto_id,
2680 t_shipto_site_id,
2681 t_shipto,
2682 t_shipto_site,
2683 t_shipfrom_id,
2684 t_shipfrom_site_id,
2685 t_shipfrom,
2686 t_shipfrom_site,
2687 t_item_name,
2688 t_item_desc,
2689 t_uom,
2690 t_ot_desc,
2691 t_bkt_type,
2692 t_bkt_type_desc,
2693 t_comments,
2694 t_created_by,
2695 t_creation_date,
2696 t_last_updated_by,
2697 t_last_update_date,
2698 t_key_date,
2699 t_partner_id,
2700 t_partner_site_id,
2701 t_orig_sr_instance_id,
2702 t_organization_id,
2703 t_release_number,
2704 t_new_ord_plac_date
2705 , t_vmi_flag
2706 , t_acceptance_required_flag
2707 , t_need_by_date
2708 , t_promised_date
2709 , t_base_item_id
2710 , t_base_item_name
2711 , t_internal_flag
2712 ,t_planner_code; --Bug 4424426
2713
2714 CLOSE mscSupply;
2715
2716 EXCEPTION WHEN OTHERS THEN
2717 LOG_MESSAGE('Error while fetching records from CURSOR mscSupply');
2718 LOG_MESSAGE(SQLERRM);
2719 RETCODE := G_ERROR;
2720 END;
2721
2722 /* Now we have all collection objects for publishing data to
2723 Supply ChaExchange.
2724 Calling SCE API.
2725 */
2726
2727 LOG_MESSAGE('Total Supply records fetched :'||t_pub_id.COUNT);
2728
2729 --===========================================================================================
2730 -- Derive dependant column values before updating or inserting records in msc_sup_dem_entries
2731 --===========================================================================================
2732 IF t_pub_id.COUNT > 0 THEN
2733
2734 FOR j in 1.. t_pub_id.COUNT LOOP
2735
2736 --===================================================
2737 -- Extend the variables which value will get derived.
2738 --===================================================
2739
2740 t_owner_item_name.EXTEND;
2741 t_customer_item_name.EXTEND;
2742 t_supplier_item_name.EXTEND;
2743 t_owner_item_desc.EXTEND;
2744 t_cust_item_desc.EXTEND;
2745 t_tp_uom.EXTEND;
2746 t_tp_quantity.EXTEND;
2747 t_ship_date.EXTEND;
2748 t_sup_item_desc.EXTEND;
2749
2750 --============================================
2751 -- Derive the Item cross reference information
2752 -- This need not be done for internal requisitions
2753 --============================================
2754
2755 if(t_internal_flag(j) is null) then
2756 --====================
2757 -- Initialize variable
2758 --====================
2759 l_supplier_item_name := null;
2760 l_lead_time := 0;
2761 l_tp_uom := null;
2762 l_conversion_rate := null;
2763 l_supplier_item_desc := null;
2764
2765 t_customer_item_name(j) := t_item_name(j);
2766 t_owner_item_name(j) := t_item_name(j);
2767 t_owner_item_desc(j) := t_item_desc(j);
2768 t_cust_item_desc(j) := t_item_desc(j);
2769
2770 BEGIN
2771
2772 --=====================================================
2773 -- We will fetch vmi_flag from itemSuppliers cursor
2774 -- but it won't be used for order types in mscSupplies
2775 -- cursor. We are fetching it here for syntax purpose.
2776 -- ====================================================
2777
2778 OPEN itemSuppliers(t_organization_id(j),
2779 t_orig_sr_instance_id(j),
2780 t_item_id(j),
2781 t_partner_id(j),
2782 t_partner_site_id(j));
2783
2784 FETCH itemSuppliers INTO l_supplier_item_name,
2785 l_lead_time,
2786 l_tp_uom,
2787 l_asl_vmi_flag,
2788 l_supplier_item_desc;
2789
2790 CLOSE itemSuppliers;
2791
2792 EXCEPTION WHEN OTHERS THEN
2793 l_supplier_item_name := null;
2794 l_lead_time := 0;
2795 l_tp_uom := t_uom(j);
2796 l_supplier_item_desc := NULL;
2797 END;
2798
2799 t_supplier_item_name(j) := l_supplier_item_name;
2800 t_tp_uom(j) := nvl(l_tp_uom, t_uom(j));
2801 t_sup_item_desc(j) := l_supplier_item_desc;
2802
2803 --===============================================
2804 -- Get the conversion rate and derive tp_quantity
2805 --===============================================
2806 msc_x_util.get_uom_conversion_rates
2807 (t_uom(j),
2808 t_tp_uom(j),
2809 t_item_id(j),
2810 l_conversion_found,
2811 l_conversion_rate);
2812
2813 IF l_conversion_found THEN
2814 t_tp_quantity(j) := t_quantity(j) * l_conversion_rate;
2815 ELSE
2816 t_tp_quantity(j) := t_quantity(j);
2817 END IF;
2818
2819 --===================================================
2820 -- Derive the ship_date and receipt_date information.
2821 --===================================================
2822 t_ship_date(j) := t_receipt_date(j) - nvl(l_lead_time, 0);
2823 else /* Internal requisitions */
2824
2825 t_tp_quantity(j) := t_quantity(j);
2826
2827 l_lead_time := 0; /* sbala, add code to calculate
2828 lead time correctly for internal
2829 reqs */
2830
2831 t_ship_date(j) := t_receipt_date(j) - nvl(l_lead_time, 0);
2832
2833 /* Added for bug# 3311573, set supplier_item_name as the OEM
2834 item name for Internal Reqs of Cust. VMI items in the customer modeled org */
2835 t_supplier_item_name(j) := t_item_name(j);
2836 t_sup_item_desc(j) := t_item_desc(j);
2837 end if;
2838 END LOOP;
2839 END IF;
2840
2841 IF t_pub_id.COUNT > 0 THEN
2842
2843 BEGIN
2844
2845 FORALL j in 1..t_pub_id.COUNT
2846
2847 UPDATE msc_sup_dem_entries
2848 SET last_refresh_number = msc_cl_refresh_s.nextval,
2849 quantity = round((nvl(t_quantity(j),0)),6),
2850 tp_quantity = round((nvl(t_tp_quantity(j),0)),6),
2851 comments = t_comments(j),
2852 ship_date = t_ship_date(j),
2853 receipt_date = t_receipt_date(j),
2854 ship_from_party_id = t_shipfrom_id(j),
2855 ship_to_party_id = t_shipto_id(j),
2856 ship_to_party_site_id = t_shipto_site_id(j),
2857 ship_to_party_name = t_shipto(j),
2858 ship_to_party_site_name = t_shipto_site(j),
2859 ship_from_party_site_id = t_shipfrom_site_id(j),
2860 ship_from_party_name = t_shipfrom(j),
2861 ship_from_party_site_name = t_shipfrom_site(j),
2862 uom_code = t_uom(j),
2863 last_update_date = sysdate,
2864 last_updated_by = -1,
2865 primary_quantity = round((nvl(t_quantity(j),0)),6),
2866 tp_uom_code = t_tp_uom(j),
2867 key_date = t_key_date(j),
2868 primary_uom = t_uom(j),
2869 need_by_date = t_need_by_date(j),
2870 promised_date = t_promised_date(j)
2871 ,internal_flag = t_internal_flag(j)
2872 WHERE plan_id = t_plan_id(j) AND
2873 sr_instance_id = t_sr_instance_id(j) AND
2874 publisher_id = t_pub_id(j) AND
2875 publisher_site_id = t_pub_site_id(j) AND
2876 NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
2877 NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
2878 publisher_order_type = t_order_type(j) AND
2879 inventory_item_id = t_item_id(j) AND
2880 NVL(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(j), G_NULL_STRING) AND
2881 NVL(order_number, G_NULL_STRING) = NVL(t_ord_num(j), G_NULL_STRING) AND
2882 NVL(line_number, G_NULL_STRING) = NVL(t_line_num(j), G_NULL_STRING) AND
2883 /* Removed Key_date from transaction key. Added release_number istead */
2884 -- NVL(key_date, sysdate) = NVL(t_key_date(j), sysdate) ;
2885 NVL(release_number, G_NULL_STRING) = NVL(t_release_number(j), G_NULL_STRING);
2886
2887 COMMIT;
2888
2889 EXCEPTION WHEN OTHERS THEN
2890 LOG_MESSAGE('Error while updating msc_sup_dem_entries');
2891 LOG_MESSAGE(SQLERRM);
2892 RETCODE := G_ERROR;
2893 END;
2894
2895 END IF;
2896
2897 /* Create collections objects for insertion */
2898 FOR j in 1.. t_pub_id.COUNT LOOP
2899 IF (SQL%BULK_ROWCOUNT(j) = 0) THEN
2900 a_ins_count.EXTEND;
2901 t_ins_sr_instance_id.EXTEND;
2902 t_ins_plan_id.EXTEND;
2903 --t_ins_transaction_id.EXTEND;
2904 t_ins_pub_id.EXTEND;
2905 t_ins_pub_site_id.EXTEND;
2906 t_ins_pub.EXTEND;
2907 t_ins_pub_site.EXTEND;
2908 t_ins_item_id.EXTEND;
2909 t_ins_base_item_id.EXTEND;
2910 t_ins_quantity.EXTEND;
2911 t_ins_order_type.EXTEND;
2912 t_ins_receipt_date.EXTEND;
2913 t_ins_ship_date.EXTEND;
2914 t_ins_supp_id.EXTEND;
2915 t_ins_supp.EXTEND;
2916 t_ins_supp_site_id.EXTEND;
2917 t_ins_supp_site.EXTEND;
2918 t_ins_line_num.EXTEND;
2919 t_ins_ord_num.EXTEND;
2920 t_ins_shipto_id.EXTEND;
2921 t_ins_shipto_site_id.EXTEND;
2922 t_ins_shipto.EXTEND;
2923 t_ins_shipto_site.EXTEND;
2924 t_ins_shipfrom_id.EXTEND;
2925 t_ins_shipfrom_site_id.EXTEND;
2926 t_ins_shipfrom.EXTEND;
2927 t_ins_shipfrom_site.EXTEND;
2928 t_ins_item_name.EXTEND;
2929 t_ins_base_item_name.EXTEND;
2930 t_ins_item_desc.EXTEND;
2931 t_ins_uom.EXTEND;
2932 t_ins_ot_desc.EXTEND;
2933 t_ins_bkt_type.EXTEND;
2934 t_ins_bkt_type_desc.EXTEND;
2935 t_ins_comments.EXTEND;
2936 t_ins_created_by.EXTEND;
2937 t_ins_creation_date.EXTEND;
2938 t_ins_last_updated_by.EXTEND;
2939 t_ins_last_update_date.EXTEND;
2940 t_ins_key_date.EXTEND;
2941 t_ins_release_number.EXTEND;
2942 t_ins_new_ord_plac_date.EXTEND;
2943 t_ins_acceptance_required_flag.EXTEND;
2944 t_ins_need_by_date.EXTEND;
2945 t_ins_promised_date.EXTEND;
2946 t_ins_internal_flag.EXTEND;
2947 t_ins_sup_item_desc.EXTEND;
2948 t_ins_planner_code.EXTEND; --Bug 4424426
2949
2950 a_ins_count(a_ins_count.COUNT) := j;
2951 t_ins_sr_instance_id(a_ins_count.COUNT) := t_sr_instance_id(j);
2952 --t_ins_transaction_id(a_ins_count.COUNT) := t_transaction_id(j);
2953 t_ins_plan_id(a_ins_count.COUNT) := t_plan_id(j);
2954 t_ins_pub_id(a_ins_count.COUNT) := t_pub_id(j);
2955 t_ins_pub_site_id(a_ins_count.COUNT) := t_pub_site_id(j);
2956 t_ins_pub(a_ins_count.COUNT) := t_pub(j);
2957 t_ins_pub_site(a_ins_count.COUNT) := t_pub_site(j);
2958 t_ins_item_id(a_ins_count.COUNT) := t_item_id(j);
2959 t_ins_base_item_id(a_ins_count.COUNT) := t_base_item_id(j);
2960 t_ins_base_item_name(a_ins_count.COUNT) := t_base_item_name(j);
2961 t_ins_quantity(a_ins_count.COUNT) := t_quantity(j);
2962 t_ins_order_type(a_ins_count.COUNT) := t_order_type(j);
2963 t_ins_supp_id(a_ins_count.COUNT) := t_supp_id(j);
2964 t_ins_supp(a_ins_count.COUNT) := t_supp(j);
2965 t_ins_supp_site_id(a_ins_count.COUNT) := t_supp_site_id(j);
2966 t_ins_supp_site(a_ins_count.COUNT) := t_supp_site(j);
2967 t_ins_line_num(a_ins_count.COUNT) := t_line_num(j);
2968 t_ins_ord_num(a_ins_count.COUNT) := t_ord_num(j);
2969 t_ins_shipto_id(a_ins_count.COUNT):= t_shipto_id(j);
2970 t_ins_shipto_site_id(a_ins_count.COUNT) := t_shipto_site_id(j);
2971 t_ins_shipto(a_ins_count.COUNT) := t_shipto(j);
2972 t_ins_shipto_site(a_ins_count.COUNT) := t_shipto_site(j);
2973 t_ins_shipfrom_id(a_ins_count.COUNT) := t_shipfrom_id(j);
2974 t_ins_shipfrom_site_id(a_ins_count.COUNT) := t_shipfrom_site_id(j);
2975 t_ins_shipfrom(a_ins_count.COUNT) := t_shipfrom(j);
2976 t_ins_shipfrom_site(a_ins_count.COUNT) := t_shipfrom_site(j);
2977 t_ins_item_name(a_ins_count.COUNT) := t_item_name(j);
2978 t_ins_item_desc(a_ins_count.COUNT) := t_item_desc(j);
2979 t_ins_uom(a_ins_count.COUNT) := t_uom(j);
2980 t_ins_ot_desc(a_ins_count.COUNT) := t_ot_desc(j);
2981 t_ins_bkt_type(a_ins_count.COUNT) := t_bkt_type(j);
2982 t_ins_bkt_type_desc(a_ins_count.COUNT) := t_bkt_type_desc(j);
2983 t_ins_comments(a_ins_count.COUNT) := t_comments(j);
2984 t_ins_created_by(a_ins_count.COUNT) := t_created_by(j);
2985 t_ins_creation_date(a_ins_count.COUNT) := t_creation_date(j);
2986 t_ins_last_updated_by(a_ins_count.COUNT) := t_last_updated_by(j);
2987 t_ins_last_update_date(a_ins_count.COUNT) := t_last_update_date(j);
2988 t_ins_key_date(a_ins_count.COUNT) := t_key_date(j);
2989 t_ins_release_number(a_ins_count.COUNT) := t_release_number(j);
2990 t_ins_new_ord_plac_date(a_ins_count.COUNT) := t_new_ord_plac_date(j);
2991 t_ins_need_by_date(a_ins_count.COUNT) := t_need_by_date(j);
2992 t_ins_promised_date(a_ins_count.COUNT) := t_promised_date(j);
2993 t_ins_internal_flag(a_ins_count.COUNT) := t_internal_flag(j);
2994 t_ins_planner_code(a_ins_count.COUNT) := t_planner_code(j);--Bug 4424426
2995
2996 t_ins_owner_item_name.EXTEND;
2997 t_ins_customer_item_name.EXTEND;
2998 t_ins_supplier_item_name.EXTEND;
2999 t_ins_owner_item_desc.EXTEND;
3000 t_ins_cust_item_desc.EXTEND;
3001 t_ins_tp_uom.EXTEND;
3002 t_ins_tp_quantity.EXTEND;
3003 t_ins_vmi_flag.EXTEND;
3004
3005 t_ins_customer_item_name(a_ins_count.COUNT) := t_item_name(j);
3006 t_ins_owner_item_name(a_ins_count.COUNT) := t_item_name(j);
3007 t_ins_owner_item_desc(a_ins_count.COUNT) := t_item_desc(j);
3008 t_ins_cust_item_desc(a_ins_count.COUNT) := t_item_desc(j);
3009 t_ins_supplier_item_name(a_ins_count.COUNT) := t_supplier_item_name(j);
3010 t_ins_sup_item_desc(a_ins_count.COUNT) := t_sup_item_desc(j);
3011
3012 /* If Supplier Item description is not available then we will use
3013 OEM Item description */
3014
3015 if t_ins_supplier_item_name(a_ins_count.COUNT) IS NOT NULL THEN
3016 if t_ins_sup_item_desc(a_ins_count.COUNT) IS NULL THEN
3017 t_ins_sup_item_desc(a_ins_count.COUNT) := t_item_desc(j);
3018 end if;
3019 end if;
3020
3021 t_ins_tp_uom(a_ins_count.COUNT) := t_tp_uom(j);
3022 t_ins_tp_quantity(a_ins_count.COUNT) := t_tp_quantity(j);
3023 t_ins_receipt_date(a_ins_count.COUNT) := t_receipt_date(j);
3024 t_ins_ship_date(a_ins_count.COUNT) := t_ship_date(j);
3025 t_ins_vmi_flag(a_ins_count.COUNT) := t_vmi_flag(j);
3026
3027 /* CP-ACK starts */
3028 t_ins_acceptance_required_flag(a_ins_count.COUNT) := t_acceptance_required_flag(j);
3029
3030 END IF;
3031 END LOOP;
3032
3033 LOG_MESSAGE('Total Supply records for insertion '||a_ins_count.COUNT);
3034
3035 /* for bug # 3323263, modified code to populate the customer columns
3036 from the ship_to* columns */
3037
3038 IF a_ins_count.COUNT > 0 THEN
3039 BEGIN
3040 FORALL j in 1..a_ins_count.COUNT
3041 insert into msc_sup_dem_entries
3042 (
3043 sr_instance_id
3044 ,transaction_id
3045 ,plan_id
3046 ,publisher_id
3047 ,publisher_site_id
3048 ,publisher_name
3049 ,publisher_site_name
3050 ,inventory_item_id
3051 ,quantity
3052 ,publisher_order_type
3053 ,receipt_date
3054 ,ship_date
3055 ,supplier_id
3056 ,supplier_name
3057 ,supplier_site_id
3058 ,supplier_site_name
3059 ,line_number
3060 ,order_number
3061 ,ship_to_party_id
3062 ,ship_to_party_site_id
3063 ,ship_to_party_name
3064 ,ship_to_party_site_name
3065 ,ship_from_party_id
3066 ,SHIP_FROM_PARTY_SITE_ID
3067 ,SHIP_FROM_PARTY_NAME
3068 ,SHIP_FROM_PARTY_SITE_NAME
3069 ,publisher_item_name
3070 ,pub_item_description
3071 ,uom_code
3072 ,publisher_order_type_desc
3073 ,bucket_type
3074 ,bucket_type_desc
3075 ,created_by
3076 ,creation_date
3077 ,last_updated_by
3078 ,last_update_date
3079 ,comments
3080 ,key_date
3081 ,item_name
3082 ,owner_item_name
3083 ,customer_item_name
3084 ,supplier_item_name
3085 ,item_description
3086 ,owner_item_description
3087 ,customer_item_description
3088 ,supplier_item_description
3089 ,primary_quantity
3090 ,tp_uom_code
3091 ,tp_quantity
3092 ,customer_id
3093 ,customer_site_id
3094 ,customer_name
3095 ,customer_site_name
3096 ,last_refresh_number
3097 ,release_number
3098 ,primary_uom
3099 ,new_order_placement_date
3100 , vmi_flag
3101 ,acceptance_required_flag
3102 ,need_by_date
3103 ,promised_date
3104 , base_item_id
3105 , base_item_name
3106 , internal_flag
3107 ,planner_code --Bug 4424426
3108 )values
3109 (
3110 t_ins_sr_instance_id(j),
3111 msc_sup_dem_entries_s.nextval,
3112 --t_ins_transaction_id(j),
3113 t_ins_plan_id(j),
3114 t_ins_pub_id(j),
3115 t_ins_pub_site_id(j),
3116 t_ins_pub(j),
3117 t_ins_pub_site(j),
3118 t_ins_item_id(j),
3119 round(t_ins_quantity(j),6),
3120 t_ins_order_type(j),
3121 t_ins_receipt_date(j),
3122 t_ins_ship_date(j),
3123 t_ins_supp_id(j),
3124 t_ins_supp(j),
3125 t_ins_supp_site_id(j),
3126 t_ins_supp_site(j),
3127 t_ins_line_num(j),
3128 t_ins_ord_num(j),
3129 t_ins_shipto_id(j),
3130 t_ins_shipto_site_id(j),
3131 t_ins_shipto(j),
3132 t_ins_shipto_site(j),
3133 t_ins_shipfrom_id(j),
3134 t_ins_shipfrom_site_id(j),
3135 t_ins_shipfrom(j),
3136 t_ins_shipfrom_site(j),
3137 t_ins_item_name(j),
3138 t_ins_item_desc(j),
3139 t_ins_uom(j),
3140 t_ins_ot_desc(j),
3141 t_ins_bkt_type(j),
3142 t_ins_bkt_type_desc(j),
3143 t_ins_created_by(j),
3144 t_ins_creation_date(j),
3145 t_ins_last_updated_by(j),
3146 t_ins_last_update_date(j),
3147 t_ins_comments(j),
3148 t_ins_key_date(j),
3149 t_ins_item_name(j),
3150 t_ins_owner_item_name(j),
3151 t_ins_customer_item_name(j),
3152 t_ins_supplier_item_name(j),
3153 t_ins_item_desc(j),
3154 t_ins_owner_item_desc(j),
3155 t_ins_cust_item_desc(j),
3156 t_ins_sup_item_desc(j),
3157 round(t_ins_quantity(j), 6),
3158 t_ins_tp_uom(j),
3159 round(t_ins_tp_quantity(j), 6),
3160 t_ins_shipto_id(j),
3161 t_ins_shipto_site_id(j),
3162 t_ins_shipto(j),
3163 t_ins_shipto_site(j),
3164 msc_cl_refresh_s.nextval,
3165 t_ins_release_number(j),
3166 t_ins_uom(j),
3167 t_new_ord_plac_date(j)
3168 , t_ins_vmi_flag(j)
3169 , t_ins_acceptance_required_flag(j)
3170 , t_ins_need_by_date(j)
3171 , t_ins_promised_date(j)
3172 , t_ins_base_item_id(j)
3173 , t_ins_base_item_name(j)
3174 ,t_ins_internal_flag(j)
3175 ,t_ins_planner_code(j)--Bug 4424426
3176 );
3177
3178 COMMIT;
3179
3180 EXCEPTION WHEN OTHERS THEN
3181 LOG_MESSAGE('Error while inserting records into msc_sup_dem_entries');
3182 LOG_MESSAGE(SQLERRM);
3183 ROLLBACK;
3184 RETCODE := G_ERROR;
3185 END;
3186
3187 END IF;
3188
3189 END IF; -- if v_lrtype = ......
3190
3191 --====================================
3192 -- Populate Unallocated On hand record
3193 --====================================
3194
3195 BEGIN
3196 IF (v_lrtype = 'I' OR
3197 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) = G_AUTO_NET_COLL)) THEN
3198 /* SBALA - Debug */
3199 LOG_MESSAGE('Fetching from unallocOnhandNetChange');
3200 LOG_MESSAGE('v_refresh_number = '||v_refresh_number||' p_sr_instance_id = '||p_sr_instance_id);
3201 /* SBALA - Debug */
3202
3203 OPEN unallocOnhandNetChange (v_oh_refresh_number,
3204 p_sr_instance_id,
3205 l_language_code);
3206
3207 FETCH unallocOnhandNetChange BULK COLLECT INTO
3208 t_plan_id,
3209 t_sr_instance_id,
3210 t_pub_id,
3211 t_pub_site_id,
3212 t_pub,
3213 t_pub_site,
3214 t_item_id ,
3215 t_quantity,
3216 t_comments,
3217 t_pub_order_type,
3218 t_bkt_type,
3219 t_item_name,
3220 t_item_desc ,
3221 t_ot_desc,
3222 t_tp_ot_desc,
3223 t_bkt_type_desc,
3224 t_uom,
3225 t_pri_uom,
3226 t_primary_quantity,
3227 t_base_item_id,
3228 t_base_item_name,
3229 t_planner_code;--Bug 4424426
3230
3231 CLOSE unallocOnhandNetChange ;
3232
3233 ELSE
3234
3235 IF ((v_lrtype = 'C') OR
3236 (v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
3237 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL)) THEN
3238
3239 OPEN unallocOnhand (v_oh_refresh_number,
3240 p_sr_instance_id,
3241 l_language_code);
3242 FETCH unallocOnhand BULK COLLECT INTO
3243 t_plan_id,
3244 t_sr_instance_id,
3245 t_pub_id,
3246 t_pub_site_id,
3247 t_pub,
3248 t_pub_site,
3249 t_item_id ,
3250 t_quantity,
3251 t_comments,
3252 t_pub_order_type,
3253 t_bkt_type,
3254 t_item_name,
3255 t_item_desc ,
3256 t_ot_desc,
3257 t_tp_ot_desc,
3258 t_bkt_type_desc,
3259 t_uom,
3260 t_pri_uom,
3261 t_primary_quantity,
3262 t_base_item_id,
3263 t_base_item_name,
3264 t_planner_code;--Bug 4424426
3265
3266
3267 CLOSE unallocOnhand;
3268
3269 END IF;
3270
3271 END IF;
3272 EXCEPTION WHEN OTHERS THEN
3273 LOG_MESSAGE('Error while fetching records from unallocOnhand cusrsor');
3274 LOG_MESSAGE(SQLERRM);
3275 RETCODE := G_ERROR;
3276 END;
3277
3278
3279 --=======================================
3280 -- Compute the values of derived columns
3281 -- before updation and insertion
3282 --=======================================
3283
3284 IF ((v_lrtype = 'C') OR
3285 (v_lrtype = 'I') OR
3286 (v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
3287 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL)) THEN
3288
3289 LOG_MESSAGE('Total records fetched for UnAllocated Onhand:'||t_plan_id.COUNT);
3290
3291
3292 FOR i IN 1..t_plan_id.COUNT LOOP
3293
3294 t_owner_item_name.EXTEND;
3295 t_owner_item_desc.EXTEND;
3296
3297
3298 t_owner_item_name(i) := t_item_name(i);
3299 t_owner_item_desc(i) := t_item_desc(i);
3300
3301
3302 END LOOP;
3303
3304 IF t_plan_id.COUNT > 0 THEN
3305
3306 BEGIN
3307 FORALL i in 1..t_plan_id.COUNT
3308
3309 update msc_sup_dem_entries
3310 set quantity = round(t_quantity(i), 6),
3311 bucket_type = t_bkt_type(i),
3312 uom_code = t_uom(i) ,
3313 primary_uom = t_pri_uom(i),
3314 primary_quantity = round(t_primary_quantity(i), 6),
3315 key_date = sysdate,
3316 new_schedule_date = sysdate,
3317 last_refresh_number = msc_cl_refresh_s.nextval ,
3318 last_update_date = l_sysdate,
3319 last_updated_by = p_user_id
3320 where plan_id = G_PLAN_ID
3321 and sr_instance_id = G_SR_INSTANCE_ID
3322 and publisher_id = t_pub_id(i)
3323 and publisher_site_id = t_pub_site_id(i)
3324 and inventory_item_id = t_item_id(i)
3325 and publisher_order_type = t_pub_order_type(i);
3326 COMMIT;
3327 EXCEPTION WHEN OTHERS THEN
3328 ROLLBACK;
3329 LOG_MESSAGE('ERROR while updating msc_up_dem_entries using allocOnhand');
3330 LOG_MESSAGE(SQLERRM);
3331 RETCODE := G_ERROR;
3332 END;
3333
3334
3335
3336 END IF;
3337
3338
3339 --==========================================================
3340 -- Insert the fetched records if the records are new records
3341 -- Step 1. Extend the insert variables.
3342 -- Step 2. BULK insert.
3343 --==========================================================
3344
3345 /* Initialize the count */
3346
3347 a_ins_count := null;
3348 a_ins_count := number_arr();
3349
3350 FOR i IN 1..t_plan_id.COUNT LOOP
3351 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
3352 a_ins_count.EXTEND;
3353 --t_ins_transaction_id.EXTEND;
3354 t_ins_plan_id.EXTEND;
3355 t_ins_sr_instance_id.EXTEND;
3356 t_ins_pub_id.EXTEND;
3357 t_ins_pub_site_id.EXTEND;
3358 t_ins_pub.EXTEND;
3359 t_ins_pub_site.EXTEND;
3360 --t_ins_new_sched_date.EXTEND;
3361 t_ins_item_id.EXTEND;
3362 t_ins_base_item_id.EXTEND;
3363 t_ins_quantity.EXTEND;
3364 t_ins_comments.EXTEND;
3365 t_ins_pub_order_type.EXTEND;
3366 t_ins_bkt_type.EXTEND;
3367 --t_ins_ord_num.EXTEND;
3368 --t_ins_new_dock_date.EXTEND;
3369 t_ins_item_name.EXTEND;
3370 t_ins_base_item_name.EXTEND;
3371 t_ins_item_desc.EXTEND;
3372 t_ins_ot_desc.EXTEND;
3373 t_ins_bkt_type_desc.EXTEND;
3374 t_ins_uom.EXTEND;
3375 --t_ins_created_by.EXTEND;
3376 --t_ins_creation_date.EXTEND;
3377 --t_ins_last_updated_by.EXTEND;
3378 --t_ins_last_update_date.EXTEND;
3379 --t_ins_key_date.EXTEND;
3380 t_ins_pri_uom.EXTEND;
3381 t_ins_primary_quantity.EXTEND;
3382
3383 --==================================
3384 -- Extend the Item related variables
3385 --==================================
3386
3387 t_ins_owner_item_name.EXTEND;
3388 ---t_ins_customer_item_name.EXTEND;
3389 ----t_ins_supplier_item_name.EXTEND;
3390 t_ins_owner_item_desc.EXTEND;
3391 ----t_ins_cust_item_desc.EXTEND;
3392 ----t_ins_tp_uom.EXTEND;
3393 ----t_ins_tp_quantity.EXTEND;
3394
3395 t_ins_planner_code.EXTEND;--Bug 4424426
3396
3397 a_ins_count(a_ins_count.COUNT) := i;
3398 --t_ins_transaction_id(a_ins_count.COUNT) := t_transaction_id(i);
3399 t_ins_plan_id(a_ins_count.COUNT) := t_plan_id(i) ;
3400 t_ins_sr_instance_id(a_ins_count.COUNT) := t_sr_instance_id(i);
3401 t_ins_pub_id(a_ins_count.COUNT) := t_pub_id(i);
3402 t_ins_pub_site_id(a_ins_count.COUNT) := t_pub_site_id(i);
3403 t_ins_pub(a_ins_count.COUNT) := t_pub(i);
3404 t_ins_pub_site(a_ins_count.COUNT) := t_pub_site(i);
3405 --t_ins_new_sched_date(a_ins_count.COUNT) := t_new_sched_date(i);
3406 t_ins_item_id(a_ins_count.COUNT) := t_item_id(i);
3407 t_ins_base_item_id(a_ins_count.COUNT) :=
3408 t_base_item_id(i);
3409 t_ins_quantity(a_ins_count.COUNT) := t_quantity(i);
3410 t_ins_comments(a_ins_count.COUNT) := t_comments(i);
3411 t_ins_pub_order_type(a_ins_count.COUNT) := t_pub_order_type(i);
3412 --t_ins_supp_id(a_ins_count.COUNT) := t_supp_id(i);
3413 --t_ins_supp(a_ins_count.COUNT) := t_supp(i);
3414 --t_ins_supp_site_id(a_ins_count.COUNT) := t_supp_site_id(i);
3415 --t_ins_supp_site(a_ins_count.COUNT) := t_supp_site(i);
3416 t_ins_bkt_type(a_ins_count.COUNT) := t_bkt_type(i);
3417 --t_ins_ord_num(a_ins_count.COUNT) := t_ord_num(i);
3418 --t_ins_new_dock_date(a_ins_count.COUNT) := t_new_dock_date(i);
3419 t_ins_item_name(a_ins_count.COUNT) := t_item_name(i);
3420 t_ins_base_item_name(a_ins_count.COUNT) :=
3421 t_base_item_name(i);
3422 t_ins_item_desc(a_ins_count.COUNT) := t_item_desc(i);
3423 t_ins_ot_desc(a_ins_count.COUNT) := t_ot_desc(i);
3424 ---t_ins_tp_ot_desc(a_ins_count.COUNT) := t_tp_ot_desc(i);
3425 t_ins_bkt_type_desc(a_ins_count.COUNT) := t_bkt_type_desc(i);
3426 t_ins_uom(a_ins_count.COUNT) := t_uom(i);
3427 --t_ins_created_by(a_ins_count.COUNT) := t_created_by(i);
3428 --t_ins_creation_date(a_ins_count.COUNT) := t_creation_date(i);
3429 --t_ins_last_updated_by(a_ins_count.COUNT) := t_last_updated_by(i);
3430 --t_ins_last_update_date(a_ins_count.COUNT):= t_last_update_date(i);
3431 --t_ins_key_date(a_ins_count.COUNT) := t_key_date(i);
3432 t_ins_pri_uom(a_ins_count.COUNT) := t_pri_uom(i);
3433 t_ins_primary_quantity(a_ins_count.COUNT) := t_primary_quantity(i);
3434 ---t_ins_customer_item_name(a_ins_count.COUNT) := t_customer_item_name(i);
3435 t_ins_owner_item_name(a_ins_count.COUNT) := t_owner_item_name(i);
3436 t_ins_owner_item_desc(a_ins_count.COUNT) := t_owner_item_desc(i);
3437
3438 t_ins_planner_code(a_ins_count.COUNT) := t_planner_code(i);--Bug 4424426
3439 --- t_ins_cust_item_desc(a_ins_count.COUNT) := t_cust_item_desc(i);
3440 ---- t_ins_supplier_item_name(a_ins_count.COUNT) := t_supplier_item_name(i);
3441 --- t_ins_tp_uom(a_ins_count.COUNT) := t_tp_uom(i);
3442 --- t_ins_tp_quantity(a_ins_count.COUNT) := t_tp_quantity(i);
3443 END IF;
3444 END LOOP;
3445
3446 LOG_MESSAGE('Total records for insertion for Unallocated Onhand:'||a_ins_count.COUNT);
3447
3448 -- ==================
3449 -- Insert the records
3450 -- ==================
3451 IF a_ins_count.COUNT > 0 THEN
3452
3453 BEGIN
3454 FORALL i IN 1..a_ins_count.COUNT
3455 INSERT INTO MSC_SUP_DEM_ENTRIES
3456 ( transaction_id,
3457 plan_id,
3458 sr_instance_id,
3459 publisher_id,
3460 publisher_site_id,
3461 publisher_name,
3462 publisher_site_name,
3463 new_schedule_date ,
3464 inventory_item_id ,
3465 quantity,
3466 comments,
3467 publisher_order_type,
3468 /** supplier_id,
3469 supplier_name,
3470 supplier_site_id,
3471 supplier_site_name, */
3472 bucket_type,
3473 --order_number,
3474 --new_dock_date,
3475 item_name,
3476 ITEM_DESCRIPTION,
3477 PUB_ITEM_DESCRIPTIION ,
3478 PUBLISHER_ORDER_TYPE_DESC,
3479 ---tp_order_type_desc,
3480 bucket_type_desc ,
3481 uom_code ,
3482 created_by,
3483 creation_date,
3484 last_updated_by,
3485 last_update_date,
3486 key_date,
3487 primary_uom,
3488 primary_quantity,
3489 /* tp_uom_code,
3490 tp_quantity, */
3491 /* customer_id,
3492 customer_site_id,
3493 customer_name,
3494 customer_site_name, */
3495 last_refresh_number,
3496 ---supplier_item_name,
3497 owner_item_name,
3498 ---customer_item_name,
3499 ---supplier_item_description,
3500 owner_item_description,
3501 ---customer_item_description
3502 base_item_id,
3503 base_item_name,
3504 planner_code--Bug 4424426
3505 )
3506 values
3507 ( msc_sup_dem_entries_s.nextval,
3508 t_ins_plan_id(i),
3509 t_ins_sr_instance_id(i),
3510 t_ins_pub_id(i),
3511 t_ins_pub_site_id(i),
3512 t_ins_pub(i),
3513 t_ins_pub_site(i),
3514 l_sysdate, --- new_schedule_date
3515 --t_ins_new_sched_date(i) ,
3516 t_ins_item_id(i) ,
3517 round(t_ins_quantity(i),6),
3518 t_ins_comments(i),
3519 t_ins_pub_order_type(i),
3520 /* t_ins_supp_id(i),
3521 t_ins_supp(i),
3522 t_ins_supp_site_id(i),
3523 t_ins_supp_site(i), */
3524 t_ins_bkt_type(i),
3525 --t_ins_ord_num(i),
3526 --t_ins_new_dock_date(i),
3527 t_ins_item_name(i),
3528 t_ins_item_desc(i) ,
3529 t_ins_item_desc(i) ,
3530 t_ins_ot_desc(i),
3531 ---t_tp_ot_desc(i),
3532 t_ins_bkt_type_desc(i) ,
3533 t_ins_uom(i) ,
3534 p_user_id, --t_ins_created_by(i),
3535 l_sysdate, --t_ins_creation_date(i),
3536 p_user_id, --t_ins_last_updated_by(i),
3537 l_sysdate, --t_ins_last_update_date(i),
3538 l_sysdate, --Key Date
3539 --t_ins_key_date(i),
3540 t_ins_pri_uom(i),
3541 round(t_ins_primary_quantity(i),6),
3542 /* t_ins_tp_uom(i),
3543 round(t_ins_tp_quantity(i), 6), */
3544 /* t_ins_pub_id(i),
3545 t_ins_pub_site_id(i),
3546 t_ins_pub(i),
3547 t_ins_pub_site(i), */
3548 msc_cl_refresh_s.nextval,
3549 ---t_ins_supplier_item_name(i),
3550 t_ins_owner_item_name(i),
3551 ----t_ins_customer_item_name(i),
3552 ---t_ins_supplier_item_name(i),
3553 t_ins_owner_item_desc(i),
3554 ---t_ins_cust_item_desc(i)
3555 t_ins_base_item_id(i),
3556 t_ins_base_item_name(i),
3557 t_ins_planner_code(i) --Bug 4424426
3558 );
3559
3560 COMMIT;
3561 EXCEPTION WHEN OTHERS THEN
3562 LOG_MESSAGE('ERROR while inserting from unallocOnhand to msc_sup_dem_entries ');
3563 LOG_MESSAGE(SQLERRM);
3564 ROLLBACK;
3565 RETCODE := G_ERROR;
3566 END;
3567 END IF;
3568
3569 END IF;
3570
3571 --===================================================================================
3572 -- Populate Allocated on hand record
3573 --===================================================================================
3574
3575 BEGIN
3576 IF ((v_lrtype = 'I') OR
3577 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) = G_AUTO_NET_COLL)) THEN
3578
3579 OPEN allocOnhandNetChange (v_oh_refresh_number,
3580 p_sr_instance_id,
3581 l_language_code);
3582 FETCH allocOnhandNetChange BULK COLLECT INTO
3583 t_plan_id,
3584 t_sr_instance_id,
3585 t_pub_id,
3586 t_pub_site_id,
3587 t_pub,
3588 t_pub_site,
3589 t_item_id ,
3590 t_quantity,
3591 t_comments,
3592 t_pub_order_type,
3593 t_supp_id,
3594 t_supp,
3595 t_supp_site_id,
3596 t_supp_site,
3597 t_bkt_type,
3598 t_item_name,
3599 t_item_desc ,
3600 t_ot_desc,
3601 t_tp_ot_desc,
3602 t_bkt_type_desc ,
3603 t_uom ,
3604 t_pri_uom,
3605 t_primary_quantity,
3606 t_partner_id,
3607 t_partner_site_id,
3608 t_orig_sr_instance_id,
3609 t_organization_id
3610 , t_vmi_flag
3611 , t_alloc_type
3612 , t_base_item_id
3613 , t_base_item_name
3614 ,t_planner_code; --Bug 4424426
3615
3616 CLOSE allocOnhandNetChange;
3617 ELSE
3618 IF ((v_lrtype = 'C') OR
3619 (v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
3620 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL)) THEN
3621
3622 OPEN allocOnhand (v_oh_refresh_number,
3623 p_sr_instance_id,
3624 l_language_code);
3625 FETCH allocOnhand BULK COLLECT INTO
3626 t_plan_id,
3627 t_sr_instance_id,
3628 t_pub_id,
3629 t_pub_site_id,
3630 t_pub,
3631 t_pub_site,
3632 t_item_id ,
3633 t_quantity,
3634 t_comments,
3635 t_pub_order_type,
3636 t_supp_id,
3637 t_supp,
3638 t_supp_site_id,
3639 t_supp_site,
3640 t_bkt_type,
3641 t_item_name,
3642 t_item_desc ,
3643 t_ot_desc,
3644 t_tp_ot_desc,
3645 t_bkt_type_desc ,
3646 t_uom ,
3647 t_pri_uom,
3648 t_primary_quantity,
3649 t_partner_id,
3650 t_partner_site_id,
3651 t_orig_sr_instance_id,
3652 t_organization_id
3653 , t_vmi_flag
3654 , t_alloc_type
3655 , t_base_item_id
3656 , t_base_item_name
3657 , t_planner_code; --Bug 4424426
3658
3659
3660
3661 CLOSE allocOnhand;
3662 END IF;
3663 END IF;
3664
3665 EXCEPTION WHEN OTHERS THEN
3666 LOG_MESSAGE('Error while fetching records from allocOnhand cusrsor');
3667 LOG_MESSAGE(SQLERRM);
3668 RETCODE := G_ERROR;
3669 END;
3670
3671
3672 --=======================================
3673 -- Compute the values of derived columns
3674 -- before updation and insertion
3675 --=======================================
3676
3677 IF ((v_lrtype = 'C') OR
3678 (v_lrtype = 'I') OR
3679 (v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
3680 (v_lrtype = 'T' AND nvl(p_oh_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL)) THEN
3681
3682 LOG_MESSAGE('Total records fetched for Allocated Onhand:'||t_plan_id.COUNT);
3683
3684 FOR i IN 1..t_plan_id.COUNT LOOP
3685
3686 --==================================
3687 -- Extend the Item related variables
3688 --==================================
3689
3690 t_owner_item_name.EXTEND;
3691 t_customer_item_name.EXTEND;
3692 t_supplier_item_name.EXTEND;
3693 t_owner_item_desc.EXTEND;
3694 t_cust_item_desc.EXTEND;
3695 t_tp_uom.EXTEND;
3696 t_tp_quantity.EXTEND;
3697 t_sup_item_desc.EXTEND;
3698
3699 --============================================
3700 -- Derive the Item cross reference information
3701 --============================================
3702
3703
3704 --====================
3705 -- Initialize variable
3706 --====================
3707 l_supplier_item_name := null;
3708 l_supplier_item_desc := null;
3709 l_tp_uom := null;
3710 l_lead_time := 0;
3711
3712 t_customer_item_name(i) := t_item_name(i);
3713 t_owner_item_name(i) := t_item_name(i);
3714 t_owner_item_desc(i) := t_item_desc(i);
3715 t_cust_item_desc(i) := t_item_desc(i);
3716
3717 IF (t_alloc_type(i) = G_SUPPLIER) then
3718
3719
3720 BEGIN
3721
3722 OPEN itemSuppliers(t_organization_id(i),
3723 t_orig_sr_instance_id(i),
3724 t_item_id(i),
3725 t_partner_id(i),
3726 t_partner_site_id(i));
3727
3728 FETCH itemSuppliers INTO l_supplier_item_name,
3729 l_lead_time,
3730 l_tp_uom,
3731 l_asl_vmi_flag,
3732 l_supplier_item_desc;
3733
3734 IF itemSuppliers%NOTFOUND THEN
3735 l_supplier_item_name := null;
3736 l_lead_time := 0;
3737 l_tp_uom := t_uom(i);
3738 l_asl_vmi_flag := 2;
3739 END IF;
3740
3741 CLOSE itemSuppliers;
3742
3743 EXCEPTION WHEN OTHERS THEN
3744 l_supplier_item_name := null;
3745 l_lead_time := 0;
3746 l_tp_uom := t_uom(i);
3747 l_asl_vmi_flag := 2;
3748 l_supplier_item_desc := null;
3749 END;
3750
3751 t_supplier_item_name(i) := l_supplier_item_name;
3752 t_tp_uom(i) := nvl(l_tp_uom, t_uom(i));
3753 t_sup_item_desc(i) := l_supplier_item_desc;
3754
3755 --==========================================
3756 -- Mark onhand as VMI onhand if
3757 -- 1. It's Allocated On Hand
3758 -- 2. Item has ASL with vmi_flag set to Yes.
3759 --==========================================
3760 t_vmi_flag(i) := nvl(l_asl_vmi_flag, 2);
3761
3762 ELSE /* Modeled Customer Case */
3763
3764 /* Added for bug# 3311573, set supplier_item_name as the OEM
3765 item name for Allocated onhand in the customer modeled org */
3766
3767 t_supplier_item_name(i) := t_item_name(i);
3768 t_sup_item_desc(i) := t_item_desc(i);
3769
3770 t_customer_item_name(i) := null;
3771
3772 /* Add code to get customer item name from msc_item customers */
3773
3774 BEGIN
3775
3776 /* sbala Using Fetching Customer item name,
3777 Using t_supp variables because that is
3778 what I get in the cursor */
3779
3780 select customer_item_name,
3781 description,
3782 uom_code
3783 into l_customer_item_name,
3784 l_customer_item_desc,
3785 l_tp_uom
3786 from msc_item_customers mic
3787 where mic.plan_id = G_PLAN_ID
3788 and mic.inventory_item_id = t_item_id(i)
3789 and mic.customer_id = t_supp_id(i)
3790 and nvl(mic.customer_site_id, -99) = decode(
3791 mic.customer_site_id,
3792 null, -99,
3793 t_supp_site_id(i));
3794
3795
3796 EXCEPTION WHEN OTHERS THEN
3797 l_customer_item_name := null;
3798 l_customer_item_desc := null;
3799 l_tp_uom := null;
3800 END;
3801 END IF;
3802
3803 t_customer_item_name(i) := l_customer_item_name;
3804 t_cust_item_desc(i) := l_customer_item_desc;
3805 t_tp_uom(i) := nvl(l_tp_uom, t_uom(i));
3806
3807
3808 --===============================================
3809 -- Get the conversion rate and derive tp_quantity
3810 --===============================================
3811 msc_x_util.get_uom_conversion_rates
3812 (t_uom(i),
3813 t_tp_uom(i),
3814 t_item_id(i),
3815 l_conversion_found,
3816 l_conversion_rate);
3817
3818 IF l_conversion_found THEN
3819 t_tp_quantity(i) := t_quantity(i) * l_conversion_rate;
3820 ELSE
3821 t_tp_quantity(i) := t_quantity(i);
3822 END IF;
3823
3824 END LOOP;
3825
3826 --===========================
3827 -- Update the fetched records
3828 --===========================
3829
3830 IF t_plan_id.COUNT > 0 THEN
3831
3832 BEGIN
3833
3834 FORALL i in 1..t_plan_id.COUNT
3835
3836 update msc_sup_dem_entries
3837 set quantity = round(t_quantity(i), 6),
3838 bucket_type = t_bkt_type(i),
3839 uom_code = t_uom(i) ,
3840 primary_uom = t_pri_uom(i),
3841 primary_quantity = round(t_primary_quantity(i), 6),
3842 key_date = sysdate,
3843 new_schedule_date = sysdate,
3844 last_refresh_number = msc_cl_refresh_s.nextval ,
3845 last_update_date = l_sysdate,
3846 last_updated_by = p_user_id,
3847 tp_quantity = round(t_tp_quantity(i), 6),
3848 tp_uom_code = t_tp_uom(i),
3849 supplier_item_name = t_supplier_item_name(i)
3850 where plan_id = G_PLAN_ID
3851 and sr_instance_id = G_SR_INSTANCE_ID
3852 and publisher_id = t_pub_id(i)
3853 and publisher_site_id = t_pub_site_id(i)
3854 and inventory_item_id = t_item_id(i)
3855 and publisher_order_type = t_pub_order_type(i)
3856 and supplier_id = t_supp_id(i)
3857 and supplier_site_id = t_supp_site_id(i)
3858 and t_alloc_type(i) = G_SUPPLIER;
3859 --and nvl(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(i), G_NULL_STRING)
3860 --and nvl(order_number, G_NULL_STRING)= NVL(t_ord_num(i), G_NULL_STRING)
3861 --and nvl(key_date, sysdate) = nvl(t_key_date(i), sysdate);
3862
3863 COMMIT;
3864 EXCEPTION WHEN OTHERS THEN
3865 ROLLBACK;
3866 LOG_MESSAGE('ERROR while updating msc_up_dem_entries using allocOnhand');
3867 LOG_MESSAGE(SQLERRM);
3868 RETCODE := G_ERROR;
3869 END;
3870
3871 FOR i IN 1..t_plan_id.COUNT LOOP
3872 a_supplier_update.EXTEND;
3873 a_supplier_update(i) := SQL%BULK_ROWCOUNT(i);
3874 END LOOP;
3875
3876 BEGIN
3877 /* sbala: Added update for Allocation type G_CUSTOMER
3878 * Keeping update separate to ensure indexes are used in update
3879 ** If no performance hit, the SQL's can be merged
3880 */
3881 FORALL i in 1..t_plan_id.COUNT
3882
3883 update msc_sup_dem_entries
3884 set quantity = round(t_quantity(i), 6),
3885 bucket_type = t_bkt_type(i),
3886 uom_code = t_uom(i) ,
3887 primary_uom = t_pri_uom(i),
3888 primary_quantity = round(t_primary_quantity(i), 6),
3889 key_date = sysdate,
3890 new_schedule_date = sysdate,
3891 last_refresh_number = msc_cl_refresh_s.nextval ,
3892 last_update_date = l_sysdate,
3893 last_updated_by = p_user_id,
3894 tp_quantity = round(t_tp_quantity(i), 6),
3895 tp_uom_code = t_tp_uom(i),
3896 /* sbala added */
3897 customer_item_name = t_customer_item_name(i)
3898 where plan_id = G_PLAN_ID
3899 and sr_instance_id = G_SR_INSTANCE_ID
3900 and publisher_id = t_pub_id(i)
3901 and publisher_site_id = t_pub_site_id(i)
3902 and inventory_item_id = t_item_id(i)
3903 and publisher_order_type = t_pub_order_type(i)
3904 /* sbala changes, keep t_supp_id, t_supp_site_id
3905 ** variables itself since they are populate with
3906 ** cust id from the SQL */
3907 and customer_id = t_supp_id(i)
3908 and customer_site_id = t_supp_site_id(i)
3909 and t_alloc_type(i) = G_CUSTOMER;
3910 --and nvl(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(i), G_NULL_STRING)
3911 --and nvl(order_number, G_NULL_STRING)= NVL(t_ord_num(i), G_NULL_STRING)
3912 --and nvl(key_date, sysdate) = nvl(t_key_date(i), sysdate);
3913
3914 COMMIT;
3915 EXCEPTION WHEN OTHERS THEN
3916 ROLLBACK;
3917 LOG_MESSAGE('ERROR while updating msc_up_dem_entries using allocOnhand');
3918 LOG_MESSAGE(SQLERRM);
3919 RETCODE := G_ERROR;
3920 END;
3921
3922 FOR i IN 1..t_plan_id.COUNT LOOP
3923 a_customer_update.EXTEND;
3924 a_customer_update(i) := SQL%BULK_ROWCOUNT(i);
3925 END LOOP;
3926
3927 FOR i IN 1..t_plan_id.COUNT LOOP
3928 a_resultant_update.EXTEND;
3929 a_resultant_update(i) := a_supplier_update(i) + a_customer_update(i);
3930 END LOOP;
3931
3932 END IF;
3933
3934 --==========================================================
3935 -- Insert the fetched records if the records are new records
3936 -- Step 1. Extend the insert variables.
3937 -- Step 2. BULK insert.
3938 --==========================================================
3939
3940 /* Initialize the count */
3941
3942 a_ins_count := null;
3943 a_ins_count := number_arr();
3944
3945 FOR i IN 1..t_plan_id.COUNT LOOP
3946 IF (a_resultant_update(i) = 0) THEN
3947 a_ins_count.EXTEND;
3948 --t_ins_transaction_id.EXTEND;
3949 t_ins_plan_id.EXTEND;
3950 t_ins_sr_instance_id.EXTEND;
3951 t_ins_pub_id.EXTEND;
3952 t_ins_pub_site_id.EXTEND;
3953 t_ins_pub.EXTEND;
3954 t_ins_pub_site.EXTEND;
3955 --t_ins_new_sched_date.EXTEND;
3956 t_ins_item_id.EXTEND;
3957 t_ins_base_item_id.EXTEND;
3958 t_ins_quantity.EXTEND;
3959 t_ins_comments.EXTEND;
3960 t_ins_pub_order_type.EXTEND;
3961 t_ins_supp_id.EXTEND;
3962 t_ins_supp.EXTEND;
3963 t_ins_supp_site_id.EXTEND;
3964 t_ins_supp_site.EXTEND;
3965 t_ins_bkt_type.EXTEND;
3966 --t_ins_ord_num.EXTEND;
3967 --t_ins_new_dock_date.EXTEND;
3968 t_ins_item_name.EXTEND;
3969 t_ins_base_item_name.EXTEND;
3970 t_ins_item_desc.EXTEND;
3971 t_ins_ot_desc.EXTEND;
3972 t_ins_tp_ot_desc.EXTEND;
3973 t_ins_bkt_type_desc.EXTEND;
3974 t_ins_uom.EXTEND;
3975 --t_ins_created_by.EXTEND;
3976 --t_ins_creation_date.EXTEND;
3977 --t_ins_last_updated_by.EXTEND;
3978 --t_ins_last_update_date.EXTEND;
3979 --t_ins_key_date.EXTEND;
3980 t_ins_pri_uom.EXTEND;
3981 t_ins_primary_quantity.EXTEND;
3982 t_ins_vmi_flag.EXTEND;
3983
3984 --==================================
3985 -- Extend the Item related variables
3986 --==================================
3987
3988 t_ins_owner_item_name.EXTEND;
3989 t_ins_customer_item_name.EXTEND;
3990 t_ins_supplier_item_name.EXTEND;
3991 t_ins_owner_item_desc.EXTEND;
3992 t_ins_cust_item_desc.EXTEND;
3993 t_ins_tp_uom.EXTEND;
3994 t_ins_tp_quantity.EXTEND;
3995 t_ins_alloc_type.EXTEND;
3996 t_ins_sup_item_desc.EXTEND;
3997 t_ins_planner_code.EXTEND; --Bug 4424426
3998
3999 a_ins_count(a_ins_count.COUNT) := i;
4000 --t_ins_transaction_id(a_ins_count.COUNT) := t_transaction_id(i);
4001 t_ins_plan_id(a_ins_count.COUNT) := t_plan_id(i) ;
4002 t_ins_sr_instance_id(a_ins_count.COUNT) := t_sr_instance_id(i);
4003 t_ins_pub_id(a_ins_count.COUNT) := t_pub_id(i);
4004 t_ins_pub_site_id(a_ins_count.COUNT) := t_pub_site_id(i);
4005 t_ins_pub(a_ins_count.COUNT) := t_pub(i);
4006 t_ins_pub_site(a_ins_count.COUNT) := t_pub_site(i);
4007 --t_ins_new_sched_date(a_ins_count.COUNT) := t_new_sched_date(i);
4008 t_ins_item_id(a_ins_count.COUNT) := t_item_id(i);
4009 t_ins_base_item_id(a_ins_count.COUNT) := t_base_item_id(i);
4010 t_ins_quantity(a_ins_count.COUNT) := t_quantity(i);
4011 t_ins_comments(a_ins_count.COUNT) := t_comments(i);
4012 t_ins_pub_order_type(a_ins_count.COUNT) := t_pub_order_type(i);
4013 t_ins_supp_id(a_ins_count.COUNT) := t_supp_id(i);
4014 t_ins_supp(a_ins_count.COUNT) := t_supp(i);
4015 t_ins_supp_site_id(a_ins_count.COUNT) := t_supp_site_id(i);
4016 t_ins_supp_site(a_ins_count.COUNT) := t_supp_site(i);
4017 t_ins_bkt_type(a_ins_count.COUNT) := t_bkt_type(i);
4018 --t_ins_ord_num(a_ins_count.COUNT) := t_ord_num(i);
4019 --t_ins_new_dock_date(a_ins_count.COUNT) := t_new_dock_date(i);
4020 t_ins_item_name(a_ins_count.COUNT) := t_item_name(i);
4021 t_ins_base_item_name(a_ins_count.COUNT) := t_base_item_name(i);
4022 t_ins_item_desc(a_ins_count.COUNT) := t_item_desc(i);
4023 t_ins_ot_desc(a_ins_count.COUNT) := t_ot_desc(i);
4024 t_ins_tp_ot_desc(a_ins_count.COUNT) := t_tp_ot_desc(i);
4025 t_ins_bkt_type_desc(a_ins_count.COUNT) := t_bkt_type_desc(i);
4026 t_ins_uom(a_ins_count.COUNT) := t_uom(i);
4027 --t_ins_created_by(a_ins_count.COUNT) := t_created_by(i);
4028 --t_ins_creation_date(a_ins_count.COUNT) := t_creation_date(i);
4029 --t_ins_last_updated_by(a_ins_count.COUNT) := t_last_updated_by(i);
4030 --t_ins_last_update_date(a_ins_count.COUNT):= t_last_update_date(i);
4031 --t_ins_key_date(a_ins_count.COUNT) := t_key_date(i);
4032 t_ins_pri_uom(a_ins_count.COUNT) := t_pri_uom(i);
4033 t_ins_primary_quantity(a_ins_count.COUNT) := t_primary_quantity(i);
4034 t_ins_customer_item_name(a_ins_count.COUNT) := t_customer_item_name(i);
4035 t_ins_owner_item_name(a_ins_count.COUNT) := t_owner_item_name(i);
4036 t_ins_owner_item_desc(a_ins_count.COUNT) := t_owner_item_desc(i);
4037 t_ins_cust_item_desc(a_ins_count.COUNT) := t_cust_item_desc(i);
4038 t_ins_supplier_item_name(a_ins_count.COUNT) := t_supplier_item_name(i);
4039 t_ins_sup_item_desc(a_ins_count.COUNT) := t_sup_item_desc(i);
4040 /* Derive supplier item description */
4041 if (t_ins_supplier_item_name(a_ins_count.COUNT) IS NOT NULL AND
4042 t_ins_sup_item_desc(a_ins_count.COUNT) IS NULL) THEN
4043 t_ins_sup_item_desc(a_ins_count.COUNT) := t_owner_item_desc(i);
4044 end if;
4045
4046 t_ins_tp_uom(a_ins_count.COUNT) := t_tp_uom(i);
4047 t_ins_tp_quantity(a_ins_count.COUNT) := t_tp_quantity(i);
4048 t_ins_vmi_flag(a_ins_count.COUNT) := t_vmi_flag(i);
4049 t_ins_alloc_type(a_ins_count.COUNT) := t_alloc_type(i);
4050
4051 t_ins_planner_code(a_ins_count.COUNT) := t_planner_code(i); --Bug 4424426
4052 END IF;
4053 END LOOP;
4054
4055 LOG_MESSAGE('Total records for insertion for Allocated Onhand:'||a_ins_count.COUNT);
4056
4057 -- ==================
4058 -- Insert the records
4059 -- ==================
4060 IF a_ins_count.COUNT > 0 THEN
4061
4062 BEGIN
4063 FORALL i IN 1..a_ins_count.COUNT
4064 INSERT INTO MSC_SUP_DEM_ENTRIES
4065 ( transaction_id,
4066 plan_id,
4067 sr_instance_id,
4068 publisher_id,
4069 publisher_site_id,
4070 publisher_name,
4071 publisher_site_name,
4072 new_schedule_date ,
4073 inventory_item_id ,
4074 quantity,
4075 comments,
4076 publisher_order_type,
4077 supplier_id,
4078 supplier_name,
4079 supplier_site_id,
4080 supplier_site_name,
4081 bucket_type,
4082 --order_number,
4083 --new_dock_date,
4084 item_name,
4085 ITEM_DESCRIPTION,
4086 PUB_ITEM_DESCRIPTIION ,
4087 PUBLISHER_ORDER_TYPE_DESC,
4088 tp_order_type_desc,
4089 bucket_type_desc ,
4090 uom_code ,
4091 created_by,
4092 creation_date,
4093 last_updated_by,
4094 last_update_date,
4095 key_date,
4096 primary_uom,
4097 primary_quantity,
4098 tp_uom_code,
4099 tp_quantity,
4100 customer_id,
4101 customer_site_id,
4102 customer_name,
4103 customer_site_name,
4104 last_refresh_number,
4105 supplier_item_name,
4106 owner_item_name,
4107 customer_item_name,
4108 supplier_item_description,
4109 owner_item_description,
4110 customer_item_description
4111 , vmi_flag
4112 , base_item_id
4113 , base_item_name
4114 ,planner_code --Bug 4424426
4115 )
4116 values
4117 ( msc_sup_dem_entries_s.nextval,
4118 t_ins_plan_id(i),
4119 t_ins_sr_instance_id(i),
4120 t_ins_pub_id(i),
4121 t_ins_pub_site_id(i),
4122 t_ins_pub(i),
4123 t_ins_pub_site(i),
4124 l_sysdate, --new_schedule_date
4125 --t_ins_new_sched_date(i) ,
4126 t_ins_item_id(i) ,
4127 round(t_ins_quantity(i),6),
4128 t_ins_comments(i),
4129 t_ins_pub_order_type(i),
4130 DECODE(t_ins_alloc_type(i),
4131 G_SUPPLIER, t_ins_supp_id(i),
4132 t_ins_pub_id(i)),
4133 DECODE(t_ins_alloc_type(i),
4134 G_SUPPLIER, t_ins_supp(i),
4135 t_ins_pub(i)),
4136 DECODE(t_ins_alloc_type(i),
4137 G_SUPPLIER, t_ins_supp_site_id(i),
4138 t_ins_pub_site_id(i)),
4139 DECODE(t_ins_alloc_type(i),
4140 G_SUPPLIER, t_ins_supp_site(i),
4141 t_ins_pub_site(i)),
4142 t_ins_bkt_type(i),
4143 --t_ins_ord_num(i),
4144 --t_ins_new_dock_date(i),
4145 t_ins_item_name(i),
4146 t_ins_item_desc(i) ,
4147 t_ins_item_desc(i) ,
4148 t_ins_ot_desc(i),
4149 t_tp_ot_desc(i),
4150 t_ins_bkt_type_desc(i) ,
4151 t_ins_uom(i) ,
4152 p_user_id, --t_ins_created_by(i),
4153 l_sysdate, --t_ins_creation_date(i),
4154 p_user_id, --t_ins_last_updated_by(i),
4155 l_sysdate, --t_ins_last_update_date(i),
4156 l_sysdate, --Key Date -> It's SYSDATE for OnHand Type of Order Types.
4157 t_ins_pri_uom(i),
4158 round(t_ins_primary_quantity(i),6),
4159 t_ins_tp_uom(i),
4160 round(t_ins_tp_quantity(i), 6),
4161 DECODE(t_ins_alloc_type(i),
4162 G_SUPPLIER, t_ins_pub_id(i),
4163 t_ins_supp_id(i)),
4164 DECODE(t_ins_alloc_type(i),
4165 G_SUPPLIER, t_ins_pub_site_id(i),
4166 t_ins_supp_site_id(i)),
4167 DECODE(t_ins_alloc_type(i),
4168 G_SUPPLIER, t_ins_pub(i),
4169 t_ins_supp(i)),
4170 DECODE(t_ins_alloc_type(i),
4171 G_SUPPLIER, t_ins_pub_site(i),
4172 t_ins_supp_site(i)),
4173 msc_cl_refresh_s.nextval,
4174 t_ins_supplier_item_name(i),
4175 t_ins_owner_item_name(i),
4176 t_ins_customer_item_name(i),
4177 t_ins_sup_item_desc(i),
4178 t_ins_owner_item_desc(i),
4179 t_ins_cust_item_desc(i)
4180 , t_ins_vmi_flag(i)
4181 , t_ins_base_item_id(i)
4182 , t_ins_base_item_name(i)
4183 , t_planner_code(i) --Bug 4424426
4184 );
4185
4186 COMMIT;
4187 EXCEPTION WHEN OTHERS THEN
4188 LOG_MESSAGE('ERROR while inserting from allocOnhand to msc_sup_dem_entries ');
4189 LOG_MESSAGE(SQLERRM);
4190 ROLLBACK;
4191 RETCODE := G_ERROR;
4192 END;
4193 END IF;
4194
4195 END IF; -- if v_lrtype = ...
4196
4197 --=============================
4198 -- Populate Sales Order Records
4199 --=============================
4200 IF ((v_lrtype = 'C') OR
4201 (v_lrtype = 'I') OR
4202 (v_lrtype = 'P' AND p_so_enabled_flag = MSC_CL_COLLECTION.SYS_YES) OR
4203 (v_lrtype = 'T' AND nvl(p_so_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL)
4204 ) THEN
4205
4206 BEGIN
4207 OPEN salesOrders (v_so_refresh_number,
4208 p_sr_instance_id,
4209 l_language_code);
4210 FETCH salesOrders BULK COLLECT INTO
4211 -- t_transaction_id,
4212 t_plan_id,
4213 t_sr_instance_id,
4214 t_pub_id,
4215 t_pub_site_id,
4216 t_pub,
4217 t_pub_site,
4218 t_item_id ,
4219 t_quantity,
4220 t_comments,
4221 t_pub_order_type,
4222 t_customer_id,
4223 t_customer_name,
4224 t_customer_site_id,
4225 t_customer_site_name,
4226 t_bkt_type,
4227 t_ord_num,
4228 t_line_num,
4229 t_ship_date,
4230 t_receipt_date,
4231 t_promise_date,
4232 t_item_name,
4233 t_item_desc ,
4234 t_ot_desc,
4235 t_tp_ot_desc,
4236 t_bkt_type_desc ,
4237 t_uom ,
4238 t_created_by,
4239 t_creation_date,
4240 t_last_updated_by,
4241 t_last_update_date,
4242 t_key_date,
4243 t_shipping_control,
4244 t_pri_uom,
4245 t_primary_quantity,
4246 t_partner_id,
4247 t_partner_site_id,
4248 t_orig_sr_instance_id,
4249 t_organization_id,
4250 t_base_item_id,
4251 t_base_item_name,
4252 t_end_order_number,
4253 t_end_order_rel_number,
4254 t_end_order_line_number,
4255 t_end_ord_pub_id,
4256 t_end_ord_pub_name,
4257 t_end_ord_pub_site_id,
4258 t_end_ord_pub_site_name,
4259 t_end_pub_ord_type,
4260 t_internal_flag,
4261 t_supp_id,
4262 t_supp_site_id,
4263 t_supp,
4264 t_supp_site,
4265 t_planner_code; --Bug 4424426
4266 CLOSE salesOrders;
4267 EXCEPTION WHEN OTHERS THEN
4268 LOG_MESSAGE('Error while fetching records from salesOrders cusrsor');
4269 LOG_MESSAGE(SQLERRM);
4270 RETCODE := G_ERROR;
4271 END;
4272
4273 LOG_MESSAGE('Total records fetched for Sales Orders :'||t_plan_id.COUNT);
4274
4275 --===========================
4276 -- Update the fetched records
4277 --===========================
4278
4279 IF t_plan_id.COUNT > 0 THEN
4280
4281 BEGIN
4282
4283 FORALL i in 1..t_plan_id.COUNT
4284
4285 update msc_sup_dem_entries
4286 set quantity = round(t_quantity(i), 6),
4287 bucket_type = t_bkt_type(i),
4288 uom_code = t_uom(i) ,
4289 primary_uom = t_pri_uom(i),
4290 primary_quantity = round(t_primary_quantity(i), 6),
4291 shipping_control = t_shipping_control(i),
4292 shipping_control_code = decode(t_shipping_control(i),G_ARRIVE_CONTROL,1,
4293 2),
4294 ship_date = t_ship_date(i),
4295 receipt_date = t_receipt_date(i),
4296 key_date = t_key_date(i),
4297 last_refresh_number = msc_cl_refresh_s.nextval,
4298 end_order_number = t_end_order_number(i),
4299 end_order_line_number = t_end_order_line_number(i),
4300 end_order_rel_number = t_end_order_rel_number(i),
4301 end_order_publisher_id = t_end_ord_pub_id(i),
4302 end_order_publisher_site_id = t_end_ord_pub_site_id(i),
4303 end_order_publisher_name = t_end_ord_pub_name(i),
4304 end_order_publisher_site_name = t_end_ord_pub_site_name(i),
4305 end_order_type = t_end_pub_ord_type(i),
4306 internal_flag = t_internal_flag(i)
4307 where plan_id = G_PLAN_ID
4308 and sr_instance_id = G_SR_INSTANCE_ID
4309 and publisher_id = t_pub_id(i)
4310 and publisher_site_id = t_pub_site_id(i)
4311 and inventory_item_id = t_item_id(i)
4312 and publisher_order_type = t_pub_order_type(i)
4313 and customer_id = t_customer_id(i)
4314 and customer_site_id = t_customer_site_id(i)
4315 and nvl(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(i), G_NULL_STRING)
4316 and nvl(order_number, G_NULL_STRING)= NVL(t_ord_num(i), G_NULL_STRING)
4317 and nvl(line_number, G_NULL_STRING)= NVL(t_line_num(i), G_NULL_STRING)
4318 --and nvl(key_date, sysdate) = nvl(t_key_date(i), sysdate)
4319 ;
4320
4321 COMMIT;
4322 EXCEPTION WHEN OTHERS THEN
4323 ROLLBACK;
4324 LOG_MESSAGE('ERROR while updating msc_up_dem_entries using Sales Orders');
4325 LOG_MESSAGE(SQLERRM);
4326 RETCODE := G_ERROR;
4327 END;
4328
4329 END IF;
4330
4331 --==========================================================
4332 -- Insert the fetched records if the records are new records
4333 -- Step 1. Extend the insert variables.
4334 -- Step 2. BULK insert.
4335 --==========================================================
4336
4337 /* Initialize the count */
4338
4339 a_ins_count := null;
4340 a_ins_count := number_arr();
4341
4342 FOR i IN 1..t_plan_id.COUNT LOOP
4343 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
4344 a_ins_count.EXTEND;
4345 t_ins_transaction_id.EXTEND;
4346 t_ins_plan_id.EXTEND;
4347 t_ins_sr_instance_id.EXTEND;
4348 t_ins_pub_id.EXTEND;
4349 t_ins_pub_site_id.EXTEND;
4350 t_ins_pub.EXTEND;
4351 t_ins_pub_site.EXTEND;
4352 t_ins_item_id.EXTEND;
4353 t_ins_base_item_id.EXTEND;
4354 t_ins_quantity.EXTEND;
4355 t_ins_comments.EXTEND;
4356 t_ins_pub_order_type.EXTEND;
4357 t_ins_customer_id.EXTEND;
4358 t_ins_customer_name.EXTEND;
4359 t_ins_customer_site_id.EXTEND;
4360 t_ins_customer_site_name.EXTEND;
4361 t_ins_bkt_type.EXTEND;
4362 t_ins_ord_num.EXTEND;
4363 t_ins_line_num.EXTEND;
4364 t_ins_ship_date.EXTEND;
4365 t_ins_receipt_date.EXTEND;
4366 t_ins_promise_date.EXTEND;
4367 t_ins_item_name.EXTEND;
4368 t_ins_base_item_name.EXTEND;
4369 t_ins_item_desc.EXTEND;
4370 t_ins_ot_desc.EXTEND;
4371 t_ins_tp_ot_desc.EXTEND;
4372 t_ins_bkt_type_desc.EXTEND;
4373 t_ins_uom.EXTEND;
4374 t_ins_created_by.EXTEND;
4375 t_ins_creation_date.EXTEND;
4376 t_ins_last_updated_by.EXTEND;
4377 t_ins_last_update_date.EXTEND;
4378 t_ins_key_date.EXTEND;
4379 t_ins_pri_uom.EXTEND;
4380 t_ins_primary_quantity.EXTEND;
4381 t_ins_tp_uom.EXTEND;
4382 t_ins_tp_quantity.EXTEND;
4383 t_ins_end_ord_num.EXTEND;
4384 t_ins_end_ord_line_num.EXTEND;
4385 t_ins_end_ord_rel_num.EXTEND;
4386 t_ins_internal_flag.EXTEND;
4387 t_ins_end_ord_pub_id.EXTEND;
4388 t_ins_end_ord_pub_name.EXTEND;
4389 t_ins_end_ord_pub_site_id.EXTEND;
4390 t_ins_end_ord_pub_site_name.EXTEND;
4391 t_ins_end_pub_ord_type.EXTEND;
4392 t_ins_supp.EXTEND;
4393 t_ins_supp_id.EXTEND;
4394 t_ins_supp_site_id.EXTEND;
4395 t_ins_supp_site.EXTEND;
4396 t_ins_shipping_control.EXTEND;
4397 t_ins_shipping_control_code.EXTEND;
4398 t_ins_end_ord_type_desc.EXTEND;
4399 t_ins_planner_code.EXTEND; --Bug 4424426
4400
4401 a_ins_count(a_ins_count.COUNT) := i;
4402 --t_ins_transaction_id(a_ins_count.COUNT):= t_transaction_id(i);
4403 t_ins_plan_id(a_ins_count.COUNT) := t_plan_id(i);
4404 t_ins_sr_instance_id(a_ins_count.COUNT):= t_sr_instance_id(i);
4405 t_ins_pub_id(a_ins_count.COUNT) := t_pub_id(i);
4406 t_ins_pub_site_id(a_ins_count.COUNT) := t_pub_site_id(i);
4407 t_ins_pub(a_ins_count.COUNT) := t_pub(i);
4408 t_ins_pub_site(a_ins_count.COUNT) := t_pub_site(i);
4409 t_ins_item_id(a_ins_count.COUNT) := t_item_id(i);
4410 t_ins_base_item_id(a_ins_count.COUNT) :=
4411 t_base_item_id(i);
4412 t_ins_quantity(a_ins_count.COUNT) := t_quantity(i);
4413 t_ins_comments(a_ins_count.COUNT) := t_comments(i);
4414 t_ins_pub_order_type(a_ins_count.COUNT):= t_pub_order_type(i);
4415 t_ins_customer_id(a_ins_count.COUNT) := t_customer_id(i);
4416 t_ins_customer_name(a_ins_count.COUNT) := t_customer_name(i);
4417 t_ins_customer_site_id(a_ins_count.COUNT):= t_customer_site_id(i);
4418 t_ins_customer_site_name(a_ins_count.COUNT):= t_customer_site_name(i);
4419 t_ins_bkt_type(a_ins_count.COUNT) := t_bkt_type(i);
4420 t_ins_ord_num(a_ins_count.COUNT) := t_ord_num(i);
4421 t_ins_line_num(a_ins_count.COUNT) := t_line_num(i);
4422 t_ins_ship_date(a_ins_count.COUNT) := t_ship_date(i);
4423 t_ins_receipt_date(a_ins_count.COUNT) := t_receipt_date(i);
4424 t_ins_promise_date(a_ins_count.COUNT) := t_promise_date(i);
4425 t_ins_item_name(a_ins_count.COUNT) := t_item_name(i);
4426 t_ins_base_item_name(a_ins_count.COUNT) :=
4427 t_base_item_name(i);
4428 t_ins_item_desc(a_ins_count.COUNT) := t_item_desc(i);
4429 t_ins_ot_desc(a_ins_count.COUNT) := t_ot_desc(i);
4430 t_ins_tp_ot_desc(a_ins_count.COUNT) := t_tp_ot_desc(i);
4431 t_ins_bkt_type_desc(a_ins_count.COUNT) := t_bkt_type_desc(i);
4432 t_ins_uom(a_ins_count.COUNT) := t_uom(i);
4433 t_ins_created_by(a_ins_count.COUNT) := t_created_by(i);
4434 t_ins_creation_date(a_ins_count.COUNT) := t_creation_date(i);
4435 t_ins_last_updated_by(a_ins_count.COUNT):= t_last_updated_by(i);
4436 t_ins_last_update_date(a_ins_count.COUNT):= t_last_update_date(i);
4437 t_ins_key_date(a_ins_count.COUNT) := t_key_date(i);
4438 t_ins_pri_uom(a_ins_count.COUNT) := t_pri_uom(i);
4439 t_ins_primary_quantity(a_ins_count.COUNT):= t_primary_quantity(i);
4440 t_ins_end_ord_num(a_ins_count.COUNT) := t_end_order_number(i);
4441 t_ins_end_ord_line_num(a_ins_count.COUNT) :=
4442 t_end_order_line_number(i);
4443 t_ins_end_ord_rel_num(a_ins_count.COUNT) := t_end_order_rel_number(i);
4444 t_ins_end_ord_pub_id(a_ins_count.COUNT) :=
4445 t_end_ord_pub_id(i);
4446
4447 t_ins_end_ord_pub_name(a_ins_count.COUNT) :=
4448 t_end_ord_pub_name(i);
4449
4450 t_ins_end_ord_pub_site_id(a_ins_count.COUNT) :=
4451 t_end_ord_pub_site_id(i);
4452
4453 t_ins_end_ord_pub_site_name(a_ins_count.COUNT) :=
4454 t_end_ord_pub_site_name(i);
4455
4456 t_ins_end_pub_ord_type(a_ins_count.COUNT) :=
4457 t_end_pub_ord_type(i);
4458
4459 t_ins_internal_flag(a_ins_count.COUNT) :=
4460 t_internal_flag(i);
4461
4462 t_ins_supp_id(a_ins_count.COUNT) := t_supp_id(i);
4463 t_ins_supp(a_ins_count.COUNT) := t_supp(i);
4464 t_ins_supp_site_id(a_ins_count.COUNT) := t_supp_site_id(i);
4465 t_ins_supp_site(a_ins_count.COUNT) := t_supp_site(i);
4466 t_ins_shipping_control(a_ins_count.COUNT) := t_shipping_control(i);
4467
4468 t_ins_planner_code(a_ins_count.COUNT) := t_planner_code(i); --Bug 4424426
4469
4470
4471 if (t_shipping_control(a_ins_count.COUNT) = G_ARRIVE_CONTROL) then
4472 /* arrive */
4473 t_ins_shipping_control_code(a_ins_count.COUNT) := 1;
4474 else
4475 /* ship */
4476 t_ins_shipping_control_code(a_ins_count.COUNT) := 2;
4477 end if;
4478
4479 if (t_ins_end_pub_ord_type(a_ins_count.COUNT) = G_PO) then
4480 /* if end order type = PO */
4481 t_ins_end_ord_type_desc(a_ins_count.COUNT) := G_PO_DESC;
4482 elsif (t_ins_end_pub_ord_type(a_ins_count.COUNT) = G_REQ) then
4483 /* if end order type = Req */
4484 t_ins_end_ord_type_desc(a_ins_count.COUNT) := G_REQ_DESC;
4485 else
4486 t_ins_end_ord_type_desc(a_ins_count.COUNT) := null;
4487
4488 end if;
4489
4490 --==================================
4491 -- Extend the Item related variables
4492 --==================================
4493
4494 t_ins_owner_item_name.EXTEND;
4495 t_ins_customer_item_name.EXTEND;
4496 t_ins_supplier_item_name.EXTEND;
4497 t_ins_owner_item_desc.EXTEND;
4498 t_ins_cust_item_desc.EXTEND;
4499 t_ins_sup_item_desc.EXTEND;
4500
4501 --============================================
4502 -- Derive the Item cross reference information
4503 --============================================
4504
4505 --====================
4506 -- Initialize variable
4507 --====================
4508 l_customer_item_name := null;
4509 l_customer_item_desc := null;
4510 l_lead_time := 0;
4511 l_tp_uom := null;
4512
4513 t_ins_supplier_item_name(a_ins_count.COUNT) := t_item_name(i);
4514 t_ins_owner_item_name(a_ins_count.COUNT) := t_item_name(i);
4515 t_ins_owner_item_desc(a_ins_count.COUNT) := t_item_desc(i);
4516 t_ins_sup_item_desc(a_ins_count.COUNT) := t_item_desc(i);
4517
4518 BEGIN
4519
4520 select customer_item_name,
4521 description,
4522 uom_code
4523 into l_customer_item_name,
4524 l_customer_item_desc,
4525 l_tp_uom
4526 from msc_item_customers mic
4527 where mic.plan_id = G_PLAN_ID
4528 and mic.inventory_item_id = t_item_id(i)
4529 and mic.customer_id = t_partner_id(i)
4530 and nvl(mic.customer_site_id, -99) = decode(mic.customer_site_id,
4531 null, -99,
4532 t_partner_site_id(i));
4533
4534 EXCEPTION WHEN OTHERS THEN
4535 l_customer_item_name := null;
4536 l_customer_item_desc := null;
4537 l_tp_uom := null;
4538 END;
4539
4540 t_ins_customer_item_name(a_ins_count.COUNT) := l_customer_item_name;
4541 t_ins_cust_item_desc(a_ins_count.COUNT) := l_customer_item_desc;
4542 t_ins_tp_uom(a_ins_count.COUNT) := nvl(l_tp_uom, t_uom(i));
4543
4544 --===============================================
4545 -- Get the conversion rate and derive tp_quantity
4546 --===============================================
4547 msc_x_util.get_uom_conversion_rates(
4548 t_uom(i),
4549 t_ins_tp_uom(a_ins_count.COUNT),
4550 t_item_id(i),
4551 l_conversion_found,
4552 l_conversion_rate);
4553
4554 IF (l_conversion_found) THEN
4555 t_ins_tp_quantity(a_ins_count.COUNT) := t_quantity(i) * nvl(l_conversion_rate, 1);
4556 ELSE
4557 t_ins_tp_quantity(a_ins_count.COUNT) := t_quantity(i);
4558 END IF;
4559
4560 END IF; -- (SQL%BULK_ROWCOUNT(i) = 0)
4561 END LOOP;
4562
4563 LOG_MESSAGE('Total records for insertion for Sales Orders:'||a_ins_count.COUNT);
4564
4565 -- ==================
4566 -- Insert the records
4567 -- ==================
4568 IF a_ins_count.COUNT > 0 THEN
4569 BEGIN
4570 FORALL i IN 1..a_ins_count.COUNT
4571 INSERT INTO MSC_SUP_DEM_ENTRIES
4572 (
4573 transaction_id,
4574 plan_id ,
4575 sr_instance_id ,
4576 publisher_id ,
4577 publisher_site_id ,
4578 publisher_name ,
4579 publisher_site_name ,
4580 inventory_item_id ,
4581 quantity ,
4582 comments ,
4583 publisher_order_type,
4584 customer_id ,
4585 customer_name ,
4586 customer_site_id,
4587 customer_site_name,
4588 bucket_type ,
4589 order_number ,
4590 line_number ,
4591 ship_date ,
4592 receipt_date ,
4593 promise_ship_date ,
4594 item_name ,
4595 pub_item_description ,
4596 publisher_order_type_desc ,
4597 tp_order_type_desc ,
4598 bucket_type_desc ,
4599 uom_code ,
4600 created_by ,
4601 creation_date ,
4602 last_updated_by,
4603 last_update_date,
4604 key_date ,
4605 shipping_control,
4606 shipping_control_code,
4607 primary_uom ,
4608 primary_quantity,
4609 owner_item_name,
4610 supplier_item_name,
4611 customer_item_name,
4612 item_description,
4613 owner_item_description,
4614 supplier_item_description,
4615 customer_item_description,
4616 supplier_id,
4617 supplier_site_id,
4618 supplier_name,
4619 supplier_site_name,
4620 last_refresh_number,
4621 tp_uom_code,
4622 tp_quantity,
4623 base_item_id,
4624 base_item_name,
4625 end_order_number,
4626 end_order_line_number,
4627 end_order_rel_number,
4628 end_order_publisher_id,
4629 end_order_publisher_site_id,
4630 end_order_publisher_name,
4631 end_order_publisher_site_name,
4632 end_order_type,
4633 end_order_type_desc,
4634 internal_flag,
4635 planner_code --Bug 4424426
4636 )
4637 values
4638 (
4639 msc_sup_dem_entries_s.nextval,
4640 t_ins_plan_id(i) ,
4641 t_ins_sr_instance_id(i),
4642 t_ins_pub_id(i) ,
4643 t_ins_pub_site_id(i) ,
4644 t_ins_pub(i) ,
4645 t_ins_pub_site(i) ,
4646 t_ins_item_id(i) ,
4647 round(t_ins_quantity(i), 6) ,
4648 t_ins_comments(i) ,
4649 t_ins_pub_order_type(i),
4650 t_ins_customer_id(i) ,
4651 t_ins_customer_name(i) ,
4652 t_ins_customer_site_id(i),
4653 t_ins_customer_site_name(i),
4654 t_ins_bkt_type(i) ,
4655 t_ins_ord_num(i) ,
4656 t_ins_line_num(i) ,
4657 t_ins_ship_date(i) ,
4658 t_ins_receipt_date(i) ,
4659 t_ins_promise_date(i) ,
4660 t_ins_item_name(i) ,
4661 t_ins_item_desc(i) ,
4662 t_ins_ot_desc(i) ,
4663 t_ins_tp_ot_desc(i) ,
4664 t_ins_bkt_type_desc(i) ,
4665 t_ins_uom(i) ,
4666 t_ins_created_by(i) ,
4667 t_ins_creation_date(i) ,
4668 t_ins_last_updated_by(i),
4669 t_ins_last_update_date(i),
4670 t_ins_key_date(i) ,
4671 t_ins_shipping_control(i),
4672 t_ins_shipping_control_code(i),
4673 t_ins_pri_uom(i) ,
4674 round(t_ins_primary_quantity(i), 6),
4675 t_ins_owner_item_name(i),
4676 t_ins_supplier_item_name(i),
4677 t_ins_customer_item_name(i),
4678 t_ins_item_desc(i),
4679 t_ins_owner_item_desc(i),
4680 t_ins_sup_item_desc(i),
4681 t_ins_cust_item_desc(i),
4682 t_ins_supp_id(i), ---- t_ins_pub_id(i) ,
4683 t_ins_supp_site_id(i) ,
4684 t_ins_supp(i) ,
4685 t_ins_supp_site(i) ,
4686 msc_cl_refresh_s.nextval,
4687 t_ins_tp_uom(i),
4688 round(t_ins_tp_quantity(i), 6),
4689 t_ins_base_item_id(i),
4690 t_ins_base_item_name(i),
4691 t_ins_end_ord_num(i),
4692 t_ins_end_ord_line_num(i),
4693 t_ins_end_ord_rel_num(i),
4694 t_ins_end_ord_pub_id(i),
4695 t_ins_end_ord_pub_site_id(i),
4696 t_ins_end_ord_pub_name(i),
4697 t_ins_end_ord_pub_site_name(i),
4698 t_ins_end_pub_ord_type(i),
4699 t_ins_end_ord_type_desc(i),
4700 t_ins_internal_flag(i),
4701 t_ins_planner_code(i) --Bug 4424426
4702 );
4703
4704 COMMIT;
4705 EXCEPTION WHEN OTHERS THEN
4706 LOG_MESSAGE('ERROR while inserting from Sales orders to msc_sup_dem_entries ');
4707 LOG_MESSAGE(SQLERRM);
4708 ROLLBACK;
4709 RETCODE := G_ERROR;
4710 END;
4711 END IF;
4712
4713 /* Update the pegging information for internal sales orders / internal reqs */
4714 BEGIN
4715
4716 FORALL i in 1..t_plan_id.COUNT
4717
4718 update msc_sup_dem_entries sd
4719 set link_trans_id = t_line_num(i)
4720 where sd.plan_id = G_PLAN_ID
4721 and sd.sr_instance_id = G_SR_INSTANCE_ID
4722 and sd.inventory_item_id = t_item_id(i)
4723 and sd.customer_id = t_customer_id(i)
4724 and sd.customer_site_id = t_customer_site_id(i)
4725 and sd.supplier_id = t_pub_id(i)
4726 and sd.supplier_site_id = t_pub_site_id(i)
4727 and sd.publisher_order_type = G_REQ
4728 and sd.internal_flag = SYS_YES
4729 and sd.order_number = t_end_order_number(i)
4730 and nvl(sd.line_number, '-1') =
4731 nvl(t_end_order_line_number(i), '-1')
4732 and nvl(sd.release_number, '-1') =
4733 nvl(t_end_order_rel_number(i), '-1')
4734 and sd.customer_id = t_end_ord_pub_id(i)
4735 and sd.customer_site_id = t_end_ord_pub_site_id(i)
4736 and sd.publisher_order_type = t_end_pub_ord_type(i)
4737 and t_internal_flag(i) = SYS_YES;
4738
4739
4740 LOG_MESSAGE('updating pegging info for int reqs 1');
4741 COMMIT;
4742 EXCEPTION WHEN OTHERS THEN
4743 ROLLBACK;
4744 LOG_MESSAGE('ERROR while updating pegging info for int reqs 1');
4745
4746 LOG_MESSAGE(SQLERRM);
4747 RETCODE := G_ERROR;
4748
4749 END;
4750 END IF; -- if v_lrtype ...
4751
4752 --===============================================================================
4753 -- After processing Sales Orders, we need to Collect PO Acknowledgment records as
4754 -- Supplier Sales Orders.
4755 --
4756 -- For Automatic collections, we will manipulate v_lrtype depending on parameters
4757 -- so that we can call following API in targeted or Net change mode.
4758 --===============================================================================
4759
4760 IF ((v_lrtype = 'C') OR
4761 (v_lrtype = 'I') OR
4762 (v_lrtype = 'P' AND p_sup_resp_flag = MSC_CL_COLLECTION.SYS_YES) OR
4763 (v_lrtype = 'T' AND nvl(p_suprep_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL)
4764 ) THEN
4765
4766 IF (v_lrtype = 'T' and nvl(p_suprep_sn_flag, G_AUTO_NO_COLL) <> G_AUTO_NO_COLL) THEN
4767
4768 IF (nvl(p_suprep_sn_flag, G_AUTO_NO_COLL) = G_AUTO_NET_COLL) THEN
4769 v_lrtype := 'I';
4770 ELSIF (nvl(p_suprep_sn_flag, G_AUTO_NO_COLL) = G_AUTO_TAR_COLL) THEN
4771 v_lrtype := 'P';
4772 END IF;
4773
4774 END IF;
4775
4776 LOG_MESSAGE('Collection mode for Supplier Responses in Collaboration ODS Load : '||v_lrtype);
4777
4778
4779 MSC_CL_SUPPLIER_RESP.PUBLISH_SUPPLIER_RESPONSE( v_suprep_refresh_number,
4780 p_sr_instance_id,
4781 a_ack_return_status,
4782 v_lrtype,
4783 p_user_id,
4784 v_in_org_str
4785 );
4786
4787 IF (a_ack_return_status = FALSE) THEN
4788 LOG_MESSAGE('Error while publishing PO Acknowledgment Records');
4789 RETCODE := G_ERROR;
4790 END IF;
4791
4792 END IF; -- IF ((v_lrtype = 'C')......
4793
4794
4795 ---=====================PRAGNESH=================================
4796
4797 if (p_so_enabled_flag = MSC_CL_COLLECTION.SYS_YES ) then
4798
4799 /* PS: added code to initialize the variables */
4800
4801 t_pub := msc_sce_loads_pkg.publisherList();
4802 t_pub_site := msc_sce_loads_pkg.pubsiteList();
4803 t_pub_site_id := msc_sce_loads_pkg.pubsiteidList();
4804 t_pub_id := msc_sce_loads_pkg.publishidList();
4805 t_supp_id := msc_sce_loads_pkg.suppidList();
4806 t_supp := msc_sce_loads_pkg.supplierList();
4807 t_supp_site_id := msc_sce_loads_pkg.suppsiteidList();
4808 t_supp_site := msc_sce_loads_pkg.suppsiteList();
4809 t_item_id := msc_sce_loads_pkg.itemidList();
4810 t_quantity := msc_sce_loads_pkg.qtyList();
4811 t_receipt_date := msc_sce_loads_pkg.receiptdateList();
4812 t_line_num := msc_sce_loads_pkg.linenumList();
4813 t_shipto_id := msc_sce_loads_pkg.shiptoidList();
4814 t_shipto_site_id := msc_sce_loads_pkg.shiptosidList();
4815 t_shipto := msc_sce_loads_pkg.shiptoList();
4816 t_shipto_site := msc_sce_loads_pkg.shiptositeList();
4817 t_shipfrom_id := msc_sce_loads_pkg.shipfromidList();
4818 t_shipfrom_site_id := msc_sce_loads_pkg.shipfromsidList();
4819 t_shipfrom := msc_sce_loads_pkg.shipfromList();
4820 t_shipfrom_site := msc_sce_loads_pkg.shipfromsiteList();
4821 t_item_name := msc_sce_loads_pkg.itemList();
4822 t_item_desc := msc_sce_loads_pkg.itemdescList();
4823 t_uom := msc_sce_loads_pkg.uomList();
4824 t_key_date := msc_sce_loads_pkg.newschedList();
4825 t_ord_num := msc_sce_loads_pkg.ordernumList();
4826 t_end_order_number := msc_sce_loads_pkg.ordernumList();
4827 t_end_order_line_number := msc_sce_loads_pkg.linenumList();
4828
4829 t_planner_code := msc_sce_loads_pkg.plannerCode();--Bug 4424426
4830
4831 LOG_MESSAGE('Writing the lv_sql_stmt ');
4832 /* lv_sql_stmt := 'select 1 '||
4833 ' ,mcsil.company_site_id '||
4834 ' ,mc.company_name '||
4835 ' ,mcs.company_site_name '||
4836 ' ,msi.inventory_item_id '||
4837 ' ,mavv.shipped_quantity '||
4838 ' ,mavv.ultimate_dropoff_date receipt_date'||
4839 ' ,mc.company_id '||
4840 ' ,mc.company_name '||
4841 ' ,mcsil.company_site_id '||
4842 ' ,mcs.company_site_name '||
4843 ' ,mavv.delivery_name '||
4844 ' ,mcr.object_id '||
4845 ' ,mc1.company_name '||
4846 ' ,mcs1.company_site_id '||
4847 ' ,mcs1.company_site_name '||
4848 ' ,mc.company_id '||
4849 ' ,mc.company_name '||
4850 ' ,mcsil.company_site_id '||
4851 ' ,mcs.company_site_name '||
4852 ' ,msi.item_name '||
4853 ' ,msi.description '||
4854 ' ,msi.uom_code '||
4855 ' ,mavv.ultimate_dropoff_date '||
4856 ' ,nvl(asn.SOURCE_DELIVERY_ID,-999999) '||
4857 ' ,mavv.DELIVERY_ID SOURCE_DELIVERY_ID'||
4858 ' ,to_char(null) ' ||
4859 ' ,to_char(null) ' ||
4860 ' ,mavv.status_code '||
4861 ' ,msi.planner_code '||
4862 ' from msc_system_items msi ,'||
4863 ' msc_trading_partners mtp ,'||
4864 ' msc_company_site_id_lid mcsil ,'||
4865 ' msc_company_sites mcs ,'||
4866 ' msc_companies mc ,'||
4867 ' msc_sup_dem_entries asn ,'||
4868 ' msc_company_relationships mcr ,'||
4869 ' msc_trading_partner_maps mtpm ,'||
4870 ' msc_companies mc1 ,'||
4871 ' msc_company_sites mcs1 ,'||
4872 ' msc_trading_partners mtp1 ,'||
4873 ' msc_trading_partner_maps mtpm1 ,'||
4874 ' mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv '||
4875 ' where mtp.partner_type = 3 '||
4876 ' and mtp.sr_instance_id = '||p_sr_instance_id||
4877 ' and mtp.modeled_customer_id is not null '||
4878 ' and mtp.modeled_customer_site_id is not null '||
4879 ' and msi.sr_instance_id = mtp.sr_instance_id'||
4880 ' and msi.plan_id = -1'||
4881 ' and msi.organization_id = mtp.sr_tp_id'||
4882 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
4883 ' and msi.INVENTORY_PLANNING_CODE = 7'||
4884 ' and msi.CONSIGNED_FLAG = 2'||
4885 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
4886 ' and asn.source_DELIVERY_ID(+) = mavv.delivery_id'||
4887 ' and asn.source_DELIVERY_ID is null '||
4888 ' and asn.publisher_order_type(+) = 15'||
4889 ' and mavv.status_code = ''IT'''||
4890 ' and mavv.CONSIGNED_FLAG = 2 '||
4891 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
4892 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
4893 ' and mtp1.partner_type = mtp.partner_type '||
4894 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
4895 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
4896 ' and mcsil.partner_type = 3 '||
4897 ' and mcsil.sr_company_id = -1 '||
4898 ' and mcsil.company_site_id = mcs.company_site_id'||
4899 ' and mcs.company_id = mc.company_id'||
4900 ' and mcs.company_id = 1 '||
4901 ' and mtp.modeled_customer_id = mtpm.tp_key'||
4902 ' and mtpm.map_type = 1'||
4903 ' and mtpm.company_key = mcr.relationship_id'||
4904 ' and mcr.object_id = mc1.company_id'||
4905 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
4906 ' and mtpm1.map_type = 3'||
4907 ' and mtpm1.company_key = mcs1.company_site_id'||
4908 ' union all '||
4909 ' select 1 '||
4910 ' ,mcsil.company_site_id '||
4911 ' ,mc.company_name '||
4912 ' ,mcs.company_site_name '||
4913 ' ,msi.inventory_item_id '||
4914 ' ,mavv.shipped_quantity '||
4915 ' ,mavv.ultimate_dropoff_date receipt_date'||
4916 ' ,mc.company_id '||
4917 ' ,mc.company_name '||
4918 ' ,mcsil.company_site_id '||
4919 ' ,mcs.company_site_name '||
4920 ' ,mavv.delivery_name '||
4921 ' ,mcr.object_id '||
4922 ' ,mc1.company_name '||
4923 ' ,mcs1.company_site_id '||
4924 ' ,mcs1.company_site_name '||
4925 ' ,mc.company_id '||
4926 ' , mc.company_name '||
4927 ' , mcsil.company_site_id '||
4928 ' , mcs.company_site_name '||
4929 ' ,msi.item_name '||
4930 ' ,msi.description '||
4931 ' ,msi.uom_code '||
4932 ' ,mavv.ultimate_dropoff_date '||
4933 ' ,asn.SOURCE_DELIVERY_ID '||
4934 ' ,mavv.DELIVERY_ID source_delivery_id'||
4935 ' ,to_char(null) ' ||
4936 ' ,to_char(null) ' ||
4937 ' ,mavv.status_code '||
4938 ' ,msi.planner_code '||
4939 ' from msc_system_items msi,'||
4940 ' msc_trading_partners mtp,'||
4941 ' msc_company_site_id_lid mcsil,'||
4942 ' msc_company_sites mcs,'||
4943 ' msc_companies mc,'||
4944 ' msc_sup_dem_entries asn,'||
4945 ' msc_company_relationships mcr,'||
4946 ' msc_trading_partner_maps mtpm,'||
4947 ' msc_companies mc1,'||
4948 ' msc_company_sites mcs1,'||
4949 ' msc_trading_partners mtp1 ,'||
4950 ' msc_trading_partner_maps mtpm1,'||
4951 ' mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv'||
4952 ' where mtp.partner_type = 3'||
4953 ' and mtp.sr_instance_id = '||p_sr_instance_id||
4954 ' and mtp.modeled_customer_id is not null '||
4955 ' and mtp.modeled_customer_site_id is not null '||
4956 ' and msi.sr_instance_id = mtp.sr_instance_id'||
4957 ' and msi.plan_id = -1'||
4958 ' and msi.INVENTORY_PLANNING_CODE = 7'||
4959 ' and msi.CONSIGNED_FLAG = 2'||
4960 ' and mavv.CONSIGNED_FLAG = 2 '||
4961 ' and msi.organization_id = mtp.sr_tp_id'||
4962 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
4963 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
4964 ' and asn.SOURCE_DELIVERY_ID = mavv.DELIVERY_ID'||
4965 ' and asn.publisher_order_type = 15'||
4966 ' and (asn.quantity <> mavv.shipped_quantity'||
4967 ' or trunc(asn.key_date) <> trunc(mavv.ultimate_dropoff_date)'||
4968 ' or mavv.status_code <> ''IT'' )'||
4969 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
4970 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
4971 ' and mtp1.partner_type = mtp.partner_type '||
4972 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
4973 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
4974 ' and mcsil.partner_type = 3 '||
4975 ' and mcsil.sr_company_id = -1 '||
4976 ' and mcsil.company_site_id = mcs.company_site_id'||
4977 ' and mcs.company_id = mc.company_id'||
4978 ' and mcs.company_id = 1 '||
4979 ' and mtp.modeled_customer_id = mtpm.tp_key'||
4980 ' and mtpm.map_type = 1'||
4981 ' and mtpm.company_key = mcr.relationship_id'||
4982 ' and mcr.object_id = mc1.company_id'||
4983 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
4984 ' and mtpm1.map_type = 3'||
4985 ' and mtpm1.company_key = mcs1.company_site_id ' ||
4986 ' union all ' ||
4987 ' select 1 '||
4988 ' ,mcsil.company_site_id '||
4989 ' ,mc.company_name '||
4990 ' ,mcs.company_site_name '||
4991 ' ,msi.inventory_item_id '||
4992 ' ,mavv.shipped_quantity '||
4993 ' ,mavv.ultimate_dropoff_date '||
4994 ' ,mc.company_id '||
4995 ' ,mc.company_name '||
4996 ' ,mcsil.company_site_id '||
4997 ' ,mcs.company_site_name '||
4998 ' ,mavv.delivery_name '||
4999 ' ,mcr.object_id '||
5000 ' ,mc1.company_name '||
5001 ' ,mcs1.company_site_id '||
5002 ' ,mcs1.company_site_name '||
5003 ' ,mc.company_id '||
5004 ' ,mc.company_name '||
5005 ' ,mcsil.company_site_id '||
5006 ' ,mcs.company_site_name '||
5007 ' ,msi.item_name '||
5008 ' ,msi.description '||
5009 ' ,msi.uom_code '||
5010 ' ,mavv.ultimate_dropoff_date '||
5011 ' ,nvl(asn.SOURCE_DELIVERY_ID,-999999) '||
5012 ' ,mavv.DELIVERY_ID '||
5013 ' ,mavv.req_order_number ' ||
5014 ' ,to_char(mavv.req_line_number) ' ||
5015 ' ,mavv.status_code '||
5016 ' ,msi.planner_code '||
5017 ' from mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv, '||
5018 ' msc_system_items msi,'||
5019 ' msc_trading_partners mtp,'||
5020 ' msc_company_site_id_lid mcsil,'||
5021 ' msc_company_sites mcs,'||
5022 ' msc_companies mc,'||
5023 ' msc_sup_dem_entries asn,'||
5024 ' msc_company_relationships mcr,'||
5025 ' msc_trading_partner_maps mtpm,'||
5026 ' msc_companies mc1,'||
5027 ' msc_company_sites mcs1,'||
5028 ' msc_trading_partners mtp1 ,'||
5029 ' msc_trading_partner_maps mtpm1'||
5030 ' where mtp.partner_type = 3'||
5031 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5032 ' and mtp.modeled_customer_id is not null'||
5033 ' and mtp.modeled_customer_site_id is not null'||
5034 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5035 ' and msi.plan_id = -1'||
5036 ' and msi.CONSIGNED_FLAG = 1'||
5037 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5038 ' and msi.organization_id = mtp.sr_tp_id'||
5039 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5040 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5041 ' and asn.source_DELIVERY_ID(+) = mavv.delivery_id'||
5042 ' and asn.source_DELIVERY_ID is null'||
5043 ' and asn.publisher_order_type(+) = 15'||
5044 ' and mavv.status_code = ''IT'''||
5045 ' and mavv.CONSIGNED_FLAG = 1'||
5046 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5047 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5048 ' and mtp1.partner_type = mtp.partner_type '||
5049 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5050 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5051 ' and mcsil.partner_type = 3 '||
5052 ' and mcsil.sr_company_id = -1 '||
5053 ' and mcsil.company_site_id = mcs.company_site_id'||
5054 ' and mcs.company_id = mc.company_id'||
5055 ' and mcs.company_id = 1 '||
5056 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5057 ' and mtpm.map_type = 1'||
5058 ' and mtpm.company_key = mcr.relationship_id'||
5059 ' and mcr.object_id = mc1.company_id'||
5060 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5061 ' and mtpm1.map_type = 3'||
5062 ' and mtpm1.company_key = mcs1.company_site_id'||
5063 ' union all '||
5064 ' select 1 '||
5065 ' ,mcsil.company_site_id '||
5066 ' ,mc.company_name '||
5067 ' ,mcs.company_site_name '||
5068 ' ,msi.inventory_item_id '||
5069 ' ,mavv.shipped_quantity '||
5070 ' ,mavv.ultimate_dropoff_date '||
5071 ' ,mc.company_id '||
5072 ' ,mc.company_name '||
5073 ' ,mcsil.company_site_id '||
5074 ' ,mcs.company_site_name '||
5075 ' ,mavv.delivery_name '||
5076 ' ,mcr.object_id '||
5077 ' ,mc1.company_name '||
5078 ' ,mcs1.company_site_id '||
5079 ' ,mcs1.company_site_name '||
5080 ' ,mc.company_id '||
5081 ' ,mc.company_name '||
5082 ' ,mcsil.company_site_id '||
5083 ' ,mcs.company_site_name '||
5084 ' ,msi.item_name '||
5085 ' ,msi.description '||
5086 ' ,msi.uom_code '||
5087 ' ,mavv.ultimate_dropoff_date'||
5088 ' ,asn.SOURCE_DELIVERY_ID '||
5089 ' ,mavv.DELIVERY_ID '||
5090 ' ,mavv.req_order_number ' ||
5091 ' ,to_char(mavv.req_line_number) ' ||
5092 ' ,mavv.status_code '||
5093 ' ,msi.planner_code '||
5094 ' from mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv, '||
5095 ' msc_system_items msi,'||
5096 ' msc_trading_partners mtp,'||
5097 ' msc_company_site_id_lid mcsil,'||
5098 ' msc_company_sites mcs,'||
5099 ' msc_companies mc,'||
5100 ' msc_sup_dem_entries asn,'||
5101 ' msc_company_relationships mcr,'||
5102 ' msc_trading_partner_maps mtpm,'||
5103 ' msc_companies mc1,'||
5104 ' msc_company_sites mcs1,'||
5105 ' msc_trading_partners mtp1 ,'||
5106 ' msc_trading_partner_maps mtpm1'||
5107 ' where mtp.partner_type = 3'||
5108 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5109 ' and mtp.modeled_customer_id is not null'||
5110 ' and mtp.modeled_customer_site_id is not null'||
5111 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5112 ' and msi.plan_id = -1'||
5113 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5114 ' and msi.CONSIGNED_FLAG = 1'||
5115 ' and mavv.CONSIGNED_FLAG = 1'||
5116 ' and msi.organization_id = mtp.sr_tp_id'||
5117 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5118 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5119 ' and asn.source_DELIVERY_ID = mavv.delivery_id'||
5120 ' and asn.publisher_order_type = 15'||
5121 ' and (asn.quantity <> mavv.shipped_quantity'||
5122 ' or trunc(asn.key_date) <> trunc(mavv.ultimate_dropoff_date)'||
5123 ' or mavv.status_code <> ''IT'' )'||
5124 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5125 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5126 ' and mtp1.partner_type = mtp.partner_type '||
5127 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5128 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5129 ' and mcsil.partner_type = 3 '||
5130 ' and mcsil.sr_company_id = -1 '||
5131 ' and mcsil.company_site_id = mcs.company_site_id'||
5132 ' and mcs.company_id = mc.company_id'||
5133 ' and mcs.company_id = 1 '||
5134 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5135 ' and mtpm.map_type = 1'||
5136 ' and mtpm.company_key = mcr.relationship_id'||
5137 ' and mcr.object_id = mc1.company_id'||
5138 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5139 ' and mtpm1.map_type = 3'||
5140 ' and mtpm1.company_key = mcs1.company_site_id';
5141 */
5142
5143 lv_sql_stmt1 := 'select 1 '||
5144 ' ,mcsil.company_site_id '||
5145 ' ,mc.company_name '||
5146 ' ,mcs.company_site_name '||
5147 ' ,msi.inventory_item_id '||
5148 ' ,mavv.shipped_quantity '||
5149 ' ,mavv.ultimate_dropoff_date receipt_date'||
5150 ' ,mc.company_id '||
5151 ' ,mc.company_name '||
5152 ' ,mcsil.company_site_id '||
5153 ' ,mcs.company_site_name '||
5154 ' ,mavv.delivery_name '||
5155 ' ,mcr.object_id '||
5156 ' ,mc1.company_name '||
5157 ' ,mcs1.company_site_id '||
5158 ' ,mcs1.company_site_name '||
5159 ' ,mc.company_id '||
5160 ' ,mc.company_name '||
5161 ' ,mcsil.company_site_id '||
5162 ' ,mcs.company_site_name '||
5163 ' ,msi.item_name '||
5164 ' ,msi.description '||
5165 ' ,msi.uom_code '||
5166 ' ,mavv.ultimate_dropoff_date '||
5167 ' ,nvl(asn.SOURCE_DELIVERY_ID,-999999) '||
5168 ' ,mavv.DELIVERY_ID SOURCE_DELIVERY_ID'||
5169 ' ,to_char(null) ' ||
5170 ' ,to_char(null) ' ||
5171 ' ,mavv.status_code '||
5172 ' ,msi.planner_code '||
5173 ' from msc_system_items msi ,'||
5174 ' msc_trading_partners mtp ,'||
5175 ' msc_company_site_id_lid mcsil ,'||
5176 ' msc_company_sites mcs ,'||
5177 ' msc_companies mc ,'||
5178 ' msc_sup_dem_entries asn ,'||
5179 ' msc_company_relationships mcr ,'||
5180 ' msc_trading_partner_maps mtpm ,'||
5181 ' msc_companies mc1 ,'||
5182 ' msc_company_sites mcs1 ,'||
5183 ' msc_trading_partners mtp1 ,'||
5184 ' msc_trading_partner_maps mtpm1 ,'||
5185 ' mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv '||
5186 ' where mtp.partner_type = 3 '||
5187 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5188 ' and mtp.modeled_customer_id is not null '||
5189 ' and mtp.modeled_customer_site_id is not null '||
5190 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5191 ' and msi.plan_id = -1'||
5192 ' and msi.organization_id = mtp.sr_tp_id'||
5193 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5194 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5195 ' and msi.CONSIGNED_FLAG = 2'||
5196 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5197 ' and asn.source_DELIVERY_ID(+) = mavv.delivery_id'||
5198 ' and asn.source_DELIVERY_ID is null '||
5199 ' and asn.publisher_order_type(+) = 15'||
5200 ' and mavv.status_code = ''IT'''||
5201 ' and mavv.CONSIGNED_FLAG = 2 '||
5202 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5203 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5204 ' and mtp1.partner_type = mtp.partner_type '||
5205 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5206 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5207 ' and mcsil.partner_type = 3 '||
5208 ' and mcsil.sr_company_id = -1 '||
5209 ' and mcsil.company_site_id = mcs.company_site_id'||
5210 ' and mcs.company_id = mc.company_id'||
5211 ' and mcs.company_id = 1 '||
5212 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5213 ' and mtpm.map_type = 1'||
5214 ' and mtpm.company_key = mcr.relationship_id'||
5215 ' and mcr.object_id = mc1.company_id'||
5216 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5217 ' and mtpm1.map_type = 3'||
5218 ' and mtpm1.company_key = mcs1.company_site_id';
5219
5220 lv_sql_stmt2 := ' select 1 '||
5221 ' ,mcsil.company_site_id '||
5222 ' ,mc.company_name '||
5223 ' ,mcs.company_site_name '||
5224 ' ,msi.inventory_item_id '||
5225 ' ,mavv.shipped_quantity '||
5226 ' ,mavv.ultimate_dropoff_date receipt_date'||
5227 ' ,mc.company_id '||
5228 ' ,mc.company_name '||
5229 ' ,mcsil.company_site_id '||
5230 ' ,mcs.company_site_name '||
5231 ' ,mavv.delivery_name '||
5232 ' ,mcr.object_id '||
5233 ' ,mc1.company_name '||
5234 ' ,mcs1.company_site_id '||
5235 ' ,mcs1.company_site_name '||
5236 ' ,mc.company_id '||
5237 ' , mc.company_name '||
5238 ' , mcsil.company_site_id '||
5239 ' , mcs.company_site_name '||
5240 ' ,msi.item_name '||
5241 ' ,msi.description '||
5242 ' ,msi.uom_code '||
5243 ' ,mavv.ultimate_dropoff_date '||
5244 ' ,asn.SOURCE_DELIVERY_ID '||
5245 ' ,mavv.DELIVERY_ID source_delivery_id'||
5246 ' ,to_char(null) ' ||
5247 ' ,to_char(null) ' ||
5248 ' ,mavv.status_code '||
5249 ' ,msi.planner_code '||
5250 ' from msc_system_items msi,'||
5251 ' msc_trading_partners mtp,'||
5252 ' msc_company_site_id_lid mcsil,'||
5253 ' msc_company_sites mcs,'||
5254 ' msc_companies mc,'||
5255 ' msc_sup_dem_entries asn,'||
5256 ' msc_company_relationships mcr,'||
5257 ' msc_trading_partner_maps mtpm,'||
5258 ' msc_companies mc1,'||
5259 ' msc_company_sites mcs1,'||
5260 ' msc_trading_partners mtp1 ,'||
5261 ' msc_trading_partner_maps mtpm1,'||
5262 ' mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv'||
5263 ' where mtp.partner_type = 3'||
5264 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5265 ' and mtp.modeled_customer_id is not null '||
5266 ' and mtp.modeled_customer_site_id is not null '||
5267 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5268 ' and msi.plan_id = -1'||
5269 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5270 ' and msi.CONSIGNED_FLAG = 2'||
5271 ' and mavv.CONSIGNED_FLAG = 2 '||
5272 ' and msi.organization_id = mtp.sr_tp_id'||
5273 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5274 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5275 ' and asn.SOURCE_DELIVERY_ID = mavv.DELIVERY_ID'||
5276 ' and asn.publisher_order_type = 15'||
5277 ' and (asn.quantity <> mavv.shipped_quantity'||
5278 ' or trunc(asn.key_date) <> trunc(mavv.ultimate_dropoff_date)'||
5279 ' or mavv.status_code <> ''IT'' )'||
5280 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5281 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5282 ' and mtp1.partner_type = mtp.partner_type '||
5283 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5284 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5285 ' and mcsil.partner_type = 3 '||
5286 ' and mcsil.sr_company_id = -1 '||
5287 ' and mcsil.company_site_id = mcs.company_site_id'||
5288 ' and mcs.company_id = mc.company_id'||
5289 ' and mcs.company_id = 1 '||
5290 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5291 ' and mtpm.map_type = 1'||
5292 ' and mtpm.company_key = mcr.relationship_id'||
5293 ' and mcr.object_id = mc1.company_id'||
5294 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5295 ' and mtpm1.map_type = 3'||
5296 ' and mtpm1.company_key = mcs1.company_site_id ';
5297
5298 lv_sql_stmt3 := ' select 1 '||
5299 ' ,mcsil.company_site_id '||
5300 ' ,mc.company_name '||
5301 ' ,mcs.company_site_name '||
5302 ' ,msi.inventory_item_id '||
5303 ' ,mavv.shipped_quantity '||
5304 ' ,mavv.ultimate_dropoff_date '||
5305 ' ,mc.company_id '||
5306 ' ,mc.company_name '||
5307 ' ,mcsil.company_site_id '||
5308 ' ,mcs.company_site_name '||
5309 ' ,mavv.delivery_name '||
5310 ' ,mcr.object_id '||
5311 ' ,mc1.company_name '||
5312 ' ,mcs1.company_site_id '||
5313 ' ,mcs1.company_site_name '||
5314 ' ,mc.company_id '||
5315 ' ,mc.company_name '||
5316 ' ,mcsil.company_site_id '||
5317 ' ,mcs.company_site_name '||
5318 ' ,msi.item_name '||
5319 ' ,msi.description '||
5320 ' ,msi.uom_code '||
5321 ' ,mavv.ultimate_dropoff_date '||
5322 ' ,nvl(asn.SOURCE_DELIVERY_ID,-999999) '||
5323 ' ,mavv.DELIVERY_ID '||
5324 ' ,mavv.req_order_number ' ||
5325 ' ,to_char(mavv.req_line_number) ' ||
5326 ' ,mavv.status_code '||
5327 ' ,msi.planner_code '||
5328 ' from mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv, '||
5329 ' msc_system_items msi,'||
5330 ' msc_trading_partners mtp,'||
5331 ' msc_company_site_id_lid mcsil,'||
5332 ' msc_company_sites mcs,'||
5333 ' msc_companies mc,'||
5334 ' msc_sup_dem_entries asn,'||
5335 ' msc_company_relationships mcr,'||
5336 ' msc_trading_partner_maps mtpm,'||
5337 ' msc_companies mc1,'||
5338 ' msc_company_sites mcs1,'||
5339 ' msc_trading_partners mtp1 ,'||
5340 ' msc_trading_partner_maps mtpm1'||
5341 ' where mtp.partner_type = 3'||
5342 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5343 ' and mtp.modeled_customer_id is not null'||
5344 ' and mtp.modeled_customer_site_id is not null'||
5345 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5346 ' and msi.plan_id = -1'||
5347 ' and msi.CONSIGNED_FLAG = 1'||
5348 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5349 ' and msi.organization_id = mtp.sr_tp_id'||
5350 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5351 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5352 ' and asn.source_DELIVERY_ID(+) = mavv.delivery_id'||
5353 ' and asn.source_DELIVERY_ID is null'||
5354 ' and asn.publisher_order_type(+) = 15'||
5355 ' and mavv.status_code = ''IT'''||
5356 ' and mavv.CONSIGNED_FLAG = 1'||
5357 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5358 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5359 ' and mtp1.partner_type = mtp.partner_type '||
5360 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5361 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5362 ' and mcsil.partner_type = 3 '||
5363 ' and mcsil.sr_company_id = -1 '||
5364 ' and mcsil.company_site_id = mcs.company_site_id'||
5365 ' and mcs.company_id = mc.company_id'||
5366 ' and mcs.company_id = 1 '||
5367 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5368 ' and mtpm.map_type = 1'||
5369 ' and mtpm.company_key = mcr.relationship_id'||
5370 ' and mcr.object_id = mc1.company_id'||
5371 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5372 ' and mtpm1.map_type = 3'||
5373 ' and mtpm1.company_key = mcs1.company_site_id';
5374
5375 lv_sql_stmt4 := ' select 1 '||
5376 ' ,mcsil.company_site_id '||
5377 ' ,mc.company_name '||
5378 ' ,mcs.company_site_name '||
5379 ' ,msi.inventory_item_id '||
5380 ' ,mavv.shipped_quantity '||
5381 ' ,mavv.ultimate_dropoff_date '||
5382 ' ,mc.company_id '||
5383 ' ,mc.company_name '||
5384 ' ,mcsil.company_site_id '||
5385 ' ,mcs.company_site_name '||
5386 ' ,mavv.delivery_name '||
5387 ' ,mcr.object_id '||
5388 ' ,mc1.company_name '||
5389 ' ,mcs1.company_site_id '||
5390 ' ,mcs1.company_site_name '||
5391 ' ,mc.company_id '||
5392 ' ,mc.company_name '||
5393 ' ,mcsil.company_site_id '||
5394 ' ,mcs.company_site_name '||
5395 ' ,msi.item_name '||
5396 ' ,msi.description '||
5397 ' ,msi.uom_code '||
5398 ' ,mavv.ultimate_dropoff_date'||
5399 ' ,asn.SOURCE_DELIVERY_ID '||
5400 ' ,mavv.DELIVERY_ID '||
5401 ' ,mavv.req_order_number ' ||
5402 ' ,to_char(mavv.req_line_number) ' ||
5403 ' ,mavv.status_code '||
5404 ' ,msi.planner_code '||
5405 ' from mrp_ap_vmi_intransits_v'||v_sr_dblink ||' mavv, '||
5406 ' msc_system_items msi,'||
5407 ' msc_trading_partners mtp,'||
5408 ' msc_company_site_id_lid mcsil,'||
5409 ' msc_company_sites mcs,'||
5410 ' msc_companies mc,'||
5411 ' msc_sup_dem_entries asn,'||
5412 ' msc_company_relationships mcr,'||
5413 ' msc_trading_partner_maps mtpm,'||
5414 ' msc_companies mc1,'||
5415 ' msc_company_sites mcs1,'||
5416 ' msc_trading_partners mtp1 ,'||
5417 ' msc_trading_partner_maps mtpm1'||
5418 ' where mtp.partner_type = 3'||
5419 ' and mtp.sr_instance_id = '||p_sr_instance_id||
5420 ' and mtp.modeled_customer_id is not null'||
5421 ' and mtp.modeled_customer_site_id is not null'||
5422 ' and msi.sr_instance_id = mtp.sr_instance_id'||
5423 ' and msi.plan_id = -1'||
5424 ' and msi.INVENTORY_PLANNING_CODE = 7'||
5425 ' and msi.CONSIGNED_FLAG = 1'||
5426 ' and mavv.CONSIGNED_FLAG = 1'||
5427 ' and msi.organization_id = mtp.sr_tp_id'||
5428 ' and mtp.sr_tp_id = mavv.destination_organization_id'||
5429 ' and msi.sr_inventory_item_id = mavv.inventory_item_id'||
5430 ' and asn.source_DELIVERY_ID = mavv.delivery_id'||
5431 ' and asn.publisher_order_type = 15'||
5432 ' and (asn.quantity <> mavv.shipped_quantity'||
5433 ' or trunc(asn.key_date) <> trunc(mavv.ultimate_dropoff_date)'||
5434 ' or mavv.status_code <> ''IT'' )'||
5435 ' and mtp1.sr_tp_id = mavv.source_organization_id '||
5436 ' and mtp1.sr_instance_id = mtp.sr_instance_id ' ||
5437 ' and mtp1.partner_type = mtp.partner_type '||
5438 ' and mtp1.sr_tp_id = mcsil.sr_company_site_id'||
5439 ' and mtp1.sr_instance_id = mcsil.sr_instance_id'||
5440 ' and mcsil.partner_type = 3 '||
5441 ' and mcsil.sr_company_id = -1 '||
5442 ' and mcsil.company_site_id = mcs.company_site_id'||
5443 ' and mcs.company_id = mc.company_id'||
5444 ' and mcs.company_id = 1 '||
5445 ' and mtp.modeled_customer_id = mtpm.tp_key'||
5446 ' and mtpm.map_type = 1'||
5447 ' and mtpm.company_key = mcr.relationship_id'||
5448 ' and mcr.object_id = mc1.company_id'||
5449 ' and nvl(mtp.modeled_customer_site_id, -99) = mtpm1.tp_key'||
5450 ' and mtpm1.map_type = 3'||
5451 ' and mtpm1.company_key = mcs1.company_site_id';
5452
5453 BEGIN
5454 i := 0;
5455 j :=1;
5456
5457 LOOP
5458
5459 -- lv_sql_stmt := 'lv_sql_stmt'||j;
5460
5461 --LOG_MESSAGE('Total records fetched for ASN Deliveries----'||lv_sql_stmt1);
5462
5463 if (j=1) then
5464 --LOG_MESSAGE('Total records fetched for ASN Deliveries---IN-');
5465 OPEN CUR_DELIVERY_ASN for lv_sql_stmt1;
5466 elsif (j=2) then
5467 OPEN CUR_DELIVERY_ASN for lv_sql_stmt2;
5468 elsif (j=3) then
5469 OPEN CUR_DELIVERY_ASN for lv_sql_stmt3;
5470 elsif (j=4) then
5471 OPEN CUR_DELIVERY_ASN for lv_sql_stmt4;
5472 else
5473 null;
5474 end if;
5475
5476
5477
5478 LOOP
5479 i := i+1;
5480 /* PS: Extend the variables in the loop */
5481 t_pub_id.EXTEND;
5482 t_pub_site_id.EXTEND;
5483 t_pub.EXTEND;
5484 t_pub_site.EXTEND;
5485 t_supp_id.EXTEND;
5486 t_supp.EXTEND;
5487 t_supp_site_id.EXTEND;
5488 t_supp_site.EXTEND;
5489 t_item_id.EXTEND;
5490 t_quantity.EXTEND;
5491 t_receipt_date.EXTEND;
5492 t_line_num.EXTEND;
5493 t_shipto_id.EXTEND;
5494 t_shipto_site_id.EXTEND;
5495 t_shipto.EXTEND;
5496 t_shipto_site.EXTEND;
5497 t_shipfrom_id.EXTEND;
5498 t_shipfrom_site_id.EXTEND;
5499 t_shipfrom.EXTEND;
5500 t_shipfrom_site.EXTEND;
5501 t_item_name.EXTEND;
5502 t_item_desc.EXTEND;
5503 t_uom.EXTEND;
5504 t_key_date.EXTEND;
5505 t_ord_num.EXTEND;
5506 t_delivery_id.EXTEND;
5507 t_status_code.EXTEND;
5508 t_end_order_number.EXTEND;
5509 t_end_order_line_number.EXTEND;
5510 t_planner_code.EXTEND;--Bug 4424426
5511
5512 FETCH CUR_DELIVERY_ASN INTO
5513 t_pub_id(i),
5514 t_pub_site_id(i),
5515 t_pub(i),
5516 t_pub_site(i),
5517 t_item_id(i),
5518 t_quantity(i),
5519 t_receipt_date(i),
5520 t_supp_id(i),
5521 t_supp(i),
5522 t_supp_site_id(i),
5523 t_supp_site(i),
5524 t_ord_num(i),
5525 t_shipto_id(i),
5526 t_shipto(i),
5527 t_shipto_site_id(i),
5528 t_shipto_site(i),
5529 t_shipfrom_id(i),
5530 t_shipfrom(i),
5531 t_shipfrom_site_id(i),
5532 t_shipfrom_site(i),
5533 t_item_name(i),
5534 t_item_desc(i),
5535 t_uom(i),
5536 t_key_date(i),
5537 t_line_num(i),
5538 t_delivery_id(i),
5539 t_end_order_number(i),
5540 t_end_order_line_number(i),
5541 t_status_code(i),
5542 t_planner_code(i);--Bug 4424426
5543
5544 EXIT WHEN CUR_DELIVERY_ASN%NOTFOUND;
5545
5546 END LOOP;
5547
5548 CLOSE CUR_DELIVERY_ASN;
5549
5550
5551
5552 /* PS: Trim the last element from the array ; since it will always be null*/
5553 t_pub_id.TRIM;
5554 t_pub_site_id.TRIM;
5555 t_pub.TRIM;
5556 t_pub_site.TRIM;
5557 t_supp_id.TRIM;
5558 t_supp.TRIM;
5559 t_supp_site_id.TRIM;
5560 t_supp_site.TRIM;
5561 t_item_id.TRIM;
5562 t_quantity.TRIM;
5563 t_receipt_date.TRIM;
5564 t_line_num.TRIM;
5565 t_shipto_id.TRIM;
5566 t_shipto_site_id.TRIM;
5567 t_shipto.TRIM;
5568 t_shipto_site.TRIM;
5569 t_shipfrom_id.TRIM;
5570 t_shipfrom_site_id.TRIM;
5571 t_shipfrom.TRIM;
5572 t_shipfrom_site.TRIM;
5573 t_item_name.TRIM;
5574 t_item_desc.TRIM;
5575 t_uom.TRIM;
5576 t_key_date.TRIM;
5577 t_ord_num.TRIM;
5578 t_delivery_id.TRIM;
5579 t_status_code.TRIM;
5580 t_planner_code.TRIM;--Bug 4424426
5581 t_end_order_number.TRIM;
5582 t_end_order_line_number.TRIM;
5583
5584
5585 LOG_MESSAGE('Total records fetched for ASN Deliveries--j'||j||'-- :'||t_pub_id.COUNT);
5586
5587 j := j+1;
5588 -- lv_sql_stmt := '';
5589
5590 If (j = 5) THEN
5591 EXIT;
5592 END IF;
5593
5594 END LOOP;
5595
5596 EXCEPTION WHEN OTHERS THEN
5597 IF (CUR_DELIVERY_ASN%ISOPEN) THEN
5598 CLOSE CUR_DELIVERY_ASN;
5599 END IF;
5600 LOG_MESSAGE('Error while fetching records for New Open Deliveries in Unconsigned ');
5601 LOG_MESSAGE(SQLERRM);
5602 RETCODE := G_ERROR;
5603 END;
5604
5605
5606 LOG_MESSAGE('Total records fetched for ASN Deliveries :'||t_pub_id.COUNT);
5607
5608 --===========================
5609 -- Update the fetched records
5610 --===========================
5611
5612 IF t_pub_id.COUNT > 0 THEN
5613
5614 BEGIN
5615
5616 FORALL i in 1..t_pub_id.COUNT
5617
5618 update msc_sup_dem_entries
5619 set key_date = t_key_date(i),
5620 receipt_date = t_receipt_date(i),
5621 quantity = decode(t_status_code(i),'IT',t_quantity(i),0),
5622 primary_quantity = decode(t_status_code(i),'IT',t_quantity(i),0),
5623 tp_quantity = decode(t_status_code(i),'IT',t_quantity(i),0),
5624 sr_delivery_status_code = t_status_code(i),
5625 last_refresh_number = msc_cl_refresh_s.nextval,
5626 last_update_date = sysdate,
5627 last_updated_by = decode(t_status_code(i),'IT',p_user_id,-999)
5628 where plan_id = G_PLAN_ID
5629 and sr_instance_id = G_SR_INSTANCE_ID
5630 and publisher_id = t_pub_id(i)
5631 and publisher_site_id = t_pub_site_id(i)
5632 and inventory_item_id = t_item_id(i)
5633 and publisher_order_type = G_ASN
5634 -- and customer_id = t_customer_id(i)
5635 -- and customer_site_id = t_customer_site_id(i)
5636 and SOURCE_delivery_id = t_line_num(i)
5637 and t_line_num(i) <> -999999;
5638
5639 COMMIT;
5640 EXCEPTION WHEN OTHERS THEN
5641 ROLLBACK;
5642 LOG_MESSAGE('ERROR while updating msc_up_dem_entries using ASN Deliveries');
5643 LOG_MESSAGE(SQLERRM);
5644 RETCODE := G_ERROR;
5645 END;
5646 LOG_MESSAGE('completed the update Deliveries');
5647
5648 END IF;
5649
5650 --==========================================================
5651 -- Insert the fetched records if the records are new records
5652 -- Step 1. Extend the insert variables.
5653 -- Step 2. BULK insert.
5654 --==========================================================
5655
5656 /* Initialize the count */
5657
5658 a_ins_count := null;
5659 a_ins_count := number_arr();
5660
5661 FOR j IN 1..t_pub_id.COUNT LOOP
5662 IF (SQL%BULK_ROWCOUNT(j) = 0) THEN
5663 a_ins_count.EXTEND;
5664 t_ins_pub_id.EXTEND;
5665 t_ins_pub_site_id.EXTEND;
5666 t_ins_pub.EXTEND;
5667 t_ins_pub_site.EXTEND;
5668 t_ins_supp_id.EXTEND;
5669 t_ins_supp.EXTEND;
5670 t_ins_supp_site_id.EXTEND;
5671 t_ins_supp_site.EXTEND;
5672 t_ins_item_id.EXTEND;
5673 t_ins_quantity.EXTEND;
5674 t_ins_receipt_date.EXTEND;
5675 t_ins_line_num.EXTEND;
5676 t_ins_shipto_id.EXTEND;
5677 t_ins_shipto_site_id.EXTEND;
5678 t_ins_shipto.EXTEND;
5679 t_ins_shipto_site.EXTEND;
5680 t_ins_shipfrom_id.EXTEND;
5681 t_ins_shipfrom_site_id.EXTEND;
5682 t_ins_shipfrom.EXTEND;
5683 t_ins_shipfrom_site.EXTEND;
5684 t_ins_item_name.EXTEND;
5685 t_ins_item_desc.EXTEND;
5686 t_ins_uom.EXTEND;
5687 t_ins_key_date.EXTEND;
5688 t_ins_ord_num.EXTEND;
5689 t_ins_delivery_id.EXTEND;
5690 t_ins_status_code.EXTEND;
5691 t_ins_planner_code.EXTEND;--Bug 4424426
5692 t_ins_end_ord_num.EXTEND;
5693 t_ins_end_ord_line_num.EXTEND;
5694
5695
5696 a_ins_count(a_ins_count.COUNT) := j;
5697 t_ins_pub_id(a_ins_count.COUNT) := t_pub_id(j);
5698 t_ins_pub_site_id(a_ins_count.COUNT) := t_pub_site_id(j);
5699 t_ins_pub(a_ins_count.COUNT) := t_pub(j);
5700 t_ins_pub_site(a_ins_count.COUNT) := t_pub_site(j);
5701 t_ins_supp_id(a_ins_count.COUNT) := t_supp_id(j);
5702 t_ins_supp(a_ins_count.COUNT) := t_supp(j);
5703 t_ins_supp_site_id(a_ins_count.COUNT) := t_supp_site_id(j);
5704 t_ins_supp_site(a_ins_count.COUNT) := t_supp_site(j);
5705 t_ins_item_id(a_ins_count.COUNT) := t_item_id(j);
5706 t_ins_quantity(a_ins_count.COUNT) := t_quantity(j);
5707 t_ins_receipt_date(a_ins_count.COUNT) := t_receipt_date(j);
5708 t_ins_shipto_id(a_ins_count.COUNT) := t_shipto_id(j);
5709 t_ins_shipto_site_id(a_ins_count.COUNT) := t_shipto_site_id(j);
5710 t_ins_shipto(a_ins_count.COUNT) := t_shipto(j);
5711 t_ins_shipto_site(a_ins_count.COUNT) := t_shipto_site(j);
5712 t_ins_shipfrom_id(a_ins_count.COUNT) := t_shipfrom_id(j);
5713 t_ins_shipfrom_site_id(a_ins_count.COUNT):= t_shipfrom_site_id(j);
5714 t_ins_shipfrom(a_ins_count.COUNT) := t_shipfrom(j);
5715 t_ins_shipfrom_site(a_ins_count.COUNT) := t_shipfrom_site(j);
5716 t_ins_item_name(a_ins_count.COUNT) := t_item_name(j);
5717 t_ins_item_desc(a_ins_count.COUNT) := t_item_desc(j);
5718 t_ins_uom(a_ins_count.COUNT) := t_uom(j);
5719 t_ins_key_date(a_ins_count.COUNT) := t_key_date(j);
5720 t_ins_line_num(a_ins_count.COUNT) := t_line_num(j);
5721 t_ins_ord_num(a_ins_count.COUNT) := t_ord_num(j);
5722 t_ins_delivery_id(a_ins_count.COUNT) := t_delivery_id(j);
5723 t_ins_status_code(a_ins_count.COUNT) := t_status_code(j);
5724 t_ins_planner_code(a_ins_count.COUNT) := t_planner_code(j);--Bug 4424426
5725 t_ins_end_ord_num(a_ins_count.COUNT) := t_end_order_number(j);
5726 t_ins_end_ord_line_num(a_ins_count.COUNT) :=
5727 t_end_order_line_number(j);
5728
5729 END IF;
5730
5731 END LOOP;
5732
5733 LOG_MESSAGE('Total records for insertion for ASN Deliveries :'||a_ins_count.COUNT);
5734
5735 IF a_ins_count.COUNT > 0 THEN
5736 BEGIN
5737 FORALL j in 1..a_ins_count.COUNT
5738 insert into msc_sup_dem_entries
5739 (
5740 sr_instance_id
5741 ,transaction_id
5742 ,plan_id
5743 ,publisher_id
5744 ,publisher_site_id
5745 ,publisher_name
5746 ,publisher_site_name
5747 ,inventory_item_id
5748 ,quantity
5749 ,publisher_order_type
5750 ,receipt_date
5751 ,supplier_id
5752 ,supplier_name
5753 ,supplier_site_id
5754 ,supplier_site_name
5755 ,SOURCE_delivery_id
5756 ,order_number
5757 ,ship_to_party_id
5758 ,ship_to_party_site_id
5759 ,ship_to_party_name
5760 ,ship_to_party_site_name
5761 ,ship_from_party_id
5762 ,SHIP_FROM_PARTY_SITE_ID
5763 ,SHIP_FROM_PARTY_NAME
5764 ,SHIP_FROM_PARTY_SITE_NAME
5765 ,publisher_item_name
5766 ,pub_item_description
5767 ,uom_code
5768 ,publisher_order_type_desc
5769 ,bucket_type
5770 ,bucket_type_desc
5771 ,created_by
5772 ,creation_date
5773 ,last_updated_by
5774 ,last_update_date
5775 ,comments
5776 ,key_date
5777 ,item_name
5778 ,owner_item_name
5779 ,customer_item_name
5780 ,supplier_item_name
5781 ,item_description
5782 ,owner_item_description
5783 ,customer_item_description
5784 ,supplier_item_description
5785 ,primary_quantity
5786 ,tp_uom_code
5787 ,tp_quantity
5788 ,customer_id
5789 ,customer_site_id
5790 ,customer_name
5791 ,customer_site_name
5792 ,last_refresh_number
5793 ,primary_uom
5794 ,vmi_flag
5795 ,end_order_number
5796 ,end_order_line_number
5797 ,sr_delivery_status_code
5798 ,planner_code--Bug 4424426
5799 )values
5800 (
5801 G_SR_INSTANCE_ID,
5802 msc_sup_dem_entries_s.nextval,
5803 G_PLAN_ID,
5804 t_ins_pub_id(j),
5805 t_ins_pub_site_id(j),
5806 t_ins_pub(j),
5807 t_ins_pub_site(j),
5808 t_ins_item_id(j),
5809 round(t_ins_quantity(j),6),
5810 G_ASN,
5811 t_ins_receipt_date(j),
5812 t_ins_supp_id(j),
5813 t_ins_supp(j),
5814 t_ins_supp_site_id(j),
5815 t_ins_supp_site(j),
5816 t_ins_delivery_id(j),
5817 t_ins_ord_num(j),
5818 t_ins_shipto_id(j),
5819 t_ins_shipto_site_id(j),
5820 t_ins_shipto(j),
5821 t_ins_shipto_site(j),
5822 t_ins_shipfrom_id(j),
5823 t_ins_shipfrom_site_id(j),
5824 t_ins_shipfrom(j),
5825 t_ins_shipfrom_site(j),
5826 t_ins_item_name(j),
5827 t_ins_item_desc(j),
5828 t_ins_uom(j),
5829 G_ASN_DESC,
5830 1,
5831 'DAY',
5832 p_user_id,
5833 sysdate,
5834 p_user_id,
5835 sysdate,
5836 'PUBLISH VMI ASN',
5837 t_ins_key_date(j),
5838 t_ins_item_name(j),
5839 t_ins_item_name(j),
5840 t_ins_item_name(j),
5841 t_ins_item_name(j),
5842 --t_ins_owner_item_name(j),
5843 --t_ins_customer_item_name(j),
5844 --t_ins_supplier_item_name(j),
5845 t_ins_item_desc(j),
5846 t_ins_item_desc(j),
5847 t_ins_item_desc(j),
5848 t_ins_item_desc(j),
5849 --t_ins_owner_item_desc(j),
5850 --t_ins_cust_item_desc(j),
5851 --t_ins_supplier_item_name(j),
5852 round(t_ins_quantity(j), 6),
5853 t_ins_uom(j),
5854 round(t_ins_quantity(j), 6),
5855 t_ins_shipto_id(j),
5856 t_ins_shipto_site_id(j),
5857 t_ins_shipto(j),
5858 t_ins_shipto_site(j),
5859 msc_cl_refresh_s.nextval,
5860 t_ins_uom(j),
5861 1,
5862 t_ins_end_ord_num(j),
5863 t_ins_end_ord_line_num(j),
5864 t_ins_status_code(j),
5865 t_ins_planner_code(j)--Bug 4424426
5866 );
5867
5868 COMMIT;
5869
5870 EXCEPTION WHEN OTHERS THEN
5871 LOG_MESSAGE('Error while inserting records into msc_sup_dem_entries');
5872 LOG_MESSAGE(SQLERRM);
5873 ROLLBACK;
5874 RETCODE := G_ERROR;
5875 END;
5876
5877 END IF;
5878
5879 /* Update the pegging information for ASN / internal reqs */
5880 BEGIN
5881 LOG_MESSAGE('updating pegging info for int reqs using ASN.');
5882
5883 FORALL i in 1..t_pub_id.COUNT
5884
5885 update msc_sup_dem_entries sd
5886 set link_trans_id = t_delivery_id(i)
5887 where sd.plan_id = G_PLAN_ID
5888 and sd.sr_instance_id = G_SR_INSTANCE_ID
5889 and sd.inventory_item_id = t_item_id(i)
5890 and sd.customer_id = t_shipto_id(i)
5891 and sd.customer_site_id = t_shipto_site_id(i)
5892 and sd.supplier_id = t_supp_id(i)
5893 and sd.supplier_site_id = t_supp_site_id(i)
5894 and sd.publisher_order_type = G_REQ
5895 and sd.internal_flag = SYS_YES
5896 and sd.order_number = t_end_order_number(i)
5897 and nvl(sd.line_number, '-1') = nvl(t_end_order_line_number(i), '-1')
5898 and t_end_order_number(i) is not null;
5899
5900 LOG_MESSAGE('Total Records for update of Reqs from ASN : '||SQL%ROWCOUNT);
5901 COMMIT;
5902
5903 EXCEPTION WHEN OTHERS THEN
5904 ROLLBACK;
5905 LOG_MESSAGE('ERROR while updating pegging info for int reqs using ASN.');
5906 LOG_MESSAGE(SQLERRM);
5907
5908 RETCODE := G_ERROR;
5909 END;
5910
5911 end if;
5912
5913 -----=====================PRAGNESH=================================
5914
5915
5916
5917 --======================================================================
5918 -- After Publsih ODS Load , we need to update refresh_number of
5919 -- transactions which are owned by non OEM company and OEM does not have
5920 -- any transaction for those Items.
5921 -- This will insure that those transactions will be captured by VMI netting
5922 -- engine.
5923 -- This needs to be done in case of complete refresh collections.
5924 --======================================================================
5925
5926 IF v_lrtype = 'C' THEN
5927
5928 BEGIN
5929
5930 UPDATE MSC_SUP_DEM_ENTRIES msde1
5931 set last_refresh_number = msc_cl_refresh_s.nextval
5932 where plan_id = G_PLAN_ID
5933 --===========================================================
5934 -- Make sure that the Transaction is owned by non OEM Company
5935 -- and has reference to OEM Company.
5936 --===========================================================
5937 and publisher_id <> G_OEM_ID
5938 and (customer_id = G_OEM_ID OR
5939 supplier_id = G_OEM_ID)
5940 and not exists ( select 1
5941 from msc_sup_dem_entries msde2
5942 where
5943 --==================================================
5944 -- Make sure that OEM has transaction for that Item.
5945 --==================================================
5946 msde2.inventory_item_id = msde1.inventory_item_id
5947 and msde2.plan_id = msde1.plan_id
5948 and msde2.publisher_id = G_OEM_ID
5949 --======================================================
5950 -- Make sure that OEM's transaction is supposed for
5951 -- TPs transaction.
5952 -- It's difficult to do pegging here. Only we will check
5953 -- for reference to TP's site in OEM transaction.
5954 --======================================================
5955 and decode(msde2.customer_id, msde1.publisher_id,
5956 msde2.customer_site_id, msde2.supplier_site_id) = msde1.publisher_site_id
5957 );
5958
5959 COMMIT;
5960
5961 EXCEPTION WHEN OTHERS THEN
5962 LOG_MESSAGE('Error in updating last_refresh_number of non OEM transactions.');
5963 LOG_MESSAGE(SQLERRM);
5964 RETCODE := G_ERROR;
5965
5966 END;
5967
5968 END IF;
5969
5970
5971
5972 --======================================================================
5973 -- Publish ODS is done. Now we need to blow away exceptions related to
5974 -- OEM. This needs to be done if the complete refresh collections is
5975 -- performed.
5976 --======================================================================
5977
5978 -- IF v_lrtype = 'C' THEN
5979 MSC_CL_POST_PUBLISH.POST_CLEANUP(v_in_org_str,
5980 v_lrtype,
5981 a_post_status);
5982
5983 IF a_post_status = G_ERROR THEN
5984 RETCODE := G_ERROR;
5985 END IF;
5986
5987 -- END IF;
5988
5989 --======================================================================
5990 -- Publish ODS is done. Now we need to call API to compute
5991 -- average daily demand for VMI Items.
5992 ---- Not required from 11.5.10
5993 --======================================================================
5994 /* IF (p_asl_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
5995
5996 BEGIN
5997
5998 MSC_X_PLANNING.CALCULATE_AVERAGE_DEMAND;
5999 LOG_MESSAGE('Done CALCULATE_AVERAGE_DEMAND');
6000 EXCEPTION WHEN OTHERS THEN
6001 LOG_MESSAGE('Error in MSC_X_PLANNING.CALCULATE_AVERAGE_DEMAND');
6002 LOG_MESSAGE(SQLERRM);
6003 RETCODE := G_ERROR;
6004 END;
6005
6006 END IF;
6007
6008 */
6009
6010 END PUBLISH;
6011
6012 END MSC_CL_PUBLISH;