1 PACKAGE BODY AR_ARXSOC_XMLP_PKG AS
2 /* $Header: ARXSOCB.pls 120.0 2007/12/27 14:09:11 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 END BEFOREREPORT;
7 FUNCTION AFTERREPORT RETURN BOOLEAN IS
8 BEGIN
9 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
10 RETURN (TRUE);
11 END AFTERREPORT;
12 FUNCTION REPORT_NAMEFORMULA(COMPANY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
13 BEGIN
14 DECLARE
15 L_REPORT_NAME VARCHAR2(80);
16 L_DATE_LOW VARCHAR2(11);
17 L_DATE_HIGH VARCHAR2(11);
18 BEGIN
19 IF P_DATE_LOW IS NULL THEN
20 L_DATE_LOW := ' ';
21 ELSE
22 L_DATE_LOW := TO_CHAR(P_DATE_LOW
23 ,'DD-MON-YYYY');
24 END IF;
25 IF P_DATE_HIGH IS NULL THEN
26 L_DATE_HIGH := ' ';
27 ELSE
28 L_DATE_HIGH := TO_CHAR(P_DATE_HIGH
29 ,'DD-MON-YYYY');
30 END IF;
31 RP_DATE_RANGE := ARP_STANDARD.FND_MESSAGE('ARXSOC_DEPOSIT_DATE_RANGE'
32 ,'FROM_DATE'
33 ,L_DATE_LOW
34 ,'TO_DATE'
35 ,L_DATE_HIGH);
36 RP_COMPANY_NAME := COMPANY_NAME;
37 SELECT
38 SUBSTR(CP.USER_CONCURRENT_PROGRAM_NAME
39 ,1
40 ,80)
41 INTO L_REPORT_NAME
42 FROM
43 FND_CONCURRENT_PROGRAMS_VL CP,
44 FND_CONCURRENT_REQUESTS CR
45 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
46 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
47 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
48 RP_REPORT_NAME := L_REPORT_NAME;
49 RETURN (L_REPORT_NAME);
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 RP_REPORT_NAME := NULL;
53 RETURN (NULL);
54 END;
55 RETURN NULL;
56 END REPORT_NAMEFORMULA;
57 FUNCTION C_DIFFERENCE_AMOUNTFORMULA(C_RCPT_CONTROL_AMOUNT IN NUMBER
58 ,C_ACTUAL_AMOUNT IN NUMBER) RETURN NUMBER IS
59 BEGIN
60 /*SRW.REFERENCE(C_RCPT_CONTROL_AMOUNT)*/NULL;
61 /*SRW.REFERENCE(C_ACTUAL_AMOUNT)*/NULL;
62 RETURN (NVL(C_RCPT_CONTROL_AMOUNT
63 ,0) - NVL(C_ACTUAL_AMOUNT
64 ,0));
65 END C_DIFFERENCE_AMOUNTFORMULA;
66 FUNCTION C_SUMMARY_LABELFORMULA(CURRENCY_A IN VARCHAR2) RETURN VARCHAR2 IS
67 BEGIN
68 RETURN (RTRIM(RPAD(CURRENCY_A
69 ,3)));
70 END C_SUMMARY_LABELFORMULA;
71 FUNCTION CA_DIFFERENCE_AMOUNTFORMULA(C_RCPT_CONTROL_AMOUNT_B IN NUMBER
72 ,CA_ACTUAL_AMOUNT IN NUMBER) RETURN NUMBER IS
73 BEGIN
74 /*SRW.REFERENCE(C_RCPT_CONTROL_AMOUNT_B)*/NULL;
75 /*SRW.REFERENCE(CA_ACTUAL_AMOUNT)*/NULL;
76 RETURN (NVL(C_RCPT_CONTROL_AMOUNT_B
77 ,0) - NVL(CA_ACTUAL_AMOUNT
78 ,0));
79 END CA_DIFFERENCE_AMOUNTFORMULA;
80 FUNCTION CA_SUMMARY_LABELFORMULA(CURRENCY_B IN VARCHAR2) RETURN VARCHAR2 IS
81 BEGIN
82 RETURN (RTRIM(RPAD(CURRENCY_B
83 ,3)));
84 END CA_SUMMARY_LABELFORMULA;
85 FUNCTION CF_DATA_NOT_FOUNDFORMULA(BANK_ACCOUNT_NAME_C IN VARCHAR2) RETURN NUMBER IS
86 BEGIN
87 RP_DATA_FOUND3 := BANK_ACCOUNT_NAME_C;
88 RETURN (0);
89 END CF_DATA_NOT_FOUNDFORMULA;
90 FUNCTION CR_DATA_FOUNDFORMULA(CURRENCY_B IN VARCHAR2) RETURN NUMBER IS
91 BEGIN
92 RP_DATA_FOUND2 := CURRENCY_B;
93 RETURN (0);
94 END CR_DATA_FOUNDFORMULA;
95 FUNCTION CM_DATA_NOT_FOUNDFORMULA(CURRENCY_A IN VARCHAR2) RETURN NUMBER IS
96 BEGIN
97 RP_DATA_FOUND1 := CURRENCY_A;
98 RETURN (0);
99 END CM_DATA_NOT_FOUNDFORMULA;
100 FUNCTION AFTERPFORM RETURN BOOLEAN IS
101 BEGIN
102 DECLARE
103 L_BANK_COUNT NUMBER(10);
104 BEGIN
105 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
106 P_DATE_LOW1 := to_char(P_DATE_LOW,'dd-mon-yy');
107 P_DATE_HIGH1 := to_char(P_DATE_HIGH,'dd-mon-yy');
108 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
109 PH_ORDER_BY := P_ORDER_BY;
110 IF UPPER(SUBSTR(P_ORDER_BY
111 ,1
112 ,1)) = 'B' THEN
113 P_ORDER_BY_1 := 'Bank';
114 ELSE
115 P_ORDER_BY_1 := 'Currency';
116 END IF;
117 IF P_DATE_LOW IS NOT NULL THEN
118 LP_DATE_LOW := ' and deposit_date >= :p_date_low ';
119 END IF;
120 IF P_DATE_HIGH IS NOT NULL THEN
121 LP_DATE_HIGH := ' and deposit_date <= :p_date_high ';
122 END IF;
123 IF P_BANK_ACCOUNT_NAME_LOW IS NOT NULL THEN
124 LP_BANK_ACCOUNT_NAME_LOW := 'and cba.bank_account_name >= :p_bank_account_name_low';
125 END IF;
126 IF P_BANK_ACCOUNT_NAME_HIGH IS NOT NULL THEN
127 LP_BANK_ACCOUNT_NAME_HIGH := 'and cba.bank_account_name <= :p_bank_account_name_high';
128 END IF;
129 SELECT
130 count(*)
131 INTO L_BANK_COUNT
132 FROM
133 CE_BANK_ACCT_USES BA,
134 CE_BANK_ACCOUNTS CBA,
135 CE_BANK_BRANCHES_V BB
136 WHERE BA.BANK_ACCT_USE_ID in (
137 SELECT
138 DISTINCT
139 REMIT_BANK_ACCT_USE_ID
140 FROM
141 AR_CASH_RECEIPTS )
142 AND CBA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID
143 AND CBA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
144 AND CBA.BANK_ACCOUNT_NAME between DECODE(P_BANK_ACCOUNT_NAME_LOW
145 ,NULL
146 ,CBA.BANK_ACCOUNT_NAME
147 ,P_BANK_ACCOUNT_NAME_LOW)
148 AND DECODE(P_BANK_ACCOUNT_NAME_HIGH
149 ,NULL
150 ,CBA.BANK_ACCOUNT_NAME
151 ,P_BANK_ACCOUNT_NAME_HIGH);
152 P_BANK_COUNT := L_BANK_COUNT;
153 END;
154 RETURN (TRUE);
155 END AFTERPFORM;
156 FUNCTION F_AMOUNTSFORMULA(AMOUNT IN NUMBER
157 ,CR_STATUS IN VARCHAR2
158 ,CR_TYPE IN VARCHAR2
159 ,REVERSAL_CATEGORY IN VARCHAR2
160 ,CASH_RECEIPT_ID IN NUMBER) RETURN NUMBER IS
161 BEGIN
162 DECLARE
163 L_ACTUAL_AMOUNT NUMBER := 0;
164 L_UNIDENTIFIED_AMOUNT NUMBER := 0;
165 L_MISC_AMOUNT NUMBER := 0;
166 L_NSF_AMOUNT NUMBER := 0;
167 L_ON_ACCOUNT_AMOUNT NUMBER := 0;
168 L_APPLIED_COUNT NUMBER(10) := 1;
169 L_UNAPPLIED_COUNT NUMBER(10) := 0;
170 L_UNIDENTIFIED_COUNT NUMBER(10) := 0;
171 L_MISC_COUNT NUMBER(10) := 0;
172 BEGIN
173 /*SRW.REFERENCE(AMOUNT)*/NULL;
174 /*SRW.REFERENCE(CR_STATUS)*/NULL;
175 /*SRW.REFERENCE(CR_TYPE)*/NULL;
176 /*SRW.REFERENCE(REVERSAL_CATEGORY)*/NULL;
177 SELECT
178 DECODE(CR_STATUS
179 ,'REV'
180 ,0
181 ,AMOUNT),
182 DECODE(CR_STATUS
183 ,'UNID'
184 ,AMOUNT
185 ,0),
186 DECODE(CR_TYPE
187 ,'MISC'
188 ,DECODE(CR_STATUS
189 ,'APP'
190 ,AMOUNT
191 ,0)
192 ,0),
193 DECODE(CR_STATUS
194 ,REVERSAL_CATEGORY
195 ,DECODE(CR_STATUS
196 ,'NSF'
197 ,AMOUNT
198 ,'STOP'
199 ,AMOUNT
200 ,0)
201 ,0),
202 DECODE(CR_STATUS
203 ,'APP'
204 ,1
205 ,''),
206 DECODE(CR_STATUS
207 ,'UNAPP'
208 ,1
209 ,0),
210 DECODE(CR_STATUS
211 ,'UNID'
212 ,1
213 ,0),
214 DECODE(CR_TYPE
215 ,'MISC'
216 ,1
217 ,0)
218 INTO L_ACTUAL_AMOUNT,L_UNIDENTIFIED_AMOUNT,L_MISC_AMOUNT,L_NSF_AMOUNT,L_APPLIED_COUNT,L_UNAPPLIED_COUNT,L_UNIDENTIFIED_COUNT,L_MISC_COUNT
219 FROM
220 DUAL;
221 IF CR_STATUS = 'APP' THEN
222 SELECT
223 SUM(AMOUNT_APPLIED)
224 INTO L_ON_ACCOUNT_AMOUNT
225 FROM
226 AR_RECEIVABLE_APPLICATIONS
227 WHERE CASH_RECEIPT_ID = F_AMOUNTSFORMULA.CASH_RECEIPT_ID
228 AND STATUS = 'ACC';
229 IF NVL(L_ON_ACCOUNT_AMOUNT
230 ,0) <> 0 THEN
231 L_APPLIED_COUNT := L_APPLIED_COUNT - 1;
232 END IF;
233 END IF;
234 P_ACTUAL_AMOUNT := L_ACTUAL_AMOUNT;
235 P_UNIDENTIFIED_AMOUNT := L_UNIDENTIFIED_AMOUNT;
236 P_MISC_AMOUNT := L_MISC_AMOUNT;
237 P_NSF_AMOUNT := L_NSF_AMOUNT;
238 P_APPLIED_COUNT := L_APPLIED_COUNT;
239 P_UNAPPLIED_COUNT := L_UNAPPLIED_COUNT;
240 P_UNIDENTIFIED_COUNT := L_UNIDENTIFIED_COUNT;
241 P_MISC_COUNT := L_MISC_COUNT;
242 RETURN (1);
243 END;
244 RETURN NULL;
245 END F_AMOUNTSFORMULA;
246 FUNCTION F_ALL_AMOUNTSFORMULA(AMOUNT_B IN NUMBER
247 ,CR_STATUS_BB IN VARCHAR2
248 ,CR_TYPE_B IN VARCHAR2
249 ,REVERSAL_CATEGORY_B IN VARCHAR2
250 ,CASH_RECEIPT_ID_B IN NUMBER) RETURN NUMBER IS
251 BEGIN
252 DECLARE
253 L_ACTUAL_AMOUNT NUMBER := 0;
254 L_UNIDENTIFIED_AMOUNT NUMBER := 0;
255 L_MISC_AMOUNT NUMBER := 0;
256 L_NSF_AMOUNT NUMBER := 0;
257 L_ON_ACCOUNT_AMOUNT NUMBER := 0;
258 L_APPLIED_COUNT NUMBER(10) := 0;
259 L_UNAPPLIED_COUNT NUMBER(10) := 0;
260 L_UNIDENTIFIED_COUNT NUMBER(10) := 0;
261 L_MISC_COUNT NUMBER(10) := 0;
262 BEGIN
263 /*SRW.REFERENCE(AMOUNT_B)*/NULL;
264 /*SRW.REFERENCE(CR_STATUS_BB)*/NULL;
265 /*SRW.REFERENCE(CR_TYPE_B)*/NULL;
266 /*SRW.REFERENCE(REVERSAL_CATEGORY_B)*/NULL;
267 SELECT
268 DECODE(CR_STATUS_BB
269 ,'REV'
270 ,0
271 ,AMOUNT_B),
272 DECODE(CR_STATUS_BB
273 ,'UNID'
274 ,AMOUNT_B
275 ,0),
276 DECODE(CR_TYPE_B
277 ,'MISC'
278 ,DECODE(CR_STATUS_BB
279 ,'APP'
280 ,AMOUNT_B
281 ,0)
282 ,0),
283 DECODE(CR_STATUS_BB
284 ,REVERSAL_CATEGORY_B
285 ,DECODE(CR_STATUS_BB
286 ,'NSF'
287 ,AMOUNT_B
288 ,'STOP'
289 ,AMOUNT_B
290 ,0)
291 ,0),
292 DECODE(CR_STATUS_BB
293 ,'APP'
294 ,1
295 ,''),
296 DECODE(CR_STATUS_BB
297 ,'UNAPP'
298 ,1
299 ,0),
300 DECODE(CR_STATUS_BB
301 ,'UNID'
302 ,1
303 ,0),
304 DECODE(CR_TYPE_B
305 ,'MISC'
306 ,1
307 ,0)
308 INTO L_ACTUAL_AMOUNT,L_UNIDENTIFIED_AMOUNT,L_MISC_AMOUNT,L_NSF_AMOUNT,L_APPLIED_COUNT,L_UNAPPLIED_COUNT,L_UNIDENTIFIED_COUNT,L_MISC_COUNT
309 FROM
310 DUAL;
311 IF CR_STATUS_BB = 'APP' THEN
312 SELECT
313 SUM(AMOUNT_APPLIED)
314 INTO L_ON_ACCOUNT_AMOUNT
315 FROM
316 AR_RECEIVABLE_APPLICATIONS
317 WHERE CASH_RECEIPT_ID = CASH_RECEIPT_ID_B
318 AND STATUS = 'ACC';
319 IF NVL(L_ON_ACCOUNT_AMOUNT
320 ,0) <> 0 THEN
321 L_APPLIED_COUNT := L_APPLIED_COUNT - 1;
322 END IF;
323 END IF;
324 PA_ACTUAL_AMOUNT := L_ACTUAL_AMOUNT;
325 PA_UNIDENTIFIED_AMOUNT := L_UNIDENTIFIED_AMOUNT;
326 PA_MISC_AMOUNT := L_MISC_AMOUNT;
327 PA_NSF_AMOUNT := L_NSF_AMOUNT;
328 PA_APPLIED_COUNT := L_APPLIED_COUNT;
329 PA_UNAPPLIED_COUNT := L_UNAPPLIED_COUNT;
330 PA_UNIDENTIFIED_COUNT := L_UNIDENTIFIED_COUNT;
331 PA_MISC_COUNT := L_MISC_COUNT;
332 RETURN (1);
333 END;
334 RETURN NULL;
335 END F_ALL_AMOUNTSFORMULA;
336 FUNCTION C_APPLIED_AMOUNTFORMULA(C_APPLIED_AMOUNT_A IN NUMBER
337 ,C_MISC_AMOUNT IN NUMBER) RETURN NUMBER IS
338 BEGIN
339 /*SRW.REFERENCE(C_APPLIED_AMOUNT_A)*/NULL;
340 /*SRW.REFERENCE(C_MISC_AMOUNT)*/NULL;
341 RETURN (NVL(C_APPLIED_AMOUNT_A
342 ,0) + NVL(C_MISC_AMOUNT
343 ,0));
344 END C_APPLIED_AMOUNTFORMULA;
345 FUNCTION CA_APPLIED_AMOUNTFORMULA(CA_APPLIED_AMOUNT_B IN NUMBER
346 ,CA_MISC_AMOUNT IN NUMBER) RETURN NUMBER IS
347 BEGIN
348 /*SRW.REFERENCE(CA_APPLIED_AMOUNT_B)*/NULL;
349 /*SRW.REFERENCE(CA_MISC_AMOUNT)*/NULL;
350 RETURN (NVL(CA_APPLIED_AMOUNT_B
351 ,0) + NVL(CA_MISC_AMOUNT
352 ,0));
353 END CA_APPLIED_AMOUNTFORMULA;
354 FUNCTION ORDER_BY_MEANINGFORMULA RETURN VARCHAR2 IS
355 BEGIN
356 DECLARE
357 L_ORDER_BY VARCHAR2(80);
358 BEGIN
359 SELECT
360 MEANING
361 INTO L_ORDER_BY
362 FROM
363 AR_LOOKUPS
364 WHERE LOOKUP_TYPE = 'SORT_BY_ARXSOC'
365 AND LOOKUP_CODE = PH_ORDER_BY;
366 RP_ORDER_BY := L_ORDER_BY;
367 RETURN (L_ORDER_BY);
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 RETURN (' ');
371 END;
372 RETURN NULL;
373 END ORDER_BY_MEANINGFORMULA;
374 FUNCTION P_ACTUAL_AMOUNT_P RETURN NUMBER IS
375 BEGIN
376 RETURN P_ACTUAL_AMOUNT;
377 END P_ACTUAL_AMOUNT_P;
378 FUNCTION P_UNIDENTIFIED_AMOUNT_P RETURN NUMBER IS
379 BEGIN
380 RETURN P_UNIDENTIFIED_AMOUNT;
381 END P_UNIDENTIFIED_AMOUNT_P;
382 FUNCTION P_MISC_AMOUNT_P RETURN NUMBER IS
383 BEGIN
384 RETURN P_MISC_AMOUNT;
385 END P_MISC_AMOUNT_P;
386 FUNCTION P_NSF_AMOUNT_P RETURN NUMBER IS
387 BEGIN
388 RETURN P_NSF_AMOUNT;
389 END P_NSF_AMOUNT_P;
390 FUNCTION P_APPLIED_COUNT_P RETURN NUMBER IS
391 BEGIN
392 RETURN P_APPLIED_COUNT;
393 END P_APPLIED_COUNT_P;
394 FUNCTION P_UNAPPLIED_COUNT_P RETURN NUMBER IS
395 BEGIN
396 RETURN P_UNAPPLIED_COUNT;
397 END P_UNAPPLIED_COUNT_P;
398 FUNCTION P_UNIDENTIFIED_COUNT_P RETURN NUMBER IS
399 BEGIN
400 RETURN P_UNIDENTIFIED_COUNT;
401 END P_UNIDENTIFIED_COUNT_P;
402 FUNCTION P_MISC_COUNT_P RETURN NUMBER IS
403 BEGIN
404 RETURN P_MISC_COUNT;
405 END P_MISC_COUNT_P;
406 FUNCTION PA_ACTUAL_AMOUNT_P RETURN NUMBER IS
407 BEGIN
408 RETURN PA_ACTUAL_AMOUNT;
409 END PA_ACTUAL_AMOUNT_P;
410 FUNCTION PA_UNIDENTIFIED_AMOUNT_P RETURN NUMBER IS
411 BEGIN
412 RETURN PA_UNIDENTIFIED_AMOUNT;
413 END PA_UNIDENTIFIED_AMOUNT_P;
414 FUNCTION PA_MISC_AMOUNT_P RETURN NUMBER IS
415 BEGIN
416 RETURN PA_MISC_AMOUNT;
417 END PA_MISC_AMOUNT_P;
418 FUNCTION PA_NSF_AMOUNT_P RETURN NUMBER IS
419 BEGIN
420 RETURN PA_NSF_AMOUNT;
421 END PA_NSF_AMOUNT_P;
422 FUNCTION PA_APPLIED_COUNT_P RETURN NUMBER IS
423 BEGIN
424 RETURN PA_APPLIED_COUNT;
425 END PA_APPLIED_COUNT_P;
426 FUNCTION PA_UNAPPLIED_COUNT_P RETURN NUMBER IS
427 BEGIN
428 RETURN PA_UNAPPLIED_COUNT;
429 END PA_UNAPPLIED_COUNT_P;
430 FUNCTION PA_UNIDENTIFIED_COUNT_P RETURN NUMBER IS
431 BEGIN
432 RETURN PA_UNIDENTIFIED_COUNT;
433 END PA_UNIDENTIFIED_COUNT_P;
434 FUNCTION PA_MISC_COUNT_P RETURN NUMBER IS
435 BEGIN
436 RETURN PA_MISC_COUNT;
437 END PA_MISC_COUNT_P;
438 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
439 BEGIN
440 RETURN RP_COMPANY_NAME;
441 END RP_COMPANY_NAME_P;
442 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
443 BEGIN
444 RETURN RP_REPORT_NAME;
445 END RP_REPORT_NAME_P;
446 FUNCTION RP_DATA_FOUND3_P RETURN VARCHAR2 IS
447 BEGIN
448 RETURN RP_DATA_FOUND3;
449 END RP_DATA_FOUND3_P;
450 FUNCTION RP_DATE_RANGE_P RETURN VARCHAR2 IS
451 BEGIN
452 RETURN RP_DATE_RANGE;
453 END RP_DATE_RANGE_P;
454 FUNCTION RP_DATA_FOUND1_P RETURN VARCHAR2 IS
455 BEGIN
456 RETURN RP_DATA_FOUND1;
457 END RP_DATA_FOUND1_P;
458 FUNCTION RP_DATA_FOUND2_P RETURN VARCHAR2 IS
459 BEGIN
460 RETURN RP_DATA_FOUND2;
461 END RP_DATA_FOUND2_P;
462 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
463 BEGIN
464 RETURN RP_ORDER_BY;
465 END RP_ORDER_BY_P;
466 END AR_ARXSOC_XMLP_PKG;
467
[Home] [Help]