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;
|
