PROCEDURE validate_oprn_data (p_validate_operation IN VARCHAR2)
IS
--Loacal variables
ln_act VARCHAR2 (100);
ln_oprn_vers NUMBER;
ln_bulk_error_cnt NUMBER;
ln_max_oprn_vers NUMBER;
ln_scale_type NUMBER;
ln_plan_type NUMBER;
ln_plan_type_val VARCHAR2(30);
ln_organization_id NUMBER;
l_count NUMBER;
l_transaction_id NUMBER;
l_paln_type VARCHAR2 (100);
l_routing_no VARCHAR2 (100);
l_oprn_no VARCHAR2 (100);
l_activity VARCHAR2 (100);
l_resources VARCHAR2 (100);
lc_activity_data VARCHAR2 (25);
lc_process_qty_uom VARCHAR2 (25);
lc_resource VARCHAR2 (25);
lc_act_rsrce NUMBER;
lc_oprn_class VARCHAR2 (7);
ln_oprn_id NUMBER;
lc_usage_uom VARCHAR2 (10);
lc_cost_alys_code VARCHAR2 (4);
lt_rsrc_first VARCHAR2 (25);
lt_rsrc_last VARCHAR2 (25);
--===============================
--Cursor to get Operation Data
--===============================
CURSOR lcu_oprn_data (cp_status_flag VARCHAR2)
IS
SELECT transaction_id
, new_oprn_no oprn_no
, oprn_vers
, oprn_desc
, oprn_class
, oprn_class_desc
, owner_org_code
, oper_valid_from
, process_qty_uom
, activity
-- , resources
, resource_count
, resource_usage
, DECODE(usage_uom,'hr','Hrs',usage_uom) usage_uom
, scale_type
, process_output_qty
, component_class
, cost_analysis_code
, DECODE(UPPER(plan_type),'OPTIONAL',NULL,plan_type) plan_type
, offset_interval
, organization_id
, error_code
, record_status
, error_message
FROM xxblr_opm_routing_stg
WHERE 1=1
AND ( NVL(record_status, 'N') = gc_newrecord_flag
OR NVL(record_status, 'N') = gc_validation_error_flag
)
ORDER BY transaction_id;--oprn_no;
--===============================
--Cursor to get Operation Number
--===============================
CURSOR lcu_oprn_no (cp_oprn_no VARCHAR2)
IS
SELECT oprn_no
FROM apps.gmd_operations_b
WHERE 1=1
AND UPPER(oprn_no) = UPPER(cp_oprn_no);
--===============================
--Cursor to get Operation Number
--===============================
CURSOR lcu_organization_id(p_org_code varchar2)
IS
SELECT organization_id
FROM apps.org_organization_definitions
WHERE 1=1
AND UPPER (organization_code) =UPPER (p_org_code);
--===============================
--Cursor to get Operation Version
--===============================
CURSOR lcu_oprn_vers (cp_oprn_no VARCHAR2, cp_oprn_vers NUMBER)
IS
SELECT oprn_id
FROM apps.gmd_operations_b
WHERE 1=1
AND UPPER(oprn_no) = (cp_oprn_no)
AND oprn_vers = NVL(cp_oprn_vers,1);
--===================================
--Cursor to get Operation Max Version
--===================================
CURSOR max_oprn_vers
IS
SELECT MAX (oprn_vers)
FROM apps.gmd_operations_b;
--===============================
--Cursor to get Operation Desc
--===============================
CURSOR lcu_oprn_desc (cp_oprn_desc NUMBER)
IS
SELECT oprn_desc
FROM apps.gmd_operations_b
WHERE 1=1
AND UPPER(oprn_desc) = UPPER(cp_oprn_desc);
--===============================
--Cursor to get Oprn Valid From
--===============================
CURSOR lcu_valid_from (cp_oprn_no VARCHAR2)
IS
SELECT effective_start_date
FROM apps.gmd_operations_b
WHERE 1=1
AND oprn_no = cp_oprn_no;
--=========================================
--Cursor to get Process Output Qty and UOM
--=========================================
CURSOR lcu_process_qty_uom (cp_uom_code VARCHAR2)
IS
SELECT DISTINCT uom_code
FROM apps.mtl_units_of_measure
WHERE 1=1
AND UPPER(uom_code) = TRIM(UPPER(cp_uom_code));
--=========================================
--Cursor to get Plan Type
--=========================================
CURSOR lcu_plan_type (p_plan_type VARCHAR2)
IS
SELECT lookup_code
FROM apps.fnd_lookup_values
WHERE 1=1
AND lookup_type = 'GMD_PRIM_RSRC_IND'
AND ( UPPER(meaning) = UPPER (p_plan_type)
OR UPPER(lookup_code) = UPPER (p_plan_type)
);
--===============================
--Cursor to get Operation Class
--===============================
CURSOR lcu_oprn_class (cp_oprn_class_desc VARCHAR2)
IS
SELECT oprn_class
FROM apps.fm_oprn_cls foc
WHERE 1=1
AND ( UPPER(foc.oprn_class_desc) = UPPER (cp_oprn_class_desc)
OR UPPER(foc.oprn_class) = UPPER (cp_oprn_class_desc)
);
--===============================
--Cursor to get Resource
--===============================
CURSOR lcu_resource (cp_resource_code VARCHAR2)
IS
SELECT resources
FROM apps.cr_rsrc_mst_vl
WHERE 1=1
AND UPPER(resources) = UPPER(TRIM(cp_resource_code));
/*
--===============================
--Cursor to get Resource Usage
--===============================
CURSOR lcu_resource_usage (cp_resources VARCHAR2)
IS
SELECT resource_usage
FROM apps.gmd_operation_resources
WHERE 1=1
AND UPPER(resources) = UPPER(cp_resources);
*/
--===============================
--Cursor to get Scale Type
--===============================
CURSOR lcu_scale_type (p_scale_type VARCHAR2)
IS
SELECT lookup_code
FROM apps.fnd_lookup_values
WHERE 1=1
AND UPPER(lookup_type) = 'SCALE_TYPE'
AND ( UPPER(meaning) = UPPER(p_scale_type)
OR UPPER(lookup_code) = UPPER(p_scale_type)
);
--=================================
--Cursor to get Resource Usage UOM
--=================================
CURSOR lcu_rers_usage_uom (p_usage_uom VARCHAR2)
IS
SELECT uom_code
FROM apps.mtl_units_of_measure
WHERE 1=1
AND ( UPPER(uom_code) = UPPER (p_usage_uom)
OR UPPER(unit_of_measure) = UPPER (p_usage_uom)
);
--===============================
--Cursor to get Cost Analysis Code
--===============================
CURSOR lcu_cost_alys_code (p_cost_analysis_code VARCHAR2)
IS
SELECT cost_analysis_code
FROM apps.cm_alys_mst
WHERE 1=1
AND cost_analysis_code = p_cost_analysis_code;
--===============================
--Cursor to get Activity
--===============================
CURSOR lcu_act_data (cp_activity VARCHAR2)
IS
SELECT activity
FROM apps.gmd_activities_vl
WHERE 1=1
AND UPPER (activity) = UPPER (cp_activity);
--===============================
--Cursor to get Valid Record Count
--===============================
CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
IS
SELECT COUNT (1)
FROM xxblr_opm_routing_stg
WHERE 1=1
AND UPPER(record_status) = UPPER(cp_record_status);
--============================
--Cursor to get Plan Type
--============================
CURSOR lcu_plan_type_val(cp_oprn_no VARCHAR2,cp_oprn_vers NUMBER,cp_activity VARCHAR2) --(cp_transaction_id NUMBER)
IS
SELECT DECODE(rsrc_cnt,0,'Primary','Auxiliary') Plan_Type
FROM (
SELECT COUNT(1) rsrc_cnt
FROM apps.gmd_operation_resources gmor
,apps.gmd_operation_activities gmoa
,apps.gmd_operations_b gmob
WHERE 1=1
AND gmor.oprn_line_id = gmoa.oprn_line_id
AND gmoa.oprn_id = gmob.oprn_id
AND UPPER(gmoa.activity) = UPPER(cp_activity)
AND UPPER(gmob.oprn_no) = UPPER(cp_oprn_no)
AND gmob.oprn_vers = cp_oprn_vers
);
/* SELECT plan_type
FROM
(
(SELECT CASE
WHEN ronum = 1 THEN 'Primary'
ELSE 'Auxiliary'
END plan_type
, transaction_id
, routing_no
, oprn_no
, activity
, resources
, oprn_vers
FROM
(
SELECT routing_no
, oprn_no
, activity
, resources
, oprn_vers
, transaction_id
, ROW_NUMBER () OVER (PARTITION BY activity,oprn_no ORDER BY resources) ronum
FROM xxblr_opm_routing_stg b
ORDER BY oprn_no
, activity
, resources
, transaction_id
)
)
) pln_typ
WHERE 1=1
AND transaction_id = cp_transaction_id;
*/
--======================================
--Cursor to get Activity data
--======================================
CURSOR lcu_act_rsrc_data (cp_oprn_no VARCHAR2, cp_oprn_vers NUMBER,cp_activity VARCHAR2,cp_resource VARCHAR2)
IS
SELECT COUNT(1) act_rsrc_cnt
FROM apps.gmd_operation_resources gmor
, apps.gmd_operation_activities gmoa
, apps.gmd_operations_b gmob
WHERE 1=1
AND gmor.oprn_line_id = gmoa.oprn_line_id
AND gmoa.oprn_id = gmob.oprn_id
AND UPPER(gmoa.activity) = UPPER(cp_activity)
AND UPPER(gmor.resources) = UPPER(cp_resource)
AND gmob.oprn_no = cp_oprn_no
AND gmob.oprn_vers = cp_oprn_vers;
TYPE per_oprn_type IS TABLE OF lcu_oprn_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_oprn_tbl per_oprn_type;
lc_prev_activity VARCHAR2(16) := 'NA';
lc_prev_oprn VARCHAR2(16) := 'NA';
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG, ' --------- Executing VALIDATE_OPRN_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_OPRN_DATA ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
/*-- not required -- Commented by srikanth. on 09/04/2013--
BEGIN
OPEN lcu_plan_type_val;
loop
FETCH lcu_plan_type_val
INTO l_paln_type;--,l_transaction_id,l_routing_no, l_oprn_no, l_activity,l_resources,l_,oprn_vers;
UPDATE xxblr_opm_routing_stg
SET plan_type = l_paln_type
WHERE transaction_id = l_transaction_id
AND routing_no = l_routing_no
AND oprn_vers = l_oprn_vers; -- added
EXIT WHEN lcu_plan_type1%NOTFOUND;
END loop;
COMMIT;
CLOSE lcu_plan_type1;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'NO DATA FOUND FOR PLAN TYPE'||SQLERRM);
END;
apps.fnd_file.put_line(apps.fnd_file.LOG,' PLAN TYPE'||l_paln_type);
*/
OPEN lcu_oprn_data (p_validate_operation);
LOOP
lt_oprn_tbl.DELETE;
FETCH lcu_oprn_data BULK COLLECT INTO lt_oprn_tbl LIMIT 50000;
l_count :=0;
IF lt_oprn_tbl.count > 0 THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'OPRN Loop COUNT:' || lt_oprn_tbl.count);
FOR i IN lt_oprn_tbl.FIRST .. lt_oprn_tbl.LAST
LOOP
l_count :=l_count+1;
apps.fnd_file.put_line (apps.fnd_file.LOG, '*******************');
apps.fnd_file.put_line (apps.fnd_file.LOG, 'Loop lcu_oprn_data - '||l_count);
--===============================
-- Applying Transformation rules
--===============================
gc_error_code := NULL;
gc_error_message := NULL;
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for operation no: '||lt_oprn_tbl (i).oprn_no);
IF lt_oprn_tbl (i).oprn_no IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',OPERATION NUM IS NULL';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
END IF;
IF lt_oprn_tbl (i).oprn_vers IS NULL
THEN
lt_oprn_tbl (i).oprn_vers := 1;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Operation version :'|| lt_oprn_tbl (i).oprn_vers);
OPEN lcu_oprn_vers (lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers);
FETCH lcu_oprn_vers
INTO ln_oprn_id;
CLOSE lcu_oprn_vers;
IF ln_oprn_id IS NOT NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := 'OPERATION NO ALREADY EXISTED ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
END IF;
IF lt_oprn_tbl (i).oprn_desc IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',OPRN DESC IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
END IF;
IF lt_oprn_tbl (i).oper_valid_from IS NULL THEN
-- lt_oprn_tbl (i).record_status := gc_error_flag;
-- gc_error_code := '-PAT04';
-- gc_error_message := ',Valid From is NULL ';
-- lt_oprn_tbl (i).error_code := gc_error_code;
-- lt_oprn_tbl (i).error_message := gc_error_message;
lt_oprn_tbl (i).oper_valid_from := TO_DATE('01-Jan-2011');
END IF;
IF lt_oprn_tbl (i).owner_org_code IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ORG CODE IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
ln_organization_id :=NULL;
OPEN lcu_organization_id(lt_oprn_tbl (i).owner_org_code);
FETCH lcu_organization_id
INTO ln_organization_id;
CLOSE lcu_organization_id;
IF ln_organization_id IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ORG CODE IS INVALID ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).organization_id := ln_organization_id;
END IF;
END IF;
IF lt_oprn_tbl (i).process_qty_uom IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',PROCESS QTY UOM IS NULL';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lc_process_qty_uom := NULL;
OPEN lcu_process_qty_uom (lt_oprn_tbl (i).process_qty_uom);
FETCH lcu_process_qty_uom
INTO lc_process_qty_uom;
CLOSE lcu_process_qty_uom;
IF lc_process_qty_uom IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',PROCESS QTY UOM IS NOT DEFINED';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).process_qty_uom := lc_process_qty_uom;
END IF;
END IF;
IF lt_oprn_tbl (i).oprn_class_desc IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',OPERATION CLASS IS NULL';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
OPEN lcu_oprn_class (lt_oprn_tbl (i).oprn_class_desc);
FETCH lcu_oprn_class
INTO lc_oprn_class;
CLOSE lcu_oprn_class;
IF lc_oprn_class IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := 'OPERATION CLASS IS INVALID';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).oprn_class := lc_oprn_class;
END IF;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 8:' || lc_process_qty_uom);
apps.fnd_file.put_line (apps.fnd_file.LOG, 'Validation for Activity');
IF lt_oprn_tbl (i).activity IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ACTIVITY IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
OPEN lcu_act_data (lt_oprn_tbl (i).activity);
FETCH lcu_act_data
INTO lc_activity_data;
CLOSE lcu_act_data;
IF lc_activity_data IS NULL
THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',ACTIVITY IS INVALID ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).activity := lc_activity_data;
END IF;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Calling Detail Loop');
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Validation Operating = '|| p_validate_operation);
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Oprn No = ');
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Oprn Activity = '|| lt_oprn_tbl (i).activity);
apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Resource'||lt_oprn_tbl (i).resource_code);
IF lt_oprn_tbl (i).resource_code IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RESOURCE IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lc_resource := NULL;
OPEN lcu_resource (lt_oprn_tbl (i).resource_code);
FETCH lcu_resource
INTO lc_resource;
CLOSE lcu_resource;
IF lc_resource IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RESOURCE IS NOT DEFINED ';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message :=gc_error_message;
ELSE
lt_oprn_tbl (i).resource_code := lc_resource;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,' Resource' || lc_resource);
END IF;
-----------------
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_A: Validation for Activity '||lt_oprn_tbl (i).activity||' & '||lt_oprn_tbl (i).resources);
IF lt_oprn_tbl (i).resources IS NULL OR lt_oprn_tbl (i).activity IS NULL THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_A.1');
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RESOURCE OR ACTIVITY IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_B ');
ELSE
lc_act_rsrce := NULL;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_C');
-- OPEN lcu_act_rsrc_data (lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers,lt_oprn_tbl (i).activity,lt_oprn_tbl (i).resources);
-- FETCH lcu_act_rsrc_data INTO lc_act_rsrce;
-- CLOSE lcu_act_rsrc_data;
SELECT COUNT(1) --gmor.oprn_line_id
INTO lc_act_rsrce
FROM apps.gmd_operation_resources gmor
, apps.gmd_operation_activities gmoa
, apps.gmd_operations_b gmob
WHERE 1=1
AND gmor.oprn_line_id = gmoa.oprn_line_id
AND gmoa.oprn_id = gmob.oprn_id
AND UPPER(gmoa.activity) = UPPER(lt_oprn_tbl (i).activity)
AND UPPER(gmor.resources) = UPPER(lt_oprn_tbl (i).resources)
AND gmob.oprn_no = lt_oprn_tbl (i).oprn_no
AND gmob.oprn_vers = lt_oprn_tbl (i).oprn_vers;
IF lc_act_rsrce != 0 THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RESOURCE ALREADY EXISTS';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lc_act_rsrce := NULL;
END IF;
END IF;
------------------
IF lt_oprn_tbl (i).scale_type IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',SCALE TYPE IS NULL ';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
ln_scale_type := NULL;
OPEN lcu_scale_type (lt_oprn_tbl (i).scale_type);
FETCH lcu_scale_type
INTO ln_scale_type;
CLOSE lcu_scale_type;
IF ln_scale_type IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',SCALE TYPE INVALID ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).scale_type := ln_scale_type;
END IF;
END IF;
/*
IF lt_oprn_tbl (i).plan_type IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',PLAN TYPE IS NULL';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE */
IF lt_oprn_tbl (i).plan_type IS NOT NULL THEN
OPEN lcu_plan_type (lt_oprn_tbl (i).plan_type);
FETCH lcu_plan_type INTO ln_plan_type;
CLOSE lcu_plan_type;
IF ln_plan_type IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',PLAN TYPE IS INVALID ';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
END IF;
ELSE ----
--commented by Srikanth on 25-Apr-2013
--OPEN lcu_plan_type_val(lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers,lt_oprn_tbl (i).activity);--(lt_oprn_tbl (i).transaction_id);
--FETCH lcu_plan_type_val INTO ln_plan_type_val;
--CLOSE lcu_plan_type_val;
--lt_oprn_tbl (i).plan_type := ln_plan_type_val;
if lc_prev_activity = lt_oprn_tbl (i).activity AND lc_prev_oprn = lt_oprn_tbl (i).oprn_no then
lt_oprn_tbl (i).plan_type := 'Auxiliary';
else
lt_oprn_tbl (i).plan_type := 'Primary';
end if;
lc_prev_activity := lt_oprn_tbl (i).activity;
lc_prev_oprn := lt_oprn_tbl (i).oprn_no;
/*
BEGIN
SELECT 'Auxilary'
INTO lt_oprn_tbl (i).plan_type
from dual
where exists ( Select 1 from xxblr_opm_routing_stg
where new_oprn_no = lt_oprn_tbl (i).oprn_no
and oprn_vers = lt_oprn_tbl (i).oprn_vers
and activity = lt_oprn_tbl (i).activity
and plan_type = 'Primary');
EXCEPTION
WHEN OTHERS THEN
lt_oprn_tbl (i).plan_type := 'Primary';
END;
*/
END IF; ----
IF lt_oprn_tbl (i).usage_uom IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',USAGE UOM IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lc_usage_uom := NULL;
OPEN lcu_rers_usage_uom (lt_oprn_tbl (i).usage_uom);
FETCH lcu_rers_usage_uom
INTO lc_usage_uom;
CLOSE lcu_rers_usage_uom;
IF lc_usage_uom IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',USAGE UOM IS INVALID ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).usage_uom := lc_usage_uom;
END IF;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 8');
/*
IF lt_oprn_tbl (i).cost_analysis_code IS NOT NULL THEN
--lt_oprn_tbl (i).record_status := gc_error_flag;
--gc_error_code := '-PAT04';
--gc_error_message :=',COST ANALYSIS CODE IS NULL ';
--lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
--lt_oprn_tbl (i).error_message := gc_error_message;
--ELSE
lc_cost_alys_code := NULL;
OPEN lcu_cost_alys_code(lt_oprn_tbl (i).cost_analysis_code);
FETCH lcu_cost_alys_code
INTO lc_cost_alys_code;
CLOSE lcu_cost_alys_code;
IF lc_cost_alys_code IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message :=',COST ANALYSIS CODE IS INVALID ';
lt_oprn_tbl (i).ERROR_CODE := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
ELSE
lt_oprn_tbl (i).cost_analysis_code :=lc_cost_alys_code;
END IF;
END IF;
*/
lt_oprn_tbl (i).cost_analysis_code := 'DIR';
apps.fnd_file.put_line(apps.fnd_file.LOG,'Track 9:'|| lt_oprn_tbl (i).cost_analysis_code);
IF lt_oprn_tbl (i).resource_usage IS NULL THEN
lt_oprn_tbl (i).record_status := gc_error_flag;
gc_error_code := '-PAT04';
gc_error_message := ',RESOURCE USAGE IS NULL ';
lt_oprn_tbl (i).error_code := gc_error_code;
lt_oprn_tbl (i).error_message := gc_error_message;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:'|| lt_oprn_tbl (i).resource_usage);
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 12:');
--=================================
-- Identifying the success records
--=================================
IF lt_oprn_tbl (i).record_status <> gc_error_flag THEN
lt_oprn_tbl (i).record_status := gc_validation_flag;
lt_oprn_tbl (i).error_message := 'All Validations Passed';
ELSE
lt_oprn_tbl (i).record_status := gc_validation_error_flag;
END IF;
END LOOP;
END IF;
--===================================================================
--Bulk Update the records withe status flag, validation error message
--===================================================================
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:' || 'Debug-10');
apps.fnd_file.put_line (apps.fnd_file.LOG, 'track 11:' || 'debug-11');
ln_bulk_error_cnt := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:' || 'Debug-9');
BEGIN
FORALL i IN lt_oprn_tbl.FIRST .. lt_oprn_tbl.LAST SAVE EXCEPTIONS
UPDATE xxblr_opm_routing_stg
SET record_status = lt_oprn_tbl (i).record_status
, error_message = lt_oprn_tbl (i).error_message
, error_code = lt_oprn_tbl (i).error_code
, oprn_vers = lt_oprn_tbl (i).oprn_vers
, oprn_class = lt_oprn_tbl (i).oprn_class
, activity = lt_oprn_tbl (i).activity
, resource_code = lt_oprn_tbl (i).resource_code
, usage_uom = lt_oprn_tbl (i).usage_uom
, scale_type = lt_oprn_tbl (i).scale_type
, plan_type = lt_oprn_tbl (i).plan_type
, cost_analysis_code = lt_oprn_tbl (i).cost_analysis_code
, process_qty_uom = lt_oprn_tbl (i).process_qty_uom
, organization_id = lt_oprn_tbl (i).organization_id
, last_update_date = gd_current_date
, last_updated_by = gn_user_id
, last_update_login = gn_login_id
WHERE transaction_id = lt_oprn_tbl (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;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:' || 'DebugT-10');
EXIT WHEN lcu_oprn_data%NOTFOUND;
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'track 12:' || 'debug-12');
CLOSE lcu_oprn_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-OPRN :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure VALIDATE_OPRN_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_oprn_data;
