[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Balances(errbuf Out NOCOPY VARCHAR2
,retcode Out NOCOPY NUMBER
,p_appl_id In NUMBER) Is
l_appl_id_tbl appl_id_type;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Balances';
SELECT jl.application_id appl_id
,jl.org_id org_id
,jl.set_of_books_id sob_id
,jl.personnel_id vendor_id
,jl.code_combination_id ccid
,jl.period_set_name period_set_name
,gp.period_type period_type
,min((gp.period_year * 10000) + gp.period_num) min_jrl_perctr
,max((gp.period_year * 10000) + gp.period_num) max_jrl_perctr
FROM jl_br_journals jl
,gl_sets_of_books sb
,gl_periods gp
WHERE jl.application_id = p_appl_id
AND nvl(jl.journal_balance_flag,'N') = 'N'
AND sb.set_of_books_id = jl.set_of_books_id
AND sb.period_set_name = jl.period_set_name
AND gp.period_set_name = sb.period_set_name
AND gp.period_type = sb.accounted_period_type
AND gp.period_name = jl.period_name
GROUP BY jl.application_id, jl.org_id, jl.set_of_books_id, jl.personnel_id,
jl.code_combination_id, jl.period_set_name, gp.period_type;
SELECT min((period_year * 10000) + period_num) min_bal_perctr
,max((period_year * 10000) + period_num) max_bal_perctr
FROM jl_br_balances jb
WHERE jb.application_id = p_appl_id
AND jb.set_of_books_id = p_sob
AND jb.personnel_id = p_vendor_id
AND jb.code_combination_id = p_ccid
AND jb.period_set_name = p_set_name;
SELECT nvl(jb.application_id, p_appl_id) appl_id
,nvl(jb.set_of_books_id, p_sob_id) sob_id
,nvl(jb.personnel_id, p_vendor_id) vendor_id
,nvl(jb.code_combination_id, p_ccid) ccid
,nvl(jb.period_set_name, gp.period_set_name) persetname
,nvl(gp.period_name,gp.period_name) pername
,nvl(gp.period_year,gp.period_year) peryear
,nvl(gp.period_num,gp.period_num) pernum
,((gp.period_year*10000) + gp.period_num) perctr
,decode(jb.ending_balance_sign, 'D', -1 * nvl(jb.ending_balance,0), nvl(jb.ending_balance, 0)) ending_balance
,nvl(jb.ending_balance_sign,'N') ending_balance_sign
FROM gl_periods gp
,jl_br_balances jb
WHERE gp.period_set_name = p_set_name
AND gp.period_type = p_period_type
AND ((gp.period_year*10000)+ gp.period_num) between p_min_per_ctr and p_max_per_ctr
AND gp.adjustment_period_flag = 'N'
AND jb.application_id(+) = p_appl_id
AND jb.set_of_books_id(+) = p_sob_id
AND jb.personnel_id(+) = p_vendor_id
AND jb.code_combination_id(+) = p_ccid
AND jb.period_set_name(+) = gp.period_set_name
AND jb.period_name(+) = gp.period_name
ORDER BY ((gp.period_year*10000)+ gp.period_num);
SELECT
NVL(SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)),0) balance
FROM jl_br_journals jb
WHERE jb.application_id = p_appl_id
AND jb.set_of_books_id = p_sob_id
AND jb.personnel_id = p_vendor_id
AND jb.code_combination_id = p_ccid
AND jb.period_set_name = p_set_name
AND jb.period_name = p_name
AND NVL(jb.journal_balance_flag,'N') = 'N';
Delete From jl_br_balances
Where application_id = PersonnelCCID_Rec.appl_id
and set_of_books_id = PersonnelCCID_Rec.sob_id
and personnel_id = PersonnelCCID_Rec.vendor_id
and code_combination_id = PersonnelCCID_Rec.ccid
and period_set_name = PersonnelCCID_Rec.period_set_name
and ((period_year*10000) + period_num) between l_min_period_ctr and l_max_period_ctr;
Insert Into jl_br_balances_all
(application_id
,set_of_books_id
,period_set_name
,period_name
,period_year
,period_num
,code_combination_id
,personnel_id
,ending_balance_sign
,ending_balance
,creation_date
,last_update_date
,last_updated_by
,last_update_login
,created_by
,org_id)
Values (
l_appl_id_tbl(k)
,l_sob_id_tbl(k)
,l_period_set_name_tbl(k)
,l_period_name_tbl(k)
,l_period_year_tbl(k)
,l_period_num_tbl(k)
,l_ccid_tbl(k)
,l_personnel_id_tbl(k)
,l_new_end_bal_sign_tbl(k)
,ABS(l_new_end_bal_tbl(k))
,sysdate
,sysdate
,l_user
,l_login_id
,l_user
,PersonnelCCID_Rec.org_id);
Update jl_br_journals
Set journal_balance_flag = 'Y'
WHERE application_id = l_appl_id_tbl(l)
AND set_of_books_id = l_sob_id_tbl(l)
AND personnel_id = l_personnel_id_tbl(l)
AND code_combination_id = l_ccid_tbl(l)
AND period_set_name = l_period_set_name_tbl(l)
AND period_name = l_period_name_tbl(l);
End Update_Balances;
SELECT sb.set_of_books_id sob_id
,sb.period_set_name period_set_name
,sb.accounted_period_type period_type
FROM gl_sets_of_books sb
,(SELECT distinct set_of_books_id
FROM jl_br_journals_all jl
WHERE jl.set_of_books_id = nvl(p_sob_id, jl.set_of_books_id)) jl_sob
WHERE sb.set_of_books_id = jl_sob.set_of_books_id;
SELECT period_year peryear
,period_num pernum
,((period_year * 10000) + period_num) perctr
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_type = p_period_type
AND period_name = p_period_name;
SELECT jl.application_id appl_id
,jl.org_id
,jl.personnel_id vendor_id
,jl.code_combination_id ccid
FROM jl_br_journals_all jl
WHERE jl.application_id = nvl(p_appl_id, jl.application_id)
AND jl.org_id = nvl(p_org_id, jl.org_id)
AND jl.set_of_books_id = cp_sob_id
AND jl.personnel_id = nvl(p_vendor_id, jl.personnel_id)
GROUP BY jl.application_id, jl.org_id, jl.personnel_id, jl.code_combination_id
UNION
SELECT jb.application_id appl_id
,jb.org_id
,jb.personnel_id vendor_id
,jb.code_combination_id ccid
FROM jl_br_balances_all jb
WHERE jb.application_id = nvl(p_appl_id, jb.application_id)
AND jb.org_id = nvl(p_org_id, jb.org_id)
AND jb.set_of_books_id = cp_sob_id
AND jb.personnel_id = nvl(p_vendor_id, jb.personnel_id)
GROUP BY jb.application_id, jb.org_id, jb.personnel_id, jb.code_combination_id;
SELECT gp.period_set_name persetname
,gp.period_name pername
,gp.period_year peryear
,gp.period_num pernum
FROM gl_periods gp
WHERE gp.period_set_name = p_set_name
AND gp.period_type = p_period_type
AND ((gp.period_year*10000)+ gp.period_num) between p_min_per_ctr and p_max_per_ctr
AND gp.adjustment_period_flag = 'N'
ORDER BY ((gp.period_year*10000)+ gp.period_num);
SELECT
NVL(sum(decode(ending_balance_sign,'D', -1 * ending_balance,
ending_balance)),0) ending_balance
FROM jl_br_balances_all jb
WHERE jb.application_id = cp_appl_id
AND jb.org_id = cp_org_id
AND jb.set_of_books_id = cp_sob
AND jb.personnel_id = cp_vendor_id
AND jb.code_combination_id = cp_ccid
AND jb.period_set_name = cp_set_name
AND jb.period_name = cp_name;
SELECT
NVL(SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)),0) balance
FROM jl_br_journals_all jb
WHERE jb.application_id = cp_appl_id
AND jb.org_id = cp_org_id
AND jb.set_of_books_id = cp_sob
AND jb.personnel_id = cp_vendor_id
AND jb.code_combination_id = cp_ccid
AND jb.period_set_name = cp_set_name
AND jb.period_name = cp_name;
Delete from JL_BR_BALANCES_ALL
Where application_id = nvl(p_appl_id, application_id)
and org_id = nvl(p_org_id, org_id)
and set_of_books_id = Sob_Rec.sob_id
and ((period_year*10000) + period_num) > l_min_period_ctr
and personnel_id = nvl(p_vendor_id, personnel_id);
Insert Into jl_br_balances_all
(application_id
,set_of_books_id
,period_set_name
,period_name
,period_year
,period_num
,code_combination_id
,personnel_id
,ending_balance_sign
,ending_balance
,creation_date
,last_update_date
,last_updated_by
,last_update_login
,created_by
,org_id)
Values (l_appl_id_tbl(j)
,l_sob_id_tbl(j)
,l_period_set_name_tbl(j)
,l_period_name_tbl(j)
,l_period_year_tbl(j)
,l_period_num_tbl(j)
,l_ccid_tbl(j)
,l_personnel_id_tbl(j)
,l_end_bal_sign_tbl(j)
,ABS(l_end_bal_tbl(j))
,sysdate
,sysdate
,l_user
,l_login_id
,l_user
,l_org_id_tbl(j));
Update jl_br_journals_all
Set journal_balance_flag = 'Y'
WHERE application_id = l_appl_id_tbl(k)
AND org_id = l_org_id_tbl(k)
AND set_of_books_id = l_sob_id_tbl(k)
AND personnel_id = l_personnel_id_tbl(k)
AND code_combination_id = l_ccid_tbl(k)
AND period_set_name = l_period_set_name_tbl(k)
AND period_name = l_period_name_tbl(k);