[Home] [Help]
PACKAGE BODY: APPS.AR_RAXGLA_XMLP_PKG
Source
1 PACKAGE BODY AR_RAXGLA_XMLP_PKG AS
2 /* $Header: RAXGLAB.pls 120.0 2007/12/27 14:19:24 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_LD_SP VARCHAR2(1);
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 RP_MESSAGE := NULL;
9 IF TO_NUMBER(P_REPORTING_LEVEL) = 1000 THEN
10 L_LD_SP := MO_UTILS.CHECK_LEDGER_IN_SP(TO_NUMBER(P_REPORTING_ENTITY_ID));
11 IF L_LD_SP = 'N' THEN
12 FND_MESSAGE.SET_NAME('FND'
13 ,'FND_MO_RPT_PARTIAL_LEDGER');
14 RP_MESSAGE := FND_MESSAGE.GET;
15 END IF;
16 END IF;
17 FND_MESSAGE.SET_NAME('AR'
18 ,'AR_REPORT_ACC_NOT_GEN');
19 CP_ACC_MESSAGE := FND_MESSAGE.GET;
20 DECLARE
21 CUST_LOW VARCHAR2(200);
22 CUST_HIGH VARCHAR2(200);
23 BEGIN
24 IF (P_CURRENCY_CODE IS NOT NULL) THEN
25 P_WHERE_1 := ' AND trx.invoice_currency_code = ''' || P_CURRENCY_CODE || '''';
26 P_SELECT_1 := ' sum(decode(sign(gl_dist.amount),
27 1, round(gl_dist.amount, 2), NULL)) curr_credit_amount,
28 sum(decode(sign(gl_dist.amount),
29 -1, -round(gl_dist.amount, 2), NULL)) curr_debit_amount ';
30 END IF;
31 IF (P_GL_ACCOUNT_TYPE = 'TAX') THEN
32 P_WHERE_GL_TYPE := 'and Account_class = ''TAX'' ';
33 ELSIF (P_GL_ACCOUNT_TYPE = 'UNBILL') THEN
34 P_WHERE_GL_TYPE := 'and Account_class = ''UNBILL'' ';
35 ELSIF (P_GL_ACCOUNT_TYPE = 'UNEARN') THEN
36 P_WHERE_GL_TYPE := 'and Account_class = ''UNEARN'' ';
37 ELSIF (P_GL_ACCOUNT_TYPE = 'SUSPENSE') THEN
38 P_WHERE_GL_TYPE := 'and Account_class = ''SUSPENSE'' ';
39 ELSIF (P_GL_ACCOUNT_TYPE = 'FREIGHT') THEN
40 P_WHERE_GL_TYPE := 'and Account_class = ''FREIGHT'' ';
41 ELSIF (P_GL_ACCOUNT_TYPE = 'REV') THEN
42 P_WHERE_GL_TYPE := 'and Account_class = ''REV'' ';
43 ELSIF (P_GL_ACCOUNT_TYPE = 'REC') THEN
44 P_WHERE_GL_TYPE := 'and Account_class = ''REC'' ';
45 ELSIF (P_GL_ACCOUNT_TYPE = 'ROUND') THEN
46 P_WHERE_GL_TYPE := 'and Account_class = ''ROUND'' ';
47 ELSIF (P_GL_ACCOUNT_TYPE IS NULL) THEN
48 P_WHERE_GL_TYPE := ' ';
49 END IF;
50 /*SRW.REFERENCE(P_COAID)*/NULL;
51 IF P_COMPANY_START IS NOT NULL THEN
52 LP_COMPANY_START := 'and ' || LP_COMPANY_START;
53 END IF;
54 IF P_COMPANY_END IS NOT NULL THEN
55 LP_COMPANY_END := 'and ' || LP_COMPANY_END;
56 END IF;
57 END;
58 RETURN (TRUE);
59 END BEFOREREPORT;
60
61 FUNCTION AFTERREPORT RETURN BOOLEAN IS
62 BEGIN
63 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
64 RETURN (TRUE);
65 END AFTERREPORT;
66
67 FUNCTION REPORT_NAMEFORMULA(COMPANY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
68 BEGIN
69 DECLARE
70 L_REPORT_NAME VARCHAR2(80);
71 BEGIN
72 RP_COMPANY_NAME := COMPANY_NAME;
73 SELECT
74 CP.USER_CONCURRENT_PROGRAM_NAME
75 INTO L_REPORT_NAME
76 FROM
77 FND_CONCURRENT_PROGRAMS_VL CP,
78 FND_CONCURRENT_REQUESTS CR
79 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
80 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
81 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
82 RP_REPORT_NAME := L_REPORT_NAME;
83 RETURN (L_REPORT_NAME);
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 RP_REPORT_NAME := 'Sales Journal By Customer';
87 RETURN ('Sales Journal By GL Account');
88 END;
89 RETURN NULL;
90 END REPORT_NAMEFORMULA;
91
92 FUNCTION REPORT_SUBTITLEFORMULA RETURN VARCHAR2 IS
93 BEGIN
94 RP_REPORT_SUBTITLE := 'GL Date ' || NVL(TO_CHAR(P_START_GL_DATE
95 ,'DD-MON-YYYY')
96 ,' ') || ' - ' || NVL(TO_CHAR(P_END_GL_DATE
97 ,'DD-MON-YYYY')
98 ,' ');
99 RETURN NULL;
100 END REPORT_SUBTITLEFORMULA;
101
102 FUNCTION C_SUM_TEXTFORMULA RETURN VARCHAR2 IS
103 BEGIN
104 IF P_GL_ACCOUNT_TYPE = 'ALL' THEN
105 RETURN ('Subtotal by Invoice Currency : ');
106 ELSE
107 RETURN ('Totals : ');
108 END IF;
109 RETURN NULL;
110 END C_SUM_TEXTFORMULA;
111
112 FUNCTION FILTER_NULL RETURN BOOLEAN IS
113 BEGIN
114 RETURN (TRUE);
115 END FILTER_NULL;
116
117 FUNCTION SET_CURR_CODE_REVFORMULA(CURRENCY_CODE_REV IN VARCHAR2) RETURN VARCHAR2 IS
118 BEGIN
119 BEGIN
120 /*SRW.REFERENCE(CURRENCY_CODE_REV)*/NULL;
121 C_CURR_CODE_REV := CURRENCY_CODE_REV;
122 RETURN (CURRENCY_CODE_REV);
123 END;
124 RETURN NULL;
125 END SET_CURR_CODE_REVFORMULA;
126
127 FUNCTION C_REPORT_BY_LINE_MEANINGFORMUL RETURN VARCHAR2 IS
128 BEGIN
129 DECLARE
130 L_MEANING VARCHAR2(80);
131 BEGIN
132 SELECT
133 MEANING
134 INTO L_MEANING
135 FROM
136 AR_LOOKUPS
137 WHERE LOOKUP_TYPE = 'YES/NO'
138 AND LOOKUP_CODE = P_REPORT_BY_LINE;
139 RP_REPORT_BY_LINE_MEANING := L_MEANING;
140 RETURN (L_MEANING);
141 END;
142 RETURN NULL;
143 END C_REPORT_BY_LINE_MEANINGFORMUL;
144
145 FUNCTION AFTERPFORM RETURN BOOLEAN IS
146 BEGIN
147 BEGIN
148 XLA_MO_REPORTING_API.INITIALIZE(P_REPORTING_LEVEL
149 ,P_REPORTING_ENTITY_ID
150 ,'AUTO');
151 P_REPORTING_ENTITY_NAME := SUBSTRB(XLA_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME
152 ,1
153 ,200);
154 P_REPORTING_LEVEL_NAME := SUBSTRB(XLA_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME
155 ,1
156 ,30);
157 P_ORG_WHERE_C := XLA_MO_REPORTING_API.GET_PREDICATE('C'
158 ,NULL);
159 P_ORG_WHERE_TRX := XLA_MO_REPORTING_API.GET_PREDICATE('TRX'
160 ,NULL);
161 P_ORG_WHERE_LINES := XLA_MO_REPORTING_API.GET_PREDICATE('LINES'
162 ,NULL);
163 P_ORG_WHERE_LINK_LINE := XLA_MO_REPORTING_API.GET_PREDICATE('LINK_LINE'
164 ,NULL);
165 P_ORG_WHERE_GL_DIST := XLA_MO_REPORTING_API.GET_PREDICATE('GL_DIST'
166 ,NULL);
167 P_ORG_WHERE_PARAM := XLA_MO_REPORTING_API.GET_PREDICATE('PARAM'
168 ,NULL);
169 IF P_START_GL_DATE IS NOT NULL THEN
170 LP_START_GL_DATE := ' and gl_dist.gl_date >= :p_start_gl_date ';
171 END IF;
172 IF P_END_GL_DATE IS NOT NULL THEN
173 LP_END_GL_DATE := ' and gl_dist.gl_date <= :p_end_gl_date ';
174 END IF;
175 IF P_TRX_DATE_HIGH IS NOT NULL THEN
176 LP_TRX_DATE_HIGH := ' and trx.trx_date <= :p_trx_date_high ';
177 END IF;
178 IF P_TRX_DATE_LOW IS NOT NULL THEN
179 LP_TRX_DATE_LOW := ' and trx.trx_date >= :p_trx_date_low ';
180 END IF;
181 IF (P_TRX_TYPE_LOW IS NOT NULL) THEN
182 LP_TRX_TYPE_LOW := 'and arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'') >= :p_trx_type_low ';
183 END IF;
184 IF (P_TRX_TYPE_HIGH IS NOT NULL) THEN
185 LP_TRX_TYPE_HIGH := 'and arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'') <= :p_trx_type_high ';
186 END IF;
187 IF (P_TRX_NUMBER_LOW IS NOT NULL) THEN
188 LP_TRX_NUMBER_LOW := 'and trx.trx_number >= :p_trx_number_low ';
189 END IF;
190 IF (P_TRX_NUMBER_HIGH IS NOT NULL) THEN
191 LP_TRX_NUMBER_HIGH := 'and trx.trx_number <= :p_trx_number_high ';
192 END IF;
193 IF (P_CUSTOMER_LOW IS NOT NULL) THEN
194 LP_CUSTOMER_LOW := 'and party.party_name >= :p_customer_low ';
195 END IF;
196 IF (P_CUSTOMER_HIGH IS NOT NULL) THEN
197 LP_CUSTOMER_HIGH := 'and party.party_name <= :p_customer_high ';
198 END IF;
199 IF (P_CUSTOMER_NUMBER_LOW IS NOT NULL) THEN
200 LP_CUSTOMER_NUMBER_LOW := 'and c.account_number >= :p_customer_number_low ';
201 END IF;
202 IF (P_CUSTOMER_NUMBER_HIGH IS NOT NULL) THEN
203 LP_CUSTOMER_NUMBER_HIGH := 'and c.account_number <= :p_customer_number_high ';
204 END IF;
205 IF (P_REPORT_BY_LINE = 'Y') THEN
206 P_LINE_NUMBER := 'decode( gl_dist.account_class,
207 ''REC'' , ''0'',
208 ''FREIGHT'' , decode(lines.link_to_cust_trx_line_id,
209 '''' , ''0'' ,
210 link_line.line_number),
211 nvl(link_line.line_number, lines.line_number)
212 )';
213 P_LINE_NUMBER_ORDER := 'decode( gl_dist.account_class,
214 ''REC'', -10,
215 ''FREIGHT'' , decode(lines.link_to_cust_trx_line_id,
216 '''' , -10,
217 link_line.line_number),
218 nvl(link_line.line_number, lines.line_number)
219 )';
220 ELSE
221 P_LINE_NUMBER := 'null';
222 END IF;
223 IF (P_ZERO_ROUND = 'Y') THEN
224 LP_ZERO_ROUND := ' and (gl_dist.account_class <> ''ROUND'' OR ' || ' (gl_dist.account_class = ''ROUND'' and gl_dist.acctd_amount <> 0)) ';
225 ELSE
226 LP_ZERO_ROUND := NULL;
227 END IF;
228 END;
229 RETURN (TRUE);
230 END AFTERPFORM;
231
232 FUNCTION LINE_NUMBER_DISPLAYFORMULA(LINE_NUMBER IN VARCHAR2) RETURN VARCHAR2 IS
233 BEGIN
234 BEGIN
235 IF (LINE_NUMBER = '0') THEN
236 RETURN ('All');
237 ELSE
238 RETURN (LINE_NUMBER);
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242 RETURN (LINE_NUMBER);
243 END;
244 RETURN NULL;
245 END LINE_NUMBER_DISPLAYFORMULA;
246
247 FUNCTION OUT_OF_BALANCEFORMULA(SUM_CURR_CR_AMT_SEG_REV IN NUMBER
248 ,SUM_CURR_DR_AMT_SEG_REV IN NUMBER) RETURN VARCHAR2 IS
249 BEGIN
250 IF (SUM_CURR_CR_AMT_SEG_REV <> SUM_CURR_DR_AMT_SEG_REV) THEN
251 RETURN ('*');
252 ELSE
253 RETURN (' ');
254 END IF;
255 RETURN NULL;
256 END OUT_OF_BALANCEFORMULA;
257
258 FUNCTION C_POSTING_STATUS_MEANINGFORMUL RETURN VARCHAR2 IS
259 BEGIN
260 DECLARE
261 STATUS_MEANING VARCHAR2(200);
262 BEGIN
263 STATUS_MEANING := '';
264 SELECT
265 MEANING
266 INTO STATUS_MEANING
267 FROM
268 AR_LOOKUPS
269 WHERE LOOKUP_TYPE = 'POSTING_STATUS'
270 AND LOOKUP_CODE = NVL(P_POSTING_STATUS
271 ,'ALL');
272 RETURN (STATUS_MEANING);
273 END;
274 RETURN NULL;
275 END C_POSTING_STATUS_MEANINGFORMUL;
276
277 FUNCTION C_GL_ACCOUNT_TYPE_MEANINGFORMU RETURN VARCHAR2 IS
278 BEGIN
279 DECLARE
280 ACCT_MEANING VARCHAR2(200);
281 BEGIN
282 ACCT_MEANING := '';
283 IF (P_GL_ACCOUNT_TYPE IS NOT NULL) THEN
284 SELECT
285 MEANING
286 INTO ACCT_MEANING
287 FROM
288 AR_LOOKUPS
289 WHERE LOOKUP_TYPE = 'AUTOGL_TYPE'
290 AND LOOKUP_CODE = P_GL_ACCOUNT_TYPE;
291 END IF;
292 RETURN (ACCT_MEANING);
293 END;
294 RETURN NULL;
295 END C_GL_ACCOUNT_TYPE_MEANINGFORMU;
296
297 FUNCTION C_ORDER_BY_MEANINGFORMULA RETURN VARCHAR2 IS
298 BEGIN
299 DECLARE
300 ORDER_MEANING VARCHAR2(200);
301 BEGIN
302 ORDER_MEANING := '';
303 IF (P_SORT_BY IS NOT NULL) THEN
304 SELECT
305 MEANING
306 INTO ORDER_MEANING
307 FROM
308 AR_LOOKUPS
309 WHERE LOOKUP_TYPE = 'SORT_BY_RAXGLR'
310 AND LOOKUP_CODE = P_SORT_BY;
311 END IF;
312 RETURN (ORDER_MEANING);
313 END;
314 RETURN NULL;
315 END C_ORDER_BY_MEANINGFORMULA;
316
317 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
318 BEGIN
319 RETURN (TRUE);
320 END BEFOREPFORM;
321
322 FUNCTION ACCT_BAL_APROMPT_P RETURN VARCHAR2 IS
323 BEGIN
324 RETURN ACCT_BAL_APROMPT;
325 END ACCT_BAL_APROMPT_P;
326
327 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
328 BEGIN
329 RETURN RP_COMPANY_NAME;
330 END RP_COMPANY_NAME_P;
331
332 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
333 BEGIN
334 RETURN RP_REPORT_NAME;
335 END RP_REPORT_NAME_P;
336
337 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
338 BEGIN
339 RETURN RP_DATA_FOUND;
340 END RP_DATA_FOUND_P;
341
342 FUNCTION RP_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
343 BEGIN
344 RETURN RP_REPORT_SUBTITLE;
345 END RP_REPORT_SUBTITLE_P;
346
347 FUNCTION GSUM_CURR_CR_AMT_P RETURN VARCHAR2 IS
348 BEGIN
349 RETURN GSUM_CURR_CR_AMT;
350 END GSUM_CURR_CR_AMT_P;
351
352 FUNCTION GSUM_CURR_DR_AMT_P RETURN VARCHAR2 IS
353 BEGIN
354 RETURN GSUM_CURR_DR_AMT;
355 END GSUM_CURR_DR_AMT_P;
356
357 FUNCTION C_CURR_CODE_REV_P RETURN VARCHAR2 IS
358 BEGIN
359 RETURN C_CURR_CODE_REV;
360 END C_CURR_CODE_REV_P;
361
362 FUNCTION RP_REPORT_BY_LINE_MEANING_P RETURN VARCHAR2 IS
363 BEGIN
364 RETURN RP_REPORT_BY_LINE_MEANING;
365 END RP_REPORT_BY_LINE_MEANING_P;
366
367 FUNCTION C_DATA_FOUND_FLAG_P RETURN VARCHAR2 IS
368 BEGIN
369 RETURN C_DATA_FOUND_FLAG;
370 END C_DATA_FOUND_FLAG_P;
371
372 FUNCTION RP_MESSAGE_P RETURN VARCHAR2 IS
373 BEGIN
374 RETURN RP_MESSAGE;
375 END RP_MESSAGE_P;
376
377 FUNCTION CP_ACC_MESSAGE_P RETURN VARCHAR2 IS
378 BEGIN
379 RETURN CP_ACC_MESSAGE;
380 END CP_ACC_MESSAGE_P;
381
382 END AR_RAXGLA_XMLP_PKG;
383