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