Procedure to create Quality Spec INV Validity Rule in OPM through API

IS

CURSOR c_stg

IS

SELECT *

FROM xx_gmd_spec_inv_vr_stg

WHERE process_flag = ‘N’;

l_inventory_spec_vrs_tbl   gmd_spec_vrs_pub.inventory_spec_vrs_tbl;

l_out_inventory_spec_tbl   gmd_spec_vrs_pub.inventory_spec_vrs_tbl;

l_inventory_spec_rec       gmd_inventory_spec_vrs%ROWTYPE;

l_return_status            VARCHAR2 (1);

l_msg_count                NUMBER;

l_msg_data                 VARCHAR2 (2000);

l_dummy                    VARCHAR2 (1000);

l_organization_id          NUMBER;

l_spec_id                  NUMBER;

l_error_count              NUMBER;

l_out_spec_status_id       NUMBER;

l_in_spec_status_id        NUMBER;

BEGIN

FOR r_stg IN c_stg

LOOP

BEGIN

— Initialize everything

l_inventory_spec_rec := NULL;

l_inventory_spec_vrs_tbl.DELETE;

l_organization_id := 0;

l_spec_id := 0;

l_error_count := 0;

l_out_spec_status_id := 0;

l_in_spec_status_id := 0;

BEGIN

SELECT organization_id

INTO l_organization_id

FROM org_organization_definitions

WHERE organization_code = r_stg.orgn_code;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Organization Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Organization Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

END;

BEGIN

SELECT gs.spec_id, gs.owner_organization_id

INTO l_spec_id, l_organization_id

FROM gmd_specifications gs, org_organization_definitions ood

WHERE gs.owner_organization_id = ood.organization_id

AND gs.spec_name = r_stg.spec_name

AND ood.organization_code = r_stg.orgn_code

AND gs.spec_vers = r_stg.spec_vers;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification is not created in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification is not created in the system’

WHERE staging_id = r_stg.staging_id;

END;

BEGIN

SELECT status_id

INTO l_in_spec_status_id

FROM mtl_material_statuses

WHERE status_code = TO_CHAR (r_stg.in_spec_lot_status);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘In Spec Lot Status Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘In Spec Lot Status Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

END;

BEGIN

SELECT status_id

INTO l_out_spec_status_id

FROM mtl_material_statuses

WHERE status_code = TO_CHAR (r_stg.out_of_spec_lot_status);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Out Spec Lot Status Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Out Spec Lot Status Code is not defined in the system’

WHERE staging_id = r_stg.staging_id;

END;

l_inventory_spec_rec.organization_id := l_organization_id;

l_inventory_spec_rec.orgn_code := r_stg.orgn_code;

l_inventory_spec_rec.spec_id := l_spec_id;

l_inventory_spec_rec.start_date := r_stg.start_date;

l_inventory_spec_rec.control_lot_attrib_ind :=

r_stg.control_lot_attrib_ind;

l_inventory_spec_rec.out_of_spec_lot_status_id :=

l_out_spec_status_id;

l_inventory_spec_rec.in_spec_lot_status_id := l_in_spec_status_id;

l_inventory_spec_rec.coa_type := r_stg.coa_type;

l_inventory_spec_rec.delete_mark := r_stg.delete_mark;

l_inventory_spec_rec.spec_vr_status := r_stg.spec_vr_status;

l_inventory_spec_vrs_tbl (1) := l_inventory_spec_rec;

IF l_error_count = 0

THEN

gmd_spec_vrs_pub.create_inventory_spec_vrs

(p_api_version                 => 1.0,

p_init_msg_list               => ‘T’,

p_commit                      => ‘F’,

p_validation_level            => ‘FULL’,

p_inventory_spec_vrs_tbl      => l_inventory_spec_vrs_tbl,

p_user_name                   => ‘DATA_LOAD_USER’,

x_inventory_spec_vrs_tbl      => l_out_inventory_spec_tbl,

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_inv_vr_stg

SET process_flag = ‘S’,

error_message = NULL

WHERE staging_id = r_stg.staging_id;

ELSE

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data,

l_dummy);

DBMS_OUTPUT.put_line (   ‘Inventory Error Message ‘

|| i

|| ‘: ‘

|| l_msg_data

);

END LOOP;

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message = SUBSTR (l_msg_data, 1, 2000)

WHERE staging_id = r_stg.staging_id;

END IF;

ELSE

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘E’,

error_message =

‘Validation Errors:  ‘ || ‘ ‘ || error_message

WHERE staging_id = r_stg.staging_id;

END IF;

EXCEPTION

WHEN OTHERS

THEN

UPDATE xx_gmd_spec_inv_vr_stg

SET process_flag = ‘O’,

error_message = SUBSTR (l_msg_data, 1, 2000)

WHERE staging_id = r_stg.staging_id;

END;

END LOOP;

COMMIT;

END xx_load_qc_spec_inv_vr;