Oracle Applications & Oracle Cloud Fusion HCM : CUSTOMER Conversion

CREATE OR REPLACE PACKAGE BODY xx_arcnv01_customer_load_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ARCNV01_CUSTOMER_LOAD_PKG
Author's Name : Madhu Dhare
Date Written : 18-Dec-2015
RICEW Object id : AR_CNV_01
Purpose : Package Body
Program Style :
Maintenance History:
Date: Name Version Remarks
----------- ------------- ----------- ------------------
18-Dec-2015 Madhu Dhare 0.1 Initial Version
18-Dec-2015 Madhu Dhare 1.0 Changes made as per the changes recomended
--------------------------------------------------------------
*/
/*-----------------------------------------------------------

Purpose-Procedure to Process Customer record
--
-------------------------------------------------------------*/
PROCEDURE process_customer_record (
p_customer_process IN xx_ar_cust_preint%ROWTYPE,
p_success OUT VARCHAR2,
p_error_api OUT VARCHAR2,
p_message_count OUT NUMBER,
p_message_data OUT VARCHAR2
);

/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Procedure to Update Custom Tables
--
-------------------------------------------------------------*/
PROCEDURE update_custom_table (
p_customer_id IN xx_ar_cust_stg.customer_id%TYPE,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
);

/* Cursor declare section*/
--Cursor to select from Staging table xx_ar_cust_stg to check customer_type is ORG/PERSON
CURSOR c_cust_control
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_cust_stg to move into preinterface table
CURSOR c_cust_preint
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_address_stg to move into preinterface table
CURSOR c_address_preint
IS
SELECT *
FROM xx_ar_address_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_contact_stg to move into preinterface table
CURSOR c_contact_preint
IS
SELECT *
FROM xx_ar_contact_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_cust_stg to validate customer_type ORG
CURSOR c_validate_org
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW' AND customer_type = 'ORG';

--Cursor to select from Staging table xx_ar_cust_stg to validate customer_type PERSON
CURSOR c_validate_person
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW' AND customer_type = 'PERSON';

--Cursor to select from Pre-Interface table xx_ar_cust_preint for customer_type ORG
CURSOR c_org_process
IS
SELECT *
FROM xx_ar_cust_preint
WHERE status = 'IP' AND customer_type = 'ORG';

--Cursor to select from Pre-Interface table xx_ar_cust_preint for customer_type PERSON
CURSOR c_person_process
IS
SELECT *
FROM xx_ar_cust_preint
WHERE status = 'IP' AND customer_type = 'PERSON';

--Cursor to select from Pre-Interface table xx_ar_contact_preint
CURSOR c_contacts (
p_cust_id xx_ar_contact_preint.customer_id%TYPE,
p_addr_id xx_ar_address_preint.address_id%TYPE
)
IS
SELECT *
FROM xx_ar_contact_preint
WHERE customer_id = p_cust_id AND address_id = p_addr_id;

--Cursor to select from Pre-Interface table xx_ar_address_preint
CURSOR c_address (p_cust_id xx_ar_address_preint.customer_id%TYPE)
IS
SELECT *
FROM xx_ar_address_preint
WHERE customer_id = p_cust_id;

/*--------------------------------------------------------------------------------------------------------
Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_error_api VARCHAR2 (200);
l_msg_count VARCHAR2 (2);
l_msg_data VARCHAR2 (300);
l_success VARCHAR2 (1);
l_completed BOOLEAN;
l_process_status NUMBER;
l_process_id NUMBER;
l_count NUMBER := 0;
l_cust_count NUMBER := 0;
l_person_error NUMBER := 0;
l_org_error NUMBER := 0;
l_cnt_org NUMBER := 0;
l_cnt_profile NUMBER := 0;
l_rec_insert_cnt NUMBER := 0;
l_cust_cnt NUMBER := 0;
l_addr_cnt NUMBER := 0;
l_contact_cnt NUMBER := 0;
l_success_pr NUMBER := 0;
l_set_process_count NUMBER := 0;
l_file_record_count NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_ARCNV01_CUSTOMER_LOAD_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'AR_CNV_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
l_error_cat_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
-- Common Validations Variables
/* Customer upload variables used in APIs */
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_organization_rec hz_party_v2pub.organization_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_id NUMBER;
p_account_number VARCHAR2 (2000);
p_party_id NUMBER;
p_party_number VARCHAR2 (2000);
p_profile_id NUMBER;
p_return_status VARCHAR2 (2000);
p_msg_count NUMBER;
p_msg_data VARCHAR2 (2000);
p_location_rec hz_location_v2pub.location_rec_type;
p_location_id NUMBER;
p_party_site_rec hz_party_site_v2pub.party_site_rec_type;
p_party_site_id NUMBER;
p_party_site_number VARCHAR2 (2000);
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_cust_acct_site_id NUMBER;
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_rec1 hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec1 hz_customer_profile_v2pub.customer_profile_rec_type;
p_customer_profile_rec2 hz_customer_profile_v2pub.customer_profile_rec_type;
p_site_use_id NUMBER;
p_relationship_rec_type hz_relationship_v2pub.relationship_rec_type;
p_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
p_cust_acct_relate_rec hz_cust_account_v2pub.cust_acct_relate_rec_type;
p_org_contact_id NUMBER;
p_party_rel_id NUMBER;
l_cust_account_id NUMBER (15);
l_party_id NUMBER (15);
l_cust_acct_site_id NUMBER (15);
l_site_use_id NUMBER (15);
l_org_contact_party_id NUMBER (15);
l_cust_acct_role_id NUMBER (15);
l_org_id NUMBER;
l_related_org_party_id NUMBER;
l_custom_profile_id NUMBER;
p_cust_account_profile_id NUMBER;
p_contact_point_id NUMBER;
p_cust_account_role_id NUMBER;
p_responsibility_id NUMBER;
p_create_profile_amt VARCHAR2 (2000);
p_contact_point_rec apps.hz_contact_point_v2pub.contact_point_rec_type;
p_person_rec apps.hz_party_v2pub.person_rec_type;
p_email_rec apps.hz_contact_point_v2pub.email_rec_type;
p_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
p_phone_rec apps.hz_contact_point_v2pub.phone_rec_type;
p_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
p_web_rec_type hz_contact_point_v2pub.web_rec_type;
p_cr_cust_acc_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
p_role_responsibility_rec hz_cust_account_role_v2pub.role_responsibility_rec_type;
l_counter NUMBER;
l_update_success VARCHAR2 (2);
l_update_message VARCHAR2 (255);
l_proc_stg_recs NUMBER := 0;
l_err_stg_recs NUMBER := 0;
l_succ_stg_recs NUMBER := 0;
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_raise_reject_record EXCEPTION;
e_record_count_error EXCEPTION;
e_data_insert_error EXCEPTION;
e_data_update_error EXCEPTION;
e_apps_init_error EXCEPTION;

--------------------------------------------------------------
--Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
BEGIN --Main Begin
BEGIN
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name,
p_program_type => g_program_type,
p_ricew_id => l_ricewid,
p_request_id => l_request_id
);

IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;

--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Err Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Cust Id'; --Third Error Header
l_error_rec.identifier4 := 'Error Count'; --Fourth Error Header
l_error_rec.identifier5 := 'Name'; --Fifth Error Header
l_error_rec.identifier6 := 'Type'; --Sixth Error Header
l_error_rec.identifier7 := 'API Name'; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);

--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;

--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);

--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;

--
--Initialize apps
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);

IF l_process_status <> 0
THEN
--Call EMF and exit from the program
xx_trace.h ('Apps initialize problem');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00000',
p_identifier2 => 'Apps initialize problem.',
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => 'xx_common_validations_pkg.init_apps_params',
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_apps_init_error;
END IF;

IF p_run_mode = 'F'
THEN
BEGIN --validate_at_control_level starts
-- File validation starts
BEGIN
BEGIN
SELECT NVL (record_count, 0)
INTO l_file_record_count
FROM xx_ar_file_stg
WHERE status = 'NW';
EXCEPTION
WHEN OTHERS
THEN
l_file_record_count := 0;
END;

l_cust_cnt := 0;

SELECT COUNT (*)
INTO l_cust_cnt
FROM xx_ar_cust_stg
WHERE status = 'NW';

SELECT COUNT (*)
INTO l_addr_cnt
FROM xx_ar_address_stg
WHERE status = 'NW';

SELECT COUNT (*)
INTO l_contact_cnt
FROM xx_ar_contact_stg
WHERE status = 'NW';

IF l_file_record_count <>
(l_cust_cnt + l_contact_cnt + l_addr_cnt
)
THEN
UPDATE xx_ar_file_stg
SET status = 'ER';

--- Raise record count error and exit from the program
xx_trace.l ('Record count mismatch in the data file');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Record count mismatch.',
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => 'Record Count check at control level',
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_record_count_error;
END IF;

COMMIT;
END;

-- File validation ends
-- Customer Type validation starts
FOR r_cust_control IN c_cust_control
LOOP
IF NVL (r_cust_control.customer_type, 'X') NOT IN
('ORG', 'PERSON')
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Invalid Customer Type');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Customer Type:'
|| NVL
(r_cust_control.customer_type,
'NULL'
),
p_identifier3 => r_cust_control.customer_id,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_cust_control.customer_id,
p_req_id => NULL,
p_status => 'ER',
p_table_type => 'STAGING',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000042',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
END IF;
END LOOP;

COMMIT;

END; --validate_at_control_level ends

BEGIN --move_data_preint_table starts
FOR r_cust_preint IN c_cust_preint
LOOP
INSERT INTO xx_ar_cust_preint
(status, request_id,
customer_id, customer_account_number,
customer_type,
customer_first_name,
customer_last_name,
customer_title,
dob, gender,
nationality,
job_title,
related_org_id,
relationship_type,
role_type,
site_use_code,
responsibility_code,
LANGUAGE,
acctg_type,
customer_class,
profile_class,
start_date_active,
end_date_active, party_id, party_number,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17,
attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23,
attribute24, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_cust_preint.customer_id, NULL,
r_cust_preint.customer_type,
r_cust_preint.customer_first_name,
r_cust_preint.customer_last_name,
r_cust_preint.customer_title,
r_cust_preint.dob, r_cust_preint.gender,
r_cust_preint.nationality,
r_cust_preint.job_title,
r_cust_preint.related_org_id,
r_cust_preint.relationship_type,
r_cust_preint.role_type,
r_cust_preint.site_use_code,
r_cust_preint.responsibility_code,
r_cust_preint.LANGUAGE,
r_cust_preint.acctg_type,
r_cust_preint.customer_class,
r_cust_preint.profile_class,
r_cust_preint.start_date_active,
r_cust_preint.end_date_active, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

FOR r_address_preint IN c_address_preint
LOOP
INSERT INTO xx_ar_address_preint
(status, request_id,
address_id,
primary_address,
customer_id,
address_type,
address1,
address2,
address3,
address4,
city,
state,
postal_code,
county,
country, attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19,
attribute20, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_address_preint.address_id,
r_address_preint.primary_address,
r_address_preint.customer_id,
r_address_preint.address_type,
r_address_preint.address1,
r_address_preint.address2,
r_address_preint.address3,
r_address_preint.address4,
r_address_preint.city,
r_address_preint.state,
r_address_preint.postal_code,
r_address_preint.county,
r_address_preint.country, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

FOR r_contact_preint IN c_contact_preint
LOOP
INSERT INTO xx_ar_contact_preint
(status, request_id,
contact_id,
customer_id,
address_id,
phone,
phone_ext,
fax,
mobile,
email,
usage_type, attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19,
attribute20, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_contact_preint.contact_id,
r_contact_preint.customer_id,
r_contact_preint.address_id,
r_contact_preint.phone,
r_contact_preint.phone_ext,
r_contact_preint.fax,
r_contact_preint.mobile,
r_contact_preint.email,
r_contact_preint.usage_type, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
--raise data insert error..exit from the program
xx_trace.h ('Data Insert Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);

RAISE e_data_insert_error;
END; --move_data_preint_table ends

SELECT COUNT (*)
INTO l_proc_stg_recs
FROM xx_ar_cust_stg;

SELECT COUNT (*)
INTO l_succ_stg_recs
FROM xx_ar_cust_stg
WHERE status = 'NW';

l_err_stg_recs := l_proc_stg_recs - l_succ_stg_recs;
/*----------------------------------------------------------------------------
--Inserting Summary Counts for Staging Table Records
----------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id,
p_display_name => l_program_name
|| ': Staging',
p_total_recs => l_proc_stg_recs,
p_successful_recs => l_succ_stg_recs,
p_error_recs => l_err_stg_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
END IF; --p_run_mode='F' ends

l_cust_count := 0;

BEGIN --validate_business_level_org starts
FOR r_org_process IN c_org_process
LOOP
l_error_flag := 0;
l_cust_count := l_cust_count + 1;

IF r_org_process.customer_first_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Organization Name is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00005',
p_identifier2 => 'Organization Name is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_org_process.end_date_active < SYSDATE
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Inactive Customer');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00007',
p_identifier2 => 'Inactive Customer',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_org_process.profile_class IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_profile
FROM ar_customer_profile_classes
WHERE NAME = r_org_process.profile_class;

IF l_cnt_profile = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Invalid Profile Class');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00008',
p_identifier2 => 'Invalid Profile Class',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

FOR r_address IN c_address (r_org_process.customer_id)
LOOP
IF r_address.address1 IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address1 is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00010',
p_identifier2 => 'Address1 is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.city IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address City is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00011',
p_identifier2 => 'Address City is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.country IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address Country is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00012',
p_identifier2 => 'Address Country is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END LOOP;

IF l_error_flag = 1
THEN
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000044',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_org_error := l_org_error + 1;
END IF;

IF l_org_error = 1000
THEN
COMMIT;
l_org_error := 0;
END IF;
END LOOP;

COMMIT;
END; --validate_business_level_org ends

l_success_pr := 0;

BEGIN --process_customer_org starts
l_counter := 0;

FOR r_org_process IN c_org_process
LOOP
l_counter := l_counter + 1;
process_customer_record (p_customer_process => r_org_process,
p_success => l_success,
p_error_api => l_error_api,
p_message_count => l_msg_count,
p_message_data => l_msg_data
);

IF l_success = 'N'
THEN
--Call EMF
xx_trace.m ('Oracle API Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 30 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00014',
p_identifier2 => l_msg_data,
p_identifier3 => r_org_process.customer_id,
p_identifier4 => l_msg_count,
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => l_error_api,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000045',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
-- Update interface tables
ELSE
-- Update interface tables
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'PR',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000046',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_success_pr := l_success_pr + 1;
END IF;

IF l_counter = 1000
THEN
COMMIT;
l_counter := 0;
END IF;
END LOOP;

COMMIT;
END; --process_customer_org ends

BEGIN --validate_business_level_person starts
FOR r_person_process IN c_person_process
LOOP
l_error_flag := 0;
l_cust_count := l_cust_count + 1;

IF r_person_process.customer_first_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('First Name Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00016',
p_identifier2 => 'First Name Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.customer_last_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Last Name Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00017',
p_identifier2 => 'Last Name Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.customer_title IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Title Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00018',
p_identifier2 => 'Title Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.end_date_active < SYSDATE
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Customer Is Inactive');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00020',
p_identifier2 => 'Customer Is Inactive',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.profile_class IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_profile
FROM ar_customer_profile_classes
WHERE NAME = r_person_process.profile_class;

IF l_cnt_profile = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Invalid Profile Class');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00021',
p_identifier2 => 'Invalid Profile Class',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

IF r_person_process.related_org_id IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_org
FROM hz_cust_accounts_all a
WHERE orig_system_reference =
r_person_process.related_org_id;

IF l_cnt_org = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Related Organization does not exist');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00022',
p_identifier2 => 'Related Organization does not exist',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

FOR r_address IN c_address (r_person_process.customer_id)
LOOP
IF r_address.address1 IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address1 Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00024',
p_identifier2 => 'Address1 Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.city IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address City Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00025',
p_identifier2 => 'Address City Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.country IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address Country Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00026',
p_identifier2 => 'Address Country Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END LOOP;

IF l_error_flag = 1
THEN
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000047',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_person_error := l_person_error + 1;
END IF;

IF l_person_error = 1000
THEN
COMMIT;
l_person_error := 0;
END IF;
END LOOP;

COMMIT;
END; --validate_business_level_person ends

BEGIN --process_customer_person starts
FOR r_person_process IN c_person_process
LOOP
l_counter := 0;
process_customer_record
(p_customer_process => r_person_process,
p_success => l_success,
p_error_api => l_error_api,
p_message_count => l_msg_count,
p_message_data => l_msg_data
);

IF l_success = 'N'
THEN
--Call EMF
xx_trace.m ('Oracle API Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 30 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00015',
p_identifier2 => l_msg_data,
p_identifier3 => r_person_process.customer_id,
p_identifier4 => l_msg_count,
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => l_error_api,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000049',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
-- Update interface tables
ELSE
-- Update interface tables
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'PR',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000048',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_success_pr := l_success_pr + 1;
END IF;

IF l_counter = 1000
THEN
COMMIT;
l_counter := 0;
END IF;
END LOOP;

COMMIT;
END; --process_customer_person ends

/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_ar_cust_preint
WHERE status = 'PR';

--
DELETE FROM xx_ar_contact_preint
WHERE status = 'PR';

--
DELETE FROM xx_ar_address_preint
WHERE status = 'PR';

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;

/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_cust_count - l_success_pr;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_cust_count,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;

--
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;

--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;

--
l_return_value := NULL;

--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;

COMMIT;
END IF;
EXCEPTION
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
WHEN e_record_count_error
THEN
l_error_recs := l_cust_cnt;
RAISE;
WHEN e_apps_init_error
THEN
RAISE;
WHEN e_data_update_error
THEN
RAISE;
WHEN e_data_insert_error
THEN
RAISE;
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
--High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000041',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
EXCEPTION --Main Exception
--Main Exception Block
WHEN OTHERS
THEN
COMMIT;

--Write to error
l_error_flag := 'Y';
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_cust_cnt,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;

/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20
--Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;

l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;

l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;

/*-----------------------------------------------------------

Purpose-Procedure to Process Customer record
--
-------------------------------------------------------------*/
PROCEDURE process_customer_record (
p_customer_process IN xx_ar_cust_preint%ROWTYPE,
p_success OUT VARCHAR2,
p_error_api OUT VARCHAR2,
p_message_count OUT NUMBER,
p_message_data OUT VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
p_success := 'S';
l_org_id := fnd_profile.VALUE ('ORG_ID');

-- Create Customer Account
IF p_customer_process.profile_class IS NOT NULL
THEN
SELECT customer_profile_class_id
INTO l_custom_profile_id
FROM ar_customer_profile_classes
WHERE NAME = p_customer_process.profile_class;
END IF;

IF p_customer_process.customer_type = 'PERSON'
AND p_customer_process.related_org_id IS NOT NULL
THEN
SELECT party_id
INTO l_related_org_party_id
FROM hz_cust_accounts
WHERE orig_system_reference =
TO_CHAR (p_customer_process.related_org_id);
END IF;

IF p_customer_process.customer_type = 'PERSON'
THEN
p_person_rec.person_first_name :=
p_customer_process.customer_first_name;
p_person_rec.person_last_name :=
p_customer_process.customer_last_name;
p_person_rec.person_pre_name_adjunct :=
p_customer_process.customer_title;
p_person_rec.gender := p_customer_process.gender;
p_person_rec.date_of_birth := p_customer_process.dob;
p_person_rec.person_name_phonetic := p_customer_process.job_title;
p_person_rec.party_rec.orig_system_reference :=
p_customer_process.customer_id;
p_person_rec.party_rec.attribute1 := p_customer_process.LANGUAGE;
p_person_rec.party_rec.attribute2 := p_customer_process.nationality;
p_cust_account_rec.account_number :=
p_customer_process.customer_account_number;
p_cust_account_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cust_account_rec.customer_class_code :=
p_customer_process.customer_class;
p_cust_account_rec.created_by_module := 'AR01 Conv';
p_cust_account_rec.customer_type := 'R';
--Hard Coded(External customer)
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
p_customer_profile_rec.site_use_id := NULL;
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F',
p_cust_account_id,
p_account_number,
p_party_id,
p_party_number,
p_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);
ELSE
p_cust_account_rec.account_name :=
p_customer_process.customer_first_name;
p_cust_account_rec.account_number :=
p_customer_process.customer_account_number;
p_cust_account_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cust_account_rec.customer_class_code :=
p_customer_process.customer_class;
p_cust_account_rec.created_by_module := 'AR01 Conv';
p_cust_account_rec.customer_type := 'R';
--Hard Coded(External customer)
p_organization_rec.organization_name :=
p_customer_process.customer_first_name;
p_organization_rec.created_by_module := 'AR01 Conv';
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
p_customer_profile_rec.site_use_id := NULL;
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_organization_rec,
p_customer_profile_rec,
'F',
p_cust_account_id,
p_account_number,
p_party_id,
p_party_number,
p_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);
END IF;

l_party_id := p_party_id;
l_cust_account_id := p_cust_account_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_cust_account_v2pub.create_cust_account';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_cust_account_v2pub.create_cust_account';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

IF p_customer_process.customer_type =
'PERSON'
--Only for PERSON TYPE starts
THEN
--Create Relation Contact and Employee
IF p_customer_process.job_title IS NOT NULL
AND p_customer_process.related_org_id IS NOT NULL
THEN
p_org_contact_rec.job_title_code := p_customer_process.job_title;
p_org_contact_rec.orig_system_reference :=
p_customer_process.customer_id;
p_org_contact_rec.party_rel_rec.object_id :=
l_related_org_party_id;
/* Reffering Party ID created by Create Customer API */
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.subject_id := l_party_id;
/* Reffering Party ID created by Create Person API */
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code :=
p_customer_process.responsibility_code;
p_org_contact_rec.party_rel_rec.relationship_type :=
p_customer_process.relationship_type;
p_org_contact_rec.created_by_module := 'AR01 Conv';
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
p_org_contact_id,
p_party_rel_id,
p_party_id,
p_party_number,
p_return_status,
p_msg_count,
p_msg_data
);
l_org_contact_party_id := p_party_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_party_contact_v2pub.create_org_contact';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_party_contact_v2pub.create_org_contact';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Customer Role
p_cr_cust_acc_role_rec.party_id := l_org_contact_party_id;
/* l_party_id Reffering value for party_id from create_org_contact */
p_cr_cust_acc_role_rec.cust_account_id := l_cust_account_id;
p_cr_cust_acc_role_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cr_cust_acc_role_rec.role_type := p_customer_process.role_type;
p_cr_cust_acc_role_rec.created_by_module := 'AR01 Conv';
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_cr_cust_acc_role_rec,
p_cust_account_role_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_cust_acct_role_id := p_cust_account_role_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_role_v2pub.create_cust_account_role';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_role_v2pub.create_cust_account_role';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Customer Responsibility
p_role_responsibility_rec.cust_account_role_id :=
l_cust_acct_role_id;
/* Reffering value for p_cust_account_role_id from above */
p_role_responsibility_rec.responsibility_type :=
p_customer_process.site_use_code;
p_role_responsibility_rec.orig_system_reference :=
p_customer_process.customer_id;
p_role_responsibility_rec.created_by_module := 'AR01 Conv';
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
p_responsibility_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_role_v2pub.create_role_responsibility';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_role_v2pub.create_role_responsibility';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;
END IF; --Only for PERSON TYPE ends

--Create Location
FOR address IN c_address (p_customer_process.customer_id)
LOOP
fnd_client_info.set_org_context (TO_CHAR (l_org_id));
p_location_rec.country := NVL (address.country, '');
p_location_rec.address1 := address.address1;
p_location_rec.address2 := NVL (address.address2, '');
p_location_rec.address3 := NVL (address.address3, '');
p_location_rec.address4 := NVL (address.address4, '');
p_location_rec.orig_system_reference := address.address_id;
p_location_rec.city := NVL (address.city, '');
p_location_rec.postal_code := NVL (address.postal_code, '');
p_location_rec.state := NVL (address.state, '');
p_location_rec.county := NVL (address.county, '');
p_location_rec.created_by_module := 'AR01 Conv';
hz_location_v2pub.create_location ('T',
p_location_rec,
p_location_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_location_v2pub.create_location';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_location_v2pub.create_location';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Party Site
p_party_site_rec.party_id := l_party_id;
p_party_site_rec.location_id := p_location_id;
p_party_site_rec.identifying_address_flag :=
NVL (address.primary_address, 'N');
p_party_site_rec.orig_system_reference :=
NVL (address.address_id, '');
p_party_site_rec.created_by_module := 'AR01 Conv';
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
p_party_site_id,
p_party_site_number,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_party_site_v2pub.create_party_site';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_party_site_v2pub.create_party_site';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

-- Cust Acct Site
p_cust_acct_site_rec.cust_account_id := l_cust_account_id;
p_cust_acct_site_rec.party_site_id := p_party_site_id;
p_cust_acct_site_rec.LANGUAGE := 'US';
p_cust_acct_site_rec.orig_system_reference :=
NVL (address.address_id, '');
p_cust_acct_site_rec.created_by_module := 'AR01 Conv';
hz_cust_account_site_v2pub.create_cust_acct_site
('T',
p_cust_acct_site_rec,
p_cust_acct_site_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_cust_acct_site_id := p_cust_acct_site_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_acct_site';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_acct_site';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

-- Create Cust Site Use
IF address.address_type IS NOT NULL
THEN
p_cust_site_use_rec.cust_acct_site_id := l_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := address.address_type;
p_cust_site_use_rec.orig_system_reference :=
NVL (address.address_id, '');
p_cust_site_use_rec.created_by_module := 'AR01 Conv';
p_cust_site_use_rec.gl_id_rec := '';

IF NVL (address.primary_address, 'N') = 'Y'
THEN
p_cust_site_use_rec.primary_flag := 'Y';
ELSE
p_cust_site_use_rec.primary_flag := 'N';
END IF;

hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_rec,
p_customer_profile_rec1,
'',
'',
p_site_use_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_site_use_id := p_site_use_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_site_use';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_site_use';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Cust Profile
p_customer_profile_rec.created_by_module := 'AR01 Conv';
p_customer_profile_rec.site_use_id := l_site_use_id;
p_customer_profile_rec.cust_account_id := l_cust_account_id;
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
hz_customer_profile_v2pub.create_customer_profile
('T',
p_customer_profile_rec,
p_create_profile_amt,
p_cust_account_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_customer_profile_v2pub.create_customer_profile';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_customer_profile_v2pub.create_customer_profile';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

IF p_customer_process.customer_type = 'ORG'
THEN
p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id := p_party_site_id;
ELSE
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_party_id;
END IF;

FOR contacts IN c_contacts (p_customer_process.customer_id,
address.address_id
)
LOOP
--Create Email
IF contacts.email IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_email_rec.email_format := 'MAILHTML';
p_email_rec.email_address := contacts.email;
hz_contact_point_v2pub.create_email_contact_point
('T',
p_contact_point_rec,
p_email_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_email_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_email_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

-- Create Phone
IF contacts.phone IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.phone;
p_phone_rec.phone_extension := contacts.phone_ext;
p_phone_rec.phone_line_type := 'TELEPHONE';
--'OFFICE'; --Hard Coded
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

--Create Mobile
IF contacts.mobile IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.mobile;
p_phone_rec.phone_extension := NULL;
p_phone_rec.phone_line_type := 'MOBILE';
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

-- Fax
IF contacts.fax IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.fax;
p_phone_rec.phone_extension := NULL;
p_phone_rec.phone_line_type := 'FAX';
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;
END LOOP; -- Contact cursor ends
END LOOP; --Address Cursor ends

COMMIT;
EXCEPTION
WHEN e_raise_reject_record
THEN
ROLLBACK;
WHEN OTHERS
THEN
ROLLBACK;
p_error_api := 'process_customer_record';
p_message_count := 0;
p_message_data := SUBSTR (SQLERRM, 1, 255);
p_success := 'N';
END process_customer_record;

PROCEDURE update_custom_table (
p_customer_id IN xx_ar_cust_stg.customer_id%TYPE,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
)
AS
BEGIN
p_success := 'S';
p_message_data := NULL;

IF p_table_type = 'STAGING'
THEN
UPDATE xx_ar_cust_stg
SET status = p_status
WHERE customer_id = p_customer_id;

UPDATE xx_ar_address_stg
SET status = p_status
WHERE customer_id = p_customer_id;

UPDATE xx_ar_contact_stg
SET status = p_status
WHERE customer_id = p_customer_id;
ELSIF p_table_type = 'PREINT'
THEN
UPDATE xx_ar_cust_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;

UPDATE xx_ar_address_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;

UPDATE xx_ar_contact_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;
ELSE
p_success := 'F';
p_message_data := 'Invalid Input Supplied';
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_success := 'F';
p_message_data := SUBSTR (SQLERRM, 1, 255);
END update_custom_table;
END xx_arcnv01_customer_load_pkg;
/

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday, 8 January 2017

CUSTOMER Conversion

No comments :

Contact us