CREATE OR REPLACE PACKAGE BODY APPS.xxdc_czib
IS
/***************************************************************************************************************************/
/** NAME: APPS.XXDC_CZIB
/** PURPOSE: This is the package to create Instance for CONVENIENCE ZONE to track Zone attributes.
/**
/**
/** REVISIONS:
/** Versoin Date Author JIRA#/CR# Description
/** --------- ----------- --------------- ---------- ------------------------------------
/** 1.0 3/5/2010 B. Sunil KUMAR 2515 Initial Revision
/**
/***************************************************************************************************************************/
PROCEDURE create_instance (
p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec
,p_ext_attrib_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
,p_pricing_attrib_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
,p_org_assignments_tbl IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl
,p_asset_assignment_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_tbl
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,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 || '.CREATE_INSTANCE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => xxd_utils.gc_start
,p_prog_unit => v_prog_unit
);
csi_item_instance_pub.create_item_instance
(p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => p_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => 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;
PROCEDURE get_party_info (
p_dealer_id IN VARCHAR2
,p_party_id OUT NUMBER
,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
);
SELECT hps.party_site_id, hp.party_id, hca.cust_account_id
INTO p_party_site_id, p_party_id, p_account_id
FROM hz_party_sites hps
,hz_parties hp
,hz_cust_accounts hca
,hz_party_site_uses hpsu
WHERE hp.party_id = hca.party_id
AND hpsu.party_site_id = hps.party_site_id
AND hps.party_id = hp.party_id
AND hca.account_number = p_dealer_id
AND hpsu.site_use_type = 'BUSINESS'
AND hpsu.primary_per_type = 'Y'
AND hpsu.status = 'A';
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_site_id := -1;
p_account_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_site_id := -2;
p_party_id := -2;
p_account_id := -2;
WHEN OTHERS
THEN
p_party_site_id := NULL;
p_party_id := NULL;
p_account_id := NULL;
END get_party_info;
PROCEDURE get_assosiate_party_info (
p_alt_rc IN VARCHAR2
,p_party_id OUT NUMBER
,p_account_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_ASSOSIATE_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, hca.cust_account_id
INTO p_party_id, p_account_id
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id AND hca.account_number = p_alt_rc;
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_account_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_id := -2;
p_account_id := -2;
WHEN OTHERS
THEN
p_party_id := NULL;
p_account_id := NULL;
END get_assosiate_party_info;
-- FUNCTION get_party_id (p_alt_rc IN VARCHAR2)
-- RETURN NUMBER
-- IS
-- v_party_id hz_parties.party_id%TYPE := 0;
-- BEGIN
-- SELECT hca.party_id
-- INTO v_party_id
-- FROM hz_cust_accounts hca
-- WHERE account_number = p_alt_rc;
-- RETURN (v_party_id);
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- v_party_id := -1;
-- RETURN (v_party_id);
-- END get_party_id;
PROCEDURE update_instance (
p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
,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_INSTANCE';
v_instance_rec csi_datastructures_pub.instance_rec;
v_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
v_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
v_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
v_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
v_txn_rec csi_datastructures_pub.transaction_rec;
x_instance_id_lst csi_datastructures_pub.id_tbl;
v_instance_rec_null csi_datastructures_pub.instance_rec;
x_msg_index_out NUMBER;
t_output VARCHAR2 (2000);
t_msg_dummy 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
);
v_txn_rec.transaction_date := SYSDATE;
v_txn_rec.source_transaction_date := SYSDATE;
v_txn_rec.transaction_type_id := 1; --NULL;
v_instance_rec := v_instance_rec_null;
csi_item_instance_pub.update_item_instance
(p_api_version => 1.0
,p_commit => gc_f
,p_init_msg_list => gc_f
,p_validation_level => 1
,p_instance_rec => v_instance_rec
,p_ext_attrib_values_tbl => v_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => v_pricing_attrib_tbl
,p_org_assignments_tbl => v_org_assignments_tbl
,p_asset_assignment_tbl => v_asset_assignment_tbl
,p_txn_rec => v_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => 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 update_instance;
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;
--xxdc_p2_conv_util.gen_pre_validation (gc_conv_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
);
xxdc_p2_conv_util.gen_datatype_validation (gc_conv_id, p_request_id);
UPDATE xxdc_czib_stg
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 prevalidation;
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';
v_total_records NUMBER := 0;
v_success_records NUMBER := 0;
v_fail_records NUMBER := 0;
v_commit_records NUMBER := 0;
v_party_site_id NUMBER;
v_account_id NUMBER;
i NUMBER;
v_organization_id NUMBER;
v_source_transaction_type_id NUMBER;
v_instance_status_id NUMBER;
v_zsdate VARCHAR2 (255);
v_zone_num NUMBER;
v_instance_number csi_item_instances.instance_number%TYPE;
v_instance_rec csi_datastructures_pub.instance_rec;
v_instance_rec_null csi_datastructures_pub.instance_rec;
v_ext_attrib_values csi_datastructures_pub.extend_attrib_values_tbl;
v_party_tbl csi_datastructures_pub.party_tbl;
v_account_tbl csi_datastructures_pub.party_account_tbl;
v_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
v_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
v_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
v_txn_rec csi_datastructures_pub.transaction_rec;
v_txn_rec_null csi_datastructures_pub.transaction_rec;
v_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
v_instance_id csi_item_instances.instance_id%TYPE;
v_relationship_code csi_ipa_relation_types.ipa_relation_type_code%TYPE;
v_obj_ver_num csi_item_instances.object_version_number%TYPE;
v_ins_type_code csi_item_instances.instance_type_code%TYPE;
v_process_status VARCHAR2 (1) := NULL;
v_radius csi_item_instances.attribute1%TYPE
:= NULL;
v_rural csi_item_instances.attribute2%TYPE
:= NULL;
v_expandable csi_item_instances.attribute3%TYPE
:= NULL;
v_type csi_item_instances.attribute17%TYPE
:= NULL;
v_inv_item_id mtl_system_items_b.inventory_item_id%TYPE;
v_party_id hz_parties.party_id%TYPE;
e_exception EXCEPTION;
i NUMBER := 0;
CURSOR cz_ib_cur
IS
SELECT *
FROM xxdc_czib_stg
WHERE ROWNUM < 10 AND zonestat = 'EZ' and request_id = nvl(p_request_id, request_id)
and NVL (process_status, 'N') IN ('P', 'PW', 'E');
--;-- ROWNUM < 2 AND zonestat = 'EZ'; -- Test
--NVL (process_status, 'N') IN ('P', 'W');
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;
FOR cz_ib_rec IN cz_ib_cur
LOOP
BEGIN
SAVEPOINT create_cz_instance;
v_process_status := NULL;
v_ext_attrib_values.DELETE;
v_party_tbl.DELETE;
v_account_tbl.DELETE;
v_pricing_attrib_tbl.DELETE;
v_org_assignments_tbl.DELETE;
v_asset_assignment_tbl.DELETE;
v_txn_rec := v_txn_rec_null;
v_instance_rec := v_instance_rec_null;
gv_zone_num := NVL (cz_ib_rec.zone_num, 'N');
v_ins_type_code := NULL;
v_instance_number := NULL;
v_instance_id := NULL;
CASE
WHEN cz_ib_rec.rural = gc_r
THEN
v_radius := gc_radius_half;
v_rural := gc_y;
v_expandable := gc_y;
v_type := gc_stand_type;
WHEN cz_ib_rec.rural IS NULL
THEN
v_radius := gc_radius_half;
v_rural := gc_y;
v_expandable := gc_y;
v_type := gc_stand_type;
WHEN cz_ib_rec.rural = gc_t
THEN
v_radius := gc_radius_half;
v_rural := gc_y;
v_expandable := gc_n;
v_type := gc_stand_type;
WHEN cz_ib_rec.rural = gc_e
THEN
v_radius := gc_radius_3;
v_rural := gc_y;
v_expandable := gc_y;
v_type := gc_expd_type;
WHEN cz_ib_rec.rural = gc_o
THEN
v_radius := gc_radius_3;
v_rural := gc_y;
v_expandable := gc_n;
v_type := gc_aggte_type;
END CASE;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_radius: '
|| v_radius
|| ' v_rural: '
|| v_rural
|| ' v_expandable: '
|| v_expandable
|| ' v_type: '
|| v_type
,p_prog_unit => v_prog_unit
);
BEGIN
SELECT lookup_code
INTO v_ins_type_code
FROM csi_lookups
WHERE lookup_type = 'CSI_INST_TYPE_CODE'
AND UPPER (meaning) = UPPER (v_type);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'Instance type :'
|| v_type
|| ' Instance Type code: '
|| v_ins_type_code
|| ' for zone_number: '
|| cz_ib_rec.zone_num
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
gv_process_msg := 'Instance Type Code not found 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_zone_num
,gv_process_msg
);
END;
BEGIN
gv_process_msg := NULL;
SELECT inventory_item_id, organization_id
INTO v_inv_item_id, v_organization_id
FROM mtl_system_items_b
WHERE segment1 = gc_item_desc;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => v_prog_unit
|| '-'
|| v_process_status
|| '-at item id'
|| gv_process_msg
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
gv_process_msg := 'Inventory Item not found 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_zone_num
,gv_process_msg
);
END;
BEGIN
gv_process_msg := NULL;
SELECT instance_status_id
INTO v_instance_status_id
FROM csi_instance_statuses
WHERE UPPER (NAME) =
DECODE (cz_ib_rec.zonestat
,'SZ', 'SERVED'
,'UZ', 'UNSERVED'
,'EZ', 'EXEMPT'
,'HZ', 'HOLD'
,cz_ib_rec.zonestat
)
AND SYSDATE BETWEEN start_date_active AND end_date_active;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'Instance status '
|| cz_ib_rec.zonestat
|| ' of Status_id is '
|| v_instance_status_id
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
v_process_status := 'E';
gv_process_msg := 'Instance Status not found 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_zone_num
,gv_process_msg
);
END;
BEGIN
gv_process_msg := NULL;
v_party_id := NULL;
v_party_site_id := NULL;
v_party_site_id := NULL;
get_party_info
(p_dealer_id =>'BM1734' -- cz_ib_rec.dealer_id-test
--
, p_party_id => v_party_id
,p_party_site_id => v_party_site_id
,p_account_id => v_account_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'Party_id: '
|| v_party_id
|| ' party_site_id: '
|| v_party_site_id
|| ' Account_id: '
|| v_account_id
|| ' of the Dealer_id: '
|| cz_ib_rec.dealer_id
,p_prog_unit => v_prog_unit
);
IF v_party_id = -1
THEN
v_process_status := 'E';
gv_process_msg :=
'Party is not exist in DORIIS for Dealer_ID: '
|| cz_ib_rec.dealer_id;
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_exp_rule_id
,p_request_id
,gv_zone_num
,gv_process_msg
);
RAISE e_exception;
ELSIF v_party_id = -2
THEN
v_process_status := 'E';
gv_process_msg :=
'More than one Parties exist in DORIIS for Dealer_ID: '
|| cz_ib_rec.dealer_id;
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_exp_rule_id
,p_request_id
,gv_zone_num
,gv_process_msg
);
RAISE e_exception;
END IF;
END party_info;
BEGIN
--Populating the data structure csi_datastructures_pub.party_tbl
v_party_tbl (1).party_source_table := gc_party_source_table;
v_party_tbl (1).party_id := v_party_id;
v_party_tbl (1).relationship_type_code :=
gc_relationship_type_code;
v_party_tbl (1).contact_flag := gc_n;
v_party_tbl (1).active_start_date := SYSDATE;
v_party_tbl (1).object_version_number := 1;
--Populating the data structure csi_datastructures_pub.party_account_tbl
v_account_tbl (1).party_account_id := v_account_id;
v_account_tbl (1).relationship_type_code :=
gc_relationship_type_code;
v_account_tbl (1).active_start_date := SYSDATE;
v_account_tbl (1).object_version_number := 1;
v_account_tbl (1).parent_tbl_index := 1;
v_account_tbl (1).call_contracts := gc_n;
v_instance_rec.inventory_item_id := v_inv_item_id;
v_instance_rec.inv_master_organization_id := v_organization_id;
v_instance_rec.external_reference := cz_ib_rec.zone_num;
v_instance_rec.mfg_serial_number_flag := gc_n;
v_instance_rec.quantity := 1;
v_instance_rec.unit_of_measure := gc_each;
v_instance_rec.accounting_class_code :=
gc_accounting_class_code;
v_instance_rec.instance_type_code := v_ins_type_code;
v_instance_rec.instance_status_id := v_instance_status_id;
v_instance_rec.customer_view_flag := gc_y;
v_instance_rec.active_start_date :=
TO_DATE (cz_ib_rec.eventdate, xxd_utils.gc_dor_dt_format);
v_instance_rec.install_date :=
TO_DATE (cz_ib_rec.eventdate, xxd_utils.gc_dor_dt_format);
v_instance_rec.location_type_code := gc_location_type_code;
v_instance_rec.install_location_type_code := 'HZ_PARTY_SITES';
v_instance_rec.location_id := v_party_site_id;
v_instance_rec.install_location_id := v_party_site_id;
v_instance_rec.CONTEXT := gc_context;
v_instance_rec.attribute2 := v_rural;
v_instance_rec.attribute1 := v_radius;
v_instance_rec.attribute3 := v_expandable;
v_instance_rec.attribute17 := v_zsdate;
v_instance_rec.object_version_number := 1;
v_txn_rec.transaction_date := SYSDATE;
v_txn_rec.source_transaction_date := SYSDATE;
v_txn_rec.transaction_type_id := 1;
v_instance_id := NULL;
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
create_instance
(p_instance_rec => v_instance_rec
,p_ext_attrib_values_tbl => v_ext_attrib_values
,p_party_tbl => v_party_tbl
,p_account_tbl => v_account_tbl
,p_pricing_attrib_tbl => v_pricing_attrib_tbl
,p_org_assignments_tbl => v_org_assignments_tbl
,p_asset_assignment_tbl => v_asset_assignment_tbl
,p_txn_rec => v_txn_rec
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
v_instance_id := v_instance_rec.instance_id;
v_instance_number := v_instance_rec.instance_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_instance_id, 0) = 0)
THEN
ROLLBACK TO create_cz_instance;
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 Instance for Zone_number: '
|| cz_ib_rec.zone_num
|| 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_num
,gv_process_msg
);
ELSE
gv_process_msg :=
'Instance is created for Zone_number '
|| cz_ib_rec.zone_num
|| ' Instance_number : '
|| v_instance_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
-- Assigning the values for Assoiated Aprty Deatils.
IF (NVL (cz_ib_rec.alt_rc, 'N') <> 'N')
AND (cz_ib_rec.zonestat = 'EZ')
THEN
gv_process_msg := NULL;
v_party_id := NULL;
v_account_id := NULL;
get_assosiate_party_info (p_alt_rc => cz_ib_rec.alt_rc
---'123456'
--
, p_party_id => v_party_id
,p_account_id => v_account_id
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'Ass_Party_id: '
|| v_party_id
|| ' Ass_Account_id: '
|| v_account_id
|| ' of the Alt_RC: '
|| cz_ib_rec.alt_rc
,p_prog_unit => v_prog_unit
);
IF v_party_id = -1
THEN
v_process_status := 'W';
gv_process_msg :=
'Party is not exist in DORIIS for ALT_RC: '
|| cz_ib_rec.dealer_id
|| ' of Zone_number: '
|| cz_ib_rec.zone_num;
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_num
,gv_process_msg
);
ELSIF v_party_id = -2
THEN
v_process_status := 'E';
gv_process_msg :=
'More than one Parties exist in DORIIS for ALT_RC: '
|| cz_ib_rec.dealer_id;
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_exp_rule_id
,p_request_id
,gv_zone_num
,gv_process_msg
);
RAISE e_exception;
END IF;
BEGIN
v_relationship_code := NULL;
SELECT ipa_relation_type_code
INTO v_relationship_code
FROM csi_ipa_relation_types
WHERE UPPER (NAME) = gc_relationship_type; -- test
--upper(cz_ib_rec.relationship_type);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob
,p_new_text => 'Assosiation relationship code: '
|| v_relationship_code
,p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_process_status := 'E';
ROLLBACK TO create_cz_instance;
gv_process_msg :=
'Relationship Type: '
|| cz_ib_rec.relationship_type
|| ' not exist in DPRIIS';
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_zone_num
,gv_process_msg
);
RAISE e_exception;
END;
SELECT object_version_number
INTO v_obj_ver_num
FROM csi_item_instances
WHERE instance_id = v_instance_id;
v_party_tbl.DELETE;
v_account_tbl.DELETE;
v_party_tbl (1).instance_id := v_instance_id;
v_party_tbl (1).party_source_table :=
gc_party_source_table;
v_party_tbl (1).party_id := v_party_id;
v_party_tbl (1).relationship_type_code :=
v_relationship_code;
--Test
--v_relationship_type;
v_party_tbl (1).contact_flag := gc_n;
v_party_tbl (1).active_start_date := SYSDATE;
v_party_tbl (1).object_version_number := v_obj_ver_num;
--Populating the data structure csi_datastructures_pub.party_account_tbl
v_account_tbl (1).party_account_id := v_account_id;
v_account_tbl (1).relationship_type_code :=
v_relationship_code;
--Test
--v_relationship_type;
--'ALTERNATE RECYCLER';
v_account_tbl (1).active_start_date := SYSDATE;
v_account_tbl (1).object_version_number := v_obj_ver_num;
v_account_tbl (1).parent_tbl_index := 1;
v_account_tbl (1).call_contracts := gc_n;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'v_process_status -'
|| v_process_status
|| ' v_party_id-'
|| v_party_id
,p_prog_unit => v_prog_unit
);
IF -- NVL (v_process_status, 'N') in ('N') AND -- test
(v_party_id NOT IN (-1, -2)
)
THEN
gr_return_status := NULL;
gr_msg_count := NULL;
gr_msg_data := NULL;
update_instance (p_party_tbl => v_party_tbl
,p_account_tbl => v_account_tbl
,x_return_status => gr_return_status
,x_msg_count => gr_msg_count
,x_msg_data => gr_msg_data
);
IF (NVL (gr_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gr_msg_count, 0) > 0)
THEN
v_process_status := 'E';
ROLLBACK TO create_cz_instance;
xxd_util_pkg.combine_message (gr_msg_count
,gv_process_msg
);
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_num
,gv_process_msg
);
ELSE
gv_process_msg :=
'instance created successfully with Assosiation for zone_num: '
|| cz_ib_rec.zone_num
|| ' of Instance_number: '
|| v_instance_number
|| CHR (10)
|| '******************************************************************************************************************';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
UPDATE xxdc_czib_stg
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE zone_num = gv_zone_num;
COMMIT;
END IF;
ELSIF NVL (v_process_status, 'N') = 'W'
AND NVL (v_party_id, 0) = -1
THEN
retcode := 1;
gv_process_msg :=
'instance created successfully with Assosiation warning for zone_num: '
|| cz_ib_rec.zone_num
|| ' of Instance_Number: '
|| v_instance_number
|| CHR (10)
|| '******************************************************************************************************************';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
UPDATE xxdc_czib_stg
SET process_status = 'W'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE zone_num = gv_zone_num;
COMMIT;
END IF;
ELSE
gv_process_msg :=
'instance created successfully with out Assosiation for zone_num: '
|| cz_ib_rec.zone_num
|| ' of Instance_Number: '
|| v_instance_number
|| CHR (10)
|| '******************************************************************************************************************';
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,p_prog_unit => v_prog_unit
);
UPDATE xxdc_czib_stg
SET process_status = 'S'
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
WHERE zone_num = gv_zone_num;
END IF;
END IF;
END;
EXCEPTION
WHEN e_exception
THEN
ROLLBACK TO create_cz_instance;
gv_process_msg :=
xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLERRM|| CHR (10)
|| '------------------------------------------------------------------------------------------------------------------';
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_exp_rule_id
,p_request_id
,gv_zone_num
,gv_process_msg
);
WHEN OTHERS
THEN
ROLLBACK TO create_cz_instance;
gv_process_msg :=
xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLERRM|| CHR (10)
|| '------------------------------------------------------------------------------------------------------------------';
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_exp_rule_id
,p_request_id
,gv_zone_num
,gv_process_msg
);
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
retcode := 2;
gv_process_msg :=
xxd_utils.gc_exception || v_prog_unit || ', error: ' || SQLERRM;
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => gv_process_msg
,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
,gv_zone_num
,gv_process_msg
);
END convert_data;
END xxdc_czib;
/
No comments:
Post a Comment