API used for EAM preventive collection plan creation and its Elements insertion.
Basically this QA tables are used for capturing daily meter reading of asset in EAM and then
This will used for work order creation.
CREATE OR REPLACE PACKAGE APPS.MLGWEAM_QA_PLANS_ELEMENTS_PKG
IS
/*+-----------------------------------------------------------------------------------+
-- ================================== Record History =================================
---------------------------------------------------------------------------------------
--
--
-- Oracle Applications Release 12.1.3
--
-- Type : Interface
-- Reference : EX067 - MD050 - Pavement Replacement Form -1[1]
-- Description : Pavement Collaction Plan Creation and its elements insertion
-- Module Name : Enterprise Asset Management
-- Program Name : MLGWEAM_QA_PLANS_ELEMENTS_PKG
-- Schema Owner : APPS
-- Run environment : PL/SQL
--
+----------------------------------------------------------------------------------+ */
/*+-----------------------------------------------------------------------------------+
Procedure "MLGWEAM_QA_PLANS_ELEMENTS_PKG" to create Pavement Collaction Plan Creation and its elements insertion
/*+-----------------------------------------------------------------------------------+*/
procedure QA_PLANS_CREATION_PROC
(P_Errbuf OUT VARCHAR2,
P_Retcode OUT NUMBER
);
procedure qa_plan_elements_proc
(P_Errbuf OUT VARCHAR2,
P_Retcode OUT NUMBER
);
/*+-----------------------------------------------------------------------------------+
Declaring Global Variables.
/*+-----------------------------------------------------------------------------------+*/
g_count NUMBER := 1;
g_error_mesg VARCHAR2 (4000) := NULL;
g_error_log_flag VARCHAR2 (10) := 'N';
g_error_flag VARCHAR2 (1) := 'N';
g_org_id VARCHAR2 (4) := fnd_profile.VALUE ('ORG_ID');
g_request_id NUMBER := fnd_global.conc_request_id;
g_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
g_resp_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
G_RESPONSIBILITY_ID NUMBER := FND_PROFILE.VALUE ('RESP_ID');
END MLGWEAM_QA_PLANS_ELEMENTS_PKG;
/
CREATE OR REPLACE PACKAGE body APPS.MLGWEAM_QA_PLANS_ELEMENTS_PKG
IS
/*+-----------------------------------------------------------------------------------+
-- ================================== Record History =================================
---------------------------------------------------------------------------------------
--
--
-- Oracle Applications Release 12.1.3
--
-- Type : Interface
-- Reference : EX067 - MD050 - Pavement Replacement Form -1[1]
-- Description : Pavement Collaction Plan Creation and its elements insertion
-- Module Name : Enterprise Asset Management
-- Program Name : MLGWEAM_QA_PLANS_ELEMENTS_PKG
-- Schema Owner : APPS
-- Run environment : PL/SQL
--
/*+-----------------------------------------------------------------------------------+
Procedure "MLGWEAM_QA_PLANS_ELEMENTS_PKG" to create Pavement Collaction Plan Creation and its elements insertion
/*+-----------------------------------------------------------------------------------+*/
procedure QA_PLANS_CREATION_PROC
(P_Errbuf OUT VARCHAR2,
P_Retcode OUT NUMBER
)
is
x_v_plan_id number;
x_v_msg_count number;
x_v_msg_data varchar2(4000);
x_v_return_status varchar2(1000);
x_error_flag varchar2(1);
x_user_name varchar2(100);
v_plan_id number:= null;
begin
FND_GLOBAL.APPS_INITIALIZE (G_USER_ID,
G_RESPONSIBILITY_ID,
G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'User Id : ' || G_USER_ID);
FND_FILE.PUT_LINE (FND_FILE.log, 'Responsibility Id : ' || G_RESPONSIBILITY_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'Resp Application Id : ' || G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request ID : ' || G_REQUEST_ID );
x_error_flag := null;
begin
select user_name
into x_user_name
from fnd_user
where 1=1
and user_id = G_USER_ID;
exception
when others then
x_error_flag := 'E';
fnd_file.put_line(fnd_file.log,'Error While selection username '||SQLERRM);
end;
begin
select plan_id
into v_plan_id
from qa_plans
where 1=1
and name = 'PAVEMENT REPLACEMENT';
exception
when no_data_found then
null;
when others then
x_error_flag := 'E';
fnd_file.put_line(fnd_file.log,'Error While selection of PAVEMENT REPLACEMENT plan '||SQLERRM);
end;
fnd_file.put_line(fnd_file.log,'Org id is '||g_org_id);
if x_error_flag is null and v_plan_id is null then
qa_plans_pub.create_collection_plan
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_user_name => x_user_name ,
p_plan_name => 'PAVEMENT REPLACEMENT',
p_organization_code => 'LGW',
p_plan_type => 'Pavement Replacement Plan',
p_description => 'Pavement Replacement Plan For WO',
p_effective_from => sysdate,
p_effective_to => NULL,
p_spec_assignment_type => qa_plans_pub.g_spec_type_none,
p_multirow_flag => 2,
x_plan_id => x_v_plan_id,
x_msg_count => x_v_msg_count,
x_msg_data => x_v_msg_data,
x_return_status => x_v_return_status
);
end if;
commit;
fnd_file.put_line(fnd_file.log,'Return Status '||x_v_return_status||' and Return Message '||x_v_msg_data);
end;
procedure qa_plan_elements_proc
(P_Errbuf OUT VARCHAR2,
P_Retcode OUT NUMBER
)
is
cursor c_qa_elements is select * from MLGWEAM_QA_ELEMENTS_TBL
where 1=1
and ELEMENT_NAME != 'COMPACTION_FAILURE_WO';
x_v_plan_id number;
x_v_msg_count number;
x_v_msg_data varchar2(4000);
x_v_return_status varchar2(1000);
x_user_name varchar2(100);
v_plan_id number;
x_error_flag varchar2(1);
x_default_value varchar2(10);
x_mandatory_flag varchar2(10);
x_enabled_flag varchar2(10);
x_displayed_flag varchar2(10);
begin
FND_GLOBAL.APPS_INITIALIZE (G_USER_ID,
G_RESPONSIBILITY_ID,
G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'User Id : ' || G_USER_ID);
FND_FILE.PUT_LINE (FND_FILE.log, 'Responsibility Id : ' || G_RESPONSIBILITY_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'Resp Application Id : ' || G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request ID : ' || G_REQUEST_ID );
null;
x_error_flag := null;
begin
select user_name
into x_user_name
from fnd_user
where 1=1
and user_id = G_USER_ID;
exception
when others then
x_error_flag := 'E';
fnd_file.put_line(fnd_file.log,'Error While selection username '||SQLERRM);
end;
begin
select plan_id
into v_plan_id
from qa_plans
where 1=1
and name = 'PAVEMENT REPLACEMENT';
exception
when no_data_found then
null;
when others then
x_error_flag := 'E';
fnd_file.put_line(fnd_file.log,'Error While selection of PAVEMENT REPLACEMENT plan '||SQLERRM);
end;
fnd_file.put_line(fnd_file.log,'Org id is collec '||g_org_id);
if x_error_flag is null and v_plan_id is not null then
fnd_file.put_line(fnd_file.log,'2');
for cx_qa_elements in c_qa_elements loop
fnd_file.put_line(fnd_file.log,'1');
x_default_value:= null;
x_mandatory_flag := null;
x_enabled_flag := null;
x_displayed_flag := null;
begin
select decode(cx_qa_elements.DEFAULT_VALUE,null,qa_plans_pub.g_inherit,cx_qa_elements.DEFAULT_VALUE)
into x_default_value
from dual;
end;
begin
select decode(cx_qa_elements.MANDATORY_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into x_mandatory_flag
from dual;
end;
begin
select decode(cx_qa_elements.ENABLED_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)--ok
into x_enabled_flag
from dual;
end;
begin
select decode(cx_qa_elements.DISPLAYED_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into x_displayed_flag from dual;
end;
qa_plans_pub.add_plan_element
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_user_name => x_user_name ,
p_plan_name => 'PAVEMENT REPLACEMENT',
p_organization_code => 'LGW',
p_element_name => cx_qa_elements.ELEMENT_NAME,
p_prompt_sequence => cx_qa_elements.PROMPT_SEQUENCE,
p_prompt => cx_qa_elements.P_PROMPT,
p_default_value => x_default_value,
p_enabled_flag => x_enabled_flag,
p_mandatory_flag => x_mandatory_flag ,
p_displayed_flag => x_displayed_flag ,
p_result_column_name => cx_qa_elements.RESULT_COLUMN_NAME,
x_msg_count => x_v_msg_count,
x_msg_data => x_v_msg_data,
x_return_status => x_v_return_status
);
fnd_file.put_line(fnd_file.log,'Return Status '||x_v_return_status||' and Return Message '||x_v_msg_data);
end loop;
end if;
commit;
end;
END MLGWEAM_QA_PLANS_ELEMENTS_PKG;
/
Could you Please share how to delete specification element and also add both
ReplyDelete