[Home] [Help]
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
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
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:
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
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;
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
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
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
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
402:
403: Cursor c_default_site_id_num(l_batch_id number) is
404: Select site_identification_number,
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;
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:
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,
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;
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
1104: ,p_column_name => 'LONGITUDE '
1105: ,p_message_name => 'MESSAGE NAME'
1106: -- ,p_message_text => 'Longitude should be between -180 and 180 for : '||site_data.site_identification_number
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 );
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;
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: ) ;
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
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
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
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;
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
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,
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
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
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
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
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
2761: ,p_site_id_num => site_data.site_id_num_intf
2762: ,p_column_name => 'CALENDAR_CODE'
2763: ,p_message_name => 'MESSAGE NAME'
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'
2767: ,p_process_status => '3'
2768: ,p_transaction_type => site_data.transaction_type_intf
2769: ,p_batch_id => site_data.batch_id
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
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
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
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
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
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
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
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
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
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'
3005: ,p_process_status => '3'
3006: ,p_transaction_type => site_data.transaction_type_intf
3007: ,p_batch_id => site_data.batch_id
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
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
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
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
3102: ,p_site_id_num => site_data.site_id_num_intf
3103: ,p_column_name => 'LE_PARTY_ID'
3104: ,p_message_name => 'MESSAGE NAME'
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
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
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
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
3200: ,p_site_id_num => site_data.site_id_num_intf
3201: ,p_column_name => 'SITE_ID'
3202: ,p_message_name => 'MESSAGE NAME'
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'
3206: ,p_process_status => '3'
3207: ,p_transaction_type => site_data.transaction_type_intf
3208: ,p_batch_id => site_data.batch_id
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
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;
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
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
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 );
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 )
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:
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
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 )
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
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
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;