Procedure to create Quality Spec & Quality Spec Values in OPM through API

IS

CURSOR c_specs

IS

SELECT *

FROM xx_gmd_spec_tests_stg

WHERE process_flag = ‘N’;

CURSOR c_test_lines (p_spec_name VARCHAR2)

IS

SELECT *

FROM xx_gmd_spec_test_values_stg

WHERE spec_name = p_spec_name AND process_flag = ‘N’;

l_spec                 gmd_specifications%ROWTYPE;

l_spec_out             gmd_specifications%ROWTYPE;

l_spec_tests_tbl       gmd_spec_pub.spec_tests_tbl;

l_spec_tests_tbl_out   gmd_spec_pub.spec_tests_tbl;

l_return_status        VARCHAR2 (1);

l_msg_count            NUMBER;

l_msg_data             VARCHAR2 (2000);

l_msg_dummy            VARCHAR2 (2000);

l_idx                  PLS_INTEGER;

v_test_id              NUMBER;

v_inventory_item_id    NUMBER;

v_organization_id      NUMBER;

l_error_count          NUMBER;

v_test_method_id       NUMBER;

v_user_id              NUMBER;

BEGIN                                                        — OPM Quality

FOR spec_rec IN c_specs

LOOP

BEGIN

l_spec_tests_tbl.DELETE;

l_idx := 0;

— Build spec header

l_spec := NULL;

v_inventory_item_id := 0;

v_organization_id := 0;

l_error_count := 0;

v_user_id := 0;

l_msg_data      :=       null;

l_msg_dummy         :=  null;

BEGIN

SELECT msib.inventory_item_id, msib.organization_id

INTO v_inventory_item_id, v_organization_id

FROM mtl_system_items_b msib,

org_organization_definitions ood

WHERE msib.organization_id = ood.organization_id

AND msib.segment1 = spec_rec.item_no

AND ood.organization_code = spec_rec.owner_organization;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘E’,

error_message =

‘Item not defined at organization level’

WHERE staging_id = spec_rec.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘E’,

error_message =

‘Item not defined at organization level’

WHERE staging_id = spec_rec.staging_id;

END;

BEGIN

— Validation for given test method exists and should be active

SELECT user_id

INTO v_user_id

FROM fnd_user

WHERE user_name = spec_rec.owner_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification Owner name not available in the system’

WHERE staging_id = spec_rec.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification Owner name not available in the system’

WHERE staging_id = spec_rec.staging_id;

END;

FOR test_rec IN c_test_lines (spec_rec.spec_name)

LOOP

l_idx := l_idx + 1;

v_test_id := 0;

v_test_method_id := 0;

BEGIN

SELECT test_id

INTO v_test_id

FROM gmd_qc_tests

WHERE test_code = test_rec.test_code;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘E’,

error_message = error_message ||’ ‘||’Test Code’||’ ‘||test_rec.test_code|| ‘is not defined’

WHERE staging_id = test_rec.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘E’,

error_message = error_message|| ‘ ‘||’Test Code’||’ ‘||test_rec.test_code|| ‘is not defined’

WHERE staging_id = test_rec.staging_id;

END;

BEGIN

— Validation for given test method exists and should be active

SELECT test_method_id

INTO v_test_method_id

FROM gmd_test_methods

WHERE 1 = 1

— AND delete_mark <> 1

AND test_method_code = test_rec.test_method;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Quality test method is not defined in the system’

WHERE staging_id = test_rec.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Quality test method is not defined in the system’

WHERE staging_id = test_rec.staging_id;

END;

l_spec_tests_tbl (l_idx).test_id := v_test_id;

l_spec_tests_tbl (l_idx).spec_id := l_spec_out.spec_id;

–test_rec.spec_name;

l_spec_tests_tbl (l_idx).test_method_id := v_test_method_id;

l_spec_tests_tbl (l_idx).seq := test_rec.seq;

l_spec_tests_tbl (l_idx).test_qty := test_rec.test_qty;

l_spec_tests_tbl (l_idx).test_qty_uom := test_rec.test_qty_uom;

l_spec_tests_tbl (l_idx).min_value_num :=

test_rec.min_value_num;

l_spec_tests_tbl (l_idx).target_value_num :=

test_rec.target_value_num;

l_spec_tests_tbl (l_idx).max_value_num :=

test_rec.max_value_num;

l_spec_tests_tbl (l_idx).min_value_char :=

test_rec.min_value_char;

l_spec_tests_tbl (l_idx).target_value_char :=

test_rec.target_value_char;

l_spec_tests_tbl (l_idx).max_value_char :=

test_rec.max_value_char;

l_spec_tests_tbl (l_idx).test_replicate :=

test_rec.test_replicate;

l_spec_tests_tbl (l_idx).check_result_interval :=

test_rec.check_result_interval;

l_spec_tests_tbl (l_idx).print_on_coa_ind :=

test_rec.print_on_coa_ind;

l_spec_tests_tbl (l_idx).use_to_control_step :=

test_rec.use_to_control_step;

l_spec_tests_tbl (l_idx).out_of_spec_action :=

test_rec.out_of_spec_action;

l_spec_tests_tbl (l_idx).exp_error_type :=

test_rec.exp_error_type;

l_spec_tests_tbl (l_idx).below_spec_min :=

test_rec.below_spec_min;

l_spec_tests_tbl (l_idx).above_spec_min :=

test_rec.above_spec_min;

l_spec_tests_tbl (l_idx).below_spec_max :=

test_rec.below_spec_max;

l_spec_tests_tbl (l_idx).above_spec_max :=

test_rec.above_spec_max;

l_spec_tests_tbl (l_idx).below_min_action_code :=

test_rec.below_min_action_code;

l_spec_tests_tbl (l_idx).above_min_action_code :=

test_rec.above_min_action_code;

l_spec_tests_tbl (l_idx).optional_ind := test_rec.optional_ind;

l_spec_tests_tbl (l_idx).display_precision :=

test_rec.display_precision;

l_spec_tests_tbl (l_idx).report_precision :=

test_rec.report_precision;

l_spec_tests_tbl (l_idx).test_priority :=

test_rec.test_priority;

l_spec_tests_tbl (l_idx).retest_lot_expiry_ind :=

test_rec.retest_lot_expiry_ind;

l_spec_tests_tbl (l_idx).print_spec_ind :=

test_rec.print_spec_ind;

l_spec_tests_tbl (l_idx).print_result_ind :=

test_rec.print_result_ind;

l_spec_tests_tbl (l_idx).below_max_action_code :=

test_rec.below_max_action_code;

l_spec_tests_tbl (l_idx).above_max_action_code :=

test_rec.above_max_action_code;

l_spec_tests_tbl (l_idx).test_display := test_rec.test_display;

l_spec_tests_tbl (l_idx).text_code := test_rec.text_code;

l_spec_tests_tbl (l_idx).viability_duration :=

test_rec.viability_duration;

l_spec_tests_tbl (l_idx).days := test_rec.days;

l_spec_tests_tbl (l_idx).hours := test_rec.hours;

l_spec_tests_tbl (l_idx).minutes := test_rec.minutes;

l_spec_tests_tbl (l_idx).seconds := test_rec.seconds;

l_spec_tests_tbl (l_idx).attribute_category :=

test_rec.attribute_category;

l_spec_tests_tbl (l_idx).attribute1 := test_rec.attribute1;

l_spec_tests_tbl (l_idx).attribute2 := test_rec.attribute2;

l_spec_tests_tbl (l_idx).attribute3 := test_rec.attribute3;

l_spec_tests_tbl (l_idx).attribute4 := test_rec.attribute4;

l_spec_tests_tbl (l_idx).from_base_ind :=

test_rec.from_base_ind;

l_spec_tests_tbl (l_idx).exclude_ind := test_rec.exclude_ind;

l_spec_tests_tbl (l_idx).modified_ind := test_rec.modified_ind;

l_spec_tests_tbl (l_idx).calc_uom_conv_ind :=

test_rec.calc_uom_conv_ind;

l_spec_tests_tbl (l_idx).to_qty_uom := test_rec.to_qty_uom;

l_spec_tests_tbl (l_idx).test_kit_qty := test_rec.test_kit_qty;

l_spec_tests_tbl (l_idx).share_test_kit :=

test_rec.share_test_kit;

l_spec_tests_tbl (l_idx).aql_group_name :=

test_rec.aql_group_name;

END LOOP;

IF l_error_count = 0

THEN

l_spec := NULL;

l_spec.spec_name := spec_rec.spec_name;

l_spec.spec_vers := spec_rec.spec_vers;

l_spec.spec_desc := spec_rec.spec_desc;

l_spec.inventory_item_id := v_inventory_item_id;

l_spec.grade_code := spec_rec.grade_code;

l_spec.owner_organization_id := v_organization_id;

l_spec.owner_id := v_user_id;

l_spec.spec_type := spec_rec.spec_type;

l_spec.spec_status := 700;

l_spec.delete_mark := 0;

gmd_spec_pub.create_spec

(p_api_version           => 2.0,

p_init_msg_list         => ‘T’,

p_commit                => ‘T’,

p_validation_level      => ‘FULL’,

p_spec                  => l_spec,

p_spec_tests_tbl        => l_spec_tests_tbl,

p_user_name             => ‘DATA_LOAD_USER’,

x_spec                  => l_spec_out,

x_spec_tests_tbl        => l_spec_tests_tbl_out,

x_return_status         => l_return_status,

x_msg_count             => l_msg_count,

x_msg_data              => l_msg_data

);

IF l_return_status = ‘S’

THEN

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘Y’,

error_message = NULL

WHERE staging_id = spec_rec.staging_id;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘Y’,

error_message = NULL

WHERE spec_name = spec_rec.spec_name;

ELSE

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

fnd_msg_pub.get (i,

fnd_api.g_false,

l_msg_data,

l_msg_dummy

);

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘E’,

error_message = error_message || ‘,’ ||

SUBSTR (l_return_status || ‘  ‘ || l_msg_data,

1,

2000

)

WHERE staging_id = spec_rec.staging_id;

UPDATE xx_gmd_spec_test_values_stg

SET process_flag = ‘E’,

error_message = error_message || ‘,’ ||

SUBSTR (l_return_status || ‘  ‘ || l_msg_data,

1,

2000

)

WHERE spec_name = spec_rec.spec_name;

END LOOP;

END IF;

ELSE

NULL;

END IF;

EXCEPTION

WHEN OTHERS

THEN

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘O’,

error_message = SUBSTR (errbuf, 1, 1000)

WHERE staging_id = spec_rec.staging_id;

UPDATE xx_gmd_spec_tests_stg

SET process_flag = ‘O’,

error_message = SUBSTR (errbuf, 1, 1000)

WHERE spec_name = spec_rec.spec_name;

END;

END LOOP;

COMMIT;

END xx_load_qc_spec_tests;