[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rs_cust_relation_id,
customer_id,
created_by,
creation_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from csp_rs_cust_relations
where resource_type = p_resource_type and resource_id = p_resource_id;
csp_rs_cust_relations_pkg.update_row(
p_RS_CUST_RELATION_ID => l_rs_cust_relation_id,
p_RESOURCE_TYPE => p_resource_type,
p_RESOURCE_ID => p_resource_id,
p_CUSTOMER_ID => p_customer_id,
p_CREATED_BY => l_created_by,
p_CREATION_DATE => l_creation_date,
p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, 1),
p_ATTRIBUTE_CATEGORY => l_attribute_category,
p_ATTRIBUTE1 => l_attribute1,
p_ATTRIBUTE2 => l_attribute2,
p_ATTRIBUTE3 => l_attribute3,
p_ATTRIBUTE4 => l_attribute4,
p_ATTRIBUTE5 => l_attribute5,
p_ATTRIBUTE6 => l_attribute6,
p_ATTRIBUTE7 => l_attribute7,
p_ATTRIBUTE8 => l_attribute8,
p_ATTRIBUTE9 => l_attribute9,
p_ATTRIBUTE10 => l_attribute10,
p_ATTRIBUTE11 => l_attribute11,
p_ATTRIBUTE12 => l_attribute12,
p_ATTRIBUTE13 => l_attribute13,
p_ATTRIBUTE14 => l_attribute14,
p_ATTRIBUTE15 => l_attribute15);
csp_rs_cust_relations_pkg.insert_row(
px_RS_CUST_RELATION_ID => l_rs_cust_relation_id,
p_RESOURCE_TYPE => p_resource_type,
p_RESOURCE_ID => p_resource_id,
p_CUSTOMER_ID => p_customer_id,
p_CREATED_BY => nvl(fnd_global.user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, 1),
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL);
select userenv('LANG') into l_language from dual;
select csp_location_id_s1.NEXTVAL into l_location_code from dual;
PROCEDURE do_update_ship_to_location
(p_location_id IN NUMBER
,p_style IN VARCHAR2
,p_address_line_1 IN VARCHAR2
,p_address_line_2 IN VARCHAR2
,p_address_line_3 IN VARCHAR2
,p_country IN VARCHAR2
,p_postal_code IN VARCHAR2
,p_region_1 IN VARCHAR2
,p_region_2 IN VARCHAR2
,p_region_3 IN VARCHAR2
,p_town_or_city IN VARCHAR2
,p_tax_name IN VARCHAR2
,p_telephone_number_1 IN VARCHAR2
,p_telephone_number_2 IN VARCHAR2
,p_telephone_number_3 IN VARCHAR2
,p_loc_information13 IN VARCHAR2
,p_loc_information14 IN VARCHAR2
,p_loc_information15 IN VARCHAR2
,p_loc_information16 IN VARCHAR2
,p_loc_information17 IN VARCHAR2
,p_loc_information18 IN VARCHAR2
,p_loc_information19 IN VARCHAR2
,p_loc_information20 IN VARCHAR2
,p_attribute_category IN VARCHAR2
,p_attribute1 IN VARCHAR2
,p_attribute2 IN VARCHAR2
,p_attribute3 IN VARCHAR2
,p_attribute4 IN VARCHAR2
,p_attribute5 IN VARCHAR2
,p_attribute6 IN VARCHAR2
,p_attribute7 IN VARCHAR2
,p_attribute8 IN VARCHAR2
,p_attribute9 IN VARCHAR2
,p_attribute10 IN VARCHAR2
,p_attribute11 IN VARCHAR2
,p_attribute12 IN VARCHAR2
,p_attribute13 IN VARCHAR2
,p_attribute14 IN VARCHAR2
,p_attribute15 IN VARCHAR2
,p_attribute16 IN VARCHAR2
,p_attribute17 IN VARCHAR2
,p_attribute18 IN VARCHAR2
,p_attribute19 IN VARCHAR2
,p_attribute20 IN VARCHAR2
,p_object_version_number IN OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'do_update_ship_to_location';
SAVEPOINT do_update_ship_to_location_PUB;
select userenv('LANG') into l_language from dual;
hr_location_api.update_location
(p_validate => false,
p_effective_date => sysdate,
-- p_language_code => l_language,
p_location_id => p_location_id,
-- p_location_code => l_location_code,
p_description => p_address_line_1,
-- p_tp_header_id => l_tp_header_id,
-- p_ece_tp_location_code => l_ece_tp_location_code,
p_address_line_1 => p_address_line_1,
p_address_line_2 => p_address_line_2,
p_address_line_3 => p_address_line_3,
-- p_bill_to_site_flag => l_bill_to_site_flag,
p_country => p_country,
-- p_designated_receiver_id => l_designated_receiver_id,
-- p_in_organization_flag => l_in_organization_flag,
-- p_inactive_date => l_inactive_date,
-- p_operating_unit_id => l_operating_unit_id,
-- p_inventory_organization_id => l_inventory_organization_id,
-- p_office_site_flag => l_office_site_flag,
p_postal_code => p_postal_code,
-- p_receiving_site_flag => l_receiving_site_flag,
p_region_1 => p_region_1,
p_region_2 => p_region_2,
p_region_3 => p_region_3,
-- p_ship_to_location_id => l_ship_to_location_id,
-- p_ship_to_site_flag => l_ship_to_site_flag,
p_style => p_style,
p_tax_name => p_tax_name,
p_telephone_number_1 => p_telephone_number_1,
p_telephone_number_2 => p_telephone_number_2,
p_telephone_number_3 => p_telephone_number_3,
p_town_or_city => p_town_or_city,
p_loc_information13 => p_loc_information13,
p_loc_information14 => p_loc_information14,
p_loc_information15 => p_loc_information15,
p_loc_information16 => p_loc_information16,
p_loc_information17 => p_loc_information17,
p_loc_information18 => p_loc_information18,
p_loc_information19 => p_loc_information19,
p_loc_information20 => p_loc_information20,
p_object_version_number => l_object_version_number,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_attribute_category => p_attribute_category
);
Rollback to do_update_ship_to_location_PUB;
Rollback to do_update_ship_to_location_PUB;
END do_update_ship_to_location;
select primary_per_type
from hz_party_site_uses
where site_use_type = 'SHIP_TO'
and primary_per_type = 'Y'
and (party_site_id in
(select party_site_id
from hz_party_sites where party_id = p_party_id and status = 'A'));
select site_use_id
from HZ_CUST_SITE_USES
where cust_acct_site_id in (
select hsc1.cust_acct_site_id
from hz_cust_acct_sites hsc1, hz_cust_acct_sites hsc2
where hsc1.cust_account_id = hsc2.cust_account_id
and hsc2.cust_acct_site_id = v_address_id
and hsc1.cust_acct_site_id <> v_address_id
)
and primary_flag = 'Y'
and status = 'A';
arh_csu_pkg.Insert_Row(
X_Site_Use_Id => b_site_use_id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => nvl(fnd_global.user_id,1),
X_Creation_Date => sysdate,
X_Created_By => nvl(fnd_global.user_id,1),
X_Site_Use_Code => 'BILL_TO',
x_customer_id => p_customer_id,
X_Address_Id => p_address_id,
X_Primary_Flag => 'N',
X_Status => 'A',
X_Location => s_location_id,
X_Last_Update_Login => nvl(fnd_global.user_id,1),
X_Contact_Id => null,
X_Bill_To_Site_Use_Id => null,
X_Sic_Code => null,
X_Payment_Term_Id => null,
X_Gsa_Indicator => 'N',
X_Ship_Partial => 'N',
X_Ship_Via => null,
X_Fob_Point => null,
X_Order_Type_Id => null,
X_Price_List_Id => null,
X_Freight_Term => null,
X_Warehouse_Id => null,
X_Territory_Id => null,
X_Tax_Code => null,
X_Tax_Reference => null,
X_Demand_Class_Code => null,
x_inventory_location_id => null,
x_inventory_organization_id => null,
X_Attribute_Category => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute16 => null,
X_Attribute17 => null,
X_Attribute18 => null,
X_Attribute19 => null,
X_Attribute20 => null,
X_Attribute21 => null,
X_Attribute22 => null,
X_Attribute23 => null,
X_Attribute24 => null,
X_Attribute25 => null,
X_Tax_Classification => null,
X_Tax_Header_Level_Flag => null,
X_Tax_Rounding_Rule => null,
X_Global_Attribute_Category => null,
X_Global_Attribute1 => null,
X_Global_Attribute2 => null,
X_Global_Attribute3 => null,
X_Global_Attribute4 => null,
X_Global_Attribute5 => null,
X_Global_Attribute6 => null,
X_Global_Attribute7 => null,
X_Global_Attribute8 => null,
X_Global_Attribute9 => null,
X_Global_Attribute10 => null,
X_Global_Attribute11 => null,
X_Global_Attribute12 => null,
X_Global_Attribute13 => null,
X_Global_Attribute14 => null,
X_Global_Attribute15 => null,
X_Global_Attribute16 => null,
X_Global_Attribute17 => null,
X_Global_Attribute18 => null,
X_Global_Attribute19 => null,
X_Global_Attribute20 => null,
X_Primary_Salesrep_Id => null,
X_Finchrg_Receivables_Trx_Id => null,
X_GL_ID_Rec => null,
X_GL_ID_Rev => null,
X_GL_ID_Tax => null,
X_GL_ID_Freight => null,
X_GL_ID_Clearing => null,
X_GL_ID_Unbilled => null,
X_GL_ID_Unearned => null,
X_GL_ID_Unpaid_rec => null,
X_GL_ID_remittance => null,
X_GL_ID_factor => null,
X_DATES_NEGATIVE_TOLERANCE => null,
X_DATES_POSITIVE_TOLERANCE => null,
X_DATE_TYPE_PREFERENCE => null,
X_OVER_SHIPMENT_TOLERANCE => null,
X_UNDER_SHIPMENT_TOLERANCE => null,
X_ITEM_CROSS_REF_PREF => null,
X_OVER_RETURN_TOLERANCE => null,
X_UNDER_RETURN_TOLERANCE => null,
X_SHIP_SETS_INCLUDE_LINES_FLAG => 'N',
X_ARRIVALSETS_INCL_LINES_FLAG => 'N',
X_SCHED_DATE_PUSH_FLAG => 'N',
X_INVOICE_QUANTITY_RULE => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
arh_csu_pkg.Insert_Row(
X_Site_Use_Id => s_site_use_id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => nvl(fnd_global.user_id,1),
X_Creation_Date => sysdate,
X_Created_By => nvl(fnd_global.user_id,1),
X_Site_Use_Code => 'SHIP_TO',
x_customer_id => p_customer_id,
X_Address_Id => p_address_id,
X_Primary_Flag => s_primary_flag,
X_Status => s_status,
X_Location => s_location_id,
X_Last_Update_Login => nvl(fnd_global.user_id,1),
X_Contact_Id => null,
-- X_Bill_To_Site_Use_Id => null,
X_Bill_To_Site_Use_Id => b_site_use_id,
X_Sic_Code => null,
X_Payment_Term_Id => null,
X_Gsa_Indicator => 'N',
X_Ship_Partial => 'N',
X_Ship_Via => null,
X_Fob_Point => null,
X_Order_Type_Id => null,
X_Price_List_Id => null,
X_Freight_Term => null,
X_Warehouse_Id => null,
X_Territory_Id => null,
X_Tax_Code => null,
X_Tax_Reference => null,
X_Demand_Class_Code => null,
x_inventory_location_id => p_inv_location_id,
x_inventory_organization_id => null,
X_Attribute_Category => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute16 => null,
X_Attribute17 => null,
X_Attribute18 => null,
X_Attribute19 => null,
X_Attribute20 => null,
X_Attribute21 => null,
X_Attribute22 => null,
X_Attribute23 => null,
X_Attribute24 => null,
X_Attribute25 => null,
X_Tax_Classification => null,
X_Tax_Header_Level_Flag => null,
X_Tax_Rounding_Rule => null,
X_Global_Attribute_Category => null,
X_Global_Attribute1 => null,
X_Global_Attribute2 => null,
X_Global_Attribute3 => null,
X_Global_Attribute4 => null,
X_Global_Attribute5 => null,
X_Global_Attribute6 => null,
X_Global_Attribute7 => null,
X_Global_Attribute8 => null,
X_Global_Attribute9 => null,
X_Global_Attribute10 => null,
X_Global_Attribute11 => null,
X_Global_Attribute12 => null,
X_Global_Attribute13 => null,
X_Global_Attribute14 => null,
X_Global_Attribute15 => null,
X_Global_Attribute16 => null,
X_Global_Attribute17 => null,
X_Global_Attribute18 => null,
X_Global_Attribute19 => null,
X_Global_Attribute20 => null,
X_Primary_Salesrep_Id => null,
X_Finchrg_Receivables_Trx_Id => null,
X_GL_ID_Rec => null,
X_GL_ID_Rev => null,
X_GL_ID_Tax => null,
X_GL_ID_Freight => null,
X_GL_ID_Clearing => null,
X_GL_ID_Unbilled => null,
X_GL_ID_Unearned => null,
X_GL_ID_Unpaid_rec => null,
X_GL_ID_remittance => null,
X_GL_ID_factor => null,
X_DATES_NEGATIVE_TOLERANCE => null,
X_DATES_POSITIVE_TOLERANCE => null,
X_DATE_TYPE_PREFERENCE => null,
X_OVER_SHIPMENT_TOLERANCE => null,
X_UNDER_SHIPMENT_TOLERANCE => null,
X_ITEM_CROSS_REF_PREF => null,
X_OVER_RETURN_TOLERANCE => null,
X_UNDER_RETURN_TOLERANCE => null,
X_SHIP_SETS_INCLUDE_LINES_FLAG => 'N',
X_ARRIVALSETS_INCL_LINES_FLAG => 'N',
X_SCHED_DATE_PUSH_FLAG => 'N',
X_INVOICE_QUANTITY_RULE => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
PROCEDURE do_update_site_use
(p_site_use_id IN NUMBER
,p_primary_flag IN VARCHAR2
,p_status IN VARCHAR2
,p_customer_id IN NUMBER
,p_inv_location_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'do_update_site_use';
s_last_update_login hz_cust_site_uses_all.last_update_login%TYPE;
s_program_update_date hz_cust_site_uses_all.program_update_date%TYPE;
s_wh_update_date hz_cust_site_uses_all.wh_update_date%TYPE;
s_last_update_date DATE;
s_last_updated_by NUMBER;
select cust_acct_site_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
site_use_code,
primary_flag,
status,
location,
last_update_login,
contact_id,
bill_to_site_use_id,
orig_system_reference,
sic_code,
payment_term_id,
gsa_indicator,
ship_partial,
ship_via,
fob_point,
order_type_id,
price_list_id,
freight_term,
warehouse_id,
territory_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
request_id,
program_application_id,
program_id,
program_update_date,
tax_reference,
sort_priority,
tax_code,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
last_accrue_charge_date,
second_last_accrue_charge_date,
last_unaccrue_charge_date,
second_last_unaccrue_chrg_date,
demand_class_code,
org_id,
tax_header_level_flag,
tax_rounding_rule,
wh_update_date,
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,
global_attribute_category,
primary_salesrep_id,
finchrg_receivables_trx_id,
dates_negative_tolerance,
dates_positive_tolerance,
date_type_preference,
over_shipment_tolerance,
under_shipment_tolerance,
item_cross_ref_pref,
over_return_tolerance,
under_return_tolerance,
ship_sets_include_lines_flag,
arrivalsets_include_lines_flag,
sched_date_push_flag,
invoice_quantity_rule,
pricing_event,
gl_id_rec,
gl_id_rev,
gl_id_tax,
gl_id_freight,
gl_id_clearing,
gl_id_unbilled,
gl_id_unearned,
gl_id_unpaid_rec,
gl_id_remittance,
gl_id_factor,
tax_classification
from hz_cust_site_uses
where site_use_id = p_site_use_id and site_use_code = 'SHIP_TO';
select bill_to_site_use_id
from hz_cust_site_uses
where site_use_id = p_site_use_id and site_use_code = 'SHIP_TO';
select cust_acct_site_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
site_use_code,
primary_flag,
status,
location,
last_update_login,
contact_id,
bill_to_site_use_id,
orig_system_reference,
sic_code,
payment_term_id,
gsa_indicator,
ship_partial,
ship_via,
fob_point,
order_type_id,
price_list_id,
freight_term,
warehouse_id,
territory_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
request_id,
program_application_id,
program_id,
program_update_date,
tax_reference,
sort_priority,
tax_code,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
last_accrue_charge_date,
second_last_accrue_charge_date,
last_unaccrue_charge_date,
second_last_unaccrue_chrg_date,
demand_class_code,
org_id,
tax_header_level_flag,
tax_rounding_rule,
wh_update_date,
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,
global_attribute_category,
primary_salesrep_id,
finchrg_receivables_trx_id,
dates_negative_tolerance,
dates_positive_tolerance,
date_type_preference,
over_shipment_tolerance,
under_shipment_tolerance,
item_cross_ref_pref,
over_return_tolerance,
under_return_tolerance,
ship_sets_include_lines_flag,
arrivalsets_include_lines_flag,
sched_date_push_flag,
invoice_quantity_rule,
pricing_event,
gl_id_rec,
gl_id_rev,
gl_id_tax,
gl_id_freight,
gl_id_clearing,
gl_id_unbilled,
gl_id_unearned,
gl_id_unpaid_rec,
gl_id_remittance,
gl_id_factor,
tax_classification
from hz_cust_site_uses
where site_use_id = l_bill_to_site_use_id and site_use_code = 'BILL_TO';
SAVEPOINT do_update_site_use_PUB;
s_last_update_date,
s_last_updated_by,
s_creation_date,
s_created_by,
s_site_use_code,
s_primary_flag,
s_status,
s_location,
s_last_update_login,
s_contact_id,
s_bill_to_site_use_id,
s_orig_system_reference,
s_sic_code,
s_payment_term_id,
s_gsa_indicator,
s_ship_partial,
s_ship_via,
s_fob_point,
s_order_type_id,
s_price_list_id,
s_freight_term,
s_warehouse_id,
s_territory_id,
s_attribute_category,
s_attribute1,
s_attribute2,
s_attribute3,
s_attribute4,
s_attribute5,
s_attribute6,
s_attribute7,
s_attribute8,
s_attribute9,
s_attribute10,
s_request_id,
s_program_application_id,
s_program_id,
s_program_update_date,
s_tax_reference,
s_sort_priority,
s_tax_code,
s_attribute11,
s_attribute12,
s_attribute13,
s_attribute14,
s_attribute15,
s_attribute16,
s_attribute17,
s_attribute18,
s_attribute19,
s_attribute20,
s_attribute21,
s_attribute22,
s_attribute23,
s_attribute24,
s_attribute25,
s_last_accrue_charge_date,
s_snd_last_accrue_charge_date,
s_last_unaccrue_charge_date,
s_snd_last_unaccrue_chrg_date,
s_demand_class_code,
s_org_id,
s_tax_header_level_flag,
s_tax_rounding_rule,
s_wh_update_date,
s_global_attribute1,
s_global_attribute2,
s_global_attribute3,
s_global_attribute4,
s_global_attribute5,
s_global_attribute6,
s_global_attribute7,
s_global_attribute8,
s_global_attribute9,
s_global_attribute10,
s_global_attribute11,
s_global_attribute12,
s_global_attribute13,
s_global_attribute14,
s_global_attribute15,
s_global_attribute16,
s_global_attribute17,
s_global_attribute18,
s_global_attribute19,
s_global_attribute20,
s_global_attribute_category,
s_primary_salesrep_id,
s_finchrg_receivables_trx_id,
s_dates_negative_tolerance,
s_dates_positive_tolerance,
s_date_type_preference,
s_over_shipment_tolerance,
s_under_shipment_tolerance,
s_item_cross_ref_pref,
s_over_return_tolerance,
s_under_return_tolerance,
s_ship_sets_include_lines_flag,
s_arv_include_lines_flag,
s_sched_date_push_flag,
s_invoice_quantity_rule,
s_pricing_event,
s_gl_id_rec,
s_gl_id_rev,
s_gl_id_tax,
s_gl_id_freight,
s_gl_id_clearing,
s_gl_id_unbilled,
s_gl_id_unearned,
s_gl_id_unpaid_rec,
s_gl_id_remittance,
s_gl_id_factor,
s_tax_classification;
arh_csu_pkg.Update_Row(
X_Site_Use_Id => l_Bill_to_site_use_id,
X_Last_Update_Date => s_last_update_date,
X_Last_Updated_By => nvl(fnd_global.user_id, 1),
X_Site_Use_Code => s_site_use_code,
X_customer_id => p_customer_id,
X_Address_Id => s_cust_acct_site_id,
X_Primary_Flag => p_primary_flag,
X_Status => p_status,
X_Location => s_location,
X_Last_Update_Login => s_last_update_login,
X_Contact_Id => s_Contact_Id,
X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
X_Sic_Code => s_Sic_Code,
X_Payment_Term_Id => s_Payment_Term_Id,
X_Gsa_Indicator => s_Gsa_Indicator,
X_Ship_Partial => s_Ship_Partial,
X_Ship_Via => s_Ship_Via,
X_Fob_Point => s_Fob_Point,
X_Order_Type_Id => s_Order_Type_Id,
X_Price_List_Id => s_Price_List_Id,
X_Freight_Term => s_Freight_Term,
X_Warehouse_Id => s_Warehouse_Id,
X_Territory_Id => s_Territory_Id,
X_Tax_Code => s_Tax_Code,
X_Tax_Reference => s_Tax_Reference,
X_Demand_Class_Code => s_Demand_Class_Code,
x_inventory_location_id => p_inv_location_id,
x_inventory_organization_id => null,
X_Attribute_Category => s_attribute_category,
X_Attribute1 => s_attribute1,
X_Attribute2 => s_attribute2,
X_Attribute3 => s_attribute3,
X_Attribute4 => s_attribute4,
X_Attribute5 => s_attribute5,
X_Attribute6 => s_attribute6,
X_Attribute7 => s_attribute7,
X_Attribute8 => s_attribute8,
X_Attribute9 => s_attribute9,
X_Attribute10 => s_attribute10,
X_Attribute11 => s_attribute11,
X_Attribute12 => s_attribute12,
X_Attribute13 => s_attribute13,
X_Attribute14 => s_attribute14,
X_Attribute15 => s_attribute15,
X_Attribute16 => s_attribute16,
X_Attribute17 => s_attribute17,
X_Attribute18 => s_attribute18,
X_Attribute19 => s_attribute19,
X_Attribute20 => s_attribute20,
X_Attribute21 => s_attribute21,
X_Attribute22 => s_attribute22,
X_Attribute23 => s_attribute23,
X_Attribute24 => s_attribute24,
X_Attribute25 => s_attribute25,
X_Tax_Classification => s_Tax_Classification,
X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Primary_Salesrep_Id => s_primary_salesrep_id,
X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
X_GL_ID_Rec => s_GL_ID_Rec,
X_GL_ID_Rev => s_GL_ID_Rev,
X_GL_ID_Tax => s_GL_ID_Tax,
X_GL_ID_Freight => s_GL_ID_Freight,
X_GL_ID_Clearing => s_GL_ID_Clearing,
X_GL_ID_Unbilled => s_GL_ID_Unbilled,
X_GL_ID_Unearned => s_GL_ID_Unearned,
X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
X_GL_ID_Remittance => s_GL_ID_Remittance,
X_GL_ID_Factor => s_GL_ID_Factor,
X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
Rollback to do_update_site_use_PUB;
s_last_update_date,
s_last_updated_by,
s_creation_date,
s_created_by,
s_site_use_code,
s_primary_flag,
s_status,
s_location,
s_last_update_login,
s_contact_id,
s_bill_to_site_use_id,
s_orig_system_reference,
s_sic_code,
s_payment_term_id,
s_gsa_indicator,
s_ship_partial,
s_ship_via,
s_fob_point,
s_order_type_id,
s_price_list_id,
s_freight_term,
s_warehouse_id,
s_territory_id,
s_attribute_category,
s_attribute1,
s_attribute2,
s_attribute3,
s_attribute4,
s_attribute5,
s_attribute6,
s_attribute7,
s_attribute8,
s_attribute9,
s_attribute10,
s_request_id,
s_program_application_id,
s_program_id,
s_program_update_date,
s_tax_reference,
s_sort_priority,
s_tax_code,
s_attribute11,
s_attribute12,
s_attribute13,
s_attribute14,
s_attribute15,
s_attribute16,
s_attribute17,
s_attribute18,
s_attribute19,
s_attribute20,
s_attribute21,
s_attribute22,
s_attribute23,
s_attribute24,
s_attribute25,
s_last_accrue_charge_date,
s_snd_last_accrue_charge_date,
s_last_unaccrue_charge_date,
s_snd_last_unaccrue_chrg_date,
s_demand_class_code,
s_org_id,
s_tax_header_level_flag,
s_tax_rounding_rule,
s_wh_update_date,
s_global_attribute1,
s_global_attribute2,
s_global_attribute3,
s_global_attribute4,
s_global_attribute5,
s_global_attribute6,
s_global_attribute7,
s_global_attribute8,
s_global_attribute9,
s_global_attribute10,
s_global_attribute11,
s_global_attribute12,
s_global_attribute13,
s_global_attribute14,
s_global_attribute15,
s_global_attribute16,
s_global_attribute17,
s_global_attribute18,
s_global_attribute19,
s_global_attribute20,
s_global_attribute_category,
s_primary_salesrep_id,
s_finchrg_receivables_trx_id,
s_dates_negative_tolerance,
s_dates_positive_tolerance,
s_date_type_preference,
s_over_shipment_tolerance,
s_under_shipment_tolerance,
s_item_cross_ref_pref,
s_over_return_tolerance,
s_under_return_tolerance,
s_ship_sets_include_lines_flag,
s_arv_include_lines_flag,
s_sched_date_push_flag,
s_invoice_quantity_rule,
s_pricing_event,
s_gl_id_rec,
s_gl_id_rev,
s_gl_id_tax,
s_gl_id_freight,
s_gl_id_clearing,
s_gl_id_unbilled,
s_gl_id_unearned,
s_gl_id_unpaid_rec,
s_gl_id_remittance,
s_gl_id_factor,
s_tax_classification;
arh_csu_pkg.Update_Row(
X_Site_Use_Id => l_site_use_id,
X_Last_Update_Date => s_last_update_date,
X_Last_Updated_By => nvl(fnd_global.user_id, 1),
X_Site_Use_Code => s_site_use_code,
X_customer_id => p_customer_id,
X_Address_Id => s_cust_acct_site_id,
X_Primary_Flag => p_primary_flag,
X_Status => p_status,
X_Location => s_location,
X_Last_Update_Login => s_last_update_login,
X_Contact_Id => s_Contact_Id,
X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
X_Sic_Code => s_Sic_Code,
X_Payment_Term_Id => s_Payment_Term_Id,
X_Gsa_Indicator => s_Gsa_Indicator,
X_Ship_Partial => s_Ship_Partial,
X_Ship_Via => s_Ship_Via,
X_Fob_Point => s_Fob_Point,
X_Order_Type_Id => s_Order_Type_Id,
X_Price_List_Id => s_Price_List_Id,
X_Freight_Term => s_Freight_Term,
X_Warehouse_Id => s_Warehouse_Id,
X_Territory_Id => s_Territory_Id,
X_Tax_Code => s_Tax_Code,
X_Tax_Reference => s_Tax_Reference,
X_Demand_Class_Code => s_Demand_Class_Code,
x_inventory_location_id => p_inv_location_id,
x_inventory_organization_id => null,
X_Attribute_Category => s_attribute_category,
X_Attribute1 => s_attribute1,
X_Attribute2 => s_attribute2,
X_Attribute3 => s_attribute3,
X_Attribute4 => s_attribute4,
X_Attribute5 => s_attribute5,
X_Attribute6 => s_attribute6,
X_Attribute7 => s_attribute7,
X_Attribute8 => s_attribute8,
X_Attribute9 => s_attribute9,
X_Attribute10 => s_attribute10,
X_Attribute11 => s_attribute11,
X_Attribute12 => s_attribute12,
X_Attribute13 => s_attribute13,
X_Attribute14 => s_attribute14,
X_Attribute15 => s_attribute15,
X_Attribute16 => s_attribute16,
X_Attribute17 => s_attribute17,
X_Attribute18 => s_attribute18,
X_Attribute19 => s_attribute19,
X_Attribute20 => s_attribute20,
X_Attribute21 => s_attribute21,
X_Attribute22 => s_attribute22,
X_Attribute23 => s_attribute23,
X_Attribute24 => s_attribute24,
X_Attribute25 => s_attribute25,
X_Tax_Classification => s_Tax_Classification,
X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Primary_Salesrep_Id => s_primary_salesrep_id,
X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
X_GL_ID_Rec => s_GL_ID_Rec,
X_GL_ID_Rev => s_GL_ID_Rev,
X_GL_ID_Tax => s_GL_ID_Tax,
X_GL_ID_Freight => s_GL_ID_Freight,
X_GL_ID_Clearing => s_GL_ID_Clearing,
X_GL_ID_Unbilled => s_GL_ID_Unbilled,
X_GL_ID_Unearned => s_GL_ID_Unearned,
X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
X_GL_ID_Remittance => s_GL_ID_Remittance,
X_GL_ID_Factor => s_GL_ID_Factor,
X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
Rollback to do_update_site_use_PUB;
s_last_update_date,
s_last_updated_by,
s_creation_date,
s_created_by,
s_site_use_code,
s_primary_flag,
s_status,
s_location,
s_last_update_login,
s_contact_id,
s_bill_to_site_use_id,
s_orig_system_reference,
s_sic_code,
s_payment_term_id,
s_gsa_indicator,
s_ship_partial,
s_ship_via,
s_fob_point,
s_order_type_id,
s_price_list_id,
s_freight_term,
s_warehouse_id,
s_territory_id,
s_attribute_category,
s_attribute1,
s_attribute2,
s_attribute3,
s_attribute4,
s_attribute5,
s_attribute6,
s_attribute7,
s_attribute8,
s_attribute9,
s_attribute10,
s_request_id,
s_program_application_id,
s_program_id,
s_program_update_date,
s_tax_reference,
s_sort_priority,
s_tax_code,
s_attribute11,
s_attribute12,
s_attribute13,
s_attribute14,
s_attribute15,
s_attribute16,
s_attribute17,
s_attribute18,
s_attribute19,
s_attribute20,
s_attribute21,
s_attribute22,
s_attribute23,
s_attribute24,
s_attribute25,
s_last_accrue_charge_date,
s_snd_last_accrue_charge_date,
s_last_unaccrue_charge_date,
s_snd_last_unaccrue_chrg_date,
s_demand_class_code,
s_org_id,
s_tax_header_level_flag,
s_tax_rounding_rule,
s_wh_update_date,
s_global_attribute1,
s_global_attribute2,
s_global_attribute3,
s_global_attribute4,
s_global_attribute5,
s_global_attribute6,
s_global_attribute7,
s_global_attribute8,
s_global_attribute9,
s_global_attribute10,
s_global_attribute11,
s_global_attribute12,
s_global_attribute13,
s_global_attribute14,
s_global_attribute15,
s_global_attribute16,
s_global_attribute17,
s_global_attribute18,
s_global_attribute19,
s_global_attribute20,
s_global_attribute_category,
s_primary_salesrep_id,
s_finchrg_receivables_trx_id,
s_dates_negative_tolerance,
s_dates_positive_tolerance,
s_date_type_preference,
s_over_shipment_tolerance,
s_under_shipment_tolerance,
s_item_cross_ref_pref,
s_over_return_tolerance,
s_under_return_tolerance,
s_ship_sets_include_lines_flag,
s_arv_include_lines_flag,
s_sched_date_push_flag,
s_invoice_quantity_rule,
s_pricing_event,
s_gl_id_rec,
s_gl_id_rev,
s_gl_id_tax,
s_gl_id_freight,
s_gl_id_clearing,
s_gl_id_unbilled,
s_gl_id_unearned,
s_gl_id_unpaid_rec,
s_gl_id_remittance,
s_gl_id_factor,
s_tax_classification;
arh_csu_pkg.Update_Row(
X_Site_Use_Id => l_Bill_to_site_use_id,
X_Last_Update_Date => s_last_update_date,
X_Last_Updated_By => nvl(fnd_global.user_id, 1),
X_Site_Use_Code => s_site_use_code,
X_customer_id => p_customer_id,
X_Address_Id => s_cust_acct_site_id,
X_Primary_Flag => p_primary_flag,
X_Status => p_status,
X_Location => s_location,
X_Last_Update_Login => s_last_update_login,
X_Contact_Id => s_Contact_Id,
X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
X_Sic_Code => s_Sic_Code,
X_Payment_Term_Id => s_Payment_Term_Id,
X_Gsa_Indicator => s_Gsa_Indicator,
X_Ship_Partial => s_Ship_Partial,
X_Ship_Via => s_Ship_Via,
X_Fob_Point => s_Fob_Point,
X_Order_Type_Id => s_Order_Type_Id,
X_Price_List_Id => s_Price_List_Id,
X_Freight_Term => s_Freight_Term,
X_Warehouse_Id => s_Warehouse_Id,
X_Territory_Id => s_Territory_Id,
X_Tax_Code => s_Tax_Code,
X_Tax_Reference => s_Tax_Reference,
X_Demand_Class_Code => s_Demand_Class_Code,
x_inventory_location_id => p_inv_location_id,
x_inventory_organization_id => null,
X_Attribute_Category => s_attribute_category,
X_Attribute1 => s_attribute1,
X_Attribute2 => s_attribute2,
X_Attribute3 => s_attribute3,
X_Attribute4 => s_attribute4,
X_Attribute5 => s_attribute5,
X_Attribute6 => s_attribute6,
X_Attribute7 => s_attribute7,
X_Attribute8 => s_attribute8,
X_Attribute9 => s_attribute9,
X_Attribute10 => s_attribute10,
X_Attribute11 => s_attribute11,
X_Attribute12 => s_attribute12,
X_Attribute13 => s_attribute13,
X_Attribute14 => s_attribute14,
X_Attribute15 => s_attribute15,
X_Attribute16 => s_attribute16,
X_Attribute17 => s_attribute17,
X_Attribute18 => s_attribute18,
X_Attribute19 => s_attribute19,
X_Attribute20 => s_attribute20,
X_Attribute21 => s_attribute21,
X_Attribute22 => s_attribute22,
X_Attribute23 => s_attribute23,
X_Attribute24 => s_attribute24,
X_Attribute25 => s_attribute25,
X_Tax_Classification => s_Tax_Classification,
X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Primary_Salesrep_Id => s_primary_salesrep_id,
X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
X_GL_ID_Rec => s_GL_ID_Rec,
X_GL_ID_Rev => s_GL_ID_Rev,
X_GL_ID_Tax => s_GL_ID_Tax,
X_GL_ID_Freight => s_GL_ID_Freight,
X_GL_ID_Clearing => s_GL_ID_Clearing,
X_GL_ID_Unbilled => s_GL_ID_Unbilled,
X_GL_ID_Unearned => s_GL_ID_Unearned,
X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
X_GL_ID_Remittance => s_GL_ID_Remittance,
X_GL_ID_Factor => s_GL_ID_Factor,
X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
Rollback to do_update_site_use_PUB;
Rollback to do_update_site_use_PUB;
Rollback to do_update_site_use_PUB;
END do_update_site_use;
s_Last_Update_Login hz_locations.Last_Update_Login%TYPE := nvl(fnd_global.user_id,1);
s_update_account_site VARCHAR2(1) := 'N';
t_last_update_date DATE;
t_party_site_last_update_date DATE;
t_loc_last_update_date DATE;
select papf.party_id
from jtf_rs_resource_extns jrre,
per_all_people_f papf
where papf.person_id = jrre.source_id
and jrre.resource_id = l_resource_id
and decode(l_resource_type,'RS_EMPLOYEE','EMPLOYEE',null) = jrre.category
and trunc(sysdate) between trunc(papf.effective_start_date) and
trunc(papf.effective_end_date)
and jrre.category = 'EMPLOYEE'
UNION ALL
select hp.party_id
from jtf_rs_resource_extns jrre,
hz_parties hp
where hp.party_id = jrre.source_id
and jrre.resource_id = l_resource_id
and jrre.category = 'PARTY';
select resource_type_code, resource_id from jtf_task_assignments where task_assignment_id = p_task_assignment_id and assignee_role = 'ASSIGNEE';
select rs_cust_relation_id,
customer_id
from csp_rs_cust_relations
where resource_type = l_resource_type
and resource_id = l_resource_id;
select party_id from hz_cust_accounts where cust_account_id = l_customer_id;
select ship_to_location_id,
timezone_id
from csp_requirement_headers
where task_assignment_id = p_task_assignment_id;
select resource_name,
source_first_name,
source_middle_name,
source_last_name
from jtf_rs_resource_extns_vl
where category = substr(l_resource_type, 4) and resource_id = l_resource_id;
select hz_parties_s.nextval from dual;
select hz_cust_accounts_s.nextval from dual;
select hz_party_sites_s.nextval from dual;
select p.customer_id,
p.address_id,
p.site_use_id,
ps.location_id,
p.address_id,
ps.identifying_address_flag,
ps.status,
z.orig_system_reference,
ps.party_id,
c.party_site_id,
ps.party_site_number,
c.bill_to_flag,
c.market_flag,
c.ship_to_flag,
c.customer_category_code,
c.key_account_flag,
c.territory_id,
c.territory,
c.ece_tp_location_code,
c.translated_customer_name,
c.last_update_date,
ps.last_update_date,
z.last_update_date
from po_location_associations p, hz_locations z, hz_cust_acct_sites c, hz_party_sites ps
where p.address_id = c.cust_acct_site_id and c.party_site_id = ps.party_site_id and ps.location_id = z.location_id and p.location_id = l_inv_location_id;
select address_line_1, address_line_2, address_line_3, town_or_city, country, postal_code, region_1, region_2, region_3, 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 hr_locations where location_id = l_inv_location_id;
select identifying_address_flag from hz_party_sites where party_id = l_party_id and identifying_address_flag = 'Y' and status = 'A';
select APPLICATION_COLUMN_NAME
from FND_DESCR_FLEX_COL_USAGE_VL
where DESCRIPTIVE_FLEXFIELD_NAME like 'Address Location'
and upper(END_USER_COLUMN_NAME) like 'PROVINCE'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_style;
select attribute_category,
attribute1,attribute2,attribute3,attribute4,attribute5,
attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,
attribute16,attribute17,attribute18,attribute19,attribute20
from hz_cust_acct_sites_all where cust_acct_site_id = p_cust_acct_site_id;
SELECT 1
FROM hz_party_usage_rules
WHERE party_usage_rule_type = 'CANNOT_COEXIST'
AND party_usage_code = 'SUPPLIER'
AND RELATED_PARTY_USAGE_CODE = 'CUSTOMER'
AND EXISTS
(SELECT 1
FROM hz_party_usg_assignments
WHERE party_id = l_party_id
AND party_usage_code = 'SUPPLIER'
)
AND NVL(EFFECTIVE_START_DATE, sysdate-1) < sysdate
AND NVL(effective_end_date, sysdate +1) > sysdate;
select userenv('LANG') into s_language from dual;
l_process_type := 'INSERT';
do_update_ship_to_location(
p_location_id => p_location_id,
p_style => p_style,
p_address_line_1 => p_address_line_1,
p_address_line_2 => p_address_line_2,
p_address_line_3 => p_address_line_3,
p_country => p_country,
p_postal_code => p_postal_code,
p_region_1 => p_region_1,
p_region_2 => p_region_2,
p_region_3 => p_region_3,
p_town_or_city => p_town_or_city,
p_tax_name => p_tax_name,
p_telephone_number_1 => p_telephone_number_1,
p_telephone_number_2 => p_telephone_number_2,
p_telephone_number_3 => p_telephone_number_3,
p_loc_information13 => p_loc_information13,
p_loc_information14 => p_loc_information14,
p_loc_information15 => p_loc_information15,
p_loc_information16 => p_loc_information16,
p_loc_information17 => p_loc_information17,
p_loc_information18 => p_loc_information18,
p_loc_information19 => p_loc_information19,
p_loc_information20 => p_loc_information20,
p_object_version_number => p_object_version_number,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_attribute_category => p_attribute_category,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_process_type := 'UPDATE';
csp_customer_account_pvt.insert_person_row
(c_cust_account_id => l_customer_id,
c_party_id => l_party_id,
c_account_number => l_customer_number,
c_Attribute_Category => null,
c_Attribute1 => null,
c_Attribute2 => null,
c_Attribute3 => null,
c_Attribute4 => null,
c_Attribute5 => null,
c_Attribute6 => null,
c_Attribute7 => null,
c_Attribute8 => null,
c_Attribute9 => null,
c_Attribute10 => null,
c_Attribute11 => null,
c_Attribute12 => null,
c_Attribute13 => null,
c_Attribute14 => null,
c_Attribute15 => null,
c_Attribute16 => null,
c_Attribute17 => null,
c_Attribute18 => null,
c_Attribute19 => null,
c_Attribute20 => null,
c_global_attribute_category => l_global_attribute_category,
c_global_attribute1 => l_global_attribute1,
c_global_attribute2 => l_global_attribute2,
c_global_attribute3 => l_global_attribute3,
c_global_attribute4 => l_global_attribute4,
c_global_attribute5 => l_global_attribute5,
c_global_attribute6 => l_global_attribute6,
c_global_attribute7 => l_global_attribute7,
c_global_attribute8 => l_global_attribute8,
c_global_attribute9 => l_global_attribute9,
c_global_attribute10 => l_global_attribute10,
c_global_attribute11 => l_global_attribute11,
c_global_attribute12 => l_global_attribute12,
c_global_attribute13 => l_global_attribute13,
c_global_attribute14 => l_global_attribute14,
c_global_attribute15 => l_global_attribute15,
c_global_attribute16 => l_global_attribute16,
c_global_attribute17 => l_global_attribute17,
c_global_attribute18 => l_global_attribute18,
c_global_attribute19 => l_global_attribute19,
c_global_attribute20 => l_global_attribute20,
c_orig_system_reference => l_orig_system_reference,
c_status => l_status,
c_customer_type => l_customer_type,
c_customer_class_code => l_customer_class_code,
c_primary_salesrep_id => l_primary_salesrep_id,
c_sales_channel_code => l_sales_channel_code,
c_order_type_id => l_order_type_id,
c_price_list_id => l_price_list_id,
c_category_code => l_category_code,
c_reference_use_flag => l_reference_use_flag,
c_tax_code => l_tax_code,
c_third_party_flag => l_third_party_flag,
c_competitor_flag => l_competitor_flag,
c_fob_point => l_fob_point,
c_tax_header_level_flag => l_tax_header_level_flag,
c_tax_rounding_rule => l_tax_rounding_rule,
c_account_name => l_account_name,
c_freight_term => l_freight_term,
c_ship_partial => l_ship_partial,
c_ship_via => l_ship_via,
c_warehouse_id => l_warehouse_id,
c_payment_term_id => l_payment_term_id,
c_DATES_NEGATIVE_TOLERANCE => l_DATES_NEGATIVE_TOLERANCE,
c_DATES_POSITIVE_TOLERANCE => l_DATES_POSITIVE_TOLERANCE,
c_DATE_TYPE_PREFERENCE => l_DATE_TYPE_PREFERENCE,
c_OVER_SHIPMENT_TOLERANCE => l_OVER_SHIPMENT_TOLERANCE,
c_UNDER_SHIPMENT_TOLERANCE => l_UNDER_SHIPMENT_TOLERANCE,
c_ITEM_CROSS_REF_PREF => l_ITEM_CROSS_REF_PREF,
c_OVER_RETURN_TOLERANCE => l_OVER_RETURN_TOLERANCE,
c_UNDER_RETURN_TOLERANCE => l_UNDER_RETURN_TOLERANCE,
c_SHIP_SETS_INCLUDE_LINES_FLAG => l_SHIP_SETS_INCLUDE_LINES_FLAG,
c_ARRIVALSETS_INCL_LINES_FLAG => l_ARRIVALSETS_INCL_LINES_FLAG,
c_SCHED_DATE_PUSH_FLAG => l_SCHED_DATE_PUSH_FLAG,
c_INVOICE_QUANTITY_RULE => l_INVOICE_QUANTITY_RULE,
t_party_id => l_party_id,
t_party_number => l_party_number,
t_customer_key => l_customer_key,
t_Attribute_Category => l_Attribute_Category,
t_Attribute1 => l_Attribute1,
t_Attribute2 => l_Attribute2,
t_Attribute3 => l_Attribute3,
t_Attribute4 => l_Attribute4,
t_Attribute5 => l_Attribute5,
t_Attribute6 => l_Attribute6,
t_Attribute7 => l_Attribute7,
t_Attribute8 => l_Attribute8,
t_Attribute9 => l_Attribute9,
t_Attribute10 => l_Attribute10,
t_Attribute11 => l_Attribute11,
t_Attribute12 => l_Attribute12,
t_Attribute13 => l_Attribute13,
t_Attribute14 => l_Attribute14,
t_Attribute15 => l_Attribute15,
t_Attribute16 => l_Attribute16,
t_Attribute17 => l_Attribute17,
t_Attribute18 => l_Attribute18,
t_Attribute19 => l_Attribute19,
t_Attribute20 => l_Attribute20,
t_global_attribute_category => l_global_attribute_category,
t_global_attribute1 => l_global_attribute1,
t_global_attribute2 => l_global_attribute2,
t_global_attribute3 => l_global_attribute3,
t_global_attribute4 => l_global_attribute4,
t_global_attribute5 => l_global_attribute5,
t_global_attribute6 => l_global_attribute6,
t_global_attribute7 => l_global_attribute7,
t_global_attribute8 => l_global_attribute8,
t_global_attribute9 => l_global_attribute9,
t_global_attribute10 => l_global_attribute10,
t_global_attribute11 => l_global_attribute11,
t_global_attribute12 => l_global_attribute12,
t_global_attribute13 => l_global_attribute13,
t_global_attribute14 => l_global_attribute14,
t_global_attribute15 => l_global_attribute15,
t_global_attribute16 => l_global_attribute16,
t_global_attribute17 => l_global_attribute17,
t_global_attribute18 => l_global_attribute18,
t_global_attribute19 => l_global_attribute19,
t_global_attribute20 => l_global_attribute20,
o_pre_name_adjunct => l_pre_name_adjunct,
o_first_name => l_first_name,
o_middle_name => l_middle_name,
o_last_name => l_last_name,
o_name_suffix => l_name_suffix,
o_tax_reference => l_tax_reference,
o_taxpayer_id => l_taxpayer_id,
o_party_name_phonetic => l_party_name_phonetic,
p_cust_account_profile_id => l_customer_profile_id,
p_cust_account_id => l_customer_id,
p_status => l_status,
p_collector_id => l_collector_id,
p_credit_analyst_id => l_credit_analyst_id,
p_credit_checking => l_credit_checking,
p_next_credit_review_date => l_next_credit_review_date,
p_tolerance => l_tolerance,
p_discount_terms => l_discount_terms,
p_dunning_letters => l_dunning_letters,
p_interest_charges => l_interest_charges,
p_send_statements => l_send_statements,
p_credit_balance_statements => l_credit_balance_statements,
p_credit_hold => l_credit_hold,
p_profile_class_id => l_customer_profile_class_id,
p_site_use_id => l_site_use_id,
p_credit_rating => l_credit_rating,
p_risk_code => l_risk_code,
p_standard_terms => l_standard_terms,
p_override_terms => l_override_terms,
p_dunning_letter_set_id => l_dunning_letter_set_id,
p_interest_period_days => l_interest_period_days,
p_payment_grace_days => l_payment_grace_days,
p_discount_grace_days => l_discount_grace_days,
p_statement_cycle_id => l_statement_cycle_id,
p_account_status => l_account_status,
p_percent_collectable => l_percent_collectable,
p_autocash_hierarchy_id => l_autocash_hierarchy_id,
p_Attribute_Category => l_Attribute_Category,
p_Attribute1 => l_Attribute1,
p_Attribute2 => l_Attribute2,
p_Attribute3 => l_Attribute3,
p_Attribute4 => l_Attribute4,
p_Attribute5 => l_Attribute5,
p_Attribute6 => l_Attribute6,
p_Attribute7 => l_Attribute7,
p_Attribute8 => l_Attribute8,
p_Attribute9 => l_Attribute9,
p_Attribute10 => l_Attribute10,
p_Attribute11 => l_Attribute11,
p_Attribute12 => l_Attribute12,
p_Attribute13 => l_Attribute13,
p_Attribute14 => l_Attribute14,
p_Attribute15 => l_Attribute15,
p_auto_rec_incl_disputed_flag => l_auto_rec_incl_disputed_flag,
p_tax_printing_option => l_tax_printing_option,
p_charge_on_fin_charge_flag => l_charge_on_fin_charge_flag,
p_grouping_rule_id => l_grouping_rule_id,
p_clearing_days => l_clearing_days,
p_jgzz_attribute_category => l_jgzz_attribute_category,
p_jgzz_attribute1 => l_jgzz_attribute1,
p_jgzz_attribute2 => l_jgzz_attribute2,
p_jgzz_attribute3 => l_jgzz_attribute3,
p_jgzz_attribute4 => l_jgzz_attribute4,
p_jgzz_attribute5 => l_jgzz_attribute5,
p_jgzz_attribute6 => l_jgzz_attribute6,
p_jgzz_attribute7 => l_jgzz_attribute7,
p_jgzz_attribute8 => l_jgzz_attribute8,
p_jgzz_attribute9 => l_jgzz_attribute9,
p_jgzz_attribute10 => l_jgzz_attribute10,
p_jgzz_attribute11 => l_jgzz_attribute11,
p_jgzz_attribute12 => l_jgzz_attribute12,
p_jgzz_attribute13 => l_jgzz_attribute13,
p_jgzz_attribute14 => l_jgzz_attribute14,
p_jgzz_attribute15 => l_jgzz_attribute15,
p_global_attribute_category => l_global_attribute_category,
p_global_attribute1 => l_global_attribute1,
p_global_attribute2 => l_global_attribute2,
p_global_attribute3 => l_global_attribute3,
p_global_attribute4 => l_global_attribute4,
p_global_attribute5 => l_global_attribute5,
p_global_attribute6 => l_global_attribute6,
p_global_attribute7 => l_global_attribute7,
p_global_attribute8 => l_global_attribute8,
p_global_attribute9 => l_global_attribute9,
p_global_attribute10 => l_global_attribute10,
p_global_attribute11 => l_global_attribute11,
p_global_attribute12 => l_global_attribute12,
p_global_attribute13 => l_global_attribute13,
p_global_attribute14 => l_global_attribute14,
p_global_attribute15 => l_global_attribute15,
p_global_attribute16 => l_global_attribute16,
p_global_attribute17 => l_global_attribute17,
p_global_attribute18 => l_global_attribute18,
p_global_attribute19 => l_global_attribute19,
p_global_attribute20 => l_global_attribute20,
p_cons_inv_flag => l_cons_inv_flag,
p_cons_inv_type => l_cons_inv_type,
p_autocash_hier_id_for_adr => l_autocash_hier_id_for_adr,
p_lockbox_matching_option => l_lockbox_matching_option,
o_person_profile_id => l_person_profile_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
t_last_update_date,
t_party_site_last_update_date,
t_loc_last_update_date;
update hz_locations set time_zone = l_timezone_id where location_id = t_site_loc_id;
do_update_site_use
(p_site_use_id => t_site_use_id
,p_primary_flag => p_primary_flag
,p_status => p_status
,p_customer_id => l_customer_id
,p_inv_location_id => l_inv_location_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
select last_update_date into t_last_update_date from hz_cust_acct_sites where CUST_ACCT_SITE_ID = t_cust_acct_site_id;
arh_addr_pkg.update_row (
X_Address_Id => t_cust_acct_site_id,
X_Last_Update_Date => t_last_update_date,
X_party_site_Last_Update_Date => t_party_site_last_update_date,
X_loc_Last_Update_Date => t_loc_last_update_date,
X_Last_Updated_By => nvl(fnd_global.user_id, 1),
X_Status => t_status,
X_Orig_System_Reference => t_orig_system_reference,
X_Country => s_country,
X_Address1 => s_address1,
X_Address2 => s_address2,
X_Address3 => s_address3,
X_Address4 => s_address4,
X_City => s_city,
X_Postal_Code => s_postal_code,
X_State => s_state,
X_Province => s_province,
X_County => s_county,
X_Last_Update_Login => nvl(fnd_global.user_id,1),
X_Address_Key => s_address_key,
X_Language => null,--s_language,
X_Attribute_Category => l_attribute_category,
X_Attribute1 => l_attribute1,
X_Attribute2 => l_attribute2,
X_Attribute3 => l_attribute3,
X_Attribute4 => l_attribute4,
X_Attribute5 => l_attribute5,
X_Attribute6 => l_attribute6,
X_Attribute7 => l_attribute7,
X_Attribute8 => l_attribute8,
X_Attribute9 => l_attribute9,
X_Attribute10 => l_attribute10,
X_Attribute11 => l_attribute11,
X_Attribute12 => l_attribute12,
X_Attribute13 => l_attribute13,
X_Attribute14 => l_attribute14,
X_Attribute15 => l_attribute15,
X_Attribute16 => l_attribute16,
X_Attribute17 => l_attribute17,
X_Attribute18 => l_attribute18,
X_Attribute19 => l_attribute19,
X_Attribute20 => l_attribute20,
X_Address_warning => s_address_warning,
X_Address_Lines_Phonetic => s_address_lines_phonetic,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Party_site_id => t_party_site_id,
X_Party_id => t_party_id,
X_Location_id => t_site_loc_id,
X_Party_Site_Number => t_party_site_number,
X_Identifying_address_flag => t_primary_flag,
X_Cust_acct_site_id => t_cust_acct_site_id,
X_Cust_account_id => l_customer_id,
X_su_Bill_To_Flag => t_bill_to_flag,
X_su_Ship_To_Flag => t_ship_to_flag,
X_su_Market_Flag => t_market_flag,
X_su_stmt_flag => s_su_stmt_flag,
X_su_dun_flag => s_su_dun_flag,
X_su_legal_flag => s_su_legal_flag,
X_Customer_Category => t_customer_category,
X_Key_Account_Flag => t_key_account_flag,
X_Territory_id => t_territory_id,
X_ece_tp_location_code => t_ece_tp_location_code,
x_address_mode => s_address_mode,
X_Territory => t_territory,
X_Translated_Customer_Name => t_translated_customer_name,
X_Sales_Tax_Geocode => s_sales_tax_geo_code,
X_Sales_Tax_Inside_City_Limits => s_sale_tax_inside_city_limits,
x_ADDRESSEE => s_addressee,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
arh_addr_pkg.update_row (
X_Address_Id => t_cust_acct_site_id,
X_Last_Update_Date => t_last_update_date,
X_party_site_Last_Update_Date => t_party_site_last_update_date,
X_loc_Last_Update_Date => t_loc_last_update_date,
X_Last_Updated_By => nvl(fnd_global.user_id, 1),
X_Status => t_status,
X_Orig_System_Reference => t_orig_system_reference,
X_Country => s_country,
X_Address1 => s_address1,
X_Address2 => s_address2,
X_Address3 => s_address3,
X_Address4 => s_address4,
X_City => s_city,
X_Postal_Code => s_postal_code,
X_State => s_state,
X_Province => s_province,
X_County => s_county,
X_Last_Update_Login => nvl(fnd_global.user_id,1),
X_Address_Key => s_address_key,
X_Language => null,--s_language,
X_Attribute_Category => l_attribute_category,
X_Attribute1 => l_attribute1,
X_Attribute2 => l_attribute2,
X_Attribute3 => l_attribute3,
X_Attribute4 => l_attribute4,
X_Attribute5 => l_attribute5,
X_Attribute6 => l_attribute6,
X_Attribute7 => l_attribute7,
X_Attribute8 => l_attribute8,
X_Attribute9 => l_attribute9,
X_Attribute10 => l_attribute10,
X_Attribute11 => l_attribute11,
X_Attribute12 => l_attribute12,
X_Attribute13 => l_attribute13,
X_Attribute14 => l_attribute14,
X_Attribute15 => l_attribute15,
X_Attribute16 => l_attribute16,
X_Attribute17 => l_attribute17,
X_Attribute18 => l_attribute18,
X_Attribute19 => l_attribute19,
X_Attribute20 => l_attribute20,
X_Address_warning => s_address_warning,
X_Address_Lines_Phonetic => s_address_lines_phonetic,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Party_site_id => t_party_site_id,
X_Party_id => t_party_id,
X_Location_id => t_site_loc_id,
X_Party_Site_Number => t_party_site_number,
X_Identifying_address_flag => t_primary_flag,
X_Cust_acct_site_id => t_cust_acct_site_id,
X_Cust_account_id => l_customer_id,
X_su_Bill_To_Flag => t_bill_to_flag,
X_su_Ship_To_Flag => t_ship_to_flag,
X_su_Market_Flag => t_market_flag,
X_su_stmt_flag => s_su_stmt_flag,
X_su_dun_flag => s_su_dun_flag,
X_su_legal_flag => s_su_legal_flag,
X_Customer_Category => t_customer_category,
X_Key_Account_Flag => t_key_account_flag,
X_Territory_id => t_territory_id,
X_ece_tp_location_code => t_ece_tp_location_code,
x_address_mode => s_address_mode,
X_Territory => t_territory,
X_Translated_Customer_Name => t_translated_customer_name,
X_Sales_Tax_Geocode => s_sales_tax_geo_code,
X_Sales_Tax_Inside_City_Limits => s_sale_tax_inside_city_limits,
x_ADDRESSEE => s_addressee,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
do_update_site_use
(p_site_use_id => t_site_use_id
,p_primary_flag => p_primary_flag
,p_status => p_status
,p_customer_id => l_customer_id
,p_inv_location_id => l_inv_location_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM DUAL;
arh_addr_pkg.insert_row(
X_Address_Id => s_cust_acct_site_id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => nvl(fnd_global.user_id,1),
X_Creation_Date => sysdate,
X_Created_By => nvl(fnd_global.user_id,1),
X_Status => s_status,
X_Orig_System_Reference => s_orig_system_reference,
X_Country => s_country,
X_Address1 => s_address1,
X_Address2 => s_address2,
X_Address3 => s_address3,
X_Address4 => s_address4,
X_City => s_city,
X_Postal_Code => s_postal_code,
X_State => s_state,
X_Province => s_province,
X_County => s_county,
X_Last_Update_Login => nvl(fnd_global.user_id,1),
X_Address_Key => s_address_key,
X_Language => null,--s_language,
X_Attribute_Category => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute16 => null,
X_Attribute17 => null,
X_Attribute18 => null,
X_Attribute19 => null,
X_Attribute20 => null,
X_Address_warning => s_address_warning,
X_Address_Lines_Phonetic => s_Address_Lines_Phonetic,
X_Global_Attribute_Category => s_global_attribute_category,
X_Global_Attribute1 => s_global_attribute1,
X_Global_Attribute2 => s_global_attribute2,
X_Global_Attribute3 => s_global_attribute3,
X_Global_Attribute4 => s_global_attribute4,
X_Global_Attribute5 => s_global_attribute5,
X_Global_Attribute6 => s_global_attribute6,
X_Global_Attribute7 => s_global_attribute7,
X_Global_Attribute8 => s_global_attribute8,
X_Global_Attribute9 => s_global_attribute9,
X_Global_Attribute10 => s_global_attribute10,
X_Global_Attribute11 => s_global_attribute11,
X_Global_Attribute12 => s_global_attribute12,
X_Global_Attribute13 => s_global_attribute13,
X_Global_Attribute14 => s_global_attribute14,
X_Global_Attribute15 => s_global_attribute15,
X_Global_Attribute16 => s_global_attribute16,
X_Global_Attribute17 => s_global_attribute17,
X_Global_Attribute18 => s_global_attribute18,
X_Global_Attribute19 => s_global_attribute19,
X_Global_Attribute20 => s_global_attribute20,
X_Party_site_id => s_party_site_id,
X_Party_id => l_party_id,
X_Location_id => s_location_id,
X_Party_Site_Number => s_party_site_number,
X_Identifying_address_flag => s_identifying_address_flag,
X_Cust_acct_site_id => s_address_id,
X_Cust_account_id => l_customer_id,
X_su_Bill_To_Flag => s_su_bill_to_flag,
X_su_Ship_To_Flag => s_su_ship_to_flag,
X_su_Market_Flag => s_su_market_flag,
X_su_stmt_flag => s_su_stmt_flag,
X_su_dun_flag => s_su_dun_flag,
X_su_legal_flag => s_su_legal_flag,
X_Customer_Category => s_customer_category,
X_Key_Account_Flag => s_key_account_flag,
X_Territory_id => s_territory_id,
X_ece_tp_location_code => s_ece_tp_location_code,
x_address_mode => s_address_mode,
x_territory => s_territory,
x_translated_customer_name => s_translated_customer_name,
x_sales_tax_geo_code => s_sales_tax_geo_code,
x_sale_tax_inside_city_limits => s_sale_tax_inside_city_limits,
x_ADDRESSEE => s_addressee,
x_shared_party_site => s_shared_party_site,
x_update_account_site => s_update_account_site,
x_create_location_party_site => s_create_location_party_site,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
update hz_locations set time_zone = l_timezone_id where location_id = s_location_id;
IF l_process_type = 'INSERT' THEN
csp_ship_to_address_pvt.call_internal_hook('CSP_SHIP_TO_ADDRESS_PVT','SHIP_TO_ADDRESS_HANDLER','A',x_return_status);
ELSIF l_process_type = 'UPDATE' THEN
csp_ship_to_address_pvt.call_internal_hook('CSP_SHIP_TO_ADDRESS_PVT','UPDATE_LOCATION','A',x_return_status);
arp_clas_pkg.insert_po_loc_associations(
p_inventory_location_id => l_inv_loc_id,
p_inventory_organization_id => null,
p_customer_id => p_customer_id,
p_address_id => p_address_id,
p_site_use_id => p_site_use_id);
select STYLE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,COUNTRY
,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,TELEPHONE_NUMBER_3,LOC_INFORMATION13
,LOC_INFORMATION14,LOC_INFORMATION15,LOC_INFORMATION16,LOC_INFORMATION17,LOC_INFORMATION18,LOC_INFORMATION19
,LOC_INFORMATION20,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,TAX_NAME
from hr_locations
where location_code like 'CSP%'
and country = p_country ;
select STYLE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,COUNTRY
,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,TELEPHONE_NUMBER_3,LOC_INFORMATION13
,LOC_INFORMATION14,LOC_INFORMATION15,LOC_INFORMATION16,LOC_INFORMATION17,LOC_INFORMATION18,LOC_INFORMATION19
,LOC_INFORMATION20,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,TAX_NAME
from hr_locations
where country = p_country ;
SELECT org_id
INTO l_org_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_acct_site_id = p_address_id;
select operating_unit
into l_ou_id
from org_organization_Definitions
where organization_id = l_org_id;
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM dual;
'Before calling arp_clas_pkg.insert_po_loc_associations');
select cust_account_id into l_cust_acct_id
from hz_cust_acct_sites_all
where cust_acct_site_id = p_address_id;
arp_clas_pkg.insert_po_loc_associations(
p_inventory_location_id => l_inv_loc_id,
p_inventory_organization_id => null,
p_customer_id => l_cust_acct_id,
p_address_id => p_address_id,
p_site_use_id => p_site_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After calling arp_clas_pkg.insert_po_loc_associations');
SELECT org_id
INTO l_org_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_acct_site_id = p_address_id;
select operating_unit
into l_ou_id
from org_organization_Definitions
where organization_id = l_org_id;
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM dual;
'Before arp_clas_pkg.insert_po_loc_associations...');
arp_clas_pkg.insert_po_loc_associations(
p_inventory_location_id => l_inv_loc_id,
p_inventory_organization_id => null,
p_customer_id => p_customer_id,
p_address_id => p_address_id,
p_site_use_id => p_site_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After arp_clas_pkg.insert_po_loc_associations...');
SELECT hook_package, hook_api
FROM jtf_hooks_data
WHERE package_name = p_package_name
AND api_name = p_api_name
AND execute_flag = 'Y'
AND processing_type = p_processing_type
ORDER BY execution_order;
SELECT c_site_use.site_use_id, c_acct_site.cust_acct_site_id
FROM hz_cust_site_uses_all c_site_use,
hz_cust_acct_sites_all c_acct_site
WHERE c_site_use.site_use_code = 'SHIP_TO'
AND c_site_use.status = 'A'
AND c_site_use.cust_acct_site_id = c_acct_site.cust_acct_site_id
AND c_acct_site.cust_account_id = p_cust_account_id
AND c_acct_site.party_site_id = p_party_site_id
AND c_acct_site.org_id = l_org_id;
SELECT location_id
FROM po_location_associations_all
WHERE site_use_id = l_site_use_id;
SELECT location_id
FROM po_location_Associations_all
WHERE location_id = l_inv_location_id;
SELECT country, address1, address2, address3,
address4, city, postal_code, state,
province, county
FROM hz_locations
WHERE location_id = px_location_id;
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM DUAL;*/
select city_name, county_name, state_abbrev
into l_city_name, l_county_name, l_state_abbrev
from pay_us_city_names ct,
pay_us_states st,
pay_us_counties c
where ct.state_code = st.state_code
and ct.county_code = c.county_code
and c.state_code = st.state_code
and st.state_abbrev = l_state
and upper(c.county_name) = upper(l_county)
and upper(ct.city_name) = upper(l_city);
SELECT
site_use_id,
customer_id,
inv_loc_id,
status
FROM
csp_rs_ship_to_addresses_v
WHERE
resource_id = p_rs_id
AND resource_type = p_rs_type
AND primary_flag = 'Y';
SELECT
customer_id,
inv_loc_id,
status
FROM
csp_rs_ship_to_addresses_v
WHERE
resource_id = p_rs_id
AND resource_type = p_rs_type
AND site_use_id = p_rs_site_use_id;
4. Update customer account site record to non primary
5. Make new site_use_id as primary
6. Update customer account site record to primary
7. COMMIT is required
*/
-- Step 1: Get current Primary Ship To address
open rs_current_primary_addr;
DO_UPDATE_SITE_USE(
P_SITE_USE_ID => l_old_site_use_id,
P_PRIMARY_FLAG => 'N',
P_STATUS => l_old_status,
P_CUSTOMER_ID => l_customer_id,
P_INV_LOCATION_ID => l_old_inv_loc_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
update HZ_CUST_ACCT_SITES_ALL
set bill_to_flag = 'Y',
ship_to_flag = 'Y'
where cust_account_id = l_customer_id
and cust_acct_site_id = (
select cust_acct_site_id from HZ_CUST_SITE_USES_ALL
where site_use_id = l_old_site_use_id);
DO_UPDATE_SITE_USE(
P_SITE_USE_ID => p_rs_site_use_id,
P_PRIMARY_FLAG => 'Y',
P_STATUS => l_new_status,
P_CUSTOMER_ID => l_customer_id,
P_INV_LOCATION_ID => l_new_inv_loc_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
update HZ_CUST_ACCT_SITES_ALL
set bill_to_flag = 'P',
ship_to_flag = 'P'
where cust_account_id = l_customer_id
and cust_acct_site_id = (
select cust_acct_site_id from HZ_CUST_SITE_USES_ALL
where site_use_id = p_rs_site_use_id);
SELECT
customer_id,
inv_loc_id,
primary_flag
FROM
csp_rs_ship_to_addresses_v
WHERE
resource_id = p_rs_id
AND resource_type = p_rs_type
AND site_use_id = p_rs_site_use_id;
2. Call DO_UPDATE_SITE_USE to update it
3. COMMIT if required
*/
-- Step 1: Get ship_to address detail
open rs_get_ship_to_addr;
DO_UPDATE_SITE_USE(
P_SITE_USE_ID => p_rs_site_use_id,
P_PRIMARY_FLAG => l_primary_flag,
P_STATUS => 'I',
P_CUSTOMER_ID => l_customer_id,
P_INV_LOCATION_ID => l_inv_loc_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
select site_use_id
from hz_cust_site_uses_all
where cust_acct_site_id = p_cust_site_id;
select cust_acct_site_id
from HZ_CUST_SITE_USES_ALL
where site_use_code = 'BILL_TO'
and site_use_id = v_ship_bill_site;
select newu.site_use_id
from HZ_CUST_SITE_USES_ALL orgu,
HZ_CUST_ACCT_SITES_ALL orgs,
HZ_CUST_SITE_USES_ALL newu,
HZ_CUST_ACCT_SITES_ALL news
where orgu.site_use_code = 'BILL_TO'
and orgu.site_use_id = v_ship_bill_site
and orgu.cust_acct_site_id = orgs.cust_acct_site_id
and news.party_site_id = orgs.party_site_id
and news.cust_acct_site_id = newu.cust_acct_site_id
and newu.site_use_code = 'BILL_TO'
and newu.location = orgu.location
and news.org_id = p_dest_org_id;
-- update inventory_location link for this new site_use
po_moac_utils_pvt.set_org_context(p_dest_org_id);
arp_clas_pkg.insert_po_loc_associations(
p_inventory_location_id => p_hr_location_id,
p_inventory_organization_id => p_dest_org_id,
p_customer_id => p_customer_id,
p_address_id => x_cust_acct_site_id,
p_site_use_id => temp_ship_to_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'arp_clas_pkg.insert_po_loc_associations... x_return_status=' || x_return_status);
SELECT
hra.address_line_1,
hra.address_line_2,
hra.address_line_3,
hra.town_or_city,
hra.postal_code,
hra.country,
hra.style,
hra.region_1,
hra.region_2,
hra.region_3,
hra.tax_name,
hra.telephone_number_1,
hra.telephone_number_2,
hra.telephone_number_3,
hra.loc_information13,
hra.loc_information14,
hra.loc_information15,
hra.loc_information16,
hra.loc_information17,
hra.loc_information18,
hra.loc_information19,
hra.loc_information20,
hra.object_version_number,
hra.attribute_category,
hra.attribute1,
hra.attribute2,
hra.attribute3,
hra.attribute4,
hra.attribute5,
hra.attribute6,
hra.attribute7,
hra.attribute8,
hra.attribute9,
hra.attribute10,
hra.attribute11,
hra.attribute12,
hra.attribute13,
hra.attribute14,
hra.attribute15,
hra.attribute16,
hra.attribute17,
hra.attribute18,
hra.attribute19,
hra.attribute20
FROM hr_locations_all hra
where hra.location_id = p_location_id;