[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_ph (
p_move_order_header_id IN NUMBER,
p_current_header_id IN NUMBER,
x_return_status OUT NOCOPY NUMBER
);
mmt_rec.last_update_date:=wct_rec.last_update_date;
mmt_rec.last_updated_by:=wct_rec.last_updated_by;
mmt_rec.last_update_login:=wct_rec.last_update_login;
mmt_rec.program_update_date:=wct_rec.program_update_date;
mmt_rec.schedule_update_code:=wct_rec.schedule_update_code;
SELECT /*+index (mmtt,mtl_material_trans_temp_n14)*/ mmtt.* FROM
mtl_material_transactions_temp mmtt, mtl_txn_request_lines
mtrl WHERE mtrl.header_id = mo_hdr_id AND
mmtt.move_order_line_id = mtrl.line_id AND
--2513907 fix
mmtt.container_item_id IS null;
SELECT mmtt.* FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.transaction_header_id = txn_hdr_id;
SELECT mmtt.* FROM
mtl_material_transactions_temp mmtt,mtl_allocations_gtmp mag
WHERE mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.move_order_header_id = mag.move_order_header_id
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) --Should not bulk
--Bug3628747
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
for update of mmtt.transaction_temp_id; -- to lock the records
SELECT mmtt.* FROM
mtl_material_transactions_temp mmtt,mtl_allocations_gtmp mag
WHERE mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) -- should not bulk those childs
--Bug3628747
AND mmtt.move_order_header_id = mag.move_order_header_id
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
AND ( EXISTS (SELECT 1 -- sub is bulk picking enabled
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.organization_id = mmtt.organization_id
AND msi.enable_bulk_pick = 'Y')
OR EXISTS (SELECT 1 -- item is bulk picking enabled
FROM mtl_system_items msi
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.bulk_picked_flag = 'Y')
)
for update of mmtt.transaction_temp_id; -- to lock the records
SELECT mmtt.* FROM
mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
wsh_pick_grouping_rules spg
WHERE mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.move_order_header_id = moh.header_id
AND spg.pick_grouping_rule_id = moh.grouping_rule_id
AND spg.pick_method <>WMS_GLOBALS.PICK_METHOD_ORDER -- not order pciking
AND moh.move_order_type = INV_GLOBALS.g_move_order_pick_wave -- pick wave move order only
-- following is the logic for the input parameters
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) -- should not bulk those childs Bug3628747
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND moh.request_number between nvl(p_input_for_bulk.start_mo_request_number,moh.request_number) and
nvl(p_input_for_bulk.end_mo_request_number,moh.request_number)
AND moh.creation_date between nvl(p_input_for_bulk.start_release_date,moh.creation_date) and
nvl(p_input_for_bulk.end_release_date,moh.creation_date)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
for update of mmtt.transaction_temp_id; -- to lock the records
SELECT mmtt.* FROM
mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
wsh_pick_grouping_rules spg
WHERE mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.move_order_header_id = moh.header_id
AND spg.pick_grouping_rule_id = moh.grouping_rule_id
AND spg.pick_method <>WMS_GLOBALS.PICK_METHOD_ORDER -- not order pciking
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) -- should not bulk those lines
-- Bug3628747
AND moh.move_order_type = INV_GLOBALS.g_move_order_pick_wave -- pick wave move order only
-- following is the logic for the input parameters
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND moh.request_number between nvl(p_input_for_bulk.start_mo_request_number,moh.request_number) and
nvl(p_input_for_bulk.end_mo_request_number,moh.request_number)
AND moh.creation_date between nvl(p_input_for_bulk.start_release_date,moh.creation_date) and
nvl(p_input_for_bulk.end_release_date,moh.creation_date)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
AND ( EXISTS (SELECT 1 -- sub is bulk picking enabled
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.organization_id = mmtt.organization_id
AND msi.enable_bulk_pick = 'Y')
OR EXISTS (SELECT 1 -- item is bulk picking enabled
FROM mtl_system_items msi
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.bulk_picked_flag = 'Y')
)
for update of mmtt.transaction_temp_id; -- to lock the records
select mmtt.*
from wsh_delivery_legs wdl,wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
wsh_trip_stops wts, mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
wsh_pick_grouping_rules spg
where wts.trip_id = p_input_for_bulk.trip_id
and wdl.pick_up_stop_id = wts.stop_id
and wdl.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and mmtt.move_order_line_id = wdd.move_order_line_id
and mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.move_order_header_id = moh.header_id
AND spg.pick_grouping_rule_id = moh.grouping_rule_id
AND spg.pick_method <>WMS_GLOBALS.PICK_METHOD_ORDER -- not order pciking
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) -- should not bulk those lines
--Bug3628747
AND moh.move_order_type = INV_GLOBALS.g_move_order_pick_wave -- pick wave move order only
-- following is the logic for the input parameters
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND moh.request_number between nvl(p_input_for_bulk.start_mo_request_number,moh.request_number) and
nvl(p_input_for_bulk.end_mo_request_number,moh.request_number)
AND moh.creation_date between nvl(p_input_for_bulk.start_release_date,moh.creation_date) and
nvl(p_input_for_bulk.end_release_date,moh.creation_date)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
for update of mmtt.transaction_temp_id; -- to lock the records
select mmtt.*
from wsh_delivery_legs wdl,wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
wsh_trip_stops wts, mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
wsh_pick_grouping_rules spg
where wts.trip_id = p_input_for_bulk.trip_id
and wdl.pick_up_stop_id = wts.stop_id
and wdl.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and mmtt.move_order_line_id = wdd.move_order_line_id
and mmtt.wms_task_status = 8 -- unreleased
And mmtt.cartonization_id IS null -- not cartonized
And mmtt.organization_id = p_organization_id
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.move_order_header_id = moh.header_id
AND spg.pick_grouping_rule_id = moh.grouping_rule_id
AND spg.pick_method <>WMS_GLOBALS.PICK_METHOD_ORDER -- not order pciking
AND (mmtt.serial_allocated_flag <> 'Y' OR
mmtt.serial_allocated_flag IS NULL ) -- should not bulk those lines
--Bug3628747
AND moh.move_order_type = INV_GLOBALS.g_move_order_pick_wave -- pick wave move order only
-- following is the logic for the input parameters
AND mmtt.subinventory_code = nvl(p_input_for_bulk.subinventory_code, mmtt.subinventory_code)
AND mmtt.inventory_item_id = nvl(p_input_for_bulk.item_id,mmtt.inventory_item_id)
AND moh.request_number between nvl(p_input_for_bulk.start_mo_request_number,moh.request_number) and
nvl(p_input_for_bulk.end_mo_request_number,moh.request_number)
AND moh.creation_date between nvl(p_input_for_bulk.start_release_date,moh.creation_date) and
nvl(p_input_for_bulk.end_release_date,moh.creation_date)
AND Exists ( select 1
From wsh_delivery_details wdd,wsh_delivery_assignments_v wda
Where wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND nvl(wda.delivery_id,-1) = decode(p_input_for_bulk.delivery_id,null,nvl(wda.delivery_id,-1),p_input_for_bulk.delivery_id)
)
AND ( EXISTS (SELECT 1 -- sub is bulk picking enabled
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.organization_id = mmtt.organization_id
AND msi.enable_bulk_pick = 'Y')
OR EXISTS (SELECT 1 -- item is bulk picking enabled
FROM mtl_system_items msi
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.bulk_picked_flag = 'Y')
)
for update of mmtt.transaction_temp_id; -- to lock the records
delete mtl_allocations_gtmp;
or p_input_for_bulk.start_release_date is not null then -- select the move_order_headers
l_fast_possible := true;
insert into mtl_allocations_gtmp
(move_order_header_id)
select moh.header_id
from mtl_txn_request_headers moh,
wsh_pick_grouping_rules spg
where spg.pick_grouping_rule_id = moh.grouping_rule_id
AND spg.pick_method <>WMS_GLOBALS.PICK_METHOD_ORDER -- not order pciking
AND moh.move_order_type = INV_GLOBALS.g_move_order_pick_wave -- pick wave move order only
AND moh.request_number between nvl(p_input_for_bulk.start_mo_request_number,moh.request_number) and
nvl(p_input_for_bulk.end_mo_request_number,moh.request_number)
AND moh.creation_date between nvl(p_input_for_bulk.start_release_date,moh.creation_date) and
nvl(p_input_for_bulk.end_release_date,moh.creation_date);
select lot_control_code,serial_number_control_code
into l_lot_control_code,l_serial_control_code
from mtl_system_items_b msi, mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = mmtt_rec.transaction_Temp_id
and mmtt.organization_id = msi.organization_id
and msi.inventory_item_id = mmtt.inventory_item_id;
update mtl_material_transactions_temp
set parent_line_id = null
where transaction_temp_id = mmtt_rec.transaction_Temp_id;
if (g_trace_on = 1) then log_event('calling update_parent_mmtt ....'); end if;
inv_trx_util_pub.update_parent_mmtt(x_return_status => l_return_status
, p_parent_line_id =>mmtt_rec.parent_line_id
, p_child_line_id => mmtt_rec.transaction_Temp_id
, p_lot_control_code => l_lot_control_code
, p_serial_control_code => l_serial_control_code);
if (g_trace_on = 1) then log_event('returning from update_parent_mmtt:'||l_return_status); end if;
if (g_trace_on = 1) then log_event('Insert transaction temp id:'||mmtt_rec.transaction_temp_id); end if;
INSERT INTO wms_cartonization_temp
(transaction_header_id,
transaction_temp_id,
source_code,
source_line_id,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
revision,
organization_id,
subinventory_code,
locator_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_cost,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_source_id,
transaction_source_name,
transaction_date,
acct_period_id,
distribution_account_id,
transaction_reference,
requisition_line_id,
requisition_distribution_id,
reason_id,
lot_number,
lot_expiration_date,
serial_number,
receiving_document,
demand_id,
rcv_transaction_id,
move_transaction_id,
completion_transaction_id,
wip_entity_type,
schedule_id,
repetitive_line_id,
employee_code,
primary_switch,
schedule_update_code,
setup_teardown_code,
item_ordering,
negative_req_flag,
operation_seq_num,
picking_line_id,
trx_source_line_id,
trx_source_delivery_id,
physical_adjustment_id,
cycle_count_id,
rma_line_id,
customer_ship_id,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
ussgl_transaction_code,
vendor_lot_number,
encumbrance_account,
encumbrance_amount,
ship_to_location,
shipment_number,
transfer_cost,
transportation_cost,
transportation_account,
freight_code,
containers,
waybill_airbill,
expected_arrival_date,
transfer_subinventory,
transfer_organization,
transfer_to_location,
new_average_cost,
value_change,
percentage_change,
material_allocation_temp_id,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
item_segments,
item_description,
item_trx_enabled_flag,
item_location_control_code,
item_restrict_subinv_code,
item_restrict_locators_code,
item_revision_qty_control_code,
item_primary_uom_code,
item_uom_class,
item_shelf_life_code,
item_shelf_life_days,
item_lot_control_code,
item_serial_control_code,
item_inventory_asset_flag,
allowed_units_lookup_code,
department_id,
department_code,
wip_supply_type,
supply_subinventory,
supply_locator_id,
valid_subinventory_flag,
valid_locator_flag,
locator_segments,
current_locator_control_code,
number_of_lots_entered,
wip_commit_flag,
next_lot_number,
lot_alpha_prefix,
next_serial_number,
serial_alpha_prefix,
shippable_flag,
posting_flag,
required_flag,
process_flag,
error_code,
error_explanation,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
movement_id,
reservation_quantity,
shipped_quantity,
transaction_line_number,
task_id,
to_task_id,
source_task_id,
project_id,
source_project_id,
pa_expenditure_org_id,
to_project_id,
expenditure_type,
final_completion_flag,
transfer_percentage,
transaction_sequence_id,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
flow_schedule,
cost_group_id,
demand_class,
qa_collection_id,
kanban_card_id,
overcompletion_transaction_qty,
overcompletion_primary_qty,
overcompletion_transaction_id,
end_item_unit_number,
scheduled_payback_date,
line_type_code,
parent_transaction_temp_id,
put_away_strategy_id,
put_away_rule_id,
pick_strategy_id,
pick_rule_id,
move_order_line_id,
task_group_id,
pick_slip_number,
reservation_id,
common_bom_seq_id,
common_routing_seq_id,
org_cost_group_id,
cost_type_id,
transaction_status,
standard_operation_id,
task_priority,
wms_task_type,
parent_line_id,
--source_lot_number,
transfer_cost_group_id,
lpn_id,
transfer_lpn_id,
wms_task_status,
content_lpn_id,
container_item_id,
cartonization_id,
pick_slip_date,
rebuild_item_id,
rebuild_serial_number,
rebuild_activity_id,
rebuild_job_name,
organization_type,
transfer_organization_type,
owning_organization_id,
owning_tp_type,
xfr_owning_organization_id,
transfer_owning_tp_type,
planning_organization_id,
planning_tp_type,
xfr_planning_organization_id,
transfer_planning_tp_type,
secondary_uom_code,
secondary_transaction_quantity,
transaction_batch_id,
transaction_batch_seq,
allocated_lpn_id,
schedule_number,
scheduled_flag,
class_code,
schedule_group,
build_sequence,
bom_revision,
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
operation_plan_id,
move_order_header_id,
serial_allocated_flag)
values
(mmtt_rec.transaction_header_id,
mmtt_rec.transaction_temp_id,
mmtt_rec.source_code,
mmtt_rec.source_line_id,
mmtt_rec.transaction_mode,
mmtt_rec.lock_flag,
mmtt_rec.last_update_date,
mmtt_rec.last_updated_by,
mmtt_rec.creation_date,
mmtt_rec.created_by,
mmtt_rec.last_update_login,
mmtt_rec.request_id,
mmtt_rec.program_application_id,
mmtt_rec.program_id,
mmtt_rec.program_update_date,
mmtt_rec.inventory_item_id,
mmtt_rec.revision,
mmtt_rec.organization_id,
mmtt_rec.subinventory_code,
mmtt_rec.locator_id,
mmtt_rec.transaction_quantity,
mmtt_rec.primary_quantity,
mmtt_rec.transaction_uom,
mmtt_rec.transaction_cost,
mmtt_rec.transaction_type_id,
mmtt_rec.transaction_action_id,
mmtt_rec.transaction_source_type_id,
mmtt_rec.transaction_source_id,
mmtt_rec.transaction_source_name,
mmtt_rec.transaction_date,
mmtt_rec.acct_period_id,
mmtt_rec.distribution_account_id,
mmtt_rec.transaction_reference,
mmtt_rec.requisition_line_id,
mmtt_rec.requisition_distribution_id,
mmtt_rec.reason_id,
mmtt_rec.lot_number,
mmtt_rec.lot_expiration_date,
mmtt_rec.serial_number,
mmtt_rec.receiving_document,
mmtt_rec.demand_id,
mmtt_rec.rcv_transaction_id,
mmtt_rec.move_transaction_id,
mmtt_rec.completion_transaction_id,
mmtt_rec.wip_entity_type,
mmtt_rec.schedule_id,
mmtt_rec.repetitive_line_id,
mmtt_rec.employee_code,
mmtt_rec.primary_switch,
mmtt_rec.schedule_update_code,
mmtt_rec.setup_teardown_code,
mmtt_rec.item_ordering,
mmtt_rec.negative_req_flag,
mmtt_rec.operation_seq_num,
mmtt_rec.picking_line_id,
mmtt_rec.trx_source_line_id,
mmtt_rec.trx_source_delivery_id,
mmtt_rec.physical_adjustment_id,
mmtt_rec.cycle_count_id,
mmtt_rec.rma_line_id,
mmtt_rec.customer_ship_id,
mmtt_rec.currency_code,
mmtt_rec.currency_conversion_rate,
mmtt_rec.currency_conversion_type,
mmtt_rec.currency_conversion_date,
mmtt_rec.ussgl_transaction_code,
mmtt_rec.vendor_lot_number,
mmtt_rec.encumbrance_account,
mmtt_rec.encumbrance_amount,
mmtt_rec.ship_to_location,
mmtt_rec.shipment_number,
mmtt_rec.transfer_cost,
mmtt_rec.transportation_cost,
mmtt_rec.transportation_account,
mmtt_rec.freight_code,
mmtt_rec.containers,
mmtt_rec.waybill_airbill,
mmtt_rec.expected_arrival_date,
mmtt_rec.transfer_subinventory,
mmtt_rec.transfer_organization,
mmtt_rec.transfer_to_location,
mmtt_rec.new_average_cost,
mmtt_rec.value_change,
mmtt_rec.percentage_change,
mmtt_rec.material_allocation_temp_id,
mmtt_rec.demand_source_header_id,
mmtt_rec.demand_source_line,
mmtt_rec.demand_source_delivery,
mmtt_rec.item_segments,
mmtt_rec.item_description,
mmtt_rec.item_trx_enabled_flag,
mmtt_rec.item_location_control_code,
mmtt_rec.item_restrict_subinv_code,
mmtt_rec.item_restrict_locators_code,
mmtt_rec.item_revision_qty_control_code,
mmtt_rec.item_primary_uom_code,
mmtt_rec.item_uom_class,
mmtt_rec.item_shelf_life_code,
mmtt_rec.item_shelf_life_days,
mmtt_rec.item_lot_control_code,
mmtt_rec.item_serial_control_code,
mmtt_rec.item_inventory_asset_flag,
mmtt_rec.allowed_units_lookup_code,
mmtt_rec.department_id,
mmtt_rec.department_code,
mmtt_rec.wip_supply_type,
mmtt_rec.supply_subinventory,
mmtt_rec.supply_locator_id,
mmtt_rec.valid_subinventory_flag,
mmtt_rec.valid_locator_flag,
mmtt_rec.locator_segments,
mmtt_rec.current_locator_control_code,
mmtt_rec.number_of_lots_entered,
mmtt_rec.wip_commit_flag,
mmtt_rec.next_lot_number,
mmtt_rec.lot_alpha_prefix,
mmtt_rec.next_serial_number,
mmtt_rec.serial_alpha_prefix,
mmtt_rec.shippable_flag,
mmtt_rec.posting_flag,
mmtt_rec.required_flag,
mmtt_rec.process_flag,
mmtt_rec.error_code,
mmtt_rec.error_explanation,
mmtt_rec.attribute_category,
mmtt_rec.attribute1,
mmtt_rec.attribute2,
mmtt_rec.attribute3,
mmtt_rec.attribute4,
mmtt_rec.attribute5,
mmtt_rec.attribute6,
mmtt_rec.attribute7,
mmtt_rec.attribute8,
mmtt_rec.attribute9,
mmtt_rec.attribute10,
mmtt_rec.attribute11,
mmtt_rec.attribute12,
mmtt_rec.attribute13,
mmtt_rec.attribute14,
mmtt_rec.attribute15,
mmtt_rec.movement_id,
mmtt_rec.reservation_quantity,
mmtt_rec.shipped_quantity,
mmtt_rec.transaction_line_number,
mmtt_rec.task_id,
mmtt_rec.to_task_id,
mmtt_rec.source_task_id,
mmtt_rec.project_id,
mmtt_rec.source_project_id,
mmtt_rec.pa_expenditure_org_id,
mmtt_rec.to_project_id,
mmtt_rec.expenditure_type,
mmtt_rec.final_completion_flag,
mmtt_rec.transfer_percentage,
mmtt_rec.transaction_sequence_id,
mmtt_rec.material_account,
mmtt_rec.material_overhead_account,
mmtt_rec.resource_account,
mmtt_rec.outside_processing_account,
mmtt_rec.overhead_account,
mmtt_rec.flow_schedule,
mmtt_rec.cost_group_id,
mmtt_rec.demand_class,
mmtt_rec.qa_collection_id,
mmtt_rec.kanban_card_id,
mmtt_rec.overcompletion_transaction_qty,
mmtt_rec.overcompletion_primary_qty,
mmtt_rec.overcompletion_transaction_id,
mmtt_rec.end_item_unit_number,
mmtt_rec.scheduled_payback_date,
mmtt_rec.line_type_code,
mmtt_rec.parent_transaction_temp_id,
mmtt_rec.put_away_strategy_id,
mmtt_rec.put_away_rule_id,
mmtt_rec.pick_strategy_id,
mmtt_rec.pick_rule_id,
mmtt_rec.move_order_line_id,
mmtt_rec.task_group_id,
mmtt_rec.pick_slip_number,
mmtt_rec.reservation_id,
mmtt_rec.common_bom_seq_id,
mmtt_rec.common_routing_seq_id,
mmtt_rec.org_cost_group_id,
mmtt_rec.cost_type_id,
mmtt_rec.transaction_status,
mmtt_rec.standard_operation_id,
mmtt_rec.task_priority,
mmtt_rec.wms_task_type,
mmtt_rec.parent_line_id,
--mmtt_rec.source_lot_number,
mmtt_rec.transfer_cost_group_id,
mmtt_rec.lpn_id,
mmtt_rec.transfer_lpn_id,
mmtt_rec.wms_task_status,
mmtt_rec.content_lpn_id,
mmtt_rec.container_item_id,
mmtt_rec.cartonization_id,
mmtt_rec.pick_slip_date,
mmtt_rec.rebuild_item_id,
mmtt_rec.rebuild_serial_number,
mmtt_rec.rebuild_activity_id,
mmtt_rec.rebuild_job_name,
mmtt_rec.organization_type,
mmtt_rec.transfer_organization_type,
mmtt_rec.owning_organization_id,
mmtt_rec.owning_tp_type,
mmtt_rec.xfr_owning_organization_id,
mmtt_rec.transfer_owning_tp_type,
mmtt_rec.planning_organization_id,
mmtt_rec.planning_tp_type,
mmtt_rec.xfr_planning_organization_id,
mmtt_rec.transfer_planning_tp_type,
mmtt_rec.secondary_uom_code,
mmtt_rec.secondary_transaction_quantity,
mmtt_rec.transaction_batch_id,
mmtt_rec.transaction_batch_seq,
mmtt_rec.allocated_lpn_id,
mmtt_rec.schedule_number,
mmtt_rec.scheduled_flag,
mmtt_rec.class_code,
mmtt_rec.schedule_group,
mmtt_rec.build_sequence,
mmtt_rec.bom_revision,
mmtt_rec.routing_revision,
mmtt_rec.bom_revision_date,
mmtt_rec.routing_revision_date,
mmtt_rec.alternate_bom_designator,
mmtt_rec.alternate_routing_designator,
mmtt_rec.operation_plan_id,
mmtt_rec.move_order_header_id,
mmtt_rec.serial_allocated_flag);
SELECT
inventory_item_id,
Decode(gross_weight_uom_code,NULL,0,nvl(gross_weight,0)),
gross_weight_uom_code,
Decode(content_volume_uom_code,NULL,0,nvl(content_volume,0)),
content_volume_uom_code,
Decode(tare_weight_uom_code,NULL,0,nvl(tare_weight,0)),
tare_weight_uom_code
INTO
x_inventory_item_id,
x_gross_weight,
x_gross_weight_uom_code,
x_content_volume,
x_content_volume_uom_code,
x_tare_weight,
x_tare_weight_uom_code
FROM
wms_license_plate_numbers
WHERE
lpn_id = p_lpn_id;
SELECT
parent_item_id,
Decode(gross_weight_uom_code,NULL,0,nvl(gross_weight,0)),
gross_weight_uom_code,
Decode(content_volume_uom_code,NULL,0,nvl(content_volume,0)),
content_volume_uom_code,
Decode(tare_weight_uom_code,NULL,0,nvl(tare_weight,0)),
tare_weight_uom_code
FROM
WMS_PACKAGING_HIST
WHERE
PARENT_PACKAGE_id = p_package_id;
SELECT
parent_item_id
INTO
x_inventory_item_id
FROM
WMS_PACKAGING_HIST
WHERE
PARENT_PACKAGE_id = p_package_id
AND rownum < 2;
SELECT
Decode(weight_uom_code,NULL,0,nvl(unit_weight,0)),
Decode(volume_uom_code,NULL,0,Nvl(unit_volume,0)),
weight_uom_code,
volume_uom_code
INTO
l_ut_wt,
l_ut_vol,
l_std_wt_uom,
l_std_vol_uom
FROM
mtl_system_items
WHERE
organization_id = p_org_id AND
inventory_item_id = x_inventory_item_id;
SELECT inventory_item_id
INTO l_ret
FROM wms_license_plate_numbers
WHERE
lpn_id = p_lpn_id;
SELECT PARENT_ITEM_ID
INTO l_ret
FROM WMS_PACKAGING_HIST
WHERE
PARENT_PACKAGE_id = p_PACKAGE_id
AND rownum < 2;
PROCEDURE insert_ph
(p_orig_header_id IN NUMBER,
p_transaction_temp_id IN NUMBER)
IS
l_header_id NUMBER;
SELECT lot_number, serial_transaction_temp_id, primary_quantity
FROM mtl_transaction_lots_temp
WHERE
transaction_temp_id = p_transaction_temp_id;
SELECT msn.serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_serial_numbers msn
WHERE
msnt.transaction_temp_id = p_temp_id AND
msn.current_organization_id = p_org_id AND
msn.inventory_item_id = p_item_id AND
msn.serial_number >= msnt.fm_serial_number AND
msn.serial_number <= msnt.to_serial_number;
error_code := 'INSERT_PH';
if (g_trace_on = 1) then log_event('entered insert_ph'); END IF;
SELECT
transaction_header_id,
organization_id,
inventory_item_id,
revision,
primary_quantity,
content_lpn_id,
cartonization_id,
container_item_id
INTO
l_header_id,
l_org_id,
l_item_id,
l_rev,
l_prim_qty,
l_clpn_id,
l_cartonization_id,
l_citem_id
FROM
wms_cartonization_temp
WHERE
transaction_temp_id = p_transaction_temp_id;
select
lot_control_code,
serial_number_control_code,
Decode(weight_uom_code,NULL,0,nvl(unit_weight,0)),
Decode(volume_uom_code,NULL,0,Nvl(unit_volume,0)),
weight_uom_code,
volume_uom_code
INTO
v_lot_control_code,
v_serial_control_code,
l_weight,
l_volume,
l_gross_weight_uom_code,
l_content_volume_uom_code
from mtl_system_items msi
where
inventory_item_id = l_item_id AND
organization_id = l_org_id;
INSERT INTO WMS_PACKAGING_HIST
(
organization_id,
header_id,
sequence_id,
pack_level,
inventory_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
revision,
lot_number,
fm_serial_number,
to_serial_number,
primary_quantity,
PACKAGE_id,
parent_package_id,
lpn_id,
parent_lpn_id,
parent_item_id,
gross_weight,
content_volume,
gross_weight_uom_code,
content_volume_uom_code,
packaging_mode,
reference_id
)
VALUES
(l_org_id,
p_orig_header_id,
wms_cartnzn_pub.g_wms_pack_hist_seq,
pack_level,
l_item_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_rev,
lot_table(counter),
serial_table(counter),
serial_table(counter),
qty_table(counter),
null,
l_cartonization_id,
null,
null,
l_citem_id,
l_gross_weight,
l_content_volume,
l_gross_weight_uom_code,
l_content_volume_uom_code,
packaging_mode,
l_temp_id
);
INSERT INTO WMS_PACKAGING_HIST
(organization_id,
header_id,
sequence_id,
pack_level,
inventory_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
revision,
lot_number,
fm_serial_number,
to_serial_number,
primary_quantity,
PACKAGE_id,
parent_package_id,
lpn_id,
parent_lpn_id,
parent_item_id,
gross_weight,
content_volume,
gross_weight_uom_code,
content_volume_uom_code,
packaging_mode,
reference_id
)
VALUES
(l_org_id,
p_orig_header_id,
wms_cartnzn_pub.g_wms_pack_hist_seq,
pack_level,
NULL,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id,
null,
null,
null,
NULL,
1,
null,
l_cartonization_id,
L_CLPN_ID,
null,
l_citem_id,
l_gross_weight,
l_content_volume,
l_gross_weight_uom_code,
l_content_volume_uom_code,
packaging_mode,
l_temp_id
);
INSERT INTO WMS_PACKAGING_HIST
(organization_id,
header_id,
sequence_id,
pack_level,
inventory_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
revision,
lot_number,
fm_serial_number,
to_serial_number,
primary_quantity,
PACKAGE_id,
parent_package_id,
lpn_id,
parent_lpn_id,
parent_item_id,
gross_weight,
content_volume,
gross_weight_uom_code,
content_volume_uom_code,
packaging_mode,
reference_id,
tare_weight,
tare_weight_uom_code
)
VALUES
(
l_org_id,
p_orig_header_id,
wms_cartnzn_pub.g_wms_pack_hist_seq,
pack_level,
l_cont_item_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id,
null,
null,
null,
NULL,
1,
l_clpn_id,
l_cartonization_id,
null,
null,
l_citem_id,
l_gross_weight,
l_content_volume,
l_gross_weight_uom_code,
l_content_volume_uom_code,
packaging_mode,
l_temp_id,
l_tare_weight,
l_tare_weight_uom_code
);
if (g_trace_on = 1) then log_event('Exception occurred in insert_ph '||l_msg); END IF;
END insert_ph;
SELECT wms_packaging_hist_s.nextVal into
l_package_sequence FROM dual;
PROCEDURE update_gross_weight(p_header_id IN NUMBER,
p_organization_id IN NUMBER)
IS
CURSOR plpns IS
SELECT
wph.parent_lpn_id,
Decode(wph.tare_weight_uom_code,NULL,0,Nvl(wph.tare_weight,0)) tare_weight,
wph.tare_weight_uom_code
FROM
wms_packaging_hist wph
WHERE
header_id = p_header_id AND
organization_id = p_organization_id AND
parent_lpn_id IS NOT NULL
ORDER BY parent_lpn_id;
SELECT gross_weight
, gross_weight_uom_code
INTO l_lpn_wt_from_db
, l_wt_uom_from_db
FROM wms_license_plate_numbers
WHERE lpn_id = prev_lpn;
log_event('LPN gross weight updated');
SELECT gross_weight_uom_code, gross_weight INTO
gr_wt_uom_code, l_lpn_cur_gr_wt
FROM wms_license_plate_numbers
WHERE lpn_id = lpn_cur.parent_lpn_id;
SELECT gross_weight
, gross_weight_uom_code
INTO l_lpn_wt_from_db
, l_wt_uom_from_db
FROM wms_license_plate_numbers
WHERE lpn_id = prev_lpn;
log_event('LPN gross weight updated');
if (g_trace_on = 1) then log_event('error occurred in update gross weight'); END IF;
END update_gross_weight;
SELECT cartonization_id, container_item_id
INTO
x_par_cart_id, x_par_cont_id
FROM wms_cartonization_temp
WHERE
content_lpn_id = p_child AND
transaction_header_id < 0;
SELECT
transaction_temp_id,
cartonization_id,
CONTAINER_ITEM_ID
FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_header_id
--Bug 2864774 fix
ORDER BY cartonization_id;
SELECT
wct.transaction_temp_id,
wct.cartonization_id,
wct.CONTAINER_ITEM_ID
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_header_id AND
wct.transaction_header_id >= 0 -- AND
-- wct.container_item_id IS NOT NULL --ER : 6682436
-- Bug 3876178
ORDER BY cartonization_id;
SELECT parent_lpn_id INTO par_lpn_id
FROM wms_packaging_hist
WHERE
parent_package_id = cart_id AND
header_id = p_header_id AND
ROWNUM < 2;
UPDATE wms_cartonization_temp
SET
cartonization_id = v_lpn_id_out,
container_item_id = cont_id
WHERE
transaction_temp_id = orig_temp_id;
if (g_trace_on = 1) then log_event('updated wms_cartonization_temp for temp id '||orig_temp_id); END IF;
UPDATE WMS_PACKAGING_HIST
SET parent_lpn_id = v_lpn_id_out
--parent_package_id = NULL
WHERE
parent_package_id = cart_id;
if (g_trace_on = 1) then log_event('updated WMS_PACKAGING_HIST for package_id '||cart_id); END IF;
UPDATE wms_packaging_hist
SET parent_package_id = NULL
WHERE
parent_lpn_id IS NOT NULL AND
parent_package_id IS NOT NULL AND
header_id = p_header_id;
update_gross_weight(p_header_id,p_organization_id);
select msi.lot_control_code, msi.serial_number_control_code
INTO v_lot_control_code, v_serial_control_code
from mtl_system_items msi where
(msi.inventory_item_id, msi.organization_id) =
(
select mmtt.inventory_item_id, mmtt.organization_id from
wms_cartonization_temp mmtt WHERE mmtt.transaction_temp_id = curr_temp_id
);
api_table.delete;
SELECT mtl_material_transactions_s.NEXTVAL INTO
api_table(api_table_index).transaction_id
FROM dual;
SELECT primary_quantity ,secondary_transaction_quantity INTO
api_table(api_table_index).primary_quantity,
api_table(api_table_index).secondary_quantity
FROM
wms_cartonization_temp
WHERE
transaction_temp_id = curr_temp_id;
UPDATE mtl_transaction_lots_temp
SET
transaction_temp_id = curr_temp_id
WHERE
transaction_temp_id = l_temporary_temp_id;
api_table.delete;
temp_id_table.DELETE;
SELECT 'Y' INTO l_ret
FROM dual WHERE exists
(SELECT inventory_item_id
FROM MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id
AND mtl_transactions_enabled_flag = 'Y'
AND enabled_flag = 'Y');
SELECT transaction_temp_id
FROM wms_cartonization_temp wct
WHERE
parent_line_id is null or -- non bulked records
parent_line_id = transaction_temp_id; -- parents only
Select transaction_temp_id
From wms_cartonization_temp
Where parent_line_id = transaction_temp_id;
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE
wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS null
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
-- AND wct.cartonization_id IS NULL
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND( (Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3
AND sub.cartonization_flag = 1))
ORDER BY wct.move_order_line_id,
wct.inventory_item_id, Abs(wct.transaction_temp_id);
SELECT DISTINCT mmtt.cartonization_id FROM
--2513907 fix
wms_cartonization_temp mmtt,
--mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl WHERE
mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mmtt.cartonization_id IS NOT NULL
ORDER BY mmtt.cartonization_id;
SELECT DISTINCT mmtt.cartonization_id FROM
--2513907 fix
wms_cartonization_temp mmtt
--mtl_material_transactions_temp mmtt
WHERE
mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.cartonization_id IS NOT NULL
ORDER BY mmtt.cartonization_id;
SELECT * FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
AND cartonization_id IS NULL
AND transfer_lpn_id IS NULL
order by move_order_line_id,
decode(content_lpn_id,null,inventory_item_id,
decode(sign(p_hdr_id),
-1,
wms_cartnzn_pub.Get_package_ItemId(content_lpn_id),
wms_cartnzn_pub.Get_LPN_ItemId(content_lpn_id)
) ); */
SELECT * FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
AND cartonization_id IS NULL
AND transfer_lpn_id IS NULL
order by move_order_line_id,
decode(content_lpn_id,null,inventory_item_id,
decode(sign(p_hdr_id),
-1,
inventory_item_id,
wms_cartnzn_pub.Get_LPN_ItemId(content_lpn_id)
) ),Abs(transaction_temp_id);
SELECT
transaction_temp_id,
inventory_item_id,
primary_quantity,
transaction_quantity,
secondary_transaction_quantity, --invconv kkillams
content_lpn_id,
container_item_id,
cartonization_id
FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
order by cartonization_id;
SELECT
wct.transaction_temp_id,
wct.inventory_item_id,
wct.primary_quantity,
wct.transaction_quantity,
wct.secondary_transaction_quantity, --invconv kkillams
wct.content_lpn_id,
wct.container_item_id,
wct.cartonization_id
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_hdr_id
order by wct.cartonization_id;
DELETE wms_cartonization_temp;
pkg_attr_table.DELETE;
lpn_attr_table.DELETE;
lpns_generated_tb.DELETE;
SELECT Nvl(cartonization_flag,-1),
Nvl(cartonize_sales_orders,'Y'),
Nvl(cartonize_manufacturing,'N'),
--Bug 3528061 fix
Nvl(allocate_serial_flag,'N'),
NVL(default_pick_op_plan_id,-1)
INTO
v_cart_value,
l_cartonize_sales_orders,
l_cartonize_manufacturing,
--Bug 3528061 fix
g_allocate_serial_flag,
wms_cartnzn_pub.g_default_pick_op_plan_id
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT percent_fill_basis_flag
INTO percent_fill_basis
FROM wsh_shipping_parameters
WHERE organization_id = p_org_id AND
ROWNUM = 1;
SELECT Nvl(auto_pick_confirm_flag, 'N')
, Nvl(autocreate_delivery_flag, 'N')
INTO l_auto_pick_confirm_flag
, l_autocreate_delivery_flag
FROM wsh_picking_batches
WHERE batch_id =
(SELECT request_number
FROM mtl_txn_request_headers
WHERE header_id = p_move_order_header_id
AND move_order_type = inv_globals.G_MOVE_ORDER_PICK_WAVE);
SELECT count(1)
INTO l_rulebased_setup_exists
FROM wms_selection_criteria_txn_v
WHERE rule_type_code = 12
AND enabled_flag = 1
AND from_organization_id = p_org_id
AND ROWNUM = 1;
IF (g_trace_on = 1) THEN log_event(' Inserting mmtt rows to wct'); END IF;
SELECT move_order_type
INTO l_move_order_type
FROM mtl_txn_request_headers
WHERE header_id = p_move_order_header_id;
IF (g_trace_on = 1) THEN log_event(' inserting mmtt rows of this header into wms_cartonization_temp '|| p_move_order_header_id ); END IF;
if (g_trace_on = 1) then log_event(' inserting mmtt rows of this header into wms_cartonization_temp '|| p_transaction_header_id ); END IF;
SELECT
revision_qty_control_code,
lot_control_code,
serial_number_control_code
INTO
l_revision_code,
l_lot_code,
l_serial_code
FROM mtl_system_items
WHERE organization_id = v1.organization_id
AND inventory_item_id = v1.inventory_item_id;
SELECT NVL(SUM(primary_transaction_quantity),0)
into l_qoh FROM mtl_onhand_quantities_detail
WHERE organization_id = v1.organization_id
AND subinventory_code = v1.subinventory_code
AND locator_id = v1.locator_id
AND lpn_id = v1.allocated_lpn_id;
select v1.transaction_temp_id, 'Y'
into t_lpn_alloc_flag_table(v1.transaction_temp_id)
from dual;
select v1.transaction_temp_id, 'N'
into t_lpn_alloc_flag_table(v1.transaction_temp_id)
from dual;
select v1.transaction_temp_id, 'Y'
into t_lpn_alloc_flag_table(v1.transaction_temp_id)
from dual;
select v1.transaction_temp_id, null
into t_lpn_alloc_flag_table(v1.transaction_temp_id)
from dual;
SELECT primary_uom_code INTO v_primary_uom_code FROM mtl_system_items
WHERE inventory_item_id = v1.inventory_item_id AND
organization_id = v1.ORGANIZATION_ID;
SELECT max_load_quantity INTO v_qty_per_cont FROM wsh_container_items
WHERE load_item_id = v1.inventory_item_id AND
master_organization_id = v1.organization_id AND
container_item_id = v_container_item_id;
select msi.serial_number_control_code
INTO v_serial_control_code
from mtl_system_items msi
where
msi.inventory_item_id = v1.inventory_item_id AND
msi.organization_id = v1.organization_id;
update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v1.primary_quantity,
p_transaction_quantity => v1.transaction_quantity,
p_secondary_quantity => v1.secondary_transaction_quantity, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => space_avail_for,
p_transaction_quantity => tr_space_avail_for,
p_secondary_quantity => sec_tr_space_avail_for, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v1.primary_quantity,
p_transaction_quantity => v1.transaction_quantity,
p_secondary_quantity => v1.secondary_transaction_quantity, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag ,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
log_event('calling update_mmtt');
update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_qty_per_cont,
p_transaction_quantity => v_tr_qty_per_cont,
p_secondary_quantity => v_sec_tr_qty_per_cont, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
log_event(' calling insert mmtt');
insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_qty_per_cont,
p_transaction_quantity => v_tr_qty_per_cont,
p_secondary_quantity => v_sec_tr_qty_per_cont, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
if (g_trace_on = 1) then log_event(' called insert mmtt'); END IF;
if (g_trace_on = 1) then log_event('calling insert mmtt'); END IF;
insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_left_prim_quant,
p_transaction_quantity => v_left_tr_quant,
p_secondary_quantity => v_sec_left_tr_quant, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
t_lpn_alloc_flag_table.delete;
if (g_trace_on = 1) then log_event(' Inserting a new row for package '||l_package_id); END IF;
insert_mmtt
( p_transaction_temp_id => l_temp_id,
p_primary_quantity => l_qty,
p_transaction_quantity => l_tr_qty,
p_secondary_quantity => l_sec_tr_qty, --invconv kkillams
p_new_txn_hdr_id => l_current_header_id,
p_new_txn_tmp_id => get_next_temp_id,
p_clpn_id => l_package_id,
p_item_id => l_citem_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
if (g_trace_on = 1) then log_event(' Calling InsertPH for temp_id'||l_temp_id); END IF;
Insert_PH(p_move_order_header_id, l_temp_id);
Insert_PH(p_transaction_header_id, l_temp_id);
DELETE wms_cartonization_temp
WHERE
transaction_header_id < 0;
PROCEDURE UPDATE_MMTT(
p_transaction_temp_id IN NUMBER,
p_primary_quantity IN NUMBER,
p_transaction_quantity IN NUMBER,
p_secondary_quantity IN NUMBER, --invconv kkillams
p_LPN_string IN VARCHAR2,
p_lpn_id IN NUMBER,
p_container_item_id IN NUMBER,
p_parent_line_id IN NUMBER,
p_upd_qty_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_lpn NUMBER := 0;
if (g_trace_on = 1) then log_event(' In update mmtt '||p_upd_qty_flag ); END IF;
SELECT lpn_id INTO l_lpn FROM wms_license_plate_numbers WHERE
license_plate_number = p_lpn_string;
SELECT parent_line_id INTO par_line_id FROM
mtl_material_transactions_temp WHERE transaction_temp_id =
p_transaction_temp_id;
UPDATE mtl_material_transactions_temp SET
primary_quantity = p_primary_quantity,
transaction_quantity = p_transaction_quantity,
secondary_transaction_quantity = p_secondary_quantity, --invconv kkillams
cartonization_id = l_LPN,
container_item_id = p_container_item_id,
parent_line_id = par_line_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE
transaction_temp_id = p_transaction_temp_id;
UPDATE mtl_material_transactions_temp SET
cartonization_id = l_LPN,
container_item_id = p_container_item_id,
parent_line_id = par_line_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE
transaction_temp_id = p_transaction_temp_id;
SELECT parent_line_id INTO par_line_id FROM
wms_cartonization_temp WHERE transaction_temp_id =
p_transaction_temp_id;
UPDATE wms_cartonization_temp SET
primary_quantity = p_primary_quantity,
transaction_quantity = p_transaction_quantity,
secondary_transaction_quantity = p_secondary_quantity, --invconv kkillams
cartonization_id = l_LPN,
parent_line_id = par_line_id,
container_item_id = p_container_item_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE
transaction_temp_id = p_transaction_temp_id;
UPDATE wms_cartonization_temp SET
cartonization_id = l_LPN,
parent_line_id = par_line_id,
container_item_id = p_container_item_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE
transaction_temp_id = p_transaction_temp_id;
END update_mmtt;
PROCEDURE INSERT_MMTT(
p_transaction_temp_id IN NUMBER,
p_primary_quantity IN NUMBER,
p_transaction_quantity IN NUMBER,
p_secondary_quantity IN NUMBER , --invconv kkillams
p_LPN_string IN VARCHAR2,
p_lpn_id IN NUMBER,
p_container_item_id IN NUMBER,
p_new_txn_hdr_id IN NUMBER,
p_new_txn_tmp_id IN NUMBER,
p_clpn_id IN NUMBER,
p_item_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
v1 WCT_ROW_TYPE;
if (g_trace_on = 1) then log_event(' In insert mmtt with temp_id '||p_transaction_temp_id ); END IF;
SELECT lpn_id INTO L_lpn FROM wms_license_plate_numbers WHERE
license_plate_number = p_lpn_string;
SELECT * INTO v1 FROM wms_cartonization_temp WHERE
transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
v1.last_update_date := Sysdate;
v1.last_updated_by := fnd_global.user_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO v1.transaction_temp_id
FROM dual;
wms_task_dispatch_engine.insert_wct
(l_wct_rec => v1);
END insert_mmtt;
SELECT line_id INTO mlid FROM mtl_txn_request_lines WHERE header_id = 40262
AND roWnum < 2;
PROCEDURE insert_device_request_rec(p_mmtt_row IN mmtt_row)
IS
CURSOR lot_ser_cursor(p_transaction_temp_id NUMBER) IS
SELECT
mtlt.lot_number lot_num,
mtlt.transaction_quantity lot_qty,
msnt.fm_serial_number ser_num
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE
mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id(+)
AND ((mmtt.transaction_temp_id=msnt.transaction_temp_id
AND mtlt.serial_transaction_temp_id=msnt.transaction_temp_id)
OR 1=1);
INSERT INTO wms_device_requests (request_id,
task_id,
task_summary,
task_type_id,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lpn_id,
xfer_lpn_id,--4472870
transaction_quantity,
last_update_date,
last_updated_by) VALUES
( WMS_Device_Integration_PVT.wms_pkRel_dev_req_id, --global var so that for all lines in a pick release, it remains same
p_mmtt_row.transaction_temp_id,
'Y',
1, --"LOAD"
l_bus_event_id,
p_mmtt_row.organization_id , --use you local variables for these values
p_mmtt_row.subinventory_code,
p_mmtt_row.locator_id ,
p_mmtt_row.transfer_organization,
NULL,
NULL,
p_mmtt_row.inventory_item_id,
p_mmtt_row.revision,
p_mmtt_row.transaction_uom,
p_mmtt_row.allocated_lpn_id,
p_mmtt_row.cartonization_id,
p_mmtt_row.transaction_quantity,
Sysdate,
FND_GLOBAL.USER_ID);
INSERT INTO wms_device_requests (request_id,
task_id,
task_summary,
task_type_id,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lpn_id,
xfer_lpn_id, --4472870
transaction_quantity,
last_update_date,
last_updated_by) VALUES
( WMS_Device_Integration_PVT.wms_pkRel_dev_req_id, --global var so that for all lines in a pick release, it remains same
p_mmtt_row.transaction_temp_id,
'Y',
2, --"DROP"
l_bus_event_id,
p_mmtt_row.organization_id ,
NULL,
NULL,
p_mmtt_row.transfer_organization,
p_mmtt_row.transfer_subinventory ,
p_mmtt_row.transfer_to_location ,
p_mmtt_row.inventory_item_id,
p_mmtt_row.revision,
p_mmtt_row.transaction_uom,
p_mmtt_row.allocated_lpn_id,
p_mmtt_row.cartonization_id,
p_mmtt_row.transaction_quantity,
Sysdate,
FND_GLOBAL.USER_ID);
l_device_id := WMS_Device_Integration_PVT.SELECT_DEVICE(wdrData,'Y',NULL);
SELECT
msi.serial_number_control_code,
msi.lot_control_code
INTO
l_serial_code,
l_lot_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_mmtt_row.inventory_item_id
AND msi.organization_id = p_mmtt_row.organization_id;
IF wms_device_integration_pvt.wms_insert_lotSer_rec_WDR = 1 THEN
FOR l_rec IN lot_ser_cursor(p_mmtt_row.transaction_temp_id) LOOP
IF (l_rec.lot_num IS NOT NULL OR l_rec.ser_num IS NOT NULL) THEN
IF (l_rec.ser_num IS NOT NULL) THEN
l_qty := 1;
INSERT INTO wms_device_requests
(request_id,
task_id,
relation_id,
sequence_id,
task_summary,
task_type_id,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lot_number,
lot_qty,
serial_number,
lpn_id,
xfer_lpn_id,
transaction_quantity,
device_id,
status_code,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(WMS_Device_Integration_PVT.wms_pkRel_dev_req_id,
p_mmtt_row.transaction_temp_id,
NULL,
NULL,
'N',
NULL,
l_bus_event_id,
p_mmtt_row.organization_id,
p_mmtt_row.subinventory_code,
p_mmtt_row.locator_id,
p_mmtt_row.transfer_organization,
p_mmtt_row.transfer_subinventory,
p_mmtt_row.transfer_to_location,
p_mmtt_row.inventory_item_id,
p_mmtt_row.revision,
p_mmtt_row.TRANSACTION_uom,
l_rec.lot_num,
l_rec.lot_qty,
l_rec.ser_num,
p_mmtt_row.allocated_lpn_id,
p_mmtt_row.cartonization_id,
l_qty,
l_device_id,
'S',
p_mmtt_row.last_update_date,
p_mmtt_row.last_updated_by,
p_mmtt_row.last_update_login);
log_event('Error in inserting lot serial details in WDR,no data');
END insert_device_request_rec;
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl WHERE wct.move_order_line_id =
mtrl.line_id AND mtrl.header_id = p_m_o_h_id;
SELECT wct.* FROM wms_cartonization_temp wct;
SELECT * FROM wms_cartonization_temp
WHERE
transaction_header_id = p_m_o_h_id;
SELECT
mtlt.lot_number lot_num,
mtlt.transaction_quantity lot_qty,
msnt.fm_serial_number ser_num
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE
mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id(+)
AND ((mmtt.transaction_temp_id=msnt.transaction_temp_id
AND mtlt.serial_transaction_temp_id=msnt.transaction_temp_id)
OR 1=1);
if (g_trace_on = 1) then log_event(' Entered insert wct rows to mmtt'||p_m_o_h_id); END IF;
insert_device_request_rec(v1);
SELECT 'Y' INTO v_exist FROM dual
WHERE
exists ( SELECT transaction_temp_id FROM mtl_material_transactions_temp
WHERE transaction_temp_id = v1.transaction_temp_id);
SELECT license_plate_number INTO v_lpn FROM wms_license_plate_numbers WHERE
lpn_id = v1.cartonization_id;
log_event('calling update mmtt for tempid:'||v1.transaction_temp_id);
UPDATE_MMTT(
p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v1.primary_quantity,
p_transaction_quantity => v1.transaction_quantity,
p_secondary_quantity => v1.secondary_transaction_quantity, --invconv kkillams
--p_LPN_string => v_lpn,
p_lpn_id => v1.cartonization_id,
p_container_item_id => v1.container_item_id,
p_parent_line_id => v1.parent_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
if (g_trace_on = 1) then log_event(' inserting row into mmtt for temp id :'||v1.transaction_temp_id); END IF;
wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => v1);
if (g_trace_on = 1) then log_event('error OCCURRED IN INSERTING WCT ROWS BACK TO MMTT'); END IF;
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE
wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS null
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY wct.inventory_item_id,
wct.move_order_line_id,
Abs(wct.transaction_temp_id);
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE
wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS null
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND wct.subinventory_code = p_subinventory_name
AND((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY wct.inventory_item_id,
wct.move_order_line_id,
Abs(wct.transaction_temp_id);
SELECT DISTINCT mmtt.cartonization_id FROM
--2513907 fix
wms_cartonization_temp mmtt,
--mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl WHERE
mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mmtt.cartonization_id IS NOT NULL
ORDER BY mmtt.cartonization_id;
SELECT * FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
AND cartonization_id IS NULL
AND transfer_lpn_id IS NULL
order by move_order_line_id,
decode(content_lpn_id,null,inventory_item_id,
decode(sign(p_hdr_id),
-1,
inventory_item_id,
wms_cartnzn_pub.Get_LPN_ItemId(content_lpn_id)
) ),Abs(transaction_temp_id);
SELECT
transaction_temp_id,
inventory_item_id,
primary_quantity,
transaction_quantity,
secondary_transaction_quantity, --invconv kkillams
content_lpn_id,
container_item_id,
cartonization_id
FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
order by cartonization_id;
SELECT
wct.transaction_temp_id,
wct.inventory_item_id,
wct.primary_quantity,
wct.transaction_quantity,
wct.secondary_transaction_quantity, --invconv kkillams
wct.content_lpn_id,
wct.container_item_id,
wct.cartonization_id
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_hdr_id
order by wct.cartonization_id;
SELECT NVL(SUM(primary_transaction_quantity),0)
into l_qoh FROM mtl_onhand_quantities_detail
WHERE organization_id = v1.organization_id
AND subinventory_code = v1.subinventory_code
AND locator_id = v1.locator_id
AND lpn_id = v1.allocated_lpn_id;
SELECT max_load_quantity
INTO v_qty_per_cont
FROM wsh_container_items
WHERE load_item_id = v1.inventory_item_id
AND master_organization_id = v1.organization_id
AND container_item_id = v_container_item_id;
update_mmtt
(p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v1.primary_quantity,
p_transaction_quantity => v1.transaction_quantity,
p_secondary_quantity => v1.secondary_transaction_quantity, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_mmtt
(p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => space_avail_for,
p_transaction_quantity => tr_space_avail_for,
p_secondary_quantity => sec_tr_space_avail_for, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v1.primary_quantity,
p_transaction_quantity => v1.transaction_quantity,
p_secondary_quantity => v1.secondary_transaction_quantity, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag ,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
log_event('calling update_mmtt');
update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_qty_per_cont,
p_transaction_quantity => v_tr_qty_per_cont,
p_secondary_quantity => v_sec_tr_qty_per_cont, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
p_parent_line_id => NULL,
p_upd_qty_flag => l_upd_qty_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
log_event(' calling insert mmtt');
insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_qty_per_cont,
p_transaction_quantity => v_tr_qty_per_cont,
p_secondary_quantity => v_sec_tr_qty_per_cont, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
log_event(' called insert mmtt');
if (g_trace_on = 1) then log_event('calling insert mmtt'); END IF;
insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id,
p_primary_quantity => v_left_prim_quant,
p_transaction_quantity => v_left_tr_quant,
p_secondary_quantity => v_sec_left_tr_quant, --invconv kkillams
--p_LPN_string => v_lpn_out,
p_lpn_id => v_lpn_id,
p_container_item_id => v_container_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
TYPE rules_table_type IS TABLE OF wms_selection_criteria_txn%rowtype;
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE
wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS null
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND wct.cartonization_id IS NULL
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND( (Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3
AND sub.cartonization_flag = 1))
ORDER BY wct.move_order_line_id,
wct.subinventory_code,
wct.inventory_item_id, Abs(wct.transaction_temp_id);
SELECT DISTINCT mmtt.cartonization_id FROM
--2513907 fix
wms_cartonization_temp mmtt,
--mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl WHERE
mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mmtt.cartonization_id IS NOT NULL
ORDER BY mmtt.cartonization_id;
SELECT
transaction_temp_id,
inventory_item_id,
primary_quantity,
transaction_quantity,
secondary_transaction_quantity, --invconv kkillams
content_lpn_id,
container_item_id,
cartonization_id
FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
order by cartonization_id;
SELECT
wct.transaction_temp_id,
wct.inventory_item_id,
wct.primary_quantity,
wct.transaction_quantity,
wct.secondary_transaction_quantity, --invconv kkillams
wct.content_lpn_id,
wct.container_item_id,
wct.cartonization_id
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_hdr_id
order by wct.cartonization_id;
SELECT count(*)
INTO l_count
FROM wms_cartonization_temp;
log_event('NUMBER OF ROWS INSERTED IN WCT : '|| l_count);
SELECT * BULK COLLECT
INTO rules_table
FROM wms_selection_criteria_txn
WHERE rule_type_code = 12
AND from_organization_id = p_org_id -- Bug : 6962305
AND enabled_flag = 1
ORDER BY sequence_number;
SELECT count(1)
INTO l_count
FROM wms_cartonization_temp
WHERE cartonization_id IS NULL
AND transaction_header_id >= 0;
log_event('Done with FIRST LEVEL OF CARTONIZATION, inserting packaging history');
insert_ph
(
p_move_order_header_id => p_move_order_header_id,
p_current_header_id => p_move_order_header_id,
x_return_status => l_packaging_return_status
);
log_event('After Insert_Ph');
DELETE wms_cartonization_temp
WHERE transaction_header_id < 0;
SELECT wct.* FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE
wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS null
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND wct.cartonization_id IS NULL
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND wct.subinventory_code = NVL(p_subinventory_name,wct.subinventory_code)
AND((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY wct.move_order_line_id,
wct.inventory_item_id,
Abs(wct.transaction_temp_id);
SELECT DISTINCT mmtt.cartonization_id FROM
--2513907 fix
wms_cartonization_temp mmtt,
--mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl WHERE
mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mmtt.cartonization_id IS NOT NULL
ORDER BY mmtt.cartonization_id;
SELECT * FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
AND cartonization_id IS NULL
AND transfer_lpn_id IS NULL
order by move_order_line_id,
decode(content_lpn_id,null,inventory_item_id,
decode(sign(p_hdr_id),
-1,
inventory_item_id,
wms_cartnzn_pub.Get_LPN_ItemId(content_lpn_id)
) ),Abs(transaction_temp_id);
SELECT
transaction_temp_id,
inventory_item_id,
primary_quantity,
transaction_quantity,
secondary_transaction_quantity, --invconv kkillams
content_lpn_id,
container_item_id,
cartonization_id
FROM
wms_cartonization_temp
WHERE
transaction_header_id = p_hdr_id
order by cartonization_id;
SELECT
wct.transaction_temp_id,
wct.inventory_item_id,
wct.primary_quantity,
wct.transaction_quantity,
wct.secondary_transaction_quantity, --invconv kkillams
wct.content_lpn_id,
wct.container_item_id,
wct.cartonization_id
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_hdr_id
order by wct.cartonization_id;
UPDATE wms_cartonization_temp
SET cartonization_id = -99999
, container_item_id = -99999
WHERE subinventory_code <> p_subinventory_name
AND organization_id = p_org_id
AND cartonization_id IS NULL;
t_lpn_alloc_flag_table.delete;
IF (g_trace_on = 1) then log_event(' Inserting a new row for package '||l_package_id); END IF;
insert_mmtt
(p_transaction_temp_id => l_temp_id,
p_primary_quantity => l_qty,
p_transaction_quantity => l_tr_qty,
p_secondary_quantity => l_sec_tr_qty, --invconv kkillams
p_new_txn_hdr_id => l_current_header_id,
p_new_txn_tmp_id => get_next_temp_id,
p_clpn_id => l_package_id,
p_item_id => l_citem_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
IF (g_trace_on = 1) THEN log_event(' Calling InsertPH for temp_id'||l_temp_id); END IF;
Insert_PH(p_move_order_header_id, l_temp_id);
Insert_PH(p_transaction_header_id, l_temp_id);
UPDATE wms_cartonization_temp
SET cartonization_id = NULL
, container_item_id = NULL
WHERE subinventory_code <> p_subinventory_name
AND organization_id = p_org_id
AND cartonization_id = -99999
AND container_item_id = -99999;
SELECT DISTINCT wct.pick_slip_number
FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_parameters mtlp,
mtl_secondary_inventories sub
WHERE mtrl.header_id = p_move_order_header_id
AND mtrl.organization_id = p_org_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND sub.secondary_inventory_name = wct.subinventory_code
AND wct.cartonization_id IS NULL
AND ((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY 1 ;
SELECT DISTINCT wct.pick_slip_number
FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_parameters mtlp,
mtl_secondary_inventories sub
WHERE mtrl.header_id = p_move_order_header_id
AND mtrl.organization_id = p_org_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.subinventory_code = p_subinventory_name
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND sub.secondary_inventory_name = wct.subinventory_code
AND wct.cartonization_id IS NULL
AND ((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY 1 ;
SELECT DISTINCT wda.delivery_id
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.pick_slip_number = p_pick_slip_number
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
ORDER BY 1 ;
SELECT DISTINCT wda.delivery_id
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.subinventory_code = p_subinventory_name
AND wct.pick_slip_number = p_pick_slip_number
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
ORDER BY 1 ;
UPDATE wms_cartonization_temp
SET cartonization_id = l_carton_lpn_id
WHERE transaction_temp_id IN
(SELECT wct.transaction_temp_id
FROM wsh_delivery_details wdd ,
wms_cartonization_temp wct ,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = wct.demand_source_line
AND wct.move_order_line_id = mtrl.line_id
AND wct.cartonization_id IS NULL
AND wda.delivery_id = l_curr_deliv
AND wct.pick_slip_number = l_pick_slip
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mtrl.organization_id = p_org_id
) ;
log_event('Updated carton LPN :'|| l_carton_lpn_id ||' for delivery:'||l_curr_deliv );
SELECT wdd.delivery_detail_id BULK COLLECT
INTO l_del_det_id_tab
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.pick_slip_number = l_pick_slip
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NULL;
SELECT wdd.delivery_detail_id BULK COLLECT
INTO l_del_det_id_tab
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.subinventory_code = p_subinventory_name
AND wct.pick_slip_number = l_pick_slip
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NULL;
UPDATE wms_cartonization_temp
SET cartonization_id = l_carton_lpn_id
WHERE transaction_temp_id IN
(SELECT wct.transaction_temp_id
FROM wsh_delivery_details wdd ,
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE wdd.delivery_detail_id = l_del_det_id_tab (i)
AND wct.pick_slip_number = l_pick_slip
AND wdd.source_line_id = wct.demand_source_line
AND wdd.move_order_line_id = mtrl.line_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.cartonization_id IS NULL
AND mtrl.header_id = p_move_order_header_id
AND mtrl.organization_id = p_org_id
) ;
log_event('Updated WCT for wdd :'||l_del_det_id_tab (i) );
l_grp_table.DELETE ;
l_del_det_id_tab.DELETE ;
SELECT DISTINCT wda.delivery_id
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.pick_slip_number = p_pick_slip_number
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
ORDER BY 1 ;
SELECT DISTINCT wda.delivery_id
FROM mtl_txn_request_lines mtrl,
wms_cartonization_temp wct,
wsh_delivery_details wdd ,
wsh_delivery_assignments wda
WHERE mtrl.organization_id = p_org_id
AND mtrl.header_id = p_move_order_header_id
AND wct.move_order_line_id = mtrl.line_id
AND wct.organization_id = mtrl.organization_id
AND wct.subinventory_code = p_subinventory_name
AND wct.pick_slip_number = p_pick_slip_number
AND wct.cartonization_id IS NULL
AND wct.demand_source_line = wdd.source_line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
ORDER BY 1 ;
SELECT wct.* BULK COLLECT INTO l_mmtt_table
FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND sub.secondary_inventory_name = wct.subinventory_code
AND wct.subinventory_code = p_subinventory_name
AND wct.organization_id = p_org_id
AND wct.cartonization_id IS NULL
AND wct.transfer_lpn_id IS NULL
AND((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY wct.move_order_line_id,
wct.inventory_item_id,
Abs(wct.transaction_temp_id);
SELECT wct.* BULK COLLECT INTO l_mmtt_table
FROM wms_cartonization_temp wct,
mtl_txn_request_lines mtrl,
mtl_secondary_inventories sub,
mtl_parameters mtlp
WHERE wct.move_order_line_id =mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND mtlp.organization_id = wct.organization_id
AND sub.secondary_inventory_name = wct.subinventory_code
AND sub.organization_id = wct.organization_id
AND wct.organization_id = p_org_id
AND wct.cartonization_id IS NULL
AND wct.transfer_lpn_id IS NULL
AND((Nvl(mtlp.cartonization_flag,-1) = 1) OR
(Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1))
ORDER BY wct.move_order_line_id,
wct.inventory_item_id,
Abs(wct.transaction_temp_id);
l_cart_tab.DELETE;
UPDATE wms_cartonization_temp
SET cartonization_id = l_out_mmtt_table(i).cartonization_id
, container_item_id = l_out_mmtt_table(i).container_item_id
WHERE transaction_temp_id = l_out_mmtt_table(i).transaction_temp_id;
log_event('Updated WCT rows with customer logic cartonization ids');
l_del_det_id_tab.DELETE;
SELECT DISTINCT wdd.delivery_detail_id BULK COLLECT
INTO l_del_det_id_tab
FROM wsh_delivery_details wdd,
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE wct.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.organization_id = mtrl.organization_id
AND wct.demand_source_line = wdd.source_line_id
AND wct.organization_id = p_org_id
AND wdd.move_order_line_id = mtrl.line_id
AND wct.cartonization_id = l_cart_tab(i);
PROCEDURE insert_ph
(
p_move_order_header_id IN NUMBER,
p_current_header_id IN NUMBER,
x_return_status OUT NOCOPY NUMBER
)
IS
L_PREV_PACKAGE_ID NUMBER;
SELECT
wct.transaction_temp_id,
wct.inventory_item_id,
wct.primary_quantity,
wct.transaction_quantity,
wct.secondary_transaction_quantity, --invconv kkillams
wct.content_lpn_id,
wct.container_item_id,
wct.cartonization_id
FROM
wms_cartonization_temp wct,
mtl_txn_request_lines mtrl
WHERE
wct.move_order_line_id = mtrl.line_id AND
mtrl.header_id = p_move_order_header_id
ORDER BY wct.cartonization_id;
IF (g_trace_on = 1) THEN log_event('Coming in my insert_ph'); END IF;
t_lpn_alloc_flag_table.DELETE;
log_event(' Inserting a new row for package '||l_package_id);
insert_mmtt
(
p_transaction_temp_id => l_temp_id,
p_primary_quantity => l_qty,
p_transaction_quantity => l_tr_qty,
p_secondary_quantity => l_sec_tr_qty, --invconv kkillams
p_new_txn_hdr_id => l_current_header_id,
p_new_txn_tmp_id => get_next_temp_id,
p_clpn_id => l_package_id,
p_item_id => l_citem_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
log_event(' Calling InsertPH for temp_id'||l_temp_id);
insert_ph(p_move_order_header_id, l_temp_id);
END insert_ph;