[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pkg_name VARCHAR2(100) := 'lot_split_data_insert';
lot_split_data_insert.insert_mmtt(p_total_qty,
p_transaction_type_id,
p_userid,
x_return_status);
INV_TRX_UTIL_PUB.trace('after returning from insert_mmtt:Stat:' || x_return_status || ':',g_pkg_name,9);
tab_input.delete;
PROCEDURE insert_data(p_ind IN NUMBER,
p_ind_1st IN NUMBER,
p_userid IN NUMBER,
p_transaction_action_id IN NUMBER,
p_transaction_source_type_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_parent_id IN NUMBER,
p_dist_account_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_msg_data VARCHAR2(2000) := 'None..' ;
INSERT
INTO mtl_material_transactions_temp
(transaction_header_id
,transaction_temp_id
,transaction_mode
,lock_flag
,Process_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_type_id
,transaction_action_id
,transaction_source_type_id
,transaction_date
,acct_period_id
,distribution_account_id
,item_description
,item_location_control_code
,item_restrict_subinv_code
,item_restrict_locators_code
,item_revision_qty_control_code
,item_primary_uom_code
,item_shelf_life_code
,item_shelf_life_days
,item_lot_control_code
,item_serial_control_code
,allowed_units_lookup_code
,parent_transaction_temp_id
,lpn_id
,transfer_lpn_id
,cost_group_id
,project_id
,task_id
,transaction_batch_id
,transaction_batch_seq
,secondary_transaction_quantity -- Bug #4093379 INVCONV
,secondary_uom_code) -- Bug #4093379 INVCONV
VALUES
( tab_input(p_ind).transaction_header_id
,tab_input(p_ind).transaction_temp_id
,3
,'N'
,'Y'
,SYSDATE
,p_userid
,SYSDATE
,p_userid
,p_userid
,NULL
,NULL
,NULL
,NULL
,tab_input(p_ind).inventory_item_id
,tab_input(p_ind).revision
,tab_input(p_ind).organization_id
,tab_input(p_ind).subinventory_code
,tab_input(p_ind).locator_id
,tab_input(p_ind).transaction_quantity
,tab_input(p_ind).primary_quantity
,tab_input(p_ind).transaction_uom
,tab_input(p_ind).transaction_type_id
,p_transaction_action_id
,p_transaction_source_type_id
,SYSDATE
,p_acct_period_id
,p_dist_account_id
,tab_input(p_ind_1st).item_description
,tab_input(p_ind_1st).item_location_control_code
,tab_input(p_ind_1st).item_restrict_subinv_code
,tab_input(p_ind_1st).item_restrict_locators_code
,tab_input(p_ind_1st).item_revision_qty_control_code
,tab_input(p_ind_1st).item_primary_uom_code
,tab_input(p_ind_1st).item_shelf_life_code
,tab_input(p_ind_1st).item_shelf_life_days
,2
,1
,tab_input(p_ind_1st).allowed_units_lookup_code
,p_parent_id
,tab_input(p_ind).lpn_id
,tab_input(p_ind).xfr_lpn_id
,tab_input(p_ind).cost_group_id
,tab_input(p_ind).project_id
,tab_input(p_ind).task_id
,tab_input(p_ind).transaction_batch_id
,tab_input(p_ind).transaction_batch_seq
,tab_input(p_ind).secondary_transaction_quantity -- Bug #4093379 INVCONV
,tab_input(p_ind).item_secondary_uom_code); -- Bug #4093379 INVCONV
INV_TRX_UTIL_PUB.trace('inserted mmtt ..' || p_ind,g_pkg_name,9);
INV_TRX_UTIL_PUB.trace('inserting mtlt ' || p_ind,g_pkg_name,9);
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 -- Bug #40993379 INVCONV
,lot_number
,lot_expiration_date
,description
,vendor_id
,supplier_lot_number
,territory_code
,grade_code
,origination_date
,date_code
,status_id
,change_date
,age
,retest_date
,maturity_date
,lot_attribute_category
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,recycled_content
,thickness
,thickness_uom
,width
,width_uom
,curl_wrinkle_fold
,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
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,secondary_unit_of_measure -- Bug #4093379 INVCONV
,parent_lot_number -- Bug #4093379 INVCONV
,origination_type -- Bug #4093379 INVCONV
,expiration_action_date -- Bug #4093379 INVCONV
,expiration_action_code -- Bug #4093379 INVCONV
,hold_date -- Bug #4093379 INVCONV
,reason_id) -- Bug #4093379 INVCONV
VALUES
( tab_input(p_ind).transaction_temp_id
,SYSDATE
,p_userid
,SYSDATE
,p_userid
,p_userid
,NULL
,NULL
,NULL
,NULL
,abs(tab_input(p_ind).transaction_quantity)
,abs(tab_input(p_ind).primary_quantity)
,abs(tab_input(p_ind).secondary_transaction_quantity) -- Bug #4093379 INVCONV
,tab_input(p_ind).lot_number
,tab_input(p_ind).lot_expiration_date
,tab_input(p_ind).description
,tab_input(p_ind).vendor_id
,tab_input(p_ind).supplier_lot_number
,tab_input(p_ind).territory_code
,tab_input(p_ind).grade_code
,tab_input(p_ind).origination_date
,tab_input(p_ind).date_code
,tab_input(p_ind).status_id
,tab_input(p_ind).change_date
,tab_input(p_ind).age
,tab_input(p_ind).retest_date
,tab_input(p_ind).maturity_date
,tab_input(p_ind).lot_attribute_category
,tab_input(p_ind).item_size
,tab_input(p_ind).color
,tab_input(p_ind).volume
,tab_input(p_ind).volume_uom
,tab_input(p_ind).place_of_origin
,tab_input(p_ind).best_by_date
,tab_input(p_ind).length
,tab_input(p_ind).length_uom
,tab_input(p_ind).recycled_content
,tab_input(p_ind).thickness
,tab_input(p_ind).thickness_uom
,tab_input(p_ind).width
,tab_input(p_ind).width_uom
,tab_input(p_ind).curl_wrinkle_fold
,tab_input(p_ind).c_attribute1
,tab_input(p_ind).c_attribute2
,tab_input(p_ind).c_attribute3
,tab_input(p_ind).c_attribute4
,tab_input(p_ind).c_attribute5
,tab_input(p_ind).c_attribute6
,tab_input(p_ind).c_attribute7
,tab_input(p_ind).c_attribute8
,tab_input(p_ind).c_attribute9
,tab_input(p_ind).c_attribute10
,tab_input(p_ind).c_attribute11
,tab_input(p_ind).c_attribute12
,tab_input(p_ind).c_attribute13
,tab_input(p_ind).c_attribute14
,tab_input(p_ind).c_attribute15
,tab_input(p_ind).c_attribute16
,tab_input(p_ind).c_attribute17
,tab_input(p_ind).c_attribute18
,tab_input(p_ind).c_attribute19
,tab_input(p_ind).c_attribute20
,tab_input(p_ind).d_attribute1
,tab_input(p_ind).d_attribute2
,tab_input(p_ind).d_attribute3
,tab_input(p_ind).d_attribute4
,tab_input(p_ind).d_attribute5
,tab_input(p_ind).d_attribute6
,tab_input(p_ind).d_attribute7
,tab_input(p_ind).d_attribute8
,tab_input(p_ind).d_attribute9
,tab_input(p_ind).d_attribute10
,tab_input(p_ind).n_attribute1
,tab_input(p_ind).n_attribute2
,tab_input(p_ind).n_attribute3
,tab_input(p_ind).n_attribute4
,tab_input(p_ind).n_attribute5
,tab_input(p_ind).n_attribute6
,tab_input(p_ind).n_attribute7
,tab_input(p_ind).n_attribute8
,tab_input(p_ind).n_attribute9
,tab_input(p_ind).n_attribute10
,tab_input(p_ind).item_secondary_uom_code -- Bug #4093379 INVCONV
,tab_input(p_ind).parent_lot_number -- Bug #4093379 INVCONV
,tab_input(p_ind).origination_type -- Bug #4093379 INVCONV
,tab_input(p_ind).expiration_action_date -- Bug #4093379 INVCONV
,tab_input(p_ind).expiration_action_code -- Bug #4093379 INVCONV
,tab_input(p_ind).hold_date -- Bug #4093379 INVCONV
,tab_input(p_ind).reason_id -- Bug #4093379 INVCONV
);
INV_TRX_UTIL_PUB.trace('inserted mtlt ' || p_ind,g_pkg_name,9);
END insert_data;
PROCEDURE insert_mmtt(p_total_qty IN NUMBER,
p_transaction_type_id IN NUMBER,
p_userid IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
ind INTEGER := 0;
INV_TRX_UTIL_PUB.trace('in insert_mmtt',g_pkg_name,9);
l_msg_data := 'select header_id from dual';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM DUAL;
l_msg_data := 'select batch_id from dual';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_batch_id
FROM DUAL;
l_msg_data := 'select acct_period_id from org_acct_periods';
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,tab_input(1).organization_id)
>= trunc(period_start_date )
AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,tab_input(1).organization_id)
<= trunc(schedule_close_date)
AND organization_id = tab_input(1).organization_id;
SELECT distribution_account_id,
wsm_enabled_flag
INTO l_dist_account_id,
l_wsm_enabled_flag
FROM mtl_parameters
WHERE organization_id = tab_input(1).organization_id;
SELECT transaction_account_id
INTO l_dist_account_id
FROM wsm_parameters
WHERE organization_id = tab_input(1).organization_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_temp_id
FROM DUAL;
l_msg_data := 'select lpn';
SELECT lpn_id
INTO l_xfr_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = tab_input(ind).lpn_number
AND parent_lpn_id IS NULL
AND lpn_context IN (1,5); -- Bug No 3886482, Pick LPNS with status 'Defined But Not Used'
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO tab_input(ind).transaction_batch_seq
FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO tab_input(ind).transaction_batch_seq
FROM DUAL;
insert_data (p_ind => ind,
p_ind_1st => ind_1st ,
p_userid => p_userid ,
p_transaction_action_id => l_transaction_action_id ,
p_transaction_source_type_id => l_transaction_source_type_id ,
p_acct_period_id => l_acct_period_id ,
p_parent_id => l_parent_id ,
p_dist_account_id => l_dist_account_id,
x_return_status => x_return_status);
insert_data (p_ind => ind,
p_ind_1st => ind_1st ,
p_userid => p_userid ,
p_transaction_action_id => l_transaction_action_id ,
p_transaction_source_type_id => l_transaction_source_type_id ,
p_acct_period_id => l_acct_period_id ,
p_parent_id => l_parent_id ,
p_dist_account_id => l_dist_account_id,
x_return_status => x_return_status);
INV_TRX_UTIL_PUB.trace('insert_mmtt:G_EXC_ERROR: ' || sqlerrm ,g_pkg_name,9);
INV_TRX_UTIL_PUB.trace('insert_mmtt:G_EXC_UNEXPECTED_ERROR: ' || sqlerrm ,g_pkg_name,9);
END insert_mmtt;
PROCEDURE select_init_parameters(
p_organization_id IN MTL_ORGANIZATIONS.organization_id%TYPE,
x_stock_locator_code OUT NOCOPY MTL_parameters.stock_locator_control_code%TYPE,
x_wmsinstall OUT NOCOPY VARCHAR2,
x_wmsorg OUT NOCOPY VARCHAR2,
x_split_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
x_merge_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
x_translate_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
x_cost_group_id OUT NOCOPY CST_COST_GROUPS.cost_group_id%TYPE,
x_primary_cost_method OUT NOCOPY MTL_PARAMETERS.primary_cost_method%TYPE,
x_wsm_enabled_flag OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_dist_account_id OUT NOCOPY mtl_parameters.distribution_account_id%TYPE
)
IS
l_transaction_type_id MTL_TRANSACTION_TYPES.transaction_type_id%TYPE;
SELECT transaction_type_name , transaction_type_id
FROM mtl_transaction_types
WHERE transaction_type_id IN ( INV_Globals.G_type_inv_lot_split,
INV_Globals.G_type_inv_lot_merge,
INV_Globals.G_type_inv_lot_translate);
SELECT stock_locator_control_code
,default_cost_group_id
,primary_cost_method
,wsm_enabled_flag
,distribution_account_id
INTO x_stock_locator_code
,x_cost_group_id
,x_primary_cost_method
,x_wsm_enabled_flag
, x_dist_account_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT NVL(transaction_account_id, 0)
INTO x_dist_account_id
FROM wsm_parameters
WHERE organization_id = p_organization_id;
END select_init_parameters;
END lot_split_data_insert;