[Home] [Help]
PACKAGE BODY: APPS.MSC_EXCEPTION_PKG
Source
1 PACKAGE BODY MSC_EXCEPTION_PKG AS
2 /* $Header: MSCHBEXB.pls 120.22.12010000.8 2008/09/03 15:50:16 wexia ship $ */
3 PROCEDURE populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
4 p_plan_id number, p_plan_run_id number) IS
5
6 l_owning_currency_code varchar2(20) := 'XXX';
7
8 BEGIN
9 retcode := 0;
10 errbuf := NULL;
11
12 -- save owning organization's functional currency
13 begin
14 select o.currency_code
15 into l_owning_currency_code
16 from msc_trading_partners o, msc_plans p
17 where o.sr_instance_id=p.sr_instance_id
18 and o.sr_tp_id=p.organization_id
19 and o.partner_type=3
20 and p.plan_id=p_plan_id;
21 exception
22 when others then
23 null;
24 end;
25
26 --dbms_output.put_line('populate_details '||p_plan_id||', '||p_plan_run_id);
27 insert into msc_exceptions_f
28 (plan_id,
29 plan_run_id,
30 organization_id,
31 sr_instance_id,
32 inventory_item_id,
33 department_id,
34 resource_id,
35 supplier_id,
36 supplier_site_id,
37 supplier_region_id,
38 customer_id,
39 customer_site_id,
40 customer_region_id,
41 project_id,
42 task_id,
43 owning_org_id,
44 owning_inst_id,
45 ship_method,
46 analysis_date,
47 aggr_type, category_set_id, sr_category_id, resource_group,
48 exception_type,
49 exception_count,
50 exception_value,
51 exception_value2,
52 exception_days,
53 exception_quantity,
54 exception_ratio,
55 created_by,
56 creation_date,
57 last_update_date,
58 last_updated_by,
59 last_update_login,
60 program_id,
61 program_login_id,
62 program_application_id,
63 request_id)
64 select
65 exception_tbl.plan_id,
66 p_plan_run_id,
67 exception_tbl.organization_id,
68 exception_tbl.sr_instance_id,
69 exception_tbl.inventory_item_id,
70 exception_tbl.department_id,
71 exception_tbl.resource_id,
72 exception_tbl.supplier_id,
73 exception_tbl.supplier_site_id,
74 mps.region_id supplier_region_id,
75 exception_tbl.customer_id,
76 exception_tbl.customer_site_id,
77 mpc.region_id customer_region_id,
78 exception_tbl.project_id,
79 exception_tbl.task_id,
80 exception_tbl.owning_org_id,
81 exception_tbl.owning_inst_id,
82 exception_tbl.ship_method,
83 exception_tbl.analysis_date,
84 to_number(0) aggr_type,
85 to_number(-23453) category_set_id,
86 to_number(-23453) sr_category_id,
87 '-23453' resource_group,
88 exception_tbl.exception_type,
89 exception_tbl.exception_count,
90 exception_tbl.exception_value,
91 exception_tbl.exception_value
92 * decode(exception_tbl.currency_code,
93 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1, nvl(mcc.CONV_RATE,0))
94 exception_value2,
95 exception_tbl.exception_days,
96 exception_tbl.exception_quantity,
97 exception_tbl.exception_ratio,
98 fnd_global.user_id,
99 sysdate,
100 sysdate,
101 fnd_global.user_id,
102 fnd_global.login_id,
103 fnd_global.conc_program_id,
104 fnd_global.conc_login_id,
105 fnd_global.prog_appl_id,
106 fnd_global.conc_request_id
107 from (
108 select
109 med.plan_id,
110 nvl(med.organization_id, -23453) organization_id,
111 nvl(med.sr_instance_id, -23453) sr_instance_id,
112 nvl(decode(med.inventory_item_id,-1,
113 decode(med.exception_type,23,
114 md.inventory_item_id,
115 nvl(ms.inventory_item_id,md.inventory_item_id)),
116 med.inventory_item_id), -23453) inventory_item_id,
117 nvl(decode(med.department_id, -1, -23453, med.department_id), -23453) department_id,
118 nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453) resource_id,
119 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
120 nvl(med.supplier_id, ms.supplier_id)),
121 49, -23453,
122 nvl(med.supplier_id, ms.supplier_id)), -23453) supplier_id,
123 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
124 nvl(med.supplier_site_id, ms.supplier_site_id)),
125 49, -23453,
126 nvl(med.supplier_site_id, ms.supplier_site_id)), -23453) supplier_site_id,
127 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
128 nvl(med.zone_id, ms.zone_id)),
129 49, -23453,
130 nvl(med.zone_id, ms.zone_id)), -23453) supplier_region_id,
131 nvl(decode(med.exception_type, 24, md.customer_id,
132 25, md.customer_id,
133 26,md.customer_id,
134 27, md.customer_id,
135 52,md.customer_id,
136 13,md.customer_id,
137 67,md.customer_id,
138 68,md.customer_id,
139 70,md.customer_id,
140 71,md.customer_id,
141 97,med.customer_id,
142 md2.customer_id), -23453) customer_id,
143 nvl(decode(med.exception_type, 24, md.customer_site_id,
144 25, md.customer_site_id,
145 26,md.customer_site_id,
146 27, md.customer_site_id,
147 52, md.customer_site_id,
148 13, md.customer_site_id,
149 67, md.customer_site_id,
150 68, md.ship_to_site_id,
151 70, md.customer_site_id,
152 71, md.customer_site_id,
153 97, med.customer_site_id,
154 md2.customer_site_id), -23453) customer_site_id,
155 nvl(decode(med.exception_type, 24, md.zone_id,
156 25, md.zone_id,
157 26,md.zone_id,
158 27, md.zone_id,
159 52, md.zone_id,
160 13, md.zone_id,
161 67, md.zone_id,
162 68, -23453,
163 70, md.zone_id,
164 71, md.zone_id,
165 97, med.zone_id,
166 md2.zone_id), -23453) customer_region_id,
167 decode(med.exception_type, 18, nvl(med.number1, -23453),
168 17,nvl(med.number1, -23453),
169 19, nvl(med.number4,nvl(ms.project_id,-23453)),
170 nvl(md.project_id, nvl(ms.project_id,-23453))) project_id,
171 decode(med.exception_type, 18, nvl(med.number2, -23453),
172 17, nvl(med.number2, -23453),
173 19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
174 nvl(md.task_id, nvl(ms.task_id,-23453)) ) task_id,
175
176 decode(sign(nvl(med.organization_id, -23453)),
177 -1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
178 decode(sign(nvl(med.sr_instance_id, -23453)),
179 -1, mp.sr_instance_id, med.sr_instance_id)),
180 med.organization_id) owning_org_id,
181
182 decode(sign(nvl(med.sr_instance_id, -23453)),
183 -1, mp.sr_instance_id, med.sr_instance_id) owning_inst_id,
184
185 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
186 DECODE ( med.exception_type,
187 55, ms.ship_method,
188 56, ms.ship_method,
189 57, ms.ship_method,
190 59, ms.ship_method,
191 40, ms.ship_method,
192 61, ms.ship_method,
193 38,msc_get_name.ship_method(med.plan_id,med.department_id,
194 med.sr_instance_id),
195 39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
196 50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
197 51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
198 msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
199 med.department_id,
200 med.organization_id,
201 med.plan_id,
202 med.sr_instance_id)) ship_method,
203
204 trunc(nvl(med.date1, nvl(mp.plan_start_date, mp.curr_start_date))) analysis_date,
205 med.exception_type,
206 count(*) exception_count,
207 sum(decode(med.exception_type,
208 2, abs(med.quantity) *msi.standard_cost,
209 3, med.quantity *msi.standard_cost,
210 6,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
211 7,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
212 8,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
213 9,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
214 10,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
215 11, abs(med.quantity) *msi.standard_cost,
216 13,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
217 14,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
218 15,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
219 16,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
220 17, abs(med.quantity) *msi.standard_cost,
221 18, med.quantity *msi.standard_cost,
222 23,md.using_requirement_quantity * msc_phub_util.get_list_price
223 (med.plan_id,med.sr_instance_id,med.organization_id,md.inventory_item_id),
224 24,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
225 25,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
226 26,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
227 27,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
228 31,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
229 32,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
230 33,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
231 34,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
232 42,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
233 43,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
234 44,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
235 47,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
236 48,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
237 49,msc_get_name.demand_quantity(med.plan_id,med.sr_instance_id,
238 med.supplier_id)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
239 53,ms.new_order_quantity * msc_phub_util.get_list_price
240 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
244 57,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
241 54,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
242 55,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
243 56,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
245 58,ms.new_order_quantity*msc_phub_util.get_list_price
246 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
247 59,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
248 60,ms.new_order_quantity*msc_phub_util.get_list_price
249 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
250 62,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
251 63,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
252 64,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
253 65,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
254 66,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
255 67,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
256 68,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
257 69,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
258 70,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
259 71,decode(med.number2, 2, ms.new_order_quantity,
260 md.using_requirement_quantity)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
261 72,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
262 73, med.quantity *msi.standard_cost,
263 74, med.quantity *msi.standard_cost,
264 75, med.quantity *msi.standard_cost,
265 76,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
266 77,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
267 114,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
268 to_number(null)) )exception_value,
269 sum(decode( med.exception_type,
270 2,decode(mp.plan_type, 8,(med.date2 -med.date1)+1,
271 (med.date2 -med.date1)),
272
273 3,decode(mp.plan_type, 8,(med.date2 -med.date1)+1,
274 (med.date2 -med.date1)),
275 6,abs(ms.reschedule_days), 6,abs(ms.reschedule_days),
276 7,ms.reschedule_days,
277 10,mp.plan_start_date - med.date1,
278 13,mp.plan_start_date - md.old_demand_date,
279 14,mp.plan_start_date - md.old_demand_date,
280 15,greatest( ms.new_schedule_date - med.date2, 0.01),
281 16,greatest( ms.new_schedule_date - med.date2, 0.01),
282 24,decode((md.dmd_satisfied_date -md.using_assembly_demand_date),0,0,
283 greatest( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01)),
284 25,decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
285 greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)),
286 26,decode((md.dmd_satisfied_date -md.using_assembly_demand_date),0,0,
287 greatest( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01)),
288 27,decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
289 greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)),
290 62,nvl(med.quantity,0),
291 63,med.quantity,
292 64,med.quantity,
293 65,med.quantity,
294 66,med.quantity,
295 to_number(null)--default
296 )) exception_days,
297 sum(decode( med.exception_type,
301 17, abs(med.quantity),
298 2, abs(med.quantity),
299 3, med.quantity,
300 11, abs(med.quantity),
302 18, med.quantity,
303 20, abs(med.quantity),
304 28, med.quantity,
305 34, med.quantity,
306 36, med.quantity,
307 37, med.quantity,
308 42,0,
309 67, abs(med.quantity),
310 73, med.quantity,
311 74, med.quantity,
312 75, med.quantity,
313 85, med.quantity,
314 86, med.quantity,
315 113, med.quantity,
316 to_number(null)
317 )) exception_quantity,
318 sum(decode( med.exception_type,
319 9,(ms.schedule_compress_days/
320 (ms.schedule_compress_days +
321 (ms.new_schedule_date - ms.new_order_placement_date))),
322 21,med.quantity,
323 22,med.quantity,
324 23,med.quantity,
325 38,med.quantity,
326 39,med.quantity,
327 40,med.quantity,
328 45,med.quantity,
329 46,med.quantity,
330 48,abs(med.number3-med.number1),
331 50,abs(med.quantity),
332 51,abs(med.quantity),
333 53,med.quantity,
334 54,med.quantity,
335 55,med.quantity,
336 56,med.quantity,
337 57,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
338 58,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
339 59,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
340 60,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
341 61,med.quantity,
342 79,med.quantity,
343 80,med.quantity,
344 to_number(null)
345 )) exception_ratio
346
347
348 from
349 msc_exception_details med,
350 msc_plans mp,
351 msc_supplies ms,
352 msc_demands md,
353 msc_demands md2,
354 msc_full_pegging mfp,
355 msc_system_items msi,
356 msc_trading_partners mtp
357 where mp.plan_id = p_plan_id
358 and mp.plan_type <> 6
359 and mp.plan_id = med.plan_id
360 and msi.inventory_item_id(+) = med.inventory_item_id
361 and msi.organization_id(+) = med.organization_id
362 and msi.sr_instance_id(+) = med.sr_instance_id
363 and msi.plan_id(+) = med.plan_id
364 and ms.sr_instance_id (+) = med.sr_instance_id
365 and ms.transaction_id (+) = med.number1
366 and ms.plan_id (+) = med.plan_id
367 and md.sr_instance_id (+) = med.sr_instance_id
368 and md.demand_id (+) = med.number1
369 and md.plan_id (+) = med.plan_id
370 and mfp.pegging_id (+) = med.number2
371 and mfp.plan_id (+) = med.plan_id
372 and md2.demand_id (+) = mfp.demand_id
373 and md2.plan_id (+) = mfp.plan_id
374 and mp.plan_id = med.plan_id
375 and mtp.sr_instance_id (+) = med.sr_instance_id
376 and mtp.sr_tp_id (+) = med.organization_id
377 and mtp.partner_type (+) = 3
378 group by
379 med.plan_id,
380 nvl(med.organization_id, -23453),
381 nvl(med.sr_instance_id, -23453),
382 nvl(decode(med.inventory_item_id,-1,
383 decode(med.exception_type,23,
384 md.inventory_item_id,
385 nvl(ms.inventory_item_id,md.inventory_item_id)),
386 med.inventory_item_id), -23453),
387 nvl(decode(med.department_id, -1, -23453, med.department_id), -23453),
388 nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453),
392 nvl(med.supplier_id, ms.supplier_id)), -23453),
389 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
390 nvl(med.supplier_id, ms.supplier_id)),
391 49, -23453,
393 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
394 nvl(med.supplier_site_id, ms.supplier_site_id)),
395 49, -23453,
396 nvl(med.supplier_site_id, ms.supplier_site_id)), -23453),
397 nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
398 nvl(med.zone_id, ms.zone_id)),
399 49, -23453,
400 nvl(med.zone_id, ms.zone_id)), -23453),
401 nvl(decode(med.exception_type, 24, md.customer_id,
402 25, md.customer_id,
403 26,md.customer_id,
404 27, md.customer_id,
405 52,md.customer_id,
406 13,md.customer_id,
407 67,md.customer_id,
408 68,md.customer_id,
409 70,md.customer_id,
410 71,md.customer_id,
411 97,med.customer_id,
412 md2.customer_id), -23453),
413 nvl(decode(med.exception_type, 24, md.customer_site_id,
414 25, md.customer_site_id,
415 26,md.customer_site_id,
416 27, md.customer_site_id,
417 52, md.customer_site_id,
418 13, md.customer_site_id,
419 67, md.customer_site_id,
420 68, md.ship_to_site_id,
421 70, md.customer_site_id,
422 71, md.customer_site_id,
423 97, med.customer_site_id,
424 md2.customer_site_id), -23453),
425 nvl(decode(med.exception_type, 24, md.zone_id,
426 25, md.zone_id,
427 26,md.zone_id,
428 27, md.zone_id,
429 52, md.zone_id,
430 13, md.zone_id,
431 67, md.zone_id,
432 68, -23453,
433 70, md.zone_id,
434 71, md.zone_id,
435 97, med.zone_id,
436 md2.zone_id), -23453),
437 decode(med.exception_type, 18, nvl(med.number1, -23453),
438 17,nvl(med.number1, -23453),
439 19, nvl(med.number4,nvl(ms.project_id,-23453)),
440 nvl(md.project_id, nvl(ms.project_id,-23453))),
441 decode(med.exception_type, 18, nvl(med.number2, -23453),
442 17, nvl(med.number2, -23453),
443 19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
444 nvl(md.task_id, nvl(ms.task_id,-23453)) ),
445
446 decode(sign(nvl(med.organization_id, -23453)),
447 -1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
448 decode(sign(nvl(med.sr_instance_id, -23453)),
449 -1, mp.sr_instance_id, med.sr_instance_id)),
450 med.organization_id),
451
452 decode(sign(nvl(med.sr_instance_id, -23453)),
453 -1, mp.sr_instance_id, med.sr_instance_id),
454
455 nvl(mtp.currency_code, l_owning_currency_code),
456 DECODE ( med.exception_type,
457 55, ms.ship_method,
458 56, ms.ship_method,
459 57, ms.ship_method,
460 59, ms.ship_method,
461 40, ms.ship_method,
462 61, ms.ship_method,
463 38,msc_get_name.ship_method(med.plan_id,med.department_id,
464 med.sr_instance_id),
465 39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
466 50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
467 51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
468 msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
469 med.department_id,
470 med.organization_id,
474 trunc(nvl(med.date1, nvl(mp.plan_start_date, mp.curr_start_date)))
471 med.plan_id,
472 med.sr_instance_id)),
473 med.exception_type,
475
476 union all
477 select
478 t.plan_id,
479 t.organization_id,
480 t.sr_instance_id,
481 t.inventory_item_id,
482 t.department_id,
483 t.resource_id,
484 t.supplier_id,
485 t.supplier_site_id,
486 t.supplier_region_id,
487 t.customer_id,
488 t.customer_site_id,
489 t.customer_region_id,
490 -23453 project_id,
491 -23453 task_id,
492 t.owning_org_id,
493 t.owning_inst_id,
494 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
495 null ship_method,
496 t.date1 analysis_date,
497 t.exception_type,
498 count(*) exception_count,
499 sum(decode(t.exception_type,
500 150, abs(t.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
501 151, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
502 152, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
503 160, abs(t.quantity) *msi.standard_cost,
504 161, t.quantity *msi.standard_cost,
505 162, t.quantity *msi.standard_cost,
506 190, abs(t.quantity) *msi.standard_cost,
507 191, t.quantity *msi.standard_cost,
508 to_number(null)) )exception_value,
509 to_number(null) exception_days,
510 sum(decode( t.exception_type,
511 150, abs(t.quantity),
512 151, t.quantity,
513 152, t.quantity,
514 160, abs(t.quantity),
515 161, t.quantity,
516 162, t.quantity,
517 170, abs(t.quantity),
518 171, t.quantity,
519 172, abs(t.quantity),
520 173, t.quantity,
521 180, abs(t.quantity),
522 181, t.quantity,
523 190, abs(t.quantity),
524 191, t.quantity,
525 200, abs(t.quantity),
526 201, t.quantity,
527 to_number(null))) exception_quantity,
528 avg(t.number2) exception_ratio
529 from
530 (select
531 med.plan_id,
532 nvl(decode(med.organization_id, -1, -23453, med.organization_id), -23453) organization_id,
533 nvl(decode(med.sr_instance_id, -1, -23453, med.sr_instance_id), -23453) sr_instance_id,
534 nvl(decode(med.inventory_item_id, -1, -23453, med.inventory_item_id), -23453) inventory_item_id,
535 nvl(decode(med.department_id, -1, -23453, med.department_id), -23453) department_id,
536 nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453) resource_id,
537 nvl(med.supplier_id, -23453) supplier_id,
538 nvl(med.supplier_site_id, -23453) supplier_site_id,
539 nvl(med.zone_id, -23453) supplier_region_id,
540 nvl(med.customer_id, -23453) customer_id,
541 nvl(med.customer_site_id, -23453) customer_site_id,
542 nvl(med.zone_id, -23453) customer_region_id,
543
544 decode(sign(nvl(med.organization_id, -23453)),
545 -1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
546 decode(sign(nvl(med.sr_instance_id, -23453)),
547 -1, mp.sr_instance_id, med.sr_instance_id)),
548 med.organization_id) owning_org_id,
549
550 decode(sign(nvl(med.sr_instance_id, -23453)),
551 -1, mp.sr_instance_id, med.sr_instance_id) owning_inst_id,
552 med.exception_type,
553 med.quantity,
554 med.date1,
555 med.number2
556 from
557 msc_exception_details med,
558 msc_plans mp
559 where mp.plan_id = med.plan_id
560 and mp.plan_type = 6
561 and mp.plan_id = p_plan_id) t,
562 msc_system_items msi,
563 msc_trading_partners mtp
564 where msi.plan_id(+) = t.plan_id
565 and msi.inventory_item_id(+) = t.inventory_item_id
566 and msi.organization_id(+) = t.owning_org_id
567 and msi.sr_instance_id(+) = t.owning_inst_id
568 and mtp.sr_instance_id(+) = t.sr_instance_id
569 and mtp.sr_tp_id(+) = t.organization_id
570 and mtp.partner_type(+) = 3
571 group by
572 t.plan_id,
573 t.organization_id,
574 t.sr_instance_id,
575 t.inventory_item_id,
576 t.department_id,
577 t.resource_id,
578 t.supplier_id,
579 t.supplier_site_id,
580 t.supplier_region_id,
581 t.customer_id,
582 t.customer_site_id,
583 t.customer_region_id,
584 t.owning_org_id,
585 t.owning_inst_id,
586 nvl(mtp.currency_code, l_owning_currency_code),
587 t.date1,
588 t.exception_type
589 ) exception_tbl,
590 msc_currency_conv_mv mcc,
591 msc_phub_customers_mv mpc,
592 msc_phub_suppliers_mv mps
593 where mcc.from_currency(+) = exception_tbl.currency_code
594 and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
595 and mcc.calendar_date(+) = exception_tbl.analysis_date
596 and mpc.customer_id(+) = exception_tbl.customer_id
597 and mpc.customer_site_id(+) = exception_tbl.customer_site_id
598 and mpc.region_id = decode(nvl(exception_tbl.customer_id, -23453),
599 -23453, nvl(exception_tbl.customer_region_id, -23453), mpc.region_id)
600 and mps.supplier_id(+) = exception_tbl.supplier_id
601 and mps.supplier_site_id(+) = exception_tbl.supplier_site_id
602 and mps.region_id = decode(nvl(exception_tbl.supplier_id, -23453),
603 -23453, nvl(exception_tbl.supplier_region_id, -23453), mps.region_id);
604
605 COMMIT;
606
607 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
608
609 EXCEPTION
610 WHEN DUP_VAL_ON_INDEX THEN
611 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
612 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
613 retcode := 2;
614 WHEN OTHERS THEN
615 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
616 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
617 retcode := 2;
618 END populate_details;
619
620 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
621 p_plan_id number, p_plan_run_id number)
622 is
623 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
624 begin
625 -- level 1
626 insert into msc_exceptions_f (
627 plan_id, plan_run_id,
628 organization_id, sr_instance_id, inventory_item_id,
629 department_id, resource_id,
630 supplier_id, supplier_site_id, supplier_region_id,
631 customer_id, customer_site_id, customer_region_id,
632 project_id, task_id,
633 owning_org_id, owning_inst_id,
634 ship_method, analysis_date,
635 aggr_type, category_set_id, sr_category_id, resource_group,
636 exception_type,
637 exception_count,
638 exception_value,
639 exception_value2,
640 exception_days,
641 exception_quantity,
642 exception_ratio,
643 created_by, creation_date,
644 last_update_date, last_updated_by, last_update_login,
645 program_id, program_login_id,
646 program_application_id, request_id)
647 -- category (42, 43, 44)
648 select
649 f.plan_id, f.plan_run_id,
650 f.organization_id, f.sr_instance_id,
651 to_number(-23453) inventory_item_id,
652 f.department_id, f.resource_id,
653 f.supplier_id, f.supplier_site_id, f.supplier_region_id,
654 f.customer_id, f.customer_site_id, f.customer_region_id,
655 f.project_id, f.task_id,
656 f.owning_org_id, f.owning_inst_id,
657 f.ship_method, f.analysis_date,
658 to_number(42) aggr_type,
659 l_category_set_id1 category_set_id,
660 nvl(q.sr_category_id, -23453),
661 '-23453' resource_group,
662 f.exception_type,
663 sum(f.exception_count),
664 sum(f.exception_value),
665 sum(f.exception_value2),
666 sum(f.exception_days),
667 sum(f.exception_quantity),
668 sum(f.exception_ratio),
669 fnd_global.user_id, sysdate,
670 sysdate, fnd_global.user_id, fnd_global.login_id,
671 fnd_global.conc_program_id, fnd_global.conc_login_id,
672 fnd_global.prog_appl_id, fnd_global.conc_request_id
673 from
674 msc_exceptions_f f,
675 msc_phub_item_categories_mv q
676 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
677 and f.aggr_type=0
678 and f.owning_inst_id=q.sr_instance_id(+)
679 and f.owning_org_id=q.organization_id(+)
680 and f.inventory_item_id=q.inventory_item_id(+)
681 and q.category_set_id(+)=l_category_set_id1
682 group by
683 f.plan_id, f.plan_run_id,
684 f.organization_id, f.sr_instance_id,
685 f.department_id, f.resource_id,
686 f.supplier_id, f.supplier_site_id, f.supplier_region_id,
687 f.customer_id, f.customer_site_id, f.customer_region_id,
688 f.project_id, f.task_id,
689 f.owning_org_id, f.owning_inst_id,
690 f.ship_method, f.analysis_date,
691 nvl(q.sr_category_id, -23453),
692 f.exception_type;
693
694 commit;
695
696 -- level 2
697 insert into msc_exceptions_f (
698 plan_id, plan_run_id,
699 organization_id, sr_instance_id, inventory_item_id,
700 department_id, resource_id,
701 supplier_id, supplier_site_id, supplier_region_id,
702 customer_id, customer_site_id, customer_region_id,
703 project_id, task_id,
704 owning_org_id, owning_inst_id,
705 ship_method, analysis_date,
706 aggr_type, category_set_id, sr_category_id, resource_group,
707 exception_type,
708 exception_count,
709 exception_value,
710 exception_value2,
711 exception_days,
712 exception_quantity,
713 exception_ratio,
714 created_by, creation_date,
715 last_update_date, last_updated_by, last_update_login,
716 program_id, program_login_id,
717 program_application_id, request_id)
718 -- category-mfg_period (1016, 1017, 1018)
719 select
720 f.plan_id, f.plan_run_id,
721 f.organization_id, f.sr_instance_id, f.inventory_item_id,
722 f.department_id, f.resource_id,
723 f.supplier_id, f.supplier_site_id, f.supplier_region_id,
724 f.customer_id, f.customer_site_id, f.customer_region_id,
728 mp.period_start_date analysis_date,
725 f.project_id, f.task_id,
726 f.owning_org_id, f.owning_inst_id,
727 f.ship_method,
729 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
730 f.category_set_id,
731 f.sr_category_id,
732 '-23453' resource_group,
733 f.exception_type,
734 sum(f.exception_count),
735 sum(f.exception_value),
736 sum(f.exception_value2),
737 sum(f.exception_days),
738 sum(f.exception_quantity),
739 sum(f.exception_ratio),
740 fnd_global.user_id, sysdate,
741 sysdate, fnd_global.user_id, fnd_global.login_id,
742 fnd_global.conc_program_id, fnd_global.conc_login_id,
743 fnd_global.prog_appl_id, fnd_global.conc_request_id
744 from
745 msc_exceptions_f f,
746 msc_phub_mfg_cal_periods_mv mp
747 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
748 and f.aggr_type between 42 and 44
749 and f.analysis_date between mp.period_start_date and mp.period_end_date
750 group by
751 f.plan_id, f.plan_run_id,
752 f.organization_id, f.sr_instance_id, f.inventory_item_id,
753 f.department_id, f.resource_id,
754 f.supplier_id, f.supplier_site_id, f.supplier_region_id,
755 f.customer_id, f.customer_site_id, f.customer_region_id,
756 f.project_id, f.task_id,
757 f.owning_org_id, f.owning_inst_id,
758 f.ship_method,
759 mp.period_start_date,
760 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
761 f.category_set_id,
762 f.sr_category_id,
763 f.exception_type;
764
765 commit;
766
767 exception
768 when dup_val_on_index then
769 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
770 SQLCODE||' -ERROR- '||SQLERRM;
771 retcode := 2;
772 when others then
773 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
774 SQLCODE||' -ERROR- '||SQLERRM;
775 retcode := 2;
776
777 end populate_summary;
778
779 PROCEDURE purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
780 p_plan_id number, p_plan_run_id number ) IS
781 BEGIN
782 retcode := 0;
783 errbuf := NULL;
784
785 DELETE FROM msc_exceptions_f
786 WHERE plan_id = p_plan_id
787 and plan_run_id = nvl(p_plan_run_id,plan_run_id);
788 COMMIT;
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
793 retcode := 2;
794 END purge_details;
795
796 END MSC_EXCEPTION_PKG;