[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT *
FROM mtl_txn_request_lines
WHERE line_id = p_move_order_line_id FOR UPDATE nowait;
SELECT
mtt.transaction_action_id
,mtt.transaction_source_type_id
,msi.primary_uom_code
,msi.revision_qty_control_code
,msi.lot_control_code
,msi.serial_number_control_code
,msi.location_control_code
,mp.stock_locator_control_code
,msi.unit_volume
,msi.volume_uom_code
,msi.unit_weight
,msi.weight_uom_code
,msi.reservable_type
,NVL(msi.end_assembly_pegging_flag,'N')
,mp.allocate_serial_flag
FROM mtl_transaction_types mtt
,mtl_system_items msi
,mtl_parameters mp
WHERE mtt.transaction_type_id = l_txn_type_id
AND msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_organization_id
AND mp.organization_id = l_organization_id;
SELECT muom.uom_code
FROM mtl_units_of_measure_tl muom,mtl_units_of_measure_tl muom2
WHERE muom2.uom_code = x_request_context.transaction_uom_code
AND muom2.language = userenv('LANG')
AND muom.uom_class = muom2.uom_class
AND muom.language = userenv('LANG')
AND muom.base_uom_flag = 'Y';
SELECT
wdd.source_header_id oe_header_id,
wdd.source_line_id oe_line_id,
NULL,
wdd.customer_id,
NULL,
wdd.ship_to_location_id ship_to_location,
NULL,
wc.freight_code -- Bug Fix 5594517
FROM wsh_delivery_details wdd,
wsh_carriers wc,
wsh_carrier_services wcs
WHERE wdd.move_order_line_id = p_move_order_line_id
AND wdd.move_order_line_id is NOT NULL
AND wdd.ship_method_code = wcs.ship_method_code (+)
AND wcs.carrier_id = wc.carrier_id (+);
SELECT oedtl.header_id oe_header_id,
oedtl.line_id oe_line_id,
NULL,
oedtl.sold_to_org_id, -- customer_id
NULL,
NULL,
NULL,
oedtl.freight_carrier_code
FROM oe_order_lines_all oedtl
WHERE oedtl.line_id = p_src_line_id;
SELECT
oola.header_id
,oola.line_id
,NULL
,oola.sold_to_org_id
--,rc.customer_number
,party.party_number
,NULL
,oola.shipment_number
,oola.freight_carrier_code
FROM oe_order_lines_all oola
, hz_parties party
, hz_cust_accounts cust_acct
WHERE oola.line_id = x_request_line_rec.reference_id
AND cust_acct.cust_account_id = oola.sold_to_org_id
AND cust_acct.party_id = party.party_id;
UPDATE mtl_txn_request_lines
SET txn_source_id = header_id
WHERE line_id = p_move_order_line_id;
UPDATE mtl_txn_request_lines SET
primary_quantity = x_request_line_rec.primary_quantity,
transaction_source_type_id = x_request_context.transaction_source_type_id,
txn_source_id = x_request_context.txn_header_id,
txn_source_line_id = x_request_context.txn_line_id
WHERE line_id = x_request_line_rec.line_id
;
SELECT status_id
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT status_id
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id;
SELECT status_id
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT
x.organization_id
,x.inventory_item_id
,x.revision
,x.lot_number
,lot.expiration_date lot_expiration_date
,x.subinventory_code
,sub.reservable_type
,x.locator_id
,x.cost_group_id
,x.date_received date_received
,x.primary_quantity primary_quantity
,NULL lpn_id
,x.project_id project_id
,x.task_id task_id
FROM
(SELECT
moq.organization_id
,moq.inventory_item_id
,moq.revision
,moq.lot_number
,moq.subinventory_code
,moq.locator_id
,moq.cost_group_id
,min(NVL(moq.orig_date_received,
moq.date_received)) date_received
,sum(moq.primary_transaction_quantity) primary_quantity
,moq.project_id
,moq.task_id
FROM
MTL_ONHAND_QUANTITIES_DETAIL moq
WHERE
moq.organization_id = :organization_id
AND moq.inventory_item_id = :inventory_item_id
GROUP BY
moq.organization_id, moq.inventory_item_id
,moq.revision, moq.lot_number
,moq.subinventory_code, moq.locator_id
,moq.cost_group_id
,moq.project_id
,moq.task_id
) x
,mtl_secondary_inventories sub
,mtl_lot_numbers lot
WHERE
x.primary_quantity > 0
AND x.organization_id = sub.organization_id
AND x.subinventory_code = sub.secondary_inventory_name
AND NVL(sub.disable_date, sysdate+1) > sysdate
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
';
g_output_serial_rows.DELETE;
SELECT serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
FOR UPDATE nowait;
SELECT p_inventory_item_id
, p_organization_id
, msn.serial_number
, null
FROM mtl_serial_numbers msn, inv_msn_gtemp img
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
AND nvl(msn.current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
AND nvl(msn.end_item_unit_number,'@@@') = nvl(p_unit_number,'@@@')
--AND nvl(msn.cost_group_id,-1) = nvl(p_cost_group_id, -1)
AND msn.current_status = 3
AND ((msn.group_mark_id is null) or (msn.group_mark_id = -1))
AND (p_detail_any_serial = 1 OR
(p_from_range <= msn.serial_number AND
Length(p_from_range) = Length(msn.serial_number))
)
AND (p_detail_any_serial = 1 OR
(msn.serial_number <= p_to_range AND
Length(p_to_range) = Length(msn.serial_number))
)
AND msn.serial_number = img.serial_number (+)
AND msn.inventory_item_id = img.inventory_item_id (+)
AND msn.current_organization_id = img.organization_id (+)
AND img.serial_number IS NULL
ORDER BY msn.serial_number;
SELECT img.inventory_item_id
, img.organization_id
, img.serial_number
--, null dont forget
, msn.status_id
FROM inv_msn_gtemp img, mtl_serial_numbers msn
WHERE img.use_flag = 0
AND msn.serial_number = img.serial_number
AND msn.inventory_item_id = img.inventory_item_id
AND msn.current_organization_id = img.organization_id
AND nvl(msn.revision,'@') = nvl(p_revision,'@')
AND nvl(msn.lot_number, '@') = nvl(p_lot_number,'@')
AND nvl(msn.current_subinventory_code,'@') = nvl(p_subinventory_code,'@')
AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
AND nvl(msn.end_item_unit_number,'@') = nvl(p_unit_number,'@')
AND msn.current_status = 3;
l_custom_select_serials INV_DETAIL_UTIL_PVT.g_serial_row_table_rec;
l_selected_serials INV_DETAIL_UTIL_PVT.g_serial_row_table_rec;
, x_serial_numbers => l_custom_select_serials );
FORALL i IN l_custom_select_serials.serial_number.first..l_custom_select_serials.serial_number.last
INSERT INTO inv_msn_gtemp (
inventory_item_id
, organization_id
, serial_number
, use_flag )
values (
l_custom_select_serials.inventory_item_id(i)
, l_custom_select_serials.organization_id(i)
, l_custom_select_serials.serial_number(i)
, 0 );
l_selected_serials.inventory_item_id
, l_selected_serials.organization_id
, l_selected_serials.serial_number
, l_selected_serials.serial_status;
DELETE FROM inv_msn_gtemp
WHERE use_flag = 0;
l_selected_serials.inventory_item_id
, l_selected_serials.organization_id
, l_selected_serials.serial_number
, l_selected_serials.serial_status;
IF l_selected_serials.serial_number.count > 0 THEN
FOR i IN l_selected_serials.serial_number.first..l_selected_serials.serial_number.last LOOP
IF (x_available_sl_qty >= p_required_sl_qty) THEN
EXIT;
, l_selected_serials.serial_status(i) ) = 'Y' ) THEN
BEGIN
INSERT INTO inv_msn_gtemp (
serial_identifier
, INVENTORY_ITEM_ID
, organization_id
, Serial_number
, use_flag )
VALUES (
1
, p_inventory_item_id
, p_organization_id
, l_selected_serials.serial_number(i)
, 1 );
IF ( lock_serial_number(p_inventory_item_id, l_selected_serials.serial_number(i)) ) THEN
-- Move last row pointer of g_output_serial_rows by 1.
g_serial_tbl_ptr := g_serial_tbl_ptr + 1;
g_output_serial_rows(g_serial_tbl_ptr).serial_number := l_selected_serials.serial_number(i);
print_debug('sn='||l_selected_serials.serial_number(i)||' skipped SQL err: '||SQLERRM(SQLCODE), 1);
print_debug('sn='||l_selected_serials.serial_number(i)||' of wrong status='||l_selected_serials.serial_status(i));
PROCEDURE insert_mmtt
(
x_return_status OUT NOCOPY VARCHAR2
,p_mmtt_tbl IN g_mmtt_tbl_type
,p_mmtt_tbl_size IN INTEGER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_MMTT';
print_debug('in insert mmtt '||p_mmtt_tbl(l_counter).transaction_temp_id );
INSERT INTO mtl_material_transactions_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 ,
secondary_transaction_quantity ,
transaction_uom ,
secondary_uom_code ,
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_id ,
overcompletion_primary_qty ,
overcompletion_transaction_qty ,
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 ,
common_bom_seq_id ,
common_routing_seq_id ,
cost_type_id ,
org_cost_group_id ,
move_order_line_id ,
task_group_id ,
pick_slip_number ,
reservation_id ,
transaction_status ,
transfer_cost_group_id ,
lpn_id ,
wms_task_type ,
allocated_lpn_id ,
move_order_header_id ,
serial_allocated_flag ,
wms_task_status ,
task_priority ,
fulfillment_base
)
VALUES
(
p_mmtt_tbl(l_counter).transaction_header_id
,p_mmtt_tbl(l_counter).transaction_temp_id
,p_mmtt_tbl(l_counter).source_code
,p_mmtt_tbl(l_counter).source_line_id
,p_mmtt_tbl(l_counter).transaction_mode
,p_mmtt_tbl(l_counter).lock_flag
,l_today
,l_user_id
,l_today
,l_user_id
,l_login_id
,p_mmtt_tbl(l_counter).request_id
,p_mmtt_tbl(l_counter).program_application_id
,p_mmtt_tbl(l_counter).program_id
,p_mmtt_tbl(l_counter).program_update_date
,p_mmtt_tbl(l_counter).inventory_item_id
,p_mmtt_tbl(l_counter).revision
,p_mmtt_tbl(l_counter).organization_id
,p_mmtt_tbl(l_counter).subinventory_code
,p_mmtt_tbl(l_counter).locator_id
,p_mmtt_tbl(l_counter).transaction_quantity
,p_mmtt_tbl(l_counter).primary_quantity
,p_mmtt_tbl(l_counter).secondary_transaction_quantity
,p_mmtt_tbl(l_counter).transaction_uom
,p_mmtt_tbl(l_counter).secondary_uom_code
,p_mmtt_tbl(l_counter).transaction_cost
,p_mmtt_tbl(l_counter).transaction_type_id
,p_mmtt_tbl(l_counter).transaction_action_id
,p_mmtt_tbl(l_counter).transaction_source_type_id
,p_mmtt_tbl(l_counter).transaction_source_id
,p_mmtt_tbl(l_counter).transaction_source_name
,p_mmtt_tbl(l_counter).transaction_date
,p_mmtt_tbl(l_counter).acct_period_id
,p_mmtt_tbl(l_counter).distribution_account_id
,p_mmtt_tbl(l_counter).transaction_reference
,p_mmtt_tbl(l_counter).requisition_line_id
,p_mmtt_tbl(l_counter).requisition_distribution_id
,p_mmtt_tbl(l_counter).reason_id
,p_mmtt_tbl(l_counter).lot_number
,p_mmtt_tbl(l_counter).lot_expiration_date
,p_mmtt_tbl(l_counter).serial_number
,p_mmtt_tbl(l_counter).receiving_document
,p_mmtt_tbl(l_counter).demand_id
,p_mmtt_tbl(l_counter).rcv_transaction_id
,p_mmtt_tbl(l_counter).move_transaction_id
,p_mmtt_tbl(l_counter).completion_transaction_id
,p_mmtt_tbl(l_counter).wip_entity_type
,p_mmtt_tbl(l_counter).schedule_id
,p_mmtt_tbl(l_counter).repetitive_line_id
,p_mmtt_tbl(l_counter).employee_code
,p_mmtt_tbl(l_counter).primary_switch
,p_mmtt_tbl(l_counter).schedule_update_code
,p_mmtt_tbl(l_counter).setup_teardown_code
,p_mmtt_tbl(l_counter).item_ordering
,p_mmtt_tbl(l_counter).negative_req_flag
,p_mmtt_tbl(l_counter).operation_seq_num
,p_mmtt_tbl(l_counter).picking_line_id
,p_mmtt_tbl(l_counter).trx_source_line_id
,p_mmtt_tbl(l_counter).trx_source_delivery_id
,p_mmtt_tbl(l_counter).physical_adjustment_id
,p_mmtt_tbl(l_counter).cycle_count_id
,p_mmtt_tbl(l_counter).rma_line_id
,p_mmtt_tbl(l_counter).customer_ship_id
,p_mmtt_tbl(l_counter).currency_code
,p_mmtt_tbl(l_counter).currency_conversion_rate
,p_mmtt_tbl(l_counter).currency_conversion_type
,p_mmtt_tbl(l_counter).currency_conversion_date
,p_mmtt_tbl(l_counter).ussgl_transaction_code
,p_mmtt_tbl(l_counter).vendor_lot_number
,p_mmtt_tbl(l_counter).encumbrance_account
,p_mmtt_tbl(l_counter).encumbrance_amount
,p_mmtt_tbl(l_counter).ship_to_location
,p_mmtt_tbl(l_counter).shipment_number
,p_mmtt_tbl(l_counter).transfer_cost
,p_mmtt_tbl(l_counter).transportation_cost
,p_mmtt_tbl(l_counter).transportation_account
,p_mmtt_tbl(l_counter).freight_code
,p_mmtt_tbl(l_counter).containers
,p_mmtt_tbl(l_counter).waybill_airbill
,p_mmtt_tbl(l_counter).expected_arrival_date
,p_mmtt_tbl(l_counter).transfer_subinventory
,p_mmtt_tbl(l_counter).transfer_organization
,p_mmtt_tbl(l_counter).transfer_to_location
,p_mmtt_tbl(l_counter).new_average_cost
,p_mmtt_tbl(l_counter).value_change
,p_mmtt_tbl(l_counter).percentage_change
,p_mmtt_tbl(l_counter).material_allocation_temp_id
,p_mmtt_tbl(l_counter).demand_source_header_id
,p_mmtt_tbl(l_counter).demand_source_line
,p_mmtt_tbl(l_counter).demand_source_delivery
,p_mmtt_tbl(l_counter).item_segments
,p_mmtt_tbl(l_counter).item_description
,p_mmtt_tbl(l_counter).item_trx_enabled_flag
,p_mmtt_tbl(l_counter).item_location_control_code
,p_mmtt_tbl(l_counter).item_restrict_subinv_code
,p_mmtt_tbl(l_counter).item_restrict_locators_code
,p_mmtt_tbl(l_counter).item_revision_qty_control_code
,p_mmtt_tbl(l_counter).item_primary_uom_code
,p_mmtt_tbl(l_counter).item_uom_class
,p_mmtt_tbl(l_counter).item_shelf_life_code
,p_mmtt_tbl(l_counter).item_shelf_life_days
,p_mmtt_tbl(l_counter).item_lot_control_code
,p_mmtt_tbl(l_counter).item_serial_control_code
,p_mmtt_tbl(l_counter).item_inventory_asset_flag
,p_mmtt_tbl(l_counter).allowed_units_lookup_code
,p_mmtt_tbl(l_counter).department_id
,p_mmtt_tbl(l_counter).department_code
,p_mmtt_tbl(l_counter).wip_supply_type
,p_mmtt_tbl(l_counter).supply_subinventory
,p_mmtt_tbl(l_counter).supply_locator_id
,p_mmtt_tbl(l_counter).valid_subinventory_flag
,p_mmtt_tbl(l_counter).valid_locator_flag
,p_mmtt_tbl(l_counter).locator_segments
,p_mmtt_tbl(l_counter).current_locator_control_code
,p_mmtt_tbl(l_counter).number_of_lots_entered
,p_mmtt_tbl(l_counter).wip_commit_flag
,p_mmtt_tbl(l_counter).next_lot_number
,p_mmtt_tbl(l_counter).lot_alpha_prefix
,p_mmtt_tbl(l_counter).next_serial_number
,p_mmtt_tbl(l_counter).serial_alpha_prefix
,p_mmtt_tbl(l_counter).shippable_flag
,p_mmtt_tbl(l_counter).posting_flag
,p_mmtt_tbl(l_counter).required_flag
,p_mmtt_tbl(l_counter).process_flag
,p_mmtt_tbl(l_counter).error_code
,p_mmtt_tbl(l_counter).error_explanation
,p_mmtt_tbl(l_counter).attribute_category
,p_mmtt_tbl(l_counter).attribute1
,p_mmtt_tbl(l_counter).attribute2
,p_mmtt_tbl(l_counter).attribute3
,p_mmtt_tbl(l_counter).attribute4
,p_mmtt_tbl(l_counter).attribute5
,p_mmtt_tbl(l_counter).attribute6
,p_mmtt_tbl(l_counter).attribute7
,p_mmtt_tbl(l_counter).attribute8
,p_mmtt_tbl(l_counter).attribute9
,p_mmtt_tbl(l_counter).attribute10
,p_mmtt_tbl(l_counter).attribute11
,p_mmtt_tbl(l_counter).attribute12
,p_mmtt_tbl(l_counter).attribute13
,p_mmtt_tbl(l_counter).attribute14
,p_mmtt_tbl(l_counter).attribute15
,p_mmtt_tbl(l_counter).movement_id
,p_mmtt_tbl(l_counter).reservation_quantity
,p_mmtt_tbl(l_counter).shipped_quantity
,p_mmtt_tbl(l_counter).transaction_line_number
,p_mmtt_tbl(l_counter).task_id
,p_mmtt_tbl(l_counter).to_task_id
,p_mmtt_tbl(l_counter).source_task_id
,p_mmtt_tbl(l_counter).project_id
,p_mmtt_tbl(l_counter).source_project_id
,p_mmtt_tbl(l_counter).pa_expenditure_org_id
,p_mmtt_tbl(l_counter).to_project_id
,p_mmtt_tbl(l_counter).expenditure_type
,p_mmtt_tbl(l_counter).final_completion_flag
,p_mmtt_tbl(l_counter).transfer_percentage
,p_mmtt_tbl(l_counter).transaction_sequence_id
,p_mmtt_tbl(l_counter).material_account
,p_mmtt_tbl(l_counter).material_overhead_account
,p_mmtt_tbl(l_counter).resource_account
,p_mmtt_tbl(l_counter).outside_processing_account
,p_mmtt_tbl(l_counter).overhead_account
,p_mmtt_tbl(l_counter).flow_schedule
,p_mmtt_tbl(l_counter).cost_group_id
,p_mmtt_tbl(l_counter).demand_class
,p_mmtt_tbl(l_counter).qa_collection_id
,p_mmtt_tbl(l_counter).kanban_card_id
,p_mmtt_tbl(l_counter).overcompletion_transaction_id
,p_mmtt_tbl(l_counter).overcompletion_primary_qty
,p_mmtt_tbl(l_counter).overcompletion_transaction_qty
,p_mmtt_tbl(l_counter).end_item_unit_number
,p_mmtt_tbl(l_counter).scheduled_payback_date
,p_mmtt_tbl(l_counter).line_type_code
,p_mmtt_tbl(l_counter).parent_transaction_temp_id
,p_mmtt_tbl(l_counter).put_away_strategy_id
,p_mmtt_tbl(l_counter).put_away_rule_id
,p_mmtt_tbl(l_counter).pick_strategy_id
,p_mmtt_tbl(l_counter).pick_rule_id
,p_mmtt_tbl(l_counter).common_bom_seq_id
,p_mmtt_tbl(l_counter).common_routing_seq_id
,p_mmtt_tbl(l_counter).cost_type_id
,p_mmtt_tbl(l_counter).org_cost_group_id
,p_mmtt_tbl(l_counter).move_order_line_id
,p_mmtt_tbl(l_counter).task_group_id
,p_mmtt_tbl(l_counter).pick_slip_number
,p_mmtt_tbl(l_counter).reservation_id
,p_mmtt_tbl(l_counter).transaction_status
,p_mmtt_tbl(l_counter).transfer_cost_group_id
,p_mmtt_tbl(l_counter).lpn_id
,p_mmtt_tbl(l_counter).wms_task_type
,p_mmtt_tbl(l_counter).allocated_lpn_id
,p_mmtt_tbl(l_counter).move_order_header_id
,p_mmtt_tbl(l_counter).serial_allocated_flag
,p_mmtt_tbl(l_counter).wms_task_status
,p_mmtt_tbl(l_counter).task_priority
,p_mmtt_tbl(l_counter).fulfillment_base
);
END insert_mmtt;
PROCEDURE insert_mtlt
(
x_return_status OUT NOCOPY VARCHAR2
,p_mtlt_tbl IN g_mtlt_tbl_type
,p_mtlt_tbl_size IN INTEGER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_MTLT';
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,transaction_quantity
,primary_quantity
,secondary_quantity
,secondary_unit_of_measure -- Bug 8217560
,lot_number
,lot_expiration_date
,error_code
,serial_transaction_temp_id
,group_header_id
,put_away_rule_id
,pick_rule_id
,lot_attribute_category
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,grade_code
,origination_date
,date_code
,change_date
,age
,retest_date
,maturity_date
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,recycled_content
,thickness
,thickness_uom
,width
,width_uom
,territory_code
,supplier_lot_number
,vendor_name
,vendor_id
,curl_wrinkle_fold
,description
,expiration_action_date
,expiration_action_code
,hold_date
)
(SELECT
p_mtlt_tbl(l_counter).transaction_temp_id
,l_today
,l_user_id
,l_today
,l_user_id
,l_login_id
,p_mtlt_tbl(l_counter).request_id
,p_mtlt_tbl(l_counter).program_application_id
,p_mtlt_tbl(l_counter).program_id
,p_mtlt_tbl(l_counter).program_update_date
,p_mtlt_tbl(l_counter).transaction_quantity
,p_mtlt_tbl(l_counter).primary_quantity
,p_mtlt_tbl(l_counter).secondary_quantity
,p_mtlt_tbl(l_counter).secondary_unit_of_measure --Bug# 8217560
,p_mtlt_tbl(l_counter).lot_number
,p_mtlt_tbl(l_counter).lot_expiration_date
,p_mtlt_tbl(l_counter).error_code
,p_mtlt_tbl(l_counter).serial_transaction_temp_id
,p_mtlt_tbl(l_counter).group_header_id
,p_mtlt_tbl(l_counter).put_away_rule_id
,p_mtlt_tbl(l_counter).pick_rule_id
,mln.lot_attribute_category
,mln.attribute_category
,mln.attribute1
,mln.attribute2
,mln.attribute3
,mln.attribute4
,mln.attribute5
,mln.attribute6
,mln.attribute7
,mln.attribute8
,mln.attribute9
,mln.attribute10
,mln.attribute11
,mln.attribute12
,mln.attribute13
,mln.attribute14
,mln.attribute15
,mln.c_attribute1
,mln.c_attribute2
,mln.c_attribute3
,mln.c_attribute4
,mln.c_attribute5
,mln.c_attribute6
,mln.c_attribute7
,mln.c_attribute8
,mln.c_attribute9
,mln.c_attribute10
,mln.c_attribute11
,mln.c_attribute12
,mln.c_attribute13
,mln.c_attribute14
,mln.c_attribute15
,mln.c_attribute16
,mln.c_attribute17
,mln.c_attribute18
,mln.c_attribute19
,mln.c_attribute20
,mln.n_attribute1
,mln.n_attribute2
,mln.n_attribute3
,mln.n_attribute4
,mln.n_attribute5
,mln.n_attribute6
,mln.n_attribute7
,mln.n_attribute8
,mln.n_attribute9
,mln.n_attribute10
,mln.d_attribute1
,mln.d_attribute2
,mln.d_attribute3
,mln.d_attribute4
,mln.d_attribute5
,mln.d_attribute6
,mln.d_attribute7
,mln.d_attribute8
,mln.d_attribute9
,mln.d_attribute10
,mln.grade_code
,mln.origination_date
,mln.date_code
,mln.change_date
,mln.age
,mln.retest_date
,mln.maturity_date
,mln.item_size
,mln.color
,mln.volume
,mln.volume_uom
,mln.place_of_origin
,mln.best_by_date
,mln.length
,mln.length_uom
,mln.recycled_content
,mln.thickness
,mln.thickness_uom
,mln.width
,mln.width_uom
,mln.territory_code
,mln.supplier_lot_number
,mln.vendor_name
,mln.vendor_id
,mln.curl_wrinkle_fold
,mln.description
,mln.expiration_action_date
,mln.expiration_action_code
,mln.hold_date
FROM mtl_material_transactions_temp mmtt,
mtl_lot_numbers mln
WHERE mmtt.transaction_temp_id = p_mtlt_tbl(l_counter).transaction_temp_id
and mln.inventory_item_id = mmtt.inventory_item_id
and mln.organization_id = mmtt.organization_id
and mln.lot_number = p_mtlt_tbl(l_counter).lot_number
);
END insert_mtlt;
PROCEDURE insert_msnt
(
x_return_status OUT NOCOPY VARCHAR2
,p_msnt_tbl IN g_msnt_tbl_type
,p_msnt_tbl_size IN INTEGER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_MSNT';
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,vendor_serial_number
,vendor_lot_number
,fm_serial_number
,to_serial_number
,serial_prefix
,error_code
,group_header_id
,parent_serial_number
,end_item_unit_number
)
VALUES
(
p_msnt_tbl(l_counter).transaction_temp_id
,l_today
,l_user_id
,l_today
,l_user_id
,l_login_id
,p_msnt_tbl(l_counter).request_id
,p_msnt_tbl(l_counter).program_application_id
,p_msnt_tbl(l_counter).program_id
,p_msnt_tbl(l_counter).program_update_date
,p_msnt_tbl(l_counter).vendor_serial_number
,p_msnt_tbl(l_counter).vendor_lot_number
,p_msnt_tbl(l_counter).fm_serial_number
,p_msnt_tbl(l_counter).to_serial_number
,p_msnt_tbl(l_counter).serial_prefix
,p_msnt_tbl(l_counter).error_code
,p_msnt_tbl(l_counter).group_header_id
,p_msnt_tbl(l_counter).parent_serial_number
,p_msnt_tbl(l_counter).end_item_unit_number
);
END insert_msnt;
CURSOR l_cursor IS SELECT mtl_material_transactions_s.NEXTVAL
FROM dual;
SELECT acct_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_organization_id
AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(p_date, Sysdate),p_organization_id)
BETWEEN trunc(period_start_date) and trunc(schedule_close_date)
ORDER BY period_start_date DESC, schedule_close_date ASC;
g_output_process_tbl.DELETE;
UPDATE mtl_serial_numbers
SET group_mark_id = p_group_mark_id
WHERE inventory_item_id = p_inventory_item_id
AND serial_number between p_serial_number_start and p_serial_number_end;
PROCEDURE update_detailed_quantities
(x_return_status OUT NOCOPY VARCHAR2)
IS
l_reservation_id NUMBER;
UPDATE mtl_reservations
SET detailed_quantity = detailed_quantity + g_output_process_tbl(l_index).primary_quantity
, secondary_detailed_quantity = secondary_detailed_quantity + g_output_process_tbl(l_index).secondary_quantity
WHERE reservation_id = l_reservation_id;
END update_detailed_quantities;
l_insert_lot BOOLEAN; -- insert a new lot record
l_insert_serial BOOLEAN; -- insert a new serial record
l_insert_txn BOOLEAN; -- insert a new transaction temp record
g_insert_lot_flag := 0;
g_insert_serial_flag := 0;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_txn_header_id FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_txn_temp_id FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_serial_temp_id FROM DUAL;
l_insert_serial := FALSE;
l_insert_lot := FALSE;
l_insert_txn := FALSE;
l_insert_txn := TRUE;
IF (l_insert_txn) THEN
print_debug('process output, l_insert_txn TRUE');
print_debug('process output, l_insert_txn FALSE');
SELECT lot_number
INTO l_reserved_lot
FROM mtl_reservations
WHERE reservation_id = g_output_process_tbl(l_index-1).reservation_id;
AND (l_insert_txn OR
g_output_process_tbl(l_index).lot_number
<> g_output_process_tbl(l_index-1).lot_number
)
AND (l_allocate_lot_flag='Y' OR p_request_context.wms_task_type = 2)) THEN -- Added for 14699845 (Flexible lot allocation)
l_insert_lot := TRUE;
g_insert_lot_flag := 1;
IF (l_insert_lot) THEN
print_debug('process output, l_insert_lot TRUE');
print_debug('process output, l_insert_lot FALSE');
AND (l_insert_txn OR l_insert_lot OR
g_output_process_tbl(l_index).serial_number_start IS NULL
OR g_output_process_tbl(l_index).serial_number_start
<> g_output_process_tbl(l_index-1).serial_number_start)
AND (p_request_context.item_lot_control_code <> 2 OR l_allocate_lot_flag='Y')) -- Added for 14699845 (Flexible lot allocation)
THEN
-- I rather not do the range overlapping comparision in the
-- condition for this if clause since that is overkill.
-- I assume the serial number will not overlap
l_insert_serial := TRUE;
g_insert_serial_flag := 1;
IF (l_insert_serial) THEN
print_debug('process output, l_insert_serial TRUE');
print_debug('process output, l_insert_serial FALSE');
IF l_insert_serial THEN
l_msnt_tbl_size := l_msnt_tbl_size +1;
IF l_insert_lot THEN
l_mtlt_tbl_size := l_mtlt_tbl_size +1;
If l_insert_serial Then
l_mtlt_tbl(l_mtlt_tbl_size).serial_transaction_temp_id := l_serial_temp_id;
print_debug('process output before mtlt update , l_lot_ten_temp_qty '|| l_lot_ten_temp_qty);
print_debug('process output before mtlt update , l_lot_temp_qty '|| l_lot_temp_qty);
print_debug('process output before mtlt update , l_lot_temp_qty_txn_uom '||l_lot_temp_qty_txn_uom);
print_debug('process output before mtlt update , l_mmtt_tbl(l_mmtt_tbl_size).wms_task_type '||p_request_context.wms_task_type );
print_debug('process output before mtlt update inside if residual, l_lot_temp_qty_txn_uom '||l_lot_temp_qty_txn_uom);
IF l_insert_txn THEN
l_mmtt_tbl_size := l_mmtt_tbl_size +1;
print_debug('process output before mmtt update, pri UOM:'||p_request_context.primary_uom_code||',txn uom:'||
p_request_context.transaction_uom_code);
print_debug('process output before mtlt update , l_ten_temp_qty '|| l_ten_temp_qty);
print_debug('process output before mmtt update , l_txn_temp_qty '|| l_txn_temp_qty);
print_debug('process output before mmtt update , l_txn_temp_qty_txn_uom '||l_txn_temp_qty_txn_uom);
print_debug('process output before mmtt update , l_mmtt_tbl(l_mmtt_tbl_size).wms_task_type '||p_request_context.wms_task_type );
print_debug('process output before mtlt update inside if residual, l_txn_temp_qty_txn_uom '||l_txn_temp_qty_txn_uom);
UPDATE mtl_txn_request_lines
SET quantity = primary_quantity,
uom_code = p_request_context.primary_uom_code
WHERE line_id = p_request_line_rec.line_id;
print_debug('process output before mmtt update , wms_engine_pvt.g_fulfillment_base value is '|| wms_engine_pvt.g_fulfillment_base);
print_debug('process output before mmtt update , fulfillment_base value is '|| l_mmtt_tbl(l_mmtt_tbl_size).fulfillment_base);
IF l_insert_serial THEN
l_mmtt_tbl(l_mmtt_tbl_size).serial_allocated_flag := 'Y';
IF (l_insert_lot OR (l_allocate_lot_flag='N' AND p_request_context.wms_task_type <> 2)) THEN -- Added for 14699845 (Flexible lot allocation)
-- reset the quantity for the next mtlt record
l_lot_temp_qty := 0;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_serial_temp_id FROM DUAL;
IF l_insert_txn THEN
-- reset the quantity for the next mmtt record
l_txn_temp_qty := 0;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_txn_temp_id FROM DUAL;
insert_mmtt
(
x_return_status => l_return_status ,
p_mmtt_tbl => l_mmtt_tbl ,
p_mmtt_tbl_size => l_mmtt_tbl_size
);
insert_mtlt
(
x_return_status => l_return_status ,
p_mtlt_tbl => l_mtlt_tbl ,
p_mtlt_tbl_size => l_mtlt_tbl_size
);
insert_msnt
(
x_return_status => l_return_status ,
p_msnt_tbl => l_msnt_tbl ,
p_msnt_tbl_size => l_msnt_tbl_size
);
inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_material_transactions_temp: '
|| l_mmtt_tbl_size);
inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_transaction_lots_temp: '
|| l_mtlt_tbl_size);
inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_serial_numbers_temp: '
|| l_msnt_tbl_size);
SELECT decode(reservable_type,2,0,reservable_type)
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT decode(reservable_type,2,0,reservable_type)
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id;
SELECT decode(reservable_type,2,0,reservable_type)
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
Select organization_code
from mtl_parameters
Where organization_id = p_organization_id;
SELECT status_id into l_status_id
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND nvl(lot_number,-9999) = nvl(p_lot_number, -9999)
AND lpn_id = p_lpn_id
AND rownum = 1;
SELECT 'Y' into l_status_return FROM DUAL WHERE EXISTS(
SELECT 1 FROM mtl_onhand_quantities_detail moqd
WHERE moqd.organization_id = p_organization_id
AND moqd.inventory_item_id = p_inventory_item_id
AND moqd.subinventory_code = p_subinventory_code
AND nvl(moqd.locator_id,-999) = nvl(p_locator_id,-999)
AND nvl(moqd.lot_number,-999) = nvl(p_lot_number, -999)
AND NOT EXISTS(SELECT 1 from mtl_status_transaction_control mtc
WHERE mtc.status_id = moqd.status_id
AND mtc.transaction_type_id = p_transaction_type_id
AND mtc.is_allowed = 2 ));