[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
arh_addr_pkg.update_site_use_flag(p_address_id => p_address_id,
p_site_use_code => p_site_use_code,
p_site_use_flag => l_site_use_flag);
PROCEDURE delete_customer_alt_names(p_rowid in varchar2,
p_status in varchar2,
p_customer_id in number,
p_site_use_id in number
) is
l_status varchar2(1);
select status
into l_status
from hz_cust_site_uses
where site_use_id = p_site_use_id;
arp_cust_alt_match_pkg.delete_match ( p_customer_id,
p_site_use_id, NULL );
arp_standard.debug('EXCEPTION: arh_csu_pkg.delete_customer_alt_names');
end delete_customer_alt_names;
cursor c_site_use is select su.site_use_id,
su.status
from hz_cust_site_uses su
where su.cust_acct_site_id = p_address_id
and su.site_use_code = p_site_use_code
order
by su.status;
PROCEDURE update_su_status ( p_customer_id in number,
p_address_id in number,
p_site_use_id in number,
p_site_use_code in varchar2,
p_status in varchar2 ) is
--
--
begin
--
--
update hz_cust_site_uses su
set su.status = p_status
where su.site_use_id = p_site_use_id;
end update_su_status;
select count(1)
into dummy
from hz_cust_site_uses su,
hz_cust_acct_sites a
where su.cust_acct_site_ID = a.cust_acct_site_ID
and a.cust_account_id = p_customer_id
and su.site_use_code = p_site_use_code
and su.status = 'A'
and ( ( p_site_use_id is null ) or ( p_site_use_id <> site_use_id));
select count(1)
into dummy
from hz_cust_site_uses
where site_use_code = p_site_use_code
and cust_acct_site_ID = p_address_id
and status = 'A'
and ( ( p_site_use_id is null ) or site_use_id <> p_site_use_id);
select count(1)
into dummy
from hz_cust_site_uses su,
hz_cust_acct_sites a
where su.cust_acct_site_ID = a.cust_acct_site_ID
and a.cust_account_id = p_customer_id
and su.site_use_code = p_site_use_code
and su.status = 'A'
and ( ( p_site_use_id is null ) or ( p_site_use_id <> site_use_id));
select count(1)
into dummy
from hz_cust_site_uses
where site_use_code = p_site_use_code
and cust_acct_site_ID = p_address_id
and status = 'A'
and ( ( p_site_use_id is null ) or site_use_id <> p_site_use_id);
select 1
into dummy
from hz_cust_site_uses su,
hz_cust_acct_sites addr
where su.cust_acct_site_ID = addr.cust_acct_site_ID
and su.site_use_code = p_site_use_code
and su.location = p_location
and addr.cust_account_ID = p_customer_id
and ( ( p_site_use_id is null ) or ( site_use_id <> p_site_use_id ))
and su.STATUS = 'A'
and rownum = 1;
select 1
into dummy
from hz_cust_site_uses su,
hz_cust_acct_sites addr
where su.cust_acct_site_ID = addr.cust_acct_site_ID
and su.site_use_code = p_site_use_code
and su.location = p_location
and addr.cust_account_ID = p_customer_id
and ( ( p_site_use_id is null ) or ( site_use_id <> p_site_use_id ))
and su.org_id = (select org_id from hz_cust_site_uses where site_use_id=p_site_use_id) -- 6066859
and su.STATUS = 'A'
and rownum = 1;
select count(1)
into dummy
from hz_cust_accounts cust,
hz_cust_acct_sites addr,
hz_cust_site_uses su
where su.cust_acct_site_ID = addr.cust_acct_site_ID
and addr.cust_account_ID = cust.cust_account_ID
and cust.cust_account_ID = p_customer_id
and site_use_code = p_site_use_code
and su.primary_flag = 'Y'
and su.status = 'A'
and ( ( p_site_use_id is null) or (site_use_id <> p_site_use_id));
select meaning
into l_site_use_meaning
from ar_lookups
where lookup_type = 'SITE_USE_CODE'
and lookup_code = p_site_use_code;
PROCEDURE Insert_Row(
X_Site_Use_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Site_Use_Code VARCHAR2,
X_customer_id NUMBER,
X_Address_Id NUMBER,
X_Primary_Flag VARCHAR2,
X_Status VARCHAR2,
X_Location IN OUT NOCOPY VARCHAR2,
X_Last_Update_Login NUMBER,
X_Contact_Id NUMBER,
X_Bill_To_Site_Use_Id NUMBER,
X_Sic_Code VARCHAR2,
X_Payment_Term_Id NUMBER,
X_Gsa_Indicator VARCHAR2,
X_Ship_Partial VARCHAR2,
X_Ship_Via VARCHAR2,
X_Fob_Point VARCHAR2,
X_Order_Type_Id NUMBER,
X_Price_List_Id NUMBER,
X_Freight_Term VARCHAR2,
X_Warehouse_Id NUMBER,
X_Territory_Id NUMBER,
X_Tax_Code VARCHAR2,
X_Tax_Reference VARCHAR2,
X_Demand_Class_Code VARCHAR2,
x_inventory_location_id NUMBER,
x_inventory_organization_id NUMBER,
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_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Tax_Classification VARCHAR2,
X_Tax_Header_Level_Flag VARCHAR2,
X_Tax_Rounding_Rule 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_Primary_Salesrep_Id NUMBER DEFAULT NULL,
X_Finchrg_Receivables_Trx_Id NUMBER DEFAULT NULL,
X_GL_ID_Rec NUMBER,
X_GL_ID_Rev NUMBER,
X_GL_ID_Tax NUMBER,
X_GL_ID_Freight NUMBER,
X_GL_ID_Clearing NUMBER,
X_GL_ID_Unbilled NUMBER,
X_GL_ID_Unearned NUMBER,
X_GL_ID_Unpaid_rec NUMBER,
X_GL_ID_Remittance NUMBER,
X_GL_ID_Factor NUMBER,
X_DATES_NEGATIVE_TOLERANCE NUMBER,
X_DATES_POSITIVE_TOLERANCE NUMBER,
X_DATE_TYPE_PREFERENCE VARCHAR2,
X_OVER_SHIPMENT_TOLERANCE NUMBER,
X_UNDER_SHIPMENT_TOLERANCE NUMBER,
X_ITEM_CROSS_REF_PREF VARCHAR2,
X_OVER_RETURN_TOLERANCE NUMBER,
X_UNDER_RETURN_TOLERANCE NUMBER,
X_SHIP_SETS_INCLUDE_LINES_FLAG VARCHAR2,
X_ARRIVALSETS_INCL_LINES_FLAG VARCHAR2,
X_SCHED_DATE_PUSH_FLAG VARCHAR2,
X_INVOICE_QUANTITY_RULE VARCHAR2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
suse_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
select hz_cust_site_uses_s.nextval into x_site_use_id from dual;
arp_clas_pkg.insert_po_loc_associations( p_inventory_location_id => x_inventory_location_id,
p_inventory_organization_id => x_inventory_organization_id,
p_customer_id => x_customer_id,
p_address_id => x_address_id,
p_site_use_id => x_site_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Insert_Row;
PROCEDURE Update_Row(
X_Site_Use_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Site_Use_Code VARCHAR2,
X_customer_id NUMBER,
X_Address_Id NUMBER,
X_Primary_Flag VARCHAR2,
X_Status VARCHAR2,
X_Location VARCHAR2,
X_Last_Update_Login NUMBER,
X_Contact_Id NUMBER,
X_Bill_To_Site_Use_Id NUMBER,
X_Sic_Code VARCHAR2,
X_Payment_Term_Id NUMBER,
X_Gsa_Indicator VARCHAR2,
X_Ship_Partial VARCHAR2,
X_Ship_Via VARCHAR2,
X_Fob_Point VARCHAR2,
X_Order_Type_Id NUMBER,
X_Price_List_Id NUMBER,
X_Freight_Term VARCHAR2,
X_Warehouse_Id NUMBER,
X_Territory_Id NUMBER,
X_Tax_Code VARCHAR2,
X_Tax_Reference VARCHAR2,
X_Demand_Class_Code VARCHAR2,
x_inventory_location_id NUMBER,
x_inventory_organization_id NUMBER,
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_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Tax_Classification VARCHAR2,
X_Tax_Header_Level_Flag VARCHAR2,
X_Tax_Rounding_Rule 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_Primary_Salesrep_Id NUMBER DEFAULT NULL,
X_Finchrg_Receivables_Trx_Id NUMBER DEFAULT NULL,
X_GL_ID_Rec NUMBER,
X_GL_ID_Rev NUMBER,
X_GL_ID_Tax NUMBER,
X_GL_ID_Freight NUMBER,
X_GL_ID_Clearing NUMBER,
X_GL_ID_Unbilled NUMBER,
X_GL_ID_Unearned NUMBER,
X_GL_ID_Unpaid_rec NUMBER,
X_GL_ID_Remittance NUMBER,
X_GL_ID_Factor NUMBER,
X_DATES_NEGATIVE_TOLERANCE NUMBER,
X_DATES_POSITIVE_TOLERANCE NUMBER,
X_DATE_TYPE_PREFERENCE VARCHAR2,
X_OVER_SHIPMENT_TOLERANCE NUMBER,
X_UNDER_SHIPMENT_TOLERANCE NUMBER,
X_ITEM_CROSS_REF_PREF VARCHAR2,
X_OVER_RETURN_TOLERANCE NUMBER,
X_UNDER_RETURN_TOLERANCE NUMBER,
X_SHIP_SETS_INCLUDE_LINES_FLAG VARCHAR2,
X_ARRIVALSETS_INCL_LINES_FLAG VARCHAR2,
X_SCHED_DATE_PUSH_FLAG VARCHAR2,
X_INVOICE_QUANTITY_RULE VARCHAR2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_object_version IN NUMBER DEFAULT -1
) IS
--
l_inventory_location_id number;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM hz_cust_site_uses
WHERE site_use_id = X_Site_Use_Id;
l_site_use_last_update_date DATE;
l_site_use_last_update_date ;
IF TO_CHAR(X_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
TO_CHAR(l_site_use_last_update_date,'DD-MON-YYYY HH:MI:SS')
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
arp_clas_pkg.update_po_loc_associations(
p_site_use_id => x_site_use_id,
p_address_id => x_address_id,
p_customer_id => x_customer_id,
p_inventory_organization_id => x_inventory_organization_id,
p_inventory_location_id => x_inventory_location_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (
p_cust_site_use_rec => suse_rec,
p_object_version_number => l_site_object_version_number,
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_site_uses
where site_use_id = x_site_use_id;
END Update_Row;