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 1136: update ams_imp_source_lines

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

Line 1153: update ams_imp_source_lines

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

Line 1635: UPDATE ams_imp_source_lines

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

Line 1656: UPDATE ams_imp_source_lines

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

Line 1678: UPDATE ams_imp_source_lines

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

Line 1694: UPDATE ams_imp_source_lines

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

Line 1708: UPDATE ams_imp_source_lines

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

Line 2032: UPDATE ams_imp_source_lines

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

Line 2053: UPDATE ams_imp_source_lines

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

Line 2075: UPDATE ams_imp_source_lines

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

Line 2091: UPDATE ams_imp_source_lines

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

Line 2106: UPDATE ams_imp_source_lines

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

Line 2180: UPDATE ams_imp_source_lines

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

Line 2281: FROM ams_imp_source_lines

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

Line 2293: select count(*) from ams_imp_source_lines

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

Line 2298: SELECT COUNT(*) FROM ams_imp_source_lines

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

Line 2302: select 'Y' from ams_imp_source_lines

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

Line 2308: select count(*) from ams_imp_source_lines

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

Line 2357: UPDATE ams_imp_source_lines

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

Line 3067: select count(*) from ams_imp_source_lines

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

Line 3072: select count(*) from ams_imp_source_lines

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

Line 3117: (select import_source_line_id from ams_imp_source_lines where

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

Line 3249: (select import_source_line_id from ams_imp_source_lines where

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

Line 3383: (select import_source_line_id from ams_imp_source_lines where

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

Line 3761: select import_source_line_id from ams_imp_source_lines

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

Line 3770: select COL68 from ams_imp_source_lines

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

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

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

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

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

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

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

Line 4031: select count(*) from ams_imp_source_lines

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

Line 4036: select count(*) from ams_imp_source_lines lines

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

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

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

Line 4068: select count(*) from ams_imp_source_lines

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

Line 4073: select count(*) from ams_imp_source_lines

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

Line 4132: (select import_source_line_id from ams_imp_source_lines where

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

Line 4474: (select import_source_line_id from ams_imp_source_lines where

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

Line 4864: from ams_imp_list_headers_all hd, ams_imp_source_lines sr

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

Line 7649: UPDATE ams_imp_source_lines

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 8258: UPDATE ams_imp_source_lines

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

Line 11088: UPDATE ams_imp_source_lines

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

Line 13674: select count(*) from ams_imp_source_lines

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

Line 13694: select 'Y' from ams_imp_source_lines

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

Line 13707: select count(*) from ams_imp_source_lines

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

Line 13712: select count(*) from ams_imp_source_lines

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

Line 13724: select count(*) from ams_imp_source_lines

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

Line 13815: UPDATE ams_imp_source_lines

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

Line 14288: (select import_source_line_id from ams_imp_source_lines where

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

Line 14486: (select import_source_line_id from ams_imp_source_lines where

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

Line 14609: (select import_source_line_id from ams_imp_source_lines where

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

Line 14698: (select import_source_line_id from ams_imp_source_lines where

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

Line 15836: UPDATE ams_imp_source_lines

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

Line 15881: select count(*) from ams_imp_source_lines

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

Line 15885: select count(*) from ams_imp_source_lines

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

Line 15890: select count(*) from ams_imp_source_lines

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

Line 15895: select count(*) from ams_imp_source_lines

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

Line 15900: select count(*) from ams_imp_source_lines

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

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

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

Line 17820: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,

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

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

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

Line 18174: ams_imp_source_lines_s.nextval, -- IMPORT_SOURCE_LINE_ID,

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

Line 18509: update ams_imp_source_lines set load_status = 'ERROR'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 20893: select count(*) from ams_imp_source_lines

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

Line 20904: select count(*) from ams_imp_source_lines

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

Line 20909: select count(*) from ams_imp_source_lines

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

Line 20914: select count(*) from ams_imp_source_lines

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

Line 20919: select count(*) from ams_imp_source_lines

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

Line 20928: select COL68 from ams_imp_source_lines

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

Line 20979: (select import_source_line_id from ams_imp_source_lines where

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

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

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

Line 21097: (select import_source_line_id from ams_imp_source_lines

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

Line 21103: select import_source_line_id from ams_imp_source_lines

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

Line 21112: from ams_imp_source_lines

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

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

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

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

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

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

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

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

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

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

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