Apps2CloudFusion: July 2023

FBDI Load Validation Scripts in Custom DB

---------------------------------------------------------

DROP SEQUENCE xxdm_fa_mass_add_src1_s;

CREATE SEQUENCE xxdm_fa_mass_add_src1_s MINVALUE 1 MAXVALUE 9999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

DROP TABLE xxdm_fa_mass_add_src1;

CREATE TABLE xxdm_fa_mass_add_src1 (

assetbook VARCHAR2(240),

description VARCHAR2(4000),

entity VARCHAR2(240),

location VARCHAR2(240),

asset_location_segment1 VARCHAR2(240),

asset_location_segment2 VARCHAR2(240),

depr_expense_account_segment5 VARCHAR2(240),

category VARCHAR2(240),

year NUMBER,

date_of_purchase DATE,

cost NUMBER,

dep_rate NUMBER,

dep_type VARCHAR2(240),

month_dep_charge NUMBER,

dep_ytd NUMBER,

commulative_deprn NUMBER,

net_book_value NUMBER,

life_rem_in_months NUMBER,

life_in_months NUMBER,

asset_id NUMBER,

asset_number VARCHAR2(240),

depr_expense_account_segment1 VARCHAR2(240),

depr_expense_account_segment2 VARCHAR2(240),

MAJOR_ASSET_CATEGORY VARCHAR2(240),

MINOR_ASSET_CATEGORY VARCHAR2(240),

dm_process_flag VARCHAR2(240),

dm_comments VARCHAR2(4000),

asset_cnv_id NUMBER DEFAULT xxdm_fa_mass_add_src1_s.NEXTVAL,

status_flag NUMBER DEFAULT 1 NOT NULL,

process_message VARCHAR2(4000),

creation_date_time DATE DEFAULT sysdate NOT NULL,

creation_user VARCHAR2(128) DEFAULT user NOT NULL,

update_date_time DATE DEFAULT sysdate NOT NULL,

update_user VARCHAR2(128) DEFAULT user NOT NULL

);

--Create package Spec

CREATE OR REPLACE PACKAGE xxdm_fa_mass_add_load_to_fbdi AS

PROCEDURE p_load_fa_mass_add;

PROCEDURE p_load_fa_mass_add_dist;

PROCEDURE p_Validate(i_Revalidate_Flag IN VARCHAR2);

PROCEDURE p_Main_Proc(i_Import_Type IN VARCHAR2);

END xxdm_fa_mass_add_load_to_fbdi;

--Create package Body

CREATE OR REPLACE PACKAGE BODY xxdm_fa_mass_add_load_to_fbdi AS

TYPE FA_Asset_Tbl_Type

TABLE OF XXDM_FA_MASS_ADD_SRC1%ROWTYPE INDEX BY BINARY_INTEGER;

Gt_FA_Asset FA_Asset_Tbl_Type;

TYPE FA_Asset_Recid_Tbl_Type

TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Gt_FA_Asset_Recid FA_Asset_Recid_Tbl_Type;

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

-- PROCEDURE: To Update the staging table with process result.

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

PROCEDURE Update_Process_Detail

Ln_Error_Count NUMBER := 0;

BEGIN

Dbms_Output.Put_Line('In Update call in Validations Procedure.');

FORALL i IN 1 .. Gt_FA_Asset.count SAVE EXCEPTIONS

UPDATE XXDM_FA_MASS_ADD_SRC1

SET ROW = Gt_FA_Asset(i)

WHERE asset_cnv_id = Gt_FA_Asset_Recid(i);

Ln_Error_Count := SQL%Bulk_Exceptions.Count;

IF Ln_Error_Count <> 0 THEN

FOR i IN 1 .. Ln_Error_Count

LOOP

Dbms_Output.Put_Line( 'message here. :' || REPLACE(SUBSTR('Error occur in UPDATE_PROCESS_DETAIL procedure. Error message : ' || SQLERRM(-sql%BULK_EXCEPTIONS(i) .Error_Code), 1, 400), Chr(10), ' '));

END LOOP;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

Dbms_Output.Put_Line('Error in Update Process Detail. :' || SQLERRM);

RAISE;

END Update_Process_Detail;

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

-- PROCEDURE: To transfer data using api

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

PROCEDURE p_Insert_Into_Interface

CURSOR Lcu_FA_Asset

SELECT *

FROM XXDM_FA_MASS_ADD_SRC1 Acs

WHERE 1 = 1

AND Status_Flag = 2;

v_Commit VARCHAR2(5);

v_Validation_Level NUMBER;

v_Init_Msg_Lst VARCHAR2(500);

v_Valid BOOLEAN;

v_Process_Message VARCHAR2(4000);

v_Commit_Limit NUMBER := 10000;

x_Bulk_Errors EXCEPTION;

PRAGMA EXCEPTION_INIT(x_Bulk_Errors, -24381);

v_Insert_Count INTEGER;

BEGIN

OPEN Lcu_FA_Asset;

LOOP

v_Valid:=true;

Gt_FA_Asset.Delete;

Gt_FA_Asset_Recid.Delete;

FETCH Lcu_FA_Asset BULK COLLECT INTO Gt_FA_Asset LIMIT v_Commit_Limit;

BEGIN

FORALL Ln_i_Index IN Gt_FA_Asset.First .. Gt_FA_Asset.Last SAVE EXCEPTIONS

INSERT INTO xxdm_fa_mass_add_fbdi (

mass_addition_id,

book_type_code,

transaction_name,

asset_number,

description,

tag_number,

manufacturer_name,

serial_number,

model_number,

asset_type,

fixed_assets_cost,

date_placed_in_service,

prorate_convention_code,

fixed_assets_units,

category_segment1,

category_segment2,

category_segment3,

category_segment4,

category_segment5,

category_segment6,

category_segment7,

posting_status,

queue_name,

feeder_system_name,

parent_asset_number,

add_to_asset_number,

asset_key_segment1,

asset_key_segment2,

asset_key_segment3,

asset_key_segment4,

asset_key_segment5,

asset_key_segment6,

asset_key_segment7,

asset_key_segment8,

asset_key_segment9,

asset_key_segment10,

inventorial,

property_type_code,

property_1245_1250_code,

in_use_flag,

owned_leased,

new_used,

material_indicator_flag,

commitment,

investment_law,

amortize_flag,

amortization_start_date,

depreciate_flag,

salvage_type,

salvage_value,

percent_salvage_value,

ytd_deprn,

deprn_reserve,

bonus_ytd_deprn,

bonus_deprn_reserve,

ytd_impairment,

impairment_reserve,

method_code,

life_in_months,

basic_rate,

adjusted_rate,

unit_of_measure,

production_capacity,

ceiling_name,

bonus_rule,

cash_generating_unit,

deprn_limit_type,

allowed_deprn_limit,

allowed_deprn_limit_amount,

payables_cost,

clearing_acct_segment1,

clearing_acct_segment2,

clearing_acct_segment3,

clearing_acct_segment4,

clearing_acct_segment5,

clearing_acct_segment6,

clearing_acct_segment7,

clearing_acct_segment8,

clearing_acct_segment9,

clearing_acct_segment10,

clearing_acct_segment11,

clearing_acct_segment12,

clearing_acct_segment13,

clearing_acct_segment14,

clearing_acct_segment15,

clearing_acct_segment16,

clearing_acct_segment17,

clearing_acct_segment18,

clearing_acct_segment19,

clearing_acct_segment20,

clearing_acct_segment21,

clearing_acct_segment22,

clearing_acct_segment23,

clearing_acct_segment24,

clearing_acct_segment25,

clearing_acct_segment26,

clearing_acct_segment27,

clearing_acct_segment28,

clearing_acct_segment29,

clearing_acct_segment30,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

attribute6,

attribute7,

attribute8,

attribute9,

attribute10,

attribute11,

attribute12,

attribute13,

attribute14,

attribute15,

attribute16,

attribute17,

attribute18,

attribute19,

attribute20,

attribute21,

attribute22,

attribute23,

attribute24,

attribute25,

attribute26,

attribute27,

attribute28,

attribute29,

attribute30,

attribute_number1,

attribute_number2,

attribute_number3,

attribute_number4,

attribute_number5,

attribute_date1,

attribute_date2,

attribute_date3,

attribute_date4,

attribute_date5,

attribute_category_code,

context,

th_attribute1,

th_attribute2,

th_attribute3,

th_attribute4,

th_attribute5,

th_attribute6,

th_attribute7,

th_attribute8,

th_attribute9,

th_attribute10,

th_attribute11,

th_attribute12,

th_attribute13,

th_attribute14,

th_attribute15,

th_attribute_number1,

th_attribute_number2,

th_attribute_number3,

th_attribute_number4,

th_attribute_number5,

th_attribute_date1,

th_attribute_date2,

th_attribute_date3,

th_attribute_date4,

th_attribute_date5,

th_attribute_category_code,

th2_attribute1,

th2_attribute2,

th2_attribute3,

th2_attribute4,

th2_attribute5,

th2_attribute6,

th2_attribute7,

th2_attribute8,

th2_attribute9,

th2_attribute10,

th2_attribute11,

th2_attribute12,

th2_attribute13,

th2_attribute14,

th2_attribute15,

th2_attribute_number1,

th2_attribute_number2,

th2_attribute_number3,

th2_attribute_number4,

th2_attribute_number5,

th2_attribute_date1,

th2_attribute_date2,

th2_attribute_date3,

th2_attribute_date4,

th2_attribute_date5,

th2_attribute_category_code,

ai_attribute1,

ai_attribute2,

ai_attribute3,

ai_attribute4,

ai_attribute5,

ai_attribute6,

ai_attribute7,

ai_attribute8,

ai_attribute9,

ai_attribute10,

ai_attribute11,

ai_attribute12,

ai_attribute13,

ai_attribute14,

ai_attribute15,

ai_attribute_number1,

ai_attribute_number2,

ai_attribute_number3,

ai_attribute_number4,

ai_attribute_number5,

ai_attribute_date1,

ai_attribute_date2,

ai_attribute_date3,

ai_attribute_date4,

ai_attribute_date5,

ai_attribute_category_code,

mass_property_flag,

group_asset_number,

reduction_rate,

reduce_addition_flag,

reduce_adjustment_flag,

reduce_retirement_flag,

recognize_gain_loss,

recapture_reserve_flag,

limit_proceeds_flag,

terminal_gain_loss,

tracking_method,

excess_allocation_option,

depreciation_option,

member_rollup_flag,

allocate_to_fully_rsv_flag,

over_depreciate_option,

preparer_email_address,

merged_code,

parent_mass_addition_id,

sum_units,

new_master_flag,

units_to_adjust,

short_fiscal_year_flag,

conversion_date,

original_deprn_start_date,

global_attribute1,

global_attribute2,

global_attribute3,

global_attribute4,

global_attribute5,

global_attribute6,

global_attribute7,

global_attribute8,

global_attribute9,

global_attribute10,

global_attribute11,

global_attribute12,

global_attribute13,

global_attribute14,

global_attribute15,

global_attribute16,

global_attribute17,

global_attribute18,

global_attribute19,

global_attribute20,

global_attribute_number1,

global_attribute_number2,

global_attribute_number3,

global_attribute_number4,

global_attribute_number5,

global_attribute_date1,

global_attribute_date2,

global_attribute_date3,

global_attribute_date4,

global_attribute_date5,

global_attribute_category,

nbv_at_switch,

period_name_fully_reserved,

period_name_extended,

prior_deprn_limit_type,

prior_deprn_limit,

prior_deprn_limit_amount,

prior_method_code,

prior_life_in_months,

prior_basic_rate,

prior_adjusted_rate,

asset_schedule_num,

lease_number,

reval_reserve,

reval_loss_balance,

reval_amortization_basis,

impair_loss_balance,

reval_ceiling,

fair_market_value,

last_price_index_value,

global_attribute_number6,

global_attribute_number7,

global_attribute_number8,

global_attribute_number9,

global_attribute_number10,

global_attribute_date6,

global_attribute_date7,

global_attribute_date8,

global_attribute_date9,

global_attribute_date10,

bk_global_attribute1,

bk_global_attribute2,

bk_global_attribute3,

bk_global_attribute4,

bk_global_attribute5,

bk_global_attribute6,

bk_global_attribute7,

bk_global_attribute8,

bk_global_attribute9,

bk_global_attribute10,

bk_global_attribute11,

bk_global_attribute12,

bk_global_attribute13,

bk_global_attribute14,

bk_global_attribute15,

bk_global_attribute16,

bk_global_attribute17,

bk_global_attribute18,

bk_global_attribute19,

bk_global_attribute20,

bk_global_attribute_number1,

bk_global_attribute_number2,

bk_global_attribute_number3,

bk_global_attribute_number4,

bk_global_attribute_number5,

bk_global_attribute_date1,

bk_global_attribute_date2,

bk_global_attribute_date3,

bk_global_attribute_date4,

bk_global_attribute_date5,

bk_global_attribute_category,

th_global_attribute1,

th_global_attribute2,

th_global_attribute3,

th_global_attribute4,

th_global_attribute5,

th_global_attribute6,

th_global_attribute7,

th_global_attribute8,

th_global_attribute9,

th_global_attribute10,

th_global_attribute11,

th_global_attribute12,

th_global_attribute13,

th_global_attribute14,

th_global_attribute15,

th_global_attribute16,

th_global_attribute17,

th_global_attribute18,

th_global_attribute19,

th_global_attribute20,

th_global_attribute_number1,

th_global_attribute_number2,

th_global_attribute_number3,

th_global_attribute_number4,

th_global_attribute_number5,

th_global_attribute_date1,

th_global_attribute_date2,

th_global_attribute_date3,

th_global_attribute_date4,

th_global_attribute_date5,

th_global_attribute_category,

ai_global_attribute1,

ai_global_attribute2,

ai_global_attribute3,

ai_global_attribute4,

ai_global_attribute5,

ai_global_attribute6,

ai_global_attribute7,

ai_global_attribute8,

ai_global_attribute9,

ai_global_attribute10,

ai_global_attribute11,

ai_global_attribute12,

ai_global_attribute13,

ai_global_attribute14,

ai_global_attribute15,

ai_global_attribute16,

ai_global_attribute17,

ai_global_attribute18,

ai_global_attribute19,

ai_global_attribute20,

ai_global_attribute_number1,

ai_global_attribute_number2,

ai_global_attribute_number3,

ai_global_attribute_number4,

ai_global_attribute_number5,

ai_global_attribute_date1,

ai_global_attribute_date2,

ai_global_attribute_date3,

ai_global_attribute_date4,

ai_global_attribute_date5,

ai_global_attribute_category,

vendor_name,

vendor_number,

po_number,

invoice_number,

invoice_voucher_number,

invoice_date,

payables_units,

invoice_line_number,

invoice_line_type,

invoice_line_description,

invoice_payment_number,

project_number,

project_task_number,

fully_reserve_on_add_flag,

deprn_adjustment_factor,

revalued_cost,

backlog_deprn_reserve,

ytd_backlog_deprn,

reval_amort_balance,

ytd_reval_amortization,

split_merged_code

)

VALUES

(

Gt_FA_Asset(Ln_i_Index).asset_id ,

Gt_FA_Asset(Ln_i_Index).assetbook ,

'Data Migration' ,

XXDM_FA_ASSET_NUMBER_S.nextval ,

substr(

Gt_FA_Asset(Ln_i_Index).description,

) ,

null ,

'CAPITALIZED' ,

Gt_FA_Asset(Ln_i_Index).cost ,

to_char(

Gt_FA_Asset(Ln_i_Index).date_of_purchase,

'YYYY/MM/DD'

) ,

'CurrentMnt' ,

1 ,

Gt_FA_Asset(Ln_i_Index).MAJOR_ASSET_CATEGORY,

Gt_FA_Asset(Ln_i_Index).MINOR_ASSET_CATEGORY,

NULL ,

'POST' ,

'IQS' ,

NULL ,

null ,

NULL ,

null ,

NULL ,

'NO' ,

'2023/06/01' , --amortization_start_date

'YES' , --depreciate_flag

null ,

Gt_FA_Asset(Ln_i_Index).dep_ytd ,

Gt_FA_Asset(Ln_i_Index).COMMULATIVE_DEPRN ,

NULL ,

'STL' ,

Gt_FA_Asset(Ln_i_Index).life_in_months,

null ,

NULL ,

null ,

NULL ,

NULL

);

EXCEPTION

WHEN x_Bulk_Errors THEN

FOR Indx IN 1 .. SQL%Bulk_Exceptions.Count

LOOP

Dbms_Output.Put_Line('Asset ID: ' || Gt_FA_Asset(SQL%BULK_EXCEPTIONS(Indx).Error_Index) .Asset_id);

Dbms_Output.Put_Line('Error...: ' || Indx || ' Array Index: ' || SQL%BULK_EXCEPTIONS(Indx) .Error_Index || ' Message: ' || SQLERRM(-sql%BULK_EXCEPTIONS(Indx).Error_Code));

END LOOP;

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := 'Error While inserting into Interface Table 1:-' || SQLERRM;

Dbms_Output.Put_Line(v_Process_Message);

END;

BEGIN

FORALL Ln_i_Index IN Gt_FA_Asset.First .. Gt_FA_Asset.Last SAVE EXCEPTIONS

INSERT INTO xxdm_fa_mass_add_dist_fbdi (

mass_addition_id,

units,

employee_email_address,

location_segment1,

location_segment2,

location_segment3,

location_segment4,

location_segment5,

location_segment6,

location_segment7,

deprn_expense_segment1,

deprn_expense_segment2,

deprn_expense_segment3,

deprn_expense_segment4,

deprn_expense_segment5,

deprn_expense_segment6,

deprn_expense_segment7,

deprn_expense_segment8,

deprn_expense_segment9,

deprn_expense_segment10,

deprn_expense_segment11,

deprn_expense_segment12,

deprn_expense_segment13,

deprn_expense_segment14,

deprn_expense_segment15,

deprn_expense_segment16,

deprn_expense_segment17,

deprn_expense_segment18,

deprn_expense_segment19,

deprn_expense_segment20,

deprn_expense_segment21,

deprn_expense_segment22,

deprn_expense_segment23,

deprn_expense_segment24,

deprn_expense_segment25,

deprn_expense_segment26,

deprn_expense_segment27,

deprn_expense_segment28,

deprn_expense_segment29,

deprn_expense_segment30

)

VALUES

(Gt_FA_Asset(Ln_i_Index).asset_id ,

1 ,

NULL ,

Gt_FA_Asset(Ln_i_Index).Asset_location_segment1,

Gt_FA_Asset(Ln_i_Index).Asset_location_segment2,

NULL ,

Gt_FA_Asset(Ln_i_Index).DEPR_EXPENSE_ACCOUNT_SEGMENT1,

Gt_FA_Asset(Ln_i_Index).DEPR_EXPENSE_ACCOUNT_SEGMENT2,

'0000' ,

'400' ,

Gt_FA_Asset(Ln_i_Index).DEPR_EXPENSE_ACCOUNT_SEGMENT5 ,

'0000' ,

'00000000' ,

NULL ,

NULL

);

COMMIT;

EXCEPTION

WHEN x_Bulk_Errors THEN

FOR Indx IN 1 .. SQL%Bulk_Exceptions.Count

LOOP

Dbms_Output.Put_Line('Asset ID: ' || Gt_FA_Asset(SQL%BULK_EXCEPTIONS(Indx).Error_Index) .Asset_id);

Dbms_Output.Put_Line('Error...: ' || Indx || ' Array Index: ' || SQL%BULK_EXCEPTIONS(Indx) .Error_Index || ' Message: ' || SQLERRM(-sql%BULK_EXCEPTIONS(Indx).Error_Code));

END LOOP;

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := 'Error While inserting into Interface Table:-' || SQLERRM;

Dbms_Output.Put_Line(v_Process_Message);

END;

COMMIT;

IF v_Valid THEN

FORALL Ln_i_Index IN Gt_FA_Asset.First .. Gt_FA_Asset.Last

UPDATE XXDM_FA_MASS_ADD_SRC1 Imc

SET Imc.Status_Flag = 5,

iMC.Process_Message ='Interfaced '

WHERE 1 =1

AND Imc.asset_cnv_id = Gt_FA_Asset(Ln_i_Index) .asset_cnv_id;

ELSE

FORALL Ln_i_Index IN Gt_FA_Asset.First .. Gt_FA_Asset.Last

UPDATE XXDM_FA_MASS_ADD_SRC1 Imc

SET Imc.Status_Flag = 6,

iMC.Process_Message ='Error while inserting into Interface '

WHERE 1 =1

AND Imc.asset_cnv_id = Gt_FA_Asset(Ln_i_Index) .asset_cnv_id;

END IF;

EXIT

WHEN Lcu_FA_Asset%NOTFOUND;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

Dbms_Output.Put_Line(' Error in p_Insert_Into_Interface procedure - ' || SQLERRM);

RAISE;

END p_Insert_Into_Interface;

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

-- PROCEDURE: To Validate the Transaction data.

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

PROCEDURE p_Validate(

i_Revalidate_Flag IN VARCHAR2)

v_Commit_Limit NUMBER := 10000;

CURSOR Lcu_FA_Asset(Status_Flag IN NUMBER)

SELECT *

FROM XXDM_FA_MASS_ADD_SRC1 Acs

WHERE 1 = 1

AND Status_Flag IN (1, Status_Flag)

;

Ln_Process_Status NUMBER;

v_Valid BOOLEAN;

v_Process_Message VARCHAR2(4000);

Ln_FA_Asset_Cnt NUMBER;

Ln_Trx_Count NUMBER;

lc_minor_asset_category VARCHAR2(240);

LN_CNT_DEP_METHODS NUMBER;

BEGIN

Dbms_Output.Put_Line('In Validations Procedure.');

IF i_Revalidate_Flag = 'Y' THEN

Ln_Process_Status := 3;

ELSE

Ln_Process_Status := 1;

END IF;

update xxdm_fa_mass_add_src1 xfmas

set

asset_id = '1000' || rownum;

commit;

lc_minor_asset_category:='GENERAL';

OPEN Lcu_FA_Asset(Ln_Process_Status);

LOOP

--Dbms_Output.Put_Line('In outer loop Validations Procedure.');

Gt_FA_Asset.Delete;

Gt_FA_Asset_Recid.Delete;

FETCH Lcu_FA_Asset BULK COLLECT INTO Gt_FA_Asset LIMIT v_Commit_Limit;

FOR Ln_i_Index IN 1 .. Gt_FA_Asset.Count

LOOP

Dbms_Output.Put_Line('In inner loop of Validations Procedure.');

v_Valid := TRUE;

v_Process_Message := NULL;

Ln_FA_Asset_Cnt := NULL;

LN_CNT_DEP_METHODS := 0;

Gt_FA_Asset_Recid(Ln_i_Index) := Gt_FA_Asset(Ln_i_Index) .asset_cnv_id;

Gt_FA_Asset(Ln_i_Index).minor_asset_category:=lc_minor_asset_category;

-- Validate not null columns

IF Gt_FA_Asset(Ln_i_Index).ASSETBOOK IS NULL THEN

v_Process_Message := v_Process_Message || ':Asset Book is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).description IS NULL THEN

v_Process_Message := v_Process_Message || ':Description is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).cost IS NULL THEN

v_Process_Message := v_Process_Message || ':Cost is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).Category IS NULL THEN

v_Process_Message := v_Process_Message || ':Asset Category1 is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).Depr_Expense_Account_Segment5 IS NULL THEN

v_Process_Message := v_Process_Message || ':Depreciation Expense Account Segment5 is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).DATE_OF_PURCHASE IS NULL THEN

v_Process_Message := v_Process_Message || ':DATE_OF_PURCHASE is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).Asset_Location_Segment1 IS NULL THEN

v_Process_Message := v_Process_Message || ':Asset Location Segment1 is mandatory';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).DEPR_EXPENSE_ACCOUNT_SEGMENT5 is not null and LENGTH(Gt_FA_Asset(Ln_i_Index).DEPR_EXPENSE_ACCOUNT_SEGMENT5)<>6 THEN

v_Process_Message := v_Process_Message || ':DEPR_EXPENSE_ACCOUNT_SEGMENT5 is greater than 6 digits';

v_Valid := FALSE;

END IF;

IF Gt_FA_Asset(Ln_i_Index).entity IS NULL THEN

v_Process_Message := v_Process_Message || ':Entity is mandatory';

v_Valid := FALSE;

END IF;

-- Derive Customer Number

IF Gt_FA_Asset(Ln_i_Index).Category IS NOT NULL THEN

BEGIN

SELECT major_asset_category

INTO Gt_FA_Asset(Ln_i_Index).major_asset_category

FROM XXDM_FA_ASSET_CAT_SEG_TT

WHERE 1 = 1

AND major_asset_category = UPPER(Gt_FA_Asset(Ln_i_Index) .Category)

and MINOR_ASSET_CATEGORY=lc_minor_asset_category

;

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Major Asset Category ' || UPPER(Gt_FA_Asset(Ln_i_Index) .Category) || ' is not present in fusion';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Major Asset Category ' || UPPER(Gt_FA_Asset(Ln_i_Index) .Category) || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Derive Location Segment1

IF Gt_FA_Asset(Ln_i_Index).location IS NOT NULL THEN

BEGIN

SELECT country

INTO Gt_FA_Asset(Ln_i_Index).Asset_location_segment1

FROM XXDM_FA_LOC_SEG_TT

WHERE Upper(country) = Upper(Gt_FA_Asset(Ln_i_Index).location)

;

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Asset Location Segment1 ' || Gt_FA_Asset(Ln_i_Index).Asset_location_segment1 || ' is not present in Fusion';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Asset Location Segment1 ' || Gt_FA_Asset(Ln_i_Index).Asset_location_segment1 || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Derive Location Segment2

IF Gt_FA_Asset(Ln_i_Index).location IS NOT NULL THEN

BEGIN

SELECT building

INTO Gt_FA_Asset(Ln_i_Index).Asset_location_segment2

FROM XXDM_FA_LOC_SEG_TT

WHERE Upper(building) = Upper(Gt_FA_Asset(Ln_i_Index).Asset_location_segment2)

;

EXCEPTION

WHEN No_Data_Found THEN

BEGIN

SELECT building

INTO Gt_FA_Asset(Ln_i_Index).Asset_location_segment2

FROM XXDM_FA_LOC_SEG_TT

WHERE Upper(country) = Upper(Gt_FA_Asset(Ln_i_Index).location)

;

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Asset Location Segment2 ' || Gt_FA_Asset(Ln_i_Index).Asset_location_segment2 || ' is not present in Fusion';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Asset Location Segment2 ' || Gt_FA_Asset(Ln_i_Index).Asset_location_segment2 || ' validation exception - ' || SQLERRM;

END;

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Asset Location Segment2 ' || Gt_FA_Asset(Ln_i_Index).Asset_location_segment2 || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Validate Deprn_expense_segment1

IF Gt_FA_Asset(Ln_i_Index).entity IS NOT NULL THEN

BEGIN

SELECT segment1

INTO Gt_FA_Asset(Ln_i_Index).depr_expense_account_segment1

FROM XXDM_FA_ENTI_SEG1_SEG2_TT

WHERE Upper(entity) = Upper(Gt_FA_Asset(Ln_i_Index).entity);

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment1 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' is not present';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment1 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Validate Deprn_expense_segment2

IF Gt_FA_Asset(Ln_i_Index).entity IS NOT NULL THEN

BEGIN

SELECT segment2

INTO Gt_FA_Asset(Ln_i_Index).depr_expense_account_segment2

FROM XXDM_FA_ENTI_SEG1_SEG2_TT

WHERE Upper(entity) = Upper(Gt_FA_Asset(Ln_i_Index).entity);

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment2 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' is not present';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment2 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Validate Deprn_expense_segment2

IF Gt_FA_Asset(Ln_i_Index).entity IS NOT NULL THEN

BEGIN

SELECT segment2

INTO Gt_FA_Asset(Ln_i_Index).depr_expense_account_segment2

FROM XXDM_FA_ENTI_SEG1_SEG2_TT

WHERE Upper(entity) = Upper(Gt_FA_Asset(Ln_i_Index).entity);

EXCEPTION

WHEN No_Data_Found THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment2 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' is not present';

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Deprn_expense_segment2 for the entity ' || Gt_FA_Asset(Ln_i_Index) .entity || ' validation exception - ' || SQLERRM;

END;

END IF;

-- Validate Deprn_expense_segment2

IF Gt_FA_Asset(Ln_i_Index).life_in_months IS NOT NULL THEN

BEGIN

SELECT COUNT(*)

INTO LN_CNT_DEP_METHODS

FROM

xxdm_fa_mass_add_src1 xfmas

WHERE

NOT EXISTS (

SELECT

FROM

xxdm_dep_method_tt xdm

WHERE

xdm.method_code = 'STL'

AND xdm.life_in_months = trunc(xfmas.life_in_months)

)

AND asset_cnv_id=Gt_FA_Asset(Ln_i_Index).asset_cnv_id;

IF LN_CNT_DEP_METHODS<>0 THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Dep Method is not defined in fusion ';

END IF ;

EXCEPTION

WHEN OTHERS THEN

v_Valid := FALSE;

v_Process_Message := v_Process_Message || ':Error in validating Dep Method is not defined in fusion -validation exception - ' || SQLERRM;

END;

END IF;

-- End of Derivations

IF v_Valid THEN

-- Dbms_Output.Put_Line('Record is Valid.');

v_Process_Message := 'VALID';

Gt_FA_Asset(Ln_i_Index).Status_Flag := 2;

Gt_FA_Asset(Ln_i_Index).Process_Message := v_Process_Message;

ELSE

--Dbms_Output.Put_Line('Record is Valid.');

Gt_FA_Asset(Ln_i_Index).Status_Flag := 3;

Gt_FA_Asset(Ln_i_Index).Process_Message := 'INVALID - ' || v_Process_Message;

END IF;

IF (Ln_i_Index MOD v_Commit_Limit) = 0 THEN

Dbms_Output.Put_Line('Before Update call in Validations Procedure.=0');

Update_Process_Detail;

END IF;

END LOOP;

COMMIT;

EXIT

WHEN Lcu_FA_Asset%NOTFOUND;

END LOOP;

CLOSE Lcu_FA_Asset;

IF (Gt_FA_Asset.Count MOD v_Commit_Limit) > 0 THEN

Dbms_Output.Put_Line('Before Update call in Validations Procedure.> 0');

Update_Process_Detail;

END IF;

EXCEPTION

WHEN OTHERS THEN

Dbms_Output.Put_Line(' Error in validation procedure - ' || SQLERRM);

RAISE;

END p_Validate;

PROCEDURE p_load_fa_mass_add IS

BEGIN

--derive asset number

/*upload asset_id*/

update xxdm_fa_mass_add_src xfmas

set

asset_id = '1000' || rownum;

commit;

/*dep method Validation --*/

UPDATE xxdm_fa_mass_add_src xfmas

SET

dm_comments = 'Dep Method is not defined in fusion ',

dm_process_flag = 'E'

WHERE

NOT EXISTS (

SELECT

FROM

xxdm_dep_method_tt xdm

WHERE

xdm.method_code = 'STL'

AND xdm.life_in_months = xfmas.life_in_months

)

AND xfmas.dm_process_flag <> 'E';

INSERT INTO xxdm_fa_mass_add_fbdi (

mass_addition_id,

book_type_code,

transaction_name,

asset_number,

description,

tag_number,

manufacturer_name,

serial_number,

model_number,

asset_type,

fixed_assets_cost,

date_placed_in_service,

prorate_convention_code,

fixed_assets_units,

category_segment1,

category_segment2,

category_segment3,

category_segment4,

category_segment5,

category_segment6,

category_segment7,

posting_status,

queue_name,

feeder_system_name,

parent_asset_number,

add_to_asset_number,

asset_key_segment1,

asset_key_segment2,

asset_key_segment3,

asset_key_segment4,

asset_key_segment5,

asset_key_segment6,

asset_key_segment7,

asset_key_segment8,

asset_key_segment9,

asset_key_segment10,

inventorial,

property_type_code,

property_1245_1250_code,

in_use_flag,

owned_leased,

new_used,

material_indicator_flag,

commitment,

investment_law,

amortize_flag,

amortization_start_date,

depreciate_flag,

salvage_type,

salvage_value,

percent_salvage_value,

ytd_deprn,

deprn_reserve,

bonus_ytd_deprn,

bonus_deprn_reserve,

ytd_impairment,

impairment_reserve,

method_code,

life_in_months,

basic_rate,

adjusted_rate,

unit_of_measure,

production_capacity,

ceiling_name,

bonus_rule,

cash_generating_unit,

deprn_limit_type,

allowed_deprn_limit,

allowed_deprn_limit_amount,

payables_cost,

clearing_acct_segment1,

clearing_acct_segment2,

clearing_acct_segment3,

clearing_acct_segment4,

clearing_acct_segment5,

clearing_acct_segment6,

clearing_acct_segment7,

clearing_acct_segment8,

clearing_acct_segment9,

clearing_acct_segment10,

clearing_acct_segment11,

clearing_acct_segment12,

clearing_acct_segment13,

clearing_acct_segment14,

clearing_acct_segment15,

clearing_acct_segment16,

clearing_acct_segment17,

clearing_acct_segment18,

clearing_acct_segment19,

clearing_acct_segment20,

clearing_acct_segment21,

clearing_acct_segment22,

clearing_acct_segment23,

clearing_acct_segment24,

clearing_acct_segment25,

clearing_acct_segment26,

clearing_acct_segment27,

clearing_acct_segment28,

clearing_acct_segment29,

clearing_acct_segment30,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

attribute6,

attribute7,

attribute8,

attribute9,

attribute10,

attribute11,

attribute12,

attribute13,

attribute14,

attribute15,

attribute16,

attribute17,

attribute18,

attribute19,

attribute20,

attribute21,

attribute22,

attribute23,

attribute24,

attribute25,

attribute26,

attribute27,

attribute28,

attribute29,

attribute30,

attribute_number1,

attribute_number2,

attribute_number3,

attribute_number4,

attribute_number5,

attribute_date1,

attribute_date2,

attribute_date3,

attribute_date4,

attribute_date5,

attribute_category_code,

context,

th_attribute1,

th_attribute2,

th_attribute3,

th_attribute4,

th_attribute5,

th_attribute6,

th_attribute7,

th_attribute8,

th_attribute9,

th_attribute10,

th_attribute11,

th_attribute12,

th_attribute13,

th_attribute14,

th_attribute15,

th_attribute_number1,

th_attribute_number2,

th_attribute_number3,

th_attribute_number4,

th_attribute_number5,

th_attribute_date1,

th_attribute_date2,

th_attribute_date3,

th_attribute_date4,

th_attribute_date5,

th_attribute_category_code,

th2_attribute1,

th2_attribute2,

th2_attribute3,

th2_attribute4,

th2_attribute5,

th2_attribute6,

th2_attribute7,

th2_attribute8,

th2_attribute9,

th2_attribute10,

th2_attribute11,

th2_attribute12,

th2_attribute13,

th2_attribute14,

th2_attribute15,

th2_attribute_number1,

th2_attribute_number2,

th2_attribute_number3,

th2_attribute_number4,

th2_attribute_number5,

th2_attribute_date1,

th2_attribute_date2,

th2_attribute_date3,

th2_attribute_date4,

th2_attribute_date5,

th2_attribute_category_code,

ai_attribute1,

ai_attribute2,

ai_attribute3,

ai_attribute4,

ai_attribute5,

ai_attribute6,

ai_attribute7,

ai_attribute8,

ai_attribute9,

ai_attribute10,

ai_attribute11,

ai_attribute12,

ai_attribute13,

ai_attribute14,

ai_attribute15,

ai_attribute_number1,

ai_attribute_number2,

ai_attribute_number3,

ai_attribute_number4,

ai_attribute_number5,

ai_attribute_date1,

ai_attribute_date2,

ai_attribute_date3,

ai_attribute_date4,

ai_attribute_date5,

ai_attribute_category_code,

mass_property_flag,

group_asset_number,

reduction_rate,

reduce_addition_flag,

reduce_adjustment_flag,

reduce_retirement_flag,

recognize_gain_loss,

recapture_reserve_flag,

limit_proceeds_flag,

terminal_gain_loss,

tracking_method,

excess_allocation_option,

depreciation_option,

member_rollup_flag,

allocate_to_fully_rsv_flag,

over_depreciate_option,

preparer_email_address,

merged_code,

parent_mass_addition_id,

sum_units,

new_master_flag,

units_to_adjust,

short_fiscal_year_flag,

conversion_date,

original_deprn_start_date,

global_attribute1,

global_attribute2,

global_attribute3,

global_attribute4,

global_attribute5,

global_attribute6,

global_attribute7,

global_attribute8,

global_attribute9,

global_attribute10,

global_attribute11,

global_attribute12,

global_attribute13,

global_attribute14,

global_attribute15,

global_attribute16,

global_attribute17,

global_attribute18,

global_attribute19,

global_attribute20,

global_attribute_number1,

global_attribute_number2,

global_attribute_number3,

global_attribute_number4,

global_attribute_number5,

global_attribute_date1,

global_attribute_date2,

global_attribute_date3,

global_attribute_date4,

global_attribute_date5,

global_attribute_category,

nbv_at_switch,

period_name_fully_reserved,

period_name_extended,

prior_deprn_limit_type,

prior_deprn_limit,

prior_deprn_limit_amount,

prior_method_code,

prior_life_in_months,

prior_basic_rate,

prior_adjusted_rate,

asset_schedule_num,

lease_number,

reval_reserve,

reval_loss_balance,

reval_amortization_basis,

impair_loss_balance,

reval_ceiling,

fair_market_value,

last_price_index_value,

global_attribute_number6,

global_attribute_number7,

global_attribute_number8,

global_attribute_number9,

global_attribute_number10,

global_attribute_date6,

global_attribute_date7,

global_attribute_date8,

global_attribute_date9,

global_attribute_date10,

bk_global_attribute1,

bk_global_attribute2,

bk_global_attribute3,

bk_global_attribute4,

bk_global_attribute5,

bk_global_attribute6,

bk_global_attribute7,

bk_global_attribute8,

bk_global_attribute9,

bk_global_attribute10,

bk_global_attribute11,

bk_global_attribute12,

bk_global_attribute13,

bk_global_attribute14,

bk_global_attribute15,

bk_global_attribute16,

bk_global_attribute17,

bk_global_attribute18,

bk_global_attribute19,

bk_global_attribute20,

bk_global_attribute_number1,

bk_global_attribute_number2,

bk_global_attribute_number3,

bk_global_attribute_number4,

bk_global_attribute_number5,

bk_global_attribute_date1,

bk_global_attribute_date2,

bk_global_attribute_date3,

bk_global_attribute_date4,

bk_global_attribute_date5,

bk_global_attribute_category,

th_global_attribute1,

th_global_attribute2,

th_global_attribute3,

th_global_attribute4,

th_global_attribute5,

th_global_attribute6,

th_global_attribute7,

th_global_attribute8,

th_global_attribute9,

th_global_attribute10,

th_global_attribute11,

th_global_attribute12,

th_global_attribute13,

th_global_attribute14,

th_global_attribute15,

th_global_attribute16,

th_global_attribute17,

th_global_attribute18,

th_global_attribute19,

th_global_attribute20,

th_global_attribute_number1,

th_global_attribute_number2,

th_global_attribute_number3,

th_global_attribute_number4,

th_global_attribute_number5,

th_global_attribute_date1,

th_global_attribute_date2,

th_global_attribute_date3,

th_global_attribute_date4,

th_global_attribute_date5,

th_global_attribute_category,

ai_global_attribute1,

ai_global_attribute2,

ai_global_attribute3,

ai_global_attribute4,

ai_global_attribute5,

ai_global_attribute6,

ai_global_attribute7,

ai_global_attribute8,

ai_global_attribute9,

ai_global_attribute10,

ai_global_attribute11,

ai_global_attribute12,

ai_global_attribute13,

ai_global_attribute14,

ai_global_attribute15,

ai_global_attribute16,

ai_global_attribute17,

ai_global_attribute18,

ai_global_attribute19,

ai_global_attribute20,

ai_global_attribute_number1,

ai_global_attribute_number2,

ai_global_attribute_number3,

ai_global_attribute_number4,

ai_global_attribute_number5,

ai_global_attribute_date1,

ai_global_attribute_date2,

ai_global_attribute_date3,

ai_global_attribute_date4,

ai_global_attribute_date5,

ai_global_attribute_category,

vendor_name,

vendor_number,

po_number,

invoice_number,

invoice_voucher_number,

invoice_date,

payables_units,

invoice_line_number,

invoice_line_type,

invoice_line_description,

invoice_payment_number,

project_number,

project_task_number,

fully_reserve_on_add_flag,

deprn_adjustment_factor,

revalued_cost,

backlog_deprn_reserve,

ytd_backlog_deprn,

reval_amort_balance,

ytd_reval_amortization,

split_merged_code

)

SELECT

xfmas.asset_id mass_addition_id,

assetbook book_type_code,

'Data Migration' transaction_name,

XXDM_FA_ASSET_NUMBER_S.nextval asset_number,

substr(

xfmas.description,

) description,

null tag_number,

null manufacturer_name,

null serial_number,

null model_number,

'CAPITALIZED' asset_type,

xfmas.cost fixed_assets_cost,

to_char(

date_of_purchase,

'YYYY/MM/DD'

) date_placed_in_service,

'CurrentMnt' prorate_convention_code,

1 fixed_assets_units,

(select MAJOR_ASSET_CATEGORY

from XXDM_FA_ASSET_CAT_SEG_TT

where MAJOR_ASSET_CATEGORY=upper(xfmas.category) and MINOR_ASSET_CATEGORY='GENERAL'

) category_segment1,

'GENERAL' category_segment2,

NULL category_segment3,

NULL category_segment4,

NULL category_segment5,

NULL category_segment6,

NULL category_segment7,

'POST' posting_status,

'POST' queue_name,

'IQS' feeder_system_name,

NULL parent_asset_number,

NULL add_to_asset_number,

NULL asset_key_segment1,

NULL asset_key_segment2,

NULL asset_key_segment3,

NULL asset_key_segment4,

NULL asset_key_segment5,

NULL asset_key_segment6,

NULL asset_key_segment7,

NULL asset_key_segment8,

NULL asset_key_segment9,

NULL asset_key_segment10,

null inventorial,

null property_type_code,

NULL property_1245_1250_code,

null in_use_flag,

null owned_leased,

null new_used,

NULL material_indicator_flag,

NULL commitment,

NULL investment_law,

'NO' amortize_flag,

'2023/06/01' amortization_start_date,

'YES' depreciate_flag,

null salvage_type,

null salvage_value,

null percent_salvage_value,

xfmas.dep_ytd ytd_deprn,

xfmas.COMMULATIVE_DEPRN deprn_reserve,--need to check mapping with sai

NULL bonus_ytd_deprn,

NULL bonus_deprn_reserve,

NULL ytd_impairment,

NULL impairment_reserve,

'STL' method_code,

xfmas.life_in_months,

null basic_rate,

null adjusted_rate,

NULL unit_of_measure,

NULL production_capacity,

NULL ceiling_name,

NULL bonus_rule,

NULL cash_generating_unit,

null deprn_limit_type,

null allowed_deprn_limit,

null allowed_deprn_limit_amount,

NULL payables_cost,

NULL clearing_acct_segment1,

NULL clearing_acct_segment2,

NULL clearing_acct_segment3,

NULL clearing_acct_segment4,

NULL clearing_acct_segment5,

NULL clearing_acct_segment6,

NULL clearing_acct_segment7,

NULL clearing_acct_segment8,

NULL clearing_acct_segment9,

NULL clearing_acct_segment10,

NULL clearing_acct_segment11,

NULL clearing_acct_segment12,

NULL clearing_acct_segment13,

NULL clearing_acct_segment14,

NULL clearing_acct_segment15,

NULL clearing_acct_segment16,

NULL clearing_acct_segment17,

NULL clearing_acct_segment18,

NULL clearing_acct_segment19,

NULL clearing_acct_segment20,

NULL clearing_acct_segment21,

NULL clearing_acct_segment22,

NULL clearing_acct_segment23,

NULL clearing_acct_segment24,

NULL clearing_acct_segment25,

NULL clearing_acct_segment26,

NULL clearing_acct_segment27,

NULL clearing_acct_segment28,

NULL clearing_acct_segment29,

NULL clearing_acct_segment30,

NULL attribute1,

NULL attribute2,

NULL attribute3,

NULL attribute4,

NULL attribute5,

NULL attribute6,

NULL attribute7,

NULL attribute8,

NULL attribute9,

NULL attribute10,

NULL attribute11,

NULL attribute12,

NULL attribute13,

NULL attribute14,

NULL attribute15,

NULL attribute16,

NULL attribute17,

NULL attribute18,

NULL attribute19,

NULL attribute20,

NULL attribute21,

NULL attribute22,

NULL attribute23,

NULL attribute24,

NULL attribute25,

NULL attribute26,

NULL attribute27,

NULL attribute28,

NULL attribute29,

NULL attribute30,

NULL attribute_number1,

NULL attribute_number2,

NULL attribute_number3,

NULL attribute_number4,

NULL attribute_number5,

NULL attribute_date1,

NULL attribute_date2,

NULL attribute_date3,

NULL attribute_date4,

NULL attribute_date5,

NULL attribute_category_code,

NULL context,

NULL th_attribute1,

NULL th_attribute2,

NULL th_attribute3,

NULL th_attribute4,

NULL th_attribute5,

NULL th_attribute6,

NULL th_attribute7,

NULL th_attribute8,

NULL th_attribute9,

NULL th_attribute10,

NULL th_attribute11,

NULL th_attribute12,

NULL th_attribute13,

NULL th_attribute14,

NULL th_attribute15,

NULL th_attribute_number1,

NULL th_attribute_number2,

NULL th_attribute_number3,

NULL th_attribute_number4,

NULL th_attribute_number5,

NULL th_attribute_date1,

NULL th_attribute_date2,

NULL th_attribute_date3,

NULL th_attribute_date4,

NULL th_attribute_date5,

NULL th_attribute_category_code,

NULL th2_attribute1,

NULL th2_attribute2,

NULL th2_attribute3,

NULL th2_attribute4,

NULL th2_attribute5,

NULL th2_attribute6,

NULL th2_attribute7,

NULL th2_attribute8,

NULL th2_attribute9,

NULL th2_attribute10,

NULL th2_attribute11,

NULL th2_attribute12,

NULL th2_attribute13,

NULL th2_attribute14,

NULL th2_attribute15,

NULL th2_attribute_number1,

NULL th2_attribute_number2,

NULL th2_attribute_number3,

NULL th2_attribute_number4,

NULL th2_attribute_number5,

NULL th2_attribute_date1,

NULL th2_attribute_date2,

NULL th2_attribute_date3,

NULL th2_attribute_date4,

NULL th2_attribute_date5,

NULL th2_attribute_category_code,

NULL ai_attribute1,

NULL ai_attribute2,

NULL ai_attribute3,

NULL ai_attribute4,

NULL ai_attribute5,

NULL ai_attribute6,

NULL ai_attribute7,

NULL ai_attribute8,

NULL ai_attribute9,

NULL ai_attribute10,

NULL ai_attribute11,

NULL ai_attribute12,

NULL ai_attribute13,

NULL ai_attribute14,

NULL ai_attribute15,

NULL ai_attribute_number1,

NULL ai_attribute_number2,

NULL ai_attribute_number3,

NULL ai_attribute_number4,

NULL ai_attribute_number5,

NULL ai_attribute_date1,

NULL ai_attribute_date2,

NULL ai_attribute_date3,

NULL ai_attribute_date4,

NULL ai_attribute_date5,

NULL ai_attribute_category_code,

NULL mass_property_flag,

NULL group_asset_number,

NULL reduction_rate,

NULL reduce_addition_flag,

NULL reduce_adjustment_flag,

NULL reduce_retirement_flag,

NULL recognize_gain_loss,

NULL recapture_reserve_flag,

NULL limit_proceeds_flag,

NULL terminal_gain_loss,

NULL tracking_method,

NULL excess_allocation_option,

NULL depreciation_option,

NULL member_rollup_flag,

NULL allocate_to_fully_rsv_flag,

NULL over_depreciate_option,

NULL preparer_email_address,

NULL merged_code,

NULL parent_mass_addition_id,

NULL sum_units,

NULL new_master_flag,

NULL units_to_adjust,

NULL short_fiscal_year_flag,

NULL conversion_date,

NULL original_deprn_start_date,

NULL global_attribute1,

NULL global_attribute2,

NULL global_attribute3,

NULL global_attribute4,

NULL global_attribute5,

NULL global_attribute6,

NULL global_attribute7,

NULL global_attribute8,

NULL global_attribute9,

NULL global_attribute10,

NULL global_attribute11,

NULL global_attribute12,

NULL global_attribute13,

NULL global_attribute14,

NULL global_attribute15,

NULL global_attribute16,

NULL global_attribute17,

NULL global_attribute18,

NULL global_attribute19,

NULL global_attribute20,

NULL global_attribute_number1,

NULL global_attribute_number2,

NULL global_attribute_number3,

NULL global_attribute_number4,

NULL global_attribute_number5,

NULL global_attribute_date1,

NULL global_attribute_date2,

NULL global_attribute_date3,

NULL global_attribute_date4,

NULL global_attribute_date5,

NULL global_attribute_category,

NULL nbv_at_switch,

NULL period_name_fully_reserved,

NULL period_name_extended,

NULL prior_deprn_limit_type,

NULL prior_deprn_limit,

NULL prior_deprn_limit_amount,

NULL prior_method_code,

NULL prior_life_in_months,

NULL prior_basic_rate,

NULL prior_adjusted_rate,

NULL asset_schedule_num,

NULL lease_number,

NULL reval_reserve,

NULL reval_loss_balance,

NULL reval_amortization_basis,

NULL impair_loss_balance,

NULL reval_ceiling,

NULL fair_market_value,

NULL last_price_index_value,

NULL global_attribute_number6,

NULL global_attribute_number7,

NULL global_attribute_number8,

NULL global_attribute_number9,

NULL global_attribute_number10,

NULL global_attribute_date6,

NULL global_attribute_date7,

NULL global_attribute_date8,

NULL global_attribute_date9,

NULL global_attribute_date10,

NULL bk_global_attribute1,

NULL bk_global_attribute2,

NULL bk_global_attribute3,

NULL bk_global_attribute4,

NULL bk_global_attribute5,

NULL bk_global_attribute6,

NULL bk_global_attribute7,

NULL bk_global_attribute8,

NULL bk_global_attribute9,

NULL bk_global_attribute10,

NULL bk_global_attribute11,

NULL bk_global_attribute12,

NULL bk_global_attribute13,

NULL bk_global_attribute14,

NULL bk_global_attribute15,

NULL bk_global_attribute16,

NULL bk_global_attribute17,

NULL bk_global_attribute18,

NULL bk_global_attribute19,

NULL bk_global_attribute20,

NULL bk_global_attribute_number1,

NULL bk_global_attribute_number2,

NULL bk_global_attribute_number3,

NULL bk_global_attribute_number4,

NULL bk_global_attribute_number5,

NULL bk_global_attribute_date1,

NULL bk_global_attribute_date2,

NULL bk_global_attribute_date3,

NULL bk_global_attribute_date4,

NULL bk_global_attribute_date5,

NULL bk_global_attribute_category,

NULL th_global_attribute1,

NULL th_global_attribute2,

NULL th_global_attribute3,

NULL th_global_attribute4,

NULL th_global_attribute5,

NULL th_global_attribute6,

NULL th_global_attribute7,

NULL th_global_attribute8,

NULL th_global_attribute9,

NULL th_global_attribute10,

NULL th_global_attribute11,

NULL th_global_attribute12,

NULL th_global_attribute13,

NULL th_global_attribute14,

NULL th_global_attribute15,

NULL th_global_attribute16,

NULL th_global_attribute17,

NULL th_global_attribute18,

NULL th_global_attribute19,

NULL th_global_attribute20,

NULL th_global_attribute_number1,

NULL th_global_attribute_number2,

NULL th_global_attribute_number3,

NULL th_global_attribute_number4,

NULL th_global_attribute_number5,

NULL th_global_attribute_date1,

NULL th_global_attribute_date2,

NULL th_global_attribute_date3,

NULL th_global_attribute_date4,

NULL th_global_attribute_date5,

NULL th_global_attribute_category,

NULL ai_global_attribute1,

NULL ai_global_attribute2,

NULL ai_global_attribute3,

NULL ai_global_attribute4,

NULL ai_global_attribute5,

NULL ai_global_attribute6,

NULL ai_global_attribute7,

NULL ai_global_attribute8,

NULL ai_global_attribute9,

NULL ai_global_attribute10,

NULL ai_global_attribute11,

NULL ai_global_attribute12,

NULL ai_global_attribute13,

NULL ai_global_attribute14,

NULL ai_global_attribute15,

NULL ai_global_attribute16,

NULL ai_global_attribute17,

NULL ai_global_attribute18,

NULL ai_global_attribute19,

NULL ai_global_attribute20,

NULL ai_global_attribute_number1,

NULL ai_global_attribute_number2,

NULL ai_global_attribute_number3,

NULL ai_global_attribute_number4,

NULL ai_global_attribute_number5,

NULL ai_global_attribute_date1,

NULL ai_global_attribute_date2,

NULL ai_global_attribute_date3,

NULL ai_global_attribute_date4,

NULL ai_global_attribute_date5,

NULL ai_global_attribute_category,

NULL vendor_name,

NULL vendor_number,

NULL po_number,

NULL invoice_number,

NULL invoice_voucher_number,

NULL invoice_date,

NULL payables_units,

NULL invoice_line_number,

NULL invoice_line_type,

NULL invoice_line_description,

NULL invoice_payment_number,

NULL project_number,

NULL project_task_number,

NULL fully_reserve_on_add_flag,

NULL deprn_adjustment_factor,

NULL revalued_cost,

NULL backlog_deprn_reserve,

NULL ytd_backlog_deprn,

NULL reval_amort_balance,

NULL ytd_reval_amortization,

NULL split_merged_code

FROM

xxdm_fa_mass_add_src xfmas

WHERE

nvl(

xfmas.dm_process_flag,

'N'

) = 'N';

COMMIT;

END p_load_fa_mass_add;

PROCEDURE p_load_fa_mass_add_dist IS

BEGIN

INSERT INTO xxdm_fa_mass_add_dist_fbdi (

mass_addition_id,

units,

employee_email_address,

location_segment1,

location_segment2,

location_segment3,

location_segment4,

location_segment5,

location_segment6,

location_segment7,

deprn_expense_segment1,

deprn_expense_segment2,

deprn_expense_segment3,

deprn_expense_segment4,

deprn_expense_segment5,

deprn_expense_segment6,

deprn_expense_segment7,

deprn_expense_segment8,

deprn_expense_segment9,

deprn_expense_segment10,

deprn_expense_segment11,

deprn_expense_segment12,

deprn_expense_segment13,

deprn_expense_segment14,

deprn_expense_segment15,

deprn_expense_segment16,

deprn_expense_segment17,

deprn_expense_segment18,

deprn_expense_segment19,

deprn_expense_segment20,

deprn_expense_segment21,

deprn_expense_segment22,

deprn_expense_segment23,

deprn_expense_segment24,

deprn_expense_segment25,

deprn_expense_segment26,

deprn_expense_segment27,

deprn_expense_segment28,

deprn_expense_segment29,

deprn_expense_segment30

)

SELECT

xfmas.asset_id mass_addition_id,

1 units,

NULL employee_email_address,

(select country from XXDM_FA_LOC_SEG_TT where country=xfmas.asset_location_segment1) location_segment1,

(select building from XXDM_FA_LOC_SEG_TT where building=xfmas.asset_location_segment2) location_segment2,

NULL location_segment3,

NULL location_segment4,

NULL location_segment5,

NULL location_segment6,

NULL location_segment7,

(

SELECT

segment1

FROM

xxdm_fa_enti_seg1_seg2_tt

WHERE

entity = xfmas.entity

) deprn_expense_segment1,

(

SELECT

segment2

FROM

xxdm_fa_enti_seg1_seg2_tt

WHERE

entity = xfmas.entity

) deprn_expense_segment2,

'0000' deprn_expense_segment3,

'400' deprn_expense_segment4,

depr_expense_account_segment2 deprn_expense_segment5,

'0000' deprn_expense_segment6,

'00000000' deprn_expense_segment7,

'00000000' deprn_expense_segment8,

NULL deprn_expense_segment9,

NULL deprn_expense_segment10,

NULL deprn_expense_segment11,

NULL deprn_expense_segment12,

NULL deprn_expense_segment13,

NULL deprn_expense_segment14,

NULL deprn_expense_segment15,

NULL deprn_expense_segment16,

NULL deprn_expense_segment17,

NULL deprn_expense_segment18,

NULL deprn_expense_segment19,

NULL deprn_expense_segment20,

NULL deprn_expense_segment21,

NULL deprn_expense_segment22,

NULL deprn_expense_segment23,

NULL deprn_expense_segment24,

NULL deprn_expense_segment25,

NULL deprn_expense_segment26,

NULL deprn_expense_segment27,

NULL deprn_expense_segment28,

NULL deprn_expense_segment29,

NULL deprn_expense_segment30

FROM

xxdm_fa_mass_add_src xfmas

WHERE

nvl(

xfmas.dm_process_flag,

'N'

) = 'N';

COMMIT;

END p_load_fa_mass_add_dist;

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

-- PROCEDURE: This procedure will be called by concurrent Program

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

PROCEDURE p_Main_Proc(i_Import_Type IN VARCHAR2)

Exp_No_Record EXCEPTION;

Vn_Record_Count NUMBER;

BEGIN

---p_Print_Log(i_Log => 'Calling Validations Procedure.');

IF i_Import_Type = 'VALIDATE' THEN

-- CALL PROCEDURE TO VALIDATE THE DATA

p_Validate(i_Revalidate_Flag => 'N');

ELSIF i_Import_Type = 'REVALIDATE' THEN

-- CALL PROCEDURE TO VALIDATE THE DATA

p_Validate(i_Revalidate_Flag => 'Y');

ELSIF i_Import_Type = 'VALIDATE AND TRANSFER' THEN

-- CALL PROCEDURE TO VALIDATE THE DATA

p_Validate(i_Revalidate_Flag => 'Y');

-- CALL PROCEDURE TO SUBMIT THE IMPORT PROGRAM

----p_Print_Log('Before Calling P_Insert_into_Interface');

p_Insert_Into_Interface;

-- p_Print_Log('After Calling P_Insert_into_Interface');

END IF;

-- CALL PROCEDURE TO PRINT OUTPUT

-- p_Print_Results;

EXCEPTION

WHEN Exp_No_Record THEN

Dbms_Output.Put_Line('************* No Record To Process *************');

WHEN OTHERS THEN

Dbms_Output.Put_Line(' Error in p_Insert_Into_Interface procedure - ' || SQLERRM);

END p_Main_Proc;

END xxdm_fa_mass_add_load_to_fbdi;

--Package Execution

begin

delete from xxdm_fa_mass_add_fbdi;

delete from xxdm_fa_mass_add_dist_fbdi;

commit;

xxdm_fa_mass_add_load_to_fbdi.p_Main_Proc('VALIDATE AND TRANSFER');

end;

Apps2CloudFusion

Navigation

Monday, 24 July 2023

FA Mass Addition FBDI Load Validation Scripts

Contact List

Navigation

Monday, 24 July 2023

FA Mass Addition FBDI Load Validation Scripts

Subscribe To A2CF