[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select we.primary_item_id item_id, -- matl charges for discrete jobs
wdj.bom_revision b_revision,
trunc(mmt.transaction_date) txn_date,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_discrete_jobs wdj
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_inv_txn_date
and i_push_end_txn_date
and mmt.transaction_id <= i_push_last_inv_txn_id
and mmt.transaction_source_id = wdj.wip_entity_id
group by trunc(mmt.transaction_date),
we.primary_item_id,
wdj.bom_revision
UNION ALL
select we.primary_item_id item_id, -- matl charges for rep. schedules
wrs.bom_revision b_revision,
trunc(mmt.transaction_date) txn_date,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_repetitive_schedules wrs
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_inv_txn_date
and i_push_end_txn_date
and mmt.transaction_id <= i_push_last_inv_txn_id
and mmt.transaction_source_id = wrs.wip_entity_id
group by trunc(mmt.transaction_date),
we.primary_item_id,
wrs.bom_revision
UNION ALL
select we.primary_item_id item_id, -- matl charges for flow schedules
wfs.bom_revision b_revision,
trunc(mmt.transaction_date) txn_date,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_flow_schedules wfs
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_inv_txn_date
and i_push_end_txn_date
and mmt.transaction_id <= i_push_last_inv_txn_id
and mmt.transaction_source_id = wfs.wip_entity_id
group by trunc(mmt.transaction_date),
we.primary_item_id,
wfs.bom_revision
UNION ALL
select wdj.primary_item_id item_id, -- resource charges for discrete jobs
wdj.bom_revision b_revision,
trunc(wt.transaction_date) txn_date,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_discrete_jobs wdj
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and i_push_end_txn_date
and wt.transaction_id <= i_push_last_wip_txn_id
and wt.wip_entity_id = wdj.wip_entity_id
group by trunc(wt.transaction_date),
wdj.primary_item_id,
wdj.bom_revision
UNION ALL
select we.primary_item_id item_id, -- resource charges for rep. schedules
wrs.bom_revision b_revision,
trunc(wt.transaction_date) txn_date,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_repetitive_schedules wrs,
wip_entities we
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and i_push_end_txn_date
and wt.transaction_id <= i_push_last_wip_txn_id
and wt.wip_entity_id = wrs.wip_entity_id
and wt.wip_entity_id = we.wip_entity_id
group by trunc(wt.transaction_date),
we.primary_item_id,
wrs.bom_revision
UNION ALL
select wfs.primary_item_id item_id, -- resource charges for flow schedules
wfs.bom_revision b_revision,
trunc(wt.transaction_date) txn_date,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_flow_schedules wfs
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and i_push_end_txn_date
and wt.transaction_id <= i_push_last_wip_txn_id
and wt.wip_entity_id = wfs.wip_entity_id
group by trunc(wt.transaction_date),
wfs.primary_item_id,
wfs.bom_revision
order by 3,1,2;
- delete existing WIP opi_ids_push_log rows within the process
date ranges to avoid duplication in case of repush
- calculate the beginning balance at the start date
- update beginning balances with daily WIP transactions within
the process date ranges.
If it is not a first push and WIP rows exist within the date
range, it is a repush. We need to set the push_flag to null
to indicate that these rows are repushed.
------------------------------------------------------------------*/
if i_push_start_inv_txn_date < i_push_start_wip_txn_date then
l_start_date := i_push_start_inv_txn_date;
delete opi_ids_push_log
where organization_id = i_org_id
and trx_date between l_start_date and i_push_end_txn_date
and cost_group_id is null
and subinventory_code is null
and locator_id is null
and lot_number is null
and end_wip_val_b is not null
and end_wip_qty is not null;
update opi_ids_push_log
set push_flag = null,
last_update_date = sysdate
where organization_id = i_org_id
and trx_date between l_start_date and i_push_end_txn_date
and cost_group_id is null
and subinventory_code is null
and locator_id is null
and lot_number is null;
DBMS_OUTPUT.PUT_LINE('call update daily_wip');
update_daily_wip(l_push_log_key,
i_org_id,
c_txn_daily_sum_rec.item_id,
c_txn_daily_sum_rec.b_revision,
c_txn_daily_sum_rec.txn_date,
c_txn_daily_sum_rec.wip_txn_val,
l_err_num,
l_err_code,
l_err_msg);
update opi_ids_push_log
set push_flag = 1,
last_update_date = sysdate
where organization_id = i_org_id
and trx_date between l_start_date and i_push_end_txn_date
and cost_group_id is null
and subinventory_code is null
and locator_id is null
and lot_number is null
and push_flag is null;
l_update_flag number;
select wdj.primary_item_id item_id, -- curr bal. for discrete jobs
wdj.bom_revision b_revision,
sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)
+ nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)
+ nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)
+ nvl(pl_material_in,0) - nvl(pl_material_out,0)
+ nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)
+ nvl(pl_resource_in,0) - nvl(pl_resource_out,0)
+ nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)
+ nvl(pl_outside_processing_in,0) - nvl(pl_outside_processing_out,0)
- nvl(tl_material_var,0)
- nvl(tl_material_overhead_var,0)
- nvl(tl_resource_var,0)
- nvl(tl_outside_processing_var,0)
- nvl(tl_overhead_var,0)
- nvl(pl_material_var,0)
- nvl(pl_material_overhead_var,0)
- nvl(pl_resource_var,0)
- nvl(pl_outside_processing_var,0)
- nvl(pl_overhead_var,0)) curr_wip_bal
from wip_period_balances wpb,
wip_discrete_jobs wdj
where wpb.wip_entity_id = wdj.wip_entity_id
and wdj.status_type in (3,4,5,6,14,15)
-- released, complete, complete no charge, on hold,
-- pending close, failed close respectively.
and wpb.organization_id = wdj.organization_id
and wdj.organization_id = i_org_id
group by wdj.primary_item_id,
wdj.bom_revision
UNION ALL
select we.primary_item_id item_id, -- current bal. for repetitive schedules
wrs.bom_revision b_revision,
sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)
+ nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)
+ nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)
+ nvl(pl_material_in,0) - nvl(pl_material_out,0)
+ nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)
+ nvl(pl_resource_in,0) - nvl(pl_resource_out,0)
+ nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)
+ nvl(pl_outside_processing_in,0) - nvl(pl_outside_processing_out,0)
- nvl(tl_material_var,0)
- nvl(tl_material_overhead_var,0)
- nvl(tl_resource_var,0)
- nvl(tl_outside_processing_var,0)
- nvl(tl_overhead_var,0)
- nvl(pl_material_var,0)
- nvl(pl_material_overhead_var,0)
- nvl(pl_resource_var,0)
- nvl(pl_outside_processing_var,0)
- nvl(pl_overhead_var,0)) curr_wip_bal
from wip_period_balances wpb,
wip_repetitive_schedules wrs,
wip_entities we
where wpb.wip_entity_id = wrs.wip_entity_id
and wpb.wip_entity_id = we.wip_entity_id
and wrs.status_type in (3,4,5,6,14,15)
-- released, complete, complete no charge, on hold,
-- pending close, failed close respectively.
and wrs.organization_id = i_org_id
and wpb.organization_id = wrs.organization_id
and we.organization_id = wrs.organization_id
group by we.primary_item_id,
wrs.bom_revision
order by 1, 2;
select we.primary_item_id item_id, -- matl charges for discrete jobs
wdj.bom_revision b_revision,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_discrete_jobs wdj
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and mmt.transaction_source_id = wdj.wip_entity_id
group by we.primary_item_id,
wdj.bom_revision
UNION ALL
select we.primary_item_id item_id, -- matl charges for rep. schedules
wrs.bom_revision b_revision,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_repetitive_schedules wrs
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and mmt.transaction_source_id = wrs.wip_entity_id
group by we.primary_item_id,
wrs.bom_revision
UNION ALL
select we.primary_item_id item_id, -- matl charges for flow schedules
wfs.bom_revision b_revision,
sum(nvl(mta.base_transaction_value,0)) wip_txn_val
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
wip_entities we,
wip_flow_schedules wfs
where mmt.transaction_source_type_id = 5
and mmt.organization_id = i_org_id
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type = 7
and mmt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and mmt.transaction_source_id = wfs.wip_entity_id
group by we.primary_item_id,
wfs.bom_revision
UNION ALL
select wdj.primary_item_id item_id, -- resource charges for discrete jobs
wdj.bom_revision b_revision,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_discrete_jobs wdj
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and wt.wip_entity_id = wdj.wip_entity_id
group by wdj.primary_item_id,
wdj.bom_revision
UNION ALL
select we.primary_item_id item_id, -- resource charges for rep. schedules
wrs.bom_revision b_revision,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_repetitive_schedules wrs,
wip_entities we
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and wt.wip_entity_id = wrs.wip_entity_id
and wt.wip_entity_id = we.wip_entity_id
group by we.primary_item_id,
wrs.bom_revision
UNION ALL
select wfs.primary_item_id item_id, -- resource charges for flow schedules
wfs.bom_revision b_revision,
sum(nvl(wta.base_transaction_value,0)) wip_txn_val
from wip_transactions wt,
wip_transaction_accounts wta,
wip_flow_schedules wfs
where wt.organization_id = i_org_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.transaction_date between i_push_start_wip_txn_date
and c_end_date
and wt.wip_entity_id = wfs.wip_entity_id
group by wfs.primary_item_id,
wfs.bom_revision
order by 1,2;
select sysdate into l_curr_date
from dual;
2, -- substract to update beginning balance
l_err_num,
l_err_code,
l_err_msg);
delete from opi_ids_push_log
where beg_wip_val_b = 0
and end_wip_val_b = 0
and subinventory_code is null
and push_flag = 1 -- available to be pushed
and period_flag is null -- not period end
and nvl(beg_int_val_b,0) = 0
and nvl(end_int_val_b,0) = 0
and nvl(beg_onh_val_b,0) = 0
and nvl(end_onh_val_b,0) = 0
and nvl(total_rec_val_b,0) = 0
and nvl(tot_issues_val_b,0) = 0;
i_update_flag IN NUMBER, -- (1=update bal , 2=substract from bal)
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
) IS
l_push_log_count number;
if i_update_flag = 1 then
l_wip_amount := i_wip_amount;
select count(*)
into l_push_log_count
from opi_ids_push_log ipl
where ipl.ids_key = i_ids_key;
update opi_ids_push_log ipl
set beg_wip_val_b =
nvl(ipl.beg_wip_val_b,0) + nvl(l_wip_amount,0),
end_wip_val_b =
nvl(ipl.end_wip_val_b,0) + nvl(l_wip_amount,0),
avg_wip_val_b =
(nvl(ipl.beg_wip_val_b,0) + nvl(l_wip_amount,0)
+ nvl(ipl.end_wip_val_b,0) + nvl(l_wip_amount,0))
/ 2,
ipl.push_flag = 1,
ipl.last_update_date = sysdate
where ipl.ids_key = i_ids_key;
select msi.inventory_item_status_code,
msi.item_type,
msi.primary_uom_code
into l_item_status,
l_item_type,
l_base_uom
from mtl_system_items msi
where msi.organization_id = i_org_id
and msi.inventory_item_id = i_item_id;
DBMS_OUTPUT.PUT_LINE('daily update - call insert_upd, key =' || i_ids_key);
OPIMPXIN.Insert_update_push_log(
i_txn_date,
i_org_id,
i_item_id,
null, -- cost group id
i_revision,
null, -- lot number
null, -- subinventory code
null, -- locator
l_item_status,
l_item_type,
l_base_uom,
'beg_wip_qty', -- p_col_nam1
0, -- p_total1
'beg_wip_val_b', -- p_col_nam2
l_wip_amount, -- p_total2
'end_wip_qty', -- p_col_nam3
0, -- p_total3
'end_wip_val_b', -- p_col_nam4
l_wip_amount, -- p_total4
'avg_wip_qty', -- p_col_nam5
0, -- p_total5
'avg_wip_val_b', -- p_col_nam6
l_wip_amount, -- p_total6
2, -- selector
l_err_num); -- l_status
EDW_LOG.PUT_LINE('Error calling OPIMPXIN.Insert_update_push_log');
select max(trx_date)
into l_trx_date
from opi_ids_push_log ipl
where ipl.organization_id = i_org_id
and ipl.inventory_item_id = i_item_id
and ipl.revision = i_revision
and ipl.trx_date < i_txn_date
and ipl.cost_group_id is null
and ipl.lot_number is null
and ipl.subinventory_code is null
and ipl.locator_id is null;
select Nvl(end_wip_val_b,0)
into l_return_val
from opi_ids_push_log ipl
where ipl.ids_key = l_ids_key;
PROCEDURE update_daily_wip(
i_ids_key IN VARCHAR2,
i_org_id IN NUMBER,
i_item_id IN NUMBER,
i_revision IN VARCHAR2,
i_txn_date IN DATE,
i_wip_amount IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
) IS
l_push_log_key varchar2(240);
select msi.inventory_item_status_code,
msi.item_type,
msi.primary_uom_code
into l_item_status,
l_item_type,
l_base_uom
from mtl_system_items msi
where msi.organization_id = i_org_id
and msi.inventory_item_id = i_item_id;
select count(*)
into l_ipl_count
from opi_ids_push_log ipl
where ipl.ids_key = i_ids_key;
select push_flag, Nvl(beg_wip_val_b,0), Nvl(end_wip_val_b,0)
into l_push_flag, l_start_value,l_end_value
from opi_ids_push_log ipl
where ipl.ids_key = i_ids_key;
update opi_ids_push_log ipl
set ipl.beg_wip_val_b = l_start_value,
ipl.end_wip_val_b = l_end_value,
ipl.avg_wip_val_b = (l_start_value + l_end_value) / 2,
ipl.push_flag = 1,
ipl.last_update_date = sysdate
where ipl.ids_key = i_ids_key;
DBMS_OUTPUT.PUT_LINE('daily update - insert key: ' || i_ids_key);
OPIMPXIN.Insert_update_push_log(
i_txn_date,
i_org_id,
i_item_id,
null, -- cost group id
i_revision,
null, -- lot number
null, -- subinventory code
null, -- locator
l_item_status,
l_item_type,
l_base_uom,
'beg_wip_qty', -- p_col_nam1
0, -- p_total1
'beg_wip_val_b', -- p_col_nam2
l_start_value, -- p_total2
'end_wip_qty', -- p_col_nam3
0, -- p_total3
'end_wip_val_b', -- p_col_nam4
l_end_value, -- p_total4
'avg_wip_qty', -- p_col_nam5
0, -- p_total5
'avg_wip_val_b', -- p_col_nam6
l_avg_value, -- p_total6
2, -- selector
l_err_num); -- l_status
EDW_LOG.PUT_LINE('Error calling OPIMPXIN.Insert_update_push_log');
EDW_LOG.PUT_LINE('OPIMPXWP.update_daily_wip:');
EDW_LOG.PUT_LINE('Error processing (OPIMPXWP.update_daily_wip)...');
o_err_msg := 'OPIMPXWP.update_daily_wip ('
|| to_char(l_stmt_num)
|| '): '
|| substr(SQLERRM, 1,200);
END update_daily_wip;