[Home] [Help]
13: l_imp_type_code VARCHAR2(30) := NULL ;
14: BEGIN
15:
16: SELECT import_type_code INTO l_imp_type_code
17: FROM cn_imp_headers
18: WHERE imp_header_id = p_imp_header_id;
19:
20: RETURN l_imp_type_code;
21: EXCEPTION
83: l_api_version CONSTANT NUMBER := 1.0;
84:
85: l_request_id NUMBER;
86: l_imp_type_code VARCHAR2(30);
87: l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
88: l_process_audit_id cn_process_audits.process_audit_id%TYPE;
89:
90: BEGIN
91: -- Standard Start of API savepoint
106: l_imp_type_code := get_imp_type_code(p_imp_header_id);
107: SELECT name, status_code,server_flag
108: INTO l_imp_header.name ,l_imp_header.status_code ,
109: l_imp_header.server_flag
110: FROM cn_imp_headers
111: WHERE imp_header_id = p_imp_header_id;
112:
113: -- open process audit batch
114: cn_message_pkg.begin_batch
317: l_imp_type_code := get_imp_type_code(p_imp_header_id);
318:
319: SELECT name, status_code
320: INTO l_name ,l_status_code
321: FROM cn_imp_headers
322: WHERE imp_header_id = p_imp_header_id;
323:
324: SELECT export_conc_program
325: INTO l_conc_pgm
465: l_dev_phase VARCHAR2(30);
466: l_dev_status VARCHAR2(30);
467: l_message VARCHAR2(240);
468: l_loaded_rows NUMBER;
469: l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
470: l_imp_type_code VARCHAR2(30);
471: l_msg_count NUMBER := 0;
472: l_process_audit_id cn_process_audits.process_audit_id%TYPE;
473: l_api_name CONSTANT VARCHAR2(30) := 'Server_Stage_data';
500:
501: SELECT name, status_code,server_flag
502: INTO l_imp_header.name ,l_imp_header.status_code ,
503: l_imp_header.server_flag
504: FROM cn_imp_headers
505: WHERE imp_header_id = p_imp_header_id;
506:
507: -- open process audit batch
508: cn_message_pkg.begin_batch
614: END IF;
615:
616: cn_message_pkg.debug('Staging Data : SQL*Loader Finished.');
617:
618: -- UPDATE cn_imp_headers
619: OPEN c_loaded_rows;
620: FETCH c_loaded_rows INTO l_loaded_rows;
621: CLOSE c_loaded_rows;
622:
661:
662: -- Get imp_header status
663: SELECT name, status_code,server_flag
664: INTO l_imp_header.name ,l_imp_header.status_code ,l_imp_header.server_flag
665: FROM cn_imp_headers
666: WHERE imp_header_id = p_imp_header_id;
667:
668: -- Start Loading Data
669: IF l_imp_header.status_code = 'STAGE' THEN
739: l_dev_status VARCHAR2(30);
740: l_message VARCHAR2(240);
741: l_loaded_rows NUMBER;
742:
743: l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
744: l_process_audit_id cn_process_audits.process_audit_id%TYPE;
745: l_imp_type_code VARCHAR2(30);
746: l_conc_program VARCHAR2(30);
747:
769:
770: SELECT name, status_code,server_flag
771: INTO l_imp_header.name ,l_imp_header.status_code,
772: l_imp_header.server_flag
773: FROM cn_imp_headers
774: WHERE imp_header_id = p_imp_header_id;
775:
776: -- open process audit batch
777: cn_message_pkg.begin_batch
950: l_api_name CONSTANT VARCHAR2(30) := 'Client_Stage_Data';
951: l_api_version CONSTANT NUMBER := 1.0;
952:
953: l_imp_type_code VARCHAR2(30);
954: l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
955: l_process_audit_id cn_process_audits.process_audit_id%TYPE;
956:
957: CURSOR c_tar_col_cv(c_imp_map_id NUMBER) IS
958: SELECT f.source_column,f.target_column_name,f.target_table_name,
1001: SELECT name, status_code,server_flag,imp_map_id, source_column_num
1002: INTO l_imp_header.name ,l_imp_header.status_code ,
1003: l_imp_header.server_flag, l_imp_header.imp_map_id,
1004: l_imp_header.source_column_num
1005: FROM cn_imp_headers
1006: WHERE imp_header_id = p_imp_header_id;
1007:
1008: -- Check if Client_Stage_Data has been process multiple time, means import
1009: -- data has been divide into multiple chunks and each chunk will call
1078:
1079: IF x_return_status <> FND_API.g_ret_sts_success THEN
1080: RAISE FND_API.G_EXC_ERROR;
1081: END IF;
1082: -- UPDATE cn_imp_headers
1083: OPEN c_loaded_rows;
1084: FETCH c_loaded_rows INTO l_loaded_rows;
1085: CLOSE c_loaded_rows;
1086: -- data may cut into 3600 element chuncks, l_row_count is not necessary
1194:
1195: -- --------------------------------------------------------+
1196: -- update_imp_headers
1197: --
1198: -- This procedure will update cn_imp_headers status,processed_row
1199: -- and failed_row
1200: -- --------------------------------------------------------+
1201: PROCEDURE update_imp_headers
1202: (p_imp_header_id IN NUMBER,
1206: p_failed_row IN NUMBER := NULL) IS
1207: PRAGMA AUTONOMOUS_TRANSACTION;
1208: BEGIN
1209:
1210: UPDATE cn_imp_headers
1211: SET status_code = Decode(p_status_code,NULL,status_code,p_status_code),
1212: staged_row =Decode(p_staged_row,NULL,staged_row,p_staged_row),
1213: processed_row =Decode(p_processed_row,NULL,processed_row,p_processed_row),
1214: failed_row = Decode(p_failed_row,NULL,failed_row,p_failed_row)
1235: IS
1236: CURSOR c_src_col_csr IS
1237: SELECT f.source_column,f.source_user_column,
1238: f.target_column_name,f.target_table_name
1239: FROM cn_imp_headers h, cn_imp_map_fields f
1240: WHERE h.imp_header_id = p_imp_header_id
1241: AND f.imp_map_id = h.imp_map_id
1242: ORDER BY f.source_column
1243: ;