CUSTOMER CONVERSION PACKAGE
CREATE OR REPLACE PACKAGE BODY XXMTZ_AR_CSTMR_CONV_API_PKG
AS
--
-- --------------------------------------------------------------
-- Global Variables Declaration
-- --------------------------------------------------------------
gc_created_by_module CONSTANT VARCHAR2 (30) := 'TCA_V2_API'; --Created by module from Lookup type "HZ_CREATED_BY_MODULES"
gc_user_id NUMBER := fnd_global.user_id; --1318;
gc_resp_id NUMBER := fnd_global.resp_id; --20678;
gc_resp_appl_id NUMBER := 222; --fnd_global.resp_appl_id;
gc_orig_system VARCHAR2(30) := 'ORACLE_AIA';
PROCEDURE xxmtz_ar_cstmr_cnv_main (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_process_action IN VARCHAR2,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2)
IS
lv_process_action VARCHAR2 (10) := p_process_action;
lv_org_id NUMBER := p_org_id;
lv_errbuf VARCHAR2 (2000);
lv_retcode NUMBER;
ex_nullv_action EXCEPTION;
ex_invalid_action EXCEPTION;
ex_main_exception EXCEPTION;
BEGIN
IF lv_process_action IS NULL THEN
RAISE ex_nullv_action;
END IF;
IF lv_process_action NOT IN ('VALIDATE', 'UPLOAD') THEN --V(Validate) U(Upload)
RAISE ex_invalid_action;
END IF;
IF lv_process_action = 'VALIDATE' THEN
xxmtz_ar_cstmr_cnv_val_prc (lv_errbuf, lv_retcode, lv_org_id,p_cust_number);
IF lv_retcode = 2 THEN
RAISE ex_main_exception;
END IF;
x_retcode := lv_retcode;
x_errbuf := lv_errbuf;
display_message('log','Validation Procedure Call has Completed');
ELSIF lv_process_action = 'UPLOAD'
THEN
xxmtz_ar_cstmr_cnv_load_prc (lv_errbuf, lv_retcode, lv_org_id,p_cust_number);
IF lv_retcode = 2 THEN
RAISE ex_main_exception;
END IF;
x_retcode := lv_retcode;
x_errbuf := lv_errbuf;
display_message('log', 'Load Procedure Call has Completed');
END IF;
EXCEPTION
WHEN ex_nullv_action THEN
x_retcode := 2;
display_message('log','Parameters passed to the program are NULL');
WHEN ex_invalid_action THEN
x_retcode := 2;
display_message('log', 'Parameters passed to the program are NULL');
WHEN ex_main_exception THEN
x_retcode := 2;
x_errbuf := 'Error Stage : ' || lv_errbuf;
display_message('log','Procedure Error--' || lv_retcode || '--'|| lv_errbuf);
WHEN OTHERS THEN
x_retcode := 2;
display_message('log','Error in Main Procedure--' || SQLCODE|| '--'|| SQLERRM);
END xxmtz_ar_cstmr_cnv_main; --End Mian Procedure
---Validate Procedure
PROCEDURE xxmtz_ar_cstmr_cnv_val_prc (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2 )
IS
-- ----------------------------------------------
-- Cursor Declaration
-- ----------------------------------------------
--Header Account
CURSOR cur_header IS
SELECT *
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('NEW', 'VALIDATEFAILED')
AND customer_number = NVL(p_cust_number,customer_number) --'TESTASP'
ORDER BY party_id, customer_number;
--Header Phone
CURSOR cur_header_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact
CURSOR cur_header_contact (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact Phone
CURSOR cur_header_con_phone ( p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Roles
CURSOR cur_header_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Sites Location
CURSOR cur_sites_location (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_location_stg_t
WHERE customer_number = p_customer_number;
-- Sites Use
CURSOR cur_sites_use (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
--,p_site_use IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number
-- AND site_use_code = p_site_use
ORDER BY site_use_code;
-- Sites Phone
CURSOR cur_sites_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE customer_number = p_customer_number;
-- Sites Contact
CURSOR cur_sites_con (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number;
-- Sites Contact Phone
CURSOR cur_sites_con_phone (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Site Contact Role
CURSOR cur_sites_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
--Variable Declaration
lv_party_id hz_parties.party_id%TYPE := NULL;
lv_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
lv_collector_id ar_collectors.collector_id%TYPE := NULL;
lv_statement_cycle_id ar_statement_cycles.statement_cycle_id%TYPE := NULL;
lv_dunning_letter_set_id ar_dunning_letter_sets.dunning_letter_set_id%TYPE :=NULL;
lv_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE:= NULL;
lv_header_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_location_id hz_locations.location_id%TYPE :=NULL;
lv_contact_point_id hz_contact_points.contact_point_id%TYPE :=NULL;
lv_contact_party_id hz_parties.party_id%TYPE := NULL;
lv_org_contact_id hz_relationships.party_id%TYPE := NULL;
lv_acct_role_id hz_cust_account_roles.cust_account_role_id%TYPE := NULL;
lv_contact_role_id hz_role_responsibility.responsibility_id%TYPE := NULL;
lv_loc_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_site_location_id hz_locations.location_id%TYPE :=NULL;
lv_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE := NULL;
lv_site_contact_point_id hz_contact_points.contact_point_id%TYPE :=NULL;
lv_site_cont_party_id hz_parties.party_id%TYPE := NULL;
lv_site_org_cont_id hz_relationships.party_id%TYPE := NULL;
lv_site_acct_role_id hz_cust_account_roles.cust_account_role_id%TYPE := NULL;
lv_terms_count NUMBER := 0;
lv_currency_count NUMBER := 0;
lv_lookups_exist NUMBER := 0;
lv_header_count NUMBER := 0;
lv_territory_count NUMBER := 0;
lv_header_validate_flag VARCHAR2 (1):= 'Y';
lv_header_communicate_flag VARCHAR2 (1):= 'Y';
lv_site_validate_flag VARCHAR2 (1):= 'Y';
lv_site_comm_validate_flag VARCHAR2 (1):= 'Y';
lv_header_validate_msg VARCHAR2 (4000) := NULL;
lv_header_communicate_msg VARCHAR2 (4000) := NULL;
lv_site_validate_msg VARCHAR2 (4000) := NULL;
lv_site_comm_validate_msg VARCHAR2 (4000) := NULL;
--User Exception
ex_lookup_setup_err EXCEPTION;
ex_header_total EXCEPTION;
BEGIN
display_message('log','*******BEGIN Validate Procedure**************');
-- --------------------------------------------------------------
-- Validate PARTY_SITE_USE_CODE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'PARTY_SITE_USE_CODE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR PARTY_SITE_USE_CODE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate COMMUNICATION_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'COMMUNICATION_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR COMMUNICATION_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate email_format Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'EMAIL_FORMAT'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR EMAIL_FORMAT Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CONTACT_POINT_PURPOSE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CONTACT_POINT_PURPOSE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CONTACT_POINT_PURPOSE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate SITE_USE_CODE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR SITE_USE_CODE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate SIC_CODE_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'SIC_CODE_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR SIC_CODE_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CODE_STATUS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CODE_STATUS'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CODE_STATUS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate YES/NO Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'YES/NO' AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR YES/NO Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CUSTOMER_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CUSTOMER_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CUSTOMER_CLASS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER CLASS' --Customer Class
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CUSTOMER CLASS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate TAX_PRINTING_OPTION Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'TAX_PRINTING_OPTION'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR TAX_PRINTING_OPTION Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate REGISTRY_STATUS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'REGISTRY_STATUS'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR REGISTRY_STATUS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CONTACT_ROLE_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CONTACT_ROLE_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CONTACT_ROLE_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate HZ_CREATED_BY_MODULES Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR HZ_CREATED_BY_MODULES Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
------------------------END Lookup Validation---------------------------------
SELECT COUNT (*)
INTO lv_header_count
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('NEW', 'VALIDATEFAILED');
--IF lv_header_count = 0 THEN
-- RAISE ex_header_total;
--END IF;
-- --------------------------------------------------------------
-- 1.Cursor Starts - Customer Header
-- --------------------------------------------------------------
FOR rec_header IN cur_header LOOP --Starts Header LOOP
lv_header_validate_flag := 'Y';
lv_header_validate_msg := NULL;
lv_party_id := NULL;
lv_cust_account_id := NULL;
lv_collector_id := NULL;
lv_profile_class_id := NULL;
lv_statement_cycle_id := NULL;
lv_dunning_letter_set_id := NULL;
lv_header_site_id := NULL;
lv_location_id := NULL;
lv_terms_count := 0;
lv_currency_count := 0;
display_message('log','Processgin Customer Number :'||rec_header.customer_number);
------------ Validate the Duplicate Organization-------------------------------
IF rec_header.party_type = 'ORGANIZATION' THEN
BEGIN
SELECT hp.party_id, cust_account_id
INTO lv_party_id, lv_cust_account_id
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_name = rec_header.customer_name
AND hp.party_type = 'ORGANIZATION'
AND hca.party_id = hp.party_id;
EXCEPTION WHEN OTHERS THEN
lv_party_id := NULL;
lv_cust_account_id := NULL;
END;
IF lv_party_id IS NOT NULL THEN
lv_header_validate_flag := 'N';
lv_header_validate_msg := 'Duplicate Organization Name :'||rec_header.customer_name;
END IF;
END IF;
------------ Validate Header Tolarence Should be in -100 to 100 range------------------
IF rec_header.tolerance IS NOT NULL THEN
IF rec_header.tolerance BETWEEN -100 AND 100 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Tolerance Value Must between -100 and 100. Actual Tolerance:'||rec_header.tolerance;
END IF;
END IF;
------------Validate Collector Name------------------
IF rec_header.collector_name IS NOT NULL THEN
BEGIN
SELECT collector_id
INTO lv_collector_id
FROM ar_collectors
WHERE NAME = rec_header.collector_name;
EXCEPTION WHEN OTHERS THEN
lv_collector_id := NULL;
END;
IF lv_collector_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Collector Name against AR_COLLECTORS Name:'||rec_header.collector_name;
END IF;
END IF;
--------------Validate Profile Class--------------
IF rec_header.profile_class_name IS NOT NULL THEN
BEGIN
SELECT profile_class_id
INTO lv_profile_class_id
FROM hz_cust_profile_classes
WHERE NAME = rec_header.profile_class_name;
EXCEPTION WHEN OTHERS THEN
lv_profile_class_id := NULL;
END;
IF lv_profile_class_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Profile Class is not exists :'||rec_header.profile_class_name;
END IF;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Profile Class is not exists :'||rec_header.profile_class_name;
END IF;
--------------Validate Header Statement Cycle ID against AR_STATEMENT_CYCLES-----------
IF rec_header.statement_cycle_name IS NOT NULL THEN
BEGIN
SELECT statement_cycle_id
INTO lv_statement_cycle_id
FROM ar_statement_cycles
WHERE NAME = rec_header.statement_cycle_name;
EXCEPTION WHEN OTHERS THEN
lv_statement_cycle_id := NULL;
END;
IF lv_statement_cycle_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Statement Cycle against AR_STATEMENT_CYCLES';
END IF;
END IF;
----------------Validate Header Dunning_Letter_Set_Id against AR_DUNNING_LETTER_SETS-----------------
IF rec_header.dunning_letter_set_id IS NOT NULL THEN
BEGIN
SELECT dunning_letter_set_id
INTO lv_dunning_letter_set_id
FROM ar_dunning_letter_sets
WHERE NAME = rec_header.dunning_letter_set_name;
EXCEPTION WHEN OTHERS THEN
lv_dunning_letter_set_id := NULL;
END;
IF lv_dunning_letter_set_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Dunning_Letter_Set_Id against AR_DUNNING_LETTER_SETS';
END IF;
END IF;
----------------Validate the standard terms against RA_TERMS-----------------------------
IF rec_header.standard_terms IS NOT NULL THEN
BEGIN
SELECT COUNT (1)
INTO lv_terms_count
FROM ra_terms
WHERE NAME = rec_header.standard_terms_name;
END;
IF lv_terms_count > 0 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Standard Terms against RA_TERMS';
END IF;
END IF;
---------------- Validate Currency Code against FND_CURRENCIES----------------------------
IF rec_header.currency_code IS NOT NULL THEN
BEGIN
SELECT COUNT (1)
INTO lv_currency_count
FROM fnd_currencies
WHERE currency_code = rec_header.currency_code;
END;
IF lv_currency_count >0 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Currency Code against FND_CURRENCIES';
END IF;
END IF;
---------------- Validate the address1, state, city and postal_code should not be null---------
IF rec_header.address1 IS NOT NULL
AND rec_header.state IS NOT NULL
AND rec_header.city IS NOT NULL
AND rec_header.postal_code IS NOT NULL
THEN
BEGIN
SELECT hl.location_id,hps.party_site_id
INTO lv_location_id,lv_header_site_id
FROM hz_locations hl,
hz_party_sites hps
WHERE hl.location_id = hps.location_id
AND address1 = rec_header.address1
AND NVL(address2,'-XX') = nvl(rec_header.address2,'-XX')
AND NVL(address3,'-XX') = nvl(rec_header.address3,'-XX')
AND NVL(address4,'-XX') = nvl(rec_header.address4,'-XX')
AND NVL(county,'-XX') = nvl(rec_header.county,'-XX')
AND state = rec_header.state
AND city = rec_header.city
AND postal_code = rec_header.postal_code
AND country = rec_header.country
AND hps.party_id = NVL(lv_party_id,rec_header.v_party_id);
display_message('log','location ID:'||lv_location_id||'-'||lv_header_site_id);
EXCEPTION WHEN OTHERS THEN
display_message('log',' Header Location is not found');
lv_location_id := NULL;
lv_header_site_id := NULL;
END;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Header Address1, State, City, postal_code should not be NULL';
END IF;
-----------Update Header Staging Table--------
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET v_party_id = lv_party_id,
v_cust_account_id = lv_cust_account_id,
customer_profile_class_id = lv_profile_class_id,
collector_id = lv_collector_id,
statement_cycle_id = lv_statement_cycle_id,
dunning_letter_set_id = lv_dunning_letter_set_id,
n_location_id = lv_location_id,
n_header_site_id = lv_header_site_id,
valid_status = DECODE(lv_header_validate_flag,'Y','VALIDATED','VALIDATEFAILED'),
api_msg = lv_header_validate_msg
WHERE customer_number = rec_header.customer_number;
-- -----------------------------------------------
-- 2.Cursor Starts - Customer Header Communication
-- ------------------------------------------------
FOR rec_header_phone IN cur_header_phone (rec_header.customer_number)
LOOP
lv_header_communicate_msg := NULL;
lv_contact_point_id := NULL;
lv_header_communicate_flag:= 'Y';
-------------Validate Phone Type-----------------------
IF rec_header_phone.contact_point_type = 'PHONE' THEN
IF rec_header_phone.phone_number IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='PHONE'
AND owner_table_id = lv_party_id --Heaer Party_id
AND phone_number = rec_header_phone.phone_number;
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id := NULL;
END;
ELSE
lv_header_communicate_flag := 'N';
lv_header_communicate_msg := 'Phone Number should not be NULL';
END IF;
END IF; --validation phone number
-------------Validate Email Type-----------------------
IF rec_header_phone.contact_point_type = 'EMAIL' THEN
IF rec_header_phone.email_address IS NOT NULL
AND rec_header_phone.email_format IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='EMAIL'
AND owner_table_id = lv_party_id --Heaer Party_id
AND email_address = rec_header_phone.email_address;
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id := NULL;
END;
ELSE
lv_header_communicate_flag := 'N';
lv_header_communicate_msg := 'Email Address and EmailFormat should not be NULL';
END IF;
END IF; --validation Email Type
--Updae staging table
display_message('log','Contact Id :'||lv_contact_point_id);
UPDATE XXMTZ_AR_CSTMR_PHONE_STG_T
SET valid_status = DECODE(lv_header_communicate_flag,'Y','VALIDATED','VALIDATEFAILED'),
api_msg = lv_header_communicate_msg,
v_contact_point_id =lv_contact_point_id
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_header_phone.contact_point_type;
-- --------------------------------------
END LOOP; --2. End Cursor - Customer Header Communication
-- --------------------------------------------------------------
--3. Cursor Starts - Customer Header Contacts
-- --------------------------------------------------------------
FOR rec_header_contact IN cur_header_contact (rec_header.customer_number) LOOP --Start CustomerHeaderContacts
lv_contact_party_id := NULL;
lv_org_contact_id := NULL;
lv_acct_role_id := NULL;
IF rec_header_contact.last_name IS NOT NULL THEN
BEGIN
SELECT hp_per.party_id contact_party_id
,hz_rel.party_id org_contact_Rel_id
,hcar.cust_account_role_id
INTO lv_contact_party_id,lv_org_contact_id,lv_acct_role_id
FROM hz_parties hp_per,
hz_relationships hz_rel,
hz_parties hp,
hz_cust_account_roles hcar
WHERE hp_per.party_type = 'PERSON'
AND hp_per.party_id = hz_rel.subject_id
AND hp.party_id = hz_rel.object_id
AND hcar.party_id = hz_rel.party_id
AND hp_per.person_last_name = rec_header_contact.last_name --Header contact last name
AND hp.party_id = lv_party_id ; --Header Party ID
EXCEPTION WHEN OTHERS THEN
lv_contact_party_id := NULL;
lv_org_contact_id := NULL;
lv_acct_role_id := NULL;
END;
IF lv_contact_party_id IS NOT NULL THEN
UPDATE xxmtz_ar_cstmr_contact_stg_t
SET v_contact_party_id = lv_contact_party_id,
v_org_contact_id = lv_org_contact_id,
v_rel_party_id = lv_acct_role_id,
api_msg = 'Contact Person is already created'
WHERE customer_number = rec_header.customer_number
AND last_name = rec_header_contact.last_name;
END IF;
END IF;
-----------------------------------------------------------------
-- 4.Cursor Starts - Customer Header Contacts Communication
-- --------------------------------------------------------------
FOR rec_header_con_phone IN cur_header_con_phone (rec_header.customer_number,
rec_header_contact.contact_id) --Start HeaderContactsCommunication
LOOP
lv_contact_point_id :=NULL;
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = lv_org_contact_id;-- 404872
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_phone_stg_t
SET v_phone_id =lv_contact_point_id
WHERE contact_id = rec_header_contact.contact_id
AND customer_number = rec_header.customer_number;
-- --------------------------------------
END LOOP; --4. End Cursor - Customer Header Contacts Communication
-- --------------------------------------------------------------
-- 5.Cursor Starts - Customer Header Contacts Roles
-- --------------------------------------------------------------
FOR rec_header_con_role IN cur_header_con_role (rec_header.customer_number,
rec_header_contact.contact_id)
LOOP
lv_contact_role_id :=NULL;
BEGIN
SELECT responsibility_id
INTO lv_contact_role_id
FROM hz_role_responsibility
WHERE cust_account_role_id = lv_acct_role_id;
EXCEPTION WHEN OTHERS THEN
lv_contact_role_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_role_stg_t
SET v_contact_role_id = lv_contact_role_id
WHERE contact_id = rec_header_contact.contact_id
AND customer_number = rec_header.customer_number;
-- --------------------------------------
END LOOP; --5. End Cursor - Customer Header Contacts Communication
-- --------------------------------------
END LOOP; --3. End Cursor - Customer Header Contacts cursor
-- --------------------------------------------------------------
-- 6. Cursor Starts - Customer Location
-- --------------------------------------------------------------
FOR rec_sites_location IN cur_sites_location (rec_header.customer_number)
LOOP
lv_site_validate_flag := 'Y';
lv_site_validate_msg := NULL;
lv_site_location_id := NULL;
lv_loc_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
---------------------Validate Customer Site Location-----------------------
IF rec_sites_location.address1 IS NOT NULL
AND rec_sites_location.state IS NOT NULL
AND rec_sites_location.city IS NOT NULL
AND rec_sites_location.postal_code IS NOT NULL THEN
BEGIN
SELECT hl.location_id,hps.party_site_id,hcas.cust_acct_site_id
INTO lv_site_location_id,lv_loc_party_site_id,lv_cust_acct_site_id
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas
WHERE hl.location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_account_id = lv_cust_account_id
AND address1 = rec_sites_location.address1
AND NVL(address2,'-XX') = nvl(rec_sites_location.address2,'-XX')
AND NVL(address3,'-XX') = nvl(rec_sites_location.address3,'-XX')
AND NVL(address4,'-XX') = nvl(rec_sites_location.address4,'-XX')
AND NVL(county,'-XX') = nvl(rec_sites_location.county,'-XX')
AND state = rec_sites_location.state
AND city = rec_sites_location.city
AND postal_code = rec_sites_location.postal_code
AND country = rec_sites_location.country
AND hps.party_id = NVL(lv_party_id,rec_header.v_party_id);
display_message('log','location ID:'||lv_location_id||'-'||lv_header_site_id);
EXCEPTION WHEN OTHERS THEN
display_message('log',' Site Location is not found');
lv_site_location_id := NULL;
lv_loc_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
END;
ELSE
lv_site_validate_flag := 'Y';
lv_site_validate_msg := lv_header_validate_msg||'Site Address1, State, City, postal_code should not be NULL';
END IF;
-----------------Validate territory ---------------------
IF rec_sites_location.territory IS NOT NULL THEN
BEGIN
SELECT COUNT (*)
INTO lv_territory_count
FROM fnd_territories
WHERE territory_code = rec_sites_location.territory;
END;
IF lv_territory_count > 0 THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg :='Currency Code against FND_TERROTORIES';
END IF;
END IF;
--------Update Staging Table----------
UPDATE xxmtz_ar_cstmr_location_stg_t
SET v_party_location_id = lv_site_location_id,
v_party_site_id = lv_loc_party_site_id,
v_cust_site_id = lv_cust_acct_site_id,
api_msg = lv_site_validate_msg
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id;
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
lv_profile_class_id := NULL;
lv_collector_id := NULL;
IF rec_sites_use.collector_name IS NOT NULL THEN
BEGIN
SELECT collector_id --COUNT (*)
INTO lv_collector_id
FROM ar_collectors
WHERE NAME = rec_sites_use.collector_name;
EXCEPTION WHEN OTHERS THEN
lv_collector_id := NULL;
END;
IF lv_collector_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg :=lv_site_validate_msg|| 'Collector Name against AR_COLLECTORS';
END IF;
END IF;
-------------- -- Validate Profile Class ID against HZ_CUST_PROFILE_CLASSES------------
IF rec_sites_use.profile_class_name IS NOT NULL THEN
BEGIN
SELECT profile_class_id
INTO lv_profile_class_id
FROM hz_cust_profile_classes
WHERE NAME = rec_sites_use.profile_class_name;
EXCEPTION WHEN OTHERS THEN
lv_profile_class_id := NULL;
END;
IF lv_profile_class_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := 'Profile Class against HZ_CUST_PROFILE_CLASSES';
END IF;
END IF;
-----------------Validate Statement Cycle ID against AR_STATEMENT_CYCLES---------------------------
IF rec_sites_use.statement_cycle_name IS NOT NULL THEN
lv_statement_cycle_id := NULL;
BEGIN
SELECT statement_cycle_id
INTO lv_statement_cycle_id
FROM ar_statement_cycles
WHERE NAME = rec_sites_use.statement_cycle_name;
EXCEPTION WHEN OTHERS THEN
lv_statement_cycle_id := NULL;
END;
IF lv_statement_cycle_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := lv_site_validate_msg||'Statement Cycle Id against AR_STATEMENT_CYCLES';
END IF;
END IF;
---------------------------Validate the Site standard terms against RA_TERMS-----------------
IF rec_sites_use.standard_terms IS NOT NULL THEN
BEGIN
SELECT COUNT (*)
INTO lv_terms_count
FROM ra_terms
WHERE NAME = rec_sites_use.standard_terms_name;
END;
IF lv_terms_count > 0 THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := 'Site Standard Terms against ra_terms';
END IF;
END IF;
-------------Update Site Use Stagin table------------
BEGIN
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET collector_id = lv_collector_id,
customer_profile_class_id = lv_profile_class_id,
statement_cycle_id = lv_statement_cycle_id,
api_msg = lv_site_validate_msg
WHERE customer_number = rec_sites_use.customer_number
AND site_use_code = rec_sites_use.site_use_code
AND address_id = rec_sites_use.address_id;
END;
-- --------------------------------------------
END LOOP; --7.END Customer Sites Business Purpose cursor
-- --------------------------------------------
-- 8. Cursor Starts - Customer Sites Communication
-- --------------------------------------------------------------
FOR rec_sites_phone IN cur_sites_phone (rec_header.customer_number)
LOOP
lv_site_contact_point_id := NULL;
lv_site_comm_validate_flag := NULL;
lv_site_comm_validate_msg := NULL;
--- Validate the phone_number should not be null---------------------
-------------Validate Phone Type-----------------------
IF rec_sites_phone.contact_point_type = 'PHONE' THEN
IF rec_sites_phone.phone_number IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_site_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='PHONE'
AND owner_table_id = lv_loc_party_site_id --Party_site_id
AND phone_number = rec_sites_phone.phone_number;
EXCEPTION WHEN OTHERS THEN
lv_site_contact_point_id := NULL;
END;
ELSE
lv_site_comm_validate_flag := 'N';
lv_site_comm_validate_msg := 'Phone Number should not be NULL';
END IF;
END IF; --validation phone number
-------------Validate Email Type-----------------------
IF rec_sites_phone.contact_point_type = 'EMAIL' THEN
IF rec_sites_phone.email_address IS NOT NULL
AND rec_sites_phone.email_format IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_site_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='EMAIL'
AND owner_table_id = lv_loc_party_site_id --Party_site_id
AND email_address = rec_sites_phone.email_address;
EXCEPTION WHEN OTHERS THEN
lv_site_contact_point_id := NULL;
END;
ELSE
lv_site_comm_validate_flag := 'N';
lv_site_comm_validate_msg := 'Email Address and EmailFormat should not be NULL';
END IF;
END IF; --validation Email Type
--Update Site communication staging table--------------
UPDATE xxmtz_ar_cstmr_site_ph_stg_t
SET v_cust_site_phone_id = lv_site_contact_point_id,
api_msg = lv_site_comm_validate_msg
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_sites_phone.contact_point_type;
-- --------------------------------------------------------------
END LOOP; --8. END Customer Sites Communication cursor
-- --------------------------------------------------------------
-- 9. Cursor Starts - Customer Sites Contacts
-- --------------------------------------------------------------
FOR rec_sites_con IN cur_sites_con (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
lv_site_cont_party_id := NULL;
lv_site_org_cont_id := NULL;
lv_site_acct_role_id := NULL;
IF rec_sites_con.last_name IS NOT NULL THEN
BEGIN
SELECT hp_per.party_id contact_party_id
,hz_rel.party_id org_contact_Rel_id
,hcar.cust_account_role_id
INTO lv_site_cont_party_id,lv_site_org_cont_id,lv_site_acct_role_id
FROM hz_parties hp_per,
hz_relationships hz_rel,
hz_parties hp,
hz_cust_account_roles hcar
WHERE hp_per.party_type = 'PERSON'
AND hp_per.party_id = hz_rel.subject_id
AND hp.party_id = hz_rel.object_id
AND hcar.party_id = hz_rel.party_id
AND hp_per.person_last_name = rec_sites_con.last_name
AND hcar.cust_account_id = lv_cust_account_id -- customer account id
AND hcar.cust_acct_site_id = lv_cust_acct_site_id ; -- customer site account id
EXCEPTION WHEN OTHERS THEN
lv_site_cont_party_id := NULL;
lv_site_org_cont_id := NULL;
lv_site_acct_role_id := NULL;
END;
IF lv_contact_party_id IS NOT NULL THEN
UPDATE xxmtz_ar_cstmr_site_con_stg_t
SET v_cust_site_contact_id = lv_site_cont_party_id,
v_org_contact_id = lv_site_org_cont_id,
v_rel_party_id = lv_site_acct_role_id,
api_msg = 'Site Contact Person is already created'
WHERE customer_number = rec_header.customer_number
AND last_name = rec_sites_con.last_name;
END IF;
END IF;
-----------------------------------------------------------------
-- 10.Cursor Starts - Customer Sites Contact Communications
-- --------------------------------------------------------------
FOR rec_sites_con_phone IN cur_sites_con_phone (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_contact_point_id :=NULL;
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = lv_site_org_cont_id; --Site org contact id
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_si_con_ph_stg_t
SET v_cust_site_contact_phone_id = lv_site_cont_party_id
WHERE contact_id = rec_sites_con_phone.contact_id
AND customer_number = rec_header.customer_number
AND phone_number = rec_sites_con_phone.phone_number;
-- --------------------------------------
END LOOP; --10. End Cursor - Customer Sites Contact Communications
-- --------------------------------------------------------------
-- 11.Cursor Starts - Cursor Starts - Customer Sites Contact Roles
-- --------------------------------------------------------------
FOR rec_sites_con_role IN cur_sites_con_role (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_contact_role_id :=NULL;
BEGIN
SELECT responsibility_id
INTO lv_contact_role_id
FROM hz_role_responsibility
WHERE cust_account_role_id = lv_site_acct_role_id; --Site Account Role id
EXCEPTION WHEN OTHERS THEN
lv_contact_role_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_si_role_stg_t
SET v_cust_site_contact_role_id = lv_contact_role_id
WHERE contact_id = rec_sites_con_role.contact_id
AND customer_number = rec_header.customer_number;
-- ---------------------------------------------------------------------
END LOOP; --11. End Cursor - Customer Header Contacts Communication
-- ---------------------------------------------------------------------
END LOOP; --9.END - Customer Sites Contacts Cursor
-- ----------------------------------------------------
END LOOP; --6.END Customer Location cursor
-- --------------------------------------------
END LOOP; --1.END Header Cursor
-- --------------------------------------
COMMIT;
display_message('log','*******END Validate Procedure**************');
EXCEPTION WHEN ex_lookup_setup_err THEN
x_retcode := 2;
display_message('log', ' AR LookUP Setup missing');
WHEN ex_header_total THEN
x_retcode := 1;
display_message('log','No Records in the Staging Table to process'|| SQLERRM);
END xxmtz_ar_cstmr_cnv_val_prc;
--Upload Procedure
PROCEDURE xxmtz_ar_cstmr_cnv_load_prc (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2 )
IS
-- --------------------------------------------------------------
-- Cursor Declaration
-- --------------------------------------------------------------
--Header Account
CURSOR cur_header IS
SELECT *
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('VALIDATED', 'PROCESSFAILED')
AND customer_number = NVL(p_cust_number,customer_number) --'TESTASP'
ORDER BY party_id, customer_number;
--Header Phone
CURSOR cur_header_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact
CURSOR cur_header_contact (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact Phone
CURSOR cur_header_con_phone ( p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Roles
CURSOR cur_header_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Sites Location
CURSOR cur_sites_location (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_location_stg_t
WHERE customer_number = p_customer_number;
-- Sites Use
CURSOR cur_sites_use (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER,
p_site_use IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number
AND site_use_code = p_site_use
ORDER BY site_use_code;
-- Sites Phone
CURSOR cur_sites_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE customer_number = p_customer_number;
-- Sites Contact
CURSOR cur_sites_con (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number;
-- Sites Contact Phone
CURSOR cur_sites_con_phone (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Role
CURSOR cur_sites_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- -----------------------------
--API Record Types
-- ----------------------------
p_organization_rec apps.hz_party_v2pub.organization_rec_type;
p_cust_account_rec apps.hz_cust_account_v2pub.cust_account_rec_type;
p_customer_profile_rec apps.hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_profile_amt_rec_type apps.hz_customer_profile_v2pub.cust_profile_amt_rec_type;
p_location_rec apps.hz_location_v2pub.location_rec_type;
p_party_site_rec apps.hz_party_site_v2pub.party_site_rec_type;
p_contact_point_rec apps.hz_contact_point_v2pub.contact_point_rec_type;
p_edi_rec apps.hz_contact_point_v2pub.edi_rec_type;
p_emailv_rec apps.hz_contact_point_v2pub.email_rec_type;
p_phone_rec apps.hz_contact_point_v2pub.phone_rec_type;
p_telex_rec apps.hz_contact_point_v2pub.telex_rec_type;
p_web_rec apps.hz_contact_point_v2pub.web_rec_type;
p_person_rec apps.hz_party_v2pub.person_rec_type;
p_org_contact_rec apps.hz_party_contact_v2pub.org_contact_rec_type;
p_org_contact_role_rec apps.hz_party_contact_v2pub.org_contact_role_rec_type;
p_h_cr_cust_acc_role_rec apps.hz_cust_account_role_v2pub.cust_account_role_rec_type;
p_role_responsibility_rec apps.hz_cust_account_role_v2pub.role_responsibility_rec_type;
p_cust_acct_site_rec apps.hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_cust_site_use_cm_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_bill_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_ship_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cr_cust_acc_role_rec apps.hz_cust_account_role_v2pub.cust_account_role_rec_type;
--------------------------------------------------
x_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
x_account_number hz_cust_accounts.account_number%TYPE;
x_party_id hz_parties.party_id%TYPE;
x_party_number hz_parties.party_number%TYPE;
x_profile_id hz_customer_profiles.cust_account_profile_id%TYPE;
x_cust_acct_profile_amt_id hz_cust_profile_amts.cust_acct_profile_amt_id%TYPE;
x_location_id hz_locations.location_id%TYPE;
x_party_site_id hz_party_sites.party_site_id%TYPE;
x_party_site_number hz_party_sites.party_site_number%TYPE;
x_contact_point_id hz_contact_points.contact_point_id%TYPE;
x_org_contact_id hz_org_contacts.org_contact_id%TYPE;
x_party_relv_id hz_parties.party_id%TYPE;
x_cust_account_role_id hz_cust_accounts.cust_account_id%TYPE;
x_responsibility_id hz_parties.party_id%TYPE;
x_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
x_site_use_id hz_cust_site_uses_all.site_use_id%TYPE;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
-------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
--User Define Exceptions
NO_PARTY_ID EXCEPTION ;
-- -----------------------------------------------------------------------------
lv_org_id NUMBER :=p_org_id;
lv_party_id hz_parties.party_id%TYPE := NULL;
lv_party_number hz_parties.party_number%TYPE := NULL;
lv_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
lv_profile_id hz_organization_profiles.organization_profile_id%TYPE := NULL;
lv_cust_acct_profile_amt_id hz_cust_profile_amts.cust_acct_profile_amt_id%TYPE := NULL;
lv_cust_account_profile_id hz_customer_profiles.cust_account_profile_id%TYPE := NULL;
lv_header_location_id hz_locations.location_id%TYPE := NULL;
lv_header_phone_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_org_contact_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_header_con_party_id hz_parties.party_id%TYPE := NULL;
lv_header_acc_role_party_id hz_parties.party_id%TYPE := NULL;
lv_header_account_role_id hz_cust_account_roles.cust_account_role_id%TYPE:= NULL;
lv_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE := NULL;
lv_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE := NULL;
lv_site_phone_id hz_contact_points.contact_point_id%TYPE:= NULL;
lv_site_emailv_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_site_contact_phone_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_site_contact_role_id hz_org_contact_roles.org_contact_role_id%TYPE:= NULL;
lv_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE:= NULL;
lv_site_location_id hz_locations.location_id%TYPE := NULL;
lv_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_bill_to_site_use_id hz_cust_site_uses_all.site_use_id%TYPE:= NULL;
lv_ship_to_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
lv_cm_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
lv_sites_con_party_id hz_parties.party_id%TYPE := NULL;
lv_site_acc_role_party_id hz_parties.party_id%TYPE := NULL;
lv_site_account_role_id hz_cust_account_roles.cust_account_role_id%TYPE:= NULL;
lv_api_org_msg VARCHAR2 (4000) :=NULL;
lv_api_header_phone_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_phone_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_role_msg VARCHAR2 (4000):=NULL;
lv_api_location_msg VARCHAR2 (4000):=NULL;
lv_api_site_use_msg VARCHAR2 (4000):=NULL;
lv_api_site_phone_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_phone_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_role_msg VARCHAR2 (4000):=NULL;
lv_message_out VARCHAR2 (4000);
lv_site_location_flag VARCHAR2 (1);
lv_sites_con_party_flag VARCHAR2 (1);
lv_site_use_count NUMBER;
lv_cust_process_flag VARCHAR2 (1);
lv_site_acc_role_party_flag VARCHAR2 (1);
BEGIN
FND_GLOBAL.APPS_INITIALIZE(user_id => fnd_global.user_id,resp_id =>fnd_global.resp_id,resp_appl_id =>222);
MO_GLOBAL.INIT ('AR');
MO_GLOBAL.SET_POLICY_CONTEXT ('S', lv_org_id);
display_message('log','begin');
FOR rec_header IN cur_header --Start HeaderLoop
LOOP
BEGIN
lv_api_org_msg := NULL;
lv_party_id := NULL;
lv_header_location_id := NULL;
lv_cust_process_flag := 'Y';
display_message('log',' Processing Customer Number :'||rec_header.customer_number);
IF rec_header.v_cust_account_id IS NULL THEN --IF CheckCustAcct
p_cust_account_rec.status := rec_header.status_party;
p_cust_account_rec.account_name := rec_header.customer_name;
p_cust_account_rec.account_number := rec_header.customer_number; --CustomerNumber
p_cust_account_rec.created_by_module := gc_created_by_module;
p_cust_account_rec.customer_type := rec_header.customer_type;
p_cust_account_rec.attribute10 := rec_header.attribute10;
p_cust_account_rec.attribute11 := rec_header.attribute11;
p_cust_account_rec.attribute12 := rec_header.attribute12;
-- p_cust_account_rec.customer_class_code := rec_header.customer_class_code;
p_cust_account_rec.tax_header_level_flag := rec_header.tax_header_level_flag;
p_cust_account_rec.arrivalsets_include_lines_flag := rec_header.arrivalsets_include_lines_flag;
p_cust_account_rec.ship_sets_include_lines_flag := rec_header.ship_sets_include_lines_flag;
p_cust_account_rec.sched_date_push_flag := rec_header.sched_date_push_flag;
p_organization_rec.organization_name := rec_header.customer_name;
p_organization_rec.duns_number_c := rec_header.duns_number_c;
p_cust_account_rec.orig_system_reference := rec_header.customer_number; --Orig system Reference
p_cust_account_rec.orig_system := gc_orig_system; --Orig System
--Checking Sic Code
IF rec_header.sic_code IS NOT NULL THEN --SIC Code
p_organization_rec.sic_code := rec_header.sic_code;
IF rec_header.sic_code_type IS NULL THEN --Sic Code Type
p_organization_rec.sic_code_type := 'OTHER';
ELSE
p_organization_rec.sic_code_type := rec_header.sic_code_type;
END IF;
END IF;
p_organization_rec.created_by_module := gc_created_by_module;
IF rec_header.gsa_indicator IS NOT NULL THEN --GSA Indicator
p_organization_rec.gsa_indicator_flag := rec_header.gsa_indicator;
END IF;
p_customer_profile_rec.collector_id := rec_header.collector_id;
p_customer_profile_rec.status := rec_header.status_profile;
p_customer_profile_rec.profile_class_id := rec_header.customer_profile_class_id;
p_customer_profile_rec.credit_checking := rec_header.credit_checking;
p_customer_profile_rec.tolerance := rec_header.tolerance;
--validation between -100 and 100
p_customer_profile_rec.discount_terms := rec_header.discount_terms;
p_customer_profile_rec.dunning_letters := rec_header.dunning_letters;
p_customer_profile_rec.interest_charges := rec_header.interest_charges;
--p_customer_profile_rec.send_statements := rec_header.statements;
p_customer_profile_rec.credit_balance_statements := rec_header.credit_balance_statements;
p_customer_profile_rec.credit_hold := rec_header.credit_hold;
p_customer_profile_rec.standard_terms := rec_header.standard_terms;
--validation against ar_terms
p_customer_profile_rec.override_terms := rec_header.override_terms;
IF rec_header.interest_period_days IS NOT NULL --Interes Period
THEN
p_customer_profile_rec.interest_charges := 'Y';
p_customer_profile_rec.charge_on_finance_charge_flag := 'Y';
p_customer_profile_rec.interest_period_days :=
rec_header.interest_period_days;
END IF;
p_customer_profile_rec.auto_rec_incl_disputed_flag := rec_header.auto_rec_incl_disputed_flag;
p_customer_profile_rec.tax_printing_option := rec_header.tax_printing_option;
-- validation tax_printing_option lookup
p_customer_profile_rec.cons_inv_flag := rec_header.cons_inv_flag;
display_message('log',' Calling HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT API...');
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_organization_rec,
p_customer_profile_rec,
'F',
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log',' Customer Account Status :'||x_return_status||' Account ID :'||NVL(x_cust_account_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' 1.Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' 2.Error Message :'||lv_message_out);
END IF;
ELSE
lv_party_id := x_party_id;
lv_party_number := x_party_number;
lv_cust_account_id := x_cust_account_id;
lv_profile_id := x_profile_id;
lv_api_org_msg := ('PartyId: '||x_party_id||' AccountID: '||x_cust_account_id||' Account# :'||x_account_number||' ProfileID :'||x_profile_id);
display_message('log',lv_api_org_msg);
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_party_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
v_party_id = lv_party_id ,
v_cust_account_id = lv_cust_account_id,
v_profile_id = lv_profile_id
WHERE customer_number = rec_header.customer_number;
END;
ELSE
display_message('log',' Customer Account Is already Exists PartyID:'||rec_header.v_party_id||' AccountID :'||rec_header.v_cust_account_id);
lv_party_id := rec_header.v_party_id;
--lv_party_number := rec_header.v_cust_account_id;
lv_cust_account_id := rec_header.v_cust_account_id;
lv_profile_id := rec_header.v_profile_id;
lv_api_org_msg := ('PartyId: '||rec_header.v_party_id||' AccountID: '||rec_header.v_cust_account_id||' ProfileID :'||rec_header.v_profile_id);
END IF; --End CheckCustAcct
--If Party is not exists then come out of loop
IF lv_party_id IS NULL THEN
RAISE NO_PARTY_ID;
END IF;
------**END Create Cust Account**-------------------
/* BEGIN
SELECT cust_account_profile_id
INTO lv_cust_account_profile_id --Profile Amount id
FROM hz_customer_profiles
WHERE cust_account_id = lv_cust_account_id;
EXCEPTION WHEN OTHERS THEN
lv_cust_account_profile_id := NULL;
END;
IF rec_header.currency_code IS NOT NULL --IF CheckProfileAmt.
AND rec_header.v_cust_acct_profile_amt_id IS NULL
--AND rec_header.trx_credit_limit IS NOT NULL --Tax Credit Limit
--AND rec_header.overall_credit_limit IS NOT NULL --Over all Credit Limit
AND lv_cust_account_profile_id IS NOT NULL THEN
p_cust_profile_amt_rec_type.cust_account_id := lv_cust_account_id;
p_cust_profile_amt_rec_type.cust_account_profile_id := lv_cust_account_profile_id;
p_cust_profile_amt_rec_type.currency_code := rec_header.currency_code;
--Validation against fnd_currencies
p_cust_profile_amt_rec_type.trx_credit_limit := rec_header.trx_credit_limit;
p_cust_profile_amt_rec_type.overall_credit_limit := rec_header.overall_credit_limit;
p_cust_profile_amt_rec_type.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUST_PROFILE_AMT...');
hz_customer_profile_v2pub.create_cust_profile_amt
('T',
'T',
p_cust_profile_amt_rec_type,
x_cust_acct_profile_amt_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Profile amt Status :'||x_return_status||' Profile Amt ID :'||NVL(x_cust_acct_profile_amt_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_org_msg := lv_api_org_msg||' : '||('CustProfileAmtId :'||x_cust_acct_profile_amt_id);
display_message('log','CustProfileAMT is Created :'||x_cust_acct_profile_amt_id);
lv_cust_acct_profile_amt_id := x_cust_acct_profile_amt_id;
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
---Update Profile Amt
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_party_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
v_cust_acct_profile_amt_id = lv_cust_acct_profile_amt_id
WHERE customer_number = rec_header.customer_number;
END;
END IF; --End CheckProfileAmt
-----------**END Create ProfileAMT**--------------------
*/
BEGIN --Begin HeaderLocation
IF rec_header.n_location_id IS NULL THEN
p_location_rec.country := rec_header.country; --'US';
p_location_rec.address1 := rec_header.address1;
p_location_rec.address2 := rec_header.address2;
p_location_rec.address3 := rec_header.address3;
p_location_rec.city := rec_header.city;
p_location_rec.postal_code := rec_header.postal_code;
p_location_rec.state := rec_header.state;
p_location_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_LOCATION_V2PUB.CREATE_LOCATION...');
hz_location_v2pub.create_location ('T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Header Location Status :'||x_return_status||' Locaion Id:'||NVL(x_location_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_header_location_id:= x_location_id;
lv_api_org_msg := lv_api_org_msg||' : '||('CustomerHeaderLocationId :'||x_location_id);
display_message('log',lv_api_org_msg);
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_header_location_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
n_location_id = lv_header_location_id
WHERE customer_number = rec_header.customer_number;
END;
ELSE
display_message('log','Location Is already created location ID:'||rec_header.n_location_id);
lv_header_location_id := rec_header.n_location_id;
END IF;
END;
-------**End HeaderLocation**------------
BEGIN --Begin HeaderPartySite
IF rec_header.n_header_site_id IS NULL THEN
p_party_site_rec.party_id := lv_party_id;
p_party_site_rec.location_id := lv_header_location_id;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE...');
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Header Party Status :'||x_return_status||' Party Site Id:'||NVL(x_party_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_org_msg := lv_api_org_msg||' : '||('CustomerHeaderPartySite :'||x_party_site_id);
display_message('log','CustHeaderLocation is Created');
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(x_party_site_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
n_header_site_id = x_party_site_id
WHERE customer_number = rec_header.customer_number;
END;
END IF;
END;
-------**End HeaderPartySite**-------------
-- --------------------------------------------------------------
-- 2.Cursor Starts - Customer Header Communication
-- --------------------------------------------------------------
FOR rec_header_phone IN cur_header_phone (rec_header.customer_number)LOOP --Start HeaderCommunication
display_message('log','In Customer Communication Creation of type :'||rec_header_phone.contact_point_type);
lv_api_header_phone_msg := NULL;
lv_header_phone_id := NULL;
--lv_header_phone_flag := 'N';
IF rec_header_phone.v_contact_point_id IS NULL
AND lv_party_id IS NOT NULL THEN --Check ContactPoint
p_contact_point_rec.contact_point_type := rec_header_phone.contact_point_type; --Contact Type PHONE or EMAIL
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := lv_party_id; --Party Id
p_contact_point_rec.primary_flag := rec_header_phone.primary_flag;
p_contact_point_rec.created_by_module := gc_created_by_module;
IF rec_header_phone.contact_point_type = 'PHONE' THEN --Phone
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
-- p_contact_point_rec.status := rec_header_phone.status;
p_phone_rec.phone_area_code := rec_header_phone.area_code;
p_phone_rec.phone_country_code := rec_header_phone.country_code;
p_phone_rec.phone_number := rec_header_phone.phone_number;
p_phone_rec.phone_line_type := rec_header_phone.phone_type;
END IF; --End Phone Contact
IF rec_header_phone.contact_point_type = 'EMAIL'
AND rec_header_phone.email_address IS NOT NULL THEN --Email
-- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.status := rec_header_phone.status;
p_emailv_rec.email_format := rec_header_phone.email_format;
p_emailv_rec.email_address := rec_header_phone.email_address;
--p_emailv_rec.url := rec_header_phone.url;
p_phone_rec.phone_line_type := rec_header_phone.phone_type;
END IF;
display_message('log','Calling..HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header Contact Status :'||x_return_status||' Contact PointID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_phone_msg := lv_api_org_msg||' : '||('HeaderContactId :'||x_contact_point_id);
display_message('log','Header Contact is Created ID :'||x_contact_point_id);
lv_header_phone_id := x_contact_point_id;
-- lv_header_phone_flag := 'Y';
END IF;
lv_api_header_phone_msg := lv_api_header_phone_msg||' : '||lv_message_out;
--Update Staging Table----------------------------
UPDATE xxmtz_ar_cstmr_phone_stg_t
SET v_contact_point_id = lv_header_phone_id,
-- v_contact_point_flag = lv_header_phone_flag,
api_msg = lv_api_header_phone_msg,
valid_status = DECODE(NVL(lv_header_phone_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_header_phone.contact_point_type
AND customer_number = rec_header.customer_number;
-- -------------------------------------------------------------
ELSE --Header Contact already exists
display_message('log','Header Contact Point is already Created. ContactID :'||rec_header_phone.v_contact_point_id);
END IF; ----End Check ContactPoint
END LOOP; --2.End HeaderCommunication
-- -----------------------------------------------------
-- 3.Cursor Starts - Customer Header Contacts
-- --------------------------------------------------------------
FOR rec_header_contact IN cur_header_contact (rec_header.customer_number) LOOP --Start CustomerHeaderContacts
display_message('log',' In recd_header_contact');
lv_header_con_party_id := NULL;
lv_header_acc_role_party_id := NULL;
lv_header_account_role_id := NULL;
lv_api_header_con_msg := NULL;
lv_org_contact_id := NULL;
IF rec_header_contact.last_name IS NOT NULL --Check LastName
AND rec_header_contact.v_contact_party_id IS NULL THEN
p_person_rec.person_first_name := rec_header_contact.first_name;
p_person_rec.person_last_name := rec_header_contact.last_name;
p_person_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling HZ_PARTY_V2PUB.CREATE_PERSON...');
hz_party_v2pub.create_person ('T',
p_person_rec,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header Contact Person Status :'||x_return_status||' Contact PartyID :'||NVL(x_party_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('HeaderContactPartyId :'||x_party_id);
display_message('log','Header Contact Person is Created PartyID :'||x_party_id);
lv_header_con_party_id := x_party_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
-- -------------------------------------------------------------------
-- Create Header Contact Relationship
-- -------------------------------------------------------------------
IF rec_header_contact.v_org_contact_id IS NULL THEN
p_org_contact_rec.party_rel_rec.subject_id := lv_header_con_party_id;
p_org_contact_rec.party_rel_rec.object_id := lv_party_id;
p_org_contact_rec.contact_number := rec_header_contact.contact_number;
p_org_contact_rec.created_by_module := gc_created_by_module;
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.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := rec_header_contact.start_date;
--p_org_contact_rec.department_code := 'ACCOUNTING';
--p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
--p_org_contact_rec.decision_maker_flag := 'Y';
--p_org_contact_rec.job_title_code := ;
--p_org_contact_rec.status := rec_sites.status_contact;
--p_org_contact_rec.party_site_id := 26211;
display_message('log','Calling HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT...');
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
x_org_contact_id,
x_party_relv_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Org Contact status :'||x_return_status||' OrgContact ID :'||NVL(x_org_contact_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('OrgContactId :'||x_org_contact_id);
display_message('log','Org Contact is Created OrgcontID :'||x_org_contact_id||' Contact PartyID:'||x_party_id);
lv_header_acc_role_party_id := x_party_id;
lv_org_contact_id := x_org_contact_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
END IF;
-- ------------------------------------------------------------------
--End Org Contact
-- -------------------------------------------------------------------
-- Create Header Contact Account Role
-- -------------------------------------------------------------------
IF rec_header_contact.v_rel_party_id IS NULL THEN
p_h_cr_cust_acc_role_rec.party_id := lv_header_acc_role_party_id;
p_h_cr_cust_acc_role_rec.cust_account_id := lv_cust_account_id;
--p_h_cr_cust_acc_role_rec.primary_flag := 'Y';
p_h_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_h_cr_cust_acc_role_rec.status := rec_header_contact.status;
p_h_cr_cust_acc_role_rec.created_by_module:= gc_created_by_module;
display_message('log','Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE...');
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_h_cr_cust_acc_role_rec,
x_cust_account_role_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Acct Role status :'||x_return_status||' AcctRoleID :'||NVL(x_cust_account_role_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('OrgRoleId :'||x_cust_account_role_id);
display_message('log','Account RoleID :'||x_cust_account_role_id);
lv_header_account_role_id := x_cust_account_role_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
--Update Staging Table----------------------------
UPDATE xxmtz_ar_cstmr_contact_stg_t
SET v_contact_party_id = lv_header_con_party_id,
v_org_contact_id = lv_header_acc_role_party_id,
v_rel_party_id = lv_header_account_role_id,
api_msg = lv_api_header_con_msg ,
valid_status = DECODE(NVL(lv_header_con_party_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE last_name = rec_header_contact.last_name
AND customer_number = rec_header.customer_number;
END IF;
ELSE
display_message('log','Contact last Name is NULL or Party is already exists'||' : '||rec_header_contact.v_contact_party_id);
lv_header_acc_role_party_id := rec_header_contact.v_org_contact_id ;
lv_header_account_role_id := rec_header_contact.v_rel_party_id;
END IF;--Check LastName
-----------------------------------------------------------------
-- 4.Cursor Starts - Customer Header Contacts Communication
-- --------------------------------------------------------------
FOR rec_header_con_phone IN cur_header_con_phone (rec_header.customer_number,
rec_header_contact.contact_id) --Start HeaderContactsCommunication
LOOP
lv_api_header_con_phone_msg := NULL;
display_message('log','In cur_header_con_phone ');
IF rec_header_con_phone.v_phone_id IS NULL THEN --Check PhoneId
display_message('log','IN cur_header_con_phone Aacc_role_party_id :'||lv_header_acc_role_party_id);
p_contact_point_rec.owner_table_id := lv_header_acc_role_party_id;
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.primary_flag := rec_header_con_phone.primary_flag;
--p_contact_point_rec.contact_point_purpose := 'BUSINESS';
--p_contact_point_rec.status := rec_header_con_phone.status;
p_phone_rec.phone_area_code := rec_header_con_phone.area_code;
p_phone_rec.phone_country_code := rec_header_con_phone.country_code;
p_phone_rec.phone_number := rec_header_con_phone.phone_number;
p_phone_rec.phone_line_type := rec_header_con_phone.phone_type;
p_contact_point_rec.created_by_module := gc_created_by_module;
display_message('log', 'Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header ContactComm status :'||x_return_status||' ContactID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_phone_msg := lv_api_header_con_phone_msg||' : '||('ContactCommunicationID :'||x_contact_point_id);
display_message('log','ContactComminicationID :'||x_cust_account_role_id);
END IF;
lv_api_header_con_phone_msg := lv_api_header_con_phone_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_phone_stg_t
SET v_phone_id = x_contact_point_id,
api_msg = lv_api_header_con_phone_msg,
valid_status = DECODE(NVL(x_contact_point_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_header_contact.contact_id
AND last_name = rec_header_con_phone.last_name;
ELSE
display_message('log','No customer con phone stg record found');
END IF; --End Check PhoneId
-- -----------------------------------------------------------------------------
END LOOP; --End HeaderContactsCommunication
--End 4.Customer Header Contacts Communication
-- --------------------------------------------------------------
-- --------------------------------------------------------------
-- 5.Cursor Starts - Customer Header Contacts Roles
-- --------------------------------------------------------------
FOR rec_header_con_role IN cur_header_con_role (rec_header.customer_number,
rec_header_contact.contact_id) LOOP --Start CustomerHeaderContactsRoles
lv_api_header_con_role_msg := NULL;
display_message('log','In cur_header_con_role ');
IF rec_header_con_role.v_contact_role_id IS NULL THEN
p_role_responsibility_rec.responsibility_type := rec_header_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := lv_header_account_role_id;
p_role_responsibility_rec.primary_flag := rec_header_con_role.primary_flag;
p_role_responsibility_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_ROLE_RESPONSIBILITY...');
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header ContactRole status :'||x_return_status||' ResposibilityID :'||NVL(x_responsibility_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_role_msg := lv_api_header_con_role_msg||' : '||('RoleRespID :'||x_responsibility_id);
display_message('log','RoleResponsibilityID :'||x_responsibility_id);
END IF;
lv_api_header_con_role_msg := lv_api_header_con_role_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_role_stg_t
SET v_contact_role_id = x_responsibility_id,
api_msg = lv_api_header_con_role_msg,
valid_status = DECODE(NVL(x_responsibility_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND Contact_id = rec_header_con_role.Contact_id;
ELSE
display_message('log',' No Customer Contact Roles Found');
END IF;
-- ----------------------------------------------------
END LOOP; --End CustomerHeaderContactsRoles
--End 5.Customer Header Contacts Roles
-- ---------------------------------------------------------------
END LOOP; --End CustomerHeaderContacts
--End 3.Customer Header Contacts
-- --------------------------------------------------------------
-- --------------------------------------------------------------
-- 6. Cursor Starts - Customer Location
-- --------------------------------------------------------------
FOR rec_sites_location IN cur_sites_location (rec_header.customer_number) LOOP
--lv_api_location_msg := NULL;
lv_site_location_id := NUll;
lv_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
lv_site_location_flag := NULL;
--lv_party_site_flag := 'N';
--lv_cust_acct_site_flag := 'N';
IF rec_sites_location.v_party_location_id IS NULL THEN --Location
display_message('log','Location:'||rec_sites_location.address1);
p_location_rec.country := rec_sites_location.country;
p_location_rec.address1 := rec_sites_location.address1;
p_location_rec.address2 := rec_sites_location.address2;
p_location_rec.address3 := rec_sites_location.address3;
p_location_rec.city := rec_sites_location.city;
p_location_rec.postal_code := rec_sites_location.postal_code;
p_location_rec.state := rec_sites_location.state;
p_location_rec.province := rec_sites_location.province;
p_location_rec.attribute1 := rec_sites_location.attribute1;
p_location_rec.sales_tax_geocode := rec_sites_location.sales_tax_geocode;
p_location_rec.sales_tax_inside_city_limits := rec_sites_location.sales_tax_inside_city_limits;
p_location_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_LOCATION_V2PUB.CREATE_LOCATION...');
hz_location_v2pub.create_location ('T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Location status :'||x_return_status||' LocationID :'||NVL(x_location_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_site_location_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_org_msg||' : '||('LocationID :'||x_location_id);
display_message('log','New LocationID :'||x_location_id);
lv_site_location_flag := 'Y';
lv_site_location_id := x_location_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
lv_site_location_id := rec_sites_location.v_party_location_id;
display_message('log','Location is Already exits Loaction ID: '||rec_sites_location.v_party_location_id);
END IF; --End Location
-- -------------------------------------------------------------------
-- Create Party Site
-- -------------------------------------------------------------------
IF rec_sites_location.v_party_site_id IS NULL THEN
--display_message('log', 'Party ID:' || lv_party_id);
display_message('log', 'Create site id with Location Id:' || lv_site_location_id);
p_party_site_rec.party_id := lv_party_id;
p_party_site_rec.location_id := lv_site_location_id;
p_party_site_rec.identifying_address_flag := rec_sites_location.identifying_address_flag;
p_party_site_rec.created_by_module := gc_created_by_module;
--p_party_site_rec.status := 'Y';
--p_party_site_rec.orig_system_reference := rec_sites_location.site_number;
--p_party_site_rec.orig_system := gc_orig_system;
display_message('log','Calling HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE...');
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Party Site status :'||x_return_status||' PartySiteID :'||NVL(x_party_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_site_location_flag := 'N';
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_location_msg||' : '||('PartySiteID :'||x_party_site_id);
display_message('log','New PartySiteID :'||x_party_site_id);
--lv_party_site_flag := 'Y';
lv_site_location_flag := 'Y';
lv_party_site_id := x_party_site_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
display_message('log','Party Site is Already Created :'||rec_sites_location.v_party_site_id);
lv_party_site_id := rec_sites_location.v_party_site_id;
END IF;
-- -------------------------------------------------------------------
-- Create Customer Account Site
-- -------------------------------------------------------------------
IF rec_sites_location.v_cust_site_id IS NULL THEN
p_cust_acct_site_rec.cust_account_id := lv_cust_account_id;
p_cust_acct_site_rec.party_site_id := lv_party_site_id;
--p_cust_acct_site_rec.org_id := lv_org_id;
p_cust_acct_site_rec.territory := rec_sites_location.territory;
p_cust_acct_site_rec.created_by_module := gc_created_by_module;
--p_cust_acct_site_rec.orig_system_reference := rec_sites_location.site_number;
--p_cust_acct_site_rec.orig_system := gc_orig_system;
display_message('log','Calling HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE...');
hz_cust_account_site_v2pub.create_cust_acct_site
('T',
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site status :'||x_return_status||' CustAcctSiteID :'||NVL(x_cust_acct_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_site_location_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_location_msg||' : '||('CustAcctSiteID :'||x_cust_acct_site_id);
display_message('log','New CustAcctSiteID :'||x_cust_acct_site_id);
--lv_cust_acct_site_flag := 'Y';
lv_site_location_flag := 'Y';
lv_cust_acct_site_id := x_cust_acct_site_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
display_message('log',' Customer Account site already exists ACCTSiteID:'||rec_sites_location.v_cust_site_id);
lv_cust_acct_site_id := rec_sites_location.v_cust_site_id;
END IF;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_location_stg_t
SET valid_status = DECODE(NVL(v_cust_site_id,NVL(lv_cust_acct_site_id,0)),0,'PROCESSFAILED','PROCESSED'),
api_msg = api_msg||lv_api_location_msg,
v_party_location_id = lv_site_location_id ,
v_party_site_id = lv_party_site_id,
v_cust_site_id = lv_cust_acct_site_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id;
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose CM
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'CM')
LOOP
display_message('log','In Party Site Use CM old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
lv_api_site_use_msg := NULL;
lv_cm_site_use_id := NULL;
--lv_cm_site_use_flag :='N';
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
-- --------------------------------------------------------------
-- Create Customer Account Site Use
-- --------------------------------------------------------------
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','CM lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_cm_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_cm_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_cm_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_cm_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_cm_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_cm_rec.location := rec_sites_use.location;
p_cust_site_use_cm_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling CM CREATE_CUST_SITE_USE API...');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_cm_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code CM Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('CM SiteUseID :'||x_site_use_id);
display_message('log','CM PartySiteUseID :'||x_site_use_id);
--lv_cm_site_use_flag := 'Y';
lv_cm_site_use_id := x_site_use_id;
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_cm_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_cm_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id;
ELSE
display_message('log','*****No CrecitMemo(CM) Site Use Found *****');
END IF;
END LOOP; --End SiteUse CM
-- ---------------------------------------------------------------------
--END Customer Sites Business Purpose CM
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose BILL_TO
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'BILL_TO')
LOOP
--lv_bill_to_site_use_flag := 'N';
lv_api_site_use_msg := NULL;
lv_bill_to_site_use_id := NULL;
display_message('log','In Party Site Use BILL_TO old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
-- --------------------------------------------------------------
-- Create Customer Account Site Use
-- --------------------------------------------------------------
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','BILL_TO lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_bill_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_bill_rec.site_use_code := rec_sites_use.site_use_code; --BILL_TO site use code
p_cust_site_use_bill_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_bill_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_bill_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_bill_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_bill_rec.location := rec_sites_use.location;
--p_cust_site_use_rec.org_id := lv_org_id;
p_cust_site_use_bill_rec.created_by_module := gc_created_by_module;
--p_cust_site_use_rec.ship_sets_include_lines_flag := rec_sites_use.ship_sets_include_lines_flag;
--p_cust_site_use_rec.arrivalsets_include_lines_flag := rec_sites_use.arrivalsets_include_lines_flag;
--p_cust_site_use_rec.sched_date_push_flag := rec_sites_use.sched_date_push_flag;
p_customer_profile_rec.collector_id := rec_sites_use.collector_id;
--p_customer_profile_rec.status := rec_sites_use.status_cust_profile;
p_customer_profile_rec.profile_class_id := rec_sites_use.customer_profile_class_id;
p_customer_profile_rec.credit_checking := rec_sites_use.credit_checking;
p_customer_profile_rec.tolerance := rec_sites_use.tolerance;
p_customer_profile_rec.discount_terms := rec_sites_use.discount_terms;
p_customer_profile_rec.dunning_letters := rec_sites_use.dunning_letters;
p_customer_profile_rec.interest_charges := rec_sites_use.interest_charges;
p_customer_profile_rec.credit_balance_statements := rec_sites_use.credit_balance_statements;
p_customer_profile_rec.credit_hold := rec_sites_use.credit_hold;
p_customer_profile_rec.standard_terms := rec_sites_use.standard_terms;
p_customer_profile_rec.override_terms := rec_sites_use.override_terms;
p_customer_profile_rec.auto_rec_incl_disputed_flag := rec_sites_use.auto_rec_incl_disputed_flag;
p_customer_profile_rec.tax_printing_option := rec_sites_use.tax_printing_option;
p_customer_profile_rec.cons_inv_flag := rec_sites_use.cons_inv_flag;
display_message('log','Calling BILL_TO HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE.....');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_bill_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code BILL_TO Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('BILL_TO SiteUseID :'||x_site_use_id);
display_message('log','BILL_TO PartySiteUseID :'||x_site_use_id);
lv_bill_to_site_use_id := x_site_use_id;
-- lv_bill_to_site_use_flag := 'Y';
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_bill_to_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_bill_to_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND location = rec_sites_use.location;
ELSE
display_message('log','*****No Bill To Site Use Found *****');
END IF;
-- ---------------------------------------------------------------------
END LOOP; --7.End SiteUse BILL_TO --END Customer Sites Business Purpose BILL_TO
-- ---------------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose SHIP_TO
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'SHIP_TO')
LOOP
lv_api_site_use_msg := NULL;
lv_ship_to_site_use_id := NULL;
lv_bill_to_site_use_id := NULL;
display_message('log','In Party Site Use SHIP_TO old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
BEGIN --Get Bill to Site use of Shipment location
SELECT v_cust_site_use_id
INTO lv_bill_to_site_use_id
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code='BILL_TO'
AND customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id
AND primary_flag ='Y';
EXCEPTION WHEN OTHERS THEN
lv_bill_to_site_use_id := NULL;
display_message('log','**NO BILL_TO_SITE_USE found for shipment Location :'||rec_sites_use.location);
END;
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
display_message('log',' SHIP TO lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_ship_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_ship_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_ship_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_ship_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_ship_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_ship_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_ship_rec.location := rec_sites_use.location;
--p_cust_site_use_rec.org_id := lv_org_id;
IF rec_sites_use.bill_to_site_use_id IS NOT NULL THEN
p_cust_site_use_ship_rec.bill_to_site_use_id := lv_bill_to_site_use_id; --Bill site id
END IF;
p_cust_site_use_ship_rec.created_by_module := gc_created_by_module;
display_message('log','Calling SHIP_TO CREATE_CUST_SITE_USE API...');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_ship_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code SHIP_TO Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('SHIP_TO SiteUseID :'||x_site_use_id);
display_message('log','SHIP_TO PartySiteUseID :'||x_site_use_id);
-- lv_ship_to_site_use_flag := 'Y';
lv_ship_to_site_use_id := x_site_use_id;
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_ship_to_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_ship_to_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id;
ELSE
display_message('log','*****No Bill To Site Use Found *****');
END IF;
-- ---------------------------------------------------------------------
END LOOP; -- 7.End SiteUse SHIP_TO
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
-- 8. Cursor Starts - Customer Sites Communication
-- --------------------------------------------------------------
FOR rec_sites_phone IN cur_sites_phone (rec_header.customer_number) --Start CustomerSitesCommunication
LOOP
display_message('log','In Site Phone');
lv_api_site_phone_msg := NULL;
IF rec_sites_phone.v_cust_site_phone_id IS NULL THEN
p_contact_point_rec.contact_point_type := rec_sites_phone.contact_point_type; --'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id := lv_party_site_id;
p_contact_point_rec.primary_flag := rec_sites_phone.primary_flag;
p_contact_point_rec.created_by_module := gc_created_by_module;
IF rec_sites_phone.contact_point_type = 'PHONE'
AND rec_sites_phone.phone_number IS NOT NULL THEN
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_phone_rec.phone_area_code := rec_sites_phone.area_code;
p_phone_rec.phone_country_code := rec_sites_phone.country_code;
p_phone_rec.phone_number := rec_sites_phone.phone_number;
p_phone_rec.phone_line_type := rec_sites_phone.phone_type;
-- p_contact_point_rec.status := rec_sites_phone.status;
END IF;
IF rec_sites_phone.contact_point_type = 'EMAIL'
AND rec_sites_phone.email_address IS NOT NULL THEN
--p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.status := rec_sites_phone.status;
p_emailv_rec.email_format := rec_sites_phone.email_format;
p_emailv_rec.email_address := rec_sites_phone.email_address;
END IF;
display_message('log','Calling EAMIL HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point ('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site Contact Status:'||x_return_status||' ContactPoinID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := 'Site ContactPoinID :'||x_contact_point_id;
display_message('log','Site ContactPoinID :'||x_contact_point_id);
lv_site_phone_id := x_contact_point_id;
--lv_site_phone_flag := 'Y';
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
--Update site Phone stg
UPDATE xxmtz_ar_cstmr_site_ph_stg_t
SET valid_status = DECODE(NVL(lv_site_phone_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_phone_msg,
v_cust_site_phone_id = lv_site_phone_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_phone.address_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; --8. End Customer Sites Communication
-- ---------------------------------------------------------------------
-- 9. Cursor Starts - Customer Sites Contacts
-- --------------------------------------------------------------
FOR rec_sites_con IN cur_sites_con (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
display_message('log','IN Cur_sites_con');
lv_api_site_phone_msg := NULL;
lv_sites_con_party_id := NULL;
lv_site_acc_role_party_id := NULL;
lv_site_account_role_id := NULL;
--lv_site_account_role_flag := 'N';
lv_sites_con_party_flag := 'Y';
IF rec_sites_con.last_name IS NOT NULL --LastName
AND rec_sites_con.v_cust_site_contact_id IS NULL THEN
p_person_rec.person_first_name := rec_sites_con.first_name;
p_person_rec.person_last_name := rec_sites_con.last_name;
p_person_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling Site ContactPerson HZ_PARTY_V2PUB.CREATE_PERSON......');
hz_party_v2pub.create_person ('T',
p_person_rec,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Sites Contact Person Status:'||x_return_status||' SiteContactPartyID :'||NVL(x_party_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('SiteContactPartyID :'||x_party_id);
lv_sites_con_party_id := x_party_id;
lv_sites_con_party_flag := 'Y';
display_message('log','SiteContactPartyID :'||x_party_id);
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
display_message('log','Last Name Should not be null Or Person is exists :'||rec_sites_con.v_cust_site_contact_id);
END IF; --End LastName
-- -------------------------------------------------------------------
-- Create Sites Contact Relationship
-- -------------------------------------------------------------------
IF rec_sites_con.v_org_contact_id IS NULL THEN
p_org_contact_rec.contact_number := rec_sites_con.contact_number;
p_org_contact_rec.created_by_module := gc_created_by_module;
p_org_contact_rec.party_rel_rec.subject_id := lv_sites_con_party_id;
--<<value for party_id from step 7>
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.object_id := lv_party_id;
--<<value for party_id from step 2>
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.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := rec_sites_con.start_date;
display_message('log',' Calling Site HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT......');
hz_party_contact_v2pub.create_org_contact
('T',
p_org_contact_rec,
x_org_contact_id,
x_party_relv_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Sites Relation Status:'||x_return_status||' OrgContactID :'||NVL(x_org_contact_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('OrgContactID :'||x_org_contact_id);
lv_site_acc_role_party_id := x_party_id;
--lv_site_acc_role_party_flag := 'Y';
--lv_sites_con_party_flag := 'Y';
display_message('log','OrgContactID :'||x_org_contact_id);
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
lv_site_acc_role_party_id:=rec_sites_con.v_org_contact_id;
END IF;
-- -------------------------------------------------------------------
-- Create Sites Contact Role
-- -------------------------------------------------------------------
IF rec_sites_con.v_rel_party_id IS NULL THEN
p_cr_cust_acc_role_rec.party_id := lv_site_acc_role_party_id;
p_cr_cust_acc_role_rec.cust_account_id := lv_cust_account_id;
p_cr_cust_acc_role_rec.cust_acct_site_id := lv_cust_acct_site_id;
--p_cr_cust_acc_role_rec.primary_flag := 'Y';
p_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_cr_cust_acc_role_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling Site HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE......');
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_cr_cust_acc_role_rec,
x_cust_account_role_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Sites Account Role Status:'||x_return_status||' CustAcctRoleID :'||NVL(x_cust_account_role_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('OrgContactID :'||x_cust_account_role_id);
display_message('log','OrgContactID :'||x_cust_account_role_id);
lv_site_account_role_id := x_cust_account_role_id;
--lv_site_account_role_flag := 'Y';
--lv_sites_con_party_flag := 'N';
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
lv_site_account_role_id := rec_sites_con.v_rel_party_id;
END IF;
----END Create Sites Contact Role
--Update Staging Table
BEGIN
UPDATE xxmtz_ar_cstmr_site_con_stg_t
SET valid_status = DECODE(NVL(lv_site_account_role_id,0),0,'PROCESSFAILED',DECODE(lv_sites_con_party_flag,'Y','PROCESSED','PROCESSFAILED')),
api_msg = lv_api_site_con_msg,
v_cust_site_contact_id = lv_sites_con_party_id,
v_org_contact_id = lv_site_acc_role_party_id,
v_rel_party_id = lv_site_account_role_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_con.address_id;
END;
-----------------------------------------------------------------
-- 10. Cursor Starts - Customer Sites Contact Communications
-- --------------------------------------------------------------
FOR rec_sites_con_phone IN cur_sites_con_phone (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_site_contact_role_id := NULL;
lv_api_site_con_role_msg := NULL;
IF rec_sites_con_phone.v_cust_site_contact_phone_id IS NULL THEN
display_message('log','IN cur_sites_con_phone ');
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := lv_site_acc_role_party_id;
--lv_party_site_id;
p_contact_point_rec.primary_flag := rec_sites_con_phone.primary_flag;
-- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
-- p_contact_point_rec.status := rec_sites_con_phone.status;
p_phone_rec.phone_area_code := rec_sites_con_phone.area_code;
p_phone_rec.phone_country_code := rec_sites_con_phone.country_code;
p_phone_rec.phone_number := rec_sites_con_phone.phone_number;
p_phone_rec.phone_line_type := rec_sites_con_phone.phone_type;
p_contact_point_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling SiteContact HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT......');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site ContactPoint Status:'||x_return_status||' SiteContactID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
--lv_site_contact_phone_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_con_phone_msg := lv_api_site_con_phone_msg||' : '||('SiteContactID :'||x_contact_point_id);
display_message('log','SiteContactID :'||x_contact_point_id);
--lv_site_contact_phone_flag := 'Y';
lv_site_contact_phone_id := x_contact_point_id;
END IF;
lv_api_site_con_phone_msg := lv_api_site_con_phone_msg||' : '||lv_message_out;
--Update Staging Table
-- display_message('log',' lv_site_contact_phone_flag Value :'||lv_site_contact_phone_flag);
UPDATE xxmtz_ar_cstmr_si_con_ph_stg_t
SET valid_status = DECODE(NVL(lv_site_contact_phone_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_con_phone_msg,
v_cust_site_contact_phone_id = lv_site_contact_phone_id
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_sites_con_phone.contact_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; -- 10. Cursor Ends - CustomerSitesCommunications
-- ---------------------------------------------------------------------
-----------------------------------------------------------------
-- 11. Cursor Starts - Customer Sites Contact Roles
-- --------------------------------------------------------------
FOR rec_sites_con_role IN cur_sites_con_role (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_api_site_con_role_msg := NULL;
IF rec_sites_con_role.v_cust_site_contact_role_id IS NULL THEN
display_message('log','IN cur_sites_con_role');
p_role_responsibility_rec.responsibility_type := rec_sites_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := lv_site_account_role_id;
p_role_responsibility_rec.primary_flag := rec_sites_con_role.primary_flag;
p_role_responsibility_rec.created_by_module := gc_created_by_module;
display_message('log','Calling Site Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_ROLE_RESPONSIBILITY........');
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site Contact Role Status:'||x_return_status||' Responsibility ID :'||NVL(x_responsibility_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
--lv_site_contact_role_flag := 'N' ;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_con_role_msg := lv_api_site_con_role_msg||' : '||('ResponsibilityID :'||x_responsibility_id);
display_message('log','ResponsibilityID :'||x_responsibility_id);
--lv_site_contact_role_flag := 'Y';
lv_site_contact_role_id := x_responsibility_id;
END IF;
lv_api_site_con_role_msg := lv_api_site_con_role_msg||' : '||lv_message_out;
--Update Staging Table
--display_message('log',' lv_site_contact_role_flag Value :'||lv_site_contact_role_flag);
UPDATE xxmtz_ar_cstmr_si_role_stg_t
SET valid_status = DECODE(NVL(lv_site_contact_role_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_con_role_msg,
v_cust_site_contact_role_id = lv_site_contact_role_id
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_sites_con.contact_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; --11.END Cursor - Customer Sites Contact Roles
-- ---------------------------------------------------------------------
END LOOP; --9. End Customer Sites Contacts
-- ---------------------------------------------------------------------
END LOOP; --6.End CustomerLocation
-- ---------------------------------------------------------------------
EXCEPTION WHEN NO_PARTY_ID THEN
display_message('log','Party Is not created');
WHEN OTHERS THEN
display_message('log','Other Error :'||Sqlerrm);
END; --End forException
END LOOP; --1.End HeaderLoop
COMMIT;
END xxmtz_ar_cstmr_cnv_load_prc;
--This Procedure is used to Display message in Log and Output files
PROCEDURE display_message(p_mode Varchar2, p_message Varchar2) IS
BEGIN
IF p_mode = 'output' then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
ELSIF p_mode = 'log' then
FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
ELSIF p_mode = 'both' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
END IF;
DBMS_OUTPUT.PUT_LINE(p_message);
END display_message;
---Procedure to Update header status if any line status is fialed.
PROCEDURE Update_Header_staus(p_customer_number IN VARCHAR2,
p_action IN VARCHAR2)
IS
lv_header_cnt NUMBER := NULL;
lv_header_comm_cnt NUMBER := NULL;
lv_header_contact_cnt NUMBER := NULL;
lv_header_cont_comm_cnt NUMBER := NULL;
lv_header_cont_role_cnt NUMBER := NULL;
lv_site_location_cnt NUMBER := NULL;
lv_site_use_cnt NUMBER := NULL;
lv_site_comm_cnt NUMBER := NULL;
lv_site_contact_cnt NUMBER := NULL;
lv_site_cont_comm_cnt NUMBER := NULL;
lv_site_cont_role_cnt NUMBER := NULL;
BEGIN
------Header Count---
SELECT COUNT(1)
INTO lv_header_cnt
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE (valid_status = ('PROCESSFAILED') --p_action
OR v_party_id IS NULL
OR n_location_id IS NULL
OR n_header_site_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Failed Count :'||lv_header_cnt);
------Header Phone Count-------
SELECT COUNT(1)
INTO lv_header_comm_cnt
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_point_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Phone Count :'||lv_header_comm_cnt);
------Header Contact------
SELECT COUNT(1)
INTO lv_header_contact_cnt
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_party_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Contact Count :'||lv_header_contact_cnt);
------Header Contact Phone count----
SELECT count(1)
INTO lv_header_cont_comm_cnt
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Header Contact Phone Count :'||lv_header_contact_cnt);
-------Header Contact Roles count------
SELECT count(1)
INTO lv_header_cont_role_cnt
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_role_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Header Contact Roles Count :'||lv_header_cont_role_cnt);
---------- Sites Location Count------------------------
SELECT count(1)
INTO lv_site_location_cnt
FROM xxmtz_ar_cstmr_location_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Location Count :'||lv_site_location_cnt);
---------- Sites Use count-------------
SELECT count(1)
INTO lv_site_use_cnt
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_use_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Use Count :'||lv_site_use_cnt);
---------- Sites Phone Count -----------------------
SELECT count(1)
INTO lv_site_comm_cnt
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Phone Count :'||lv_site_comm_cnt);
------------ Sites Contact Count --------------------
SELECT count(1)
INTO lv_site_contact_cnt
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Count :'||lv_site_contact_cnt);
------------ Sites Contact Phone Count --------------------
SELECT count(1)
INTO lv_site_cont_comm_cnt
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Phone Count :'||lv_site_cont_comm_cnt);
---------------- Header Contact Role Count--------------------
SELECT Count(1)
INTO lv_site_cont_role_cnt
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_role_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Role Count :'||lv_site_cont_role_cnt);
IF lv_header_cnt = 0
AND lv_header_comm_cnt = 0
AND lv_header_contact_cnt = 0
AND lv_header_cont_comm_cnt = 0
AND lv_header_cont_role_cnt = 0
AND lv_site_location_cnt = 0
AND lv_site_use_cnt = 0
AND lv_site_comm_cnt = 0
AND lv_site_contact_cnt = 0
AND lv_site_cont_comm_cnt = 0
AND lv_site_cont_role_cnt = 0
THEN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = 'PROCESSED'
WHERE customer_number = p_customer_number;
display_message('log','Customer :'||p_customer_number||' Is Processed');
ELSE
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = 'PROCESSFAILED'
WHERE customer_number = p_customer_number;
display_message('log',' Customer :'||p_customer_number||' Is Failed');
END IF;
END Update_Header_staus;
END XXMTZ_AR_CSTMR_CONV_API_PKG;
/
AS
--
-- --------------------------------------------------------------
-- Global Variables Declaration
-- --------------------------------------------------------------
gc_created_by_module CONSTANT VARCHAR2 (30) := 'TCA_V2_API'; --Created by module from Lookup type "HZ_CREATED_BY_MODULES"
gc_user_id NUMBER := fnd_global.user_id; --1318;
gc_resp_id NUMBER := fnd_global.resp_id; --20678;
gc_resp_appl_id NUMBER := 222; --fnd_global.resp_appl_id;
gc_orig_system VARCHAR2(30) := 'ORACLE_AIA';
PROCEDURE xxmtz_ar_cstmr_cnv_main (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_process_action IN VARCHAR2,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2)
IS
lv_process_action VARCHAR2 (10) := p_process_action;
lv_org_id NUMBER := p_org_id;
lv_errbuf VARCHAR2 (2000);
lv_retcode NUMBER;
ex_nullv_action EXCEPTION;
ex_invalid_action EXCEPTION;
ex_main_exception EXCEPTION;
BEGIN
IF lv_process_action IS NULL THEN
RAISE ex_nullv_action;
END IF;
IF lv_process_action NOT IN ('VALIDATE', 'UPLOAD') THEN --V(Validate) U(Upload)
RAISE ex_invalid_action;
END IF;
IF lv_process_action = 'VALIDATE' THEN
xxmtz_ar_cstmr_cnv_val_prc (lv_errbuf, lv_retcode, lv_org_id,p_cust_number);
IF lv_retcode = 2 THEN
RAISE ex_main_exception;
END IF;
x_retcode := lv_retcode;
x_errbuf := lv_errbuf;
display_message('log','Validation Procedure Call has Completed');
ELSIF lv_process_action = 'UPLOAD'
THEN
xxmtz_ar_cstmr_cnv_load_prc (lv_errbuf, lv_retcode, lv_org_id,p_cust_number);
IF lv_retcode = 2 THEN
RAISE ex_main_exception;
END IF;
x_retcode := lv_retcode;
x_errbuf := lv_errbuf;
display_message('log', 'Load Procedure Call has Completed');
END IF;
EXCEPTION
WHEN ex_nullv_action THEN
x_retcode := 2;
display_message('log','Parameters passed to the program are NULL');
WHEN ex_invalid_action THEN
x_retcode := 2;
display_message('log', 'Parameters passed to the program are NULL');
WHEN ex_main_exception THEN
x_retcode := 2;
x_errbuf := 'Error Stage : ' || lv_errbuf;
display_message('log','Procedure Error--' || lv_retcode || '--'|| lv_errbuf);
WHEN OTHERS THEN
x_retcode := 2;
display_message('log','Error in Main Procedure--' || SQLCODE|| '--'|| SQLERRM);
END xxmtz_ar_cstmr_cnv_main; --End Mian Procedure
---Validate Procedure
PROCEDURE xxmtz_ar_cstmr_cnv_val_prc (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2 )
IS
-- ----------------------------------------------
-- Cursor Declaration
-- ----------------------------------------------
--Header Account
CURSOR cur_header IS
SELECT *
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('NEW', 'VALIDATEFAILED')
AND customer_number = NVL(p_cust_number,customer_number) --'TESTASP'
ORDER BY party_id, customer_number;
--Header Phone
CURSOR cur_header_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact
CURSOR cur_header_contact (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact Phone
CURSOR cur_header_con_phone ( p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Roles
CURSOR cur_header_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Sites Location
CURSOR cur_sites_location (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_location_stg_t
WHERE customer_number = p_customer_number;
-- Sites Use
CURSOR cur_sites_use (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
--,p_site_use IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number
-- AND site_use_code = p_site_use
ORDER BY site_use_code;
-- Sites Phone
CURSOR cur_sites_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE customer_number = p_customer_number;
-- Sites Contact
CURSOR cur_sites_con (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number;
-- Sites Contact Phone
CURSOR cur_sites_con_phone (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Site Contact Role
CURSOR cur_sites_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
--Variable Declaration
lv_party_id hz_parties.party_id%TYPE := NULL;
lv_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
lv_collector_id ar_collectors.collector_id%TYPE := NULL;
lv_statement_cycle_id ar_statement_cycles.statement_cycle_id%TYPE := NULL;
lv_dunning_letter_set_id ar_dunning_letter_sets.dunning_letter_set_id%TYPE :=NULL;
lv_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE:= NULL;
lv_header_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_location_id hz_locations.location_id%TYPE :=NULL;
lv_contact_point_id hz_contact_points.contact_point_id%TYPE :=NULL;
lv_contact_party_id hz_parties.party_id%TYPE := NULL;
lv_org_contact_id hz_relationships.party_id%TYPE := NULL;
lv_acct_role_id hz_cust_account_roles.cust_account_role_id%TYPE := NULL;
lv_contact_role_id hz_role_responsibility.responsibility_id%TYPE := NULL;
lv_loc_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_site_location_id hz_locations.location_id%TYPE :=NULL;
lv_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE := NULL;
lv_site_contact_point_id hz_contact_points.contact_point_id%TYPE :=NULL;
lv_site_cont_party_id hz_parties.party_id%TYPE := NULL;
lv_site_org_cont_id hz_relationships.party_id%TYPE := NULL;
lv_site_acct_role_id hz_cust_account_roles.cust_account_role_id%TYPE := NULL;
lv_terms_count NUMBER := 0;
lv_currency_count NUMBER := 0;
lv_lookups_exist NUMBER := 0;
lv_header_count NUMBER := 0;
lv_territory_count NUMBER := 0;
lv_header_validate_flag VARCHAR2 (1):= 'Y';
lv_header_communicate_flag VARCHAR2 (1):= 'Y';
lv_site_validate_flag VARCHAR2 (1):= 'Y';
lv_site_comm_validate_flag VARCHAR2 (1):= 'Y';
lv_header_validate_msg VARCHAR2 (4000) := NULL;
lv_header_communicate_msg VARCHAR2 (4000) := NULL;
lv_site_validate_msg VARCHAR2 (4000) := NULL;
lv_site_comm_validate_msg VARCHAR2 (4000) := NULL;
--User Exception
ex_lookup_setup_err EXCEPTION;
ex_header_total EXCEPTION;
BEGIN
display_message('log','*******BEGIN Validate Procedure**************');
-- --------------------------------------------------------------
-- Validate PARTY_SITE_USE_CODE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'PARTY_SITE_USE_CODE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR PARTY_SITE_USE_CODE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate COMMUNICATION_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'COMMUNICATION_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR COMMUNICATION_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate email_format Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'EMAIL_FORMAT'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR EMAIL_FORMAT Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CONTACT_POINT_PURPOSE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CONTACT_POINT_PURPOSE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CONTACT_POINT_PURPOSE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate SITE_USE_CODE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR SITE_USE_CODE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate SIC_CODE_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'SIC_CODE_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR SIC_CODE_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CODE_STATUS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CODE_STATUS'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CODE_STATUS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate YES/NO Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'YES/NO' AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR YES/NO Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CUSTOMER_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CUSTOMER_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CUSTOMER_CLASS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER CLASS' --Customer Class
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CUSTOMER CLASS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate TAX_PRINTING_OPTION Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS ( SELECT 1
FROM ar_lookups
WHERE lookup_type = 'TAX_PRINTING_OPTION'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR TAX_PRINTING_OPTION Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate REGISTRY_STATUS Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (SELECT 1
FROM ar_lookups
WHERE lookup_type = 'REGISTRY_STATUS'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR REGISTRY_STATUS Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate CONTACT_ROLE_TYPE Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'CONTACT_ROLE_TYPE'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR CONTACT_ROLE_TYPE Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
-- --------------------------------------------------------------
-- Validate HZ_CREATED_BY_MODULES Lookup Types Setup
-- --------------------------------------------------------------
BEGIN
SELECT COUNT (1)
INTO lv_lookups_exist
FROM ar_lookups
WHERE EXISTS (
SELECT 1
FROM ar_lookups
WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
AND enabled_flag = 'Y');
IF lv_lookups_exist = 0 THEN
display_message('log','AR HZ_CREATED_BY_MODULES Lookups Type Setup Error');
RAISE ex_lookup_setup_err;
END IF;
END;
------------------------END Lookup Validation---------------------------------
SELECT COUNT (*)
INTO lv_header_count
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('NEW', 'VALIDATEFAILED');
--IF lv_header_count = 0 THEN
-- RAISE ex_header_total;
--END IF;
-- --------------------------------------------------------------
-- 1.Cursor Starts - Customer Header
-- --------------------------------------------------------------
FOR rec_header IN cur_header LOOP --Starts Header LOOP
lv_header_validate_flag := 'Y';
lv_header_validate_msg := NULL;
lv_party_id := NULL;
lv_cust_account_id := NULL;
lv_collector_id := NULL;
lv_profile_class_id := NULL;
lv_statement_cycle_id := NULL;
lv_dunning_letter_set_id := NULL;
lv_header_site_id := NULL;
lv_location_id := NULL;
lv_terms_count := 0;
lv_currency_count := 0;
display_message('log','Processgin Customer Number :'||rec_header.customer_number);
------------ Validate the Duplicate Organization-------------------------------
IF rec_header.party_type = 'ORGANIZATION' THEN
BEGIN
SELECT hp.party_id, cust_account_id
INTO lv_party_id, lv_cust_account_id
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_name = rec_header.customer_name
AND hp.party_type = 'ORGANIZATION'
AND hca.party_id = hp.party_id;
EXCEPTION WHEN OTHERS THEN
lv_party_id := NULL;
lv_cust_account_id := NULL;
END;
IF lv_party_id IS NOT NULL THEN
lv_header_validate_flag := 'N';
lv_header_validate_msg := 'Duplicate Organization Name :'||rec_header.customer_name;
END IF;
END IF;
------------ Validate Header Tolarence Should be in -100 to 100 range------------------
IF rec_header.tolerance IS NOT NULL THEN
IF rec_header.tolerance BETWEEN -100 AND 100 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Tolerance Value Must between -100 and 100. Actual Tolerance:'||rec_header.tolerance;
END IF;
END IF;
------------Validate Collector Name------------------
IF rec_header.collector_name IS NOT NULL THEN
BEGIN
SELECT collector_id
INTO lv_collector_id
FROM ar_collectors
WHERE NAME = rec_header.collector_name;
EXCEPTION WHEN OTHERS THEN
lv_collector_id := NULL;
END;
IF lv_collector_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Collector Name against AR_COLLECTORS Name:'||rec_header.collector_name;
END IF;
END IF;
--------------Validate Profile Class--------------
IF rec_header.profile_class_name IS NOT NULL THEN
BEGIN
SELECT profile_class_id
INTO lv_profile_class_id
FROM hz_cust_profile_classes
WHERE NAME = rec_header.profile_class_name;
EXCEPTION WHEN OTHERS THEN
lv_profile_class_id := NULL;
END;
IF lv_profile_class_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Profile Class is not exists :'||rec_header.profile_class_name;
END IF;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Profile Class is not exists :'||rec_header.profile_class_name;
END IF;
--------------Validate Header Statement Cycle ID against AR_STATEMENT_CYCLES-----------
IF rec_header.statement_cycle_name IS NOT NULL THEN
BEGIN
SELECT statement_cycle_id
INTO lv_statement_cycle_id
FROM ar_statement_cycles
WHERE NAME = rec_header.statement_cycle_name;
EXCEPTION WHEN OTHERS THEN
lv_statement_cycle_id := NULL;
END;
IF lv_statement_cycle_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Statement Cycle against AR_STATEMENT_CYCLES';
END IF;
END IF;
----------------Validate Header Dunning_Letter_Set_Id against AR_DUNNING_LETTER_SETS-----------------
IF rec_header.dunning_letter_set_id IS NOT NULL THEN
BEGIN
SELECT dunning_letter_set_id
INTO lv_dunning_letter_set_id
FROM ar_dunning_letter_sets
WHERE NAME = rec_header.dunning_letter_set_name;
EXCEPTION WHEN OTHERS THEN
lv_dunning_letter_set_id := NULL;
END;
IF lv_dunning_letter_set_id IS NOT NULL THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Dunning_Letter_Set_Id against AR_DUNNING_LETTER_SETS';
END IF;
END IF;
----------------Validate the standard terms against RA_TERMS-----------------------------
IF rec_header.standard_terms IS NOT NULL THEN
BEGIN
SELECT COUNT (1)
INTO lv_terms_count
FROM ra_terms
WHERE NAME = rec_header.standard_terms_name;
END;
IF lv_terms_count > 0 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Standard Terms against RA_TERMS';
END IF;
END IF;
---------------- Validate Currency Code against FND_CURRENCIES----------------------------
IF rec_header.currency_code IS NOT NULL THEN
BEGIN
SELECT COUNT (1)
INTO lv_currency_count
FROM fnd_currencies
WHERE currency_code = rec_header.currency_code;
END;
IF lv_currency_count >0 THEN
NULL;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||' :Currency Code against FND_CURRENCIES';
END IF;
END IF;
---------------- Validate the address1, state, city and postal_code should not be null---------
IF rec_header.address1 IS NOT NULL
AND rec_header.state IS NOT NULL
AND rec_header.city IS NOT NULL
AND rec_header.postal_code IS NOT NULL
THEN
BEGIN
SELECT hl.location_id,hps.party_site_id
INTO lv_location_id,lv_header_site_id
FROM hz_locations hl,
hz_party_sites hps
WHERE hl.location_id = hps.location_id
AND address1 = rec_header.address1
AND NVL(address2,'-XX') = nvl(rec_header.address2,'-XX')
AND NVL(address3,'-XX') = nvl(rec_header.address3,'-XX')
AND NVL(address4,'-XX') = nvl(rec_header.address4,'-XX')
AND NVL(county,'-XX') = nvl(rec_header.county,'-XX')
AND state = rec_header.state
AND city = rec_header.city
AND postal_code = rec_header.postal_code
AND country = rec_header.country
AND hps.party_id = NVL(lv_party_id,rec_header.v_party_id);
display_message('log','location ID:'||lv_location_id||'-'||lv_header_site_id);
EXCEPTION WHEN OTHERS THEN
display_message('log',' Header Location is not found');
lv_location_id := NULL;
lv_header_site_id := NULL;
END;
ELSE
lv_header_validate_flag := 'N';
lv_header_validate_msg := lv_header_validate_msg||'Header Address1, State, City, postal_code should not be NULL';
END IF;
-----------Update Header Staging Table--------
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET v_party_id = lv_party_id,
v_cust_account_id = lv_cust_account_id,
customer_profile_class_id = lv_profile_class_id,
collector_id = lv_collector_id,
statement_cycle_id = lv_statement_cycle_id,
dunning_letter_set_id = lv_dunning_letter_set_id,
n_location_id = lv_location_id,
n_header_site_id = lv_header_site_id,
valid_status = DECODE(lv_header_validate_flag,'Y','VALIDATED','VALIDATEFAILED'),
api_msg = lv_header_validate_msg
WHERE customer_number = rec_header.customer_number;
-- -----------------------------------------------
-- 2.Cursor Starts - Customer Header Communication
-- ------------------------------------------------
FOR rec_header_phone IN cur_header_phone (rec_header.customer_number)
LOOP
lv_header_communicate_msg := NULL;
lv_contact_point_id := NULL;
lv_header_communicate_flag:= 'Y';
-------------Validate Phone Type-----------------------
IF rec_header_phone.contact_point_type = 'PHONE' THEN
IF rec_header_phone.phone_number IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='PHONE'
AND owner_table_id = lv_party_id --Heaer Party_id
AND phone_number = rec_header_phone.phone_number;
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id := NULL;
END;
ELSE
lv_header_communicate_flag := 'N';
lv_header_communicate_msg := 'Phone Number should not be NULL';
END IF;
END IF; --validation phone number
-------------Validate Email Type-----------------------
IF rec_header_phone.contact_point_type = 'EMAIL' THEN
IF rec_header_phone.email_address IS NOT NULL
AND rec_header_phone.email_format IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='EMAIL'
AND owner_table_id = lv_party_id --Heaer Party_id
AND email_address = rec_header_phone.email_address;
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id := NULL;
END;
ELSE
lv_header_communicate_flag := 'N';
lv_header_communicate_msg := 'Email Address and EmailFormat should not be NULL';
END IF;
END IF; --validation Email Type
--Updae staging table
display_message('log','Contact Id :'||lv_contact_point_id);
UPDATE XXMTZ_AR_CSTMR_PHONE_STG_T
SET valid_status = DECODE(lv_header_communicate_flag,'Y','VALIDATED','VALIDATEFAILED'),
api_msg = lv_header_communicate_msg,
v_contact_point_id =lv_contact_point_id
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_header_phone.contact_point_type;
-- --------------------------------------
END LOOP; --2. End Cursor - Customer Header Communication
-- --------------------------------------------------------------
--3. Cursor Starts - Customer Header Contacts
-- --------------------------------------------------------------
FOR rec_header_contact IN cur_header_contact (rec_header.customer_number) LOOP --Start CustomerHeaderContacts
lv_contact_party_id := NULL;
lv_org_contact_id := NULL;
lv_acct_role_id := NULL;
IF rec_header_contact.last_name IS NOT NULL THEN
BEGIN
SELECT hp_per.party_id contact_party_id
,hz_rel.party_id org_contact_Rel_id
,hcar.cust_account_role_id
INTO lv_contact_party_id,lv_org_contact_id,lv_acct_role_id
FROM hz_parties hp_per,
hz_relationships hz_rel,
hz_parties hp,
hz_cust_account_roles hcar
WHERE hp_per.party_type = 'PERSON'
AND hp_per.party_id = hz_rel.subject_id
AND hp.party_id = hz_rel.object_id
AND hcar.party_id = hz_rel.party_id
AND hp_per.person_last_name = rec_header_contact.last_name --Header contact last name
AND hp.party_id = lv_party_id ; --Header Party ID
EXCEPTION WHEN OTHERS THEN
lv_contact_party_id := NULL;
lv_org_contact_id := NULL;
lv_acct_role_id := NULL;
END;
IF lv_contact_party_id IS NOT NULL THEN
UPDATE xxmtz_ar_cstmr_contact_stg_t
SET v_contact_party_id = lv_contact_party_id,
v_org_contact_id = lv_org_contact_id,
v_rel_party_id = lv_acct_role_id,
api_msg = 'Contact Person is already created'
WHERE customer_number = rec_header.customer_number
AND last_name = rec_header_contact.last_name;
END IF;
END IF;
-----------------------------------------------------------------
-- 4.Cursor Starts - Customer Header Contacts Communication
-- --------------------------------------------------------------
FOR rec_header_con_phone IN cur_header_con_phone (rec_header.customer_number,
rec_header_contact.contact_id) --Start HeaderContactsCommunication
LOOP
lv_contact_point_id :=NULL;
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = lv_org_contact_id;-- 404872
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_phone_stg_t
SET v_phone_id =lv_contact_point_id
WHERE contact_id = rec_header_contact.contact_id
AND customer_number = rec_header.customer_number;
-- --------------------------------------
END LOOP; --4. End Cursor - Customer Header Contacts Communication
-- --------------------------------------------------------------
-- 5.Cursor Starts - Customer Header Contacts Roles
-- --------------------------------------------------------------
FOR rec_header_con_role IN cur_header_con_role (rec_header.customer_number,
rec_header_contact.contact_id)
LOOP
lv_contact_role_id :=NULL;
BEGIN
SELECT responsibility_id
INTO lv_contact_role_id
FROM hz_role_responsibility
WHERE cust_account_role_id = lv_acct_role_id;
EXCEPTION WHEN OTHERS THEN
lv_contact_role_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_role_stg_t
SET v_contact_role_id = lv_contact_role_id
WHERE contact_id = rec_header_contact.contact_id
AND customer_number = rec_header.customer_number;
-- --------------------------------------
END LOOP; --5. End Cursor - Customer Header Contacts Communication
-- --------------------------------------
END LOOP; --3. End Cursor - Customer Header Contacts cursor
-- --------------------------------------------------------------
-- 6. Cursor Starts - Customer Location
-- --------------------------------------------------------------
FOR rec_sites_location IN cur_sites_location (rec_header.customer_number)
LOOP
lv_site_validate_flag := 'Y';
lv_site_validate_msg := NULL;
lv_site_location_id := NULL;
lv_loc_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
---------------------Validate Customer Site Location-----------------------
IF rec_sites_location.address1 IS NOT NULL
AND rec_sites_location.state IS NOT NULL
AND rec_sites_location.city IS NOT NULL
AND rec_sites_location.postal_code IS NOT NULL THEN
BEGIN
SELECT hl.location_id,hps.party_site_id,hcas.cust_acct_site_id
INTO lv_site_location_id,lv_loc_party_site_id,lv_cust_acct_site_id
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas
WHERE hl.location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_account_id = lv_cust_account_id
AND address1 = rec_sites_location.address1
AND NVL(address2,'-XX') = nvl(rec_sites_location.address2,'-XX')
AND NVL(address3,'-XX') = nvl(rec_sites_location.address3,'-XX')
AND NVL(address4,'-XX') = nvl(rec_sites_location.address4,'-XX')
AND NVL(county,'-XX') = nvl(rec_sites_location.county,'-XX')
AND state = rec_sites_location.state
AND city = rec_sites_location.city
AND postal_code = rec_sites_location.postal_code
AND country = rec_sites_location.country
AND hps.party_id = NVL(lv_party_id,rec_header.v_party_id);
display_message('log','location ID:'||lv_location_id||'-'||lv_header_site_id);
EXCEPTION WHEN OTHERS THEN
display_message('log',' Site Location is not found');
lv_site_location_id := NULL;
lv_loc_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
END;
ELSE
lv_site_validate_flag := 'Y';
lv_site_validate_msg := lv_header_validate_msg||'Site Address1, State, City, postal_code should not be NULL';
END IF;
-----------------Validate territory ---------------------
IF rec_sites_location.territory IS NOT NULL THEN
BEGIN
SELECT COUNT (*)
INTO lv_territory_count
FROM fnd_territories
WHERE territory_code = rec_sites_location.territory;
END;
IF lv_territory_count > 0 THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg :='Currency Code against FND_TERROTORIES';
END IF;
END IF;
--------Update Staging Table----------
UPDATE xxmtz_ar_cstmr_location_stg_t
SET v_party_location_id = lv_site_location_id,
v_party_site_id = lv_loc_party_site_id,
v_cust_site_id = lv_cust_acct_site_id,
api_msg = lv_site_validate_msg
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id;
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
lv_profile_class_id := NULL;
lv_collector_id := NULL;
IF rec_sites_use.collector_name IS NOT NULL THEN
BEGIN
SELECT collector_id --COUNT (*)
INTO lv_collector_id
FROM ar_collectors
WHERE NAME = rec_sites_use.collector_name;
EXCEPTION WHEN OTHERS THEN
lv_collector_id := NULL;
END;
IF lv_collector_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg :=lv_site_validate_msg|| 'Collector Name against AR_COLLECTORS';
END IF;
END IF;
-------------- -- Validate Profile Class ID against HZ_CUST_PROFILE_CLASSES------------
IF rec_sites_use.profile_class_name IS NOT NULL THEN
BEGIN
SELECT profile_class_id
INTO lv_profile_class_id
FROM hz_cust_profile_classes
WHERE NAME = rec_sites_use.profile_class_name;
EXCEPTION WHEN OTHERS THEN
lv_profile_class_id := NULL;
END;
IF lv_profile_class_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := 'Profile Class against HZ_CUST_PROFILE_CLASSES';
END IF;
END IF;
-----------------Validate Statement Cycle ID against AR_STATEMENT_CYCLES---------------------------
IF rec_sites_use.statement_cycle_name IS NOT NULL THEN
lv_statement_cycle_id := NULL;
BEGIN
SELECT statement_cycle_id
INTO lv_statement_cycle_id
FROM ar_statement_cycles
WHERE NAME = rec_sites_use.statement_cycle_name;
EXCEPTION WHEN OTHERS THEN
lv_statement_cycle_id := NULL;
END;
IF lv_statement_cycle_id IS NOT NULL THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := lv_site_validate_msg||'Statement Cycle Id against AR_STATEMENT_CYCLES';
END IF;
END IF;
---------------------------Validate the Site standard terms against RA_TERMS-----------------
IF rec_sites_use.standard_terms IS NOT NULL THEN
BEGIN
SELECT COUNT (*)
INTO lv_terms_count
FROM ra_terms
WHERE NAME = rec_sites_use.standard_terms_name;
END;
IF lv_terms_count > 0 THEN
NULL;
ELSE
lv_site_validate_flag := 'N';
lv_site_validate_msg := 'Site Standard Terms against ra_terms';
END IF;
END IF;
-------------Update Site Use Stagin table------------
BEGIN
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET collector_id = lv_collector_id,
customer_profile_class_id = lv_profile_class_id,
statement_cycle_id = lv_statement_cycle_id,
api_msg = lv_site_validate_msg
WHERE customer_number = rec_sites_use.customer_number
AND site_use_code = rec_sites_use.site_use_code
AND address_id = rec_sites_use.address_id;
END;
-- --------------------------------------------
END LOOP; --7.END Customer Sites Business Purpose cursor
-- --------------------------------------------
-- 8. Cursor Starts - Customer Sites Communication
-- --------------------------------------------------------------
FOR rec_sites_phone IN cur_sites_phone (rec_header.customer_number)
LOOP
lv_site_contact_point_id := NULL;
lv_site_comm_validate_flag := NULL;
lv_site_comm_validate_msg := NULL;
--- Validate the phone_number should not be null---------------------
-------------Validate Phone Type-----------------------
IF rec_sites_phone.contact_point_type = 'PHONE' THEN
IF rec_sites_phone.phone_number IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_site_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='PHONE'
AND owner_table_id = lv_loc_party_site_id --Party_site_id
AND phone_number = rec_sites_phone.phone_number;
EXCEPTION WHEN OTHERS THEN
lv_site_contact_point_id := NULL;
END;
ELSE
lv_site_comm_validate_flag := 'N';
lv_site_comm_validate_msg := 'Phone Number should not be NULL';
END IF;
END IF; --validation phone number
-------------Validate Email Type-----------------------
IF rec_sites_phone.contact_point_type = 'EMAIL' THEN
IF rec_sites_phone.email_address IS NOT NULL
AND rec_sites_phone.email_format IS NOT NULL THEN
BEGIN
SELECT contact_point_id
INTO lv_site_contact_point_id
FROM hz_contact_points
WHERE contact_point_type ='EMAIL'
AND owner_table_id = lv_loc_party_site_id --Party_site_id
AND email_address = rec_sites_phone.email_address;
EXCEPTION WHEN OTHERS THEN
lv_site_contact_point_id := NULL;
END;
ELSE
lv_site_comm_validate_flag := 'N';
lv_site_comm_validate_msg := 'Email Address and EmailFormat should not be NULL';
END IF;
END IF; --validation Email Type
--Update Site communication staging table--------------
UPDATE xxmtz_ar_cstmr_site_ph_stg_t
SET v_cust_site_phone_id = lv_site_contact_point_id,
api_msg = lv_site_comm_validate_msg
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_sites_phone.contact_point_type;
-- --------------------------------------------------------------
END LOOP; --8. END Customer Sites Communication cursor
-- --------------------------------------------------------------
-- 9. Cursor Starts - Customer Sites Contacts
-- --------------------------------------------------------------
FOR rec_sites_con IN cur_sites_con (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
lv_site_cont_party_id := NULL;
lv_site_org_cont_id := NULL;
lv_site_acct_role_id := NULL;
IF rec_sites_con.last_name IS NOT NULL THEN
BEGIN
SELECT hp_per.party_id contact_party_id
,hz_rel.party_id org_contact_Rel_id
,hcar.cust_account_role_id
INTO lv_site_cont_party_id,lv_site_org_cont_id,lv_site_acct_role_id
FROM hz_parties hp_per,
hz_relationships hz_rel,
hz_parties hp,
hz_cust_account_roles hcar
WHERE hp_per.party_type = 'PERSON'
AND hp_per.party_id = hz_rel.subject_id
AND hp.party_id = hz_rel.object_id
AND hcar.party_id = hz_rel.party_id
AND hp_per.person_last_name = rec_sites_con.last_name
AND hcar.cust_account_id = lv_cust_account_id -- customer account id
AND hcar.cust_acct_site_id = lv_cust_acct_site_id ; -- customer site account id
EXCEPTION WHEN OTHERS THEN
lv_site_cont_party_id := NULL;
lv_site_org_cont_id := NULL;
lv_site_acct_role_id := NULL;
END;
IF lv_contact_party_id IS NOT NULL THEN
UPDATE xxmtz_ar_cstmr_site_con_stg_t
SET v_cust_site_contact_id = lv_site_cont_party_id,
v_org_contact_id = lv_site_org_cont_id,
v_rel_party_id = lv_site_acct_role_id,
api_msg = 'Site Contact Person is already created'
WHERE customer_number = rec_header.customer_number
AND last_name = rec_sites_con.last_name;
END IF;
END IF;
-----------------------------------------------------------------
-- 10.Cursor Starts - Customer Sites Contact Communications
-- --------------------------------------------------------------
FOR rec_sites_con_phone IN cur_sites_con_phone (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_contact_point_id :=NULL;
BEGIN
SELECT contact_point_id
INTO lv_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = lv_site_org_cont_id; --Site org contact id
EXCEPTION WHEN OTHERS THEN
lv_contact_point_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_si_con_ph_stg_t
SET v_cust_site_contact_phone_id = lv_site_cont_party_id
WHERE contact_id = rec_sites_con_phone.contact_id
AND customer_number = rec_header.customer_number
AND phone_number = rec_sites_con_phone.phone_number;
-- --------------------------------------
END LOOP; --10. End Cursor - Customer Sites Contact Communications
-- --------------------------------------------------------------
-- 11.Cursor Starts - Cursor Starts - Customer Sites Contact Roles
-- --------------------------------------------------------------
FOR rec_sites_con_role IN cur_sites_con_role (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_contact_role_id :=NULL;
BEGIN
SELECT responsibility_id
INTO lv_contact_role_id
FROM hz_role_responsibility
WHERE cust_account_role_id = lv_site_acct_role_id; --Site Account Role id
EXCEPTION WHEN OTHERS THEN
lv_contact_role_id :=NULL;
END;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_si_role_stg_t
SET v_cust_site_contact_role_id = lv_contact_role_id
WHERE contact_id = rec_sites_con_role.contact_id
AND customer_number = rec_header.customer_number;
-- ---------------------------------------------------------------------
END LOOP; --11. End Cursor - Customer Header Contacts Communication
-- ---------------------------------------------------------------------
END LOOP; --9.END - Customer Sites Contacts Cursor
-- ----------------------------------------------------
END LOOP; --6.END Customer Location cursor
-- --------------------------------------------
END LOOP; --1.END Header Cursor
-- --------------------------------------
COMMIT;
display_message('log','*******END Validate Procedure**************');
EXCEPTION WHEN ex_lookup_setup_err THEN
x_retcode := 2;
display_message('log', ' AR LookUP Setup missing');
WHEN ex_header_total THEN
x_retcode := 1;
display_message('log','No Records in the Staging Table to process'|| SQLERRM);
END xxmtz_ar_cstmr_cnv_val_prc;
--Upload Procedure
PROCEDURE xxmtz_ar_cstmr_cnv_load_prc (x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_cust_number IN VARCHAR2 )
IS
-- --------------------------------------------------------------
-- Cursor Declaration
-- --------------------------------------------------------------
--Header Account
CURSOR cur_header IS
SELECT *
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE valid_status IN ('VALIDATED', 'PROCESSFAILED')
AND customer_number = NVL(p_cust_number,customer_number) --'TESTASP'
ORDER BY party_id, customer_number;
--Header Phone
CURSOR cur_header_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact
CURSOR cur_header_contact (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE customer_number = p_customer_number;
-- Header Contact Phone
CURSOR cur_header_con_phone ( p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Roles
CURSOR cur_header_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Sites Location
CURSOR cur_sites_location (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_location_stg_t
WHERE customer_number = p_customer_number;
-- Sites Use
CURSOR cur_sites_use (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER,
p_site_use IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number
AND site_use_code = p_site_use
ORDER BY site_use_code;
-- Sites Phone
CURSOR cur_sites_phone (p_customer_number IN VARCHAR2)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE customer_number = p_customer_number;
-- Sites Contact
CURSOR cur_sites_con (p_customer_number IN VARCHAR2,
p_address_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE address_id = p_address_id
AND customer_number = p_customer_number;
-- Sites Contact Phone
CURSOR cur_sites_con_phone (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- Header Contact Role
CURSOR cur_sites_con_role (p_customer_number IN VARCHAR2,
p_contact_id IN NUMBER)
IS
SELECT *
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE contact_id = p_contact_id
AND customer_number = p_customer_number;
-- -----------------------------
--API Record Types
-- ----------------------------
p_organization_rec apps.hz_party_v2pub.organization_rec_type;
p_cust_account_rec apps.hz_cust_account_v2pub.cust_account_rec_type;
p_customer_profile_rec apps.hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_profile_amt_rec_type apps.hz_customer_profile_v2pub.cust_profile_amt_rec_type;
p_location_rec apps.hz_location_v2pub.location_rec_type;
p_party_site_rec apps.hz_party_site_v2pub.party_site_rec_type;
p_contact_point_rec apps.hz_contact_point_v2pub.contact_point_rec_type;
p_edi_rec apps.hz_contact_point_v2pub.edi_rec_type;
p_emailv_rec apps.hz_contact_point_v2pub.email_rec_type;
p_phone_rec apps.hz_contact_point_v2pub.phone_rec_type;
p_telex_rec apps.hz_contact_point_v2pub.telex_rec_type;
p_web_rec apps.hz_contact_point_v2pub.web_rec_type;
p_person_rec apps.hz_party_v2pub.person_rec_type;
p_org_contact_rec apps.hz_party_contact_v2pub.org_contact_rec_type;
p_org_contact_role_rec apps.hz_party_contact_v2pub.org_contact_role_rec_type;
p_h_cr_cust_acc_role_rec apps.hz_cust_account_role_v2pub.cust_account_role_rec_type;
p_role_responsibility_rec apps.hz_cust_account_role_v2pub.role_responsibility_rec_type;
p_cust_acct_site_rec apps.hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_cust_site_use_cm_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_bill_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_ship_rec apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cr_cust_acc_role_rec apps.hz_cust_account_role_v2pub.cust_account_role_rec_type;
--------------------------------------------------
x_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
x_account_number hz_cust_accounts.account_number%TYPE;
x_party_id hz_parties.party_id%TYPE;
x_party_number hz_parties.party_number%TYPE;
x_profile_id hz_customer_profiles.cust_account_profile_id%TYPE;
x_cust_acct_profile_amt_id hz_cust_profile_amts.cust_acct_profile_amt_id%TYPE;
x_location_id hz_locations.location_id%TYPE;
x_party_site_id hz_party_sites.party_site_id%TYPE;
x_party_site_number hz_party_sites.party_site_number%TYPE;
x_contact_point_id hz_contact_points.contact_point_id%TYPE;
x_org_contact_id hz_org_contacts.org_contact_id%TYPE;
x_party_relv_id hz_parties.party_id%TYPE;
x_cust_account_role_id hz_cust_accounts.cust_account_id%TYPE;
x_responsibility_id hz_parties.party_id%TYPE;
x_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
x_site_use_id hz_cust_site_uses_all.site_use_id%TYPE;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
-------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
--User Define Exceptions
NO_PARTY_ID EXCEPTION ;
-- -----------------------------------------------------------------------------
lv_org_id NUMBER :=p_org_id;
lv_party_id hz_parties.party_id%TYPE := NULL;
lv_party_number hz_parties.party_number%TYPE := NULL;
lv_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
lv_profile_id hz_organization_profiles.organization_profile_id%TYPE := NULL;
lv_cust_acct_profile_amt_id hz_cust_profile_amts.cust_acct_profile_amt_id%TYPE := NULL;
lv_cust_account_profile_id hz_customer_profiles.cust_account_profile_id%TYPE := NULL;
lv_header_location_id hz_locations.location_id%TYPE := NULL;
lv_header_phone_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_org_contact_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_header_con_party_id hz_parties.party_id%TYPE := NULL;
lv_header_acc_role_party_id hz_parties.party_id%TYPE := NULL;
lv_header_account_role_id hz_cust_account_roles.cust_account_role_id%TYPE:= NULL;
lv_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE := NULL;
lv_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE := NULL;
lv_site_phone_id hz_contact_points.contact_point_id%TYPE:= NULL;
lv_site_emailv_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_site_contact_phone_id hz_contact_points.contact_point_id%TYPE := NULL;
lv_site_contact_role_id hz_org_contact_roles.org_contact_role_id%TYPE:= NULL;
lv_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE:= NULL;
lv_site_location_id hz_locations.location_id%TYPE := NULL;
lv_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
lv_bill_to_site_use_id hz_cust_site_uses_all.site_use_id%TYPE:= NULL;
lv_ship_to_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
lv_cm_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
lv_sites_con_party_id hz_parties.party_id%TYPE := NULL;
lv_site_acc_role_party_id hz_parties.party_id%TYPE := NULL;
lv_site_account_role_id hz_cust_account_roles.cust_account_role_id%TYPE:= NULL;
lv_api_org_msg VARCHAR2 (4000) :=NULL;
lv_api_header_phone_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_phone_msg VARCHAR2 (4000):=NULL;
lv_api_header_con_role_msg VARCHAR2 (4000):=NULL;
lv_api_location_msg VARCHAR2 (4000):=NULL;
lv_api_site_use_msg VARCHAR2 (4000):=NULL;
lv_api_site_phone_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_phone_msg VARCHAR2 (4000):=NULL;
lv_api_site_con_role_msg VARCHAR2 (4000):=NULL;
lv_message_out VARCHAR2 (4000);
lv_site_location_flag VARCHAR2 (1);
lv_sites_con_party_flag VARCHAR2 (1);
lv_site_use_count NUMBER;
lv_cust_process_flag VARCHAR2 (1);
lv_site_acc_role_party_flag VARCHAR2 (1);
BEGIN
FND_GLOBAL.APPS_INITIALIZE(user_id => fnd_global.user_id,resp_id =>fnd_global.resp_id,resp_appl_id =>222);
MO_GLOBAL.INIT ('AR');
MO_GLOBAL.SET_POLICY_CONTEXT ('S', lv_org_id);
display_message('log','begin');
FOR rec_header IN cur_header --Start HeaderLoop
LOOP
BEGIN
lv_api_org_msg := NULL;
lv_party_id := NULL;
lv_header_location_id := NULL;
lv_cust_process_flag := 'Y';
display_message('log',' Processing Customer Number :'||rec_header.customer_number);
IF rec_header.v_cust_account_id IS NULL THEN --IF CheckCustAcct
p_cust_account_rec.status := rec_header.status_party;
p_cust_account_rec.account_name := rec_header.customer_name;
p_cust_account_rec.account_number := rec_header.customer_number; --CustomerNumber
p_cust_account_rec.created_by_module := gc_created_by_module;
p_cust_account_rec.customer_type := rec_header.customer_type;
p_cust_account_rec.attribute10 := rec_header.attribute10;
p_cust_account_rec.attribute11 := rec_header.attribute11;
p_cust_account_rec.attribute12 := rec_header.attribute12;
-- p_cust_account_rec.customer_class_code := rec_header.customer_class_code;
p_cust_account_rec.tax_header_level_flag := rec_header.tax_header_level_flag;
p_cust_account_rec.arrivalsets_include_lines_flag := rec_header.arrivalsets_include_lines_flag;
p_cust_account_rec.ship_sets_include_lines_flag := rec_header.ship_sets_include_lines_flag;
p_cust_account_rec.sched_date_push_flag := rec_header.sched_date_push_flag;
p_organization_rec.organization_name := rec_header.customer_name;
p_organization_rec.duns_number_c := rec_header.duns_number_c;
p_cust_account_rec.orig_system_reference := rec_header.customer_number; --Orig system Reference
p_cust_account_rec.orig_system := gc_orig_system; --Orig System
--Checking Sic Code
IF rec_header.sic_code IS NOT NULL THEN --SIC Code
p_organization_rec.sic_code := rec_header.sic_code;
IF rec_header.sic_code_type IS NULL THEN --Sic Code Type
p_organization_rec.sic_code_type := 'OTHER';
ELSE
p_organization_rec.sic_code_type := rec_header.sic_code_type;
END IF;
END IF;
p_organization_rec.created_by_module := gc_created_by_module;
IF rec_header.gsa_indicator IS NOT NULL THEN --GSA Indicator
p_organization_rec.gsa_indicator_flag := rec_header.gsa_indicator;
END IF;
p_customer_profile_rec.collector_id := rec_header.collector_id;
p_customer_profile_rec.status := rec_header.status_profile;
p_customer_profile_rec.profile_class_id := rec_header.customer_profile_class_id;
p_customer_profile_rec.credit_checking := rec_header.credit_checking;
p_customer_profile_rec.tolerance := rec_header.tolerance;
--validation between -100 and 100
p_customer_profile_rec.discount_terms := rec_header.discount_terms;
p_customer_profile_rec.dunning_letters := rec_header.dunning_letters;
p_customer_profile_rec.interest_charges := rec_header.interest_charges;
--p_customer_profile_rec.send_statements := rec_header.statements;
p_customer_profile_rec.credit_balance_statements := rec_header.credit_balance_statements;
p_customer_profile_rec.credit_hold := rec_header.credit_hold;
p_customer_profile_rec.standard_terms := rec_header.standard_terms;
--validation against ar_terms
p_customer_profile_rec.override_terms := rec_header.override_terms;
IF rec_header.interest_period_days IS NOT NULL --Interes Period
THEN
p_customer_profile_rec.interest_charges := 'Y';
p_customer_profile_rec.charge_on_finance_charge_flag := 'Y';
p_customer_profile_rec.interest_period_days :=
rec_header.interest_period_days;
END IF;
p_customer_profile_rec.auto_rec_incl_disputed_flag := rec_header.auto_rec_incl_disputed_flag;
p_customer_profile_rec.tax_printing_option := rec_header.tax_printing_option;
-- validation tax_printing_option lookup
p_customer_profile_rec.cons_inv_flag := rec_header.cons_inv_flag;
display_message('log',' Calling HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT API...');
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_organization_rec,
p_customer_profile_rec,
'F',
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log',' Customer Account Status :'||x_return_status||' Account ID :'||NVL(x_cust_account_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' 1.Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' 2.Error Message :'||lv_message_out);
END IF;
ELSE
lv_party_id := x_party_id;
lv_party_number := x_party_number;
lv_cust_account_id := x_cust_account_id;
lv_profile_id := x_profile_id;
lv_api_org_msg := ('PartyId: '||x_party_id||' AccountID: '||x_cust_account_id||' Account# :'||x_account_number||' ProfileID :'||x_profile_id);
display_message('log',lv_api_org_msg);
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_party_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
v_party_id = lv_party_id ,
v_cust_account_id = lv_cust_account_id,
v_profile_id = lv_profile_id
WHERE customer_number = rec_header.customer_number;
END;
ELSE
display_message('log',' Customer Account Is already Exists PartyID:'||rec_header.v_party_id||' AccountID :'||rec_header.v_cust_account_id);
lv_party_id := rec_header.v_party_id;
--lv_party_number := rec_header.v_cust_account_id;
lv_cust_account_id := rec_header.v_cust_account_id;
lv_profile_id := rec_header.v_profile_id;
lv_api_org_msg := ('PartyId: '||rec_header.v_party_id||' AccountID: '||rec_header.v_cust_account_id||' ProfileID :'||rec_header.v_profile_id);
END IF; --End CheckCustAcct
--If Party is not exists then come out of loop
IF lv_party_id IS NULL THEN
RAISE NO_PARTY_ID;
END IF;
------**END Create Cust Account**-------------------
/* BEGIN
SELECT cust_account_profile_id
INTO lv_cust_account_profile_id --Profile Amount id
FROM hz_customer_profiles
WHERE cust_account_id = lv_cust_account_id;
EXCEPTION WHEN OTHERS THEN
lv_cust_account_profile_id := NULL;
END;
IF rec_header.currency_code IS NOT NULL --IF CheckProfileAmt.
AND rec_header.v_cust_acct_profile_amt_id IS NULL
--AND rec_header.trx_credit_limit IS NOT NULL --Tax Credit Limit
--AND rec_header.overall_credit_limit IS NOT NULL --Over all Credit Limit
AND lv_cust_account_profile_id IS NOT NULL THEN
p_cust_profile_amt_rec_type.cust_account_id := lv_cust_account_id;
p_cust_profile_amt_rec_type.cust_account_profile_id := lv_cust_account_profile_id;
p_cust_profile_amt_rec_type.currency_code := rec_header.currency_code;
--Validation against fnd_currencies
p_cust_profile_amt_rec_type.trx_credit_limit := rec_header.trx_credit_limit;
p_cust_profile_amt_rec_type.overall_credit_limit := rec_header.overall_credit_limit;
p_cust_profile_amt_rec_type.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUST_PROFILE_AMT...');
hz_customer_profile_v2pub.create_cust_profile_amt
('T',
'T',
p_cust_profile_amt_rec_type,
x_cust_acct_profile_amt_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Profile amt Status :'||x_return_status||' Profile Amt ID :'||NVL(x_cust_acct_profile_amt_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_org_msg := lv_api_org_msg||' : '||('CustProfileAmtId :'||x_cust_acct_profile_amt_id);
display_message('log','CustProfileAMT is Created :'||x_cust_acct_profile_amt_id);
lv_cust_acct_profile_amt_id := x_cust_acct_profile_amt_id;
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
---Update Profile Amt
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_party_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
v_cust_acct_profile_amt_id = lv_cust_acct_profile_amt_id
WHERE customer_number = rec_header.customer_number;
END;
END IF; --End CheckProfileAmt
-----------**END Create ProfileAMT**--------------------
*/
BEGIN --Begin HeaderLocation
IF rec_header.n_location_id IS NULL THEN
p_location_rec.country := rec_header.country; --'US';
p_location_rec.address1 := rec_header.address1;
p_location_rec.address2 := rec_header.address2;
p_location_rec.address3 := rec_header.address3;
p_location_rec.city := rec_header.city;
p_location_rec.postal_code := rec_header.postal_code;
p_location_rec.state := rec_header.state;
p_location_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_LOCATION_V2PUB.CREATE_LOCATION...');
hz_location_v2pub.create_location ('T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Header Location Status :'||x_return_status||' Locaion Id:'||NVL(x_location_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_header_location_id:= x_location_id;
lv_api_org_msg := lv_api_org_msg||' : '||('CustomerHeaderLocationId :'||x_location_id);
display_message('log',lv_api_org_msg);
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(lv_header_location_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
n_location_id = lv_header_location_id
WHERE customer_number = rec_header.customer_number;
END;
ELSE
display_message('log','Location Is already created location ID:'||rec_header.n_location_id);
lv_header_location_id := rec_header.n_location_id;
END IF;
END;
-------**End HeaderLocation**------------
BEGIN --Begin HeaderPartySite
IF rec_header.n_header_site_id IS NULL THEN
p_party_site_rec.party_id := lv_party_id;
p_party_site_rec.location_id := lv_header_location_id;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE...');
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Header Party Status :'||x_return_status||' Party Site Id:'||NVL(x_party_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_org_msg := lv_api_org_msg||' : '||('CustomerHeaderPartySite :'||x_party_site_id);
display_message('log','CustHeaderLocation is Created');
END IF;
lv_api_org_msg := lv_api_org_msg||' : '||lv_message_out;
BEGIN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = DECODE(NVL(x_party_site_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_org_msg,
n_header_site_id = x_party_site_id
WHERE customer_number = rec_header.customer_number;
END;
END IF;
END;
-------**End HeaderPartySite**-------------
-- --------------------------------------------------------------
-- 2.Cursor Starts - Customer Header Communication
-- --------------------------------------------------------------
FOR rec_header_phone IN cur_header_phone (rec_header.customer_number)LOOP --Start HeaderCommunication
display_message('log','In Customer Communication Creation of type :'||rec_header_phone.contact_point_type);
lv_api_header_phone_msg := NULL;
lv_header_phone_id := NULL;
--lv_header_phone_flag := 'N';
IF rec_header_phone.v_contact_point_id IS NULL
AND lv_party_id IS NOT NULL THEN --Check ContactPoint
p_contact_point_rec.contact_point_type := rec_header_phone.contact_point_type; --Contact Type PHONE or EMAIL
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := lv_party_id; --Party Id
p_contact_point_rec.primary_flag := rec_header_phone.primary_flag;
p_contact_point_rec.created_by_module := gc_created_by_module;
IF rec_header_phone.contact_point_type = 'PHONE' THEN --Phone
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
-- p_contact_point_rec.status := rec_header_phone.status;
p_phone_rec.phone_area_code := rec_header_phone.area_code;
p_phone_rec.phone_country_code := rec_header_phone.country_code;
p_phone_rec.phone_number := rec_header_phone.phone_number;
p_phone_rec.phone_line_type := rec_header_phone.phone_type;
END IF; --End Phone Contact
IF rec_header_phone.contact_point_type = 'EMAIL'
AND rec_header_phone.email_address IS NOT NULL THEN --Email
-- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.status := rec_header_phone.status;
p_emailv_rec.email_format := rec_header_phone.email_format;
p_emailv_rec.email_address := rec_header_phone.email_address;
--p_emailv_rec.url := rec_header_phone.url;
p_phone_rec.phone_line_type := rec_header_phone.phone_type;
END IF;
display_message('log','Calling..HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header Contact Status :'||x_return_status||' Contact PointID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_phone_msg := lv_api_org_msg||' : '||('HeaderContactId :'||x_contact_point_id);
display_message('log','Header Contact is Created ID :'||x_contact_point_id);
lv_header_phone_id := x_contact_point_id;
-- lv_header_phone_flag := 'Y';
END IF;
lv_api_header_phone_msg := lv_api_header_phone_msg||' : '||lv_message_out;
--Update Staging Table----------------------------
UPDATE xxmtz_ar_cstmr_phone_stg_t
SET v_contact_point_id = lv_header_phone_id,
-- v_contact_point_flag = lv_header_phone_flag,
api_msg = lv_api_header_phone_msg,
valid_status = DECODE(NVL(lv_header_phone_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND contact_point_type = rec_header_phone.contact_point_type
AND customer_number = rec_header.customer_number;
-- -------------------------------------------------------------
ELSE --Header Contact already exists
display_message('log','Header Contact Point is already Created. ContactID :'||rec_header_phone.v_contact_point_id);
END IF; ----End Check ContactPoint
END LOOP; --2.End HeaderCommunication
-- -----------------------------------------------------
-- 3.Cursor Starts - Customer Header Contacts
-- --------------------------------------------------------------
FOR rec_header_contact IN cur_header_contact (rec_header.customer_number) LOOP --Start CustomerHeaderContacts
display_message('log',' In recd_header_contact');
lv_header_con_party_id := NULL;
lv_header_acc_role_party_id := NULL;
lv_header_account_role_id := NULL;
lv_api_header_con_msg := NULL;
lv_org_contact_id := NULL;
IF rec_header_contact.last_name IS NOT NULL --Check LastName
AND rec_header_contact.v_contact_party_id IS NULL THEN
p_person_rec.person_first_name := rec_header_contact.first_name;
p_person_rec.person_last_name := rec_header_contact.last_name;
p_person_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling HZ_PARTY_V2PUB.CREATE_PERSON...');
hz_party_v2pub.create_person ('T',
p_person_rec,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header Contact Person Status :'||x_return_status||' Contact PartyID :'||NVL(x_party_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('HeaderContactPartyId :'||x_party_id);
display_message('log','Header Contact Person is Created PartyID :'||x_party_id);
lv_header_con_party_id := x_party_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
-- -------------------------------------------------------------------
-- Create Header Contact Relationship
-- -------------------------------------------------------------------
IF rec_header_contact.v_org_contact_id IS NULL THEN
p_org_contact_rec.party_rel_rec.subject_id := lv_header_con_party_id;
p_org_contact_rec.party_rel_rec.object_id := lv_party_id;
p_org_contact_rec.contact_number := rec_header_contact.contact_number;
p_org_contact_rec.created_by_module := gc_created_by_module;
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.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := rec_header_contact.start_date;
--p_org_contact_rec.department_code := 'ACCOUNTING';
--p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
--p_org_contact_rec.decision_maker_flag := 'Y';
--p_org_contact_rec.job_title_code := ;
--p_org_contact_rec.status := rec_sites.status_contact;
--p_org_contact_rec.party_site_id := 26211;
display_message('log','Calling HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT...');
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
x_org_contact_id,
x_party_relv_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Org Contact status :'||x_return_status||' OrgContact ID :'||NVL(x_org_contact_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('OrgContactId :'||x_org_contact_id);
display_message('log','Org Contact is Created OrgcontID :'||x_org_contact_id||' Contact PartyID:'||x_party_id);
lv_header_acc_role_party_id := x_party_id;
lv_org_contact_id := x_org_contact_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
END IF;
-- ------------------------------------------------------------------
--End Org Contact
-- -------------------------------------------------------------------
-- Create Header Contact Account Role
-- -------------------------------------------------------------------
IF rec_header_contact.v_rel_party_id IS NULL THEN
p_h_cr_cust_acc_role_rec.party_id := lv_header_acc_role_party_id;
p_h_cr_cust_acc_role_rec.cust_account_id := lv_cust_account_id;
--p_h_cr_cust_acc_role_rec.primary_flag := 'Y';
p_h_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_h_cr_cust_acc_role_rec.status := rec_header_contact.status;
p_h_cr_cust_acc_role_rec.created_by_module:= gc_created_by_module;
display_message('log','Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE...');
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_h_cr_cust_acc_role_rec,
x_cust_account_role_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Acct Role status :'||x_return_status||' AcctRoleID :'||NVL(x_cust_account_role_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_msg := lv_api_header_con_msg||' : '||('OrgRoleId :'||x_cust_account_role_id);
display_message('log','Account RoleID :'||x_cust_account_role_id);
lv_header_account_role_id := x_cust_account_role_id;
END IF;
lv_api_header_con_msg := lv_api_header_con_msg||' : '||lv_message_out;
--Update Staging Table----------------------------
UPDATE xxmtz_ar_cstmr_contact_stg_t
SET v_contact_party_id = lv_header_con_party_id,
v_org_contact_id = lv_header_acc_role_party_id,
v_rel_party_id = lv_header_account_role_id,
api_msg = lv_api_header_con_msg ,
valid_status = DECODE(NVL(lv_header_con_party_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE last_name = rec_header_contact.last_name
AND customer_number = rec_header.customer_number;
END IF;
ELSE
display_message('log','Contact last Name is NULL or Party is already exists'||' : '||rec_header_contact.v_contact_party_id);
lv_header_acc_role_party_id := rec_header_contact.v_org_contact_id ;
lv_header_account_role_id := rec_header_contact.v_rel_party_id;
END IF;--Check LastName
-----------------------------------------------------------------
-- 4.Cursor Starts - Customer Header Contacts Communication
-- --------------------------------------------------------------
FOR rec_header_con_phone IN cur_header_con_phone (rec_header.customer_number,
rec_header_contact.contact_id) --Start HeaderContactsCommunication
LOOP
lv_api_header_con_phone_msg := NULL;
display_message('log','In cur_header_con_phone ');
IF rec_header_con_phone.v_phone_id IS NULL THEN --Check PhoneId
display_message('log','IN cur_header_con_phone Aacc_role_party_id :'||lv_header_acc_role_party_id);
p_contact_point_rec.owner_table_id := lv_header_acc_role_party_id;
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.primary_flag := rec_header_con_phone.primary_flag;
--p_contact_point_rec.contact_point_purpose := 'BUSINESS';
--p_contact_point_rec.status := rec_header_con_phone.status;
p_phone_rec.phone_area_code := rec_header_con_phone.area_code;
p_phone_rec.phone_country_code := rec_header_con_phone.country_code;
p_phone_rec.phone_number := rec_header_con_phone.phone_number;
p_phone_rec.phone_line_type := rec_header_con_phone.phone_type;
p_contact_point_rec.created_by_module := gc_created_by_module;
display_message('log', 'Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header ContactComm status :'||x_return_status||' ContactID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_cust_process_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_phone_msg := lv_api_header_con_phone_msg||' : '||('ContactCommunicationID :'||x_contact_point_id);
display_message('log','ContactComminicationID :'||x_cust_account_role_id);
END IF;
lv_api_header_con_phone_msg := lv_api_header_con_phone_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_phone_stg_t
SET v_phone_id = x_contact_point_id,
api_msg = lv_api_header_con_phone_msg,
valid_status = DECODE(NVL(x_contact_point_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_header_contact.contact_id
AND last_name = rec_header_con_phone.last_name;
ELSE
display_message('log','No customer con phone stg record found');
END IF; --End Check PhoneId
-- -----------------------------------------------------------------------------
END LOOP; --End HeaderContactsCommunication
--End 4.Customer Header Contacts Communication
-- --------------------------------------------------------------
-- --------------------------------------------------------------
-- 5.Cursor Starts - Customer Header Contacts Roles
-- --------------------------------------------------------------
FOR rec_header_con_role IN cur_header_con_role (rec_header.customer_number,
rec_header_contact.contact_id) LOOP --Start CustomerHeaderContactsRoles
lv_api_header_con_role_msg := NULL;
display_message('log','In cur_header_con_role ');
IF rec_header_con_role.v_contact_role_id IS NULL THEN
p_role_responsibility_rec.responsibility_type := rec_header_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := lv_header_account_role_id;
p_role_responsibility_rec.primary_flag := rec_header_con_role.primary_flag;
p_role_responsibility_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_ROLE_RESPONSIBILITY...');
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Header ContactRole status :'||x_return_status||' ResposibilityID :'||NVL(x_responsibility_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_header_con_role_msg := lv_api_header_con_role_msg||' : '||('RoleRespID :'||x_responsibility_id);
display_message('log','RoleResponsibilityID :'||x_responsibility_id);
END IF;
lv_api_header_con_role_msg := lv_api_header_con_role_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_con_role_stg_t
SET v_contact_role_id = x_responsibility_id,
api_msg = lv_api_header_con_role_msg,
valid_status = DECODE(NVL(x_responsibility_id,0),0,'PROCESSFAILED','PROCESSED')
WHERE customer_number = rec_header.customer_number
AND Contact_id = rec_header_con_role.Contact_id;
ELSE
display_message('log',' No Customer Contact Roles Found');
END IF;
-- ----------------------------------------------------
END LOOP; --End CustomerHeaderContactsRoles
--End 5.Customer Header Contacts Roles
-- ---------------------------------------------------------------
END LOOP; --End CustomerHeaderContacts
--End 3.Customer Header Contacts
-- --------------------------------------------------------------
-- --------------------------------------------------------------
-- 6. Cursor Starts - Customer Location
-- --------------------------------------------------------------
FOR rec_sites_location IN cur_sites_location (rec_header.customer_number) LOOP
--lv_api_location_msg := NULL;
lv_site_location_id := NUll;
lv_party_site_id := NULL;
lv_cust_acct_site_id := NULL;
lv_site_location_flag := NULL;
--lv_party_site_flag := 'N';
--lv_cust_acct_site_flag := 'N';
IF rec_sites_location.v_party_location_id IS NULL THEN --Location
display_message('log','Location:'||rec_sites_location.address1);
p_location_rec.country := rec_sites_location.country;
p_location_rec.address1 := rec_sites_location.address1;
p_location_rec.address2 := rec_sites_location.address2;
p_location_rec.address3 := rec_sites_location.address3;
p_location_rec.city := rec_sites_location.city;
p_location_rec.postal_code := rec_sites_location.postal_code;
p_location_rec.state := rec_sites_location.state;
p_location_rec.province := rec_sites_location.province;
p_location_rec.attribute1 := rec_sites_location.attribute1;
p_location_rec.sales_tax_geocode := rec_sites_location.sales_tax_geocode;
p_location_rec.sales_tax_inside_city_limits := rec_sites_location.sales_tax_inside_city_limits;
p_location_rec.created_by_module := gc_created_by_module;
display_message('log','Calling HZ_LOCATION_V2PUB.CREATE_LOCATION...');
hz_location_v2pub.create_location ('T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Location status :'||x_return_status||' LocationID :'||NVL(x_location_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_site_location_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_org_msg||' : '||('LocationID :'||x_location_id);
display_message('log','New LocationID :'||x_location_id);
lv_site_location_flag := 'Y';
lv_site_location_id := x_location_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
lv_site_location_id := rec_sites_location.v_party_location_id;
display_message('log','Location is Already exits Loaction ID: '||rec_sites_location.v_party_location_id);
END IF; --End Location
-- -------------------------------------------------------------------
-- Create Party Site
-- -------------------------------------------------------------------
IF rec_sites_location.v_party_site_id IS NULL THEN
--display_message('log', 'Party ID:' || lv_party_id);
display_message('log', 'Create site id with Location Id:' || lv_site_location_id);
p_party_site_rec.party_id := lv_party_id;
p_party_site_rec.location_id := lv_site_location_id;
p_party_site_rec.identifying_address_flag := rec_sites_location.identifying_address_flag;
p_party_site_rec.created_by_module := gc_created_by_module;
--p_party_site_rec.status := 'Y';
--p_party_site_rec.orig_system_reference := rec_sites_location.site_number;
--p_party_site_rec.orig_system := gc_orig_system;
display_message('log','Calling HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE...');
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Party Site status :'||x_return_status||' PartySiteID :'||NVL(x_party_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_site_location_flag := 'N';
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_location_msg||' : '||('PartySiteID :'||x_party_site_id);
display_message('log','New PartySiteID :'||x_party_site_id);
--lv_party_site_flag := 'Y';
lv_site_location_flag := 'Y';
lv_party_site_id := x_party_site_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
display_message('log','Party Site is Already Created :'||rec_sites_location.v_party_site_id);
lv_party_site_id := rec_sites_location.v_party_site_id;
END IF;
-- -------------------------------------------------------------------
-- Create Customer Account Site
-- -------------------------------------------------------------------
IF rec_sites_location.v_cust_site_id IS NULL THEN
p_cust_acct_site_rec.cust_account_id := lv_cust_account_id;
p_cust_acct_site_rec.party_site_id := lv_party_site_id;
--p_cust_acct_site_rec.org_id := lv_org_id;
p_cust_acct_site_rec.territory := rec_sites_location.territory;
p_cust_acct_site_rec.created_by_module := gc_created_by_module;
--p_cust_acct_site_rec.orig_system_reference := rec_sites_location.site_number;
--p_cust_acct_site_rec.orig_system := gc_orig_system;
display_message('log','Calling HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE...');
hz_cust_account_site_v2pub.create_cust_acct_site
('T',
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site status :'||x_return_status||' CustAcctSiteID :'||NVL(x_cust_acct_site_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_site_location_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_location_msg := lv_api_location_msg||' : '||('CustAcctSiteID :'||x_cust_acct_site_id);
display_message('log','New CustAcctSiteID :'||x_cust_acct_site_id);
--lv_cust_acct_site_flag := 'Y';
lv_site_location_flag := 'Y';
lv_cust_acct_site_id := x_cust_acct_site_id;
END IF;
lv_api_location_msg := lv_api_location_msg||' : '||lv_message_out;
ELSE
display_message('log',' Customer Account site already exists ACCTSiteID:'||rec_sites_location.v_cust_site_id);
lv_cust_acct_site_id := rec_sites_location.v_cust_site_id;
END IF;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_location_stg_t
SET valid_status = DECODE(NVL(v_cust_site_id,NVL(lv_cust_acct_site_id,0)),0,'PROCESSFAILED','PROCESSED'),
api_msg = api_msg||lv_api_location_msg,
v_party_location_id = lv_site_location_id ,
v_party_site_id = lv_party_site_id,
v_cust_site_id = lv_cust_acct_site_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id;
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose CM
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'CM')
LOOP
display_message('log','In Party Site Use CM old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
lv_api_site_use_msg := NULL;
lv_cm_site_use_id := NULL;
--lv_cm_site_use_flag :='N';
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
-- --------------------------------------------------------------
-- Create Customer Account Site Use
-- --------------------------------------------------------------
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','CM lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_cm_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_cm_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_cm_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_cm_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_cm_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_cm_rec.location := rec_sites_use.location;
p_cust_site_use_cm_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling CM CREATE_CUST_SITE_USE API...');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_cm_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code CM Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('CM SiteUseID :'||x_site_use_id);
display_message('log','CM PartySiteUseID :'||x_site_use_id);
--lv_cm_site_use_flag := 'Y';
lv_cm_site_use_id := x_site_use_id;
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_cm_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_cm_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id;
ELSE
display_message('log','*****No CrecitMemo(CM) Site Use Found *****');
END IF;
END LOOP; --End SiteUse CM
-- ---------------------------------------------------------------------
--END Customer Sites Business Purpose CM
-- --------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose BILL_TO
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'BILL_TO')
LOOP
--lv_bill_to_site_use_flag := 'N';
lv_api_site_use_msg := NULL;
lv_bill_to_site_use_id := NULL;
display_message('log','In Party Site Use BILL_TO old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
-- --------------------------------------------------------------
-- Create Customer Account Site Use
-- --------------------------------------------------------------
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','BILL_TO lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_bill_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_bill_rec.site_use_code := rec_sites_use.site_use_code; --BILL_TO site use code
p_cust_site_use_bill_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_bill_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_bill_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_bill_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_bill_rec.location := rec_sites_use.location;
--p_cust_site_use_rec.org_id := lv_org_id;
p_cust_site_use_bill_rec.created_by_module := gc_created_by_module;
--p_cust_site_use_rec.ship_sets_include_lines_flag := rec_sites_use.ship_sets_include_lines_flag;
--p_cust_site_use_rec.arrivalsets_include_lines_flag := rec_sites_use.arrivalsets_include_lines_flag;
--p_cust_site_use_rec.sched_date_push_flag := rec_sites_use.sched_date_push_flag;
p_customer_profile_rec.collector_id := rec_sites_use.collector_id;
--p_customer_profile_rec.status := rec_sites_use.status_cust_profile;
p_customer_profile_rec.profile_class_id := rec_sites_use.customer_profile_class_id;
p_customer_profile_rec.credit_checking := rec_sites_use.credit_checking;
p_customer_profile_rec.tolerance := rec_sites_use.tolerance;
p_customer_profile_rec.discount_terms := rec_sites_use.discount_terms;
p_customer_profile_rec.dunning_letters := rec_sites_use.dunning_letters;
p_customer_profile_rec.interest_charges := rec_sites_use.interest_charges;
p_customer_profile_rec.credit_balance_statements := rec_sites_use.credit_balance_statements;
p_customer_profile_rec.credit_hold := rec_sites_use.credit_hold;
p_customer_profile_rec.standard_terms := rec_sites_use.standard_terms;
p_customer_profile_rec.override_terms := rec_sites_use.override_terms;
p_customer_profile_rec.auto_rec_incl_disputed_flag := rec_sites_use.auto_rec_incl_disputed_flag;
p_customer_profile_rec.tax_printing_option := rec_sites_use.tax_printing_option;
p_customer_profile_rec.cons_inv_flag := rec_sites_use.cons_inv_flag;
display_message('log','Calling BILL_TO HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE.....');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_bill_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code BILL_TO Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('BILL_TO SiteUseID :'||x_site_use_id);
display_message('log','BILL_TO PartySiteUseID :'||x_site_use_id);
lv_bill_to_site_use_id := x_site_use_id;
-- lv_bill_to_site_use_flag := 'Y';
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_bill_to_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_bill_to_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND location = rec_sites_use.location;
ELSE
display_message('log','*****No Bill To Site Use Found *****');
END IF;
-- ---------------------------------------------------------------------
END LOOP; --7.End SiteUse BILL_TO --END Customer Sites Business Purpose BILL_TO
-- ---------------------------------------------------------------------
-- 7. Cursor Starts - Customer Sites Business Purpose SHIP_TO
-- --------------------------------------------------------------
FOR rec_sites_use IN cur_sites_use (rec_header.customer_number,
rec_sites_location.address_id,
'SHIP_TO')
LOOP
lv_api_site_use_msg := NULL;
lv_ship_to_site_use_id := NULL;
lv_bill_to_site_use_id := NULL;
display_message('log','In Party Site Use SHIP_TO old addressID :'||rec_sites_location.address_id||'status:'|| rec_sites_use.site_use_status);
BEGIN
SELECT COUNT (1)
INTO lv_site_use_count
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id
AND v_cust_site_use_id is NULL;
END;
BEGIN --Get Bill to Site use of Shipment location
SELECT v_cust_site_use_id
INTO lv_bill_to_site_use_id
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE site_use_code='BILL_TO'
AND customer_number = rec_header.customer_number
AND address_id = rec_sites_location.address_id
AND primary_flag ='Y';
EXCEPTION WHEN OTHERS THEN
lv_bill_to_site_use_id := NULL;
display_message('log','**NO BILL_TO_SITE_USE found for shipment Location :'||rec_sites_use.location);
END;
IF rec_sites_use.v_cust_site_use_id IS NULL AND lv_site_use_count > 0 THEN
display_message('log','Site Use Count :'||lv_site_use_count||' Party Site ID :'||lv_party_site_id);
display_message('log',' SHIP TO lv_cust_acct_site_id:'|| lv_cust_acct_site_id);
p_cust_site_use_ship_rec.cust_acct_site_id := lv_cust_acct_site_id;
p_cust_site_use_ship_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_ship_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_ship_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_ship_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_ship_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_ship_rec.location := rec_sites_use.location;
--p_cust_site_use_rec.org_id := lv_org_id;
IF rec_sites_use.bill_to_site_use_id IS NOT NULL THEN
p_cust_site_use_ship_rec.bill_to_site_use_id := lv_bill_to_site_use_id; --Bill site id
END IF;
p_cust_site_use_ship_rec.created_by_module := gc_created_by_module;
display_message('log','Calling SHIP_TO CREATE_CUST_SITE_USE API...');
hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_ship_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Cust Site Use code SHIP_TO Status:'||x_return_status||' SiteUseID :'||NVL(x_site_use_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_use_msg := lv_api_site_use_msg||' : '||('SHIP_TO SiteUseID :'||x_site_use_id);
display_message('log','SHIP_TO PartySiteUseID :'||x_site_use_id);
-- lv_ship_to_site_use_flag := 'Y';
lv_ship_to_site_use_id := x_site_use_id;
END IF;
lv_api_site_use_msg := lv_api_site_use_msg||' : '||lv_message_out;
--Update Staging Table
UPDATE xxmtz_ar_cstmr_site_use_stg_t
SET valid_status = DECODE(NVL(lv_ship_to_site_use_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_use_msg,
v_cust_site_use_id = lv_ship_to_site_use_id
WHERE site_use_code = rec_sites_use.site_use_code
AND customer_number = rec_sites_use.customer_number
AND address_id = rec_sites_use.address_id;
ELSE
display_message('log','*****No Bill To Site Use Found *****');
END IF;
-- ---------------------------------------------------------------------
END LOOP; -- 7.End SiteUse SHIP_TO
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
-- 8. Cursor Starts - Customer Sites Communication
-- --------------------------------------------------------------
FOR rec_sites_phone IN cur_sites_phone (rec_header.customer_number) --Start CustomerSitesCommunication
LOOP
display_message('log','In Site Phone');
lv_api_site_phone_msg := NULL;
IF rec_sites_phone.v_cust_site_phone_id IS NULL THEN
p_contact_point_rec.contact_point_type := rec_sites_phone.contact_point_type; --'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id := lv_party_site_id;
p_contact_point_rec.primary_flag := rec_sites_phone.primary_flag;
p_contact_point_rec.created_by_module := gc_created_by_module;
IF rec_sites_phone.contact_point_type = 'PHONE'
AND rec_sites_phone.phone_number IS NOT NULL THEN
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_phone_rec.phone_area_code := rec_sites_phone.area_code;
p_phone_rec.phone_country_code := rec_sites_phone.country_code;
p_phone_rec.phone_number := rec_sites_phone.phone_number;
p_phone_rec.phone_line_type := rec_sites_phone.phone_type;
-- p_contact_point_rec.status := rec_sites_phone.status;
END IF;
IF rec_sites_phone.contact_point_type = 'EMAIL'
AND rec_sites_phone.email_address IS NOT NULL THEN
--p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.status := rec_sites_phone.status;
p_emailv_rec.email_format := rec_sites_phone.email_format;
p_emailv_rec.email_address := rec_sites_phone.email_address;
END IF;
display_message('log','Calling EAMIL HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT...');
hz_contact_point_v2pub.create_contact_point ('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site Contact Status:'||x_return_status||' ContactPoinID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := 'Site ContactPoinID :'||x_contact_point_id;
display_message('log','Site ContactPoinID :'||x_contact_point_id);
lv_site_phone_id := x_contact_point_id;
--lv_site_phone_flag := 'Y';
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
--Update site Phone stg
UPDATE xxmtz_ar_cstmr_site_ph_stg_t
SET valid_status = DECODE(NVL(lv_site_phone_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_phone_msg,
v_cust_site_phone_id = lv_site_phone_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_phone.address_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; --8. End Customer Sites Communication
-- ---------------------------------------------------------------------
-- 9. Cursor Starts - Customer Sites Contacts
-- --------------------------------------------------------------
FOR rec_sites_con IN cur_sites_con (rec_header.customer_number,
rec_sites_location.address_id)
LOOP
display_message('log','IN Cur_sites_con');
lv_api_site_phone_msg := NULL;
lv_sites_con_party_id := NULL;
lv_site_acc_role_party_id := NULL;
lv_site_account_role_id := NULL;
--lv_site_account_role_flag := 'N';
lv_sites_con_party_flag := 'Y';
IF rec_sites_con.last_name IS NOT NULL --LastName
AND rec_sites_con.v_cust_site_contact_id IS NULL THEN
p_person_rec.person_first_name := rec_sites_con.first_name;
p_person_rec.person_last_name := rec_sites_con.last_name;
p_person_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling Site ContactPerson HZ_PARTY_V2PUB.CREATE_PERSON......');
hz_party_v2pub.create_person ('T',
p_person_rec,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Customer Sites Contact Person Status:'||x_return_status||' SiteContactPartyID :'||NVL(x_party_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('SiteContactPartyID :'||x_party_id);
lv_sites_con_party_id := x_party_id;
lv_sites_con_party_flag := 'Y';
display_message('log','SiteContactPartyID :'||x_party_id);
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
display_message('log','Last Name Should not be null Or Person is exists :'||rec_sites_con.v_cust_site_contact_id);
END IF; --End LastName
-- -------------------------------------------------------------------
-- Create Sites Contact Relationship
-- -------------------------------------------------------------------
IF rec_sites_con.v_org_contact_id IS NULL THEN
p_org_contact_rec.contact_number := rec_sites_con.contact_number;
p_org_contact_rec.created_by_module := gc_created_by_module;
p_org_contact_rec.party_rel_rec.subject_id := lv_sites_con_party_id;
--<<value for party_id from step 7>
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.object_id := lv_party_id;
--<<value for party_id from step 2>
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.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := rec_sites_con.start_date;
display_message('log',' Calling Site HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT......');
hz_party_contact_v2pub.create_org_contact
('T',
p_org_contact_rec,
x_org_contact_id,
x_party_relv_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Sites Relation Status:'||x_return_status||' OrgContactID :'||NVL(x_org_contact_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('OrgContactID :'||x_org_contact_id);
lv_site_acc_role_party_id := x_party_id;
--lv_site_acc_role_party_flag := 'Y';
--lv_sites_con_party_flag := 'Y';
display_message('log','OrgContactID :'||x_org_contact_id);
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
lv_site_acc_role_party_id:=rec_sites_con.v_org_contact_id;
END IF;
-- -------------------------------------------------------------------
-- Create Sites Contact Role
-- -------------------------------------------------------------------
IF rec_sites_con.v_rel_party_id IS NULL THEN
p_cr_cust_acc_role_rec.party_id := lv_site_acc_role_party_id;
p_cr_cust_acc_role_rec.cust_account_id := lv_cust_account_id;
p_cr_cust_acc_role_rec.cust_acct_site_id := lv_cust_acct_site_id;
--p_cr_cust_acc_role_rec.primary_flag := 'Y';
p_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_cr_cust_acc_role_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling Site HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE......');
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_cr_cust_acc_role_rec,
x_cust_account_role_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Sites Account Role Status:'||x_return_status||' CustAcctRoleID :'||NVL(x_cust_account_role_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
lv_sites_con_party_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||('OrgContactID :'||x_cust_account_role_id);
display_message('log','OrgContactID :'||x_cust_account_role_id);
lv_site_account_role_id := x_cust_account_role_id;
--lv_site_account_role_flag := 'Y';
--lv_sites_con_party_flag := 'N';
END IF;
lv_api_site_phone_msg := lv_api_site_phone_msg||' : '||lv_message_out;
ELSE
lv_site_account_role_id := rec_sites_con.v_rel_party_id;
END IF;
----END Create Sites Contact Role
--Update Staging Table
BEGIN
UPDATE xxmtz_ar_cstmr_site_con_stg_t
SET valid_status = DECODE(NVL(lv_site_account_role_id,0),0,'PROCESSFAILED',DECODE(lv_sites_con_party_flag,'Y','PROCESSED','PROCESSFAILED')),
api_msg = lv_api_site_con_msg,
v_cust_site_contact_id = lv_sites_con_party_id,
v_org_contact_id = lv_site_acc_role_party_id,
v_rel_party_id = lv_site_account_role_id
WHERE customer_number = rec_header.customer_number
AND address_id = rec_sites_con.address_id;
END;
-----------------------------------------------------------------
-- 10. Cursor Starts - Customer Sites Contact Communications
-- --------------------------------------------------------------
FOR rec_sites_con_phone IN cur_sites_con_phone (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_site_contact_role_id := NULL;
lv_api_site_con_role_msg := NULL;
IF rec_sites_con_phone.v_cust_site_contact_phone_id IS NULL THEN
display_message('log','IN cur_sites_con_phone ');
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := lv_site_acc_role_party_id;
--lv_party_site_id;
p_contact_point_rec.primary_flag := rec_sites_con_phone.primary_flag;
-- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
-- p_contact_point_rec.status := rec_sites_con_phone.status;
p_phone_rec.phone_area_code := rec_sites_con_phone.area_code;
p_phone_rec.phone_country_code := rec_sites_con_phone.country_code;
p_phone_rec.phone_number := rec_sites_con_phone.phone_number;
p_phone_rec.phone_line_type := rec_sites_con_phone.phone_type;
p_contact_point_rec.created_by_module := gc_created_by_module;
display_message('log',' Calling SiteContact HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT......');
hz_contact_point_v2pub.create_contact_point
('T',
p_contact_point_rec,
p_edi_rec,
p_emailv_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site ContactPoint Status:'||x_return_status||' SiteContactID :'||NVL(x_contact_point_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
--lv_site_contact_phone_flag := 'N';
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_con_phone_msg := lv_api_site_con_phone_msg||' : '||('SiteContactID :'||x_contact_point_id);
display_message('log','SiteContactID :'||x_contact_point_id);
--lv_site_contact_phone_flag := 'Y';
lv_site_contact_phone_id := x_contact_point_id;
END IF;
lv_api_site_con_phone_msg := lv_api_site_con_phone_msg||' : '||lv_message_out;
--Update Staging Table
-- display_message('log',' lv_site_contact_phone_flag Value :'||lv_site_contact_phone_flag);
UPDATE xxmtz_ar_cstmr_si_con_ph_stg_t
SET valid_status = DECODE(NVL(lv_site_contact_phone_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_con_phone_msg,
v_cust_site_contact_phone_id = lv_site_contact_phone_id
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_sites_con_phone.contact_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; -- 10. Cursor Ends - CustomerSitesCommunications
-- ---------------------------------------------------------------------
-----------------------------------------------------------------
-- 11. Cursor Starts - Customer Sites Contact Roles
-- --------------------------------------------------------------
FOR rec_sites_con_role IN cur_sites_con_role (rec_header.customer_number,
rec_sites_con.contact_id)
LOOP
lv_api_site_con_role_msg := NULL;
IF rec_sites_con_role.v_cust_site_contact_role_id IS NULL THEN
display_message('log','IN cur_sites_con_role');
p_role_responsibility_rec.responsibility_type := rec_sites_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := lv_site_account_role_id;
p_role_responsibility_rec.primary_flag := rec_sites_con_role.primary_flag;
p_role_responsibility_rec.created_by_module := gc_created_by_module;
display_message('log','Calling Site Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_ROLE_RESPONSIBILITY........');
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);
display_message('log','Site Contact Role Status:'||x_return_status||' Responsibility ID :'||NVL(x_responsibility_id,0)||' Message Count :'||x_msg_count);
IF x_return_status <> 'S' THEN
lv_message_out := NULL;
--lv_site_contact_role_flag := 'N' ;
IF NVL (x_msg_count, 0) > 1 THEN
FOR i IN 1 .. x_msg_count LOOP
lv_message_out := lv_message_out|| '--'|| SUBSTR (fnd_msg_pub.get (p_encoded => 'F'),1,255);
END LOOP;
display_message('log',' Error Message :'||lv_message_out);
ELSE
lv_message_out := x_msg_data;
display_message('log',' Error Message :'||lv_message_out);
END IF;
ELSE
lv_api_site_con_role_msg := lv_api_site_con_role_msg||' : '||('ResponsibilityID :'||x_responsibility_id);
display_message('log','ResponsibilityID :'||x_responsibility_id);
--lv_site_contact_role_flag := 'Y';
lv_site_contact_role_id := x_responsibility_id;
END IF;
lv_api_site_con_role_msg := lv_api_site_con_role_msg||' : '||lv_message_out;
--Update Staging Table
--display_message('log',' lv_site_contact_role_flag Value :'||lv_site_contact_role_flag);
UPDATE xxmtz_ar_cstmr_si_role_stg_t
SET valid_status = DECODE(NVL(lv_site_contact_role_id,0),0,'PROCESSFAILED','PROCESSED'),
api_msg = lv_api_site_con_role_msg,
v_cust_site_contact_role_id = lv_site_contact_role_id
WHERE customer_number = rec_header.customer_number
AND contact_id = rec_sites_con.contact_id;
END IF;
-- ---------------------------------------------------------------------
END LOOP; --11.END Cursor - Customer Sites Contact Roles
-- ---------------------------------------------------------------------
END LOOP; --9. End Customer Sites Contacts
-- ---------------------------------------------------------------------
END LOOP; --6.End CustomerLocation
-- ---------------------------------------------------------------------
EXCEPTION WHEN NO_PARTY_ID THEN
display_message('log','Party Is not created');
WHEN OTHERS THEN
display_message('log','Other Error :'||Sqlerrm);
END; --End forException
END LOOP; --1.End HeaderLoop
COMMIT;
END xxmtz_ar_cstmr_cnv_load_prc;
--This Procedure is used to Display message in Log and Output files
PROCEDURE display_message(p_mode Varchar2, p_message Varchar2) IS
BEGIN
IF p_mode = 'output' then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
ELSIF p_mode = 'log' then
FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
ELSIF p_mode = 'both' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
END IF;
DBMS_OUTPUT.PUT_LINE(p_message);
END display_message;
---Procedure to Update header status if any line status is fialed.
PROCEDURE Update_Header_staus(p_customer_number IN VARCHAR2,
p_action IN VARCHAR2)
IS
lv_header_cnt NUMBER := NULL;
lv_header_comm_cnt NUMBER := NULL;
lv_header_contact_cnt NUMBER := NULL;
lv_header_cont_comm_cnt NUMBER := NULL;
lv_header_cont_role_cnt NUMBER := NULL;
lv_site_location_cnt NUMBER := NULL;
lv_site_use_cnt NUMBER := NULL;
lv_site_comm_cnt NUMBER := NULL;
lv_site_contact_cnt NUMBER := NULL;
lv_site_cont_comm_cnt NUMBER := NULL;
lv_site_cont_role_cnt NUMBER := NULL;
BEGIN
------Header Count---
SELECT COUNT(1)
INTO lv_header_cnt
FROM xxmtz_ar_cstmr_hdr_stg_t
WHERE (valid_status = ('PROCESSFAILED') --p_action
OR v_party_id IS NULL
OR n_location_id IS NULL
OR n_header_site_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Failed Count :'||lv_header_cnt);
------Header Phone Count-------
SELECT COUNT(1)
INTO lv_header_comm_cnt
FROM xxmtz_ar_cstmr_phone_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_point_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Phone Count :'||lv_header_comm_cnt);
------Header Contact------
SELECT COUNT(1)
INTO lv_header_contact_cnt
FROM xxmtz_ar_cstmr_contact_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_party_id IS NULL)
AND customer_number = NVL(p_customer_number,customer_number);
display_message('log','Header Contact Count :'||lv_header_contact_cnt);
------Header Contact Phone count----
SELECT count(1)
INTO lv_header_cont_comm_cnt
FROM xxmtz_ar_cstmr_con_phone_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Header Contact Phone Count :'||lv_header_contact_cnt);
-------Header Contact Roles count------
SELECT count(1)
INTO lv_header_cont_role_cnt
FROM xxmtz_ar_cstmr_con_role_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_contact_role_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Header Contact Roles Count :'||lv_header_cont_role_cnt);
---------- Sites Location Count------------------------
SELECT count(1)
INTO lv_site_location_cnt
FROM xxmtz_ar_cstmr_location_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Location Count :'||lv_site_location_cnt);
---------- Sites Use count-------------
SELECT count(1)
INTO lv_site_use_cnt
FROM xxmtz_ar_cstmr_site_use_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_use_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Use Count :'||lv_site_use_cnt);
---------- Sites Phone Count -----------------------
SELECT count(1)
INTO lv_site_comm_cnt
FROM xxmtz_ar_cstmr_site_ph_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Phone Count :'||lv_site_comm_cnt);
------------ Sites Contact Count --------------------
SELECT count(1)
INTO lv_site_contact_cnt
FROM xxmtz_ar_cstmr_site_con_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Count :'||lv_site_contact_cnt);
------------ Sites Contact Phone Count --------------------
SELECT count(1)
INTO lv_site_cont_comm_cnt
FROM xxmtz_ar_cstmr_si_con_ph_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_phone_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Phone Count :'||lv_site_cont_comm_cnt);
---------------- Header Contact Role Count--------------------
SELECT Count(1)
INTO lv_site_cont_role_cnt
FROM xxmtz_ar_cstmr_si_role_stg_t
WHERE (valid_status IN ('PROCESSFAILED')
OR v_cust_site_contact_role_id IS NULL)
AND customer_number = p_customer_number;
display_message('log','Sites Contact Role Count :'||lv_site_cont_role_cnt);
IF lv_header_cnt = 0
AND lv_header_comm_cnt = 0
AND lv_header_contact_cnt = 0
AND lv_header_cont_comm_cnt = 0
AND lv_header_cont_role_cnt = 0
AND lv_site_location_cnt = 0
AND lv_site_use_cnt = 0
AND lv_site_comm_cnt = 0
AND lv_site_contact_cnt = 0
AND lv_site_cont_comm_cnt = 0
AND lv_site_cont_role_cnt = 0
THEN
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = 'PROCESSED'
WHERE customer_number = p_customer_number;
display_message('log','Customer :'||p_customer_number||' Is Processed');
ELSE
UPDATE xxmtz_ar_cstmr_hdr_stg_t
SET valid_status = 'PROCESSFAILED'
WHERE customer_number = p_customer_number;
display_message('log',' Customer :'||p_customer_number||' Is Failed');
END IF;
END Update_Header_staus;
END XXMTZ_AR_CSTMR_CONV_API_PKG;
/
No comments:
Post a Comment