[Home] [Help]
2165: --the status in AVAILABLE or LOCKED
2166: CURSOR c_get_list(l_source_type VARCHAR2, l_media_id NUMBER) is
2167: SELECT acr.list_header_id
2168: FROM ams_list_cont_restrictions acr,
2169: ams_list_headers_all alh
2170: WHERE acr.list_header_id = alh.list_header_id
2171: AND alh.status_code in ('AVAILABLE','LOCKED')
2172: AND alh.list_source_type = l_source_type
2173: AND acr.media_id = l_media_id;
2270: ac.campaign_id,
2271: ac.activity_id
2272: FROM ams_campaign_schedules_b ac,
2273: ams_list_rule_usages am,
2274: ams_list_headers_all al
2275: WHERE al.list_used_by_id = ac.schedule_id
2276: AND am.list_header_id(+) = al.list_header_id
2277: AND ac.schedule_id = p_list_used_by_id
2278: AND al.list_header_id = p_list_header_id ;
2279:
2280: --to get the datasource of the target group
2281: CURSOR c_get_list_data_source is
2282: SELECT list_source_type
2283: FROM ams_list_headers_all
2284: WHERE list_header_id = p_list_header_id ;
2285:
2286: --to get the columns in ams_list_entries mapped
2287: --to the attributes of the datasource which
2569: select al.list_header_id,
2570: al.ctrl_random_pct_row_selection,
2571: al.ctrl_random_nth_row_selection,
2572: al.generate_control_group_flag, al.generation_type
2573: from ams_act_lists acl,ams_list_headers_all al
2574: where acl.list_used_by = p_list_used_by
2575: and acl.list_used_by_id = p_list_used_by_id
2576: and acl.list_act_type = 'TARGET'
2577: and al.list_header_id = acl.list_header_id ;
2609: l_parameter_list WF_PARAMETER_LIST_T;
2610: l_new_item_key VARCHAR2(30);
2611: l_tg_status_code varchar2(30);
2612: cursor c_tg_status is
2613: select status_code from ams_list_headers_all
2614: where list_header_id = l_list_header_id;
2615:
2616: /* Bug fix: 3799192. Added by rrajesh on 07/30/04. */
2617: l_tca_field_mapped varchar2(1);
2617: l_tca_field_mapped varchar2(1);
2618:
2619: cursor c_master_ds_tca_mapped(list_head_id IN NUMBER)
2620: IS
2621: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
2622: where hd.list_header_id = list_head_id
2623: and hd.LIST_SOURCE_TYPE = ty.source_type_code
2624: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
2625: and fd.tca_column_id is NOT NULL;
2630:
2631: cursor c_remote_list(list_head_id in number)
2632: IS
2633: select nvl(stypes.remote_flag,'N')
2634: from ams_list_src_types stypes, ams_list_headers_all list
2635: where list.list_source_type = stypes.source_type_code
2636: and list_header_id = list_head_id ;
2637:
2638: /* added by savio for p1 bug 3817724 */
2737: write_to_act_log(p_msg_data => 'Data Source fields are not mapped with tca fields -- Aborting target group generation process ',
2738: p_arc_log_used_by => 'TARGET',
2739: p_log_used_by_id => l_list_header_id,
2740: p_level => 'HIGH');
2741: UPDATE ams_list_headers_all
2742: SET last_generation_success_flag = 'N',
2743: status_code = 'FAILED',
2744: user_status_id = 311,
2745: status_date = sysdate,
3025: x_msg_data OUT NOCOPY VARCHAR2
3026: ) is
3027: cursor c1 is
3028: select al.list_header_id
3029: from ams_act_lists acl,ams_list_headers_all al
3030: where acl.list_used_by = p_list_used_by
3031: and acl.list_used_by_id = p_list_used_by_id
3032: and acl.list_act_type = 'TARGET'
3033: and al.list_header_id = acl.list_header_id ;
3045: l_api_version CONSTANT NUMBER := 1.0;
3046:
3047: CURSOR c_status IS
3048: SELECT status_code
3049: FROM ams_list_headers_all
3050: WHERE list_header_id = l_list_header_id ;
3051:
3052: l_status_code varchar2(30);
3053:
3347: l_created_by NUMBER; --batoleti added this var. For bug# 6688996
3348: /* batoleti. Bug# 6688996. Added the below cursor */
3349: CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
3350: SELECT created_by
3351: FROM ams_list_headers_all
3352: WHERE list_header_id= x_list_header_id;
3353:
3354:
3355: begin
3954: l_sched_rec c_new_schedule_details%rowtype;
3955:
3956: CURSOR c_list_size (p_list_header_id IN NUMBER) is
3957: SELECT no_of_rows_active
3958: FROM ams_list_headers_all
3959: WHERE list_header_id = p_list_header_id;
3960:
3961: -- ckapoor R12 dedupe rule copy
3962:
4076: x_msg_data => l_msg_data,
4077: p_listheader_rec => l_listheader_rec,
4078: x_listheader_id => l_list_header_id
4079: );
4080: UPDATE ams_list_headers_all SET query_template_id = l_query_temp_id
4081: WHERE list_header_id = l_list_header_id;
4082: --for i in 1 .. l_msg_count loop
4083: --FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
4084: --FND_API.G_FALSE,
5122: -- This api is called to Lock the Traget Group when the Schedule is in ACTIVE Status
5123: --
5124: -- ALGORITHM
5125: -- a) Take as input type and id e.g. CSCH and schedule id
5126: -- b) It will query AMS_LIST_HEADERS_ALL to check if target group exists for object type and id
5127: -- c) Will do nothing (i.e. will return success) if target group does not exist (since Target Groups are relevant only for certain type of schedules)
5128: -- d) If Target Group is already in LOCKED status do nothing, return;
5129: -- e) If Target Group exists will update the user status id and status code of TG to LOCKED.
5130: -- f) User_Status_Code and User_Status_Id are passed to make it more generic. So we can change the Target Group status as per the requirement and not only to LOCKED status.
5155: AMS_Utility_PVT.debug_message('****FUNCTION: UPDATE_LIST_STATUS_TO_LOCKED start****');
5156:
5157: END IF;
5158:
5159: UPDATE AMS_LIST_HEADERS_ALL
5160: SET STATUS_CODE = 'LOCKED',
5161: USER_STATUS_ID = (select user_status_id from ams_user_statuses_vl where system_status_type ='AMS_LIST_STATUS' and system_status_code ='LOCKED' and default_flag ='Y' ),
5162: STATUS_DATE = SYSDATE,
5163: LAST_UPDATE_DATE = SYSDATE