[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM ozf_claims_all
WHERE cstl_wf_item_key = p_item_key;
select ppl.person_id employee_id
from jtf_rs_resource_extns rsc , per_people_f ppl
where rsc.category = 'EMPLOYEE' and ppl.person_id = rsc.source_id
and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
and rsc.resource_id = p_user_id;
SELECT object_version_number
, status_code
, org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
OZF_CLAIM_PVT.Update_Claim (
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_data => l_msg_data
,x_msg_count => l_msg_count
,p_claim => l_claim_rec
,p_event => 'UPDATE'
,p_mode => 'AUTO'
,x_object_version_number => l_object_version
);
SELECT root_claim_id
, payment_method
, vendor_id
, vendor_site_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT COUNT(claim_id)
FROM ozf_claims
WHERE root_claim_id = cv_claim_id ;
SELECT c.claim_number,
lk.meaning,
c.amount,
c.amount_remaining,
c.amount_settled
FROM ozf_claims c
, ozf_lookups lk
WHERE root_claim_id = cv_claim_id
AND c.status_code = lk.lookup_code
AND lk.lookup_type = 'OZF_CLAIM_STATUS'
ORDER BY claim_number;
SELECT ln.claim_line_id
, ln.line_number
, ln.item_id
, ln.quantity_uom
, ln.quantity
, ln.rate
, ln.claim_currency_amount
, ln.tax_code
, ln.earnings_associated_flag
, ln.org_id
FROM ozf_claim_lines ln
WHERE ln.claim_id = cv_claim_id;
SELECT padded_concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = cv_gl_code_id;
SELECT lk.meaning
, trx.trx_number
FROM ozf_claim_lines ln
, ra_customer_trx trx
, ozf_lookups lk
WHERE ln.claim_line_id = cv_claim_line_id
AND ln.source_object_class = lk.lookup_code
AND lk.lookup_type = 'OZF_OBJECT_CLASS'
AND ln.source_object_id = trx.customer_trx_id;
SELECT concatenated_segments
FROM mtl_system_items_vl
WHERE inventory_item_id = cv_item_id
AND organization_id = cv_org_id;
SELECT unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = cv_uom_code;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id = cv_vendor_id;
SELECT vendor_site_code
FROM po_vendor_sites
WHERE vendor_site_id = cv_vendor_site_id;
PROCEDURE Update_Docs(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
SELECT payment_method
, org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT distinct customer_trx_id
FROM ar_payment_schedules
WHERE trx_number = cv_trx_number
AND class = 'CM';
SELECT distinct customer_trx_id
FROM ar_payment_schedules
WHERE trx_number = cv_trx_number
AND class in ( 'DM', 'CB', 'INV');
SELECT distinct customer_trx_id
FROM ar_payment_schedules
WHERE trx_number = cv_trx_number;
SELECT adjustment_id
FROM ar_adjustments
WHERE adjustment_number = cv_trx_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"
, pay.status
from ar_payment_schedules pay
where pay.customer_trx_id = cv_trx_id
group by pay.customer_trx_id, pay.cust_trx_type_id,pay.trx_number,
pay.trx_date , pay.status;
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_trx_id;
SELECT rec.receivable_application_id --"settlement_id"
, pay.payment_schedule_id --"settlement_type_id"
, NULL --"settlement_number"
, rec.apply_date --"settlement_date"
, rec.amount_applied --"settlement_amount"
, pay.status --"status_code"
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.cash_receipt_id = cv_cash_receipt_id
AND rec.applied_payment_schedule_id = -3
AND rec.applied_payment_schedule_id = pay.payment_schedule_id;
SELECT settlement_doc_id
, object_version_number
FROM ozf_settlement_docs
WHERE claim_id = cv_claim_id;
SAVEPOINT Update_Docs;
'Update_Docs'||'-'||'Update_Settlement_Docs',
itemtype,
itemkey,
to_char(actid),
funcmode
);
OZF_Settlement_Doc_PVT.Delete_Settlement_Doc(
p_api_version_number => 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 => l_msg_count,
x_msg_data => l_msg_data,
p_settlement_doc_id => l_settlement_doc_rec.settlement_doc_id,
p_object_version_number => l_settlement_doc_rec.object_version_number
);
'Update_Docs'||'-'||'Update_Claim',
itemtype,
itemkey,
to_char(actid),
funcmode
);
ROLLBACK TO Update_Docs;
'Update_Docs',
itemtype,
itemkey,
to_char(actid),
funcmode
);
END Update_Docs;
SELECT org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT settlement_doc_id
, object_version_number
FROM ozf_settlement_docs
WHERE claim_id = cv_claim_id;
OZF_Settlement_Doc_PVT.Delete_Settlement_Doc(
p_api_version_number => 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 => l_msg_count,
x_msg_data => l_msg_data,
p_settlement_doc_id => l_settlement_doc_rec.settlement_doc_id,
p_object_version_number => l_settlement_doc_rec.object_version_number
);
'Create_Settle_Doc'||'-'||'Delete_Settlement_Doc',
itemtype,
itemkey,
to_char(actid),
funcmode
);
SELECT object_version_number
, org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_CLAIM_STATUS'
AND system_status_code = cv_status_code;
OZF_Settlement_Doc_PVT.Update_Claim_From_Settlement(
p_api_version_number => 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 => l_msg_count
,x_msg_data => l_msg_data
,p_claim_id => l_claim_rec.claim_id
,p_object_version_number => l_claim_rec.object_version_number
,p_status_code => l_status_code
,p_payment_status => 'PAID'
);
OZF_AR_PAYMENT_PVT.update_dispute_amount(
p_claim_rec => l_claim_rec
,p_dispute_amount => l_settle_amount
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
SELECT object_version_number
, org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_CLAIM_STATUS'
AND system_status_code = cv_status_code;
OZF_CLAIM_PVT.Update_Claim (
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_data => l_msg_data
,x_msg_count => l_msg_count
,p_claim => l_claim_rec
,p_event => 'UPDATE'
,p_mode => 'AUTO'
,x_object_version_number => l_object_version
);
SELECT object_version_number
, claim_number
, claim_class
, amount_settled
, currency_code
, receipt_id
, receipt_number
, source_object_id
, source_object_number
, payment_method
, gl_date
, effective_date
, cust_account_id
, cust_billto_acct_site_id
, cust_shipto_acct_site_id
, owner_id
, org_id
, claim_type_id
, claim_date
, due_date
, reason_code_id
, comments
, root_claim_id
, sales_rep_id
, vendor_id
, vendor_site_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT fnd.user_name
FROM fnd_user fnd
, ams_jtf_rs_emp_v rs
WHERE rs.user_id = fnd.user_id
AND rs.resource_id = cv_owner_id;
SELECT location
FROM hz_cust_site_uses
WHERE site_use_id = cv_site_id;
SELECT p.party_name,
a.account_number
FROM hz_cust_accounts a
, hz_parties p
WHERE a.party_id = p.party_id
AND a.cust_account_id = cv_cust_account_id;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = cv_org_id ;
SELECT name
, cm_trx_type_id
, dm_trx_type_id
, cb_trx_type_id
FROM ozf_claim_types_vl
WHERE claim_type_id = cv_claim_type_id;
SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cv_trx_type_id;
SELECT name
FROM ozf_reason_codes_all_vl
WHERE reason_code_id = cv_reason_code_id ;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_PAYMENT_METHOD'
AND lookup_code = cv_payment_method ;
SELECT nvl(s.name, r.source_name)
FROM jtf_rs_salesreps s
, jtf_rs_resource_extns r
WHERE s.resource_id = r.resource_id
AND s.salesrep_id = cv_salesrep_id
AND s.org_id = cv_org_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CLAIM_STATUS'
AND lookup_code = cv_status_code;
SELECT ar.meaning
FROM ar_lookups ar
, ozf_reason_codes_vl rs
WHERE rs.reason_code_id = cv_reason_code_id
AND ar.lookup_type = 'CREDIT_MEMO_REASON'
AND rs.reason_code = ar.lookup_code;
SELECT ar.meaning
FROM ar_lookups ar
, ozf_reason_codes_vl rs
WHERE rs.reason_code_id = cv_reason_code_id
AND ar.lookup_type = 'ADJUST_REASON'
AND rs.adjustment_reason_code = ar.lookup_code;
SELECT payment_method
, settlement_amount
FROM ozf_settlement_docs
WHERE claim_id = cv_claim_id
AND payment_status = 'PENDING_PAID';
UPDATE ozf_claims_all
SET cstl_wf_item_key = l_itemkey
WHERE claim_id = p_claim_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;
SELECT object_version_number
, org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT claim_number
, settled_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
FROM ozf_claims
WHERE claim_id = cv_claim_id;