[Home] [Help]
526: l_msg_count := FND_MSG_PUB.Count_Msg;
527: IF(l_msg_count <> 0)THEN
528: FOR l_iterator IN 1 .. l_msg_count LOOP
529: FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
530: FND_API.G_FALSE,
531: l_msg_data,
532: l_msg_count);
533: UPDATE ams_list_headers_all
534: SET result_text = result_text||' '|| l_msg_data
974: l_null varchar2(30) := null;
975: -- deletes from ams_list_entries in case of standard type of generation
976: BEGIN
977: write_to_act_log('Executing delete_list_entries to delete entries from ams_list_entries(Since list/target group is generated in STANDARD mode).', 'LIST', g_list_header_id,'LOW');
978: x_return_status := FND_API.G_RET_STS_SUCCESS;
979: -------------------------------------------------------------------------------
980: --Delete all existing entries for this list which are in the temporary table.--
981: -------------------------------------------------------------------------------
982: l_delete_action := 'List tmp Entries delete';
1031: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
1032: FND_MESSAGE.Set_Token('TEXT', 'Delete List Entries ' ||
1033: l_delete_action || ' '|| SQLERRM||' '||SQLCODE);
1034: FND_MSG_PUB.Add;
1035: x_return_status := FND_API.G_RET_STS_ERROR ;
1036:
1037: END delete_list_entries;
1038:
1039:
1066: SET no_of_rows_used = 0
1067: WHERE arc_action_used_by = 'LIST'
1068: and action_used_by_id = p_list_header_id;
1069:
1070: x_return_status := FND_API.G_RET_STS_SUCCESS;
1071:
1072: EXCEPTION
1073: WHEN OTHERS THEN
1074: write_to_act_log('Error while executing initialize_list_header procedure', 'LIST', g_list_header_id,'HIGH');
1075: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
1076: FND_MESSAGE.Set_Token('TEXT', 'Initialize List Header ' ||
1077: SQLERRM||' '||SQLCODE);
1078: FND_MSG_PUB.Add;
1079: x_return_status := FND_API.G_RET_STS_ERROR ;
1080: END initialize_list_header;
1081:
1082: -------------------------------------------------------------------------------
1083: -- Start of Comments
1106: x_msg_data OUT NOCOPY varchar2,
1107: x_return_status OUT NOCOPY VARCHAR2 ) IS
1108: BEGIN
1109: write_to_act_log('Executing procedure initialize_list', 'LIST', g_list_header_id,'LOW');
1110: x_return_status := FND_API.G_RET_STS_SUCCESS;
1111: -----------------------------------------------------------------
1112: --Delete any existing log entries for this list. --
1113: -----------------------------------------------------------------
1114: --DELETE FROM ams_act_logs
1122: initialize_list_header(p_list_header_id => p_list_header_rec.list_header_id,
1123: x_msg_count => x_msg_count,
1124: x_msg_data => x_msg_data,
1125: x_return_status => x_return_status);
1126: if x_return_status <> FND_API.g_ret_sts_success then
1127: write_to_act_log('Error while executing initialize_list_header. Unable to Initialize List ', 'LIST', g_list_header_id,'HIGH');
1128: raise FND_API.g_exc_unexpected_error;
1129: end if;
1130: -----------------------------------------------------------------
1124: x_msg_data => x_msg_data,
1125: x_return_status => x_return_status);
1126: if x_return_status <> FND_API.g_ret_sts_success then
1127: write_to_act_log('Error while executing initialize_list_header. Unable to Initialize List ', 'LIST', g_list_header_id,'HIGH');
1128: raise FND_API.g_exc_unexpected_error;
1129: end if;
1130: -----------------------------------------------------------------
1131: --Deleting any existing entries. if generation type is Standard--
1132: -----------------------------------------------------------------
1135: delete_list_entries (p_list_header_id => p_list_header_rec.list_header_id,
1136: x_msg_count => x_msg_count,
1137: x_msg_data => x_msg_data,
1138: x_return_status => x_return_status);
1139: if x_return_status <> FND_API.g_ret_sts_success then
1140: write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', g_list_header_id,'HIGH');
1141: raise FND_API.g_exc_unexpected_error;
1142: end if;
1143: end if;
1137: x_msg_data => x_msg_data,
1138: x_return_status => x_return_status);
1139: if x_return_status <> FND_API.g_ret_sts_success then
1140: write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', g_list_header_id,'HIGH');
1141: raise FND_API.g_exc_unexpected_error;
1142: end if;
1143: end if;
1144: write_to_act_log('Procedure initialize_list executed successfully.', 'LIST', g_list_header_id,'LOW');
1145: -- CHECK if we need to initlialize list select actions and
1144: write_to_act_log('Procedure initialize_list executed successfully.', 'LIST', g_list_header_id,'LOW');
1145: -- CHECK if we need to initlialize list select actions and
1146: -- and list header number of rows etc.
1147: EXCEPTION
1148: WHEN FND_API.g_exc_error THEN
1149: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1150: x_return_status := FND_API.g_ret_sts_error;
1151: FND_MSG_PUB.count_and_get(
1152: p_encoded => FND_API.g_false,
1146: -- and list header number of rows etc.
1147: EXCEPTION
1148: WHEN FND_API.g_exc_error THEN
1149: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1150: x_return_status := FND_API.g_ret_sts_error;
1151: FND_MSG_PUB.count_and_get(
1152: p_encoded => FND_API.g_false,
1153: p_count => x_msg_count,
1154: p_data => x_msg_data
1148: WHEN FND_API.g_exc_error THEN
1149: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1150: x_return_status := FND_API.g_ret_sts_error;
1151: FND_MSG_PUB.count_and_get(
1152: p_encoded => FND_API.g_false,
1153: p_count => x_msg_count,
1154: p_data => x_msg_data
1155: );
1156:
1153: p_count => x_msg_count,
1154: p_data => x_msg_data
1155: );
1156:
1157: WHEN FND_API.g_exc_unexpected_error THEN
1158: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1159: x_return_status := FND_API.g_ret_sts_unexp_error ;
1160: FND_MSG_PUB.count_and_get(
1161: p_encoded => FND_API.g_false,
1155: );
1156:
1157: WHEN FND_API.g_exc_unexpected_error THEN
1158: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1159: x_return_status := FND_API.g_ret_sts_unexp_error ;
1160: FND_MSG_PUB.count_and_get(
1161: p_encoded => FND_API.g_false,
1162: p_count => x_msg_count,
1163: p_data => x_msg_data
1157: WHEN FND_API.g_exc_unexpected_error THEN
1158: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1159: x_return_status := FND_API.g_ret_sts_unexp_error ;
1160: FND_MSG_PUB.count_and_get(
1161: p_encoded => FND_API.g_false,
1162: p_count => x_msg_count,
1163: p_data => x_msg_data
1164: );
1165:
1164: );
1165:
1166: WHEN OTHERS THEN
1167: write_to_act_log('Error while executing procedure initialize_list '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
1168: x_return_status := FND_API.g_ret_sts_unexp_error ;
1169: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1170: FND_MSG_PUB.add_exc_msg(g_pkg_name, g_file_name);
1171: END IF;
1172: FND_MSG_PUB.count_and_get(
1169: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1170: FND_MSG_PUB.add_exc_msg(g_pkg_name, g_file_name);
1171: END IF;
1172: FND_MSG_PUB.count_and_get(
1173: p_encoded => FND_API.g_false,
1174: p_count => x_msg_count,
1175: p_data => x_msg_data
1176: );
1177:
3292: if l_found = 'N' then
3293: write_to_act_log('Validation failed for this SQL string in validate_sql_string procedure'||l_found,'LIST', g_list_header_id,'HIGH');
3294: FND_MESSAGE.set_name('AMS', 'AMS_LIST_FROM_NOT_FOUND');
3295: FND_MSG_PUB.Add;
3296: RAISE FND_API.G_EXC_ERROR;
3297: end if;
3298: write_to_act_log('SQL string validated successfully in validate_sql_string','LIST', g_list_header_id,'LOW');
3299:
3300: l_found := 'N';
3313: if l_found = 'N' then
3314: write_to_act_log('No master type found','LIST', g_list_header_id,'LOW');
3315: FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_MASTER_TYPE');
3316: FND_MSG_PUB.Add;
3317: RAISE FND_API.G_EXC_ERROR;
3318: end if;
3319:
3320: if p_list_action_type = 'INCLUDE' then
3321: l_found := 'N';
3399: end if;
3400: end if;
3401: write_to_act_log('Execution of process_all_sql ends ', 'LIST', g_list_header_id,'LOW');
3402: EXCEPTION
3403: WHEN FND_API.G_EXC_ERROR THEN
3404: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3405: x_return_status := FND_API.G_RET_STS_ERROR ;
3406: FND_MSG_PUB.count_and_get(
3407: p_encoded => FND_API.g_false,
3401: write_to_act_log('Execution of process_all_sql ends ', 'LIST', g_list_header_id,'LOW');
3402: EXCEPTION
3403: WHEN FND_API.G_EXC_ERROR THEN
3404: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3405: x_return_status := FND_API.G_RET_STS_ERROR ;
3406: FND_MSG_PUB.count_and_get(
3407: p_encoded => FND_API.g_false,
3408: p_count => x_msg_count,
3409: p_data => x_msg_data
3403: WHEN FND_API.G_EXC_ERROR THEN
3404: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3405: x_return_status := FND_API.G_RET_STS_ERROR ;
3406: FND_MSG_PUB.count_and_get(
3407: p_encoded => FND_API.g_false,
3408: p_count => x_msg_count,
3409: p_data => x_msg_data
3410: );
3411:
3408: p_count => x_msg_count,
3409: p_data => x_msg_data
3410: );
3411:
3412: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3413: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3414: x_return_status := FND_API.G_RET_STS_ERROR ;
3415: FND_MSG_PUB.count_and_get(
3416: p_encoded => FND_API.g_false,
3410: );
3411:
3412: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3413: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3414: x_return_status := FND_API.G_RET_STS_ERROR ;
3415: FND_MSG_PUB.count_and_get(
3416: p_encoded => FND_API.g_false,
3417: p_count => x_msg_count,
3418: p_data => x_msg_data
3412: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3413: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3414: x_return_status := FND_API.G_RET_STS_ERROR ;
3415: FND_MSG_PUB.count_and_get(
3416: p_encoded => FND_API.g_false,
3417: p_count => x_msg_count,
3418: p_data => x_msg_data
3419: );
3420:
3419: );
3420:
3421: WHEN OTHERS THEN
3422: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3423: x_return_status := FND_API.G_RET_STS_ERROR ;
3424: FND_MSG_PUB.count_and_get(
3425: p_encoded => FND_API.g_false,
3426: p_count => x_msg_count,
3427: p_data => x_msg_data
3421: WHEN OTHERS THEN
3422: write_to_act_log('Error while executing process_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
3423: x_return_status := FND_API.G_RET_STS_ERROR ;
3424: FND_MSG_PUB.count_and_get(
3425: p_encoded => FND_API.g_false,
3426: p_count => x_msg_count,
3427: p_data => x_msg_data
3428: );
3429:
3572: write_to_act_log('Procedure process_sql executed.', 'LIST', g_list_header_id,'LOW' );
3573: exception
3574: when others then
3575: write_to_act_log('Error while executing procedure process_sql '||sqlcode||' '|| sqlerrm, 'LIST', g_list_header_id,'HIGH');
3576: x_return_status := FND_API.G_RET_STS_ERROR ;
3577: END process_sql;
3578:
3579: PROCEDURE process_diwb (p_action_used_by_id in number,
3580: p_incl_object_id in number,
3650: where list_header_id = g_list_header_id;
3651: -- Added for cancel list gen as it prevents parallel update- Raghu
3652: -- of list headers when cancel button is pressed
3653: commit;
3654: x_return_status := FND_API.G_RET_STS_ERROR;
3655: return;
3656: end if;
3657: --
3658: l_counter := l_counter +1 ;
3727: write_to_act_log('Executing process_cell since segment has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
3728: write_to_act_log('Calling ams_cell_pvt.get_comp_sql to get the sql', 'LIST', g_list_header_id,'LOW');
3729: ams_cell_pvt.get_comp_sql(
3730: p_api_version => 1.0,
3731: p_init_msg_list => FND_API.g_false,
3732: p_validation_level => FND_API.g_valid_level_full,
3733: x_return_status => x_return_status,
3734: x_msg_count => x_msg_count ,
3735: x_msg_data =>x_msg_data,
3728: write_to_act_log('Calling ams_cell_pvt.get_comp_sql to get the sql', 'LIST', g_list_header_id,'LOW');
3729: ams_cell_pvt.get_comp_sql(
3730: p_api_version => 1.0,
3731: p_init_msg_list => FND_API.g_false,
3732: p_validation_level => FND_API.g_valid_level_full,
3733: x_return_status => x_return_status,
3734: x_msg_count => x_msg_count ,
3735: x_msg_data =>x_msg_data,
3736: p_cell_id => p_incl_object_id ,
3733: x_return_status => x_return_status,
3734: x_msg_count => x_msg_count ,
3735: x_msg_data =>x_msg_data,
3736: p_cell_id => p_incl_object_id ,
3737: p_party_id_only => FND_API.g_true, --bug 4635925
3738: x_sql_tbl => l_sql_2
3739: );
3740: write_to_act_log('Procedure ams_cell_pvt.get_comp_sql executed. Return status is '||x_return_status, 'LIST', g_list_header_id,'LOW');
3741: l_sql_string_final := '';
3799: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
3800: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
3801: p_action_rec ams_listaction_pvt.action_rec_type;
3802: l_list_select_action_id number;
3803: l_return_status VARCHAR2(100) := FND_API.G_FALSE;
3804:
3805: ----------------------------------------------------------------------------
3806: -- Cursor definition to select list_select_action_id.Will be used in loop to
3807: -- Process each cursor record according to order specified by the user
3946: x_msg_data => x_msg_data ,
3947: x_return_status => x_return_status
3948: );
3949:
3950: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3951: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
3952: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
3953: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
3954: end if;
3951: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
3952: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
3953: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
3954: end if;
3955: RAISE FND_API.G_EXC_ERROR;
3956: ELSE
3957: write_to_act_log('Success in Cancel List generation', 'LIST', g_list_header_id,'LOW');
3958: END IF;
3959:
3984: IF (p_action_rec.list_action_type <> 'INCLUDE')then
3985: write_to_act_log('Action type of the first selection should always be INCLUDE. Aborting list generation.', 'LIST', g_list_header_id,'HIGH');
3986: FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_FIRST_INCLUDE');
3987: FND_MSG_PUB.Add;
3988: RAISE FND_API.G_EXC_ERROR;
3989: END IF; -- End of if for list_action_type check
3990: END IF; --End of Validation:- First Action Include Check
3991:
3992: write_to_act_log('Calling validate_listaction procedure to validate the list selection attributes','LIST',g_list_header_id, 'LOW');
3991:
3992: write_to_act_log('Calling validate_listaction procedure to validate the list selection attributes','LIST',g_list_header_id, 'LOW');
3993: ams_listaction_pvt.Validate_ListAction
3994: ( p_api_version => 1.0,
3995: p_init_msg_list => FND_API.G_FALSE,
3996: p_validation_level => JTF_PLSQL_API.G_VALID_LEVEL_RECORD,
3997: x_return_status => x_return_status,
3998: x_msg_count => x_msg_count,
3999: x_msg_data => x_msg_data,
3999: x_msg_data => x_msg_data,
4000: p_action_rec => p_action_rec
4001: );
4002:
4003: IF x_return_status = FND_API.g_ret_sts_error THEN
4004: RAISE FND_API.g_exc_error;
4005: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4006: RAISE FND_API.g_exc_unexpected_error;
4007: END IF;
4000: p_action_rec => p_action_rec
4001: );
4002:
4003: IF x_return_status = FND_API.g_ret_sts_error THEN
4004: RAISE FND_API.g_exc_error;
4005: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4006: RAISE FND_API.g_exc_unexpected_error;
4007: END IF;
4008:
4001: );
4002:
4003: IF x_return_status = FND_API.g_ret_sts_error THEN
4004: RAISE FND_API.g_exc_error;
4005: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4006: RAISE FND_API.g_exc_unexpected_error;
4007: END IF;
4008:
4009: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4002:
4003: IF x_return_status = FND_API.g_ret_sts_error THEN
4004: RAISE FND_API.g_exc_error;
4005: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4006: RAISE FND_API.g_exc_unexpected_error;
4007: END IF;
4008:
4009: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4010: write_to_act_log('Error while validating list selections', 'LIST', g_list_header_id,'HIGH');
4150: end loop;
4151:
4152: end if;
4153:
4154: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
4155: RAISE FND_API.G_EXC_ERROR;
4156: END IF;
4157: write_to_act_log('Action on the list/target group selection completed. ', 'LIST', g_list_header_id,'LOW');
4158: -- end Of Dynamic Procedure
4151:
4152: end if;
4153:
4154: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
4155: RAISE FND_API.G_EXC_ERROR;
4156: END IF;
4157: write_to_act_log('Action on the list/target group selection completed. ', 'LIST', g_list_header_id,'LOW');
4158: -- end Of Dynamic Procedure
4159: l_join_string :='';
4327: main_gen_end_time = sysdate
4328: where list_header_id = g_list_header_id;
4329: update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
4330: write_to_act_log('Aborting list generation ', 'LIST', g_list_header_id,'HIGH');
4331: x_return_status := FND_API.g_ret_sts_error; --Gen return status should go to error.
4332: return;
4333: --
4334: else
4335: write_to_act_log('List generated successfully in remote instance', 'LIST', g_list_header_id,'HIGH');
4509: main_gen_end_time = sysdate
4510: where list_header_id = g_list_header_id;
4511: update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
4512: write_to_act_log('Aborting list generation ', 'LIST', g_list_header_id,'HIGH');
4513: x_return_status := FND_API.g_ret_sts_error; --Gen return status should go to error.
4514: return;
4515: --
4516: else
4517: write_to_act_log('List generated successfully in remote instance', 'LIST', g_list_header_id,'HIGH');
4520: end if;
4521: end loop;
4522: WRITE_TO_ACT_LOG('Execution of Process_list_action completed', 'LIST', g_list_header_id,'LOW');
4523: EXCEPTION
4524: WHEN FND_API.G_EXC_ERROR THEN
4525: IF(c_action_dets%ISOPEN)THEN
4526: CLOSE c_action_dets;
4527: END IF;
4528: write_to_act_log('Error while executing process_list_actions '
4527: END IF;
4528: write_to_act_log('Error while executing process_list_actions '
4529: ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
4530: -- Check if reset of the status is required
4531: x_return_status := FND_API.G_RET_STS_ERROR ;
4532: FND_MSG_PUB.count_and_get(
4533: p_encoded => FND_API.g_false,
4534: p_count => x_msg_count,
4535: p_data => x_msg_data);
4529: ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
4530: -- Check if reset of the status is required
4531: x_return_status := FND_API.G_RET_STS_ERROR ;
4532: FND_MSG_PUB.count_and_get(
4533: p_encoded => FND_API.g_false,
4534: p_count => x_msg_count,
4535: p_data => x_msg_data);
4536:
4537: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4533: p_encoded => FND_API.g_false,
4534: p_count => x_msg_count,
4535: p_data => x_msg_data);
4536:
4537: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4538: IF(c_action_dets%ISOPEN)THEN
4539: CLOSE c_action_dets;
4540: END IF;
4541: write_to_act_log('Error while executing process_list_actions '
4542: ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
4543: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
4544: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
4545: FND_MSG_PUB.Add;
4546: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4547: FND_MSG_PUB.count_and_get(
4548: p_encoded => FND_API.g_false,
4549: p_count => x_msg_count,
4550: p_data => x_msg_data);
4544: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
4545: FND_MSG_PUB.Add;
4546: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4547: FND_MSG_PUB.count_and_get(
4548: p_encoded => FND_API.g_false,
4549: p_count => x_msg_count,
4550: p_data => x_msg_data);
4551:
4552: WHEN AMS_LISTGENERATION_UTIL_PKG.cancelListGen THEN
4553: IF(c_action_dets%ISOPEN)THEN
4554: CLOSE c_action_dets;
4555: END IF;
4556:
4557: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4558:
4559: write_to_act_log('executing process_list_actions - user action to cancel list generation detected ' ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
4560: -- Got to raise the exception again because Listgen has to end generation.
4561: raise AMS_LISTGENERATION_UTIL_PKG.cancelListGen;
4568: ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
4569: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
4570: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
4571: FND_MSG_PUB.Add;
4572: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4573: FND_MSG_PUB.count_and_get(
4574: p_encoded => FND_API.g_false,
4575: p_count => x_msg_count,
4576: p_data => x_msg_data);
4570: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
4571: FND_MSG_PUB.Add;
4572: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4573: FND_MSG_PUB.count_and_get(
4574: p_encoded => FND_API.g_false,
4575: p_count => x_msg_count,
4576: p_data => x_msg_data);
4577:
4578: END process_list_actions;
4613: p_no_of_rows_min_requested in number,
4614: x_return_status OUT NOCOPY VARCHAR2 ) IS
4615:
4616: l_entry_count NUMBER;
4617: l_return_status VARCHAR2(100) := FND_API.G_FALSE;
4618: l_source_code VARCHAR2(100) := 'NONE';
4619: l_source_id NUMBER := 0;
4620:
4621: l_created_by NUMBER;
4730: FND_MESSAGE.set_name('AMS', 'AMS_LIST_MINIMUM_NOT_REACHED');
4731: FND_MESSAGE.Set_Token('NO_AVAILABLE',TO_CHAR(l_entry_count));
4732: FND_MESSAGE.Set_Token('NO_REQUESTED',TO_CHAR(p_no_of_rows_min_requested));
4733: FND_MSG_PUB.Add;
4734: x_return_status := FND_API.G_RET_STS_ERROR;
4735: ELSE
4736: x_return_status := FND_API.G_RET_STS_SUCCESS;
4737: END if;
4738: */
4732: FND_MESSAGE.Set_Token('NO_REQUESTED',TO_CHAR(p_no_of_rows_min_requested));
4733: FND_MSG_PUB.Add;
4734: x_return_status := FND_API.G_RET_STS_ERROR;
4735: ELSE
4736: x_return_status := FND_API.G_RET_STS_SUCCESS;
4737: END if;
4738: */
4739: x_return_status := FND_API.G_RET_STS_SUCCESS;
4740: -- End changes for migration
4735: ELSE
4736: x_return_status := FND_API.G_RET_STS_SUCCESS;
4737: END if;
4738: */
4739: x_return_status := FND_API.G_RET_STS_SUCCESS;
4740: -- End changes for migration
4741: exception
4742: when others then
4743: write_to_act_log('Error while executing procedure create_list_entries', 'LIST', g_list_header_id,'HIGH');
4740: -- End changes for migration
4741: exception
4742: when others then
4743: write_to_act_log('Error while executing procedure create_list_entries', 'LIST', g_list_header_id,'HIGH');
4744: x_return_status := FND_API.G_RET_STS_ERROR;
4745:
4746: end create_list_entries;
4747:
4748:
6715: END LOOP;
6716: CLOSE c_child_mapping;
6717: END LOOP;
6718: CLOSE C_MAPPING_TYPES_USED;
6719: x_return_status := FND_API.G_RET_STS_SUCCESS;
6720: write_to_act_log('Calling update_list_entries to update selections based on list.','LIST', g_list_header_id,'LOW');
6721: -- update_list_entries(p_list_header_id); LPO
6722: write_to_act_log('Calling update_import_list_entries to update selections based on import.','LIST', g_list_header_id,'LOW');
6723: update_import_list_entries (p_list_header_id);
6732: CLOSE C_MAPPING_TYPE_FIELDS;
6733: END IF;
6734:
6735: write_to_act_log('Error while generating list in update mode : '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
6736: x_return_status := FND_API.G_RET_STS_ERROR;
6737: END GET_LIST_ENTRY_DATA;
6738:
6739:
6740: -- START OF COMMENTS
6749: -------------------------------------------------------------------------------
6750:
6751: PROCEDURE GENERATE_LIST
6752: ( p_api_version IN NUMBER,
6753: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
6754: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6755: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6756: p_list_header_id IN NUMBER,
6757: x_return_status OUT NOCOPY VARCHAR2,
6750:
6751: PROCEDURE GENERATE_LIST
6752: ( p_api_version IN NUMBER,
6753: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
6754: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6755: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6756: p_list_header_id IN NUMBER,
6757: x_return_status OUT NOCOPY VARCHAR2,
6758: x_msg_count OUT NOCOPY NUMBER,
6751: PROCEDURE GENERATE_LIST
6752: ( p_api_version IN NUMBER,
6753: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
6754: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6755: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6756: p_list_header_id IN NUMBER,
6757: x_return_status OUT NOCOPY VARCHAR2,
6758: x_msg_count OUT NOCOPY NUMBER,
6759: x_msg_data OUT NOCOPY VARCHAR2) IS
7051: last_update_date = sysdate,
7052: main_gen_end_time = sysdate
7053: WHERE list_header_id = g_list_header_id;
7054: FND_MSG_PUB.count_and_get(
7055: p_encoded => FND_API.g_false,
7056: p_count => x_msg_count,
7057: p_data => x_msg_data
7058: );
7059: x_return_status := FND_API.g_ret_sts_error;
7055: p_encoded => FND_API.g_false,
7056: p_count => x_msg_count,
7057: p_data => x_msg_data
7058: );
7059: x_return_status := FND_API.g_ret_sts_error;
7060: --
7061: logger;
7062: RETURN;
7063: END IF;
7088: WHERE list_header_id = p_list_header_id;
7089: -- calling logging program
7090: logger;
7091: --
7092: IF FND_API.To_Boolean ( p_commit ) THEN
7093: COMMIT WORK;
7094: END IF;
7095: --Modified by bmuthukr. Bug # 4083665
7096: x_return_status := FND_API.g_ret_sts_error;
7092: IF FND_API.To_Boolean ( p_commit ) THEN
7093: COMMIT WORK;
7094: END IF;
7095: --Modified by bmuthukr. Bug # 4083665
7096: x_return_status := FND_API.g_ret_sts_error;
7097: --
7098: RETURN;
7099: end if;
7100:
7130: last_update_date = sysdate,
7131: main_gen_end_time = sysdate
7132: WHERE list_header_id = g_list_header_id;
7133: --Modified by bmuthukr. Bug # 4083665
7134: x_return_status := FND_API.g_ret_sts_error;
7135: --
7136: logger;
7137: RETURN;
7138: END IF;
7142: p_arc_log_used_by => 'LIST',
7143: p_log_used_by_id => p_list_header_id,
7144: p_level => 'LOW');
7145:
7146: x_return_status := FND_API.G_RET_STS_SUCCESS;
7147:
7148: -- Standard call to check for call compatibility.
7149: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7150: p_api_version,
7145:
7146: x_return_status := FND_API.G_RET_STS_SUCCESS;
7147:
7148: -- Standard call to check for call compatibility.
7149: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7150: p_api_version,
7151: l_api_name,
7152: G_PKG_NAME)
7153: THEN
7150: p_api_version,
7151: l_api_name,
7152: G_PKG_NAME)
7153: THEN
7154: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7155: END IF;
7156:
7157: IF FND_API.to_Boolean( p_init_msg_list ) THEN
7158: FND_MSG_PUB.initialize;
7153: THEN
7154: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7155: END IF;
7156:
7157: IF FND_API.to_Boolean( p_init_msg_list ) THEN
7158: FND_MSG_PUB.initialize;
7159: END IF;
7160:
7161: -- Checking if Debug is set. If debug is set then log debugging message
7165: FND_MSG_PUB.Add;
7166: END IF;
7167:
7168: -- Initialize API return status to success
7169: x_return_status := FND_API.G_RET_STS_SUCCESS;
7170:
7171:
7172: open c_remote_list;
7173: fetch c_remote_list into g_remote_list,g_database_link;
7246: p_log_used_by_id => p_list_header_id,
7247: p_level => 'LOW');
7248: end if;
7249:
7250: IF x_return_status = FND_API.g_ret_sts_error THEN
7251: RAISE FND_API.g_exc_error;
7252: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7253: RAISE FND_API.g_exc_unexpected_error;
7254: END IF;
7247: p_level => 'LOW');
7248: end if;
7249:
7250: IF x_return_status = FND_API.g_ret_sts_error THEN
7251: RAISE FND_API.g_exc_error;
7252: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7253: RAISE FND_API.g_exc_unexpected_error;
7254: END IF;
7255:
7248: end if;
7249:
7250: IF x_return_status = FND_API.g_ret_sts_error THEN
7251: RAISE FND_API.g_exc_error;
7252: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7253: RAISE FND_API.g_exc_unexpected_error;
7254: END IF;
7255:
7256: if l_listheader_rec.generation_type = 'UPD' then
7249:
7250: IF x_return_status = FND_API.g_ret_sts_error THEN
7251: RAISE FND_API.g_exc_error;
7252: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7253: RAISE FND_API.g_exc_unexpected_error;
7254: END IF;
7255:
7256: if l_listheader_rec.generation_type = 'UPD' then
7257: open c_check_gen_mode;
7269: last_update_date = sysdate,
7270: main_gen_end_time = sysdate,
7271: no_of_rows_in_ctrl_group = null
7272: WHERE list_header_id = g_list_header_id;
7273: x_return_status := FND_API.g_ret_sts_error;
7274: logger;
7275: RETURN;
7276: end if;
7277: end if;
7334: --
7335: GET_LIST_ENTRY_DATA(
7336: p_list_header_id =>l_listheader_rec.list_header_id,
7337: x_return_status => x_return_status);
7338: IF x_return_status = FND_API.g_ret_sts_error THEN
7339: RAISE FND_API.g_exc_error;
7340: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7341: RAISE FND_API.g_exc_unexpected_error;
7342: END IF;
7335: GET_LIST_ENTRY_DATA(
7336: p_list_header_id =>l_listheader_rec.list_header_id,
7337: x_return_status => x_return_status);
7338: IF x_return_status = FND_API.g_ret_sts_error THEN
7339: RAISE FND_API.g_exc_error;
7340: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7341: RAISE FND_API.g_exc_unexpected_error;
7342: END IF;
7343: -- end if;
7336: p_list_header_id =>l_listheader_rec.list_header_id,
7337: x_return_status => x_return_status);
7338: IF x_return_status = FND_API.g_ret_sts_error THEN
7339: RAISE FND_API.g_exc_error;
7340: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7341: RAISE FND_API.g_exc_unexpected_error;
7342: END IF;
7343: -- end if;
7344: -- END IF;
7337: x_return_status => x_return_status);
7338: IF x_return_status = FND_API.g_ret_sts_error THEN
7339: RAISE FND_API.g_exc_error;
7340: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7341: RAISE FND_API.g_exc_unexpected_error;
7342: END IF;
7343: -- end if;
7344: -- END IF;
7345: ELSE --
7363: write_to_act_log('Error in generating list.', 'LIST', g_list_header_id,'HIGH');
7364: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
7365: end if;
7366:
7367: IF x_return_status = FND_API.g_ret_sts_error THEN
7368: RAISE FND_API.g_exc_error;
7369: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7370: RAISE FND_API.g_exc_unexpected_error;
7371: END IF;
7364: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
7365: end if;
7366:
7367: IF x_return_status = FND_API.g_ret_sts_error THEN
7368: RAISE FND_API.g_exc_error;
7369: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7370: RAISE FND_API.g_exc_unexpected_error;
7371: END IF;
7372:
7365: end if;
7366:
7367: IF x_return_status = FND_API.g_ret_sts_error THEN
7368: RAISE FND_API.g_exc_error;
7369: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7370: RAISE FND_API.g_exc_unexpected_error;
7371: END IF;
7372:
7373:
7366:
7367: IF x_return_status = FND_API.g_ret_sts_error THEN
7368: RAISE FND_API.g_exc_error;
7369: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7370: RAISE FND_API.g_exc_unexpected_error;
7371: END IF;
7372:
7373:
7374: /******************************************************************************/
7385: x_msg_data => x_msg_data ,
7386: x_return_status => x_return_status
7387: );
7388:
7389: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
7390: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
7391: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
7392: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
7393: end if;
7390: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
7391: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
7392: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
7393: end if;
7394: RAISE FND_API.G_EXC_ERROR;
7395: ELSE
7396: write_to_act_log('Success in Cancel List generation', 'LIST', g_list_header_id,'LOW');
7397: END IF;
7398:
7408: set enabled_flag = 'N'
7409: where newly_updated_flag = 'N'
7410: and list_header_id = l_listheader_rec.list_header_id;
7411: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
7412: IF x_return_status = FND_API.g_ret_sts_error THEN
7413: RAISE FND_API.g_exc_error;
7414: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7415: RAISE FND_API.g_exc_unexpected_error;
7416: END IF;
7409: where newly_updated_flag = 'N'
7410: and list_header_id = l_listheader_rec.list_header_id;
7411: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
7412: IF x_return_status = FND_API.g_ret_sts_error THEN
7413: RAISE FND_API.g_exc_error;
7414: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7415: RAISE FND_API.g_exc_unexpected_error;
7416: END IF;
7417: -- -----------------------
7410: and list_header_id = l_listheader_rec.list_header_id;
7411: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
7412: IF x_return_status = FND_API.g_ret_sts_error THEN
7413: RAISE FND_API.g_exc_error;
7414: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7415: RAISE FND_API.g_exc_unexpected_error;
7416: END IF;
7417: -- -----------------------
7418: else -- if g_remote_list = 'Y' then
7411: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
7412: IF x_return_status = FND_API.g_ret_sts_error THEN
7413: RAISE FND_API.g_exc_error;
7414: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
7415: RAISE FND_API.g_exc_unexpected_error;
7416: END IF;
7417: -- -----------------------
7418: else -- if g_remote_list = 'Y' then
7419: write_to_act_log('Updating list in remote instance.', 'LIST', p_list_header_id,'HIGH');
7544: out x_msg_data;
7545: write_to_act_log('x return status '||x_return_status, 'LIST', g_list_header_id,'LOW');
7546: write_to_act_log('apply_size_reduction procedure executed in the remote instance.', 'LIST', g_list_header_id,'LOW');
7547: end if;
7548: IF (x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
7549: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
7550: write_to_act_log('Error in while executing size reduction procedure.', 'LIST', g_list_header_id,'HIGH');
7551: end if;
7552: RAISE FND_API.G_EXC_ERROR;
7548: IF (x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
7549: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
7550: write_to_act_log('Error in while executing size reduction procedure.', 'LIST', g_list_header_id,'HIGH');
7551: end if;
7552: RAISE FND_API.G_EXC_ERROR;
7553: ELSE
7554: write_to_act_log('Procedure apply_size_reduction executed successfully.', 'LIST', g_list_header_id,'LOW');
7555: IF FND_API.To_Boolean ( p_commit ) THEN
7556: COMMIT;
7551: end if;
7552: RAISE FND_API.G_EXC_ERROR;
7553: ELSE
7554: write_to_act_log('Procedure apply_size_reduction executed successfully.', 'LIST', g_list_header_id,'LOW');
7555: IF FND_API.To_Boolean ( p_commit ) THEN
7556: COMMIT;
7557: END IF;
7558: END IF;
7559: end if;
7669: elsif nvl(x_return_status,'S') = 'S' then
7670: write_to_act_log('List header and selections info updated.'||x_msg_data , 'LIST', g_list_header_id,'LOW');
7671: end if;
7672:
7673: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
7674: write_to_act_log('Error after calling update_list_dets procedure', 'LIST', p_list_header_id,'HIGH');
7675: RAISE FND_API.G_EXC_ERROR;
7676: ELSE
7677: IF FND_API.To_Boolean ( p_commit ) THEN
7671: end if;
7672:
7673: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
7674: write_to_act_log('Error after calling update_list_dets procedure', 'LIST', p_list_header_id,'HIGH');
7675: RAISE FND_API.G_EXC_ERROR;
7676: ELSE
7677: IF FND_API.To_Boolean ( p_commit ) THEN
7678: write_to_act_log('Generate_list : Commit', 'LIST', g_list_header_id,'LOW');
7679: COMMIT;
7673: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
7674: write_to_act_log('Error after calling update_list_dets procedure', 'LIST', p_list_header_id,'HIGH');
7675: RAISE FND_API.G_EXC_ERROR;
7676: ELSE
7677: IF FND_API.To_Boolean ( p_commit ) THEN
7678: write_to_act_log('Generate_list : Commit', 'LIST', g_list_header_id,'LOW');
7679: COMMIT;
7680: END IF;
7681: END IF;
7702:
7703: logger;
7704: -- Standard check of p_commit.
7705:
7706: IF FND_API.To_Boolean ( p_commit ) THEN
7707: COMMIT WORK;
7708: END IF;
7709:
7710: -- Success Message
7715: FND_MESSAGE.Set_Token('ROW', 'AMS_ListGeneration_PKG.Generate_List');
7716: FND_MSG_PUB.Add;
7717: --END IF;
7718:
7719: x_return_status := FND_API.G_RET_STS_SUCCESS;
7720: --IF (AMS_DEBUG_HIGH_ON) THEN
7721: --THEN
7722: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
7723: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.Generate_List: END');
7723: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.Generate_List: END');
7724: FND_MSG_PUB.Add;
7725: --END IF;
7726: FND_MSG_PUB.count_and_get(
7727: p_encoded => FND_API.g_false,
7728: p_count => x_msg_count,
7729: p_data => x_msg_data
7730: );
7731:
7729: p_data => x_msg_data
7730: );
7731:
7732: EXCEPTION
7733: WHEN FND_API.G_EXC_ERROR THEN
7734: -- calling logging program
7735: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7736:
7737: --
7744: main_gen_end_time = sysdate
7745: WHERE list_header_id = p_list_header_id;
7746: logger;
7747: -- Check if reset of the status is required
7748: x_return_status := FND_API.G_RET_STS_ERROR ;
7749: FND_MSG_PUB.count_and_get(
7750: p_encoded => FND_API.g_false,
7751: p_count => x_msg_count,
7752: p_data => x_msg_data);
7746: logger;
7747: -- Check if reset of the status is required
7748: x_return_status := FND_API.G_RET_STS_ERROR ;
7749: FND_MSG_PUB.count_and_get(
7750: p_encoded => FND_API.g_false,
7751: p_count => x_msg_count,
7752: p_data => x_msg_data);
7753:
7754: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7750: p_encoded => FND_API.g_false,
7751: p_count => x_msg_count,
7752: p_data => x_msg_data);
7753:
7754: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7755: -- calling logging program
7756: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7757: --
7758: UPDATE ams_list_headers_all
7766: logger;
7767: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
7768: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
7769: FND_MSG_PUB.Add;
7770: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7771: FND_MSG_PUB.count_and_get(
7772: p_encoded => FND_API.g_false,
7773: p_count => x_msg_count,
7774: p_data => x_msg_data);
7768: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
7769: FND_MSG_PUB.Add;
7770: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7771: FND_MSG_PUB.count_and_get(
7772: p_encoded => FND_API.g_false,
7773: p_count => x_msg_count,
7774: p_data => x_msg_data);
7775: ---------------Cancel List Gen Exception Begin --------------
7776: WHEN AMS_LISTGENERATION_UTIL_PKG.cancelListGen THEN
7790: FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
7791: FND_MESSAGE.Set_Token('ROW', 'AMS_ListGeneration_PKG.Generate_List');
7792: FND_MSG_PUB.Add;
7793:
7794: x_return_status := FND_API.G_RET_STS_SUCCESS;
7795:
7796: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
7797: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.Generate_List: END');
7798: FND_MSG_PUB.Add;
7797: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.Generate_List: END');
7798: FND_MSG_PUB.Add;
7799:
7800: FND_MSG_PUB.count_and_get(
7801: p_encoded => FND_API.g_false,
7802: p_count => x_msg_count,
7803: p_data => x_msg_data
7804: );
7805: ---------------Cancel List Gen Exception End --------------
7819: logger;
7820: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
7821: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
7822: FND_MSG_PUB.Add;
7823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7824: FND_MSG_PUB.count_and_get(
7825: p_encoded => FND_API.g_false,
7826: p_count => x_msg_count,
7827: p_data => x_msg_data);
7821: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
7822: FND_MSG_PUB.Add;
7823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7824: FND_MSG_PUB.count_and_get(
7825: p_encoded => FND_API.g_false,
7826: p_count => x_msg_count,
7827: p_data => x_msg_data);
7828: END GENERATE_LIST;
7829:
7828: END GENERATE_LIST;
7829:
7830: PROCEDURE create_list
7831: ( p_api_version IN NUMBER,
7832: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
7833: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7834: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7835: p_list_name in varchar2,
7836: p_list_type in varchar2,
7829:
7830: PROCEDURE create_list
7831: ( p_api_version IN NUMBER,
7832: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
7833: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7834: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7835: p_list_name in varchar2,
7836: p_list_type in varchar2,
7837: p_owner_user_id in number,
7830: PROCEDURE create_list
7831: ( p_api_version IN NUMBER,
7832: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
7833: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7834: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7835: p_list_name in varchar2,
7836: p_list_type in varchar2,
7837: p_owner_user_id in number,
7838: p_sql_string in OUT NOCOPY varchar2,
7843: x_msg_count OUT NOCOPY NUMBER,
7844: x_msg_data OUT NOCOPY VARCHAR2,
7845: x_list_header_id OUT NOCOPY NUMBER ) is
7846: l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
7847: l_init_msg_list varchar2(2000) := FND_API.G_FALSE;
7848: l_api_version number := 1.0;
7849: l_api_name constant varchar2(30) := 'Create_List';
7850: l_list_query_rec AMS_List_Query_PVT.list_query_rec_type ;
7851: l_list_query_id number ;
7882: close c_mapping_subtypes;
7883: */
7884:
7885: p_sql_string := l_select_string ||','|| substrb(p_sql_string,instr(upper(p_sql_string),'SELECT',1)+6);
7886: x_return_status := FND_API.G_RET_STS_SUCCESS;
7887: -- Standard call to check for call compatibility.
7888: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7889: p_api_version,
7890: l_api_name,
7884:
7885: p_sql_string := l_select_string ||','|| substrb(p_sql_string,instr(upper(p_sql_string),'SELECT',1)+6);
7886: x_return_status := FND_API.G_RET_STS_SUCCESS;
7887: -- Standard call to check for call compatibility.
7888: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7889: p_api_version,
7890: l_api_name,
7891: G_PKG_NAME) THEN
7892: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7888: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7889: p_api_version,
7890: l_api_name,
7891: G_PKG_NAME) THEN
7892: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7893: END IF;
7894:
7895:
7896: -- Initialize message list IF p_init_msg_list is set to TRUE.
7893: END IF;
7894:
7895:
7896: -- Initialize message list IF p_init_msg_list is set to TRUE.
7897: IF FND_API.to_Boolean( p_init_msg_list ) THEN
7898: FND_MSG_PUB.initialize;
7899: END IF;
7900:
7901: -- Debug Message
7905: FND_MSG_PUB.Add;
7906: END IF;
7907:
7908: -- Initialize API return status to success
7909: x_return_status := FND_API.G_RET_STS_SUCCESS;
7910: -- Perform the database operation
7911:
7912: -- ams_listheader_pvt.init_listheader_rec(l_list_header_rec);
7913: l_list_header_rec.list_name := p_list_name;
7928: p_listheader_rec => l_list_header_rec,
7929: x_listheader_id => x_list_header_id
7930: );
7931:
7932: if x_return_status <> FND_API.g_ret_sts_success THEN
7933: RAISE FND_API.G_EXC_ERROR;
7934: end if;
7935:
7936: FND_MESSAGE.set_name('AMS', 'bfore list query');
7929: x_listheader_id => x_list_header_id
7930: );
7931:
7932: if x_return_status <> FND_API.g_ret_sts_success THEN
7933: RAISE FND_API.G_EXC_ERROR;
7934: end if;
7935:
7936: FND_MESSAGE.set_name('AMS', 'bfore list query');
7937: FND_MSG_PUB.Add;
7950: x_msg_data => x_msg_data,
7951: p_list_query_rec => l_list_query_rec ,
7952: x_list_query_id => l_list_query_id
7953: );
7954: if x_return_status <> FND_API.g_ret_sts_success THEN
7955: RAISE FND_API.G_EXC_ERROR;
7956: end if;
7957:
7958: l_action_rec.arc_action_used_by := 'LIST';
7951: p_list_query_rec => l_list_query_rec ,
7952: x_list_query_id => l_list_query_id
7953: );
7954: if x_return_status <> FND_API.g_ret_sts_success THEN
7955: RAISE FND_API.G_EXC_ERROR;
7956: end if;
7957:
7958: l_action_rec.arc_action_used_by := 'LIST';
7959: l_action_rec.action_used_by_id := x_list_header_id ;
7972: x_msg_data => x_msg_data,
7973: p_action_rec => l_action_rec,
7974: x_action_id => l_action_id
7975: ) ;
7976: if x_return_status <> FND_API.g_ret_sts_success THEN
7977: RAISE FND_API.G_EXC_ERROR;
7978: end if;
7979: --For bug 4351391
7980: ams_list_wf.StartListBizEventProcess(p_list_header_id => x_list_header_id);
7973: p_action_rec => l_action_rec,
7974: x_action_id => l_action_id
7975: ) ;
7976: if x_return_status <> FND_API.g_ret_sts_success THEN
7977: RAISE FND_API.G_EXC_ERROR;
7978: end if;
7979: --For bug 4351391
7980: ams_list_wf.StartListBizEventProcess(p_list_header_id => x_list_header_id);
7981:
7989: x_return_status => x_return_status,
7990: x_msg_count => x_msg_count,
7991: x_msg_data => x_msg_data);
7992:
7993: if x_return_status <> FND_API.g_ret_sts_success THEN
7994: RAISE FND_API.G_EXC_ERROR;
7995: end if;
7996: -- END of API body.
7997:
7990: x_msg_count => x_msg_count,
7991: x_msg_data => x_msg_data);
7992:
7993: if x_return_status <> FND_API.g_ret_sts_success THEN
7994: RAISE FND_API.G_EXC_ERROR;
7995: end if;
7996: -- END of API body.
7997:
7998: if x_return_status <> FND_API.g_ret_sts_success THEN
7994: RAISE FND_API.G_EXC_ERROR;
7995: end if;
7996: -- END of API body.
7997:
7998: if x_return_status <> FND_API.g_ret_sts_success THEN
7999: RAISE FND_API.G_EXC_ERROR;
8000: end if;
8001: -- END of API body.
8002: --
7995: end if;
7996: -- END of API body.
7997:
7998: if x_return_status <> FND_API.g_ret_sts_success THEN
7999: RAISE FND_API.G_EXC_ERROR;
8000: end if;
8001: -- END of API body.
8002: --
8003: */
8002: --
8003: */
8004: -- Standard check of p_commit.
8005:
8006: IF FND_API.To_Boolean ( p_commit ) THEN
8007: COMMIT WORK;
8008: END IF;
8009:
8010: -- Success Message
8022: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.create_list: END');
8023: FND_MSG_PUB.Add;
8024: --END IF;
8025: FND_MSG_PUB.count_and_get(
8026: p_encoded => FND_API.g_false,
8027: p_count => x_msg_count,
8028: p_data => x_msg_data
8029: );
8030:
8028: p_data => x_msg_data
8029: );
8030:
8031: EXCEPTION
8032: WHEN FND_API.G_EXC_ERROR THEN
8033: -- Check if reset of the status is required
8034: x_return_status := FND_API.G_RET_STS_ERROR ;
8035: FND_MSG_PUB.count_and_get(
8036: p_encoded => FND_API.g_false,
8030:
8031: EXCEPTION
8032: WHEN FND_API.G_EXC_ERROR THEN
8033: -- Check if reset of the status is required
8034: x_return_status := FND_API.G_RET_STS_ERROR ;
8035: FND_MSG_PUB.count_and_get(
8036: p_encoded => FND_API.g_false,
8037: p_count => x_msg_count,
8038: p_data => x_msg_data);
8032: WHEN FND_API.G_EXC_ERROR THEN
8033: -- Check if reset of the status is required
8034: x_return_status := FND_API.G_RET_STS_ERROR ;
8035: FND_MSG_PUB.count_and_get(
8036: p_encoded => FND_API.g_false,
8037: p_count => x_msg_count,
8038: p_data => x_msg_data);
8039:
8040: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8036: p_encoded => FND_API.g_false,
8037: p_count => x_msg_count,
8038: p_data => x_msg_data);
8039:
8040: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8041: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8042: FND_MSG_PUB.count_and_get(
8043: p_encoded => FND_API.g_false,
8044: p_count => x_msg_count,
8037: p_count => x_msg_count,
8038: p_data => x_msg_data);
8039:
8040: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8041: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8042: FND_MSG_PUB.count_and_get(
8043: p_encoded => FND_API.g_false,
8044: p_count => x_msg_count,
8045: p_data => x_msg_data);
8039:
8040: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8041: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8042: FND_MSG_PUB.count_and_get(
8043: p_encoded => FND_API.g_false,
8044: p_count => x_msg_count,
8045: p_data => x_msg_data);
8046:
8047: WHEN OTHERS THEN
8044: p_count => x_msg_count,
8045: p_data => x_msg_data);
8046:
8047: WHEN OTHERS THEN
8048: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8049: FND_MSG_PUB.count_and_get(
8050: p_encoded => FND_API.g_false,
8051: p_count => x_msg_count,
8052: p_data => x_msg_data);
8046:
8047: WHEN OTHERS THEN
8048: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8049: FND_MSG_PUB.count_and_get(
8050: p_encoded => FND_API.g_false,
8051: p_count => x_msg_count,
8052: p_data => x_msg_data);
8053: END CREATE_LIST;
8054:
8053: END CREATE_LIST;
8054:
8055: PROCEDURE create_import_list
8056: ( p_api_version IN NUMBER,
8057: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8058: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8059: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8060: p_owner_user_id in number,
8061: p_imp_list_header_id in number,
8054:
8055: PROCEDURE create_import_list
8056: ( p_api_version IN NUMBER,
8057: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8058: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8059: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8060: p_owner_user_id in number,
8061: p_imp_list_header_id in number,
8062: x_return_status OUT NOCOPY VARCHAR2,
8055: PROCEDURE create_import_list
8056: ( p_api_version IN NUMBER,
8057: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8058: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8059: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8060: p_owner_user_id in number,
8061: p_imp_list_header_id in number,
8062: x_return_status OUT NOCOPY VARCHAR2,
8063: x_msg_count OUT NOCOPY NUMBER,
8064: x_msg_data OUT NOCOPY VARCHAR2,
8065: x_list_header_id OUT NOCOPY NUMBER ,
8066: p_list_name in VARCHAR2 ) is
8067: l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
8068: l_init_msg_list varchar2(2000) := FND_API.G_FALSE;
8069: l_api_version number := 1.0;
8070: l_api_name constant varchar2(30) := 'Create_import_List';
8071: l_action_rec AMS_ListAction_PVT.action_rec_type ;
8072: l_action_id number;
8104: else
8105: l_import_list_name := p_list_name ;
8106: end if;
8107:
8108: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8109: p_api_version,
8110: l_api_name,
8111: G_PKG_NAME) THEN
8112: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8108: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8109: p_api_version,
8110: l_api_name,
8111: G_PKG_NAME) THEN
8112: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8113: END IF;
8114:
8115:
8116: -- Initialize message list IF p_init_msg_list is set to TRUE.
8113: END IF;
8114:
8115:
8116: -- Initialize message list IF p_init_msg_list is set to TRUE.
8117: IF FND_API.to_Boolean( p_init_msg_list ) THEN
8118: FND_MSG_PUB.initialize;
8119: END IF;
8120:
8121: -- Debug Message
8124: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.cerate_list: Start', TRUE);
8125: FND_MSG_PUB.Add;
8126: END IF;
8127: -- Initialize API return status to success
8128: x_return_status := FND_API.G_RET_STS_SUCCESS;
8129: -- Perform the database operation
8130:
8131: -- ams_listheader_pvt.init_listheader_rec(l_list_header_rec);
8132: l_list_header_rec.list_name := l_import_list_name ;
8147: p_listheader_rec => l_list_header_rec,
8148: x_listheader_id => x_list_header_id
8149: );
8150:
8151: if x_return_status <> FND_API.g_ret_sts_success THEN
8152: RAISE FND_API.G_EXC_ERROR;
8153: end if;
8154:
8155: l_action_rec.arc_action_used_by := 'LIST';
8148: x_listheader_id => x_list_header_id
8149: );
8150:
8151: if x_return_status <> FND_API.g_ret_sts_success THEN
8152: RAISE FND_API.G_EXC_ERROR;
8153: end if;
8154:
8155: l_action_rec.arc_action_used_by := 'LIST';
8156: l_action_rec.action_used_by_id := x_list_header_id ;
8171: x_action_id => l_action_id
8172: ) ;
8173: FND_MESSAGE.set_name('AMS','after list action->'|| l_action_id|| '<-');
8174: FND_MSG_PUB.Add;
8175: if x_return_status <> FND_API.g_ret_sts_success THEN
8176: RAISE FND_API.G_EXC_ERROR;
8177: end if;
8178:
8179: GENERATE_LIST
8172: ) ;
8173: FND_MESSAGE.set_name('AMS','after list action->'|| l_action_id|| '<-');
8174: FND_MSG_PUB.Add;
8175: if x_return_status <> FND_API.g_ret_sts_success THEN
8176: RAISE FND_API.G_EXC_ERROR;
8177: end if;
8178:
8179: GENERATE_LIST
8180: ( p_api_version => 1.0,
8185: x_return_status => x_return_status,
8186: x_msg_count => x_msg_count,
8187: x_msg_data => x_msg_data);
8188:
8189: if x_return_status <> FND_API.g_ret_sts_success THEN
8190: RAISE FND_API.G_EXC_ERROR;
8191: end if;
8192: -- END of API body.
8193: --
8186: x_msg_count => x_msg_count,
8187: x_msg_data => x_msg_data);
8188:
8189: if x_return_status <> FND_API.g_ret_sts_success THEN
8190: RAISE FND_API.G_EXC_ERROR;
8191: end if;
8192: -- END of API body.
8193: --
8194:
8193: --
8194:
8195: -- Standard check of p_commit.
8196:
8197: IF FND_API.To_Boolean ( p_commit ) THEN
8198: COMMIT WORK;
8199: END IF;
8200:
8201: -- Success Message
8214: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.create_list: END');
8215: FND_MSG_PUB.Add;
8216: --END IF;
8217: FND_MSG_PUB.count_and_get(
8218: p_encoded => FND_API.g_false,
8219: p_count => x_msg_count,
8220: p_data => x_msg_data
8221: );
8222:
8220: p_data => x_msg_data
8221: );
8222:
8223: EXCEPTION
8224: WHEN FND_API.G_EXC_ERROR THEN
8225: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8226: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8227: FND_MSG_PUB.Add;
8228: -- Check if reset of the status is required
8225: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8226: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8227: FND_MSG_PUB.Add;
8228: -- Check if reset of the status is required
8229: x_return_status := FND_API.G_RET_STS_ERROR ;
8230: FND_MSG_PUB.count_and_get(
8231: p_encoded => FND_API.g_false,
8232: p_count => x_msg_count,
8233: p_data => x_msg_data);
8227: FND_MSG_PUB.Add;
8228: -- Check if reset of the status is required
8229: x_return_status := FND_API.G_RET_STS_ERROR ;
8230: FND_MSG_PUB.count_and_get(
8231: p_encoded => FND_API.g_false,
8232: p_count => x_msg_count,
8233: p_data => x_msg_data);
8234:
8235: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8231: p_encoded => FND_API.g_false,
8232: p_count => x_msg_count,
8233: p_data => x_msg_data);
8234:
8235: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8236: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8237: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8238: FND_MSG_PUB.Add;
8239: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8235: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8236: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8237: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8238: FND_MSG_PUB.Add;
8239: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8240: FND_MSG_PUB.count_and_get(
8241: p_encoded => FND_API.g_false,
8242: p_count => x_msg_count,
8243: p_data => x_msg_data);
8237: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8238: FND_MSG_PUB.Add;
8239: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8240: FND_MSG_PUB.count_and_get(
8241: p_encoded => FND_API.g_false,
8242: p_count => x_msg_count,
8243: p_data => x_msg_data);
8244:
8245: WHEN OTHERS THEN
8245: WHEN OTHERS THEN
8246: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8247: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8248: FND_MSG_PUB.Add;
8249: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8250: FND_MSG_PUB.count_and_get(
8251: p_encoded => FND_API.g_false,
8252: p_count => x_msg_count,
8253: p_data => x_msg_data);
8247: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8248: FND_MSG_PUB.Add;
8249: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8250: FND_MSG_PUB.count_and_get(
8251: p_encoded => FND_API.g_false,
8252: p_count => x_msg_count,
8253: p_data => x_msg_data);
8254: END CREATE_import_LIST;
8255:
8254: END CREATE_import_LIST;
8255:
8256: PROCEDURE create_list_from_query
8257: ( p_api_version IN NUMBER,
8258: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8261: p_list_name in varchar2,
8262: p_list_type in varchar2,
8255:
8256: PROCEDURE create_list_from_query
8257: ( p_api_version IN NUMBER,
8258: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8261: p_list_name in varchar2,
8262: p_list_type in varchar2,
8263: p_owner_user_id in number,
8256: PROCEDURE create_list_from_query
8257: ( p_api_version IN NUMBER,
8258: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8261: p_list_name in varchar2,
8262: p_list_type in varchar2,
8263: p_owner_user_id in number,
8264: p_list_header_id in number,
8270: x_msg_count OUT NOCOPY NUMBER,
8271: x_msg_data OUT NOCOPY VARCHAR2
8272: ) is
8273: l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
8274: l_init_msg_list varchar2(2000) := FND_API.G_FALSE;
8275: l_api_version number := 1.0;
8276: l_api_name constant varchar2(30) := 'Create_List';
8277: l_list_query_rec_tbl AMS_List_Query_PVT.list_query_rec_type_tbl ;
8278: l_list_query_id number ;
8281:
8282: l_master_type_id number;
8283: l_source_type_code varchar2(30);
8284: BEGIN
8285: x_return_status := FND_API.G_RET_STS_SUCCESS;
8286: -- Standard call to check for call compatibility.
8287: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8288: p_api_version,
8289: l_api_name,
8283: l_source_type_code varchar2(30);
8284: BEGIN
8285: x_return_status := FND_API.G_RET_STS_SUCCESS;
8286: -- Standard call to check for call compatibility.
8287: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8288: p_api_version,
8289: l_api_name,
8290: G_PKG_NAME) THEN
8291: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8287: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8288: p_api_version,
8289: l_api_name,
8290: G_PKG_NAME) THEN
8291: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8292: END IF;
8293:
8294:
8295: -- Initialize message list IF p_init_msg_list is set to TRUE.
8292: END IF;
8293:
8294:
8295: -- Initialize message list IF p_init_msg_list is set to TRUE.
8296: IF FND_API.to_Boolean( p_init_msg_list ) THEN
8297: FND_MSG_PUB.initialize;
8298: END IF;
8299:
8300: -- Debug Message
8304: FND_MSG_PUB.Add;
8305: END IF;
8306:
8307: -- Initialize API return status to success
8308: x_return_status := FND_API.G_RET_STS_SUCCESS;
8309: -- Perform the database operation
8310:
8311: l_list_query_rec_tbl.name := p_list_name ||'_'|| p_list_header_id;
8312: -- l_list_query_rec_tbl.sql_string := p_sql_string ;
8326: x_parent_list_query_id => l_list_query_id
8327: );
8328:
8329:
8330: if x_return_status <> FND_API.g_ret_sts_success THEN
8331: RAISE FND_API.G_EXC_ERROR;
8332: end if;
8333:
8334: l_action_rec.arc_action_used_by := 'LIST';
8327: );
8328:
8329:
8330: if x_return_status <> FND_API.g_ret_sts_success THEN
8331: RAISE FND_API.G_EXC_ERROR;
8332: end if;
8333:
8334: l_action_rec.arc_action_used_by := 'LIST';
8335: l_action_rec.action_used_by_id := p_list_header_id ;
8349: x_msg_data => x_msg_data,
8350: p_action_rec => l_action_rec,
8351: x_action_id => l_action_id
8352: ) ;
8353: if x_return_status <> FND_API.g_ret_sts_success THEN
8354: RAISE FND_API.G_EXC_ERROR;
8355: end if;
8356:
8357: GENERATE_LIST
8350: p_action_rec => l_action_rec,
8351: x_action_id => l_action_id
8352: ) ;
8353: if x_return_status <> FND_API.g_ret_sts_success THEN
8354: RAISE FND_API.G_EXC_ERROR;
8355: end if;
8356:
8357: GENERATE_LIST
8358: ( p_api_version => 1.0,
8363: x_return_status => x_return_status,
8364: x_msg_count => x_msg_count,
8365: x_msg_data => x_msg_data);
8366:
8367: if x_return_status <> FND_API.g_ret_sts_success THEN
8368: RAISE FND_API.G_EXC_ERROR;
8369: end if;
8370: -- END of API body.
8371: --
8364: x_msg_count => x_msg_count,
8365: x_msg_data => x_msg_data);
8366:
8367: if x_return_status <> FND_API.g_ret_sts_success THEN
8368: RAISE FND_API.G_EXC_ERROR;
8369: end if;
8370: -- END of API body.
8371: --
8372:
8371: --
8372:
8373: -- Standard check of p_commit.
8374:
8375: IF FND_API.To_Boolean ( p_commit ) THEN
8376: COMMIT WORK;
8377: END IF;
8378:
8379: -- Success Message
8392: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.create_list: END');
8393: FND_MSG_PUB.Add;
8394: --END IF;
8395: FND_MSG_PUB.count_and_get(
8396: p_encoded => FND_API.g_false,
8397: p_count => x_msg_count,
8398: p_data => x_msg_data
8399: );
8400:
8398: p_data => x_msg_data
8399: );
8400:
8401: EXCEPTION
8402: WHEN FND_API.G_EXC_ERROR THEN
8403: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8404: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8405: FND_MSG_PUB.Add;
8406: -- Check if reset of the status is required
8403: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8404: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8405: FND_MSG_PUB.Add;
8406: -- Check if reset of the status is required
8407: x_return_status := FND_API.G_RET_STS_ERROR ;
8408: FND_MSG_PUB.count_and_get(
8409: p_encoded => FND_API.g_false,
8410: p_count => x_msg_count,
8411: p_data => x_msg_data);
8405: FND_MSG_PUB.Add;
8406: -- Check if reset of the status is required
8407: x_return_status := FND_API.G_RET_STS_ERROR ;
8408: FND_MSG_PUB.count_and_get(
8409: p_encoded => FND_API.g_false,
8410: p_count => x_msg_count,
8411: p_data => x_msg_data);
8412:
8413: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8409: p_encoded => FND_API.g_false,
8410: p_count => x_msg_count,
8411: p_data => x_msg_data);
8412:
8413: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8414: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8415: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8416: FND_MSG_PUB.Add;
8417: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8413: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8414: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8415: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8416: FND_MSG_PUB.Add;
8417: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8418: FND_MSG_PUB.count_and_get(
8419: p_encoded => FND_API.g_false,
8420: p_count => x_msg_count,
8421: p_data => x_msg_data);
8415: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8416: FND_MSG_PUB.Add;
8417: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8418: FND_MSG_PUB.count_and_get(
8419: p_encoded => FND_API.g_false,
8420: p_count => x_msg_count,
8421: p_data => x_msg_data);
8422:
8423: WHEN OTHERS THEN
8423: WHEN OTHERS THEN
8424: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8425: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8426: FND_MSG_PUB.Add;
8427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8428: FND_MSG_PUB.count_and_get(
8429: p_encoded => FND_API.g_false,
8430: p_count => x_msg_count,
8431: p_data => x_msg_data);
8425: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8426: FND_MSG_PUB.Add;
8427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8428: FND_MSG_PUB.count_and_get(
8429: p_encoded => FND_API.g_false,
8430: p_count => x_msg_count,
8431: p_data => x_msg_data);
8432: END CREATE_LIST_FROM_QUERY ;
8433:
8432: END CREATE_LIST_FROM_QUERY ;
8433:
8434: PROCEDURE create_list_from_query
8435: ( p_api_version IN NUMBER,
8436: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8437: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8438: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8439: p_list_name in varchar2,
8440: p_list_type in varchar2,
8433:
8434: PROCEDURE create_list_from_query
8435: ( p_api_version IN NUMBER,
8436: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8437: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8438: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8439: p_list_name in varchar2,
8440: p_list_type in varchar2,
8441: p_owner_user_id in number,
8434: PROCEDURE create_list_from_query
8435: ( p_api_version IN NUMBER,
8436: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
8437: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8438: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8439: p_list_name in varchar2,
8440: p_list_type in varchar2,
8441: p_owner_user_id in number,
8442: p_list_header_id in number,
8449: x_msg_count OUT NOCOPY NUMBER,
8450: x_msg_data OUT NOCOPY VARCHAR2
8451: ) is
8452: l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
8453: l_init_msg_list varchar2(2000) := FND_API.G_FALSE;
8454: l_api_version number := 1.0;
8455: l_api_name constant varchar2(30) := 'Create_List';
8456: l_list_query_rec_tbl AMS_List_Query_PVT.list_query_rec_type_tbl ;
8457: l_list_query_id number ;
8460:
8461: l_master_type_id number;
8462: l_source_type_code varchar2(30);
8463: BEGIN
8464: x_return_status := FND_API.G_RET_STS_SUCCESS;
8465: -- Standard call to check for call compatibility.
8466: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8467: p_api_version,
8468: l_api_name,
8462: l_source_type_code varchar2(30);
8463: BEGIN
8464: x_return_status := FND_API.G_RET_STS_SUCCESS;
8465: -- Standard call to check for call compatibility.
8466: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8467: p_api_version,
8468: l_api_name,
8469: G_PKG_NAME) THEN
8470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8466: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8467: p_api_version,
8468: l_api_name,
8469: G_PKG_NAME) THEN
8470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8471: END IF;
8472:
8473:
8474: -- Initialize message list IF p_init_msg_list is set to TRUE.
8471: END IF;
8472:
8473:
8474: -- Initialize message list IF p_init_msg_list is set to TRUE.
8475: IF FND_API.to_Boolean( p_init_msg_list ) THEN
8476: FND_MSG_PUB.initialize;
8477: END IF;
8478:
8479: -- Debug Message
8483: FND_MSG_PUB.Add;
8484: END IF;
8485:
8486: -- Initialize API return status to success
8487: x_return_status := FND_API.G_RET_STS_SUCCESS;
8488: -- Perform the database operation
8489:
8490: l_list_query_rec_tbl.name := p_list_name ||'_'|| p_list_header_id;
8491: -- l_list_query_rec_tbl.sql_string := p_sql_string ;
8507: x_parent_list_query_id => l_list_query_id
8508: );
8509:
8510:
8511: if x_return_status <> FND_API.g_ret_sts_success THEN
8512: RAISE FND_API.G_EXC_ERROR;
8513: end if;
8514:
8515: l_action_rec.arc_action_used_by := 'LIST';
8508: );
8509:
8510:
8511: if x_return_status <> FND_API.g_ret_sts_success THEN
8512: RAISE FND_API.G_EXC_ERROR;
8513: end if;
8514:
8515: l_action_rec.arc_action_used_by := 'LIST';
8516: l_action_rec.action_used_by_id := p_list_header_id ;
8530: x_msg_data => x_msg_data,
8531: p_action_rec => l_action_rec,
8532: x_action_id => l_action_id
8533: ) ;
8534: if x_return_status <> FND_API.g_ret_sts_success THEN
8535: RAISE FND_API.G_EXC_ERROR;
8536: end if;
8537:
8538: GENERATE_LIST
8531: p_action_rec => l_action_rec,
8532: x_action_id => l_action_id
8533: ) ;
8534: if x_return_status <> FND_API.g_ret_sts_success THEN
8535: RAISE FND_API.G_EXC_ERROR;
8536: end if;
8537:
8538: GENERATE_LIST
8539: ( p_api_version => 1.0,
8544: x_return_status => x_return_status,
8545: x_msg_count => x_msg_count,
8546: x_msg_data => x_msg_data);
8547:
8548: if x_return_status <> FND_API.g_ret_sts_success THEN
8549: RAISE FND_API.G_EXC_ERROR;
8550: end if;
8551: -- END of API body.
8552: --
8545: x_msg_count => x_msg_count,
8546: x_msg_data => x_msg_data);
8547:
8548: if x_return_status <> FND_API.g_ret_sts_success THEN
8549: RAISE FND_API.G_EXC_ERROR;
8550: end if;
8551: -- END of API body.
8552: --
8553:
8552: --
8553:
8554: -- Standard check of p_commit.
8555:
8556: IF FND_API.To_Boolean ( p_commit ) THEN
8557: COMMIT WORK;
8558: END IF;
8559:
8560: -- Success Message
8573: FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.create_list: END');
8574: FND_MSG_PUB.Add;
8575: --END IF;
8576: FND_MSG_PUB.count_and_get(
8577: p_encoded => FND_API.g_false,
8578: p_count => x_msg_count,
8579: p_data => x_msg_data
8580: );
8581:
8579: p_data => x_msg_data
8580: );
8581:
8582: EXCEPTION
8583: WHEN FND_API.G_EXC_ERROR THEN
8584: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8585: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8586: FND_MSG_PUB.Add;
8587: -- Check if reset of the status is required
8584: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8585: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8586: FND_MSG_PUB.Add;
8587: -- Check if reset of the status is required
8588: x_return_status := FND_API.G_RET_STS_ERROR ;
8589: FND_MSG_PUB.count_and_get(
8590: p_encoded => FND_API.g_false,
8591: p_count => x_msg_count,
8592: p_data => x_msg_data);
8586: FND_MSG_PUB.Add;
8587: -- Check if reset of the status is required
8588: x_return_status := FND_API.G_RET_STS_ERROR ;
8589: FND_MSG_PUB.count_and_get(
8590: p_encoded => FND_API.g_false,
8591: p_count => x_msg_count,
8592: p_data => x_msg_data);
8593:
8594: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8590: p_encoded => FND_API.g_false,
8591: p_count => x_msg_count,
8592: p_data => x_msg_data);
8593:
8594: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8595: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8596: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8597: FND_MSG_PUB.Add;
8598: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8594: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8595: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8596: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8597: FND_MSG_PUB.Add;
8598: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8599: FND_MSG_PUB.count_and_get(
8600: p_encoded => FND_API.g_false,
8601: p_count => x_msg_count,
8602: p_data => x_msg_data);
8596: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8597: FND_MSG_PUB.Add;
8598: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8599: FND_MSG_PUB.count_and_get(
8600: p_encoded => FND_API.g_false,
8601: p_count => x_msg_count,
8602: p_data => x_msg_data);
8603:
8604: WHEN OTHERS THEN
8604: WHEN OTHERS THEN
8605: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
8606: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8607: FND_MSG_PUB.Add;
8608: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8609: FND_MSG_PUB.count_and_get(
8610: p_encoded => FND_API.g_false,
8611: p_count => x_msg_count,
8612: p_data => x_msg_data);
8606: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
8607: FND_MSG_PUB.Add;
8608: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8609: FND_MSG_PUB.count_and_get(
8610: p_encoded => FND_API.g_false,
8611: p_count => x_msg_count,
8612: p_data => x_msg_data);
8613: END CREATE_LIST_FROM_QUERY ;
8614: -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
8855: x_msg_data => x_msg_data ,
8856: x_return_status => x_return_status
8857: );
8858:
8859: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
8860: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
8861: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
8862: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
8863: end if;
8860: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
8861: write_to_act_log('Error in Cancel List generation', 'LIST', g_list_header_id,'HIGH');
8862: write_to_act_log('Error while executing Cancel List generation '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
8863: end if;
8864: RAISE FND_API.G_EXC_ERROR;
8865: ELSE
8866: write_to_act_log('Success in Cancel List generation', 'LIST', g_list_header_id,'LOW');
8867: END IF;
8868:
8952: end loop;
8953: end if;
8954:
8955:
8956: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
8957: RAISE FND_API.G_EXC_ERROR;
8958: END IF;
8959: -- end Of Dynamic Procedure
8960: l_join_string :='';
8953: end if;
8954:
8955:
8956: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
8957: RAISE FND_API.G_EXC_ERROR;
8958: END IF;
8959: -- end Of Dynamic Procedure
8960: l_join_string :='';
8961: write_to_act_log('Action on this selection completed. ', 'LIST', g_list_header_id,'LOW');
9123: no_of_rows_in_ctrl_group = null
9124: where list_header_id = g_list_header_id;
9125: update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
9126: write_to_act_log('Aborting list generation ', 'LIST', g_list_header_id,'HIGH');
9127: x_return_status := FND_API.g_ret_sts_error; --Gen return status should go to error.
9128: return;
9129: --
9130: else
9131: write_to_act_log('Target group generated in remote instance', 'LIST', g_list_header_id,'HIGH');
9317: no_of_rows_in_ctrl_group = null
9318: where list_header_id = g_list_header_id;
9319: update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
9320: write_to_act_log('Aborting list generation ', 'LIST', g_list_header_id,'HIGH');
9321: x_return_status := FND_API.g_ret_sts_error; --Gen return status should go to error.
9322: return;
9323: --
9324: else
9325: write_to_act_log('Target group generated successfully in remote instance', 'LIST', g_list_header_id,'HIGH');
9336: IF(c_action_dets%ISOPEN)THEN
9337: CLOSE c_action_dets;
9338: END IF;
9339:
9340: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9341:
9342: write_to_act_log('executing process_tar_actions - user action to cancel list generation detected ', 'LIST', g_list_header_id,'HIGH');
9343: -- Got to raise the exception again because Listgen has to end generation.
9344: raise AMS_LISTGENERATION_UTIL_PKG.cancelListGen;
9342: write_to_act_log('executing process_tar_actions - user action to cancel list generation detected ', 'LIST', g_list_header_id,'HIGH');
9343: -- Got to raise the exception again because Listgen has to end generation.
9344: raise AMS_LISTGENERATION_UTIL_PKG.cancelListGen;
9345:
9346: WHEN FND_API.G_EXC_ERROR THEN
9347: IF(c_action_dets%ISOPEN)THEN
9348: CLOSE c_action_dets;
9349: END IF;
9350: -- Check if reset of the status is required
9348: CLOSE c_action_dets;
9349: END IF;
9350: -- Check if reset of the status is required
9351: write_to_act_log('Error while executing procedure process_tar_actions '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
9352: x_return_status := FND_API.G_RET_STS_ERROR ;
9353: FND_MSG_PUB.count_and_get(
9354: p_encoded => FND_API.g_false,
9355: p_count => x_msg_count,
9356: p_data => x_msg_data);
9350: -- Check if reset of the status is required
9351: write_to_act_log('Error while executing procedure process_tar_actions '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
9352: x_return_status := FND_API.G_RET_STS_ERROR ;
9353: FND_MSG_PUB.count_and_get(
9354: p_encoded => FND_API.g_false,
9355: p_count => x_msg_count,
9356: p_data => x_msg_data);
9357:
9358: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
9354: p_encoded => FND_API.g_false,
9355: p_count => x_msg_count,
9356: p_data => x_msg_data);
9357:
9358: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
9359: IF(c_action_dets%ISOPEN)THEN
9360: CLOSE c_action_dets;
9361: END IF;
9362: write_to_act_log('Error while executing procedure process_tar_actions '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
9362: write_to_act_log('Error while executing procedure process_tar_actions '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
9363: FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
9364: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
9365: FND_MSG_PUB.Add;
9366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9367: FND_MSG_PUB.count_and_get(
9368: p_encoded => FND_API.g_false,
9369: p_count => x_msg_count,
9370: p_data => x_msg_data);
9364: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
9365: FND_MSG_PUB.Add;
9366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9367: FND_MSG_PUB.count_and_get(
9368: p_encoded => FND_API.g_false,
9369: p_count => x_msg_count,
9370: p_data => x_msg_data);
9371:
9372: WHEN OTHERS THEN
9376: write_to_act_log('Error while executing procedure process_tar_actions '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
9377: FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
9378: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
9379: FND_MSG_PUB.Add;
9380: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9381: FND_MSG_PUB.count_and_get(
9382: p_encoded => FND_API.g_false,
9383: p_count => x_msg_count,
9384: p_data => x_msg_data);
9378: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
9379: FND_MSG_PUB.Add;
9380: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9381: FND_MSG_PUB.count_and_get(
9382: p_encoded => FND_API.g_false,
9383: p_count => x_msg_count,
9384: p_data => x_msg_data);
9385: END process_tar_actions;
9386:
9387: -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
9388:
9389: PROCEDURE GENERATE_TARGET_GROUP
9390: ( p_api_version IN NUMBER,
9391: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
9392: p_commit IN VARCHAR2 := FND_API.G_FALSE,
9393: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
9394: p_list_header_id IN NUMBER,
9395: x_return_status OUT NOCOPY VARCHAR2,
9388:
9389: PROCEDURE GENERATE_TARGET_GROUP
9390: ( p_api_version IN NUMBER,
9391: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
9392: p_commit IN VARCHAR2 := FND_API.G_FALSE,
9393: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
9394: p_list_header_id IN NUMBER,
9395: x_return_status OUT NOCOPY VARCHAR2,
9396: x_msg_count OUT NOCOPY NUMBER,
9389: PROCEDURE GENERATE_TARGET_GROUP
9390: ( p_api_version IN NUMBER,
9391: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
9392: p_commit IN VARCHAR2 := FND_API.G_FALSE,
9393: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
9394: p_list_header_id IN NUMBER,
9395: x_return_status OUT NOCOPY VARCHAR2,
9396: x_msg_count OUT NOCOPY NUMBER,
9397: x_msg_data OUT NOCOPY VARCHAR2) IS
9641: ctrl_status_code = 'DRAFT',
9642: no_of_rows_in_ctrl_group = null
9643: WHERE list_header_id = p_list_header_id;
9644: FND_MSG_PUB.count_and_get(
9645: p_encoded => FND_API.g_false,
9646: p_count => x_msg_count,
9647: p_data => x_msg_data
9648: );
9649: x_return_status := FND_API.g_ret_sts_error;
9645: p_encoded => FND_API.g_false,
9646: p_count => x_msg_count,
9647: p_data => x_msg_data
9648: );
9649: x_return_status := FND_API.g_ret_sts_error;
9650: --
9651: logger;
9652: RETURN;
9653: END IF;
9684: WHERE list_header_id = p_list_header_id;
9685: -- calling logging program
9686: logger;
9687: --
9688: IF FND_API.To_Boolean ( p_commit ) THEN
9689: COMMIT WORK;
9690: END IF;
9691: --Modified by bmuthukr. Bug # 4083665
9692: x_return_status := FND_API.g_ret_sts_error;
9688: IF FND_API.To_Boolean ( p_commit ) THEN
9689: COMMIT WORK;
9690: END IF;
9691: --Modified by bmuthukr. Bug # 4083665
9692: x_return_status := FND_API.g_ret_sts_error;
9693: --
9694: RETURN;
9695: end if;
9696:
9730: ctrl_status_code = 'DRAFT',
9731: no_of_rows_in_ctrl_group = null
9732: WHERE list_header_id = g_list_header_id;
9733: --Modified by bmuthukr. Bug # 4083665
9734: x_return_status := FND_API.g_ret_sts_error;
9735: --
9736: logger;
9737: RETURN;
9738: END IF;
9736: logger;
9737: RETURN;
9738: END IF;
9739:
9740: x_return_status := FND_API.G_RET_STS_SUCCESS;
9741:
9742: -- Standard call to check for call compatibility.
9743: IF NOT FND_API.Compatible_API_Call ( l_api_version,
9744: p_api_version,
9739:
9740: x_return_status := FND_API.G_RET_STS_SUCCESS;
9741:
9742: -- Standard call to check for call compatibility.
9743: IF NOT FND_API.Compatible_API_Call ( l_api_version,
9744: p_api_version,
9745: l_api_name,
9746: G_PKG_NAME)
9747: THEN
9744: p_api_version,
9745: l_api_name,
9746: G_PKG_NAME)
9747: THEN
9748: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9749: END IF;
9750:
9751: IF FND_API.to_Boolean( p_init_msg_list ) THEN
9752: FND_MSG_PUB.initialize;
9747: THEN
9748: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9749: END IF;
9750:
9751: IF FND_API.to_Boolean( p_init_msg_list ) THEN
9752: FND_MSG_PUB.initialize;
9753: END IF;
9754:
9755: -- Checking if Debug is set. If debug is set then log debugging message
9760: FND_MSG_PUB.Add;
9761: END IF;
9762:
9763: -- Initialize API return status to success
9764: x_return_status := FND_API.G_RET_STS_SUCCESS;
9765:
9766: l_error_position := '<- Init List->';
9767: --These values need to be fetched before calling initialize_list. Changes done by bmuthukr.
9768:
9823: else
9824: write_to_act_log('Target group initialized.' ,'LIST',p_list_header_id,'HIGH');
9825: end if;
9826:
9827: IF x_return_status = FND_API.g_ret_sts_error THEN
9828: RAISE FND_API.g_exc_error;
9829: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9830: RAISE FND_API.g_exc_unexpected_error;
9831: END IF;
9824: write_to_act_log('Target group initialized.' ,'LIST',p_list_header_id,'HIGH');
9825: end if;
9826:
9827: IF x_return_status = FND_API.g_ret_sts_error THEN
9828: RAISE FND_API.g_exc_error;
9829: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9830: RAISE FND_API.g_exc_unexpected_error;
9831: END IF;
9832: -- -------------------------------------------------------------------------
9825: end if;
9826:
9827: IF x_return_status = FND_API.g_ret_sts_error THEN
9828: RAISE FND_API.g_exc_error;
9829: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9830: RAISE FND_API.g_exc_unexpected_error;
9831: END IF;
9832: -- -------------------------------------------------------------------------
9833: if g_remote_list = 'Y' then
9826:
9827: IF x_return_status = FND_API.g_ret_sts_error THEN
9828: RAISE FND_API.g_exc_error;
9829: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9830: RAISE FND_API.g_exc_unexpected_error;
9831: END IF;
9832: -- -------------------------------------------------------------------------
9833: if g_remote_list = 'Y' then
9834: remote_list_gen(p_list_header_id => p_list_header_id,
9862: status_date = sysdate,
9863: last_update_date = sysdate,
9864: main_gen_end_time = sysdate
9865: WHERE list_header_id = g_list_header_id;
9866: x_return_status := FND_API.g_ret_sts_error;
9867: logger;
9868: RETURN;
9869: end if;
9870: end if;
9925: write_to_act_log('Target group is generated in UPDATE mode in local instance.', 'LIST', g_list_header_id,'HIGH');
9926: GET_LIST_ENTRY_DATA(
9927: p_list_header_id =>l_listheader_rec.list_header_id,
9928: x_return_status => x_return_status);
9929: IF x_return_status = FND_API.g_ret_sts_error THEN
9930: RAISE FND_API.g_exc_error;
9931: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9932: RAISE FND_API.g_exc_unexpected_error;
9933: END IF;
9926: GET_LIST_ENTRY_DATA(
9927: p_list_header_id =>l_listheader_rec.list_header_id,
9928: x_return_status => x_return_status);
9929: IF x_return_status = FND_API.g_ret_sts_error THEN
9930: RAISE FND_API.g_exc_error;
9931: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9932: RAISE FND_API.g_exc_unexpected_error;
9933: END IF;
9934: --end if;
9927: p_list_header_id =>l_listheader_rec.list_header_id,
9928: x_return_status => x_return_status);
9929: IF x_return_status = FND_API.g_ret_sts_error THEN
9930: RAISE FND_API.g_exc_error;
9931: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9932: RAISE FND_API.g_exc_unexpected_error;
9933: END IF;
9934: --end if;
9935: -- END IF;
9928: x_return_status => x_return_status);
9929: IF x_return_status = FND_API.g_ret_sts_error THEN
9930: RAISE FND_API.g_exc_error;
9931: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9932: RAISE FND_API.g_exc_unexpected_error;
9933: END IF;
9934: --end if;
9935: -- END IF;
9936: else
9955: write_to_act_log('Error in generating list', 'LIST', g_list_header_id,'HIGH');
9956: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9957: end if;
9958:
9959: IF x_return_status = FND_API.g_ret_sts_error THEN
9960: RAISE FND_API.g_exc_error;
9961: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9962: RAISE FND_API.g_exc_unexpected_error;
9963: END IF;
9956: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9957: end if;
9958:
9959: IF x_return_status = FND_API.g_ret_sts_error THEN
9960: RAISE FND_API.g_exc_error;
9961: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9962: RAISE FND_API.g_exc_unexpected_error;
9963: END IF;
9964:
9957: end if;
9958:
9959: IF x_return_status = FND_API.g_ret_sts_error THEN
9960: RAISE FND_API.g_exc_error;
9961: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9962: RAISE FND_API.g_exc_unexpected_error;
9963: END IF;
9964:
9965:
9958:
9959: IF x_return_status = FND_API.g_ret_sts_error THEN
9960: RAISE FND_API.g_exc_error;
9961: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9962: RAISE FND_API.g_exc_unexpected_error;
9963: END IF;
9964:
9965:
9966: -- added for R12. bmuthukr
10253: -- END of API body.
10254: --
10255: -- Standard check of p_commit.
10256:
10257: IF FND_API.To_Boolean ( p_commit ) THEN
10258: COMMIT WORK;
10259: END IF;
10260:
10261: -- Success Message
10267: FND_MSG_PUB.Add;
10268: --END IF;
10269:
10270:
10271: x_return_status := FND_API.G_RET_STS_SUCCESS;
10272: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
10273: --THEN
10274: FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
10275: FND_MESSAGE.Set_Token('ROW', 'AMS_ListGeneration_PKG.Generate_List: END');
10275: FND_MESSAGE.Set_Token('ROW', 'AMS_ListGeneration_PKG.Generate_List: END');
10276: FND_MSG_PUB.Add;
10277: --END IF;
10278: FND_MSG_PUB.count_and_get(
10279: p_encoded => FND_API.g_false,
10280: p_count => x_msg_count,
10281: p_data => x_msg_data
10282: );
10283:
10281: p_data => x_msg_data
10282: );
10283:
10284: EXCEPTION
10285: WHEN FND_API.G_EXC_ERROR THEN
10286: -- calling logging program
10287: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10288:
10289: UPDATE ams_list_headers_all
10301:
10302: --write_to_act_log('Error: AMS_ListGeneration_PKG.Generate_List: '||
10303: --l_error_position||sqlerrm||sqlcode);
10304: -- Check if reset of the status is required
10305: x_return_status := FND_API.G_RET_STS_ERROR ;
10306: FND_MSG_PUB.count_and_get(
10307: p_encoded => FND_API.g_false,
10308: p_count => x_msg_count,
10309: p_data => x_msg_data);
10303: --l_error_position||sqlerrm||sqlcode);
10304: -- Check if reset of the status is required
10305: x_return_status := FND_API.G_RET_STS_ERROR ;
10306: FND_MSG_PUB.count_and_get(
10307: p_encoded => FND_API.g_false,
10308: p_count => x_msg_count,
10309: p_data => x_msg_data);
10310:
10311: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
10307: p_encoded => FND_API.g_false,
10308: p_count => x_msg_count,
10309: p_data => x_msg_data);
10310:
10311: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
10312: -- calling logging program
10313: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10314:
10315: UPDATE ams_list_headers_all
10327: --l_error_position||sqlerrm||sqlcode);
10328: FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
10329: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
10330: FND_MSG_PUB.Add;
10331: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
10332: FND_MSG_PUB.count_and_get(
10333: p_encoded => FND_API.g_false,
10334: p_count => x_msg_count,
10335: p_data => x_msg_data);
10329: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
10330: FND_MSG_PUB.Add;
10331: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
10332: FND_MSG_PUB.count_and_get(
10333: p_encoded => FND_API.g_false,
10334: p_count => x_msg_count,
10335: p_data => x_msg_data);
10336:
10337: WHEN OTHERS THEN
10355: --l_error_position||sqlerrm||sqlcode);
10356: FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
10357: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
10358: FND_MSG_PUB.Add;
10359: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
10360: FND_MSG_PUB.count_and_get(
10361: p_encoded => FND_API.g_false,
10362: p_count => x_msg_count,
10363: p_data => x_msg_data);
10357: FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
10358: FND_MSG_PUB.Add;
10359: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
10360: FND_MSG_PUB.count_and_get(
10361: p_encoded => FND_API.g_false,
10362: p_count => x_msg_count,
10363: p_data => x_msg_data);
10364: END GENERATE_TARGET_GROUP;
10365:
11605: end if;
11606:
11607: Ams_Utility_Pvt.Write_Conc_log('After Update_List_Dets call : ');
11608:
11609: IF(l_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
11610: RAISE FND_API.G_EXC_ERROR;
11611: END IF;
11612: EXCEPTION
11613: WHEN OTHERS THEN
11606:
11607: Ams_Utility_Pvt.Write_Conc_log('After Update_List_Dets call : ');
11608:
11609: IF(l_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
11610: RAISE FND_API.G_EXC_ERROR;
11611: END IF;
11612: EXCEPTION
11613: WHEN OTHERS THEN
11614: UPDATE ams_list_headers_all
11802: -- FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
11803: -- FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
11804: -- FND_MSG_PUB.Add;
11805: FND_MSG_PUB.count_and_get(
11806: p_encoded => FND_API.g_false,
11807: p_count => x_msg_count,
11808: p_data => x_msg_data );
11809: x_return_status := 'S';
11810: x_msg_data := to_char(l_request_id);
11816: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
11817: FND_MESSAGE.Set_Token('TEXT', 'Error '|| sqlerrm||' '||sqlcode);
11818: FND_MSG_PUB.Add;
11819: FND_MSG_PUB.count_and_get(
11820: p_encoded => FND_API.g_false,
11821: p_count => x_msg_count,
11822: p_data => x_msg_data
11823: );
11824: x_return_status := 'E';
11821: p_count => x_msg_count,
11822: p_data => x_msg_data
11823: );
11824: x_return_status := 'E';
11825: RAISE FND_API.g_exc_unexpected_error;
11826: end if;
11827: end calc_selection_running_total;
11828:
11829: -- ----------------------------------------------------------------------------
11941: write_to_act_log('Error. The action type of the first selection is NOT INCLUDE.', 'LIST', g_list_header_id,'HIGH');
11942: Ams_Utility_Pvt.Write_Conc_log('process list actions : first action INCLUDE check failed');
11943: FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_FIRST_INCLUDE');
11944: FND_MSG_PUB.Add;
11945: RAISE FND_API.G_EXC_ERROR;
11946: END IF; -- End of if for list_action_type check
11947: END IF; --End of Validation:- First Action Include Check
11948:
11949:
11948:
11949:
11950: ams_listaction_pvt.Validate_ListAction
11951: ( p_api_version => 1.0,
11952: p_init_msg_list => FND_API.G_FALSE,
11953: p_validation_level => JTF_PLSQL_API.G_VALID_LEVEL_RECORD,
11954: x_return_status => x_return_status,
11955: x_msg_count => x_msg_count,
11956: x_msg_data => x_msg_data,
11956: x_msg_data => x_msg_data,
11957: p_action_rec => p_action_rec
11958: );
11959:
11960: IF x_return_status = FND_API.g_ret_sts_error THEN
11961: RAISE FND_API.g_exc_error;
11962: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
11963: RAISE FND_API.g_exc_unexpected_error;
11964: END IF;
11957: p_action_rec => p_action_rec
11958: );
11959:
11960: IF x_return_status = FND_API.g_ret_sts_error THEN
11961: RAISE FND_API.g_exc_error;
11962: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
11963: RAISE FND_API.g_exc_unexpected_error;
11964: END IF;
11965:
11958: );
11959:
11960: IF x_return_status = FND_API.g_ret_sts_error THEN
11961: RAISE FND_API.g_exc_error;
11962: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
11963: RAISE FND_API.g_exc_unexpected_error;
11964: END IF;
11965:
11966: Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : Dynamic ');
11959:
11960: IF x_return_status = FND_API.g_ret_sts_error THEN
11961: RAISE FND_API.g_exc_error;
11962: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
11963: RAISE FND_API.g_exc_unexpected_error;
11964: END IF;
11965:
11966: Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : Dynamic ');
11967: /********************************************************************
12023: end loop;
12024: end if; -- if p_action_rec.list_action_type = 'INCLUDE'
12025:
12026:
12027: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
12028: RAISE FND_API.G_EXC_ERROR;
12029: END IF;
12030: Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : End Dynamic ');
12031: -- end Of Dynamic Procedure
12024: end if; -- if p_action_rec.list_action_type = 'INCLUDE'
12025:
12026:
12027: IF(x_return_status <>FND_API.G_RET_STS_SUCCESS )THEN
12028: RAISE FND_API.G_EXC_ERROR;
12029: END IF;
12030: Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : End Dynamic ');
12031: -- end Of Dynamic Procedure
12032: l_join_string :='';
12112: end loop;
12113: close c_delta;
12114: commit;
12115: EXCEPTION
12116: WHEN FND_API.G_EXC_ERROR THEN
12117: write_to_act_log('Error while executing procedure calc_running_total '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12118: IF(c_action_dets%ISOPEN)THEN
12119: CLOSE c_action_dets;
12120: END IF;
12118: IF(c_action_dets%ISOPEN)THEN
12119: CLOSE c_action_dets;
12120: END IF;
12121: -- Check if reset of the status is required
12122: x_return_status := FND_API.G_RET_STS_ERROR ;
12123: FND_MSG_PUB.count_and_get(
12124: p_encoded => FND_API.g_false,
12125: p_count => x_msg_count,
12126: p_data => x_msg_data);
12120: END IF;
12121: -- Check if reset of the status is required
12122: x_return_status := FND_API.G_RET_STS_ERROR ;
12123: FND_MSG_PUB.count_and_get(
12124: p_encoded => FND_API.g_false,
12125: p_count => x_msg_count,
12126: p_data => x_msg_data);
12127:
12128: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
12124: p_encoded => FND_API.g_false,
12125: p_count => x_msg_count,
12126: p_data => x_msg_data);
12127:
12128: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
12129: write_to_act_log('Error while executing procedure calc_running_total '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12130: IF(c_action_dets%ISOPEN)THEN
12131: CLOSE c_action_dets;
12132: END IF;
12135: ||sqlerrm||sqlcode, 'LIST', g_list_header_id);
12136: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
12137: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
12138: FND_MSG_PUB.Add;
12139: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
12140: FND_MSG_PUB.count_and_get(
12141: p_encoded => FND_API.g_false,
12142: p_count => x_msg_count,
12143: p_data => x_msg_data);
12137: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
12138: FND_MSG_PUB.Add;
12139: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
12140: FND_MSG_PUB.count_and_get(
12141: p_encoded => FND_API.g_false,
12142: p_count => x_msg_count,
12143: p_data => x_msg_data);
12144:
12145: WHEN OTHERS THEN
12152: ||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
12153: FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
12154: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
12155: FND_MSG_PUB.Add;
12156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
12157: FND_MSG_PUB.count_and_get(
12158: p_encoded => FND_API.g_false,
12159: p_count => x_msg_count,
12160: p_data => x_msg_data);
12154: FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
12155: FND_MSG_PUB.Add;
12156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
12157: FND_MSG_PUB.count_and_get(
12158: p_encoded => FND_API.g_false,
12159: p_count => x_msg_count,
12160: p_data => x_msg_data);
12161: END calc_running_total;
12162:
12516: -- write_to_act_log('Process_rt_all_sql return status: After validate_sql_string call ','LIST', g_list_header_id);
12517: if l_found = 'N' then
12518: FND_MESSAGE.set_name('AMS', 'AMS_LIST_FROM_NOT_FOUND');
12519: FND_MSG_PUB.Add;
12520: RAISE FND_API.G_EXC_ERROR;
12521: end if;
12522: /* write_to_act_log('Process_rt_all_sql: FROM Position ->'|| l_from_position ||
12523: '<--FROM Counter ->' || l_from_counter ||
12524: '<--FROM Found ->' || l_found, 'LIST', g_list_header_id);*/
12536:
12537: if l_found = 'N' then
12538: FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_MASTER_TYPE');
12539: FND_MSG_PUB.Add;
12540: RAISE FND_API.G_EXC_ERROR;
12541: end if;
12542: write_to_act_log('Master type is '|| l_master_type ||'<--' , 'LIST', g_list_header_id,'LOW');
12543:
12544: l_found := 'N';
12582:
12583: write_to_act_log('Procedure process_rt_all_sql executed ', 'LIST', g_list_header_id ,'LOW');
12584:
12585: EXCEPTION
12586: WHEN FND_API.G_EXC_ERROR THEN
12587: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12588: x_return_status := FND_API.G_RET_STS_ERROR ;
12589: FND_MSG_PUB.count_and_get(
12590: p_encoded => FND_API.g_false,
12584:
12585: EXCEPTION
12586: WHEN FND_API.G_EXC_ERROR THEN
12587: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12588: x_return_status := FND_API.G_RET_STS_ERROR ;
12589: FND_MSG_PUB.count_and_get(
12590: p_encoded => FND_API.g_false,
12591: p_count => x_msg_count,
12592: p_data => x_msg_data
12586: WHEN FND_API.G_EXC_ERROR THEN
12587: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12588: x_return_status := FND_API.G_RET_STS_ERROR ;
12589: FND_MSG_PUB.count_and_get(
12590: p_encoded => FND_API.g_false,
12591: p_count => x_msg_count,
12592: p_data => x_msg_data
12593: );
12594:
12591: p_count => x_msg_count,
12592: p_data => x_msg_data
12593: );
12594:
12595: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
12596: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12597: x_return_status := FND_API.G_RET_STS_ERROR ;
12598: FND_MSG_PUB.count_and_get(
12599: p_encoded => FND_API.g_false,
12593: );
12594:
12595: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
12596: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12597: x_return_status := FND_API.G_RET_STS_ERROR ;
12598: FND_MSG_PUB.count_and_get(
12599: p_encoded => FND_API.g_false,
12600: p_count => x_msg_count,
12601: p_data => x_msg_data
12595: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
12596: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12597: x_return_status := FND_API.G_RET_STS_ERROR ;
12598: FND_MSG_PUB.count_and_get(
12599: p_encoded => FND_API.g_false,
12600: p_count => x_msg_count,
12601: p_data => x_msg_data
12602: );
12603:
12602: );
12603:
12604: WHEN OTHERS THEN
12605: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12606: x_return_status := FND_API.G_RET_STS_ERROR ;
12607: FND_MSG_PUB.count_and_get(
12608: p_encoded => FND_API.g_false,
12609: p_count => x_msg_count,
12610: p_data => x_msg_data
12604: WHEN OTHERS THEN
12605: write_to_act_log('Error when executing procedure process_rt_all_sql '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12606: x_return_status := FND_API.G_RET_STS_ERROR ;
12607: FND_MSG_PUB.count_and_get(
12608: p_encoded => FND_API.g_false,
12609: p_count => x_msg_count,
12610: p_data => x_msg_data
12611: );
12612:
12758:
12759: exception
12760: when others then
12761: write_to_act_log('Error while executing process_run_total_sql ' ||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
12762: x_return_status := FND_API.G_RET_STS_ERROR ;
12763: END process_run_total_sql;
12764:
12765: -- ----------------------------------------------------------------------------------------------
12766: PROCEDURE process_run_total_cell
12812:
12813: write_to_act_log('Executing procedure process_run_total_cell', 'LIST', g_list_header_id,'LOW' );
12814: ams_cell_pvt.get_comp_sql(
12815: p_api_version => 1.0,
12816: p_init_msg_list => FND_API.g_false,
12817: p_validation_level => FND_API.g_valid_level_full,
12818: x_return_status => x_return_status,
12819: x_msg_count => x_msg_count ,
12820: x_msg_data =>x_msg_data,
12813: write_to_act_log('Executing procedure process_run_total_cell', 'LIST', g_list_header_id,'LOW' );
12814: ams_cell_pvt.get_comp_sql(
12815: p_api_version => 1.0,
12816: p_init_msg_list => FND_API.g_false,
12817: p_validation_level => FND_API.g_valid_level_full,
12818: x_return_status => x_return_status,
12819: x_msg_count => x_msg_count ,
12820: x_msg_data =>x_msg_data,
12821: p_cell_id => p_incl_object_id ,
12818: x_return_status => x_return_status,
12819: x_msg_count => x_msg_count ,
12820: x_msg_data =>x_msg_data,
12821: p_cell_id => p_incl_object_id ,
12822: p_party_id_only => FND_API.g_false,
12823: x_sql_tbl => l_sql_2
12824: );
12825: write_to_act_log('AMS_ListGeneration_PKG.After Comp sql:', 'LIST', g_list_header_id,'LOW');
12826:
13690: -- write_to_act_log('Party id is '||l_party_id, 'LIST', p_list_header_id,'LOW');
13691: if x_msg_count > 0 then
13692: FOR i IN 1..x_msg_count LOOP
13693: -- Following code was modified by bmuthukr
13694: --x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
13695: --x_tmp_var1 := substrb(x_tmp_var1 || ' '|| x_tmp_var,1,4000);
13696: x_tmp_var := 'Please make sure that the required fields (eg. person first name, last name) are available in the remote database';
13697: write_to_act_log('Updating the tca_load_status as ERROR', 'LIST', p_list_header_id,'HIGH');
13698:
13746:
13747: commit;
13748:
13749: EXCEPTION
13750: WHEN FND_API.G_EXC_ERROR THEN
13751: write_to_act_log('Error while executing procedure tca_upload_process '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13752: x_return_status := FND_API.G_RET_STS_ERROR ;
13753:
13754: if l_prof <> 'N' then
13748:
13749: EXCEPTION
13750: WHEN FND_API.G_EXC_ERROR THEN
13751: write_to_act_log('Error while executing procedure tca_upload_process '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13752: x_return_status := FND_API.G_RET_STS_ERROR ;
13753:
13754: if l_prof <> 'N' then
13755: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13756: end if;
13755: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13756: end if;
13757:
13758: FND_MSG_PUB.count_and_get(
13759: p_encoded => FND_API.g_false,
13760: p_count => x_msg_count,
13761: p_data => x_msg_data
13762: );
13763:
13762: );
13763:
13764:
13765:
13766: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
13767: write_to_act_log('Error while executing procedure tca_upload_process '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13768: x_return_status := FND_API.G_RET_STS_ERROR ;
13769:
13770: if l_prof <> 'N' then
13764:
13765:
13766: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
13767: write_to_act_log('Error while executing procedure tca_upload_process '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13768: x_return_status := FND_API.G_RET_STS_ERROR ;
13769:
13770: if l_prof <> 'N' then
13771: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13772: end if;
13771: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13772: end if;
13773:
13774: FND_MSG_PUB.count_and_get(
13775: p_encoded => FND_API.g_false,
13776: p_count => x_msg_count,
13777: p_data => x_msg_data
13778: );
13779:
13778: );
13779:
13780: WHEN OTHERS THEN
13781: write_to_act_log('Error while executing procedure tca_upload_process '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13782: x_return_status := FND_API.G_RET_STS_ERROR ;
13783:
13784: if l_prof <> 'N' then
13785: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13786: end if;
13785: fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
13786: end if;
13787:
13788: FND_MSG_PUB.count_and_get(
13789: p_encoded => FND_API.g_false,
13790: p_count => x_msg_count,
13791: p_data => x_msg_data
13792: );
13793:
13829: p_arc_log_used_by => 'LIST',
13830: p_log_used_by_id => p_list_header_id,
13831: p_level => 'LOW');
13832: x_remote_gen := 'Y';
13833: x_return_status := FND_API.G_RET_STS_SUCCESS;
13834: else
13835: write_to_act_log(p_msg_data => 'List selection does not include segments/workbook/SQL.' ,
13836: p_arc_log_used_by => 'LIST',
13837: p_log_used_by_id => p_list_header_id,
13847: p_arc_log_used_by => 'LIST',
13848: p_log_used_by_id => p_list_header_id,
13849: p_level => 'LOW');
13850: x_remote_gen := 'Y';
13851: x_return_status := FND_API.G_RET_STS_SUCCESS;
13852: else
13853: write_to_act_log(p_msg_data => 'List/TG will be generated in local instance.' ,
13854: p_arc_log_used_by => 'LIST',
13855: p_log_used_by_id => p_list_header_id,
13854: p_arc_log_used_by => 'LIST',
13855: p_log_used_by_id => p_list_header_id,
13856: p_level => 'LOW');
13857: x_remote_gen := 'N';
13858: x_return_status := FND_API.G_RET_STS_SUCCESS;
13859: end if;
13860: end if;
13861:
13862: EXCEPTION
13861:
13862: EXCEPTION
13863: WHEN OTHERS THEN
13864: write_to_act_log('Error while executing procedure remote_list_gen '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
13865: x_return_status := FND_API.G_RET_STS_ERROR ;
13866: x_msg_data := 'Error while executing procedure remote_list_gen '||sqlcode||' '||sqlerrm;
13867: x_msg_count := 1;
13868: FND_MSG_PUB.count_and_get(
13869: p_encoded => FND_API.g_false,
13865: x_return_status := FND_API.G_RET_STS_ERROR ;
13866: x_msg_data := 'Error while executing procedure remote_list_gen '||sqlcode||' '||sqlerrm;
13867: x_msg_count := 1;
13868: FND_MSG_PUB.count_and_get(
13869: p_encoded => FND_API.g_false,
13870: p_count => x_msg_count,
13871: p_data => x_msg_data
13872: );
13873:
13983:
13984: exception
13985: when others then
13986: write_to_act_log('Error while executing procedure is_manual '||sqlcode||' '||sqlerrm,'LIST',p_list_header_id,'HIGH');
13987: x_return_status := FND_API.G_RET_STS_ERROR ;
13988: x_msg_data := 'Error while executing procedure is_manual '||sqlcode||' '||sqlerrm;
13989: x_msg_count := 1;
13990: end is_manual;
13991:
14065: commit;
14066:
14067: exception
14068: when others then
14069: x_return_status := FND_API.G_RET_STS_ERROR;
14070: x_msg_data := 'Error while executing upd_list_header_info. '||sqlcode||' '||sqlerrm;
14071: x_msg_count := 1;
14072: end upd_list_header_info;
14073: