[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_loc_updated IN OUT NOCOPY VARCHAR2);
to a given address format. Line breaks are inserted in order for the
segments to be allocated inside the given box dimension.
If the box size is not big enough to contain all the required
segment together with segment joint characters(spaces/commas),
or the box width is not long enough to contain any segment,
then the function truncates the string to provide the possible output.
REQUIRES
address_style : address format style
address1 : address line 1
address2 : address line 2
address3 : address line 3
address4 : address line 4
city : name of city
county : name of county
state : name of state
province : name of province
postal_code : postal code
territory_short_name : territory short name
OPTIONAL REQUIRES
country_code : country code
customer_name : customer name
first_name : contact first name
last_name : contact last name
mail_stop : mailing informatioin
default_country_code : default country code
default_country_desc : default territory short name
print_home_country_flag : flag to control home county printing
print_default_attn_flag : flag to control default attention message
width NUMBER : address box width
height_min : address box minimum height
height_max : address box maximum height
RETURN
formatted address string
+--------------------------------------------------------------------*/
FUNCTION format_address( address_style IN VARCHAR2,
address1 IN VARCHAR2,
address2 IN VARCHAR2,
address3 IN VARCHAR2,
address4 IN VARCHAR2,
city IN VARCHAR2,
county IN VARCHAR2,
state IN VARCHAR2,
province IN VARCHAR2,
postal_code IN VARCHAR2,
territory_short_name IN VARCHAR2,
country_code IN VARCHAR2 default NULL,
customer_name IN VARCHAR2 default NULL,
first_name IN VARCHAR2 default NULL,
last_name IN VARCHAR2 default NULL,
mail_stop IN VARCHAR2 default NULL,
default_country_code IN VARCHAR2,
default_country_desc IN VARCHAR2,
print_home_country_flag IN VARCHAR2,
print_default_attn_flag IN VARCHAR2,
width IN NUMBER,
height_min IN NUMBER,
height_max IN NUMBER
)return VARCHAR2 IS
l_fmt_bkwd_compatible VARCHAR2(10) :='Y';
PROCEDURE insert_site_use( p_customer_id in number,
p_address_id in number,
p_site_use_code in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) is
--
l_rowid varchar2(18);
arh_csu_pkg.insert_row(
X_Site_Use_Id => l_site_use_id,
X_Last_Update_Date => sysdate,
x_Last_Updated_By => fnd_global.user_id,
X_Creation_Date => sysdate,
X_Created_By => fnd_global.user_id,
X_Site_Use_Code => p_site_use_code,
X_customer_id => p_customer_id,
X_Address_Id => p_address_id,
X_Primary_Flag => 'N',
X_Status => 'A',
X_Location => l_location,
X_Last_Update_Login => fnd_global.Login_id,
X_Contact_Id => null,
X_Bill_To_Site_Use_Id => null,
X_Sic_Code => null,
X_Payment_Term_Id => null,
X_Gsa_Indicator => null,
X_Ship_Partial => null,
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 => null,
X_ARRIVALSETS_INCL_LINES_FLAG => null,
X_SCHED_DATE_PUSH_FLAG => null,
X_INVOICE_QUANTITY_RULE => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
end insert_site_use;
arh_csu_pkg.update_su_status(p_customer_id => p_customer_id,
p_address_id => p_address_id,
p_site_use_id => l_site_use_id,
p_site_use_code => p_site_use_code,
p_status =>'A');
insert_site_use(p_customer_id, p_address_id,p_site_use_code,x_msg_count,
x_msg_data,x_return_status);
arh_csu_pkg.update_su_status(p_customer_id => p_customer_id,
p_address_id => p_address_id,
p_site_use_id => l_site_use_id,
p_site_use_code => p_site_use_code,
p_status => 'I');
procedure update_site_use_flag ( p_address_id in number,
p_site_use_code in varchar2,
p_site_use_flag in varchar2 ) is
--
--
begin
--
-- The procedure is only callled if one of the flags requires updating
-- therfore the if then else logic has been pushed into the sql
--
update hz_cust_acct_sites
set bill_to_flag = decode(p_site_use_code,
'BILL_TO',p_site_use_flag,
bill_to_flag),
ship_to_flag = decode(p_site_use_code,
'SHIP_TO',p_site_use_flag,
ship_to_flag),
market_flag = decode(p_site_use_code,
'MARKET',p_site_use_flag,
market_flag)
where cust_acct_site_id = p_address_id;
end update_site_use_flag;
update hz_party_sites set identifying_address_flag = 'N'
where party_id = x_party_id and identifying_address_flag = 'Y';
select count(1)
into dummy
from hz_cust_acct_sites addr
where addr.orig_system_reference = p_orig_system_reference;
PROCEDURE delete_customer_alt_names(p_rowid in varchar2,
p_status in varchar2,
p_customer_id in number,
p_address_id in number ) is
l_status varchar2(1);
select status
into l_status
from hz_cust_acct_sites
where rowid = p_rowid;
select site_use_id
into l_site_use_id
from hz_cust_site_uses
where cust_acct_site_id = p_address_id
and site_use_code = 'BILL_TO'
and status = 'A';
arp_cust_alt_match_pkg.delete_match ( p_customer_id, l_site_use_id , NULL) ;
arp_standard.debug('EXCEPTION: arh_addr_pkg.delete_customer_alt_names');
end delete_customer_alt_names;
PROCEDURE insert_row (
X_Address_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Status VARCHAR2,
X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
X_Country VARCHAR2,
X_Address1 VARCHAR2,
X_Address2 VARCHAR2,
X_Address3 VARCHAR2,
X_Address4 VARCHAR2,
X_City VARCHAR2,
X_Postal_Code VARCHAR2,
X_State VARCHAR2,
X_Province VARCHAR2,
X_County VARCHAR2,
X_Last_Update_Login NUMBER,
X_Address_Key VARCHAR2,
X_Language VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Address_warning out NOCOPY boolean,
X_Address_Lines_Phonetic VARCHAR2,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_Party_site_id IN OUT NOCOPY NUMBER,
X_Party_id NUMBER,
X_Location_id IN OUT NOCOPY NUMBER,
X_Party_Site_Number IN OUT NOCOPY VARCHAR2,
X_Identifying_address_flag VARCHAR2,
X_Cust_acct_site_id in out NOCOPY NUMBER,
X_Cust_account_id NUMBER,
X_su_Bill_To_Flag VARCHAR2,
X_su_Ship_To_Flag VARCHAR2,
X_su_Market_Flag VARCHAR2,
X_su_stmt_flag VARCHAR2,
X_su_dun_flag VARCHAR2,
X_su_legal_flag VARCHAR2,
X_Customer_Category VARCHAR2,
X_Key_Account_Flag VARCHAR2,
X_Territory_id NUMBER,
X_ece_tp_location_code VARCHAR2,
x_address_mode VARCHAR2,
x_territory VARCHAR2,
x_translated_customer_name VARCHAR2,
x_sales_tax_geo_code VARCHAR2,
x_sale_tax_inside_city_limits VARCHAR2,
x_ADDRESSEE VARCHAR2,
x_shared_party_site IN VARCHAR2,
x_update_account_site IN VARCHAR2,
x_create_location_party_site IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
--Bug#2689667 {
x_description IN VARCHAR2 DEFAULT NULL,
x_short_description IN VARCHAR2 DEFAULT NULL,
x_floor IN VARCHAR2 DEFAULT NULL,
x_house_number IN VARCHAR2 DEFAULT NULL,
x_location_directions IN VARCHAR2 DEFAULT NULL,
x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
x_po_box_number IN VARCHAR2 DEFAULT NULL,
x_street IN VARCHAR2 DEFAULT NULL,
x_street_number IN VARCHAR2 DEFAULT NULL,
x_street_suffix IN VARCHAR2 DEFAULT NULL,
x_suite IN VARCHAR2 DEFAULT NULL,
--}
/*Bug 3976386 MOAC changes*/
X_ORG_ID IN NUMBER DEFAULT NULL
)
IS
location_rec hz_location_v2pub.location_rec_type;
select hr_locations_s.nextval into x_location_id from dual;
select count(*) into l_count
from hz_locations
where location_id = x_location_id;
select hr_locations_s.nextval into x_location_id from dual;
select count(*) into l_count
from hz_locations
where location_id = x_location_id;
IF x_update_account_site = 'Y' THEN
HZ_TAX_ASSIGNMENT_V2PUB.create_loc_assignment(
p_location_id => x_location_id,
p_lock_flag => FND_API.G_TRUE,
p_created_by_module => 'FORM-WRAPPER',
p_application_id => -222,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loc_id => x_loc_id );
UPDATE hz_cust_acct_sites
SET party_site_id = x_party_site_id
WHERE cust_acct_site_id = x_cust_acct_site_id;
IF x_update_account_site = 'N' THEN
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
p_cust_acct_site_rec => asite_rec,
x_cust_acct_site_id => x_cust_acct_site_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
arp_standard.debug('ARHADDRB:Insert Row: After Insert into hz_cust_Acct_sites');
insert_site_use (X_Cust_account_id,x_address_id,'BILL_TO',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use bill_to call ');
insert_site_use (X_Cust_account_id,x_address_id,'SHIP_TO',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use ship_to call ');
insert_site_use (X_Cust_account_id,x_address_id,'MARKET',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use market call ');
insert_site_use (X_Cust_account_id,x_address_id,'STMTS',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use stmts call ');
insert_site_use (X_Cust_account_id,x_address_id,'DUN',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use dun call ');
insert_site_use (X_Cust_account_id,x_address_id,'LEGAL',x_msg_count,x_msg_data, x_return_status);
arp_standard.debug('After insert_site_use legal call ');
app_exception.invalid_argument('arp_addr_pkg.Insert_Row', 'x_address_mode', x_address_mode);
x_address_warning := arp_adds.location_segment_inserted;
arp_standard.debug('AROADDRB:Insert Row: END');
END Insert_Row;
PROCEDURE update_row (
X_Address_Id NUMBER,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_party_site_Last_Update_Date IN OUT NOCOPY DATE,
X_loc_Last_Update_Date IN OUT NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Status VARCHAR2,
X_Orig_System_Reference VARCHAR2,
X_Country VARCHAR2,
X_Address1 VARCHAR2,
X_Address2 VARCHAR2,
X_Address3 VARCHAR2,
X_Address4 VARCHAR2,
X_City VARCHAR2,
X_Postal_Code VARCHAR2,
X_State VARCHAR2,
X_Province VARCHAR2,
X_County VARCHAR2,
X_Last_Update_Login NUMBER,
X_Address_Key VARCHAR2,
X_Language VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Address_warning OUT NOCOPY BOOLEAN,
X_Address_Lines_Phonetic VARCHAR2,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_Party_site_id NUMBER,
X_Party_id NUMBER,
X_Location_id NUMBER,
X_Party_Site_Number VARCHAR2,
X_Identifying_address_flag VARCHAR2,
X_Cust_acct_site_id NUMBER,
X_Cust_account_id NUMBER,
X_su_Bill_To_Flag VARCHAR2,
X_su_Ship_To_Flag VARCHAR2,
X_su_Market_Flag VARCHAR2,
X_su_stmt_flag VARCHAR2,
X_su_dun_flag VARCHAR2,
X_su_legal_flag VARCHAR2,
X_Customer_Category VARCHAR2,
X_Key_Account_Flag VARCHAR2,
X_Territory_id NUMBER,
X_ece_tp_location_code VARCHAR2,
x_address_mode VARCHAR2,
X_Territory VARCHAR2,
X_Translated_Customer_Name VARCHAR2,
X_Sales_Tax_Geocode VARCHAR2,
X_Sales_Tax_Inside_City_Limits VARCHAR2,
x_ADDRESSEE VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_object_version IN NUMBER,
x_object_version_party_site IN NUMBER,
x_object_version_location IN NUMBER,
--Bug#2689667 {
x_description IN VARCHAR2 DEFAULT NULL,
x_short_description IN VARCHAR2 DEFAULT NULL,
x_floor IN VARCHAR2 DEFAULT NULL,
x_house_number IN VARCHAR2 DEFAULT NULL,
x_location_directions IN VARCHAR2 DEFAULT NULL,
x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
x_po_box_number IN VARCHAR2 DEFAULT NULL,
x_street IN VARCHAR2 DEFAULT NULL,
x_street_number IN VARCHAR2 DEFAULT NULL,
x_street_suffix IN VARCHAR2 DEFAULT NULL,
x_suite IN VARCHAR2 DEFAULT NULL,
--}
/*Bug 3976386 MOAC changes*/
X_ORG_ID IN NUMBER DEFAULT NULL
)
IS
--
--
l_site_use_id number;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM HZ_LOCATIONS
WHERE LOCATION_ID = x_location_id;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = X_Party_site_id;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM HZ_CUST_ACCT_SITES
WHERE CUST_ACCT_SITE_ID = X_address_id;
l_last_update_date DATE;
l_last_update_date ;
IF TO_CHAR(X_loc_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
l_last_update_date ;
IF TO_CHAR(X_party_site_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
l_last_update_date ;
IF TO_CHAR(X_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
HZ_LOCATION_V2PUB.update_location (
p_location_rec => location_rec,
p_object_version_number => l_object_version_location,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT last_update_date
INTO x_loc_last_update_date
FROM hz_locations
WHERE location_id = x_location_id;
HZ_PARTY_SITE_V2PUB.update_party_site (
p_party_site_rec => psite_rec,
p_object_version_number => l_object_version_party_site,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT last_update_date
INTO x_party_site_last_update_date
FROM hz_party_sites
WHERE party_site_id = x_party_site_id;
HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site (
p_cust_acct_site_rec => asite_rec,
p_object_version_number => l_object_version_account_site,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT last_update_date
INTO x_last_update_date
FROM hz_cust_acct_sites
WHERE cust_acct_site_id = x_address_id;
app_exception.invalid_argument('arp_addr_pkg.Insert_Row', 'x_address_mode',
x_address_mode);
x_address_warning := arp_adds.location_segment_inserted;
END Update_Row;
select loc_assign.loc_id
into location_identifier
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_loc_assignments loc_assign
where acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.location_id = loc_assign.location_id
and nvl(acct_site.org_id,-99) = nvl(loc_assign.org_id, -99)
and acct_site.cust_acct_site_id = p_address_id;
select site_use_id
from hz_cust_site_uses
where cust_acct_site_id = p_address_id
and site_use_code = 'SHIP_TO';
select site_use_id
from hz_cust_site_uses
where cust_acct_site_id = p_address_id
and site_use_code = 'BILL_TO';
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all
where org_id =
(select org_id from hz_cust_acct_sites_all where cust_acct_site_id = p_address_id);
select 'x'
into invoice_count
from dual
where exists ( select 'y'
from ra_cust_trx_types ctt,
ra_customer_trx_lines ctl,
ra_customer_trx ct
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and ct.customer_trx_id = ctl.customer_trx_id
and 'TAX' = ctl.line_type
and (
'Y' = arpt_sql_func_util.get_posted_flag
( ct.customer_trx_id,
ctt.post_to_gl,
ct.complete_flag
) -- posted_flag
OR 'Y' = arpt_sql_func_util.get_activity_flag
( ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
) -- activity_flag
OR DECODE(ct.printing_last_printed,
NULL,'N', 'Y') = 'Y'
)
and ct.ship_to_customer_id = p_customer_id
and ct.ship_to_site_use_id = i.site_use_id
);
select 'x'
into invoice_count
from dual
where exists ( select 'y'
from ra_cust_trx_types ctt,
ra_customer_trx_lines ctl,
ra_customer_trx ct
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and ct.customer_trx_id = ctl.customer_trx_id
and 'TAX' = ctl.line_type
and (
'Y' = arpt_sql_func_util.get_posted_flag
( ct.customer_trx_id,
ctt.post_to_gl,
ct.complete_flag
) -- posted_flag
OR 'Y' = arpt_sql_func_util.get_activity_flag
( ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
) -- activity_flag
OR DECODE(ct.printing_last_printed,
NULL,'N', 'Y') = 'Y'
)
and ct.bill_to_customer_id = p_customer_id
and ct.bill_to_site_use_id = j.site_use_id
);
select site_use_id
from hz_cust_site_uses_all
where cust_acct_site_id = p_address_id
and site_use_code = 'SHIP_TO';
select site_use_id
from hz_cust_site_uses_all
where cust_acct_site_id = p_address_id
and site_use_code = 'BILL_TO';
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all
where org_id =
(select org_id from hz_cust_acct_sites_all where cust_acct_site_id = p_address_id);
select 'x'
into invoice_count
from dual
where exists ( select 'y'
from ra_cust_trx_types_all ctt,
ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and nvl(ct.org_id, -99) = nvl(ctt.org_id, -99)
and ct.customer_trx_id = ctl.customer_trx_id
and 'TAX' = ctl.line_type
and (
'Y' = arpt_sql_func_util.get_posted_flag
( ct.customer_trx_id,
ctt.post_to_gl,
ct.complete_flag
) -- posted_flag
OR 'Y' = arpt_sql_func_util.get_activity_flag
( ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
) -- activity_flag
OR DECODE(ct.printing_last_printed,
NULL,'N', 'Y') = 'Y'
)
and ct.ship_to_customer_id = p_customer_id
and ct.ship_to_site_use_id = i.site_use_id
);
select 'x'
into invoice_count
from dual
where exists ( select 'y'
from ra_cust_trx_types_all ctt,
ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and nvl(ct.org_id,-99) = nvl(ctt.org_id, -99)
and ct.customer_trx_id = ctl.customer_trx_id
and 'TAX' = ctl.line_type
and (
'Y' = arpt_sql_func_util.get_posted_flag
( ct.customer_trx_id,
ctt.post_to_gl,
ct.complete_flag
) -- posted_flag
OR 'Y' = arpt_sql_func_util.get_activity_flag
( ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
) -- activity_flag
OR DECODE(ct.printing_last_printed,
NULL,'N', 'Y') = 'Y'
)
and ct.bill_to_customer_id = p_customer_id
and ct.bill_to_site_use_id = j.site_use_id
);
SELECT acct_site.cust_account_id,
acct_site.cust_acct_site_id
FROM hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
WHERE loc.location_id = party_site.location_id
AND party_site.party_site_id = acct_site.party_site_id
AND loc.location_id = p_location_id;
SELECT change_printed_invoice_flag
FROM ar_system_parameters;
select distinct acct_site.org_id
from hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
where loc.location_id = party_site.location_id
and party_site.party_site_id = acct_site.party_site_id
and loc.location_id = p_location_id;
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all where org_id=l_org_id;
x_loc_updated => loc_modified );
SELECT acct_site.cust_account_id,
acct_site.cust_acct_site_id
FROM hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
WHERE loc.location_id = party_site.location_id
AND party_site.party_site_id = acct_site.party_site_id
AND loc.location_id = p_location_id;
SELECT change_printed_invoice_flag
FROM ar_system_parameters;
select distinct acct_site.org_id
from hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
where loc.location_id = party_site.location_id
and party_site.party_site_id = acct_site.party_site_id
and loc.location_id = p_location_id;
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all where org_id=l_org_id;
x_loc_updated => loc_modified);
SELECT acct_site.cust_account_id,
acct_site.cust_acct_site_id
FROM hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
WHERE loc.location_id = party_site.location_id
AND party_site.party_site_id = acct_site.party_site_id
AND loc.location_id = p_location_id;
SELECT change_printed_invoice_flag
FROM ar_system_parameters;
select distinct acct_site.org_id
from hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
where loc.location_id = party_site.location_id
and party_site.party_site_id = acct_site.party_site_id
and loc.location_id = p_location_id;
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all where org_id=l_org_id;
x_loc_updated IN OUT NOCOPY VARCHAR2)
IS
CURSOR c_exist_loc IS
SELECT loc.country ,
loc.address1 ,
loc.address2 ,
loc.address3 ,
loc.address4 ,
loc.city ,
loc.postal_code ,
loc.state ,
loc.province ,
loc.county ,
loc.language ,
loc.address_lines_phonetic,
loc.description ,
loc.short_description ,
loc.floor ,
loc.house_number,
loc.location_directions,
loc.postal_plus4_code,
loc.po_box_number,
loc.street ,
loc.street_number,
loc.street_suffix,
loc.suite ,
loc.Sales_Tax_Inside_City_Limits,
loc.Sales_Tax_Geocode
FROM hz_locations loc
WHERE location_id = p_location_id;
x_loc_updated := 'X';
x_loc_updated := loc_modified;
arp_standard.debug(' x_loc_updated :'||x_loc_updated);
x_loc_updated IN OUT NOCOPY VARCHAR2)
IS
l_location_rec hz_location_v2pub.location_rec_type;
x_loc_updated => x_loc_updated);
IF x_loc_updated = 'Y' THEN
x_country := l_exist_loc_rec.Country;
select acct_site.cust_account_id, acct_site.cust_acct_site_id
from hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
where loc.location_id = party_site.location_id
and party_site.party_site_id = acct_site.party_site_id
and loc.location_id = p_location_id;
select distinct acct_site.org_id
from hz_locations loc,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site
where loc.location_id = party_site.location_id
and party_site.party_site_id = acct_site.party_site_id
and loc.location_id = p_location_id;
select change_printed_invoice_flag
into check_value
from ar_system_parameters_all where org_id=l_org_id;
-- rule is, "THE EDI LOCATION SHOULD BE UNIQUE FOR A CUSTOMER". Insert and Update
-- are rejected with an error message if a duplicate is provided. Release-11
-- change for EDI.
----------------------------------------------------------------------------------
--
procedure check_unique_edi_location(p_edi_location in varchar2,
p_customer_id in number,
p_orig_system_reference in varchar2) is
dummy number;
select count(1)
into dummy
from hz_cust_acct_sites addr
where addr.cust_account_id = p_customer_id
and addr.ece_tp_location_code = p_edi_location
and addr.orig_system_reference <> p_orig_system_reference;