[Home] [Help]
PACKAGE BODY: APPS.AP_APXVDTIN_XMLP_PKG
Source
1 PACKAGE BODY AP_APXVDTIN_XMLP_PKG AS
2 /* $Header: APXVDTINB.pls 120.0 2007/12/27 08:49:12 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 FROM_ADDRESS_LINE1 HR_LOCATIONS.ADDRESS_LINE_1%TYPE;
7 FROM_ADDRESS_LINE2 HR_LOCATIONS.ADDRESS_LINE_2%TYPE;
8 FROM_ADDRESS_LINE3 HR_LOCATIONS.ADDRESS_LINE_3%TYPE;
9 FROM_ADDRESS_LINE4 HR_LOCATIONS.ADDRESS_LINE_3%TYPE;
10 FROM_CITY HR_LOCATIONS.TOWN_OR_CITY%TYPE;
11 FROM_STATE HR_LOCATIONS.REGION_3%TYPE;
12 FROM_ZIP HR_LOCATIONS.POSTAL_CODE%TYPE;
13 LOCATION_ID HR_LOCATIONS.LOCATION_ID%TYPE;
14 DISP_RETURN_DATE VARCHAR2(30);
15 FROM_ATTENTION_LINE AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
16 INIT_FAILURE EXCEPTION;
17 BEGIN
18 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
19 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
20 SELECT
21 DECODE(P_ADDR_CHOICE
22 ,'ADDRESS1'
23 ,SUBSTR(L.ADDRESS_LINE_1
24 ,1
25 ,35)
26 ,'LOCATION'
27 ,LOCATION_CODE
28 ,'REP_ENTITY'
29 ,R.ENTITY_NAME
30 ,SUBSTR(L.ADDRESS_LINE_1
31 ,1
32 ,35)),
33 DECODE(P_ADDR_CHOICE
34 ,'ADDRESS1'
35 ,SUBSTR(L.ADDRESS_LINE_2
36 ,1
37 ,35)
38 ,'LOCATION'
39 ,SUBSTR(L.ADDRESS_LINE_1
40 ,1
41 ,35)
42 ,'REP_ENTITY'
43 ,SUBSTR(L.ADDRESS_LINE_1
44 ,1
45 ,35)
46 ,SUBSTR(L.ADDRESS_LINE_2
47 ,1
48 ,35)),
49 DECODE(P_ADDR_CHOICE
50 ,'ADDRESS1'
51 ,SUBSTR(L.ADDRESS_LINE_3
52 ,1
53 ,35)
54 ,'LOCATION'
55 ,SUBSTR(L.ADDRESS_LINE_2
56 ,1
57 ,35)
58 ,'REP_ENTITY'
59 ,SUBSTR(L.ADDRESS_LINE_2
60 ,1
61 ,35)
62 ,SUBSTR(L.ADDRESS_LINE_3
63 ,1
64 ,35)),
65 DECODE(P_ADDR_CHOICE
66 ,'ADDRESS1'
67 ,NULL
68 ,'LOCATION'
69 ,SUBSTR(L.ADDRESS_LINE_3
70 ,1
71 ,35)
72 ,'REP_ENTITY'
73 ,SUBSTR(L.ADDRESS_LINE_3
74 ,1
75 ,35)
76 ,SUBSTR(L.ADDRESS_LINE_3
77 ,1
78 ,35)),
79 SUBSTR(L.TOWN_OR_CITY
80 ,1
81 ,30),
82 SUBSTR(NVL(L.REGION_3
83 ,L.REGION_2)
84 ,1
85 ,30),
86 SUBSTR(L.POSTAL_CODE
87 ,1
88 ,20),
89 L.LOCATION_ID,
90 TO_CHAR(P_RETURN_BY_DATE
91 ,'fmMonth DD, YYYY'),
92 APL.DISPLAYED_FIELD
93 INTO FROM_ADDRESS_LINE1,FROM_ADDRESS_LINE2,FROM_ADDRESS_LINE3,FROM_ADDRESS_LINE4,FROM_CITY,FROM_STATE,FROM_ZIP,LOCATION_ID,DISP_RETURN_DATE,FROM_ATTENTION_LINE
94 FROM
95 HR_LOCATIONS L,
96 AP_REPORTING_ENTITIES R,
97 AP_LOOKUP_CODES APL
98 WHERE L.LOCATION_ID = R.LOCATION_ID
99 AND R.TAX_ENTITY_ID = P_TAX_ENTITY_ID
100 AND APL.LOOKUP_CODE = P_ATTENTION_LINE
101 AND APL.LOOKUP_TYPE = 'ATTENTION';
102 C_FROM_ADD_LINE_1 := FROM_ADDRESS_LINE1;
103 C_FROM_ADD_LINE_2 := FROM_ADDRESS_LINE2;
104 C_FROM_ADD_LINE_3 := FROM_ADDRESS_LINE3;
105 C_FROM_ADD_LINE_4 := FROM_ADDRESS_LINE4;
106 C_FROM_CITY := FROM_CITY;
107 C_FROM_STATE := FROM_STATE;
108 C_FROM_ZIP := FROM_ZIP;
109 C_LOCATION_ID := LOCATION_ID;
110 C_DISP_RETURN_BY_DATE := DISP_RETURN_DATE;
111 C_ATTENTION_LINE := FROM_ATTENTION_LINE;
112 IF (GET_APP_COLUMN_NAME <> TRUE) THEN
113 RAISE INIT_FAILURE;
114 END IF;
115 /*SRW.BREAK*/NULL;
116 RETURN (TRUE);
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 /*SRW.MESSAGE('999'
120 ,'Cancelled -- Location/address not set up for company')*/NULL;
121 RETURN (FALSE);
122 WHEN OTHERS THEN
123 /*SRW.MESSAGE('992'
124 ,'Cancelled -- Error occurred in Before Report Trigger.')*/NULL;
125 RETURN (FALSE);
126 END;
127 RETURN (TRUE);
128 END BEFOREREPORT;
129
130 FUNCTION C_RETURN_BY_DATEFORMULA RETURN VARCHAR2 IS
131 BEGIN
132 RETURN (TO_CHAR(P_RETURN_BY_DATE
133 ,'fmMonth DD,YYYY'));
134 END C_RETURN_BY_DATEFORMULA;
135
136 FUNCTION AFTERREPORT RETURN BOOLEAN IS
137 BEGIN
138 BEGIN
139 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
140 EXCEPTION
141 WHEN OTHERS THEN
142 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
143 END;
144 RETURN (TRUE);
145 END AFTERREPORT;
146
147 FUNCTION GET_APP_COLUMN_NAME RETURN BOOLEAN IS
148 CHART_OF_ACCOUNTS_ID NUMBER(15);
149 APPLICATION_COLUMN_NAME VARCHAR2(30);
150 BEGIN
151 SELECT
152 FND.APPLICATION_COLUMN_NAME,
153 GL.CHART_OF_ACCOUNTS_ID
154 INTO APPLICATION_COLUMN_NAME,CHART_OF_ACCOUNTS_ID
155 FROM
156 FND_SEGMENT_ATTRIBUTE_VALUES FND,
157 GL_SETS_OF_BOOKS GL
158 WHERE SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
159 AND FND.ATTRIBUTE_VALUE = 'Y'
160 AND FND.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID
161 AND GL.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
162 AND ID_FLEX_CODE = 'GL#';
163 C_APP_COLUMN_NAME := APPLICATION_COLUMN_NAME;
164 C_CHART_ACCTS_ID := CHART_OF_ACCOUNTS_ID;
165 C_DYNAMIC_SQL := '';
166 IF (C_APP_COLUMN_NAME like 'SEGMENT1') THEN
167 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment1 = REL.balancing_segment_value';
168 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT2') THEN
169 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment2 = REL.balancing_segment_value';
170 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT3') THEN
171 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment3 = REL.balancing_segment_value';
172 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT4') THEN
173 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment4 = REL.balancing_segment_value';
174 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT5') THEN
175 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment5 = REL.balancing_segment_value';
176 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT6') THEN
177 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment6 = REL.balancing_segment_value';
178 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT7') THEN
179 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment7 = REL.balancing_segment_value';
180 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT8') THEN
181 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment8 = REL.balancing_segment_value';
182 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT9') THEN
183 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment9 = REL.balancing_segment_value';
184 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT10') THEN
185 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment10 = REL.balancing_segment_value';
186 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT11') THEN
187 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment11 = REL.balancing_segment_value';
188 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT12') THEN
189 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment12 = REL.balancing_segment_value';
190 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT13') THEN
191 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment13 = REL.balancing_segment_value';
192 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT14') THEN
193 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment14 = REL.balancing_segment_value';
194 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT15') THEN
195 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment15 = REL.balancing_segment_value';
196 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT16') THEN
197 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment16 = REL.balancing_segment_value';
198 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT17') THEN
199 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment17 = REL.balancing_segment_value';
200 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT18') THEN
201 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment18 = REL.balancing_segment_value';
202 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT19') THEN
203 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment19 = REL.balancing_segment_value';
204 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT20') THEN
205 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment20 = REL.balancing_segment_value';
206 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT21') THEN
207 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment21 = REL.balancing_segment_value';
208 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT22') THEN
209 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment22 = REL.balancing_segment_value';
210 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT23') THEN
211 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment23 = REL.balancing_segment_value';
212 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT24') THEN
213 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment24 = REL.balancing_segment_value';
214 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT25') THEN
215 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment25 = REL.balancing_segment_value';
216 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT26') THEN
217 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment26 = REL.balancing_segment_value';
218 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT27') THEN
219 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment27 = REL.balancing_segment_value';
220 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT28') THEN
221 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment28 = REL.balancing_segment_value';
222 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT29') THEN
223 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment29 = REL.balancing_segment_value';
224 ELSIF (C_APP_COLUMN_NAME like 'SEGMENT30') THEN
225 C_DYNAMIC_SQL := C_DYNAMIC_SQL || 'AND CC.segment30 = REL.balancing_segment_value';
226 END IF;
227 RETURN (TRUE);
228 EXCEPTION
229 WHEN OTHERS THEN
230 RETURN (FALSE);
231 END GET_APP_COLUMN_NAME;
232
233 FUNCTION C_CURDATE_P RETURN VARCHAR2 IS
234 BEGIN
235 RETURN C_CURDATE;
236 END C_CURDATE_P;
237
238 FUNCTION C_CURR_DATE_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN C_CURR_DATE;
241 END C_CURR_DATE_P;
242
243 FUNCTION C_FROM_ADD_LINE_1_P RETURN VARCHAR2 IS
244 BEGIN
245 RETURN C_FROM_ADD_LINE_1;
246 END C_FROM_ADD_LINE_1_P;
247
248 FUNCTION C_FROM_ADD_LINE_2_P RETURN VARCHAR2 IS
249 BEGIN
250 RETURN C_FROM_ADD_LINE_2;
251 END C_FROM_ADD_LINE_2_P;
252
253 FUNCTION C_FROM_CITY_P RETURN VARCHAR2 IS
254 BEGIN
255 RETURN C_FROM_CITY;
256 END C_FROM_CITY_P;
257
258 FUNCTION C_FROM_ADD_LINE_3_P RETURN VARCHAR2 IS
259 BEGIN
260 RETURN C_FROM_ADD_LINE_3;
261 END C_FROM_ADD_LINE_3_P;
262
263 FUNCTION C_FROM_STATE_P RETURN VARCHAR2 IS
264 BEGIN
265 RETURN C_FROM_STATE;
266 END C_FROM_STATE_P;
267
268 FUNCTION C_FROM_ZIP_P RETURN VARCHAR2 IS
269 BEGIN
270 RETURN C_FROM_ZIP;
271 END C_FROM_ZIP_P;
272
273 FUNCTION C_LOCATION_ID_P RETURN NUMBER IS
274 BEGIN
275 RETURN C_LOCATION_ID;
276 END C_LOCATION_ID_P;
277
278 FUNCTION C_DISP_RETURN_BY_DATE_P RETURN VARCHAR2 IS
279 BEGIN
280 RETURN C_DISP_RETURN_BY_DATE;
281 END C_DISP_RETURN_BY_DATE_P;
282
283 FUNCTION C_FROM_ADD_LINE_4_P RETURN VARCHAR2 IS
284 BEGIN
285 RETURN C_FROM_ADD_LINE_4;
286 END C_FROM_ADD_LINE_4_P;
287
288 FUNCTION C_ATTENTION_LINE_P RETURN VARCHAR2 IS
289 BEGIN
290 RETURN C_ATTENTION_LINE;
291 END C_ATTENTION_LINE_P;
292
293 FUNCTION C_APP_COLUMN_NAME_P RETURN VARCHAR2 IS
294 BEGIN
295 RETURN C_APP_COLUMN_NAME;
296 END C_APP_COLUMN_NAME_P;
297
298 FUNCTION C_CHART_ACCTS_ID_P RETURN NUMBER IS
299 BEGIN
300 RETURN C_CHART_ACCTS_ID;
301 END C_CHART_ACCTS_ID_P;
302
303 FUNCTION C_DYNAMIC_SQL_P RETURN VARCHAR2 IS
304 BEGIN
305 RETURN C_DYNAMIC_SQL;
306 END C_DYNAMIC_SQL_P;
307
308 END AP_APXVDTIN_XMLP_PKG;
309