[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM gme_batch_header_mig
WHERE NVL(migrated_batch_ind, ' ') <> 'M'
AND organization_id IS NOT NULL
ORDER BY batch_id;
SELECT *
FROM gme_material_details_mig
WHERE batch_id = v_batch_id
ORDER BY line_type, line_no;
SELECT item_no
FROM ic_item_mst
WHERE item_id = v_item_id;
SELECT *
FROM mtl_system_items_vl
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
SELECT whse_code
FROM ic_tran_pnd
WHERE trans_id = v_trans_id;
SELECT *
FROM gme_batch_steps_mig
WHERE batch_id = v_batch_id
ORDER BY batchstep_no;
SELECT *
FROM gme_batch_step_activ_mig
WHERE batchstep_id = v_batchstep_id
AND batch_id = v_old_batch_id;
SELECT *
FROM gme_batch_step_resources_mig
WHERE batchstep_activity_id = v_batchstep_activity_id
AND batch_id = v_old_batch_id;
SELECT *
FROM gme_process_parameters_mig
WHERE batchstep_resource_id = v_batchstep_resource_id
AND batch_id = v_old_batch_id;
SELECT *
FROM gme_resource_txns_mig
WHERE line_id = v_batchstep_rsrc_id
AND doc_id = v_old_batch_id;
SELECT inventory_item_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_recipe_validity_rule_id
AND organization_id = v_organization_id
AND ROWNUM = 1;
SELECT inventory_item_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_recipe_validity_rule_id
AND organization_id IS NULL
AND ROWNUM = 1;
UPDATE gme_batch_header
SET batch_no = SUBSTR(batch_no,1,30)||'-M'
WHERE batch_id = get_batches.batch_id;
UPDATE wip_entities
SET wip_entity_name = wip_entity_name||'-M'
WHERE entity_type = DECODE(get_batches.batch_type, 10, gme_common_pvt.g_wip_entity_type_fpo, gme_common_pvt.g_wip_entity_type_batch)
AND organization_id = get_batches.organization_id
AND wip_entity_name = l_prefix||get_batches.batch_no;
SELECT wip_entities_s.NEXTVAL INTO l_wip_entity_id FROM DUAL;
INSERT INTO wip_entities
(wip_entity_id, organization_id
,last_update_date, last_updated_by
,creation_date, created_by
,wip_entity_name
,entity_type
,gen_object_id
,primary_item_id)
VALUES (l_wip_entity_id, l_batch_header.organization_id
,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
,l_prefix||l_batch_header.batch_no
,DECODE (l_batch_header.batch_type
,0, gme_common_pvt.g_wip_entity_type_batch
,gme_common_pvt.g_wip_entity_type_fpo)
,mtl_gen_object_id_s.NEXTVAL
,l_primary_item_id);
INSERT INTO gme_batch_header VALUES l_batch_tbl(a);
SELECT gem5_line_id_s.NEXTVAL INTO l_mtl_dtl_tbl(i).material_detail_id FROM DUAL;
INSERT INTO gme_material_details VALUES l_mtl_dtl_tbl(a);
l_steps_tbl.delete;
l_activities_tbl.delete;
l_resources_tbl.delete;
l_proc_param_tbl.delete;
l_rsrc_txns_tbl.delete;
SELECT gme_batch_step_s.NEXTVAL INTO l_steps_tbl(i).batchstep_id FROM DUAL;
SELECT gme_batch_step_activity_s.NEXTVAL INTO l_activities_tbl(j).batchstep_activity_id FROM DUAL;
SELECT gem5_batchstepline_id_s.NEXTVAL INTO l_resources_tbl(k).batchstep_resource_id FROM DUAL;
SELECT gme_process_parameters_id_s.NEXTVAL INTO l_proc_param_tbl(m).process_param_id FROM DUAL;
SELECT gem5_poc_trans_id_s.NEXTVAL INTO l_rsrc_txns_tbl(n).poc_trans_id FROM DUAL;
gme_debug.put_line('After steps processing now inserting all step data');
INSERT INTO gme_batch_steps VALUES l_steps_tbl(a);
gme_debug.put_line('After insert steps');
INSERT INTO gme_batch_step_activities VALUES l_activities_tbl(a);
gme_debug.put_line('After insert activities');
INSERT INTO gme_batch_step_resources VALUES l_resources_tbl(a);
gme_debug.put_line('After insert resources');
INSERT INTO gme_process_parameters VALUES l_proc_param_tbl(a);
gme_debug.put_line('After insert process params');
INSERT INTO gme_resource_txns VALUES l_rsrc_txns_tbl(a);
gme_debug.put_line('After insert rsrc txns');
IF l_batch_header.batch_type = 0 AND NVL (l_batch_header.update_inventory_ind, 'Y') = 'Y' THEN
gme_move_orders_pvt.create_move_order_hdr
(p_organization_id => l_batch_header.organization_id
,p_move_order_type => gme_common_pvt.g_invis_move_order_type
,x_move_order_header_id => l_batch_header.move_order_header_id
,x_return_status => l_return_status);
UPDATE gme_batch_header
SET move_order_header_id = l_batch_header.move_order_header_id
WHERE batch_id = l_batch_header.batch_id;
UPDATE gme_material_details
SET move_order_line_id = l_mtl_dtl_tbl(a).move_order_line_id,
material_requirement_date = l_mtl_dtl_tbl(a).material_requirement_date
WHERE material_detail_id = l_mtl_dtl_tbl(a).material_detail_id;
gme_debug.put_line('After update material');
UPDATE gme_batch_header_mig
SET migrated_batch_ind = 'M'
WHERE batch_id = get_batches.batch_id;
gme_debug.put_line('Insert lab lots');
insert_lab_lots;
/* Bug 5703541 Added update stmt and loop for mtl_lot_conv_audit */
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line('Update mtl_lot_conv_audit');
FOR get_rec IN (SELECT old_batch_id, new_batch_id FROM gme_batch_mapping_mig
WHERE old_batch_id IN (SELECT DISTINCT(batch_id) FROM mtl_lot_conv_audit)) LOOP
UPDATE mtl_lot_conv_audit
SET batch_id = get_rec.new_batch_id
WHERE batch_id = get_rec.old_batch_id;
x_batch_header.update_inventory_ind := p_batch_header_mig.update_inventory_ind;
x_batch_header.last_update_date := p_batch_header_mig.last_update_date;
x_batch_header.last_updated_by := p_batch_header_mig.last_updated_by;
x_batch_header.last_update_login := p_batch_header_mig.last_update_login;
x_batch_header.delete_mark := p_batch_header_mig.delete_mark;
x_mtl_dtl(i).last_update_date := p_mtl_dtl_mig(i).last_update_date;
x_mtl_dtl(i).last_updated_by := p_mtl_dtl_mig(i).last_updated_by;
x_mtl_dtl(i).last_update_login := p_mtl_dtl_mig(i).last_update_login;
x_steps(i).delete_mark := p_steps_mig(i).delete_mark;
x_steps(i).last_update_date := p_steps_mig(i).last_update_date;
x_steps(i).last_updated_by := p_steps_mig(i).last_updated_by;
x_steps(i).last_update_login := p_steps_mig(i).last_update_login;
x_activities(l_cnt).delete_mark := p_activities_mig(i).delete_mark;
x_activities(l_cnt).last_update_date := p_activities_mig(i).last_update_date;
x_activities(l_cnt).last_updated_by := p_activities_mig(i).last_updated_by;
x_activities(l_cnt).last_update_login := p_activities_mig(i).last_update_login;
x_resources(l_cnt).last_update_login := p_resources_mig(i).last_update_login;
x_resources(l_cnt).last_update_date := p_resources_mig(i).last_update_date;
x_resources(l_cnt).last_updated_by := p_resources_mig(i).last_updated_by;
x_parameters(l_cnt).last_updated_by := p_parameters_mig(i).last_updated_by;
x_parameters(l_cnt).last_update_login := p_parameters_mig(i).last_update_login;
x_parameters(l_cnt).last_update_date := p_parameters_mig(i).last_update_date;
x_rsrc_txns(l_cnt).last_update_date := p_rsrc_txns_mig(i).last_update_date;
x_rsrc_txns(l_cnt).last_updated_by := p_rsrc_txns_mig(i).last_updated_by;
x_rsrc_txns(l_cnt).last_update_login := p_rsrc_txns_mig(i).last_update_login;
x_rsrc_txns(l_cnt).delete_mark := p_rsrc_txns_mig(i).delete_mark;
x_rsrc_txns(l_cnt).program_update_date := p_rsrc_txns_mig(i).program_update_date;
SELECT s.batchstep_id
FROM gme_batch_steps_mig m, gme_batch_steps s
WHERE m.batchstep_id = p_old_step_id
AND s.batch_id = p_new_batch_id
AND s.batchstep_no = m.batchstep_no;
SELECT d.material_detail_id
FROM gme_material_details_mig m, gme_material_details d
WHERE m.material_detail_id = p_old_mat_id
AND d.batch_id = p_new_batch_id
AND d.line_type = m.line_type
AND d.line_no = m.line_no;
SELECT *
FROM gme_batch_step_dep_mig
WHERE batch_id = v_batch_id;
l_step_dep_tbl(i).last_update_login := l_step_dep_mig_tbl(i).last_update_login;
l_step_dep_tbl(i).last_updated_by := l_step_dep_mig_tbl(i).last_updated_by;
l_step_dep_tbl(i).last_update_date := l_step_dep_mig_tbl(i).last_update_date;
INSERT INTO gme_batch_step_dependencies VALUES l_step_dep_tbl(a);
SELECT *
FROM gme_batch_step_items_mig
WHERE batch_id = p_old_batch_id;
l_item_step_tbl(i).last_update_login := l_item_step_mig_tbl(i).last_update_login;
l_item_step_tbl(i).last_update_date := l_item_step_mig_tbl(i).last_update_date;
l_item_step_tbl(i).last_updated_by := l_item_step_mig_tbl(i).last_updated_by;
INSERT INTO gme_batch_step_items VALUES l_item_step_tbl(a);
SELECT *
FROM gme_batch_step_charges_mig
WHERE batch_id = p_old_batch_id;
l_step_chrg_tbl(i).last_updated_by := l_step_chrg_mig_tbl(i).last_updated_by;
l_step_chrg_tbl(i).last_update_login := l_step_chrg_mig_tbl(i).last_update_login;
l_step_chrg_tbl(i).last_update_date := l_step_chrg_mig_tbl(i).last_update_date;
INSERT INTO gme_batch_step_charges VALUES l_step_chrg_tbl(a);
SELECT *
FROM gme_batch_step_transfers_mig
WHERE batch_id = p_old_batch_id;
SELECT gem5_wip_trans_id_s.NEXTVAL INTO l_step_txfrs_tbl(i).wip_trans_id FROM DUAL;
l_step_txfrs_tbl(i).last_updated_by := l_step_txfrs_mig_tbl(i).last_updated_by;
l_step_txfrs_tbl(i).last_update_date := l_step_txfrs_mig_tbl(i).last_update_date;
l_step_txfrs_tbl(i).last_update_login := l_step_txfrs_mig_tbl(i).last_update_login;
l_step_txfrs_tbl(i).delete_mark := l_step_txfrs_mig_tbl(i).delete_mark;
INSERT INTO gme_batch_step_transfers VALUES l_step_txfrs_tbl(a);
INSERT INTO gme_batch_mapping_mig(plant_code,
old_batch_id,
old_batch_no,
organization_id,
new_batch_id,
new_batch_no,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (p_batch_header_mig.plant_code,
p_batch_header_mig.batch_id,
SUBSTR(p_batch_header_mig.batch_no,1,30)||'-M',
p_batch_header.organization_id,
p_batch_header.batch_id,
p_batch_header.batch_no,
gme_common_pvt.g_user_ident,
gme_common_pvt.g_timestamp,
gme_common_pvt.g_user_ident,
gme_common_pvt.g_timestamp);
SELECT d.material_detail_id new_ing_line_id, d.phantom_id old_phantom_id, d.inventory_item_id,
m.plant_code, m.new_batch_no
FROM gme_material_details d, gme_batch_mapping_mig m
WHERE d.batch_id = m.new_batch_id
AND d.line_type = -1
AND d.phantom_id > 0
AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
SELECT new_batch_id
FROM gme_batch_mapping_mig
WHERE old_batch_id = v_batch_id;
SELECT material_detail_id
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = gme_common_pvt.g_line_type_prod
AND inventory_item_id = v_inventory_item_id
ORDER BY line_no;
/* Update Phantom ing */
UPDATE gme_material_details
SET phantom_id = l_new_phantom_id,
phantom_line_id = l_new_prod_line_id
WHERE material_detail_id = get_phantoms.new_ing_line_id;
/* Update phantom batch hdr */
UPDATE gme_batch_header
SET parentline_id = get_phantoms.new_ing_line_id
WHERE batch_id = l_new_phantom_id;
/* Update phantom product */
UPDATE gme_material_details
SET phantom_line_id = get_phantoms.new_ing_line_id
WHERE material_detail_id = l_new_prod_line_id;
SELECT m.*, o.actual_start_date
FROM gme_batch_header_mig o, gme_batch_mapping_mig m
WHERE o.batch_status = gme_common_pvt.g_batch_wip
AND m.old_batch_id = o.batch_id
AND o.parentline_id IS NULL
AND m.new_batch_id NOT IN (SELECT batch_id
FROM gme_batch_header
WHERE batch_id = m.new_batch_id
AND batch_status = gme_common_pvt.g_batch_wip)
ORDER BY m.organization_id, m.new_batch_no;
SELECT s.*, m.step_status old_step_status, m.actual_start_date old_actual_start_date,
m.actual_cmplt_date old_actual_cmplt_date
FROM gme_batch_steps_mig m, gme_batch_steps s
WHERE m.batch_id = v_old_batch_id
AND s.batch_id = v_new_batch_id
AND m.step_status > gme_common_pvt.g_step_pending
AND s.batchstep_no = m.batchstep_no
AND NOT(s.step_status = m.step_status)
ORDER BY s.batchstep_no;
SELECT d.material_detail_id, dm.wip_plan_qty
FROM gme_material_details d, gme_material_details_mig dm, gme_batch_mapping_mig hmap
WHERE d.line_type = dm.line_type
AND d.line_no = dm.line_no
AND d.item_id = dm.item_id
AND d.batch_id = hmap.new_batch_id
AND dm.batch_id = hmap.old_batch_id
AND d.wip_plan_qty <> dm.wip_plan_qty
AND d.batch_id = v_new_batch_id
AND hmap.old_batch_id = v_old_batch_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT batch_id
FROM gme_material_details
WHERE batch_id = v_batch_id
AND phantom_type > 0
AND phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig));
UPDATE gme_material_details
SET wip_plan_qty = get_wip_plan.wip_plan_qty
WHERE material_detail_id = get_wip_plan.material_detail_id;
UPDATE gme_resource_txns
SET trans_date = l_date
WHERE trans_date < l_date
AND POC_TRANS_ID IN (select t.POC_TRANS_ID
FROM gme_resource_txns t, gme_batch_step_resources r,
gme_batch_step_activities a, gme_batch_steps s
WHERE s.batch_id = l_batch_header.batch_id
AND t.doc_id = l_batch_header.batch_id
AND s.batchstep_id = l_step_rec.batchstep_id
AND s.batch_id = a.batch_id
AND s.batchstep_id = a.batchstep_id
AND r.batch_id = a.batch_id
AND r.batchstep_id = a.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND r.batch_id = t.doc_id
AND t.line_id = r.batchstep_resource_id);
SELECT subinventory_ind_flag
FROM ic_whse_mst
WHERE whse_code = v_whse_code;
SELECT secondary_inventory_name, organization_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = v_whse_code;
SELECT s.secondary_inventory_name, s.organization_id
FROM mtl_secondary_inventories s, ic_whse_mst w
WHERE secondary_inventory_name = v_whse_code
AND w.whse_code = s.secondary_inventory_name
AND s.organization_id = w.mtl_organization_id;
SELECT i.locator_id, m.organization_id, m.subinventory_code
FROM ic_loct_mst i, mtl_item_locations m
WHERE i.location = p_location
AND i.whse_code = p_whse_code
AND m.inventory_location_id = i.locator_id;
SELECT m.inventory_location_id locator_id, m.organization_id, m.subinventory_code
FROM mtl_item_locations m
WHERE m.segment1 = p_location
AND m.subinventory_code = x_subinventory;
SELECT revision
FROM mtl_item_revisions
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND effectivity_date <= gme_common_pvt.g_timestamp
ORDER BY effectivity_date DESC;
SELECT reason_id
FROM sy_reas_cds_b
WHERE reason_code = p_reason_code;
SELECT *
FROM ic_loct_mst
WHERE location = p_location;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
SELECT NVL(NVL(s.expense_account, s.material_account),NVL(m.expense_account, m.material_account))
FROM mtl_secondary_inventories s, mtl_parameters m
WHERE s.secondary_inventory_name = p_subinventory
AND m.organization_id = p_org_id;
SELECT d.*, i.mtl_transactions_enabled_flag, i.reservable_type, i.segment1, i.lot_control_code,
i.revision_qty_control_code, i.primary_uom_code, i.secondary_uom_code, i.restrict_subinventories_code,
NVL(i.location_control_code,1) location_control_code, i.restrict_locators_code, i.segment1 item_no
FROM gme_material_details d, mtl_system_items_b i
WHERE d.material_detail_id = v_material_detail_id
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id;
SELECT lot_no,sublot_no
FROM ic_lots_mst
WHERE lot_id = v_lot_id;
l_mmli_tbl.delete;
SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
l_mtl_dtl_rec.last_update_date := l_mtl_rec.last_update_date;
l_mtl_dtl_rec.last_updated_by := l_mtl_rec.last_updated_by;
l_mtl_dtl_rec.last_update_login := l_mtl_rec.last_update_login;
SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
-- Bug 13706812 - Changed update statement to take advantage of existing index which includes batch_id.
UPDATE gme_batch_txns_mig
SET migrated_ind = 1
WHERE trans_id = l_txns_tbl(i).trans_id
AND batch_id = l_txns_tbl(i).doc_id; -- This is the 11i batch id.
SELECT expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id
AND lot_number = v_lot_number;
SELECT i.mtl_transactions_enabled_flag, i.reservable_type, i.segment1, i.lot_control_code,
i.revision_qty_control_code, i.primary_uom_code, i.secondary_uom_code, i.restrict_subinventories_code,
NVL(i.location_control_code,1) location_control_code, i.restrict_locators_code, i.segment1 item_no
FROM mtl_system_items_b i
WHERE i.organization_id = v_organization_id
AND i.inventory_item_id = v_inventory_item_id;
SELECT m.inventory_location_id locator_id
FROM mtl_item_locations m
WHERE m.segment1 = v_location
AND m.organization_id = v_organization_id
AND m.subinventory_code = v_subinventory;
SELECT negative_inv_receipt_code, stock_locator_control_code, organization_code
FROM mtl_parameters
WHERE organization_id = v_org_id;
SELECT mtl_material_transactions_s.NEXTVAL INTO
l_issue_rec.transaction_interface_id FROM DUAL;
l_issue_rec.last_update_date := p_txn_rec.last_update_date;
l_issue_rec.last_updated_by := p_txn_rec.last_updated_by;
l_issue_lot_rec.last_update_date := p_txn_rec.last_update_date;
l_issue_lot_rec.last_updated_by := p_txn_rec.last_updated_by;
insert_interface_recs(p_mti_rec => l_issue_rec,
p_mtli_rec => l_issue_lot_rec,
x_return_status => l_return_status);
SELECT mtl_material_transactions_s.NEXTVAL INTO
l_receipt_rec.transaction_interface_id FROM DUAL;
SELECT whse_code
INTO l_receipt_rec.subinventory_code
FROM ic_whse_mst
WHERE mtl_organization_id = p_batch_org_id;
l_receipt_lot_rec.last_update_date := p_txn_rec.last_update_date;
l_receipt_lot_rec.last_updated_by := p_txn_rec.last_updated_by;
insert_interface_recs(p_mti_rec => l_receipt_rec,
p_mtli_rec => l_receipt_lot_rec,
x_return_status => l_return_status);
PROCEDURE insert_interface_recs(p_mti_rec IN mtl_transactions_interface%ROWTYPE,
p_mtli_rec IN mtl_transaction_lots_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_mti_tbl gme_common_pvt.mtl_tran_int_tbl;
l_api_name VARCHAR2(30) := 'insert_interface_recs';
INSERT INTO mtl_transactions_interface VALUES l_mti_tbl(a);
INSERT INTO mtl_transaction_lots_interface VALUES l_mtli_tbl(b);
END insert_interface_recs;
SELECT s.batchstep_id, m.step_close_date, bm.new_batch_no, s.batchstep_no, bm.plant_code, bm.old_batch_id
FROM gme_batch_steps_mig m, gme_batch_steps s, gme_batch_mapping_mig bm
WHERE m.step_status = gme_common_pvt.g_step_closed
AND bm.old_batch_id = m.batch_id
AND s.batch_id = bm.new_batch_id
AND s.batchstep_no = m.batchstep_no
AND NOT(s.step_status = m.step_status)
AND s.step_status = gme_common_pvt.g_step_completed
ORDER BY s.batch_id, s.batchstep_no;
SELECT 1
FROM gme_batch_step_items bsi, gme_material_details gmdn,
gme_material_details gmdo, gme_batch_txns_mig txn, ic_tran_pnd itp
WHERE bsi.batchstep_id = v_batchstep_id
AND gmdn.material_detail_id = bsi.material_detail_id
AND gmdo.batch_id = v_old_batch_id
AND gmdo.line_type = gmdn.line_type
AND gmdo.line_no = gmdn.line_no
AND txn.batch_id = v_old_batch_id
AND NVL(txn.migrated_ind, 0) = 0
AND itp.trans_id = txn.trans_id
AND itp.line_id = gmdo.material_detail_id;
p_delete_pending => fnd_api.g_false,
p_org_code => NULL,
p_batch_no => NULL,
x_batch_step_rec => l_out_step_rec);
PROCEDURE insert_lab_lots IS
l_api_name VARCHAR2(30) := 'insert_lab_lots';
SELECT l.*, m.organization_id, m.new_batch_no, m.new_batch_id, m.plant_code, l.rowid
FROM gme_batch_mapping_mig m, gme_lab_batch_lots l
WHERE m.old_batch_id = l.batch_id
AND NVL(attribute27, 'A') <> 'M';
SELECT i.segment1, i.lot_control_code
FROM mtl_system_items_b i
WHERE i.organization_id = v_organization_id
AND i.inventory_item_id = v_inventory_item_id;
SELECT d.*
FROM gme_material_details d
WHERE d.material_detail_id = v_material_detail_id;
UPDATE gme_lab_batch_lots
SET attribute27 = 'M'
WHERE rowid = get_lots.rowid;
END insert_lab_lots;