[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select *
Into l_sysparm_rec
From ar_system_parameters;
oe_debug_pub.add( 'AFTER SELECT FROM AR_SYSTEM_PARAMETES' ) ;
Select party_id,
party_name
Into x_party_id,
x_party_name
From hz_parties
Where party_number = p_party_number
And party_type = p_party_type;
oe_debug_pub.add( 'AFTER SELECT OF PARTY INFO.' ) ;
PROCEDURE Update_Error_Flag(
p_rowid IN Rowid)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
oe_debug_pub.add( 'ENTERING PROCEDURE UPDATE_ERROR_FLAG' ) ;
Update Oe_Customer_Info_Iface_All
Set Error_Flag = 'Y'
Where rowid = p_rowid;
oe_debug_pub.add( 'EXITING PROCEDURE UPDATE_ERROR_FLAG' ) ;
OE_DEBUG_PUB.add ('Update Error Flag: Unexpected Error : '||sqlerrm);
END Update_Error_Flag;
PROCEDURE Update_Address_id(type_of_address IN VARCHAR2,
usage_site_id IN NUMBER,
row_id IN rowid )
IS
Pragma AUTONOMOUS_TRANSACTION;
oe_debug_pub.add (' Entering Update_Address_id');
SELECT new_address_id_ship,new_address_id_bill,new_address_id_Deliver
into l_address_id_ship, l_address_id_bill, l_address_id_deliver
FROM oe_customer_info_iface_all
WHERE rowid = row_id
FOR UPDATE NOWAIT;
UPDATE oe_customer_info_iface_all
SET new_address_id_ship = usage_site_id
WHERE rowid = row_id;
UPDATE oe_customer_info_iface_all
SET new_address_id_bill = usage_site_id
WHERE rowid = row_id;
UPDATE oe_customer_info_iface_all
SET new_address_id_Deliver = usage_site_id
WHERE rowid = row_id;
oe_debug_pub.add ('Update_Address_id: ' || usage_site_id);
OE_DEBUG_PUB.add ('Update_address_id: Unexpected Error : '||sqlerrm);
END Update_Address_Id;
fnd_msg_pub.delete_msg;
Select cust_account_role_id
From hz_cust_account_roles
Where party_id = p_contact_party_id
And cust_account_id = p_cust_acct_id
And role_type = 'CONTACT'
And status = 'A'
And cust_acct_site_id is null;
SELECT responsibility_type
FROM hz_role_responsibility
WHERE cust_account_role_id = l_role_id;
l_select Number;
fnd_msg_pub.delete_msg;
Select hz_contact_points_s.nextval
Into l_contact_points_rec.contact_point_id
From dual;
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
Select party_number,
organization_name,
person_first_name,
person_last_name,
person_middle_name,
person_name_suffix,
person_title,
customer_type,
email_address,
phone_area_code,
phone_number,
phone_extension,
new_account_id,
new_account_number,
new_party_id,
customer_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
rowid,
phone_country_code
from oe_customer_info_iface_all
where customer_info_ref = l_customer_info_ref
and customer_info_type_code = 'ACCOUNT';
Update_Error_Flag(p_rowid => customer_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => customer_rec.rowid);
Update_Error_Flag(p_rowid => customer_rec.rowid);
Update_Error_Flag(p_rowid => customer_rec.rowid);
Update_Error_Flag(p_rowid => customer_rec.rowid);
Update_Error_Flag(p_rowid => customer_rec.rowid);
fnd_msg_pub.delete_msg;
Update oe_customer_info_iface_all
Set New_Party_Id = x_cust_party_id,
New_Party_Number = l_party_number,
New_Account_Id = x_cust_account_id,
New_Account_Number = x_cust_account_Number
Where rowid = customer_rec.rowid;
Update_Error_Flag(p_rowid => customer_rec.rowid);
fnd_msg_pub.delete_msg;
Update oe_customer_info_iface_all
Set New_Party_Id = x_cust_party_id,
New_Party_Number = l_party_number,
New_Account_Id = x_cust_account_id,
New_Account_Number = x_cust_account_Number
Where rowid = customer_rec.rowid;
Update_Error_Flag(p_rowid => customer_rec.rowid);
Update_Error_Flag(p_rowid => customer_rec.rowid);
Select party_id, cust_account_id
Into x_party_id, x_account_id
From hz_cust_accounts
Where account_number = p_customer_number;
Select parent_customer_ref,
current_customer_number,
current_customer_id,
country,
address1,
address2,
address3,
address4,
city,
postal_code,
state,
province,
county,
is_ship_to_address,
is_bill_to_address,
is_deliver_to_address,
new_address_id_ship,
new_address_id_bill,
new_address_id_deliver,
location_number,
site_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
rowid
From oe_customer_info_iface_all
WHERE customer_info_ref = l_customer_info_ref
AND customer_info_type_code = 'ADDRESS';
select 'Y' into l_site_id_exists
from hz_cust_site_uses
where site_use_id=address_info_rec.new_address_id_ship
and status='A';
select 'Y' into l_site_id_exists
from hz_cust_site_uses
where site_use_id=address_info_rec.new_address_id_bill
and status='A';
select 'Y' into l_site_id_exists
from hz_cust_site_uses
where site_use_id=address_info_rec.new_address_id_deliver
and status='A';
Update_Error_Flag(p_rowid => address_info_rec.rowid);
Select party_id
Into l_customer_party_id
From hz_cust_accounts
Where cust_account_id = l_customer_info_id;
Update_Error_Flag(p_rowid => address_info_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => address_info_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => address_info_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Address_id( type_of_address => 'SHIP_TO',
usage_site_id => l_site_use_id_ship,
row_id => address_info_rec.rowid );
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Address_id( type_of_address => 'BILL_TO',
usage_site_id => l_site_use_id_bill,
row_id => address_info_rec.rowid );
Update_Error_Flag(p_rowid => address_info_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Address_id( type_of_address => 'DELIVER_TO',
usage_site_id => l_site_use_id_deliver,
row_id => address_info_rec.rowid );
Update_Error_Flag(p_rowid => address_info_rec.rowid);
Select person_first_name,
person_last_name,
person_middle_name,
person_name_suffix,
person_title,
email_address,
phone_area_code,
phone_number,
phone_extension,
current_customer_number,
current_customer_id,
parent_customer_ref,
new_contact_id,
new_party_id,
is_ship_to_address,
is_bill_to_address,
is_deliver_to_address,
rowid,
phone_country_code
from oe_customer_info_iface_all
where customer_info_ref = l_customer_info_ref
and customer_info_type_code = 'CONTACT';
Select party_id
Into l_customer_party_id
From hz_cust_accounts
Where cust_account_id = l_customer_info_id;
Select contact_rec.person_last_name || DECODE(contact_rec.person_first_name, NULL, NULL, ', '|| contact_rec.PERSON_FIRST_NAME) || DECODE(contact_rec.Person_Name_Suffix, NULL, NULL, ', '||contact_rec.Person_Name_Suffix)
Into l_contact_name
From Dual;
Update_Error_Flag(p_rowid => contact_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => contact_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => contact_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
Update_Error_Flag(p_rowid => contact_rec.rowid);
oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE' ) ;
Select hz_party_number_s.nextval
Into l_party_rec.party_number
From Dual;
oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_PARTY_REC.PARTY_NUMBER ) ;
Update_Error_Flag(p_rowid => contact_rec.rowid);
fnd_msg_pub.delete_msg;
oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE FOR ORG' ) ;
Select hz_party_number_s.nextval
Into l_org_contact_rec.party_rel_rec.party_rec.party_number
From Dual;
oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_ORG_CONTACT_REC.PARTY_REL_REC.PARTY_REC.PARTY_NUMBER ) ;
Select party_type
Into l_org_contact_rec.party_rel_rec.object_type
From HZ_PARTIES
Where party_id = l_customer_party_id;
oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM CONTACT SEQ' ) ;
Select hz_contact_numbers_s.nextval
Into l_org_contact_rec.contact_number
From Dual;
oe_debug_pub.add( 'AFTER SELECTING NEW CONTACT SEQUENCE ' || L_ORG_CONTACT_REC.CONTACT_NUMBER ) ;
Update_Error_Flag(p_rowid => contact_rec.rowid);
fnd_msg_pub.delete_msg;
Update_Error_Flag(p_rowid => contact_rec.rowid);
fnd_msg_pub.delete_msg;
Select party_name
Into x_contact_name
From hz_parties
Where party_id = l_contact_party_id;
Update oe_customer_info_iface_all
Set New_Contact_Id = x_contact_id,
New_Party_Id = x_rel_party_id,
is_ship_to_address =
decode(l_type_of_contact,'SHIP_TO','Y',is_ship_to_address),
is_bill_to_address =
decode(l_type_of_contact,'BILL_TO','Y',is_bill_to_address),
is_deliver_to_address =
decode(l_type_of_contact,'DELIVER_TO','Y',is_deliver_to_address)
Where rowid = contact_rec.rowid;
Update_Error_Flag(p_rowid => contact_rec.rowid);
Select country,
address1,
address2,
address3,
address4,
city,
postal_code,
state,
province,
county,
is_ship_to_address,
is_bill_to_address,
is_deliver_to_address,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20
from oe_customer_info_iface_all
where customer_info_ref = l_customer_info_ref
and customer_info_type_code = 'ADDRESS';
Procedure Delete_Customer_Info(
p_header_customer_rec In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Rec_Type,
p_line_customer_tbl In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Tbl_Type)
Is
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'INSIDE DELETE_CUSTOMER_INFO' ) ;
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Orig_Sys_Customer_Ref
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
And customer_info_ref Not In (Select Sold_To_Contact_Ref
From Oe_Headers_Iface_All b
Where b.Sold_To_Contact_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
and customer_info_type_code = 'ADDRESS' )
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Ship_Address_Ref
From Oe_Lines_Iface_All c
Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
and customer_info_type_code = 'ADDRESS')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Bill_Address_Ref
From Oe_Lines_Iface_All c
Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
and customer_info_type_code = 'ADDRESS')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Deliver_Address_Ref
From Oe_Lines_Iface_All c
Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
And customer_info_ref Not In ((Select Ship_To_Contact_Ref
From Oe_Headers_Iface_All b
Where b.Ship_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Ship_To_Contact_Ref
From Oe_Lines_Iface_All c
Where c.Ship_To_Contact_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
And customer_info_ref Not In ((Select Bill_To_Contact_Ref
From Oe_Headers_Iface_All b
Where b.Bill_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Bill_To_Contact_Ref
From Oe_Lines_Iface_All c
Where c.Bill_To_Contact_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
From Oe_Headers_Iface_All b
Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Deliver_To_Contact_Ref
From Oe_Lines_Iface_All c
Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
and customer_info_type_code = 'ADDRESS' )
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
From Oe_Lines_Iface_All b
Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Ship_Address_Ref
From Oe_Headers_Iface_All c
Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
and customer_info_type_code = 'ADDRESS' )
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
From Oe_Lines_Iface_All b
Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Bill_Address_Ref
From Oe_Headers_Iface_All c
Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
and customer_info_type_code = 'ADDRESS')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
From Oe_Lines_Iface_All b
Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
UNION ALL
(Select Orig_Deliver_Address_Ref
From Oe_Headers_Iface_All c
Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
And customer_info_ref Not In ((Select Ship_To_Contact_Ref
From Oe_Lines_Iface_All b
Where b.Ship_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Ship_To_Contact_Ref
From Oe_Headers_Iface_All c
Where c.Ship_To_Contact_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
And customer_info_ref Not In ((Select Bill_To_Contact_Ref
From Oe_Lines_Iface_All b
Where b.Bill_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Bill_To_Contact_Ref
From Oe_Headers_Iface_All c
Where c.Bill_To_Contact_Ref = a.customer_info_ref));
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
and customer_info_type_code = 'CONTACT')
And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
From Oe_Headers_Iface_All b
Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
Delete
From oe_customer_info_iface_all a
Where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
From Oe_Lines_Iface_All b
Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
UNION ALL
(Select Deliver_To_Contact_Ref
From Oe_Headers_Iface_All c
Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
oe_debug_pub.add( 'PROBLEM IN CALL TO DELETE_CUSTOMER_INFO. ABORT PROCESSING' ) ;
fnd_message.set_token('API_NAME', 'Delete_Customer_Info');
oe_debug_pub.add( 'EXITING IN DELETE_CUSTOMER_INFO PROCEDURE WITH ERROR' ) ;
End Delete_Customer_Info;