1 PACKAGE BODY GL_CASH_CLR_ACCT_ANAL_RPT_PKG AS
2 /* $Header: glxccaab.pls 120.1.12000000.1 2007/10/23 17:11:09 sgudupat noship $ */
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 ORDER BY
151 batch_name
152 ,jrnl_name';
153
154 ELSIF rept_type_param = 'SUMMARY' THEN
155 gc_gl_uncleared_query:=
156 'SELECT
157 NVL(SUM(NVL(gjl.accounted_dr,0)),0) jrnl_line_dr
158 ,NVL(SUM(NVL(gjl.accounted_cr,0)),0) jrnl_line_cr
159 FROM
160 gl_je_lines gjl
161 ,gl_je_headers gjh
162 ,gl_je_batches gjb
163 ,gl_period_statuses gps
164 WHERE
165 gjl.ledger_id = :ledger_id_param
166 AND gjl.code_combination_id = :ccid
167 AND gps.ledger_id = gjl.ledger_id
168 AND gjl.period_name = gps.period_name
169 AND gps.effective_period_num
170 BETWEEN :gn_effective_period_num_from
171 AND :gn_effective_period_num_to
172 AND gps.application_id = 200
173 AND gjl.status =''P''
174 AND gjb.je_batch_id = gjh.je_batch_id
175 AND gjh.je_header_id = gjl.je_header_id
176 AND UPPER(gjh.je_source) = ''MANUAL''
177 AND gps.application_id = 200
178 AND gjl.gl_sl_link_id IS NULL';
179 END IF;
180
181 -- Build the AP Cleared query based on the value of rept_type_param
182 IF rept_type_param = 'DETAIL' THEN
183 gc_ap_cleared_query:=
184 'SELECT
185 aca.check_number doc_num
186 ,gjl.effective_date line_effective_date
187 ,gjh.description jrnl_desc
188 ,xal.accounted_dr jrnl_line_dr
189 ,xal.accounted_cr jrnl_line_cr
190 FROM
191 gl_je_lines gjl
192 ,gl_import_references gir
193 ,gl_je_headers gjh
194 ,xla_ae_lines xal
195 ,xla_ae_headers xah
196 ,xla_transaction_entities xte
197 ,ap_checks_all aca
198 ,gl_period_statuses gps
199 WHERE
200 gjl.ledger_id = :ledger_id_param
201 AND gjl.code_combination_id = :ccid
202 AND gps.ledger_id = gjl.ledger_id
203 AND gjl.period_name = gps.period_name
204 AND gps.effective_period_num
205 BETWEEN :gn_effective_period_num_from
206 AND :gn_effective_period_num_to
207 AND gps.application_id = 200
208 AND gjl.status = ''P''
209 AND gjh.je_header_id = gjl.je_header_id
210 AND gjh.je_source = ''Payables''
211 AND gir.je_header_id = gjh.je_header_id
212 AND gir.je_line_num = gjl.je_line_num
213 AND gir.gl_sl_link_id = xal.gl_sl_link_id
214 AND gir.gl_sl_link_table = xal.gl_sl_link_table
215 AND xal.application_id = 200
216 AND xah.ae_header_id = xal.ae_header_id
217 AND xah.application_id = xal.application_id
218 AND xte.entity_id = xah.entity_id
219 AND xte.application_id = xah.application_id
220 AND aca.check_id = xte.source_id_int_1
221 AND aca.status_lookup_code IN (''CLEARED'',''RECONCILED'')
222 ORDER BY doc_num';
223
224 ELSIF rept_type_param = 'SUMMARY' THEN
225 gc_ap_cleared_query:=
226 'SELECT
227 NVL(SUM(NVL(xal.accounted_dr,0)),0) jrnl_line_dr
228 ,NVL(SUM(NVL(xal.accounted_cr,0)),0) jrnl_line_cr
229 FROM
230 gl_je_lines gjl
231 ,gl_import_references gir
232 ,gl_je_headers gjh
233 ,xla_ae_lines xal
234 ,xla_ae_headers xah
235 ,xla_transaction_entities xte
236 ,ap_checks_all aca
237 ,gl_period_statuses gps
238 WHERE
239 gjl.ledger_id = :ledger_id_param
240 AND gjl.code_combination_id = :ccid
241 AND gps.ledger_id = gjl.ledger_id
242 AND gjl.period_name = gps.period_name
243 AND gps.effective_period_num
244 BETWEEN :gn_effective_period_num_from
245 AND :gn_effective_period_num_to
246 AND gps.application_id = 200
247 AND gjl.status = ''P''
248 AND gjh.je_header_id = gjl.je_header_id
249 AND gjh.je_source = ''Payables''
250 AND gir.je_header_id = gjh.je_header_id
251 AND gir.je_line_num = gjl.je_line_num
252 AND gir.gl_sl_link_id = xal.gl_sl_link_id
253 AND gir.gl_sl_link_table = xal.gl_sl_link_table
254 AND xal.application_id = 200
255 AND xah.ae_header_id = xal.ae_header_id
256 AND xah.application_id = xal.application_id
257 AND xte.entity_id = xah.entity_id
258 AND xte.application_id = xah.application_id
259 AND aca.check_id = xte.source_id_int_1
260 AND aca.status_lookup_code IN (''CLEARED'',''RECONCILED'')';
261 END IF;
262
263 RETURN (true);
264 END before_Report;
265
266 FUNCTION access_set_name RETURN VARCHAR2 IS
267 lc_access_set_name VARCHAR2(50);
268
269 CURSOR get_access_set_name IS
270 SELECT gas.name data_access_set_name
271 FROM gl_access_sets gas
272 WHERE gas.access_set_id = data_access_set_id_param;
273 BEGIN
274 OPEN get_access_set_name;
275 FETCH get_access_set_name INTO lc_access_set_name;
276 CLOSE get_access_set_name;
277
278 RETURN lc_access_set_name;
279 END access_set_name;
280
281 FUNCTION ledger_name RETURN VARCHAR2 IS
282 lc_ledger_name VARCHAR2(50);
283
284 CURSOR get_ledger_name IS
285 SELECT gll.name ledger_name
286 FROM gl_ledgers gll
287 WHERE gll.ledger_id = ledger_id_param;
288 BEGIN
289 OPEN get_ledger_name;
290 FETCH get_ledger_name INTO lc_ledger_name;
291 CLOSE get_ledger_name;
292
293 RETURN lc_ledger_name;
294 END ledger_name;
295
296 END GL_CASH_CLR_ACCT_ANAL_RPT_PKG;
[Home] [Help]