Oracle Apps Important Scripts and Packages

Mass Addition,Location Creation and Mass update of Assets

CREATE OR REPLACE PACKAGE APPS.KFSH_ASSET_COMMON_PKG
/*
Object Name:: KFSH_ASSET_COMMON_PKG
Object Type::PACKAGE
Created By:-
Purpose::MASS ADDITION OF ASSET,UPDATION of ASSET
Version ::1.0
*/
AS
PROCEDURE KFSH_FA_MASS_ADDITION;

PROCEDURE kfsh_update_asset_details;

PROCEDURE create_location (p_faculty VARCHAR2,
p_asset_number VARCHAR2,
p_text VARCHAR2,
p_building VARCHAR2,
p_floor VARCHAR2,
p_dept VARCHAR2,
p_room VARCHAR2,
p_area VARCHAR2,
p_stat OUT VARCHAR2);
END KFSH_ASSET_COMMON_PKG;
/

CREATE OR REPLACE PACKAGE BODY APPS.KFSH_ASSET_COMMON_PKG

Object Name:: KFSH_UPLOAD_ASSET

Object Type::PACKAGE

Created By:-

Purpose::MASS ADDITION OF ASSET

Version ::1.0

PROCEDURE KFSH_FA_MASS_ADDITION

l_trans_rec FA_API_TYPES.trans_rec_type;

l_dist_trans_rec FA_API_TYPES.trans_rec_type;

l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;

l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;

l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;

l_asset_type_rec FA_API_TYPES.asset_type_rec_type;

l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;

l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;

l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;

l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;

l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;

l_inv_tbl FA_API_TYPES.inv_tbl_type;

l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;

l_desc_flex_tbl FA_API_TYPES.desc_flex_rec_type;

l_return_status VARCHAR2 (1);

l_mesg_count NUMBER;

l_mesg VARCHAR2 (4000);

v_flag VARCHAR2 (2) := 'S';

v_cat_id NUMBER;

v_error_msg VARCHAR2 (4000);

l_loc_id_new NUMBER;

l_ccid NUMBER;

l_stat VARCHAR2 (1);

v_cnt NUMBER;

v_per_id NUMBER;

CURSOR c1

SELECT *

FROM KFSH_ASSET_UPLOAD_JEDDAH

WHERE PROCESS_FLAG IS NULL;

BEGIN

FA_SRVR_MSG.Init_Server_Message;

FOR i IN c1

LOOP

BEGIN

v_flag := 'S';

v_cat_id := NULL;

v_error_msg := NULL;

l_loc_id_new := NULL;

l_mesg := NULL;

l_ccid := NULL;

v_per_id:=NULL;

if i.ASSET_NUMBER='NOT AVAILABLE' then

i.ASSET_NUMBER:='';

end if;

if i.TAG_NUMBER='NOT AVAILABLE' then

i.TAG_NUMBER:='';

end if;

IF i.ASSET_NUMBER IS NOT NULL

THEN

BEGIN

SELECT 1

INTO v_cnt

FROM fa_additions

WHERE asset_number = TRIM (i.ASSET_NUMBER);

v_flag := 'N';

v_error_msg := v_error_msg || ':Asset Already Added';

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

BEGIN

SELECT 1

INTO v_cnt

FROM fa_mass_additions

WHERE asset_number = TRIM (i.ASSET_NUMBER);

v_flag := 'N';

v_error_msg := v_error_msg || ':Asset Already Added';

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END IF;

/*GET Dist CCID*/

BEGIN

SELECT code_combination_id

INTO l_ccid

FROM gl_code_combinations_kfv gcc

WHERE concatenated_segments = TRIM (i.Depriciation_Expense);

EXCEPTION

WHEN OTHERS

THEN

v_flag := 'N';

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while Fetching i.Depriciation_Expense';

END;

-- desc info

l_asset_desc_rec.description := i.DESCRIPTION;

l_asset_desc_rec.asset_key_ccid := 798237;

l_asset_desc_rec.tag_number := i.TAG_NUMBER;

l_asset_desc_rec.serial_number := i.SERIAL_NUMBER;

l_asset_desc_rec.manufacturer_name := i.MANUFACTURER_NAME;

l_asset_desc_rec.model_number := i.MODELNUMBER;

l_asset_desc_rec.asset_number := i.ASSET_NUMBER;

-- cat infoN

BEGIN

SELECT category_id

INTO v_cat_id

FROM fa_categories

WHERE segment1 || '-' || segment2 = i.ASSET_CATEGORY;

EXCEPTION

WHEN OTHERS

THEN

v_flag := 'N';

v_error_msg := v_error_msg || ':CAtegory Invalid';

END;

l_asset_cat_rec.category_id := v_cat_id; --type info

l_asset_type_rec.asset_type := 'CAPITALIZED';

-- fin info

l_asset_fin_rec.cost := i.cost;

l_asset_fin_rec.date_placed_in_service := TO_DATE ('24-Nov-2014');

--l_asset_fin_rec.depreciate_flag := 'YES';

-- -- deprn info

-- l_asset_deprn_rec.ytd_deprn := &ytd

-- l_asset_deprn_rec.deprn_reserve := &reserve

-- l_asset_deprn_rec.bonus_ytd_deprn := 0;

-- l_asset_deprn_rec.bonus_deprn_reserve := 0;

-- book / trans info

l_asset_hdr_rec.book_type_code := 'KFSH JEDDAH NEW';

-- distribution info

/*GET New Location TEXT */

BEGIN

SELECT location_id

INTO l_loc_id_new

FROM fa_locations_kfv

WHERE CONCATENATED_SEGMENTS =

'KFJ'

|| '.'

|| i.building_code

|| '.'

|| i.FLOOR

|| '.'

|| i.DEPT_UNIT

|| '.'

|| i.AREA

|| '.'

|| i.room

|| '.'

|| 'XXX';

EXCEPTION

WHEN OTHERS

THEN

APPS.KFSH_ASSET_COMMON_PKG.

create_location ('KFJ',

i.asset_number,

'XXX',

i.building_code,

i.FLOOR,

i.Dept_unit,

i.room,

i.AREA,

l_stat);

IF l_stat <> 'S'

THEN

v_flag := 'N';

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while Fetching New Location'

|| l_stat;

END IF;

END;

----Assignted to details

BEGIN

SELECT person_id

INTO v_per_id

FROM per_all_people_f

WHERE employee_number=i.employee_number

and trunc(sysdate) between effective_start_date and effective_end_date;

EXCEPTION

WHEN OTHERS

THEN

v_flag := 'N';

v_error_msg := v_error_msg || ':Employee Invalid';

END;

l_asset_dist_rec.units_assigned := 1;

l_asset_dist_rec.expense_ccid := l_ccid;

l_asset_dist_rec.location_ccid := l_loc_id_new;

l_asset_dist_rec.assigned_to := v_per_id;

l_asset_dist_rec.transaction_units := 1;

l_asset_dist_tbl (1) := l_asset_dist_rec;

----desc

-- call the api

IF v_flag = 'S'

THEN

fa_addition_pub.

do_addition ( -- std parameters

p_api_version => 1.0,

p_init_msg_list => FND_API.G_FALSE,

p_commit => FND_API.G_FALSE,

p_validation_level => FND_API.G_VALID_LEVEL_FULL,

p_calling_fn => NULL,

x_return_status => l_return_status,

x_msg_count => l_mesg_count,

x_msg_data => l_mesg,

-- api parameters

px_trans_rec => l_trans_rec,

px_dist_trans_rec => l_dist_trans_rec,

px_asset_hdr_rec => l_asset_hdr_rec,

px_asset_desc_rec => l_asset_desc_rec,

px_asset_type_rec => l_asset_type_rec,

px_asset_cat_rec => l_asset_cat_rec,

px_asset_hierarchy_rec => l_asset_hierarchy_rec,

px_asset_fin_rec => l_asset_fin_rec,

px_asset_deprn_rec => l_asset_deprn_rec,

px_asset_dist_tbl => l_asset_dist_tbl,

px_inv_tbl => l_inv_tbl);

--dump messages

l_mesg_count := fnd_msg_pub.count_msg;

IF l_mesg_count > 0

THEN

l_mesg :=

CHR (10)

|| SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

250);

DBMS_OUTPUT.put_line (l_mesg);

FOR i IN 1 .. (l_mesg_count - 1)

LOOP

l_mesg :=

SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE),

250);

DBMS_OUTPUT.put_line (l_mesg);

END LOOP;

END IF;

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

THEN

DBMS_OUTPUT.put_line ('FAILURE');

UPDATE KFSH_ASSET_UPLOAD_JEDDAH

SET PROCESS_FLAG = 'F',

MESSAGE =

SUBSTR (v_error_msg || '-' || l_mesg, 1, 3999),

created = fnd_profile.VALUE ('user_id')

WHERE seq_id = i.seq_id;

ELSE

DBMS_OUTPUT.put_line ('SUCCESS');

DBMS_OUTPUT.

put_line (

'THID' || TO_CHAR (l_trans_rec.transaction_header_id));

DBMS_OUTPUT.

put_line (

'ASSET_ID' || TO_CHAR (l_asset_hdr_rec.asset_id));

DBMS_OUTPUT.

put_line ('ASSET_NUMBER' || l_asset_desc_rec.asset_number);

UPDATE KFSH_ASSET_UPLOAD_JEDDAH

SET PROCESS_FLAG = 'S',

MESSAGE = 'Asset :' || l_asset_desc_rec.asset_number,

created = fnd_profile.VALUE ('user_id')

WHERE seq_id = i.seq_id;

COMMIT;

BEGIN

UPDATE fa_additions_b

SET ATTRIBUTE2 = NULL, ---Project

ATTRIBUTE1 = i.CPU, ---CPU

ATTRIBUTE10 = i.Ram, ---RAM

ATTRIBUTE11 = i.OS, --OS

ATTRIBUTE12 = i.MacAddress, --MAC ADDRESS

ATTRIBUTE13 = i.IPaddress, --IP ADDRESS

ATTRIBUTE3 = i.Qname, -- ICIS QNAME

ATTRIBUTE4 = i.Qname, --ORACLE QNAME

ATTRIBUTE5 = i.NetworkNodeNo, --NETWORK NODE NO

ATTRIBUTE6 = i.MonitorSize, --MONITOR SIZE

ATTRIBUTE7 = i.HardDeskSize, --HARD DESK SIZE

ATTRIBUTE8 = i.OTHER_DETAILS, --OTHER DETAILS

ATTRIBUTE14 = i.START_DATE, --START DATE

ATTRIBUTE15 = i.END_DATE, --End date

ATTRIBUTE17 = i.BRAVO_PHONE_NO, --Bravo Phone No./Dispatch No

ATTRIBUTE18 = i.SIM_CARD_NO, --SIM CARD NO

ATTRIBUTE19 = i.ACCOUNT_NO, --ACCOUNT NO

ATTRIBUTE20 = i.SHORTCUT_NO, --SHORTCUT NO

ATTRIBUTE9 = i.PIN_1, --PIN-1/PUK-1

ATTRIBUTE16 = i.PACKAGE --Package

WHERE asset_id=l_asset_hdr_rec.asset_id;

-- AND book_type_code = 'KFSH JEDDAH NEW';

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

UPDATE KFSH_ASSET_UPLOAD_JEDDAH

SET PROCESS_FLAG = 'F',

MESSAGE = 'ERROR in UPDATE',

created = fnd_profile.VALUE ('user_id')

WHERE seq_id = i.seq_id;

END;

END IF;

ELSE

UPDATE KFSH_ASSET_UPLOAD_JEDDAH

SET PROCESS_FLAG = 'F',

MESSAGE =SUBSTR (v_error_msg || '-' || l_mesg, 1, 3999),

created = fnd_profile.VALUE ('user_id')

WHERE seq_id = i.seq_id;

END IF;

fnd_msg_pub.delete_msg ();

EXCEPTION

WHEN OTHERS

THEN

UPDATE KFSH_ASSET_UPLOAD_JEDDAH

SET PROCESS_FLAG = 'F',

MESSAGE = SUBSTR (v_error_msg || '-' || l_mesg, 1, 3999),

created = fnd_profile.VALUE ('user_id')

WHERE seq_id = i.seq_id;

END;

END LOOP;

END;

PROCEDURE kfsh_update_asset_details

l_trans_rec FA_API_TYPES.trans_rec_type;

l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;

l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;

l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;

l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;

l_stat VARCHAR2 (100);

l_return_status VARCHAR2 (1);

l_mesg_count NUMBER;

l_msg_count NUMBER := 0;

l_mesg VARCHAR2 (512);

l_msg_data VARCHAR2 (4000);

v_error_msg VARCHAR2 (1000);

v_prc_flag VARCHAR2 (1) := 'N';

l_asset_id NUMBER;

l_facility VARCHAR2 (240);

l_build_code VARCHAR2 (240);

l_floor VARCHAR2 (240);

l_dept VARCHAR2 (240);

l_area VARCHAR2 (240);

l_room VARCHAR2 (240);

l_text VARCHAR2 (240);

l_location_id NUMBER;

l_ccid NUMBER;

l_loc_id_new NUMBER;

l_ccid_new NUMBER;

l_segment1 VARCHAR2 (240);

l_segment3 VARCHAR2 (240);

l_segment4 VARCHAR2 (240);

l_segment5 VARCHAR2 (240);

l_bk_code VARCHAR2 (240);

l_dist_id NUMBER;

l_transaction_units NUMBER;

L_ASSIGNED_TO NUMBER;

l_category_id number;

CURSOR c1

SELECT *

FROM kfsh_update_asset_data

WHERE process_flag IS NULL; --and rownum<=1000;

BEGIN

--dbms_output.enable(10000000);

FA_SRVR_MSG.Init_Server_Message;

FOR i IN c1

LOOP

BEGIN

v_error_msg := NULL;

v_prc_flag := 'Y';

/*Validation for Asset Id*/

BEGIN

SELECT asset_id

INTO l_asset_id

FROM fa_additions

WHERE asset_number = i.asset_number;

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg := v_error_msg || '-' || 'Invalid Asset';

END;

/*Get existing Location id ,CCID*/

BEGIN

SELECT location_id,

dis.code_combination_id,

dis.BOOK_TYPE_CODE,

dis.distribution_id,

UNITS_ASSIGNED,

ASSIGNED_TO

INTO l_location_id,

l_ccid,

l_bk_code,

l_dist_id,

l_transaction_units,

L_ASSIGNED_TO

FROM APPS.FA_ADDITIONS_B ass,

FA_DISTRIBUTION_HISTORY dis,

--kfsh.kfsh_fa_assets_transfer_t trn,

gl_code_combinations gcc

WHERE ass.ASSET_NUMBER = i.asset_number -- and ass.asset_number = 'C0005971'

AND ass.asset_id = dis.asset_id

AND dis.date_effective =

(SELECT MAX (date_effective)

FROM FA_DISTRIBUTION_HISTORY

WHERE asset_id = dis.asset_id)

AND gcc.code_combination_id = dis.code_combination_id;

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while fetching location and distribution data';

END;

/*GET Existing Facility,TEXT */

BEGIN

SELECT segment1, segment7

INTO l_facility, l_text

FROM fa_locations_kfv

WHERE location_id = l_location_id;

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg || '-' || 'Error while fetching Facility';

END;

/*GET New Location TEXT */

BEGIN

SELECT location_id

INTO l_loc_id_new

FROM fa_locations_kfv

WHERE CONCATENATED_SEGMENTS =

l_facility

|| '.'

|| i.building_code

|| '.'

|| i.FLOOR

|| '.'

|| i.Dept

|| '.'

|| i.AREA

|| '.'

|| i.room

|| '.'

|| l_text;

--dbms_output.put_line(l_loc_id_new);

EXCEPTION

WHEN OTHERS

THEN

BEGIN

APPS.KFSH_ASSET_COMMON_PKG.

create_location (l_facility,

i.asset_number,

l_text,

i.building_code,

i.FLOOR,

i.Dept,

i.room,

i.AREA,

l_stat);

SELECT location_id

INTO l_loc_id_new

FROM fa_locations_kfv

WHERE CONCATENATED_SEGMENTS =

l_facility

|| '.'

|| i.building_code

|| '.'

|| i.FLOOR

|| '.'

|| i.Dept

|| '.'

|| i.AREA

|| '.'

|| i.room

|| '.'

|| l_text;

-- create_location (p_faculty VARCHAR2,

-- p_asset_number VARCHAR2,

-- p_text VARCHAR2,

-- p_building VARCHAR2,

-- p_floor VARCHAR2,

-- p_dept VARCHAR2,

-- p_room VARCHAR2,

-- p_area VARCHAR2,

-- p_stat OUT VARCHAR2);

IF l_stat <> 'S'

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while Fetching New Location';

END IF;

EXCEPTION

WHEN OTHERS THEN

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while Fetching New Location';

END;

/*Get existing Segment values from GL*/

BEGIN

SELECT SEGMENT1,

SEGMENT3,

SEGMENT4,

SEGMENT5

INTO l_segment1,

l_segment3,

l_segment4,

l_segment5

FROM gl_code_combinations

WHERE code_combination_id = l_ccid;

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg

|| '-'

|| 'Error while fetching exiting gl segments';

END;

/*Get new ccid from GL*/

--added on 28 july

BEGIN

SELECT code_combination_id

INTO l_ccid_new

FROM gl_code_combinations_kfv

WHERE CONCATENATED_SEGMENTS=trim(i.EXPENSE_ACCOUNT);

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg || '-' || 'Error while Fetching New CCID';

END;

---Coomented on 28 july 2015

-- BEGIN

-- SELECT code_combination_id

-- INTO l_ccid_new

-- FROM gl_code_combinations

-- WHERE SEGMENT1 = l_segment1

-- AND segment2 = i.COST_CENETER

-- AND SEGMENT3 = l_segment3

-- AND SEGMENT4 = l_segment4

-- AND SEGMENT5 = l_segment5;

-- EXCEPTION

-- WHEN OTHERS

-- THEN

-- v_prc_flag := 'N';

-- v_error_msg :=

-- v_error_msg || '-' || 'Error while Fetching New CCID';

-- END;

/*Get new Category d from FA*/

--added on 28 july

BEGIN

SELECT category_id

INTO l_category_id

from fa_categories where segment1||'-'||segment2=trim(i.Category);

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg || '-' || 'Error while Fetching Category id';

END;

/*Get Employee details */

--added on 28 july

BEGIN

select person_id into L_ASSIGNED_TO

from per_all_people_f where employee_number=trim(i.EMPLOYEE_NUMBER)

and trunc(sysdate) between effective_start_date and effective_end_date;

EXCEPTION

WHEN OTHERS

THEN

v_prc_flag := 'N';

v_error_msg :=

v_error_msg || '-' || 'Error while Fetching Employee';

END;

IF v_prc_flag = 'Y'

THEN

l_asset_hdr_rec.asset_id := l_asset_id;

l_asset_cat_rec.category_id:=l_category_id;

l_asset_desc_rec.description := i.descripition;

l_asset_desc_rec.serial_number := i.SERIAL_NUMBER;

l_asset_desc_rec.model_number := i.MODEL_number;

l_asset_desc_rec.tag_number := i.TAG_NUMBER;

l_asset_desc_rec.MANUFACTURER_NAME := i.MANUFACTURER_NAME;

l_asset_hdr_rec.book_type_code := l_bk_code;

l_asset_dist_tbl (1).distribution_id := l_dist_id;

l_asset_dist_tbl (1).transaction_units := -l_transaction_units;

either above 2 lines or below 4 lines must be provided

for source distribution:

l_asset_dist_tbl(1).transaction_units := -2;

l_asset_dist_tbl(1).assigned_to := 11;

l_asset_dist_tbl(1).expense_ccid :=15338;

l_asset_dist_tbl(1).location_ccid := 3; */

-- fill in dist info for destination distribution

--l_asset_dist_tbl (2).units_assigned := 1;

l_asset_dist_tbl (2).transaction_units := l_transaction_units; --i.transaction_units;

l_asset_dist_tbl (2).assigned_to := L_ASSIGNED_TO;

l_asset_dist_tbl (2).expense_ccid := l_ccid_new;

l_asset_dist_tbl (2).location_ccid := l_loc_id_new;

-- l_asset_dist_tbl (3).transaction_units := 1;

-- l_asset_dist_tbl (3).assigned_to := 10;

-- l_asset_dist_tbl (3).expense_ccid := 92565;

-- l_asset_dist_tbl (3).location_ccid := 3;

l_trans_rec.who_info.last_updated_by := 0;

l_trans_rec.who_info.last_update_login := 0;

-- l_trans_rec.TRANSACTION_DATE_ENTERED := SYSDATE-90;

-- l_trans_rec.WHO_INFO.last_update_date := sysdate-90;

FA_ASSET_DESC_PUB.

update_desc (

-- std parameters

p_api_version => 1.0,

p_init_msg_list => FND_API.G_FALSE,

p_commit => FND_API.G_FALSE,

p_validation_level => FND_API.G_VALID_LEVEL_FULL,

p_calling_fn => NULL,

x_return_status => l_return_status,

x_msg_count => l_mesg_count,

x_msg_data => l_mesg,

-- api parameters

px_trans_rec => l_trans_rec,

px_asset_hdr_rec => l_asset_hdr_rec,

px_asset_desc_rec_new => l_asset_desc_rec,

px_asset_cat_rec_new => l_asset_cat_rec);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

THEN

l_mesg_count := fnd_msg_pub.count_msg;

IF l_mesg_count > 0

THEN

l_mesg :=

CHR (10)

|| SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

800);

-- dbms_output.put_line(l_mesg);

FOR i IN 1 .. (l_mesg_count - 1)

LOOP

l_mesg :=

SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE),

800);

-- dbms_output.put_line(l_mesg);

END LOOP;

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Description Details-'

|| l_mesg

WHERE asset_number = i.asset_number;

fnd_msg_pub.delete_msg ();

ELSE

l_mesg :=

CHR (10)

|| SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

800);

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Description Details-'

|| l_mesg

WHERE asset_number = i.asset_number;

fnd_msg_pub.delete_msg ();

END IF;

ELSE ----IF Desc update was successfull

fa_transfer_pub.

do_transfer (

p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

p_calling_fn => NULL,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

px_trans_rec => l_trans_rec,

px_asset_hdr_rec => l_asset_hdr_rec,

px_asset_dist_tbl => l_asset_dist_tbl);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

THEN

l_msg_count := fnd_msg_pub.count_msg;

IF l_msg_count > 0

THEN

v_error_msg :=

CHR (10)

|| SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

250);

-- dbms_output.put_line(l_mesg);

FOR i IN 1 .. (l_msg_count - 1)

LOOP

v_error_msg :=

SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE),

250);

-- dbms_output.put_line(l_mesg);

END LOOP;

Rollback;

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Transfer Details-'

|| SUBSTR (v_error_msg, 1, 1000)

WHERE asset_number = i.asset_number;

fnd_msg_pub.delete_msg ();

ELSE

v_error_msg :=

CHR (10)

|| SUBSTR (

fnd_msg_pub.

get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

250);

Rollback;

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Transfer Details-'

|| SUBSTR (v_error_msg, 1, 1000)

WHERE asset_number = i.asset_number;

fnd_msg_pub.delete_msg ();

END IF;

ELSE

BEGIN

--commented on 28 july 2015 as DFF setup was changed

UPDATE fa_additions_b

SET ATTRIBUTE17 = i.Network_Node_No,

ATTRIBUTE10 = i.CPU,

ATTRIBUTE11 = i.Ram,

ATTRIBUTE14 = i.IP_address,

ATTRIBUTE13 = i.Mac_Address,

ATTRIBUTE18 =i.Monitor_Size,

ATTRIBUTE15 = i.Qname,

ATTRIBUTE12 = i.OS ,

ATTRIBUTE2 = i.OTHER_DETAILS,

ATTRIBUTE19 = i.Hard_Desk_Size

WHERE asset_number = i.asset_number;

-- UPDATE fa_additions_b

-- SET ATTRIBUTE5 = i.Network_Node_No,

-- ATTRIBUTE1 = i.CPU,

-- ATTRIBUTE10 = i.Ram,

-- ATTRIBUTE13 = i.IP_address,

-- ATTRIBUTE12 = i.Mac_Address,

-- ATTRIBUTE8 = i.OTHER_DETAILS,

-- ATTRIBUTE4 = i.Qname,

-- ATTRIBUTE6 = i.Monitor_Size,

-- ATTRIBUTE7 = i.Hard_Desk_Size

-- WHERE asset_number = i.asset_number;

UPDATE kfsh_update_asset_data

SET process_flag = 'S',

error_msg = 'Succesfully updated the Data'

WHERE asset_number = i.asset_number;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Error in updation for attributes values'

WHERE asset_number = i.asset_number;

END;

END IF;

Commit;

END IF;

--dump messages

ELSE

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Decription Details-'

|| SUBSTR (v_error_msg, 1, 1000)

WHERE asset_number = i.asset_number;

Commit;

END IF;

EXCEPTION

WHEN OTHERS

THEN

UPDATE kfsh_update_asset_data

SET process_flag = 'F',

error_msg =

'Could not update Details-'

|| SUBSTR (v_error_msg, 1, 1000)

WHERE asset_number = i.asset_number;

Commit;

DBMS_OUTPUT.put_line ('FAILED');

END;

END LOOP;

--COMMIT;

END;

PROCEDURE create_location (p_faculty VARCHAR2,

p_asset_number VARCHAR2,

p_text VARCHAR2,

p_building VARCHAR2,

p_floor VARCHAR2,

p_dept VARCHAR2,

p_room VARCHAR2,

p_area VARCHAR2,

p_stat OUT VARCHAR2)

l_application_short_name VARCHAR2 (50);

l_key_flex_code VARCHAR2 (4);

l_structure_number NUMBER;

l_validation_date DATE;

l_combination_id NUMBER;

l_keyval_status BOOLEAN;

l_concat_segments fnd_flex_ext.segmentarray;

CURSOR cur_syscomb

SELECT BUILDING_CODE,

FLOOR,

DEPT,

AREA,

ROOM

FROM kfsh_update_asset_data

WHERE asset_number = p_asset_number;

BEGIN

fnd_global.apps_initialize (1131,

20563,

140,

-1);

-- FOR i_cur_outcomb IN cur_syscomb

-- LOOP

-- BEGIN

l_concat_segments (1) := NULL;

l_concat_segments (2) := NULL;

l_concat_segments (3) := NULL;

l_concat_segments (4) := NULL;

l_concat_segments (5) := NULL;

l_concat_segments (6) := NULL;

l_concat_segments (7) := NULL;

l_application_short_name := 'OFA';

l_key_flex_code := 'LOC#';

l_structure_number := 101;

l_validation_date := TO_DATE (SYSDATE - 365);

--'USA-NY-SAN FRANCISCO-OFFICE1'

l_concat_segments (1) := p_faculty;

l_concat_segments (2) := p_building;

l_concat_segments (3) := p_floor;

l_concat_segments (4) := p_dept;

l_concat_segments (5) := p_area;

l_concat_segments (6) := p_room;

l_concat_segments (7) := p_text;

-- l_concat_segments (8) := i_cur_outcomb.segment8;

/* l_concat_segments (1) := '11';

l_concat_segments (2) := '111';

l_concat_segments (3) := '511105';

l_concat_segments (4) := '01';

l_concat_segments (5) := '1006';

l_concat_segments (6) := '102';

l_concat_segments (7) := '00000';

l_concat_segments (8) := '0000';*/

/* l_concat_segments (1) := 'USA';

l_concat_segments (2) := 'NY';

l_concat_segments (3) := 'BALA';

l_concat_segments (4) := '1';*/

DBMS_OUTPUT.put_line ('API Started');

fnd_flex_server1.set_debugging ('6');

DBMS_OUTPUT.

put_line (

'Delimiter:'

|| fnd_flex_ext.

get_delimiter (l_application_short_name, l_key_flex_code, 101));

IF fnd_flex_ext.

get_combination_id (

application_short_name => l_application_short_name,

key_flex_code => l_key_flex_code,

structure_number => 101,

validation_date => l_validation_date,

n_segments => 7,

segments => l_concat_segments,

combination_id => l_combination_id)

THEN

DBMS_OUTPUT.put_line ('API Combination Created' || l_combination_id);

p_stat := 'S';

ELSE

DBMS_OUTPUT.put_line (fnd_message.get);

p_stat := 'F';

DBMS_OUTPUT.

put_line ('API Not Combination Created' || l_combination_id);

END IF;

-- EXCEPTIOn

-- WHEN OTHERS THEN

-- DBMS_OUTPUT.

-- put_line ('API Not Combination Created' || l_combination_id);

-- END;

-- END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

p_stat := 'F';

DBMS_OUTPUT.

put_line ('API Not Combination Created' || l_combination_id);

END;

END KFSH_ASSET_COMMON_PKG;

Oracle Apps Important Scripts and Packages

Monday, 20 February 2017

Requistion Add line,delete line,update line details,and distributions API

Monday, 3 August 2015