[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select collector_id,
collector_name,
credit_checking,
tolerance,
interest_charges,
charge_on_finance_charge_flag,
interest_period_days,
discount_terms,
discount_grace_days,
statements,
statement_cycle_id,
statement_cycle_name,
credit_balance_statements,
standard_terms,
standard_terms_name,
override_terms,
payment_grace_days,
dunning_letters,
dunning_letter_set_id,
dunning_letter_set_name,
autocash_hierarchy_id,
autocash_hierarchy_name,
auto_rec_incl_disputed_flag,
tax_printing_option,
grouping_rule_id,
grouping_rule_name,
cons_inv_flag,
cons_inv_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
jgzz_attribute_category,
jgzz_attribute1,
jgzz_attribute2,
jgzz_attribute3,
jgzz_attribute4,
jgzz_attribute5,
jgzz_attribute6,
jgzz_attribute7,
jgzz_attribute8,
jgzz_attribute9,
jgzz_attribute10,
jgzz_attribute11,
jgzz_attribute12,
jgzz_attribute13,
jgzz_attribute14,
jgzz_attribute15,
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,
lockbox_matching_option,
lockbox_matching_option_name,
autocash_hierarchy_id_for_adr,
autocash_hierarchy_name_adr
from AR_CUSTOMER_PROFILE_CLASSES_V
-- from AR_CUST_PROF_CLASSES_TEST_V
where customer_profile_class_id = x_customer_profile_class_id;
select
hz_cust_profile_amts_s.nextval,
x_customer_profile_id,
currency_code,
trx_credit_limit,
overall_credit_limit,
min_dunning_amount,
min_dunning_invoice_amount,
max_interest_charge,
min_statement_amount,
auto_rec_min_receipt_amount,
interest_rate,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
min_fc_balance_amount,
min_fc_invoice_amount,
x_customer_id,
x_site_use_id,
jgzz_attribute_category,
jgzz_attribute1,
jgzz_attribute2,
jgzz_attribute3,
jgzz_attribute4,
jgzz_attribute5,
jgzz_attribute6,
jgzz_attribute7,
jgzz_attribute8,
jgzz_attribute9,
jgzz_attribute10,
jgzz_attribute11,
jgzz_attribute12,
jgzz_attribute13,
jgzz_attribute14,
jgzz_attribute15,
'CSPSHIPAD',
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,
EXCHANGE_RATE_TYPE,
MIN_FC_INVOICE_OVERDUE_TYPE,
MIN_FC_INVOICE_PERCENT,
MIN_FC_BALANCE_OVERDUE_TYPE,
MIN_FC_BALANCE_PERCENT,
INTEREST_TYPE,
INTEREST_FIXED_AMOUNT,
INTEREST_SCHEDULE_ID,
PENALTY_TYPE,
PENALTY_RATE,
MIN_INTEREST_CHARGE,
PENALTY_FIXED_AMOUNT,
PENALTY_SCHEDULE_ID
from hz_cust_prof_class_amts
where profile_class_id = x_customer_profile_class_id;
-- can insert rows into ar_customer_profile_amounts.
-- Customer_id wil be null when inserting a profile .
-- and not null if they are updating an existing profile.
--
--
if ( x_customer_id is null ) then
select hz_cust_accounts_s.nextval into x_customer_id from dual;
select hz_customer_profiles_s.nextval into x_customer_profile_id from dual;
-- Delete only the profile_amounts that match the currency that is
-- present in the profile_amounts of the new customer profile class.
--
delete from hz_cust_profile_amts
where cust_account_profile_id = x_customer_profile_id
and currency_code in ( select currency_code
from hz_cust_prof_class_amts
where profile_class_id = x_customer_profile_class_id
);
update hz_cust_profile_amts
set site_use_id = null
where cust_acct_profile_amt_id = x_cust_acct_profile_amt_id
and site_use_id = -1;
PROCEDURE insert_person_row(
c_cust_account_id IN OUT NOCOPY NUMBER ,
c_party_id IN OUT NOCOPY NUMBER,
c_account_number IN OUT NOCOPY VARCHAR2,
c_Attribute_Category IN VARCHAR2,
c_Attribute1 IN VARCHAR2,
c_Attribute2 IN VARCHAR2,
c_Attribute3 IN VARCHAR2,
c_Attribute4 IN VARCHAR2,
c_Attribute5 IN VARCHAR2,
c_Attribute6 IN VARCHAR2,
c_Attribute7 IN VARCHAR2,
c_Attribute8 IN VARCHAR2,
c_Attribute9 IN VARCHAR2,
c_Attribute10 IN VARCHAR2,
c_Attribute11 IN VARCHAR2,
c_Attribute12 IN VARCHAR2,
c_Attribute13 IN VARCHAR2,
c_Attribute14 IN VARCHAR2,
c_Attribute15 IN VARCHAR2,
c_Attribute16 IN VARCHAR2,
c_Attribute17 IN VARCHAR2,
c_Attribute18 IN VARCHAR2,
c_Attribute19 IN VARCHAR2,
c_Attribute20 IN VARCHAR2,
c_global_attribute_category IN VARCHAR2,
c_global_attribute1 IN VARCHAR2,
c_global_attribute2 IN VARCHAR2,
c_global_attribute3 IN VARCHAR2,
c_global_attribute4 IN VARCHAR2,
c_global_attribute5 IN VARCHAR2,
c_global_attribute6 IN VARCHAR2,
c_global_attribute7 IN VARCHAR2,
c_global_attribute8 IN VARCHAR2,
c_global_attribute9 IN VARCHAR2,
c_global_attribute10 IN VARCHAR2,
c_global_attribute11 IN VARCHAR2,
c_global_attribute12 IN VARCHAR2,
c_global_attribute13 IN VARCHAR2,
c_global_attribute14 IN VARCHAR2,
c_global_attribute15 IN VARCHAR2,
c_global_attribute16 IN VARCHAR2,
c_global_attribute17 IN VARCHAR2,
c_global_attribute18 IN VARCHAR2,
c_global_attribute19 IN VARCHAR2,
c_global_attribute20 IN VARCHAR2,
c_orig_system_reference IN VARCHAR2,
c_status IN VARCHAR2,
c_customer_type IN VARCHAR2,
c_customer_class_code IN VARCHAR2,
c_primary_salesrep_id IN NUMBER ,
c_sales_channel_code IN VARCHAR2,
c_order_type_id IN NUMBER,
c_price_list_id IN NUMBER ,
c_category_code IN VARCHAR2,
c_reference_use_flag IN VARCHAR2,
c_tax_code IN VARCHAR2,
c_third_party_flag IN VARCHAR2,
c_competitor_flag IN VARCHAR2,
c_fob_point IN VARCHAR2,
c_tax_header_level_flag IN VARCHAR2,
c_tax_rounding_rule IN VARCHAR2,
c_account_name IN VARCHAR2,
c_freight_term IN VARCHAR2,
c_ship_partial IN VARCHAR2,
c_ship_via IN VARCHAR2,
c_warehouse_id IN NUMBER,
c_payment_term_id IN NUMBER ,
c_DATES_NEGATIVE_TOLERANCE IN NUMBER,
c_DATES_POSITIVE_TOLERANCE IN NUMBER,
c_DATE_TYPE_PREFERENCE IN VARCHAR2,
c_OVER_SHIPMENT_TOLERANCE IN NUMBER,
c_UNDER_SHIPMENT_TOLERANCE IN NUMBER,
c_ITEM_CROSS_REF_PREF IN VARCHAR2,
c_OVER_RETURN_TOLERANCE IN NUMBER,
c_UNDER_RETURN_TOLERANCE IN NUMBER,
c_SHIP_SETS_INCLUDE_LINES_FLAG IN VARCHAR2,
c_ARRIVALSETS_INCL_LINES_FLAG IN VARCHAR2,
c_SCHED_DATE_PUSH_FLAG IN VARCHAR2,
c_INVOICE_QUANTITY_RULE IN VARCHAR2,
t_party_id IN NUMBER ,
t_party_number IN OUT NOCOPY VARCHAR2,
t_customer_key IN VARCHAR2,
t_Attribute_Category IN VARCHAR2,
t_Attribute1 IN VARCHAR2,
t_Attribute2 IN VARCHAR2,
t_Attribute3 IN VARCHAR2,
t_Attribute4 IN VARCHAR2,
t_Attribute5 IN VARCHAR2,
t_Attribute6 IN VARCHAR2,
t_Attribute7 IN VARCHAR2,
t_Attribute8 IN VARCHAR2,
t_Attribute9 IN VARCHAR2,
t_Attribute10 IN VARCHAR2,
t_Attribute11 IN VARCHAR2,
t_Attribute12 IN VARCHAR2,
t_Attribute13 IN VARCHAR2,
t_Attribute14 IN VARCHAR2,
t_Attribute15 IN VARCHAR2,
t_Attribute16 IN VARCHAR2,
t_Attribute17 IN VARCHAR2,
t_Attribute18 IN VARCHAR2,
t_Attribute19 IN VARCHAR2,
t_Attribute20 IN VARCHAR2,
t_global_attribute_category IN VARCHAR2,
t_global_attribute1 IN VARCHAR2,
t_global_attribute2 IN VARCHAR2,
t_global_attribute3 IN VARCHAR2,
t_global_attribute4 IN VARCHAR2,
t_global_attribute5 IN VARCHAR2,
t_global_attribute6 IN VARCHAR2,
t_global_attribute7 IN VARCHAR2,
t_global_attribute8 IN VARCHAR2,
t_global_attribute9 IN VARCHAR2,
t_global_attribute10 IN VARCHAR2,
t_global_attribute11 IN VARCHAR2,
t_global_attribute12 IN VARCHAR2,
t_global_attribute13 IN VARCHAR2,
t_global_attribute14 IN VARCHAR2,
t_global_attribute15 IN VARCHAR2,
t_global_attribute16 IN VARCHAR2,
t_global_attribute17 IN VARCHAR2,
t_global_attribute18 IN VARCHAR2,
t_global_attribute19 IN VARCHAR2,
t_global_attribute20 IN VARCHAR2,
o_pre_name_adjunct IN VARCHAR2,
o_first_name IN VARCHAR2,
o_middle_name IN VARCHAR2,
o_last_name IN VARCHAR2,
o_name_suffix IN VARCHAR2,
o_tax_reference IN VARCHAR2,
o_taxpayer_id IN VARCHAR2,
o_party_name_phonetic IN VARCHAR2,
p_cust_account_profile_id IN NUMBER ,
p_cust_account_id IN NUMBER ,
p_status IN VARCHAR2,
p_collector_id IN NUMBER ,
p_credit_analyst_id IN NUMBER ,
p_credit_checking IN VARCHAR2,
p_next_credit_review_date DATE ,
p_tolerance IN NUMBER,
p_discount_terms IN VARCHAR2,
p_dunning_letters IN VARCHAR2,
p_interest_charges IN VARCHAR2,
p_send_statements IN VARCHAR2,
p_credit_balance_statements IN VARCHAR2,
p_credit_hold IN VARCHAR2,
p_profile_class_id IN NUMBER ,
p_site_use_id IN NUMBER ,
p_credit_rating IN VARCHAR2,
p_risk_code IN VARCHAR2,
p_standard_terms IN NUMBER ,
p_override_terms IN VARCHAR2,
p_dunning_letter_set_id IN NUMBER,
p_interest_period_days IN NUMBER,
p_payment_grace_days IN NUMBER,
p_discount_grace_days IN NUMBER,
p_statement_cycle_id IN NUMBER ,
p_account_status IN VARCHAR2,
p_percent_collectable IN NUMBER ,
p_autocash_hierarchy_id IN NUMBER,
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_auto_rec_incl_disputed_flag IN VARCHAR2,
p_tax_printing_option IN VARCHAR2,
p_charge_on_fin_charge_flag IN VARCHAR2,
p_grouping_rule_id IN NUMBER ,
p_clearing_days IN NUMBER,
p_jgzz_attribute_category IN VARCHAR2,
p_jgzz_attribute1 IN VARCHAR2,
p_jgzz_attribute2 IN VARCHAR2,
p_jgzz_attribute3 IN VARCHAR2,
p_jgzz_attribute4 IN VARCHAR2,
p_jgzz_attribute5 IN VARCHAR2,
p_jgzz_attribute6 IN VARCHAR2,
p_jgzz_attribute7 IN VARCHAR2,
p_jgzz_attribute8 IN VARCHAR2,
p_jgzz_attribute9 IN VARCHAR2,
p_jgzz_attribute10 IN VARCHAR2,
p_jgzz_attribute11 IN VARCHAR2,
p_jgzz_attribute12 IN VARCHAR2,
p_jgzz_attribute13 IN VARCHAR2,
p_jgzz_attribute14 IN VARCHAR2,
p_jgzz_attribute15 IN VARCHAR2,
p_global_attribute_category IN VARCHAR2,
p_global_attribute1 IN VARCHAR2,
p_global_attribute2 IN VARCHAR2,
p_global_attribute3 IN VARCHAR2,
p_global_attribute4 IN VARCHAR2,
p_global_attribute5 IN VARCHAR2,
p_global_attribute6 IN VARCHAR2,
p_global_attribute7 IN VARCHAR2,
p_global_attribute8 IN VARCHAR2,
p_global_attribute9 IN VARCHAR2,
p_global_attribute10 IN VARCHAR2,
p_global_attribute11 IN VARCHAR2,
p_global_attribute12 IN VARCHAR2,
p_global_attribute13 IN VARCHAR2,
p_global_attribute14 IN VARCHAR2,
p_global_attribute15 IN VARCHAR2,
p_global_attribute16 IN VARCHAR2,
p_global_attribute17 IN VARCHAR2,
p_global_attribute18 IN VARCHAR2,
p_global_attribute19 IN VARCHAR2,
p_global_attribute20 IN VARCHAR2,
p_cons_inv_flag IN VARCHAR2,
p_cons_inv_type IN VARCHAR2,
p_autocash_hier_id_for_adr IN NUMBER ,
p_lockbox_matching_option IN VARCHAR2,
o_person_profile_id in OUT NOCOPY number,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY varchar2,
x_return_status OUT NOCOPY VARCHAR2) is
acct_rec hz_cust_account_v2pub.cust_account_rec_type;
cursor C_REFERENCE_FOR is select relationship_id from
hz_relationships where subject_id = c_party_id
and relationship_code = 'REFERENCE_FOR'
and subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
x_party_last_update_date date;
X_PARTY_REL_LAST_UPDATE_DATE date;
cursor C_PARTNER_OF is select relationship_id from
hz_relationships where subject_id = c_party_id
and relationship_code = 'PARTNER_OF'
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES'
and directional_flag = 'F';
cursor C_COMPETITOR_OF is select relationship_id from
hz_relationships where subject_id = c_party_id
and relationship_code = 'COMPETITOR_OF'
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES'
and directional_flag = 'F';
/*acct_rec.status_update_date := null ; --new
/* Update of party relationship table */
prel_rec.party_rec.party_id := c_party_id;
/* select last_update_date into x_party_rel_last_update_date
from hz_party_relationships where party_relationship_id = x_party_rel_id;
select last_update_date into x_party_last_update_date
from hz_parties where party_id = c_party_id;*/
select object_version_number into l_object_version_number
from hz_relationships where relationship_id = x_party_rel_id;
select object_version_number into l_party_object_version_number
from hz_parties where party_id = c_party_id;
HZ_RELATIONSHIP_V2PUB.update_relationship (
p_init_msg_list => FND_API.G_FALSE,
p_relationship_rec => prel_rec ,
p_object_version_number => l_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count =>x_msg_count ,
x_msg_data => x_msg_data);
/* Update of party relationship table */
prel_rec.party_rec.party_id := c_party_id;
/* select last_update_date into x_party_rel_last_update_date
from hz_party_relationships where party_relationship_id = x_party_rel_id;
select last_update_date into x_party_last_update_date
from hz_parties where party_id = c_party_id;*/
select object_version_number into l_object_version_number
from hz_relationships where relationship_id = x_party_rel_id;
select object_version_number into l_party_object_version_number
from hz_parties where party_id = c_party_id;
HZ_RELATIONSHIP_V2PUB.update_relationship (
p_init_msg_list => FND_API.G_FALSE,
p_relationship_rec => prel_rec ,
p_object_version_number => l_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count =>x_msg_count ,
x_msg_data => x_msg_data);
/* Update of party relationship table */
prel_rec.party_rec.party_id := c_party_id;
/*select last_update_date,object_version_number into x_party_rel_last_update_date,l_object_version_number
from hz_party_relationships where party_relationship_id = x_party_rel_id;
select last_update_date into x_party_last_update_date
from hz_parties where party_id = c_party_id;*/
select object_version_number into l_object_version_number
from hz_relationships where relationship_id = x_party_rel_id;
select object_version_number into l_party_object_version_number
from hz_parties where party_id = c_party_id;
/*HZ_PARTY_PUB.update_party_relationship (
1,
null,
null,
prel_rec,
x_party_rel_last_update_date,
x_party_last_update_date,
x_return_status,
x_msg_count,
x_msg_data);*/
HZ_RELATIONSHIP_V2PUB.update_relationship (
p_init_msg_list => FND_API.G_FALSE,
p_relationship_rec => prel_rec ,
p_object_version_number => l_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count =>x_msg_count ,
x_msg_data => x_msg_data);
end insert_person_row;