API used for EAM asset maintenance during work order failure.
This API used to create failure code and its cause and its resolution for an asset in an asset group.
create table xx_test_failure_combinations
(failure_code varchar2(80)
, failure_desc varchar2(2000)
, cause_code varchar2(80)
, cause_desc varchar2(2000)
, resolution_code varchar2(80)
, resolution_desc varchar2(2000)
)
TYPE eam_failureset_rec_type IS RECORD
(set_name VARCHAR2(80) ,
description VARCHAR2(240) ,
effective_end_date DATE ,
set_id NUMBER ,
stored_last_upd_date DATE);
PROCEDURE Create_FailureSet
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_failureset_rec IN EAM_FailureSets_PUB.eam_failureset_rec_type,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_failureset_id OUT NOCOPY NUMBER
);
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from eam_failure_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Failure Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.FAILURE_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description := null;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.DELETE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_failure_codes
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from xx_eam_failure_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Failure Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.FAILURE_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description :=r1.description;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.CREATE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_failure_codes
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from eam_cause_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Cause Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.CAUSE_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description := null;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.DELETE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_cause_codes
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from xx_eam_cause_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Cause Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.CAUSE_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description := r1.description;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.CREATE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_cause_codes
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from eam_resolution_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Resolution Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.RESOLUTION_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description := null;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.DELETE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_resolution_codes
declare
type x_failure_code is table of EAM_FailureCodes_PUB.eam_failurecode_rec_type index by binary_integer;
v_x_failure_code x_failure_code;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from xx_eam_resolution_codes
where 1=1;
--and failure_code = 'Disturbance';
begin
begin
select LOOKUP_CODE
into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Resolution Code';
exception
when others then
null;
end;
/*
select *
from eam_failure_codes
where 1=1
and failure_code = 'Disturbance'
*/
for r1 in c1 loop
v_x_failure_code(1).code_type := v_lookup_code;
v_x_failure_code(1).code := r1.RESOLUTION_CODE;--'AVR Control type Alternators';
v_x_failure_code(1).description := r1.description;
v_x_failure_code(1).effective_end_date := null;
v_x_failure_code(1).stored_last_upd_date := null;
EAM_FAILURECODES_PUB.CREATE_CODE
(p_api_version => 1
,p_init_msg_list => v_p_init_msg_list
,p_commit => v_p_commit
,p_failurecode_rec => v_x_failure_code(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
dbms_output.put_line('the status us filure '||v_x_return_status||'message data is '||v_x_msg_count);
dbms_output.put_line('xx'||v_x_msg_data);
end loop;
end;
select * from eam_resolution_codes
select LOOKUP_CODE,meaning
--into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Failure Code';
select * from dual
declare
type x_failure_com is table of eam_failurecodes_pub.eam_combination_rec_type index by binary_integer;
v_x_failure_com x_failure_com;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
cursor c1 is
select *
from xx_eam_failure_combinations
where 1=1;
--and failure_code = 'AVR Control type Alternators';
--and combination_id = 2132;
v_count number;
begin
v_count := 1;
for r1 in c1 loop
v_x_failure_com(1).set_id := r1.set_id;
v_x_failure_com(1).set_name := null;--'Electrical Systems';
v_x_failure_com(1).failure_code := r1.failure_code;---'AVR Control type Alternators';
v_x_failure_com(1).failure_description := null;
v_x_failure_com(1).cause_code := r1.cause_code;--'High voltage either on no-load or with load';
v_x_failure_com(1).cause_description := null;
v_x_failure_com(1).resolution_code := r1.resolution_code;--'Check speed';
v_x_failure_com(1).resolution_description := null;
v_x_failure_com(1).effective_end_date := null;
v_x_failure_com(1).combination_id := r1.combination_id;--2128;
v_x_failure_com(1).stored_last_upd_date := null;
v_x_failure_com(1).created_by := null;
v_x_failure_com(1).creation_date := null;
v_x_failure_com(1).last_update_date := null;
v_x_failure_com(1).last_updated_by := null;
v_x_failure_com(1).last_update_login := null;
EAM_FAILURECODES_PUB.delete_combination
(p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_combination_rec => v_x_failure_com(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
);
--dbms_output.put_line('the status us '||v_x_return_status||'message data is '||v_x_msg_count);
--dbms_output.put_line('xx'||v_x_msg_data);
--v_count := v_count+1;
end loop;
end;
select * from xx_eam_failure_combinations
declare
type x_failure_com is table of eam_failurecodes_pub.eam_combination_rec_type index by binary_integer;
v_x_failure_com x_failure_com;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
v_p_commit VARCHAR2(100) := FND_API.G_FALSE;
v_x_return_status VARCHAR2(1000);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(1000);
v_lookup_code number;
v_set_id number;
cursor c1 is
select *
from xx_eam_failure_combinations
where 1=1;
--and failure_code = 'AVR Control type Alternators';
--and combination_id = 2132;
v_count number;
v_x_combination_id number;
begin
v_count := 1;
begin
select set_id
into v_set_id
from eam_failure_sets
where 1=1
and set_name = 'DDIL Failure Set';
exception
when others then
null;
end;
for r1 in c1 loop
v_x_failure_com(1).set_id := v_set_id;
v_x_failure_com(1).set_name := null;--'Electrical Systems';
v_x_failure_com(1).failure_code := r1.failure_code;---'AVR Control type Alternators';
v_x_failure_com(1).failure_description := null;
v_x_failure_com(1).cause_code := r1.cause_code;--'High voltage either on no-load or with load';
v_x_failure_com(1).cause_description := null;
v_x_failure_com(1).resolution_code := r1.resolution_code;--'Check speed';
v_x_failure_com(1).resolution_description := null;
v_x_failure_com(1).effective_end_date := null;
v_x_failure_com(1).combination_id := null;--2128;
v_x_failure_com(1).stored_last_upd_date := null;
v_x_failure_com(1).created_by := null;
v_x_failure_com(1).creation_date := null;
v_x_failure_com(1).last_update_date := null;
v_x_failure_com(1).last_updated_by := null;
v_x_failure_com(1).last_update_login := null;
EAM_FAILURECODES_PUB.create_combination
(p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_combination_rec => v_x_failure_com(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
,x_combination_id => v_x_combination_id
);
dbms_output.put_line('the status us '||v_x_return_status||'combination id'||v_x_combination_id);
--dbms_output.put_line('xx'||v_x_msg_data);
--v_count := v_count+1;
end loop;
end;
select * from eam_failure_combinations where trim(failure_code) not in ( select trim(failure_code) from xx_eam_failure_combinations)
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_combination_rec IN EAM_FailureCodes_PUB.eam_combination_rec_type,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
TYPE eam_combination_rec_type IS RECORD
(set_id NUMBER ,
set_name VARCHAR2(80),
failure_code VARCHAR2(80),
failure_description VARCHAR2(2000),
cause_code VARCHAR2(80),
cause_description VARCHAR2(2000),
resolution_code VARCHAR2(80),
resolution_description VARCHAR2(2000),
effective_end_date DATE ,
combination_id NUMBER,
stored_last_upd_date DATE,
created_by NUMBER ,
creation_date DATE ,
last_update_date DATE ,
last_updated_by NUMBER ,
last_update_login NUMBER);
TYPE eam_failurecode_rec_type IS RECORD
(code_type NUMBER ,
code VARCHAR2(80) ,
description VARCHAR2(2000),
effective_end_date DATE ,
stored_last_upd_date DATE);
select *
select LOOKUP_CODE
--into v_lookup_code
from FND_LOOKUP_VALUES
where 1=1
and lookup_type = 'EAM_FAILURE_CODE_TYPE'
and meaning = 'Failure Code';
select *
from eam_failure_combinations
where 1=1
and resolution_code is not null
select *
from eam_failure_codes
where 1=1
and failure_code = 'AVR Control type Alternators'
order by failure_code
select *
from eam_failure_combinations
where 1=1
and failure_code = 'AVR Control type Alternators'
select *
from eam_failure_combinations
where 1=1
and failure_code = 'AVR Control type Alternators'
and combination_id = 2132;
select * from eam_failure_sets
create table xx_eam_failure_sets as
select *
from eam_failure_sets where set_name = 'gear train'
select * from eam_failure_combinations --where set_id = 3006
delete from eam_failure_sets
declare
--v_p_failureset_rec EAM_FailureSets_PUB.eam_failureset_rec_type;
type xv_p_failureset_rec is table of EAM_FailureSets_PUB.eam_failureset_rec_type index by binary_integer;
v_x_return_status VARCHAR2(100);
v_x_msg_count NUMBER;
v_x_msg_data VARCHAR2(2000);
v_x_failureset_id NUMBER;
v_p_api_version NUMBER;
v_p_init_msg_list VARCHAR2(1000) := FND_API.G_FALSE;
v_p_commit VARCHAR2(1000) := FND_API.G_FALSE;
v_p_failureset_rec xv_p_failureset_rec;
begin
v_p_failureset_rec(1).set_name:= 'DDIL Failure Set';
v_p_failureset_rec(1).description:= 'DDIL Failure Set';
v_p_failureset_rec(1).effective_end_date:= null;
v_p_failureset_rec(1).set_id := null;
v_p_failureset_rec(1).stored_last_upd_date := null;
EAM_FailureSets_PUB.Create_FailureSet
( p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_failureset_rec => v_p_failureset_rec(1)
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,x_msg_data => v_x_msg_data
,x_failureset_id => v_x_failureset_id
);
dbms_output.put_line('the return status is'||v_x_return_status);
commit;
end;
select * from eam_failure_sets
select * from eam_failure_codes
select * from eam_resolution_codes
select * from eam_cause_codes
select * from EAM_FAILURE_COMBINATIONS
No comments:
Post a Comment