[Home] [Help]
PACKAGE BODY: APPS.ZX_PARTY_TAX_PROFILE_PKG
Source
1 package body ZX_PARTY_TAX_PROFILE_PKG as
2 /* $Header: zxcptytaxprfileb.pls 120.13 2010/12/22 14:13:38 srajapar ship $ */
3
4
5 procedure INSERT_ROW (
6 p_collecting_authority_flag IN VARCHAR2,
7 p_provider_type_code IN VARCHAR2,
8 p_create_awt_dists_type_code IN VARCHAR2,
9 p_create_awt_invoices_type_cod IN VARCHAR2,
10 p_tax_classification_code IN VARCHAR2,
11 p_self_assess_flag IN VARCHAR2,
12 p_allow_offset_tax_flag IN VARCHAR2,
13 p_rep_registration_number IN VARCHAR2,
14 p_effective_from_use_le IN DATE,
15 p_record_type_code IN VARCHAR2,
16 p_request_id IN NUMBER,
17 p_attribute1 IN VARCHAR2,
18 p_attribute2 IN VARCHAR2,
19 p_attribute3 IN VARCHAR2,
20 p_attribute4 IN VARCHAR2,
21 p_attribute5 IN VARCHAR2,
22 p_attribute6 IN VARCHAR2,
23 p_attribute7 IN VARCHAR2,
24 p_attribute8 IN VARCHAR2,
25 p_attribute9 IN VARCHAR2,
26 p_attribute10 IN VARCHAR2,
27 p_attribute11 IN VARCHAR2,
28 p_attribute12 IN VARCHAR2,
29 p_attribute13 IN VARCHAR2,
30 p_attribute14 IN VARCHAR2,
31 p_attribute15 IN VARCHAR2,
32 p_attribute_category IN VARCHAR2,
33 p_party_id IN NUMBER,
34 p_program_login_id IN NUMBER,
35 p_party_type_code IN VARCHAR2,
36 p_supplier_flag IN VARCHAR2,
37 p_customer_flag IN VARCHAR2,
38 p_site_flag IN VARCHAR2,
39 p_process_for_applicability_fl IN VARCHAR2,
40 p_rounding_level_code IN VARCHAR2,
41 p_rounding_rule_code IN VARCHAR2,
42 p_withholding_start_date IN DATE,
43 p_inclusive_tax_flag IN VARCHAR2,
44 p_allow_awt_flag IN VARCHAR2,
45 p_use_le_as_subscriber_flag IN VARCHAR2,
46 p_legal_establishment_flag IN VARCHAR2,
47 p_first_party_le_flag IN VARCHAR2,
48 p_reporting_authority_flag IN VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2
50 ) is
51
52 begin
53 -- calling the overridden method here
54 INSERT_ROW (
55 p_collecting_authority_flag => p_collecting_authority_flag
56 ,p_provider_type_code => p_provider_type_code
57 ,p_create_awt_dists_type_code => p_create_awt_dists_type_code
58 ,p_create_awt_invoices_type_cod => p_create_awt_invoices_type_cod
59 ,p_tax_classification_code => p_tax_classification_code
60 ,p_self_assess_flag => p_self_assess_flag
61 ,p_allow_offset_tax_flag => p_allow_offset_tax_flag
62 ,p_rep_registration_number => p_rep_registration_number
63 ,p_effective_from_use_le => p_effective_from_use_le
64 ,p_record_type_code => p_record_type_code
65 ,p_request_id => p_request_id
66 ,p_attribute1 => p_attribute1
67 ,p_attribute2 => p_attribute2
68 ,p_attribute3 => p_attribute3
69 ,p_attribute4 => p_attribute4
70 ,p_attribute5 => p_attribute5
71 ,p_attribute6 => p_attribute6
72 ,p_attribute7 => p_attribute7
73 ,p_attribute8 => p_attribute8
74 ,p_attribute9 => p_attribute9
75 ,p_attribute10 => p_attribute10
76 ,p_attribute11 => p_attribute11
77 ,p_attribute12 => p_attribute12
78 ,p_attribute13 => p_attribute13
79 ,p_attribute14 => p_attribute14
80 ,p_attribute15 => p_attribute15
81 ,p_attribute_category => p_attribute_category
82 ,p_party_id => p_party_id
83 ,p_program_login_id => p_program_login_id
84 ,p_party_type_code => p_party_type_code
85 ,p_supplier_flag => p_supplier_flag
86 ,p_customer_flag => p_customer_flag
87 ,p_site_flag => p_site_flag
88 ,p_process_for_applicability_fl => p_process_for_applicability_fl
89 ,p_rounding_level_code => p_rounding_level_code
90 ,p_rounding_rule_code => p_rounding_rule_code
91 ,p_withholding_start_date => p_withholding_start_date
92 ,p_inclusive_tax_flag => p_inclusive_tax_flag
93 ,p_allow_awt_flag => p_allow_awt_flag
94 ,p_use_le_as_subscriber_flag => p_use_le_as_subscriber_flag
95 ,p_legal_establishment_flag => p_legal_establishment_flag
96 ,p_first_party_le_flag => p_first_party_le_flag
97 ,p_reporting_authority_flag => p_reporting_authority_flag
98 ,x_return_status => x_return_status
99 ,p_registration_type_code => NULL
100 ,p_country_code => NULL
101 );
102 end INSERT_ROW;
103
104
105 procedure INSERT_ROW (
106 p_collecting_authority_flag IN VARCHAR2,
107 p_provider_type_code IN VARCHAR2,
108 p_create_awt_dists_type_code IN VARCHAR2,
109 p_create_awt_invoices_type_cod IN VARCHAR2,
110 p_tax_classification_code IN VARCHAR2,
111 p_self_assess_flag IN VARCHAR2,
112 p_allow_offset_tax_flag IN VARCHAR2,
113 p_rep_registration_number IN VARCHAR2,
114 p_effective_from_use_le IN DATE,
115 p_record_type_code IN VARCHAR2,
116 p_request_id IN NUMBER,
117 p_attribute1 IN VARCHAR2,
118 p_attribute2 IN VARCHAR2,
119 p_attribute3 IN VARCHAR2,
120 p_attribute4 IN VARCHAR2,
121 p_attribute5 IN VARCHAR2,
122 p_attribute6 IN VARCHAR2,
123 p_attribute7 IN VARCHAR2,
124 p_attribute8 IN VARCHAR2,
125 p_attribute9 IN VARCHAR2,
126 p_attribute10 IN VARCHAR2,
127 p_attribute11 IN VARCHAR2,
128 p_attribute12 IN VARCHAR2,
129 p_attribute13 IN VARCHAR2,
130 p_attribute14 IN VARCHAR2,
131 p_attribute15 IN VARCHAR2,
132 p_attribute_category IN VARCHAR2,
133 p_party_id IN NUMBER,
134 p_program_login_id IN NUMBER,
135 p_party_type_code IN VARCHAR2,
136 p_supplier_flag IN VARCHAR2,
137 p_customer_flag IN VARCHAR2,
138 p_site_flag IN VARCHAR2,
139 p_process_for_applicability_fl IN VARCHAR2,
140 p_rounding_level_code IN VARCHAR2,
141 p_rounding_rule_code IN VARCHAR2,
142 p_withholding_start_date IN DATE,
143 p_inclusive_tax_flag IN VARCHAR2,
144 p_allow_awt_flag IN VARCHAR2,
145 p_use_le_as_subscriber_flag IN VARCHAR2,
146 p_legal_establishment_flag IN VARCHAR2,
147 p_first_party_le_flag IN VARCHAR2,
148 p_reporting_authority_flag IN VARCHAR2,
149 x_return_status OUT NOCOPY VARCHAR2,
150 p_registration_type_code IN VARCHAR2,
151 p_country_code IN VARCHAR2
152 ) is
153 L_PARTY_TAX_PROFILE_ID ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
154 L_EFFECTIVE_FROM_USE_LE ZX_PARTY_TAX_PROFILE.EFFECTIVE_FROM_USE_LE%TYPE := P_EFFECTIVE_FROM_USE_LE;
155 L_CREATED_BY_MODULE HZ_PARTIES.CREATED_BY_MODULE%TYPE;
156
157 CURSOR ptp_cur IS
158 SELECT PARTY_TAX_PROFILE_ID
159 FROM ZX_PARTY_TAX_PROFILE
160 WHERE PARTY_TAX_PROFILE_ID = L_PARTY_TAX_PROFILE_ID;
161
162 begin
163 --Initialise x_return_status variable
164 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
165 select ZX_PARTY_TAX_PROFILE_S.nextval into L_PARTY_TAX_PROFILE_ID from dual;
166
167 -- commenting out for bug 10236399
168 -- BEGIN
169 -- Check created module name
170 -- select nvl(substr(created_by_module,1,3),'ZX') created_by_module
171 -- into L_CREATED_BY_MODULE
172 -- from hz_parties where party_id = P_PARTY_ID;
173 -- IF L_CREATED_BY_MODULE = 'XLE' THEN
174 -- return;
175 -- END IF;
176 -- EXCEPTION
177 -- WHEN OTHERS THEN
178 -- NULL;
179 -- END;
180
181 --Perform validations before inserting data
182 --AllowOffsetTax and SetforSelfAssessment flag would be mutually exclusive
183 IF P_SELF_ASSESS_FLAG = 'Y' AND P_ALLOW_OFFSET_TAX_FLAG = 'Y' THEN
184 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
185 arp_util_tax.debug('Error: "Offset Tax and Set for Self Assessment can not both be "Y" at the same time." for Party Id: ' || P_PARTY_ID || ' and Party Type: ' ||P_PARTY_TYPE_CODE);
186 END IF;
187 --when UseLeAsSubscriberFlag is set to 'Y', then setEffectiveFromUseLe as System date
188 IF P_USE_LE_AS_SUBSCRIBER_FLAG = 'Y' THEN
189 L_EFFECTIVE_FROM_USE_LE := SYSDATE;
190 END IF;
191 --Insert only when there is no error
192 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
193 insert into ZX_PARTY_TAX_PROFILE (
194 COLLECTING_AUTHORITY_FLAG,
195 PROVIDER_TYPE_CODE,
196 CREATE_AWT_DISTS_TYPE_CODE,
197 CREATE_AWT_INVOICES_TYPE_CODE,
198 TAX_CLASSIFICATION_CODE,
199 SELF_ASSESS_FLAG,
200 ALLOW_OFFSET_TAX_FLAG,
201 REP_REGISTRATION_NUMBER,
202 EFFECTIVE_FROM_USE_LE,
203 RECORD_TYPE_CODE,
204 REQUEST_ID,
205 PARTY_TAX_PROFILE_ID,
206 ATTRIBUTE1,
207 ATTRIBUTE2,
208 ATTRIBUTE3,
209 ATTRIBUTE4,
210 ATTRIBUTE5,
211 ATTRIBUTE6,
212 ATTRIBUTE7,
213 ATTRIBUTE8,
214 ATTRIBUTE9,
215 ATTRIBUTE10,
216 ATTRIBUTE11,
217 ATTRIBUTE12,
218 ATTRIBUTE13,
219 ATTRIBUTE14,
220 ATTRIBUTE15,
221 ATTRIBUTE_CATEGORY,
222 PARTY_ID,
223 PROGRAM_LOGIN_ID,
224 PARTY_TYPE_CODE,
225 SUPPLIER_FLAG,
226 CUSTOMER_FLAG,
227 SITE_FLAG,
228 PROCESS_FOR_APPLICABILITY_FLAG,
229 ROUNDING_LEVEL_CODE,
230 ROUNDING_RULE_CODE,
231 WITHHOLDING_START_DATE,
232 INCLUSIVE_TAX_FLAG,
233 ALLOW_AWT_FLAG,
234 USE_LE_AS_SUBSCRIBER_FLAG,
235 LEGAL_ESTABLISHMENT_FLAG,
236 FIRST_PARTY_LE_FLAG,
237 REPORTING_AUTHORITY_FLAG,
238 CREATION_DATE,
239 CREATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_LOGIN,
243 OBJECT_VERSION_NUMBER,
244 REGISTRATION_TYPE_CODE,
245 COUNTRY_CODE
246 ) values (
247 P_COLLECTING_AUTHORITY_FLAG,
248 P_PROVIDER_TYPE_CODE,
249 P_CREATE_AWT_DISTS_TYPE_CODE,
250 P_CREATE_AWT_INVOICES_TYPE_COD,
251 P_TAX_CLASSIFICATION_CODE,
252 P_SELF_ASSESS_FLAG,
253 P_ALLOW_OFFSET_TAX_FLAG,
254 DECODE(P_REP_REGISTRATION_NUMBER, fnd_api.g_miss_char,
255 NULL, P_REP_REGISTRATION_NUMBER),
256 L_EFFECTIVE_FROM_USE_LE,
257 P_RECORD_TYPE_CODE,
258 P_REQUEST_ID,
259 L_PARTY_TAX_PROFILE_ID,
260 P_ATTRIBUTE1,
261 P_ATTRIBUTE2,
262 P_ATTRIBUTE3,
263 P_ATTRIBUTE4,
264 P_ATTRIBUTE5,
265 P_ATTRIBUTE6,
266 P_ATTRIBUTE7,
267 P_ATTRIBUTE8,
268 P_ATTRIBUTE9,
269 P_ATTRIBUTE10,
270 P_ATTRIBUTE11,
271 P_ATTRIBUTE12,
272 P_ATTRIBUTE13,
273 P_ATTRIBUTE14,
274 P_ATTRIBUTE15,
275 P_ATTRIBUTE_CATEGORY,
276 P_PARTY_ID,
277 P_PROGRAM_LOGIN_ID,
278 P_PARTY_TYPE_CODE,
279 P_SUPPLIER_FLAG,
280 P_CUSTOMER_FLAG,
281 P_SITE_FLAG,
282 P_PROCESS_FOR_APPLICABILITY_FL,
283 P_ROUNDING_LEVEL_CODE,
284 P_ROUNDING_RULE_CODE,
285 P_WITHHOLDING_START_DATE,
286 P_INCLUSIVE_TAX_FLAG,
287 P_ALLOW_AWT_FLAG,
288 P_USE_LE_AS_SUBSCRIBER_FLAG,
289 P_LEGAL_ESTABLISHMENT_FLAG,
290 P_FIRST_PARTY_LE_FLAG,
291 P_REPORTING_AUTHORITY_FLAG,
292 sysdate,
293 FND_GLOBAL.User_ID,
294 sysdate,
295 FND_GLOBAL.User_ID,
296 FND_GLOBAL.Login_ID,
297 1,
298 P_REGISTRATION_TYPE_CODE,
299 P_COUNTRY_CODE
300 );
301 OPEN ptp_cur;
302 FETCH ptp_cur INTO L_PARTY_TAX_PROFILE_ID;
303 IF (ptp_cur%notfound) then
304 --Set x_return_status param
305 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
306 arp_util_tax.debug('Error: "The Ptp row has not been inserted for Party Id: ' || P_PARTY_ID || ' and Party Type: ' ||P_PARTY_TYPE_CODE || '."');
307 END IF;
308 CLOSE ptp_cur;
309 END IF;
310 EXCEPTION
311 --Index violation check
312 WHEN DUP_VAL_ON_INDEX THEN
313 --Set x_return_status param
314 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
315 arp_util_tax.debug('Error: "The Ptp row already exists for Party Id: ' || P_PARTY_ID || ' and Party Type: ' ||P_PARTY_TYPE_CODE || '."');
316 end INSERT_ROW;
317
318
319 procedure UPDATE_ROW (
320 p_party_tax_profile_id IN NUMBER,
321 p_collecting_authority_flag IN VARCHAR2,
322 p_provider_type_code IN VARCHAR2,
323 p_create_awt_dists_type_code IN VARCHAR2,
324 p_create_awt_invoices_type_cod IN VARCHAR2,
325 p_tax_classification_code IN VARCHAR2,
326 p_self_assess_flag IN VARCHAR2,
327 p_allow_offset_tax_flag IN VARCHAR2,
328 p_rep_registration_number IN VARCHAR2,
329 p_effective_from_use_le IN DATE,
330 p_record_type_code IN VARCHAR2,
331 p_request_id IN NUMBER,
332 p_attribute1 IN VARCHAR2,
336 p_attribute5 IN VARCHAR2,
333 p_attribute2 IN VARCHAR2,
334 p_attribute3 IN VARCHAR2,
335 p_attribute4 IN VARCHAR2,
337 p_attribute6 IN VARCHAR2,
338 p_attribute7 IN VARCHAR2,
339 p_attribute8 IN VARCHAR2,
340 p_attribute9 IN VARCHAR2,
341 p_attribute10 IN VARCHAR2,
342 p_attribute11 IN VARCHAR2,
343 p_attribute12 IN VARCHAR2,
344 p_attribute13 IN VARCHAR2,
345 p_attribute14 IN VARCHAR2,
346 p_attribute15 IN VARCHAR2,
347 p_attribute_category IN VARCHAR2,
348 p_party_id IN NUMBER,
349 p_program_login_id IN NUMBER,
350 p_party_type_code IN VARCHAR2,
351 p_supplier_flag IN VARCHAR2,
352 p_customer_flag IN VARCHAR2,
353 p_site_flag IN VARCHAR2,
354 p_process_for_applicability_fl IN VARCHAR2,
355 p_rounding_level_code IN VARCHAR2,
356 p_rounding_rule_code IN VARCHAR2,
357 p_withholding_start_date IN DATE,
358 p_inclusive_tax_flag IN VARCHAR2,
359 p_allow_awt_flag IN VARCHAR2,
360 p_use_le_as_subscriber_flag IN VARCHAR2,
361 p_legal_establishment_flag IN VARCHAR2,
362 p_first_party_le_flag IN VARCHAR2,
363 p_reporting_authority_flag IN VARCHAR2,
364 x_return_status OUT NOCOPY VARCHAR2
365 ) is
366 begin
367 -- calling the overridden method here
368 UPDATE_ROW (
369 p_party_tax_profile_id => p_party_tax_profile_id
370 ,p_collecting_authority_flag => p_collecting_authority_flag
371 ,p_provider_type_code => p_provider_type_code
372 ,p_create_awt_dists_type_code => p_create_awt_dists_type_code
373 ,p_create_awt_invoices_type_cod => p_create_awt_invoices_type_cod
374 ,p_tax_classification_code => p_tax_classification_code
375 ,p_self_assess_flag => p_self_assess_flag
376 ,p_allow_offset_tax_flag => p_allow_offset_tax_flag
377 ,p_rep_registration_number => p_rep_registration_number
378 ,p_effective_from_use_le => p_effective_from_use_le
379 ,p_record_type_code => p_record_type_code
380 ,p_request_id => p_request_id
381 ,p_attribute1 => p_attribute1
382 ,p_attribute2 => p_attribute2
383 ,p_attribute3 => p_attribute3
384 ,p_attribute4 => p_attribute4
385 ,p_attribute5 => p_attribute5
386 ,p_attribute6 => p_attribute6
387 ,p_attribute7 => p_attribute7
388 ,p_attribute8 => p_attribute8
389 ,p_attribute9 => p_attribute9
390 ,p_attribute10 => p_attribute10
391 ,p_attribute11 => p_attribute11
392 ,p_attribute12 => p_attribute12
393 ,p_attribute13 => p_attribute13
394 ,p_attribute14 => p_attribute14
395 ,p_attribute15 => p_attribute15
396 ,p_attribute_category => p_attribute_category
397 ,p_party_id => p_party_id
398 ,p_program_login_id => p_program_login_id
399 ,p_party_type_code => p_party_type_code
400 ,p_supplier_flag => p_supplier_flag
401 ,p_customer_flag => p_customer_flag
402 ,p_site_flag => p_site_flag
403 ,p_process_for_applicability_fl => p_process_for_applicability_fl
404 ,p_rounding_level_code => p_rounding_level_code
405 ,p_rounding_rule_code => p_rounding_rule_code
406 ,p_withholding_start_date => p_withholding_start_date
407 ,p_inclusive_tax_flag => p_inclusive_tax_flag
408 ,p_allow_awt_flag => p_allow_awt_flag
409 ,p_use_le_as_subscriber_flag => p_use_le_as_subscriber_flag
410 ,p_legal_establishment_flag => p_legal_establishment_flag
411 ,p_first_party_le_flag => p_first_party_le_flag
412 ,p_reporting_authority_flag => p_reporting_authority_flag
413 ,x_return_status => x_return_status
414 ,p_registration_type_code => NULL
415 ,p_country_code => NULL
416 );
417 end UPDATE_ROW;
418
419 procedure UPDATE_ROW (
420 p_party_tax_profile_id IN NUMBER,
421 p_collecting_authority_flag IN VARCHAR2,
422 p_provider_type_code IN VARCHAR2,
423 p_create_awt_dists_type_code IN VARCHAR2,
424 p_create_awt_invoices_type_cod IN VARCHAR2,
425 p_tax_classification_code IN VARCHAR2,
426 p_self_assess_flag IN VARCHAR2,
427 p_allow_offset_tax_flag IN VARCHAR2,
428 p_rep_registration_number IN VARCHAR2,
429 p_effective_from_use_le IN DATE,
430 p_record_type_code IN VARCHAR2,
431 p_request_id IN NUMBER,
432 p_attribute1 IN VARCHAR2,
433 p_attribute2 IN VARCHAR2,
434 p_attribute3 IN VARCHAR2,
435 p_attribute4 IN VARCHAR2,
436 p_attribute5 IN VARCHAR2,
437 p_attribute6 IN VARCHAR2,
438 p_attribute7 IN VARCHAR2,
439 p_attribute8 IN VARCHAR2,
440 p_attribute9 IN VARCHAR2,
441 p_attribute10 IN VARCHAR2,
442 p_attribute11 IN VARCHAR2,
443 p_attribute12 IN VARCHAR2,
444 p_attribute13 IN VARCHAR2,
445 p_attribute14 IN VARCHAR2,
446 p_attribute15 IN VARCHAR2,
450 p_party_type_code IN VARCHAR2,
447 p_attribute_category IN VARCHAR2,
448 p_party_id IN NUMBER,
449 p_program_login_id IN NUMBER,
451 p_supplier_flag IN VARCHAR2,
452 p_customer_flag IN VARCHAR2,
453 p_site_flag IN VARCHAR2,
454 p_process_for_applicability_fl IN VARCHAR2,
455 p_rounding_level_code IN VARCHAR2,
456 p_rounding_rule_code IN VARCHAR2,
457 p_withholding_start_date IN DATE,
458 p_inclusive_tax_flag IN VARCHAR2,
459 p_allow_awt_flag IN VARCHAR2,
460 p_use_le_as_subscriber_flag IN VARCHAR2,
461 p_legal_establishment_flag IN VARCHAR2,
462 p_first_party_le_flag IN VARCHAR2,
463 p_reporting_authority_flag IN VARCHAR2,
464 x_return_status OUT NOCOPY VARCHAR2,
465 p_registration_type_code IN VARCHAR2,
466 p_country_code IN VARCHAR2
467 ) is
468 L_EFFECTIVE_FROM_USE_LE ZX_PARTY_TAX_PROFILE.EFFECTIVE_FROM_USE_LE%TYPE := P_EFFECTIVE_FROM_USE_LE;
469 begin
470 --Initialise x_return_status variable
471 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
472 --Perform validations before inserting data
473 --AllowOffsetTax and SetforSelfAssessment flag would be mutually exclusive
474 IF P_SELF_ASSESS_FLAG = 'Y' AND P_ALLOW_OFFSET_TAX_FLAG = 'Y' THEN
475 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
476 arp_util_tax.debug('Error: "Offset Tax and Set for Self Assessment can not both be "Y" at the same time." for Party Id: ' || P_PARTY_ID || ' and Party Type: ' ||P_PARTY_TYPE_CODE);
477 END IF;
478 --when UseLeAsSubscriberFlag is set to 'Y', then setEffectiveFromUseLe as System date
479 IF P_USE_LE_AS_SUBSCRIBER_FLAG = 'Y' THEN
480 L_EFFECTIVE_FROM_USE_LE := SYSDATE;
481 ELSE
482 L_EFFECTIVE_FROM_USE_LE := NULL;
483 END IF;
484 --Update only when there is no error
485 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
486 update ZX_PARTY_TAX_PROFILE set
487 COLLECTING_AUTHORITY_FLAG = DECODE( P_COLLECTING_AUTHORITY_FLAG, NULL, COLLECTING_AUTHORITY_FLAG, P_COLLECTING_AUTHORITY_FLAG ),
488 PROVIDER_TYPE_CODE = DECODE( P_PROVIDER_TYPE_CODE, NULL, PROVIDER_TYPE_CODE, P_PROVIDER_TYPE_CODE ),
489 CREATE_AWT_DISTS_TYPE_CODE = DECODE( P_CREATE_AWT_DISTS_TYPE_CODE, NULL, CREATE_AWT_DISTS_TYPE_CODE, P_CREATE_AWT_DISTS_TYPE_CODE ),
490 CREATE_AWT_INVOICES_TYPE_CODE = DECODE( P_CREATE_AWT_INVOICES_TYPE_COD, NULL, CREATE_AWT_INVOICES_TYPE_CODE, P_CREATE_AWT_INVOICES_TYPE_COD ),
491 TAX_CLASSIFICATION_CODE = DECODE( P_TAX_CLASSIFICATION_CODE, NULL, TAX_CLASSIFICATION_CODE, P_TAX_CLASSIFICATION_CODE ),
492 SELF_ASSESS_FLAG = DECODE( P_SELF_ASSESS_FLAG, NULL, SELF_ASSESS_FLAG, P_SELF_ASSESS_FLAG ),
493 ALLOW_OFFSET_TAX_FLAG = DECODE( P_ALLOW_OFFSET_TAX_FLAG, NULL, ALLOW_OFFSET_TAX_FLAG, P_ALLOW_OFFSET_TAX_FLAG ),
494 REP_REGISTRATION_NUMBER = DECODE( P_REP_REGISTRATION_NUMBER, NULL, REP_REGISTRATION_NUMBER,fnd_api.g_miss_char, NULL, P_REP_REGISTRATION_NUMBER ),
495 EFFECTIVE_FROM_USE_LE = DECODE( L_EFFECTIVE_FROM_USE_LE, NULL, EFFECTIVE_FROM_USE_LE, L_EFFECTIVE_FROM_USE_LE ),
496 RECORD_TYPE_CODE = DECODE( P_RECORD_TYPE_CODE, NULL, RECORD_TYPE_CODE, P_RECORD_TYPE_CODE ),
497 REQUEST_ID = DECODE( P_REQUEST_ID, NULL, REQUEST_ID, P_REQUEST_ID ),
498 ATTRIBUTE1 = DECODE( P_ATTRIBUTE1, NULL, ATTRIBUTE1, P_ATTRIBUTE1 ),
499 ATTRIBUTE2 = DECODE( P_ATTRIBUTE2, NULL, ATTRIBUTE2, P_ATTRIBUTE2 ),
500 ATTRIBUTE3 = DECODE( P_ATTRIBUTE3, NULL, ATTRIBUTE3, P_ATTRIBUTE3 ),
501 ATTRIBUTE4 = DECODE( P_ATTRIBUTE4, NULL, ATTRIBUTE4, P_ATTRIBUTE4 ),
502 ATTRIBUTE5 = DECODE( P_ATTRIBUTE5, NULL, ATTRIBUTE5, P_ATTRIBUTE5 ),
503 ATTRIBUTE6 = DECODE( P_ATTRIBUTE6, NULL, ATTRIBUTE6, P_ATTRIBUTE6 ),
504 ATTRIBUTE7 = DECODE( P_ATTRIBUTE7, NULL, ATTRIBUTE7, P_ATTRIBUTE7 ),
505 ATTRIBUTE8 = DECODE( P_ATTRIBUTE8, NULL, ATTRIBUTE8, P_ATTRIBUTE8 ),
506 ATTRIBUTE9 = DECODE( P_ATTRIBUTE9, NULL, ATTRIBUTE9, P_ATTRIBUTE9 ),
507 ATTRIBUTE10 = DECODE( P_ATTRIBUTE10, NULL, ATTRIBUTE10, P_ATTRIBUTE10 ),
508 ATTRIBUTE11 = DECODE( P_ATTRIBUTE11, NULL, ATTRIBUTE11, P_ATTRIBUTE11 ),
509 ATTRIBUTE12 = DECODE( P_ATTRIBUTE12, NULL, ATTRIBUTE12, P_ATTRIBUTE12 ),
510 ATTRIBUTE13 = DECODE( P_ATTRIBUTE13, NULL, ATTRIBUTE13, P_ATTRIBUTE13 ),
511 ATTRIBUTE14 = DECODE( P_ATTRIBUTE14, NULL, ATTRIBUTE14, P_ATTRIBUTE14 ),
512 ATTRIBUTE15 = DECODE( P_ATTRIBUTE15, NULL, ATTRIBUTE15, P_ATTRIBUTE15 ),
513 ATTRIBUTE_CATEGORY = DECODE( P_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY ),
514 PARTY_ID = DECODE( P_PARTY_ID, NULL, PARTY_ID, P_PARTY_ID ),
515 PROGRAM_LOGIN_ID = DECODE( P_PROGRAM_LOGIN_ID, NULL, PROGRAM_LOGIN_ID, P_PROGRAM_LOGIN_ID ),
516 PARTY_TYPE_CODE = DECODE( P_PARTY_TYPE_CODE, NULL, PARTY_TYPE_CODE, P_PARTY_TYPE_CODE),
517 SUPPLIER_FLAG = DECODE( P_SUPPLIER_FLAG, NULL, SUPPLIER_FLAG, P_SUPPLIER_FLAG ),
518 CUSTOMER_FLAG = DECODE( P_CUSTOMER_FLAG, NULL, CUSTOMER_FLAG, P_CUSTOMER_FLAG ),
519 SITE_FLAG = DECODE( P_SITE_FLAG, NULL, SITE_FLAG, P_SITE_FLAG ),
520 PROCESS_FOR_APPLICABILITY_FLAG = DECODE( P_PROCESS_FOR_APPLICABILITY_FL, NULL, PROCESS_FOR_APPLICABILITY_FLAG, P_PROCESS_FOR_APPLICABILITY_FL ),
521 ROUNDING_LEVEL_CODE = DECODE( P_ROUNDING_LEVEL_CODE, NULL, ROUNDING_LEVEL_CODE, P_ROUNDING_LEVEL_CODE ),
522 ROUNDING_RULE_CODE = DECODE( P_ROUNDING_RULE_CODE, NULL, ROUNDING_RULE_CODE, P_ROUNDING_RULE_CODE ),
523 WITHHOLDING_START_DATE = DECODE( P_WITHHOLDING_START_DATE, NULL, WITHHOLDING_START_DATE, P_WITHHOLDING_START_DATE ),
524 INCLUSIVE_TAX_FLAG = DECODE( P_INCLUSIVE_TAX_FLAG, NULL, INCLUSIVE_TAX_FLAG, P_INCLUSIVE_TAX_FLAG ),
525 ALLOW_AWT_FLAG = DECODE( P_ALLOW_AWT_FLAG, NULL, ALLOW_AWT_FLAG, P_ALLOW_AWT_FLAG ),
526 USE_LE_AS_SUBSCRIBER_FLAG = DECODE( P_USE_LE_AS_SUBSCRIBER_FLAG, NULL, USE_LE_AS_SUBSCRIBER_FLAG, P_USE_LE_AS_SUBSCRIBER_FLAG ),
527 LEGAL_ESTABLISHMENT_FLAG = DECODE( P_LEGAL_ESTABLISHMENT_FLAG, NULL, LEGAL_ESTABLISHMENT_FLAG, P_LEGAL_ESTABLISHMENT_FLAG ),
528 FIRST_PARTY_LE_FLAG = DECODE( P_FIRST_PARTY_LE_FLAG, NULL, FIRST_PARTY_LE_FLAG, P_FIRST_PARTY_LE_FLAG ),
532 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_ID,
529 REPORTING_AUTHORITY_FLAG = DECODE( P_REPORTING_AUTHORITY_FLAG, NULL, REPORTING_AUTHORITY_FLAG, P_REPORTING_AUTHORITY_FLAG ),
530 LAST_UPDATE_DATE = sysdate,
531 LAST_UPDATED_BY = FND_GLOBAL.User_ID,
533 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
534 REGISTRATION_TYPE_CODE = DECODE (P_REGISTRATION_TYPE_CODE,NULL, REGISTRATION_TYPE_CODE, P_REGISTRATION_TYPE_CODE),
535 COUNTRY_CODE = DECODE (P_COUNTRY_CODE,NULL, COUNTRY_CODE, P_COUNTRY_CODE)
536 where PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
537 if (sql%notfound) then
538 --Set x_return_status param
539 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
540 arp_util_tax.debug('Error: "The Ptp row has not been updated for Party Id: ' || P_PARTY_ID || ' and Party Type: ' ||P_PARTY_TYPE_CODE || '."');
541 end if;--end sql%notfound
542 end if;--end X_RETURN_STATUS
543 end UPDATE_ROW;
544
545 procedure DELETE_ROW (
546 p_party_tax_profile_id IN NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2
548 ) is
549 begin
550 --Initialise x_return_status variable
551 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
552 --Delete the row
553 delete from ZX_PARTY_TAX_PROFILE
554 where PARTY_TAX_PROFILE_ID = P_PARTY_TAX_PROFILE_ID;
555
556 if (sql%notfound) then
557 --Set x_return_status param
558 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
559 raise no_data_found;
560 end if;
561 end DELETE_ROW;
562
563 PROCEDURE sync_tax_reg_num (
564 p_party_id IN NUMBER
565 ,p_tax_reg_num IN VARCHAR2
566 ,x_return_status OUT NOCOPY VARCHAR2
567 ,x_msg_count OUT NOCOPY NUMBER
568 ,x_msg_data OUT NOCOPY VARCHAR2
569 ) IS
570
571 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
572 l_party_ovn NUMBER(15) := NULL;
573 l_dummy_number NUMBER(15) := NULL;
574 l_dummy_char HZ_PARTIES.party_type%TYPE;
575
576 BEGIN
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578 x_msg_count := 0;
579 x_msg_data := NULL;
580
581 IF p_party_id IS NULL THEN
582 RETURN;
583 ELSE
584 BEGIN
585
586 SELECT party_type
587 INTO l_dummy_char
588 FROM hz_parties
589 WHERE party_id = p_party_id;
590
591 IF l_dummy_char <> 'ORGANIZATION' THEN
592 -- no action reqd for suppliers
593 RETURN;
594 END IF;
595
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 RETURN;
599 END;
600 END IF;
601
602 hz_party_v2pub.get_organization_rec
603 (p_init_msg_list => FND_API.G_TRUE
604 ,p_party_id => p_party_id
605 ,x_organization_rec => l_organization_rec
606 ,x_return_status => x_return_status
607 ,x_msg_count => x_msg_count
608 ,x_msg_data => x_msg_data
609 );
610
611 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
612 RETURN;
613 END IF;
614
615 IF NVL(l_organization_rec.tax_reference,FND_API.G_MISS_CHAR) <>
616 NVL(p_tax_reg_num,FND_API.G_MISS_CHAR) THEN
617 l_organization_rec.tax_reference := NVL(p_tax_reg_num,FND_API.G_MISS_CHAR);
618
619 SELECT object_version_number
620 INTO l_party_ovn
621 FROM hz_parties
622 WHERE party_id = p_party_id;
623
624 hz_party_v2pub.update_organization
625 (p_init_msg_list => FND_API.G_TRUE
626 ,p_organization_rec => l_organization_rec
627 ,p_party_object_version_number => l_party_ovn
628 ,x_profile_id => l_dummy_number
629 ,x_return_status => x_return_status
630 ,x_msg_count => x_msg_count
631 ,x_msg_data => x_msg_data
632 );
633 END IF;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
638 x_msg_count := 1;
639 x_msg_data := SQLERRM;
640
641 END sync_tax_reg_num;
642
643 END ZX_PARTY_TAX_PROFILE_PKG;