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_LIST_HEADERS_ALL

Line 393: select nvl(batch_size,0),parent_request_id from ams_imp_list_headers_all

389: i_batch_size number;
390:
391:
392: cursor c_header_status is
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

Line 406: select import_type from ams_imp_list_headers_all

402:
403: -- Cursor to check if it's B2B or B2C party type.
404:
405: cursor b2borb2c is
406: select import_type from ams_imp_list_headers_all
407: where import_list_header_id = p_import_list_header_id;
408:
409:
410: /* For Mapped party */

Line 413: ams_imp_list_headers_all hd

409:
410: /* For Mapped party */
411: cursor party_mapped is
412: SELECT 'Y' FROM ams_list_src_fields sf,
413: ams_imp_list_headers_all hd
414: WHERE sf.field_column_name = 'PARTY_ID'
415: AND sf.enabled_flag = 'Y'
416: AND sf.list_source_type_id = hd.list_source_type_id
417: and hd.import_type = 'CUSTOMER'

Line 932: select status_code from ams_imp_list_headers_all

928: where fnd.user_Name = upper(x_sa_email_address)
929: and fnd.user_id = ams.user_id;
930:
931: cursor c_hdr_status is
932: select status_code from ams_imp_list_headers_all
933: where import_list_header_id = p_import_list_header_id;
934:
935: party_rec hz_party_v2pub.party_rec_type;
936: org_rec hz_party_v2pub.organization_rec_type;

Line 1083: update ams_imp_list_headers_all set BATCH_SIZE = l_numb_of_inst,

1079: end if;
1080: if l_numb_of_inst > 10 then
1081: l_numb_of_inst := 10;
1082: end if;
1083: update ams_imp_list_headers_all set BATCH_SIZE = l_numb_of_inst,
1084: parent_request_id = l_request_id
1085: where import_list_header_id = p_import_list_header_id;
1086: commit;
1087:

Line 1182: update ams_imp_list_headers_all set BATCH_SIZE = BATCH_SIZE - 1

1178: p_msg_type => 'DEBUG');
1179: */
1180: ELSIF p_number_of_processes = 0 THEN
1181:
1182: update ams_imp_list_headers_all set BATCH_SIZE = BATCH_SIZE - 1
1183: where import_list_header_id = p_import_list_header_id;
1184: commit;
1185:
1186:

Line 1745: UPDATE ams_imp_list_headers_all

1741: l_vr_code_exists := null;
1742: END LOOP;
1743: CLOSE b2b;
1744:
1745: UPDATE ams_imp_list_headers_all
1746: -- SET processed_rows = i_number_of_rows_processed
1747: SET processed_rows = nvl(processed_rows,0) + i_number_of_rows_processed
1748: WHERE import_list_header_id = p_import_list_header_id;
1749:

Line 2144: UPDATE ams_imp_list_headers_all

2140:
2141: END LOOP;
2142: CLOSE b2c;
2143:
2144: UPDATE ams_imp_list_headers_all
2145: -- SET processed_rows = i_number_of_rows_processed
2146: SET processed_rows = nvl(processed_rows,0) + i_number_of_rows_processed
2147: WHERE import_list_header_id = p_import_list_header_id;
2148:

Line 2201: UPDATE ams_imp_list_headers_all

2197: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2198: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2199: system_status_code = 'ERROR' and default_flag = 'Y';
2200:
2201: UPDATE ams_imp_list_headers_all
2202: set status_code = l_lookup_code,
2203: user_status_id = l_user_status_id,
2204: status_date = sysdate
2205: where import_list_header_id = p_import_list_header_id;

Line 2285: select name, server_flag, status_code, import_type, execute_mode from ams_imp_list_headers_all

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
2286: where import_list_header_id = p_import_list_header_id;
2287:
2288: cursor c_status is
2289: select status_code, nvl(dedupe_flag,'N') from ams_imp_list_headers_all

Line 2289: select status_code, nvl(dedupe_flag,'N') from ams_imp_list_headers_all

2285: select name, server_flag, status_code, import_type, execute_mode from ams_imp_list_headers_all
2286: where import_list_header_id = p_import_list_header_id;
2287:
2288: cursor c_status is
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

Line 2315: ams_imp_list_headers_all hd

2311:
2312: -- For Mapped osr
2313: cursor osr_mapped is
2314: SELECT 'Y' FROM ams_list_src_fields sf,
2315: ams_imp_list_headers_all hd
2316: WHERE sf.field_column_name = 'ORIG_SYSTEM_REFERENCE'
2317: AND sf.enabled_flag = 'Y'
2318: AND sf.list_source_type_id = hd.list_source_type_id
2319: and hd.import_type in ('B2B','B2C')

Line 2350: UPDATE ams_imp_list_headers_all

2346: system_status_code = 'STAGED' and default_flag = 'Y';
2347:
2348: select as_import_interface_s.nextval into l_batch_id from dual;
2349:
2350: UPDATE ams_imp_list_headers_all
2351: set status_code = l_lookup_code,
2352: user_status_id = l_user_status_id,
2353: batch_id = l_batch_id,
2354: status_date = sysdate

Line 2378: UPDATE ams_imp_list_headers_all

2374: -- commented by ndadwal for Bug 5113207
2375: /* if l_status_code = 'NEW' then
2376: if l_server_flag = 'Y' then
2377: if p_staged_only = 'Y' then
2378: UPDATE ams_imp_list_headers_all
2379: set loaded_no_of_rows = l_loaded_rows,
2380: loaded_date = sysdate,
2381: status_code = 'STAGING',
2382: status_date = sysdate

Line 2392: UPDATE ams_imp_list_headers_all

2388: -- added by ndadwal for Bug 5113207
2389: if l_status_code = 'NEW' then
2390: if l_server_flag = 'Y' then
2391: if p_staged_only = 'Y' then
2392: UPDATE ams_imp_list_headers_all
2393: set loaded_no_of_rows = l_loaded_rows,
2394: loaded_date = sysdate,
2395: status_code = 'STAGED',
2396: status_date = sysdate

Line 2407: UPDATE ams_imp_list_headers_all

2403: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2404: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2405: system_status_code = 'STAGED' and default_flag = 'Y';
2406:
2407: UPDATE ams_imp_list_headers_all
2408: set loaded_no_of_rows = l_loaded_rows,
2409: loaded_date = sysdate,
2410: status_code = l_lookup_code,
2411: user_status_id = l_user_status_id,

Line 2472: UPDATE ams_imp_list_headers_all

2468: l_user_status_id := null;
2469: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2470: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2471: system_status_code = 'ERROR' and default_flag = 'Y';
2472: UPDATE ams_imp_list_headers_all
2473: set status_code = l_lookup_code,
2474: user_status_id = l_user_status_id,
2475: status_date = sysdate
2476: where import_list_header_id = p_import_list_header_id;

Line 2544: UPDATE ams_imp_list_headers_all

2540: l_user_status_id := null;
2541: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2542: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2543: system_status_code = 'ERROR' and default_flag = 'Y';
2544: UPDATE ams_imp_list_headers_all
2545: set status_code = l_lookup_code,
2546: user_status_id = l_user_status_id,
2547: status_date = sysdate
2548: where import_list_header_id = p_import_list_header_id;

Line 2576: UPDATE ams_imp_list_headers_all

2572: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2573: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2574: system_status_code = 'STAGED' and default_flag = 'Y';
2575:
2576: UPDATE ams_imp_list_headers_all
2577: set loaded_no_of_rows = l_loaded_rows,
2578: loaded_date = sysdate,
2579: status_code = l_lookup_code,
2580: user_status_id = l_user_status_id,

Line 2628: UPDATE ams_imp_list_headers_all

2624: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2625: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2626: system_status_code = 'ERROR' and default_flag = 'Y';
2627:
2628: UPDATE ams_imp_list_headers_all
2629: set status_code = l_lookup_code,
2630: user_status_id = l_user_status_id,
2631: status_date = sysdate
2632: where import_list_header_id = p_import_list_header_id;

Line 2716: UPDATE ams_imp_list_headers_all

2712: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2713: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2714: system_status_code = 'ERROR' and default_flag = 'Y';
2715:
2716: UPDATE ams_imp_list_headers_all
2717: set status_code = l_lookup_code,
2718: user_status_id = l_user_status_id,
2719: status_date = sysdate
2720: where import_list_header_id = p_import_list_header_id;

Line 2742: UPDATE ams_imp_list_headers_all

2738: l_user_status_id := null;
2739: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2740: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2741: system_status_code = 'ERROR' and default_flag = 'Y';
2742: UPDATE ams_imp_list_headers_all
2743: set status_code = l_lookup_code,
2744: user_status_id = l_user_status_id,
2745: status_date = sysdate
2746: where import_list_header_id = p_import_list_header_id;

Line 2764: UPDATE ams_imp_list_headers_all

2760: l_user_status_id := null;
2761: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2762: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2763: system_status_code = 'ERROR' and default_flag = 'Y';
2764: UPDATE ams_imp_list_headers_all
2765: set status_code = l_lookup_code,
2766: user_status_id = l_user_status_id,
2767: status_date = sysdate
2768: where import_list_header_id = p_import_list_header_id;

Line 2786: UPDATE ams_imp_list_headers_all

2782: l_user_status_id := null;
2783: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2784: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2785: system_status_code = 'ERROR' and default_flag = 'Y';
2786: UPDATE ams_imp_list_headers_all
2787: set status_code = l_lookup_code,
2788: user_status_id = l_user_status_id,
2789: status_date = sysdate
2790: where import_list_header_id = p_import_list_header_id;

Line 2814: UPDATE ams_imp_list_headers_all

2810: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2811: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2812: system_status_code = 'ERROR' and default_flag = 'Y';
2813:
2814: UPDATE ams_imp_list_headers_all
2815: set status_code = l_lookup_code,
2816: user_status_id = l_user_status_id,
2817: status_date = sysdate
2818: where import_list_header_id = p_import_list_header_id;

Line 2837: UPDATE ams_imp_list_headers_all

2833: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
2834: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2835: system_status_code = 'ERROR' and default_flag = 'Y';
2836:
2837: UPDATE ams_imp_list_headers_all
2838: set status_code = l_lookup_code,
2839: user_status_id = l_user_status_id,
2840: status_date = sysdate
2841: where import_list_header_id = p_import_list_header_id;

Line 2862: update ams_imp_list_headers_all

2858:
2859: open c_dup_recs;
2860: fetch c_dup_recs into l_duplicate_records;
2861: close c_dup_recs;
2862: update ams_imp_list_headers_all
2863: set number_of_duplicate_records = l_duplicate_records
2864: where import_list_header_id = p_import_list_header_id;
2865:
2866: AMS_Utility_PVT.Create_Log (

Line 2933: select LOADED_NO_OF_ROWS into l_load_rows from ams_imp_list_headers_all

2929: p_msg_data => 'Starting List Generation for List Import.',
2930: p_msg_type => 'DEBUG'
2931: );
2932: l_load_rows := 0;
2933: select LOADED_NO_OF_ROWS into l_load_rows from ams_imp_list_headers_all
2934: where import_list_header_id = p_import_list_header_id;
2935:
2936: if l_load_rows > 0 then
2937:

Line 3053: select import_type,VALIDATE_FILE,NUMBER_OF_INSTANCES from ams_imp_list_headers_all

3049: l_numb_of_inst number;
3050:
3051:
3052: cursor c_import_type is
3053: select import_type,VALIDATE_FILE,NUMBER_OF_INSTANCES from ams_imp_list_headers_all
3054: where import_list_header_id = p_import_list_header_id;
3055:
3056: cursor c_conc_name is
3057: SELECT concurrent_program FROM ams_imp_list_import_types

Line 3094: UPDATE ams_imp_list_headers_all

3090: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3091: system_status_code = 'SCHEDULED' and default_flag = 'Y';
3092:
3093:
3094: UPDATE ams_imp_list_headers_all
3095: set status_code = l_lookup_code,
3096: user_status_id = l_user_status_id,
3097: status_date = sysdate
3098: where import_list_header_id = p_import_list_header_id;

Line 3143: UPDATE ams_imp_list_headers_all

3139: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3140: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3141: system_status_code = 'ERROR' and default_flag = 'Y';
3142:
3143: UPDATE ams_imp_list_headers_all
3144: set status_code = l_lookup_code,
3145: user_status_id = l_user_status_id,
3146: status_date = sysdate
3147: where import_list_header_id = p_import_list_header_id;

Line 3190: UPDATE ams_imp_list_headers_all

3186: l_user_status_id := null;
3187: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3188: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3189: system_status_code = 'ERROR' and default_flag = 'Y';
3190: UPDATE ams_imp_list_headers_all
3191: set status_code = l_lookup_code,
3192: user_status_id = l_user_status_id,
3193: status_date = sysdate
3194: where import_list_header_id = p_import_list_header_id;

Line 3269: UPDATE ams_imp_list_headers_all

3265: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3266: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3267: system_status_code = 'ERROR' and default_flag = 'Y';
3268:
3269: UPDATE ams_imp_list_headers_all
3270: set status_code = l_lookup_code,
3271: user_status_id = l_user_status_id,
3272: status_date = sysdate
3273: where import_list_header_id = p_import_list_header_id;

Line 3333: UPDATE ams_imp_list_headers_all

3329: l_user_status_id := null;
3330: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3331: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3332: system_status_code = 'ERROR' and default_flag = 'Y';
3333: UPDATE ams_imp_list_headers_all
3334: set status_code = l_lookup_code,
3335: user_status_id = l_user_status_id,
3336: status_date = sysdate
3337: where import_list_header_id = p_import_list_header_id;

Line 3373: UPDATE ams_imp_list_headers_all

3369: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3370: system_status_code = 'ERROR' and default_flag = 'Y';
3371: end if;
3372:
3373: UPDATE ams_imp_list_headers_all
3374: set status_code = l_lookup_code,
3375: user_status_id = l_user_status_id,
3376: status_date = sysdate
3377: where import_list_header_id = p_import_list_header_id;

Line 3392: UPDATE ams_imp_list_headers_all

3388: close c_loaded_records;
3389: open c_failed_records;
3390: fetch c_failed_records into l_failed_records;
3391: close c_failed_records;
3392: UPDATE ams_imp_list_headers_all
3393: SET loaded_no_of_rows = l_loaded_records,
3394: number_of_failed_records = l_failed_records,
3395: loaded_date = sysdate
3396: WHERE import_list_header_id = p_import_list_header_id;

Line 3411: UPDATE ams_imp_list_headers_all

3407: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3408: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3409: system_status_code = 'ERROR' and default_flag = 'Y';
3410:
3411: UPDATE ams_imp_list_headers_all
3412: set status_code = l_lookup_code,
3413: user_status_id = l_user_status_id,
3414: status_date = sysdate
3415: where import_list_header_id = p_import_list_header_id;

Line 3466: select import_type from ams_imp_list_headers_all where import_list_header_id = p_import_list_header_id;

3462: cursor c_file_type is
3463: select file_type from ams_imp_documents where import_list_header_id = p_import_list_header_id;
3464:
3465: cursor c_imptype is
3466: select import_type from ams_imp_list_headers_all where import_list_header_id = p_import_list_header_id;
3467:
3468:
3469: begin
3470:

Line 3478: update ams_imp_list_headers_all

3474: p_log_used_by_id => p_import_list_header_id,
3475: p_msg_data => 'Starting List Import Process',
3476: p_msg_type => 'DEBUG'
3477: );
3478: update ams_imp_list_headers_all
3479: set GENERATE_LIST = decode(p_generate_list,'Y','Y','DUMMY','N','N'),
3480: GENERATED_LIST_NAME = p_list_name
3481: where import_list_header_id = p_import_list_header_id;
3482: commit;

Line 3501: UPDATE ams_imp_list_headers_all

3497: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3498: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3499: system_status_code = 'ERROR' and default_flag = 'Y';
3500:
3501: UPDATE ams_imp_list_headers_all
3502: set status_code = l_lookup_code,
3503: user_status_id = l_user_status_id,
3504: status_date = sysdate
3505: where import_list_header_id = p_import_list_header_id;

Line 3525: UPDATE ams_imp_list_headers_all

3521: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3522: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3523: system_status_code = 'ERROR' and default_flag = 'Y';
3524:
3525: UPDATE ams_imp_list_headers_all
3526: set status_code = 'ERROR',
3527: user_status_id = l_user_status_id,
3528: status_date = sysdate
3529: where import_list_header_id = p_import_list_header_id;

Line 3560: UPDATE ams_imp_list_headers_all

3556: l_user_status_id := null;
3557: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3558: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3559: system_status_code = 'ERROR' and default_flag = 'Y';
3560: UPDATE ams_imp_list_headers_all
3561: set status_code = l_lookup_code,
3562: user_status_id = l_user_status_id,
3563: status_date = sysdate
3564: where import_list_header_id = p_import_list_header_id;

Line 3616: select import_type from ams_imp_list_headers_all where import_list_header_id = p_import_list_header_id;

3612: cursor c_prof_file_size is
3613: select file_size, file_type from AMS_IMP_DOCUMENTS where import_list_header_id = p_import_list_header_id;
3614:
3615: cursor c_imptype is
3616: select import_type from ams_imp_list_headers_all where import_list_header_id = p_import_list_header_id;
3617:
3618: BEGIN
3619: -- x_prof_file_size := fnd_profile.value('AMS_IMP_CLIENT_FILE_SIZE');
3620: open c_prof_file_size;

Line 3626: update ams_imp_list_headers_all

3622: close c_prof_file_size;
3623: open c_imptype;
3624: fetch c_imptype into x_imp_type;
3625: close c_imptype;
3626: update ams_imp_list_headers_all
3627: set GENERATE_LIST = decode(p_generate_list,'Y','Y','DUMMY','N','N'),
3628: GENERATED_LIST_NAME = p_list_name
3629: where import_list_header_id = p_import_list_header_id;
3630: commit;

Line 3645: UPDATE ams_imp_list_headers_all

3641: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3642: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3643: system_status_code = 'ERROR' and default_flag = 'Y';
3644:
3645: UPDATE ams_imp_list_headers_all
3646: set status_code = 'ERROR',
3647: user_status_id = l_user_status_id,
3648: status_date = sysdate
3649: where import_list_header_id = p_import_list_header_id;

Line 3680: UPDATE ams_imp_list_headers_all

3676: l_user_status_id := null;
3677: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3678: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3679: system_status_code = 'ERROR' and default_flag = 'Y';
3680: UPDATE ams_imp_list_headers_all
3681: set status_code = l_lookup_code,
3682: user_status_id = l_user_status_id,
3683: status_date = sysdate
3684: where import_list_header_id = p_import_list_header_id;

Line 3735: FROM ams_imp_list_headers_all

3731:
3732: /* mayjain bug 5235979 */
3733: cursor c_rule_type is
3734: SELECT 'IMPORT_CUST_' || import_type
3735: FROM ams_imp_list_headers_all
3736: where import_list_header_id = p_import_list_header_id;
3737:
3738: cursor c_ruleid (p_rule_type VARCHAR2) is
3739: SELECT us.list_rule_id

Line 3757: ams_imp_list_headers_all

3753: WHERE list_rule_id = i_rule_id;
3754:
3755: cursor c_word is
3756: select nvl(ENABLE_WORD_REPLACEMENT_FLAG,'N'), nvl(b2b_flag,'N') from
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

Line 3997: from ams_imp_list_headers_all

3993: WHERE load_status = 'SUCCESS' AND batch_id = l_batch_id;
3994:
3995: cursor c_event_status is
3996: select nvl(PROCESSED_ROWS,0), nvl(NUMBER_OF_FAILED_RECORDS,0)
3997: from ams_imp_list_headers_all
3998: where import_list_header_id = p_import_list_header_id;
3999:
4000: cursor c_conc_name is
4001: SELECT concurrent_program FROM ams_imp_list_import_types

Line 4005: SELECT batch_id, VALIDATE_FILE, EXECUTE_MODE FROM ams_imp_list_headers_all

4001: SELECT concurrent_program FROM ams_imp_list_import_types
4002: where import_type = 'LEAD';
4003:
4004: cursor c_lead_process is
4005: SELECT batch_id, VALIDATE_FILE, EXECUTE_MODE FROM ams_imp_list_headers_all
4006: WHERE import_list_header_id = p_import_list_header_id ;
4007:
4008: cursor c_lead_source_system is
4009: select source_system from ams_lead_mapping_v

Line 4152: UPDATE ams_imp_list_headers_all

4148: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
4149: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
4150: system_status_code = 'ERROR' and default_flag = 'Y';
4151:
4152: UPDATE ams_imp_list_headers_all
4153: set status_code = l_lookup_code,
4154: user_status_id = l_user_status_id,
4155: status_date = sysdate
4156: where import_list_header_id = p_import_list_header_id;

Line 4204: UPDATE ams_imp_list_headers_all

4200: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
4201: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
4202: system_status_code = 'ERROR' and default_flag = 'Y';
4203:
4204: UPDATE ams_imp_list_headers_all
4205: set status_code = l_lookup_code,
4206: user_status_id = l_user_status_id,
4207: status_date = sysdate
4208: where import_list_header_id = p_import_list_header_id;

Line 4233: UPDATE ams_imp_list_headers_all

4229: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
4230: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
4231: system_status_code = 'ERROR' and default_flag = 'Y';
4232:
4233: UPDATE ams_imp_list_headers_all
4234: set status_code = l_lookup_code,
4235: user_status_id = l_user_status_id,
4236: status_date = sysdate,
4237: number_of_failed_records = l_rec_failed_in_ams

Line 4273: UPDATE ams_imp_list_headers_all

4269: l_user_status_id := null;
4270: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
4271: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
4272: system_status_code = 'ERROR' and default_flag = 'Y';
4273: UPDATE ams_imp_list_headers_all
4274: set status_code = l_lookup_code,
4275: user_status_id = l_user_status_id,
4276: status_date = sysdate
4277: where import_list_header_id = p_import_list_header_id;

Line 4332: UPDATE ams_imp_list_headers_all

4328: l_user_status_id := null;
4329: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
4330: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
4331: system_status_code = 'ERROR' and default_flag = 'Y';
4332: UPDATE ams_imp_list_headers_all
4333: set status_code = l_lookup_code,
4334: user_status_id = l_user_status_id,
4335: status_date = sysdate
4336: where import_list_header_id = p_import_list_header_id;

Line 4383: UPDATE ams_imp_list_headers_all

4379: p_msg_data => 'Some of the leads in the batch has errors.',
4380: p_msg_type => 'DEBUG'
4381: );
4382: /*
4383: UPDATE ams_imp_list_headers_all
4384: set status_code = l_lookup_code,
4385: user_status_id = l_user_status_id,
4386: status_date = sysdate
4387: where import_list_header_id = p_import_list_header_id;

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 6768: FROM ams_imp_list_headers_all

6764:
6765: CURSOR c_lead_batch_id is
6766: SELECT
6767: batch_id
6768: FROM ams_imp_list_headers_all
6769: WHERE import_list_header_id = p_import_list_header_id;
6770:
6771: CURSOR c_lead_date_fields is
6772: SELECT

Line 7727: from ams_imp_list_headers_all

7723: and batch_id = p_batch_id;
7724:
7725: cursor c_imp_type is
7726: select import_type
7727: from ams_imp_list_headers_all
7728: where import_list_header_id = p_import_list_header_id;
7729:
7730: cursor c_b2bxmlrec is
7731: select org_imp_xml_element_id, add_imp_xml_element_id, ocont_imp_xml_element_id,

Line 8115: FROM ams_imp_list_headers_all

8111:
8112: CURSOR c_lead_batch_id is
8113: SELECT
8114: batch_id
8115: FROM ams_imp_list_headers_all
8116: WHERE import_list_header_id = p_import_list_header_id;
8117:
8118: CURSOR c_as_list_headers is
8119: SELECT

Line 8607: FROM ams_imp_list_headers_all

8603:
8604: CURSOR c_lead_batch_id is
8605: SELECT
8606: batch_id
8607: FROM ams_imp_list_headers_all
8608: WHERE import_list_header_id = p_import_list_header_id;
8609:
8610: -- Cursor for B2B party type
8611:

Line 10007: select import_type from ams_imp_list_headers_all

10003:
10004: -- Cursor to check if it's B2B or B2C party type.
10005:
10006: cursor b2borb2c is
10007: select import_type from ams_imp_list_headers_all
10008: where import_list_header_id = p_import_list_header_id;
10009:
10010:
10011: CURSOR c_b2b_date_fields is

Line 11138: FROM ams_imp_list_headers_all

11134:
11135: CURSOR c_lead_batch_id is
11136: SELECT
11137: batch_id
11138: FROM ams_imp_list_headers_all
11139: WHERE import_list_header_id = p_import_list_header_id;
11140:
11141: -- Cursor for B2B party type
11142:

Line 11978: select import_type from ams_imp_list_headers_all

11974:
11975: -- Cursor to check if it's B2B or B2C party type.
11976:
11977: cursor b2borb2c is
11978: select import_type from ams_imp_list_headers_all
11979: where import_list_header_id = p_import_list_header_id;
11980:
11981:
11982: begin

Line 13670: ,nvl(NUMBER_OF_INSTANCES,1) from ams_imp_list_headers_all

13666: L_MESG_TEXT varchar2(2000);
13667:
13668: cursor c_import_type is
13669: select import_type, dedupe_flag, nvl(VALIDATE_FILE,'N'),status_code,EXECUTE_MODE,nvl(RECORD_UPDATE_FLAG,'N')
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

Line 13680: from ams_imp_list_headers_all

13676: and duplicate_flag = 'Y';
13677:
13678: cursor c_event_status is
13679: select nvl(PROCESSED_ROWS,0), nvl(NUMBER_OF_FAILED_RECORDS,0)
13680: from ams_imp_list_headers_all
13681: where import_list_header_id = p_import_list_header_id;
13682:
13683: cursor c_conc_name is
13684: SELECT concurrent_program FROM ams_imp_list_import_types

Line 13731: ams_imp_list_headers_all hd

13727:
13728: -- For Mapped osr
13729: cursor osr_mapped is
13730: SELECT 'Y' FROM ams_list_src_fields sf,
13731: ams_imp_list_headers_all hd
13732: WHERE sf.field_column_name = 'ORIG_SYSTEM_REFERENCE'
13733: AND sf.enabled_flag = 'Y'
13734: AND sf.list_source_type_id = hd.list_source_type_id
13735: and hd.import_type in ('B2B','B2C')

Line 13783: UPDATE ams_imp_list_headers_all

13779: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
13780: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
13781: system_status_code = 'ERROR' and default_flag = 'Y';
13782:
13783: UPDATE ams_imp_list_headers_all
13784: set status_code = l_lookup_code,
13785: user_status_id = l_user_status_id,
13786: status_date = sysdate
13787: where import_list_header_id = p_import_list_header_id;

Line 13808: UPDATE ams_imp_list_headers_all

13804: system_status_code = 'STAGED' and default_flag = 'Y';
13805:
13806: select as_import_interface_s.nextval into l_batch_id from dual;
13807:
13808: UPDATE ams_imp_list_headers_all
13809: set status_code = l_lookup_code,
13810: user_status_id = l_user_status_id,
13811: batch_id = l_batch_id,
13812: status_date = sysdate

Line 13884: UPDATE ams_imp_list_headers_all

13880: null,
13881: substr(x_mdata,1,2000));
13882: end if;
13883: end if;
13884: UPDATE ams_imp_list_headers_all
13885: set status_code = 'SCHEDULED',
13886: loaded_date = sysdate,
13887: status_date = sysdate
13888: where import_list_header_id = p_import_list_header_id;

Line 13915: update ams_imp_list_headers_all

13911:
13912: open c_dup_recs;
13913: fetch c_dup_recs into l_duplicate_records;
13914: close c_dup_recs;
13915: update ams_imp_list_headers_all
13916: set number_of_duplicate_records = l_duplicate_records
13917: where import_list_header_id = p_import_list_header_id;
13918:
13919: AMS_Utility_PVT.Create_Log (

Line 14043: UPDATE ams_imp_list_headers_all

14039: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14040: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14041: system_status_code = 'ERROR' and default_flag = 'Y';
14042:
14043: UPDATE ams_imp_list_headers_all
14044: set status_code = l_lookup_code,
14045: user_status_id = l_user_status_id,
14046: status_date = sysdate
14047: where import_list_header_id = p_import_list_header_id;

Line 14097: UPDATE ams_imp_list_headers_all

14093: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14094: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14095: system_status_code = 'ERROR' and default_flag = 'Y';
14096:
14097: UPDATE ams_imp_list_headers_all
14098: set status_code = l_lookup_code,
14099: user_status_id = l_user_status_id,
14100: status_date = sysdate
14101: where import_list_header_id = p_import_list_header_id;

Line 14133: UPDATE ams_imp_list_headers_all

14129: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14130: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14131: system_status_code = 'ERROR' and default_flag = 'Y';
14132:
14133: UPDATE ams_imp_list_headers_all
14134: set status_code = l_lookup_code,
14135: user_status_id = l_user_status_id,
14136: status_date = sysdate
14137: where import_list_header_id = p_import_list_header_id;

Line 14168: UPDATE ams_imp_list_headers_all

14164: l_user_status_id := null;
14165: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14166: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14167: system_status_code = 'ERROR' and default_flag = 'Y';
14168: UPDATE ams_imp_list_headers_all
14169: set status_code = l_lookup_code,
14170: user_status_id = l_user_status_id,
14171: status_date = sysdate
14172: where import_list_header_id = p_import_list_header_id;

Line 14225: UPDATE ams_imp_list_headers_all

14221: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14222: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14223: system_status_code = 'ERROR' and default_flag = 'Y';
14224:
14225: UPDATE ams_imp_list_headers_all
14226: set status_code = l_lookup_code,
14227: user_status_id = l_user_status_id,
14228: status_date = sysdate
14229: where import_list_header_id = p_import_list_header_id;

Line 14261: UPDATE ams_imp_list_headers_all

14257: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14258: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14259: system_status_code = 'ERROR' and default_flag = 'Y';
14260:
14261: UPDATE ams_imp_list_headers_all
14262: set status_code = l_lookup_code,
14263: user_status_id = l_user_status_id,
14264: status_date = sysdate
14265: where import_list_header_id = p_import_list_header_id;

Line 14312: UPDATE ams_imp_list_headers_all

14308: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14309: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14310: system_status_code = 'ERROR' and default_flag = 'Y';
14311:
14312: UPDATE ams_imp_list_headers_all
14313: set status_code = l_lookup_code,
14314: user_status_id = l_user_status_id,
14315: status_date = sysdate
14316: where import_list_header_id = p_import_list_header_id;

Line 14337: UPDATE ams_imp_list_headers_all

14333: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14334: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14335: system_status_code = 'ERROR' and default_flag = 'Y';
14336:
14337: UPDATE ams_imp_list_headers_all
14338: set status_code = l_lookup_code,
14339: user_status_id = l_user_status_id,
14340: status_date = sysdate
14341: where import_list_header_id = p_import_list_header_id;

Line 14410: UPDATE ams_imp_list_headers_all

14406: l_user_status_id := null;
14407: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14408: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14409: system_status_code = 'ERROR' and default_flag = 'Y';
14410: UPDATE ams_imp_list_headers_all
14411: set status_code = l_lookup_code,
14412: user_status_id = l_user_status_id,
14413: status_date = sysdate
14414: where import_list_header_id = p_import_list_header_id;

Line 14444: UPDATE ams_imp_list_headers_all

14440: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14441: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14442: system_status_code = 'ERROR' and default_flag = 'Y';
14443:
14444: UPDATE ams_imp_list_headers_all
14445: set status_code = l_lookup_code,
14446: user_status_id = l_user_status_id,
14447: status_date = sysdate
14448: where import_list_header_id = p_import_list_header_id;

Line 14463: UPDATE ams_imp_list_headers_all

14459: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14460: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14461: system_status_code = 'ERROR' and default_flag = 'Y';
14462:
14463: UPDATE ams_imp_list_headers_all
14464: set status_code = l_lookup_code,
14465: user_status_id = l_user_status_id,
14466: status_date = sysdate
14467: where import_list_header_id = p_import_list_header_id;

Line 14476: UPDATE ams_imp_list_headers_all

14472: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14473: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14474: system_status_code = 'COMPLETED' and default_flag = 'Y';
14475:
14476: UPDATE ams_imp_list_headers_all
14477: set status_code = l_lookup_code,
14478: user_status_id = l_user_status_id,
14479: status_date = sysdate
14480: where import_list_header_id = p_import_list_header_id;

Line 14494: UPDATE ams_imp_list_headers_all

14490: close c_loaded_records;
14491: open c_failed_records;
14492: fetch c_failed_records into l_failed_records;
14493: close c_failed_records;
14494: UPDATE ams_imp_list_headers_all
14495: SET loaded_no_of_rows = l_loaded_records,
14496: number_of_failed_records = l_failed_records,
14497: loaded_date = sysdate
14498: WHERE import_list_header_id = p_import_list_header_id;

Line 14512: select LOADED_NO_OF_ROWS into l_loaded_rows from ams_imp_list_headers_all

14508: p_msg_type => 'DEBUG'
14509: );
14510:
14511: l_loaded_rows := 0;
14512: select LOADED_NO_OF_ROWS into l_loaded_rows from ams_imp_list_headers_all
14513: where import_list_header_id = p_import_list_header_id;
14514:
14515: if l_loaded_rows > 0 then
14516:

Line 14556: UPDATE ams_imp_list_headers_all

14552: --
14553:
14554: if l_import_type = 'LEAD' then
14555: if l_status_code = 'STAGED' then
14556: UPDATE ams_imp_list_headers_all
14557: set status_code = 'SCHEDULED',
14558: loaded_date = sysdate,
14559: status_date = sysdate
14560: where import_list_header_id = p_import_list_header_id;

Line 14589: UPDATE ams_imp_list_headers_all

14585: --
14586:
14587: if l_import_type = 'EVENT' then
14588: if l_status_code = 'STAGED' then
14589: UPDATE ams_imp_list_headers_all
14590: set status_code = 'SCHEDULED',
14591: loaded_date = sysdate,
14592: status_date = sysdate
14593: where import_list_header_id = p_import_list_header_id;

Line 14629: UPDATE ams_imp_list_headers_all

14625: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14626: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14627: system_status_code = 'ERROR' and default_flag = 'Y';
14628:
14629: UPDATE ams_imp_list_headers_all
14630: set status_code = l_lookup_code,
14631: user_status_id = l_user_status_id,
14632: status_date = sysdate
14633: where import_list_header_id = p_import_list_header_id;

Line 14676: UPDATE ams_imp_list_headers_all

14672: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14673: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14674: system_status_code = 'ERROR' and default_flag = 'Y';
14675:
14676: UPDATE ams_imp_list_headers_all
14677: set status_code = l_lookup_code,
14678: user_status_id = l_user_status_id,
14679: status_date = sysdate
14680: where import_list_header_id = p_import_list_header_id;

Line 14689: UPDATE ams_imp_list_headers_all

14685: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
14686: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
14687: system_status_code = 'COMPLETED' and default_flag = 'Y';
14688:
14689: UPDATE ams_imp_list_headers_all
14690: set status_code = l_lookup_code,
14691: user_status_id = l_user_status_id,
14692: status_date = sysdate
14693: where import_list_header_id = p_import_list_header_id;

Line 14707: UPDATE ams_imp_list_headers_all

14703: open c_failed_records;
14704: fetch c_failed_records into l_failed_records;
14705: close c_failed_records;
14706:
14707: UPDATE ams_imp_list_headers_all
14708: SET loaded_no_of_rows = l_loaded_records,
14709: number_of_failed_records = l_failed_records,
14710: loaded_date = sysdate
14711: where import_list_header_id = p_import_list_header_id;

Line 15365: FROM ams_imp_list_headers_all

15361:
15362: CURSOR c_lead_batch_id is
15363: SELECT
15364: batch_id
15365: FROM ams_imp_list_headers_all
15366: WHERE import_list_header_id = p_import_list_header_id;
15367:
15368: begin
15369:

Line 15983: UPDATE ams_imp_list_headers_all

15979: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
15980: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
15981: system_status_code = l_status_code and default_flag = 'Y';
15982:
15983: UPDATE ams_imp_list_headers_all
15984: set status_code = l_status_code,
15985: user_status_id = l_user_status_id,
15986: loaded_no_of_rows = l_tot_numb_success_records,
15987: number_of_duplicate_records = l_tot_numb_dup_records,

Line 16037: select decode(import_type,'B2B','Y','N') from ams_imp_list_headers_all

16033:
16034: l_b2b_flag VARCHAR2(1);
16035:
16036: cursor c_b2b is
16037: select decode(import_type,'B2B','Y','N') from ams_imp_list_headers_all
16038: WHERE import_list_header_id = p_import_list_header_id;
16039:
16040: begin
16041:

Line 17609: cursor c_batchID is select batch_id from ams_imp_list_headers_all

17605: x_return_status OUT NOCOPY varchar2,
17606: x_msg_data OUT NOCOPY varchar2) is
17607:
17608: l_batch_id number;
17609: cursor c_batchID is select batch_id from ams_imp_list_headers_all
17610: where import_list_header_id = p_import_list_header_id;
17611:
17612: begin
17613: open c_batchID;

Line 18047: cursor c_batchID is select batch_id from ams_imp_list_headers_all

18043: x_return_status OUT NOCOPY varchar2,
18044: x_msg_data OUT NOCOPY varchar2) IS
18045:
18046: l_batch_id number;
18047: cursor c_batchID is select batch_id from ams_imp_list_headers_all
18048: where import_list_header_id = p_import_list_header_id;
18049:
18050: begin
18051: open c_batchID;

Line 20889: from ams_imp_list_headers_all

20885:
20886: cursor c_import_type is
20887: select
20888: GENERATE_LIST,GENERATED_LIST_NAME,OWNER_USER_ID,dedupe_flag
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

Line 20956: UPDATE ams_imp_list_headers_all

20952: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
20953: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
20954: system_status_code = 'ERROR' and default_flag = 'Y';
20955:
20956: UPDATE ams_imp_list_headers_all
20957: set status_code = l_lookup_code,
20958: user_status_id = l_user_status_id,
20959: status_date = sysdate
20960: where import_list_header_id = p_import_list_header_id;

Line 20969: UPDATE ams_imp_list_headers_all

20965: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
20966: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
20967: system_status_code = 'COMPLETED' and default_flag = 'Y';
20968:
20969: UPDATE ams_imp_list_headers_all
20970: set status_code = l_lookup_code,
20971: user_status_id = l_user_status_id,
20972: status_date = sysdate
20973: where import_list_header_id = p_import_list_header_id;

Line 20990: UPDATE ams_imp_list_headers_all

20986: close c_failed_records;
20987: open c_dup_records;
20988: fetch c_dup_records into L_TOTAL_DUP_RECORDS;
20989: close c_dup_records;
20990: UPDATE ams_imp_list_headers_all
20991: SET loaded_no_of_rows = l_loaded_records,
20992: number_of_failed_records = l_failed_records,
20993: number_of_duplicate_records = L_TOTAL_DUP_RECORDS,
20994: loaded_date = sysdate

Line 21023: select LOADED_NO_OF_ROWS into l_loaded_rows from ams_imp_list_headers_all

21019: p_msg_data => 'Starting List Generation for List Import.',
21020: p_msg_type => 'DEBUG'
21021: );
21022: l_loaded_rows := 0;
21023: select LOADED_NO_OF_ROWS into l_loaded_rows from ams_imp_list_headers_all
21024: where import_list_header_id = p_import_list_header_id;
21025:
21026: if l_loaded_rows > 0 then
21027: AMS_ListGeneration_PKG.create_import_list

Line 21182: select import_type from ams_imp_list_headers_all

21178: L_IMPORT_SOURCE_LINE_ID IMPORT_SOURCE_LINE_ID_LIST;
21179: L_TRANSPOSED_PHONE_NUMBER TRANSPOSED_PH_NUMBER_LIST;
21180:
21181: cursor c_imp_type is
21182: select import_type from ams_imp_list_headers_all
21183: where import_list_header_id = p_import_list_header_id;
21184:
21185: cursor c_b2b_ph_no is
21186: select IMPORT_SOURCE_LINE_ID ,

Line 21383: select import_type,last_updated_by,vendor_id,charset from ams_imp_list_headers_all

21379: l_error_text VARCHAR2(4000);
21380: l_user_status_id NUMBER;
21381:
21382: Cursor c_header_info is
21383: select import_type,last_updated_by,vendor_id,charset from ams_imp_list_headers_all
21384: where import_list_header_id = p_import_list_header_id;
21385:
21386: Begin
21387: AMS_Utility_PVT.Create_Log (

Line 21458: UPDATE ams_imp_list_headers_all

21454: l_user_status_id := null;
21455: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
21456: WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
21457: system_status_code = 'ERROR' and default_flag = 'Y';
21458: UPDATE ams_imp_list_headers_all
21459: set status_code = 'ERROR' ,
21460: user_status_id = l_user_status_id,
21461: status_date = sysdate
21462: where import_list_header_id = p_import_list_header_id;