1 PACKAGE BODY GL_CASH_CLR_ACCT_ANAL_RPT_PKG AS
2 /* $Header: glxccaab.pls 120.2 2010/07/16 06:14:22 skotakar ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION before_report RETURN BOOLEAN IS
9 BEGIN
10 -- Set the security context of subledger application
11 XLA_SECURITY_PKG.SET_SECURITY_CONTEXT(200);
12
13 -- Build the where clause based on database security
14 gc_access_where:= GL_ACCESS_SET_SECURITY_PKG.get_security_clause
15 (data_access_set_id_param,
16 'R',
17 'LEDGER_COLUMN',
18 'LEDGER_ID',
19 'gb',
20 'SEG_COLUMN',
21 NULL,
22 'gcc',
23 NULL);
24 IF gc_access_where is NULL THEN
25 gc_access_where:= '1 = 1';
26 END IF;
27
28 -- Get the effective period numbers
29 SELECT MAX(CASE gps.period_name
30 WHEN period_from_param THEN gps.effective_period_num END)
31 ,MAX(CASE gps.period_name
32 WHEN period_to_param THEN gps.effective_period_num END)
33 INTO gn_effective_period_num_from
34 ,gn_effective_period_num_to
35 FROM gl_period_statuses gps
36 WHERE gps.ledger_id = ledger_id_param
37 AND gps.application_id = 200
38 AND gps.period_name IN (period_from_param, period_to_param);
39
40 -- Build the AP uncleared query based on the value of rept_type_param
41 IF rept_type_param = 'DETAIL' THEN
42 gc_ap_uncleared_query:=
43 'SELECT
44 aca.check_number doc_num
45 ,gjl.effective_date line_effective_date
46 ,gjh.description jrnl_desc
47 ,xal.accounted_dr jrnl_line_dr
48 ,xal.accounted_cr jrnl_line_cr
49 FROM
50 gl_je_lines gjl
51 ,gl_import_references gir
52 ,gl_je_headers gjh
53 ,xla_ae_lines xal
54 ,xla_ae_headers xah
55 ,xla_transaction_entities xte
56 ,ap_checks_all aca
57 ,gl_period_statuses gps
58 WHERE
59 gjl.ledger_id = :ledger_id_param
60 AND gjl.code_combination_id = :ccid
61 AND gps.ledger_id = gjl.ledger_id
62 AND gjl.period_name = gps.period_name
63 AND gps.effective_period_num
64 BETWEEN :gn_effective_period_num_from
65 AND :gn_effective_period_num_to
66 AND gps.application_id = 200
67 AND gjl.status = ''P''
68 AND gjh.je_header_id = gjl.je_header_id
69 AND gjh.je_source = ''Payables''
70 AND gir.je_header_id = gjh.je_header_id
71 AND gir.je_line_num = gjl.je_line_num
72 AND gir.gl_sl_link_id = xal.gl_sl_link_id
73 AND gir.gl_sl_link_table = xal.gl_sl_link_table
74 AND xal.application_id = 200
75 AND xah.ae_header_id = xal.ae_header_id
76 AND xah.application_id = xal.application_id
77 AND xte.entity_id = xah.entity_id
78 AND xte.application_id = xah.application_id
79 AND aca.check_id = xte.source_id_int_1
80 AND aca.status_lookup_code NOT IN (''CLEARED'',''RECONCILED'')
81 ORDER BY doc_num';
82
83 ELSIF rept_type_param = 'SUMMARY' THEN
84 gc_ap_uncleared_query:=
85 'SELECT
86 NVL(SUM(NVL(xal.accounted_dr,0)),0) jrnl_line_dr
87 ,NVL(SUM(NVL(xal.accounted_cr,0)),0) jrnl_line_cr
88 FROM
89 gl_je_lines gjl
90 ,gl_import_references gir
91 ,gl_je_headers gjh
92 ,xla_ae_lines xal
93 ,xla_ae_headers xah
94 ,xla_transaction_entities xte
95 ,ap_checks_all aca
96 ,gl_period_statuses gps
97 WHERE
98 gjl.ledger_id = :ledger_id_param
99 AND gjl.code_combination_id = :ccid
100 AND gps.ledger_id = gjl.ledger_id
101 AND gjl.period_name = gps.period_name
102 AND gps.effective_period_num
103 BETWEEN :gn_effective_period_num_from
104 AND :gn_effective_period_num_to
105 AND gps.application_id = 200
106 AND gjl.status = ''P''
107 AND gjh.je_header_id = gjl.je_header_id
108 AND gjh.je_source = ''Payables''
109 AND gir.je_header_id = gjh.je_header_id
110 AND gir.je_line_num = gjl.je_line_num
111 AND gir.gl_sl_link_id = xal.gl_sl_link_id
112 AND gir.gl_sl_link_table = xal.gl_sl_link_table
113 AND xal.application_id = 200
114 AND xah.ae_header_id = xal.ae_header_id
115 AND xah.application_id = xal.application_id
116 AND xte.entity_id = xah.entity_id
117 AND xte.application_id = xah.application_id
118 AND aca.check_id = xte.source_id_int_1
119 AND aca.status_lookup_code NOT IN (''CLEARED'',''RECONCILED'')';
120 END IF;
121
122 -- Build the GL Uncleared query based on the value of rept_type_param
123 IF rept_type_param = 'DETAIL' THEN
124 gc_gl_uncleared_query:=
125 'SELECT
126 gjb.name batch_name
127 ,gjl.effective_date line_effective_date
128 ,gjh.name jrnl_name
129 ,gjl.accounted_dr jrnl_line_dr
130 ,gjl.accounted_cr jrnl_line_cr
131 FROM
132 gl_je_lines gjl
133 ,gl_je_headers gjh
134 ,gl_je_batches gjb
135 ,gl_period_statuses gps
136 WHERE
137 gjl.ledger_id = :ledger_id_param
138 AND gjl.code_combination_id = :ccid
139 AND gps.ledger_id = gjl.ledger_id
140 AND gjl.period_name = gps.period_name
141 AND gps.effective_period_num
142 BETWEEN :gn_effective_period_num_from
143 AND :gn_effective_period_num_to
144 AND gjl.status =''P''
145 AND gjb.je_batch_id = gjh.je_batch_id
146 AND gjh.je_header_id = gjl.je_header_id
147 AND UPPER(gjh.je_source) = ''MANUAL''
148 AND gjl.gl_sl_link_id IS NULL
149 AND gps.application_id = 200
150 /*Added this code to pick only unreconciled transactions in Cash Management*/
151 --As part of bug 9166199
152 AND NOT EXISTS (SELECT ''X''
153 FROM ce_statement_reconcils_all csra
154 WHERE csra.je_header_id = gjh.je_header_id
155 AND csra.reference_id = gjl.je_line_num
156 AND csra.status_flag = ''M''
157 AND csra.current_record_flag = ''Y''
158 AND csra.REFERENCE_TYPE = ''JE_LINE''
159 )
160 ORDER BY
161 batch_name
162 ,jrnl_name';
163
164 ELSIF rept_type_param = 'SUMMARY' THEN
165 gc_gl_uncleared_query:=
166 'SELECT
167 NVL(SUM(NVL(gjl.accounted_dr,0)),0) jrnl_line_dr
168 ,NVL(SUM(NVL(gjl.accounted_cr,0)),0) jrnl_line_cr
169 FROM
170 gl_je_lines gjl
171 ,gl_je_headers gjh
172 ,gl_je_batches gjb
173 ,gl_period_statuses gps
174 WHERE
175 gjl.ledger_id = :ledger_id_param
176 AND gjl.code_combination_id = :ccid
177 AND gps.ledger_id = gjl.ledger_id
178 AND gjl.period_name = gps.period_name
179 AND gps.effective_period_num
180 BETWEEN :gn_effective_period_num_from
181 AND :gn_effective_period_num_to
182 AND gps.application_id = 200
183 AND gjl.status =''P''
184 AND gjb.je_batch_id = gjh.je_batch_id
185 AND gjh.je_header_id = gjl.je_header_id
186 AND UPPER(gjh.je_source) = ''MANUAL''
187 AND gps.application_id = 200
188 AND gjl.gl_sl_link_id IS NULL
189 /*Added this code to pick only unreconciled transactions in Cash Management*/
190 --As part of bug 9166199
191 AND NOT EXISTS (SELECT ''X''
192 FROM ce_statement_reconcils_all csra
193 WHERE csra.je_header_id = gjh.je_header_id
194 AND csra.reference_id = gjl.je_line_num
195 AND csra.status_flag = ''M''
196 AND csra.current_record_flag = ''Y''
197 AND csra.REFERENCE_TYPE = ''JE_LINE''
198 )' ;
199 END IF;
200
201 -- Build the AP Cleared query based on the value of rept_type_param
202 IF rept_type_param = 'DETAIL' THEN
203 gc_ap_cleared_query:=
204 'SELECT
205 aca.check_number doc_num
206 ,gjl.effective_date line_effective_date
207 ,gjh.description jrnl_desc
208 ,xal.accounted_dr jrnl_line_dr
209 ,xal.accounted_cr jrnl_line_cr
210 FROM
211 gl_je_lines gjl
212 ,gl_import_references gir
213 ,gl_je_headers gjh
214 ,xla_ae_lines xal
215 ,xla_ae_headers xah
216 ,xla_transaction_entities xte
217 ,ap_checks_all aca
218 ,gl_period_statuses gps
219 WHERE
220 gjl.ledger_id = :ledger_id_param
221 AND gjl.code_combination_id = :ccid
222 AND gps.ledger_id = gjl.ledger_id
223 AND gjl.period_name = gps.period_name
224 AND gps.effective_period_num
225 BETWEEN :gn_effective_period_num_from
226 AND :gn_effective_period_num_to
227 AND gps.application_id = 200
228 AND gjl.status = ''P''
229 AND gjh.je_header_id = gjl.je_header_id
230 AND gjh.je_source = ''Payables''
231 AND gir.je_header_id = gjh.je_header_id
232 AND gir.je_line_num = gjl.je_line_num
233 AND gir.gl_sl_link_id = xal.gl_sl_link_id
234 AND gir.gl_sl_link_table = xal.gl_sl_link_table
235 AND xal.application_id = 200
236 AND xah.ae_header_id = xal.ae_header_id
237 AND xah.application_id = xal.application_id
238 AND xte.entity_id = xah.entity_id
239 AND xte.application_id = xah.application_id
240 AND aca.check_id = xte.source_id_int_1
241 AND aca.status_lookup_code IN (''CLEARED'',''RECONCILED'')
242 ORDER BY doc_num';
243
244 ELSIF rept_type_param = 'SUMMARY' THEN
245 gc_ap_cleared_query:=
246 'SELECT
247 NVL(SUM(NVL(xal.accounted_dr,0)),0) jrnl_line_dr
248 ,NVL(SUM(NVL(xal.accounted_cr,0)),0) jrnl_line_cr
249 FROM
250 gl_je_lines gjl
251 ,gl_import_references gir
252 ,gl_je_headers gjh
253 ,xla_ae_lines xal
254 ,xla_ae_headers xah
255 ,xla_transaction_entities xte
256 ,ap_checks_all aca
257 ,gl_period_statuses gps
258 WHERE
259 gjl.ledger_id = :ledger_id_param
260 AND gjl.code_combination_id = :ccid
261 AND gps.ledger_id = gjl.ledger_id
262 AND gjl.period_name = gps.period_name
263 AND gps.effective_period_num
264 BETWEEN :gn_effective_period_num_from
265 AND :gn_effective_period_num_to
266 AND gps.application_id = 200
267 AND gjl.status = ''P''
268 AND gjh.je_header_id = gjl.je_header_id
269 AND gjh.je_source = ''Payables''
270 AND gir.je_header_id = gjh.je_header_id
271 AND gir.je_line_num = gjl.je_line_num
272 AND gir.gl_sl_link_id = xal.gl_sl_link_id
273 AND gir.gl_sl_link_table = xal.gl_sl_link_table
274 AND xal.application_id = 200
275 AND xah.ae_header_id = xal.ae_header_id
276 AND xah.application_id = xal.application_id
277 AND xte.entity_id = xah.entity_id
278 AND xte.application_id = xah.application_id
279 AND aca.check_id = xte.source_id_int_1
280 AND aca.status_lookup_code IN (''CLEARED'',''RECONCILED'')';
281 END IF;
282
283 RETURN (true);
284 END before_Report;
285
286 FUNCTION access_set_name RETURN VARCHAR2 IS
287 lc_access_set_name VARCHAR2(50);
288
289 CURSOR get_access_set_name IS
290 SELECT gas.name data_access_set_name
291 FROM gl_access_sets gas
292 WHERE gas.access_set_id = data_access_set_id_param;
293 BEGIN
294 OPEN get_access_set_name;
295 FETCH get_access_set_name INTO lc_access_set_name;
296 CLOSE get_access_set_name;
297
298 RETURN lc_access_set_name;
299 END access_set_name;
300
301 FUNCTION ledger_name RETURN VARCHAR2 IS
302 lc_ledger_name VARCHAR2(50);
303
304 CURSOR get_ledger_name IS
305 SELECT gll.name ledger_name
306 FROM gl_ledgers gll
307 WHERE gll.ledger_id = ledger_id_param;
308 BEGIN
309 OPEN get_ledger_name;
310 FETCH get_ledger_name INTO lc_ledger_name;
311 CLOSE get_ledger_name;
312
313 RETURN lc_ledger_name;
314 END ledger_name;
315
316 END GL_CASH_CLR_ACCT_ANAL_RPT_PKG;
[Home] [Help]