eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GL_GLXUSA_XMLP_PKG

Source


1 PACKAGE BODY GL_GLXUSA_XMLP_PKG AS
2 /* $Header: GLXUSAB.pls 120.1 2007/12/28 10:48:37 vijranga noship $ */
3 
4 function AfterReport return boolean is
5 begin
6 
7 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
8   return (TRUE);
9 end;
10 
11 function BeforeReport return boolean is
12 begin
13 
14 /*SRW.USER_EXIT('FND SRWINIT');*/null;
15 
16 
17 DECLARE
18 
19  t_ledger_id                      NUMBER;
20  t_chart_of_accounts_id           NUMBER;
21  t_currency_code                  VARCHAR2(15);
22  t_ledger_name                    VARCHAR2(30);
23 
24  t_to_ledger_id                   NUMBER;
25  t_to_ledger_name                 VARCHAR2(30);
26  t_to_chart_of_accounts_id        NUMBER;
27  t_to_currency_code               VARCHAR2(15);
28 
29  t_from_ledger_id                 NUMBER;
30  t_from_ledger_name               VARCHAR2(30);
31  t_from_chart_of_accounts_id      NUMBER;
32  t_from_currency_code             VARCHAR2(15);
33 
34  t_consolidation_id               NUMBER;
35  t_consolidation_name             VARCHAR2(33);
36  t_consolidation_method           VARCHAR2(30);
37  t_consolidation_currency_code    VARCHAR2(15);
38  t_consolidation_description      VARCHAR2(240);
39  t_consolidation_start_date       DATE;
40  t_consolidation_end_date         DATE;
41 
42  t_error_buffer                   VARCHAR2(400);
43  t_records_present                VARCHAR2(1);
44 
45  P1             VARCHAR2(500);
46  P2             VARCHAR2(500);
47  P3             VARCHAR2(500);
48  P4             VARCHAR2(500);
49  P5             VARCHAR2(500);
50  P6             VARCHAR2(500);
51  P6_ADB         VARCHAR2(500);
52  P7             VARCHAR2(500);
53  P8             VARCHAR2(500);
54  P9             VARCHAR2(500);
55  P10_1          VARCHAR2(500);
56  P10_2          VARCHAR2(500);
57  P10_3          VARCHAR2(500);
58  P10_4          VARCHAR2(500);
59  P10_5          VARCHAR2(500);
60  P10_6          VARCHAR2(500);
61  P10_7          VARCHAR2(500);
62  P11            VARCHAR2(500);
63  P12            VARCHAR2(500);
64  P13            VARCHAR2(500);
65  P14            VARCHAR2(500);
66  P15            VARCHAR2(500);
67  P16            VARCHAR2(500);
68  P17            VARCHAR2(500);
69  P18            VARCHAR2(500);
70  P19            VARCHAR2(500);
71  P20            VARCHAR2(500);
72  P21            VARCHAR2(500);
73  P22            VARCHAR2(500);
74  P23            VARCHAR2(500);
75  P24            VARCHAR2(500);
76  P25            VARCHAR2(500);
77  P26            VARCHAR2(500);
78  P27            VARCHAR2(500);
79  P28            VARCHAR2(500);
80  P29            VARCHAR2(500);
81  P30            VARCHAR2(500);
82  P31            VARCHAR2(500);
83  P32            VARCHAR2(500);
84  P33            VARCHAR2(500);
85  P34            VARCHAR2(500);
86  P35            VARCHAR2(500);
87  P36            VARCHAR2(500);
88  P37            VARCHAR2(500);
89  P38            VARCHAR2(500);
90  P39            VARCHAR2(500);
91  P40            VARCHAR2(500);
92 
93 
94 BEGIN
95 
96   t_consolidation_id := to_number(P_CONSOLIDATION_ID);
97   gl_get_consolidation_info(t_consolidation_id,
98                             t_consolidation_name,
99                             t_consolidation_method,
100                             t_consolidation_currency_code,
101                             t_from_ledger_id,
102                             t_to_ledger_id,
103                             t_consolidation_description,
104                             t_error_buffer);
105 
106  if (t_error_buffer is not NULL) then
107    /*SRW.MESSAGE(0, t_error_buffer);*/null;
108 
109    RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
110 
111  else
112    CONSOLIDATION_NAME   := t_consolidation_name;
113    SubsidLedgerId   := t_from_ledger_id;
114  end if;
115 
116     begin
117   SELECT glr.target_ledger_name
118   INTO SubsidLedgerName
119   FROM gl_ledger_relationships glr, gl_consolidation gcs
120   WHERE glr.target_currency_code = gcs.from_currency_code
121   AND glr.source_ledger_id = gcs.from_ledger_id
122   AND glr.target_ledger_id = gcs.from_ledger_id
123   AND gcs.consolidation_id = P_CONSOLIDATION_ID;
124   exception
125     when NO_DATA_FOUND then
126     t_error_buffer := SQLERRM;
127     /*srw.message(0, t_error_buffer);*/null;
128 
129     raise_application_error(-20101,null);/*srw.program_abort;*/null;
130 
131   end;
132 
133 
134  gl_info.gl_get_ledger_info(t_from_ledger_id,
135                           t_from_chart_of_accounts_id,
136                           t_from_ledger_name,
137                           t_from_currency_code,
138                           t_error_buffer);
139 
140  if (t_error_buffer is not NULL) then
141    /*SRW.MESSAGE(0, t_error_buffer);*/null;
142 
143    RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
144 
145  else
146    STRUCT_NUM            := to_char(t_from_chart_of_accounts_id);
147    FromCurrencyCode := t_from_currency_code;
148  end if;
149 
150  gl_info.gl_get_ledger_info(t_to_ledger_id,
151                           t_to_chart_of_accounts_id,
152                           t_to_ledger_name,
153                           t_to_currency_code,
154                           t_error_buffer);
155 
156  if (t_error_buffer is not NULL) then
157    /*SRW.MESSAGE(0, t_error_buffer);*/null;
158 
159    RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
160 
161  else
162    ParentLedgerName    := t_to_ledger_name;
163    ConsCurrencyCode := t_consolidation_currency_code;
164  end if;
165 
166 
167 
168 P1  := ' EXISTS ( SELECT	sysdate ' ;
169 P2  := '          FROM	 gl_consolidation_accounts	CONS_ACCT, ';
170 P3  := '                 gl_consolidation_history	GLH ';
171 P4  := ' WHERE	GLH.consolidation_id = :P_CONSOLIDATION_ID  ' ;
172 P5  := ' AND	GLH.from_period_name = :P_PERIOD_NAME  ';
173 P6  := ' AND	GLH.average_consolidation_flag = :P_N  ';
174 P7  := ' AND	GLH.actual_flag = :P_A  ';
175 P8  := ' AND	GLH.amount_type = :P_PERIOD_TYPE ';
176 P9  := ' AND	GLH.consolidation_run_id = CONS_ACCT.consolidation_run_id  ';
177 P10_1:=' AND    GLH.consolidation_run_id = ';
178 P10_2:='        (SELECT MAX(glh2.consolidation_run_id) ';
179 P10_3:='         FROM   GL_CONSOLIDATION_HISTORY GLH2 ';
180 P10_4:='         WHERE  glh2.consolidation_id = GLH.consolidation_id ';
181 P10_5:='         AND    glh2.from_period_name = GLH.from_period_name ';
182 P10_6:='         AND    glh2.actual_flag = GLH.actual_flag ';
183 P10_7:='         AND    glh2.amount_type = GLH.amount_type) ';
184 P11 := 'and nvl(GLCC.SEGMENT1,:PZ) between nvl(cons_acct.SEGMENT1_low,:PZ) and nvl(cons_acct.SEGMENT1_high,:PZ) ';
185 P12 := 'and nvl(GLCC.SEGMENT2,:PZ) between nvl(cons_acct.SEGMENT2_low,:PZ) and nvl(cons_acct.SEGMENT2_high,:PZ) ';
186 P13 := 'and nvl(GLCC.SEGMENT3,:PZ) between nvl(cons_acct.SEGMENT3_low,:PZ) and nvl(cons_acct.SEGMENT3_high,:PZ) ';
187 P14 := 'and nvl(GLCC.SEGMENT4,:PZ) between nvl(cons_acct.SEGMENT4_low,:PZ) and nvl(cons_acct.SEGMENT4_high,:PZ) ';
188 P15 := 'and nvl(GLCC.SEGMENT5,:PZ) between nvl(cons_acct.SEGMENT5_low,:PZ) and nvl(cons_acct.SEGMENT5_high,:PZ) ';
189 P16 := 'and nvl(GLCC.SEGMENT6,:PZ) between nvl(cons_acct.SEGMENT6_low,:PZ) and nvl(cons_acct.SEGMENT6_high,:PZ) ';
190 P17 := 'and nvl(GLCC.SEGMENT7,:PZ) between nvl(cons_acct.SEGMENT7_low,:PZ) and nvl(cons_acct.SEGMENT7_high,:PZ) ';
191 P18 := 'and nvl(GLCC.SEGMENT8,:PZ) between nvl(cons_acct.SEGMENT8_low,:PZ) and nvl(cons_acct.SEGMENT8_high,:PZ) ';
192 P19 := 'and nvl(GLCC.SEGMENT9,:PZ) between nvl(cons_acct.SEGMENT9_low,:PZ) and nvl(cons_acct.SEGMENT9_high,:PZ) ';
193 P20 := 'and nvl(GLCC.SEGMENT10,:PZ) between nvl(cons_acct.SEGMENT10_low,:PZ) and nvl(cons_acct.SEGMENT10_high,:PZ) ';
194 P21 := 'and nvl(GLCC.SEGMENT11,:PZ) between nvl(cons_acct.SEGMENT11_low,:PZ) and nvl(cons_acct.SEGMENT11_high,:PZ) ';
195 P22 := 'and nvl(GLCC.SEGMENT12,:PZ) between nvl(cons_acct.SEGMENT12_low,:PZ) and nvl(cons_acct.SEGMENT12_high,:PZ) ';
196 P23 := 'and nvl(GLCC.SEGMENT13,:PZ) between nvl(cons_acct.SEGMENT13_low,:PZ) and nvl(cons_acct.SEGMENT13_high,:PZ) ';
197 P24 := 'and nvl(GLCC.SEGMENT14,:PZ) between nvl(cons_acct.SEGMENT14_low,:PZ) and nvl(cons_acct.SEGMENT14_high,:PZ) ';
198 P25 := 'and nvl(GLCC.SEGMENT15,:PZ) between nvl(cons_acct.SEGMENT15_low,:PZ) and nvl(cons_acct.SEGMENT15_high,:PZ) ';
199 P26 := 'and nvl(GLCC.SEGMENT16,:PZ) between nvl(cons_acct.SEGMENT16_low,:PZ) and nvl(cons_acct.SEGMENT16_high,:PZ) ';
200 P27 := 'and nvl(GLCC.SEGMENT17,:PZ) between nvl(cons_acct.SEGMENT17_low,:PZ) and nvl(cons_acct.SEGMENT17_high,:PZ) ';
201 P28 := 'and nvl(GLCC.SEGMENT18,:PZ) between nvl(cons_acct.SEGMENT18_low,:PZ) and nvl(cons_acct.SEGMENT18_high,:PZ) ';
202 P29 := 'and nvl(GLCC.SEGMENT19,:PZ) between nvl(cons_acct.SEGMENT19_low,:PZ) and nvl(cons_acct.SEGMENT19_high,:PZ) ';
203 P30 := 'and nvl(GLCC.SEGMENT20,:PZ) between nvl(cons_acct.SEGMENT20_low,:PZ) and nvl(cons_acct.SEGMENT20_high,:PZ) ';
204 P31 := 'and nvl(GLCC.SEGMENT21,:PZ) between nvl(cons_acct.SEGMENT21_low,:PZ) and nvl(cons_acct.SEGMENT21_high,:PZ) ';
205 P32 := 'and nvl(GLCC.SEGMENT22,:PZ) between nvl(cons_acct.SEGMENT22_low,:PZ) and nvl(cons_acct.SEGMENT22_high,:PZ) ';
206 P33 := 'and nvl(GLCC.SEGMENT23,:PZ) between nvl(cons_acct.SEGMENT23_low,:PZ) and nvl(cons_acct.SEGMENT23_high,:PZ) ';
207 P34 := 'and nvl(GLCC.SEGMENT24,:PZ) between nvl(cons_acct.SEGMENT24_low,:PZ) and nvl(cons_acct.SEGMENT24_high,:PZ) ';
208 P35 := 'and nvl(GLCC.SEGMENT25,:PZ) between nvl(cons_acct.SEGMENT25_low,:PZ) and nvl(cons_acct.SEGMENT25_high,:PZ) ';
209 P36 := 'and nvl(GLCC.SEGMENT26,:PZ) between nvl(cons_acct.SEGMENT26_low,:PZ) and nvl(cons_acct.SEGMENT26_high,:PZ) ';
210 P37 := 'and nvl(GLCC.SEGMENT27,:PZ) between nvl(cons_acct.SEGMENT27_low,:PZ) and nvl(cons_acct.SEGMENT27_high,:PZ) ';
211 P38 := 'and nvl(GLCC.SEGMENT28,:PZ) between nvl(cons_acct.SEGMENT28_low,:PZ) and nvl(cons_acct.SEGMENT28_high,:PZ) ';
212 P39 := 'and nvl(GLCC.SEGMENT29,:PZ) between nvl(cons_acct.SEGMENT29_low,:PZ) and nvl(cons_acct.SEGMENT29_high,:PZ) ';
213 P40 := 'and nvl(GLCC.SEGMENT30,:PZ) between nvl(cons_acct.SEGMENT30_low,:PZ) and nvl(cons_acct.SEGMENT30_high,:PZ)) ';
214 
215    P_Accounts_Clause := 'N';
216 
217    gl_check_cons_accounts(P_CONSOLIDATION_ID,
218                           'N',
219                           P_A,
220                           P_PERIOD_TYPE,
221                           t_records_present);
222 
223    if ( P_Accounts_Clause = 'Y' ) then
224      P_Accounts := P1 || P2 || P3 || P4 || P5 || P6 || P7 || P8 || P9 ||
225                    P10_1 || P10_2 || P10_3 || P10_4 || P10_5 || P10_6 || P10_7 ||
226                    P11 || P12 || P13 || P14 || P15 || P16 || P17 || P18 || P19 ||
227                    P20 || P21 || P22 || P23 || P24 || P25 || P26 || P27 || P28 || P29 ||
228                    P30 || P31 || P32 || P33 || P34 || P35 || P36 || P37 || P38 || P39 || P40;
229    else
230      P_Accounts := ' 1 = 1 ' ;
231    end if;
232 	IF (P_Accounts IS NULL) then
233 		P_Accounts := '1=1';
234 	END IF;
235    P_Accounts_Clause := 'N';
236    gl_check_cons_accounts(P_CONSOLIDATION_ID,
237                           'Y',
238                           P_A,
239                           P_PERIOD_TYPE,
240                           t_records_present);
241   if (P_Accounts_Clause = 'Y') then
242     P6_ADB  := ' AND	GLH.average_consolidation_flag = :P_Y ';
243     P_Accounts_ADB := P1 || P2 || P3 || P4 || P5 || P6_ADB || P7 || P8 || P9 ||
244                P10_1 || P10_2 || P10_3 || P10_4 || P10_5 || P10_6 || P10_7 ||
245                P11 || P12 || P13 || P14 || P15 || P16 || P17 || P18 || P19 ||
246                P20 || P21 || P22 || P23 || P24 || P25 || P26 || P27 || P28 || P29 ||
247                P30 || P31 || P32 || P33 || P34 || P35 || P36 || P37 || P38 || P39 || P40;
248   else
249     P_Accounts_ADB := ' 1 = 1 ' ;
250   end if;
251 	IF (P_Accounts_Clause IS NULL) then
252 		P_Accounts_Clause := '1=1';
253 	END IF;
254    if ( ConsCurrencyCode = FromCurrencyCode) then
255      if (P_PERIOD_TYPE = 'PTD' ) then
256        P_BALJOIN := '(nvl(glb.period_net_dr,0) <> 0 or nvl(glb.period_net_cr,0) <> 0)';
257      elsif (P_PERIOD_TYPE = 'YTD' ) then
258        P_BALJOIN := '(nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) <> 0 or nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0) <> 0)';
259      elsif (P_PERIOD_TYPE = 'QTD' ) then
260        P_BALJOIN := '(nvl(glb.quarter_to_date_dr,0) + nvl(glb.period_net_dr,0) <> 0 or nvl(glb.quarter_to_date_cr,0) + nvl(glb.period_net_cr,0) <> 0)';
261      elsif  (P_PERIOD_TYPE = 'PJTD' ) then
262        P_BALJOIN := '(nvl(glb.project_to_date_dr,0) + nvl(glb.period_net_dr,0) <> 0 or nvl(glb.project_to_date_cr,0) + nvl(glb.period_net_cr,0) <> 0)';
263       end if;
264 
265    else
266       if (P_PERIOD_TYPE = 'PTD' ) then
267        P_BALJOIN := 'nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0) <> 0';
268       elsif (P_PERIOD_TYPE = 'YTD' ) then
269        P_BALJOIN := '(nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0)) - (nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0)) <> 0';
270       elsif (P_PERIOD_TYPE = 'QTD' ) then
271        P_BALJOIN := '(nvl(glb.quarter_to_date_dr,0) + nvl(glb.period_net_dr,0)) - (nvl(glb.quarter_to_date_cr,0) + nvl(glb.period_net_cr,0)) <> 0';
272       elsif (P_PERIOD_TYPE = 'PJTD' ) then
273        P_BALJOIN := '(nvl(glb.project_to_date_dr,0) + nvl(glb.period_net_dr,0)) - (nvl(glb.project_to_date_cr,0) + nvl(glb.period_net_cr,0)) <> 0';
274        end if;
275    end if;
276 
277 	IF (P_BALJOIN IS NULL) then
278 		P_BALJOIN := '1=1';
279 	END IF;
280 
281       if (P_PERIOD_TYPE = 'PATD') then
282 	P_BALJOINAB := '(nvl(gdb.period_average_to_date_num, 0) <> 0)';
283    elsif (P_PERIOD_TYPE = 'QATD') then
284 	P_BALJOINAB := '(nvl(gdb.quarter_average_to_date_num, 0) <> 0)';
285    elsif (P_PERIOD_TYPE = 'YATD') then
286 	P_BALJOINAB := '(nvl(gdb.year_average_to_date_num, 0) <> 0)';
287    elsif (P_PERIOD_TYPE = 'EOD') then
288 	P_BALJOINAB := '(nvl(gdb.end_of_date_balance_num, 0) <> 0)';
289    end if;
290 	  IF (P_BALJOINAB IS NULL) then
291 		P_BALJOINAB := '1=1';
292 	END IF;
293       if (P_PERIOD_TYPE = 'PATD' OR P_PERIOD_TYPE = 'QATD' OR
294        P_PERIOD_TYPE = 'YATD' OR P_PERIOD_TYPE = 'EOD') then
295 	SELECT period_set_name INTO PeriodSetName
296 	FROM   GL_LEDGERS
297 	WHERE  ledger_id = SubsidLedgerID;
298    end if;
299 
300 END;
301 
302 /*SRW.REFERENCE(STRUCT_NUM);*/null;
303 
304 
305  null;
306 /*SRW.REFERENCE(STRUCT_NUM);*/null;
307 
308 
309  null;  return (TRUE);
310 end;
311 
312 procedure gl_get_consolidation_info(
313                            cons_id number, cons_name out nocopy varchar2,
314                            method out varchar2, curr_code out nocopy varchar2,
315                            from_ledid out number, to_ledid out nocopy number,
316                            description out nocopy varchar2,
317                            errbuf out nocopy varchar2) is
318   begin
319     select glc.name, glc.method, glc.from_currency_code,
320            glc.from_ledger_id, glc.to_ledger_id,
321            glc.description
322     into cons_name, method, curr_code, from_ledid, to_ledid,
323          description
324     from gl_consolidation glc
325     where glc.consolidation_id = cons_id;
326 
327   EXCEPTION
328     WHEN NO_DATA_FOUND THEN
329       errbuf := gl_message.get_message('GL_PLL_INVALID_CONSOLID_ID', 'Y',
330                                    'CID', to_char(cons_id));
331   end;
332 
333 procedure gl_check_cons_accounts(
334                            cons_id  number,
335                            avg_flag varchar2,
336                            actual_flag varchar2,
337                            amount_type varchar2,
338                            records_present out nocopy varchar2
339                            ) is
340   begin
341     SELECT 'Y' INTO P_Accounts_Clause
342     FROM   GL_CONSOLIDATION_ACCOUNTS CA
343     WHERE  CA.consolidation_id = cons_id
344     AND    CA.consolidation_run_id =
345            ( SELECT max(CH.consolidation_run_id)
346              FROM   GL_CONSOLIDATION_HISTORY CH
347              WHERE  CH.consolidation_id = cons_id
348              AND    CH.average_consolidation_flag = avg_flag
349              AND    CH.actual_flag = actual_flag
350              AND    CH.amount_type = amount_type )
351     AND ROWNUM < 2;
352   EXCEPTION
353     WHEN NO_DATA_FOUND THEN
354       P_Accounts_Clause := 'N';
355   end;
356 
357 --Functions to refer Oracle report placeholders--
358 
359  Function STRUCT_NUM_p return varchar2 is
360 	Begin
361 	 return STRUCT_NUM;
362 	 END;
363  Function CONSOLIDATION_NAME_p return varchar2 is
364 	Begin
365 	 return CONSOLIDATION_NAME;
366 	 END;
367  Function ParentLedgerName_p return varchar2 is
368 	Begin
369 	 return ParentLedgerName;
370 	 END;
371  Function SubsidLedgerName_p return varchar2 is
372 	Begin
373 	 return SubsidLedgerName;
374 	 END;
375  Function ConsCurrencyCode_p return varchar2 is
376 	Begin
377 	 return ConsCurrencyCode;
378 	 END;
379  Function SubsidLedgerId_p return number is
380 	Begin
381 	 return SubsidLedgerId;
382 	 END;
383  Function FromCurrencyCode_p return varchar2 is
384 	Begin
385 	 return FromCurrencyCode;
386 	 END;
387  Function PeriodSetName_p return varchar2 is
388 	Begin
389 	 return PeriodSetName;
390 	 END;
391 END GL_GLXUSA_XMLP_PKG ;
392 
393