[Home] [Help]
PACKAGE BODY: APPS.RRS_IMPORT_INTERFACE_PKG
Source
1 package body rrs_import_interface_pkg as
2 /* $Header: RRSIMINB.pls 120.2.12020000.2 2012/11/08 23:22:33 sunarang ship $ */
3
4
5
6 ----------------------------------------------------------------------------
7 -- Global constants
8 ----------------------------------------------------------------------------
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RRS_IMPORT_INTERFACE_PKG';
10 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
11 G_PROGAM_APPLICATION_ID NUMBER := FND_GLOBAL.PROG_APPL_ID;
12 G_PROGAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
13 G_USER_NAME FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
16 G_CURRENT_USER_ID NUMBER;
17 G_CURRENT_LOGIN_ID NUMBER;
18 G_HZ_PARTY_ID VARCHAR2(30);
19 G_ADD_ERRORS_TO_FND_STACK VARCHAR2(1);
20 G_APPLICATION_CONTEXT VARCHAR2(30);
21 G_DATE_FORMAT CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
22
23 G_APPLICATION_ID NUMBER(3) := 718;
24 G_DATA_ROWS_UPLOADED_NEW CONSTANT NUMBER := 0;
25 G_PS_TO_BE_PROCESSED CONSTANT VARCHAR2(1) := 1;
26 G_PS_IN_PROCESS CONSTANT VARCHAR2(1) := 2;
27 G_PS_GENERIC_ERROR CONSTANT VARCHAR2(1) := 3;
28 G_PS_SUCCESS CONSTANT VARCHAR2(1) := 4;
29 G_RETCODE_SUCCESS_WITH_WARNING CONSTANT VARCHAR(1) := 'W';
30
31 G_TX_TYPE_CREATE CONSTANT VARCHAR2(6) := 'CREATE';
32 G_TX_TYPE_UPDATE CONSTANT VARCHAR2(6) := 'UPDATE';
33
34
35
36
37 local_processing_errors rrs_processing_errors_tab;
38
39
40 Procedure main(
41 ERRBUF OUT NOCOPY VARCHAR2
42 ,RETCODE OUT NOCOPY VARCHAR2
43 ,p_batch_id IN NUMBER
44 ,p_purge_rows IN VARCHAR2
45 ,p_gather_stats IN VARCHAR2
46 ) is
47 l_batch_id NUMBER;
48 l_data_set_id NUMBER;
49 x_return_status VARCHAR2(1);
50 x_verify_sites_data VARCHAR2(1);
51 x_return_flag VARCHAR2(1);
52 l_site_exist VARCHAR2(1) := 'N';
53 l_loc_exist VARCHAR2(1) := 'N';
54 l_ta_exist VARCHAR2(1) := 'N';
55 conc_status Boolean;
56 l_exists VARCHAR2(1);
57 l_n_exists VARCHAR2(1);
58
59 begin
60
61 FND_FILE.put_line(FND_FILE.LOG, 'Batch ID : '||p_batch_id);
62 FND_FILE.put_line(FND_FILE.LOG, 'Purge Processed Rows : '||p_purge_rows);
63 FND_FILE.put_line(FND_FILE.LOG, 'Gather Statistics on Interface Tables : '||p_gather_stats);
64
65
66 x_return_status := 'S';
67 x_verify_sites_data := 'S';
68
69 begin
70 select 'S'
71 into x_verify_sites_data
72 from RRS_SITES_INTERFACE
73 WHERE batch_id = p_batch_id
74 and Process_status = G_PS_TO_BE_PROCESSED
75 and rownum < 2;
76 exception
77 When no_data_found then
78 x_verify_sites_data := 'E';
79
80 end;
81 check_prereqs( p_batch_id => p_batch_id
82 ,x_return_status => x_return_status
83 );
84
85 if x_return_status = 'S' AND x_verify_sites_data = 'S' then
86 /*
87 check_prereqs( p_batch_id => p_batch_id
88 ,x_return_status => x_return_status
89 );
90 */
91
92 /********************************************************************
93 * This Update is for defaulting the Address1 in case of Site
94 * creation/updation using no value for address1 field. We are populating
95 * the Site Name in Address1 field.
96 * ************************************************************/
97
98 Update RRS_SITES_INTERFACE
99 Set Address1 = Site_name
100 where batch_id = p_batch_id
101 and process_status = G_PS_TO_BE_PROCESSED
102 and Address1 is NULL
103 and country is NOT NULL
104 and rowid in (select rowid
105 from RRS_SITES_INTERFACE
106 where batch_id = p_batch_id
107 and process_status = G_PS_TO_BE_PROCESSED
108 and Address1 is NULL);
109
110
111 If p_gather_stats = 'Y' then
112
113 fnd_stats.gather_table_stats('RRS','RRS_SITES_INTERFACE',cascade=>true,percent=>30);
114
115 end if;
116
117 Validate_new_rows( p_batch_id => p_batch_id
118 , p_purge_rows => p_purge_rows
119 ,x_return_flag=>x_return_flag
120 );
121
122
123 Validate_update_rows( p_batch_id => p_batch_id
124 ,p_purge_rows => p_purge_rows
125 ,x_return_flag => x_return_flag
126 );
127
128
129 end if ;
130
131 Begin
132 select 'Y'
133 into l_site_exist
134 from RRS_SITE_UA_INTF A
135 where a.batch_id = p_batch_id
136 and (A.SITE_ID is NOT NULL )
137 and (A.Process_status = G_PS_TO_BE_PROCESSED )
138 and rownum < 2;
139
140 Exception
141 When NO_DATA_FOUND THEN
142 l_site_exist := 'N';
143
144 End;
145
146 Begin
147 select 'Y'
148 into l_loc_exist
149 from RRS_LOCATION_UA_INTF A
150 where a.batch_id = p_batch_id
151 and (A.LOCATION_ID is NOT NULL )
152 and (A.Process_status = G_PS_TO_BE_PROCESSED )
153 and rownum < 2;
154
155 Exception
156 When NO_DATA_FOUND THEN
157 l_loc_exist := 'N';
158
159 End;
160
161 Begin
162 select 'Y'
163 into l_ta_exist
164 from RRS_TRADEAREA_UA_INTF A
165 where a.batch_id = p_batch_id
166 and (A.TRADE_AREA_ID is NOT NULL )
167 and (A.Process_status = G_PS_TO_BE_PROCESSED )
168 and rownum < 2;
169
170 Exception
171 When NO_DATA_FOUND THEN
172 l_ta_exist := 'N';
173
174 End;
175
176 If ( l_site_exist = 'Y' OR l_loc_exist = 'Y' OR l_ta_exist = 'Y' ) then
177
178 -- l_transaction_id := MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL;
179 -- l_data_set_id := RRS_SITE_INTF_SETS_S.NEXTVAL;
180
181 SELECT RRS_SITE_INTF_SETS_S.NEXTVAL
182 INTO l_data_set_id
183 FROM DUAL;
184
185 If l_site_exist = 'Y' then
186 UPDATE RRS_SITE_UA_INTF
187 SET DATA_SET_ID = l_data_set_id,
188 Transaction_id = l_data_set_id,
189 ATTR_GROUP_TYPE = 'RRS_SITEMGMT_GROUP',
190 DATA_LEVEL_ID = 71802,
191 DATA_LEVEL_NAME = 'SITE_LEVEL'
192 where batch_id = p_batch_id
193 and SITE_ID is NOT NULL
194 and Process_status = G_PS_TO_BE_PROCESSED;
195
196 If p_gather_stats = 'Y' then
197
198 fnd_stats.gather_table_stats('RRS','RRS_SITE_UA_INTF',cascade=>true,percent=>30);
199
200 end if;
201
202 end if;
203
204 If l_loc_exist = 'Y' then
205 UPDATE RRS_LOCATION_UA_INTF
206 SET DATA_SET_ID = l_data_set_id,
207 Transaction_id = l_data_set_id,
208 ATTR_GROUP_TYPE = 'RRS_LOCATION_GROUP',
209 DATA_LEVEL_ID = 71801,
210 DATA_LEVEL_NAME = 'LOCATION_LEVEL'
211 where batch_id = p_batch_id
212 and LOCATION_ID is NOT NULL
213 and Process_status = G_PS_TO_BE_PROCESSED;
214
215 If p_gather_stats = 'Y' then
216
217 fnd_stats.gather_table_stats('RRS','RRS_LOCATION_UA_INTF',cascade=>true,percent=>30);
218
219 end if;
220
221
222 end if;
223
224
225 If l_ta_exist = 'Y' then
226 UPDATE RRS_TRADEAREA_UA_INTF
227 SET DATA_SET_ID = l_data_set_id,
228 Transaction_id = l_data_set_id,
229 ATTR_GROUP_TYPE = 'RRS_TRADE_AREA_GROUP',
230 DATA_LEVEL_ID = 71803,
231 DATA_LEVEL_NAME = 'TRADE_AREA_LEVEL'
232 where batch_id = p_batch_id
233 and trade_area_id is not null
234 and Process_status = G_PS_TO_BE_PROCESSED;
235
236 If p_gather_stats = 'Y' then
237
238 fnd_stats.gather_table_stats('RRS','RRS_TRADEAREA_UA_INTF',cascade=>true,percent=>30);
239
240 end if;
241
242
243 end if;
244
245
246 RRS_SITE_UDA_BULKLOAD_INTF.LOAD_USERATTR_DATA( ERRBUF => errbuf
247 ,RETCODE => retcode
248 ,p_batch_id => p_batch_id
249 ,p_data_set_id => l_data_set_id
250 ,p_purge_successful_lines => p_purge_rows
251 );
252
253 IF ( RETCODE = FND_API.G_RET_STS_SUCCESS ) THEN
254 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('SUCCESS', 'Success: All the rows processed successfully.');
255 ELSIF ( RETCODE = G_RETCODE_SUCCESS_WITH_WARNING ) THEN
256 FND_FILE.put_line(FND_FILE.LOG, 'Few records failed the validations. Please check the interface errors table for details.');
257 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: One or more rows errored due to validation checks.');
258 ELSIF ( RETCODE = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
259 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Error: Unexpected Error happened while processing the Interface rows in this Batch.');
260
261 END IF;
262
263 end if;
264
265 end;
266
267 /************************************************************************************
268 * This procedure check_prereqs will do the pre-req checks before starting the
269 * validations. If pre-req checks fails than no other validation will happen
270 * and process will stop immediately.
271 * *********************************************************************/
272
273 Procedure check_prereqs(
274 p_batch_id IN number
275 ,x_return_status OUT NOCOPY varchar2
276 ) is
277
278 l_count number;
279
280 Type rrs_site_counts_rec is RECORD (
281 site_identification_number rrs_sites_interface.site_identification_number%Type
282 ,site_count number
283 );
284 Type rrs_site_counts_tab is Table of rrs_site_counts_rec;
285 l_site_counts rrs_site_counts_tab;
286
287 local_processing_errors rrs_processing_errors_tab;
288 p_processing_errors rrs_processing_errors_tab;
289 l_msg VARCHAR2(1000);
290
291 begin
292
293 p_processing_errors := rrs_processing_errors_tab();
294 local_processing_errors := rrs_processing_errors_tab();
295
296
297 SELECT SITE_IDENTIFICATION_NUMBER , count(*)
298 BULK COLLECT
299 INTO l_site_counts
300 FROM RRS_SITES_INTERFACE
301 WHERE BATCH_ID = p_batch_id
302 AND TRANSACTION_TYPE = G_TX_TYPE_CREATE
303 AND PROCESS_STATUS = G_PS_TO_BE_PROCESSED
304 AND SITE_IDENTIFICATION_NUMBER IS NOT NULL
305 GROUP BY SITE_IDENTIFICATION_NUMBER
306 HAVING count(*) > 1;
307
308 IF l_site_counts.count > 0 Then
309
310 x_return_status := 'E';
311
312 fnd_message.set_name('RRS', 'RRS_IMP_SITE_DUP');
313 fnd_message.set_token('NUM', l_site_counts(1).site_identification_number);
314 l_msg := fnd_message.get;
315
316
317 prepare_error_mesg(
318 p_site_id => NULL
319 ,p_site_id_num => l_site_counts(1).site_identification_number
320 ,p_column_name => 'SITE_IDENTIFICATION_NUMBER'
321 ,p_message_name => 'MESSAGE NAME'
322 -- ,p_message_text => 'This Batch has multiple rows with same Site Identification Number : '||l_site_counts(1).site_identification_number|| ' , Modify the batch.'
323 ,p_message_text => l_msg
324 ,p_source_table_name => 'RRS_SITES_INTERFACE'
325 ,p_destination_table_name => 'RRS_SITES_B'
326 ,p_process_status => '3'
327 ,p_transaction_type => G_TX_TYPE_CREATE
328 ,p_batch_id => p_batch_id
329 ,p_processing_errors => local_processing_errors
330 );
331
332 Write_interface_errors(p_processing_errors => local_processing_errors);
333
334 else
335
336 Delete from RRS_INTERFACE_ERRORS
337 Where batch_id = p_batch_id
338 and Process_status = '3';
339
340 x_return_status := 'S';
341
342 end if;
343
344
345 /********************************************************************
346 * This Update is for defaulting the Address1 in case of Site
347 * creation/updation using no value for address1 field. We are populating
348 * the Site Name in Address1 field.
349 * ************************************************************/
350
351 /*
352 * Moved this SQL just before calling Validat_new_rows and Validate_update_rows.
353 *
354 *
355 Update RRS_SITES_INTERFACE
356 Set Address1 = Site_name
357 where batch_id = p_batch_id
358 and process_status = G_PS_TO_BE_PROCESSED
359 and Address1 is NULL
360 and country is NOT NULL
361 and rowid in (select rowid
362 from RRS_SITES_INTERFACE
363 where batch_id = p_batch_id
364 and process_status = G_PS_TO_BE_PROCESSED
365 and Address1 is NULL);
366
367
368
369 select count(distinct(site_status_code))
370 into l_count
371 from rrs_sites_interface
372 where batch_id = p_batch_id
373 and process_status = G_PS_TO_BE_PROCESSED;
374
375 If l_count > 1 then
376
377 x_return_status := 'E';
378 elsif l_count = 1 then
379
380 x_return_status := 'S';
381
382 end if;
383 */
384
385
386 end;
387
388
389
390 Procedure Validate_new_rows(
391 p_batch_id IN number
392 ,p_purge_rows IN varchar2
393 ,x_return_flag OUT NOCOPY varchar2
394 ) is
395 p_site_id varchar2(30);
396 p_site_id_num varchar2(30);
397 l_found varchar2(1);
398 l_batch_id number;
399 l_row_status varchar2(1);
400 p_transaction_type varchar2(6);
404 Select site_identification_number,
401 conc_status Boolean;
402
403 Cursor c_default_site_id_num(l_batch_id number) is
405 ROWID
406 from RRS_SITES_INTERFACE
407 where batch_id = p_batch_id
408 and process_status = G_PS_IN_PROCESS
409 and transaction_type = G_TX_TYPE_CREATE
410 and site_identification_number is NULL;
411
412
413 Cursor c_new_interface_row(l_batch_id number) is
414 select
415 SITE_ID,
416 SITE_IDENTIFICATION_NUMBER,
417 SITE_NAME,
418 SITE_TYPE_CODE,
419 SITE_STATUS_CODE,
420 SITE_USE_TYPE_CODE,
421 BRANDNAME_CODE,
422 CALENDAR_CODE,
423 LOCATION_STATUS,
424 LOCATION_ID,
425 SITE_PARTY_ID,
426 PARTY_SITE_ID,
427 LE_PARTY_ID,
428 CREATED_BY,
429 CREATION_DATE,
430 LAST_UPDATED_BY,
431 LAST_UPDATE_DATE,
432 LAST_UPDATE_LOGIN,
433 ADDRESS1,
434 ADDRESS2,
435 ADDRESS3,
436 ADDRESS4,
437 ADDRESS_LINES_PHONETIC,
438 CITY,
439 POSTAL_CODE,
440 STATE,
441 PROVINCE,
442 COUNTY,
443 COUNTRY,
444 GEOMETRY_SOURCE,
445 LONGITUDE,
446 LATITUDE,
447 REQUEST_ID,
448 PROGRAM_APPLICATION_ID,
449 PROGRAM_ID,
450 PROGRAM_UPDATE_DATE,
451 TRANSACTION_TYPE,
452 BATCH_PROCESSING,
453 BATCH_ID
454 FROM RRS_SITES_INTERFACE
455 WHERE TRANSACTION_TYPE = G_TX_TYPE_CREATE
456 and BATCH_ID = l_batch_id
457 and Process_status = G_PS_IN_PROCESS;
458
459
460 local_processing_errors rrs_processing_errors_tab;
461 p_processing_errors rrs_processing_errors_tab;
462
463 p_init_msg_list varchar2(1) := 'T';
464 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
465 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
466
467 p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
468 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
469
470 p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
471 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
472
473 p_party_usage_code varchar2(30);
474
475 p_do_addr_val varchar2(1) := 'Y';
476 x_location_id number;
477 x_addr_val_status varchar2(3);
478 x_addr_warn_msg varchar2(240);
479 x_return_status varchar2(1);
480 x_msg_count number;
481 x_msg_data varchar2(1000);
482
483 x_party_id number;
484 x_party_number number;
485 x_profile_id number;
486
487
488 x_party_site_id number;
489 x_party_site_number number;
490
491 l_create_location varchar2(1);
492 l_create_party varchar2(1);
493
494 l_geo_source varchar2(30);
495 l_db_geo_source varchar2(30);
496 l_upd_geo_data varchar2(1);
497
498 Type rrs_site_id_rec is RECORD (site_id RRS_SITES_B.SITE_ID%TYPE);
499 Type rrs_site_id_tab is TABLE OF NUMBER;
500 s_site_ids rrs_site_id_tab;
501
502 x_num_rows number;
503
504 l_site_id_num number;
505 l_site_id_num_exist varchar2(30);
506 l_security_enabled VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_ROLE_BASED_SECURITY_ENABLED'),'N');
507 l_site_author_role VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_AUTO_ASSIGN_SITE_AUTHOR'),'N');
508 l_security_passed VARCHAR2(1) := 'Y';
509
510 l_msg VARCHAR2(1000);
511 Begin
512
513 l_batch_id := p_batch_id;
514
515 /* add a check , if any row in status 2 for CREATE throw error back and dont
516 * touch any row in the batch. */
517
518
519 /**************************************************************
520 * Before Starting all the validatios , let's mark all the rows in
521 * Interface table with status Validation started ( 2 ). After
522 * completion of this processing , all the rows in this batch should have
523 * status as either Validation failed ( 3 ) or Validation succeeded ( 4 )
524 * **************************************************************/
525
526 UPDATE RRS_SITES_INTERFACE
527 SET PROCESS_STATUS = G_PS_IN_PROCESS,
528 REQUEST_ID = G_REQUEST_ID,
529 PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID,
530 PROGRAM_ID = G_PROGAM_ID,
531 PROGRAM_UPDATE_DATE = SYSDATE,
532 LAST_UPDATED_BY = G_USER_ID,
533 LAST_UPDATE_DATE = SYSDATE,
534 LAST_UPDATE_LOGIN = G_LOGIN_ID
535 WHERE BATCH_ID = p_batch_id
536 AND PROCESS_STATUS = G_PS_TO_BE_PROCESSED
537 AND TRANSACTION_TYPE = G_TX_TYPE_CREATE;
538
539 /* code for RBAC starts here */
540 If ( sql%rowcount ) > 0 then
541
542 if l_security_enabled = 'Y' then
543 IF fnd_function.test('RRS_CREATE_SITE') THEN
544 null;
545 ELSE
546 l_security_passed := 'N';
547
548 Update RRS_SITES_INTERFACE
549 Set Process_status = '3'
550 Where Batch_id = p_batch_id
551 and process_status = G_PS_IN_PROCESS
552 and transaction_type = G_TX_TYPE_CREATE;
553
554 FND_FILE.put_line(FND_FILE.LOG, 'User does not have Create Site Privilege for creating sites. Please contact your Site/System Administrator' );
555 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: One or more rows errored due to validation checks. ');
556
557 END IF;
558
559 end if;
560
561
562 end if;
563
564 /* code for RBAC ends here */
565
566
567
568 /*******************************************************************
569 * Based on profile check of Automatic generation of Site
570 * Identification number , let's generate the
571 * Site_identification_number using a sequence and assign
572 * it to every row of this batch with Transaction_type = 'CREATE'.
573 * ***********************************************************/
574
575 If l_security_passed = 'Y' then
576
577 IF (FND_PROFILE.VALUE('RRS_AUTO_DEFAULT_SITE_NUM') = '1' ) then
578
579
580 FOR site_id_num IN c_default_site_id_num(l_batch_id) LOOP
581
582
583 -- l_site_id_num := rrs_default_site_number_s.nextval;
584 SELECT RRS_DEFAULT_SITE_NUMBER_S.NEXTVAL
585 INTO l_site_id_num
586 FROM DUAL;
587
588 Begin
589 Select site_identification_number
590 into l_site_id_num_exist
591 from RRS_SITES_INTERFACE
592 where site_identification_number = to_char(l_site_id_num)
593 and batch_id = p_batch_id
594 and process_status = G_PS_IN_PROCESS
595 and transaction_type = G_TX_TYPE_CREATE;
596 Exception
597 When TOO_MANY_ROWS then null;
598 When NO_DATA_FOUND then
599
600 Update RRS_SITES_INTERFACE
601 Set site_identification_number = l_site_id_num
602 where site_identification_number is NULL
603 and batch_id = p_batch_id
604 and process_status = G_PS_IN_PROCESS
605 and transaction_type = G_TX_TYPE_CREATE
606 and ROWID = site_id_num.ROWID;
607
608 End;
609
610 END LOOP;
611
612 End if;
613
614
615 FOR site_data IN c_new_interface_row(l_batch_id) LOOP
616
617 p_processing_errors := rrs_processing_errors_tab();
618 local_processing_errors := rrs_processing_errors_tab();
619
620 l_row_status := 'S';
621 l_create_party := 'N';
622 l_create_location := 'N';
623 l_upd_geo_data := 'Y';
624
625 begin
626 Check_site_id_num(
627 p_site_id_num=>site_data.site_identification_number
628 ,p_site_id=>site_data.site_id
629 ,p_transaction_type=>site_data.transaction_type
630 ,x_return_flag=>x_return_flag
631 );
632 if x_return_flag = 'S' AND l_row_status = 'S' then
633 null;
634 -- dbms_output.put_line('Site Identification Number validation succeeded ');
635 elsif x_return_flag = 'E' then
636 l_row_status := 'E';
637
638 fnd_message.set_name('RRS', 'RRS_IMP_SITE_EXIST');
639 l_msg := fnd_message.get;
640
641 prepare_error_mesg(
642 p_site_id => NULL
643 ,p_site_id_num => site_data.site_identification_number
644 ,p_column_name => 'SITE_IDENTIFICATION_NUMBER'
645 ,p_message_name => 'MESSAGE NAME'
646 -- ,p_message_text => 'This Site Identification Number already exists, Enter a new number.'
647 ,p_message_text => l_msg
648 ,p_source_table_name => 'RRS_SITES_INTERFACE'
649 ,p_destination_table_name => 'RRS_SITES_B'
650 ,p_process_status => '3'
651 ,p_transaction_type => site_data.transaction_type
652 ,p_batch_id => site_data.batch_id
653 ,p_processing_errors => local_processing_errors
654 );
655 end if;
656
657 If site_data.site_name is NULL Then
658 l_row_status := 'E';
659
660 fnd_message.set_name('RRS', 'RRS_IMP_NAME_NULL');
661 fnd_message.set_token('NUM', site_data.site_identification_number);
662 l_msg := fnd_message.get;
663
664 prepare_error_mesg(
665 p_site_id => NULL
666 ,p_site_id_num => site_data.site_identification_number
667 ,p_column_name => 'SITE_NAME'
668 ,p_message_name => 'MESSAGE NAME'
669 -- ,p_message_text => 'Site Name cannot be Null for : '||site_data.site_identification_number
670 ,p_message_text => l_msg
671 ,p_source_table_name => 'RRS_SITES_INTERFACE'
672 ,p_destination_table_name => 'RRS_SITES_TL'
673 ,p_process_status => '3'
674 ,p_transaction_type => site_data.transaction_type
675 ,p_batch_id => site_data.batch_id
676 ,p_processing_errors => local_processing_errors
677 );
678 end if;
679
680
681 If site_data.site_status_code is NOT NULL then
682 Check_site_status_code( p_site_id_num => site_data.site_identification_number
683 ,p_site_status_code => site_data.site_status_code
684 ,x_return_flag => x_return_flag
685 );
686 if x_return_flag = 'S' AND l_row_status = 'S' then
687 null;
688 -- dbms_output.put_line('Site status code validation succeeded ');
689 elsif x_return_flag = 'E' then
690 l_row_status := 'E';
691
692 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_STATUS');
693 fnd_message.set_token('NUM', site_data.site_identification_number);
694 l_msg := fnd_message.get;
695
696 prepare_error_mesg(
697 p_site_id => NULL
698 ,p_site_id_num => site_data.site_identification_number
699 ,p_column_name => 'SITE_STATUS_CODE'
700 ,p_message_name => 'MESSAGE NAME'
701 -- ,p_message_text => 'Site Status code is not valid for site_id : '||site_data.site_identification_number
702 ,p_message_text => l_msg
703 ,p_source_table_name => 'RRS_SITES_INTERFACE'
704 ,p_destination_table_name => 'RRS_SITES_B'
705 ,p_process_status => '3'
706 ,p_transaction_type => site_data.transaction_type
707 ,p_batch_id => site_data.batch_id
708 ,p_processing_errors => local_processing_errors
709 );
710 end if;
711
712 else
713 l_row_status := 'E';
714
715 fnd_message.set_name('RRS', 'RRS_IMP_NULL_STATUS');
716 fnd_message.set_token('NUM', site_data.site_identification_number);
717 l_msg := fnd_message.get;
718
719 prepare_error_mesg(
720 p_site_id => NULL
721 ,p_site_id_num => site_data.site_identification_number
722 ,p_column_name => 'SITE_STATUS_CODE'
723 ,p_message_name => 'MESSAGE NAME'
724 -- ,p_message_text => 'Site Status code cannot be null for site_id : '||site_data.site_identification_number
725 ,p_message_text => l_msg
726 ,p_source_table_name => 'RRS_SITES_INTERFACE'
727 ,p_destination_table_name => 'RRS_SITES_B'
728 ,p_process_status => '3'
729 ,p_transaction_type => site_data.transaction_type
730 ,p_batch_id => site_data.batch_id
731 ,p_processing_errors => local_processing_errors
732 );
733
734 end if;
735
736
737
738
739 If site_data.brandname_code is NOT NULL then
740 Check_site_brand_code( p_site_id_num => site_data.site_identification_number
741 ,p_site_brand_code => site_data.brandname_code
742 ,x_return_flag => x_return_flag
743 );
744 if x_return_flag = 'S' AND l_row_status = 'S' then
745 null;
746 -- dbms_output.put_line('Brandname code code validation succeeded ');
747 elsif x_return_flag = 'E' then
748 l_row_status := 'E';
749
750 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_BRAND');
751 fnd_message.set_token('NUM', site_data.site_identification_number);
752 l_msg := fnd_message.get;
753
754 prepare_error_mesg(
755 p_site_id => NULL
756 ,p_site_id_num => site_data.site_identification_number
757 ,p_column_name => 'BRANDNAME_CODE'
758 ,p_message_name => 'MESSAGE NAME'
759 -- ,p_message_text => 'Brandname Code validation fails for : '||site_data.site_identification_number
760 ,p_message_text => l_msg
761 ,p_source_table_name => 'RRS_SITES_INTERFACE'
762 ,p_destination_table_name => 'RRS_SITES_B'
763 ,p_process_status => '3'
764 ,p_transaction_type => site_data.transaction_type
765 ,p_batch_id => site_data.batch_id
766 ,p_processing_errors => local_processing_errors
767 );
768 end if;
769
770 end if;
771
772
773 if site_data.calendar_code is NOT NULL then
774 Check_site_calendar_code( p_site_id_num => site_data.site_identification_number
775 ,p_site_calendar_code => site_data.calendar_code
776 ,x_return_flag => x_return_flag
777 );
778 if x_return_flag = 'S' AND l_row_status = 'S' then
779 null;
780 -- dbms_output.put_line('Calendar code validation succeeded ');
781 elsif x_return_flag = 'E' then
782 l_row_status := 'E';
783
784 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_CAL');
785 fnd_message.set_token('NUM', site_data.site_identification_number);
786 l_msg := fnd_message.get;
787
788 prepare_error_mesg(
789 p_site_id => NULL
790 ,p_site_id_num => site_data.site_identification_number
791 ,p_column_name => 'CALENDAR_CODE'
792 ,p_message_name => 'MESSAGE NAME'
793 -- ,p_message_text => 'Calendar Code validation fails for : '||site_data.site_identification_number
794 ,p_message_text => l_msg
795 ,p_source_table_name => 'RRS_SITES_INTERFACE'
796 ,p_destination_table_name => 'RRS_SITES_B'
797 ,p_process_status => '3'
798 ,p_transaction_type => site_data.transaction_type
799 ,p_batch_id => site_data.batch_id
800 ,p_processing_errors => local_processing_errors
801 );
802 end if;
803 end if;
804
805 if site_data.site_use_type_code is NOT NULL then
806 Check_site_use_type_code( p_site_id_num => site_data.site_identification_number
807 ,p_site_use_type_code => site_data.site_use_type_code
808 ,x_return_flag => x_return_flag
809 );
810 if x_return_flag = 'S' AND l_row_status = 'S' then
811 null;
812 -- dbms_output.put_line('Site use type code validation succeeded ');
813 elsif x_return_flag = 'E' then
814 l_row_status := 'E';
815
816 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_USE_CODE');
817 fnd_message.set_token('NUM', site_data.site_identification_number);
818 l_msg := fnd_message.get;
819
820 prepare_error_mesg(
821 p_site_id => NULL
822 ,p_site_id_num => site_data.site_identification_number
823 ,p_column_name => 'SITE_USE_TYPE_CODE'
824 ,p_message_name => 'MESSAGE NAME'
825 -- ,p_message_text => 'Site Use Type Code validation fails for : '||site_data.site_identification_number
826 ,p_message_text => l_msg
827 ,p_source_table_name => 'RRS_SITES_INTERFACE'
828 ,p_destination_table_name => 'RRS_SITE_USES'
829 ,p_process_status => '3'
830 ,p_transaction_type => site_data.transaction_type
831 ,p_batch_id => site_data.batch_id
832 ,p_processing_errors => local_processing_errors
833 );
834 end if;
835 end if;
836
837
838
839 If site_data.country is NOT NULL then
840
841 Check_location_country( p_site_id_num => site_data.site_identification_number
842 ,p_location_id => site_data.location_id
843 ,p_country_code => site_data.country
844 ,x_return_flag => x_return_flag
845 );
846 if x_return_flag = 'S' AND l_row_status = 'S' then
847 null;
848 -- dbms_output.put_line('Country code validation succeeded ');
849 elsif x_return_flag = 'E' then
850 l_row_status := 'E';
851
852 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_COUNTRY');
853 fnd_message.set_token('NUM', site_data.site_identification_number);
854 l_msg := fnd_message.get;
855
856 prepare_error_mesg(
857 p_site_id => NULL
858 ,p_site_id_num => site_data.site_identification_number
859 ,p_column_name => 'COUNTRY_CODE'
860 ,p_message_name => 'MESSAGE NAME'
861 -- ,p_message_text => 'Country Code validation fails for : '||site_data.site_identification_number
862 ,p_message_text => l_msg
863 ,p_source_table_name => 'RRS_SITES_INTERFACE'
864 ,p_destination_table_name => 'HZ_LOCATIONS'
865 ,p_process_status => '3'
866 ,p_transaction_type => site_data.transaction_type
867 ,p_batch_id => site_data.batch_id
868 ,p_processing_errors => local_processing_errors
869 );
870 end if;
871 else
872 l_row_status := 'E';
873
874 fnd_message.set_name('RRS', 'RRS_IMP_NULL_COUNTRY');
875 fnd_message.set_token('NUM', site_data.site_identification_number);
876 l_msg := fnd_message.get;
877
878 prepare_error_mesg(
879 p_site_id => NULL
880 ,p_site_id_num => site_data.site_identification_number
881 ,p_column_name => 'COUNTRY_CODE'
882 ,p_message_name => 'MESSAGE NAME'
883 -- ,p_message_text => 'Invalid Country Code. country Code is required for : '||site_data.site_identification_number
884 ,p_message_text => l_msg
885 ,p_source_table_name => 'RRS_SITES_INTERFACE'
886 ,p_destination_table_name => 'HZ_LOCATIONS'
887 ,p_process_status => '3'
888 ,p_transaction_type => site_data.transaction_type
889 ,p_batch_id => site_data.batch_id
890 ,p_processing_errors => local_processing_errors
891 );
892
893 end if;
894
895 If site_data.location_status = 'E' and site_data.location_id is NOT NULL Then
896
897 Check_location_id(p_site_id_num => site_data.site_identification_number
898 ,p_location_id => site_data.location_id
899 ,p_country_code => site_data.country
900 ,x_return_flag => x_return_flag
901 );
902 if x_return_flag = 'S' AND l_row_status = 'S' then
903 null;
904 -- dbms_output.put_line('Location ID validation succeeded 1');
905 elsif x_return_flag = 'E' then
906 l_row_status := 'E';
907
908 fnd_message.set_name('RRS', 'RRS_IMP_LOC_STATUS_INVALID');
909 fnd_message.set_token('NUM', site_data.site_identification_number);
910 l_msg := fnd_message.get;
911
912 prepare_error_mesg(
913 p_site_id => NULL
914 ,p_site_id_num => site_data.site_identification_number
915 ,p_column_name => 'LOCATION_ID'
916 ,p_message_name => 'MESSAGE NAME'
917 -- ,p_message_text => 'Location Status and Location ID combination is not valid for : '||site_data.site_identification_number
918 ,p_message_text => l_msg
919 ,p_source_table_name => 'RRS_SITES_INTERFACE'
920 ,p_destination_table_name => 'HZ_LOCATIONS'
921 ,p_process_status => '3'
922 ,p_transaction_type => site_data.transaction_type
923 ,p_batch_id => site_data.batch_id
924 ,p_processing_errors => local_processing_errors
925 );
926 end if;
927 /*
928 elsif site_data.location_status = 'E' and site_data.address1 is NOT NULL and site_data.country is NOT NULL Then
929
930 Check_address1( p_site_id_num => site_data.site_identification_number
931 ,p_location_id => site_data.location_id
932 ,p_location_status => site_data.location_status
933 ,p_country_code => site_data.country
934 ,p_address1 => site_data.address1
935 ,x_return_flag => x_return_flag
936 );
937 if x_return_flag = 'S' AND l_row_status = 'S' then
938 null;
939 -- dbms_output.put_line('Location ID validation succeeded 2');
940 elsif x_return_flag = 'E' then
941 l_row_status := 'E';
942
943 fnd_message.set_name('RRS', 'RRS_IMP_ADD_INVALID');
944 fnd_message.set_token('NUM', site_data.site_identification_number);
945 l_msg := fnd_message.get;
946
947 prepare_error_mesg(
948 p_site_id => NULL
949 ,p_site_id_num => site_data.site_identification_number
950 ,p_column_name => 'ADDRESS1'
951 ,p_message_name => 'MESSAGE NAME'
952 -- ,p_message_text => 'Location Status and Address combination is not valid for : '||site_data.site_identification_number
953 ,p_message_text => l_msg
954 ,p_source_table_name => 'RRS_SITES_INTERFACE'
955 ,p_destination_table_name => 'HZ_LOCATIONS'
956 ,p_process_status => '3'
957 ,p_transaction_type => site_data.transaction_type
958 ,p_batch_id => site_data.batch_id
959 ,p_processing_errors => local_processing_errors
960 );
961 end if;
962 */
963
964 elsif site_data.location_status = 'N' and site_data.address1 is NOT NULL and site_data.country is NOT NULL Then
965
966 Check_address1( p_site_id_num => site_data.site_identification_number
967 ,p_location_id => site_data.location_id
968 ,p_location_status => site_data.location_status
969 ,p_country_code => site_data.country
970 ,p_address1 => site_data.address1
971 ,x_return_flag => x_return_flag
972 );
976 l_create_location := 'Y';
973 -- dbms_output.put_line('How about now... : '||x_return_flag);
974 -- dbms_output.put_line('Row Status now... : '||l_row_status);
975 if x_return_flag = 'S' AND l_row_status = 'S' then
977
978
979 elsif x_return_flag = 'E' then
980 l_row_status := 'E';
981
982 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_LOC_COMB');
983 fnd_message.set_token('NUM', site_data.site_identification_number);
984 l_msg := fnd_message.get;
985
986 prepare_error_mesg(
987 p_site_id => NULL
988 ,p_site_id_num => site_data.site_identification_number
989 ,p_column_name => 'ADDRESS1'
990 ,p_message_name => 'MESSAGE NAME'
991 -- ,p_message_text => 'Location status , Address1 and Country validations fails for : '||site_data.site_identification_number
992 ,p_message_text => l_msg
993 ,p_source_table_name => 'RRS_SITES_INTERFACE'
994 ,p_destination_table_name => 'HZ_LOCATIONS'
995 ,p_process_status => '3'
996 ,p_transaction_type => site_data.transaction_type
997 ,p_batch_id => site_data.batch_id
998 ,p_processing_errors => local_processing_errors
999 );
1000 end if;
1001
1002 elsIf site_data.location_status = 'N' and site_data.location_id is NOT NULL Then
1003 l_row_status := 'E';
1004
1005 fnd_message.set_name('RRS', 'RRS_IMP_LOC_STATUS_INVALID');
1006 fnd_message.set_token('NUM', site_data.site_identification_number);
1007 l_msg := fnd_message.get;
1008
1009 prepare_error_mesg(
1010 p_site_id => NULL
1011 ,p_site_id_num => site_data.site_identification_number
1012 ,p_column_name => 'LOCATION_ID'
1013 ,p_message_name => 'MESSAGE NAME'
1014 -- ,p_message_text => 'Location Status and Location ID combination is not valid for : '||site_data.site_identification_number
1015 ,p_message_text => l_msg
1016 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1017 ,p_destination_table_name => 'HZ_LOCATIONS'
1018 ,p_process_status => '3'
1019 ,p_transaction_type => site_data.transaction_type
1020 ,p_batch_id => site_data.batch_id
1021 ,p_processing_errors => local_processing_errors
1022 );
1023
1024 elsif site_data.location_status = 'N' and (site_data.address1 is NULL OR site_data.country is NULL) Then
1025 l_row_status := 'E';
1026
1027 fnd_message.set_name('RRS', 'RRS_IMP_ADD_INVALID');
1028 fnd_message.set_token('NUM', site_data.site_identification_number);
1029 l_msg := fnd_message.get;
1030
1031 prepare_error_mesg(
1032 p_site_id => NULL
1033 ,p_site_id_num => site_data.site_identification_number
1034 ,p_column_name => 'ADDRESS1'
1035 ,p_message_name => 'MESSAGE NAME'
1036 -- ,p_message_text => 'Location Status and Address combination is not valid for : '||site_data.site_identification_number
1037 ,p_message_text => l_msg
1038 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1039 ,p_destination_table_name => 'HZ_LOCATIONS'
1040 ,p_process_status => '3'
1041 ,p_transaction_type => site_data.transaction_type
1042 ,p_batch_id => site_data.batch_id
1043 ,p_processing_errors => local_processing_errors
1044 );
1045 else
1046 l_row_status := 'E';
1047
1048 fnd_message.set_name('RRS', 'RRS_IMP_LOC_INVALID');
1049 fnd_message.set_token('NUM', site_data.site_identification_number);
1050 l_msg := fnd_message.get;
1051
1052 prepare_error_mesg(
1053 p_site_id => NULL
1054 ,p_site_id_num => site_data.site_identification_number
1055 ,p_column_name => 'LOCATION_STATUS'
1056 ,p_message_name => 'MESSAGE NAME'
1057 -- ,p_message_text => 'Location Data is invalid for : '||site_data.site_identification_number
1058 ,p_message_text => l_msg
1059 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1060 ,p_destination_table_name => 'RRS_SITES_INTERFACE'
1061 ,p_process_status => '3'
1062 ,p_transaction_type => site_data.transaction_type
1063 ,p_batch_id => site_data.batch_id
1064 ,p_processing_errors => local_processing_errors
1065 );
1066
1067 End if;
1068
1069 If ( ( site_data.LONGITUDE is NOT NULL AND site_data.LATITUDE is NULL ) OR
1070 ( site_data.LONGITUDE is NULL and site_data.LATITUDE is NOT NULL )) Then
1071
1072 l_row_status := 'E';
1073
1074 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_LONG_LAT');
1075 fnd_message.set_token('NUM', site_data.site_identification_number);
1076 l_msg := fnd_message.get;
1077
1078 prepare_error_mesg(
1079 p_site_id => NULL
1080 ,p_site_id_num => site_data.site_identification_number
1081 ,p_column_name => 'LONGITUDE LATITUDE'
1082 ,p_message_name => 'MESSAGE NAME'
1083 -- ,p_message_text => 'Both langitude and latitude should be either Null or Not Null for : '||site_data.site_identification_number
1084 ,p_message_text => l_msg
1085 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1086 ,p_destination_table_name => 'HZ_LOCATIONS'
1087 ,p_process_status => '3'
1088 ,p_transaction_type => site_data.transaction_type
1089 ,p_batch_id => site_data.batch_id
1090 ,p_processing_errors => local_processing_errors
1091 );
1092
1093 elsif ( site_data.LONGITUDE is NOT NULL AND site_data.LATITUDE is NOT NULL ) then
1094 if (site_data.Longitude < -180 OR site_data.Longitude > 180 ) then
1095 l_row_status := 'E';
1096
1097 fnd_message.set_name('RRS', 'RRS_IMP_LONG_RANGE');
1098 fnd_message.set_token('NUM', site_data.site_identification_number);
1099 l_msg := fnd_message.get;
1100
1101 prepare_error_mesg(
1102 p_site_id => NULL
1106 -- ,p_message_text => 'Longitude should be between -180 and 180 for : '||site_data.site_identification_number
1103 ,p_site_id_num => site_data.site_identification_number
1104 ,p_column_name => 'LONGITUDE '
1105 ,p_message_name => 'MESSAGE NAME'
1107 ,p_message_text => l_msg
1108 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1109 ,p_destination_table_name => 'HZ_LOCATIONS'
1110 ,p_process_status => '3'
1111 ,p_transaction_type => site_data.transaction_type
1112 ,p_batch_id => site_data.batch_id
1113 ,p_processing_errors => local_processing_errors
1114 );
1115
1116 elsif ( site_data.latitude < -90 OR site_data.latitude > 90 ) then
1117 l_row_status := 'E';
1118
1119 fnd_message.set_name('RRS', 'RRS_IMP_LAT_RANGE');
1120 fnd_message.set_token('NUM', site_data.site_identification_number);
1121 l_msg := fnd_message.get;
1122
1123 prepare_error_mesg(
1124 p_site_id => NULL
1125 ,p_site_id_num => site_data.site_identification_number
1126 ,p_column_name => 'LATITUDE'
1127 ,p_message_name => 'MESSAGE NAME'
1128 -- ,p_message_text => 'Latitude should be between -90 and 90 for : '||site_data.site_identification_number
1129 ,p_message_text => l_msg
1130 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1131 ,p_destination_table_name => 'HZ_LOCATIONS'
1132 ,p_process_status => '3'
1133 ,p_transaction_type => site_data.transaction_type
1134 ,p_batch_id => site_data.batch_id
1135 ,p_processing_errors => local_processing_errors
1136 );
1137
1138 end if;
1139
1140 end if;
1141
1142
1143 If ( site_data.GEOMETRY_SOURCE is NOT NULL ) then
1144
1145 Check_geo_source_code( p_site_id_num => site_data.site_identification_number
1146 ,p_geo_source_code => site_data.geometry_source
1147 ,x_return_flag => x_return_flag
1148 );
1149 if x_return_flag = 'S' AND l_row_status = 'S' then
1150 null;
1151 -- dbms_output.put_line('Site use type code validation succeeded ');
1152 elsif x_return_flag = 'E' then
1153 l_row_status := 'E';
1154
1155 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_GEO_SRC');
1156 fnd_message.set_token('NUM', site_data.site_identification_number);
1157 l_msg := fnd_message.get;
1158
1159 prepare_error_mesg(
1160 p_site_id => NULL
1161 ,p_site_id_num => site_data.site_identification_number
1162 ,p_column_name => 'GEOMETRY_SOURCE'
1163 ,p_message_name => 'MESSAGE NAME'
1164 -- ,p_message_text => 'Geometry Source Code validation fails for : '||site_data.site_identification_number
1165 ,p_message_text => l_msg
1166 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1167 ,p_destination_table_name => 'HZ_LOCATIONS'
1168 ,p_process_status => '3'
1169 ,p_transaction_type => site_data.transaction_type
1170 ,p_batch_id => site_data.batch_id
1171 ,p_processing_errors => local_processing_errors
1172 );
1173 end if;
1174
1175 end if ;
1176
1177
1178 If site_data.GEOMETRY_SOURCE is NULL and site_data.LONGITUDE is NOT NULL and site_data.LATITUDE is NOT NULL then
1179 l_geo_source := 'RRS_USER_ENTERED';
1180
1181
1182 elsif site_data.GEOMETRY_SOURCE is NULL and site_data.LONGITUDE is NULL and site_data.LATITUDE is NULL then
1183 If site_data.location_status = 'N' then
1184
1185 IF (FND_PROFILE.VALUE('RRS_GEOCODE_SRC_PREFERENCE') = 'RRS_USER_ENTERED') then
1186 l_geo_source := 'RRS_USER_ENTERED';
1187 else
1188 l_geo_source := 'RRS_GOOGLE';
1189 end if;
1190
1191 elsif site_data.location_status = 'E' and l_row_status = 'S' then
1192
1193 l_upd_geo_data := 'N';
1194
1195 select geometry_source
1196 into l_db_geo_source
1197 from hz_locations
1198 where location_id = site_data.location_id;
1199
1200 If l_db_geo_source is NULL then
1201 IF (FND_PROFILE.VALUE('RRS_GEOCODE_SRC_PREFERENCE') = 'RRS_USER_ENTERED') then
1202 l_geo_source := 'RRS_USER_ENTERED';
1203 else
1204 l_geo_source := 'RRS_GOOGLE';
1205 end if;
1206 UPDATE HZ_LOCATIONS
1207 SET GEOMETRY_SOURCE = l_geo_source
1208 WHERE LOCATION_ID = site_data.location_id;
1209 end if;
1210
1211
1212 end if;
1213
1214
1215 elsif site_data.GEOMETRY_SOURCE = 'RRS_GOOGLE' and site_data.LONGITUDE is NOT NULL and site_data.LATITUDE is NOT NULL then
1216 l_row_status := 'E';
1217
1218 fnd_message.set_name('RRS', 'RRS_IMP_GEO_SRC');
1219 fnd_message.set_token('NUM', site_data.site_identification_number);
1220 l_msg := fnd_message.get;
1221
1222 prepare_error_mesg(
1223 p_site_id => NULL
1224 ,p_site_id_num => site_data.site_identification_number
1225 ,p_column_name => 'GEOMETRY_SOURCE'
1226 ,p_message_name => 'MESSAGE NAME'
1227 -- ,p_message_text => 'Longitude-Latitude should be Null for Geometry Source as System Generated for : '||site_data.site_identification_number
1228 ,p_message_text => l_msg
1229 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1230 ,p_destination_table_name => 'HZ_LOCATIONS'
1231 ,p_process_status => '3'
1232 ,p_transaction_type => site_data.transaction_type
1233 ,p_batch_id => site_data.batch_id
1234 ,p_processing_errors => local_processing_errors
1235 );
1236
1237 end if;
1238
1239 If site_data.site_type_code is NULL then
1240 l_row_status := 'E';
1241
1242 fnd_message.set_name('RRS', 'RRS_IMP_NULL_TYPE');
1243 fnd_message.set_token('NUM', site_data.site_identification_number);
1244 l_msg := fnd_message.get;
1245
1246 prepare_error_mesg(
1247 p_site_id => NULL
1248 ,p_site_id_num => site_data.site_identification_number
1249 ,p_column_name => 'SITE_TYPE_CODE'
1250 ,p_message_name => 'MESSAGE NAME'
1251 -- ,p_message_text => 'Site type code cannot be null for site_id : '||site_data.site_identification_number
1252 ,p_message_text => l_msg
1253 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1254 ,p_destination_table_name => 'RRS_SITES_B'
1255 ,p_process_status => '3'
1256 ,p_transaction_type => site_data.transaction_type
1257 ,p_batch_id => site_data.batch_id
1258 ,p_processing_errors => local_processing_errors
1259 );
1260
1261 else
1262
1263 Check_site_type_code( p_site_id_num => site_data.site_identification_number
1264 ,p_site_type_code => site_data.site_type_code
1265 ,x_return_flag => x_return_flag
1266 );
1267 if x_return_flag = 'S' AND l_row_status = 'S' then
1268 null;
1269 -- dbms_output.put_line('Site type code validation succeeded ');
1270 elsif x_return_flag = 'E' then
1271 l_row_status := 'E';
1272
1273 fnd_message.set_name('RRS', 'RRS_IMP_INVALID_TYPE');
1274 fnd_message.set_token('NUM', site_data.site_identification_number);
1275 l_msg := fnd_message.get;
1276
1277 prepare_error_mesg(
1278 p_site_id => NULL
1279 ,p_site_id_num => site_data.site_identification_number
1280 ,p_column_name => 'SITE_TYPE_CODE'
1281 ,p_message_name => 'MESSAGE NAME'
1282 -- ,p_message_text => 'Site type code validation fails for : '||site_data.site_identification_number
1283 ,p_message_text => l_msg
1284 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1285 ,p_destination_table_name => 'RRS_SITES_B'
1286 ,p_process_status => '3'
1287 ,p_transaction_type => site_data.transaction_type
1288 ,p_batch_id => site_data.batch_id
1289 ,p_processing_errors => local_processing_errors
1290 );
1291 end if;
1292 end if;
1293
1294 If (site_data.site_type_code = 'E' )
1295 AND ( site_data.le_party_id is NOT NULL OR site_data.party_site_id is NOT NULL ) then
1296
1297 l_row_status := 'E';
1298 prepare_error_mesg(
1299 p_site_id => NULL
1300 ,p_site_id_num => site_data.site_identification_number
1301 ,p_column_name => 'SITE_PARTY_ID'
1302 ,p_message_name => 'MESSAGE NAME'
1303 ,p_message_text => 'Site type code and External party validation fails for : '||site_data.site_identification_number
1304 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1305 ,p_destination_table_name => 'RRS_SITES_B'
1306 ,p_process_status => '3'
1307 ,p_transaction_type => site_data.transaction_type
1308 ,p_batch_id => site_data.batch_id
1309 ,p_processing_errors => local_processing_errors
1310 );
1311
1312 elsIf site_data.site_type_code = 'E' AND site_data.site_party_id is NOT NULL then
1313
1314
1315 Check_site_party_id( p_site_id_num => site_data.site_identification_number
1316 ,p_site_party_id => site_data.site_party_id
1317 ,x_return_flag => x_return_flag
1318 );
1319 if x_return_flag = 'S' AND l_row_status = 'S' then
1320 null;
1321 -- dbms_output.put_line('Site type code and External Party validation succeeded ');
1322 elsif x_return_flag = 'E' then
1323 l_row_status := 'E';
1324 prepare_error_mesg(
1325 p_site_id => NULL
1326 ,p_site_id_num => site_data.site_identification_number
1327 ,p_column_name => 'SITE_PARTY_ID'
1328 ,p_message_name => 'MESSAGE NAME'
1329 ,p_message_text => 'Site type code and External party validation fails for : '||site_data.site_identification_number
1330 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1331 ,p_destination_table_name => 'RRS_SITES_B'
1332 ,p_process_status => '3'
1333 ,p_transaction_type => site_data.transaction_type
1334 ,p_batch_id => site_data.batch_id
1335 ,p_processing_errors => local_processing_errors
1336 );
1337 end if;
1338 /*
1339 else
1340 l_row_status := 'E';
1341 prepare_error_mesg(
1342 p_site_id => NULL
1343 ,p_site_id_num => site_data.site_identification_number
1344 ,p_column_name => 'SITE_PARTY_ID'
1345 ,p_message_name => 'MESSAGE NAME'
1346 ,p_message_text => 'Site type code and External party validation fails for : '||site_data.site_identification_number
1347 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1348 ,p_destination_table_name => 'RRS_SITES_B'
1349 ,p_process_status => '3'
1350 ,p_transaction_type => site_data.transaction_type
1351 ,p_batch_id => site_data.batch_id
1352 ,p_processing_errors => local_processing_errors
1353 );
1354 dbms_output.put_line(' Invalid combination of Site Type Code and Site Party ID for site_id : '||site_data.site_identification_number);
1355 */
1356
1357 end if;
1358
1359 If site_data.site_type_code = 'I' AND site_data.le_party_id is NOT NULL then
1360
1361
1362 Check_le_party_id( p_site_id_num => site_data.site_identification_number
1363 ,p_le_party_id => site_data.le_party_id
1364 ,x_return_flag => x_return_flag
1365 );
1366 if x_return_flag = 'S' AND l_row_status = 'S' then
1367
1368 l_create_party := 'Y';
1369 elsif x_return_flag = 'E' then
1370 l_row_status := 'E';
1371 prepare_error_mesg(
1372 p_site_id => NULL
1373 ,p_site_id_num => site_data.site_identification_number
1374 ,p_column_name => 'LE_PARTY_ID'
1375 ,p_message_name => 'MESSAGE NAME'
1376 ,p_message_text => 'Site type code and LE party validation fails for : '||site_data.site_identification_number
1377 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1378 ,p_destination_table_name => 'RRS_SITES_B'
1379 ,p_process_status => '3'
1380 ,p_transaction_type => site_data.transaction_type
1381 ,p_batch_id => site_data.batch_id
1382 ,p_processing_errors => local_processing_errors
1383 );
1384 end if;
1385 elsIf (site_data.site_type_code = 'I' AND site_data.le_party_id is NULL ) AND (
1386 site_data.site_party_id is NOT NULL OR site_data.party_site_id is NOT NULL ) then
1387 l_row_status := 'E';
1388 prepare_error_mesg(
1389 p_site_id => NULL
1390 ,p_site_id_num => site_data.site_identification_number
1391 ,p_column_name => 'LE_PARTY_ID'
1392 ,p_message_name => 'MESSAGE NAME'
1393 ,p_message_text => 'Site type code and LE party validation fails for : '||site_data.site_identification_number
1394 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1395 ,p_destination_table_name => 'RRS_SITES_B'
1396 ,p_process_status => '3'
1397 ,p_transaction_type => site_data.transaction_type
1398 ,p_batch_id => site_data.batch_id
1399 ,p_processing_errors => local_processing_errors
1400 );
1401 /*
1402 else
1403 dbms_output.put_line(' Invalid combination of Site Type Code and Site Party ID for site_id : '||site_data.site_identification_number);
1404 */
1405
1406 end if;
1407
1408
1409
1410 -- lot of processing needs to be done here.. for party , location and party_site also.
1411
1412 -- Location Creation.
1413
1414 If l_create_location = 'Y' and l_row_status = 'S' then
1415
1416 l_location_rec.country := site_data.country;
1417 l_location_rec.address1 := site_data.address1;
1418 l_location_rec.address2 := site_data.address2;
1419 l_location_rec.address3 := site_data.address3;
1420 l_location_rec.address4 := site_data.address4;
1421 l_location_rec.city := site_data.city;
1422 l_location_rec.postal_code := site_data.postal_code;
1423 l_location_rec.state := site_data.state;
1424 l_location_rec.province := site_data.province;
1425 l_location_rec.county := site_data.county;
1426 l_location_rec.address_lines_phonetic := site_data.address_lines_phonetic;
1427 l_location_rec.created_by_module := 'RRS';
1428 l_location_rec.application_id := 718;
1429
1430
1431 hz_location_v2pub.create_location(p_init_msg_list => 'T'
1432 ,p_location_rec => l_location_rec
1433 ,p_do_addr_val => 'Y'
1434 ,x_location_id => x_location_id
1435 ,x_addr_val_status => x_addr_val_status
1436 ,x_addr_warn_msg => x_addr_warn_msg
1437 ,x_return_status => x_return_status
1438 ,x_msg_count => x_msg_count
1439 ,x_msg_data => x_msg_data
1440 );
1441
1442 If x_return_status = 'S' AND l_row_status = 'S' then
1443 null;
1444 -- dbms_output.put_line('New location ID for Site : '||site_data.site_identification_number||' is => '||x_location_id);
1445
1446 elsif x_return_status = 'E' then
1447 l_row_status := 'E';
1448 prepare_error_mesg(
1449 p_site_id => NULL
1450 ,p_site_id_num => site_data.site_identification_number
1451 ,p_column_name => 'LOCATION_ID'
1452 ,p_message_name => 'MESSAGE NAME'
1453 ,p_message_text => 'Error Creating the location for : '||site_data.site_identification_number||' '||x_msg_data
1454 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1455 ,p_destination_table_name => 'HZ_LOCATIONS'
1456 ,p_process_status => '3'
1457 ,p_transaction_type => site_data.transaction_type
1458 ,p_batch_id => site_data.batch_id
1459 ,p_processing_errors => local_processing_errors
1460 );
1461
1462 end if;
1463 End if;
1464
1465 -- Party Creation
1466
1467 If l_create_party = 'Y' and l_row_status = 'S' then
1468
1469 -- Party Creation
1470
1471 l_organization_rec.organization_name := site_data.site_identification_number;
1472 l_organization_rec.created_by_module := 'RRS';
1473
1474 hz_party_v2pub.create_organization(p_organization_rec => l_organization_rec
1475 ,p_party_usage_code => 'REAL_ESTATE'
1476 ,x_return_status => x_return_status
1477 ,x_msg_count => x_msg_count
1478 ,x_msg_data => x_msg_data
1479 ,x_party_id => x_party_id
1480 ,x_party_number => x_party_number
1481 ,x_profile_id => x_profile_id
1482 );
1483 If x_return_status = 'S' AND l_row_status = 'S' then
1484
1485 null;
1486 -- dbms_output.put_line('New Party ID for Site : '||site_data.site_identification_number||' is => '||x_party_id);
1487
1488 elsif x_return_status = 'E' then
1489 l_row_status := 'E';
1490 prepare_error_mesg(
1491 p_site_id => NULL
1492 ,p_site_id_num => site_data.site_identification_number
1493 ,p_column_name => 'SITE_PARTY_ID'
1494 ,p_message_name => 'MESSAGE NAME'
1495 ,p_message_text => 'Error Creating the Party for : '||site_data.site_identification_number
1496 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1497 ,p_destination_table_name => 'RRS_SITES_B'
1498 ,p_process_status => '3'
1499 ,p_transaction_type => site_data.transaction_type
1500 ,p_batch_id => site_data.batch_id
1501 ,p_processing_errors => local_processing_errors
1502 );
1503
1504 end if;
1505
1506
1507
1508 -- Party Site Creation
1509
1510
1511 If l_create_location = 'Y' and l_row_status = 'S' then
1512
1513
1514 l_party_site_rec.location_id := x_location_id;
1515
1516 else
1517
1518 l_party_site_rec.location_id := site_data.location_id;
1519
1520 end if;
1521
1522 l_party_site_rec.party_id := x_party_id;
1523 l_party_site_rec.identifying_address_flag := 'Y';
1524 l_party_site_rec.created_by_module := 'RRS';
1525
1526 hz_party_site_v2pub.create_party_site( p_init_msg_list => 'T'
1527 ,p_party_site_rec => l_party_site_rec
1528 ,x_party_site_id => x_party_site_id
1529 ,x_party_site_number => x_party_site_number
1530 ,x_return_status => x_return_status
1531 ,x_msg_count => x_msg_count
1532 ,x_msg_data => x_msg_data
1533 );
1534 If x_return_status = 'S' AND l_row_status = 'S' then
1535
1536 null;
1537 -- dbms_output.put_line('New Party Site ID for Site : '||site_data.site_identification_number||' is => '||x_party_site_id);
1538
1539 elsif x_return_status = 'E' then
1540 l_row_status := 'E';
1541 prepare_error_mesg(
1542 p_site_id => NULL
1543 ,p_site_id_num => site_data.site_identification_number
1544 ,p_column_name => 'PARTY_SITE_ID'
1545 ,p_message_name => 'MESSAGE NAME'
1546 ,p_message_text => 'Error Creating the Party Site for : '||site_data.site_identification_number
1547 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1548 ,p_destination_table_name => 'RRS_SITES_B'
1549 ,p_process_status => '3'
1550 ,p_transaction_type => site_data.transaction_type
1551 ,p_batch_id => site_data.batch_id
1552 ,p_processing_errors => local_processing_errors
1553 );
1554
1555 end if;
1556
1557
1558 end if;
1559
1560
1561 -- call for updating HZ_Locations for Geometry coordinates.
1562
1563 if l_upd_geo_data = 'Y' and l_row_status = 'S' then
1564 RRS_SITE_UTILS.Update_geometry_for_locations ( p_loc_id => nvl(site_data.location_id , x_location_id)
1565 ,p_lat => site_data.latitude
1566 ,p_long => site_data.longitude
1567 ,p_status => 'GOOD'
1568 ,p_geo_source => nvl(site_data.geometry_source , l_geo_source )
1569 ,x_return_status => x_return_status
1570 ,x_msg_count => x_msg_count
1571 ,x_msg_data => x_msg_data
1572 );
1573
1574 If x_return_status = 'S' AND l_row_status = 'S' then
1575 null;
1576
1577 elsif x_return_status = 'E' then
1578 l_row_status := 'E';
1579 prepare_error_mesg( p_site_id => NULL
1580 ,p_site_id_num => site_data.site_identification_number
1581 ,p_column_name => 'GEOMETRY_SOURCE'
1582 ,p_message_name => 'MESSAGE NAME'
1583 ,p_message_text => 'Error Updating the Geometry Information for : '||site_data.site_identification_number
1584 ,p_source_table_name => 'RRS_SITES_INTERFACE'
1585 ,p_destination_table_name => 'HZ_LOCATIONS'
1586 ,p_process_status => '3'
1587 ,p_transaction_type => site_data.transaction_type
1588 ,p_batch_id => site_data.batch_id
1589 ,p_processing_errors => local_processing_errors
1590 );
1591
1592 end if;
1593
1594 end if;
1595
1596
1597
1598 /********************************************
1599 Here we will check if all the validations are successful so far,
1600 we will update the process_status of this row to be Validation_succeeded
1601 ( 4 )
1602 *********************************************/
1603
1604 If l_row_status = 'S' then
1605 -- dbms_output.put_line (' Create Loaction Flag is : '||l_create_location);
1606
1607 If l_create_party = 'Y' and l_create_location = 'Y' then
1608 update rrs_sites_interface
1609 set location_id = x_location_id,
1610 site_party_id = x_party_id ,
1611 party_site_id = x_party_site_id
1612 where site_identification_number = site_data.site_identification_number
1613 and batch_id = p_batch_id
1614 and Transaction_type = G_TX_TYPE_CREATE
1615 and process_status = G_PS_IN_PROCESS
1616 and location_status = 'N';
1617
1618 elsif l_create_party = 'Y' then
1619
1620 update rrs_sites_interface
1621 set site_party_id = x_party_id ,
1622 party_site_id = x_party_site_id
1623 where site_identification_number = site_data.site_identification_number
1624 and process_status = G_PS_IN_PROCESS
1625 and batch_id = p_batch_id;
1626
1627 elsif l_create_location = 'Y' then
1628 -- dbms_output.put_line (' updating Loaction ID : '||x_location_id);
1629
1630 update rrs_sites_interface
1631 set location_id = x_location_id
1632 where site_identification_number = site_data.site_identification_number
1633 and batch_id = p_batch_id
1634 and process_status = G_PS_IN_PROCESS
1635 and Transaction_type = G_TX_TYPE_CREATE;
1636
1637 end if;
1638
1639
1640
1641 /*
1642 Update RRS_SITES_INTERFACE
1643 Set Process_status = G_PS_SUCCESS
1644 Where Batch_id = p_batch_id
1645 and process_status = G_PS_IN_PROCESS
1646 and site_identification_number = site_data.site_identification_number
1647 and transaction_type = G_TX_TYPE_CREATE;
1648 */
1649
1650 elsif l_row_status = 'E' then
1651
1652 Update RRS_SITES_INTERFACE
1653 Set Process_status = '3'
1654 Where Batch_id = p_batch_id
1655 and process_status = G_PS_IN_PROCESS
1656 and site_identification_number = site_data.site_identification_number
1657 and transaction_type = G_TX_TYPE_CREATE;
1658
1659 Write_interface_errors(p_processing_errors => local_processing_errors);
1660 else
1661
1662 /*********************
1663 * This behaviour should never happen but I am documenting for exceptional
1664 * case. Update all the rows with process_status = '2' to '1' after
1665 * completion of the processing logic. It should always return 0 rows.
1666 * *********************/
1667
1668 Update RRS_SITES_INTERFACE
1669 Set Process_status = G_PS_TO_BE_PROCESSED
1670 Where Batch_id = p_batch_id
1671 and process_status = G_PS_IN_PROCESS
1672 and transaction_type in ( G_TX_TYPE_CREATE );
1673
1674
1675 end if;
1676
1677 end;
1678
1679 End Loop;
1680
1681
1682 Create_sites(
1683 p_batch_id => p_batch_id
1684 ,p_transaction_type => G_TX_TYPE_CREATE
1685 ,p_purge_rows => p_purge_rows
1686 ,x_num_rows => x_num_rows
1687 ,x_return_status => x_return_status
1688 );
1689
1690
1691
1692 end if; /* for l_security_passed */
1693
1694
1695
1696 end; /* Validate_new_rows */
1697
1698
1699
1700 Procedure Check_site_id_num(
1701 p_site_id_num IN varchar2
1702 ,p_site_id IN varchar2
1703 ,p_transaction_type IN varchar2
1704 ,x_return_flag OUT NOCOPY varchar2
1705 ) is
1706
1707 begin
1708 If p_site_id_num is NOT NULL and p_transaction_type = G_TX_TYPE_CREATE then
1709
1710 Begin
1711 select 'E'
1712 into x_return_flag
1713 from rrs_sites_b
1714 where site_identification_number = p_site_id_num;
1715 exception
1716 when no_data_found then
1717 x_return_flag := 'S';
1718 end;
1719
1720 elsIf p_site_id_num is NOT NULL and p_transaction_type = G_TX_TYPE_UPDATE then
1721
1722 Begin
1723 select 'S'
1724 into x_return_flag
1725 from rrs_sites_b
1726 where site_identification_number = p_site_id_num;
1727 exception
1728 when no_data_found then
1729 x_return_flag := 'E';
1730 end;
1731
1732 elsif p_site_id is NOT NULL and p_site_id_num is NULL then
1733
1734 Begin
1735 select 'S'
1736 into x_return_flag
1737 from rrs_sites_b
1738 where site_id = p_site_id;
1739 exception
1740 when no_data_found then
1741 null;
1742 -- dbms_output.put_line('Returning Success 2 for p_site_id : '||p_site_id);
1743 end;
1744
1745 End if;
1746
1747 end;
1748
1749
1750
1751 Procedure Check_site_type_code(
1752 p_site_id_num IN varchar2
1753 ,p_site_type_code IN varchar2
1754 ,x_return_flag OUT NOCOPY varchar2
1755 ) is
1756 l_lookup_code varchar2(30);
1757 begin
1758
1759 begin
1760 select LOOKUP_CODE
1761 into l_lookup_code
1762 from rrs_lookups_v
1763 where LOOKUP_TYPE = 'RRS_SITE_TYPE'
1764 and LOOKUP_CODE = p_site_type_code;
1765 exception
1766 When no_data_found then
1767 x_return_flag := 'E';
1768 end;
1769 If l_lookup_code is NOT NULL Then
1770 x_return_flag := 'S';
1771 end if;
1772 end;
1773
1774
1775
1776 Procedure Check_site_status_code(
1777 p_site_id_num IN varchar2
1778 ,p_site_status_code IN varchar2
1779 ,x_return_flag OUT NOCOPY varchar2
1780 ) is
1781 l_lookup_code varchar2(30);
1782 begin
1783
1784 begin
1785 select LOOKUP_CODE
1786 into l_lookup_code
1787 from rrs_lookups_v
1788 where LOOKUP_TYPE = 'RRS_SITE_STATUS'
1789 and LOOKUP_CODE = p_site_status_code;
1790 exception
1791 When no_data_found then
1792 x_return_flag := 'E';
1793 end;
1794 If l_lookup_code is NOT NULL Then
1795 x_return_flag := 'S';
1796 end if;
1797 end;
1798
1799
1800 Procedure Check_site_brand_code(
1801 p_site_id_num IN varchar2
1802 ,p_site_brand_code IN varchar2
1803 ,x_return_flag OUT NOCOPY varchar2
1804 ) is
1805 l_lookup_code varchar2(30);
1806 begin
1807
1808 begin
1809 select Lookup_code
1810 into l_lookup_code
1811 from rrs_lookups_v
1812 where lookup_type = 'RRS_BRAND_NAME'
1813 and lookup_code = p_site_brand_code;
1814 exception
1815 when no_data_found then
1816 x_return_flag := 'E';
1817
1818 end;
1819 If l_lookup_code is NOT NULL then
1820 x_return_flag := 'S';
1821 end if;
1822 end;
1823
1824
1825 Procedure Check_site_use_type_code(
1826 p_site_id_num IN varchar2
1827 ,p_site_use_type_code IN varchar2
1828 ,x_return_flag OUT NOCOPY varchar2
1829 ) is
1830
1831 l_site_use_type_code varchar2(30);
1832
1833 begin
1834
1835
1836 begin
1837 Select LOOKUP_CODE
1838 into l_site_use_type_code
1839 From Fnd_Lookup_Values
1840 Where Lookup_Type In ('PARTY_SITE_USE_CODE' )
1841 AND View_Application_Id In ( 222 )
1842 AND Language = userenv('Lang')
1843 And Security_Group_Id = 0
1844 AND LOOKUP_CODE = p_site_use_type_code;
1845 exception
1846 When no_data_found then
1847 x_return_flag := 'E';
1848
1849 end;
1850 If l_site_use_type_code is NOT NULL then
1851 x_return_flag := 'S';
1852 end if;
1853
1854
1855 end;
1856
1857
1858
1859 Procedure Check_site_calendar_code(
1860 p_site_id_num IN varchar2
1861 ,p_site_calendar_code IN varchar2
1862 ,x_return_flag OUT NOCOPY varchar2
1863 ) is
1864 l_calendar_code varchar2(30);
1865 begin
1866
1867
1868 begin
1869 select calendar_code
1870 into l_calendar_code
1871 from BOM_CALENDARS
1872 where calendar_code = p_site_calendar_code;
1873 exception
1874 When no_data_found then
1875 x_return_flag := 'E';
1876
1877 end;
1878 If l_calendar_code is NOT NULL then
1879 x_return_flag := 'S';
1880 end if;
1881 end;
1882
1883
1884 Procedure Check_location_id(
1885 p_site_id_num IN varchar2
1886 ,p_location_id IN number
1887 ,p_country_code IN varchar2
1888 ,x_return_flag OUT NOCOPY varchar2
1889 )is
1890 l_location_id number;
1891 begin
1892
1893 begin
1894
1895 select location_id
1896 into l_location_id
1897 from hz_locations
1898 where location_id = p_location_id;
1899 /*
1900 and country = p_country_code
1901 */
1902 exception
1903 When no_data_found then
1904 x_return_flag := 'E';
1905
1906 end;
1907 If l_location_id is NOT NULL then
1908 x_return_flag := 'S';
1909 end if;
1910
1911 end;
1912
1913
1914
1915 Procedure Check_address1(
1916 p_site_id_num IN varchar2
1917 ,p_location_status IN varchar2
1918 ,p_location_id IN number
1919 ,p_country_code IN varchar2
1920 ,p_address1 IN varchar2
1921 ,x_return_flag OUT NOCOPY varchar2
1922 ) is
1923
1924 l_address1 varchar2(240);
1925 begin
1926
1927 begin
1928 select address1
1929 /*
1930 ,address2
1931 ,address3
1932 ,address4
1933 ,address_lines_phonetic
1934 ,city
1935 ,postal_code
1936 ,state
1937 ,province
1938 ,county
1939 */
1940 Into l_address1
1941 From HZ_locations
1942 where address1 = p_address1
1943 and country = p_country_code;
1944 exception
1945 When no_data_found then
1946 IF p_location_status = 'E' Then
1947 x_return_flag := 'E';
1948 elsif p_location_status = 'N' then
1949 x_return_flag := 'S';
1950 end if;
1951
1952 When too_many_rows then
1953 IF p_location_status = 'E' Then
1954 x_return_flag := 'E';
1955 elsif p_location_status = 'N' then
1956 x_return_flag := 'S';
1957 end if;
1958 When others then
1959 x_return_flag := 'E';
1960
1961
1962 end;
1963 If l_address1 is NOT NULL Then
1964 x_return_flag := 'S';
1965 end if;
1966
1967 end;
1968
1969
1970
1971
1972
1973 Procedure Check_site_party_id(
1974 p_site_id_num IN varchar2
1975 ,p_site_party_id IN number
1976 ,x_return_flag OUT NOCOPY varchar2
1977 )is
1978 l_site_party_id number;
1979
1980 Begin
1981
1982 Begin
1983 SELECT HP.PARTY_ID
1984 into l_site_party_id
1985 FROM HZ_PARTIES HP, HZ_ORGANIZATION_PROFILES HOP
1986 WHERE HP.PARTY_ID = HOP.PARTY_ID
1987 AND HP.PARTY_ID = p_site_party_id
1988 AND NVL(HOP.INTERNAL_FLAG,'N') = 'N'
1989 AND HP.PARTY_TYPE = 'ORGANIZATION'
1990 AND SYSDATE BETWEEN NVL(TRUNC(EFFECTIVE_START_DATE),TRUNC(SYSDATE)) AND NVL(EFFECTIVE_END_DATE,TRUNC(SYSDATE)+1);
1991 exception
1992 When no_data_found then
1993 x_return_flag := 'E';
1994
1995 end;
1996 If l_site_party_id is NOT NULL then
1997 x_return_flag := 'S';
1998 end if;
1999
2000 end;
2001
2002
2003 Procedure Check_le_party_id(
2004 p_site_id_num IN varchar2
2005 ,p_le_party_id IN number
2006 ,x_return_flag OUT NOCOPY varchar2
2007 )is
2008 l_le_party_id number;
2009 Begin
2010
2011 Begin
2012
2013 SELECT xep.legal_entity_id
2014 into l_le_party_id
2015 FROM xle_entity_profiles xep,
2016 xle_registrations xr,
2017 xle_jurisdictions_vl jur,
2018 hr_locations hl,
2019 hz_geographies b,
2020 hz_parties hp,
2021 xle_lookups l,
2022 xle_lookups l1
2023 WHERE l.lookup_type = 'XLE_YES_NO'
2024 AND l.lookup_code = xep.transacting_entity_flag
2025 AND xep.geography_id = b.geography_id
2026 AND xr.location_id = hl.location_id
2027 AND xr.source_id = xep.legal_entity_id
2028 AND xr.identifying_flag='Y'
2029 AND xr.source_table = 'XLE_ENTITY_PROFILES'
2030 AND jur.jurisdiction_id=xr.jurisdiction_id
2031 AND l1.lookup_type = 'XLE_REG_CODE'
2032 AND jur.registration_code_le = l1.lookup_code
2033 AND hp.party_id = xep.party_id
2034 AND SYSDATE < NVL(xep.effective_to, SYSDATE + 1)
2035 AND xep.party_id = p_le_party_id;
2036 exception
2037 When no_data_found then
2038 x_return_flag := 'E';
2039
2040 end;
2041 If l_le_party_id is NOT NULL then
2042 x_return_flag := 'S';
2043 end if;
2044
2045 end;
2046
2047
2048 Procedure Check_location_country(
2049 p_site_id_num IN varchar2
2050 ,p_location_id IN number
2051 ,p_country_code IN varchar2
2052 ,x_return_flag OUT NOCOPY varchar2
2053 ) is
2054
2055 l_country_code varchar2(3);
2056 begin
2057
2058 begin
2059 Select Territory_code
2060 into l_country_code
2061 From Fnd_Territories_Vl
2062 where Territory_code = p_country_code
2063 and obsolete_flag = 'N';
2064 exception
2065 When no_data_found then
2066 x_return_flag := 'E';
2067
2068 end;
2069 if l_country_code is NOT NULL then
2070 x_return_flag := 'S';
2071 end if;
2072
2073 end;
2074
2075 Procedure Check_geo_source_code(
2076 p_site_id_num IN varchar2
2077 ,p_geo_source_code IN varchar2
2078 ,x_return_flag OUT NOCOPY varchar2
2079 ) is
2080 l_lookup_code varchar2(30);
2081 begin
2082
2083 begin
2084 select LOOKUP_CODE
2085 into l_lookup_code
2086 from rrs_lookups_v
2087 where LOOKUP_TYPE = 'RRS_GEO_SOURCE'
2088 and LOOKUP_CODE = p_geo_source_code;
2089 exception
2090 When no_data_found then
2091 x_return_flag := 'E';
2092 end;
2093
2094
2095 If l_lookup_code is NOT NULL Then
2096 x_return_flag := 'S';
2097 end if;
2098
2099 end;
2100
2101
2102
2103 Procedure Write_interface_errors(
2104 p_processing_errors IN RRS_PROCESSING_ERRORS_TAB
2105 )is
2106 conc_status Boolean;
2107 begin
2108
2109 INSERT into RRS_INTERFACE_ERRORS(
2110 SITE_ID,
2111 SITE_IDENTIFICATION_NUMBER,
2112 COLUMN_NAME,
2113 MESSAGE_NAME,
2114 MESSAGE_TYPE,
2115 MESSAGE_TEXT,
2116 SOURCE_TABLE_NAME,
2117 DESTINATION_TABLE_NAME,
2118 CREATED_BY,
2119 CREATION_DATE,
2120 LAST_UPDATED_BY,
2121 LAST_UPDATE_DATE,
2122 LAST_UPDATE_LOGIN,
2123 REQUEST_ID,
2124 PROGRAM_APPLICATION_ID,
2125 PROGRAM_ID,
2126 PROGRAM_UPDATE_DATE,
2127 PROCESS_STATUS,
2128 TRANSACTION_TYPE,
2129 BATCH_ID
2130 )
2131 (select
2132 *
2133 from table( p_processing_errors)
2134 );
2135
2136 If ( sql%rowcount ) > 0 then
2137 FND_FILE.put_line(FND_FILE.LOG, 'Few records failed the validations. Please check the interface errors table for details. ');
2138 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: One or more rows errored due to validation checks. ');
2139 end if;
2140
2141
2142 end;
2143
2144
2145 Procedure prepare_error_mesg(
2146 p_site_id IN varchar2
2147 ,p_site_id_num IN varchar2
2148 ,p_column_name IN varchar2
2149 ,p_message_name IN varchar2
2150 ,p_message_text IN varchar2
2151 ,p_source_table_name IN varchar2
2152 ,p_destination_table_name IN varchar2
2153 ,p_process_status IN varchar2
2154 ,p_transaction_type IN varchar2
2155 ,p_batch_id IN number
2156 ,p_processing_errors IN OUT NOCOPY RRS_PROCESSING_ERRORS_TAB
2157 )is
2158 begin
2159
2160
2161 p_processing_errors.Extend();
2162 p_processing_errors(p_processing_errors.Last) := rrs_processing_errors_rec(
2163 p_site_id
2164 ,p_site_id_num
2165 ,p_column_name
2166 ,p_message_name
2167 ,'C'
2168 ,p_message_text
2169 ,p_source_table_name
2170 ,p_destination_table_name
2171 ,G_USER_ID
2172 ,sysdate
2173 ,G_USER_ID
2174 ,sysdate
2175 ,G_LOGIN_ID
2176 ,G_REQUEST_ID
2177 ,G_APPLICATION_ID
2178 ,G_PROGAM_ID
2179 ,sysdate
2180 ,p_process_status
2181 ,p_transaction_type
2182 ,p_batch_id
2183 );
2184
2185
2186 end;
2187
2188
2189 Procedure Create_sites(
2190 p_batch_id IN number
2191 ,p_transaction_type IN varchar2
2192 ,p_purge_rows IN varchar2
2193 ,x_num_rows OUT NOCOPY number
2194 ,x_return_status OUT NOCOPY varchar2
2195 )is
2196
2197 cursor c_new_sites (p_batch_id number ) is
2198 Select RSB.Site_identification_number,RSB.site_id
2199 from RRS_SITES_B RSB , RRS_SITES_INTERFACE RSI
2200 where RSB.site_identification_number = RSI.site_identification_number
2201 and RSI.batch_id = p_batch_id
2202 and RSI.process_status = G_PS_IN_PROCESS
2203 and RSI.transaction_type = G_TX_TYPE_CREATE;
2204
2205 conc_status Boolean;
2206 l_api_version Number := 1.0;
2207 l_security_enabled VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_ROLE_BASED_SECURITY_ENABLED'),'N');
2208 l_site_author_role VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_AUTO_ASSIGN_SITE_AUTHOR'),'N');
2209 l_errorcode NUMBER;
2210 l_return_status VARCHAR2(1);
2211 -- l_grant_guid FND_GRANTS.GRANT_GUID%TYPE;
2212 l_grant_guid RAW(16);
2213 l_party_id NUMBER;
2214 l_view_priv VARCHAR2(1) := 'N';
2215 l_edit_priv VARCHAR2(1) := 'N';
2216
2217 begin
2218
2219 /***********************************************
2220 * Here the code starts for copying the validation succeeded data from Interface
2221 * tables into base tables.
2222 * ************************************************/
2223
2224 Begin
2225
2226 insert into rrs_sites_b
2227 (
2228 SITE_ID
2229 ,SITE_IDENTIFICATION_NUMBER
2230 ,SITE_TYPE_CODE
2231 ,SITE_STATUS_CODE
2232 ,BRANDNAME_CODE
2233 ,CALENDAR_CODE
2234 ,LOCATION_ID
2235 ,SITE_PARTY_ID
2236 ,PARTY_SITE_ID
2237 ,LE_PARTY_ID
2238 ,IS_TEMPLATE_FLAG
2239 ,CREATED_BY
2240 ,CREATION_DATE
2241 ,LAST_UPDATED_BY
2242 ,LAST_UPDATE_DATE
2243 ,LAST_UPDATE_LOGIN
2244 )
2245 (select
2246 rrs_sites_s.nextval
2247 ,SITE_IDENTIFICATION_NUMBER
2248 ,SITE_TYPE_CODE
2249 ,SITE_STATUS_CODE
2250 ,BRANDNAME_CODE
2251 ,CALENDAR_CODE
2252 ,LOCATION_ID
2253 ,SITE_PARTY_ID
2254 ,PARTY_SITE_ID
2255 ,LE_PARTY_ID
2256 ,'N'
2257 ,G_USER_ID
2258 ,SYSDATE
2259 ,LAST_UPDATED_BY
2260 ,SYSDATE
2261 ,LAST_UPDATE_LOGIN
2262 From RRS_SITES_INTERFACE
2263 where batch_id = p_batch_id
2264 and transaction_type = G_TX_TYPE_CREATE
2265 and process_status = G_PS_IN_PROCESS
2266 ) ;
2267 Exception
2268 When Others Then
2269 Rollback;
2270 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Error: Unexpected Error occured during processing of Sites data. ');
2271
2272 End;
2273
2274 If ( sql%rowcount ) > 0 then
2275 FND_FILE.put_line(FND_FILE.LOG, 'Total No. of Sites Created : '||to_char(sql%rowcount));
2276 end if;
2277
2278 Begin
2279
2280 insert into RRS_SITES_TL(
2281 SITE_ID
2282 ,NAME
2283 ,LANGUAGE
2284 ,SOURCE_LANG
2285 ,CREATED_BY
2286 ,CREATION_DATE
2287 ,LAST_UPDATED_BY
2288 ,LAST_UPDATE_DATE
2289 ,LAST_UPDATE_LOGIN
2290 ,DESCRIPTION
2291 )
2292 (select
2293 B.site_id
2294 ,a.SITE_NAME
2295 ,L.LANGUAGE_CODE
2296 ,userenv('LANG')
2297 ,G_USER_ID
2298 ,SYSDATE
2299 ,a.LAST_UPDATED_BY
2300 ,SYSDATE
2301 ,a.LAST_UPDATE_LOGIN
2302 ,NULL
2303 From RRS_SITES_INTERFACE A ,RRS_SITES_B B, FND_LANGUAGES L
2304 where batch_id = p_batch_id
2305 and transaction_type = G_TX_TYPE_CREATE
2306 and process_status = G_PS_IN_PROCESS
2307 and A.site_identification_number = b.site_identification_number
2308 and L.INSTALLED_FLAG in ('I', 'B')
2309 and not exists
2310 (select NULL
2311 from RRS_SITES_TL T
2312 where T.SITE_ID = B.site_id
2313 and T.LANGUAGE = L.LANGUAGE_CODE)
2314 );
2315
2316 Exception
2317 When Others Then
2318 Rollback;
2319 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Error: Unexpected Error occured during processing of Sites data. ');
2320
2321 end;
2322
2323 /*
2324 If ( sql%rowcount ) > 0 then
2325 FND_FILE.put_line(FND_FILE.LOG, 'Few records failed the validations. Please check the interface errors table for details. ');
2326 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: One or more rows errored due to validation checks. ');
2327 end if;
2328 */
2329
2330
2331
2332 Begin
2333
2334 insert into RRS_SITE_USES(
2335 SITE_USE_ID
2336 ,SITE_ID
2337 ,SITE_USE_TYPE_CODE
2338 ,STATUS_CODE
2339 ,IS_PRIMARY_FLAG
2340 ,OBJECT_VERSION_NUMBER
2341 ,CREATED_BY
2342 ,CREATION_DATE
2343 ,LAST_UPDATED_BY
2344 ,LAST_UPDATE_DATE
2345 ,LAST_UPDATE_LOGIN
2346 )
2347 (
2348 Select
2349 rrs_site_uses_s.nextval
2350 ,B.SITE_ID
2351 ,A.SITE_USE_TYPE_CODE
2352 ,'A'
2353 ,'Y'
2354 ,1
2355 ,G_USER_ID
2356 ,SYSDATE
2357 ,G_USER_ID
2358 ,SYSDATE
2359 ,G_LOGIN_ID
2360 From RRS_SITES_INTERFACE A ,RRS_SITES_B B
2361 where batch_id = p_batch_id
2362 and transaction_type = G_TX_TYPE_CREATE
2363 and process_status = G_PS_IN_PROCESS
2364 and A.SITE_USE_TYPE_CODE is NOT NULL
2365 and A.site_identification_number = B.site_identification_number
2366 );
2367
2368 Exception
2369 When Others Then
2370 Rollback;
2371 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Error: Unexpected Error occured during processing of Sites data. ');
2372
2373 end;
2374
2375
2376 /* RBAC 2 write a cursor code for checking every row for Grants and then insert the data */
2377
2378
2379 if l_security_enabled = 'Y' and l_site_author_role = 'Y' then
2380
2381 select nvl(customer_id,person_party_id)
2382 into l_party_id
2383 from fnd_user
2384 where user_id = FND_GLOBAL.USER_ID;
2385
2386
2387 For c_check_grants in c_new_sites(p_batch_id) Loop
2388
2389 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => l_api_version, p_privilege => 'RRS_VIEW_SITE', p_object_name => 'RRS_SITE', p_object_key => c_check_grants.site_id, p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
2390
2391 FND_FILE.PUT_LINE(FND_FILE.LOG,'User has View Site access for Site : '||c_check_grants.site_id );
2392 l_view_priv := 'Y';
2393
2394 end if;
2395
2396 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => l_api_version, p_privilege => 'RRS_EDIT_SITE',
2397 p_object_name => 'RRS_SITE', p_object_key => c_check_grants.site_id,
2398 p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
2399
2400 l_edit_priv := 'Y';
2401
2402
2403 end if;
2404
2405 If l_view_priv = 'N' or l_edit_priv = 'N' then
2406
2407 EGO_SECURITY_PUB.grant_role_guid(
2408 p_api_version => 1.0,
2409 p_role_name => 'RRS_SITE_AUTHOR',
2410 p_object_name => 'RRS_SITE',
2411 p_instance_type => 'INSTANCE',
2412 P_INSTANCE_SET_ID=> NULL,
2413 P_INSTANCE_PK1_VALUE=>c_check_grants.site_id,
2414 P_INSTANCE_PK2_VALUE=>NULL,
2415 P_INSTANCE_PK3_VALUE=>NULL,
2416 P_INSTANCE_PK4_VALUE=>NULL,
2417 P_INSTANCE_PK5_VALUE=>NULL,
2418 p_party_id => l_party_id,
2419 p_start_date => NULL,
2420 p_end_date => NULL,
2421 x_return_status => l_return_status,
2422 x_errorcode => l_errorcode,
2423 x_grant_guid => l_grant_guid);
2424 IF l_return_status <> 'T' THEN
2425 FND_FILE.PUT_LINE(FND_FILE.LOG,'Site Author Role not Assigned to Site : '||c_check_grants.site_identification_number);
2426
2427 END IF;
2428
2429 l_view_priv := 'N';
2430 l_edit_priv := 'N';
2431
2432 end if;
2433
2434 end Loop;
2435
2436 end if;
2437
2438 /* RBAC 2 write a cursor code for checking every row for Grants and then insert the data */
2439
2440 If ( p_purge_rows = 'Y' ) THEN
2441 DELETE from RRS_SITES_INTERFACE
2442 where batch_id = p_batch_id
2443 and transaction_type = G_TX_TYPE_CREATE
2444 and process_status = G_PS_IN_PROCESS;
2445 else
2446
2447 UPDATE RRS_SITES_INTERFACE
2448 SET PROCESS_STATUS = G_PS_SUCCESS
2449 WHERE PROCESS_STATUS= G_PS_IN_PROCESS
2450 AND BATCH_ID = p_batch_id
2451 AND TRANSACTION_TYPE= G_TX_TYPE_CREATE;
2452
2453 end if;
2454
2455
2456 end;
2457
2458
2459 Procedure Validate_update_rows(
2460 p_batch_id IN number
2461 ,p_purge_rows IN varchar2
2462 ,x_return_flag OUT NOCOPY varchar2
2463 ) is
2464
2465 p_site_id varchar2(30);
2466 p_site_id_num varchar2(30);
2467 l_found varchar2(1);
2468 l_batch_id number;
2469 l_row_status varchar2(1);
2470 p_transaction_type varchar2(6);
2471
2472 l_geo_source varchar2(30);
2473
2474 cursor c_update_interface_row (l_batch_id number )is
2475 select
2476 a.SITE_ID site_id_intf
2477 ,a.SITE_IDENTIFICATION_NUMBER site_id_num_intf
2478 ,a.SITE_NAME site_name_intf
2479 ,a.SITE_TYPE_CODE site_type_code_intf
2480 ,a.SITE_STATUS_CODE site_status_code_intf
2481 ,a.SITE_USE_TYPE_CODE site_use_type_code_intf
2482 ,a.BRANDNAME_CODE brandname_code_intf
2483 ,a.CALENDAR_CODE calendar_code_intf
2484 ,a.LOCATION_STATUS location_status_intf
2485 ,a.LOCATION_ID location_id_intf
2486 ,a.SITE_PARTY_ID site_party_id_intf
2487 ,a.PARTY_SITE_ID party_site_id_intf
2488 ,a.LE_PARTY_ID le_party_id_intf
2489 ,a.ADDRESS1 address1_intf
2490 ,a.ADDRESS2 address2_intf
2494 ,a.CITY city_intf
2491 ,a.ADDRESS3 address3_intf
2492 ,a.ADDRESS4 address4_intf
2493 ,a.ADDRESS_LINES_PHONETIC address_lines_phonetic_intf
2495 ,a.POSTAL_CODE postal_code_intf
2496 ,a.STATE state_intf
2497 ,a.PROVINCE province_intf
2498 ,a.COUNTY county_intf
2499 ,a.COUNTRY country_intf
2500 ,a.GEOMETRY_SOURCE geometry_source_intf
2501 ,a.Longitude Longitude_intf
2502 ,a.Latitude Latitude_intf
2503 ,a.TRANSACTION_TYPE transaction_type_intf
2504 ,BATCH_PROCESSING
2505 ,BATCH_ID
2506 ,b.SITE_ID site_id
2507 ,b.SITE_IDENTIFICATION_NUMBER site_identification_number
2508 ,c.NAME site_name
2509 ,b.SITE_TYPE_CODE site_type_code
2510 ,b.SITE_STATUS_CODE site_status_code
2511 ,d.SITE_USE_TYPE_CODE site_use_type_code
2512 ,b.BRANDNAME_CODE brandname_code
2513 ,b.CALENDAR_CODE calendar_code
2514 ,b.LOCATION_ID location_id
2515 ,b.SITE_PARTY_ID site_party_id
2516 ,b.PARTY_SITE_ID party_site_id
2517 ,b.LE_PARTY_ID le_party_id
2518 ,h.ADDRESS1 address1
2519 ,h.ADDRESS2 address2
2520 ,h.ADDRESS3 address3
2521 ,h.ADDRESS4 address4
2522 ,h.ADDRESS_LINES_PHONETIC address_lines_phonetic
2523 ,h.CITY city
2524 ,h.POSTAL_CODE postal_code
2525 ,h.STATE state
2526 ,h.PROVINCE province
2527 ,h.COUNTY county
2528 ,h.COUNTRY country
2529 ,h.GEOMETRY_SOURCE geometry_source
2530 ,h.object_version_number
2531 FROM RRS_SITES_INTERFACE a, RRS_SITES_B B , RRS_SITES_TL C, RRS_SITE_USES D,
2532 HZ_LOCATIONS H
2533 WHERE TRANSACTION_TYPE = G_TX_TYPE_UPDATE
2534 and BATCH_ID = l_batch_id
2535 and Process_status = G_PS_IN_PROCESS
2536 and b.site_id = c.site_id
2537 and c.language = userenv('Lang')
2538 and a.site_id = d.site_id(+)
2539 and d.is_primary_flag(+) = 'Y'
2540 and h.location_id = b.location_id
2541 and a.site_identification_number = b.site_identification_number;
2542
2543
2544 local_processing_errors rrs_processing_errors_tab;
2545 p_processing_errors rrs_processing_errors_tab;
2546
2547 p_init_msg_list varchar2(1) := 'T';
2548 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2549 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2550
2551 p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
2552 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
2553
2554 p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
2555 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
2556
2557 p_party_usage_code varchar2(30);
2558
2559 p_do_addr_val varchar2(1) := 'Y';
2560 x_location_id number;
2561 x_addr_val_status varchar2(3);
2562 x_addr_warn_msg varchar2(240);
2563 x_return_status varchar2(1);
2564 x_msg_count number;
2565 x_msg_data varchar2(1000);
2566
2567 x_party_id number;
2568 x_party_number number;
2569 x_profile_id number;
2570
2571
2572 x_party_site_id number;
2573 x_party_site_number number;
2574
2575 l_update_location varchar2(1);
2576 l_create_party varchar2(1);
2577
2578 Type rrs_site_id_rec is RECORD (site_id RRS_SITES_B.SITE_ID%TYPE);
2579 Type rrs_site_id_tab is TABLE OF NUMBER;
2580 s_site_ids rrs_site_id_tab;
2581
2582 x_num_rows number;
2583 p_object_version_number number;
2584
2585 l_api_version Number := 1.0;
2586 l_security_enabled VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_ROLE_BASED_SECURITY_ENABLED'),'N');
2587 l_security_passed VARCHAR2(1) := 'Y';
2588
2589 l_msg VARCHAR2(1000);
2590
2591
2592 begin
2593
2594
2595 /**************************************************************
2596 * Before Starting all the validatios , let's mark all the rows in
2597 * Interface table with status Validation started ( 2 ). After
2598 * completion of this processing , all the rows in this batch should
2599 * have status as either Validation failed ( 3 ) or Validation succeeded
2600 * ( 4 )
2601 * **************************************************************/
2602
2603 Update RRS_SITES_INTERFACE
2604 Set Process_status = G_PS_IN_PROCESS,
2605 REQUEST_ID = G_REQUEST_ID,
2606 PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID,
2607 PROGRAM_ID = G_PROGAM_ID,
2608 PROGRAM_UPDATE_DATE = SYSDATE,
2609 CREATED_BY = G_USER_ID,
2610 CREATION_DATE = SYSDATE,
2611 LAST_UPDATED_BY = G_USER_ID,
2612 LAST_UPDATE_DATE = SYSDATE,
2613 LAST_UPDATE_LOGIN = G_LOGIN_ID
2614
2615 Where Batch_id = p_batch_id
2616 and process_status = G_PS_TO_BE_PROCESSED
2617 and transaction_type = G_TX_TYPE_UPDATE;
2618
2619 l_batch_id := p_batch_id;
2620
2621 FOR site_data IN c_update_interface_row(l_batch_id) LOOP
2622
2623
2624 p_processing_errors := rrs_processing_errors_tab();
2625 local_processing_errors := rrs_processing_errors_tab();
2626
2627 l_row_status := 'S';
2628 l_create_party := 'N';
2629 l_update_location := 'N';
2630
2631 begin
2632
2633 Check_site_id_num(
2634 p_site_id_num=>site_data.site_id_num_intf
2635 ,p_site_id=>site_data.site_id
2636 ,p_transaction_type=>site_data.transaction_type_intf
2637 ,x_return_flag=>x_return_flag
2638 );
2639 if x_return_flag = 'S' AND l_row_status = 'S' then
2640 null;
2644 prepare_error_mesg(
2641 -- dbms_output.put_line('Site Identification Number validation succeeded ');
2642 elsif x_return_flag = 'E' then
2643 l_row_status := 'E';
2645 p_site_id => NULL
2646 ,p_site_id_num => site_data.site_id_num_intf
2647 ,p_column_name => 'SITE_IDENTIFICATION_NUMBER'
2648 ,p_message_name => 'MESSAGE NAME'
2649 ,p_message_text => 'Site Identification Number '||site_data.site_id_num_intf||' does not exist, Enter an existing number.'
2650 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2651 ,p_destination_table_name => 'RRS_SITES_B'
2652 ,p_process_status => '3'
2653 ,p_transaction_type => site_data.transaction_type_intf
2654 ,p_batch_id => site_data.batch_id
2655 ,p_processing_errors => local_processing_errors
2656 );
2657 end if;
2658
2659 If site_data.site_name_intf is NULL Then
2660 l_row_status := 'E';
2661 prepare_error_mesg( p_site_id => NULL
2662 ,p_site_id_num => site_data.site_id_num_intf
2663 ,p_column_name => 'SITE_NAME'
2664 ,p_message_name => 'MESSAGE NAME'
2665 ,p_message_text => 'Site Name cannot be Null for : '||site_data.site_identification_number
2666 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2667 ,p_destination_table_name => 'RRS_SITES_TL'
2668 ,p_process_status => '3'
2669 ,p_transaction_type => site_data.transaction_type_intf
2670 ,p_batch_id => site_data.batch_id
2671 ,p_processing_errors => local_processing_errors
2672 );
2673 end if;
2674
2675
2676
2677 If site_data.site_status_code_intf is NOT NULL then
2678 Check_site_status_code( p_site_id_num => site_data.site_id_num_intf
2679 ,p_site_status_code => site_data.site_status_code_intf
2680 ,x_return_flag => x_return_flag
2681 );
2682 if x_return_flag = 'S' AND l_row_status = 'S' then
2683 null;
2684 -- dbms_output.put_line('Site status code validation succeeded ');
2685 elsif x_return_flag = 'E' then
2686 l_row_status := 'E';
2687 prepare_error_mesg(
2688 p_site_id => NULL
2689 ,p_site_id_num => site_data.site_id_num_intf
2690 ,p_column_name => 'SITE_STATUS_CODE'
2691 ,p_message_name => 'MESSAGE NAME'
2692 ,p_message_text => 'Site Status code is not valid for site_id : '||site_data.site_identification_number
2693 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2694 ,p_destination_table_name => 'RRS_SITES_B'
2695 ,p_process_status => '3'
2696 ,p_transaction_type => site_data.transaction_type_intf
2697 ,p_batch_id => site_data.batch_id
2698 ,p_processing_errors => local_processing_errors
2699 );
2700 end if;
2701
2702 else
2703 l_row_status := 'E';
2704 prepare_error_mesg(
2705 p_site_id => NULL
2706 ,p_site_id_num => site_data.site_id_num_intf
2707 ,p_column_name => 'SITE_STATUS_CODE'
2708 ,p_message_name => 'MESSAGE NAME'
2709 ,p_message_text => 'Site Status code cannot be null for site_id : '||site_data.site_id_num_intf
2710 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2711 ,p_destination_table_name => 'RRS_SITES_B'
2712 ,p_process_status => '3'
2713 ,p_transaction_type => site_data.transaction_type_intf
2714 ,p_batch_id => site_data.batch_id
2715 ,p_processing_errors => local_processing_errors
2716 );
2717
2718 end if;
2719
2720
2721 If site_data.brandname_code_intf is NOT NULL then
2722 Check_site_brand_code( p_site_id_num => site_data.site_id_num_intf
2723 ,p_site_brand_code => site_data.brandname_code_intf
2724 ,x_return_flag => x_return_flag
2725 );
2726 if x_return_flag = 'S' AND l_row_status = 'S' then
2727 null;
2728 -- dbms_output.put_line('Brandname code code validation succeeded ');
2729 elsif x_return_flag = 'E' then
2730 l_row_status := 'E';
2731 prepare_error_mesg(
2732 p_site_id => NULL
2733 ,p_site_id_num => site_data.site_id_num_intf
2734 ,p_column_name => 'BRANDNAME_CODE'
2735 ,p_message_name => 'MESSAGE NAME'
2736 ,p_message_text => 'Brandname Code validation fails for : '||site_data.site_id_num_intf
2737 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2738 ,p_destination_table_name => 'RRS_SITES_B'
2739 ,p_process_status => '3'
2740 ,p_transaction_type => site_data.transaction_type_intf
2741 ,p_batch_id => site_data.batch_id
2742 ,p_processing_errors => local_processing_errors
2743 );
2744 end if;
2745
2746 end if;
2747
2748
2749 if site_data.calendar_code_intf is NOT NULL then
2750 Check_site_calendar_code( p_site_id_num => site_data.site_id_num_intf
2751 ,p_site_calendar_code => site_data.calendar_code_intf
2752 ,x_return_flag => x_return_flag
2753 );
2754 if x_return_flag = 'S' AND l_row_status = 'S' then
2755 null;
2756 -- dbms_output.put_line('Calendar code validation succeeded ');
2757 elsif x_return_flag = 'E' then
2758 l_row_status := 'E';
2759 prepare_error_mesg(
2760 p_site_id => NULL
2761 ,p_site_id_num => site_data.site_id_num_intf
2762 ,p_column_name => 'CALENDAR_CODE'
2763 ,p_message_name => 'MESSAGE NAME'
2767 ,p_process_status => '3'
2764 ,p_message_text => 'Calendar Code validation fails for : '||site_data.site_id_num_intf
2765 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2766 ,p_destination_table_name => 'RRS_SITES_B'
2768 ,p_transaction_type => site_data.transaction_type_intf
2769 ,p_batch_id => site_data.batch_id
2770 ,p_processing_errors => local_processing_errors
2771 );
2772 end if;
2773
2774 end if;
2775
2776 If site_data.site_use_type_code IS NULL Then
2777 x_return_flag := 'S';
2778
2779 elsif site_data.site_use_type_code is NOT NULL and ( site_data.site_use_type_code <> site_data.site_use_type_code_intf ) then
2780 l_row_status := 'E';
2781 prepare_error_mesg(
2782 p_site_id => NULL
2783 ,p_site_id_num => site_data.site_id_num_intf
2784 ,p_column_name => 'SITE_USE_TYPE_CODE'
2785 ,p_message_name => 'MESSAGE NAME'
2786 ,p_message_text => 'Site Use Type Code ( Purpose ) Code cannot be changed for : '||site_data.site_id_num_intf
2787 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2788 ,p_destination_table_name => 'RRS_SITE_USES'
2789 ,p_process_status => '3'
2790 ,p_transaction_type => site_data.transaction_type_intf
2791 ,p_batch_id => site_data.batch_id
2792 ,p_processing_errors => local_processing_errors
2793 );
2794
2795 end if;
2796
2797
2798 if site_data.location_status_intf = 'E' and site_data.Location_id IS NOT NULL and site_data.address1_intf is NOT NULL then
2799
2800
2801 If ( site_data.address1_intf <> site_data.address1 OR
2802 site_data.address2_intf <> site_data.address2 OR
2803 site_data.address3_intf <> site_data.address3 OR
2804 site_data.address4_intf <> site_data.address4 OR
2805 site_data.address_lines_phonetic_intf <> site_data.address_lines_phonetic OR
2806 site_data.city_intf <> site_data.city OR
2807 site_data.postal_code_intf <> site_data.postal_code OR
2808 site_data.state_intf <> site_data.state OR
2809 site_data.province_intf <> site_data.province OR
2810 site_data.county_intf <> site_data.county
2811 ) Then
2812
2813
2814 l_update_location := 'Y';
2815
2816 /*
2817 update_address( p_site_id_num => site_data.site_id_num_intf
2818 ,p_location_id => site_data.location_id_intf
2819 ,p_location_status => site_data.location_status_intf
2820 ,p_country_code => site_data.country_intf
2821 ,p_address1 => site_data.address1_intf
2822 ,x_return_flag => x_return_flag
2823 );
2824 if x_return_flag = 'S' AND l_row_status = 'S' then
2825 dbms_output.put_line('Location ID validation succeeded 2');
2826 elsif x_return_flag = 'E' then
2827 l_row_status := 'E';
2828 prepare_error_mesg(
2829 p_site_id => NULL
2830 ,p_site_id_num => site_data.site_id_num_intf
2831 ,p_column_name => 'ADDRESS1'
2832 ,p_message_name => 'MESSAGE NAME'
2833 ,p_message_text => 'Location Status and Address combination is not valid for : '||site_data.site_id_num_intf
2834 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2835 ,p_destination_table_name => 'HZ_LOCATIONS'
2836 ,p_process_status => '3'
2837 ,p_transaction_type => site_data.transaction_type_intf
2838 ,p_batch_id => site_data.batch_id
2839 ,p_processing_errors => local_processing_errors
2840 );
2841 end if;
2842 */
2843 end if;
2844 else
2845 l_row_status := 'E';
2846 prepare_error_mesg(
2847 p_site_id => NULL
2848 ,p_site_id_num => site_data.site_id_num_intf
2849 ,p_column_name => 'LOCATION_STATUS'
2850 ,p_message_name => 'MESSAGE NAME'
2851 ,p_message_text => 'Location Status cannot be null or New for : '||site_data.site_id_num_intf
2852 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2853 ,p_destination_table_name => 'HZ_LOCATIONS'
2854 ,p_process_status => '3'
2855 ,p_transaction_type => site_data.transaction_type_intf
2856 ,p_batch_id => site_data.batch_id
2857 ,p_processing_errors => local_processing_errors
2858 );
2859
2860 End if;
2861
2862
2863
2864 If ( ( site_data.LONGITUDE_intf is NOT NULL AND site_data.LATITUDE_intf is NULL ) OR
2865 ( site_data.LONGITUDE_intf is NULL and site_data.LATITUDE_intf is NOT NULL )) Then
2866
2867 l_row_status := 'E';
2868 prepare_error_mesg(
2869 p_site_id => NULL
2870 ,p_site_id_num => site_data.site_id_num_intf
2871 ,p_column_name => 'LONGITUDE LATITUDE'
2872 ,p_message_name => 'MESSAGE NAME'
2873 -- ,p_message_text => 'Both longitude and latitude should be either Null or Not Null for : '||site_data.site_id_num_intf
2874 ,p_message_text => l_msg||' for '||site_data.site_id_num_intf
2875 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2876 ,p_destination_table_name => 'HZ_LOCATIONS'
2877 ,p_process_status => '3'
2878 ,p_transaction_type => site_data.transaction_type_intf
2879 ,p_batch_id => site_data.batch_id
2880 ,p_processing_errors => local_processing_errors
2881 );
2885 prepare_error_mesg(
2882 elsif ( site_data.LONGITUDE_intf is NOT NULL AND site_data.LATITUDE_intf is NOT NULL ) then
2883 if (site_data.Longitude_intf < -180 OR site_data.Longitude_intf > 180 ) then
2884 l_row_status := 'E';
2886 p_site_id => NULL
2887 ,p_site_id_num => site_data.site_id_num_intf
2888 ,p_column_name => 'LONGITUDE '
2889 ,p_message_name => 'MESSAGE NAME'
2890 ,p_message_text => 'Longitude should be between -180 and 180 for : '||site_data.site_id_num_intf
2891 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2892 ,p_destination_table_name => 'HZ_LOCATIONS'
2893 ,p_process_status => '3'
2894 ,p_transaction_type => site_data.transaction_type_intf
2895 ,p_batch_id => site_data.batch_id
2896 ,p_processing_errors => local_processing_errors
2897 );
2898
2899 elsif ( site_data.latitude_intf < -90 OR site_data.latitude_intf > 90 ) then
2900 l_row_status := 'E';
2901 prepare_error_mesg(
2902 p_site_id => NULL
2903 ,p_site_id_num => site_data.site_id_num_intf
2904 ,p_column_name => 'LATITUDE'
2905 ,p_message_name => 'MESSAGE NAME'
2906 ,p_message_text => 'Latitude should be between -90 and 90 for : '||site_data.site_id_num_intf
2907 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2908 ,p_destination_table_name => 'HZ_LOCATIONS'
2909 ,p_process_status => '3'
2910 ,p_transaction_type => site_data.transaction_type_intf
2911 ,p_batch_id => site_data.batch_id
2912 ,p_processing_errors => local_processing_errors
2913 );
2914
2915 end if;
2916
2917
2918 end if;
2919
2920
2921 If ( site_data.GEOMETRY_SOURCE_intf is NOT NULL ) then
2922
2923 Check_geo_source_code( p_site_id_num => site_data.site_id_num_intf
2924 ,p_geo_source_code => site_data.geometry_source_intf
2925 ,x_return_flag => x_return_flag
2926 );
2927 if x_return_flag = 'S' AND l_row_status = 'S' then
2928 null;
2929 elsif x_return_flag = 'E' then
2930 l_row_status := 'E';
2931 prepare_error_mesg(
2932 p_site_id => NULL
2933 ,p_site_id_num => site_data.site_id_num_intf
2934 ,p_column_name => 'GEOMETRY_SOURCE'
2935 ,p_message_name => 'MESSAGE NAME'
2936 ,p_message_text => 'Geometry Source Code validation fails for : '||site_data.site_id_num_intf
2937 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2938 ,p_destination_table_name => 'HZ_LOCATIONS'
2939 ,p_process_status => '3'
2940 ,p_transaction_type => site_data.transaction_type_intf
2941 ,p_batch_id => site_data.batch_id
2942 ,p_processing_errors => local_processing_errors
2943 );
2944 end if;
2945
2946 end if ;
2947
2948
2949 If site_data.GEOMETRY_SOURCE_intf is NULL and site_data.LONGITUDE_intf is NOT NULL and site_data.LATITUDE_intf is NOT NULL then
2950 l_geo_source := 'RRS_USER_ENTERED';
2951
2952
2953 elsif site_data.GEOMETRY_SOURCE_intf is NULL and site_data.LONGITUDE_intf is NULL and site_data.LATITUDE_intf is NULL then
2954
2955 l_geo_source := 'RRS_GOOGLE';
2956
2957
2958 elsif site_data.GEOMETRY_SOURCE_intf = 'RRS_GOOGLE' and site_data.LONGITUDE_intf is NOT NULL and site_data.LATITUDE_intf is NOT NULL then
2959 l_row_status := 'E';
2960 prepare_error_mesg(
2961 p_site_id => NULL
2962 ,p_site_id_num => site_data.site_id_num_intf
2963 ,p_column_name => 'GEOMETRY_SOURCE'
2964 ,p_message_name => 'MESSAGE NAME'
2965 ,p_message_text => 'Longitude-Latitude should be Null for Geometry Source as System Generated for : '||site_data.site_id_num_intf
2966 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2967 ,p_destination_table_name => 'HZ_LOCATIONS'
2968 ,p_process_status => '3'
2969 ,p_transaction_type => site_data.transaction_type_intf
2970 ,p_batch_id => site_data.batch_id
2971 ,p_processing_errors => local_processing_errors
2972 );
2973
2974 end if;
2975
2976
2977
2978
2979 If site_data.site_type_code_intf is NULL then
2980 l_row_status := 'E';
2981 prepare_error_mesg(
2982 p_site_id => NULL
2983 ,p_site_id_num => site_data.site_id_num_intf
2984 ,p_column_name => 'SITE_TYPE_CODE'
2985 ,p_message_name => 'MESSAGE NAME'
2986 ,p_message_text => 'Site type code cannot be null for site_id : '||site_data.site_id_num_intf
2987 ,p_source_table_name => 'RRS_SITES_INTERFACE'
2988 ,p_destination_table_name => 'RRS_SITES_B'
2989 ,p_process_status => '3'
2990 ,p_transaction_type => site_data.transaction_type_intf
2991 ,p_batch_id => site_data.batch_id
2992 ,p_processing_errors => local_processing_errors
2993 );
2994
2995 elsif site_data.site_type_code is NOT NULL and site_data.site_party_id is NOT NULL and site_data.site_type_code <> site_data.site_type_code_intf then
2996 l_row_status := 'E';
2997 prepare_error_mesg(
2998 p_site_id => NULL
2999 ,p_site_id_num => site_data.site_id_num_intf
3000 ,p_column_name => 'SITE_TYPE_CODE'
3001 ,p_message_name => 'MESSAGE NAME'
3002 ,p_message_text => 'Site type code cannot be changed for site_id : '||site_data.site_id_num_intf
3003 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3004 ,p_destination_table_name => 'RRS_SITES_B'
3008 ,p_processing_errors => local_processing_errors
3005 ,p_process_status => '3'
3006 ,p_transaction_type => site_data.transaction_type_intf
3007 ,p_batch_id => site_data.batch_id
3009 );
3010
3011
3012 elsif site_data.site_type_code = 'E' AND (site_data.party_site_id_intf IS NOT NULL OR site_data.le_party_id_intf is NOT NULL ) then
3013 l_row_status := 'E';
3014 prepare_error_mesg(
3015 p_site_id => NULL
3016 ,p_site_id_num => site_data.site_id_num_intf
3017 ,p_column_name => 'SITE_TYPE_CODE'
3018 ,p_message_name => 'MESSAGE NAME'
3019 ,p_message_text => 'Site type code cannot be changed for site_id : '||site_data.site_id_num_intf
3020 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3021 ,p_destination_table_name => 'RRS_SITES_B'
3022 ,p_process_status => '3'
3023 ,p_transaction_type => site_data.transaction_type_intf
3024 ,p_batch_id => site_data.batch_id
3025 ,p_processing_errors => local_processing_errors
3026 );
3027
3028
3029 elsif ( site_data.site_type_code = 'E' and site_data.site_party_id is NOT NULL AND site_data.site_party_id_intf <> site_data.site_party_id ) then
3030 l_row_status := 'E';
3031 prepare_error_mesg(
3032 p_site_id => NULL
3033 ,p_site_id_num => site_data.site_id_num_intf
3034 ,p_column_name => 'SITE_PARTY_ID'
3035 ,p_message_name => 'MESSAGE NAME'
3036 ,p_message_text => 'Site Party ID cannot be changed for : '||site_data.site_id_num_intf
3037 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3038 ,p_destination_table_name => 'RRS_SITES_B'
3039 ,p_process_status => '3'
3040 ,p_transaction_type => site_data.transaction_type_intf
3041 ,p_batch_id => site_data.batch_id
3042 ,p_processing_errors => local_processing_errors
3043 );
3044
3045
3046 elsif site_data.site_type_code = 'E' and site_data.site_party_id is NULL AND site_data.site_party_id_intf IS NOT NULL then
3047
3048 Check_site_party_id( p_site_id_num => site_data.site_id_num_intf
3049 ,p_site_party_id => site_data.site_party_id_intf
3050 ,x_return_flag => x_return_flag
3051 );
3052 if x_return_flag = 'S' AND l_row_status = 'S' then
3053 null;
3054 -- dbms_output.put_line('Site type code and External Party validation succeeded ');
3055 elsif x_return_flag = 'E' then
3056 l_row_status := 'E';
3057 prepare_error_mesg(
3058 p_site_id => NULL
3059 ,p_site_id_num => site_data.site_id_num_intf
3060 ,p_column_name => 'SITE_PARTY_ID'
3061 ,p_message_name => 'MESSAGE NAME'
3062 ,p_message_text => 'Site type code and External party validation fails for : '||site_data.site_id_num_intf
3063 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3064 ,p_destination_table_name => 'RRS_SITES_B'
3065 ,p_process_status => '3'
3066 ,p_transaction_type => site_data.transaction_type_intf
3067 ,p_batch_id => site_data.batch_id
3068 ,p_processing_errors => local_processing_errors
3069 );
3070 end if;
3071
3072 elsif ( site_data.site_type_code = 'I' and site_data.site_party_id is NOT NULL AND site_data.le_party_id_intf <> site_data.le_party_id ) then
3073 l_row_status := 'E';
3074 prepare_error_mesg(
3075 p_site_id => NULL
3076 ,p_site_id_num => site_data.site_id_num_intf
3077 ,p_column_name => 'LE_PARTY_ID'
3078 ,p_message_name => 'MESSAGE NAME'
3079 ,p_message_text => 'LE Party ID cannot be changed for : '||site_data.site_id_num_intf
3080 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3081 ,p_destination_table_name => 'RRS_SITES_B'
3082 ,p_process_status => '3'
3083 ,p_transaction_type => site_data.transaction_type_intf
3084 ,p_batch_id => site_data.batch_id
3085 ,p_processing_errors => local_processing_errors
3086 );
3087
3088
3089 elsif site_data.site_type_code = 'I' and site_data.le_party_id is NULL AND site_data.le_party_id_intf IS NOT NULL then
3090
3091 Check_le_party_id( p_site_id_num => site_data.site_id_num_intf
3092 ,p_le_party_id => site_data.le_party_id_intf
3093 ,x_return_flag => x_return_flag
3094 );
3095 if x_return_flag = 'S' AND l_row_status = 'S' then
3096
3097 l_create_party := 'Y';
3098 elsif x_return_flag = 'E' then
3099 l_row_status := 'E';
3100 prepare_error_mesg(
3104 ,p_message_name => 'MESSAGE NAME'
3101 p_site_id => NULL
3102 ,p_site_id_num => site_data.site_id_num_intf
3103 ,p_column_name => 'LE_PARTY_ID'
3105 ,p_message_text => 'Site type code and LE party validation fails for : '||site_data.site_id_num_intf
3106 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3107 ,p_destination_table_name => 'RRS_SITES_B'
3108 ,p_process_status => '3'
3109 ,p_transaction_type => site_data.transaction_type_intf
3110 ,p_batch_id => site_data.batch_id
3111 ,p_processing_errors => local_processing_errors
3112 );
3113 end if;
3114 /*
3115 * else
3116 * dbms_output.put_line(' Invalid combination of Site Type Code and Site
3117 * Party ID for site_id : '||site_data.site_identification_number);
3118 * */
3119
3120 elsif site_data.site_type_code = 'I' and site_data.le_party_id is NULL AND site_data.le_party_id_intf IS NULL
3121 AND ( site_data.site_party_id_intf is NOT NULL OR site_data.party_site_id_intf is NOT NULL ) then
3122 l_row_status := 'E';
3123 prepare_error_mesg(
3124 p_site_id => NULL
3125 ,p_site_id_num => site_data.site_id_num_intf
3126 ,p_column_name => 'LE_PARTY_ID'
3127 ,p_message_name => 'MESSAGE NAME'
3128 ,p_message_text => 'Site type code and Party validation fails for : '||site_data.site_id_num_intf
3129 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3130 ,p_destination_table_name => 'RRS_SITES_B'
3131 ,p_process_status => '3'
3132 ,p_transaction_type => site_data.transaction_type_intf
3133 ,p_batch_id => site_data.batch_id
3134 ,p_processing_errors => local_processing_errors
3135 );
3136
3137 else
3138
3139 Check_site_type_code( p_site_id_num => site_data.site_id_num_intf
3140 ,p_site_type_code => site_data.site_type_code_intf
3141 ,x_return_flag => x_return_flag
3142 );
3143 if x_return_flag = 'S' AND l_row_status = 'S' then
3144 null;
3145 -- dbms_output.put_line('Site type code validation succeeded ');
3146 elsif x_return_flag = 'E' then
3147 l_row_status := 'E';
3148 prepare_error_mesg(
3149 p_site_id => NULL
3150 ,p_site_id_num => site_data.site_id_num_intf
3151 ,p_column_name => 'SITE_TYPE_CODE'
3152 ,p_message_name => 'MESSAGE NAME'
3153 ,p_message_text => 'Site type code validation fails for : '||site_data.site_id_num_intf
3154 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3155 ,p_destination_table_name => 'RRS_SITES_B'
3156 ,p_process_status => '3'
3157 ,p_transaction_type => site_data.transaction_type_intf
3158 ,p_batch_id => site_data.batch_id
3159 ,p_processing_errors => local_processing_errors
3160 );
3161 end if;
3162 end if;
3163
3164
3165 /* RBAC 2 validations start */
3166 if l_security_enabled = 'Y' then
3167
3168
3169 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => l_api_version, p_privilege => 'RRS_VIEW_SITE', p_object_name => 'RRS_SITE', p_object_key => site_data.site_id_intf, p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
3170
3171 null;
3172 else
3173 l_row_status := 'E';
3174 prepare_error_mesg(
3175 p_site_id => NULL
3176 ,p_site_id_num => site_data.site_id_num_intf
3177 ,p_column_name => 'SITE_ID'
3178 ,p_message_name => 'MESSAGE NAME'
3179 ,p_message_text => 'User does not have View Privileges on Site : '||site_data.site_id_num_intf
3180 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3181 ,p_destination_table_name => 'RRS_SITES_B'
3182 ,p_process_status => '3'
3183 ,p_transaction_type => site_data.transaction_type_intf
3184 ,p_batch_id => site_data.batch_id
3185 ,p_processing_errors => local_processing_errors
3186 );
3187
3188
3189 end if;
3190
3191 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => l_api_version, p_privilege => 'RRS_EDIT_SITE',
3192 p_object_name => 'RRS_SITE', p_object_key => site_data.site_id_intf,
3193 p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
3194
3195 null;
3196 else
3197 l_row_status := 'E';
3198 prepare_error_mesg(
3199 p_site_id => NULL
3200 ,p_site_id_num => site_data.site_id_num_intf
3201 ,p_column_name => 'SITE_ID'
3202 ,p_message_name => 'MESSAGE NAME'
3206 ,p_process_status => '3'
3203 ,p_message_text => 'User does not have Edit Privileges on Site : '||site_data.site_id_num_intf
3204 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3205 ,p_destination_table_name => 'RRS_SITES_B'
3207 ,p_transaction_type => site_data.transaction_type_intf
3208 ,p_batch_id => site_data.batch_id
3209 ,p_processing_errors => local_processing_errors
3210 );
3211
3212
3213 end if;
3214
3215
3216
3217 end if;
3218
3219 /* RBAC 2 validations end */
3220
3221
3222 If l_update_location = 'Y' and l_row_status = 'S' then
3223
3224
3225 l_location_rec.location_id := site_data.location_id;
3226 l_location_rec.country := site_data.country;
3227 l_location_rec.address1 := site_data.address1_intf;
3228 l_location_rec.address2 := site_data.address2_intf;
3229 l_location_rec.address3 := site_data.address3_intf;
3230 l_location_rec.address4 := site_data.address4_intf;
3231 l_location_rec.city := site_data.city_intf;
3232 l_location_rec.postal_code := site_data.postal_code_intf;
3233 l_location_rec.state := site_data.state_intf;
3234 l_location_rec.province := site_data.province_intf;
3235 l_location_rec.county := site_data.county_intf;
3236 l_location_rec.address_lines_phonetic := site_data.address_lines_phonetic_intf;
3237
3238
3239 hz_location_v2pub.update_location(p_init_msg_list => 'T'
3240 ,p_location_rec => l_location_rec
3241 ,p_do_addr_val => 'Y'
3242 ,p_object_version_number => site_data.object_version_number
3243 ,x_addr_val_status => x_addr_val_status
3244 ,x_addr_warn_msg => x_addr_warn_msg
3245 ,x_return_status => x_return_status
3246 ,x_msg_count => x_msg_count
3247 ,x_msg_data => x_msg_data
3248 );
3249
3250 If x_return_status = 'S' AND l_row_status = 'S' then
3251 null;
3252 -- dbms_output.put_line('location updated for Site : '||site_data.site_identification_number);
3253
3254 else
3255 l_row_status := 'E';
3256
3257 end if;
3258
3259
3260 end if;
3261
3262
3263 -- Party Creation
3264
3265 If l_create_party = 'Y' and l_row_status = 'S' then
3266
3267 -- Party Creation
3268
3269 l_organization_rec.organization_name := site_data.site_identification_number;
3270 l_organization_rec.created_by_module := 'RRS';
3271
3272 hz_party_v2pub.create_organization(p_organization_rec => l_organization_rec
3273 ,p_party_usage_code => 'REAL_ESTATE'
3274 ,x_return_status => x_return_status
3275 ,x_msg_count => x_msg_count
3276 ,x_msg_data => x_msg_data
3277 ,x_party_id => x_party_id
3278 ,x_party_number => x_party_number
3279 ,x_profile_id => x_profile_id
3280 );
3281 If x_return_status = 'S' AND l_row_status = 'S' then
3282
3283 null;
3284 -- dbms_output.put_line('New Party ID for Site : '||site_data.site_identification_number||' is => '||x_party_id);
3285
3286 else
3287 l_row_status := 'E';
3288
3289 end if;
3290
3291
3292
3293 -- Party Site Creation
3294
3295
3296 l_party_site_rec.location_id := site_data.location_id;
3297
3298 l_party_site_rec.party_id := x_party_id;
3299 l_party_site_rec.identifying_address_flag := 'Y';
3300 l_party_site_rec.created_by_module := 'RRS';
3301
3302 hz_party_site_v2pub.create_party_site( p_init_msg_list => 'T'
3303 ,p_party_site_rec => l_party_site_rec
3304 ,x_party_site_id => x_party_site_id
3305 ,x_party_site_number => x_party_site_number
3306 ,x_return_status => x_return_status
3307 ,x_msg_count => x_msg_count
3308 ,x_msg_data => x_msg_data
3309 );
3310 If x_return_status = 'S' AND l_row_status = 'S' then
3311
3312 null;
3313 -- dbms_output.put_line('New Party Site ID for Site : '||site_data.site_identification_number||' is => '||x_party_site_id);
3314
3315 else
3316 l_row_status := 'E';
3317
3318 end if;
3319
3320
3321 end if;
3322
3323 If l_row_status = 'S' then
3324 -- call for updating HZ_Locations for Geometry coordinates.
3325 RRS_SITE_UTILS.Update_geometry_for_locations ( p_loc_id => site_data.location_id
3326 ,p_lat => site_data.latitude_intf
3327 ,p_long => site_data.longitude_intf
3328 ,p_status => 'GOOD'
3329 ,p_geo_source => nvl(site_data.geometry_source_intf , l_geo_source )
3330 ,x_return_status => x_return_status
3331 ,x_msg_count => x_msg_count
3332 ,x_msg_data => x_msg_data
3333 );
3334
3335 If x_return_status = 'S' AND l_row_status = 'S' then
3336 null;
3337
3338 elsif x_return_status = 'E' then
3339 l_row_status := 'E';
3340 prepare_error_mesg( p_site_id => NULL
3341 ,p_site_id_num => site_data.site_id_num_intf
3342 ,p_column_name => 'GEOMETRY_SOURCE'
3343 ,p_message_name => 'MESSAGE NAME'
3344 ,p_message_text => 'Error Updating the Geometry Information for : '||site_data.site_id_num_intf
3345 ,p_source_table_name => 'RRS_SITES_INTERFACE'
3346 ,p_destination_table_name => 'HZ_LOCATIONS'
3347 ,p_process_status => '3'
3348 ,p_transaction_type => site_data.transaction_type_intf
3349 ,p_batch_id => site_data.batch_id
3350 ,p_processing_errors => local_processing_errors
3351 );
3352
3353 end if;
3354 end if;
3355
3356
3357
3358 /********************************************
3359 Here we will check if all the validations are successful so far,
3360 we will update the process_status of this row to be Validation_succeeded
3361 ( 4 )
3362 *********************************************/
3363
3364 If l_row_status = 'S' then
3365
3366 if l_create_party = 'Y' then
3367
3368 update rrs_sites_interface
3369 set site_party_id = x_party_id ,
3370 party_site_id = x_party_site_id
3371 where site_identification_number = site_data.site_id_num_intf;
3372 end if;
3373
3374 /*
3375 Update RRS_SITES_INTERFACE
3376 Set Process_status = G_PS_SUCCESS
3377 Where Batch_id = p_batch_id
3378 and process_status = G_PS_IN_PROCESS
3379 and site_identification_number = site_data.site_id_num_intf
3380 and transaction_type = G_TX_TYPE_UPDATE;
3381 */
3382
3383 elsif l_row_status = 'E' then
3384
3385 Update RRS_SITES_INTERFACE
3386 Set Process_status = '3'
3387 Where Batch_id = p_batch_id
3388 and process_status = G_PS_IN_PROCESS
3389 and site_identification_number = site_data.site_id_num_intf
3390 and transaction_type = G_TX_TYPE_UPDATE;
3391
3392 Write_interface_errors(p_processing_errors => local_processing_errors);
3393 else
3394
3395 /*********************
3396 * This behaviour should never happen but I am documenting for exceptional
3397 * case. Update all the rows with process_status = '2' to '1' after
3398 * completion of the processing logic. It should always return 0 rows.
3399 * *********************/
3400
3401 Update RRS_SITES_INTERFACE
3402 Set Process_status = G_PS_TO_BE_PROCESSED
3403 Where Batch_id = p_batch_id
3404 and process_status = G_PS_IN_PROCESS
3405 and transaction_type in ( G_TX_TYPE_UPDATE );
3406
3407 end if;
3408
3409 end;
3410
3411
3412 End Loop;
3413
3414 Update_sites(
3415 p_batch_id => p_batch_id
3416 ,p_transaction_type => G_TX_TYPE_UPDATE
3417 ,p_purge_rows => p_purge_rows
3418 ,x_num_rows => x_num_rows
3419 ,x_return_status => x_return_status
3420 );
3421
3422
3423
3424 end;
3425
3426 Procedure Update_sites(
3427 p_batch_id IN number
3428 ,p_transaction_type IN varchar2
3429 ,p_purge_rows IN varchar2
3430 ,x_num_rows OUT NOCOPY number
3431 ,x_return_status OUT NOCOPY varchar2
3432 )is
3433
3434
3435 begin
3436
3437 /***********************************************
3438 * Here the code starts for copying the validation succeeded data from
3439 * Interface tables into base tables.
3440 * ************************************************/
3441
3442
3443 Update RRS_SITES_B A
3444 Set (
3445 A.SITE_TYPE_CODE, A.SITE_STATUS_CODE , A.BRANDNAME_CODE , A.CALENDAR_CODE , A.SITE_PARTY_ID , A.PARTY_SITE_ID ,
3446 A.LE_PARTY_ID , A.OBJECT_VERSION_NUMBER , A.LAST_UPDATED_BY , A.LAST_UPDATE_DATE
3447 ) =
3448 (SELECT
3449 B.SITE_TYPE_CODE , B.SITE_STATUS_CODE, B.BRANDNAME_CODE, B.CALENDAR_CODE, B.SITE_PARTY_ID, B.PARTY_SITE_ID,
3450 B.LE_PARTY_ID, A.OBJECT_VERSION_NUMBER + 1, B.LAST_UPDATED_BY, sysdate
3451 From RRS_SITES_INTERFACE B
3452 where A.site_identification_number = B.site_identification_number
3453 and B.Batch_id = p_batch_id
3454 and B.PROCESS_STATUS = G_PS_IN_PROCESS
3455 and B.Transaction_type = G_TX_TYPE_UPDATE )
3456 Where A.site_identification_number in (select C.site_identification_number
3457 from RRS_SITES_INTERFACE C
3458 Where C.Batch_id = p_batch_id
3459 and C.Process_status = G_PS_IN_PROCESS
3460 and C.Transaction_type = G_TX_TYPE_UPDATE );
3461
3462 If ( sql%rowcount ) > 0 then
3463 FND_FILE.put_line(FND_FILE.LOG, 'Total No. of Sites Updated : '||to_char(sql%rowcount));
3464 end if;
3465
3466
3467 Update RRS_SITES_TL A
3468 Set Name = (select site_name from RRS_SITES_INTERFACE B, RRS_SITES_B RSB
3469 where RSB.site_identification_number = B.site_identification_number
3470 and RSB.Site_id = A.Site_id
3471 and B.Batch_id = p_batch_id
3472 and B.PROCESS_STATUS = G_PS_IN_PROCESS
3473 and B.Transaction_type = G_TX_TYPE_UPDATE ),
3474 SOURCE_LANG = userenv('LANG')
3475 Where A.site_id in (select RSB1.site_id
3476 from RRS_SITES_INTERFACE C, RRS_SITES_B RSB1
3477 Where C.Batch_id = p_batch_id
3478 and RSB1.site_identification_number = C.site_identification_number
3479 and C.Process_status = G_PS_IN_PROCESS
3480 and C.Transaction_type = G_TX_TYPE_UPDATE )
3481 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
3482
3483
3484 insert into RRS_SITE_USES(
3485 SITE_USE_ID
3486 ,SITE_ID
3487 ,SITE_USE_TYPE_CODE
3488 ,STATUS_CODE
3489 ,IS_PRIMARY_FLAG
3490 ,OBJECT_VERSION_NUMBER
3491 ,CREATED_BY
3492 ,CREATION_DATE
3493 ,LAST_UPDATED_BY
3494 ,LAST_UPDATE_DATE
3495 ,LAST_UPDATE_LOGIN
3496 )
3497 (
3498 Select
3499 rrs_site_uses_s.nextval
3500 ,B.SITE_ID
3501 ,SITE_USE_TYPE_CODE
3502 ,'A'
3503 ,'Y'
3504 ,1
3505 ,G_USER_ID
3506 ,SYSDATE
3507 ,A.LAST_UPDATED_BY
3508 ,SYSDATE
3509 ,A.LAST_UPDATE_LOGIN
3510 From RRS_SITES_INTERFACE A ,RRS_SITES_B B
3511 where batch_id = p_batch_id
3512 and transaction_type = G_TX_TYPE_UPDATE
3513 and process_status = G_PS_IN_PROCESS
3514 and A.SITE_USE_TYPE_CODE is NOT NULL
3515 and A.site_identification_number = B.site_identification_number
3516 and NOT EXISTS ( select C.site_id
3517 from RRS_SITE_USES C
3518 where B.Site_id = C.Site_id )
3519 );
3520
3521
3522 If ( p_purge_rows = 'Y' ) THEN
3523 DELETE from RRS_SITES_INTERFACE
3524 where batch_id = p_batch_id
3525 and transaction_type = G_TX_TYPE_UPDATE
3526 and process_status = G_PS_IN_PROCESS;
3527 else
3528 UPDATE RRS_SITES_INTERFACE
3529 SET PROCESS_STATUS = G_PS_SUCCESS
3530 WHERE PROCESS_STATUS=G_PS_IN_PROCESS
3531 AND BATCH_ID = p_batch_id
3532 AND TRANSACTION_TYPE=G_TX_TYPE_UPDATE;
3533
3534 end if;
3535
3536 end;
3537
3538 End rrs_import_interface_pkg;