[Home] [Help]
PACKAGE BODY: APPS.ZX_R11I_TAX_PARTNER_PKG
Source
1 package body zx_r11i_tax_partner_pkg AS
2 /* $Header: zxir11ipartnerpkgb.pls 120.29.12010000.2 2008/11/12 12:32:32 spasala ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_R11I_TAX_PARTNER_PKG';
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_R11I_TAX_PTNR_PKG.';
13
14 G_LINES_PER_FETCH CONSTANT NUMBER:= 1000;
15 G_MAX_LINES_PER_FETCH CONSTANT NUMBER:= 1000000;
16 /* ======================================================================*
17 | Global Structure Data Types |
18 * ======================================================================*/
19
20 TYPE NUMBER_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21 TYPE VARCHAR2_1_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
22 TYPE VARCHAR2_2_tbl_type IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
23 TYPE VARCHAR2_15_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
24 TYPE VARCHAR2_30_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
25 TYPE VARCHAR2_40_tbl_type IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
26 TYPE VARCHAR2_80_tbl_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
27 TYPE VARCHAR2_50_tbl_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
28 TYPE VARCHAR2_240_tbl_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
29 TYPE VARCHAR2_360_tbl_type IS TABLE OF VARCHAR2(360) INDEX BY BINARY_INTEGER;
30 TYPE DATE_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
31
32 TYPE ZX_PTNR_NEG_LINE_TYPE IS RECORD (
33 INTERNAL_ORGANIZATION_ID NUMBER_tbl_type ,
34 EVENT_CLASS_MAPPING_ID NUMBER_tbl_type,
35 TRX_ID NUMBER_tbl_type,
36 ENTITY_CODE VARCHAR2_30_tbl_type,
37 EVENT_CLASS_CODE VARCHAR2_30_tbl_type,
38 APPLICATION_ID NUMBER_tbl_type,
39 TRX_DATE DATE_tbl_type,
40 TRX_CURRENCY_CODE VARCHAR2_15_tbl_type,
41 TRX_NUMBER VARCHAR2_240_tbl_type,
42 RECORD_TYPE_CODE VARCHAR2_30_tbl_type,
43 TRX_LINE_ID NUMBER_tbl_type,
44 TRX_LEVEL_TYPE VARCHAR2_30_tbl_type,
45 LINE_LEVEL_ACTION VARCHAR2_30_tbl_type,
46 TRX_LINE_DATE DATE_tbl_type,
47 LINE_AMT NUMBER_tbl_type,
48 TRX_LINE_QUANTITY NUMBER_tbl_type,
49 UNIT_PRICE NUMBER_tbl_type,
50 PRODUCT_ID NUMBER_tbl_type,
51 PRODUCT_ORG_ID NUMBER_tbl_type,
52 UOM_CODE VARCHAR2_30_tbl_type,
53 PRODUCT_TYPE VARCHAR2_240_tbl_type,
54 PRODUCT_CODE VARCHAR2_40_tbl_type,
55 FOB_POINT VARCHAR2_30_tbl_type,
56 SHIP_TO_GEOGRAPHY_TYPE1 VARCHAR2_30_tbl_type,
57 SHIP_TO_GEOGRAPHY_VALUE1 VARCHAR2_30_tbl_type,
58 SHIP_TO_GEOGRAPHY_TYPE2 VARCHAR2_30_tbl_type,
59 SHIP_TO_GEOGRAPHY_VALUE2 VARCHAR2_30_tbl_type,
60 SHIP_TO_GEOGRAPHY_TYPE3 VARCHAR2_30_tbl_type,
61 SHIP_TO_GEOGRAPHY_VALUE3 VARCHAR2_30_tbl_type,
62 SHIP_TO_GEOGRAPHY_TYPE4 VARCHAR2_30_tbl_type,
63 SHIP_TO_GEOGRAPHY_VALUE4 VARCHAR2_30_tbl_type,
64 SHIP_TO_GEOGRAPHY_TYPE5 VARCHAR2_30_tbl_type,
65 SHIP_TO_GEOGRAPHY_VALUE5 VARCHAR2_30_tbl_type,
66 SHIP_TO_GEOGRAPHY_TYPE6 VARCHAR2_30_tbl_type ,
67 SHIP_TO_GEOGRAPHY_VALUE6 VARCHAR2_30_tbl_type ,
68 SHIP_TO_GEOGRAPHY_TYPE7 VARCHAR2_30_tbl_type ,
69 SHIP_TO_GEOGRAPHY_VALUE7 VARCHAR2_30_tbl_type ,
70 SHIP_TO_GEOGRAPHY_TYPE8 VARCHAR2_30_tbl_type ,
71 SHIP_TO_GEOGRAPHY_VALUE8 VARCHAR2_30_tbl_type ,
72 SHIP_TO_GEOGRAPHY_TYPE9 VARCHAR2_30_tbl_type ,
73 SHIP_TO_GEOGRAPHY_VALUE9 VARCHAR2_30_tbl_type ,
74 SHIP_TO_GEOGRAPHY_TYPE10 VARCHAR2_30_tbl_type ,
75 SHIP_TO_GEOGRAPHY_VALUE10 VARCHAR2_30_tbl_type ,
76 SHIP_FROM_GEOGRAPHY_TYPE1 VARCHAR2_30_tbl_type ,
77 SHIP_FROM_GEOGRAPHY_VALUE1 VARCHAR2_30_tbl_type ,
78 SHIP_FROM_GEOGRAPHY_TYPE2 VARCHAR2_30_tbl_type ,
79 SHIP_FROM_GEOGRAPHY_VALUE2 VARCHAR2_30_tbl_type ,
80 SHIP_FROM_GEOGRAPHY_TYPE3 VARCHAR2_30_tbl_type ,
81 SHIP_FROM_GEOGRAPHY_VALUE3 VARCHAR2_30_tbl_type ,
82 SHIP_FROM_GEOGRAPHY_TYPE4 VARCHAR2_30_tbl_type ,
83 SHIP_FROM_GEOGRAPHY_VALUE4 VARCHAR2_30_tbl_type ,
84 SHIP_FROM_GEOGRAPHY_TYPE5 VARCHAR2_30_tbl_type ,
85 SHIP_FROM_GEOGRAPHY_VALUE5 VARCHAR2_30_tbl_type ,
86 SHIP_FROM_GEOGRAPHY_TYPE6 VARCHAR2_30_tbl_type ,
87 SHIP_FROM_GEOGRAPHY_VALUE6 VARCHAR2_30_tbl_type ,
88 SHIP_FROM_GEOGRAPHY_TYPE7 VARCHAR2_30_tbl_type ,
89 SHIP_FROM_GEOGRAPHY_VALUE7 VARCHAR2_30_tbl_type ,
90 SHIP_FROM_GEOGRAPHY_TYPE8 VARCHAR2_30_tbl_type ,
91 SHIP_FROM_GEOGRAPHY_VALUE8 VARCHAR2_30_tbl_type ,
92 SHIP_FROM_GEOGRAPHY_TYPE9 VARCHAR2_30_tbl_type ,
93 SHIP_FROM_GEOGRAPHY_VALUE9 VARCHAR2_30_tbl_type ,
94 SHIP_FROM_GEOGRAPHY_TYPE10 VARCHAR2_30_tbl_type ,
95 SHIP_FROM_GEOGRAPHY_VALUE10 VARCHAR2_30_tbl_type ,
96 BILL_TO_GEOGRAPHY_TYPE1 VARCHAR2_30_tbl_type ,
97 BILL_TO_GEOGRAPHY_VALUE1 VARCHAR2_30_tbl_type ,
98 BILL_TO_GEOGRAPHY_TYPE2 VARCHAR2_30_tbl_type ,
99 BILL_TO_GEOGRAPHY_VALUE2 VARCHAR2_30_tbl_type ,
100 BILL_TO_GEOGRAPHY_TYPE3 VARCHAR2_30_tbl_type ,
101 BILL_TO_GEOGRAPHY_VALUE3 VARCHAR2_30_tbl_type ,
102 BILL_TO_GEOGRAPHY_TYPE4 VARCHAR2_30_tbl_type ,
103 BILL_TO_GEOGRAPHY_VALUE4 VARCHAR2_30_tbl_type ,
104 BILL_TO_GEOGRAPHY_TYPE5 VARCHAR2_30_tbl_type ,
105 BILL_TO_GEOGRAPHY_VALUE5 VARCHAR2_30_tbl_type ,
106 BILL_TO_GEOGRAPHY_TYPE6 VARCHAR2_30_tbl_type ,
107 BILL_TO_GEOGRAPHY_VALUE6 VARCHAR2_30_tbl_type ,
108 BILL_TO_GEOGRAPHY_TYPE7 VARCHAR2_30_tbl_type ,
109 BILL_TO_GEOGRAPHY_VALUE7 VARCHAR2_30_tbl_type ,
110 BILL_TO_GEOGRAPHY_TYPE8 VARCHAR2_30_tbl_type ,
111 BILL_TO_GEOGRAPHY_VALUE8 VARCHAR2_30_tbl_type ,
112 BILL_TO_GEOGRAPHY_TYPE9 VARCHAR2_30_tbl_type ,
113 BILL_TO_GEOGRAPHY_VALUE9 VARCHAR2_30_tbl_type ,
114 BILL_TO_GEOGRAPHY_TYPE10 VARCHAR2_30_tbl_type ,
115 BILL_TO_GEOGRAPHY_VALUE10 VARCHAR2_30_tbl_type ,
116 BILL_FROM_GEOGRAPHY_TYPE1 VARCHAR2_30_tbl_type ,
117 BILL_FROM_GEOGRAPHY_VALUE1 VARCHAR2_30_tbl_type ,
118 BILL_FROM_GEOGRAPHY_TYPE2 VARCHAR2_30_tbl_type ,
119 BILL_FROM_GEOGRAPHY_VALUE2 VARCHAR2_30_tbl_type ,
120 BILL_FROM_GEOGRAPHY_TYPE3 VARCHAR2_30_tbl_type ,
121 BILL_FROM_GEOGRAPHY_VALUE3 VARCHAR2_30_tbl_type ,
122 BILL_FROM_GEOGRAPHY_TYPE4 VARCHAR2_30_tbl_type ,
123 BILL_FROM_GEOGRAPHY_VALUE4 VARCHAR2_30_tbl_type ,
124 BILL_FROM_GEOGRAPHY_TYPE5 VARCHAR2_30_tbl_type ,
125 BILL_FROM_GEOGRAPHY_VALUE5 VARCHAR2_30_tbl_type ,
126 BILL_FROM_GEOGRAPHY_TYPE6 VARCHAR2_30_tbl_type ,
127 BILL_FROM_GEOGRAPHY_VALUE6 VARCHAR2_30_tbl_type ,
128 BILL_FROM_GEOGRAPHY_TYPE7 VARCHAR2_30_tbl_type ,
129 BILL_FROM_GEOGRAPHY_VALUE7 VARCHAR2_30_tbl_type ,
130 BILL_FROM_GEOGRAPHY_TYPE8 VARCHAR2_30_tbl_type ,
131 BILL_FROM_GEOGRAPHY_VALUE8 VARCHAR2_30_tbl_type ,
132 BILL_FROM_GEOGRAPHY_TYPE9 VARCHAR2_30_tbl_type ,
133 BILL_FROM_GEOGRAPHY_VALUE9 VARCHAR2_30_tbl_type ,
134 BILL_FROM_GEOGRAPHY_TYPE10 VARCHAR2_30_tbl_type,
135 BILL_FROM_GEOGRAPHY_VALUE10 VARCHAR2_30_tbl_type,
136 SHIP_FROM_LOCATION_ID NUMBER_tbl_type,
137 SHIP_TO_LOCATION_ID NUMBER_tbl_type,
138 BILL_TO_LOCATION_ID NUMBER_tbl_type,
139 BILL_FROM_LOCATION_ID NUMBER_tbl_type,
140 ACCOUNT_CCID NUMBER_tbl_type,
141 APPL_FROM_TRX_ID NUMBER_tbl_type,
142 APPL_FROM_LINE_ID NUMBER_tbl_type,
143 APPL_FROM_TRX_LEVEL_TYPE VARCHAR2_30_tbl_type,
144 APPL_FROM_TRX_NUMBER VARCHAR2_240_tbl_type,
145 ADJUSTED_DOC_TRX_ID NUMBER_tbl_type,
146 ADJUSTED_DOC_LINE_ID NUMBER_tbl_type,
147 ADJUSTED_DOC_TRX_LEVEL_TYPE VARCHAR2_30_tbl_type,
148 EXEMPT_CERTIFICATE_NUMBER VARCHAR2_80_tbl_type,
149 EXEMPT_REASON VARCHAR2_240_tbl_type,
150 EXEMPTION_CONTROL_FLAG VARCHAR2_1_tbl_type,
151 SHIP_FROM_PARTY_TAX_PROF_ID NUMBER_tbl_type,
152 BILL_FROM_PARTY_TAX_PROF_ID NUMBER_tbl_type,
153 SHIP_TO_SITE_TAX_PROF_ID NUMBER_tbl_type,
154 SHIP_TO_PARTY_TAX_PROF_ID NUMBER_tbl_type,
155 BILL_TO_CUST_ACCT_SITE_USE_ID NUMBER_tbl_type,
156 BILL_TO_SITE_TAX_PROF_ID NUMBER_tbl_type,
157 BILL_TO_PARTY_TAX_PROF_ID NUMBER_tbl_type,
158 TRADING_HQ_SITE_TAX_PROF_ID NUMBER_tbl_type,
159 TRADING_HQ_PARTY_TAX_PROF_ID NUMBER_tbl_type,
160 SHIP_THIRD_PTY_ACCT_ID NUMBER_tbl_type, -- Bug 4939819
161 BILL_THIRD_PTY_ACCT_ID NUMBER_tbl_type,
162 ADJUSTED_DOC_APPLICATION_ID NUMBER_tbl_type,
163 ADJUSTED_DOC_ENTITY_CODE VARCHAR2_30_tbl_type,
164 ADJUSTED_DOC_EVENT_CLASS_CODE VARCHAR2_30_tbl_type,
165 SHIP_TO_PARTY_NAME VARCHAR2_360_tbl_type,
166 SHIP_FROM_PARTY_NAME VARCHAR2_360_tbl_type,
167 BILL_FROM_PARTY_NAME VARCHAR2_360_tbl_type,
168 BILL_TO_PARTY_NAME VARCHAR2_360_tbl_type,
169 SHIP_TO_PARTY_NUMBER VARCHAR2_30_tbl_type,
170 SHIP_FROM_PARTY_NUMBER VARCHAR2_30_tbl_type,
171 BILL_FROM_PARTY_NUMBER VARCHAR2_30_tbl_type,
172 BILL_TO_PARTY_NUMBER VARCHAR2_30_tbl_type,
173 TAX_PROVIDER_ID NUMBER_tbl_type,
174 TAX_REGIME_CODE VARCHAR2_30_tbl_type,
175 LINE_EXT_VARCHAR_ATTRIBUTE1 VARCHAR2_240_tbl_type,
176 LINE_EXT_VARCHAR_ATTRIBUTE2 VARCHAR2_240_tbl_type,
177 LINE_EXT_VARCHAR_ATTRIBUTE3 VARCHAR2_240_tbl_type,
178 LINE_EXT_VARCHAR_ATTRIBUTE4 VARCHAR2_240_tbl_type ,
179 LINE_EXT_VARCHAR_ATTRIBUTE5 VARCHAR2_240_tbl_type ,
180 LINE_EXT_VARCHAR_ATTRIBUTE6 VARCHAR2_240_tbl_type ,
181 LINE_EXT_VARCHAR_ATTRIBUTE7 VARCHAR2_240_tbl_type ,
182 LINE_EXT_VARCHAR_ATTRIBUTE8 VARCHAR2_240_tbl_type ,
183 LINE_EXT_VARCHAR_ATTRIBUTE9 VARCHAR2_240_tbl_type ,
184 LINE_EXT_VARCHAR_ATTRIBUTE10 VARCHAR2_240_tbl_type ,
185 LINE_EXT_VARCHAR_ATTRIBUTE11 VARCHAR2_240_tbl_type ,
186 LINE_EXT_VARCHAR_ATTRIBUTE12 VARCHAR2_240_tbl_type ,
187 LINE_EXT_VARCHAR_ATTRIBUTE13 VARCHAR2_240_tbl_type ,
188 LINE_EXT_VARCHAR_ATTRIBUTE14 VARCHAR2_240_tbl_type ,
189 LINE_EXT_VARCHAR_ATTRIBUTE15 VARCHAR2_240_tbl_type ,
190 LINE_EXT_VARCHAR_ATTRIBUTE16 VARCHAR2_240_tbl_type ,
191 LINE_EXT_VARCHAR_ATTRIBUTE17 VARCHAR2_240_tbl_type ,
192 LINE_EXT_VARCHAR_ATTRIBUTE18 VARCHAR2_240_tbl_type ,
193 LINE_EXT_VARCHAR_ATTRIBUTE19 VARCHAR2_240_tbl_type ,
194 LINE_EXT_VARCHAR_ATTRIBUTE20 VARCHAR2_240_tbl_type ,
195 LINE_EXT_VARCHAR_ATTRIBUTE21 VARCHAR2_240_tbl_type ,
196 LINE_EXT_VARCHAR_ATTRIBUTE22 VARCHAR2_240_tbl_type ,
197 LINE_EXT_VARCHAR_ATTRIBUTE23 VARCHAR2_240_tbl_type ,
198 LINE_EXT_VARCHAR_ATTRIBUTE24 VARCHAR2_240_tbl_type ,
199 LINE_EXT_VARCHAR_ATTRIBUTE25 VARCHAR2_240_tbl_type ,
200 LINE_EXT_VARCHAR_ATTRIBUTE26 VARCHAR2_240_tbl_type ,
201 LINE_EXT_VARCHAR_ATTRIBUTE27 VARCHAR2_240_tbl_type ,
202 LINE_EXT_VARCHAR_ATTRIBUTE28 VARCHAR2_240_tbl_type ,
203 LINE_EXT_VARCHAR_ATTRIBUTE29 VARCHAR2_240_tbl_type ,
204 LINE_EXT_VARCHAR_ATTRIBUTE30 VARCHAR2_240_tbl_type ,
205 LINE_EXT_NUMBER_ATTRIBUTE1 NUMBER_tbl_type,
206 LINE_EXT_NUMBER_ATTRIBUTE2 NUMBER_tbl_type,
207 LINE_EXT_NUMBER_ATTRIBUTE3 NUMBER_tbl_type,
208 LINE_EXT_NUMBER_ATTRIBUTE4 NUMBER_tbl_type,
209 LINE_EXT_NUMBER_ATTRIBUTE5 NUMBER_tbl_type,
210 LINE_EXT_NUMBER_ATTRIBUTE6 NUMBER_tbl_type,
211 LINE_EXT_NUMBER_ATTRIBUTE7 NUMBER_tbl_type,
212 LINE_EXT_NUMBER_ATTRIBUTE8 NUMBER_tbl_type,
213 LINE_EXT_NUMBER_ATTRIBUTE9 NUMBER_tbl_type,
214 LINE_EXT_NUMBER_ATTRIBUTE10 NUMBER_tbl_type,
215 LINE_EXT_DATE_ATTRIBUTE1 DATE_tbl_type,
216 LINE_EXT_DATE_ATTRIBUTE2 DATE_tbl_type,
217 LINE_EXT_DATE_ATTRIBUTE3 DATE_tbl_type,
218 LINE_EXT_DATE_ATTRIBUTE4 DATE_tbl_type,
219 LINE_EXT_DATE_ATTRIBUTE5 DATE_tbl_type,
220 SHIP_TO_CUST_ACCT_SITE_USE_ID NUMBER_tbl_type,
221 SHIP_THIRD_PTY_ACCT_SITE_ID NUMBER_tbl_type,
222 BILL_THIRD_PTY_ACCT_SITE_ID NUMBER_tbl_type,
223 RECEIVABLES_TRX_TYPE_ID NUMBER_tbl_type
224 );
225
226 zx_ptnr_neg_lines_tab ZX_PTNR_NEG_LINE_TYPE;/*Global table declaration*/
227 i NUMBER;
228 g_vertex_installed BOOLEAN;
229 g_taxware_installed BOOLEAN;
230
231 Procedure get_service_provider(p_org_id IN NUMBER,
232 p_le_id IN NUMBER,
233 x_provider_id OUT NOCOPY NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2) is
235
236 l_use_Le_As_Subscriber_Flag zx_party_tax_profile.Use_Le_As_Subscriber_Flag%type;
237 l_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type;
238 l_provider_id number;
239 l_return_status varchar2(50);
240 x_msg_count VARCHAR2(50);
241 x_msg_data VARCHAR2(50);
242 x_effective_date DATE;
243
244 Begin
245
246 if p_org_id is NOT NULL then
247 if p_le_id is NULL then
248 begin
249 SELECT Use_Le_As_Subscriber_Flag,
250 party_tax_profile_id
251 INTO l_use_Le_As_Subscriber_Flag,
252 l_party_tax_profile_id
253 FROM zx_party_tax_profile
254 WHERE party_id = p_org_id
255 AND Party_Type_Code = 'OU';
256 exception when others then
257 /* This error should be: not able to find PTP for the input OU */
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 return;
260 end;
261
262 if nvl(l_use_le_as_subscriber_flag,'N') = 'N' then
263 if l_party_tax_profile_id IS NULL THEN
264 /* This error should be: not able to find PTP for the input OU */
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 return;
267 else
268 zx_security.set_security_context(p_first_party_org_id => l_party_tax_profile_id,
269 p_effective_date => SYSDATE,
270 x_return_status => l_return_status);
271 end if;
272 else
273 /* We will return null x_provider_id if product does not pass LE
274 but use_le_as_subscriber_flag is checked 'Y' at PTP */
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276 return;
277 end if;
278 else
279 -- if p_org_id is NOT NULL and p_le_id is NOT NULL then
280 zx_api_pub.set_tax_security_context(
281 p_api_version => 1.0 ,
282 p_init_msg_list =>NULL,
283 p_commit => 'N',
284 p_validation_level => 1,
285 x_msg_count =>x_msg_count,
286 x_msg_data =>x_msg_data,
287 p_internal_org_id => p_org_id,
288 p_legal_entity_id => p_le_id,
289 p_transaction_date => SYSDATE,
290 p_related_doc_date => NULL,
291 p_adjusted_doc_date =>NULL,
292 x_effective_date =>x_effective_date,
293 x_return_status => l_return_status);
294
295 end if;
296
297 /* As per bug 3985196, the regime codes have been hard coded */
298
299 If(l_return_status=FND_API.G_RET_STS_SUCCESS) then
300
301 ZX_TPI_SERVICES_PKG.get_service_provider (p_application_id => 222,
302 p_entity_code => 'TRANSACTIONS',
303 p_event_class_code => 'INVOICE',
304 p_tax_regime_code => 'US-SALES-TAX-VERTEX',
305 x_provider_id => l_provider_id,
306 x_return_status => l_return_status);
307
308 if nvl(x_provider_id, -1) <> 1 then
309 ZX_TPI_SERVICES_PKG.get_service_provider (p_application_id => 222,
310 p_entity_code => 'TRANSACTIONS',
311 p_event_class_code => 'INVOICE',
312 p_tax_regime_code => 'US-SALES-TAX-TAXWARE',
313 x_provider_id => l_provider_id,
314 x_return_status => l_return_status);
315 end if;
316 end if;
317 x_return_status:=l_return_status;
318 x_provider_id :=l_provider_id;
319 end if;
320
321 end ;
322
323 FUNCTION IS_CITY_LIMIT_VALID(p_organization_id IN NUMBER,
324 p_legal_entity_id IN NUMBER,
325 p_city_limit IN VARCHAR2) return boolean is
326 l_provider_id number;
327 l_return_status varchar2(200);
328 BEGIN
329
330 return is_city_limit_valid(p_city_limit);
331
332 END ;
333
334 FUNCTION IS_GEOCODE_VALID(p_organization_id IN NUMBER,
335 p_legal_entity_id IN NUMBER,
336 p_geocode IN VARCHAR2)return BOOLEAN is
337 l_provider_id number;
338 l_return_status varchar2(200);
339
340 BEGIN
341
342 return is_geocode_valid(p_geocode);
343
344 END ;
345
346
347 FUNCTION TAX_VENDOR_EXTENSION(p_organization_id IN NUMBER,
348 p_legal_entity_id IN NUMBER)return BOOLEAN is
349 l_provider_id number;
350 l_return_status varchar2(200);
351 l_api_name CONSTANT VARCHAR2(80) := 'TAX_VENDOR_EXTENSION';
352
353 BEGIN
354
355 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
356 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
357 END IF;
358
359 return TAX_VENDOR_EXTENSION;
360
361 END TAX_VENDOR_EXTENSION;
362
363 /* Bug 5139634: Overloaded api for city limit validation without requiring OU/LE context.
364 */
365 FUNCTION IS_CITY_LIMIT_VALID(p_city_limit IN VARCHAR2) return BOOLEAN IS
366 l_api_name CONSTANT VARCHAR2(80) := 'IS_CITY_LIMIT_VALID';
367
368 BEGIN
369
370 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
372 END IF;
373
374 IF p_city_limit IS NULL THEN
375 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
376 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Null city limit passed.');
377 END IF;
378 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
380 END IF;
381 return TRUE;
382 END IF;
383
384 IF g_vertex_installed THEN
385 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
386 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Validating City Limit for Vertex.');
387 END IF;
388 IF ZX_TAX_VERTEX_PKG.IS_CITY_LIMIT_VALID(p_city_limit) THEN
389 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
390 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
391 END IF;
392 return TRUE;
393 ELSE
394 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
395 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
396 END IF;
397 return FALSE;
398 END IF;
399 END IF;
400
401 IF g_taxware_installed THEN
402 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Validating City Limit for Taxware.');
404 END IF;
405 IF ZX_TAX_TAXWARE_PKG.IS_CITY_LIMIT_VALID(p_city_limit) THEN
406 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
407 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
408 END IF;
409 return TRUE;
410 ELSE
411 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
412 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
413 END IF;
414 return FALSE;
415 END IF;
416 END IF;
417
418 -- Bug 5331410, Now for eBTax also, we are capturing City Limit.
419
420 IF (p_city_limit IN (0,1)) THEN
421 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
422 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
423 END IF;
424 return TRUE;
425 ELSE
426 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
428 END IF;
429 return FALSE;
430 END IF;
431
432 END IS_CITY_LIMIT_VALID;
433
434 /* Bug 5139634: Overloaded api for geocode validation without requiring OU/LE context.
435 */
436 FUNCTION IS_GEOCODE_VALID(p_geocode IN VARCHAR2) return BOOLEAN IS
437 l_api_name CONSTANT VARCHAR2(80) := 'IS_GEOCODE_VALID';
438
439 BEGIN
440
441 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
442 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
443 END IF;
444
445 IF p_geocode IS NULL THEN
446 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
447 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Null geocode passed.');
448 END IF;
449 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
450 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
451 END IF;
452 return TRUE;
453 END IF;
454
455 IF g_vertex_installed THEN
456 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Validating Geocode for Vertex.');
458 END IF;
459 IF ZX_TAX_VERTEX_PKG.IS_GEOCODE_VALID(p_geocode) THEN
460 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
462 END IF;
463 return TRUE;
464 END IF;
465 END IF;
466
467 IF g_taxware_installed THEN
468 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
469 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Validating Geocode for Taxware.');
470 END IF;
471 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
472 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
473 END IF;
474 return ZX_TAX_TAXWARE_PKG.IS_GEOCODE_VALID(p_geocode);
475 END IF;
476
477 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
478 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
479 END IF;
480 return FALSE;
481
482 END IS_GEOCODE_VALID;
483
484 /* Bug 5139634: This function checks if Vertex and/or Taxware has been installed in the customer instance
485 and accordingly return the existence of partner to the calling product.
486 */
487 FUNCTION TAX_VENDOR_EXTENSION return BOOLEAN is
488 l_api_name CONSTANT VARCHAR2(80) := 'TAX_VENDOR_EXTENSION';
489
490 BEGIN
491
492 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
494 END IF;
495
496 /* Check if Vertex Q-Series have been installed in the customer instance */
497
498 IF nvl(g_vertex_installed, ZX_TAX_VERTEX_PKG.INSTALLED) THEN
499 g_vertex_installed := TRUE;
500 ELSE
501 g_vertex_installed := FALSE;
502 END IF;
503
504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
505 IF g_vertex_installed THEN
506 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Vertex is installed');
507 ELSE
508 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Vertex is not installed');
509 END IF;
510 END IF;
511
512 IF nvl(g_taxware_installed, ZX_TAX_TAXWARE_PKG.INSTALLED) THEN
513 g_taxware_installed := TRUE;
514 ELSE
515 g_taxware_installed := FALSE;
516 END IF;
517
518 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
519 --Bug 7299805: Modified the incorrect check for taxware installation
520 --IF g_vertex_installed THEN
521 IF g_taxware_installed THEN
522 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Taxware is installed');
523 ELSE
524 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Taxware is not installed');
525 END IF;
526 END IF;
527
528 IF ( g_vertex_installed OR g_taxware_installed ) THEN
529 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
530 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
531 END IF;
532 return TRUE;
533 ELSE
534 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
535 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
536 END IF;
537 return FALSE;
538 END IF;
539
540 END TAX_VENDOR_EXTENSION;
541
542 PROCEDURE ship_to_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
543 l_geography_id number;
544 l_geography_code varchar2(30);
545 l_return_status varchar2(20);
546 l_api_name CONSTANT VARCHAR2(80) := 'SHIP_TO_GEO_NAMES_DERIVE';
547 BEGIN
548 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
549 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
550 END IF;
551 IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
552 ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
553 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i) := NULL;
554 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE1(i) := NULL;
555 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE2(i) := NULL;
556 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE2(i) := NULL;
557 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE3(i) := NULL;
558 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE3(i) := NULL;
559 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE4(i) := NULL;
560 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE4(i) := NULL; -- Bug 5200373
561 ELSE
562 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i) := 'STATE';
563 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i),
564 p_location_type => 'SHIP_TO',
565 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i) ,
566 x_geography_id => l_geography_id ,
567 x_geography_code => l_geography_code ,
568 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE1(i) ,
569 x_return_status =>l_return_status);
570 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE2(i):='COUNTY';
571 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i),
572 p_location_type => 'SHIP_TO',
573 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE2(i) ,
574 x_geography_id => l_geography_id ,
575 x_geography_code => l_geography_code ,
576 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE2(i) ,
577 x_return_status =>l_return_status);
578 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE3(i):='CITY';
579 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i),
580 p_location_type => 'SHIP_TO',
581 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE3(i) ,
582 x_geography_id => l_geography_id ,
583 x_geography_code => l_geography_code ,
584 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE3(i) ,
585 x_return_status =>l_return_status);
586 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE4(i):='POSTAL_CODE';
587 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id =>ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i),
588 p_location_type => 'SHIP_TO',
589 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE4(i) ,
590 x_geography_id => l_geography_id ,
591 x_geography_code => l_geography_code ,
592 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE4(i) ,
593 x_return_status =>l_return_status);
594 END IF;
595 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
596 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
597 END IF;
598
599 END ship_to_geo_names_derive;
600
601 PROCEDURE ship_from_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
602 l_geography_id number;
603 l_geography_code varchar2(30);
604 l_return_status varchar2(20);
605 l_api_name CONSTANT VARCHAR2(80) := 'SHIP_FROM_GEO_NAMES_DERIVE';
606 BEGIN
607 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
609 END IF;
610
611 IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
612 ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
613 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i) := NULL;
614 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE1(i) := NULL;
615 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE2(i) := NULL;
616 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE2(i) := NULL;
617 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE3(i) := NULL;
618 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE3(i) := NULL;
619 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE4(i) := NULL;
620 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE4(i) := NULL; -- Bug 5200373
621 ELSE
622 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i):='STATE';
623 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_location_id(i),
624 p_location_type => 'SHIP_FROM',
625 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i) ,
626 x_geography_id => l_geography_id ,
627 x_geography_code => l_geography_code ,
628 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE1(i) ,
629 x_return_status =>l_return_status);
630 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE2(i):='COUNTY';
631 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_location_id(i),
632 p_location_type => 'SHIP_FROM',
633 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE2(i) ,
634 x_geography_id => l_geography_id ,
635 x_geography_code => l_geography_code ,
636 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE2(i) ,
637 x_return_status =>l_return_status);
638 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE3(i):='CITY';
639 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_location_id(i),
640 p_location_type => 'SHIP_FROM',
641 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE3(i) ,
642 x_geography_id => l_geography_id ,
643 x_geography_code => l_geography_code ,
644 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE3(i) ,
645 x_return_status =>l_return_status);
646 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE4(i):='POSTAL_CODE';
647 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_location_id(i),
648 p_location_type => 'SHIP_FROM',
649 p_geography_type => ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE4(i) ,
650 x_geography_id => l_geography_id ,
651 x_geography_code => l_geography_code ,
652 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE4(i) ,
653 x_return_status =>l_return_status);
654 END IF;
655 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
657 END IF;
658
659 END ship_from_geo_names_derive;
660
661 PROCEDURE bill_from_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
662 l_geography_id number;
663 l_geography_code varchar2(30);
664 l_return_status varchar2(20);
665 l_api_name CONSTANT VARCHAR2(80) := 'BILL_FROM_GEO_NAMES_DERIVE';
666 BEGIN
667 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
668 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
669 END IF;
670
671 IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
672 ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
673 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i) := NULL;
674 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE1(i) := NULL;
675 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE2(i) := NULL;
676 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE2(i) := NULL;
677 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE3(i) := NULL;
678 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE3(i) := NULL;
679 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE4(i) := NULL;
680 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i) := NULL; -- Bug 5200373
681 ELSE
682 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i):='STATE';
683 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_location_id(i),
684 p_location_type => 'BILL_FROM',
685 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i) ,
686 x_geography_id => l_geography_id ,
687 x_geography_code => l_geography_code ,
688 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE1(i) ,
689 x_return_status =>l_return_status);
690 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE2(i):='COUNTY';
691 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_location_id(i),
692 p_location_type => 'BILL_FROM',
693 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE2(i) ,
694 x_geography_id => l_geography_id ,
695 x_geography_code => l_geography_code ,
696 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE2(i) ,
697 x_return_status =>l_return_status);
698 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE3(i):='CITY';
699 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_location_id(i),
700 p_location_type => 'BILL_FROM',
701 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE3(i) ,
702 x_geography_id => l_geography_id ,
703 x_geography_code => l_geography_code ,
704 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE3(i) ,
705 x_return_status =>l_return_status);
706 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE4(i):='POSTAL_CODE';
707 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_location_id(i),
708 p_location_type => 'BILL_FROM',
709 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE4(i) ,
710 x_geography_id => l_geography_id ,
711 x_geography_code => l_geography_code ,
712 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i) ,
713 x_return_status =>l_return_status);
714 END IF;
715 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
717 END IF;
718
719 END bill_from_geo_names_derive;
720
721 PROCEDURE bill_to_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
722 l_geography_id number;
723 l_geography_code varchar2(30);
724 l_return_status varchar2(20);
725 l_api_name CONSTANT VARCHAR2(80) := 'BILL_TO_GEO_NAMES_DERIVE';
726 BEGIN
727
728 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
729 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
730 END IF;
731
732 IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
733 ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
734 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i) := NULL;
735 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE1(i) := NULL;
736 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE2(i) := NULL;
737 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE2(i) := NULL;
738 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE3(i) := NULL;
739 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE3(i) := NULL;
740 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE4(i) := NULL;
741 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE4(i) := NULL; -- Bug 5200373
742 ELSE
743 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i):='STATE';
744 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_TO_location_id(i),
745 p_location_type => 'BILL_TO',
746 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i) ,
747 x_geography_id => l_geography_id ,
748 x_geography_code => l_geography_code ,
749 x_geography_name => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE1(i) ,
750 x_return_status => l_return_status);
751 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE2(i):='COUNTY';
752 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_TO_location_id(i),
753 p_location_type => 'BILL_TO',
754 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE2(i) ,
755 x_geography_id => l_geography_id ,
756 x_geography_code => l_geography_code ,
757 x_geography_name => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE2(i) ,
758 x_return_status => l_return_status);
759 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE3(i):='CITY';
760 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_TO_location_id(i),
761 p_location_type => 'BILL_TO',
762 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE3(i) ,
763 x_geography_id => l_geography_id ,
764 x_geography_code => l_geography_code ,
765 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE3(i) ,
766 x_return_status =>l_return_status);
767 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE4(i):='POSTAL_CODE';
768 ZX_TCM_GEO_JUR_PKG.get_master_geography(p_location_id => ZX_PTNR_NEG_LINES_TAB.BILL_TO_location_id(i),
769 p_location_type => 'BILL_TO',
770 p_geography_type => ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE4(i) ,
771 x_geography_id => l_geography_id ,
772 x_geography_code => l_geography_code ,
773 x_geography_name =>ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE4(i) ,
774 x_return_status =>l_return_status);
775 END IF;
776 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
777 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
778 END IF;
779
780 END bill_to_geo_names_derive;
781
782
783 /* Bug 4939819
784 The source of party number and name information varies depending upon the family group.
785
786 For O2C products, the information can be derived as follows.
787 Party Type PTP known Other Info Derivation Logic
788 --------------- ----- ----------- ----------------
789 SHIP/BILL TO Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
790
791 SHIP/BILL TO N SHIP_THIRD_PTY_ACCT_ID Via HZ_CUST_ACCOUNTS, HZ_PARTIES
792 In this case, SHIP_THIRD_PTY_ACCT_ID holds value of CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS.
793
794 SHIP/BILL FROM Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
795 As per Desh's update in the bug, If a eBiz customer wants to pass first party inv org id in
796 Rel 12 with new partner integration, he must do legal to business association using LE form.
797 That would create a PTP for the first party inv org.
798 Hence, there will be a record in HZ_PARTIES and is correct to derive info from HZ_PARTIES.
799
800 SHIP/BILL FROM N NULL (PTP is always expected)
801
802 -------------------------------------------------------------------------------------------
803 For P2P products, the information can be derived as follows.
804 Party Type PTP known Other Info Derivation Logic
805 --------------- ----- ----------- ----------------
806 SHIP/BILL TO Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
807 As per Desh's update in the bug, If a eBiz customer wants to pass first party inv org id in
808 Rel 12 with new partner integration, he must do legal to business association using LE form.
809 That would create a PTP for the first party inv org.
810 Hence, there will definitely be record in HZ_PARTIES.
811
812 SHIP/BILL TO N NULL (PTP is always expected)
813
814 SHIP/BILL FROM Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
815
816 SHIP/BILL FROM N SHIP_THIRD_PTY_ACCT_ID Via PO_VENDORS
817 In this case, SHIP_THIRD_PTY_ACCT_ID holds value of VENDOR_ID.
818
819 */
820
821 PROCEDURE bill_to_party_name_derive IS
822 l_api_name CONSTANT VARCHAR2(80) := 'BILL_TO_PARTY_NAME_DERIVE';
823
824 BEGIN
825 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
826 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
827 END IF;
828
829 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
830 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'l_bill_to_ptp_id = '||ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i));
831 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'l_bill_third_pty_acct_id = '||ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i));
832 END IF;
833
834 IF ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
835 BEGIN
836 SELECT pty.party_name,
837 pty.party_number
838 INTO ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
839 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i)
840 FROM hz_parties pty,
841 zx_party_tax_profile ptp
842 WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i)
843 AND ptp.party_id = pty.party_id;
844 EXCEPTION WHEN OTHERS THEN
845 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i) := NULL;
846 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i) := NULL;
847 END;
848 ELSE
849 IF ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i) IS NOT NULL THEN
850 BEGIN
851 SELECT hzp.party_name,
852 hzp.party_number
853 INTO ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
854 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i)
855 FROM hz_cust_accounts hzca,
856 hz_parties hzp
857 WHERE hzp.party_id = hzca.party_id
858 AND hzca.cust_account_id = ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i);
859 END;
860 ELSE
861 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i) := NULL;
862 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i) := NULL;
863 END IF;
864 END IF;
865
866 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
868 END IF;
869
870 END bill_to_party_name_derive;
871
872 PROCEDURE ship_to_party_name_derive IS
873 l_api_name CONSTANT VARCHAR2(80) := 'SHIP_TO_PARTY_NAME_DERIVE';
874
875 BEGIN
876 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
877 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
878 END IF;
879
880 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
881 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'l_ship_to_ptp_id = '||ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i));
882 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'l_ship_third_pty_acct_id = '||ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_ID(i));
883 END IF;
884
885 IF ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i) IS NULL THEN
886 IF ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_ID(i) IS NOT NULL THEN
887 BEGIN
888 SELECT hzp.party_name,
889 hzp.party_number
890 INTO ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
891 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i)
892 FROM hz_cust_accounts hzca,
893 hz_parties hzp
894 WHERE hzp.party_id = hzca.party_id
895 AND hzca.cust_account_id = ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i);
896 EXCEPTION WHEN OTHERS THEN
897 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i) := NULL;
898 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i) := NULL;
899 END;
900 ELSE
901 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i) := NULL;
902 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i) := NULL;
903 END IF;
904 ELSIF ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i) = ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i) THEN
905 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i) := ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i);
906 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i) := ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i);
907 ELSE
908 BEGIN
909 SELECT pty.party_name,
910 pty.party_number
911 INTO ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
912 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i)
913 FROM hz_parties pty,
914 zx_party_tax_profile ptp
915 WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i)
916 AND ptp.party_id = pty.party_id;
917 EXCEPTION WHEN OTHERS THEN
918 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i) := NULL;
919 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i) := NULL;
920 END;
921 END IF;
922 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
923 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
924 END IF;
925
926 END ship_to_party_name_derive;
927
928 PROCEDURE bill_from_party_name_derive IS
929 l_api_name CONSTANT VARCHAR2(80) := 'BILL_FROM_PARTY_NAME_DERIVE';
930
931 BEGIN
932 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
934 END IF;
935 IF ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
936 BEGIN
937 SELECT pty.party_name,
938 pty.party_number
939 INTO ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i),
940 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i)
941 FROM hz_parties pty,
942 zx_party_tax_profile ptp
943 WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i)
944 AND ptp.party_id = pty.party_id;
945 EXCEPTION WHEN OTHERS THEN
946 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i) := NULL;
947 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i) := NULL;
948 END;
949 ELSE
950 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i) := NULL;
951 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i) := NULL;
952 END IF;
953 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
955 END IF;
956
957 END bill_from_party_name_derive;
958
959 PROCEDURE ship_from_party_name_derive IS
960 l_api_name CONSTANT VARCHAR2(80) := 'SHIP_FROM_PARTY_NAME_DERIVE';
961
962 BEGIN
963 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
964 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
965 END IF;
966 IF ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i) IS NULL THEN
967 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i) := NULL;
968 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i) := NULL;
969 ELSIF ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i) = ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i) THEN
970 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i) := ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i);
971 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i) := ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i);
972 ELSE
973 BEGIN
974 SELECT pty.party_name,
975 pty.party_number
976 INTO ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i),
977 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i)
978 FROM hz_parties pty,
979 zx_party_tax_profile ptp
980 WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i)
981 AND ptp.party_id = pty.party_id;
982 EXCEPTION WHEN OTHERS THEN
983 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i) := NULL;
984 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i) := NULL;
985 END;
986 END IF;
987 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
988 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
989 END IF;
990
991 END ship_from_party_name_derive;
992
993 PROCEDURE get_hz_location_id(p_site_use_id IN NUMBER, x_location_id OUT NOCOPY NUMBER) IS
994
995 BEGIN
996 BEGIN
997 SELECT ps.location_id
998 INTO x_location_id
999 FROM hz_cust_site_uses_all siteuse, hz_cust_acct_sites_all site, hz_party_sites ps
1000 WHERE siteuse.site_use_id = p_site_use_id and
1001 siteuse.cust_acct_site_id = site.cust_acct_site_id and
1002 site.party_site_id = ps.party_site_id ;
1003
1004 EXCEPTION WHEN OTHERS THEN
1005 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1006 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'get_hz_location_id', 'Location not found.');
1007 END IF;
1008
1009
1010 END;
1011
1012 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||'get_hz_location_id'||'.END',G_PKG_NAME
1014 || ': ' ||'get_hz_location_id'||'(-)');
1015 END IF;
1016
1017 END get_hz_location_id;
1018
1019 PROCEDURE get_party_tax_profile_id(p_site_use_id IN NUMBER, x_party_tax_profile_id OUT NOCOPY NUMBER) IS
1020
1021 BEGIN
1022 BEGIN
1023 SELECT ptp.party_tax_profile_id
1024 INTO x_party_tax_profile_id
1025 FROM hz_cust_site_uses_all siteuse, hz_cust_acct_sites_all site, hz_cust_accounts acct, zx_party_tax_profile ptp
1026 WHERE siteuse.site_use_id = p_site_use_id and
1027 siteuse.cust_acct_site_id = site.cust_acct_site_id and
1028 site.cust_account_id = acct.cust_account_id and
1029 acct.party_id = ptp.party_id and
1030 ptp.party_type_code = 'THIRD_PARTY' ;
1031
1032 EXCEPTION WHEN OTHERS THEN
1033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'get_party_tax_profile_id', 'Party tax profile id not found.');
1035 END IF;
1036
1037
1038 END;
1039
1040 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1041 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||'get_party_tax_profile_id'||'.END',G_PKG_NAME
1042 || ': ' ||'get_party_tax_profile_id' ||'(-)');
1043 END IF;
1044
1045 END get_party_tax_profile_id;
1046
1047
1048 PROCEDURE FLUSH_TABLE_INFORMATION IS
1049 l_api_name CONSTANT VARCHAR2(80) := 'FLUSH_TABLE_INFORMATION';
1050
1051 BEGIN
1052 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME || ': ' ||l_api_name||'(+)');
1054 END IF;
1055
1056 zx_ptnr_neg_lines_tab.INTERNAL_ORGANIZATION_ID.DELETE;
1057 zx_ptnr_neg_lines_tab.EVENT_CLASS_MAPPING_ID.DELETE;
1058 zx_ptnr_neg_lines_tab.TRX_ID.DELETE;
1059 zx_ptnr_neg_lines_tab.ENTITY_CODE.DELETE;
1060 zx_ptnr_neg_lines_tab.EVENT_CLASS_CODE.DELETE;
1061 zx_ptnr_neg_lines_tab.APPLICATION_ID.DELETE;
1062 zx_ptnr_neg_lines_tab.TRX_DATE.DELETE;
1063 zx_ptnr_neg_lines_tab.TRX_CURRENCY_CODE.DELETE;
1064 zx_ptnr_neg_lines_tab.TRX_NUMBER.DELETE;
1065 zx_ptnr_neg_lines_tab.RECORD_TYPE_CODE.DELETE;
1066 zx_ptnr_neg_lines_tab.TRX_LINE_ID.DELETE;
1067 zx_ptnr_neg_lines_tab.TRX_LEVEL_TYPE.DELETE;
1068 zx_ptnr_neg_lines_tab.LINE_LEVEL_ACTION.DELETE;
1069 zx_ptnr_neg_lines_tab.TRX_LINE_DATE.DELETE;
1070 zx_ptnr_neg_lines_tab.LINE_AMT.DELETE;
1071 zx_ptnr_neg_lines_tab.TRX_LINE_QUANTITY.DELETE;
1072 zx_ptnr_neg_lines_tab.UNIT_PRICE.DELETE;
1073 zx_ptnr_neg_lines_tab.PRODUCT_ID.DELETE;
1074 zx_ptnr_neg_lines_tab.PRODUCT_ORG_ID.DELETE;
1075 zx_ptnr_neg_lines_tab.UOM_CODE.DELETE;
1076 zx_ptnr_neg_lines_tab.PRODUCT_TYPE.DELETE;
1077 zx_ptnr_neg_lines_tab.PRODUCT_CODE.DELETE;
1078 zx_ptnr_neg_lines_tab.FOB_POINT.DELETE;
1079 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE1.DELETE;
1080 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE1.DELETE;
1081 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE2.DELETE;
1082 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE2.DELETE;
1083 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE3.DELETE;
1084 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE3.DELETE;
1085 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE4.DELETE;
1086 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE4.DELETE;
1087 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE5.DELETE;
1088 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE5.DELETE;
1089 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE6.DELETE;
1090 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE6.DELETE;
1091 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE7.DELETE;
1092 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE7.DELETE;
1093 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE8.DELETE;
1094 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE8.DELETE;
1095 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE9.DELETE;
1096 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE9.DELETE;
1097 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE10.DELETE;
1098 zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE10.DELETE;
1099 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE1.DELETE;
1100 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE1.DELETE;
1101 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE2.DELETE;
1102 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE2.DELETE;
1103 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE3.DELETE;
1104 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE3.DELETE;
1105 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE4.DELETE;
1106 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE4.DELETE;
1107 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE5.DELETE;
1108 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE5.DELETE;
1109 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE6.DELETE;
1110 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE6.DELETE;
1111 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE7.DELETE;
1112 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE7.DELETE;
1113 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE8.DELETE;
1114 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE8.DELETE;
1115 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE9.DELETE;
1116 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE9.DELETE;
1117 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE10.DELETE;
1118 zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE10.DELETE;
1119 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE1.DELETE;
1120 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE1.DELETE;
1121 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE2.DELETE;
1122 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE2.DELETE;
1123 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE3.DELETE;
1124 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE3.DELETE;
1125 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE4.DELETE;
1126 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE4.DELETE;
1127 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE5.DELETE;
1128 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE5.DELETE;
1129 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE6.DELETE;
1130 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE6.DELETE;
1131 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE7.DELETE;
1132 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE7.DELETE;
1133 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE8.DELETE;
1134 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE8.DELETE;
1135 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE9.DELETE;
1136 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE9.DELETE;
1137 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE10.DELETE;
1138 zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE10.DELETE;
1139 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE1.DELETE;
1140 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE1.DELETE;
1141 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE2.DELETE;
1142 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE2.DELETE;
1143 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE3.DELETE;
1144 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE3.DELETE;
1145 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE4.DELETE;
1146 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE4.DELETE;
1147 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE5.DELETE;
1148 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE5.DELETE;
1149 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE6.DELETE;
1150 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE6.DELETE;
1151 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE7.DELETE;
1152 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE7.DELETE;
1153 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE8.DELETE;
1154 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE8.DELETE;
1155 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE9.DELETE;
1156 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE9.DELETE;
1157 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE10.DELETE;
1158 zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE10.DELETE;
1159 zx_ptnr_neg_lines_tab.SHIP_FROM_LOCATION_ID.DELETE;
1160 zx_ptnr_neg_lines_tab.SHIP_TO_LOCATION_ID.DELETE;
1161 zx_ptnr_neg_lines_tab.BILL_TO_LOCATION_ID.DELETE;
1162 zx_ptnr_neg_lines_tab.BILL_FROM_LOCATION_ID.DELETE;
1163 zx_ptnr_neg_lines_tab.ACCOUNT_CCID.DELETE;
1164 zx_ptnr_neg_lines_tab.APPL_FROM_TRX_ID.DELETE;
1165 zx_ptnr_neg_lines_tab.APPL_FROM_LINE_ID.DELETE;
1166 zx_ptnr_neg_lines_tab.APPL_FROM_TRX_LEVEL_TYPE.DELETE;
1167 zx_ptnr_neg_lines_tab.APPL_FROM_TRX_NUMBER.DELETE;
1168 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_TRX_ID.DELETE;
1169 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_LINE_ID.DELETE;
1170 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_TRX_LEVEL_TYPE.DELETE;
1171 zx_ptnr_neg_lines_tab.EXEMPT_CERTIFICATE_NUMBER.DELETE;
1172 zx_ptnr_neg_lines_tab.EXEMPT_REASON.DELETE;
1173 zx_ptnr_neg_lines_tab.EXEMPTION_CONTROL_FLAG.DELETE;
1174 zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_TAX_PROF_ID.DELETE;
1175 zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_TAX_PROF_ID.DELETE;
1176 zx_ptnr_neg_lines_tab.SHIP_TO_SITE_TAX_PROF_ID.DELETE;
1177 zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_TAX_PROF_ID.DELETE;
1178 zx_ptnr_neg_lines_tab.BILL_TO_CUST_ACCT_SITE_USE_ID.DELETE;
1179 zx_ptnr_neg_lines_tab.BILL_TO_SITE_TAX_PROF_ID.DELETE;
1180 zx_ptnr_neg_lines_tab.BILL_TO_PARTY_TAX_PROF_ID.DELETE;
1181 zx_ptnr_neg_lines_tab.TRADING_HQ_SITE_TAX_PROF_ID.DELETE;
1182 zx_ptnr_neg_lines_tab.TRADING_HQ_PARTY_TAX_PROF_ID.DELETE;
1183 zx_ptnr_neg_lines_tab.SHIP_THIRD_PTY_ACCT_ID.DELETE; -- Bug 4939819
1184 zx_ptnr_neg_lines_tab.BILL_THIRD_PTY_ACCT_ID.DELETE;
1185 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_APPLICATION_ID.DELETE;
1186 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_ENTITY_CODE.DELETE;
1187 zx_ptnr_neg_lines_tab.ADJUSTED_DOC_EVENT_CLASS_CODE.DELETE;
1188 zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_NAME.DELETE;
1189 zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_NAME.DELETE;
1190 zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_NAME.DELETE;
1191 zx_ptnr_neg_lines_tab.BILL_TO_PARTY_NAME.DELETE;
1192 zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_NUMBER.DELETE;
1193 zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_NUMBER.DELETE;
1194 zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_NUMBER.DELETE;
1195 zx_ptnr_neg_lines_tab.BILL_TO_PARTY_NUMBER.DELETE;
1196 zx_ptnr_neg_lines_tab.TAX_PROVIDER_ID.DELETE;
1197 zx_ptnr_neg_lines_tab.TAX_REGIME_CODE.DELETE;
1198 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE1.DELETE;
1199 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE2.DELETE;
1200 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE3.DELETE;
1201 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE4.DELETE;
1202 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE5.DELETE;
1203 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE6.DELETE;
1204 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE7.DELETE;
1205 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE8.DELETE;
1206 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE9.DELETE;
1207 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE10.DELETE;
1208 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE11.DELETE;
1209 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE12.DELETE;
1210 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE13.DELETE;
1211 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE14.DELETE;
1212 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE15.DELETE;
1213 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE16.DELETE;
1214 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE17.DELETE;
1215 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE18.DELETE;
1216 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE19.DELETE;
1217 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE20.DELETE;
1218 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE21.DELETE;
1219 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE22.DELETE;
1220 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE23.DELETE;
1221 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE24.DELETE;
1222 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE25.DELETE;
1223 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE26.DELETE;
1224 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE27.DELETE;
1225 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE28.DELETE;
1226 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE29.DELETE;
1227 zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE30.DELETE;
1228 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE1.DELETE;
1229 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE2.DELETE;
1230 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE3.DELETE;
1231 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE4.DELETE;
1232 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE5.DELETE;
1233 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE6.DELETE;
1234 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE7.DELETE;
1235 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE8.DELETE;
1236 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE9.DELETE;
1237 zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE10.DELETE;
1238 zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE1.DELETE;
1239 zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE2.DELETE;
1240 zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE3.DELETE;
1241 zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE4.DELETE;
1242 zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE5.DELETE;
1243 zx_ptnr_neg_lines_tab.SHIP_THIRD_PTY_ACCT_SITE_ID.DELETE;
1244 zx_ptnr_neg_lines_tab.BILL_THIRD_PTY_ACCT_SITE_ID.DELETE;
1245 zx_ptnr_neg_lines_tab.SHIP_TO_CUST_ACCT_SITE_USE_ID.DELETE;
1246 zx_ptnr_neg_lines_tab.RECEIVABLES_TRX_TYPE_ID.DELETE;
1247
1248 /* This initialization of zx_ptnr_neg_line_gt is incorrect.
1249 When multiple item lines are updated, "COPY_PTNR_TAX_LINE_BEF_UPD" is called
1250 individually for each item line updated. Hence, subsequent calls delete the
1251 earlier records inserted in zx_ptnr_neg_line_gt.
1252 As far as updating multiple item lines is concerned, records are deleted from
1253 zx_ptnr_neg_line_gt after each line update in zxvtxsrvcpkgb.pls */
1254
1255 -- delete from zx_ptnr_neg_line_gt;
1256
1257 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1258 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
1259 END IF;
1260
1261 END flush_table_information;
1262
1263 PROCEDURE COPY_PTNR_TAX_LINE_BEF_UPD
1264 (p_tax_line_id IN ZX_LINES.tax_line_id%type,
1265 x_return_status OUT NOCOPY VARCHAR2) IS
1266
1267 l_application_id zx_lines.application_id%type;
1268 l_api_name CONSTANT VARCHAR2(80) := 'COPY_PTNR_TAX_LINE_BEF_UPD';
1269
1270 BEGIN
1271
1272 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1273 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1274 END IF;
1275
1276 /*Set the return status to Success */
1277 x_return_status := FND_API.G_RET_STS_SUCCESS;
1278
1279 /* We should not call this API for products other than AR. */
1280
1281 BEGIN
1282 select application_id
1283 into l_application_id
1284 from zx_lines
1285 where tax_line_id = p_tax_line_id;
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 l_application_id := NULL;
1289 END;
1290 IF nvl(l_application_id, -1) <> 222 THEN
1291 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1292 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' Skipping this procedure as no synchronization needed.');
1293 END IF;
1294 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
1296 END IF;
1297 return;
1298 END IF;
1299
1300 BEGIN
1301 INSERT INTO
1302 ZX_PTNR_NEG_TAX_LINE_GT
1303 (
1304 tax_line_id ,
1305 document_type_id ,
1306 application_id ,
1307 entity_code ,
1308 event_class_code ,
1309 trx_id ,
1310 trx_line_id ,
1311 trx_level_type ,
1312 country_code ,
1313 tax ,
1314 situs ,
1315 tax_jurisdiction ,
1316 tax_currency_code ,
1317 tax_curr_tax_amount ,
1318 tax_amount ,
1319 tax_rate_percentage ,
1320 taxable_amount ,
1321 exempt_certificate_number ,
1322 exempt_rate_modifier ,
1323 exempt_reason ,
1324 tax_only_line_flag ,
1325 inclusive_tax_line_flag ,
1326 use_tax_flag ,
1327 ebiz_override_flag ,
1328 user_override_flag ,
1329 last_manual_entry ,
1330 manually_entered_flag ,
1331 cancel_flag ,
1332 delete_flag
1333 )
1334
1335 SELECT
1336 p_tax_line_id,
1337 zxevnt.EVENT_CLASS_MAPPING_ID,
1338 zxlines.application_id,
1339 zxlines.entity_code,
1340 zxlines.event_class_code,
1341 zxlines.trx_id,
1342 zxlines.trx_line_id,
1343 zxlines.trx_level_type,
1344 zxlines.tax_regime_code,
1345 zxlines.tax,
1346 zxlines.place_of_supply_type_code,
1347 zxlines.tax_jurisdiction_code,
1348 zxlines.tax_currency_code,
1349 zxlines.tax_amt_tax_curr,
1350 zxlines. TAX_AMT ,
1351 zxlines.tax_rate,
1352 zxlines.TAXABLE_AMT,
1353 zxlines.exempt_certificate_number,
1354 zxlines.exempt_rate_modifier,
1355 zxlines.exempt_reason,
1356 zxlines.tax_only_line_flag,
1357 zxlines.tax_amt_included_flag,
1358 zxlines.self_assessed_flag,
1359 decode(zxlines.overridden_flag, 'N', 'Y', 'N') ,
1360 zxlines.overridden_flag,
1361 zxlines.last_manual_entry,
1362 zxlines.manually_entered_flag,
1363 zxlines.cancel_flag,
1364 zxlines.delete_flag
1365
1366 FROM zx_lines zxlines, zx_evnt_cls_mappings zxevnt
1367 WHERE zxlines.tax_line_id = p_tax_line_id
1368 AND zxevnt.event_class_code = zxlines.event_class_code
1369 AND zxevnt.application_id = zxlines.application_id
1370 AND zxevnt.entity_code = zxlines.entity_code;
1371
1372
1373 EXCEPTION WHEN OTHERS THEN
1374 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1375 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1376 'Exception Encoutered in zx_r11i_tax_partner_pkg.COPY_PTNR_TAX_LINE_BEF_UPD: '||sqlerrm);
1377 END IF;
1378 END;
1379
1380 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
1382 END IF;
1383
1384 END COPY_PTNR_TAX_LINE_BEF_UPD;
1385
1386 PROCEDURE COPY_TRX_LINE_FOR_PTNR_BEF_UPD
1387 (p_trx_line_dist_tbl IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE,
1388 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1389 p_update_index IN NUMBER,
1390 p_trx_copy_for_tax_update IN VARCHAR2,
1391 p_regime_code IN VARCHAR2,
1392 p_tax_provider_id IN VARCHAR2,
1393 x_return_status OUT NOCOPY VARCHAR2) is
1394 l_geography_id number;
1395 l_geography_code varchar2(30);
1396 l_regiome_code varchar2(20);
1397 l_tax_provider_id number;
1398 l_return_status varchar2(20);
1399 l_regime_code varchar2(30);
1400 l_bill_from_ptp_id number;
1401 ptr number;
1402 l_count number :=0;
1403 -- zx_ptnr_neg_lines_tab ZX_PTNR_NEG_LINE_TYPE;
1404
1405 x_messages_tbl ZX_TAX_PARTNER_PKG.messages_tbl_type;
1406
1407 Cursor lines(p_event_class_rec ZX_API_PUB.event_class_rec_type) is
1408 SELECT
1409 INTERNAL_ORGANIZATION_ID,
1410 EVENT_CLASS_MAPPING_ID,
1411 TRX_ID,
1412 ENTITY_CODE,
1413 EVENT_CLASS_CODE,
1414 APPLICATION_ID,
1415 TRX_DATE,
1416 TRX_CURRENCY_CODE,
1417 TRX_NUMBER,
1418 RECORD_TYPE_CODE,
1419 TRX_LINE_ID,
1420 TRX_LEVEL_TYPE,
1421 LINE_LEVEL_ACTION,
1422 TRX_LINE_DATE,
1423 LINE_AMT,
1424 TRX_LINE_QUANTITY,
1425 UNIT_PRICE,
1426 PRODUCT_ID,
1427 PRODUCT_ORG_ID,
1428 UOM_CODE,
1429 PRODUCT_TYPE,
1430 PRODUCT_CODE,
1431 FOB_POINT,
1432 EXEMPT_CERTIFICATE_NUMBER,
1433 EXEMPT_REASON_CODE, --Bug 6434040
1434 EXEMPTION_CONTROL_FLAG,
1435 SHIP_FROM_PARTY_TAX_PROF_ID,
1436 BILL_FROM_PARTY_TAX_PROF_ID,
1437 SHIP_TO_SITE_TAX_PROF_ID,
1438 SHIP_TO_PARTY_TAX_PROF_ID,
1439 SHIP_TO_LOCATION_ID,
1440 BILL_TO_CUST_ACCT_SITE_USE_ID,
1441 BILL_TO_SITE_TAX_PROF_ID,
1442 BILL_TO_PARTY_TAX_PROF_ID,
1443 BILL_TO_LOCATION_ID,
1444 BILL_FROM_LOCATION_ID,
1445 --POA_LOCATION_ID,
1446 --POO_LOCATION_ID,
1447 SHIP_FROM_LOCATION_ID,
1448 TRADING_HQ_SITE_TAX_PROF_ID,
1449 TRADING_HQ_PARTY_TAX_PROF_ID,
1450 SHIP_THIRD_PTY_ACCT_ID, -- Bug 4939819
1451 BILL_THIRD_PTY_ACCT_ID,
1452 ADJUSTED_DOC_APPLICATION_ID,
1453 ADJUSTED_DOC_ENTITY_CODE,
1454 ADJUSTED_DOC_EVENT_CLASS_CODE,
1455 ADJUSTED_DOC_TRX_ID,
1456 ADJUSTED_DOC_LINE_ID,
1457 ADJUSTED_DOC_TRX_LEVEL_TYPE,
1458 SHIP_TO_CUST_ACCT_SITE_USE_ID,
1459 SHIP_THIRD_PTY_ACCT_SITE_ID,
1460 BILL_THIRD_PTY_ACCT_SITE_ID,
1461 RECEIVABLES_TRX_TYPE_ID
1462 FROM ZX_LINES_DET_FACTORS
1463 WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
1464 ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
1465 EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
1466 TRX_ID = p_event_class_rec.TRX_ID;
1467
1468 l_api_name CONSTANT VARCHAR2(80) := 'COPY_TRX_LINE_FOR_PTNR_BEF_UPD';
1469
1470 Begin
1471
1472 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1473 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1474 END IF;
1475 /*Set the return status to Success */
1476 x_return_status := FND_API.G_RET_STS_SUCCESS;
1477
1478 /* TSRM calls the ZX_SRVC_TYP_PKG.db_update_line_det_factors after the tax calculation
1479 is successfully completed to update some flags on ZX_LINE_DET_FACTORS. In this scenario,
1480 we need not take the snapshot of the ZX_LINE_DET_FACTORS as these updates may not be
1481 relevant for the partners.
1482 If the call is made during the post tax calculation, the ZX_API_PUB.G_PUB_SRVC is set to
1483 'CALCULATE_TAX'. Hence, we will aviod the snapshot capturing during this scenario.
1484 We should not call this API for products other than AR. */
1485
1486 IF p_event_class_rec.application_id = 222 THEN
1487 IF ZX_API_PUB.G_PUB_SRVC = 'CALCULATE_TAX' THEN
1488 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1489 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' Skipping this procedure as no synchronization needed.');
1490 END IF;
1491 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
1493 END IF;
1494 return;
1495 END IF;
1496 ELSE
1497 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1498 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' Skipping this procedure as no synchronization needed.');
1499 END IF;
1500 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1501 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
1502 END IF;
1503 return;
1504 END IF;
1505
1506 IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR p_tax_provider_id is NULL THEN
1507 begin
1508 select tax_regime_code,
1509 tax_provider_id
1510 into l_regime_code,
1511 l_tax_provider_id
1512 from zx_lines
1513 WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1514 AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1515 AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1516 AND TRX_ID = p_event_class_rec.TRX_ID
1517 AND TAX_PROVIDER_ID in (1,2)
1518 AND rownum = 1;
1519 exception when others then
1520 l_tax_provider_id := NULL;
1521 end;
1522 else
1523 l_regime_code := p_regime_code;
1524 l_tax_provider_id := p_tax_provider_id;
1525 end if;
1526
1527 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1529 ' l_regime_code: ' || l_regime_code);
1530 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1531 ' l_tax_provider_id: ' || to_char(l_tax_provider_id));
1532 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1533 ' p_trx_copy_for_tax_update: '|| p_trx_copy_for_tax_update);
1534 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1535 'APPLICATION_ID: '|| p_event_class_rec.APPLICATION_ID);
1536 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1537 'ENTITY_CODE: ' || p_event_class_rec.ENTITY_CODE);
1538 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1539 'EVENT_CLASS_CODE: '|| p_event_class_rec.EVENT_CLASS_CODE);
1540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1541 'TRX_ID: '|| to_char(p_event_class_rec.TRX_ID));
1542 END IF;
1543
1544 IF nvl(l_tax_provider_id, -1) IN (1, 2) THEN
1545
1546 FLUSH_TABLE_INFORMATION; /*To Refresh the old data in table if any*/
1547
1548 IF p_trx_line_dist_tbl.application_id.exists(1) THEN
1549
1550 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1551 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1552 ' p_trx_line_dist_tbl is passed');
1553 END IF;
1554
1555 FOR ptr IN p_trx_line_dist_tbl.application_id.FIRST .. p_update_index loop
1556 I:=ptr;
1557 Select
1558 INTERNAL_ORGANIZATION_ID,
1559 EVENT_CLASS_MAPPING_ID,
1560 TRX_ID,
1561 ENTITY_CODE,
1562 EVENT_CLASS_CODE,
1563 APPLICATION_ID,
1564 TRX_DATE,
1565 TRX_CURRENCY_CODE,
1566 TRX_NUMBER,
1567 RECORD_TYPE_CODE,
1568 TRX_LINE_ID,
1569 TRX_LEVEL_TYPE,
1570 LINE_LEVEL_ACTION,
1571 TRX_LINE_DATE,
1572 LINE_AMT,
1573 TRX_LINE_QUANTITY,
1574 UNIT_PRICE,
1575 PRODUCT_ID,
1576 PRODUCT_ORG_ID,
1577 UOM_CODE,
1578 PRODUCT_TYPE,
1579 PRODUCT_CODE,
1580 FOB_POINT,
1581 EXEMPT_CERTIFICATE_NUMBER,
1582 EXEMPT_REASON_CODE, --Bug 6434040
1583 EXEMPTION_CONTROL_FLAG,
1584 SHIP_FROM_PARTY_TAX_PROF_ID,
1585 BILL_FROM_PARTY_TAX_PROF_ID,
1586 SHIP_TO_SITE_TAX_PROF_ID,
1587 SHIP_TO_PARTY_TAX_PROF_ID,
1588 SHIP_TO_LOCATION_ID,
1589 BILL_TO_CUST_ACCT_SITE_USE_ID,
1590 BILL_TO_SITE_TAX_PROF_ID,
1591 BILL_TO_PARTY_TAX_PROF_ID,
1592 BILL_TO_LOCATION_ID,
1593 BILL_FROM_LOCATION_ID,
1594 --POA_LOCATION_ID,
1595 --POO_LOCATION_ID,
1596 SHIP_FROM_LOCATION_ID,
1597 TRADING_HQ_SITE_TAX_PROF_ID,
1598 TRADING_HQ_PARTY_TAX_PROF_ID,
1599 SHIP_THIRD_PTY_ACCT_ID, -- Bug 4939819
1600 BILL_THIRD_PTY_ACCT_ID,
1601 ADJUSTED_DOC_APPLICATION_ID,
1602 ADJUSTED_DOC_ENTITY_CODE,
1603 ADJUSTED_DOC_EVENT_CLASS_CODE,
1604 ADJUSTED_DOC_TRX_ID,
1605 ADJUSTED_DOC_LINE_ID,
1606 ADJUSTED_DOC_TRX_LEVEL_TYPE,
1607 SHIP_TO_CUST_ACCT_SITE_USE_ID,
1608 SHIP_THIRD_PTY_ACCT_SITE_ID,
1609 BILL_THIRD_PTY_ACCT_SITE_ID,
1610 RECEIVABLES_TRX_TYPE_ID
1611 INTO
1612 ZX_PTNR_NEG_LINES_TAB.INTERNAL_ORGANIZATION_ID(i),
1613 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_MAPPING_ID(i),
1614 ZX_PTNR_NEG_LINES_TAB.TRX_ID(i),
1615 ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE(i),
1616 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE(i),
1617 ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID(i),
1618 ZX_PTNR_NEG_LINES_TAB.TRX_DATE(i),
1619 ZX_PTNR_NEG_LINES_TAB.TRX_CURRENCY_CODE(i),
1620 ZX_PTNR_NEG_LINES_TAB.TRX_NUMBER(i),
1621 ZX_PTNR_NEG_LINES_TAB.RECORD_TYPE_CODE(i),
1622 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID(i),
1623 ZX_PTNR_NEG_LINES_TAB.TRX_LEVEL_TYPE(i),
1624 ZX_PTNR_NEG_LINES_TAB.LINE_LEVEL_ACTION(i),
1625 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_DATE(i),
1626 ZX_PTNR_NEG_LINES_TAB.LINE_AMT(i),
1627 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_QUANTITY(i),
1628 ZX_PTNR_NEG_LINES_TAB.UNIT_PRICE(i),
1629 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ID(i),
1630 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ORG_ID(i),
1631 ZX_PTNR_NEG_LINES_TAB.UOM_CODE(i),
1632 ZX_PTNR_NEG_LINES_TAB.PRODUCT_TYPE(i),
1633 ZX_PTNR_NEG_LINES_TAB.PRODUCT_CODE(i),
1634 ZX_PTNR_NEG_LINES_TAB.FOB_POINT(i),
1635 ZX_PTNR_NEG_LINES_TAB.EXEMPT_CERTIFICATE_NUMBER(i),
1636 ZX_PTNR_NEG_LINES_TAB.EXEMPT_REASON(i),
1637 ZX_PTNR_NEG_LINES_TAB.EXEMPTION_CONTROL_FLAG(i),
1638 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i),
1639 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i),
1640 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_SITE_TAX_PROF_ID(i),
1641 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i),
1642 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_LOCATION_ID(i),
1643 ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i),
1644 ZX_PTNR_NEG_LINES_TAB.BILL_TO_SITE_TAX_PROF_ID(i),
1645 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i),
1646 ZX_PTNR_NEG_LINES_TAB.BILL_TO_LOCATION_ID(i),
1647 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_LOCATION_ID(i),
1648 --ZX_PTNR_NEG_LINES_TAB.POA_LOCATION_ID(i),
1649 --ZX_PTNR_NEG_LINES_TAB.POO_LOCATION_ID(i),
1650 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_LOCATION_ID(i),
1651 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_SITE_TAX_PROF_ID(i),
1652 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_PARTY_TAX_PROF_ID(i),
1653 ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_ID(i), -- Bug 4939819
1654 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i),
1655 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_APPLICATION_ID(i),
1656 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_ENTITY_CODE(i),
1657 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_EVENT_CLASS_CODE(i),
1658 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_ID(i),
1659 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_LINE_ID(i),
1660 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_LEVEL_TYPE(i),
1661 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i),
1662 ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_SITE_ID(i),
1663 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_SITE_ID(i),
1664 ZX_PTNR_NEG_LINES_TAB.RECEIVABLES_TRX_TYPE_ID(i)
1665 FROM ZX_LINES_DET_FACTORS
1666 WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
1667 ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
1668 EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
1669 TRX_ID = p_event_class_rec.TRX_ID AND
1670 TRX_LINE_ID = p_trx_line_dist_tbl.TRX_LINE_ID(i) AND
1671 TRX_LEVEL_TYPE = p_trx_line_dist_tbl.TRX_LEVEL_TYPE(i);
1672
1673 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1674 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1675 'TRX_LINE_ID: '|| to_char(p_trx_line_dist_tbl.TRX_LINE_ID(i)));
1676 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1677 'TRX_LEVEL_TYPE: '|| p_trx_line_dist_tbl.TRX_LEVEL_TYPE(i));
1678 END IF;
1679
1680 ZX_PTNR_NEG_LINES_TAB.TAX_REGIME_CODE(i) := l_regime_code;
1681 ZX_PTNR_NEG_LINES_TAB.TAX_PROVIDER_ID(i) := l_tax_provider_id;
1682
1683 ship_to_geo_names_derive(p_trx_copy_for_tax_update);
1684 ship_from_geo_names_derive(p_trx_copy_for_tax_update);
1685 bill_to_geo_names_derive(p_trx_copy_for_tax_update);
1686 bill_from_geo_names_derive(p_trx_copy_for_tax_update);
1687
1688 --/*Here logic for derivation of party name and number
1689 bill_to_party_name_derive;
1690 ship_to_party_name_derive;
1691 bill_from_party_name_derive;
1692 ship_from_party_name_derive;
1693
1694 End loop;
1695
1696 ELSE
1697 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1698 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1699 ' p_trx_line_dist_tbl is not passed');
1700 END IF;
1701
1702 OPEN lines(p_event_class_rec);
1703 LOOP
1704 FETCH lines BULK COLLECT INTO
1705 ZX_PTNR_NEG_LINES_TAB.INTERNAL_ORGANIZATION_ID,
1706 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_MAPPING_ID,
1707 ZX_PTNR_NEG_LINES_TAB.TRX_ID,
1708 ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE,
1709 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE,
1710 ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID,
1711 ZX_PTNR_NEG_LINES_TAB.TRX_DATE,
1712 ZX_PTNR_NEG_LINES_TAB.TRX_CURRENCY_CODE,
1713 ZX_PTNR_NEG_LINES_TAB.TRX_NUMBER,
1714 ZX_PTNR_NEG_LINES_TAB.RECORD_TYPE_CODE,
1715 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID,
1716 ZX_PTNR_NEG_LINES_TAB.TRX_LEVEL_TYPE,
1717 ZX_PTNR_NEG_LINES_TAB.LINE_LEVEL_ACTION,
1718 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_DATE,
1719 ZX_PTNR_NEG_LINES_TAB.LINE_AMT,
1720 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_QUANTITY,
1721 ZX_PTNR_NEG_LINES_TAB.UNIT_PRICE,
1722 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ID,
1723 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ORG_ID,
1724 ZX_PTNR_NEG_LINES_TAB.UOM_CODE,
1725 ZX_PTNR_NEG_LINES_TAB.PRODUCT_TYPE,
1726 ZX_PTNR_NEG_LINES_TAB.PRODUCT_CODE,
1727 ZX_PTNR_NEG_LINES_TAB.FOB_POINT,
1728 ZX_PTNR_NEG_LINES_TAB.EXEMPT_CERTIFICATE_NUMBER,
1729 ZX_PTNR_NEG_LINES_TAB.EXEMPT_REASON,
1730 ZX_PTNR_NEG_LINES_TAB.EXEMPTION_CONTROL_FLAG,
1731 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID,
1732 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID,
1733 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_SITE_TAX_PROF_ID,
1734 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID,
1735 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_LOCATION_ID,
1736 ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID,
1737 ZX_PTNR_NEG_LINES_TAB.BILL_TO_SITE_TAX_PROF_ID,
1738 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID,
1739 ZX_PTNR_NEG_LINES_TAB.BILL_TO_LOCATION_ID,
1740 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_LOCATION_ID,
1741 --ZX_PTNR_NEG_LINES_TAB.POA_LOCATION_ID,
1742 --ZX_PTNR_NEG_LINES_TAB.POO_LOCATION_ID,
1743 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_LOCATION_ID,
1744 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_SITE_TAX_PROF_ID,
1745 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_PARTY_TAX_PROF_ID,
1746 ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_ID, -- Bug 4939819
1747 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID,
1748 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_APPLICATION_ID,
1749 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_ENTITY_CODE,
1750 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_EVENT_CLASS_CODE,
1751 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_ID,
1752 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_LINE_ID,
1753 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_LEVEL_TYPE,
1754 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID,
1755 ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_SITE_ID,
1756 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_SITE_ID,
1757 ZX_PTNR_NEG_LINES_TAB.RECEIVABLES_TRX_TYPE_ID
1758 LIMIT G_LINES_PER_FETCH;
1759 EXIT WHEN lines%NOTFOUND;
1760 END LOOP;
1761 CLOSE lines;
1762
1763 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1764 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1765 ' ZX_API_PUB.G_PUB_SRVC '|| ZX_API_PUB.G_PUB_SRVC);
1766 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1767 ' No of records in ZX_PTNR_NEG_LINES_TAB = '|| ZX_PTNR_NEG_LINES_TAB.application_id.LAST);
1768 END IF;
1769
1770
1771 FOR ptr IN ZX_PTNR_NEG_LINES_TAB.application_id.FIRST .. ZX_PTNR_NEG_LINES_TAB.application_id.LAST loop
1772
1773 I:=ptr;
1774
1775 IF ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i) is null THEN
1776 get_hz_location_id(ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i), ZX_PTNR_NEG_LINES_TAB.ship_to_location_id(i));
1777
1778 END IF;
1779
1780 IF ZX_PTNR_NEG_LINES_TAB.bill_to_location_id(i) is null THEN
1781 get_hz_location_id(ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i), ZX_PTNR_NEG_LINES_TAB.bill_to_location_id(i));
1782
1783 END IF;
1784
1785 IF ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i) is null THEN
1786 get_party_tax_profile_id(ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i),ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i));
1787 END IF;
1788
1789 IF ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i) is null THEN
1790 get_party_tax_profile_id(ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i),ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i));
1791 END IF;
1792
1793 ZX_PTNR_NEG_LINES_TAB.TAX_REGIME_CODE(i) := l_regime_code;
1794 ZX_PTNR_NEG_LINES_TAB.TAX_PROVIDER_ID(i) := l_tax_provider_id;
1795
1796 ship_to_geo_names_derive(p_trx_copy_for_tax_update);
1797 ship_from_geo_names_derive(p_trx_copy_for_tax_update);
1798 bill_to_geo_names_derive(p_trx_copy_for_tax_update);
1799 bill_from_geo_names_derive(p_trx_copy_for_tax_update);
1800
1801 --/*Here logic for derivation of party name and number
1802 bill_to_party_name_derive;
1803 ship_to_party_name_derive;
1804 bill_from_party_name_derive;
1805 ship_from_party_name_derive;
1806
1807 END LOOP;
1808
1809 END IF;
1810
1811 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1812 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1813 ' Inserting into zx_ptnr_neg_line_gt');
1814 END IF;
1815
1816 FORALL i IN zx_ptnr_neg_lines_tab.application_id.FIRST .. zx_ptnr_neg_lines_tab.application_id.LAST
1817
1818 Insert into zx_ptnr_neg_line_gt(
1819 INTERNAL_ORGANIZATION_ID,
1820 EVENT_CLASS_MAPPING_ID,
1821 TRX_ID,
1822 ENTITY_CODE,
1823 EVENT_CLASS_CODE,
1824 APPLICATION_ID,
1825 TRX_DATE,
1826 TRX_CURRENCY_CODE,
1827 TRX_NUMBER,
1828 RECORD_TYPE_CODE,
1829 TRX_LINE_ID,
1830 TRX_LEVEL_TYPE,
1831 LINE_LEVEL_ACTION,
1832 TRX_LINE_DATE,
1833 LINE_AMT,
1834 TRX_LINE_QUANTITY,
1835 UNIT_PRICE,
1836 PRODUCT_ID,
1837 PRODUCT_ORG_ID,
1838 UOM_CODE,
1839 PRODUCT_TYPE,
1840 PRODUCT_CODE,
1841 FOB_POINT,
1842 TAX_REGIME_CODE,
1843 TAX_PROVIDER_ID,
1844 EXEMPT_CERTIFICATE_NUMBER,
1845 EXEMPT_REASON,
1846 EXEMPTION_CONTROL_FLAG,
1847 SHIP_TO_PARTY_TAX_PROF_ID,
1848 SHIP_FROM_PARTY_TAX_PROF_ID,
1849 SHIP_TO_PARTY_NUMBER,
1850 SHIP_TO_PARTY_NAME,
1851 SHIP_FROM_PARTY_NUMBER,
1852 SHIP_FROM_PARTY_NAME,
1853 BILL_TO_PARTY_NUMBER,
1854 BILL_TO_PARTY_NAME,
1855 BILL_FROM_PARTY_NUMBER,
1856 BILL_FROM_PARTY_NAME,
1857 SHIP_TO_SITE_TAX_PROF_ID,
1858 SHIP_TO_LOCATION_ID,
1859 BILL_TO_CUST_ACCT_SITE_USE_ID,
1860 BILL_TO_SITE_TAX_PROF_ID,
1861 BILL_TO_PARTY_TAX_PROF_ID,
1862 BILL_TO_LOCATION_ID,
1863 TRADING_HQ_SITE_TAX_PROF_ID,
1864 TRADING_HQ_PARTY_TAX_PROF_ID,
1865 BILL_THIRD_PTY_ACCT_ID,
1866 ADJUSTED_DOC_EVENT_CLASS_CODE,
1867 ADJUSTED_DOC_APPLICATION_ID,
1868 ADJUSTED_DOC_ENTITY_CODE,
1869 ADJUSTED_DOC_TRX_ID,
1870 ADJUSTED_DOC_LINE_ID,
1871 ADJUSTED_DOC_TRX_LEVEL_TYPE,
1872 SHIP_TO_CUST_ACCT_SITE_USE_ID,
1873 SHIP_THIRD_PTY_ACCT_SITE_ID,
1874 BILL_THIRD_PTY_ACCT_SITE_ID,
1875 SHIP_TO_GEOGRAPHY_TYPE1,
1876 SHIP_TO_GEOGRAPHY_VALUE1,
1877 SHIP_TO_GEOGRAPHY_TYPE2,
1878 SHIP_TO_GEOGRAPHY_VALUE2,
1879 SHIP_TO_GEOGRAPHY_TYPE3,
1880 SHIP_TO_GEOGRAPHY_VALUE3,
1881 SHIP_TO_GEOGRAPHY_TYPE4,
1882 SHIP_TO_GEOGRAPHY_VALUE4,
1883 SHIP_FROM_GEOGRAPHY_TYPE1,
1884 SHIP_FROM_GEOGRAPHY_VALUE1,
1885 SHIP_FROM_GEOGRAPHY_TYPE2,
1886 SHIP_FROM_GEOGRAPHY_VALUE2,
1887 SHIP_FROM_GEOGRAPHY_TYPE3,
1888 SHIP_FROM_GEOGRAPHY_VALUE3,
1889 SHIP_FROM_GEOGRAPHY_TYPE4,
1890 SHIP_FROM_GEOGRAPHY_VALUE4,
1891 BILL_TO_GEOGRAPHY_TYPE1,
1892 BILL_TO_GEOGRAPHY_VALUE1,
1893 BILL_TO_GEOGRAPHY_TYPE2,
1894 BILL_TO_GEOGRAPHY_VALUE2,
1895 BILL_TO_GEOGRAPHY_TYPE3,
1896 BILL_TO_GEOGRAPHY_VALUE3,
1897 BILL_TO_GEOGRAPHY_TYPE4,
1898 BILL_TO_GEOGRAPHY_VALUE4,
1899 BILL_FROM_GEOGRAPHY_TYPE1,
1900 BILL_FROM_GEOGRAPHY_VALUE1,
1901 BILL_FROM_GEOGRAPHY_TYPE2,
1902 BILL_FROM_GEOGRAPHY_VALUE2,
1903 BILL_FROM_GEOGRAPHY_TYPE3,
1904 BILL_FROM_GEOGRAPHY_VALUE3,
1905 BILL_FROM_GEOGRAPHY_TYPE4,
1906 BILL_FROM_GEOGRAPHY_VALUE4,
1907 RECEIVABLES_TRX_TYPE_ID
1908 )
1909 values(
1910 ZX_PTNR_NEG_LINES_TAB.INTERNAL_ORGANIZATION_ID(i),
1911 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_MAPPING_ID(i),
1912 ZX_PTNR_NEG_LINES_TAB.TRX_ID(i),
1913 ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE(i),
1914 ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE(i),
1915 ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID(i),
1916 ZX_PTNR_NEG_LINES_TAB.TRX_DATE(i),
1917 ZX_PTNR_NEG_LINES_TAB.TRX_CURRENCY_CODE(i),
1918 ZX_PTNR_NEG_LINES_TAB.TRX_NUMBER(i),
1919 ZX_PTNR_NEG_LINES_TAB.RECORD_TYPE_CODE(i),
1920 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID(i),
1921 ZX_PTNR_NEG_LINES_TAB.TRX_LEVEL_TYPE(i),
1922 ZX_PTNR_NEG_LINES_TAB.LINE_LEVEL_ACTION(i),
1923 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_DATE(i),
1924 ZX_PTNR_NEG_LINES_TAB.LINE_AMT(i),
1925 ZX_PTNR_NEG_LINES_TAB.TRX_LINE_QUANTITY(i),
1926 ZX_PTNR_NEG_LINES_TAB.UNIT_PRICE(i),
1927 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ID(i),
1928 ZX_PTNR_NEG_LINES_TAB.PRODUCT_ORG_ID(i),
1929 ZX_PTNR_NEG_LINES_TAB.UOM_CODE(i),
1930 ZX_PTNR_NEG_LINES_TAB.PRODUCT_TYPE(i),
1931 ZX_PTNR_NEG_LINES_TAB.PRODUCT_CODE(i),
1932 ZX_PTNR_NEG_LINES_TAB.FOB_POINT(i),
1933 ZX_PTNR_NEG_LINES_TAB.TAX_REGIME_CODE(i),
1934 ZX_PTNR_NEG_LINES_TAB.TAX_PROVIDER_ID(i),
1935 ZX_PTNR_NEG_LINES_TAB.EXEMPT_CERTIFICATE_NUMBER(i),
1936 ZX_PTNR_NEG_LINES_TAB.EXEMPT_REASON(i),
1937 ZX_PTNR_NEG_LINES_TAB.EXEMPTION_CONTROL_FLAG(i),
1938 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i),
1939 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i),
1940 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i),
1941 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
1942 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i),
1943 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i),
1944 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i),
1945 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
1946 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i),
1947 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i),
1948 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_SITE_TAX_PROF_ID(i),
1949 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_LOCATION_ID(i),
1950 ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i),
1951 ZX_PTNR_NEG_LINES_TAB.BILL_TO_SITE_TAX_PROF_ID(i),
1952 ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i),
1953 ZX_PTNR_NEG_LINES_TAB.BILL_TO_LOCATION_ID(i),
1954 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_SITE_TAX_PROF_ID(i),
1955 ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_PARTY_TAX_PROF_ID(i),
1956 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i),
1957 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_EVENT_CLASS_CODE(i),
1958 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_APPLICATION_ID(i),
1959 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_ENTITY_CODE(i),
1960 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_ID(i),
1961 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_LINE_ID(i),
1962 ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_LEVEL_TYPE(i),
1963 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i),
1964 ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_SITE_ID(i),
1965 ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_SITE_ID(i),
1966 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i),
1967 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE1(i),
1968 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE2(i),
1969 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE2(i),
1970 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE3(i),
1971 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE3(i),
1972 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE4(i),
1973 ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE4(i),
1974 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i),
1975 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE1(i),
1976 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE2(i),
1977 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE2(i),
1978 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE3(i),
1979 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE3(i),
1980 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE4(i),
1981 ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE4(i),
1982 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i),
1983 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE1(i),
1984 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE2(i),
1985 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE2(i),
1986 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE3(i),
1987 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE3(i),
1988 ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE4(i),
1989 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i),
1990 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i),
1991 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE1(i),
1992 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE2(i),
1993 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE2(i),
1994 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE3(i),
1995 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE3(i),
1996 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE4(i),
1997 ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i),
1998 ZX_PTNR_NEG_LINES_TAB.RECEIVABLES_TRX_TYPE_ID(i)
1999 );
2000 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2001 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2002 ' Fetching Extensibile Attributes during negation');
2003 END IF;
2004
2005 /*Bug 4950953 , 4950901 making default value as NULL as it causing issue in update operation subsequently*/
2006
2007 FOR i IN zx_ptnr_neg_lines_tab.application_id.FIRST .. zx_ptnr_neg_lines_tab.application_id.LAST
2008 LOOP
2009
2010 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) := NULL;
2011 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE2(i) := NULL;
2012 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE3(i) := NULL;
2013 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE4(i) := NULL;
2014 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE5(i) := NULL;
2015 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE6(i) := NULL;
2016 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE7(i) := NULL;
2017 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE8(i) := NULL;
2018 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE9(i) := NULL;
2019 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE10(i) := NULL;
2020 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE11(i) := NULL;
2021 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE12(i) := NULL;
2022 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE13(i) := NULL;
2023 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE14(i) := NULL;
2024 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE15(i) := NULL;
2025 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE16(i) := NULL;
2026 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE17(i) := NULL;
2027 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE18(i) := NULL;
2028 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE19(i) := NULL;
2029 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE20(i) := NULL;
2030 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE21(i) := NULL;
2031 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE22(i) := NULL;
2032 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE23(i) := NULL;
2033 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE24(i) := NULL;
2034 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE1(i) := NULL;
2035 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE2(i) := NULL;
2036 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE3(i) := NULL;
2037 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE4(i) := NULL;
2038 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE5(i) := NULL;
2039 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE6(i) := NULL;
2040 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_DATE_ATTRIBUTE1(i) := NULL;
2041
2042 end loop;
2043
2044 --Vchallur
2045 /*Bug 4950953 , 4950901 During Invoice update,complete scenario's need to populate the
2046 ZX_TRX_PRE_PROC_OPTIONS_GT as it used in user procedures.*/
2047
2048 Begin
2049 select count(*)
2050 into l_count
2051 from ZX_TRX_PRE_PROC_OPTIONS_GT
2052 where APPLICATION_ID = p_event_class_rec.application_id
2053 AND ENTITY_CODE = p_event_class_rec.entity_code
2054 AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
2055 AND TRX_ID = p_event_class_rec.trx_id;
2056
2057 Exception
2058 when no_data_found then
2059 l_count:=0;
2060 end;
2061
2062 If(l_count=0) then
2063
2064 INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
2065 APPLICATION_ID,
2066 ENTITY_CODE,
2067 EVENT_CLASS_CODE,
2068 EVNT_CLS_MAPPING_ID,
2069 TAX_EVENT_TYPE_CODE,
2070 PROD_FAMILY_GRP_CODE,
2071 TRX_ID,
2072 TAX_REGIME_CODE,
2073 PARTNER_PROCESSING_FLAG,
2074 TAX_PROVIDER_ID,
2075 EVENT_ID,
2076 QUOTE_FLAG,
2077 RECORD_FLAG,
2078 RECORD_FOR_PARTNERS_FLAG,
2079 APPLICATION_SHORT_NAME,
2080 LEGAL_ENTITY_NUMBER,
2081 ESTABLISHMENT_NUMBER, -- Bug 5139731
2082 ALLOW_TAX_CALCULATION_FLAG,
2083 CREATION_DATE,
2084 CREATED_BY,
2085 LAST_UPDATE_DATE,
2086 LAST_UPDATED_BY,
2087 LAST_UPDATE_LOGIN
2088 )
2089 VALUES (p_event_class_rec.internal_organization_id,
2090 p_event_class_rec.application_id,
2091 p_event_class_rec.entity_code,
2092 p_event_class_rec.event_class_code,
2093 p_event_class_rec.event_class_mapping_id,
2094 p_event_class_rec.tax_event_type_code,
2095 p_event_class_rec.prod_family_grp_code,
2096 p_event_class_rec.trx_id,
2097 l_regime_code,
2098 NULL, --p_ptnr_processing_flag,
2099 l_tax_provider_id,
2100 p_event_class_rec.event_id,
2101 p_event_class_rec.quote_flag,
2102 p_event_class_rec.record_flag,
2103 p_event_class_rec.record_for_partners_flag,
2104 NULL,--l_application_short_name,
2105 NULL,--l_legal_entity_number,
2106 NULL,--l_establishment_number,
2107 p_event_class_rec.process_for_applicability_flag,
2108 sysdate,
2109 fnd_global.user_id,
2110 sysdate,
2111 fnd_global.user_id,
2112 fnd_global.conc_login_id);
2113 end if;
2114 FOR i IN zx_ptnr_neg_lines_tab.application_id.FIRST .. zx_ptnr_neg_lines_tab.application_id.LAST
2115 LOOP
2116 If l_tax_provider_id = 1 then/*Case for VERTEX*/
2117 ZX_VTX_USER_PKG.g_line_negation := TRUE;
2118 ZX_VTX_USER_PKG.g_trx_line_id := zx_ptnr_neg_lines_tab.TRX_LINE_ID(i);
2119 ZX_VTX_USER_PKG.Derive_Hdr_Ext_Attr(x_return_status
2120 , x_messages_tbl);
2121 ZX_VTX_USER_PKG.Derive_Line_Ext_Attr(x_return_status
2122 , x_messages_tbl);
2123 /*Assigning values fetched from user procedures*/
2124 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) :=ZX_VTX_USER_PKG.arp_trx_line_type_tab(1);
2125 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE2(i) :=ZX_VTX_USER_PKG.arp_product_code_tab(1);
2126 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE3(i) :=ZX_VTX_USER_PKG.cert_num_tab(1);
2127 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE4(i) :=ZX_VTX_USER_PKG.arp_state_exempt_reason_tab(1);
2128 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE5(i) :=ZX_VTX_USER_PKG.arp_county_exempt_reason_tab(1);
2129 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE6(i) :=ZX_VTX_USER_PKG.arp_city_exempt_reason_tab(1);
2130 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE7(i) :=ZX_VTX_USER_PKG.arp_district_exempt_rs_tab(1);
2131 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE8(i) :=ZX_VTX_USER_PKG.arp_audit_flag_tab(1);
2132 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE9(i) :=ZX_VTX_USER_PKG.arp_ship_to_add_tab(1);
2133 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE10(i) :=ZX_VTX_USER_PKG.arp_ship_from_add_tab(1);
2134 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE11(i) :=ZX_VTX_USER_PKG.arp_poa_add_code_tab(1);
2135 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE12(i) :=ZX_VTX_USER_PKG.arp_customer_code_tab(1);
2136 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE13(i) :=ZX_VTX_USER_PKG.arp_customer_class_tab(1);
2137 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE14(i) :=ZX_VTX_USER_PKG.arp_company_code_tab(1);
2138 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE15(i) :=ZX_VTX_USER_PKG.arp_division_code_tab(1);
2139 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE1(i) :=ZX_VTX_USER_PKG.arp_state_exempt_percent_tab(1);
2140 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE2(i) :=ZX_VTX_USER_PKG.arp_county_exempt_pct_tab(1);
2141 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE3(i) :=ZX_VTX_USER_PKG.arp_city_exempt_pct_tab(1);
2142 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE4(i) :=ZX_VTX_USER_PKG.arp_district_exempt_pct_tab(1);
2143 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_DATE_ATTRIBUTE1(i) :=ZX_VTX_USER_PKG.arp_transaction_date_tab(1);
2144
2145 ZX_VTX_USER_PKG.g_line_negation := FALSE;
2146
2147 elsif l_tax_provider_id = 2 then /*Case for TAXWARE*/
2148 ZX_TAXWARE_USER_PKG.g_line_negation := TRUE;
2149 ZX_TAXWARE_USER_PKG.g_trx_line_id := zx_ptnr_neg_lines_tab.TRX_LINE_ID(i);
2150 ZX_TAXWARE_USER_PKG.Derive_Hdr_Ext_Attr(x_return_status
2151 , x_messages_tbl);
2152 ZX_TAXWARE_USER_PKG.Derive_Line_Ext_Attr(x_return_status
2153 , x_messages_tbl);
2154 /*Assigning values fetched from user procedures*/
2155 --ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) :=ZX_TAXWARE_USER_PKG.arp_tax_type_tab(1);
2156 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) :=NULL;
2157 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE2(i) :=ZX_TAXWARE_USER_PKG.arp_product_code_tab(1);
2158 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE3(i) :=ZX_TAXWARE_USER_PKG.use_step_tab(1);
2159 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE4(i) :=ZX_TAXWARE_USER_PKG.arp_state_exempt_reason_tab(1);
2160 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE5(i) :=ZX_TAXWARE_USER_PKG.arp_county_exempt_reason_tab(1);
2161 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE6(i) :=ZX_TAXWARE_USER_PKG.arp_city_exempt_reason_tab(1);
2162 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE7(i) :=ZX_TAXWARE_USER_PKG.step_proc_flag_tab(1);
2163 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE8(i) :=ZX_TAXWARE_USER_PKG.arp_audit_flag_tab(1);
2164 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE9(i) :=ZX_TAXWARE_USER_PKG.arp_ship_to_add_tab(1);
2165 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE10(i) :=ZX_TAXWARE_USER_PKG.arp_ship_from_add_tab(1);
2166 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE11(i) :=ZX_TAXWARE_USER_PKG.arp_poa_add_code_tab(1);
2167 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE12(i) :=ZX_TAXWARE_USER_PKG.arp_customer_code_tab(1);
2168 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE13(i) :=ZX_TAXWARE_USER_PKG.arp_customer_name_tab(1);
2169 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE14(i) :=ZX_TAXWARE_USER_PKG.arp_company_code_tab(1);
2170 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE15(i) :=ZX_TAXWARE_USER_PKG.arp_division_code_tab(1);
2171 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE16(i) :=ZX_TAXWARE_USER_PKG.arp_vnd_ctrl_exmpt_tab(1);
2172 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE17(i) :=ZX_TAXWARE_USER_PKG.arp_use_nexpro_tab(1);
2173 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE18(i) :=ZX_TAXWARE_USER_PKG.arp_service_ind_tab(1);
2174 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE19(i) :=ZX_TAXWARE_USER_PKG.crit_flag_tab(1);
2175 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE20(i) :=ZX_TAXWARE_USER_PKG.arp_poo_add_code_tab(1);
2176 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE21(i) :=ZX_TAXWARE_USER_PKG.calculation_flag_tab(1);
2177 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE22(i) :=ZX_TAXWARE_USER_PKG.state_cert_no_tab(1);
2178 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE23(i) :=ZX_TAXWARE_USER_PKG.county_cert_no_tab(1);
2179 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE24(i) :=ZX_TAXWARE_USER_PKG.city_cert_no_tab(1);
2180 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE1(i) :=ZX_TAXWARE_USER_PKG.arp_state_exempt_percent_tab(1);
2181 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE2(i) :=ZX_TAXWARE_USER_PKG.arp_county_exempt_pct_tab(1);
2182 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE3(i) :=ZX_TAXWARE_USER_PKG.arp_city_exempt_pct_tab(1);
2183 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE4(i) :=ZX_TAXWARE_USER_PKG.sec_county_exempt_pct_tab(1);
2184 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE5(i) :=ZX_TAXWARE_USER_PKG.sec_city_exempt_pct_tab(1);
2185 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE6(i) :=ZX_TAXWARE_USER_PKG.arp_tax_sel_param_tab(1);
2186 ZX_PTNR_NEG_LINES_TAB.LINE_EXT_DATE_ATTRIBUTE1(i) :=ZX_TAXWARE_USER_PKG.arp_transaction_date_tab(1);
2187 ZX_TAXWARE_USER_PKG.g_line_negation := FALSE;
2188 end if;
2189 End loop;
2190
2191 IF ZX_API_PUB.G_PUB_SRVC NOT IN ('GLOBAL_DOCUMENT_UPDATE', 'OVERRIDE_TAX') THEN -- Bug 5200373
2192 DELETE FROM ZX_TRX_PRE_PROC_OPTIONS_GT
2193 WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
2194 AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
2195 AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
2196 AND TRX_ID = p_event_class_rec.TRX_ID;
2197 END IF;
2198
2199 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2200 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2201 ' updating zx_ptnr_neg_line_gt');
2202 END IF;
2203
2204 FORALL i IN zx_ptnr_neg_lines_tab.application_id.FIRST .. zx_ptnr_neg_lines_tab.application_id.LAST
2205 Update ZX_PTNR_NEG_LINE_GT SET
2206 LINE_EXT_VARCHAR_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) ,
2207 LINE_EXT_VARCHAR_ATTRIBUTE2 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE2(i) ,
2208 LINE_EXT_VARCHAR_ATTRIBUTE3 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE3(i) ,
2209 LINE_EXT_VARCHAR_ATTRIBUTE4 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE4(i) ,
2210 LINE_EXT_VARCHAR_ATTRIBUTE5 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE5(i) ,
2211 LINE_EXT_VARCHAR_ATTRIBUTE6 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE6(i) ,
2212 LINE_EXT_VARCHAR_ATTRIBUTE7 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE7(i) ,
2213 LINE_EXT_VARCHAR_ATTRIBUTE8 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE8(i) ,
2214 LINE_EXT_VARCHAR_ATTRIBUTE9 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE9(i) ,
2215 LINE_EXT_VARCHAR_ATTRIBUTE10 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE10(i) ,
2216 LINE_EXT_VARCHAR_ATTRIBUTE11 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE11(i) ,
2217 LINE_EXT_VARCHAR_ATTRIBUTE12 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE12(i) ,
2218 LINE_EXT_VARCHAR_ATTRIBUTE13 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE13(i) ,
2219 LINE_EXT_VARCHAR_ATTRIBUTE14 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE14(i) ,
2220 LINE_EXT_VARCHAR_ATTRIBUTE15 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE15(i) ,
2221 LINE_EXT_VARCHAR_ATTRIBUTE16 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE16(i) ,
2222 LINE_EXT_VARCHAR_ATTRIBUTE17 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE17(i) ,
2223 LINE_EXT_VARCHAR_ATTRIBUTE18 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE18(i) ,
2224 LINE_EXT_VARCHAR_ATTRIBUTE19 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE19(i) ,
2225 LINE_EXT_VARCHAR_ATTRIBUTE20 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE20(i) ,
2226 LINE_EXT_VARCHAR_ATTRIBUTE21 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE21(i) ,
2227 LINE_EXT_VARCHAR_ATTRIBUTE22 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE22(i) ,
2228 LINE_EXT_VARCHAR_ATTRIBUTE23 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE23(i) ,
2229 LINE_EXT_VARCHAR_ATTRIBUTE24 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE24(i) ,
2230 LINE_EXT_NUMBER_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE1(i) ,
2231 LINE_EXT_NUMBER_ATTRIBUTE2 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE2(i) ,
2232 LINE_EXT_NUMBER_ATTRIBUTE3 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE3(i) ,
2233 LINE_EXT_NUMBER_ATTRIBUTE4 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE4(i) ,
2234 LINE_EXT_NUMBER_ATTRIBUTE5 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE5(i) ,
2235 LINE_EXT_NUMBER_ATTRIBUTE6 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE6(i) ,
2236 LINE_EXT_DATE_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_DATE_ATTRIBUTE1(i)
2237 WHERE
2238 APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
2239 ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
2240 EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
2241 TRX_ID = p_event_class_rec.TRX_ID AND
2242 TRX_LINE_ID = zx_ptnr_neg_lines_tab.TRX_LINE_ID(i) AND
2243 TRX_LEVEL_TYPE = zx_ptnr_neg_lines_tab.TRX_LEVEL_TYPE(i);
2244 end if;
2245 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2246 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME || ': ' ||l_api_name||'(-)');
2247 END IF;
2248
2249 END COPY_TRX_LINE_FOR_PTNR_BEF_UPD;
2250
2251 PROCEDURE CREATE_SRVC_REGISTN_FROM_UI
2252 (p_api_version IN NUMBER,
2253 x_error_msg OUT NOCOPY VARCHAR2,
2254 x_return_status OUT NOCOPY VARCHAR2,
2255 p_srvc_prvdr_id IN NUMBER,
2256 p_regime_usage_id IN NUMBER,
2257 p_business_flow IN VARCHAR2) IS
2258
2259 l_api_name CONSTANT VARCHAR2(80) := 'CREATE_SRVC_REGISTN_FROM_UI';
2260 l_error_counter NUMBER;
2261 l_srvc_prvdr_name VARCHAR2(80);
2262 x_error_msg_tbl ERROR_MESSAGE_TBL%TYPE;
2263 p_country_code VARCHAR2(30);
2264
2265 Begin
2266 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2267 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2268 END IF;
2269 /*Set the return status to Success */
2270 x_return_status := FND_API.G_RET_STS_SUCCESS;
2271 l_error_counter := 1;
2272
2273 /*Fetch the parnter name*/
2274
2275 select tax_regime_code
2276 into p_country_code
2277 from zx_regimes_usages
2278 where regime_usage_id=p_regime_usage_id;
2279
2280 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2281 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' p_srvc_prvdr_id :'||p_srvc_prvdr_id);
2282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' p_country_code :'||p_country_code);
2283 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' p_business_flow :'||p_business_flow);
2284 END IF;
2285
2286 Begin
2287 SELECT pty.party_name
2288 INTO l_srvc_prvdr_name
2289 FROM HZ_PARTIES pty,
2290 ZX_PARTY_TAX_PROFILE ptp
2291 WHERE ptp.party_tax_profile_id =p_srvc_prvdr_id
2292 AND pty.party_id = ptp.party_id
2293 AND ptp.provider_type_code in ('BOTH', 'SERVICE');
2294 Exception
2295 When others then
2296 fnd_message.set_name('ZX', 'ZX_TAX_PARTNER_NOTFOUND');
2297 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2298 x_return_status := FND_API.G_RET_STS_ERROR;
2299 l_error_counter := l_error_counter+1;
2300 return;
2301 End;
2302
2303 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2304 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' l_srvc_prvdr_name :'|| l_srvc_prvdr_name);
2305 END IF;
2306
2307
2308 if(p_srvc_prvdr_id = 1) then
2309
2310 ZX_API_PRVDR_PUB.create_srvc_registration(
2311 p_api_version,
2312 x_error_msg_tbl,
2313 x_return_status,
2314 l_srvc_prvdr_name,
2315 'CALCULATE_TAX',
2316 p_country_code,
2317 p_business_flow,
2318 'ZX_VERTEX_TAX_SERVICE_PKG',
2319 'CALCULATE_TAX_API');
2320
2321 ZX_API_PRVDR_PUB.create_srvc_registration(
2322 p_api_version,
2323 x_error_msg_tbl,
2324 x_return_status,
2325 l_srvc_prvdr_name,
2326 'SYNCHRONIZE_FOR_TAX',
2327 p_country_code,
2328 p_business_flow,
2329 'ZX_VERTEX_TAX_SERVICE_PKG',
2330 'SYNCHRONIZE_VERTEX_REPOSITORY');
2331
2332
2333 ZX_API_PRVDR_PUB.create_srvc_registration(
2334 p_api_version,
2335 x_error_msg_tbl,
2336 x_return_status,
2337 l_srvc_prvdr_name,
2338 'DOCUMENT_LEVEL_CHANGES',
2339 p_country_code,
2340 p_business_flow,
2341 'ZX_VERTEX_TAX_SERVICE_PKG',
2342 'GLOBAL_DOCUMENT_UPDATE');
2343
2344 elsif(p_srvc_prvdr_id = 2) then
2345 ZX_API_PRVDR_PUB.create_srvc_registration(
2346 p_api_version,
2347 x_error_msg_tbl,
2348 x_return_status,
2349 l_srvc_prvdr_name,
2350 'CALCULATE_TAX',
2351 p_country_code,
2352 p_business_flow,
2353 'ZX_TAXWARE_TAX_SERVICE_PKG',
2354 'CALCULATE_TAX_API');
2355
2356 ZX_API_PRVDR_PUB.create_srvc_registration(
2357 p_api_version,
2358 x_error_msg_tbl,
2359 x_return_status,
2360 l_srvc_prvdr_name,
2361 'SYNCHRONIZE_FOR_TAX',
2362 p_country_code,
2363 p_business_flow,
2364 'ZX_TAXWARE_TAX_SERVICE_PKG',
2365 'SYNCHRONIZE_TAXWARE_REPOSITORY');
2366
2367 ZX_API_PRVDR_PUB.create_srvc_registration(
2368 p_api_version,
2369 x_error_msg_tbl,
2370 x_return_status,
2371 l_srvc_prvdr_name,
2372 'DOCUMENT_LEVEL_CHANGES',
2373 p_country_code,
2374 p_business_flow,
2375 'ZX_TAXWARE_TAX_SERVICE_PKG',
2376 'GLOBAL_DOCUMENT_UPDATE');
2377 else
2378 fnd_message.set_name('ZX', 'ZX_TAX_PARTNER_NOTFOUND');
2379 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2380 x_return_status := FND_API.G_RET_STS_ERROR;
2381 l_error_counter := l_error_counter+1;
2382 return;
2383
2384 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2385 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(falied -)');
2386 END IF;
2387
2388
2389 End if;
2390
2391 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2392 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(-)');
2393 END IF;
2394
2395 End CREATE_SRVC_REGISTN_FROM_UI;
2396
2397 procedure CREATE_EXTN_REGISTN_FROM_UI
2398 (p_api_version IN NUMBER,
2399 x_error_msg OUT NOCOPY varchar2,
2400 x_return_status OUT NOCOPY VARCHAR2,
2401 p_srvc_prvdr_id IN NUMBER,
2402 p_regime_usage_id IN NUMBER,
2403 p_code_generator_flag IN VARCHAR2) is
2404
2405 l_context_flex_structure_id NUMBER;
2406 l_error_counter NUMBER;
2407
2408 l_api_name varchar2(80) := 'CREATE_EXTN_REGISTN_FROM_UI';
2409 l_tax_regime_code ZX_REGIMES_USAGES.tax_regime_code%type;
2410 x_error_msg_tbl error_message_tbl%type;
2411 l_api_owner_id number;
2412 l_context_cc_id number;
2413
2414 Begin
2415 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2416 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2417 END IF;
2418 /*Set the return status to Success */
2419 x_return_status := FND_API.G_RET_STS_SUCCESS;
2420 l_error_counter := 1;
2421
2422 Begin
2423 SELECT context_flex_structure_id
2424 INTO l_context_flex_structure_id
2425 FROM ZX_SERVICE_TYPES
2426 WHERE service_category_code = 'USER_EXT'
2427 AND rownum=1;
2428 Exception when others then
2429 x_error_msg_tbl(l_error_counter) := 'No service type defined for user extension';
2430 x_return_status := FND_API.G_RET_STS_ERROR;
2431 l_error_counter := l_error_counter+1;
2432 return;
2433 END;
2434
2435
2436 Begin
2437 SELECT distinct ru.tax_regime_code
2438 into l_tax_regime_code
2439 FROM zx_regimes_usages ru,
2440 zx_srvc_subscriptions srvc
2441 WHERE srvc.regime_usage_id=p_regime_usage_id
2442 and srvc.regime_usage_id=ru.regime_usage_id
2443 AND NOT EXISTS (SELECT 1
2444 FROM ZX_API_CODE_COMBINATIONS comb
2445 WHERE comb.segment_attribute1 = ru.tax_regime_code
2446 AND comb.segment_attribute2 is null
2447 );
2448 Exception
2449 when others then
2450 l_tax_regime_code := NULL;
2451 End;
2452
2453 if (l_tax_regime_code is not NULL) then
2454 INSERT INTO ZX_API_CODE_COMBINATIONS (
2455 CONTEXT_FLEX_STRUCTURE_ID,
2456 CODE_COMBINATION_ID,
2457 SEGMENT_ATTRIBUTE1,
2458 SUMMARY_FLAG,
2459 ENABLED_FLAG,
2460 RECORD_TYPE_CODE,
2461 CREATION_DATE,
2462 CREATED_BY,
2463 LAST_UPDATE_DATE,
2464 LAST_UPDATED_BY,
2465 LAST_UPDATE_LOGIN)
2466 VALUES (l_context_flex_structure_id,
2467 ZX_API_CODE_COMBINATIONS_S.nextval,
2468 l_tax_regime_code,
2469 'N',
2470 'Y',
2471 'EBTAX_CREATED',
2472 sysdate, --creation_date
2473 fnd_global.user_Id, --created_by
2474 sysdate, --last_update_date
2475 fnd_global.user_id, --last_updated_by
2476 fnd_global.conc_login_id --last_update_login
2477 );
2478
2479
2480 INSERT INTO ZX_API_REGISTRATIONS (
2481 API_REGISTRATION_ID,
2482 API_OWNER_ID,
2483 CONTEXT_CCID,
2484 PACKAGE_NAME,
2485 PROCEDURE_NAME,
2486 SERVICE_TYPE_ID,
2487 RECORD_TYPE_CODE,
2488 OBJECT_VERSION_NUMBER,
2489 CREATION_DATE,
2490 CREATED_BY,
2491 LAST_UPDATE_DATE,
2492 LAST_UPDATED_BY,
2493 LAST_UPDATE_LOGIN
2494 )
2495 SELECT ZX_API_REGISTRATIONS_S.nextval,
2496 ru.first_pty_org_id,
2497 comb.code_combination_id,
2498 decode(srvc.srvc_provider_id,1,'ZX_VTX_USER_PKG','ZX_TAXWARE_USER_PKG'),
2499 decode(srvctypes.service_type_code,'DERIVE_HDR_ATTRS','DERIVE_HDR_EXT_ATTR',
2500 'DERIVE_LINE_ATTRS','DERIVE_LINE_EXT_ATTR'),
2501 srvctypes.service_type_id,
2502 'EBTAX_CREATED',
2503 1,
2504 sysdate, --creation_date
2505 fnd_global.user_Id, --created_by
2506 sysdate, --last_update_date
2507 fnd_global.user_id, --last_updated_by
2508 fnd_global.conc_login_id --last_update_login;
2509 FROM ZX_REGIMES_USAGES ru,
2510 ZX_API_CODE_COMBINATIONS comb,
2511 ZX_SRVC_SUBSCRIPTIONS srvc,
2512 ZX_SERVICE_TYPES srvctypes
2513 WHERE ru.regime_usage_id = p_regime_usage_id
2514 AND srvc.srvc_provider_id = p_srvc_prvdr_id
2515 AND srvctypes.context_flex_structure_id= l_context_flex_structure_id
2516 AND srvc.regime_usage_id = ru.regime_usage_id
2517 AND ru.tax_regime_code = comb.segment_attribute1
2518 AND comb.segment_attribute2 is null
2519 AND comb.context_flex_structure_id = srvctypes.context_flex_structure_id
2520 AND srvctypes.service_type_code in ('DERIVE_HDR_ATTRS','DERIVE_LINE_ATTRS')
2521 AND NOT EXISTS (SELECT 1
2522 FROM ZX_API_REGISTRATIONS api
2523 WHERE api.service_type_id = srvctypes.service_type_id
2524 AND api.context_ccid = comb.code_combination_id
2525 AND api.api_owner_id = ru.first_pty_org_id
2526 );
2527
2528
2529 INSERT INTO zx_api_owner_statuses (
2530 API_OWNER_ID,
2531 SERVICE_CATEGORY_CODE,
2532 STATUS_CODE,
2533 CREATION_DATE,
2534 CREATED_BY,
2535 LAST_UPDATE_DATE,
2536 LAST_UPDATED_BY,
2537 LAST_UPDATE_LOGIN
2538 )
2539 SELECT ru.first_pty_org_id,
2540 'USER_EXT',
2541 'NEW',
2542 sysdate, --creation_date
2543 fnd_global.user_Id, --created_by
2544 sysdate, --last_update_date
2545 fnd_global.user_id, --last_updated_by
2546 fnd_global.conc_login_id --last_update_login;
2547 FROM ZX_SRVC_SUBSCRIPTIONS srvc,
2548 ZX_REGIMES_USAGES ru
2549 WHERE ru.regime_usage_id = p_regime_usage_id
2550 AND srvc.srvc_provider_id = p_srvc_prvdr_id
2551 AND srvc.regime_usage_id = ru.regime_usage_id
2552 AND NOT EXISTS (SELECT 1
2553 FROM ZX_API_OWNER_STATUSES sts
2554 WHERE sts.api_owner_id = ru.first_pty_org_id
2555 );
2556
2557 if(p_code_generator_flag = 'Y') then
2558 Begin
2559 select ru.first_pty_org_id,
2560 comb.code_combination_id
2561 INTO l_api_owner_id,
2562 l_context_cc_id
2563 FROM ZX_REGIMES_USAGES ru,
2564 ZX_API_CODE_COMBINATIONS comb,
2565 ZX_SRVC_SUBSCRIPTIONS srvc,
2566 ZX_SERVICE_TYPES srvctypes
2567 WHERE ru.regime_usage_id = p_regime_usage_id
2568 AND srvc.srvc_provider_id = p_srvc_prvdr_id
2569 AND srvctypes.context_flex_structure_id= l_context_flex_structure_id
2570 AND srvc.regime_usage_id = ru.regime_usage_id
2571 AND ru.tax_regime_code = comb.segment_attribute1
2572 AND comb.segment_attribute2 is null
2573 AND comb.context_flex_structure_id = srvctypes.context_flex_structure_id
2574 AND srvctypes.service_type_code in ('DERIVE_HDR_ATTRS','DERIVE_LINE_ATTRS');
2575
2576 EXECUTE_EXTN_PLUGIN_FROM_UI(P_API_VERSION => 1.0,
2577 X_ERROR_MSG => x_error_msg,
2578 X_RETURN_STATUS => x_return_status ,
2579 P_API_OWNER_ID => l_api_owner_id ,
2580 P_CONTEXT_CC_ID => l_context_cc_id );
2581 Exception When Others then
2582 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2583 x_return_status := FND_API.G_RET_STS_ERROR;
2584 l_error_counter := l_error_counter+1;
2585 return;
2586 End;
2587
2588 END if;
2589
2590 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2591 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(-)');
2592 END IF;
2593
2594
2595 END IF;
2596
2597 End CREATE_EXTN_REGISTN_FROM_UI;
2598
2599 Procedure execute_srvc_plugin_from_ui
2600 ( p_api_version IN NUMBER,
2601 x_error_msg OUT NOCOPY VARCHAR2,
2602 x_return_status OUT NOCOPY VARCHAR2,
2603 p_srvc_prvdr_id IN NUMBER) is
2604
2605 l_srvc_prvdr_name varchar2(80);
2606 l_error_counter number;
2607 l_api_name constant varchar2(80) := 'EXECUTE_SRVC_PLUGIN_FROM_UI';
2608 x_error_msg_tbl error_message_tbl%type;
2609
2610 Begin
2611 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2612 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2613 END IF;
2614 /*Set the return status to Success */
2615 x_return_status := FND_API.G_RET_STS_SUCCESS;
2616 l_error_counter := 1;
2617
2618 /*Fetch the parnter name*/
2619
2620 Begin
2621 SELECT pty.party_name
2622 INTO l_srvc_prvdr_name
2623 FROM HZ_PARTIES pty,
2624 ZX_PARTY_TAX_PROFILE ptp
2625 WHERE ptp.party_tax_profile_id =p_srvc_prvdr_id
2626 AND pty.party_id = ptp.party_id
2627 AND ptp.provider_type_code in ('BOTH', 'SERVICE');
2628 Exception
2629 When others then
2630 fnd_message.set_name('ZX', 'ZX_TAX_PARTNER_NOTFOUND');
2631 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2632 x_return_status := FND_API.G_RET_STS_ERROR;
2633 l_error_counter := l_error_counter+1;
2634 return;
2635 End;
2636
2637 ZX_API_PRVDR_PUB.execute_srvc_plugin(p_api_version ,
2638 x_error_msg_tbl,
2639 x_return_status,
2640 l_srvc_prvdr_name);
2641
2642 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2643 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2644 x_return_status := FND_API.G_RET_STS_ERROR;
2645 l_error_counter := l_error_counter+1;
2646 return;
2647 End if;
2648
2649 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2650 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2651 END IF;
2652
2653 End execute_srvc_plugin_from_ui;
2654
2655 Procedure execute_extn_plugin_from_ui
2656 ( p_api_version IN NUMBER,
2657 x_error_msg OUT NOCOPY varchar2,
2658 x_return_status OUT NOCOPY VARCHAR2,
2659 p_api_owner_id IN number,
2660 p_context_cc_id IN number) is
2661
2662 l_srvc_provider_id number;
2663 l_error_counter number;
2664 l_api_name constant varchar2(80) := 'EXECUTE_EXTN_PLUGIN_FROM_UI';
2665 l_request_id number;
2666 x_error_msg_tbl error_message_tbl%type;
2667
2668 Begin
2669 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2670 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2671 END IF;
2672 /*Set the return status to Success */
2673 x_return_status := FND_API.G_RET_STS_SUCCESS;
2674 l_error_counter := 1;
2675
2676 Begin
2677 Select
2678 distinct srvc.srvc_provider_id
2679 into l_srvc_provider_id
2680 from zx_api_code_combinations comb,
2681 zx_regimes_usages usage,
2682 zx_srvc_subscriptions srvc
2683 where comb.code_combination_id= p_context_cc_id
2684 and comb.segment_attribute1= usage.tax_regime_code
2685 and comb.segment_attribute2= 'O2C'
2686 and usage.first_pty_org_id= p_api_owner_id
2687 and srvc.regime_usage_id=usage.regime_usage_id
2688 and not exists (select 1 from zx_api_registrations
2689 where API_OWNER_ID = srvc.srvc_provider_id
2690 and CONTEXT_CCID = p_context_cc_id);
2691 Exception
2692 When others then
2693 l_srvc_provider_id := NULL;
2694 End;
2695
2696 IF(l_srvc_provider_id is not NULL) then
2697 execute_srvc_plugin_from_ui(p_api_version,
2698 x_error_msg,
2699 x_return_status,
2700 l_srvc_provider_id);
2701 End if;
2702
2703 savepoint execute_srvc_plugin_pvt;
2704
2705 Begin
2706 l_request_id := fnd_request.submit_request
2707 (
2708 application => 'ZX',
2709 program => 'ZXPTNRSRVCPLUGIN',
2710 sub_request => false,
2711 argument1 => 'USER_EXT',
2712 argument2 => P_api_owner_id
2713 );
2714 commit;
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 ROLLBACK TO execute_srvc_plugin_pvt;
2719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2720 fnd_message.set_name('ZX', 'ZX_UNEXPECTED_ERROR');
2721 x_error_msg_tbl(l_error_counter) := fnd_message.get;
2722 l_error_counter := l_error_counter+1;
2723 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
2724 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
2725 END IF;
2726 End;
2727
2728 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2729 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2730 END IF;
2731
2732 END execute_extn_plugin_from_ui;
2733
2734 END zx_r11i_tax_partner_pkg;