[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_message_into_table (
p_table_name IN VARCHAR2,
p_procedure_name IN VARCHAR2,
p_parameters IN VARCHAR2,
p_message IN VARCHAR2,
p_error_type IN VARCHAR2
) IS
PRAGMA autonomous_transaction;
INSERT INTO gme_temp_exceptions
(table_name,
procedure_name,
parameters,
message,
error_type,
script_date
)
VALUES (p_table_name,
p_procedure_name,
p_parameters,
TO_CHAR (SYSDATE, g_date_format) || ': ' || p_message,
p_error_type,
g_mig_date
);
END insert_message_into_table;
PROCEDURE insert_message (
p_table_name IN VARCHAR2,
p_procedure_name IN VARCHAR2,
p_parameters IN VARCHAR2,
p_message IN VARCHAR2,
p_error_type IN VARCHAR2
) IS
BEGIN
insert_message_into_table (
p_table_name => p_table_name,
p_procedure_name => p_procedure_name,
p_parameters => p_parameters,
p_message => p_message,
p_error_type => p_error_type
);
END insert_message;
insert_message (
p_table_name => 'DUAL',
p_procedure_name => 'initialize_migration',
p_parameters => 'none',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
UPDATE pm_btch_hdr_bak
SET in_use = 0
WHERE in_use IS NULL OR
in_use NOT in (0,100);
insert_message (
p_table_name => 'pm_btch_hdr',
p_procedure_name => 'unlock_all',
p_parameters => 'none',
p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_matl_dtl_bak
SET in_use = 0
WHERE in_use IS NULL OR
in_use NOT in (0,100);
insert_message (
p_table_name => 'pm_matl_dtl',
p_procedure_name => 'unlock_all',
p_parameters => 'none',
p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_rout_dtl
SET in_use = 0
WHERE in_use IS NULL OR
in_use NOT in (0,100);
insert_message (
p_table_name => 'pm_rout_dtl',
p_procedure_name => 'unlock_all',
p_parameters => 'none',
p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
insert_message (
p_table_name => 'pm_btch_hdr/pm_matl_dtl/pm_rout_dtl',
p_procedure_name => 'unlock_all',
p_parameters => 'none',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batch_id, batchstep_no
FROM pm_rout_dtl
WHERE delete_mark = 1;
SELECT batchstepline_id
FROM pm_oprn_dtl
WHERE delete_mark = 1;
UPDATE pm_oprn_dtl
SET delete_mark = 1
WHERE batch_id = l_cur_get_del_steps.batch_id AND
batchstep_no = l_cur_get_del_steps.batchstep_no;
UPDATE pc_tran_pnd
SET delete_mark = 1
WHERE line_id = l_cur_get_del_step_dtls.batchstepline_id;
insert_message (
p_table_name => 'pm_oprn_dtl/pc_tran_pnd',
p_procedure_name => 'del_step_dtl_for_del_steps',
p_parameters => 'none',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'get_actual_date',
p_parameters => TO_CHAR (p_date, g_date_format),
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT plan_rsrc_count * plan_rsrc_usage
INTO v_resource_usage
FROM pm_oprn_dtl
WHERE batchstepline_id = p_batchstepline_id;
insert_message (
p_table_name => 'pm_oprn_dtl',
p_procedure_name => 'get_planned_usage',
p_parameters => p_batchstepline_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT step_status
INTO l_step_status
FROM gme_batch_steps
WHERE batchstep_id = p_batchstep_id;
insert_message (
p_table_name => 'gme_batch_steps',
p_procedure_name => 'get_actual_activity_factor',
p_parameters => p_batchstep_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT SUM (resource_usage)
INTO v_resource_usage
FROM pc_tran_pnd
WHERE line_id = p_line_id AND
delete_mark <> 1 AND
completed_ind = 1;
insert_message (
p_table_name => 'pc_tran_pnd',
p_procedure_name => 'get_actual_usage',
p_parameters => p_line_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT oprn_id
INTO v_oprn_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id AND
batchstep_no = p_batchstep_no;
insert_message (
p_table_name => 'gme_batch_steps',
p_procedure_name => 'get_oprn_id',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' batchstep_no = '
|| p_batchstep_no,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT process_qty_um
INTO v_process_qty_uom
FROM fm_oprn_mst
WHERE oprn_id = p_oprn_id;
insert_message (
p_table_name => 'fm_oprn_mst',
p_procedure_name => 'get_process_qty_uom',
p_parameters => p_oprn_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT NVL (MAX (rollover_ind), -1)
INTO v_actual_cost_ind
FROM cm_cmpt_dtl ccd,
cm_acst_led acl,
pm_matl_dtl_bak bdtl,
pm_btch_hdr_bak bhdr
WHERE ccd.cmpntcost_id = acl.cmpntcost_id AND
ccd.delete_mark = 0 AND
acl.source_ind = 0 AND
acl.transline_id = bdtl.line_id AND
bdtl.batch_id = bhdr.batch_id AND
bhdr.batch_id = p_batch_id;
insert_message (
p_table_name => 'cm_cmpt_dtl, cm_acst_led, pm_matl_dtl_bak, pm_btch_hdr_bak',
p_procedure_name => 'get_actual_cost_ind',
p_parameters => p_batch_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT NVL (
MAX (gl_posted_ind),
0
) -- for those customers (IMCO and maybe others) who have manually reopened
-- batches via sqlplus and then closed through the application.
INTO v_gl_posted_ind
FROM pm_hist_hdr
WHERE batch_id = p_batch_id AND
new_status = 4;
insert_message (
p_table_name => 'pm_hist_hdr',
p_procedure_name => 'get_gl_posted_ind',
p_parameters => p_batch_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT 1
INTO v_exist
FROM pm_rout_dtl
WHERE batch_id = p_batch_id AND
delete_mark <> 1 AND
ROWNUM = 1;
insert_message (
p_table_name => 'pm_rout_dtl',
p_procedure_name => 'get_poc_data_ind',
p_parameters => p_batch_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT std_um
INTO v_ref_uom
FROM sy_uoms_typ
WHERE um_type = p_uom_class;
insert_message (
p_table_name => 'sy_uoms_typ',
p_procedure_name => 'get_ref_uom',
p_parameters => p_uom_class,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batchstep_id
INTO v_batchstep_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id AND
batchstep_no = p_batchstep_no;
insert_message (
p_table_name => 'gme_batch_steps',
p_procedure_name => 'get_batchstep_id',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' batchstep_no = '
|| p_batchstep_no,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batchstep_id
INTO v_step_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id AND
batchstep_no = p_batchstep_no;
SELECT batchstep_activity_id
INTO v_activity_id
FROM gme_batch_step_activities
WHERE batch_id = p_batch_id AND
batchstep_id = v_step_id AND
activity = p_activity;
insert_message (
p_table_name => 'gme_batch_steps, gme_batch_step_activities',
p_procedure_name => 'get_activity_id',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' batchstep_no = '
|| p_batchstep_no
|| ' activity = '
|| p_activity,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT offset_interval
INTO l_act_offset
FROM gme_batch_step_activities
WHERE activity = p_activity AND
batch_id = p_batch_id AND
batchstep_id = (SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id AND
batchstep_no = p_batchstep_no);
insert_message (
p_table_name => 'gme_batch_step_activities',
p_procedure_name => 'get_rsrc_offset',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' batchstep_no = '
|| p_batchstep_no
|| ' activity = '
|| p_activity
|| ' activity offset = '
|| p_offset,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT min_capacity,
max_capacity,
capacity_uom
FROM cr_rsrc_dtl
WHERE resources = v_rsrc AND
orgn_code = (SELECT plant_code
FROM pm_btch_hdr_bak
WHERE batch_id = v_batch_id);
insert_message (
p_table_name => 'cr_rsrc_dtl',
p_procedure_name => 'get_capacity',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' resource = '
|| p_resources,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'get_min_capacity',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' resource = '
|| p_rsrc,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'get_max_capacity',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' resource = '
|| p_rsrc,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'get_capacity_uom',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' resource = '
|| p_rsrc,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batch_status, batch_type
INTO v_batch_status, v_batch_type
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT SUM (
gmicuom.uom_conversion (
i.item_id,
i.lot_id,
i.trans_qty,
i.trans_um,
l.item_um,
0
)
)
INTO v_qty
FROM ic_tran_pnd i, pm_matl_dtl_bak l
WHERE doc_id = p_batch_id AND
doc_type IN ('PROD', 'FPO') AND
i.line_id = p_line_id AND
i.line_id = l.line_id AND
completed_ind = 1 AND
delete_mark = 0;
SELECT line_type
INTO v_line_type
FROM pm_matl_dtl_bak
WHERE line_id = p_line_id;
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'get_actual_qty',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' line_id = '
|| p_line_id
|| ' old actual_qty = '
|| p_actual_qty,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batch_status, plant_code, batch_no, batch_type
INTO v_batch_status, v_plant_code, v_batch_no, v_batch_type
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT SUM (
gmicuom.uom_conversion (
i.item_id,
i.lot_id,
i.trans_qty / (1 + l.scrap_factor),
i.trans_um,
l.item_um,
0
)
)
INTO v_qty
FROM ic_tran_pnd i, pm_matl_dtl_bak l
WHERE doc_id = p_batch_id AND
doc_type IN ('PROD', 'FPO') AND
i.line_id = p_line_id AND
i.line_id = l.line_id AND
delete_mark = 0;
SELECT line_type, line_no
INTO v_line_type, v_line_no
FROM pm_matl_dtl_bak
WHERE line_id = p_line_id;
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'get_planned_qty',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' line_id = '
|| p_line_id
|| ' original plan_qty = '
|| p_plan_qty,
p_message =>
'Plan quantity could not be calculated from transactions for '||l_batch_type_desc||
' with plant code = '||v_plant_code||'- batch no = '||v_batch_no||
' and '||l_line_type_desc||' line no = '||v_line_no||'. Using original plan_qty.',
p_error_type => 'I'
);
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'get_planned_qty',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' line_id = '
|| p_line_id
|| ' original plan_qty = '
|| p_plan_qty,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT batch_status
INTO v_batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT scrap_factor,
item_um,
line_type
INTO v_scrap_factor,
v_item_um,
v_line_type
FROM pm_matl_dtl_bak
WHERE line_id = p_line_id;
SELECT SUM (
gmicuom.uom_conversion (
item_id,
lot_id,
trans_qty / (1 + v_scrap_factor),
trans_um,
v_item_um,
0
)
)
INTO v_qty
FROM ic_tran_pnd
WHERE doc_id = p_batch_id AND
doc_type IN ('PROD', 'FPO') AND
line_id = p_line_id AND
delete_mark = 0;
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'get_wip_planned_qty',
p_parameters => 'batch_id = '
|| p_batch_id
|| ' line_id = '
|| p_line_id
|| ' old actual_qty = '
|| p_actual_qty,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
error_inserting_txn EXCEPTION;
SELECT h.batch_id,
h.plant_code,
h.batch_type,
h.batch_no,
d.line_no,
d.line_id
FROM pm_btch_hdr_bak h, pm_matl_dtl_bak d
WHERE h.batch_status = 2 AND
d.batch_id = h.batch_id AND
d.line_type IN (-1, 1) AND
d.in_use <
100 -- => ensure you only get those materials that were not migrated.
ORDER BY h.plant_code, h.batch_type, h.batch_no, d.line_no;
insert_message (
p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
p_procedure_name => 'check_wip_batches',
p_parameters => 'batch_id = '
|| l_batch_header.batch_id
|| ' line_id = '
|| get_rec.line_id,
p_message => 'Unable to load the transactions',
p_error_type => FND_API.G_RET_STS_ERROR
);
insert_message (
p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
p_procedure_name => 'check_wip_batches',
p_parameters => 'batch_id = '
|| l_batch_header.batch_id
|| ' line_id = '
|| get_rec.line_id,
p_message => 'Unable to determine the default lot.',
p_error_type => FND_API.G_RET_STS_ERROR
);
SELECT completed_ind,
trans_qty
INTO l_completed_ind,
l_trans_qty
FROM ic_tran_pnd
WHERE trans_id = l_def_lot_id;
SELECT *
INTO l_tran_row
FROM ic_tran_pnd
WHERE trans_id = l_def_lot_id;
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (get_rec.batch_id),
p_message => l_message,
p_error_type => FND_API.G_RET_STS_ERROR
);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (get_rec.batch_id),
p_message => l_message,
p_error_type => 'I'
);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (get_rec.batch_id)
|| ' line_id=>'
|| TO_CHAR (get_rec.line_id)
|| ' trans_id=>'
|| TO_CHAR(l_tran_row.trans_id),
p_message => l_message,
p_error_type => l_return_status
);
insert_inv_txns_gtmp (
p_batch_id => l_ic_tran_cmp_out.doc_id,
p_doc_type => l_ic_tran_cmp_out.doc_type,
p_trans_id => l_ic_tran_cmp_out.trans_id,
x_return_status => l_return_status
);
RAISE error_inserting_txn;
/* Insert a pending transaction */
gmi_trans_engine_pub.create_pending_transaction (
1,
FND_API.g_false,
FND_API.g_false,
FND_API.g_valid_level_full,
l_tran_pending,
l_ic_tran_pnd_out,
l_return_status,
l_msg_count,
l_msg_data
);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (get_rec.batch_id)
|| ' line_id=>'
|| TO_CHAR (get_rec.line_id)
|| ' trans_id=>'
|| TO_CHAR(l_tran_row.trans_id),
p_message => l_message,
p_error_type => l_return_status
);
insert_inv_txns_gtmp (
p_batch_id => l_ic_tran_pnd_out.doc_id,
p_doc_type => l_ic_tran_pnd_out.doc_type,
p_trans_id => l_ic_tran_pnd_out.trans_id,
x_return_status => l_return_status
);
RAISE error_inserting_txn;
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE trans_id IN
(l_def_lot_id, l_ic_tran_cmp_out.trans_id);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (get_rec.batch_id),
p_message => l_message,
p_error_type => l_return_status
);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => 'Batch_Id=>' || TO_CHAR (get_rec.batch_id),
p_message => 'Completed default lot transaction found for batch '
|| get_rec.batch_no
|| ' in plant '
|| get_rec.plant_code
|| ' for '
|| l_line_type_desc
|| ' line number '
|| get_rec.line_no,
p_error_type => 'I'
);
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'check_wip_batches',
p_parameters => 'none',
p_message => 'number of transactions reversed = ' || l_reversal_count,
p_error_type => 'P'
);
WHEN error_create_tran OR error_build_ic_tran_row OR error_inserting_txn THEN
x_return_status := l_return_status;
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'CHECK_WIP_BATCHES',
p_parameters => '',
p_message => SQLERRM,
p_error_type => 'D'
);
insert_message (
p_table_name => 'IC_TRAN_PND',
p_procedure_name => 'BUILD_GMI_TRANS',
p_parameters => 'Batch_Id=>'
|| TO_CHAR (p_ic_tran_row.doc_id)
|| 'Trans ID=>'
|| TO_CHAR (p_ic_tran_row.trans_id),
p_message => SQLERRM,
p_error_type => x_return_status
);
insert_message (
p_table_name => 'gme_migration_control',
p_procedure_name => 'is_GME_validated',
p_parameters => l_table_name,
p_message => SQLERRM,
p_error_type => 'D'
);
UPDATE gme_migration_control
SET migrated_ind = 'Y',
last_update_date = g_mig_date
WHERE table_name = l_table_name;
insert_message (
p_table_name => 'gme_migration_control',
p_procedure_name => 'set_GME_validated',
p_parameters => l_table_name,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT migrated_ind
FROM gme_migration_control
WHERE table_name = v_table_name;
INSERT INTO gme_migration_control
(table_name,
migrated_ind,
last_update_date
)
VALUES (l_table_name,
'N',
g_mig_date
);
UPDATE gme_migration_control
SET migrated_ind = 'N',
last_update_date = g_mig_date
WHERE table_name = l_table_name;
insert_message (
p_table_name => 'gme_migration_control',
p_procedure_name => 'reset_GME_validated',
p_parameters => l_table_name,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT migrated_ind
FROM gme_migration_control
WHERE table_name = v_table_name;
INSERT INTO gme_migration_control
(table_name,
migrated_ind,
last_update_date
)
VALUES (p_table_name,
'N',
g_mig_date
);
insert_message (
p_table_name => 'gme_migration_control',
p_procedure_name => 'is_table_migrated',
p_parameters => p_table_name,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
UPDATE gme_migration_control
SET migrated_ind = 'Y',
last_update_date = g_mig_date
WHERE table_name = p_table_name;
insert_message (
p_table_name => 'gme_migration_control',
p_procedure_name => 'set_table_migrated',
p_parameters => p_table_name,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT default_tablespace tablespace_name
FROM dba_users
WHERE username like v_User
UNION
SELECT distinct tablespace_name
FROM all_tables
WHERE owner like v_User;
SELECT sum(bytes) into l_total_space
FROM dba_data_files
WHERE TABLESPACE_NAME = l_cur_ts_name.tablespace_name;
SELECT sum(bytes) into l_free_space
FROM dba_free_space
WHERE TABLESPACE_NAME = l_cur_ts_name.tablespace_name;
insert_message (
p_table_name => 'none',
p_procedure_name => 'tablespace_check',
p_parameters => 'USER= '||p_User||
' tablespace name= '||l_cur_ts_name.tablespace_name||
' total space= '||to_char(l_total_space)||' bytes'||
' free space= '||to_char(l_free_space)||' bytes'||
' target minimum % free= '||to_char(p_pct_free)||
' actual % free= '||to_char(l_pct_free),
p_message => 'Tablespace information',
p_error_type => 'I'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'tablespace_check',
p_parameters => '',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT count(1)
FROM pm_btch_hdr_bak;
insert_message (
p_table_name => 'none',
p_procedure_name => 'GME_data_exists',
p_parameters => '',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
insert_message (
p_table_name => NULL,
p_procedure_name => 'reset_GME_validated',
p_parameters => NULL,
p_message => NULL,
p_error_type => 'V' -- validation control
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'run',
p_parameters => 'p_commit = '||l_commit_text,
p_message => 'Procedure has started',
p_error_type => 'P'
);
insert_batch_header (x_return_status => l_return_status);
insert_material_details (x_return_status => l_return_status);
insert_batch_steps (x_return_status => l_return_status);
insert_batch_step_dtls (x_return_status => l_return_status);
insert_batch_step_items (x_return_status => l_return_status);
insert_batch_step_dependencies (x_return_status => l_return_status);
insert_batch_history (x_return_status => l_return_status);
insert_batch_step_transfers (x_return_status => l_return_status);
insert_text_header (x_return_status => l_return_status);
insert_text_dtl (x_return_status => l_return_status);
insert_message (
p_table_name => NULL,
p_procedure_name => 'set_GME_validated',
p_parameters => NULL,
p_message => NULL,
p_error_type => 'V' -- validation control
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'run',
p_parameters => 'p_commit = '||l_commit_text,
p_message => 'Procedure has ended',
p_error_type => 'P'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'run',
p_parameters => 'p_commit = ' || l_commit_text,
p_message => 'GME '||l_message||' found no data.',
p_error_type => 'I'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'run',
p_parameters => 'p_commit = ' || l_commit_text,
p_message => 'Validation must be successfully run prior to running migration.',
p_error_type => 'D'
);
insert_message (
p_table_name => 'none',
p_procedure_name => 'run',
p_parameters => 'p_commit = ' || l_commit_text,
p_message => SQLERRM || ' with pos = ' || l_pos || l_message,
p_error_type => 'D'
);
PROCEDURE insert_batch_header (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_batch_header
(batch_id,
plant_code,
batch_no,
batch_type,
prod_id,
prod_sequence,
recipe_validity_rule_id,
formula_id,
routing_id,
plan_start_date,
actual_start_date,
due_date,
plan_cmplt_date,
actual_cmplt_date,
batch_status,
priority_value,
priority_code,
print_count,
fmcontrol_class,
wip_whse_code,
batch_close_date,
poc_ind,
actual_cost_ind,
gl_posted_ind,
update_inventory_ind,
automatic_step_calculation,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
text_code,
parentline_id,
fpo_id,
migrated_batch_ind,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT batch_id,
plant_code,
batch_no,
batch_type,
prod_id,
prod_sequence,
fmeff_id,
formula_id,
routing_id,
plan_start_date,
get_actual_date (actual_start_date),
due_date,
expct_cmplt_date,
get_actual_date (actual_cmplt_date),
batch_status,
priority_value,
priority_code,
print_count,
fmcontrol_class,
wip_whse_code,
get_actual_date (batch_close_date),
get_poc_data_ind (batch_id),
get_actual_cost_ind (batch_id),
get_gl_posted_ind (batch_id),
'Y' --update_inventory_ind => lab batches introduced in 11I+
,
0 --automatic_step_calculation
,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
text_code,
parentline_id,
NULL --fpo_id
,
'Y' --migrated_batch_ind
,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM pm_btch_hdr_bak
WHERE in_use < 100;
insert_message (
p_table_name => 'gme_batch_header',
p_procedure_name => 'insert_batch_header',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
/* If we get here, that means that the above insert was successful;
UPDATE pm_btch_hdr_bak
SET in_use = in_use + 100
WHERE in_use < 100;
insert_message (
p_table_name => 'pm_btch_hdr_bak/gme_batch_header',
p_procedure_name => 'insert_batch_header',
p_parameters => 'none',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_header;
PROCEDURE insert_material_details (x_return_status OUT NOCOPY VARCHAR2) IS
l_count NUMBER (5) DEFAULT 0;
INSERT INTO gme_material_details
(material_detail_id,
batch_id,
formulaline_id,
line_no,
item_id,
line_type,
plan_qty,
item_um,
item_um2,
actual_qty,
original_qty,
wip_plan_qty,
release_type,
scrap_factor,
scale_type,
contribute_yield_ind,
scale_multiple,
scale_rounding_variance,
rounding_direction,
contribute_step_qty_ind,
phantom_type,
cost_alloc,
alloc_ind,
cost,
text_code,
phantom_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT line_id,
batch_id,
formulaline_id,
line_no,
item_id,
line_type,
get_planned_qty (batch_id, line_id, plan_qty),
item_um,
item_um2,
get_actual_qty (batch_id, line_id, actual_qty),
plan_qty --original_qty
,
get_wip_planned_qty (batch_id, line_id, actual_qty),
release_type,
scrap_factor,
DECODE (scale_type, 0, 0, 1, 1, 2, 0, 3, 1, scale_type),
DECODE (scale_type, 2, 'N', 'Y') --contribute_yield_ind
,
NULL --scale_multiple
,
NULL --scale_rounding_variance
,
NULL --rounding_direction
,
'Y' --contribute_step_qty_ind
,
phantom_type,
cost_alloc,
alloc_ind,
cost,
text_code,
phantom_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM pm_matl_dtl_bak
WHERE in_use < 100;
insert_message (
p_table_name => 'gme_material_details',
p_procedure_name => 'insert_material_details',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_matl_dtl_bak
SET in_use = in_use + 100
WHERE in_use < 100;
insert_message (
p_table_name => 'GME_MATERIAL_DETAILS',
p_procedure_name => 'INSERT_MATERIAL_DETAILS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_material_details;
PROCEDURE insert_batch_steps (x_return_status OUT NOCOPY VARCHAR2) IS
v_step_rec pm_rout_dtl%ROWTYPE;
INSERT INTO gme_batch_steps
(batch_id,
batchstep_id,
routingstep_id,
batchstep_no,
oprn_id,
plan_step_qty,
actual_step_qty,
step_qty_uom,
backflush_qty,
plan_start_date,
actual_start_date,
due_date,
plan_cmplt_date,
actual_cmplt_date,
step_close_date,
step_status,
priority_code,
priority_value,
steprelease_type,
max_step_capacity,
max_step_capacity_uom,
plan_charges,
actual_charges,
text_code,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
mass_ref_uom,
volume_ref_uom,
plan_volume_qty,
plan_mass_qty,
actual_volume_qty,
actual_mass_qty
)
SELECT batch_id,
gme_batch_step_s.NEXTVAL --batchstep_id
,
routingstep_id,
batchstep_no,
oprn_id,
plan_step_qty,
actual_step_qty,
get_process_qty_uom (oprn_id) --step_qty_uom
,
backflush_qty,
plan_start_date,
get_actual_date (actual_start_date),
due_date,
expct_cmplt_date,
get_actual_date (actual_cmplt_date),
get_actual_date (step_close_date),
step_status,
priority_code,
priority_value,
1 --steprelease_type
,
NULL --max_step_capacity
,
NULL --max_step_capacity_uom
,
NULL --plan_charges
,
NULL --actual_charges
,
text_code,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
get_ref_uom (
fnd_profile.VALUE ('LM$UOM_MASS_TYPE')
) --mass_ref_uom
,
get_ref_uom (
fnd_profile.VALUE ('LM$UOM_VOLUME_TYPE')
) --volume_ref_uom
,
NULL --plan_volume_qty
,
NULL --plan_mass_qty
,
NULL --actual_volume_qty
,
NULL --actual_mass_qty
FROM pm_rout_dtl
WHERE in_use < 100 AND
delete_mark <> 1;
insert_message (
p_table_name => 'gme_batch_steps',
p_procedure_name => 'insert_batch_steps',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_rout_dtl
SET in_use = in_use + 100
WHERE in_use < 100 AND
delete_mark <> 1;
insert_message (
p_table_name => 'GME_BATCH_STEPS',
p_procedure_name => 'INSERT_BATCH_STEPS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_steps;
PROCEDURE insert_batch_step_dtls (x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR get_activities IS
SELECT batch_id,
batchstep_no,
activity,
MIN (offset_interval) min_offset,
MIN (plan_start_date) plan_start_date,
MAX (plan_cmplt_date) plan_cmplt_date,
MIN (actual_start_date) actual_start_date,
MAX (actual_cmplt_date) actual_cmplt_date
FROM pm_oprn_dtl
WHERE delete_mark < 100 AND
delete_mark <> 1
GROUP BY batch_id, batchstep_no, activity;
SELECT *
FROM pm_oprn_dtl
WHERE batch_id = v_batch_id AND
batchstep_no = v_step_no AND
activity = v_activity AND
offset_interval = v_offset AND
delete_mark < 100 AND
delete_mark <> 1;
error_insert_rsrc_txns EXCEPTION;
INSERT INTO gme_batch_step_activities
(batch_id,
activity,
batchstep_id,
batchstep_activity_id,
oprn_line_id,
offset_interval,
plan_start_date,
actual_start_date,
plan_cmplt_date,
actual_cmplt_date,
plan_activity_factor,
actual_activity_factor,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT v_oprn_dtl.batch_id,
v_oprn_dtl.activity,
get_batchstep_id (
v_oprn_dtl.batch_id,
v_oprn_dtl.batchstep_no
) --batchstep_id
,
gme_batch_step_activity_s.NEXTVAL --batchstep_activity_id
,
v_oprn_dtl.oprn_line_id,
v_oprn_dtl.offset_interval,
v_activities.plan_start_date,
get_actual_date (v_activities.actual_start_date),
v_activities.plan_cmplt_date,
get_actual_date (v_activities.actual_cmplt_date),
1 --plan_activity_factor
,
get_actual_activity_factor (
get_batchstep_id (
v_oprn_dtl.batch_id,
v_oprn_dtl.batchstep_no
)
) --actual_activity_factor
,
v_oprn_dtl.delete_mark,
v_oprn_dtl.created_by,
v_oprn_dtl.creation_date,
v_oprn_dtl.last_updated_by,
v_oprn_dtl.last_update_date,
v_oprn_dtl.last_update_login
FROM sys.DUAL;
insert_message (
p_table_name => 'gme_batch_step_activities',
p_procedure_name => 'insert_batch_step_dtls',
p_parameters => 'none',
p_message => 'number of records inserted = ' || l_act_cnt,
p_error_type => 'P'
);
INSERT INTO gme_batch_step_resources
(batchstep_resource_id,
batchstep_activity_id,
resources,
cost_analysis_code,
cost_cmpntcls_id,
prim_rsrc_ind,
scale_type,
plan_rsrc_count,
actual_rsrc_count,
resource_qty_uom,
plan_rsrc_usage,
actual_rsrc_usage,
usage_uom,
plan_start_date,
actual_start_date,
plan_cmplt_date,
actual_cmplt_date,
offset_interval,
min_capacity,
max_capacity,
process_parameter_1,
process_parameter_2,
process_parameter_3,
process_parameter_4,
process_parameter_5,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
text_code,
batch_id,
batchstep_id,
capacity_uom,
actual_rsrc_qty,
plan_rsrc_qty,
calculate_charges
)
SELECT batchstepline_id --batchstep_resource_id
/* batchstepline_id is used as the line_id for the pc_tran_pnd table */
,
get_activity_id (
batch_id,
batchstep_no,
activity
) --batchstep_activity_id
,
resources,
cost_analysis_code,
cost_cmpntcls_id,
prim_rsrc_ind,
scale_type,
plan_rsrc_count,
actual_rsrc_count,
get_process_qty_uom (
get_oprn_id (batch_id, batchstep_no)
) --resource_qty_uom
,
get_planned_usage (batchstepline_id) --plan_rsrc_usage
,
get_actual_usage (batchstepline_id) --actual_rsrc_usage
,
usage_um,
plan_start_date,
get_actual_date (actual_start_date),
plan_cmplt_date,
get_actual_date (actual_cmplt_date),
get_rsrc_offset (
batch_id,
batchstep_no,
activity,
offset_interval
) --offset_interval
,
get_min_capacity (batch_id, resources) --min_capacity
,
get_max_capacity (batch_id, resources) --max_capacity
,
NULL --process_parameter_1
,
NULL --process_parameter_2
,
NULL --process_parameter_3
,
NULL --process_parameter_4
,
NULL --process_parameter_5
,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
text_code,
batch_id,
get_batchstep_id (batch_id, batchstep_no) --batchstep_id
,
get_capacity_uom (batch_id, resources) --capacity_uom
,
actual_rsrc_qty,
plan_rsrc_qty,
0 --calculate_charges
FROM pm_oprn_dtl
WHERE delete_mark < 100 AND
delete_mark <> 1;
insert_message (
p_table_name => 'gme_batch_step_resources',
p_procedure_name => 'insert_batch_step_dtls',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_oprn_dtl
SET delete_mark = delete_mark + 100
WHERE delete_mark < 100 AND
delete_mark <> 1;
insert_resource_txns (l_return_status);
RAISE error_insert_rsrc_txns;
WHEN error_insert_rsrc_txns THEN
x_return_status := l_return_status;
insert_message (
p_table_name => 'PM_OPRN_DTL',
p_procedure_name => 'INSERT_BATCH_STEP_DTLS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_step_dtls;
PROCEDURE insert_batch_step_items (x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2 (1);
INSERT INTO gme_batch_step_items
(material_detail_id,
batch_id,
batchstep_id,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT batchline_id,
MIN (batch_id),
get_batchstep_id (
MIN (batch_id),
MIN (batchstep_no)
) --batchstep_id
,
MIN (text_code),
MIN (created_by),
MIN (creation_date),
MIN (last_updated_by),
MIN (last_update_date),
MIN (last_update_login)
FROM pm_rout_mtl pm
WHERE NOT EXISTS ( SELECT 1
FROM gme_batch_step_items
WHERE material_detail_id = pm.batchline_id)
AND
EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = pm.batch_id AND
step.batchstep_no = pm.batchstep_no)
AND
EXISTS ( SELECT 1
FROM gme_material_details matl
WHERE matl.batch_id = pm.batch_id AND
matl.material_detail_id = pm.batchline_id)
GROUP BY batchline_id;
insert_message (
p_table_name => 'gme_batch_step_items',
p_procedure_name => 'insert_batch_step_items',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
insert_message (
p_table_name => 'GME_BATCH_STEP_ITEMS',
p_procedure_name => 'INSERT_BATCH_STEP_ITEMS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_step_items;
SELECT batch_id, batchstep_no, batchline_id
FROM pm_rout_mtl pm
WHERE NOT EXISTS ( SELECT 1
FROM gme_batch_step_items
WHERE material_detail_id = pm.batchline_id)
AND
(
NOT EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = pm.batch_id AND
step.batchstep_no = pm.batchstep_no) OR
NOT EXISTS ( SELECT 1
FROM gme_material_details matl
WHERE matl.batch_id = pm.batch_id AND
matl.material_detail_id = pm.batchline_id)
);
insert_message (
p_table_name => 'gme_batch_step_items',
p_procedure_name => 'report_step_item_orphans',
p_parameters => 'Batch_id=>'||to_char(l_cur_get_orphans.batch_id)
||' step_no=>'||l_cur_get_orphans.batchstep_no
||' material_detail_id=>'||to_char(l_cur_get_orphans.batchline_id),
p_message => 'Step or material does not exist in parent table; item / step association will not be migrated',
insert_message (
p_table_name => 'GME_BATCH_STEP_ITEMS',
p_procedure_name => 'REPORT_STEP_ITEM_ORPHANS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => 'D'
);
PROCEDURE insert_batch_step_dependencies (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_batch_step_dependencies
(batch_id,
batchstep_id,
dep_type,
dep_step_id,
rework_code,
standard_delay,
min_delay,
max_delay,
transfer_qty,
transfer_um,
transfer_percent,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT batch_id,
get_batchstep_id (batch_id, batchstep_no) --batchstep_id
,
dep_type,
get_batchstep_id (batch_id, dep_step_no) --dep_step_id
,
rework_code,
standard_delay,
min_delay,
max_delay,
transfer_qty,
transfer_um,
100 --transfer_percent
,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
NULL --attribute1
,
NULL --attribute2
,
NULL --attribute3
,
NULL --attribute4
,
NULL --attribute5
,
NULL --attribute6
,
NULL --attribute7
,
NULL --attribute8
,
NULL --attribute9
,
NULL --attribute10
,
NULL --attribute11
,
NULL --attribute12
,
NULL --attribute13
,
NULL --attribute14
,
NULL --attribute15
,
NULL --attribute16
,
NULL --attribute17
,
NULL --attribute18
,
NULL --attribute19
,
NULL --attribute20
,
NULL --attribute21
,
NULL --attribute22
,
NULL --attribute23
,
NULL --attribute24
,
NULL --attribute25
,
NULL --attribute26
,
NULL --attribute27
,
NULL --attribute28
,
NULL --attribute29
,
NULL --attribute30
,
NULL --attribute_category
FROM pm_rout_dep dep
WHERE dep_type <
100 -- Only bring over dependencies for which both steps are still defined...
-- If there is a record in dep table and not the 2 corresponding rows in step table, that means
-- the record was marked for delete in the steps table, and, the old code
-- did not delete the dependency.
AND
EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = dep.batch_id AND
step.batchstep_no = dep.batchstep_no) AND
EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = dep.batch_id AND
step.batchstep_no = dep.dep_step_no);
insert_message (
p_table_name => 'gme_batch_step_dependencies',
p_procedure_name => 'insert_batch_step_dependencies',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_rout_dep
SET dep_type = dep_type + 100
WHERE dep_type < 100;
insert_message (
p_table_name => 'GME_BATCH_STEP_DEPENDENCIES',
p_procedure_name => 'INSERT_BATCH_STEP_DEPENDENCIES',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_step_dependencies;
SELECT batch_id, batchstep_no, dep_step_no
FROM pm_rout_dep dep
WHERE dep_type < 100 AND
(NOT EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = dep.batch_id AND
step.batchstep_no = dep.batchstep_no) OR
NOT EXISTS ( SELECT 1
FROM gme_batch_steps step
WHERE step.batch_id = dep.batch_id AND
step.batchstep_no = dep.dep_step_no));
insert_message (
p_table_name => 'gme_batch_step_dependencies',
p_procedure_name => 'report_step_dep_orphans',
p_parameters => 'Batch_id=>'||to_char(l_cur_get_orphans.batch_id)
||' step_no=>'||l_cur_get_orphans.batchstep_no
||' dep_step_no=>'||l_cur_get_orphans.dep_step_no,
p_message => 'Step or dependent step does not exist in steps table; dependency will not be migrated',
insert_message (
p_table_name => 'GME_BATCH_STEP_DEPENDENCIES',
p_procedure_name => 'REPORT_STEP_DEP_ORPHANS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => 'D'
);
PROCEDURE insert_resource_txns (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_resource_txns
(poc_trans_id,
orgn_code,
doc_type,
doc_id,
line_type,
line_id,
resources,
resource_usage,
trans_um,
trans_date,
completed_ind,
event_id,
posted_ind,
overrided_protected_ind,
reason_code,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
text_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
program_id,
program_application_id,
request_id,
program_update_date
)
SELECT poc_trans_id,
orgn_code,
doc_type,
doc_id,
line_type,
line_id,
resources,
resource_usage,
trans_um,
trans_date,
completed_ind,
event_id,
posted_ind,
'N' --overrided_protected_ind
,
reason_code,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
text_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
program_id,
program_application_id,
request_id,
program_update_date
FROM pc_tran_pnd
WHERE delete_mark < 100 AND
delete_mark <> 1 AND
(completed_ind = 1 OR
(completed_ind = 0 AND
resource_usage <> 0
)
); --don't migrate 0 pending
insert_message (
p_table_name => 'gme_resource_txns',
p_procedure_name => 'insert_resource_txns',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pc_tran_pnd
SET delete_mark = delete_mark + 100
WHERE delete_mark < 100 AND
delete_mark <> 1;
insert_message (
p_table_name => 'GME_RESOURCE_TXNS',
p_procedure_name => 'INSERT_RESOURCE_TXNS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_resource_txns;
PROCEDURE insert_batch_history (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_batch_history
(event_id,
batch_id,
orig_status,
new_status,
orig_wip_whse,
new_wip_whse,
gl_posted_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
request_id,
program_update_date
)
SELECT event_id,
batch_id,
orig_status,
new_status,
orig_wip_whse,
new_wip_whse,
gl_posted_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
request_id,
program_update_date
FROM pm_hist_hdr
WHERE orig_status < 100;
insert_message (
p_table_name => 'gme_batch_history',
p_procedure_name => 'insert_batch_history',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_hist_hdr
SET orig_status = orig_status + 100
WHERE orig_status < 100;
insert_message (
p_table_name => 'GME_BATCH_HISTORY',
p_procedure_name => 'INSERT_BATCH_HISTORY',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_history;
PROCEDURE insert_batch_step_transfers (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_batch_step_transfers
(wip_trans_id,
batch_id,
batchstep_no,
transfer_step_no,
line_type,
trans_qty,
trans_um,
trans_date,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
delete_mark,
text_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT wip_trans_id,
batch_id,
batchstep_no,
transfer_step_no,
line_type,
trans_qty,
trans_um,
trans_date,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
delete_mark,
text_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM pm_oprn_wip
WHERE delete_mark < 100;
insert_message (
p_table_name => 'gme_batch_step_transfers',
p_procedure_name => 'insert_batch_step_transfers',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
UPDATE pm_oprn_wip
SET delete_mark = delete_mark + 100
WHERE delete_mark < 100;
insert_message (
p_table_name => 'GME_BATCH_STEP_TRANSFERS',
p_procedure_name => 'INSERT_BATCH_STEP_TRANSFERS',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_batch_step_transfers;
PROCEDURE insert_text_header (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_text_header
(text_code,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
)
SELECT text_code,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
FROM pm_text_hdr pm
WHERE NOT EXISTS ( SELECT 1
FROM gme_text_header
WHERE text_code = pm.text_code);
insert_message (
p_table_name => 'gme_text_header/pm_text_hdr',
p_procedure_name => 'insert_text_header',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
INSERT INTO gme_text_header
(text_code,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
)
SELECT text_code,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
FROM pc_text_hdr pc
WHERE NOT EXISTS ( SELECT 1
FROM gme_text_header
WHERE text_code = pc.text_code);
insert_message (
p_table_name => 'gme_text_header/pc_text_hdr',
p_procedure_name => 'insert_text_header',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
insert_message (
p_table_name => 'GME_TEXT_HEADER',
p_procedure_name => 'INSERT_TEXT_HEADER',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_text_header;
PROCEDURE insert_text_dtl (x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO gme_text_table_tl
(text_code,
lang_code,
paragraph_code,
sub_paracode,
line_no,
text,
language,
source_lang,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
)
SELECT text_code,
lang_code,
paragraph_code,
sub_paracode,
line_no,
text,
language,
source_lang,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
FROM pm_text_tbl_tl pm
WHERE NOT EXISTS ( SELECT 1
FROM gme_text_table_tl
WHERE text_code = pm.text_code);
insert_message (
p_table_name => 'gme_text_table_tl/pm_text_tbl_tl',
p_procedure_name => 'insert_text_dtl',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
INSERT INTO gme_text_table_tl
(text_code,
lang_code,
paragraph_code,
sub_paracode,
line_no,
text,
language,
source_lang,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
)
SELECT text_code,
lang_code,
paragraph_code,
sub_paracode,
line_no,
text,
language,
source_lang,
last_updated_by,
created_by,
last_update_date,
creation_date,
last_update_login
FROM pc_text_tbl_tl pc
WHERE NOT EXISTS ( SELECT 1
FROM gme_text_table_tl
WHERE text_code = pc.text_code);
insert_message (
p_table_name => 'gme_text_table_tl/pc_text_tbl_tl',
p_procedure_name => 'insert_text_dtl',
p_parameters => 'none',
p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
p_error_type => 'P'
);
insert_message (
p_table_name => 'GME_TEXT_TABLE_TL',
p_procedure_name => 'INSERT_TEXT_DTL',
p_parameters => '',
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_text_dtl;
SELECT b.batch_id,
m.line_id
FROM pm_matl_dtl_bak m, pm_btch_hdr_bak b
WHERE m.in_use < 100 AND -- => only check for lines that were not migrated
b.batch_id = m.batch_id AND
b.batch_status IN (2, 3) AND
b.batch_type = 0
ORDER BY m.batch_id;
insert_message (
p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
p_procedure_name => 'check_wip_batches',
p_parameters => 'batch_id = '
|| l_batch_header.batch_id
|| ' line_id = '
|| l_matl.line_id,
p_message => 'Unable to load the transactions',
p_error_type => FND_API.G_RET_STS_ERROR
);
insert_message (
p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
p_procedure_name => 'check_wip_batches',
p_parameters => 'batch_id = '
|| l_batch_header.batch_id
|| ' line_id = '
|| l_matl.line_id,
p_message => 'Unable to determine the default lot.',
p_error_type => FND_API.G_RET_STS_ERROR
);
SELECT *
INTO l_tran_rec
FROM ic_tran_pnd
WHERE trans_id = l_def_lot_id;
UPDATE ic_tran_pnd
SET completed_ind = 0
WHERE trans_id = l_tran_rec.trans_id;
UPDATE ic_tran_pnd
SET completed_ind = 0
WHERE trans_id = l_tran_rec.trans_id;
SELECT gem5_trans_id_s.NEXTVAL
INTO l_trans_id
FROM sys.DUAL;
INSERT INTO ic_tran_pnd
(trans_id,
item_id,
line_id,
co_code,
orgn_code,
whse_code,
lot_id,
location,
doc_id,
doc_type,
doc_line,
line_type,
reason_code,
creation_date,
trans_date,
trans_qty,
trans_qty2,
qc_grade,
lot_status,
trans_stat,
trans_um,
trans_um2,
op_code,
completed_ind,
staged_ind,
gl_posted_ind,
event_id,
delete_mark,
text_code,
last_update_date,
created_by,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
reverse_id
)
VALUES (l_trans_id,
l_tran_rec.item_id,
l_tran_rec.line_id,
l_tran_rec.co_code,
l_tran_rec.orgn_code,
l_tran_rec.whse_code,
l_tran_rec.lot_id,
l_tran_rec.location,
l_tran_rec.doc_id,
l_tran_rec.doc_type,
l_tran_rec.doc_line,
l_tran_rec.line_type,
l_tran_rec.reason_code,
l_tran_rec.creation_date,
l_tran_rec.trans_date,
0 /* l_tran_rec.trans_qty */,
0 /* l_tran_rec.trans_qty2 */,
l_tran_rec.qc_grade,
l_tran_rec.lot_status,
l_tran_rec.trans_stat,
l_tran_rec.trans_um,
l_tran_rec.trans_um2,
l_tran_rec.op_code,
0 /* l_tran_rec.completed_ind */,
l_tran_rec.staged_ind,
l_tran_rec.gl_posted_ind,
l_tran_rec.event_id,
l_tran_rec.delete_mark,
l_tran_rec.text_code,
l_tran_rec.last_update_date,
l_tran_rec.created_by,
l_tran_rec.last_updated_by,
l_tran_rec.last_update_login,
l_tran_rec.program_application_id,
l_tran_rec.program_id,
l_tran_rec.program_update_date,
l_tran_rec.request_id,
l_tran_rec.reverse_id
);
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'split_trans_line',
p_parameters => 'none',
p_message => 'number of zero quantity transactions uncompleted = '
|| l_flip_count,
p_error_type => 'P'
);
insert_message (
p_table_name => 'ic_tran_pnd',
p_procedure_name => 'split_trans_line',
p_parameters => 'none',
p_message => 'number of zero quantity transactions inserted = '
|| l_new_txn_count,
p_error_type => 'P'
);
insert_message (
p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
p_procedure_name => 'SPLIT_TRANS_LINE',
p_parameters => 'Batch ID='
|| l_matl.batch_id
|| ' Line ID='
|| l_matl.line_id,
p_message => SQLERRM,
p_error_type => x_return_status
);
SELECT *
FROM gme_inventory_txns_gtmp
WHERE transaction_no = 2 AND
trans_qty <>
0 -- these are already matched up... don't match them again.
ORDER BY line_type,
item_id,
material_detail_id,
whse_code,
lot_id,
location,
completed_ind,
trans_id;
SELECT *
FROM gme_inventory_txns_gtmp
WHERE transaction_no <> 2 -- Should this be indexed.
ORDER BY line_type,
item_id,
material_detail_id,
whse_code,
lot_id,
location,
completed_ind,
trans_id;
SELECT *
FROM gme_inventory_txns_gtmp
WHERE completed_ind = 1 AND
trans_qty = 0
ORDER BY line_type,
item_id,
material_detail_id,
whse_code,
lot_id,
location,
completed_ind,
trans_id;
SELECT 1
FROM gme_inventory_txns_gtmp
WHERE doc_id = p_batch_id AND
doc_type = p_batch_type AND
ROWNUM = 1;
error_inserting_txn EXCEPTION;
SELECT *
INTO l_batch_row
FROM pm_btch_hdr_bak
WHERE batch_id = l_batch_id;
DELETE FROM gme_inventory_txns_gtmp;
insert_inv_txns_gtmp (
p_batch_id => l_batch_id,
p_doc_type => l_doc_type,
x_return_status => l_return_status
);
RAISE error_inserting_txn;
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE trans_id IN
(l_last_txn.trans_id, l_current_txn.trans_id);
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE ((line_type = -1 AND -- Ingredient
trans_qty > 0
) OR
(line_type <> -1 AND
trans_qty < 0
)
);
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE trans_id = match_revs.trans_id;
insert_message (
p_table_name => 'gme_inventory_txns_gtmp',
p_procedure_name => 'load_trans',
p_parameters => '',
p_message => 'Batch_id not specified for load',
p_error_type => x_return_status
);
WHEN error_inserting_txn THEN
x_return_status := l_return_status;
insert_message (
p_table_name => 'gme_inventory_txns_gtmp',
p_procedure_name => 'load_trans',
p_parameters => 'batch_id = ' || l_batch_id,
p_message => SQLERRM -- || ' with pos = ' || l_pos
,
p_error_type => x_return_status
);
PROCEDURE insert_inv_txns_gtmp (
p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
p_doc_type IN ic_tran_pnd.doc_type%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_trans_id IN ic_tran_pnd.trans_id%TYPE DEFAULT NULL
) IS
l_all_txns VARCHAR2 (100);
INSERT INTO gme_inventory_txns_gtmp
(trans_id,
item_id,
co_code,
orgn_code,
whse_code,
lot_id,
location,
doc_id,
doc_type,
doc_line,
line_type,
reason_code,
trans_date,
trans_qty,
trans_qty2,
qc_grade,
lot_status,
trans_stat,
trans_um,
trans_um2,
completed_ind,
staged_ind,
gl_posted_ind,
event_id,
delete_mark,
text_code,
action_code,
material_detail_id,
transaction_no,
organization_id,
locator_id,
subinventory,
alloc_um,
alloc_qty
)
SELECT i.trans_id,
i.item_id,
i.co_code,
i.orgn_code,
i.whse_code,
i.lot_id,
i.location,
i.doc_id,
i.doc_type,
i.doc_line,
i.line_type,
i.reason_code,
i.trans_date,
i.trans_qty,
i.trans_qty2,
i.qc_grade,
i.lot_status,
i.trans_stat,
i.trans_um,
i.trans_um2,
i.completed_ind,
i.staged_ind,
i.gl_posted_ind,
i.event_id,
i.delete_mark,
i.text_code,
'NONE',
i.line_id,
1,
0,
0,
NULL,
NULL,
NULL
FROM ic_tran_pnd i
WHERE doc_id = p_batch_id AND
doc_type = p_doc_type AND
-- retrieve only the trans_id passed or if that's NULL, all txns for the batch
(i.trans_id = p_trans_id OR
p_trans_id IS NULL
) AND
-- return only those txns that look like they may be def txns
-- in get_default_lot, if more than one of these came back, we will determine which
-- txn is really the default txn
lot_id = 0 AND
location = p_default_loct AND
delete_mark = 0;
l_all_txns := ' Note: Attempted to insert all batch txns.';
l_all_txns := ' Attempt to insert single transaction.';
insert_message (
p_table_name => 'GME_INVENTORY_TXNS_GTMP',
p_procedure_name => 'INSERT_INV_TXNS_GTMP',
p_parameters => ' Batch_Id=>'
|| p_batch_id
|| ' Doc Type=>'
|| p_doc_type
|| ' Trans ID=>'
|| p_trans_id
|| l_all_txns,
p_message => SQLERRM,
p_error_type => x_return_status
);
END insert_inv_txns_gtmp;
SELECT trans_id,
whse_code
FROM gme_inventory_txns_gtmp
WHERE doc_id = v_batch_id AND
doc_type = v_doc_type AND
material_detail_id = v_line_id AND
transaction_no <> 2 -- don't look at the reversals...
ORDER BY line_type,
item_id,
material_detail_id,
whse_code,
lot_id,
location,
completed_ind,
trans_id;
SELECT loct_ctl
FROM ic_whse_mst
WHERE whse_code = v_whse_code;
SELECT batch_no,
plant_code
FROM pm_btch_hdr_bak
WHERE batch_id = v_batch_id;
SELECT *
INTO l_matl_dtl
FROM pm_matl_dtl_bak
WHERE line_id = p_line_id;
SELECT *
INTO l_item_mst
FROM ic_item_mst
WHERE item_id = l_matl_dtl.item_id;
SELECT batch_type
INTO l_batch_type
FROM pm_btch_hdr_bak
WHERE batch_id = l_matl_dtl.batch_id;
SELECT COUNT (1)
INTO l_cnt
FROM gme_inventory_txns_gtmp
WHERE doc_id = l_matl_dtl.batch_id AND
doc_type = l_doc_type AND
material_detail_id = p_line_id AND
transaction_no <> 2 AND
trans_qty = 0;
SELECT trans_id
INTO x_def_lot_id
FROM gme_inventory_txns_gtmp
WHERE doc_id = l_matl_dtl.batch_id AND
doc_type = l_doc_type AND
material_detail_id = p_line_id AND
transaction_no <> 2 AND
trans_qty = 0;
SELECT *
INTO l_ic_tran_pnd
FROM ic_tran_pnd
WHERE trans_id = get_rec.trans_id;
insert_message (
p_table_name => 'gme_inventory_txns_gtmp',
p_procedure_name => 'get_default_lot',
p_parameters => 'line_id= ' || p_line_id,
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => x_return_status
);
SELECT whse_code
FROM ps_whse_eff
WHERE plant_code = p_orgn_code AND
(whse_item_id IS NULL OR whse_item_id = p_item_id) AND
((p_line_type > 0 AND replen_ind = 1) OR
(p_line_type < 0 AND consum_ind = 1)
)
ORDER BY whse_item_id, whse_code;
SELECT batch_id, line_type, line_no
FROM pm_matl_dtl_bak
GROUP BY batch_id, line_type, line_no
HAVING COUNT(1) > 1;
SELECT line_id
FROM pm_matl_dtl_bak
WHERE batch_id = v_batch_id
AND line_type = v_line_type
ORDER BY line_no asc;
insert_message (
p_table_name => 'pm_matl_dtl',
p_procedure_name => 'renumber_duplicate_line_no',
p_parameters => 'batch_id = '||l_dup_rec.batch_id||
' line_type = '||l_dup_rec.line_type||
' line_no = '||l_dup_rec.line_no,
p_message => 'Found batch with duplicate batch_id, line_type, line_no',
p_error_type => 'I'
);
UPDATE pm_matl_dtl_bak
SET line_no = l_line_no
WHERE line_id = rec.line_id;
insert_message (
p_table_name => 'pm_matl_dtl',
p_procedure_name => 'renumber_duplicate_line_no',
p_parameters => 'none',
p_message => 'Number of batches to renumber for duplicate batch_id/line_type/line_no = ' || l_dup_no,
p_error_type => 'P'
);
insert_message (
p_table_name => 'pm_matl_dtl',
p_procedure_name => 'renumber_duplicate_line_no',
p_parameters => 'none',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);
SELECT d.batch_id, line_type, max(line_no) max_line_no,
count(1) line_count
FROM gme_material_details d, gme_batch_header b
WHERE d.batch_id=b.batch_id
AND batch_status in (1,2,3)
AND batch_type = 0
GROUP BY d.batch_id, line_type
HAVING max(line_no) <> count(1);
SELECT material_detail_id
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = v_line_type
ORDER BY line_no asc;
insert_message (
p_table_name => 'gme_material_details',
p_procedure_name => 'renumber_blank_line_no',
p_parameters => 'batch_id = '||l_get_bl_batches.batch_id||
' line_type = '||l_get_bl_batches.line_type||
' max_line_no = '||
l_get_bl_batches.max_line_no ||
' line_count = '||l_get_bl_batches.line_count,
p_message => 'Found batch that required renumbering',
p_error_type => 'I'
);
UPDATE gme_material_details
SET line_no = l_line_no
WHERE material_detail_id = rec.material_detail_id;
insert_message (
p_table_name => 'gme_material_details',
p_procedure_name => 'renumber_blank_line_no',
p_parameters => 'none',
p_message => 'Number of batches to renumber for blank line_no = ' || l_dup_no,
p_error_type => 'P'
);
insert_message (
p_table_name => 'gme_material_details',
p_procedure_name => 'renumber_blank_line_no',
p_parameters => 'none',
p_message => SQLERRM || ' with pos = ' || l_pos,
p_error_type => 'D'
);