[Home] [Help]
208: WHERE schedule_id = p_scheduleid;
209:
210: CURSOR C_Content_CL( l_obj_id IN NUMBER) IS
211: SELECT ibcassn.content_item_id,citm.name
212: FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
213: WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
214: AND IbcAssn.Content_item_id = citm.citem_id
215: AND citm.item_status <> 'APPROVED'
216: AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_CSCH' ;
216: AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_CSCH' ;
217:
218: CURSOR C_Content_NDM( l_obj_id IN NUMBER) IS
219: SELECT ibcassn.content_item_id,citm.name
220: FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
221: WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
222: AND IbcAssn.Content_item_id = citm.citem_id
223: AND citm.item_status <> 'APPROVED'
224: AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_COLLAT'
225: AND citm.VERSION = 1 ;
226:
227: CURSOR C_Content_Exists( l_obj_id IN NUMBER) IS
228: SELECT count(1)
229: FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
230: WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
231: AND IbcAssn.Content_item_id = citm.citem_id
232: AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_CSCH') ;
233:
232: AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_CSCH') ;
233:
234: CURSOR C_Content_Exists_NDM( l_obj_id IN NUMBER) IS
235: SELECT count(1)
236: FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
237: WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
238: AND IbcAssn.Content_item_id = citm.citem_id
239: AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_COLLAT') ;
240:
1986:
1987: -- soagrawa added the following cursor on 30-sep-2003 for stamping version in 11.5.10
1988: CURSOR c_cover_letter_det IS
1989: SELECT ci.live_citem_version_id
1990: FROM ibc_associations assoc, ibc_content_Items ci
1991: WHERE assoc.association_type_code = 'AMS_CSCH'
1992: AND assoc.associated_object_val1 = to_char(p_schedule_id) -- fix for bug # 4145845
1993: AND assoc.content_item_id = ci.content_Item_id;
1994:
3975: -- 1. Does target group exist?
3976: -- Yes => 1.1 Increase usage
3977: -- 1.2 Is channel Email, Print, Fax
3978: -- Yes => 1.2.1 Increase contacted count
3979: -- 1.2.2 Stamp version in ibc_associations table
3980: -- 1.2.3 Send Fulfillment Request
3981: -- 1.2.4 Update list sent out date
3982: --
3983: -- Any error in any of the API callouts?
4020:
4021: -- soagrawa added the following cursor on 30-sep-2003 for stamping version
4022: CURSOR c_cover_letter_det (l_csch_id IN NUMBER) IS
4023: SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
4024: FROM ibc_associations assoc, ibc_content_Items ci
4025: WHERE assoc.association_type_code = 'AMS_CSCH'
4026: AND assoc.associated_object_val1 = to_char(l_csch_id) -- fix for bug # 4145845
4027: AND assoc.content_item_id = ci.content_Item_id;
4028:
4165: p_msg_count => l_msg_count,
4166: p_msg_data => l_msg_data);*/
4167:
4168: --
4169: -- 1.2.2 Stamp version in ibc_associations table
4170: -- anchaudh : from R12 onwards this stamping of cover letter version will take place in the new event subscription api.
4171: --
4172: /*AMS_Utility_PVT.Create_Log (
4173: x_return_status => l_log_return_status,
4185: IF l_association_id IS NOT null
4186: AND l_cover_letter_id IS NOT null
4187: AND l_cover_letter_ver_id IS NOT NULl
4188: THEN
4189: Ibc_Associations_Pkg.UPDATE_ROW(
4190: p_association_id => l_association_id
4191: ,p_content_item_id => l_cover_letter_id
4192: ,p_citem_version_id => l_cover_letter_ver_id
4193: ,p_association_type_code => 'AMS_CSCH'
6049: l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
6050:
6051: CURSOR c_citem_assoc (l_csch_id IN NUMBER) IS
6052: SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
6053: FROM ibc_associations assoc, ibc_content_Items ci
6054: WHERE assoc.association_type_code in ('AMS_CSCH','AMS_COLLAT')
6055: AND assoc.associated_object_val1 = to_char(l_csch_id)
6056: AND assoc.content_item_id = ci.content_Item_id;
6057:
6079: IF l_association_id IS NOT null
6080: AND l_citem_id IS NOT null
6081: AND l_citem_ver_id IS NOT NULl
6082: THEN
6083: Ibc_Associations_Pkg.UPDATE_ROW(
6084: p_association_id => l_association_id
6085: ,p_citem_version_id => l_citem_ver_id
6086: );
6087: END IF;