1 PACKAGE BODY IGC_CBC_INQUIRY_PKG AS
2 /* $Header: IGCINQRB.pls 120.2.12000000.1 2007/08/20 12:15:23 mbremkum ship $ */
3
4 g_period_name gl_periods.period_name%TYPE;
5 g_period_year gl_periods.period_year%TYPE;
6 g_quarter_num gl_periods.quarter_num%TYPE;
7 g_period_num gl_periods.period_num%TYPE;
8 g_gl_budget_version_id gl_budget_versions.budget_version_id%TYPE;
9 g_true VARCHAR2(1);
10 g_false VARCHAR2(1);
11
12 g_amount_type VARCHAR2(4);
13
14 --PJTD
15 --PTD
16 --QTD
17 --YTD
18
19 PROCEDURE Initialize
20 (
21 p_amount_type IN VARCHAR2,
22 p_period_cutoff IN gl_periods.period_name%TYPE,
23 p_set_of_books_id IN gl_period_statuses.set_of_books_id%TYPE,
24 p_gl_budget_version_id IN gl_budget_versions.budget_version_id%TYPE
25 ) IS
26
27 l_gl_application_id fnd_application.application_id%TYPE;
28
29 CURSOR c_gl_periods IS
30 SELECT period_name,
31 period_num,
32 quarter_num,
33 period_year
34 FROM gl_period_statuses
35 WHERE set_of_books_id = p_set_of_books_id
36 AND period_name = p_period_cutoff
37 AND application_id = l_gl_application_id;
38
39 BEGIN
40
41 -- --------------------------------------------------------------------
42 -- Obtain the application ID that will be used throughout this process.
43 -- --------------------------------------------------------------------
44 SELECT application_id
45 INTO l_gl_application_id
46 FROM fnd_application
47 WHERE application_short_name = 'SQLGL';
48
49 OPEN c_gl_periods;
50 FETCH c_gl_periods INTO g_period_name,g_period_num,g_quarter_num,g_period_year;
51
52 IF c_gl_periods%NOTFOUND THEN
53 CLOSE c_gl_periods;
54 raise NO_DATA_FOUND;
55 END IF;
56
57 CLOSE c_gl_periods;
58 g_amount_type:=p_amount_type;
59 g_gl_budget_version_id:=p_gl_budget_version_id;
60 g_true :='T';
61 g_false :='F';
62
63 END Initialize;
64
65
66 FUNCTION Check_Amount_Type
67 (p_period_name IN gl_periods.period_name%TYPE,
68 p_period_year IN gl_periods.period_year%TYPE,
69 p_quarter_num IN gl_periods.quarter_num%TYPE,
70 p_period_num IN gl_periods.period_num%TYPE,
71 p_actual_flag IN igc_cbc_je_lines.actual_flag%TYPE,
72 p_gl_budget_version_id IN gl_budget_versions.budget_version_id%TYPE
73 ) RETURN VARCHAR2
74 IS
75 l_ret_status VARCHAR2(1) :=g_false;
76 BEGIN
77
78 IF (NVL(p_gl_budget_version_id,0) <> g_gl_budget_version_id OR g_gl_budget_version_id IS NULL)
79 AND p_actual_flag='B' THEN
80 RETURN g_false;
81 END IF;
82
83 IF ( g_amount_type='PJTD'
84 AND p_period_year <= g_period_year
85 AND p_quarter_num <= g_quarter_num
86 AND p_period_num <= g_period_num
87 )
88 OR
89 ( g_amount_type='PTD'
90 AND p_period_year = g_period_year
91 AND p_quarter_num = g_quarter_num
92 AND p_period_num = g_period_num
93 )
94 OR
95 ( g_amount_type='QTD'
96 AND p_period_year = g_period_year
97 AND p_quarter_num = g_quarter_num
98 AND p_period_num <= g_period_num
99 )
100 OR
101 ( g_amount_type='YTD'
102 AND p_period_year = g_period_year
103 AND p_quarter_num <= g_quarter_num
104 AND p_period_num <= g_period_num
105 )
106 THEN
107
108 l_ret_status:=g_true;
109
110 END IF;
111
112 return l_ret_status;
113
114 END Check_Amount_Type;
115
116
117 FUNCTION Get_Amount_Type RETURN VARCHAR2
118 IS
119 BEGIN
120 return g_amount_type;
121 END Get_Amount_type;
122
123 FUNCTION Get_Period_Name RETURN VARCHAR2
124 IS
125 BEGIN
126 return g_period_name;
127 END Get_Period_Name;
128
129 FUNCTION Get_FC_Balances(
130 p_mode IN VARCHAR2,
131 p_dccid IN igc_cbc_je_lines.code_combination_id%TYPE, -- Detail CCID
132 p_sob_id IN igc_cbc_je_lines.set_of_books_id%TYPE, -- Set of Books ID
133 p_budget_ver IN igc_cbc_je_lines.budget_version_id%TYPE, -- Budget ID
134 p_period_yr IN igc_cbc_je_lines.period_year%TYPE, -- Period year, ie 2000
135 p_period_nm IN igc_cbc_je_lines.period_num%TYPE, -- Period number
136 p_quarter_nm IN igc_cbc_je_lines.quarter_num%TYPE, -- Quarter number (1-4)
137 p_batch_id IN igc_cbc_je_lines.cbc_je_batch_id%TYPE,
138 p_actual_flg IN igc_cbc_je_lines.actual_flag%TYPE, --- 'B' or 'E'
139 p_enc_type_id IN igc_cbc_je_lines.encumbrance_type_id%TYPE, -- 1000 or 1082
140 p_line_num IN igc_cbc_je_lines.cbc_je_line_num%TYPE
141 )
142 RETURN NUMBER IS
143
144 -- Local variables
145 l_budget_bal NUMBER;
146 l_commit_bal NUMBER;
147 l_actual_bal NUMBER;
148 l_funds_avail NUMBER DEFAULT 0;
149 l_return_bal NUMBER DEFAULT 0;
150
151 l_api_name CONSTANT VARCHAR2(30) := 'Get_FC_Balances';
152
153 -- Remove this once the function has been integrated into the package
154 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGCBEFCB';
155 BEGIN
156
157 -- Budget balance with amount_type of year-to-date ('YTD')
158 IF(p_actual_flg = 'B') and (p_mode = 'ytd_balance') THEN
159
160 -- Total the debit balance of the budget from PSB
161 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
162 INTO l_budget_bal
163 FROM igc_cbc_je_lines JE
164 WHERE JE.actual_flag = 'B' -- 'B' for Budget
165 and JE.code_combination_id = p_dccid
166 and JE.period_year = p_period_yr
167 and JE.set_of_books_id = p_sob_id
168 and JE.status = 'P' --Permanent
169 and JE.period_num <= p_period_nm
170 and JE.budget_version_id = p_budget_ver;
171
172 l_return_bal := l_budget_bal;
173
174 END IF;
175
176
177 -- Budget balance with amount_type of quarter-to-date ('QTD')
178 IF(p_actual_flg = 'B') and (p_mode = 'qtd_balance') THEN
179
180 -- Total the debit balance of the budget from PSB
181 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
182 INTO l_budget_bal
183 FROM igc_cbc_je_lines JE
184 WHERE JE.actual_flag = 'B' -- 'B' for Budget
185 and JE.code_combination_id = p_dccid
186 and JE.period_year = p_period_yr
187 and JE.set_of_books_id = p_sob_id
188 and JE.status = 'P' --Permanent
189 and JE.quarter_num = p_quarter_nm
190 and JE.period_num <= p_period_nm
191 and JE.budget_version_id = p_budget_ver;
192
193 l_return_bal := l_budget_bal;
194
195 END IF;
196
197 -- Budget balance with amount_type of period-to-date ('PTD')
198 IF(p_actual_flg = 'B') and (p_mode = 'ptd_balance') THEN
199
200 -- Total the debit balance of the budget from PSB
201 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
202 INTO l_budget_bal
203 FROM igc_cbc_je_lines JE
204 WHERE JE.actual_flag = 'B' -- 'B' for Budget
205 and JE.code_combination_id = p_dccid
206 and JE.period_year = p_period_yr
207 and JE.set_of_books_id = p_sob_id
208 and JE.status = 'P' --Permanent status
209 and JE.period_num = p_period_nm
210 and JE.budget_version_id = p_budget_ver;
211
212 l_return_bal := l_budget_bal;
213
214 END IF;
215
216
217 -- Encumbrance balance with amount_type of period_to_date ('PTD')
218 IF(p_actual_flg = 'E') and (p_mode = 'ptd_balance') THEN
219
220 -- Total the debit balance from the provisional contract commitments
221 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
222 INTO l_commit_bal
223 FROM igc_cbc_je_lines JE
224 WHERE JE.actual_flag = 'E'
225 and JE.code_combination_id = p_dccid
226 and JE.period_year = p_period_yr
227 and JE.encumbrance_type_id = p_enc_type_id
228 and JE.set_of_books_id = p_sob_id
229 and JE.period_num = p_period_nm;
230
231 l_return_bal := l_commit_bal;
232
233 END IF;
234
235
236 -- Encumbrance balance with amount_type of quarter_to_date ('QTD')
237 IF(p_actual_flg = 'E') and (p_mode = 'qtd_balance') THEN
238
239 -- Total the debit balance from the provisional contract commitments
240 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
241 INTO l_commit_bal
242 FROM igc_cbc_je_lines JE
243 WHERE JE.actual_flag = 'E'
244 and JE.code_combination_id = p_dccid
245 and JE.period_year = p_period_yr
246 and JE.encumbrance_type_id = p_enc_type_id
247 and JE.set_of_books_id = p_sob_id
248 and JE.quarter_num = p_quarter_nm
249 and JE.period_num <= p_period_nm;
250
251 l_return_bal := l_commit_bal;
252
253 END IF;
254
255
256 -- Encumbrance balance with amount_type of year_to_date ('YTD')
257 IF(p_actual_flg = 'E') and (p_mode = 'ytd_balance') THEN
258
259 -- Total the debit balance from the provisional contract commitments
260 SELECT nvl(sum(entered_dr),0) - nvl(sum(entered_cr),0)
261 INTO l_commit_bal
262 FROM igc_cbc_je_lines JE
263 WHERE JE.actual_flag = 'E'
264 and JE.code_combination_id = p_dccid
265 and JE.period_year = p_period_yr
266 and JE.encumbrance_type_id = p_enc_type_id
267 and JE.set_of_books_id = p_sob_id
268 and JE.period_num <= p_period_nm;
269
270 l_return_bal := l_commit_bal;
271
272 END IF;
273
274
275 -- Return balance
276 return(l_return_bal);
277
278
279 -- Return a debit balance of 0 in the case of an error
280 EXCEPTION
281
282 WHEN NO_DATA_FOUND THEN
283 l_return_bal := 0;
284 return(l_return_bal);
285
286
287 END Get_FC_Balances;
288
289 END IGC_CBC_INQUIRY_PKG;
[Home] [Help]