[Home] [Help]
PACKAGE BODY: APPS.MSC_EXCEPTION_PKG
Source
1 package body msc_exception_pkg as
2 /* $Header: MSCHBEXB.pls 120.64.12020000.2 2012/10/11 13:57:34 wexia ship $ */
3
4 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
5 p_plan_id number, p_plan_run_id number) IS
6
7 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
8 l_plan_start_date date;
9 l_plan_cutoff_date date;
10 l_plan_type number;
11 l_sr_instance_id number;
12 l_start_time timestamp := systimestamp;
13 begin
14 msc_phub_util.log('msc_exception_pkg.populate_details:');
15 retcode := 0;
16 errbuf := null;
17
18 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date
19 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date
20 from msc_plan_runs
21 where plan_id=p_plan_id
22 and plan_run_id=p_plan_run_id;
23
24 insert into msc_exceptions_f
25 (plan_id,
26 plan_run_id,
27 organization_id,
28 sr_instance_id,
29 inventory_item_id,
30 department_id,
31 resource_id,
32 supplier_id,
33 supplier_site_id,
34 customer_id,
35 customer_site_id,
36 customer_region_id,
37 project_id,
38 task_id,
39 owning_org_id,
40 owning_inst_id,
41 ship_method,
42 vmi_flag,
43 order_type,
44 analysis_date,
45 aggr_type, category_set_id, sr_category_id,
46 exception_type,
47 exception_count,
48 exception_value,
49 exception_value2,
50 exception_days,
51 exception_quantity,
52 exception_ratio,
53 created_by,
54 creation_date,
55 last_update_date,
56 last_updated_by,
57 last_update_login,
58 program_id,
59 program_login_id,
60 program_application_id,
61 request_id)
62 select
63 exception_tbl.plan_id,
64 p_plan_run_id,
65 exception_tbl.organization_id,
66 exception_tbl.sr_instance_id,
67 exception_tbl.inventory_item_id,
68 exception_tbl.department_id,
69 exception_tbl.resource_id,
70 exception_tbl.supplier_id,
71 exception_tbl.supplier_site_id,
72 exception_tbl.customer_id,
73 exception_tbl.customer_site_id,
74 exception_tbl.customer_region_id,
75 exception_tbl.project_id,
76 exception_tbl.task_id,
77 exception_tbl.owning_org_id,
78 exception_tbl.owning_inst_id,
79 nvl(exception_tbl.ship_method, '-23453'),
80 exception_tbl.vmi_flag,
81 exception_tbl.order_type,
82 exception_tbl.analysis_date,
83 to_number(0) aggr_type,
84 to_number(-23453) category_set_id,
85 to_number(-23453) sr_category_id,
86 exception_tbl.exception_type,
87 exception_tbl.exception_count,
88 exception_tbl.exception_value,
89 exception_tbl.exception_value
90 * decode(exception_tbl.currency_code,
91 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1, nvl(mcc.CONV_RATE,0))
92 exception_value2,
93 exception_tbl.exception_days,
94 exception_tbl.exception_quantity,
95 exception_tbl.exception_ratio,
96 fnd_global.user_id,
97 sysdate,
98 sysdate,
99 fnd_global.user_id,
100 fnd_global.login_id,
101 fnd_global.conc_program_id,
102 fnd_global.conc_login_id,
103 fnd_global.prog_appl_id,
104 fnd_global.conc_request_id
105 from (
106 select
107 med.plan_id,
108 decode(sign(nvl(med.organization_id, -23453)),
109 -1, -23453, med.organization_id) organization_id,
110 decode(sign(nvl(med.organization_id, -23453)),
111 -1, -23453, med.sr_instance_id) 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
120 nvl(decode(med.exception_type,
121 48, decode(med.number2,1,-23453,nvl(med.supplier_id, ms.supplier_id)),
122 49, -23453, nvl(med.supplier_id, ms.supplier_id)), -23453) supplier_id,
123 nvl(decode(med.exception_type,
124 48, decode(med.number2,1,-23453,nvl(med.supplier_site_id, ms.supplier_site_id)),
125 49, -23453, nvl(med.supplier_site_id, ms.supplier_site_id)), -23453) supplier_site_id,
126 nvl(decode(med.exception_type, 24, md.customer_id,
127 25, md.customer_id,
128 26,md.customer_id,
129 27, md.customer_id,
130 52,md.customer_id,
131 13,md.customer_id,
132 67,md.customer_id,
133 68,md.customer_id,
134 70,md.customer_id,
135 71,md.customer_id,
136 97,med.customer_id,
137 md2.customer_id), -23453) customer_id,
138 nvl(decode(med.exception_type, 24, md.customer_site_id,
139 25, md.customer_site_id,
140 26,md.customer_site_id,
141 27, md.customer_site_id,
142 52, md.customer_site_id,
143 13, md.customer_site_id,
144 67, md.customer_site_id,
145 68, md.ship_to_site_id,
146 70, md.customer_site_id,
147 71, md.customer_site_id,
148 97, med.customer_site_id,
149 md2.customer_site_id), -23453) customer_site_id,
150 nvl(decode(med.exception_type, 24, md.zone_id,
151 25, md.zone_id,
152 26,md.zone_id,
153 27, md.zone_id,
154 52, md.zone_id,
155 13, md.zone_id,
156 67, md.zone_id,
157 68, -23453,
158 70, md.zone_id,
159 71, md.zone_id,
160 97, med.zone_id,
161 md2.zone_id), -23453) customer_region_id,
162 decode(med.exception_type, 18, nvl(med.number1, -23453),
163 17,nvl(med.number1, -23453),
164 19, nvl(med.number4,nvl(ms.project_id,-23453)),
165 nvl(md.project_id, nvl(ms.project_id,-23453))) project_id,
166 decode(med.exception_type, 18, nvl(med.number2, -23453),
167 17, nvl(med.number2, -23453),
168 19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
169 nvl(md.task_id, nvl(ms.task_id,-23453)) ) task_id,
170
171 decode(sign(nvl(med.organization_id, -23453)),
172 -1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
173 decode(sign(nvl(med.sr_instance_id, -23453)),
174 -1, l_sr_instance_id, med.sr_instance_id)),
175 med.organization_id) owning_org_id,
176
177 decode(sign(nvl(med.sr_instance_id, -23453)),
178 -1, l_sr_instance_id, med.sr_instance_id) owning_inst_id,
179
180 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
181 DECODE ( med.exception_type,
182 55, ms.ship_method,
183 56, ms.ship_method,
184 57, ms.ship_method,
185 59, ms.ship_method,
186 40, ms.ship_method,
187 61, ms.ship_method,
188 38,msc_get_name.ship_method(med.plan_id,med.department_id,
189 med.sr_instance_id),
190 39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
191 50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
192 51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
193 msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
194 med.department_id,
195 med.organization_id,
196 med.plan_id,
197 med.sr_instance_id)) ship_method,
198
199 nvl(msi.vmi_flag, 0) vmi_flag,
200 decode(med.exception_type,10,ms.order_type,-23453) order_type,
201 trunc(nvl(med.date1, l_plan_start_date)) analysis_date,
202 med.exception_type,
203 count(*) exception_count,
204 sum(decode(med.exception_type,
205 2,abs(med.quantity) *msi.standard_cost,
206 3,med.quantity *msi.standard_cost,
207 6,(case when l_plan_type in (101,102,103,105) then med.number5
208 else med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) end),
209 7,(case when l_plan_type in (101,102,103,105) then med.number5
210 else med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) end),
211 8,med.quantity *nvl(msi.standard_cost,0),
212 9,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
213 10,med.quantity *nvl(msi.standard_cost,0),
214 11,abs(med.quantity) *nvl(msi.standard_cost,0),
215 13,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
216 14,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
217 15,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
218 16,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
219 17,abs(med.quantity) *nvl(msi.standard_cost,0),
220 18,med.quantity *nvl(msi.standard_cost,0),
221 23,md.using_requirement_quantity * msc_phub_util.get_list_price
222 (med.plan_id,med.sr_instance_id,med.organization_id,md.inventory_item_id),
223 24,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
224 25,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
225 26,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
226 27,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
227 31,med.quantity *nvl(msi.standard_cost,0),
228 32,med.quantity *nvl(msi.standard_cost,0),
229 33,med.quantity *nvl(msi.standard_cost,0),
230 34,med.quantity *nvl(msi.standard_cost,0),
231 42,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
232 43,med.quantity*nvl(msi.standard_cost,0),
233 44,med.quantity*nvl(msi.standard_cost,0),
234 47,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
235 48,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
236 49,msc_get_name.demand_quantity(med.plan_id,med.sr_instance_id,
237 med.supplier_id)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
238 53,ms.new_order_quantity * msc_phub_util.get_list_price
239 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
243 57,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
240 54,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
241 55,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
242 56,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
244 58,ms.new_order_quantity*msc_phub_util.get_list_price
245 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
246 59,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
247 60,ms.new_order_quantity*msc_phub_util.get_list_price
248 (med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
249 62,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
250 63,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
251 64,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
252 65,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
253 66,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
254 67,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
255 68,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
256 69,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
257 70,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
258 71,decode(med.number2, 2, ms.new_order_quantity,
259 md.using_requirement_quantity)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
260 72,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
261 73, med.quantity *msi.standard_cost,
262 74, med.quantity *msi.standard_cost,
263 75, med.quantity *msi.standard_cost,
264 76,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
265 77,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
266 81,med.quantity*nvl(msi.standard_cost,0),--DRP bnaghi
267 82,med.quantity*nvl(msi.standard_cost,0),--DRP bnaghi
268 95,med.quantity*nvl(msi.standard_cost,0),--DRP bnaghi
269 114,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
270 to_number(null)) )exception_value,
271
272 decode(l_plan_type,5,to_number(null), sum(decode( med.exception_type,
273 2, (case when l_plan_type=8 then (med.date2 -med.date1)+1
274 when l_plan_type in (101,102,103,105) then med.number1
275 else med.date2 - med.date1 end),
276 3,decode(l_plan_type, 8,(med.date2 -med.date1)+1,
277 (med.date2 -med.date1)),
278 6,abs(ms.reschedule_days),
279 7,ms.reschedule_days,
280 10,l_plan_start_date - med.date1,
281 13,l_plan_start_date - md.old_demand_date,
282 14,l_plan_start_date - md.old_demand_date,
283 15,greatest( ms.new_schedule_date - med.date2, 0.01),
284 16,greatest( ms.new_schedule_date - med.date2, 0.01),
285 24, (case when l_plan_type=101 then nvl(med.number4,0) else
286 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)) end),
288 25, (case when l_plan_type=101 then null else
289 decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
290 greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)) end),
291 26, (case when l_plan_type=101 then nvl(med.number4,0) else
292 decode((md.dmd_satisfied_date - md.using_assembly_demand_date),0,0,
293 greatest(md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01)) end),
294 27, (case when l_plan_type=101 then null else
295 decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
296 greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)) end),
297 62,nvl(med.quantity,0),
298 63,med.quantity,
299 64,med.quantity,
300 65,med.quantity,
301 66,med.quantity,
302 to_number(null)--default
303 ))) exception_days,
304
305 sum(case when med.exception_type in (2,11,17,20)
306 then abs(med.quantity)
307 when med.exception_type in (3,6,7,8,9,10,12,13,14,15,16,18,19,24,25,26,27,31,33,36,37,43,44,49,68,69,72,73,74,75,76,81,82,84,85,86,95,113)
308 then med.quantity
309 when med.exception_type in (34,57,58,59,60,77)
310 then ms.new_order_quantity
311 when med.exception_type in (52,70)
312 then md.using_requirement_quantity
313 when med.exception_type in (67)
317 else to_number(null) end) exception_quantity,
314 then abs(md.using_requirement_quantity)
315 when med.exception_type in (42)
316 then 0
318
319
320 decode(l_plan_type,5,to_number(null), sum(decode( med.exception_type,
321 9,(case when nvl(ms.schedule_compress_days + (ms.new_schedule_date - ms.new_order_placement_date), 0) = 0 then 0
322 else ms.schedule_compress_days/ (ms.schedule_compress_days + (ms.new_schedule_date - ms.new_order_placement_date)) end),
323 21,med.quantity,
324 22,med.quantity,
325 23,med.quantity,
326 38,med.quantity,
327 39,med.quantity,
328 40,med.quantity,
329 45,med.quantity,
330 46,med.quantity,
331 48,abs(med.number3-med.number1),
332 50,abs(med.quantity),
333 51,abs(med.quantity),
334 53,med.quantity,
335 54,med.quantity,
336 55,med.quantity,
337 56,med.quantity,
338 57,(case when l_plan_type in (101,102,103,105) then
339 decode(nvl(med.number4,0),0,0,nvl(med.number6,0)/med.number4)
340 else decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)
341 end),
342 58,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
343 59,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
344 60,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
345 61,med.quantity,
346 79,med.quantity,
347 80,med.quantity,
348 to_number(null)
349 ))) exception_ratio
350
351
352 from
353 msc_exception_details med,
354 msc_apcc_item_d msi,
355 msc_trading_partners mtp,
356 msc_supplies ms,
357 msc_demands md,
358 msc_full_pegging mfp,
359 msc_demands md2
360 where med.plan_id=p_plan_id
361 and l_plan_type <> 6
362 and msi.inventory_item_id(+) = med.inventory_item_id
363 and msi.organization_id(+) = med.organization_id
364 and msi.sr_instance_id(+) = med.sr_instance_id
365 and msi.plan_id(+) = med.plan_id
366 and ms.sr_instance_id(+) = med.sr_instance_id
367 and ms.transaction_id(+) = med.number1
368 and ms.plan_id(+) = med.plan_id
369 and md.sr_instance_id(+) = med.sr_instance_id
370 and md.demand_id(+) = med.number1
371 and md.plan_id(+) = med.plan_id
372 and mfp.pegging_id(+) = med.number2
373 and mfp.plan_id(+) = med.plan_id
374 and md2.demand_id(+) = mfp.demand_id
375 and md2.plan_id(+) = mfp.plan_id
376 and mtp.sr_instance_id(+) = med.sr_instance_id
377 and mtp.sr_tp_id(+) = med.organization_id
378 and mtp.partner_type(+) = 3
379 group by
380 med.plan_id,
381 decode(sign(nvl(med.organization_id, -23453)),
382 -1, -23453, med.organization_id),
383 decode(sign(nvl(med.organization_id, -23453)),
384 -1, -23453, med.sr_instance_id),
385 nvl(decode(med.inventory_item_id,-1,
386 decode(med.exception_type,23,
387 md.inventory_item_id,
388 nvl(ms.inventory_item_id,md.inventory_item_id)),
389 med.inventory_item_id), -23453),
390 nvl(decode(med.department_id, -1, -23453, med.department_id), -23453),
391 nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453),
392 nvl(decode(med.exception_type,
393 48, decode(med.number2,1,-23453,nvl(med.supplier_id, ms.supplier_id)),
394 49, -23453, nvl(med.supplier_id, ms.supplier_id)), -23453),
395 nvl(decode(med.exception_type,
396 48, decode(med.number2,1,-23453,nvl(med.supplier_site_id, ms.supplier_site_id)),
397 49, -23453, nvl(med.supplier_site_id, ms.supplier_site_id)), -23453),
398 nvl(decode(med.exception_type, 24, md.customer_id,
399 25, md.customer_id,
400 26,md.customer_id,
401 27, md.customer_id,
402 52,md.customer_id,
403 13,md.customer_id,
404 67,md.customer_id,
405 68,md.customer_id,
406 70,md.customer_id,
407 71,md.customer_id,
408 97,med.customer_id,
409 md2.customer_id), -23453),
410 nvl(decode(med.exception_type, 24, md.customer_site_id,
411 25, md.customer_site_id,
415 13, md.customer_site_id,
412 26,md.customer_site_id,
413 27, md.customer_site_id,
414 52, md.customer_site_id,
416 67, md.customer_site_id,
417 68, md.ship_to_site_id,
418 70, md.customer_site_id,
419 71, md.customer_site_id,
420 97, med.customer_site_id,
421 md2.customer_site_id), -23453),
422 nvl(decode(med.exception_type, 24, md.zone_id,
423 25, md.zone_id,
424 26,md.zone_id,
425 27, md.zone_id,
426 52, md.zone_id,
427 13, md.zone_id,
428 67, md.zone_id,
429 68, -23453,
430 70, md.zone_id,
431 71, md.zone_id,
432 97, med.zone_id,
433 md2.zone_id), -23453),
434 decode(med.exception_type, 18, nvl(med.number1, -23453),
435 17,nvl(med.number1, -23453),
436 19, nvl(med.number4,nvl(ms.project_id,-23453)),
437 nvl(md.project_id, nvl(ms.project_id,-23453))),
438 decode(med.exception_type, 18, nvl(med.number2, -23453),
439 17, nvl(med.number2, -23453),
440 19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
441 nvl(md.task_id, nvl(ms.task_id,-23453)) ),
442
443 decode(sign(nvl(med.organization_id, -23453)),
444 -1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
445 decode(sign(nvl(med.sr_instance_id, -23453)),
446 -1, l_sr_instance_id, med.sr_instance_id)),
447 med.organization_id),
448
449 decode(sign(nvl(med.sr_instance_id, -23453)),
450 -1, l_sr_instance_id, med.sr_instance_id),
451
452 nvl(mtp.currency_code, l_owning_currency_code),
453 DECODE ( med.exception_type,
454 55, ms.ship_method,
455 56, ms.ship_method,
456 57, ms.ship_method,
457 59, ms.ship_method,
458 40, ms.ship_method,
459 61, ms.ship_method,
460 38,msc_get_name.ship_method(med.plan_id,med.department_id,
461 med.sr_instance_id),
462 39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
463 50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
464 51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
465 msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
466 med.department_id,
467 med.organization_id,
468 med.plan_id,
469 med.sr_instance_id)),
470 nvl(msi.vmi_flag, 0),
471 med.exception_type,
472 decode(med.exception_type,10,ms.order_type,-23453),
473 trunc(nvl(med.date1, l_plan_start_date))
474
475 -- SNO
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.customer_id,
487 t.customer_site_id,
488 t.customer_region_id,
489 -23453 project_id,
490 -23453 task_id,
491 t.owning_org_id,
492 t.owning_inst_id,
493 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
494 null ship_method,
495 nvl(msi.vmi_flag, 0) vmi_flag,
496 -23453 order_type,
497 t.date1 analysis_date,
498 t.exception_type,
499 count(*) exception_count,
500 sum(decode(t.exception_type,
501 150, abs(t.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
502 151, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
503 152, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
504 160, abs(t.quantity) *msi.standard_cost,
505 161, t.quantity *msi.standard_cost,
506 162, t.quantity *msi.standard_cost,
507 190, abs(t.quantity) *msi.standard_cost,
508 191, t.quantity *msi.standard_cost,
509 to_number(null)) )exception_value,
510 to_number(null) exception_days,
514 152, t.quantity,
511 sum(decode( t.exception_type,
512 150, abs(t.quantity),
513 151, t.quantity,
515 160, abs(t.quantity),
516 161, t.quantity,
517 162, t.quantity,
518 170, abs(t.quantity),
519 171, t.quantity,
520 172, abs(t.quantity),
521 173, t.quantity,
522 180, abs(t.quantity),
523 181, t.quantity,
524 190, abs(t.quantity),
525 191, t.quantity,
526 200, abs(t.quantity),
527 201, t.quantity,
528 to_number(null))) exception_quantity,
529 avg(t.number2) exception_ratio
530 from
531 (select
532 med.plan_id,
533 nvl(decode(med.organization_id, -1, -23453, med.organization_id), -23453) organization_id,
534 nvl(decode(med.sr_instance_id, -1, -23453, med.sr_instance_id), -23453) sr_instance_id,
535 nvl(decode(med.inventory_item_id, -1, -23453, med.inventory_item_id), -23453) inventory_item_id,
536 nvl(decode(med.department_id, -1, -23453, med.department_id), -23453) department_id,
537 nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453) resource_id,
538 nvl(med.supplier_id, -23453) supplier_id,
539 nvl(med.supplier_site_id, -23453) supplier_site_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, l_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, l_sr_instance_id, med.sr_instance_id) owning_inst_id,
552 med.exception_type,
553 med.quantity,
554 trunc(nvl(med.date1, l_plan_start_date)) date1,
555 med.number2
556 from
557 msc_exception_details med
558 where med.plan_id=p_plan_id
559 and l_plan_type = 6) t,
560 msc_apcc_item_d msi,
561 msc_trading_partners mtp
562 where msi.plan_id(+) = t.plan_id
563 and msi.inventory_item_id(+) = t.inventory_item_id
564 and msi.organization_id(+) = t.owning_org_id
565 and msi.sr_instance_id(+) = t.owning_inst_id
566 and mtp.sr_instance_id(+) = t.sr_instance_id
567 and mtp.sr_tp_id(+) = t.organization_id
568 and mtp.partner_type(+) = 3
569 group by
570 t.plan_id,
571 t.organization_id,
572 t.sr_instance_id,
573 t.inventory_item_id,
574 t.department_id,
575 t.resource_id,
576 t.supplier_id,
577 t.supplier_site_id,
578 t.customer_id,
579 t.customer_site_id,
580 t.customer_region_id,
581 t.owning_org_id,
582 t.owning_inst_id,
583 nvl(msi.vmi_flag, 0),
584 nvl(mtp.currency_code, l_owning_currency_code),
585 t.date1,
586 t.exception_type
587 ) exception_tbl,
588 msc_currency_conv_mv mcc
589 where mcc.from_currency(+) = exception_tbl.currency_code
590 and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
591 and mcc.calendar_date(+) = exception_tbl.analysis_date;
592
593 msc_phub_util.log('msc_exceptions_f, insert='||sql%rowcount);
594 commit;
595
596 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_EXCEPTIONS_F', p_plan_run_id);
597
598 summarize_exceptions_f(errbuf, retcode, p_plan_id, p_plan_run_id);
599 msc_phub_util.log('msc_exception_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
600
601 exception
602 when others then
603 msc_phub_util.log('msc_exception_pkg.populate_details: '||sqlerrm);
604 raise;
605
606 end populate_details;
607
608 procedure summarize_exceptions_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
609 p_plan_id number, p_plan_run_id number)
610 is
611 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
612 begin
613 msc_phub_util.log('msc_exception_pkg.summarize_exceptions_f');
614 retcode := 0;
615 errbuf := '';
616
617 delete from msc_exceptions_f
618 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
619 msc_phub_util.log('msc_exception_pkg.summarize_exceptions_f, delete='||sql%rowcount);
620 commit;
621
622 -- level 1
623 insert into msc_exceptions_f (
624 plan_id, plan_run_id,
625 organization_id, sr_instance_id, inventory_item_id,
629 project_id, task_id,
626 department_id, resource_id,
627 supplier_id, supplier_site_id,
628 customer_id, customer_site_id, customer_region_id,
630 owning_org_id, owning_inst_id,
631 ship_method, vmi_flag,
632 order_type,
633 analysis_date,
634 aggr_type, category_set_id, sr_category_id,
635 exception_type,
636 exception_count,
637 exception_value,
638 exception_value2,
639 exception_days,
640 exception_quantity,
641 exception_ratio,
642 created_by, creation_date,
643 last_update_date, last_updated_by, last_update_login,
644 program_id, program_login_id,
645 program_application_id, request_id)
646 -- category (42, 43, 44)
647 select
648 f.plan_id, f.plan_run_id,
649 f.organization_id, f.sr_instance_id,
650 to_number(-23453) inventory_item_id,
651 f.department_id, f.resource_id,
652 f.supplier_id, f.supplier_site_id,
653 f.customer_id, f.customer_site_id, f.customer_region_id,
654 f.project_id, f.task_id,
655 f.owning_org_id, f.owning_inst_id,
656 f.ship_method, f.vmi_flag,
657 f.order_type,
658 f.analysis_date,
659 to_number(42) aggr_type,
660 l_category_set_id1 category_set_id,
661 nvl(q.sr_category_id, -23453),
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,
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.vmi_flag,
691 f.order_type, f.analysis_date,
692 nvl(q.sr_category_id, -23453),
693 f.exception_type;
694
695 msc_phub_util.log('msc_exception_pkg.summarize_exceptions_f, level1='||sql%rowcount);
696 commit;
697
698 -- level 2
699 insert into msc_exceptions_f (
700 plan_id, plan_run_id,
701 organization_id, sr_instance_id, inventory_item_id,
702 department_id, resource_id,
703 supplier_id, supplier_site_id,
704 customer_id, customer_site_id, customer_region_id,
705 project_id, task_id,
706 owning_org_id, owning_inst_id,
707 ship_method, vmi_flag,
708 order_type,
709 analysis_date,
710 aggr_type, category_set_id, sr_category_id,
711 exception_type,
712 exception_count,
713 exception_value,
714 exception_value2,
715 exception_days,
716 exception_quantity,
717 exception_ratio,
718 created_by, creation_date,
719 last_update_date, last_updated_by, last_update_login,
720 program_id, program_login_id,
721 program_application_id, request_id)
722 -- category-mfg_period (1016, 1017, 1018)
723 select
724 f.plan_id, f.plan_run_id,
725 f.organization_id, f.sr_instance_id, f.inventory_item_id,
726 f.department_id, f.resource_id,
727 f.supplier_id, f.supplier_site_id,
728 f.customer_id, f.customer_site_id, f.customer_region_id,
729 f.project_id, f.task_id,
730 f.owning_org_id, f.owning_inst_id,
731 f.ship_method, f.vmi_flag,
732 f.order_type,
733 d.mfg_period_start_date analysis_date,
734 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
735 f.category_set_id,
736 f.sr_category_id,
737 f.exception_type,
738 sum(f.exception_count),
739 sum(f.exception_value),
740 sum(f.exception_value2),
741 sum(f.exception_days),
742 sum(f.exception_quantity),
743 sum(f.exception_ratio),
744 fnd_global.user_id, sysdate,
745 sysdate, fnd_global.user_id, fnd_global.login_id,
746 fnd_global.conc_program_id, fnd_global.conc_login_id,
747 fnd_global.prog_appl_id, fnd_global.conc_request_id
748 from
749 msc_exceptions_f f,
750 msc_phub_dates_mv d
751 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
752 and f.aggr_type between 42 and 44
753 and f.analysis_date = d.calendar_date
754 and d.mfg_period_start_date is not null
755 group by
759 f.supplier_id, f.supplier_site_id,
756 f.plan_id, f.plan_run_id,
757 f.organization_id, f.sr_instance_id, f.inventory_item_id,
758 f.department_id, f.resource_id,
760 f.customer_id, f.customer_site_id, f.customer_region_id,
761 f.project_id, f.task_id,
762 f.owning_org_id, f.owning_inst_id,
763 f.ship_method, f.vmi_flag,
764 f.order_type,
765 d.mfg_period_start_date,
766 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
767 f.category_set_id,
768 f.sr_category_id,
769 f.exception_type;
770
771 msc_phub_util.log('msc_exception_pkg.summarize_exceptions_f, level2='||sql%rowcount);
772 commit;
773
774 exception
775 when others then
776 retcode := 2;
777 errbuf := 'msc_exception_pkg.summarize_exceptions_f: '||sqlerrm;
778 raise;
779
780 end summarize_exceptions_f;
781
782 procedure export_exceptions_f (
783 errbuf out nocopy varchar2, retcode out nocopy varchar2,
784 p_st_transaction_id number, p_plan_run_id number,
785 p_dblink varchar2, p_source_version varchar2)
786 is
787 l_sql varchar2(5000);
788 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
789 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
790 begin
791 msc_phub_util.log('msc_exception_pkg.export_exceptions_f');
792 retcode := 0;
793 errbuf := null;
794
795 delete from msc_st_exceptions_f where st_transaction_id=p_st_transaction_id;
796 commit;
797
798 l_sql :=
799 ' insert into msc_st_exceptions_f('||
800 ' st_transaction_id,'||
801 ' error_code,'||
802 ' sr_instance_id,'||
803 ' organization_id,'||
804 ' owning_inst_id,'||
805 ' owning_org_id,'||
806 ' inventory_item_id,'||
807 ' department_id,'||
808 ' resource_id,'||
809 ' customer_id,'||
810 ' customer_site_id,'||
811 ' customer_region_id,'||
812 ' supplier_id,'||
813 ' supplier_site_id,'||
814 ' project_id,'||
815 ' task_id,'||
816 ' organization_code,'||
817 ' owning_org_code,'||
818 ' item_name,'||
819 ' department_code,'||
820 ' department_class,'||
821 ' resource_code,'||
822 ' resource_group_name,'||
823 ' customer_name,'||
824 ' customer_site_code,'||
825 ' customer_zone,'||
826 ' supplier_name,'||
827 ' supplier_site_code,'||
828 ' project_number,'||
829 ' task_number,'||
830 ' ship_method,';
831 if (p_source_version >= '12.1.3') then l_sql := l_sql||
832 ' vmi_flag,
833 order_type,';
834 end if;
835 l_sql := l_sql||
836 ' analysis_date,'||
837 ' exception_type,'||
838 ' exception_count,'||
839 ' exception_value,'||
840 ' exception_value2,'||
841 ' exception_days,'||
842 ' exception_quantity,'||
843 ' exception_ratio,'||
844 ' created_by, creation_date,'||
845 ' last_updated_by, last_update_date, last_update_login'||
846 ' )'||
847 ' select'||
848 ' :p_st_transaction_id,'||
849 ' 0,'||
850 ' f.sr_instance_id,'||
851 ' f.organization_id,'||
852 ' f.owning_inst_id,'||
853 ' f.owning_org_id,'||
854 ' f.inventory_item_id,'||
855 ' f.department_id,'||
856 ' f.resource_id,'||
857 ' f.customer_id,'||
858 ' f.customer_site_id,'||
859 ' f.customer_region_id,'||
860 ' f.supplier_id,'||
861 ' f.supplier_site_id,'||
862 ' f.project_id,'||
863 ' f.task_id,'||
864 ' mtp.organization_code,'||
865 ' mtp2.organization_code,'||
866 ' mi.item_name,'||
867 ' mdr.department_code,'||
868 ' mdr.department_class,'||
869 ' mdr.resource_code,'||
870 ' mdr.resource_group_name,'||
871 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
872 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
873 ' decode(f.customer_region_id, -23453, null, cmv.zone),'||
874 ' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
875 ' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
876 ' proj.project_number,'||
877 ' proj.task_number,'||
878 ' f.ship_method,';
879 if (p_source_version >= '12.1.3') then l_sql := l_sql||
880 ' f.vmi_flag,
881 f.order_type,';
882 end if;
883 l_sql := l_sql||
884 ' f.analysis_date,'||
885 ' f.exception_type,'||
886 ' f.exception_count,'||
887 ' f.exception_value,'||
888 ' f.exception_value2,'||
889 ' f.exception_days,'||
890 ' f.exception_quantity,'||
894 ' from'||
891 ' f.exception_ratio,'||
892 ' fnd_global.user_id, sysdate,'||
893 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
895 ' '||l_apps_schema||'.msc_exceptions_f'||l_suffix||' f,'||
896 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
897 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
898 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
899 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv,'||
900 ' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv,'||
901 ' '||l_apps_schema||'.msc_department_resources'||l_suffix||' mdr,';
902 if (p_source_version >= '12.1.3') then l_sql := l_sql||
903 ' '||l_apps_schema||'.msc_phub_projects_mv'||l_suffix||' proj';
904 else l_sql := l_sql||
905 ' (select p.sr_instance_id, p.organization_id,'||
906 ' p.project_id, t.task_id, p.project_number, t.task_number'||
907 ' from '||l_apps_schema||'.msc_projects'||l_suffix||' p, '||l_apps_schema||'.msc_project_tasks'||l_suffix||' t'||
908 ' where p.project_id=t.project_id'||
909 ' and p.plan_id=t.plan_id'||
910 ' and p.sr_instance_id=t.sr_instance_id'||
911 ' and p.organization_id=t.organization_id'||
912 ' and p.plan_id=-1) proj';
913 end if;
914 l_sql := l_sql||
915 ' where f.plan_run_id=:p_plan_run_id'||
916 ' and f.aggr_type=0'||
917 ' and mtp.partner_type(+)=3'||
918 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
919 ' and mtp.sr_tp_id(+)=f.organization_id'||
920 ' and mtp2.partner_type(+)=3'||
921 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
922 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
923 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
924 ' and mdr.plan_id(+)=-1'||
925 ' and mdr.department_id(+)=f.department_id'||
926 ' and mdr.resource_id(+)=f.resource_id'||
927 ' and mdr.sr_instance_id(+)=f.sr_instance_id'||
928 ' and mdr.organization_id(+)=f.organization_id'||
929 ' and cmv.customer_id(+)=f.customer_id'||
930 ' and cmv.customer_site_id(+)=f.customer_site_id'||
931 ' and cmv.region_id(+)=f.customer_region_id';
932 if (p_source_version >= '12.1.3') then l_sql := l_sql||
933 ' and cmv.sr_instance_id(+)=decode(f.customer_region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
934 end if;
935 l_sql := l_sql||
936 ' and smv.supplier_id(+)=f.supplier_id'||
937 ' and smv.supplier_site_id(+)=f.supplier_site_id'||
938 ' and proj.project_id(+)=f.project_id'||
939 ' and proj.task_id(+)=f.task_id'||
940 ' and proj.sr_instance_id(+)=f.sr_instance_id'||
941 ' and proj.organization_id(+)=f.organization_id';
942
943 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
944 commit;
945 msc_phub_util.log('msc_exception_pkg.export_exceptions_f: complete, retcode='||retcode);
946
947 exception
948 when others then
949 retcode := 2;
950 errbuf := 'msc_exception_pkg.export_exceptions_f: '||sqlerrm;
951 msc_phub_util.log(errbuf);
952 end export_exceptions_f;
953
954 procedure import_exceptions_f (
955 errbuf out nocopy varchar2, retcode out nocopy varchar2,
956 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
957 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
958 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
959 is
960 l_staging_table varchar2(30) := 'msc_st_exceptions_f';
961 l_fact_table varchar2(30) := 'msc_exceptions_f';
962 l_result number := 0;
963 begin
964 msc_phub_util.log('msc_exception_pkg.import_exceptions_f');
965 retcode := 0;
966 errbuf := null;
967
968 l_result := l_result + msc_phub_util.prepare_staging_dates(
969 l_staging_table, 'analysis_date', p_st_transaction_id,
970 p_upload_mode, p_overwrite_after_date,
971 p_plan_start_date, p_plan_cutoff_date);
972
973 l_result := l_result + msc_phub_util.prepare_fact_dates(
974 l_fact_table, 1, 'analysis_date', p_plan_id, p_plan_run_id,
975 p_upload_mode, p_overwrite_after_date);
976
977 l_result := l_result + msc_phub_util.decode_organization_key(
978 l_staging_table, p_st_transaction_id, p_def_instance_code,
979 'sr_instance_id', 'organization_id', 'organization_code');
980
981 l_result := l_result + msc_phub_util.decode_organization_key(
982 l_staging_table, p_st_transaction_id, p_def_instance_code,
983 'owning_inst_id', 'owning_org_id', 'owning_org_code');
984
985 l_result := l_result + msc_phub_util.decode_item_key(
986 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
987
988 l_result := l_result + msc_phub_util.decode_customer_key(
989 l_staging_table, p_st_transaction_id,
990 'customer_id', 'customer_site_id', 'owning_inst_id', 'customer_region_id',
991 'customer_name', 'customer_site_code', 'customer_zone');
992
993 l_result := l_result + msc_phub_util.decode_supplier_key(
994 l_staging_table, p_st_transaction_id,
995 'supplier_id', 'supplier_site_id',
996 'supplier_name', 'supplier_site_code');
997
998 l_result := l_result + msc_phub_util.decode_resource_key(
1002 l_staging_table, p_st_transaction_id);
999 l_staging_table, p_st_transaction_id);
1000
1001 l_result := l_result + msc_phub_util.decode_project_key(
1003
1004 msc_phub_util.log('msc_exception_pkg.import_exceptions_f: insert into msc_exceptions_f');
1005 insert into msc_exceptions_f (
1006 plan_id,
1007 plan_run_id,
1008 sr_instance_id,
1009 organization_id,
1010 owning_inst_id,
1011 owning_org_id,
1012 inventory_item_id,
1013 department_id,
1014 resource_id,
1015 customer_id,
1016 customer_site_id,
1017 customer_region_id,
1018 supplier_id,
1019 supplier_site_id,
1020 project_id,
1021 task_id,
1022 ship_method,
1023 vmi_flag,
1024 order_type,
1025 analysis_date,
1026 exception_type,
1027 exception_count,
1028 exception_value,
1029 exception_value2,
1030 exception_days,
1031 exception_quantity,
1032 exception_ratio,
1033 aggr_type, category_set_id, sr_category_id,
1034 created_by, creation_date,
1035 last_updated_by, last_update_date, last_update_login
1036 )
1037 select
1038 p_plan_id,
1039 p_plan_run_id,
1040 nvl(sr_instance_id, -23453),
1041 nvl(organization_id, -23453),
1042 nvl(owning_inst_id, -23453),
1043 nvl(owning_org_id, -23453),
1044 nvl(inventory_item_id, -23453),
1045 nvl(department_id, -23453),
1046 nvl(resource_id, -23453),
1047 nvl(customer_id, -23453),
1048 nvl(customer_site_id, -23453),
1049 nvl(customer_region_id, -23453),
1050 nvl(supplier_id, -23453),
1051 nvl(supplier_site_id, -23453),
1052 nvl(project_id, -23453),
1053 nvl(task_id, -23453),
1054 ship_method,
1055 vmi_flag,
1056 order_type,
1057 analysis_date,
1058 exception_type,
1059 exception_count,
1060 exception_value,
1061 exception_value2,
1062 exception_days,
1063 exception_quantity,
1064 exception_ratio,
1065 0, -23453, -23453,
1066 fnd_global.user_id, sysdate,
1067 fnd_global.user_id, sysdate, fnd_global.login_id
1068 from msc_st_exceptions_f
1069 where st_transaction_id=p_st_transaction_id and error_code=0;
1070
1071 msc_phub_util.log('msc_exception_pkg.import_exceptions_f: inserted='||sql%rowcount);
1072 commit;
1073
1074 summarize_exceptions_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1075
1076 if (l_result > 0) then
1077 retcode := -1;
1078 end if;
1079
1080 msc_phub_util.log('msc_exception_pkg.import_exceptions_f: complete, retcode='||retcode);
1081
1082 exception
1083 when others then
1084 retcode := 2;
1085 errbuf := 'msc_exception_pkg.import_exceptions_f: '||sqlerrm;
1086 msc_phub_util.log(errbuf);
1087 end import_exceptions_f;
1088
1089 end msc_exception_pkg;