[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(amount_due_remaining), trx_number
FROM ar_payment_schedules pay
WHERE customer_trx_id = cv_trx_id
GROUP BY trx_number;
SELECT NVL(COUNT(trade_profile_id), 0)
FROM ozf_cust_trd_prfls cust, hz_cust_accounts hz
WHERE cust.party_id = hz.party_id
AND hz.cust_account_id = p_id;
SELECT autopay_flag
, autopay_periodicity
, autopay_periodicity_type
, days_due
FROM ozf_sys_parameters;
SELECT payment_method
, cust_account_id
, cust_billto_acct_site_id
, vendor_id
, vendor_site_id
FROM ozf_claims
WHERE claim_id = p_id;
SELECT claim_line_id
, object_version_number
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
OZF_CLAIM_LINE_PVT.Update_Claim_Line(
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_count => x_msg_count --ninarasi fix for bug 14333514
,x_msg_data => x_msg_data
,p_claim_line_rec => l_claim_line_rec
,x_object_version => l_line_obj_ver
);
SELECT claim_class
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT claim_class
FROM ozf_claims
WHERE claim_id = cv_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 SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE custom_setup_id = cv_custom_setup_id
AND object_attribute = 'PAPR';
SELECT object_version_number
--, user_status_id
, settled_by -- Fix for Bug 4754509
, comments
FROM ozf_claims
WHERE claim_id = l_claim_id;
SELECT ap_payment_term_id
FROM ozf_sys_parameters
WHERE set_of_books_id = cv_books_id;
SELECT gl_date_type
FROM ozf_sys_parameters
WHERE set_of_books_id = cv_set_of_books_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 SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT SUM(amount_due_remaining),
invoice_currency_code
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id
GROUP BY invoice_currency_code;
| Update claim settled/remaining amount
| and settled by/date information
|
| mchang fix @11.5.9: the following logic needs to confirm for the status flow
| PENDING_APPROVAL
| --(approval workflow)--> CLOSED
| -> PENDING_APPROVAL
| --(adjust over util workflow)--> CLOSED
*---------------------------------------------------------*/
IF p_approval_require = 'N' AND
p_prev_status <> 'APPROVED' THEN
-- claim settled/remaining amount is been updated already
-- for status order PENDING_APPROVAL -> (CLOSED) -> APPROVED
-- update amount_settled
-- R12.1 Enhancement: Checking for ACCOUNTING_ONLY payment method
IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO','AP_DEBIT','CHECK','EFT','WIRE','AP_DEFAULT','RMA','ACCOUNTING_ONLY') THEN
OPEN csr_line_sum_amt(p_claim_rec.claim_id);
| 7. Update payment_status and claim status_code by settlement method.
*---------------------------------------------------------*/
x_claim_rec.status_code := 'PENDING_CLOSE';
| 8. Update payment_status to PENDING
*---------------------------------------------------------*/
x_claim_rec.payment_status := 'PENDING';
SELECT ap_payment_term_id
FROM ozf_sys_parameters
WHERE set_of_books_id = cv_books_id;
SELECT claim_number
, object_version_number
, settled_date
, effective_date
, vendor_id
, vendor_site_id
, amount_settled
, currency_code
, exchange_rate
, exchange_rate_type
, exchange_rate_date
, payment_method
, set_of_books_id
, gl_date
, claim_class
, payment_reference_id
, source_object_class
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT cust_trx_type_id
, trx_number
, status
FROM ar_payment_schedules
WHERE customer_trx_id = cv_customer_trx_id;
SELECT NVL(SUM(lu.amount), 0)
FROM ozf_claim_lines_util lu
WHERE lu.claim_line_id IN (
SELECT l.claim_line_id
FROM ozf_claim_lines l
WHERE l.claim_id = p_id);
SELECT osp.post_to_gl
FROM ozf_sys_parameters_all osp
, ozf_claims_all oc
WHERE osp.org_id = oc.org_id
AND oc.claim_id = p_id;
PROCEDURE Update_Claim_Remaining_Amount(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Remaining_Amount';
SELECT ABS(pay.tax_original)
FROM ar_payment_schedules pay
, ozf_settlement_docs sd
WHERE pay.customer_trx_id = sd.settlement_id
AND sd.claim_id = cv_claim_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
END Update_Claim_Remaining_Amount;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CLAIM_STATUS'
AND lookup_code = c_status_code;
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE custom_setup_id = cv_custom_setup_id
AND object_attribute = 'APPR';
SELECT custom_setup_id
, payment_method
, write_off_flag
, under_write_off_threshold
, status_code
FROM ozf_claims
WHERE claim_id = cv_claim_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 = p_claim_org_id;
SELECT lu.claim_line_util_id
, lu.amount
, lu.currency_code
, lu.exchange_rate_type
, lu.exchange_rate
, fu.plan_currency_code
, fu.exchange_rate_type
, fu.exchange_rate
, fu.exchange_rate_date
FROM ozf_claim_lines_all ln
, ozf_claim_lines_util_all lu
, ozf_funds_utilized_all_b fu
WHERE ln.claim_id = p_claim_id
AND ln.claim_line_id = lu.claim_line_id
AND lu.utilization_id = fu.utilization_id;
UPDATE ozf_claim_lines_util_all
SET exchange_rate_date = l_claim_settled_date
WHERE claim_line_util_id = l_line_util_id;
UPDATE ozf_claim_lines_util_all
SET acctd_amount = l_lu_acctd_amount
, exchange_rate_date = l_claim_settled_date
, exchange_rate = l_claim_exc_rate
-- , utilized_acctd_amount = l_util_acctd_amount
, fxgl_acctd_amount = l_fxgl_amount
WHERE claim_line_util_id = l_line_util_id;
SELECT status_code
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT NVL(approval_matched_credit, 'F'),NVL(approval_new_credit,'F')
FROM ozf_sys_parameters os, ozf_claims_all oc
WHERE oc.org_id = os.org_id
and oc.claim_id = p_claim_id;
Update_Claim_Remaining_Amount (
x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_claim_rec => l_claim_rec
,x_claim_rec => x_claim_rec
);
SELECT custom_setup_id
, payment_method
, write_off_flag
, under_write_off_threshold
, status_code
, settled_from
, payment_reference_number
, payment_reference_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT NVL(approval_matched_credit, 'F'),NVL(approval_new_credit,'F')
FROM ozf_sys_parameters os, ozf_claims_all oc
WHERE oc.org_id = os.org_id
and oc.claim_id = p_claim_id;
SELECT org_id
FROM ozf_claims_all
WHERE claim_id = p_claim_id;