Sri EBS Fusion Techno Functional: Load_data_using

CREATE OR REPLACE PACKAGE XXSRI.SRIPA_BUDGETS_CONV_PKG AUTHID CURRENT_USER

/* SRIPA_BUDGETS_CONV_PKG PACKAGE V1.0*/

-- +=========================================================================================+ --

-- | TEMPO + --

-- +=========================================================================================+ --

-- |$Id: SRIPA_BUDGETS_CONV_PKG | --

-- | | --

-- |This package contains the following sub programs: | --

-- |================================================= | --

-- | | --

-- |Type Name Description | --

-- |========= =========== =================================== | --

-- |PROCEDURE extract_budgets_data Loads budgets data from diffetrent | --

-- | source systems to master table | --

-- | | --

-- |PROCEDURE main Calls below five procedures | --

-- | to stage table | --

-- | 1) pre_validate_budgets_data | --

-- | 2) transform_budgets_data | --

-- | 3) validate_budgets_data | --

-- | 4) load_budgetsdata_stage | --

-- | 5) create_budgets | --

-- | | --

-- |PROCEDURE pre_validate_budgets_data Prevalidates budgets data | --

-- | | --

-- |PROCEDURE transform_budgets_data Applies transformations to budgets data and | --

-- | load's transformed data into transformation | --

-- | table | --

-- | | --

-- |PROCEDURE validate_budgets_data Validates budgets data in transformation table | --

-- | | --

-- |PROCEDURE load_budgetsdata_stage Loads budgets data from transformation table | --

-- | to Stage table | --

-- | | --

-- |PROCEDURE create_budgets Calls api to create budgets | --

-- | | --

-- +=========================================================================================+ --

--=================

-- Global Variables

--=================

gn_request_id NUMBER :=apps.fnd_global.conc_request_id;

gn_prog_appl_id NUMBER :=apps.fnd_global.prog_appl_id;

gn_responsibility_id NUMBER :=apps.fnd_global.resp_id;

gn_respappl_id NUMBER :=apps.fnd_global.resp_appl_id;

gn_program_id NUMBER :=apps.fnd_global.conc_program_id;

gn_user_id NUMBER :=apps.fnd_global.user_id;

gn_login_id NUMBER :=apps.fnd_global.login_id;

gn_business_group_id NUMBER :=apps.fnd_global.per_business_group_id;

gn_org_id NUMBER :=apps.fnd_global.org_id;

gn_bulk_limit NUMBER :=10000;
gn_bulk_error_cnt NUMBER ;

gd_current_date DATE := SYSDATE;

gc_error_flag VARCHAR2(1) := 'E';

gc_success_flag VARCHAR2(1) := 'S';

gc_duplicate_flag VARCHAR2(1) := 'D';

gc_preval_flag VARCHAR2(2) := 'PV';

gc_validation_flag VARCHAR2(1) := 'V';

gc_process_flag VARCHAR2(1) := 'P';

gc_transformation_flag VARCHAR2(1) := 'T';

gc_newrecord_flag VARCHAR2(1) := 'N';

gc_program_name VARCHAR2(100):= 'Budgets Conversion';

gc_error_code SRIpa_budgets_stg.error_code%TYPE:='Error Code -> ';

gc_error_message SRIpa_budgets_stg.error_message%TYPE:='Error Message -> ';

gc_true BOOLEAN := TRUE;

gc_false BOOLEAN := FALSE;

-- |-----------------------------------------------------------------------------------------| --

-- |PROCEDURE : extract_budgets_data | --

-- | | --

-- |DESCRIPTION : Loads Budgets data in SRIPA_Budgets_data from different Source Systems | --

-- |CALLED BY : main | --

-- | | --

-- |PARAMETERS : | --

-- | | --

-- |NAME MODE TYPE DESCRIPTION | --

-- |------------- ---- --------- ---------------------------------------------- | --

-- |ERRBUF OUT VARCHAR2 Returns the error message | --

-- | | --

-- |RETCODE OUT VARCHAR2 Returns the error code | --

-- | | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE extract_budgets_data(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
);

-- |-----------------------------------------------------------------------------------------| --
-- |PROCEDURE : main | --
-- | | --
-- |DESCRIPTION : Loads budgets data from soucrce tables to Master table ,Prevalidates data | --
-- | Transform's the data ,Validates the data and creates budgets | --
-- | CALLS : 1) pre_validate_budgets_data | --
-- | 2) transform_budgets_data | --
-- | 3) validate_budgets_data | --
-- | 4) load_budgetsdata_stage | --
-- | 5) create_budgets | --
-- | | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |ERRBUF OUT VARCHAR2 Returns the error message | --
-- | | --
-- |RETCODE OUT VARCHAR2 Returns the error code | --
-- | | --
-- |P_PREVALIDATE_DATA_FLAG IN VARCHAR2 RPV - Re Pre-Validate | --
-- | Yes - Pre Validate | --
-- | No - No Pre Validation | --
-- | | --
-- |P_TRANSFORM_DATA_FLAG IN VARCHAR2 RT - Re-Transform | --
-- | Yes - Transform | --
-- | No - No Transformation | --
-- | | --
-- |P_VALIDATE_DATA_FLAG IN VARCHAR2 RV - Re-Validate | --
-- | Yes - Validate | --
-- | No - No Validation | --
-- | | --
-- |P_LOAD_DATA_STAGE_FLAG IN VARCHAR2 Yes - then loads data from Trnsf table | --
-- | to stage table | --
-- | No - No Data Load | --
-- | | --
-- |P_CREATE_BUDGET_FLAG IN VARCHAR2 Yes - Create Budgets | --
-- | No - Do not Create Budgets | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE main(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_prevalidate_data_flag IN VARCHAR2,
p_transform_data_flag IN VARCHAR2,
p_validate_data_flag IN VARCHAR2,
p_load_data_stage_flag IN VARCHAR2,
p_create_budget_flag IN VARCHAR2
);

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : pre_validate_budgets_data | --
-- | | --
-- |DESCRIPTION : Applying Pre Validation rules | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |P_PREVALIDATE_DATA_FLAG IN VARCHAR2 RPV - Re Pre-Validate | --
-- | Yes - Pre Validate | --
-- | No - No Pre Validation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE pre_validate_budgets_data(
p_prevalidate_data_flag IN VARCHAR2
);

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : transform_budgets_data | --
-- | | --
-- |DESCRIPTION : Transform budgets data and load into the transform table | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |P_TRANSFORM_DATA_FLAG IN VARCHAR2 RT - Re-Transform | --
-- | Yes - Transform | --
-- | No - No Transformation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE transform_budgets_data(
p_transform_data_flag IN VARCHAR2
);

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : validate_budgets_data | --
-- | | --
-- |DESCRIPTION : Validates data in transform table | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |-------------------- ---- -------------- ---------------------------------------- | --
-- |P_VALIDATE_DATA_FLAG IN VARCHAR2 RV - Re-Validate | --
-- | Yes - Validate | --
-- | No - No Validation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE validate_budgets_data(
p_validate_data_flag IN VARCHAR2
);

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : load_budgetsdata_stage | --
-- | | --
-- |DESCRIPTION : Loads Budgets data in Stage table from transformation | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |---------------------- ---- ---------- ---------------------------------------------- | --
-- |P_LOAD_DATA_STAGE_FLAG IN VARCHAR2 Yes - then loads data from Trnsf table | --
-- | to stage table | --
-- | No - No Data Load | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE load_budgetsdata_stage(
p_load_data_stage_flag IN VARCHAR2
);

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : create_budgets | --
-- | | --
-- |DESCRIPTION : Loads budgets data in stage table from transform | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |---------------------- ---- -------------- ---------------------------------------- | --
-- | | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE create_budgets;

END SRIPA_BUDGETS_CONV_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXSRI.SRIPA_BUDGETS_CONV_PKG
IS
/* SRIPA_BUDGETS_CONV_PKG PACKAGE V1.0*/
-- +=========================================================================================+ --
-- | TEMPO + --
-- +=========================================================================================+ --
-- |$Id: SRIPA_BUDGETS_CONV_PKG | --
-- | | --
-- | | --
-- |This package contains the following sub programs: | --
-- |================================================= | --
-- | | --
-- |Type Name Description | --
-- |========= =========== =================================== | --
-- |PROCEDURE extract_budgets_data Loads budgets data from diffetrent | --
-- | source systems to master table | --
-- | | --
-- |PROCEDURE main Calls below five procedures | --
-- | to stage table | --
-- | 1) pre_validate_budgets_data | --
-- | 2) transform_budgets_data | --
-- | 3) validate_budgets_data | --
-- | 4) load_budgetsdata_stage | --
-- | 5) create_budgets | --
-- | | --
-- |PROCEDURE pre_validate_budgets_data Prevalidates budgets data | --
-- | | --
-- |PROCEDURE transform_budgets_data Applies transformations to budgets data and | --
-- | load's transformed data into transformation | --
-- | table | --
-- | | --
-- |PROCEDURE validate_budgets_data Validates budgets data in transformation table | --
-- | | --
-- |PROCEDURE load_budgetsdata_stage Loads budgets data from transformation table | --
-- | to Stage table | --
-- | | --
-- |PROCEDURE create_budgets Calls api to create budgets | --
-- | | --
-- +=========================================================================================+ --

-- |-----------------------------------------------------------------------------------------| --
-- |PROCEDURE : extract_SRINET_budgetsdata | --
-- | | --
-- |DESCRIPTION : Private procedure to load budgets data in to SRIPA_Budgets_data from SRINET | --
-- | Source system table. | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- --------- ---------------------------------------------- | --
-- |P_SRINET_STATUS OUT VARCHAR2 S - Success | --
-- | E - Error | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE extract_SRINET_budgetsdata(p_SRINET_status OUT NOCOPY VARCHAR2)
IS
--=====================
--Local Variables
--=====================
ln_legacyrec_cnt NUMBER;
ln_loadedrec_cnt NUMBER;
ln_bulk_error_cnt NUMBER;

ld_load_date DATE:=SYSDATE;
ld_extract_date DATE;
ld_current_date DATE:=SYSDATE;

ln_phase1_cnt NUMBER:=0;
ln_phase2_cnt NUMBER:=0;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_SRINET_BUDGETSDATA ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure EXTRACT_SRINET_BUDGETSDATA ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' Number of Records To Be Loaded Into Table :-> '||ln_legacyrec_cnt);

p_SRINET_status := gc_success_flag;

SRI_conv_util_pkg.write_out(' Number of Records Loaded Into Table :-> '||ln_loadedrec_cnt);
SRI_conv_util_pkg.write_out(' Number Of Records Failured :-> '||(ln_legacyrec_cnt-ln_loadedrec_cnt));
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure EXTRACT_SRINET_BUDGETSDATA Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_SRINET_BUDGETSDATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

p_SRINET_status := gc_error_flag;
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure EXTRACT_SRINET_BUDGETSDATA');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_SRINET_BUDGETSDATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END extract_SRINET_budgetsdata;

-- |-----------------------------------------------------------------------------------------| --
-- |PROCEDURE : extract_budgets_data | --
-- | | --
-- |DESCRIPTION : Loads Budgets data in SRIPA_Budgets_data from different Source Systems | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- --------- ---------------------------------------------- | --
-- |ERRBUF OUT VARCHAR2 Returns the error message | --
-- | | --
-- |RETCODE OUT VARCHAR2 Returns the error code | --
-- | | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE extract_budgets_data(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
)
IS

--==========================
--Local Variables Declartion
--==========================

lc_SRINET_status VARCHAR2(30):= gc_success_flag;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure EXTRACT_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));

--===========================================
--Call Procedure Load data from Source table
--where Source system = SRINET
--===========================================
extract_SRINET_budgetsdata(lc_SRINET_status);

IF lc_SRINET_status = gc_success_flag THEN

SRI_conv_util_pkg.write_log(' Data From Source System SRINET Loaded successfully');

ELSE

SRI_conv_util_pkg.write_log(' Data From Source System SRINET Not Loaded successfully');

END IF;

SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure EXTRACT_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure EXTRACT_BUDGETS_DATA');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure EXTRACT_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END extract_budgets_data;

-- |-----------------------------------------------------------------------------------------| --
-- |PROCEDURE : main | --
-- | | --
-- |DESCRIPTION : Loads budgets data from soucrce tables to Master table ,Prevalidates data | --
-- | Transform's the data ,Validates the data and creates budgets | --
-- | CALLS : 1) pre_validate_budgets_data | --
-- | 2) transform_budgets_data | --
-- | 3) validate_budgets_data | --
-- | 4) load_budgetsdata_stage | --
-- | 5) create_budgets | --
-- | | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |ERRBUF OUT VARCHAR2 Returns the error message | --
-- | | --
-- |RETCODE OUT VARCHAR2 Returns the error code | --
-- | | --
-- |P_PREVALIDATE_DATA_FLAG IN VARCHAR2 RPV - Re Pre-Validate | --
-- | Yes - Pre Validate | --
-- | No - No Pre Validation | --
-- | | --
-- |P_TRANSFORM_DATA_FLAG IN VARCHAR2 RT - Re-Transform | --
-- | Yes - Transform | --
-- | No - No Transformation | --
-- | | --
-- |P_VALIDATE_DATA_FLAG IN VARCHAR2 RV - Re-Validate | --
-- | Yes - Validate | --
-- | No - No Validation | --
-- | | --
-- |P_LOAD_DATA_STAGE_FLAG IN VARCHAR2 Yes - then loads data from Trnsf table | --
-- | to stage table | --
-- | No - No Data Load | --
-- | | --
-- |P_CREATE_BUDGET_FLAG IN VARCHAR2 Yes - Create Budgets | --
-- | No - Do not Create Budgets | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE main(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_prevalidate_data_flag IN VARCHAR2,
p_transform_data_flag IN VARCHAR2,
p_validate_data_flag IN VARCHAR2,
p_load_data_stage_flag IN VARCHAR2,
p_create_budget_flag IN VARCHAR2
)
IS
BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure MAIN ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure MAIN ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));

IF p_prevalidate_data_flag = 'Y' THEN

--=======================================================================
--Call Procedure to prevalidate new records in SRIPA_Budgets_Data table
--=======================================================================
pre_validate_budgets_data(gc_newrecord_flag);

ELSIF p_prevalidate_data_flag = 'RPV' THEN

--============================================================================
--Call Procedure to Re prevalidate error records in SRIPA_Budgets_Data table
--============================================================================
pre_validate_budgets_data(gc_error_flag);

END IF;

IF p_transform_data_flag = 'Y' THEN

--====================================================
--Call Procedure to transform prevalidated records in
--SRIPA_Budgets_Data and insert into TRNSF table
--====================================================
transform_budgets_data(gc_preval_flag);

ELSIF p_transform_data_flag = 'RT' THEN

--=================================================
--Call Procedure to Re transform Error records in
--SRIPA_Budgets_Data and insert into TRNSF table
--=================================================
transform_budgets_data(gc_error_flag);

END IF;

IF p_validate_data_flag = 'Y' THEN

--==============================================
--Call Procedure to Validate Data in TRNSF table
--==============================================
validate_budgets_data(gc_transformation_flag);

ELSIF p_validate_data_flag = 'RV' THEN

--==========================================================
--Call Procedure to Re Validate Error Records in TRNSF table
--==========================================================
validate_budgets_data(gc_error_flag);

END IF;

IF p_load_data_stage_flag='Y' THEN

--=============================================================
--Call Procedure to load data from TRNSF table to Stage table
--=============================================================
load_budgetsdata_stage(gc_validation_flag);

END IF;

IF p_create_budget_flag = 'Y' THEN

--==================================
--Call Procedure to Create Budgets
--==================================
create_budgets;

END IF;

SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure MAIN Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure MAIN Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(' Error Occured in The Procedure MAIN');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure MAIN Exit------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END main;

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : pre_validate_budgets_data | --
-- | | --
-- |DESCRIPTION : Applying Pre Validation rules | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |P_PREVALIDATE_DATA_FLAG IN VARCHAR2 RPV - Re Pre-Validate | --
-- | Yes - Pre Validate | --
-- | No - No Pre Validation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE pre_validate_budgets_data(
p_prevalidate_data_flag IN VARCHAR2
)
IS
--=============================
--Local Variables Declaration
--=============================
ln_newrec_cnt NUMBER;
ln_prevalrec_cnt NUMBER;
ln_bulk_error_cnt NUMBER;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure PRE_VALIDATE_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure PRE_VALIDATE_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' Number Of Records to be Pre Validated :-> '||ln_newrec_cnt);

SRI_conv_util_pkg.write_out(' Number Of Pre Validated Records :-> '||ln_prevalrec_cnt);
SRI_conv_util_pkg.write_out(' Number Of Failured Records :-> '||(ln_newrec_cnt-ln_prevalrec_cnt));
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure PRE_VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure PRE_VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured In The Procedure PRE_VALIDATE_BUDGETS_DATA :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure PRE_VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END pre_validate_budgets_data;

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : transform_budgets_data | --
-- | | --
-- |DESCRIPTION : Transform budgets data and load into the transform table | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |----------------------- ---- -------------- ---------------------------------------- | --
-- |P_TRANSFORM_DATA_FLAG IN VARCHAR2 RT - Re-Transform | --
-- | Yes - Transform | --
-- | No - No Transformation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE transform_budgets_data(
p_transform_data_flag IN VARCHAR2
)
IS
--==========================
--Local Variable Declaration
--==========================
ln_prevalrec_cnt NUMBER;
ln_trnsfrec_cnt NUMBER;
ln_bulk_error_cnt NUMBER;

lc_bulk_insert_flag VARCHAR2(30):= gc_success_flag;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure TRANSFORM_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure TRANSFORM_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' Number Of Records to be Transformed :-> '||ln_prevalrec_cnt);

SRI_conv_util_pkg.write_out(' Number Of Transformed Records :-> '||ln_trnsfrec_cnt);
SRI_conv_util_pkg.write_out(' Number Of Failured Records :-> '||(ln_prevalrec_cnt-ln_trnsfrec_cnt));
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure TRANSFORM_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure TRANSFORM_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure TRANSFORM_BUDGETS_DATA');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure TRANSFORM_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END transform_budgets_data;

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : validate_budgets_data | --
-- | | --
-- |DESCRIPTION : Validates data in transform table | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |-------------------- ---- -------------- ---------------------------------------- | --
-- |P_VALIDATE_DATA_FLAG IN VARCHAR2 RV - Re-Validate | --
-- | Yes - Validate | --
-- | No - No Validation | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE validate_budgets_data(
p_validate_data_flag IN VARCHAR2
)
IS
--===========================
--Local Variables Declaration
--===========================
ln_trnsfrec_cnt NUMBER;
ln_prevalrec_cnt NUMBER;
ln_valrec_cnt NUMBER;
ln_bulk_error_cnt NUMBER;

--========================================
--Cursor to get Transformed Record Count
--========================================
CURSOR lcu_get_trnsfrec_cnt(cp_status_flag VARCHAR2)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_trnsf PBT
WHERE PBT.status_flag = cp_status_flag;

--=================================================
--Cursor to get Transformed Records to be Validated
--=================================================
CURSOR lcu_trnsf_data(cp_status_flag VARCHAR2)
IS
SELECT PBT.budget_name
,PBT.period_name
,PBT.project_number
,PBT.project_name
,PBT.task_number
,PBT.expenditure_type
,PBT.currency
,PBT.budget_type
,PBT.budgeted_cost
,PBT.budgeted_revenue
,PBT.primary_key
,gd_current_date creation_date
,gd_current_date last_update_date
,gn_user_id last_updated_by
,gn_user_id created_by
,gn_login_id last_update_login
,gn_request_id request_id
,gn_prog_appl_id program_application_id
,gn_program_id program_id
,gd_current_date program_update_date
,PBT.status_flag
,PBT.record_id
,PBT.error_code
,PBT.error_message
,PBT.budget_id
,PBT.budget_line_id
,PBT.project_id
,PBT.task_id
,PBT.budget_start_date
,PBT.budget_end_date
FROM SRIpa_budgets_trnsf PBT
WHERE PBT.status_flag = cp_status_flag;

--=======================================
--Cursor to get Validated Record Count
--=======================================
CURSOR lcu_get_valrec_cnt(cp_status_flag VARCHAR2,
cp_request_id NUMBER
)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_trnsf PBT
WHERE PBT.status_flag = cp_status_flag
AND PBT.request_id = cp_request_id;

TYPE trnsf_data_tbl_typ IS TABLE OF lcu_trnsf_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_trnsf_data_tbl_typ trnsf_data_tbl_typ;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure VALIDATE_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure VALIDATE_BUDGETS_DATA ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));

--==============================
--Cursor to get New Record Count
--==============================
OPEN lcu_get_trnsfrec_cnt(p_validate_data_flag);
FETCH lcu_get_trnsfrec_cnt INTO ln_trnsfrec_cnt;
CLOSE lcu_get_trnsfrec_cnt;
SRI_conv_util_pkg.write_out(' Number Of Records To Be Pre Validated :-> '||ln_trnsfrec_cnt);

OPEN lcu_trnsf_data(p_validate_data_flag);
LOOP

lt_trnsf_data_tbl_typ.DELETE;

EXIT WHEN lcu_trnsf_data%NOTFOUND;
FETCH lcu_trnsf_data BULK COLLECT INTO lt_trnsf_data_tbl_typ LIMIT gn_bulk_limit;

IF lt_trnsf_data_tbl_typ.COUNT > 0 THEN

FOR i IN 1..lt_trnsf_data_tbl_typ.COUNT
LOOP

lt_trnsf_data_tbl_typ(i).status_flag := gc_validation_flag;

END LOOP;

--=============================
--Set ln_bulk_error_cnt to zero
--=============================
ln_bulk_error_cnt:=0;

--===================
--Bulk update
--===================

BEGIN

FORALL i IN 1..lt_trnsf_data_tbl_typ.COUNT SAVE EXCEPTIONS
UPDATE SRIpa_budgets_trnsf
SET status_flag = lt_trnsf_data_tbl_typ(i).status_flag
,request_id = gn_request_id
,error_code = lt_trnsf_data_tbl_typ(i).error_code
,error_message = lt_trnsf_data_tbl_typ(i).error_message
WHERE record_id = lt_trnsf_data_tbl_typ(i).record_id;

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(' Bulk Update Error Message :-> '||SQLERRM);

ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

FOR i IN 1..ln_bulk_error_cnt
LOOP
SRI_conv_util_pkg.write_log('Error# '||i||' at iteration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
SRI_conv_util_pkg.write_log('Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

ROLLBACK;
RAISE;
END;

END IF;--IF lt_trnsf_data_tbl_typ.COUNT > 0 THEN

END LOOP;
CLOSE lcu_trnsf_data;

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

--=======================================
--Cursor to get Prevalidated Record Count
--=======================================
OPEN lcu_get_valrec_cnt(gc_validation_flag,
gn_request_id
);
FETCH lcu_get_valrec_cnt INTO ln_valrec_cnt;
CLOSE lcu_get_valrec_cnt;

SRI_conv_util_pkg.write_out(' Number Of Validated Records In Transform Table :-> '||ln_valrec_cnt);
SRI_conv_util_pkg.write_out(' Number Of Failured Records In Transform Table :-> '||(ln_trnsfrec_cnt-ln_valrec_cnt));
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure VALIDATE_BUDGETS_DATA');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure VALIDATE_BUDGETS_DATA Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END validate_budgets_data;

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : load_budgetsdata_stage | --
-- | | --
-- |DESCRIPTION : Loads Budgets data in Stage table from transformation | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |---------------------- ---- ---------- ---------------------------------------------- | --
-- |P_LOAD_DATA_STAGE_FLAG IN VARCHAR2 Yes - then loads data from Trnsf table | --
-- | to stage table | --
-- | No - No Data Load | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE load_budgetsdata_stage(
p_load_data_stage_flag IN VARCHAR2
)
IS
--=====================
--Local Variables
--=====================
ln_valrec_cnt NUMBER;
ln_loadedrec_cnt NUMBER;
ln_bulk_error_cnt NUMBER;

lc_bulk_insert_flag VARCHAR2(30):= gc_success_flag;

--===================================================
--Cursor to get validated record count in TRNSF table
--===================================================
CURSOR lcu_get_valrec_cnt(cp_status_flag VARCHAR2)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_trnsf PBS
WHERE PBS.status_flag = cp_status_flag;

--==========================================================
--Cursor to get validated data to be Loaded Into Stage Table
--==========================================================
CURSOR lcu_validated_data(cp_status_flag VARCHAR2)
IS
SELECT PBT.budget_name
,PBT.period_name
,PBT.project_number
,PBT.project_name
,PBT.task_number
,PBT.expenditure_type
,PBT.currency
,PBT.budget_type
,PBT.budgeted_cost
,PBT.budgeted_revenue
,PBT.primary_key
,gd_current_date creation_date
,gd_current_date last_update_date
,gn_user_id last_updated_by
,gn_user_id created_by
,gn_login_id last_update_login
,gn_request_id request_id
,gn_prog_appl_id program_application_id
,gn_program_id program_id
,gd_current_date program_update_date
,PBT.status_flag
,PBT.record_id
,PBT.error_code
,PBT.error_message
,PBT.budget_id
,PBT.budget_line_id
,PBT.project_id
,PBT.task_id
,PBT.budget_start_date
,PBT.budget_end_date
FROM SRIpa_budgets_trnsf PBT
WHERE PBT.status_flag=cp_status_flag;

--=================================
--Cursor to get loaded record count
--=================================
CURSOR lcu_get_loadedrec_cnt(cp_status_flag VARCHAR2,
cp_request_id NUMBER
)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_stg PBS
WHERE PBS.status_flag = cp_status_flag
AND PBS.request_id = cp_request_id;

TYPE validated_data_tbl_typ IS TABLE OF lcu_validated_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_validated_data_tbl_typ validated_data_tbl_typ;


BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure LOAD_BUDGETSDATA_STAGE ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure LOAD_BUDGETSDATA_STAGE ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));

--======================================
--Cursor to get transformed record count
--======================================
OPEN lcu_get_valrec_cnt(p_load_data_stage_flag);
FETCH lcu_get_valrec_cnt INTO ln_valrec_cnt;
CLOSE lcu_get_valrec_cnt;
SRI_conv_util_pkg.write_out(' Number of Validated Records To Be Loaded :-> '||ln_valrec_cnt);

--=====================================================
--Cursor to get validated data to load into stage table
--=====================================================
OPEN lcu_validated_data(p_load_data_stage_flag);
LOOP

lt_validated_data_tbl_typ.DELETE;

EXIT WHEN lcu_validated_data%NOTFOUND;
FETCH lcu_validated_data BULK COLLECT INTO lt_validated_data_tbl_typ LIMIT gn_bulk_limit;

--=============================
--Set ln_bulk_error_cnt to zero
--=============================
ln_bulk_error_cnt :=0;

BEGIN

--===========
--Bulk Insert
--===========
FORALL i IN 1..lt_validated_data_tbl_typ.COUNT SAVE EXCEPTIONS
INSERT INTO xxcnv.SRIpa_budgets_stg VALUES lt_validated_data_tbl_typ(i);

EXCEPTION
WHEN OTHERS THEN
lc_bulk_insert_flag:= gc_error_flag;
SRI_conv_util_pkg.write_log(' Bulk Error Count :-> '||ln_bulk_error_cnt);
SRI_conv_util_pkg.write_log(' Bulk Insert Error Message :-> '||SQLERRM);

ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..ln_bulk_error_cnt
LOOP
SRI_conv_util_pkg.write_log('Error# '||i||' at iteration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
SRI_conv_util_pkg.write_log('Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
ROLLBACK;
--RAISE;
END;

END LOOP;
CLOSE lcu_validated_data;

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
--=================================
--Cursor to get loaded record count
--=================================
OPEN lcu_get_loadedrec_cnt(gc_validation_flag,
gn_request_id
);
FETCH lcu_get_loadedrec_cnt INTO ln_loadedrec_cnt;
CLOSE lcu_get_loadedrec_cnt;

SRI_conv_util_pkg.write_out(' Number of Records Loaded Into Table :-> '||ln_loadedrec_cnt);
SRI_conv_util_pkg.write_out(' Number Of Records Failured :-> '||(ln_valrec_cnt-ln_loadedrec_cnt));

COMMIT;
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
--======================================
--Update TRNSF Table status as processed
--======================================
IF ln_loadedrec_cnt > 0 THEN

BEGIN

UPDATE SRIpa_budgets_trnsf PBT
SET PBT.status_flag=gc_process_flag
WHERE EXISTS (SELECT 1
FROM SRIpa_budgets_stg PBS
WHERE PBS.record_id = PBT.record_id
AND PBS.status_flag = gc_validation_flag
AND PBS.request_id = gn_request_id
);
EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Exception Occured While Updating TRNSF Table record status as Processed');
SRI_conv_util_pkg.write_log(' Error Message :-> '||SQLERRM);

END;

END IF;

SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure LOAD_BUDGETSDATA_STAGE Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure LOAD_BUDGETSDATA_STAGE Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure LOAD_BUDGETSDATA_STAGE');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure LOAD_BUDGETSDATA_STAGE Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END load_budgetsdata_stage;

-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : create_budgets | --
-- | | --
-- |DESCRIPTION : Loads budgets data in stage table from transform | --
-- |CALLED BY : main | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |---------------------- ---- -------------- ---------------------------------------- | --
-- | | --
-- |-----------------------------------------------------------------------------------------+ --

PROCEDURE create_budgets
IS

--===========================
--Local Variables Declaration
--===========================
ln_validatedrec_cnt NUMBER;
ln_processedrec_cnt NUMBER;

lc_api_error_code VARCHAR2(30) := 'Error Code Is :-> ';
lc_api_error_message VARCHAR2(30) := 'Error Message Is :-> ';

--==========================
--Local Variable Declaration
--==========================
ln_valrec_cnt NUMBER;
ln_processrec_cnt NUMBER;

--=================================
--Variables Used In Set Global Info
--=================================
ln_msg_count2 NUMBER;
lc_msg_data2 VARCHAR2(2000);
lc_return_status2 VARCHAR2(200);

ln_api_version_number NUMBER :=1.0;
ln_msg_count NUMBER;
ln_msg_index_out NUMBER;
ln_msg_count1 NUMBER;
ln_pa_project_id NUMBER;
ln_resource_list_id NUMBER;
ln_bulk_error_cnt NUMBER;

lc_pm_product_code VARCHAR2(10);
lc_pm_project_reference VARCHAR2(25);
lc_budget_type_code VARCHAR2(30);
lc_version_name apps.pa_budget_versions.version_name%TYPE;
lc_change_reason_code VARCHAR2(30);
lc_description VARCHAR2(255);
lc_entry_method_code VARCHAR2(30);
lc_resource_list_name VARCHAR2(60);
lc_commit VARCHAR2(1):= 'F';
lc_return_status VARCHAR2(1);
lc_msg_data VARCHAR2(2000);
lc_data VARCHAR2(2000);

lt_budget_lines_in apps.pa_budget_pub.budget_line_in_tbl_type;
lr_budget_lines_in_rec apps.pa_budget_pub.budget_line_in_rec_type;
lt_budget_lines_out apps.pa_budget_pub.budget_line_out_tbl_type;

--=====================================
--Cursor to get Validated Records Count
--=====================================
CURSOR lcu_get_validatedrec_cnt(cp_status_flag VARCHAR2)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_stg PBS
WHERE PBS.status_flag=cp_status_flag;

--============================================
--Cursor to get validated data to be processed
--============================================
CURSOR lcu_validated_data(cp_status_flag VARCHAR2)
IS
SELECT PBS.budget_name
,PBS.period_name
,PBS.project_number
,PBS.project_name
,PBS.task_number
,PBS.expenditure_type
,PBS.currency
,PBS.budget_type
,PBS.budgeted_cost
,PBS.budgeted_revenue
,PBS.primary_key
,gd_current_date creation_date
,gd_current_date last_update_date
,gn_user_id last_updated_by
,gn_user_id created_by
,gn_login_id last_update_login
,gn_request_id request_id
,gn_prog_appl_id program_application_id
,gn_program_id program_id
,gd_current_date program_update_date
,PBS.status_flag
,PBS.record_id
,PBS.error_code
,PBS.error_message
,PBS.budget_id
,PBS.budget_line_id
,PBS.project_id
,PBS.task_id
,PBS.budget_start_date
,PBS.budget_end_date
FROM SRIpa_budgets_stg PBS
WHERE PBS.status_flag=cp_status_flag;

--=====================================
--Cursor to get Processed Records Count
--=====================================
CURSOR lcu_get_processedrec_cnt(cp_status_flag IN VARCHAR2)
IS
SELECT COUNT(1)
FROM SRIpa_budgets_stg PBS
WHERE PBS.status_flag=cp_status_flag;

TYPE validated_data_tbl_typ IS TABLE OF lcu_validated_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_validated_data_tbl_typ validated_data_tbl_typ;

BEGIN

SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_log(' ------ Procedure CREATE_BUDGETS ------');
SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));

SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));
SRI_conv_util_pkg.write_out(' ------ Procedure CREATE_BUDGETS ------');
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
--=========================
-- set global info
--=========================
BEGIN

apps.pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0
,p_responsibility_id => gn_responsibility_id
,p_user_id => gn_user_id
,p_operating_unit_id => gn_org_id
,p_msg_count => ln_msg_count
,p_msg_data => lc_msg_data
,p_return_status => lc_return_status
);
END;


IF lc_return_status <> gc_success_flag THEN

SRI_conv_util_pkg.write_log(' Set Global Info API Status :-> '||lc_return_status);
SRI_conv_util_pkg.write_log(' So , Terminating the program');

RETURN;

ELSE

SRI_conv_util_pkg.write_log(' Set Global Info API Status :-> '||lc_return_status);

END IF;

lc_return_status := NULL;

--=====================================
--Cursor to get Validated Records Count
--=====================================
OPEN lcu_get_validatedrec_cnt(gc_validation_flag);
FETCH lcu_get_validatedrec_cnt INTO ln_validatedrec_cnt;
CLOSE lcu_get_validatedrec_cnt;
SRI_conv_util_pkg.write_out(' Number of Validated Records :-> '||ln_validatedrec_cnt);

--============================================
--Cursor to get validated data to be processed
--============================================
OPEN lcu_validated_data(gc_validation_flag);
LOOP

lt_validated_data_tbl_typ.DELETE;
EXIT WHEN lcu_validated_data%NOTFOUND;
FETCH lcu_validated_data BULK COLLECT INTO lt_validated_data_tbl_typ LIMIT gn_bulk_limit;

IF lt_validated_data_tbl_typ.COUNT > 0 THEN

FOR i in 1..lt_validated_data_tbl_typ.COUNT
LOOP

SRI_conv_util_pkg.write_log(' Record Id :-> '||lt_validated_data_tbl_typ(i).record_id);

--==========================
--Variables Initialization
--=========================
ln_msg_count :=NULL;
ln_msg_index_out :=NULL;
ln_msg_count1 :=NULL;
ln_pa_project_id :=NULL;
ln_resource_list_id :=NULL;
lc_pm_product_code :=NULL;
lc_pm_project_reference :=NULL;
lc_budget_type_code :=NULL;
lc_version_name :=NULL;
lc_change_reason_code :=NULL;
lc_description :=NULL;
lc_entry_method_code :=NULL;
lc_resource_list_name :=NULL;
--lc_commit :=NUL
lc_return_status :=NULL;
lc_msg_data :=NULL;
lc_data :=NULL;

--===========
--budget data
--===========
lc_pm_product_code := 'MSPROJECT';
ln_pa_project_id := lt_validated_data_tbl_typ(i).project_id;
lc_pm_project_reference := NULL;
lc_budget_type_code := 'AC';
lc_change_reason_code := NULL;
lc_description := NULL;
lc_version_name := 'Budget created as part of Data Migration';
lc_entry_method_code := 'PA_LOWEST_TASK_BY_DATE_RANGE';
ln_resource_list_id := 1041;
lc_resource_list_name := NULL;

lt_budget_lines_in.DELETE;

lr_budget_lines_in_rec.pa_task_id := lt_validated_data_tbl_typ(i).task_id;
lr_budget_lines_in_rec.resource_list_member_id := 2077;
--lr_budget_lines_in_rec.quantity := 94; -- is not mandatory
lr_budget_lines_in_rec.budget_start_date := lt_validated_data_tbl_typ(i).budget_start_date;
lr_budget_lines_in_rec.budget_end_date := lt_validated_data_tbl_typ(i).budget_end_date;
lr_budget_lines_in_rec.revenue := lt_validated_data_tbl_typ(i).budgeted_revenue;
lr_budget_lines_in_rec.raw_cost := lt_validated_data_tbl_typ(i).budgeted_cost;
lt_budget_lines_in(i) := lr_budget_lines_in_rec;

lt_validated_data_tbl_typ(i).error_code := lc_api_error_code;
lt_validated_data_tbl_typ(i).error_message := lc_api_error_message;

--===============
--Intiate Budget
--===============
apps.pa_budget_pub.init_budget;

--====================
--Call Budget API
--====================
BEGIN

apps.pa_budget_pub.create_draft_budget( p_api_version_number => ln_api_version_number
,p_msg_count => ln_msg_count
,p_msg_data => lc_msg_data
,p_return_status => lc_return_status
,p_pm_product_code => lc_pm_product_code
,p_pa_project_id => ln_pa_project_id
,p_pm_project_reference => lc_pm_project_reference
,p_budget_type_code => lc_budget_type_code
,p_change_reason_code => lc_change_reason_code
,p_budget_version_name => lc_version_name
,p_description => lc_description
,p_entry_method_code => lc_entry_method_code
,p_resource_list_name => lc_resource_list_name
,p_resource_list_id => ln_resource_list_id
,p_budget_lines_in => lt_budget_lines_in
,p_budget_lines_out => lt_budget_lines_out
);

IF lc_return_status<>'S' THEN

SRI_conv_util_pkg.write_log(' API Return Status :-> '||lc_return_status);
FOR i IN 1..ln_msg_count
LOOP
apps.pa_interface_utils_pub.get_messages(p_msg_data => lc_msg_data
,p_encoded => 'F'
,p_msg_index => ln_msg_count -- 11i.PJ_PF.I
,p_data => lc_data
,p_msg_count => ln_msg_count1
,p_msg_index_out => ln_msg_index_out
);

SRI_conv_util_pkg.write_log(' API Error Message :-> '||lc_data);
SRI_conv_util_pkg.write_log(' API Error Message 0 :-> '||ln_msg_count);
SRI_conv_util_pkg.write_log(' API Error Message 1 :-> '||ln_msg_count1);
--SRI_conv_util_pkg.write_log('lc_msg_index_out : '||ln_msg_index_out);
--SRI_conv_util_pkg.write_log('lc_msg_data : '||lc_msg_data);

lt_validated_data_tbl_typ(i).error_message:=lt_validated_data_tbl_typ(i).error_code||' , '||lc_data;

END LOOP;

lt_validated_data_tbl_typ(i).status_flag:=gc_error_flag;
lt_validated_data_tbl_typ(i).error_code:=lt_validated_data_tbl_typ(i).error_code||' , '||'PAM04PERR001';--API Error Occured

ELSE
SRI_conv_util_pkg.write_log(' Budget Successfully Created');
lt_validated_data_tbl_typ(i).status_flag:=gc_process_flag;

END IF;
EXCEPTION
WHEN OTHERS THEN

lt_validated_data_tbl_typ(i).status_flag:=gc_error_flag;
SRI_conv_util_pkg.write_log(' Others Exception Occured In API Block :-> '||SQLERRM);
lt_validated_data_tbl_typ(i).error_code:='PAM04PERR0000';
lt_validated_data_tbl_typ(i).error_message:=SQLERRM;
ROLLBACK;
RAISE;

END;

END LOOP;

--=============================
--Set ln_bulk_error_cnt to zero
--=============================
ln_bulk_error_cnt :=0;

--===================================================================
--Bulk Update the records with status flag, validation error message
--===================================================================
BEGIN

FORALL i IN 1..lt_validated_data_tbl_typ.COUNT SAVE EXCEPTIONS
UPDATE SRIpa_budgets_stg
SET budget_id = lt_validated_data_tbl_typ(i).budget_id
,status_flag = lt_validated_data_tbl_typ(i).status_flag
,request_id = gn_request_id
,error_code = lt_validated_data_tbl_typ(i).error_code
,error_message = lt_validated_data_tbl_typ(i).error_message
WHERE record_id = lt_validated_data_tbl_typ(i).record_id;
--COMMIT;

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log('Error in procedure CREATE_BUDGETS while Bulk Update - '||SQLERRM);

ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

FOR i IN 1..ln_bulk_error_cnt
LOOP
SRI_conv_util_pkg.write_log('Error# '||i||' at iteration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
SRI_conv_util_pkg.write_log('Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

ROLLBACK;
--RAISE;

END;

END IF;

END LOOP;
CLOSE lcu_validated_data;

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
--=====================================
--Cursor to get Prccessed Records Count
--=====================================
OPEN lcu_get_processedrec_cnt(gc_process_flag);
FETCH lcu_get_processedrec_cnt INTO ln_processedrec_cnt;
CLOSE lcu_get_processedrec_cnt;

SRI_conv_util_pkg.write_out(' Number of Records Successfully Processed :-> '||ln_processedrec_cnt);
SRI_conv_util_pkg.write_out(' Number of Failured records :-> '||(ln_validatedrec_cnt-ln_processedrec_cnt));
SRI_conv_util_pkg.write_out(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_out(' ------ Procedure CREATE_BUDGETS Exit------');
SRI_conv_util_pkg.write_out(RPAD('*',80,'*'));

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' ------ Procedure CREATE_BUDGETS Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS THEN

SRI_conv_util_pkg.write_log(RPAD(' ',80,' '));
SRI_conv_util_pkg.write_log(' Error Occured in The Procedure CREATE_BUDGETS');
SRI_conv_util_pkg.write_log(' Error Message Is :-> '||SQLERRM);
SRI_conv_util_pkg.write_log(' ------ Procedure CREATE_BUDGETS Exit------');
SRI_conv_util_pkg.write_log(RPAD('*',80,'*'));

END create_budgets;
END SRIPA_BUDGETS_CONV_PKG;

Sri EBS Fusion Techno Functional

Load_data_using_API

0 comments:

Post a Comment

Total Pageviews

Categories