1 package body ZX_REGISTRATIONS_PKG as
2 /* $Header: zxcregb.pls 120.10 2005/09/16 23:28:22 dbetanco ship $ */
3
4 procedure INSERT_ROW (
5 P_REQUEST_ID in NUMBER,
6 P_ATTRIBUTE1 in VARCHAR2,
7 P_ATTRIBUTE2 in VARCHAR2,
8 P_ATTRIBUTE3 in VARCHAR2,
9 P_ATTRIBUTE4 in VARCHAR2,
10 P_ATTRIBUTE5 in VARCHAR2,
11 P_ATTRIBUTE6 in VARCHAR2,
12 P_VALIDATION_RULE in VARCHAR2,
13 P_ROUNDING_RULE_CODE in VARCHAR2,
14 P_TAX_JURISDICTION_CODE in VARCHAR2,
15 P_SELF_ASSESS_FLAG in VARCHAR2,
16 P_REGISTRATION_STATUS_CODE in VARCHAR2,
17 P_REGISTRATION_SOURCE_CODE in VARCHAR2,
18 P_REGISTRATION_REASON_CODE in VARCHAR2,
19 P_TAX in VARCHAR2,
20 P_TAX_REGIME_CODE in VARCHAR2,
21 P_INCLUSIVE_TAX_FLAG in VARCHAR2,
22 P_EFFECTIVE_FROM in DATE,
23 P_EFFECTIVE_TO in DATE,
24 P_REP_PARTY_TAX_NAME in VARCHAR2,
25 P_DEFAULT_REGISTRATION_FLAG in VARCHAR2,
26 P_BANK_ACCOUNT_NUM in VARCHAR2,
27 P_RECORD_TYPE_CODE in VARCHAR2,
28 P_LEGAL_LOCATION_ID in NUMBER,
29 P_TAX_AUTHORITY_ID in NUMBER,
30 P_REP_TAX_AUTHORITY_ID in NUMBER,
31 P_COLL_TAX_AUTHORITY_ID in NUMBER,
32 P_REGISTRATION_TYPE_CODE in VARCHAR2,
33 P_REGISTRATION_NUMBER in VARCHAR2,
34 P_PARTY_TAX_PROFILE_ID in NUMBER,
35 P_LEGAL_REGISTRATION_ID in NUMBER,
36 P_BANK_ID in NUMBER,
37 P_BANK_BRANCH_ID in NUMBER,
38 P_ACCOUNT_SITE_ID in NUMBER,
39 P_ATTRIBUTE14 in VARCHAR2,
40 P_ATTRIBUTE15 in VARCHAR2,
41 P_ATTRIBUTE_CATEGORY in VARCHAR2,
42 P_PROGRAM_LOGIN_ID in NUMBER,
43 P_ACCOUNT_ID in NUMBER,
44 P_TAX_CLASSIFICATION_CODE in VARCHAR2,
45 P_ATTRIBUTE7 in VARCHAR2,
46 P_ATTRIBUTE8 in VARCHAR2,
47 P_ATTRIBUTE9 in VARCHAR2,
48 P_ATTRIBUTE10 in VARCHAR2,
49 P_ATTRIBUTE11 in VARCHAR2,
50 P_ATTRIBUTE12 in VARCHAR2,
51 P_ATTRIBUTE13 in VARCHAR2,
52 X_RETURN_STATUS out NOCOPY VARCHAR2
53 ) is
54 L_REGISTRATION_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
55 L_DEF_REGISTRATION_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
56 L_REG_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
57 L_REG_ID1 ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
58 L_INCLUSIVE_TAX_FLAG ZX_REGISTRATIONS.INCLUSIVE_TAX_FLAG%TYPE;
59 L_SELF_ASSESS_FLAG ZX_REGISTRATIONS.SELF_ASSESS_FLAG%TYPE;
60 L_ROUNDING_RULE_CODE ZX_REGISTRATIONS.ROUNDING_RULE_CODE%TYPE;
61 L_COUNTRY_CODE ZX_REGIMES_B.COUNTRY_CODE%TYPE;
62 L_PARTY_TYPE_CODE ZX_PARTY_TAX_PROFILE.PARTY_TYPE_CODE%TYPE;
63 x_party_type_token VARCHAR2(1000);
64 x_party_name_token VARCHAR2(5000);
65 x_party_site_name_token VARCHAR2(5000);
66 x_error_buffer VARCHAR2(80);
67 x_return_status1 VARCHAR2(80);
68 CURSOR reg_cur IS SELECT REGISTRATION_ID FROM ZX_REGISTRATIONS
69 WHERE REGISTRATION_ID = L_REGISTRATION_ID;
70 --Get Default Registration for current PTP_ID
71 CURSOR default_reg_cur IS SELECT REGISTRATION_ID FROM ZX_REGISTRATIONS
72 WHERE PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID
73 AND DEFAULT_REGISTRATION_FLAG = 'Y';
74 begin
75 --Initialise x_return_status variable
76 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
77 --Inclusive_tax_flag, ROUNDING_RULE_CODE and self_assess_flag for tax registrations are defaulted primary from party tax profile.
78 BEGIN
79 select INCLUSIVE_TAX_FLAG, SELF_ASSESS_FLAG, ROUNDING_RULE_CODE, PARTY_TYPE_CODE
80 INTO L_INCLUSIVE_TAX_FLAG, L_SELF_ASSESS_FLAG, L_ROUNDING_RULE_CODE, L_PARTY_TYPE_CODE
81 from ZX_PARTY_TAX_PROFILE
82 where PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
83 EXCEPTION WHEN NO_DATA_FOUND THEN
84 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
85 arp_util_tax.debug('Error: "No Party created in ZX_PARTY_TAX_PROFILE for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
86 END;
87 --Effective From cannot be null
88 IF P_EFFECTIVE_FROM IS NULL THEN
89 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
90 arp_util_tax.debug('Error: "Effective From cannot be null for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
91 END IF;
92 --Tax Regime Code cannot be null
93 IF P_TAX_REGIME_CODE IS NULL THEN
94 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
95 arp_util_tax.debug('Error: "Tax Regime Code cannot be null for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
96 END IF;
97 IF (P_EFFECTIVE_FROM IS NOT NULL) AND (P_EFFECTIVE_FROM > P_EFFECTIVE_TO) THEN
98 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
99 arp_util_tax.debug('Error: "The Effective To date must be the same as or after the Effective From date. for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
100 END IF;
101 IF (P_TAX IS NULL) AND (P_TAX_JURISDICTION_CODE IS NOT NULL) THEN
102 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
103 arp_util_tax.debug('Error: "Tax must be entered if the Tax Jursidiction Code has been specified for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
104 END IF;
105 --Tax is mandatory when Jurisdiction is not null
106 IF (P_TAX_REGIME_CODE IS NOT NULL) AND (P_PARTY_TAX_PROFILE_ID IS NOT NULL) THEN
107 --Registration should be unique
108 BEGIN
109 SELECT REGISTRATION_ID
110 INTO L_REG_ID
111 FROM ZX_REGISTRATIONS
112 WHERE ((P_EFFECTIVE_TO is NULL AND EFFECTIVE_TO is NULL)
113 OR (P_EFFECTIVE_FROM BETWEEN EFFECTIVE_FROM
114 AND NVL(EFFECTIVE_TO,P_EFFECTIVE_TO ) )
115 OR ( NVL(P_EFFECTIVE_TO, EFFECTIVE_TO+1) BETWEEN EFFECTIVE_FROM
116 AND NVL(EFFECTIVE_TO ,P_EFFECTIVE_TO ) )
117 OR (P_EFFECTIVE_FROM < EFFECTIVE_FROM
118 AND (NVL(P_EFFECTIVE_TO, EFFECTIVE_TO) > EFFECTIVE_TO OR P_EFFECTIVE_TO IS NULL)))
119 AND (REGISTRATION_NUMBER = P_REGISTRATION_NUMBER OR (P_REGISTRATION_NUMBER IS NULL and REGISTRATION_NUMBER is null))
120 AND TAX_REGIME_CODE = P_TAX_REGIME_CODE
121 AND (TAX = P_TAX OR (P_TAX IS NULL and TAX is null))
122 AND (TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE OR (P_TAX_JURISDICTION_CODE IS NULL and TAX_JURISDICTION_CODE is null))
123 AND PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
124 EXCEPTION WHEN NO_DATA_FOUND THEN
125 NULL;
126 END;
127 --Registration should be unique at each level
128 BEGIN
129 SELECT REGISTRATION_ID
130 INTO L_REG_ID1
131 FROM ZX_REGISTRATIONS
132 WHERE ((P_EFFECTIVE_TO is NULL AND EFFECTIVE_TO is NULL)
133 OR (P_EFFECTIVE_FROM BETWEEN EFFECTIVE_FROM
134 AND NVL(EFFECTIVE_TO,P_EFFECTIVE_TO ) )
135 OR ( NVL(P_EFFECTIVE_TO, EFFECTIVE_TO+1) BETWEEN EFFECTIVE_FROM
136 AND NVL(EFFECTIVE_TO ,P_EFFECTIVE_TO ) )
137 OR (P_EFFECTIVE_FROM < EFFECTIVE_FROM
138 AND (NVL(P_EFFECTIVE_TO, EFFECTIVE_TO) > EFFECTIVE_TO OR P_EFFECTIVE_TO IS NULL)))
139 AND NOT(REGISTRATION_NUMBER = P_REGISTRATION_NUMBER OR (P_REGISTRATION_NUMBER IS NULL and REGISTRATION_NUMBER is null))
140 AND TAX_REGIME_CODE = P_TAX_REGIME_CODE
141 AND (TAX = P_TAX OR (P_TAX IS NULL and TAX is null))
142 AND (TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE OR (P_TAX_JURISDICTION_CODE IS NULL and TAX_JURISDICTION_CODE is null))
143 AND PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
144 EXCEPTION WHEN NO_DATA_FOUND THEN
145 NULL;
146 END;
147 END IF;
148 IF L_REG_ID IS NOT NULL THEN
149 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
150 arp_util_tax.debug('Error: "The registration details already exist for this party for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
151 END IF;
152 IF L_REG_ID1 IS NOT NULL THEN
153 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
154 arp_util_tax.debug('Error: "This party already has a registration for this Jurisdiction for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
155 END IF;
156 --If the registration is marked as default, then registration number must be entered.
157 IF P_DEFAULT_REGISTRATION_FLAG = 'Y' AND P_REGISTRATION_NUMBER IS NULL THEN
158 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
159 arp_util_tax.debug('Error: "Tax Registration Number is not valid for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
160 --Check RegistrationNumber validity
161 ELSIF (P_REGISTRATION_NUMBER IS NOT NULL AND P_LEGAL_REGISTRATION_ID IS NULL) THEN
162 BEGIN
163 BEGIN
164 SELECT COUNTRY_CODE
165 INTO L_COUNTRY_CODE
166 FROM ZX_REGIMES_B
167 WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE;
168 EXCEPTION WHEN NO_DATA_FOUND THEN
169 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
170 arp_util_tax.debug('Error: "No CountryCOde exists in ZX_REGIMES_B for the Registration with Regime : "
171 ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
172 END;
173 /*
174 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN
175 (l_country_code,
176 p_registration_number,
177 p_tax_regime_code,
178 p_tax,
179 p_party_tax_profile_id,
180 l_party_type_code,
181 p_registration_type_code,
182 x_error_buffer,
183 x_return_status1,
184 x_party_type_token,
185 x_party_name_token,
186 x_party_site_name_token
187 );
188 */
189 x_return_status1 := 'S'; -- P1
190 IF x_return_status1 <> 'S' THEN
191 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
192 arp_util_tax.debug('Error: "Registration Number is invalid for the Registration with Regime : "
193 ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
194 END IF;
195 END;
196 END IF;
197 -- Check the Default Registration status and accordingly populates corresponding
198 -- Registration Number to PartyTaxProfile entity
199 IF P_DEFAULT_REGISTRATION_FLAG = 'Y' THEN
200 OPEN default_reg_cur;
201 FETCH default_reg_cur INTO L_DEF_REGISTRATION_ID;
202 --If this is the only defaulted registration then update PTP
203 IF (default_reg_cur%notfound) AND (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) then
204 --Update PTP
205 UPDATE ZX_PARTY_TAX_PROFILE
206 SET Rep_Registration_Number = P_REGISTRATION_NUMBER,
207 Object_Version_Number = Object_Version_Number + 1
208 WHERE PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID
209 ;
210 ELSE
211 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
212 arp_util_tax.debug('Error: "Already a Regiatration has been defaulted for Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
213 END IF;
214 CLOSE default_reg_cur;
215 END IF;--end P_DEFAULT_REGISTRATION_FLAG
216 --Insert only when there is no error
217 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
218 select ZX_REGISTRATIONS_S.nextval into L_REGISTRATION_ID from dual;
219 insert into ZX_REGISTRATIONS (
220 REQUEST_ID,
221 ATTRIBUTE1,
222 ATTRIBUTE2,
223 ATTRIBUTE3,
224 ATTRIBUTE4,
225 ATTRIBUTE5,
226 ATTRIBUTE6,
227 VALIDATION_RULE,
228 ROUNDING_RULE_CODE,
229 TAX_JURISDICTION_CODE,
230 SELF_ASSESS_FLAG,
231 REGISTRATION_STATUS_CODE,
232 REGISTRATION_SOURCE_CODE,
233 REGISTRATION_REASON_CODE,
234 TAX,
235 TAX_REGIME_CODE,
236 INCLUSIVE_TAX_FLAG,
237 EFFECTIVE_FROM,
238 EFFECTIVE_TO,
239 REP_PARTY_TAX_NAME,
240 DEFAULT_REGISTRATION_FLAG,
241 BANK_ACCOUNT_NUM,
242 RECORD_TYPE_CODE,
243 REGISTRATION_ID,
244 LEGAL_LOCATION_ID,
248 REGISTRATION_TYPE_CODE,
245 TAX_AUTHORITY_ID,
246 REP_TAX_AUTHORITY_ID,
247 COLL_TAX_AUTHORITY_ID,
249 REGISTRATION_NUMBER,
250 PARTY_TAX_PROFILE_ID,
251 LEGAL_REGISTRATION_ID,
252 BANK_ID,
253 BANK_BRANCH_ID,
254 ACCOUNT_SITE_ID,
255 ATTRIBUTE14,
256 ATTRIBUTE15,
257 ATTRIBUTE_CATEGORY,
258 PROGRAM_LOGIN_ID,
259 ACCOUNT_ID,
260 TAX_CLASSIFICATION_CODE,
261 ATTRIBUTE7,
262 ATTRIBUTE8,
263 ATTRIBUTE9,
264 ATTRIBUTE10,
265 ATTRIBUTE11,
266 ATTRIBUTE12,
267 ATTRIBUTE13,
268 CREATION_DATE,
269 CREATED_BY,
270 LAST_UPDATE_DATE,
271 LAST_UPDATED_BY,
272 LAST_UPDATE_LOGIN,
273 OBJECT_VERSION_NUMBER
274 ) values (
275 P_REQUEST_ID,
276 P_ATTRIBUTE1,
277 P_ATTRIBUTE2,
278 P_ATTRIBUTE3,
279 P_ATTRIBUTE4,
280 P_ATTRIBUTE5,
281 P_ATTRIBUTE6,
282 P_VALIDATION_RULE,
283 NVL(P_ROUNDING_RULE_CODE, L_ROUNDING_RULE_CODE),
284 P_TAX_JURISDICTION_CODE,
285 NVL(P_SELF_ASSESS_FLAG, L_SELF_ASSESS_FLAG),
286 P_REGISTRATION_STATUS_CODE,
287 P_REGISTRATION_SOURCE_CODE,
288 P_REGISTRATION_REASON_CODE,
289 P_TAX,
290 P_TAX_REGIME_CODE,
291 NVL(P_INCLUSIVE_TAX_FLAG, L_INCLUSIVE_TAX_FLAG),
292 NVL(P_EFFECTIVE_FROM, SYSDATE),
293 P_EFFECTIVE_TO,
294 P_REP_PARTY_TAX_NAME,
295 P_DEFAULT_REGISTRATION_FLAG,
296 P_BANK_ACCOUNT_NUM,
297 P_RECORD_TYPE_CODE,
298 L_REGISTRATION_ID,
299 P_LEGAL_LOCATION_ID,
300 P_TAX_AUTHORITY_ID,
301 P_REP_TAX_AUTHORITY_ID,
302 P_COLL_TAX_AUTHORITY_ID,
303 P_REGISTRATION_TYPE_CODE,
304 P_REGISTRATION_NUMBER,
305 P_PARTY_TAX_PROFILE_ID,
306 P_LEGAL_REGISTRATION_ID,
307 P_BANK_ID,
308 P_BANK_BRANCH_ID,
309 P_ACCOUNT_SITE_ID,
310 P_ATTRIBUTE14,
311 P_ATTRIBUTE15,
312 P_ATTRIBUTE_CATEGORY,
313 P_PROGRAM_LOGIN_ID,
314 P_ACCOUNT_ID,
315 P_TAX_CLASSIFICATION_CODE,
316 P_ATTRIBUTE7,
317 P_ATTRIBUTE8,
318 P_ATTRIBUTE9,
319 P_ATTRIBUTE10,
320 P_ATTRIBUTE11,
321 P_ATTRIBUTE12,
322 P_ATTRIBUTE13,
323 sysdate,
324 FND_GLOBAL.User_ID,
325 sysdate,
326 FND_GLOBAL.User_ID,
327 FND_GLOBAL.Login_ID,
328 1
329 );
330 OPEN reg_cur;
331 FETCH reg_cur INTO L_REGISTRATION_ID;
332 IF (reg_cur%notfound) then
333 CLOSE reg_cur;
334 --Set x_return_status param
335 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
336 arp_util_tax.debug('Error: "The Registration row has not been inserted for Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
337 END IF;--end reg_cur%notfound
338 CLOSE reg_cur;
339 END IF;--end X_RETURN_STATUS
340 EXCEPTION
341 --Index violation check
342 WHEN DUP_VAL_ON_INDEX THEN
343 --Set x_return_status param
344 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
345 arp_util_tax.debug('Error: "The Registration row already exists for Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
346 end INSERT_ROW;
347
348 procedure UPDATE_ROW (
349 P_REGISTRATION_ID in NUMBER,
350 P_REQUEST_ID in NUMBER,
351 P_ATTRIBUTE1 in VARCHAR2,
352 P_ATTRIBUTE2 in VARCHAR2,
353 P_ATTRIBUTE3 in VARCHAR2,
354 P_ATTRIBUTE4 in VARCHAR2,
355 P_ATTRIBUTE5 in VARCHAR2,
356 P_ATTRIBUTE6 in VARCHAR2,
357 P_VALIDATION_RULE in VARCHAR2,
358 P_ROUNDING_RULE_CODE in VARCHAR2,
359 P_TAX_JURISDICTION_CODE in VARCHAR2,
360 P_SELF_ASSESS_FLAG in VARCHAR2,
361 P_REGISTRATION_STATUS_CODE in VARCHAR2,
362 P_REGISTRATION_SOURCE_CODE in VARCHAR2,
363 P_REGISTRATION_REASON_CODE in VARCHAR2,
364 P_TAX in VARCHAR2,
365 P_TAX_REGIME_CODE in VARCHAR2,
366 P_INCLUSIVE_TAX_FLAG in VARCHAR2,
367 P_EFFECTIVE_FROM in DATE,
368 P_EFFECTIVE_TO in DATE,
369 P_REP_PARTY_TAX_NAME in VARCHAR2,
370 P_DEFAULT_REGISTRATION_FLAG in VARCHAR2,
371 P_BANK_ACCOUNT_NUM in VARCHAR2,
372 P_RECORD_TYPE_CODE in VARCHAR2,
373 P_LEGAL_LOCATION_ID in NUMBER,
374 P_TAX_AUTHORITY_ID in NUMBER,
375 P_REP_TAX_AUTHORITY_ID in NUMBER,
376 P_COLL_TAX_AUTHORITY_ID in NUMBER,
377 P_REGISTRATION_TYPE_CODE in VARCHAR2,
378 P_REGISTRATION_NUMBER in VARCHAR2,
379 P_PARTY_TAX_PROFILE_ID in NUMBER,
380 P_LEGAL_REGISTRATION_ID in NUMBER,
381 P_BANK_ID in NUMBER,
382 P_BANK_BRANCH_ID in NUMBER,
383 P_ACCOUNT_SITE_ID in NUMBER,
384 P_ATTRIBUTE14 in VARCHAR2,
385 P_ATTRIBUTE15 in VARCHAR2,
386 P_ATTRIBUTE_CATEGORY in VARCHAR2,
387 P_PROGRAM_LOGIN_ID in NUMBER,
388 P_ACCOUNT_ID in NUMBER,
389 P_TAX_CLASSIFICATION_CODE in VARCHAR2,
390 P_ATTRIBUTE7 in VARCHAR2,
391 P_ATTRIBUTE8 in VARCHAR2,
392 P_ATTRIBUTE9 in VARCHAR2,
393 P_ATTRIBUTE10 in VARCHAR2,
394 P_ATTRIBUTE11 in VARCHAR2,
395 P_ATTRIBUTE12 in VARCHAR2,
396 P_ATTRIBUTE13 in VARCHAR2,
397 X_RETURN_STATUS out NOCOPY VARCHAR2
398 ) is
399 L_REGISTRATION_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
400 L_DEF_REGISTRATION_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
401 L_REG_ID ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
402 L_REG_ID1 ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
403 --If Registration has active exemptions then it cannot be end dated.
404 -----Commented out since exemption is not based on registrations
405 /* CURSOR exempt_cur IS
406 SELECT EFFECTIVE_TO
407 FROM ZX_EXEMPTIONS
411 --Get Default Registration for current PTP_ID
408 WHERE TAX_REGISTRATION_ID = P_REGISTRATION_ID;
409 */
410 -----Commented out since exemption is not based on registrations
412 CURSOR default_reg_cur IS SELECT REGISTRATION_ID FROM ZX_REGISTRATIONS
413 WHERE PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID
414 AND DEFAULT_REGISTRATION_FLAG = 'Y'
415 AND REGISTRATION_ID <> P_REGISTRATION_ID;
416 begin
417 --Initialise x_return_status variable
418 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
419 --Effective From cannot be null
420 IF P_EFFECTIVE_FROM IS NULL THEN
421 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
422 arp_util_tax.debug('Error: "Effective From cannot be null for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
423 END IF;
424 --Tax Regime Code cannot be null
425 IF P_TAX_REGIME_CODE IS NULL THEN
426 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
427 arp_util_tax.debug('Error: "Tax Regime Code cannot be null for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
428 END IF;
429 IF (P_EFFECTIVE_FROM IS NOT NULL) AND (P_EFFECTIVE_FROM > P_EFFECTIVE_TO) THEN
430 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
431 arp_util_tax.debug('Error: "The Effective To date must be the same as or after the Effective From date. for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
432 END IF;
433 --Tax is mandatory when Jurisdiction is not null
434 IF (P_TAX IS NULL) AND (P_TAX_JURISDICTION_CODE IS NOT NULL) THEN
435 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
436 arp_util_tax.debug('Error: "Tax must be entered if the Tax Jursidiction Code has been specified for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
437 END IF;
438 IF (P_TAX_REGIME_CODE IS NOT NULL) AND (P_PARTY_TAX_PROFILE_ID IS NOT NULL) THEN
439 --Registration should be unique
440 BEGIN
441 SELECT REGISTRATION_ID
442 INTO L_REG_ID
443 FROM ZX_REGISTRATIONS
444 WHERE ((P_EFFECTIVE_TO is NULL AND EFFECTIVE_TO is NULL)
445 OR (P_EFFECTIVE_FROM BETWEEN EFFECTIVE_FROM
446 AND NVL(EFFECTIVE_TO,P_EFFECTIVE_TO ) )
447 OR ( NVL(P_EFFECTIVE_TO, EFFECTIVE_TO+1) BETWEEN EFFECTIVE_FROM
448 AND NVL(EFFECTIVE_TO ,P_EFFECTIVE_TO ) )
449 OR (P_EFFECTIVE_FROM < EFFECTIVE_FROM
450 AND (NVL(P_EFFECTIVE_TO, EFFECTIVE_TO) > EFFECTIVE_TO OR P_EFFECTIVE_TO IS NULL)))
451 AND (REGISTRATION_NUMBER = P_REGISTRATION_NUMBER OR (P_REGISTRATION_NUMBER IS NULL and REGISTRATION_NUMBER is null))
452 AND TAX_REGIME_CODE = P_TAX_REGIME_CODE
453 AND (TAX = P_TAX OR (P_TAX IS NULL and TAX is null))
454 AND (TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE OR (P_TAX_JURISDICTION_CODE IS NULL and TAX_JURISDICTION_CODE is null))
455 AND PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID
456 AND REGISTRATION_ID <> P_REGISTRATION_ID;
457 EXCEPTION WHEN NO_DATA_FOUND THEN
458 NULL;
459 END;
460 --Registration should be unique at each level
461 BEGIN
462 SELECT REGISTRATION_ID
463 INTO L_REG_ID1
464 FROM ZX_REGISTRATIONS
465 WHERE ((P_EFFECTIVE_TO is NULL AND EFFECTIVE_TO is NULL)
466 OR (P_EFFECTIVE_FROM BETWEEN EFFECTIVE_FROM
467 AND NVL(EFFECTIVE_TO,P_EFFECTIVE_TO ) )
468 OR ( NVL(P_EFFECTIVE_TO, EFFECTIVE_TO+1) BETWEEN EFFECTIVE_FROM
469 AND NVL(EFFECTIVE_TO ,P_EFFECTIVE_TO ) )
470 OR (P_EFFECTIVE_FROM < EFFECTIVE_FROM
471 AND (NVL(P_EFFECTIVE_TO, EFFECTIVE_TO) > EFFECTIVE_TO OR P_EFFECTIVE_TO IS NULL)))
472 AND NOT(REGISTRATION_NUMBER = P_REGISTRATION_NUMBER OR (P_REGISTRATION_NUMBER IS NULL and REGISTRATION_NUMBER is null))
473 AND TAX_REGIME_CODE = P_TAX_REGIME_CODE
474 AND (TAX = P_TAX OR (P_TAX IS NULL and TAX is null))
475 AND (TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE OR (P_TAX_JURISDICTION_CODE IS NULL and TAX_JURISDICTION_CODE is null))
476 AND PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID
477 AND REGISTRATION_ID <> P_REGISTRATION_ID;
478 EXCEPTION WHEN NO_DATA_FOUND THEN
479 NULL;
480 END;
481 END IF;
482 IF L_REG_ID IS NOT NULL THEN
483 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
484 arp_util_tax.debug('Error: "The registration details already exist for this party for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
485 END IF;
486 IF L_REG_ID1 IS NOT NULL THEN
487 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
488 arp_util_tax.debug('Error: "This party already has a registration for this Jurisdiction for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
489 END IF;
490 --If Registration has active exemptions then it cannot be end dated.
491 -----Commented out since exemption is not based on registrations
492 /*IF P_EFFECTIVE_TO IS NOT NULL THEN
493 FOR exempt_rec IN exempt_cur LOOP
494 IF exempt_rec.EFFECTIVE_TO IS NOT NULL THEN
495 IF P_EFFECTIVE_TO < exempt_rec.EFFECTIVE_TO THEN
496 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
497 arp_util_tax.debug('Error: "ZX_EXEMPT_EXISTS_FOR_REG for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
498 EXIT;
499 END IF;
500 ELSE --exempt_rec.EFFECTIVE_TO IS NULL
501 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
505 END LOOP;
502 arp_util_tax.debug('Error: "ZX_EXEMPT_EXISTS_FOR_REG for the Registration with Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
503 EXIT;
504 END IF; -- exempt_rec.EFFECTIVE_TO
506 END IF; -- end P_EFFECTIVE_TO
507 */
508 -----Commented out since exemption is not based on registrations
509 --Check the Default Registration status and accordingly populates corresponding Registration Number to PartyTaxProfile entity
510 IF P_DEFAULT_REGISTRATION_FLAG = 'Y' THEN
511 OPEN default_reg_cur;
512 FETCH default_reg_cur INTO L_DEF_REGISTRATION_ID;
513 --If this is the only defaulted registration then update PTP
514 IF (default_reg_cur%notfound) AND (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) then
515 --Update PTP
516 UPDATE ZX_PARTY_TAX_PROFILE SET Rep_Registration_Number = P_REGISTRATION_NUMBER,
517 Object_Version_Number = Object_Version_Number + 1
518 WHERE PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
519
520 ELSE
521 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
522 arp_util_tax.debug('Error: "Already a Regiatration has been defaulted for Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
523 END IF;
524 CLOSE default_reg_cur;
525 END IF;--end P_DEFAULT_REGISTRATION_FLAG
526 --Update only when there is no error
527 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
528 update ZX_REGISTRATIONS set
529 REQUEST_ID = DECODE( P_REQUEST_ID, NULL, REQUEST_ID, P_REQUEST_ID ),
530 ATTRIBUTE1 = DECODE( P_ATTRIBUTE1, NULL, ATTRIBUTE1, P_ATTRIBUTE1 ),
531 ATTRIBUTE2 = DECODE( P_ATTRIBUTE2, NULL, ATTRIBUTE2, P_ATTRIBUTE2 ),
532 ATTRIBUTE3 = DECODE( P_ATTRIBUTE3, NULL, ATTRIBUTE3, P_ATTRIBUTE3 ),
533 ATTRIBUTE4 = DECODE( P_ATTRIBUTE4, NULL, ATTRIBUTE4, P_ATTRIBUTE4 ),
534 ATTRIBUTE5 = DECODE( P_ATTRIBUTE5, NULL, ATTRIBUTE5, P_ATTRIBUTE5 ),
535 ATTRIBUTE6 = DECODE( P_ATTRIBUTE6, NULL, ATTRIBUTE6, P_ATTRIBUTE6 ),
536 VALIDATION_RULE = DECODE( P_VALIDATION_RULE, NULL, VALIDATION_RULE, P_VALIDATION_RULE ),
537 ROUNDING_RULE_CODE = DECODE( P_ROUNDING_RULE_CODE, NULL, ROUNDING_RULE_CODE, P_ROUNDING_RULE_CODE ),
538 TAX_JURISDICTION_CODE = DECODE( P_TAX_JURISDICTION_CODE, NULL, TAX_JURISDICTION_CODE, P_TAX_JURISDICTION_CODE ),
539 SELF_ASSESS_FLAG = DECODE( P_SELF_ASSESS_FLAG, NULL, SELF_ASSESS_FLAG, P_SELF_ASSESS_FLAG ),
540 REGISTRATION_STATUS_CODE = DECODE( P_REGISTRATION_STATUS_CODE, NULL, REGISTRATION_STATUS_CODE, P_REGISTRATION_STATUS_CODE ),
541 REGISTRATION_SOURCE_CODE = DECODE( P_REGISTRATION_SOURCE_CODE, NULL, REGISTRATION_SOURCE_CODE, P_REGISTRATION_SOURCE_CODE ),
542 REGISTRATION_REASON_CODE = DECODE( P_REGISTRATION_REASON_CODE, NULL, REGISTRATION_REASON_CODE, P_REGISTRATION_REASON_CODE ),
543 TAX = DECODE( P_TAX, NULL, TAX, P_TAX ),
544 TAX_REGIME_CODE = DECODE( P_TAX_REGIME_CODE, NULL, TAX_REGIME_CODE, P_TAX_REGIME_CODE ),
545 INCLUSIVE_TAX_FLAG = DECODE( P_INCLUSIVE_TAX_FLAG, NULL, INCLUSIVE_TAX_FLAG, P_INCLUSIVE_TAX_FLAG ),
546 EFFECTIVE_FROM = DECODE( P_EFFECTIVE_FROM, NULL, EFFECTIVE_FROM, P_EFFECTIVE_FROM ),
547 EFFECTIVE_TO = DECODE( P_EFFECTIVE_TO, NULL, EFFECTIVE_TO, P_EFFECTIVE_TO ),
548 REP_PARTY_TAX_NAME = DECODE( P_REP_PARTY_TAX_NAME, NULL, REP_PARTY_TAX_NAME, P_REP_PARTY_TAX_NAME ),
549 DEFAULT_REGISTRATION_FLAG = DECODE( P_DEFAULT_REGISTRATION_FLAG, NULL, DEFAULT_REGISTRATION_FLAG, P_DEFAULT_REGISTRATION_FLAG ),
550 BANK_ACCOUNT_NUM = DECODE( P_BANK_ACCOUNT_NUM, NULL, BANK_ACCOUNT_NUM, P_BANK_ACCOUNT_NUM ),
551 RECORD_TYPE_CODE = DECODE( P_RECORD_TYPE_CODE, NULL, RECORD_TYPE_CODE, P_RECORD_TYPE_CODE ),
552 LEGAL_LOCATION_ID = DECODE( P_LEGAL_LOCATION_ID, NULL, LEGAL_LOCATION_ID, P_LEGAL_LOCATION_ID ),
553 TAX_AUTHORITY_ID = DECODE( P_TAX_AUTHORITY_ID, NULL, TAX_AUTHORITY_ID, P_TAX_AUTHORITY_ID ),
554 REP_TAX_AUTHORITY_ID = DECODE( P_REP_TAX_AUTHORITY_ID, NULL, REP_TAX_AUTHORITY_ID, P_REP_TAX_AUTHORITY_ID ),
555 COLL_TAX_AUTHORITY_ID = DECODE( P_COLL_TAX_AUTHORITY_ID, NULL, COLL_TAX_AUTHORITY_ID, P_COLL_TAX_AUTHORITY_ID ),
556 REGISTRATION_TYPE_CODE = DECODE( P_REGISTRATION_TYPE_CODE, NULL, REGISTRATION_TYPE_CODE, P_REGISTRATION_TYPE_CODE ),
557 REGISTRATION_NUMBER = DECODE( P_REGISTRATION_NUMBER, NULL, REGISTRATION_NUMBER, P_REGISTRATION_NUMBER ),
558 PARTY_TAX_PROFILE_ID = DECODE( P_PARTY_TAX_PROFILE_ID, NULL, PARTY_TAX_PROFILE_ID, P_PARTY_TAX_PROFILE_ID ),
559 LEGAL_REGISTRATION_ID = DECODE( P_LEGAL_REGISTRATION_ID, NULL, LEGAL_REGISTRATION_ID, P_LEGAL_REGISTRATION_ID ),
560 BANK_ID = DECODE( P_BANK_ID, NULL, BANK_ID, P_BANK_ID ),
561 BANK_BRANCH_ID = DECODE( P_BANK_BRANCH_ID, NULL, BANK_BRANCH_ID, P_BANK_BRANCH_ID ),
562 ACCOUNT_SITE_ID = DECODE( P_ACCOUNT_SITE_ID, NULL, ACCOUNT_SITE_ID, P_ACCOUNT_SITE_ID ),
563 ATTRIBUTE14 = DECODE( P_ATTRIBUTE14, NULL, ATTRIBUTE14, P_ATTRIBUTE14 ),
564 ATTRIBUTE15 = DECODE( P_ATTRIBUTE15, NULL, ATTRIBUTE15, P_ATTRIBUTE15 ),
565 ATTRIBUTE_CATEGORY = DECODE( P_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY ),
566 PROGRAM_LOGIN_ID = DECODE( P_PROGRAM_LOGIN_ID, NULL, PROGRAM_LOGIN_ID, P_PROGRAM_LOGIN_ID ),
567 ACCOUNT_ID = DECODE( P_ACCOUNT_ID, NULL, ACCOUNT_ID, P_ACCOUNT_ID ),
568 TAX_CLASSIFICATION_CODE = DECODE( P_TAX_CLASSIFICATION_CODE, NULL, TAX_CLASSIFICATION_CODE, P_TAX_CLASSIFICATION_CODE ),
569 ATTRIBUTE7 = DECODE( P_ATTRIBUTE7, NULL, ATTRIBUTE7, P_ATTRIBUTE7 ),
570 ATTRIBUTE8 = DECODE( P_ATTRIBUTE8, NULL, ATTRIBUTE8, P_ATTRIBUTE8 ),
571 ATTRIBUTE9 = DECODE( P_ATTRIBUTE9, NULL, ATTRIBUTE9, P_ATTRIBUTE9 ),
572 ATTRIBUTE10 = DECODE( P_ATTRIBUTE10, NULL, ATTRIBUTE10, P_ATTRIBUTE10 ),
573 ATTRIBUTE11 = DECODE( P_ATTRIBUTE11, NULL, ATTRIBUTE11, P_ATTRIBUTE11 ),
574 ATTRIBUTE12 = DECODE( P_ATTRIBUTE12, NULL, ATTRIBUTE12, P_ATTRIBUTE12 ),
575 ATTRIBUTE13 = DECODE( P_ATTRIBUTE13, NULL, ATTRIBUTE13, P_ATTRIBUTE13 ),
576 LAST_UPDATE_DATE = sysdate,
577 LAST_UPDATED_BY = FND_GLOBAL.User_ID,
578 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_ID,
579 Object_Version_Number = Object_Version_Number + 1
580 where REGISTRATION_ID = P_REGISTRATION_ID;
581 if (sql%notfound) then
582 --Set x_return_status param
583 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
584 arp_util_tax.debug('Error: "The Registration row has not been updated for Regime : " ' || P_TAX_REGIME_CODE || ' and Tax: ' ||P_TAX || ' and Jurisdiction: ' || P_TAX_JURISDICTION_CODE);
585 end if;--end sql%notfound
586 END IF;--end X_RETURN_STATUS
587 end UPDATE_ROW;
588
589 procedure DELETE_ROW (
590 P_REGISTRATION_ID in NUMBER,
591 X_RETURN_STATUS out NOCOPY VARCHAR2
592 ) is
593 begin
594 --Initialise x_return_status variable
595 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
596 --Delete the row
597 delete from ZX_REGISTRATIONS
598 where REGISTRATION_ID = P_REGISTRATION_ID;
599
600 if (sql%notfound) then
601 --Set x_return_status param
602 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
603 raise no_data_found;
604 end if;
605 end DELETE_ROW;
606
607 end ZX_REGISTRATIONS_PKG;
[Home] [Help]