[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_existing_flag (
p_errnum OUT NOCOPY NUMBER,
p_errmesg OUT NOCOPY VARCHAR2);
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select trunc(min(calendar_date))
into g_date_from
from bom_calendar_dates ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
Delete_Temp_Info(p_group_id => x_group_id);
update_existing_flag (p_errnum => p_errnum,
p_errmesg => p_errmesg);
Delete_Temp_Info(p_group_id => x_group_id);
Delete_Temp_Info(p_group_id =>x_group_id);
Delete_Temp_Info(p_group_id =>x_group_id);
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select trunc(min(calendar_date))
into g_date_from
from bom_calendar_dates ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
Delete_Temp_Info (p_group_id => x_group_id);
SELECT DISTINCT organization_id
FROM mtl_parameters
WHERE process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
select trunc(min(calendar_date))
into g_date_from
from bom_calendar_dates ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
INSERT INTO wip_bis_mnra_temp (
shift_date,
resource_id,
department_id,
organization_id,
--simulation_set,
available_hours
)
SELECT
trunc (shift_date),
resource_id,
department_id,
organization_id,
--simulation_set, -- Not used after that --3779182
--sum(((to_time-from_time)/3600)*capacity_units) --BUG - 3581581
-- sum(((decode(sign(to_time - from_time),
-- -1, ( 86400 - from_time ) + to_time,
-- 1, ( to_time - from_time ) ,
-- 0 ))/3600)*capacity_units)
decode(sum(shift_num),
0, nvl(sum(capacity_units)*24,0),
nvl(sum(((decode(sign(to_time - from_time),
-1, ( 86400 - from_time ) + to_time,
1, ( to_time - from_time ) ,
0 ))/3600)*capacity_units),0))
FROM mrp_net_resource_avail
WHERE simulation_set IS NULL
and shift_date BETWEEN x_date_from AND (x_date_to + 0.99999)
GROUP BY
trunc (shift_date),
resource_id,
department_id,
organization_id;--,
INSERT INTO wip_bis_mnra_temp (
shift_date,
resource_id,
department_id,
organization_id,
-- simulation_set,
available_hours
)
SELECT
trunc (shift_date),
resource_id,
department_id,
organization_id,
-- simulation_set,
--sum(((to_time-from_time)/3600)*capacity_units) --BUG - 3581581
-- sum(((decode(sign(to_time - from_time),
-- -1, ( 86400 - from_time ) + to_time,
-- 1, ( to_time - from_time ) ,
-- 0 ))/3600)*capacity_units)
decode(sum(shift_num),
0, nvl(sum(capacity_units)*24,0),
nvl(sum(((decode(sign(to_time - from_time),
-1, ( 86400 - from_time ) + to_time,
1, ( to_time - from_time ) ,
0 ))/3600)*capacity_units),0))
FROM mrp_net_resource_avail mrp_outer
WHERE simulation_set IS NULL
and mrp_outer.shift_date BETWEEN x_date_from AND (x_date_to + 0.99999) --3779182
AND mrp_outer.resource_id = nvl(p_resource_id, mrp_outer.resource_id)
AND mrp_outer.department_id = nvl(p_department_id, mrp_outer.department_id)
AND mrp_outer.organization_id = nvl(p_organization_id, mrp_outer.organization_id)
and not exists
( select null
from wip_bis_mnra_temp mrp_inner
where mrp_outer.shift_date between trunc (mrp_inner.shift_date) and( trunc (mrp_inner.shift_date)+ 0.99999)
and mrp_outer.resource_id= mrp_inner.resource_id
and mrp_outer.department_id= mrp_inner.department_id
and mrp_outer.organization_id = mrp_inner.organization_id
)
GROUP BY
trunc (shift_date),
resource_id,
department_id,
organization_id; --,
INSERT INTO wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
resource_id,
resource_code,
wip_entity_id,-- added for bug 3604065
operation_seq_num, -- bug 3662056
applied_units_utz,
available_units,
transaction_date,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
SELECT
x_group_id group_id,
utz_rows.organization_id,
utz_rows.department_id,
utz_rows.department_code,
utz_rows.resource_id,
utz_rows.resource_code,
utz_rows.wip_entity_id, -- Bug 3604065
utz_rows.operation_seq_num, --bug 3662056
utz_rows.applied_units_utz,
nvl (mnra_temp.available_hours, 0) available_units,
utz_rows.transaction_date,
WIP_UTILIZATION indicator_type,
WIP_UTZ_PHASE_TWO process_phase,
sysdate last_update_date,
g_userid last_updated_by,
SYSDATE creation_date,
g_userid created_by,
g_applicationid program_application_id
FROM wip_bis_mnra_temp mnra_temp,
(SELECT
wt.organization_id organization_id,
bd.department_id department_id,
bd.department_code department_code,
wt.resource_id resource_id,
br.resource_code resource_code,
wt.wip_entity_id wip_entity_id, -- Bug 3604065
wt.operation_seq_num operation_seq_num, --bug 3662056
trunc(wt.transaction_date) transaction_date,
sum(inv_convert.inv_um_convert(0,NULL,wt.primary_quantity,
wt.primary_uom,g_uom_code,
NULL,NULL)) applied_units_utz
FROM
bom_resources br,
bom_departments bd,
bom_department_resources bdr,
wip_transactions wt,
mtl_units_of_measure muom
WHERE
wt.transaction_date BETWEEN x_date_from AND
(x_date_to + 0.99999)
AND wt.resource_id = nvl(p_resource_id, wt.resource_id)
AND wt.department_id = nvl(p_department_id, wt.department_id)
AND wt.organization_id = nvl(p_organization_id,
wt.organization_id)
AND wt.transaction_type in (1, 3)
AND bdr.resource_id = wt.resource_id
AND bdr.department_id = wt.department_id
AND bd.department_id = nvl(bdr.share_from_dept_id,
bdr.department_id)
AND bd.organization_id = wt.organization_id
AND br.resource_id = wt.resource_id
AND br.unit_of_measure = muom.uom_code
AND muom.uom_class = g_uom_class
AND br.organization_id = wt.organization_id
GROUP BY
wt.organization_id,
bd.department_id,
bd.department_code,
wt.resource_id,
br.resource_code,
wt.wip_entity_id,-- Bug 3604065
wt.operation_seq_num, --bug 3662056
trunc(wt.transaction_date)) utz_rows
WHERE mnra_temp.organization_id (+) = utz_rows.organization_id
AND mnra_temp.department_id (+) = utz_rows.department_id
AND mnra_temp.resource_id (+) = utz_rows.resource_id
AND mnra_temp.shift_date (+) = utz_rows.transaction_date;
update wip_indicators_temp wbpi
set wbpi.available_units = 0
where wbpi.available_units is not null
and wbpi.indicator_type=WIP_UTILIZATION
and wbpi.process_phase= WIP_UTZ_PHASE_TWO
and wbpi.group_id=x_group_id
and wbpi.wip_entity_id <>
(select min(wit.wip_entity_id)
from wip_indicators_temp wit
where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
and wbpi.resource_id = wit.resource_id
and wbpi.department_id = wit.department_id
and wbpi.organization_id = wit.organization_id
and wbpi.group_id=wit.group_id
and wit.indicator_type=WIP_UTILIZATION
and wit.process_phase= WIP_UTZ_PHASE_TWO);
update wip_indicators_temp wbpi
set wbpi.available_units = 0
where wbpi.available_units is not null
and wbpi.indicator_type=WIP_UTILIZATION
and wbpi.process_phase= WIP_UTZ_PHASE_TWO
and wbpi.group_id=x_group_id
and wbpi.operation_seq_num <>
(select min(wit.operation_seq_num)
from wip_indicators_temp wit
where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
and wbpi.resource_id = wit.resource_id
and wbpi.department_id = wit.department_id
and wbpi.organization_id = wit.organization_id
and wbpi.wip_entity_id = wit.wip_entity_id
and wbpi.group_id=wit.group_id
and wit.indicator_type=WIP_UTILIZATION
and wit.process_phase= WIP_UTZ_PHASE_TWO);
Delete_Temp_Info (p_group_id => x_group_id);
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select trunc(min(calendar_date))
into g_date_from
from bom_calendar_dates ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
Delete_Temp_Info (p_group_id => x_group_id);
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
wip_entity_id,
operation_seq_num,
indicator_type,
process_phase,
transaction_date,
applied_units_prd,
standard_units,
standard_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
p_group_id,
wmt.organization_id,
wo.department_id,
bd.department_code,
wmt.wip_entity_id,
wo.operation_seq_num,
p_indicator,
WIP_EFF_PHASE_ONE, /* First Process Phase */
trunc(wmt.transaction_date),
null,
null,
sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
0, -- Within the same operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, -- From Queue
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2 , 0,
1, 0, -- this is not possible but still
(wmt.primary_quantity)
),
2, -- From Run
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0, -- this is not possible but still
(wmt.primary_quantity)
),
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(-1*wmt.primary_quantity)
)
),
-1, -- Move in the positive direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
decode( wo.count_point_type,
3, 0,
wmt.primary_quantity)
),
decode( wo.count_point_type,
3, 0,
(wmt.primary_quantity)
)
),
1, -- Move in the negative direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
3, 0,
(-1*wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
decode( wo.count_point_type,
3, 0,
-1*wmt.primary_quantity)
),
decode( wo.count_point_type,
3, 0,
(-1*wmt.primary_quantity)
)
)
) ) "Quantity",
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
wip_move_transactions wmt,
wip_operations wo,
bom_departments bd
where
trunc(wmt.transaction_date) between trunc(nvl(p_date_from,wmt.transaction_date))
and trunc(nvl(p_date_to,wmt.transaction_date))
and wo.operation_seq_num <= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
-1,wmt.TO_OPERATION_SEQ_NUM, 1, wmt.FM_OPERATION_SEQ_NUM,
wmt.FM_OPERATION_SEQ_NUM)
and wo.operation_seq_num >= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
-1,wmt.FM_OPERATION_SEQ_NUM, 1, wmt.TO_OPERATION_SEQ_NUM,
wmt.FM_OPERATION_SEQ_NUM)
and wmt.organization_id = wo.organization_id
and wo.department_id = bd.department_id
and wo.wip_entity_id = wmt.wip_entity_id
and wo.organization_id = bd.organization_id
and wo.department_id = nvl(p_department_id, wo.department_id)
and bd.organization_id = nvl(p_organization_id, bd.organization_id)
group by
wmt.organization_id,
wo.department_id,
bd.department_code,
wmt.wip_entity_id,
wo.operation_seq_num,
p_indicator,
1,
trunc(wmt.transaction_date),
null,
null,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
having sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
0, -- Within the same operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, -- From Queue
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2 , 0,
1, 0, -- this is not possible but still
(wmt.primary_quantity)
),
2, -- From Run
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0, -- this is not possible but still
(wmt.primary_quantity)
),
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(-1*wmt.primary_quantity)
)
),
-1, -- Move in the positive direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
decode( wo.count_point_type,
3, 0,
wmt.primary_quantity)
),
decode( wo.count_point_type,
3, 0,
(wmt.primary_quantity)
)
),
1, -- Move in the negative direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
3, 0,
(-1*wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
decode( wo.count_point_type,
3, 0,
-1*wmt.primary_quantity)
),
decode( wo.count_point_type,
3, 0,
(-1*wmt.primary_quantity)
)
)
) ) <> 0 ;
2. Delete the original rows + lot based resource
transactions except for the first transaction
information.
The summarization and steps across the various combinations
have been commented out long back and are being removed from the
file. (digupta 10/02/03).
*/
PROCEDURE Calculate_Std_Units(
p_group_id IN NUMBER,
p_resource_id IN NUMBER,
p_errnum OUT NOCOPY NUMBER,
p_errmesg OUT NOCOPY VARCHAR2,
p_indicator IN NUMBER )
IS
x_step NUMBER ;
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
standard_quantity,
resource_id,
resource_code,
wip_entity_id,
operation_seq_num,
resource_basis,
indicator_type,
process_phase,
transaction_date,
standard_units,
applied_units_prd,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
wit.group_id,
wit.organization_id,
decode(wit.indicator_type,
WIP_EFFICIENCY, wit.department_id,
WIP_PRODUCTIVITY,
nvl(bdr.share_from_dept_id,wit.department_id)
),
decode(wit.indicator_type,
WIP_EFFICIENCY, wit.department_code,
WIP_PRODUCTIVITY,
nvl(bd.department_code, wit.department_code)),
wit.standard_quantity,
wor.resource_id,
br.resource_code,
wit.wip_entity_id,
wit.operation_seq_num,
wor.basis_type,
wit.indicator_type,
WIP_EFF_PHASE_TWO, /* This is second stage */
transaction_date, -- already trunc'ed
inv_convert.inv_um_convert(0,
NULL,
decode(wor.basis_type,
1, (wit.standard_quantity*
wor.usage_rate_or_amount),
2, (wit.standard_quantity)),
wor.uom_code,
g_uom_code,
NULL,
NULL),
null,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.program_application_id
from wip_indicators_temp wit,
bom_resources br,
bom_departments bd,
bom_department_resources bdr,
wip_operation_resources wor,
mtl_units_of_measure muom
where
wor.wip_entity_id = wit.wip_entity_id
and wor.operation_seq_num = wit.operation_seq_num
and wit.indicator_type = p_indicator
and wor.resource_id = nvl(p_resource_id, wor.resource_id)
and br.organization_id = wor.organization_id
and br.resource_id = wor.resource_id
and bdr.resource_id = br.resource_id
and bdr.department_id = wit.department_id
and bd.department_id (+) = bdr.share_from_dept_id
and wor.uom_code = muom.uom_code
and muom.uom_class = g_uom_class;
delete from wip_indicators_temp wit
where wit.indicator_type = p_indicator
and ( ( wit.process_phase = WIP_EFF_PHASE_ONE )
or ( wit.process_phase = WIP_EFF_PHASE_TWO
and wit.resource_basis = 2
and wit.transaction_date >
(
select min(transaction_date)
from wip_indicators_temp wit2
where wit2.wip_entity_id = wit.wip_entity_id
and wit2.indicator_type = wit.indicator_type
and wit2.operation_seq_num = wit.operation_seq_num
and wit2.resource_id = wit.resource_id
and wit2.resource_basis = 2)
)
);
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
/* update wip_indicators_temp wit
set APPLIED_UNITS_PRD = (
select nvl(wt.primary_quantity,0)
from wip_transactions wt
where wt.organization_id = wit.organization_id
and wt.transaction_date BETWEEN trunc(wit.transaction_date)
and trunc (wit.transaction_date) + 0.999999
and wt.transaction_type in (1, 3)
and wt.operation_seq_num = wit.operation_seq_num
AND wt.wip_entity_id = wit.wip_entity_id
and wt.department_id = wit.department_id
and wt.resource_id = wit.resource_id
)
where wit.indicator_type = WIP_EFFICIENCY
AND wit.process_phase = WIP_EFF_PHASE_THREE ;
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
insert into wip_indicators_temp(
group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
standard_quantity,
standard_units,
applied_units_prd,
transaction_date,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
p_group_id,
wt.organization_id,
wt.wip_entity_id,
wt.operation_seq_num,
bd.department_id,
bd.department_code,
wt.resource_id,
br.resource_code,
0,
0,
sum(inv_convert.inv_um_convert(0,NULL,wt.primary_quantity,
wt.primary_uom,g_uom_code,NULL,NULL)),
trunc(wt.transaction_date),
WIP_EFFICIENCY,
WIP_EFF_PHASE_THREE, -- this is the third and final phase
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
bom_resources br,
bom_departments bd,
bom_department_resources bdr,
wip_transactions wt,
mtl_units_of_measure muom
where
wt.transaction_date between trunc(p_date_from)
and trunc(p_date_to) + 0.999999
and wt.resource_id = nvl(p_resource_id, wt.resource_id)
and wt.department_id = nvl(p_department_id, wt.department_id)
and wt.organization_id = nvl(p_organization_id, wt.organization_id)
and wt.transaction_type in (1, 3)
and bdr.resource_id = wt.resource_id
and bdr.department_id = wt.department_id
and bd.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
and bd.organization_id = wt.organization_id
and br.resource_id = wt.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = wt.organization_id
group by
wt.organization_id,
wt.wip_entity_id,
wt.operation_seq_num,
bd.department_id,
bd.department_code,
wt.resource_id,
br.resource_code,
trunc(wt.transaction_date);
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
wip_entity_id,
operation_seq_num,
indicator_type,
process_phase,
transaction_date,
total_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
p_group_id,
wmt.organization_id,
wo.department_id,
bd.department_code,
wmt.wip_entity_id,
wo.operation_seq_num,
WIP_YIELD,
1, /* this is the first step */
trunc(wmt.transaction_date),
sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
0, -- Within the same operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, -- From Queue
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2 , 0,
1, 0, -- this is not possible but still
(wmt.primary_quantity)
),
2, -- From Run
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0, -- this is not possible but still
(wmt.primary_quantity)
),
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(-1*wmt.primary_quantity)
)
),
-1, -- Move in the positive direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
(wmt.primary_quantity)
),
(wmt.primary_quantity)
),
1, -- Move in the negative direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
(-1*wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(-1*wmt.primary_quantity)
),
(-1*wmt.primary_quantity)
)
) ) "Quantity",
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
wip_move_transactions wmt,
wip_operations wo,
bom_departments bd
where trunc(wmt.transaction_date) between trunc(nvl(p_date_from,wmt.transaction_date))
and trunc(nvl(p_date_to,wmt.transaction_date))
-- below statement is equavivalent to between only. Dont know why such a complex condition.
-- and wo.operation_seq_num between wmt.FM_OPERATION_SEQ_NUM and wmt.TO_OPERATION_SEQ_NUM
and wo.operation_seq_num <= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
-1,wmt.TO_OPERATION_SEQ_NUM, 1, wmt.FM_OPERATION_SEQ_NUM,
wmt.FM_OPERATION_SEQ_NUM)
and wo.operation_seq_num >= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
-1,wmt.FM_OPERATION_SEQ_NUM, 1, wmt.TO_OPERATION_SEQ_NUM,
wmt.FM_OPERATION_SEQ_NUM)
and wmt.organization_id = wo.organization_id
and wo.wip_entity_id = wmt.wip_entity_id
and wo.organization_id = bd.organization_id
and wo.department_id = bd.department_id
and wo.department_id = nvl(p_department_id, wo.department_id)
and bd.organization_id = nvl(p_organization_id,bd.organization_id)
group by
wmt.organization_id,
wo.department_id,
bd.department_code,
wmt.wip_entity_id,
wo.operation_seq_num,
trunc(wmt.transaction_date),
WIP_YIELD,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
having sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
0, -- Within the same operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, -- From Queue
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2 , 0,
1, 0, -- this is not possible but still
(wmt.primary_quantity)
),
2, -- From Run
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0, -- this is not possible but still
(wmt.primary_quantity)
),
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, .99,--instead of 0 it is made .99 for bug 3280671
(-1*wmt.primary_quantity)
)
),
-1, -- Move in the positive direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
(wmt.primary_quantity)
),
(wmt.primary_quantity)
),
1, -- Move in the negative direction
decode( wo.operation_seq_num,
wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1, 0,
2, 0,
(-1*wmt.primary_quantity)
),
wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
3, 0,
4, 0,
5, 0,
(-1*wmt.primary_quantity)
),
(-1*wmt.primary_quantity)
)
) ) <> 0 ;
SELECT /* WIP_MOVE_TRANSACTIONS_N2 */
organization_id,
wip_entity_id,
fm_operation_seq_num,
to_operation_seq_num,
fm_intraoperation_step_type,
to_intraoperation_step_type,
primary_quantity,
trunc(transaction_date) transaction_date
FROM wip_move_transactions
WHERE ( trunc(transaction_date) >= trunc(nvl(p_date_from,transaction_date))
AND trunc(transaction_date) <= trunc(nvl(p_date_to,transaction_date)) )-- Or is replace AND Bug 3280671
--( trunc(transaction_date) >= trunc(nvl(p_date_from,transaction_date))
--OR trunc(transaction_date) <= trunc(nvl(p_date_to,transaction_date)) )
AND organization_id = nvl(p_org_id,organization_id)
AND fm_intraoperation_step_type = 5 ;
UPDATE wip_indicators_temp wit
SET wit.scrap_quantity = (
SELECT nvl(sum(wmt.primary_quantity),0)
FROM wip_move_transactions wmt
WHERE wmt.wip_entity_id = wit.wip_entity_id
AND wmt.to_operation_seq_num = wit.operation_seq_num
AND wmt.organization_id = wit.organization_id
AND wmt.to_intraoperation_step_type = 5
AND wmt.fm_intraoperation_step_type <> 5
AND wmt.transaction_date BETWEEN
nvl(p_date_from, wmt.transaction_date)
AND nvl(p_date_to + 0.99999,
wmt.transaction_date)
AND wmt.transaction_date BETWEEN wit.transaction_date
AND wit.transaction_date + 0.99999
),
wit.process_phase = WIP_DEPT_YIELD /* process phase 2 */
WHERE wit.indicator_type = WIP_YIELD;
update wip_indicators_temp
set scrap_quantity = (scrap_quantity -
Adj_Rec.Primary_Quantity)
where indicator_type = WIP_YIELD
and process_phase = WIP_DEPT_YIELD
and organization_id = Adj_Rec.organization_id
and wip_entity_id = Adj_Rec.Wip_Entity_id
and operation_seq_num = Adj_Rec.fm_operation_seq_num
and transaction_date = Adj_Rec.transaction_date ;
update wip_indicators_temp
set scrap_quantity = (scrap_quantity +
Adj_Rec.Primary_Quantity)
where indicator_type = WIP_YIELD
and process_phase = WIP_DEPT_YIELD
and organization_id = Adj_Rec.organization_id
and wip_entity_id = Adj_Rec.Wip_Entity_id
and operation_seq_num = Adj_Rec.to_operation_seq_num
and transaction_date = Adj_Rec.transaction_date ;
update wip_indicators_temp
set scrap_quantity = (scrap_quantity -
Adj_Rec.Primary_Quantity)
where indicator_type = WIP_YIELD
and process_phase = WIP_DEPT_YIELD
and organization_id = Adj_Rec.organization_id
and wip_entity_id = Adj_Rec.Wip_Entity_id
and operation_seq_num = Adj_Rec.fm_operation_seq_num
and transaction_date = Adj_Rec.transaction_date ;
insert into wip_indicators_temp(
group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
total_quantity,
scrap_quantity,
transaction_date,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
wit.group_id,
wit.organization_id,
wit.wip_entity_id,
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wor.resource_id,
br.resource_code,
sum(wit.total_quantity),
sum(wit.scrap_quantity),
wit.transaction_date, -- already trunc'ed
wit.indicator_type,
WIP_RES_YIELD, /* This is the resource phase */
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.program_application_id
from wip_indicators_temp wit,
bom_resources br,
wip_operation_resources wor
where wor.wip_entity_id = wit.wip_entity_id
and wor.operation_seq_num = wit.operation_seq_num
and wit.indicator_type = WIP_YIELD
and wit.process_phase = WIP_DEPT_YIELD
and br.organization_id = wor.organization_id
and br.resource_id = wor.resource_id
group by
wit.group_id,
wit.organization_id,
wit.wip_entity_id,
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wor.resource_id,
br.resource_code,
wit.transaction_date,
wit.indicator_type,
WIP_RES_YIELD,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.program_application_id ;
delete from wip_indicators_temp
where indicator_type = WIP_YIELD
and process_phase = 1 ;
insert into wip_bis_prod_indicators (
ORGANIZATION_ID,
WIP_ENTITY_ID,
INVENTORY_ITEM_ID,
TRANSACTION_DATE,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
DEPARTMENT_CODE,
RESOURCE_ID,
RESOURCE_CODE,
STANDARD_HOURS,
APPLIED_HOURS_PRD,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
select wit.organization_id,
wit.wip_entity_id,
we.primary_item_id,
trunc(wit.transaction_date),
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wit.resource_id,
wit.resource_code,
wit.standard_units,
wit.applied_units_prd,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.last_update_login,
wit.request_id,
wit.program_application_id,
sysdate
from wip_entities we,
wip_bis_eff_temp wit
where we.wip_entity_id = wit.wip_entity_id
and we.organization_id = wit.organization_id
and wit.indicator_type = WIP_EFFICIENCY
and not exists (
select null
from wip_bis_prod_indicators wbpi
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wbpi.transaction_date between
trunc(wit.transaction_date)
and trunc(wit.transaction_date) + 0.99999
) ;
update/*+ PARALLEL*/ wip_bis_prod_indicators wbpi
set (wbpi.APPLIED_HOURS_UTZ, wbpi.AVAILABLE_HOURS) =
( select wit.applied_units_utz, wit.available_units
from wip_bis_utz_temp wit
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wit.transaction_date BETWEEN
trunc(wbpi.transaction_date)
AND trunc (wbpi.transaction_date) + 0.99999
and wit.indicator_type = WIP_UTILIZATION
)
where wbpi.APPLIED_HOURS_UTZ is null
and wbpi.AVAILABLE_HOURS is null ;
update /*+ INDEX(wbpi WIP_BIS_PROD_INDICATORS_N8) */ wip_bis_prod_indicators wbpi
set wbpi.AVAILABLE_HOURS = 0
where wbpi.AVAILABLE_HOURS is not null
and wbpi.wip_entity_id <>
(select /*+ INDEX(wit WIP_BIS_PROD_INDICATORS_N8) INDEX_FFS(wit WIP_BIS_PROD_INDICATORS_N8)*/ min(wit.wip_entity_id)
from wip_bis_prod_indicators wit
where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
and wbpi.resource_id = wit.resource_id
and wbpi.department_id = wit.department_id
and wbpi.organization_id = wit.organization_id );
update /*+ INDEX(wbpi WIP_BIS_PROD_INDICATORS_N8) */ wip_bis_prod_indicators wbpi
set wbpi.AVAILABLE_HOURS = 0
where wbpi.AVAILABLE_HOURS is not null
and wbpi.operation_seq_num <>
(select /*+ INDEX(wit WIP_BIS_PROD_INDICATORS_N8) INDEX_FFS(wit WIP_BIS_PROD_INDICATORS_N8)*/ min(wit.operation_seq_num)
from wip_bis_prod_indicators wit
where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
and wbpi.resource_id = wit.resource_id
and wbpi.department_id = wit.department_id
and wbpi.organization_id = wit.organization_id
and wbpi.wip_entity_id = wip_entity_id);
update wip_bis_prod_indicators wbpi
set (wbpi.TOTAL_QUANTITY, wbpi.SCRAp_QUANTITY) =
( select wit.total_quantity, wit.scrap_quantity
from wip_bis_yld_temp wit
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wit.transaction_date BETWEEN trunc(wbpi.transaction_date)
and trunc (wbpi.transaction_date) + 0.99999
and wit.indicator_type = WIP_YIELD
and wit.process_phase = WIP_RES_YIELD
)
where wbpi.TOTAL_QUANTITY is null
and wbpi.SCRAP_QUANTITY is null ;
insert into wip_bis_prod_indicators (
ORGANIZATION_ID,
WIP_ENTITY_ID,
INVENTORY_ITEM_ID,
TRANSACTION_DATE,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
DEPARTMENT_CODE,
RESOURCE_ID,
RESOURCE_CODE,
APPLIED_HOURS_UTZ,
AVAILABLE_HOURS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
select wit.organization_id,
wit.wip_entity_id,
we.primary_item_id,
trunc(wit.transaction_date),
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wit.resource_id,
wit.resource_code,
wit.applied_units_utz,
wit.available_units,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.last_update_login,
wit.request_id,
wit.program_application_id,
sysdate
from wip_entities we,
wip_bis_utz_temp wit
where we.wip_entity_id = wit.wip_entity_id
and we.organization_id = wit.organization_id
and wit.indicator_type = WIP_UTILIZATION
and not exists (
select null
from wip_bis_prod_indicators wbpi
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wbpi.transaction_date between
trunc(wit.transaction_date)
and trunc(wit.transaction_date) + 0.99999);
update wip_bis_prod_indicators wbpi
set (wbpi.total_quantity, wbpi.scrap_quantity) =
( select wit.total_quantity, wit.scrap_quantity
from wip_bis_yld_temp wit
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wit.transaction_date BETWEEN trunc(wbpi.transaction_date)
and trunc (wbpi.transaction_date) + 0.99999
and wit.indicator_type = WIP_YIELD
and wit.process_phase = WIP_RES_YIELD
)
where wbpi.total_quantity is null
and wbpi.scrap_quantity is null ;
insert into wip_bis_prod_indicators (
ORGANIZATION_ID,
WIP_ENTITY_ID,
INVENTORY_ITEM_ID,
TRANSACTION_DATE,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
DEPARTMENT_CODE,
RESOURCE_ID,
RESOURCE_CODE,
TOTAL_QUANTITY,
SCRAP_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
select wit.organization_id,
wit.wip_entity_id,
we.primary_item_id,
trunc(wit.transaction_date),
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wit.resource_id,
wit.resource_code,
wit.total_quantity,
wit.scrap_quantity,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.last_update_login,
wit.request_id,
wit.program_application_id,
sysdate
from wip_entities we,
wip_bis_yld_temp wit
where we.wip_entity_id = wit.wip_entity_id
and we.organization_id = wit.organization_id
and wit.indicator_type = WIP_YIELD
and wit.process_phase = WIP_RES_YIELD
and not exists
(select null
from wip_bis_prod_indicators wbpi
where wit.organization_id = wbpi.organization_id
and wit.wip_entity_id = wbpi.wip_entity_id
and wit.operation_seq_num = wbpi.operation_seq_num
and wit.department_id = wbpi.department_id
and wit.resource_id = wbpi.resource_id
and wbpi.transaction_date between trunc(wit.transaction_date)
and trunc(wit.transaction_date) + 0.99999);
INSERT INTO wip_bis_prod_dept_yield (
ORGANIZATION_ID,
WIP_ENTITY_ID,
INVENTORY_ITEM_ID,
TRANSACTION_DATE,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
DEPARTMENT_CODE,
TOTAL_QUANTITY,
SCRAP_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
SELECT wit.organization_id,
wit.wip_entity_id,
we.primary_item_id,
wit.transaction_date,
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wit.total_quantity,
wit.scrap_quantity,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.last_update_login,
wit.request_id,
wit.program_application_id,
sysdate
FROM wip_entities we,
wip_bis_yld_temp wit
WHERE we.wip_entity_id = wit.wip_entity_id
AND we.organization_id = wit.organization_id
AND wit.indicator_type = WIP_YIELD
AND wit.process_phase = WIP_DEPT_YIELD;
SELECT sum (available_hours)
INTO l_all_available_hours
FROM wip_bis_mnra_temp;
SELECT count (*)
INTO l_wit_utz_size
FROM (SELECT distinct organization_id,
resource_id,
department_id,
transaction_date
FROM wip_bis_utz_temp
WHERE process_phase = WIP_UTZ_PHASE_TWO
AND indicator_type = WIP_UTILIZATION) wit_distinct;
insert into wip_bis_prod_indicators(
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
applied_hours_utz,
AVAILABLE_HOURS,
transaction_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
mnra.organization_id,
null,
null,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
null,
decode (wit.net_occurances,
NULL, l_wit_utz_size * mnra.available_hours,
(l_wit_utz_size - net_occurances) * mnra.available_hours),
mnra.shift_date, -- already trunc'ed
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
FROM
(SELECT organization_id,
department_id,
resource_id,
transaction_date,
count (*) net_occurances
FROM wip_bis_utz_temp
WHERE indicator_type = WIP_UTILIZATION
AND process_phase = WIP_UTZ_PHASE_TWO
GROUP BY organization_id,
department_id,
resource_id,
transaction_date) wit,
bom_resources br,
bom_departments bd,
wip_bis_mnra_temp mnra,
mtl_units_of_measure muom
where mnra.shift_date BETWEEN trunc(g_date_from)
AND trunc (g_date_to) + 0.99999
and br.resource_id = mnra.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = mnra.organization_id
and bd.department_id = mnra.department_id
and bd.organization_id = mnra.organization_id
and mnra.shift_date = wit.transaction_date(+) -- both are trunc'ed
and mnra.resource_id = wit.resource_id(+)
and mnra.department_id = wit.department_id(+)
and mnra.organization_id = wit.organization_id(+);
SELECT distinct
organization_id
FROM mtl_parameters
WHERE process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
/* Bug 3589936 - Below insert does not take care of shift times when to_time is less
than from_time for available_units. If the shift starts late night today and ends
tomorrow morning, then to_time will be less than the from_time. Now added decode
and sign to take care of the same */
insert into wip_bis_prod_indicators(
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
applied_hours_utz,
available_hours,
transaction_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
mnra1.organization_id,
null,
null,
mnra1.department_id,
mnra1.department_code,
mnra1.resource_id,
mnra1.resource_code,
null,
mnra1.available_hours,
mnra1.shift_date,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
(select
mnra.organization_id organization_id,
mnra.department_id department_id,
bd.department_code department_code,
mnra.resource_id resource_id,
br.resource_code resource_code,
decode(sum(mnra.shift_num),
0, sum(capacity_units)*24,
sum(((decode(sign(mnra.to_time - mnra.from_time),
-1, ( 86400 - mnra.from_time ) + mnra.to_time,
1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)) available_hours,
trunc(mnra.shift_date) shift_date
FROM
bom_resources br,
bom_departments bd,
mrp_net_resource_avail mnra,
mtl_units_of_measure muom
where
trunc(mnra.shift_date) between trunc(g_date_from) and trunc(g_date_to)
and trunc(mnra.shift_date) >= trunc(br.creation_date)
and br.resource_id = mnra.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = mnra.organization_id
and bd.department_id = mnra.department_id
and bd.organization_id = mnra.organization_id
and mnra.organization_id = x_org_id
group by mnra.organization_id,
mnra.department_id,
mnra.resource_id,
mnra.shift_date,
bd.department_code,
br.resource_code ) mnra1
where not exists
(select null
from wip_indicators_temp wit
where wit.group_id = p_group_id
and wit.indicator_type = WIP_UTILIZATION
and wit.process_phase = WIP_UTZ_PHASE_TWO
and mnra1.shift_date = trunc(wit.transaction_date)
and mnra1.resource_id = wit.resource_id
and mnra1.department_id = wit.department_id
and mnra1.organization_id = wit.organization_id );
Delete_Temp_Info(p_group_id=>p_group_Id);
delete from wip_bis_prod_indicators
where existing_flag is null ;
delete from wip_bis_prod_dept_yield
where existing_flag is null ;
PROCEDURE Delete_Temp_Info (p_group_id in number)
IS
BEGIN
IF NOT (fnd_installation.get_app_info(
'WIP', g_status, g_industry, g_wip_schema)) THEN
RAISE_APPLICATION_ERROR (-20000,
'Unable to get session information.');
fnd_file.put_line(fnd_file.log,'Failed in Delete_Temp_Info.');
END Delete_Temp_Info;
INSERT INTO wip_bis_prod_assy_yield (
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
SELECT
organization_id,
transaction_source_id,
inventory_item_id,
trunc (transaction_date),
sum (primary_quantity),
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid
FROM
mtl_material_transactions
WHERE transaction_source_type_id = 5
AND transaction_action_id IN (31,32)
AND organization_id = nvl(p_organization_id, organization_id)
AND transaction_date between
trunc(nvl(p_date_from,transaction_date))
and trunc(nvl(p_date_to,transaction_date)) + 0.99999
GROUP BY
organization_id,
transaction_source_id,
inventory_item_id,
trunc(transaction_date),
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid ;
UPDATE wip_bis_prod_assy_yield wbpay
SET wbpay.scrap_quantity = (
SELECT Nvl(sum(decode(wmt.fm_intraoperation_step_type,
5, -1*(primary_quantity),
decode(wmt.to_intraoperation_step_type,
5, primary_quantity,
0 ))),0)
FROM wip_move_transactions wmt
WHERE wmt.wip_entity_id = wbpay.wip_entity_id
AND wmt.organization_id = wbpay.organization_id
AND trunc(wmt.transaction_date)= trunc(wbpay.transaction_date)
AND (wmt.fm_intraoperation_step_type = 5
OR wmt.to_intraoperation_step_type = 5
AND (wmt.fm_intraoperation_step_type <> wmt.to_intraoperation_step_type))); --3280671
INSERT INTO wip_bis_prod_assy_yield(
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
scrap_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
SELECT
wmt.organization_id,
wmt.wip_entity_id,
we.primary_item_id,
trunc(wmt.transaction_date),
0,
sum(decode(wmt.fm_intraoperation_step_type,
5, -1*(primary_quantity),
decode(wmt.to_intraoperation_step_type,
5, primary_quantity,
0 ))),
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid
FROM
wip_entities we,
wip_move_transactions wmt
WHERE we.wip_entity_id = wmt.wip_entity_id
AND we.organization_id = wmt.organization_id
AND wmt.organization_id = nvl(p_organization_id,
wmt.organization_id)
AND wmt.transaction_date BETWEEN
trunc(nvl(p_date_from,wmt.transaction_date))
AND trunc(nvl(p_date_to,wmt.transaction_date)) + 0.99999
AND (wmt.fm_intraoperation_step_type = 5
OR wmt.to_intraoperation_step_type = 5
AND (wmt.fm_intraoperation_step_type <> wmt.to_intraoperation_step_type)) --3280671
-- AND (wmt.fm_intraoperation_step_type <> 5
--AND wmt.to_intraoperation_step_type <> 5))
AND NOT exists (
SELECT 'X'
FROM wip_bis_prod_assy_yield wbpay1
WHERE wbpay1.wip_entity_id = wmt.wip_entity_id
AND wbpay1.organization_id = wmt.organization_id
AND wbpay1.transaction_date BETWEEN
trunc(wmt.transaction_date) AND
trunc (wmt.transaction_date) + 0.99999)
GROUP BY
wmt.organization_id,
wmt.wip_entity_id,
we.primary_item_id,
trunc(wmt.transaction_date),
0,
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid ;
Delete_Temp_Info (p_group_id => x_group_id);
SELECT count(*)
INTO x_from_count
FROM mrp_net_resource_avail
WHERE organization_id = p_organization_id
AND shift_date BETWEEN p_date_from AND p_date_from + 0.99999
AND simulation_set is null ;
SELECT count(*)
INTO x_to_count
FROM mrp_net_resource_avail
WHERE organization_id = p_organization_id
AND shift_date BETWEEN p_date_to AND p_date_to +0.99999
AND simulation_set is null ;
productivity - to insert that we need to insert a simple cursor to go
through all the departments in an organization in bd
*/
Procedure Populate_Productivity(
p_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_date_from IN DATE,
p_date_to IN DATE,
p_department_id IN NUMBER,
p_resource_id IN NUMBER,
p_userid IN NUMBER,
p_applicationid IN NUMBER,
p_errnum OUT NOCOPY NUMBER,
p_errmesg OUT NOCOPY VARCHAR2)
IS
/**************************************************************
Cursor to get all valid inventory organizations
**************************************************************/
CURSOR All_Orgs is
SELECT distinct organization_id
FROM mtl_parameters
WHERE organization_id = nvl(p_organization_id, organization_id)
AND process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
select trunc(sysdate)
into g_date_from
from dual ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
standard_quantity,
resource_id,
resource_code,
transaction_date,
standard_units,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
group_id,
organization_id,
department_id,
department_code,
sum(standard_quantity),
resource_id,
resource_code,
transaction_date, -- already trunc'ed
sum(standard_units),
WIP_PRODUCTIVITY,
WIP_PROD_PHASE_FOUR,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id
from wip_indicators_temp
where indicator_type = WIP_PRODUCTIVITY
and process_phase = WIP_PROD_PHASE_THREE
group by
group_id,
organization_id,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
WIP_PRODUCTIVITY,
WIP_PROD_PHASE_THREE, -- This is the third Phase
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id ;
delete from wip_indicators_temp
where indicator_type = WIP_PRODUCTIVITY
and process_phase = WIP_PROD_PHASE_THREE ;
UPDATE wip_indicators_temp wit
SET wit.available_units = (
select
--nvl(sum(((to_time-from_time)/3600)*capacity_units),0)
-- nvl(sum(((decode(sign(to_time - from_time),
-- -1, ( 86400 - from_time ) + to_time,
-- 1, ( to_time - from_time ) ,
-- 0 ))/3600)*capacity_units),0)
decode(sum(shift_num),
0, nvl(sum(capacity_units)*24,0),
nvl(sum(((decode(sign(to_time - from_time),
-1, ( 86400 - from_time ) + to_time,
1, ( to_time - from_time ) ,
0 ))/3600)*capacity_units),0))
from
mrp_net_resource_avail mnra
where
mnra.organization_id = wit.organization_id
and mnra.department_id = wit.department_id
and mnra.resource_id = wit.resource_id
and wit.transaction_date between trunc(mnra.shift_date)
and trunc (mnra.shift_date) + 0.99999
and simulation_set is null
)
where wit.indicator_type = WIP_PRODUCTIVITY
and process_phase = WIP_PROD_PHASE_FOUR ;
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
resource_id,
resource_code,
standard_units,
available_units,
transaction_date,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
x_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
null,
--sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units),
--sum(((decode(sign(mnra.to_time - mnra.from_time),
-- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
-- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
decode(sum(shift_num),
0, sum(capacity_units)*24,
sum(((decode(sign(mnra.to_time - mnra.from_time),
-1, ( 86400 - mnra.from_time ) + mnra.to_time,
1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
trunc(mnra.shift_date),
WIP_PRODUCTIVITY,
WIP_PROD_PHASE_FOUR,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
bom_resources br,
bom_departments bd,
mrp_net_resource_avail mnra,
mtl_units_of_measure muom
where
mnra.shift_date between trunc(x_date_from) and
trunc(x_date_to) + 0.99999
and br.resource_id = mnra.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = mnra.organization_id
and bd.department_id = mnra.department_id
and bd.organization_id = mnra.organization_id
and mnra.organization_id = p_organization_id
and mnra.department_id = p_department_id
and mnra.resource_id = nvl(p_resource_id, mnra.resource_id)
and mnra.shift_date not in (
select distinct transaction_date
from wip_indicators_temp wit
where wit.resource_id = nvl(p_resource_id, wit.resource_id)
and wit.department_id = p_department_id
and wit.organization_id = p_organization_id
and wit.indicator_type = WIP_PRODUCTIVITY
and wit.process_phase = WIP_PROD_PHASE_FOUR
and wit.transaction_date between
trunc(x_date_from) and trunc(x_date_to) + 0.99999
)
group by
x_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
null,
trunc(mnra.shift_date),
WIP_PRODUCTIVITY,
WIP_PROD_PHASE_FOUR,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid ;
Delete_Temp_Info(p_group_id=>x_group_Id);
select distinct organization_id, department_id, resource_id
from bom_department_resources_v bdrv,
mtl_units_of_measure muom
where bdrv.organization_id = nvl(p_organization_id, organization_id)
and bdrv.department_id = nvl(p_department_id, department_id)
and bdrv.resource_id = nvl(p_resource_id, resource_id)
AND bdrv.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
AND bdrv.share_from_dept_id IS null ;
SELECT distinct organization_id
FROM mtl_parameters
WHERE organization_id = nvl(p_organization_id, organization_id)
AND process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select uom_class
into g_uom_class
from mtl_units_of_measure
where uom_code = g_uom_code;
select trunc(sysdate)
into g_date_from
from dual ;
select trunc(max(calendar_date))
into g_date_to
from bom_calendar_dates ;
select trunc(min(start_date)), trunc(max(completion_date))
into x_sim_date_from, x_sim_date_to
from wip_operation_resources
where trunc(start_date) between trunc(x_date_from)
and trunc(x_date_to)
or trunc(completion_date) between trunc(x_date_from)
and trunc(x_date_to) ;
insert into wip_indicators_temp (
group_id,
organization_id,
resource_id,
resource_code,
department_id,
department_code,
transaction_date,
available_units,
required_hours,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
x_group_id,
wor.organization_id,
wor.resource_id,
wor.resource_code,
bdr.department_id,
bd.department_code,
mnra.shift_date,
null,
decode(sign(sum(inv_convert.inv_um_convert(0,NULL,decode(wor.basis_type,
1,
NVL((wor.usage_rate_or_amount*wo.scheduled_quantity
- nvl(wor.applied_resource_units,0)),0)*
get_Workday_Ratio(wor.resource_id, wor.organization_id,trunc(wor.start_date), trunc(wor.completion_date), trunc(mnra.shift_date)),
DECODE(nvl(wor.applied_resource_units,0),
0,
decode(trunc(wor.start_date),
trunc(mnra.shift_date),
NVL(wor.usage_rate_or_amount,0),
0),
0)
),wor.uom_code,g_uom_code,NULL,NULL
))),1,sum(inv_convert.inv_um_convert(0,NULL,decode(wor.basis_type,
1,
NVL((wor.usage_rate_or_amount*wo.scheduled_quantity
- nvl(wor.applied_resource_units,0)),0)*
get_Workday_Ratio(wor.resource_id, wor.organization_id,trunc(wor.start_date), trunc(wor.completion_date), trunc(mnra.shift_date)),
DECODE(nvl(wor.applied_resource_units,0),
0,
decode(trunc(wor.start_date),
trunc(mnra.shift_date),
NVL(wor.usage_rate_or_amount,0),
0),
0)
),wor.uom_code,g_uom_code,NULL,NULL
)),0) "Required",
WIP_RESOURCE_LOAD, -- Indicator Type
WIP_RL_PHASE_ONE, -- process phase
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid
from
mrp_net_resource_avail mnra,
bom_departments bd,
bom_department_resources bdr,
wip_operations_v wo,
wip_operation_resources_v wor,
wip_discrete_jobs wdj
where
wdj.wip_entity_id = wor.wip_entity_id
and wdj.organization_id = wor.organization_id
and wdj.status_type in (1, 3, 6 ) -- unreleased, released and hold
and mnra.simulation_set is null
and mnra.resource_id = wor.resource_id
and mnra.organization_id = wor.organization_id
and wor.organization_id = nvl(Dept_Res_Rec.Organization_id, wor.organization_id)
and wor.resource_id = nvl(Dept_Res_Rec.resource_id, wor.resource_id)
and mnra.shift_date between trunc(wor.start_date) and trunc(wor.completion_date) + 0.99999
and ( ( wor.start_date between trunc(x_date_from)
and trunc(x_date_to) + 0.99999
)
or ( wor.completion_date between trunc(x_date_from)
and trunc(x_date_to) + 0.99999
)
or ( wor.start_date < trunc(x_date_from) + 0.99999 and
wor.completion_date > trunc(x_date_to) + 0.99999
)
)
and mnra.shift_date between trunc(x_date_from)
and trunc(x_date_to) + 0.99999
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and nvl(wo.repetitive_schedule_id,-999) = nvl(wor.repetitive_schedule_id, -999)
and bdr.resource_id = wor.resource_id
and bdr.share_from_dept_id is null
and bdr.department_id = nvl(Dept_Res_rec.department_id, bdr.department_id)
and bd.organization_id = wor.organization_id
and bd.department_id = bdr.department_id
group by
x_group_id,
wor.organization_id,
wor.resource_id,
wor.resource_code,
bdr.department_id,
bd.department_code,
mnra.shift_date,
null,
WIP_RESOURCE_LOAD,
WIP_RL_PHASE_ONE,
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid ;
UPDATE wip_indicators_temp wit
SET wit.available_units = (
select
-- nvl(sum(((to_time-from_time)/3600)*capacity_units),0) --BUG - 3565583
-- nvl(sum(((decode(sign(to_time - from_time),
-- -1, ( 86400 - from_time ) + to_time,
-- 1, ( to_time - from_time ) ,
-- 0 ))/3600)*capacity_units),0)
decode(sum(shift_num),
0, nvl(sum(capacity_units)*24,0),
nvl(sum(((decode(sign(to_time - from_time),
-1, ( 86400 - from_time ) + to_time,
1, ( to_time - from_time ) ,
0 ))/3600)*capacity_units),0))
from
mrp_net_resource_avail mnra
where
mnra.organization_id = wit.organization_id
and mnra.department_id = wit.department_id
and mnra.resource_id = wit.resource_id
and wit.transaction_date between trunc(mnra.shift_date)
and trunc (mnra.shift_date) + 0.99999
and simulation_set is null
)
where wit.organization_id = Dept_Res_Rec.organization_id
AND wit.department_id = Dept_Res_Rec.department_id
AND wit.resource_id = Dept_Res_Rec.resource_id
and wit.indicator_type = WIP_RESOURCE_LOAD
and process_phase = WIP_RL_PHASE_ONE ;
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
resource_id,
resource_code,
required_hours,
available_units,
transaction_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
x_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
null,
-- sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units), --BUG - 3565583
-- sum(((decode(sign(mnra.to_time - mnra.from_time),
-- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
-- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
decode(sum(mnra.shift_num),
0, sum(capacity_units)*24,
sum(((decode(sign(mnra.to_time - mnra.from_time),
-1, ( 86400 - mnra.from_time ) + mnra.to_time,
1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
trunc(mnra.shift_date),
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
bom_resources br,
bom_departments bd,
mrp_net_resource_avail mnra,
mtl_units_of_measure muom
where
mnra.shift_date between trunc(x_date_from) and
trunc(x_date_to) + 0.99999
and br.resource_id = mnra.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = mnra.organization_id
and bd.department_id = mnra.department_id
and bd.organization_id = mnra.organization_id
and mnra.organization_id = Dept_Res_Rec.organization_id
and mnra.department_id = Dept_Res_Rec.department_id
and mnra.resource_id = Dept_Res_Rec.resource_id
and mnra.shift_date not in (
select distinct transaction_date
from wip_indicators_temp wit
where wit.resource_id = Dept_Res_Rec.resource_id
and wit.department_id = Dept_Res_Rec.department_id
and wit.organization_id = Dept_Res_Rec.organization_id
and wit.indicator_type = WIP_RESOURCE_LOAD
and wit.process_phase = WIP_RL_PHASE_ONE
and wit.transaction_date between
trunc(x_date_from) and trunc(x_date_to) + 0.99999
)
group by
x_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
trunc(mnra.shift_date),
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid ;
Delete_Temp_Info(p_group_id=>x_group_Id);
select distinct organization_id, department_id, resource_id
from mrp_net_resource_avail
where organization_id = nvl(p_organization_id, organization_id)
and department_id = nvl(p_department_id, department_id)
and resource_id = nvl(p_resource_id, resource_id)
and trunc(shift_date) between trunc(p_date_from)
and trunc(p_date_to)
and simulation_set is null ;
insert into wip_indicators_temp(
group_id,
organization_id,
department_id,
department_code,
resource_id,
resource_code,
applied_units_utz,
available_units,
transaction_date,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id)
select
p_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
null,
--sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units), --BUG - 3581581
-- sum(((decode(sign(mnra.to_time - mnra.from_time),
-- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
-- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
decode(sum(mnra.shift_num),
0, sum(capacity_units)*24,
sum(((decode(sign(mnra.to_time - mnra.from_time),
-1, ( 86400 - mnra.from_time ) + mnra.to_time,
1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
trunc(mnra.shift_date),
WIP_UTILIZATION,
WIP_UTZ_PHASE_TWO,
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid
from
bom_resources br,
bom_departments bd,
mrp_net_resource_avail mnra,
mtl_units_of_measure muom
where
mnra.shift_date between trunc(p_date_from) and
trunc(p_date_to) + 0.99999
and br.resource_id = mnra.resource_id
and br.unit_of_measure = muom.uom_code
and muom.uom_class = g_uom_class
and br.organization_id = mnra.organization_id
and bd.department_id = mnra.department_id
and bd.organization_id = mnra.organization_id
and mnra.organization_id = Dept_Res_Rec.organization_id
and mnra.department_id = Dept_Res_Rec.department_id
and mnra.resource_id = Dept_Res_Rec.resource_id
and mnra.shift_date not in (
select distinct transaction_date
from wip_indicators_temp wit
where wit.resource_id = Dept_Res_Rec.resource_id
and wit.department_id = Dept_Res_Rec.department_id
and wit.organization_id = Dept_Res_Rec.organization_id
and wit.indicator_type = WIP_UTILIZATION
and wit.process_phase = WIP_UTZ_PHASE_TWO
and wit.transaction_date between
trunc(p_date_from) and trunc(p_date_to) + 0.99999
)
group by
p_group_id,
mnra.organization_id,
mnra.department_id,
bd.department_code,
mnra.resource_id,
br.resource_code,
trunc(mnra.shift_date),
sysdate,
g_userid,
SYSDATE,
g_userid,
g_applicationid ;
Delete_Temp_Info(p_group_id=>p_group_Id);
select
nvl(count(*),0)
into
x_no_of_day_shifts
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date = p_transaction_date ;
select
nvl(count(distinct shift_date),0)
into
x_total_days
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date between p_start_date and p_completion_date ;
* to be updated :
* 1. Inventory_Item_Id -- The Id of the inventory item.
* 2. Inventory_Item_Name -- The name of the inventory item.
* 3. Category_Id -- The Id of the category to which the item
* belongs
* 4. Category_Name -- The name of the category
*************************************************************/
PROCEDURE denormalize_item_dimension(
p_table_name IN VARCHAR2,
p_errnum IN OUT NOCOPY NUMBER,
p_errmesg IN OUT NOCOPY VARCHAR2)
AS
x_cursor_id INTEGER ;
'UPDATE ' || p_table_name || ' xtable ' ||
' SET ( ' ||
'inventory_item_name, ' ||
'category_id, ' ||
'category_name ' || ') = ' ||
'( SELECT ' ||
' mif.item_number, ' ||
' mic.category_id, ' ||
' mckfv.concatenated_segments ' ||
' FROM ' ||
' mtl_item_flexfields mif, ' ||
' mtl_categories_kfv mckfv, ' ||
' mtl_item_categories mic, ' ||
' mtl_default_category_sets mdcs ' ||
' WHERE mif.organization_id = xtable.organization_id ' ||
' AND mif.inventory_item_id = xtable.inventory_item_id ' ||
' AND mic.inventory_item_id (+) = xtable.inventory_item_id ' ||
' AND mic.organization_id (+) = xtable.organization_id ' ||
' AND mdcs.category_set_id (+) = mic.category_set_id ' ||
' AND mdcs.functional_area_id = 7 ' ||
' AND mckfv.category_id = mic.category_id ' ||
' ) ' ;
* to be updated :
* 1. Transaction_Date -- The date of the transaction
* 2. Period_Set_Name -- The GL Periods, period_set_name
* 3. Year -- The Year in the GL Periods
* 4. Quarter -- The Quarter in the GL Periods
* 5. Month -- The Month in the GL periods
*************************************************************/
PROCEDURE denormalize_time_dimension(
p_table_name IN VARCHAR2,
p_errnum IN OUT NOCOPY NUMBER,
p_errmesg IN OUT NOCOPY VARCHAR2)
AS
x_cursor_id INTEGER ;
'UPDATE ' || p_table_name || ' xtable ' ||
' SET ( ' ||
'period_set_name ' || ',' ||
'year ' || ',' ||
'quarter ' || ',' ||
'month ' || ') = ' ||
'( SELECT /*+ ORDERED */ ' ||
' yr.period_set_name, '||
' yr.period_name, ' ||
' qt.period_name, ' ||
' mo.period_name ' ||
' FROM ' ||
-- ' org_organization_definitions ood , ' ||
' gl_sets_of_books gsob, ' ||
' gl_periods mo, ' ||
' gl_periods qt, ' ||
' gl_periods yr ' ||
-- ' WHERE ood.organization_id = xtable.organization_id ' ||
-- ' AND gsob.set_of_books_id = ood.set_of_books_id ' ||
' WHERE gsob.set_of_books_id = xtable.set_of_books_id ' ||
' AND yr.period_set_name = gsob.period_set_name ' ||
' AND yr.period_type = ''Year'' ' ||
' AND xtable.transaction_date between yr.start_date and yr.end_date ' ||
' AND yr.adjustment_period_flag = ''N'' ' ||
' AND qt.period_set_name = gsob.period_set_name ' ||
' AND qt.period_type = ''Quarter'' ' ||
' AND xtable.transaction_date between qt.start_date and qt.end_date ' ||
' AND qt.adjustment_period_flag = ''N'' ' ||
' AND mo.period_set_name = gsob.period_set_name ' ||
' AND mo.period_type = gsob.ACCOUNTED_PERIOD_TYPE ' ||
' AND xtable.transaction_date between mo.start_date and mo.end_date ' ||
' AND mo.adjustment_period_flag = ''N'' ' ||
' ) ' ;
* to be updated :
* 1. Organization_ID -- The Organization Id
* 2. Organization_Name -- The Organization Name
* 3. Legal_Entity_ID -- The Legal Entity Id
* 4. Legal_Entity_Name -- The Legal Entity Name
* 5. Operating_Unit_ID -- The operating unit ID
* 6. Operating_Unit_Name -- The operating unit name
* 7. set_of_books_id -- The set of books id
* 8. set_of_books_name -- The set of books name
*************************************************************/
PROCEDURE denormalize_org_dimension(
p_table_name IN VARCHAR2,
p_errnum IN OUT NOCOPY NUMBER,
p_errmesg IN OUT NOCOPY VARCHAR2)
AS
x_cursor_id INTEGER ;
'UPDATE ' || p_table_name || ' xtable ' ||
' SET ( ' ||
'organization_name ' || ',' ||
'legal_entity_id ' || ',' ||
'legal_entity_name ' || ',' ||
'operating_unit_id ' || ',' ||
'operating_unit_name ' || ',' ||
'set_of_books_id ' || ',' ||
'set_of_books_name ' || ' ) = ' ||
'( SELECT /*+ ORDERED USE_HASH (ood) USE_HASH (hle) USE_HASH (gsob) USE_HASH (hou) PARALLEL*/ ' ||
' ood.organization_name, ' ||
' hle.organization_id , ' ||
' hle.name, '||
' hou.organization_id, ' ||
' hou.name, ' ||
' ood.set_of_books_id, ' ||
' gsob.name ' ||
' FROM ' ||
' org_organization_definitions ood, ' ||
' hr_legal_entities hle, ' ||
' gl_sets_of_books gsob ,' ||
' hr_operating_units hou ' ||
' WHERE ood.organization_id = xtable.organization_id ' ||
' AND hle.organization_id = ood.legal_entity ' ||
-- ' AND hle.set_of_books_id = ood.set_of_books_id ' ||
' AND gsob.set_of_books_id = ood.set_of_books_id ' ||
' AND hou.organization_id = ood.operating_unit ' ||
' AND hou.default_legal_context_id = to_char(ood.legal_entity) ' ||
' ) ' ;
* to be updated :
* 1. Organization_ID -- The Organization Id
* 2. location_id -- The Location ID
* 3. country_code -- The Country Code
* 4. country_Name -- The Country Name
* 5. Area_Code -- The Area Code
* 6. Area_Name -- The Area Name
* 7. region_code -- The region Code
* 8. region_name -- The Region Name
*************************************************************/
PROCEDURE denormalize_geo_dimension(
p_table_name IN VARCHAR2,
p_errnum IN OUT NOCOPY NUMBER,
p_errmesg IN OUT NOCOPY VARCHAR2)
AS
x_cursor_id INTEGER ;
select application_column_name
into x_mapping
from bis_flex_mappings_v
where id_flex_code = 'HR_LOCATIONS'
and flex_field_type = 'D'
and level_short_name = 'REGION' ;
'UPDATE ' || p_table_name || ' xtable ' ||
' SET ( ' ||
'location_id ' || ',' ||
'country_code ' || ',' ||
'country_name ' || ',' ||
'area_code ' || ',' ||
'area_name ' || ',' ||
'region_code ' || ') = ' ||
'( SELECT/*+ ORDERED PARALLEL */ ' ||
' horgu.location_id, ' ||
' hl.country, ' ||
' bthv.child_territory_name, ' ||
' bthv.parent_territory_code, ' ||
' bthv.parent_territory_name, ' ;
'UPDATE ' || p_table_name || ' xtable ' ||
' SET ( ' ||
'region_name ' || ') = ' ||
'( SELECT ' ||
' bthv.child_territory_name ' ||
' FROM ' ||
' bis_territory_hierarchies_v bthv ' ||
' WHERE bthv.child_territory_code = xtable.region_code ' ||
' AND bthv.child_territory_type = ''REGION'' ' ||
' AND bthv.parent_territory_type = ''COUNTRY'' ' ||
' AND bthv.parent_territory_code = xtable.country_code ' ||
' ) ' ;
INSERT INTO ' ||p_table_name || ' (' ||
' group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
shift_num,
standard_quantity,
total_quantity,
scrap_quantity,
standard_units,
applied_units_prd,
applied_units_utz,
available_units,
resource_cost,
resource_basis,
indicator_type,
process_phase,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
line_id,
available_quantity,
required_quantity,
required_hours,
share_from_dept_id' || ' ) ' ||
'SELECT group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
shift_num,
standard_quantity,
total_quantity,
scrap_quantity,
standard_units,
applied_units_prd,
applied_units_utz,
available_units,
resource_cost,
resource_basis,
indicator_type,
process_phase,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
line_id,
available_quantity,
required_quantity,
required_hours,
share_from_dept_id
FROM wip_indicators_temp wit
WHERE wit.indicator_type = ' || p_indicator);
insert into WIP_BIS_EFF_TEMP(
group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
shift_num,
standard_quantity,
total_quantity,
scrap_quantity,
standard_units,
applied_units_prd,
applied_units_utz,
available_units,
resource_cost,
resource_basis,
indicator_type,
process_phase,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
line_id,
available_quantity,
required_quantity,
required_hours,
share_from_dept_id)
select
p_group_id,
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
trunc(transaction_date),
NULL,
SUM(standard_quantity),
sum(total_quantity),
sum(scrap_quantity),
SUM(standard_units),
sum(applied_units_prd),
sum(applied_units_utz),
sum(available_units),
sum(resource_cost),
NULL,
WIP_EFFICIENCY,
WIP_EFF_PHASE_FOUR, -- this is the fourth and final phase
sysdate,
g_userid,
g_userid,
sysdate,
NULL,
NULL,
g_applicationid ,
NULL,
sysdate,
NULL,
sum(available_quantity),
sum(required_quantity),
sum(required_hours),
NULL
from
wip_indicators_temp
where indicator_type =WIP_EFFICIENCY
and process_phase in(WIP_EFF_PHASE_ONE, WIP_EFF_PHASE_TWO,WIP_EFF_PHASE_THREE)
group by
organization_id,
wip_entity_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
trunc(transaction_date);
INSERT INTO /*+ NOAPPEND */ wip_bis_prod_indicators (
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
standard_hours,
applied_hours_prd,
available_hours,
applied_hours_utz,
total_quantity,
scrap_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_update_date)
SELECT /*+ leading(wit2)*/ wit.organization_id,
wit.wip_entity_id,
we.primary_item_id,
wit.transaction_date, -- already trunc'ed
wit.operation_seq_num,
wit.department_id,
wit.department_code,
wit.resource_id,
wit.resource_code,
wit.standard_units,
wit.applied_units_prd,
wit2.available_units,
wit2.applied_units_utz,
wit3.total_quantity,
wit3.scrap_quantity,
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.last_update_login,
wit.request_id,
wit.program_application_id,
sysdate
FROM wip_entities we,
wip_bis_yld_temp wit3,
wip_bis_utz_temp wit2,
wip_bis_eff_temp wit
WHERE
wit2.organization_id = wit.organization_id
AND wit2.department_id = wit.department_id
AND wit2.resource_id = wit.resource_id
AND wit2.wip_entity_id = wit.wip_entity_id --Bug 3604065
AND wit2.operation_seq_num = wit.operation_seq_num --Bug 3604065
AND wit2.transaction_date = wit.transaction_date -- trunc'ed
AND wit3.organization_id = wit.organization_id
AND wit3.wip_entity_id = wit.wip_entity_id
AND wit3.operation_seq_num = wit.operation_seq_num
AND wit3.department_id = wit.department_id
AND wit3.resource_id = wit.resource_id
AND wit3.transaction_date = wit.transaction_date -- trunc'ed
AND wit3.process_phase = 3
AND we.wip_entity_id = wit.wip_entity_id
AND we.organization_id = wit.organization_id;
SELECT 1
FROM wip_bis_prod_indicators
WHERE nvl (existing_flag, -1) <> 1
AND rownum < 2;
SELECT 1
FROM wip_bis_prod_assy_yield
WHERE nvl (existing_flag, -1) <> 1
AND rownum < 2;
SELECT 1
FROM wip_bis_prod_dept_yield
WHERE nvl (existing_flag, -1) <> 1
AND rownum < 2;
SELECT count(1) INTO l_wip_bis_prod_indicators FROM wip_bis_prod_indicators;
SELECT count(1) INTO l_wip_bis_prod_assy_yield FROM wip_bis_prod_assy_yield;
SELECT count(1) INTO l_wip_bis_prod_dept_yield FROM wip_bis_prod_dept_yield;
INSERT INTO wip_bis_prod_indicators_temp (
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
total_quantity,
scrap_quantity,
standard_hours,
applied_hours_prd,
applied_hours_utz,
available_hours,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
category_id,
category_name,
inventory_item_name,
period_set_name,
year,
quarter,
month,
indicator_type,
share_from_dept_id)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
total_quantity,
scrap_quantity,
standard_hours,
applied_hours_prd,
applied_hours_utz,
available_hours,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
category_id,
category_name,
inventory_item_name,
period_set_name,
year,
quarter,
month,
indicator_type,
share_from_dept_id
FROM wip_bis_prod_indicators
WHERE transaction_date < trunc (p_max_backup_date);
INSERT INTO wip_bis_prod_assy_yield_temp (
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
category_id,
category_name,
inventory_item_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
category_id,
category_name,
inventory_item_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
FROM wip_bis_prod_assy_yield
WHERE transaction_date < trunc (p_max_backup_date);
INSERT INTO wip_bis_prod_dept_yield_temp (
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
transaction_date,
total_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
transaction_date,
total_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
FROM wip_bis_prod_dept_yield
WHERE transaction_date < trunc (p_max_backup_date);
/* Update the existing flag off all rows in
in:
wip_bis_prod_indicators
wip_bis_prod_assy_yield
wip_bis_prod_dept_yield
This is for fixing bug 3387800 which causes various views
on these tables to turn up empty.
Do not commit here.
*/
PROCEDURE update_existing_flag (
p_errnum OUT NOCOPY NUMBER,
p_errmesg OUT NOCOPY VARCHAR2)
IS
proc_name VARCHAR2 (40);
proc_name := 'update_existing_flag';
UPDATE wip_bis_prod_indicators
SET existing_flag = 1;
UPDATE wip_bis_prod_assy_yield
SET existing_flag = 1;
UPDATE wip_bis_prod_dept_yield
SET existing_flag = 1;
END update_existing_flag;
INSERT INTO wip_bis_prod_indicators (
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
total_quantity,
scrap_quantity,
standard_hours,
applied_hours_prd,
applied_hours_utz,
available_hours,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
category_id,
category_name,
inventory_item_name,
period_set_name,
year,
quarter,
month,
indicator_type,
share_from_dept_id)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
resource_id,
resource_code,
transaction_date,
total_quantity,
scrap_quantity,
standard_hours,
applied_hours_prd,
applied_hours_utz,
available_hours,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
category_id,
category_name,
inventory_item_name,
period_set_name,
year,
quarter,
month,
indicator_type,
share_from_dept_id
FROM wip_bis_prod_indicators_temp;
INSERT INTO wip_bis_prod_assy_yield (
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
category_id,
category_name,
inventory_item_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
transaction_date,
completed_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
category_id,
category_name,
inventory_item_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
FROM wip_bis_prod_assy_yield_temp;
INSERT INTO wip_bis_prod_dept_yield (
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
transaction_date,
total_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
)
SELECT
organization_id,
wip_entity_id,
inventory_item_id,
operation_seq_num,
department_id,
department_code,
transaction_date,
total_quantity,
scrap_quantity,
existing_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
set_of_books_name,
legal_entity_id,
legal_entity_name,
operating_unit_id,
operating_unit_name,
organization_name,
location_id,
area_code,
area_name,
country_code,
country_name,
region_code,
region_name,
period_set_name,
year,
quarter,
month
FROM wip_bis_prod_dept_yield_temp;