The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_row
( p_row_id OUT NOCOPY VARCHAR2
, p_credit_usage_rule_set_id NUMBER
, p_credit_usage_id NUMBER
, p_credit_profile_amt_id NUMBER
, p_cust_acct_profile_amt_id NUMBER
, p_profile_class_amt_id NUMBER
, p_creation_date DATE
, p_created_by NUMBER
, p_last_update_date DATE
, p_last_updated_by NUMBER
, p_last_update_login NUMBER
, p_attribute_category VARCHAR2
, p_attribute1 VARCHAR2
, p_attribute2 VARCHAR2
, p_attribute3 VARCHAR2
, p_attribute4 VARCHAR2
, p_attribute5 VARCHAR2
, p_attribute6 VARCHAR2
, p_attribute7 VARCHAR2
, p_attribute8 VARCHAR2
, p_attribute9 VARCHAR2
, p_attribute10 VARCHAR2
, p_attribute11 VARCHAR2
, p_attribute12 VARCHAR2
, p_attribute13 VARCHAR2
, p_attribute14 VARCHAR2
, p_attribute15 VARCHAR2
)
IS
CURSOR usages_csr IS
SELECT
rowid
FROM
HZ_CREDIT_USAGES
WHERE credit_usage_id=p_credit_usage_id;
INSERT INTO hz_credit_usages
( credit_usage_id
, credit_profile_amt_id
, cust_acct_profile_amt_id
, profile_class_amount_id
, credit_usage_rule_set_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
VALUES
( p_credit_usage_id
, p_credit_profile_amt_id
, p_cust_acct_profile_amt_id
, p_profile_class_amt_id
, p_credit_usage_rule_set_id
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login
, null
, null
, null
, null
, p_attribute_category
, p_attribute1
, p_attribute2
, p_attribute3
, p_attribute4
, p_attribute5
, p_attribute6
, p_attribute7
, p_attribute8
, p_attribute9
, p_attribute10
, p_attribute11
, p_attribute12
, p_attribute13
, p_attribute14
, p_attribute15
);
FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
END Insert_row;
PROCEDURE Delete_row
( p_row_id VARCHAR2
)
IS
BEGIN
DELETE
FROM HZ_CREDIT_USAGES
WHERE ROWID=p_row_id;
FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
END Delete_row;
SELECT *
FROM hz_credit_usages
WHERE rowid=CHARTOROWID(p_row_id)
FOR UPDATE OF cust_acct_profile_amt_id NOWAIT;
FND_MESSAGE.Set_name('FND', 'FORM_RECORD_DELETED');
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
ORDER BY cpa.overall_credit_limit ;
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'Y'
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND NVL(cur2.exclude_flag,'N') = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
)
ORDER BY cpa.overall_credit_limit ;
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.site_use_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
ORDER BY cpa.overall_credit_limit ;
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.site_use_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND NVL(cur2.exclude_flag,'N') = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
)
ORDER BY cpa.overall_credit_limit ;
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.party_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
ORDER BY cpa.overall_credit_limit ;
SELECT cpa.cust_acct_profile_amt_id
, cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.party_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND NVL(cur2.exclude_flag,'N') = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
)
ORDER BY cpa.overall_credit_limit ;
SELECT rset.credit_usage_rule_set_id ,
rset.global_exposure_flag
FROM hz_credit_usages usg
, hz_credit_usage_rule_sets_b rset
WHERE usg.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id
AND rset.credit_usage_rule_set_id = usg.credit_usage_rule_set_id ;
SELECT 'X'
FROM hz_credit_usage_rules
WHERE credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND usage_type = 'CURRENCY'
AND NVL(include_all_flag, 'N') = 'Y';
SELECT user_code
FROM hz_credit_usage_rules cur
WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND cur.usage_type = 'CURRENCY'
AND cur.user_code IS NOT NULL
AND NVL(cur.exclude_flag,'N') = 'N';
SELECT user_code
FROM hz_credit_usage_rules cur
WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND cur.usage_type = 'CURRENCY'
AND cur.user_code IS NOT NULL
AND NVL(cur.exclude_flag,'N') = 'Y';
x_usage_curr_tbl.DELETE ;