[Home] [Help]
PACKAGE BODY: APPS.IGI_STP_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY IGI_STP_TABLE_HANDLER_PKG AS
2 -- $Header: igistpab.pls 120.11.12000000.8 2007/10/09 06:05:00 gkumares ship $
3 --following variables added for bug 3199481: fnd logging changes: sdixit
4 l_debug_level number;
5 l_state_level number;
6 l_proc_level number;
7 l_event_level number;
8 l_excep_level number;
9 l_error_level number;
10 l_unexp_level number;
11
12
13
14
15
16 PROCEDURE Address_Insert_Row( X_rowid IN OUT NOCOPY VARCHAR2,
17 X_address_id NUMBER,
18 X_customer_id NUMBER,
19 X_org_id NUMBER,
20 X_status VARCHAR2,
21 X_orig_system_reference VARCHAR2,
22 X_country VARCHAR2,
23 X_address1 VARCHAR2,
24 X_address2 VARCHAR2,
25 X_address3 VARCHAR2,
26 X_address4 VARCHAR2,
27 X_city VARCHAR2,
28 X_postal_code VARCHAR2,
29 X_state VARCHAR2,
30 X_province VARCHAR2,
31 X_county VARCHAR2,
32 X_address_key VARCHAR2,
33 X_key_account_flag VARCHAR2,
34 X_language VARCHAR2,
35 X_address_lines_phonetic VARCHAR2,
36 X_customer_category_code VARCHAR2,
37 X_ece_tp_location_code VARCHAR2,
38 X_stp_common_ref VARCHAR2,
39 X_stp_alt_addr VARCHAR2,
40 X_stp_supplier VARCHAR2,
41 X_stp_site_inactive_date DATE,
42 X_creation_date DATE,
43 X_created_by NUMBER,
44 X_last_update_login NUMBER,
45 X_last_update_date DATE,
46 X_last_updated_by NUMBER,
47 X_party_id NUMBER,
48 X_location_id NUMBER,
49 X_party_site_id NUMBER,
50 X_party_site_number VARCHAR2) IS
51
52
53 CURSOR C IS SELECT location_id FROM HZ_LOCATIONS
54 WHERE location_id = X_location_id;
55 CURSOR C1 IS SELECT party_site_id FROM HZ_PARTY_SITES
56 WHERE party_site_id = X_party_site_id;
57 CURSOR C2 IS SELECT rowid FROM HZ_CUST_ACCT_SITES
58 WHERE cust_acct_site_id = X_address_id;
59 CURSOR C3 IS SELECT address_id FROM IGI_RA_ADDRESSES
60 WHERE address_id = X_address_id;
61 CURSOR C4 IS SELECT location_id FROM HZ_LOC_ASSIGNMENTS
62 WHERE location_id = X_location_id;
63
64 c_location_id number(15);
65 c1_party_site_id number(15);
66 c3_address_id number(15);
67
68 p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
69 p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
70 x_site_use_id NUMBER;
71 x_return_status VARCHAR2(2000);
72 x_msg_count NUMBER;
73 x_msg_data VARCHAR2(2000);
74
75 -- Bug 2037659 Fix
76
77 l_location_rec hz_location_v2pub.location_rec_type;
78 l_return_status varchar2(3);
79 l_msg_count number;
80 l_msg_data varchar2(2000);
81 l_loc_id number;
82 l_loc_out_id number;
83
84 -- Bug 2037659 Fix
85
86 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
87 l_party_site_id number;
88 l_party_site_number varchar2(2000);
89
90
91 l_application_id number;
92 l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
93 l_cust_acct_site_id number;
94 l_profile_value varchar2(100); -- For capturing the value returned from fnd_profile.get
95
96 v_cnt number;
97
98 BEGIN
99
100 -- Bug 2037659 Fix
101
102 l_location_rec.location_id := x_location_id;
103 l_location_rec.country := x_country;
104 l_location_rec.address1 := x_address1;
105 l_location_rec.address2 := x_address2;
106 l_location_rec.address3 := x_address3;
107 l_location_rec.address4 := x_address4;
108 l_location_rec.city := x_city;
109 l_location_rec.postal_code := x_postal_code;
110 l_location_rec.state := x_state;
111 l_location_rec.province := x_province;
112 l_location_rec.county := x_county;
113 l_location_rec.language := x_language;
114 l_location_rec.orig_system_reference := x_location_id;
115 l_location_rec.created_by_module := 'IGI_STP';
116 -- Bug 2846318 Start
117 l_location_rec.address_lines_phonetic := x_address_lines_phonetic;
118 -- Bug 2846318 End
119
120
121 l_cust_acct_site_rec.cust_acct_site_id := X_address_id;
122 l_cust_acct_site_rec.cust_account_id := X_customer_id;
123 l_cust_acct_site_rec.party_site_id := X_party_site_id;
124 l_cust_acct_site_rec.orig_system_reference := X_orig_system_reference;
125 l_cust_acct_site_rec.status := X_status;
126 l_cust_acct_site_rec.customer_category_code := X_customer_category_code;
127 l_cust_acct_site_rec.key_account_flag := X_key_account_flag;
128 l_cust_acct_site_rec.ece_tp_location_code := X_ece_tp_location_code;
129 l_cust_acct_site_rec.created_by_module := 'IGI_STP';
130
131
132 hz_location_v2pub.create_location
133 (
134 p_init_msg_list => fnd_api.g_false,
135 p_location_rec => l_location_rec,
136 x_location_id => l_loc_id,
137 x_return_status => l_return_status,
138 x_msg_count => l_msg_count,
139 x_msg_data => l_msg_data
140 );
141
142
143 OPEN C;
144 FETCH C INTO c_location_id;
145 IF (C%NOTFOUND) THEN
146 CLOSE C;
147 Raise NO_DATA_FOUND;
148 END IF;
149 CLOSE C;
150
151
152
153 -- Bug 2037659 Fix
154 /*
155 hz_tax_assignment_v2pub.create_loc_assignment
156 (
157 p_init_msg_list => fnd_api.g_false,
158 p_location_id => l_loc_id,
159 p_lock_flag => fnd_api.g_false,
160 p_created_by_module => 'IGI_STP',
161 p_application_id => l_application_id,
162 x_return_status => l_return_status,
163 x_msg_count => l_msg_count,
164 x_msg_data => l_msg_data,
165 x_loc_id => l_loc_out_id
166 );
167
168
169
170 OPEN C4;
171 FETCH C4 INTO c_location_id;
172 IF (C4%NOTFOUND) THEN
173 CLOSE C4;
174 Raise NO_DATA_FOUND;
175 END IF;
176 CLOSE C4; */
177
178 l_party_site_rec.party_site_id := X_party_site_id;
179 l_party_site_rec.party_id := X_party_id;
180 l_party_site_rec.location_id := X_location_id;
181
182 FND_PROFILE.GET('HZ_GENERATE_PARTY_SITE_NUMBER', l_profile_value);
183
184 IF (l_profile_value = 'N') THEN
185 l_party_site_rec.party_site_number := X_party_site_number;
186 ELSE
187 l_party_site_rec.party_site_number := NULL;
188 END IF;
189
190 IF (X_status = 'A') THEN /* Code changed */
191 l_party_site_rec.identifying_address_flag := 'Y';
192 ELSE
193 l_party_site_rec.identifying_address_flag := 'N';
194 END IF;
195
196 l_party_site_rec.status := X_status;
197 l_party_site_rec.created_by_module := 'IGI_STP';
198
199 hz_party_site_v2pub.create_party_site
200 (
201 p_init_msg_list => fnd_api.g_true,
202 p_party_site_rec => l_party_site_rec,
203 x_party_site_id => l_party_site_id,
204 x_party_site_number => l_party_site_number,
205 x_return_status => l_return_status,
206 x_msg_count => l_msg_count,
207 x_msg_data => l_msg_data
208 );
209
210 OPEN C1;
211 FETCH C1 INTO c1_party_site_id;
212 IF (C1%NOTFOUND) THEN
213 CLOSE C1;
214 Raise NO_DATA_FOUND;
215 END IF;
216 CLOSE C1;
217
218
219 hz_cust_account_site_v2pub.create_cust_acct_site
220 (
221 p_init_msg_list => fnd_api.g_false,
222 p_cust_acct_site_rec => l_cust_acct_site_rec,
223 x_cust_acct_site_id => l_cust_acct_site_id,
224 x_return_status => l_return_status,
225 x_msg_count => l_msg_count,
226 x_msg_data => l_msg_data
227 );
228
229 OPEN C2;
230 FETCH C2 INTO X_ROWID;
231 IF (C2%NOTFOUND) THEN
232 CLOSE C2;
233 Raise NO_DATA_FOUND;
234 END IF;
235 CLOSE C2;
236
237 /* p_cust_site_use_rec.cust_acct_site_id := l_cust_acct_site_id;
238 p_cust_site_use_rec.site_use_code := 'BILL_TO';
239 p_cust_site_use_rec.created_by_module := 'IGI_STP';
240 hz_cust_account_site_v2pub.create_cust_site_use(
241 'T',
242 p_cust_site_use_rec,
243 p_customer_profile_rec,
244 '',
245 '',
246 x_site_use_id,
247 x_return_status,
248 x_msg_count,
249 x_msg_data); */
250
251
252
253 INSERT INTO igi_ra_addresses(
254 address_id,
255 ORG_ID,
256 stp_common_ref,
257 stp_alt_addr,
258 stp_supplier,
259 stp_site_inactive_date,
260 creation_date,
261 created_by,
262 last_update_login,
263 last_update_date,
264 last_updated_by)
265 VALUES(
266 X_address_id,
267 X_ORG_ID,
268 X_stp_common_ref,
269 X_stp_alt_addr,
270 X_stp_supplier,
271 X_stp_site_inactive_date,
272 X_creation_date,
273 X_created_by,
274 X_last_update_login,
275 X_last_update_date,
276 X_last_updated_by);
277
278 OPEN C3;
279 FETCH C3 INTO c3_address_id;
280 IF (C3%NOTFOUND) THEN
281 CLOSE C3;
282 Raise NO_DATA_FOUND;
283 END IF;
284 CLOSE C3;
285
286
287
288 END Address_Insert_Row;
289
290
291
292
293
294 /***************************************************************************************/
295 PROCEDURE Address_Update_Row( X_rowid IN OUT NOCOPY VARCHAR2,
296 X_org_id NUMBER,
297 X_status VARCHAR2,
298 X_orig_system_reference VARCHAR2,
299 X_country VARCHAR2,
300 X_address1 VARCHAR2,
301 X_address2 VARCHAR2,
302 X_address3 VARCHAR2,
303 X_address4 VARCHAR2,
304 X_city VARCHAR2,
305 X_postal_code VARCHAR2,
306 X_state VARCHAR2,
307 X_province VARCHAR2,
308 X_county VARCHAR2,
309 X_address_key VARCHAR2,
310 X_language VARCHAR2,
311 X_address_lines_phonetic VARCHAR2,
312 X_customer_category_code VARCHAR2,
313 X_ece_tp_location_code VARCHAR2,
314 X_stp_common_ref VARCHAR2,
315 X_stp_alt_addr VARCHAR2,
316 X_stp_supplier VARCHAR2,
317 X_stp_site_inactive_date DATE,
318 X_last_update_login NUMBER,
319 X_last_update_date DATE,
320 X_last_updated_by NUMBER) IS
321 l_location_rec hz_location_v2pub.location_rec_type;
322 l_return_status varchar2(3);
323 l_msg_count number;
324 l_msg_data varchar2(2000);
325
326 l_location_id number;
327 l_object_version_number number;
328 cursor c_loc is select LOC.LOCATION_ID from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES CS
329 WHERE PS.PARTY_SITE_ID = CS.PARTY_SITE_ID
330 AND LOC.LOCATION_ID = PS.LOCATION_ID
331 AND CS.rowid = x_rowid;
332
333 l_party_site_id number;
334 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
335 cursor c_party is select PS.party_site_id from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES CS
336 WHERE PS.PARTY_SITE_ID = CS.PARTY_SITE_ID
337 AND LOC.LOCATION_ID = PS.LOCATION_ID
338 AND CS.rowid = x_rowid;
339
340 l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
341 l_cust_acct_site_id number;
342 cursor c_cust_acct is select cust_acct_site_id from hz_cust_acct_sites
343 where rowid = X_rowid;
344
345 cursor c_obj_version_loc (p_location_id hz_locations.location_id%type) is
346 select object_version_number
347 from hz_locations
348 where (location_id = p_location_id);
349
350 cursor c_obj_version_site (p_party_site_id hz_party_sites.party_site_id%type) is
351 select object_version_number
352 from hz_party_sites
353 where (party_site_id = p_party_site_id);
354
355 cursor c_obj_version_cust_acct (p_cust_acct_site_id hz_cust_acct_sites.cust_acct_site_id%type) is
356 select object_version_number
357 from hz_cust_acct_sites
358 where (cust_acct_site_id = p_cust_acct_site_id);
359
360 BEGIN
361 OPEN C_LOC;
362 FETCH C_LOC INTO l_location_id;
363 IF (C_LOC%NOTFOUND) THEN
364 CLOSE C_LOC;
365 Raise NO_DATA_FOUND;
366 END IF;
367 CLOSE C_LOC;
368
369 --Modified for bug # 5263736
370 l_location_rec.location_id := l_location_id;
371 l_location_rec.country := x_country;
372 l_location_rec.address1 := x_address1;
373 l_location_rec.address2 := NVL(x_address2, fnd_api.g_miss_char);
374 l_location_rec.address3 := NVL(x_address3, fnd_api.g_miss_char);
375 l_location_rec.address4 := NVL(x_address4, fnd_api.g_miss_char);
376 l_location_rec.city := NVL(x_city, fnd_api.g_miss_char);
377 l_location_rec.postal_code := NVL(x_postal_code, fnd_api.g_miss_char);
378 l_location_rec.state := NVL(x_state, fnd_api.g_miss_char);
379 l_location_rec.province := NVL(x_province, fnd_api.g_miss_char);
380 l_location_rec.county := NVL(x_county, fnd_api.g_miss_char);
381 l_location_rec.language := NVL(x_language, fnd_api.g_miss_char);
382 l_location_rec.address_lines_phonetic := NVL(x_address_lines_phonetic, fnd_api.g_miss_char);
383 --Modified for bug # 5263736
384
385
386 OPEN C_PARTY;
387 FETCH C_PARTY INTO l_party_site_id;
388 IF (C_PARTY%NOTFOUND) THEN
389 CLOSE C_PARTY;
390 Raise NO_DATA_FOUND;
391 END IF;
392 CLOSE C_PARTY;
393
394
395 l_party_site_rec.party_site_id := l_party_site_id;
396 l_party_site_rec.status := X_status;
397 l_party_site_rec.created_by_module := 'IGI_STP';
398
399 OPEN C_CUST_ACCT;
400 FETCH C_CUST_ACCT INTO l_cust_acct_site_id;
401 IF (C_CUST_ACCT%NOTFOUND) THEN
402 CLOSE C_CUST_ACCT;
403 Raise NO_DATA_FOUND;
404 END IF;
405 CLOSE C_CUST_ACCT;
406
407 --Modified for bug # 5263736
408 l_cust_acct_site_rec.cust_acct_site_id := NVL(l_cust_acct_site_id, fnd_api.g_miss_num);
409 l_cust_acct_site_rec.status := X_status;
410 l_cust_acct_site_rec.language := NVL(X_language, fnd_api.g_miss_char);
411 l_cust_acct_site_rec.customer_category_code := NVL(X_customer_category_code, fnd_api.g_miss_char);
412 l_cust_acct_site_rec.ece_tp_location_code := NVL(X_ece_tp_location_code, fnd_api.g_miss_char);
413 --Modified for bug # 5263736
414
415 OPEN c_obj_version_loc (l_location_id);
416 FETCH c_obj_version_loc INTO l_object_version_number;
417 CLOSE c_obj_version_loc;
418
419
420
421
422 hz_location_v2pub.update_location
423 (
424 p_init_msg_list => fnd_api.g_false,
425 p_location_rec => l_location_rec,
426 p_object_version_number => l_object_version_number,
427 X_return_status => l_return_status,
428 X_msg_count => l_msg_count,
429 X_msg_data => l_msg_data
430 );
431
432
433
434
435
436 --Added for bug 5263736
437 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
438 RETURN;
439 END IF;
440 --Added for bug 5263736
441 /* Since the same local variable is used for capturing object version numbers of hz_locations and hz_party_sites first nullify it */
442
443 l_object_version_number := NULL;
444
445 OPEN c_obj_version_site (l_party_site_id);
446 FETCH c_obj_version_site INTO l_object_version_number;
447 CLOSE c_obj_version_site;
448
449 hz_party_site_v2pub.update_party_site
450 (
451 p_init_msg_list => fnd_api.g_false,
452 p_party_site_rec => l_party_site_rec,
453 p_object_version_number => l_object_version_number,
454 x_return_status => l_return_status,
455 x_msg_count => l_msg_count,
456 x_msg_data => l_msg_data
457 );
458
459 /* Since the same local variable is used for capturing object version numbers of hz_locations, hz_party_sites andcust_acc hz_cust_acct_sites first nullify it */
460
461 l_object_version_number := NULL;
462
463 OPEN c_obj_version_cust_acct (l_cust_acct_site_id);
464 FETCH c_obj_version_cust_acct INTO l_object_version_number;
465 CLOSE c_obj_version_cust_acct;
466
467 hz_cust_account_site_v2pub.update_cust_acct_site
468 (
469 p_init_msg_list => fnd_api.g_false,
470 p_cust_acct_site_rec => l_cust_acct_site_rec,
471 p_object_version_number => l_object_version_number,
472 x_return_status => l_return_status,
473 x_msg_count => l_msg_count,
474 x_msg_data => l_msg_data
475 );
476
477 UPDATE igi_ra_addresses ira
478 SET
479 stp_common_ref = X_stp_common_ref,
480 stp_alt_addr = X_stp_alt_addr,
481 stp_supplier = X_stp_supplier,
482 stp_site_inactive_date = X_stp_site_inactive_date,
483 last_update_login = X_last_update_login,
484 last_update_date = X_last_update_date,
485 last_updated_by = X_last_updated_by
486 WHERE ira.address_id = (select CS.cust_acct_site_id
487 from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES CS
488 WHERE PS.PARTY_SITE_ID = CS.PARTY_SITE_ID
489 AND LOC.LOCATION_ID = PS.LOCATION_ID
490 AND CS.rowid = x_rowid);
491 END Address_Update_Row;
492
493
494 /***************************************************************************************/
495 PROCEDURE Address_Lock_Row( X_rowid IN OUT NOCOPY VARCHAR2,
496 X_org_id NUMBER,
497 X_status VARCHAR2,
498 X_orig_system_reference VARCHAR2,
499 X_country VARCHAR2,
500 X_address1 VARCHAR2,
501 X_address2 VARCHAR2,
502 X_address3 VARCHAR2,
503 X_address4 VARCHAR2,
504 X_city VARCHAR2,
505 X_postal_code VARCHAR2,
506 X_state VARCHAR2,
507 X_province VARCHAR2,
508 X_county VARCHAR2,
509 X_address_key VARCHAR2,
510 X_language VARCHAR2,
511 X_address_lines_phonetic VARCHAR2,
512 X_customer_category_code VARCHAR2,
513 X_ece_tp_location_code VARCHAR2,
514 X_stp_common_ref VARCHAR2,
515 X_stp_alt_addr VARCHAR2,
516 X_stp_supplier VARCHAR2,
517 X_stp_site_inactive_date DATE) IS
518 CURSOR C IS
519 SELECT CS.ORG_ID,
520 CS.STATUS,
521 CS.orig_system_reference,
522 LOC.country,
523 LOC.address1,
524 LOC.address2,
525 LOC.address3,
526 LOC.address4,
527 LOC.city,
528 LOC.postal_code,
529 LOC.state,
530 LOC.province,
531 LOC.county ,
532 LOC.address_key,
533 LOC.language,
534 LOC.address_lines_phonetic,
535 CS.customer_category_code,
536 CS.ece_tp_location_code
537 FROM HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES CS
538 WHERE PS.PARTY_SITE_ID = CS.PARTY_SITE_ID
539 AND LOC.LOCATION_ID = PS.LOCATION_ID
540 AND CS.rowid = x_rowid
541 FOR UPDATE OF CS.cust_acct_site_id NOWAIT;
542
543 RecAddr C%ROWTYPE;
544
545 CURSOR C1 IS
546 SELECT *
547 FROM igi_ra_addresses ira
548 WHERE ira.address_id = (select CS.cust_acct_site_id
549 from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES CS
550 WHERE PS.PARTY_SITE_ID = CS.PARTY_SITE_ID
551 AND LOC.LOCATION_ID = PS.LOCATION_ID
552 AND CS.rowid = x_rowid)
553 FOR UPDATE OF ira.address_Id NOWAIT;
554
555 RecAddr1 C1%ROWTYPE;
556
557 BEGIN
558 OPEN C;
559 FETCH C INTO RecAddr;
560 IF (C%NOTFOUND) THEN
561 CLOSE C;
562 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
563 --bug 3199481 fnd logging changes: sdixit
564 IF (l_excep_level >= l_debug_level ) THEN
565 FND_LOG.MESSAGE (l_excep_level ,
566 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Address_Lock_Row.msg1',
567 FALSE);
568 END IF;
569 APP_EXCEPTION.RAISE_EXCEPTION;
570 END IF;
571 CLOSE C;
572
573 OPEN C1;
574 FETCH C1 INTO RecAddr1;
575 IF (C1%NOTFOUND) THEN
576 CLOSE C1;
577 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
578 --bug 3199481 fnd logging changes: sdixit
579 IF (l_excep_level >= l_debug_level ) THEN
580 FND_LOG.MESSAGE (l_excep_level ,
581 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Address_Lock_Row.msg2',
582 FALSE);
583 END IF;
584 APP_EXCEPTION.RAISE_EXCEPTION;
585 END IF;
586 CLOSE C1;
587
588 IF (
589 ( (RecAddr.org_id = X_org_id)
590 OR ( (RecAddr.org_id IS NULL)
591 AND (X_org_id IS NULL)
592 )
593 )
594 AND (RecAddr.status = X_status)
595 AND (RecAddr.orig_system_reference = X_orig_system_reference)
596 AND (RecAddr.country = X_country)
597 AND (RecAddr.address1 = X_address1)
598 AND ( (RecAddr.address2 = X_address2)
599 OR ( (RecAddr.address2 IS NULL)
600 AND (X_address2 IS NULL)))
601 AND ( (RecAddr.address3 = X_address3)
602 OR ( (RecAddr.address3 IS NULL)
603 AND (X_address3 IS NULL)))
604 AND ( (RecAddr.address4 = X_address4)
605 OR ( (RecAddr.address4 IS NULL)
606 AND (X_address4 IS NULL)))
607 AND ( (RecAddr.city = X_city)
608 OR ( (RecAddr.city IS NULL)
609 AND (X_city IS NULL)))
610 AND ( (RecAddr.postal_code = X_postal_code)
611 OR ( (RecAddr.postal_code IS NULL)
612 AND (X_postal_code IS NULL)))
613 AND ( (RecAddr.state = X_state)
614 OR ( (RecAddr.state IS NULL)
615 AND (X_state IS NULL)))
616 AND ( (RecAddr.province = X_province)
617 OR ( (RecAddr.province IS NULL)
618 AND (X_province IS NULL)))
619 AND ( (RecAddr.county = X_county)
620 OR ( (RecAddr.county IS NULL)
621 AND (X_county IS NULL)))
622 AND ( (RecAddr.address_key = X_address_key)
623 OR ( (RecAddr.address_key IS NULL)
624 AND (X_address_key IS NULL)))
625 AND ( (RecAddr.language = X_language)
626 OR ( (RecAddr.language IS NULL)
627 AND (X_language IS NULL)))
628 AND ( (RecAddr.address_lines_phonetic = X_address_lines_phonetic)
629 OR ( (RecAddr.address_lines_phonetic IS NULL)
630 AND (X_address_lines_phonetic IS NULL)))
631 AND ( (RecAddr.customer_category_code = X_customer_category_code)
632 OR ( (RecAddr.customer_category_code IS NULL)
633 AND (X_customer_category_code IS NULL)))
634 AND ( (RecAddr.ece_tp_location_code = X_ece_tp_location_code)
635 OR ( (RecAddr.ece_tp_location_code IS NULL)
636 AND (X_ece_tp_location_code IS NULL)))
637 AND (RecAddr1.stp_common_ref = X_stp_common_ref)
638 AND ( (RecAddr1.stp_alt_addr = X_stp_alt_addr)
639 OR ( (RecAddr1.stp_alt_addr IS NULL)
640 AND (X_stp_alt_addr IS NULL)))
641 AND ( (RecAddr1.stp_supplier = X_stp_supplier)
642 OR ( (RecAddr1.stp_supplier IS NULL)
643 AND (X_stp_supplier IS NULL)))
644 AND ( (RecAddr1.stp_site_inactive_date = X_stp_site_inactive_date)
645 OR ( (RecAddr1.stp_site_inactive_date IS NULL)
646 AND (X_stp_site_inactive_date IS NULL)))
647 ) THEN
648 return;
649 ELSE
650
651 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
652 --bug 3199481 fnd logging changes: sdixit
653 IF (l_excep_level >= l_debug_level ) THEN
654 FND_LOG.MESSAGE (l_excep_level ,
655 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Address_Lock_Row',
656 FALSE);
657 END IF;
658 APP_EXCEPTION.Raise_Exception;
659 END IF;
660
661 END Address_Lock_Row;
662
663 /***************************************************************************************/
664 PROCEDURE Site_Insert_Row( X_Rowid IN VARCHAR2,
665 X_vendor_site_id NUMBER,
666 X_vendor_id NUMBER,
667 X_vendor_site_code VARCHAR2,
668 X_purchasing_site_flag VARCHAR2,
669 X_rfq_only_site_flag VARCHAR2,
670 X_pay_site_flag VARCHAR2,
671 X_attention_ar_flag VARCHAR2,
672 X_address_line1 VARCHAR2,
673 X_address_line2 VARCHAR2,
674 X_address_line3 VARCHAR2,
675 X_city VARCHAR2,
676 X_state VARCHAR2,
677 X_zip VARCHAR2,
678 X_province VARCHAR2,
679 X_country VARCHAR2,
680 X_customer_num VARCHAR2,
681 X_ship_to_location_id NUMBER,
682 X_bill_to_location_id NUMBER,
683 X_inactive_date DATE,
684 X_payment_method_lookup_code VARCHAR2,
685 X_terms_date_basis VARCHAR2,
686 X_vat_code VARCHAR2,
687 X_accts_pay_code_comb_id NUMBER,
688 X_prepay_code_combination_id NUMBER,
689 X_pay_group_lookup_code VARCHAR2,
690 X_payment_priority NUMBER,
691 X_terms_id NUMBER,
692 X_pay_date_basis_lookup_code VARCHAR2,
693 X_always_take_disc_flag VARCHAR2,
694 X_invoice_currency_code VARCHAR2,
695 X_payment_currency_code VARCHAR2,
696 X_hold_all_payments_flag VARCHAR2,
697 X_hold_future_payments_flag VARCHAR2,
698 X_hold_unmatched_inv_flag VARCHAR2,
699 X_exclusive_payment_flag VARCHAR2,
700 X_tax_reporting_site_flag VARCHAR2,
701 X_validation_number NUMBER,
702 X_excl_freight_from_discount VARCHAR2,
703 X_org_id NUMBER,
704 X_address_line4 VARCHAR2,
705 X_county VARCHAR2,
706 X_address_style VARCHAR2,
707 X_language VARCHAR2,
708 X_allow_awt_flag VARCHAR2,
709 X_auto_tax_calc_flag VARCHAR2,
710 X_auto_tax_calc_override VARCHAR2,
711 X_amount_includes_tax_flag VARCHAR2,
712 X_ap_tax_rounding_rule VARCHAR2,
713 X_vendor_site_code_alt VARCHAR2,
714 X_address_lines_alt VARCHAR2,
715 X_bank_charge_bearer VARCHAR2,
716 X_ece_tp_location_code VARCHAR2,
717 X_pcard_site_flag VARCHAR2,
718 X_creation_date DATE,
719 X_created_by NUMBER,
720 X_last_update_login NUMBER,
721 X_last_update_date DATE,
722 X_last_updated_by NUMBER,
723 X_fin_match_option VARCHAR2 ) IS
724
725
726
727 l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
728 l_return_status varchar2(2000);
729 l_msg_count NUMBER;
730 l_msg_data VARCHAR2(2000);
731 l_vendor_site_id AP_SUPPLIERS.VENDOR_ID%TYPE;
732 l_party_site_id HZ_PARTY_SITES.party_site_id %TYPE;
733 l_location_id HZ_LOCATIONS.location_id%type;
734
735 CURSOR C IS SELECT rowid FROM ap_supplier_sites
736 WHERE vendor_site_id = l_vendor_site_id;
737
738 v_rowid VARCHAR2(25);
739
740 BEGIN
741
742
743 l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := X_accts_pay_code_comb_id ;
744 l_vendor_site_rec.ADDRESS_LINE1 := X_address_line1 ;
745 l_vendor_site_rec.ADDRESS_LINE2 := X_address_line2;
746 l_vendor_site_rec.ADDRESS_LINE3 := X_address_line3;
747 l_vendor_site_rec.ADDRESS_LINE4 := X_address_line4;
748 l_vendor_site_rec.ADDRESS_LINES_ALT:= X_address_lines_alt;
749 l_vendor_site_rec.ADDRESS_STYLE := X_address_style;
750 l_vendor_site_rec.ALLOW_AWT_FLAG := X_allow_awt_flag ;
751 l_vendor_site_rec.ALWAYS_TAKE_DISC_FLAG := X_always_take_disc_flag;
752 l_vendor_site_rec.BANK_CHARGE_BEARER := x_bank_charge_bearer ;
753 l_vendor_site_rec.BILL_TO_LOCATION_ID := x_bill_to_location_id;
754 l_vendor_site_rec.CITY:= x_city ;
755 l_vendor_site_rec.COUNTRY := x_country ;
756 l_vendor_site_rec.COUNTY := x_county ;
757 l_vendor_site_rec.CUSTOMER_NUM := x_customer_num ;
758 l_vendor_site_rec.ECE_TP_LOCATION_CODE := x_ece_tp_location_code;
759 l_vendor_site_rec.MATCH_OPTION := x_fin_match_option ;
760 l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG := x_hold_all_payments_flag ;
761 l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG := x_hold_future_payments_flag ;
762 l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG := x_hold_unmatched_inv_flag ;
763 l_vendor_site_rec.INACTIVE_DATE := x_inactive_date ;
764 l_vendor_site_rec.INVOICE_CURRENCY_CODE := x_invoice_currency_code ;
765 l_vendor_site_rec.LANGUAGE := x_language ;
766 l_vendor_site_rec.LAST_UPDATE_DATE := x_last_update_date ;
767 l_vendor_site_rec.LAST_UPDATED_BY := x_last_updated_by ;
768 l_vendor_site_rec.ORG_ID := x_org_id ;
769 l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE := x_pay_date_basis_lookup_code;
770 l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE := x_pay_group_lookup_code ;
771 l_vendor_site_rec.PAY_SITE_FLAG := x_pay_site_flag ;
772 l_vendor_site_rec.PAYMENT_CURRENCY_CODE := x_payment_currency_code ;
773 l_vendor_site_rec.PAYMENT_PRIORITY := x_payment_priority ;
774 l_vendor_site_rec.PCARD_SITE_FLAG := x_pcard_site_flag ;
775 l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID := x_prepay_code_combination_id;
776 l_vendor_site_rec.PROVINCE := x_province ;
777 l_vendor_site_rec.PURCHASING_SITE_FLAG := x_purchasing_site_flag ;
778 l_vendor_site_rec.RFQ_ONLY_SITE_FLAG := x_rfq_only_site_flag ;
779 l_vendor_site_rec.SHIP_TO_LOCATION_ID := x_ship_to_location_id ;
780 l_vendor_site_rec.STATE := x_state ;
781 l_vendor_site_rec.TAX_REPORTING_SITE_FLAG := x_tax_reporting_site_flag;
782 l_vendor_site_rec.TERMS_DATE_BASIS := x_terms_date_basis ;
783 l_vendor_site_rec.TERMS_ID := x_terms_id ;
784 l_vendor_site_rec.VALIDATION_NUMBER := x_validation_number;
785 l_vendor_site_rec.VENDOR_ID := x_vendor_id ;
786 l_vendor_site_rec.VENDOR_SITE_CODE := x_vendor_site_code ;
787 l_vendor_site_rec.VENDOR_SITE_CODE_ALT := x_vendor_site_code_alt ;
788 l_vendor_site_rec.VENDOR_SITE_ID := x_vendor_site_id ;
789 l_vendor_site_rec.ZIP := x_zip ;
790
791
792
793 AP_VENDOR_PUB_PKG.Create_Vendor_Site
794 (p_api_version => 1.0,
795 p_init_msg_list => FND_API.G_TRUE,
796 p_commit =>FND_API.G_FALSE,
797 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
798 x_return_status => l_return_status,
799 x_msg_count => l_msg_count,
800 x_msg_data => l_msg_data,
801 p_vendor_site_rec => l_vendor_site_rec,
802 x_vendor_site_id => l_vendor_site_id,
803 x_party_site_id => l_party_site_id,
804 x_location_id => l_location_id);
805
806
807
808 OPEN C;
809 FETCH C INTO v_rowid;
810 IF (C%NOTFOUND) THEN
811 CLOSE C;
812 Raise NO_DATA_FOUND;
813 END IF;
814 CLOSE C;
815 END Site_Insert_Row;
816
817 /***************************************************************************************/
818 PROCEDURE Site_Update_Row(X_vendor_site_id IN number,
819 X_vendor_site_code VARCHAR2,
820 X_address_line1 VARCHAR2,
821 X_address_line2 VARCHAR2,
822 X_address_line3 VARCHAR2,
823 X_city VARCHAR2,
824 X_state VARCHAR2,
825 X_zip VARCHAR2,
826 X_province VARCHAR2,
827 X_country VARCHAR2,
828 X_inactive_date DATE,
829 X_org_id NUMBER,
830 X_address_line4 VARCHAR2,
831 X_county VARCHAR2,
832 X_address_style VARCHAR2,
833 X_language VARCHAR2,
834 X_vendor_site_code_alt VARCHAR2,
835 X_address_lines_alt VARCHAR2,
836 X_bank_charge_bearer VARCHAR2,
837 X_ece_tp_location_code VARCHAR2,
838 X_pay_site_flag VARCHAR2,
839 X_address_lines_phonetic VARCHAR2,
840 X_last_update_login NUMBER,
841 X_last_update_date DATE,
842 X_last_updated_by NUMBER) IS
843
844 l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
845
846
847 l_return_status varchar2(2000);
848 l_msg_count NUMBER;
849 l_msg_data VARCHAR2(2000);
850 l_vendor_site_id AP_SUPPLIERS.VENDOR_ID%TYPE;
851 l_party_site_id HZ_PARTY_SITES.party_site_id %TYPE;
852 l_location_id HZ_LOCATIONS.location_id%type;
853
854
855 l_location_rec hz_location_v2pub.location_rec_type;
856 l_object_version_number number;
857 cursor c_loc is select LOC.LOCATION_ID from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC,AP_SUPPLIER_SITES_ALL AP
858 WHERE PS.PARTY_SITE_ID = AP.PARTY_SITE_ID
859 AND LOC.LOCATION_ID = PS.LOCATION_ID
860 AND LOC.LOCATION_ID = AP.LOCATION_ID
861 AND AP.vendor_site_id = X_vendor_site_id
862 AND AP.ORG_ID = X_org_id;
863
864 cursor c_obj_version_loc (p_location_id hz_locations.location_id%type) is
865 select object_version_number
866 from hz_locations
867 where (location_id = p_location_id);
868
869
870
871 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
872 cursor c_party is select PS.party_site_id from HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, AP_SUPPLIER_SITES_ALL AP
873 WHERE PS.PARTY_SITE_ID = AP.PARTY_SITE_ID
874 AND AP.vendor_site_id = X_vendor_site_id
875 AND AP.ORG_ID = X_org_id;
876
877 cursor c_obj_version_site (p_party_site_id hz_party_sites.party_site_id%type) is
878 select object_version_number
879 from hz_party_sites
880 where (party_site_id = p_party_site_id);
881 BEGIN
882
883 OPEN C_LOC;
884 FETCH C_LOC INTO l_location_id;
885 IF (C_LOC%NOTFOUND) THEN
886 CLOSE C_LOC;
887 Raise NO_DATA_FOUND;
888 END IF;
889
890
891 CLOSE C_LOC;
892
893
894 l_location_rec.location_id := l_location_id;
895 l_location_rec.country := NVL(X_country,fnd_api.g_miss_char);
896 l_location_rec.address1 := NVL(X_address_line1,fnd_api.g_miss_char);
897 l_location_rec.address2 := NVL(X_address_line2,fnd_api.g_miss_char);
898 l_location_rec.address3 := NVL(X_address_line3,fnd_api.g_miss_char);
899 l_location_rec.address4 := NVL(X_address_line4,fnd_api.g_miss_char);
900 l_location_rec.city := NVL(x_city,fnd_api.g_miss_char);
901 l_location_rec.postal_code := NVL(X_zip,fnd_api.g_miss_char);
902 l_location_rec.state := NVL(x_state,fnd_api.g_miss_char);
903 l_location_rec.province := NVL(x_province,fnd_api.g_miss_char);
904 l_location_rec.county := NVL(x_county,fnd_api.g_miss_char);
905 l_location_rec.language := NVL(x_language,fnd_api.g_miss_char);
906 --l_location_rec.address_lines_phonetic := NVL(x_address_lines_phonetic,fnd_api.g_miss_char);
907
908
909 OPEN C_PARTY;
910 FETCH C_PARTY INTO l_party_site_id;
911 IF (C_PARTY%NOTFOUND) THEN
912 CLOSE C_PARTY;
913 Raise NO_DATA_FOUND;
914 END IF;
915 CLOSE C_PARTY;
916
917
918 l_party_site_rec.party_site_id := l_party_site_id;
919 select decode(X_pay_site_flag,'Y','A','I') into l_party_site_rec.status from dual;
920 --l_party_site_rec.created_by_module := 'AP_SUPPLIERS_API';
921
922
923 OPEN c_obj_version_loc (l_location_id);
924 FETCH c_obj_version_loc INTO l_object_version_number;
925 CLOSE c_obj_version_loc;
926
927
928 hz_location_v2pub.update_location
929 (
930 'T',
931 l_location_rec,
932 l_object_version_number,
933 l_return_status,
934 l_msg_count,
935 l_msg_data
936 );
937
938
939
940 commit;
941
942
943
944 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
945 RETURN;
946 END IF;
947
948 l_object_version_number := NULL;
949
950 OPEN c_obj_version_site (l_party_site_id);
951 FETCH c_obj_version_site INTO l_object_version_number;
952 CLOSE c_obj_version_site;
953
954
955 hz_party_site_v2pub.update_party_site
956 (
957 p_init_msg_list => fnd_api.g_false,
958 p_party_site_rec => l_party_site_rec,
959 p_object_version_number => l_object_version_number,
960 x_return_status => l_return_status,
961 x_msg_count => l_msg_count,
962 x_msg_data => l_msg_data
963 );
964
965
966
967 commit;
968
969
970
971 l_object_version_number := NULL;
972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
973 RETURN;
974 END IF;
975
976
977
978 /* l_vendor_site_rec.VENDOR_SITE_CODE := x_vendor_site_code ;
979 l_vendor_site_rec.ADDRESS_LINE1 := NVL(X_address_line1, fnd_api.g_miss_char);
980 l_vendor_site_rec.ADDRESS_LINE2 := NVL(X_address_line2, fnd_api.g_miss_char);
981 l_vendor_site_rec.ADDRESS_LINE3 := NVL(X_address_line3, fnd_api.g_miss_char);
982 l_vendor_site_rec.CITY:= NVL(x_city, fnd_api.g_miss_char);
983 l_vendor_site_rec.STATE := NVL(x_state, fnd_api.g_miss_char);
984 l_vendor_site_rec.ZIP := NVL(X_zip, fnd_api.g_miss_char);
985 l_vendor_site_rec.PROVINCE := NVL(x_state, fnd_api.g_miss_char);
986 l_vendor_site_rec.COUNTRY := x_country ;
987 l_vendor_site_rec.INACTIVE_DATE := x_inactive_date ;
988 l_vendor_site_rec.ORG_ID := X_org_id ;
989 l_vendor_site_rec.ADDRESS_LINE4 := NVL(X_address_line4, fnd_api.g_miss_char);
990 l_vendor_site_rec.COUNTY := NVL(x_county, fnd_api.g_miss_char);
991 l_vendor_site_rec.ADDRESS_STYLE := X_address_style;
992 l_vendor_site_rec.LANGUAGE := NVL(x_language, fnd_api.g_miss_char);
993 l_vendor_site_rec.VENDOR_SITE_CODE := x_vendor_site_code ;
994 l_vendor_site_rec.ADDRESS_LINES_ALT:= X_address_lines_alt;
995 l_vendor_site_rec.BANK_CHARGE_BEARER := x_bank_charge_bearer ;
996 l_vendor_site_rec.ECE_TP_LOCATION_CODE := x_ece_tp_location_code;
997 l_vendor_site_rec.PAY_SITE_FLAG := x_pay_site_flag ;
998 l_vendor_site_rec.LAST_UPDATE_DATE := x_last_update_date ;
999 l_vendor_site_rec.LAST_UPDATED_BY := x_last_updated_by ;
1000
1001
1002
1003
1004 AP_VENDOR_PUB_PKG.update_Vendor_Site
1005 (p_api_version => 1.0,
1006 p_init_msg_list => FND_API.G_TRUE,
1007 p_commit =>FND_API.G_FALSE,
1008 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1009 x_return_status => l_return_status,
1010 x_msg_count => l_msg_count,
1011 x_msg_data => l_msg_data,
1012 p_vendor_site_rec => l_vendor_site_rec,
1013 p_vendor_site_id => X_vendor_site_id
1014 ); */
1015
1016 AP_TCA_SUPPLIER_SYNC_PKG.SYNC_Supplier_Sites(l_return_status,
1017 l_msg_count,
1018 l_msg_data,
1019 l_location_id,
1020 l_party_site_id);
1021
1022
1023
1024 IF (SQL%NOTFOUND) THEN
1025 Raise NO_DATA_FOUND;
1026 END IF;
1027 END Site_Update_Row;
1028
1029 /***************************************************************************************/
1030 PROCEDURE Site_Lock_Row(X_vendor_site_id IN number,
1031 X_vendor_site_code VARCHAR2,
1032 X_address_line1 VARCHAR2,
1033 X_address_line2 VARCHAR2,
1034 X_address_line3 VARCHAR2,
1035 X_city VARCHAR2,
1036 X_state VARCHAR2,
1037 X_zip VARCHAR2,
1038 X_province VARCHAR2,
1039 X_country VARCHAR2,
1040 X_inactive_date DATE,
1041 X_org_id NUMBER,
1042 X_address_line4 VARCHAR2,
1043 X_county VARCHAR2,
1044 X_address_style VARCHAR2,
1045 X_language VARCHAR2,
1046 X_vendor_site_code_alt VARCHAR2,
1047 X_address_lines_alt VARCHAR2,
1048 X_bank_charge_bearer VARCHAR2,
1049 X_ece_tp_location_code VARCHAR2) IS
1050 CURSOR C IS
1051 SELECT *
1052 FROM ap_supplier_sites_all
1053 WHERE vendor_site_id = X_vendor_site_id
1054 and org_id = x_org_id
1055 FOR UPDATE OF vendor_site_id NOWAIT;
1056
1057 RecSite C%ROWTYPE;
1058
1059 BEGIN
1060 OPEN C;
1061 FETCH C INTO RecSite;
1062 IF (C%NOTFOUND) THEN
1063 CLOSE C;
1064 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1065 --bug 3199481 fnd logging changes: sdixit
1066 IF (l_excep_level >= l_debug_level ) THEN
1067 FND_LOG.MESSAGE (l_excep_level ,
1068 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Site_Lock_Row',FALSE);
1069 END IF;
1070 APP_EXCEPTION.RAISE_EXCEPTION;
1071 END IF;
1072 CLOSE C;
1073
1074 IF (
1075 (RecSite.vendor_site_code = X_vendor_site_code)
1076 AND ( (RecSite.address_line1 = X_address_line1)
1077 OR ( (RecSite.address_line1 IS NULL)
1078 AND (X_address_line1 IS NULL)))
1079 AND ( (RecSite.address_line2 = X_address_line2)
1080 OR ( (RecSite.address_line2 IS NULL)
1081 AND (X_address_line2 IS NULL)))
1082 AND ( (RecSite.address_line3 = X_address_line3)
1083 OR ( (RecSite.address_line3 IS NULL)
1084 AND (X_address_line3 IS NULL)))
1085 AND ( (RecSite.city = X_city)
1086 OR ( (RecSite.city IS NULL)
1087 AND (X_city IS NULL)))
1088 AND ( (RecSite.state = X_state)
1089 OR ( (RecSite.state IS NULL)
1090 AND (X_state IS NULL)))
1091 AND ( (RecSite.zip = X_zip)
1092 OR ( (RecSite.zip IS NULL)
1093 AND (X_zip IS NULL)))
1094 AND ( (RecSite.province = X_province)
1095 OR ( (RecSite.province IS NULL)
1096 AND (X_province IS NULL)))
1097 AND ( (RecSite.country = X_country)
1098 OR ( (RecSite.country IS NULL)
1099 AND (X_country IS NULL)))
1100 AND ( (RecSite.inactive_date = X_inactive_date)
1101 OR ( (RecSite.inactive_date IS NULL)
1102 AND (X_inactive_date IS NULL)))
1103 AND ( (RecSite.org_id = X_org_id)
1104 OR ( (RecSite.org_id IS NULL)
1105 AND (X_org_id IS NULL)))
1106 AND ( (RecSite.address_line4 = X_address_line4)
1107 OR ( (RecSite.address_line4 IS NULL)
1108 AND (X_address_line4 IS NULL)))
1109 AND ( (RecSite.county = X_county)
1110 OR ( (RecSite.county IS NULL)
1111 AND (X_county IS NULL)))
1112 AND ( (RecSite.address_style = X_address_style)
1113 OR ( (RecSite.address_style IS NULL)
1114 AND (X_address_style IS NULL)))
1115 AND ( (RecSite.language = X_language)
1116 OR ( (RecSite.language IS NULL)
1117 AND (X_language IS NULL)))
1118 AND ( (RecSite.vendor_site_code_alt = X_vendor_site_code_alt)
1119 OR ( (RecSite.vendor_site_code_alt IS NULL)
1120 AND (X_vendor_site_code_alt IS NULL)))
1121 AND ( (RecSite.address_lines_alt = X_address_lines_alt)
1122 OR ( (RecSite.address_lines_alt IS NULL)
1123 AND (X_address_lines_alt IS NULL)))
1124 AND ( (RecSite.bank_charge_bearer = X_bank_charge_bearer)
1125 OR ( (RecSite.bank_charge_bearer IS NULL)
1126 AND (X_bank_charge_bearer IS NULL)))
1127 AND ( (RecSite.ece_tp_location_code = X_ece_tp_location_code)
1128 OR ( (RecSite.ece_tp_location_code IS NULL)
1129 AND (X_ece_tp_location_code IS NULL)))
1130 ) THEN
1131 return;
1132 ELSE
1133 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1134 --bug 3199481 fnd logging changes: sdixit
1135 IF (l_excep_level >= l_debug_level ) THEN
1136 FND_LOG.MESSAGE (l_excep_level ,
1137 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Site_Lock_Row',
1138 FALSE);
1139 END IF;
1140 APP_EXCEPTION.Raise_Exception;
1141 END IF;
1142
1143 END Site_Lock_Row;
1144
1145 /***************************************************************************************/
1146 PROCEDURE Customer_Insert_Row( X_rowid IN OUT NOCOPY VARCHAR2,
1147 X_customer_id NUMBER,
1148 X_customer_name VARCHAR2,
1149 X_customer_number OUT NOCOPY VARCHAR2,
1150 X_customer_key VARCHAR2,
1151 X_status VARCHAR2,
1152 X_stp_enforce_threshold VARCHAR2,
1153 X_orig_system_reference VARCHAR2,
1154 X_customer_prospect_code VARCHAR2,
1155 X_customer_type VARCHAR2,
1156 X_tax_reference VARCHAR2,
1157 X_gsa_indicator VARCHAR2,
1158 X_jgzz_fiscal_code VARCHAR2,
1159 X_warehouse_id NUMBER,
1160 X_competitor_flag VARCHAR2,
1161 X_reference_use_flag VARCHAR2,
1162 X_third_party_flag VARCHAR2,
1163 X_customer_name_phonetic VARCHAR2,
1164 X_stp_type VARCHAR2,
1165 X_creation_date DATE,
1166 X_created_by NUMBER,
1167 X_last_update_login NUMBER,
1168 X_last_update_date DATE,
1169 X_last_updated_by NUMBER,
1170 X_party_id NUMBER,
1171 X_party_number VARCHAR2,
1172 X_party_type VARCHAR2,
1173 X_account_replication_key NUMBER ) IS
1174
1175 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
1176 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
1177 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1178 l_return_status VARCHAR2(2000);
1179 l_msg_count NUMBER;
1180 l_msg_data VARCHAR2(2000);
1181 l_party_id NUMBER ;
1182 l_party_number VARCHAR2(2000);
1183 l_profile_id NUMBER;
1184
1185 l_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
1186 l_account_number NUMBER;
1187 l_cust_account_id number;
1188
1189 c_party_id number(15);
1190 c_customer_id number(15);
1191
1192 CURSOR C IS SELECT party_id FROM hz_parties
1193 WHERE party_id = X_party_id;
1194 CURSOR C1 IS SELECT rowid FROM hz_cust_accounts
1195 WHERE cust_account_id = X_customer_id;
1196 CURSOR C2 IS SELECT customer_id FROM igi_ra_customers
1197 WHERE customer_id = X_customer_id;
1198
1199 CURSOR c_gen_cust_num IS
1200 SELECT generate_customer_number
1201 FROM ar_system_parameters;
1202
1203 l_gen_cust_num varchar2(1);
1204 l_profile_value varchar2(100); -- For capturing the value returned by fnd_profile.get
1205 BEGIN
1206 --fnd_log.string(1,'IGI_STP','In Customer Insert');
1207
1208 l_party_rec.party_id := X_party_id;
1209
1210
1211 FND_PROFILE.GET('HZ_GENERATE_PARTY_NUMBER', l_profile_value);
1212
1213 IF (l_profile_value = 'N') THEN
1214 l_party_rec.party_number := X_party_number;
1215 ELSE
1216 l_party_rec.party_number := NULL;
1217 END IF;
1218
1219 l_party_rec.orig_system_reference := X_orig_system_reference;
1220 l_party_rec.status := X_status;
1221 l_organization_rec.organization_name := X_customer_name;
1222 l_organization_rec.tax_reference := X_tax_reference;
1223 l_organization_rec.jgzz_fiscal_code := X_jgzz_fiscal_code;
1224 l_organization_rec.content_source_type := 'USER_ENTERED';
1225 l_organization_rec.created_by_module := 'IGI_STP';
1226 l_organization_rec.party_rec := l_party_rec;
1227
1228 l_cust_account_rec.cust_account_id := X_customer_id;
1229
1230 OPEN c_gen_cust_num;
1231 FETCH c_gen_cust_num INTO l_gen_cust_num;
1232 IF (l_gen_cust_num <> 'Y') THEN
1233 SELECT hz_cust_accounts_s.nextval INTO X_Customer_Number
1234 FROM dual;
1235 END IF;
1236 CLOSE c_gen_cust_num;
1237
1238 l_cust_account_rec.account_number := X_customer_number; /* Please check */
1239 l_cust_account_rec.status := X_status;
1240 l_cust_account_rec.orig_system_reference := X_orig_system_reference;
1241 l_cust_account_rec.customer_type := X_customer_type;
1242 --
1243 -- Bug 2918737 Start
1244 --
1245 -- l_cust_account_rec.warehouse_id := X_warehouse_id;
1246 --
1247 -- Bug 2918737 End
1248 --
1249 l_cust_account_rec.created_by_module := 'IGI_STP';
1250
1251
1252 hz_party_v2pub.create_organization
1253 (
1254 p_init_msg_list => fnd_api.g_true,
1255 p_organization_rec => l_organization_rec,
1256 x_return_status => l_return_status,
1257 x_msg_count => l_msg_count,
1258 x_msg_data => l_msg_data,
1259 x_party_id => l_party_id,
1260 x_party_number => l_party_number,
1261 x_profile_id => l_profile_id
1262 );
1263
1264
1265
1266
1267
1268 OPEN C;
1269 FETCH C INTO c_party_id;
1270 IF (C%NOTFOUND) THEN
1271 CLOSE C;
1272 Raise NO_DATA_FOUND;
1273 END IF;
1274 CLOSE C;
1275
1276
1277 hz_cust_account_v2pub.create_cust_account
1278 (
1279 p_init_msg_list => fnd_api.g_true,
1280 p_cust_account_rec => l_cust_account_rec,
1281 p_organization_rec => l_organization_rec,
1282 p_customer_profile_rec => l_customer_profile_rec,
1283 p_create_profile_amt => fnd_api.g_true,
1284 x_cust_account_id => l_cust_account_id,
1285 x_account_number => l_account_number,
1286 x_party_id => l_party_id,
1287 x_party_number => l_party_number,
1288 x_profile_id => l_profile_id,
1289 x_return_status => l_return_status,
1290 x_msg_count => l_msg_count,
1291 x_msg_data => l_msg_data
1292 );
1293
1294 X_Customer_Number := l_account_number; -- Assign the account number returned to the OUT variable X_Customer_Number so that the same can be reflected in the form
1295
1296 OPEN C1;
1297 FETCH C1 INTO X_ROWID;
1298 IF (C1%NOTFOUND) THEN
1299 CLOSE C1;
1300 Raise NO_DATA_FOUND;
1301 END IF;
1302 CLOSE C1;
1303
1304
1305
1306 INSERT INTO igi_ra_customers(
1307 customer_id,
1308 stp_enforce_threshold,
1309 stp_type,
1310 creation_date,
1311 created_by,
1312 last_update_login,
1313 last_update_date,
1314 last_updated_by)
1315 VALUES(
1316 X_customer_id,
1317 X_stp_enforce_threshold,
1318 X_stp_type,
1319 X_creation_date,
1320 X_created_by,
1321 X_last_update_login,
1322 X_last_update_date,
1323 X_last_updated_by);
1324
1325 OPEN C2;
1326 FETCH C2 INTO c_customer_id;
1327 IF (C2%NOTFOUND) THEN
1328 CLOSE C2;
1329 Raise NO_DATA_FOUND;
1330 END IF;
1331 CLOSE C2;
1332
1333
1334
1335 /*Included the following Insert Statement for Bug 2450283*/
1336 /*The Standard Customer Form(AR) expects a record in hz_organization_profiles.
1337 So including this insert statement here. The situation arises when STP customer
1338 is queried in the Core AR Customer Standard Form and modifications are made and saved.*/
1339 /*
1340 INSERT into hz_organization_profiles
1341 (ORGANIZATION_PROFILE_ID,
1342 PARTY_ID,
1343 ORGANIZATION_NAME,
1344 LAST_UPDATE_DATE,
1345 LAST_UPDATED_BY,
1346 CREATION_DATE,
1347 CREATED_BY,
1348 CONTENT_SOURCE_TYPE,
1349 EFFECTIVE_START_DATE)
1350 VALUES
1351 (hz_organization_profiles_s.nextval,
1352 X_party_id,
1353 X_customer_name,
1354 X_last_update_date,
1355 X_last_updated_by,
1356 X_creation_date,
1357 X_created_by,
1358 'USER_ENTERED',
1359 trunc(sysdate));
1360 */
1361 END Customer_Insert_Row;
1362 /***************************************************************************************/
1363 PROCEDURE Customer_Update_Row (X_rowid IN OUT NOCOPY VARCHAR2,
1364 X_customer_name VARCHAR2,
1365 X_customer_number VARCHAR2,
1366 X_customer_key VARCHAR2,
1367 X_status VARCHAR2,
1368 X_stp_enforce_threshold VARCHAR2,
1369 X_orig_system_reference VARCHAR2,
1370 X_tax_reference VARCHAR2,
1371 X_jgzz_fiscal_code VARCHAR2,
1372 X_warehouse_id NUMBER,
1373 X_customer_name_phonetic VARCHAR2,
1374 X_stp_type VARCHAR2,
1375 X_last_update_login NUMBER,
1376 X_last_update_date DATE,
1377 X_last_updated_by NUMBER ) IS
1378
1379 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
1380 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
1381 l_return_status VARCHAR2(2000);
1382 l_msg_count NUMBER;
1383 l_msg_data VARCHAR2(2000);
1384 l_party_id NUMBER;
1385 l_party_number VARCHAR2(2000);
1386 l_profile_id NUMBER;
1387
1388
1389
1390 l_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
1391 l_object_version_number NUMBER;
1392 l_cust_account_id NUMBER;
1393
1394 --Bug 3902175
1395 l_object_cust_version_number NUMBER;
1396
1397 CURSOR c_all IS
1398 SELECT nvl(a.object_version_number,fnd_api.G_NULL_NUM),a.party_id,a.cust_account_id,nvl(p.object_version_number,fnd_api.G_NULL_NUM)
1399 FROM hz_cust_accounts a,hz_parties p
1400 WHERE a.party_id = p.party_id
1401 AND a.rowid = X_rowid;
1402
1403 BEGIN
1404
1405
1406 OPEN C_ALL;
1407 FETCH C_ALL INTO l_object_cust_version_number,l_party_id,l_cust_account_id,l_object_version_number;
1408 IF (C_ALL%NOTFOUND) THEN
1409 RAISE NO_DATA_FOUND;
1410 CLOSE C_ALL;
1411 END IF;
1412 CLOSE C_ALL;
1413
1414
1415 l_party_rec.party_id := l_party_id;
1416 l_party_rec.orig_system_reference := nvl(X_orig_system_reference,fnd_api.G_NULL_CHAR);
1417 l_party_rec.status := nvl(X_status,fnd_api.G_NULL_CHAR);
1418
1419 l_organization_rec.organization_name := nvl(X_customer_name,fnd_api.G_NULL_CHAR);
1420 l_organization_rec.tax_reference := nvl(X_tax_reference,fnd_api.G_NULL_CHAR);
1421 l_organization_rec.jgzz_fiscal_code := nvl(X_jgzz_fiscal_code,fnd_api.G_NULL_CHAR);
1422 l_organization_rec.content_source_type := 'USER_ENTERED';
1423 l_organization_rec.party_rec := l_party_rec;
1424
1425
1426 l_cust_account_rec.cust_account_id := l_cust_account_id;
1427 l_cust_account_rec.status := nvl(X_status,fnd_api.G_NULL_CHAR);
1428
1429
1430 hz_party_v2pub.update_organization
1431 (
1432 p_init_msg_list => fnd_api.g_false,
1433 p_organization_rec => l_organization_rec,
1434 p_party_object_version_number => l_object_version_number,
1435 x_profile_id => l_profile_id,
1436 x_return_status => l_return_status,
1437 x_msg_count => l_msg_count,
1438 x_msg_data => l_msg_data
1439 );
1440 l_object_version_number := NULL;
1441
1442
1443 hz_cust_account_v2pub.update_cust_account
1444 (
1445 p_init_msg_list => fnd_api.g_false,
1446 p_cust_account_rec => l_cust_account_rec,
1447 p_object_version_number => l_object_cust_version_number,
1448 x_return_status => l_return_status,
1449 x_msg_count => l_msg_count,
1450 x_msg_data => l_msg_data
1451 );
1452
1453 UPDATE igi_ra_customers irc
1454 SET stp_enforce_threshold = X_stp_enforce_threshold,
1455 stp_type = X_stp_type,
1456 last_update_login = X_last_update_login,
1457 last_update_date = X_last_update_date,
1458 last_updated_by = X_last_updated_by
1459 WHERE irc.customer_id = (select a.cust_account_id from hz_cust_accounts a,hz_parties p
1460 where a.party_id = p.party_id and a.rowid = X_rowid); -- Bug 3902175
1461
1462 IF (SQL%NOTFOUND) THEN
1463 Raise NO_DATA_FOUND;
1464 END IF;
1465 END Customer_Update_Row;
1466
1467 /***************************************************************************************/
1468 PROCEDURE Customer_Lock_Row( X_rowid IN OUT NOCOPY VARCHAR2,
1469 X_customer_name VARCHAR2,
1470 X_customer_number VARCHAR2,
1471 X_customer_key VARCHAR2,
1472 X_status VARCHAR2,
1473 X_stp_enforce_threshold VARCHAR2,
1474 X_orig_system_reference VARCHAR2,
1475 X_tax_reference VARCHAR2,
1476 X_jgzz_fiscal_code VARCHAR2,
1477 X_warehouse_id NUMBER,
1478 X_customer_name_phonetic VARCHAR2,
1479 X_stp_type VARCHAR2 ) IS
1480
1481 CURSOR C IS
1482 SELECT *
1483 FROM igi_ar_customers_v --bug3514922 sdixit
1484 WHERE row_id = X_rowid
1485 FOR UPDATE OF customer_id NOWAIT;
1486
1487 RecCust C%ROWTYPE;
1488
1489 CURSOR C1 IS
1490 SELECT *
1491 FROM igi_ra_customers irc
1492 WHERE irc.customer_id = (select a.cust_account_id from hz_cust_accounts a,hz_parties p -- Bug 3902175
1493 where a.party_id = p.party_id and a.rowid = X_rowid)
1494 FOR UPDATE OF irc.customer_id NOWAIT;
1495
1496 RecCust1 C1%ROWTYPE;
1497
1498 BEGIN
1499
1500 OPEN C;
1501 FETCH C INTO RecCust;
1502 IF (C%NOTFOUND) THEN
1503 CLOSE C;
1504 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1505 --bug 3199481 fnd logging changes: sdixit
1506 IF (l_excep_level >= l_debug_level ) THEN
1507 FND_LOG.MESSAGE (l_excep_level ,
1508 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Customer_Lock_Row',
1509 FALSE);
1510 END IF;
1511 APP_EXCEPTION.RAISE_EXCEPTION;
1512 END IF;
1513 CLOSE C;
1514
1515 OPEN C1;
1516 FETCH C1 INTO RecCust1;
1517 IF (C1%NOTFOUND) THEN
1518 CLOSE C1;
1519 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1520 --bug 3199481 fnd logging changes: sdixit
1521 IF (l_excep_level >= l_debug_level ) THEN
1522 FND_LOG.MESSAGE (l_excep_level ,
1523 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Customer_Lock_Row',
1524 FALSE);
1525 END IF;
1526 APP_EXCEPTION.RAISE_EXCEPTION;
1527 END IF;
1528 CLOSE C1;
1529
1530 IF (
1531 (RecCust.customer_name = X_customer_name)
1532 AND (RecCust.customer_number = X_customer_number)
1533 AND ( (RecCust.customer_key = X_customer_key)
1534 OR ( (RecCust.customer_key IS NULL)
1535 AND (X_customer_key IS NULL)))
1536 AND (RecCust.status = X_status)
1537 AND ( (RecCust1.stp_enforce_threshold = X_stp_enforce_threshold)
1538 OR ( (RecCust1.stp_enforce_threshold IS NULL)
1539 AND (X_stp_enforce_threshold IS NULL)))
1540 AND (RecCust.orig_system_reference = X_orig_system_reference)
1541 AND ( (RecCust.tax_reference = X_tax_reference)
1542 OR ( (RecCust.tax_reference IS NULL)
1543 AND (X_tax_reference IS NULL)))
1544 AND ( (RecCust.jgzz_fiscal_code = X_jgzz_fiscal_code)
1545 OR ( (RecCust.jgzz_fiscal_code IS NULL)
1546 AND (X_jgzz_fiscal_code IS NULL)))
1547 -- AND ( (RecCust.warehouse_id = X_warehouse_id) -- Bug 3902175 commented because Lock issue occurs
1548 -- OR ( (RecCust.warehouse_id IS NULL)
1549 -- AND (X_warehouse_id IS NULL)))
1550 -- AND ( (RecCust.customer_name_phonetic = X_customer_name_phonetic)
1551 -- OR ( (RecCust.customer_name_phonetic IS NULL)
1552 -- AND (X_customer_name_phonetic IS NULL)))
1553 AND ( (RecCust1.stp_type = X_stp_type)
1554 OR ( (RecCust1.stp_type IS NULL)
1555 AND (X_stp_type IS NULL)))
1556 ) THEN
1557 return;
1558 ELSE
1559
1560 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1561 --bug 3199481 fnd logging changes: sdixit
1562 IF (l_excep_level >= l_debug_level ) THEN
1563 FND_LOG.MESSAGE (l_excep_level ,
1564 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Customer_Lock_Row',
1565 FALSE);
1566 END IF;
1567 APP_EXCEPTION.Raise_Exception;
1568 END IF;
1569
1570 END Customer_Lock_Row;
1571
1572 /***************************************************************************************/
1573 PROCEDURE Supplier_Insert_Row( X_rowid IN VARCHAR2,
1574 X_vendor_id NUMBER,
1575 X_vendor_name VARCHAR2,
1576 X_segment1 VARCHAR2,
1577 X_stp_enforce_threshold VARCHAR2,
1578 X_summary_flag VARCHAR2,
1579 X_enabled_flag VARCHAR2,
1580 X_one_time_flag VARCHAR2,
1581 X_payment_priority NUMBER,
1582 X_num_1099 VARCHAR2,
1583 X_start_date_active DATE,
1584 X_end_date_active DATE,
1585 X_women_owned_flag VARCHAR2,
1586 X_small_business_flag VARCHAR2,
1587 X_hold_flag VARCHAR2,
1588 X_federal_reportable_flag VARCHAR2,
1589 X_vat_registration_num VARCHAR2,
1590 X_vendor_name_alt VARCHAR2,
1591 X_auto_tax_calc_flag VARCHAR2,
1592 X_auto_tax_calc_override VARCHAR2,
1593 X_ap_tax_rounding_rule VARCHAR2,
1594 X_bank_charge_bearer VARCHAR2,
1595 X_state_reportable_flag VARCHAR2,
1596 X_amount_includes_tax_flag VARCHAR2,
1597 X_hold_all_payments_flag VARCHAR2,
1598 X_hold_future_payments_flag VARCHAR2,
1599 X_always_take_disc_flag VARCHAR2,
1600 X_excl_freight_from_discount VARCHAR2,
1601 X_auto_calc_interest_flag VARCHAR2,
1602 X_invoice_currency_code VARCHAR2,
1603 X_payment_currency_code VARCHAR2,
1604 X_exclusive_payment_flag VARCHAR2,
1605 X_terms_date_basis VARCHAR2,
1606 X_pay_date_basis_lookup_code VARCHAR2,
1607 X_payment_method_lookup_code VARCHAR2,
1608 X_enforce_ship_to_loc_code VARCHAR2,
1609 X_qty_rcv_tolerance NUMBER,
1610 X_qty_rcv_exception_code VARCHAR2,
1611 X_days_early_receipt_allowed NUMBER,
1612 X_allow_subst_receipts_flag VARCHAR2,
1613 X_days_late_receipt_allowed NUMBER,
1614 X_allow_unord_receipts_flag VARCHAR2,
1615 X_receipt_days_exception_code VARCHAR2,
1616 X_allow_awt_flag VARCHAR2,
1617 X_bill_to_location_id NUMBER,
1618 X_receiving_routing_id NUMBER,
1619 X_ship_to_location_id NUMBER,
1620 X_pay_group_lookup_code VARCHAR2,
1621 X_terms_id NUMBER,
1622 X_set_of_books_id NUMBER,
1623 X_inspection_required_flag VARCHAR2,
1624 X_receipt_required_flag VARCHAR2,
1625 X_creation_date DATE,
1626 X_created_by NUMBER,
1627 X_last_update_login NUMBER,
1628 X_last_update_date DATE,
1629 X_last_updated_by NUMBER,
1630 X_future_dated_payment_ccid NUMBER,
1631 X_fin_match_option VARCHAR2,
1632 X_po_create_dm_flag VARCHAR2,
1633 X_customer_id NUMBER ) IS
1634
1635 l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
1636 l_return_status varchar2(2000);
1637 l_msg_count NUMBER;
1638 l_msg_data VARCHAR2(2000);
1639 l_vendor_id AP_SUPPLIERS.VENDOR_ID%TYPE;
1640 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
1641
1642
1643
1644 CURSOR C IS SELECT rowid FROM AP_SUPPLIERS
1645 WHERE vendor_id = l_vendor_id;
1646 CURSOR C1 IS SELECT vendor_id FROM igi_po_vendors
1647 WHERE vendor_id = l_vendor_id;
1648 c1_vendor_id number(15);
1649
1650 v_rowid VARCHAR2(25);
1651
1652 BEGIN
1653
1654
1655
1656 l_vendor_rec.VENDOR_ID := X_vendor_id;
1657 l_vendor_rec.SEGMENT1 := X_segment1;
1658 l_vendor_rec.VENDOR_NAME := X_vendor_name;
1659 l_vendor_rec.ALLOW_AWT_FLAG := X_allow_awt_flag;
1660 l_vendor_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG := X_allow_subst_receipts_flag;
1661 l_vendor_rec.ALLOW_UNORDERED_RECEIPTS_FLAG := X_allow_unord_receipts_flag;
1662 l_vendor_rec.ALWAYS_TAKE_DISC_FLAG := X_always_take_disc_flag ;
1663 l_vendor_rec.AUTO_CALCULATE_INTEREST_FLAG := X_auto_calc_interest_flag;
1664 l_vendor_rec.BANK_CHARGE_BEARER := X_bank_charge_bearer;
1665 l_vendor_rec.DAYS_EARLY_RECEIPT_ALLOWED := X_days_early_receipt_allowed;
1666 l_vendor_rec.DAYS_LATE_RECEIPT_ALLOWED := X_days_late_receipt_allowed ;
1667 l_vendor_rec.ENABLED_FLAG := X_enabled_flag;
1668 l_vendor_rec.END_DATE_ACTIVE := X_end_date_active;
1669 l_vendor_rec.ENFORCE_SHIP_TO_LOCATION_CODE := X_enforce_ship_to_loc_code;
1670 l_vendor_rec.EXCLUDE_FREIGHT_FROM_DISCOUNT := X_excl_freight_from_discount;
1671 l_vendor_rec.FEDERAL_REPORTABLE_FLAG := X_federal_reportable_flag;
1672 l_vendor_rec.HOLD_ALL_PAYMENTS_FLAG := X_hold_all_payments_flag;
1673 l_vendor_rec.HOLD_FLAG := X_hold_flag;
1674 l_vendor_rec.HOLD_FUTURE_PAYMENTS_FLAG := X_hold_future_payments_flag;
1675 l_vendor_rec.INSPECTION_REQUIRED_FLAG := X_inspection_required_flag;
1676 l_vendor_rec.INVOICE_CURRENCY_CODE := X_invoice_currency_code;
1677 l_vendor_rec.JGZZ_FISCAL_CODE := X_num_1099 ;
1678 l_vendor_rec.ONE_TIME_FLAG := X_one_time_flag;
1679 l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE := X_pay_date_basis_lookup_code;
1680 l_vendor_rec.PAY_GROUP_LOOKUP_CODE := X_pay_group_lookup_code;
1681 l_vendor_rec.PAYMENT_CURRENCY_CODE := X_payment_currency_code;
1682 l_vendor_rec.PAYMENT_PRIORITY := X_payment_priority;
1683 l_vendor_rec.QTY_RCV_EXCEPTION_CODE := X_qty_rcv_exception_code;
1684 l_vendor_rec.QTY_RCV_TOLERANCE := X_qty_rcv_tolerance;
1685 l_vendor_rec.RECEIPT_DAYS_EXCEPTION_CODE := X_receipt_days_exception_code;
1686 l_vendor_rec.RECEIPT_REQUIRED_FLAG := X_receipt_required_flag ;
1687 l_vendor_rec.RECEIVING_ROUTING_ID := X_receiving_routing_id;
1688 l_vendor_rec.SEGMENT1 := X_segment1;
1689 l_vendor_rec.SET_OF_BOOKS_ID := X_set_of_books_id;
1690 l_vendor_rec.SMALL_BUSINESS_FLAG := X_small_business_flag;
1691 l_vendor_rec.START_DATE_ACTIVE := X_start_date_active;
1692 l_vendor_rec.STATE_REPORTABLE_FLAG := X_state_reportable_flag;
1693 l_vendor_rec.SUMMARY_FLAG := X_summary_flag;
1694 l_vendor_rec.TAX_REFERENCE := X_vat_registration_num;
1695 l_vendor_rec.TERMS_DATE_BASIS := X_terms_date_basis;
1696 l_vendor_rec.TERMS_ID := X_terms_id;
1697 l_vendor_rec.VENDOR_ID := X_vendor_id;
1698 l_vendor_rec.VENDOR_NAME := X_vendor_name;
1699 l_vendor_rec.VENDOR_NAME_ALT := X_vendor_name_alt;
1700 l_vendor_rec.WOMEN_OWNED_FLAG := X_women_owned_flag;
1701
1702
1703
1704 ap_vendor_pub_pkg.create_vendor
1705 (p_init_msg_list => fnd_api.g_true,
1706 p_api_version => 1.0,
1707 p_commit => FND_API.G_FALSE,
1708 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1709 p_vendor_rec => l_vendor_rec,
1710 x_return_status => l_return_status,
1711 x_msg_count => l_msg_count,
1712 x_msg_data => l_msg_data,
1713 x_vendor_id => l_vendor_id,
1714 x_party_id => l_party_id);
1715
1716
1717
1718
1719
1720 OPEN C;
1721 FETCH C INTO v_rowid;
1722 IF (C%NOTFOUND) THEN
1723 CLOSE C;
1724 Raise NO_DATA_FOUND;
1725 END IF;
1726 CLOSE C;
1727
1728 INSERT INTO igi_po_vendors(
1729 vendor_id,
1730 stp_enforce_threshold,
1731 customer_id,
1732 creation_date,
1733 created_by,
1734 last_update_login,
1735 last_update_date,
1736 last_updated_by)
1737 VALUES(
1738 l_vendor_id,
1739 X_stp_enforce_threshold,
1740 X_customer_id,
1741 X_creation_date,
1742 X_created_by,
1743 X_last_update_login,
1744 X_last_update_date,
1745 X_last_updated_by);
1746 OPEN C1;
1747 FETCH C1 INTO c1_vendor_id;
1748 IF (C1%NOTFOUND) THEN
1749 CLOSE C1;
1750 Raise NO_DATA_FOUND;
1751 END IF;
1752 CLOSE C1;
1753 END Supplier_Insert_Row;
1754
1755 /***************************************************************************************/
1756 PROCEDURE Supplier_Update_Row(X_rowid IN VARCHAR2,
1757 X_vendor_name VARCHAR2,
1758 X_num_1099 VARCHAR2,
1759 X_vat_registration_num VARCHAR2,
1760 X_VENDOR_ID NUMBER,
1761 X_vendor_name_alt VARCHAR2,
1762 X_end_date_active DATE,
1763 X_status VARCHAR2,
1764 X_orig_system_reference VARCHAR2,
1765 X_last_update_login NUMBER,
1766 X_last_update_date DATE,
1767 X_stp_enforce_threshold VARCHAR2,
1768 X_last_updated_by NUMBER
1769 ) IS
1770
1771 l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
1772 l_return_status varchar2(2000);
1773 l_msg_count NUMBER;
1774 l_msg_data VARCHAR2(2000);
1775 l_vendor_id AP_SUPPLIERS.VENDOR_ID%TYPE;
1776 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
1777 l_object_version_number NUMBER;
1778 l_profile_id NUMBER;
1779
1780 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
1781 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
1782 BEGIN
1783
1784 select party_id,nvl(object_version_number,fnd_api.G_NULL_NUM) into l_party_id,l_object_version_number from hz_parties where party_name = X_vendor_name and created_by_module = 'AP_SUPPLIERS_API';
1785
1786 l_party_rec.party_id := l_party_id;
1787 --l_party_rec.orig_system_reference := nvl(X_orig_system_reference,fnd_api.G_NULL_CHAR);
1788 l_party_rec.status := nvl(X_status,fnd_api.G_NULL_CHAR);
1789
1790
1791 l_organization_rec.organization_name := nvl(X_vendor_name,fnd_api.G_NULL_CHAR);
1792 l_organization_rec.tax_reference := nvl(X_vat_registration_num,fnd_api.G_NULL_CHAR);
1793 l_organization_rec.jgzz_fiscal_code := nvl(X_num_1099,fnd_api.G_NULL_CHAR);
1794 l_organization_rec.content_source_type := 'USER_ENTERED';
1795 l_organization_rec.party_rec := l_party_rec;
1796
1797
1798 hz_party_v2pub.update_organization
1799 (
1800 p_init_msg_list => fnd_api.g_false,
1801 p_organization_rec => l_organization_rec,
1802 p_party_object_version_number => l_object_version_number,
1803 x_profile_id => l_profile_id,
1804 x_return_status => l_return_status,
1805 x_msg_count => l_msg_count,
1806 x_msg_data => l_msg_data
1807 );
1808
1809 AP_TCA_SUPPLIER_SYNC_PKG.SYNC_Supplier(l_return_status,
1810 l_msg_count,
1811 l_msg_data,
1812 l_party_id);
1813
1814
1815
1816 /* l_vendor_rec.VENDOR_NAME := X_vendor_name;
1817 l_vendor_rec.JGZZ_FISCAL_CODE := nvl(X_num_1099,fnd_api.G_NULL_CHAR);
1818 l_vendor_rec.TAX_REFERENCE := nvl(X_vat_registration_num,fnd_api.G_NULL_CHAR);
1819 l_vendor_rec.VENDOR_NAME_ALT := X_vendor_name_alt;
1820 l_vendor_rec.END_DATE_ACTIVE := X_end_date_active;
1821
1822
1823
1824
1825 AP_VENDOR_PUB_PKG.Update_Vendor(
1826 p_api_version => 1.0,
1827 p_init_msg_list => FND_API.G_TRUE,
1828 p_commit => FND_API.G_FALSE,
1829 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1830 x_return_status => l_return_status,
1831 x_msg_count => l_msg_count,
1832 x_msg_data => l_msg_data,
1833 p_vendor_rec => l_vendor_rec,
1834 p_vendor_id => X_VENDOR_ID); */
1835
1836
1837
1838
1839 UPDATE igi_po_vendors ipv
1840 SET ipv.stp_enforce_threshold = X_stp_enforce_threshold
1841 WHERE ipv.vendor_id = (select pv.vendor_id
1842 from AP_SUPPLIERS pv
1843 where pv.rowid = X_rowid);
1844
1845 END Supplier_Update_Row;
1846
1847 /***************************************************************************************/
1848 PROCEDURE Supplier_Lock_Row( X_rowid IN VARCHAR2,
1849 X_vendor_name VARCHAR2,
1850 X_num_1099 VARCHAR2,
1851 X_vat_registration_num VARCHAR2,
1852 X_vendor_name_alt VARCHAR2,
1853 X_stp_enforce_threshold VARCHAR2,
1854 X_end_date_active DATE) IS
1855
1856 CURSOR C IS
1857 SELECT *
1858 FROM ap_suppliers
1859 WHERE rowid = X_rowid
1860 FOR UPDATE OF vendor_id NOWAIT;
1861
1862 RecSupp C%ROWTYPE;
1863 CURSOR C1 IS
1864 SELECT *
1865 FROM igi_po_vendors ipv
1866 WHERE ipv.vendor_id = (select pv.vendor_id
1867 from ap_suppliers pv
1868 where pv.rowid = X_rowid )
1869 FOR UPDATE OF ipv.vendor_id NOWAIT;
1870 RecSupp1 C1%ROWTYPE;
1871
1872 BEGIN
1873 OPEN C;
1874 FETCH C INTO RecSupp;
1875 IF (C%NOTFOUND) THEN
1876 CLOSE C;
1877 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1878 --bug 3199481 fnd logging changes: sdixit
1879 IF (l_excep_level >= l_debug_level ) THEN
1880 FND_LOG.MESSAGE (l_excep_level ,
1881 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Supplier_Lock_Row.msg1',
1882 FALSE);
1883 END IF;
1884 APP_EXCEPTION.RAISE_EXCEPTION;
1885 END IF;
1886 CLOSE C;
1887 OPEN C1;
1888 FETCH C1 INTO RecSupp1;
1889 IF (C1%NOTFOUND) THEN
1890 CLOSE C1;
1891 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1892 --bug 3199481 fnd logging changes: sdixit
1893 IF (l_excep_level >= l_debug_level ) THEN
1894 FND_LOG.MESSAGE (l_excep_level ,
1895 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Supplier_Lock_Row.msg2',
1896 FALSE);
1897 END IF;
1898 APP_EXCEPTION.RAISE_EXCEPTION;
1899 END IF;
1900 CLOSE C1;
1901
1902 -- Bug 3079883 (Tpradhan)
1903 -- Modified the equality of vendor name below to compare it with only the first 50 characters
1904 -- obtained from po_vendors for Bug 3079883.
1905 --bug 3519422 sdixit
1906 --reverting above fix as now customer name is 360 characters
1907
1908 IF (
1909 (RecSupp.vendor_name = X_vendor_name)
1910 AND ( (RecSupp.num_1099 = X_num_1099)
1911 OR ( (RecSupp.num_1099 IS NULL)
1912 AND (X_num_1099 IS NULL)))
1913 AND ( (RecSupp.vat_registration_num = X_vat_registration_num)
1914 OR ( (RecSupp.vat_registration_num IS NULL)
1915 AND (X_vat_registration_num IS NULL)))
1916 AND ( (RecSupp.vendor_name_alt = X_vendor_name_alt)
1917 OR ( (RecSupp.vendor_name_alt IS NULL)
1918 AND (X_vendor_name_alt IS NULL)))
1919 AND ( (RecSupp1.stp_enforce_threshold = X_stp_enforce_threshold)
1920 OR ( (RecSupp1.stp_enforce_threshold IS NULL)
1921 AND (X_stp_enforce_threshold IS NULL)))
1922 AND ( (RecSupp.end_date_active = X_end_date_active)
1923 OR ( (RecSupp.end_date_active IS NULL)
1924 AND (X_end_date_active IS NULL)))
1925 ) THEN
1926 return;
1927 ELSE
1928 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1929 --bug 3199481 fnd logging changes: sdixit
1930 IF (l_excep_level >= l_debug_level ) THEN
1931 FND_LOG.MESSAGE (l_excep_level ,
1932 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Supplier_Lock_Row',
1933 FALSE);
1934 END IF;
1935 APP_EXCEPTION.Raise_Exception;
1936 END IF;
1937
1938 END Supplier_Lock_Row;
1939
1940 /***************************************************************************************/
1941
1942 PROCEDURE Package_Update_Row( X_rowid VARCHAR2,
1943 X_exchange_rate NUMBER,
1944 X_exchange_rate_type VARCHAR2,
1945 X_exchange_date DATE,
1946 X_last_update_login NUMBER,
1947 X_last_update_date DATE,
1948 X_last_updated_by NUMBER) IS
1949 BEGIN
1950 UPDATE igi_stp_packages
1951 SET exchange_rate = X_exchange_rate,
1952 exchange_rate_type = X_exchange_rate_type,
1953 exchange_date = X_exchange_date,
1954 last_update_login = X_last_update_login,
1955 last_update_date = X_last_update_date,
1956 last_updated_by = X_last_updated_by
1957 WHERE rowid = x_rowid;
1958 IF (SQL%NOTFOUND) THEN
1959 Raise NO_DATA_FOUND;
1960 END IF;
1961
1962 END Package_Update_Row;
1963
1964
1965 /***************************************************************************************/
1966 PROCEDURE Package_Lock_Row( X_rowid IN OUT NOCOPY VARCHAR2,
1967 X_batch_id NUMBER,
1968 X_package_id NUMBER,
1969 X_package_num NUMBER,
1970 X_stp_id NUMBER,
1971 X_application VARCHAR2,
1972 X_accounting_date DATE,
1973 X_trx_number VARCHAR2,
1974 X_related_trx_number VARCHAR2,
1975 X_reference VARCHAR2,
1976 X_amount NUMBER,
1977 X_currency_code VARCHAR2,
1978 X_exchange_rate NUMBER,
1979 X_exchange_rate_type VARCHAR2,
1980 X_exchange_date DATE) IS
1981 CURSOR C IS
1982 SELECT *
1983 FROM igi_stp_packages
1984 WHERE rowid = X_rowid
1985 FOR UPDATE of exchange_rate NOWAIT;
1986
1987 Recinfo C%ROWTYPE;
1988
1989 BEGIN
1990 OPEN C;
1991 FETCH C INTO Recinfo;
1992 IF (C%NOTFOUND) THEN
1993 CLOSE C;
1994 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1995 --bug 3199481 fnd logging changes: sdixit
1996 IF (l_excep_level >= l_debug_level ) THEN
1997 FND_LOG.MESSAGE (l_excep_level ,
1998 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Package_Lock_Row',
1999 FALSE);
2000 END IF;
2001 APP_EXCEPTION.RAISE_EXCEPTION;
2002 END IF;
2003 CLOSE C;
2004
2005 IF ( (Recinfo.batch_id = X_batch_id)
2006 AND (Recinfo.package_id = X_package_id)
2007 AND ( (Recinfo.package_num = X_package_num)
2008 OR ( (Recinfo.package_num IS NULL)
2009 AND (X_package_num IS NULL)))
2010 AND (Recinfo.stp_id = X_stp_id)
2011 AND (Recinfo.application = X_application)
2012 AND (Recinfo.accounting_date = X_accounting_date)
2013 AND ( (Recinfo.trx_number = X_trx_number)
2014 OR ( (Recinfo.trx_number IS NULL)
2015 AND (X_trx_number IS NULL)))
2016 AND ( (Recinfo.related_trx_number = X_related_trx_number)
2017 OR ( (Recinfo.related_trx_number IS NULL)
2018 AND (X_related_trx_number IS NULL)))
2019 AND ( (Recinfo.reference = X_reference)
2020 OR ( (Recinfo.reference IS NULL)
2021 AND (X_reference IS NULL)))
2022 AND (Recinfo.amount = X_amount)
2023 AND ( (Recinfo.currency_code = X_currency_code)
2024 OR ( (Recinfo.currency_code IS NULL)
2025 AND (X_currency_code IS NULL)))
2026 AND ( (Recinfo.exchange_rate = X_exchange_rate)
2027 OR ( (Recinfo.exchange_rate IS NULL)
2028 AND (X_exchange_rate IS NULL)))
2029 AND ( (Recinfo.exchange_rate_type = X_exchange_rate_type)
2030 OR ( (Recinfo.exchange_rate_type IS NULL)
2031 AND (X_exchange_rate_type IS NULL)))
2032 AND ( (Recinfo.exchange_date = X_exchange_date)
2033 OR ( (Recinfo.exchange_date IS NULL)
2034 AND (X_exchange_date IS NULL)))
2035 )then
2036 return;
2037 ELSE
2038 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2039 --bug 3199481 fnd logging changes: sdixit
2040 IF (l_excep_level >= l_debug_level ) THEN
2041 FND_LOG.MESSAGE (l_excep_level ,
2042 'igi.pls.igistpab.IGI_STP_TABLE_HANDLER_PKG.Package_Lock_Row',
2043 FALSE);
2044 END IF;
2045 APP_EXCEPTION.Raise_Exception;
2046 END IF;
2047
2048 END Package_Lock_Row;
2049 BEGIN
2050 -- Bug 3902175 GSCC warnings fixed
2051 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2052 l_state_level := FND_LOG.LEVEL_STATEMENT;
2053 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
2054 l_event_level := FND_LOG.LEVEL_EVENT;
2055 l_excep_level := FND_LOG.LEVEL_EXCEPTION;
2056 l_error_level := FND_LOG.LEVEL_ERROR;
2057 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
2058
2059 END IGI_STP_TABLE_HANDLER_PKG;