[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT claim_tax_incl_flag
FROM ozf_sys_parameters_all
WHERE org_id = cv_org_id;
| Update_Payment_Detail
|
| NOTES
|
| HISTORY
| 20-Apr-2005 Sahana Bug4308188: Overloaded Procedure
*=======================================================================*/
PROCEDURE Update_Payment_Detail(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_validation_level IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_id IN NUMBER
,p_payment_method IN VARCHAR2
,p_deduction_type IN VARCHAR2
,p_cash_receipt_id IN NUMBER
,p_customer_trx_id IN NUMBER
,p_adjust_id IN NUMBER
,p_settlement_doc_id IN NUMBER
,p_settlement_mode IN VARCHAR2
) IS
BEGIN
Update_Payment_Detail(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_claim_id => p_claim_id
,p_payment_method => p_payment_method
,p_deduction_type => p_deduction_type
,p_cash_receipt_id => p_cash_receipt_id
,p_customer_trx_id => p_customer_trx_id
,p_adjust_id => p_adjust_id
,p_settlement_doc_id => p_settlement_doc_id
,p_settlement_mode => p_settlement_mode
,p_settlement_amount => NULL);
END Update_Payment_Detail;
| Update_Payment_Detail
|
| NOTES
|
| HISTORY
| 15-MAR-2002 mchang Create.
*=======================================================================*/
PROCEDURE Update_Payment_Detail(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_validation_level IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_id IN NUMBER
,p_payment_method IN VARCHAR2
,p_deduction_type IN VARCHAR2
,p_cash_receipt_id IN NUMBER
,p_customer_trx_id IN NUMBER
,p_adjust_id IN NUMBER
,p_settlement_doc_id IN NUMBER
,p_settlement_mode IN VARCHAR2
,p_settlement_amount IN NUMBER --Bug4308188
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Payment_Detail';
SELECT pay.customer_trx_id
, pay.cust_trx_type_id
, pay.trx_number
, rec.apply_date
, rec.amount_applied
, pay.status
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.cash_receipt_id = cv_cash_receipt_id
AND rec.applied_payment_schedule_id = pay.payment_schedule_id
AND pay.customer_trx_id = cv_customer_trx_id
AND rec.display = 'Y';
SELECT pay.customer_trx_id
, pay.cust_trx_type_id
, pay.trx_number
, pay.trx_date --?? verify
, pay.amount_applied
, pay.status
FROM ar_payment_schedules pay
WHERE pay.customer_trx_id = cv_customer_trx_id;
SELECT cust.customer_trx_id --"settlement_id"
, cust.cust_trx_type_id --"settlement_type_id"
, cust.trx_number --"settlement_number"
, cust.trx_date --"settlement_date"
, sum(cust_lines.extended_amount) --"settlement_amount"
, pay.status --"status_code"
FROM ra_customer_trx cust
, ra_customer_trx_lines cust_lines
, ar_payment_schedules pay
WHERE cust.customer_trx_id = cv_customer_trx_id
AND cust.customer_trx_id = pay.customer_trx_id
AND cust.customer_trx_id = cust_lines.customer_trx_id
AND pay.customer_trx_id = cust_lines.customer_trx_id -- fix for bug 4897546
AND cust.complete_flag = 'Y'
GROUP BY cust.customer_trx_id
, cust.cust_trx_type_id
, cust.trx_number
, cust.trx_date
, pay.status;
SELECT pay.customer_trx_id
, pay.payment_schedule_id
, pay.trx_number
, rec.apply_date
, rec.amount_applied
, pay.status
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.cash_receipt_id = cv_cash_receipt_id
AND rec.applied_payment_schedule_id = pay.payment_schedule_id
AND pay.payment_schedule_id = -1
AND rec.display='Y'
ORDER BY rec.receivable_application_id desc;
SELECT pay.customer_trx_id
, pay.payment_schedule_id
, pay.trx_number
, rec.apply_date
, rec.amount_applied
, pay.status
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.cash_receipt_id = cv_cash_receipt_id
AND rec.applied_payment_schedule_id = pay.payment_schedule_id
AND pay.payment_schedule_id = -3
AND rec.display='Y'
ORDER BY rec.receivable_application_id desc;
SELECT adj.adjustment_id --"settlement_id"
, adj.receivables_trx_id --"settlement_type_id"
, adj.adjustment_number --"settlement_number"
, adj.apply_date --"settlement_date"
, adj.amount --"settlement_amount"
, pay.status --"status_code"
FROM ar_adjustments adj
, ar_payment_schedules pay
WHERE adj.payment_schedule_id = pay.payment_schedule_id
AND adj.adjustment_id = cv_adjust_id;
SELECT object_version_number
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT pay.customer_trx_id
, pay.cust_trx_type_id
, pay.trx_number
, sysdate
, (oc.amount_settled * -1)
, pay.status
FROM ar_payment_schedules pay
, ozf_claims oc
WHERE pay.customer_trx_id = oc.payment_reference_id
AND oc.claim_id = cv_claim_id;
SELECT object_version_number
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = cv_settlement_doc_id;
SELECT payment_method,
amount
FROM ozf_claims
WHERE claim_id = cv_claim_id;
OZF_SETTLEMENT_DOC_PVT.Update_Settlement_Doc(
p_api_version_number => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_settlement_doc_rec => l_settlement_doc_tbl(j),
x_object_version_number => l_dummy_number
);
END Update_Payment_Detail;
| Update_Claim_Tax_Amount
|
| NOTES
|
| HISTORY
| 16-MAY-2002 mchang Create.
*=======================================================================*/
PROCEDURE Update_Claim_Tax_Amount(
p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
,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_Claim_Tax_Amount';
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
UPDATE ozf_claims_all
SET tax_amount = NVL(p_claim_rec.tax_amount,0)
, acctd_tax_amount = NVL(l_acctd_tax_amount,0)
, amount_settled = p_claim_rec.amount_settled
, acctd_amount_settled = l_acctd_amount_settled
, amount_remaining = l_amount_remaining
, acctd_amount_remaining = l_acctd_amount_remaining
WHERE claim_id = p_claim_rec.claim_id;
END Update_Claim_Tax_Amount;
| Update_Claim_Line_Status
|
| NOTES
|
| HISTORY
| 14-NOV-2002 mchang Create.
*=======================================================================*/
PROCEDURE Update_Claim_Line_Status(
p_claim_line_id 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_Claim_Line_Status';
UPDATE ozf_claim_lines_all
SET payment_status = 'PAID'
WHERE claim_line_id = p_claim_line_id;
END Update_Claim_Line_Status;
SELECT party_name
FROM hz_parties party,
hz_cust_accounts_all cust
WHERE party.party_id = cust.party_id
AND cust.cust_account_id = cv_cust_account_id;
UPDATE ozf_claims
SET status_code = 'OPEN',
user_status_id = ozf_utility_pvt.get_default_user_status( 'OZF_CLAIM_STATUS', 'OPEN'),
amount_remaining = NVL(amount_remaining,0) + amount_settled,
acctd_amount_remaining = NVL(acctd_amount_remaining,0)+acctd_amount_settled,
amount_settled = 0,
acctd_amount_settled = 0,
payment_status = null,
payment_reference_id = DECODE(payment_method,'RMA',null,payment_reference_id),
payment_reference_date = null,
payment_reference_number = DECODE(payment_method,'RMA',null,payment_reference_number),
last_updated_by = NVL(FND_GLOBAL.user_id,-1),
last_update_login = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = l_sys_date,
settled_date = null,
settled_by = null
WHERE claim_id = p_claim_id;
,p_event => 'UPDATE'
,x_need_to_create => l_need_to_create
,x_claim_history_id => l_claim_history_id
);
UPDATE ozf_settlement_docs_all stlmnt_docs
SET payment_status = (SELECT SUBSTRB(lkp.displayed_field,1,30)
FROM ap_lookup_codes lkp
where lkp.lookup_type = 'CHECK STATE'
and lkp.lookup_code IN (select status_lookup_code
FROM ap_invoice_payments pay, ap_checks chk
WHERE pay.check_id = chk.check_id
AND pay.invoice_payment_id = stlmnt_docs.settlement_id)),
last_updated_by = NVL(FND_GLOBAL.user_id,-1),
last_update_login = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = l_sys_date
WHERE claim_id = p_claim_id;
,p_last_updated_by => FND_GLOBAL.user_id
,x_jtf_note_id => l_note_id
,p_note_type => 'AMS_JUSTIFICATION'
,p_last_update_date => l_sys_date
,p_creation_date => l_sys_date);
SELECT payment_method
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT --pay.invoice_payment_id --"settlement_id"
chk.check_id --"settlement_id"
, chk.payment_method_code --"settlement_type"
, chk.check_id --"settlement_type_id"
, chk.check_number --"settlement_number"
, chk.check_date --"settlement_date"
, sum(pay.amount) --"settlement_amount"
, chk.status_lookup_code --"status_code"
, oc.payment_method -- "payment_method"
, sum(NVL(pay.discount_taken,0))
, inv.invoice_amount
FROM ap_invoice_payments_all pay
, ap_checks_all chk
, ap_invoices_all inv
, ozf_claims_all oc
WHERE pay.check_id = chk.check_id
AND inv.invoice_id = pay.invoice_id
AND oc.claim_id = cv_claim_id
AND oc.payment_reference_number = inv.invoice_num
AND oc.vendor_id = inv.vendor_id
AND oc.vendor_site_id = inv.vendor_site_id
GROUP BY chk.check_id , chk.payment_method_code , chk.check_number
, chk.check_date, chk.status_lookup_code, oc.payment_method, inv.invoice_amount
ORDER BY chk.check_id;
SELECT inv.invoice_id --"settlement_id"
, inv.invoice_type_lookup_code --"settlement_type"
, null --"settlement_type_id"
, inv.invoice_num --"settlement_number"
, inv.invoice_date --"settlement_date"
, inv.invoice_amount --"settlement_amount"
, AP_INVOICES_PKG.GET_APPROVAL_STATUS( INV.INVOICE_ID, INV.INVOICE_AMOUNT, INV.PAYMENT_STATUS_FLAG, INV.INVOICE_TYPE_LOOKUP_CODE) --"status_code"
, 'AP_DEBIT' --"payment_method"
, NULL
, inv.invoice_amount
FROM ap_invoices_all inv
, ozf_claims_all oc
WHERE oc.claim_id = cv_claim_id
AND oc.payment_reference_number = inv.invoice_num
AND oc.vendor_id = inv.vendor_id
AND oc.vendor_site_id = inv.vendor_site_id;
x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
SELECT cust.customer_trx_id --"settlement_id"
, cust.cust_trx_type_id --"settlement_type_id"
, cust.trx_number --"settlement_number"
, cust.trx_date --"settlement_date"
, pay.amount_due_original --"settlement_amount"
, pay.status --"status_code"
FROM ra_customer_trx cust,
ra_customer_trx_lines lines,
ar_payment_schedules pay
where cust.customer_trx_id = lines.customer_trx_id
and cust.customer_trx_id = pay.customer_trx_id
and cust.complete_flag = 'Y'
AND lines.interface_line_attribute2 = cv_claim_id
AND lines.interface_line_attribute1 = cv_claim_number;
select pay.customer_trx_id --"settlement_id"
, pay.cust_trx_type_id --"settlement_type_id"
, pay.trx_number --"settlement_number"
, pay.trx_date --"settlement_date"
, sum(pay.amount_due_original) --"settlement_amount"
from ar_payment_schedules pay , ( select distinct customer_trx_id
from ozf_claim_lines ln , ra_customer_trx_lines cm_line
where cm_line.line_type = 'LINE'
and cm_line.interface_line_context = 'ORDER ENTRY' --added filter for 4940650
and cm_line.interface_line_attribute6 = to_char(ln.payment_reference_id) -- order line id
and ln.claim_id = cv_claim_id) cla
where pay.customer_trx_id = cla.customer_trx_id
group by pay.customer_trx_id, pay.cust_trx_type_id,pay.trx_number, pay.trx_date;
SELECT settlement_id
, settlement_type_id
, settlement_number
, settlement_date
, settlement_amount
, claim_id
, claim_line_id
, payment_method
FROM ozf_settlement_docs
WHERE claim_id = cv_claim_id;
x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
PROCEDURE Update_Claim_From_Settlement(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_claim_id IN NUMBER,
p_object_version_number IN NUMBER,
p_status_code IN VARCHAR2,
p_payment_status IN VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_From_Settlement';
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 fu.fund_id,
fu.component_type,
fu.component_id,
SUM(lu.acctd_amount),
SUM(lu.plan_curr_amount),
SUM(lu.univ_curr_amount)
FROM ozf_claim_lines_util_all lu
,ozf_claim_lines_all l
,ozf_funds_utilized_all_b fu
WHERE l.claim_line_id = lu.claim_line_id
AND fu.utilization_id = lu.utilization_id
AND l.claim_id = cv_claim_id
GROUP BY fu.fund_id,fu.component_type,fu.component_id;
SELECT object_version_number
, paid_amt
, currency_code_tc
, exchange_rate
, exchange_rate_type
, exchange_rate_date
/* BEGIN OF BUG2740879 FIXING 01/21/2003 */
--FROM ozf_funds
FROM ozf_funds_all_b
/* END OF BUG2740879 FIXING 01/21/2003 */
WHERE fund_id = cv_fund_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT close_status_id
FROM ozf_claims_all oc,
ams_user_statuses_vl us
WHERE oc.claim_id = p_claim_id
AND oc.close_status_id = us.user_status_id
AND us.system_status_type = 'OZF_CLAIM_STATUS'
AND us.system_status_code = p_status_code;
SELECT source_object_class,
created_by
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
SELECT ln.claim_line_id
, ln.object_version_number
, ln.last_update_date
, ln.last_updated_by
, ln.creation_date
, ln.created_by
, ln.last_update_login
, ln.request_id
, ln.program_application_id
, ln.program_update_date
, ln.program_id
, ln.created_from
, ln.claim_id
, ln.line_number
, ln.split_from_claim_line_id
, ln.amount
, ln.claim_currency_amount
, ln.acctd_amount
, ln.currency_code
, ln.exchange_rate_type
, ln.exchange_rate_date
, ln.exchange_rate
, ln.set_of_books_id
, ln.valid_flag
, ln.source_object_id
, ln.source_object_class
, ln.source_object_type_id
, ln.source_object_line_id
, ln.plan_id
, ln.offer_id
, ln.utilization_id
, ln.payment_method
, ln.payment_reference_id
, ln.payment_reference_number
, ln.payment_reference_date
, ln.voucher_id
, ln.voucher_number
, ln.payment_status
, ln.approved_flag
, ln.approved_date
, ln.approved_by
, ln.settled_date
, ln.settled_by
, ln.performance_complete_flag
, ln.performance_attached_flag
, ln.item_id
, ln.item_description
, ln.quantity
, ln.quantity_uom
, ln.rate
, ln.activity_type
, ln.activity_id
, ln.related_cust_account_id
, ln.relationship_type
, ln.earnings_associated_flag
, ln.comments
, ln.tax_code
, ln.attribute_category
, ln.attribute1
, ln.attribute2
, ln.attribute3
, ln.attribute4
, ln.attribute5
, ln.attribute6
, ln.attribute7
, ln.attribute8
, ln.attribute9
, ln.attribute10
, ln.attribute11
, ln.attribute12
, ln.attribute13
, ln.attribute14
, ln.attribute15
, ln.org_id
, ln.sale_date
, ln.item_type
, ln.tax_amount
, ln.claim_curr_tax_amount
, ln.activity_line_id
, ln.offer_type
, ln.prorate_earnings_flag
, ln.earnings_end_date
, ln.dpp_cust_account_id
FROM ozf_claim_lines ln
WHERE ln.claim_id = cv_claim_id;
SAVEPOINT Update_Claim_From_Settlement;
UPDATE ozf_claims_all
SET payment_status = p_payment_status
, status_code = p_status_code
, user_status_id = l_claim_rec.user_status_id
WHERE claim_id = p_claim_id;
OZF_Utility_PVT.debug_message('Updated Fund Paid Amount = '||l_fund_rec.paid_amt);
OZF_Funds_PVT.Update_Fund(
p_api_version => 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 => x_msg_count
,x_msg_data => x_msg_data
,p_fund_rec => l_fund_rec
,p_mode => 'ADJUST'
);
, l_line_detail_tbl(l_line_counter).last_update_date
, l_line_detail_tbl(l_line_counter).last_updated_by
, l_line_detail_tbl(l_line_counter).creation_date
, l_line_detail_tbl(l_line_counter).created_by
, l_line_detail_tbl(l_line_counter).last_update_login
, l_line_detail_tbl(l_line_counter).request_id
, l_line_detail_tbl(l_line_counter).program_application_id
, l_line_detail_tbl(l_line_counter).program_update_date
, l_line_detail_tbl(l_line_counter).program_id
, l_line_detail_tbl(l_line_counter).created_from
, l_line_detail_tbl(l_line_counter).claim_id
, l_line_detail_tbl(l_line_counter).line_number
, l_line_detail_tbl(l_line_counter).split_from_claim_line_id
, l_line_detail_tbl(l_line_counter).amount
, l_line_detail_tbl(l_line_counter).claim_currency_amount
, l_line_detail_tbl(l_line_counter).acctd_amount
, l_line_detail_tbl(l_line_counter).currency_code
, l_line_detail_tbl(l_line_counter).exchange_rate_type
, l_line_detail_tbl(l_line_counter).exchange_rate_date
, l_line_detail_tbl(l_line_counter).exchange_rate
, l_line_detail_tbl(l_line_counter).set_of_books_id
, l_line_detail_tbl(l_line_counter).valid_flag
, l_line_detail_tbl(l_line_counter).source_object_id
, l_line_detail_tbl(l_line_counter).source_object_class
, l_line_detail_tbl(l_line_counter).source_object_type_id
, l_line_detail_tbl(l_line_counter).source_object_line_id
, l_line_detail_tbl(l_line_counter).plan_id
, l_line_detail_tbl(l_line_counter).offer_id
, l_line_detail_tbl(l_line_counter).utilization_id
, l_line_detail_tbl(l_line_counter).payment_method
, l_line_detail_tbl(l_line_counter).payment_reference_id
, l_line_detail_tbl(l_line_counter).payment_reference_number
, l_line_detail_tbl(l_line_counter).payment_reference_date
, l_line_detail_tbl(l_line_counter).voucher_id
, l_line_detail_tbl(l_line_counter).voucher_number
, l_line_detail_tbl(l_line_counter).payment_status
, l_line_detail_tbl(l_line_counter).approved_flag
, l_line_detail_tbl(l_line_counter).approved_date
, l_line_detail_tbl(l_line_counter).approved_by
, l_line_detail_tbl(l_line_counter).settled_date
, l_line_detail_tbl(l_line_counter).settled_by
, l_line_detail_tbl(l_line_counter).performance_complete_flag
, l_line_detail_tbl(l_line_counter).performance_attached_flag
, l_line_detail_tbl(l_line_counter).item_id
, l_line_detail_tbl(l_line_counter).item_description
, l_line_detail_tbl(l_line_counter).quantity
, l_line_detail_tbl(l_line_counter).quantity_uom
, l_line_detail_tbl(l_line_counter).rate
, l_line_detail_tbl(l_line_counter).activity_type
, l_line_detail_tbl(l_line_counter).activity_id
, l_line_detail_tbl(l_line_counter).related_cust_account_id
, l_line_detail_tbl(l_line_counter).relationship_type
, l_line_detail_tbl(l_line_counter).earnings_associated_flag
, l_line_detail_tbl(l_line_counter).comments
, l_line_detail_tbl(l_line_counter).tax_code
, l_line_detail_tbl(l_line_counter).attribute_category
, l_line_detail_tbl(l_line_counter).attribute1
, l_line_detail_tbl(l_line_counter).attribute2
, l_line_detail_tbl(l_line_counter).attribute3
, l_line_detail_tbl(l_line_counter).attribute4
, l_line_detail_tbl(l_line_counter).attribute5
, l_line_detail_tbl(l_line_counter).attribute6
, l_line_detail_tbl(l_line_counter).attribute7
, l_line_detail_tbl(l_line_counter).attribute8
, l_line_detail_tbl(l_line_counter).attribute9
, l_line_detail_tbl(l_line_counter).attribute10
, l_line_detail_tbl(l_line_counter).attribute11
, l_line_detail_tbl(l_line_counter).attribute12
, l_line_detail_tbl(l_line_counter).attribute13
, l_line_detail_tbl(l_line_counter).attribute14
, l_line_detail_tbl(l_line_counter).attribute15
, l_line_detail_tbl(l_line_counter).org_id
, l_line_detail_tbl(l_line_counter).sale_date
, l_line_detail_tbl(l_line_counter).item_type
, l_line_detail_tbl(l_line_counter).tax_amount
, l_line_detail_tbl(l_line_counter).claim_curr_tax_amount
, l_line_detail_tbl(l_line_counter).activity_line_id
, l_line_detail_tbl(l_line_counter).offer_type
, l_line_detail_tbl(l_line_counter).prorate_earnings_flag
, l_line_detail_tbl(l_line_counter).earnings_end_date
, l_line_detail_tbl(l_line_counter).dpp_cust_account_id;
ROLLBACK TO Update_Claim_From_Settlement;
ROLLBACK TO Update_Claim_From_Settlement;
ROLLBACK TO Update_Claim_From_Settlement;
END Update_Claim_From_Settlement;
Update_Claim_Tax_Amount(
p_claim_rec => l_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
SELECT adj.adjustment_id --"settlement_id"
, adj.receivables_trx_id --"settlement_type_id"
, adj.adjustment_number --"settlement_number"
, adj.apply_date --"settlement_date"
, adj.amount --"settlement_amount"
, pay.status --"status_code"
FROM ar_adjustments adj
, ar_payment_schedules pay
WHERE adj.payment_schedule_id = pay.payment_schedule_id
AND adj.adjustment_id = cv_adjust_id;
l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
| 2. Update Invoice Dispute Amount if source deduction
*----------------------------*/
IF OZF_DEBUG_HIGH_ON THEN
OZF_Utility_PVT.debug_message(l_full_name||' : Create AR Write Off');
| Update Settled Amount|
*----------------------*/
l_claim_rec.amount_settled := l_claim_rec.amount_settled + p_difference_amount;
Update_Claim_Tax_Amount(
p_claim_rec => l_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
select sum(nvl(amount_line_items_original, 0))
, sum(nvl(tax_original,0))
, sum(nvl(freight_original,0))
, previous_customer_trx_id
from ar_payment_schedules ps, ra_customer_trx trx , ( select distinct customer_trx_id
from ozf_claim_lines ln , ra_customer_trx_lines cm_line
where cm_line.line_type = 'LINE'
and cm_line.interface_line_context = 'ORDER ENTRY' --added filter for 4953844
and cm_line.interface_line_attribute6 = to_char(ln.payment_reference_id)
and ln.claim_id = cv_claim_id) cla
where ps.customer_trx_id = trx.customer_trx_id
and ps.customer_trx_id = cla.customer_trx_id
group by previous_customer_trx_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
Update_Claim_Tax_Amount(
p_claim_rec => l_upd_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
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_setl_rec.claim_id,
p_object_version_number => p_claim_setl_rec.object_version_number,
p_status_code => 'CLOSED',
p_payment_status => 'PAID'
);
FND_DSQL.add_text('SELECT claim_id, claim_number, object_version_number, claim_class, amount_remaining, amount_settled, source_object_id, payment_method ');
SELECT COUNT(claim_line_id)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT COUNT(claim_line_id)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id
AND payment_status = 'PAID';
SELECT claim_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id
FOR UPDATE NOWAIT;
SELECT flow_status_code
FROM oe_order_headers_all,
ozf_claims_all
WHERE claim_id = cv_claim_id
AND payment_reference_id = header_id;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
/*Update_Claim_Line_Status(
p_claim_line_id => l_settlement_doc_tbl(j).claim_line_id
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
UPDATE ozf_claims_all
SET payment_status = 'INTERFACED'
WHERE claim_id = l_claim_rma_fetch(i).claim_id;
UPDATE ozf_claims_all
SET payment_status = 'INTERFACED'
WHERE claim_id = l_claim_rma_fetch(i).claim_id;
UPDATE ozf_claim_lines_all
SET payment_status = 'PAID'
WHERE claim_id = l_claim_rma_fetch(i).claim_id;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
SELECT NVL(SUM(amount_line_items_original), 0)
, NVL(SUM(tax_original), 0)
, NVL(SUM(freight_original), 0)
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT *
FROM ozf_claims_all
WHERE claim_id = cv_claim_id
FOR UPDATE NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
Update_Claim_Tax_Amount(
p_claim_rec => l_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
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 => l_claim_id_ver(i).claim_id,
p_object_version_number => l_claim_id_ver(i).object_version_number,
p_status_code => 'CLOSED',
p_payment_status => 'PAID'
);
SELECT settlement_doc_id
, object_version_number
, settlement_amount
, status_code
FROM ozf_settlement_docs
WHERE settlement_id = cv_settlement_id;
SELECT invoice_id,
cancelled_date
FROM ap_invoices_all ap,
ozf_claims_all oc
WHERE claim_id = cv_claim_id
AND invoice_num = oc.payment_reference_number
AND ap.vendor_id = oc.vendor_id
AND ap.vendor_site_id = oc.vendor_site_id;
SELECT invoice_amount
FROM ap_invoices
WHERE invoice_id = cv_invoice_id;
SELECT sum(pay.amount + NVL(pay.discount_taken,0))
FROM ap_invoice_payments_all pay
, ap_invoices_all inv
WHERE inv.invoice_id = pay.invoice_id
AND inv.invoice_id = cv_invoice_id;
DELETE FROM ozf_settlement_docs_all
WHERE claim_id = l_claim_id_ver(i).claim_id;
Update_Settlement_Doc(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.g_false,
p_commit => p_commit,
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_settlement_doc_rec => l_settlement_doc_tbl(j),
x_object_version_number => l_settlement_doc_tbl(j).object_version_number
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update settlement doc :: settlement_doc_id='||l_settlement_doc_tbl(j).settlement_id);
Update_Claim_Tax_Amount(
p_claim_rec => l_claim_rec
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
Update_Claim_From_Settlement(
p_api_version_number => l_api_version_number,
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 => l_claim_rec.claim_id,
p_object_version_number => l_claim_rec.object_version_number,
p_status_code => 'CLOSED',
p_payment_status => 'PAID'
);
SELECT MEANING
FROM OZF_LOOKUPS
WHERE lookup_type= 'OZF_CLAIM_CLASS'
AND lookup_code = p_lkup_code;
SELECT MEANING
FROM OZF_LOOKUPS
WHERE lookup_type= 'OZF_PAYMENT_METHOD'
AND lookup_code = p_lkup_code;
SELECT SUBSTRB(PARTY.PARTY_NAME,1,50) NAME
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES PARTY
WHERE CA.party_id = party.party_id
AND CA.CUST_ACCOUNT_ID = p_cust_act_id;
SELECT NAME
FROM OZF_CLAIM_TYPES_VL
WHERE CLAIM_TYPE_ID = p_claim_type_id;
SELECT NAME
FROM OZF_REASON_CODES_VL
WHERE REASON_CODE_ID = p_reason_code_id;
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
l_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT ozf_settlement_docs_all_s.NEXTVAL
FROM DUAL;
SELECT COUNT(settlement_doc_id)
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = cv_id;
SELECT set_of_books_id,
currency_code,
exchange_rate_date,
exchange_rate_type,
exchange_rate,
org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
l_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
OZF_SETTLEMENT_DOCS_PKG.Insert_Row(
px_settlement_doc_id => l_settlement_doc_rec.settlement_doc_id,
px_object_version_number => l_settlement_doc_rec.object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
p_program_update_date => SYSDATE,
p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
p_created_from => l_settlement_doc_rec.created_from,
p_claim_id => l_settlement_doc_rec.claim_id,
p_claim_line_id => l_settlement_doc_rec.claim_line_id,
p_payment_method => l_settlement_doc_rec.payment_method,
p_settlement_id => l_settlement_doc_rec.settlement_id,
p_settlement_type => l_settlement_doc_rec.settlement_type,
p_settlement_type_id => l_settlement_doc_rec.settlement_type_id,
p_settlement_number => l_settlement_doc_rec.settlement_number,
p_settlement_date => l_settlement_doc_rec.settlement_date,
p_settlement_amount => l_settlement_doc_rec.settlement_amount,
p_settlement_acctd_amount => l_settlement_doc_rec.settlement_acctd_amount,
p_status_code => l_settlement_doc_rec.status_code,
p_attribute_category => l_settlement_doc_rec.attribute_category,
p_attribute1 => l_settlement_doc_rec.attribute1,
p_attribute2 => l_settlement_doc_rec.attribute2,
p_attribute3 => l_settlement_doc_rec.attribute3,
p_attribute4 => l_settlement_doc_rec.attribute4,
p_attribute5 => l_settlement_doc_rec.attribute5,
p_attribute6 => l_settlement_doc_rec.attribute6,
p_attribute7 => l_settlement_doc_rec.attribute7,
p_attribute8 => l_settlement_doc_rec.attribute8,
p_attribute9 => l_settlement_doc_rec.attribute9,
p_attribute10 => l_settlement_doc_rec.attribute10,
p_attribute11 => l_settlement_doc_rec.attribute11,
p_attribute12 => l_settlement_doc_rec.attribute12,
p_attribute13 => l_settlement_doc_rec.attribute13,
p_attribute14 => l_settlement_doc_rec.attribute14,
p_attribute15 => l_settlement_doc_rec.attribute15,
px_org_id => l_claim_rec.org_id,
p_payment_reference_id => l_settlement_doc_rec.payment_reference_id,
p_payment_reference_number => l_settlement_doc_rec.payment_reference_number,
p_payment_status => l_settlement_doc_rec.payment_status,
p_group_claim_id => l_settlement_doc_rec.group_claim_id,
p_gl_date => TRUNC(l_settlement_doc_rec.gl_date),
p_wo_rec_trx_id => l_settlement_doc_rec.wo_rec_trx_id
);
PROCEDURE Update_Settlement_Doc(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_settlement_doc_rec IN settlement_doc_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Settlement_Doc';
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT object_version_number
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = cv_settle_doc_id;
SELECT set_of_books_id,
currency_code,
exchange_rate_date,
exchange_rate_type,
exchange_rate,
org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SAVEPOINT Update_Settlement_Doc;
l_last_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
OZF_SETTLEMENT_DOCS_PKG.Update_Row(
p_settlement_doc_id => l_complete_doc_rec.settlement_doc_id,
p_object_version_number => l_object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => l_complete_doc_rec.request_id,
p_program_application_id => l_complete_doc_rec.program_application_id,
p_program_update_date => l_complete_doc_rec.program_update_date,
p_program_id => l_complete_doc_rec.program_id,
p_created_from => l_complete_doc_rec.created_from,
p_claim_id => l_complete_doc_rec.claim_id,
p_claim_line_id => l_complete_doc_rec.claim_line_id,
p_payment_method => l_complete_doc_rec.payment_method,
p_settlement_id => l_complete_doc_rec.settlement_id,
p_settlement_type => l_complete_doc_rec.settlement_type,
p_settlement_type_id => l_complete_doc_rec.settlement_type_id,
p_settlement_number => l_complete_doc_rec.settlement_number,
p_settlement_date => l_complete_doc_rec.settlement_date,
p_settlement_amount => l_complete_doc_rec.settlement_amount,
p_settlement_acctd_amount=> l_complete_doc_rec.settlement_acctd_amount,
p_status_code => l_complete_doc_rec.status_code,
p_attribute_category => l_complete_doc_rec.attribute_category,
p_attribute1 => l_complete_doc_rec.attribute1,
p_attribute2 => l_complete_doc_rec.attribute2,
p_attribute3 => l_complete_doc_rec.attribute3,
p_attribute4 => l_complete_doc_rec.attribute4,
p_attribute5 => l_complete_doc_rec.attribute5,
p_attribute6 => l_complete_doc_rec.attribute6,
p_attribute7 => l_complete_doc_rec.attribute7,
p_attribute8 => l_complete_doc_rec.attribute8,
p_attribute9 => l_complete_doc_rec.attribute9,
p_attribute10 => l_complete_doc_rec.attribute10,
p_attribute11 => l_complete_doc_rec.attribute11,
p_attribute12 => l_complete_doc_rec.attribute12,
p_attribute13 => l_complete_doc_rec.attribute13,
p_attribute14 => l_complete_doc_rec.attribute14,
p_attribute15 => l_complete_doc_rec.attribute15,
p_org_id => l_claim_rec.org_id,
p_payment_reference_id => l_complete_doc_rec.payment_reference_id,
p_payment_reference_number => l_complete_doc_rec.payment_reference_number,
p_payment_status => l_complete_doc_rec.payment_status,
p_group_claim_id => l_complete_doc_rec.group_claim_id,
p_gl_date => TRUNC(l_complete_doc_rec.gl_date),
p_wo_rec_trx_id => l_complete_doc_rec.wo_rec_trx_id
);
UPDATE ozf_settlement_docs_all SET
object_version_number = l_object_version_number,
last_update_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
created_from = l_complete_doc_rec.created_from,
claim_id = l_complete_doc_rec.claim_id,
claim_line_id = l_complete_doc_rec.claim_line_id,
payment_method = l_complete_doc_rec.payment_method,
settlement_id = l_complete_doc_rec.settlement_id,
settlement_type = l_complete_doc_rec.settlement_type,
settlement_type_id = l_complete_doc_rec.settlement_type_id,
settlement_number = l_complete_doc_rec.settlement_number,
settlement_date = l_complete_doc_rec.settlement_date,
settlement_amount = l_complete_doc_rec.settlement_amount,
status_code = l_complete_doc_rec.status_code,
attribute_category = l_complete_doc_rec.attribute_category,
attribute1 = l_complete_doc_rec.attribute1,
attribute2 = l_complete_doc_rec.attribute2,
attribute3 = l_complete_doc_rec.attribute3,
attribute4 = l_complete_doc_rec.attribute4,
attribute5 = l_complete_doc_rec.attribute5,
attribute6 = l_complete_doc_rec.attribute6,
attribute7 = l_complete_doc_rec.attribute7,
attribute8 = l_complete_doc_rec.attribute8,
attribute9 = l_complete_doc_rec.attribute9,
attribute10 = l_complete_doc_rec.attribute10,
attribute11 = l_complete_doc_rec.attribute11,
attribute12 = l_complete_doc_rec.attribute12,
attribute13 = l_complete_doc_rec.attribute13,
attribute14 = l_complete_doc_rec.attribute14,
attribute15 = l_complete_doc_rec.attribute15,
org_id = l_complete_doc_rec.org_id
WHERE settlement_doc_id = p_settlement_doc_rec.settlement_doc_id
AND object_version_number = p_settlement_doc_rec.object_version_number;
ROLLBACK TO Update_Settlement_Doc;
ROLLBACK TO Update_Settlement_Doc;
ROLLBACK TO Update_Settlement_Doc;
End Update_Settlement_Doc;
PROCEDURE Delete_Settlement_Doc(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_settlement_doc_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Settlement_Doc';
SELECT object_version_number
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = cv_settle_doc_id;
SAVEPOINT Delete_Settlement_Doc;
OZF_Utility_PVT.debug_message(l_full_name ||': delete');
OZF_SETTLEMENT_DOCS_PKG.Delete_Row(
p_settlement_doc_id => p_settlement_doc_id
);
DELETE FROM ozf_settlement_docs_all
WHERE settlement_doc_id = p_settlement_doc_id
AND object_version_number = p_object_version_number;
ROLLBACK TO Delete_Settlement_Doc;
ROLLBACK TO Delete_Settlement_Doc;
ROLLBACK TO Delete_Settlement_Doc;
End Delete_Settlement_Doc;
SELECT settlement_doc_id
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = p_settlement_doc_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
IF p_settlement_doc_rec.last_update_date = FND_API.g_miss_date OR
p_settlement_doc_rec.last_update_date IS NULL THEN
x_return_status := FND_API.g_ret_sts_error;
IF p_settlement_doc_rec.last_updated_by = FND_API.g_miss_num OR
p_settlement_doc_rec.last_updated_by IS NULL THEN
x_return_status := FND_API.g_ret_sts_error;
IF p_settlement_doc_rec.last_update_date IS NULL THEN
x_return_status := FND_API.g_ret_sts_error;
IF p_settlement_doc_rec.last_updated_by IS NULL THEN
x_return_status := FND_API.g_ret_sts_error;
SELECT *
FROM ozf_settlement_docs_all
WHERE settlement_doc_id = p_settlement_doc_rec.settlement_doc_id;
IF p_settlement_doc_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := NULL;
IF p_settlement_doc_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_settlement_doc_rec.last_update_date;
IF p_settlement_doc_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := NULL;
IF p_settlement_doc_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_settlement_doc_rec.last_updated_by;
IF p_settlement_doc_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := NULL;
IF p_settlement_doc_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_settlement_doc_rec.last_update_login;
IF p_settlement_doc_rec.program_update_date = FND_API.g_miss_date THEN
x_complete_rec.program_update_date := NULL;
IF p_settlement_doc_rec.program_update_date IS NULL THEN
x_complete_rec.program_update_date := l_settlement_doc_rec.program_update_date;
PROCEDURE Update_Settlement_Doc_Tbl(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_settlement_doc_tbl IN settlement_doc_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Settlement_Doc_Tbl';
SAVEPOINT Update_Settlement_Doc_Tbl;
OZF_SETTLEMENT_DOC_PVT.Update_Settlement_Doc(
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 => l_msg_count,
x_msg_data => l_msg_data,
p_settlement_doc_rec => l_settlement_doc,
x_object_version_number => l_object_version_number
);
ROLLBACK TO Update_Settlement_Doc_Tbl;
ROLLBACK TO Update_Settlement_Doc_Tbl;
ROLLBACK TO Update_Settlement_Doc_Tbl;
END Update_Settlement_Doc_Tbl;