1 PACKAGE BODY AR_ARXAPIPM_XMLP_PKG AS
2 /* $Header: ARXAPIPMB.pls 120.0 2007/12/27 13:26:56 abraghun noship $ */
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
52 IF P_INV_DATE_LOW IS NOT NULL THEN
53 RP_SUB_TITLE1 := fnd_date.date_to_chardate(P_INV_DATE_LOW)||' - '|| fnd_date.date_to_chardate(P_INV_DATE_HIGH);
54 END IF;
55 IF P_DUE_DATE_LOW IS NOT NULL THEN
56 RP_SUB_TITLE2 := fnd_date.date_to_chardate(P_DUE_DATE_LOW)||' - '|| fnd_date.date_to_chardate(P_DUE_DATE_HIGH);
57 END IF;
58
59 RETURN NULL; end;
60
61 function AfterReport return boolean is
62 begin
63
64 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
65 return (TRUE);
66 end;
67
68 function AfterPForm return boolean is
69 begin
70
71
72
73 LP_SUM_COLUMN := 'decode(decode(min(lc.lookup_code),
74 ''AVAILABLE_FOR_RECEIPT'', ''PS'',
75 ''STARTED_CREATION'', ''PS'',
76 ''COMPLETED_CREATION'', ''PS'',
77 ''STARTED_APPROVAL'', ''PS'',
78 ''APPL''),
79 ''PS'', sum(pays.amount_due_remaining) ,
80 sum(decode(ra.confirmed_flag,''Y'', 0, ra.amount_applied)))';
81
82 LP_GROUP_BY := ' group by decode (:P_SORT_BY, ''DUE DATE'', null, ''INVOICE NUMBER'', trx.trx_number, null),
83 decode(:P_SORT_BY,''DUE DATE'', pays.due_date, ''INVOICE NUMBER'', pays.due_date, null),
84 decode(:P_SORT_BY,''DUE DATE'', decode(:RP_SUMMARIZE, ''YES'',null, trx.trx_number), null ),
85 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),
86 decode(:RP_SUMMARIZE, ''YES'', null, su.location),
87 decode(:RP_SUMMARIZE, ''YES'', null, trx.trx_number), decode(:RP_SUMMARIZE, ''YES'', null, trxtype.name), decode(:RP_SUMMARIZE, ''YES'', null, trx.trx_date),
88 decode(:RP_SUMMARIZE, ''YES'', null, batch.name), pays.due_date ';
89
90 IF P_SORT_BY = 'DUE DATE' THEN
91 IF P_SUMMARIZE = 'Y' THEN
92 RP_SUMMARIZE := 'YES';
93 ELSE
94 RP_SUMMARIZE := 'NO';
95 END IF;
96 ELSE
97 RP_SUMMARIZE := 'NO';
98 END IF;
99
100
101
102
103
104 IF P_STATUS IS NOT NULL THEN
105 IF P_STATUS = 'AVAILABLE_FOR_RECEIPT' THEN
106
107 LP_STATUS :=
108 ' and nvl(batch.batch_applied_status,
109 ''AVAILABLE_FOR_RECEIPT'') = :P_STATUS ';
110 ELSE
111
112 LP_STATUS :=
113 ' and nvl(batch.batch_applied_status,
114 ''AVAILABLE_FOR_RECEIPT'') = :P_STATUS and crh.prv_stat_cash_receipt_hist_id IS NULL';
115 END IF;
116 END IF;
117
118 IF P_INV_DATE_LOW IS NOT NULL THEN
119 LP_INV_DATE :=
120 ' and trx.trx_date >= :p_inv_date_low';
121 END IF;
122 IF P_INV_DATE_HIGH IS NOT NULL THEN
123 LP_INV_DATE := LP_INV_DATE ||
124 ' and trx.trx_date <= :p_inv_date_high';
125 END IF;
126
127 IF P_INV_NUM_LOW IS NOT NULL THEN
128 LP_INV_NUM :=
129 ' and trx.trx_number >= :p_inv_num_low';
130 END IF;
131 IF P_INV_NUM_HIGH IS NOT NULL THEN
132 LP_INV_NUM := LP_INV_NUM ||
133 ' and trx.trx_number <= :p_inv_num_high';
134 END IF;
135
136 IF P_DUE_DATE_LOW IS NOT NULL THEN
137 LP_DUE_DATE :=
138 ' and pays.due_date >= :p_due_date_low';
139 END IF;
140 IF P_DUE_DATE_HIGH IS NOT NULL THEN
141 LP_DUE_DATE := LP_DUE_DATE ||
142 ' and pays.due_date <= :p_due_date_high';
143 END IF;
144
145 IF P_PMT_MTD IS NOT NULL THEN
146 LP_PMT_METHOD :=
147 ' and pmt.name = :p_pmt_mtd';
148 END IF;
149
150 IF P_CUST_NAME IS NOT NULL THEN
151 LP_CUST_NAME :=
152 ' and PARTY.PARTY_NAME = :p_cust_name';
153 END IF;
154
155
156 IF P_CUST_NUMBER IS NOT NULL THEN
157 LP_CUST_NUM :=
158 ' and cust.ACCOUNT_NUMBER = :p_cust_number';
159 END IF;
160
161 IF P_INV_NUM_LOW IS NOT NULL THEN
162 LP_INV_NUM :=
163 ' and trx.trx_number >= :p_inv_num_low';
164 END IF;
165 IF P_INV_NUM_HIGH IS NOT NULL THEN
166 LP_INV_NUM := LP_INV_NUM ||
167 ' and trx.trx_number <= :p_inv_num_high';
168 END IF;
169
170
171 IF P_INV_TYPE IS NOT NULL THEN
172 LP_INV_TYPE :=
173 ' and trxtype.name = :p_inv_type';
174 END IF;
175
176 IF P_CURRENCY IS NOT NULL THEN
177 LP_CURRENCY :=
178 ' and trx.invoice_currency_code = :p_currency';
179 END IF; return (TRUE);
180 end;
181
182 function RP_DSP_SORT_BYFormula return VARCHAR2 is
183 begin
184
185 DECLARE
186 l_sort_by varchar(50);
187 BEGIN
188 SELECT meaning
189 INTO l_sort_by
190 FROM AR_LOOKUPS
191 WHERE lookup_code = P_SORT_BY
192 AND lookup_type = 'SORT_BY_ARXAPIPM';
193
194 RETURN(l_sort_by);
195
196 EXCEPTION
197 WHEN OTHERS THEN RETURN NULL;
198 END;
199 RETURN NULL; end;
200
201 function RP_DSP_SUMMARIZEFormula return VARCHAR2 is
202 begin
203
204 DECLARE
205 l_summarize varchar(50);
206 BEGIN
207 SELECT meaning
208 INTO l_summarize
209 FROM FND_LOOKUPS
210 WHERE lookup_code = P_SUMMARIZE
211 AND lookup_type = 'YES_NO';
212
213 RETURN(l_summarize);
214
215 EXCEPTION
216 WHEN OTHERS THEN RETURN NULL;
217 END;
218 RETURN NULL; end;
219
220 function RP_DSP_STATUSFormula return VARCHAR2 is
221 begin
222
223 DECLARE
224 l_status varchar(50);
225 BEGIN
226 SELECT meaning
227 INTO l_status
228 FROM AR_LOOKUPS
229 WHERE lookup_code = P_STATUS
230 AND lookup_type = 'ARXAPIPM_BATCH_APPLIED_STATUS';
231
232 RETURN(l_status);
233
234 EXCEPTION
235 WHEN OTHERS THEN RETURN NULL;
236 END;
237 RETURN NULL; end;
238
239 function CF_report_dateFormula return Char is
240 begin
241 return(fnd_date.date_to_chardt(SYSDATE));
242 end;
243
244 --Functions to refer Oracle report placeholders--
245
246 Function RP_COMPANY_NAME_p return varchar2 is
247 Begin
248 return RP_COMPANY_NAME;
249 END;
250 Function RP_REPORT_NAME_p return varchar2 is
251 Begin
252 return RP_REPORT_NAME;
253 END;
254 Function RP_SUB_TITLE1_p return varchar2 is
255 Begin
256 return RP_SUB_TITLE1;
257 END;
258 Function RP_SUB_TITLE2_p return varchar2 is
259 Begin
260 return RP_SUB_TITLE2;
261 END;
262 Function RP_DATA_FOUND_p return varchar2 is
263 Begin
264 return RP_DATA_FOUND;
265 END;
266 Function RP_FUNCTIONAL_CURRENCY_p return varchar2 is
267 Begin
268 return RP_FUNCTIONAL_CURRENCY;
269 END;
270 Function D_SUM_AMOUNT_DUE_CURRFormula return VARCHAR2 is
271 begin
272 RP_DATA_FOUND := '1';
273 return null;
274 end;
275 END AR_ARXAPIPM_XMLP_PKG ;
276
277
[Home] [Help]