[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ parallel(cte) */
cte.inv_material_transaction_id mtl_txn_id,
cte.transaction_error_id txn_error_id
FROM csi_txn_errors cte
WHERE cte.processed_flag in ('E', 'R')
AND cte.inv_material_transaction_id is not null;
SELECT mut.serial_number serial_number,
mut.inventory_item_id item_id,
mut.organization_id organization_id
FROM mtl_unit_transactions mut
WHERE mut.transaction_id = p_mtl_txn_id
UNION
SELECT mut.serial_number serial_number,
mut.inventory_item_id item_id,
mut.organization_id organization_id
FROM mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut
WHERE mtln.transaction_id = p_mtl_txn_id
AND mut.transaction_id = mtln.serial_transaction_id;
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
mmt.transaction_date mtl_txn_date,
mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.transaction_type_id mtl_type_id,
mtt.transaction_type_name mtl_txn_name,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_source_type_id,
mmt.transaction_source_id mtl_source_id,
mmt.trx_source_line_id mtl_source_line_id,
mmt.transaction_quantity mtl_txn_qty,
mtt.type_class mtl_type_class,
mmt.transfer_transaction_id mtl_xfer_txn_id,
mmt.revision mtl_revision,
to_char(null) lot_number,
to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mmt.transaction_id = mut.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
UNION
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
mmt.transaction_date mtl_txn_date,
mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.transaction_type_id mtl_type_id,
mtt.transaction_type_name mtl_txn_name,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_source_type_id,
mmt.transaction_source_id mtl_source_id,
mmt.trx_source_line_id mtl_source_line_id,
mmt.transaction_quantity mtl_txn_qty,
mtt.type_class mtl_type_class,
mmt.transfer_transaction_id mtl_xfer_txn_id,
mmt.revision mtl_revision,
mtln.lot_number lot_number,
to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mtln.organization_id = mut.organization_id
AND mtln.transaction_date = mut.transaction_date
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
ORDER BY 1 desc, 2 desc;
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.transfer_organization_id xfer_organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
abs(mmt.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.transaction_action_id mtl_action_id,
mmt.trx_source_line_id trx_source_line_id,
mmt.transaction_source_id mtl_source_id,
mmt.source_project_id source_project_id,
mmt.source_task_id source_task_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id;
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
to_char(null) lot_number,
abs(mmt.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt
WHERE (mmt.transaction_id = p_mtl_txn_id
OR
mmt.transaction_id = p_mtl_xfer_txn_id)
AND mmt.transaction_quantity < 0
UNION
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
mtln.lot_number lot_number,
abs(mtln.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE (mmt.transaction_id = p_mtl_txn_id
OR
mmt.transaction_id = p_mtl_xfer_txn_id)
AND mmt.transaction_quantity < 0
AND mtln.transaction_id = mmt.transaction_id;
SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS : ')
INTO l_date_time_stamp
FROM sys.dual;
PROCEDURE Update_Lookup(p_routine_name in varchar2) IS
l_type VARCHAR2(30) := 'CSI_CORRECTION_ROUTINES';
UPDATE FND_LOOKUP_VALUES
SET enabled_flag = 'N',
last_updated_by = -1,
last_update_date = sysdate
WHERE lookup_type = l_type
AND lookup_code = p_routine_name;
END Update_Lookup;
SELECT transaction_type_id
INTO l_txn_type_id
FROM csi_txn_types
WHERE source_transaction_type = 'DATA_CORRECTION';
SELECT mtln.lot_number
FROM mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut
WHERE mtln.transaction_id = cp_mtl_txn_id
AND mut.transaction_id = mtln.serial_transaction_id
AND mut.serial_number = cp_serial_number;
select CSI_ITEM_INSTANCES_H_S.nextval
into p_inst_hist_tbl(i) from dual;
select CSI_II_RELATIONSHIPS_H_S.nextval
into p_rel_hist_tbl(i) from dual;
select CSI_I_VERSION_LABELS_H_S.nextval
into p_ver_label_hist_tbl(i) from dual;
select CSI_I_PARTIES_H_S.nextval
into p_party_hist_tbl(i) from dual;
select CSI_IP_ACCOUNTS_H_S.nextval
into p_account_hist_tbl(i) from dual;
select CSI_I_ORG_ASSIGNMENTS_H_S.nextval
into p_org_hist_tbl(i) from dual;
select CSI_I_PRICING_ATTRIBS_H_S.nextval
into p_pricing_hist_tbl(i) from dual;
select CSI_IEA_VALUES_H_S.nextval
into p_ext_hist_tbl(i) from dual;
select CSI_I_ASSETS_H_S.nextval
into p_asset_hist_tbl(i) from dual;
p_asset_rec_tab.update_status(i) := p_asset_tbl(i).update_status;
SELECT object_version_number
INTO x_pty_tbl(l_ind).object_version_number
FROM csi_i_parties
WHERE instance_party_id = p_pty_h_tbl(l_ind).instance_party_id;
SELECT object_version_number
INTO x_pa_tbl(l_ind).object_version_number
FROM csi_ip_accounts
WHERE ip_account_id = p_pa_h_tbl(l_ind).ip_account_id;
SELECT object_version_number
INTO x_ou_tbl(l_ind).object_version_number
FROM csi_i_org_assignments
WHERE instance_ou_id = p_ou_h_tbl(l_ind).instance_ou_id;
x_asset_tbl(l_ind).update_status := p_asset_h_tbl(l_ind).update_status;
SELECT object_version_number
INTO x_asset_tbl(l_ind).object_version_number
FROM csi_i_assets
WHERE instance_asset_id = p_asset_h_tbl(l_ind).instance_asset_id;
SELECT instance_id,
date_time_stamp,
inventory_item_id,
serial_number,
lot_control_code,
mtl_txn_id,
mtl_txn_creation_date
FROM csi_ii_forward_sync_temp
WHERE process_flag <> 'P';
px_pty_tbl.delete;
px_pa_tbl.delete;
px_ou_tbl.delete;
px_prc_tbl.delete;
px_eav_tbl.delete;
px_ea_tbl.delete;
px_asset_tbl.delete;
px_vl_tbl.delete;
px_iir_tbl.delete;
SELECT csi_ii_forward_sync_temp_s.nextval
INTO l_csi_txn_rec.source_line_ref_id
FROM sys.dual;
SELECT 'Y'
INTO l_error_exists
FROM csi_txn_errors
WHERE processed_flag in ('E','R')
AND inv_material_transaction_id = all_txn.mtl_txn_id
AND rownum < 2;
SELECT customer_view_flag,
merchant_view_flag,
quantity,
object_version_number
INTO g_inst_rec.customer_view_flag,
g_inst_rec.merchant_view_flag,
g_inst_rec.quantity,
g_inst_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = g_inst_rec.instance_id;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_inst_rec,
p_party_tbl => l_pty_tbl,
p_account_tbl => l_pa_tbl,
p_org_assignments_tbl => l_ou_tbl,
p_ext_attrib_values_tbl => l_eav_tbl,
p_pricing_attrib_tbl => l_prc_tbl,
p_asset_assignment_tbl => l_asset_tbl,
p_txn_rec => l_csi_txn_rec,
x_instance_id_lst => l_inst_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_version_number
INTO l_vl_tbl(l_ind).object_version_number
FROM csi_i_version_labels
WHERE version_label_id = l_vl_tbl(l_ind).version_label_id;
csi_item_instance_pub.update_version_label(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_version_label_tbl => l_vl_tbl,
p_txn_rec => l_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_version_number
INTO l_iir_tbl(iir_ind).object_version_number
FROM csi_ii_relationships
WHERE relationship_id = l_iir_tbl(iir_ind).relationship_id;
g_iir_tbl.delete;
SELECT object_version_number
INTO l_iir_tbl(iir_ind).object_version_number
FROM csi_ii_relationships
WHERE relationship_id = l_iir_tbl(iir_ind).relationship_id;
csi_ii_relationships_pub.update_relationship (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_relationship_tbl => l_iir_tbl,
p_txn_rec => l_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE csi_ii_forward_sync_temp
SET process_flag = 'P'
WHERE instance_id = fs_rec.instance_id;
PROCEDURE insert_full_dump(p_instance_id IN NUMBER) IS
p_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_REC;
savepoint Insert_Full_Dump;
SELECT csi_transactions_s.nextval
INTO l_txn_id
FROM sys.dual;
p_party_header_tbl.DELETE;
p_account_header_tbl.DELETE;
p_org_header_tbl.DELETE;
p_pricing_attrib_tbl.DELETE;
p_ext_attrib_tbl.DELETE;
p_ext_attrib_def_tbl.DELETE;
p_asset_header_tbl.DELETE;
x_version_label_tbl.DELETE;
x_relationship_tbl.DELETE;
SELECT customer_view_flag,
merchant_view_flag,
quantity
INTO p_instance_rec.customer_view_flag,
p_instance_rec.merchant_view_flag,
p_instance_rec.quantity
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
l_instance_asset_tbl(l_ctr).update_status := p_asset_header_tbl(i).update_status;
log('Before Inserting into Instances history..');
INSERT INTO CSI_ITEM_INSTANCES_H(
INSTANCE_HISTORY_ID,
INSTANCE_ID,
TRANSACTION_ID,
NEW_INSTANCE_NUMBER,
NEW_EXTERNAL_REFERENCE,
NEW_INVENTORY_ITEM_ID,
NEW_INVENTORY_REVISION,
NEW_INV_MASTER_ORGANIZATION_ID,
NEW_SERIAL_NUMBER ,
NEW_MFG_SERIAL_NUMBER_FLAG,
NEW_LOT_NUMBER,
NEW_QUANTITY,
NEW_UNIT_OF_MEASURE,
NEW_ACCOUNTING_CLASS_CODE,
NEW_INSTANCE_CONDITION_ID,
NEW_INSTANCE_STATUS_ID,
NEW_CUSTOMER_VIEW_FLAG,
NEW_MERCHANT_VIEW_FLAG,
NEW_SELLABLE_FLAG,
NEW_SYSTEM_ID,
NEW_INSTANCE_TYPE_CODE,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_LOCATION_TYPE_CODE,
NEW_LOCATION_ID,
NEW_INV_ORGANIZATION_ID,
NEW_INV_SUBINVENTORY_NAME,
NEW_INV_LOCATOR_ID,
NEW_PA_PROJECT_ID,
NEW_PA_PROJECT_TASK_ID,
NEW_IN_TRANSIT_ORDER_LINE_ID,
NEW_WIP_JOB_ID,
NEW_PO_ORDER_LINE_ID,
NEW_COMPLETENESS_FLAG,
FULL_DUMP_FLAG,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
NEW_INST_LOC_TYPE_CODE,
NEW_INST_LOC_ID,
NEW_INST_USAGE_CODE,
NEW_last_vld_organization_id,
NEW_CONFIG_INST_REV_NUM,
NEW_CONFIG_VALID_STATUS,
NEW_INSTANCE_DESCRIPTION)
VALUES(
l_inst_hist_tbl(i),
l_inst_rec_tab.INSTANCE_ID(i),
l_txn_id,
l_inst_rec_tab.INSTANCE_NUMBER(i),
l_inst_rec_tab.EXTERNAL_REFERENCE(i),
l_inst_rec_tab.INVENTORY_ITEM_ID(i),
l_inst_rec_tab.INVENTORY_REVISION(i),
l_inst_rec_tab.INV_MASTER_ORGANIZATION_ID(i),
l_inst_rec_tab.SERIAL_NUMBER (i),
l_inst_rec_tab.MFG_SERIAL_NUMBER_FLAG(i),
l_inst_rec_tab.LOT_NUMBER(i),
l_inst_rec_tab.QUANTITY(i),
l_inst_rec_tab.UNIT_OF_MEASURE(i),
l_inst_rec_tab.ACCOUNTING_CLASS_CODE(i),
l_inst_rec_tab.INSTANCE_CONDITION_ID(i),
l_inst_rec_tab.INSTANCE_STATUS_ID(i),
l_inst_rec_tab.CUSTOMER_VIEW_FLAG(i),
l_inst_rec_tab.MERCHANT_VIEW_FLAG(i),
l_inst_rec_tab.SELLABLE_FLAG(i),
l_inst_rec_tab.SYSTEM_ID(i),
l_inst_rec_tab.INSTANCE_TYPE_CODE(i),
l_inst_rec_tab.ACTIVE_START_DATE(i),
l_inst_rec_tab.ACTIVE_END_DATE(i),
l_inst_rec_tab.LOCATION_TYPE_CODE(i),
l_inst_rec_tab.LOCATION_ID(i),
l_inst_rec_tab.INV_ORGANIZATION_ID(i),
l_inst_rec_tab.INV_SUBINVENTORY_NAME(i),
l_inst_rec_tab.INV_LOCATOR_ID(i),
l_inst_rec_tab.PA_PROJECT_ID(i),
l_inst_rec_tab.PA_PROJECT_TASK_ID(i),
l_inst_rec_tab.IN_TRANSIT_ORDER_LINE_ID(i),
l_inst_rec_tab.WIP_JOB_ID(i),
l_inst_rec_tab.PO_ORDER_LINE_ID(i),
l_inst_rec_tab.COMPLETENESS_FLAG(i),
'Y',
l_inst_rec_tab.CONTEXT(i),
l_inst_rec_tab.ATTRIBUTE1(i),
l_inst_rec_tab.ATTRIBUTE2(i),
l_inst_rec_tab.ATTRIBUTE3(i),
l_inst_rec_tab.ATTRIBUTE4(i),
l_inst_rec_tab.ATTRIBUTE5(i),
l_inst_rec_tab.ATTRIBUTE6(i),
l_inst_rec_tab.ATTRIBUTE7(i),
l_inst_rec_tab.ATTRIBUTE8(i),
l_inst_rec_tab.ATTRIBUTE9(i),
l_inst_rec_tab.ATTRIBUTE10(i),
l_inst_rec_tab.ATTRIBUTE11(i),
l_inst_rec_tab.ATTRIBUTE12(i),
l_inst_rec_tab.ATTRIBUTE13(i),
l_inst_rec_tab.ATTRIBUTE14(i),
l_inst_rec_tab.ATTRIBUTE15(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1,
l_inst_rec_tab.INSTALL_LOCATION_TYPE_CODE(i), --fix for bug4881769
l_inst_rec_tab.INSTALL_LOCATION_ID(i),
l_inst_rec_tab.INSTANCE_USAGE_CODE(i),
l_inst_rec_tab.vld_organization_id(i),
l_inst_rec_tab.CONFIG_INST_REV_NUM(i),
l_inst_rec_tab.CONFIG_VALID_STATUS(i),
l_inst_rec_tab.INSTANCE_DESCRIPTION(i));
log('Before Inserting into Version Labels history ..');
INSERT INTO CSI_I_VERSION_LABELS_H(
VERSION_LABEL_HISTORY_ID,
VERSION_LABEL_ID,
TRANSACTION_ID,
NEW_VERSION_LABEL,
NEW_DESCRIPTION,
NEW_DATE_TIME_STAMP,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_ver_label_hist_tbl(i),
l_version_label_rec_tab.VERSION_LABEL_ID(i),
l_txn_id,
l_version_label_rec_tab.VERSION_LABEL(i),
l_version_label_rec_tab.DESCRIPTION(i),
l_version_label_rec_tab.DATE_TIME_STAMP(i),
l_version_label_rec_tab.ACTIVE_START_DATE(i),
l_version_label_rec_tab.ACTIVE_END_DATE(i),
l_version_label_rec_tab.CONTEXT(i),
l_version_label_rec_tab.ATTRIBUTE1(i),
l_version_label_rec_tab.ATTRIBUTE2(i),
l_version_label_rec_tab.ATTRIBUTE3(i),
l_version_label_rec_tab.ATTRIBUTE4(i),
l_version_label_rec_tab.ATTRIBUTE5(i),
l_version_label_rec_tab.ATTRIBUTE6(i),
l_version_label_rec_tab.ATTRIBUTE7(i),
l_version_label_rec_tab.ATTRIBUTE8(i),
l_version_label_rec_tab.ATTRIBUTE9(i),
l_version_label_rec_tab.ATTRIBUTE10(i),
l_version_label_rec_tab.ATTRIBUTE11(i),
l_version_label_rec_tab.ATTRIBUTE12(i),
l_version_label_rec_tab.ATTRIBUTE13(i),
l_version_label_rec_tab.ATTRIBUTE14(i),
l_version_label_rec_tab.ATTRIBUTE15(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before inserting into Parties history..');
INSERT INTO CSI_I_PARTIES_H(
INSTANCE_PARTY_HISTORY_ID,
INSTANCE_PARTY_ID,
TRANSACTION_ID,
NEW_PARTY_SOURCE_TABLE,
NEW_PARTY_ID,
NEW_RELATIONSHIP_TYPE_CODE,
NEW_CONTACT_FLAG,
NEW_CONTACT_IP_ID,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
NEW_PRIMARY_FLAG,
NEW_PREFERRED_FLAG,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_party_hist_tbl(i),
l_party_rec_tab.INSTANCE_PARTY_ID(i),
l_txn_id,
l_party_rec_tab.PARTY_SOURCE_TABLE(i),
l_party_rec_tab.PARTY_ID(i),
l_party_rec_tab.RELATIONSHIP_TYPE_CODE(i),
l_party_rec_tab.CONTACT_FLAG(i),
l_party_rec_tab.CONTACT_IP_ID(i),
l_party_rec_tab.ACTIVE_START_DATE(i),
l_party_rec_tab.ACTIVE_END_DATE(i),
l_party_rec_tab.CONTEXT(i),
l_party_rec_tab.ATTRIBUTE1(i),
l_party_rec_tab.ATTRIBUTE2(i),
l_party_rec_tab.ATTRIBUTE3(i),
l_party_rec_tab.ATTRIBUTE4(i),
l_party_rec_tab.ATTRIBUTE5(i),
l_party_rec_tab.ATTRIBUTE6(i),
l_party_rec_tab.ATTRIBUTE7(i),
l_party_rec_tab.ATTRIBUTE8(i),
l_party_rec_tab.ATTRIBUTE9(i),
l_party_rec_tab.ATTRIBUTE10(i),
l_party_rec_tab.ATTRIBUTE11(i),
l_party_rec_tab.ATTRIBUTE12(i),
l_party_rec_tab.ATTRIBUTE13(i),
l_party_rec_tab.ATTRIBUTE14(i),
l_party_rec_tab.ATTRIBUTE15(i),
l_party_rec_tab.PRIMARY_FLAG(i),
l_party_rec_tab.PREFERRED_FLAG(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before Inserting into Party Accounts history');
INSERT INTO CSI_IP_ACCOUNTS_H(
IP_ACCOUNT_HISTORY_ID,
IP_ACCOUNT_ID,
TRANSACTION_ID,
NEW_PARTY_ACCOUNT_ID,
NEW_RELATIONSHIP_TYPE_CODE,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
NEW_BILL_TO_ADDRESS,
NEW_SHIP_TO_ADDRESS,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_account_hist_tbl(i),
l_account_rec_tab.IP_ACCOUNT_ID(i),
l_txn_id,
l_account_rec_tab.PARTY_ACCOUNT_ID(i),
l_account_rec_tab.RELATIONSHIP_TYPE_CODE(i),
l_account_rec_tab.ACTIVE_START_DATE(i),
l_account_rec_tab.ACTIVE_END_DATE(i),
l_account_rec_tab.CONTEXT(i),
l_account_rec_tab.ATTRIBUTE1(i),
l_account_rec_tab.ATTRIBUTE2(i),
l_account_rec_tab.ATTRIBUTE3(i),
l_account_rec_tab.ATTRIBUTE4(i),
l_account_rec_tab.ATTRIBUTE5(i),
l_account_rec_tab.ATTRIBUTE6(i),
l_account_rec_tab.ATTRIBUTE7(i),
l_account_rec_tab.ATTRIBUTE8(i),
l_account_rec_tab.ATTRIBUTE9(i),
l_account_rec_tab.ATTRIBUTE10(i),
l_account_rec_tab.ATTRIBUTE11(i),
l_account_rec_tab.ATTRIBUTE12(i),
l_account_rec_tab.ATTRIBUTE13(i),
l_account_rec_tab.ATTRIBUTE14(i),
l_account_rec_tab.ATTRIBUTE15(i),
l_account_rec_tab.BILL_TO_ADDRESS(i),
l_account_rec_tab.SHIP_TO_ADDRESS(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before Inserting into Org Assignments history');
INSERT INTO CSI_I_ORG_ASSIGNMENTS_H(
INSTANCE_OU_HISTORY_ID,
INSTANCE_OU_ID,
TRANSACTION_ID,
NEW_OPERATING_UNIT_ID,
NEW_RELATIONSHIP_TYPE_CODE,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_org_hist_tbl(i),
l_org_units_rec_tab.INSTANCE_OU_ID(i),
l_txn_id,
l_org_units_rec_tab.OPERATING_UNIT_ID(i),
l_org_units_rec_tab.RELATIONSHIP_TYPE_CODE(i),
l_org_units_rec_tab.ACTIVE_START_DATE(i),
l_org_units_rec_tab.ACTIVE_END_DATE(i),
l_org_units_rec_tab.CONTEXT(i),
l_org_units_rec_tab.ATTRIBUTE1(i),
l_org_units_rec_tab.ATTRIBUTE2(i),
l_org_units_rec_tab.ATTRIBUTE3(i),
l_org_units_rec_tab.ATTRIBUTE4(i),
l_org_units_rec_tab.ATTRIBUTE5(i),
l_org_units_rec_tab.ATTRIBUTE6(i),
l_org_units_rec_tab.ATTRIBUTE7(i),
l_org_units_rec_tab.ATTRIBUTE8(i),
l_org_units_rec_tab.ATTRIBUTE9(i),
l_org_units_rec_tab.ATTRIBUTE10(i),
l_org_units_rec_tab.ATTRIBUTE11(i),
l_org_units_rec_tab.ATTRIBUTE12(i),
l_org_units_rec_tab.ATTRIBUTE13(i),
l_org_units_rec_tab.ATTRIBUTE14(i),
l_org_units_rec_tab.ATTRIBUTE15(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before Inserting into Pricing Attribs history');
INSERT INTO CSI_I_PRICING_ATTRIBS_H(
PRICE_ATTRIB_HISTORY_ID,
PRICING_ATTRIBUTE_ID,
TRANSACTION_ID,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
NEW_PRICING_CONTEXT,
NEW_PRICING_ATTRIBUTE1,
NEW_PRICING_ATTRIBUTE2,
NEW_PRICING_ATTRIBUTE3,
NEW_PRICING_ATTRIBUTE4,
NEW_PRICING_ATTRIBUTE5,
NEW_PRICING_ATTRIBUTE6,
NEW_PRICING_ATTRIBUTE7,
NEW_PRICING_ATTRIBUTE8,
NEW_PRICING_ATTRIBUTE9,
NEW_PRICING_ATTRIBUTE10,
NEW_PRICING_ATTRIBUTE11,
NEW_PRICING_ATTRIBUTE12,
NEW_PRICING_ATTRIBUTE13,
NEW_PRICING_ATTRIBUTE14,
NEW_PRICING_ATTRIBUTE15,
NEW_PRICING_ATTRIBUTE16,
NEW_PRICING_ATTRIBUTE17,
NEW_PRICING_ATTRIBUTE18,
NEW_PRICING_ATTRIBUTE19,
NEW_PRICING_ATTRIBUTE20,
NEW_PRICING_ATTRIBUTE21,
NEW_PRICING_ATTRIBUTE22,
NEW_PRICING_ATTRIBUTE23,
NEW_PRICING_ATTRIBUTE24,
NEW_PRICING_ATTRIBUTE25,
NEW_PRICING_ATTRIBUTE26,
NEW_PRICING_ATTRIBUTE27,
NEW_PRICING_ATTRIBUTE28,
NEW_PRICING_ATTRIBUTE29,
NEW_PRICING_ATTRIBUTE30,
NEW_PRICING_ATTRIBUTE31,
NEW_PRICING_ATTRIBUTE32,
NEW_PRICING_ATTRIBUTE33,
NEW_PRICING_ATTRIBUTE34,
NEW_PRICING_ATTRIBUTE35,
NEW_PRICING_ATTRIBUTE36,
NEW_PRICING_ATTRIBUTE37,
NEW_PRICING_ATTRIBUTE38,
NEW_PRICING_ATTRIBUTE39,
NEW_PRICING_ATTRIBUTE40,
NEW_PRICING_ATTRIBUTE41,
NEW_PRICING_ATTRIBUTE42,
NEW_PRICING_ATTRIBUTE43,
NEW_PRICING_ATTRIBUTE44,
NEW_PRICING_ATTRIBUTE45,
NEW_PRICING_ATTRIBUTE46,
NEW_PRICING_ATTRIBUTE47,
NEW_PRICING_ATTRIBUTE48,
NEW_PRICING_ATTRIBUTE49,
NEW_PRICING_ATTRIBUTE50,
NEW_PRICING_ATTRIBUTE51,
NEW_PRICING_ATTRIBUTE52,
NEW_PRICING_ATTRIBUTE53,
NEW_PRICING_ATTRIBUTE54,
NEW_PRICING_ATTRIBUTE55,
NEW_PRICING_ATTRIBUTE56,
NEW_PRICING_ATTRIBUTE57,
NEW_PRICING_ATTRIBUTE58,
NEW_PRICING_ATTRIBUTE59,
NEW_PRICING_ATTRIBUTE60,
NEW_PRICING_ATTRIBUTE61,
NEW_PRICING_ATTRIBUTE62,
NEW_PRICING_ATTRIBUTE63,
NEW_PRICING_ATTRIBUTE64,
NEW_PRICING_ATTRIBUTE65,
NEW_PRICING_ATTRIBUTE66,
NEW_PRICING_ATTRIBUTE67,
NEW_PRICING_ATTRIBUTE68,
NEW_PRICING_ATTRIBUTE69,
NEW_PRICING_ATTRIBUTE70,
NEW_PRICING_ATTRIBUTE71,
NEW_PRICING_ATTRIBUTE72,
NEW_PRICING_ATTRIBUTE73,
NEW_PRICING_ATTRIBUTE74,
NEW_PRICING_ATTRIBUTE75,
NEW_PRICING_ATTRIBUTE76,
NEW_PRICING_ATTRIBUTE77,
NEW_PRICING_ATTRIBUTE78,
NEW_PRICING_ATTRIBUTE79,
NEW_PRICING_ATTRIBUTE80,
NEW_PRICING_ATTRIBUTE81,
NEW_PRICING_ATTRIBUTE82,
NEW_PRICING_ATTRIBUTE83,
NEW_PRICING_ATTRIBUTE84,
NEW_PRICING_ATTRIBUTE85,
NEW_PRICING_ATTRIBUTE86,
NEW_PRICING_ATTRIBUTE87,
NEW_PRICING_ATTRIBUTE88,
NEW_PRICING_ATTRIBUTE89,
NEW_PRICING_ATTRIBUTE90,
NEW_PRICING_ATTRIBUTE91,
NEW_PRICING_ATTRIBUTE92,
NEW_PRICING_ATTRIBUTE93,
NEW_PRICING_ATTRIBUTE94,
NEW_PRICING_ATTRIBUTE95,
NEW_PRICING_ATTRIBUTE96,
NEW_PRICING_ATTRIBUTE97,
NEW_PRICING_ATTRIBUTE98,
NEW_PRICING_ATTRIBUTE99,
NEW_PRICING_ATTRIBUTE100,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_pricing_hist_tbl(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE_ID(i),
l_txn_id,
l_pricing_rec_tab.ACTIVE_START_DATE(i),
l_pricing_rec_tab.ACTIVE_END_DATE(i),
l_pricing_rec_tab.CONTEXT(i),
l_pricing_rec_tab.ATTRIBUTE1(i),
l_pricing_rec_tab.ATTRIBUTE2(i),
l_pricing_rec_tab.ATTRIBUTE3(i),
l_pricing_rec_tab.ATTRIBUTE4(i),
l_pricing_rec_tab.ATTRIBUTE5(i),
l_pricing_rec_tab.ATTRIBUTE6(i),
l_pricing_rec_tab.ATTRIBUTE7(i),
l_pricing_rec_tab.ATTRIBUTE8(i),
l_pricing_rec_tab.ATTRIBUTE9(i),
l_pricing_rec_tab.ATTRIBUTE10(i),
l_pricing_rec_tab.ATTRIBUTE11(i),
l_pricing_rec_tab.ATTRIBUTE12(i),
l_pricing_rec_tab.ATTRIBUTE13(i),
l_pricing_rec_tab.ATTRIBUTE14(i),
l_pricing_rec_tab.ATTRIBUTE15(i),
l_pricing_rec_tab.PRICING_CONTEXT(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE1(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE2(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE3(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE4(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE5(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE6(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE7(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE8(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE9(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE10(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE11(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE12(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE13(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE14(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE15(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE16(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE17(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE18(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE19(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE20(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE21(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE22(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE23(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE24(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE25(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE26(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE27(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE28(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE29(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE30(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE31(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE32(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE33(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE34(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE35(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE36(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE37(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE38(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE39(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE40(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE41(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE42(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE43(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE44(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE45(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE46(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE47(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE48(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE49(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE50(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE51(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE52(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE53(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE54(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE55(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE56(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE57(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE58(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE59(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE60(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE61(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE62(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE63(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE64(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE65(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE66(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE67(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE68(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE69(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE70(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE71(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE72(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE73(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE74(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE75(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE76(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE77(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE78(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE79(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE80(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE81(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE82(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE83(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE84(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE85(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE86(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE87(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE88(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE89(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE90(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE91(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE92(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE93(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE94(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE95(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE96(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE97(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE98(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE99(i),
l_pricing_rec_tab.PRICING_ATTRIBUTE100(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before Inserting into Ext Attribs history');
INSERT INTO CSI_IEA_VALUES_H(
ATTRIBUTE_VALUE_HISTORY_ID,
ATTRIBUTE_VALUE_ID,
TRANSACTION_ID,
NEW_ATTRIBUTE_VALUE,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
NEW_CONTEXT,
NEW_ATTRIBUTE1,
NEW_ATTRIBUTE2,
NEW_ATTRIBUTE3,
NEW_ATTRIBUTE4,
NEW_ATTRIBUTE5,
NEW_ATTRIBUTE6,
NEW_ATTRIBUTE7,
NEW_ATTRIBUTE8,
NEW_ATTRIBUTE9,
NEW_ATTRIBUTE10,
NEW_ATTRIBUTE11,
NEW_ATTRIBUTE12,
NEW_ATTRIBUTE13,
NEW_ATTRIBUTE14,
NEW_ATTRIBUTE15,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_ext_hist_tbl(i),
l_ext_attr_rec_tab.ATTRIBUTE_VALUE_ID(i),
l_txn_id,
l_ext_attr_rec_tab.ATTRIBUTE_VALUE(i),
l_ext_attr_rec_tab.ACTIVE_START_DATE(i),
l_ext_attr_rec_tab.ACTIVE_END_DATE(i),
l_ext_attr_rec_tab.CONTEXT(i),
l_ext_attr_rec_tab.ATTRIBUTE1(i),
l_ext_attr_rec_tab.ATTRIBUTE2(i),
l_ext_attr_rec_tab.ATTRIBUTE3(i),
l_ext_attr_rec_tab.ATTRIBUTE4(i),
l_ext_attr_rec_tab.ATTRIBUTE5(i),
l_ext_attr_rec_tab.ATTRIBUTE6(i),
l_ext_attr_rec_tab.ATTRIBUTE7(i),
l_ext_attr_rec_tab.ATTRIBUTE8(i),
l_ext_attr_rec_tab.ATTRIBUTE9(i),
l_ext_attr_rec_tab.ATTRIBUTE10(i),
l_ext_attr_rec_tab.ATTRIBUTE11(i),
l_ext_attr_rec_tab.ATTRIBUTE12(i),
l_ext_attr_rec_tab.ATTRIBUTE13(i),
l_ext_attr_rec_tab.ATTRIBUTE14(i),
l_ext_attr_rec_tab.ATTRIBUTE15(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before Inserting into Assets history');
INSERT INTO CSI_I_ASSETS_H(
INSTANCE_ASSET_HISTORY_ID,
INSTANCE_ASSET_ID,
TRANSACTION_ID,
NEW_INSTANCE_ID,
NEW_FA_ASSET_ID,
NEW_ASSET_QUANTITY,
NEW_FA_BOOK_TYPE_CODE,
NEW_FA_LOCATION_ID,
NEW_UPDATE_STATUS,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
FULL_DUMP_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(
l_asset_hist_tbl(i),
l_asset_rec_tab.INSTANCE_ASSET_ID(i),
l_txn_id,
l_asset_rec_tab.INSTANCE_ID(i),
l_asset_rec_tab.FA_ASSET_ID(i),
l_asset_rec_tab.ASSET_QUANTITY(i),
l_asset_rec_tab.FA_BOOK_TYPE_CODE(i),
l_asset_rec_tab.FA_LOCATION_ID(i),
l_asset_rec_tab.UPDATE_STATUS(i),
l_asset_rec_tab.ACTIVE_START_DATE(i),
l_asset_rec_tab.ACTIVE_END_DATE(i),
'Y',
l_user_id,
sysdate,
l_user_id,
sysdate,
-1,
1);
log('Before inserting into Relationships history');
INSERT INTO CSI_II_RELATIONSHIPS_H(
RELATIONSHIP_HISTORY_ID
,RELATIONSHIP_ID
,TRANSACTION_ID
,NEW_SUBJECT_ID
,NEW_POSITION_REFERENCE
,NEW_ACTIVE_START_DATE
,NEW_ACTIVE_END_DATE
,NEW_MANDATORY_FLAG
,NEW_CONTEXT
,NEW_ATTRIBUTE1
,NEW_ATTRIBUTE2
,NEW_ATTRIBUTE3
,NEW_ATTRIBUTE4
,NEW_ATTRIBUTE5
,NEW_ATTRIBUTE6
,NEW_ATTRIBUTE7
,NEW_ATTRIBUTE8
,NEW_ATTRIBUTE9
,NEW_ATTRIBUTE10
,NEW_ATTRIBUTE11
,NEW_ATTRIBUTE12
,NEW_ATTRIBUTE13
,NEW_ATTRIBUTE14
,NEW_ATTRIBUTE15
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES(
l_rel_hist_tbl(i)
,l_ii_relationship_rec_tab.RELATIONSHIP_ID(i)
,l_txn_id
,l_ii_relationship_rec_tab.SUBJECT_ID(i)
,l_ii_relationship_rec_tab.POSITION_REFERENCE(i)
,l_ii_relationship_rec_tab.ACTIVE_START_DATE(i)
,l_ii_relationship_rec_tab.ACTIVE_END_DATE(i)
,l_ii_relationship_rec_tab.MANDATORY_FLAG(i)
,l_ii_relationship_rec_tab.CONTEXT(i)
,l_ii_relationship_rec_tab.ATTRIBUTE1(i)
,l_ii_relationship_rec_tab.ATTRIBUTE2(i)
,l_ii_relationship_rec_tab.ATTRIBUTE3(i)
,l_ii_relationship_rec_tab.ATTRIBUTE4(i)
,l_ii_relationship_rec_tab.ATTRIBUTE5(i)
,l_ii_relationship_rec_tab.ATTRIBUTE6(i)
,l_ii_relationship_rec_tab.ATTRIBUTE7(i)
,l_ii_relationship_rec_tab.ATTRIBUTE8(i)
,l_ii_relationship_rec_tab.ATTRIBUTE9(i)
,l_ii_relationship_rec_tab.ATTRIBUTE10(i)
,l_ii_relationship_rec_tab.ATTRIBUTE11(i)
,l_ii_relationship_rec_tab.ATTRIBUTE12(i)
,l_ii_relationship_rec_tab.ATTRIBUTE13(i)
,l_ii_relationship_rec_tab.ATTRIBUTE14(i)
,l_ii_relationship_rec_tab.ATTRIBUTE15(i)
,'Y'
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES(
l_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'Full Dump' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1);
log('Insert_Full_Dump Successfully completed...');
log('Comp error in Insert_Full_Dump..');
ROLLBACK TO Insert_Full_Dump;
ROLLBACK TO Insert_Full_Dump;
END Insert_Full_Dump;
SELECT application_id, concurrent_queue_id
INTO l_applid, l_managerid
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'XDP_Q_EVENT_SVC';
l_error_rec.error_stage := 'IB_UPDATE';
SELECT msg_id,
msg_code,
msg_status,
body_text,
creation_date,
description
FROM xnp_msgs
WHERE (msg_code like 'CSI%' OR msg_code like 'CSE%')
AND recipient_name is null
AND msg_status IN ('READY', 'FAILED', 'REJECTED')
AND msg_creation_date > p_freeze_date;
INSERT INTO csi_xnp_msgs_temp(
msg_id,
msg_code,
msg_text,
msg_status,
source_id,
source_type,
creation_date,
description,
process_flag)
VALUES(
msg_rec.msg_id,
msg_rec.msg_code,
msg_rec.body_text, --l_msg_text, --Changed for Bug 13384784,
msg_rec.msg_status,
l_source_id,
l_source_type,
msg_rec.creation_date,
msg_rec.description,
'Y');
SELECT msg_id,
msg_code,
msg_text,
msg_status,
source_id,
source_type,
creation_date,
description,
process_flag
FROM csi_xnp_msgs_temp
WHERE source_type = 'MTL_TRANSACTION_ID'
AND source_id is not null;
SELECT fnd_Profile.value('csi_upgrading_from_release')
INTO l_release
FROM sys.dual;
SELECT mmt.transaction_id,
mmt.transaction_type_id ,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mtt.type_class,
mmt.transaction_quantity
INTO l_mtl_txn_id,
l_mtl_type_id,
l_mtl_action_id,
l_mtl_source_type_id,
l_mtl_type_class,
l_mtl_txn_qty
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_id = q_rec.source_id
AND mtt.transaction_type_id = mmt.transaction_type_id;
UPDATE xnp_msgs
SET msg_status = 'PROCESSED'
WHERE msg_id = q_rec.msg_id;
SELECT transaction_id mtl_txn_id,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_source_type_id,
mmt.transaction_source_id mtl_source_id,
mmt.transaction_type_id mtl_type_id,
mtt.type_class mtl_type_class,
mmt.transaction_quantity mtl_txn_qty
FROM mtl_system_items msi,
mtl_transaction_types mtt,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (1, 27, 31, 32, 33, 34)
AND mmt.transaction_date > p_freeze_date
AND mmt.transaction_source_id = pc_wip_job_id
AND mmt.creation_date < pc_mtl_creation_date
AND mtt.transaction_type_id = mmt.transaction_type_id
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y';
l_missing_tbl.delete;
SELECT fnd_Profile.value('CSI_UPGRADING_FROM_RELEASE')
INTO l_release
FROM sys.dual;
SELECT 'Y' INTO l_processed_flag
FROM sys.dual
WHERE exists(
SELECT '1' FROM csi_transactions
WHERE inv_material_transaction_id = wip_txn_rec.mtl_txn_id);
SELECT 'Y' INTO l_txn_error_flag
FROM sys.dual
WHERE EXISTS (
SELECT '1' FROM csi_txn_errors
WHERE inv_material_transaction_id = wip_txn_rec.mtl_txn_id
AND processed_flag in ('E', 'R'));
SELECT 'Y' INTO l_pending_msg_found
FROM sys.dual
WHERE exists (
SELECT '1'
FROM csi_xnp_msgs_temp
WHERE source_type = 'MTL_TRANSACTION_ID'
AND source_id = wip_txn_rec.mtl_txn_id
AND nvl(msg_status, 'READY') <> 'PROCESSED');
l_missing_tbl.delete;
SELECT fnd_Profile.value('csi_upgrading_from_release')
INTO l_release
FROM sys.dual;
SELECT 'Y' INTO l_processed_flag
FROM sys.dual
WHERE exists(
SELECT '1' FROM csi_transactions
WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
OR
inv_material_transaction_id = all_txn_rec.mtl_xfer_txn_id));
SELECT 'Y' INTO l_processed_flag
FROM sys.dual
WHERE exists(
SELECT '1' FROM csi_transactions
WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id);
SELECT 'Y' INTO l_txn_error_flag
FROM sys.dual
WHERE EXISTS (
SELECT '1' FROM csi_txn_errors
WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
OR
inv_material_transaction_id = all_txn_rec.mtl_xfer_txn_id)
AND processed_flag in ('E', 'R'));
SELECT 'Y' INTO l_txn_error_flag
FROM sys.dual
WHERE EXISTS (
SELECT '1' FROM csi_txn_errors
WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id
AND processed_flag in ('E', 'R'));
SELECT 'Y' INTO l_pending_msg_found
FROM sys.dual
WHERE exists (
SELECT '1'
FROM csi_xnp_msgs_temp
WHERE source_type = 'MTL_TRANSACTION_ID'
AND source_id = all_txn_rec.mtl_txn_id
AND nvl(msg_status, 'READY') <> 'PROCESSED');
SELECT msn.serial_number serial_number,
msn.inventory_item_id item_id,
msn.current_organization_id organization_id
FROM mtl_serial_numbers msn
WHERE exists (
SELECT '1'
FROM mtl_system_items msi,
mtl_parameters mp
WHERE mp.organization_id = msn.current_organization_id
AND msi.organization_id = mp.master_organization_id
AND msi.inventory_item_id = msn.inventory_item_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y');
SELECT serial_number_control_code,
lot_control_code
INTO l_serial_code,
l_lot_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id_tab(ind)
AND organization_id = l_organization_id_tab(ind);
SELECT tld.instance_id,
tld.serial_number,
tld.inventory_item_id,
tld.inv_organization_id
FROM csi_t_txn_line_details tld,
csi_t_transaction_lines tl
WHERE tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
AND tl.source_transaction_id = p_order_line_id
AND tld.transaction_line_id = tl.transaction_line_id
AND tld.instance_id is not null;
SELECT inventory_item_id,
organization_id,
transaction_source_type_id,
transaction_action_id,
transaction_source_id,
trx_source_line_id,
transaction_date,
creation_date
INTO l_inv_item_id,
l_inv_org_id,
l_mtl_source_type_id,
l_mtl_action_id,
l_wip_job_id,
l_order_line_id,
l_err_txn_date,
l_mtl_creation_date
FROM mtl_material_transactions
WHERE transaction_id = l_mtl_txn_id_tab(ind);
SELECT serial_number_control_code,
lot_control_code
INTO l_serial_code,
l_lot_code
FROM mtl_system_items
WHERE inventory_item_id = l_inv_item_id
AND organization_id = l_inv_org_id;
SELECT serial_number,
inventory_item_id,
last_vld_organization_id
INTO l_inst_serial_number,
l_inst_item_id,
l_inst_vld_org_id
FROM csi_item_instances
WHERE instance_id = tld_inst_rec.instance_id;
SELECT serial_number_control_code,
lot_control_code
INTO l_serial_code,
l_lot_code
FROM mtl_system_items
WHERE inventory_item_id = l_inst_item_id
AND organization_id = l_inst_vld_org_id;
SELECT cte.inv_material_transaction_id mtl_txn_id,
cte.transaction_error_id txn_error_id
FROM csi_txn_errors cte
WHERE cte.inv_material_transaction_id = pc_mtl_txn_id
AND cte.processed_flag in ('E', 'R');
l_already_inserted varchar2(1);
skip_insert exception;
SELECT fnd_Profile.value('csi_upgrading_from_release')
INTO l_release
FROM sys.dual;
SELECT inventory_item_id,
organization_id,
transaction_source_type_id,
transaction_action_id,
transaction_date
INTO l_inv_item_id,
l_inv_org_id,
l_err_txn_source_type_id,
l_err_txn_action_id,
l_err_txn_date
FROM mtl_material_transactions
WHERE transaction_id = l_mtl_txn_id_tab(ind);
UPDATE csi_txn_errors
SET processed_flag = 'D',
error_text = 'Transaction prior to freeze_date in install parameter',
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE transaction_error_id = l_txn_error_id_tab(ind);
SELECT serial_number_control_code,
lot_control_code
INTO l_err_txn_serial_code,
l_err_txn_lot_code
FROM mtl_system_items
WHERE inventory_item_id = l_inv_item_id
AND organization_id = l_inv_org_id;
SELECT instance_id ,
last_vld_organization_id,
owner_party_id,
owner_party_account_id,
location_type_code,
instance_usage_code,
active_end_date,
inv_organization_id,
inv_subinventory_name,
inventory_revision,
nvl(migrated_flag, 'N')
INTO l_inst_id,
l_inst_vld_org_id,
l_inst_owner_pty_id,
l_inst_owner_acct_id,
l_inst_location,
l_inst_usage_code,
l_inst_end_date,
l_inst_org_id,
l_inst_subinv_name,
l_inst_rev_num,
l_inst_mig_flag
FROM csi_item_instances
WHERE inventory_item_id = srl_rec.item_id
AND serial_number = srl_rec.serial_number;
SELECT instance_party_id
INTO l_owner_ip_id
FROM csi_i_parties
WHERE instance_id = l_inst_id
AND relationship_type_code = 'OWNER'
AND rownum = 1;
SELECT ip_account_id
INTO l_owner_ipa_id
FROM csi_ip_accounts
WHERE instance_party_id = l_owner_ip_id
AND relationship_type_code = 'OWNER';
raise skip_insert;
SELECT cte.transaction_type_id,
cte.error_text
INTO l_csi_txn_type_id,
l_error
FROM csi_txn_errors cte
WHERE (cte.inv_material_transaction_id = txn_rec.mtl_txn_id
OR
cte.inv_material_transaction_id = txn_rec.mtl_xfer_txn_id)
AND cte.processed_flag in ('E', 'R')
AND rownum = 1;
SELECT cte.transaction_type_id,
cte.error_text
INTO l_csi_txn_type_id,
l_error
FROM csi_txn_errors cte
WHERE cte.inv_material_transaction_id = txn_rec.mtl_txn_id
AND cte.processed_flag in ('E', 'R')
AND rownum = 1;
SELECT transaction_id,
transaction_type_id
INTO l_csi_txn_id,
l_csi_txn_type_id
FROM csi_transactions
WHERE (inv_material_transaction_id = txn_rec.mtl_txn_id
OR
inv_material_transaction_id = txn_rec.mtl_xfer_txn_id)
AND rownum = 1;
SELECT transaction_id,
transaction_type_id
INTO l_csi_txn_id,
l_csi_txn_type_id
FROM csi_transactions
WHERE inv_material_transaction_id = txn_rec.mtl_txn_id
AND rownum = 1;
SELECT 'Y' INTO l_already_inserted
FROM csi_diagnostics_temp
WHERE serial_number = srl_rec.serial_number
AND inventory_item_id = srl_rec.item_id
AND mtl_txn_id = txn_rec.mtl_txn_id;
SELECT serial_number_control_code,
lot_control_code ,
revision_qty_control_code
INTO l_serial_code,
l_lot_code,
l_revision_code
FROM mtl_system_items
WHERE inventory_item_id = txn_rec.item_id
AND organization_id = txn_rec.organization_id;
INSERT INTO csi_diagnostics_temp(
diag_seq_id,
serial_number,
lot_number,
serial_control_code,
lot_control_code,
revision_control_code,
mtl_creation_date,
mtl_txn_date,
mtl_txn_id,
mtl_txn_name,
mtl_txn_qty,
mtl_xfer_txn_id,
mtl_item_revision,
inventory_item_id,
organization_id,
error_flag,
error_text,
mtl_type_id,
mtl_action_id,
mtl_src_type_id,
mtl_type_class,
source_type,
csi_txn_type_id,
csi_txn_id,
oe_order_line_id,
wip_job_id,
oe_rma_line_id,
instance_id,
instance_vld_organization_id,
instance_location,
instance_usage_code,
instance_organization_id,
instance_subinv_name,
instance_revision,
instance_end_date,
instance_owner_party_id,
instance_owner_account_id,
instance_mig_flag,
internal_party_id,
last_transaction_flag,
create_flag,
process_flag,
temporary_message)
VALUES(
l_diag_seq_id,
srl_rec.serial_number,
txn_rec.lot_number,
l_serial_code,
l_lot_code,
l_revision_code,
txn_rec.mtl_creation_date,
txn_rec.mtl_txn_date,
txn_rec.mtl_txn_id,
txn_rec.mtl_txn_name,
txn_rec.mtl_txn_qty,
txn_rec.mtl_xfer_txn_id,
txn_rec.mtl_revision,
srl_rec.item_id,
txn_rec.organization_id,
l_error_flag,
l_error,
txn_rec.mtl_type_id,
txn_rec.mtl_action_id,
txn_rec.mtl_source_type_id,
txn_rec.mtl_type_class,
l_source_type,
l_csi_txn_type_id,
l_csi_txn_id,
l_order_line_id,
l_wip_job_id,
l_rma_line_id,
l_inst_id,
l_inst_vld_org_id,
l_inst_location,
l_inst_usage_code,
l_inst_org_id,
l_inst_subinv_name,
l_inst_rev_num,
l_inst_end_date,
l_inst_owner_pty_id,
l_inst_owner_acct_id,
l_inst_mig_flag,
l_internal_party_id,
l_last_txn_flag,
l_create_flag,
l_process_flag,
l_temp_message);
WHEN skip_insert THEN
null;
UPDATE csi_txn_errors
SET processed_flag = 'D',
error_text = 'Serial control is now inappropriate for this txn. Knocking this',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE inv_material_transaction_id = l_mtl_txn_id_tab(ind)
AND processed_flag in ('E', 'R');
DELETE FROM csi_diagnostics_temp
WHERE mtl_txn_id = l_mtl_txn_id_tab(ind)
AND error_flag = 'E';
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'SOISHIP',
expire_flag = l_expire_flag,
temporary_message = 'Updating the srlsoi instance with returned RMA info.'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'SOISHIP',
temporary_message = 'Creating/Updating a non serial instance with staging info'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'SHIP',
temporary_message = 'Creating/Updating the instance with staging info'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'RMA',
temporary_message = 'Just mark error transaction to be re-processed'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'WIPISSUE',
temporary_message = 'Stamp the inv location from serial and mark it FOR processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'WIPRETURN',
temporary_message = 'Make it a WIP instance and allow re-processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'WIPCOMPL',
temporary_message = 'Mark the error FOR re-processing.'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'MISCRCPT',
temporary_message = 'Stamp the INV location from serial'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'MISCISSUE',
temporary_message = 'Stamp the INV location from serial'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'SIXFER',
temporary_message = 'Stamp the inv location from serial and mark it FOR processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'PROJRCPT',
temporary_message = 'Stamp the project location from serial and mark it for processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'IORGRCPT',
temporary_message = 'Stamp the intransit location from serial and mark it for processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'ISORCPT',
temporary_message = 'Stamp the intransit location from serial and mark it for processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'M',
process_code = 'INTRSHIP',
temporary_message = 'Stamp the inv location from serial and mark it for processing'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'R',
temporary_message = 'Marking this for re-process.'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'R',
temporary_message = 'Marking it for re-process'
WHERE diag_seq_id = p_diag_txn_tbl(l_ind).diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'X',
temporary_message = 'Succeeding txn processed in IB for this serial'
WHERE diag_seq_id = p_diag_txn_tbl(l_ind).diag_seq_id;
SELECT rowid row_id
FROM csi_diagnostics_temp;
UPDATE csi_diagnostics_temp
SET process_flag = 'N',
process_code = null,
entangled_flag = null,
expire_flag = null,
temporary_message = null
WHERE rowid = diag_rec.row_id;
SELECT distinct mtl_creation_date, mtl_txn_id
FROM csi_diagnostics_temp
WHERE nvl(process_flag, 'N') = 'M'
ORDER BY mtl_creation_date asc, mtl_txn_id asc;
SELECT diag_seq_id,
serial_number,
inventory_item_id,
instance_id,
lot_control_code,
process_flag
FROM csi_diagnostics_temp
WHERE mtl_txn_id = p_mtl_txn_id
AND nvl(process_flag, 'N') <> 'M';
SELECT diag_seq_id
FROM csi_diagnostics_temp
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id
AND mtl_creation_date < p_mtl_creation_date
AND nvl(process_flag,'N') = 'M';
SELECT diag_seq_id
FROM csi_diagnostics_temp
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id
AND mtl_creation_date > p_mtl_creation_date
AND nvl(process_flag,'N') = 'M'
ORDER by mtl_creation_date asc, mtl_txn_id asc;
SELECT diag_seq_id,
mtl_txn_id,
csi_txn_id,
mtl_txn_date
FROM csi_diagnostics_temp
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id
AND mtl_creation_date < p_mtl_creation_date
ORDER BY mtl_creation_date desc, mtl_txn_id desc;
FOR m_rec IN m_cur -- read all the distinct inv txn records that are marked for update
LOOP
BEGIN
-- check if this txn is still there to be processed in the loop
BEGIN
SELECT process_code
INTO l_process_code
FROM csi_diagnostics_temp
WHERE mtl_txn_id = m_rec.mtl_txn_id
AND nvl(process_flag,'N') = 'M' -- marked for update
AND rownum = 1;
UPDATE csi_diagnostics_temp
SET process_flag = 'N',
temporary_message = 'updating for entanglement processing'
WHERE diag_seq_id = m_srl_rec.diag_seq_id;
UPDATE csi_diagnostics_temp
SET process_code = l_process_code,
process_flag = 'M',
temporary_message = 'updating for entanglement processing'
WHERE diag_seq_id = m_srl_rec.diag_seq_id;
SELECT nvl(min(diag_seq_id), -999999)
INTO l_p_diag_seq_id
FROM csi_diagnostics_temp
WHERE serial_number = m_srl_rec.serial_number
AND inventory_item_id = m_srl_rec.inventory_item_id
AND mtl_creation_date > m_rec.mtl_creation_date
AND csi_txn_id is not null;
SELECT mtl_txn_id,
mtl_creation_date
INTO l_p_mtl_txn_id,
l_p_mtl_creation_date
FROM csi_diagnostics_temp
WHERE diag_seq_id = l_p_diag_seq_id;
SELECT csi_txn_id
INTO l_p_csi_txn_id
FROM csi_diagnostics_temp
WHERE serial_number = m_srl_rec.serial_number
AND inventory_item_id = m_srl_rec.inventory_item_id
AND mtl_txn_id = l_p_mtl_txn_id;
SELECT process_flag,
date_time_stamp
INTO l_process_flag,
l_current_date
FROM csi_ii_forward_sync_temp
WHERE instance_id = m_srl_rec.instance_id;
Insert_Full_Dump(p_instance_id => m_srl_rec.instance_id);
UPDATE csi_ii_forward_sync_temp
SET date_time_stamp = l_current_date,
mtl_txn_id = l_p_mtl_txn_id,
mtl_txn_creation_date = l_p_mtl_creation_date,
contracts_flag = l_contracts_flag,
process_flag = 'N'
WHERE instance_id = m_srl_rec.instance_id;
Insert_Full_Dump(p_instance_id => m_srl_rec.instance_id);
INSERT INTO csi_ii_forward_sync_temp(
instance_id,
serial_number,
inventory_item_id,
lot_control_code,
date_time_stamp,
mtl_txn_id,
mtl_txn_creation_date,
csi_txn_id,
contracts_flag,
process_flag,
error_message)
VALUES(
m_srl_rec.instance_id,
m_srl_rec.serial_number,
m_srl_rec.inventory_item_id,
m_srl_rec.lot_control_code,
sysdate,
l_p_mtl_txn_id,
l_p_mtl_creation_date,
l_p_csi_txn_id,
l_contracts_flag,
'N',
null);
UPDATE csi_diagnostics_temp
SET process_flag = 'R',
temporary_message = 'unmarking the serial to be processed after entanglement.'
WHERE diag_seq_id = p_lt_rec.diag_seq_id;
SELECT transaction_id INTO l_txn_rec.transaction_id
FROM csi_transactions
WHERE inv_material_transaction_id = missing_csi_rec.mtl_txn_id;
UPDATE csi_diagnostics_temp
SET csi_txn_id = l_txn_rec.transaction_id,
error_flag = 'P'
WHERE diag_seq_id = missing_csi_rec.diag_seq_id;
SELECT serial_number,
inventory_item_id
FROM csi_diagnostics_temp
GROUP BY serial_number, inventory_item_id;
SELECT diag_seq_id,
serial_number,
inventory_item_id,
organization_id,
mtl_txn_id,
mtl_txn_qty,
mtl_creation_date,
serial_control_code,
lot_control_code,
revision_control_code,
csi_txn_id,
instance_id,
create_flag,
source_type,
error_flag ,
mtl_item_revision
FROM csi_diagnostics_temp
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id
ORDER by mtl_creation_date desc, mtl_txn_id desc;
l_diag_txn_tbl.delete;
l_diag_txn_x_tbl.delete;
SELECT csi_ii_forward_sync_temp_s.nextval
INTO l_seq_value
FROM sys.dual;
SELECT *
FROM csi_diagnostics_temp
ORDER BY serial_number, inventory_item_id, mtl_creation_date desc, mtl_txn_id desc;
SELECT * FROM csi_diagnostics_temp A
WHERE exists (
SELECT 'X' FROM csi_diagnostics_temp B
WHERE B.serial_number = A.serial_number
AND B.inventory_item_id = A.inventory_item_id
AND B.process_flag in ('M', 'X', 'R'))
ORDER BY A.serial_number, a.inventory_item_id, a.mtl_creation_date desc, a.mtl_txn_id desc;
SELECT * FROM csi_diagnostics_temp A
WHERE not exists (
SELECT 'X' FROM csi_diagnostics_temp B
WHERE B.serial_number = A.serial_number
AND B.inventory_item_id = A.inventory_item_id
AND B.process_flag in ('M', 'X', 'R'))
ORDER BY A.serial_number, a.inventory_item_id, a.mtl_creation_date desc, a.mtl_txn_id desc;
SELECT * FROM csi_diagnostics_temp A
WHERE exists (
SELECT 'X' FROM csi_diagnostics_temp B
WHERE B.serial_number = A.serial_number
AND B.inventory_item_id = A.inventory_item_id
AND B.process_flag = 'E')
ORDER BY A.serial_number, a.inventory_item_id, a.mtl_creation_date desc, a.mtl_txn_id desc;
SELECT last_vld_organization_id,
inv_organization_id,
inv_subinventory_name,
inventory_revision,
inv_locator_id,
location_type_code,
instance_usage_code,
location_id
INTO l_vld_organization_id,
l_inv_organization_id,
l_inv_subinventory_name,
l_inventory_revision,
l_inv_locator_id,
l_location_type_code,
l_instance_usage_code,
l_location_id
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
SELECT last_vld_organization_id,
inv_organization_id,
inv_subinventory_name,
inventory_revision,
inv_locator_id,
location_type_code,
instance_usage_code,
location_id,
lot_number,
pa_project_id,
pa_project_task_id,
accounting_class_code,
wip_job_id,
in_transit_order_line_id
INTO l_vld_organization_id,
l_inv_organization_id,
l_inv_subinventory_name,
l_inventory_revision,
l_inv_locator_id,
l_location_type_code,
l_instance_usage_code,
l_location_id,
l_lot_number,
l_pa_project_id,
l_pa_project_task_id,
l_accounting_class_code,
l_wip_job_id,
l_in_transit_order_line_id
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
px_parties_tbl.delete;
px_pty_accts_tbl.delete;
px_org_units_tbl.delete;
px_ea_values_tbl.delete;
px_pricing_tbl.delete;
px_assets_tbl.delete;
SELECT mtl_txn_id,
mtl_src_type_id,
mtl_txn_date
FROM csi_diagnostics_temp
WHERE process_flag = 'X';
UPDATE csi_txn_errors
SET processed_flag = 'D',
error_text = 'A future transaction is processed. So not processing this.',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE inv_material_transaction_id = knock_rec.mtl_txn_id
AND processed_flag in ('E', 'R');
SELECT transaction_id INTO l_txn_rec.transaction_id
FROM csi_transactions
WHERE inv_material_transaction_id = knock_rec.mtl_txn_id;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
SELECT primary_uom_code
INTO l_instance_rec.unit_of_measure
FROM mtl_system_items
WHERE inventory_item_id = l_instance_rec.inventory_item_id
AND organization_id = l_instance_rec.vld_organization_id;
UPDATE csi_transactions
SET inv_material_transaction_id = null
WHERE transaction_id = l_txn_rec.transaction_id;
PROCEDURE update_instance(
p_txn_rec IN csi_datastructures_pub.transaction_rec,
p_instance_rec IN csi_datastructures_pub.instance_rec,
p_parties_tbl IN csi_datastructures_pub.party_tbl,
p_pty_accts_tbl IN csi_datastructures_pub.party_account_tbl,
x_return_status OUT nocopy varchar2,
x_error_message OUT nocopy varchar2)
IS
l_not_the_same boolean := TRUE;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
savepoint update_instance;
UPDATE csi_item_instances
SET last_vld_organization_id = l_instance_rec.vld_organization_id,
active_end_date = null
WHERE instance_id = l_instance_rec.instance_id;
l_parties_tbl.delete;
l_pty_accts_tbl.delete;
SELECT instance_party_id,
object_version_number
INTO l_parties_tbl(lp_ind).instance_party_id,
l_parties_tbl(lp_ind).object_version_number
FROM csi_i_parties
WHERE instance_id = l_instance_rec.instance_id
AND relationship_type_code = l_parties_tbl(lp_ind).relationship_type_code
AND rownum = 1;
SELECT object_version_number
INTO l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
log(' update status :'||l_return_status||' '||l_msg_data);
UPDATE csi_transactions
SET inv_material_transaction_id = null
WHERE transaction_id = l_txn_rec.transaction_id;
UPDATE csi_item_instances
SET active_end_date = l_instance_rec.active_end_date
WHERE instance_id = l_instance_rec.instance_id;
UPDATE csi_item_instances
SET active_end_date = null
WHERE instance_id = l_instance_rec.instance_id;
rollback to update_instance;
END update_instance;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT distinct mtl_txn_id ,
inventory_item_id,
organization_id
FROM csi_diagnostics_temp
WHERE process_flag = 'M'
AND process_code = 'SOISHIP';
SELECT cdt.diag_seq_id,
cdt.process_code,
cdt.mtl_txn_id,
cdt.mtl_txn_date mtl_txn_date,
cdt.serial_number,
cdt.inventory_item_id,
cdt.instance_id,
cdt.csi_txn_id,
cdt.csi_txn_type_id,
cdt.wip_job_id,
cdt.internal_party_id,
nvl(cdt.create_flag, 'Y') create_flag,
nvl(cdt.expire_flag, 'N') expire_flag,
cdt.serial_control_code,
cdt.lot_control_code
FROM csi_diagnostics_temp cdt
WHERE cdt.mtl_txn_id = p_mtl_txn_id
AND cdt.process_flag = 'M'
AND nvl(cdt.create_flag, 'Y') = 'N'; -- marked FOR processing
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
to_char(null) lot_number,
abs(mmt.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id
AND p_lot_code = 1
UNION
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
mtln.lot_number lot_number,
abs(mtln.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE mmt.transaction_id = p_mtl_txn_id
AND mtln.transaction_id = mmt.transaction_id
AND p_lot_code <> 1;
SELECT transaction_date,
trx_source_line_id
INTO l_mtl_txn_date,
l_order_line_id
FROM mtl_material_transactions
WHERE transaction_id = txn_rec.mtl_txn_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'E',
temporary_message = l_error_message
WHERE diag_seq_id = returned_serial_rec.diag_seq_id;
SELECT lot_control_code,
primary_uom_code
INTO l_lot_code,
l_uom_code
FROM mtl_system_items
WHERE inventory_item_id = txn_rec.inventory_item_id
AND organization_id = txn_rec.organization_id;
SELECT instance_id,
quantity,
object_version_number
INTO l_instance_id,
l_quantity,
l_object_version_number
FROM csi_item_instances
WHERE location_type_code = 'INVENTORY'
AND instance_usage_code = 'IN_INVENTORY'
AND inventory_item_id = stage_rec.item_id
AND inv_organization_id = stage_rec.organization_id
AND inv_subinventory_name = stage_rec.subinv_code
AND nvl(inv_locator_id,-9999) = nvl(stage_rec.locator_id,-9999)
AND nvl(lot_number,'$$##$$') = nvl(stage_rec.lot_number,'$$##$$')
AND nvl(inventory_revision,'$$##$$') = nvl(stage_rec.revision,'$$##$$')
AND serial_number is null;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
UPDATE csi_diagnostics_temp
SET process_flag = 'P',
temporary_message = 'Sales order issue instance staged.'
WHERE mtl_txn_id = txn_rec.mtl_txn_id;
UPDATE csi_txn_errors
SET processed_flag = 'R',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE inv_material_transaction_id = txn_rec.mtl_txn_id
AND processed_flag = 'E';
UPDATE csi_diagnostics_temp
SET process_flag = 'E',
temporary_message = l_error_message
WHERE mtl_txn_id = txn_rec.mtl_txn_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT mtl_txn_id,
mtl_action_id,
mtl_src_type_id,
mtl_type_id
FROM csi_diagnostics_temp
WHERE inventory_item_id = p_item_id
AND serial_number = p_serial_number
AND diag_seq_id > p_diag_seq_id
ORDER by diag_seq_id asc;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT location_type_code
INTO l_location_type_code
FROM csi_item_instances
WHERE instance_id = p_diag_txn_rec.inst_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT location_type_code
INTO l_location_type_code
FROM csi_item_instances
WHERE instance_id = p_diag_txn_rec.inst_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT transfer_transaction_id
INTO l_mtl_xfer_txn_id
FROM mtl_material_transactions
WHERE transaction_id = p_diag_txn_rec.mtl_txn_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT serial_number_control_code
INTO l_src_serial_code
FROM mtl_system_items_b
WHERE inventory_item_id = inv_rec.item_id
AND organization_id = inv_rec.xfer_organization_id;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_inv_location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = inv_rec.organization_id
AND mssi.secondary_inventory_name = inv_rec.subinv_code
AND haou.organization_id = mssi.organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
l_parties_tbl.delete;
SELECT instance_id,
object_version_number,
quantity
INTO l_instance_rec.instance_id,
l_instance_rec.object_version_number,
l_instance_quantity
FROM csi_item_instances
WHERE inventory_item_id = inv_rec.item_id
AND nvl(inventory_revision,'$$##$$') = nvl(inv_rec.revision, '$$##$$')
AND nvl(lot_number,'$$##$$') = nvl(l_lot_number, '$$##$$')
AND location_type_code = 'INVENTORY'
AND instance_usage_code = 'IN_TRANSIT'
AND serial_number is null;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_parties_tbl.delete;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT serial_number_control_code
INTO l_src_serial_code
FROM mtl_system_items_b
WHERE inventory_item_id = inv_rec.item_id
AND organization_id = inv_rec.xfer_organization_id;
SELECT serial_number_control_code
INTO l_dest_serial_code
FROM mtl_system_items_b
WHERE inventory_item_id = inv_rec.item_id
AND organization_id = inv_rec.organization_id;
SELECT trx_source_line_id
INTO l_instance_rec.in_transit_order_line_id
FROM mtl_material_transactions
WHERE transaction_action_id = 21
AND transaction_source_type_id = 8
AND (shipment_number, inventory_item_id) IN (
SELECT shipment_number, inventory_item_id
FROM mtl_material_transactions
WHERE transaction_id = inv_rec.mtl_txn_id)
AND rownum = 1;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT instance_id,
object_version_number,
quantity
INTO l_instance_rec.instance_id,
l_instance_rec.object_version_number,
l_instance_quantity
FROM csi_item_instances
WHERE inventory_item_id = inv_rec.item_id
AND nvl(inventory_revision,'$$##$$') = nvl(inv_rec.revision, '$$##$$')
AND nvl(lot_number,'$$##$$') = nvl(l_lot_number, '$$##$$')
AND location_type_code = 'IN_TRANSIT'
AND instance_usage_code = 'IN_TRANSIT'
AND serial_number is null;
SELECT trx_source_line_id
INTO l_instance_rec.in_transit_order_line_id
FROM mtl_material_transactions
WHERE transaction_action_id = 21
AND transaction_source_type_id = 8
AND (shipment_number, inventory_item_id) IN (
SELECT shipment_number, inventory_item_id
FROM mtl_material_transactions
WHERE transaction_id = inv_rec.mtl_txn_id)
AND rownum = 1;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
to_char(null) lot_number,
abs(mmt.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id
AND p_lot_code = 1
UNION
SELECT mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinv_code,
mmt.locator_id locator_id,
mmt.revision revision,
mtln.lot_number lot_number,
abs(mtln.primary_quantity) quantity,
mmt.transaction_date mtl_txn_date,
mmt.transaction_id mtl_txn_id,
mmt.trx_source_line_id trx_source_line_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE mmt.transaction_id = p_mtl_txn_id
AND mtln.transaction_id = mmt.transaction_id
AND p_lot_code <> 1;
SELECT serial_number_control_code,
lot_control_code,
primary_uom_code
INTO l_src_serial_code,
l_src_lot_code,
l_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = inv_rec.item_id
AND organization_id = inv_rec.organization_id;
SELECT serial_number_control_code,
lot_control_code
INTO l_dest_serial_code,
l_dest_lot_code
FROM mtl_system_items
WHERE inventory_item_id = inv_rec.item_id
AND organization_id = inv_rec.xfer_organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
SELECT instance_id,
quantity,
object_version_number
INTO l_instance_id,
l_quantity,
l_object_version_number
FROM csi_item_instances
WHERE location_type_code = 'INVENTORY'
AND instance_usage_code = 'IN_INVENTORY'
AND inventory_item_id = stage_rec.item_id
AND inv_organization_id = stage_rec.organization_id
AND inv_subinventory_name = stage_rec.subinv_code
AND nvl(inv_locator_id,-9999) = nvl(stage_rec.locator_id,-9999)
AND nvl(lot_number,'$$##$$') = nvl(stage_rec.lot_number,'$$##$$')
AND nvl(inventory_revision,'$$##$$') = nvl(stage_rec.revision,'$$##$$')
AND serial_number is null;
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
log(' update_item_instance:nsrl: '||l_return_status);
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
UPDATE csi_diagnostics_temp
SET process_flag = 'P'
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
UPDATE csi_txn_errors
SET processed_flag = 'R',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE inv_material_transaction_id = p_diag_txn_rec.mtl_txn_id
AND processed_flag = 'E';
UPDATE csi_diagnostics_temp
SET process_flag = 'E',
temporary_message = l_error_message
WHERE diag_seq_id = p_diag_txn_rec.diag_seq_id;
SELECT cdt.diag_seq_id,
nvl(cdt.process_code,'NONE') process_code,
cdt.mtl_txn_id,
cdt.mtl_txn_date mtl_txn_date,
cdt.serial_number,
cdt.inventory_item_id,
cdt.instance_id,
cdt.csi_txn_id,
cdt.csi_txn_type_id,
cdt.wip_job_id,
nvl(cdt.create_flag, 'N') create_flag,
nvl(cdt.expire_flag, 'N') expire_flag,
cdt.serial_control_code,
cdt.lot_control_code,
cdt.source_type,
cdt.internal_party_id
FROM csi_diagnostics_temp cdt
WHERE nvl(cdt.process_flag ,'N') = 'M'; -- marked FOR processing
SELECT distinct mtl_txn_id
FROM csi_diagnostics_temp
WHERE process_flag = 'R';
update csi_txn_errors
SET processed_flag = 'R',
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE inv_material_transaction_id = reprocess_rec.mtl_txn_id
AND processed_flag = 'E';
SELECT mmt.creation_date mtl_creation_date,
mut.transaction_id mtl_txn_id,
mut.transaction_date mtl_txn_date,
mmt.trx_source_line_id rma_line_id
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial
AND mut.inventory_item_id = p_item_id
AND mut.transaction_date > p_freeze_date
AND mmt.transaction_id = mut.transaction_id
AND mmt.transaction_type_id = 15
UNION
SELECT mmt.creation_date mtl_creation_date,
mtln.transaction_id mtl_txn_id,
mtln.transaction_date mtl_txn_date,
mmt.trx_source_line_id rma_line_id
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial
AND mut.inventory_item_id = p_item_id
AND mtln.serial_transaction_id = mut.transaction_id
AND mtln.transaction_date > p_freeze_date
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_type_id = 15
ORDER by 1 desc, 2 desc;
SELECT ctld.sub_type_id
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctld
WHERE ctl.source_transaction_table = 'OE_ORDER_LINES_ALL'
AND ctl.source_transaction_type_id = 53
AND ctl.source_transaction_id = p_rma_line_id
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.source_transaction_flag = 'Y';
SELECT sub_type_id
INTO l_dflt_sub_type_id
FROM csi_txn_sub_types
WHERE transaction_type_id = 53
AND default_flag = 'Y';
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner_flag
FROM csi_txn_sub_types
WHERE transaction_type_id = 53
AND sub_type_id = l_sub_type_id;
SELECT nvl(oel.sold_to_org_id, oeh.sold_to_org_id)
INTO l_owner_account_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oel.line_id = l_rma_line_id
AND oeh.header_id = oel.header_id;
SELECT party_id
INTO l_owner_party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_owner_account_id;
SELECT msn.serial_number serial_number,
msn.inventory_item_id inventory_item_id,
msn.current_organization_id organization_id,
msn.revision revision,
msn.current_subinventory_code subinventory_code,
msn.current_locator_id locator_id,
msn.lot_number lot_number,
msi.primary_uom_code uom_code,
msi.serial_number_control_code serial_code,
msi.lot_control_code lot_code
FROM mtl_system_items msi,
mtl_serial_numbers msn
WHERE msi.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = msn.current_organization_id
AND msi.serial_number_control_code in (2,5)
AND msn.current_status = 3
AND EXISTS (
SELECT '1'
FROM mtl_parameters mp,
mtl_system_items msi_mast
WHERE mp.organization_id = msi.organization_id
AND msi_mast.inventory_item_id = msi.inventory_item_id
AND msi_mast.organization_id = mp.master_organization_id
AND nvl(msi_mast.comms_nl_trackable_flag,'N') = 'Y')
AND EXISTS (
SELECT '1'
FROM mtl_onhand_quantities moq
WHERE moq.inventory_item_id = msn.inventory_item_id
AND moq.organization_id = msn.current_organization_id
AND moq.subinventory_code = msn.current_subinventory_code
AND nvl(moq.locator_id,-999) = nvl(msn.current_locator_id,-999)
AND nvl(moq.lot_number,'$#$') = nvl(msn.lot_number,'$#$')
AND nvl(moq.revision,'$#$') = nvl(msn.revision,'$#$') );
SELECT fnd_Profile.value('csi_upgrading_from_release')
INTO l_release
FROM sys.dual;
SELECT 'Y'
INTO l_fs_found
FROM csi_ii_forward_sync_temp
WHERE inventory_item_id = srl_rec.inventory_item_id
AND serial_number = srl_rec.serial_number
AND process_flag <> 'P';
SELECT location_id
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND organization_id = l_instance_rec.vld_organization_id;
SELECT location_id
INTO l_instance_rec.location_id
FROM hr_all_organization_units
WHERE organization_id = l_instance_rec.vld_organization_id;
SELECT instance_id ,
object_version_number
INTO l_instance_rec.instance_id,
l_instance_rec.object_version_number
FROM csi_item_instances
WHERE inventory_item_id = srl_rec.inventory_item_id
AND serial_number = srl_rec.serial_number;
SELECT 'X' INTO l_txn_is_in_csi
FROM sys.dual
WHERE exists (
SELECT 'Y' FROM csi_transactions
WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id);
SELECT 'Y' INTO l_pending_msg_found
FROM sys.dual
WHERE exists (
SELECT '1'
FROM csi_xnp_msgs_temp
WHERE source_type = 'MTL_TRANSACTION_ID'
AND source_id = all_txn_rec.mtl_txn_id
AND nvl(msg_status, 'READY') <> 'PROCESSED');
SELECT 'Y' INTO l_pending_err_found
FROM sys.dual
WHERE exists (
SELECT '1'
FROM csi_txn_errors
WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
OR
inv_material_transaction_id = all_txn_rec.mtl_txn_id)
AND processed_flag in ('E', 'R'));
SELECT 'Y' INTO l_pending_err_found
FROM sys.dual
WHERE exists (
SELECT '1'
FROM csi_txn_errors
WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id
AND processed_flag in ('E', 'R'));
SELECT instance_party_id,
object_version_number
INTO l_parties_tbl(1).instance_party_id,
l_parties_tbl(1).object_version_number
FROM csi_i_parties
WHERE instance_id = l_instance_rec.instance_id
AND relationship_type_code = 'OWNER';
SELECT object_version_number
INTO l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
log(' csi_item_instance_pub.update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id
and cir.relationship_type_code = l_rel_type_code
and ((cir.active_end_date is null) or (cir.active_end_date > sysdate))
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES cii
where cii.instance_id = cir.subject_id
and ((active_end_date is null) or (active_end_date > sysdate)));
SELECT transaction_id,
source_line_ref_id,
inv_material_transaction_id
FROM csi_transactions
WHERE transaction_type_id = 53
AND inv_material_transaction_id is null;
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_type_id = 15 -- RMA Transaction
AND inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND trx_source_line_id = p_line_id
order by creation_date desc, transaction_id desc;
SELECT inventory_item_id,
ship_from_org_id
INTO l_inventory_item_id,
l_organization_id
FROM oe_order_lines_all
WHERE line_id = l_rma_line_id_tab(ind);
UPDATE csi_transactions
SET inv_material_transaction_id = l_mtl_txn_id_tab(u_ind)
WHERE transaction_id = l_csi_txn_id_tab(u_ind);
PROCEDURE Delete_Dup_Srl_Inv_Instance IS
CURSOR CHECK_CUR IS
select count(*)
from CSI_ITEM_INSTANCES
where location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and creation_date = last_update_date
and serial_number is not null
and lot_number is null
group by inventory_item_id,serial_number
having count(*) > 1 ;
select instance_id,
inventory_item_id,
inv_organization_id,
inventory_revision,
inv_subinventory_name,
inv_locator_id,
serial_number,
last_update_date
from csi_item_instances
where instance_id between p_min and p_max
and serial_number is not null
and lot_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and migrated_flag = 'Y'
and mfg_serial_number_flag = 'Y'
and last_update_date = creation_date;
select min(instance_id)
from csi_item_instances
where serial_number is not null
and lot_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and migrated_flag = 'Y'
and mfg_serial_number_flag = 'Y'
and last_update_date = creation_date;
select max(instance_id)
from csi_item_instances
where serial_number is not null
and lot_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and migrated_flag = 'Y'
and mfg_serial_number_flag = 'Y'
and last_update_date = creation_date;
SELECT transaction_id
from CSI_INST_TRANSACTIONS_V
where instance_id = p_instance_id;
l_instance_tbl.DELETE;
l_txn_tbl.DELETE;
select mmt.subinventory_code,mmt.locator_id,mmt.organization_id,mmt.revision
into v_subinv,v_locator_id,v_org_id,v_revision
from MTL_MATERIAL_TRANSACTIONS mmt
where mmt.transaction_id = (select max(transaction_id)
from MTL_UNIT_TRANSACTIONS
where serial_number = csi_rec.serial_number
and inventory_item_id = csi_rec.inventory_item_id
and last_update_date <= csi_rec.last_update_date);
DELETE FROM CSI_ITEM_INSTANCES WHERE instance_id = l_instance_tbl(j);
DELETE FROM CSI_I_PARTIES WHERE instance_id = l_instance_tbl(j);
DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_instance_tbl(j);
DELETE FROM CSI_ITEM_INSTANCES_H WHERE transaction_id = l_txn_tbl(j);
DELETE FROM CSI_I_PARTIES_H WHERE transaction_id = l_txn_tbl(j);
DELETE FROM CSI_I_VERSION_LABELS_H WHERE transaction_id = l_txn_tbl(j);
DELETE FROM CSI_TRANSACTIONS WHERE transaction_id = l_txn_tbl(j);
END Delete_Dup_Srl_Inv_Instance;
PROCEDURE Update_Instance_Usage IS
CURSOR CSI_CUR IS
Select instance_id,location_type_code
,serial_number_control_code,serial_number,null usage_code
from CSI_ITEM_INSTANCES cii
,MTL_SYSTEM_ITEMS_B msi
Where cii.instance_usage_code is NULL
and msi.inventory_item_id = cii.inventory_item_id
and msi.organization_id = cii.last_vld_organization_id;
select 'x'
into l_exists
from CSI_II_RELATIONSHIPS
where subject_id = instance_id_mig(i)
and relationship_type_code = l_rel_type_code
and ((active_end_date is null) or (active_end_date > sysdate));
UPDATE CSI_ITEM_INSTANCES
set instance_usage_code = usage_code_mig(j)
,last_update_date = sysdate
where instance_id = instance_id_mig(j);
END Update_Instance_Usage;
PROCEDURE Update_Full_dump_flag IS
CURSOR CSI_REL_CUR IS
SELECT relationship_history_id
FROM CSI_II_RELATIONSHIPS_H
WHERE nvl(MIGRATED_FLAG,'N') = 'Y'
AND full_dump_flag <> 'Y';
SELECT system_id,min(system_history_id) system_history_id
FROM CSI_SYSTEMS_H
WHERE nvl(MIGRATED_FLAG,'N') = 'Y'
AND full_dump_flag <> 'Y'
group by system_id;
update CSI_II_RELATIONSHIPS_H
set full_dump_flag = 'Y'
where relationship_history_id = rel_history_id_mig(j);
UPDATE CSI_SYSTEMS_H
set full_dump_flag = 'Y'
where system_history_id = system_history_id_mig(j);
END Update_Full_dump_flag;
select count(*)
from csi_item_instances
where serial_number is not null
group by serial_number, inventory_item_id
having count(*) > 1;
select count(*), serial_number, inventory_item_id
from csi_item_instances
where serial_number is not null
group by serial_number, inventory_item_id
having count(*) > 1;
select instance_id,instance_status_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
order by instance_id desc;
select instance_status_id
into v_instance_status_id
from CSI_INSTANCE_STATUSES
where name = 'EXPIRED';
select max(instance_id)
into v_instance_id
from csi_item_instances
where inventory_item_id = i.inventory_item_id
and serial_number = i.serial_number;
update csi_item_instances_h
set old_serial_number = i.serial_number
,new_serial_number = new_serial_number||'-DUP'||to_char(v_duplicate_count)
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
,old_instance_status_id = j.instance_status_id
,new_instance_status_id = v_instance_status_id
where instance_history_id = (select max(instance_history_id)
from csi_item_instances_h
where instance_id = j.instance_id);
update csi_item_instances
set serial_number = serial_number||'-DUP'||to_char(v_duplicate_count)
,active_end_date = sysdate
,instance_status_id = v_instance_status_id
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
where instance_id = j.instance_id;
PROCEDURE Update_Vld_Organization IS
CURSOR CSI_INS_CUR IS
SELECT instance_id,inventory_item_id,inv_organization_id,last_vld_organization_id
,serial_number,lot_number
,mfg_serial_number_flag,creation_complete_flag
,inventory_revision
,instance_usage_code
FROM CSI_ITEM_INSTANCES
WHERE location_type_code = 'INVENTORY'
AND inv_organization_id is not null
AND nvl(last_vld_organization_id,-999) <> inv_organization_id;
Update CSI_ITEM_INSTANCES
set last_vld_organization_id = vld_org_id_mig(j)
where instance_id = instance_id_mig(j);
Update CSI_ITEM_INSTANCES_H
set new_last_vld_organization_id = vld_org_id_mig(j)
where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
where instance_id = instance_id_mig(j));
END Update_Vld_Organization;
PROCEDURE Update_Revision IS
CURSOR c1 IS
SELECT a.instance_id
,a.inventory_item_id
,a.last_vld_organization_id
,a.last_oe_order_line_id
,a.serial_number
,a.inventory_revision
FROM csi_item_instances a
,mtl_system_items_b b
WHERE a.inventory_item_id = b.inventory_item_id
AND a.last_vld_organization_id = b.organization_id
AND a.creation_complete_flag = 'Y'
AND a.inventory_revision IS NULL
AND a.migrated_flag = 'Y'
AND b.revision_qty_control_code = 2;
l_sql_stmt := 'select cr.revision from CS_CP_REVISIONS cr, '||l_object_name || ' where cp.customer_product_id = :inst_id and cr.cp_revision_id = cp.current_cp_revision_id';
select revision
into revision_mig(i)
from MTL_MATERIAL_TRANSACTIONS
where transaction_type_id = 33
and transaction_action_id = 1
and trx_source_line_id = order_line_id_mig(i)
and rownum < 2;
select item_revision
into revision_mig(i)
from OE_ORDER_LINES_ALL
where line_id = order_line_id_mig(i);
select revision
into revision_mig(i)
from MTL_SERIAL_NUMBERS
where inventory_item_id = item_id_mig(i)
and serial_number = serial_number_mig(i);
UPDATE csi_item_instances
SET inventory_revision = revision_mig(j)
WHERE instance_id = instance_id_mig(j);
Update CSI_ITEM_INSTANCES_H
set new_inventory_revision = revision_mig(j)
where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
where instance_id = instance_id_mig(j));
END Update_Revision;
PROCEDURE Update_Dup_Srl_Instance IS
CURSOR CSI_CUR IS -- Cursor to delete the Inv instances emerged from wrong WHERE clause in Mig
select cii.instance_id
from CSI_ITEM_INSTANCES cii
where cii.location_type_code = 'INVENTORY'
and cii.instance_usage_code = 'IN_INVENTORY'
and mfg_serial_number_flag = 'Y'
and cii.serial_number is not null
and cii.lot_number is not null
and cii.migrated_flag = 'Y'
and cii.creation_date = cii.last_update_date
and not exists (select 'X' from mtl_serial_numbers msn
where msn.inventory_item_id = cii.inventory_item_id
and msn.serial_number = cii.serial_number
and msn.lot_number = cii.lot_number);
SELECT transaction_id
from CSI_INST_TRANSACTIONS_V
where instance_id = p_instance_id;
select count(*), serial_number, inventory_item_id
from csi_item_instances
where migrated_flag = 'Y'
and serial_number is not null
group by serial_number, inventory_item_id
having count(*) > 1;
select max(instance_id)
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and inv_subinventory_name IS NOT NULL
and inv_organization_id IS NOT NULL;
select max(instance_id)
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
select max(instance_id)
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
and last_oe_rma_line_id is not null
and migrated_flag = 'Y';
select instance_id,instance_status_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
order by instance_id desc;
select transaction_id
from csi_item_instances_h
where instance_id = p_inst_id1
intersect
select transaction_id
from csi_item_instances_h
where instance_id = p_inst_id2;
l_sql_stmt := 'select terminated_flag,cp.customer_product_status_id from CSI_INSTANCE_STATUSES cis, '||l_object_name||' where cp.customer_product_id = :inst_id and cis.instance_status_id = cp.customer_product_status_id';
DELETE FROM CSI_ITEM_INSTANCES WHERE instance_id = l_del_inst_tbl(j);
DELETE FROM CSI_I_PARTIES WHERE instance_id = l_del_inst_tbl(j);
DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_del_inst_tbl(j);
DELETE FROM CSI_ITEM_INSTANCES_H WHERE transaction_id = l_del_txn_tbl(j);
DELETE FROM CSI_I_PARTIES_H WHERE transaction_id = l_del_txn_tbl(j);
DELETE FROM CSI_I_VERSION_LABELS_H WHERE transaction_id = l_del_txn_tbl(j);
DELETE FROM CSI_TRANSACTIONS WHERE transaction_id = l_del_txn_tbl(j);
BEGIN -- Second Update
begin
select instance_status_id
into v_instance_status_id
from CSI_INSTANCE_STATUSES
where name = 'EXPIRED';
select location_id,inv_organization_id
,inv_subinventory_name,inv_locator_id,inventory_revision
into v_loc_id,v_org_id,v_subinv,v_locator,v_rev
from CSI_ITEM_INSTANCES
where instance_id = v_inv_instance_id;
select serial_number_control_code
into v_srl_control
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = i.inventory_item_id
and organization_id = v_org_id;
select instance_status_id
into v_status_id
from CSI_INSTANCE_STATUSES
where name = v_status;
update CSI_ITEM_INSTANCES
set location_type_code = 'INVENTORY'
,location_id = v_loc_id
,accounting_class_code = 'CUST_PROD'
,instance_usage_code = v_inst_usage_code
,last_vld_organization_id = v_org_id
,inv_organization_id = v_org_id
,inv_subinventory_name = v_subinv
,inv_locator_id = v_locator
,instance_status_id = v_status_id
,inventory_revision = v_rev
where instance_id = v_ret_instance_id;
update csi_item_instances
set external_reference = serial_number
, serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
,active_end_date = sysdate
,instance_status_id = v_instance_status_id
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
where instance_id = j.instance_id;
update csi_item_instances_h
set instance_id = v_instance_id
,new_external_reference = i.serial_number
,old_serial_number = nvl(new_serial_number,i.serial_number)
,new_serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
where instance_id = j.instance_id;
update csi_item_instances_h
set new_external_reference = i.serial_number
,old_serial_number = nvl(new_serial_number,i.serial_number)
,new_serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
where instance_history_id = (select max(instance_history_id)
from csi_item_instances_h
where instance_id = j.instance_id
and creation_date < v_freeze_date);
END; -- Second Update
END Update_Dup_Srl_Instance;
PROCEDURE Delete_Dup_Account IS
CURSOR IP_CUR IS
select cia.instance_party_id
from csi_ip_accounts cia
where cia.relationship_type_code = 'OWNER'
group by cia.instance_party_id
having count(*) > 1;
select ip_account_id
from csi_ip_accounts
where instance_party_id = p_inst_party_id
and relationship_type_code = 'OWNER'
and ip_account_id <> p_ip_acct_id
order by ip_account_id asc;
select max(ip_account_id)
into v_max_ip_acct_id
from csi_ip_accounts
where instance_party_id = party.instance_party_id
and relationship_type_code = 'OWNER'
and ((active_end_date is null) or (active_end_date > sysdate));
select min(ip_account_history_id)
into v_min_ret_id
from CSI_IP_ACCOUNTS_H
where ip_account_id = v_max_ip_acct_id;
select min(ip_account_history_id)
into v_min_history_id
from CSI_IP_ACCOUNTS_H
where ip_account_id = acct.ip_account_id;
Update CSI_IP_ACCOUNTS_H
set old_party_account_id = v_old_account_id
where ip_account_history_id = v_min_history_id;
DELETE from CSI_IP_ACCOUNTS_H
where ip_account_id = acct.ip_account_id
and ip_account_history_id <> v_min_history_id;
select new_party_account_id
into v_old_account_id
from CSI_IP_ACCOUNTS_H
where ip_account_history_id = v_min_history_id;
Update CSI_IP_ACCOUNTS_H
set ip_account_id = v_max_ip_acct_id
where ip_account_history_id = v_min_history_id;
Delete from CSI_IP_ACCOUNTS
where ip_account_id = acct.ip_account_id;
Update CSI_IP_ACCOUNTS_H
set old_party_account_id = v_old_account_id
where ip_account_history_id = v_min_ret_id;
END Delete_Dup_Account;
PROCEDURE Update_Instance_Party_Source IS
CURSOR CSI_INS_CUR IS
SELECT instance_id,null
FROM csi_item_instances
WHERE (owner_party_source_table is null
OR owner_party_source_table not in ('HZ_PARTIES','PO_VENDORS','EMPLOYEE','TEAM','GROUP'));
SELECT party_source_table
INTO party_src_table_mig(i)
FROM csi_i_parties
WHERE relationship_type_code = 'OWNER'
AND instance_id = instance_id_mig(i);
UPDATE CSI_ITEM_INSTANCES
SET OWNER_PARTY_SOURCE_TABLE = party_src_table_mig(j)
WHERE instance_id = instance_id_mig(j);
END Update_Instance_Party_Source;
PROCEDURE Update_Contact_Party_Record IS
CURSOR CSI_PARTY_CUR IS
select instance_id,instance_party_id,contact_ip_id,
contact_flag,relationship_type_code
from CSI_I_PARTIES
where contact_ip_id IS NOT NULL;
l_upd_pty_tbl.DELETE;
l_exp_pty_tbl.DELETE;
select 'x'
into l_exists
from CSI_I_PARTIES
where instance_party_id = contact_ip_id_mig(i)
and instance_id = instance_id_mig(i);
UPDATE CSI_I_PARTIES
set contact_ip_id = null
where instance_party_id = l_upd_pty_tbl(j);
UPDATE CSI_I_PARTIES
set contact_ip_id = null,
active_end_date = sysdate
where instance_party_id = l_exp_pty_tbl(j);
END Update_Contact_Party_Record;
PROCEDURE Revert_Party_Rel_Type_Update IS
CURSOR INS_CUR IS
select instance_id,relationship_type_code,count(*)
from CSI_I_PARTIES
where relationship_type_code = 'OWNER'
group by instance_id,relationship_type_code
having count(*) > 1;
select a.instance_party_history_id instance_party_history_id,a.instance_party_id instance_party_id,
a.old_relationship_type_code old_rel_type,
a.old_party_id old_party_id,a.new_party_id new_party_id
from CSI_I_PARTIES_H a
where a.old_relationship_type_code is not null
and a.old_relationship_type_code <> 'OWNER'
and a.new_relationship_type_code is not null
and a.new_relationship_type_code = 'OWNER'
and a.new_party_id is not null
and a.old_party_id is not null
and exists (select 'x' from CSI_I_PARTIES b
where b.instance_id = (select c.instance_id from CSI_I_PARTIES c
where c.instance_party_id = a.instance_party_id)
and b.relationship_type_code = 'OWNER')
and a.instance_party_history_id = (select max(instance_party_history_id)
from CSI_I_PARTIES_H d
where d.instance_party_id = a.instance_party_id);
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select CSI_TRANSACTIONS_S.nextval
into l_txn_id from dual;
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
l_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'Update Dup Owner' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1
);
Update CSI_I_PARTIES
set relationship_type_code = party_rec.old_rel_type,
party_id = party_rec.old_party_id
where instance_party_id = party_rec.instance_party_id;
Update CSI_I_PARTIES
set party_id = party_rec.new_party_id
where instance_id = (select instance_id from CSI_I_PARTIES
where instance_party_id = party_rec.instance_party_id)
and relationship_type_code = 'OWNER';
Update CSI_I_PARTIES_H
set new_relationship_type_code = party_rec.old_rel_type,
new_party_id = party_rec.old_party_id
where instance_party_history_id = party_rec.instance_party_history_id;
select instance_party_id
into l_ip_id
from CSI_I_PARTIES
where instance_id = l_inst_tbl(j)
and relationship_type_code = 'OWNER'
and party_id = l_internal_party_id;
Update CSI_IP_ACCOUNTS
set active_end_date = sysdate
where instance_party_id = l_ip_id
and relationship_type_code = 'OWNER';
-- Insert into IP Accounts history
select ip_account_id
into l_ip_account_id
from CSI_IP_ACCOUNTS
where instance_party_id = l_ip_id
and relationship_type_code = 'OWNER';
INSERT INTO CSI_IP_ACCOUNTS_H
( IP_ACCOUNT_HISTORY_ID
,IP_ACCOUNT_ID
,TRANSACTION_ID
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
( CSI_IP_ACCOUNTS_H_S.nextval
,l_ip_account_id
,l_txn_id
,NULL
,SYSDATE
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1
);
Update CSI_ITEM_INSTANCES
set owner_party_id = l_internal_party_id,
owner_party_account_id = null
where instance_id = l_inst_tbl(j);
select last_vld_organization_id
into l_vld_org_id
from CSI_ITEM_INSTANCES
where instance_id = l_inst_tbl(j);
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = l_txn_id;
END Revert_Party_Rel_Type_Update;
PROCEDURE Update_Master_Organization_ID IS
CURSOR CSI_CUR IS
select cii.instance_id,cii.inv_master_organization_id,
cii.last_vld_organization_id
from CSI_ITEM_INSTANCES cii
where not exists (select 'x'
from MTL_PARAMETERS msi
where organization_id = inv_master_organization_id
and master_organization_id = inv_master_organization_id);
select master_organization_id
into inv_master_org_id_mig(j)
from MTL_PARAMETERS
where organization_id = last_vld_org_id_mig(j);
UPDATE CSI_ITEM_INSTANCES
set inv_master_organization_id = inv_master_org_id_mig(i)
where instance_id = instance_id_mig(i);
UPDATE CSI_ITEM_INSTANCES_H
set old_inv_master_organization_id = decode(old_inv_master_organization_id,null,null,inv_master_org_id_mig(i)),
new_inv_master_organization_id = decode(new_inv_master_organization_id,null,null,inv_master_org_id_mig(i))
where instance_id = instance_id_mig(i);
END Update_Master_Organization_ID;
l_rel_tbl_next_lvl.delete;
l_rel_tbl.DELETE;
PROCEDURE Delete_Dup_Relationship IS
CURSOR CSI_REL_CUR IS
select object_id,subject_id,relationship_type_code,count(*)
from CSI_II_RELATIONSHIPS
where active_end_date is null
and nvl(migrated_flag,'N') = 'Y'
group by object_id,subject_id,relationship_type_code
having count(*) > 1;
select cir.relationship_id
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id
and cir.subject_id = p_subject_id
and cir.relationship_type_code = p_rel_type
and cir.active_end_date is null
and nvl(cir.migrated_flag,'N') = 'Y'
and cir.relationship_id <> p_rel_id
and not exists (select 'x' from CSI_II_RELATIONSHIPS_H cirh
where cirh.relationship_id = cir.relationship_id
and nvl(cirh.migrated_flag,'N') = 'N');
select max(cir.relationship_id)
into l_ret_relationship_id
from CSI_II_RELATIONSHIPS cir
where cir.object_id = l_object_id_mig(k)
and cir.subject_id = l_subject_id_mig(k)
and cir.relationship_type_code = l_rel_type_code_mig(k)
and cir.active_end_date is null
and nvl(cir.migrated_flag,'N') = 'Y'
and exists (select 'x' from CSI_II_RELATIONSHIPS_H cirh
where cirh.relationship_id = cir.relationship_id
and nvl(cirh.migrated_flag,'N') = 'N');
select max(relationship_id)
into l_ret_relationship_id
from CSI_II_RELATIONSHIPS
where object_id = l_object_id_mig(k)
and subject_id = l_subject_id_mig(k)
and relationship_type_code = l_rel_type_code_mig(k)
and active_end_date is null
and nvl(migrated_flag,'N') = 'Y';
DELETE FROM CSI_II_RELATIONSHIPS_H
WHERE relationship_id = l_rel_id_tbl(j);
DELETE FROM CSI_II_RELATIONSHIPS
WHERE relationship_id = l_rel_id_tbl(j);
END Delete_Dup_Relationship;
select instance_id,rowid
from CSI_EXPIRE_INSTANCES_TEMP
where process_code = p_process_code
and processed_flag in ('E','R');
select last_vld_organization_id
into l_vld_org
from CSI_ITEM_INSTANCES
where instance_id = exp.instance_id;
UPDATE CSI_EXPIRE_INSTANCES_TEMP
set processed_flag = 'E',
error_message = x_msg_data
where rowid = exp.rowid;
UPDATE CSI_EXPIRE_INSTANCES_TEMP
set processed_flag = 'P',
error_message = null
where rowid = exp.rowid;
select cii.instance_id,cii.inventory_item_id,cii.inv_master_organization_id
from CSI_ITEM_INSTANCES cii
where nvl(cii.active_end_date,(sysdate+1)) > sysdate;
select cii.instance_id,cii.inventory_item_id,cii.inv_master_organization_id,
cir.relationship_id
from CSI_ITEM_INSTANCES cii,
CSI_II_RELATIONSHIPS cir
where nvl(cii.active_end_date,(sysdate+1)) > sysdate
and cir.subject_id = cii.instance_id
and cir.relationship_type_code = 'COMPONENT-OF'
and nvl(cir.active_end_date,(sysdate+1)) > sysdate;
select cii.transaction_error_id,
mmt.inventory_item_id,mmt.organization_id
from CSI_TXN_ERRORS cii,
MTL_MATERIAL_TRANSACTIONS mmt
where cii.processed_flag in ('E', 'R')
and cii.inv_material_transaction_id is not null
and mmt.transaction_id = cii.inv_material_transaction_id;
select transaction_error_id,source_id
from CSI_TXN_ERRORS cii
where cii.inv_material_transaction_id is null
and cii.source_id is not null
and cii.processed_flag in ('E', 'R');
DELETE FROM CSI_EXPIRE_INSTANCES_TEMP
WHERE processed_flag = 'P'
AND process_code = 'EXPNL';
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select nvl(comms_nl_trackable_flag,'N')
into l_nl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(j)
and organization_id = mast_org_id_mig(j);
UPDATE CSI_II_RELATIONSHIPS
set active_end_date = sysdate
where relationship_id = l_rel_tbl(i);
select CSI_TRANSACTIONS_S.nextval
into v_txn_id from dual;
l_inst_tbl.DELETE;
l_inst_hist_tbl.DELETE;
select nvl(comms_nl_trackable_flag,'N')
into l_nl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = mast_org_id_mig(i);
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
l_child_tbl.DELETE;
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
INSERT INTO CSI_EXPIRE_INSTANCES_TEMP
( INSTANCE_ID,
PROCESSED_FLAG,
PROCESS_CODE
)
VALUES
( l_inst_tbl(j),
'R',
'EXPNL'
);
UPDATE CSI_ITEM_INSTANCES
set active_end_date = sysdate,
instance_status_id = 1,
last_update_date = sysdate,
last_updated_by = v_user_id
where instance_id = l_inst_tbl(j);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
l_inst_hist_tbl(j)
,v_txn_id
,l_inst_tbl(j)
,SYSDATE
,SYSDATE
,v_user_id
,v_user_id
,-1
,1
);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,SOURCE_LINE_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'COMMS_NL_TRACKABLE_FLAG got switched off' -- SOURCE_HEADER_REF
,'DATAFIX By Expire Non_NL Trackable' -- SOURCE_LINE_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_user_id
,sysdate
,v_user_id
,sysdate
,-1
,1
);
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = v_txn_id;
UPDATE CSI_II_RELATIONSHIPS
set active_end_date = null
where relationship_id = l_rel_tbl(i);
select comms_nl_trackable_flag
into l_nl
from MTL_SYSTEM_ITEMS msi,
MTL_PARAMETERS mp
where mp.organization_id = organization_id_mig(i)
and msi.inventory_item_id = inventory_item_id_mig(i)
and msi.organization_id = mp.master_organization_id;
UPDATE csi_txn_errors
SET processed_flag = 'D',
error_text = 'COMMS_NL_TRACKABLE_FLAG got switched off',
last_update_date = sysdate
WHERE transaction_error_id = l_upd_txn_tbl(i);
l_upd_txn_tbl.DELETE;
select oel.inventory_item_id,oel.org_id
into l_item_id, l_org_id
from OE_ORDER_LINES_ALL oel
where oel.line_id = source_id_mig(i);
select comms_nl_trackable_flag
into l_nl
from MTL_SYSTEM_ITEMS msi,
MTL_PARAMETERS mp
where mp.organization_id = l_organization_id
and msi.inventory_item_id = l_item_id
and msi.organization_id = mp.master_organization_id;
UPDATE CSI_TXN_ERRORS
set processed_flag = 'D',
error_text = 'COMMS_NL_TRACKABLE_FLAG got switched off',
last_update_date = sysdate
where transaction_error_id = l_upd_txn_tbl(i);
PROCEDURE Update_No_ctl_Srl_Lot_Inst IS
/* commenting the following cursors for the bug 5989350 ,Since there is
no need to nullify the serial and lot number for the instances
which are already shipped to customers */
/* CURSOR CSI_SRL_CUR IS
select cii.instance_id,cii.serial_number,
cii.last_vld_organization_id,cii.inventory_item_id
from CSI_ITEM_INSTANCES cii
where cii.serial_number is not null
and cii.last_vld_organization_id is not null
and cii.inv_organization_id is null
and cii.inv_subinventory_name is null; -- to filter Inventory instances
select cii.instance_id,cii.lot_number,
cii.last_vld_organization_id,cii.inventory_item_id
from CSI_ITEM_INSTANCES cii
where cii.lot_number is not null
and cii.last_vld_organization_id is not null
and cii.inv_organization_id is null
and cii.inv_subinventory_name is null; -- to filter Inventory instances
select cii.instance_id,cii.serial_number,
cii.last_vld_organization_id,cii.inventory_item_id
from CSI_ITEM_INSTANCES cii
where cii.serial_number is not null
and cii.last_vld_organization_id is not null
and cii.location_type_code = 'INVENTORY'
and cii.instance_usage_code = 'IN_INVENTORY'
and nvl(cii.active_end_date,(sysdate+1)) > sysdate
and cii.inv_organization_id is not null
and cii.inv_subinventory_name is not null; -- to handle Inventory instances
select cii.instance_id,cii.lot_number,
cii.last_vld_organization_id,cii.inventory_item_id
from CSI_ITEM_INSTANCES cii
where cii.lot_number is not null
and cii.last_vld_organization_id is not null
and cii.inv_organization_id is not null
and cii.location_type_code = 'INVENTORY'
and cii.instance_usage_code = 'IN_INVENTORY'
and nvl(cii.active_end_date,(sysdate+1)) > sysdate
and cii.inv_subinventory_name is not null; */ -- to handle Inventory instances
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select CSI_TRANSACTIONS_S.nextval
into v_srl_txn_id from dual;
select CSI_TRANSACTIONS_S.nextval
into v_lot_txn_id from dual;
l_inst_tbl.DELETE;
l_srl_tbl.DELETE;
l_inst_hist_tbl.DELETE;
select serial_number_control_code
into v_srl_ctl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = vld_org_id_mig(i);
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
-- Bulk Update Instances
l_srl_ins_flag := 'Y';
UPDATE CSI_ITEM_INSTANCES
set serial_number = null,
mfg_serial_number_flag = 'N',
last_update_date = sysdate,
last_updated_by = v_user_id
where instance_id = l_inst_tbl(j);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,OLD_SERIAL_NUMBER
,NEW_SERIAL_NUMBER
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
l_inst_hist_tbl(j)
,v_srl_txn_id
,l_inst_tbl(j)
,l_srl_tbl(j)
,NULL
,SYSDATE
,SYSDATE
,v_user_id
,v_user_id
,-1
,1
);
l_inst_tbl.DELETE;
l_lot_tbl.DELETE;
l_inst_hist_tbl.DELETE;
select lot_control_code
into v_lot_ctl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = vld_org_id_mig(i);
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
-- Bulk Update Instances
FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
UPDATE CSI_ITEM_INSTANCES
set lot_number = null,
last_update_date = sysdate,
last_updated_by = v_user_id
where instance_id = l_inst_tbl(j);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,OLD_LOT_NUMBER
,NEW_LOT_NUMBER
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
l_inst_hist_tbl(j)
,v_lot_txn_id
,l_inst_tbl(j)
,l_lot_tbl(j)
,NULL
,SYSDATE
,SYSDATE
,v_user_id
,v_user_id
,-1
,1
);
l_inst_tbl.DELETE;
l_inst_hist_tbl.DELETE;
select serial_number_control_code
into v_srl_ctl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = vld_org_id_mig(i);
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
-- Bulk Update Instances
l_srl_ins_flag := 'Y';
UPDATE CSI_ITEM_INSTANCES
set active_end_date = sysdate,
instance_status_id = 1,
last_update_date = sysdate,
last_updated_by = v_user_id
where instance_id = l_inst_tbl(j);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
l_inst_hist_tbl(j)
,v_srl_txn_id
,l_inst_tbl(j)
,SYSDATE
,SYSDATE
,v_user_id
,v_user_id
,-1
,1
);
l_inst_tbl.DELETE;
l_inst_hist_tbl.DELETE;
select lot_control_code
into v_lot_ctl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = vld_org_id_mig(i);
select CSI_ITEM_INSTANCES_H_S.nextval
into l_inst_hist_tbl(l_ctr) from dual;
-- Bulk Update Instances
FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
UPDATE CSI_ITEM_INSTANCES
set active_end_date = sysdate,
instance_status_id = 1,
last_update_date = sysdate,
last_updated_by = v_user_id
where instance_id = l_inst_tbl(j);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
l_inst_hist_tbl(j)
,v_lot_txn_id
,l_inst_tbl(j)
,SYSDATE
,SYSDATE
,v_user_id
,v_user_id
,-1
,1
);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,SOURCE_LINE_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_srl_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'Serial or Lot Control got switched off' -- SOURCE_HEADER_REF
,'DATAFIX By Update_No_ctl_Srl_Lot_Inst' -- SOURCE_LINE_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_user_id
,sysdate
,v_user_id
,sysdate
,-1
,1
);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,SOURCE_LINE_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_lot_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'Serial or Lot Control got switched off' -- SOURCE_HEADER_REF
,'DATAFIX By Update_No_ctl_Srl_Lot_Inst' -- SOURCE_LINE_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_user_id
,sysdate
,v_user_id
,sysdate
,-1
,1
);
END Update_No_ctl_Srl_Lot_Inst;
PROCEDURE Create_or_Update_Shipping_Inst IS
CURSOR CSI_CUR IS
select cii.transaction_error_id,cii.inv_material_transaction_id,
null,null,null,null,null,null
from csi_txn_errors cii
where cii.processed_flag in ('E', 'R')
and cii.inv_material_transaction_id is not null;
select lot_number,ABS(primary_quantity) transaction_quantity
from mtl_transaction_lot_numbers
where transaction_id = p_txn_id;
v_last_updated_by NUMBER := fnd_global.user_id;
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select instance_status_id
into v_ins_status_id
from CSI_INSTANCE_STATUSES
where name = 'Latest';
select inventory_item_id,organization_id,revision,
subinventory_code,locator_id,abs(primary_quantity) transaction_quantity
into inventory_item_id_mig(i),organization_id_mig(i),revision_mig(i),
subinv_mig(i),locator_id_mig(i),quantity_mig(i)
from MTL_MATERIAL_TRANSACTIONS
where transaction_id = mat_txn_id_mig(i)
and ( (transaction_type_id = 33) OR
(transaction_source_type_id = 5 AND transaction_quantity < 0) );
select primary_uom_code,serial_number_control_code,lot_control_code
into v_pri_uom,v_srl_ctl,v_lot_ctl
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = organization_id_mig(i);
select master_organization_id
into v_mast_org_id
from MTL_PARAMETERS
where organization_id = organization_id_mig(i);
select comms_nl_trackable_flag
into v_nl_trackable
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = v_mast_org_id;
Update CSI_TXN_ERRORS
set processed_flag = 'D'
,error_text = 'COMMS_NL_TRACKABLE_FLAG got switched off'
,last_update_date = sysdate
where transaction_error_id = txn_error_id_mig(i);
select quantity,instance_id
into v_ins_qty,v_instance_id
from CSI_ITEM_INSTANCES
where inventory_item_id = inventory_item_id_mig(i)
and inv_organization_id = organization_id_mig(i)
and serial_number is null
and lot_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and inv_subinventory_name = subinv_mig(i)
and nvl(inv_locator_id,-999) = nvl(locator_id_mig(i),-999)
and nvl(inventory_revision,'$#$') = nvl(revision_mig(i),'$#$');
select CSI_TRANSACTIONS_S.nextval
into v_txn_id
from DUAL;
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'DATAFIX by STAGING Bump' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
);
v_err_msg := 'Error while Inserting into CSI_TRANSACTIONS '||substr(sqlerrm,1,1000);
UPDATE CSI_ITEM_INSTANCES
set quantity = quantity + quantity_mig(i),
active_end_date = null,
instance_status_id = v_ins_status_id,
last_update_date = sysdate,
last_updated_by = v_last_updated_by,
last_vld_organization_id = organization_id_mig(i)
where instance_id = v_instance_id;
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
CSI_ITEM_INSTANCES_H_S.nextval
,v_txn_id
,v_instance_id
,SYSDATE
,SYSDATE
,v_created_by
,v_last_updated_by
,-1
,1
);
Update CSI_TXN_ERRORS
set processed_flag = 'R',
last_update_date = sysdate
where transaction_error_id = txn_error_id_mig(i);
select location_id
into v_location_id
from MTL_SECONDARY_INVENTORIES
where organization_id = organization_id_mig(i)
and secondary_inventory_name = subinv_mig(i);
select location_id
into v_location_id
from HR_ORGANIZATION_UNITS
where organization_id = organization_id_mig(i);
select csi_item_instances_s.nextval
into v_instance_id
from DUAL;
-- Insert into CSI_ITEM_INSTANCES
Begin
INSERT INTO CSI_ITEM_INSTANCES(
INSTANCE_ID
,INSTANCE_NUMBER
,EXTERNAL_REFERENCE
,INVENTORY_ITEM_ID
,INVENTORY_REVISION
,INV_MASTER_ORGANIZATION_ID
,QUANTITY
,UNIT_OF_MEASURE
,ACCOUNTING_CLASS_CODE
,INSTANCE_STATUS_ID
,CUSTOMER_VIEW_FLAG
,MERCHANT_VIEW_FLAG
,SELLABLE_FLAG
,SYSTEM_ID
,INSTANCE_TYPE_CODE
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,LOCATION_TYPE_CODE
,LOCATION_ID
,INV_ORGANIZATION_ID
,INV_SUBINVENTORY_NAME
,INV_LOCATOR_ID
,INSTALL_DATE
,MANUALLY_CREATED_FLAG
,CREATION_COMPLETE_FLAG
,COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,INSTANCE_USAGE_CODE
,OWNER_PARTY_SOURCE_TABLE
,OWNER_PARTY_ID
,LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_instance_id -- INSTANCE_ID
,v_instance_id -- INSTANCE_NUMBER
,NULL -- EXTERNAL_REFERENCE
,inventory_item_id_mig(i) -- INVENTORY_ITEM_ID
,revision_mig(i) -- INVENTORY_REVISION
,v_mast_org_id -- INV_MASTER_ORGANIZATION_ID
,quantity_mig(i) -- QUANTITY
,v_pri_uom -- UNIT_OF_MEASURE (PRIMARY)
,'INV' -- ACCOUNTING_CLASS_CODE
,v_ins_status_id -- INSTANCE_STATUS_ID
,'N' -- CUSTOMER_VIEW_FLAG
,'Y' -- MERCHANT_VIEW_FLAG
,'Y' -- SELLABLE_FLAG
,NULL -- SYSTEM_ID
,NULL -- INSTANCE_TYPE_CODE
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,'INVENTORY' -- LOCATION_TYPE_CODE
,v_location_id -- LOCATION_ID
,organization_id_mig(i) -- INV_ORGANIZATION_ID
,subinv_mig(i) -- INV_SUBINVENTORY_NAME
,locator_id_mig(i) -- INV_LOCATOR_ID
,NULL -- INSTALL_DATE
,'N' -- MANUALLY_CREATED_FLAG
,'Y' -- CREATION_COMPLETE_FLAG
,'Y' -- COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'IN_INVENTORY' -- INSTANCE_USAGE_CODE
,'HZ_PARTIES' -- OWNER_PARTY_SOURCE_TABLE
,v_party_id -- OWNER_PARTY_ID
,organization_id_mig(i) -- LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
select CSI_ITEM_INSTANCES_H_S.nextval
into v_ins_history_id
from DUAL;
INSERT INTO CSI_ITEM_INSTANCES_H(
INSTANCE_HISTORY_ID
,INSTANCE_ID
,TRANSACTION_ID
,OLD_INSTANCE_NUMBER
,NEW_INSTANCE_NUMBER
,OLD_EXTERNAL_REFERENCE
,NEW_EXTERNAL_REFERENCE
,OLD_INVENTORY_ITEM_ID
,NEW_INVENTORY_ITEM_ID
,OLD_INVENTORY_REVISION
,NEW_INVENTORY_REVISION
,OLD_INV_MASTER_ORGANIZATION_ID
,NEW_INV_MASTER_ORGANIZATION_ID
,OLD_QUANTITY
,NEW_QUANTITY
,OLD_UNIT_OF_MEASURE
,NEW_UNIT_OF_MEASURE
,OLD_ACCOUNTING_CLASS_CODE
,NEW_ACCOUNTING_CLASS_CODE
,OLD_INSTANCE_STATUS_ID
,NEW_INSTANCE_STATUS_ID
,OLD_CUSTOMER_VIEW_FLAG
,NEW_CUSTOMER_VIEW_FLAG
,OLD_MERCHANT_VIEW_FLAG
,NEW_MERCHANT_VIEW_FLAG
,OLD_SELLABLE_FLAG
,NEW_SELLABLE_FLAG
,OLD_SYSTEM_ID
,NEW_SYSTEM_ID
,OLD_INSTANCE_TYPE_CODE
,NEW_INSTANCE_TYPE_CODE
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,OLD_LOCATION_TYPE_CODE
,NEW_LOCATION_TYPE_CODE
,OLD_LOCATION_ID
,NEW_LOCATION_ID
,OLD_INV_ORGANIZATION_ID
,NEW_INV_ORGANIZATION_ID
,OLD_INV_SUBINVENTORY_NAME
,NEW_INV_SUBINVENTORY_NAME
,OLD_INV_LOCATOR_ID
,NEW_INV_LOCATOR_ID
,OLD_COMPLETENESS_FLAG
,NEW_COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,FULL_DUMP_FLAG
,OLD_INST_USAGE_CODE
,NEW_INST_USAGE_CODE
,OLD_LAST_VLD_ORGANIZATION_ID
,NEW_LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_ins_history_id -- INSTANCE_HISTORY_ID
,v_instance_id -- INSTANCE_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_INSTANCE_NUMBER
,v_instance_id -- NEW_INSTANCE_NUMBER
,NULL -- OLD_EXTERNAL_REFERENCE
,NULL -- NEW_EXTERNAL_REFERENCE
,NULL -- OLD_INVENTORY_ITEM_ID
,inventory_item_id_mig(i) -- NEW_INVENTORY_ITEM_ID
,NULL -- OLD_INVENTORY_REVISION
,revision_mig(i) -- NEW_INVENTORY_REVISION
,NULL -- OLD_INV_MASTER_ORGANIZATION_ID
,v_mast_org_id -- NEW_INV_MASTER_ORGANIZATION_ID
,NULL -- OLD_QUANTITY
,quantity_mig(i) -- NEW_QUANTITY
,NULL -- OLD_UNIT_OF_MEASURE
,v_pri_uom -- NEW_UNIT_OF_MEASURE
,NULL -- OLD_ACCOUNTING_CLASS_CODE
,'INV' -- NEW_ACCOUNTING_CLASS_CODE
,NULL -- OLD_INSTANCE_STATUS_ID
,v_ins_status_id -- NEW_INSTANCE_STATUS_ID
,NULL -- OLD_CUSTOMER_VIEW_FLAG
,'N' -- NEW_CUSTOMER_VIEW_FLAG
,NULL -- OLD_MERCHANT_VIEW_FLAG
,'Y' -- NEW_MERCHANT_VIEW_FLAG
,NULL -- OLD_SELLABLE_FLAG
,NULL -- NEW_SELLABLE_FLAG
,NULL -- OLD_SYSTEM_ID
,NULL -- NEW_SYSTEM_ID
,NULL -- OLD_INSTANCE_TYPE_CODE
,NULL -- NEW_INSTANCE_TYPE_CODE
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,NULL -- OLD_LOCATION_TYPE_CODE
,'INVENTORY' -- NEW_LOCATION_TYPE_CODE
,NULL -- OLD_LOCATION_ID
,v_location_id -- NEW_LOCATION_ID
,NULL -- OLD_INV_ORGANIZATION_ID
,organization_id_mig(i) -- NEW_INV_ORGANIZATION_ID
,NULL -- OLD_INV_SUBINVENTORY_NAME
,subinv_mig(i) -- NEW_INV_SUBINVENTORY_NAME
,NULL -- OLD_INV_LOCATOR_ID
,locator_id_mig(i) -- NEW_INV_LOCATOR_ID
,NULL -- OLD_COMPLETENESS_FLAG
,'Y' -- NEW_COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'N' -- FULL_DUMP_FLAG
,NULL -- OLD_INST_USAGE_CODE
,'IN_INVENTORY' -- NEW_INST_USAGE_CODE
,NULL -- OLD_LAST_VLD_ORGANIZATION_ID
,organization_id_mig(i) -- NEW_LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
select CSI_I_PARTIES_S.nextval
into v_ins_party_id
from DUAL;
INSERT INTO CSI_I_PARTIES(
INSTANCE_PARTY_ID
,INSTANCE_ID
,PARTY_SOURCE_TABLE
,PARTY_ID
,RELATIONSHIP_TYPE_CODE
,CONTACT_FLAG
,CONTACT_IP_ID
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_id -- INSTANCE_PARTY_ID
,v_instance_id -- INSTANCE_ID
,'HZ_PARTIES' -- PARTY_SOURCE_TABLE
,v_party_id -- PARTY_ID
,'OWNER' -- RELATIONSHIP_TYPE_CODE
,'N' -- CONTACT_FLAG
,NULL -- CONTACT_IP_ID
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES '||SUBSTR(sqlerrm,1,1000);
-- Insert into CSI_I_PARTIES_H
Begin
select CSI_I_PARTIES_H_S.nextval
into v_ins_party_history_id
from DUAL;
-- Insert into CSI_I_PARTIES_H
Begin
INSERT INTO CSI_I_PARTIES_H(
INSTANCE_PARTY_HISTORY_ID
,INSTANCE_PARTY_ID
,TRANSACTION_ID
,OLD_PARTY_SOURCE_TABLE
,NEW_PARTY_SOURCE_TABLE
,OLD_PARTY_ID
,NEW_PARTY_ID
,OLD_RELATIONSHIP_TYPE_CODE
,NEW_RELATIONSHIP_TYPE_CODE
,OLD_CONTACT_FLAG
,NEW_CONTACT_FLAG
,OLD_CONTACT_IP_ID
,NEW_CONTACT_IP_ID
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_history_id -- INSTANCE_PARTY_HISTORY_ID
,v_ins_party_id -- INSTANCE_PARTY_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_PARTY_SOURCE_TABLE
,'HZ_PARTIES' -- NEW_PARTY_SOURCE_TABLE
,NULL -- OLD_PARTY_ID
,v_party_id -- NEW_PARTY_ID
,NULL -- OLD_RELATIONSHIP_TYPE_CODE
,'OWNER' -- NEW_RELATIONSHIP_TYPE_CODE
,NULL -- OLD_CONTACT_FLAG
,'N' -- NEW_CONTACT_FLAG
,NULL -- OLD_CONTACT_IP_ID
,NULL -- NEW_CONTACT_IP_ID
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,'N' -- FULL_DUMP_FLAG
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
,NULL
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES_H using the same Instance '||SUBSTR(sqlerrm,1,1000);
select quantity,instance_id
into v_ins_qty,v_instance_id
from CSI_ITEM_INSTANCES
where inventory_item_id = inventory_item_id_mig(i)
and inv_organization_id = organization_id_mig(i)
and serial_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and inv_subinventory_name = subinv_mig(i)
and nvl(inv_locator_id,-999) = nvl(locator_id_mig(i),-999)
and nvl(inventory_revision,'$#$') = nvl(revision_mig(i),'$#$')
and lot_number = lot_rec.lot_number;
select CSI_TRANSACTIONS_S.nextval
into v_txn_id
from DUAL;
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'DATAFIX By STAGING Bump' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
);
v_err_msg := 'Error while Inserting into CSI_TRANSACTIONS '||substr(sqlerrm,1,1000);
UPDATE CSI_ITEM_INSTANCES
set quantity = quantity + lot_rec.transaction_quantity,
active_end_date = null,
instance_status_id = v_ins_status_id,
last_update_date = sysdate,
last_updated_by = v_last_updated_by,
last_vld_organization_id = organization_id_mig(i)
where instance_id = v_instance_id;
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
CSI_ITEM_INSTANCES_H_S.nextval
,v_txn_id
,v_instance_id
,SYSDATE
,SYSDATE
,v_created_by
,v_last_updated_by
,-1
,1
);
Update CSI_TXN_ERRORS
set processed_flag = 'R',
last_update_date = sysdate
where transaction_error_id = txn_error_id_mig(i);
select location_id
into v_location_id
from MTL_SECONDARY_INVENTORIES
where organization_id = organization_id_mig(i)
and secondary_inventory_name = subinv_mig(i);
select location_id
into v_location_id
from HR_ORGANIZATION_UNITS
where organization_id = organization_id_mig(i);
select csi_item_instances_s.nextval
into v_instance_id
from DUAL;
-- Insert into CSI_ITEM_INSTANCES
Begin
INSERT INTO CSI_ITEM_INSTANCES(
INSTANCE_ID
,INSTANCE_NUMBER
,EXTERNAL_REFERENCE
,INVENTORY_ITEM_ID
,INVENTORY_REVISION
,INV_MASTER_ORGANIZATION_ID
,MFG_SERIAL_NUMBER_FLAG
,LOT_NUMBER
,QUANTITY
,UNIT_OF_MEASURE
,ACCOUNTING_CLASS_CODE
,INSTANCE_STATUS_ID
,CUSTOMER_VIEW_FLAG
,MERCHANT_VIEW_FLAG
,SELLABLE_FLAG
,SYSTEM_ID
,INSTANCE_TYPE_CODE
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,LOCATION_TYPE_CODE
,LOCATION_ID
,INV_ORGANIZATION_ID
,INV_SUBINVENTORY_NAME
,INV_LOCATOR_ID
,INSTALL_DATE
,MANUALLY_CREATED_FLAG
,CREATION_COMPLETE_FLAG
,COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,INSTANCE_USAGE_CODE
,OWNER_PARTY_SOURCE_TABLE
,OWNER_PARTY_ID
,LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_instance_id -- INSTANCE_ID
,v_instance_id -- INSTANCE_NUMBER
,NULL -- EXTERNAL_REFERENCE
,inventory_item_id_mig(i) -- INVENTORY_ITEM_ID
,revision_mig(i) -- INVENTORY_REVISION
,v_mast_org_id -- INV_MASTER_ORGANIZATION_ID
,'Y' -- MFG_SERIAL_NUMBER_FLAG
,lot_rec.lot_number -- LOT_NUMBER
,lot_rec.transaction_quantity -- QUANTITY
,v_pri_uom -- UNIT_OF_MEASURE (PRIMARY)
,'INV' -- ACCOUNTING_CLASS_CODE
,v_ins_status_id -- INSTANCE_STATUS_ID
,'N' -- CUSTOMER_VIEW_FLAG
,'Y' -- MERCHANT_VIEW_FLAG
,'Y' -- SELLABLE_FLAG
,NULL -- SYSTEM_ID
,NULL -- INSTANCE_TYPE_CODE
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,'INVENTORY' -- LOCATION_TYPE_CODE
,v_location_id -- LOCATION_ID
,organization_id_mig(i) -- INV_ORGANIZATION_ID
,subinv_mig(i) -- INV_SUBINVENTORY_NAME
,locator_id_mig(i) -- INV_LOCATOR_ID
,NULL -- INSTALL_DATE
,'N' -- MANUALLY_CREATED_FLAG
,'Y' -- CREATION_COMPLETE_FLAG
,'Y' -- COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'IN_INVENTORY' -- INSTANCE_USAGE_CODE
,'HZ_PARTIES' -- OWNER_PARTY_SOURCE_TABLE
,v_party_id -- OWNER_PARTY_ID
,organization_id_mig(i) -- LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
select CSI_ITEM_INSTANCES_H_S.nextval
into v_ins_history_id
from DUAL;
INSERT INTO CSI_ITEM_INSTANCES_H(
INSTANCE_HISTORY_ID
,INSTANCE_ID
,TRANSACTION_ID
,OLD_INSTANCE_NUMBER
,NEW_INSTANCE_NUMBER
,OLD_EXTERNAL_REFERENCE
,NEW_EXTERNAL_REFERENCE
,OLD_INVENTORY_ITEM_ID
,NEW_INVENTORY_ITEM_ID
,OLD_INVENTORY_REVISION
,NEW_INVENTORY_REVISION
,OLD_INV_MASTER_ORGANIZATION_ID
,NEW_INV_MASTER_ORGANIZATION_ID
,OLD_MFG_SERIAL_NUMBER_FLAG
,NEW_MFG_SERIAL_NUMBER_FLAG
,OLD_LOT_NUMBER
,NEW_LOT_NUMBER
,OLD_QUANTITY
,NEW_QUANTITY
,OLD_UNIT_OF_MEASURE
,NEW_UNIT_OF_MEASURE
,OLD_ACCOUNTING_CLASS_CODE
,NEW_ACCOUNTING_CLASS_CODE
,OLD_INSTANCE_STATUS_ID
,NEW_INSTANCE_STATUS_ID
,OLD_CUSTOMER_VIEW_FLAG
,NEW_CUSTOMER_VIEW_FLAG
,OLD_MERCHANT_VIEW_FLAG
,NEW_MERCHANT_VIEW_FLAG
,OLD_SELLABLE_FLAG
,NEW_SELLABLE_FLAG
,OLD_SYSTEM_ID
,NEW_SYSTEM_ID
,OLD_INSTANCE_TYPE_CODE
,NEW_INSTANCE_TYPE_CODE
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,OLD_LOCATION_TYPE_CODE
,NEW_LOCATION_TYPE_CODE
,OLD_LOCATION_ID
,NEW_LOCATION_ID
,OLD_INV_ORGANIZATION_ID
,NEW_INV_ORGANIZATION_ID
,OLD_INV_SUBINVENTORY_NAME
,NEW_INV_SUBINVENTORY_NAME
,OLD_INV_LOCATOR_ID
,NEW_INV_LOCATOR_ID
,OLD_COMPLETENESS_FLAG
,NEW_COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,FULL_DUMP_FLAG
,OLD_INST_USAGE_CODE
,NEW_INST_USAGE_CODE
,OLD_LAST_VLD_ORGANIZATION_ID
,NEW_LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_ins_history_id -- INSTANCE_HISTORY_ID
,v_instance_id -- INSTANCE_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_INSTANCE_NUMBER
,v_instance_id -- NEW_INSTANCE_NUMBER
,NULL -- OLD_EXTERNAL_REFERENCE
,NULL -- NEW_EXTERNAL_REFERENCE
,NULL -- OLD_INVENTORY_ITEM_ID
,inventory_item_id_mig(i) -- NEW_INVENTORY_ITEM_ID
,NULL -- OLD_INVENTORY_REVISION
,revision_mig(i) -- NEW_INVENTORY_REVISION
,NULL -- OLD_INV_MASTER_ORGANIZATION_ID
,v_mast_org_id -- NEW_INV_MASTER_ORGANIZATION_ID
,NULL -- OLD_MFG_SERIAL_NUMBER_FLAG
,'Y' -- NEW_MFG_SERIAL_NUMBER_FLAG
,NULL -- OLD_LOT_NUMBER
,lot_rec.lot_number -- NEW_LOT_NUMBER
,NULL -- OLD_QUANTITY
,lot_rec.transaction_quantity -- NEW_QUANTITY
,NULL -- OLD_UNIT_OF_MEASURE
,v_pri_uom -- NEW_UNIT_OF_MEASURE
,NULL -- OLD_ACCOUNTING_CLASS_CODE
,'INV' -- NEW_ACCOUNTING_CLASS_CODE
,NULL -- OLD_INSTANCE_STATUS_ID
,v_ins_status_id -- NEW_INSTANCE_STATUS_ID
,NULL -- OLD_CUSTOMER_VIEW_FLAG
,'N' -- NEW_CUSTOMER_VIEW_FLAG
,NULL -- OLD_MERCHANT_VIEW_FLAG
,'Y' -- NEW_MERCHANT_VIEW_FLAG
,NULL -- OLD_SELLABLE_FLAG
,NULL -- NEW_SELLABLE_FLAG
,NULL -- OLD_SYSTEM_ID
,NULL -- NEW_SYSTEM_ID
,NULL -- OLD_INSTANCE_TYPE_CODE
,NULL -- NEW_INSTANCE_TYPE_CODE
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,NULL -- OLD_LOCATION_TYPE_CODE
,'INVENTORY' -- NEW_LOCATION_TYPE_CODE
,NULL -- OLD_LOCATION_ID
,v_location_id -- NEW_LOCATION_ID
,NULL -- OLD_INV_ORGANIZATION_ID
,organization_id_mig(i) -- NEW_INV_ORGANIZATION_ID
,NULL -- OLD_INV_SUBINVENTORY_NAME
,subinv_mig(i) -- NEW_INV_SUBINVENTORY_NAME
,NULL -- OLD_INV_LOCATOR_ID
,locator_id_mig(i) -- NEW_INV_LOCATOR_ID
,NULL -- OLD_COMPLETENESS_FLAG
,'Y' -- NEW_COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'N' -- FULL_DUMP_FLAG
,NULL -- OLD_INST_USAGE_CODE
,'IN_INVENTORY' -- NEW_INST_USAGE_CODE
,NULL -- OLD_LAST_VLD_ORGANIZATION_ID
,organization_id_mig(i) -- NEW_LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
select CSI_I_PARTIES_S.nextval
into v_ins_party_id
from DUAL;
INSERT INTO CSI_I_PARTIES(
INSTANCE_PARTY_ID
,INSTANCE_ID
,PARTY_SOURCE_TABLE
,PARTY_ID
,RELATIONSHIP_TYPE_CODE
,CONTACT_FLAG
,CONTACT_IP_ID
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_id -- INSTANCE_PARTY_ID
,v_instance_id -- INSTANCE_ID
,'HZ_PARTIES' -- PARTY_SOURCE_TABLE
,v_party_id -- PARTY_ID
,'OWNER' -- RELATIONSHIP_TYPE_CODE
,'N' -- CONTACT_FLAG
,NULL -- CONTACT_IP_ID
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES '||SUBSTR(sqlerrm,1,1000);
-- Insert into CSI_I_PARTIES_H
Begin
select CSI_I_PARTIES_H_S.nextval
into v_ins_party_history_id
from DUAL;
-- Insert into CSI_I_PARTIES_H
Begin
INSERT INTO CSI_I_PARTIES_H(
INSTANCE_PARTY_HISTORY_ID
,INSTANCE_PARTY_ID
,TRANSACTION_ID
,OLD_PARTY_SOURCE_TABLE
,NEW_PARTY_SOURCE_TABLE
,OLD_PARTY_ID
,NEW_PARTY_ID
,OLD_RELATIONSHIP_TYPE_CODE
,NEW_RELATIONSHIP_TYPE_CODE
,OLD_CONTACT_FLAG
,NEW_CONTACT_FLAG
,OLD_CONTACT_IP_ID
,NEW_CONTACT_IP_ID
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_history_id -- INSTANCE_PARTY_HISTORY_ID
,v_ins_party_id -- INSTANCE_PARTY_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_PARTY_SOURCE_TABLE
,'HZ_PARTIES' -- NEW_PARTY_SOURCE_TABLE
,NULL -- OLD_PARTY_ID
,v_party_id -- NEW_PARTY_ID
,NULL -- OLD_RELATIONSHIP_TYPE_CODE
,'OWNER' -- NEW_RELATIONSHIP_TYPE_CODE
,NULL -- OLD_CONTACT_FLAG
,'N' -- NEW_CONTACT_FLAG
,NULL -- OLD_CONTACT_IP_ID
,NULL -- NEW_CONTACT_IP_ID
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,'N' -- FULL_DUMP_FLAG
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
,NULL
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES_H using the same Instance '||SUBSTR(sqlerrm,1,1000);
Update csi_txn_errors
set processed_flag = 'R',
last_update_date = sysdate
where transaction_error_id = txn_error_id_mig(i);
END Create_or_Update_Shipping_Inst;
SELECT msn.serial_number,
msn.inventory_item_id,
msn.current_organization_id,
msi.lot_control_code
FROM mtl_serial_numbers msn,
mtl_system_items msi
WHERE msn.current_status = 1 -- predefined state (for rma'ed serials)
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = msn.current_organization_id
AND msi.serial_number_control_code = 6 -- serialized at so issue
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y';
SELECT 'Y'
INTO l_last_rma_processed
FROM sys.dual
WHERE exists
(SELECT '1' FROM csi_transactions
WHERE inv_material_transaction_id = l_last_mtl_txn_id);
SELECT instance_id,
object_version_number,
location_type_code,
instance_usage_code
INTO l_instance_rec.instance_id,
l_instance_rec.object_version_number,
l_instance_rec.location_type_code,
l_instance_rec.instance_usage_code
FROM csi_item_instances
WHERE inventory_item_id = l_item_id_tab(l_ind)
AND serial_number = l_serial_number_tab(l_ind);
SELECT object_version_number
INTO l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
SELECT nvl(mssi.location_id, haou.location_id)
INTO l_instance_rec.location_id
FROM mtl_secondary_inventories mssi,
hr_all_organization_units haou
WHERE mssi.organization_id = l_instance_rec.inv_organization_id
AND mssi.secondary_inventory_name = l_instance_rec.inv_subinventory_name
AND haou.organization_id = mssi.organization_id;
update_instance(
p_txn_rec => l_txn_rec,
p_instance_rec => l_instance_rec,
p_parties_tbl => l_parties_tbl,
p_pty_accts_tbl => l_pty_accts_tbl,
x_return_status => l_return_status,
x_error_message => l_error_message);
select moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.lot_number lot_number
, msi.primary_uom_code primary_uom_code
, sum(moq.transaction_quantity) onhand_qty
from
mtl_system_items msi
, mtl_onhand_quantities moq
where msi.inventory_item_id = moq.inventory_item_id
and msi.organization_id = moq.organization_id
and msi.serial_number_control_code in (1,6) -- No Serial control and at SO Issue Items
group by
moq.organization_id
, moq.inventory_item_id
, moq.revision
, moq.subinventory_code
, moq.locator_id
, moq.lot_number
, msi.primary_uom_code;
v_last_updated_by NUMBER := fnd_global.user_id;
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select instance_status_id
into v_ins_status_id
from CSI_INSTANCE_STATUSES
where name = 'Latest';
-- to delete the records if the Insert fails. Assigning NULL will ensure that we do not delete
-- wrong set of previous records if the insert fails for the next.
v_txn_id := -99999;
select master_organization_id
into v_mast_org_id
from MTL_PARAMETERS
where organization_id = organization_id_mig(i);
select comms_nl_trackable_flag
into v_nl_trackable
from MTL_SYSTEM_ITEMS
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = v_mast_org_id;
select count(*)
into l_error_count
from CSI_TXN_ERRORS cii,
MTL_MATERIAL_TRANSACTIONS mmt
where cii.inv_material_transaction_id is not null
and cii.inv_material_transaction_id = mmt.transaction_id
and cii.processed_flag in ('E','R')
and mmt.inventory_item_id = inventory_item_id_mig(i)
and mmt.organization_id = organization_id_mig(i);
select location_id
into v_location_id
from MTL_SECONDARY_INVENTORIES
where organization_id = organization_id_mig(i)
and secondary_inventory_name = subinv_mig(i);
select location_id
into v_location_id
from HR_ORGANIZATION_UNITS
where organization_id = organization_id_mig(i);
select quantity,instance_id,active_end_date
into v_ins_qty,v_instance_id,v_end_date
from CSI_ITEM_INSTANCES
where inventory_item_id = inventory_item_id_mig(i)
and inv_organization_id = organization_id_mig(i)
and serial_number is null
and location_type_code = 'INVENTORY'
and instance_usage_code = 'IN_INVENTORY'
and inv_subinventory_name = subinv_mig(i)
and nvl(inv_locator_id,-999) = nvl(locator_id_mig(i),-999)
and nvl(inventory_revision,'$#$') = nvl(revision_mig(i),'$#$')
and nvl(lot_number,'$#$') = nvl(lot_mig(i),'$#$');
UPDATE CSI_ITEM_INSTANCES
set quantity = quantity_mig(i)
,active_end_date = decode(quantity_mig(i),0,sysdate,null)
,instance_status_id = decode(quantity_mig(i),0,1,v_ins_status_id)
,last_update_date = sysdate
,last_updated_by = v_last_updated_by
,last_vld_organization_id = organization_id_mig(i)
where instance_id = v_instance_id;
select CSI_TRANSACTIONS_S.nextval
into v_txn_id
from DUAL;
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'DATAFIX By IB-INV Synch' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
);
v_err_msg := 'Error while Inserting into CSI_TRANSACTIONS '||substr(sqlerrm,1,1000);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,OLD_QUANTITY
,NEW_QUANTITY
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
CSI_ITEM_INSTANCES_H_S.nextval
,v_txn_id
,v_instance_id
,v_ins_qty
,quantity_mig(i)
,SYSDATE
,SYSDATE
,v_created_by
,v_last_updated_by
,-1
,1
);
-- For each record, we insert a record into CSI_TRANSACTIONS. This Transaction ID will be used
-- to populate the History Record.
Begin
select CSI_TRANSACTIONS_S.nextval
into v_txn_id
from DUAL;
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
v_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'DATAFIX By IB-INV Synch' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
);
v_err_msg := 'Error while Inserting into CSI_TRANSACTIONS '||substr(sqlerrm,1,1000);
select csi_item_instances_s.nextval
into v_instance_id
from DUAL;
-- Insert into CSI_ITEM_INSTANCES
Begin
INSERT INTO CSI_ITEM_INSTANCES(
INSTANCE_ID
,INSTANCE_NUMBER
,EXTERNAL_REFERENCE
,INVENTORY_ITEM_ID
,INVENTORY_REVISION
,INV_MASTER_ORGANIZATION_ID
,MFG_SERIAL_NUMBER_FLAG
,LOT_NUMBER
,QUANTITY
,UNIT_OF_MEASURE
,ACCOUNTING_CLASS_CODE
,INSTANCE_CONDITION_ID
,INSTANCE_STATUS_ID
,CUSTOMER_VIEW_FLAG
,MERCHANT_VIEW_FLAG
,SELLABLE_FLAG
,SYSTEM_ID
,INSTANCE_TYPE_CODE
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,LOCATION_TYPE_CODE
,LOCATION_ID
,INV_ORGANIZATION_ID
,INV_SUBINVENTORY_NAME
,INV_LOCATOR_ID
,PA_PROJECT_ID
,PA_PROJECT_TASK_ID
,IN_TRANSIT_ORDER_LINE_ID
,WIP_JOB_ID
,PO_ORDER_LINE_ID
,LAST_OE_ORDER_LINE_ID
,LAST_OE_RMA_LINE_ID
,LAST_PO_PO_LINE_ID
,LAST_OE_PO_NUMBER
,LAST_WIP_JOB_ID
,LAST_PA_PROJECT_ID
,LAST_PA_TASK_ID
,LAST_OE_AGREEMENT_ID
,INSTALL_DATE
,MANUALLY_CREATED_FLAG
,RETURN_BY_DATE
,ACTUAL_RETURN_DATE
,CREATION_COMPLETE_FLAG
,COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,INSTANCE_USAGE_CODE
,OWNER_PARTY_SOURCE_TABLE
,OWNER_PARTY_ID
,LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_instance_id -- INSTANCE_ID
,v_instance_id -- INSTANCE_NUMBER
,NULL -- EXTERNAL_REFERENCE
,INVENTORY_ITEM_ID_mig(i) -- INVENTORY_ITEM_ID
,revision_mig(i) -- INVENTORY_REVISION
,v_mast_org_id -- INV_MASTER_ORGANIZATION_ID
,v_mfg_srl_flag -- MFG_SERIAL_NUMBER_FLAG
,LOT_mig(i) -- LOT_NUMBER
,quantity_mig(i) -- QUANTITY
,uom_code_mig(i) -- UNIT_OF_MEASURE (PRIMARY)
,'INV' -- ACCOUNTING_CLASS_CODE
,v_ins_condition_id -- INSTANCE_CONDITION_ID
,v_ins_status_id -- INSTANCE_STATUS_ID
,'N' -- CUSTOMER_VIEW_FLAG
,'Y' -- MERCHANT_VIEW_FLAG
,'Y' -- SELLABLE_FLAG
,NULL -- SYSTEM_ID
,NULL -- INSTANCE_TYPE_CODE
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,'INVENTORY' -- LOCATION_TYPE_CODE
,v_location_id -- LOCATION_ID
,ORGANIZATION_ID_mig(i) -- INV_ORGANIZATION_ID
,subinv_mig(i) -- INV_SUBINVENTORY_NAME
,LOCATOR_ID_mig(i) -- INV_LOCATOR_ID
,NULL -- PA_PROJECT_ID
,NULL -- PA_PROJECT_TASK_ID
,NULL -- IN_TRANSIT_ORDER_LINE_ID
,NULL -- WIP_JOB_ID
,NULL -- PO_ORDER_LINE_ID
,NULL -- LAST_OE_ORDER_LINE_ID
,NULL -- LAST_OE_RMA_LINE_ID
,NULL -- LAST_PO_PO_LINE_ID
,NULL -- LAST_OE_PO_NUMBER
,NULL -- LAST_WIP_JOB_ID
,NULL -- LAST_PA_PROJECT_ID
,NULL -- LAST_PA_TASK_ID
,NULL -- LAST_OE_AGREEMENT_ID
,NULL -- INSTALL_DATE
,'N' -- MANUALLY_CREATED_FLAG
,NULL -- RETURN_BY_DATE
,NULL -- ACTUAL_RETURN_DATE
,'Y' -- CREATION_COMPLETE_FLAG
,'Y' -- COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'IN_INVENTORY' -- INSTANCE_USAGE_CODE
,'HZ_PARTIES' -- OWNER_PARTY_SOURCE_TABLE
,v_party_id -- OWNER_PARTY_ID
,ORGANIZATION_ID_mig(i) -- LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
select CSI_ITEM_INSTANCES_H_S.nextval
into v_ins_history_id
from DUAL;
INSERT INTO CSI_ITEM_INSTANCES_H(
INSTANCE_HISTORY_ID
,INSTANCE_ID
,TRANSACTION_ID
,OLD_INSTANCE_NUMBER
,NEW_INSTANCE_NUMBER
,OLD_EXTERNAL_REFERENCE
,NEW_EXTERNAL_REFERENCE
,OLD_INVENTORY_ITEM_ID
,NEW_INVENTORY_ITEM_ID
,OLD_INVENTORY_REVISION
,NEW_INVENTORY_REVISION
,OLD_INV_MASTER_ORGANIZATION_ID
,NEW_INV_MASTER_ORGANIZATION_ID
,OLD_MFG_SERIAL_NUMBER_FLAG
,NEW_MFG_SERIAL_NUMBER_FLAG
,OLD_LOT_NUMBER
,NEW_LOT_NUMBER
,OLD_QUANTITY
,NEW_QUANTITY
,OLD_UNIT_OF_MEASURE
,NEW_UNIT_OF_MEASURE
,OLD_ACCOUNTING_CLASS_CODE
,NEW_ACCOUNTING_CLASS_CODE
,OLD_INSTANCE_CONDITION_ID
,NEW_INSTANCE_CONDITION_ID
,OLD_INSTANCE_STATUS_ID
,NEW_INSTANCE_STATUS_ID
,OLD_CUSTOMER_VIEW_FLAG
,NEW_CUSTOMER_VIEW_FLAG
,OLD_MERCHANT_VIEW_FLAG
,NEW_MERCHANT_VIEW_FLAG
,OLD_SELLABLE_FLAG
,NEW_SELLABLE_FLAG
,OLD_SYSTEM_ID
,NEW_SYSTEM_ID
,OLD_INSTANCE_TYPE_CODE
,NEW_INSTANCE_TYPE_CODE
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,OLD_LOCATION_TYPE_CODE
,NEW_LOCATION_TYPE_CODE
,OLD_LOCATION_ID
,NEW_LOCATION_ID
,OLD_INV_ORGANIZATION_ID
,NEW_INV_ORGANIZATION_ID
,OLD_INV_SUBINVENTORY_NAME
,NEW_INV_SUBINVENTORY_NAME
,OLD_INV_LOCATOR_ID
,NEW_INV_LOCATOR_ID
,OLD_PA_PROJECT_ID
,NEW_PA_PROJECT_ID
,OLD_PA_PROJECT_TASK_ID
,NEW_PA_PROJECT_TASK_ID
,OLD_IN_TRANSIT_ORDER_LINE_ID
,NEW_IN_TRANSIT_ORDER_LINE_ID
,OLD_WIP_JOB_ID
,NEW_WIP_JOB_ID
,OLD_PO_ORDER_LINE_ID
,NEW_PO_ORDER_LINE_ID
,OLD_COMPLETENESS_FLAG
,NEW_COMPLETENESS_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,FULL_DUMP_FLAG
,OLD_INST_USAGE_CODE
,NEW_INST_USAGE_CODE
,OLD_LAST_VLD_ORGANIZATION_ID
,NEW_LAST_VLD_ORGANIZATION_ID
)
VALUES(
v_ins_history_id -- INSTANCE_HISTORY_ID
,v_instance_id -- INSTANCE_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_INSTANCE_NUMBER
,v_instance_id -- NEW_INSTANCE_NUMBER
,NULL -- OLD_EXTERNAL_REFERENCE
,NULL -- NEW_EXTERNAL_REFERENCE
,NULL -- OLD_INVENTORY_ITEM_ID
,INVENTORY_ITEM_ID_mig(i) -- NEW_INVENTORY_ITEM_ID
,NULL -- OLD_INVENTORY_REVISION
,revision_mig(i) -- NEW_INVENTORY_REVISION
,NULL -- OLD_INV_MASTER_ORGANIZATION_ID
,v_mast_org_id -- NEW_INV_MASTER_ORGANIZATION_ID
,NULL -- OLD_MFG_SERIAL_NUMBER_FLAG
,v_mfg_srl_flag -- NEW_MFG_SERIAL_NUMBER_FLAG
,NULL -- OLD LOT
,LOT_mig(i) -- NEW LOT
,NULL -- OLD_QUANTITY
,quantity_mig(i) -- NEW_QUANTITY
,NULL -- OLD_UNIT_OF_MEASURE
,uom_code_mig(i) -- NEW_UNIT_OF_MEASURE
,NULL -- OLD_ACCOUNTING_CLASS_CODE
,'INV' -- NEW_ACCOUNTING_CLASS_CODE
,NULL -- OLD_INSTANCE_CONDITION_ID
,v_ins_condition_id -- NEW_INSTANCE_CONDITION_ID
,NULL -- OLD_INSTANCE_STATUS_ID
,v_ins_status_id -- NEW_INSTANCE_STATUS_ID
,NULL -- OLD_CUSTOMER_VIEW_FLAG
,'N' -- NEW_CUSTOMER_VIEW_FLAG
,NULL -- OLD_MERCHANT_VIEW_FLAG
,'Y' -- NEW_MERCHANT_VIEW_FLAG
,NULL -- OLD_SELLABLE_FLAG
,NULL -- NEW_SELLABLE_FLAG
,NULL -- OLD_SYSTEM_ID
,NULL -- NEW_SYSTEM_ID
,NULL -- OLD_INSTANCE_TYPE_CODE
,NULL -- NEW_INSTANCE_TYPE_CODE
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,NULL -- OLD_LOCATION_TYPE_CODE
,'INVENTORY' -- NEW_LOCATION_TYPE_CODE
,NULL -- OLD_LOCATION_ID
,v_location_id -- NEW_LOCATION_ID
,NULL -- OLD_INV_ORGANIZATION_ID
,ORGANIZATION_ID_mig(i) -- NEW_INV_ORGANIZATION_ID
,NULL -- OLD_INV_SUBINVENTORY_NAME
,subinv_mig(i) -- NEW_INV_SUBINVENTORY_NAME
,NULL -- OLD_INV_LOCATOR_ID
,LOCATOR_ID_mig(i) -- NEW_INV_LOCATOR_ID
,NULL -- OLD_PA_PROJECT_ID
,NULL -- NEW_PA_PROJECT_ID
,NULL -- OLD_PA_PROJECT_TASK_ID
,NULL -- NEW_PA_PROJECT_TASK_ID
,NULL -- OLD_IN_TRANSIT_ORDER_LINE_ID
,NULL -- NEW_IN_TRANSIT_ORDER_LINE_ID
,NULL -- OLD_WIP_JOB_ID
,NULL -- NEW_WIP_JOB_ID
,NULL -- OLD_PO_ORDER_LINE_ID
,NULL -- NEW_PO_ORDER_LINE_ID
,NULL -- OLD_COMPLETENESS_FLAG
,'Y' -- NEW_COMPLETENESS_FLAG
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
,'N' -- FULL_DUMP_FLAG
,NULL -- OLD_INST_USAGE_CODE
,'IN_INVENTORY' -- NEW_INST_USAGE_CODE
,NULL -- OLD_LAST_VLD_ORGANIZATION_ID
,ORGANIZATION_ID_mig(i) -- NEW_LAST_VLD_ORGANIZATION_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
select CSI_I_PARTIES_S.nextval
into v_ins_party_id
from DUAL;
INSERT INTO CSI_I_PARTIES(
INSTANCE_PARTY_ID
,INSTANCE_ID
,PARTY_SOURCE_TABLE
,PARTY_ID
,RELATIONSHIP_TYPE_CODE
,CONTACT_FLAG
,CONTACT_IP_ID
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_id -- INSTANCE_PARTY_ID
,v_instance_id -- INSTANCE_ID
,'HZ_PARTIES' -- PARTY_SOURCE_TABLE
,v_party_id -- PARTY_ID
,'OWNER' -- RELATIONSHIP_TYPE_CODE
,'N' -- CONTACT_FLAG
,NULL -- CONTACT_IP_ID
,SYSDATE -- ACTIVE_START_DATE
,NULL -- ACTIVE_END_DATE
,v_created_by -- CREATED_BY
,sysdate -- CREATION_DATE
,v_last_updated_by -- LAST_UPDATED_BY
,sysdate -- LAST_UPDATE_DATE
,-1 -- LAST_UPDATE_LOGIN
,1 -- OBJECT_VERSION_NUMBER
,NULL -- SECURITY_GROUP_ID
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES '||SUBSTR(sqlerrm,1,1000);
-- Insert into CSI_I_PARTIES_H
Begin
select CSI_I_PARTIES_H_S.nextval
into v_ins_party_history_id
from DUAL;
-- Insert into CSI_I_PARTIES_H
Begin
INSERT INTO CSI_I_PARTIES_H(
INSTANCE_PARTY_HISTORY_ID
,INSTANCE_PARTY_ID
,TRANSACTION_ID
,OLD_PARTY_SOURCE_TABLE
,NEW_PARTY_SOURCE_TABLE
,OLD_PARTY_ID
,NEW_PARTY_ID
,OLD_RELATIONSHIP_TYPE_CODE
,NEW_RELATIONSHIP_TYPE_CODE
,OLD_CONTACT_FLAG
,NEW_CONTACT_FLAG
,OLD_CONTACT_IP_ID
,NEW_CONTACT_IP_ID
,OLD_ACTIVE_START_DATE
,NEW_ACTIVE_START_DATE
,OLD_ACTIVE_END_DATE
,NEW_ACTIVE_END_DATE
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
VALUES(
v_ins_party_history_id -- INSTANCE_PARTY_HISTORY_ID
,v_ins_party_id -- INSTANCE_PARTY_ID
,v_txn_id -- TRANSACTION_ID
,NULL -- OLD_PARTY_SOURCE_TABLE
,'HZ_PARTIES' -- NEW_PARTY_SOURCE_TABLE
,NULL -- OLD_PARTY_ID
,v_party_id -- NEW_PARTY_ID
,NULL -- OLD_RELATIONSHIP_TYPE_CODE
,'OWNER' -- NEW_RELATIONSHIP_TYPE_CODE
,NULL -- OLD_CONTACT_FLAG
,'N' -- NEW_CONTACT_FLAG
,NULL -- OLD_CONTACT_IP_ID
,NULL -- NEW_CONTACT_IP_ID
,NULL -- OLD_ACTIVE_START_DATE
,SYSDATE -- NEW_ACTIVE_START_DATE
,NULL -- OLD_ACTIVE_END_DATE
,NULL -- NEW_ACTIVE_END_DATE
,'N' -- FULL_DUMP_FLAG
,v_created_by
,sysdate
,v_last_updated_by
,sysdate
,-1
,1
,NULL
);
v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
||' into CSI_I_PARTIES_H using the same Instance '||SUBSTR(sqlerrm,1,1000);
SELECT cii.transaction_error_id,
mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_id
FROM csi_txn_errors cii,
mtl_material_transactions mmt
WHERE cii.processed_flag in ('E', 'R')
AND cii.inv_material_transaction_id is not null
AND mmt.transaction_id = cii.inv_material_transaction_id;
SELECT transaction_type_id
INTO v_txn_type_id
FROM csi_txn_types
WHERE source_transaction_type = 'DATA_CORRECTION';
SELECT serial_number_control_code,
lot_control_code
INTO v_srl_ctl,
v_lot_ctl
FROM mtl_system_items
WHERE inventory_item_id = item_id(i)
AND organization_id = inv_org_id(i);
UPDATE csi_txn_errors
SET processed_flag = 'D',
error_text = 'Data fix done - mark_error_transactions',
last_update_date = sysdate
WHERE transaction_error_id = l_upd_txn_tbl(i);
select cii.instance_id,cii.inventory_item_id,cii.inv_organization_id,
cii.inv_subinventory_name,cii.inv_locator_id,
cii.inventory_revision,cii.lot_number,cii.quantity
from CSI_ITEM_INSTANCES cii,
MTL_SYSTEM_ITEMS msi
where cii.location_type_code = 'INVENTORY'
and cii.instance_usage_code = 'IN_INVENTORY'
and cii.serial_number is NULL
and msi.inventory_item_id = cii.inventory_item_id
and msi.organization_id = cii.inv_master_organization_id
and nvl(msi.comms_nl_trackable_flag,'N') = 'Y';
select transaction_type_id
into v_txn_type_id
from CSI_TXN_TYPES
where SOURCE_TRANSACTION_TYPE = 'DATA_CORRECTION';
select CSI_TRANSACTIONS_S.nextval
into l_txn_id from dual;
select count(*)
into l_error_count
from CSI_TXN_ERRORS cii,
MTL_MATERIAL_TRANSACTIONS mmt
where cii.inv_material_transaction_id is not null
and cii.inv_material_transaction_id = mmt.transaction_id
and cii.processed_flag in ('E','R')
and mmt.inventory_item_id = inventory_item_id_mig(i)
and mmt.organization_id = organization_id_mig(i);
select NVL(sum(transaction_quantity),0)
into v_qty
from MTL_ONHAND_QUANTITIES
where inventory_item_id = inventory_item_id_mig(i)
and organization_id = organization_id_mig(i)
and subinventory_code = subinv_mig(i)
and nvl(locator_id,-999) = nvl(locator_id_mig(i),-999)
and nvl(revision,'$#$') = nvl(revision_mig(i),'$#$')
and nvl(lot_number,'$#$') = nvl(lot_mig(i),'$#$');
UPDATE CSI_ITEM_INSTANCES
set quantity = v_qty,
active_end_date = decode(v_qty,0,sysdate,active_end_date),
instance_status_id = decode(v_qty,0,1,instance_status_id),
last_update_date = sysdate,
last_updated_by = l_user_id
where instance_id = instance_id_mig(i);
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,TRANSACTION_ID
,INSTANCE_ID
,CREATION_DATE
,LAST_UPDATE_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES
(
CSI_ITEM_INSTANCES_H_S.nextval
,l_txn_id
,instance_id_mig(i)
,SYSDATE
,SYSDATE
,l_user_id
,l_user_id
,-1
,1
);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
l_txn_id -- TRANSACTION_ID
,SYSDATE -- TRANSACTION_DATE
,SYSDATE -- SOURCE_TRANSACTION_DATE
,'Reverse Synch' -- SOURCE_HEADER_REF
,v_txn_type_id -- TRANSACTION_TYPE_ID
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1
);
SELECT m_msi.concatenated_segments,
m_msi.comms_nl_trackable_flag,
c_msi.comms_nl_trackable_flag,
m_mp.organization_code,
c_mp.organization_code
FROM mtl_system_items_b c_msi, -- Child Items
mtl_parameters c_mp, -- Child Parameters
mtl_system_items_kfv m_msi, -- Master Items
mtl_parameters m_mp -- Master Parameters
WHERE m_mp.organization_id = m_mp.master_organization_id
AND m_msi.organization_id = m_mp.organization_id
AND m_msi.organization_id = c_mp.master_organization_id
AND c_mp.master_organization_id = m_mp.organization_id
AND c_msi.organization_id = c_mp.organization_id
AND m_msi.organization_id <> c_mp.organization_id
AND c_msi.organization_id <> m_msi.organization_id
AND c_msi.inventory_item_id = m_msi.inventory_item_id
AND nvl(m_msi.comms_nl_trackable_flag,'N') <> nvl(c_msi.comms_nl_trackable_flag,'N')
AND EXISTS (SELECT 1 from HR_ALL_ORGANIZATION_UNITS haou
WHERE date_to IS NULL
AND haou.organization_id =m_msi.organization_id)
AND EXISTS (SELECT 1 from HR_ALL_ORGANIZATION_UNITS haou
WHERE date_to IS NULL
AND haou.organization_id =c_msi.organization_id)
ORDER BY m_msi.inventory_item_id;
SELECT /*+ parallel(a) parallel(c) */
a.instance_id,
a.inventory_item_id,
a.location_type_code,
a.location_id,
a.inv_organization_id,
a.inv_subinventory_name,
a.instance_usage_code,
a.quantity,
a.active_end_date,
a.inventory_revision,
a.inv_locator_id,
a.lot_number,
a.owner_party_id
FROM csi_item_instances a,
mtl_system_items_b c
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM csi_item_instances b
WHERE b.inventory_item_id = a.inventory_item_id
AND b.location_type_code = a.location_type_code
-- AND b.location_id = a.location_id
AND b.serial_number is null
AND b.inv_organization_id = a.inv_organization_id
AND b.inv_subinventory_name = a.inv_subinventory_name
AND b.instance_usage_code = a.instance_usage_code
AND nvl(b.inventory_revision,'$#$') = nvl(a.inventory_revision,'$#$')
AND nvl(b.inv_locator_id,-999) = nvl(a.inv_locator_id,-999)
AND nvl(b.lot_number,'$#$')= nvl(a.lot_number,'$#$')
AND b.owner_party_id = a.owner_party_id
AND b.location_type_code = 'INVENTORY'
AND b.instance_usage_code = 'IN_INVENTORY')
-- AND b.active_end_date IS NULL)
-- AND a.active_end_date IS NULL
AND a.inventory_item_id = c.inventory_item_id
AND a.inv_organization_id = c.organization_id
AND a.serial_number is null
AND c.serial_number_control_code IN (1,6);
SELECT a.instance_id,
a.inventory_item_id,
a.location_type_code,
a.location_id,
a.inv_organization_id,
a.inv_subinventory_name,
a.instance_usage_code,
a.quantity,
a.active_end_date,
a.inventory_revision,
a.inv_locator_id,
a.lot_number,
a.owner_party_id
FROM csi_item_instances a,
mtl_system_items_b c
WHERE a.ROWID = (SELECT MIN(b.ROWID)
FROM csi_item_instances b
WHERE b.inventory_item_id = a.inventory_item_id
AND b.location_type_code = a.location_type_code
-- AND b.location_id = a.location_id
AND b.serial_number is null
AND b.inv_organization_id = a.inv_organization_id
AND b.inv_subinventory_name = a.inv_subinventory_name
AND b.instance_usage_code = a.instance_usage_code
AND nvl(b.inventory_revision,'$#$') = nvl(a.inventory_revision,'$#$')
AND nvl(b.inv_locator_id,-999) = nvl(a.inv_locator_id,-999)
AND nvl(b.lot_number,'$#$')= nvl(a.lot_number,'$#$')
AND b.owner_party_id = a.owner_party_id
AND b.location_type_code = 'INVENTORY'
AND b.instance_usage_code = 'IN_INVENTORY')
-- AND b.active_end_date IS NULL)
AND a.inventory_item_id = c.inventory_item_id
AND a.inv_organization_id = c.organization_id
AND a.serial_number is null
AND c.serial_number_control_code IN (1,6);
DELETE FROM csi_item_instances_h
WHERE instance_id=i.instance_id;
DELETE FROM csi_i_parties_h
WHERE instance_party_id in (select instance_party_id from csi_i_parties
WHERE instance_id=i.instance_id);
DELETE FROM csi_i_parties
WHERE instance_id=i.instance_id;
DELETE FROM csi_item_instances
WHERE instance_id=i.instance_id;
UPDATE csi_item_instances
SET quantity=j.quantity+temp_quantity,
instance_status_id = l_status_id,
active_end_date = l_active_end_date
WHERE instance_id=j.instance_id;
SELECT cii.transaction_error_id,
cii.inv_material_transaction_id,
cii.error_text,
mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_quantity,
mmt.trx_source_line_id,
mmt.revision
FROM csi_txn_errors cii,
mtl_material_transactions mmt
WHERE cii.processed_flag = 'E'
AND cii.inv_material_transaction_id is not null
AND mmt.transaction_id = cii.inv_material_transaction_id
AND mmt.transaction_type_id = 15; -- RMA
SELECT ctld.sub_type_id, ctld.instance_id
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctld
WHERE ctl.source_transaction_table = 'OE_ORDER_LINES_ALL'
AND ctl.source_transaction_id = p_rma_line_id
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.source_transaction_flag = 'Y';
SELECT lot_number,transaction_quantity
FROM mtl_transaction_lot_numbers
WHERE transaction_id = p_txn_id;
SELECT instance_id,
instance_number,
last_vld_organization_id,
quantity,
lot_number,
inventory_revision ,
owner_party_account_id account_id,
owner_party_id party_id
FROM csi_item_instances,
hz_parties
WHERE inventory_item_id = p_item_id
AND owner_party_account_id = decode(p_chg_owner,'Y',owner_party_account_id,p_customer_id)
AND instance_usage_code = 'OUT_OF_ENTERPRISE'
AND nvl(lot_number,'$#$') = nvl(p_lot_number,'$#$')
AND nvl(inventory_revision,'$#$') = nvl(p_revision,'$#$')
AND party_id = owner_party_id
ORDER BY party_name asc, quantity desc;
SELECT hp.party_name
INTO l_customer_name
FROM hz_cust_accounts hca,
hz_parties hp
WHERE hca.cust_account_id = p_account_id
AND hp.party_id = hca.party_id;
SELECT party_name
INTO l_customer_name
FROM hz_parties
WHERE party_id = p_party_id;
SELECT concatenated_segments,
serial_number_control_code,
lot_control_code
INTO l_item, l_srl_ctl,l_lot_ctl
FROM mtl_system_items_kfv mtl
WHERE mtl.inventory_item_id = csi_rec.inventory_item_id
AND mtl.organization_id = csi_rec.organization_id;
SELECT comms_nl_trackable_flag,
organization_code
INTO l_ib_flag,
l_org_code
FROM mtl_system_items_b msi ,
mtl_parameters mp
WHERE msi.inventory_item_id = csi_rec.inventory_item_id
AND msi.organization_id = mp.master_organization_id
AND mp.organization_id = csi_rec.organization_id;
SELECT nvl(line.sold_to_org_id, hdr.sold_to_org_id),
hdr.order_number,
line.line_number||'.'||line.shipment_number
INTO l_customer_id,
l_rma_num,
l_rma_line_num
FROM oe_order_lines_all line,
oe_order_headers_all hdr
WHERE line.line_id = csi_rec.trx_source_line_id
AND hdr.header_id = line.header_id;
SELECT a.instance_id,
a.serial_number,
a.inventory_item_id inst_item_id,
d.inventory_item_id serial_item_id,
d.current_status,
d.current_organization_id,
a.manually_created_flag,
a.instance_usage_code,
a.location_type_code,
a.active_end_date
FROM csi_item_instances a,
mtl_serial_numbers d,
mtl_parameters e
WHERE a.serial_number is not null
AND d.serial_number = a.serial_number
AND d.inventory_item_id <> a.inventory_item_id
AND e.organization_id = nvl(a.last_vld_organization_id, a.inv_master_organization_id)
AND e.serial_number_type = 3;
SELECT cte.transaction_error_id transaction_error_id,
cte.inv_material_transaction_id inv_material_transaction_id,
mmt.inventory_item_id item_id,
mmt.organization_id organization_id,
mmt.trx_source_line_id mtl_src_line_id,
mmt.creation_date mtl_creation_date,
abs(mmt.primary_quantity) mtl_txn_qty,
msi.serial_number_control_code serial_code,
oel.sold_to_org_id owner_acct,
oel.ordered_quantity ordered_qty,
oel.order_quantity_uom ordered_uom,
hca.party_id party_id
FROM csi_txn_errors cte,mtl_material_transactions mmt,mtl_system_items msi,oe_order_lines_all oel,hz_cust_accounts hca
WHERE cte.processed_flag in ('E', 'R')
AND cte.transaction_type_id = 53
AND cte.inv_material_transaction_id = mmt.transaction_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND msi.serial_number_control_code = 1
AND oel.line_id = mmt.trx_source_line_id
AND hca.cust_account_id = oel.sold_to_org_id
ORDER BY item_id, party_id, owner_acct,mtl_txn_qty;
SELECT cii.instance_id instance_id,
cii.quantity quantity,
cii.active_start_date active_start_date
FROM csi_item_instances cii
WHERE cii.inventory_item_id = p_item_id
AND cii.accounting_class_code = 'CUST_PROD'
AND cii.instance_usage_code = 'OUT_OF_ENTERPRISE'
AND cii.owner_party_id = p_owner_party_id
AND cii.owner_party_account_id = p_owner_acct_id
AND sysdate between nvl(cii.active_start_date, sysdate-1) and nvl(cii.active_end_date, sysdate+1)
ORDER BY quantity;
SELECT txn_line_detail_id ,
instance_id,
quantity
FROM csi_t_txn_line_details
WHERE transaction_line_id = p_transaction_line_id
AND source_transaction_flag = 'Y';
SELECT sub_type_id ,
src_change_owner
INTO l_sub_type_id,
l_change_owner
FROM csi_txn_sub_types
WHERE transaction_type_id = 53
AND default_flag = 'Y';
l_instance_tbl.delete;
SELECT transaction_line_id
INTO l_transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
AND source_transaction_id = l_rma_txn_tbl(i).mtl_src_line_id
AND source_transaction_type_id = 53;
SELECT instance_party_id
INTO l_pty_dtl_tbl(1).instance_party_id
FROM csi_i_parties
WHERE instance_id = l_instance_tbl(j).instance_id
AND relationship_type_code = 'OWNER';
UPDATE csi_t_txn_line_details
SET instance_id = l_instance_tbl(j).instance_id,
instance_exists_flag = 'Y'
WHERE txn_line_detail_id = l_txn_line_detail_id;
UPDATE csi_txn_errors
SET processed_flag = 'R'
WHERE transaction_error_id = l_rma_txn_tbl(i).Txn_error_id;
l_instance_tbl.DELETE(j);
SELECT transaction_error_id,
inv_material_transaction_id
FROM csi_txn_errors
WHERE processed_flag in ('E', 'R')
AND inv_material_transaction_id is not null
AND (source_type is null OR transaction_type_id is null);
SELECT fnd_Profile.value('csi_upgrading_from_release')
INTO l_release
FROM sys.dual;
SELECT 'Y' INTO l_txn_processed
FROM sys.dual
WHERE exists (
SELECT 'X' FROM csi_transactions
WHERE inv_material_transaction_id = err_rec.inv_material_transaction_id);
UPDATE csi_txn_errors
SET processed_flag = 'D'
WHERE transaction_error_id = err_rec.transaction_error_id;
SELECT mmt.transaction_id,
mmt.transaction_type_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mtt.type_class,
mmt.transaction_quantity
INTO l_mtl_txn_id,
l_mtl_type_id,
l_mtl_action_id,
l_mtl_source_type_id,
l_mtl_type_class,
l_mtl_txn_qty
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_id = err_rec.inv_material_transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id;
UPDATE csi_txn_errors
SET source_type = l_source_type,
transaction_type_id = l_csi_txn_type_id
WHERE transaction_error_id = err_rec.transaction_error_id;
SELECT cii.instance_id
FROM csi_item_instances cii
WHERE cii.location_type_code = 'WIP'
AND cii.instance_usage_code <> 'IN_RELATIONSHIP'
AND exists (
SELECT 'X' FROM mtl_system_items msi
WHERE msi.inventory_item_id = cii.inventory_item_id
AND msi.organization_id = cii.last_vld_organization_id
AND msi.serial_number_control_code in (1, 6));
UPDATE csi_item_instances
SET instance_usage_code = 'IN_WIP'
WHERE instance_id = l_instance_id_tab(ind);
PROCEDURE delete_dup_nsrl_wip_instances IS
l_keep_instance_id number;
SELECT cii.inventory_item_id,
cii.inventory_revision,
cii.lot_number,
cii.wip_job_id
FROM csi_item_instances cii,
mtl_system_items msi
WHERE cii.location_type_code = 'WIP'
AND cii.instance_usage_code = 'IN_WIP'
AND msi.inventory_item_id = cii.inventory_item_id
AND msi.organization_id = cii.last_vld_organization_id
AND msi.serial_number_control_code in (1, 6)
GROUP BY cii.inventory_item_id,
cii.inventory_revision,
cii.lot_number,
cii.wip_job_id
HAVING count(*) > 1;
SELECT cii.instance_id,
cii.quantity
FROM csi_item_instances cii
WHERE cii.location_type_code = 'WIP'
AND cii.instance_usage_code = 'IN_WIP'
AND cii.inventory_item_id = p_item_id
AND nvl(cii.inventory_revision, '#*#*#') = nvl(p_revision, '#*#*#')
AND nvl(cii.lot_number,'#*#*#') = nvl(p_lot_number, '#*#*#')
AND cii.wip_job_id = p_wip_job_id;
SELECT instance_party_id
FROM csi_i_parties
WHERE instance_id = p_instance_id;
UPDATE csi_item_instances
SET quantity = quantity + dup_inst_rec.quantity
WHERE instance_id = l_keep_instance_id;
DELETE FROM csi_ip_accounts
WHERE instance_party_id = ip_rec.instance_party_id;
DELETE FROM csi_i_parties_h
WHERE instance_party_id = ip_rec.instance_party_id;
DELETE FROM csi_i_parties
WHERE instance_id = dup_inst_rec.instance_id;
DELETE FROM csi_item_instances
WHERE instance_id = dup_inst_rec.instance_id;
DELETE FROM csi_item_instances_h
WHERE instance_id = dup_inst_rec.instance_id;
END delete_dup_nsrl_wip_instances;
PROCEDURE Delete_Dup_Org_Assignments IS
cursor csi_dup_cur is
select instance_id,relationship_type_code
from csi_i_org_assignments
group by instance_id,relationship_type_code
having count(*) > 1;
select *
from csi_i_org_assignments
where instance_id = p_instance_id
and relationship_type_code = p_rel_type_code
order by creation_date asc;
select coah.* from csi_i_org_assignments_h coah,
csi_i_org_assignments coa
where coa.instance_id = p_instance_id
and coa.relationship_type_code = p_rel_type_code
and coah.instance_ou_id = coa.instance_ou_id
order by coah.transaction_id,coah.last_update_date asc;
select instance_ou_id
into v_max_ou_id
from csi_i_org_assignments
where instance_id = dup_rec.instance_id
and relationship_type_code = dup_rec.relationship_type_code
and creation_date = ( select max(creation_date)
from csi_i_org_assignments
where instance_id = dup_rec.instance_id
and relationship_type_code = dup_rec.relationship_type_code
and nvl(active_end_date,(sysdate+1)) > sysdate)
and nvl(active_end_date,(sysdate+1)) > sysdate
and rownum < 2;
select max(instance_ou_id)
into v_max_ou_id
from csi_i_org_assignments
where instance_id = dup_rec.instance_id
and relationship_type_code = dup_rec.relationship_type_code;
l_del_tbl.DELETE;
l_org_hist_tbl.DELETE;
UPDATE CSI_I_ORG_ASSIGNMENTS
set (operating_unit_id,active_end_date,context,attribute1,attribute2,attribute3,
attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,last_update_date) =
(select operating_unit_id,active_end_date,context,attribute1,attribute2,attribute3,
attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,sysdate
from csi_i_org_assignments
where instance_ou_id = v_max_ou_id)
where instance_ou_id = org_rec.instance_ou_id;
l_del_ou_hist_tbl.DELETE;
update csi_i_org_assignments_h
set old_operating_unit_id = l_prev_rec.old_operating_unit_id,
new_operating_unit_id = l_prev_rec.new_operating_unit_id,
old_relationship_type_code = l_prev_rec.old_relationship_type_code,
new_relationship_type_code = l_prev_rec.new_relationship_type_code,
old_active_start_date = l_prev_rec.old_active_start_date,
new_active_start_date = l_prev_rec.new_active_start_date,
old_active_end_date = l_prev_rec.old_active_end_date,
new_active_end_date = l_prev_rec.new_active_end_date,
old_context = l_prev_rec.old_context,
new_context = l_prev_rec.new_context,
old_attribute1 = l_prev_rec.old_attribute1,
new_attribute1= l_prev_rec.new_attribute1,
old_attribute2 = l_prev_rec.old_attribute2,
new_attribute2= l_prev_rec.new_attribute2,
old_attribute3 = l_prev_rec.old_attribute3,
new_attribute3= l_prev_rec.new_attribute3,
old_attribute4 = l_prev_rec.old_attribute4,
new_attribute4= l_prev_rec.new_attribute4,
old_attribute5 = l_prev_rec.old_attribute5,
new_attribute5= l_prev_rec.new_attribute5,
old_attribute6 = l_prev_rec.old_attribute6,
new_attribute6= l_prev_rec.new_attribute6,
old_attribute7 = l_prev_rec.old_attribute7,
new_attribute7= l_prev_rec.new_attribute7,
old_attribute8 = l_prev_rec.old_attribute8,
new_attribute8= l_prev_rec.new_attribute8,
old_attribute9 = l_prev_rec.old_attribute9,
new_attribute9= l_prev_rec.new_attribute9,
old_attribute10 = l_prev_rec.old_attribute10,
new_attribute10= l_prev_rec.new_attribute10,
old_attribute11 = l_prev_rec.old_attribute11,
new_attribute11= l_prev_rec.new_attribute11,
old_attribute12 = l_prev_rec.old_attribute12,
new_attribute12= l_prev_rec.new_attribute12,
old_attribute13 = l_prev_rec.old_attribute13,
new_attribute13= l_prev_rec.new_attribute13,
old_attribute14 = l_prev_rec.old_attribute14,
new_attribute14= l_prev_rec.new_attribute14,
old_attribute15 = l_prev_rec.old_attribute15,
new_attribute15= l_prev_rec.new_attribute15,
last_update_date = sysdate
where instance_ou_history_id = l_prev_rec.instance_ou_history_id;
delete from csi_i_org_assignments_h
where instance_ou_history_id = l_del_ou_hist_tbl(i);
l_del_ou_hist_tbl.DELETE;
-- Update the instance_ou_id with the one that is retained
IF l_flag = 'Y' THEN -- Just in case the last record in the loop matches with prev txn
update csi_i_org_assignments_h
set old_operating_unit_id = l_prev_rec.old_operating_unit_id,
new_operating_unit_id = l_prev_rec.new_operating_unit_id,
old_relationship_type_code = l_prev_rec.old_relationship_type_code,
new_relationship_type_code = l_prev_rec.new_relationship_type_code,
old_active_start_date = l_prev_rec.old_active_start_date,
new_active_start_date = l_prev_rec.new_active_start_date,
old_active_end_date = l_prev_rec.old_active_end_date,
new_active_end_date = l_prev_rec.new_active_end_date,
old_context = l_prev_rec.old_context,
new_context = l_prev_rec.new_context,
old_attribute1 = l_prev_rec.old_attribute1,
new_attribute1= l_prev_rec.new_attribute1,
old_attribute2 = l_prev_rec.old_attribute2,
new_attribute2= l_prev_rec.new_attribute2,
old_attribute3 = l_prev_rec.old_attribute3,
new_attribute3= l_prev_rec.new_attribute3,
old_attribute4 = l_prev_rec.old_attribute4,
new_attribute4= l_prev_rec.new_attribute4,
old_attribute5 = l_prev_rec.old_attribute5,
new_attribute5= l_prev_rec.new_attribute5,
old_attribute6 = l_prev_rec.old_attribute6,
new_attribute6= l_prev_rec.new_attribute6,
old_attribute7 = l_prev_rec.old_attribute7,
new_attribute7= l_prev_rec.new_attribute7,
old_attribute8 = l_prev_rec.old_attribute8,
new_attribute8= l_prev_rec.new_attribute8,
old_attribute9 = l_prev_rec.old_attribute9,
new_attribute9= l_prev_rec.new_attribute9,
old_attribute10 = l_prev_rec.old_attribute10,
new_attribute10= l_prev_rec.new_attribute10,
old_attribute11 = l_prev_rec.old_attribute11,
new_attribute11= l_prev_rec.new_attribute11,
old_attribute12 = l_prev_rec.old_attribute12,
new_attribute12= l_prev_rec.new_attribute12,
old_attribute13 = l_prev_rec.old_attribute13,
new_attribute13= l_prev_rec.new_attribute13,
old_attribute14 = l_prev_rec.old_attribute14,
new_attribute14= l_prev_rec.new_attribute14,
old_attribute15 = l_prev_rec.old_attribute15,
new_attribute15= l_prev_rec.new_attribute15,
last_update_date = sysdate
where instance_ou_history_id = l_prev_rec.instance_ou_history_id;
delete from csi_i_org_assignments_h
where instance_ou_history_id = l_del_ou_hist_tbl(i);
l_del_ou_hist_tbl.DELETE;
update csi_i_org_assignments_h
set instance_ou_id = v_ret_ou_id,
last_update_date = sysdate
where instance_ou_history_id = l_org_hist_tbl(x).instance_ou_history_id;
-- Delete the Duplicate Org Assignments
FORALL x in l_del_tbl.FIRST .. l_del_tbl.LAST
DELETE FROM CSI_I_ORG_ASSIGNMENTS
where instance_ou_id = l_del_tbl(x);
END Delete_Dup_Org_Assignments;
SELECT inventory_item_id,
serial_number,
instance_id,
date_time_stamp,
mtl_txn_id,
error_message
FROM csi_ii_forward_sync_temp
WHERE process_flag <> 'P';
SELECT cdt.mtl_txn_name,
cdt.mtl_txn_id,
cdt.mtl_txn_date,
cte.error_text
FROM csi_diagnostics_temp cdt,
csi_txn_errors cte
WHERE cdt.inventory_item_id = p_item_id
AND cdt.serial_number = p_serial_number
AND cte.inv_material_transaction_id = cdt.mtl_txn_id
AND cte.processed_flag in ('E', 'R')
ORDER BY diag_seq_id;
SELECT mtl_txn_id
FROM csi_ii_forward_sync_temp
WHERE process_flag <> 'P'
AND mtl_txn_creation_date is null
FOR UPDATE OF mtl_txn_creation_date;
SELECT creation_date
INTO l_creation_date
FROM mtl_material_transactions
WHERE transaction_id = fs_rec.mtl_txn_id;
UPDATE csi_ii_forward_sync_temp
SET mtl_txn_creation_date = l_creation_date
WHERE current of fs_cur;
BEFORE INSERT ON MTL_MATERIAL_TRANSACTIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_nl_trackable VARCHAR2(1);
SELECT comms_nl_trackable_flag
INTO v_nl_trackable
FROM MTL_SYSTEM_ITEMS msi,
MTL_PARAMETERS mp
where mp.organization_id = :new.organization_id
and msi.inventory_item_id = :new.inventory_item_id
and msi.organization_id = mp.master_organization_id;
:new.last_updated_by := null;
select lookup_code,enabled_flag
from CSI_LOOKUPS
where lookup_type = l_type;
UPDATE FND_LOOKUP_VALUES
SET enabled_flag = 'Y',
last_updated_by = -1,
last_update_date = sysdate
WHERE lookup_type = l_type;
IF Is_Routine_Enabled(l_lookup_tbl,'DELETE_DUP_RELATIONSHIP') THEN
Update_Lookup('DELETE_DUP_RELATIONSHIP');
log(date_time_stamp||'delete_dup_relationship');
csi_diagnostics_pkg.Delete_Dup_Relationship;
log(date_time_stamp||'delete_dup_relationship already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_NO_CTL_SRL_LOT_INST') THEN
Update_Lookup('UPDATE_NO_CTL_SRL_LOT_INST');
log(date_time_stamp||'update_no_ctl_srl_lot_inst');
csi_diagnostics_pkg.Update_No_Ctl_Srl_Lot_Inst;
log(date_time_stamp||'Update_No_Ctl_Srl_Lot_Inst already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'DELETE_DUP_SRL_INV_INSTANCE') THEN
Update_Lookup('DELETE_DUP_SRL_INV_INSTANCE');
log(date_time_stamp||'delete_dup_srl_inv_instance');
csi_diagnostics_pkg.Delete_Dup_Srl_Inv_Instance;
log(date_time_stamp||'Delete_Dup_Srl_Inv_Instance already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_DUP_SRL_INSTANCE') THEN
Update_Lookup('UPDATE_DUP_SRL_INSTANCE');
log(date_time_stamp||'update_dup_srl_instance');
csi_diagnostics_pkg.Update_Dup_Srl_Instance;
log(date_time_stamp||'Update_Dup_Srl_Instance already executed...');
Update_Lookup('DEL_API_DUP_SRL_INSTANCE');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_INSTANCE_USAGE') THEN
Update_Lookup('UPDATE_INSTANCE_USAGE');
log(date_time_stamp||'update_instance_usage');
csi_diagnostics_pkg.Update_Instance_Usage;
log(date_time_stamp||'Update_Instance_Usage already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_FULL_DUMP_FLAG') THEN
Update_Lookup('UPDATE_FULL_DUMP_FLAG');
log(date_time_stamp||'update_full_dump_flag');
csi_diagnostics_pkg.Update_Full_dump_flag;
log(date_time_stamp||'Update_Full_dump_flag already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_VLD_ORGANIZATION') THEN
Update_Lookup('UPDATE_VLD_ORGANIZATION');
log(date_time_stamp||'update_vld_organization');
csi_diagnostics_pkg.Update_Vld_Organization;
log(date_time_stamp||'Update_Vld_Organization already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_REVISION') THEN
Update_Lookup('UPDATE_REVISION');
log(date_time_stamp||'update_revision');
csi_diagnostics_pkg.Update_Revision;
log(date_time_stamp||'Update_Revision already executed...');
Update_Lookup('MERGE_NON_SRL_INV_INSTANCE');
IF Is_Routine_Enabled(l_lookup_tbl,'DELETE_DUP_ACCOUNT') THEN
Update_Lookup('DELETE_DUP_ACCOUNT');
log(date_time_stamp||'delete_dup_account');
csi_diagnostics_pkg.delete_dup_account;
log(date_time_stamp||'delete_dup_account already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_INSTANCE_PARTY_SOURCE') THEN
Update_Lookup('UPDATE_INSTANCE_PARTY_SOURCE');
log(date_time_stamp||'update_instance_party_source');
csi_diagnostics_pkg.update_instance_party_source;
log(date_time_stamp||'update_instance_party_source already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_CONTACT_PARTY_RECORD') THEN
Update_Lookup('UPDATE_CONTACT_PARTY_RECORD');
log(date_time_stamp||'update_contact_party_record');
csi_diagnostics_pkg.update_contact_party_record;
log(date_time_stamp||'update_contact_party_record already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'REVERT_PARTY_REL_TYPE_UPDATE') THEN
Update_Lookup('REVERT_PARTY_REL_TYPE_UPDATE');
log(date_time_stamp||'revert_party_rel_type_update');
csi_diagnostics_pkg.revert_party_rel_type_update;
log(date_time_stamp||'revert_party_rel_type_update already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'UPDATE_MASTER_ORGANIZATION_ID') THEN
Update_Lookup('UPDATE_MASTER_ORGANIZATION_ID');
log(date_time_stamp||'update_master_organization_id');
csi_diagnostics_pkg.update_master_organization_ID;
log(date_time_stamp||'update_master_organization_ID already executed...');
Update_Lookup('MISSING_MTL_TXN_ID_IN_CSI');
Update_Lookup('FIX_WIP_USAGE');
IF Is_Routine_Enabled(l_lookup_tbl,'DELETE_DUP_NSRL_WIP_INSTANCES') THEN
Update_Lookup('DELETE_DUP_NSRL_WIP_INSTANCES');
log(date_time_stamp||'delete_dup_nsrl_wip_instances');
csi_diagnostics_pkg.delete_dup_nsrl_wip_instances;
log(date_time_stamp||'delete_dup_nsrl_wip_instances already executed...');
IF Is_Routine_Enabled(l_lookup_tbl,'DELETE_DUP_ORG_ASSIGNMENTS') THEN
Update_Lookup('DELETE_DUP_ORG_ASSIGNMENTS');
log(date_time_stamp||'Delete_Dup_Org_Assignments');
csi_diagnostics_pkg.Delete_Dup_Org_Assignments;
log(date_time_stamp||'Delete_Dup_Org_Assignments already executed...');
p_option => 'SELECTED');
Update_Lookup('EXPIRE_NON_TRACKABLE_INSTANCE');
log(date_time_stamp||'create_or_update_shipping_inst');
csi_diagnostics_pkg.create_or_update_shipping_inst;
p_option => 'SELECTED');
SELECT count(*)
INTO l_recount
FROM csi_ii_forward_sync_temp
WHERE process_flag <> 'P';
l_update_name VARCHAR2(30) := 'csidiagb.pls';
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE
,l_table_owner
,l_table_name
,l_update_name
,x_worker_id
,x_num_workers
,x_batch_size,0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid
,l_end_rowid
,l_any_rows_to_process
,x_batch_size
,TRUE);
UPDATE /*+ rowid(cii) */ csi_item_instances cii
SET operational_status_code =
DECODE(instance_usage_code,'IN_SERVICE','IN_SERVICE','OUT_OF_SERVICE',
'OUT_OF_SERVICE', 'INSTALLED','INSTALLED','NOT_USED')
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = -1
where operational_status_code IS NULL
AND rowid between l_start_rowid and l_end_rowid;
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
x_batch_size,
FALSE);