183: BEGIN
184:
185: retcode := 0;
186:
187: HZ_GNR_UTIL_PKG.outandlog('Starting Concurrent Program ''Geo Name Referencing''');
188: HZ_GNR_UTIL_PKG.outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
189: HZ_GNR_UTIL_PKG.outandlog('NEWLINE');
190:
191: -- Initialize return status and message stack
184:
185: retcode := 0;
186:
187: HZ_GNR_UTIL_PKG.outandlog('Starting Concurrent Program ''Geo Name Referencing''');
188: HZ_GNR_UTIL_PKG.outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
189: HZ_GNR_UTIL_PKG.outandlog('NEWLINE');
190:
191: -- Initialize return status and message stack
192: FND_MSG_PUB.initialize;
185: retcode := 0;
186:
187: HZ_GNR_UTIL_PKG.outandlog('Starting Concurrent Program ''Geo Name Referencing''');
188: HZ_GNR_UTIL_PKG.outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
189: HZ_GNR_UTIL_PKG.outandlog('NEWLINE');
190:
191: -- Initialize return status and message stack
192: FND_MSG_PUB.initialize;
193:
326: IF (NVL(l_num_of_workers,0) < 1) THEN
327: l_num_of_workers := 1;
328: END IF;
329:
330: HZ_GNR_UTIL_PKG.log('Spawning ' || to_char(l_num_of_workers) || ' Workers for Geo Name Referencing');
331:
332: -- Submit requests as per profile HZ_GNR_NUM_OF_WORKERS
333:
334: FOR I in 1..TO_NUMBER(l_num_of_workers) LOOP
346: p_end_date,
347: to_char(l_num_of_workers)
348: );
349: IF l_sub_requests(i) = 0 THEN
350: HZ_GNR_UTIL_PKG.log('Error submitting worker ' || i);
351: HZ_GNR_UTIL_PKG.log(fnd_message.get);
352: ELSE
353: HZ_GNR_UTIL_PKG.log('Submitted request for Worker ' || TO_CHAR(i) );
354: HZ_GNR_UTIL_PKG.log('Request ID : ' || l_sub_requests(i));
347: to_char(l_num_of_workers)
348: );
349: IF l_sub_requests(i) = 0 THEN
350: HZ_GNR_UTIL_PKG.log('Error submitting worker ' || i);
351: HZ_GNR_UTIL_PKG.log(fnd_message.get);
352: ELSE
353: HZ_GNR_UTIL_PKG.log('Submitted request for Worker ' || TO_CHAR(i) );
354: HZ_GNR_UTIL_PKG.log('Request ID : ' || l_sub_requests(i));
355: END IF;
349: IF l_sub_requests(i) = 0 THEN
350: HZ_GNR_UTIL_PKG.log('Error submitting worker ' || i);
351: HZ_GNR_UTIL_PKG.log(fnd_message.get);
352: ELSE
353: HZ_GNR_UTIL_PKG.log('Submitted request for Worker ' || TO_CHAR(i) );
354: HZ_GNR_UTIL_PKG.log('Request ID : ' || l_sub_requests(i));
355: END IF;
356: EXIT when l_sub_requests(i) = 0;
357: END LOOP;
350: HZ_GNR_UTIL_PKG.log('Error submitting worker ' || i);
351: HZ_GNR_UTIL_PKG.log(fnd_message.get);
352: ELSE
353: HZ_GNR_UTIL_PKG.log('Submitted request for Worker ' || TO_CHAR(i) );
354: HZ_GNR_UTIL_PKG.log('Request ID : ' || l_sub_requests(i));
355: END IF;
356: EXIT when l_sub_requests(i) = 0;
357: END LOOP;
358:
355: END IF;
356: EXIT when l_sub_requests(i) = 0;
357: END LOOP;
358:
359: HZ_GNR_UTIL_PKG.outandlog('Concurrent Program Execution completed ');
360: HZ_GNR_UTIL_PKG.outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
361:
362: EXCEPTION
363: WHEN FND_API.G_EXC_ERROR THEN
356: EXIT when l_sub_requests(i) = 0;
357: END LOOP;
358:
359: HZ_GNR_UTIL_PKG.outandlog('Concurrent Program Execution completed ');
360: HZ_GNR_UTIL_PKG.outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
361:
362: EXCEPTION
363: WHEN FND_API.G_EXC_ERROR THEN
364: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
360: HZ_GNR_UTIL_PKG.outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
361:
362: EXCEPTION
363: WHEN FND_API.G_EXC_ERROR THEN
364: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
365: retcode := 2;
366: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
367: FND_FILE.close;
368: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
362: EXCEPTION
363: WHEN FND_API.G_EXC_ERROR THEN
364: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
365: retcode := 2;
366: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
367: FND_FILE.close;
368: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
370: retcode := 2;
365: retcode := 2;
366: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
367: FND_FILE.close;
368: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
370: retcode := 2;
371: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
372: FND_FILE.close;
373: WHEN OTHERS THEN
367: FND_FILE.close;
368: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
370: retcode := 2;
371: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
372: FND_FILE.close;
373: WHEN OTHERS THEN
374: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
375: retcode := 2;
370: retcode := 2;
371: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
372: FND_FILE.close;
373: WHEN OTHERS THEN
374: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
375: retcode := 2;
376: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
377: FND_FILE.close;
378: END;
372: FND_FILE.close;
373: WHEN OTHERS THEN
374: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
375: retcode := 2;
376: errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
377: FND_FILE.close;
378: END;
379:
380: /**
918: IF l_worker_number = l_num_workers THEN
919: l_worker_number := 0;
920: END IF;
921:
922: HZ_GNR_UTIL_PKG.log('Starting Concurrent Program ''Geo Name Referencing Worker: '||p_worker_number||'''');
923: HZ_GNR_UTIL_PKG.log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
924: HZ_GNR_UTIL_PKG.log('NEWLINE');
925:
926: -- Initialize Global Variable
919: l_worker_number := 0;
920: END IF;
921:
922: HZ_GNR_UTIL_PKG.log('Starting Concurrent Program ''Geo Name Referencing Worker: '||p_worker_number||'''');
923: HZ_GNR_UTIL_PKG.log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
924: HZ_GNR_UTIL_PKG.log('NEWLINE');
925:
926: -- Initialize Global Variable
927: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
920: END IF;
921:
922: HZ_GNR_UTIL_PKG.log('Starting Concurrent Program ''Geo Name Referencing Worker: '||p_worker_number||'''');
923: HZ_GNR_UTIL_PKG.log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
924: HZ_GNR_UTIL_PKG.log('NEWLINE');
925:
926: -- Initialize Global Variable
927: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
928: -- For any address validation call from GNR this variable will be set at
923: HZ_GNR_UTIL_PKG.log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
924: HZ_GNR_UTIL_PKG.log('NEWLINE');
925:
926: -- Initialize Global Variable
927: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
928: -- For any address validation call from GNR this variable will be set at
929: -- PROCEDURE level to 'GNR'
930: -- Nishant (Perf Bug 5881539 16-APR-2007)
931: HZ_GNR_PKG.G_API_PURPOSE := 'GNR';
954:
955: -- Table : HR_LOCATIONS_ALL Run Type : ALL
956: IF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ALL')) THEN
957:
958: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ALL');
959: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
955: -- Table : HR_LOCATIONS_ALL Run Type : ALL
956: IF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ALL')) THEN
957:
958: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ALL');
959: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
956: IF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ALL')) THEN
957:
958: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ALL');
959: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
957:
958: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ALL');
959: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
965: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
959: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
965: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
966: HZ_GNR_UTIL_PKG.outandlog(' ');
967:
961: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
965: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
966: HZ_GNR_UTIL_PKG.outandlog(' ');
967:
968: OPEN c_loc_hr_all(l_country_code,
969: l_from_location_id,
962: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
965: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
966: HZ_GNR_UTIL_PKG.outandlog(' ');
967:
968: OPEN c_loc_hr_all(l_country_code,
969: l_from_location_id,
970: l_to_location_id,
979:
980: l_count_num_of_rec := l_count_num_of_rec + 1;
981: l_num_locations := l_num_locations + 1;
982:
983: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
984:
985: BEGIN
986: HZ_GNR_PKG.delete_gnr(p_locId => l_cur_location_id,
987: p_locTbl => p_location_table_name,
993: );
994: EXCEPTION
995: WHEN FND_API.G_EXC_ERROR THEN
996: l_num_err_locations := l_num_err_locations + 1;
997: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
998: FND_MSG_PUB.Reset;
999: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1000: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1001: END LOOP;
996: l_num_err_locations := l_num_err_locations + 1;
997: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
998: FND_MSG_PUB.Reset;
999: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1000: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1001: END LOOP;
1002: FND_MSG_PUB.Delete_Msg;
1003: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004: l_num_err_locations := l_num_err_locations + 1;
1001: END LOOP;
1002: FND_MSG_PUB.Delete_Msg;
1003: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004: l_num_err_locations := l_num_err_locations + 1;
1005: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1006: FND_MSG_PUB.Reset;
1007: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1008: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1009: END LOOP;
1004: l_num_err_locations := l_num_err_locations + 1;
1005: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1006: FND_MSG_PUB.Reset;
1007: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1008: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1009: END LOOP;
1010: FND_MSG_PUB.Delete_Msg;
1011: WHEN OTHERS THEN
1012: l_num_err_locations := l_num_err_locations + 1;
1009: END LOOP;
1010: FND_MSG_PUB.Delete_Msg;
1011: WHEN OTHERS THEN
1012: l_num_err_locations := l_num_err_locations + 1;
1013: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1014: FND_MSG_PUB.Reset;
1015: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1016: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1017: END LOOP;
1012: l_num_err_locations := l_num_err_locations + 1;
1013: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1014: FND_MSG_PUB.Reset;
1015: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1016: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1017: END LOOP;
1018: FND_MSG_PUB.Delete_Msg;
1019: END;
1020:
1028: COMMIT;
1029:
1030: l_num_success_locations := l_num_locations - l_num_err_locations;
1031:
1032: HZ_GNR_UTIL_PKG.log(' ');
1033: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1029:
1030: l_num_success_locations := l_num_locations - l_num_err_locations;
1031:
1032: HZ_GNR_UTIL_PKG.log(' ');
1033: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1030: l_num_success_locations := l_num_locations - l_num_err_locations;
1031:
1032: HZ_GNR_UTIL_PKG.log(' ');
1033: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1031:
1032: HZ_GNR_UTIL_PKG.log(' ');
1033: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1033: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041:
1034: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041:
1042: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1035: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041:
1042: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1043:
1036:
1037: HZ_GNR_UTIL_PKG.out(' ');
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041:
1042: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1043:
1044: -- Table : HR_LOCATIONS_ALL Run Type : NEW
1038: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041:
1042: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1043:
1044: -- Table : HR_LOCATIONS_ALL Run Type : NEW
1045: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'NEW')) THEN
1046:
1043:
1044: -- Table : HR_LOCATIONS_ALL Run Type : NEW
1045: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'NEW')) THEN
1046:
1047: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : NEW');
1048: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1044: -- Table : HR_LOCATIONS_ALL Run Type : NEW
1045: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'NEW')) THEN
1046:
1047: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : NEW');
1048: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1045: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'NEW')) THEN
1046:
1047: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : NEW');
1048: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1046:
1047: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : NEW');
1048: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1054: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1048: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1054: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1055: HZ_GNR_UTIL_PKG.outandlog(' ');
1056:
1050: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1054: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1055: HZ_GNR_UTIL_PKG.outandlog(' ');
1056:
1057: OPEN c_loc_hr_new(l_country_code,
1058: l_from_location_id,
1051: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1054: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1055: HZ_GNR_UTIL_PKG.outandlog(' ');
1056:
1057: OPEN c_loc_hr_new(l_country_code,
1058: l_from_location_id,
1059: l_to_location_id,
1069:
1070: l_count_num_of_rec := l_count_num_of_rec + 1;
1071: l_num_locations := l_num_locations + 1;
1072:
1073: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1074:
1075: BEGIN
1076: HZ_GNR_PKG.validateHrLoc(l_cur_location_id,
1077: l_return_status
1078: );
1079: EXCEPTION
1080: WHEN FND_API.G_EXC_ERROR THEN
1081: l_num_err_locations := l_num_err_locations + 1;
1082: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1083: FND_MSG_PUB.Reset;
1084: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1085: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1086: END LOOP;
1081: l_num_err_locations := l_num_err_locations + 1;
1082: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1083: FND_MSG_PUB.Reset;
1084: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1085: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1086: END LOOP;
1087: FND_MSG_PUB.Delete_Msg;
1088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089: l_num_err_locations := l_num_err_locations + 1;
1086: END LOOP;
1087: FND_MSG_PUB.Delete_Msg;
1088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089: l_num_err_locations := l_num_err_locations + 1;
1090: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1091: FND_MSG_PUB.Reset;
1092: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1093: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1094: END LOOP;
1089: l_num_err_locations := l_num_err_locations + 1;
1090: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1091: FND_MSG_PUB.Reset;
1092: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1093: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1094: END LOOP;
1095: FND_MSG_PUB.Delete_Msg;
1096: WHEN OTHERS THEN
1097: l_num_err_locations := l_num_err_locations + 1;
1094: END LOOP;
1095: FND_MSG_PUB.Delete_Msg;
1096: WHEN OTHERS THEN
1097: l_num_err_locations := l_num_err_locations + 1;
1098: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1099: FND_MSG_PUB.Reset;
1100: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1101: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1102: END LOOP;
1097: l_num_err_locations := l_num_err_locations + 1;
1098: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1099: FND_MSG_PUB.Reset;
1100: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1101: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1102: END LOOP;
1103: FND_MSG_PUB.Delete_Msg;
1104: END;
1105:
1113: COMMIT;
1114:
1115: l_num_success_locations := l_num_locations - l_num_err_locations;
1116:
1117: HZ_GNR_UTIL_PKG.log(' ');
1118: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1114:
1115: l_num_success_locations := l_num_locations - l_num_err_locations;
1116:
1117: HZ_GNR_UTIL_PKG.log(' ');
1118: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1115: l_num_success_locations := l_num_locations - l_num_err_locations;
1116:
1117: HZ_GNR_UTIL_PKG.log(' ');
1118: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1116:
1117: HZ_GNR_UTIL_PKG.log(' ');
1118: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1118: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126:
1119: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126:
1127: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1120: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126:
1127: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1128:
1121:
1122: HZ_GNR_UTIL_PKG.out(' ');
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126:
1127: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1128:
1129: -- Table : HR_LOCATIONS_ALL Run Type : ERROR
1123: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126:
1127: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1128:
1129: -- Table : HR_LOCATIONS_ALL Run Type : ERROR
1130: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ERROR')) THEN
1131:
1128:
1129: -- Table : HR_LOCATIONS_ALL Run Type : ERROR
1130: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ERROR')) THEN
1131:
1132: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ERROR');
1133: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1129: -- Table : HR_LOCATIONS_ALL Run Type : ERROR
1130: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ERROR')) THEN
1131:
1132: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ERROR');
1133: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1130: ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ERROR')) THEN
1131:
1132: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ERROR');
1133: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1131:
1132: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ERROR');
1133: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1139: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1133: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1139: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1140: HZ_GNR_UTIL_PKG.outandlog(' ');
1141:
1135: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1139: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1140: HZ_GNR_UTIL_PKG.outandlog(' ');
1141:
1142: OPEN c_loc_hr_error(l_country_code,
1143: l_from_location_id,
1136: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1139: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1140: HZ_GNR_UTIL_PKG.outandlog(' ');
1141:
1142: OPEN c_loc_hr_error(l_country_code,
1143: l_from_location_id,
1144: l_to_location_id,
1154:
1155: l_count_num_of_rec := l_count_num_of_rec + 1;
1156: l_num_locations := l_num_locations + 1;
1157:
1158: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1159:
1160: BEGIN
1161: HZ_GNR_PKG.validateHrLoc(l_cur_location_id,
1162: l_return_status
1163: );
1164: EXCEPTION
1165: WHEN FND_API.G_EXC_ERROR THEN
1166: l_num_err_locations := l_num_err_locations + 1;
1167: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1168: FND_MSG_PUB.Reset;
1169: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1170: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1171: END LOOP;
1166: l_num_err_locations := l_num_err_locations + 1;
1167: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1168: FND_MSG_PUB.Reset;
1169: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1170: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1171: END LOOP;
1172: FND_MSG_PUB.Delete_Msg;
1173: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174: l_num_err_locations := l_num_err_locations + 1;
1171: END LOOP;
1172: FND_MSG_PUB.Delete_Msg;
1173: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174: l_num_err_locations := l_num_err_locations + 1;
1175: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1176: FND_MSG_PUB.Reset;
1177: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1178: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1179: END LOOP;
1174: l_num_err_locations := l_num_err_locations + 1;
1175: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1176: FND_MSG_PUB.Reset;
1177: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1178: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1179: END LOOP;
1180: FND_MSG_PUB.Delete_Msg;
1181: WHEN OTHERS THEN
1182: l_num_err_locations := l_num_err_locations + 1;
1179: END LOOP;
1180: FND_MSG_PUB.Delete_Msg;
1181: WHEN OTHERS THEN
1182: l_num_err_locations := l_num_err_locations + 1;
1183: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1184: FND_MSG_PUB.Reset;
1185: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1186: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1187: END LOOP;
1182: l_num_err_locations := l_num_err_locations + 1;
1183: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1184: FND_MSG_PUB.Reset;
1185: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1186: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1187: END LOOP;
1188: FND_MSG_PUB.Delete_Msg;
1189: END;
1190:
1198: COMMIT;
1199:
1200: l_num_success_locations := l_num_locations - l_num_err_locations;
1201:
1202: HZ_GNR_UTIL_PKG.log(' ');
1203: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1199:
1200: l_num_success_locations := l_num_locations - l_num_err_locations;
1201:
1202: HZ_GNR_UTIL_PKG.log(' ');
1203: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1200: l_num_success_locations := l_num_locations - l_num_err_locations;
1201:
1202: HZ_GNR_UTIL_PKG.log(' ');
1203: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1201:
1202: HZ_GNR_UTIL_PKG.log(' ');
1203: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1203: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211:
1204: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211:
1212: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1205: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211:
1212: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1213:
1206:
1207: HZ_GNR_UTIL_PKG.out(' ');
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211:
1212: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1213:
1214: -- Table : HZ_LOCATIONS Run Type : ALL
1208: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211:
1212: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1213:
1214: -- Table : HZ_LOCATIONS Run Type : ALL
1215: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1212: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1213:
1214: -- Table : HZ_LOCATIONS Run Type : ALL
1215: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1217: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1213:
1214: -- Table : HZ_LOCATIONS Run Type : ALL
1215: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1217: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1214: -- Table : HZ_LOCATIONS Run Type : ALL
1215: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1217: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1215: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1217: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1223: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1217: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1223: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1224: HZ_GNR_UTIL_PKG.outandlog(' ');
1225:
1219: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1223: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1224: HZ_GNR_UTIL_PKG.outandlog(' ');
1225:
1226: FOR l_c_loc_hz IN c_loc_hz_all(l_country_code,
1227: l_from_location_id,
1220: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1223: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1224: HZ_GNR_UTIL_PKG.outandlog(' ');
1225:
1226: FOR l_c_loc_hz IN c_loc_hz_all(l_country_code,
1227: l_from_location_id,
1228: l_to_location_id,
1235: l_count_num_of_rec := l_count_num_of_rec + 1;
1236: l_num_locations := l_num_locations + 1;
1237:
1238: l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1239: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1240:
1241: BEGIN -- added to handle unexpected error from ARH package that is
1242: -- called inside location_updation_allowed check (Bug 5099223)
1243: -- Nishant (17-Mar-2006)
1242: -- called inside location_updation_allowed check (Bug 5099223)
1243: -- Nishant (17-Mar-2006)
1244:
1245: -- If the Location updation is allowed
1246: IF (HZ_GNR_UTIL_PKG.location_updation_allowed(l_cur_location_id) OR
1247: NOT(HZ_GNR_UTIL_PKG.gnr_exists(l_cur_location_id,p_location_table_name))) THEN
1248:
1249: BEGIN
1250: -- delete for all usages (l_usage_code is set to ALL for runtype ALL)
1243: -- Nishant (17-Mar-2006)
1244:
1245: -- If the Location updation is allowed
1246: IF (HZ_GNR_UTIL_PKG.location_updation_allowed(l_cur_location_id) OR
1247: NOT(HZ_GNR_UTIL_PKG.gnr_exists(l_cur_location_id,p_location_table_name))) THEN
1248:
1249: BEGIN
1250: -- delete for all usages (l_usage_code is set to ALL for runtype ALL)
1251: -- Commented the below code for bug to handle "DUMMY" locations. Bug # 5022121
1283: X_STATUS => l_return_status);
1284: EXCEPTION
1285: WHEN FND_API.G_EXC_ERROR THEN
1286: l_num_err_locations := l_num_err_locations + 1;
1287: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1288: FND_MSG_PUB.Reset;
1289: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1290: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1291: END LOOP;
1286: l_num_err_locations := l_num_err_locations + 1;
1287: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1288: FND_MSG_PUB.Reset;
1289: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1290: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1291: END LOOP;
1292: FND_MSG_PUB.Delete_Msg;
1293: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1294: l_num_err_locations := l_num_err_locations + 1;
1291: END LOOP;
1292: FND_MSG_PUB.Delete_Msg;
1293: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1294: l_num_err_locations := l_num_err_locations + 1;
1295: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1296: FND_MSG_PUB.Reset;
1297: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1298: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1299: END LOOP;
1294: l_num_err_locations := l_num_err_locations + 1;
1295: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1296: FND_MSG_PUB.Reset;
1297: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1298: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1299: END LOOP;
1300: FND_MSG_PUB.Delete_Msg;
1301: WHEN OTHERS THEN
1302: l_num_err_locations := l_num_err_locations + 1;
1299: END LOOP;
1300: FND_MSG_PUB.Delete_Msg;
1301: WHEN OTHERS THEN
1302: l_num_err_locations := l_num_err_locations + 1;
1303: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1304: HZ_GNR_UTIL_PKG.out(SQLERRM);
1305: FND_MSG_PUB.Reset;
1306: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1307: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1300: FND_MSG_PUB.Delete_Msg;
1301: WHEN OTHERS THEN
1302: l_num_err_locations := l_num_err_locations + 1;
1303: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1304: HZ_GNR_UTIL_PKG.out(SQLERRM);
1305: FND_MSG_PUB.Reset;
1306: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1307: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1308: END LOOP;
1303: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1304: HZ_GNR_UTIL_PKG.out(SQLERRM);
1305: FND_MSG_PUB.Reset;
1306: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1307: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1308: END LOOP;
1309: FND_MSG_PUB.Delete_Msg;
1310: END;
1311:
1309: FND_MSG_PUB.Delete_Msg;
1310: END;
1311:
1312: ELSE
1313: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1314: HZ_GNR_UTIL_PKG.out('Skipped GNR - transaction exists for this location');
1315: END IF;
1316:
1317: EXCEPTION WHEN OTHERS THEN
1310: END;
1311:
1312: ELSE
1313: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1314: HZ_GNR_UTIL_PKG.out('Skipped GNR - transaction exists for this location');
1315: END IF;
1316:
1317: EXCEPTION WHEN OTHERS THEN
1318: l_num_err_locations := l_num_err_locations + 1;
1315: END IF;
1316:
1317: EXCEPTION WHEN OTHERS THEN
1318: l_num_err_locations := l_num_err_locations + 1;
1319: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1320: HZ_GNR_UTIL_PKG.out('Error: '||SQLERRM);
1321: FND_MSG_PUB.Reset;
1322: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1323: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1316:
1317: EXCEPTION WHEN OTHERS THEN
1318: l_num_err_locations := l_num_err_locations + 1;
1319: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1320: HZ_GNR_UTIL_PKG.out('Error: '||SQLERRM);
1321: FND_MSG_PUB.Reset;
1322: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1323: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1324: END LOOP;
1319: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1320: HZ_GNR_UTIL_PKG.out('Error: '||SQLERRM);
1321: FND_MSG_PUB.Reset;
1322: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1323: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1324: END LOOP;
1325: FND_MSG_PUB.Delete_Msg;
1326: END;
1327:
1334: COMMIT;
1335:
1336: l_num_success_locations := l_num_locations - l_num_err_locations;
1337:
1338: HZ_GNR_UTIL_PKG.log(' ');
1339: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1335:
1336: l_num_success_locations := l_num_locations - l_num_err_locations;
1337:
1338: HZ_GNR_UTIL_PKG.log(' ');
1339: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1336: l_num_success_locations := l_num_locations - l_num_err_locations;
1337:
1338: HZ_GNR_UTIL_PKG.log(' ');
1339: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1337:
1338: HZ_GNR_UTIL_PKG.log(' ');
1339: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1339: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1346: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347:
1340: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1346: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347:
1348: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1341: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1346: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347:
1348: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1349:
1342:
1343: HZ_GNR_UTIL_PKG.out(' ');
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1346: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347:
1348: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1349:
1350: -- Table : HZ_LOCATIONS Run Type : NEW
1344: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1346: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347:
1348: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1349:
1350: -- Table : HZ_LOCATIONS Run Type : NEW
1351: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1348: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1349:
1350: -- Table : HZ_LOCATIONS Run Type : NEW
1351: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1353: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1349:
1350: -- Table : HZ_LOCATIONS Run Type : NEW
1351: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1353: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1350: -- Table : HZ_LOCATIONS Run Type : NEW
1351: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1353: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1351: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1353: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1359: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1353: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1359: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1360: HZ_GNR_UTIL_PKG.outandlog(' ');
1361:
1355: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1359: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1360: HZ_GNR_UTIL_PKG.outandlog(' ');
1361:
1362: FOR l_c_loc_hz IN c_loc_hz_new(l_country_code,
1363: l_from_location_id,
1356: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1359: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1360: HZ_GNR_UTIL_PKG.outandlog(' ');
1361:
1362: FOR l_c_loc_hz IN c_loc_hz_new(l_country_code,
1363: l_from_location_id,
1364: l_to_location_id,
1372: l_count_num_of_rec := l_count_num_of_rec + 1;
1373: l_num_locations := l_num_locations + 1;
1374:
1375: l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1376: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1377:
1378: BEGIN
1379: HZ_GNR_PKG.validateLoc(
1380: P_LOCATION_ID => l_cur_location_id,
1404: X_STATUS => l_return_status);
1405: EXCEPTION
1406: WHEN FND_API.G_EXC_ERROR THEN
1407: l_num_err_locations := l_num_err_locations + 1;
1408: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1409: FND_MSG_PUB.Reset;
1410: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1411: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1412: END LOOP;
1407: l_num_err_locations := l_num_err_locations + 1;
1408: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1409: FND_MSG_PUB.Reset;
1410: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1411: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1412: END LOOP;
1413: FND_MSG_PUB.Delete_Msg;
1414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1415: l_num_err_locations := l_num_err_locations + 1;
1412: END LOOP;
1413: FND_MSG_PUB.Delete_Msg;
1414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1415: l_num_err_locations := l_num_err_locations + 1;
1416: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1417: FND_MSG_PUB.Reset;
1418: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1419: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1420: END LOOP;
1415: l_num_err_locations := l_num_err_locations + 1;
1416: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1417: FND_MSG_PUB.Reset;
1418: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1419: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1420: END LOOP;
1421: FND_MSG_PUB.Delete_Msg;
1422: WHEN OTHERS THEN
1423: l_num_err_locations := l_num_err_locations + 1;
1420: END LOOP;
1421: FND_MSG_PUB.Delete_Msg;
1422: WHEN OTHERS THEN
1423: l_num_err_locations := l_num_err_locations + 1;
1424: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1425: FND_MSG_PUB.Reset;
1426: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1427: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1428: END LOOP;
1423: l_num_err_locations := l_num_err_locations + 1;
1424: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1425: FND_MSG_PUB.Reset;
1426: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1427: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1428: END LOOP;
1429: FND_MSG_PUB.Delete_Msg;
1430: END;
1431:
1438: COMMIT;
1439:
1440: l_num_success_locations := l_num_locations - l_num_err_locations;
1441:
1442: HZ_GNR_UTIL_PKG.log(' ');
1443: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1439:
1440: l_num_success_locations := l_num_locations - l_num_err_locations;
1441:
1442: HZ_GNR_UTIL_PKG.log(' ');
1443: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1440: l_num_success_locations := l_num_locations - l_num_err_locations;
1441:
1442: HZ_GNR_UTIL_PKG.log(' ');
1443: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1441:
1442: HZ_GNR_UTIL_PKG.log(' ');
1443: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1443: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451:
1444: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451:
1452: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1445: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451:
1452: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1453:
1446:
1447: HZ_GNR_UTIL_PKG.out(' ');
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451:
1452: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1453:
1454: -- Table : HZ_LOCATIONS Run Type : ERROR
1448: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451:
1452: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1453:
1454: -- Table : HZ_LOCATIONS Run Type : ERROR
1455: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1452: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1453:
1454: -- Table : HZ_LOCATIONS Run Type : ERROR
1455: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1457: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1453:
1454: -- Table : HZ_LOCATIONS Run Type : ERROR
1455: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1457: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1454: -- Table : HZ_LOCATIONS Run Type : ERROR
1455: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1457: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1455: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1457: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1463: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1457: HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458: HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1463: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1464: HZ_GNR_UTIL_PKG.outandlog(' ');
1465:
1459: HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1463: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1464: HZ_GNR_UTIL_PKG.outandlog(' ');
1465:
1466: FOR l_c_loc_hz IN c_loc_hz_error(l_country_code,
1467: l_from_location_id,
1460: ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1461: HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462: ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1463: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1464: HZ_GNR_UTIL_PKG.outandlog(' ');
1465:
1466: FOR l_c_loc_hz IN c_loc_hz_error(l_country_code,
1467: l_from_location_id,
1468: l_to_location_id,
1476: l_count_num_of_rec := l_count_num_of_rec + 1;
1477: l_num_locations := l_num_locations + 1;
1478:
1479: l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1480: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1481:
1482: BEGIN
1483: HZ_GNR_PKG.validateLoc(
1484: P_LOCATION_ID => l_cur_location_id,
1508: X_STATUS => l_return_status);
1509: EXCEPTION
1510: WHEN FND_API.G_EXC_ERROR THEN
1511: l_num_err_locations := l_num_err_locations + 1;
1512: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1513: FND_MSG_PUB.Reset;
1514: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1515: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1516: END LOOP;
1511: l_num_err_locations := l_num_err_locations + 1;
1512: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1513: FND_MSG_PUB.Reset;
1514: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1515: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1516: END LOOP;
1517: FND_MSG_PUB.Delete_Msg;
1518: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519: l_num_err_locations := l_num_err_locations + 1;
1516: END LOOP;
1517: FND_MSG_PUB.Delete_Msg;
1518: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519: l_num_err_locations := l_num_err_locations + 1;
1520: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1521: FND_MSG_PUB.Reset;
1522: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1523: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1524: END LOOP;
1519: l_num_err_locations := l_num_err_locations + 1;
1520: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1521: FND_MSG_PUB.Reset;
1522: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1523: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1524: END LOOP;
1525: FND_MSG_PUB.Delete_Msg;
1526: WHEN OTHERS THEN
1527: l_num_err_locations := l_num_err_locations + 1;
1524: END LOOP;
1525: FND_MSG_PUB.Delete_Msg;
1526: WHEN OTHERS THEN
1527: l_num_err_locations := l_num_err_locations + 1;
1528: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1529: FND_MSG_PUB.Reset;
1530: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1531: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1532: END LOOP;
1527: l_num_err_locations := l_num_err_locations + 1;
1528: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1529: FND_MSG_PUB.Reset;
1530: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1531: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1532: END LOOP;
1533: FND_MSG_PUB.Delete_Msg;
1534: END;
1535:
1542: COMMIT;
1543:
1544: l_num_success_locations := l_num_locations - l_num_err_locations;
1545:
1546: HZ_GNR_UTIL_PKG.log(' ');
1547: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1543:
1544: l_num_success_locations := l_num_locations - l_num_err_locations;
1545:
1546: HZ_GNR_UTIL_PKG.log(' ');
1547: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1544: l_num_success_locations := l_num_locations - l_num_err_locations;
1545:
1546: HZ_GNR_UTIL_PKG.log(' ');
1547: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1545:
1546: HZ_GNR_UTIL_PKG.log(' ');
1547: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1547: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555:
1548: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555:
1556: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1549: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555:
1556: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1557:
1550:
1551: HZ_GNR_UTIL_PKG.out(' ');
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555:
1556: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1557:
1558: -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1552: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555:
1556: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1557:
1558: -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1559: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1556: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1557:
1558: -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1559: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561: HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1557:
1558: -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1559: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561: HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1565:
1558: -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1559: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561: HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1565:
1566: -- Initialize Global Variable so that later update location is avoided
1559: ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561: HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1565:
1566: -- Initialize Global Variable so that later update location is avoided
1567: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
1560: HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561: HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562: HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1565:
1566: -- Initialize Global Variable so that later update location is avoided
1567: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
1568: -- Nishant (Perf Bug 5407103 4-AUG-2006)
1563: HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564: HZ_GNR_UTIL_PKG.outandlog(' ');
1565:
1566: -- Initialize Global Variable so that later update location is avoided
1567: -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
1568: -- Nishant (Perf Bug 5407103 4-AUG-2006)
1569: ------------------------------------------------------------------------
1570: -- Replaced by HZ_GNR_PKG.G_API_PURPOSE
1571: -- Nishant (Perf Bug 5881539 16-APR-2007)
1589: l_count_num_of_rec := l_count_num_of_rec + 1;
1590: l_num_locations := l_num_locations + 1;
1591:
1592: l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1593: -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1594:
1595: BEGIN
1596:
1597: HZ_GNR_PKG.validateLoc(
1622: X_STATUS => l_return_status);
1623: EXCEPTION
1624: WHEN FND_API.G_EXC_ERROR THEN
1625: l_num_err_locations := l_num_err_locations + 1;
1626: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1627: FND_MSG_PUB.Reset;
1628: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1629: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1630: END LOOP;
1625: l_num_err_locations := l_num_err_locations + 1;
1626: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1627: FND_MSG_PUB.Reset;
1628: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1629: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1630: END LOOP;
1631: FND_MSG_PUB.Delete_Msg;
1632: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1633: l_num_err_locations := l_num_err_locations + 1;
1630: END LOOP;
1631: FND_MSG_PUB.Delete_Msg;
1632: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1633: l_num_err_locations := l_num_err_locations + 1;
1634: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1635: FND_MSG_PUB.Reset;
1636: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1637: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1638: END LOOP;
1633: l_num_err_locations := l_num_err_locations + 1;
1634: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1635: FND_MSG_PUB.Reset;
1636: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1637: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1638: END LOOP;
1639: FND_MSG_PUB.Delete_Msg;
1640: WHEN OTHERS THEN
1641: l_num_err_locations := l_num_err_locations + 1;
1638: END LOOP;
1639: FND_MSG_PUB.Delete_Msg;
1640: WHEN OTHERS THEN
1641: l_num_err_locations := l_num_err_locations + 1;
1642: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1643: HZ_GNR_UTIL_PKG.out(SQLERRM);
1644: FND_MSG_PUB.Reset;
1645: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1646: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1639: FND_MSG_PUB.Delete_Msg;
1640: WHEN OTHERS THEN
1641: l_num_err_locations := l_num_err_locations + 1;
1642: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1643: HZ_GNR_UTIL_PKG.out(SQLERRM);
1644: FND_MSG_PUB.Reset;
1645: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1646: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1647: END LOOP;
1642: HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1643: HZ_GNR_UTIL_PKG.out(SQLERRM);
1644: FND_MSG_PUB.Reset;
1645: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1646: HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1647: END LOOP;
1648: FND_MSG_PUB.Delete_Msg;
1649: END;
1650:
1658: COMMIT;
1659:
1660: l_num_success_locations := l_num_locations - l_num_err_locations;
1661:
1662: HZ_GNR_UTIL_PKG.log(' ');
1663: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1659:
1660: l_num_success_locations := l_num_locations - l_num_err_locations;
1661:
1662: HZ_GNR_UTIL_PKG.log(' ');
1663: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1660: l_num_success_locations := l_num_locations - l_num_err_locations;
1661:
1662: HZ_GNR_UTIL_PKG.log(' ');
1663: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1661:
1662: HZ_GNR_UTIL_PKG.log(' ');
1663: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1663: HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671:
1664: HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671:
1672: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1665: HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671:
1672: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1673: END IF;
1666:
1667: HZ_GNR_UTIL_PKG.out(' ');
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671:
1672: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1673: END IF;
1674:
1668: HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669: HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670: HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671:
1672: HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1673: END IF;
1674:
1675: -- Reset Global variable
1676: HZ_GNR_PKG.G_API_PURPOSE := NULL;
1687:
1688: -- Reset Global variable
1689: HZ_GNR_PKG.G_API_PURPOSE := NULL;
1690:
1691: HZ_GNR_UTIL_PKG.outandlog('Unknown Error at Location ID: ' || l_cur_location_id || ' : ' || SQLERRM);
1692: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
1693: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1694: HZ_GNR_UTIL_PKG.outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1695: END LOOP;
1688: -- Reset Global variable
1689: HZ_GNR_PKG.G_API_PURPOSE := NULL;
1690:
1691: HZ_GNR_UTIL_PKG.outandlog('Unknown Error at Location ID: ' || l_cur_location_id || ' : ' || SQLERRM);
1692: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
1693: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1694: HZ_GNR_UTIL_PKG.outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1695: END LOOP;
1696:
1690:
1691: HZ_GNR_UTIL_PKG.outandlog('Unknown Error at Location ID: ' || l_cur_location_id || ' : ' || SQLERRM);
1692: HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
1693: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1694: HZ_GNR_UTIL_PKG.outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1695: END LOOP;
1696:
1697: retcode := 2;
1698: errbuf := 'Unexpected SQL Error at location id :' || l_cur_location_id;
1902: l_cntry varchar2(2);
1903: l_addr_val_level varchar2(30);
1904: l_addr_warn_msg varchar2(2000);
1905: l_country_code VARCHAR2(10);
1906: l_usage_tbl HZ_GNR_UTIL_PKG.usage_tbl_type;
1907:
1908: l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRCB:HZ_GNR_PKG';
1909: l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
1910: l_debug_prefix VARCHAR2(30) := p_location_id;