The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_id
from ozf_claims_all
where claim_id = p_id;
SELECT approver_seq, approver_type, object_approver_id
FROM ams_approvers
WHERE ams_approval_detail_id = rule_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE -1 )
AND TRUNC(NVL(end_date_active,SYSDATE + 1));
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => p_activity_id,
p_object_type_code => 'CLAM',
p_sequence_num => null,
p_action_code => null,
p_object_version_num => null,
p_approval_type => 'EARNING'
);
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => p_activity_id,
p_object_type_code => 'CLAM',
p_sequence_num => null,
p_action_code => null,
p_object_version_num => null,
p_approval_type => 'PERFORMANCE'
);
SELECT o.offer_code
FROM ozf_offers o
WHERE o.qp_list_header_id = cv_offer_id;
SELECT o.offer_code
FROM ozf_offers o
WHERE o.qp_list_header_id = cv_offer_id;
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
AND a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
AND rc.reason_code = rlk_cm.lookup_code(+)
AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
AND a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
AND rc.reason_code = rlk_cm.lookup_code(+)
AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
AND a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
AND rc.reason_code = rlk_cm.lookup_code(+)
AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
ANd a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
AND rc.reason_code = rlk_cm.lookup_code(+)
AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
AND a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
AND rc.reason_code = rlk_cm.lookup_code(+)
AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
SELECT claim_number
, custom_setup_id
, amount_settled
, org_id
, to_char(claim_type_id) -- 'CLAM' changed by slkrishn
, to_char(reason_code_id) -- added by slkrishn for reason(priority)
, claim_date
, due_date
, owner_id
, currency_code
FROM ozf_claims_all
WHERE claim_id = p_act_id;
SELECT c.claim_number
, a.account_number
, hp.party_name
, curr.name
, c.amount_settled
, c.claim_date
, c.due_date
, ct.name
, hc.location
, rlk_cm.meaning
, rlk_adj.meaning
, lk_pm.meaning
, 'resource_name'
, c.comments
FROM ozf_claims_all c
, hz_cust_accounts a
, hz_parties hp
, ozf_claim_types_all_vl ct
, hz_cust_site_uses_all hc
, ozf_reason_codes_all_b rc
, ar_lookups rlk_cm
, ar_lookups rlk_adj
, ozf_lookups lk_pm
, fnd_currencies_vl curr
WHERE c.claim_id = p_claim_id
AND c.cust_account_id = a.cust_account_id
AND a.party_id = hp.party_id
AND c.currency_code = curr.currency_code
AND c.claim_type_id = ct.claim_type_id
AND c.cust_billto_acct_site_id = hc.site_use_id(+)
AND c.reason_code_id = rc.reason_code_id
AND c.payment_method = lk_pm.lookup_code
/* [BEGIN OF FIXING BUG2474662 22-JUL-2002]: overpayment is not been selected. */
--AND lk_pm.lookup_type = decode(c.claim_class, 'CLAIM', 'OZF_CLAIM_PAYMENT_METHOD', 'OZF_DEDUCTION_PAYMENT_METHOD');
UPDATE ozf_claims_all
SET appr_wf_item_key = itemkey
WHERE claim_id = l_activity_id;
SELECT object_version_number
--, user_status_id
, owner_id
, comments
FROM ozf_claims_all --//Bugfix : 8442938 - Changed table name from ozf_claims to ozf_claims_all
WHERE claim_id = l_claim_id;
UPDATE ozf_claims_all
SET OBJECT_VERSION_NUMBER = l_object_version_number
WHERE claim_id = p_claim_id;
PROCEDURE update_claim_status(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Status';
l_update_status VARCHAR2(12);
SELECT object_version_number
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT amount_settled
, payment_method
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT claim_id,acctd_amount,
acctd_amount_adjusted,
amount,
amount_adjusted
FROM ozf_claims_all
WHERE group_claim_id =p_claim_id;
l_update_status := wf_engine.getitemattrtext(
itemtype => itemtype
,itemkey => itemkey
,aname => 'UPDATE_GEN_STATUS'
);
IF l_update_status IN ('APPROVED', 'REJECTED') AND
(l_user_id IS NULL OR l_resp_id IS NULL OR l_appl_id IS NULL) THEN
l_update_status := NULL;
,aname => 'UPDATE_GEN_STATUS'
,avalue => l_update_status
);
,'UPDATE_CLAIM_STATUS'
,itemtype
,itemkey
,actid
,l_error_msg
);
ELSIF l_update_status IN ('APPROVED', 'REJECTED') THEN
FND_GLOBAL.apps_initialize( user_id => l_user_id
, resp_id => l_resp_id
, resp_appl_id => l_appl_id
--, security_group_id => l_security_group_id
);
IF l_update_status = 'APPROVED' AND
l_approval_type = 'EARNING' THEN
IF OZF_Claim_Accrual_PVT.Perform_Approval_Required(l_claim_id) = FND_API.g_true THEN
-- Start Performance Approval
Start_Approval_Process(
p_claim_id => l_claim_id
,p_orig_status_id => l_orig_status_id
,p_new_status_id => l_new_status_id
,p_reject_status_id => l_reject_status_id
,p_approval_type => 'PERFORMANCE'
,x_return_status => l_return_status
);
ELSIF l_update_status = 'APPROVED' AND
l_approval_type = 'PERFORMANCE' THEN
-- Start Performance Approval
OZF_CLAIM_SETTLEMENT_PVT.Claim_Approval_Required(p_claim_id => l_claim_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,x_approval_require => l_approval_require);
IF l_update_status IN ('APPROVED', 'REJECTED') THEN
--set message.
FND_MESSAGE.Set_Name('AMS','AMS_WF_NTF_AMOUNT_CHANGE_FYI');
IF l_update_status = 'APPROVED' THEN
l_next_status_id := l_new_status_id;
ozf_utility_pvt.debug_message(l_full_name ||': update_status = ' || l_update_status);
ELSIF l_update_status = 'REJECTED' THEN
l_next_status_id := l_reject_status_id;
ozf_utility_pvt.debug_message(l_full_name || l_status_code || l_approved_amount || l_update_status);
IF l_update_status = 'APPROVED' THEN
OZF_MASS_SETTLEMENT_PVT.Start_Mass_Payment(
p_group_claim_id => l_claim_rec.claim_id,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count
);
UPDATE ozf_claims_all
SET amount_settled = l_amt_settled,
amount_remaining = l_amt -(l_amt_adjusted + l_amt_settled),
acctd_amount_settled = l_act_amt_settled,
acctd_amount_remaining = l_act_amt - (l_act_amt_adjusted + l_act_amt_settled),
settled_by = NVL(FND_GLOBAL.user_id,-1), --//Bugfix : 8202109
settled_date = TRUNC(SYSDATE)
WHERE claim_id = l_claim_id1;
OZF_Claim_PVT.Update_Claim(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,p_claim => l_claim_rec
,p_event => 'UPDATE'
,p_mode => 'AUTO'
,x_object_version_number => l_object_version_number
);
,'UPDATE_CLAIM_STATUS'
,itemtype
,itemkey
,actid
,l_error_msg
);
END update_claim_status;