[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CLAIM_ID
,OBJECT_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,PROGRAM_ID
,CREATED_FROM
,BATCH_ID
,CLAIM_NUMBER
,CLAIM_TYPE_ID
,CLAIM_CLASS
,CLAIM_DATE
,DUE_DATE
,OWNER_ID
,HISTORY_EVENT
,HISTORY_EVENT_DATE
,HISTORY_EVENT_DESCRIPTION
,SPLIT_FROM_CLAIM_ID
,DUPLICATE_CLAIM_ID
,SPLIT_DATE
,ROOT_CLAIM_ID
,AMOUNT
,AMOUNT_ADJUSTED
,AMOUNT_REMAINING
,AMOUNT_SETTLED
,ACCTD_AMOUNT
,ACCTD_AMOUNT_REMAINING
,TAX_AMOUNT
,TAX_CODE
,TAX_CALCULATION_FLAG
,CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE_DATE
,EXCHANGE_RATE
,SET_OF_BOOKS_ID
,ORIGINAL_CLAIM_DATE
,SOURCE_OBJECT_ID
,SOURCE_OBJECT_CLASS
,SOURCE_OBJECT_TYPE_ID
,SOURCE_OBJECT_NUMBER
,CUST_ACCOUNT_ID
,CUST_BILLTO_ACCT_SITE_ID
,CUST_SHIPTO_ACCT_SITE_ID
,LOCATION_ID
,PAY_RELATED_ACCOUNT_FLAG
,RELATED_CUST_ACCOUNT_ID
,RELATED_SITE_USE_ID
,RELATIONSHIP_TYPE
,VENDOR_ID
,VENDOR_SITE_ID
,REASON_TYPE
,REASON_CODE_ID
,TASK_TEMPLATE_GROUP_ID
,STATUS_CODE
,USER_STATUS_ID
,SALES_REP_ID
,COLLECTOR_ID
,CONTACT_ID
,BROKER_ID
,TERRITORY_ID
,CUSTOMER_REF_DATE
,CUSTOMER_REF_NUMBER
,ASSIGNED_TO
,RECEIPT_ID
,RECEIPT_NUMBER
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,GL_DATE
,PAYMENT_METHOD
,VOUCHER_ID
,VOUCHER_NUMBER
,PAYMENT_REFERENCE_ID
,PAYMENT_REFERENCE_NUMBER
,PAYMENT_REFERENCE_DATE
,PAYMENT_STATUS
,APPROVED_FLAG
,APPROVED_DATE
,APPROVED_BY
,SETTLED_DATE
,SETTLED_BY
,EFFECTIVE_DATE
,CUSTOM_SETUP_ID
,TASK_ID
,COUNTRY_ID
,COMMENTS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,DEDUCTION_ATTRIBUTE_CATEGORY
,DEDUCTION_ATTRIBUTE1
,DEDUCTION_ATTRIBUTE2
,DEDUCTION_ATTRIBUTE3
,DEDUCTION_ATTRIBUTE4
,DEDUCTION_ATTRIBUTE5
,DEDUCTION_ATTRIBUTE6
,DEDUCTION_ATTRIBUTE7
,DEDUCTION_ATTRIBUTE8
,DEDUCTION_ATTRIBUTE9
,DEDUCTION_ATTRIBUTE10
,DEDUCTION_ATTRIBUTE11
,DEDUCTION_ATTRIBUTE12
,DEDUCTION_ATTRIBUTE13
,DEDUCTION_ATTRIBUTE14
,DEDUCTION_ATTRIBUTE15
,ORG_ID
,wo_rec_trx_id
,legal_entity_id
INTO
x_claim_rec.claim_id
,x_claim_rec.object_version_number
,x_claim_rec.last_update_date
,x_claim_rec.last_updated_by
,x_claim_rec.creation_date
,x_claim_rec.created_by
,x_claim_rec.last_update_login
,x_claim_rec.request_id
,x_claim_rec.program_application_id
,x_claim_rec.program_update_date
,x_claim_rec.program_id
,x_claim_rec.created_from
,x_claim_rec.batch_id
,x_claim_rec.claim_number
,x_claim_rec.claim_type_id
,x_claim_rec.claim_class
,x_claim_rec.claim_date
,x_claim_rec.due_date
,x_claim_rec.owner_id
,x_claim_rec.history_event
,x_claim_rec.history_event_date
,x_claim_rec.history_event_description
,x_claim_rec.split_from_claim_id
,x_claim_rec.duplicate_claim_id
,x_claim_rec.split_date
,x_claim_rec.root_claim_id
,x_claim_rec.amount
,x_claim_rec.amount_adjusted
,x_claim_rec.amount_remaining
,x_claim_rec.amount_settled
,x_claim_rec.acctd_amount
,x_claim_rec.acctd_amount_remaining
,x_claim_rec.tax_amount
,x_claim_rec.tax_code
,x_claim_rec.tax_calculation_flag
,x_claim_rec.currency_code
,x_claim_rec.exchange_rate_type
,x_claim_rec.exchange_rate_date
,x_claim_rec.exchange_rate
,x_claim_rec.set_of_books_id
,x_claim_rec.original_claim_date
,x_claim_rec.source_object_id
,x_claim_rec.source_object_class
,x_claim_rec.source_object_type_id
,x_claim_rec.source_object_number
,x_claim_rec.cust_account_id
,x_claim_rec.cust_billto_acct_site_id
,x_claim_rec.cust_shipto_acct_site_id
,x_claim_rec.location_id
,x_claim_rec.pay_related_account_flag
,x_claim_rec.related_cust_account_id
,x_claim_rec.related_site_use_id
,x_claim_rec.relationship_type
,x_claim_rec.vendor_id
,x_claim_rec.vendor_site_id
,x_claim_rec.reason_type
,x_claim_rec.reason_code_id
,x_claim_rec.task_template_group_id
,x_claim_rec.status_code
,x_claim_rec.user_status_id
,x_claim_rec.sales_rep_id
,x_claim_rec.collector_id
,x_claim_rec.contact_id
,x_claim_rec.broker_id
,x_claim_rec.territory_id
,x_claim_rec.customer_ref_date
,x_claim_rec.customer_ref_number
,x_claim_rec.assigned_to
,x_claim_rec.receipt_id
,x_claim_rec.receipt_number
,x_claim_rec.doc_sequence_id
,x_claim_rec.doc_sequence_value
,x_claim_rec.gl_date
,x_claim_rec.payment_method
,x_claim_rec.voucher_id
,x_claim_rec.voucher_number
,x_claim_rec.payment_reference_id
,x_claim_rec.payment_reference_number
,x_claim_rec.payment_reference_date
,x_claim_rec.payment_status
,x_claim_rec.approved_flag
,x_claim_rec.approved_date
,x_claim_rec.approved_by
,x_claim_rec.settled_date
,x_claim_rec.settled_by
,x_claim_rec.effective_date
,x_claim_rec.custom_setup_id
,x_claim_rec.task_id
,x_claim_rec.country_id
,x_claim_rec.comments
,x_claim_rec.attribute_category
,x_claim_rec.attribute1
,x_claim_rec.attribute2
,x_claim_rec.attribute3
,x_claim_rec.attribute4
,x_claim_rec.attribute5
,x_claim_rec.attribute6
,x_claim_rec.attribute7
,x_claim_rec.attribute8
,x_claim_rec.attribute9
,x_claim_rec.attribute10
,x_claim_rec.attribute11
,x_claim_rec.attribute12
,x_claim_rec.attribute13
,x_claim_rec.attribute14
,x_claim_rec.attribute15
,x_claim_rec.deduction_attribute_category
,x_claim_rec.deduction_attribute1
,x_claim_rec.deduction_attribute2
,x_claim_rec.deduction_attribute3
,x_claim_rec.deduction_attribute4
,x_claim_rec.deduction_attribute5
,x_claim_rec.deduction_attribute6
,x_claim_rec.deduction_attribute7
,x_claim_rec.deduction_attribute8
,x_claim_rec.deduction_attribute9
,x_claim_rec.deduction_attribute10
,x_claim_rec.deduction_attribute11
,x_claim_rec.deduction_attribute12
,x_claim_rec.deduction_attribute13
,x_claim_rec.deduction_attribute14
,x_claim_rec.deduction_attribute15
,x_claim_rec.org_id
,x_claim_rec.wo_rec_trx_id
,x_claim_rec.legal_entity_id
FROM ozf_claims_all
WHERE claim_id = p_claim_id ;
OZF_SETTLEMENT_DOC_PVT.Update_Claim_From_Settlement(
p_api_version_number => 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_count => x_msg_count,
x_msg_data => x_msg_data,
p_claim_id => p_claim_rec.claim_id,
p_object_version_number => p_claim_rec.object_version_number,
p_status_code => 'CLOSED',
p_payment_status => 'PAID'
);
SELECT claim_number, claim_id, customer_ref_number
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT p.claim_number, p.claim_id, c.customer_ref_number
FROM ozf_claims c , ozf_claims p
WHERE c.claim_id = cv_claim_id
AND c.root_claim_id = p.claim_id;
SELECT deduction_attribute1
, deduction_attribute2
, deduction_attribute3
, deduction_attribute4
, deduction_attribute5
, deduction_attribute6
, deduction_attribute7
, deduction_attribute8
, deduction_attribute9
, deduction_attribute10
, deduction_attribute11
, deduction_attribute12
, deduction_attribute13
, deduction_attribute14
, deduction_attribute15
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
SELECT payment_schedule_id
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT payment_schedule_id
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT ABS(amount_due_remaining)
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT source_object_id, payment_method -- For bug#9279072
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM ar_receivable_applications_all
WHERE cash_receipt_id = p_cash_receipt_id
AND applied_customer_trx_id = p_customer_trx_id
AND status = 'APP'
AND display = 'Y';
SELECT deduction_attribute1
, deduction_attribute2
, deduction_attribute3
, deduction_attribute4
, deduction_attribute5
, deduction_attribute6
, deduction_attribute7
, deduction_attribute8
, deduction_attribute9
, deduction_attribute10
, deduction_attribute11
, deduction_attribute12
, deduction_attribute13
, deduction_attribute14
, deduction_attribute15
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
| Update_Dispute_Amount
|
| NOTES
|
| HISTORY
| 15-MAR-2002 mchang Create.
*=======================================================================*/
PROCEDURE Update_dispute_amount(
p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
,p_dispute_amount IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Dispute_Amount';
SELECT claim_number
FROM ozf_claims
WHERE claim_id = cv_root_claim_id;
ARP_DEDUCTION_COVER.update_amount_in_dispute(
p_customer_trx_id => p_claim_rec.source_object_id,
p_claim_number => l_root_claim_number,
p_amount => p_dispute_amount,
p_init_msg_list => FND_API.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Update_Dispute_Amount;
SELECT name
FROM ra_batch_sources bs
, ozf_sys_parameters sys
WHERE sys.batch_source_id = bs.batch_source_id
AND sys.set_of_books_id = cv_set_of_books_id;
SELECT reason_code
FROM ozf_reason_codes_all_b
WHERE reason_code_id = cv_reason_code_id;
SELECT claim_number
FROM ozf_claims
WHERE claim_id = cv_root_claim_id;
SELECT SUM(NVL(amount_line_items_remaining, 0))
, SUM(NVL(tax_remaining, 0))
, SUM(NVL(freight_remaining, 0))
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM ra_customer_trx
WHERE customer_trx_id = cv_cust_trx_id;
SELECT oc.customer_reason, oc.customer_ref_number,
oc.reason_code_id, osp.post_to_gl,
(SELECT SUBSTRB(name,1,30) FROM ozf_reason_codes_vl WHERE reason_code_id = oc.reason_code_id) as name
FROM ozf_sys_parameters_all osp,
ozf_claims_all oc
WHERE
NVL(osp.org_id, -99) = NVL(oc.org_id, -99)
AND oc.claim_id = cv_claim_id;
Update_dispute_amount(
p_claim_rec => p_claim_rec
,p_dispute_amount => x_cm_amount
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
SELECT cb_trx_type_id
FROM ozf_claim_types_vl
WHERE claim_type_id = cv_claim_type_id;
SELECT CB_TRX_TYPE_ID
FROM ozf_sys_parameters
WHERE set_of_books_id = cv_set_of_books_id;
SELECT invoicing_reason_code
FROM ozf_reason_codes_all_b
WHERE reason_code_id = cv_reason_code_id;
SELECT default_reference
FROM ra_batch_sources
WHERE batch_source_id = 12;
SELECT customer_reason, customer_ref_number, reason_code_id
--, customer_ref_date --11.5.10 enhancements - TM should pass
-- Uncomment and Corresponding change for this has to be done while assigning to code after
-- AR enhancement for this is done.
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT SUBSTRB(name,1,30) name
FROM ozf_reason_codes_vl
WHERE reason_code_id = cv_reason_code_id;
SELECT DECODE(MAX(gl.period_name), '', 0, 1)
FROM gl_period_statuses gl
WHERE gl.application_id = 222
AND gl.set_of_books_id = p_set_of_books_id
AND gl.adjustment_period_flag = 'N'
AND p_gl_date BETWEEN gl.start_date AND gl.end_date
AND gl.closing_status IN ('O', 'F');
SELECT receivable_application_id
, applied_payment_schedule_id
FROM ar_receivable_applications
WHERE cash_receipt_id = cv_cash_receipt_id
AND applied_customer_trx_id = cv_customer_trx_id
AND application_ref_type = 'CLAIM'
ANd secondary_application_ref_id = cv_claim_id
AND display = 'Y';
SELECT adjustment_reason_code
FROM ozf_reason_codes_vl
WHERE reason_code_id = cv_reason_code_id;
SELECT claim_number
FROM ozf_claims
WHERE claim_id = cv_root_claim_id;
SELECT adj_rec_trx_id
, wo_rec_trx_id
, neg_wo_rec_trx_id
FROM ozf_claim_types_vl
WHERE claim_type_id = cv_claim_type_id;
SELECT adj_rec_trx_id
, wo_rec_trx_id
, neg_wo_rec_trx_id
FROM ozf_sys_parameters;
SELECT claim_number, claim_id, customer_ref_number
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT p.claim_number, p.claim_id, c.customer_ref_number
FROM ozf_claims c , ozf_claims p
WHERE c.claim_id = cv_claim_id
AND c.root_claim_id = p.claim_id;
SELECT pay.amount_due_remaining,
pay.amount_line_items_remaining,
pay.tax_remaining,
pay.freight_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;
| Update Deduction payment detail
*------------------------------------------------------------*/
IF g_debug THEN
OZF_Utility_PVT.debug_message('l_x_wo_adjust_id :' || l_x_wo_adjust_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 => 'WRITE_OFF'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_claim_rec.source_object_id
,p_adjust_id => l_x_wo_adjust_id
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
);
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 => 'WRITE_OFF'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_claim_rec.source_object_id
,p_adjust_id => l_x_wo_adjust_id
);
ARP_DEDUCTION_COVER.update_amount_in_dispute(
p_customer_trx_id => p_claim_rec.source_object_id,
p_claim_number => l_root_claim_number,
p_amount => p_write_off_amount * -1,
p_init_msg_list => FND_API.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
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 => 'WRITE_OFF'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_claim_rec.source_object_id
,p_adjust_id => l_x_wo_adjust_id
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => 'MASS_SETTLEMENT'
);
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 => 'WRITE_OFF'
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_claim_rec.receipt_id
,p_customer_trx_id => p_claim_rec.source_object_id
,p_adjust_id => l_x_wo_adjust_id
);
UPDATE ozf_claims_all
SET wo_rec_trx_id = l_receivables_trx_id
WHERE claim_id = p_claim_rec.claim_id
AND wo_rec_trx_id IS NULL;
SELECT source_object_id
, source_object_line_id
, credit_to
, SUM(quantity) qty
, AVG(rate) rate
, SUM(NVL(claim_currency_amount,0)) amount
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id
AND source_object_id = cv_inv_id
GROUP BY source_object_id,source_object_line_id,credit_to;
SELECT ar.applied_customer_trx_id,
-- cli.source_object_id,
cli.source_object_line_id,
cli.credit_to,
SUM(cli.quantity) qty,
AVG(cli.rate) rate,
SUM(NVL(cli.claim_currency_amount,0)) amount
FROM ar_receivable_applications_all ar,
ozf_claims_all cla,
ozf_claim_lines_all cli
WHERE cla.claim_id = cli.claim_id
AND ar.cash_receipt_id = cla.receipt_id
AND ar.status ='APP'
AND ar.display ='Y'
AND cla.claim_id = cv_claim_id
AND ar.applied_customer_trx_id = cv_inv_id
GROUP BY ar.applied_customer_trx_id,cli.source_object_line_id,cli.credit_to;
SELECT rec.amount_applied
, pay.amount_due_remaining
, NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
, NVL(pay.tax_remaining, 0) tax_remaining
, NVL(pay.freight_remaining, 0) freight_remaining
, 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
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.display = 'Y';
SELECT 0 amount_applied
, 0 amount_due_remaining
, 0 amount_line_items_remaining
, 0 tax_remaining
, 0 freight_remaining
, 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
FROM ar_receivable_applications_all rec
WHERE rec.cash_receipt_id = cv_cash_receipt_id
AND rec.display = 'Y';
SELECT COUNT(claim_line_id)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT rec.cash_receipt_id
FROM ar_receivable_applications_all rec
, ar_payment_schedules pay
WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
AND pay.customer_trx_id = cv_invoice_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 customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = cv_invoice_line_id;
| -> 4. Update dispute amount.
| Invoice Deduction - Credit to Invoice
| -> create credit memo for the invoice
*------------------------------------------------------------*/
-- Update dispute amount.
IF g_debug THEN
OZF_Utility_PVT.debug_message('Source Deduction -> Update dispute amount');
Update_dispute_amount(
p_claim_rec => p_claim_rec
,p_dispute_amount => l_cm_amount
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
| Update Deduction payment detail
*------------------------------------------------------------*/
IF l_cm_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 => p_claim_rec.payment_method
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => NULL
,p_customer_trx_id => l_cm_customer_trx_id
,p_adjust_id => NULL
);
SELECT rec.amount_applied
, pay.amount_due_remaining
, NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
, NVL(pay.tax_remaining, 0) tax_remaining
, NVL(pay.freight_remaining, 0) freight_remaining
, 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
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.display = 'Y';
SELECT COUNT(claim_line_id)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT source_object_class
, source_object_id
, source_object_line_id
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT rec.cash_receipt_id
FROM ar_receivable_applications_all rec
, ar_payment_schedules pay
WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
AND pay.customer_trx_id = cv_invoice_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 customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = cv_invoice_line_id;
SELECT source_object_id, sum(claim_currency_amount) amt
FROM ozf_claim_lines_all
WHERE claim_id = cv_claim_id
GROUP BY source_object_id;
SELECT ar.applied_customer_trx_id,
cli.source_object_id,
SUM(cli.claim_currency_amount) amt
FROM ar_receivable_applications_all ar,
ozf_claims_all cla,
ozf_claim_lines_all cli
WHERE cla.claim_id = cli.claim_id
AND ar.cash_receipt_id = cla.receipt_id
AND ar.status ='APP'
AND ar.display ='Y'
AND cla.claim_id = cv_claim_id
GROUP BY ar.applied_customer_trx_id,cli.source_object_id;
SELECT
cli.source_object_id,
SUM(cli.claim_currency_amount) amt
FROM
ozf_claims_all cla,
ozf_claim_lines_all cli
WHERE cla.claim_id = cli.claim_id
AND cla.claim_id = cv_claim_id
GROUP BY cli.source_object_id;
SELECT count(*)
FROM ar_receivable_applications_all ar,
ozf_claims_all cla,
ozf_claim_lines_all cli
WHERE cla.claim_id = cli.claim_id
AND ar.cash_receipt_id = cla.receipt_id
AND ar.status ='APP'
AND ar.display ='Y'
AND cla.claim_id = cv_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.application_ref_type
, rec.application_ref_id
, rec.application_ref_num
, rec.secondary_application_ref_id
, sum(rec.amount_applied) amount_applied
, sum(rec.amount_applied_from) amount_applied_from --4684931
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
group by rec.application_ref_type, rec.application_ref_id, rec.application_ref_num,
rec.secondary_application_ref_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
, rec.amount_applied_from --4684931
, 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,
amount_applied_from, --4684931
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';
SELECT invoice_currency_code
FROM ra_customer_trx
WHERE customer_trx_id = cv_customer_trx_id;
SELECT currency_code
FROM ar_cash_receipts
WHERE cash_receipt_id = cv_cash_receipt_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
);
| Invoice Deduction -> 1. Update amount in dispute
| 2. Unapply invoice from receipt.
| 3. Apply credit memo with amount_settled on receipt.
| 4. Apply original invoice with old balance + amount_settled.
| <>
| 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
| 1.5. Unapply existing credit memo
| 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');
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 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 => p_claim_rec.payment_method
,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_amount => l_settlement_amount -- Bug4308188
);
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 g_debug 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
);
| 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 => p_claim_rec.payment_method
,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
);
| 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 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,
amount_applied_from,--bug 4684931
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';
SELECT claim_id, amount_due_remaining FROM ozf_claims_all, ar_payment_schedules_all
WHERE source_object_id = customer_trx_id
AND source_object_class = class
AND claim_class = 'DEDUCTION'
AND cust_account_id = customer_id
AND customer_trx_id = p_customer_trx_id;
SELECT invoice_currency_code
FROM ra_customer_trx
WHERE customer_trx_id = cv_customer_trx_id;
SELECT currency_code
FROM ar_cash_receipts
WHERE cash_receipt_id = cv_cash_receipt_id;
SELECT rec.application_ref_type
, rec.application_ref_id
, rec.application_ref_num
, rec.secondary_application_ref_id
, sum(rec.amount_applied) amount_applied
, sum(rec.amount_applied_from) amount_applied_from --4684931
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
group by rec.application_ref_type, rec.application_ref_id, rec.application_ref_num,
rec.secondary_application_ref_id;
SELECT source_object_class
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT amount_applied
FROM ar_receivable_applications_all
WHERE secondary_application_ref_id = cv_claim_id;
SELECT amount_applied
FROM ar_payment_schedules_all
WHERE customer_trx_id = cv_customer_trx_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
*------------------------------------------------------------*/
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 => p_claim_rec.payment_method
,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_amount => l_settlement_amount -- Bug4308188
);
OZF_Utility_PVT.debug_message('Before calling the update_payment_detail ==> '||l_claim_rec.claim_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 => l_claim_rec.claim_id
,p_payment_method => 'ACCOUNTING_ONLY'
,p_deduction_type => 'CLAIM'
,p_cash_receipt_id => NULL
,p_customer_trx_id => NULL --l_claim_rec.source_object_id
,p_adjust_id => NULL
);
OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_return_status);
OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_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
);
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
);