eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMS_LISTIMPORT_PVT dependencies on AMS_IMP_SOURCE_LINES

Line 89: -- This program populates the ams_imp-source_lines table.

85: x_msg_data OUT NOCOPY VARCHAR2
86: );
87:
88: --
89: -- This program populates the ams_imp-source_lines table.
90: --
91: Procedure Create_b2b_src_lines(
92: p_import_list_header_id IN number,
93: p_cust_b2b_tbl in cust_data_in_tbl,

Line 98: -- This program populates the ams_imp-source_lines table.

94: x_return_status OUT NOCOPY varchar2,
95: x_msg_data OUT NOCOPY varchar2) ;
96:
97: --
98: -- This program populates the ams_imp-source_lines table.
99: --
100: Procedure Create_b2c_src_lines(
101: p_import_list_header_id IN number,
102: p_cust_b2c_tbl in cust_b2c_data_in_tbl,

Line 397: select count(*) from ams_imp_source_lines

393: select nvl(batch_size,0),parent_request_id from ams_imp_list_headers_all
394: where import_list_header_id = p_import_list_header_id;
395:
396: cursor c_total_recs is
397: select count(*) from ams_imp_source_lines
398: where import_list_header_id = p_import_list_header_id
399: and load_status in ('ACTIVE','RELOAD');
400:
401:

Line 1137: update ams_imp_source_lines

1133: p_msg_type => 'DEBUG'
1134: );
1135: end if;
1136:
1137: update ams_imp_source_lines
1138: set request_id = new_request_id
1139: where import_list_header_id = p_import_list_header_id
1140: and request_id is null
1141: and load_status in ('ACTIVE','RELOAD')

Line 1154: update ams_imp_source_lines

1150: x_return_status ,
1151: x_msg_count ,
1152: x_msg_data );
1153: else
1154: update ams_imp_source_lines
1155: set request_id = new_request_id
1156: where import_list_header_id = p_import_list_header_id
1157: and request_id is null
1158: and ROWNUM <= l_batch_size;

Line 1636: UPDATE ams_imp_source_lines

1632: x_return_status,
1633: x_msg_count ,
1634: x_msg_data );
1635: if x_return_status <> 'S' then
1636: UPDATE ams_imp_source_lines
1637: SET load_status = 'ERROR', ENABLED_FLAG = null
1638: WHERE import_source_line_id = i_import_source_line_id;
1639: AMS_List_Import_PUB.error_capture (
1640: 1, 'T', 'F', null,

Line 1657: UPDATE ams_imp_source_lines

1653: open vehicle_code_exists(l_vr_code);
1654: fetch vehicle_code_exists into l_vr_code_exists;
1655: close vehicle_code_exists;
1656: if l_vr_code_exists is null then
1657: UPDATE ams_imp_source_lines
1658: SET load_status = 'ERROR',ENABLED_FLAG = null
1659: WHERE import_source_line_id = i_import_source_line_id;
1660: AMS_List_Import_PUB.error_capture (
1661: 1,

Line 1679: UPDATE ams_imp_source_lines

1675: open resource_exists (l_sa_email);
1676: fetch resource_exists into l_resource_id;
1677: close resource_exists;
1678: if l_resource_id is null then
1679: UPDATE ams_imp_source_lines
1680: SET load_status = 'ERROR',ENABLED_FLAG = null
1681: WHERE import_source_line_id = i_import_source_line_id;
1682: AMS_List_Import_PUB.error_capture (
1683: 1,

Line 1695: UPDATE ams_imp_source_lines

1691: i_import_source_line_id,
1692: null, null, null,null,
1693: 'SALES_AGENT_EMAIL_ADDRESS : Resource ID does not exist for this sales agent.');
1694: end if;
1695: UPDATE ams_imp_source_lines
1696: SET resource_id = l_resource_id
1697: WHERE import_source_line_id = i_import_source_line_id;
1698: end if;
1699:

Line 1709: UPDATE ams_imp_source_lines

1705: x_msg_data := x_tmp_var1;
1706: END IF;
1707: errbuf := 'ERROR -'||substr(x_msg_data,1,180);
1708: if x_return_status <> 'S' then
1709: UPDATE ams_imp_source_lines
1710: SET load_status = 'ERROR',ENABLED_FLAG = null
1711: WHERE import_source_line_id = i_import_source_line_id;
1712: AMS_List_Import_PUB.error_capture (
1713: 1,

Line 2033: UPDATE ams_imp_source_lines

2029: x_return_status,
2030: x_msg_count ,
2031: x_msg_data );
2032: if x_return_status <> 'S' then
2033: UPDATE ams_imp_source_lines
2034: SET load_status = 'ERROR',ENABLED_FLAG = null
2035: WHERE import_source_line_id = i_import_source_line_id;
2036: AMS_List_Import_PUB.error_capture (
2037: 1, 'T', 'F', null,

Line 2054: UPDATE ams_imp_source_lines

2050: open vehicle_code_exists(l_vr_code);
2051: fetch vehicle_code_exists into l_vr_code_exists;
2052: close vehicle_code_exists;
2053: if l_vr_code_exists is null then
2054: UPDATE ams_imp_source_lines
2055: SET load_status = 'ERROR',ENABLED_FLAG = null
2056: WHERE import_source_line_id = i_import_source_line_id;
2057: AMS_List_Import_PUB.error_capture (
2058: 1,

Line 2076: UPDATE ams_imp_source_lines

2072: open resource_exists (l_sa_email);
2073: fetch resource_exists into l_resource_id;
2074: close resource_exists;
2075: if l_resource_id is null then
2076: UPDATE ams_imp_source_lines
2077: SET load_status = 'ERROR',ENABLED_FLAG = null
2078: WHERE import_source_line_id = i_import_source_line_id;
2079: AMS_List_Import_PUB.error_capture (
2080: 1,

Line 2092: UPDATE ams_imp_source_lines

2088: i_import_source_line_id,
2089: null, null, null,null,
2090: 'SALES_AGENT_EMAIL_ADDRESS : Resource ID does not exist for this sales agent.');
2091: end if;
2092: UPDATE ams_imp_source_lines
2093: SET resource_id = l_resource_id
2094: WHERE import_source_line_id = i_import_source_line_id
2095: AND import_list_header_id = p_import_list_header_id;
2096: end if;

Line 2107: UPDATE ams_imp_source_lines

2103: x_msg_data := x_tmp_var1;
2104: END IF;
2105: errbuf := 'ERROR -'||substr(x_msg_data,1,180);
2106: if x_return_status <> 'S' then
2107: UPDATE ams_imp_source_lines
2108: SET load_status = 'ERROR',ENABLED_FLAG = null
2109: WHERE import_source_line_id = i_import_source_line_id;
2110: AMS_List_Import_PUB.error_capture (
2111: 1,

Line 2181: UPDATE ams_imp_source_lines

2177: EXCEPTION
2178: WHEN OTHERS THEN
2179: errbuf:= substr(SQLERRM,1,254);
2180: retcode:= 2;
2181: UPDATE ams_imp_source_lines
2182: SET load_status = 'ERROR'
2183: WHERE import_source_line_id = i_import_source_line_id;
2184: AMS_List_Import_PUB.error_capture (
2185: 1,

Line 2282: FROM ams_imp_source_lines

2278: l_osr_mapped VARCHAR2(1);
2279:
2280: CURSOR c_loaded_rows IS
2281: SELECT COUNT(*)
2282: FROM ams_imp_source_lines
2283: WHERE import_list_header_id = p_import_list_header_id;
2284:
2285: cursor c_header_name is
2286: select name, server_flag, status_code, import_type, execute_mode from ams_imp_list_headers_all

Line 2294: select count(*) from ams_imp_source_lines

2290: select status_code, nvl(dedupe_flag,'N') from ams_imp_list_headers_all
2291: where import_list_header_id = p_import_list_header_id;
2292:
2293: cursor c_dup_recs is
2294: select count(*) from ams_imp_source_lines
2295: where import_list_header_id = p_import_list_header_id
2296: and duplicate_flag = 'Y';
2297:
2298: cursor c_loaded_rows_for_lead IS

Line 2299: SELECT COUNT(*) FROM ams_imp_source_lines

2295: where import_list_header_id = p_import_list_header_id
2296: and duplicate_flag = 'Y';
2297:
2298: cursor c_loaded_rows_for_lead IS
2299: SELECT COUNT(*) FROM ams_imp_source_lines
2300: WHERE import_list_header_id = p_import_list_header_id;
2301:
2302: cursor c_rec_in_stag_tab is
2303: select 'Y' from ams_imp_source_lines

Line 2303: select 'Y' from ams_imp_source_lines

2299: SELECT COUNT(*) FROM ams_imp_source_lines
2300: WHERE import_list_header_id = p_import_list_header_id;
2301:
2302: cursor c_rec_in_stag_tab is
2303: select 'Y' from ams_imp_source_lines
2304: where import_list_header_id = p_import_list_header_id
2305: and load_status in ('ACTIVE','RELOAD')
2306: and rownum < 2;
2307:

Line 2309: select count(*) from ams_imp_source_lines

2305: and load_status in ('ACTIVE','RELOAD')
2306: and rownum < 2;
2307:
2308: cursor c_total_records is
2309: select count(*) from ams_imp_source_lines
2310: where import_list_header_id = p_import_list_header_id
2311: and load_status in ('ACTIVE','RELOAD');
2312:
2313: -- For Mapped osr

Line 2358: UPDATE ams_imp_source_lines

2354: batch_id = l_batch_id,
2355: status_date = sysdate
2356: where import_list_header_id = p_import_list_header_id;
2357:
2358: UPDATE ams_imp_source_lines
2359: set batch_id = l_batch_id,
2360: request_id = NULL
2361: where import_list_header_id = p_import_list_header_id
2362: and load_status in ('RELOAD','ACTIVE');

Line 3068: select count(*) from ams_imp_source_lines

3064: and error_type = 'E'
3065: and rownum < 2;
3066:
3067: cursor c_loaded_records is
3068: select count(*) from ams_imp_source_lines
3069: where import_list_header_id = p_import_list_header_id
3070: and load_status = G_STATUS_SUCCESS;
3071:
3072: cursor c_failed_records is

Line 3073: select count(*) from ams_imp_source_lines

3069: where import_list_header_id = p_import_list_header_id
3070: and load_status = G_STATUS_SUCCESS;
3071:
3072: cursor c_failed_records is
3073: select count(*) from ams_imp_source_lines
3074: where import_list_header_id = p_import_list_header_id
3075: and load_status = G_STATUS_ERROR;
3076:
3077:

Line 3118: (select import_source_line_id from ams_imp_source_lines where

3114: delete from ams_list_import_errors where import_list_header_id
3115: = p_import_list_header_id and import_source_line_id = 0;
3116: delete from ams_list_import_errors where import_list_header_id
3117: = p_import_list_header_id and import_source_line_id in
3118: (select import_source_line_id from ams_imp_source_lines where
3119: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_RELOAD);
3120: /*
3121: if l_file_type = 'XML' then
3122: update_xml_error_text (

Line 3250: (select import_source_line_id from ams_imp_source_lines where

3246: delete from ams_list_import_errors where import_list_header_id
3247: = p_import_list_header_id and import_source_line_id = 0;
3248: delete from ams_list_import_errors where import_list_header_id
3249: = p_import_list_header_id and import_source_line_id in
3250: (select import_source_line_id from ams_imp_source_lines where
3251: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_RELOAD);
3252:
3253: l_return_status := null;
3254: execute_event_data_validation ( p_import_list_header_id, l_return_status);

Line 3384: (select import_source_line_id from ams_imp_source_lines where

3380:
3381: -- Delete all the errors from the errors table which are sucessful.
3382: delete from ams_list_import_errors where import_list_header_id
3383: = p_import_list_header_id and import_source_line_id in
3384: (select import_source_line_id from ams_imp_source_lines where
3385: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_SUCCESS);
3386:
3387: open c_loaded_records;
3388: fetch c_loaded_records into l_loaded_records;

Line 3762: select import_source_line_id from ams_imp_source_lines

3758: ams_imp_list_headers_all
3759: WHERE import_list_header_id = p_import_list_header_id;
3760:
3761: cursor c_imp_src is
3762: select import_source_line_id from ams_imp_source_lines
3763: WHERE import_list_header_id = p_import_list_header_id
3764: AND load_status <> 'DUPLICATE';
3765:
3766: cursor c_file_type is

Line 3771: select COL68 from ams_imp_source_lines

3767: select file_type from ams_imp_documents
3768: where import_list_header_id = p_import_list_header_id;
3769:
3770: cursor c_dup_recs is
3771: select COL68 from ams_imp_source_lines
3772: where load_status = 'DUPLICATE'
3773: AND import_list_header_id = p_import_list_header_id;
3774:
3775:

Line 3882: UPDATE ams_imp_source_lines a SET a.duplicate_flag = 'Y' , load_status = 'DUPLICATE'

3878: close c_imp_src;
3879: end if ; -- l_word_replace = 'y ;
3880:
3881:
3882: UPDATE ams_imp_source_lines a SET a.duplicate_flag = 'Y' , load_status = 'DUPLICATE'
3883: WHERE a.import_list_header_id = p_import_list_header_id
3884: AND a.ROWID > (SELECT MIN(b.ROWID) FROM ams_imp_source_lines b
3885: WHERE b.import_list_header_id = a.import_list_header_id
3886: AND b.dedupe_key = a.dedupe_key

Line 3884: AND a.ROWID > (SELECT MIN(b.ROWID) FROM ams_imp_source_lines b

3880:
3881:
3882: UPDATE ams_imp_source_lines a SET a.duplicate_flag = 'Y' , load_status = 'DUPLICATE'
3883: WHERE a.import_list_header_id = p_import_list_header_id
3884: AND a.ROWID > (SELECT MIN(b.ROWID) FROM ams_imp_source_lines b
3885: WHERE b.import_list_header_id = a.import_list_header_id
3886: AND b.dedupe_key = a.dedupe_key
3887: AND b.import_list_header_id = p_import_list_header_id
3888: );

Line 4018: select 'Y' from ams_imp_source_lines -- ams_list_import_errors

4014: and rownum < 2;
4015:
4016: /*
4017: cursor c_error_exist is
4018: select 'Y' from ams_imp_source_lines -- ams_list_import_errors
4019: where import_list_header_id = p_import_list_header_id
4020: and load_status = 'ERROR'
4021: -- and error_type = 'E'
4022: and rownum < 2;

Line 4032: select count(*) from ams_imp_source_lines

4028: and col1 <> 'This is a duplicate lead.'
4029: and rownum < 2;
4030:
4031: cursor c_rec_in_ams is
4032: select count(*) from ams_imp_source_lines
4033: where import_list_header_id = p_import_list_header_id
4034: and load_status in ('ACTIVE','RELOAD');
4035:
4036: cursor c_rec_failed_in_ams is

Line 4037: select count(*) from ams_imp_source_lines lines

4033: where import_list_header_id = p_import_list_header_id
4034: and load_status in ('ACTIVE','RELOAD');
4035:
4036: cursor c_rec_failed_in_ams is
4037: select count(*) from ams_imp_source_lines lines
4038: where lines.import_list_header_id = p_import_list_header_id
4039: and lines.load_status = G_STATUS_ERROR;
4040:
4041: /*

Line 4043: select count(*) from ams_imp_source_lines lines,

4039: and lines.load_status = G_STATUS_ERROR;
4040:
4041: /*
4042: cursor c_rec_failed_in_ams is
4043: select count(*) from ams_imp_source_lines lines,
4044: ams_list_import_errors errors
4045: where lines.import_list_header_id = p_import_list_header_id
4046: and lines.load_status = 'ERROR'
4047: and lines.import_list_header_id = errors.import_list_header_id

Line 4069: select count(*) from ams_imp_source_lines

4065: and load_status in ('ERROR','T-ERROR','UNEXP_ERROR');
4066:
4067:
4068: cursor c_loaded_records is
4069: select count(*) from ams_imp_source_lines
4070: where import_list_header_id = p_import_list_header_id
4071: and load_status = G_STATUS_SUCCESS;
4072:
4073: cursor c_failed_records is

Line 4074: select count(*) from ams_imp_source_lines

4070: where import_list_header_id = p_import_list_header_id
4071: and load_status = G_STATUS_SUCCESS;
4072:
4073: cursor c_failed_records is
4074: select count(*) from ams_imp_source_lines
4075: where import_list_header_id = p_import_list_header_id
4076: and load_status = G_STATUS_ERROR;
4077:
4078: cursor c_rec_pro_dup_in_as is

Line 4133: (select import_source_line_id from ams_imp_source_lines where

4129: delete from ams_list_import_errors where import_list_header_id
4130: = p_import_list_header_id and import_source_line_id = 0;
4131: delete from ams_list_import_errors where import_list_header_id
4132: = p_import_list_header_id and import_source_line_id in
4133: (select import_source_line_id from ams_imp_source_lines where
4134: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_RELOAD);
4135:
4136: x_return_status := null;
4137: execute_lead_data_validation ( p_import_list_header_id,x_return_status );

Line 4475: (select import_source_line_id from ams_imp_source_lines where

4471:
4472: -- Delete all the errors from the errors table which are sucessful.
4473: delete from ams_list_import_errors where import_list_header_id
4474: = p_import_list_header_id and import_source_line_id in
4475: (select import_source_line_id from ams_imp_source_lines where
4476: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_SUCCESS);
4477:
4478: -- STATUS CODE PROCESS
4479:

Line 4865: from ams_imp_list_headers_all hd, ams_imp_source_lines sr

4861:
4862:
4863: cursor b2borb2c is
4864: select hd.import_type, hd.rented_list_flag, hd.import_list_header_id
4865: from ams_imp_list_headers_all hd, ams_imp_source_lines sr
4866: where hd.import_list_header_id = sr.import_list_header_id
4867: and sr.party_id = p_party_id;
4868:
4869: cursor c_relationship is

Line 7650: UPDATE ams_imp_source_lines

7646: end loop;
7647: close c_invalid_src_sys;
7648: end if;
7649:
7650: UPDATE ams_imp_source_lines
7651: SET load_status = decode(load_status,'DUPLICATE','DUPLICATE','ERROR')
7652: WHERE import_list_header_id = p_import_list_header_id
7653: and import_source_line_id in
7654: (select import_source_line_id from ams_list_import_errors

Line 8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;

8084: p_import_list_header_id NUMBER,
8085: p_return_status OUT NOCOPY VARCHAR2
8086: ) IS
8087:
8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;
8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;

Line 8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;

8085: p_return_status OUT NOCOPY VARCHAR2
8086: ) IS
8087:
8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;
8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;
8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;

Line 8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

8086: ) IS
8087:
8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;
8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;
8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;
8094:

Line 8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;

8087:
8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;
8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;
8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;
8094:
8095: l_last_fetch BOOLEAN := FALSE;

Line 8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;

8088: --TYPE l_batch_id IS TABLE OF AMS_IMP_SOURCE_LINES.BATCH_ID%TYPE;
8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;
8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;
8094:
8095: l_last_fetch BOOLEAN := FALSE;
8096: l_limit_rows number := 1000;

Line 8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;

8089: TYPE l_imp_list_header_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID%TYPE;
8090: TYPE l_imp_source_line_id IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
8091: TYPE l_relationship_type IS TABLE OF AMS_IMP_SOURCE_LINES.COL69%TYPE;
8092: TYPE l_relationship_code IS TABLE OF AMS_IMP_SOURCE_LINES.COL70%TYPE;
8093: TYPE l_import_failure_reason IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_FAILURE_REASON%TYPE;
8094:
8095: l_last_fetch BOOLEAN := FALSE;
8096: l_limit_rows number := 1000;
8097:

Line 8259: UPDATE ams_imp_source_lines

8255: CLOSE c_as_list_headers;
8256:
8257:
8258:
8259: UPDATE ams_imp_source_lines
8260: SET load_status = 'ERROR'
8261: WHERE import_list_header_id = p_import_list_header_id
8262: and import_source_line_id in
8263: (select import_source_line_id from ams_list_import_errors

Line 11089: UPDATE ams_imp_source_lines

11085: l_ADDRESS_EXPIRATION_DATE, l_DATE_OF_BIRTH,l_DATE_OF_DEATH,null,l_MARITAL_STATUS_EFF_DATE);
11086: End Loop;
11087: CLOSE c_b2c_date_fields;
11088: end if;
11089: UPDATE ams_imp_source_lines
11090: SET load_status = decode(load_status,'DUPLICATE','DUPLICATE','ERROR')
11091: WHERE import_list_header_id = p_import_list_header_id
11092: and import_source_line_id in
11093: (select import_source_line_id from ams_list_import_errors

Line 13675: select count(*) from ams_imp_source_lines

13671: ,nvl(NUMBER_OF_INSTANCES,1) from ams_imp_list_headers_all
13672: where import_list_header_id = p_import_list_header_id;
13673:
13674: cursor c_dup_recs is
13675: select count(*) from ams_imp_source_lines
13676: where import_list_header_id = p_import_list_header_id
13677: and duplicate_flag = 'Y';
13678:
13679: cursor c_event_status is

Line 13695: select 'Y' from ams_imp_source_lines

13691: and error_type = 'E'
13692: and rownum < 2;
13693:
13694: cursor c_rec_in_stag_tab is
13695: select 'Y' from ams_imp_source_lines
13696: where import_list_header_id = p_import_list_header_id
13697: and load_status in ('ACTIVE','RELOAD')
13698: and rownum < 2;
13699:

Line 13708: select count(*) from ams_imp_source_lines

13704: and e.load_status in ('ACTIVE','RELOAD')
13705: and rownum < 2;
13706:
13707: cursor c_loaded_records is
13708: select count(*) from ams_imp_source_lines
13709: where import_list_header_id = p_import_list_header_id
13710: and load_status = G_STATUS_SUCCESS;
13711:
13712: cursor c_failed_records is

Line 13713: select count(*) from ams_imp_source_lines

13709: where import_list_header_id = p_import_list_header_id
13710: and load_status = G_STATUS_SUCCESS;
13711:
13712: cursor c_failed_records is
13713: select count(*) from ams_imp_source_lines
13714: where import_list_header_id = p_import_list_header_id
13715: and load_status = G_STATUS_ERROR;
13716:
13717: cursor c_prog_name (x_imp_type varchar2) is

Line 13725: select count(*) from ams_imp_source_lines

13721: cursor c_file_type is
13722: select file_type from ams_imp_documents where import_list_header_id = p_import_list_header_id;
13723:
13724: cursor c_total_records is
13725: select count(*) from ams_imp_source_lines
13726: where import_list_header_id = p_import_list_header_id
13727: and load_status in ('ACTIVE','RELOAD');
13728:
13729: -- For Mapped osr

Line 13816: UPDATE ams_imp_source_lines

13812: batch_id = l_batch_id,
13813: status_date = sysdate
13814: where import_list_header_id = p_import_list_header_id;
13815:
13816: UPDATE ams_imp_source_lines
13817: set batch_id = l_batch_id,
13818: request_id = NULL
13819: where import_list_header_id = p_import_list_header_id
13820: and load_status in ('RELOAD','ACTIVE');

Line 14289: (select import_source_line_id from ams_imp_source_lines where

14285: delete from ams_list_import_errors where import_list_header_id
14286: = p_import_list_header_id and import_source_line_id = 0;
14287: delete from ams_list_import_errors where import_list_header_id
14288: = p_import_list_header_id and import_source_line_id in
14289: (select import_source_line_id from ams_imp_source_lines where
14290: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_RELOAD);
14291: if l_file_type = 'XML' then
14292: update_xml_error_text (
14293: p_import_list_header_id,

Line 14487: (select import_source_line_id from ams_imp_source_lines where

14483:
14484: -- Delete all the errors from the errors table which are sucessful.
14485: delete from ams_list_import_errors where import_list_header_id
14486: = p_import_list_header_id and import_source_line_id in
14487: (select import_source_line_id from ams_imp_source_lines where
14488: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_SUCCESS);
14489: open c_loaded_records;
14490: fetch c_loaded_records into l_loaded_records;
14491: close c_loaded_records;

Line 14610: (select import_source_line_id from ams_imp_source_lines where

14606: delete from ams_list_import_errors where import_list_header_id
14607: = p_import_list_header_id and import_source_line_id = 0;
14608: delete from ams_list_import_errors where import_list_header_id
14609: = p_import_list_header_id and import_source_line_id in
14610: (select import_source_line_id from ams_imp_source_lines where
14611: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_RELOAD);
14612:
14613: l_return_status := null;
14614: execute_event_data_validation ( p_import_list_header_id, l_return_status);

Line 14699: (select import_source_line_id from ams_imp_source_lines where

14695: end if;
14696: -- Delete all the errors from the errors table which are sucessful.
14697: delete from ams_list_import_errors where import_list_header_id
14698: = p_import_list_header_id and import_source_line_id in
14699: (select import_source_line_id from ams_imp_source_lines where
14700: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_SUCCESS);
14701: open c_loaded_records;
14702: fetch c_loaded_records into l_loaded_records;
14703: close c_loaded_records;

Line 15837: UPDATE ams_imp_source_lines

15833: EXIT WHEN c_event_fields%NOTFOUND;
15834: END LOOP;
15835: CLOSE c_event_fields;
15836:
15837: UPDATE ams_imp_source_lines
15838: SET load_status = 'ERROR'
15839: WHERE import_list_header_id = p_import_list_header_id
15840: and import_source_line_id in
15841: (select import_source_line_id from ams_list_import_errors

Line 15882: select count(*) from ams_imp_source_lines

15878:
15879:
15880:
15881: cursor c_tot_numb_records is
15882: select count(*) from ams_imp_source_lines
15883: where import_list_header_id = p_import_list_header_id;
15884:
15885: cursor c_tot_numb_error_records is
15886: select count(*) from ams_imp_source_lines

Line 15886: select count(*) from ams_imp_source_lines

15882: select count(*) from ams_imp_source_lines
15883: where import_list_header_id = p_import_list_header_id;
15884:
15885: cursor c_tot_numb_error_records is
15886: select count(*) from ams_imp_source_lines
15887: where import_list_header_id = p_import_list_header_id
15888: and load_status = G_STATUS_ERROR;
15889:
15890: cursor c_tot_numb_dup_records is

Line 15891: select count(*) from ams_imp_source_lines

15887: where import_list_header_id = p_import_list_header_id
15888: and load_status = G_STATUS_ERROR;
15889:
15890: cursor c_tot_numb_dup_records is
15891: select count(*) from ams_imp_source_lines
15892: where import_list_header_id = p_import_list_header_id
15893: and load_status = G_STATUS_DUPLICATE;
15894:
15895: cursor c_tot_numb_success_records is

Line 15896: select count(*) from ams_imp_source_lines

15892: where import_list_header_id = p_import_list_header_id
15893: and load_status = G_STATUS_DUPLICATE;
15894:
15895: cursor c_tot_numb_success_records is
15896: select count(*) from ams_imp_source_lines
15897: where import_list_header_id = p_import_list_header_id
15898: and load_status = G_STATUS_SUCCESS;
15899:
15900: cursor c_tot_numb_active_records is

Line 15901: select count(*) from ams_imp_source_lines

15897: where import_list_header_id = p_import_list_header_id
15898: and load_status = G_STATUS_SUCCESS;
15899:
15900: cursor c_tot_numb_active_records is
15901: select count(*) from ams_imp_source_lines
15902: where import_list_header_id = p_import_list_header_id
15903: and load_status in ('ACTIVE','RELOAD');
15904:
15905:

Line 17601: -- This program populates the ams_imp-source_lines table.

17597:
17598: END Process_b2c_xml_data;
17599: -- -----------------------------------------------------
17600: --
17601: -- This program populates the ams_imp-source_lines table.
17602: --
17603: Procedure Create_b2b_src_lines(
17604: p_import_list_header_id IN number,
17605: p_cust_b2b_tbl in cust_data_in_tbl,

Line 17821: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,

17817: orig_system_reference
17818: )
17819: VALUES
17820: (
17821: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,
17822: FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
17823: SYSDATE, -- LAST_UPDATE_DATE,
17824: SYSDATE, -- CREATION_DATE,
17825: FND_GLOBAL.User_ID, -- CREATED_BY,

Line 18039: -- This program populates the ams_imp-source_lines table.

18035:
18036: end Create_b2b_src_lines;
18037: -- -------------------------------------------------------------------
18038: --
18039: -- This program populates the ams_imp-source_lines table.
18040: --
18041: Procedure Create_b2c_src_lines(
18042: p_import_list_header_id IN number,
18043: p_cust_b2c_tbl in cust_b2c_data_in_tbl,

Line 18175: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,

18171: rent_owned_indicator
18172: )
18173: VALUES
18174: (
18175: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,
18176: FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
18177: SYSDATE, -- LAST_UPDATE_DATE,
18178: SYSDATE, -- CREATION_DATE,
18179: FND_GLOBAL.User_ID, -- CREATED_BY,

Line 18510: update ams_imp_source_lines set load_status = 'ERROR'

18506: p_log_used_by_id => p_import_list_header_id,
18507: p_msg_data => FND_MESSAGE.get,
18508: p_msg_type => 'DEBUG'
18509: );
18510: update ams_imp_source_lines set load_status = 'ERROR'
18511: where import_list_header_id = p_import_list_header_id
18512: and load_status in ('ACTIVE','RELOAD');
18513:
18514: AMS_List_Import_PUB.error_capture (

Line 18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

18536: x_msg_data OUT NOCOPY VARCHAR2
18537: ) IS
18538: l_limit_rows number := 1000;
18539: l_last_fetch BOOLEAN;
18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;

Line 18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;

18537: ) IS
18538: l_limit_rows number := 1000;
18539: l_last_fetch BOOLEAN;
18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18545:

Line 18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;

18538: l_limit_rows number := 1000;
18539: l_last_fetch BOOLEAN;
18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18545:
18546: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;

18539: l_last_fetch BOOLEAN;
18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18545:
18546: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
18547: L_ORG_KEY ORG_KEY_LIST;

Line 18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;

18540: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18541: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18542: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18543: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18544: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18545:
18546: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
18547: L_ORG_KEY ORG_KEY_LIST;
18548: L_ORG_EXIST ORG_EXIST_LIST;

Line 18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

18805: x_msg_data OUT NOCOPY VARCHAR2
18806: ) IS
18807: l_limit_rows number := 1000;
18808: l_last_fetch BOOLEAN;
18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;

Line 18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;

18806: ) IS
18807: l_limit_rows number := 1000;
18808: l_last_fetch BOOLEAN;
18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18814:

Line 18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;

18807: l_limit_rows number := 1000;
18808: l_last_fetch BOOLEAN;
18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18814:
18815: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;

18808: l_last_fetch BOOLEAN;
18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18814:
18815: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
18816: L_ORG_KEY ORG_KEY_LIST;

Line 18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;

18809: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
18810: TYPE ORG_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_KEY%TYPE;
18811: TYPE ORG_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_EXIST%TYPE;
18812: TYPE ORG_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ORG_PARTY_ID%TYPE;
18813: TYPE PARTY_NAME_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.COL1%TYPE;
18814:
18815: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
18816: L_ORG_KEY ORG_KEY_LIST;
18817: L_ORG_EXIST ORG_EXIST_LIST;

Line 19080: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

19076: x_msg_data OUT NOCOPY VARCHAR2
19077: ) IS
19078: l_limit_rows number := 1000;
19079: l_last_fetch BOOLEAN;
19080: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:

Line 19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;

19077: ) IS
19078: l_limit_rows number := 1000;
19079: l_last_fetch BOOLEAN;
19080: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

Line 19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;

19078: l_limit_rows number := 1000;
19079: l_last_fetch BOOLEAN;
19080: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

Line 19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;

19079: l_last_fetch BOOLEAN;
19080: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

Line 19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19081: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;

Line 19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19082: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;
19090:

Line 19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19083: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;
19090:
19091: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19084:
19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;
19090:
19091: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
19092: L_person_KEY person_KEY_LIST;

Line 19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;

19085: TYPE person_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19086: TYPE person_phone_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19087: TYPE person_address1_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19088: TYPE person_country_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19089: TYPE person_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.load_status%TYPE;
19090:
19091: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
19092: L_person_KEY person_KEY_LIST;
19093: L_person_EXIST person_EXIST_LIST;

Line 19407: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

19403: x_msg_data OUT NOCOPY VARCHAR2
19404: ) IS
19405: l_limit_rows number := 1000;
19406: l_last_fetch BOOLEAN;
19407: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19408: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19409: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19410: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19411:

Line 19408: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;

19404: ) IS
19405: l_limit_rows number := 1000;
19406: l_last_fetch BOOLEAN;
19407: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19408: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19409: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19410: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19411:
19412: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 19409: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;

19405: l_limit_rows number := 1000;
19406: l_last_fetch BOOLEAN;
19407: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19408: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19409: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19410: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19411:
19412: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
19413: L_person_KEY person_KEY_LIST;

Line 19410: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;

19406: l_last_fetch BOOLEAN;
19407: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19408: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19409: TYPE person_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_EXIST%TYPE;
19410: TYPE person_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_PARTY_ID%TYPE;
19411:
19412: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
19413: L_person_KEY person_KEY_LIST;
19414: L_person_EXIST person_EXIST_LIST;

Line 19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

19664: x_msg_data OUT NOCOPY VARCHAR2
19665: ) IS
19666: l_limit_rows number := 1000;
19667: l_last_fetch BOOLEAN;
19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;

Line 19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;

19665: ) IS
19666: l_limit_rows number := 1000;
19667: l_last_fetch BOOLEAN;
19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;

Line 19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;

19666: l_limit_rows number := 1000;
19667: l_last_fetch BOOLEAN;
19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

Line 19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;

19667: l_last_fetch BOOLEAN;
19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;

Line 19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;

19668: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;
19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

Line 19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;

19669: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;
19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19677:

Line 19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19670: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;
19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19677:
19678:

Line 19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;

19671: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;
19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19677:
19678:
19679: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;

19672: TYPE ocont_org_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_key%TYPE;
19673: TYPE ocont_person_key_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_key%TYPE;
19674: TYPE ocont_email_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19675: TYPE ocont_phnumb_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.transposed_phone_number%TYPE;
19676: TYPE ocont_status_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.col1%TYPE;
19677:
19678:
19679: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
19680: L_person_KEY person_KEY_LIST;

Line 20060: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

20056: x_msg_data OUT NOCOPY VARCHAR2
20057: ) IS
20058: l_limit_rows number := 1000;
20059: l_last_fetch BOOLEAN;
20060: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20061: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
20062: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
20063: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
20064:

Line 20061: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;

20057: ) IS
20058: l_limit_rows number := 1000;
20059: l_last_fetch BOOLEAN;
20060: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20061: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
20062: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
20063: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
20064:
20065: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 20062: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;

20058: l_limit_rows number := 1000;
20059: l_last_fetch BOOLEAN;
20060: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20061: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
20062: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
20063: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
20064:
20065: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20066: L_person_KEY person_KEY_LIST;

Line 20063: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;

20059: l_last_fetch BOOLEAN;
20060: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20061: TYPE person_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.person_KEY%TYPE;
20062: TYPE ocont_EXIST_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_EXIST%TYPE;
20063: TYPE ocont_PARTY_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.ocont_PARTY_ID%TYPE;
20064:
20065: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20066: L_person_KEY person_KEY_LIST;
20067: L_ocont_EXIST ocont_EXIST_LIST;

Line 20374: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

20370: x_msg_data OUT NOCOPY VARCHAR2
20371: ) is
20372: l_limit_rows number := 1000;
20373: l_last_fetch BOOLEAN;
20374: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20375: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20376: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20377:
20378: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 20375: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;

20371: ) is
20372: l_limit_rows number := 1000;
20373: l_last_fetch BOOLEAN;
20374: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20375: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20376: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20377:
20378: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20379: L_address_KEY address_KEY_LIST;

Line 20376: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;

20372: l_limit_rows number := 1000;
20373: l_last_fetch BOOLEAN;
20374: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20375: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20376: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20377:
20378: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20379: L_address_KEY address_KEY_LIST;
20380: L_PARTY_location_ID PARTY_location_ID_LIST;

Line 20532: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

20528: x_msg_data OUT NOCOPY VARCHAR2
20529: ) is
20530: l_limit_rows number := 1000;
20531: l_last_fetch BOOLEAN;
20532: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20533: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_address_KEY%TYPE;
20534: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_location_ID%TYPE;
20535:
20536: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 20533: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_address_KEY%TYPE;

20529: ) is
20530: l_limit_rows number := 1000;
20531: l_last_fetch BOOLEAN;
20532: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20533: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_address_KEY%TYPE;
20534: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_location_ID%TYPE;
20535:
20536: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20537: L_org_address_KEY address_KEY_LIST;

Line 20534: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_location_ID%TYPE;

20530: l_limit_rows number := 1000;
20531: l_last_fetch BOOLEAN;
20532: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20533: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_address_KEY%TYPE;
20534: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.org_location_ID%TYPE;
20535:
20536: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20537: L_org_address_KEY address_KEY_LIST;
20538: L_org_location_ID party_location_ID_LIST;

Line 20690: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

20686: ) IS
20687:
20688: l_limit_rows number := 1000;
20689: l_last_fetch BOOLEAN;
20690: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20691: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20692: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20693:
20694: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;

Line 20691: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;

20687:
20688: l_limit_rows number := 1000;
20689: l_last_fetch BOOLEAN;
20690: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20691: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20692: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20693:
20694: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20695: L_address_KEY address_KEY_LIST;

Line 20692: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;

20688: l_limit_rows number := 1000;
20689: l_last_fetch BOOLEAN;
20690: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
20691: TYPE address_KEY_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.address_KEY%TYPE;
20692: TYPE PARTY_location_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.PARTY_location_ID%TYPE;
20693:
20694: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
20695: L_address_KEY address_KEY_LIST;
20696: L_PARTY_location_ID PARTY_location_ID_LIST;

Line 20894: select count(*) from ams_imp_source_lines

20890: from ams_imp_list_headers_all
20891: where import_list_header_id = p_import_list_header_id;
20892:
20893: cursor c_dup_recs is
20894: select count(*) from ams_imp_source_lines
20895: where import_list_header_id = p_import_list_header_id
20896: and duplicate_flag = 'Y';
20897:
20898: cursor c_error_exist is

Line 20905: select count(*) from ams_imp_source_lines

20901: and error_type = 'E'
20902: and rownum < 2;
20903:
20904: cursor c_loaded_records is
20905: select count(*) from ams_imp_source_lines
20906: where import_list_header_id = p_import_list_header_id
20907: and load_status = G_STATUS_SUCCESS;
20908:
20909: cursor c_failed_records is

Line 20910: select count(*) from ams_imp_source_lines

20906: where import_list_header_id = p_import_list_header_id
20907: and load_status = G_STATUS_SUCCESS;
20908:
20909: cursor c_failed_records is
20910: select count(*) from ams_imp_source_lines
20911: where import_list_header_id = p_import_list_header_id
20912: and load_status = G_STATUS_ERROR;
20913:
20914: cursor c_total_records is

Line 20915: select count(*) from ams_imp_source_lines

20911: where import_list_header_id = p_import_list_header_id
20912: and load_status = G_STATUS_ERROR;
20913:
20914: cursor c_total_records is
20915: select count(*) from ams_imp_source_lines
20916: where import_list_header_id = p_import_list_header_id
20917: and load_status = 'ACTIVE';
20918:
20919: cursor c_dup_records is

Line 20920: select count(*) from ams_imp_source_lines

20916: where import_list_header_id = p_import_list_header_id
20917: and load_status = 'ACTIVE';
20918:
20919: cursor c_dup_records is
20920: select count(*) from ams_imp_source_lines
20921: where import_list_header_id = p_import_list_header_id
20922: and load_status = G_STATUS_DUPLICATE;
20923:
20924: cursor c_file_type is

Line 20929: select COL68 from ams_imp_source_lines

20925: select file_type from ams_imp_documents
20926: where import_list_header_id = p_import_list_header_id;
20927:
20928: cursor c_dup_xml_recs is
20929: select COL68 from ams_imp_source_lines
20930: where load_status = 'DUPLICATE'
20931: AND import_list_header_id = p_import_list_header_id;
20932:
20933:

Line 20980: (select import_source_line_id from ams_imp_source_lines where

20976:
20977: -- Delete all the errors from the errors table which are sucessful.
20978: delete from ams_list_import_errors where import_list_header_id
20979: = p_import_list_header_id and import_source_line_id in
20980: (select import_source_line_id from ams_imp_source_lines where
20981: import_list_header_id = p_import_list_header_id and load_status = G_STATUS_SUCCESS);
20982: open c_loaded_records;
20983: fetch c_loaded_records into l_loaded_records;
20984: close c_loaded_records;

Line 21091: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

21087:
21088: l_limit_rows number := 1000;
21089: l_last_fetch BOOLEAN;
21090:
21091: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
21092:
21093: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
21094:
21095: cursor c_ordered_recs is

Line 21098: (select import_source_line_id from ams_imp_source_lines

21094:
21095: cursor c_ordered_recs is
21096:
21097: select ab.import_source_line_id from
21098: (select import_source_line_id from ams_imp_source_lines
21099: where import_list_header_id = p_import_list_header_id
21100: and request_id is null
21101: order by org_key) ab
21102: where rownum <= p_batch_size;

Line 21104: select import_source_line_id from ams_imp_source_lines

21100: and request_id is null
21101: order by org_key) ab
21102: where rownum <= p_batch_size;
21103: /*
21104: select import_source_line_id from ams_imp_source_lines
21105: where import_list_header_id = p_import_list_header_id
21106: and request_id is null
21107: and rownum <= p_batch_size;
21108:

Line 21113: from ams_imp_source_lines

21109:
21110: SELECT AB.import_source_line_id
21111: from
21112: ( select import_source_line_id, org_key ,ROW_NUMBER() OVER (order by org_key) as id
21113: from ams_imp_source_lines
21114: where import_list_header_id = p_import_list_header_id
21115: and request_id is null ) AB
21116: where AB.id <= p_batch_size;
21117: */

Line 21134: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES

21130:
21131: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
21132: EXIT;
21133: END IF;
21134: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES
21135: SET request_id = p_request_id
21136: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
21137:
21138: if l_last_fetch then

Line 21176: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;

21172: l_limit_rows number := 1000;
21173: l_last_fetch BOOLEAN;
21174: l_imp_type varchar2(30);
21175:
21176: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
21177: TYPE TRANSPOSED_PH_NUMBER_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.TRANSPOSED_PHONE_NUMBER%TYPE;
21178:
21179: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
21180: L_TRANSPOSED_PHONE_NUMBER TRANSPOSED_PH_NUMBER_LIST;

Line 21177: TYPE TRANSPOSED_PH_NUMBER_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.TRANSPOSED_PHONE_NUMBER%TYPE;

21173: l_last_fetch BOOLEAN;
21174: l_imp_type varchar2(30);
21175:
21176: TYPE IMPORT_SOURCE_LINE_ID_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.IMPORT_SOURCE_LINE_ID%TYPE;
21177: TYPE TRANSPOSED_PH_NUMBER_LIST IS TABLE OF AMS_IMP_SOURCE_LINES.TRANSPOSED_PHONE_NUMBER%TYPE;
21178:
21179: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
21180: L_TRANSPOSED_PHONE_NUMBER TRANSPOSED_PH_NUMBER_LIST;
21181:

Line 21224: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES

21220:
21221: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
21222: EXIT;
21223: END IF;
21224: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES
21225: SET TRANSPOSED_PHONE_NUMBER = L_TRANSPOSED_PHONE_NUMBER(i)
21226: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
21227: if l_last_fetch then
21228: exit;

Line 21247: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES

21243:
21244: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
21245: EXIT;
21246: END IF;
21247: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_IMP_SOURCE_LINES
21248: SET TRANSPOSED_PHONE_NUMBER = L_TRANSPOSED_PHONE_NUMBER(i)
21249: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
21250: if l_last_fetch then
21251: exit;