[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select o.currency_code
into l_owning_currency_code
from msc_trading_partners o, msc_plans p
where o.sr_instance_id=p.sr_instance_id
and o.sr_tp_id=p.organization_id
and o.partner_type=3
and p.plan_id=p_plan_id;
insert into msc_exceptions_f
(plan_id,
plan_run_id,
organization_id,
sr_instance_id,
inventory_item_id,
department_id,
resource_id,
supplier_id,
supplier_site_id,
supplier_region_id,
customer_id,
customer_site_id,
customer_region_id,
project_id,
task_id,
owning_org_id,
owning_inst_id,
ship_method,
analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
exception_type,
exception_count,
exception_value,
exception_value2,
exception_days,
exception_quantity,
exception_ratio,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
select
exception_tbl.plan_id,
p_plan_run_id,
exception_tbl.organization_id,
exception_tbl.sr_instance_id,
exception_tbl.inventory_item_id,
exception_tbl.department_id,
exception_tbl.resource_id,
exception_tbl.supplier_id,
exception_tbl.supplier_site_id,
mps.region_id supplier_region_id,
exception_tbl.customer_id,
exception_tbl.customer_site_id,
mpc.region_id customer_region_id,
exception_tbl.project_id,
exception_tbl.task_id,
exception_tbl.owning_org_id,
exception_tbl.owning_inst_id,
exception_tbl.ship_method,
exception_tbl.analysis_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
'-23453' resource_group,
exception_tbl.exception_type,
exception_tbl.exception_count,
exception_tbl.exception_value,
exception_tbl.exception_value
* decode(exception_tbl.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1, nvl(mcc.CONV_RATE,0))
exception_value2,
exception_tbl.exception_days,
exception_tbl.exception_quantity,
exception_tbl.exception_ratio,
fnd_global.user_id,
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_program_id,
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
fnd_global.conc_request_id
from (
select
med.plan_id,
nvl(med.organization_id, -23453) organization_id,
nvl(med.sr_instance_id, -23453) sr_instance_id,
nvl(decode(med.inventory_item_id,-1,
decode(med.exception_type,23,
md.inventory_item_id,
nvl(ms.inventory_item_id,md.inventory_item_id)),
med.inventory_item_id), -23453) inventory_item_id,
nvl(decode(med.department_id, -1, -23453, med.department_id), -23453) department_id,
nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453) resource_id,
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.supplier_id, ms.supplier_id)),
49, -23453,
nvl(med.supplier_id, ms.supplier_id)), -23453) supplier_id,
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.supplier_site_id, ms.supplier_site_id)),
49, -23453,
nvl(med.supplier_site_id, ms.supplier_site_id)), -23453) supplier_site_id,
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.zone_id, ms.zone_id)),
49, -23453,
nvl(med.zone_id, ms.zone_id)), -23453) supplier_region_id,
nvl(decode(med.exception_type, 24, md.customer_id,
25, md.customer_id,
26,md.customer_id,
27, md.customer_id,
52,md.customer_id,
13,md.customer_id,
67,md.customer_id,
68,md.customer_id,
70,md.customer_id,
71,md.customer_id,
97,med.customer_id,
md2.customer_id), -23453) customer_id,
nvl(decode(med.exception_type, 24, md.customer_site_id,
25, md.customer_site_id,
26,md.customer_site_id,
27, md.customer_site_id,
52, md.customer_site_id,
13, md.customer_site_id,
67, md.customer_site_id,
68, md.ship_to_site_id,
70, md.customer_site_id,
71, md.customer_site_id,
97, med.customer_site_id,
md2.customer_site_id), -23453) customer_site_id,
nvl(decode(med.exception_type, 24, md.zone_id,
25, md.zone_id,
26,md.zone_id,
27, md.zone_id,
52, md.zone_id,
13, md.zone_id,
67, md.zone_id,
68, -23453,
70, md.zone_id,
71, md.zone_id,
97, med.zone_id,
md2.zone_id), -23453) customer_region_id,
decode(med.exception_type, 18, nvl(med.number1, -23453),
17,nvl(med.number1, -23453),
19, nvl(med.number4,nvl(ms.project_id,-23453)),
nvl(md.project_id, nvl(ms.project_id,-23453))) project_id,
decode(med.exception_type, 18, nvl(med.number2, -23453),
17, nvl(med.number2, -23453),
19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
nvl(md.task_id, nvl(ms.task_id,-23453)) ) task_id,
decode(sign(nvl(med.organization_id, -23453)),
-1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id)),
med.organization_id) owning_org_id,
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id) owning_inst_id,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
DECODE ( med.exception_type,
55, ms.ship_method,
56, ms.ship_method,
57, ms.ship_method,
59, ms.ship_method,
40, ms.ship_method,
61, ms.ship_method,
38,msc_get_name.ship_method(med.plan_id,med.department_id,
med.sr_instance_id),
39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
med.department_id,
med.organization_id,
med.plan_id,
med.sr_instance_id)) ship_method,
trunc(nvl(med.date1, nvl(mp.plan_start_date, mp.curr_start_date))) analysis_date,
med.exception_type,
count(*) exception_count,
sum(decode(med.exception_type,
2, abs(med.quantity) *msi.standard_cost,
3, med.quantity *msi.standard_cost,
6,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
7,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
8,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
9,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
10,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
11, abs(med.quantity) *msi.standard_cost,
13,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
14,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
15,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
16,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
17, abs(med.quantity) *msi.standard_cost,
18, med.quantity *msi.standard_cost,
23,md.using_requirement_quantity * msc_phub_util.get_list_price
(med.plan_id,med.sr_instance_id,med.organization_id,md.inventory_item_id),
24,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
25,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
26,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
27,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
31,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
32,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
33,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
34,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
42,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
43,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
44,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
47,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
48,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
49,msc_get_name.demand_quantity(med.plan_id,med.sr_instance_id,
med.supplier_id)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
53,ms.new_order_quantity * msc_phub_util.get_list_price
(med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
54,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
55,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
56,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
57,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
58,ms.new_order_quantity*msc_phub_util.get_list_price
(med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
59,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
60,ms.new_order_quantity*msc_phub_util.get_list_price
(med.plan_id,med.sr_instance_id,med.organization_id,ms.inventory_item_id),
62,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
63,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
64,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
65,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
66,ms.new_order_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
67,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
68,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
69,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
70,md.using_requirement_quantity*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
71,decode(med.number2, 2, ms.new_order_quantity,
md.using_requirement_quantity)*nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
72,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
73, med.quantity *msi.standard_cost,
74, med.quantity *msi.standard_cost,
75, med.quantity *msi.standard_cost,
76,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
77,med.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
114,abs(med.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
to_number(null)) )exception_value,
sum(decode( med.exception_type,
2,decode(mp.plan_type, 8,(med.date2 -med.date1)+1,
(med.date2 -med.date1)),
3,decode(mp.plan_type, 8,(med.date2 -med.date1)+1,
(med.date2 -med.date1)),
6,abs(ms.reschedule_days), 6,abs(ms.reschedule_days),
7,ms.reschedule_days,
10,mp.plan_start_date - med.date1,
13,mp.plan_start_date - md.old_demand_date,
14,mp.plan_start_date - md.old_demand_date,
15,greatest( ms.new_schedule_date - med.date2, 0.01),
16,greatest( ms.new_schedule_date - med.date2, 0.01),
24,decode((md.dmd_satisfied_date -md.using_assembly_demand_date),0,0,
greatest( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01)),
25,decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)),
26,decode((md.dmd_satisfied_date -md.using_assembly_demand_date),0,0,
greatest( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01)),
27,decode((md.using_assembly_demand_date - md.dmd_satisfied_date), 0,0,
greatest(md.using_assembly_demand_date - md.dmd_satisfied_date, 0.01)),
62,nvl(med.quantity,0),
63,med.quantity,
64,med.quantity,
65,med.quantity,
66,med.quantity,
to_number(null)--default
)) exception_days,
sum(decode( med.exception_type,
2, abs(med.quantity),
3, med.quantity,
11, abs(med.quantity),
17, abs(med.quantity),
18, med.quantity,
20, abs(med.quantity),
28, med.quantity,
34, med.quantity,
36, med.quantity,
37, med.quantity,
42,0,
67, abs(med.quantity),
73, med.quantity,
74, med.quantity,
75, med.quantity,
85, med.quantity,
86, med.quantity,
113, med.quantity,
to_number(null)
)) exception_quantity,
sum(decode( med.exception_type,
9,(ms.schedule_compress_days/
(ms.schedule_compress_days +
(ms.new_schedule_date - ms.new_order_placement_date))),
21,med.quantity,
22,med.quantity,
23,med.quantity,
38,med.quantity,
39,med.quantity,
40,med.quantity,
45,med.quantity,
46,med.quantity,
48,abs(med.number3-med.number1),
50,abs(med.quantity),
51,abs(med.quantity),
53,med.quantity,
54,med.quantity,
55,med.quantity,
56,med.quantity,
57,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
58,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
59,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
60,(decode(nvl(med.quantity,0),0,0,nvl(med.number5,0)/med.quantity)),
61,med.quantity,
79,med.quantity,
80,med.quantity,
to_number(null)
)) exception_ratio
from
msc_exception_details med,
msc_plans mp,
msc_supplies ms,
msc_demands md,
msc_demands md2,
msc_full_pegging mfp,
msc_system_items msi,
msc_trading_partners mtp
where mp.plan_id = p_plan_id
and mp.plan_type <> 6
and mp.plan_id = med.plan_id
and msi.inventory_item_id(+) = med.inventory_item_id
and msi.organization_id(+) = med.organization_id
and msi.sr_instance_id(+) = med.sr_instance_id
and msi.plan_id(+) = med.plan_id
and ms.sr_instance_id (+) = med.sr_instance_id
and ms.transaction_id (+) = med.number1
and ms.plan_id (+) = med.plan_id
and md.sr_instance_id (+) = med.sr_instance_id
and md.demand_id (+) = med.number1
and md.plan_id (+) = med.plan_id
and mfp.pegging_id (+) = med.number2
and mfp.plan_id (+) = med.plan_id
and md2.demand_id (+) = mfp.demand_id
and md2.plan_id (+) = mfp.plan_id
and mp.plan_id = med.plan_id
and mtp.sr_instance_id (+) = med.sr_instance_id
and mtp.sr_tp_id (+) = med.organization_id
and mtp.partner_type (+) = 3
group by
med.plan_id,
nvl(med.organization_id, -23453),
nvl(med.sr_instance_id, -23453),
nvl(decode(med.inventory_item_id,-1,
decode(med.exception_type,23,
md.inventory_item_id,
nvl(ms.inventory_item_id,md.inventory_item_id)),
med.inventory_item_id), -23453),
nvl(decode(med.department_id, -1, -23453, med.department_id), -23453),
nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453),
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.supplier_id, ms.supplier_id)),
49, -23453,
nvl(med.supplier_id, ms.supplier_id)), -23453),
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.supplier_site_id, ms.supplier_site_id)),
49, -23453,
nvl(med.supplier_site_id, ms.supplier_site_id)), -23453),
nvl(decode(med.exception_type,48,decode(med.number2,1,-23453,
nvl(med.zone_id, ms.zone_id)),
49, -23453,
nvl(med.zone_id, ms.zone_id)), -23453),
nvl(decode(med.exception_type, 24, md.customer_id,
25, md.customer_id,
26,md.customer_id,
27, md.customer_id,
52,md.customer_id,
13,md.customer_id,
67,md.customer_id,
68,md.customer_id,
70,md.customer_id,
71,md.customer_id,
97,med.customer_id,
md2.customer_id), -23453),
nvl(decode(med.exception_type, 24, md.customer_site_id,
25, md.customer_site_id,
26,md.customer_site_id,
27, md.customer_site_id,
52, md.customer_site_id,
13, md.customer_site_id,
67, md.customer_site_id,
68, md.ship_to_site_id,
70, md.customer_site_id,
71, md.customer_site_id,
97, med.customer_site_id,
md2.customer_site_id), -23453),
nvl(decode(med.exception_type, 24, md.zone_id,
25, md.zone_id,
26,md.zone_id,
27, md.zone_id,
52, md.zone_id,
13, md.zone_id,
67, md.zone_id,
68, -23453,
70, md.zone_id,
71, md.zone_id,
97, med.zone_id,
md2.zone_id), -23453),
decode(med.exception_type, 18, nvl(med.number1, -23453),
17,nvl(med.number1, -23453),
19, nvl(med.number4,nvl(ms.project_id,-23453)),
nvl(md.project_id, nvl(ms.project_id,-23453))),
decode(med.exception_type, 18, nvl(med.number2, -23453),
17, nvl(med.number2, -23453),
19, decode(med.number4,null,nvl(ms.task_id,-23453),med.number1),
nvl(md.task_id, nvl(ms.task_id,-23453)) ),
decode(sign(nvl(med.organization_id, -23453)),
-1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id)),
med.organization_id),
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id),
nvl(mtp.currency_code, l_owning_currency_code),
DECODE ( med.exception_type,
55, ms.ship_method,
56, ms.ship_method,
57, ms.ship_method,
59, ms.ship_method,
40, ms.ship_method,
61, ms.ship_method,
38,msc_get_name.ship_method(med.plan_id,med.department_id,
med.sr_instance_id),
39, msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
50,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
51,msc_get_name.ship_method(med.plan_id,med.department_id,med.sr_instance_id),
msc_get_name.department_code( decode (med.resource_id, -1, 1, 2),
med.department_id,
med.organization_id,
med.plan_id,
med.sr_instance_id)),
med.exception_type,
trunc(nvl(med.date1, nvl(mp.plan_start_date, mp.curr_start_date)))
union all
select
t.plan_id,
t.organization_id,
t.sr_instance_id,
t.inventory_item_id,
t.department_id,
t.resource_id,
t.supplier_id,
t.supplier_site_id,
t.supplier_region_id,
t.customer_id,
t.customer_site_id,
t.customer_region_id,
-23453 project_id,
-23453 task_id,
t.owning_org_id,
t.owning_inst_id,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
null ship_method,
t.date1 analysis_date,
t.exception_type,
count(*) exception_count,
sum(decode(t.exception_type,
150, abs(t.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
151, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
152, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
160, abs(t.quantity) *msi.standard_cost,
161, t.quantity *msi.standard_cost,
162, t.quantity *msi.standard_cost,
190, abs(t.quantity) *msi.standard_cost,
191, t.quantity *msi.standard_cost,
to_number(null)) )exception_value,
to_number(null) exception_days,
sum(decode( t.exception_type,
150, abs(t.quantity),
151, t.quantity,
152, t.quantity,
160, abs(t.quantity),
161, t.quantity,
162, t.quantity,
170, abs(t.quantity),
171, t.quantity,
172, abs(t.quantity),
173, t.quantity,
180, abs(t.quantity),
181, t.quantity,
190, abs(t.quantity),
191, t.quantity,
200, abs(t.quantity),
201, t.quantity,
to_number(null))) exception_quantity,
avg(t.number2) exception_ratio
from
(select
med.plan_id,
nvl(decode(med.organization_id, -1, -23453, med.organization_id), -23453) organization_id,
nvl(decode(med.sr_instance_id, -1, -23453, med.sr_instance_id), -23453) sr_instance_id,
nvl(decode(med.inventory_item_id, -1, -23453, med.inventory_item_id), -23453) inventory_item_id,
nvl(decode(med.department_id, -1, -23453, med.department_id), -23453) department_id,
nvl(decode(med.department_id, -1, -23453, med.resource_id), -23453) resource_id,
nvl(med.supplier_id, -23453) supplier_id,
nvl(med.supplier_site_id, -23453) supplier_site_id,
nvl(med.zone_id, -23453) supplier_region_id,
nvl(med.customer_id, -23453) customer_id,
nvl(med.customer_site_id, -23453) customer_site_id,
nvl(med.zone_id, -23453) customer_region_id,
decode(sign(nvl(med.organization_id, -23453)),
-1, msc_hub_calendar.get_item_org(p_plan_id, med.inventory_item_id,
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id)),
med.organization_id) owning_org_id,
decode(sign(nvl(med.sr_instance_id, -23453)),
-1, mp.sr_instance_id, med.sr_instance_id) owning_inst_id,
med.exception_type,
med.quantity,
med.date1,
med.number2
from
msc_exception_details med,
msc_plans mp
where mp.plan_id = med.plan_id
and mp.plan_type = 6
and mp.plan_id = p_plan_id) t,
msc_system_items msi,
msc_trading_partners mtp
where msi.plan_id(+) = t.plan_id
and msi.inventory_item_id(+) = t.inventory_item_id
and msi.organization_id(+) = t.owning_org_id
and msi.sr_instance_id(+) = t.owning_inst_id
and mtp.sr_instance_id(+) = t.sr_instance_id
and mtp.sr_tp_id(+) = t.organization_id
and mtp.partner_type(+) = 3
group by
t.plan_id,
t.organization_id,
t.sr_instance_id,
t.inventory_item_id,
t.department_id,
t.resource_id,
t.supplier_id,
t.supplier_site_id,
t.supplier_region_id,
t.customer_id,
t.customer_site_id,
t.customer_region_id,
t.owning_org_id,
t.owning_inst_id,
nvl(mtp.currency_code, l_owning_currency_code),
t.date1,
t.exception_type
) exception_tbl,
msc_currency_conv_mv mcc,
msc_phub_customers_mv mpc,
msc_phub_suppliers_mv mps
where mcc.from_currency(+) = exception_tbl.currency_code
and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.calendar_date(+) = exception_tbl.analysis_date
and mpc.customer_id(+) = exception_tbl.customer_id
and mpc.customer_site_id(+) = exception_tbl.customer_site_id
and mpc.region_id = decode(nvl(exception_tbl.customer_id, -23453),
-23453, nvl(exception_tbl.customer_region_id, -23453), mpc.region_id)
and mps.supplier_id(+) = exception_tbl.supplier_id
and mps.supplier_site_id(+) = exception_tbl.supplier_site_id
and mps.region_id = decode(nvl(exception_tbl.supplier_id, -23453),
-23453, nvl(exception_tbl.supplier_region_id, -23453), mps.region_id);
insert into msc_exceptions_f (
plan_id, plan_run_id,
organization_id, sr_instance_id, inventory_item_id,
department_id, resource_id,
supplier_id, supplier_site_id, supplier_region_id,
customer_id, customer_site_id, customer_region_id,
project_id, task_id,
owning_org_id, owning_inst_id,
ship_method, analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
exception_type,
exception_count,
exception_value,
exception_value2,
exception_days,
exception_quantity,
exception_ratio,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category (42, 43, 44)
select
f.plan_id, f.plan_run_id,
f.organization_id, f.sr_instance_id,
to_number(-23453) inventory_item_id,
f.department_id, f.resource_id,
f.supplier_id, f.supplier_site_id, f.supplier_region_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.project_id, f.task_id,
f.owning_org_id, f.owning_inst_id,
f.ship_method, f.analysis_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
'-23453' resource_group,
f.exception_type,
sum(f.exception_count),
sum(f.exception_value),
sum(f.exception_value2),
sum(f.exception_days),
sum(f.exception_quantity),
sum(f.exception_ratio),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_exceptions_f f,
msc_phub_item_categories_mv q
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and f.owning_inst_id=q.sr_instance_id(+)
and f.owning_org_id=q.organization_id(+)
and f.inventory_item_id=q.inventory_item_id(+)
and q.category_set_id(+)=l_category_set_id1
group by
f.plan_id, f.plan_run_id,
f.organization_id, f.sr_instance_id,
f.department_id, f.resource_id,
f.supplier_id, f.supplier_site_id, f.supplier_region_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.project_id, f.task_id,
f.owning_org_id, f.owning_inst_id,
f.ship_method, f.analysis_date,
nvl(q.sr_category_id, -23453),
f.exception_type;
insert into msc_exceptions_f (
plan_id, plan_run_id,
organization_id, sr_instance_id, inventory_item_id,
department_id, resource_id,
supplier_id, supplier_site_id, supplier_region_id,
customer_id, customer_site_id, customer_region_id,
project_id, task_id,
owning_org_id, owning_inst_id,
ship_method, analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
exception_type,
exception_count,
exception_value,
exception_value2,
exception_days,
exception_quantity,
exception_ratio,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category-mfg_period (1016, 1017, 1018)
select
f.plan_id, f.plan_run_id,
f.organization_id, f.sr_instance_id, f.inventory_item_id,
f.department_id, f.resource_id,
f.supplier_id, f.supplier_site_id, f.supplier_region_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.project_id, f.task_id,
f.owning_org_id, f.owning_inst_id,
f.ship_method,
mp.period_start_date analysis_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
f.category_set_id,
f.sr_category_id,
'-23453' resource_group,
f.exception_type,
sum(f.exception_count),
sum(f.exception_value),
sum(f.exception_value2),
sum(f.exception_days),
sum(f.exception_quantity),
sum(f.exception_ratio),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_exceptions_f f,
msc_phub_mfg_cal_periods_mv mp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type between 42 and 44
and f.analysis_date between mp.period_start_date and mp.period_end_date
group by
f.plan_id, f.plan_run_id,
f.organization_id, f.sr_instance_id, f.inventory_item_id,
f.department_id, f.resource_id,
f.supplier_id, f.supplier_site_id, f.supplier_region_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.project_id, f.task_id,
f.owning_org_id, f.owning_inst_id,
f.ship_method,
mp.period_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
f.category_set_id,
f.sr_category_id,
f.exception_type;
DELETE FROM msc_exceptions_f
WHERE plan_id = p_plan_id
and plan_run_id = nvl(p_plan_run_id,plan_run_id);