[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| UPDATE_BATCH_INDICATOR column. |
| Archana Mundhe 08/12/2008 Bug 6845259 |
| Modified the update of ic_item_mst_b_mig based on item_id |
| Archana Mundhe 03/25/2009 Bug 8363586 |
| Modified migrate_inventory_balances to exclude records that|
| are delete marked from being processed. |
| Kedar Bavadekar - 06/23/09 |
| Fix for Bug#8242978 . Added Ship confirm in |
| disallowed for status with shipping indicator |
| unchecked |
| Kedar Bavadekar - 07/01/09. Fix for Bug#8650503. |
| Added parameter X_ONHAND_CONTROL in call to |
| INSERT_ROW in package mtl_material_statuses_pkg |
| Kedar Bavadekar - 03/17/10 . Fix for Bug#9143261 |
| - Update on-hand control field for exisiting status |
| and new statues |
| Kedar Bavadekar - 03/29/2010 Bug#9403348 |
| Modified migrate_inventory_balances to fix sql |
| that fetches inventory locator |
| Kedar Bavadekar - 11/30/2010 Bug#10413599 |
| Update status_id column in ic_lots_mst for |
| existing status and also changed update on |
| mtl_material_statues to mtl_material_statues_b |
| in procedure migrate_lot_status |
| Srinivasulu Puri- 11/04/2011 Bug#13335019 |
| for migrate_inventory_balances procedure added |
| 2 new parameters start rowid and end rowid to |
| utilise adparallel functionality to improve the |
| performance. |
| also commented a update to mtl_material_transactions|
| which was setting costed flags now that code is |
| moved to GMI package where it was setting them to Y |
| Srinivasulu Puri - 3/30/2012 Bug#13910275 commented category migrated |
| ind check in migrate_item_category insert stmt |
| as category migration is parallel and update of |
| ic_item_mst_b_mig is not based on organization_id |
| Shaliu Chen - 06/05/2012 Bug#14147616 |
| Modify init_doc_seq procedure to add exception |
| handling program to ignore Duplicate Key on |
| Index error |
+==========================================================================+
*/
G_DEFAULT_LOCT VARCHAR2(50);
SELECT item_no ||'('||to_char(p_item_id)||')'
INTO l_return_val
FROM ic_item_mst_b
WHERE item_id = p_item_id;
SELECT segment1 ||'('||to_char(p_ditem_id)||')'
INTO l_return_val
FROM mtl_system_items_b
WHERE organization_id = p_organization_id AND
inventory_item_id = p_ditem_id;
SELECT lot_no ||decode(sublot_no, NULL,NULL,', '||sublot_no)
||'('||to_char(p_lot_id)||')'
INTO l_return_val
FROM ic_lots_mst
WHERE lot_id = p_lot_id AND
rownum = 1;
SELECT organization_code ||'('||to_char(p_organization_id)||')'
INTO l_return_val
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT *
FROM ic_invn_typ
WHERE migrated_ind is NULL;
SELECT count(*)
INTO l_count
FROM fnd_lookup_values
WHERE
lookup_type = 'ITEM_TYPE' and
lookup_code = l_inv_type.inv_type and
view_application_id = 3 and
ROWNUM = 1;
IF (l_inv_type.delete_mark = 1) THEN
l_enabled_flag := 'N';
FND_LOOKUP_VALUES_PKG.INSERT_ROW (
X_ROWID=> l_rowid,
X_LOOKUP_TYPE=> 'ITEM_TYPE',
X_SECURITY_GROUP_ID=> 0,
X_VIEW_APPLICATION_ID=> 3,
X_LOOKUP_CODE=> l_inv_type.inv_type,
X_TAG=> NULL,
X_ENABLED_FLAG=> l_enabled_flag,
X_START_DATE_ACTIVE=> NULL,
X_END_DATE_ACTIVE=> NULL,
X_TERRITORY_CODE=> NULL,
X_ATTRIBUTE_CATEGORY=> NULL,
X_ATTRIBUTE1 => l_inv_type.attribute1,
X_ATTRIBUTE2 => l_inv_type.attribute2,
X_ATTRIBUTE3 => l_inv_type.attribute3,
X_ATTRIBUTE4 => l_inv_type.attribute4,
X_ATTRIBUTE5 => l_inv_type.attribute5,
X_ATTRIBUTE6 => l_inv_type.attribute6,
X_ATTRIBUTE7 => l_inv_type.attribute7,
X_ATTRIBUTE8 => l_inv_type.attribute8,
X_ATTRIBUTE9 => l_inv_type.attribute9,
X_ATTRIBUTE10 => l_inv_type.attribute10,
X_ATTRIBUTE11 => l_inv_type.attribute11,
X_ATTRIBUTE12 => l_inv_type.attribute12,
X_ATTRIBUTE13 => l_inv_type.attribute13,
X_ATTRIBUTE14 => l_inv_type.attribute14,
X_ATTRIBUTE15 => l_inv_type.attribute15,
X_MEANING=> l_inv_type.inv_type,
X_DESCRIPTION=> l_inv_type.inv_type_desc,
X_CREATION_DATE=> l_inv_type.creation_date,
X_CREATED_BY=> l_inv_type.created_by,
X_LAST_UPDATE_DATE=> l_inv_type.last_update_date,
X_LAST_UPDATED_BY=> l_inv_type.last_updated_by,
X_LAST_UPDATE_LOGIN=> NULL
);
UPDATE ic_invn_typ
SET
migrated_ind = 1
WHERE
inv_type = l_inv_type.inv_type;
SELECT * FROM gmi_category_sets
WHERE migrated_ind is NULL AND
OPM_CLASS in ('ALLOC_CLASS','SEQ_CLASS','SUB_STANDARD_CLASS',
'TECH_CLASS','GL_CLASS','COST_CLASS','GL_BUSINESS_CLASS',
'GL_PRODUCT_LINE');
SELECT functional_area_id, category_set_id
INTO l_functional_area_id, l_category_set_id
FROM mtl_default_category_sets s,
mfg_lookups l
WHERE
l.lookup_type = 'MTL_FUNCTIONAL_AREAS' and
l.meaning = 'Process '|| decode (c.user_opm_class, 'General Ledger Class',
'GL Class', 'GL Product Line', 'Product Line',
c.user_opm_class) AND
l.lookup_code = s.functional_area_id;
-- Update discrete functional area with OPM category set id for convergence
-- functional areas
UPDATE mtl_default_category_sets
SET category_set_id = NVL(c.category_set_id, -1)
WHERE
functional_area_id = l_functional_area_id and
category_set_id = -1;
UPDATE gmi_category_sets
SET migrated_ind = 1
WHERE opm_class = c.opm_class;
SELECT DISTINCT
i.inventory_item_id,
i.organization_id,
g.category_set_id
FROM gmi_item_categories g,
ic_item_mst_b_mig i,
mtl_item_categories m
WHERE i.rowid BETWEEN p_start_rowid AND p_end_rowid AND
i.migrated_ind is not NULL and
i.category_migrated_ind is NULL and
i.item_id = g.item_id and
m.organization_id = i.organization_id and
m.inventory_item_id = i.inventory_item_id and
m.category_set_id = g.category_set_id and
m.category_id <> g.category_id;
-- insert new record in discrete
insert into mtl_item_categories(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
WH_UPDATE_DATE)
SELECT
i.inventory_item_id,
i.organization_id,
g.category_set_id,
g.category_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM gmi_item_categories g,
ic_item_mst_b_mig i
WHERE i.rowid BETWEEN p_start_rowid AND p_end_rowid AND
i.migrated_ind is not NULL and
-- i.category_migrated_ind is NULL and bug 13910275
i.item_id = g.item_id and
NOT EXISTS(
SELECT 1
FROM mtl_item_categories
WHERE
organization_id = i.organization_id AND
inventory_item_id = i.inventory_item_id AND
category_set_id = g.category_set_id);
/* Select rows with error */
FOR r in c_item_cat_error LOOP
-- Log warning message
-- dbms_output.put_line ('A different category already assigned in discrete. Org id, Item, category set id' || to_char(r.organization_id)||', '||to_char(v_inventory_item_id)||', '||to_char(r.category_set_id));
/* Update the rows as migrated */
/* Bug 6845259 */
/* Modified the update*/
UPDATE ic_item_mst_b_mig mig
SET category_migrated_ind = 1
WHERE exists ( SELECT 1
FROM gmi_item_categories gic
WHERE mig.item_id = gic.item_id)
AND ROWID BETWEEN p_start_rowid AND p_end_rowid;
/* UPDATE ic_item_mst_b_mig
SET category_migrated_ind = 1
WHERE
(organization_id, inventory_item_id) IN (
SELECT organization_id, inventory_item_id
FROM gmi_item_categories
WHERE
rowid BETWEEN p_start_rowid AND p_end_rowid); */
SELECT *
FROM ic_lots_sts
WHERE status_id is NULL ;
SELECT transaction_type_id, transaction_type_name, transaction_source_type_id
FROM mtl_transaction_types
WHERE
status_control_flag = 1 and
disable_date is NULL ;
SELECT count(*)
INTO l_count
FROM mtl_material_statuses_tl
WHERE
status_code = l_lot_status.lot_status and
rownum = 1;
UPDATE mtl_material_statuses_b -- 10413599
SET onhand_control = 1
WHERE status_id = (SELECT status_id
FROM mtl_material_statuses_tl
WHERE status_code = l_lot_status.lot_status
AND rownum = 1);
UPDATE ic_lots_sts
SET status_id = (SELECT status_id
FROM mtl_material_statuses_tl
WHERE status_code = l_lot_status.lot_status
AND rownum = 1),
migrated_ind = 1
WHERE lot_status = l_lot_status.lot_status;
SELECT mtl_material_status_s.NEXTVAL
INTO l_status_id
FROM DUAL;
MTL_MATERIAL_STATUSES_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_STATUS_ID => l_status_id,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_LOCATOR_CONTROL => 2,
X_LOT_CONTROL => 1,
X_SERIAL_CONTROL => 2,
X_ZONE_CONTROL => 2,
X_ONHAND_CONTROL => 1, /* Fix for Bug#9143261. It should be 1 instead of 2 as fixed for Bug#8650503 */
X_REQUEST_ID => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ENABLED_FLAG => l_lot_status.delete_mark+1,
X_STATUS_CODE => l_lot_status.lot_status,
X_DESCRIPTION => l_lot_status.status_desc,
X_CREATION_DATE => l_lot_status.creation_date,
X_CREATED_BY => l_lot_status.created_by,
X_LAST_UPDATE_DATE => l_lot_status.last_update_date,
X_LAST_UPDATED_BY => l_lot_status.last_updated_by,
X_LAST_UPDATE_LOGIN => NULL,
X_LPN_CONTROL => 2,
X_INVENTORY_ATP_CODE => l_lot_status.rejected_ind+1,
X_RESERVABLE_TYPE => l_lot_status.rejected_ind+1,
X_AVAILABILITY_TYPE => l_availability_type
);
INSERT INTO MTL_STATUS_TRANSACTION_CONTROL (
status_id,
transaction_type_id,
is_allowed,
creation_date,
created_by,
last_updated_by,
last_update_date
) VALUES (
l_status_id,
tt.transaction_type_id,
l_is_allowed,
l_lot_status.creation_date,
l_lot_status.created_by,
l_lot_status.last_updated_by,
l_lot_status.last_update_date);
UPDATE ic_lots_sts
SET
status_id = l_status_id,
migrated_ind = 1
WHERE
lot_status = l_lot_status.lot_status;
SELECT * FROM gmd_actions_b
WHERE migrated_ind is NULL;
SELECT * FROM gmd_actions_tl
WHERE action_code = p_action_code;
SELECT count(*)
INTO l_count
FROM mtl_actions_b
WHERE action_code = g.action_code;
IF (g.delete_mark = 1) THEN
l_disable_flag := 'Y';
MTL_ACTIONS_PVT.INSERT_ROW (
X_ROWID => l_rowid,
X_ACTION_CODE => g.action_code,
X_DESCRIPTION => ' ',
X_DISABLE_FLAG => l_disable_flag,
X_ATTRIBUTE1 => g.ATTRIBUTE1,
X_ATTRIBUTE2 => g.ATTRIBUTE2,
X_ATTRIBUTE3 => g.ATTRIBUTE3,
X_ATTRIBUTE4 => g.ATTRIBUTE4,
X_ATTRIBUTE5 => g.ATTRIBUTE5,
X_ATTRIBUTE6 => g.ATTRIBUTE6,
X_ATTRIBUTE7 => g.ATTRIBUTE7,
X_ATTRIBUTE8 => g.ATTRIBUTE8,
X_ATTRIBUTE9 => g.ATTRIBUTE9,
X_ATTRIBUTE10 => g.ATTRIBUTE10,
X_ATTRIBUTE11 => g.ATTRIBUTE11,
X_ATTRIBUTE12 => g.ATTRIBUTE12,
X_ATTRIBUTE13 => g.ATTRIBUTE13,
X_ATTRIBUTE14 => g.ATTRIBUTE14,
X_ATTRIBUTE15 => g.ATTRIBUTE15,
X_ATTRIBUTE16 => g.ATTRIBUTE16,
X_ATTRIBUTE17 => g.ATTRIBUTE17,
X_ATTRIBUTE18 => g.ATTRIBUTE18,
X_ATTRIBUTE19 => g.ATTRIBUTE19,
X_ATTRIBUTE20 => g.ATTRIBUTE20,
X_ATTRIBUTE21 => g.ATTRIBUTE21,
X_ATTRIBUTE22 => g.ATTRIBUTE22,
X_ATTRIBUTE23 => g.ATTRIBUTE23,
X_ATTRIBUTE24 => g.ATTRIBUTE24,
X_ATTRIBUTE25 => g.ATTRIBUTE25,
X_ATTRIBUTE26 => g.ATTRIBUTE26,
X_ATTRIBUTE27 => g.ATTRIBUTE27,
X_ATTRIBUTE28 => g.ATTRIBUTE28,
X_ATTRIBUTE29 => g.ATTRIBUTE29,
X_ATTRIBUTE30 => g.ATTRIBUTE30,
X_ATTRIBUTE_CATEGORY => g.ATTRIBUTE_CATEGORY,
X_CREATION_DATE => g.CREATION_DATE,
X_CREATED_BY => g.CREATED_BY,
X_LAST_UPDATE_DATE => g.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => g.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => g.LAST_UPDATE_LOGIN);
UPDATE mtl_actions_TL
SET
DESCRIPTION = gt.action_desc,
SOURCE_LANG = gt.source_lang
WHERE
action_code = gt.action_code AND
language = gt.language;
UPDATE gmd_actions_b
SET migrated_ind = 1
WHERE action_code = g.action_code;
UPDATE gmd_actions_b
SET migrated_ind = 1
WHERE action_code = g.action_code;
SELECT * FROM gmd_grades_b
WHERE migrated_ind is NULL;
SELECT * FROM gmd_grades_tl
WHERE QC_GRADE = p_qc_grade;
SELECT count(*)
INTO l_count
FROM mtl_grades_b
WHERE grade_code = g.qc_grade;
IF (g.delete_mark = 1) THEN
l_disable_flag := 'Y';
MTL_GRADES_PVT.INSERT_ROW (
X_ROWID => l_rowid,
X_GRADE_CODE => g.qc_grade,
X_DESCRIPTION => nvl(g.qc_grade_desc, g.qc_grade),
X_DISABLE_FLAG => l_disable_flag,
X_ATTRIBUTE1 => g.ATTRIBUTE1,
X_ATTRIBUTE2 => g.ATTRIBUTE2,
X_ATTRIBUTE3 => g.ATTRIBUTE3,
X_ATTRIBUTE4 => g.ATTRIBUTE4,
X_ATTRIBUTE5 => g.ATTRIBUTE5,
X_ATTRIBUTE6 => g.ATTRIBUTE6,
X_ATTRIBUTE7 => g.ATTRIBUTE7,
X_ATTRIBUTE8 => g.ATTRIBUTE8,
X_ATTRIBUTE9 => g.ATTRIBUTE9,
X_ATTRIBUTE10 => g.ATTRIBUTE10,
X_ATTRIBUTE11 => g.ATTRIBUTE11,
X_ATTRIBUTE12 => g.ATTRIBUTE12,
X_ATTRIBUTE13 => g.ATTRIBUTE13,
X_ATTRIBUTE14 => g.ATTRIBUTE14,
X_ATTRIBUTE15 => g.ATTRIBUTE15,
X_ATTRIBUTE16 => g.ATTRIBUTE16,
X_ATTRIBUTE17 => g.ATTRIBUTE17,
X_ATTRIBUTE18 => g.ATTRIBUTE18,
X_ATTRIBUTE19 => g.ATTRIBUTE19,
X_ATTRIBUTE20 => g.ATTRIBUTE20,
X_ATTRIBUTE21 => g.ATTRIBUTE21,
X_ATTRIBUTE22 => g.ATTRIBUTE22,
X_ATTRIBUTE23 => g.ATTRIBUTE23,
X_ATTRIBUTE24 => g.ATTRIBUTE24,
X_ATTRIBUTE25 => g.ATTRIBUTE25,
X_ATTRIBUTE26 => g.ATTRIBUTE26,
X_ATTRIBUTE27 => g.ATTRIBUTE27,
X_ATTRIBUTE28 => g.ATTRIBUTE28,
X_ATTRIBUTE29 => g.ATTRIBUTE29,
X_ATTRIBUTE30 => g.ATTRIBUTE30,
X_ATTRIBUTE_CATEGORY => g.ATTRIBUTE_CATEGORY,
X_CREATION_DATE => g.CREATION_DATE,
X_CREATED_BY => g.CREATED_BY,
X_LAST_UPDATE_DATE => g.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => g.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => g.LAST_UPDATE_LOGIN);
UPDATE MTL_GRADES_TL
SET
DESCRIPTION = gt.qc_grade_desc,
SOURCE_LANG = gt.source_lang
WHERE
grade_code = gt.qc_grade AND
language = gt.language;
UPDATE gmd_grades_b
SET migrated_ind = 1
WHERE qc_grade = g.qc_grade;
UPDATE gmd_grades_b
SET migrated_ind = 1
WHERE qc_grade = g.qc_grade;
SELECT distinct grade_code FROM mtl_lot_numbers
WHERE grade_code is not NULL;
SELECT count(*)
INTO l_count
FROM mtl_grades_b
WHERE grade_code = g.grade_code;
MTL_GRADES_PVT.INSERT_ROW (
X_ROWID => l_rowid,
X_GRADE_CODE => g.grade_code,
X_DESCRIPTION => g.grade_code,
X_DISABLE_FLAG => 'N',
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_CREATION_DATE => sysdate,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => 0,
X_LAST_UPDATE_LOGIN => NULL);
SELECT m.parent_lot_number, m.lot_number, m.organization_id, c.*
FROM ic_item_cnv c, ic_lots_mst_mig m
WHERE
c.rowid BETWEEN p_start_rowid AND p_end_rowid and
c.item_id = m.item_id and
c.lot_id = m.lot_id and
nvl(m.migrated_ind,0) = 1 and
m.conv_migrated_ind is NULL;
SELECT *
FROM gmi_item_conv_audit
WHERE conversion_id = pconversion_id;
SELECT *
FROM gmi_item_conv_audit_details
WHERE conv_audit_id = pconv_audit_id;
SELECT bu.unit_of_measure,
bu.uom_code,
bu.uom_class
INTO l_from_unit_of_measure,
l_from_uom_code,
l_from_uom_class
FROM mtl_system_items_b i,
mtl_units_of_measure iu,
mtl_units_of_measure bu
WHERE
i.organization_id = c.organization_id
AND i.inventory_item_id = l_inventory_item_id
AND i.primary_uom_code = iu.uom_Code
AND iu.uom_class = bu.uom_class
AND bu.base_uom_flag = 'Y';
SELECT unit_of_measure, uom_code
INTO l_to_unit_of_measure, l_to_uom_code
FROM mtl_units_of_measure
WHERE
uom_class = c.um_type AND
base_uom_flag = 'Y';
SELECT count(*)
INTO l_count
FROM mtl_lot_uom_class_conversions
WHERE
organization_id = c.organization_id AND
inventory_item_id = l_inventory_item_id AND
lot_number = c.lot_number AND
from_uom_class = l_from_uom_class AND
to_uom_class = c.um_type;
SELECT MTL_CONVERSION_ID_S.NEXTVAL INTO l_conversion_id FROM DUAL;
INSERT INTO mtl_lot_uom_class_conversions(
conversion_id,
lot_number,
organization_id,
inventory_item_id,
from_unit_of_measure,
from_uom_code,
from_uom_class,
to_unit_of_measure,
to_uom_code,
to_uom_class,
conversion_rate,
disable_date,
event_spec_disp_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
l_conversion_id,
c.lot_number,
c.organization_id,
l_inventory_item_id,
l_from_unit_of_measure,
l_from_uom_code,
l_from_uom_class,
l_to_unit_of_measure,
l_to_uom_code,
c.um_type,
c.type_factor,
DECODE (c.delete_mark, 1, c.last_update_date, NULL),
c.event_spec_disp_id,
c.created_by,
c.creation_date,
c.last_updated_by,
c.last_update_date,
c.last_update_login
);
SELECT MTL_CONV_AUDIT_ID_S.NEXTVAL
INTO l_conv_audit_id FROM DUAL;
SELECT reason_id INTO l_reason_id
FROM sy_reas_cds_b
WHERE
reason_code = cuadit.reason_code;
-- UPDATE_BATCH_INDICATOR column.
INSERT INTO mtl_lot_conv_audit(
conv_audit_id,
conversion_id,
conversion_date,
update_type_indicator,
batch_id,
reason_id,
old_conversion_rate,
new_conversion_rate,
event_spec_disp_id,
created_by,
creation_date,
last_updated_by,
last_update_date
)VALUES(
l_conv_audit_id,
l_conversion_id,
cuadit.conversion_date,
NVL(cuadit.update_batch_indicator, 0),
cuadit.batch_id,
l_reason_id,
cuadit.old_type_factor,
cuadit.new_type_factor,
cuadit.event_spec_disp_id,
cuadit.created_by,
cuadit.creation_date,
cuadit.last_updated_by,
cuadit.last_update_date);
SELECT organization_id, subinventory_ind_flag, migrated_ind
INTO l_organization_id, l_subinventory_ind_flag, l_migrated_ind
FROM ic_whse_mst
WHERE
whse_code = adetail.whse_code;
SELECT fnd_profile.value ('IC$DEFAULT_LOCT')
INTO G_DEFAULT_LOCT
FROM dual;
SELECT locator_id INTO l_locator_id
FROM ic_loct_mst
WHERE
whse_code = adetail.whse_code AND
location = adetail.location;
SELECT MTL_CONV_AUDIT_DETAIL_ID_S.NEXTVAL
INTO l_conv_audit_detail_id FROM DUAL;
INSERT INTO mtl_lot_conv_audit_details(
conv_audit_detail_id,
conv_audit_id,
revision,
organization_id,
subinventory_code,
lpn_id,
locator_id,
old_primary_qty,
old_secondary_qty,
new_primary_qty,
new_secondary_qty,
transaction_primary_qty,
transaction_secondary_qty,
transaction_update_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)VALUES(
l_conv_audit_detail_id,
l_conv_audit_id,
NULL,
l_organization_id,
adetail.whse_code,
NULL,
l_locator_id,
adetail.old_onhand_qty,
adetail.old_onhand_qty2,
adetail.new_onhand_qty,
adetail.new_onhand_qty2,
adetail.trans_qty,
adetail.trans_qty2,
adetail.trans_update_flag,
adetail.created_by,
adetail.creation_date,
adetail.last_updated_by,
adetail.last_update_date
);
UPDATE ic_lots_mst_mig
SET
conv_migrated_ind = 1,
last_update_date = sysdate,
last_updated_by = 0
WHERE
item_id = c.item_id AND
organization_id = c.organization_id AND
lot_number = c.lot_number;
INSERT INTO sy_docs_seq(
doc_type,
orgn_code,
assignment_type,
last_assigned,
format_size,
pad_char,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
trans_cnt)
SELECT
'DXFR',
p_orgn_code,
2,
0,
6,
0,
0,
sysdate,
0,
sysdate,
0,
0
FROM dual
WHERE
NOT EXISTS (
SELECT 1
FROM sy_docs_seq
WHERE
doc_type = 'DXFR' AND
orgn_code = p_orgn_code);
SELECT count(*)
INTO l_count
FROM sy_reas_cds
WHERE
reason_code = 'CNVM';
sy_reas_cds_pkg.insert_row (
x_rowid => l_rowid,
x_reason_code => 'CNVM',
x_reason_desc2 => NULL,
x_reason_type => 0,
x_flow_type => 0,
x_auth_string => NULL,
x_delete_mark => 0,
x_text_code => NULL,
x_trans_cnt => 0,
x_reason_desc1 => 'OPM Convergence Migration',
x_creation_date => sysdate,
x_created_by => 0,
x_last_update_date => sysdate,
x_last_updated_by => 0,
x_last_update_login => NULL);
UPDATE sy_reas_cds_b
SET reason_id = -99
WHERE reason_code = 'CNVM';
l_last_updated_by NUMBER;
SELECT l.rowid, l.*,itm.loct_ctl item_loct_ctl,
itm.item_um, itm.lot_ctl,
itm.noninv_ind, itm.item_no
FROM ic_loct_inv l ,ic_item_mst_b itm
WHERE l.migrated_ind is NULL
AND ROUND(l.loct_onhand, 5) <> 0
AND l.delete_mark = 0
AND l.rowid between p_start_rowid and p_end_rowid
AND l.item_id = itm.item_id
ORDER by whse_code;
SELECT subinventory_code, count(*)
FROM ic_loct_mst o, mtl_item_locations d
WHERE o.locator_id = d.inventory_location_id AND
o.whse_code = p_whse_code
GROUP BY whse_code, subinventory_code
ORDER by 2 desc;
SELECT period_name, period_year,
period_number , end_date
INTO l_period_name, l_period_year,
l_period_number, l_period_end_date
FROM org_acct_periods_v
WHERE rec_type = 'GL_PERIOD' AND
period_set_name = l_period_set_name AND
accounted_period_type = l_accounted_period_type AND
end_date > l_last_scheduled_close_date AND
start_date < sysdate;
SELECT orgn_code, subinventory_ind_flag, loct_ctl,
organization_id, migrated_ind, last_updated_by
INTO l_orgn_code, l_subinventory_ind_flag, l_whse_loct_ctl,
l_organization_id, l_migrated_ind, l_last_updated_by
FROM ic_whse_mst
WHERE
whse_code = bal.whse_code;
SELECT b.period_set_name, b.accounted_period_type
INTO l_period_set_name, l_accounted_period_type
FROM org_organization_definitions a,
gl_sets_of_books b
WHERE a.organization_id = l_organization_id
AND a.set_of_books_id = b.set_of_books_id;
SELECT NVL(MAX(schedule_close_date), sysdate)
INTO l_last_scheduled_close_date
FROM org_acct_periods
WHERE organization_id = l_organization_id;
p_user_id => l_last_updated_by,
p_login_id => NULL,
p_acct_period_type => l_accounted_period_type,
p_org_period_set_name => l_period_set_name,
p_open_period_name => fp.period_name,
p_open_period_year => fp.period_year,
p_open_period_num => fp.period_number,
x_last_scheduled_close_date => l_last_scheduled_close_date,
p_period_end_date => fp.end_date,
x_prior_period_open => l_prior_period_open,
x_new_acct_period_id => l_new_acct_period_id,
x_duplicate_open_period => l_duplicate_open_period,
x_commit_complete => l_commit_complete,
x_return_status => l_return_status );
SELECT loct_ctl, item_um, lot_ctl, noninv_ind, item_no
INTO l_item_loct_ctl, l_item_um, l_lot_ctl, l_noninv_ind, l_item_no
FROM ic_item_mst_b
WHERE
item_id = bal.item_id;
SELECT fnd_profile.value ('IC$DEFAULT_LOCT')
INTO G_DEFAULT_LOCT
FROM dual;
SELECT ol.locator_id, dl.subinventory_code
INTO l_locator_id, l_subinventory_code
FROM ic_loct_mst ol, mtl_item_locations dl, ic_whse_mst iwm
WHERE
ol.whse_code = bal.whse_code AND
ol.location = bal.location AND
ol.whse_code = iwm.whse_code AND
dl.organization_id = iwm.organization_id AND
ol.locator_id = dl.inventory_location_id (+);
-- Update the discrete transactions as costed
UPDATE mtl_material_transactions
SET
costed_flag = NULL,
opm_costed_flag = NULL
WHERE
transaction_set_id = l_transaction_set_id; */
-- Update mtl transaction id back in OPM table
UPDATE ic_loct_inv
SET migrated_ind = 1,
material_transaction_id = l_transaction_set_id
WHERE
rowid = bal.rowid;