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