[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
**Appends to a message the name of the object anf the operation (insert, update ,delete)
*/
PROCEDURE AddfailMsg
( p_object IN VARCHAR2,
p_operation IN VARCHAR2 ) IS
BEGIN
fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
x_cas_rec.PROGRAM_UPDATE_DATE := p_cas_rec.PROGRAM_UPDATE_DATE;
x_cas_rec.LAST_UPDATED_BY := p_cas_rec.LAST_UPDATED_BY ;
x_cas_rec.LAST_UPDATE_DATE := p_cas_rec.LAST_UPDATE_DATE;
x_cas_rec.LAST_UPDATE_LOGIN := p_cas_rec.LAST_UPDATE_LOGIN;
x_case_object_rec.PROGRAM_UPDATE_DATE := p_attribute_rec.PROGRAM_UPDATE_DATE;
x_case_object_rec.LAST_UPDATED_BY := p_attribute_rec.LAST_UPDATED_BY ;
x_case_object_rec.LAST_UPDATE_DATE := p_attribute_rec.LAST_UPDATE_DATE;
x_case_object_rec.LAST_UPDATE_LOGIN := p_attribute_rec.LAST_UPDATE_LOGIN;
x_case_def_rec.PROGRAM_UPDATE_DATE := p_attribute_rec.PROGRAM_UPDATE_DATE;
x_case_def_rec.LAST_UPDATED_BY := p_attribute_rec.LAST_UPDATED_BY ;
x_case_def_rec.LAST_UPDATE_DATE := p_attribute_rec.LAST_UPDATE_DATE;
x_case_def_rec.LAST_UPDATE_LOGIN := p_attribute_rec.LAST_UPDATE_LOGIN;
Select column_name,column_value,table_name
From iex_case_definitions
where cas_id =p_cas_id
and active_flag ='Y';
select * from iex_cases_all_b
where cas_id =p_cas_id
and active_flag ='Y';
select comments from iex_cases_tl
where cas_id =p_cas_id and
userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and active_flag ='Y';
x_cas_rec.PROGRAM_UPDATE_DATE := get_cas_rec.PROGRAM_UPDATE_DATE;
Select del.delinquency_id
from iex_delinquencies del,
iex_writeoffs wrioff
where del.case_id = p_caseid
and wrioff.delinquency_id = del.delinquency_id
--Start of BUG 4408860
-- For Bug 4408860
--jsanju 06/05/05
-- we shpudl npot be looking for status ='CLOSE'
--since from iex.h, we do not have case delinquencies and
-- and the old delinquencies with case id has been update to status ='CLOSE'
--and del.status <> 'CURRENT';
Select del.delinquency_id
from iex_delinquencies del,
iex_bankruptcies bank
where del.case_id = p_caseid
and bank.delinquency_id = del.delinquency_id
--Start of BUG 4408860
-- For Bug 4408860
--jsanju 06/05/05
-- we shpudl npot be looking for status ='CLOSE'
--since from iex.h, we do not have case delinquencies and
-- and the old delinquencies with case id has been update to status ='CLOSE'
--and del.status <> 'CURRENT';
Select del.delinquency_id
from iex_delinquencies del,
iex_repossessions repo
where del.case_id = p_caseid
and repo.delinquency_id = del.delinquency_id
--Start of BUG 4408860
-- For Bug 4408860
--jsanju 06/05/05
-- we shpudl npot be looking for status ='CLOSE'
--since from iex.h, we do not have case delinquencies and
-- and the old delinquencies with case id has been update to status ='CLOSE'
--and del.status <> 'CURRENT';
Select del.delinquency_id
from iex_delinquencies del,
iex_litigations litg
where del.case_id = p_caseid
and litg.delinquency_id = del.delinquency_id
--Start of BUG 4408860
-- For Bug 4408860
--jsanju 06/05/05
-- we shpudl npot be looking for status ='CLOSE'
--since from iex.h, we do not have case delinquencies and
-- and the old delinquencies with case id has been update to status ='CLOSE'
--and del.status <> 'CURRENT';
select iex_cases_all_b_s.nextval
into itemkey
from dual;
vstr1 VARCHAR2(100) := 'SELECT COUNT(*) FROM ' ;
l_case_sql VARCHAR2(2000):= ' and exists (select null from iex_cases_all_b ICAS where ICAS.cas_id = a.cas_id'||
' and ICAS.case_state='||'''OPEN''' ||
' and ICAS.active_flag='||'''Y''' ||' ) ';
vstr1 VARCHAR2 (2000):= ' select a.cas_id from ';
l_first_sql VARCHAR2 (2000):=' select a.cas_id from '||
' iex_case_definitions a '||
' where a.column_name =' ||''''|| l_column_name || ''''||
' and a.column_value =' ||''''|| l_column_value || ''''||
' and a.active_flag='||'''Y''';
v_sql_str1 VARCHAR2(100) := ' and exists ( select null from ';
l_sql := l_sql || ' and exists ( select null from '||
' iex_case_definitions ' || l_table_alias ||
' where column_name =' ||''''|| l_column_name || ''''||
' and column_value =' ||''''|| l_column_value || ''''||
' and '||l_table_alias ||'.cas_id= ' ||'a.cas_id'||
' and '||l_table_alias ||'.active_flag='||'''Y''' ;
** Updates the status of a case. Also create a new case and updates the case objects to the new case
** if specified ( the p_copy_objects parameter should be equal to 'Y')
** Required Parameters are
** a) p_Cas_id --> Cas Id
** Optional Parameters
** a) p_copy_objects --> default is 'N'. If 'Y' then create new case and copy all the case objects to
** the new case. If Value is 'N',then do not copy the case objects.
** p_close_date -->Closing date of the case.
*/
PROCEDURE CloseCase(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 ,
P_Commit IN VARCHAR2 ,
P_validation_level IN NUMBER ,
P_cas_id IN NUMBER,
p_close_date IN DATE,
p_copy_objects IN VARCHAR2,
p_cas_Rec IN cas_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CloseCase';
select object_version_number,
case_object_id
from iex_case_objects
where cas_id =p_cas_id
and active_flag ='Y';
SELECT cas_id,object_version_number
INTO l_cas_id,l_object_version_number
FROM iex_cases_all_b
WHERE cas_id = p_cas_id
AND case_state = 'OPEN'
and active_flag ='Y';
iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling Update PVT');
iex_cases_pvt.update_cas
(P_Api_Version_Number =>l_api_version_number,
P_Init_Msg_List =>FND_API.G_FALSE,
P_Commit =>FND_API.G_FALSE,
p_validation_level =>P_validation_level,
P_cas_Rec =>l_cas_Rec,
X_Return_Status =>l_return_status,
X_Msg_Count =>l_msg_count,
X_Msg_Data =>l_msg_data,
xo_object_version_number =>l_object_version_number);
iex_debug_pub.logMessage('CloseCase: ' || 'After Calling update case PVT and Status =>'||l_return_status);
p_operation => 'UPDATE' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
IEX_DEBUG_PUB.LogMessage ('CloseCase: ' || ' the case object ID updated is =>'
||cas_obj_rec.case_object_id ||' and new cas ID is '||
l_cas_new_id||' and old case id is => '||l_cas_id );
iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling update Case Object PVT');
iex_case_objects_pvt.update_case_objects(
P_Api_Version_Number =>l_api_version_number,
P_Init_Msg_List =>FND_API.G_FALSE,
P_Commit =>FND_API.G_FALSE,
p_validation_level =>P_validation_level,
P_case_object_Rec =>l_case_object_Rec,
X_Return_Status =>l_return_status,
X_Msg_Count =>l_msg_count,
X_Msg_Data =>l_msg_data,
xo_object_version_number =>l_object_version_number);
iex_debug_pub.logMessage('CloseCase: ' || 'After Calling update Case Object PVT and Status =>'||l_return_status);
p_operation => 'INSERT' );
select ORIG_SYSTEM_SOURCE_CODE
from OKC_K_HEADERS_B
where id=p_contract_id;
SELECT cas_id INTO l_cas_id
FROM iex_cases_all_b
WHERE cas_id = p_cas_id
AND case_state = 'OPEN'
and active_flag ='Y';
p_operation => 'INSERT' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
** Delete the contracts from iex_case_objects
** It first checks if a case exists, else creates
** a new case with the given case definition. Also checks if the case definition
** elements passed are valid or not.The logic is as follows
** 1. Checks validity of Case Definition
** 2.Gets Case Id for the given Valid Case Definition
** 3.Create Case if case does not exists with the valid case definition
** 4.Creates Case Object
** Rescore both the cases and creates/updates the delinquencies.
Optional Parameters
** p_cas_rec --> populate other attributes of a case, like attributes 1-15, concurrent program fields
**
*/
PROCEDURE ReassignCaseObjects(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 ,
P_Commit IN VARCHAR2 ,
P_validation_level IN NUMBER ,
P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE
,
P_cas_id IN NUMBER ,
P_case_number IN VARCHAR2 ,
P_case_comments IN VARCHAR2 ,
P_case_established_date IN DATE ,
P_org_id IN NUMBER ,
P_object_code IN VARCHAR2 ,
P_party_id IN NUMBER,
P_object_id IN NUMBER,
p_cas_rec IN CAS_Rec_Type ,
X_case_object_id OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'ReassignCaseObjects';
Select user_name
from jtf_rs_resource_extns a,
iex_Cases_all_b b
where b.cas_id =p_old_case_id
and a.resource_id = nvl(b.access_resource_id,
fnd_profile.value('IEX_DEFAULT_CASE_AGENT'));
Select contract_number
from okc_k_headers_b
where id =p_contract_id;
SELECT cas_id INTO l_cas_id
FROM iex_cases_all_b
WHERE cas_id = p_cas_id
AND case_state = 'OPEN'
and active_flag ='Y';
select a.cas_id ,b.contract_number
into l_old_case_id,l_contract_number
from iex_case_objects a, okc_k_headers_b b
where a.object_id =p_object_id
and b.id =a.object_id;
select case_object_id into l_case_object_id
from iex_case_objects
where object_id =p_object_id;
iex_case_objects_pvt.delete_case_objects(
P_Api_Version_Number =>l_api_version_number,
P_Init_Msg_List =>FND_API.G_TRUE,
P_Commit =>FND_API.G_FALSE,
p_validation_level =>P_validation_level,
P_case_object_ID =>l_case_object_id,
X_Return_Status =>l_return_status,
X_Msg_Count =>l_msg_count,
X_Msg_Data =>l_msg_data);
p_operation => 'DELETE' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
p_operation => 'INSERT' );
/* Name UpdateCase
** api : Current version 2.0
** Purpose To update a case. Change the status from delinquent to current when the case
** comes out NOCOPY of delinquency
**
*/
PROCEDURE UpdateCase(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 ,
P_Commit IN VARCHAR2 ,
P_validation_level IN NUMBER ,
p_cas_rec IN CAS_Rec_Type ,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UpdateCase';
IEX_DEBUG_PUB.LogMessage ('UpdateCase: ' || '*********Start of Procedure => '||l_api_name||' *********');
SAVEPOINT UPDATECASE_PUB;
iex_debug_pub.logMessage('UpdateCase: ' || 'After Api compatability Check');
iex_debug_pub.logMessage('UpdateCase: ' || 'After FND_GLOBAL_USER Check');
SELECT cas_id,object_version_number
INTO l_cas_id,l_object_version_number
FROM iex_cases_all_b
WHERE cas_id = p_cas_rec.cas_id
and active_flag ='Y';
iex_debug_pub.logMessage('UpdateCase: ' || 'After Item validation ');
iex_debug_pub.logMessage('UpdateCase: ' || 'Before Calling Update PVT');
iex_cases_pvt.update_cas
(P_Api_Version_Number =>l_api_version_number,
P_Init_Msg_List =>FND_API.G_FALSE,
P_Commit =>FND_API.G_FALSE,
p_validation_level =>P_validation_level,
P_cas_Rec =>l_cas_Rec,
X_Return_Status =>l_return_status,
X_Msg_Count =>l_msg_count,
X_Msg_Data =>l_msg_data,
xo_object_version_number =>l_object_version_number);
iex_debug_pub.logMessage('UpdateCase: ' || 'After Calling update case PVT and Status =>'||l_return_status);
p_operation => 'UPDATE' );
IEX_DEBUG_PUB.LogMessage ('UpdateCase: ' || '*********End of Procedure => '||l_api_name||' *********');
END UpdateCase;
p_operation => 'INSERT' );
ELSE -- if case contact insert = success
--if it is a primary contact
if P_case_contact_tbl(i).primary_flag = 'Y' THEN
l_assign := TRUE;
p_operation => 'INSERT' );
END IF; --checking return status of case contact insert
Select object_id INTO x_ObjectID
from iex_case_objects
where object_id =P_ObjectID;