create or replace PACKAGE XXCUSTOM_EAM_COLLCT_PN_CONV_PKG IS
/*
---------------------------Quality Collection Plan Set-up ----------------------
*/
gc_request_id NUMBER := fnd_global.conc_request_id;
gc_login_id NUMBER := fnd_global.login_id;
gc_user_id NUMBER := fnd_global.user_id;
PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_process_flag IN VARCHAR2);
PROCEDURE validate_data;
PROCEDURE collection_plan_process;
PROCEDURE add_collection_plan_element;
END XXCUSTOM_EAM_COLLCT_PN_CONV_PKG ;
create or replace PACKAGE BODY XXCUSTOM_EAM_COLLCT_PN_CONV_PKG IS
/***************** Main Procedure Starts *********************/
PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_process_flag IN VARCHAR2) IS
BEGIN
IF p_process_flag = 'V' THEN
validate_data;
END IF;
IF p_process_flag = 'P' THEN
collection_plan_process;
add_collection_plan_element;
END IF;
EXCEPTION
WHEN OTHERS THEN
errbuf := 'Exception in Main - ' || SQLERRM;
retcode := 2;
fnd_file.put_line(fnd_FILE.LOG,
' Error Occured in The Procedure MAIN');
END MAIN;
/****************** Validate Procedure Starts *******************/
PROCEDURE validate_data
IS
CURSOR c_stg_data IS
SELECT rowid row_id, stg.*
FROM XXCUSTOM_EAM_COLLCT_PN_STG STG
WHERE PROCESS_FLAG in ( 'N', 'E');
l_organization_id NUMBER;
l_comp_item_id NUMBER;
l_assembly_item_id NUMBER;
l_supply_type_cd VARCHAR2(40);
l_subinventory VARCHAR2(10);
l_status VARCHAR2(1);
l_error_message VARCHAR2(4000);
lv_item_valid NUMBER;
l_set_id NUMBER;
l_type_code VARCHAR2(40);
l_cnt_plan NUMBER;
l_prompt_name VARCHAR2(120);
lv_cnt_data NUMBER;
BEGIN
BEGIN
UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
SET INFORMATION_FLAG = replace (INFORMATION_FLAG, CHR(13), '')
WHERE process_flag = 'N';
COMMIT;
End ;
FOR i_stg_data IN c_stg_data LOOP
l_organization_id :=null;
l_type_code :=null;
l_status :='N' ;
l_error_message :=null;
l_cnt_plan :=0;
l_prompt_name :=NULL;
lv_cnt_data :=0;
/********** Validation for Organization Code ****************/
BEGIN
SELECT organization_id
INTO l_organization_id
FROM org_organization_definitions
WHERE (upper (organization_code) = upper (i_stg_data.organization_code)
OR upper (organization_name) = upper (i_stg_data.organization_code) );
EXCEPTION
WHEN OTHERS THEN
l_status := 'E';
l_error_message := l_error_message || '-' ||
'Invalid Organization Code';
END;
/********** Validation for Collection Plan ****************/
BEGIN
Select count (*)
INTO lv_cnt_data
From (
SELECT
organization_code, plan_name,
description, effective_from_date,
effective_to_date, plan_type,
display_multi_flag
FROM XXCUSTOM_eam_collct_pn_stg stg
WHERE PLAN_NAME = i_stg_data.PLAN_NAME
GROUP BY
organization_code, plan_name,
description, effective_from_date,
effective_to_date, plan_type,
display_multi_flag
) ;
IF lv_cnt_data <> 1 THEN
l_status := 'E';
l_error_message := l_error_message || '-' ||
' Collection Plan must be uniq ';
END IF;
END;
/********** Validation for Collection Plan ****************/
BEGIN
SELECT count (*)
INTO l_cnt_plan
FROM QA_PLANS
WHERE upper (NAME) = upper (i_stg_data.PLAN_NAME);
IF l_cnt_plan <> 0 THEN
l_status := 'E';
l_error_message := l_error_message || '-' ||
'Invalid Collection Plan Code';
END IF;
END;
/********** Validation for Element Name ****************/
BEGIN
SELECT PROMPT
INTO l_prompt_name
FROM QA_CHARS
WHERE NAME = i_stg_data.ELEMENT_NAME;
EXCEPTION
WHEN OTHERS THEN
l_status := 'E';
l_error_message := l_error_message || '-' ||
'Invalid Element Name ';
END;
/***************** Flag Updation , Validation Ends ******************/
IF l_status = 'E' THEN
UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
SET PROCESS_FLAG = l_status,
error_message = l_error_message
WHERE rowid = i_stg_data.row_id;
---fnd_file.put_line(fnd_file.log,' Schedule name --: ' || i_stg_data.schedule_name ||' :- validation failed Details :-'||l_error_message);
ELSE
UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
SET PROCESS_FLAG = 'V',
error_message = 'Record Validate',
organization_id = l_organization_id,
prompt_name = l_prompt_name,
creation_date = SYSDATE,
last_updated_date = SYSDATE ,
last_updated_by = gc_user_id
WHERE rowid = i_stg_data.row_id;
END IF;
COMMIT;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,
'Validate Procedure Main Exception - ' || SQLERRM);
END validate_data;
/********************* Process BOM Procedure Starts ********************/
PROCEDURE collection_plan_process IS
CURSOR cur_upload_data IS
SELECT
organization_code,
plan_name,
description,
effective_from_date,
effective_to_date,
plan_type,
display_multi_flag
FROM
XXCUSTOM_eam_collct_pn_stg stg
WHERE
process_flag = 'V'
AND plan_id IS NULL
GROUP BY
organization_code,
plan_name,
description,
effective_from_date,
effective_to_date,
plan_type ,
display_multi_flag ;
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;
lv_plan_id number;
l_msg_count number;
l_msg_data varchar2(4000);
l_return_status varchar2(1000);
l_multi_flag number ;
BEGIN
fnd_global.apps_initialize(fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id
);
FOR i_api IN cur_upload_data
LOOP
l_multi_flag :=NULL;
lv_plan_id :=NULL;
l_msg_data :=NULL;
l_msg_count :=NULL;
l_return_status :=NULL;
/*
Select decode ( upper (i_api.display_multi_flag), 'YES', 2, 'NO', 1, 2 )
INTO l_multi_flag
FROM dual;
*/
l_all_count := l_all_count + 1;
BEGIN
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 => fnd_global.user_name ,
p_plan_name => i_api.plan_name,
p_organization_code => i_api.organization_code,
p_plan_type => i_api.plan_type,
p_description => i_api.description,
p_effective_from => i_api.effective_from_date,
p_effective_to => i_api.effective_to_date,
p_spec_assignment_type => qa_plans_pub.g_spec_type_none,
p_multirow_flag => 2, --l_multi_flag,
x_plan_id => lv_plan_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status
);
commit;
IF l_return_status =fnd_api.g_ret_sts_success
THEN
UPDATE XXCUSTOM_eam_collct_pn_stg
SET process_flag = 'P',
error_message = 'Callection Record Created',
PLAN_ID = lv_plan_id,
last_updated_date = SYSDATE ,
last_updated_by = gc_user_id
WHERE plan_name = i_api.plan_name;
l_success_count := l_success_count + 1;
ELSE
IF l_msg_count >= 1
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
END LOOP;
END IF;
UPDATE XXCUSTOM_eam_collct_pn_stg
SET process_flag = 'E1',
error_message = l_msg_data,
last_updated_date = SYSDATE ,
last_updated_by = gc_user_id
WHERE plan_name = i_api.plan_name;
l_failure_count := l_failure_count + 1;
END IF;
COMMIT;
dbms_output.put_line ('Return Status '||l_return_status||' and Return Message '||l_msg_data);
END;
END LOOP;
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
fnd_file.put_line (fnd_file.log,' Total Record count : '||L_ALL_COUNT);
fnd_file.put_line (fnd_file.log,' Total Success Record Count : '||L_SUCCESS_COUNT);
fnd_file.put_line (fnd_file.log,' Total Fail Record Count :'||L_FAILURE_COUNT);
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
fnd_file.put_line(fnd_file.log,
'+********************** Exception found in XXCUSTOM_EAM_COLLCT_PN_CONV_PKG.MAIN ----> Process Data Procedure - ' || SQLERRM);
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
END collection_plan_process;
PROCEDURE add_collection_plan_element
IS
CURSOR cur_upload_data IS
SELECT
rowid row_id, stg.*
FROM
XXCUSTOM_eam_collct_pn_stg stg
WHERE
process_flag = 'P'
AND plan_id IS NOT NULL ;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(1000);
l_default_value VARCHAR2(10);
l_mandatory_flag VARCHAR2(10);
l_enabled_flag VARCHAR2(10);
l_displayed_flag VARCHAR2(10);
l_read_only_flag VARCHAR2(10);
l_information_flag VARCHAR2(10);
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;
BEGIN
BEGIN
FOR i_api IN cur_upload_data
LOOP
l_default_value :=NULL;
l_mandatory_flag :=NULL;
l_enabled_flag :=NULL;
l_displayed_flag :=NULL;
l_read_only_flag :=NULL;
l_enabled_flag :=NULL;
l_information_flag :=NULL;
l_msg_data :=NULL;
l_msg_count :=NULL;
l_return_status :=NULL;
l_all_count := l_all_count + 1;
/*
begin
select --- decode(i_api.DEFAULT_VALUE,null,qa_plans_pub.g_inherit,cx_qa_elements.DEFAULT_VALUE)
qa_plans_pub.g_inherit
into l_default_value
from dual;
end;
*/
begin
select decode(i_api.mandatory_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into l_mandatory_flag
from dual;
end;
begin
select decode(i_api.enabled_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)--ok
into l_enabled_flag
from dual;
end;
begin
select decode(i_api.read_only_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into l_read_only_flag
from dual;
end;
begin
select decode(i_api.displayed_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into l_displayed_flag
from dual;
end;
begin
select decode(i_api.information_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
into l_information_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 => fnd_global.user_name ,
p_plan_name => i_api.plan_name,
p_organization_code => i_api.organization_code,
p_element_name => i_api.element_name,
p_prompt_sequence => NULL,-- i_api.prompt_seq,
p_prompt => i_api.prompt_name,
p_default_value => l_default_value,
p_enabled_flag => l_enabled_flag,
p_mandatory_flag => l_mandatory_flag ,
p_displayed_flag => l_displayed_flag ,
p_read_only_flag => l_read_only_flag ,
p_information_flag => l_information_flag ,
p_result_column_name => NULL, --'ATTRIBUTE',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status
);
COMMIT;
IF l_return_status =fnd_api.g_ret_sts_success
THEN
UPDATE XXCUSTOM_eam_collct_pn_stg
SET PROCESS_FLAG = 'S',
error_message = 'Callection and Element Created',
last_updated_date = SYSDATE ,
last_updated_by = gc_user_id
WHERE rowid = i_api.row_id;
l_success_count := l_success_count + 1;
ELSE
IF l_msg_count >= 1
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
END LOOP;
END IF;
UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
SET PROCESS_FLAG = 'E1',
error_message = l_msg_data,
last_updated_date = SYSDATE ,
last_updated_by = gc_user_id
WHERE rowid = i_api.row_id;
l_failure_count := l_failure_count + 1;
END IF;
END LOOP;
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
fnd_file.put_line (fnd_file.log,' Total Record count : '||L_ALL_COUNT);
fnd_file.put_line (fnd_file.log,' Total Success Record Count : '||L_SUCCESS_COUNT);
fnd_file.put_line (fnd_file.log,' Total Fail Record Count :'||L_FAILURE_COUNT);
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
END ;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
fnd_file.put_line(fnd_file.log,
'+********************** Exception found in XXCUSTOM_eam_collct_pn_conv_pkg.add_collection_plan_element ----> Process Data Procedure - ' || SQLERRM);
fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
END add_collection_plan_element;
END XXCUSTOM_EAM_COLLCT_PN_CONV_PKG;
CSV DATA FILE ---
Output file.
