[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
into l_profile_count
from hz_customer_profiles cp
where cp.cust_account_id = p_customer_id
and cp.site_use_id is null;
select count(1)
into l_profile_count
from hz_customer_profiles cp,
hz_cust_site_uses su
where cp.cust_account_id = p_customer_id
and cp.site_use_id = p_site_use_id
and su.site_use_id = cp.site_use_id
and su.site_use_code in ('BILL_TO','DUN','STMTS') ;
PROCEDURE update_customer_alt_names(p_rowid in varchar2,
p_standard_terms in number,
p_customer_id in number,
p_site_use_id in number
)
IS
l_standard_terms number := null ;
select standard_terms
from hz_customer_profiles
where rowid = p_rowid;
arp_cust_alt_match_pkg.update_pay_term_id ( p_customer_id,
p_site_use_id , p_standard_terms );
arp_standard.debug('EXCEPTION: hzp_cprof_pkg.update_customer_alt_names');
END update_customer_alt_names;
PROCEDURE Insert_Row(
X_Customer_Profile_Id IN OUT NOCOPY NUMBER,
X_Auto_Rec_Incl_Disputed_Flag VARCHAR2,
X_Collector_Id NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Credit_Balance_Statements VARCHAR2,
X_Credit_Checking VARCHAR2,
X_Credit_Hold VARCHAR2,
X_Customer_Id NUMBER,
X_Discount_Terms VARCHAR2,
X_Dunning_Letters VARCHAR2,
X_Interest_Charges VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Statements VARCHAR2,
X_Status VARCHAR2,
X_Tolerance NUMBER,
X_Tax_Printing_Option VARCHAR2,
X_Account_Status VARCHAR2,
X_Autocash_Hierarchy_Id NUMBER,
X_Credit_Rating VARCHAR2,
X_Customer_Profile_Class_Id NUMBER,
X_Discount_Grace_Days NUMBER,
X_Dunning_Letter_Set_Id NUMBER,
X_Interest_Period_Days NUMBER,
X_Last_Update_Login NUMBER,
X_Override_Terms VARCHAR2,
X_Payment_Grace_Days NUMBER,
X_Percent_Collectable NUMBER,
X_Risk_Code VARCHAR2,
X_Site_Use_Id NUMBER,
X_Standard_Terms NUMBER,
X_Statement_Cycle_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_Charge_On_Fin_Charge_Flag VARCHAR2,
X_Grouping_Rule_Id NUMBER,
X_Cons_Inv_Flag VARCHAR2,
X_Cons_Inv_Type VARCHAR2,
X_Clearing_Days NUMBER,
X_Jgzz_attribute_Category VARCHAR2,
X_Jgzz_attribute1 VARCHAR2,
X_Jgzz_attribute2 VARCHAR2,
X_Jgzz_attribute3 VARCHAR2,
X_Jgzz_attribute4 VARCHAR2,
X_Jgzz_attribute5 VARCHAR2,
X_Jgzz_attribute6 VARCHAR2,
X_Jgzz_attribute7 VARCHAR2,
X_Jgzz_attribute8 VARCHAR2,
X_Jgzz_attribute9 VARCHAR2,
X_Jgzz_attribute10 VARCHAR2,
X_Jgzz_attribute11 VARCHAR2,
X_Jgzz_attribute12 VARCHAR2,
X_Jgzz_attribute13 VARCHAR2,
X_Jgzz_attribute14 VARCHAR2,
X_Jgzz_attribute15 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_lockbox_matching_option VARCHAR2,
X_autocash_hierarchy_id_adr NUMBER,
--{ BUG 2310474
X_Review_Cycle VARCHAR2 DEFAULT NULL,
X_Credit_Classification VARCHAR2 DEFAULT NULL,
X_Next_Credit_Review_Date DATE DEFAULT NULL,
X_Last_Credit_Review_Date DATE DEFAULT NULL,
X_Credit_Analyst_id NUMBER DEFAULT NULL,
X_Party_id NUMBER DEFAULT NULL,
--}
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2
)
IS
-- suse_rec hz_customer_accounts_pub.acct_site_uses_rec_type;
SELECT hz_customer_profiles_s.nextval
INTO x_customer_profile_id
FROM dual;
END Insert_Row;
PROCEDURE insert_row(x_customer_id NUMBER,
x_site_use_id NUMBER,
x_customer_profile_class_id NUMBER,
x_party_id NUMBER DEFAULT NULL,
x_last_credit_review_date DATE DEFAULT NULL,
x_next_credit_review_date DATE DEFAULT NULL )
IS
--
--
l_customer_profile_id number;
SELECT *
FROM hz_cust_profile_classes
WHERE profile_class_id = x_customer_profile_class_id;
SELECT hz_customer_profiles_s.NEXTVAL
INTO l_customer_profile_id
FROM DUAL;
END insert_row;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Customer_Profile_Id NUMBER,
X_Auto_Rec_Incl_Disputed_Flag VARCHAR2,
X_Collector_Id NUMBER,
X_Credit_Balance_Statements VARCHAR2,
X_Credit_Checking VARCHAR2,
X_Credit_Hold VARCHAR2,
X_Customer_Id NUMBER,
X_Discount_Terms VARCHAR2,
X_Dunning_Letters VARCHAR2,
X_Interest_Charges VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Statements VARCHAR2,
X_Status VARCHAR2,
X_Tolerance NUMBER,
X_Tax_Printing_Option VARCHAR2,
X_Account_Status VARCHAR2,
X_Autocash_Hierarchy_Id NUMBER,
X_Credit_Rating VARCHAR2,
X_Customer_Profile_Class_Id NUMBER,
X_Discount_Grace_Days NUMBER,
X_Dunning_Letter_Set_Id NUMBER,
X_Interest_Period_Days NUMBER,
X_Last_Update_Login NUMBER,
X_Override_Terms VARCHAR2,
X_Payment_Grace_Days NUMBER,
X_Percent_Collectable NUMBER,
X_Risk_Code VARCHAR2,
X_Site_Use_Id NUMBER,
X_Standard_Terms NUMBER,
X_Statement_Cycle_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_Charge_On_Fin_Charge_Flag VARCHAR2,
X_Grouping_Rule_Id NUMBER,
X_Cons_Inv_Flag VARCHAR2,
X_Cons_Inv_Type VARCHAR2,
X_Clearing_Days NUMBER,
X_Jgzz_attribute_Category VARCHAR2,
X_Jgzz_attribute1 VARCHAR2,
X_Jgzz_attribute2 VARCHAR2,
X_Jgzz_attribute3 VARCHAR2,
X_Jgzz_attribute4 VARCHAR2,
X_Jgzz_attribute5 VARCHAR2,
X_Jgzz_attribute6 VARCHAR2,
X_Jgzz_attribute7 VARCHAR2,
X_Jgzz_attribute8 VARCHAR2,
X_Jgzz_attribute9 VARCHAR2,
X_Jgzz_attribute10 VARCHAR2,
X_Jgzz_attribute11 VARCHAR2,
X_Jgzz_attribute12 VARCHAR2,
X_Jgzz_attribute13 VARCHAR2,
X_Jgzz_attribute14 VARCHAR2,
X_Jgzz_attribute15 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_lockbox_matching_option VARCHAR2,
X_autocash_hierarchy_id_adr NUMBER,
--{2310474
X_party_id NUMBER DEFAULT NULL,
X_review_cycle VARCHAR2 DEFAULT NULL,
X_credit_classification VARCHAR2 DEFAULT NULL,
X_credit_analyst_id NUMBER DEFAULT NULL,
X_last_credit_review_date DATE DEFAULT NULL,
X_next_credit_review_date DATE DEFAULT NULL,
--}
X_object_version IN NUMBER DEFAULT -1
) IS
l_object_version NUMBER;
SELECT OBJECT_VERSION_NUMBER
INTO l_object_version
FROM HZ_CUSTOMER_PROFILES
WHERE ROWID = X_rowid;
hzp_cprof_pkg.update_customer_alt_names( X_Rowid , X_Standard_Terms ,
X_Customer_Id , X_Site_Use_Id ) ;
HZ_CUSTOMER_PROFILE_V2PUB.update_customer_profile (
p_customer_profile_rec => l_cp_rec,
p_object_version_number => l_object_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
END Update_Row;
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,
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
FROM hz_cust_prof_class_amts
WHERE profile_class_id = x_customer_profile_class_id;
SELECT profile_class_amount_id
FROM hz_cust_prof_class_amts
WHERE profile_class_id = l_profile_class_id
AND currency_code = l_currency_code;
SELECT profile_class_id
FROM hz_customer_profiles
WHERE cust_account_profile_id = p_customer_profile_id;
-- can insert rows into hz_cust_profile_amts.
-- 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.
--
-- Bug 2046191: revised to delete all prior currency amounts to
-- accurately reflect the current profile class currencies
-- and their respective amounts.
--
--{BUG#3046535
/********** Bug Fix Begin#2 : 3107081 ***********************/
/**** Get the Site Level Customer Profile Id if a profile already exists ****/
BEGIN
IF x_site_use_id IS NOT NULL THEN
SELECT DISTINCT cust_account_profile_id
INTO old_customer_profile_id
FROM HZ_CUST_PROFILE_AMTS
WHERE cust_account_id = x_customer_id
AND site_use_id = x_site_use_id;
/**** In the WHERE Clause of the DELETE Statements present in the IF and ELSE parts,
* Use old_customer_profile_id for Site Level Profile
* Use x_customer_profile_id for Account Level Profile ****/
IF p_keep_replace = 'REPLACE' THEN
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id =
NVL(old_customer_profile_id,x_customer_profile_id);
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id =
NVL(old_customer_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_customer_profiles
SET profile_class_id = x_customer_profile_class_id
WHERE cust_account_profile_id = x_customer_profile_id;
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id = x_customer_profile_id;
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
);
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,
lockbox_matching_option,
lockbox_matching_option_name,
autocash_hierarchy_id_for_adr,
autocash_hierarchy_name_adr,
--{2310474
review_cycle,
credit_classification,
credit_classification_meaning,
review_cycle_name,
credit_analyst_id,
credit_analyst_name
--}
FROM AR_CUSTOMER_PROFILE_CLASSES_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
FROM hz_cust_prof_class_amts
WHERE profile_class_id = x_customer_profile_class_id;
SELECT profile_class_amount_id
FROM hz_cust_prof_class_amts
WHERE profile_class_id = l_profile_class_id
AND currency_code = l_currency_code;
SELECT profile_class_id
FROM hz_customer_profiles
WHERE cust_account_profile_id = p_customer_profile_id;
-- can insert rows into hz_cust_profile_amts.
-- 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.
--
-- Bug 2046191: revised to delete all prior currency amounts to
-- accurately reflect the current profile class currencies
-- and their respective amounts.
--
--{BUG#3046535
/********** Bug Fix Begin#5 : 3107081 *************************/
/**** Get the Site Level Customer Profile Id if a profile already exists ****/
BEGIN
IF x_site_use_id IS NOT NULL THEN
SELECT DISTINCT cust_account_profile_id
INTO old_customer_profile_id
FROM HZ_CUST_PROFILE_AMTS
WHERE cust_account_id = x_customer_id
AND site_use_id = x_site_use_id;
/**** In the WHERE Clause of the DELETE Statements present in the IF and ELSE parts,
* Use old_customer_profile_id for Site Level Profile
* Use x_customer_profile_id for Account Level Profile ****/
IF p_keep_replace = 'REPLACE' THEN
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id =
NVL(old_customer_profile_id,x_customer_profile_id);
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id =
NVL(old_customer_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_customer_profiles
SET profile_class_id = x_customer_profile_class_id
WHERE cust_account_profile_id = x_customer_profile_id;
DELETE FROM hz_cust_profile_amts
WHERE cust_account_profile_id = x_customer_profile_id;
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
);
arp_standard.debug('EXCEPTION: hzp_cprof_pkg.delete_profile_amout'||x_msg_data);
| PROCEDURE : UPDATE_PROFILE_AMOUNT |
| |
| PARAMETERS : |
| Arguments in the record type |
| Hz_Customer_Profile_V2pub.Cust_Profile_Amt_Rec_Type |
| |
| DESCRIPTION : |
| From the arguments entered contruct the recors type and call |
| Hz_Customer_Profile_V2pub.update_cust_profile_amt |
| |
| HISTORY : |
| 12-DEC-2002 H. Yu Created |
+---------------------------------------------------------------*/
PROCEDURE update_profile_amount
( p_cust_acct_profile_amt_id IN NUMBER,
p_cust_account_profile_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_trx_credit_limit IN NUMBER,
p_overall_credit_limit IN NUMBER,
p_min_dunning_amount IN NUMBER,
p_min_dunning_invoice_amount IN NUMBER,
p_max_interest_charge IN NUMBER,
p_min_statement_amount IN NUMBER,
p_auto_rec_min_receipt_amount IN NUMBER,
p_interest_rate IN NUMBER,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_min_fc_balance_amount IN NUMBER,
p_min_fc_invoice_amount IN NUMBER,
p_cust_account_id IN NUMBER,
p_site_use_id IN NUMBER,
p_expiration_date IN DATE,
p_jgzz_attribute_category IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute1 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute2 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute3 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute4 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute5 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute6 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute7 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute8 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute9 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute10 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute11 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute12 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute13 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute14 IN VARCHAR2 DEFAULT NULL,
p_jgzz_attribute15 IN VARCHAR2 DEFAULT NULL,
p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_version IN NUMBER DEFAULT -1,
X_Last_Update_Date IN DATE)
IS
CURSOR cu_version_prof_amt IS
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM hz_cust_profile_amts
WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
l_last_update_date DATE;
arp_standard.debug('update_profile_amount (+)');
arp_standard.debug('last_update_date:'||X_Last_Update_Date );
l_last_update_date;
arp_standard.debug('Last_update_date:'||to_char(l_last_update_date));
HZ_CUSTOMER_PROFILE_V2PUB.update_cust_profile_amt
( p_cust_profile_amt_rec => l_rec,
p_object_version_number => l_object_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
arp_standard.debug('update_profile_amount (-)');
arp_standard.debug('Exception hzp_cprof_pkg.update_profile_amount:'||x_msg_data);
arp_standard.debug('Exception hzp_cprof_pkg.update_profile_amount:'||x_msg_data);
arp_standard.debug('EXCEPTION OTHERS: hzp_cprof_pkg.update_profile_amout'||x_msg_data);
| PROCEDURE : DELETE_PROFILE_AMOUNT |
| |
| PARAMETERS : |
| NUMBER Customer_profile_amt_id |
| |
| DESCRIPTION : |
| Delete a record from Hz_Cust_Profile_Amts |
| |
| HISTORY : |
| 12-DEC-2002 H. Yu Created |
+---------------------------------------------------------------*/
PROCEDURE delete_profile_amount
(p_cust_acct_profile_amt_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
BEGIN
arp_standard.debug('delete_profile_amount(+)');
DELETE FROM hz_cust_profile_amts
WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
arp_standard.debug('delete_profile_amount(-)');
arp_standard.debug('EXCEPTION NO_DATA_FOUND: hzp_cprof_pkg.delete_profile_amout');
arp_standard.debug('EXCEPTION OTHERS: hzp_cprof_pkg.delete_profile_amout'||x_msg_data);