[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:
3945: -- 1. Does target group exist?
3946: -- Yes => 1.1 Increase usage
3947: -- 1.2 Is channel Email, Print, Fax
3948: -- Yes => 1.2.1 Increase contacted count
3949: -- 1.2.2 Stamp version in ibc_associations table
3950: -- 1.2.3 Send Fulfillment Request
3951: -- 1.2.4 Update list sent out date
3952: --
3953: -- Any error in any of the API callouts?
3990:
3991: -- soagrawa added the following cursor on 30-sep-2003 for stamping version
3992: CURSOR c_cover_letter_det (l_csch_id IN NUMBER) IS
3993: SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
3994: FROM ibc_associations assoc, ibc_content_Items ci
3995: WHERE assoc.association_type_code = 'AMS_CSCH'
3996: AND assoc.associated_object_val1 = to_char(l_csch_id) -- fix for bug # 4145845
3997: AND assoc.content_item_id = ci.content_Item_id;
3998:
4135: p_msg_count => l_msg_count,
4136: p_msg_data => l_msg_data);*/
4137:
4138: --
4139: -- 1.2.2 Stamp version in ibc_associations table
4140: -- anchaudh : from R12 onwards this stamping of cover letter version will take place in the new event subscription api.
4141: --
4142: /*AMS_Utility_PVT.Create_Log (
4143: x_return_status => l_log_return_status,
4155: IF l_association_id IS NOT null
4156: AND l_cover_letter_id IS NOT null
4157: AND l_cover_letter_ver_id IS NOT NULl
4158: THEN
4159: Ibc_Associations_Pkg.UPDATE_ROW(
4160: p_association_id => l_association_id
4161: ,p_content_item_id => l_cover_letter_id
4162: ,p_citem_version_id => l_cover_letter_ver_id
4163: ,p_association_type_code => 'AMS_CSCH'
6019: l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
6020:
6021: CURSOR c_citem_assoc (l_csch_id IN NUMBER) IS
6022: SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
6023: FROM ibc_associations assoc, ibc_content_Items ci
6024: WHERE assoc.association_type_code in ('AMS_CSCH','AMS_COLLAT')
6025: AND assoc.associated_object_val1 = to_char(l_csch_id)
6026: AND assoc.content_item_id = ci.content_Item_id;
6027:
6049: IF l_association_id IS NOT null
6050: AND l_citem_id IS NOT null
6051: AND l_citem_ver_id IS NOT NULl
6052: THEN
6053: Ibc_Associations_Pkg.UPDATE_ROW(
6054: p_association_id => l_association_id
6055: ,p_citem_version_id => l_citem_ver_id
6056: );
6057: END IF;