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