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

 

 

Introduction:

This blog has the PLSQL query that can be used to create Quality Spec WIP Validity Rule using oracle seeded API.

 

Cause of the issue:

Business will provide the raw data based on that we need to create Quality Spec WIP Validity Rule.

 

How do we solve:
Created a PL/SQL procedure to create Quality WIP Validity Rule in OPM using API.

 

   CREATE OR REPLACE PROCEDURE xx_load_qc_spec_wip_vr (errbuf OUT VARCHAR2, retcode OUT NUMBER)

IS

CURSOR c_staging

IS

SELECT *

FROM xx_gmd_spec_wip_vr_stg

WHERE process_flag = ‘N’;

 

l_api_version          NUMBER                            := 1.0;

l_init_msg_list        VARCHAR2 (1)                      := ‘T’;

l_commit               VARCHAR2 (1)                      := ‘F’;

l_validation_level     VARCHAR2 (10)                     := ‘FULL’;

l_user_name            VARCHAR2 (100)                    := ‘DATA_LOAD_USER’;

l_wip_spec_vrs_tbl     gmd_spec_vrs_pub.wip_spec_vrs_tbl;

l_out_spec_vrs_tbl     gmd_spec_vrs_pub.wip_spec_vrs_tbl;

l_return_status        VARCHAR2 (1);

l_msg_count            NUMBER;

l_msg_data             VARCHAR2 (2000);

l_dummy                VARCHAR2 (1000);

l_spec_id              NUMBER;

l_recipe_id            NUMBER;

l_error_count          NUMBER;

l_organization_id      NUMBER;

l_out_spec_status_id   NUMBER;

l_in_spec_status_id    NUMBER;

BEGIN

FOR r_stg IN c_staging

LOOP

BEGIN

l_wip_spec_vrs_tbl.DELETE;

l_spec_id := 0;

l_recipe_id := 0;

l_error_count := 0;

l_organization_id := 0;

l_out_spec_status_id := 0;

l_in_spec_status_id := 0;

 

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_wip_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification is not defined in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

 

UPDATE xx_gmd_spec_wip_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Specification is not defined in the system’

WHERE staging_id = r_stg.staging_id;

END;

 

BEGIN

SELECT recipe_id

INTO l_recipe_id

FROM gmd_recipes

WHERE recipe_no = r_stg.recipe_no;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_error_count := 1;

 

UPDATE xx_gmd_spec_wip_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Receipe is not defined in the system’

WHERE staging_id = r_stg.staging_id;

WHEN OTHERS

THEN

l_error_count := 1;

 

UPDATE xx_gmd_spec_wip_vr_stg

SET process_flag = ‘E’,

error_message =

error_message

|| ‘ ,’

|| ‘Receipe is not defined in the system’

WHERE staging_id = r_stg.staging_id;

END;

 

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_wip_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_wip_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 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_wip_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_wip_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_wip_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_wip_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_wip_spec_vrs_tbl (1).spec_id := l_spec_id;

l_wip_spec_vrs_tbl (1).recipe_id := l_recipe_id;

l_wip_spec_vrs_tbl (1).organization_id := l_organization_id;

l_wip_spec_vrs_tbl (1).orgn_code := r_stg.orgn_code;

l_wip_spec_vrs_tbl (1).recipe_version := r_stg.recipe_version;

l_wip_spec_vrs_tbl (1).formula_no := r_stg.formula_no;

l_wip_spec_vrs_tbl (1).formula_vers := r_stg.formula_version;

l_wip_spec_vrs_tbl (1).oprn_no := r_stg.oprn_no;

l_wip_spec_vrs_tbl (1).oprn_vers := r_stg.oprn_vers;

l_wip_spec_vrs_tbl (1).spec_vr_status := r_stg.spec_vr_status;

l_wip_spec_vrs_tbl (1).start_date := r_stg.start_date;

l_wip_spec_vrs_tbl (1).control_lot_attrib_ind :=

r_stg.control_lot_attrib_ind;

l_wip_spec_vrs_tbl (1).out_of_spec_lot_status_id :=

l_out_spec_status_id;

— r_stg.out_of_spec_lot_status;

l_wip_spec_vrs_tbl (1).in_spec_lot_status_id :=

l_in_spec_status_id;

— r_stg.in_spec_lot_status;

l_wip_spec_vrs_tbl (1).coa_type := r_stg.coa_type;

l_wip_spec_vrs_tbl (1).delete_mark := r_stg.delete_mark;

 

IF l_error_count = 0

THEN

gmd_spec_vrs_pub.create_wip_spec_vrs

(p_api_version           => l_api_version,

p_init_msg_list         => l_init_msg_list,

p_commit                => l_commit,

p_validation_level      => l_validation_level,

p_wip_spec_vrs_tbl      => l_wip_spec_vrs_tbl,

p_user_name             => l_user_name,

x_wip_spec_vrs_tbl      => l_out_spec_vrs_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_wip_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 (   ‘WIP Error Message ‘

|| i

|| ‘: ‘

|| l_msg_data

);

END LOOP;

 

UPDATE xx_gmd_spec_wip_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_wip_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_wip_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_wip_vr;

 

 

Recent Posts