[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jtot_object1_code,
object1_id1,
object1_id2
FROM okc_rules_b
WHERE rgp_id =
(SELECT id
FROM okc_rule_groups_b
WHERE dnz_chr_id = p_khr_id
AND cle_id IS NULL
AND rgd_code = 'LABILL')
AND rule_information_category = p_rule_category;
SELECT id1
FROM okx_cust_trx_types_v
WHERE name = 'Invoice-OKL'
AND set_of_books_id = p_sob_id
AND org_id = p_org_id;
SELECT id1
FROM okx_cust_trx_types_v
WHERE name = 'Credit Memo-OKL'
AND set_of_books_id = p_sob_id
AND org_id = p_org_id;
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT ID
FROM OKL_TRX_TYPES_V
WHERE AEP_CODE = 'BILLING';
SELECT ID
FROM OKL_TRX_TYPES_V
WHERE AEP_CODE = 'CREDIT_MEMO';
SELECT a.cust_acct_id cust_account_id,
b.cust_acct_site_id,
c.standard_terms payment_term_id
FROM okc_k_headers_v a,
okx_cust_site_uses_v b,
hz_customer_profiles c
WHERE a.id = p_contract_id
AND a.bill_to_site_use_id = b.id1
AND a.bill_to_site_use_id = c.site_use_id(+);
SELECT c.receipt_method_id
FROM ra_cust_receipt_methods c
WHERE c.cust_receipt_method_id = p_cust_rct_mthd;
SELECT bank_account_id
FROM okx_rcpt_method_accounts_v
WHERE id1 = p_id;
SELECT B.TERM_ID
FROM RA_TERMS_TL T, RA_TERMS_B B
where T.name = 'IMMEDIATE' and T.LANGUAGE = userenv('LANG')
and B.TERM_ID = T.TERM_ID;
SELECT c.creation_method_code
FROM ar_receipt_methods m,
ar_receipt_classes c
WHERE m.receipt_class_id = c.receipt_class_id
AND m.receipt_method_id = p_rct_method_id;
SELECT currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
SELECT inf.id,
rul.rule_information4 review_invoice_yn
FROM okc_rule_groups_v rgp,
okc_rules_v rul,
okl_invoice_formats_v inf
WHERE rgp.dnz_chr_id = cp_khr_id
AND rgp.chr_id = rgp.dnz_chr_id
AND rgp.id = rul.rgp_id
AND rgp.cle_id IS NULL
AND rgp.rgd_code = 'LABILL'
AND rul.rule_information_category = 'LAINVD'
AND rul.rule_information1 = inf.name;
SELECT rule_information1 private_label
FROM okc_rule_groups_b a,
okc_rules_b b
WHERE a.dnz_chr_id = cp_khr_id
AND a.rgd_code = 'LALABL'
AND a.id = b.rgp_id
AND b.rule_information_category = 'LALOGO';
SELECT receivables_invoice_id
FROM okl_txd_ar_ln_dtls_v
WHERE id = p_tld_id;
SELECT receivables_invoice_id
FROM okl_txl_ar_inv_lns_v
WHERE id = p_til_id;
SELECT c.minimum_accountable_unit,
c.PRECISION
FROM fnd_currencies c
WHERE c.currency_code = cp_currency_code;
SELECT c.minimum_accountable_unit,
c.PRECISION
FROM fnd_currencies c,
okl_trx_ar_invoices_b b
WHERE c.currency_code = b.currency_code
AND b.khr_id = p_khr_id;
SELECT NVL(inv_organization_id, -99)
FROM okc_k_headers_b
WHERE id = p_contract_id;
okl_tai_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => lp_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_til_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
x_tilv_rec => lx_tilv_rec);
okl_tld_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => lp_tldv_tbl(l_til_loop_cnt),
x_tldv_rec => lx_tldv_rec);
okl_tld_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => lp_tldv_tbl(l_tld_loop_cnt),
x_tldv_rec => lx_tldv_rec);
okl_tai_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => lp_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_til_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
x_tilv_rec => lx_tilv_rec);
okl_tld_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => lp_tldv_tbl(l_tld_loop_cnt),
x_tldv_rec => lx_tldv_rec);
SELECT
ity.name ity_name,
ilt.name ilt_name
FROM okl_invoice_types_v ity,
okl_invc_line_types_v ilt,
okl_invc_frmt_strms_v frs,
okl_strm_type_v sty
WHERE ity.inf_id = p_format_id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND sty.id = frs.sty_id
AND frs.sty_id = p_stream_id;
SELECT
ity.name ity_name,
ilt.name ilt_name
FROM okl_invoice_types_v ity,
okl_invc_line_types_v ilt
WHERE ity.inf_id = p_format_id
AND ilt.ity_id = ity.id;
PROCEDURE update_manual_invoice(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
,x_taiv_rec OUT NOCOPY okl_tai_pvt.taiv_rec_type
,x_tilv_tbl OUT NOCOPY okl_til_pvt.tilv_tbl_type
,x_tldv_tbl OUT NOCOPY okl_tld_pvt.tldv_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_manual_invoice';
SELECT ID FROM OKL_TXD_AR_LN_DTLS_B
WHERE TIL_ID_DETAILS = p_til_id_details;
SAVEPOINT UPDATE_MANUAL_INVOICE;
okl_tai_pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => lp_taiv_rec,
x_taiv_rec => lx_taiv_rec);
/***** Update the TIL records and correspondingly its TLD record. *****/
FOR i IN l_tilv_Updt_tbl.FIRST .. l_tilv_Updt_tbl.LAST LOOP
okl_til_pvt.update_row(
p_api_version => p_api_version ,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => l_tilv_Updt_tbl(i),
x_tilv_rec => lx_tilv_rec);
okl_tld_pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => lp_tldv_tbl(i),
x_tldv_rec => lx_tldv_rec);
/***** Insert into TIL records and correspondingly its TLD record. *****/
lp_tldv_tbl.delete;
okl_til_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => l_tilv_Crt_tbl(i),
x_tilv_rec => lx_tilv_rec);
okl_tld_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => lp_tldv_tbl(i),
x_tldv_rec => lx_tldv_rec);
/***** Insertion completed in TIL and TLD *****/
l_flag_acc_call := 'Y';
ROLLBACK TO UPDATE_MANUAL_INVOICE;
ROLLBACK TO UPDATE_MANUAL_INVOICE;
ROLLBACK TO UPDATE_MANUAL_INVOICE;
END update_manual_invoice;
PROCEDURE delete_manual_invoice(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_taiv_id NUMBER
,p_tilv_id NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_manual_invoice';
SELECT ID til_id
FROM OKL_TXL_AR_INV_LNS_B
WHERE TAI_ID = p_taiv_id;
SELECT ID tld_id
FROM OKL_TXD_AR_LN_DTLS_B
WHERE TIL_ID_DETAILS = g_til_id;
SAVEPOINT DELETE_MANUAL_INVOICE ;
OKL_TLD_PVT.delete_row(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => l_tldv_rec
);
OKL_TIL_PVT.delete_row(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => l_tilv_rec
);
OKL_TAI_PVT.delete_row(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => l_taiv_rec);
ELSIF lp_til_id IS NOT NULL THEN -- Delete a TIL Rccord and all its TLD Records.
FOR get_tld_dtl_rec in get_tld_dtl_csr(lp_til_id)
LOOP
l_tldv_rec.id := get_tld_dtl_rec.tld_id;
OKL_TLD_PVT.delete_row(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tldv_rec => l_tldv_rec
);
OKL_TIL_PVT.delete_row(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => l_tilv_rec
);
ROLLBACK TO DELETE_MANUAL_INVOICE;
ROLLBACK TO DELETE_MANUAL_INVOICE;
ROLLBACK TO DELETE_MANUAL_INVOICE;
END delete_manual_invoice;