[Home] [Help]
PACKAGE BODY: APPS.MSC_SCE_PUBLISH_PKG
Source
1 PACKAGE BODY msc_sce_publish_pkg AS
2 /* $Header: MSCXPUBB.pls 120.10 2011/12/23 08:37:02 sbnaik ship $ */
3
4
5 PROCEDURE publish_plan_orders (
6 p_errbuf out nocopy varchar2,
7 p_retcode out nocopy varchar2,
8 p_plan_id in number,
9 p_org_code in varchar2 default null,
10 p_planner_code in varchar2 default null,
11 p_abc_class in varchar2 default null,
12 p_item_id in number default null,
13 p_item_list in varchar2 default null,
14 p_planning_gp in varchar2 default null,
15 p_project_id in number default null,
16 p_task_id in number default null,
17 p_supplier_id in number default null,
18 p_supplier_site_id in number default null,
19 p_horizon_start in varchar2,
20 p_horizon_end in varchar2,
21 p_auto_version in number default 1,
22 p_version in number default null,
23 p_purchase_order in number default 2,
24 p_requisition in number default 2,
25 p_overwrite in number default 1,
26 p_publish_dos in number default 1 -- bug#6893383 **SPP-Publish dos for defective supplier**
27 ) IS
28
29 p_org_id Number;
30 p_inst_code Varchar2(3);
31 p_sr_instance_id Number;
32 p_category_set_id Number := null;
33 p_category_name Varchar2(240) := null;
34 p_designator Varchar2(10);
35 l_version Number;
36 l_user_id NUMBER;
37 l_user_name VARCHAR2(100);
38 l_resp_name VARCHAR2(30);
39 l_application_name VARCHAR2(50);
40 l_item_name VARCHAR2(255);
41 l_log_message VARCHAR2(1000);
42 l_supp_name VARCHAR2(100);
43 l_supp_site VARCHAR2(30);
44 l_records_exist NUMBER;
45 l_cursor1 NUMBER;
46 l_cursor2 NUMBER;
47 l_language VARCHAR2(30);
48 l_language_code VARCHAR2(4);
49 l_purchase_order number;
50 l_requisition number;
51 l_external_repair_order number; --bug#6893383
52 l_plan_type number; --bug#6893383
53
54 l_horizon_start date; --canonical date
55 l_horizon_end date; --canonical date
56
57 t_pub companyNameList := companyNameList();
58 t_pub_id numberList := numberList();
59 t_pub_site companySiteList := companySiteList();
60 t_pub_site_id numberList := numberList();
61 t_org_id numberList;
62 t_sr_instance_id numberList;
63 t_item_id numberList;
64 t_order_type numberList;
65 t_qty numberList;
66 t_planned_order_qty numberList;
67 t_released_qty numberList;
68 t_pub_ot numberList;
69 t_supp companyNameList := companyNameList();
70 t_supp_id numberList := numberList();
71 t_supp_site companySiteList := companySiteList();
72 t_supp_site_id numberList := 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_ship_from companyNameList;
78 t_ship_from_id numberList;
79 t_ship_from_site companySiteList;
80 t_ship_from_site_id numberList;
81 t_bkt_type numberList;
82 t_posting_party_id numberList;
83 t_owner_item_name itemNameList;
84 t_owner_item_desc itemDescList;
85 t_pub_ot_desc fndMeaningList;
86 t_proj_number numberList;
87 t_task_number numberList;
88 t_planning_gp planningGroupList;
89 t_bkt_type_desc fndMeaningList;
90 t_posting_party_name companyNameList;
91 t_uom_code itemUomList;
92 t_planner_code plannerCodeList;
93 t_bucket_type numberList;
94 t_key_date dateList;
95 t_ship_date dateList;
96 t_receipt_date dateList;
97 t_source_supp_id numberList;
98 t_source_supp_site_id numberList;
99 t_order_num orderNumList; -- bug#7310179
100 t_line_num lineNumList; -- bug#7310179
101 t_master_item_name itemNameList := itemNameList();
102 t_master_item_desc itemDescList := itemDescList();
103 t_supp_item_name itemNameList := itemNameList();
104 t_supp_item_desc itemDescList := itemDescList();
105 t_tp_uom itemUomList := itemUomList();
106 t_tp_qty numberList := numberList();
107 t_tp_planned_order_qty numberList := numberList();
108 t_tp_released_qty numberList := numberList();
109 t_base_item_type numberList := numberList();
110 t_base_item_id numberList := numberList();
111 t_base_item_name itemNameList := itemNameList();
112 t_base_item_desc itemDescList := itemDescList();
113
114 l_list_of_item varchar2(1000);
115 l_item_list varchar2(1000);
116 l_item_id number;
117 l_item_notification varchar2(1000);
118
119 -- RP-CP Integration
120 l_plan_status number := 0; -- 1 is SAVED plan and 0 is UNSAVED plan
121
122 CURSOR planned_orders_c1 (
123 p_plan_id in number,
124 p_org_id in number,
125 p_sr_instance_id in number,
126 p_horizon_start in date,
127 p_horizon_end in date,
128 p_category_set_id in number,
129 p_category_name in varchar2,
130 p_planner_code in varchar2,
131 p_abc_class in varchar2,
132 p_item_id in number,
133 p_item_list in varchar2,
134 p_planning_gp in varchar2,
135 p_project_id in number,
136 p_task_id in number,
137 p_supplier_id in number,
138 p_supplier_site_id in number,
139 p_purchase_order in number,
140 p_requisition in number
141 ) IS
142
143
144 select sup.organization_id, --org id
145 sup.sr_instance_id, --sr_instance_id
146 sup.supplier_id,
147 sup.supplier_site_id,
148 i.base_item_id,
149 sup.inventory_item_id, --inventory item id
150 sup.order_type,
151 GREATEST ( SUM(decode(sup.firm_planned_type,
152 1,sup.firm_quantity,
153 sup.new_order_quantity)),
154 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
155 SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
156 nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
157 sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
158 nvl(sup.implemented_quantity,0))) planned_order_quantity,
159 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
160 i.item_name, --publisher item name
161 i.description, --publisher item desc
162 NULL, --sup.project_id, --project number
163 NULL, --sup.task_id, --task number
164 NULL, --sup.planning_group, --planning group
165 i.uom_code, --primary uom
166 i.planner_code, --planner code
167 mpb.bucket_type,
168 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
169 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
170 ), --key_date
171 decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
172 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
173 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
174 ) --reciept_date
175 from msc_plan_organizations_v ov,
176 msc_system_items i,
177 msc_supplies sup,
178 msc_plan_buckets mpb
179 where ov.plan_id = p_plan_id and
180 ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
181 ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
182 i.plan_id = ov.plan_id and
183 i.organization_id = ov.planned_organization and
184 i.sr_instance_id = ov.sr_instance_id and
185 NVL(i.base_item_id, i.inventory_item_id)
186 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
187 from msc_system_items i1
188 where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
189 and i1.organization_id = i.organization_id
190 and i1.plan_id = i.plan_id
191 and i1.sr_instance_id = i.sr_instance_id) and
192 i.item_name = nvl(p_item_list,i.item_name) and
193 NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
194 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
195 ((sup.order_type IN (PLANNED_ORDER,PLANNED_NEW_BUY_ORDER) and
196 sup.source_supplier_id is not null and
197 sup.source_supplier_site_id is not null and
198 sup.source_supplier_id = nvl(p_supplier_id, sup.source_supplier_id) and
199 sup.source_supplier_site_id = nvl(p_supplier_site_id, sup.source_supplier_site_id)
200 )
201 OR
202 (sup.order_type in (p_purchase_order, p_requisition) and
203 sup.supplier_id is not null and
204 sup.supplier_site_id is not null and
205 sup.supplier_id = nvl(p_supplier_id, sup.supplier_id) and
206 sup.supplier_site_id = nvl(p_supplier_site_id, sup.supplier_site_id)
207 )
208 ) and
209 sup.plan_id = i.plan_id and
210 sup.organization_id = i.organization_id and
211 sup.sr_instance_id = i.sr_instance_id and
212 sup.inventory_item_id = i.inventory_item_id and
213 nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
214 nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
215 nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
216 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
217 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))) --key_date between horizon
218 between NVL(p_horizon_start, sysdate - 36500)
219 and NVL(p_horizon_end, sysdate + 36500)
220 AND mpb.plan_id = sup.plan_id
221 and mpb.sr_instance_id = sup.sr_instance_id
222 and mpb.curr_flag = 1
223 and nvl(sup.firm_date,sup.new_schedule_date)
224 between mpb.bkt_start_date and mpb.bkt_end_date
225 GROUP BY sup.organization_id, --org id
226 sup.sr_instance_id, --sr_instance_id
227 sup.supplier_id,
228 sup.supplier_site_id,
229 i.base_item_id,
230 sup.inventory_item_id, --inventory item id
231 sup.order_type,
232 i.item_name, --publisher item name
233 i.description, --publisher item desc
234 i.uom_code, --primary uom
235 i.planner_code, --planner code
236 mpb.bucket_type,
237 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
238 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
239 ), --key_date
240 decode(sup.firm_date, null, sup.new_ship_date , null) --ship_date
241
242 UNION --get modelled suppleirs
243
244 select sup.organization_id, --org id
245 sup.sr_instance_id, --sr_instance_id
246 t1.modeled_supplier_id,
247 t1.modeled_supplier_site_id,
248 i.base_item_id,
249 sup.inventory_item_id, --inventory item id
250 sup.order_type,
251 GREATEST ( SUM(decode(sup.firm_planned_type,
252 1,sup.firm_quantity,
253 sup.new_order_quantity)),
254 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
255 SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
256 nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
257 sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
258 nvl(sup.implemented_quantity,0))) planned_order_quantity,
259 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
260 i.item_name, --publisher item name
261 i.description, --publisher item desc
262 NULL, --sup.project_id, --project number
263 NULL, --sup.task_id, --task number
264 NULL, --sup.planning_group, --planning group
265 i.uom_code, --primary uom
266 i.planner_code, --planner code
267 mpb.bucket_type,
268 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
269 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
270 ), --key_date
271 decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
272 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
273 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
274 ) --reciept_date
275 from msc_plan_organizations_v ov,
276 msc_system_items i,
277 msc_supplies sup,
278 msc_trading_partners t1,
279 msc_plan_buckets mpb
280 where ov.plan_id = p_plan_id and
281 ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
282 ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
283 i.plan_id = ov.plan_id and
284 i.organization_id = ov.planned_organization and
285 i.sr_instance_id = ov.sr_instance_id and
286 NVL(i.base_item_id, i.inventory_item_id)
287 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
288 from msc_system_items i1
289 where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
290 and i1.organization_id = i.organization_id
291 and i1.plan_id = i.plan_id
292 and i1.sr_instance_id = i.sr_instance_id) and
293 i.item_name = nvl(p_item_list,i.item_name) and
294 NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
295 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
296 -- bug#6893383 include planned_external_repair_order, external_repair_order
297 -- used by SPP plan only when supp is modelled as org
298 ((sup.order_type IN (PLANNED_ORDER,EXPECTED_INBOUND_SHIPMENT,
299 PLANNED_EXTERNAL_REPAIR_ORDER, l_external_repair_order) and
300 /*order type EXPECTED_INBOUND_SHIPMENT is for DRP Plan Bug: 3951295*/
301 --sup.source_supplier_id is null and
302 -- ignore above two lines, no effect on commenting. Why-external_repair_order populate both supplier_id and source_org_id
303 --sup.source_supplier_site_id is null and
304 sup.source_organization_id is not null and
305 sup.source_sr_instance_id is not null and
306 sup.source_organization_id <> sup.organization_id and
307 t1.sr_tp_id = sup.source_organization_id and
308 t1.sr_instance_id = sup.source_sr_instance_id and
309 t1.partner_type = 3
310 )
311 OR
312 (sup.order_type in (p_purchase_order, p_requisition) and
313 sup.source_supplier_id is null and
314 sup.source_supplier_site_id is null and
315 sup.source_organization_id is not null and -- bug#7446024
316 sup.source_sr_instance_id is not null and
317 t1.sr_tp_id = sup.source_organization_id and
318 t1.sr_instance_id = sup.source_sr_instance_id and
319 t1.partner_type = 3
320 )
321 ) and
322 t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
323 t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
324 t1.modeled_supplier_id is not null and
325 t1.modeled_supplier_site_id is not null and
326 sup.plan_id = i.plan_id and
327 sup.organization_id = i.organization_id and
328 sup.sr_instance_id = i.sr_instance_id and
329 sup.inventory_item_id = i.inventory_item_id and
330 nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
331 nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
332 nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
333 decode(sup.firm_date,null,nvl(sup.new_dock_date, msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
334 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0)))
335 between NVL(p_horizon_start, sysdate - 36500 )
336 and NVL(p_horizon_end, sysdate + 36500)
337 and mpb.plan_id = sup.plan_id
338 and mpb.sr_instance_id = sup.sr_instance_id
339 and mpb.curr_flag = 1
340 and nvl(sup.firm_date,sup.new_schedule_date)
341 between mpb.bkt_start_date and mpb.bkt_end_date
342 GROUP BY sup.organization_id,
343 sup.sr_instance_id,
344 t1.modeled_supplier_id,
345 t1.modeled_supplier_site_id,
346 i.base_item_id,
347 sup.inventory_item_id,
348 sup.order_type,
349 i.item_name,
350 i.description,
351 i.uom_code,
352 i.planner_code, --planner code
353 mpb.bucket_type,
354 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
355 msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
356 ), --key_date
357 decode(sup.firm_date, null, sup.new_ship_date , null) -- ship date
358 ORDER BY 1,2,3,4,5,6,7,11,12,16,18,19;
359
360 CURSOR defective_planned_orders_c1(
361 p_plan_id in number,
362 p_org_id in number,
363 p_sr_instance_id in number,
364 p_horizon_start in date,
365 p_horizon_end in date,
366 p_category_set_id in number,
367 p_category_name in varchar2,
368 p_planner_code in varchar2,
369 p_abc_class in varchar2,
370 p_item_id in number,
371 p_item_list in varchar2,
372 p_planning_gp in varchar2,
373 p_project_id in number,
374 p_task_id in number,
375 p_supplier_id in number,
376 p_supplier_site_id in number,
377 p_purchase_order in number,
378 p_requisition in number
379 ) IS
380 select sup.source_organization_id, --org id
381 sup.source_sr_instance_id, --sr_instance_id
382 t1.modeled_supplier_id,
383 t1.modeled_supplier_site_id,
384 i.base_item_id,
385 sup.inventory_item_id, --inventory item id
386 decode (sup.order_type,
387 EXPECTED_INBOUND_SHIPMENT, PLANNED_TRANSFER_DEF,
388 INTRANSIT_SHIPMENT, INTRANSIT_SHIPMENT_DEF,
389 INTRANSIT_RECEIPT , INTRANSIT_RECEIPT_DEF
390 ) order_type, --internal order_types local to this package
391 GREATEST ( SUM(decode(sup.firm_planned_type,
392 1,sup.firm_quantity,
393 sup.new_order_quantity)),
394 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
395 SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
396 nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
397 sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
398 nvl(sup.implemented_quantity,0))) planned_order_quantity,
399 SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
400 i.item_name, --publisher item name
401 i.description, --publisher item desc
402 NULL, --sup.project_id, --project number
403 NULL, --sup.task_id, --task number
404 NULL, --sup.planning_group, --planning group
405 i.uom_code, --primary uom
406 NULL, --i.planner_code, --planner code
407 mpb.bucket_type,
408 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
409 msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
410 ), --key_date
411 decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
412 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
413 msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
414 ), --reciept_date
415 -- order_number => order number for intransit shipment, NULL for Expected inbound shipment
416 sup.order_number order_number, -- use only for intransit_shipment_def and intransit_receipt_def
417 NULL line_number -- dummy type not to be used
418 from msc_plan_organizations_v ov,
419 msc_system_items i,
420 msc_supplies sup,
421 msc_trading_partners t1,
422 msc_plan_buckets mpb
423 where ov.plan_id = p_plan_id and
424 ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
425 ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
426 ov.plan_id = sup.plan_id and
427 ov.planned_organization = sup.source_organization_id and
428 ov.sr_instance_id = sup.source_sr_instance_id and
429 /* Check for defective item type*/
430 sup.item_type_value = 2 and -- for bad goods only
431 NVL(i.base_item_id, i.inventory_item_id)
432 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
433 from msc_system_items i1
434 where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
435 and i1.organization_id = i.organization_id
436 and i1.plan_id = i.plan_id
437 and i1.sr_instance_id = i.sr_instance_id) and
438 i.item_name = nvl(p_item_list,i.item_name) and
439 NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
440 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
441 (
442 -- Planned Transfer : Planned Transfer created at supplier modelled org for defective warehouse
443 sup.order_type in (EXPECTED_INBOUND_SHIPMENT, INTRANSIT_SHIPMENT, INTRANSIT_RECEIPT ) and
444 sup.source_supplier_id is null and
445 sup.source_supplier_site_id is null and
446 sup.source_organization_id is not null and
447 sup.source_sr_instance_id is not null and
448 sup.source_organization_id <> sup.organization_id and
449 t1.sr_tp_id = sup.organization_id and
450 t1.sr_instance_id = sup.sr_instance_id and
451 t1.partner_type = 3 and
452 t1.modeled_supplier_id is not null and
453 t1.modeled_supplier_site_id is not null
454 ) and
455 t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
456 t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
457 t1.modeled_supplier_id is not null and
458 t1.modeled_supplier_site_id is not null and
459 sup.plan_id = i.plan_id and
460 sup.source_organization_id = i.organization_id and
461 sup.source_sr_instance_id = i.sr_instance_id and
462 sup.inventory_item_id = i.inventory_item_id and
463 nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
464 nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
465 nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
466 decode(sup.firm_date,null,nvl(sup.new_dock_date, msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
467 msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0)))
468 between NVL(p_horizon_start, sysdate - 36500 )
469 and NVL(p_horizon_end, sysdate + 36500)
470 and mpb.plan_id = sup.plan_id
471 and mpb.sr_instance_id = sup.source_sr_instance_id
472 and mpb.curr_flag = 1
473 and nvl(sup.firm_date,sup.new_schedule_date)
474 between mpb.bkt_start_date and mpb.bkt_end_date
475 GROUP BY sup.source_organization_id, --org id
476 sup.source_sr_instance_id,
477 t1.modeled_supplier_id,
478 t1.modeled_supplier_site_id,
479 i.base_item_id,
480 sup.inventory_item_id,
481 decode (sup.order_type,EXPECTED_INBOUND_SHIPMENT, PLANNED_TRANSFER_DEF,
482 INTRANSIT_SHIPMENT, INTRANSIT_SHIPMENT_DEF,
483 INTRANSIT_RECEIPT , INTRANSIT_RECEIPT_DEF ),
484 i.item_name,
485 i.description,
486 i.uom_code,
487 NULL, --i.planner_code, --planner code
488 mpb.bucket_type,
489 decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
490 msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
491 ), --key_date
492 decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
493 sup.order_number
494 UNION
495 /* dmd_satisfied_date NOT NULL -> ISO that are unshipped will have dmd_satisfie_date ,
496 NULL -> Parially shipped or shipped and received ISO */
497 select dem.organization_id, --org id
498 dem.sr_instance_id, --sr_instance_id
499 t1.modeled_supplier_id,
500 t1.modeled_supplier_site_id,
501 i.base_item_id,
502 dem.inventory_item_id, --inventory item id
503 decode (dem.origination_type,
504 SALES_ORDER ,ISO_DEF
505 ) order_type, --internal order_types local to this package
506 SUM(NVL(dem.old_demand_quantity,0)) quantity, --quantity
507 0 planned_order_quantity, -- planned_order_quantity
508 0 released_quantity, -- released_quantity
509 i.item_name, --publisher item name
510 i.description, --publisher item desc
511 NULL, --project number
512 NULL, --task number
513 NULL, --planning group
514 i.uom_code, --primary uom
515 NULL, --i.planner_code, --planner code
516 mpb.bucket_type, -- bucket_type
517 dem.schedule_ship_date, --key_date
518 dem.schedule_ship_date, --ship_date
519 dem.schedule_arrival_date, --reciept_date
520 -- Order_number => <order_number>.Order Only.ORDER ENTRY(<line_number>)
521 decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--<order_number>
522 TRIM(decode(instr(dem.order_number,'(',-1,1),0,null,substr(dem.order_number,instr(dem.order_number,'(',-1,1) + 1,instr(dem.order_number,')',-1,1) - instr(dem.order_number,'(',-1,1) -1 ))) --<line_number>
523 from msc_plan_organizations_v ov,
524 msc_system_items i,
525 msc_demands dem,
526 msc_trading_partners t1,
527 msc_plan_buckets mpb
528 where ov.plan_id = p_plan_id and
529 ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
530 ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
531 ov.plan_id = dem.plan_id and
532 ov.planned_organization = dem.organization_id and
533 ov.sr_instance_id = dem.sr_instance_id and
534 ov.plan_id = i.plan_id and
535 ov.planned_organization = i.organization_id and
536 ov.sr_instance_id = i.sr_instance_id and
537 /* Check for defective item type*/
538 dem.item_type_value = 2 and -- for bad goods only
539 NVL(i.base_item_id, i.inventory_item_id)
540 IN (select nvl(i1.base_item_id,i1.inventory_item_id)
541 from msc_system_items i1
542 where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
543 and i1.organization_id = i.organization_id
544 and i1.plan_id = i.plan_id
545 and i1.sr_instance_id = i.sr_instance_id) and
546 i.item_name = nvl(p_item_list,i.item_name) and
547 NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
548 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
549 (
550 dem.origination_type in (SALES_ORDER) and
551 dem.disposition_id is not null and -- verify if SO is internal (ISO)
552 --dem.customer_id is null and
553 --dem.customer_site_id is null and
554 dem.source_organization_id is not null and -- supplier_modeled org
555 dem.source_org_instance_id is not null and
556 dem.source_organization_id <> dem.organization_id and
557 t1.sr_tp_id = dem.source_organization_id and
558 t1.sr_instance_id = dem.source_org_instance_id and
559 t1.partner_type = 3 and
560 t1.modeled_supplier_id is not null and
561 t1.modeled_supplier_site_id is not null
562 ) and
563 t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
564 t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
565 dem.plan_id = i.plan_id and
566 dem.organization_id = i.organization_id and
567 dem.sr_instance_id = i.sr_instance_id and
568 dem.inventory_item_id = i.inventory_item_id and
569 nvl(dem.planning_group, '-99') = nvl(p_planning_gp, nvl(dem.planning_group, '-99')) and
570 nvl(dem.project_id,-99) = nvl(p_project_id, nvl(dem.project_id,-99)) and
571 nvl(dem.task_id, -99) = nvl(p_task_id, nvl(dem.task_id, -99)) and
572 dem.dmd_satisfied_date between nvl(p_horizon_start, dem.dmd_satisfied_date)
573 and nvl(p_horizon_end, dem.dmd_satisfied_date)
574 and exists ( select 'PICK_ONLY_UNSHIPPED_ISO'
575 from msc_sales_orders mso
576 where mso.demand_id = dem.demand_id
577 and mso.sr_instance_id = dem.sr_instance_id
578 and (mso.primary_uom_quantity - mso.completed_quantity) > 0 )
579 and mpb.plan_id = dem.plan_id
580 and mpb.sr_instance_id = dem.sr_instance_id
581 and mpb.curr_flag = 1
582 and dem.dmd_satisfied_date between mpb.bkt_start_date and mpb.bkt_end_date
583 GROUP BY dem.organization_id,
584 dem.sr_instance_id,
585 t1.modeled_supplier_id,
586 t1.modeled_supplier_site_id,
587 i.base_item_id,
588 dem.inventory_item_id,
589 decode (dem.origination_type,SALES_ORDER , ISO_DEF),
590 i.item_name,
591 i.description,
592 i.uom_code,
593 NULL, --i.planner_code, --planner code
594 mpb.bucket_type,
595 dem.schedule_ship_date, --ship date or key date
596 dem.schedule_arrival_date, --reciept_date
597 decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--<order_number>
598 TRIM(decode(instr(dem.order_number,'(',-1,1),0,null,substr(dem.order_number,instr(dem.order_number,'(',-1,1) + 1,instr(dem.order_number,')',-1,1) - instr(dem.order_number,'(',-1,1) -1 ))) --<line_number>
599 ORDER BY 1,2,3,4,5,6,7,11,12,16,18,19;
600
601
602
603 BEGIN
604
605 if fnd_global.conc_request_id > 0 then
606 select
607 fnd_global.user_id,
608 fnd_global.user_name --,
609 --fnd_global.resp_name,
610 --fnd_global.application_name
611 into l_user_id,
612 l_user_name --,
613 --l_resp_name,
614 --l_application_name
615 from dual;
616 end if;
617
618 if l_user_id is null then
619 l_language_code := 'US';
620 else
621 l_language := fnd_preference.get(UPPER(l_user_name),'WF','LANGUAGE');
622 IF l_language IS NOT NULL THEN
623 SELECT language_code
624 INTO l_language_code
625 FROM fnd_languages
626 WHERE nls_language = l_language;
627 ELSE
628 l_language_code := 'US';
629 END IF;
630 end if;
631
632
633 log_message('At 1');
634
635 -- bug#6893383
636
637 select compile_designator,curr_plan_type,saved_flag
638 into p_designator,l_plan_type,l_plan_status
639 from msc_plans
640 where plan_id = p_plan_id;
641
642 log_message('Designator/Plan Type/Plan Status : ' || p_designator || '/' || l_plan_type || '/' || l_plan_status );
643
644 IF (l_plan_type > 100 ) THEN
645 log_message('Publishing data for RP plan : '||p_designator);
646
647 IF (nvl(l_plan_status, 0) <> 1) THEN
648 log_message(' ');
649 log_message('Selected RP plan is not Saved. Please Save the plan before launching Publish Order Forecast.');
650 log_message('Exiting..');
651 log_message(' ');
652
653 p_retcode := 1; --G_WARNING
654 return;
655 END IF;
656 END IF;
657
658 if p_org_code is not null then
659 p_inst_code := substr(p_org_code,1,instr(p_org_code,':')-1);
660 log_message('p_inst_code := ' || p_inst_code);
661 begin
662 select instance_id
663 into p_sr_instance_id
664 from msc_apps_instances
665 where instance_code = p_inst_code;
666 log_message('p_sr_instance_id := ' || p_sr_instance_id);
667
668 select sr_tp_id
669 into p_org_id
670 from msc_trading_partners
671 where organization_code = p_org_code and
672 sr_instance_id = p_sr_instance_id and
673 partner_type = 3 and
674 company_id is null;
675 log_message('p_org_id := ' || p_org_id);
676 exception
677 when others then
678 p_org_id := null;
679 p_sr_instance_id := null;
680 end;
681 else
682 p_org_id := null;
683 p_sr_instance_id := null;
684 end if;
685
686 if p_auto_version = 1 then
687 l_version := nvl(p_version,0) + 1;
688 else
689 l_version := null;
690 end if;
691
692 l_log_message := get_message('MSC','MSC_X_PUB_OF',l_language_code) || ' ' || fnd_global.local_chr(10) ||
693 get_message('MSC','MSC_X_PUB_PLAN',l_language_code) || ': ' || p_designator || fnd_global.local_chr(10);
694
695 IF p_org_code IS NOT NULL THEN
696 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ORG',l_language_code) || ': ' || p_org_code || fnd_global.local_chr(10);
697 END IF;
698
699 IF p_item_id IS NOT NULL THEN
700 SELECT item_name
701 INTO l_item_name
702 FROM msc_items
703 WHERE inventory_item_id = p_item_id;
704 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ITEM',l_language_code) || ': ' || l_item_name || fnd_global.local_chr(10);
705 END IF;
706
707 IF p_supplier_id IS NOT NULL THEN
708 SELECT partner_name
709 INTO l_supp_name
710 FROM msc_trading_partners
711 WHERE partner_id = p_supplier_id;
712 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_SUPPLIER',l_language_code) || ': ' || l_supp_name || fnd_global.local_chr(10);
713 END IF;
714
715 IF p_supplier_site_id IS NOT NULL THEN
716 SELECT tp_site_code
717 INTO l_supp_site
718 FROM msc_trading_partner_sites
719 WHERE partner_id = p_supplier_id
720 AND partner_site_id = p_supplier_site_id;
721 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_SUPP_SITE',l_language_code) || ': ' || l_supp_site || fnd_global.local_chr(10);
722 END IF;
723
724 IF p_planner_code IS NOT NULL THEN
725 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PLANNER',l_language_code) || ': ' || p_planner_code || fnd_global.local_chr(10);
726 END IF;
727
728 IF p_planning_gp IS NOT NULL THEN
729 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);
730 END IF;
731
732 IF p_project_id IS NOT NULL THEN
733 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);
734 END IF;
735
736 IF p_task_id IS NOT NULL THEN
737 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);
738 END IF;
739
740 IF p_abc_class IS NOT NULL THEN
741 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);
742 END IF;
743 log_message(l_log_message);
744
745 l_log_message := get_message('MSC','MSC_X_PUB_DELETE_OF',l_language_code) || fnd_global.local_chr(10);
746 log_message(l_log_message);
747
748 -- Option 1 -> YES , 2-> NO , bug#68938833
749 log_message('Publish Defective Outbound Shipment : ' || p_publish_dos);
750
751 IF p_purchase_order = 1 then
752 l_purchase_order := 1;
753 l_external_repair_order := EXTERNAL_REPAIR_ORDER ; --bug#6893383
754 ELSE
755 l_purchase_order := null;
756 l_external_repair_order := null; --bug#6893383
757 END IF;
758 IF p_requisition = 1 then
759 l_requisition := 2;
760 ELSE
761 l_requisition := null;
762 END IF;
763
764 --------------------------------------------------------------------------
765 -- set the standard date as canonical date
766 --------------------------------------------------------------------------
767 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start);
768 l_horizon_end := fnd_date.canonical_to_date(p_horizon_end);
769
770 ----------------------------------------------------------------------------
771 -- get the input item list and parse it.
772 ----------------------------------------------------------------------------
773 l_list_of_item := p_item_list;
774
775
776 WHILE l_list_of_item is not null
777 LOOP
778
779 IF (instr(l_list_of_item,',') <> 0 ) THEN
780 l_item_list :=
781 ltrim(rtrim(substr(l_list_of_item,1,instr(l_list_of_item,',')-1),' '),' ');
782
783 l_list_of_item := substr(l_list_of_item,instr(l_list_of_item,',')+1,length(l_list_of_item)) ;
784 ELSIF (instr(l_list_of_item,',') = 0 ) THEN
785 l_item_list := ltrim(rtrim(substr(l_list_of_item,1,length (l_list_of_item)),' '),' ') ;
786 l_list_of_item := null;
787 END IF;
788
789 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item: ' || l_item_list);
790
791 IF (l_item_list is not null) THEN
792 begin
793 select inventory_item_id
794 into l_item_id
795 from msc_items
796 where item_name = l_item_list;
797 exception
798 when no_data_found then
799 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item not found: ' || l_item_list);
800 l_item_id := null;
801 when others then
802 l_item_id := null;
803 end;
804 END IF;
805 IF (l_item_id is not null) THEN
806 log_message('At 2');
807
808 delete_old_forecast(
809 p_plan_id,
810 p_org_id,
811 p_sr_instance_id,
812 p_planner_code,
813 p_abc_class,
814 l_item_id,
815 p_planning_gp,
816 p_project_id,
817 p_task_id,
818 p_supplier_id,
819 p_supplier_site_id,
820 l_horizon_start,
821 l_horizon_end,
822 p_overwrite
823 );
824
825 log_message('At 3');
826 OPEN planned_orders_c1 (
827 p_plan_id
828 ,p_org_id
829 ,p_sr_instance_id
830 ,l_horizon_start
831 ,l_horizon_end
832 ,p_category_set_id
833 ,p_category_name
834 ,p_planner_code
835 ,p_abc_class
836 ,p_item_id
837 ,l_item_list
838 ,p_planning_gp
839 ,p_project_id
840 ,p_task_id
841 ,p_supplier_id
842 ,p_supplier_site_id
843 ,l_purchase_order
844 ,l_requisition
845 );
846
847 FETCH planned_orders_c1
848 BULK COLLECT INTO
849 t_org_id,
850 t_sr_instance_id,
851 t_source_supp_id,
852 t_source_supp_site_id,
853 t_base_item_id,
854 t_item_id,
855 t_order_type,
856 t_qty,
857 t_planned_order_qty,
858 t_released_qty,
859 t_owner_item_name,
860 t_owner_item_desc,
861 t_proj_number,
862 t_task_number,
863 t_planning_gp,
864 t_uom_code,
865 t_planner_code,
866 t_bucket_type,
867 t_key_date,
868 t_ship_date,
869 t_receipt_date;
870
871 CLOSE planned_orders_c1;
872
873 log_message('At 4');
874
875 IF t_org_id IS NOT NULL AND t_org_id.COUNT > 0 THEN
876 log_message ('Records fetched by cursor := ' || t_org_id.COUNT);
877 log_message('At 5');
878
879 ------------------------------------------------------------------------
880 -- the following reintialize is required
881 -- if there are multiple items, the get_optional_info will accumulate the
882 -- output value from the previous item. Therefore, individual item
883 -- should has a fresh output data
884 -----------------------------------------------------------------------
885 t_pub := companyNameList();
886 t_pub_id := numberList();
887 t_pub_site := companySiteList();
888 t_pub_site_id := numberList();
889 t_supp := companyNameList();
890 t_supp_id := numberList();
891 t_supp_site := companySiteList();
892 t_supp_site_id := numberList();
893 t_master_item_name := itemNameList();
894 t_master_item_desc := itemDescList();
895 t_supp_item_name := itemNameList();
896 t_supp_item_desc := itemDescList();
897 t_tp_uom := itemUomList();
898 t_tp_qty := numberList();
899 t_tp_planned_order_qty := numberList();
900 t_tp_released_qty := numberList();
901
902 get_optional_info(
903 t_item_id
904 ,t_org_id
905 ,t_sr_instance_id
906 ,t_source_supp_id
907 ,t_source_supp_site_id
908 ,t_uom_code
909 ,t_qty
910 ,t_planned_order_qty
911 ,t_released_qty
912 ,t_base_item_id
913 ,t_base_item_name
914 ,t_base_item_desc
915 ,t_master_item_name
916 ,t_master_item_desc
917 ,t_supp_item_name
918 ,t_supp_item_desc
919 ,t_tp_uom
920 ,t_tp_qty
921 ,t_tp_planned_order_qty
922 ,t_tp_released_qty
923 ,t_ship_date
924 ,t_receipt_date
925 ,t_pub_id
926 ,t_pub
927 ,t_pub_site_id
928 ,t_pub_site
929 ,t_supp_id
930 ,t_supp
931 ,t_supp_site_id
932 ,t_supp_site
933 );
934
935
936
937 log_message('At 6');
938
939 insert_into_sup_dem(
940 t_pub
941 ,t_pub_id
942 ,t_pub_site
943 ,t_pub_site_id
944 ,t_item_id
945 ,t_order_type
946 ,t_qty
947 ,t_planned_order_qty
948 ,t_released_qty
949 ,t_supp
950 ,t_supp_id
951 ,t_supp_site
952 ,t_supp_site_id
953 ,t_owner_item_name
954 ,t_owner_item_desc
955 ,t_base_item_id
956 ,t_base_item_name
957 ,t_base_item_desc
958 ,t_proj_number
959 ,t_task_number
960 ,t_planning_gp
961 ,t_uom_code
962 ,t_planner_code
963 ,t_bucket_type
964 ,t_key_date
965 ,t_ship_date
966 ,t_receipt_date
967 ,t_master_item_name
968 ,t_master_item_desc
969 ,t_supp_item_name
970 ,t_supp_item_desc
971 ,t_tp_uom
972 ,t_tp_qty
973 ,t_tp_planned_order_qty
974 ,t_tp_released_qty
975 ,l_version
976 ,p_designator
977 ,l_user_id
978 ,l_language_code
979 );
980 ELSE
981 l_log_message := 'Number of records published: ' || 0 || '.';
982 log_message(l_log_message);
983 l_cursor1 := 0;
984 END IF;
985
986
987 END IF;
988 /*--------------------------------------------------------------------------------
989 Launch the notification here (where an item list is provided
990 --------------------------------------------------------------------------------*/
991
992 msc_x_wfnotify_pkg.Launch_Publish_WF (p_errbuf,
993 p_retcode,
994 p_designator,
995 l_version,
996 l_horizon_start,
997 l_horizon_end,
998 p_plan_id,
999 p_sr_instance_id,
1000 p_org_id,
1001 l_item_id,
1002 p_supplier_id,
1003 p_supplier_site_id,
1004 null, ---p_customer_id,
1005 null, ---p_customer_site_id,
1006 p_planner_code,
1007 p_abc_class,
1008 p_planning_gp,
1009 p_project_id,
1010 p_task_id ,
1011 ORDER_FORECAST);
1012 END LOOP;
1013 IF (p_item_list is null) THEN
1014 log_message('At 2');
1015
1016 delete_old_forecast(
1017 p_plan_id,
1018 p_org_id,
1019 p_sr_instance_id,
1020 p_planner_code,
1021 p_abc_class,
1022 p_item_id,
1023 p_planning_gp,
1024 p_project_id,
1025 p_task_id,
1026 p_supplier_id,
1027 p_supplier_site_id,
1028 l_horizon_start,
1029 l_horizon_end,
1030 p_overwrite
1031 );
1032
1033 log_message('At 3');
1034 log_message('p_plan_id : ' || p_plan_id);
1035 log_message('p_org_id : ' || p_org_id);
1036 log_message('p_sr_instance_id : ' || p_sr_instance_id);
1037 log_message('p_horizon_start : ' || p_horizon_start);
1038 log_message('p_horizon_end : ' || p_horizon_end);
1039 log_message('p_category_set_id : ' || p_category_set_id);
1040 log_message('p_category_name : ' || p_category_name);
1041 log_message('p_planner_code : ' || p_planner_code);
1042 log_message('p_abc_class : ' || p_abc_class);
1043 log_message('p_item_id : ' || p_item_id);
1044 log_message('l_item_list : ' || l_item_list);
1045 log_message('p_planning_gp : ' || p_planning_gp);
1046 log_message('p_project_id : ' || p_project_id);
1047 log_message('p_task_id : ' || p_task_id);
1048 log_message('p_supplier_id : ' || p_supplier_id);
1049 log_message('p_supplier_site_id : ' || p_supplier_site_id);
1050 log_message('l_purchase_order : ' || l_purchase_order);
1051 log_message('l_requisition : ' || l_requisition);
1052 -- External Repair Order is like converting PO from Planned Order in ASCP plan,
1053 -- Include only when user specify include purchase order - YES in conc program
1054 log_message('l_external_repair_order : ' || l_external_repair_order); --bug#6893383
1055 log_message('p_publish_dos : ' || p_publish_dos ); --bug#6893383
1056
1057 OPEN planned_orders_c1 (
1058 p_plan_id
1059 ,p_org_id
1060 ,p_sr_instance_id
1061 ,l_horizon_start
1062 ,l_horizon_end
1063 ,p_category_set_id
1064 ,p_category_name
1065 ,p_planner_code
1066 ,p_abc_class
1067 ,p_item_id
1068 ,l_item_list
1069 ,p_planning_gp
1070 ,p_project_id
1071 ,p_task_id
1072 ,p_supplier_id
1073 ,p_supplier_site_id
1074 ,l_purchase_order
1075 ,l_requisition
1076 );
1077
1078 FETCH planned_orders_c1
1079 BULK COLLECT INTO
1080 t_org_id,
1081 t_sr_instance_id,
1082 t_source_supp_id,
1083 t_source_supp_site_id,
1084 t_base_item_id,
1085 t_item_id,
1086 t_order_type,
1087 t_qty,
1088 t_planned_order_qty,
1089 t_released_qty,
1090 t_owner_item_name,
1091 t_owner_item_desc,
1092 t_proj_number,
1093 t_task_number,
1094 t_planning_gp,
1095 t_uom_code,
1096 t_planner_code,
1097 t_bucket_type,
1098 t_key_date,
1099 t_ship_date,
1100 t_receipt_date;
1101
1102 CLOSE planned_orders_c1;
1103
1104 log_message('At 4');
1105
1106
1107 IF t_org_id IS NOT NULL AND t_org_id.COUNT > 0 THEN
1108 log_message ('Records fetched by cursor := ' || t_org_id.COUNT);
1109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records fetched by cursor := ' || t_org_id.COUNT);
1110 log_message('At 5');
1111
1112 get_optional_info(
1113 t_item_id
1114 ,t_org_id
1115 ,t_sr_instance_id
1116 ,t_source_supp_id
1117 ,t_source_supp_site_id
1118 ,t_uom_code
1119 ,t_qty
1120 ,t_planned_order_qty
1121 ,t_released_qty
1122 ,t_base_item_id
1123 ,t_base_item_name
1124 ,t_base_item_desc
1125 ,t_master_item_name
1126 ,t_master_item_desc
1127 ,t_supp_item_name
1128 ,t_supp_item_desc
1129 ,t_tp_uom
1130 ,t_tp_qty
1131 ,t_tp_planned_order_qty
1132 ,t_tp_released_qty
1133 ,t_ship_date
1134 ,t_receipt_date
1135 ,t_pub_id
1136 ,t_pub
1137 ,t_pub_site_id
1138 ,t_pub_site
1139 ,t_supp_id
1140 ,t_supp
1141 ,t_supp_site_id
1142 ,t_supp_site
1143 );
1144
1145
1146
1147 log_message('At 6');
1148 insert_into_sup_dem(
1149 t_pub
1150 ,t_pub_id
1151 ,t_pub_site
1152 ,t_pub_site_id
1153 ,t_item_id
1154 ,t_order_type
1155 ,t_qty
1156 ,t_planned_order_qty
1157 ,t_released_qty
1158 ,t_supp
1159 ,t_supp_id
1160 ,t_supp_site
1161 ,t_supp_site_id
1162 ,t_owner_item_name
1163 ,t_owner_item_desc
1164 ,t_base_item_id
1165 ,t_base_item_name
1166 ,t_base_item_desc
1167 ,t_proj_number
1168 ,t_task_number
1169 ,t_planning_gp
1170 ,t_uom_code
1171 ,t_planner_code
1172 ,t_bucket_type
1173 ,t_key_date
1174 ,t_ship_date
1175 ,t_receipt_date
1176 ,t_master_item_name
1177 ,t_master_item_desc
1178 ,t_supp_item_name
1179 ,t_supp_item_desc
1180 ,t_tp_uom
1181 ,t_tp_qty
1182 ,t_tp_planned_order_qty
1183 ,t_tp_released_qty
1184 ,l_version
1185 ,p_designator
1186 ,l_user_id
1187 ,l_language_code
1188 );
1189 ELSE
1190 l_log_message := 'Number of records published: ' || 0 || '.';
1191 log_message(l_log_message);
1192 l_cursor1 := 0;
1193 END IF;
1194
1195 /*--------------------------------------------------------------------------------
1196 Launch the notification here
1197 --------------------------------------------------------------------------------*/
1198
1199 msc_x_wfnotify_pkg.Launch_Publish_WF (p_errbuf,
1200 p_retcode,
1201 p_designator,
1202 l_version,
1203 l_horizon_start,
1204 l_horizon_end,
1205 p_plan_id,
1206 p_sr_instance_id,
1207 p_org_id,
1208 p_item_id,
1209 p_supplier_id,
1210 p_supplier_site_id,
1211 null, ---p_customer_id,
1212 null, ---p_customer_site_id,
1213 p_planner_code,
1214 p_abc_class,
1215 p_planning_gp,
1216 p_project_id,
1217 p_task_id ,
1218 ORDER_FORECAST);
1219
1220 -- SPP calculate Return Forecast, DOS and push it to msc_sup_dem_entries only when SPP plan is run
1221 IF l_plan_type = SPP_PLAN THEN
1222 OPEN defective_planned_orders_c1 (
1223 p_plan_id
1224 ,p_org_id
1225 ,p_sr_instance_id
1226 ,l_horizon_start
1227 ,l_horizon_end
1228 ,p_category_set_id
1229 ,p_category_name
1230 ,p_planner_code
1231 ,p_abc_class
1232 ,p_item_id
1233 ,l_item_list
1234 ,p_planning_gp
1235 ,p_project_id
1236 ,p_task_id
1237 ,p_supplier_id
1238 ,p_supplier_site_id
1239 ,l_purchase_order
1240 ,l_requisition
1241 );
1242
1243 FETCH defective_planned_orders_c1
1244 BULK COLLECT INTO
1245 t_org_id,
1246 t_sr_instance_id,
1247 t_source_supp_id,
1248 t_source_supp_site_id,
1249 t_base_item_id,
1250 t_item_id,
1251 t_order_type,
1252 t_qty,
1253 t_planned_order_qty,
1254 t_released_qty,
1255 t_owner_item_name,
1256 t_owner_item_desc,
1257 t_proj_number,
1258 t_task_number,
1259 t_planning_gp,
1260 t_uom_code,
1261 t_planner_code,
1262 t_bucket_type,
1263 t_key_date,
1264 t_ship_date,
1265 t_receipt_date,
1266 t_order_num, -- bug#7310179
1267 t_line_num; -- bug#7310179
1268
1269 CLOSE defective_planned_orders_c1;
1270
1271 log_message('At 7');
1272
1273
1274 IF t_org_id IS NOT NULL AND t_org_id.COUNT > 0 THEN
1275 log_message ('Defective Planned Order fetched by cursor := ' || t_org_id.COUNT);
1276 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Defective Planned Order fetched by cursor := ' || t_org_id.COUNT);
1277 log_message('At 8');
1278
1279 t_pub := companyNameList();
1280 t_pub_id := numberList();
1281 t_pub_site := companySiteList();
1282 t_pub_site_id := numberList();
1283 t_supp := companyNameList();
1284 t_supp_id := numberList();
1285 t_supp_site := companySiteList();
1286 t_supp_site_id := numberList();
1287 t_master_item_name := itemNameList();
1288 t_master_item_desc := itemDescList();
1289 t_base_item_name := itemNameList();
1290 t_base_item_desc := itemDescList();
1291 t_supp_item_name := itemNameList();
1292 t_supp_item_desc := itemDescList();
1293 t_tp_uom := itemUomList();
1294 t_tp_qty := numberList();
1295 t_tp_planned_order_qty := numberList();
1296 t_tp_released_qty := numberList();
1297
1298 get_optional_info(
1299 t_item_id
1300 ,t_org_id
1301 ,t_sr_instance_id
1302 ,t_source_supp_id
1303 ,t_source_supp_site_id
1304 ,t_uom_code
1305 ,t_qty
1306 ,t_planned_order_qty
1307 ,t_released_qty
1308 ,t_base_item_id
1309 ,t_base_item_name
1310 ,t_base_item_desc
1311 ,t_master_item_name
1312 ,t_master_item_desc
1313 ,t_supp_item_name
1314 ,t_supp_item_desc
1315 ,t_tp_uom
1316 ,t_tp_qty
1317 ,t_tp_planned_order_qty
1318 ,t_tp_released_qty
1319 ,t_ship_date
1320 ,t_receipt_date
1321 ,t_pub_id
1322 ,t_pub
1323 ,t_pub_site_id
1324 ,t_pub_site
1325 ,t_supp_id
1326 ,t_supp
1327 ,t_supp_site_id
1328 ,t_supp_site
1329 );
1330
1331
1332 log_message('At 9');
1333 insert_into_sup_dem_rf_dos(
1334 t_pub
1335 ,t_pub_id
1336 ,t_pub_site
1337 ,t_pub_site_id
1338 ,t_item_id
1339 ,t_order_type
1340 ,t_qty
1341 ,t_planned_order_qty
1342 ,t_released_qty
1343 ,t_supp
1344 ,t_supp_id
1345 ,t_supp_site
1346 ,t_supp_site_id
1347 ,t_owner_item_name
1348 ,t_owner_item_desc
1349 ,t_base_item_id
1350 ,t_base_item_name
1351 ,t_base_item_desc
1352 ,t_proj_number
1353 ,t_task_number
1354 ,t_planning_gp
1355 ,t_uom_code
1356 ,t_planner_code
1357 ,t_bucket_type
1358 ,t_key_date
1359 ,t_ship_date
1360 ,t_receipt_date
1361 ,t_order_num -- bug#7310179
1362 ,t_line_num -- bug#7310179
1363 ,t_master_item_name
1364 ,t_master_item_desc
1365 ,t_supp_item_name
1366 ,t_supp_item_desc
1367 ,t_tp_uom
1368 ,t_tp_qty
1369 ,t_tp_planned_order_qty
1370 ,t_tp_released_qty
1371 ,l_version
1372 ,p_designator
1373 ,l_user_id
1374 ,l_language_code
1375 ,p_publish_dos --bug#6893383
1376 );
1377
1378 ELSE
1379 l_log_message := 'Returns Forecast records published: ' || 0 || '.';
1380 log_message(l_log_message);
1381 -- l_cursor1 := 0;
1382 END IF;
1383
1384 END IF; --IF l_plan_type = SPP_PLAN
1385
1386
1387 END IF;
1388
1389
1390 commit;
1391
1392
1393 IF l_cursor1 = 0 THEN
1394 l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',l_language_code) || ': ' || 0 || '.' || fnd_global.local_chr(10);
1395 log_message(l_log_message);
1396 END IF;
1397
1398 IF l_version IS NOT NULL THEN
1399 BEGIN
1400 SELECT 1 INTO l_records_exist
1401 FROM dual
1402 WHERE exists ( SELECT 1
1403 FROM msc_sup_dem_entries
1404 WHERE plan_id = -1
1405 AND publisher_order_type = 2
1406 AND designator = p_designator
1407 AND version = TO_CHAR(l_version));
1408 EXCEPTION
1409 WHEN OTHERS then
1410 l_records_exist := 0;
1411 END;
1412 IF l_records_exist = 1 then
1413 l_log_message := get_message('MSC','MSC_X_PUB_NEW_VERSION',l_language_code) || ' ' || l_version || '.' || fnd_global.local_chr(10);
1414 log_message(l_log_message);
1415
1416 --update version number in msc_plans
1417
1418 UPDATE msc_plans
1419 SET publish_fcst_version = l_version
1420 WHERE plan_id = p_plan_id;
1421
1422 END IF;
1423 END IF;
1424
1425
1426 /*--------------------------------------------------------------------------------
1427 auto launch netting engine
1428 ----------------------------------------------------------------------------------*/
1429 IF ( ( FND_PROFILE.VALUE('MSC_X_AUTO_SCEM_MODE') = 2
1430 OR FND_PROFILE.VALUE('MSC_X_AUTO_SCEM_MODE') = 3
1431 ) -- PUBLISH or ALL
1432 AND ( FND_PROFILE.VALUE('MSC_X_CONFIGURATION') = 2
1433 OR FND_PROFILE.VALUE('MSC_X_CONFIGURATION') = 3
1434 ) -- APS+CP or CP only
1435 ) THEN
1436 BEGIN
1437 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Launching SCEM engine');
1438 MSC_X_CP_FLOW.Start_SCEM_Engine_WF;
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_CP_FLOW.Start_SCEM_Engine_WF');
1442 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1443 END;
1444 END IF;
1445
1446 EXCEPTION
1447 when others then
1448 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in publish plan order proc: ' ||sqlerrm);
1449 END publish_plan_orders;
1450
1451
1452 PROCEDURE get_optional_info(
1453 t_item_id IN numberList,
1454 t_org_id IN numberList,
1455 t_sr_instance_id IN numberList,
1456 t_source_supp_id IN numberList,
1457 t_source_supp_site_id IN numberList,
1458 t_uom_code IN itemUomList,
1459 t_qty IN numberList,
1460 t_planned_order_qty IN numberList,
1461 t_released_qty IN numberList,
1462 t_base_item_id IN numberList,
1463 t_base_item_name IN OUT NOCOPY itemNameList,
1464 t_base_item_desc IN OUT NOCOPY itemDescList,
1465 t_master_item_name IN OUT NOCOPY itemNameList,
1466 t_master_item_desc IN OUT NOCOPY itemDescList,
1467 t_supp_item_name IN OUT NOCOPY itemNameList,
1468 t_supp_item_desc IN OUT NOCOPY itemDescList,
1469 t_tp_uom IN OUT NOCOPY itemUomList,
1470 t_tp_qty IN OUT NOCOPY numberList,
1471 t_tp_planned_order_qty IN OUT NOCOPY numberList,
1472 t_tp_released_qty IN OUT NOCOPY numberList,
1473 t_ship_date IN OUT NOCOPY dateList,
1474 t_receipt_date IN dateList,
1475 t_pub_id IN OUT NOCOPY numberList,
1476 t_pub IN OUT NOCOPY companyNameList,
1477 t_pub_site_id IN OUT NOCOPY numberList,
1478 t_pub_site IN OUT NOCOPY companySiteList,
1479 t_supp_id IN OUT NOCOPY numberList,
1480 t_supp IN OUT NOCOPY companyNameList,
1481 t_supp_site_id IN OUT NOCOPY numberList,
1482 t_supp_site IN OUT NOCOPY companySiteList
1483 ) IS
1484
1485 l_conversion_found boolean;
1486 l_conversion_rate number;
1487 l_lead_time number;
1488 l_using_org_id number;
1489
1490 CURSOR c_supplier_lead_time (
1491 p_item_id in number,
1492 p_org_id in number,
1493 p_inst_id in number,
1494 p_supp_id in number,
1495 p_supp_site_id in number
1496 ) IS
1497 select distinct mis.processing_lead_time,
1498 mis.using_organization_id
1499 from msc_item_suppliers mis,
1500 msc_trading_partner_maps m,
1501 msc_trading_partner_maps m2,
1502 msc_company_relationships r
1503 where mis.plan_id = -1 and
1504 mis.inventory_item_id = p_item_id and
1505 mis.organization_id = p_org_id and
1506 mis.sr_instance_id = p_inst_id and
1507 r.relationship_type = 2 and
1508 r.subject_id = 1 and
1509 r.object_id = p_supp_id and
1510 m.map_type = 1 and
1511 m.company_key = r.relationship_id and
1512 mis.supplier_id = m.tp_key and
1513 m2.map_type = 3 and
1514 m2.company_key = p_supp_site_id and
1515 nvl(mis.supplier_site_id, m2.tp_key) = m2.tp_key
1516 order by mis.using_organization_id desc;
1517
1518 BEGIN
1519 if t_item_id is not null and t_item_id.COUNT > 0 then
1520 log_message('In get_optional_info : ' || t_item_id.COUNT);
1521 for j in 1..t_item_id.COUNT loop
1522 --log_message('DEBUG : t_item_id(' || j ||')/t_org_id(' || j || ')/' ||
1523 -- 't_sr_instance_id(' || j ||')/t_source_supp_id('|| j || ')/' ||
1524 -- 't_source_supp_site_id(' || j ||')/t_qty(' || j || ')/' ||
1525 -- 't_base_item_id(' || j ||')/t_order_type(' || j || ') : ' ||
1526 -- t_item_id(j) || '/' || t_org_id(j) || '/' || t_sr_instance_id(j) || '/' ||t_source_supp_id(j))|| '/' ||
1527 -- t_source_supp_site_id(j) || '/' || t_qty(j) || '/' || t_base_item_id(j) || '/' ||t_order_type(j));
1528 t_pub_id.EXTEND;
1529 t_pub.EXTEND;
1530 t_pub_site.EXTEND;
1531 t_pub_site_id.EXTEND;
1532
1533 if (j = 1) or (t_org_id(j-1) <> t_org_id(j)) or (t_sr_instance_id(j-1) <> t_sr_instance_id(j)) then
1534
1535 select c.company_id,
1536 c.company_name,
1537 s.company_site_id,
1538 s.company_site_name
1539 into t_pub_id(j),
1540 t_pub(j),
1541 t_pub_site_id(j),
1542 t_pub_site(j)
1543 from msc_companies c,
1544 msc_company_sites s,
1545 msc_trading_partner_maps m,
1546 msc_trading_partners t
1547 where t.sr_tp_id = t_org_id(j) and
1548 t.sr_instance_id = t_sr_instance_id(j) and
1549 t.partner_type = 3 and
1550 m.tp_key = t.partner_id and
1551 m.map_type = 2 and
1552 s.company_site_id = m.company_key and
1553 c.company_id = s.company_id;
1554
1555 else
1556 t_pub_id(j) := t_pub_id(j-1);
1557 t_pub(j) := t_pub(j-1);
1558 t_pub_site_id(j) := t_pub_site_id(j-1);
1559 t_pub_site(j) := t_pub_site(j-1);
1560 end if;
1561
1562 --log_message('DEBUG : t_pub(' || j ||')/t_pub_site('||j|| ') : ' || t_pub(j) || '/' || t_pub_site(j));
1563
1564 t_supp_id.EXTEND;
1565 t_supp.EXTEND;
1566 t_supp_site.EXTEND;
1567 t_supp_site_id.EXTEND;
1568
1569 if (j = 1) or (t_source_supp_id(j-1) <> t_source_supp_id(j)) then
1570 BEGIN
1571 select distinct c.company_id,
1572 c.company_name
1573 into t_supp_id(j),
1574 t_supp(j)
1575 from msc_companies c,
1576 msc_company_sites s,
1577 msc_trading_partner_maps m,
1578 msc_company_relationships r
1579 where m.tp_key = t_source_supp_id(j) and
1580 m.map_type = 1 and
1581 r.relationship_id = m.company_key and
1582 r.subject_id = t_pub_id(j) and
1583 r.relationship_type = 2 and
1584 c.company_id = r.object_id;
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 t_supp_id(j) := null;
1588 t_supp(j) := null;
1589 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error in 2nd query: ' || sqlerrm);
1590 END;
1591 else
1592 t_supp_id(j) := t_supp_id(j-1);
1593 t_supp(j) := t_supp(j-1);
1594 end if;
1595
1596 if (j = 1) or (t_source_supp_site_id(j-1) <> t_source_supp_site_id(j)) then
1597 BEGIN
1598 select s.company_site_id,
1599 s.company_site_name
1600 into t_supp_site_id(j),
1601 t_supp_site(j)
1602 from msc_company_sites s,
1603 msc_trading_partner_maps m
1604 where m.tp_key = t_source_supp_site_id(j) and
1605 m.map_type = 3 and
1606 s.company_site_id = m.company_key and
1607 s.company_id = t_supp_id(j);
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 t_supp_site_id(j) := null;
1611 t_supp_site(j) := null;
1612 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error in 3rd query: ' || sqlerrm);
1613 END;
1614 else
1615 t_supp_site_id(j) := t_supp_site_id(j-1);
1616 t_supp_site(j) := t_supp_site(j-1);
1617 end if;
1618
1619 --log_message('DEBUG : t_supp_id(' || j ||')/t_supp('||j|| ') : ' || t_supp_id(j) || '/' || t_supp(j));
1620
1621 t_master_item_name.EXTEND;
1622 t_master_item_desc.EXTEND;
1623
1624 select item_name,
1625 description
1626 into t_master_item_name(j),
1627 t_master_item_desc(j)
1628 from msc_items
1629 where inventory_item_id = t_item_id(j);
1630
1631 t_supp_item_name.EXTEND;
1632 t_supp_item_desc.EXTEND;
1633 t_tp_uom.EXTEND;
1634 t_tp_qty.EXTEND;
1635 t_tp_planned_order_qty.EXTEND;
1636 t_tp_released_qty.EXTEND;
1637
1638 begin
1639 select msi.item_name,
1640 msi.description,
1641 msi.uom_code
1642 into t_supp_item_name(j),
1643 t_supp_item_desc(j),
1644 t_tp_uom(j)
1645 from msc_system_items msi,
1646 msc_trading_partners part,
1647 msc_trading_partner_maps map
1648 where msi.plan_id = -1 and
1649 msi.inventory_item_id = t_item_id(j) and
1650 msi.organization_id = part.sr_tp_id and
1651 msi.sr_instance_id = part.sr_instance_id and
1652 part.partner_id = map.tp_key and
1653 map.map_type = 2 and
1654 map.company_key = t_supp_site_id(j) and
1655 nvl(part.company_id,-1) = t_supp_id(j);
1656 exception
1657 when no_data_found then
1658
1659 begin
1660 select distinct mis.supplier_item_name,
1661 mis.description,
1662 mis.uom_code
1663 into t_supp_item_name(j),
1664 t_supp_item_desc(j),
1665 t_tp_uom(j)
1666 from msc_item_suppliers mis,
1667 msc_trading_partner_maps m,
1668 msc_trading_partner_maps m2,
1669 msc_company_relationships r
1670 where mis.plan_id = -1 and
1671 mis.inventory_item_id = t_item_id(j) and
1672 mis.organization_id = t_org_id(j) and
1673 mis.sr_instance_id = t_sr_instance_id(j) and
1674 r.relationship_type = 2 and
1675 r.subject_id = 1 and
1676 r.object_id = t_supp_id(j) and
1677 m.map_type = 1 and
1678 m.company_key = r.relationship_id and
1679 mis.supplier_id = m.tp_key and
1680 m2.map_type = 3 and
1681 m2.company_key = t_supp_site_id(j) and
1682 nvl(mis.supplier_site_id, m2.tp_key) = m2.tp_key;
1683
1684 exception
1685 when OTHERS then
1686 t_supp_item_name(j) := null;
1687 t_supp_item_desc(j) := null;
1688 t_tp_uom(j) := t_uom_code(j);
1689 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error in 4th query: ' || sqlerrm);
1690 end;
1691 end;
1692
1693 IF (t_supp_item_desc(j) is null and t_supp_item_name(j) is not null ) THEN
1694 begin
1695 select description
1696 into t_supp_item_desc(j)
1697 from msc_system_items
1698 where plan_id = -1 and
1699 inventory_item_id = t_item_id(j) and
1700 organization_id = t_org_id(j) and
1701 sr_instance_id = t_sr_instance_id(j);
1702 exception
1703 when OTHERS then
1704 t_supp_item_desc(j) := null;
1705 end;
1706 END IF;
1707
1708 /*--------------------------------------------------------------------
1709 get the base item name
1710 -------------------------------------------------------------------*/
1711
1712 t_base_item_name.EXTEND;
1713 t_base_item_desc.EXTEND;
1714 BEGIN
1715 select item_name, description
1716 into t_base_item_name(j),
1717 t_base_item_desc(j)
1718 from msc_items
1719 where inventory_item_id = t_base_item_id(j);
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 t_base_item_name(j) := null;
1723 t_base_item_desc(j) := null;
1724 END;
1725
1726
1727 msc_x_util.get_uom_conversion_rates( t_uom_code(j),
1728 t_tp_uom(j),
1729 t_item_id(j),
1730 l_conversion_found,
1731 l_conversion_rate);
1732 if l_conversion_found then
1733 t_tp_qty(j) := nvl(t_qty(j),0)* l_conversion_rate;
1734 t_tp_planned_order_qty(j) := nvl(t_planned_order_qty(j),0) * l_conversion_rate;
1735 t_tp_released_qty(j) := nvl(t_released_qty(j),0) * l_conversion_rate;
1736 else
1737 t_tp_qty(j) := t_qty(j);
1738 t_tp_planned_order_qty(j) := t_planned_order_qty(j);
1739 t_tp_released_qty(j) := t_released_qty(j);
1740 end if;
1741
1742
1743 --Ship date = receipt date - ppt
1744 /*debug*/
1745 if (t_ship_date(j) is NULL) then
1746 open c_supplier_lead_time(
1747 t_item_id(j),
1748 t_org_id(j),
1749 t_sr_instance_id(j),
1750 t_supp_id(j),
1751 t_supp_site_id(j)
1752 );
1753 fetch c_supplier_lead_time into l_lead_time, l_using_org_id;
1754 close c_supplier_lead_time;
1755
1756
1757 t_ship_date(j) := t_receipt_date(j) - nvl(l_lead_time, 0);
1758 end if;
1759
1760 end loop;
1761 end if;
1762
1763 EXCEPTION
1764 WHEN others then
1765 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error in get option info proc: ' || sqlerrm);
1766 END get_optional_info;
1767
1768
1769 PROCEDURE insert_into_sup_dem (
1770 t_pub IN companyNameList,
1771 t_pub_id IN numberList,
1772 t_pub_site IN companySiteList,
1773 t_pub_site_id IN numberList,
1774 t_item_id IN numberList,
1775 t_order_type IN numberList,
1776 t_qty IN numberList,
1777 t_planned_order_qty IN numberList,
1778 t_released_qty IN numberList,
1779 t_supp IN companyNameList,
1780 t_supp_id IN numberList,
1781 t_supp_site IN companySiteList,
1782 t_supp_site_id IN numberList,
1783 t_owner_item_name IN itemNameList,
1784 t_owner_item_desc IN itemDescList,
1785 t_base_item_id IN numberList,
1786 t_base_item_name IN itemNameList,
1787 t_base_item_desc IN itemDescList,
1788 t_proj_number IN numberList,
1789 t_task_number IN numberList,
1790 t_planning_gp IN planningGroupList,
1791 t_uom_code IN itemUomList,
1792 t_planner_code IN plannerCodeList,
1793 t_bucket_type IN numberList,
1794 t_key_date IN dateList,
1795 t_ship_date IN dateList,
1796 t_receipt_date IN dateList,
1797 t_master_item_name IN itemNameList,
1798 t_master_item_desc IN itemDescList,
1799 t_supp_item_name IN itemNameList,
1800 t_supp_item_desc IN itemDescList,
1801 t_tp_uom IN itemUomList,
1802 t_tp_qty IN numberList,
1803 t_tp_planned_order_qty IN numberList,
1804 t_tp_released_qty IN numberList,
1805 p_version IN varchar2,
1806 p_designator IN varchar2,
1807 p_user_id IN number,
1808 p_language_code IN varchar2
1809 ) IS
1810
1811
1812 l_order_type_desc varchar2(80);
1813 l_log_message VARCHAR2(1000);
1814
1815 l_bucket_type_desc varchar2(80);
1816 l_publish_item_id number;
1817 l_prev_publish_item_id number;
1818 l_count number := 0;
1819 l_planned_order_qty Number;
1820 l_tp_planned_order_qty Number;
1821 l_released_qty Number;
1822 l_tp_released_qty Number;
1823 l_item_id Number;
1824 l_base_item_name msc_sup_dem_entries.item_name%type;
1825 l_base_item_desc msc_sup_dem_entries.item_description%type;
1826
1827 BEGIN
1828
1829
1830 log_message('In insert_into_sup_dem');
1831 if t_pub_id is not null and t_pub_id.COUNT > 0 then
1832 log_message('Records fetched := ' || t_pub_id.COUNT);
1833 FND_FILE.PUT_LINE(FND_FILE.LOG,'Records fetched := ' || t_pub_id.COUNT);
1834 if t_pub_id is not null and t_pub_id.COUNT > 0 THEN
1835 l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',p_language_code) || ': ' || t_pub_id.COUNT || '.' || fnd_global.local_chr(10);
1836 log_message(l_log_message);
1837 log_message('Records to be inserted : ' || t_pub_id.COUNT);
1838
1839 ------------------------------------------------------------------------------------------
1840 -- If the item has base model, will use the base model for the order forecast order type
1841 -- use the regular item for other order types
1842 ------------------------------------------------------------------------------------------
1843 FOR j in 1..t_pub_id.COUNT LOOP
1844
1845 if (j > 1 and t_pub_id(j) = t_pub_id(j-1) and
1846 t_pub_site_id(j) = t_pub_site_id(j-1) and
1847 t_item_id(j) = t_item_id(j-1) and
1848 t_supp_id(j) = t_supp_id(j-1) and
1849 t_supp_site_id(j) = t_supp_site_id(j-1) and
1850 trunc(t_key_date(j)) = trunc(t_key_date(j-1)) and
1851 trunc(t_ship_date(j)) = trunc(t_ship_date(j-1)) and
1852 t_order_type(j) = t_order_type(j-1)) THEN
1853
1854 IF (t_order_type(j) = PLANNED_EXTERNAL_REPAIR_ORDER) THEN
1855 l_order_type_desc:=MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',ORDER_FORECAST);
1856 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
1857 begin
1858 IF (t_base_item_id(j) is not null) THEN
1859 update msc_sup_dem_entries
1860 set quantity = quantity + t_qty(j),
1861 primary_quantity = primary_quantity + t_qty(j),
1862 tp_quantity = tp_quantity + t_tp_qty(j)
1863 where publisher_id = t_pub_id(j)
1864 and publisher_site_id = t_pub_site_id(j)
1865 and supplier_id = t_supp_id(j)
1866 and supplier_site_id = t_supp_site_id(j)
1867 and inventory_item_id = t_base_item_id(j)
1868 and trunc(key_date) = trunc(t_key_date(j))
1869 and publisher_order_type = ORDER_FORECAST;
1870 IF (SQL%ROWCOUNT = 0) THEN
1871 insert into msc_sup_dem_entries (
1872 transaction_id,
1873 plan_id,
1874 sr_instance_id,
1875 publisher_name,
1876 publisher_id,
1877 publisher_site_name,
1878 publisher_site_id,
1879 customer_name,
1880 customer_id,
1881 customer_site_name,
1882 customer_site_id,
1883 supplier_name,
1884 supplier_id,
1885 supplier_site_name,
1886 supplier_site_id,
1887 ship_from_party_name,
1888 ship_from_party_id,
1889 ship_from_party_site_name,
1890 ship_from_party_site_id,
1891 ship_to_party_name,
1892 ship_to_party_id,
1893 ship_to_party_site_name,
1894 ship_to_party_site_id,
1895 publisher_order_type,
1896 publisher_order_type_desc,
1897 bucket_type_desc,
1898 bucket_type,
1899 inventory_item_id,
1900 item_name,
1901 owner_item_name,
1902 customer_item_name,
1903 supplier_item_name,
1904 item_description,
1905 owner_item_description,
1906 customer_item_description,
1907 supplier_item_description,
1908 primary_uom,
1909 uom_code,
1910 tp_uom_code,
1911 key_date,
1912 ship_date,
1913 receipt_date,
1914 quantity,
1915 primary_quantity,
1916 tp_quantity,
1917 last_refresh_number,
1918 posting_party_name,
1919 posting_party_id,
1920 created_by,
1921 creation_date,
1922 last_updated_by,
1923 last_update_date,
1924 project_number,
1925 task_number,
1926 planning_group,
1927 planner_code,
1928 version,
1929 designator
1930 ) values (
1931 msc_sup_dem_entries_s.nextval,
1932 -1,
1933 -1,
1934 t_pub(j),
1935 t_pub_id(j),
1936 t_pub_site(j),
1937 t_pub_site_id(j),
1938 t_pub(j),
1939 t_pub_id(j),
1940 t_pub_site(j),
1941 t_pub_site_id(j),
1942 t_supp(j),
1943 t_supp_id(j),
1944 t_supp_site(j),
1945 t_supp_site_id(j),
1946 t_supp(j),
1947 t_supp_id(j),
1948 t_supp_site(j),
1949 t_supp_site_id(j),
1950 t_pub(j),
1951 t_pub_id(j),
1952 t_pub_site(j),
1953 t_pub_site_id(j),
1954 ORDER_FORECAST,
1955 l_order_type_desc,
1956 l_bucket_type_desc,
1957 t_bucket_type(j),
1958 t_base_item_id(j),
1959 t_base_item_name(j),
1960 t_base_item_name(j),
1961 t_base_item_name(j),
1962 t_supp_item_name(j),
1963 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
1964 t_base_item_desc(j),
1965 t_base_item_desc(j),
1966 t_supp_item_desc(j),
1967 t_uom_code(j),
1968 t_uom_code(j),
1969 t_tp_uom(j),
1970 t_key_date(j),
1971 t_ship_date(j),
1972 t_receipt_date(j),
1973 t_qty(j),
1974 t_qty(j),
1975 t_tp_qty(j),
1976 msc_cl_refresh_s.nextval,
1977 t_pub(j),
1978 t_pub_id(j),
1979 nvl(p_user_id,-1),
1980 sysdate,
1981 nvl(p_user_id,-1),
1982 sysdate,
1983 t_proj_number(j),
1984 t_task_number(j),
1985 t_planning_gp(j),
1986 t_planner_code(j),
1987 p_version,
1988 p_designator);
1989 END IF; --rowcount
1990 ELSE
1991 update msc_sup_dem_entries
1992 set quantity = quantity + t_qty(j),
1993 primary_quantity = primary_quantity + t_qty(j),
1994 tp_quantity = tp_quantity + t_tp_qty(j)
1995 where publisher_id = t_pub_id(j)
1996 and publisher_site_id = t_pub_site_id(j)
1997 and supplier_id = t_supp_id(j)
1998 and supplier_site_id = t_supp_site_id(j)
1999 and inventory_item_id = t_item_id(j)
2000 and trunc(key_date) = trunc(t_key_date(j))
2001 and publisher_order_type = ORDER_FORECAST;
2002 END IF;
2003
2004 exception
2005 when others then
2006 null;
2007 end;
2008 ELSIF (t_order_type(j) = EXTERNAL_REPAIR_ORDER) THEN
2009 l_order_type_desc:=MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',ORDER_FORECAST);
2010 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
2011 begin
2012 IF (t_base_item_id(j) is not null) THEN
2013 update msc_sup_dem_entries
2014 set quantity = quantity + t_qty(j),
2015 primary_quantity = primary_quantity + t_qty(j),
2016 tp_quantity = tp_quantity + t_tp_qty(j)
2017 where publisher_id = t_pub_id(j)
2018 and publisher_site_id = t_pub_site_id(j)
2019 and supplier_id = t_supp_id(j)
2020 and supplier_site_id = t_supp_site_id(j)
2021 and inventory_item_id = t_base_item_id(j)
2022 and trunc(key_date) = trunc(t_key_date(j))
2023 and publisher_order_type = ORDER_FORECAST;
2024 IF (SQL%ROWCOUNT = 0) THEN
2025 insert into msc_sup_dem_entries (
2026 transaction_id,
2027 plan_id,
2028 sr_instance_id,
2029 publisher_name,
2030 publisher_id,
2031 publisher_site_name,
2032 publisher_site_id,
2033 customer_name,
2034 customer_id,
2035 customer_site_name,
2036 customer_site_id,
2037 supplier_name,
2038 supplier_id,
2039 supplier_site_name,
2040 supplier_site_id,
2041 ship_from_party_name,
2042 ship_from_party_id,
2043 ship_from_party_site_name,
2044 ship_from_party_site_id,
2045 ship_to_party_name,
2046 ship_to_party_id,
2047 ship_to_party_site_name,
2048 ship_to_party_site_id,
2049 publisher_order_type,
2050 publisher_order_type_desc,
2051 bucket_type_desc,
2052 bucket_type,
2053 inventory_item_id,
2054 item_name,
2055 owner_item_name,
2056 customer_item_name,
2057 supplier_item_name,
2058 item_description,
2059 owner_item_description,
2060 customer_item_description,
2061 supplier_item_description,
2062 primary_uom,
2063 uom_code,
2064 tp_uom_code,
2065 key_date,
2066 ship_date,
2067 receipt_date,
2068 quantity,
2069 primary_quantity,
2070 tp_quantity,
2071 last_refresh_number,
2072 posting_party_name,
2073 posting_party_id,
2074 created_by,
2075 creation_date,
2076 last_updated_by,
2077 last_update_date,
2078 project_number,
2079 task_number,
2080 planning_group,
2081 planner_code,
2082 version,
2083 designator
2084 ) values (
2085 msc_sup_dem_entries_s.nextval,
2086 -1,
2087 -1,
2088 t_pub(j),
2089 t_pub_id(j),
2090 t_pub_site(j),
2091 t_pub_site_id(j),
2092 t_pub(j),
2093 t_pub_id(j),
2094 t_pub_site(j),
2095 t_pub_site_id(j),
2096 t_supp(j),
2097 t_supp_id(j),
2098 t_supp_site(j),
2099 t_supp_site_id(j),
2100 t_supp(j),
2101 t_supp_id(j),
2102 t_supp_site(j),
2103 t_supp_site_id(j),
2104 t_pub(j),
2105 t_pub_id(j),
2106 t_pub_site(j),
2107 t_pub_site_id(j),
2108 ORDER_FORECAST,
2109 l_order_type_desc,
2110 l_bucket_type_desc,
2111 t_bucket_type(j),
2112 t_base_item_id(j),
2113 t_base_item_name(j),
2114 t_base_item_name(j),
2115 t_base_item_name(j),
2116 t_supp_item_name(j),
2117 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2118 t_base_item_desc(j),
2119 t_base_item_desc(j),
2120 t_supp_item_desc(j),
2121 t_uom_code(j),
2122 t_uom_code(j),
2123 t_tp_uom(j),
2124 t_key_date(j),
2125 t_ship_date(j),
2126 t_receipt_date(j),
2127 t_qty(j),
2128 t_qty(j),
2129 t_tp_qty(j),
2130 msc_cl_refresh_s.nextval,
2131 t_pub(j),
2132 t_pub_id(j),
2133 nvl(p_user_id,-1),
2134 sysdate,
2135 nvl(p_user_id,-1),
2136 sysdate,
2137 t_proj_number(j),
2138 t_task_number(j),
2139 t_planning_gp(j),
2140 t_planner_code(j),
2141 p_version,
2142 p_designator);
2143 END IF; --rowcount
2144 ELSE
2145 update msc_sup_dem_entries
2146 set quantity = quantity + t_qty(j),
2147 primary_quantity = primary_quantity + t_qty(j),
2148 tp_quantity = tp_quantity + t_tp_qty(j)
2149 where publisher_id = t_pub_id(j)
2150 and publisher_site_id = t_pub_site_id(j)
2151 and supplier_id = t_supp_id(j)
2152 and supplier_site_id = t_supp_site_id(j)
2153 and inventory_item_id = t_item_id(j)
2154 and trunc(key_date) = trunc(t_key_date(j))
2155 and publisher_order_type = ORDER_FORECAST;
2156 END IF;
2157
2158 exception
2159 when others then
2160 null;
2161 end;
2162 ELSIF (t_order_type(j) = PLANNED_NEW_BUY_ORDER) THEN
2163 l_order_type_desc:=MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',ORDER_FORECAST);
2164 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
2165 begin
2166 IF (t_base_item_id(j) is not null) THEN
2167 update msc_sup_dem_entries
2168 set quantity = quantity + t_qty(j),
2169 primary_quantity = primary_quantity + t_qty(j),
2170 tp_quantity = tp_quantity + t_tp_qty(j)
2171 where publisher_id = t_pub_id(j)
2172 and publisher_site_id = t_pub_site_id(j)
2173 and supplier_id = t_supp_id(j)
2174 and supplier_site_id = t_supp_site_id(j)
2175 and inventory_item_id = t_base_item_id(j)
2176 and trunc(key_date) = trunc(t_key_date(j))
2177 and publisher_order_type = ORDER_FORECAST;
2178 IF (SQL%ROWCOUNT = 0) THEN
2179 insert into msc_sup_dem_entries (
2180 transaction_id,
2181 plan_id,
2182 sr_instance_id,
2183 publisher_name,
2184 publisher_id,
2185 publisher_site_name,
2186 publisher_site_id,
2187 customer_name,
2188 customer_id,
2189 customer_site_name,
2190 customer_site_id,
2191 supplier_name,
2192 supplier_id,
2193 supplier_site_name,
2194 supplier_site_id,
2195 ship_from_party_name,
2196 ship_from_party_id,
2197 ship_from_party_site_name,
2198 ship_from_party_site_id,
2199 ship_to_party_name,
2200 ship_to_party_id,
2201 ship_to_party_site_name,
2202 ship_to_party_site_id,
2203 publisher_order_type,
2204 publisher_order_type_desc,
2205 bucket_type_desc,
2206 bucket_type,
2207 inventory_item_id,
2208 item_name,
2209 owner_item_name,
2210 customer_item_name,
2211 supplier_item_name,
2212 item_description,
2213 owner_item_description,
2214 customer_item_description,
2215 supplier_item_description,
2216 primary_uom,
2217 uom_code,
2218 tp_uom_code,
2219 key_date,
2220 ship_date,
2221 receipt_date,
2222 quantity,
2223 primary_quantity,
2224 tp_quantity,
2225 last_refresh_number,
2226 posting_party_name,
2227 posting_party_id,
2228 created_by,
2229 creation_date,
2230 last_updated_by,
2231 last_update_date,
2232 project_number,
2233 task_number,
2234 planning_group,
2235 planner_code,
2236 version,
2237 designator
2238 ) values (
2239 msc_sup_dem_entries_s.nextval,
2240 -1,
2241 -1,
2242 t_pub(j),
2243 t_pub_id(j),
2244 t_pub_site(j),
2245 t_pub_site_id(j),
2246 t_pub(j),
2247 t_pub_id(j),
2248 t_pub_site(j),
2249 t_pub_site_id(j),
2250 t_supp(j),
2251 t_supp_id(j),
2252 t_supp_site(j),
2253 t_supp_site_id(j),
2254 t_supp(j),
2255 t_supp_id(j),
2256 t_supp_site(j),
2257 t_supp_site_id(j),
2258 t_pub(j),
2259 t_pub_id(j),
2260 t_pub_site(j),
2261 t_pub_site_id(j),
2262 ORDER_FORECAST,
2263 l_order_type_desc,
2264 l_bucket_type_desc,
2265 t_bucket_type(j),
2266 t_base_item_id(j),
2267 t_base_item_name(j),
2268 t_base_item_name(j),
2269 t_base_item_name(j),
2270 t_supp_item_name(j),
2271 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2272 t_base_item_desc(j),
2273 t_base_item_desc(j),
2274 t_supp_item_desc(j),
2275 t_uom_code(j),
2276 t_uom_code(j),
2277 t_tp_uom(j),
2278 t_key_date(j),
2279 t_ship_date(j),
2280 t_receipt_date(j),
2281 t_qty(j),
2282 t_qty(j),
2283 t_tp_qty(j),
2284 msc_cl_refresh_s.nextval,
2285 t_pub(j),
2286 t_pub_id(j),
2287 nvl(p_user_id,-1),
2288 sysdate,
2289 nvl(p_user_id,-1),
2290 sysdate,
2291 t_proj_number(j),
2292 t_task_number(j),
2293 t_planning_gp(j),
2294 t_planner_code(j),
2295 p_version,
2296 p_designator);
2297 END IF; --rowcount
2298 ELSE
2299 update msc_sup_dem_entries
2300 set quantity = quantity + t_qty(j),
2301 primary_quantity = primary_quantity + t_qty(j),
2302 tp_quantity = tp_quantity + t_tp_qty(j)
2303 where publisher_id = t_pub_id(j)
2304 and publisher_site_id = t_pub_site_id(j)
2305 and supplier_id = t_supp_id(j)
2306 and supplier_site_id = t_supp_site_id(j)
2307 and inventory_item_id = t_item_id(j)
2308 and trunc(key_date) = trunc(t_key_date(j))
2309 and publisher_order_type = ORDER_FORECAST;
2310 END IF;
2311
2312 exception
2313 when others then
2314 null;
2315 end;
2316 ELSIF (t_order_type(j) = PLANNED_ORDER) THEN
2317 begin
2318 IF (t_base_item_id(j) is not null) THEN
2319
2320
2321 update msc_sup_dem_entries
2322 set quantity = quantity + t_qty(j),
2323 primary_quantity = primary_quantity + t_qty(j),
2324 tp_quantity = tp_quantity + t_tp_qty(j)
2325 where publisher_id = t_pub_id(j)
2326 and publisher_site_id = t_pub_site_id(j)
2327 and supplier_id = t_supp_id(j)
2328 and supplier_site_id = t_supp_site_id(j)
2329 and inventory_item_id = t_base_item_id(j)
2330 and trunc(key_date) = trunc(t_key_date(j))
2331 and publisher_order_type = ORDER_FORECAST;
2332
2333 IF (SQL%ROWCOUNT = 0) THEN
2334 insert into msc_sup_dem_entries (
2335 transaction_id,
2336 plan_id,
2337 sr_instance_id,
2338 publisher_name,
2339 publisher_id,
2340 publisher_site_name,
2341 publisher_site_id,
2342 customer_name,
2343 customer_id,
2344 customer_site_name,
2345 customer_site_id,
2346 supplier_name,
2347 supplier_id,
2348 supplier_site_name,
2349 supplier_site_id,
2350 ship_from_party_name,
2351 ship_from_party_id,
2352 ship_from_party_site_name,
2353 ship_from_party_site_id,
2354 ship_to_party_name,
2355 ship_to_party_id,
2356 ship_to_party_site_name,
2357 ship_to_party_site_id,
2358 publisher_order_type,
2359 publisher_order_type_desc,
2360 bucket_type_desc,
2361 bucket_type,
2362 inventory_item_id,
2363 item_name,
2364 owner_item_name,
2365 customer_item_name,
2366 supplier_item_name,
2367 item_description,
2368 owner_item_description,
2369 customer_item_description,
2370 supplier_item_description,
2371 primary_uom,
2372 uom_code,
2373 tp_uom_code,
2374 key_date,
2375 ship_date,
2376 receipt_date,
2377 quantity,
2378 primary_quantity,
2379 tp_quantity,
2380 last_refresh_number,
2381 posting_party_name,
2382 posting_party_id,
2383 created_by,
2384 creation_date,
2385 last_updated_by,
2386 last_update_date,
2387 project_number,
2388 task_number,
2389 planning_group,
2390 planner_code,
2391 version,
2392 designator
2393 ) values (
2394 msc_sup_dem_entries_s.nextval,
2395 -1,
2396 -1,
2397 t_pub(j),
2398 t_pub_id(j),
2399 t_pub_site(j),
2400 t_pub_site_id(j),
2401 t_pub(j),
2402 t_pub_id(j),
2403 t_pub_site(j),
2404 t_pub_site_id(j),
2405 t_supp(j),
2406 t_supp_id(j),
2407 t_supp_site(j),
2408 t_supp_site_id(j),
2409 t_supp(j),
2410 t_supp_id(j),
2411 t_supp_site(j),
2412 t_supp_site_id(j),
2413 t_pub(j),
2414 t_pub_id(j),
2415 t_pub_site(j),
2416 t_pub_site_id(j),
2417 ORDER_FORECAST,
2418 l_order_type_desc,
2419 l_bucket_type_desc,
2420 t_bucket_type(j),
2421 t_base_item_id(j),
2422 t_base_item_name(j),
2423 t_base_item_name(j),
2424 t_base_item_name(j),
2425 t_supp_item_name(j),
2426 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2427 t_base_item_desc(j),
2428 t_base_item_desc(j),
2429 t_supp_item_desc(j),
2430 t_uom_code(j),
2431 t_uom_code(j),
2432 t_tp_uom(j),
2433 t_key_date(j),
2434 t_ship_date(j),
2435 t_receipt_date(j),
2436 t_qty(j),
2437 t_qty(j),
2438 t_tp_qty(j),
2439 msc_cl_refresh_s.nextval,
2440 t_pub(j),
2441 t_pub_id(j),
2442 nvl(p_user_id,-1),
2443 sysdate,
2444 nvl(p_user_id,-1),
2445 sysdate,
2446 t_proj_number(j),
2447 t_task_number(j),
2448 t_planning_gp(j),
2449 t_planner_code(j),
2450 p_version,
2451 p_designator);
2452 END IF; --rowcount
2453 ELSE
2454 update msc_sup_dem_entries
2455 set quantity = quantity + t_qty(j),
2456 primary_quantity = primary_quantity + t_qty(j),
2457 tp_quantity = quantity + t_tp_qty(j)
2458 where publisher_id = t_pub_id(j)
2459 and publisher_site_id = t_pub_site_id(j)
2460 and supplier_id = t_supp_id(j)
2461 and supplier_site_id = t_supp_site_id(j)
2462 and inventory_item_id = t_item_id(j)
2463 and trunc(key_date) = trunc(t_key_date(j))
2464 and publisher_order_type = ORDER_FORECAST;
2465
2466 END IF;
2467
2468 update msc_sup_dem_entries
2469 set quantity = quantity + t_planned_order_qty(j),
2470 primary_quantity = primary_quantity + t_planned_order_qty(j) ,
2471 tp_quantity = tp_quantity + t_tp_planned_order_qty(j)
2472 where publisher_id = t_pub_id(j)
2473 and publisher_site_id = t_pub_site_id(j)
2474 and supplier_id = t_supp_id(j)
2475 and supplier_site_id = t_supp_site_id(j)
2476 and inventory_item_id = t_item_id(j)
2477 and trunc(key_date) = trunc(t_key_date(j))
2478 and publisher_order_type = CP_PLANNED_ORDER;
2479
2480 update msc_sup_dem_entries
2481 set quantity = quantity + t_released_qty(j),
2482 primary_quantity = primary_quantity + t_released_qty(j),
2483 tp_quantity = tp_quantity + t_tp_released_qty(j)
2484 where publisher_id = t_pub_id(j)
2485 and publisher_site_id = t_pub_site_id(j)
2486 and supplier_id = t_supp_id(j)
2487 and supplier_site_id = t_supp_site_id(j)
2488 and inventory_item_id = t_item_id(j)
2489 and trunc(key_date) = trunc(t_key_date(j))
2490 and publisher_order_type = CP_RELEASED_PLANNED_ORDER;
2491
2492 exception
2493 when others then
2494 null;
2495 end;
2496 ELSIF (t_order_type(j) = REQUISITION) THEN
2497
2498 IF (t_base_item_id(j) is not null) THEN
2499
2500 update msc_sup_dem_entries
2501 set quantity = quantity + t_qty(j),
2502 primary_quantity = primary_quantity + t_qty(j),
2503 tp_quantity = tp_quantity + t_tp_qty(j)
2504 where publisher_id = t_pub_id(j)
2505 and publisher_site_id = t_pub_site_id(j)
2506 and supplier_id = t_supp_id(j)
2507 and supplier_site_id = t_supp_site_id(j)
2508 and inventory_item_id = t_base_item_id(j)
2509 and trunc(key_date) = trunc(t_key_date(j))
2510 and publisher_order_type = ORDER_FORECAST;
2511 IF (SQL%ROWCOUNT = 0) THEN
2512 insert into msc_sup_dem_entries (
2513 transaction_id,
2514 plan_id,
2515 sr_instance_id,
2516 publisher_name,
2517 publisher_id,
2518 publisher_site_name,
2519 publisher_site_id,
2520 customer_name,
2521 customer_id,
2522 customer_site_name,
2523 customer_site_id,
2524 supplier_name,
2525 supplier_id,
2526 supplier_site_name,
2527 supplier_site_id,
2528 ship_from_party_name,
2529 ship_from_party_id,
2530 ship_from_party_site_name,
2531 ship_from_party_site_id,
2532 ship_to_party_name,
2533 ship_to_party_id,
2534 ship_to_party_site_name,
2535 ship_to_party_site_id,
2536 publisher_order_type,
2537 publisher_order_type_desc,
2538 bucket_type_desc,
2539 bucket_type,
2540 inventory_item_id,
2541 item_name,
2542 owner_item_name,
2543 customer_item_name,
2544 supplier_item_name,
2545 item_description,
2546 owner_item_description,
2547 customer_item_description,
2548 supplier_item_description,
2549 primary_uom,
2550 uom_code,
2551 tp_uom_code,
2552 key_date,
2553 ship_date,
2554 receipt_date,
2555 quantity,
2556 primary_quantity,
2557 tp_quantity,
2558 last_refresh_number,
2559 posting_party_name,
2560 posting_party_id,
2561 created_by,
2562 creation_date,
2563 last_updated_by,
2564 last_update_date,
2565 project_number,
2566 task_number,
2567 planning_group,
2568 planner_code,
2569 version,
2570 designator
2571 ) values (
2572 msc_sup_dem_entries_s.nextval,
2573 -1,
2574 -1,
2575 t_pub(j),
2576 t_pub_id(j),
2577 t_pub_site(j),
2578 t_pub_site_id(j),
2579 t_pub(j),
2580 t_pub_id(j),
2581 t_pub_site(j),
2582 t_pub_site_id(j),
2583 t_supp(j),
2584 t_supp_id(j),
2585 t_supp_site(j),
2586 t_supp_site_id(j),
2587 t_supp(j),
2588 t_supp_id(j),
2589 t_supp_site(j),
2590 t_supp_site_id(j),
2591 t_pub(j),
2592 t_pub_id(j),
2593 t_pub_site(j),
2594 t_pub_site_id(j),
2595 ORDER_FORECAST,
2596 l_order_type_desc,
2597 l_bucket_type_desc,
2598 t_bucket_type(j),
2599 t_base_item_id(j),
2600 t_base_item_name(j),
2601 t_base_item_name(j),
2602 t_base_item_name(j),
2603 t_supp_item_name(j),
2604 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2605 t_base_item_desc(j),
2606 t_base_item_desc(j),
2607 t_supp_item_desc(j),
2608 t_uom_code(j),
2609 t_uom_code(j),
2610 t_tp_uom(j),
2611 t_key_date(j),
2612 t_ship_date(j),
2613 t_receipt_date(j),
2614 t_qty(j),
2615 t_qty(j),
2616 t_tp_qty(j),
2617 msc_cl_refresh_s.nextval,
2618 t_pub(j),
2619 t_pub_id(j),
2620 nvl(p_user_id,-1),
2621 sysdate,
2622 nvl(p_user_id,-1),
2623 sysdate,
2624 t_proj_number(j),
2625 t_task_number(j),
2626 t_planning_gp(j),
2627 t_planner_code(j),
2628 p_version,
2629 p_designator);
2630 END IF; --rowcount
2631
2632 ELSE
2633 update msc_sup_dem_entries
2634 set quantity = quantity + t_qty(j),
2635 primary_quantity = primary_quantity + t_qty(j),
2636 tp_quantity = tp_quantity + t_tp_qty(j)
2637 where publisher_id = t_pub_id(j)
2638 and publisher_site_id = t_pub_site_id(j)
2639 and supplier_id = t_supp_id(j)
2640 and supplier_site_id = t_supp_site_id(j)
2641 and inventory_item_id = t_item_id(j)
2642 and trunc(key_date) = trunc(t_key_date(j))
2643 and publisher_order_type = ORDER_FORECAST;
2644 END IF;
2645
2646 ELSIF (t_order_type(j) = PURCHASE_ORDER) THEN
2647
2648
2649 IF (t_base_item_id(j) is not null) THEN
2650 update msc_sup_dem_entries
2651 set quantity = quantity + t_qty(j),
2652 primary_quantity = primary_quantity + t_qty(j),
2653 tp_quantity = tp_quantity + t_tp_qty(j)
2654 where publisher_id = t_pub_id(j)
2655 and publisher_site_id = t_pub_site_id(j)
2656 and supplier_id = t_supp_id(j)
2657 and supplier_site_id = t_supp_site_id(j)
2658 and inventory_item_id = t_base_item_id(j)
2659 and trunc(key_date) = trunc(t_key_date(j))
2660 and publisher_order_type = ORDER_FORECAST;
2661 ELSE
2662 update msc_sup_dem_entries
2663 set quantity = quantity + t_qty(j),
2664 primary_quantity = primary_quantity + t_qty(j),
2665 tp_quantity = tp_quantity + t_tp_qty(j)
2666 where publisher_id = t_pub_id(j)
2667 and publisher_site_id = t_pub_site_id(j)
2668 and supplier_id = t_supp_id(j)
2669 and supplier_site_id = t_supp_site_id(j)
2670 and inventory_item_id = t_item_id(j)
2671 and trunc(key_date) = trunc(t_key_date(j))
2672 and publisher_order_type = ORDER_FORECAST;
2673 END IF;
2674 update msc_sup_dem_entries
2675 set quantity = quantity + t_qty(j),
2676 primary_quantity = primary_quantity + t_qty(j) ,
2677 tp_quantity = tp_quantity + t_tp_qty(j)
2678 where publisher_id = t_pub_id(j)
2679 and publisher_site_id = t_pub_site_id(j)
2680 and supplier_id = t_supp_id(j)
2681 and supplier_site_id = t_supp_site_id(j)
2682 and inventory_item_id = t_item_id(j)
2683 and trunc(key_date) = trunc(t_key_date(j))
2684 and publisher_order_type = CP_PURCHASE_ORDER_FROM_PLAN;
2685
2686 --- added to publish Planned Inbound Shipment PIS to CP as PIS
2687
2688 ELSIF (t_order_type(j) = EXPECTED_INBOUND_SHIPMENT) THEN
2689 begin
2690 IF (t_base_item_id(j) is not null) THEN
2691
2692
2693 update msc_sup_dem_entries
2694 set quantity = quantity + t_qty(j),
2695 primary_quantity = primary_quantity + t_qty(j),
2696 tp_quantity = tp_quantity + t_tp_qty(j)
2697 where publisher_id = t_pub_id(j)
2698 and publisher_site_id = t_pub_site_id(j)
2699 and supplier_id = t_supp_id(j)
2700 and supplier_site_id = t_supp_site_id(j)
2701 and inventory_item_id = t_base_item_id(j)
2702 and trunc(key_date) = trunc(t_key_date(j))
2703 and publisher_order_type = ORDER_FORECAST;
2704
2705 IF (SQL%ROWCOUNT = 0) THEN
2706 insert into msc_sup_dem_entries (
2707 transaction_id,
2708 plan_id,
2709 sr_instance_id,
2710 publisher_name,
2711 publisher_id,
2712 publisher_site_name,
2713 publisher_site_id,
2714 customer_name,
2715 customer_id,
2716 customer_site_name,
2717 customer_site_id,
2718 supplier_name,
2719 supplier_id,
2720 supplier_site_name,
2721 supplier_site_id,
2722 ship_from_party_name,
2723 ship_from_party_id,
2724 ship_from_party_site_name,
2725 ship_from_party_site_id,
2726 ship_to_party_name,
2727 ship_to_party_id,
2728 ship_to_party_site_name,
2729 ship_to_party_site_id,
2730 publisher_order_type,
2731 publisher_order_type_desc,
2732 bucket_type_desc,
2733 bucket_type,
2734 inventory_item_id,
2735 item_name,
2736 owner_item_name,
2737 customer_item_name,
2738 supplier_item_name,
2739 item_description,
2740 owner_item_description,
2741 customer_item_description,
2742 supplier_item_description,
2743 primary_uom,
2744 uom_code,
2745 tp_uom_code,
2746 key_date,
2747 ship_date,
2748 receipt_date,
2749 quantity,
2750 primary_quantity,
2751 tp_quantity,
2752 last_refresh_number,
2753 posting_party_name,
2754 posting_party_id,
2755 created_by,
2756 creation_date,
2757 last_updated_by,
2758 last_update_date,
2759 project_number,
2760 task_number,
2761 planning_group,
2762 planner_code,
2763 version,
2764 designator
2765 ) values (
2766 msc_sup_dem_entries_s.nextval,
2767 -1,
2768 -1,
2769 t_pub(j),
2770 t_pub_id(j),
2771 t_pub_site(j),
2772 t_pub_site_id(j),
2773 t_pub(j),
2774 t_pub_id(j),
2775 t_pub_site(j),
2776 t_pub_site_id(j),
2777 t_supp(j),
2778 t_supp_id(j),
2779 t_supp_site(j),
2780 t_supp_site_id(j),
2781 t_supp(j),
2782 t_supp_id(j),
2783 t_supp_site(j),
2784 t_supp_site_id(j),
2785 t_pub(j),
2786 t_pub_id(j),
2787 t_pub_site(j),
2788 t_pub_site_id(j),
2789 ORDER_FORECAST,
2790 l_order_type_desc,
2791 l_bucket_type_desc,
2792 t_bucket_type(j),
2793 t_base_item_id(j),
2794 t_base_item_name(j),
2795 t_base_item_name(j),
2796 t_base_item_name(j),
2797 t_supp_item_name(j),
2798 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2799 t_base_item_desc(j),
2800 t_base_item_desc(j),
2801 t_supp_item_desc(j),
2802 t_uom_code(j),
2803 t_uom_code(j),
2804 t_tp_uom(j),
2805 t_key_date(j),
2806 t_ship_date(j),
2807 t_receipt_date(j),
2808 t_qty(j),
2809 t_qty(j),
2810 t_tp_qty(j),
2811 msc_cl_refresh_s.nextval,
2812 t_pub(j),
2813 t_pub_id(j),
2814 nvl(p_user_id,-1),
2815 sysdate,
2816 nvl(p_user_id,-1),
2817 sysdate,
2818 t_proj_number(j),
2819 t_task_number(j),
2820 t_planning_gp(j),
2821 t_planner_code(j),
2822 p_version,
2823 p_designator);
2824 END IF; --rowcount
2825 ELSE
2826 update msc_sup_dem_entries
2827 set quantity = quantity + t_qty(j),
2828 primary_quantity = primary_quantity + t_qty(j),
2829 tp_quantity = quantity + t_tp_qty(j)
2830 where publisher_id = t_pub_id(j)
2831 and publisher_site_id = t_pub_site_id(j)
2832 and supplier_id = t_supp_id(j)
2833 and supplier_site_id = t_supp_site_id(j)
2834 and inventory_item_id = t_item_id(j)
2835 and trunc(key_date) = trunc(t_key_date(j))
2836 and publisher_order_type = ORDER_FORECAST;
2837
2838 END IF;
2839
2840 update msc_sup_dem_entries
2841 set quantity = quantity + t_planned_order_qty(j),
2842 primary_quantity = primary_quantity + t_planned_order_qty(j) ,
2843 tp_quantity = tp_quantity + t_tp_planned_order_qty(j)
2844 where publisher_id = t_pub_id(j)
2845 and publisher_site_id = t_pub_site_id(j)
2846 and supplier_id = t_supp_id(j)
2847 and supplier_site_id = t_supp_site_id(j)
2848 and inventory_item_id = t_item_id(j)
2849 and trunc(key_date) = trunc(t_key_date(j))
2850 and publisher_order_type = CP_PLANNED_INBOUND_SHIPMENT;
2851
2852 update msc_sup_dem_entries
2853 set quantity = quantity + t_released_qty(j),
2854 primary_quantity = primary_quantity + t_released_qty(j),
2855 tp_quantity = tp_quantity + t_tp_released_qty(j)
2856 where publisher_id = t_pub_id(j)
2857 and publisher_site_id = t_pub_site_id(j)
2858 and supplier_id = t_supp_id(j)
2859 and supplier_site_id = t_supp_site_id(j)
2860 and inventory_item_id = t_item_id(j)
2861 and trunc(key_date) = trunc(t_key_date(j))
2862 and publisher_order_type = CP_RELEASED_INBOUND_SHIPMENT;
2863
2864 exception
2865 when others then
2866 null;
2867 end;
2868
2869 END IF;
2870
2871 /*-----------------------------------------------------
2872 Elsif j > 1
2873 -------------------------------------------------------*/
2874 ELSIF (j > 1 and t_pub_id(j) = t_pub_id(j-1) and
2875 t_pub_site_id(j) = t_pub_site_id(j-1) and
2876 t_item_id(j) = t_item_id(j-1) and
2877 t_supp_id(j) = t_supp_id(j-1) and
2878 t_supp_site_id(j) = t_supp_site_id(j-1) and
2879 trunc(t_key_date(j)) = trunc(t_key_date(j-1)) and
2880 trunc(t_ship_date(j)) = trunc(t_ship_date(j-1)) and
2881 t_order_type(j) <> t_order_type(j-1)) THEN
2882
2883 IF (t_base_item_id(j) is not null) THEN
2884 update msc_sup_dem_entries
2885 set quantity = quantity + t_qty(j),
2886 primary_quantity = primary_quantity + t_qty(j) ,
2887 tp_quantity = tp_quantity + t_tp_qty(j)
2888 where publisher_id = t_pub_id(j)
2889 and publisher_site_id = t_pub_site_id(j)
2890 and supplier_id = t_supp_id(j)
2891 and supplier_site_id = t_supp_site_id(j)
2892 and inventory_item_id = t_base_item_id(j)
2893 and trunc(key_date) = trunc(t_key_date(j))
2894 and publisher_order_type = ORDER_FORECAST;
2895 IF (SQL%ROWCOUNT = 0) THEN
2896 insert into msc_sup_dem_entries (
2897 transaction_id,
2898 plan_id,
2899 sr_instance_id,
2900 publisher_name,
2901 publisher_id,
2902 publisher_site_name,
2903 publisher_site_id,
2904 customer_name,
2905 customer_id,
2906 customer_site_name,
2907 customer_site_id,
2908 supplier_name,
2909 supplier_id,
2910 supplier_site_name,
2911 supplier_site_id,
2912 ship_from_party_name,
2913 ship_from_party_id,
2914 ship_from_party_site_name,
2915 ship_from_party_site_id,
2916 ship_to_party_name,
2917 ship_to_party_id,
2918 ship_to_party_site_name,
2919 ship_to_party_site_id,
2920 publisher_order_type,
2921 publisher_order_type_desc,
2922 bucket_type_desc,
2923 bucket_type,
2924 inventory_item_id,
2925 item_name,
2926 owner_item_name,
2927 customer_item_name,
2928 supplier_item_name,
2929 item_description,
2930 owner_item_description,
2931 customer_item_description,
2932 supplier_item_description,
2933 primary_uom,
2934 uom_code,
2935 tp_uom_code,
2936 key_date,
2937 ship_date,
2938 receipt_date,
2939 quantity,
2940 primary_quantity,
2941 tp_quantity,
2942 last_refresh_number,
2943 posting_party_name,
2944 posting_party_id,
2945 created_by,
2946 creation_date,
2947 last_updated_by,
2948 last_update_date,
2949 project_number,
2950 task_number,
2951 planning_group,
2952 planner_code,
2953 version,
2954 designator
2955 ) values (
2956 msc_sup_dem_entries_s.nextval,
2957 -1,
2958 -1,
2959 t_pub(j),
2960 t_pub_id(j),
2961 t_pub_site(j),
2962 t_pub_site_id(j),
2963 t_pub(j),
2964 t_pub_id(j),
2965 t_pub_site(j),
2966 t_pub_site_id(j),
2967 t_supp(j),
2968 t_supp_id(j),
2969 t_supp_site(j),
2970 t_supp_site_id(j),
2971 t_supp(j),
2972 t_supp_id(j),
2973 t_supp_site(j),
2974 t_supp_site_id(j),
2975 t_pub(j),
2976 t_pub_id(j),
2977 t_pub_site(j),
2978 t_pub_site_id(j),
2979 ORDER_FORECAST,
2980 l_order_type_desc,
2981 l_bucket_type_desc,
2982 t_bucket_type(j),
2983 t_base_item_id(j),
2984 t_base_item_name(j),
2985 t_base_item_name(j),
2986 t_base_item_name(j),
2987 t_supp_item_name(j),
2988 nvl(t_base_item_desc(j), t_owner_item_desc(j)),
2989 t_base_item_desc(j),
2990 t_base_item_desc(j),
2991 t_supp_item_desc(j),
2992 t_uom_code(j),
2993 t_uom_code(j),
2994 t_tp_uom(j),
2995 t_key_date(j),
2996 t_ship_date(j),
2997 t_receipt_date(j),
2998 t_qty(j),
2999 t_qty(j),
3000 t_tp_qty(j),
3001 msc_cl_refresh_s.nextval,
3002 t_pub(j),
3003 t_pub_id(j),
3004 nvl(p_user_id,-1),
3005 sysdate,
3006 nvl(p_user_id,-1),
3007 sysdate,
3008 t_proj_number(j),
3009 t_task_number(j),
3010 t_planning_gp(j),
3011 t_planner_code(j),
3012 p_version,
3013 p_designator);
3014 END IF; --rowcount
3015
3016 ELSE
3017 update msc_sup_dem_entries
3018 set quantity = quantity + t_qty(j),
3019 primary_quantity = primary_quantity + t_qty(j) ,
3020 tp_quantity = tp_quantity + t_tp_qty(j)
3021 where publisher_id = t_pub_id(j)
3022 and publisher_site_id = t_pub_site_id(j)
3023 and supplier_id = t_supp_id(j)
3024 and supplier_site_id = t_supp_site_id(j)
3025 and inventory_item_id = t_item_id(j)
3026 and trunc(key_date) = trunc(t_key_date(j))
3027 and publisher_order_type = ORDER_FORECAST;
3028 END IF;
3029
3030 IF (t_order_type(j) = PLANNED_ORDER) THEN
3031 -------------------------------------------------------
3032 -- PLANNED_ORDER
3033 ------------------------------------------------------
3034 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'PLO' || t_planned_order_qty(j) || ' tp '
3035 -- || t_tp_planned_order_qty(j) || 'date ' || t_key_date(j));
3036 log_message('PLO' || t_planned_order_qty(j) || ' tp ' || t_tp_planned_order_qty(j) || 'date ' || t_key_date(j));
3037 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PLANNED_ORDER);
3038 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3039
3040 IF ( t_planned_order_qty(j) <> 0 ) THEN
3041 l_planned_order_qty := t_planned_order_qty(j);
3042 l_tp_planned_order_qty := t_planned_order_qty(j);
3043 IF (t_planned_order_qty(j) < 0 ) THEN
3044 l_planned_order_qty := 0;
3045 l_tp_planned_order_qty := 0;
3046 END IF;
3047 insert into msc_sup_dem_entries (
3048 transaction_id,
3049 plan_id,
3050 sr_instance_id,
3051 publisher_name,
3052 publisher_id,
3053 publisher_site_name,
3054 publisher_site_id,
3055 customer_name,
3056 customer_id,
3057 customer_site_name,
3058 customer_site_id,
3059 supplier_name,
3060 supplier_id,
3061 supplier_site_name,
3062 supplier_site_id,
3063 ship_from_party_name,
3064 ship_from_party_id,
3065 ship_from_party_site_name,
3066 ship_from_party_site_id,
3067 ship_to_party_name,
3068 ship_to_party_id,
3069 ship_to_party_site_name,
3070 ship_to_party_site_id,
3071 publisher_order_type,
3072 publisher_order_type_desc,
3073 bucket_type_desc,
3074 bucket_type,
3075 inventory_item_id,
3076 item_name,
3077 owner_item_name,
3078 customer_item_name,
3079 supplier_item_name,
3080 item_description,
3081 owner_item_description,
3082 customer_item_description,
3083 supplier_item_description,
3084 primary_uom,
3085 uom_code,
3086 tp_uom_code,
3087 key_date,
3088 ship_date,
3089 receipt_date,
3090 quantity,
3091 primary_quantity,
3092 tp_quantity,
3093 last_refresh_number,
3094 posting_party_name,
3095 posting_party_id,
3096 created_by,
3097 creation_date,
3098 last_updated_by,
3099 last_update_date,
3100 project_number,
3101 task_number,
3102 planning_group,
3103 planner_code,
3104 version,
3105 designator,
3106 base_item_id,
3107 base_item_name
3108 ) values (
3109 msc_sup_dem_entries_s.nextval,
3110 -1,
3111 -1,
3112 t_pub(j),
3113 t_pub_id(j),
3114 t_pub_site(j),
3115 t_pub_site_id(j),
3116 t_pub(j),
3117 t_pub_id(j),
3118 t_pub_site(j),
3119 t_pub_site_id(j),
3120 t_supp(j),
3121 t_supp_id(j),
3122 t_supp_site(j),
3123 t_supp_site_id(j),
3124 t_supp(j),
3125 t_supp_id(j),
3126 t_supp_site(j),
3127 t_supp_site_id(j),
3128 t_pub(j),
3129 t_pub_id(j),
3130 t_pub_site(j),
3131 t_pub_site_id(j),
3132 CP_PLANNED_ORDER,
3133 l_order_type_desc,
3134 l_bucket_type_desc,
3135 t_bucket_type(j),
3136 t_item_id(j),
3137 t_master_item_name(j),
3138 t_owner_item_name(j),
3139 t_owner_item_name(j),
3140 t_supp_item_name(j),
3141 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
3142 t_owner_item_desc(j),
3143 t_owner_item_desc(j),
3144 t_supp_item_desc(j),
3145 t_uom_code(j),
3146 t_uom_code(j),
3147 t_tp_uom(j),
3148 t_key_date(j),
3149 t_ship_date(j),
3150 t_receipt_date(j),
3151 l_planned_order_qty,
3152 l_planned_order_qty,
3153 l_tp_planned_order_qty,
3154 msc_cl_refresh_s.nextval,
3155 t_pub(j),
3156 t_pub_id(j),
3157 nvl(p_user_id,-1),
3158 sysdate,
3159 nvl(p_user_id,-1),
3160 sysdate,
3161 t_proj_number(j),
3162 t_task_number(j),
3163 t_planning_gp(j),
3164 t_planner_code(j),
3165 p_version,
3166 p_designator,
3167 t_base_item_id(j),
3168 t_base_item_name(j));
3169 END IF;
3170 ----------------------------------------------------------
3171 -- RELEASED_PLANNED_ORDER
3172 ----------------------------------------------------------
3173
3174 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_RELEASED_PLANNED_ORDER);
3175 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3176 IF (t_released_qty(j) <> 0 ) THEN
3177 l_released_qty := t_released_qty(j);
3178 l_tp_released_qty := t_tp_released_qty(j);
3179 IF (t_released_qty(j) < 0 ) THEN
3180 l_released_qty := 0;
3181 l_tp_released_qty := 0;
3182 END IF;
3183 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'released qty ' || t_released_qty(j) || ' tp '
3184 -- || t_tp_released_qty(j) || 'date ' || t_key_date(j));
3185 log_message( 'released qty ' || t_released_qty(j) || ' tp ' || t_tp_released_qty(j) || 'date ' || t_key_date(j));
3186 insert into msc_sup_dem_entries (
3187 transaction_id,
3188 plan_id,
3189 sr_instance_id,
3190 publisher_name,
3191 publisher_id,
3192 publisher_site_name,
3193 publisher_site_id,
3194 customer_name,
3195 customer_id,
3196 customer_site_name,
3197 customer_site_id,
3198 supplier_name,
3199 supplier_id,
3200 supplier_site_name,
3201 supplier_site_id,
3202 ship_from_party_name,
3203 ship_from_party_id,
3204 ship_from_party_site_name,
3205 ship_from_party_site_id,
3206 ship_to_party_name,
3207 ship_to_party_id,
3208 ship_to_party_site_name,
3209 ship_to_party_site_id,
3210 publisher_order_type,
3211 publisher_order_type_desc,
3212 bucket_type_desc,
3213 bucket_type,
3214 inventory_item_id,
3215 item_name,
3216 owner_item_name,
3217 customer_item_name,
3218 supplier_item_name,
3219 item_description,
3220 owner_item_description,
3221 customer_item_description,
3222 supplier_item_description,
3223 primary_uom,
3224 uom_code,
3225 tp_uom_code,
3226 key_date,
3227 ship_date,
3228 receipt_date,
3229 quantity,
3230 primary_quantity,
3231 tp_quantity,
3232 last_refresh_number,
3233 posting_party_name,
3234 posting_party_id,
3235 created_by,
3236 creation_date,
3237 last_updated_by,
3238 last_update_date,
3239 project_number,
3240 task_number,
3241 planning_group,
3242 planner_code,
3243 version,
3244 designator,
3245 base_item_id,
3246 base_item_name
3247 ) values (
3248 msc_sup_dem_entries_s.nextval,
3249 -1,
3250 -1,
3251 t_pub(j),
3252 t_pub_id(j),
3253 t_pub_site(j),
3254 t_pub_site_id(j),
3255 t_pub(j),
3256 t_pub_id(j),
3257 t_pub_site(j),
3258 t_pub_site_id(j),
3259 t_supp(j),
3260 t_supp_id(j),
3261 t_supp_site(j),
3262 t_supp_site_id(j),
3263 t_supp(j),
3264 t_supp_id(j),
3265 t_supp_site(j),
3266 t_supp_site_id(j),
3267 t_pub(j),
3268 t_pub_id(j),
3269 t_pub_site(j),
3270 t_pub_site_id(j),
3271 CP_RELEASED_PLANNED_ORDER,
3272 l_order_type_desc,
3273 l_bucket_type_desc,
3274 t_bucket_type(j),
3275 t_item_id(j),
3276 t_master_item_name(j),
3277 t_owner_item_name(j),
3278 t_owner_item_name(j),
3279 t_supp_item_name(j),
3280 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
3281 t_owner_item_desc(j),
3282 t_owner_item_desc(j),
3283 t_supp_item_desc(j),
3284 t_uom_code(j),
3285 t_uom_code(j),
3286 t_tp_uom(j),
3287 t_key_date(j),
3288 t_ship_date(j),
3289 t_receipt_date(j),
3290 l_released_qty,
3291 l_released_qty,
3292 l_tp_released_qty,
3293 msc_cl_refresh_s.nextval,
3294 t_pub(j),
3295 t_pub_id(j),
3296 nvl(p_user_id,-1),
3297 sysdate,
3298 nvl(p_user_id,-1),
3299 sysdate,
3300 t_proj_number(j),
3301 t_task_number(j),
3302 t_planning_gp(j),
3303 t_planner_code(j),
3304 p_version,
3305 p_designator,
3306 t_base_item_id(j),
3307 t_base_item_name(j));
3308
3309 END IF;
3310 ELSIF (t_order_type(j) = PURCHASE_ORDER) THEN
3311
3312 ------------------------------------------------------
3313 -- PURCHASE_ORDER_FROM_PLAN
3314 ------------------------------------------------------
3315 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PURCHASE_ORDER_FROM_PLAN);
3316 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3317
3318 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Po ' || t_qty(j) || ' tp ' || t_tp_qty(j)
3319 --|| 'date ' || t_key_date(j));
3320 log_message( 'Po ' || t_qty(j) || ' tp ' || t_tp_qty(j) || 'date ' || t_key_date(j));
3321 insert into msc_sup_dem_entries (
3322 transaction_id,
3323 plan_id,
3324 sr_instance_id,
3325 publisher_name,
3326 publisher_id,
3327 publisher_site_name,
3328 publisher_site_id,
3329 customer_name,
3330 customer_id,
3331 customer_site_name,
3332 customer_site_id,
3333 supplier_name,
3334 supplier_id,
3335 supplier_site_name,
3336 supplier_site_id,
3337 ship_from_party_name,
3338 ship_from_party_id,
3339 ship_from_party_site_name,
3340 ship_from_party_site_id,
3341 ship_to_party_name,
3342 ship_to_party_id,
3343 ship_to_party_site_name,
3344 ship_to_party_site_id,
3345 publisher_order_type,
3346 publisher_order_type_desc,
3347 bucket_type_desc,
3348 bucket_type,
3349 inventory_item_id,
3350 item_name,
3351 owner_item_name,
3352 customer_item_name,
3353 supplier_item_name,
3354 item_description,
3355 owner_item_description,
3356 customer_item_description,
3357 supplier_item_description,
3358 primary_uom,
3359 uom_code,
3360 tp_uom_code,
3361 key_date,
3362 ship_date,
3363 receipt_date,
3364 quantity,
3365 primary_quantity,
3366 tp_quantity,
3367 last_refresh_number,
3368 posting_party_name,
3369 posting_party_id,
3370 created_by,
3371 creation_date,
3372 last_updated_by,
3373 last_update_date,
3374 project_number,
3375 task_number,
3376 planning_group,
3377 planner_code,
3378 version,
3379 designator,
3380 base_item_id,
3381 base_item_name
3382 ) values (
3383 msc_sup_dem_entries_s.nextval,
3384 -1,
3385 -1,
3386 t_pub(j),
3387 t_pub_id(j),
3388 t_pub_site(j),
3389 t_pub_site_id(j),
3390 t_pub(j),
3391 t_pub_id(j),
3392 t_pub_site(j),
3393 t_pub_site_id(j),
3394 t_supp(j),
3395 t_supp_id(j),
3396 t_supp_site(j),
3397 t_supp_site_id(j),
3398 t_supp(j),
3399 t_supp_id(j),
3400 t_supp_site(j),
3401 t_supp_site_id(j),
3402 t_pub(j),
3403 t_pub_id(j),
3404 t_pub_site(j),
3405 t_pub_site_id(j),
3406 CP_PURCHASE_ORDER_FROM_PLAN,
3407 l_order_type_desc,
3408 l_bucket_type_desc,
3409 t_bucket_type(j),
3410 t_item_id(j),
3411 t_master_item_name(j),
3412 t_owner_item_name(j),
3413 t_owner_item_name(j),
3414 t_supp_item_name(j),
3415 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
3416 t_owner_item_desc(j),
3417 t_owner_item_desc(j),
3418 t_supp_item_desc(j),
3419 t_uom_code(j),
3420 t_uom_code(j),
3421 t_tp_uom(j),
3422 t_key_date(j),
3423 t_ship_date(j),
3424 t_receipt_date(j),
3425 t_qty(j),
3426 t_qty(j),
3427 t_tp_qty(j),
3428 msc_cl_refresh_s.nextval,
3429 t_pub(j),
3430 t_pub_id(j),
3431 nvl(p_user_id,-1),
3432 sysdate,
3433 nvl(p_user_id,-1),
3434 sysdate,
3435 t_proj_number(j),
3436 t_task_number(j),
3437 t_planning_gp(j),
3438 t_planner_code(j),
3439 p_version,
3440 p_designator,
3441 t_base_item_id(j),
3442 t_base_item_name(j));
3443
3444 -- added to publish Planned Inbound Shipment (PIS) to CP as PIS
3445
3446 ELSIF (t_order_type(j) = EXPECTED_INBOUND_SHIPMENT) THEN
3447 ---------------------------------------------------------
3448 -- EXPECTED_INBOUND_SHIPMENT (PLANNED_INBOUND_SHIPMENT)
3449 ---------------------------------------------------------
3450
3451 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PLANNED_INBOUND_SHIPMENT);
3452 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3453
3454 IF ( t_planned_order_qty(j) <> 0 ) THEN
3455 l_planned_order_qty := t_planned_order_qty(j);
3456 l_tp_planned_order_qty := t_planned_order_qty(j);
3457 IF (t_planned_order_qty(j) < 0 ) THEN
3458 l_planned_order_qty := 0;
3459 l_tp_planned_order_qty := 0;
3460 END IF;
3461 log_message(' PIS ' || t_planned_order_qty(j) || ' tp ' || t_tp_planned_order_qty(j) || ' date ' || t_key_date(j));
3462
3463 insert into msc_sup_dem_entries (
3464 transaction_id,
3465 plan_id,
3466 sr_instance_id,
3467 publisher_name,
3468 publisher_id,
3469 publisher_site_name,
3470 publisher_site_id,
3471 customer_name,
3472 customer_id,
3473 customer_site_name,
3474 customer_site_id,
3475 supplier_name,
3476 supplier_id,
3477 supplier_site_name,
3478 supplier_site_id,
3479 ship_from_party_name,
3480 ship_from_party_id,
3481 ship_from_party_site_name,
3482 ship_from_party_site_id,
3483 ship_to_party_name,
3484 ship_to_party_id,
3485 ship_to_party_site_name,
3486 ship_to_party_site_id,
3487 publisher_order_type,
3488 publisher_order_type_desc,
3489 bucket_type_desc,
3490 bucket_type,
3491 inventory_item_id,
3492 item_name,
3493 owner_item_name,
3494 customer_item_name,
3495 supplier_item_name,
3496 item_description,
3497 owner_item_description,
3498 customer_item_description,
3499 supplier_item_description,
3500 primary_uom,
3501 uom_code,
3502 tp_uom_code,
3503 key_date,
3504 ship_date,
3505 receipt_date,
3506 quantity,
3507 primary_quantity,
3508 tp_quantity,
3509 last_refresh_number,
3510 posting_party_name,
3511 posting_party_id,
3512 created_by,
3513 creation_date,
3514 last_updated_by,
3515 last_update_date,
3516 project_number,
3517 task_number,
3518 planning_group,
3519 planner_code,
3520 version,
3521 designator,
3522 base_item_id,
3523 base_item_name
3524 ) values (
3525 msc_sup_dem_entries_s.nextval,
3526 -1,
3527 -1,
3528 t_pub(j),
3529 t_pub_id(j),
3530 t_pub_site(j),
3531 t_pub_site_id(j),
3532 t_pub(j),
3533 t_pub_id(j),
3534 t_pub_site(j),
3535 t_pub_site_id(j),
3536 t_supp(j),
3537 t_supp_id(j),
3538 t_supp_site(j),
3539 t_supp_site_id(j),
3540 t_supp(j),
3541 t_supp_id(j),
3542 t_supp_site(j),
3543 t_supp_site_id(j),
3544 t_pub(j),
3545 t_pub_id(j),
3546 t_pub_site(j),
3547 t_pub_site_id(j),
3548 CP_PLANNED_INBOUND_SHIPMENT,
3549 l_order_type_desc,
3550 l_bucket_type_desc,
3551 t_bucket_type(j),
3552 t_item_id(j),
3553 t_master_item_name(j),
3554 t_owner_item_name(j),
3555 t_owner_item_name(j),
3556 t_supp_item_name(j),
3557 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
3558 t_owner_item_desc(j),
3559 t_owner_item_desc(j),
3560 t_supp_item_desc(j),
3561 t_uom_code(j),
3562 t_uom_code(j),
3563 t_tp_uom(j),
3564 t_key_date(j),
3565 t_ship_date(j),
3566 t_receipt_date(j),
3567 l_planned_order_qty,
3568 l_planned_order_qty,
3569 l_tp_planned_order_qty,
3570 msc_cl_refresh_s.nextval,
3571 t_pub(j),
3572 t_pub_id(j),
3573 nvl(p_user_id,-1),
3574 sysdate,
3575 nvl(p_user_id,-1),
3576 sysdate,
3577 t_proj_number(j),
3578 t_task_number(j),
3579 t_planning_gp(j),
3580 t_planner_code(j),
3581 p_version,
3582 p_designator,
3583 t_base_item_id(j),
3584 t_base_item_name(j));
3585 END IF;
3586 ----------------------------------------------------------
3587 -- CP_RELEASED_INBOUND_SHIPMENT (qty released from PIS)
3588 ----------------------------------------------------------
3589
3590 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_RELEASED_INBOUND_SHIPMENT);
3591 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3592 IF (t_released_qty(j) <> 0 ) THEN
3593 l_released_qty := t_released_qty(j);
3594 l_tp_released_qty := t_tp_released_qty(j);
3595 IF (t_released_qty(j) < 0 ) THEN
3596 l_released_qty := 0;
3597 l_tp_released_qty := 0;
3598 END IF;
3599
3600 log_message( 'released qty from PIS ' || t_released_qty(j) || ' tp ' || t_tp_released_qty(j) || ' date ' || t_key_date(j));
3601 insert into msc_sup_dem_entries (
3602 transaction_id,
3603 plan_id,
3604 sr_instance_id,
3605 publisher_name,
3606 publisher_id,
3607 publisher_site_name,
3608 publisher_site_id,
3609 customer_name,
3610 customer_id,
3611 customer_site_name,
3612 customer_site_id,
3613 supplier_name,
3614 supplier_id,
3615 supplier_site_name,
3616 supplier_site_id,
3617 ship_from_party_name,
3618 ship_from_party_id,
3619 ship_from_party_site_name,
3620 ship_from_party_site_id,
3621 ship_to_party_name,
3622 ship_to_party_id,
3623 ship_to_party_site_name,
3624 ship_to_party_site_id,
3625 publisher_order_type,
3626 publisher_order_type_desc,
3627 bucket_type_desc,
3628 bucket_type,
3629 inventory_item_id,
3630 item_name,
3631 owner_item_name,
3632 customer_item_name,
3633 supplier_item_name,
3634 item_description,
3635 owner_item_description,
3636 customer_item_description,
3637 supplier_item_description,
3638 primary_uom,
3639 uom_code,
3640 tp_uom_code,
3641 key_date,
3642 ship_date,
3643 receipt_date,
3644 quantity,
3645 primary_quantity,
3646 tp_quantity,
3647 last_refresh_number,
3648 posting_party_name,
3649 posting_party_id,
3650 created_by,
3651 creation_date,
3652 last_updated_by,
3653 last_update_date,
3654 project_number,
3655 task_number,
3656 planning_group,
3657 planner_code,
3658 version,
3659 designator,
3660 base_item_id,
3661 base_item_name
3662 ) values (
3663 msc_sup_dem_entries_s.nextval,
3664 -1,
3665 -1,
3666 t_pub(j),
3667 t_pub_id(j),
3668 t_pub_site(j),
3669 t_pub_site_id(j),
3670 t_pub(j),
3671 t_pub_id(j),
3672 t_pub_site(j),
3673 t_pub_site_id(j),
3674 t_supp(j),
3675 t_supp_id(j),
3676 t_supp_site(j),
3677 t_supp_site_id(j),
3678 t_supp(j),
3679 t_supp_id(j),
3680 t_supp_site(j),
3681 t_supp_site_id(j),
3682 t_pub(j),
3683 t_pub_id(j),
3684 t_pub_site(j),
3685 t_pub_site_id(j),
3686 CP_RELEASED_INBOUND_SHIPMENT,
3687 l_order_type_desc,
3688 l_bucket_type_desc,
3689 t_bucket_type(j),
3690 t_item_id(j),
3691 t_master_item_name(j),
3692 t_owner_item_name(j),
3693 t_owner_item_name(j),
3694 t_supp_item_name(j),
3695 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
3696 t_owner_item_desc(j),
3697 t_owner_item_desc(j),
3698 t_supp_item_desc(j),
3699 t_uom_code(j),
3700 t_uom_code(j),
3701 t_tp_uom(j),
3702 t_key_date(j),
3703 t_ship_date(j),
3704 t_receipt_date(j),
3705 l_released_qty,
3706 l_released_qty,
3707 l_tp_released_qty,
3708 msc_cl_refresh_s.nextval,
3709 t_pub(j),
3710 t_pub_id(j),
3711 nvl(p_user_id,-1),
3712 sysdate,
3713 nvl(p_user_id,-1),
3714 sysdate,
3715 t_proj_number(j),
3716 t_task_number(j),
3717 t_planning_gp(j),
3718 t_planner_code(j),
3719 p_version,
3720 p_designator,
3721 t_base_item_id(j),
3722 t_base_item_name(j));
3723 END IF;
3724
3725 END IF;
3726
3727 /*----------------------------------------------------
3728 else
3729 ---------------------------------------------------*/
3730 ELSE
3731
3732 FND_FILE.PUT_LINE(FND_FILE.LOG, 'qty ' || t_qty(j) || ' tp ' || t_tp_qty(j)|| 'date ' || t_key_date(j) || ' Item ' || t_item_id(j) || ' base ' || t_base_item_id(j));
3733 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',ORDER_FORECAST);
3734 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3735
3736 IF (t_base_item_id(j) is null) THEN
3737 l_item_id := t_item_id(j);
3738 l_base_item_name := null;
3739 l_base_item_desc := null;
3740 ELSE
3741 l_item_id := t_base_item_id(j);
3742 l_base_item_name := t_base_item_name(j);
3743 l_base_item_desc := t_base_item_desc(j);
3744 END IF;
3745
3746 l_count := 0;
3747
3748 SELECT count(*)
3749 INTO l_count
3750 FROM msc_sup_dem_entries
3751 where publisher_id = t_pub_id(j)
3752 and publisher_site_id = t_pub_site_id(j)
3753 and supplier_id = t_supp_id(j)
3754 and supplier_site_id = t_supp_site_id(j)
3755 and inventory_item_id = l_item_id
3756 and trunc(key_date) = trunc(t_key_date(j))
3757 and publisher_order_type = ORDER_FORECAST;
3758
3759 IF l_count > 0 THEN
3760
3761 update msc_sup_dem_entries
3762 set quantity = quantity + t_qty(j),
3763 primary_quantity = primary_quantity + t_qty(j) ,
3764 tp_quantity = tp_quantity + t_tp_qty(j)
3765 where publisher_id = t_pub_id(j)
3766 and publisher_site_id = t_pub_site_id(j)
3767 and supplier_id = t_supp_id(j)
3768 and supplier_site_id = t_supp_site_id(j)
3769 and inventory_item_id = l_item_id
3770 and trunc(key_date) = trunc(t_key_date(j))
3771 and publisher_order_type = ORDER_FORECAST;
3772 ELSE
3773
3774 begin
3775 insert into msc_sup_dem_entries (
3776 transaction_id,
3777 plan_id,
3778 sr_instance_id,
3779 publisher_name,
3780 publisher_id,
3781 publisher_site_name,
3782 publisher_site_id,
3783 customer_name,
3784 customer_id,
3785 customer_site_name,
3786 customer_site_id,
3787 supplier_name,
3788 supplier_id,
3789 supplier_site_name,
3790 supplier_site_id,
3791 ship_from_party_name,
3792 ship_from_party_id,
3793 ship_from_party_site_name,
3794 ship_from_party_site_id,
3795 ship_to_party_name,
3796 ship_to_party_id,
3797 ship_to_party_site_name,
3798 ship_to_party_site_id,
3799 publisher_order_type,
3800 publisher_order_type_desc,
3801 bucket_type_desc,
3802 bucket_type,
3803 inventory_item_id,
3804 item_name,
3805 owner_item_name,
3806 customer_item_name,
3807 supplier_item_name,
3808 item_description,
3809 owner_item_description,
3810 customer_item_description,
3811 supplier_item_description,
3812 primary_uom,
3813 uom_code,
3814 tp_uom_code,
3815 key_date,
3816 ship_date,
3817 receipt_date,
3818 quantity,
3819 primary_quantity,
3820 tp_quantity,
3821 last_refresh_number,
3822 posting_party_name,
3823 posting_party_id,
3824 created_by,
3825 creation_date,
3826 last_updated_by,
3827 last_update_date,
3828 project_number,
3829 task_number,
3830 planning_group,
3831 planner_code,
3832 version,
3833 designator
3834 ) values (
3835 msc_sup_dem_entries_s.nextval,
3836 -1,
3837 -1,
3838 t_pub(j),
3839 t_pub_id(j),
3840 t_pub_site(j),
3841 t_pub_site_id(j),
3842 t_pub(j),
3843 t_pub_id(j),
3844 t_pub_site(j),
3845 t_pub_site_id(j),
3846 t_supp(j),
3847 t_supp_id(j),
3848 t_supp_site(j),
3849 t_supp_site_id(j),
3850 t_supp(j),
3851 t_supp_id(j),
3852 t_supp_site(j),
3853 t_supp_site_id(j),
3854 t_pub(j),
3855 t_pub_id(j),
3856 t_pub_site(j),
3857 t_pub_site_id(j),
3858 ORDER_FORECAST,
3859 l_order_type_desc,
3860 l_bucket_type_desc,
3861 t_bucket_type(j),
3862 l_item_id,
3863 nvl(l_base_item_name, t_master_item_name(j)),
3864 nvl(l_base_item_name, t_owner_item_name(j)),
3865 nvl(l_base_item_name, t_owner_item_name(j)),
3866 t_supp_item_name(j),
3867 nvl(l_base_item_desc, nvl(t_master_item_desc(j), t_owner_item_desc(j))),
3868 nvl(l_base_item_desc, t_owner_item_desc(j)),
3869 nvl(l_base_item_desc, t_owner_item_desc(j)),
3870 t_supp_item_desc(j),
3871 t_uom_code(j),
3872 t_uom_code(j),
3873 t_tp_uom(j),
3874 t_key_date(j),
3875 t_ship_date(j),
3876 t_receipt_date(j),
3877 t_qty(j),
3878 t_qty(j),
3879 t_tp_qty(j),
3880 msc_cl_refresh_s.nextval,
3881 t_pub(j),
3882 t_pub_id(j),
3883 nvl(p_user_id,-1),
3884 sysdate,
3885 nvl(p_user_id,-1),
3886 sysdate,
3887 t_proj_number(j),
3888 t_task_number(j),
3889 t_planning_gp(j),
3890 t_planner_code(j),
3891 p_version,
3892 p_designator);
3893 exception
3894 when others then
3895 FND_FILE.PUT_LINE(FND_FILE.LOG, 'insert failed on else statement with base item ' || sqlerrm);
3896 end;
3897 END IF;
3898 -- Check in if-else ladder for type of order and insert accordingly
3899 -- Ladder 1 -> Planned Order - insert CP Planned Order, Released Planned Order
3900 -- Ladder 2 -> Purchase Order - insert CP Purchase Order from Plan
3901 -- Ladder 3 -> Expected Inbound Shipment - insert Planned Inbound Shipment
3902 IF (t_order_type(j) = PLANNED_ORDER) THEN
3903
3904 -------------------------------------------------------
3905 -- PLANNED_ORDER
3906 ------------------------------------------------------
3907 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'PLO' || t_planned_order_qty(j) || ' tp '
3908 -- || t_tp_planned_order_qty(j) || 'date ' || t_key_date(j));
3909 log_message('PLO' || t_planned_order_qty(j) || ' tp ' || t_tp_planned_order_qty(j) || 'date ' || t_key_date(j));
3910 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PLANNED_ORDER);
3911 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
3912 IF ( t_planned_order_qty(j) <> 0 ) THEN
3913 l_planned_order_qty := t_planned_order_qty(j);
3914 l_tp_planned_order_qty := t_tp_planned_order_qty(j);
3915
3916 IF (t_planned_order_qty(j) < 0 ) THEN
3917 l_planned_order_qty := 0;
3918 l_tp_planned_order_qty := 0;
3919 END IF;
3920 insert into msc_sup_dem_entries (
3921 transaction_id,
3922 plan_id,
3923 sr_instance_id,
3924 publisher_name,
3925 publisher_id,
3926 publisher_site_name,
3927 publisher_site_id,
3928 customer_name,
3929 customer_id,
3930 customer_site_name,
3931 customer_site_id,
3932 supplier_name,
3933 supplier_id,
3934 supplier_site_name,
3935 supplier_site_id,
3936 ship_from_party_name,
3937 ship_from_party_id,
3938 ship_from_party_site_name,
3939 ship_from_party_site_id,
3940 ship_to_party_name,
3941 ship_to_party_id,
3942 ship_to_party_site_name,
3943 ship_to_party_site_id,
3944 publisher_order_type,
3945 publisher_order_type_desc,
3946 bucket_type_desc,
3947 bucket_type,
3948 inventory_item_id,
3949 item_name,
3950 owner_item_name,
3951 customer_item_name,
3952 supplier_item_name,
3953 item_description,
3954 owner_item_description,
3955 customer_item_description,
3956 supplier_item_description,
3957 primary_uom,
3958 uom_code,
3959 tp_uom_code,
3960 key_date,
3961 ship_date,
3962 receipt_date,
3963 quantity,
3964 primary_quantity,
3965 tp_quantity,
3966 last_refresh_number,
3967 posting_party_name,
3968 posting_party_id,
3969 created_by,
3970 creation_date,
3971 last_updated_by,
3972 last_update_date,
3973 project_number,
3974 task_number,
3975 planning_group,
3976 planner_code,
3977 version,
3978 designator,
3979 base_item_id,
3980 base_item_name
3981 ) values (
3982 msc_sup_dem_entries_s.nextval,
3983 -1,
3984 -1,
3985 t_pub(j),
3986 t_pub_id(j),
3987 t_pub_site(j),
3988 t_pub_site_id(j),
3989 t_pub(j),
3990 t_pub_id(j),
3991 t_pub_site(j),
3992 t_pub_site_id(j),
3993 t_supp(j),
3994 t_supp_id(j),
3995 t_supp_site(j),
3996 t_supp_site_id(j),
3997 t_supp(j),
3998 t_supp_id(j),
3999 t_supp_site(j),
4000 t_supp_site_id(j),
4001 t_pub(j),
4002 t_pub_id(j),
4003 t_pub_site(j),
4004 t_pub_site_id(j),
4005 CP_PLANNED_ORDER,
4006 l_order_type_desc,
4007 l_bucket_type_desc,
4008 t_bucket_type(j),
4009 t_item_id(j),
4010 t_master_item_name(j),
4011 t_owner_item_name(j),
4012 t_owner_item_name(j),
4013 t_supp_item_name(j),
4014 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
4015 t_owner_item_desc(j),
4016 t_owner_item_desc(j),
4017 t_supp_item_desc(j),
4018 t_uom_code(j),
4019 t_uom_code(j),
4020 t_tp_uom(j),
4021 t_key_date(j),
4022 t_ship_date(j),
4023 t_receipt_date(j),
4024 l_planned_order_qty,
4025 l_planned_order_qty,
4026 l_tp_planned_order_qty,
4027 msc_cl_refresh_s.nextval,
4028 t_pub(j),
4029 t_pub_id(j),
4030 nvl(p_user_id,-1),
4031 sysdate,
4032 nvl(p_user_id,-1),
4033 sysdate,
4034 t_proj_number(j),
4035 t_task_number(j),
4036 t_planning_gp(j),
4037 t_planner_code(j),
4038 p_version,
4039 p_designator,
4040 t_base_item_id(j),
4041 t_base_item_name(j));
4042 END IF;
4043 ----------------------------------------------------------
4044 -- RELEASED_PLANNED_ORDER
4045 ----------------------------------------------------------
4046
4047 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_RELEASED_PLANNED_ORDER);
4048 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4049 IF (t_released_qty(j) <> 0 ) THEN
4050 l_released_qty := t_released_qty(j);
4051 l_tp_released_qty := t_tp_released_qty(j);
4052 IF (t_released_qty(j) < 0 ) THEN
4053 l_released_qty := 0;
4054 l_tp_released_qty := 0;
4055 END IF;
4056 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'released qty ' || t_released_qty(j) || ' tp '
4057 -- || t_tp_released_qty(j) || 'date ' || t_key_date(j));
4058 log_message( 'released qty ' || t_released_qty(j) || ' tp ' || t_tp_released_qty(j) || 'date ' || t_key_date(j));
4059 insert into msc_sup_dem_entries (
4060 transaction_id,
4061 plan_id,
4062 sr_instance_id,
4063 publisher_name,
4064 publisher_id,
4065 publisher_site_name,
4066 publisher_site_id,
4067 customer_name,
4068 customer_id,
4069 customer_site_name,
4070 customer_site_id,
4071 supplier_name,
4072 supplier_id,
4073 supplier_site_name,
4074 supplier_site_id,
4075 ship_from_party_name,
4076 ship_from_party_id,
4077 ship_from_party_site_name,
4078 ship_from_party_site_id,
4079 ship_to_party_name,
4080 ship_to_party_id,
4081 ship_to_party_site_name,
4082 ship_to_party_site_id,
4083 publisher_order_type,
4084 publisher_order_type_desc,
4085 bucket_type_desc,
4086 bucket_type,
4087 inventory_item_id,
4088 item_name,
4089 owner_item_name,
4090 customer_item_name,
4091 supplier_item_name,
4092 item_description,
4093 owner_item_description,
4094 customer_item_description,
4095 supplier_item_description,
4096 primary_uom,
4097 uom_code,
4098 tp_uom_code,
4099 key_date,
4100 ship_date,
4101 receipt_date,
4102 quantity,
4103 primary_quantity,
4104 tp_quantity,
4105 last_refresh_number,
4106 posting_party_name,
4107 posting_party_id,
4108 created_by,
4109 creation_date,
4110 last_updated_by,
4111 last_update_date,
4112 project_number,
4113 task_number,
4114 planning_group,
4115 planner_code,
4116 version,
4117 designator,
4118 base_item_id,
4119 base_item_name
4120 ) values (
4121 msc_sup_dem_entries_s.nextval,
4122 -1,
4123 -1,
4124 t_pub(j),
4125 t_pub_id(j),
4126 t_pub_site(j),
4127 t_pub_site_id(j),
4128 t_pub(j),
4129 t_pub_id(j),
4130 t_pub_site(j),
4131 t_pub_site_id(j),
4132 t_supp(j),
4133 t_supp_id(j),
4134 t_supp_site(j),
4135 t_supp_site_id(j),
4136 t_supp(j),
4137 t_supp_id(j),
4138 t_supp_site(j),
4139 t_supp_site_id(j),
4140 t_pub(j),
4141 t_pub_id(j),
4142 t_pub_site(j),
4143 t_pub_site_id(j),
4144 CP_RELEASED_PLANNED_ORDER,
4145 l_order_type_desc,
4146 l_bucket_type_desc,
4147 t_bucket_type(j),
4148 t_item_id(j),
4149 t_master_item_name(j),
4150 t_owner_item_name(j),
4151 t_owner_item_name(j),
4152 t_supp_item_name(j),
4153 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
4154 t_owner_item_desc(j),
4155 t_owner_item_desc(j),
4156 t_supp_item_desc(j),
4157 t_uom_code(j),
4158 t_uom_code(j),
4159 t_tp_uom(j),
4160 t_key_date(j),
4161 t_ship_date(j),
4162 t_receipt_date(j),
4163 t_released_qty(j),
4164 t_released_qty(j),
4165 t_tp_released_qty(j),
4166 msc_cl_refresh_s.nextval,
4167 t_pub(j),
4168 t_pub_id(j),
4169 nvl(p_user_id,-1),
4170 sysdate,
4171 nvl(p_user_id,-1),
4172 sysdate,
4173 t_proj_number(j),
4174 t_task_number(j),
4175 t_planning_gp(j),
4176 t_planner_code(j),
4177 p_version,
4178 p_designator,
4179 t_base_item_id(j),
4180 t_base_item_name(j));
4181
4182 END IF;
4183 ELSIF (t_order_type(j) = PURCHASE_ORDER) THEN
4184
4185 ------------------------------------------------------
4186 -- PURCHASE_ORDER_FROM_PLAN
4187 ------------------------------------------------------
4188 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PURCHASE_ORDER_FROM_PLAN);
4189 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4190 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Po ' || t_qty(j) || ' tp ' || t_tp_qty(j)
4191 --|| 'date ' || t_key_date(j));
4192 log_message( 'Po ' || t_qty(j) || ' tp ' || t_tp_qty(j) || 'date ' || t_key_date(j));
4193 insert into msc_sup_dem_entries (
4194 transaction_id,
4195 plan_id,
4196 sr_instance_id,
4197 publisher_name,
4198 publisher_id,
4199 publisher_site_name,
4200 publisher_site_id,
4201 customer_name,
4202 customer_id,
4203 customer_site_name,
4204 customer_site_id,
4205 supplier_name,
4206 supplier_id,
4207 supplier_site_name,
4208 supplier_site_id,
4209 ship_from_party_name,
4210 ship_from_party_id,
4211 ship_from_party_site_name,
4212 ship_from_party_site_id,
4213 ship_to_party_name,
4214 ship_to_party_id,
4215 ship_to_party_site_name,
4216 ship_to_party_site_id,
4217 publisher_order_type,
4218 publisher_order_type_desc,
4219 bucket_type_desc,
4220 bucket_type,
4221 inventory_item_id,
4222 item_name,
4223 owner_item_name,
4224 customer_item_name,
4225 supplier_item_name,
4226 item_description,
4227 owner_item_description,
4228 customer_item_description,
4229 supplier_item_description,
4230 primary_uom,
4231 uom_code,
4232 tp_uom_code,
4233 key_date,
4234 ship_date,
4235 receipt_date,
4236 quantity,
4237 primary_quantity,
4238 tp_quantity,
4239 last_refresh_number,
4240 posting_party_name,
4241 posting_party_id,
4242 created_by,
4243 creation_date,
4244 last_updated_by,
4245 last_update_date,
4246 project_number,
4247 task_number,
4248 planning_group,
4249 planner_code,
4250 version,
4251 designator,
4252 base_item_id,
4253 base_item_name
4254 ) values (
4255 msc_sup_dem_entries_s.nextval,
4256 -1,
4257 -1,
4258 t_pub(j),
4259 t_pub_id(j),
4260 t_pub_site(j),
4261 t_pub_site_id(j),
4262 t_pub(j),
4263 t_pub_id(j),
4264 t_pub_site(j),
4265 t_pub_site_id(j),
4266 t_supp(j),
4267 t_supp_id(j),
4268 t_supp_site(j),
4269 t_supp_site_id(j),
4270 t_supp(j),
4271 t_supp_id(j),
4272 t_supp_site(j),
4273 t_supp_site_id(j),
4274 t_pub(j),
4275 t_pub_id(j),
4276 t_pub_site(j),
4277 t_pub_site_id(j),
4278 CP_PURCHASE_ORDER_FROM_PLAN,
4279 l_order_type_desc,
4280 l_bucket_type_desc,
4281 t_bucket_type(j),
4282 t_item_id(j),
4283 t_master_item_name(j),
4284 t_owner_item_name(j),
4285 t_owner_item_name(j),
4286 t_supp_item_name(j),
4287 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
4288 t_owner_item_desc(j),
4289 t_owner_item_desc(j),
4290 t_supp_item_desc(j),
4291 t_uom_code(j),
4292 t_uom_code(j),
4293 t_tp_uom(j),
4294 t_key_date(j),
4295 t_ship_date(j),
4296 t_receipt_date(j),
4297 t_qty(j),
4298 t_qty(j),
4299 t_tp_qty(j),
4300 msc_cl_refresh_s.nextval,
4301 t_pub(j),
4302 t_pub_id(j),
4303 nvl(p_user_id,-1),
4304 sysdate,
4305 nvl(p_user_id,-1),
4306 sysdate,
4307 t_proj_number(j),
4308 t_task_number(j),
4309 t_planning_gp(j),
4310 t_planner_code(j),
4311 p_version,
4312 p_designator,
4313 t_base_item_id(j),
4314 t_base_item_name(j));
4315
4316 -- added to publish PIS to CP as PIS
4317
4318 ELSIF (t_order_type(j) = EXPECTED_INBOUND_SHIPMENT) THEN
4319
4320 ---------------------------------------------------------
4321 -- EXPECTED_INBOUND_SHIPMENT (PLANNED_INBOUND_SHIPMENT)
4322 ---------------------------------------------------------
4323
4324 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_PLANNED_INBOUND_SHIPMENT);
4325 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4326 IF ( t_planned_order_qty(j) <> 0 ) THEN
4327 l_planned_order_qty := t_planned_order_qty(j);
4328 l_tp_planned_order_qty := t_tp_planned_order_qty(j);
4329
4330 IF (t_planned_order_qty(j) < 0 ) THEN
4331 l_planned_order_qty := 0;
4332 l_tp_planned_order_qty := 0;
4333 END IF;
4334 log_message('PIS ' || t_planned_order_qty(j) || ' tp ' || t_tp_planned_order_qty(j) || ' date ' || t_key_date(j));
4335
4336 insert into msc_sup_dem_entries (
4337 transaction_id,
4338 plan_id,
4339 sr_instance_id,
4340 publisher_name,
4341 publisher_id,
4342 publisher_site_name,
4343 publisher_site_id,
4344 customer_name,
4345 customer_id,
4346 customer_site_name,
4347 customer_site_id,
4348 supplier_name,
4349 supplier_id,
4350 supplier_site_name,
4351 supplier_site_id,
4352 ship_from_party_name,
4353 ship_from_party_id,
4354 ship_from_party_site_name,
4355 ship_from_party_site_id,
4356 ship_to_party_name,
4357 ship_to_party_id,
4358 ship_to_party_site_name,
4359 ship_to_party_site_id,
4360 publisher_order_type,
4361 publisher_order_type_desc,
4362 bucket_type_desc,
4363 bucket_type,
4364 inventory_item_id,
4365 item_name,
4366 owner_item_name,
4367 customer_item_name,
4368 supplier_item_name,
4369 item_description,
4370 owner_item_description,
4371 customer_item_description,
4372 supplier_item_description,
4373 primary_uom,
4374 uom_code,
4375 tp_uom_code,
4376 key_date,
4377 ship_date,
4378 receipt_date,
4379 quantity,
4380 primary_quantity,
4381 tp_quantity,
4382 last_refresh_number,
4383 posting_party_name,
4384 posting_party_id,
4385 created_by,
4386 creation_date,
4387 last_updated_by,
4388 last_update_date,
4389 project_number,
4390 task_number,
4391 planning_group,
4392 planner_code,
4393 version,
4394 designator,
4395 base_item_id,
4396 base_item_name
4397 ) values (
4398 msc_sup_dem_entries_s.nextval,
4399 -1,
4400 -1,
4401 t_pub(j),
4402 t_pub_id(j),
4403 t_pub_site(j),
4404 t_pub_site_id(j),
4405 t_pub(j),
4406 t_pub_id(j),
4407 t_pub_site(j),
4408 t_pub_site_id(j),
4409 t_supp(j),
4410 t_supp_id(j),
4411 t_supp_site(j),
4412 t_supp_site_id(j),
4413 t_supp(j),
4414 t_supp_id(j),
4415 t_supp_site(j),
4416 t_supp_site_id(j),
4417 t_pub(j),
4418 t_pub_id(j),
4419 t_pub_site(j),
4420 t_pub_site_id(j),
4421 CP_PLANNED_INBOUND_SHIPMENT,
4422 l_order_type_desc,
4423 l_bucket_type_desc,
4424 t_bucket_type(j),
4425 t_item_id(j),
4426 t_master_item_name(j),
4427 t_owner_item_name(j),
4428 t_owner_item_name(j),
4429 t_supp_item_name(j),
4430 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
4431 t_owner_item_desc(j),
4432 t_owner_item_desc(j),
4433 t_supp_item_desc(j),
4434 t_uom_code(j),
4435 t_uom_code(j),
4436 t_tp_uom(j),
4437 t_key_date(j),
4438 t_ship_date(j),
4439 t_receipt_date(j),
4440 l_planned_order_qty,
4441 l_planned_order_qty,
4442 l_tp_planned_order_qty,
4443 msc_cl_refresh_s.nextval,
4444 t_pub(j),
4445 t_pub_id(j),
4446 nvl(p_user_id,-1),
4447 sysdate,
4448 nvl(p_user_id,-1),
4449 sysdate,
4450 t_proj_number(j),
4451 t_task_number(j),
4452 t_planning_gp(j),
4453 t_planner_code(j),
4454 p_version,
4455 p_designator,
4456 t_base_item_id(j),
4457 t_base_item_name(j));
4458 END IF;
4459 ----------------------------------------------------------
4460 -- CP_RELEASED_INBOUND_SHIPMENT (qty released from PIS)
4461 ----------------------------------------------------------
4462
4463 l_order_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',CP_RELEASED_INBOUND_SHIPMENT);
4464 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4465 IF (t_released_qty(j) <> 0 ) THEN
4466 l_released_qty := t_released_qty(j);
4467 l_tp_released_qty := t_tp_released_qty(j);
4468 IF (t_released_qty(j) < 0 ) THEN
4469 l_released_qty := 0;
4470 l_tp_released_qty := 0;
4471 END IF;
4472 log_message( 'released qty from PIS ' || t_released_qty(j) || ' tp ' || t_tp_released_qty(j) || 'date ' || t_key_date(j));
4473
4474 insert into msc_sup_dem_entries (
4475 transaction_id,
4476 plan_id,
4477 sr_instance_id,
4478 publisher_name,
4479 publisher_id,
4480 publisher_site_name,
4481 publisher_site_id,
4482 customer_name,
4483 customer_id,
4484 customer_site_name,
4485 customer_site_id,
4486 supplier_name,
4487 supplier_id,
4488 supplier_site_name,
4489 supplier_site_id,
4490 ship_from_party_name,
4491 ship_from_party_id,
4492 ship_from_party_site_name,
4493 ship_from_party_site_id,
4494 ship_to_party_name,
4495 ship_to_party_id,
4496 ship_to_party_site_name,
4497 ship_to_party_site_id,
4498 publisher_order_type,
4499 publisher_order_type_desc,
4500 bucket_type_desc,
4501 bucket_type,
4502 inventory_item_id,
4503 item_name,
4504 owner_item_name,
4505 customer_item_name,
4506 supplier_item_name,
4507 item_description,
4508 owner_item_description,
4509 customer_item_description,
4510 supplier_item_description,
4511 primary_uom,
4512 uom_code,
4513 tp_uom_code,
4514 key_date,
4515 ship_date,
4516 receipt_date,
4517 quantity,
4518 primary_quantity,
4519 tp_quantity,
4520 last_refresh_number,
4521 posting_party_name,
4522 posting_party_id,
4523 created_by,
4524 creation_date,
4525 last_updated_by,
4526 last_update_date,
4527 project_number,
4528 task_number,
4529 planning_group,
4530 planner_code,
4531 version,
4532 designator,
4533 base_item_id,
4534 base_item_name
4535 ) values (
4536 msc_sup_dem_entries_s.nextval,
4537 -1,
4538 -1,
4539 t_pub(j),
4540 t_pub_id(j),
4541 t_pub_site(j),
4542 t_pub_site_id(j),
4543 t_pub(j),
4544 t_pub_id(j),
4545 t_pub_site(j),
4546 t_pub_site_id(j),
4547 t_supp(j),
4548 t_supp_id(j),
4549 t_supp_site(j),
4550 t_supp_site_id(j),
4551 t_supp(j),
4552 t_supp_id(j),
4553 t_supp_site(j),
4554 t_supp_site_id(j),
4555 t_pub(j),
4556 t_pub_id(j),
4557 t_pub_site(j),
4558 t_pub_site_id(j),
4559 CP_RELEASED_INBOUND_SHIPMENT,
4560 l_order_type_desc,
4561 l_bucket_type_desc,
4562 t_bucket_type(j),
4563 t_item_id(j),
4564 t_master_item_name(j),
4565 t_owner_item_name(j),
4566 t_owner_item_name(j),
4567 t_supp_item_name(j),
4568 nvl(t_master_item_desc(j), t_owner_item_desc(j)),
4569 t_owner_item_desc(j),
4570 t_owner_item_desc(j),
4571 t_supp_item_desc(j),
4572 t_uom_code(j),
4573 t_uom_code(j),
4574 t_tp_uom(j),
4575 t_key_date(j),
4576 t_ship_date(j),
4577 t_receipt_date(j),
4578 t_released_qty(j),
4579 t_released_qty(j),
4580 t_tp_released_qty(j),
4581 msc_cl_refresh_s.nextval,
4582 t_pub(j),
4583 t_pub_id(j),
4584 nvl(p_user_id,-1),
4585 sysdate,
4586 nvl(p_user_id,-1),
4587 sysdate,
4588 t_proj_number(j),
4589 t_task_number(j),
4590 t_planning_gp(j),
4591 t_planner_code(j),
4592 p_version,
4593 p_designator,
4594 t_base_item_id(j),
4595 t_base_item_name(j));
4596 END IF;
4597
4598 END IF;
4599
4600
4601 end if;
4602 END LOOP;
4603
4604 -- break into 2 queries for performance purpose
4605 FORALL j in t_pub_id.FIRST..t_pub_id.LAST
4606 update msc_sup_dem_entries
4607 set quantity = 0, tp_quantity = 0, primary_quantity = 0
4608 where publisher_order_type = CP_PLANNED_ORDER
4609 and publisher_id = t_pub_id(j)
4610 and publisher_site_id = t_pub_site_id(j)
4611 and supplier_id = t_supp_id(j)
4612 and supplier_site_id = t_supp_site_id(j)
4613 and inventory_item_id = t_item_id(j)
4614 and quantity < 0;
4615
4616
4617 FORALL j in t_pub_id.FIRST..t_pub_id.LAST
4618 update msc_sup_dem_entries
4619 set quantity = 0, tp_quantity = 0, primary_quantity = 0
4620 where publisher_order_type = CP_RELEASED_PLANNED_ORDER
4621 and publisher_id = t_pub_id(j)
4622 and publisher_site_id = t_pub_site_id(j)
4623 and supplier_id = t_supp_id(j)
4624 and supplier_site_id = t_supp_site_id(j)
4625 and inventory_item_id = t_item_id(j)
4626 and quantity < 0;
4627
4628 -- added for PIS
4629
4630 FORALL j in t_pub_id.FIRST..t_pub_id.LAST
4631 update msc_sup_dem_entries
4632 set quantity = 0, tp_quantity = 0, primary_quantity = 0
4633 where publisher_order_type = CP_PLANNED_INBOUND_SHIPMENT
4634 and publisher_id = t_pub_id(j)
4635 and publisher_site_id = t_pub_site_id(j)
4636 and supplier_id = t_supp_id(j)
4637 and supplier_site_id = t_supp_site_id(j)
4638 and inventory_item_id = t_item_id(j)
4639 and quantity < 0;
4640
4641
4642 FORALL j in t_pub_id.FIRST..t_pub_id.LAST
4643 update msc_sup_dem_entries
4644 set quantity = 0, tp_quantity = 0, primary_quantity = 0
4645 where publisher_order_type = CP_RELEASED_INBOUND_SHIPMENT
4646 and publisher_id = t_pub_id(j)
4647 and publisher_site_id = t_pub_site_id(j)
4648 and supplier_id = t_supp_id(j)
4649 and supplier_site_id = t_supp_site_id(j)
4650 and inventory_item_id = t_item_id(j)
4651 and quantity < 0;
4652
4653 ELSE
4654 l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',p_language_code) || ': ' || 0;
4655 log_message(l_log_message);
4656 end if;
4657 end if;
4658 EXCEPTION
4659 when others then
4660 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in insert_sup_dem_entries: ' || sqlerrm);
4661 log_message('Error ' || sqlerrm);
4662 --dbms_output.put_line('error ' || sqlerrm);
4663 END insert_into_sup_dem;
4664
4665 /*=====================================================================================
4666 | CP-SPP Integration |
4667 | Planned External Repair Order will be published as Order Forecast |
4668 | Planned_Order_Def + ISO_Def will be publish as Returns Forecast |
4669 | If user specify Publish Defective Outbound Shipment = YES , only then publish DOS |
4670 | ISO_Def = Publish As Defective Outbound Shipment |
4671 =====================================================================================*/
4672
4673 PROCEDURE insert_into_sup_dem_rf_dos (
4674 t_pub IN companyNameList,
4675 t_pub_id IN numberList,
4676 t_pub_site IN companySiteList,
4677 t_pub_site_id IN numberList,
4678 t_item_id IN numberList,
4679 t_order_type IN numberList,
4680 t_qty IN numberList,
4681 t_planned_order_qty IN numberList,
4682 t_released_qty IN numberList,
4683 t_supp IN companyNameList,
4684 t_supp_id IN numberList,
4685 t_supp_site IN companySiteList,
4686 t_supp_site_id IN numberList,
4687 t_owner_item_name IN itemNameList,
4688 t_owner_item_desc IN itemDescList,
4689 t_base_item_id IN numberList,
4690 t_base_item_name IN itemNameList,
4691 t_base_item_desc IN itemDescList,
4692 t_proj_number IN numberList,
4693 t_task_number IN numberList,
4694 t_planning_gp IN planningGroupList,
4695 t_uom_code IN itemUomList,
4696 t_planner_code IN plannerCodeList,
4697 t_bucket_type IN numberList,
4698 t_key_date IN dateList,
4699 t_ship_date IN dateList,
4700 t_receipt_date IN dateList,
4701 t_order_num IN orderNumList, -- bug#7310179
4702 t_line_num IN lineNumList, -- bug#7310179
4703 t_master_item_name IN itemNameList,
4704 t_master_item_desc IN itemDescList,
4705 t_supp_item_name IN itemNameList,
4706 t_supp_item_desc IN itemDescList,
4707 t_tp_uom IN itemUomList,
4708 t_tp_qty IN numberList,
4709 t_tp_planned_order_qty IN numberList,
4710 t_tp_released_qty IN numberList,
4711 p_version IN varchar2,
4712 p_designator IN varchar2,
4713 p_user_id IN number,
4714 p_language_code IN varchar2,
4715 p_publish_dos IN number
4716 ) IS
4717
4718 l_qty NUMBER;
4719 l_tp_qty NUMBER;
4720 l_order_type_desc varchar2(80);
4721 l_bucket_type_desc varchar2(80);
4722
4723 BEGIN
4724 -- RETURNS FORECAST
4725 -- Key_Date = Receipt_date for INTRANSIT_SHIPMENT,INTRANSIT RECEIPT AND PLANNER_TRANSFER_DEF
4726 -- and Ship_Date for ISO_DEF
4727 -- DEFECTIVE OUTBOUND SHIPMENT
4728 -- Key_Date = Receipt_Date for INTRANSIT_SHIPMENT, INTRANSIT_RECEIPT and ship_date for ISO_DEF
4729 -- Note : Ship_Date is null for INTRANSIT_SHIPMENT and INTRANSIT_RECEIPT if order is firmed.
4730
4731 IF t_pub_id is not null and t_pub_id.COUNT > 0 THEN
4732 FOR j in 1..t_pub_id.COUNT LOOP
4733
4734 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Type : ' ||t_order_type(j) || '/qty : ' || t_qty(j)
4735 || '/date : ' || t_key_date(j) || '/Item : ' || t_item_id(j)
4736 || '/base :' || t_base_item_id(j));
4737
4738 IF (t_order_type(j) IN (PLANNED_TRANSFER_DEF, ISO_DEF, INTRANSIT_SHIPMENT_DEF, INTRANSIT_RECEIPT_DEF) ) THEN -- RETURNS FORECAST
4739 IF ( t_qty(j) <> 0 ) THEN
4740 l_qty := t_qty(j);
4741 l_tp_qty := t_tp_qty(j);
4742 --=============
4743 IF (t_qty(j) < 0 ) THEN
4744 l_qty := 0;
4745 l_tp_qty := 0;
4746 END IF;
4747 --=============
4748 update msc_sup_dem_entries
4749 set quantity = quantity + l_qty,
4750 primary_quantity = primary_quantity + l_qty,
4751 tp_quantity = tp_quantity + l_tp_qty
4752 where publisher_id = t_pub_id(j)
4753 and publisher_site_id = t_pub_site_id(j)
4754 and supplier_id = t_supp_id(j)
4755 and supplier_site_id = t_supp_site_id(j)
4756 and inventory_item_id = t_item_id(j)
4757 and trunc(key_date) = DECODE(t_order_type(j),ISO_DEF,trunc(t_receipt_date(j)),trunc(t_key_date(j)))
4758 and publisher_order_type = RETURNS_FORECAST ;
4759
4760 IF SQL%NOTFOUND THEN
4761 l_order_type_desc:=MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',RETURNS_FORECAST);
4762 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4763 INSERT INTO msc_sup_dem_entries
4764 (transaction_id,
4765 plan_id,
4766 sr_instance_id,
4767 publisher_name,
4768 publisher_id,
4769 publisher_site_name,
4770 publisher_site_id,
4771 customer_name,
4772 customer_id,
4773 customer_site_name,
4774 customer_site_id,
4775 supplier_name,
4776 supplier_id,
4777 supplier_site_name,
4778 supplier_site_id,
4779 ship_from_party_name,
4780 ship_from_party_id,
4781 ship_from_party_site_name,
4782 ship_from_party_site_id,
4783 ship_to_party_name,
4784 ship_to_party_id,
4785 ship_to_party_site_name,
4786 ship_to_party_site_id,
4787 publisher_order_type,
4788 publisher_order_type_desc,
4789 bucket_type_desc,
4790 bucket_type,
4791 inventory_item_id,
4792 item_name,
4793 owner_item_name,
4794 customer_item_name,
4795 supplier_item_name,
4796 item_description,
4797 owner_item_description,
4798 customer_item_description,
4799 supplier_item_description,
4800 primary_uom,
4801 uom_code,
4802 tp_uom_code,
4803 key_date,
4804 ship_date,
4805 receipt_date,
4806 quantity,
4807 primary_quantity,
4808 tp_quantity,
4809 last_refresh_number,
4810 posting_party_name,
4811 posting_party_id,
4812 created_by,
4813 creation_date,
4814 last_updated_by,
4815 last_update_date,
4816 project_number,
4817 task_number,
4818 planning_group,
4819 planner_code,
4820 version,
4821 designator,
4822 base_item_id,
4823 base_item_name)
4824 VALUES(
4825 msc_sup_dem_entries_s.nextval,
4826 -1,
4827 -1,
4828 t_pub(j),
4829 t_pub_id(j),
4830 t_pub_site(j),
4831 t_pub_site_id(j),
4832 t_pub(j),
4833 t_pub_id(j),
4834 t_pub_site(j),
4835 t_pub_site_id(j),
4836 t_supp(j),
4837 t_supp_id(j),
4838 t_supp_site(j),
4839 t_supp_site_id(j),
4840 t_supp(j),
4841 t_supp_id(j),
4842 t_supp_site(j),
4843 t_supp_site_id(j),
4844 t_pub(j),
4845 t_pub_id(j),
4846 t_pub_site(j),
4847 t_pub_site_id(j),
4848 RETURNS_FORECAST,
4849 l_order_type_desc,
4850 l_bucket_type_desc,
4851 t_bucket_type(j),
4852 t_item_id(j),
4853 t_master_item_name(j),
4854 t_owner_item_name(j),
4855 t_owner_item_name(j),
4856 t_supp_item_name(j),
4857 nvl(t_master_item_desc(j),
4858 t_owner_item_desc(j)),
4859 t_owner_item_desc(j),
4860 t_owner_item_desc(j),
4861 t_supp_item_desc(j),
4862 t_uom_code(j),
4863 t_uom_code(j),
4864 t_tp_uom(j),
4865 DECODE(t_order_type(j),ISO_DEF,trunc(t_receipt_date(j)),trunc(t_key_date(j))),
4866 t_ship_date(j),
4867 t_receipt_date(j),
4868 l_qty,
4869 l_qty,
4870 l_tp_qty,
4871 msc_cl_refresh_s.nextval,
4872 t_pub(j),
4873 t_pub_id(j),
4874 nvl(p_user_id, -1),
4875 sysdate,
4876 nvl(p_user_id, -1),
4877 sysdate,
4878 t_proj_number(j),
4879 t_task_number(j),
4880 t_planning_gp(j),
4881 t_planner_code(j),
4882 p_version,
4883 p_designator,
4884 t_base_item_id(j),
4885 t_base_item_name(j));
4886 END IF; -- IF SQL%NOTFOUND
4887
4888 -- Publish DOS Start (only when Publish Defective Outbound Shipment = YES in MSCXPO)
4889 IF (p_publish_dos = 1 AND ( t_order_type(j) IN (ISO_DEF, INTRANSIT_SHIPMENT_DEF, INTRANSIT_RECEIPT_DEF))) THEN
4890
4891 update msc_sup_dem_entries
4892 set quantity = quantity + l_qty,
4893 primary_quantity = primary_quantity + l_qty,
4894 tp_quantity = tp_quantity + l_tp_qty
4895 where publisher_id = t_pub_id(j)
4896 and publisher_site_id = t_pub_site_id(j)
4897 and supplier_id = t_supp_id(j)
4898 and supplier_site_id = t_supp_site_id(j)
4899 and inventory_item_id = t_item_id(j)
4900 and trunc(key_date) = trunc(t_key_date(j))
4901 and order_number = t_order_num(j) -- bug#7310179
4902 and NVL(line_number,G_NULL_STRING) = NVL(t_line_num(j),G_NULL_STRING) -- bug#7310179
4903 and publisher_order_type = DEFECTIVE_OUTBOUND_SHIPMENT;
4904
4905 IF SQL%NOTFOUND THEN
4906 l_order_type_desc:=MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_ORDER_TYPE',DEFECTIVE_OUTBOUND_SHIPMENT);
4907 l_bucket_type_desc := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_BUCKET_TYPE',t_bucket_type(j));
4908 INSERT INTO msc_sup_dem_entries
4909 (transaction_id,
4910 plan_id,
4911 sr_instance_id,
4912 publisher_name,
4913 publisher_id,
4914 publisher_site_name,
4915 publisher_site_id,
4916 customer_name,
4917 customer_id,
4918 customer_site_name,
4919 customer_site_id,
4920 supplier_name,
4921 supplier_id,
4922 supplier_site_name,
4923 supplier_site_id,
4924 ship_from_party_name,
4925 ship_from_party_id,
4926 ship_from_party_site_name,
4927 ship_from_party_site_id,
4928 ship_to_party_name,
4929 ship_to_party_id,
4930 ship_to_party_site_name,
4931 ship_to_party_site_id,
4932 publisher_order_type,
4933 publisher_order_type_desc,
4934 bucket_type_desc,
4935 bucket_type,
4936 inventory_item_id,
4937 item_name,
4938 owner_item_name,
4939 customer_item_name,
4940 supplier_item_name,
4941 item_description,
4942 owner_item_description,
4943 customer_item_description,
4944 supplier_item_description,
4945 primary_uom,
4946 uom_code,
4947 tp_uom_code,
4948 key_date,
4949 ship_date,
4950 receipt_date,
4951 quantity,
4952 primary_quantity,
4953 tp_quantity,
4954 last_refresh_number,
4955 posting_party_name,
4956 posting_party_id,
4957 created_by,
4958 creation_date,
4959 last_updated_by,
4960 last_update_date,
4961 order_number, -- bug#7310179
4962 line_number, -- bug#7310179
4963 project_number,
4964 task_number,
4965 planning_group,
4966 planner_code,
4967 version,
4968 designator,
4969 base_item_id,
4970 base_item_name)
4971 VALUES(
4972 msc_sup_dem_entries_s.nextval,
4973 -1,
4974 -1,
4975 t_pub(j),
4976 t_pub_id(j),
4977 t_pub_site(j),
4978 t_pub_site_id(j),
4979 t_pub(j),
4980 t_pub_id(j),
4981 t_pub_site(j),
4982 t_pub_site_id(j),
4983 t_supp(j),
4984 t_supp_id(j),
4985 t_supp_site(j),
4986 t_supp_site_id(j),
4987 t_supp(j),
4988 t_supp_id(j),
4989 t_supp_site(j),
4990 t_supp_site_id(j),
4991 t_pub(j),
4992 t_pub_id(j),
4993 t_pub_site(j),
4994 t_pub_site_id(j),
4995 DEFECTIVE_OUTBOUND_SHIPMENT,
4996 l_order_type_desc,
4997 l_bucket_type_desc,
4998 t_bucket_type(j),
4999 t_item_id(j),
5000 t_master_item_name(j),
5001 t_owner_item_name(j),
5002 t_owner_item_name(j),
5003 t_supp_item_name(j),
5004 nvl(t_master_item_desc(j),
5005 t_owner_item_desc(j)),
5006 t_owner_item_desc(j),
5007 t_owner_item_desc(j),
5008 t_supp_item_desc(j),
5009 t_uom_code(j),
5010 t_uom_code(j),
5011 t_tp_uom(j),
5012 t_key_date(j),
5013 t_ship_date(j),
5014 t_receipt_date(j),
5015 l_qty,
5016 l_qty,
5017 l_tp_qty,
5018 msc_cl_refresh_s.nextval,
5019 t_pub(j),
5020 t_pub_id(j),
5021 nvl(p_user_id, -1),
5022 sysdate,
5023 nvl(p_user_id, -1),
5024 sysdate,
5025 t_order_num(j), -- bug#7310179
5026 t_line_num(j), -- bug#7310179
5027 t_proj_number(j),
5028 t_task_number(j),
5029 t_planning_gp(j),
5030 t_planner_code(j),
5031 p_version,
5032 p_designator,
5033 t_base_item_id(j),
5034 t_base_item_name(j));
5035 END IF; -- IF SQL%NOTFOUND
5036 END IF; -- IF p_publish_dos = 1
5037 --Publish DOS End
5038 END IF; -- IF t_qty <> 0
5039 END IF; -- RETURNS FORECAST
5040
5041 END LOOP; -- end FOR LOOP
5042 END IF ; --t_pub_id.COUNT > 0
5043
5044 END insert_into_sup_dem_rf_dos; -- end of procedure insert_into_sup_dem_rf_dos
5045
5046
5047
5048 PROCEDURE delete_old_forecast(
5049 p_plan_id in number,
5050 p_org_id in number,
5051 p_sr_instance_id in number,
5052 p_planner_code in varchar2,
5053 p_abc_class in varchar2,
5054 p_item_id in number,
5055 p_planning_gp in varchar2,
5056 p_project_id in number,
5057 p_task_id in number,
5058 p_supplier_id in number,
5059 p_supplier_site_id in number,
5060 p_horizon_start in date,
5061 p_horizon_end in date,
5062 p_overwrite in number
5063 ) IS
5064 --t_publisher_site_id numberList;
5065 l_supplier_id number;
5066 l_supplier_site_id number;
5067 l_row number;
5068
5069 BEGIN
5070 log_message('In delete_old_forecast');
5071
5072 if p_supplier_id is not null then
5073 BEGIN
5074 select c.company_id
5075 into l_supplier_id
5076 from msc_trading_partner_maps m,
5077 msc_company_relationships r,
5078 msc_companies c
5079 where m.tp_key = p_supplier_id and
5080 m.map_type = 1 and
5081 m.company_key = r.relationship_id and
5082 r.relationship_type = 2 and
5083 r.subject_id = 1 and
5084 c.company_id = r.object_id;
5085 EXCEPTION
5086 WHEN NO_DATA_FOUND THEN
5087 l_supplier_id := NULL;
5088 WHEN OTHERS THEN
5089 l_supplier_id := NULL;
5090 END;
5091 else
5092 l_supplier_id := null;
5093 end if;
5094
5095 log_message('l_supplier_id := ' || l_supplier_id);
5096
5097 if p_supplier_site_id is not null then
5098 BEGIN
5099 select cs.company_site_id
5100 into l_supplier_site_id
5101 from msc_trading_partner_maps m,
5102 msc_company_sites cs
5103 where m.tp_key = p_supplier_site_id and
5104 m.map_type = 3 and
5105 cs.company_site_id = m.company_key;
5106 EXCEPTION
5107 WHEN NO_DATA_FOUND THEN
5108 l_supplier_site_id := null;
5109 WHEN OTHERS THEN
5110 l_supplier_site_id := null;
5111 END;
5112 else
5113 l_supplier_site_id := null;
5114 end if;
5115
5116 log_message('l_supplier_site_id := ' || l_supplier_site_id);
5117
5118 IF ( p_overwrite = 1) THEN --delete all
5119 delete from msc_sup_dem_entries sd
5120 where sd.publisher_order_type in (ORDER_FORECAST,CP_PLANNED_ORDER,
5121 CP_PURCHASE_ORDER_FROM_PLAN,CP_RELEASED_PLANNED_ORDER,
5122 CP_PLANNED_INBOUND_SHIPMENT, CP_RELEASED_INBOUND_SHIPMENT,
5123 RETURNS_FORECAST,DEFECTIVE_OUTBOUND_SHIPMENT) and -- bug#6893383
5124 sd.plan_id = -1 and
5125 sd.publisher_id = 1 and
5126 exists (select cs.company_site_id
5127 from msc_plan_organizations o,
5128 msc_company_sites cs,
5129 msc_trading_partner_maps m,
5130 msc_trading_partners p
5131 where o.plan_id = p_plan_id
5132 AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
5133 AND O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
5134 AND P.SR_TP_ID = O.ORGANIZATION_ID
5135 AND P.SR_INSTANCE_ID = O.SR_INSTANCE_ID
5136 and p.partner_type = 3 and
5137 m.tp_key = p.partner_id and
5138 m.map_type = 2 and
5139 cs.company_site_id = m.company_key and
5140 cs.company_id = 1 and
5141 cs.company_site_id = sd.publisher_site_id and rownum=1) and
5142 sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5143 sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5144 exists (select nvl(i.base_item_id,i.inventory_item_id)
5145 from msc_system_items i,
5146 msc_plan_organizations o
5147 where o.plan_id = p_plan_id and
5148 i.plan_id = o.plan_id and
5149 O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID) AND
5150 O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID) AND
5151 I.ORGANIZATION_ID = O.ORGANIZATION_ID AND
5152 I.SR_INSTANCE_ID = O.SR_INSTANCE_ID AND
5153 NVL(i.planner_code,'-99') = NVL(p_planner_code,
5154 NVL(i.planner_code,'-99')) and
5155 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
5156 NVL(i.abc_class_name,'-99')) and
5157 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)
5158 and NVL(sd.base_item_id, sd.inventory_item_id) =nvl(i.base_item_id,i.inventory_item_id) and
5159 rownum=1 ) ;
5160
5161 l_row := SQL%ROWCOUNT;
5162 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
5163
5164 ELSIF ( p_overwrite = 2) THEN --delete by overwritten
5165 delete from msc_sup_dem_entries sd
5166 where sd.publisher_order_type IN (ORDER_FORECAST, CP_PLANNED_ORDER,
5167 CP_PURCHASE_ORDER_FROM_PLAN, CP_RELEASED_PLANNED_ORDER,
5168 CP_PLANNED_INBOUND_SHIPMENT, CP_RELEASED_INBOUND_SHIPMENT,
5169 RETURNS_FORECAST,DEFECTIVE_OUTBOUND_SHIPMENT) and -- bug#6893383
5170 sd.plan_id = -1 and
5171 sd.publisher_id = 1 and
5172 exists (select cs.company_site_id
5173 from msc_plan_organizations o,
5174 msc_company_sites cs,
5175 msc_trading_partner_maps m,
5176 msc_trading_partners p
5177 where o.plan_id = p_plan_id and
5178 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
5179 p.sr_instance_id = nvl(p_sr_instance_id,
5180 o.sr_instance_id) and
5181 p.partner_type = 3 and
5182 m.tp_key = p.partner_id and
5183 m.map_type = 2 and
5184 cs.company_site_id = m.company_key and
5185 cs.company_id = 1 and
5186 sd.publisher_site_id = cs.company_site_id and
5187 rownum=1) and
5188 sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5189 sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5190 exists (select nvl(i.base_item_id,i.inventory_item_id)
5191 from msc_system_items i,
5192 msc_plan_organizations o
5193 where o.plan_id = p_plan_id and
5194 i.plan_id = o.plan_id and
5195 i.organization_id = nvl(p_org_id,
5196 o.organization_id) and
5197 i.sr_instance_id = nvl(p_sr_instance_id,
5198 o.sr_instance_id) and
5199 NVL(i.planner_code,'-99') = NVL(p_planner_code,
5200 NVL(i.planner_code,'-99')) and
5201 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
5202 NVL(i.abc_class_name,'-99')) and
5203 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id) and
5204 NVL(sd.base_item_id, sd.inventory_item_id) =nvl(i.base_item_id,i.inventory_item_id) and
5205 rownum=1) and
5206 key_date between nvl(p_horizon_start, sysdate - 36500) and
5207 nvl(p_horizon_end, sysdate + 36500);
5208
5209 l_row := SQL%ROWCOUNT;
5210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
5211 END IF;
5212 commit;
5213
5214 END delete_old_forecast;
5215
5216 PROCEDURE LOG_MESSAGE(
5217 p_string IN VARCHAR2
5218 ) IS
5219 BEGIN
5220 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
5221 FND_FILE.PUT_LINE(FND_FILE.LOG, p_string);
5222 END IF;
5223
5224 EXCEPTION
5225 WHEN OTHERS THEn
5226 NULL;
5227
5228 END LOG_MESSAGE;
5229
5230 FUNCTION get_message (
5231 p_app IN VARCHAR2,
5232 p_name IN VARCHAR2,
5233 p_lang IN VARCHAR2
5234 ) RETURN VARCHAR2 IS
5235 msg VARCHAR2(2000) := NULL;
5236 CURSOR c1(app_name VARCHAR2, msg_name VARCHAR2, lang VARCHAR2) IS
5237 SELECT m.message_text
5238 FROM fnd_new_messages m,
5239 fnd_application a
5240 WHERE m.message_name = msg_name AND
5241 m.language_code = lang AND
5242 a.application_short_name = app_name AND
5243 m.application_id = a.application_id;
5244 BEGIN
5245 OPEN c1(p_app, p_name, p_lang);
5246 FETCH c1 INTO msg;
5247 IF (c1%NOTFOUND) then
5248 msg := p_name;
5249 END IF;
5250 CLOSE c1;
5251 RETURN msg;
5252 END get_message;
5253
5254 END msc_sce_publish_pkg;