[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select_stmt LONG :=
'SELECT NVL(MSNI.SERIAL_ATTRIBUTE_CATEGORY,
MSN.SERIAL_ATTRIBUTE_CATEGORY),
NVL(MSNI.C_ATTRIBUTE1,
MSN.C_ATTRIBUTE1),
NVL(MSNI.C_ATTRIBUTE2,
MSN.C_ATTRIBUTE2),
NVL(MSNI.C_ATTRIBUTE3,
MSN.C_ATTRIBUTE3),
NVL(MSNI.C_ATTRIBUTE4,
MSN.C_ATTRIBUTE4),
NVL(MSNI.C_ATTRIBUTE5,
MSN.C_ATTRIBUTE5),
NVL(MSNI.C_ATTRIBUTE6,
MSN.C_ATTRIBUTE6),
NVL(MSNI.C_ATTRIBUTE7,
MSN.C_ATTRIBUTE7),
NVL(MSNI.C_ATTRIBUTE8,
MSN.C_ATTRIBUTE8),
NVL(MSNI.C_ATTRIBUTE9,
MSN.C_ATTRIBUTE9),
NVL(MSNI.C_ATTRIBUTE10,
MSN.C_ATTRIBUTE10),
NVL(MSNI.C_ATTRIBUTE11,
MSN.C_ATTRIBUTE11),
NVL(MSNI.C_ATTRIBUTE12,
MSN.C_ATTRIBUTE12),
NVL(MSNI.C_ATTRIBUTE13,
MSN.C_ATTRIBUTE13),
NVL(MSNI.C_ATTRIBUTE14,
MSN.C_ATTRIBUTE14),
NVL(MSNI.C_ATTRIBUTE15,
MSN.C_ATTRIBUTE15),
NVL(MSNI.C_ATTRIBUTE16,
MSN.C_ATTRIBUTE16),
NVL(MSNI.C_ATTRIBUTE17,
MSN.C_ATTRIBUTE17),
NVL(MSNI.C_ATTRIBUTE18,
MSN.C_ATTRIBUTE18),
NVL(MSNI.C_ATTRIBUTE19,
MSN.C_ATTRIBUTE19),
NVL(MSNI.C_ATTRIBUTE20,
MSN.C_ATTRIBUTE20),
NVL(MSNI.D_ATTRIBUTE1,
MSN.D_ATTRIBUTE1),
NVL(MSNI.D_ATTRIBUTE2,
MSN.D_ATTRIBUTE2),
NVL(MSNI.D_ATTRIBUTE3,
MSN.D_ATTRIBUTE3),
NVL(MSNI.D_ATTRIBUTE4,
MSN.D_ATTRIBUTE4),
NVL(MSNI.D_ATTRIBUTE5,
MSN.D_ATTRIBUTE5),
NVL(MSNI.D_ATTRIBUTE6,
MSN.D_ATTRIBUTE6),
NVL(MSNI.D_ATTRIBUTE7,
MSN.D_ATTRIBUTE7),
NVL(MSNI.D_ATTRIBUTE8,
MSN.D_ATTRIBUTE8),
NVL(MSNI.D_ATTRIBUTE9,
MSN.D_ATTRIBUTE9),
NVL(MSNI.D_ATTRIBUTE10,
MSN.D_ATTRIBUTE10),
NVL(MSNI.N_ATTRIBUTE1,
MSN.N_ATTRIBUTE1),
NVL(MSNI.N_ATTRIBUTE2,
MSN.N_ATTRIBUTE2),
NVL(MSNI.N_ATTRIBUTE3,
MSN.N_ATTRIBUTE3),
NVL(MSNI.N_ATTRIBUTE4,
MSN.N_ATTRIBUTE4),
NVL(MSNI.N_ATTRIBUTE5,
MSN.N_ATTRIBUTE5),
NVL(MSNI.N_ATTRIBUTE6,
MSN.N_ATTRIBUTE6),
NVL(MSNI.N_ATTRIBUTE7,
MSN.N_ATTRIBUTE7),
NVL(MSNI.N_ATTRIBUTE8,
MSN.N_ATTRIBUTE8),
NVL(MSNI.N_ATTRIBUTE9,
MSN.N_ATTRIBUTE9),
NVL(MSNI.N_ATTRIBUTE10,
MSN.N_ATTRIBUTE10),
NVL(MSNI.TERRITORY_CODE,
MSN.TERRITORY_CODE),
NVL(MSNI.TIME_SINCE_NEW,
MSN.TIME_SINCE_NEW),
NVL(MSNI.CYCLES_SINCE_NEW,
MSN.CYCLES_SINCE_NEW),
NVL(MSNI.TIME_SINCE_OVERHAUL,
MSN.TIME_SINCE_OVERHAUL),
NVL(MSNI.CYCLES_SINCE_OVERHAUL,
MSN.CYCLES_SINCE_OVERHAUL),
NVL(MSNI.TIME_SINCE_REPAIR,
MSN.TIME_SINCE_REPAIR),
NVL(MSNI.CYCLES_SINCE_REPAIR,
MSN.CYCLES_SINCE_REPAIR),
NVL(MSNI.TIME_SINCE_VISIT,
MSN.TIME_SINCE_VISIT),
NVL(MSNI.CYCLES_SINCE_VISIT,
MSN.CYCLES_SINCE_VISIT),
NVL(MSNI.TIME_SINCE_MARK,
MSN.TIME_SINCE_MARK),
NVL(MSNI.CYCLES_SINCE_MARK,
MSN.CYCLES_SINCE_MARK),
NVL(MSNI.NUMBER_OF_REPAIRS,
MSN.NUMBER_OF_REPAIRS),
NVL(MSNI.ATTRIBUTE_CATEGORY,
MSN.ATTRIBUTE_CATEGORY),
NVL(MSNI.ATTRIBUTE1,
MSN.ATTRIBUTE1),
NVL(MSNI.ATTRIBUTE2,
MSN.ATTRIBUTE2),
NVL(MSNI.ATTRIBUTE3,
MSN.ATTRIBUTE3),
NVL(MSNI.ATTRIBUTE4,
MSN.ATTRIBUTE4),
NVL(MSNI.ATTRIBUTE5,
MSN.ATTRIBUTE5),
NVL(MSNI.ATTRIBUTE6,
MSN.ATTRIBUTE6),
NVL(MSNI.ATTRIBUTE7,
MSN.ATTRIBUTE7),
NVL(MSNI.ATTRIBUTE8,
MSN.ATTRIBUTE8),
NVL(MSNI.ATTRIBUTE9,
MSN.ATTRIBUTE9),
NVL(MSNI.ATTRIBUTE10,
MSN.ATTRIBUTE10),
NVL(MSNI.ATTRIBUTE11,
MSN.ATTRIBUTE11),
NVL(MSNI.ATTRIBUTE12,
MSN.ATTRIBUTE12),
NVL(MSNI.ATTRIBUTE13,
MSN.ATTRIBUTE13),
NVL(MSNI.ATTRIBUTE14,
MSN.ATTRIBUTE14),
NVL(MSNI.ATTRIBUTE15,
MSN.ATTRIBUTE15)
FROM
MTL_SERIAL_NUMBERS_INTERFACE MSNI,
MTL_SERIAL_NUMBERS MSN
WHERE
MSNI.TRANSACTION_INTERFACE_ID = :B_PARENT_ID AND
MSNI.FM_SERIAL_NUMBER = :B_FM_SERIAL_NUMBER AND
MSNI.TO_SERIAL_NUMBER = :B_TO_SERIAL_NUMBER AND
MSN.SERIAL_NUMBER = :B_SERIAL_NUMBER AND
MSN.INVENTORY_ITEM_ID = :B_ITEM_ID AND
MSN.CURRENT_ORGANIZATION_ID = :B_ORG_ID';
PROCEDURE errupdate(p_rowid IN VARCHAR2, lot_rowid IN VARCHAR2 DEFAULT NULL);
* in the parent lots. In this case, we need to insert additional
* record in mmtt for the remaining qty
* This procedure assumes that the primary qty is already calculated
* and the qty comparison is done with the primary qty.
* This procedure is called after calling LotTrxInsert
* As part of J-dev, we will use tmpInsert to
* move lot transaction records from MTI to MMTT. (also for I)
* Some changes have been made in this API for I + J, to enable bulk
* insert. do not re-insert the parent transaction.
* do not insert into MMTT here, but into MTI only, if we are
* creating a new record for this transaction
*******************************************************************/
Function Check_Partial_Split(
p_parent_id IN NUMBER,
p_current_index IN NUMBER
) RETURN boolean
IS
cursor mti_csr(p_interface_id NUMBER) IS
select mti.transaction_header_id,
mti.acct_period_id,
mti.distribution_account_id,
mti.transaction_interface_id,
mti.transaction_Type_id,
mti.inventory_item_id,
mti.revision,
mti.organization_id,
mti.subinventory_code,
mti.locator_id,
mti.transaction_quantity,
mti.primary_quantity,
mti.transaction_uom,
mti.lpn_id,
mti.transfer_lpn_id,
mti.cost_group_id,
mti.transaction_source_type_id,
mti.transaction_Action_id,
mti.parent_id,
mti.created_by,
mtli.lot_number,
mtli.lot_expiration_date,
mtli.description,
mtli.vendor_id,
mtli.supplier_lot_number,
mtli.territory_code,
mtli.grade_code,
mtli.origination_date,
mtli.date_code,
mtli.status_id,
mtli.change_date,
mtli.age,
mtli.retest_date,
mtli.maturity_date,
mtli.lot_attribute_category,
mtli.item_size,
mtli.color,
mtli.volume,
mtli.volume_uom,
mtli.place_of_origin,
mtli.best_by_date,
mtli.length,
mtli.length_uom,
mtli.recycled_content,
mtli.thickness,
mtli.thickness_uom,
mtli.width,
mtli.width_uom,
mtli.curl_wrinkle_fold,
mtli.c_attribute1,
mtli.c_Attribute2,
mtli.c_attribute3,
mtli.c_attribute4,
mtli.c_attribute5,
mtli.c_attribute6,
mtli.c_attribute7,
mtli.c_attribute8,
mtli.c_attribute9,
mtli.c_attribute10,
mtli.c_attribute11,
mtli.c_attribute12,
mtli.c_attribute13,
mtli.c_attribute14,
mtli.c_attribute15,
mtli.c_attribute16,
mtli.c_attribute17,
mtli.c_attribute18,
mtli.c_attribute19,
mtli.c_attribute20,
mtli.d_attribute1,
mtli.d_attribute2,
mtli.d_attribute3,
mtli.d_attribute4,
mtli.d_attribute5,
mtli.d_attribute6,
mtli.d_attribute7,
mtli.d_attribute8,
mtli.d_attribute9,
mtli.d_attribute10,
mtli.n_attribute1,
mtli.n_attribute2,
mtli.n_attribute3,
mtli.n_attribute4,
mtli.n_attribute5,
mtli.n_attribute6,
mtli.n_attribute7,
mtli.n_attribute8,
mtli.n_attribute9,
mtli.n_attribute10,
mtli.attribute1,
mtli.attribute2,
mtli.attribute3,
mtli.attribute4,
mtli.attribute5,
mtli.attribute6,
mtli.attribute7,
mtli.attribute8,
mtli.attribute9,
mtli.attribute10,
mtli.attribute11,
mtli.attribute12,
mtli.attribute13,
mtli.attribute14,
mtli.attribute15,
mtli.attribute_category,
mtli.parent_object_type, --R12 Genealogy enhancements
mtli.parent_object_id, --R12 Genealogy enhancements
mtli.parent_object_number, --R12 Genealogy enhancements
mtli.parent_item_id, --R12 Genealogy enhancements
mtli.parent_object_type2, --R12 Genealogy enhancements
mtli.parent_object_id2, --R12 Genealogy enhancements
mtli.parent_object_number2, --R12 Genealogy enhancements
msi.description item_description,
msi.location_control_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.revision_qty_control_code,
msi.primary_uom_code,
msi.shelf_life_code,
msi.shelf_life_days,
msi.allowed_units_lookup_code,
mti.transaction_batch_id,
mti.transaction_batch_seq,
mti.kanban_card_id,
mti.transaction_mode --J-dev
FROM MTL_TRANSACTIONS_INTERFACE MTI,
MTL_TRANSACTION_LOTS_INTERFACE MTLI,
MTL_SYSTEM_ITEMS_B MSI
WHERE mti.transaction_interface_id = p_interface_id
AND MTI.transaction_interface_id = mtli.transaction_interface_id
AND MTI.organization_id = msi.organization_id
AND mti.inventory_item_id = msi.inventory_item_id
and mti.process_flag = 1;
SELECT count(parent_id)
INTO l_count
FROM mtl_transactions_interface
WHERE parent_id = p_parent_id;
SELECT abs(primary_quantity)
INTO l_split_qty
FROM mtl_transactions_interface
WHERE transaction_interface_id = p_parent_id;
SELECT sum(abs(primary_quantity))
INTO l_partial_total_qty
FROM mtl_transactions_interface
WHERE parent_id = p_parent_id
AND transaction_interface_id <> p_parent_id;
select mtl_material_transactions_s.nextval
into l_transaction_interface_id --J-dev
FROM dual;
inv_log_util.trace('insert into mmti is ' || l_mti_csr.transaction_interface_id, 'INV_TXN_MANAGER_GRP', 9);
INSERT INTO mtl_transactions_interface
( transaction_header_id ,
transaction_interface_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,*/--J-dev not in MTI
parent_id,--J-dev
lpn_id ,
transfer_lpn_id
,cost_group_id,
transaction_batch_id,
transaction_batch_seq,
kanban_card_id)
VALUES
( l_mti_csr.transaction_header_id,
l_transaction_interface_id,--J-dev
l_mti_csr.transaction_mode /*2722754 */,
2,--J-dev
1,--J-dev
sysdate,
l_mti_csr.created_by,
sysdate,
l_mti_csr.created_by,
l_mti_csr.created_by,
NULL,
NULL,
NULL,
NULL,
l_mti_csr.inventory_item_id,
l_mti_csr.revision,
l_mti_csr.organization_id,
l_mti_csr.subinventory_code,
l_mti_csr.locator_id,
l_remaining_qty,
l_remaining_qty,
l_mti_csr.primary_uom_code,
l_mti_csr.transaction_type_id,
l_mti_csr.transaction_action_id,
l_mti_csr.transaction_source_type_id,
sysdate,
l_mti_csr.acct_period_id,
l_mti_csr.distribution_account_id,
/*l_mti_csr.item_description,
l_mti_csr.location_control_code,
l_mti_csr.restrict_subinventories_code,
l_mti_csr.restrict_locators_code,
l_mti_csr.revision_qty_control_code,
l_mti_csr.primary_uom_code,
l_mti_csr.shelf_life_code,
l_mti_csr.shelf_life_days,
2,
1,
l_mti_csr.allowed_units_lookup_code,*/--J-dev Not in MTI
l_mti_csr.parent_id,
l_mti_csr.lpn_id,
l_mti_csr.transfer_lpn_id,
l_mti_csr.cost_group_id,
l_mti_csr.transaction_batch_id,
l_mti_csr.transaction_batch_seq,
l_mti_csr.kanban_card_id);
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, --J-dev
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,
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 ,
attribute1 ,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category ,
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2) --R12 Genealogy enhancements
VALUES
( l_transaction_interface_id,
SYSDATE,
l_mti_csr.created_by,
SYSDATE,
l_mti_csr.created_by,
l_mti_Csr.created_by,
NULL,
NULL,
NULL,
NULL,
l_remaining_qty,
l_remaining_qty,
l_mti_csr.lot_number,
l_mti_csr.lot_expiration_date,
l_mti_csr.description,
l_mti_csr.vendor_id,
l_mti_csr.supplier_lot_number,
l_mti_csr.territory_code,
l_mti_csr.grade_code,
l_mti_csr.origination_date,
l_mti_csr.date_code,
l_mti_csr.status_id,
l_mti_csr.change_date,
l_mti_csr.age,
l_mti_csr.retest_date,
l_mti_csr.maturity_date,
l_mti_csr.lot_attribute_category,
l_mti_csr.item_size,
l_mti_csr.color,
l_mti_csr.volume,
l_mti_csr.volume_uom,
l_mti_csr.place_of_origin,
l_mti_csr.best_by_date,
l_mti_csr.length,
l_mti_csr.length_uom,
l_mti_csr.recycled_content,
l_mti_csr.thickness,
l_mti_csr.thickness_uom,
l_mti_csr.width,
l_mti_csr.width_uom,
l_mti_csr.curl_wrinkle_fold,
l_mti_csr.c_attribute1,
l_mti_csr.c_attribute2,
l_mti_csr.c_attribute3,
l_mti_csr.c_attribute4,
l_mti_csr.c_attribute5,
l_mti_csr.c_attribute6,
l_mti_csr.c_attribute7,
l_mti_csr.c_attribute8,
l_mti_csr.c_attribute9,
l_mti_csr.c_attribute10,
l_mti_csr.c_attribute11,
l_mti_csr.c_attribute12,
l_mti_csr.c_attribute13,
l_mti_csr.c_attribute14,
l_mti_csr.c_attribute15,
l_mti_csr.c_attribute16,
l_mti_csr.c_attribute17,
l_mti_csr.c_attribute18,
l_mti_csr.c_attribute19,
l_mti_csr.c_attribute20,
l_mti_csr.d_attribute1,
l_mti_csr.d_attribute2,
l_mti_csr.d_attribute3,
l_mti_csr.d_attribute4,
l_mti_csr.d_attribute5,
l_mti_csr.d_attribute6,
l_mti_csr.d_attribute7,
l_mti_csr.d_attribute8,
l_mti_csr.d_attribute9,
l_mti_csr.d_attribute10,
l_mti_csr.n_attribute1,
l_mti_csr.n_attribute2,
l_mti_csr.n_attribute3,
l_mti_csr.n_attribute4,
l_mti_csr.n_attribute5,
l_mti_csr.n_attribute6,
l_mti_csr.n_attribute7,
l_mti_csr.n_attribute8,
l_mti_csr.n_attribute9,
l_mti_csr.n_attribute10,
l_mti_csr.attribute1,
l_mti_csr.attribute2,
l_mti_csr.attribute3,
l_mti_csr.attribute4,
l_mti_csr.attribute5,
l_mti_csr.attribute6,
l_mti_csr.attribute7,
l_mti_csr.attribute8,
l_mti_csr.attribute9,
l_mti_csr.attribute10,
l_mti_csr.attribute11,
l_mti_csr.attribute12,
l_mti_csr.attribute13,
l_mti_csr.attribute14,
l_mti_csr.attribute15,
l_mti_csr.attribute_category,
l_mti_csr.parent_object_type, --R12 Genealogy enhancements
l_mti_csr.parent_object_id, --R12 Genealogy enhancements
l_mti_csr.parent_object_number, --R12 Genealogy enhancements
l_mti_csr.parent_item_id, --R12 Genealogy enhancements
l_mti_csr.parent_object_type2, --R12 Genealogy enhancements
l_mti_csr.parent_object_id2, --R12 Genealogy enhancements
l_mti_csr.parent_object_number2); --R12 Genealogy enhancements
SELECT CHART_OF_ACCOUNTS_ID
INTO l_chart
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = p_orgid;
SELECT DST_SEGMENT1, DST_SEGMENT2, DST_SEGMENT3,
DST_SEGMENT4, DST_SEGMENT5, DST_SEGMENT6,
DST_SEGMENT7, DST_SEGMENT8, DST_SEGMENT9,
DST_SEGMENT10, DST_SEGMENT11, DST_SEGMENT12,
DST_SEGMENT13, DST_SEGMENT14, DST_SEGMENT15,
DST_SEGMENT16, DST_SEGMENT17, DST_SEGMENT18,
DST_SEGMENT19, DST_SEGMENT20, DST_SEGMENT21,
DST_SEGMENT22, DST_SEGMENT23, DST_SEGMENT24,
DST_SEGMENT25, DST_SEGMENT26, DST_SEGMENT27,
DST_SEGMENT28, DST_SEGMENT29, DST_SEGMENT30,
TRANSACTION_DATE /*Bug#5176266*/
INTO tmp_seg_arr(1).colvalue, tmp_seg_arr(2).colvalue, tmp_seg_arr(3).colvalue,
tmp_seg_arr(4).colvalue, tmp_seg_arr(5).colvalue, tmp_seg_arr(6).colvalue,
tmp_seg_arr(7).colvalue, tmp_seg_arr(8).colvalue, tmp_seg_arr(9).colvalue,
tmp_seg_arr(10).colvalue, tmp_seg_arr(11).colvalue, tmp_seg_arr(12).colvalue,
tmp_seg_arr(13).colvalue, tmp_seg_arr(14).colvalue, tmp_seg_arr(15).colvalue,
tmp_seg_arr(16).colvalue, tmp_seg_arr(17).colvalue, tmp_seg_arr(18).colvalue,
tmp_seg_arr(19).colvalue, tmp_seg_arr(20).colvalue, tmp_seg_arr(21).colvalue,
tmp_seg_arr(22).colvalue, tmp_seg_arr(23).colvalue, tmp_seg_arr(24).colvalue,
tmp_seg_arr(25).colvalue, tmp_seg_arr(26).colvalue, tmp_seg_arr(27).colvalue,
tmp_seg_arr(28).colvalue, tmp_seg_arr(29).colvalue, tmp_seg_arr(30).colvalue,
l_trxdate/*Bug#5176266*/
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ROWID = p_rowid;
SELECT CHART_OF_ACCOUNTS_ID
INTO l_chart
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = p_orgid;
SELECT
ITEM_SEGMENT1,
ITEM_SEGMENT2,
ITEM_SEGMENT3,
ITEM_SEGMENT4,
ITEM_SEGMENT5,
ITEM_SEGMENT6,
ITEM_SEGMENT7,
ITEM_SEGMENT8,
ITEM_SEGMENT9,
ITEM_SEGMENT10,
ITEM_SEGMENT11,
ITEM_SEGMENT12,
ITEM_SEGMENT13,
ITEM_SEGMENT14,
ITEM_SEGMENT15,
ITEM_SEGMENT16,
ITEM_SEGMENT17,
ITEM_SEGMENT18,
ITEM_SEGMENT19,
ITEM_SEGMENT20
INTO
l_segs1(1),
l_segs1(2),
l_segs1(3),
l_segs1(4),
l_segs1(5),
l_segs1(6),
l_segs1(7),
l_segs1(8),
l_segs1(9),
l_segs1(10),
l_segs1(11),
l_segs1(12),
l_segs1(13),
l_segs1(14),
l_segs1(15),
l_segs1(16),
l_segs1(17),
l_segs1(18),
l_segs1(19),
l_segs1(20)
FROM mtl_transactions_interface mti
WHERE mti.rowid = p_rowid;
SELECT_COMB_FROM_VIEW => 'MTL_SYSTEM_ITEMS_FVL') ;
SELECT DSP_SEGMENT1, DSP_SEGMENT2, DSP_SEGMENT3,
DSP_SEGMENT4, DSP_SEGMENT5, DSP_SEGMENT6,
DSP_SEGMENT7, DSP_SEGMENT8, DSP_SEGMENT9,
DSP_SEGMENT10, DSP_SEGMENT11, DSP_SEGMENT12,
DSP_SEGMENT13, DSP_SEGMENT14, DSP_SEGMENT15,
DSP_SEGMENT16, DSP_SEGMENT17, DSP_SEGMENT18,
DSP_SEGMENT19, DSP_SEGMENT20, DSP_SEGMENT21,
DSP_SEGMENT22, DSP_SEGMENT23, DSP_SEGMENT24,
DSP_SEGMENT25, DSP_SEGMENT26, DSP_SEGMENT27,
DSP_SEGMENT28, DSP_SEGMENT29, DSP_SEGMENT30
INTO tmp_seg_arr(1).colvalue, tmp_seg_arr(2).colvalue, tmp_seg_arr(3).colvalue,
tmp_seg_arr(4).colvalue, tmp_seg_arr(5).colvalue, tmp_seg_arr(6).colvalue,
tmp_seg_arr(7).colvalue, tmp_seg_arr(8).colvalue, tmp_seg_arr(9).colvalue,
tmp_seg_arr(10).colvalue, tmp_seg_arr(11).colvalue, tmp_seg_arr(12).colvalue,
tmp_seg_arr(13).colvalue, tmp_seg_arr(14).colvalue, tmp_seg_arr(15).colvalue,
tmp_seg_arr(16).colvalue, tmp_seg_arr(17).colvalue, tmp_seg_arr(18).colvalue,
tmp_seg_arr(19).colvalue, tmp_seg_arr(20).colvalue, tmp_seg_arr(21).colvalue,
tmp_seg_arr(22).colvalue, tmp_seg_arr(23).colvalue, tmp_seg_arr(24).colvalue,
tmp_seg_arr(25).colvalue, tmp_seg_arr(26).colvalue, tmp_seg_arr(27).colvalue,
tmp_seg_arr(28).colvalue, tmp_seg_arr(29).colvalue, tmp_seg_arr(30).colvalue
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ROWID = p_rowid;
SELECT CHART_OF_ACCOUNTS_ID
INTO l_chart
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = p_orgid;
* errupdate()
*
******************************************************************/
/* Bug 5343678 rowid is a keyword so we should never pass it as parameter */
/* Also passed the reqd number of parameters in all places just to avoid confusion as
parameter name is not prefixed in any of the calls */
PROCEDURE errupdate(p_rowid IN VARCHAR2 DEFAULT NULL, lot_rowid IN VARCHAR2 DEFAULT NULL)
IS
l_userid NUMBER := -1; -- = prg_info.userid;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE ROWID = lot_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2
WHERE ROWID = p_rowid;
END errupdate;
SELECT inventory_item_id,
organization_id,
distribution_account_id,
transaction_source_type_id,
transaction_source_id,
transaction_date, --Bug#5176266.
rowid
FROM MTL_TRANSACTIONS_INTERFACE
WHERE transaction_header_id = p_header_id
AND process_flag = 1
AND (inventory_item_id is NULL OR distribution_account_id is NULL
OR
(transaction_source_id is NULL AND transaction_source_type_id in (2,3,6,8))); --Bug 2971400
errupdate(c_mti_row.rowid,null);
errupdate(c_mti_row.rowid,null);
SELECT DISTRIBUTION_ACCOUNT
INTO l_acctid
FROM MTL_GENERIC_DISPOSITIONS
WHERE ORGANIZATION_ID = c_mti_row.organization_id
AND DISPOSITION_ID = l_trxsrc;
errupdate(c_mti_row.rowid,null);
errupdate(c_mti_row.rowid,null);
errupdate(c_mti_row.rowid,null);
UPDATE MTL_TRANSACTIONS_INTERFACE
SET inventory_item_id = l_itemid,
distribution_account_id = l_acctid,
transaction_source_id = l_trxsrc
WHERE rowid = c_mti_row.rowid;
SELECT lot_divisible_flag
, tracking_quantity_ind
, secondary_default_ind
, secondary_uom_code
, primary_uom_code /* Bug 5446542 */
FROM mtl_system_items
WHERE organization_id = org_id
AND inventory_item_id = item_id;
errupdate(p_rowid, p_lot_rowid);
errupdate(p_rowid, p_lot_rowid);
errupdate(p_rowid, p_lot_rowid);
errupdate(p_rowid, p_lot_rowid);
errupdate(p_rowid, p_lot_rowid);
errupdate(p_rowid, p_lot_rowid);
* For patchet J, wip desktop transactions will be inserted into MTI,
* validated by INV and moved to MMTT. Since WIP already does some
* validations, we will by-pass some validation done here (based on the
* variable l_validation_full)
* The validation to be done for WIP desktop are:
* MTI WIP Validation
* DESKTOP WIP TRANSACTIONS
* 1. Flow Schedule will only exist for WIP Transactions
* 2. Derive Transaction_action_id and Transaction_source_type_id
* from transaction_type_id
* 3. Validate Shippable_flag records in MTI have transaction_enabled items.
* 4. Validate Inventory_item_flag, Inventory_asset_flag Costing Enabled_Flag
* 5. Validate Subinventory_code(disabled ?)
* 6. Validate Transfer Subinventory(disabled ?)
* 7. Validate Restricted Subinventories
* 8. Validate Subinventory for the following:
* -You cannot issue from non tracked
* - You cannot transfer from expense sub to asset sub for asset items
* -If the expense to asset transfer allowed profile is set then
* You cannot issue from a non-tracked sub
* All other transfers are valid
* 9. Validate Transaction_type_id (disable date )
* 10. Validate Transaction_action(currently we do not support cycle
* count
* and some internal orders through MTI)
* 11. Validate Organization(disable date)
* 12. Validate Locators(disable date)
* 13. Validate Revision ( should exists in mtl_item_revisions,
* we do not validate against disbaled_date for revision).
* 14. Validate Transaction reasons(disable date)
* 15. Validate Transaction Qty Sign
* 16. Validate OverCompletion Trx qtyshould not be -ve or greater
* than the transaction qty.
* 17. Validate Distribution Account
*(if dist account id is not null or if null,should be an asset item/sub)
*18. Validate transaction UOM
*19. Validate cost_group, Xfr cost Groups
* 20. Validate VMI: po_asl_attributes
* 21. Validate LPN, Xfr LPN, Contenet LPN
* All Other validations will be by-passed
******************************************************************/
PROCEDURE validate_group(
p_header_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_userid NUMBER,
p_loginid NUMBER,
p_validation_level NUMBER:= fnd_api.g_valid_level_full)
IS
srctypeid NUMBER;
SELECT
ROWID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_UOM_CODE
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
ORDER BY ORGANIZATION_ID,INVENTORY_ITEM_ID,REVISION,
SUBINVENTORY_CODE,LOCATOR_ID;
SELECT lot_control_code, tracking_quantity_ind, secondary_uom_code
FROM mtl_system_items
WHERE organization_id = org_id
AND inventory_item_id = item_id;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
TRANSACTION_ACTION_ID =
(SELECT MTT.TRANSACTION_ACTION_ID
FROM MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID
= MTI.TRANSACTION_TYPE_ID),
TRANSACTION_SOURCE_TYPE_ID =
(SELECT MTT.TRANSACTION_SOURCE_TYPE_ID
FROM MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID
= MTI.TRANSACTION_TYPE_ID)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1;
UPDATE mtl_transactions_interface mti
SET last_update_date = SYSDATE,
last_updated_by = p_userid,
last_update_login = p_loginid,
program_update_date = SYSDATE,
process_flag = 3,
lock_flag = 2,
error_code = substrb(l_error_code,1,240),
error_explanation = substrb(l_error_exp,1,240)
WHERE transaction_header_id = p_header_id
AND process_flag = 1
AND transaction_type_id in (25, 90, 91, 92, 38, 48, 55,
56, 57, 58, 24, 93, 66, 67,
68, 80, 94, 26, 28, 77)
AND EXISTS
(SELECT 'This is a Process Mfg Org'
FROM mtl_parameters mp
WHERE mp.process_enabled_flag = 'Y'
AND mp.organization_id = mti.organization_id
);
SELECT TRANSACTION_SOURCE_TYPE_ID,
DECODE(UPPER(NVL(FLOW_SCHEDULE,'N')), 'Y',1,0),
NVL(SCHEDULED_FLAG,0)
INTO srctypeid, tvu_flow_schedule, tev_scheduled_flag
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND ROWNUM < 2;
SELECT
DECODE(UPPER(NVL(FLOW_SCHEDULE,'N')), 'Y',1,0)
INTO flow_schedule_children
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_ACTION_ID IN (1, 27, 33, 34)
AND PROCESS_FLAG = 1
AND ROWNUM < 2;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND process_flag = 1
AND ((transaction_source_type_id = inv_globals.G_sourcetype_inventory
AND transaction_action_id IN
(inv_globals.G_action_logicalissue,
inv_globals.G_action_logicalicsales,
inv_globals.G_action_logicalicreceipt,
inv_globals.G_action_logicalicrcptreturn,
inv_globals.G_action_logicalicsalesreturn,
inv_globals.G_action_logicalreceipt)) OR
(transaction_source_type_id = inv_globals.G_sourcetype_rma
AND transaction_action_id = inv_globals.G_action_logicalreceipt)
OR
(transaction_source_type_id = inv_globals.G_sourcetype_purchaseorder
AND transaction_action_id in
(inv_globals.G_action_logicalissue,
-- inv_globals.G_action_logicaldeladj,
-- inv_globals.G_action_logicalreceipt,
inv_globals.G_action_retropriceupdate)) OR
(transaction_source_type_id = inv_globals.G_sourcetype_rma
AND transaction_action_id = inv_globals.G_action_logicalreceipt) OR
(transaction_source_type_id = inv_globals.G_sourcetype_intreq
AND transaction_action_id = inv_globals.G_action_logicalexpreqreceipt) OR
(transaction_source_type_id = inv_globals.G_sourcetype_salesorder
and transaction_action_id = inv_globals.G_action_logicalissue)) ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID <> 24
AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT (TRANSACTION_SOURCE_TYPE_ID = 5 AND
nvl(OPERATION_SEQ_NUM,1) < 0 AND nvl(WIP_SUPPLY_TYPE,0) = 6)
-- AND ((TRANSACTION_SOURCE_TYPE_ID = 5 AND WIP_SUPPLY_TYPE <> 6) OR
-- (transaction_source_type_id <>5))
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y');
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND INVENTORY_ITEM_ID IS NOT NULL
AND (TRANSACTION_ACTION_ID NOT IN (1, 27, 33, 34)
OR TRANSACTION_SOURCE_TYPE_ID <> 5)
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_FLAG = 'Y');
| The items are validated seperately for average cost update
| transactions as done in the form
+----------------------------------------------------------------*/
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 24
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_FLAG = 'Y'
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
AND MSI.COSTING_ENABLED_FLAG = 'Y');
| The inv layer is validated seperately for layer cost update
| transactions. Only positive qty layers can be updated.
+----------------------------------------------------------------*/
IF (l_validate_full) THEN--J-dev
loaderrmsg('INV_POS_QTY_LAYER','INV_POS_QTY_LAYER');
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 24
AND TRANSACTION_SOURCE_TYPE_ID = 15
AND NOT EXISTS (
SELECT NULL
FROM CST_INV_LAYERS CIL
WHERE CIL.INV_LAYER_ID = MTI.TRANSACTION_SOURCE_ID
AND CIL.LAYER_QUANTITY > 0);
inv_log_util.trace('Validating for layer cost update ' || l_count || ' failed', 'INV_TXN_MANAGER_GRP', 9);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 3
AND EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MS1,
MTL_SYSTEM_ITEMS MS2
WHERE MS1.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MS1.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MS2.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MS2.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND ((MS1.LOT_CONTROL_CODE = 1 AND
MS2.LOT_CONTROL_CODE = 2)
OR (MS1.SERIAL_NUMBER_CONTROL_CODE IN (1,6)
AND MS2.SERIAL_NUMBER_CONTROL_CODE IN (2,3,5))
OR (MS1.REVISION_QTY_CONTROL_CODE = 1 AND
MS2.REVISION_QTY_CONTROL_CODE = 2)));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_ACTION_ID = 3
AND PROCESS_FLAG = 1
AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y');
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID NOT IN (24, 30) /* CFM Scrap Transactions */
AND (NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE)
OR (SHIPPABLE_FLAG = 'N'
AND SUBINVENTORY_CODE IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE)));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND (TRANSACTION_ACTION_ID IN (2,3,21,5)
AND TRANSFER_SUBINVENTORY IS NOT NULL)
AND ((NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID =
DECODE(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID,5,MTI.organization_id,
MTI.TRANSFER_ORGANIZATION)
AND MSI.SECONDARY_INVENTORY_NAME = MTI.TRANSFER_SUBINVENTORY
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE))
OR (SHIPPABLE_FLAG = 'N'
AND TRANSFER_SUBINVENTORY IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID =
DECODE(MTI.TRANSACTION_ACTION_ID,3,
MTI.TRANSFER_ORGANIZATION,21,
MTI.TRANSFER_ORGANIZATION,MTI.ORGANIZATION_ID)
AND MSI.SECONDARY_INVENTORY_NAME =
MTI.TRANSFER_SUBINVENTORY
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE)));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND SUBINVENTORY_CODE IS NOT NULL
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MIS.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MTI.SUBINVENTORY_CODE
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSFER_SUBINVENTORY IS NOT NULL
AND TRANSACTION_ACTION_ID in (2,21,3,5)
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID, 2,
MTI.ORGANIZATION_ID,
MTI.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MIS.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID,
MTI.TRANSFER_ORGANIZATION)
AND MIS.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MTI.TRANSFER_SUBINVENTORY
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID,
MTI.TRANSFER_ORGANIZATION)
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
SELECT FND_PROFILE.VALUE('INV:EXPENSE_TO_ASSET_TRANSFER')
INTO l_profile
FROM dual;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.QUANTITY_TRACKED = 2))
OR (TRANSACTION_ACTION_ID = 21
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI,
MTL_SYSTEM_ITEMS ITM
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ITM.INVENTORY_ASSET_FLAG = 'Y'
AND MSI.ASSET_INVENTORY = 2)));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID in (2,5)
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI,
MTL_SYSTEM_ITEMS ITM
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.INVENTORY_ASSET_FLAG = 'Y'
AND MSI.ASSET_INVENTORY = 2)
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI,
MTL_SYSTEM_ITEMS ITM
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.TRANSFER_SUBINVENTORY
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.INVENTORY_ASSET_FLAG = 'Y'
AND MSI.ASSET_INVENTORY = 1);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 3
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 2)
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.TRANSFER_SUBINVENTORY
AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MSI.ASSET_INVENTORY = 1);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
AND EXISTS (
SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.QUANTITY_TRACKED = 2)
)) ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_SOURCE_TYPE_ID in (2,8)
AND NOT EXISTS (
SELECT NULL
FROM MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID = MTI.TRANSACTION_SOURCE_ID
AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
AND ENABLED_FLAG = 'Y');
/*Bug#5176266. Made changes to the below UPDATE statement
to validate the Account effective date against transactions
date( not sysdate)*/
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_SOURCE_TYPE_ID = 3
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE GCC.CODE_COMBINATION_ID = MTI.TRANSACTION_SOURCE_ID
AND GCC.CHART_OF_ACCOUNTS_ID = OOD.CHART_OF_ACCOUNTS_ID
AND OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND GCC.ENABLED_FLAG = 'Y'
AND NVL(GCC.START_DATE_ACTIVE, MTI.TRANSACTION_DATE - 1) <= MTI.TRANSACTION_DATE
AND NVL(GCC.END_DATE_ACTIVE, MTI.TRANSACTION_DATE + 1) > MTI.TRANSACTION_DATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_SOURCE_TYPE_ID = INV_GLOBALS.G_SourceType_AccountAlias
AND NOT EXISTS (
SELECT NULL
FROM MTL_GENERIC_DISPOSITIONS MGD
WHERE MGD.DISPOSITION_ID = MTI.TRANSACTION_SOURCE_ID
AND MGD.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MGD.ENABLED_FLAG = 'Y'
AND NVL(MGD.EFFECTIVE_DATE, SYSDATE - 1) <= SYSDATE
AND NVL(MGD.DISABLE_DATE, SYSDATE + 1) > SYSDATE );
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND nvl(MTT.DISABLE_DATE,SYSDATE+1) > SYSDATE );
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND (TRANSACTION_ACTION_ID in (4,8,12,28,29)
OR (TRANSACTION_ACTION_ID = 30 AND UPPER(NVL(FLOW_SCHEDULE,'N')) <> 'Y')); /* CFM Scrap Transaction */
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND PROCESS_FLAG = 1
AND (NOT EXISTS (
SELECT NULL
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE)
OR NOT EXISTS (
SELECT NULL
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.TO_ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MIP.FROM_ORGANIZATION_ID = MTI.ORGANIZATION_ID));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID NOT IN (24,33,34)
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_REVISIONS MIR,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
AND MIR.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIR.REVISION = MTI.REVISION
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
AND MTI.REVISION IS NULL
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MTI.ORGANIZATION_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 3
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_REVISIONS MIR,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
AND MIR.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MIR.REVISION = MTI.REVISION
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
-- AND MTI.REVISION IS NULL --Bug#3285134. No REVISION validation for Revision to Non-Revision Org Xfer
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND REASON_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_REASONS MTR
WHERE MTR.REASON_ID = MTI.REASON_ID
AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND INVENTORY_ITEM_ID IS NOT NULL
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_UOMS_VIEW MIUV
WHERE MIUV.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIUV.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MIUV.UOM_CODE = MTI.TRANSACTION_UOM);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_QUANTITY > 0
AND TRANSACTION_ACTION_ID IN (1,21,32,34);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_QUANTITY < 0
AND TRANSACTION_ACTION_ID IN (12,27,31,33);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET TRANSACTION_QUANTITY = l_transaction_quantity,
SECONDARY_TRANSACTION_QUANTITY = l_secondary_quantity,
SECONDARY_UOM_CODE = l_secondary_uom_code
WHERE ROWID = l_rowid;
errupdate(l_rowid, NULL);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET SECONDARY_UOM_CODE = l_item_secondary_uom_code
WHERE ROWID = l_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET SECONDARY_TRANSACTION_QUANTITY = NULL,
SECONDARY_UOM_CODE = NULL
WHERE ROWID = l_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND NVL(OVERCOMPLETION_TRANSACTION_QTY,1) <= 0;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND OVERCOMPLETION_TRANSACTION_QTY IS NOT NULL
AND OVERCOMPLETION_TRANSACTION_QTY > TRANSACTION_QUANTITY;
/*Bug#5176266. Made changes to the below UPDATE statement
to validate the Account effective date against transactions
date( not sysdate)*/
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND DISTRIBUTION_ACCOUNT_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.DISTRIBUTION_ACCOUNT_ID
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND NVL(GCC.START_DATE_ACTIVE, MTI.TRANSACTION_DATE - 1) <= MTI.TRANSACTION_DATE
AND NVL(GCC.END_DATE_ACTIVE, MTI.TRANSACTION_DATE + 1) > MTI.TRANSACTION_DATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND DISTRIBUTION_ACCOUNT_ID IS NULL
AND (TRANSACTION_SOURCE_TYPE_ID = INV_Globals.G_SourceType_Inventory OR
TRANSACTION_SOURCE_TYPE_ID >=100)
AND NVL(OWNING_ORGANIZATION_ID,organization_id) = organization_id
AND NVL(OWNING_TP_TYPE,2) = 2 -- if it is null we are considering it as normal item..
-- Added the above two lines for the bug # 5896859
AND (TRANSACTION_ACTION_ID IN (1,27)
AND EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI,
mtl_secondary_inventories MSUB
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
AND MSUB.SECONDARY_INVENTORY_NAME = MTI.SUBINVENTORY_CODE
AND MSUB.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSUB.ASSET_INVENTORY = 1));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND FREIGHT_CODE IS NOT NULL
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM ORG_FREIGHT FR
WHERE FR.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND FR.FREIGHT_CODE = MTI.FREIGHT_CODE
AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND TRANSPORTATION_ACCOUNT IS NOT NULL
AND PROCESS_FLAG = 1
AND NOT EXISTS (
SELECT NULL
FROM ORG_FREIGHT FR
WHERE FR.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND FR.FREIGHT_CODE = MTI.FREIGHT_CODE
AND FR.DISTRIBUTION_Account = MTI.TRANSPORTATION_ACCOUNT
AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND UPPER(NVL(FLOW_SCHEDULE,'N')) = 'N'
AND NOT EXISTS (
SELECT NULL
FROM WIP_ENTITIES WEN
WHERE WEN.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND WEN.WIP_ENTITY_ID = MTI.TRANSACTION_SOURCE_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND UPPER(NVL(FLOW_SCHEDULE,'N')) = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM WIP_ENTITIES WEN
WHERE WEN.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND WEN.WIP_ENTITY_ID = MTI.TRANSACTION_SOURCE_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROCESS_FLAG = 3,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_SOURCE_TYPE_ID = INV_GLOBALS.G_SourceType_PrjContracts
AND NOT EXISTS (
SELECT NULL
FROM OKE_K_HEADERS_V OKHV
WHERE MTI.TRANSACTION_SOURCE_ID = OKHV.K_HEADER_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 1
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM PA_TASKS_LOWEST_V TSK
WHERE TSK.PROJECT_ID = MTI.SOURCE_PROJECT_ID AND
TSK.TASK_ID = MTI.SOURCE_TASK_ID );
SELECT FND_PROFILE.VALUE('INV_PROJ_MISC_TXN_EXP_TYPE')
INTO l_profile
FROM dual;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 1
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM CST_PROJ_EXP_TYPES_VAL_V CET
WHERE CET.EXPENDITURE_TYPE = MTI.EXPENDITURE_TYPE
AND CET.COST_ELEMENT_ID = 1
AND TRUNC(MTI.TRANSACTION_DATE) >= CET.SYS_LINK_START_DATE
AND TRUNC(MTI.TRANSACTION_DATE) <= NVL(SYS_LINK_END_DATE,
MTI.TRANSACTION_DATE + 1)
AND TRUNC(MTI.TRANSACTION_DATE) >= CET.EXP_TYPE_START_DATE
AND TRUNC(MTI.TRANSACTION_DATE) <= NVL(EXP_TYPE_END_DATE,
MTI.TRANSACTION_DATE+1)) ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 1
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND MTI.EXPENDITURE_TYPE IS NOT NULL ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
--AND TRANSACTION_ACTION_ID = 24
--AND TRANSACTION_SOURCE_TYPE_ID IN (13,15)
AND COST_GROUP_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = MTI.COST_GROUP_ID
AND NVL(CCG.ORGANIZATION_ID, MTI.ORGANIZATION_ID)
=MTI.ORGANIZATION_ID
AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE) ) ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSFER_COST_GROUP_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = MTI.TRANSFER_COST_GROUP_ID
AND NVL(CCG.ORGANIZATION_ID, MTI.ORGANIZATION_ID) =
MTI.ORGANIZATION_ID
AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE) ) ;
/*Bug#4951558. In the where clause of the below UPDATE statement, added the
condition, 'paa.using_organization_code = -1' because
'using_organization_code' is set to -1 in the table, 'po_asl_attributes' if
the global flag is set to 'Yes' in the ASL for an (item, supplier)
combination*/
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND PLANNING_ORGANIZATION_ID IS NOT NULL
AND planning_tp_type = 1
AND planning_organization_id <> ORGANIZATION_ID
AND NOT EXISTS (
SELECT NULL
FROM po_asl_attributes paa
WHERE PAA.vendor_site_id = mti.planning_organization_id
AND PAA.ITEM_ID = MTI.INVENTORY_ITEM_ID
AND (paa.using_organization_id = -1 OR
paa.using_organization_id = mti.organization_id));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND PLANNING_ORGANIZATION_ID IS NOT NULL
AND planning_tp_type = 2
AND planning_organization_id <> ORGANIZATION_ID
AND NOT EXISTS (
SELECT NULL
FROM mtl_parameters mp
where
mp.organization_id = mti.planning_organization_id);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND lpn_id IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM wms_license_plate_numbers wlpn
where
wlpn.lpn_id = mti.lpn_id);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSFER_LPN_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM wms_license_plate_numbers wlpn
where
wlpn.lpn_id = mti.TRANSFER_LPN_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND CONTENT_LPN_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM wms_license_plate_numbers wlpn
where
wlpn.lpn_id = mti.CONTENT_LPN_ID);
| Update the interface table with shippable item flag
| to be used for OE transactions
+-------------------------------------------------------------*/
IF (srctypeid = INV_GLOBALS.G_SourceType_SalesOrder OR
srctypeid = INV_GLOBALS.G_SourceType_IntOrder OR
srctypeid = INV_GLOBALS.G_SourceType_PrjContracts) THEN
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
SHIPPABLE_FLAG = (SELECT SHIPPABLE_ITEM_FLAG
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND INVENTORY_ITEM_ID IS NOT NULL
AND TRANSACTION_SOURCE_TYPE_ID in (2,8,16)
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
INVENTORY_ITEM_ID = (SELECT DECODE(MTI.TRANSACTION_ACTION_ID,30,
PRIMARY_ITEM_ID,31,PRIMARY_ITEM_ID,32,
PRIMARY_ITEM_ID,
MTI.INVENTORY_ITEM_ID) -- CFM Scrap Transactions
FROM WIP_ENTITIES WE
WHERE WE.WIP_ENTITY_ID = MTI.TRANSACTION_SOURCE_ID
AND WE.ORGANIZATION_ID = MTI.ORGANIZATION_ID),
WIP_ENTITY_TYPE = (SELECT ENTITY_TYPE
FROM WIP_ENTITIES WE
WHERE WE.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = MTI.TRANSACTION_SOURCE_ID)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND PROCESS_FLAG = 1;
| Update MTI with right op seq num for non-CFMs
+--------------------------------------------------------------*/
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
OPERATION_SEQ_NUM = (SELECT nvl(max(operation_seq_num),1)
FROM WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = MTI.TRANSACTION_SOURCE_ID
AND WO.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND UPPER(NVL(FLOW_SCHEDULE,'N')) = 'N'
AND TRANSACTION_ACTION_ID IN (32,31)
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID IN
(SELECT DISTINCT MTI2.TRANSACTION_BATCH_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI2
WHERE MTI2.TRANSACTION_HEADER_ID = p_header_id
AND MTI2.PROCESS_FLAG = 3
AND MTI2.ERROR_CODE IS NOT NULL);
update EAM validations from Transaction Manager */
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1
AND ((REBUILD_ITEM_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI, MTL_PARAMETERS MP
WHERE MSI.INVENTORY_ITEM_ID = MTI.REBUILD_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.EAM_ITEM_TYPE = 3
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'))
OR (REBUILD_ACTIVITY_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.REBUILD_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.EAM_ITEM_TYPE = 2)));
SELECT LOT_NUMBER,
TRANSACTION_QUANTITY,
-- INVCONV start fabdi
SECONDARY_TRANSACTION_QUANTITY,
GRADE_CODE,
RETEST_DATE,
MATURITY_DATE,
PARENT_LOT_NUMBER,
ORIGINATION_DATE,
ORIGINATION_TYPE,
EXPIRATION_ACTION_CODE,
EXPIRATION_ACTION_DATE,
LOT_EXPIRATION_DATE,
HOLD_DATE,
REASON_ID,
-- INVCONV end fabdi
SERIAL_TRANSACTION_TEMP_ID,
fnd_date.date_to_canonical(LOT_EXPIRATION_DATE),
ROWID,
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2 --R12 Genealogy enhancements
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = p_intid;
SELECT copy_lot_attribute_flag,
lot_number_generation
FROM mtl_parameters
WHERE organization_id = p_orgid;
SELECT COPY_LOT_ATTRIBUTE_FLAG , child_lot_flag
from mtl_system_items
WHERE inventory_item_id = p_itemid
AND organization_id = p_orgid;
SELECT transaction_interface_id
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ROWID = p_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = p_rowid
AND ABS(TRANSACTION_QUANTITY) <>
(SELECT ABS(SUM(TRANSACTION_QUANTITY))
FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI
WHERE MTLI.TRANSACTION_INTERFACE_ID = p_intid);
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_INTERFACE_ID = p_intid
AND EXISTS (
SELECT NULL
FROM MTL_LOT_NUMBERS MLN
WHERE MLN.LOT_NUMBER = MTLI.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID <> p_itemid);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = p_rowid;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET TRANSACTION_QUANTITY = l_lotqty,
SECONDARY_TRANSACTION_QUANTITY = l_secondary_quantity
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET GRADE_CODE = l_grade_code ,
RETEST_DATE = l_retest_date ,
MATURITY_DATE = l_maturity_date,
PARENT_LOT_NUMBER = l_parent_lot_number,
ORIGINATION_DATE = l_origination_date,
ORIGINATION_TYPE = l_origination_type ,
EXPIRATION_ACTION_CODE = l_expiration_action_code,
EXPIRATION_ACTION_DATE = l_expiration_action_date ,
LOT_EXPIRATION_DATE = l_expiration_date ,
HOLD_DATE = l_hold_date,
REASON_ID = l_reason_id
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2
WHERE ROWID = p_rowid;
SELECT
fnd_date.date_to_canonical(EXPIRATION_DATE)
INTO l_lotexpdate
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = p_itemid
AND ORGANIZATION_ID = p_orgid
AND LOT_NUMBER = l_lotnum;
SELECT fnd_date.date_to_canonical(SYSDATE + p_shlfdays)
INTO l_lotexpdate
FROM DUAL;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2
WHERE ROWID = p_rowid;
SELECT 1
into l_tnum
FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_sertempid
AND ROWNUM < 2;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = p_rowid;
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_sertempid;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
PRIMARY_QUANTITY = l_lotpriqty,
LOT_EXPIRATION_DATE = fnd_date.canonical_to_date(l_lotexpdate),
SERIAL_TRANSACTION_TEMP_ID = l_sertempid
WHERE ROWID = l_lotrowid;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE ROWID = p_rowid;
SELECT
LOCATOR_ID,
LOC_SEGMENT1,
LOC_SEGMENT2,
LOC_SEGMENT3,
LOC_SEGMENT4,
LOC_SEGMENT5,
LOC_SEGMENT6,
LOC_SEGMENT7,
LOC_SEGMENT8,
LOC_SEGMENT9,
LOC_SEGMENT10,
LOC_SEGMENT11,
LOC_SEGMENT12,
LOC_SEGMENT13,
LOC_SEGMENT14,
LOC_SEGMENT15,
LOC_SEGMENT16,
LOC_SEGMENT17,
LOC_SEGMENT18,
LOC_SEGMENT19,
LOC_SEGMENT20
INTO
l_locid,
l_segs1(1),
l_segs1(2),
l_segs1(3),
l_segs1(4),
l_segs1(5),
l_segs1(6),
l_segs1(7),
l_segs1(8),
l_segs1(9),
l_segs1(10),
l_segs1(11),
l_segs1(12),
l_segs1(13),
l_segs1(14),
l_segs1(15),
l_segs1(16),
l_segs1(17),
l_segs1(18),
l_segs1(19),
l_segs1(20)
FROM mtl_transactions_interface mti
WHERE mti.rowid = p_rowid;
SELECT
TRANSFER_LOCATOR,
XFER_LOC_SEGMENT1,
XFER_LOC_SEGMENT2,
XFER_LOC_SEGMENT3,
XFER_LOC_SEGMENT4,
XFER_LOC_SEGMENT5,
XFER_LOC_SEGMENT6,
XFER_LOC_SEGMENT7,
XFER_LOC_SEGMENT8,
XFER_LOC_SEGMENT9,
XFER_LOC_SEGMENT10,
XFER_LOC_SEGMENT11,
XFER_LOC_SEGMENT12,
XFER_LOC_SEGMENT13,
XFER_LOC_SEGMENT14,
XFER_LOC_SEGMENT15,
XFER_LOC_SEGMENT16,
XFER_LOC_SEGMENT17,
XFER_LOC_SEGMENT18,
XFER_LOC_SEGMENT19,
XFER_LOC_SEGMENT20
INTO
l_locid,
l_segs1(1),
l_segs1(2),
l_segs1(3),
l_segs1(4),
l_segs1(5),
l_segs1(6),
l_segs1(7),
l_segs1(8),
l_segs1(9),
l_segs1(10),
l_segs1(11),
l_segs1(12),
l_segs1(13),
l_segs1(14),
l_segs1(15),
l_segs1(16),
l_segs1(17),
l_segs1(18),
l_segs1(19),
l_segs1(20)
FROM mtl_transactions_interface mti
WHERE mti.rowid = p_rowid;
SELECT
LOCATOR_ID,
LOC_SEGMENT1,
LOC_SEGMENT2,
LOC_SEGMENT3,
LOC_SEGMENT4,
LOC_SEGMENT5,
LOC_SEGMENT6,
LOC_SEGMENT7,
LOC_SEGMENT8,
LOC_SEGMENT9,
LOC_SEGMENT10,
LOC_SEGMENT11,
LOC_SEGMENT12,
LOC_SEGMENT13,
LOC_SEGMENT14,
LOC_SEGMENT15,
LOC_SEGMENT16,
LOC_SEGMENT17,
LOC_SEGMENT18,
'',
''
INTO
l_locid,
l_segs1(1),
l_segs1(2),
l_segs1(3),
l_segs1(4),
l_segs1(5),
l_segs1(6),
l_segs1(7),
l_segs1(8),
l_segs1(9),
l_segs1(10),
l_segs1(11),
l_segs1(12),
l_segs1(13),
l_segs1(14),
l_segs1(15),
l_segs1(16),
l_segs1(17),
l_segs1(18),
l_segs1(19),
l_segs1(20)
FROM mtl_transactions_interface mti
WHERE mti.rowid = p_rowid;
SELECT
TRANSFER_LOCATOR,
XFER_LOC_SEGMENT1,
XFER_LOC_SEGMENT2,
XFER_LOC_SEGMENT3,
XFER_LOC_SEGMENT4,
XFER_LOC_SEGMENT5,
XFER_LOC_SEGMENT6,
XFER_LOC_SEGMENT7,
XFER_LOC_SEGMENT8,
XFER_LOC_SEGMENT9,
XFER_LOC_SEGMENT10,
XFER_LOC_SEGMENT11,
XFER_LOC_SEGMENT12,
XFER_LOC_SEGMENT13,
XFER_LOC_SEGMENT14,
XFER_LOC_SEGMENT15,
XFER_LOC_SEGMENT16,
XFER_LOC_SEGMENT17,
XFER_LOC_SEGMENT18,
'',
''
INTO
l_locid,
l_segs1(1),
l_segs1(2),
l_segs1(3),
l_segs1(4),
l_segs1(5),
l_segs1(6),
l_segs1(7),
l_segs1(8),
l_segs1(9),
l_segs1(10),
l_segs1(11),
l_segs1(12),
l_segs1(13),
l_segs1(14),
l_segs1(15),
l_segs1(16),
l_segs1(17),
l_segs1(18),
l_segs1(19),
l_segs1(20)
FROM mtl_transactions_interface mti
WHERE mti.rowid = p_rowid;
SELECT PROJECT_ID, TASK_ID
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = p_ltv_trx_src_id
AND ROWNUM < 2 ;
SELECT PROJECT_ID, TASK_ID
FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = p_ltv_trx_src_id
AND ROWNUM < 2 ;
SELECT DECODE(NVL(PROJECT_REFERENCE_ENABLED, 2),1,1,0)
INTO l_ltv_project_ref_enabled
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_ltv_orgid ;
SELECT count(1) INTO l_unit_no_ok
FROM PJM_UNIT_NUMBERS_LOV_V
WHERE UNIT_NUMBER =p_unit_number;
SELECT Nvl(project_reference_enabled,2), default_cost_group_id, primary_cost_method
INTO l_project_enabled, l_org_cost_group_id, l_primary_cost_method
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT project_id INTO l_project_id
FROM mtl_item_locations
WHERE organization_id =p_org_id
AND inventory_location_id = p_locatorid;
SELECT mpp.costing_group_id INTO l_cost_group_id
FROM mrp_project_parameters mpp
WHERE mpp.project_id = l_project_id
AND mpp.organization_id = p_org_id;
SELECT default_cost_group_id INTO l_cost_group_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv
AND organization_id = p_org_id
AND default_cost_group_id IS NOT NULL;
* update_mil() : To update dynamic locators in autonomous mode
* Added for Bug# 5044059
* Added a parameter p_plocid Physical Locator for Bug# 7323175
******************************************************************/
PROCEDURE update_mil(p_userid NUMBER,
p_loginid NUMBER,
p_applid NUMBER,
p_progid NUMBER,
p_reqstid NUMBER,
p_subinv VARCHAR2,
p_default_locator_status NUMBER,
p_orgid NUMBER,
p_locid NUMBER,
p_plocid NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT 1 INTO l_count
FROM MTL_ITEM_LOCATIONS
WHERE ORGANIZATION_ID = p_orgid
/* Start: Fix for Bug# 7323175 : Also considering the physical locator for locking */
--AND INVENTORY_LOCATION_ID = p_locid
AND INVENTORY_LOCATION_ID IN (p_locid,p_plocid)
/* End: Fix for Bug# 7323175 */
AND SUBINVENTORY_CODE is NULL FOR UPDATE NOWAIT;
UPDATE MTL_ITEM_LOCATIONS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_APPLICATION_ID = p_applid,
PROGRAM_ID = p_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = p_reqstid,
SUBINVENTORY_CODE = p_subinv,
STATUS_ID = p_default_locator_status,
/* Start: Fix for Bug# 7323175: Stamping PHYSICAL_LOCATION_ID with the physical locator for both
physical and logical locators for Project enabled Orgs. For the case of Non project enabled orgs
p_plocid would be null */
PHYSICAL_LOCATION_ID = p_plocid
/* End: Fix for Bug# 7323175 */
WHERE ORGANIZATION_ID = p_orgid
/* Start: Fix for Bug# 7323175: Updating the physical locator as well in mil */
--AND INVENTORY_LOCATION_ID = p_locid
AND INVENTORY_LOCATION_ID IN (p_locid,p_plocid)
/* End: Fix for Bug# 7323175 */
AND SUBINVENTORY_CODE is NULL;
inv_log_util.trace('Rows updated in MIL = '||SQL%ROWCOUNT, 'INV_TXN_MANAGER_GRP','9');
inv_log_util.trace('update_mil SQL : ' || substr(sqlerrm, 1, 200), 'INV_TXN_MANAGER_GRP','9');
END update_mil;
/*Bug#5125632. Added the following procedure to update the lot status
of lots for a given line in MTLI, if they are NULL.
Fist MTL_LOT_NUMBERS is checked to find the status. If there is no row in this
table, then, MTL_SYSTEM_ITEMS is checked to fetch the 'default_lot_status_id'
for the corresponding item*/
Procedure update_status_id_in_mtli( p_txn_interface_id IN NUMBER
,p_org_id IN NUMBER
,p_inventory_item_id IN NUMBER ) Is
l_status_enabled VARCHAR2(1);
SELECT ROWID
, lot_number
, status_id
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE transaction_interface_id = p_txn_interface_id;
inv_log_util.trace('Entered The Procedure update_status_id_in_mtli() with the parameters:', 'INV_TXN_MANAGER_GRP','1');
SELECT status_id
INTO l_mtli_status_id
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = lots_rec.lot_number;
inv_log_util.trace('After selecting from MLN, Value is:', 'INV_TXN_MANAGER_GRP','1');
SELECT lot_status_enabled
,default_lot_status_id
INTO l_status_enabled
,l_status_id
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
inv_log_util.trace('After selecting from MSI, Values are:', 'INV_TXN_MANAGER_GRP','1');
inv_log_util.trace('Before Update of MTLI', 'INV_TXN_MANAGER_GRP','1');
UPDATE mtl_transaction_lots_interface
SET status_id = l_mtli_status_id
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
,last_update_login = fnd_global.login_id
,request_id = fnd_global.conc_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = Decode(fnd_global.conc_request_id, -1, NULL, SYSDATE)
WHERE ROWID = lots_rec.rowid;
inv_log_util.trace('After Update of MTLI', 'INV_TXN_MANAGER_GRP','1');
inv_log_util.trace('Exception occurred in update_status_id_in_mtli procedure:', 'INV_TXN_MANAGER_GRP','1');
End update_status_id_in_mtli;
* and inserts them into the corresponding master tables.
* These validations are only needed for Receipt into stores transaction -> 27
* and for the transaction sources -> 3,6 and 13.
*
******************************************************************/
PROCEDURE validate_lot_serial_for_rcpt
(p_interface_id IN NUMBER
, p_org_id IN NUMBER
, p_item_id IN NUMBER
, p_lotctrl IN NUMBER
, p_serctrl IN NUMBER
, p_rev IN VARCHAR2 DEFAULT NULL
, p_trx_src_id IN NUMBER DEFAULT NULL
, p_trx_action_id IN NUMBER DEFAULT NULL
, p_subinventory_code IN VARCHAR2 DEFAULT NULL
, p_locator_id IN NUMBER DEFAULT NULL
, x_proc_msg OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_MTLI IS
SELECT LOT_NUMBER
, LOT_EXPIRATION_DATE
, 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
, STATUS_ID
, 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
, SERIAL_TRANSACTION_TEMP_ID
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = p_interface_id;
SELECT FM_SERIAL_NUMBER
, TO_SERIAL_NUMBER
FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = interface_id;
SELECT 1 INTO l_lot_exists
FROM DUAL
WHERE EXISTS(SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = rec_MTLI.LOT_NUMBER
AND inventory_item_id = p_item_id
AND organization_id = p_org_id);
SELECT
TRANSACTION_INTERFACE_ID,
TRANSACTION_HEADER_ID,
REQUEST_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
TRANSFER_ORGANIZATION,
TRANSFER_SUBINVENTORY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_QUANTITY,
LOCATOR_ID,
TRANSFER_LOCATOR,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
DISTRIBUTION_ACCOUNT_ID,
NVL(SHIPPABLE_FLAG,'Y'),
ROWID,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
REQUISITION_LINE_ID,
OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
END_ITEM_UNIT_NUMBER,
SCHEDULED_PAYBACK_DATE, /* Borrow Payback */
REVISION, /* Borrow Payback */
ORG_COST_GROUP_ID, /* PCST */
COST_TYPE_ID, /* PCST */
PRIMARY_QUANTITY,
SOURCE_LINE_ID,
PROCESS_FLAG,
TRANSACTION_SOURCE_NAME,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PARENT_ID,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
-- INVCONV start fabdi
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_UOM_CODE
-- INVCONV end fabdi
,SHIP_TO_LOCATION_ID --eIB Build; Bug# 4348541
l_org_cost_group_id NUMBER; /* PCST (Periodic Cost Update) */
l_avg_cost_update NUMBER;
l_avg_cost_update := 2;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 1
AND EXISTS (
SELECT null
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT null
FROM pa_projects_expend_v prj1
WHERE prj1.project_id = mti.source_project_id ) ;
inv_log_util.trace('After update on MTI for INV_PRJ_ERR', 'INV_TXN_MANAGER_GRP',9);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 1
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM PA_ORGANIZATIONS_EXPEND_V POE
WHERE POE.ORGANIZATION_ID = MTI.PA_EXPENDITURE_ORG_ID
AND TRUNC(SYSDATE) BETWEEN POE.DATE_FROM
AND NVL(POE.DATE_TO, TRUNC(SYSDATE)));
inv_log_util.trace('After update on MTI for INV_PAORG_ERR','INV_TXN_MANAGER_GRP',9);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
SELECT PRIMARY_UOM_CODE,1,1,1,2
INTO l_priuom,
l_locctrl,
l_lotctrl,
l_serctrl,
l_resloc
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_itemid
AND ORGANIZATION_ID = l_orgid;
errupdate(l_rowid,null);
SELECT decode(P.STOCK_LOCATOR_CONTROL_CODE,4,
decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE,
S.LOCATOR_TYPE),P.STOCK_LOCATOR_CONTROL_CODE),
PRIMARY_UOM_CODE,
LOT_CONTROL_CODE,
SERIAL_NUMBER_CONTROL_CODE,
RESTRICT_LOCATORS_CODE,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
P.LOT_NUMBER_UNIQUENESS
INTO l_locctrl ,
l_priuom,
l_lotctrl,
l_serctrl,
l_resloc,
l_shlfcode,
l_shlfdays,
l_lotuniq
FROM MTL_PARAMETERS P,
MTL_SECONDARY_INVENTORIES S,
MTL_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = l_itemid
AND S.SECONDARY_INVENTORY_NAME = l_subinv
AND P.ORGANIZATION_ID = l_orgid
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = I.ORGANIZATION_ID;
errupdate(l_rowid,null);
SELECT Nvl(FND_PROFILE.Value('INV_CREATE_LOC_AT'), 2)
INTO g_create_loc_at
FROM DUAL;
errupdate(l_rowid,null);
SELECT DECODE(NVL(PROJECT_REFERENCE_ENABLED, 2),1,1,0)
INTO l_project_ref_enabled
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_orgid ;
errupdate(l_rowid);
update Locators in autonomous mode*/
IF (g_create_loc_at = 1) THEN
update_mil( p_userid
, p_loginid
, p_applid
, p_progid
, l_reqstid
, l_subinv
, l_default_locator_status
, l_orgid
, l_locid
, l_plocid);
/* Added one more parameter l_plocid for Bug# 7323175 to update_mil() for the physical locator */
ELSE
UPDATE MTL_ITEM_LOCATIONS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_APPLICATION_ID = p_applid,
PROGRAM_ID = p_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
SUBINVENTORY_CODE = l_subinv,
STATUS_ID = l_default_locator_status,
/* Start: Fix for Bug# 7323175: Stamping PHYSICAL_LOCATION_ID with the physical locator for both
physical and logical locators for Project enabled Orgs. For the case of Non project enabled orgs
p_plocid would be null */
PHYSICAL_LOCATION_ID = l_plocid
/* End: Fix for Bug# 7323175 */
WHERE ORGANIZATION_ID = l_orgid
/* Start: Fix for Bug# 7323175: Updating the physical locator as well in mil */
--AND INVENTORY_LOCATION_ID = l_locid
AND INVENTORY_LOCATION_ID IN (l_locid,l_plocid)
/* End: Fix for Bug# 7323175 */
AND SUBINVENTORY_CODE is NULL;
SELECT DECODE(UPPER(NVL(FLOW_SCHEDULE,'N')), 'Y', 1, 0), NVL(SCHEDULED_FLAG, 0)
INTO tev_flow_schedule, tev_scheduled_flag
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ROWID = l_rowid;
errupdate(l_rowid,null);
SELECT SUBINVENTORY_CODE
INTO l_xsubinv
FROM MTL_ITEM_SUB_DEFAULTS
WHERE INVENTORY_ITEM_ID = l_itemid
AND ORGANIZATION_ID = l_xorgid
AND DEFAULT_TYPE = 2;
errupdate(l_rowid,null);
errupdate(l_rowid,null);
SELECT decode(P.STOCK_LOCATOR_CONTROL_CODE,4,
decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE,
S.LOCATOR_TYPE),P.STOCK_LOCATOR_CONTROL_CODE),
LOT_CONTROL_CODE,
SERIAL_NUMBER_CONTROL_CODE,
RESTRICT_LOCATORS_CODE
INTO l_xlocctrl,
l_xlotctrl,
l_xserctrl,
l_xresloc
FROM MTL_PARAMETERS P,
MTL_SECONDARY_INVENTORIES S,
MTL_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = l_itemid
AND S.SECONDARY_INVENTORY_NAME = l_xsubinv
AND P.ORGANIZATION_ID = l_xorgid
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = I.ORGANIZATION_ID;
errupdate(l_rowid,null);
SELECT LOCATOR_ID
INTO l_xlocid
FROM MTL_ITEM_LOC_DEFAULTS MTLD,
MTL_ITEM_LOCATIONS MIL
WHERE MTLD.LOCATOR_ID=MIL.INVENTORY_LOCATION_ID
AND MTLD.ORGANIZATION_ID=MIL.ORGANIZATION_ID
AND MTLD.INVENTORY_ITEM_ID = l_itemid
AND MTLD.ORGANIZATION_ID = l_xorgid
AND MTLD.SUBINVENTORY_CODE = l_xsubinv
AND MTLD.DEFAULT_TYPE = 2
AND NVL(MIL.DISABLE_DATE,SYSDATE+1) > SYSDATE;
errupdate(l_rowid,null);
project and task from the requisition to a locator that is selected
from locator defaults. If the transfer bet orgs is 'direct', the shipment
transaction itself creates the recipt transaction and while doing so
it picks up the default locator from the locator defaults.
*/
IF (l_req_line_id IS NOT NULL) THEN
INV_PROJECT.Get_project_loc_for_prj_Req(
x_return_status,
l_xlocid,
l_xorgid,
l_req_line_id);
errupdate(l_rowid,null);
SELECT DECODE(NVL(PROJECT_REFERENCE_ENABLED, 2),1,1,0)
INTO l_project_ref_enabled
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_xorgid ;
update Locators in autonomous mode*/
IF (g_create_loc_at = 1) THEN
update_mil( p_userid
, p_loginid
, p_applid
, p_progid
, l_reqstid
, l_xsubinv
, l_default_locator_status
, l_xorgid
, l_xlocid
, l_xplocid);
/* Addded a parameter l_xplocid for Bug# 7323175 in update_mil */
ELSE
UPDATE MTL_ITEM_LOCATIONS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_APPLICATION_ID = p_applid,
PROGRAM_ID = p_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
SUBINVENTORY_CODE = l_xsubinv,
STATUS_ID = l_default_locator_status,
/* Start: Fix for Bug# 7323175: Stamping PHYSICAL_LOCATION_ID with the physical locator for both
physical and logical locators for Project enabled Orgs. For the case of Non project enabled orgs
l_xplocid would be null */
PHYSICAL_LOCATION_ID = l_xplocid
/* End: Fix for Bug# 7323175 */
WHERE ORGANIZATION_ID = l_xorgid
/* Start: Fix for Bug# 7323175: Updating the physical locator as well in mil */
--AND INVENTORY_LOCATION_ID = l_xlocid
AND INVENTORY_LOCATION_ID IN (l_xlocid,l_xplocid)
/* End: Fix for Bug# 7323175 */
AND SUBINVENTORY_CODE is NULL;
errupdate(l_rowid,null);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
SELECT fob_point
INTO l_fob_point
FROM mtl_interorg_parameters
WHERE from_organization_id = l_orgid
AND to_organization_id = l_xorgid;
errupdate(l_rowid,null);
/* Bug# 6271039, For average cost update and layer cost update, validate rows in
* MTI for material account, material overhead account, resource account,
* outside processing account, overhead account. */
IF ( l_acttype = 24 AND ((l_srctype = INV_Globals.G_SourceType_Inventory) OR (l_srctype = 15)) AND ( l_avg_cost_update = 2 ) )
OR (( l_acttype = 24 AND l_srctype = 14 )) THEN
/*-----------------------------------------------------------+
| Validate material account
+-----------------------------------------------------------*/
IF (l_validate_full) THEN --J-dev
FND_MESSAGE.set_name('INV','INV_MATERIAL_ACCOUNT');
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND MATERIAL_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.MATERIAL_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND MATERIAL_OVERHEAD_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.MATERIAL_OVERHEAD_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND RESOURCE_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.RESOURCE_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND OUTSIDE_PROCESSING_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.OUTSIDE_PROCESSING_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND OVERHEAD_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.OVERHEAD_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID
= (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
select nvl(lcm_enabled_flag,'N')
into l_lcm_enabled_org
from mtl_parameters
where organization_id = l_orgid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND MATERIAL_EXPENSE_ACCOUNT IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = MTI.MATERIAL_EXPENSE_ACCOUNT
AND GCC.CHART_OF_ACCOUNTS_ID =
(SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MTI.ORGANIZATION_ID)
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, mti.transaction_date - 1)) <= trunc(mti.transaction_date)
AND trunc(NVL(GCC.END_DATE_ACTIVE, mti.transaction_date + 1)) >= trunc(mti.transaction_date));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND MATERIAL_EXPENSE_ACCOUNT IS NOT NULL
AND TRANSACTION_QUANTITY <= 0 ;
/* for average cost update and layer cost update, validate rows in */
/* mtl_txn_cost_det_interface table , if R10 avg cost profile is set */
IF ( l_acttype = 24 AND ((l_srctype = INV_Globals.G_SourceType_Inventory)
OR (l_srctype = 15)) ) THEN
IF ( l_avg_cost_update = 2 ) THEN
/* should we check also if interface id is not null and
generate an id if it is null before calling validate */
CSTPACIT.cost_det_validate(l_intid,
l_orgid,
l_itemid,
l_new_avg_cst,
l_per_chng,
l_val_chng,
l_mat_accnt,
l_mat_ovhd_accnt,
l_res_accnt,
l_osp_accnt,
l_ovhd_accnt,
l_error_num,
l_error_code,
l_error_exp);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
SELECT PRIMARY_COST_METHOD
INTO l_primary_cost_method
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_orgid ;
IF ( l_avg_cost_update = 2 AND (l_primary_cost_method = 2 OR
l_primary_cost_method = 5 OR
l_primary_cost_method = 6 ) )
THEN
l_cst_temp := CSTPACMS.validate_move_snap_to_temp
(l_intid,
l_intid,
1, -- for inventory l_interface_table=1
l_priqty,
l_error_num,
l_error_code,
l_error_exp) ;
errupdate(l_rowid,null);
INV_GLOBALS.G_ACTION_COSTUPDATE
, INV_GLOBALS.G_ACTION_INV_LOT_SPLIT
, INV_GLOBALS.G_ACTION_INV_LOT_MERGE
, INV_GLOBALS.G_ACTION_INV_LOT_TRANSLATE)
) THEN
IF (l_lotctrl = 2 AND
(l_serctrl = 2 OR l_serctrl = 5 OR
(l_serctrl = 6 AND l_srctype = 2 AND l_acttype = 1) OR
(l_serctrl = 6 AND l_srctype = 16 AND l_acttype = 1) OR
(l_serctrl = 6 AND l_srctype = 8 AND l_acttype IN (3,21)) )
) THEN
-- lot and serial controlled item
-- validate lot quantities and mmtt quantity.
BEGIN
IF (l_debug = 1) THEN
inv_log_util.trace('validating lot/serial quantities','INV_TXN_MANAGER_GRP', 9);
SELECT NVL(SUM(ABS(NVL(mtli.transaction_quantity,0))), 0)
INTO l_lot_ser_qty
FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id =l_intid
AND ABS(nvl(mtli.primary_quantity, inv_convert.inv_um_convert
(l_itemid,5,mtli.transaction_quantity,l_trxuom,l_priuom,'','')))
= (SELECT SUM(get_serial_diff_wrp
(msni.fm_serial_number,nvl(msni.to_serial_number,msni.fm_serial_number)))
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id
= mtli.serial_transaction_temp_id);
errupdate(l_rowid,null);
SELECT SUM(get_serial_diff_wrp
(fm_serial_number,NVL(to_serial_number,fm_serial_number)))
INTO l_lot_ser_qty
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id =l_intid;
errupdate(l_rowid,null);
errupdate(l_rowid,null);
SELECT 1
into l_tnum
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_intid
AND ROWNUM < 2;
errupdate(l_rowid,null);
errupdate(l_rowid,null);
DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_intid;
errupdate(l_rowid,null);
SELECT 1
into l_tnum
FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_intid
AND ROWNUM < 2;
errupdate(l_rowid,null);
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_intid;
/*Bug#5125632. Calling 'update_status_id_in_mtli' to update the 'status_id' column
of the table, 'MTLI', for the row corrsponding to the currnet line */
IF (l_lotctrl = 2) THEN
update_status_id_in_mtli(l_intid
,l_orgid
,l_itemid);
SELECT DISTRIBUTION_ACCOUNT
INTO l_acct
FROM MTL_GENERIC_DISPOSITIONS
WHERE ORGANIZATION_ID = l_orgid
AND DISPOSITION_ID = l_trxsrc;
errupdate(l_rowid,null);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
errupdate(l_rowid,null);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
INVENTORY_ITEM_ID = l_itemid,
DISTRIBUTION_ACCOUNT_ID = l_acct,
LOCATOR_ID = l_locid,
TRANSACTION_SOURCE_ID = l_trxsrc,
ACCT_PERIOD_ID = l_prdid,
PRIMARY_QUANTITY = l_priqty,
TRANSFER_ORGANIZATION = l_xorgid,
TRANSFER_SUBINVENTORY = l_xsubinv,
TRANSFER_LOCATOR = l_xlocid,
TRANSACTION_INTERFACE_ID = l_intid,
END_ITEM_UNIT_NUMBER = l_unit_number,
OVERCOMPLETION_PRIMARY_QTY = l_overcomp_primary_qty
WHERE ROWID = l_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE rowid =l_rowid
AND PROCESS_FLAG = 1
AND LOCATOR_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MIL.SUBINVENTORY_CODE = MTI.SUBINVENTORY_CODE
AND MIL.INVENTORY_LOCATION_ID = MTI.LOCATOR_ID
AND TRUNC(MTI.TRANSACTION_DATE) <= NVL(MIL.DISABLE_DATE,
MTI.TRANSACTION_DATE + 1));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE rowid =l_rowid
AND PROCESS_FLAG = 1
AND LOCATOR_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_LOCATORS MSL,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 1
AND MSL.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSL.SUBINVENTORY_CODE = MTI.SUBINVENTORY_CODE
AND MSL.SECONDARY_LOCATOR = MTI.LOCATOR_ID
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.RESTRICT_LOCATORS_CODE = 2
AND ITM.ORGANIZATION_ID = MTI.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID IN (2,3,5)
AND TRANSFER_LOCATOR IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,3,
MTI.TRANSFER_ORGANIZATION,MTI.ORGANIZATION_ID)
AND MIL.SUBINVENTORY_CODE = MTI.TRANSFER_SUBINVENTORY
AND MIL.INVENTORY_LOCATION_ID = MTI.TRANSFER_LOCATOR
AND TRUNC(MTI.TRANSACTION_DATE) <= NVL(MIL.DISABLE_DATE,
MTI.TRANSACTION_DATE + 1));
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID=l_rowid
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID in (2,21,3,5)
AND TRANSFER_LOCATOR IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_LOCATORS MSL,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID, MTI.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 1
AND MSL.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID, MTI.TRANSFER_ORGANIZATION)
AND MSL.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSL.SUBINVENTORY_CODE = MTI.TRANSFER_SUBINVENTORY
AND MSL.SECONDARY_LOCATOR = MTI.TRANSFER_LOCATOR
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MTI.TRANSACTION_ACTION_ID,2,
MTI.ORGANIZATION_ID, MTI.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 2);
SELECT mp_from.process_enabled_flag, mp_to.process_enabled_flag
INTO l_process_enabled_flag_from, l_process_enabled_flag_to
FROM mtl_parameters mp_from, mtl_parameters mp_to
WHERE mp_from.organization_id = l_orgid
AND mp_to.organization_id = l_xorgid;
SELECT TO_NUMBER(src.org_information3) src_ou, TO_NUMBER(dest.org_information3) dest_ou
INTO l_from_ou, l_to_ou
FROM hr_organization_information src, hr_organization_information dest
WHERE src.organization_id = l_orgid
AND src.org_information_context = 'Accounting Information'
AND dest.organization_id = l_xorgid
AND dest.org_information_context = 'Accounting Information'
;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
transfer_price = l_transfer_price_priuom
WHERE ROWID = l_rowid;
errupdate(l_rowid,null);
inv_log_util.trace('update secondary quantity on line as sum of lot level secondary quantities', 'INV_TXN_MANAGER_GRP', 9);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET secondary_transaction_quantity = (SELECT SUM(SECONDARY_TRANSACTION_QUANTITY)
FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI
WHERE MTLI.TRANSACTION_INTERFACE_ID = p_line_Rec_Type.TRANSACTION_INTERFACE_ID)
WHERE ROWID = l_rowid;
SELECT cost_group_id, transfer_cost_group_id
INTO l_cost_group_id, l_xfer_cost_group_id
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ROWID = l_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID NOT IN (5,6,24,30,50,51,52) -- Added 7025628
AND COST_GROUP_ID IS NOT NULL
AND COST_GROUP_ID <> l_temp_cost_group_id;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID IN (2,3,21,5)
AND TRANSFER_COST_GROUP_ID IS NOT NULL
AND TRANSFER_COST_GROUP_ID <> l_temp_xfer_cost_group_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_userid,
LAST_UPDATE_LOGIN = p_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_rowid
AND PROCESS_FLAG = 1;
SELECT ACCT_PERIOD_ID
INTO acct_period_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = p_org_id
AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(NVL(p_trans_date,SYSDATE),p_org_id)
BETWEEN PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
ORDER BY PERIOD_START_DATE DESC, SCHEDULE_CLOSE_DATE ASC;
SELECT ACCT_PERIOD_ID
INTO acct_period_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = p_org_id
AND TRUNC(SCHEDULE_CLOSE_DATE) >=
INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(NVL(p_trans_date,SYSDATE),p_org_id)
AND TRUNC(PERIOD_START_DATE) <=
INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(NVL(p_trans_date,SYSDATE),p_org_id);
* tmpinsert()
*
******************************************************************/
FUNCTION tmpinsert(p_header_id IN NUMBER,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full )
RETURN BOOLEAN
IS
l_lt_flow_schedule NUMBER;
inv_log_util.TRACE ( 'In tmpinsert '
, 'INV_TXN_MANAGER_GRP'
, '9'
);
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS (SELECT transaction_interface_id
FROM mtl_transactions_interface
WHERE transaction_header_id = p_header_id
AND process_flag = 1
AND transaction_type_id NOT IN
(inv_globals.g_type_inv_lot_split
, inv_globals.g_type_inv_lot_merge
, inv_globals.g_type_inv_lot_translate));
inv_log_util.TRACE ( 'Returning from tmpinsert '
, 'INV_TXN_MANAGER_GRP'
, '9'
);
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
INVENTORY_ITEM_ID,
REVISION,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_REFERENCE,
REASON_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_COST,
DISTRIBUTION_ACCOUNT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSPORTATION_COST,
TRANSFER_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
DEMAND_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
CUSTOMER_SHIP_ID,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PICKING_LINE_ID,
REQUIRED_FLAG,
NEGATIVE_REQ_FLAG,
REPETITIVE_LINE_ID,
PRIMARY_SWITCH,
OPERATION_SEQ_NUM,
SETUP_TEARDOWN_CODE,
SCHEDULE_UPDATE_CODE,
DEPARTMENT_ID,
EMPLOYEE_CODE,
SCHEDULE_ID,
WIP_ENTITY_TYPE,
ENCUMBRANCE_AMOUNT,
ENCUMBRANCE_ACCOUNT,
USSGL_TRANSACTION_CODE,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
SHIP_TO_LOCATION,
COMPLETION_TRANSACTION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
POSTING_FLAG,
FINAL_COMPLETION_FLAG,
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
END_ITEM_UNIT_NUMBER,
ORG_COST_GROUP_ID, /* PCST (Periodic Cost Update) */
COST_TYPE_ID, /* PCST */
MOVE_ORDER_LINE_ID,
LPN_ID,
CONTENT_LPN_ID,
TRANSFER_LPN_ID,
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,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRANSFER_COST_GROUP_ID,
TRANSACTION_MODE,
-- start of fix for eam
-- added following 4 columns
REBUILD_ITEM_ID,
REBUILD_ACTIVITY_ID,
REBUILD_SERIAL_NUMBER,
rebuild_job_name,
kanban_card_id ,-- end of fix for eam
class_code,--J dev (accounting_class in MTI)
scheduled_flag,--J dev
schedule_number,--J dev
routing_revision_date,--J dev
move_transaction_id,--J dev
wip_supply_type,--J dev
build_sequence,--J dev
bom_revision,--J dev
routing_revision,--J dev
bom_revision_date,--J dev
alternate_bom_designator,--J dev
alternate_routing_designator, -- end of fix for eam
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV fabdi start
SECONDARY_UOM_CODE, -- INVCONV fabdi end
RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
)
SELECT
TRANSACTION_HEADER_ID,
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
'Y',
SYSDATE,
CREATED_BY,
SYSDATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
SYSDATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
INVENTORY_ITEM_ID,
REVISION,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_REFERENCE,
REASON_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_COST,
DISTRIBUTION_ACCOUNT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_LOCATOR,
SHIPMENT_NUMBER,
TRANSPORTATION_COST,
TRANSFER_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
DEMAND_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
CUSTOMER_SHIP_ID,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PICKING_LINE_ID,
REQUIRED_FLAG,
NEGATIVE_REQ_FLAG,
REPETITIVE_LINE_ID,
PRIMARY_SWITCH,
OPERATION_SEQ_NUM,
SETUP_TEARDOWN_CODE,
SCHEDULE_UPDATE_CODE,
DEPARTMENT_ID,
EMPLOYEE_CODE,
SCHEDULE_ID,
WIP_ENTITY_TYPE,
ENCUMBRANCE_AMOUNT,
ENCUMBRANCE_ACCOUNT,
USSGL_TRANSACTION_CODE,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
SHIP_TO_LOCATION_ID,
Nvl(completion_transaction_id,DECODE(TRANSACTION_ACTION_ID,31,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,32,MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,NULL)),--J-dev as wip will pass this to us. For I we need the decode.
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
'N',
NVL(FINAL_COMPLETION_FLAG,Decode(l_patchset_j,1,FINAL_COMPLETION_FLAG,
'N')),
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
END_ITEM_UNIT_NUMBER,
ORG_COST_GROUP_ID, /* PCST */
COST_TYPE_ID,
DECODE(TRANSACTION_SOURCE_TYPE_ID,4,SOURCE_LINE_ID,null), /* PCST */
LPN_ID,
CONTENT_LPN_ID,
transfer_lpn_id,
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,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRANSFER_COST_GROUP_ID,
Decode(p_validation_level,fnd_api.g_valid_level_none,transaction_mode,INV_TXN_MANAGER_GRP.proc_mode_mti),
-- start of fix for eam
-- added following 4 columns
REBUILD_ITEM_ID,
REBUILD_ACTIVITY_ID,
REBUILD_SERIAL_NUMBER,
rebuild_job_name,
-- end of fix for eam
kanban_card_id,
accounting_class,--J dev (class_code in mmtt)
scheduled_flag,--J dev
schedule_number,--J dev
routing_revision_date,--J dev
move_transaction_id,--J dev
wip_supply_type , --J dev
build_sequence,--J dev
bom_revision,--J dev
routing_revision,--J dev
bom_revision_date,--J dev
alternate_bom_designator,--J dev
alternate_routing_designator, --J-dev
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV start fabdi
SECONDARY_UOM_CODE, -- INVCONV fabdi end
RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
FROM MTL_TRANSACTIONS_INTERFACE
--WHERE ROWID = p_rowid--J-dev
WHERE transaction_header_id = p_header_id
AND PROCESS_FLAG = 1
AND transaction_type_id NOT IN /*OSFM Support for Lot Serialized Items*/
(inv_globals.g_type_inv_lot_split
, inv_globals.g_type_inv_lot_merge
, inv_globals.g_type_inv_lot_translate
);
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
INVENTORY_ITEM_ID,
REVISION,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_REFERENCE,
REASON_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_COST,
DISTRIBUTION_ACCOUNT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSPORTATION_COST,
TRANSFER_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
DEMAND_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
DEMAND_CLASS,
CUSTOMER_SHIP_ID,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PICKING_LINE_ID,
REQUIRED_FLAG,
NEGATIVE_REQ_FLAG,
REPETITIVE_LINE_ID,
PRIMARY_SWITCH,
OPERATION_SEQ_NUM,
SETUP_TEARDOWN_CODE,
SCHEDULE_UPDATE_CODE,
DEPARTMENT_ID,
EMPLOYEE_CODE,
SCHEDULE_ID,
WIP_ENTITY_TYPE,
ENCUMBRANCE_AMOUNT,
ENCUMBRANCE_ACCOUNT,
USSGL_TRANSACTION_CODE,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
SHIP_TO_LOCATION,
COMPLETION_TRANSACTION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
POSTING_FLAG,
FINAL_COMPLETION_FLAG,
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
END_ITEM_UNIT_NUMBER,
COMMON_BOM_SEQ_ID,
COMMON_ROUTING_SEQ_ID,
ORG_COST_GROUP_ID, /* PCST */
COST_TYPE_ID,
LPN_ID,
CONTENT_LPN_ID,
transfer_lpn_id,
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,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRANSFER_COST_GROUP_ID,
TRANSACTION_MODE,
-- start of fix for eam
-- added following 4 columns
REBUILD_ITEM_ID,
REBUILD_ACTIVITY_ID,
REBUILD_SERIAL_NUMBER,
rebuild_job_name,
-- end of fix for eam
kanban_card_id,
class_code,--J dev (class_code in mmtt)
scheduled_flag,--J dev
schedule_number,--J dev
routing_revision_date,--J dev
move_transaction_id,--J dev
wip_supply_type,
build_sequence,--J dev
bom_revision,--J dev
routing_revision,--J dev
bom_revision_date,--J dev
alternate_bom_designator,--J dev
alternate_routing_designator , --J dev
SECONDARY_TRANSACTION_QUANTITY , -- INVCONV fabdi
SECONDARY_UOM_CODE, -- INVCONV fabdi end
RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
)
SELECT
MTI.TRANSACTION_HEADER_ID,
MTI.TRANSACTION_INTERFACE_ID,
MTI.SOURCE_CODE,
MTI.SOURCE_LINE_ID,
'Y',
SYSDATE,
MTI.CREATED_BY,
SYSDATE,
MTI.LAST_UPDATED_BY,
MTI.LAST_UPDATE_LOGIN,
MTI.PROGRAM_ID,
SYSDATE,
MTI.PROGRAM_APPLICATION_ID,
MTI.REQUEST_ID,
MTI.ORGANIZATION_ID,
MTI.SUBINVENTORY_CODE,
MTI.LOCATOR_ID,
MTI.INVENTORY_ITEM_ID,
MTI.REVISION,
MTI.TRANSACTION_TYPE_ID,
MTI.TRANSACTION_ACTION_ID,
MTI.TRANSACTION_SOURCE_TYPE_ID,
MTI.TRANSACTION_SOURCE_ID,
MTI.TRANSACTION_SOURCE_NAME,
MTI.TRANSACTION_REFERENCE,
MTI.REASON_ID,
MTI.TRANSACTION_DATE,
MTI.ACCT_PERIOD_ID,
MTI.TRANSACTION_QUANTITY,
MTI.TRANSACTION_UOM,
MTI.PRIMARY_QUANTITY,
MTI.TRANSACTION_COST,
MTI.DISTRIBUTION_ACCOUNT_ID,
MTI.TRANSFER_SUBINVENTORY,
MTI.TRANSFER_ORGANIZATION,
MTI.TRANSFER_LOCATOR,
MTI.SHIPMENT_NUMBER,
MTI.TRANSPORTATION_COST,
MTI.TRANSFER_COST,
MTI.TRANSPORTATION_ACCOUNT,
MTI.FREIGHT_CODE,
MTI.CONTAINERS,
MTI.WAYBILL_AIRBILL,
MTI.EXPECTED_ARRIVAL_DATE,
MTI.CURRENCY_CODE,
MTI.CURRENCY_CONVERSION_DATE,
MTI.CURRENCY_CONVERSION_TYPE,
MTI.CURRENCY_CONVERSION_RATE,
MTI.NEW_AVERAGE_COST,
MTI.VALUE_CHANGE,
MTI.PERCENTAGE_CHANGE,
MTI.DEMAND_ID,
MTI.DEMAND_SOURCE_HEADER_ID,
MTI.DEMAND_SOURCE_LINE,
MTI.DEMAND_SOURCE_DELIVERY,
MTI.DEMAND_CLASS,
MTI.CUSTOMER_SHIP_ID,
MTI.TRX_SOURCE_DELIVERY_ID,
MTI.TRX_SOURCE_LINE_ID,
MTI.PICKING_LINE_ID,
MTI.REQUIRED_FLAG,
MTI.NEGATIVE_REQ_FLAG,
MTI.REPETITIVE_LINE_ID,
MTI.PRIMARY_SWITCH,
MTI.OPERATION_SEQ_NUM,
MTI.SETUP_TEARDOWN_CODE,
MTI.SCHEDULE_UPDATE_CODE,
MTI.DEPARTMENT_ID,
MTI.EMPLOYEE_CODE,
MTI.SCHEDULE_ID,
MTI.WIP_ENTITY_TYPE,
MTI.ENCUMBRANCE_AMOUNT,
MTI.ENCUMBRANCE_ACCOUNT,
MTI.USSGL_TRANSACTION_CODE,
MTI.SHIPPABLE_FLAG,
MTI.REQUISITION_LINE_ID,
MTI.REQUISITION_DISTRIBUTION_ID,
MTI.SHIP_TO_LOCATION_ID,
NVL(mti.completion_transaction_id,MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL),
--J-dev as wip may pass this to us in J. NVL for I
MTI.ATTRIBUTE_CATEGORY,
MTI.ATTRIBUTE1,
MTI.ATTRIBUTE2,
MTI.ATTRIBUTE3,
MTI.ATTRIBUTE4,
MTI.ATTRIBUTE5,
MTI.ATTRIBUTE6,
MTI.ATTRIBUTE7,
MTI.ATTRIBUTE8,
MTI.ATTRIBUTE9,
MTI.ATTRIBUTE10,
MTI.ATTRIBUTE11,
MTI.ATTRIBUTE12,
MTI.ATTRIBUTE13,
MTI.ATTRIBUTE14,
MTI.ATTRIBUTE15,
MTI.MOVEMENT_ID,
MTI.SOURCE_PROJECT_ID,
MTI.SOURCE_TASK_ID,
MTI.EXPENDITURE_TYPE,
MTI.PA_EXPENDITURE_ORG_ID,
MTI.PROJECT_ID,
MTI.TASK_ID,
MTI.TO_PROJECT_ID,
MTI.TO_TASK_ID,
'Y',
NVL(MTI.FINAL_COMPLETION_FLAG,Decode(l_patchset_j,1,MTI.FINAL_COMPLETION_FLAG,'N')),
MTI.TRANSFER_PERCENTAGE,
MTI.MATERIAL_ACCOUNT,
MTI.MATERIAL_OVERHEAD_ACCOUNT,
MTI.RESOURCE_ACCOUNT,
MTI.OUTSIDE_PROCESSING_ACCOUNT,
MTI.OVERHEAD_ACCOUNT,
MTI.COST_GROUP_ID,
MTI.FLOW_SCHEDULE,
MTI.QA_COLLECTION_ID,
MTI.OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
MTI.OVERCOMPLETION_PRIMARY_QTY,
MTI.OVERCOMPLETION_TRANSACTION_ID,
MTI.END_ITEM_UNIT_NUMBER,
BOM.COMMON_BILL_SEQUENCE_ID,
BOR.COMMON_ROUTING_SEQUENCE_ID,
ORG_COST_GROUP_ID, /* PCST */
COST_TYPE_ID, /* PCST */
MTI.LPN_ID,
MTI.CONTENT_LPN_ID,
MTI.TRANSFER_LPN_ID,
MTI.ORGANIZATION_TYPE,
MTI.TRANSFER_ORGANIZATION_TYPE,
MTI.OWNING_ORGANIZATION_ID,
MTI.OWNING_TP_TYPE,
MTI.XFR_OWNING_ORGANIZATION_ID,
MTI.TRANSFER_OWNING_TP_TYPE,
MTI.PLANNING_ORGANIZATION_ID,
MTI.PLANNING_TP_TYPE,
MTI.XFR_PLANNING_ORGANIZATION_ID,
MTI.TRANSFER_PLANNING_TP_TYPE,
MTI.TRANSACTION_BATCH_ID,
MTI.TRANSACTION_BATCH_SEQ,
MTI.TRANSFER_COST_GROUP_ID,
Decode(p_validation_level,fnd_api.g_valid_level_none,mti.transaction_mode,INV_TXN_MANAGER_GRP.proc_mode_mti),
-- start of fix for eam
-- added following 4 columns
MTI.REBUILD_ITEM_ID,
MTI.REBUILD_ACTIVITY_ID,
MTI.REBUILD_SERIAL_NUMBER,
MTI.rebuild_job_name,
-- end of fix for eam
mti.kanban_card_id,
mti.accounting_class,--J dev (class_code in mmtt)
mti.scheduled_flag,--J dev
mti.schedule_number,--J dev
mti.routing_revision_date,--J dev
mti.move_transaction_id,--J dev
mti.wip_supply_type,--J-dev
mti.build_sequence,--J dev
mti.bom_revision,--J dev
mti.routing_revision,--J dev
mti.bom_revision_date,--J dev
mti.alternate_bom_designator,--J dev
mti.alternate_routing_designator, --J dev
mti.SECONDARY_TRANSACTION_QUANTITY , -- INVCONV fabdi
mti.SECONDARY_UOM_CODE, -- INVCONV fabdi
mti.RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
FROM MTL_TRANSACTIONS_INTERFACE MTI,
BOM_BILL_OF_MATERIALS BOM,
BOM_OPERATIONAL_ROUTINGS BOR
WHERE TRANSACTION_HEADER_ID = p_header_id
/*WHERE MTI.ROWID = p_rowid*/--J-dev
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID IN (30,31, 32) /* CFM Scrap Transactions */
AND BOM.ASSEMBLY_ITEM_ID(+) = MTI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID(+) = MTI.ORGANIZATION_ID
AND ((BOM.ALTERNATE_BOM_DESIGNATOR is null AND
MTI.ALTERNATE_BOM_DESIGNATOR is null) OR
(BOM.ALTERNATE_BOM_DESIGNATOR = MTI.ALTERNATE_BOM_DESIGNATOR))
AND BOR.ASSEMBLY_ITEM_ID(+) = MTI.INVENTORY_ITEM_ID
AND BOR.ORGANIZATION_ID(+) = MTI.ORGANIZATION_ID
AND ((BOR.ALTERNATE_ROUTING_DESIGNATOR is null AND
MTI.ALTERNATE_ROUTING_DESIGNATOR is null) OR
(BOR.ALTERNATE_ROUTING_DESIGNATOR = MTI.ALTERNATE_ROUTING_DESIGNATOR));
INSERT INTO mtl_material_transactions_temp
(
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
INVENTORY_ITEM_ID,
REVISION,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_REFERENCE,
REASON_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_COST,
DISTRIBUTION_ACCOUNT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSPORTATION_COST,
TRANSFER_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
DEMAND_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
DEMAND_CLASS,
CUSTOMER_SHIP_ID,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PICKING_LINE_ID,
REQUIRED_FLAG,
NEGATIVE_REQ_FLAG,
REPETITIVE_LINE_ID,
PRIMARY_SWITCH,
OPERATION_SEQ_NUM,
SETUP_TEARDOWN_CODE,
SCHEDULE_UPDATE_CODE,
DEPARTMENT_ID,
EMPLOYEE_CODE,
SCHEDULE_ID,
WIP_ENTITY_TYPE,
ENCUMBRANCE_AMOUNT,
ENCUMBRANCE_ACCOUNT,
USSGL_TRANSACTION_CODE,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
SHIP_TO_LOCATION,
COMPLETION_TRANSACTION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
POSTING_FLAG,
FINAL_COMPLETION_FLAG,
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY, -- Overcompletion Transactions --
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
END_ITEM_UNIT_NUMBER,
ORG_COST_GROUP_ID, -- PCST --
COST_TYPE_ID, /* PCST */
LPN_ID,
CONTENT_LPN_ID,
transfer_lpn_id,
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,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRANSFER_COST_GROUP_ID,
TRANSACTION_MODE,
-- start of fix for eam
-- added following 4 columns
REBUILD_ITEM_ID,
REBUILD_ACTIVITY_ID,
REBUILD_SERIAL_NUMBER,
rebuild_job_name,
kanban_card_id,
class_code,--J dev (class_code in mmtt)
scheduled_flag,--J dev
schedule_number,--J dev
routing_revision_date,--J dev
move_transaction_id,--J dev
wip_supply_type,
build_sequence,--J dev
bom_revision,--J dev
routing_revision,--J dev
bom_revision_date,--J dev
alternate_bom_designator,--J dev
alternate_routing_designator , -- end of fix for eam
SECONDARY_TRANSACTION_QUANTITY , -- INVCONV fabdi start
SECONDARY_UOM_CODE, -- INVCONV fabdi end
RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
)
SELECT
MTI.TRANSACTION_HEADER_ID,
MTI.TRANSACTION_INTERFACE_ID,
MTI.SOURCE_CODE,
MTI.SOURCE_LINE_ID,
'Y',
SYSDATE,
MTI.CREATED_BY,
SYSDATE,
MTI.LAST_UPDATED_BY,
MTI.LAST_UPDATE_LOGIN,
MTI.PROGRAM_ID,
SYSDATE,
MTI.PROGRAM_APPLICATION_ID,
MTI.REQUEST_ID,
MTI.ORGANIZATION_ID,
MTI.SUBINVENTORY_CODE,
MTI.LOCATOR_ID,
MTI.INVENTORY_ITEM_ID,
MTI.REVISION,
MTI.TRANSACTION_TYPE_ID,
MTI.TRANSACTION_ACTION_ID,
MTI.TRANSACTION_SOURCE_TYPE_ID,
MTI.TRANSACTION_SOURCE_ID,
MTI.TRANSACTION_SOURCE_NAME,
MTI.TRANSACTION_REFERENCE,
MTI.REASON_ID,
MTI.TRANSACTION_DATE,
MTI.ACCT_PERIOD_ID,
MTI.TRANSACTION_QUANTITY,
MTI.TRANSACTION_UOM,
MTI.PRIMARY_QUANTITY,
MTI.TRANSACTION_COST,
MTI.DISTRIBUTION_ACCOUNT_ID,
MTI.TRANSFER_SUBINVENTORY,
MTI.TRANSFER_ORGANIZATION,
MTI.TRANSFER_LOCATOR,
MTI.SHIPMENT_NUMBER,
MTI.TRANSPORTATION_COST,
MTI.TRANSFER_COST,
MTI.TRANSPORTATION_ACCOUNT,
MTI.FREIGHT_CODE,
MTI.CONTAINERS,
MTI.WAYBILL_AIRBILL,
MTI.EXPECTED_ARRIVAL_DATE,
MTI.CURRENCY_CODE,
MTI.CURRENCY_CONVERSION_DATE,
MTI.CURRENCY_CONVERSION_TYPE,
MTI.CURRENCY_CONVERSION_RATE,
MTI.NEW_AVERAGE_COST,
MTI.VALUE_CHANGE,
MTI.PERCENTAGE_CHANGE,
MTI.DEMAND_ID,
MTI.DEMAND_SOURCE_HEADER_ID,
MTI.DEMAND_SOURCE_LINE,
MTI.DEMAND_SOURCE_DELIVERY,
MTI.DEMAND_CLASS,
MTI.CUSTOMER_SHIP_ID,
MTI.TRX_SOURCE_DELIVERY_ID,
MTI.TRX_SOURCE_LINE_ID,
MTI.PICKING_LINE_ID,
MTI.REQUIRED_FLAG,
MTI.NEGATIVE_REQ_FLAG,
MTI.REPETITIVE_LINE_ID,
MTI.PRIMARY_SWITCH,
MTI.OPERATION_SEQ_NUM,
MTI.SETUP_TEARDOWN_CODE,
MTI.SCHEDULE_UPDATE_CODE,
MTI.DEPARTMENT_ID,
MTI.EMPLOYEE_CODE,
MTI.SCHEDULE_ID,
MTI.WIP_ENTITY_TYPE,
MTI.ENCUMBRANCE_AMOUNT,
MTI.ENCUMBRANCE_ACCOUNT,
MTI.USSGL_TRANSACTION_CODE,
MTI.SHIPPABLE_FLAG,
MTI.REQUISITION_LINE_ID,
MTI.REQUISITION_DISTRIBUTION_ID,
MTI.SHIP_TO_LOCATION_ID,
MTI.COMPLETION_TRANSACTION_ID,
MTI.ATTRIBUTE_CATEGORY,
MTI.ATTRIBUTE1,
MTI.ATTRIBUTE2,
MTI.ATTRIBUTE3,
MTI.ATTRIBUTE4,
MTI.ATTRIBUTE5,
MTI.ATTRIBUTE6,
MTI.ATTRIBUTE7,
MTI.ATTRIBUTE8,
MTI.ATTRIBUTE9,
MTI.ATTRIBUTE10,
MTI.ATTRIBUTE11,
MTI.ATTRIBUTE12,
MTI.ATTRIBUTE13,
MTI.ATTRIBUTE14,
MTI.ATTRIBUTE15,
MTI.MOVEMENT_ID,
MTI.SOURCE_PROJECT_ID,
MTI.SOURCE_TASK_ID,
MTI.EXPENDITURE_TYPE,
MTI.PA_EXPENDITURE_ORG_ID,
MTI.PROJECT_ID,
MTI.TASK_ID,
MTI.TO_PROJECT_ID,
MTI.TO_TASK_ID,
'Y',
NVL(MTI.FINAL_COMPLETION_FLAG,Decode(l_patchset_j,1,MTI.FINAL_COMPLETION_FLAG,'N')),
MTI.TRANSFER_PERCENTAGE,
MTI.MATERIAL_ACCOUNT,
MTI.MATERIAL_OVERHEAD_ACCOUNT,
MTI.RESOURCE_ACCOUNT,
MTI.OUTSIDE_PROCESSING_ACCOUNT,
MTI.OVERHEAD_ACCOUNT,
MTI.COST_GROUP_ID,
MTI.FLOW_SCHEDULE,
MTI.QA_COLLECTION_ID,
MTI.OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
MTI.OVERCOMPLETION_PRIMARY_QTY,
MTI.OVERCOMPLETION_TRANSACTION_ID,
MTI.END_ITEM_UNIT_NUMBER,
MTI.ORG_COST_GROUP_ID, /* PCST */
MTI.COST_TYPE_ID, /* PCST */
MTI.LPN_ID,
MTI.CONTENT_LPN_ID,
MTI.TRANSFER_LPN_ID , /* PCST */
mti.organization_type,
mti.transfer_organization_type,
mti.owning_organization_id,
mti.owning_tp_type,
mti.xfr_owning_organization_id,
mti.transfer_owning_tp_type,
mti.planning_organization_id,
mti.planning_tp_type,
mti.xfr_planning_organization_id,
mti.TRANSFER_PLANNING_TP_TYPE,
MTI.TRANSACTION_BATCH_ID,
MTI.TRANSACTION_BATCH_SEQ,
MTI.TRANSFER_COST_GROUP_ID,
Decode(p_validation_level,fnd_api.g_valid_level_none,mti.transaction_mode,INV_TXN_MANAGER_GRP.proc_mode_mti),
-- start of fix for eam
-- added following 4 columns
MTI.REBUILD_ITEM_ID,
MTI.REBUILD_ACTIVITY_ID,
MTI.REBUILD_SERIAL_NUMBER,
MTI.rebuild_job_name,
-- end of fix for eam
mti.kanban_card_id,
mti.accounting_class,--J dev (class_code in mmtt)
mti.scheduled_flag,--J dev
mti.schedule_number,--J dev
mti.routing_revision_date,--J dev
mti.move_transaction_id,--J de
mti.wip_supply_type,
mti.build_sequence,--J dev
mti.bom_revision,--J dev
mti.routing_revision,--J dev
mti.bom_revision_date,--J dev
mti.alternate_bom_designator,--J dev
mti.alternate_routing_designator ,
mti.SECONDARY_TRANSACTION_QUANTITY , -- INVCONV fabdi start
mti.SECONDARY_UOM_CODE, -- INVCONV fabdi end
mti.RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
and MTI.PROCESS_FLAG = 1
and MTI.TRANSACTION_ACTION_ID IN (1, 27, 33, 34) ;
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
INVENTORY_ITEM_ID,
REVISION,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_REFERENCE,
REASON_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_COST,
DISTRIBUTION_ACCOUNT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSPORTATION_COST,
TRANSFER_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
DEMAND_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
DEMAND_CLASS,
CUSTOMER_SHIP_ID,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PICKING_LINE_ID,
REQUIRED_FLAG,
NEGATIVE_REQ_FLAG,
REPETITIVE_LINE_ID,
PRIMARY_SWITCH,
OPERATION_SEQ_NUM,
SETUP_TEARDOWN_CODE,
SCHEDULE_UPDATE_CODE,
DEPARTMENT_ID,
EMPLOYEE_CODE,
SCHEDULE_ID,
WIP_ENTITY_TYPE,
ENCUMBRANCE_AMOUNT,
ENCUMBRANCE_ACCOUNT,
USSGL_TRANSACTION_CODE,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
SHIP_TO_LOCATION,
COMPLETION_TRANSACTION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
POSTING_FLAG,
FINAL_COMPLETION_FLAG,
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY, -- Overcompletion Transactions --
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
END_ITEM_UNIT_NUMBER,
ORG_COST_GROUP_ID, -- PCST --
COST_TYPE_ID, /* PCST */
LPN_ID,
CONTENT_LPN_ID,
transfer_lpn_id,
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,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRANSFER_COST_GROUP_ID,
TRANSACTION_MODE,
-- start of fix for eam
-- added following 4 columns
REBUILD_ITEM_ID,
REBUILD_ACTIVITY_ID,
REBUILD_SERIAL_NUMBER,
rebuild_job_name,
kanban_card_id,
build_sequence,--J dev
bom_revision,--J dev
routing_revision,--J dev
bom_revision_date,--J dev
alternate_bom_designator,--J dev
alternate_routing_designator,-- end of fix for eam
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV fabdi
SECONDARY_UOM_CODE, -- INVCONV fabdi end
RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
)
SELECT
MTI.TRANSACTION_HEADER_ID,
MTI.TRANSACTION_INTERFACE_ID,
MTI.SOURCE_CODE,
MTI.SOURCE_LINE_ID,
'Y',
SYSDATE,
MTI.CREATED_BY,
SYSDATE,
MTI.LAST_UPDATED_BY,
MTI.LAST_UPDATE_LOGIN,
MTI.PROGRAM_ID,
SYSDATE,
MTI.PROGRAM_APPLICATION_ID,
MTI.REQUEST_ID,
MTI.ORGANIZATION_ID,
MTI.SUBINVENTORY_CODE,
MTI.LOCATOR_ID,
MTI.INVENTORY_ITEM_ID,
MTI.REVISION,
MTI.TRANSACTION_TYPE_ID,
MTI.TRANSACTION_ACTION_ID,
MTI.TRANSACTION_SOURCE_TYPE_ID,
MTI.TRANSACTION_SOURCE_ID,
MTI.TRANSACTION_SOURCE_NAME,
MTI.TRANSACTION_REFERENCE,
MTI.REASON_ID,
MTI.TRANSACTION_DATE,
MTI.ACCT_PERIOD_ID,
MTI.TRANSACTION_QUANTITY,
MTI.TRANSACTION_UOM,
MTI.PRIMARY_QUANTITY,
MTI.TRANSACTION_COST,
MTI.DISTRIBUTION_ACCOUNT_ID,
MTI.TRANSFER_SUBINVENTORY,
MTI.TRANSFER_ORGANIZATION,
MTI.TRANSFER_LOCATOR,
MTI.SHIPMENT_NUMBER,
MTI.TRANSPORTATION_COST,
MTI.TRANSFER_COST,
MTI.TRANSPORTATION_ACCOUNT,
MTI.FREIGHT_CODE,
MTI.CONTAINERS,
MTI.WAYBILL_AIRBILL,
MTI.EXPECTED_ARRIVAL_DATE,
MTI.CURRENCY_CODE,
MTI.CURRENCY_CONVERSION_DATE,
MTI.CURRENCY_CONVERSION_TYPE,
MTI.CURRENCY_CONVERSION_RATE,
MTI.NEW_AVERAGE_COST,
MTI.VALUE_CHANGE,
MTI.PERCENTAGE_CHANGE,
MTI.DEMAND_ID,
MTI.DEMAND_SOURCE_HEADER_ID,
MTI.DEMAND_SOURCE_LINE,
MTI.DEMAND_SOURCE_DELIVERY,
MTI.DEMAND_CLASS,
MTI.CUSTOMER_SHIP_ID,
MTI.TRX_SOURCE_DELIVERY_ID,
MTI.TRX_SOURCE_LINE_ID,
MTI.PICKING_LINE_ID,
MTI.REQUIRED_FLAG,
MTI.NEGATIVE_REQ_FLAG,
MTI.REPETITIVE_LINE_ID,
MTI.PRIMARY_SWITCH,
MTI.OPERATION_SEQ_NUM,
MTI.SETUP_TEARDOWN_CODE,
MTI.SCHEDULE_UPDATE_CODE,
MTI.DEPARTMENT_ID,
MTI.EMPLOYEE_CODE,
MTI.SCHEDULE_ID,
MTI.WIP_ENTITY_TYPE,
MTI.ENCUMBRANCE_AMOUNT,
MTI.ENCUMBRANCE_ACCOUNT,
MTI.USSGL_TRANSACTION_CODE,
MTI.SHIPPABLE_FLAG,
MTI.REQUISITION_LINE_ID,
MTI.REQUISITION_DISTRIBUTION_ID,
MTI.SHIP_TO_LOCATION_ID,
MMTT.COMPLETION_TRANSACTION_ID,
MTI.ATTRIBUTE_CATEGORY,
MTI.ATTRIBUTE1,
MTI.ATTRIBUTE2,
MTI.ATTRIBUTE3,
MTI.ATTRIBUTE4,
MTI.ATTRIBUTE5,
MTI.ATTRIBUTE6,
MTI.ATTRIBUTE7,
MTI.ATTRIBUTE8,
MTI.ATTRIBUTE9,
MTI.ATTRIBUTE10,
MTI.ATTRIBUTE11,
MTI.ATTRIBUTE12,
MTI.ATTRIBUTE13,
MTI.ATTRIBUTE14,
MTI.ATTRIBUTE15,
MTI.MOVEMENT_ID,
MTI.SOURCE_PROJECT_ID,
MTI.SOURCE_TASK_ID,
MTI.EXPENDITURE_TYPE,
MTI.PA_EXPENDITURE_ORG_ID,
MTI.PROJECT_ID,
MTI.TASK_ID,
MTI.TO_PROJECT_ID,
MTI.TO_TASK_ID,
'Y',
NVL(MTI.FINAL_COMPLETION_FLAG,
Decode(l_patchset_j,1,MTI.FINAL_COMPLETION_FLAG,'N')),
MTI.TRANSFER_PERCENTAGE,
MTI.MATERIAL_ACCOUNT,
MTI.MATERIAL_OVERHEAD_ACCOUNT,
MTI.RESOURCE_ACCOUNT,
MTI.OUTSIDE_PROCESSING_ACCOUNT,
MTI.OVERHEAD_ACCOUNT,
MTI.COST_GROUP_ID,
MTI.FLOW_SCHEDULE,
MTI.QA_COLLECTION_ID,
MTI.OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
MTI.OVERCOMPLETION_PRIMARY_QTY,
MTI.OVERCOMPLETION_TRANSACTION_ID,
MTI.END_ITEM_UNIT_NUMBER,
MTI.ORG_COST_GROUP_ID, /* PCST */
MTI.COST_TYPE_ID, /* PCST */
MTI.LPN_ID,
MTI.CONTENT_LPN_ID,
MTI.TRANSFER_LPN_ID , /* PCST */
mti.organization_type,
mti.transfer_organization_type,
mti.owning_organization_id,
mti.owning_tp_type,
mti.xfr_owning_organization_id,
mti.transfer_owning_tp_type,
mti.planning_organization_id,
mti.planning_tp_type,
mti.xfr_planning_organization_id,
mti.TRANSFER_PLANNING_TP_TYPE,
MTI.TRANSACTION_BATCH_ID,
MTI.TRANSACTION_BATCH_SEQ,
MTI.TRANSFER_COST_GROUP_ID,
Decode(p_validation_level,fnd_api.g_valid_level_none,mti.transaction_mode,INV_TXN_MANAGER_GRP.proc_mode_mti),
-- start of fix for eam
-- added following 4 columns
MTI.REBUILD_ITEM_ID,
MTI.REBUILD_ACTIVITY_ID,
MTI.REBUILD_SERIAL_NUMBER,
MTI.rebuild_job_name,
-- end of fix for eam
mti.kanban_card_id,
mti.build_sequence,--J dev
mti.bom_revision,--J dev
mti.routing_revision,--J dev
mti.bom_revision_date,--J dev
mti.alternate_bom_designator,--J dev
mti.alternate_routing_designator,
mti.SECONDARY_TRANSACTION_QUANTITY, -- INVCONV fabdi
mti.SECONDARY_UOM_CODE, -- INVCONV fabdi end
mti.RELIEVE_RESERVATIONS_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.RELIEVE_HIGH_LEVEL_RSV_FLAG, /*** {{ R12 Enhanced reservations code changes ***/
mti.TRANSFER_PRICE -- INVCONV umoogala For Process-Discrete Transfer Enh.
FROM MTL_TRANSACTIONS_INTERFACE MTI,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
/* WHERE MTI.ROWID = p_rowid J-dev*/
and MTI.PROCESS_FLAG = 1
and MTI.TRANSACTION_ACTION_ID IN (1, 27, 33, 34)
and MTI.PARENT_ID = MMTT.TRANSACTION_TEMP_ID
and MTI.TRANSACTION_HEADER_ID = MMTT.TRANSACTION_HEADER_ID;
inv_log_util.trace('going to insert lot'||p_header_id,'INV_TXN_MANAGER_GRP','9');
/* Inserting LOT transactions */
INSERT INTO MTL_TRANSACTION_LOTS_TEMP
(TRANSACTION_TEMP_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
PRIMARY_QUANTITY,
TRANSACTION_QUANTITY,
serial_transaction_temp_id,
LOT_ATTRIBUTE_CATEGORY, --Bug #3841935
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,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY ,
group_header_id,--added for J
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
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,
VENDOR_ID,
TERRITORY_CODE,
PARENT_LOT_NUMBER , -- INVCONV start fabdi
ORIGINATION_TYPE ,
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE,
HOLD_DATE ,
REASON_ID,
SECONDARY_QUANTITY, -- INVCONV start fabdi
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2 --R12 Genealogy enhancements
)
SELECT
TRANSACTION_INTERFACE_ID,
ltrim(rtrim(LOT_NUMBER)), /*Bug 6390860 added ltrim, rtrim*/
LOT_EXPIRATION_DATE,
LAST_UPDATED_BY,
SYSDATE,
SYSDATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
SYSDATE,
REQUEST_ID,
PRIMARY_QUANTITY,
TRANSACTION_QUANTITY,
serial_transaction_temp_id,
LOT_ATTRIBUTE_CATEGORY,
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,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY ,
p_header_id ,--J dev corresponds to the header_id
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
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,
VENDOR_ID,
TERRITORY_CODE,
PARENT_LOT_NUMBER , -- INVCONV start fabdi
ORIGINATION_TYPE ,
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE,
HOLD_DATE ,
REASON_ID,
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV start fabdi
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2 --R12 Genealogy enhancements
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID IN (
SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE mti.TRANSACTION_HEADER_ID = p_header_id
/*WHERE MTI.ROWID = p_rowid J-dev*/
AND mti.TRANSACTION_INTERFACE_ID IS NOT NULL
AND mti.PROCESS_FLAG = 1
AND transaction_type_id NOT IN /*OSFM Support for Lot Serialized Items*/
(inv_globals.g_type_inv_lot_split
, inv_globals.g_type_inv_lot_merge
, inv_globals.g_type_inv_lot_translate
));
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,
parent_serial_number,
SERIAL_ATTRIBUTE_CATEGORY, --Bug #3841935
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,
group_header_id, --added for J
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
status_id, --Bug 5023244
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2) --R12 Genealogy enhancements
SELECT
TRANSACTION_INTERFACE_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,
ltrim(rtrim(FM_SERIAL_NUMBER)),/*Bug 4764048 added ltrim,rtrim*/
ltrim(rtrim(TO_SERIAL_NUMBER)),/*Bug 4764048 added ltrim,rtrim*/
parent_serial_number,
SERIAL_ATTRIBUTE_CATEGORY,
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,
p_header_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
status_id, --Bug 5023244
parent_object_type, --R12 Genealogy enhancements
parent_object_id, --R12 Genealogy enhancements
parent_object_number, --R12 Genealogy enhancements
parent_item_id, --R12 Genealogy enhancements
parent_object_type2, --R12 Genealogy enhancements
parent_object_id2, --R12 Genealogy enhancements
parent_object_number2 --R12 Genealogy enhancements
FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE (TRANSACTION_INTERFACE_ID IN (
SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE TRANSACTION_HEADER_ID = p_header_id
/*WHERE MTI.ROWID = p_rowid*/--J-dev
AND TRANSACTION_INTERFACE_ID IS NOT NULL
AND PROCESS_FLAG = 1
AND transaction_type_id NOT IN
(inv_globals.g_type_inv_lot_split
, inv_globals.g_type_inv_lot_merge
, inv_globals.g_type_inv_lot_translate
)
UNION ALL
SELECT SERIAL_TRANSACTION_TEMP_ID
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID IN (
SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = p_header_id
/*WHERE rowid = p_rowid J-dev*/
AND TRANSACTION_INTERFACE_ID IS NOT NULL
AND PROCESS_FLAG = 1
AND transaction_type_id NOT IN
(inv_globals.g_type_inv_lot_split
, inv_globals.g_type_inv_lot_merge
, inv_globals.g_type_inv_lot_translate
)))
);
inv_log_util.trace('Error in tmpinsert: sqlerrm : ' || substr(sqlerrm, 1, 200),
'INV_TXN_MANAGER_GRP','9');
END tmpinsert;
*Private procedure used in tmpinsert2(). This is used to get all the serial *
*attributes from MSNI in x_lot_ser_attr_tbl which is then used for attr val.*
*****************************************************************************/
PROCEDURE get_serial_attr_record (
x_lot_ser_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
, p_transaction_interface_id IN NUMBER
, p_fm_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_serial_number IN VARCHAR2
, p_item_id IN NUMBER
, p_org_id IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
)
IS
/*Bug:5408823.Commented out the following code as the code to
populate metadata of the serial attributes is moved to
get_serial_attr_table.
*/
/*l_app_owner_schema VARCHAR2 (30);
SELECT column_name
, data_type
, data_length
FROM all_tab_columns
WHERE table_name = UPPER (p_table_name)
AND owner = l_app_owner_schema
Bug:4724150. Commented the following condition 1 as the attribute
columns becomes out of range of 20 to 91 when some extraneous attributes are added
--AND column_id BETWEEN 20 AND 101 --attribute columns.
AND column_name NOT IN ('STATUS_ID','STATUS_NAME', 'ORIGINATION_DATE')
ORDER BY column_id;
l_select_stmt :=
l_select_stmt
|| ' NVL(MSNI.'
|| l_lot_ser_attr_tbl (l_column_idx).column_name
|| ', MSN.'
|| l_lot_ser_attr_tbl (l_column_idx).column_name
|| ')';
l_select_stmt :=
l_select_stmt
|| ', NVL(MSNI.'
|| l_lot_ser_attr_tbl (l_column_idx).column_name
|| ', MSN.'
|| l_lot_ser_attr_tbl (l_column_idx).column_name
|| ')';
l_select_stmt :=
l_select_stmt
|| ' from mtl_serial_numbers_interface msni,'
|| ' mtl_serial_numbers msn,'
|| ' mtl_transaction_lots_interface mtli,'
|| ' mtl_transactions_interface mti'
|| ' where mti.parent_id = :b_parent_id'
|| ' and mti.transaction_interface_id <> mti.parent_id'
|| ' and mtli.transaction_interface_id = mti.transaction_interface_id'
|| ' and msni.transaction_interface_id = mtli.serial_transaction_temp_id'
|| ' AND inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,:b_serial_number) <> -1'
|| ' and inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,nvl(msni.to_serial_number,msni.fm_serial_number)) >='
|| ' inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,:b_serial_number)'
|| ' AND msn.serial_number = :b_serial_number'
|| ' AND msn.inventory_item_id = mti.inventory_item_id '
|| ' AND msn.current_organization_id = mti.organization_id ';
DBMS_SQL.parse (l_sql_p, g_select_stmt, DBMS_SQL.native);
* tmpinsert2() : Procedure to insert records in to MMTT, MTLT and MSNT*
* for lot split/merge/translate transactions *
***************************PSEUDO CODE*********************************
* for each MTI fetched for the given header_id *
* *
* insert into MMTT *
* fetch the MTLI corresponding to the MTI fetched above. *
* insert into MTLT (there will be only one MTLI for a given MTI) *
* if(Lot Split OR Lot Merge) then *
* for each MSNI for the above MTLI *
* Get the number of serials in MSNI (if frm_serial <> to_serial)*
* Get the serial difference if serials present in range *
* For each expanded serial Loop *
* If Resulting Serial THEN *
* Do Serial Attribute Validations *
* Insert into MSNT *
* Else IF Source Serial *
* Insert into MSNT with serial attribts directly from MSNI*
* End Loop *
* Else If Lot Translate Then *
* For each serial in MSN for the lot in MTI Loop *
* Insert into MSNT *
* End Loop *
* Update the MTLT with the serial_txn_temp_id as txn_temp_id of *
* the MSNTs inserted above. *
* End If *
* *
* Populate the genealogy records for the MSNTs inserted. *
* For lot split : populate the resulting MSNTs *
* For lot merge : populate the source MSNTs *
* For lot translate : populate the resulting MSNTs *
***********************************************************************/
PROCEDURE tmpinsert2 (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_validation_status OUT NOCOPY VARCHAR2
, p_header_id IN NUMBER
, p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full
)
IS
/*Select only lot split/merge and translate transactions */
CURSOR mti_csr(l_header_id IN NUMBER)
IS
SELECT mti.transaction_header_id
, mti.parent_id
, mti.transaction_interface_id
, mti.source_code
, mti.source_line_id
, mti.created_by
, mti.last_updated_by
, mti.last_update_login
, mti.program_id
, mti.program_application_id
, mti.request_id
, mti.organization_id
, mti.subinventory_code
, mti.locator_id
, mti.inventory_item_id
, mti.revision
, mti.transaction_type_id
, mti.transaction_action_id
, mti.transaction_source_type_id
, mti.transaction_source_id
, mti.transaction_source_name
, mti.transaction_reference
, mti.reason_id
, mti.transaction_date
, mti.acct_period_id
, mti.transaction_quantity
, mti.transaction_uom
, mti.primary_quantity
, mti.transaction_cost
, mti.distribution_account_id
, mti.transfer_subinventory
, mti.transfer_organization
, mti.transfer_locator
, mti.shipment_number
, mti.transportation_cost
, mti.transfer_cost
, mti.transportation_account
, mti.freight_code
, mti.containers
, mti.waybill_airbill
, mti.expected_arrival_date
, mti.currency_code
, mti.currency_conversion_date
, mti.currency_conversion_type
, mti.currency_conversion_rate
, mti.new_average_cost
, mti.value_change
, mti.percentage_change
, mti.demand_id
, mti.demand_source_header_id
, mti.demand_source_line
, mti.demand_source_delivery
, mti.customer_ship_id
, mti.trx_source_delivery_id
, mti.trx_source_line_id
, mti.picking_line_id
, mti.required_flag
, mti.negative_req_flag
, mti.repetitive_line_id
, mti.primary_switch
, mti.operation_seq_num
, mti.setup_teardown_code
, mti.schedule_update_code
, mti.department_id
, mti.employee_code
, mti.schedule_id
, mti.wip_entity_type
, mti.encumbrance_amount
, mti.encumbrance_account
, mti.ussgl_transaction_code
, mti.shippable_flag
, mti.requisition_line_id
, mti.requisition_distribution_id
, mti.ship_to_location_id
, mti.completion_transaction_id
, mti.attribute_category
, mti.attribute1
, mti.attribute2
, mti.attribute3
, mti.attribute4
, mti.attribute5
, mti.attribute6
, mti.attribute7
, mti.attribute8
, mti.attribute9
, mti.attribute10
, mti.attribute11
, mti.attribute12
, mti.attribute13
, mti.attribute14
, mti.attribute15
, mti.movement_id
, mti.source_project_id
, mti.source_task_id
, mti.expenditure_type
, mti.pa_expenditure_org_id
, mti.project_id
, mti.task_id
, mti.to_project_id
, mti.to_task_id
, mti.final_completion_flag
, mti.transfer_percentage
, mti.material_account
, mti.material_overhead_account
, mti.resource_account
, mti.outside_processing_account
, mti.overhead_account
, mti.cost_group_id
, mti.flow_schedule
, mti.qa_collection_id
, mti.overcompletion_transaction_qty
, mti.overcompletion_primary_qty
, mti.overcompletion_transaction_id
, mti.end_item_unit_number
, mti.org_cost_group_id
, mti.cost_type_id
, mti.lpn_id
, mti.content_lpn_id
, mti.transfer_lpn_id
, mti.organization_type
, mti.transfer_organization_type
, mti.owning_organization_id
, mti.owning_tp_type
, mti.xfr_owning_organization_id
, mti.transfer_owning_tp_type
, mti.planning_organization_id
, mti.planning_tp_type
, mti.xfr_planning_organization_id
, mti.transfer_planning_tp_type
, mti.transaction_batch_id
, mti.transaction_batch_seq
, mti.transfer_cost_group_id
, mti.transaction_mode
, mti.rebuild_item_id
, mti.rebuild_activity_id
, mti.rebuild_serial_number
, mti.rebuild_job_name
, mti.kanban_card_id
, mti.accounting_class
, mti.scheduled_flag
, mti.schedule_number
, mti.routing_revision_date
, mti.move_transaction_id
, mti.wip_supply_type
, mti.build_sequence
, mti.bom_revision
, mti.routing_revision
, mti.bom_revision_date
, mti.alternate_bom_designator
, mti.alternate_routing_designator
, mti.secondary_transaction_quantity
, mti.secondary_uom_code
, mti.relieve_reservations_flag /*** {{ R12 Enhanced reservations code changes ***/
, mti.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
FROM mtl_transactions_interface mti
WHERE mti.transaction_header_id = l_header_id
AND mti.transaction_action_id IN
(inv_globals.g_action_inv_lot_split
, inv_globals.g_action_inv_lot_merge
, inv_globals.g_action_inv_lot_translate
)
AND mti.transaction_source_type_id = 13
AND mti.process_flag = 1
ORDER BY mti.transaction_batch_id
, mti.transaction_batch_seq
, mti.organization_id
, mti.inventory_item_id
, mti.revision
, mti.subinventory_code
, mti.locator_id;
SELECT transaction_interface_id
, ltrim(rtrim(lot_number)) lot_number /*Bug 6390860 added ltrim, rtrim and alias*/
, lot_expiration_date
, last_updated_by
, created_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
, primary_quantity
, transaction_quantity
, serial_transaction_temp_id
, lot_attribute_category
, 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
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, p_header_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, 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
, vendor_id
, territory_code
, parent_lot_number
, origination_type
, expiration_action_date
, expiration_action_code
, hold_date
, reason_id
, secondary_transaction_quantity
--R12 Genealogy enhancements
, parent_object_type
, parent_object_id
, parent_object_number
, parent_item_id
, parent_object_type2
, parent_object_id2
, parent_object_number2 --R12 Genealogy enhancements
FROM mtl_transaction_lots_interface
WHERE transaction_interface_id = p_transaction_interface_id;
SELECT transaction_interface_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
, ltrim(rtrim(fm_serial_number)) fm_serial_number /*Bug 4764048 added ltrim,rtrim*/
, ltrim(rtrim(to_serial_number)) to_serial_number /*Bug 4764048 added ltrim,rtrim*/
, parent_serial_number
, serial_attribute_category
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
FROM mtl_serial_numbers_interface msni
WHERE transaction_interface_id = l_serial_transaction_temp_id;
SELECT serial_attribute_category
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
FROM mtl_serial_numbers msn
WHERE msn.serial_number = l_serial_number
AND msn.inventory_item_id = l_inventory_item_id
AND msn.current_organization_id = l_organization_id;
SELECT 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
, serial_number
, parent_serial_number
, serial_attribute_category
, 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
, p_header_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_status = 3
AND current_subinventory_code = p_subinventory_code
AND NVL(current_locator_id,-1) = NVL(p_locator_id, -1)
AND NVL(lpn_id, -1) = NVL(p_lpn_id, -1)
AND lot_number = p_lot_number ;
*columns need to be populated/updated. We will pass the serial_transaction_temp_id of the
*resultant lot(l_mtli_csr.serial_transaction_temp_id)
*/
CURSOR msnt_serials_csr (l_serial_transaction_temp_id IN NUMBER)
IS
SELECT fm_serial_number
, transaction_temp_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_transaction_temp_id
FOR UPDATE NOWAIT;
SELECT mtli.lot_number
, mtli.serial_transaction_temp_id
FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id =
(SELECT mti.transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = l_transaction_interface_id
AND mti.transaction_interface_id <> mti.parent_id);
mydebug ('breadcrumb 10', 'tmpinsert2');
/* Insert the MMTT for each MTI. For each MTI insert the corresponding MTLT.
* For each MTLT there might be several MSNIs. Populate these into MSNTs .
* For lot translate there will be no MSNIs. In this case we have to get the
* values from the MTLT and MSN.
*/
BEGIN
IF (l_debug = 1)
THEN
mydebug ('breadcrumb 20 header id ' || p_header_id, 'tmpinsert2');
mydebug ('Inserting into MMTT',
'tmpinsert2');
'tmpinsert2');
, 'tmpinsert2'
);
mydebug ('breadcrumb 30','tmpinsert2');
INSERT INTO mtl_material_transactions_temp
(transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, process_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_update_date
, program_application_id
, request_id
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_reference
, reason_id
, transaction_date
, acct_period_id
, transaction_quantity
, transaction_uom
, primary_quantity
, transaction_cost
, distribution_account_id
, transfer_subinventory
, transfer_organization
, transfer_to_location
, shipment_number
, transportation_cost
, transfer_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, currency_code
, currency_conversion_date
, currency_conversion_type
, currency_conversion_rate
, new_average_cost
, value_change
, percentage_change
, demand_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, customer_ship_id
, trx_source_delivery_id
, trx_source_line_id
, picking_line_id
, required_flag
, negative_req_flag
, repetitive_line_id
, primary_switch
, operation_seq_num
, setup_teardown_code
, schedule_update_code
, department_id
, employee_code
, schedule_id
, wip_entity_type
, encumbrance_amount
, encumbrance_account
, ussgl_transaction_code
, shippable_flag
, requisition_line_id
, requisition_distribution_id
, ship_to_location
, completion_transaction_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, source_project_id
, source_task_id
, expenditure_type
, pa_expenditure_org_id
, project_id
, task_id
, to_project_id
, to_task_id
, posting_flag
, final_completion_flag
, transfer_percentage
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, cost_group_id
, flow_schedule
, qa_collection_id
, overcompletion_transaction_qty
, overcompletion_primary_qty
, overcompletion_transaction_id
, end_item_unit_number
, org_cost_group_id
, cost_type_id
, move_order_line_id
, lpn_id
, content_lpn_id
, transfer_lpn_id
, 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
, transaction_batch_id
, transaction_batch_seq
, transfer_cost_group_id
, transaction_mode
, rebuild_item_id
, rebuild_activity_id
, rebuild_serial_number
, rebuild_job_name
, kanban_card_id
, class_code
, scheduled_flag
, schedule_number
, routing_revision_date
, move_transaction_id
, wip_supply_type
, build_sequence
, bom_revision
, routing_revision
, bom_revision_date
, alternate_bom_designator
, alternate_routing_designator
, secondary_transaction_quantity
, secondary_uom_code
, parent_transaction_temp_id
, relieve_reservations_flag /*** {{ R12 Enhanced reservations code changes ***/
, relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
)
VALUES (l_mti_csr.transaction_header_id
, l_mti_csr.transaction_interface_id
, l_mti_csr.source_code
, l_mti_csr.source_line_id
, 'Y'
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
, l_mti_csr.program_id
, l_sysdate
, l_mti_csr.program_application_id
, l_mti_csr.request_id
, l_mti_csr.organization_id
, l_mti_csr.subinventory_code
, l_mti_csr.locator_id
, l_mti_csr.inventory_item_id
, l_mti_csr.revision
, l_mti_csr.transaction_type_id
, l_mti_csr.transaction_action_id
, l_mti_csr.transaction_source_type_id
, l_mti_csr.transaction_source_id
, l_mti_csr.transaction_source_name
, l_mti_csr.transaction_reference
, l_mti_csr.reason_id
, l_mti_csr.transaction_date
, l_mti_csr.acct_period_id
, l_mti_csr.transaction_quantity
, l_mti_csr.transaction_uom
, l_mti_csr.primary_quantity
, l_mti_csr.transaction_cost
, l_mti_csr.distribution_account_id
, l_mti_csr.transfer_subinventory
, l_mti_csr.transfer_organization
, l_mti_csr.transfer_locator
, l_mti_csr.shipment_number
, l_mti_csr.transportation_cost
, l_mti_csr.transfer_cost
, l_mti_csr.transportation_account
, l_mti_csr.freight_code
, l_mti_csr.containers
, l_mti_csr.waybill_airbill
, l_mti_csr.expected_arrival_date
, l_mti_csr.currency_code
, l_mti_csr.currency_conversion_date
, l_mti_csr.currency_conversion_type
, l_mti_csr.currency_conversion_rate
, l_mti_csr.new_average_cost
, l_mti_csr.value_change
, l_mti_csr.percentage_change
, l_mti_csr.demand_id
, l_mti_csr.demand_source_header_id
, l_mti_csr.demand_source_line
, l_mti_csr.demand_source_delivery
, l_mti_csr.customer_ship_id
, l_mti_csr.trx_source_delivery_id
, l_mti_csr.trx_source_line_id
, l_mti_csr.picking_line_id
, l_mti_csr.required_flag
, l_mti_csr.negative_req_flag
, l_mti_csr.repetitive_line_id
, l_mti_csr.primary_switch
, l_mti_csr.operation_seq_num
, l_mti_csr.setup_teardown_code
, l_mti_csr.schedule_update_code
, l_mti_csr.department_id
, l_mti_csr.employee_code
, l_mti_csr.schedule_id
, l_mti_csr.wip_entity_type
, l_mti_csr.encumbrance_amount
, l_mti_csr.encumbrance_account
, l_mti_csr.ussgl_transaction_code
, l_mti_csr.shippable_flag
, l_mti_csr.requisition_line_id
, l_mti_csr.requisition_distribution_id
, l_mti_csr.ship_to_location_id
, NVL (l_mti_csr.completion_transaction_id
, DECODE (l_mti_csr.transaction_action_id
, 31, mtl_material_transactions_s.NEXTVAL
, 32, mtl_material_transactions_s.NEXTVAL
, NULL
)
)
, l_mti_csr.attribute_category
, l_mti_csr.attribute1
, l_mti_csr.attribute2
, l_mti_csr.attribute3
, l_mti_csr.attribute4
, l_mti_csr.attribute5
, l_mti_csr.attribute6
, l_mti_csr.attribute7
, l_mti_csr.attribute8
, l_mti_csr.attribute9
, l_mti_csr.attribute10
, l_mti_csr.attribute11
, l_mti_csr.attribute12
, l_mti_csr.attribute13
, l_mti_csr.attribute14
, l_mti_csr.attribute15
, l_mti_csr.movement_id
, l_mti_csr.source_project_id
, l_mti_csr.source_task_id
, l_mti_csr.expenditure_type
, l_mti_csr.pa_expenditure_org_id
, l_mti_csr.project_id
, l_mti_csr.task_id
, l_mti_csr.to_project_id
, l_mti_csr.to_task_id
, 'N'
, NVL(l_mti_csr.final_completion_flag,Decode(l_patchset_j,1,l_mti_csr.final_completion_flag,'N'))
, l_mti_csr.transfer_percentage
, l_mti_csr.material_account
, l_mti_csr.material_overhead_account
, l_mti_csr.resource_account
, l_mti_csr.outside_processing_account
, l_mti_csr.overhead_account
, l_mti_csr.cost_group_id
, l_mti_csr.flow_schedule
, l_mti_csr.qa_collection_id
, l_mti_csr.overcompletion_transaction_qty
, /* Overcompletion Transactions */
l_mti_csr.overcompletion_primary_qty
, l_mti_csr.overcompletion_transaction_id
, l_mti_csr.end_item_unit_number
, l_mti_csr.org_cost_group_id
, l_mti_csr.cost_type_id
, DECODE (l_mti_csr.transaction_source_type_id
, 4, l_mti_csr.source_line_id
, NULL
)
, l_mti_csr.lpn_id
, l_mti_csr.content_lpn_id
, l_mti_csr.transfer_lpn_id
, l_mti_csr.organization_type
, l_mti_csr.transfer_organization_type
, l_mti_csr.owning_organization_id
, l_mti_csr.owning_tp_type
, l_mti_csr.xfr_owning_organization_id
, l_mti_csr.transfer_owning_tp_type
, l_mti_csr.planning_organization_id
, l_mti_csr.planning_tp_type
, l_mti_csr.xfr_planning_organization_id
, l_mti_csr.transfer_planning_tp_type
, l_mti_csr.transaction_batch_id
, l_mti_csr.transaction_batch_seq
, l_mti_csr.transfer_cost_group_id
,
--this goes into transaction_mode
DECODE (p_validation_level
, fnd_api.g_valid_level_none, l_mti_csr.transaction_mode
, inv_txn_manager_grp.proc_mode_mti
)
, l_mti_csr.rebuild_item_id
, l_mti_csr.rebuild_activity_id
, l_mti_csr.rebuild_serial_number
, l_mti_csr.rebuild_job_name
, l_mti_csr.kanban_card_id
, l_mti_csr.accounting_class
, l_mti_csr.scheduled_flag
, l_mti_csr.schedule_number
, l_mti_csr.routing_revision_date
, l_mti_csr.move_transaction_id
, l_mti_csr.wip_supply_type
, l_mti_csr.build_sequence
, l_mti_csr.bom_revision
, l_mti_csr.routing_revision
, l_mti_csr.bom_revision_date
, l_mti_csr.alternate_bom_designator
, l_mti_csr.alternate_routing_designator
, l_mti_csr.secondary_transaction_quantity
, l_mti_csr.secondary_uom_code
, l_mti_csr.parent_id
, l_mti_csr.relieve_reservations_flag /*** {{ R12 Enhanced reservations code changes ***/
, l_mti_csr.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
);
mydebug ('breadcrumb 40', 'tmpinsert2');
mydebug ('breadcrumb 50', 'tmpinsert2');
mydebug ('Inserting into MTLI', 'tmpinsert2');
INSERT INTO mtl_transaction_lots_temp
(transaction_temp_id
, lot_number
, lot_expiration_date
, last_updated_by
, last_update_date
, creation_date
, created_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, primary_quantity
, transaction_quantity
, serial_transaction_temp_id
, lot_attribute_category
, 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
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, group_header_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, 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
, vendor_id
, territory_code
, parent_lot_number
, origination_type
, expiration_action_date
, expiration_action_code
, hold_date
, reason_id
, secondary_quantity
--R12 Genealogy enhancements
, parent_object_type
, parent_object_id
, parent_object_number
, parent_item_id
, parent_object_type2
, parent_object_id2
, parent_object_number2
--R12 Genealogy enhancements
)
VALUES (l_mtli_csr.transaction_interface_id
, l_mtli_csr.lot_number
, l_mtli_csr.lot_expiration_date
, l_user_id
, l_sysdate
, l_sysdate
, l_user_id
, l_login_id
, l_mtli_csr.program_application_id
, l_mtli_csr.program_id
, l_mtli_csr.program_update_date
, l_mtli_csr.request_id
, l_mtli_csr.primary_quantity
, l_mtli_csr.transaction_quantity
, l_mtli_csr.serial_transaction_temp_id
, l_mtli_csr.lot_attribute_category
, l_mtli_csr.c_attribute1
, l_mtli_csr.c_attribute2
, l_mtli_csr.c_attribute3
, l_mtli_csr.c_attribute4
, l_mtli_csr.c_attribute5
, l_mtli_csr.c_attribute6
, l_mtli_csr.c_attribute7
, l_mtli_csr.c_attribute8
, l_mtli_csr.c_attribute9
, l_mtli_csr.c_attribute10
, l_mtli_csr.c_attribute11
, l_mtli_csr.c_attribute12
, l_mtli_csr.c_attribute13
, l_mtli_csr.c_attribute14
, l_mtli_csr.c_attribute15
, l_mtli_csr.c_attribute16
, l_mtli_csr.c_attribute17
, l_mtli_csr.c_attribute18
, l_mtli_csr.c_attribute19
, l_mtli_csr.c_attribute20
, l_mtli_csr.d_attribute1
, l_mtli_csr.d_attribute2
, l_mtli_csr.d_attribute3
, l_mtli_csr.d_attribute4
, l_mtli_csr.d_attribute5
, l_mtli_csr.d_attribute6
, l_mtli_csr.d_attribute7
, l_mtli_csr.d_attribute8
, l_mtli_csr.d_attribute9
, l_mtli_csr.d_attribute10
, l_mtli_csr.n_attribute1
, l_mtli_csr.n_attribute2
, l_mtli_csr.n_attribute3
, l_mtli_csr.n_attribute4
, l_mtli_csr.n_attribute5
, l_mtli_csr.n_attribute6
, l_mtli_csr.n_attribute7
, l_mtli_csr.n_attribute8
, l_mtli_csr.n_attribute9
, l_mtli_csr.n_attribute10
, l_mtli_csr.attribute1
, l_mtli_csr.attribute2
, l_mtli_csr.attribute3
, l_mtli_csr.attribute4
, l_mtli_csr.attribute5
, l_mtli_csr.attribute6
, l_mtli_csr.attribute7
, l_mtli_csr.attribute8
, l_mtli_csr.attribute9
, l_mtli_csr.attribute10
, l_mtli_csr.attribute11
, l_mtli_csr.attribute12
, l_mtli_csr.attribute13
, l_mtli_csr.attribute14
, l_mtli_csr.attribute15
, l_mtli_csr.attribute_category
, l_mtli_csr.p_header_id
, l_mtli_csr.description
, l_mtli_csr.vendor_name
, l_mtli_csr.supplier_lot_number
, l_mtli_csr.origination_date
, l_mtli_csr.date_code
, l_mtli_csr.grade_code
, l_mtli_csr.change_date
, l_mtli_csr.maturity_date
, l_mtli_csr.status_id
, l_mtli_csr.retest_date
, l_mtli_csr.age
, l_mtli_csr.item_size
, l_mtli_csr.color
, l_mtli_csr.volume
, l_mtli_csr.volume_uom
, l_mtli_csr.place_of_origin
, l_mtli_csr.best_by_date
, l_mtli_csr.LENGTH
, l_mtli_csr.length_uom
, l_mtli_csr.recycled_content
, l_mtli_csr.thickness
, l_mtli_csr.thickness_uom
, l_mtli_csr.width
, l_mtli_csr.width_uom
, l_mtli_csr.curl_wrinkle_fold
, l_mtli_csr.vendor_id
, l_mtli_csr.territory_code
, l_mtli_csr.parent_lot_number
, l_mtli_csr.origination_type
, l_mtli_csr.expiration_action_date
, l_mtli_csr.expiration_action_code
, l_mtli_csr.hold_date
, l_mtli_csr.reason_id
, l_mtli_csr.secondary_transaction_quantity
--R12 Genealogy enhancements
, l_mtli_csr.parent_object_type
, l_mtli_csr.parent_object_id
, l_mtli_csr.parent_object_number
, l_mtli_csr.parent_item_id
, l_mtli_csr.parent_object_type2
, l_mtli_csr.parent_object_id2
, l_mtli_csr.parent_object_number2
--R12 Genealogy enhancements
);
mydebug ('breadcrumb 60', 'tmpinsert2');
/* Need to insert the MSNTs if the item is serial controlled as well */
BEGIN
IF (l_debug = 1)
THEN
mydebug ('Determine the serial control code', 'tmpinsert2');
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_mti_csr.inventory_item_id
AND organization_id = l_mti_csr.organization_id;
, 'tmpinsert2'
);
mydebug ('breadcrumb 70', 'tmpinsert2');
, 'tmpinsert2'
);
mydebug ('breadcrumb 80', 'tmpinsert2');
mydebug ('Lot Split/merge transaction', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 90', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 100', 'tmpinsert2');
, 'tmpinsert2'
);
mydebug ('breadcrumb 110', 'tmpinsert2');
mydebug ('breadcrumb 120', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 130', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 140', 'tmpinsert2');
mydebug ('l_validated_ser_attr_tbl.COUNT => ' || l_validated_ser_attr_tbl.COUNT, 'tmpinsert2');
mydebug ('Inserting into MSNT', 'tmpinsert2');
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
, parent_serial_number
, dff_updated_flag
, serial_attribute_category
, 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
, group_header_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
)
VALUES (l_ser_csr.transaction_interface_id
, l_sysdate
, l_ser_csr.last_updated_by
, l_sysdate
, l_ser_csr.created_by
, l_ser_csr.last_update_login
, l_ser_csr.request_id
, l_ser_csr.program_application_id
, l_ser_csr.program_id
, l_ser_csr.program_update_date
, l_ser_csr.vendor_serial_number
, l_ser_csr.vendor_lot_number
, l_next_serial
, l_next_serial
, l_ser_csr.parent_serial_number
, 'Y'
, l_indexed_ser_attr_tbl('SERIAL_ATTRIBUTE_CATEGORY').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE1').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE2').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE3').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE4').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE5').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE6').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE7').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE8').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE9').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE10').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE11').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE12').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE13').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE14').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE15').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE16').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE17').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE18').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE19').column_value
, l_indexed_ser_attr_tbl ('C_ATTRIBUTE20').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE1').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE2').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE3').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE4').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE5').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE6').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE7').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE8').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE9').column_value
, l_indexed_ser_attr_tbl ('D_ATTRIBUTE10').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE1').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE2').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE3').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE4').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE5').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE6').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE7').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE8').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE9').column_value
, l_indexed_ser_attr_tbl ('N_ATTRIBUTE10').column_value
, p_header_id -- Added for J
, l_indexed_ser_attr_tbl ('ATTRIBUTE_CATEGORY').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE1').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE2').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE3').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE4').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE5').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE6').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE7').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE8').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE9').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE10').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE11').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE12').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE13').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE14').column_value
, l_indexed_ser_attr_tbl ('ATTRIBUTE15').column_value
, l_ser_csr.status_id
, l_indexed_ser_attr_tbl ('TERRITORY_CODE').column_value
, l_indexed_ser_attr_tbl ('TIME_SINCE_NEW').column_value
, l_indexed_ser_attr_tbl ('CYCLES_SINCE_NEW').column_value
, l_indexed_ser_attr_tbl ('TIME_SINCE_OVERHAUL').column_value
, l_indexed_ser_attr_tbl ('CYCLES_SINCE_OVERHAUL').column_value
, l_indexed_ser_attr_tbl ('TIME_SINCE_REPAIR').column_value
, l_indexed_ser_attr_tbl ('CYCLES_SINCE_REPAIR').column_value
, l_indexed_ser_attr_tbl ('TIME_SINCE_VISIT').column_value
, l_indexed_ser_attr_tbl ('CYCLES_SINCE_VISIT').column_value
, l_indexed_ser_attr_tbl ('TIME_SINCE_MARK').column_value
, l_indexed_ser_attr_tbl ('CYCLES_SINCE_MARK').column_value
, l_indexed_ser_attr_tbl ('NUMBER_OF_REPAIRS').column_value
);
mydebug ('breadcrumb 150', 'tmpinsert2');
mydebug ('breadcrumb 160', 'tmpinsert2');
('Inserting MSNTs for the source for split/merge'
, 'tmpinsert2'
);
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
, parent_serial_number
, dff_updated_flag
, serial_attribute_category
, 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
, group_header_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
)
VALUES (l_ser_csr.transaction_interface_id
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
, l_ser_csr.request_id
, l_ser_csr.program_application_id
, l_ser_csr.program_id
, l_ser_csr.program_update_date
, l_ser_csr.vendor_serial_number
, l_ser_csr.vendor_lot_number
, l_next_serial
, l_next_serial
, l_ser_csr.parent_serial_number
, 'Y'
, l_ser_csr.serial_attribute_category
, l_ser_csr.c_attribute1
, l_ser_csr.c_attribute2
, l_ser_csr.c_attribute3
, l_ser_csr.c_attribute4
, l_ser_csr.c_attribute5
, l_ser_csr.c_attribute6
, l_ser_csr.c_attribute7
, l_ser_csr.c_attribute8
, l_ser_csr.c_attribute9
, l_ser_csr.c_attribute10
, l_ser_csr.c_attribute11
, l_ser_csr.c_attribute12
, l_ser_csr.c_attribute13
, l_ser_csr.c_attribute14
, l_ser_csr.c_attribute15
, l_ser_csr.c_attribute16
, l_ser_csr.c_attribute17
, l_ser_csr.c_attribute18
, l_ser_csr.c_attribute19
, l_ser_csr.c_attribute20
, l_ser_csr.d_attribute1
, l_ser_csr.d_attribute2
, l_ser_csr.d_attribute3
, l_ser_csr.d_attribute4
, l_ser_csr.d_attribute5
, l_ser_csr.d_attribute6
, l_ser_csr.d_attribute7
, l_ser_csr.d_attribute8
, l_ser_csr.d_attribute9
, l_ser_csr.d_attribute10
, l_ser_csr.n_attribute1
, l_ser_csr.n_attribute2
, l_ser_csr.n_attribute3
, l_ser_csr.n_attribute4
, l_ser_csr.n_attribute5
, l_ser_csr.n_attribute6
, l_ser_csr.n_attribute7
, l_ser_csr.n_attribute8
, l_ser_csr.n_attribute9
, l_ser_csr.n_attribute10
, p_header_id
, l_ser_csr.attribute_category
, l_ser_csr.attribute1
, l_ser_csr.attribute2
, l_ser_csr.attribute3
, l_ser_csr.attribute4
, l_ser_csr.attribute5
, l_ser_csr.attribute6
, l_ser_csr.attribute7
, l_ser_csr.attribute8
, l_ser_csr.attribute9
, l_ser_csr.attribute10
, l_ser_csr.attribute11
, l_ser_csr.attribute12
, l_ser_csr.attribute13
, l_ser_csr.attribute14
, l_ser_csr.attribute15
, l_ser_csr.status_id
, l_ser_csr.territory_code
, l_ser_csr.time_since_new
, l_ser_csr.cycles_since_new
, l_ser_csr.time_since_overhaul
, l_ser_csr.cycles_since_overhaul
, l_ser_csr.time_since_repair
, l_ser_csr.cycles_since_repair
, l_ser_csr.time_since_visit
, l_ser_csr.cycles_since_visit
, l_ser_csr.time_since_mark
, l_ser_csr.cycles_since_mark
, l_ser_csr.number_of_repairs
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 170', 'tmpinsert2');
('NO_DATA_FOUND while inserting into MSNT for lot /split'
, ''
);
('exception raised while inserting into MSNT for lot split/merge'
, SQLERRM
);
mydebug ('This is lot translate transaction', 'tmpinsert2');
mydebug ('breadcrumb 180', 'tmpinsert2');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_sequence
FROM DUAL;
SELECT mti.inventory_item_id
, mtli.lot_number
, mti.subinventory_code
, mti.locator_id
, mti.lpn_id
INTO l_old_item_id
,l_old_lot_num
,l_old_sub_code
,l_old_locator_id
,l_old_lpn_id
FROM mtl_transactions_interface mti
,mtl_transaction_lots_interface mtli
WHERE mti.transaction_interface_id = mtli.transaction_interface_id
AND mti.transaction_interface_id = mti.parent_id
AND mti.transaction_interface_id = l_mti_csr.parent_id;
mydebug('In Lot translate : dest Records : checking DFF context', 'tmpinsert2');
mydebug('l_context_value_src => '|| l_context_value_src, 'tmpinsert2');
mydebug('l_context_value_dst => '|| l_context_value_dst, 'tmpinsert2');
mydebug('breadcrumb 185', 'tmpinsert2');
mydebug('Lot translate : Items have different Serial Attr Context', 'tmpinsert2');
mydebug ('breadcrumb 190', 'tmpinsert2');
mydebug ('In loop Inserting MSNT for serial => ' || l_ser_csr.serial_number
, 'tmpinsert2'
);
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
, parent_serial_number
, serial_attribute_category
, 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
, group_header_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
)
VALUES (l_sequence
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
, l_ser_csr.request_id
, l_ser_csr.program_application_id
, l_ser_csr.program_id
, l_ser_csr.program_update_date
, l_ser_csr.vendor_serial_number
, l_ser_csr.vendor_lot_number
, l_ser_csr.serial_number
, l_ser_csr.serial_number
, l_ser_csr.parent_serial_number
, l_ser_csr.serial_attribute_category
, l_ser_csr.c_attribute1
, l_ser_csr.c_attribute2
, l_ser_csr.c_attribute3
, l_ser_csr.c_attribute4
, l_ser_csr.c_attribute5
, l_ser_csr.c_attribute6
, l_ser_csr.c_attribute7
, l_ser_csr.c_attribute8
, l_ser_csr.c_attribute9
, l_ser_csr.c_attribute10
, l_ser_csr.c_attribute11
, l_ser_csr.c_attribute12
, l_ser_csr.c_attribute13
, l_ser_csr.c_attribute14
, l_ser_csr.c_attribute15
, l_ser_csr.c_attribute16
, l_ser_csr.c_attribute17
, l_ser_csr.c_attribute18
, l_ser_csr.c_attribute19
, l_ser_csr.c_attribute20
, l_ser_csr.d_attribute1
, l_ser_csr.d_attribute2
, l_ser_csr.d_attribute3
, l_ser_csr.d_attribute4
, l_ser_csr.d_attribute5
, l_ser_csr.d_attribute6
, l_ser_csr.d_attribute7
, l_ser_csr.d_attribute8
, l_ser_csr.d_attribute9
, l_ser_csr.d_attribute10
, l_ser_csr.n_attribute1
, l_ser_csr.n_attribute2
, l_ser_csr.n_attribute3
, l_ser_csr.n_attribute4
, l_ser_csr.n_attribute5
, l_ser_csr.n_attribute6
, l_ser_csr.n_attribute7
, l_ser_csr.n_attribute8
, l_ser_csr.n_attribute9
, l_ser_csr.n_attribute10
, p_header_id
, l_ser_csr.attribute_category
, l_ser_csr.attribute1
, l_ser_csr.attribute2
, l_ser_csr.attribute3
, l_ser_csr.attribute4
, l_ser_csr.attribute5
, l_ser_csr.attribute6
, l_ser_csr.attribute7
, l_ser_csr.attribute8
, l_ser_csr.attribute9
, l_ser_csr.attribute10
, l_ser_csr.attribute11
, l_ser_csr.attribute12
, l_ser_csr.attribute13
, l_ser_csr.attribute14
, l_ser_csr.attribute15
, l_ser_csr.status_id
, l_ser_csr.territory_code
, l_ser_csr.time_since_new
, l_ser_csr.cycles_since_new
, l_ser_csr.time_since_overhaul
, l_ser_csr.cycles_since_overhaul
, l_ser_csr.time_since_repair
, l_ser_csr.cycles_since_repair
, l_ser_csr.time_since_visit
, l_ser_csr.cycles_since_visit
, l_ser_csr.time_since_mark
, l_ser_csr.cycles_since_mark
, l_ser_csr.number_of_repairs
);
mydebug ('breadcrumb 200', 'tmpinsert2');
/*Need to update the MTLT with the serial_transaction_temp_id generated above */
IF (l_debug = 1)
THEN
mydebug
(' update the MTLT with the serial transaction temp_id '
, 'tmpinsert2'
);
, 'tmpinsert2'
);
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = l_sequence
WHERE transaction_temp_id = l_mtli_csr.transaction_interface_id;
, 'tmpinsert2'
);
mydebug ('breadcrumb 210', 'tmpinsert2');
mydebug ('breadcrumb 220', 'tmpinsert2');
SELECT lot_number
INTO l_parent_object_number2
FROM mtl_transaction_lots_interface
WHERE transaction_interface_id = l_mti_csr.parent_id;
SELECT serial_transaction_temp_id
INTO l_serial_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mtli_csr.transaction_interface_id;
, 'tmpinsert2'
);
, 'tmpinsert2'
);
mydebug ('breadcrumb 230', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
UPDATE mtl_serial_numbers_temp
SET parent_object_number2 = l_mtli_csr.lot_number
, parent_object_number = fm_serial_number
, object_number2 = l_parent_object_number2
, object_type2 = 1
, parent_object_type2 = 1
WHERE transaction_temp_id =
NVL (l_mtli_csr.serial_transaction_temp_id --For Lot translate
, l_serial_temp_id
);
mydebug ('breadcrumb 240', 'tmpinsert2');
, 'tmpinsert2'
);
, 'tmpinsert2'
);
, 'tmpinsert2'
);
UPDATE mtl_serial_numbers_temp
SET parent_object_number2 = l_parent_object_number2
, parent_object_number = fm_serial_number
, object_number2 = l_mtli_csr.lot_number
, object_type2 = 1
, parent_object_type2 = 1
WHERE transaction_temp_id =
l_mtli_csr.serial_transaction_temp_id;
mydebug ('breadcrumb 250', 'tmpinsert2');
mydebug ('breadcrumb 260', 'tmpinsert2');
, 'tmpinsert2'
);
mydebug ('breadcrumb 270', 'tmpinsert2');
, 'tmpinsert2'
);
mydebug ('breadcrumb 280', 'tmpinsert2');
mydebug (' exception happened in tmpinsert2 ' || SQLERRM, 'tmpinsert2');
mydebug (' insertions done .. !! ', 'tmpinsert2');
mydebug ('breadcrumb 290', 'tmpinsert2');
END tmpinsert2;
SELECT FND_PROFILE.value(p_prof)
INTO x_ret
FROM dual;
SELECT
TRANSACTION_INTERFACE_ID,
TRANSACTION_HEADER_ID,
REQUEST_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
TRANSFER_ORGANIZATION,
TRANSFER_SUBINVENTORY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_QUANTITY,
LOCATOR_ID,
TRANSFER_LOCATOR,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
DISTRIBUTION_ACCOUNT_ID,
NVL(SHIPPABLE_FLAG,'Y'),
ROWID,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
REQUISITION_LINE_ID,
OVERCOMPLETION_TRANSACTION_QTY, /* Overcompletion Transactions */
END_ITEM_UNIT_NUMBER,
SCHEDULED_PAYBACK_DATE, /* Borrow Payback */
REVISION, /* Borrow Payback */
ORG_COST_GROUP_ID, /* PCST */
COST_TYPE_ID, /* PCST */
PRIMARY_QUANTITY,
SOURCE_LINE_ID,
PROCESS_FLAG,
TRANSACTION_SOURCE_NAME,
TRX_SOURCE_DELIVERY_ID,
TRX_SOURCE_LINE_ID,
PARENT_ID,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
-- INVCONV start fabdi
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_UOM_CODE
-- INVCONV end fabdi
,SHIP_TO_LOCATION_ID -- eIB Build; Bug# 4348541
SELECT
p_line_rec_type.ROWID,
p_line_rec_type.INVENTORY_ITEM_ID,
p_line_rec_type.REVISION,
p_line_rec_type.ORGANIZATION_ID,
p_line_rec_type.SUBINVENTORY_CODE,
p_line_rec_type.LOCATOR_ID,
ABS(p_line_rec_type.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
NULL LOT_NUMBER,
p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
p_line_rec_type.TRANSACTION_ACTION_ID,
p_line_rec_type.TRANSACTION_SOURCE_ID,
p_line_rec_type.TRANSACTION_SOURCE_NAME,
--Jalaj Srivastava 5010595
--for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
decode(p_line_rec_type.transaction_source_type_id,5,decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID),p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
MSI.REVISION_QTY_CONTROL_CODE,
decode(p_line_rec_type.transaction_source_type_id,5,1,MSI.lot_control_code) lot_control_code,--j-dev
decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,null) TRANSFER_SUBINVENTORY,
p_line_rec_type.TRANSFER_LOCATOR,
p_line_rec_type.transaction_date,
MP.NEGATIVE_INV_RECEIPT_CODE
FROM MTL_PARAMETERS MP,
MTL_SYSTEM_ITEMS MSI
WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
-- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
AND p_line_rec_type.PROCESS_FLAG = 1
--Jalaj Srivastava 5010595
--for GME (wip_enity_type=10) select only non lot controlled items
AND ((MSI.LOT_CONTROL_CODE = 1) OR (p_line_rec_type.transaction_source_type_id=5 and p_line_rec_type.wip_entity_type <> 10))--J-dev--verify this
AND ( ( (p_line_rec_type.wip_entity_type <> 10)
AND ( (p_flow_sch <> 1
AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
OR (p_flow_sch = 1
AND p_line_rec_type.TRANSACTION_ACTION_ID = 32 )
)
)
--Jalaj Srivastava 5232394
--select all transactions for GME
OR (p_line_rec_type.wip_entity_type = 10)
)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
UNION
SELECT
p_line_rec_type.ROWID,
p_line_rec_type.INVENTORY_ITEM_ID,
p_line_rec_type.REVISION,
p_line_rec_type.ORGANIZATION_ID,
p_line_rec_type.SUBINVENTORY_CODE,
p_line_rec_type.LOCATOR_ID,
ABS(MTLI.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
MTLI.lot_number LOT_NUMBER,
p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
p_line_rec_type.TRANSACTION_ACTION_ID,
p_line_rec_type.TRANSACTION_SOURCE_ID,
p_line_rec_type.TRANSACTION_SOURCE_NAME,
--Jalaj Srivastava 5010595
--for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
MSI.REVISION_QTY_CONTROL_CODE,
MSI.lot_control_code lot_control_code,
decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,5,p_line_rec_type.transfer_subinventory,null) TRANSFER_SUBINVENTORY,
p_line_rec_type.TRANSFER_LOCATOR,
p_line_rec_type.transaction_date,
MP.NEGATIVE_INV_RECEIPT_CODE
FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
MTL_PARAMETERS MP,
MTL_SYSTEM_ITEMS MSI
WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
--AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
AND MTLI.TRANSACTION_INTERFACE_ID = p_line_rec_type.TRANSACTION_INTERFACE_ID
AND p_line_rec_type.PROCESS_FLAG = 1
AND MSI.LOT_CONTROL_CODE = 2
AND ( ( (p_line_rec_type.wip_entity_type <> 10)
AND ( (p_flow_sch <> 1
AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
OR (p_flow_sch = 1
AND p_line_rec_type.TRANSACTION_ACTION_ID = 32 )
)
)
--Jalaj Srivastava 5232394
--select all transactions for GME
OR (p_line_rec_type.wip_entity_type = 10)
)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
-- Pawan 11th july added this for validation of lot for GME only
AND ((p_line_rec_type.transaction_source_type_id <> 5) OR
(p_line_rec_type.transaction_source_type_id = 5 AND
p_line_rec_type.wip_entity_type = 10 ));--J-dev verify
SELECT DECODE(UPPER(FLOW_SCHEDULE),'Y', 1, 0)
INTO gi_flow_schedule
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = l_header_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_ACTION_ID IN (30,31, 32) --CFM Scrap Transactions
AND PROCESS_FLAG = 1
AND ROWNUM < 2 ;
/** UPDATE MTI row with Group Failure Message **/
null;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID IN
(SELECT DISTINCT MTI2.TRANSACTION_BATCH_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI2
WHERE MTI2.TRANSACTION_HEADER_ID = l_header_id
AND MTI2.PROCESS_FLAG = 3
AND MTI2.ERROR_CODE IS NOT NULL);
SELECT demand_source_header_id , demand_source_line
INTO l_dem_hdr_id,l_dem_line_id
FROM mtl_transactions_interface
WHERE
ROWID = l_temp_rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID =l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_interface_id = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;--Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_interface_id = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
inv_quantity_tree_pub.update_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 2,
p_is_revision_control => l_revision_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_demand_source_type_id => nvl(l_srctypeid,-9999),
p_demand_source_header_id => nvl(l_srcid,-9999),
p_demand_source_line_id => nvl(l_srclineid,-9999),
p_revision => l_rev,
p_lot_number => l_lotnum,
p_subinventory_code => l_xfrsub,
p_locator_id => l_xlocid,
p_primary_quantity => l_trx_qty,
p_quantity_type => inv_quantity_tree_pvt.g_qoh,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
update tree with correct sign.
sign is derived from transaction_quantity */
inv_quantity_tree_pub.update_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 2,
p_is_revision_control => l_revision_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_demand_source_type_id => nvl(l_srctypeid,-9999),
p_demand_source_header_id => nvl(l_srcid,-9999),
p_demand_source_line_id => nvl(l_srclineid,-9999),
p_revision => l_rev,
p_lot_number => l_lotnum,
p_subinventory_code => l_sub_code,
p_locator_id => l_locid,
p_primary_quantity => (sign(l_line_rec_type.transaction_quantity)*(l_trx_qty)),
p_quantity_type => inv_quantity_tree_pvt.g_qoh,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
inv_log_util.trace('after update of quantity tree qoh='||l_qoh||' l_att='||l_att||' l_atr='||l_atr,'INV_TXN_MANAGER_GRP', 9);
SELECT COUNT(*)
INTO l_count_success
FROM mtl_transactions_interface
WHERE transaction_header_id = l_header_id
AND process_flag = 1;
IF (NOT tmpinsert(l_header_id,p_validation_level)) THEN
l_error_exp := FND_MESSAGE.get;
inv_log_util.trace('Error in tmpinsert='|| l_error_exp, 'INV_TXN_MANAGER_GRP', 9);
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID =l_header_id
AND PROCESS_FLAG = 1;
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID
IN(
SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID =l_header_id
AND PROCESS_FLAG <> 3
union all
SELECT SERIAL_TRANSACTION_TEMP_ID
FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID
IN (
SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG <> 3 )) ;
DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID IN
(SELECT TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID =l_header_id
AND PROCESS_FLAG <> 3);
DELETE FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG <> 3;
END IF;--tmpinsert
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1;
SELECT *
FROM mtl_lot_numbers
WHERE lot_number = p_lotnum
AND inventory_item_id = p_itemid
AND organization_id = p_orgid;
SELECT MTR.REASON_ID
FROM MTL_TRANSACTION_REASONS MTR
WHERE MTR.REASON_ID = p_reason_id
AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
select RETEST_INTERVAL,
EXPIRATION_ACTION_INTERVAL ,
SHELF_LIFE_CODE,
MATURITY_DAYS,
HOLD_DAYS,
CHILD_LOT_FLAG ,
GRADE_CONTROL_FLAG
from mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT count(1) into l_lot_onhand
FROM mtl_onhand_quantities_detail
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
AND lot_number = p_lot_number
AND PRIMARY_TRANSACTION_QUANTITY > 0
AND rownum = 1;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_INTERFACE_ID = p_intid;
errupdate(p_rowid,null);
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_INTERFACE_ID = p_intid;
errupdate(p_rowid,null);
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = l_reqstid,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_INTERFACE_ID = p_intid;
errupdate(p_rowid,null);
IF (l_debug = 1) THEN mydebug('{{ Select lot_number from MLN}}' ); END IF;
select lot_number
INTO l_object_number
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id;
IF (l_debug = 1) THEN mydebug('{{ Select serial_number from MSN }}' ); END IF;
SELECT serial_number
INTO l_object_number
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id;
IF (l_debug = 1) THEN mydebug('{{ Select gen_object_id from MLN }}' ); END IF;
select gen_object_id
INTO l_object_id
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND lot_number = p_object_number;
IF (l_debug = 1) THEN mydebug('{{ Select gen_object_id from MSN}}' ); END IF;
SELECT gen_object_id
INTO l_object_id
FROM mtl_serial_numbers
WHERE current_organization_id = p_org_id
AND inventory_item_id = p_item_id
AND serial_number = p_object_number;
IF (l_debug = 1) THEN mydebug('{{ Select lot_number from MLN}}' ); END IF;
select lot_number
INTO l_object_number2
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id2;
IF (l_debug = 1) THEN mydebug('{{ Select serial_number from MSN }}' ); END IF;
SELECT serial_number
INTO l_object_number2
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id2;
IF (l_debug = 1) THEN mydebug('{{ Select gen_object_id from MLN }}' ); END IF;
select gen_object_id
INTO l_object_id2
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND lot_number = p_object_number2;
IF (l_debug = 1) THEN mydebug('{{ Select gen_object_id from MSN}}' ); END IF;
SELECT gen_object_id
INTO l_object_id2
FROM mtl_serial_numbers
WHERE current_organization_id = p_org_id
AND inventory_item_id = p_item_id
AND serial_number = p_object_number2;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET parent_object_id = l_object_id
,parent_object_id2 = l_object_id2
,parent_object_number = l_object_number
,parent_object_number2 = l_object_number2
WHERE ROWID = p_rowid;
UPDATE MTL_SERIAL_NUMBERS_INTERFACE MSNI
SET parent_object_id = l_object_id
,parent_object_id2 = l_object_id2
,parent_object_number = l_object_number
,parent_object_number2 = l_object_number2
WHERE ROWID = p_rowid;
SELECT FM_SERIAL_NUMBER
,TO_SERIAL_NUMBER
,PARENT_SERIAL_NUMBER
,PARENT_OBJECT_TYPE
,PARENT_OBJECT_ID
,PARENT_OBJECT_NUMBER
,PARENT_OBJECT_TYPE2
,PARENT_OBJECT_ID2
,PARENT_OBJECT_NUMBER2
,PARENT_ITEM_ID
,ROWID
FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = p_interface_id;