[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE ozf_claims_all
SET payment_status = 'PAID'
, status_code = 'CLOSED'
, user_status_id = OZF_UTILITY_PVT.get_default_user_status(
'OZF_CLAIM_STATUS'
,'CLOSED'
)
WHERE group_claim_id = p_group_claim_id
AND claim_id = p_claim_id;
SELECT rec.amount_applied
FROM ar_receivable_applications rec
WHERE rec.applied_payment_schedule_id = -4
AND rec.cash_receipt_id = cv_cash_receipt_id
AND rec.application_ref_type = 'CLAIM'
AND rec.display = 'Y'
AND rec.secondary_application_ref_id = cv_root_claim_id;
SELECT rec.comments
, rec.payment_set_id
, rec.application_ref_type
, rec.application_ref_id
, rec.application_ref_num
, rec.secondary_application_ref_id
, rec.application_ref_reason
, rec.customer_reference
, rec.amount_applied
, pay.amount_due_remaining
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
AND rec.cash_receipt_id = cv_cash_receipt_id
AND pay.customer_trx_id = cv_customer_trx_id
AND rec.application_ref_type = 'CLAIM'
AND rec.display = 'Y'
AND rec.secondary_application_ref_id = cv_root_claim_id;
OZF_AR_Payment_PVT.Update_dispute_amount(
p_claim_rec => p_claim_rec
,p_dispute_amount => (p_amount_applied * -1)
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => 'RECEIPT'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => NULL
,p_adjust_id => NULL
,p_settlement_doc_id => NULL
,p_settlement_mode => 'MASS_SETTLEMENT'
);
OZF_AR_Payment_PVT.Update_dispute_amount(
p_claim_rec => l_payment_claim_rec
,p_dispute_amount => (p_amount_applied * -1)
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => 'RECEIPT'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => NULL
,p_adjust_id => NULL
,p_settlement_doc_id => NULL
,p_settlement_mode => 'MASS_SETTLEMENT'
);
SELECT rec.comments
, rec.payment_set_id
, rec.application_ref_type
, rec.application_ref_id
, rec.application_ref_num
, rec.secondary_application_ref_id
, rec.application_ref_reason
, rec.customer_reference
, rec.amount_applied
, pay.amount_due_remaining
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
AND rec.cash_receipt_id = cv_cash_receipt_id
AND pay.customer_trx_id = cv_customer_trx_id
AND rec.application_ref_type = 'CLAIM'
AND rec.display = 'Y'
AND rec.secondary_application_ref_id = cv_root_claim_id;
SELECT amount_applied
FROM ar_receivable_applications
WHERE application_ref_type = 'CLAIM'
AND applied_payment_schedule_id = -4
AND display = 'Y'
AND secondary_application_ref_id = cv_root_claim_id;
SELECT amount_applied, apply_date -- Fix for Bug 3091401. TM passes old apply date
FROM ar_receivable_applications
WHERE cash_receipt_id = cv_cash_receipt_id
AND applied_customer_trx_id = cv_customer_trx_id
AND display = 'Y'
AND status = 'APP';
| Update Claim Status to CLOSED.
*------------------------------------------------------------*/
/*
Close_Claim(
p_claim_rec => p_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Invoice Deduction -> 1. Update amount in dispute
| 2. Apply credit memo with amount_settled on receipt.
| 3. Reapply invoice related deduction.
| <>:
| Invoice Deduction -> 1. Update amount in dispute
| 2. Reapply credit memo with increase amount on receipt.
| 3. Reapply invoice related deduction.
*------------------------------------------------------------*/
OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
, p_claim_rec.source_object_id
, p_claim_rec.root_claim_id
);
OZF_Utility_PVT.debug_message('Invoice Deduction -> 1. Update amount in dispute');
OZF_AR_Payment_PVT.Update_dispute_amount(
p_claim_rec => p_claim_rec
,p_dispute_amount => (p_credit_memo_amount * -1)
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Update payment detail
*------------------------------------------------------------*/
IF l_online_upd_ded_status THEN
-- Update Deduction payment detail
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => 'PREV_OPEN_CREDIT'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_payment_reference_id
,p_adjust_id => NULL
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
,p_settlement_amount => l_settlement_amount --Bug4308188
);
SELECT dm_trx_type_id
FROM ozf_claim_types_all_b
WHERE claim_type_id = cv_claim_type_id;
SELECT amount_applied
FROM ar_receivable_applications
WHERE application_ref_type = 'CLAIM'
AND applied_payment_schedule_id = -4
AND display = 'Y'
AND secondary_application_ref_id = cv_root_claim_id;
SELECT trx_number
FROM ra_customer_trx
WHERE customer_trx_id = cv_customer_trx_id;
SELECT amount_applied, apply_date -- Fix for Bug 3091401. TM passes old apply date
FROM ar_receivable_applications
WHERE cash_receipt_id = cv_cash_receipt_id
AND applied_customer_trx_id = cv_customer_trx_id
AND display = 'Y'
AND status = 'APP';
| Update Claim Status to CLOSED.
*------------------------------------------------------------*/
/*
Close_Claim(
p_claim_rec => p_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Update Deduction payment detail
*------------------------------------------------------------*/
IF l_online_upd_ded_status THEN
-- Update Deduction payment detail
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => 'PREV_OPEN_DEBIT'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_payment_reference_id
,p_adjust_id => NULL
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
,p_settlement_amount => l_settlement_amount --Bug4308188
);
| Update Claim Status to CLOSED.
*------------------------------------------------------------*/
/*
Close_Claim(
p_claim_rec => p_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Update Deduction payment detail
*------------------------------------------------------------*/
IF l_cb_customer_trx_id IS NOT NULL THEN
-- Update Deduction payment detail
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => 'CHARGEBACK'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => l_cb_customer_trx_id
,p_adjust_id => NULL
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
);
UPDATE ozf_claims_all
SET payment_status = 'PENDING'
, status_code = 'PENDING_CLOSE'
, user_status_id = OZF_UTILITY_PVT.get_default_user_status(
'OZF_CLAIM_STATUS'
,'PENDING_CLOSE'
)
WHERE claim_id = p_claim_rec.claim_id;
| Update Claim Status to CLOSED.
*------------------------------------------------------------*/
/*
Close_Claim(
p_claim_rec => p_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
SELECT rec.amount_applied
FROM ar_receivable_applications rec
WHERE rec.applied_payment_schedule_id = -4
AND rec.cash_receipt_id = cv_cash_receipt_id
AND rec.application_ref_type = 'CLAIM'
AND rec.display = 'Y'
AND rec.secondary_application_ref_id = cv_root_claim_id;
| Update Claim Status to CLOSED.
*------------------------------------------------------------*/
/*
Close_Claim(
p_claim_rec => p_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Update Deduction payment detail
*------------------------------------------------------------*/
-- Update Deduction payment detail
IF OZF_DEBUG_HIGH_ON THEN
OZF_Utility_PVT.debug_message('cash_receipt_id = '||p_claim_rec.receipt_id);
OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_rec.claim_id
,p_payment_method => p_claim_rec.payment_method
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => NULL --p_claim_rec.payment_reference_id
,p_adjust_id => NULL
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
);
SELECT claim_id
, claim_number
, claim_class
, receipt_id
, receipt_number
, source_object_id
, source_object_number
, amount_remaining
FROM ozf_claims_all
WHERE group_claim_id = cv_group_claim_id
AND claim_class = cv_claim_class
ORDER BY claim_date, group_claim_id;
SELECT settlement_doc_id
, payment_method
, settlement_id
, settlement_number
, settlement_type_id
, settlement_amount
, gl_date
, wo_rec_trx_id
FROM ozf_settlement_docs_all
WHERE claim_id = cv_group_claim_id;
SELECT source_object_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
l_settle_doc_tbl2.delete(l_idx_setl_doc2);
SELECT claim_id
, claim_number
, object_version_number
FROM ozf_claims_all
WHERE group_claim_id = cv_group_claim_id;
SELECT gl_date_type
FROM ozf_sys_parameters;
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim => x_claim_tbl(i)
,p_event => 'UPDATE'
,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
,x_object_version_number => l_claim_obj_ver_num
);
SELECT object_version_number
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE object_attribute = 'APPR'
AND custom_setup_id =( SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE activity_type_code = 'GROUP'
AND object_type = 'CLAM'
AND enabled_flag = 'Y' );
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE object_attribute = 'APPR'
AND custom_setup_id = ( SELECT acs.custom_setup_id
FROM ams_custom_setups_b acs,
ozf_claim_def_rules_ALL ocd
WHERE acs.activity_type_code = 'GROUP'
AND acs.object_type = 'CLAM'
AND acs.enabled_flag = 'Y'
AND acs.custom_setup_id = ocd.custom_setup_id
AND ocd.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID() );
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 => x_msg_data
,x_msg_count => x_msg_count
,p_claim => l_claim_pvt_rec --l_complete_claim_group_tbl(i)
,p_event => 'UPDATE'
,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
,x_object_version_number => l_claim_obj_ver_num
);
UPDATE ozf_claims_all
SET payment_status = 'PENDING'
, status_code = 'PENDING_APPROVAL'
, user_status_id = OZF_UTILITY_PVT.get_default_user_status(
'OZF_CLAIM_STATUS'
,'PENDING_APPROVAL'
)
WHERE claim_id = p_group_claim_id;
UPDATE ozf_claims_all
SET payment_status = 'PENDING'
, status_code = 'PENDING_APPROVAL'
, user_status_id = OZF_UTILITY_PVT.get_default_user_status(
'OZF_CLAIM_STATUS'
,'PENDING_APPROVAL'
)
WHERE claim_id = p_group_claim_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_CLAIM_STATUS'
AND default_flag = 'Y'
AND system_status_code = cv_status_code;
UPDATE ozf_claims_all
SET status_code = 'REJECTED'
, user_status_id = l_rejected_user_status_id
WHERE claim_id = p_group_claim_id;
UPDATE ozf_claims_all
SET status_code = 'OPEN'
, user_status_id = l_open_user_status_id
, amount_remaining = amount - NVL(amount_adjusted,0)
, acctd_amount_remaining = acctd_amount - NVL(acctd_amount_adjusted,0)
, amount_settled = 0
, acctd_amount_settled = 0
, group_claim_id = null
, payment_method = null
WHERE group_claim_id = p_group_claim_id;
UPDATE ozf_settlement_docs_all
SET payment_status = 'CANCELLED'
WHERE group_claim_id = p_group_claim_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_CLAIM_STATUS'
AND default_flag = 'Y'
AND system_status_code = cv_status_code;
SELECT payment_reference_id
FROM ozf_claim_lines_all
WHERE claim_id = cv_group_claim_id;
SELECT settlement_doc_id
, payment_method
, settlement_id
, settlement_number
, settlement_type_id
, settlement_amount
, payment_reference_id
, payment_reference_number
, group_claim_id
, gl_date
, wo_rec_trx_id
FROM ozf_settlement_docs_all
WHERE claim_id = cv_claim_id
AND group_claim_id = cv_group_claim_id
AND payment_status <> 'CANCELLED';
SELECT status_code,
user_status_id
FROM ozf_claims_all
WHERE claim_id =p_claim_id;
p_event => 'UPDATE',
x_history_event => l_history_event,
x_history_event_description => l_history_event_description,
x_needed_to_create => l_needed_to_create,
x_return_status => l_return_status
);
UPDATE ozf_settlement_docs_all
SET payment_status = 'PENDING_PAID'
WHERE settlement_doc_id = l_settlement_doc_tbl(j).settlement_doc_id;
--//Update Claim History
IF l_status_code <>'CLOSED' THEN
l_claim_rec_hist.claim_id :=l_settlement_doc_tbl(j).payment_reference_id;
p_event => 'UPDATE',
x_history_event => l_history_event,
x_history_event_description => l_history_event_description,
x_needed_to_create => l_needed_to_create,
x_return_status => l_return_status
);
l_settlement_doc_tbl.DELETE;
UPDATE ozf_claims_all
SET payment_status = 'PAID'
, status_code = 'CLOSED'
, user_status_id = l_close_user_status_id
WHERE claim_id = p_group_claim_id;
SELECT amount_remaining
, object_version_number
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT set_of_books_id
FROM ozf_sys_parameters;
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE object_attribute = 'APPR'
AND custom_setup_id = ( SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE activity_type_code = 'GROUP'
AND object_type = 'CLAM'
AND enabled_flag = 'Y' );
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE object_attribute = 'APPR'
AND custom_setup_id = ( SELECT acs.custom_setup_id
FROM ams_custom_setups_b acs,
ozf_claim_def_rules_ALL ocd
WHERE acs.activity_type_code = 'GROUP'
AND acs.object_type = 'CLAM'
AND acs.enabled_flag = 'Y'
AND acs.custom_setup_id = ocd.custom_setup_id
AND ocd.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID() );
SELECT gl_date_type
FROM ozf_sys_parameters
WHERE set_of_books_id = cv_set_of_books_id;
SELECT acctd_amount,
acctd_amount_adjusted,
amount,
amount_adjusted
FROM ozf_claims_all
WHERE claim_id =p_claim_id;
l_open_claim_tbl.DELETE(i);
OZF_Utility_PVT.debug_message('Update Claim Id:'||l_open_claim_tbl(i).claim_id);
UPDATE ozf_claims_all
SET group_claim_id = l_group_claim_id
, comments = l_comments
--, gl_date = l_gl_date
--, wo_rec_trx_id = l_wo_rec_trx_id
WHERE claim_id = l_open_claim_tbl(i).claim_id;
UPDATE ozf_claims_all
SET amount = l_group_claim_amt
, amount_remaining = l_group_trx_amt
, amount_settled = l_group_settle_amt
, amount_adjusted = l_group_rem_amt
, settled_by = NVL(FND_GLOBAL.user_id,-1) --//Bugfix : 8202109
, settled_date = TRUNC(SYSDATE)
WHERE claim_id = l_group_claim_id;
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 =p_open_claim_tbl(i).claim_id;