[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT xls.id xls_id
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls
WHERE xls.xsi_id_details = xsi.id AND
xsi.id = p_xsi_id;
SELECT id esd_id
FROM okl_xtd_sell_invs_v
WHERE xls_id = p_xls_id;
error_tbl.DELETE;
INSERT INTO OKL_EXT_SELL_INVS_B
VALUES xsi_tbl(indx);
PRINT_TO_LOG('BULK For inserting external header, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_ext_sell_invs_b');
INSERT INTO OKL_EXT_SELL_INVS_TL
VALUES xsitl_tbl(indx);
PRINT_TO_LOG('BULK For inserting external header tl, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_ext_sell_invs_tl');
INSERT INTO OKL_XTL_SELL_INVS_B
VALUES xls_tbl(indx);
PRINT_TO_LOG('BULK For inserting external lines, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_xtl_sell_invs_b');
INSERT INTO OKL_XTL_SELL_INVS_TL
VALUES xlstl_tbl(indx);
PRINT_TO_LOG('BULK For inserting external lines tl, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_xtl_sell_invs_tl');
INSERT INTO OKL_XTD_SELL_INVS_B
VALUES esd_tbl(indx);
PRINT_TO_LOG('BULK For inserting external details, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_xtd_sell_invs_b');
INSERT INTO OKL_XTD_SELL_INVS_TL
VALUES esdtl_tbl(indx);
PRINT_TO_LOG('BULK For inserting external details tl, error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
PRINT_TO_LOG('BULK Done Inserting into okl_xtd_sell_invs_tl');
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'PROCESSED',
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE ID = tai_id_tbl(indx);
Fnd_File.PUT_LINE (Fnd_File.LOG, 'BULK Status updated!');
DELETE FROM Okl_Xtd_Sell_Invs_B
WHERE id = del_xtd_rec.esd_id;
DELETE FROM Okl_Xtd_Sell_Invs_TL
WHERE id = del_xtd_rec.esd_id;
DELETE FROM Okl_Xtl_Sell_Invs_B
WHERE id = del_error_rec.xls_id;
DELETE FROM Okl_Xtl_Sell_Invs_TL
WHERE id = del_error_rec.xls_id;
DELETE FROM Okl_Ext_Sell_Invs_B
WHERE id = error_tbl(i).id;
DELETE FROM Okl_Ext_Sell_Invs_TL
WHERE id = error_tbl(i).id;
error_tbl.DELETE;
tai_id_tbl.DELETE;
xsi_tbl.DELETE;
xls_tbl.DELETE;
esd_tbl.DELETE;
xsitl_tbl.DELETE;
xlstl_tbl.DELETE;
esdtl_tbl.DELETE;
SELECT cr_dr_flag,
code_combination_id,
source_id,
amount,
percentage,
--Start code changes for rev rec by fmiao on 10/05/2004
NVL(comments, '-99') comments --End code changes for rev rec by fmiao on 10/05/2004
FROM okl_trns_acc_dstrs
WHERE source_id = p_source_id
AND source_table = p_source_table;
SELECT xsi.id xsi_id,
xls.id xls_id
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld
WHERE til.tai_id = tai.id
AND tld.til_id_details = til.id
AND xls.xsi_id_details = xsi.id
AND xls.tld_id = tld.id
AND tai.id = p_tai_id;
SELECT xsi.id xsi_id,
xls.id xls_id
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til
WHERE til.tai_id = tai.id
AND xls.xsi_id_details = xsi.id
AND xls.til_id = til.id
AND tai.id = p_tai_id;
SELECT id esd_id
FROM okl_xtd_sell_invs_v
WHERE xls_id = p_xls_id;
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 object1_id1
FROM okc_k_party_roles_b
WHERE jtot_object1_code = 'OKX_PARTY'
AND rle_code = 'CUSTOMER'
AND chr_id = p_khr_id
and dnz_chr_id = chr_id ;
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 Jtot_object1_code, Jtot_object2_code, object1_id1
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 cust_acct_site_id,
payment_term_id
FROM okx_cust_site_uses_v
WHERE id1 = p_id1;
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 object1_id1
FROM okc_rules_b rul
WHERE rul.rule_information_category = 'CAN'
AND EXISTS (SELECT '1' FROM okc_rule_groups_b rgp
WHERE rgp.id = rul.rgp_id
AND rgp.rgd_code = 'LACAN'
AND rgp.chr_id = rul.dnz_chr_id
AND rgp.chr_id = p_khr_id);
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT A.cust_account_id,
A.cust_acct_site_id,
A.payment_term_id
FROM Okx_cust_site_uses_v A, okx_customer_accounts_v C
WHERE A.id1 = p_id
AND C.id1 = A.cust_account_id
AND A.site_use_code = Code;*/
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 contract_number
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT name
FROM okl_strm_type_v
WHERE id = p_sty_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 c.minimum_accountable_unit,
c.PRECISION
FROM fnd_currencies c
WHERE c.currency_code = cp_currency_code;
SELECT currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
SELECT rule_information1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LAASTX' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LAASTX' AND
rgp.dnz_chr_id = p_khr_id;
SELECT rule_information1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LAASTX' AND
rgp.cle_id = p_cle_id AND
rul.rule_information_category = 'LAASTX' AND
rgp.dnz_chr_id = p_khr_id;
l_program_update_date DATE;
SELECT decode(fnd_global.conc_request_id, -1, NULL, fnd_global.conc_request_id)
FROM dual;
SELECT name
FROM hr_operating_units
WHERE organization_id = nvl(to_number(substrb(userenv('CLIENT_INFO'), 1, 10)), -99);
SELECT COUNT(*)
FROM okl_ext_sell_invs_v
WHERE trx_status_code = p_sts
AND request_id = p_req_id;
SELECT COUNT(*)
FROM okl_ext_sell_invs_v
WHERE trx_status_code = p_sts
AND request_id = p_req_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 NVL(inv_organization_id, -99)
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
SELECT 'X' FROM
OKL_TXD_AR_LN_DTLS_B
WHERE ID = p_tld_id
AND TLD_ID_REVERSES IS NULL;
SELECT 'X' FROM
OKL_TXL_AR_INV_LNS_B
WHERE ID = p_til_id
AND TIL_ID_REVERSES IS NULL;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
INTO
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
FROM dual;
xsi_tbl.DELETE;
xls_tbl.DELETE;
esd_tbl.DELETE;
xsitl_tbl.DELETE;
xlstl_tbl.DELETE;
esdtl_tbl.DELETE;
tai_id_tbl.DELETE;
-- caling Bulk insert
bulk_process
(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_commit);
xsi_tbl(l_xsi_cnt).LAST_UPDATE_DATE := SYSDATE;
xsi_tbl(l_xsi_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xsi_tbl(l_xsi_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
xsi_tbl(l_xsi_cnt).program_update_date := l_program_update_date;
-- Updated after consolidation
xsitl_tbl(l_xsitl_cnt).XTRX_CONS_INVOICE_NUMBER := NULL;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_DATE := SYSDATE;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
okl_ext_sell_invs_pub.insert_ext_sell_invs(l_api_version, l_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xsiv_rec, x_xsiv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
xls_tbl(l_xls_cnt).LAST_UPDATE_DATE := SYSDATE;
xls_tbl(l_xls_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xls_tbl(l_xls_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
xls_tbl(l_xls_cnt).program_update_date := l_program_update_date;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_DATE := SYSDATE;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
okl_xtl_sell_invs_pub.insert_xtl_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xlsv_rec, x_xlsv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
Okl_Debug_Pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
okl_xtd_sell_invs_pub.insert_xtd_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_esdv_rec, x_esdv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
DELETE FROM okl_xtd_sell_invs_b
WHERE id = d_esdv_rec.id;
DELETE FROM okl_xtd_sell_invs_tl
WHERE id = d_esdv_rec.id;
DELETE FROM okl_xtl_sell_invs_b
WHERE id = d_xlsv_rec.id;
DELETE FROM okl_xtl_sell_invs_tl
WHERE id = d_xlsv_rec.id;
DELETE FROM okl_ext_sell_invs_b
WHERE id = d_xsiv_rec.id;
DELETE FROM okl_ext_sell_invs_tl
WHERE id = d_xsiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = n_taiv_rec.trx_status_code
WHERE id = n_taiv_rec.id;
esd_tbl(l_esd_cnt).LAST_UPDATE_DATE := SYSDATE;
esd_tbl(l_esd_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
esd_tbl(l_esd_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
esd_tbl(l_esd_cnt).program_update_date := l_program_update_date;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_DATE := SYSDATE;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
-- clear out the tbl for 2 level insert --
l_commit_cnt2 := 0;
xsi_tbl.DELETE;
xls_tbl.DELETE;
esd_tbl.DELETE;
xsitl_tbl.DELETE;
xlstl_tbl.DELETE;
esdtl_tbl.DELETE;
tai_id_tbl.DELETE;
-- Bulk insert/update records, Commit and restart
bulk_process
(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_commit);
--populate tai table for update
tai_id_tbl(l_tai_id_cnt) := p_ie_tbl2(h).tai_id;
xsi_tbl(l_xsi_cnt).LAST_UPDATE_DATE := SYSDATE;
xsi_tbl(l_xsi_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xsi_tbl(l_xsi_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
xsi_tbl(l_xsi_cnt).program_update_date := l_program_update_date;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_DATE := SYSDATE;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
okl_ext_sell_invs_pub.insert_ext_sell_invs(l_api_version, l_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xsiv_rec, x_xsiv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
-- Updated after Consolidation
xls_tbl(l_xls_cnt).XTRX_CONS_LINE_NUMBER := NULL;
xls_tbl(l_xls_cnt).LAST_UPDATE_DATE := SYSDATE;
xls_tbl(l_xls_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xls_tbl(l_xls_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
xls_tbl(l_xls_cnt).program_update_date := l_program_update_date;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_DATE := SYSDATE;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
okl_xtl_sell_invs_pub.insert_xtl_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xlsv_rec, x_xlsv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
Okl_Debug_Pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
esd_tbl(l_esd_cnt).LAST_UPDATE_DATE := SYSDATE;
esd_tbl(l_esd_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
esd_tbl(l_esd_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
esd_tbl(l_esd_cnt).program_update_date := l_program_update_date;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_DATE := SYSDATE;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
PRINT_TO_LOG('TBL insert for < 500 records ...commented out');
--tai_id_tbl.DELETE;
okl_xtd_sell_invs_pub.insert_xtd_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_esdv_rec, x_esdv_rec);
okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
DELETE FROM okl_xtd_sell_invs_b
WHERE id = d_esdv_rec.id;
DELETE FROM okl_xtd_sell_invs_tl
WHERE id = d_esdv_rec.id;
DELETE FROM okl_xtl_sell_invs_b
WHERE id = d_xlsv_rec.id;
DELETE FROM okl_xtl_sell_invs_tl
WHERE id = d_xlsv_rec.id;
DELETE FROM okl_ext_sell_invs_b
WHERE id = d_xsiv_rec.id;
DELETE FROM okl_ext_sell_invs_tl
WHERE id = d_xsiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = n_taiv_rec.trx_status_code
WHERE id = n_taiv_rec.id;
SELECT t1.id tai_id,
t1.khr_id contract_id,
t1.trx_status_code trx_status_code,
t1.date_invoiced date_invoiced,
t1.ixx_id ixx_id,
t1.irm_id irm_id,
t1.irt_id irt_id,
t1.ibt_id ibt_id,
t1.set_of_books_id set_of_books_id,
t1.description tai_description,
t1.currency_code currency_code,
--Start code added by pgomes on 20-NOV-2002
t1.currency_conversion_type currency_conversion_type,
t1.currency_conversion_rate currency_conversion_rate,
t1.currency_conversion_date currency_conversion_date,
--End code added by pgomes on 20-NOV-2002
t1.org_id org_id,
t1.trx_number trx_number,
t1.legal_entity_id, -- for LE Uptake project 08-11-2006
t2.inv_receiv_line_code inv_receiv_line_code,
NVL(t3.description, t2.description) til_description,
t2.quantity quantity,
t2.kle_id kle_id,
t3.id tld_id,
t3.amount amount,
t3.tld_id_reverses tld_id_reverses,
t3.sty_id sty_id,
t4.taxable_default_yn taxable_default_yn,
t3.sel_id sel_id,
-- Start changes on remarketing by fmiao on 10/18/04 --
t3.inventory_item_id inventory_item_id,
-- End changes on remarketing by fmiao on 10/18/04 --
NVL(t3.inventory_org_id, t2.inventory_org_id) inventory_org_id
FROM okl_trx_ar_invoices_v t1,
okl_txl_ar_inv_lns_v t2,
okl_txd_ar_ln_dtls_v t3,
okl_strm_type_v t4,
okc_k_headers_b CHR
WHERE t1.trx_status_code = 'SUBMITTED'
AND t1.khr_id = CHR.id
AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
AND t2.tai_id = t1.id
AND t3.til_id_details = t2.id
AND t4.id = t3.sty_id
ORDER BY tai_id;
SELECT t1.id tai_id,
t1.khr_id contract_id,
t1.trx_status_code trx_status_code,
t1.date_invoiced date_invoiced,
t1.ixx_id ixx_id,
t1.irm_id irm_id,
t1.irt_id irt_id,
t1.ibt_id ibt_id,
t1.set_of_books_id set_of_books_id,
t1.description tai_description,
t1.currency_code currency_code,
--Start code added by pgomes on 20-NOV-2002
t1.currency_conversion_type currency_conversion_type,
t1.currency_conversion_rate currency_conversion_rate,
t1.currency_conversion_date currency_conversion_date,
--End code added by pgomes on 20-NOV-2002
t1.org_id org_id,
t1.trx_number trx_number,
t1.legal_entity_id, -- for LE Uptake project 08-11-2006
t2.id til_id,
t2.kle_id kle_id,
t2.inv_receiv_line_code inv_receiv_line_code,
t2.description til_description,
t2.quantity quantity,
t2.amount amount,
t2.til_id_reverses til_id_reverses,
t2.sty_id sty_id,
t4.taxable_default_yn taxable_default_yn,
-- Start changes on remarketing by fmiao on 10/18/04 --
t2.inventory_item_id inventory_item_id,
-- End changes on remarketing by fmiao on 10/18/04 --
t2.inventory_org_id inventory_org_id,
-- Start Bug 4673593
t2.bank_acct_id bank_acct_id, -- End Bug 4673593 --bug 5160519,
t1.qte_id qte_id --Termination Quote id --bug 5160519:end
FROM okl_trx_ar_invoices_v t1,
okl_txl_ar_inv_lns_v t2,
okl_strm_type_v t4,
okc_k_headers_b CHR
WHERE t1.trx_status_code = 'SUBMITTED'
AND t1.khr_id = CHR.id
AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
AND t2.tai_id = t1.id
AND t4.id = t2.sty_id
AND NOT EXISTS
(SELECT *
FROM okl_txd_ar_ln_dtls_b t3
WHERE t3.til_id_details = t2.id)
ORDER BY tai_id;
SELECT t1.id tai_id,
t1.khr_id contract_id,
t1.trx_status_code trx_status_code,
t1.date_invoiced date_invoiced,
t1.ixx_id ixx_id,
t1.irm_id irm_id,
t1.irt_id irt_id,
t1.ibt_id ibt_id,
t1.set_of_books_id set_of_books_id,
t1.description tai_description,
t1.currency_code currency_code,
--Start code added by pgomes on 20-NOV-2002
t1.currency_conversion_type currency_conversion_type,
t1.currency_conversion_rate currency_conversion_rate,
t1.currency_conversion_date currency_conversion_date,
--End code added by pgomes on 20-NOV-2002
t1.org_id org_id,
t1.trx_number trx_number,
t1.legal_entity_id, -- for LE Uptake project 08-11-2006
t2.inv_receiv_line_code inv_receiv_line_code,
NVL(t3.description, t2.description) til_description,
t2.quantity quantity,
t2.kle_id kle_id,
t3.id tld_id,
t3.amount amount,
t3.tld_id_reverses tld_id_reverses,
t3.sty_id sty_id,
t4.taxable_default_yn taxable_default_yn,
t3.sel_id sel_id,
-- Start changes on remarketing by fmiao on 10/18/04 --
t3.inventory_item_id inventory_item_id,
-- End changes on remarketing by fmiao on 10/18/04 --
NVL(t3.inventory_org_id, t2.inventory_org_id) inventory_org_id
FROM okl_trx_ar_invoices_v t1,
okl_txl_ar_inv_lns_v t2,
okl_txd_ar_ln_dtls_v t3,
okl_strm_type_v t4,
okc_k_headers_b CHR,
OKL_PARALLEL_PROCESSES pws
WHERE t1.trx_status_code = 'SUBMITTED'
AND t1.khr_id = CHR.id
AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
AND t2.tai_id = t1.id
AND t3.til_id_details = t2.id
AND t4.id = t3.sty_id
-- parallel process
AND pws.object_type = 'PREP_CONTRACT'
AND pws.object_value = CHR.contract_number
AND pws.assigned_process = p_assigned_process
ORDER BY tai_id;
SELECT t1.id tai_id,
t1.khr_id contract_id,
t1.trx_status_code trx_status_code,
t1.date_invoiced date_invoiced,
t1.ixx_id ixx_id,
t1.irm_id irm_id,
t1.irt_id irt_id,
t1.ibt_id ibt_id,
t1.set_of_books_id set_of_books_id,
t1.description tai_description,
t1.currency_code currency_code,
--Start code added by pgomes on 20-NOV-2002
t1.currency_conversion_type currency_conversion_type,
t1.currency_conversion_rate currency_conversion_rate,
t1.currency_conversion_date currency_conversion_date,
--End code added by pgomes on 20-NOV-2002
t1.org_id org_id,
t1.trx_number trx_number,
t1.legal_entity_id, -- for LE Uptake project 08-11-2006
t2.id til_id,
t2.kle_id kle_id,
t2.inv_receiv_line_code inv_receiv_line_code,
t2.description til_description,
t2.quantity quantity,
t2.amount amount,
t2.til_id_reverses til_id_reverses,
t2.sty_id sty_id,
t4.taxable_default_yn taxable_default_yn,
-- Start changes on remarketing by fmiao on 10/18/04 --
t2.inventory_item_id inventory_item_id,
-- End changes on remarketing by fmiao on 10/18/04 --
t2.inventory_org_id inventory_org_id,
-- Start Bug 4673593
t2.bank_acct_id bank_acct_id, -- End Bug 4673593 --bug 5160519,
t1.qte_id qte_id --Termination Quote id
--bug 5160519:end
FROM okl_trx_ar_invoices_v t1,
okl_txl_ar_inv_lns_v t2,
okl_strm_type_v t4,
okc_k_headers_b CHR,
OKL_PARALLEL_PROCESSES pws
WHERE t1.trx_status_code = 'SUBMITTED'
AND t1.khr_id = CHR.id
AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
AND t2.tai_id = t1.id
AND t4.id = t2.sty_id
AND NOT EXISTS
(SELECT *
FROM okl_txd_ar_ln_dtls_v t3
WHERE t3.til_id_details = t2.id)
-- parallel process
AND pws.object_type = 'PREP_CONTRACT'
AND pws.object_value = CHR.contract_number
AND pws.assigned_process = p_assigned_process
ORDER BY tai_id;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
SELECT COUNT(*)
FROM okl_ext_sell_invs_v
WHERE trx_status_code = p_sts AND
request_id = p_req_id ;
SELECT NAME
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; -- MOAC fix - Bug#5378114 --varangan- 29-9-06
ie_tbl1.DELETE;
ie_tbl2.DELETE;
ie_tbl1.DELETE;
ie_tbl2.DELETE;
ie_tbl1.DELETE;
ie_tbl2.DELETE;
ie_tbl1.DELETE;
ie_tbl2.DELETE;
total_error_tbl.DELETE;
ie_tbl1.DELETE;
ie_tbl2.DELETE;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;