[Home] [Help]
PACKAGE BODY: APPS.MSC_SCE_PUB_SUPPLY_COMMIT_PKG
Source
1 PACKAGE BODY msc_sce_pub_supply_commit_pkg AS
2 /* $Header: MSCXPSCB.pls 120.4 2008/01/07 07:09:45 dejoshi ship $ */
3
4 G_SHIP_CONTROL VARCHAR2(30);
5 G_ARRIVE_CONTROL VARCHAR2(30);
6 G_CUSTOMER VARCHAR2(30) := 'BUYER';
7 G_SUPPLIER VARCHAR2(30) := 'SUPPLIER';
8 G_SUPPLY_COMMIT Number := 3;
9
10 PROCEDURE publish_supply_commits (
11 p_errbuf out nocopy varchar2,
12 p_retcode out nocopy varchar2,
13 p_plan_id in number,
14 p_org_code in varchar2 default null,
15 p_planner_code in varchar2 default null,
16 p_abc_class in varchar2 default null,
17 p_item_id in number default null,
18 p_planning_gp in varchar2 default null,
19 p_project_id in number default null,
20 p_task_id in number default null,
21 p_source_customer_id in number default null,
22 p_source_customer_site_id in number default null,
23 p_horizon_start in varchar2,
24 p_horizon_end in varchar2,
25 p_auto_version in number default 1,
26 p_version in number default null,
27 p_include_so_flag in number default 2,
28 p_overwrite in number
29 ) IS
30
31 p_org_id Number;
32 p_inst_code Varchar2(3);
33 p_sr_instance_id Number;
34 p_designator Varchar2(10);
35 l_version Number;
36 l_new_version Number;
37 l_user_id NUMBER;
38 l_user_name varchar2(100);
39 l_item_name VARCHAR2(255);
40 l_log_message VARCHAR2(1000);
41 l_cust_name VARCHAR2(100);
42 l_cust_site VARCHAR2(30);
43 l_records_exist NUMBER;
44 l_cursor1 NUMBER;
45 l_language VARCHAR2(30);
46 l_language_code VARCHAR2(4);
47
48 l_horizon_start date; --canonical date
49 l_horizon_end date; --canonical date
50
51 t_pub companyNameList;
52 t_pub_id numberList;
53 t_pub_site companySiteList;
54 t_pub_site_id numberList;
55 t_item_id numberList;
56 t_base_item_id numberList;
57 t_qty numberList;
58 t_pub_ot numberList;
59 t_cust companyNameList;
60 t_cust_id numberList;
61 t_cust_site companySiteList;
62 t_cust_site_id numberList;
63 t_ship_from companyNameList;
64 t_ship_from_id numberList;
65 t_ship_from_site companySiteList;
66 t_ship_from_site_id numberList;
67 t_ship_to companyNameList;
68 t_ship_to_id numberList;
69 t_ship_to_site companySiteList;
70 t_ship_to_site_id numberList;
71 t_bkt_type numberList;
72 t_posting_party_id numberList;
73 t_item_name itemNameList;
74 t_item_desc itemDescList;
75 t_base_item_name itemNameList;
76 t_base_item_desc itemDescList;
77 t_pub_ot_desc fndMeaningList;
78 t_proj_number numberList;
79 t_task_number numberList;
80 t_planning_gp planningGroupList;
81 t_bkt_type_desc fndMeaningList;
82 t_posting_party_name companyNameList;
83 t_uom_code itemUomList;
84 t_planner_code plannerCodeList;
85 t_ship_date dateList;
86 t_receipt_date dateList;
87 t_key_date dateList;
88 t_shipping_control shippingControlList;
89 t_src_cust_id numberList;
90 t_src_cust_site_id numberList;
91 t_src_org_id numberList;
92 t_src_instance_id numberList;
93
94 t_tp_item_name itemNameList := itemNameList();
95 t_tp_uom itemUomList := itemUomList();
96 t_tp_qty numberList := numberList();
97 t_tp_receipt_date dateList := dateList();
98 t_master_item_name itemNameList := itemNameList();
99 t_master_item_desc itemDescList := itemDescList();
100 t_cust_item_name itemNameList := itemNameList();
101 t_cust_item_desc itemDescList := itemDescList();
102
103
104 CURSOR supply_commits_c2 (
105 p_plan_id in number,
106 p_org_id in number,
107 p_sr_instance_id in number,
108 p_horizon_start in date,
109 p_horizon_end in date,
110 p_planner_code in varchar2,
111 p_abc_class in varchar2,
112 p_item_id in number,
113 p_planning_gp in varchar2,
114 p_project_id in number,
115 p_task_id in number,
116 p_source_customer_id in number,
117 p_source_customer_site_id in number,
118 p_include_so_flag in number,
119 p_language_code in varchar2
120 ) IS
121 select c.company_name, --publisher
122 c.company_id, --publisher id
123 cs.company_site_name, --publisher site
124 cs.company_site_id, --publisher site id
125 nvl(item.base_item_id,item.inventory_item_id), --inventory item id
126 sum(mfp.allocated_quantity), --quantity
127 3, --publisher order type
128 c1.company_name, --customer name
129 c1.company_id, --customer id
130 cs1.company_site_name, --customer site
131 cs1.company_site_id, --customer site id
132 c.company_name, --ship from
133 c.company_id, --ship from id
134 cs.company_site_name, --ship from site
135 cs.company_site_id, --ship from site id
136 c1.company_name, --ship to
137 c1.company_id, --ship to id
138 cs1.company_site_name, --ship to site
139 cs1.company_site_id, --ship to site id
140 mpb.bucket_type, -- bucket type
141 c.company_id, --posting party id
142 null, --fcst.item_name, --publisher item name
143 null, --fcst.description, --publisher item desc
144 'Supply commit', --publisher order type desc
145 NULL, -- dem.project_id, --project number
146 NULL, -- dem.task_id, --task number
147 NULL, -- dem.planning_group, --planning group
148 flv.meaning, -- bucket type description
149 c.company_name, --posting supplier name [Owner]
150 item.uom_code, --primary uom
151 item.planner_code, --planner code
152 trunc(sup.new_schedule_date) ship_date,--mfp.supply_date , --ship_date
153 (trunc(sup.new_schedule_date) + nvl(dem.intransit_lead_time,0)) receipt_date, --receipt_date
154 decode(upper(nvl(mtps.shipping_control, G_CUSTOMER)),
155 G_CUSTOMER, trunc(sup.new_schedule_date),
156 (trunc(sup.new_schedule_date) + nvl(dem.intransit_lead_time,0))) key_date,
157 upper(nvl(mtps.shipping_control, G_CUSTOMER)) shipping_control,
158 dem.customer_id, --Partner Id
159 dem.customer_site_id, --Partner Site Id
160 dem.organization_id, --Partner Org Id
161 dem.sr_instance_id --Partner Instance Id
162 from
163 msc_demands dem,
164 msc_system_items item,
165 msc_company_sites cs,
166 msc_company_sites cs1,
167 msc_companies c,
168 msc_companies c1,
169 msc_trading_partner_maps m,
170 msc_trading_partner_maps m1,
171 msc_trading_partner_maps m2,
172 msc_trading_partners t,
173 msc_trading_partners t1,
174 msc_trading_partner_sites mtps, --- PO_SHIP_DATE
175 msc_company_relationships r,
176 msc_plan_buckets mpb,
177 msc_full_pegging mfp,
178 msc_supplies sup,
179 fnd_lookup_values flv
180 where
181 /* msc_system_items msi pk:
182 SR_INSTANCE_ID
183 PLAN_ID
184 ORGANIZATION_ID
185 INVENTORY_ITEM_ID
186
187 msc_trading_partners pk:
188 PARTNER_ID
189 unique Key
190 SR_INSTANCE_ID
191 SR_TP_ID
192 PARTNER_TYPE
193
194 msc_trading_partners.partner_type
195 1 - Suppliers
196 2 - Customers
197 3 - Org
198
199 msc_trading_partner_maps.map_type
200 1 - Trading Partners
201 2 - Planning Org
202 3 - Site
203
204 MAP_TYPE + TP_KEY is unique for MAP_TYPE = 2
205
206 msc_company_sites pk:
207 COMPANY_ID
208 COMPANY_SITE_ID
209
210 msc_companies pk:
211 COMPANY_ID
212
213 msc_trading_partners pk:
214 PARTNER_ID
215 unique Key
216 SR_INSTANCE_ID
217 SR_TP_ID
218 PARTNER_TYPE
219 */
220 t.sr_tp_id = dem.organization_id and
221 t.sr_instance_id = dem.sr_instance_id and
222 t.partner_type = 3 and
223 m.tp_key = t.partner_id and
224 m.map_type = 2 and
225 /* Join with Company Site PK.... */
226 m.company_key = cs.company_site_id and
227 /* Join Company Site with Company PK... */
228 c.company_id = cs.company_id and
229 /* driving Company from Customer info */
230 t1.partner_id = dem.customer_id and
231 t1.partner_type = 2 and
232 /* Partner map: MAP_TYPE + TP_KEY is unique for MAP_TYPE = 1 */
233 m1.tp_key = t1.partner_id and
234 m1.map_type = 1 and
235 /* Customer Map -> Company */
236 /* Object is customer of Subject */
237 m1.company_key = r.relationship_id and
238 r.subject_id = c.company_id and
239 r.object_id = c1.company_id and
240 r.relationship_type = 1 and
241 /* driving Company info from Customer Site */
242 m2.tp_key = dem.customer_site_id and
243 m2.map_type = 3 and
244 cs1.company_site_id = m2.company_key and
245 cs1.company_id = c1.company_id and
246 dem.customer_site_id = mtps.partner_site_id and
247 /* Outer Join for Customer Site */
248 /*
249 m2.tp_key (+) = dem.customer_site_id and
250 m2.map_type (+) = 3 and
251 cs1.company_site_id (+) = m2.company_key and
252 nvl(cs1.company_id, c1.company_id) = c1.company_id
253 */
254 /* Filter conditions */
255 dem.customer_id is not null and
256 dem.customer_site_id is not null and
257 dem.plan_id = p_plan_id and
258 dem.organization_id = NVL(p_org_id, dem.organization_id) and
259 dem.sr_instance_id = NVL(p_sr_instance_id, dem.sr_instance_id) and
260 dem.customer_id = NVL(p_source_customer_id, dem.customer_id) and
261 dem.customer_site_id = NVL(p_source_customer_site_id, dem.customer_site_id) and
262 dem.inventory_item_id = dem.using_assembly_item_id and
263 dem.origination_type in(6,7,8,9,11,29,30,42) and
264 NVL(item.base_item_id, item.inventory_item_id)
265 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
266 from msc_system_items i1
267 where i1.inventory_item_id = nvl(p_item_id, i1.inventory_item_id )
268 and i1.organization_id = item.organization_id
269 and i1.plan_id = item.plan_id
270 and i1.sr_instance_id = item.sr_instance_id) and
271 item.plan_id = dem.plan_id and
272 item.sr_instance_id = dem.sr_instance_id and
273 item.organization_id = dem.organization_id and
274 item.inventory_item_id = dem.inventory_item_id and
275 NVL(item.planner_code,'-99') = NVL(p_planner_code, NVL(item.planner_code,'-99')) and
276 NVL(item.abc_class_name,'-99') = NVL(p_abc_class,NVL(item.abc_class_name,'-99')) and
277 nvl(dem.planning_group, '-99') = nvl(p_planning_gp, nvl(dem.planning_group, '-99')) and
278 nvl(dem.project_id,-99) = nvl(p_project_id, nvl(dem.project_id,-99)) and
279 nvl(dem.task_id, -99) = nvl(p_task_id, nvl(dem.task_id, -99)) and
280 (nvl(dem.dmd_satisfied_date,dem.using_assembly_demand_date) between nvl(p_horizon_start, nvl(dem.dmd_satisfied_date,dem.using_assembly_demand_date)) and nvl(p_horizon_end,nvl(dem.dmd_satisfied_date,dem.using_assembly_demand_date)))
281 and (nvl(dem.dmd_satisfied_date,dem.using_assembly_demand_date) between mpb.bkt_start_date and mpb.bkt_end_date)
282 and dem.origination_type <> decode(p_include_so_flag, 2, 6, -1)
283 and dem.origination_type <> decode(p_include_so_flag, 2, 30, -1)
284 and mpb.plan_id = dem.plan_id
285 and mpb.sr_instance_id = dem.sr_instance_id
286 and mpb.curr_flag = 1
287 and flv.language = p_language_code
288 and flv.lookup_type = 'MSC_X_BUCKET_TYPE'
289 and flv.lookup_code = mpb.bucket_type
290 and dem.plan_id = mfp.plan_id
291 and dem.demand_id = mfp.demand_id
292 and dem.sr_instance_id = mfp.sr_instance_id
293 and mfp.pegging_id = mfp.end_pegging_id
294 and sup.sr_instance_id = mfp.sr_instance_id
295 and sup.plan_id = mfp.plan_id
296 and sup.transaction_id = mfp.transaction_id
297 and sup.order_type in (1,2,3,4,5,7,8,11,12,14,15,16,17,18,27,28,29,30)
298 GROUP BY c.company_name, --publisher
299 c.company_id, --publisher id
300 cs.company_site_name, --publisher site
301 cs.company_site_id, --publisher site id
302 nvl(item.base_item_id,item.inventory_item_id), --inventory item id
303 -- dem.quantity, --quantity
304 3, --publisher order type
305 c1.company_name, --customer name
306 c1.company_id, --customer id
307 cs1.company_site_name, --customer site
308 cs1.company_site_id, --customer site id
309 c.company_name, --ship from
310 c.company_id, --ship from id
311 cs.company_site_name, --ship from site
312 cs.company_site_id, --ship from site id
313 c1.company_name, --ship to
314 c1.company_id, --ship to id
315 cs1.company_site_name, --ship to site
316 cs1.company_site_id, --ship to site id
317 mpb.bucket_type, --bucket type
318 c.company_id, --posting party id
319 null, --fcst.item_name, --publisher item name
320 null, --fcst.description, --publisher item desc
321 'Supply commit', --publisher order type desc
322 NULL, -- dem.project_id, --project number
323 NULL, -- dem.task_id, --task number
324 NULL, -- dem.planning_group, --planning group
325 flv.meaning, --bucket type desc
326 c.company_name, --posting supplier name [Owner]
327 item.uom_code, --primary uom
328 item.planner_code, --planner code
329 trunc(sup.new_schedule_date) , --ship_date
330 (trunc(sup.new_schedule_date) + nvl(dem.intransit_lead_time,0)), --receipt_date
331 decode(upper(nvl(mtps.shipping_control, G_CUSTOMER)),
332 G_CUSTOMER, trunc(sup.new_schedule_date),
333 (trunc(sup.new_schedule_date) + nvl(dem.intransit_lead_time,0))), --key_date
334 upper(nvl(mtps.shipping_control, G_CUSTOMER)), -- shipping_control
335 dem.customer_id, --Partner Id
336 dem.customer_site_id, --Partner Site Id
337 dem.organization_id, --Partner Org Id
338 dem.sr_instance_id --Partner Instance Id
339 ;
340
341 CURSOR supply_commits_c1 (
342 p_plan_id in number,
343 p_org_id in number,
344 p_sr_instance_id in number,
345 p_horizon_start in date,
346 p_horizon_end in date,
347 p_planner_code in varchar2,
348 p_abc_class in varchar2,
349 p_item_id in number,
350 p_planning_gp in varchar2,
351 p_project_id in number,
352 p_task_id in number,
353 p_source_customer_id in number,
354 p_source_customer_site_id in number,
355 p_include_so_flag in number,
356 p_language_code in varchar2
357 ) IS
358 select c.company_name, --publisher
359 c.company_id, --publisher id
360 cs.company_site_name, --publisher site
361 cs.company_site_id, --publisher site id
362 nvl(item.base_item_id,item.inventory_item_id), --inventory item id
363 SUM(fcst.quantity), --quantity
364 3, --publisher order type
365 c1.company_name, --customer name
366 c1.company_id, --customer id
367 cs1.company_site_name, --customer site
368 cs1.company_site_id, --customer site id
369 c.company_name, --ship from
370 c.company_id, --ship from id
371 cs.company_site_name, --ship from site
372 cs.company_site_id, --ship from site id
373 c1.company_name, --ship to
374 c1.company_id, --ship to id
375 cs1.company_site_name, --ship to site
376 cs1.company_site_id, --ship to site id
377 mpb.bucket_type, -- bucket type
378 c.company_id, --posting party id
379 null, --fcst.item_name, --publisher item name
380 null, --fcst.description, --publisher item desc
381 'Supply commit', --publisher order type desc
382 NULL, -- fcst.project_id, --project number
383 NULL, -- fcst.task_id, --task number
384 NULL, -- fcst.planning_group, --planning group
385 flv.meaning, -- bucket type description
386 c.company_name, --posting supplier name [Owner]
387 fcst.uom_code, --primary uom
388 fcst.planner_code, --planner code --Bug 4424426
389 fcst.dmd_satisfied_date , --ship_date
390 fcst.planned_arrival_date, --receipt_date
391 decode(upper(nvl(mtps.shipping_control, G_CUSTOMER)),
392 G_CUSTOMER, fcst.dmd_satisfied_date,
393 fcst.planned_arrival_date) key_date,
394 upper(nvl(mtps.shipping_control, G_CUSTOMER)) shipping_control,
395 fcst.customer_id, --Partner Id
396 fcst.customer_site_id, --Partner Site Id
397 fcst.organization_id, --Partner Org Id
398 fcst.sr_instance_id --Partner Instance Id
399 from
400 msc_constrained_forecast_v fcst,
401 msc_system_items item,
402 msc_company_sites cs,
403 msc_company_sites cs1,
404 msc_companies c,
405 msc_companies c1,
406 msc_trading_partner_maps m,
407 msc_trading_partner_maps m1,
408 msc_trading_partner_maps m2,
409 msc_trading_partners t,
410 msc_trading_partners t1,
411 msc_trading_partner_sites mtps, --- PO_SHIP_DATE
412 msc_company_relationships r,
413 msc_plan_buckets mpb,
414 fnd_lookup_values flv
415 where
416 /* msc_system_items msi pk:
417 SR_INSTANCE_ID
418 PLAN_ID
419 ORGANIZATION_ID
420 INVENTORY_ITEM_ID
421
422 msc_trading_partners pk:
423 PARTNER_ID
424 unique Key
425 SR_INSTANCE_ID
426 SR_TP_ID
427 PARTNER_TYPE
428
429 msc_trading_partners.partner_type
430 1 - Suppliers
431 2 - Customers
432 3 - Org
433
434 msc_trading_partner_maps.map_type
435 1 - Trading Partners
436 2 - Planning Org
437 3 - Site
438
439 MAP_TYPE + TP_KEY is unique for MAP_TYPE = 2
440
441 msc_company_sites pk:
442 COMPANY_ID
443 COMPANY_SITE_ID
444
445 msc_companies pk:
446 COMPANY_ID
447
448 msc_trading_partners pk:
449 PARTNER_ID
450 unique Key
451 SR_INSTANCE_ID
452 SR_TP_ID
453 PARTNER_TYPE
454 */
455 t.sr_tp_id = fcst.organization_id and
456 t.sr_instance_id = fcst.sr_instance_id and
457 t.partner_type = 3 and
458 m.tp_key = t.partner_id and
459 m.map_type = 2 and
460 /* Join with Company Site PK.... */
461 m.company_key = cs.company_site_id and
462 /* Join Company Site with Company PK... */
463 c.company_id = cs.company_id and
464 /* driving Company from Customer info */
465 t1.partner_id = fcst.customer_id and
466 t1.partner_type = 2 and
467 /* Partner map: MAP_TYPE + TP_KEY is unique for MAP_TYPE = 1 */
468 m1.tp_key = t1.partner_id and
469 m1.map_type = 1 and
470 /* Customer Map -> Company */
471 /* Object is customer of Subject */
472 m1.company_key = r.relationship_id and
473 r.subject_id = c.company_id and
474 r.object_id = c1.company_id and
475 r.relationship_type = 1 and
476 /* driving Company info from Customer Site */
477 m2.tp_key = fcst.customer_site_id and
478 m2.map_type = 3 and
479 cs1.company_site_id = m2.company_key and
480 cs1.company_id = c1.company_id and
481 fcst.customer_site_id = mtps.partner_site_id and
482 /* Outer Join for Customer Site */
483 /*
484 m2.tp_key (+) = fcst.customer_site_id and
485 m2.map_type (+) = 3 and
486 cs1.company_site_id (+) = m2.company_key and
487 nvl(cs1.company_id, c1.company_id) = c1.company_id
488 */
489 /* Filter conditions */
490 fcst.customer_id is not null and
491 fcst.customer_site_id is not null and
492 fcst.plan_id = p_plan_id and
493 fcst.organization_id = NVL(p_org_id, fcst.organization_id) and
494 fcst.sr_instance_id = NVL(p_sr_instance_id, fcst.sr_instance_id) and
495 fcst.customer_id = NVL(p_source_customer_id, fcst.customer_id) and
496 fcst.customer_site_id = NVL(p_source_customer_site_id, fcst.customer_site_id) and
497 NVL(item.base_item_id, item.inventory_item_id)
498 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
499 from msc_system_items i1
500 where i1.inventory_item_id = nvl(p_item_id, i1.inventory_item_id )
501 and i1.organization_id = item.organization_id
502 and i1.plan_id = item.plan_id
503 and i1.sr_instance_id = item.sr_instance_id) and
504 item.plan_id = fcst.plan_id and
505 item.sr_instance_id = fcst.sr_instance_id and
506 item.organization_id = fcst.organization_id and
507 item.inventory_item_id = fcst.inventory_item_id and
508 NVL(fcst.planner_code,'-99') = NVL(p_planner_code, NVL(fcst.planner_code,'-99')) and
509 NVL(fcst.abc_class_name,'-99') = NVL(p_abc_class,NVL(fcst.abc_class_name,'-99')) and
510 nvl(fcst.planning_group, '-99') = nvl(p_planning_gp, nvl(fcst.planning_group, '-99')) and
511 nvl(fcst.project_id,-99) = nvl(p_project_id, nvl(fcst.project_id,-99)) and
512 nvl(fcst.task_id, -99) = nvl(p_task_id, nvl(fcst.task_id, -99)) and
513 fcst.end_date between nvl(p_horizon_start, fcst.end_date) and nvl(p_horizon_end, fcst.end_date)
514 and fcst.origination_type <> decode(p_include_so_flag, 2, 6, -1)
515 and fcst.origination_type <> decode(p_include_so_flag, 2, 30, -1)
516 and mpb.plan_id = fcst.plan_id
517 and mpb.sr_instance_id = fcst.sr_instance_id
518 and mpb.curr_flag = 1
519 and fcst.end_date between mpb.bkt_start_date and mpb.bkt_end_date
520 and flv.language = p_language_code
521 and flv.lookup_type = 'MSC_X_BUCKET_TYPE'
522 and flv.lookup_code = mpb.bucket_type
523 GROUP BY c.company_name, --publisher
524 c.company_id, --publisher id
525 cs.company_site_name, --publisher site
526 cs.company_site_id, --publisher site id
527 nvl(item.base_item_id,item.inventory_item_id), --inventory item id
528 -- fcst.quantity, --quantity
529 3, --publisher order type
530 c1.company_name, --customer name
531 c1.company_id, --customer id
532 cs1.company_site_name, --customer site
533 cs1.company_site_id, --customer site id
534 c.company_name, --ship from
535 c.company_id, --ship from id
536 cs.company_site_name, --ship from site
537 cs.company_site_id, --ship from site id
538 c1.company_name, --ship to
539 c1.company_id, --ship to id
540 cs1.company_site_name, --ship to site
541 cs1.company_site_id, --ship to site id
542 mpb.bucket_type, --bucket type
543 c.company_id, --posting party id
544 null, --fcst.item_name, --publisher item name
545 null, --fcst.description, --publisher item desc
546 'Supply commit', --publisher order type desc
547 NULL, -- fcst.project_id, --project number
548 NULL, -- fcst.task_id, --task number
549 NULL, -- fcst.planning_group, --planning group
550 flv.meaning, --bucket type desc
551 c.company_name, --posting supplier name [Owner]
552 fcst.uom_code, --primary uom
553 fcst.planner_code, --planner code --Bug 4424426
554 fcst.dmd_satisfied_date , --ship_date
555 fcst.planned_arrival_date, --receipt_date
556 decode(upper(nvl(mtps.shipping_control, G_CUSTOMER)),
557 G_CUSTOMER, fcst.dmd_satisfied_date,
558 fcst.planned_arrival_date), --key_date
559 upper(nvl(mtps.shipping_control, G_CUSTOMER)), -- shipping_control
560 fcst.customer_id, --Partner Id
561 fcst.customer_site_id, --Partner Site Id
562 fcst.organization_id, --Partner Org Id
563 fcst.sr_instance_id --Partner Instance Id
564 ;
565 BEGIN
566 print_debug_info('Just entered publish_supply_commits:');
567
568 if fnd_global.conc_request_id > 0 then
569 select
570 fnd_global.user_id ,
571 fnd_global.user_name --,
572 --fnd_global.resp_name,
573 --fnd_global.application_name
574 into l_user_id,
575 l_user_name --,
576 --l_resp_name,
577 --l_application_name
578 from dual;
579 end if;
580
581 if l_user_id is null then
582 l_language_code := 'US';
583 else
584 l_language := fnd_preference.get(UPPER(l_user_name),'WF','LANGUAGE');
585 IF l_language IS NOT NULL THEN
586 SELECT language_code
587 INTO l_language_code
588 FROM fnd_languages
589 WHERE nls_language = l_language;
590 ELSE
591 l_language_code := 'US';
592 END IF;
593 end if;
594
595 select compile_designator
596 into p_designator
597 from msc_plans
598 where plan_id = p_plan_id;
599
600 BEGIN
601 select MEANING
602 into G_SHIP_CONTROL
603 from fnd_lookup_values
604 where LOOKUP_TYPE = 'MSC_X_SHIPPING_CONTROL'
605 and LOOKUP_CODE =2
606 and language = l_language_code;
607
608 select MEANING
609 into G_ARRIVE_CONTROL
610 from fnd_lookup_values
611 where LOOKUP_TYPE = 'MSC_X_SHIPPING_CONTROL'
612 and LOOKUP_CODE =1
613 and language = l_language_code;
614 EXCEPTION
615 WHEN OTHERS THEN
616 G_SHIP_CONTROL := 'Ship';
617 G_ARRIVE_CONTROL := 'Arrival';
618 END;
619
620 if p_org_code is not null then
621 p_inst_code := substr(p_org_code,1,instr(p_org_code,':')-1);
622 print_debug_info('p_inst_code := ' || p_inst_code);
623
624 begin
625 select instance_id
626 into p_sr_instance_id
627 from msc_apps_instances
628 where instance_code = p_inst_code;
629 print_debug_info('p_sr_instance_id := ' || p_sr_instance_id);
630
631 select sr_tp_id
632 into p_org_id
633 from msc_trading_partners
634 where organization_code = p_org_code and
635 sr_instance_id = p_sr_instance_id and
636 partner_type = 3 and
637 company_id is null;
638 print_debug_info('p_org_id := ' || p_org_id);
639 exception
640 when others then
641 p_org_id := null;
642 p_sr_instance_id := null;
643 end;
644 else
645 p_org_id := null;
646 p_sr_instance_id := null;
647 end if;
648
649 if p_auto_version = 1 then
650 l_version := nvl(p_version,0) + 1;
651 else
652 l_version := null;
653 end if;
654
655 l_log_message := get_message('MSC','MSC_X_PUB_SC',l_language_code) || ' ' || fnd_global.local_chr(10) ||
656 get_message('MSC','MSC_X_PUB_PLAN',l_language_code) || ': ' || p_designator || fnd_global.local_chr(10);
657
658 IF p_org_code IS NOT NULL THEN
659 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ORG',l_language_code) || ': ' || p_org_code || fnd_global.local_chr(10);
660 END IF;
661
662 IF p_item_id IS NOT NULL THEN
663 SELECT item_name
664 INTO l_item_name
665 FROM msc_items
666 WHERE inventory_item_id = p_item_id;
667 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ITEM',l_language_code) || ': ' || l_item_name || fnd_global.local_chr(10);
668 END IF;
669
670 IF p_source_customer_id IS NOT NULL THEN
671 SELECT partner_name
672 INTO l_cust_name
673 FROM msc_trading_partners
674 WHERE partner_id = p_source_customer_id;
675 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_CUSTOMER',l_language_code) || ': ' || l_cust_name || fnd_global.local_chr(10);
676 END IF;
677
678 IF p_source_customer_site_id IS NOT NULL THEN
679 SELECT location
680 INTO l_cust_site
681 FROM msc_trading_partner_sites
682 WHERE partner_id = p_source_customer_id
683 AND partner_site_id = p_source_customer_site_id
684 AND tp_site_code = 'SHIP_TO';
685 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_CUST_SITE',l_language_code) || ': ' || l_cust_site || fnd_global.local_chr(10);
686 END IF;
687
688 --------------------------------------------------------------------------
689 -- set the standard date as canonical date
690 --------------------------------------------------------------------------
691 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start);
692 l_horizon_end := fnd_date.canonical_to_date(p_horizon_end);
693
694
695 IF p_planner_code IS NOT NULL THEN
696 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PLANNER',l_language_code) || ': ' || p_planner_code || fnd_global.local_chr(10);
697 END IF;
698
699 IF p_planning_gp IS NOT NULL THEN
700 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PLAN_GP',l_language_code) || ': ' || p_planning_gp || fnd_global.local_chr(10);
701 END IF;
702
703 IF p_project_id IS NOT NULL THEN
704 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PROJ_NUM',l_language_code) || ': ' || p_project_id || fnd_global.local_chr(10);
705 END IF;
706
707 IF p_task_id IS NOT NULL THEN
708 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_TASK_NUM',l_language_code) || ': ' || p_task_id || fnd_global.local_chr(10);
709 END IF;
710
711 IF p_abc_class IS NOT NULL THEN
712 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ABC_CLASS',l_language_code) || ': ' || p_abc_class || fnd_global.local_chr(10);
713 END IF;
714 log_message(l_log_message);
715
716 l_log_message := get_message('MSC','MSC_X_PUB_DEL_SC',l_language_code) || fnd_global.local_chr(10);
717 log_message(l_log_message);
718
719 delete_old_forecast(
720 p_plan_id,
721 p_org_id,
722 p_sr_instance_id,
723 p_planner_code,
724 p_abc_class,
725 p_item_id,
726 p_planning_gp,
727 p_project_id,
728 p_task_id,
729 p_source_customer_id,
730 p_source_customer_site_id,
731 l_horizon_start,
732 l_horizon_end,
733 p_overwrite
734 );
735
736
737 print_debug_info('p_plan_id := ' || p_plan_id);
738 print_debug_info('p_org_id := ' || p_org_id);
739 print_debug_info('p_sr_instance_id := ' || p_sr_instance_id);
740 print_debug_info('p_horizon_start := ' || p_horizon_start);
741 print_debug_info('p_horizon_end := ' || p_horizon_end);
742 print_debug_info('p_planner_code := ' || p_planner_code);
743 print_debug_info('p_include_so_flag := ' || p_include_so_flag);
744 print_debug_info('p_item_id := ' || p_item_id);
745 print_debug_info('p_source_customer_id := ' || p_source_customer_id);
746 print_debug_info('p_source_customer_site_id := ' || p_source_customer_site_id);
747
748 -- bug#6345381
749 -- dejoshi
750
751 IF (nvl(FND_PROFILE.VALUE('MSC_PUBLISH_SUPPLY_COMMIT'),'N') = 'N') THEN
752 OPEN supply_commits_c1 (
753 p_plan_id
754 ,p_org_id
755 ,p_sr_instance_id
756 ,l_horizon_start
757 ,l_horizon_end
758 ,p_planner_code
759 ,p_abc_class
760 ,p_item_id
761 ,p_planning_gp
762 ,p_project_id
763 ,p_task_id
764 ,p_source_customer_id
765 ,p_source_customer_site_id
766 ,p_include_so_flag
767 ,l_language_code
768 );
769
770 FETCH supply_commits_c1 BULK COLLECT INTO
771 t_pub
772 ,t_pub_id
773 ,t_pub_site
774 ,t_pub_site_id
775 ,t_item_id
776 ,t_qty
777 ,t_pub_ot
778 ,t_cust
779 ,t_cust_id
780 ,t_cust_site
781 ,t_cust_site_id
782 ,t_ship_from
783 ,t_ship_from_id
784 ,t_ship_from_site
785 ,t_ship_from_site_id
786 ,t_ship_to
787 ,t_ship_to_id
788 ,t_ship_to_site
789 ,t_ship_to_site_id
790 ,t_bkt_type
791 ,t_posting_party_id
792 ,t_item_name
793 ,t_item_desc
794 ,t_pub_ot_desc
795 ,t_proj_number
796 ,t_task_number
797 ,t_planning_gp
798 ,t_bkt_type_desc
799 ,t_posting_party_name
800 ,t_uom_code
801 ,t_planner_code
802 ,t_ship_date
803 ,t_receipt_date
804 ,t_key_date
805 ,t_shipping_control
806 ,t_src_cust_id
807 ,t_src_cust_site_id
808 ,t_src_org_id
809 ,t_src_instance_id;
810 CLOSE supply_commits_c1;
811
812 print_debug_info('New Records fetched by cursor := ' || t_pub.COUNT);
813 ELSE
814
815
816 OPEN supply_commits_c2 (
817 p_plan_id
818 ,p_org_id
819 ,p_sr_instance_id
820 ,l_horizon_start
821 ,l_horizon_end
822 ,p_planner_code
823 ,p_abc_class
824 ,p_item_id
825 ,p_planning_gp
826 ,p_project_id
827 ,p_task_id
828 ,p_source_customer_id
829 ,p_source_customer_site_id
830 ,p_include_so_flag
831 ,l_language_code
832 );
833
834 FETCH supply_commits_c2 BULK COLLECT INTO
835 t_pub
836 ,t_pub_id
837 ,t_pub_site
838 ,t_pub_site_id
839 ,t_item_id
840 ,t_qty
841 ,t_pub_ot
842 ,t_cust
843 ,t_cust_id
844 ,t_cust_site
845 ,t_cust_site_id
846 ,t_ship_from
847 ,t_ship_from_id
848 ,t_ship_from_site
849 ,t_ship_from_site_id
850 ,t_ship_to
851 ,t_ship_to_id
852 ,t_ship_to_site
853 ,t_ship_to_site_id
854 ,t_bkt_type
855 ,t_posting_party_id
856 ,t_item_name
857 ,t_item_desc
858 ,t_pub_ot_desc
859 ,t_proj_number
860 ,t_task_number
861 ,t_planning_gp
862 ,t_bkt_type_desc
863 ,t_posting_party_name
864 ,t_uom_code
865 ,t_planner_code
866 ,t_ship_date
867 ,t_receipt_date
868 ,t_key_date
869 ,t_shipping_control
870 ,t_src_cust_id
871 ,t_src_cust_site_id
872 ,t_src_org_id
873 ,t_src_instance_id;
874 CLOSE supply_commits_c2;
875 print_debug_info('Used Supply_commits_cursor2 as the value of the profile is ' || FND_PROFILE.VALUE('MSC_PUBLISH_SUPPLY_COMMIT'));
876 END IF;
877 -- End of bug#6345381 changes
878 IF t_pub IS NOT NULL AND t_pub.COUNT > 0 THEN
879 print_debug_info('Records fetched by cursor := ' || t_pub.COUNT);
880 l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',l_language_code) || ': ' || t_pub.COUNT || '.' || fnd_global.local_chr(10);
881 log_message(l_log_message);
882
883 get_optional_info(
884 t_item_id,
885 t_pub_id,
886 t_cust_id,
887 t_cust_site_id,
888 t_src_cust_id,
889 t_src_cust_site_id,
890 t_src_org_id,
891 t_src_instance_id,
892 t_item_name,
893 t_uom_code,
894 t_qty,
895 t_receipt_date,
896 t_tp_receipt_date,
897 --t_tp_item_name,
898 t_master_item_name,
899 t_master_item_desc,
900 t_cust_item_name,
901 t_cust_item_desc,
902 t_tp_uom,
903 t_tp_qty,
904 t_item_desc
905 );
906
907 -- check for the latest version, in case if another user has updated it
908 IF p_auto_version = 1 THEN
909 SELECT nvl(publish_supply_commit_version,0)+1 INTO l_new_version
910 FROM msc_plans
911 WHERE plan_id = p_plan_id;
912 IF l_version <> l_new_version THEN
913 print_debug_info('Warning: Someone has already published supply commit with the version '||l_version||'. The new version is ' ||l_new_version);
914 l_version := l_new_version;
915 END IF;
916 END IF;
917
918 print_debug_info('before insert_into_sup_dem');
919 insert_into_sup_dem(
920 t_pub
921 ,t_pub_id
922 ,t_pub_site
923 ,t_pub_site_id
924 ,t_item_id
925 ,t_qty
926 ,t_pub_ot
927 ,t_cust
928 ,t_cust_id
929 ,t_cust_site
930 ,t_cust_site_id
931 ,t_ship_from
932 ,t_ship_from_id
933 ,t_ship_from_site
934 ,t_ship_from_site_id
935 ,t_ship_to
936 ,t_ship_to_id
937 ,t_ship_to_site
938 ,t_ship_to_site_id
939 ,t_bkt_type
940 ,t_posting_party_id
941 ,t_item_name
942 ,t_item_desc
943 ,t_master_item_name
944 ,t_master_item_desc
945 ,t_cust_item_name
946 ,t_cust_item_desc
947 ,t_pub_ot_desc
948 ,t_proj_number
949 ,t_task_number
950 ,t_planning_gp
951 ,t_bkt_type_desc
952 ,t_posting_party_name
953 ,t_uom_code
954 ,t_planner_code
955 ,t_ship_date
956 ,t_receipt_date
957 ,t_tp_item_name
958 ,t_tp_uom
959 ,t_tp_qty
960 ,l_version
961 ,p_designator
962 ,l_user_id
963 ,t_shipping_control
964 ,t_key_date
965 );
966 ELSE
967 l_cursor1 := 0;
968 end if;
969 commit;
970
971
972 IF l_cursor1 = 0 THEN
973 l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',l_language_code) || ': ' || 0 || '.' || fnd_global.local_chr(10);
974 log_message(l_log_message);
975 END IF;
976
977 IF l_version IS NOT NULL THEN
978 BEGIN
979 SELECT 1 INTO l_records_exist
980 FROM dual
981 WHERE exists ( SELECT 1
982 FROM msc_sup_dem_entries
983 WHERE plan_id = -1
984 AND publisher_order_type = 3
985 AND publisher_id = 1
986 AND designator = p_designator
987 AND version = l_version);
988 EXCEPTION
989 WHEN OTHERS then
990 l_records_exist := 0;
991 END;
992 IF l_records_exist = 1 then
993 l_log_message := get_message('MSC','MSC_X_PUB_NEW_VERSION_SC',l_language_code) || ' ' || l_version || '.' || fnd_global.local_chr(10);
994 log_message(l_log_message);
995
996 --update version number in msc_plans
997
998 UPDATE msc_plans
999 SET publish_supply_commit_version = l_version
1000 WHERE plan_id = p_plan_id;
1001
1002 END IF;
1003 END IF;
1004
1005 /*--------------------------------------------------------------------------------
1006 Launch the notification here
1007 --------------------------------------------------------------------------------*/
1008
1009 msc_x_wfnotify_pkg.Launch_Publish_WF (p_errbuf,
1010 p_retcode,
1011 p_designator,
1012 l_version,
1013 l_horizon_start,
1014 l_horizon_end,
1015 p_plan_id,
1016 p_sr_instance_id,
1017 p_org_id,
1018 p_item_id,
1019 null, --p_supplier_id,
1020 null, --p_supplier_site_id,
1021 p_source_customer_id,
1022 p_source_customer_site_id,
1023 p_planner_code,
1024 p_abc_class,
1025 p_planning_gp,
1026 p_project_id,
1027 p_task_id,
1028 G_SUPPLY_COMMIT);
1029
1030
1031
1032 -- launch SCEM engine
1033 IF ( ( FND_PROFILE.VALUE('MSC_X_AUTO_SCEM_MODE') = 2
1034 OR FND_PROFILE.VALUE('MSC_X_AUTO_SCEM_MODE') = 3
1035 ) -- PUBLISH or ALL
1036 AND ( FND_PROFILE.VALUE('MSC_X_CONFIGURATION') = 2
1037 OR FND_PROFILE.VALUE('MSC_X_CONFIGURATION') = 3
1038 ) -- APS+CP or CP
1039 ) THEN
1040 BEGIN
1041 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Launching SCEM engine');
1042 MSC_X_CP_FLOW.Start_SCEM_Engine_WF;
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_CP_FLOW.Start_SCEM_Engine_WF');
1046 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1047 END;
1048 END IF;
1049
1050 -- add exception handler
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in msc_sce_pub_supply_commit_pkg.publish_supply_commits');
1054 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1055
1056 END publish_supply_commits;
1057
1058
1059 PROCEDURE get_optional_info(
1060 t_item_id IN numberList,
1061 t_pub_id IN numberList,
1062 t_cust_id IN numberList,
1063 t_cust_site_id IN numberList,
1064 t_src_cust_id IN numberList,
1065 t_src_cust_site_id IN numberList,
1066 t_src_org_id IN numberList,
1067 t_src_instance_id IN numberList,
1068 t_item_name IN OUT NOCOPY itemNameList,
1069 t_uom_code IN itemUomList,
1070 t_qty IN numberList,
1071 t_receipt_date IN dateList,
1072 t_tp_receipt_date IN OUT NOCOPY dateList,
1073 --t_tp_item_name IN OUT NOCOPY itemNameList,
1074 t_master_item_name IN OUT NOCOPY itemNameList,
1075 t_master_item_desc IN OUT NOCOPY itemDescList,
1076 t_cust_item_name IN OUT NOCOPY itemNameList,
1077 t_cust_item_desc IN OUT NOCOPY itemDescList,
1078 t_tp_uom IN OUT NOCOPY itemUomList,
1079 t_tp_qty IN OUT NOCOPY numberList,
1080 t_item_desc IN OUT NOCOPY itemDescList
1081 ) IS
1082
1083 l_conversion_found boolean;
1084 l_conversion_rate number;
1085 l_to_location_id number;
1086 l_org_location_id number;
1087 l_lead_time number;
1088 l_tp_cust_id number;
1089 l_tp_cust_site_id number;
1090 l_session_id number;
1091 l_regions_return_status VARCHAR2(1);
1092
1093 BEGIN
1094
1095 for j in t_item_id.FIRST..t_item_id.LAST loop
1096 t_tp_receipt_date.EXTEND;
1097 --t_tp_item_name.EXTEND;
1098 t_tp_uom.EXTEND;
1099 t_tp_qty.EXTEND;
1100 t_master_item_name.EXTEND;
1101 t_master_item_desc.EXTEND;
1102 t_cust_item_name.EXTEND;
1103 t_cust_item_desc.EXTEND;
1104 /*-------------------------------------------------------------------------------
1105 get the item_description -- this works for both standard item or base item
1106 ---------------------------------------------------------------------------------*/
1107 begin
1108 select item_name, description
1109 into t_item_name(j), t_item_desc(j)
1110 from msc_system_items
1111 where sr_instance_id = t_src_instance_id(j)
1112 and organization_id = t_src_org_id(j)
1113 and plan_id = -1
1114 and inventory_item_id = t_item_id(j);
1115
1116 exception
1117 when others then
1118 t_item_name(j) := null;
1119 t_item_desc(j) := null;
1120 end;
1121
1122
1123 begin
1124 select item_name,
1125 description
1126 into t_master_item_name(j),
1127 t_master_item_desc(j)
1128 from msc_items
1129 where inventory_item_id = t_item_id(j);
1130 exception
1131 when others then
1132 t_master_item_name(j) := t_item_name(j);
1133 t_master_item_desc(j) := null;
1134 end;
1135
1136 begin
1137 select customer_item_name,
1138 uom_code
1139 into t_cust_item_name(j),
1140 t_tp_uom(j)
1141 from msc_item_customers mcf,
1142 msc_trading_partner_maps m,
1143 msc_trading_partner_maps m2,
1144 msc_company_relationships r
1145 where mcf.inventory_item_id = t_item_id(j) and
1146 r.relationship_type = 1 and
1147 r.subject_id = t_pub_id(j) and
1148 r.object_id = t_cust_id(j) and
1149 m.map_type = 1 and
1150 m.company_key = r.relationship_id and
1151 mcf.customer_id = m.tp_key and
1152 m2.map_type = 3 and
1153 m2.company_key = t_cust_site_id(j) and
1154 mcf.customer_site_id = m2.tp_key;
1155 exception
1156 when NO_DATA_FOUND then
1157 t_cust_item_name(j) := null;
1158 t_tp_uom(j) := t_uom_code(j);
1159 end;
1160
1161 msc_x_util.get_uom_conversion_rates( t_uom_code(j),
1162 t_tp_uom(j),
1163 t_item_id(j),
1164 l_conversion_found,
1165 l_conversion_rate);
1166 if l_conversion_found then
1167 t_tp_qty(j) := nvl(t_qty(j),0)* l_conversion_rate;
1168 else
1169 t_tp_qty(j) := t_qty(j);
1170 end if;
1171
1172 print_debug_info('Item id' || t_item_id(j));
1173 --print_debug_info('receipt date ' || t_tp_receipt_date(j));
1174
1175 end loop;
1176
1177 -- added exception handler
1178 EXCEPTION WHEN OTHERS THEN
1179 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in msc_sce_pub_supply_commit_pkg.get_optional_info');
1180 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1181
1182 END get_optional_info;
1183
1184
1185 PROCEDURE insert_into_sup_dem (
1186 t_pub IN companyNameList,
1187 t_pub_id IN numberList,
1188 t_pub_site IN companySiteList,
1189 t_pub_site_id IN numberList,
1190 t_item_id IN numberList,
1191 t_qty IN numberList,
1192 t_pub_ot IN numberList,
1193 t_cust IN companyNameList,
1194 t_cust_id IN numberList,
1195 t_cust_site IN companySiteList,
1196 t_cust_site_id IN numberList,
1197 t_ship_from IN companyNameList,
1198 t_ship_from_id IN numberList,
1199 t_ship_from_site IN companySiteList,
1200 t_ship_from_site_id IN numberList,
1201 t_ship_to IN companyNameList,
1202 t_ship_to_id IN numberList,
1203 t_ship_to_site IN companySiteList,
1204 t_ship_to_site_id IN numberList,
1205 t_bkt_type IN numberList,
1206 t_posting_party_id IN numberList,
1207 t_item_name IN itemNameList,
1208 t_item_desc IN itemDescList,
1209 t_master_item_name IN itemNameList,
1210 t_master_item_desc IN itemDescList,
1211 t_cust_item_name IN itemNameList,
1212 t_cust_item_desc IN itemDescList,
1213 t_pub_ot_desc IN fndMeaningList,
1214 t_proj_number IN numberList,
1215 t_task_number IN numberList,
1216 t_planning_gp IN planningGroupList,
1217 t_bkt_type_desc IN fndMeaningList,
1218 t_posting_party_name IN companyNameList,
1219 t_uom_code IN itemUomList,
1220 t_planner_code IN plannerCodeList,
1221 t_ship_date IN dateList,
1222 t_receipt_date IN dateList,
1223 t_tp_item_name IN itemNameList,
1224 t_tp_uom IN itemUomList,
1225 t_tp_qty IN numberList,
1226 p_version IN varchar2,
1227 p_designator IN VARCHAR2,
1228 p_user_id IN number,
1229 t_shipping_control IN shippingControlList,
1230 t_key_date IN dateList
1231 ) IS
1232 l_order_type_desc varchar2(80);
1233 BEGIN
1234
1235 BEGIN
1236 select meaning
1237 into l_order_type_desc
1238 from mfg_lookups
1239 where lookup_type = 'MSC_X_ORDER_TYPE'
1240 and lookup_code = 3;
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 l_order_type_desc := 'Supply commit';
1244 END;
1245
1246 FORALL j in t_pub.FIRST..t_pub.LAST
1247 insert into msc_sup_dem_entries (
1248 transaction_id,
1249 plan_id,
1250 sr_instance_id,
1251 publisher_name,
1252 publisher_id,
1253 publisher_site_name,
1254 publisher_site_id,
1255 customer_name,
1256 customer_id,
1257 customer_site_name,
1258 customer_site_id,
1259 supplier_name,
1260 supplier_id,
1261 supplier_site_name,
1262 supplier_site_id,
1263 ship_from_party_name,
1264 ship_from_party_id,
1265 ship_from_party_site_name,
1266 ship_from_party_site_id,
1267 ship_to_party_name,
1268 ship_to_party_id,
1269 ship_to_party_site_name,
1270 ship_to_party_site_id,
1271 publisher_order_type,
1272 publisher_order_type_desc,
1273 bucket_type_desc,
1274 bucket_type,
1275 --publisher_item_name,
1276 --trading_partner_item_name,
1277 item_name,
1278 item_description,
1279 owner_item_name,
1280 owner_item_description,
1281 supplier_item_name,
1282 supplier_item_description,
1283 customer_item_name,
1284 customer_item_description,
1285 inventory_item_id,
1286 --pub_item_description,
1287 primary_uom,
1288 uom_code,
1289 tp_uom_code,
1290 key_date,
1291 ship_date,
1292 receipt_date,
1293 shipping_control,
1294 shipping_control_code,
1295 quantity,
1296 primary_quantity,
1297 tp_quantity,
1298 last_refresh_number,
1299 posting_party_name,
1300 posting_party_id,
1301 created_by,
1302 creation_date,
1303 last_updated_by,
1304 last_update_date,
1305 project_number,
1306 task_number,
1307 planning_group,
1308 planner_code,
1309 version,
1310 designator
1311 ) values (
1312 msc_sup_dem_entries_s.nextval,
1313 -1,
1314 -1,
1315 t_pub(j),
1316 t_pub_id(j),
1317 t_pub_site(j),
1318 t_pub_site_id(j),
1319 t_cust(j),
1320 t_cust_id(j),
1321 t_cust_site(j),
1322 t_cust_site_id(j),
1323 t_pub(j),
1324 t_pub_id(j),
1325 t_pub_site(j),
1326 t_pub_site_id(j),
1327 t_ship_from(j),
1328 t_ship_from_id(j),
1329 t_ship_from_site(j),
1330 t_ship_from_site_id(j),
1331 t_ship_to(j),
1332 t_ship_to_id(j),
1333 t_ship_to_site(j),
1334 t_ship_to_site_id(j),
1335 t_pub_ot(j),
1336 --t_pub_ot_desc(j),
1337 l_order_type_desc,
1338 t_bkt_type_desc(j),
1339 t_bkt_type(j),
1340 --t_item_name(j),
1341 --t_tp_item_name(j),
1342 t_master_item_name(j),
1343 nvl(t_master_item_desc(j), t_item_desc(j)),
1344 t_item_name(j),
1345 t_item_desc(j),
1346 t_item_name(j),
1347 t_item_desc(j),
1348 t_cust_item_name(j),
1349 t_cust_item_desc(j),
1350 t_item_id(j),
1351 --t_item_desc(j),
1352 t_uom_code(j),
1353 t_uom_code(j),
1354 t_tp_uom(j),
1355 t_key_date(j),
1356 t_ship_date(j),
1357 t_receipt_date(j),
1358 decode(t_shipping_control(j),G_CUSTOMER,G_SHIP_CONTROL,
1359 G_ARRIVE_CONTROL),
1360 decode(t_shipping_control(j),G_CUSTOMER,2,
1361 1),
1362 t_qty(j),
1363 t_qty(j),
1364 t_tp_qty(j),
1365 msc_cl_refresh_s.nextval,
1366 t_posting_party_name(j),
1367 t_posting_party_id(j),
1368 p_user_id,
1369 sysdate,
1370 p_user_id,
1371 sysdate,
1372 t_proj_number(j),
1373 t_task_number(j),
1374 t_planning_gp(j),
1375 t_planner_code(j),
1376 p_version,
1377 p_designator
1378 );
1379
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 print_user_info('Error in msc_sce_pub_supply_commit_pkg.insert_into_sup_dem');
1383 print_user_info(SQLERRM);
1384 END insert_into_sup_dem;
1385
1386
1387 PROCEDURE delete_old_forecast(
1388 p_plan_id in number,
1389 p_org_id in number,
1390 p_sr_instance_id in number,
1391 p_planner_code in varchar2,
1392 p_abc_class in varchar2,
1393 p_item_id in number,
1394 p_planning_gp in varchar2,
1395 p_project_id in number,
1396 p_task_id in number,
1397 p_source_customer_id in number,
1398 p_source_customer_site_id in number,
1399 p_horizon_start in date,
1400 p_horizon_end in date,
1401 p_overwrite in number
1402 ) IS
1403 --t_publisher_site_id numberList;
1404 l_customer_id number;
1405 l_customer_site_id number;
1406 l_row number;
1407
1408 BEGIN
1409 --print_debug_info('In delete_old_forecast');
1410
1411 if p_source_customer_id is not null then
1412 BEGIN
1413 select c.company_id
1414 into l_customer_id
1415 from msc_trading_partner_maps m,
1416 msc_company_relationships r,
1417 msc_companies c
1418 where m.tp_key = p_source_customer_id and
1419 m.map_type = 1 and
1420 m.company_key = r.relationship_id and
1421 r.relationship_type = 1 and
1422 r.subject_id = 1 and /* Owner Company Id */
1423 c.company_id = r.object_id;
1424 EXCEPTION
1425 WHEN NO_DATA_FOUND THEN
1426 l_customer_id := NULL;
1427 WHEN OTHERS THEN
1428 l_customer_id := NULL;
1429 END;
1430 else
1431 l_customer_id := null;
1432 end if;
1433
1434 --print_debug_info('l_customer_id := ' || l_customer_id);
1435 --Bug 4116657..
1436 if p_source_customer_site_id is not null then
1437 BEGIN
1438 select cs.company_site_id
1439 into l_customer_site_id
1440 from msc_trading_partner_maps m,
1441 msc_company_sites cs
1442 where m.tp_key = p_source_customer_site_id and
1443 m.map_type = 3 and
1444 cs.company_site_id = m.company_key;
1445 EXCEPTION
1446 WHEN NO_DATA_FOUND THEN
1447 l_customer_site_id := null;
1448 WHEN OTHERS THEN
1449 l_customer_site_id := null;
1450 END;
1451 else
1452 l_customer_site_id := null;
1453 end if;
1454
1455 --print_debug_info('l_customer_site_id := ' || l_customer_site_id);
1456
1457 IF (p_overwrite = 1) THEN -- delete all
1458 delete from msc_sup_dem_entries sd
1459 where sd.publisher_order_type = 3 and
1460 sd.plan_id = -1 and
1461 sd.publisher_id = 1 and
1462 sd.publisher_site_id IN (select cs.company_site_id
1463 from msc_plan_organizations o,
1464 msc_company_sites cs,
1465 msc_trading_partner_maps m,
1466 msc_trading_partners p
1467 where o.plan_id = p_plan_id and
1468 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
1469 p.sr_instance_id = nvl(p_sr_instance_id, o.sr_instance_id) and
1470 p.partner_type = 3 and
1471 m.tp_key = p.partner_id and
1472 m.map_type = 2 and
1473 cs.company_site_id = m.company_key and
1474 cs.company_id = 1) and
1475 sd.customer_id = nvl(l_customer_id, sd.customer_id) and
1476 sd.customer_site_id = nvl(l_customer_site_id, sd.customer_site_id) and
1477 NVL(sd.base_item_id, sd.inventory_item_id) IN (select nvl(i.base_item_id,i.inventory_item_id)
1478 from msc_system_items i,
1479 msc_plan_organizations o
1480 where o.plan_id = p_plan_id and
1481 i.plan_id = o.plan_id and
1482 i.organization_id = nvl(p_org_id,
1483 o.organization_id) and
1484 i.sr_instance_id = nvl(p_sr_instance_id,
1485 o.sr_instance_id) and
1486 NVL(i.planner_code,'-99') = NVL(p_planner_code,
1487 NVL(i.planner_code,'-99')) and
1488 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
1489 NVL(i.abc_class_name,'-99')) and
1490 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id))
1491 and
1492 NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) ; --bug 4344713
1493 --NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
1494 --NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
1495 --NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99'));
1496
1497 l_row := SQL%ROWCOUNT;
1498 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
1499
1500 ELSIF p_overwrite = 2 THEN
1501 delete from msc_sup_dem_entries sd
1502 where sd.publisher_order_type = 3 and
1503 sd.plan_id = -1 and
1504 sd.publisher_id = 1 and
1505 sd.publisher_site_id IN (select cs.company_site_id
1506 from msc_plan_organizations o,
1507 msc_company_sites cs,
1508 msc_trading_partner_maps m,
1509 msc_trading_partners p
1510 where o.plan_id = p_plan_id and
1511 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
1512 p.sr_instance_id = nvl(p_sr_instance_id, o.sr_instance_id) and
1513 p.partner_type = 3 and
1514 m.tp_key = p.partner_id and
1515 m.map_type = 2 and
1516 cs.company_site_id = m.company_key and
1517 cs.company_id = 1) and
1518 sd.customer_id = nvl(l_customer_id, sd.customer_id) and
1519 sd.customer_site_id = nvl(l_customer_site_id, sd.customer_site_id) and
1520 NVL(sd.base_item_id, sd.inventory_item_id) IN (select nvl(i.base_item_id,i.inventory_item_id)
1521 from msc_system_items i,
1522 msc_plan_organizations o
1523 where o.plan_id = p_plan_id and
1524 i.plan_id = o.plan_id and
1525 i.organization_id = nvl(p_org_id,
1526 o.organization_id) and
1527 i.sr_instance_id = nvl(p_sr_instance_id,
1528 o.sr_instance_id) and
1529 NVL(i.planner_code,'-99') = NVL(p_planner_code,
1530 NVL(i.planner_code,'-99')) and
1531 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
1532 NVL(i.abc_class_name,'-99')) and
1533 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)) and
1534 NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and --bug 4344713
1535 --NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
1536 --NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
1537 --NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
1538 sd.ship_date between NVL(p_horizon_start, sd.ship_date) and NVL(p_horizon_end, sd.ship_date);
1539
1540 l_row := SQL%ROWCOUNT;
1541 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
1542 END IF;
1543
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 print_user_info('Error in msc_sce_pub_supply_commit_pkg.delete_old_forecast');
1547 print_user_info(SQLERRM);
1548 END delete_old_forecast;
1549
1550 PROCEDURE LOG_MESSAGE(
1551 p_string IN VARCHAR2
1552 ) IS
1553 BEGIN
1554 IF fnd_global.conc_request_id > 0 THEN
1555 FND_FILE.PUT_LINE(FND_FILE.LOG, p_string);
1556 ELSE
1557 --DBMS_OUTPUT.PUT_LINE( p_string);
1558 null;
1559 END IF;
1560 END LOG_MESSAGE;
1561
1562 FUNCTION get_message (
1563 p_app IN VARCHAR2,
1564 p_name IN VARCHAR2,
1565 p_lang IN VARCHAR2
1566 ) RETURN VARCHAR2 IS
1567 msg VARCHAR2(2000) := NULL;
1568 CURSOR c1(app_name VARCHAR2, msg_name VARCHAR2, lang VARCHAR2) IS
1569 SELECT m.message_text
1570 FROM fnd_new_messages m,
1571 fnd_application a
1572 WHERE m.message_name = msg_name AND
1573 m.language_code = lang AND
1574 a.application_short_name = app_name AND
1575 m.application_id = a.application_id;
1576 BEGIN
1577 OPEN c1(p_app, p_name, p_lang);
1578 FETCH c1 INTO msg;
1579 IF (c1%NOTFOUND) then
1580 msg := p_name;
1581 END IF;
1582 CLOSE c1;
1583 RETURN msg;
1584 END get_message;
1585
1586 -- This procesure prints out debug information
1587 PROCEDURE print_debug_info(
1588 p_debug_info IN VARCHAR2
1589 )IS
1590
1591 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
1592
1593 BEGIN
1594 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
1595 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
1596 END IF;
1597 --dbms_output.put_line(p_debug_info); -- ut
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 RAISE;
1601 END print_debug_info;
1602
1603 -- This procesure prints out message to user
1604 PROCEDURE print_user_info(
1605 p_user_info IN VARCHAR2
1606 )IS
1607 BEGIN
1608 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
1609 -- dbms_output.put_line(p_user_info); -- ut
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 RAISE;
1613 END print_user_info;
1614
1615 END msc_sce_pub_supply_commit_pkg;