FA ADDITION IMPORT API SCRIPT

FA ADDITION IMPORT API SCRIPT
------------------------------------------------

/* Formatted on 2013/04/20 03:32 (Formatter Plus v4.8.8) */
DECLARE
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_return_status VARCHAR2 (1);
l_mesg_count NUMBER;
l_mesg VARCHAR2 (4000);
l_expense_ccid NUMBER;
l_location_ccid NUMBER;
l_category_id NUMBER;
CURSOR cur_fa_addition
IS
SELECT *
FROM oomco_fa_upload
WHERE to_char(DATEPLACEDINSERVICE,'DD-MON-YYYY') ='30-APR-2005'
AND asset_type in ('CAPITALIZED')
and assetnumber NOT IN (SELECT asset_number
FROM fa_additions_b)
ORDER BY DATEPLACEDINSERVICE DESC ;
BEGIN
FOR c1 IN cur_fa_addition
LOOP
BEGIN
SELECT code_combination_id
INTO l_expense_ccid
FROM gl_code_combinations
WHERE enabled_flag = 'Y'
AND segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7
|| '.'
|| segment8 = c1.expense_account;
EXCEPTION
WHEN OTHERS
THEN
--l_expense_ccid := NULL;
DBMS_OUTPUT.put_line ( ' Expense account does not exist'
|| c1.expense_account
);
END;
BEGIN
SELECT location_id
INTO l_location_ccid
FROM fa_locations
WHERE segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6 =
c1.country
|| '-'
|| c1.region
|| '-'
|| c1.wilatay
|| '-'
|| c1.sbu
|| '-'
|| c1.cost_centre
|| '-'
|| c1.asset_location;
EXCEPTION
WHEN OTHERS
THEN
--l_location_ccid := NULL;
DBMS_OUTPUT.put_line ( 'Location does not exist'
|| c1.country
|| '-'
|| c1.region
|| '-'
|| c1.wilatay
|| '-'
|| c1.sbu
|| '-'
|| c1.cost_centre
|| '-'
|| c1.asset_location
);
END;
BEGIN
SELECT category_id
INTO l_category_id
FROM fa_categories
WHERE segment1 || '-' || segment2 || '-' || segment3 =
c1.majorcategory
|| '-'
|| c1.minor_category
|| '-'
|| c1.subminor;
EXCEPTION
WHEN OTHERS
THEN
--l_category_id := NULL;
DBMS_OUTPUT.put_line ( ' Category does not exist'
|| c1.majorcategory
|| '-'
|| c1.minor_category
|| '-'
|| c1.subminor
);
END;
DBMS_OUTPUT.ENABLE (10000000);
fa_srvr_msg.init_server_message;
-- desc info
l_asset_hdr_rec.asset_id := oomco_asset_id.NEXTVAL;
l_asset_desc_rec.asset_number := c1.assetnumber;
l_asset_desc_rec.description := c1.description;
l_asset_desc_rec.asset_key_ccid := 1;
-- cat info
l_asset_cat_rec.category_id := l_category_id;
--type info
l_asset_type_rec.asset_type := c1.asset_type;
-- fin info
l_asset_fin_rec.COST := c1.asset_cost;
l_asset_fin_rec.original_cost := c1.asset_cost;
l_asset_fin_rec.date_placed_in_service := c1.dateplacedinservice;
l_asset_fin_rec.prorate_date:= c1.dateplacedinservice;
l_asset_fin_rec.life_in_months := c1.lifeinmonths;
l_asset_fin_rec.depreciate_flag := 'YES';
-- deprn info
l_asset_deprn_rec.ytd_deprn := NULL;
l_asset_deprn_rec.deprn_reserve := NULL;
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 := 'CORPORATE BOOK';
-- distribution info
l_asset_dist_rec.units_assigned := c1.units;
l_asset_dist_rec.expense_ccid := l_expense_ccid;
l_asset_dist_rec.location_ccid := l_location_ccid;
l_asset_dist_rec.assigned_to := NULL;
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl (1) := l_asset_dist_rec;
-- 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
);
COMMIT;
--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
DBMS_OUTPUT.put_line ('FAILURE');
/*UPDATE oomco_fa_upload
SET error_message = 'FAILURE'
WHERE assetnumber = c1.assetnumber;
COMMIT;*/
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);
END IF;
COMMIT;
END LOOP;
END;