[Home] [Help]
PACKAGE BODY: APPS.AR_ARXRJR_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXRJR_XMLP_PKG AS
2 /* $Header: ARXRJRB.pls 120.1 2008/06/04 10:53:16 npannamp noship $ */
3
4 function BeforeReport return boolean is
5
6 l_ld_sp varchar2(1);
7
8
9 h_sob_id number;
10 h_rep_type varchar2(1);
11 begin
12 /*SRW.USER_EXIT('FND SRWINIT');*/null;
13 /*ADDED AS FIX*/
14 P_REPORT_MODE_T:=NVL(P_REPORT_MODE,'Transaction');
15 P_ORDER_BY_T:= NVL(P_ORDER_BY,'Accounting Flexfield');
16 /*FIX ENDS*/
17
18 rp_message:=null;
19 IF to_number(p_reporting_level) = 1000 THEN
20 l_ld_sp:= mo_utils.check_ledger_in_sp(TO_NUMBER(p_reporting_entity_id));
21
22 IF l_ld_sp = 'N' THEN
23 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
24 rp_message := FND_MESSAGE.get;
25 END IF;
26 END IF;
27
28
29
30 FND_MESSAGE.SET_NAME('AR','AR_REPORT_ACC_NOT_GEN');
31 cp_acc_message := FND_MESSAGE.get;
32
33
34
35 if p_reporting_level = 1000 then
36 h_sob_id := p_reporting_context;
37 elsif p_reporting_level = 3000 then
38 select set_of_books_id into h_sob_id from ar_system_parameters_all
39 where org_id = p_reporting_context;
40 end if;
41
42 h_rep_type:='P';
43
44
45
46 p_hist:= 'ar_cash_receipt_history_all';
47 p_dist:='ar_xla_ard_lines_v';
48 p_gl:='gl_code_combinations';
49 p_batch:= 'ar_batches_all';
50 p_cash:='ar_cash_receipts_all';
51 p_cust:='hz_cust_accounts_all';
52 p_party:='hz_parties';
53 p_site:='hz_cust_site_uses_all';
54 p_rm:='ar_receipt_methods';
55 p_rc:='ar_receipt_classes';
56 p_look:='ar_lookups';
57
58 begin
59 p_reporting_entity_id:=p_reporting_context;
60 xla_mo_reporting_api.initialize(p_reporting_level,p_reporting_entity_id,'AUTO');
61 reporting_context_name:=substrb(xla_mo_reporting_api.get_reporting_entity_name,1,80);
62 reporting_entity_level_name:=reporting_context_name;
63 reporting_level_name:=substrb(xla_mo_reporting_api.get_reporting_level_name,1,30);
64 p_cr_where:=xla_mo_reporting_api.get_predicate('cr',null);
65 p_site_where:=xla_mo_reporting_api.get_predicate('site_uses',null);
66 p_cust_where:=xla_mo_reporting_api.get_predicate('cust_acct',null);
67 select replace(p_cr_where,':p_reporting_entity_id',p_reporting_context),
68 replace(p_cust_where,':p_reporting_entity_id',p_reporting_context),
69 replace(p_site_where,':p_reporting_entity_id',p_reporting_context)
70 into p_cr_where,p_cust_where,p_site_where from dual;
71
72 If p_reporting_level <>3000 then
73 begin
74 select substrb(meaning,1,10) into reporting_context_name from ar_lookups
75 where lookup_code='ALL' and lookup_type='ALL';
76 exception
77 when others then
78 reporting_context_name:=null;
79 end;
80 end if;
81 end;
82
83 declare
84 begin
85
86
87
88
89
90 SELECT sob.name, nvl(p_currency, sob.currency_code)
91 INTO p_company_name, p_currency_disp
92 FROM gl_sets_of_books sob
93 WHERE sob.set_of_books_id = h_sob_id;
94
95
96
97 if p_in_customer_name_low is not null then
98 lp_customer_name_low := ' and party.party_name >=' || ''':p_in_customer_name_low''';
99 select replace(lp_customer_name_low,':p_in_customer_name_low',p_in_customer_name_low)
100 into lp_customer_name_low from dual;
101 end if;
102
103 if p_in_customer_name_high is not null then
104 lp_customer_name_high := ' and party.party_name <= '|| ''':p_in_customer_name_high''';
105 select replace(lp_customer_name_high,':p_in_customer_name_high',p_in_customer_name_high)
106 into lp_customer_name_high from dual;
107 end if;
108
109
110 if p_in_customer_num_low is not null then
111 lp_customer_num_low := ' and cust_acct.account_number>='||''':p_in_customer_num_low''';
112 select replace(lp_customer_num_low,':p_in_customer_num_low',p_in_customer_num_low)
113 into lp_customer_num_low from dual;
114 end if;
115
116 if p_in_customer_num_high is not null then
117 lp_customer_num_high := ' and cust_acct.account_number<='||''':p_in_customer_num_high''';
118 select replace(lp_customer_num_high,':p_in_customer_num_high',p_in_customer_num_high)
119 into lp_customer_num_high from dual;
120 end if;
121
122
123 null;
124
125
126 null;
127
128 if p_in_company_low IS NOT NULL then
129
130 null;
131 lp_company_low := ' and nvl(' || lp_company_low || ',''NULL'') >= ''' || p_in_company_low || ''' ';
132 end if ;
133
134 if p_in_company_high IS NOT NULL then
135
136 null;
137 lp_company_high := ' and nvl(' || lp_company_high || ',''NULL'') <= ''' || p_in_company_high || ''' ';
138 end if ;
139
140
141
142 if p_in_account_low IS NOT NULL then
143
144 null;
145 lp_account_low := ' and '|| lp_account_low1;
146
147 end if ;
148
149 if p_in_account_high IS NOT NULL then
150
151 null;
152 lp_account_high := ' and ' || lp_account_high1;
153
154 end if ;
155
156
157
158 LP_NAME := 'b.name';
159 LP_TRXDATE := 'crh.trx_date';
160 LP_GLDATE := 'crh.gl_date';
161
162 --if p_report_mode = 'Balance' then
163 if p_report_mode_t = 'Balance' then
164 LP_NAME := 'NULL';
165 LP_TRXDATE := 'NULL';
166 LP_GLDATE := 'NULL';
167 end if;
168
169
170 C_BAL_OR_TRANS_AMOUNT := 'DECODE(d.amount_dr, null,
171 DECODE(:p_currency, null,
172 -d.acctd_amount_cr, -d.amount_cr),
173 DECODE(:p_currency, null,
174 d.acctd_amount_dr, d.amount_dr) ) ';
175
176
177 --if p_report_mode = 'Balance' then
178 if p_report_mode_t = 'Balance' then
179 C_BAL_OR_TRANS_AMOUNT := 'SUM (DECODE(d.amount_cr, null,
180 DECODE(:p_currency, null,
181 d.acctd_amount_dr, d.amount_dr),
182 DECODE(:p_currency, null,
183 -d.acctd_amount_cr, -d.amount_cr))) ';
184 end if;
185
186
187 --LP_GROUP_BY := NULL;
188 LP_GROUP_BY := ' ';
189
190 --if p_report_mode = 'Balance' then
191 if p_report_mode_t = 'Balance' then
192 LP_GROUP_BY := ' GROUP BY'||
193 ' '||lp_company_seg||','||
194 ' '||'cr.cash_receipt_id,'||
195 ' '||'st.meaning,'||
196 ' '||lp_accounting_flex||','||
197 ' '||'rc.name,'||
198 ' '||'rm.name,'||
199 ' '||'cr.receipt_number,'||
200 ' '||'party.party_name,'||
201 ' '||'cust_acct.account_number,'||
202 ' '||'site_uses.location ';
203 end if;
204
205
206 --C_HAVING := NULL;
207 --lp_order_by := NULL;
208 C_HAVING := ' ';
209 lp_order_by := ' ';
210
211 --if p_report_mode = 'Balance' then
212 if p_report_mode_t = 'Balance' then
213 C_HAVING := ' '||
214 'HAVING'||' '||'SUM (DECODE(d.amount_cr, null,
215 DECODE(:p_currency, null,
216 d.acctd_amount_dr, d.amount_dr),
217 DECODE(:p_currency, null,
218 -d.acctd_amount_cr,-d.amount_cr))) <> 0';
219 ELSE
220 lp_order_by := ', cr.cash_receipt_id, crh.cash_receipt_history_id';
221 end if;
222
223 end;
224 return (TRUE);
225 end;
226
227 function AfterReport return boolean is
228 begin
229
230 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
231 return (TRUE);
232 end;
233
234 function AfterPForm return boolean is
235 begin
236
237 BEGIN
238 IF p_gl_date_low IS NOT NULL THEN
239 lp_gl_date_low := ' and crh.gl_date >= :p_gl_date_low ';
240 END IF;
241
242 IF p_gl_date_high IS NOT NULL THEN
243 lp_gl_date_high := ' and crh.gl_date <= :p_gl_date_high ';
244 END IF;
245
246
247 IF p_status IS NOT NULL THEN
248 lp_source_type := ' AND d.source_type = :p_status ';
249 END IF;
250
251 IF p_receipt_class IS NOT NULL THEN
252 lp_receipt_class := ' AND rc.name = :p_receipt_class ';
253 END IF;
254
255 IF p_payment_method IS NOT NULL THEN
256 lp_payment_method := ' AND rm.name = :p_payment_method ';
257 END IF;
258
259 IF p_currency IS NOT NULL THEN
260 lp_currency := ' AND cr.currency_code = :p_currency ';
261 END IF;
262
263
264 END;
265
266 return (TRUE);
267 end;
268
269
270 /*added as fix*/
271 function F_ACC_MESSAGEFormatTrigger return VARCHAR2 is
272 temp boolean;
273 begin
274 temp:=(arp_util.open_period_exists(p_reporting_level,p_reporting_entity_id,p_gl_date_low,p_gl_date_high));
275 if temp then
276 FACCMSG:='TRUE';
277 else
278 FACCMSG:='FALSE';
279 end if;
280 RETURN (FACCMSG);
281
282 end;
283
284 --Functions to refer Oracle report placeholders--
285
286 Function RP_DATA_FOUND_p return varchar2 is
287 Begin
288 return RP_DATA_FOUND;
289 END;
290 Function C_BAL_OR_TRANS_AMOUNT_p return varchar2 is
291 Begin
292 return C_BAL_OR_TRANS_AMOUNT;
293 END;
294 Function C_HAVING_p return varchar2 is
295 Begin
296 return C_HAVING;
297 END;
298 Function reporting_level_name_p return varchar2 is
299 Begin
300 return reporting_level_name;
301 END;
302 Function reporting_context_name_p return varchar2 is
303 Begin
304 return reporting_context_name;
305 END;
306 Function Reporting_entity_level_name_p return varchar2 is
307 Begin
308 return Reporting_entity_level_name;
309 END;
310 Function rp_message_p return varchar2 is
311 Begin
312 return rp_message;
313 END;
314 Function CP_ACC_MESSAGE_p return varchar2 is
315 Begin
316 return CP_ACC_MESSAGE;
317 END;
318 END AR_ARXRJR_XMLP_PKG ;
319