[Home] [Help]
PACKAGE BODY: APPS.AR_ARXAPIPM_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXAPIPM_XMLP_PKG AS
2 /* $Header: ARXAPIPMB.pls 120.1 2011/03/08 06:35:35 dgaurab ship $ */
3
4 function report_nameformula(Company_Name in varchar2, functional_currency in varchar2) return varchar2 is
5 begin
6
7 DECLARE
8 l_report_name VARCHAR2(80);
9 BEGIN
10 RP_Company_Name := Company_Name;
11 RP_Functional_Currency := functional_currency;
12 SELECT substr(cp.user_concurrent_program_name, 1, 80)
13 INTO l_report_name
14 FROM FND_CONCURRENT_PROGRAMS_VL cp,
15 FND_CONCURRENT_REQUESTS cr
16 WHERE cr.request_id = P_CONC_REQUEST_ID
17 AND cp.application_id = cr.program_application_id
18 AND cp.concurrent_program_id = cr.concurrent_program_id;
19
20 RP_Report_Name := l_report_name;
21 RP_Report_Name := substr(RP_Report_Name,1,instr(RP_Report_Name,' (XML)'));
22
23 RETURN(l_report_name);
24 EXCEPTION
25 WHEN NO_DATA_FOUND
26 THEN RP_REPORT_NAME := 'Transactions Awaiting Consolidation';
27 RETURN('Transactions Awaiting Consolidation');
28 END;
29 RETURN NULL; end;
30
31 function BeforeReport return boolean is
32 begin
33
34 begin
35 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
36 /*SRW.USER_EXIT('FND SRWINIT');*/null;
37 --ADDED AS FIX
38 P_INV_DATE_LOW_T :=to_char(P_INV_DATE_LOW,'DD-MON-YY');
39 P_INV_DATE_HIGH_T :=to_char(P_INV_DATE_HIGH,'DD-MON-YY');
40 P_DUE_DATE_LOW_T :=to_char(P_DUE_DATE_LOW,'DD-MON-YY');
41 P_DUE_DATE_HIGH_T :=to_char(P_DUE_DATE_HIGH,'DD-MON-YY');
42
43
44
45 end;
46 return (TRUE);
47 end;
48
49 function Sub_TitleFormula return VARCHAR2 is
50 begin
51 --Bug11799279
52 IF P_INV_DATE_LOW IS NOT NULL THEN
53 RP_SUB_TITLE1 := fnd_date.date_to_chardate(P_INV_DATE_LOW, calendar_aware=> FND_DATE.calendar_aware_alt)||
54 ' - '||
55 fnd_date.date_to_chardate(P_INV_DATE_HIGH, calendar_aware=> FND_DATE.calendar_aware_alt);
56 END IF;
57 IF P_DUE_DATE_LOW IS NOT NULL THEN
58 RP_SUB_TITLE2 := fnd_date.date_to_chardate(P_DUE_DATE_LOW, calendar_aware=> FND_DATE.calendar_aware_alt)||
59 ' - '||
60 fnd_date.date_to_chardate(P_DUE_DATE_HIGH, calendar_aware=> FND_DATE.calendar_aware_alt);
61 END IF;
62
63 RETURN NULL; end;
64
65 function AfterReport return boolean is
66 begin
67
68 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
69 return (TRUE);
70 end;
71
72 function AfterPForm return boolean is
73 begin
74
75
76
77 LP_SUM_COLUMN := 'decode(decode(min(lc.lookup_code),
78 ''AVAILABLE_FOR_RECEIPT'', ''PS'',
79 ''STARTED_CREATION'', ''PS'',
80 ''COMPLETED_CREATION'', ''PS'',
81 ''STARTED_APPROVAL'', ''PS'',
82 ''APPL''),
83 ''PS'', sum(pays.amount_due_remaining) ,
84 sum(decode(ra.confirmed_flag,''Y'', 0, ra.amount_applied)))';
85
86 LP_GROUP_BY := ' group by decode (:P_SORT_BY, ''DUE DATE'', null, ''INVOICE NUMBER'', trx.trx_number, null),
87 decode(:P_SORT_BY,''DUE DATE'', pays.due_date, ''INVOICE NUMBER'', pays.due_date, null),
88 decode(:P_SORT_BY,''DUE DATE'', decode(:RP_SUMMARIZE, ''YES'',null, trx.trx_number), null ),
89 trx.invoice_currency_code, lc.meaning, lc.lookup_code, pmt.name, decode(:RP_SUMMARIZE, ''YES'', null, SUBSTRB(PARTY.PARTY_NAME,1,50)), decode(:RP_SUMMARIZE, ''YES'', null, cust.ACCOUNT_NUMBER),
90 decode(:RP_SUMMARIZE, ''YES'', null, su.location),
91 decode(:RP_SUMMARIZE, ''YES'', null, trx.trx_number), decode(:RP_SUMMARIZE, ''YES'', null, trxtype.name), decode(:RP_SUMMARIZE, ''YES'', null, trx.trx_date),
92 decode(:RP_SUMMARIZE, ''YES'', null, batch.name), pays.due_date ';
93
94 IF P_SORT_BY = 'DUE DATE' THEN
95 IF P_SUMMARIZE = 'Y' THEN
96 RP_SUMMARIZE := 'YES';
97 ELSE
98 RP_SUMMARIZE := 'NO';
99 END IF;
100 ELSE
101 RP_SUMMARIZE := 'NO';
102 END IF;
103
104
105
106
107
108 IF P_STATUS IS NOT NULL THEN
109 IF P_STATUS = 'AVAILABLE_FOR_RECEIPT' THEN
110
111 LP_STATUS :=
112 ' and nvl(batch.batch_applied_status,
113 ''AVAILABLE_FOR_RECEIPT'') = :P_STATUS ';
114 ELSE
115
116 LP_STATUS :=
117 ' and nvl(batch.batch_applied_status,
118 ''AVAILABLE_FOR_RECEIPT'') = :P_STATUS and crh.prv_stat_cash_receipt_hist_id IS NULL';
119 END IF;
120 END IF;
121
122 IF P_INV_DATE_LOW IS NOT NULL THEN
123 LP_INV_DATE :=
124 ' and trx.trx_date >= :p_inv_date_low';
125 END IF;
126 IF P_INV_DATE_HIGH IS NOT NULL THEN
127 LP_INV_DATE := LP_INV_DATE ||
128 ' and trx.trx_date <= :p_inv_date_high';
129 END IF;
130
131 IF P_INV_NUM_LOW IS NOT NULL THEN
132 LP_INV_NUM :=
133 ' and trx.trx_number >= :p_inv_num_low';
134 END IF;
135 IF P_INV_NUM_HIGH IS NOT NULL THEN
136 LP_INV_NUM := LP_INV_NUM ||
137 ' and trx.trx_number <= :p_inv_num_high';
138 END IF;
139
140 IF P_DUE_DATE_LOW IS NOT NULL THEN
141 LP_DUE_DATE :=
142 ' and pays.due_date >= :p_due_date_low';
143 END IF;
144 IF P_DUE_DATE_HIGH IS NOT NULL THEN
145 LP_DUE_DATE := LP_DUE_DATE ||
146 ' and pays.due_date <= :p_due_date_high';
147 END IF;
148
149 IF P_PMT_MTD IS NOT NULL THEN
150 LP_PMT_METHOD :=
151 ' and pmt.name = :p_pmt_mtd';
152 END IF;
153
154 IF P_CUST_NAME IS NOT NULL THEN
155 LP_CUST_NAME :=
156 ' and PARTY.PARTY_NAME = :p_cust_name';
157 END IF;
158
159
160 IF P_CUST_NUMBER IS NOT NULL THEN
161 LP_CUST_NUM :=
162 ' and cust.ACCOUNT_NUMBER = :p_cust_number';
163 END IF;
164
165 IF P_INV_NUM_LOW IS NOT NULL THEN
166 LP_INV_NUM :=
167 ' and trx.trx_number >= :p_inv_num_low';
168 END IF;
169 IF P_INV_NUM_HIGH IS NOT NULL THEN
170 LP_INV_NUM := LP_INV_NUM ||
171 ' and trx.trx_number <= :p_inv_num_high';
172 END IF;
173
174
175 IF P_INV_TYPE IS NOT NULL THEN
176 LP_INV_TYPE :=
177 ' and trxtype.name = :p_inv_type';
178 END IF;
179
180 IF P_CURRENCY IS NOT NULL THEN
181 LP_CURRENCY :=
182 ' and trx.invoice_currency_code = :p_currency';
183 END IF; return (TRUE);
184 end;
185
186 function RP_DSP_SORT_BYFormula return VARCHAR2 is
187 begin
188
189 DECLARE
190 l_sort_by varchar(50);
191 BEGIN
192 SELECT meaning
193 INTO l_sort_by
194 FROM AR_LOOKUPS
195 WHERE lookup_code = P_SORT_BY
196 AND lookup_type = 'SORT_BY_ARXAPIPM';
197
198 RETURN(l_sort_by);
199
200 EXCEPTION
201 WHEN OTHERS THEN RETURN NULL;
202 END;
203 RETURN NULL; end;
204
205 function RP_DSP_SUMMARIZEFormula return VARCHAR2 is
206 begin
207
208 DECLARE
209 l_summarize varchar(50);
210 BEGIN
211 SELECT meaning
212 INTO l_summarize
213 FROM FND_LOOKUPS
214 WHERE lookup_code = P_SUMMARIZE
215 AND lookup_type = 'YES_NO';
216
217 RETURN(l_summarize);
218
219 EXCEPTION
220 WHEN OTHERS THEN RETURN NULL;
221 END;
222 RETURN NULL; end;
223
224 function RP_DSP_STATUSFormula return VARCHAR2 is
225 begin
226
227 DECLARE
228 l_status varchar(50);
229 BEGIN
230 SELECT meaning
231 INTO l_status
232 FROM AR_LOOKUPS
233 WHERE lookup_code = P_STATUS
234 AND lookup_type = 'ARXAPIPM_BATCH_APPLIED_STATUS';
235
236 RETURN(l_status);
237
238 EXCEPTION
239 WHEN OTHERS THEN RETURN NULL;
240 END;
241 RETURN NULL; end;
242
243 function CF_report_dateFormula return Char is
244 begin
245 --Bug11799279
246 return(fnd_date.date_to_chardt(SYSDATE, calendar_aware=> FND_DATE.calendar_aware_alt));
247 end;
248
249 --Functions to refer Oracle report placeholders--
250
251 Function RP_COMPANY_NAME_p return varchar2 is
252 Begin
253 return RP_COMPANY_NAME;
254 END;
255 Function RP_REPORT_NAME_p return varchar2 is
256 Begin
257 return RP_REPORT_NAME;
258 END;
259 Function RP_SUB_TITLE1_p return varchar2 is
260 Begin
261 return RP_SUB_TITLE1;
262 END;
263 Function RP_SUB_TITLE2_p return varchar2 is
264 Begin
265 return RP_SUB_TITLE2;
266 END;
267 Function RP_DATA_FOUND_p return varchar2 is
268 Begin
269 return RP_DATA_FOUND;
270 END;
271 Function RP_FUNCTIONAL_CURRENCY_p return varchar2 is
272 Begin
273 return RP_FUNCTIONAL_CURRENCY;
274 END;
275 Function D_SUM_AMOUNT_DUE_CURRFormula return VARCHAR2 is
276 begin
277 RP_DATA_FOUND := '1';
278 return null;
279 end;
280 END AR_ARXAPIPM_XMLP_PKG ;
281
282