[Home] [Help]
PACKAGE BODY: APPS.AR_ARBRRMLT_XMLP_PKG
Source
1 PACKAGE BODY AR_ARBRRMLT_XMLP_PKG AS
2 /* $Header: ARBRRMLTB.pls 120.1 2011/03/08 13:21:38 dgaurab ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 LP_CREATION_DATE_FROM := P_CREATION_DATE_FROM;
7 LP_CREATION_DATE_TO := P_CREATION_DATE_TO;
8 LP_DRAWEE_NAME_FROM := P_DRAWEE_NAME_FROM;
9 LP_DRAWEE_NAME_TO := P_DRAWEE_NAME_TO;
10 LP_DRAWEE_NUMBER_FROM := P_DRAWEE_NUMBER_FROM;
11 LP_DRAWEE_NUMBER_TO := P_DRAWEE_NUMBER_TO;
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 SELECT
14 SET_OF_BOOKS_ID
15 INTO P_SET_OF_BOOKS_ID
16 FROM
17 AR_SYSTEM_PARAMETERS;
18 IF LP_DRAWEE_NAME_FROM IS NULL THEN
19 LP_DRAWEE_NAME_FROM := LP_DRAWEE_NAME_TO;
20 END IF;
21 IF LP_DRAWEE_NAME_TO IS NULL THEN
22 LP_DRAWEE_NAME_TO := LP_DRAWEE_NAME_FROM;
23 END IF;
24 IF LP_DRAWEE_NAME_FROM IS NULL THEN
25 LP_CUSTOMER_RANGE := ' AND 1 = 1';
26 ELSE
27 LP_CUSTOMER_RANGE := ' and party.party_name between ' || '''' || LP_DRAWEE_NAME_FROM || '''' || ' and ' || '''' || LP_DRAWEE_NAME_TO || '''';
28 END IF;
29 IF LP_CREATION_DATE_FROM IS NULL THEN
30 LP_CREATION_DATE_FROM := LP_CREATION_DATE_TO;
31 END IF;
32 IF LP_CREATION_DATE_TO IS NULL THEN
33 LP_CREATION_DATE_TO := LP_CREATION_DATE_FROM;
34 END IF;
35 IF LP_CREATION_DATE_FROM IS NULL THEN
36 LP_CREATION_DATE_RANGE := NULL;
37
38 ELSE
39 LP_CREATION_DATE_RANGE := ' and trx_date between ' || '''' || LP_CREATION_DATE_FROM || '''' || ' and ' || '''' || LP_CREATION_DATE_TO || '''';
40 END IF;
41 IF LP_DRAWEE_NUMBER_FROM IS NULL THEN
42 LP_DRAWEE_NUMBER_FROM := LP_DRAWEE_NUMBER_TO;
43 END IF;
44 IF LP_DRAWEE_NUMBER_TO IS NULL THEN
45 LP_DRAWEE_NUMBER_TO := LP_DRAWEE_NUMBER_FROM;
46 END IF;
47 IF LP_DRAWEE_NUMBER_FROM IS NULL THEN
48 LP_CUSTOMER_NUMBER_RANGE := ' AND 1 = 1';
49 ELSE
50 LP_CUSTOMER_NUMBER_RANGE := ' and cust.cust_account_id between ' || LP_DRAWEE_NUMBER_FROM || ' and ' || LP_DRAWEE_NUMBER_TO;
51 END IF;
52 IF P_DAYS_LATE_SINCE_CREATION IS NULL THEN
53 LP_DAYS_LATE_SINCE_CREATION := NULL;
54 ELSE
55 LP_DAYS_LATE_SINCE_CREATION := ' and trunc(sysdate - term_due_date) = ' || P_DAYS_LATE_SINCE_CREATION;
56 END IF;
57 RETURN (TRUE);
58 EXCEPTION
59 WHEN OTHERS THEN
60 /*SRW.MESSAGE(9999
61 ,'Exception Others in Before Report Trigger')*/NULL;
62 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
63 RETURN (TRUE);
64 END BEFOREREPORT;
65
66 FUNCTION AFTERREPORT RETURN BOOLEAN IS
67 BEGIN
68 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
69 RETURN (TRUE);
70 END AFTERREPORT;
71
72 FUNCTION CF_CURRENT_DATEFORMULA RETURN CHAR IS
73 BEGIN
74 --Bug11799279
75 RETURN FND_DATE.DATE_TO_CHARDATE(SYSDATE, calendar_aware=> FND_DATE.calendar_aware_alt);
76 END CF_CURRENT_DATEFORMULA;
77
78 FUNCTION CF_BR_DATEFORMULA(BR_DATE IN DATE) RETURN CHAR IS
79 BEGIN
80 --Bug11799279
81 RETURN (FND_DATE.DATE_TO_CHARDATE(BR_DATE, calendar_aware=> FND_DATE.calendar_aware_alt));
82 END CF_BR_DATEFORMULA;
83
84 FUNCTION AFTERPFORM RETURN BOOLEAN IS
85 L_PRINT_COUNTRY_FLAG VARCHAR2(1);
86 L_DEFAULT_COUNTRY VARCHAR2(60);
87 BEGIN
88 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
89 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
90 /*SRW.MESSAGE(100
91 ,'DEBUG: AfterpForm')*/NULL;
92 SELECT
93 PRINT_HOME_COUNTRY_FLAG,
94 NVL(DEFAULT_COUNTRY
95 ,'US')
96 INTO L_PRINT_COUNTRY_FLAG,L_DEFAULT_COUNTRY
97 FROM
98 AR_SYSTEM_PARAMETERS;
99 P_PRINT_HOME_COUNTRY_FLAG := L_PRINT_COUNTRY_FLAG;
100 P_DEFAULT_COUNTRY := L_DEFAULT_COUNTRY;
101 P_DEFAULT_COUNTRY_DESCRIPTION := GET_COUNTRY_DESCRIPTION(P_DEFAULT_COUNTRY);
102 RETURN (TRUE);
103 END AFTERPFORM;
104
105 FUNCTION GET_COUNTRY_DESCRIPTION(COUNTRY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
106 COUNTRY_DESCRIPTION VARCHAR2(80) := NULL;
107 BEGIN
108 /*SRW.MESSAGE(100
109 ,'DEBUG: Get_Country_Description.')*/NULL;
110 IF COUNTRY_CODE IS NOT NULL THEN
111 IF COUNTRY_CODE <> NVL(P_DEFAULT_COUNTRY
112 ,'XXXXXX') THEN
113 SELECT
114 TERRITORY_SHORT_NAME
115 INTO COUNTRY_DESCRIPTION
116 FROM
117 FND_TERRITORIES_VL
118 WHERE TERRITORY_CODE = COUNTRY_CODE;
119 IF COUNTRY_DESCRIPTION IS NULL THEN
120 COUNTRY_DESCRIPTION := COUNTRY_CODE;
121 END IF;
122 ELSE
123 IF P_PRINT_HOME_COUNTRY_FLAG = 'Y' THEN
124 SELECT
125 TERRITORY_SHORT_NAME
126 INTO COUNTRY_DESCRIPTION
127 FROM
128 FND_TERRITORIES_VL
129 WHERE TERRITORY_CODE = COUNTRY_CODE;
130 ELSE
131 COUNTRY_DESCRIPTION := NULL;
132 END IF;
133 END IF;
134 ELSE
135 COUNTRY_DESCRIPTION := NULL;
136 END IF;
137 RETURN (COUNTRY_DESCRIPTION);
138 EXCEPTION
142 IF SQLCODE = -20000 THEN
139 WHEN NO_DATA_FOUND THEN
140 RETURN (COUNTRY_CODE);
141 WHEN OTHERS THEN
143 /*SRW.MESSAGE('100'
144 ,ARP_STANDARD.FND_MESSAGE(ARP_STANDARD.MD_MSG_TEXT + ARP_STANDARD.MD_MSG_NUMBER))*/NULL;
145 RETURN (COUNTRY_CODE);
146 ELSE
147 /*SRW.MESSAGE('100'
148 ,'Oracle Error in call to' || ' arp_adds.location_description ' || TO_CHAR(SQLCODE
149 ,'999999'))*/NULL;
150 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
151 END IF;
152 RETURN NULL;
153 END GET_COUNTRY_DESCRIPTION;
154
155 FUNCTION CUSTOMER_ADDRESS_STRINGFORMULA(CUST_NAME IN VARCHAR2
156 ,CUST_ADDRESS1 IN VARCHAR2
157 ,CUST_ADDRESS2 IN VARCHAR2
158 ,CUST_ADDRESS3 IN VARCHAR2
159 ,CUST_ADDRESS4 IN VARCHAR2
160 ,CUST_CITY IN VARCHAR2
161 ,CUST_STATE IN VARCHAR2
162 ,CUST_POSTAL_CODE IN VARCHAR2
163 ,CUST_COUNTRY IN VARCHAR2) RETURN VARCHAR2 IS
164 CUST_ADDRESS_STRING VARCHAR2(5000);
165 ADDRESS_STYLE VARCHAR2(30);
166 ADDRESS1 VARCHAR2(240);
167 ADDRESS2 VARCHAR2(240);
168 ADDRESS3 VARCHAR2(240);
169 ADDRESS4 VARCHAR2(240);
170 CITY VARCHAR2(60);
171 COUNTY VARCHAR2(60);
172 STATE VARCHAR2(60);
173 PROVINCE VARCHAR2(60);
174 POSTAL_CODE VARCHAR2(60);
175 TERRITORY_SHORT_NAME VARCHAR2(80);
176 COUNTRY_CODE VARCHAR2(60);
177 CUSTOMER_NAME VARCHAR2(360);
178 BILL_TO_LOCATION VARCHAR2(40);
179 FIRST_NAME VARCHAR2(40);
180 LAST_NAME VARCHAR2(50);
181 MAIL_STOP VARCHAR2(60);
182 DEFAULT_COUNTRY_CODE VARCHAR2(60);
183 DEFAULT_COUNTRY_DESC VARCHAR2(80);
184 PRINT_HOME_COUNTRY_FLAG VARCHAR2(5);
185 PRINT_DEFAULT_ATTN_FLAG VARCHAR2(5);
186 WIDTH NUMBER(3);
187 HEIGHT_MIN NUMBER(3);
188 HEIGHT_MAX NUMBER(3);
189 BEGIN
190 /*SRW.REFERENCE(CUST_NAME)*/NULL;
191 /*SRW.REFERENCE(CUST_ADDRESS1)*/NULL;
192 /*SRW.REFERENCE(CUST_ADDRESS2)*/NULL;
193 /*SRW.REFERENCE(CUST_ADDRESS3)*/NULL;
194 /*SRW.REFERENCE(CUST_ADDRESS4)*/NULL;
195 /*SRW.REFERENCE(CUST_CITY)*/NULL;
196 /*SRW.REFERENCE(CUST_STATE)*/NULL;
197 /*SRW.REFERENCE(CUST_POSTAL_CODE)*/NULL;
198 /*SRW.REFERENCE(CUST_COUNTRY)*/NULL;
199 /*SRW.REFERENCE(P_DEFAULT_COUNTRY)*/NULL;
200 /*SRW.REFERENCE(P_DEFAULT_COUNTRY_DESCRIPTION)*/NULL;
201 /*SRW.REFERENCE(P_PRINT_HOME_COUNTRY_FLAG)*/NULL;
202 ADDRESS1 := CUST_ADDRESS1;
203 ADDRESS2 := CUST_ADDRESS2;
204 ADDRESS3 := CUST_ADDRESS3;
205 ADDRESS4 := CUST_ADDRESS4;
206 CITY := CUST_CITY;
207 STATE := CUST_STATE;
208 COUNTY := NULL;
209 PROVINCE := NULL;
210 POSTAL_CODE := CUST_POSTAL_CODE;
211 TERRITORY_SHORT_NAME := NULL;
212 COUNTRY_CODE := CUST_COUNTRY;
213 CUSTOMER_NAME := CUST_NAME;
214 BILL_TO_LOCATION := NULL;
215 FIRST_NAME := NULL;
216 LAST_NAME := NULL;
217 MAIL_STOP := NULL;
218 DEFAULT_COUNTRY_CODE := P_DEFAULT_COUNTRY;
219 DEFAULT_COUNTRY_DESC := P_DEFAULT_COUNTRY_DESCRIPTION;
220 PRINT_HOME_COUNTRY_FLAG := P_PRINT_HOME_COUNTRY_FLAG;
221 PRINT_DEFAULT_ATTN_FLAG := 'N';
222 WIDTH := 40;
223 HEIGHT_MIN := 8;
224 HEIGHT_MAX := 8;
225 CUST_ADDRESS_STRING := ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(ADDRESS_STYLE
226 ,ADDRESS1
227 ,ADDRESS2
228 ,ADDRESS3
229 ,ADDRESS4
230 ,CITY
231 ,COUNTY
232 ,STATE
233 ,PROVINCE
234 ,POSTAL_CODE
235 ,TERRITORY_SHORT_NAME
236 ,COUNTRY_CODE
237 ,CUSTOMER_NAME
238 ,BILL_TO_LOCATION
239 ,FIRST_NAME
240 ,LAST_NAME
241 ,MAIL_STOP
242 ,DEFAULT_COUNTRY_CODE
243 ,DEFAULT_COUNTRY_DESC
244 ,PRINT_HOME_COUNTRY_FLAG
245 ,PRINT_DEFAULT_ATTN_FLAG
246 ,WIDTH
247 ,HEIGHT_MIN
248 ,HEIGHT_MAX);
249 RETURN (CUST_ADDRESS_STRING);
250 RETURN NULL;
251 EXCEPTION
252 WHEN OTHERS THEN
253 /*SRW.MESSAGE(5000
254 ,'DEBUG: Cannot format Cust_address_String.')*/NULL;
255 CUST_ADDRESS_STRING := '';
256 RETURN (CUST_ADDRESS_STRING);
257 END CUSTOMER_ADDRESS_STRINGFORMULA;
258
259 FUNCTION C_OUR_STREET_P RETURN VARCHAR2 IS
260 BEGIN
261 RETURN C_OUR_STREET;
262 END C_OUR_STREET_P;
263
264 FUNCTION C_OUR_ZIPCITY_P RETURN VARCHAR2 IS
265 BEGIN
266 RETURN C_OUR_ZIPCITY;
267 END C_OUR_ZIPCITY_P;
268
269 FUNCTION C_LISTNUM_P RETURN NUMBER IS
270 BEGIN
271 RETURN C_LISTNUM;
272 END C_LISTNUM_P;
273
274 FUNCTION C_OUR_SOB_NAME_P RETURN VARCHAR2 IS
275 BEGIN
276 RETURN C_OUR_SOB_NAME;
277 END C_OUR_SOB_NAME_P;
278
279 FUNCTION C_LOCATION_P RETURN VARCHAR2 IS
280 BEGIN
281 RETURN C_LOCATION;
282 END C_LOCATION_P;
283
284 END AR_ARBRRMLT_XMLP_PKG;
285