[Home] [Help]
PACKAGE BODY: APPS.GL_BUDGET_TRANSFER_PKG
Source
1 PACKAGE BODY gl_budget_transfer_pkg AS
2 /* $Header: glibdxfb.pls 120.4 2005/05/05 01:02:40 kvora ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7 PROCEDURE get_from_to_balance (balance_type VARCHAR2,
8 xledger_id NUMBER,
9 xbc_enabled_flag VARCHAR2,
10 xperiod_name VARCHAR2,
11 xbudget_version_id NUMBER,
12 xcurrency_code VARCHAR2,
13 from_code_combination_id NUMBER,
14 to_code_combination_id NUMBER,
15 from_balance IN OUT NOCOPY NUMBER,
16 to_balance IN OUT NOCOPY NUMBER) IS
17
18 BEGIN
19 from_balance := get_balance(balance_type,
20 xledger_id,
21 xbc_enabled_flag,
22 xperiod_name,
23 xbudget_version_id,
24 xcurrency_code,
25 from_code_combination_id);
26
27 to_balance := get_balance(balance_type,
28 xledger_id,
29 xbc_enabled_flag,
30 xperiod_name,
31 xbudget_version_id,
32 xcurrency_code,
33 to_code_combination_id);
34 END get_from_to_balance;
35
36 FUNCTION get_balance (balance_type VARCHAR2,
37 xledger_id NUMBER,
38 xbc_enabled_flag VARCHAR2,
39 xperiod_name VARCHAR2,
40 xbudget_version_id NUMBER,
41 xcurrency_code VARCHAR2,
42 code_combination_id NUMBER) RETURN NUMBER IS
43 balance NUMBER := 0;
44 BEGIN
45
46 -- If budgetary control is enabled, get the balance
47 -- from gl_bc_packets. Otherwise, get the balance from
48 -- gl_interface.
49 IF (xbc_enabled_flag = 'Y') THEN
50 balance := get_bc_balance(balance_type,
51 xledger_id,
52 xperiod_name,
53 'B',
54 xbudget_version_id,
55 xcurrency_code,
56 code_combination_id);
57 END IF;
58
59 -- Get the rest of the balance from gl_interface
60 balance := balance
61 + get_posted_balance(balance_type,
62 xledger_id,
63 xperiod_name,
64 'B',
65 xbudget_version_id,
66 xcurrency_code,
67 code_combination_id);
68
69 return(balance);
70 END get_balance;
71
72 PROCEDURE get_from_to_bc_balance(balance_type VARCHAR2,
73 xledger_id NUMBER,
74 xperiod_name VARCHAR2,
75 xactual_flag VARCHAR2,
76 xbudget_version_id NUMBER,
77 xcurrency_code VARCHAR2,
78 from_code_combination_id NUMBER,
79 to_code_combination_id NUMBER,
80 from_balance IN OUT NOCOPY NUMBER,
81 to_balance IN OUT NOCOPY NUMBER) IS
82
83 BEGIN
84
85 from_balance := get_bc_balance(balance_type,
86 xledger_id,
87 xperiod_name,
88 xactual_flag,
89 xbudget_version_id,
90 xcurrency_code,
91 from_code_combination_id);
92 to_balance := get_bc_balance(balance_type,
93 xledger_id,
94 xperiod_name,
95 xactual_flag,
96 xbudget_version_id,
97 xcurrency_code,
98 to_code_combination_id);
99 END get_from_to_bc_balance;
100
101 FUNCTION get_bc_balance (balance_type VARCHAR2,
102 xledger_id NUMBER,
103 xperiod_name VARCHAR2,
104 xactual_flag VARCHAR2,
105 xbudget_version_id NUMBER,
106 xcurrency_code VARCHAR2,
107 xcode_combination_id NUMBER) RETURN NUMBER IS
108
109 CURSOR get_balance IS
110 SELECT sum( nvl(pkt.entered_dr,0)
111 - nvl(pkt.entered_cr,0))
112 FROM gl_period_statuses per, gl_bc_packet_arrival_order ao,
113 gl_bc_packets pkt
114 WHERE per.application_id = 101
115 AND per.ledger_id = xledger_id
116 AND per.period_name = xperiod_name
117 AND ao.ledger_id = per.ledger_id
118 AND ao.affect_funds_flag = 'Y'
119 AND pkt.packet_id = ao.packet_id
120 AND pkt.ledger_id = ao.ledger_id
121 AND pkt.period_year * 1000 + pkt.period_num
122 <= per.period_year * 1000 + per.period_num
123 AND pkt.period_year*1000000 + pkt.quarter_num*1000 + pkt.period_num
124 >= decode (balance_type,
125 'PTD', per.period_year*1000000
126 + per.quarter_num*1000
127 + per.period_num,
128 'QTD', per.period_year*1000000
129 + per.quarter_num*1000,
130 'YTD', per.period_year*1000000,
131 'PJTD', 0)
132 AND pkt.code_combination_id = xcode_combination_id
133 AND pkt.actual_flag = xactual_flag
134 AND ( pkt.budget_version_id = xbudget_version_id
135 OR xactual_flag IN ('A','E'))
136 AND ( pkt.encumbrance_type_id = xbudget_version_id
137 OR xactual_flag IN ('A','B'))
138 AND pkt.currency_code = xcurrency_code
139 AND pkt.status_code = 'A';
140
141 temp_bal NUMBER;
142 BEGIN
143
144 -- Get the from and to balances from gl_balances
145 OPEN get_balance;
146 FETCH get_balance INTO temp_bal;
147 IF (get_balance%NOTFOUND) THEN
148 CLOSE get_balance;
149 RETURN(0);
150 ELSE
151 CLOSE get_balance;
152 RETURN(nvl(temp_bal,0));
153 END IF;
154
155 END get_bc_balance;
156
157 FUNCTION get_posted_balance (balance_type VARCHAR2,
158 xledger_id NUMBER,
159 xperiod_name VARCHAR2,
160 xactual_flag VARCHAR2,
161 xbudget_version_id NUMBER,
162 xcurrency_code VARCHAR2,
163 xcode_combination_id NUMBER) RETURN NUMBER IS
164
165 CURSOR get_balance IS
166 SELECT nvl(period_net_dr,0) - nvl(period_net_cr,0) PERIOD_NET,
167 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) BEGIN_BAL,
168 nvl(quarter_to_date_dr,0)-nvl(quarter_to_date_cr,0) QUARTER_BAL,
169 nvl(project_to_date_dr,0)-nvl(project_to_date_cr,0) PROJECT_BAL
170 FROM gl_balances bal
171 WHERE bal.ledger_id = xledger_id
172 AND bal.period_name = xperiod_name
173 AND bal.code_combination_id = xcode_combination_id
174 AND bal.actual_flag = xactual_flag
175 AND ( bal.budget_version_id = xbudget_version_id
176 OR xactual_flag IN ('A','E'))
177 AND ( bal.encumbrance_type_id = xbudget_version_id
178 OR xactual_flag IN ('A','B'))
179 AND bal.currency_code = xcurrency_code;
180
181 period_net NUMBER;
182 begin_bal NUMBER;
183 quarter_bal NUMBER;
184 project_bal NUMBER;
185 BEGIN
186
187 -- Get the from and to balances from gl_balances
188 OPEN get_balance;
189 FETCH get_balance INTO period_net, begin_bal, quarter_bal, project_bal;
190 IF (get_balance%NOTFOUND) THEN
191 CLOSE get_balance;
192 RETURN(0);
193 ELSE
194 CLOSE get_balance;
195
196 -- Calculate out the desired balance
197 IF (balance_type = 'PTD') THEN
198 RETURN(nvl(period_net,0));
199 ELSIF (balance_type = 'QTD') THEN
200 RETURN(nvl(quarter_bal,0) + nvl(period_net,0));
201 ELSIF (balance_type = 'YTD') THEN
202 RETURN(nvl(begin_bal,0) + nvl(period_net,0));
203 ELSIF (balance_type = 'PJTD') THEN
204 RETURN(nvl(project_bal,0) + nvl(period_net,0));
205 END IF;
206 END IF;
207
208 END get_posted_balance;
209
210 END gl_budget_transfer_pkg;