1 PACKAGE BODY GL_SEL_SEG_TURNOVER_RPT_PKG AS
2 -- $Header: glxssegb.pls 120.0.12000000.1 2007/10/23 16:28:37 sgudupat noship $
3 --=====================================================================
4 --=====================================================================
5 PROCEDURE gl_get_effective_num (p_ledger_id IN NUMBER
6 ,p_period_name IN VARCHAR2
7 ,x_per_eff_num OUT NOCOPY NUMBER
8 ,x_errbuf OUT NOCOPY VARCHAR2)
9 IS
10 lc_ledger_obj_type VARCHAR2(1);
11 ln_single_ledger_id NUMBER;
12 BEGIN
13 SELECT gl1.object_type_code
14 INTO lc_ledger_obj_type
15 FROM gl_ledgers gl1
16 WHERE gl1.ledger_id = p_ledger_id;
17
18 IF (lc_ledger_obj_type = 'L') THEN
19 ln_single_ledger_id := p_ledger_id;
20 ELSE
21 SELECT gl1.ledger_id
22 INTO ln_single_ledger_id
23 FROM gl_ledger_set_assignments glsa
24 ,gl_ledgers gl1
25 WHERE glsa.ledger_set_id = p_ledger_id
26 AND gl1.ledger_id = glsa.ledger_id
27 AND gl1.object_type_code = 'L'
28 AND ROWNUM = 1;
29 END IF;
30
31 SELECT gps.effective_period_num
32 INTO x_per_eff_num
33 FROM gl_period_statuses gps
34 WHERE gps.period_name = p_period_name
35 AND gps.ledger_id = ln_single_ledger_id
36 AND gps.application_id = 101;
37
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 x_errbuf := gl_message.get_message('GL_PLL_INVALID_PERIOD', 'Y'
41 ,'PERIOD', p_period_name
42 ,'LDGID', TO_CHAR(p_ledger_id));
43
44 WHEN OTHERS THEN
45 x_errbuf := SQLERRM;
46 END gl_get_effective_num;
47
48 --=====================================================================
49 --=====================================================================
50 FUNCTION int_doc_number(p_je_header_id IN NUMBER
51 ,p_je_line_num IN NUMBER)
52 RETURN VARCHAR2
53 IS
54 lc_doc_num VARCHAR2(100);
55 BEGIN
56 BEGIN
57 SELECT SUBSTR(aba.description,1,2)||'/'||
58 SUBSTR(csh.statement_number,1,4)||'/'||
59 SUBSTR(TO_CHAR(csl.line_number),1,4)
60 INTO lc_doc_num
61 FROM gl_import_references gir
62 ,ce_statement_lines csl
63 ,ce_statement_headers csh
64 ,ap_bank_accounts aba
65 WHERE gir.je_header_id = p_je_header_id
66 AND gir.je_line_num = p_je_line_num
67 AND gir.reference_3 = csl.statement_line_id
68 AND csh.statement_header_id = csl.statement_header_id
69 AND csh.bank_account_id = aba.bank_account_id;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 BEGIN
73 SELECT ai.doc_sequence_value
74 INTO lc_doc_num
75 FROM gl_import_references gir
76 ,ap_invoices ai
77 WHERE gir.je_header_id = p_je_header_id
78 AND gir.je_line_num = p_je_line_num
79 AND gir.reference_6 = 'AP Invoices'
80 AND gir.reference_2 = ai.invoice_id;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 BEGIN
84 SELECT TO_CHAR(gir.reference_4)
85 INTO lc_doc_num
86 FROM gl_import_references gir
87 WHERE gir.je_header_id = p_je_header_id
88 AND gir.je_line_num = p_je_line_num
89 AND (gir.reference_6 IN ('INV','DM','DEP','CM','AP Payments','AP Reconciled Payments','CUSTOMER','DIALOG_HK')
90 OR gir.reference_10 IN ('AR_CASH_RECEIPT_HISTORY','AR_RECEIVABLE_APPLICATIONS'));
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 lc_doc_num := NULL;
94 WHEN TOO_MANY_ROWS THEN
95 lc_doc_num := NULL;
96 END;
97 WHEN TOO_MANY_ROWS THEN
98 lc_doc_num := NULL;
99 END;
100 WHEN TOO_MANY_ROWS THEN
101 lc_doc_num := NULL;
102 WHEN OTHERS THEN
103 lc_doc_num := SQLERRM;
104 END;
105 RETURN(lc_doc_num);
106
107 END int_doc_number;
108
109 --=====================================================================
110 --=====================================================================
111 FUNCTION ctrl_segment_name(p_coa_id_in IN NUMBER
112 ,p_ctrl_seg_num_in IN NUMBER)
113 RETURN VARCHAR2
114 IS
115 lc_segment_name VARCHAR2(30);
116 BEGIN
117 BEGIN
118 SELECT fifseg.segment_name
119 INTO lc_segment_name
120 FROM fnd_id_flex_structures fifs
121 ,fnd_id_flex_segments fifseg
122 WHERE fifs.id_flex_code = fifseg.id_flex_code
123 AND fifs.id_flex_num = fifseg.id_flex_num
124 AND fifs.application_id = fifseg.application_id
125 AND fifseg.id_flex_code = 'GL#'
126 AND fifseg.id_flex_num = p_coa_id_in
127 AND fifseg.application_id = 101
128 AND fifseg.segment_num = p_ctrl_seg_num_in;
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 lc_segment_name := NULL;
132 WHEN OTHERS THEN
133 lc_segment_name := NULL;
134 END;
135 RETURN (lc_segment_name);
136 END ctrl_segment_name;
137
138 --=====================================================================
139 --=====================================================================
140 FUNCTION beforereport
141 RETURN BOOLEAN
142 IS
143 lc_security_mode VARCHAR2(1);
144 lc_error_msg VARCHAR2(200);
145 BEGIN
146
147 /*--*****************************************************
148 -- Obtain Period From and To Effective Numbers
149 --******************************************************/
150
151 gl_get_effective_num(LEDGER_ID_PARAM,PERIOD_FROM_PARAM
152 ,GC_PER_EFF_FROM_NUM,lc_error_msg);
153
154 IF lc_error_msg IS NOT NULL THEN
155 Raise_application_error(-20002,SQLERRM);
156 END IF;
157
158 gl_get_effective_num(LEDGER_ID_PARAM,PERIOD_TO_PARAM
159 ,GC_PER_EFF_TO_NUM,lc_error_msg);
160
161 IF lc_error_msg IS NOT NULL THEN
162 Raise_application_error(-20002,SQLERRM);
163 END IF;
164
165 --****************************************************
166 --Calculate Beginning and Period To Date column names
167 --****************************************************
168 /* default: if currency type is T of S (for all A/B/E balances) */
169 GC_BEGIN_DR_SELECT := 'SUM(DECODE(gps.effective_period_num, '||GC_PER_EFF_FROM_NUM||', NVL(gb.begin_balance_dr,0), 0))';
170 GC_BEGIN_CR_SELECT := 'SUM(DECODE(gps.effective_period_num, '||GC_PER_EFF_FROM_NUM||', NVL(gb.begin_balance_cr,0), 0))';
171 GC_PERIOD_DR_SELECT := 'SUM(NVL(gb.period_net_dr,0))';
172 GC_PERIOD_CR_SELECT := 'SUM(NVL(gb.period_net_cr,0))';
173
174 IF (CURRENCY_TYPE_PARAM = 'E') THEN
175 /* For Actual only - may pull from regular or BEQ columns */
176 IF (BALANCE_TYPE_PARAM = 'A') THEN
177 GC_BEGIN_DR_SELECT := 'SUM(NVL(DECODE(gps.effective_period_num, '||GC_PER_EFF_FROM_NUM||', DECODE(gb.translated_flag,''R'',gb.begin_balance_dr,gb.begin_balance_dr_beq),0), 0))';
178 GC_BEGIN_CR_SELECT := 'SUM(NVL(DECODE(gps.effective_period_num, '||GC_PER_EFF_FROM_NUM||', DECODE(gb.translated_flag,''R'',gb.begin_balance_cr,gb.begin_balance_cr_beq),0), 0))';
179 GC_PERIOD_DR_SELECT := 'SUM(NVL(DECODE(gb.translated_flag,''R'',gb.period_net_dr,gb.period_net_dr_beq),0))';
180 GC_PERIOD_CR_SELECT := 'SUM(NVL(DECODE(gb.translated_flag,''R'',gb.period_net_cr,gb.period_net_cr_beq),0))';
181 END IF;
182 END IF;
183 GC_NONZERO_WHERE := ' ( '||GC_PERIOD_DR_SELECT||' <> 0 OR '||GC_PERIOD_CR_SELECT||' <> 0)';
184
185 /*--*****************************************************
186 -- Identifying the BALANCE_TYPE_PARAM value
187 -- If A => Actual (No filter)
188 -- B => Budget (Filter data on Budget Name)
189 -- E => Encumbrance (Filter data on Encumbrance Type)
190 --******************************************************/
191
192 IF (BALANCE_TYPE_PARAM = 'A') THEN
193 GC_BALANCE_WHERE := ' 1 = 1 ';
194 ELSIF (BALANCE_TYPE_PARAM = 'B') THEN
195 GC_BALANCE_WHERE := ' gb.budget_version_id = ' || TO_CHAR(BUDGETNAME_ENCUMBRANCETYPE);
196 ELSE
197 GC_BALANCE_WHERE := ' gb.encumbrance_type_id = ' || TO_CHAR(BUDGETNAME_ENCUMBRANCETYPE);
198 END IF;
199
200 /*--*****************************************************
201 -- Identifying the Resulting Currency value this is
202 -- based on CURRENCY_TYPE_PARAM value
203 -- If T => Total (Ledger Currency)
204 -- E => Entered (Entered Currency)
205 -- S => Statistical (Entered Currency)
206 --******************************************************/
207
208 IF (CURRENCY_TYPE_PARAM = 'T') THEN
209 GC_RESULTING_CURRENCY := ''''||LEDGER_CURRENCY_PARAM||'''';
210 ELSE /* E or S */
211 GC_RESULTING_CURRENCY := ''''||ENTERED_CURRENCY_PARAM||'''';
212 END IF;
213
214 /*--*****************************************************
215 -- Identifying the GC_TRANSLATE_WHERE value
216 -- If E => Entered (Query filter will be Translated_flag IS NULL or 'R')
217 -- T => Total (Query filter will be Translated_flag IS NULL)
218 -- S => Statistical (Query filter will be Translated_flag IS NULL)
219 --******************************************************/
220
221 IF (CURRENCY_TYPE_PARAM = 'E') THEN
222 GC_TRANSLATE_WHERE := ' (gb.translated_flag = ''R'' OR gb.translated_flag IS NULL ) ';
223 ELSE /* T or S */
224 GC_TRANSLATE_WHERE := ' gb.translated_flag IS NULL ';
225 END IF;
226
227 /*--*****************************************************
228 -- Obtain the Data Access Security Clause
229 --******************************************************/
230
231 GC_DAS_BAL_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(ACCESS_SET_ID_PARAM
232 ,'R'
233 ,'LEDGER_COLUMN'
234 ,'LEDGER_ID'
235 ,'gb'
236 ,'SEG_COLUMN'
237 ,NULL
238 ,'gcc'
239 ,NULL);
240 IF (GC_DAS_BAL_WHERE IS NULL) THEN
241 GC_DAS_BAL_WHERE := ' 1 = 1 ';
242 END IF;
243
244 GC_DAS_JE_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(ACCESS_SET_ID_PARAM
245 ,'R'
246 ,'LEDGER_COLUMN'
247 ,'LEDGER_ID'
248 ,'gjh'
249 ,'SEG_COLUMN'
250 ,NULL
251 ,'gcc'
252 ,NULL);
253 IF (GC_DAS_JE_WHERE IS NULL) THEN
254 GC_DAS_JE_WHERE := ' 1 = 1 ';
255 END IF;
256
257 /*--*****************************************************
258 -- Segment Security enhancement to secure the balance
259 -- of begin and end balance. By calling gl_security_pkg,
260 -- the main query will only return rows that users have
261 -- the valid segment value access
262 --******************************************************/
263
264 BEGIN
265 FND_PROFILE.GET('GL_STD_ANALYSIS_REPORT_BALANCE_SECURITY', lc_security_mode);
266 EXCEPTION
267 WHEN OTHERS THEN
268 lc_security_mode := 'N';
269 END;
270
271 IF( NVL(lc_security_mode,'N') = 'Y') THEN
272 GL_SECURITY_PKG.INIT_SEGVAL;
273 GC_SECURITY_WHERE := ' GL_SECURITY_PKG.VALIDATE_ACCESS(' || LEDGER_ID_PARAM ||', gcc.code_combination_id) = ''TRUE'' ';
274 ELSE
275 GC_SECURITY_WHERE := ' 1 = 1 ';
276 END IF;
277
278 /*--*****************************************************
279 -- Obtain the Currency Code filter based on
280 -- Currency Type parameter
281 -- If CURRENCY_TYPE_PARAM is
282 -- 'S' => Statistical Currency data is selected
283 -- 'T' => All Currencies data is selected except STAT
284 -- 'E' => Entered Currency data is selected
285 (through Entered Currency parameter)
286 --******************************************************/
287
288 IF (CURRENCY_TYPE_PARAM = 'S') THEN
289 GC_CURRENCY_WHERE := '( gjh.currency_code = ''STAT''' ||' OR gjl.stat_amount IS NOT NULL)';
290 ELSIF (CURRENCY_TYPE_PARAM = 'T') THEN
291 GC_CURRENCY_WHERE := 'gjh.currency_code != ''STAT''';
292 ELSE /* currency type 'E' */
293 GC_CURRENCY_WHERE := 'gjh.currency_code = ''' || ENTERED_CURRENCY_PARAM || '''';
294 END IF;
295
296 /*--*****************************************************
297 -- Internal Document Number to be displayed or not
298 --******************************************************/
299
300 IF PRINT_INTERNAL_DOC_NUM_PARAM = 'Y' THEN
301 GC_INT_DOC_NUM := 'GL_SEL_SEG_TURNOVER_RPT_PKG.int_doc_number(gjl.je_header_id, gjl.je_line_num)';
302 ELSE
303 GC_INT_DOC_NUM := 'NULL';
304 END IF;
305
306 /*--*****************************************************
307 -- Used to obtain the Segment Names (Owner and Subordinate)
308 --******************************************************/
309 gc_additional_segment_name1 := ctrl_segment_name(COA_ID_PARAM, ADDITIONAL_SEGMENT_NUM1_PARAM);
310 gc_additional_segment_name2 := ctrl_segment_name(COA_ID_PARAM, ADDITIONAL_SEGMENT_NUM2_PARAM);
311
312 /*--*****************************************************
313 -- Used to obtain the Data Access Set Name
314 --******************************************************/
315
316 SELECT gas.name
317 INTO gc_data_access_set_name
318 FROM gl_access_sets gas
319 WHERE gas.access_set_id = ACCESS_SET_ID_PARAM;
320
321 RETURN(TRUE);
322 END beforereport;
323
324 END GL_SEL_SEG_TURNOVER_RPT_PKG;
[Home] [Help]