-- +================================================================================+
-- | +
-- +================================================================================+
-- | Project : FIXED ASSET |
-- | Created By : Vivek |
-- | $Id: xx_assets_src_line_package.pkb |
-- | |
-- | Description : Package for |
-- | Creating a Fixed Asset |
-- | |
-- | |
-- | |
-- |Version Date Author Remarks |
-- +======= =========== ============= ========================================+
-- |1.0 21-FEB-2018 Vivek Initial Version. |
-- | |
--+=================================================================================+
PROCEDURE LOG (p_message IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_message);
DBMS_OUTPUT.put_line (p_message);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Check Error In LOG:' || SQLERRM);
DBMS_OUTPUT.put_line ('Check Error In LOG:' || SQLERRM);
END;
PROCEDURE XXHRS_asset_src_line_procedure (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_stg_id IN NUMBER
)
AS
CURSOR c1
IS
SELECT *
FROM XXORC_ASSETS_INSERT
WHERE stg_id = p_stg_id;
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_inv_rec_type fa_api_types.inv_rec_type;
l_return_status VARCHAR2 (1);
l_mesg_count NUMBER;
l_mesg VARCHAR2 (4000);
l_user_id NUMBER;
n NUMBER := 1;
l_stg_id NUMBER;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'setting the context');
LOG ('==========PROGRAM STARTS HERE===========');
-- mo_global.init ('FA');
fnd_global.apps_initialize (user_id => fnd_global.user_id,
--1013497 , --l_user_id
resp_id => fnd_global.resp_id,
--20563 --l_resp_id = Fixed Asset Manager
resp_appl_id => fnd_global.resp_appl_id
-- 140 --l_resp_app_id -- 222);
);
-- mo_global.set_policy_context ('S', 204);
mo_global.set_policy_context ('S', fnd_profile.VALUE ('ORG_ID'));
DBMS_OUTPUT.ENABLE (10000000);
fa_srvr_msg.init_server_message;
--Stage ID Validations
BEGIN
l_stg_id := NULL;
SELECT COUNT (stg_id)
INTO l_stg_id
FROM xx_fixed_assets_stg
WHERE stg_id = p_stg_id AND status = 'Y';
IF l_stg_id > 0
THEN
LOG (p_stg_id || ' STAGE ID already Processed');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
LOG (l_stg_id || 'STAGE ID NOT FOUND');
WHEN OTHERS
THEN
LOG ('STAGE ID NOT FOUND' || SQLERRM);
END;
BEGIN
FOR r1 IN c1
LOOP
-- desc info
l_asset_desc_rec.asset_number := r1.asset_number;
l_asset_desc_rec.tag_number := r1.tag_number;
l_asset_desc_rec.serial_number := r1.serial_number;
l_asset_desc_rec.in_use_flag := r1.in_use_flag;
l_asset_desc_rec.new_used := r1.new_used;
l_asset_desc_rec.owned_leased := r1.owned_leased;
l_asset_desc_rec.current_units := r1.current_units;
l_asset_desc_rec.description := r1.asset_description;
l_asset_desc_rec.asset_key_ccid := r1.asset_key_ccid;
-- cat info
-- Valid Value in FA_CATEGORIES
l_asset_cat_rec.category_id := r1.category_id;
--type info
l_asset_type_rec.asset_type := r1.asset_type;
-- Asset Financial Information --
l_asset_fin_rec.date_placed_in_service :=
r1.date_placed_in_service;
l_asset_fin_rec.deprn_start_date := r1.deprn_start_date;
l_asset_fin_rec.life_in_months := r1.life_in_months;
l_asset_fin_rec.original_cost := r1.original_cost;
--l_asset_fin_rec.COST := r1.costs;
-- deprn info
l_asset_deprn_rec.set_of_books_id := r1.set_of_books_id;
-- book / trans info
-- Valid value in FA_BOOK_CONTROLS
l_asset_hdr_rec.book_type_code := r1.book_type_code;
-- distribution info
l_asset_dist_rec.units_assigned := r1.units_assigned;
-- Valid Record from GL Code cominations with record type = 'E' (Expense)
l_asset_dist_rec.expense_ccid := r1.expense_ccid;
-- Valid Value in FA Locations
l_asset_dist_rec.location_ccid := r1.location_ccid;
-- l_asset_dist_rec.assigned_to := r1.assigned_to;
l_asset_dist_tbl (1) := l_asset_dist_rec;
----Inv Information
-- l_inv_rec_type.invoice_number := r1.invoice_num;
-- l_inv_rec_type.attribute1 := r1.attribute1;
-- l_inv_rec_type.attribute2 := r1.attribute2;
-- l_inv_rec_type.attribute3 := r1.attribute3;
l_inv_rec_type.invoice_id := r1.invoice_id;
l_inv_rec_type.invoice_line_number := r1.invoice_line_number;
l_inv_rec_type.ap_distribution_line_number :=
r1.ap_distribution_line_number;
l_inv_rec_type.description := r1.description;
l_inv_rec_type.po_vendor_id := r1.po_vendor_id;
l_inv_rec_type.po_number := r1.po_number;
l_inv_rec_type.payables_batch_name := r1.payables_batch_name;
l_inv_rec_type.fixed_assets_cost := r1.fixed_assets_cost;
l_inv_rec_type.deleted_flag := r1.deleted_flag;
l_inv_rec_type.create_batch_id := r1.batch_id;
l_inv_rec_type.payables_code_combination_id :=
r1.payables_code_combination_id;
l_inv_rec_type.feeder_system_name := r1.feeder_system_name;
l_inv_rec_type.payables_cost := r1.payables_cost;
l_inv_rec_type.inv_indicator := r1.inv_indicator;
l_inv_tbl (n) := l_inv_rec_type;
n := n + 1;
LOG ('Invoice_ID: ' || l_inv_rec_type.invoice_id);
LOG ('Invoice_Number: ' || l_inv_rec_type.invoice_number);
END LOOP;
-- call the api
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
);
DBMS_OUTPUT.put_line (l_return_status);
--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
),
1,
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),
1,
250
);
DBMS_OUTPUT.put_line (l_mesg);
END LOOP;
fnd_msg_pub.delete_msg ();
END IF;
IF (l_return_status <> fnd_api.g_ret_sts_success)
THEN
LOG ('FAILURE');
ELSE
LOG ('SUCCESS');
LOG ('THID ' || TO_CHAR (l_trans_rec.transaction_header_id));
LOG ('STG_ID ' || p_stg_id);
LOG ('ASSET_ID ' || TO_CHAR (l_asset_hdr_rec.asset_id));
LOG ('ASSET_NUMBER ' || l_asset_desc_rec.asset_number);
UPDATE xx_fixed_assets_stg
SET status = 'Y'
WHERE stg_id = p_stg_id;
END IF;
COMMIT;
END xx_assets_src_line_procdure;
END;
END;
Effected Tables:
fa_additions_b
fa_books
