CREATE OR REPLACE PACKAGE BODY APPS.xxdc_cz_case
IS
/***************************************************************************************************************************/
/** NAME: APPS.XXDC_CZ_CASE
/** PURPOSE: This is the package to create Load Inspection cases in DORIIS EBS Ssystem.
/**
/**
/** REVISIONS:
/** Ver Date Author JIRA#/CR# Description
/** --------- ----------- --------------- ---------- ------------------------------------
/** 1.0 3/1/2010 B. Sunil KUMAR 2530 Initial Revision
/**
/***************************************************************************************************************************/
FUNCTION get_constant_val(p_constant_name IN VARCHAR2) RETURN VARCHAR2 IS
v_value VARCHAR2(1000);
BEGIN
Execute Immediate 'BEGIN :x := '||gc_pkg||'.'||p_constant_name||'; end;'
USING OUT v_value;
return v_value;
END;
FUNCTION validate_event_code (
p_dealer_id IN VARCHAR2
,p_zone_number IN NUMBER
)
RETURN NUMBER
IS
v_result VARCHAR2 (50);
v_cnt NUMBER := 0;
BEGIN
/***************************************************************************************************************************/
/** PURPOSE: Checking the event codes for Approved case ot not .
/**
/***************************************************************************************************************************/
v_result := NULL;
v_cnt := 0;
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code IN
(gc_code_2000
,gc_code_2024
,gc_code_2025
,gc_code_2040
,gc_code_2001
,gc_code_2010
);
IF v_cnt = 4
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code IN
(gc_code_2000, gc_code_2024, gc_code_2025, gc_code_2040);
IF v_cnt = 4
THEN
BEGIN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2000;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2024;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2025;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2040;
IF v_cnt = 1
THEN
v_result := 'SUCCESS';
END IF;
END IF;
END IF;
END IF;
END;
ELSE
v_cnt := 0;
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code IN
(gc_code_2001, gc_code_2024, gc_code_2025, gc_code_2010);
IF v_cnt = 4
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2001;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2010;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2024;
IF v_cnt = 1
THEN
SELECT COUNT (*)
INTO v_cnt
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2025;
IF v_cnt = 1
THEN
v_result := 'SUCCESS';
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
IF NVL (v_result, 'N') = 'SUCCESS'
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END validate_event_code;
FUNCTION get_case_resoultion (
p_dealer_id IN VARCHAR2
,p_zone_number IN VARCHAR2
)
RETURN VARCHAR2
IS
v_cnt NUMBER := 0;
BEGIN
SELECT COUNT (*)
INTO v_cnt
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2000)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2024)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2025)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2040);
IF v_cnt = 1
THEN
RETURN 'APPROVED';
ELSE
SELECT COUNT (*)
INTO v_cnt
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2001)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2024)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2025)
AND EXISTS (
SELECT 1
FROM xxdc_cz_case_sm
WHERE zone_number = p_zone_number
AND dealer_id = p_dealer_id
AND event_code = gc_code_2010);
IF v_cnt = 1
THEN
RETURN 'DENIED';
ELSE
RETURN 'NOT VALID';
END IF;
END IF;
RETURN 'ERROR';
END get_case_resoultion;
PROCEDURE get_case_party_info (
p_caller IN VARCHAR2
,p_legacy_acct_num IN VARCHAR2
,p_party_id OUT NUMBER
,p_party_type OUT VARCHAR2
,p_account_id OUT NUMBER
,p_party_site_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_CASE_PARTY_INFO';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
SELECT hp.party_id, hp.party_type, hps.party_site_id
,hca.cust_account_id
INTO p_party_id, p_party_type, p_party_site_id
,p_account_id
FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hps.identifying_address_flag = 'Y'
AND hca.account_number = p_legacy_acct_num;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_party_id := -1;
p_party_type := NULL;
p_account_id := -1;
p_party_site_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_id := -2;
p_party_type := NULL;
p_account_id := -2;
p_party_site_id := -2;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text
,p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| 'Party_info '
|| ', error: '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech
,p_prog_unit => v_prog_unit
,p_mesg => gv_debug_log_text
);
END get_case_party_info;
PROCEDURE get_case_bill_to_site_use_id (
p_caller IN VARCHAR2
,p_legacy_acct_num IN VARCHAR2
,p_bill_to_party_id OUT NUMBER
,p_cust_acct_id OUT NUMBER
,p_bill_to_site_id OUT NUMBER
,p_bill_to_site_use_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_CASE_BILL_TO_SITE_USE_ID';
BEGIN
SELECT ps.party_site_id, pu.party_site_use_id, ca.cust_account_id
,hp.party_id
INTO p_bill_to_site_id, p_bill_to_site_use_id, p_cust_acct_id
,p_bill_to_party_id
FROM hz_parties hp
,hz_cust_accounts_all ca
,hz_party_sites ps
,hz_party_site_uses pu
WHERE ca.account_number = p_legacy_acct_num
AND ca.party_id = hp.party_id
AND ps.party_id = ca.party_id
AND pu.party_site_id = ps.party_site_id
AND ps.identifying_address_flag = 'Y'
AND pu.site_use_type = 'BILL_TO'
AND pu.primary_per_type = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_bill_to_party_id := -1;
p_bill_to_site_id := -1;
p_bill_to_site_use_id := -1;
p_cust_acct_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_bill_to_party_id := -2;
p_bill_to_site_id := -2;
p_bill_to_site_use_id := -2;
p_cust_acct_id := -2;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text
,p_new_text => xxd_utils.gc_exception
|| 'p_caller - '
|| p_caller
|| v_prog_unit
|| ', error: '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech
,p_prog_unit => v_prog_unit
,p_mesg => gv_debug_log_text
);
END get_case_bill_to_site_use_id;
FUNCTION get_instance_id (p_zone_number IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INSTANCE_ID';
v_instance_id csi_item_instances.instance_id%TYPE;
BEGIN
SELECT instance_id
INTO v_instance_id
FROM csi_item_instances
WHERE external_reference = p_zone_number;
RETURN v_instance_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_instance_id;
PROCEDURE create_cz_case (
p_service_request_rec IN cs_servicerequest_pub.service_request_rec_type
,p_notes_table IN cs_servicerequest_pub.notes_table
,p_contacts_table IN cs_servicerequest_pub.contacts_table
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_sr_create_out_rec OUT NOCOPY cs_servicerequest_pub.sr_create_out_rec_type
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_CZ_CASE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
cs_servicerequest_pub.create_servicerequest
(p_api_version => 4.0
--,p_init_msg_list => 'T'
, p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_resp_appl_id => fnd_global.resp_appl_id
,p_resp_id => fnd_global.resp_id
,p_user_id => fnd_global.user_id
,p_login_id => fnd_global.login_id
,p_org_id => fnd_global.org_id
,p_request_id => NULL
--fnd_global.conc_request_id
, p_service_request_rec => p_service_request_rec
,p_notes => p_notes_table
,p_contacts => p_contacts_table
,p_auto_assign => 'N'
,p_auto_generate_tasks => 'N'
,x_sr_create_out_rec => x_sr_create_out_rec
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END create_cz_case;
-- PROCEDURE create_task (
-- p_qc_rev_start_dt IN VARCHAR2
-- ,p_qc_rev_end_dt IN VARCHAR2
-- ,p_record_no IN VARCHAR2
-- ,p_file_no IN VARCHAR2
-- ,
-- --p_owner_id IN NUMBER,
-- p_incident_id IN NUMBER
-- ,p_task_status IN VARCHAR2
-- ,x_return_status OUT VARCHAR2
-- ,x_msg_count OUT NUMBER
-- ,x_msg_data OUT VARCHAR2
-- ,x_task_id OUT NUMBER
-- )
-- IS
-- v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_TASK';
-- v_task_type_id jtf_task_types_vl.task_type_id%TYPE;
-- v_task_status_id jtf_task_statuses_vl.task_status_id%TYPE;
-- v_task_priority_id jtf_task_priorities_vl.task_priority_id%TYPE;
-- v_incident_number cs_incidents_all_vl.incident_number%TYPE;
-- BEGIN
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => xxd_utils.gc_start
-- ,p_prog_unit => v_prog_unit
-- );
-- -- Select task status from JTF_TASK_STATUSES_vl.
-- SELECT task_status_id
-- INTO v_task_status_id
-- FROM jtf_task_statuses_vl
-- WHERE UPPER (NAME) = UPPER (p_task_status);
-- -- Select task type from JTF_TASK_TYPES_vl.
-- SELECT task_type_id
-- INTO v_task_type_id
-- FROM jtf_task_types_tl
-- WHERE LANGUAGE = 'US' AND UPPER (NAME) = UPPER (gc_task_type);
-- /*
-- -- Select task priority
-- SELECT task_priority_id
-- INTO l_task_priority_id
-- FROM jtf_task_priorities_tl
-- WHERE LANGUAGE = 'US' AND UPPER (NAME) = UPPER (p_task_priority);
-- */
-- -- Get the incident number from cs_inicdents_all_b
-- SELECT incident_number
-- INTO v_incident_number
-- FROM cs.cs_incidents_all_b
-- WHERE incident_id = p_incident_id;
-- -- Call The public API
-- jtf_tasks_pub.create_task
-- (p_api_version => 1.0
-- ,p_init_msg_list => fnd_api.g_false
-- ,p_commit => fnd_api.g_false
-- ,p_task_name => gc_task_name
-- ,p_task_type_id => v_task_type_id
-- ,p_description => gc_task_name
-- ,p_task_status_id => v_task_status_id
-- --,p_task_priority_id => l_task_priority_id
-- , p_owner_type_code => 'RS_EMPLOYEE'
-- --,p_owner_id => p_owner_id
-- , p_source_object_type_code => 'SR'
-- ,p_source_object_id => p_incident_id
-- ,p_source_object_name => v_incident_number
-- --,p_planned_start_date => to_date(p_qc_rev_start_dt, xxd_utils.xxd_utils.gc_dor_dt_format)
-- --,p_planned_end_date => to_date(p_qc_rev_end_dt, xxd_utils.xxd_utils.gc_dor_dt_format)
-- , p_actual_start_date => TO_DATE
-- (p_qc_rev_start_dt
-- ,xxd_utils.gc_dor_dt_format
-- )
-- ,p_actual_end_date => TO_DATE
-- (p_qc_rev_end_dt
-- ,xxd_utils.gc_dor_dt_format
-- )
-- ,p_task_assign_tbl => jtf_tasks_pub.g_miss_task_assign_tbl
-- ,p_task_depends_tbl => jtf_tasks_pub.g_miss_task_depends_tbl
-- ,p_task_rsrc_req_tbl => jtf_tasks_pub.g_miss_task_rsrc_req_tbl
-- ,p_task_refer_tbl => jtf_tasks_pub.g_miss_task_refer_tbl
-- ,p_task_dates_tbl => jtf_tasks_pub.g_miss_task_dates_tbl
-- ,p_task_notes_tbl => jtf_tasks_pub.g_miss_task_notes_tbl
-- ,p_task_recur_rec => jtf_tasks_pub.g_miss_task_recur_rec
-- ,p_task_contacts_tbl => jtf_tasks_pub.g_miss_task_contacts_tbl
-- ,x_return_status => x_return_status
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
-- ,x_task_id => x_task_id
-- );
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => xxd_utils.gc_complete
-- ,p_prog_unit => v_prog_unit
-- );
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- x_return_status := fnd_api.g_ret_sts_error;
-- x_msg_data :=
-- 'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => xxd_utils.gc_exception
-- || v_prog_unit
-- || ', error: '
-- || SQLCODE
-- || ' - '
-- || SQLERRM
-- ,p_prog_unit => v_prog_unit
-- );
-- END create_task;
PROCEDURE get_party_info (
p_zone_number IN VARCHAR2
,p_party_id OUT NUMBER
,p_party_type OUT VARCHAR2
,p_party_site_id OUT NUMBER
,p_account_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_PARTY_INFO';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
p_party_id := NULL;
p_party_site_id := NULL;
p_party_type := NULL;
p_account_id := NULL;
SELECT owner_party_id, owner_party_account_id, location_id
INTO p_party_id, p_account_id, p_party_site_id
FROM csi_item_instances
WHERE external_reference = p_zone_number;
SELECT party_type
INTO p_party_type
FROM hz_parties
WHERE party_id = p_party_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'No instance is exist for zone_number: '
|| p_zone_number
,p_prog_unit => v_prog_unit
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
END get_party_info;
PROCEDURE create_note (
p_incident_id IN cs_incidents_all_b.incident_id%TYPE
,p_note_detail IN jtf_notes_tl.notes_detail%TYPE
,p_note_enter_date IN jtf_notes_tl.creation_date%TYPE
DEFAULT SYSDATE
,gr_return_status OUT NOCOPY VARCHAR2
,gr_msg_count OUT NOCOPY NUMBER
,gr_msg_data OUT NOCOPY VARCHAR2
,p_jtf_note_id OUT NOCOPY jtf_notes_tl.jtf_note_id%TYPE
)
IS
v_notes jtf_notes_tl.notes%TYPE;
v_note_type fnd_lookup_values.meaning%TYPE;
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_NOTE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
v_note_type := 'COMP:COMMENT'; --'Conversion : Load Inspection';
v_notes := SUBSTR (p_note_detail, 1, 2000);
jtf_notes_pub.create_note (p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,p_org_id => fnd_global.org_id
,p_source_object_id => p_incident_id
,p_source_object_code => 'SR'
,p_notes => v_notes
,p_notes_detail => p_note_detail
,p_note_status => 'I'
,p_entered_by => fnd_global.user_id
,p_entered_date => p_note_enter_date
,x_jtf_note_id => p_jtf_note_id
,p_last_update_date => SYSDATE
,p_last_updated_by => fnd_global.user_id
,p_creation_date => SYSDATE
,p_created_by => fnd_global.user_id
,p_note_type => v_note_type
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gr_return_status := fnd_api.g_ret_sts_error;
gr_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END create_note;
PROCEDURE update_case_status (
p_incident_id NUMBER
,p_incident_close_date DATE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.UPDATE_CASE_STATUS';
v_obj_ver cs_incidents_all_b.object_version_number%TYPE;
v_incident_status_id cs_incidents_all_b.incident_status_id%TYPE;
x_interaction_id NUMBER := NULL;
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
SELECT object_version_number
INTO v_obj_ver
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT incident_status_id
INTO v_incident_status_id
FROM cs_incident_statuses_vl
WHERE UPPER (NAME) = gc_case_status_closed
AND incident_subtype = gc_inc_subtype;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
cs_servicerequest_pub.update_status
(p_api_version => 2.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_resp_appl_id => fnd_global.resp_appl_id
,p_resp_id => fnd_global.resp_id
,p_user_id => fnd_global.user_id
,p_login_id => NULL
,p_request_id => p_incident_id
,p_request_number => NULL
,p_object_version_number => v_obj_ver
,p_status_id => v_incident_status_id
,p_status => NULL
,p_closed_date => p_incident_close_date
,p_audit_comments => NULL
,p_called_by_workflow => NULL
,p_workflow_process_id => NULL
,p_comments => NULL
,p_public_comment_flag => NULL
,x_interaction_id => x_interaction_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END;
PROCEDURE process_case_ext_attrs (
p_incident_id IN NUMBER
,p_ext_attr_grp_tbl IN cs_servicerequest_pub.ext_attr_grp_tbl_type
,p_ext_attr_tbl IN cs_servicerequest_pub.ext_attr_tbl_type
,x_failed_row_id_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.PROCESS_CASE_EXT_ATTRS';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
cs_servicerequest_pub.process_sr_ext_attrs
(p_api_version => 1.0
,p_init_msg_list => gc_t
,p_commit => gc_commit_flag
,p_incident_id => p_incident_id
,p_ext_attr_grp_tbl => p_ext_attr_grp_tbl
,p_ext_attr_tbl => p_ext_attr_tbl
,p_modified_by => fnd_global.user_id
,p_modified_on => SYSDATE
,x_failed_row_id_list => x_failed_row_id_list
,x_return_status => gr_return_status
,x_errorcode => x_errorcode
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gr_return_status := fnd_api.g_ret_sts_error;
gr_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END process_case_ext_attrs;
PROCEDURE update_case_assosiation (
p_incident_id IN cs_incidents_all_b.incident_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_contacts_table IN cs_servicerequest_pub.contacts_table
)
IS
p_version_number cs_incidents_all_b.object_version_number%TYPE;
v_prog_unit VARCHAR (100)
:= gc_pkg || '.UPDATE_CASE_ASSOSIATIONS';
v_service_request_rec cs_servicerequest_pub.service_request_rec_type;
v_notes_table cs_servicerequest_pub.notes_table;
x_workflow_process_id NUMBER;
x_interaction_id NUMBER;
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
cs_servicerequest_pub.initialize_rec (v_service_request_rec);
v_notes_table.DELETE;
SELECT object_version_number
INTO p_version_number
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
cs_servicerequest_pub.update_servicerequest
(p_api_version => 3.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_request_id => p_incident_id
,p_request_number => NULL
,p_audit_comments => 'Creating assosiations'
,p_object_version_number => p_version_number
,p_resp_appl_id => fnd_global.resp_appl_id
,p_resp_id => fnd_global.resp_id
,p_last_updated_by => fnd_global.user_id
,p_last_update_login => NULL
,p_last_update_date => SYSDATE
,p_service_request_rec => v_service_request_rec
,p_notes => v_notes_table
,p_contacts => p_contacts_table
,p_called_by_workflow => fnd_api.g_false
,p_workflow_process_id => NULL
,x_workflow_process_id => x_workflow_process_id
,x_interaction_id => x_interaction_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END update_case_assosiation;
PROCEDURE prevalidation (
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_debug IN VARCHAR2 DEFAULT 'Y'
,p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.PREVALIDATION';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
retcode := 0;
-----------------------------------------------------------------------------------------
-- Pre Validation generic procedure to check for pre-validations and configuration settings
-----------------------------------------------------------------------------------------
--xxdc_p2_conv_util.gen_pre_validation (gc_conv_id, p_request_id);
xxdc_p2_conv_util.prevalidation (p_conv_id => gc_conv_id
,p_request_id => p_request_id
,p_log => gv_debug_log_clob
,p_result => retcode
);
----------------------------------------------------------------------------------------
-- Data Type check staging table data with target table column data type.
-----------------------------------------------------------------------------------------
xxdc_p2_conv_util.gen_datatype_validation (gc_conv_id, p_request_id);
--update_processstatus (p_request_id);
UPDATE xxdc_cz_case_ex
SET process_status =
DECODE (process_status
,'N', 'P'
,process_status
);
UPDATE xxdc_cz_case_sm
SET process_status =
DECODE (process_status
,'N', 'P'
,process_status
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG
,p_retcode => retcode
,p_debug => p_debug
,p_stuff => gv_debug_log_clob
,p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG
,p_retcode => retcode
,p_debug => p_debug
,p_stuff => gv_debug_log_clob
,p_result => gv_result
);
END;
FUNCTION get_case_date (p_dealer_id IN VARCHAR2, p_zone_number IN VARCHAR2)
RETURN VARCHAR2
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_CASE_DATE';
i NUMBER := 0;
j NUMBER := 0;
v_case_date xxdc_cz_case_sm.event_date%TYPE := NULL;
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
SELECT COUNT (*)
INTO i
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2040;
IF i = 1
THEN
SELECT event_date
INTO v_case_date
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2040;
ELSE
SELECT event_date
INTO v_case_date
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2001;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
RETURN v_case_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_case_date;
END get_case_date;
FUNCTION get_case_close_date (
p_dealer_id IN VARCHAR2
,p_zone_number IN VARCHAR2
)
RETURN VARCHAR2
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_CASE_CLOSE_DATE';
i NUMBER := 0;
j NUMBER := 0;
v_case_close_date xxdc_cz_case_sm.event_date%TYPE := NULL;
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
SELECT COUNT (*)
INTO i
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2040;
IF i = 1
THEN
SELECT event_date
INTO v_case_close_date
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2000;
ELSE
SELECT event_date
INTO v_case_close_date
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2010;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
RETURN v_case_close_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_case_close_date;
END get_case_close_date;
FUNCTION get_resolution_summ (
p_dealer_id IN VARCHAR2
,p_zone_number IN VARCHAR2
)
RETURN VARCHAR2
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_RESOLUTION_SUMM';
v_resolution_summary cs_incidents_all_b.resolution_description%TYPE
:= NULL;
v_dz_type VARCHAR2 (50) := NULL;
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
SELECT dz_type
INTO v_dz_type
FROM xxdc_cz_case_sm
WHERE dealer_id = p_dealer_id
AND zone_number = p_zone_number
AND event_code = gc_code_2010;
IF NVL (v_dz_type, 'N') = gc_nf
THEN
v_resolution_summary := 'Deactive - Not Full Line';
ELSIF NVL (v_dz_type, 'N') = gc_lt
THEN
v_resolution_summary := 'Deactive - Sales Threshold';
ELSE
v_resolution_summary := NULL;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
RETURN v_resolution_summary;
EXCEPTION
WHEN OTHERS
THEN
v_resolution_summary := 'ERROR';
RETURN v_resolution_summary;
END get_resolution_summ;
PROCEDURE convert_sm_data(p_request_id in number)
IS
v_incident_type_id cs_incidents_all_b.incident_type_id%TYPE;
v_incident_status_id cs_incidents_all_b.incident_status_id%TYPE;
v_incident_severity_id cs_incidents_all_b.incident_severity_id%TYPE;
v_incident_urgency_id cs_incidents_all_b.incident_urgency_id%TYPE;
v_customer_id cs_incidents_all_b.customer_id%TYPE;
v_account_id cs_incidents_all_b.account_id%TYPE;
v_owner_group_id cs_incidents_all_b.owner_group_id%TYPE;
v_party_role_type cs_party_roles_tl.party_role_code%TYPE;
v_resoluton_code cs_incidents_all_b.resolution_code%TYPE;
v_problem_code cs_incidents_all_b.problem_code%TYPE;
p_ext_attr_grp_tbl cs_servicerequest_pub.ext_attr_grp_tbl_type;
p_ext_attr_tbl cs_servicerequest_pub.ext_attr_tbl_type;
v_twi_attr_group_id ego_fnd_dsc_flx_ctx_ext.attr_group_id%TYPE
:= NULL;
v_source_attr_group_id ego_fnd_dsc_flx_ctx_ext.attr_group_id%TYPE
:= NULL;
v_attr_group_id ego_fnd_dsc_flx_ctx_ext.application_id%TYPE
:= NULL;
v_attr_group_type ego_attr_groups_v.attr_group_type%TYPE;
v_incident_location_id cs_incidents_all_b.incident_location_id%TYPE;
v_party_type hz_parties.party_type%TYPE := NULL;
v_incident_id cs_incidents_all_b.incident_id%TYPE
:= NULL;
v_incident_number cs_incidents_all_b.incident_number%TYPE
:= NULL;
v_note_detail jtf_notes_tl.notes_detail%TYPE
:= EMPTY_CLOB
();
v_case_close_date VARCHAR2 (250);
v_bill_to_party_id cs_incidents_all_b.bill_to_party_id%TYPE;
v_jtf_note_contexts_tab_dflt jtf_notes_pub.jtf_note_contexts_tbl_type;
v_notes_table cs_servicerequest_pub.notes_table;
v_contacts_table cs_servicerequest_pub.contacts_table;
v_ass_contacts_table cs_servicerequest_pub.contacts_table;
v_sr_create_out_rec cs_servicerequest_pub.sr_create_out_rec_type;
v_service_request_rec cs_servicerequest_pub.service_request_rec_type;
v_task_status_id jtf_task_statuses_vl.task_status_id%TYPE;
v_attribute_group_id ego_fnd_dsc_flx_ctx_ext.application_id%TYPE
:= NULL;
v_owner_id jtf_rs_resource_extns.resource_id%TYPE;
vr_task_id jtf_tasks_b.task_id%TYPE;
v_task_type_id jtf_task_types_tl.NAME%TYPE;
v_schedule_start_date VARCHAR2 (250);
v_actual_start_date VARCHAR2 (250);
v_application_id NUMBER := 0;
v_party_id hz_parties.party_id%TYPE;
v_party_number hz_parties.party_number%TYPE;
v_party_site_id hz_party_sites.party_site_id%TYPE;
v_inc_bill_to_site_id hz_party_sites.party_site_id%TYPE;
v_inc_bill_to_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
v_party_name hz_parties.party_name%TYPE;
v_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
v_inc_bill_acct_id cs_incidents_all_b.bill_to_account_id%TYPE
:= NULL;
v_mtl_desc VARCHAR2 (255);
x_failed_row_id_list VARCHAR2 (200) := NULL;
x_errorcode NUMBER := 0;
x_msg_index_out NUMBER;
v_prog_unit VARCHAR (100)
:= gc_pkg || '.CONVERT_SM_DATA';
v_api_err_rule_id NUMBER := -97;
v_Prog_err_rule_id NUMBER := -96;
i NUMBER := 0;
j NUMBER := 0;
x_message VARCHAR2 (4000);
v_process_status VARCHAR2 (1) := NULL;
e_exception EXCEPTION;
v_jtf_note_id jtf_notes_tl.jtf_note_id%TYPE := NULL;
v_sqlldr_req_id xxdc_p2_conv_errors.sqlload_request_id%TYPE;
retcode VARCHAR2 (1) := 0;
v_case_date xxdc_cz_case_sm.event_date%TYPE := NULL;
v_resolution_chk VARCHAR2 (20);
p_debug VARCHAR2 (1) := 'Y';
-----------------------------------------------------------------------------------------
-- Cursor to get the records after group by Dealer_id, Zone_number from xxdc_czib_sm
-----------------------------------------------------------------------------------------
CURSOR cz_case_sm_main_cur
IS
SELECT dealer_id, zone_number
FROM xxdc.xxdc_cz_case_sm
WHERE ROWNUM < 5 --zone_number = 3832
--zone_number = '1001' -- test
AND request_id = nvl(p_request_id, request_id) and process_status in ('P', 'PW','E') --test
GROUP BY dealer_id, zone_number
;
BEGIN
v_sqlldr_req_id := p_request_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
-----------------------------------------------------------------------------------------
-- case Type Validation
-----------------------------------------------------------------------------------------
v_incident_type_id :=
xxdc_p2_conv_util.get_inc_type_id (v_prog_unit, gc_sm_case_type);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_type_id: '
|| v_incident_type_id
|| ' for Case type: '
|| gc_sm_case_type
,p_prog_unit => v_prog_unit
);
v_incident_status_id :=
xxdc_p2_conv_util.get_inc_status_id (v_prog_unit
,gc_case_status_open);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_status_id for open status'
|| v_incident_status_id
,p_prog_unit => v_prog_unit
);
v_incident_severity_id :=
xxdc_p2_conv_util.get_inc_severity_id (v_prog_unit, gc_case_severity);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_severity_id: '
|| v_incident_severity_id
,p_prog_unit => v_prog_unit
);
v_incident_urgency_id :=
xxdc_p2_conv_util.get_inc_urgency_id (v_prog_unit, gc_case_urgency);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_urgency_id: '
|| v_incident_urgency_id
,p_prog_unit => v_prog_unit
);
v_owner_group_id :=
xxdc_p2_conv_util.get_inc_group_id (v_prog_unit
,'DOR Convenience Zone Group'
);
--gc_case_owner_group_name );
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_owner_group_id: '
|| v_owner_group_id
,p_prog_unit => v_prog_unit
);
v_problem_code :=
xxdc_p2_conv_util.get_inc_problem_code (v_prog_unit
,gc_sm_problem_code
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_problem_code: ' || v_problem_code
,p_prog_unit => v_prog_unit
);
SELECT resource_id
INTO v_owner_id
FROM jtf_rs_resource_extns
WHERE UPPER (source_name) = UPPER ('Ledesma, Hope');
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_owner_id: ' || v_owner_id
,p_prog_unit => v_prog_unit
);
-- test gc_sm_owner_name
FOR cz_case_sm_main_rec IN cz_case_sm_main_cur
LOOP
BEGIN
SAVEPOINT new_case;
v_process_status := NULL;
v_case_close_date := NULL;
gv_legacy_id :=
cz_case_sm_main_rec.zone_number
|| '-'
|| cz_case_sm_main_rec.dealer_id;
gv_dealer_id := NVL (cz_case_sm_main_rec.dealer_id, 'N');
gv_zone_number := NVL (cz_case_sm_main_rec.zone_number, 'N');
v_resolution_chk :=
get_case_resoultion (gv_dealer_id, gv_zone_number);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'Resolution Chk: '
|| v_resolution_chk
,p_prog_unit => v_prog_unit
);
IF v_resolution_chk = gc_approved
THEN
v_case_date := get_case_date (gv_dealer_id, gv_zone_number);
v_resoluton_code :=
xxdc_p2_conv_util.get_inc_resolution_code (v_prog_unit
,gc_approved
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_resoluton_code '
|| v_resoluton_code
,p_prog_unit => v_prog_unit
);
v_service_request_rec.incident_occurred_date :=
TO_DATE (v_case_date, xxd_utils.gc_dor_dt_format);
v_service_request_rec.resolution_summary :=
gc_case_resolution_summary;
ELSE
v_case_date := get_case_date (gv_dealer_id, gv_zone_number);
v_service_request_rec.incident_occurred_date :=
TO_DATE (v_case_date, xxd_utils.gc_dor_dt_format);
v_resoluton_code :=
xxdc_p2_conv_util.get_inc_resolution_code (v_prog_unit
,gc_approved
);
v_service_request_rec.resolution_summary :=
get_resolution_summ (gv_dealer_id, gv_zone_number);
IF NVL (v_service_request_rec.resolution_summary, 'N') = 'N'
THEN
gv_process_msg :=
'DZ_TYPE is not in "NF" or "LT" for Legacy_id: '
|| gv_legacy_id;
ELSE
RAISE e_exception;
END IF;
END IF;
xxdc_p2_conv_util.get_inc_location_info
(p_caller => v_prog_unit
,p_legacy_acct_num => gv_dealer_id
--'BM1400' --test
, p_party_id => v_party_id
,p_party_name => v_party_name
,p_party_type => v_party_type
,p_account_id => v_cust_account_id
,p_party_site_id => v_party_site_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_party_id- '
|| v_party_id
|| ' v_party_id-'
|| v_party_name
|| 'v_party_name -'
|| ' v_party_type-'
|| v_party_type
|| 'v_account_id-'
|| v_cust_account_id
,p_prog_unit => v_prog_unit
);
v_service_request_rec.summary := v_problem_code;
v_service_request_rec.problem_code := v_problem_code;
v_service_request_rec.request_date := SYSDATE;
v_service_request_rec.type_id := v_incident_type_id;
v_service_request_rec.status_id := 3100;
--v_incident_status_id; --test
v_service_request_rec.severity_id := v_incident_severity_id;
v_service_request_rec.urgency_id := v_incident_urgency_id;
v_service_request_rec.customer_id := v_party_id;
v_service_request_rec.inventory_org_id := fnd_global.org_id;
v_service_request_rec.account_id := v_cust_account_id;
v_service_request_rec.owner_group_id := v_owner_group_id;
v_service_request_rec.group_type := gc_owner_group_type;
v_service_request_rec.incident_location_id := v_party_site_id;
v_service_request_rec.resolution_code := v_resoluton_code;
v_service_request_rec.problem_code := v_problem_code;
v_service_request_rec.incident_location_type :=
gc_incident_location_type;
--v_service_request_rec.owner_id := v_owner_id;
v_service_request_rec.customer_product_id :=
get_instance_id (gv_zone_number); --8765
-- get_instance_id (cz_case_sm_main_rec.zone_number); -- test
xxdc_p2_conv_util.get_bill_to_pt_site_use_id
(p_caller => v_prog_unit
,p_legacy_acct_num => gv_dealer_id
,p_bill_to_party_id => v_bill_to_party_id
,p_cust_acct_id => v_inc_bill_acct_id
,p_bill_to_site_id => v_inc_bill_to_site_id
,p_bill_to_site_use_id => v_inc_bill_to_site_use_id
);
v_service_request_rec.bill_to_party_id := v_bill_to_party_id; ---TEST
--v_service_request_rec.bill_to_accont_id := v_inc_bill_acct_id;
v_service_request_rec.bill_to_site_id := v_inc_bill_to_site_id;
v_service_request_rec.bill_to_site_use_id :=
v_inc_bill_to_site_use_id;
v_service_request_rec.external_reference := gv_zone_number;
--cz_case_sm_main_rec.zone_number -- test
v_service_request_rec.creation_date := SYSDATE;
v_service_request_rec.created_by := fnd_global.user_id;
v_service_request_rec.caller_type := v_party_type;
v_service_request_rec.request_attribute_15 := gv_legacy_id;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
v_incident_id := NULL;
create_cz_case (p_service_request_rec => v_service_request_rec
,p_notes_table => v_notes_table
,p_contacts_table => v_contacts_table
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,x_sr_create_out_rec => v_sr_create_out_rec
);
v_incident_id := v_sr_create_out_rec.request_id;
v_incident_number := v_sr_create_out_rec.request_number;
IF (NVL (gr_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gr_msg_count, 0) > 0)
OR (NVL (v_incident_number, 'N') = 'N')
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating a Case for Legacy_ID: '
|| gv_legacy_id
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_api_err_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
ELSE
gv_process_msg :=
'Case is created for legacy_id '
|| gv_legacy_id
|| ' Case_Number : '
|| v_incident_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
-- Creating a Note to the Above Case
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
v_note_detail := NULL;
gv_process_msg := 'Note Creating Block ';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
SELECT comments
INTO v_note_detail
FROM xxdc_cz_case_sm
WHERE dealer_id = gv_dealer_id
AND zone_number = gv_zone_number
AND event_code = gc_code_2025;
gv_process_msg := 'Note:' || v_note_detail;
v_jtf_note_id := NULL;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
gv_process_msg := 'Call API to create Note';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
create_note
(p_incident_id => v_incident_id
,p_note_detail => v_note_detail
,p_note_enter_date => TO_DATE
(v_case_date
,xxd_utils.gc_dor_dt_format
)
,gr_return_status => gr_return_status
,gr_msg_count => gr_msg_count
,gr_msg_data => gr_msg_data
,p_jtf_note_id => v_jtf_note_id
);
IF (gr_return_status <> fnd_api.g_ret_sts_success)
OR (NVL (v_jtf_note_id, 0) = 0)
OR (NVL (gr_msg_count, 0) > 0)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when applying note for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_api_err_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
ELSE
gv_process_msg :=
'NOTE was successfully created Note_ID: '
|| v_jtf_note_id
|| ' for Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
--Creation of Task to the above case
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
SELECT event_date
INTO v_schedule_start_date
FROM xxdc_cz_case_sm
WHERE dealer_id = gv_dealer_id
AND zone_number = gv_zone_number
AND event_code = gc_code_2024;
SELECT event_date
INTO v_actual_start_date
FROM xxdc_cz_case_sm
WHERE dealer_id = gv_dealer_id
AND zone_number = gv_zone_number
AND event_code = gc_code_2025;
SELECT task_status_id
INTO v_task_status_id
FROM jtf_task_statuses_vl
WHERE UPPER (NAME) = UPPER (gc_sm_task_status);
-- Select task type from JTF_TASK_TYPES_vl.
SELECT task_type_id
INTO v_task_type_id
FROM jtf_task_types_tl
WHERE LANGUAGE = 'US'
AND UPPER (NAME) = UPPER (gc_sm_task_type);
--gc_task_name); -- test
SELECT resource_id
INTO v_owner_id
FROM jtf_rs_resource_extns
WHERE UPPER (source_name) = UPPER ('Ledesma, Hope');
-- upper(gc_sm_owner_name) --test
-- Get the incident number from cs_inicdents_all_b
SELECT incident_number
INTO v_incident_number
FROM cs.cs_incidents_all_b
WHERE incident_id = v_incident_id;
--Test owner id should be capture here
jtf_tasks_pub.create_task
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_task_name => gc_task_name
,p_task_type_id => v_task_type_id
,p_description => gc_task_name
,p_task_status_id => v_task_status_id
,p_owner_id => v_owner_id -- test
,p_owner_type_code => gc_task_owner_type_code
,p_source_object_type_code => gc_task_source_type_code
,p_source_object_id => v_incident_id
,p_scheduled_start_date => TO_DATE
(v_schedule_start_date
,xxd_utils.gc_dor_dt_format
)
,p_scheduled_end_date => TO_DATE
(v_schedule_start_date
,xxd_utils.gc_dor_dt_format
)
,p_actual_start_date => TO_DATE
(v_actual_start_date
,xxd_utils.gc_dor_dt_format
)
,p_actual_end_date => TO_DATE
(v_actual_start_date
,xxd_utils.gc_dor_dt_format
)
,p_task_assign_tbl => jtf_tasks_pub.g_miss_task_assign_tbl
,p_task_depends_tbl => jtf_tasks_pub.g_miss_task_depends_tbl
,p_task_rsrc_req_tbl => jtf_tasks_pub.g_miss_task_rsrc_req_tbl
,p_task_refer_tbl => jtf_tasks_pub.g_miss_task_refer_tbl
,p_task_dates_tbl => jtf_tasks_pub.g_miss_task_dates_tbl
,p_task_notes_tbl => jtf_tasks_pub.g_miss_task_notes_tbl
,p_task_recur_rec => jtf_tasks_pub.g_miss_task_recur_rec
,p_task_contacts_tbl => jtf_tasks_pub.g_miss_task_contacts_tbl
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,x_task_id => vr_task_id
);
IF (gr_return_status <> fnd_api.g_ret_sts_success)
OR (NVL (vr_task_id, 0) = 0)
OR (NVL (gr_msg_count, 0) > 0)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating task for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_api_err_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
ELSE
gv_process_msg :=
'TASK was successfully created Note_ID: '
|| v_jtf_note_id
|| ' for Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
END IF;
-- Creation of Extensible Attributes to the above case
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
--v_attribute_group_id := xxdc_p2_conv_util.GET_ATTR_GROUP_ID(gc_attr_source_group_name); TEST
SELECT attr_group_id, application_id
,attr_group_type
INTO v_attribute_group_id, v_application_id
,v_attr_group_type
FROM ego_attr_groups_v
WHERE attr_group_disp_name = 'Source'; -- test
p_ext_attr_grp_tbl (1).row_identifier := 1;
p_ext_attr_grp_tbl (1).pk_column_1 := v_incident_id;
p_ext_attr_grp_tbl (1).object_name := gc_source_object_name;
p_ext_attr_grp_tbl (1).attr_group_id := v_attribute_group_id;
p_ext_attr_grp_tbl (1).attr_group_type := v_attr_group_type;
p_ext_attr_grp_tbl (1).CONTEXT := -1;
p_ext_attr_grp_tbl (1).attr_group_name :=
gc_attr_source_group_name;
p_ext_attr_grp_tbl (1).attr_group_app_id := v_application_id;
p_ext_attr_grp_tbl (1).mapping_req := gc_n;
p_ext_attr_grp_tbl (1).operation := gc_att_operation;
p_ext_attr_tbl (1).row_identifier := 1;
p_ext_attr_tbl (1).attr_name := gc_att_source;
---test
p_ext_attr_tbl (1).attr_value_str := 'BMDS Referral'; --test
-- gc_att_source_value
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => 'Before att group -' || i
-- || j
-- ,p_prog_unit => v_prog_unit
-- );
--gc_att_source_value;
x_failed_row_id_list := NULL;
gr_return_status := NULL;
x_errorcode := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
process_case_ext_attrs
(p_incident_id => v_incident_id
,p_ext_attr_grp_tbl => p_ext_attr_grp_tbl
,p_ext_attr_tbl => p_ext_attr_tbl
,x_failed_row_id_list => x_failed_row_id_list
,x_return_status => gr_return_status
,x_errorcode => x_errorcode
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF ( gr_return_status <> fnd_api.g_ret_sts_success
OR NVL (gr_msg_count, 0) > 0
OR gr_return_status = 'U'
)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating extensible attributes for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_api_err_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
ELSE
gv_process_msg :=
'Extensible attributes was successfully created for Case_Number: '
|| v_incident_number
|| ' of the Legacy_ID: '
|| gv_legacy_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
END IF;
-- Updating a case with status closed.
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
v_case_close_date :=
get_case_close_date (gv_dealer_id, gv_zone_number);
update_case_status
(p_incident_id => v_incident_id
,p_incident_close_date => TO_DATE
(v_case_close_date
,xxd_utils.gc_dor_dt_format
)
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF ( (gr_return_status = fnd_api.g_ret_sts_success)
OR (NVL (gr_msg_count, 0) > 0)
)
THEN
gv_process_msg :=
'Updated Case Status Successfully for Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
UPDATE xxdc_cz_case_sm
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE dealer_id = gv_dealer_id
AND zone_number = gv_zone_number;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'Case was created sucessfully Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id
|| '*************************************************'
,p_prog_unit => v_prog_unit
);
COMMIT;
ELSE
v_process_status := 'E';
ROLLBACK TO new_case;
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when updating a case for Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_api_err_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO new_case;
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_prog_err_rule_id
,p_request_id
,gv_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
-- xxd_utils.write_stuff (p_type => fnd_file.LOG
-- ,p_retcode => retcode
-- ,p_debug => p_debug
-- ,p_stuff => gv_debug_log_clob
-- ,p_result => gv_result
-- );
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO new_case;
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,v_prog_err_rule_id
,p_request_id
,gv_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
-- xxd_utils.write_stuff (p_type => fnd_file.LOG
-- ,p_retcode => retcode
-- ,p_debug => p_debug
-- ,p_stuff => gv_debug_log_clob
-- ,p_result => gv_result
-- );
END convert_sm_data;
PROCEDURE convert_ex_data(p_request_id in number)
IS
/*
Procedure to convert the data of Excemption Review Cases
*/
v_incident_type_id cs_incidents_all_b.incident_type_id%TYPE;
v_incident_status_id cs_incidents_all_b.incident_status_id%TYPE;
v_incident_severity_id cs_incidents_all_b.incident_severity_id%TYPE;
v_incident_urgency_id cs_incidents_all_b.incident_urgency_id%TYPE;
v_customer_id cs_incidents_all_b.customer_id%TYPE;
v_account_id cs_incidents_all_b.account_id%TYPE;
v_owner_group_id cs_incidents_all_b.owner_group_id%TYPE;
v_party_role_code cs_party_roles_tl.party_role_code%TYPE;
v_resoluton_code cs_incidents_all_b.resolution_code%TYPE;
v_problem_code cs_incidents_all_b.problem_code%TYPE;
p_ext_attr_grp_tbl cs_servicerequest_pub.ext_attr_grp_tbl_type;
p_ext_attr_tbl cs_servicerequest_pub.ext_attr_tbl_type;
v_contact_type cs_hz_sr_contact_points.contact_type%TYPE;
v_twi_attr_group_id ego_fnd_dsc_flx_ctx_ext.attr_group_id%TYPE
:= NULL;
v_source_attr_group_id ego_fnd_dsc_flx_ctx_ext.attr_group_id%TYPE
:= NULL;
v_attr_group_id ego_fnd_dsc_flx_ctx_ext.application_id%TYPE
:= NULL;
v_incident_location_id cs_incidents_all_b.incident_location_id%TYPE;
v_party_type hz_parties.party_type%TYPE := NULL;
v_incident_id cs_incidents_all_b.incident_id%TYPE
:= NULL;
v_incident_number cs_incidents_all_b.incident_number%TYPE
:= NULL;
v_note_detail jtf_notes_tl.notes_detail%TYPE
:= EMPTY_CLOB
();
v_bill_to_party_id cs_incidents_all_b.bill_to_party_id%TYPE;
v_jtf_note_contexts_tab_dflt jtf_notes_pub.jtf_note_contexts_tbl_type;
v_notes_table cs_servicerequest_pub.notes_table;
v_contacts_table cs_servicerequest_pub.contacts_table;
v_ass_contacts_table cs_servicerequest_pub.contacts_table;
v_sr_create_out_rec cs_servicerequest_pub.sr_create_out_rec_type;
v_service_request_rec cs_servicerequest_pub.service_request_rec_type;
v_task_status_id jtf_task_statuses_vl.task_status_id%TYPE;
v_attribute_group_id ego_fnd_dsc_flx_ctx_ext.application_id%TYPE
:= NULL;
v_attr_group_type ego_attr_groups_v.attr_group_type%TYPE;
v_owner_id jtf_rs_resource_extns.resource_id%TYPE;
vr_task_id jtf_tasks_b.task_id%TYPE;
v_task_type_id jtf_task_types_tl.NAME%TYPE;
v_participant_str VARCHAR2 (250);
v_schedule_start_date VARCHAR2 (250);
v_actual_start_date VARCHAR2 (250);
v_application_id NUMBER := 0;
v_party_id hz_parties.party_id%TYPE;
v_party_number hz_parties.party_number%TYPE;
v_party_site_id hz_party_sites.party_site_id%TYPE;
v_inc_bill_to_site_id hz_party_sites.party_site_id%TYPE;
v_inc_bill_to_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
v_party_name hz_parties.party_name%TYPE;
v_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
v_inc_bill_acct_id cs_incidents_all_b.bill_to_account_id%TYPE
:= NULL;
v_account_number hz_cust_accounts.account_number%TYPE;
v_mtl_desc VARCHAR2 (255);
x_failed_row_id_list VARCHAR2 (200) := NULL;
x_errorcode NUMBER := 0;
x_msg_index_out NUMBER;
v_prog_unit VARCHAR (100)
:= gc_pkg || '.CONVERT_EX_DATA';
i NUMBER := 0;
j NUMBER := 0;
x_message VARCHAR2 (4000);
v_process_status VARCHAR2 (1) := NULL;
e_exception EXCEPTION;
v_jtf_note_id jtf_notes_tl.jtf_note_id%TYPE := NULL;
v_sqlldr_req_id xxdc_p2_conv_errors.sqlload_request_id%TYPE;
retcode VARCHAR2 (1) := 0;
v_case_date xxdc_cz_case_sm.event_date%TYPE := NULL;
v_resolution_chk VARCHAR2 (20);
p_debug VARCHAR2 (1) := 'Y';
-----------------------------------------------------------------------------------------
-- Cursor to get the records after group by Dealer_id, Zone_number from xxdc_czib_sm
-----------------------------------------------------------------------------------------
CURSOR cz_case_ex_cur
IS
SELECT *
FROM xxdc.xxdc_cz_case_ex
WHERE ROWNUM < 5
--and request_id = NVL (p_request_id, request_id)
;
BEGIN
v_sqlldr_req_id := p_request_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
-----------------------------------------------------------------------------------------
-- case Type Validation
-----------------------------------------------------------------------------------------
v_incident_type_id :=
xxdc_p2_conv_util.get_inc_type_id (v_prog_unit, gc_ex_case_type);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_type_id: '
|| v_incident_type_id
|| ' for Case type: '
|| gc_ex_case_type
,p_prog_unit => v_prog_unit
);
v_incident_status_id :=
xxdc_p2_conv_util.get_inc_status_id (v_prog_unit
,gc_case_status_open);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_status_id for open status'
|| v_incident_status_id
,p_prog_unit => v_prog_unit
);
v_incident_severity_id :=
xxdc_p2_conv_util.get_inc_severity_id (v_prog_unit, gc_case_severity);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_severity_id: '
|| v_incident_severity_id
,p_prog_unit => v_prog_unit
);
v_incident_urgency_id :=
xxdc_p2_conv_util.get_inc_urgency_id (v_prog_unit, gc_case_urgency);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_incident_urgency_id: '
|| v_incident_urgency_id
,p_prog_unit => v_prog_unit
);
v_owner_group_id :=
xxdc_p2_conv_util.get_inc_group_id (v_prog_unit
,gc_case_owner_group_name
);
--gc_case_owner_group_name );
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_owner_group_id: '
|| v_owner_group_id
,p_prog_unit => v_prog_unit
);
v_problem_code :=
xxdc_p2_conv_util.get_inc_problem_code (v_prog_unit
,gc_ex_problem_code
);
--v_owner_group_id = xxdc_p2_conv_util.GET_INC_GROUP_ID(v_prog_unit, 'DOR Convenience Zone Group');--gc_case_owner_group_name);-- Test
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_problem_code: ' || v_problem_code
,p_prog_unit => v_prog_unit
);
v_resoluton_code :=
xxdc_p2_conv_util.get_inc_resolution_code (v_prog_unit, gc_approved);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_resoluton_code '
|| v_resoluton_code
,p_prog_unit => v_prog_unit
);
SELECT resource_id
INTO v_owner_id
FROM jtf_rs_resource_extns
WHERE UPPER (source_name) = UPPER (gc_ex_owner_name); -- test
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_owner_id ' || v_owner_id
,p_prog_unit => v_prog_unit
);
-- test gc_ex_owner_name
FOR cz_case_ex_rec IN cz_case_ex_cur
LOOP
BEGIN
--fnd_global.apps_initialize (3556, 50843, 170);
SAVEPOINT new_case;
v_process_status := NULL;
gv_zone_number := NVL (cz_case_ex_rec.zone_number, 'N');
get_party_info (p_zone_number => gv_zone_number
,p_party_id => v_party_id
,p_party_type => v_party_type
,p_party_site_id => v_party_site_id
,p_account_id => v_cust_account_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_party_id- '
|| v_party_id
|| ' v_party_id-'
|| v_party_name
|| 'v_party_name -'
|| ' v_party_type-'
|| v_party_type
|| 'v_account_id-'
|| v_cust_account_id
,p_prog_unit => v_prog_unit
);
v_service_request_rec.summary := v_problem_code;
v_service_request_rec.resolution_summary :=
SUBSTR (NVL (cz_case_ex_rec.comments, ''), 1, 250);
v_service_request_rec.problem_code := v_problem_code;
v_service_request_rec.incident_occurred_date :=
TO_DATE (cz_case_ex_rec.initiator_date
,xxd_utils.gc_dor_dt_format
);
v_service_request_rec.request_date := SYSDATE;
v_service_request_rec.type_id := v_incident_type_id;
v_service_request_rec.status_id := 3100;
--v_incident_status_id; --test
v_service_request_rec.severity_id := v_incident_severity_id;
v_service_request_rec.urgency_id := v_incident_urgency_id;
v_service_request_rec.resolution_code := v_resoluton_code;
v_service_request_rec.problem_code := v_problem_code;
v_service_request_rec.customer_id := v_party_id;
v_service_request_rec.inventory_org_id := fnd_global.org_id;
v_service_request_rec.account_id := v_cust_account_id;
v_service_request_rec.owner_group_id := v_owner_group_id;
v_service_request_rec.group_type := gc_owner_group_type;
v_service_request_rec.incident_location_id := v_party_site_id;
v_service_request_rec.incident_location_type :=
gc_incident_location_type;
--v_service_request_rec.owner_id := v_owner_id;
v_service_request_rec.customer_product_id :=
get_instance_id (gv_zone_number);--8765);
-- get_instance_id (cz_case_ex_rec.zone_number); -- test
v_service_request_rec.external_reference := gv_zone_number;-- 8765;
v_service_request_rec.request_attribute_15 := gv_zone_number;
--cz_case_ex_rec.zone_number -- test
SELECT account_number
INTO v_account_number
FROM hz_cust_accounts
WHERE cust_account_id = v_cust_account_id;
xxdc_p2_conv_util.get_bill_to_pt_site_use_id
(p_caller => v_prog_unit
,p_legacy_acct_num => v_account_number
,p_bill_to_party_id => v_bill_to_party_id
,p_cust_acct_id => v_inc_bill_acct_id
,p_bill_to_site_id => v_inc_bill_to_site_id
,p_bill_to_site_use_id => v_inc_bill_to_site_use_id
);
v_service_request_rec.bill_to_party_id := v_bill_to_party_id; ---TEST
--v_service_request_rec.bill_to_accont_id := v_inc_bill_acct_id;
v_service_request_rec.bill_to_site_id := v_inc_bill_to_site_id;
v_service_request_rec.bill_to_site_use_id :=
v_inc_bill_to_site_use_id;
v_service_request_rec.creation_date := SYSDATE;
v_service_request_rec.created_by := fnd_global.user_id;
v_service_request_rec.caller_type := v_party_type;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
v_incident_id := NULL;
create_cz_case (p_service_request_rec => v_service_request_rec
,p_notes_table => v_notes_table
,p_contacts_table => v_contacts_table
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,x_sr_create_out_rec => v_sr_create_out_rec
);
v_incident_id := v_sr_create_out_rec.request_id;
v_incident_number := v_sr_create_out_rec.request_number;
IF (NVL (gr_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gr_msg_count, 0) > 0)
OR (NVL (v_incident_number, 'N') = 'N')
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating a Case for Zone_Number: '
|| gv_zone_number
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
ELSE
gv_process_msg :=
'Case is created for Zone_number: '
|| gv_zone_number
|| ' Case_Number : '
|| v_incident_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
-- Creation of Assosiation for above case
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
v_ass_contacts_table.DELETE;
SELECT party_id
INTO v_party_id
FROM hz_cust_accounts
WHERE account_number = 'RC14061';
--DS1880'; -- cz_case_ex_rec.alt_rc;
SELECT party_role_code
INTO v_party_role_code
FROM cs_party_roles_tl
WHERE UPPER (NAME) = UPPER ('Designated RC');
--cz_case_ex_rec.relationship); --test
v_ass_contacts_table (1).party_id := v_party_id;
v_ass_contacts_table (1).start_date_active := SYSDATE;
v_ass_contacts_table (1).contact_type := gc_contact_type;
v_ass_contacts_table (1).party_role_code := v_party_role_code;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
update_case_assosiation
(p_incident_id => v_incident_id
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,p_contacts_table => v_ass_contacts_table
);
IF ( (gr_return_status <> fnd_api.g_ret_sts_success)
OR (NVL (gr_msg_count, 0) <> 0)
)
THEN
v_process_status := 'E';
gv_process_msg := NULL;
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating assosiation for Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_warning_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
ELSE
gv_process_msg :=
'Assosiation Created Successfully for Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
END IF;
-- Task Creation Block
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
SELECT task_status_id
INTO v_task_status_id
FROM jtf_task_statuses_vl
WHERE UPPER (NAME) = UPPER (gc_ex_task_status);
-- Select task type from JTF_TASK_TYPES_vl.
SELECT task_type_id
INTO v_task_type_id
FROM jtf_task_types_tl
WHERE LANGUAGE = 'US'
AND UPPER (NAME) = UPPER ('CZ:Site Visit');
--gc_ex_task_type); -- test
SELECT resource_id
INTO v_owner_id
FROM jtf_rs_resource_extns
WHERE UPPER (source_name) = UPPER ('Ledesma, Hope');
-- upper(gc_ex_owner_name) --test
-- Get the incident number from cs_inicdents_all_b
SELECT incident_number
INTO v_incident_number
FROM cs.cs_incidents_all_b
WHERE incident_id = v_incident_id;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
jtf_tasks_pub.create_task
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_task_name => gc_task_name
,p_task_type_id => v_task_type_id
,p_description => gc_task_name
,p_task_status_id => v_task_status_id
,p_owner_id => v_owner_id -- test
,p_owner_type_code => gc_task_owner_type_code
,p_source_object_type_code => gc_task_source_type_code
,p_source_object_id => v_incident_id
--,p_source_object_name => v_incident_number
, p_task_assign_tbl => jtf_tasks_pub.g_miss_task_assign_tbl
,p_task_depends_tbl => jtf_tasks_pub.g_miss_task_depends_tbl
,p_task_rsrc_req_tbl => jtf_tasks_pub.g_miss_task_rsrc_req_tbl
,p_task_refer_tbl => jtf_tasks_pub.g_miss_task_refer_tbl
,p_task_dates_tbl => jtf_tasks_pub.g_miss_task_dates_tbl
,p_task_notes_tbl => jtf_tasks_pub.g_miss_task_notes_tbl
,p_task_recur_rec => jtf_tasks_pub.g_miss_task_recur_rec
,p_task_contacts_tbl => jtf_tasks_pub.g_miss_task_contacts_tbl
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,x_task_id => vr_task_id
);
IF (gr_return_status <> fnd_api.g_ret_sts_success)
OR (NVL (vr_task_id, 0) = 0)
OR (NVL (gr_msg_count, 0) > 0)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating task for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
ELSE
gv_process_msg :=
'TASK was successfully created for Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
END IF;
-- Creating Assosiation Participant External Attributes
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
p_ext_attr_grp_tbl.DELETE;
p_ext_attr_tbl.DELETE;
v_attribute_group_id := NULL;
v_application_id := NULL;
v_party_id := NULL;
v_contact_type := NULL;
--v_attribute_group_id := xxdc_p2_conv_util.GET_ATTR_GROUP_ID(gc_attr_source_group_name); TEST
SELECT attr_group_id, application_id
,attr_group_type
INTO v_attribute_group_id, v_application_id
,v_attr_group_type
FROM ego_attr_groups_v
WHERE attr_group_disp_name = gc_attr_source_group_name;
-- test
p_ext_attr_grp_tbl (1).row_identifier := 1;
p_ext_attr_grp_tbl (1).pk_column_1 := v_incident_id;
p_ext_attr_grp_tbl (1).object_name := gc_source_object_name;
p_ext_attr_grp_tbl (1).attr_group_id := v_attribute_group_id;
p_ext_attr_grp_tbl (1).attr_group_type := v_attr_group_type;
p_ext_attr_grp_tbl (1).CONTEXT := -1;
p_ext_attr_grp_tbl (1).attr_group_name :=
gc_attr_source_group_name;
p_ext_attr_grp_tbl (1).attr_group_app_id := v_application_id;
p_ext_attr_grp_tbl (1).mapping_req := gc_n;
p_ext_attr_grp_tbl (1).operation := gc_att_operation;
p_ext_attr_tbl (1).row_identifier := 1;
p_ext_attr_tbl (1).attr_name := gc_att_source;
---test
p_ext_attr_tbl (1).attr_value_str := cz_case_ex_rec.initiator;-- 'BMDS Referral'; --test
-- gc_att_source_value
x_failed_row_id_list := NULL;
gr_return_status := NULL;
x_errorcode := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
process_case_ext_attrs
(p_incident_id => v_incident_id
,p_ext_attr_grp_tbl => p_ext_attr_grp_tbl
,p_ext_attr_tbl => p_ext_attr_tbl
,x_failed_row_id_list => x_failed_row_id_list
,x_return_status => gr_return_status
,x_errorcode => x_errorcode
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF ( gr_return_status <> fnd_api.g_ret_sts_success
OR NVL (gr_msg_count, 0) > 0
OR gr_return_status = 'U'
)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating global external Source attribute for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
ELSE
gv_process_msg :=
'Global External Source Attribute was successfully created for Case_Number: '
|| v_incident_number
|| ' of the Zone_Number: '
|| gv_zone_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
p_ext_attr_grp_tbl.DELETE;
p_ext_attr_tbl.DELETE;
v_attribute_group_id := NULL;
v_application_id := NULL;
v_party_id := NULL;
v_contact_type := NULL;
/*
Creation of global external Source attribute
*/
SELECT attr_group_id, application_id
,attr_group_type
INTO v_attribute_group_id, v_application_id
,v_attr_group_type
FROM ego_attr_groups_v
WHERE attr_group_disp_name = gc_pr_attr_group_name;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_attribute_group_id '
|| v_attribute_group_id
|| 'v_application_id '
|| v_application_id
|| ' v_attr_group_type '
|| v_attr_group_type
|| ' gc_pr_attr_group_name '
|| gc_pr_attr_group_name
,p_prog_unit => v_prog_unit
);
SELECT party_id, contact_type
INTO v_party_id, v_contact_type
FROM cs_hz_sr_contact_points
WHERE incident_id = v_incident_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'party_id, contact_type '
|| v_party_id
|| v_contact_type
|| 'v_party_role_code '
|| v_party_role_code
,p_prog_unit => v_prog_unit
);
SELECT party_type
, hca.account_number
|| '|'
|| SUBSTR (hp.party_name, 1, 120)
,hp.party_id
INTO v_party_type
,v_participant_str
,v_party_id
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.account_number = 'RC14061';
--DS1880'; -- cz_case_ex_rec.alt_rc; -- Test;
DBMS_OUTPUT.put_line ('party_type ' || v_party_type);
p_ext_attr_grp_tbl (1).row_identifier := 1;
p_ext_attr_grp_tbl (1).pk_column_1 := v_incident_id;
p_ext_attr_grp_tbl (1).pk_column_2 := v_party_id;
p_ext_attr_grp_tbl (1).pk_column_3 := v_contact_type;
p_ext_attr_grp_tbl (1).pk_column_4 := v_party_role_code;
--v_party_type;
p_ext_attr_grp_tbl (1).object_name := gc_pr_object_name;
p_ext_attr_grp_tbl (1).attr_group_id := v_attribute_group_id;
p_ext_attr_grp_tbl (1).attr_group_type := v_attr_group_type;
p_ext_attr_grp_tbl (1).CONTEXT := v_party_role_code;
--'Designated RC';--v_party_role_code;--v_incident_type_id;
p_ext_attr_grp_tbl (1).attr_group_name :=
gc_pr_attr_group_name;
p_ext_attr_grp_tbl (1).attr_group_app_id := v_application_id;
p_ext_attr_grp_tbl (1).mapping_req := gc_n;
p_ext_attr_grp_tbl (1).operation := gc_att_operation;
p_ext_attr_tbl (1).row_identifier := 1;
p_ext_attr_tbl (1).attr_name := gc_att_participant_account;
p_ext_attr_tbl (1).attr_value_str := v_party_id;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
process_case_ext_attrs
(p_incident_id => v_incident_id
,p_ext_attr_grp_tbl => p_ext_attr_grp_tbl
,p_ext_attr_tbl => p_ext_attr_tbl
,x_failed_row_id_list => x_failed_row_id_list
,x_return_status => gr_return_status
,x_errorcode => x_errorcode
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF ( gr_return_status <> fnd_api.g_ret_sts_success
OR NVL (gr_msg_count, 0) > 0
OR gr_return_status = 'U'
)
THEN
ROLLBACK TO new_case;
v_process_status := 'E';
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating Assosiation Participant External Attributes for Case_Number: '
|| v_incident_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
ELSE
gv_process_msg :=
'Assosiation Participant External Attributes was successfully created for Case_Number: '
|| v_incident_number
|| ' of the Zone_Number: '
|| gv_zone_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
END IF;
END IF;
END IF;
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
update_case_status
(p_incident_id => v_incident_id
,p_incident_close_date => TO_DATE
(cz_case_ex_rec.decision_date
,xxd_utils.gc_dor_dt_format
)
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF ( (gr_return_status = fnd_api.g_ret_sts_success)
OR (NVL (gr_msg_count, 0) > 0)
)
THEN
gv_process_msg :=
'Updated Case Status Successfully for Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_process_msg);
UPDATE xxdc_cz_case_ex
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE zone_number = gv_zone_number;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'Case was created sucessfully Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number
|| '*************************************************'
,p_prog_unit => v_prog_unit
);
COMMIT;
ELSE
v_process_status := 'E';
ROLLBACK TO new_case;
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when updating a case for Case_Number: '
|| v_incident_number
|| ' of Zone_Number: '
|| gv_zone_number
|| '. Error message: '
|| gv_process_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_zone_number
,gv_process_msg
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO new_case;
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_rule_id
,p_request_id
,gv_zone_number
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO new_case;
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_rule_id
,p_request_id
,gv_zone_number
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END convert_ex_data;
PROCEDURE convert_data (
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_debug IN VARCHAR2 DEFAULT 'Y'
,p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CONVERT_DATA';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
retcode := 0;
convert_ex_data(p_request_id);
convert_sm_data(p_request_id);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_complete
,p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG
,p_retcode => retcode
,p_debug => p_debug
,p_stuff => gv_debug_log_clob
,p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG
,p_retcode => retcode
,p_debug => p_debug
,p_stuff => gv_debug_log_clob
,p_result => gv_result
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_rule_id
,p_request_id
,NULL
, v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END;
END xxdc_cz_case;
/
No comments:
Post a Comment