[Home] [Help]
PACKAGE BODY: APPS.GL_XML_JOURNAL_RPT_PKG
Source
1 PACKAGE BODY GL_XML_JOURNAL_RPT_PKG AS
2 /* $Header: glumlrxb.pls 120.4 2006/06/28 17:31:34 spala noship $ */
3
4 PROCEDURE START_PERIOD_DATE_NAME(X_START_DATE DATE,
5 X_LEDGER_ID NUMBER,
6 X_PERIOD_START_DATE OUT NOCOPY DATE,
7 X_START_PERIOD_NAME OUT NOCOPY VARCHAR2) IS
8
9 start_period_date DATE;
10 l_period_name VARCHAR2(100);
11 BEGIN
12
13 SELECT start_date, period_name
14 INTO start_period_date,l_period_name
15 FROM gl_period_statuses gps
16 WHERE gps.application_id = 101
17 AND gps.ledger_id = x_ledger_id
18 AND x_start_date between gps.START_DATE and gps.END_DATE
19 AND gps.adjustment_period_flag <> 'Y';
20
21
22 X_PERIOD_START_DATE := START_PERIOD_DATE;
23 X_START_PERIOD_NAME := l_period_name;
24
25 -- Add exception block to avoid error when user enters
26 -- start date which does not exists in gl_period_statuses.
27 -- See bug #259063.
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31
32 SELECT p1.start_date, p1.period_name
33 INTO start_period_date, l_period_name
34 FROM gl_period_statuses p1
35 WHERE p1.application_id = 101
36 AND p1.ledger_id = x_ledger_id
37 AND p1.adjustment_period_flag <> 'Y'
38 AND p1.start_date =
39 (SELECT MIN(p2.start_date)
40 FROM gl_period_statuses p2
41 WHERE p2.application_id = 101
42 AND p2.ledger_id = x_ledger_id
43 AND p2.adjustment_period_flag <> 'Y');
44
45
46 X_PERIOD_START_DATE := START_PERIOD_DATE;
47 X_START_PERIOD_NAME := l_period_name;
48
49
50 WHEN OTHERS THEN
51 X_PERIOD_START_DATE := NULL;
52 X_START_PERIOD_NAME := NULL;
53 END START_PERIOD_DATE_NAME;
54
55 Function Net_Line_Balance (P_ACCT_SEG_WHERE VARCHAR2,
56 P_STATUS VARCHAR2,
57 P_START_DATE DATE,
58 P_CURRENCY VARCHAR2,
59 P_LED_ID NUMBER,
60 P_BAL_SEG_NAME VARCHAR2,
61 P_BAL_SEG_VAL VARCHAR2,
62 P_ACCT_SEG_NAME VARCHAR2,
63 P_ACCT_SEG_VAL VARCHAR2,
64 P_SEC_SEG_NAME VARCHAR2,
65 P_SEC_SEG_VAL VARCHAR2) RETURN NUMBER IS
66
67 l_Sql_Stmt VARCHAR2(4000);
68 l_sql_stmt1 VARCHAR2(100);
69 Net_Line_Balance NUMBER;
70 l_start_prd_date DATE;
71 l_start_prd_name VARCHAR2(100);
72 l_status VARCHAR2(10);
73 l_stmt2 VARCHAR2(100);
74
75 BEGIN
76
77 START_PERIOD_DATE_NAME(X_START_DATE => P_START_DATE,
78 X_LEDGER_ID => P_LED_ID,
79 X_PERIOD_START_DATE => l_start_prd_date,
80 X_START_PERIOD_NAME => l_start_prd_name);
81
82 IF (P_CURRENCY = 'STAT') THEN
83 l_sql_stmt1 := ' AND hed.currency_code = ''STAT'' ';
84 ELSE
85 l_sql_stmt1 := ' AND hed.currency_code <> ''STAT'' ';
86 END IF;
87
88 IF P_STATUS IS NULL THEN
89 l_status := 'A';
90 ELSE
91 l_status := P_STATUS;
92 END IF;
93 IF (P_SEC_SEG_NAME IS NOT NULL) AND
94 (P_SEC_SEG_VAL IS NOT NULL)THEN
95 l_stmt2 := ' AND '||P_SEC_SEG_NAME||' = '''||P_SEC_SEG_VAL||'''';
96 END IF;
97
98
99 l_Sql_Stmt := ' SELECT
100 (SUM(nvl(accounted_dr, 0)) - SUM(nvl(accounted_cr, 0)))
101 FROM
102 GL_JE_HEADERS HED,
103 GL_JE_LINES LINE,
104 GL_CODE_COMBINATIONS CC
105 WHERE '''||l_STATUS||''' = ''P'''||
106 P_ACCT_SEG_WHERE||
107 ' AND CC.code_combination_id = line.code_combination_id
108 AND HED.je_header_id = line.je_header_id
109 AND trunc(line.effective_date) >= :START_PRD_DATE
110 AND trunc(line.effective_date) < :START_DATE
111 AND hed.period_name = :period_name '||
112 ' AND hed.actual_flag = ''A'''||
113 l_sql_stmt1||
114 ' AND hed.ledger_id = :LED_ID
115 AND '||p_bal_seg_name|| ' = :bal_Seg_Val
116 AND '|| p_acct_seg_name||' = :acct_seg_val '||l_stmt2||
117 ' AND LINE.STATUS = ''P''';
118
119
120 EXECUTE IMMEDIATE l_sql_stmt INTO NET_LINE_BALANCE Using
121 l_start_prd_date, P_START_DATE,
122 l_start_prd_name,P_LED_ID, P_BAL_SEG_VAL,
123 P_ACCT_Seg_Val;
124
125
126 Return NVL(Net_Line_Balance,'');
127
128 EXCEPTION
129
130 WHEN NO_DATA_FOUND Then
131 Return NULL;
132
133 END Net_Line_Balance;
134
135
136 Function Net_Begin_Balance (P_ACCT_SEG_WHERE VARCHAR2,
137 P_STATUS VARCHAR2,
138 P_START_DATE DATE,
139 P_CURRENCY VARCHAR2,
140 P_LED_ID NUMBER,
141 P_BAL_SEG_NAME VARCHAR2,
142 P_BAL_SEG_VAL VARCHAR2,
143 P_ACCT_SEG_NAME VARCHAR2,
144 P_ACCT_SEG_VAL VARCHAR2,
145 P_SEC_SEG_NAME VARCHAR2,
146 P_SEC_SEG_VAL VARCHAR2) RETURN NUMBER IS
147
148 l_stmt VARCHAR2(4000);
149 NET_BEG_BALANCE NUMBER;
150 l_prd_start_date DATE;
151 l_prd_start_name VARCHAR2(100);
152 l_stmt1 VARCHAR2(100);
153 l_status VARCHAR2(10);
154
155 BEGIN
156
157 START_PERIOD_DATE_NAME(X_START_DATE => P_START_DATE,
158 X_LEDGER_ID => P_LED_ID,
159 X_PERIOD_START_DATE => l_prd_start_date,
160 X_START_PERIOD_NAME => l_prd_start_name);
161
162
163 IF (P_SEC_SEG_NAME IS NOT NULL) AND
164 (P_SEC_SEG_VAL IS NOT NULL) THEN
165 l_stmt1 := ' AND '||P_SEC_SEG_NAME||' = '''||P_SEC_SEG_VAL||'''';
166 END IF;
167
168 IF P_STATUS IS NULL THEN
169 l_status := 'A';
170 ELSE
171 l_status := P_STATUS;
172 END IF;
173
174 l_stmt := 'SELECT
175 (SUM(decode(cc.summary_flag, ''N'', nvl(gb.begin_balance_dr, 0), 0)) -
176 SUM(decode(cc.summary_flag, ''N'', nvl(gb.begin_balance_cr, 0),0)))
177 FROM GL_CODE_COMBINATIONS CC,
178 GL_BALANCES GB
179 WHERE '''||l_status ||''' = ''P''
180 AND CC.code_combination_id = GB.code_combination_id '
181 ||P_ACCT_SEG_WHERE ||
182 ' AND GB.currency_code = '''||P_CURRENCY||'''
183 AND GB.ledger_id = :LGR_ID '
184 ||l_stmt1||
185 ' AND GB.actual_flag = ''A''
186 AND GB.period_name = '''||l_prd_start_name||'''
187 AND '||P_BAL_SEG_NAME ||' = '''||P_Bal_Seg_Val||'''
188 AND '||P_ACCT_SEG_NAME || ' = '''||P_ACCT_SEG_VAL||'''';
189
190
191 EXECUTE IMMEDIATE l_stmt INTO NET_BEG_BALANCE USING P_LED_ID;
192
193
194 Return Net_Beg_Balance;
195
196 EXCEPTION
197
198 WHEN NO_DATA_FOUND Then
199
200 Return NULL;
201
202 End Net_Begin_Balance;
203
204
205
206 FUNCTION Get_Contra_Account(P_Account_Select VARCHAR2,
207 p_Header_id NUMBER,
208 P_Sub_Doc_Seq_Id NUMBER,
209 P_Sub_Doc_SEq_Val VARCHAR2,
210 p_Accounted_Dr NUMBER,
211 P_Accounted_Cr NUMBER) RETURN VARCHAR2 IS
212
213 contra_account_name varchar2(240);
214 CONTRA_ACCT_SEGMENT varchar2(2000);
215 TYPE C_SQL_Cur_Type IS REF CURSOR;
216 c_sql_cur C_SQL_Cur_Type;
217 l_type VARCHAR2(25);
218 v_sql_stmt VARCHAR2(2000);
219 TYPE CONTRA_ACCOUNT_NAME_TBL IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
220 t_contra_account_name_tbl CONTRA_ACCOUNT_NAME_TBL;
221
222 BEGIN
223 CONTRA_ACCT_SEGMENT := 'G'||P_Account_select;
224 IF nvl(p_accounted_dr,0) <> 0 and nvl(p_accounted_cr,0) <> 0 THEN
225
226 var.prev_type := 'BOTH';
227 var.header_id_prev := p_header_id;
228 var.sub_doc_sequence_id_prev := p_sub_doc_seq_id;
229 var.sub_doc_sequence_value_prev := p_sub_doc_seq_val;
230
231 RETURN(null);
232
233 ELSIF (P_ACCOUNTED_DR IS NOT NULL and P_ACCOUNTED_DR <> 0) then
234
235 v_sql_stmt := 'select distinct '||
236 contra_acct_segment ||
237 ' from gl_je_lines gjl, gl_code_combinations gcc ' ||
238 'where gjl.je_header_id = :header_id and '||
239 'gjl.accounted_cr IS NOT NULL and gjl.accounted_cr <> 0 and ' ||
240 'gjl.code_combination_id = gcc.code_combination_id and '||
241 '((gjl.subledger_doc_sequence_id = :sub_doc_sequence_id and '||
242 'gjl.subledger_doc_sequence_value = :sub_doc_sequence_value) or '||
243 '(:sub_doc_sequence_id is null and gjl.subledger_doc_sequence_id is null and '||
244 'gjl.subledger_doc_sequence_value is null))';
245 l_type :='DR';
246
247 ELSIF p_accounted_cr IS NOT NULL and p_accounted_cr <> 0 THEN
248 v_sql_stmt := 'select distinct ' ||
249 contra_acct_segment ||
250 ' from gl_je_lines gjl, gl_code_combinations gcc '||
251 'where gjl.je_header_id = :header_id and '||
252 'gjl.accounted_dr IS NOT NULL and gjl.accounted_dr <> 0 and ' ||
253 'gjl.code_combination_id = gcc.code_combination_id and '||
254 '((gjl.subledger_doc_sequence_id = :sub_doc_sequence_id and '||
255 'gjl.subledger_doc_sequence_value = :sub_doc_sequence_value) or '||
256 '(:sub_doc_sequence_id is null and gjl.subledger_doc_sequence_id is null and '||
257 'gjl.subledger_doc_sequence_value is null))';
258 l_type :='CR';
259 END IF;
260
261
262
263
264 IF l_type = var.prev_type
265 AND p_header_id = var.header_id_prev
266 AND NVL(p_sub_doc_seq_id, -1) = NVL(var.sub_doc_sequence_id_prev, -1)
267 AND NVL(p_sub_doc_seq_val, -1)
268 = NVL(var.sub_doc_sequence_value_prev, -1) THEN
269 return (var.contra_account_name_prev);
270 END IF;
271
272 var.prev_type := l_type;
273 var.header_id_prev := p_header_id;
274 var.sub_doc_sequence_id_prev := p_sub_doc_seq_id;
275 var.sub_doc_sequence_value_prev := p_sub_doc_seq_val;
276
277 open c_sql_cur for v_sql_stmt using p_header_id,
278 p_sub_doc_seq_id ,
279 p_sub_doc_seq_val ,
280 p_sub_doc_seq_id ;
281 fetch c_sql_cur bulk collect into t_contra_account_name_tbl;
282 IF c_sql_cur%rowcount = 0 then
283
284 RAISE NO_DATA_FOUND;
285
286 END IF;
287
288 IF c_sql_cur%rowcount >= 2 then
289 RAISE TOO_MANY_ROWS;
290 ELSE
291 contra_account_name := t_contra_account_name_tbl(1);
292 END IF;
293
294 CLOSE c_sql_cur;
295
296 RETURN(contra_account_name);
297
298 EXCEPTION
299 WHEN TOO_MANY_ROWS THEN
300
301 close c_sql_cur;
302 var.contra_account_name_prev := 'MULTIPLE';
303 RETURN ('MULTIPLE');
304
305 WHEN NO_DATA_FOUND THEN
306
307 close c_sql_cur;
308 var.contra_account_name_prev := 'NO CONTRA ACCOUNT';
309 RETURN ('NO CONTRA ACCOUNT');
310
311 WHEN OTHERS THEN
312
313 close c_sql_cur;
314 return(sqlcode||sqlerrm);
315
316
317
318
319
320
321
322
323
324
325 End Get_Contra_Account;
326
327 END GL_XML_JOURNAL_RPT_PKG;