[Home] [Help]
17: G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvalsb.pls';
18: g_list_header_id number;
19: g_count NUMBER := 1;
20: g_remote_list VARCHAR2(1) := 'N';
21: g_message_table AMS_LISTGENERATION_PKG.sql_string;
22: g_message_table_null AMS_LISTGENERATION_PKG.sql_string;
23: AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24: AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25: AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
18: g_list_header_id number;
19: g_count NUMBER := 1;
20: g_remote_list VARCHAR2(1) := 'N';
21: g_message_table AMS_LISTGENERATION_PKG.sql_string;
22: g_message_table_null AMS_LISTGENERATION_PKG.sql_string;
23: AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24: AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25: AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26:
109: l_return_status VARCHAR2(1);
110:
111: l_count NUMBER;
112: l_string_copy VARCHAR2(32767);
113: l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
114: l_length NUMBER;
115:
116:
117: CURSOR c_get_cell_name IS
183: IF (AMS_DEBUG_HIGH_ON) THEN
184: AMS_Utility_PVT.debug_message('validate string:' || length(l_sql_string));
185: END IF;
186: l_found := 'N';
187: AMS_ListGeneration_PKG.validate_sql_string(
188: p_sql_string => l_sql_string_tbl ,
189: p_search_string => 'FROM',
190: p_comma_valid => 'N',
191: x_found => l_found,
202: END IF;
203:
204: l_found := 'N';
205:
206: AMS_ListGeneration_PKG.get_master_types (
207: p_sql_string => l_sql_string_tbl,
208: p_start_length => 1,
209: p_start_counter => 1,
210: p_end_length => l_from_position,
317: l_return_status VARCHAR2(1);
318:
319: BEGIN
320:
321: if nvl(ams_listgeneration_pkg.g_log_level,'HIGH') = 'HIGH' and p_level = 'LOW' then
322: return;
323: end if;
324:
325: ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
321: if nvl(ams_listgeneration_pkg.g_log_level,'HIGH') = 'HIGH' and p_level = 'LOW' then
322: return;
323: end if;
324:
325: ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
326:
327: /* ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
328: ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
329: ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
323: end if;
324:
325: ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
326:
327: /* ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
328: ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
329: ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
330: */
331:
324:
325: ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
326:
327: /* ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
328: ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
329: ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
330: */
331:
332: -- g_message_table(g_count) := p_msg_data;
325: ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
326:
327: /* ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
328: ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
329: ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
330: */
331:
332: -- g_message_table(g_count) := p_msg_data;
333: -- g_count := g_count + 1;
1954: -- Debug Message
1955: /*
1956: IF (AMS_DEBUG_HIGH_ON) THEN
1957: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
1958: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.cerate_list: Start', TRUE);
1959: FND_MSG_PUB.Add;
1960: END IF;
1961: */
1962: -- Initialize API return status to success
2178: LOOP
2179: fetch c_get_list into l_list_header_id ;
2180: exit when c_get_list%notfound;
2181: if p_supp_type = 'PARTY_ID' then
2182: if ams_listgeneration_pkg.g_remote_list_gen = 'N' then
2183: UPDATE ams_list_entries a
2184: SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
2185: WHERE a.list_header_id = p_list_header_id
2186: AND a.enabled_flag = 'Y'
2195: write_to_act_log(p_msg_data => 'Calling remote api for doing party id based suppression for suppression list'||l_list_header_id,
2196: p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2197: execute immediate
2198: 'begin
2199: ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2200: ' end;'
2201: using p_sql_string,
2202: p_list_header_id,
2203: l_list_header_id,
2206: out x_msg_data,
2207: out x_return_status;
2208: end if;
2209: elsif p_supp_type = 'DEDUPE' then
2210: if nvl(ams_listgeneration_pkg.g_remote_list_gen,'N') = 'N' then
2211: EXECUTE IMMEDIATE p_sql_string using l_list_header_id;
2212: UPDATE ams_list_entries a SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
2213: WHERE a.list_header_id = p_list_header_id
2214: AND a.enabled_flag = 'Y'
2223: write_to_act_log(p_msg_data => 'Calling remote api for doing dedupe rule based suppression for suppression list'||l_list_header_id,
2224: p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2225: execute immediate
2226: 'begin
2227: ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2228: ' end;'
2229: using p_sql_string,
2230: p_list_header_id,
2231: l_list_header_id,
2375:
2376: l_string := l_string||'' ;
2377: l_string := l_string||' where enabled_flag = '||''''||'Y'||''''||' and list_header_id = :b1 ';
2378: write_to_act_log(p_msg_data => 'SQL string with DD keys from the selected DD Rule = '||l_string,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2379: if ams_listgeneration_pkg.g_remote_list_gen = 'N' then
2380: execute immediate l_string using p_list_header_id;
2381: elsif ams_listgeneration_pkg.g_remote_list_gen = 'Y' then
2382: execute immediate
2383: 'begin
2377: l_string := l_string||' where enabled_flag = '||''''||'Y'||''''||' and list_header_id = :b1 ';
2378: write_to_act_log(p_msg_data => 'SQL string with DD keys from the selected DD Rule = '||l_string,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2379: if ams_listgeneration_pkg.g_remote_list_gen = 'N' then
2380: execute immediate l_string using p_list_header_id;
2381: elsif ams_listgeneration_pkg.g_remote_list_gen = 'Y' then
2382: execute immediate
2383: 'begin
2384: ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2385: ' end;'
2380: execute immediate l_string using p_list_header_id;
2381: elsif ams_listgeneration_pkg.g_remote_list_gen = 'Y' then
2382: execute immediate
2383: 'begin
2384: ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2385: ' end;'
2386: using l_string,
2387: p_list_header_id,
2388: p_list_header_id,
2391: out x_msg_data,
2392: out x_return_status;
2393: /*execute immediate
2394: 'begin
2395: ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2396: ' end;'
2397: using l_string,
2398: p_list_header_id,
2399: null,
2652: l_generate_control_group_flag ,
2653: l_generation_type ;
2654: close c1;
2655: g_list_header_id := l_list_header_id ;
2656: ams_listgeneration_pkg.find_log_level(l_list_header_id);
2657:
2658: write_to_act_log(p_msg_data => 'Executing generate_target_group_list_old procedure for kicking off target group generation',
2659: p_arc_log_used_by => 'LIST',
2660: p_log_used_by_id => l_list_header_id,
2660: p_log_used_by_id => l_list_header_id,
2661: p_level => 'HIGH');
2662:
2663: --Added by bmuthukr for bug 3710720
2664: ams_listgeneration_pkg.is_manual(p_list_header_id => l_list_header_id,
2665: x_return_status => x_return_status,
2666: x_msg_count => x_msg_count,
2667: x_msg_data => x_msg_data,
2668: x_is_manual => l_is_manual);
2668: x_is_manual => l_is_manual);
2669: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
2670: write_to_act_log('Error in executing is_manual procedure', 'LIST', g_list_header_id,'HIGH');
2671: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
2672: ams_listgeneration_pkg.logger;
2673: return;
2674: end if;
2675:
2676: if nvl(l_is_manual,'N') = 'Y' then
2674: end if;
2675:
2676: if nvl(l_is_manual,'N') = 'Y' then
2677: write_to_act_log('Either list is a manual list, or incl are based on EMP list. Cannot generate','LIST',l_list_header_id,'HIGH');
2678: ams_listgeneration_pkg.logger;
2679: return;
2680: end if;
2681: --Ends changes.
2682:
2746: last_update_date = sysdate,
2747: main_gen_end_time = sysdate
2748: WHERE list_header_id = l_list_header_id;
2749: -- calling logging program
2750: ams_listgeneration_pkg.logger;
2751: --
2752: RETURN;
2753: end if;
2754: end if ;
2753: end if;
2754: end if ;
2755:
2756: /* End Bug fix: 3799192 */
2757: write_to_act_log(p_msg_data => 'Calling ams_listgeneration_pkg.generate_target_group procedure to generate target group',
2758: p_arc_log_used_by => 'TARGET',
2759: p_log_used_by_id => l_list_header_id,
2760: p_level => 'LOW');
2761:
2759: p_log_used_by_id => l_list_header_id,
2760: p_level => 'LOW');
2761:
2762:
2763: AMS_LISTGENERATION_PKG.GENERATE_TARGET_GROUP
2764: ( p_api_version => p_api_version,
2765: p_init_msg_list => FND_API.G_TRUE,
2766: p_commit => FND_API.G_FALSE,
2767: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2782: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2783: RAISE FND_API.g_exc_unexpected_error;
2784: END IF;
2785:
2786: write_to_act_log(p_msg_data => 'Procedure AMS_LISTGENERATION_PKG.GENERATE_TARGET_GROUP executed successfully.' ,
2787: p_arc_log_used_by => 'LIST',
2788: p_log_used_by_id => g_list_header_id,
2789: p_level => 'HIGH');
2790:
2901: arc_list_used_by_source = p_list_used_by ,
2902: source_code_for_id = p_list_used_by_id
2903: where list_header_id = l_list_header_id ;
2904:
2905: AMS_LISTGENERATION_PKG.Update_List_Dets(l_list_header_id ,x_return_status ) ;
2906: -- --------------------------------------------------------
2907: -- Business Event for Traffic Cop
2908: -----------------------------------------------------------
2909: open c_tg_status;
2940: end if;
2941: -- --------------------------------------------------------
2942: IF (AMS_DEBUG_HIGH_ON) THEN
2943: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2944: FND_MESSAGE.Set_Token('TEXT', 'AMS_LISTGENERATION_PKG.cerate_list: Start', TRUE);
2945: FND_MSG_PUB.Add;
2946: END IF;
2947: write_to_act_log(p_msg_data => 'Target Group available ' ,
2948: p_arc_log_used_by => 'LIST',
2947: write_to_act_log(p_msg_data => 'Target Group available ' ,
2948: p_arc_log_used_by => 'LIST',
2949: p_log_used_by_id => g_list_header_id,
2950: p_level=>'HIGH');
2951: ams_listgeneration_pkg.logger;
2952: -- Initialize API return status to success
2953: x_return_status := FND_API.G_RET_STS_SUCCESS;
2954: -- Perform the database operation
2955:
3192: END;
3193:
3194:
3195: PROCEDURE validate_sql_string
3196: (p_sql_string in AMS_LISTGENERATION_PKG.sql_string
3197: ,p_search_string in varchar2
3198: ,p_comma_valid in varchar2
3199: ,x_found OUT NOCOPY varchar2
3200: ,x_position OUT NOCOPY number
3210: /* Searching of the string is done by concatenating the two strings of
3211: 2000 each gjoby more expln needed
3212: */
3213: x_found := 'N';
3214: --l_position := 'AMS_ListGeneration_PKG.Validate_sql_string start';
3215: l_sql_string_1 := lpad(l_sql_string_1,2000,' ');
3216:
3217: for i in 1 .. p_sql_string.last
3218: loop
3264: exit;
3265: end if;
3266: l_sql_string_1 := l_sql_string_2;
3267: end loop;
3268: --l_position := 'AMS_ListGeneration_PKG.Validate_sql_string end';
3269: exception
3270: when others then
3271: write_to_act_log('AMS_ListGeneration_PKG.Error' || sqlerrm,null,null );
3272: END;
3267: end loop;
3268: --l_position := 'AMS_ListGeneration_PKG.Validate_sql_string end';
3269: exception
3270: when others then
3271: write_to_act_log('AMS_ListGeneration_PKG.Error' || sqlerrm,null,null );
3272: END;
3273:
3274: PROCEDURE get_condition(p_sql_string in AMS_LISTGENERATION_PKG.sql_string ,
3275: p_search_string in varchar2,
3270: when others then
3271: write_to_act_log('AMS_ListGeneration_PKG.Error' || sqlerrm,null,null );
3272: END;
3273:
3274: PROCEDURE get_condition(p_sql_string in AMS_LISTGENERATION_PKG.sql_string ,
3275: p_search_string in varchar2,
3276: p_comma_valid in varchar2,
3277: x_position OUT NOCOPY number,
3278: x_counter OUT NOCOPY number,
3276: p_comma_valid in varchar2,
3277: x_position OUT NOCOPY number,
3278: x_counter OUT NOCOPY number,
3279: x_found OUT NOCOPY varchar2,
3280: x_sql_string OUT NOCOPY AMS_LISTGENERATION_PKG.sql_string) is
3281: l_where_position number;
3282: l_where_counter number;
3283: l_counter number := 0;
3284: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3280: x_sql_string OUT NOCOPY AMS_LISTGENERATION_PKG.sql_string) is
3281: l_where_position number;
3282: l_where_counter number;
3283: l_counter number := 0;
3284: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3285: begin
3286: validate_sql_string(p_sql_string => p_sql_string ,
3287: p_search_string => p_search_string,
3288: p_comma_valid => 'N',
3305:
3306: PROCEDURE form_sql_statement(p_select_statement in varchar2,
3307: p_select_add_statement in varchar2,
3308: p_master_type in varchar2,
3309: p_child_types in AMS_LISTGENERATION_PKG.child_type,
3310: p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3311: p_act_list_header_id in number,
3312: p_action_used_by_id in number,
3313: x_final_string OUT NOCOPY varchar2
3306: PROCEDURE form_sql_statement(p_select_statement in varchar2,
3307: p_select_add_statement in varchar2,
3308: p_master_type in varchar2,
3309: p_child_types in AMS_LISTGENERATION_PKG.child_type,
3310: p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3311: p_act_list_header_id in number,
3312: p_action_used_by_id in number,
3313: x_final_string OUT NOCOPY varchar2
3314: ) is
3502: PROCEDURE process_insert_sql
3503: (p_select_statement in varchar2,
3504: p_select_add_statement in varchar2,
3505: p_master_type in varchar2,
3506: p_child_types in AMS_LISTGENERATION_PKG.child_type,
3507: p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3508: p_act_list_header_id in number,
3509: p_action_used_by_id in number,
3510: x_std_sql OUT NOCOPY varchar2 ,
3503: (p_select_statement in varchar2,
3504: p_select_add_statement in varchar2,
3505: p_master_type in varchar2,
3506: p_child_types in AMS_LISTGENERATION_PKG.child_type,
3507: p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3508: p_act_list_header_id in number,
3509: p_action_used_by_id in number,
3510: x_std_sql OUT NOCOPY varchar2 ,
3511: x_include_sql OUT NOCOPY varchar2
3546: PROCEDURE process_all_sql (
3547: p_action_used_by_id in number,
3548: p_act_list_header_id in number,
3549: p_incl_object_id in number,
3550: p_sql_string in AMS_LISTGENERATION_PKG.sql_string,
3551: p_primary_key in varchar2,
3552: p_source_object_name in varchar2,
3553: x_msg_count OUT NOCOPY number,
3554: x_msg_data OUT NOCOPY varchar2,
3555: x_return_status IN OUT NOCOPY VARCHAR2,
3556: x_std_sql OUT NOCOPY varchar2 ,
3557: x_include_sql OUT NOCOPY varchar2
3558: ) is
3559: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3560: --l_sql_string sql_string;
3561: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3562: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3563: l_counter NUMBER := 1;
3557: x_include_sql OUT NOCOPY varchar2
3558: ) is
3559: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3560: --l_sql_string sql_string;
3561: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3562: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3563: l_counter NUMBER := 1;
3564: l_from_position number;
3565: l_from_counter number;
3558: ) is
3559: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3560: --l_sql_string sql_string;
3561: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3562: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3563: l_counter NUMBER := 1;
3564: l_from_position number;
3565: l_from_counter number;
3566: l_end_position number;
3573: l_master_type varchar2(80);
3574: l_master_type_id number;
3575: l_source_object_name varchar2(80);
3576: l_source_object_pk_field varchar2(80);
3577: l_child_types AMS_LISTGENERATION_PKG.child_type;
3578: l_select_condition varchar2(2000);
3579: l_select_add_condition varchar2(2000);
3580: l_sql_string_v2 varchar2(4000);
3581: l_no_of_chunks number;
3603: write_to_act_log('Process_all_sql: FROM Position ->'|| l_from_position ||
3604: '<--FROM Counter ->' || l_from_counter ||
3605: '<--FROM Found ->' || l_found,null,null);
3606: l_found := 'N';
3607: AMS_LISTGENERATION_PKG.get_master_types (p_sql_string => l_sql_string,
3608: p_start_length => 1,
3609: p_start_counter => 1,
3610: p_end_length => l_from_position,
3611: p_end_counter => l_from_counter,
3622: --write_to_act_log('Process_all_sql: Master Type->'|| l_master_type ||'<--' );
3623:
3624:
3625: l_found := 'N';
3626: AMS_LISTGENERATION_PKG.get_child_types (p_sql_string => l_sql_string,
3627: p_start_length => 1,
3628: p_start_counter => 1,
3629: p_end_length => l_from_position,
3630: p_end_counter => l_from_counter,
3685: write_to_act_log('Process_all_sql: ***********end insert sql->' ,null,null);
3686: --write_to_act_log('Proc_all__sql -> end' ||x_return_status);
3687: EXCEPTION
3688: WHEN FND_API.G_EXC_ERROR THEN
3689: write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql: ',null,null);
3690: x_return_status := FND_API.G_RET_STS_ERROR ;
3691: FND_MSG_PUB.count_and_get(
3692: p_encoded => FND_API.g_false,
3693: p_count => x_msg_count,
3694: p_data => x_msg_data
3695: );
3696:
3697: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3698: write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql: ',null,null);
3699: x_return_status := FND_API.G_RET_STS_ERROR ;
3700: FND_MSG_PUB.count_and_get(
3701: p_encoded => FND_API.g_false,
3702: p_count => x_msg_count,
3703: p_data => x_msg_data
3704: );
3705:
3706: WHEN OTHERS THEN
3707: write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql:' ,null,null);
3708: x_return_status := FND_API.G_RET_STS_ERROR ;
3709: FND_MSG_PUB.count_and_get(
3710: p_encoded => FND_API.g_false,
3711: p_count => x_msg_count,
3730: -- Given the sql id from ams_list_select_actions it will retrieve the
3731: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3732: -- workbook_name.
3733: ------------------------------------------------------------------------------
3734: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3735: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3736: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3737: l_counter NUMBER := 1;
3738: l_from_position number;
3731: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3732: -- workbook_name.
3733: ------------------------------------------------------------------------------
3734: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3735: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3736: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3737: l_counter NUMBER := 1;
3738: l_from_position number;
3739: l_from_counter number;
3732: -- workbook_name.
3733: ------------------------------------------------------------------------------
3734: l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3735: l_where_string AMS_LISTGENERATION_PKG.sql_string;
3736: l_from_string AMS_LISTGENERATION_PKG.sql_string;
3737: l_counter NUMBER := 1;
3738: l_from_position number;
3739: l_from_counter number;
3740: l_end_position number;
3747: l_master_type varchar2(80);
3748: l_master_type_id number;
3749: l_source_object_name varchar2(80);
3750: l_source_object_pk_field varchar2(80);
3751: l_child_types AMS_LISTGENERATION_PKG.child_type;
3752: l_select_condition varchar2(2000);
3753: l_select_add_condition varchar2(2000);
3754: l_msg_data VARCHAR2(2000);
3755: l_msg_count number;
3760: l_final_big_sql VARCHAR2(32767);
3761: l_const_sql VARCHAR2(32767);
3762: BEGIN
3763:
3764: --write_to_act_log('AMS_ListGeneration_PKG.Get Comp sql:');
3765: ams_cell_pvt.get_comp_sql(
3766: p_api_version => 1.0,
3767: p_init_msg_list => FND_API.g_false,
3768: p_validation_level => FND_API.g_valid_level_full,
3772: p_cell_id => p_incl_object_id ,
3773: p_party_id_only => FND_API.g_false,
3774: x_sql_tbl => l_sql_2
3775: );
3776: --write_to_act_log('AMS_ListGeneration_PKG.After Comp sql:');
3777:
3778: l_sql_string_final := '';
3779: for i in 1 .. l_sql_2.last
3780: loop
3791: j := j+1;
3792: l_sql_string(j) := substrb(l_sql_string_final,2001 ,2000);
3793: end if;
3794:
3795: --write_to_act_log('AMS_ListGeneration_PKG.Process all sql:');
3796: --write_to_act_log('l_sql_string'||l_sql_string.last);
3797: for i in 1 .. l_sql_string.last
3798: loop
3799: l_no_of_chunks := ceil(length(l_sql_string(i))/2000 );
3839: for j in 1 ..l_no_of_chunks
3840: loop
3841: null;
3842: -- WRITE_TO_ACT_LOG(substrb(x_include_sql,(2000*j) - 1999,2000));
3843: --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3844: end loop;
3845: --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3846: EXECUTE IMMEDIATE l_final_big_sql;
3847:
3841: null;
3842: -- WRITE_TO_ACT_LOG(substrb(x_include_sql,(2000*j) - 1999,2000));
3843: --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3844: end loop;
3845: --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3846: EXECUTE IMMEDIATE l_final_big_sql;
3847:
3848: END process_cell ;
3849: --