May
21
2017

FA Defterlerinin Kategorilere Atanması İşlemi Apisi

Yazar Erkan Erkişi Yorumlar

Herkese Merhabalar,

Ufak bir askerlik molasından sonra tekrar yazma şansı bulabildim. Bu sıralar aynı yoğun iş temposuna alışmaya çalışıyorum. Tabi ki fikir alışverişlerinden, paylaşmaktan ve kendimize vakit harcamaktan asla taviz vermiyoruz. 🙂

Elle yapılması can sıkıcı bir FA kurulumlarında defterleri kategorilere atama ve hesapları girme durumu var. Bunun bir public apisi bulunmamakta ancak aşağıdaki iki tabloya insertleri doğru yapmamız halinde işlemler gerçekleşiyor. Bu noktada kesinlikle dikkatli olmakta fayda var.

fa_category_books
fa_category_book_defaults

Örnek PL/SQL Kodu:

CREATE OR REPLACE PACKAGE APPS.XXDBO_FA_BOOK_TO_CAT_PKG
AS
/*Erkan Erkişi tarafından 16.05.2017 oluşturuldu.

Assign book to categories api

*/

TYPE fa_cat_books_tbl IS TABLE OF fa_category_books%ROWTYPE
INDEX BY BINARY_INTEGER;

TYPE fa_cat_books_def_tbl IS TABLE OF fa_category_book_defaults%ROWTYPE
INDEX BY BINARY_INTEGER;

function get_new_ccid(p_old_ccid in number) return number;
function get_control(p_cat_id in number,p_book_type_code in varchar) return boolean;
-- FUNCTION get_new_ccid (p_conc_Segm IN varchar) RETURN number;

FUNCTION create_ccid (
p_concat_segments IN VARCHAR2,
p_chart_of_accounts_id IN NUMBER
)
RETURN NUMBER;

FUNCTION get_new_depr_method (
p_dep_method IN VARCHAR2
)
RETURN VARCHAR2;

PROCEDURE insert_fa_category_books (p_cat_books IN fa_cat_books_tbl);

PROCEDURE insert_fa_category_books_def (p_cat_books_def IN fa_cat_books_def_tbl);

PROCEDURE Start_process (p_cat_id IN number,p_new_cat_id in number);
END XXDBO_FA_BOOK_TO_CAT_PKG;
/

****

CREATE OR REPLACE PACKAGE BODY APPS.xxdbo_fa_book_to_cat_pkg
AS
/* FUNCTION get_new_ccid (p_conc_segm IN varchar2)
RETURN NUMBER
IS
l_deger NUMBER;
BEGIN
SELECT code_combination_id
INTO l_deger
FROM gl_code_combinations_kfv
WHERE concatenated_segments = p_conc_segm AND ROWNUM = 1;

RETURN l_deger;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;*/

FUNCTION get_new_ccid (p_old_ccid IN number)
RETURN NUMBER
IS
l_deger NUMBER;
l_con VARCHAR2 (100);
BEGIN
BEGIN
SELECT concatenated_segments
INTO l_con
FROM gl_code_combinations_kfv@xxxdblink
WHERE code_combination_id = p_old_ccid AND ROWNUM = 1;

BEGIN
SELECT code_combination_id
INTO l_deger
FROM gl_code_combinations_kfv
WHERE concatenated_segments = l_con AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_deger := create_ccid (l_con, 50352);
RETURN l_deger;
END;
RETURN l_deger;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
END get_new_ccid;

FUNCTION get_control (p_cat_id IN number, p_book_type_code IN varchar)
RETURN BOOLEAN
IS
l_deger BOOLEAN;
al NUMBER;
BEGIN
BEGIN
SELECT 1
INTO al
FROM fa_category_books
WHERE category_id = p_cat_id AND book_type_code = p_book_type_code;

l_deger := FALSE;
RETURN l_deger;
EXCEPTION
WHEN OTHERS
THEN
l_deger := TRUE;
RETURN l_deger;
END;
END get_control;

FUNCTION create_ccid (p_concat_segments IN varchar2,
p_chart_of_accounts_id IN number
)
RETURN NUMBER
IS
l_ccid NUMBER;
BEGIN
SELECT fnd_flex_ext.get_ccid ('SQLGL',
'GL#',
p_chart_of_accounts_id,
SYSDATE,
p_concat_segments
)
INTO l_ccid
FROM DUAL;

RETURN l_ccid;
END create_ccid;

FUNCTION get_new_depr_method (p_dep_method IN varchar2
)
RETURN varchar2
IS
l_depr_method varchar2(50);
BEGIN

Begin
select GAZI_DEPRN_METHOD into l_depr_method from XXDBO_COMP_METHODS_T
where IST_DEPRN_METHOD = p_dep_method;
exception when others then
l_depr_method := p_dep_method;
end;

RETURN l_depr_method;
END get_new_depr_method;

PROCEDURE insert_fa_category_books (p_cat_books IN fa_cat_books_tbl)
IS
i NUMBER;
BEGIN
i := p_cat_books.FIRST;

IF p_cat_books (i).category_id IS NOT NULL
THEN

dbms_output.put_line(' Insert staarts - category_id : '|| p_cat_books(i).category_id || ' BOOK_TYPE_CODE: ' ||p_cat_books(i).BOOK_TYPE_CODE );

INSERT INTO fa_category_books
(
category_id,
book_type_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
asset_clearing_acct,
asset_cost_acct,
cip_clearing_acct,
cip_cost_acct,
deprn_expense_acct,
deprn_reserve_acct,
reval_amortization_acct,
reval_reserve_acct,
asset_cost_account_ccid,
asset_clearing_account_ccid,
wip_cost_account_ccid,
wip_clearing_account_ccid,
reserve_account_ccid,
reval_amort_account_ccid,
reval_reserve_account_ccid,
life_extension_ceiling,
life_extension_factor,
percent_salvage_value,
bonus_deprn_expense_acct,
bonus_deprn_reserve_acct,
bonus_reserve_acct_ccid
)
VALUES (
p_cat_books (i).category_id,
p_cat_books (i).book_type_code,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
p_cat_books (i).asset_clearing_acct, --ok
p_cat_books (i).asset_cost_acct, --ok
p_cat_books (i).cip_clearing_acct, --ok
p_cat_books (i).cip_cost_acct, --ok
p_cat_books (i).deprn_expense_acct, --ok
p_cat_books (i).deprn_reserve_acct, --ok
p_cat_books (i).reval_amortization_acct,
p_cat_books (i).reval_reserve_acct,
p_cat_books (i).asset_cost_account_ccid, --ok
p_cat_books (i).asset_clearing_account_ccid, --ok
p_cat_books (i).wip_cost_account_ccid, --ok
p_cat_books (i).wip_clearing_account_ccid, --ok
p_cat_books (i).reserve_account_ccid, --ok
p_cat_books (i).reval_amort_account_ccid,
p_cat_books (i).reval_reserve_account_ccid,
p_cat_books (i).life_extension_ceiling,
p_cat_books (i).life_extension_factor,
p_cat_books (i).percent_salvage_value,
p_cat_books (i).bonus_deprn_expense_acct, --ok
p_cat_books (i).bonus_deprn_reserve_acct, --ok
p_cat_books (i).bonus_reserve_acct_ccid --ok
);
END IF;
END;

PROCEDURE insert_fa_category_books_def (p_cat_books_def IN fa_cat_books_def_tbl
)
IS
j NUMBER;
BEGIN
j := p_cat_books_def.FIRST;

IF p_cat_books_def (j).category_id IS NOT NULL
THEN

dbms_output.put_line(' Insert2 staarts - category_id : '|| p_cat_books_def(j).category_id || ' BOOK_TYPE_CODE: ' ||p_cat_books_def(j).BOOK_TYPE_CODE );

INSERT INTO fa_category_book_defaults
(
category_id,
book_type_code,
start_dpis,
end_dpis,
life_in_months,
deprn_method,
prorate_convention_code,
itc_eligible_flag,
use_itc_ceilings_flag,
ceiling_name,
basic_rate,
adjusted_rate,
bonus_rule,
depreciate_flag,
retirement_prorate_convention,
price_index_name,
use_stl_retirements_flag,
stl_method_code,
stl_life_in_months,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
subcomponent_life_rule,
minimum_life_in_months,
production_capacity,
unit_of_measure,
capital_gain_threshold,
use_deprn_limits_flag,
allowed_deprn_limit,
special_deprn_limit_amount,
percent_salvage_value,
mass_property_flag
)
VALUES (
p_cat_books_def (j).category_id,
p_cat_books_def (j).book_type_code,
p_cat_books_def (j).start_dpis,
p_cat_books_def (j).end_dpis,
p_cat_books_def (j).life_in_months, --600
p_cat_books_def (j).deprn_method, --STL
p_cat_books_def (j).prorate_convention_code, --KIST
p_cat_books_def (j).itc_eligible_flag, --NO
p_cat_books_def (j).use_itc_ceilings_flag, --NO
p_cat_books_def (j).ceiling_name,
p_cat_books_def (j).basic_rate,
p_cat_books_def (j).adjusted_rate,
p_cat_books_def (j).bonus_rule,
p_cat_books_def (j).depreciate_flag, --YES
p_cat_books_def (j).retirement_prorate_convention, --YILLIK
p_cat_books_def (j).price_index_name,
p_cat_books_def (j).use_stl_retirements_flag, --NO
p_cat_books_def (j).stl_method_code, --null
p_cat_books_def (j).stl_life_in_months, --null
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
p_cat_books_def (j).subcomponent_life_rule,
p_cat_books_def (j).minimum_life_in_months,
p_cat_books_def (j).production_capacity,
p_cat_books_def (j).unit_of_measure,
p_cat_books_def (j).capital_gain_threshold, --12
p_cat_books_def (j).use_deprn_limits_flag, --NO
p_cat_books_def (j).allowed_deprn_limit,
p_cat_books_def (j).special_deprn_limit_amount,
p_cat_books_def (j).percent_salvage_value,
p_cat_books_def (j).mass_property_flag --N
);
END IF;
END;

PROCEDURE start_process (p_cat_id IN number, p_new_cat_id IN number)
IS
CURSOR cat_books (p_cat_id IN number)
IS
SELECT *
FROM fa_category_books@xxxdblink
WHERE category_id = p_cat_id
AND book_type_code IN --('HN VUK'); /*
('SANKO MAK. VUK',
'STM IST SUBE',
'TRIES DEFTERI',
'SANKO MAK UFRS',
'HN UFRS',
'SMS UFRS',
'ROBUTEL UFRS',
'TRIES UFRS',
'HN VUK',
'ROBUTEL FA');
CURSOR cat_books_def (p_cat_id IN number,p_book_type_code in varchar2)
IS
SELECT *
FROM fa_category_book_defaults@xxxdblink
WHERE category_id = p_cat_id --AND book_type_code IN ('HN VUK');
and book_type_code = p_book_type_code;
/* AND book_type_code IN
('SANKO MAK. VUK',
'STM IST SUBE',
'TRIES DEFTERI',
'SANKO MAK UFRS',
'HN UFRS',
'SMS UFRS',
'ROBUTEL UFRS',
'TRIES UFRS',
'HN VUK',
'ROBUTEL FA');*/
l_rec_cat_book fa_cat_books_tbl ;
l_rec_cat_book_def fa_cat_books_def_tbl ;
c NUMBER := 0;
v NUMBER := 0;
BEGIN

FOR cat IN cat_books (p_cat_id)
LOOP
IF get_control (p_new_cat_id, cat.book_type_code)
THEN
DBMS_OUTPUT.put_line ('---------------------------------------------------------------');

SELECT *
INTO l_rec_cat_book (c)
FROM fa_category_books@xxxdblink
WHERE category_id = p_cat_id
AND book_type_code = cat.book_type_code;

-- DBMS_OUTPUT.put_line(l_rec_cat_book (c).asset_cost_account_ccid
-- || ' : asset_cost_account_ccid');

l_rec_cat_book (c).asset_cost_account_ccid :=
get_new_ccid (l_rec_cat_book (c).asset_cost_account_ccid);
l_rec_cat_book (c).asset_clearing_account_ccid :=
get_new_ccid (l_rec_cat_book (c).asset_clearing_account_ccid);
l_rec_cat_book (c).wip_cost_account_ccid :=
get_new_ccid (l_rec_cat_book (c).wip_cost_account_ccid);
l_rec_cat_book (c).wip_clearing_account_ccid :=
get_new_ccid (l_rec_cat_book (c).wip_clearing_account_ccid);
l_rec_cat_book (c).reserve_account_ccid :=
get_new_ccid (l_rec_cat_book (c).reserve_account_ccid);
l_rec_cat_book (c).bonus_reserve_acct_ccid :=
get_new_ccid (l_rec_cat_book (c).bonus_reserve_acct_ccid);

--**kategori

l_rec_cat_book (c).category_id := p_new_cat_id;
-- DBMS_OUTPUT.put_line(l_rec_cat_book (c).category_id
-- || ' : category_id');

insert_fa_category_books (l_rec_cat_book);
DBMS_OUTPUT.put_line ('First Insert OK for => ' || l_rec_cat_book (c).book_Type_Code);
FOR cat_def IN cat_books_def (p_cat_id,cat.book_type_code)
LOOP
SELECT *
INTO l_rec_cat_book_def (v)
FROM fa_category_book_defaults@xxxdblink
WHERE category_id = p_cat_id
AND book_type_code = cat_def.book_type_code;

l_rec_cat_book_def (c).category_id := p_new_cat_id;
l_rec_cat_book_def (c).DEPRN_METHOD := get_new_depr_method(l_rec_cat_book_def (c).DEPRN_METHOD);

insert_fa_category_books_def (l_rec_cat_book_def);
DBMS_OUTPUT.put_line ('Second Insert OK for =>' || l_rec_cat_book_def (c).book_Type_Code);
END LOOP;
else
dbms_output.put_line ('Already Exists');
END IF;
END LOOP;
END;
END xxdbo_fa_book_to_cat_pkg;
/

About
Latest Posts

Erkan Erkişi

1992 doğumlu, Pendik Fatih Anadolu Lisesi ve İstanbul Üniversitesi
Endüstri Mühendisliği mezunu. Üniversite eğitimi boyunca çeşitli işlerle
meşgul oldu. Son senesinde Oracle ile tanıştı ve 3 sene Oracle uygulamaları teknik ve fonsiyonel danışmanlık yaptı.