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;
SELECT claim_class
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cv_settlement_type_id;
SELECT trx_number
FROM ar_payment_schedules
WHERE payment_schedule_id = cv_settlement_type_id;
SELECT name
FROM ar_receivables_trx
WHERE receivables_trx_id = cv_settlement_type_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
AND lookup_code = cv_payment_method;
| 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;
SELECT claim_class
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cv_settlement_type_id;
SELECT trx_number
FROM ar_payment_schedules
WHERE payment_schedule_id = cv_settlement_type_id;
SELECT name
FROM ar_receivables_trx
WHERE receivables_trx_id = cv_settlement_type_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
AND lookup_code = cv_payment_method;
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,payment_reference_number
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 = TO_NUMBER(inv.reference_key1) --//Fix for bug 13557215
AND oc.claim_id = cv_claim_id
--AND oc.payment_reference_number = inv.invoice_num --//Fix for bug 13976621
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 --//Fix for Bug 13976621
AND oc.claim_id = TO_NUMBER(inv.reference_key1) --//Fix for bug 13557215
AND oc.vendor_id = inv.vendor_id
AND oc.vendor_site_id = inv.vendor_site_id;
SELECT inv.invoice_num
FROM ozf_claims_all oc
,ap_invoices_all inv
WHERE oc.claim_id = TO_NUMBER(inv.reference_key1)
--AND oc.payment_reference_number = inv.invoice_num --//Fix for Bug 13976621
AND oc.claim_id = cv_claim_id;
UPDATE ozf_claims_all
SET payment_reference_number = l_invoice_number
WHERE claim_id = p_claim_id;
x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
SELECT DISTINCT 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 claim_class, payment_method
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cv_settlement_type_id;
SELECT trx_number
FROM ar_payment_schedules
WHERE payment_schedule_id = cv_settlement_type_id;
SELECT name
FROM ar_receivables_trx
WHERE receivables_trx_id = cv_settlement_type_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
AND lookup_code = cv_payment_method;
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';
l_claim_update_data CLOB;
l_item_key_update VARCHAR2(50);
l_event_name_update VARCHAR2(80);
l_parameter_list_update wf_parameter_list_t;
SELECT payment_status
FROM ozf_claims_all
where claim_id= p_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 fu.fund_id,
fu.component_type,
fu.component_id,
SUM(lu.acctd_amount),
SUM(lu.plan_curr_amount),
SUM(lu.univ_curr_amount),
SUM(lu.util_curr_amount) -- 8710054
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
/* 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;
SELECT status_code
,amount
,cust_account_id
,claim_class
,payment_method
,split_from_claim_id
FROM ozf_claims_all
WHERE 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
, last_update_date = SYSDATE -- hbandi added this line of code for resolving the Bug # 9793391 .
, last_updated_by = NVL(FND_GLOBAL.user_id,-1) -- hbandi added this line of code for resolving the Bug # 9793391 .
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 => 'SETTLE' --Bug 13529250, Change p_mode to 'SETTLE' from 'ADJUST' to avoid budget paid amount validation
);
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_item_key_update := l_claim_rec.claim_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
l_parameter_list_update := WF_PARAMETER_LIST_T();
l_claim_update_data := DBMS_XMLGEN.getXml('select claim.claim_id ClaimID,
claim.status_code ClaimStatus,
(claim.cust_account_id || '':'' || cust.party_id)
ClaimCustAcctID,
claim.receipt_id ClaimRecID,
claim.receipt_number ClaimRecNum,
claim.gl_date ClaimGLDate,
claim.attribute_category ClaimAttCat,
claim.attribute1 ClaimAttr1,
claim.attribute2 ClaimAttr2,
claim.attribute3 ClaimAttr3,
claim.attribute4 ClaimAttr4,
claim.attribute5 ClaimAttr5,
claim.attribute6 ClaimAttr6,
claim.attribute7 ClaimAttr7,
claim.attribute8 ClaimAttr8,
claim.attribute9 ClaimAttr9,
claim.attribute10 ClaimAttr10,
claim.attribute11 ClaimAttr11,
claim.attribute12 ClaimAttr12,
claim.attribute13 ClaimAttr13,
claim.attribute14 ClaimAttr14,
claim.attribute15 ClaimAttr15,
claim.deduction_attribute_category CalimDedCat,
claim.deduction_attribute1 ClaimDedAttr1,
claim.deduction_attribute2 ClaimDedAttr2,
claim.deduction_attribute1 ClaimDedAttr3,
claim.deduction_attribute1 ClaimDedAttr4,
claim.deduction_attribute1 ClaimDedAttr5,
claim.deduction_attribute1 ClaimDedAttr6,
claim.deduction_attribute1 ClaimDedAttr7,
claim.deduction_attribute1 ClaimDedAttr8,
claim.deduction_attribute1 ClaimDedAttr9,
claim.deduction_attribute1 ClaimDedAttr10,
claim.deduction_attribute1 ClaimDedAttr11,
claim.deduction_attribute1 ClaimDedAttr12,
claim.deduction_attribute1 ClaimDedAttr13,
claim.deduction_attribute1 ClaimDedAttr14,
claim.deduction_attribute1 ClaimDedAttr15,
claim.org_id
from ozf_claims_all claim, hz_cust_accounts cust
Where
claim.cust_account_id = cust.cust_account_id AND
claim.claim_id = ' || l_claim_rec.claim_id);
l_event_name_update := 'oracle.apps.ozf.bpel.claim';
wf_event.raise(p_event_name => l_event_name_update,
p_event_key => l_item_key_update,
p_event_data => l_claim_update_data,
p_parameters => l_parameter_list_update,
p_send_date => sysdate);
, 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
AND claim_id = cv_claim_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 --//Fix for bug 13976621
AND TO_NUMBER(ap.reference_key1) = oc.claim_id --//Fix for bug 13557215
AND ap.vendor_id = oc.vendor_id
AND ap.vendor_site_id = oc.vendor_site_id
ORDER BY ap.creation_date desc; --//Fix for bug 13976621
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;
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'INVOICE TYPE';
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'PAYMENT METHOD';
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;
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'INVOICE TYPE';
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'PAYMENT METHOD';
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;