Following API contains some of the pl/sql features such as cursors, cursor variables, exception handling, loop, error handling etc.
DROP TABLE spc_cust_error_table
/
CREATE TABLE spc_cust_error_table
(source_transaction_id varchar2(1000),
ERROR_TYPE varchar2(1000),
error_message varchar2(1000),
table_name varchar2(1000),
column_name varchar2(1000))
/
CREATE OR REPLACE PACKAGE spc_ar_cust_conv
AS
PROCEDURE spc_arcust_conv;
END spc_ar_cust_conv;
/
CREATE OR REPLACE PACKAGE BODY spc_ar_cust_conv
AS
spc_ccid NUMBER;
PROCEDURE spc_arcust_conv
IS
spc_error_msg_ex varchar2(1000) :=NULL;
spc_insert_update_flag varchar2(10) := 'I';
v_err_msg VARCHAR2(100) := NULL;
spc_created_by NUMBER := 1085;
spc_last_update_date DATE := SYSDATE;
spc_creation_date DATE := SYSDATE;
spc_last_updated_by NUMBER := 1085;
spc_last_update_login NUMBER := NULL;
spc_custhead_record_status VARCHAR2(1000) := NULL;
spc_custprofile_record_status VARCHAR2(1000) := NULL;
spc_err_msg VARCHAR2(1000) := NULL;
spc_cust_profile_class_name VARCHAR2(1000) := NULL;
spc_dunning_letter_set_name VARCHAR2(1000) := NULL;
spc_grouping_rule_name VARCHAR2(1000) := NULL;
spc_standard_term_name VARCHAR2(1000) := NULL;
spc_statement_cycle_name VARCHAR2(1000) := NULL;
spc_autocash_hierarchy_name varchar2(1000) := NULL;
spc_collector_name varchar2(1000) := NULL;
spc_customer_class_code VARCHAR2(1000) := NULL;
spc_cust_tax_code VARCHAR2(1000) := NULL;
spc_site_use_tax_code VARCHAR2(1000) := NULL;
spc_cust_ship_via_code VARCHAR2(1000) := NULL;
spc_site_ship_via_code VARCHAR2(1000) := NULL;
spc_site_use_code VARCHAR2(1000) := NULL;
spc_orig_system_customer_ref VARCHAR2(1000) := NULL;
spc_orig_system_address_ref VARCHAR2(1000) := NULL;
spc_orig_system_contact_ref VARCHAR2(1000) := NULL;
spc_orig_system_telephone_ref VARCHAR2(1000) := NULL;
spc_charge_flag VARCHAR2(1000) := NULL;
spc_cust_class VARCHAR2(1000) := NULL;
spc_orig_system_parent_ref VARCHAR2(1000) := NULL;
spc_bill_to_site_ref VARCHAR2(1000) := NULL;
v_rule0 number := NULL;
v_rule1 number := NULL;
v_rule2 number := NULL;
v_rule3 number := NULL;
v_rule4 number := NULL;
v_rule5 number := NULL;
v_rule6 number := NULL;
v_rule7 number := NULL;
spc_tax_org_id NUMBER := NULL;
/***************************************************************************************************************************
First cursor for driving the second,third,fourth,fifth cursors
**************************************************************************************************************************/
CURSOR spc_cust_main
IS
SELECT customer_id,status FROM ra_customers@TRAIN.WORLD;
/***************************************************************************************************************************
Second cursor for converting the customers,customer addresses and the addresses business purposes.This cursor is dependent on First Cursor
Skip customers created 3 months back or earlier and not used on any converted Sales Order or Invoice
**************************************************************************************************************************/
CURSOR spc_custhead_cur(spc_header_customer_id number)
IS
SELECT
a.customer_id orig_system_customer_ref ,
b.address_id orig_system_address_ref ,
c.site_use_code ,
a.customer_number ,
a.customer_name ,
a.status customer_status ,
a.customer_type ,
c.location ,
b.address_id ,
b.address1 ,
b.address2 ,
b.address3 ,
b.address4 ,
b.city ,
b.state ,
b.province ,
b.county ,
b.country ,
b.postal_code ,
b.org_id ,
a.customer_category_code ,
a.customer_class_code ,
a.tax_code cust_tax_code ,
a.tax_reference cust_tax_reference ,
c.tax_code site_use_tax_code ,
c.tax_reference site_use_tax_reference ,
c.demand_class_code ,
a.ship_via cust_ship_via_code ,
c.ship_via site_ship_via_code ,
c.primary_flag primary_site_use_flag ,
a.last_update_login ,
a.last_updated_by ,
a.last_update_date ,
a.created_by ,
a.creation_date ,
a.customer_id ,
a.customer_name translated_customer_name ,
a.attribute1 customer_attribute1 ,
a.attribute2 customer_attribute2 ,
a.attribute3 customer_attribute3 ,
a.attribute4 customer_attribute4 ,
a.attribute5 customer_attribute5 ,
a.attribute6 customer_attribute6 ,
a.attribute7 customer_attribute7 ,
a.attribute8 customer_attribute8 ,
a.attribute9 customer_attribute9 ,
a.attribute10 customer_attribute10 ,
a.attribute11 customer_attribute11 ,
a.attribute12 customer_attribute12 ,
a.attribute13 customer_attribute13 ,
a.attribute14 customer_attribute14 ,
a.attribute15 customer_attribute15 ,
b.attribute1 address_attribute1 ,
b.attribute2 address_attribute2 ,
b.attribute3 address_attribute3 ,
b.attribute4 address_attribute4 ,
b.attribute5 address_attribute5 ,
b.attribute6 address_attribute6 ,
b.attribute7 address_attribute7 ,
b.attribute8 address_attribute8 ,
b.attribute9 address_attribute9 ,
b.attribute10 address_attribute10 ,
b.attribute11 address_attribute11 ,
b.attribute12 address_attribute12 ,
b.attribute13 address_attribute13 ,
b.attribute14 address_attribute14 ,
b.attribute15 address_attribute15 ,
b.attribute_category address_attribute_category ,
a.attribute_category customer_attribute_category ,
c.bill_to_site_use_id bill_to_orig_address_ref ,
c.site_use_id old_site_use_id ,
b.status address_status
FROM
ra_customers@TRAIN.WORLD a,
ra_addresses_all@TRAIN.WORLD b,
ra_site_uses_all@TRAIN.WORLD c
WHERE a.customer_id= b.customer_id(+)
AND b.address_id= c.address_id(+)
AND a.customer_id=spc_header_customer_id;
/***************************************************************************************************************************
Third cursor for converting the customer profiles information and profile amounts info.
This cursor is dependent on First Cursor
**************************************************************************************************************************/
CURSOR spc_custprofile_cur(spc_profile_customer_id number) IS
SELECT
a.account_status ,
a.customer_id ,
a.auto_rec_incl_disputed_flag ,
a.autocash_hierarchy_id ,
b.auto_rec_min_receipt_amount ,
nvl(a.charge_on_finance_charge_flag,'N') charge_on_finance_charge_flag ,
a.clearing_days ,
a.collector_id ,
a.cons_inv_flag ,
a.cons_inv_type ,
a.credit_balance_statements ,
a.credit_hold ,
a.credit_rating ,
b.currency_code ,
a.customer_profile_id ,
a.discount_terms ,
a.credit_checking ,
a.dunning_letters ,
a.interest_charges ,
a.statements ,
a.discount_grace_days ,
a.dunning_letter_set_id ,
a.grouping_rule_id ,
a.interest_period_days ,
b.interest_rate ,
b.max_interest_charge ,
b.min_dunning_amount ,
b.min_dunning_invoice_amount ,
b.min_fc_balance_amount ,
b.min_fc_invoice_amount ,
b.min_statement_amount ,
a.customer_id orig_system_customer_ref ,
b.overall_credit_limit ,
a.override_terms ,
a.payment_grace_days ,
a.percent_collectable ,
a.risk_code ,
a.standard_terms ,
a.statement_cycle_id ,
a.tax_printing_option ,
a.tolerance ,
b.trx_credit_limit ,
c.name cust_profile_class_name ,
d.name dunning_letter_set_name ,
e.name grouping_rule_name ,
f.name standard_term_name ,
g.name statement_cycle_name ,
h.hierarchy_name autocash_hierarchy_name ,
i.name collector_name ,
j.address_id orig_system_address_ref ,
j.site_use_id
FROM
ar_customer_profiles@TRAIN.WORLD a,
ar_customer_profile_amounts@TRAIN.WORLD b,
ar_customer_profile_classes@TRAIN.WORLD c,
ar_dunning_letter_sets@TRAIN.WORLD d,
ra_grouping_rules@TRAIN.WORLD e,
ra_terms@TRAIN.WORLD f,
ar_statement_cycles@TRAIN.WORLD g,
ar_autocash_hierarchies@TRAIN.WORLD h,
ar_collectors@TRAIN.WORLD i,
ra_site_uses_all@TRAIN.WORLD j
WHERE
a.site_use_id =j.site_use_id(+)
AND
a.customer_profile_id =b.customer_profile_id(+)
AND
a.customer_profile_class_id =c.customer_profile_class_id(+)
AND
a.dunning_letter_set_id =d.dunning_letter_set_id(+)
AND
a.grouping_rule_id =e.grouping_rule_id(+)
AND
a.standard_terms =f.term_id(+)
AND
a.statement_cycle_id =g.statement_cycle_id(+)
AND
a.autocash_hierarchy_id =h.autocash_hierarchy_id(+)
AND
a.collector_id =i.collector_id(+)
AND
a.customer_id =spc_profile_customer_id ;
/***************************************************************************************************************************
Fourth cursor for converting the contacts for the customer,contacts for the customer address,contact phone numbers for the customer contact,contact phone numbers for the customer address contact.This cursor is dependent on First Cursor
**************************************************************************************************************************/
CURSOR spc_cust_contact_cur (spc_contact_customer_id NUMBER)
IS
SELECT a.customer_id orig_system_customer_ref ,
a.address_id orig_system_address_ref ,
a.orig_system_reference orig_system_contact_ref ,
b.orig_system_reference orig_system_telephone_ref ,
a.first_name contact_first_name ,
a.last_name contact_last_name ,
a.title contact_title ,
a.job_title contact_job_title ,
a.sex_code ,
a.salutation ,
a.email_address ,
a.mail_stop ,
a.contact_key ,
b.phone_number telephone ,
b.extension telephone_extension ,
b.phone_type telephone_type ,
b.area_code telephone_area_code ,
a.last_update_date ,
a.last_updated_by ,
a.creation_date ,
a.created_by ,
a.last_update_login ,
a.contact_id
FROM
ra_contacts@TRAIN.WORLD a,
ra_phones@TRAIN.WORLD b
WHERE
a.customer_id = spc_contact_customer_id
AND a.contact_id is not null
AND a.contact_id = b.contact_id(+);
/***************************************************************************************************************************
Fifth cursor for converting the customer phone numbers at customer level.This cursor is dependent on First Cursor
**************************************************************************************************************************/
CURSOR spc_cust_phone_cur (spc_phone_customer_id NUMBER)
IS
SELECT a.orig_system_reference orig_system_telephone_ref ,
a.customer_id orig_system_customer_Ref ,
a.address_id orig_system_address_ref ,
a.phone_number telephone ,
a.extension telephone_extension ,
a.phone_type telephone_type ,
a.area_code telephone_area_code ,
a.last_update_date ,
a.last_updated_by ,
a.creation_date ,
a.created_by ,
a.last_update_login ,
a.contact_id
FROM
ra_phones@TRAIN.WORLD a
WHERE a.customer_id = spc_phone_customer_id
AND a.contact_id is null;
/***************************************************************************************************************************
DECLARE SECTION IS OVER . BEGIN SECTION STARTS HERE.OPENING THE FIRST CURSOR
**************************************************************************************************************************/
BEGIN
FOR spc_for_cust_main IN spc_cust_main
LOOP
v_rule0 :=NULL;
v_rule1 :=NULL;
v_rule2 :=NULL;
v_rule3 :=NULL;
v_rule4 :=NULL;
spc_custhead_record_status :=NULL;
--convert all active customers which are created in last three months
SELECT count(*) into v_rule0
FROM ra_customers@train.world
WHERE status='A' AND TRUNC(CREATION_DATE) > ADD_MONTHS(TRUNC(SYSDATE),-3)
and customer_id=spc_for_cust_main.customer_id;
--convert active customers which are required for invoices
select count(*) into v_rule1
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and bill_to_customer_id = spc_for_cust_main.customer_id
and spc_for_cust_main.status='A';
--convert active customers which are required for salesorders
SELECT count(*) into v_rule2
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and customer_id=spc_for_cust_main.customer_id
and spc_for_cust_main.status='A';
--convert inactive customers which are required for open invoices
--dont convert the customer invoices for the inactive customers
select count(*) into v_rule3
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and STATUS_TRX='OP'
and bill_to_customer_id = spc_for_cust_main.customer_id
and spc_for_cust_main.status='I';
--convert inactive customers which are required for open salesorders
--dont convert the customer sales orders for the inactive customers
SELECT count(*) into v_rule4
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and open_flag='Y'
and customer_id=spc_for_cust_main.customer_id
and spc_for_cust_main.status='I';
IF v_rule0<1 AND v_rule1<1 AND v_rule2<1 AND v_rule3<1 AND v_rule4<1 THEN
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_main.customer_id,'customer rule fired',NULL,NULL,NULL);
GOTO END_LOOP1;
END IF;
/********************************************************************************************************** PROCESSING THE SECOND CURSOR
**********************************************************************************************************/
FOR spc_for_custhead_cur IN spc_custhead_cur(spc_for_cust_main.customer_id)
LOOP
v_rule5 :=NULL;
v_rule6 :=NULL;
v_rule7 :=NULL;
--convert inactive customer addresses which are required for open salesorders
--dont convert the customer sales orders for the inactive addresses
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.address_status='I' THEN
SELECT count(*) into v_rule5
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and open_flag='Y'
and invoice_to_site_use_id=spc_for_custhead_cur.old_site_use_id;
END IF;
--convert inactive customer addresses which are required for open invoices
--dont convert the customer invoices for the inactive addresses
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.address_status='I' THEN
SELECT count(*) into v_rule6
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and STATUS_TRX='OP'
and bill_to_site_use_id=spc_for_custhead_cur.old_site_use_id;
END IF;
--dont convert the customer addresses without business purposes
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.site_use_code is null THEN
v_rule7:=0;
END IF;
IF v_rule5<1 AND v_rule6<1 OR v_rule7<1 THEN
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_main.customer_id,'address rule fired',NULL,NULL,NULL);
GOTO END_LOOP2;
END IF;
/**********************************************************************************************************
Validating customer tax code
**********************************************************************************************************/
spc_tax_org_id:=NULL;
IF spc_for_custhead_cur.org_id=106 then spc_tax_org_id:=83;
ELSIF spc_for_custhead_cur.org_id=104 then spc_tax_org_id:=84;
END IF;
BEGIN
spc_cust_tax_code :=NULL;
IF spc_for_custhead_cur.cust_tax_code IS NOT NULL THEN
SELECT tax_code INTO
spc_cust_tax_code
FROM ar_vat_tax_all
where tax_code=spc_for_custhead_cur.cust_tax_code
AND org_id=spc_tax_org_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','spc_for_custhead_cur.cust_tax_code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
END;
BEGIN
spc_site_use_tax_code :=NULL;
IF spc_for_custhead_cur.site_use_tax_code IS NOT NULL THEN
SELECT tax_code INTO
spc_site_use_tax_code
FROM ar_vat_tax_all
WHERE tax_code=spc_for_custhead_cur.site_use_tax_code
AND org_id=spc_tax_org_id
AND ROWNUM<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL',spc_for_custhead_cur.site_use_tax_code);
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
END;
/**********************************************************************************************************
Validating customer ship via code
**********************************************************************************************************/
BEGIN
spc_cust_ship_via_code :=NULL;
IF spc_for_custhead_cur.cust_ship_via_code IS NOT NULL THEN
SELECT freight_code INTO
spc_cust_ship_via_code
FROM wsh_carriers
WHERE
freight_code=spc_for_custhead_cur.cust_ship_via_code and rownum<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,' cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,'cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,'cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
BEGIN
spc_site_ship_via_code :=NULL;
IF spc_for_custhead_cur.site_ship_via_code IS NOT NULL THEN
SELECT freight_code INTO
spc_site_ship_via_code
FROM wsh_carriers
WHERE
freight_code=spc_for_custhead_cur.site_ship_via_code and rownum<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
/**********************************************************************************************************
Validating site use code
**********************************************************************************************************/
BEGIN
spc_site_use_code :=NULL;
IF spc_for_custhead_cur.site_use_code IS NOT NULL THEN
SELECT lookup_code INTO
spc_site_use_code
FROM fnd_lookup_values_vl
WHERE
lookup_type='SITE_USE_CODE'
AND LOOKUP_CODE=spc_for_custhead_cur.site_use_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
BEGIN
spc_cust_class :=NULL;
IF spc_for_custhead_cur.customer_class_code IS NOT NULL THEN
SELECT lookup_code INTO
spc_cust_class
FROM fnd_lookup_values_vl
WHERE
lookup_type='CUSTOMER CLASS'
AND LOOKUP_CODE=spc_for_custhead_cur.customer_class_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
BEGIN
spc_orig_system_parent_ref :=NULL;
IF spc_for_custhead_cur.bill_to_orig_address_ref IS NOT NULL THEN
SELECT a.customer_id INTO spc_orig_system_parent_ref
from
ra_addresses_all@train.world a,
ra_site_uses_all@train.world b
where
a.address_id=b.address_id
and
b.site_use_id=spc_for_custhead_cur.bill_to_orig_address_ref;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
END;
BEGIN
spc_bill_to_site_ref :=NULL;
IF spc_for_custhead_cur.bill_to_orig_address_ref IS NOT NULL THEN
SELECT a.address_id INTO spc_bill_to_site_ref
from
ra_addresses_all@train.world a,
ra_site_uses_all@train.world b
where
a.address_id=b.address_id
and
b.site_use_id=spc_for_custhead_cur.bill_to_orig_address_ref;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
END;
IF spc_custhead_record_status is null then
BEGIN
INSERT INTO ra_customers_interface_all(
org_id ,
customer_class_code ,
address1 ,
address2 ,
address3 ,
address4 ,
orig_system_parent_ref ,
orig_system_address_ref ,
orig_system_customer_ref ,
bill_to_orig_address_ref ,
city ,
country ,
county ,
postal_code ,
province ,
state ,
primary_site_use_flag ,
site_use_code ,
site_use_tax_code ,
site_use_tax_reference ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
customer_category_code ,
customer_name ,
customer_number ,
customer_status ,
customer_type ,
cust_ship_via_code ,
cust_tax_code ,
cust_tax_reference ,
insert_update_flag ,
location ,
translated_customer_name ,
site_ship_via_code ,
--language ,
--terrritory ,
--demand_class_code ,
--cust_tax_exempt_num ,
--warning_text ,
--validated_flag ,
--jgzz_fiscal_code ,
--site_use_tax_exempt_num ,
--location_ccid ,
--message_text ,
--org_id ,
--party_number ,
--person_first_name ,
--person_flag ,
--person_last_name ,
--request_id ,
--customer_key ,
--customer_name_phonetic ,
--customer_prospect_code ,
--bill_to_orig_address_ref ,
--orig_system_parent_ref ,
--orig_system_party_ref ,
--gl_id_clearing ,
--gl_id_factor ,
--gl_id_freight ,
--gl_id_rec ,
--gl_id_remittance ,
--gl_id_rev ,
--gl_id_tax ,
--gl_id_unbilled ,
--gl_id_unearned ,
--gl_id_unpaid_rec ,
--address_key ,
--address_lines_phonetic ,
customer_attribute1 ,
customer_attribute10 ,
customer_attribute11 ,
customer_attribute12 ,
customer_attribute13 ,
customer_attribute14 ,
customer_attribute15 ,
customer_attribute2 ,
customer_attribute3 ,
customer_attribute4 ,
customer_attribute5 ,
customer_attribute6 ,
customer_attribute7 ,
customer_attribute8 ,
customer_attribute9 ,
customer_attribute_category ,
address_attribute1 ,
address_attribute10 ,
address_attribute11 ,
address_attribute12 ,
address_attribute13 ,
address_attribute14 ,
address_attribute15 ,
address_attribute2 ,
address_attribute3 ,
address_attribute4 ,
address_attribute5 ,
address_attribute6 ,
address_attribute7 ,
address_attribute8 ,
address_attribute9 ,
address_attribute_category ,
--site_ship_via_code ,
--site_use_attribute1 ,
--site_use_attribute10 ,
--site_use_attribute11 ,
--site_use_attribute12 ,
--site_use_attribute13 ,
site_use_attribute14
--site_use_attribute15 ,
--site_use_attribute16 ,
--site_use_attribute17 ,
--site_use_attribute18 ,
--site_use_attribute19 ,
--site_use_attribute2 ,
--site_use_attribute20 ,
--site_use_attribute21 ,
--site_use_attribute22 ,
--site_use_attribute23 ,
--site_use_attribute24 ,
--site_use_attribute25 ,
--site_use_attribute3 ,
--site_use_attribute4 ,
--site_use_attribute5 ,
--site_use_attribute6 ,
--site_use_attribute7 ,
--site_use_attribute8 ,
--site_use_attribute9 ,
--site_use_attribute_category ,
--global_attribute1 ,
--global_attribute10 ,
--global_attribute11 ,
--global_attribute12 ,
--global_attribute13 ,
--global_attribute14 ,
--global_attribute15 ,
--global_attribute16 ,
--global_attribute17 ,
--global_attribute18 ,
--global_attribute19 ,
--global_attribute2 ,
--global_attribute20 ,
--global_attribute3 ,
--global_attribute4 ,
--global_attribute5 ,
--global_attribute6 ,
--global_attribute7 ,
--global_attribute8 ,
--global_attribute9 ,
--global_attribute_category ,
--gdf_address_attribute1 ,
--gdf_address_attribute10 ,
--gdf_address_attribute11 ,
--gdf_address_attribute12 ,
--gdf_address_attribute13 ,
--gdf_address_attribute14 ,
--gdf_address_attribute15 ,
--gdf_address_attribute16 ,
--gdf_address_attribute17 ,
--gdf_address_attribute18 ,
--gdf_address_attribute19 ,
--gdf_address_attribute2 ,
--gdf_address_attribute20 ,
--gdf_address_attribute3 ,
--gdf_address_attribute4 ,
--gdf_address_attribute5 ,
--gdf_address_attribute6 ,
--gdf_address_attribute7 ,
--gdf_address_attribute8 ,
--gdf_address_attribute9 ,
--gdf_address_attr_cat ,
--gdf_site_use_attribute1 ,
--gdf_site_use_attribute10 ,
--gdf_site_use_attribute11 ,
--gdf_site_use_attribute12 ,
--gdf_site_use_attribute13 ,
--gdf_site_use_attribute14 ,
--gdf_site_use_attribute15 ,
--gdf_site_use_attribute16 ,
--gdf_site_use_attribute17 ,
--gdf_site_use_attribute18 ,
--gdf_site_use_attribute19 ,
--gdf_site_use_attribute2 ,
--gdf_site_use_attribute20 ,
--gdf_site_use_attribute3 ,
--gdf_site_use_attribute4 ,
--gdf_site_use_attribute5 ,
--gdf_site_use_attribute6 ,
--gdf_site_use_attribute7 ,
--gdf_site_use_attribute8 ,
--gdf_site_use_attribute9 ,
--gdf_site_use_attr_cat ,
--interface_status
)
VALUES
( spc_for_custhead_cur.org_id ,
spc_cust_class ,
spc_for_custhead_cur.address1 ,
spc_for_custhead_cur.address2 ,
spc_for_custhead_cur.address3 ,
spc_for_custhead_cur.address4 ,
spc_orig_system_parent_ref ,
spc_for_custhead_cur.orig_system_address_ref ,
spc_for_custhead_cur.orig_system_customer_ref ,
spc_bill_to_site_ref ,
spc_for_custhead_cur.city ,
spc_for_custhead_cur.country ,
spc_for_custhead_cur.county ,
spc_for_custhead_cur.postal_code ,
spc_for_custhead_cur.province ,
spc_for_custhead_cur.state ,
spc_for_custhead_cur.primary_site_use_flag ,
spc_for_custhead_cur.site_use_code ,
spc_site_use_tax_code ,
spc_for_custhead_cur.site_use_tax_reference ,
spc_created_by ,
spc_creation_date ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_custhead_cur.customer_category_code ,
spc_for_custhead_cur.customer_name ,
spc_for_custhead_cur.customer_number ,
spc_for_custhead_cur.customer_status ,
spc_for_custhead_cur.customer_type ,
spc_cust_ship_via_code ,
spc_cust_tax_code ,
spc_for_custhead_cur.cust_tax_reference ,
spc_insert_update_flag ,
spc_for_custhead_cur.location ,
spc_for_custhead_cur.translated_customer_name ,
spc_site_ship_via_code ,
--language ,
--terrritory ,
--demand_class_code ,
--cust_tax_exempt_num ,
--warning_text ,
--validated_flag ,
--jgzz_fiscal_code ,
--site_use_tax_exempt_num ,
--location_ccid ,
--message_text ,
--org_id ,
--party_number ,
--person_first_name ,
--person_flag ,
--person_last_name ,
--request_id ,
--customer_key ,
--customer_name_phonetic ,
--customer_prospect_code ,
--bill_to_orig_address_ref ,
--orig_system_parent_ref ,
--orig_system_party_ref ,
--gl_id_clearing ,
--gl_id_factor ,
--gl_id_freight ,
--gl_id_rec ,
--gl_id_remittance ,
--gl_id_rev ,
--gl_id_tax ,
--gl_id_unbilled ,
--gl_id_unearned ,
--gl_id_unpaid_rec ,
--address_key ,
--address_lines_phonetic ,
spc_for_custhead_cur.customer_attribute1 ,
spc_for_custhead_cur.customer_attribute10 ,
spc_for_custhead_cur.customer_attribute11 ,
spc_for_custhead_cur.customer_attribute12 ,
spc_for_custhead_cur.customer_attribute13 ,
spc_for_custhead_cur.orig_system_customer_ref ,
spc_for_custhead_cur.customer_attribute15 ,
spc_for_custhead_cur.customer_attribute2 ,
spc_for_custhead_cur.customer_attribute3 ,
spc_for_custhead_cur.customer_attribute4 ,
spc_for_custhead_cur.customer_attribute5 ,
spc_for_custhead_cur.customer_attribute6 ,
spc_for_custhead_cur.customer_attribute7 ,
spc_for_custhead_cur.customer_attribute8 ,
spc_for_custhead_cur.customer_attribute9 ,
spc_for_custhead_cur.customer_attribute_category ,
spc_for_custhead_cur.address_attribute1 ,
spc_for_custhead_cur.address_attribute10 ,
spc_for_custhead_cur.address_attribute11 ,
spc_for_custhead_cur.address_attribute12 ,
spc_for_custhead_cur.address_attribute13 ,
spc_for_custhead_cur.orig_system_address_ref ,
spc_for_custhead_cur.address_attribute15 ,
spc_for_custhead_cur.address_attribute2 ,
spc_for_custhead_cur.address_attribute3 ,
spc_for_custhead_cur.address_attribute4 ,
spc_for_custhead_cur.address_attribute5 ,
spc_for_custhead_cur.address_attribute6 ,
spc_for_custhead_cur.address_attribute7 ,
spc_for_custhead_cur.address_attribute8 ,
spc_for_custhead_cur.address_attribute9 ,
spc_for_custhead_cur.address_attribute_category ,
--site_ship_via_code ,
--site_use_attribute1 ,
--site_use_attribute10 ,
--site_use_attribute11 ,
--site_use_attribute12 ,
--site_use_attribute13 ,
spc_for_custhead_cur.old_site_use_id
--site_use_attribute15 ,
--site_use_attribute16 ,
--site_use_attribute17 ,
--site_use_attribute18 ,
--site_use_attribute19 ,
--site_use_attribute2 ,
--site_use_attribute20 ,
--site_use_attribute21 ,
--site_use_attribute22 ,
--site_use_attribute23 ,
--site_use_attribute24 ,
--site_use_attribute25 ,
--site_use_attribute3 ,
--site_use_attribute4 ,
--site_use_attribute5 ,
--site_use_attribute6 ,
--site_use_attribute7 ,
--site_use_attribute8 ,
--site_use_attribute9 ,
--site_use_attribute_category ,
--global_attribute1 ,
--global_attribute10 ,
--global_attribute11 ,
--global_attribute12 ,
--global_attribute13 ,
--global_attribute14 ,
--global_attribute15 ,
--global_attribute16 ,
--global_attribute17 ,
--global_attribute18 ,
--global_attribute19 ,
--global_attribute2 ,
--global_attribute20 ,
--global_attribute3 ,
--global_attribute4 ,
--global_attribute5 ,
--global_attribute6 ,
--global_attribute7 ,
--global_attribute8 ,
--global_attribute9 ,
--global_attribute_category ,
--gdf_address_attribute1 ,
--gdf_address_attribute10 ,
--gdf_address_attribute11 ,
--gdf_address_attribute12 ,
--gdf_address_attribute13 ,
--gdf_address_attribute14 ,
--gdf_address_attribute15 ,
--gdf_address_attribute16 ,
--gdf_address_attribute17 ,
--gdf_address_attribute18 ,
--gdf_address_attribute19 ,
--gdf_address_attribute2 ,
--gdf_address_attribute20 ,
--gdf_address_attribute3 ,
--gdf_address_attribute4 ,
--gdf_address_attribute5 ,
--gdf_address_attribute6 ,
--gdf_address_attribute7 ,
--gdf_address_attribute8 ,
--gdf_address_attribute9 ,
--gdf_address_attr_cat ,
--gdf_site_use_attribute1 ,
--gdf_site_use_attribute10 ,
--gdf_site_use_attribute11 ,
--gdf_site_use_attribute12 ,
--gdf_site_use_attribute13 ,
--gdf_site_use_attribute14 ,
--gdf_site_use_attribute15 ,
--gdf_site_use_attribute16 ,
--gdf_site_use_attribute17 ,
--gdf_site_use_attribute18 ,
--gdf_site_use_attribute19 ,
--gdf_site_use_attribute2 ,
--gdf_site_use_attribute20 ,
--gdf_site_use_attribute3 ,
--gdf_site_use_attribute4 ,
--gdf_site_use_attribute5 ,
--gdf_site_use_attribute6 ,
--gdf_site_use_attribute7 ,
--gdf_site_use_attribute8 ,
--gdf_site_use_attribute9 ,
--gdf_site_use_attr_cat ,
--interface_status
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_custhead_cur.customer_number,'other error for header insert',spc_error_msg_ex,'customer number',NULL);
END;
END IF;
<
NULL;
END LOOP;
/**********************************************************************************************************
PROCESSING THE THIRD CURSOR
**********************************************************************************************************/
FOR spc_for_custprofile_cur IN spc_custprofile_cur(spc_for_cust_main.customer_id)
LOOP
spc_custprofile_record_status:=NULL;
/**********************************************************************************************************
validating customer profile classes;
**********************************************************************************************************/
BEGIN
spc_cust_profile_class_name :=NULL;
IF spc_for_custprofile_cur.cust_profile_class_name IS NOT NULL THEN
SELECT name INTO
spc_cust_profile_class_name
FROM ar_customer_profile_classes where name=spc_for_custprofile_cur.cust_profile_class_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
END;
/********************************************************************************************************** validating dunning letters
**********************************************************************************************************/
BEGIN
spc_dunning_letter_set_name :=NULL;
IF spc_for_custprofile_cur.dunning_letter_set_name IS NOT NULL THEN
SELECT name INTO
spc_dunning_letter_set_name
FROM ar_dunning_letter_sets where name=spc_for_custprofile_cur.dunning_letter_set_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
END;
/********************************************************************************************************** validating grouping rules
**********************************************************************************************************/
BEGIN
spc_grouping_rule_name :=NULL;
IF spc_for_custprofile_cur.grouping_rule_name IS NOT NULL THEN
SELECT name INTO
spc_grouping_rule_name
FROM ra_grouping_rules where name=spc_for_custprofile_cur.grouping_rule_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
END;
/********************************************************************************************************** validating standard terms
**********************************************************************************************************/
BEGIN
spc_standard_term_name :=NULL;
IF spc_for_custprofile_cur.standard_term_name IS NOT NULL THEN
SELECT name INTO
spc_standard_term_name
FROM ra_terms_vl where name=spc_for_custprofile_cur.standard_term_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
END;
/**********************************************************************************************************
validating statement cycles
**********************************************************************************************************/
BEGIN
spc_statement_cycle_name :=NULL;
IF spc_for_custprofile_cur.statement_cycle_name IS NOT NULL THEN
SELECT name INTO
spc_statement_cycle_name
FROM ar_statement_cycles where name=spc_for_custprofile_cur.statement_cycle_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles',spc_for_custprofile_cur.statement_cycle_name);
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles','name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles','name');
END;
/********************************************************************************************************** validating autocash hierarchies
**********************************************************************************************************/
BEGIN
spc_autocash_hierarchy_name :=NULL;
IF spc_for_custprofile_cur.autocash_hierarchy_name IS NOT NULL THEN
SELECT hierarchy_name INTO
spc_autocash_hierarchy_name
FROM ar_autocash_hierarchies where hierarchy_name=spc_for_custprofile_cur.autocash_hierarchy_name ;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name ',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name ',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
END;
/********************************************************************************************************** validating collectors
**********************************************************************************************************/
BEGIN
spc_collector_name :=NULL;
IF spc_for_custprofile_cur.collector_name IS NOT NULL THEN
SELECT name INTO
spc_collector_name
FROM ar_collectors
WHERE name=spc_for_custprofile_cur.collector_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
END;
IF spc_for_custprofile_cur.interest_charges is null or spc_for_custprofile_cur.interest_charges='N' THEN
spc_charge_flag:=NULL;
ELSE
spc_charge_flag:=spc_for_custprofile_cur.charge_on_finance_charge_flag;
END IF;
IF spc_custprofile_record_status is null then
BEGIN
INSERT INTO ra_customer_profiles_int_all(
created_by ,
creation_date ,
autocash_hierarchy_name ,
auto_rec_incl_disputed_flag ,
auto_rec_min_receipt_amount ,
charge_on_finance_charge_flag ,
clearing_days ,
collector_name ,
cons_inv_flag ,
cons_inv_type ,
credit_balance_statements ,
credit_checking ,
credit_hold ,
credit_rating ,
currency_code ,
customer_profile_class_name ,
discount_grace_days ,
discount_terms ,
dunning_letters ,
dunning_letter_set_name ,
account_status ,
grouping_rule_name ,
insert_update_flag ,
interest_charges ,
interest_period_days ,
interest_rate ,
last_updated_by ,
last_update_date ,
last_update_login ,
max_interest_charge ,
min_dunning_amount ,
min_dunning_invoice_amount ,
min_fc_balance_amount ,
min_fc_invoice_amount ,
min_statement_amount ,
orig_system_address_ref ,
orig_system_customer_ref ,
overall_credit_limit ,
override_terms ,
payment_grace_days ,
percent_collectable ,
risk_code ,
standard_term_name ,
statements ,
statement_cycle_name ,
tax_printing_option ,
tolerance ,
trx_credit_limit ,
--interface_status ,
--request_id ,
--validated_flag ,
--org_id ,
--validated_flag ,
--amount_attribute1 ,
--amount_attribute10 ,
--amount_attribute11 ,
--amount_attribute12 ,
--amount_attribute13 ,
--amount_attribute14 ,
--amount_attribute15 ,
--amount_attribute2 ,
--amount_attribute3 ,
--amount_attribute4 ,
--amount_attribute5 ,
--amount_attribute6 ,
--amount_attribute7 ,
--amount_attribute8 ,
--amount_attribute9 ,
--amount_attribute_category ,
attribute1 ,
attribute2
--attribute11 ,
--attribute12 ,
--attribute13 ,
--attribute14 ,
--attribute15 ,
--attribute2 ,
--attribute3 ,
--attribute4 ,
--attribute5 ,
--attribute6 ,
--attribute7 ,
--attribute8 ,
--attribute9 ,
--attribute_category ,
--gdf_cust_prof_attribute1 ,
--att_cust_prof_attribute10 ,
--att_cust_prof_attribute11 ,
--att_cust_prof_attribute12 ,
--att_cust_prof_attribute13 ,
--att_cust_prof_attribute14 ,
--att_cust_prof_attribute15 ,
--att_cust_prof_attribute16 ,
--att_cust_prof_attribute17 ,
--att_cust_prof_attribute18 ,
--att_cust_prof_attribute19 ,
--att_cust_prof_attribute2 ,
--att_cust_prof_attribute20 ,
--att_cust_prof_attribute3 ,
--att_cust_prof_attribute4 ,
--att_cust_prof_attribute5 ,
--att_cust_prof_attribute6 ,
--att_cust_prof_attribute7 ,
--att_cust_prof_attribute8 ,
--att_cust_prof_attribute9 ,
--att_cust_prof_attr_cat ,
--attbal_attribute1 ,
--attbal_attribute10 ,
--attbal_attribute11 ,
--attbal_attribute12 ,
--attbal_attribute13 ,
--attbal_attribute14 ,
--attbal_attribute15 ,
--attbal_attribute16 ,
--attbal_attribute17 ,
--attbal_attribute18 ,
--attbal_attribute19 ,
--attbal_attribute2 ,
--attbal_attribute20 ,
--attbal_attribute3 ,
--attbal_attribute4 ,
--attbal_attribute5 ,
--attbal_attribute6 ,
--attbal_attribute7 ,
--attbal_attribute8 ,
--attbal_attribute9 ,
--attbal_attribute_category ,
)VALUES(
spc_created_by ,
spc_creation_date ,
spc_autocash_hierarchy_name ,
spc_for_custprofile_cur.auto_rec_incl_disputed_flag ,
spc_for_custprofile_cur.auto_rec_min_receipt_amount ,
spc_charge_flag ,
spc_for_custprofile_cur.clearing_days ,
spc_collector_name ,
spc_for_custprofile_cur.cons_inv_flag ,
spc_for_custprofile_cur.cons_inv_type ,
spc_for_custprofile_cur.credit_balance_statements ,
spc_for_custprofile_cur.credit_checking ,
spc_for_custprofile_cur.credit_hold ,
spc_for_custprofile_cur.credit_rating ,
spc_for_custprofile_cur.currency_code ,
spc_cust_profile_class_name ,
spc_for_custprofile_cur.discount_grace_days ,
spc_for_custprofile_cur.discount_terms ,
spc_for_custprofile_cur.dunning_letters ,
spc_dunning_letter_set_name ,
spc_for_custprofile_cur.account_status ,
spc_grouping_rule_name ,
spc_insert_update_flag ,
spc_for_custprofile_cur.interest_charges ,
spc_for_custprofile_cur.interest_period_days ,
spc_for_custprofile_cur.interest_rate ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_custprofile_cur.max_interest_charge ,
spc_for_custprofile_cur.min_dunning_amount ,
spc_for_custprofile_cur.min_dunning_invoice_amount ,
spc_for_custprofile_cur.min_fc_balance_amount ,
spc_for_custprofile_cur.min_fc_invoice_amount ,
spc_for_custprofile_cur.min_statement_amount ,
spc_for_custprofile_cur.orig_system_address_ref ,
spc_for_custprofile_cur.orig_system_customer_ref ,
spc_for_custprofile_cur.overall_credit_limit ,
spc_for_custprofile_cur.override_terms ,
spc_for_custprofile_cur.payment_grace_days ,
spc_for_custprofile_cur.percent_collectable ,
spc_for_custprofile_cur.risk_code ,
spc_standard_term_name ,
spc_for_custprofile_cur.statements ,
spc_statement_cycle_name ,
spc_for_custprofile_cur.tax_printing_option ,
spc_for_custprofile_cur.tolerance ,
spc_for_custprofile_cur.trx_credit_limit ,
--interface_status ,
--request_id ,
--validated_flag ,
--org_id ,
--validated_flag ,
--amount_attribute1 ,
--amount_attribute10 ,
--amount_attribute11 ,
--amount_attribute12 ,
--amount_attribute13 ,
--amount_attribute14 ,
--amount_attribute15 ,
--amount_attribute2 ,
--amount_attribute3 ,
--amount_attribute4 ,
--amount_attribute5 ,
--amount_attribute6 ,
--amount_attribute7 ,
--amount_attribute8 ,
--amount_attribute9 ,
--amount_attribute_category ,
spc_for_custprofile_cur.overall_credit_limit ,
spc_for_custprofile_cur.trx_credit_limit
--attribute2
--attribute11 ,
--attribute12 ,
--attribute13 ,
--attribute14 ,
--attribute15 ,
--attribute2 ,
--attribute3 ,
--attribute4 ,
--attribute5 ,
--attribute6 ,
--attribute7 ,
--attribute8 ,
--attribute9 ,
--attribute_category ,
--gdf_cust_prof_attribute1 ,
--att_cust_prof_attribute10 ,
--att_cust_prof_attribute11 ,
--att_cust_prof_attribute12 ,
--att_cust_prof_attribute13 ,
--att_cust_prof_attribute14 ,
--att_cust_prof_attribute15 ,
--att_cust_prof_attribute16 ,
--att_cust_prof_attribute17 ,
--att_cust_prof_attribute18 ,
--att_cust_prof_attribute19 ,
--att_cust_prof_attribute2 ,
--att_cust_prof_attribute20 ,
--att_cust_prof_attribute3 ,
--att_cust_prof_attribute4 ,
--att_cust_prof_attribute5 ,
--att_cust_prof_attribute6 ,
--att_cust_prof_attribute7 ,
--att_cust_prof_attribute8 ,
--att_cust_prof_attribute9 ,
--att_cust_prof_attr_cat ,
--attbal_attribute1 ,
--attbal_attribute10 ,
--attbal_attribute11 ,
--attbal_attribute12 ,
--attbal_attribute13 ,
--attbal_attribute14 ,
--attbal_attribute15 ,
--attbal_attribute16 ,
--attbal_attribute17 ,
--attbal_attribute18 ,
--attbal_attribute19 ,
--attbal_attribute2 ,
--attbal_attribute20 ,
--attbal_attribute3 ,
--attbal_attribute4 ,
--attbal_attribute5 ,
--attbal_attribute6 ,
--attbal_attribute7 ,
--attbal_attribute8 ,
--attbal_attribute9 ,
--attbal_attribute_category ,
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
( spc_for_custprofile_cur.orig_system_customer_ref,'other error for profile',spc_error_msg_ex,'customerid',NULL);
END;
END IF;
END LOOP;
/********************************************************************************************************** PROCESSING THE FOURTH CURSOR
**********************************************************************************************************/
FOR spc_for_cust_contact_cur IN spc_cust_contact_cur (spc_for_cust_main.customer_id)
LOOP
BEGIN
INSERT INTO ra_contact_phones_int_all(
contact_attribute14 ,
contact_first_name ,
contact_job_title ,
contact_last_name ,
contact_title ,
created_by ,
creation_date ,
email_address ,
insert_update_flag ,
last_updated_by ,
last_update_date ,
last_update_login ,
mail_stop ,
orig_system_address_ref ,
orig_system_contact_ref ,
orig_system_customer_ref ,
orig_system_telephone_ref ,
salutation ,
sex_code ,
telephone ,
telephone_area_code ,
telephone_extension ,
telephone_type
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--org_id
--phone_attribute9 ,
--phone_attribute_category
)VALUES
( spc_for_cust_contact_cur.contact_id ,
spc_for_cust_contact_cur.contact_first_name ,
spc_for_cust_contact_cur.contact_job_title ,
spc_for_cust_contact_cur.contact_last_name ,
spc_for_cust_contact_cur.contact_title ,
spc_created_by ,
spc_creation_date ,
spc_for_cust_contact_cur.email_address ,
spc_insert_update_flag ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_cust_contact_cur.mail_stop ,
spc_for_cust_contact_cur.orig_system_address_ref ,
spc_for_cust_contact_cur.orig_system_contact_ref ,
spc_for_cust_contact_cur.orig_system_customer_ref ,
spc_for_cust_contact_cur.orig_system_telephone_ref ,
spc_for_cust_contact_cur.salutation ,
spc_for_cust_contact_cur.sex_code ,
spc_for_cust_contact_cur.telephone ,
spc_for_cust_contact_cur.telephone_area_code ,
spc_for_cust_contact_cur.telephone_extension ,
spc_for_cust_contact_cur.telephone_type
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--spc_for_cust_contact_cur.org_id
--phone_attribute9 ,
--phone_attribute_category
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex:=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_contact_cur.orig_system_customer_ref,'other error for contact',spc_error_msg_ex,'customerid',NULL);
END;
END LOOP;
/********************************************************************************************************** PROCESSING THE FIFTH CURSOR
**********************************************************************************************************/
FOR spc_for_cust_phone_cur IN spc_cust_phone_cur (spc_for_cust_main.customer_id)
LOOP
BEGIN
INSERT INTO ra_contact_phones_int_all(
created_by ,
creation_date ,
insert_update_flag ,
last_updated_by ,
last_update_date ,
last_update_login ,
orig_system_address_ref ,
orig_system_customer_ref ,
orig_system_telephone_ref ,
telephone ,
telephone_area_code ,
telephone_extension ,
telephone_type
--contact_first_name ,
--contact_job_title ,
--contact_last_name ,
--contact_title ,
--salutation ,
--sex_code ,
--email_address ,
--mail_stop ,
--orig_system_contact_ref ,
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--org_id ,
--phone_attribute9 ,
--phone_attribute_category
)VALUES
(
spc_created_by ,
spc_creation_date ,
spc_insert_update_flag ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_cust_phone_cur.orig_system_address_ref ,
spc_for_cust_phone_cur.orig_system_customer_ref ,
spc_for_cust_phone_cur.orig_system_telephone_ref ,
spc_for_cust_phone_cur.telephone ,
spc_for_cust_phone_cur.telephone_area_code ,
spc_for_cust_phone_cur.telephone_extension ,
spc_for_cust_phone_cur.telephone_type
--contact_first_name ,
--contact_job_title ,
--contact_last_name ,
--contact_title ,
--salutation ,
--sex_code ,
--email_address ,
--mail_stop ,
--orig_system_contact_ref ,
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--spc_for_cust_phone_cur.org_id ,
--phone_attribute9 ,
--phone_attribute_category
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_phone_cur.orig_system_customer_ref,'other error for contact phone',spc_error_msg_ex,'customerid',NULL);
END;
END LOOP;
<
NULL;
END LOOP;
END spc_arcust_conv;
END spc_ar_cust_conv;
/