[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ APPEND PARALLEL (opi_dbi_wms_uom_rates) */ into opi_dbi_wms_uom_rates
(
inventory_item_id,
primary_uom_code,
transaction_uom_code,
rate
)
select
inventory_item_id,
primary_uom_code,
transaction_uom_code,
( case when primary_uom_code = transaction_uom_code then 1
else opi_dbi_wms_utility_pkg.get_uom_rate(
inventory_item_id,
primary_uom_code,
transaction_uom_code
)
end
) rate
from
(
select /*+ PARALLEL (wdth) PARALLEL (msi) PARALLEL (rtx) PARALLEL (oplan)
PARALLEL (sinv) USE_HASH(wdth) USE_HASH(msi) USE_HASH(rtx) USE_HASH(oplan)
USE_HASH(sinv) pq_distribute(oplan hash,hash) pq_distribute(msi hash,hash)
pq_distribute(rtx hash,hash) */ distinct
wdth.inventory_item_id,
msi.primary_uom_code,
wdth.transaction_uom_code
from
wms_dispatched_tasks_history wdth,
mtl_system_items msi,
poa_dbi_rtx_f rtx,
wms_op_plans_b oplan,
mtl_secondary_inventories sinv
where
nvl(wdth.is_parent, 'N') = 'Y'
and wdth.task_type = 2
and wdth.status = 6
and nvl(sinv.subinventory_type, 1) = 1
and oplan.plan_type_id = 1
and wdth.dest_subinventory_code = sinv.secondary_inventory_name
and wdth.inventory_item_id = msi.inventory_item_id
and wdth.organization_id = msi.organization_id
and wdth.source_document_id = rtx.transaction_id
and wdth.organization_id = sinv.organization_id
and wdth.operation_plan_id = oplan.operation_plan_id
and wdth.last_update_date >= d_start_date
and (wdth.last_update_date is null or wdth.last_update_date <= d_end_date)
and wdth.creation_date >= d_start_date
);
insert /*+ APPEND */ into opi_dbi_wms_uom_rates
(
inventory_item_id,
primary_uom_code,
transaction_uom_code,
rate
)
select
inventory_item_id,
primary_uom_code,
transaction_uom_code,
( case when primary_uom_code = transaction_uom_code then 1
else opi_dbi_wms_utility_pkg.get_uom_rate(
inventory_item_id,
primary_uom_code,
transaction_uom_code
)
end
) rate
from
(
select /*+ leading(wdth) */ distinct
wdth.inventory_item_id,
msi.primary_uom_code,
wdth.transaction_uom_code
from
wms_dispatched_tasks_history wdth,
mtl_system_items msi,
poa_dbi_rtx_f rtx,
wms_op_plans_b oplan,
mtl_secondary_inventories sinv
where
nvl(wdth.is_parent, 'N') = 'Y'
and wdth.task_type = 2
and wdth.status = 6
and nvl(sinv.subinventory_type, 1) = 1
and oplan.plan_type_id = 1
and wdth.dest_subinventory_code = sinv.secondary_inventory_name
and wdth.inventory_item_id = msi.inventory_item_id
and wdth.organization_id = msi.organization_id
and wdth.source_document_id = rtx.transaction_id
and wdth.organization_id = sinv.organization_id
and wdth.operation_plan_id = oplan.operation_plan_id
and wdth.last_update_date between d_start_date and d_end_date
and wdth.creation_date >= d_glob_date
);
INSERT /*+ APPEND PARALLEL(t) */ INTO opi_dbi_wms_rtp_f t (
task_id,
transaction_id,
organization_id,
subinventory_code,
inventory_item_id,
operation_plan_id,
op_plan_instance_id,
source_document_id,
putaway_completion_date,
putaway_quantity,
putaway_uom_code,
putaway_uom_conv_rate,
rcv_transaction_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
) SELECT task_id,
transaction_id,
organization_id,
subinventory_code,
inventory_item_id,
operation_plan_id,
op_plan_instance_id,
source_document_id,
putaway_completion_date,
putaway_quantity,
putaway_uom_code,
putaway_uom_conv_rate,
rcv_transaction_date,
current_time,
login_id,
login_id,
user_id,
null,
null,
null,
null
FROM (
SELECT /*+ PARALLEL(wdth) PARALLEL(rtx) PARALLEL(oplan) PARALLEL(sinv) PARALLEL(msi)
PARALLEL(rat) USE_HASH(wdth) use_hash(rtx) use_hash(oplan) use_hash(sinv)
use_hash(msi) use_hash(rat) pq_distribute(wdth hash,hash)
pq_distribute(oplan hash,hash) pq_distribute(msi hash,hash) pq_distribute(rtx hash,hash) */
wdth.task_id,
wdth.transaction_id,
wdth.organization_id,
wdth.dest_subinventory_code subinventory_code,
wdth.inventory_item_id,
wdth.operation_plan_id,
wdth.op_plan_instance_id,
wdth.source_document_id,
wdth.drop_off_time putaway_completion_date,
wdth.transaction_quantity putaway_quantity,
msi.primary_uom_code putaway_uom_code,
rtx.receive_txn_date rcv_transaction_date,
rat.rate putaway_uom_conv_rate,
l_start_time current_time,
l_login login_id,
l_user user_id
FROM wms_dispatched_tasks_history wdth,
poa_dbi_rtx_f rtx,
wms_op_plans_b oplan,
mtl_secondary_inventories sinv,
mtl_system_items msi,
opi_dbi_wms_uom_rates rat
WHERE nvl(wdth.is_parent, 'N') = 'Y' -- make sure that op plan started after inspections have 18/1/27 and task_type of 2, checked that 18/1/27 is true.. need to check 2
and wdth.task_type = 2
and wdth.status = 6
and nvl(sinv.subinventory_type, 1) = 1
and oplan.plan_type_id = 1
and wdth.dest_subinventory_code = sinv.secondary_inventory_name
and wdth.inventory_item_id = msi.inventory_item_id
and wdth.organization_id = msi.organization_id
and wdth.organization_id = sinv.organization_id
and wdth.operation_plan_id = oplan.operation_plan_id
and wdth.source_document_id = rtx.transaction_id
and wdth.inventory_item_id = rat.inventory_item_id
and wdth.transaction_uom_code = rat.transaction_uom_code
and msi.primary_uom_code = rat.primary_uom_code
and wdth.creation_date >= d_start_date
and wdth.last_update_date >= d_start_date
and (wdth.last_update_date is null or wdth.last_update_date <= d_end_date) );
select /*+ leading(wdth) */
wdth.task_id,
wdth.transaction_id,
wdth.organization_id,
wdth.dest_subinventory_code subinventory_code,
wdth.inventory_item_id,
wdth.operation_plan_id,
wdth.op_plan_instance_id,
wdth.source_document_id,
wdth.drop_off_time putaway_completion_date,
wdth.transaction_quantity putaway_quantity,
msi.primary_uom_code putaway_uom_code,
rtx.receive_txn_date rcv_transaction_date,
rat.rate putaway_uom_conv_rate,
l_start_time current_time,
l_login login_id,
l_user user_id
FROM wms_dispatched_tasks_history wdth,
poa_dbi_rtx_f rtx,
wms_op_plans_b oplan,
mtl_secondary_inventories sinv,
mtl_system_items msi,
opi_dbi_wms_uom_rates rat
WHERE nvl(wdth.is_parent, 'N') = 'Y' -- make sure that op plan started after inspections have 18/1/27 and task_type of 2, checked that 18/1/27 is true.. need to check 2
and wdth.task_type = 2
and wdth.status = 6
and nvl(sinv.subinventory_type, 1) = 1
and oplan.plan_type_id = 1
and wdth.dest_subinventory_code = sinv.secondary_inventory_name
and wdth.inventory_item_id = msi.inventory_item_id
and wdth.organization_id = msi.organization_id
and wdth.organization_id = sinv.organization_id
and wdth.operation_plan_id = oplan.operation_plan_id
and wdth.source_document_id = rtx.transaction_id
and wdth.last_update_date between d_start_date and d_end_date
and wdth.inventory_item_id = rat.inventory_item_id
and msi.primary_uom_code = rat.primary_uom_code
and wdth.transaction_uom_code = rat.transaction_uom_code
and wdth.creation_date >= d_glob_date
) s
ON (t.task_id = s.task_id)
WHEN matched THEN UPDATE SET
t.organization_id = s.organization_id,
t.subinventory_code = s.subinventory_code,
t.inventory_item_id = s.inventory_item_id,
t.operation_plan_id = s.operation_plan_id,
t.op_plan_instance_id = s.op_plan_instance_id,
t.source_document_id = s.source_document_id,
t.putaway_completion_date = s.putaway_completion_date,
t.putaway_quantity = s.putaway_quantity,
t.putaway_uom_code = s.putaway_uom_code,
t.putaway_uom_conv_rate = s.putaway_uom_conv_rate,
t.rcv_transaction_date = s.rcv_transaction_date ,
t.last_update_date = sysdate,
t.created_by = s.user_id,
t.last_updated_by = s.user_id,
t.last_update_login = s.login_id,
t.program_id = null,
t.program_login_id = null,
t.program_application_id = null,
t.request_id = null
WHEN NOT matched THEN INSERT (
t.task_id,
t.transaction_id,
t.organization_id,
t.subinventory_code,
t.inventory_item_id,
t.operation_plan_id,
t.op_plan_instance_id,
t.source_document_id,
t.putaway_completion_date,
t.putaway_quantity,
t.putaway_uom_code,
t.putaway_uom_conv_rate,
t.rcv_transaction_date,
t.last_update_date,
t.created_by,
t.last_updated_by,
t.last_update_login,
t.program_id,
t.program_login_id,
t.program_application_id,
t.request_id
) VALUES (
s.task_id,
s.transaction_id,
s.organization_id,
s.subinventory_code,
s.inventory_item_id,
s.operation_plan_id,
s.op_plan_instance_id,
s.source_document_id,
s.putaway_completion_date,
s.putaway_quantity,
s.putaway_uom_code,
s.putaway_uom_conv_rate,
s.rcv_transaction_date,
sysdate,
s.user_id,
s.user_id,
s.login_id,
null,
null,
null,
null
);