[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_spec_test_rec (
p_spec_test_rec IN gmd_spec_tests_b%ROWTYPE
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE insert_new_spec_rec (
p_spec_rec IN gmd_specifications%ROWTYPE
, x_new_spec_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE update_validity_rule (
p_vr_id IN NUMBER
, p_spec_type IN VARCHAR2
, p_end_date IN DATE
, p_start_date IN DATE
, p_new_status IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
);
l_object_select_ind NUMBER;
DELETE FROM gmd_msnr_results
WHERE concurrent_id IS NULL;
DBMS_SQL.define_column (l_cursor_id, 6, l_object_select_ind);
DBMS_SQL.column_value (l_cursor_id, 6, l_object_select_ind);
l_object_select_ind := 0;
x_search_tbl (l_row_cnt).object_select_ind := l_object_select_ind;
INSERT INTO gmd_msnr_results
(concurrent_id
, object_id
, object_name
, object_vers
, object_desc
, object_status_code
, object_status_desc
, object_select_ind
)
VALUES (NULL
, l_object_id
, l_object_name
, l_object_vers
, l_object_desc
, l_object_status_code
, l_object_status_desc
, l_object_select_ind
);
/* l_select variable and also modified the IF condition that verifies the */
/* range of test values to consider NULL target values. */
/* ************************************************************************ */
PROCEDURE mass_replace_oper_spec_val (
err_buf OUT NOCOPY VARCHAR2
, ret_code OUT NOCOPY VARCHAR2
, pconcurrent_id IN VARCHAR2 DEFAULT NULL
, pobject_type IN VARCHAR2
, preplace_type IN VARCHAR2
, pold_name IN VARCHAR2
, pnew_name IN VARCHAR2
, poptional_ind IN VARCHAR2 DEFAULT NULL
, pprint_spec_ind IN VARCHAR2 DEFAULT NULL
, pprint_result_ind IN VARCHAR2 DEFAULT NULL
, ptarget_value IN VARCHAR2 DEFAULT NULL
, ptarget_min IN VARCHAR2 DEFAULT NULL
, ptarget_max IN VARCHAR2 DEFAULT NULL
, preport_precision IN VARCHAR2 DEFAULT NULL
, pstore_precision IN VARCHAR2 DEFAULT NULL
, ptest_priority IN VARCHAR2 DEFAULT NULL
, pcreate_vers IN VARCHAR2 DEFAULT 'N'
) IS
l_api_name VARCHAR2 (100) := 'MASS_REPLACE_OPER_SPEC_VAL';
l_select VARCHAR2 (1000);
NO_UPDATE_EXCEPTION EXCEPTION;
SELECT UPPER (pobject_type) object_type -- e.g 'SPEC_TEST' etc
, UPPER (preplace_type) replace_type -- e.g 'TEST_VALUE'
, pold_name old_name -- e.g 'SHY-SPEC-TEST'
, pnew_name new_name -- e.g 'TDAN-SPEC-TEST'
, preport_precision report_preci -- defaults to null
, pstore_precision store_precis -- defaults to null
, object_id -- e.g formula_id = 100
, object_name -- e.g formula_no = 'SHY-TEST'
, object_vers -- e.g formula_vers = 2
, object_desc
, object_status_code -- e.g formula_status = '100'
, concurrent_id
FROM gmd_msnr_results
WHERE object_select_ind = 1
AND concurrent_id = TO_NUMBER (pconcurrent_id);
SELECT *
FROM gmd_qc_tests_b
WHERE test_code = p_test_name;
SELECT *
FROM gmd_qc_tests_b
WHERE test_id = p_test_id;
SELECT *
FROM gmd_specifications_b
WHERE spec_id = p_spec_id;
SELECT 1
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id;
SELECT 1
FROM gmd_spec_tests_b sptst
, gmd_qc_tests_b tst
WHERE sptst.spec_id = p_spec_id
AND sptst.test_id = tst.test_id
AND tst.test_code = p_test_name;
SELECT *
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id;
SELECT msi.organization_id, msi.inventory_item_id, msi.revision_qty_control_code, s.revision
FROM mtl_system_items msi, gmd_specifications_b s
WHERE msi.organization_id = p_org_id
AND msi.inventory_item_id = s.inventory_item_id
AND s.spec_id = p_spec_id
AND msi.process_quality_enabled_flag = 'Y';
SELECT count(*)
FROM mtl_item_revisions
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND revision = p_revision;
SELECT meaning
INTO l_string
FROM gem_lookups
WHERE lookup_type = 'GMD_QM_REPLACE_OPTIONS'
AND lookup_code = preplace_type;
l_select := 'select spts.* '; --Bug#5973270. Included spts.
l_query := l_select || l_from || l_where;
SELECT spec_version_control_ind
INTO l_state
FROM gmd_quality_config
WHERE organization_id = (SELECT owner_organization_id
FROM gmd_specifications_b
WHERE spec_id = get_object_rec.object_id);
UPDATE gmd_specifications_b
SET spec_status = l_new_status
WHERE spec_id = get_object_rec.object_id;
default_log (' Update Specification Succesful');
raise no_update_exception;
RAISE NO_UPDATE_EXCEPTION;
/*Select user_id
Into l_new_owner_id
From fnd_user
Where user_name = l_new_Owner;
UPDATE gmd_specifications_b
SET owner_id = l_new_owner
WHERE spec_id = get_object_rec.object_id;
default_log (' Update Specification Succesful');
raise no_update_exception;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
UPDATE gmd_specifications_b
SET owner_organization_id = l_new_ownerorg
WHERE spec_id = get_object_rec.object_id;
default_log (' Update Specification Succesful');
raise no_update_exception;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
DEBUG (' update gmd_spec_tests_b, spec_id ' || l_spec_test_rec.spec_id);
DEBUG (' update gmd_spec_tests_b, test_id ' || l_spec_test_rec.test_id);
UPDATE gmd_spec_tests_b
SET optional_ind = l_test_values_rec.optional_ind
, print_spec_ind = l_test_values_rec.print_spec_ind
, print_result_ind = l_test_values_rec.print_result_ind
, target_value_num = l_test_values_rec.target_value_num
, target_value_char = l_test_values_rec.target_value_char
, min_value_char = l_test_values_rec.min_value_char
, max_value_char = l_test_values_rec.max_value_char
, min_value_num = l_test_values_rec.min_value_num
, max_value_num = l_test_values_rec.max_value_num
, report_precision = l_test_values_rec.report_precision
, display_precision = l_test_values_rec.store_precision
, test_priority = l_test_values_rec.test_priority
WHERE spec_id = l_spec_test_rec.spec_id
AND test_id = l_spec_test_rec.test_id;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
IF l_gmd_test_rec.test_id <> TO_NUMBER (pnew_name) THEN /* do not insert the one to be deleted*/
default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
, p_from_test_id => l_gmd_test_rec.test_id
, p_to_test_name => l_gmd_test_rec.test_code
, poptional_ind => NULL
, pprint_spec_ind => NULL
, pprint_result_ind => NULL
, ptarget_value => NULL
, ptarget_min => NULL
, ptarget_max => NULL
, preport_precision => NULL
, pstore_precision => NULL
, ptest_priority => NULL
, p_spec_test_rec => l_spec_test_rec
, x_return_status => l_return_status
);
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
/* old test is deleted and new ones are created */
IF l_new_spec = 'N' THEN
l_new_spec_id := get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
/* do not insert the one to be replaced*/
default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
, p_from_test_id => l_gmd_test_rec.test_id
, p_to_test_name => l_gmd_test_rec.test_code
, poptional_ind => NULL
, pprint_spec_ind => NULL
, pprint_result_ind => NULL
, ptarget_value => NULL
, ptarget_min => NULL
, ptarget_max => NULL
, preport_precision => NULL
, pstore_precision => NULL
, ptest_priority => NULL
, p_spec_test_rec => l_spec_test_rec
, x_return_status => l_return_status
);
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
SELECT seq INTO l_seq
FROM gmd_spec_tests_b
WHERE spec_id = get_object_rec.object_id
AND test_id = TO_NUMBER (pold_name);
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
* Update the validity rule for the spec with the new values
*/
l_start_date := NULL;
RAISE NO_UPDATE_EXCEPTION;
WHEN NO_UPDATE_EXCEPTION THEN
IF pobject_type = 'SPECIFICATION' THEN
default_log ( 'No Replacement for Spec Name: '
|| get_object_rec.object_name
|| ', Version: '
|| get_object_rec.object_vers);
DELETE FROM gmd_msnr_results
WHERE concurrent_id = TO_NUMBER (pconcurrent_id);
fnd_message.set_name ('GMD', 'GMD_CONC_UPDATE_OBJECT_FAILED');
SELECT *
FROM gmd_qc_tests_b
WHERE test_code = p_test_name;
SELECT value_char
FROM gmd_qc_test_values
WHERE test_id = p_test_id
AND text_range_seq = p_num_value;
select VALUE_CHAR from gmd_qc_test_values
where test_id = l_gmd_test_rec.test_id;
select text_range_seq, VALUE_CHAR from gmd_qc_test_values
where test_id = l_gmd_test_rec.test_id;
SELECT MAX (seq) + 10
INTO l_seq
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id;
l_spec_test_rec.last_update_date := NULL;
l_spec_test_rec.last_updated_by := NULL;
l_spec_test_rec.last_update_login := NULL;
SELECT *
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id;
SELECT *
FROM gmd_qc_tests_b
WHERE test_code = p_test_name;
SELECT value_char
FROM gmd_qc_test_values
WHERE test_id = p_test_id
AND text_range_seq = p_num_value;
SELECT *
FROM gmd_specifications
WHERE spec_id = p_spec_id;
SELECT default_specification_status
, manage_validity_rules_ind
FROM gmd_quality_config
WHERE organization_id = p_orgn_id;
SELECT spec_vr_status
FROM gmd_all_spec_vrs_vl
WHERE spec_id = p_spec_id;
SELECT MAX (spec_vers) + 1
INTO l_new_spec_rec.spec_vers
FROM gmd_specifications
WHERE spec_name = l_old_spec_rec.spec_name;
insert_new_spec_rec (p_spec_rec => l_new_spec_rec
, x_new_spec_id => x_new_spec_id
, x_return_status => x_return_status
);
PROCEDURE insert_new_spec_rec (
p_spec_rec IN gmd_specifications%ROWTYPE
, x_new_spec_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_new_spec_rec gmd_specifications%ROWTYPE;
DEBUG ('Inserting new version of the spec: ' || l_new_spec_rec.spec_name);
DEBUG ('Inserting new version of the spec ver: ' || l_new_spec_rec.spec_vers);
GMD_SPECIFICATIONS_PVT.INSERT_ROW
( x_rowid => l_row_id
, x_spec_id => l_spec_id
, x_spec_name => l_new_spec_rec.spec_name
, x_spec_vers => l_new_spec_rec.spec_vers
, x_inventory_item_id => l_new_spec_rec.inventory_item_id /* Bug# 5882074 */
, x_revision => l_new_spec_rec.revision /* Bug# 5882074 */
, x_grade_code => l_new_spec_rec.grade_code /* Bug# 5882074 */
, x_spec_status => l_new_spec_rec.spec_status
, x_overlay_ind => l_new_spec_rec.overlay_ind
, x_spec_type => l_new_spec_rec.spec_type
, x_base_spec_id => l_new_spec_rec.base_spec_id
, x_owner_organization_id => l_new_spec_rec.owner_organization_id /* Bug# 5882074 */
, x_owner_id => l_new_spec_rec.owner_id
, x_sample_inv_trans_ind => l_new_spec_rec.sample_inv_trans_ind
, x_delete_mark => l_new_spec_rec.delete_mark
, x_text_code => l_new_spec_rec.text_code
, x_attribute_category => l_new_spec_rec.attribute_category
, x_attribute1 => l_new_spec_rec.attribute1
, x_attribute2 => l_new_spec_rec.attribute2
, x_attribute3 => l_new_spec_rec.attribute3
, x_attribute4 => l_new_spec_rec.attribute4
, x_attribute5 => l_new_spec_rec.attribute5
, x_attribute6 => l_new_spec_rec.attribute6
, x_attribute7 => l_new_spec_rec.attribute7
, x_attribute8 => l_new_spec_rec.attribute8
, x_attribute9 => l_new_spec_rec.attribute9
, x_attribute10 => l_new_spec_rec.attribute10
, x_attribute11 => l_new_spec_rec.attribute11
, x_attribute12 => l_new_spec_rec.attribute12
, x_attribute13 => l_new_spec_rec.attribute13
, x_attribute14 => l_new_spec_rec.attribute14
, x_attribute15 => l_new_spec_rec.attribute15
, x_attribute16 => l_new_spec_rec.attribute16
, x_attribute17 => l_new_spec_rec.attribute17
, x_attribute18 => l_new_spec_rec.attribute18
, x_attribute19 => l_new_spec_rec.attribute19
, x_attribute20 => l_new_spec_rec.attribute20
, x_attribute21 => l_new_spec_rec.attribute21
, x_attribute22 => l_new_spec_rec.attribute22
, x_attribute23 => l_new_spec_rec.attribute23
, x_attribute24 => l_new_spec_rec.attribute24
, x_attribute25 => l_new_spec_rec.attribute25
, x_attribute26 => l_new_spec_rec.attribute26
, x_attribute27 => l_new_spec_rec.attribute27
, x_attribute28 => l_new_spec_rec.attribute28
, x_attribute29 => l_new_spec_rec.attribute29
, x_attribute30 => l_new_spec_rec.attribute30
, x_spec_desc => l_new_spec_rec.spec_desc
, x_creation_date => l_new_spec_rec.creation_date
, x_created_by => l_new_spec_rec.created_by
, x_last_update_date => l_new_spec_rec.last_update_date
, x_last_updated_by => l_new_spec_rec.last_updated_by
, x_last_update_login => l_new_spec_rec.last_update_login
);
default_log ('Insert New spec rec result in Error');
END insert_new_spec_rec;
SELECT *
FROM gmd_qc_tests_b
WHERE test_code = p_test_name;
SELECT 1
FROM gmd_spec_tests_b sptst
, gmd_qc_tests_b tst
WHERE sptst.spec_id = p_spec_id
AND sptst.test_id = tst.test_id
AND tst.test_code = p_test_name;
SELECT t.test_code
, t.test_id
, t.expression
FROM gmd_spec_tests_b s
, gmd_qc_tests_b t
WHERE s.spec_id = p_spec_id
AND s.test_id = t.test_id
AND t.test_type <> 'E';
DEBUG ('Inserting new optional_ind: ' || l_spec_test_rec.optional_ind);
insert_spec_test_rec (p_spec_test_rec => l_spec_test_rec, x_return_status => x_return_status);
/* delete spec_test record for the test with p_test_name to spec_id
*/
PROCEDURE del_spec_test_rec (
p_spec_id IN NUMBER
, p_spec_name IN VARCHAR2
, p_test_name IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
SELECT *
FROM gmd_qc_tests_b
WHERE test_id = TO_NUMBER (p_test_name);
SELECT 1
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id;
SELECT t.test_code
, t.test_id
, t.expression
FROM gmd_spec_tests_b s
, gmd_qc_tests_b t
WHERE s.spec_id = p_spec_id
AND s.test_id = t.test_id
AND t.test_type = 'E';
DELETE gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = l_gmd_test_rec.test_id;
default_log ('Delete spec test error ' || SQLERRM);
default_log ('Delete spec test error ' || SQLERRM);
PROCEDURE insert_spec_test_rec (
p_spec_test_rec IN gmd_spec_tests_b%ROWTYPE
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_spec_test_rec gmd_spec_tests_b%ROWTYPE;
DEBUG ('Inserting spec test rec ');
GMD_SPEC_TESTS_PVT.INSERT_ROW (
x_rowid => l_row_id
, x_spec_id => l_spec_test_rec.spec_id
, x_test_id => l_spec_test_rec.test_id
, x_attribute1 => l_spec_test_rec.attribute1
, x_attribute2 => l_spec_test_rec.attribute2
, x_min_value_char => l_spec_test_rec.min_value_char
, x_test_method_id => l_spec_test_rec.test_method_id
, x_seq => l_spec_test_rec.seq
, x_from_base_ind => l_spec_test_rec.from_base_ind
, x_exclude_ind => l_spec_test_rec.exclude_ind
, x_modified_ind => l_spec_test_rec.modified_ind
, x_test_qty => l_spec_test_rec.test_qty
, x_test_qty_uom => l_spec_test_rec.test_qty_uom /* Bug# 5882074 */
, x_min_value_num => l_spec_test_rec.min_value_num
, x_target_value_num => l_spec_test_rec.target_value_num
, x_max_value_num => l_spec_test_rec.max_value_num
, x_attribute5 => l_spec_test_rec.attribute5
, x_attribute6 => l_spec_test_rec.attribute6
, x_attribute7 => l_spec_test_rec.attribute7
, x_attribute8 => l_spec_test_rec.attribute8
, x_attribute9 => l_spec_test_rec.attribute9
, x_attribute10 => l_spec_test_rec.attribute10
, x_attribute11 => l_spec_test_rec.attribute11
, x_attribute12 => l_spec_test_rec.attribute12
, x_attribute13 => l_spec_test_rec.attribute13
, x_attribute14 => l_spec_test_rec.attribute14
, x_attribute15 => l_spec_test_rec.attribute15
, x_attribute16 => l_spec_test_rec.attribute16
, x_attribute17 => l_spec_test_rec.attribute17
, x_attribute18 => l_spec_test_rec.attribute18
, x_use_to_control_step => l_spec_test_rec.use_to_control_step
, x_print_spec_ind => l_spec_test_rec.print_spec_ind
, x_print_result_ind => l_spec_test_rec.print_result_ind
, x_text_code => l_spec_test_rec.text_code
, x_attribute_category => l_spec_test_rec.attribute_category
, x_attribute3 => l_spec_test_rec.attribute3
, x_retest_lot_expiry_ind => l_spec_test_rec.retest_lot_expiry_ind
, x_attribute19 => l_spec_test_rec.attribute19
, x_attribute20 => l_spec_test_rec.attribute20
, x_max_value_char => l_spec_test_rec.max_value_char
, x_test_replicate => l_spec_test_rec.test_replicate
, x_check_result_interval => l_spec_test_rec.check_result_interval
, x_out_of_spec_action => l_spec_test_rec.out_of_spec_action
, x_exp_error_type => l_spec_test_rec.exp_error_type
, x_below_spec_min => l_spec_test_rec.below_spec_min
, x_above_spec_min => l_spec_test_rec.above_spec_min
, x_below_spec_max => l_spec_test_rec.below_spec_max
, x_above_spec_max => l_spec_test_rec.above_spec_max
, x_below_min_action_code => l_spec_test_rec.below_min_action_code
, x_above_min_action_code => l_spec_test_rec.above_min_action_code
, x_below_max_action_code => l_spec_test_rec.below_max_action_code
, x_above_max_action_code => l_spec_test_rec.above_max_action_code
, x_optional_ind => l_spec_test_rec.optional_ind
, x_display_precision => l_spec_test_rec.display_precision
, x_report_precision => l_spec_test_rec.report_precision
, x_test_priority => l_spec_test_rec.test_priority
, x_print_on_coa_ind => l_spec_test_rec.print_on_coa_ind
, x_target_value_char => l_spec_test_rec.target_value_char
, x_attribute4 => l_spec_test_rec.attribute4
, x_attribute21 => l_spec_test_rec.attribute21
, x_attribute22 => l_spec_test_rec.attribute22
, x_attribute23 => l_spec_test_rec.attribute23
, x_attribute24 => l_spec_test_rec.attribute24
, x_attribute25 => l_spec_test_rec.attribute25
, x_attribute26 => l_spec_test_rec.attribute26
, x_attribute27 => l_spec_test_rec.attribute27
, x_attribute28 => l_spec_test_rec.attribute28
, x_attribute29 => l_spec_test_rec.attribute29
, x_attribute30 => l_spec_test_rec.attribute30
, x_test_display => NULL --l_to_spec_test_rec.test_display
, x_creation_date => NULL
, x_created_by => NULL
, x_last_update_date => NULL
, x_last_updated_by => NULL
, x_last_update_login => NULL
, x_viability_duration => l_spec_test_rec.viability_duration
, x_test_expiration_days => l_spec_test_rec.days
, x_test_expiration_hours => l_spec_test_rec.hours
, x_test_expiration_minutes => l_spec_test_rec.minutes
, x_test_expiration_seconds => l_spec_test_rec.seconds
, x_calc_uom_conv_ind => l_spec_test_rec.calc_uom_conv_ind
, x_to_qty_uom => l_spec_test_rec.to_qty_uom /* Bug# 5882074 */
);
END insert_spec_test_rec;
SELECT manage_validity_rules_ind
, default_specification_status
FROM gmd_quality_config
WHERE organization_id = p_orgn_id;
SELECT *
FROM gmd_specifications_b
WHERE spec_id = p_spec_id;
SELECT spec_vr_id
, spec_type
, spec_vr_status
FROM gmd_all_spec_vrs_vl
WHERE spec_id = p_spec_id
AND spec_name = p_spec_name
AND owner_organization_id = p_orgn_id;
SELECT spec_type
INTO l_spec_type
FROM gmd_all_spec_vrs_vl
WHERE spec_vr_id = l_vr_id;
update_validity_rule (p_vr_id => l_vr_id
, p_spec_type => l_spec_type
, p_end_date => p_end_date
, p_start_date => p_start_date
, p_new_status => p_new_status
, x_return_status => x_return_status
);
SELECT *
INTO l_cust_spec_vrs_in
FROM gmd_customer_spec_vrs
WHERE spec_vr_id = l_spec_vr_id;
l_return := gmd_customer_spec_vrs_pvt.insert_row
( p_customer_spec_vrs => l_cust_spec_vrs_in
, x_customer_spec_vrs => l_cust_spec_vrs_out );
SELECT *
INTO l_inv_spec_vrs_in
FROM gmd_inventory_spec_vrs
WHERE spec_vr_id = l_spec_vr_id;
l_return := gmd_inventory_spec_vrs_pvt.insert_row
( p_inventory_spec_vrs => l_inv_spec_vrs_in
, x_inventory_spec_vrs => l_inv_spec_vrs_out );
SELECT *
INTO l_wip_spec_vrs_in
FROM gmd_wip_spec_vrs
WHERE spec_vr_id = l_spec_vr_id;
l_return := gmd_wip_spec_vrs_pvt.insert_row
( p_wip_spec_vrs => l_wip_spec_vrs_in
, x_wip_spec_vrs => l_wip_spec_vrs_out );
SELECT *
INTO l_supplier_spec_vrs_in
FROM gmd_supplier_spec_vrs
WHERE spec_vr_id = l_spec_vr_id;
l_return := gmd_supplier_spec_vrs_pvt.insert_row
( p_supplier_spec_vrs => l_supplier_spec_vrs_in
, x_supplier_spec_vrs => l_supplier_spec_vrs_out);
UPDATE gmd_customer_spec_vrs
SET end_date = SYSDATE
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_inventory_spec_vrs
SET end_date = SYSDATE
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_wip_spec_vrs
SET end_date = SYSDATE
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_supplier_spec_vrs
SET end_date = SYSDATE
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_customer_spec_vrs
SET spec_vr_status = 1000
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_inventory_spec_vrs
SET spec_vr_status = 1000
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_wip_spec_vrs
SET spec_vr_status = 1000
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_supplier_spec_vrs
SET spec_vr_status = 1000
WHERE spec_vr_id = l_spec_vr_id;
PROCEDURE update_validity_rule (
p_vr_id IN NUMBER
, p_spec_type IN VARCHAR2
, p_end_date IN DATE
, p_start_date IN DATE
, p_new_status IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_cust_spec_vrs gmd_customer_spec_vrs%ROWTYPE;
SELECT spec_status
FROM gmd_all_spec_vrs_vl
WHERE spec_vr_id = p_vr_id;
DEBUG ('In Update_validity_rule, validity_rule_id ' || l_spec_vr_id);
DEBUG ('In Update_validity_rule, spec_type ' || l_spec_type);
DEBUG ('In Update_validity_rule, end_date ' || p_end_date);
DEBUG ('In Update_validity_rule, start_date ' || p_start_date);
DEBUG ('In Update_validity_rule, new_status ' || p_new_status);
UPDATE gmd_customer_spec_vrs
SET spec_vr_status = p_new_status
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_customer_spec_vrs
SET start_date = p_start_date
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_customer_spec_vrs
SET end_date = p_end_date
WHERE spec_vr_id = l_spec_vr_id;
default_log (' Updated Customer Validity Rule: ' || l_spec_vr_id);
UPDATE gmd_inventory_spec_vrs
SET spec_vr_status = p_new_status
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_inventory_spec_vrs
SET start_date = p_start_date
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_inventory_spec_vrs
SET end_date = p_end_date
WHERE spec_vr_id = l_spec_vr_id;
default_log (' Updated Inventory Validity Rule: ' || l_spec_vr_id);
UPDATE gmd_wip_spec_vrs
SET spec_vr_status = p_new_status
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_wip_spec_vrs
SET start_date = p_start_date
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_wip_spec_vrs
SET end_date = p_end_date
WHERE spec_vr_id = l_spec_vr_id;
default_log (' Updated WIP Validity Rule: ' || l_spec_vr_id);
UPDATE gmd_supplier_spec_vrs
SET spec_vr_status = p_new_status
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_supplier_spec_vrs
SET start_date = p_start_date
WHERE spec_vr_id = l_spec_vr_id;
UPDATE gmd_supplier_spec_vrs
SET end_date = p_end_date
WHERE spec_vr_id = l_spec_vr_id;
default_log (' Updated Supplier Validity Rule: ' || l_spec_vr_id);
default_log ('Update validity for spec results in Error');
default_log ('Update validity for spec results in Error');
END update_validity_rule;