CREATE OR REPLACE PACKAGE BODY APPS.xxdc_load_insp_case
IS
/***************************************************************************************************************************/
/** NAME: APPS.XXDC_LOAD_INSP
/** 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 2512 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;
PROCEDURE create_case (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,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
,p_auto_assign IN VARCHAR2 DEFAULT 'N'
,x_sr_create_out_rec OUT NOCOPY cs_servicerequest_pub.sr_create_out_rec_type
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_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 => gc_t
,p_commit => NVL
(gc_commit_flag
,'F'
)
,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
,p_service_request_rec => p_service_request_rec
,p_notes => p_notes_table
,p_contacts => p_contacts_table
,p_auto_assign => gc_n
,p_auto_generate_tasks => gc_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_case;
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 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 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
|| 'AFTERAPI IN PRO'
,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_processstatus (p_request_id IN NUMBER)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.UPDATE_PROCESSSTATUS';
CURSOR case_status_cur
IS
SELECT *
FROM xxdc_load_insp_case_main xlic
WHERE process_status IN ('RS', 'RD')
AND request_id = NVL (p_request_id, request_id);
CURSOR case_dtl_status_cur
IS
SELECT *
FROM xxdc_load_insp_case_dtl xlicd
WHERE process_status IN ('RS', 'RD')
AND request_id = NVL (p_request_id, request_id);
CURSOR case_note_status_cur
IS
SELECT *
FROM xxdc_load_insp_case_note xlicn
WHERE process_status IN ('RS', 'RD')
AND request_id = NVL (p_request_id, request_id);
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
FOR case_status_rec IN case_status_cur
LOOP
UPDATE xxdc_load_insp_case_dtl xlicd
SET xlicd.process_status =
DECODE (xlicd.process_status
,'RS', 'RS'
,'RD', 'RD'
,case_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_status_rec.legacy_id
AND request_id = p_request_id;
UPDATE xxdc_load_insp_case_note xlicn
SET xlicn.process_status =
DECODE (xlicn.process_status
,'RS', 'RS'
,'RD', 'RD'
,case_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_status_rec.legacy_id
AND request_id = p_request_id;
END LOOP;
FOR case_dtl_status_rec IN case_dtl_status_cur
LOOP
UPDATE xxdc_load_insp_case_main xlicm
SET xlicm.process_status =
DECODE (xlicm.process_status
,'RS', 'RS'
,'RD', 'RD'
,case_dtl_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_dtl_status_rec.legacy_id
AND request_id = p_request_id;
UPDATE xxdc_load_insp_case_note xlicn
SET xlicn.process_status =
DECODE (xlicn.process_status
,'RS', 'RS'
,'RD', 'RD'
,case_dtl_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_dtl_status_rec.legacy_id
AND request_id = p_request_id;
END LOOP;
FOR case_note_status_rec IN case_note_status_cur
LOOP
UPDATE xxdc_load_insp_case_main
SET process_status =
DECODE (process_status
,'RS', 'RS'
,'RD', 'RD'
,case_note_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_note_status_rec.legacy_id
AND request_id = p_request_id;
--,'RS','RS','RD','RD',
UPDATE xxdc_load_insp_case_note
SET process_status =
DECODE (process_status
,'RS', 'RS'
,'RD', 'RD'
,case_note_status_rec.process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE legacy_id = case_note_status_rec.legacy_id
AND request_id = p_request_id;
END LOOP;
UPDATE xxdc_load_insp_case_main
SET process_status =
DECODE (process_status
,gc_n, 'P'
,process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE request_id = p_request_id;
UPDATE xxdc_load_insp_case_dtl
SET process_status =
DECODE (process_status
,gc_n, 'P'
,process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE request_id = p_request_id;
UPDATE xxdc_load_insp_case_note
SET process_status =
DECODE (process_status
,gc_n, 'P'
,process_status
)
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE request_id = 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
);
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'sqlerrcode - '
|| SQLCODE
|| 'sqlerrcode - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END update_processstatus;
PROCEDURE update_case_status (
p_incident_id NUMBER
-- ,p_incident_status_id NUMBER
, 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 => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_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 => SYSDATE
,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_util_pkg.combine_message (x_msg_count, x_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
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;
FUNCTION reason_check (p_reason IN VARCHAR2)
RETURN VARCHAR2
IS
v_reason VARCHAR2 (500) := NULL;
BEGIN
SELECT ffv.flex_value
INTO v_reason
FROM fnd_flex_values ffv, fnd_flex_value_sets ffvs, ego_attrs_v eav
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.flex_value_set_name = eav.value_set_name
AND ffv.flex_value = p_reason
AND eav.attr_display_name = gc_reason;
RETURN v_reason;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END reason_check;
FUNCTION payment_denied_chk (p_rec_id IN NUMBER)
RETURN VARCHAR2
IS
v_payment_denied VARCHAR2 (50) := NULL;
BEGIN
SELECT payment_denied
INTO v_payment_denied
FROM xxdc.xxdc_load_insp_case_dtl
WHERE TRIM (payment_denied) IN ('Denied Payment', 'Reduced Payment')
AND rec_id = p_rec_id;
RETURN v_payment_denied;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END payment_denied_chk;
FUNCTION get_material_desc (p_material IN VARCHAR2)
RETURN VARCHAR2
IS
v_material_desc mtl_system_items_b.description%TYPE := NULL;
BEGIN
SELECT msi.description
INTO v_material_desc
FROM mtl_system_items msi
,mtl_item_categories mic
,mtl_categories_b mcb
WHERE msi.inventory_item_id = mic.inventory_item_id
AND msi.description = NVL (p_material, 'N')
AND mic.category_id = mcb.category_id
AND mcb.segment1 = 'Disbursement'
AND mcb.segment2 = 'CRVOUT'
AND msi.enabled_flag = 'Y'
AND msi.inventory_item_status_code = 'Active';
RETURN v_material_desc;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_material_desc;
PROCEDURE get_party_info (
p_account_number 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_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_account_number;
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
p_party_id := NULL;
p_party_type := NULL;
p_account_id := NULL;
p_party_site_id := NULL;
END;
PROCEDURE get_party_bill_to_info (
p_account_number IN VARCHAR2
,p_account_id OUT NUMBER
,p_party_site_id OUT NUMBER
,p_party_site_use_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_PARTY_BILL_TO_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 ps.party_site_id, pu.party_site_use_id, ca.cust_account_id
INTO p_party_site_id, p_party_site_use_id, p_account_id
FROM hz_parties hp
,hz_cust_accounts_all ca
,hz_party_sites ps
,hz_party_site_uses pu
WHERE ca.account_number = p_account_number
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';
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_site_id := -1;
p_party_site_use_id := -1;
p_account_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_site_id := -2;
p_party_site_use_id := -2;
p_account_id := -2;
END;
PROCEDURE prevalidation (
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_debug IN VARCHAR2 DEFAULT 'Y'
,p_request_id IN NUMBER
)
AS
/***************************************************************************************************************************/
/** -----------------------------------------------------------------------------------------
/** Pre Validation before running the conversion check for validations and configuration settings
/** ------------------------------------------------------------------------------------------
/***************************************************************************************************************************/
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);
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;
PROCEDURE split_data (p_request_id IN NUMBER)
IS
/***************************************************************************************************************************/
/** -----------------------------------------------------------------------------------------
/** Spilt the xxdc_load_insp_stg data to 3 scond stage tables
/** ------------------------------------------------------------------------------------------
/***************************************************************************************************************************/
v_prog_unit VARCHAR (100) := gc_pkg || '.SPLIT_DATA';
p_case_date VARCHAR2 (255);
p_pr_cert_id VARCHAR2 (255);
v_legacy_id VARCHAR2 (255);
v_pr_name VARCHAR2 (255);
v_item_id mtl_system_items_b.inventory_item_id%TYPE;
v_source_file_name VARCHAR2 (255);
v_conv_id NUMBER;
retcode NUMBER := 0;
p_debug VARCHAR2 (3) := 'Y';
-- get the data from staging table according to the passed veriable of one case at a time
CURSOR load_insp_cur (p_case_date IN VARCHAR2, p_pr_cert_id IN VARCHAR2)
IS
SELECT *
FROM xxdc.xxdc_load_insp_case_stg
WHERE case_date = p_case_date
AND pr_cert_id = p_pr_cert_id
AND request_id = NVL (p_request_id, request_id);
-- cursor to group the data with case_date and pr_cert_id
CURSOR load_insp_case_cur
IS
SELECT case_date, pr_cert_id
FROM xxdc.xxdc_load_insp_case_stg
WHERE request_id = NVL (p_request_id, request_id)
GROUP BY case_date, pr_cert_id
ORDER BY pr_cert_id;
BEGIN
-- SAVEPOINT stage_data;
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;
-- EXECUTE IMMEDIATE 'delete xxdc.xxdc_load_insp_case_main where request_id = '
-- || p_request_id;
-- EXECUTE IMMEDIATE 'delete xxdc.xxdc_load_insp_case_dtl where request_id = '
-- || p_request_id;
-- EXECUTE IMMEDIATE 'delete xxdc.xxdc_load_insp_case_note where request_id = '
-- || p_request_id;
FOR load_insp_case_rec IN load_insp_case_cur
LOOP
SELECT pr_name, source_file_name, conv_id
INTO v_pr_name, v_source_file_name, v_conv_id
FROM xxdc.xxdc_load_insp_case_stg
WHERE case_date = load_insp_case_rec.case_date
AND pr_cert_id = load_insp_case_rec.pr_cert_id
AND ROWNUM = 1
AND request_id = p_request_id;
p_case_date := load_insp_case_rec.case_date;
p_pr_cert_id := load_insp_case_rec.pr_cert_id;
v_legacy_id :=
load_insp_case_rec.pr_cert_id
|| '-'
|| TO_CHAR (TO_DATE (load_insp_case_rec.case_date
,xxd_utils.gc_dor_dt_format
)
,'YYYYMMDD'
);
-----------------------------------------------------------------------------------------
-- Insert the values into case header table XXDC_LOAD_INSP_CASE by group the case_date and pr_cert_id
-----------------------------------------------------------------------------------------
INSERT INTO xxdc.xxdc_load_insp_case_main
(source_file_name, conv_id, legacy_id
,action_date
,pr_cert_id, pr_name
,created_by, creation_date, last_updated_by
,last_update_date, request_id, process_status
)
VALUES (v_source_file_name, v_conv_id, v_legacy_id
,load_insp_case_rec.case_date
,load_insp_case_rec.pr_cert_id, v_pr_name
,fnd_global.user_id, SYSDATE, fnd_global.user_id
,SYSDATE, p_request_id, gc_n
);
FOR load_insp_rec IN load_insp_cur (p_case_date, p_pr_cert_id)
LOOP
-----------------------------------------------------------------------------------------
-- Insert the additinal attribute values into XXDC_LOAD_INSP_CASE_DTL
-----------------------------------------------------------------------------------------
INSERT INTO xxdc.xxdc_load_insp_case_dtl
(rec_id
,source_file_name
,conv_id, legacy_id
,rc_cert_id
,rc_name
,dr6_fsn
,doriis_item_code
,material
,rec_wt
,wt_ticket
,vlp_no
,vlp_state
,trailer_lp
,trailer_state
,roll_off_no
,description
,percent_non_eligible
,payment_denied
,payment_adjusted, created_by
,creation_date, last_updated_by, last_update_date
,request_id, process_status
)
VALUES (xxdc.xxdc_legacy_id.NEXTVAL
,load_insp_rec.source_file_name
,load_insp_rec.conv_id, v_legacy_id
,load_insp_rec.shipper_id
,load_insp_rec.shipper_name
,load_insp_rec.transaction_number
,load_insp_rec.doriis_item_code
,load_insp_rec.material
,load_insp_rec.received_weight
,load_insp_rec.weight_ticket_number
,load_insp_rec.vehicle_plate
,load_insp_rec.vehicle_plate_state
,load_insp_rec.trailer_plate
,load_insp_rec.trailer_plate_state
,load_insp_rec.roll_off_id
,load_insp_rec.description
,load_insp_rec.pt_non_eligible
,load_insp_rec.payment_denied
,load_insp_rec.payment_adjusted, fnd_global.user_id
,SYSDATE, fnd_global.user_id, SYSDATE
,p_request_id, gc_n
);
-----------------------------------------------------------------------------------------
-- Insert the note values into XXDC_LOAD_INSP_CASE_NOTE
-----------------------------------------------------------------------------------------
INSERT INTO xxdc.xxdc_load_insp_case_note
(rec_id
,source_file_name
,conv_id, legacy_id
,dor_rep1, dor_rep2
,pr_rep
,rc_inspection_report
,rc_insp_report_mailed
,notes, created_by, creation_date
,last_updated_by, last_update_date, request_id
,process_status
)
VALUES (xxdc.xxdc_legacy_id.CURRVAL
,load_insp_rec.source_file_name
,load_insp_rec.conv_id, v_legacy_id
,load_insp_rec.dor_rep1, load_insp_rec.dor_rep2
,load_insp_rec.pr_rep
,load_insp_rec.rc_inspection_report
,load_insp_rec.rc_insp_report_mailed
,load_insp_rec.notes, fnd_global.user_id, SYSDATE
,fnd_global.user_id, SYSDATE, p_request_id
,gc_n
);
END LOOP;
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 stage_data;
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 split_data;
PROCEDURE convert_data (
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_debug IN VARCHAR2 DEFAULT 'Y'
,p_request_id IN NUMBER
)
IS
/***************************************************************************************************************************/
/** -----------------------------------------------------------------------------------------
/** Main Program to conversion process after run the split data and pre validation
/** ------------------------------------------------------------------------------------------
/***************************************************************************************************************************/
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_resolution_code cs_incidents_all_b.resolution_code%TYPE;
v_problem_code cs_incidents_all_b.problem_code%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;
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_bill_to_account_id cs_incidents_all_b.bill_to_account_id%TYPE
:= NULL;
v_assosiation_id NUMBER := 0;
-- t_note jtf_notes_tl.notes_detail%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_note_type VARCHAR2 (50);
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_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_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_DATA';
i NUMBER := 0;
j NUMBER := 0;
x_message VARCHAR2 (4000);
v_process_status VARCHAR2 (1) := NULL;
e_exception EXCEPTION;
e_rollback EXCEPTION;
v_jtf_note_id jtf_notes_tl.jtf_note_id%TYPE := NULL;
v_payment_denied xxdc_load_insp_case_dtl.payment_denied%TYPE
:= NULL;
v_reason xxdc_load_insp_case_dtl.description%TYPE
:= NULL;
v_sqlldr_req_id xxdc_p2_conv_errors.sqlload_request_id%TYPE;
-----------------------------------------------------------------------------------------
-- Main cursor to create the case
-----------------------------------------------------------------------------------------
CURSOR load_insp_case_cur
IS
SELECT *
FROM xxdc.xxdc_load_insp_case_main
WHERE ROWNUM < 25
AND request_id = NVL (p_request_id, request_id)
AND process_status IN ('P', 'PW', 'E');
--legacy_id = 'RC4076-20061027' -- test
--
--and process_status in ('P', 'W','RD','RS','S') --test
--AND request_id = p_request_id
-- AND process_status = 'P'; --
--ORDER BY pr_name DESC;
-- WHERE legacy_id = 'PR0175-20001019'; --'RC4076-20061027';
-- WHERE process_status = 'N' AND ROWNUM < 3; --IN ('RD')
-----------------------------------------------------------------------------------------
-- Cursor to Create the Assosiations
-----------------------------------------------------------------------------------------
CURSOR load_insp_case_note_cur (p_legacy_id IN VARCHAR2)
IS
SELECT xlicd.rc_cert_id, xlicd.dr6_fsn, xlicd.payment_adjusted
,xlicn.dor_rep1, xlicn.dor_rep2, xlicn.pr_rep
,xlicn.rc_inspection_report, xlicn.rc_insp_report_mailed
,xlicn.notes
FROM xxdc_load_insp_case_dtl xlicd, xxdc_load_insp_case_note xlicn
WHERE xlicd.rec_id = xlicn.rec_id
AND xlicn.legacy_id = p_legacy_id
AND xlicd.legacy_id = p_legacy_id
AND xlicd.request_id = NVL (p_request_id, xlicd.request_id)
AND xlicn.request_id = NVL (p_request_id, xlicn.request_id);
CURSOR load_insp_case_dtl_cur (p_legacy_id IN VARCHAR2)
IS
SELECT DISTINCT rc_cert_id
FROM xxdc.xxdc_load_insp_case_dtl
WHERE legacy_id = p_legacy_id
AND request_id = NVL (p_request_id, request_id);
--AND process_status IN ('RD') ;
-----------------------------------------------------------------------------------------
-- Cursor to get id for creation of NOTE
-----------------------------------------------------------------------------------------
CURSOR load_insp_case_dtl_info_cur (p_legacy_id IN VARCHAR2)
IS
SELECT *
FROM xxdc.xxdc_load_insp_case_dtl a
WHERE legacy_id = p_legacy_id
AND request_id = NVL (p_request_id, request_id);
--AND process_status IN ('RD') ;
BEGIN
v_sqlldr_req_id := p_request_id;
retcode := 0;
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
-----------------------------------------------------------------------------------------
BEGIN
SELECT incident_type_id
INTO v_incident_type_id
FROM cs_incident_types_vl
WHERE UPPER (NAME) = gc_case_type
AND incident_subtype = gc_inc_subtype;
gv_process_msg :=
'Case Type Id: '
|| v_incident_type_id
|| ' for case type:'
|| gc_case_type;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
gv_process_msg :=
'Case type "Comp: Load Inspection" not exist in DORIIS';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| 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_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END case_type;
-----------------------------------------------------------------------------------------
-- Case Status Validation
-----------------------------------------------------------------------------------------
BEGIN
SELECT incident_status_id
INTO v_incident_status_id
FROM cs_incident_statuses_vl
WHERE UPPER (NAME) = gc_case_status_open
AND incident_subtype = gc_inc_subtype;
gv_process_msg :=
'Case Status Id: '
|| v_incident_status_id
|| ' for case Status:'
|| gc_case_status_open;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg := 'Case Status not exist in DORIIS';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
v_process_status := 'E';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_rule_id
,p_request_id
,gv_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END case_status;
-----------------------------------------------------------------------------------------
-- Case Severity Validation
-----------------------------------------------------------------------------------------
BEGIN
SELECT incident_severity_id
INTO v_incident_severity_id
FROM cs_incident_severities_vl
WHERE UPPER (NAME) = gc_case_severity
AND incident_subtype = gc_inc_subtype;
gv_process_msg :=
'Case severity Id: '
|| v_incident_severity_id
|| ' for case severity:'
|| gc_case_severity;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg := 'Case severity not exist in DORIIS';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
v_process_status := 'E';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_rule_id
,p_request_id
,gv_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END case_severity;
-----------------------------------------------------------------------------------------
-- Case Urgency Validation
-----------------------------------------------------------------------------------------
BEGIN
SELECT incident_urgency_id
INTO v_incident_urgency_id
FROM cs_incident_urgencies_vl
WHERE UPPER (NAME) = gc_case_urgency;
gv_process_msg :=
'Case urgency Id: '
|| v_incident_urgency_id
|| ' for case urgency:'
|| gc_case_urgency;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg := 'case urgency is not defind in DORIIS';
v_process_status := 'E';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| 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_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END case_urgency;
-----------------------------------------------------------------------------------------
-- Case Owner Group Validation
-----------------------------------------------------------------------------------------
BEGIN
SELECT GROUP_ID
INTO v_owner_group_id
FROM jtf_rs_groups_vl
WHERE UPPER (group_name) = gc_case_group_name
AND SYSDATE BETWEEN start_date_active
AND NVL (end_date_active, SYSDATE);
gv_process_msg :=
'Case Owner Group Id: '
|| v_owner_group_id
|| ' for case owner group name:'
|| gc_case_group_name;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg :=
'Case Owner Group "DOR Investigation Group" exists in DORIIS';
v_process_status := 'E';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| 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_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END case_owner_group;
-----------------------------------------------------------------------------------------
-- Assosiation Party Role Validation
-----------------------------------------------------------------------------------------
BEGIN
SELECT party_role_code
INTO v_party_role_type
FROM cs_party_roles_tl
WHERE NAME = gc_assosiation_party_role_type;
gv_process_msg :=
'Case Assosiate party role type code: '
|| v_party_role_type
|| ' for role type:'
|| gc_assosiation_party_role_type;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| 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_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END;
BEGIN
--- Note Type Validation
SELECT 1
INTO i
FROM fnd_lookup_values
WHERE lookup_type = gc_note_lookup_type AND meaning = 'COMP:COMMENT';
--gc_case_note_type; --' TEST
gv_process_msg :=
'Case note type : ' || gc_case_note_type || ' is exists in DORIIS';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
|| 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_legacy_id
, gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END;
-----------------------------------------------------------------------------------------
-- Attribute Group Validation
-----------------------------------------------------------------------------------------
BEGIN
v_application_id := NULL;
SELECT attr_group_id, application_id
INTO v_twi_attr_group_id, v_application_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE descriptive_flex_context_code = gc_attr_group_name;
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg :=
'Attribute Gruop: '
|| gc_attr_group_name
|| ' not exist in DORIIS';
v_process_status := 'E';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_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 => gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
END arr_group_id;
BEGIN
SELECT attr_group_id, application_id
INTO v_source_attr_group_id, v_application_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE descriptive_flex_context_code = gc_attr_source_group_name;
EXCEPTION
WHEN OTHERS
THEN
gv_process_msg :=
'Source Group type-'
|| gc_attr_source_group_name
|| ' not exist in DORIIS';
v_process_status := 'E';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_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;
v_resolution_code :=
xxdc_p2_conv_util.get_inc_resolution_code (v_prog_unit
,gc_resolution_type
);
v_problem_code :=
xxdc_p2_conv_util.get_inc_problem_code (v_prog_unit, gc_problem_type);
-- IF v_process_status = 'E'
-- THEN
-- xxd_utils.LOG
-- (p_log_text => gv_debug_log_clob
-- ,p_new_text => 'All the setups are not done please check before run conversoin again'
-- ,p_prog_unit => v_prog_unit
-- );
-- ELSE
FOR load_insp_case_rec IN load_insp_case_cur
LOOP
BEGIN
gv_process_msg := NULL;
v_process_status := NULL;
v_party_id := NULL;
v_party_site_id := NULL;
v_cust_account_id := NULL;
gv_legacy_id := load_insp_case_rec.legacy_id;
SAVEPOINT new_rec;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'Start Processing the record Legacy_ID: '
|| gv_legacy_id
,p_prog_unit => v_prog_unit
);
/*
*Party Info Validation
*/
BEGIN
xxdc_p2_conv_util.get_party_info
(p_caller => v_prog_unit
,p_legacy_acct_num => load_insp_case_rec.pr_cert_id
,p_party_id => v_party_id
,p_party_type => v_party_type
,p_account_id => v_cust_account_id
,p_party_site_id => v_party_site_id
);
-- get_party_info
-- (p_account_number => load_insp_case_rec.pr_cert_id
-- ,p_party_id => v_party_id
-- ,p_party_type => v_party_type
-- ,p_account_id => v_party_site_id
-- ,p_party_site_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_type - '
|| v_party_type
|| ' v_party_site_id - '
|| v_party_site_id
|| ' v_party_account_id - '
|| v_cust_account_id
,p_prog_unit => v_prog_unit
);
gv_process_msg :=
' v_party_id - '
|| v_party_id
|| ' v_party_type - '
|| v_party_type
|| ' v_party_site_id - '
|| v_party_site_id
|| ' v_party_account_id - '
|| v_cust_account_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
IF v_party_id = -1
THEN
gv_process_msg :=
'Pr_cert_id: '
|| load_insp_case_rec.pr_cert_id
|| ' does not have any party in DORIIS';
RAISE e_exception;
ELSIF v_party_id = -2
THEN
gv_process_msg :=
'Pr_cert_id: '
|| load_insp_case_rec.pr_cert_id
|| ' has more than one party in DORIIS';
RAISE e_exception;
END IF;
xxdc_p2_conv_util.get_bill_to_pt_site_use_id
(p_caller => v_prog_unit
,p_legacy_acct_num => load_insp_case_rec.pr_cert_id
,p_bill_to_party_id => v_bill_to_party_id
,p_cust_acct_id => v_bill_to_account_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
);
-- get_party_bill_to_info
-- (p_account_number => load_insp_case_rec.pr_cert_id
-- -- ,p_party_id => v_bill_to_party_id
-- , p_account_id => v_bill_to_account_id
-- ,p_party_site_id => v_inc_bill_to_site_id
-- ,p_party_site_use_id => v_inc_bill_to_site_use_id
-- );
gv_process_msg :=
'Bill_to party Details => v_inc_bill_to_site_id - '
|| v_inc_bill_to_site_id
|| 'bill Party id '
|| v_bill_to_party_id
|| ' v_inc_bill_to_site_use_id - '
|| v_inc_bill_to_site_use_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
IF v_inc_bill_to_site_id = -1
THEN
gv_process_msg :=
'Pr_cert_id: '
|| load_insp_case_rec.pr_cert_id
|| ' does not have Bill to site in DORIIS';
RAISE e_exception;
ELSIF v_inc_bill_to_site_id = -2
THEN
gv_process_msg :=
'Pr_cert_id: '
|| load_insp_case_rec.pr_cert_id
|| ' has more than one Bill to sites in DORIIS';
RAISE e_exception;
END IF;
END party_info;
BEGIN
gv_process_msg := 'Assingning the values to case record';
v_service_request_rec.summary :=
load_insp_case_rec.pr_cert_id
|| ' - '
|| load_insp_case_rec.pr_name;
v_service_request_rec.request_date := SYSDATE;
v_service_request_rec.type_id := v_incident_type_id;
v_service_request_rec.status_id := v_incident_status_id;
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.resolution_code := v_resolution_code;
v_service_request_rec.problem_code := v_problem_code;
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.bill_to_party_id := v_bill_to_party_id;
-- v_service_request_rec.bill_to_accont_id := v_bill_to_account_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_legacy_id;
v_service_request_rec.creation_date := SYSDATE;
v_service_request_rec.incident_occurred_date :=
TO_DATE (load_insp_case_rec.action_date
,xxd_utils.gc_dor_dt_format
);
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;
END;
/*
* Calling the Create_case procedure
*/
v_incident_id := NULL;
v_incident_number := NULL;
gr_return_status := NULL;
gr_msg_data := NULL;
gr_msg_count := NULL;
-- if all validations good then process record.
gv_process_msg := 'Before Calling API to create Case';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
create_case (p_init_msg_list => gc_t
,p_commit => NVL (gc_commit_flag
,'F'
)
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
,p_service_request_rec => v_service_request_rec
,p_notes_table => v_notes_table
,p_contacts_table => v_contacts_table
,p_auto_assign => gc_n
,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_rec;
v_process_status := 'E';
IF (NVL (gr_msg_count, 0) > 0)
THEN
xxd_util_pkg.combine_message (gr_msg_count, gv_process_msg);
END IF;
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
,xxdc_p2_conv_util.gc_api_error_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 notes to already created 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
);
FOR load_insp_case_note_rec IN
load_insp_case_note_cur (gv_legacy_id)
LOOP
v_note_detail := EMPTY_CLOB;
v_note_detail :=
'RC CERT ID: '
|| NVL (load_insp_case_note_rec.rc_cert_id, 'NULL')
|| ' DR6 FSN: '
|| NVL (load_insp_case_note_rec.dr6_fsn, 'NULL')
|| CHR (10)
|| 'DOR REP1: '
|| NVL (load_insp_case_note_rec.dor_rep1, 'NULL')
|| ' DOR REP2: '
|| NVL (load_insp_case_note_rec.dor_rep2, 'NULL')
|| CHR (10)
|| 'PR REP: '
|| NVL (load_insp_case_note_rec.pr_rep, 'NULL')
|| CHR (10)
|| 'PAYMENT ADJUSTED: '
|| '$ '
|| NVL (load_insp_case_note_rec.payment_adjusted, 0)
|| CHR (10)
|| 'RC INSPECTION REPORT: '
|| NVL (load_insp_case_note_rec.rc_inspection_report
,'NULL'
)
|| ' RC INSP REPORT MAILED: '
|| NVL (load_insp_case_note_rec.rc_insp_report_mailed
,'NULL'
)
|| CHR (10)
|| 'NOTES: '
|| NVL (load_insp_case_note_rec.notes, 'NULL');
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
(load_insp_case_rec.action_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_rec;
v_process_status := 'E';
IF (NVL (gr_msg_count, 0) > 0)
THEN
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
END IF;
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
,xxdc_p2_conv_util.gc_api_error_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;
END LOOP;
--END LOOP;
END IF;
-----------------------------------------------------------------------------------------
-- Creating the Assosiations to already created case
-----------------------------------------------------------------------------------------
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_jtf_note_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
v_assosiation_id := 0;
v_ass_contacts_table.DELETE;
FOR load_insp_dtl_info_rec IN
load_insp_case_dtl_cur (gv_legacy_id)
LOOP
-----------------------------------------------------------------------------------------
-- Party Info for create assosiation
-----------------------------------------------------------------------------------------
BEGIN
v_party_id := NULL;
v_party_type := NULL;
-- xxdc_p2_conv_util.get_case_party_info
-- (p_caller => v_prog_unit
-- ,p_legacy_acct_num => load_insp_dtl_info_rec.rc_cert_id
-- ,p_party_id => v_party_id
-- ,p_party_type => v_party_type
-- ,p_account_id => v_cust_account_id
-- ,p_party_site_id => v_party_site_id
-- );
get_party_info
(p_account_number => load_insp_dtl_info_rec.rc_cert_id
,p_party_id => v_party_id
,p_party_type => v_party_type
,p_account_id => v_cust_account_id
,p_party_site_id => v_party_site_id
);
IF v_party_id = -1
THEN
gv_process_msg :=
'Assosiation Party_id is not exist for RC_Cert_ID: '
|| load_insp_dtl_info_rec.rc_cert_id
|| ' of Legacy_ID: '
|| gv_legacy_id;
retcode := 1;
v_process_status := 'W';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_warning_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 => gv_process_msg
|| xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
,p_prog_unit => v_prog_unit
);
ELSIF v_party_id = -2
THEN
RAISE e_exception;
ELSE
gv_process_msg :=
'Creating Assosiation to already Created Case_Number: '
|| v_incident_number
|| 'With RC_CERT_ID: '
|| load_insp_dtl_info_rec.rc_cert_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;
-----------------------------------------------------------------------------------------
-- Assign the info to assosiation party Table
-----------------------------------------------------------------------------------------
IF (NVL (v_party_id, 0) NOT IN (-1, -2, 0))
AND NVL (v_party_role_type, 'N') <> 'N'
THEN
gv_process_msg :=
'Assosiation Party_id: '
|| v_party_id
|| ' with patty_role is: '
|| v_party_role_type;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
v_assosiation_id := v_assosiation_id + 1;
v_ass_contacts_table (v_assosiation_id).party_id :=
v_party_id;
v_ass_contacts_table (v_assosiation_id).start_date_active :=
SYSDATE;
v_ass_contacts_table (v_assosiation_id).contact_type :=
gc_contact_type;
v_ass_contacts_table (v_assosiation_id).party_role_code :=
v_party_role_type;
END IF;
END LOOP;
-----------------------------------------------------------------------------------------
-- Calling Procedure to create a Assosiation
-----------------------------------------------------------------------------------------
IF v_ass_contacts_table.COUNT > 0
THEN
gr_return_status := NULL;
gr_msg_data := NULL;
gr_msg_count := 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
gv_process_msg :=
'Assosiation Created 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
);
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
ELSE
v_process_status := 'E';
IF (NVL (gr_msg_count, 0) > 0)
THEN
gv_process_msg := NULL;
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gr_return_status
|| ' when creating assosiation 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
,xxdc_p2_conv_util.gc_warning_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
END IF;
END IF;
END IF;
-----------------------------------------------------------------------------------------
-- Add the Extensible Attribute Info to CASE
-----------------------------------------------------------------------------------------
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
i := 0;
j := 0;
p_ext_attr_grp_tbl.DELETE;
p_ext_attr_tbl.DELETE;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
FOR load_insp_dtl_info_rec IN
load_insp_case_dtl_info_cur (gv_legacy_id)
LOOP
BEGIN
i := i + 1;
v_reason :=
reason_check (load_insp_dtl_info_rec.description);
gv_process_msg := ' Reason - ' || v_reason;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
v_payment_denied :=
payment_denied_chk (load_insp_dtl_info_rec.rec_id);
gv_process_msg :=
' Payment Denied - ' || v_payment_denied;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => 'Before att group -'
-- || i
-- || j
-- ,p_prog_unit => v_prog_unit
-- );
p_ext_attr_grp_tbl (i).row_identifier := i;
p_ext_attr_grp_tbl (i).pk_column_1 := v_incident_id;
p_ext_attr_grp_tbl (i).object_name := gc_object_name;
p_ext_attr_grp_tbl (i).attr_group_id :=
v_twi_attr_group_id;
p_ext_attr_grp_tbl (i).attr_group_type := gc_object_type;
p_ext_attr_grp_tbl (i).CONTEXT := v_incident_type_id;
p_ext_attr_grp_tbl (i).attr_group_name :=
gc_attr_group_name;
p_ext_attr_grp_tbl (i).attr_group_app_id :=
v_application_id;
p_ext_attr_grp_tbl (i).mapping_req := gc_n;
p_ext_attr_grp_tbl (i).operation := gc_att_operation;
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_shipper_id;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.rc_cert_id, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_shipper_name;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.rc_name, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_trx_num;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.dr6_fsn, '');
v_mtl_desc :=
get_material_desc (load_insp_dtl_info_rec.material);
gv_process_msg := ' Material LOV - ' || v_mtl_desc;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_material;
p_ext_attr_tbl (j).attr_value_str := NVL (load_insp_dtl_info_rec.DORIIS_ITEM_CODE, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_recvd_wt;
p_ext_attr_tbl (j).attr_value_num :=
TO_NUMBER
(TRIM (REPLACE (load_insp_dtl_info_rec.rec_wt
,','
,''
)
)
);
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_wt_tic_num;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.wt_ticket, '')
|| j;
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_vehicle_plate;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.vlp_no, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_vehicle_state;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.vlp_state, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_trailer_plate;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.trailer_lp, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_trailer_state;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.trailer_state, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_roll_off_id;
p_ext_attr_tbl (j).attr_value_str :=
NVL (load_insp_dtl_info_rec.roll_off_no, '');
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_reason;
p_ext_attr_tbl (j).attr_value_str := trim(load_insp_dtl_info_rec.description);
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name :=
gc_att_pt_non_eligible_conts;
p_ext_attr_tbl (j).attr_value_num :=
TO_NUMBER (load_insp_dtl_info_rec.percent_non_eligible);
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_action_taken;
p_ext_attr_tbl (j).attr_value_str := TRIM (load_insp_dtl_info_rec.payment_denied);
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_net_wt_adj;
p_ext_attr_tbl (j).attr_value_num :=
TO_NUMBER
(REPLACE (load_insp_dtl_info_rec.payment_adjusted
,','
,''
)
);
-- xxd_utils.LOG (p_log_text => gv_debug_log_clob
-- ,p_new_text => 'Before att group -'
-- || i
-- || j
-- ,p_prog_unit => v_prog_unit
-- );
END;
END LOOP;
i := i + 1;
p_ext_attr_grp_tbl (i).row_identifier := i;
p_ext_attr_grp_tbl (i).pk_column_1 := v_incident_id;
p_ext_attr_grp_tbl (i).object_name := gc_object_name;
p_ext_attr_grp_tbl (i).attr_group_id := v_source_attr_group_id;
p_ext_attr_grp_tbl (i).attr_group_type := gc_object_type;
p_ext_attr_grp_tbl (i).CONTEXT := -1;
p_ext_attr_grp_tbl (i).attr_group_name :=
gc_attr_source_group_name;
p_ext_attr_grp_tbl (i).attr_group_app_id := v_application_id;
p_ext_attr_grp_tbl (i).mapping_req := gc_n;
p_ext_attr_grp_tbl (i).operation := gc_att_operation;
j := j + 1;
p_ext_attr_tbl (j).row_identifier := i;
p_ext_attr_tbl (j).attr_name := gc_att_source;
---test
p_ext_attr_tbl (j).attr_value_str := 'BMDS Referral';
-- 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_rec;
v_process_status := 'E';
IF (NVL (gr_msg_count, 0) > 0)
THEN
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
END IF;
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
,xxdc_p2_conv_util.gc_api_error_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;
IF (NVL (v_incident_id, 0) <> 0)
AND (NVL (v_process_status, 'N') <> 'E')
THEN
gr_return_status := NULL;
gr_msg_data := NULL;
gr_msg_count := NULL;
update_case_status (p_incident_id => v_incident_id
-- ,p_incident_status_id NUMBER
, 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
);
IF NVL (v_process_status, 'N') = 'W'
THEN
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'Case was created with Assosiation worning Case_Number: '
|| v_incident_number
|| ' of Legacy_ID: '
|| gv_legacy_id
|| CHR (10)
|| '**************************************************************************************************'
,p_prog_unit => v_prog_unit
);
UPDATE xxdc_load_insp_case_main
SET process_status = 'W'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
UPDATE xxdc_load_insp_case_dtl
SET process_status = 'W'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
UPDATE xxdc_load_insp_case_note
SET process_status = 'W'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
ELSE
UPDATE xxdc_load_insp_case_main
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
UPDATE xxdc_load_insp_case_dtl
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
UPDATE xxdc_load_insp_case_note
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,request_id = fnd_global.conc_request_id
WHERE legacy_id = gv_legacy_id
AND request_id = NVL (p_request_id, request_id);
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
|| CHR (10)
|| '**************************************************************************************************'
,p_prog_unit => v_prog_unit
);
COMMIT;
END IF;
ELSE
v_process_status := 'E';
ROLLBACK TO new_rec;
IF (NVL (gr_msg_count, 0) > 0)
THEN
gv_process_msg := NULL;
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
END IF;
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
,xxdc_p2_conv_util.gc_api_error_rule_id
,p_request_id
,gv_legacy_id
,gv_process_msg
);
END IF;
END IF;
EXCEPTION
WHEN e_exception
THEN
ROLLBACK TO new_rec;
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id
,xxdc_p2_conv_util.gc_exp_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
|| 'Legacy_id: '
|| gv_legacy_id
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM
|| CHR (10)
|| '**************************************************************************************************'
,p_prog_unit => v_prog_unit
);
WHEN OTHERS
THEN
ROLLBACK TO new_rec;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'In exception'
,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_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
|| CHR (10)
|| '**************************************************************************************************'
,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_rec;
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 convert_data;
END xxdc_load_insp_case;
/
No comments:
Post a Comment