[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_profile.value('INV_DEBUG_TRACE')
INTO l_trace_on
FROM dual;
SELECT group_id,
to_organization_id,
item_id,
item_revision,
shipment_header_id,
po_line_id,
quantity,
unit_of_measure,
uom_code,
header_interface_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
select GRADE_CODE
, fnd_date.date_to_canonical(ORIGINATION_DATE)
, DATE_CODE
, to_char(STATUS_ID)
, fnd_date.date_to_canonical(CHANGE_DATE)
, to_number(AGE)
, fnd_date.date_to_canonical(RETEST_DATE)
, fnd_date.date_to_canonical(MATURITY_DATE)
, LOT_ATTRIBUTE_CATEGORY
, to_char(ITEM_SIZE)
, COLOR
, to_char(VOLUME )
, VOLUME_UOM_CODE
, PLACE_OF_ORIGIN
, fnd_date.date_to_canonical(BEST_BY_DATE )
, to_char(LENGTH )
, LENGTH_UOM_CODE
, to_char(RECYCLED_CONTENT )
, to_char(THICKNESS )
, THICKNESS_UOM_CODE
, to_char(WIDTH )
, WIDTH_UOM_CODE
, 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
, fnd_date.date_to_canonical(D_ATTRIBUTE1)
, fnd_date.date_to_canonical(D_ATTRIBUTE2)
, fnd_date.date_to_canonical(D_ATTRIBUTE3)
, fnd_date.date_to_canonical(D_ATTRIBUTE4)
, fnd_date.date_to_canonical(D_ATTRIBUTE5)
, fnd_date.date_to_canonical(D_ATTRIBUTE6)
, fnd_date.date_to_canonical(D_ATTRIBUTE7)
, fnd_date.date_to_canonical(D_ATTRIBUTE8)
, fnd_date.date_to_canonical(D_ATTRIBUTE9)
, fnd_date.date_to_canonical(D_ATTRIBUTE10)
, to_char(N_ATTRIBUTE1)
, to_char(N_ATTRIBUTE2)
, to_char(N_ATTRIBUTE3)
, to_char(N_ATTRIBUTE4)
, to_char(N_ATTRIBUTE5)
, to_char(N_ATTRIBUTE6)
, to_char(N_ATTRIBUTE7)
, to_char(N_ATTRIBUTE8)
, to_char(N_ATTRIBUTE10)
, SUPPLIER_LOT_NUMBER
, to_char(N_ATTRIBUTE9)
, territory_code
, STATUS_NAME
, EXPIRATION_DATE
, LOT_NUMBER
-- other columns
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
from wms_lpn_contents_interface
WHERE interface_transaction_id = p_interface_transaction_id
AND rownum = 1 ; --Bug#4437403.
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
SAVEPOINT InsertLot_sv;
select mtl_gen_object_id_s.nextval into l_object_id from dual;
SELECT LOT_CONTROL_CODE
INTO l_lot_control_code
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_inventory_item_id
AND ORGANIZATION_ID = l_organization_id;
SELECT LOT_NUMBER_UNIQUENESS
INTO l_lotunique
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_organization_id;
SELECT count(1)
INTO l_lotcount
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id <> l_inventory_item_id
AND lot_number = l_lot_number;
SELECT count(1)
INTO l_lotcount
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = l_inventory_item_id
AND ORGANIZATION_ID = l_organization_id
AND LOT_NUMBER = l_lot_number;
SELECT SHELF_LIFE_CODE,
SHELF_LIFE_DAYS
INTO l_shelf_life_code,
l_shelf_life_days
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_inventory_item_id
AND ORGANIZATION_ID = l_organization_id;
SELECT SYSDATE + l_shelf_life_days
INTO l_expiration_date
FROM DUAL;
select status_id
into l_lot_status_id
from mtl_material_statuses_vl
where status_code = l_status_name
;
print_debug('Before Inserting into MTL_LOT_NUMBERS ' , 4);
INSERT INTO MTL_LOT_NUMBERS
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LOT_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EXPIRATION_DATE,
DISABLE_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
GEN_OBJECT_ID,
DESCRIPTION,
VENDOR_ID,
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,
SUPPLIER_LOT_NUMBER,
N_ATTRIBUTE10,
TERRITORY_CODE)
VALUES
(l_inventory_item_id,
l_organization_id,
l_lot_number,
SYSDATE,
l_userid,
SYSDATE,
l_userid,
l_loginid,
l_expiration_date,
null,
l_ATTRIBUTE_CATEGORY ,
l_ATTRIBUTE1 ,
l_ATTRIBUTE2 ,
l_ATTRIBUTE3 ,
l_ATTRIBUTE4 ,
l_ATTRIBUTE5 ,
l_ATTRIBUTE6 ,
l_ATTRIBUTE7 ,
l_ATTRIBUTE8,
l_ATTRIBUTE9 ,
l_ATTRIBUTE10 ,
l_ATTRIBUTE11,
l_ATTRIBUTE12 ,
l_ATTRIBUTE13 ,
l_ATTRIBUTE14 ,
l_ATTRIBUTE15 ,
null,
null,
null,
null,
l_object_id,
null,
to_number(null), -- Vendor ID currently Set as Null
g_lot_attributes_tbl(1).COLUMN_VALUE,
fnd_date.canonical_to_date(g_lot_attributes_tbl(2).COLUMN_VALUE),
g_lot_attributes_tbl(3).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(4).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(5).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(6).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(7).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(8).COLUMN_VALUE),
g_lot_attributes_tbl(9).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(10).COLUMN_VALUE),
g_lot_attributes_tbl(11).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(12).COLUMN_VALUE),
g_lot_attributes_tbl(13).COLUMN_VALUE,
g_lot_attributes_tbl(14).COLUMN_VALUE,
fnd_date.canonical_to_date(g_lot_attributes_tbl(15).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(16).COLUMN_VALUE),
g_lot_attributes_tbl(17).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(18).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(19).COLUMN_VALUE),
g_lot_attributes_tbl(20).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(21).COLUMN_VALUE),
g_lot_attributes_tbl(22).COLUMN_VALUE,
g_lot_attributes_tbl(23).COLUMN_VALUE,
g_lot_attributes_tbl(24).COLUMN_VALUE,
g_lot_attributes_tbl(25).COLUMN_VALUE,
g_lot_attributes_tbl(26).COLUMN_VALUE,
g_lot_attributes_tbl(27).COLUMN_VALUE,
g_lot_attributes_tbl(28).COLUMN_VALUE,
g_lot_attributes_tbl(29).COLUMN_VALUE,
g_lot_attributes_tbl(30).COLUMN_VALUE,
g_lot_attributes_tbl(31).COLUMN_VALUE,
g_lot_attributes_tbl(32).COLUMN_VALUE,
g_lot_attributes_tbl(33).COLUMN_VALUE,
g_lot_attributes_tbl(34).COLUMN_VALUE,
g_lot_attributes_tbl(35).COLUMN_VALUE,
g_lot_attributes_tbl(36).COLUMN_VALUE,
g_lot_attributes_tbl(37).COLUMN_VALUE,
g_lot_attributes_tbl(38).COLUMN_VALUE,
g_lot_attributes_tbl(39).COLUMN_VALUE,
g_lot_attributes_tbl(40).COLUMN_VALUE,
g_lot_attributes_tbl(41).COLUMN_VALUE,
g_lot_attributes_tbl(42).COLUMN_VALUE,
g_lot_attributes_tbl(43).COLUMN_VALUE,
fnd_date.canonical_to_date(g_lot_attributes_tbl(44).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(45).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(46).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(47).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(48).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(49).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(50).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(51).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(52).COLUMN_VALUE),
fnd_date.canonical_to_date(g_lot_attributes_tbl(53).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(54).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(55).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(56).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(57).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(58).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(59).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(60).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(61).COLUMN_VALUE),
to_number(g_lot_attributes_tbl(62).COLUMN_VALUE),
g_lot_attributes_tbl(63).COLUMN_VALUE,
to_number(g_lot_attributes_tbl(64).COLUMN_VALUE),
g_lot_attributes_tbl(65).COLUMN_VALUE);
print_debug('After Inserting into MTL_LOT_NUMBERS ' , 4);
ROLLBACK TO InsertLot_sv;
ROLLBACK TO InsertLot_sv;
ROLLBACK TO InsertLot_sv;
PROCEDURE insertSerial(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_serial_number IN VARCHAR2,
p_initialization_date IN DATE,
p_completion_date IN DATE,
p_ship_date IN DATE,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_current_locator_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_trx_src_id IN NUMBER,
p_unit_vendor_id IN NUMBER,
p_vendor_lot_number IN VARCHAR2,
p_vendor_serial_number IN VARCHAR2,
p_receipt_issue_type IN NUMBER,
p_txn_src_id IN NUMBER,
p_txn_src_name IN VARCHAR2,
p_txn_src_type_id IN NUMBER,
p_transaction_id IN NUMBER,
p_current_status IN NUMBER,
p_parent_item_id IN NUMBER,
p_parent_serial_number IN VARCHAR2,
p_cost_group_id IN NUMBER,
p_serial_transaction_intf_id IN NUMBER,
p_status_id IN NUMBER,
x_object_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'insertSerial';
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
select SERIAL_ATTRIBUTE_CATEGORY
, fnd_date.date_to_canonical(ORIGINATION_DATE )
, 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
, fnd_date.date_to_canonical(D_ATTRIBUTE1 )
, fnd_date.date_to_canonical(D_ATTRIBUTE2 )
, fnd_date.date_to_canonical(D_ATTRIBUTE3 )
, fnd_date.date_to_canonical(D_ATTRIBUTE4 )
, fnd_date.date_to_canonical(D_ATTRIBUTE5 )
, fnd_date.date_to_canonical(D_ATTRIBUTE6 )
, fnd_date.date_to_canonical(D_ATTRIBUTE7)
, fnd_date.date_to_canonical(D_ATTRIBUTE8)
, fnd_date.date_to_canonical( D_ATTRIBUTE9)
, fnd_date.date_to_canonical(D_ATTRIBUTE10 )
, to_char(N_ATTRIBUTE1 )
, to_char(N_ATTRIBUTE2)
, to_char(N_ATTRIBUTE3)
, to_char(N_ATTRIBUTE4)
, to_char(N_ATTRIBUTE5)
, to_char(N_ATTRIBUTE6)
, to_char(N_ATTRIBUTE7)
, to_char(N_ATTRIBUTE8)
,to_char( N_ATTRIBUTE9)
, to_char(N_ATTRIBUTE10)
, STATUS_ID
, TERRITORY_CODE
, TIME_SINCE_NEW
, CYCLES_SINCE_NEW
, TIME_SINCE_OVERHAUL
, CYCLES_SINCE_OVERHAUL
, TIME_SINCE_REPAIR
, CYCLES_SINCE_REPAIR
, TIME_SINCE_VISIT
, CYCLES_SINCE_VISIT
, TIME_SINCE_MARK
, CYCLES_SINCE_MARK
, NUMBER_OF_REPAIRS
, STATUS_NAME
-- other columns
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
from mtl_serial_numbers_interface
where TRANSACTION_INTERFACE_ID = P_SERIAL_TRANSACTION_INTF_ID;
print_debug('Inside Insert Serial ',4);
SAVEPOINT insertSerial_sv;
SELECT serial_number_control_code
INTO l_serial_control_code
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
select mtl_gen_object_id_s.nextval into x_object_id from dual;
select status_id
into l_status_id
from mtl_material_statuses_vl
where status_code = l_status_name
;
print_debug('Before Inserting into MTL_SERIAL_NUMBER' , 4);
INSERT INTO MTL_SERIAL_NUMBERS(
INVENTORY_ITEM_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INITIALIZATION_DATE,
COMPLETION_DATE,
SHIP_DATE,
CURRENT_STATUS,
REVISION,
LOT_NUMBER,
FIXED_ASSET_TAG,
RESERVED_ORDER_ID,
PARENT_ITEM_ID,
PARENT_SERIAL_NUMBER,
ORIGINAL_WIP_ENTITY_ID,
ORIGINAL_UNIT_VENDOR_ID,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
LAST_TXN_SOURCE_TYPE_ID,
LAST_TRANSACTION_ID,
LAST_RECEIPT_ISSUE_TYPE,
LAST_TXN_SOURCE_NAME,
LAST_TXN_SOURCE_ID,
DESCRIPTIVE_TEXT,
CURRENT_SUBINVENTORY_CODE,
CURRENT_LOCATOR_ID,
CURRENT_ORGANIZATION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GROUP_MARK_ID,
LINE_MARK_ID,
LOT_LINE_MARK_ID,
END_ITEM_UNIT_NUMBER,
GEN_OBJECT_ID,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
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,
STATUS_ID,
TERRITORY_CODE,
COST_GROUP_ID,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS
)
VALUES
(p_inventory_item_id,
p_serial_number,
SYSDATE,
l_userid,
SYSDATE,
l_userid,
l_loginid,
null,
null,
null,
null,
p_initialization_date,
p_completion_date,
p_ship_date,
p_current_status,
p_revision,
p_lot_number,
null,
null,
p_parent_item_id,
p_parent_serial_number,
p_trx_src_id,
p_unit_vendor_id,
p_vendor_serial_number,
p_vendor_lot_number,
p_txn_src_type_id,
p_transaction_id,
p_receipt_issue_type,
p_txn_src_name,
p_txn_src_id,
g_serial_attributes_tbl(31).COLUMN_VALUE,
p_subinventory_code,
p_current_locator_id,
p_organization_id,
l_ATTRIBUTE_CATEGORY ,
l_ATTRIBUTE1 ,
l_ATTRIBUTE2 ,
l_ATTRIBUTE3 ,
l_ATTRIBUTE4 ,
l_ATTRIBUTE5 ,
l_ATTRIBUTE6 ,
l_ATTRIBUTE7 ,
l_ATTRIBUTE8,
l_ATTRIBUTE9 ,
l_ATTRIBUTE10 ,
l_ATTRIBUTE11,
l_ATTRIBUTE12 ,
l_ATTRIBUTE13 ,
l_ATTRIBUTE14 ,
l_ATTRIBUTE15 ,
null,
null,
null,
null,
x_object_id,
g_serial_attributes_tbl(1).COLUMN_VALUE,
fnd_date.canonical_to_date(g_serial_attributes_tbl(2).COLUMN_VALUE),
g_serial_attributes_tbl(3).COLUMN_VALUE,
g_serial_attributes_tbl(4).COLUMN_VALUE,
g_serial_attributes_tbl(5).COLUMN_VALUE,
g_serial_attributes_tbl(6).COLUMN_VALUE,
g_serial_attributes_tbl(7).COLUMN_VALUE,
g_serial_attributes_tbl(8).COLUMN_VALUE,
g_serial_attributes_tbl(9).COLUMN_VALUE,
g_serial_attributes_tbl(10).COLUMN_VALUE,
g_serial_attributes_tbl(11).COLUMN_VALUE,
g_serial_attributes_tbl(12).COLUMN_VALUE,
g_serial_attributes_tbl(13).COLUMN_VALUE,
g_serial_attributes_tbl(14).COLUMN_VALUE,
g_serial_attributes_tbl(15).COLUMN_VALUE,
g_serial_attributes_tbl(16).COLUMN_VALUE,
g_serial_attributes_tbl(17).COLUMN_VALUE,
g_serial_attributes_tbl(18).COLUMN_VALUE,
g_serial_attributes_tbl(19).COLUMN_VALUE,
g_serial_attributes_tbl(20).COLUMN_VALUE,
g_serial_attributes_tbl(21).COLUMN_VALUE,
g_serial_attributes_tbl(22).COLUMN_VALUE,
fnd_date.canonical_to_date(g_serial_attributes_tbl(23).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(24).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(25).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(26).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(27).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(28).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(29).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(30).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(31).COLUMN_VALUE),
fnd_date.canonical_to_date(g_serial_attributes_tbl(32).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(33).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(34).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(35).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(36).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(37).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(38).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(39).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(40).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(41).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(42).COLUMN_VALUE),
l_status_id,
g_serial_attributes_tbl(44).COLUMN_VALUE,
INV_COST_GROUP_PUB.G_COST_GROUP_ID,
to_number(g_serial_attributes_tbl(45).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(46).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(47).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(48).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(49).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(50).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(51).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(52).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(53).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(54).COLUMN_VALUE),
to_number(g_serial_attributes_tbl(55).COLUMN_VALUE) );
l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_auto;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
ROLLBACK TO insertSerial_sv;
END insertSerial;
PROCEDURE insert_range_serial
(p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_initialization_date IN DATE,
p_completion_date IN DATE,
p_ship_date IN DATE,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_current_locator_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_trx_src_id IN NUMBER,
p_unit_vendor_id IN NUMBER,
p_vendor_lot_number IN VARCHAR2,
p_vendor_serial_number IN VARCHAR2,
p_receipt_issue_type IN NUMBER,
p_txn_src_id IN NUMBER,
p_txn_src_name IN VARCHAR2,
p_txn_src_type_id IN NUMBER,
p_transaction_id IN NUMBER,
p_current_status IN NUMBER,
p_parent_item_id IN NUMBER,
p_parent_serial_number IN VARCHAR2,
p_cost_group_id IN NUMBER,
p_serial_transaction_intf_id IN NUMBER,
p_status_id IN NUMBER,
p_inspection_status IN NUMBER,
x_object_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_from_ser_number NUMBER;
SAVEPOINT SP_insert_range_serial;
SELECT current_status, group_mark_id
INTO l_current_status, l_group_mark_id
FROM mtl_serial_numbers
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
print_debug('Calling Insert Serial for = '|| l_cur_serial_number ,4);
insertserial
(p_commit => p_commit,
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_serial_number => l_cur_serial_number,
p_initialization_date => p_initialization_date,
p_completion_date => p_completion_date,
p_ship_date => p_ship_date,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_current_locator_id => p_current_locator_id,
p_subinventory_code => p_subinventory_code,
p_trx_src_id => p_trx_src_id,
p_unit_vendor_id => p_unit_vendor_id,
p_vendor_lot_number => p_vendor_lot_number,
p_vendor_serial_number => p_vendor_serial_number,
p_receipt_issue_type => p_receipt_issue_type,
p_txn_src_id => p_txn_src_id,
p_txn_src_name => p_txn_src_name,
p_txn_src_type_id => p_txn_src_type_id,
p_transaction_id => p_transaction_id,
p_current_status => p_current_status,
p_parent_item_id => p_parent_item_id,
p_parent_serial_number => p_parent_serial_number,
p_cost_group_id => p_cost_group_id,
p_serial_transaction_intf_id => p_serial_transaction_intf_id,
p_status_id => p_status_id,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO SP_insert_range_serial;
ROLLBACK TO SP_insert_range_serial;
ROLLBACK TO SP_insert_range_serial;
END insert_range_serial;