PROCEDURE validate_recipe_data (p_validate_recipe IN VARCHAR2)
IS
--Local Variables
lc_formula_num VARCHAR2(25);
lc_routing_num VARCHAR2(25);
ln_routing_id NUMBER;
ln_routstep_id NUMBER;
ln_formula_id NUMBER;
ln_formulaline_id NUMBER;
ln_recipe_type NUMBER;
ln_valrec_cnt NUMBER (10);
ln_trnsfrec_cnt NUMBER (10);
ln_recipe_no VARCHAR2 (30);
ln_routing_vers NUMBER (10);
ln_org_id NUMBER (10);
ln_bulk_error_cnt NUMBER (20);
ln_formula_vers NUMBER (10);
ln_rout_max_vers NUMBER (10);
ln_ffm_max_vers NUMBER (10);
ln_max_vers NUMBER (10);
lv_record_status_recipe VARCHAR2 (2);
lv_error_code_recipe VARCHAR2 (30);
lv_error_message_recipe VARCHAR2 (500);
--===============================
--Cursor to get Recipe Data
--===============================
CURSOR lcu_recipe_data
IS
SELECT transaction_id
, sr_no
, new_recipe_no recipe_no
, recipe_version
, recipe_description
, new_formula_no formula_no
, formula_version
, new_routing_no routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND ( NVL(record_status, 'N') = gc_newrecord_flag
OR NVL(record_status, 'N') = gc_validation_error_flag)
ORDER BY new_recipe_no;
--===============================
--Cursor to get Recipe Number
--===============================
/* cursor lcu_recipe_no(cp_recipe_no VARCHAR2)
is
select recipe_no
FROM apps.gmd_recipes
where recipe_no =cp_recipe_no; */
--=====================================
--Cursor to get Recipe Num With Version
--=====================================
CURSOR lcu_recipe_no (cp_recipe_no VARCHAR2, cp_recipe_vers NUMBER)
IS
SELECT recipe_no
FROM apps.gmd_recipes
WHERE 1=1
AND recipe_no = cp_recipe_no
AND recipe_version = cp_recipe_vers;
--===============================
--Cursor to get Formula Number
--===============================
CURSOR lcu_formula_no (cp_formula_no VARCHAR2)
IS
SELECT formula_no
FROM apps.fm_form_mst
WHERE 1=1
AND formula_no = cp_formula_no;
--===============================
--Cursor to get Formula Line Id
--===============================
CURSOR lcu_formulaline_id (p_formula_no VARCHAR2, p_formula_vers NUMBER)
IS
SELECT fmd.formulaline_id
FROM apps.fm_form_mst_b ffb,
apps.fm_matl_dtl fmd
WHERE 1=1
AND ffb.formula_id = fmd.formula_id
AND ffb.formula_no = p_formula_no
AND ffb.formula_vers = p_formula_vers
ORDER BY fmd.formulaline_id;
--=====================================
--Cursor to get Formula Num with Version
--======================================
CURSOR lcu_formula_num (
cp_formula_no VARCHAR2,
cp_formula_version NUMBER
)
IS
SELECT formula_no,formula_id
FROM apps.fm_form_mst
WHERE 1=1
AND formula_no = cp_formula_no
AND formula_vers = cp_formula_version;
--===============================
--Cursor to get Routing Number
--===============================
CURSOR lcu_routing_no (cp_routing_no VARCHAR2)
IS
SELECT routing_no
FROM apps.fm_rout_hdr
WHERE 1=1
AND routing_no = cp_routing_no;
--===================================
--Cursor to get Routing Num with Vers
--===================================
CURSOR lcu_routing_num (cp_routing_no VARCHAR2, cp_routing_vers NUMBER)
IS
SELECT routing_no,
routing_id
FROM apps.fm_rout_hdr
WHERE 1=1
AND routing_no = cp_routing_no
AND routing_vers = cp_routing_vers;
--===================================
--Cursor to get Routing Num with Vers
--===================================
CURSOR lcu_routstep_id (p_routing_no VARCHAR2,p_routing_vers NUMBER)
IS
SELECT frd.routingstep_id
FROM apps.fm_rout_hdr frh,
apps.fm_rout_dtl frd
WHERE 1=1
AND frh.routing_id = frd.routing_id
AND frh.routing_no = p_routing_no
AND frh.routing_vers = p_routing_vers;
--===============================
--Cursor to get Org ID
--===============================
CURSOR lcu_org_id (cp_owner_org_code VARCHAR2)
IS
SELECT organization_id
FROM apps.org_organization_definitions
WHERE 1=1
AND UPPER(organization_code) = UPPER(cp_owner_org_code);
-- Cursor to get Recipe Validity date
--===============================
--Cursor to get Recipe Type
--===============================
CURSOR lcu_recipe_type(p_recipe_type varchar2)
IS
SELECT lookup_code
FROM apps.fnd_lookup_values
WHERE 1=1
AND UPPER(lookup_type) = 'GMD_RECIPE_TYPE'
AND ( UPPER(meaning) = UPPER (p_recipe_type)
OR UPPER(lookup_code) = p_recipe_type );
--================================
--Cursor to get Max Recipe Version
--================================
CURSOR lcu_max_vers
IS
SELECT MAX (recipe_version)
FROM apps.gmd_recipes;
--===============================
--Cursor to get Formula Version
--===============================
CURSOR lcu_ffm_max_vers
IS
SELECT MAX (formula_vers)
FROM apps.fm_form_mst;
--=================================
--Cursor to get Max Routing Version
--=================================
CURSOR lcu_rout_max_vers
IS
SELECT MAX (routing_vers)
FROM apps.fm_rout_hdr;
--===============================
--Cursor to get Valid Records
--===============================
CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
IS
SELECT COUNT (1)
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND record_status = cp_record_status;
----------
--=========================================================================
--Cursor to Update formula line id and routing id in formula staging table
--==========================================================================
CURSOR lcu_updt_form_rout_id(p_formula_id number,p_routing_id number)
IS
SELECT fmd.formulaline_id
, frd.routingstep_id
-- , xrof.new_item_no
-- , xrof.issue_to_step
, xrof.transaction_id
FROM xxblr_opm_formula_stg xrof
, apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
, apps.mtl_system_items_b msi
, apps.fm_rout_dtl frd
WHERE 1=1
AND xrof.new_formula_no = ffm.formula_no
AND xrof.formula_vers = ffm.formula_vers
AND ffm.formula_id = p_formula_id
AND fmd.formula_id = ffm.formula_id
AND ( fmd.inventory_item_id = msi.inventory_item_id
and msi.organization_id = g_master_org
and msi.segment1 = xrof.new_item_no
)
AND fmd.qty = xrof.required_qty
AND frd.routing_id = p_routing_id
AND frd.routingstep_no = xrof.issue_to_step
ORDER BY xrof.transaction_id;
---------------
TYPE per_tbl_type IS TABLE OF lcu_recipe_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per per_tbl_type;
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing VALIDATE_RECIPE_DATA ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing VALIDATE_RECIPE_DATA ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_recipe_data;
LOOP
lt_per.DELETE;
FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;
FOR i IN lt_per.FIRST .. lt_per.LAST
LOOP
apps.fnd_file.put_line (apps.fnd_file.LOG,'Transaction ID:-'|| lt_per (i).transaction_id);
--===============================
-- Applying Transformation rules
--===============================
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number');
IF lt_per (i).recipe_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE NUM IS NULL ';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Version');
IF lt_per (i).recipe_version IS NULL
THEN
lt_per (i).recipe_version := 1;
END IF;
ln_recipe_no :=NULL;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
OPEN lcu_recipe_no (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_no
INTO ln_recipe_no;
CLOSE lcu_recipe_no;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid_1.0');
IF ln_recipe_no IS NOT NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE NUMBER ALREADY EXISTED';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number descrp');
IF lt_per (i).recipe_description IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE DESCRIPTION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula_no');
IF lt_per (i).formula_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA NUMBER IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula Version');
IF lt_per (i).formula_version IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA VERSION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
apps.fnd_file.put_line (apps.fnd_file.LOG,'Formula version :'|| lt_per (i).formula_version);
OPEN lcu_formula_num (lt_per (i).formula_no,
lt_per (i).formula_version
);
FETCH lcu_formula_num
INTO lc_formula_num,ln_formula_id;
CLOSE lcu_formula_num;
IF lc_formula_num IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA NO IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).formula_no := lc_formula_num;
lt_per(i).formula_id := ln_formula_id;
END IF;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
IF lt_per (i).routing_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING NUMBER IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
IF lt_per (i).routing_version IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING VERSION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lc_routing_num :=NULL;
ln_routing_id :=NULL;
OPEN lcu_routing_num (lt_per (i).routing_no,lt_per (i).routing_version);
FETCH lcu_routing_num
INTO lc_routing_num,ln_routing_id;
CLOSE lcu_routing_num;
IF lc_routing_num IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING NUMBER DOESNOT EXIST';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).routing_no := lc_routing_num;
lt_per(i).routing_id := ln_routing_id;
END IF;
END IF;
IF lt_per (i).recipe_validity_date IS NULL
THEN
lt_per (i).recipe_validity_date := '01-JAN-2011';
/*
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE VALIDITY DATE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message; */
END IF;
IF lt_per (i).owner_org_code IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,OWNER ORG CODE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
ln_org_id :=NULL;
OPEN lcu_org_id (lt_per (i).owner_org_code);
FETCH lcu_org_id INTO ln_org_id;
CLOSE lcu_org_id;
IF ln_org_id IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,OWNER ORG CODE IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per (i).organization_id := ln_org_id;
END IF;
END IF;
IF lt_per (i).recipe_type IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE TYPE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
ln_recipe_type :=NULL;
OPEN lcu_recipe_type(lt_per (i).recipe_type);
FETCH lcu_recipe_type INTO ln_recipe_type;
CLOSE lcu_recipe_type;
IF ln_recipe_type IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE TYPE IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).recipe_type := ln_recipe_type;
END IF;
END IF;
--============VALIDATIONS FOR RECIPE VALIDATITY RULES==============--
ln_formulaline_id :=NULL;
OPEN lcu_formulaline_id(lt_per (i).formula_no,lt_per (i).formula_version);
FETCH lcu_formulaline_id
INTO ln_formulaline_id;
CLOSE lcu_formulaline_id;
IF ln_formulaline_id IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA LINE ID IS NOT FOUND';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
-- ELSE
-- lt_per (i).formulaline_id := ln_formulaline_id; -- commented for a while
END IF;
ln_routstep_id := NULL;
OPEN lcu_routstep_id (lt_per (i).routing_no,lt_per (i).routing_version);
FETCH lcu_routstep_id INTO ln_routstep_id;
CLOSE lcu_routstep_id;
IF ln_routstep_id Is null then
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING STEP ID IS NOT FOUND';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
-- ELSE
-- lt_per (i).routstep_id := ln_routstep_id;
END IF;
--=================================
-- Identifying the success records
--=================================
IF lt_per (i).record_status <> gc_error_flag
THEN
lt_per (i).record_status := gc_validation_flag;
lt_per (i).error_message := 'All Validations Passed';
ELSE
lt_per (i).record_status := gc_validation_error_flag;
END IF;
BEGIN
FOR c_rec IN lcu_updt_form_rout_id(lt_per (i).formula_id,lt_per (i).routing_id)
LOOP
UPDATE xxblr_opm_formula_stg
SET formulaline_id = c_rec.formulaline_id
, routingstep_id = c_rec.routingstep_id
-- , record_status_recipe = lv_record_status_recipe
-- , error_message_recipe = lv_error_message_recipe
-- , error_code_recipe = lv_error_code_recipe
WHERE 1=1
AND transaction_id = c_rec.transaction_id;
EXIT WHEN lcu_updt_form_rout_id%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'Formulaline id Step id Update Error Message :-> '|| SQLERRM);
END;
END LOOP;
--===================================================================
--Bulk Update the records with status flag, validation error message
--===================================================================
ln_bulk_error_cnt := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 11:');
BEGIN
FORALL i IN lt_per.FIRST .. lt_per.LAST SAVE EXCEPTIONS
UPDATE xxblr_opm_recipe_stg
SET record_status = lt_per (i).record_status,
error_message = lt_per (i).error_message,
error_code = lt_per (i).error_code,
recipe_type = lt_per (i).recipe_type,
new_formula_no = lt_per (i).formula_no,
formula_version = lt_per (i).formula_version,
formula_id = lt_per (i).formula_id,
organization_id = lt_per (i).organization_id,
recipe_validity_date = lt_per (i).recipe_validity_date,
new_routing_no = lt_per (i).routing_no,
routing_id = lt_per (i).routing_id,
recipe_version = lt_per (i).recipe_version,
last_update_date = gd_current_date,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE transaction_id = lt_per (i).transaction_id;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'Bulk Update Error Message :-> '|| SQLERRM);
ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. ln_bulk_error_cnt
LOOP
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP;
END;
EXIT WHEN lcu_recipe_data%NOTFOUND;
END LOOP;
CLOSE lcu_recipe_data;
COMMIT;
OPEN lcu_valrec_cnt ('V'); --gc_validation_flag);
FETCH lcu_valrec_cnt
INTO ln_valrec_cnt;
CLOSE lcu_valrec_cnt;
apps.fnd_file.put_line (apps.fnd_file.LOG,' Number Of Records Validated :-> '|| ln_valrec_cnt);
apps.fnd_file.put_line (apps.fnd_file.LOG,' Number Of Records Failured :-> '|| (ln_trnsfrec_cnt - ln_valrec_cnt));
apps.fnd_file.put_line (apps.fnd_file.LOG,' ------ Procedure VALIDATE_DATA Exit------');
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-RECP-DATA :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure VALIDATE_RECIPE_DATA Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END validate_recipe_data;
IS
--Local Variables
lc_formula_num VARCHAR2(25);
lc_routing_num VARCHAR2(25);
ln_routing_id NUMBER;
ln_routstep_id NUMBER;
ln_formula_id NUMBER;
ln_formulaline_id NUMBER;
ln_recipe_type NUMBER;
ln_valrec_cnt NUMBER (10);
ln_trnsfrec_cnt NUMBER (10);
ln_recipe_no VARCHAR2 (30);
ln_routing_vers NUMBER (10);
ln_org_id NUMBER (10);
ln_bulk_error_cnt NUMBER (20);
ln_formula_vers NUMBER (10);
ln_rout_max_vers NUMBER (10);
ln_ffm_max_vers NUMBER (10);
ln_max_vers NUMBER (10);
lv_record_status_recipe VARCHAR2 (2);
lv_error_code_recipe VARCHAR2 (30);
lv_error_message_recipe VARCHAR2 (500);
--===============================
--Cursor to get Recipe Data
--===============================
CURSOR lcu_recipe_data
IS
SELECT transaction_id
, sr_no
, new_recipe_no recipe_no
, recipe_version
, recipe_description
, new_formula_no formula_no
, formula_version
, new_routing_no routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND ( NVL(record_status, 'N') = gc_newrecord_flag
OR NVL(record_status, 'N') = gc_validation_error_flag)
ORDER BY new_recipe_no;
--===============================
--Cursor to get Recipe Number
--===============================
/* cursor lcu_recipe_no(cp_recipe_no VARCHAR2)
is
select recipe_no
FROM apps.gmd_recipes
where recipe_no =cp_recipe_no; */
--=====================================
--Cursor to get Recipe Num With Version
--=====================================
CURSOR lcu_recipe_no (cp_recipe_no VARCHAR2, cp_recipe_vers NUMBER)
IS
SELECT recipe_no
FROM apps.gmd_recipes
WHERE 1=1
AND recipe_no = cp_recipe_no
AND recipe_version = cp_recipe_vers;
--===============================
--Cursor to get Formula Number
--===============================
CURSOR lcu_formula_no (cp_formula_no VARCHAR2)
IS
SELECT formula_no
FROM apps.fm_form_mst
WHERE 1=1
AND formula_no = cp_formula_no;
--===============================
--Cursor to get Formula Line Id
--===============================
CURSOR lcu_formulaline_id (p_formula_no VARCHAR2, p_formula_vers NUMBER)
IS
SELECT fmd.formulaline_id
FROM apps.fm_form_mst_b ffb,
apps.fm_matl_dtl fmd
WHERE 1=1
AND ffb.formula_id = fmd.formula_id
AND ffb.formula_no = p_formula_no
AND ffb.formula_vers = p_formula_vers
ORDER BY fmd.formulaline_id;
--=====================================
--Cursor to get Formula Num with Version
--======================================
CURSOR lcu_formula_num (
cp_formula_no VARCHAR2,
cp_formula_version NUMBER
)
IS
SELECT formula_no,formula_id
FROM apps.fm_form_mst
WHERE 1=1
AND formula_no = cp_formula_no
AND formula_vers = cp_formula_version;
--===============================
--Cursor to get Routing Number
--===============================
CURSOR lcu_routing_no (cp_routing_no VARCHAR2)
IS
SELECT routing_no
FROM apps.fm_rout_hdr
WHERE 1=1
AND routing_no = cp_routing_no;
--===================================
--Cursor to get Routing Num with Vers
--===================================
CURSOR lcu_routing_num (cp_routing_no VARCHAR2, cp_routing_vers NUMBER)
IS
SELECT routing_no,
routing_id
FROM apps.fm_rout_hdr
WHERE 1=1
AND routing_no = cp_routing_no
AND routing_vers = cp_routing_vers;
--===================================
--Cursor to get Routing Num with Vers
--===================================
CURSOR lcu_routstep_id (p_routing_no VARCHAR2,p_routing_vers NUMBER)
IS
SELECT frd.routingstep_id
FROM apps.fm_rout_hdr frh,
apps.fm_rout_dtl frd
WHERE 1=1
AND frh.routing_id = frd.routing_id
AND frh.routing_no = p_routing_no
AND frh.routing_vers = p_routing_vers;
--===============================
--Cursor to get Org ID
--===============================
CURSOR lcu_org_id (cp_owner_org_code VARCHAR2)
IS
SELECT organization_id
FROM apps.org_organization_definitions
WHERE 1=1
AND UPPER(organization_code) = UPPER(cp_owner_org_code);
-- Cursor to get Recipe Validity date
--===============================
--Cursor to get Recipe Type
--===============================
CURSOR lcu_recipe_type(p_recipe_type varchar2)
IS
SELECT lookup_code
FROM apps.fnd_lookup_values
WHERE 1=1
AND UPPER(lookup_type) = 'GMD_RECIPE_TYPE'
AND ( UPPER(meaning) = UPPER (p_recipe_type)
OR UPPER(lookup_code) = p_recipe_type );
--================================
--Cursor to get Max Recipe Version
--================================
CURSOR lcu_max_vers
IS
SELECT MAX (recipe_version)
FROM apps.gmd_recipes;
--===============================
--Cursor to get Formula Version
--===============================
CURSOR lcu_ffm_max_vers
IS
SELECT MAX (formula_vers)
FROM apps.fm_form_mst;
--=================================
--Cursor to get Max Routing Version
--=================================
CURSOR lcu_rout_max_vers
IS
SELECT MAX (routing_vers)
FROM apps.fm_rout_hdr;
--===============================
--Cursor to get Valid Records
--===============================
CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
IS
SELECT COUNT (1)
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND record_status = cp_record_status;
----------
--=========================================================================
--Cursor to Update formula line id and routing id in formula staging table
--==========================================================================
CURSOR lcu_updt_form_rout_id(p_formula_id number,p_routing_id number)
IS
SELECT fmd.formulaline_id
, frd.routingstep_id
-- , xrof.new_item_no
-- , xrof.issue_to_step
, xrof.transaction_id
FROM xxblr_opm_formula_stg xrof
, apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
, apps.mtl_system_items_b msi
, apps.fm_rout_dtl frd
WHERE 1=1
AND xrof.new_formula_no = ffm.formula_no
AND xrof.formula_vers = ffm.formula_vers
AND ffm.formula_id = p_formula_id
AND fmd.formula_id = ffm.formula_id
AND ( fmd.inventory_item_id = msi.inventory_item_id
and msi.organization_id = g_master_org
and msi.segment1 = xrof.new_item_no
)
AND fmd.qty = xrof.required_qty
AND frd.routing_id = p_routing_id
AND frd.routingstep_no = xrof.issue_to_step
ORDER BY xrof.transaction_id;
---------------
TYPE per_tbl_type IS TABLE OF lcu_recipe_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per per_tbl_type;
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing VALIDATE_RECIPE_DATA ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing VALIDATE_RECIPE_DATA ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_recipe_data;
LOOP
lt_per.DELETE;
FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;
FOR i IN lt_per.FIRST .. lt_per.LAST
LOOP
apps.fnd_file.put_line (apps.fnd_file.LOG,'Transaction ID:-'|| lt_per (i).transaction_id);
--===============================
-- Applying Transformation rules
--===============================
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number');
IF lt_per (i).recipe_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE NUM IS NULL ';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Version');
IF lt_per (i).recipe_version IS NULL
THEN
lt_per (i).recipe_version := 1;
END IF;
ln_recipe_no :=NULL;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
OPEN lcu_recipe_no (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_no
INTO ln_recipe_no;
CLOSE lcu_recipe_no;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid_1.0');
IF ln_recipe_no IS NOT NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE NUMBER ALREADY EXISTED';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number descrp');
IF lt_per (i).recipe_description IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RECIPE DESCRIPTION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula_no');
IF lt_per (i).formula_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA NUMBER IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula Version');
IF lt_per (i).formula_version IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA VERSION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
apps.fnd_file.put_line (apps.fnd_file.LOG,'Formula version :'|| lt_per (i).formula_version);
OPEN lcu_formula_num (lt_per (i).formula_no,
lt_per (i).formula_version
);
FETCH lcu_formula_num
INTO lc_formula_num,ln_formula_id;
CLOSE lcu_formula_num;
IF lc_formula_num IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA NO IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).formula_no := lc_formula_num;
lt_per(i).formula_id := ln_formula_id;
END IF;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
IF lt_per (i).routing_no IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING NUMBER IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
END IF;
IF lt_per (i).routing_version IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING VERSION IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lc_routing_num :=NULL;
ln_routing_id :=NULL;
OPEN lcu_routing_num (lt_per (i).routing_no,lt_per (i).routing_version);
FETCH lcu_routing_num
INTO lc_routing_num,ln_routing_id;
CLOSE lcu_routing_num;
IF lc_routing_num IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING NUMBER DOESNOT EXIST';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).routing_no := lc_routing_num;
lt_per(i).routing_id := ln_routing_id;
END IF;
END IF;
IF lt_per (i).recipe_validity_date IS NULL
THEN
lt_per (i).recipe_validity_date := '01-JAN-2011';
/*
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE VALIDITY DATE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message; */
END IF;
IF lt_per (i).owner_org_code IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,OWNER ORG CODE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
ln_org_id :=NULL;
OPEN lcu_org_id (lt_per (i).owner_org_code);
FETCH lcu_org_id INTO ln_org_id;
CLOSE lcu_org_id;
IF ln_org_id IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,OWNER ORG CODE IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per (i).organization_id := ln_org_id;
END IF;
END IF;
IF lt_per (i).recipe_type IS NULL
THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE TYPE IS NULL';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
ln_recipe_type :=NULL;
OPEN lcu_recipe_type(lt_per (i).recipe_type);
FETCH lcu_recipe_type INTO ln_recipe_type;
CLOSE lcu_recipe_type;
IF ln_recipe_type IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ' ,RECIPE TYPE IS INVALID';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
ELSE
lt_per(i).recipe_type := ln_recipe_type;
END IF;
END IF;
--============VALIDATIONS FOR RECIPE VALIDATITY RULES==============--
ln_formulaline_id :=NULL;
OPEN lcu_formulaline_id(lt_per (i).formula_no,lt_per (i).formula_version);
FETCH lcu_formulaline_id
INTO ln_formulaline_id;
CLOSE lcu_formulaline_id;
IF ln_formulaline_id IS NULL THEN
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',FORMULA LINE ID IS NOT FOUND';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
-- ELSE
-- lt_per (i).formulaline_id := ln_formulaline_id; -- commented for a while
END IF;
ln_routstep_id := NULL;
OPEN lcu_routstep_id (lt_per (i).routing_no,lt_per (i).routing_version);
FETCH lcu_routstep_id INTO ln_routstep_id;
CLOSE lcu_routstep_id;
IF ln_routstep_id Is null then
lt_per (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ROUTING STEP ID IS NOT FOUND';
lt_per (i).error_code := gc_error_code;
lt_per (i).error_message := gc_error_message;
-- ELSE
-- lt_per (i).routstep_id := ln_routstep_id;
END IF;
--=================================
-- Identifying the success records
--=================================
IF lt_per (i).record_status <> gc_error_flag
THEN
lt_per (i).record_status := gc_validation_flag;
lt_per (i).error_message := 'All Validations Passed';
ELSE
lt_per (i).record_status := gc_validation_error_flag;
END IF;
BEGIN
FOR c_rec IN lcu_updt_form_rout_id(lt_per (i).formula_id,lt_per (i).routing_id)
LOOP
UPDATE xxblr_opm_formula_stg
SET formulaline_id = c_rec.formulaline_id
, routingstep_id = c_rec.routingstep_id
-- , record_status_recipe = lv_record_status_recipe
-- , error_message_recipe = lv_error_message_recipe
-- , error_code_recipe = lv_error_code_recipe
WHERE 1=1
AND transaction_id = c_rec.transaction_id;
EXIT WHEN lcu_updt_form_rout_id%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'Formulaline id Step id Update Error Message :-> '|| SQLERRM);
END;
END LOOP;
--===================================================================
--Bulk Update the records with status flag, validation error message
--===================================================================
ln_bulk_error_cnt := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 11:');
BEGIN
FORALL i IN lt_per.FIRST .. lt_per.LAST SAVE EXCEPTIONS
UPDATE xxblr_opm_recipe_stg
SET record_status = lt_per (i).record_status,
error_message = lt_per (i).error_message,
error_code = lt_per (i).error_code,
recipe_type = lt_per (i).recipe_type,
new_formula_no = lt_per (i).formula_no,
formula_version = lt_per (i).formula_version,
formula_id = lt_per (i).formula_id,
organization_id = lt_per (i).organization_id,
recipe_validity_date = lt_per (i).recipe_validity_date,
new_routing_no = lt_per (i).routing_no,
routing_id = lt_per (i).routing_id,
recipe_version = lt_per (i).recipe_version,
last_update_date = gd_current_date,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE transaction_id = lt_per (i).transaction_id;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'Bulk Update Error Message :-> '|| SQLERRM);
ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. ln_bulk_error_cnt
LOOP
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP;
END;
EXIT WHEN lcu_recipe_data%NOTFOUND;
END LOOP;
CLOSE lcu_recipe_data;
COMMIT;
OPEN lcu_valrec_cnt ('V'); --gc_validation_flag);
FETCH lcu_valrec_cnt
INTO ln_valrec_cnt;
CLOSE lcu_valrec_cnt;
apps.fnd_file.put_line (apps.fnd_file.LOG,' Number Of Records Validated :-> '|| ln_valrec_cnt);
apps.fnd_file.put_line (apps.fnd_file.LOG,' Number Of Records Failured :-> '|| (ln_trnsfrec_cnt - ln_valrec_cnt));
apps.fnd_file.put_line (apps.fnd_file.LOG,' ------ Procedure VALIDATE_DATA Exit------');
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-RECP-DATA :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure VALIDATE_RECIPE_DATA Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END validate_recipe_data;
No comments:
Post a Comment
Note: only a member of this blog may post a comment.