1 PACKAGE BODY AR_ARXAPRRM_XMLP_PKG AS
2 /* $Header: ARXAPRRMB.pls 120.0 2007/12/27 13:32:21 abraghun noship $ */
3 function report_nameformula(Company_Name in varchar2, functional_currency in varchar2) return varchar2 is
4 begin
5 DECLARE
6 l_report_name VARCHAR2(80);
7 BEGIN
8 RP_Company_Name := Company_Name;
9 RP_FUNCTIONAL_CURRENCY := functional_currency;
10 SELECT substr(cp.user_concurrent_program_name,1,80)
11 INTO l_report_name
12 FROM FND_CONCURRENT_PROGRAMS_VL cp,
13 FND_CONCURRENT_REQUESTS cr
14 WHERE cr.request_id = P_CONC_REQUEST_ID
15 AND cp.application_id = cr.program_application_id
16 AND cp.concurrent_program_id = cr.concurrent_program_id;
17 l_report_name:= substr(l_report_name,1,instr(l_report_name,' (XML)'));
18 RP_Report_Name := l_report_name;
19 RETURN(l_report_name);
20 EXCEPTION
21 WHEN NO_DATA_FOUND
22 THEN RP_REPORT_NAME := 'Receipts Awaiting Remittance Report';
23 RETURN('Receipts Awaiting Remittance Report');
24 END;
25 RETURN NULL; end;
26 function BeforeReport return boolean is
27 begin
28 begin
29 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
30 /*SRW.USER_EXIT('FND SRWINIT');*/null;
31 end;
32 return (TRUE);
33 end;
34 function Sub_TitleFormula return VARCHAR2 is
35 begin
36 begin
37 RP_SUB_TITLE := ' ';
38 return(' ');
39 end;
40 RETURN NULL; end;
41 function AfterReport return boolean is
42 begin
43 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
44 return (TRUE);
45 end;
46 function COUNTERFormula return Number is
47 begin
48 RETURN(1);
49 end;
50 function AfterPForm return boolean is
51 l_remit_amount_low NUMBER;
52 l_remit_amount_high NUMBER;
53 begin
54 P_MATURITY_DATE_LOW1 := to_char(P_MATURITY_DATE_LOW,'DD-MON-YY');
55 P_MATURITY_DATE_HIGH1 := to_char(P_MATURITY_DATE_HIGH,'DD-MON-YY');
56 IF P_SUMMARIZE = 'Y' THEN
57 RP_SUMMARIZE := 'YES';
58 ELSE
59 RP_SUMMARIZE := 'NO';
60 END IF;
61 IF RP_SUMMARIZE = 'YES' THEN
62 RP_SUM_COL_AMOUNT := ' sum(cr.amount) ';
63 RP_SUM_COL_CHARGES := ' sum(cr.factor_discount_amount) ';
64 RP_GROUP_BY :=
65 ' group by cr.currency_code, status_lc.meaning, decode(:P_SORT_BY, ''MATURITY DATE'', ps.due_date, null),
66 decode(:P_SORT_BY, ''RECEIPT NUMBER'', cr.receipt_number, null),
67 decode(:P_SORT_BY, ''REMITTANCE BANK'', cbranch.bank_name, null),
68 cba.bank_account_name,
69 decode(:RP_SUMMARIZE, ''YES'', null, cbranch.bank_name),
70 decode(:RP_SUMMARIZE, ''YES'', null, cbranch.bank_branch_name),
71 ps.due_date, decode(:RP_SUMMARIZE, ''YES'', null, rmethod_lc.meaning),
72 rmethod.name, decode(:RP_SUMMARIZE, ''YES'', null, cr.receipt_number) ';
73 END IF;
74 IF P_STATUS IS NOT NULL THEN
75 LP_STATUS:=' and nvl(batch.batch_applied_status, ''AVAILABLE_FOR_REMITT'') = :P_STATUS';
76 END IF;
77 IF P_REMIT_ACCOUNT IS NOT NULL THEN
78 LP_REMIT_ACCOUNT := ' and racct.bank_account_name = :P_REMIT_ACCOUNT';
79 END IF;
80 IF P_REMIT_METHOD IS NOT NULL THEN
81 LP_REMIT_METHOD := ' and rclass.remit_method_code = :P_REMIT_METHOD ';
82 END IF;
83 IF P_PMT_METHOD IS NOT NULL THEN
84 LP_PMT_METHOD := ' and rmethod.name = :P_PMT_METHOD ';
85 END IF;
86 IF P_MATURITY_DATE_LOW IS NOT NULL THEN
87 LP_MATURITY_DATE := ' and ps.due_date >= :P_MATURITY_DATE_LOW';
88 END IF;
89 IF P_MATURITY_DATE_HIGH IS NOT NULL THEN
90 LP_MATURITY_DATE := LP_MATURITY_DATE ||
91 ' and ps.due_date <= :P_MATURITY_DATE_HIGH';
92 END IF;
93 IF P_REMIT_AMOUNT_LOW IS NOT NULL THEN
94 l_remit_amount_low := FND_NUMBER.CANONICAL_TO_NUMBER(P_REMIT_AMOUNT_LOW);
95 LP_REMIT_AMOUNT := ' and cr.amount >= '|| l_remit_amount_low;
96 END IF;
97 IF P_REMIT_AMOUNT_HIGH IS NOT NULL THEN
98 l_remit_amount_high := FND_NUMBER.CANONICAL_TO_NUMBER(P_REMIT_AMOUNT_HIGH);
99 LP_REMIT_AMOUNT := LP_REMIT_AMOUNT ||
100 ' and cr.amount <= ' || l_remit_amount_high;
101 END IF;
102 IF P_CURRENCY IS NOT NULL THEN
103 LP_CURRENCY := ' and cr.currency_code = :P_CURRENCY';
104 END IF;
105 return (TRUE);
106 end;
107 function RP_DISP_SUMMARIZEFormula return VARCHAR2 is
108 begin
109 DECLARE
110 l_meaning varchar(30);
111 BEGIN
112 SELECT meaning
113 INTO l_meaning
114 FROM FND_LOOKUPS
115 WHERE LOOKUP_TYPE = 'YES_NO'
116 AND LOOKUP_CODE = P_SUMMARIZE;
117 RETURN(l_meaning);
118 EXCEPTION
119 WHEN OTHERS THEN RETURN NULL;
120 END;
121 RETURN NULL; end;
122 function RP_DISP_SORT_BYFormula return VARCHAR2 is
123 begin
124 DECLARE
125 l_sort_by varchar(30);
126 BEGIN
127 SELECT meaning
128 INTO l_sort_by
129 FROM AR_LOOKUPS
130 WHERE lookup_type = 'SORT_BY_ARXAPRRM'
131 AND lookup_code = P_SORT_BY;
132 RETURN(l_sort_by);
133 EXCEPTION
134 WHEN OTHERS THEN RETURN NULL;
135 END;
136 RETURN NULL; end;
137 function RP_DISP_STATUSFormula return VARCHAR2 is
138 begin
139 DECLARE
140 l_status varchar(30);
141 BEGIN
142 SELECT meaning
143 INTO l_status
144 FROM AR_LOOKUPS
145 WHERE lookup_type = 'ARXAPRRM_BATCH_APPLIED_STATUS'
146 AND lookup_code = P_STATUS;
147 RETURN(l_status);
148 EXCEPTION
149 WHEN OTHERS THEN RETURN NULL;
150 END;
151 RETURN NULL; end;
152 function RP_DISP_REMIT_METHODFormula return VARCHAR2 is
153 begin
154 DECLARE
155 l_rmethod varchar(30);
156 BEGIN
157 SELECT meaning
158 INTO l_rmethod
159 FROM AR_LOOKUPS
160 WHERE lookup_type = 'REMITTANCE_METHOD'
161 AND lookup_code = P_REMIT_METHOD;
162 RETURN(l_rmethod);
163 EXCEPTION
164 WHEN OTHERS THEN RETURN NULL;
165 END;
166 RETURN NULL; end;
167 --Functions to refer Oracle report placeholders--
168 Function COUNTER_p return number is
169 Begin
170 return COUNTER;
171 END;
172 Function RP_COMPANY_NAME_p return varchar2 is
173 Begin
174 return RP_COMPANY_NAME;
175 END;
176 Function RP_REPORT_NAME_p return varchar2 is
177 Begin
178 return RP_REPORT_NAME;
179 END;
180 Function RP_SUB_TITLE_p return varchar2 is
181 Begin
182 return RP_SUB_TITLE;
183 END;
184 Function RP_DATA_FOUND_p return varchar2 is
185 Begin
186 return RP_DATA_FOUND;
187 END;
188 Function RP_FUNCTIONAL_CURRENCY_p return varchar2 is
189 Begin
190 return RP_FUNCTIONAL_CURRENCY;
191 END;
192 function D_SUM_AMOUNT_CURRFormula return VARCHAR2 is
193 begin
194 RP_DATA_FOUND := 1;
195 return NULL;
196 end;
197 END AR_ARXAPRRM_XMLP_PKG ;
198
[Home] [Help]