[Home] [Help]
PACKAGE BODY: APPS.JE_JEITAPEX_XMLP_PKG
Source
1 PACKAGE BODY JE_JEITAPEX_XMLP_PKG AS
2 /* $Header: JEITAPEXB.pls 120.1 2007/12/25 16:55:37 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 P_SET_OF_BKS_ID NUMBER;
5 --P_FIRST_DAY DATE;
6 --P_LAST_DAY DATE;
7 L_LE_ID_COUNT NUMBER;
8 ERRBUF VARCHAR2(200);
9 RETCODE NUMBER;
10 BEGIN
11 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
12 P_LEGAL_ENTITY_ID_V := P_LEGAL_ENTITY_ID;
13 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
14 BEGIN
15 C_LISTNUM := 0;
16 IF P_SET_OF_BOOKS_ID IS NULL THEN
17 P_SET_OF_BKS_ID := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
18 ELSE
19 P_SET_OF_BKS_ID := P_SET_OF_BOOKS_ID;
20 END IF;
21 SELECT
22 count(*)
23 INTO L_LE_ID_COUNT
24 FROM
25 JE_IT_EXEMPT_LETTERS
26 WHERE SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
27 AND LEGAL_ENTITY_ID IS NULL;
28 /*SRW.MESSAGE(1
29 ,L_LE_ID_COUNT)*/NULL;
30 SELECT
31 NVL(A.LEGAL_ENTITY_ID
32 ,B.LEGAL_ENTITY_ID)
33 INTO P_LEGAL_ENTITY_ID_V
34 FROM
35 JG_ZZ_VAT_REP_ENTITIES A,
36 JG_ZZ_VAT_REP_ENTITIES B
37 WHERE A.MAPPING_VAT_REP_ENTITY_ID = B.VAT_REPORTING_ENTITY_ID (+)
38 AND A.VAT_REPORTING_ENTITY_ID = P_REP_IDENTIFIER;
39 IF L_LE_ID_COUNT > 0 THEN
40 JE_IT_TAX_EX_UPGRADE.UPGRADE_MAIN(ERRBUF
41 ,RETCODE
42 ,P_SET_OF_BOOKS_ID
43 ,P_LEGAL_ENTITY_ID_V);
44 END IF;
45 SELECT
46 TO_DATE(START_DATE
47 ,'DD-MM-RRRR')
48 INTO P_FIRST_DAY
49 FROM
50 GL_PERIODS
51 WHERE PERIOD_SET_NAME = P_PERIOD_SET_NAME
52 AND PERIOD_NAME = P_PERIOD_FROM;
53 SELECT
54 TO_DATE(END_DATE
55 ,'DD-MM-RRRR')
56 INTO P_LAST_DAY
57 FROM
58 GL_PERIODS
59 WHERE PERIOD_SET_NAME = P_PERIOD_SET_NAME
60 AND PERIOD_NAME = P_PERIOD_TO;
61 SELECT
62 LEDGER.NAME,
63 LEDGER.CURRENCY_CODE
64 INTO C_OUR_SOB_NAME,C_FUNCTIONAL_CURRENCY
65 FROM
66 GL_LEDGERS_PUBLIC_V LEDGER
67 WHERE LEDGER.LEDGER_ID = P_SET_OF_BKS_ID;
68 DECLARE
69 P_EFFECTIVE_DATE DATE;
70 X_RETURN_STATUS VARCHAR2(100);
71 X_MSG_COUNT NUMBER;
72 X_MSG_DATA VARCHAR2(100);
73 X_ME_PARTY_ID NUMBER;
74 X_REGISTRATION_NUMBER VARCHAR2(100);
75 P_COUNTRY VARCHAR2(100);
76 BEGIN
77 SELECT
78 sysdate
79 INTO P_EFFECTIVE_DATE
80 FROM
81 DUAL;
82 SELECT
83 ETBP.PARTY_ID
84 INTO X_ME_PARTY_ID
85 FROM
86 XLE_ETB_PROFILES ETBP
87 WHERE ETBP.MAIN_ESTABLISHMENT_FLAG = 'Y'
88 AND ETBP.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID_V;
89 IF X_ME_PARTY_ID IS NOT NULL THEN
90 X_REGISTRATION_NUMBER := ZX_API_PUB.GET_DEFAULT_TAX_REG(P_API_VERSION => 1.0
91 ,P_INIT_MSG_LIST => NULL
92 ,P_COMMIT => NULL
93 ,P_VALIDATION_LEVEL => NULL
94 ,X_RETURN_STATUS => X_RETURN_STATUS
95 ,X_MSG_COUNT => X_MSG_COUNT
96 ,X_MSG_DATA => X_MSG_DATA
97 ,P_PARTY_ID => X_ME_PARTY_ID
98 ,P_PARTY_TYPE => 'LEGAL_ESTABLISHMENT'
99 ,P_EFFECTIVE_DATE => P_EFFECTIVE_DATE);
100 END IF;
101 C_VAT_REG_NUM := X_REGISTRATION_NUMBER;
102 SELECT
103 XFIV.NAME,
104 XFIV.ADDRESS_LINE_1,
105 SUBSTR(SUBSTR(XFIV.POSTAL_CODE
106 ,1
107 ,5) || ' ' || XFIV.TOWN_OR_CITY || ' ' || XFIV.REGION_1
108 ,1
109 ,40),
110 SUBSTR(XFIV.POSTAL_CODE
111 ,1
112 ,5) || ' ' || XFIV.TOWN_OR_CITY,
113 SUBSTR(HRL.DESCRIPTION
114 ,1
115 ,43),
116 XFIV.COUNTRY
117 INTO C_LE_NAME,C_OUR_STREET,C_OUR_ZIPCITY,C_OUR_ZIP_CITY,C_LOCATION,P_COUNTRY
118 FROM
119 XLE_FIRSTPARTY_INFORMATION_V XFIV,
120 HR_LOCATIONS HRL
121 WHERE XFIV.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID_V
122 AND XFIV.LOCATION_ID = HRL.LOCATION_ID;
123 C_COUNTRY_CODE := P_COUNTRY;
124 SELECT
125 GEOGRAPHY_NAME
126 INTO C_OUR_COUNTRY
127 FROM
128 HZ_GEOGRAPHIES
129 WHERE GEOGRAPHY_CODE = P_COUNTRY
130 AND GEOGRAPHY_TYPE = 'COUNTRY';
131 SELECT
132 REGISTRATION_NUMBER
133 INTO C_TAXPAYER_ID
134 FROM
135 XLE_REGISTRATIONS_V
136 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID_V
137 AND LEGISLATIVE_CATEGORY = 'INCOME_TAX';
138 SELECT
139 REGISTRATION_NUMBER
140 INTO C_COMMERCIAL_NUM
141 FROM
142 XLE_REGISTRATIONS_V
143 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID_V
144 AND LEGISLATIVE_CATEGORY = 'COMMERCIAL_LAW';
145 EXCEPTION
146 WHEN TOO_MANY_ROWS THEN
147 /*SRW.MESSAGE(9999
148 ,'Esiste piu'' di una company nell'' organizzazione')*/NULL;
149 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
150 WHEN NO_DATA_FOUND THEN
151 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
152 END;
153 END;
154 RETURN (TRUE);
155 END BEFOREREPORT;
156
157 FUNCTION LISTNUMFORMULA RETURN NUMBER IS
158 BEGIN
159 C_LISTNUM := C_LISTNUM + 1;
160 RETURN (C_LISTNUM);
161 END LISTNUMFORMULA;
162
163 FUNCTION PRINT_COMMITFORMULA(PRINT_ROWID IN VARCHAR2) RETURN VARCHAR2 IS
164 pragma autonomous_transaction;
165 BEGIN
166 /*SRW.REFERENCE(PRINT_ROWID)*/NULL;
167
168 BEGIN
169 UPDATE
170 JE_IT_EXEMPT_LETTERS
171 SET
172 PRINT_FLAG = 'N'
173 WHERE ROWID = PRINT_ROWID;
174 commit;
175 END;
176 RETURN NULL;
177 END PRINT_COMMITFORMULA;
178
179 FUNCTION ISSUE_COMMITFORMULA(ISSUE_ROWID IN VARCHAR2) RETURN NUMBER IS
180 pragma autonomous_transaction;
181 BEGIN
182 /*SRW.REFERENCE(ISSUE_ROWID)*/NULL;
183
184 BEGIN
185 UPDATE
186 JE_IT_EXEMPT_LETTERS
187 SET
188 ISSUE_FLAG = 'N'
189 ,ISSUE_DATE = SYSDATE
190 WHERE ROWID = ISSUE_ROWID;
191 commit;
192 END;
193 RETURN (0);
194 END ISSUE_COMMITFORMULA;
195
196 FUNCTION AFTERREPORT RETURN BOOLEAN IS
197 BEGIN
198 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
199 RETURN (TRUE);
200 END AFTERREPORT;
201
202 FUNCTION CF_CURRENT_DATEFORMULA RETURN CHAR IS
203 BEGIN
204 RETURN FND_DATE.DATE_TO_CHARDATE(SYSDATE);
205 END CF_CURRENT_DATEFORMULA;
206
207 FUNCTION CF_PLACE_DATE_ISSUEDFORMULA(CF_CURRENT_DATE IN VARCHAR2) RETURN CHAR IS
208 PLACE_DATE VARCHAR2(40);
209 BEGIN
210 PLACE_DATE := P_PROVINCE || ' ' || CF_CURRENT_DATE;
211 RETURN (PLACE_DATE);
212 END CF_PLACE_DATE_ISSUEDFORMULA;
213
214 FUNCTION CF_PERSON_PROFILEFORMULA(PTYPE IN VARCHAR2
215 ,PARTYID IN NUMBER) RETURN NUMBER IS
216 BEGIN
217 IF PTYPE = 'PERSON' THEN
218 SELECT
219 PLACE_OF_BIRTH || ' ' || DATE_OF_BIRTH,
220 GENDER
221 INTO C_PLACE,C_SEX
222 FROM
223 HZ_PERSON_PROFILES HPP
224 WHERE EXISTS (
225 SELECT
226 null
227 FROM
228 HZ_PARTIES HP
229 WHERE HP.PARTY_ID = HPP.PARTY_ID
230 AND HP.PARTY_NAME = HPP.PERSON_NAME
231 AND HP.PARTY_ID = PARTYID );
232 RETURN (1);
233 ELSE
234 RETURN (0);
235 END IF;
236 END CF_PERSON_PROFILEFORMULA;
237
238 FUNCTION C_OUR_STREET_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN C_OUR_STREET;
241 END C_OUR_STREET_P;
242
243 FUNCTION C_OUR_ZIPCITY_P RETURN VARCHAR2 IS
244 BEGIN
245 RETURN C_OUR_ZIPCITY;
246 END C_OUR_ZIPCITY_P;
247
248 FUNCTION C_LISTNUM_P RETURN NUMBER IS
249 BEGIN
250 RETURN C_LISTNUM;
251 END C_LISTNUM_P;
252
253 FUNCTION C_COMMIT_P RETURN NUMBER IS
254 BEGIN
255 RETURN C_COMMIT;
256 END C_COMMIT_P;
257
258 FUNCTION C_OUR_VATCODE_P RETURN VARCHAR2 IS
259 BEGIN
260 RETURN C_OUR_VATCODE;
261 END C_OUR_VATCODE_P;
262
263 FUNCTION C_OUR_SOB_NAME_P RETURN VARCHAR2 IS
264 BEGIN
265 RETURN C_OUR_SOB_NAME;
266 END C_OUR_SOB_NAME_P;
267
268 FUNCTION C_LOCATION_P RETURN VARCHAR2 IS
269 BEGIN
270 RETURN C_LOCATION;
271 END C_LOCATION_P;
272
273 FUNCTION C_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
274 BEGIN
275 RETURN C_FUNCTIONAL_CURRENCY;
276 END C_FUNCTIONAL_CURRENCY_P;
277
278 FUNCTION C_LE_NAME_P RETURN VARCHAR2 IS
279 BEGIN
280 RETURN C_LE_NAME;
281 END C_LE_NAME_P;
282
283 FUNCTION C_OUR_ZIP_CITY_P RETURN VARCHAR2 IS
284 BEGIN
285 RETURN C_OUR_ZIP_CITY;
286 END C_OUR_ZIP_CITY_P;
287
288 FUNCTION C_OUR_COUNTRY_P RETURN VARCHAR2 IS
289 BEGIN
290 RETURN C_OUR_COUNTRY;
291 END C_OUR_COUNTRY_P;
292
293 FUNCTION C_TAXPAYER_ID_P RETURN VARCHAR2 IS
294 BEGIN
295 RETURN C_TAXPAYER_ID;
296 END C_TAXPAYER_ID_P;
297
298 FUNCTION C_COMMERCIAL_NUM_P RETURN VARCHAR2 IS
299 BEGIN
300 RETURN C_COMMERCIAL_NUM;
301 END C_COMMERCIAL_NUM_P;
302
303 FUNCTION C_VAT_REG_NUM_P RETURN VARCHAR2 IS
304 BEGIN
305 RETURN C_VAT_REG_NUM;
306 END C_VAT_REG_NUM_P;
307
308 FUNCTION C_PLACE_P RETURN VARCHAR2 IS
309 BEGIN
310 RETURN C_PLACE;
311 END C_PLACE_P;
312
313 FUNCTION C_SEX_P RETURN VARCHAR2 IS
314 BEGIN
315 RETURN C_SEX;
316 END C_SEX_P;
317
318 FUNCTION C_COUNTRY_CODE_P RETURN VARCHAR2 IS
319 BEGIN
320 RETURN C_COUNTRY_CODE;
321 END C_COUNTRY_CODE_P;
322
323 FUNCTION P_FIRST_DAY_P RETURN DATE IS
324 BEGIN
325 RETURN P_FIRST_DAY;
326 END P_FIRST_DAY_P;
327
328 FUNCTION P_LAST_DAY_P RETURN DATE IS
329 BEGIN
330 RETURN P_LAST_DAY;
331 END P_LAST_DAY_P;
332
333 END JE_JEITAPEX_XMLP_PKG;
334
335