[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select account_derivation,
PAY_DIST_SET_ID
from OKL_SYS_ACCT_OPTS;
SELECT *
FROM okl_ext_pay_invs_b
WHERE trx_status_code = 'ENTERED'
FOR UPDATE OF TRX_STATUS_CODE;
SELECT *
FROM okl_xtl_pay_invs_b
WHERE xpi_id_details = p_xpi_id;
SELECT self_bill_inv_num
FROM okl_cnsld_ap_invs
WHERE cnsld_ap_inv_id = p_cnsld_ap_inv_id;
SELECT taxable_yn
FROM okl_txl_ap_inv_lns_b
WHERE id = p_tpl_id;
SELECT CHR.CONTRACT_NUMBER
FROM okc_k_headers_b chr
,okl_txl_ap_inv_lns_all_b tpl
,okl_xtl_pay_invs_all_b xlp
WHERE XLP.ID = p_id
AND XLP.tpl_id = TPL.id
AND TPL.khr_id = chr.id;
SELECT CHR.CONTRACT_NUMBER
FROM okc_k_headers_b chr
,okl_trx_ap_invoices_b tap
,okl_txl_ap_inv_lns_all_b tpl
,okl_xtl_pay_invs_all_b xlp
WHERE XLP.XPI_ID_DETAILS = p_id
AND XLP.tap_id = TAP.id
AND tpl.tap_id = tap.id
AND tpl.khr_id = chr.id; */
SELECT povs.vendor_id
FROM po_vendor_sites_all povs
WHERE povs.vendor_site_id = p_site_id;
SELECT try.name try_name,
tpl.sty_id
FROM okl_txl_ap_inv_lns_b tpl
, okl_trx_ap_invoices_b tap
, okl_trx_types_v try
WHERE tpl.id = p_tpl_id
AND tpl.tap_id = tap.id
AND tap.try_id = try.id;
select cle.name
from OKC_K_LINES_V cle
where cle.id = p_cle_id;
select stream_type_purpose
from OKL_STRM_TYPE_B
where id = p_sty_id;
SELECT NVL(kle.cle_id, kle.id) top_kle_id
, kle.dnz_chr_id khr_id
-- , try.name try_name -- cklee 06/19/2007
FROM OKC_K_LINES_B kle
, okl_txl_ap_inv_lns_b tpl
, okl_trx_ap_invoices_b tap
, okl_trx_types_v try
WHERE tpl.id = p_tpl_id
AND tpl.kle_id = kle.id
AND tpl.tap_id = tap.id
AND tap.try_id = try.id;
SELECT csi.install_location_id
, csi.location_id
FROM csi_item_instances csi,
okc_k_items cim,
okc_k_lines_b inst,
okc_k_lines_b ib,
okc_line_styles_b lse
WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
AND cim.cle_id = ib.id
AND ib.cle_id = inst.id
AND inst.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM2'
AND inst.cle_id = p_top_kle_id;
SELECT csi.install_location_id
, csi.location_id
, csi.install_location_type_code
-- , csi.location_type_code
FROM csi_item_instances csi,
okc_k_items cim,
okc_k_lines_b inst,
okc_k_lines_b ib,
okc_line_styles_b lse
WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
AND cim.cle_id = ib.id
AND ib.cle_id = inst.id
AND inst.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM2'
AND inst.cle_id = p_top_kle_id;
select hps.location_id
from hz_party_sites hps
where hps.party_site_id = p_party_site_id;
select hps.party_site_id install_location_id,
hl.location_id
from hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
okl_txl_itm_insts tii,
okc_k_lines_b cleb_ib,
okc_k_lines_b cleb_inst,
okc_line_styles_b lse1,
okc_line_styles_b lse2
where hl.location_id = hps.location_id
and hps.party_site_id = hpsu.party_site_id
and hpsu.party_site_use_id = tii.object_id1_new
and tii.jtot_object_code_new = 'OKX_PARTSITE'
and tii.kle_id = cleb_ib.id
and cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
and cleb_ib.cle_id = cleb_inst.id
and cleb_ib.lse_id = lse1.id
and lse1.lty_code = 'INST_ITEM'
and cleb_inst.cle_id = p_top_kle_id
and cleb_inst.lse_id = lse2.id
and lse2.lty_code = 'FREE_FORM2';
SELECT cust_acct_id,
sts_code --07-May-2008 cklee -- Fixed bug:7015970
FROM okc_k_headers_b khr
where khr.id = p_khr_id;
SELECT a.CUST_ACCT_SITE_ID
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
b.site_use_code = 'SHIP_TO' AND
a.party_site_id = c.party_site_id AND
a.cust_account_id = p_customer_num AND
c.party_site_id = p_install_location AND
c.location_id = p_location;
SELECT c.OBJECT1_ID1,
c.OBJECT1_ID2 --21-Oct-2011 sechawla Bug 12888543 : added to get the inventory org id
FROM okc_k_lines_b a,
okc_line_styles_b b,
okc_k_items c
WHERE a.cle_id = p_cle_id
AND a.lse_id = b.id
AND b.lty_code = 'ITEM'
AND a.id = c.cle_id
UNION
SELECT c.object1_id1,
c.OBJECT1_ID2 --21-Oct-2011 sechawla Bug 12888543 : added to get the inventory org id
FROM okc_k_lines_v a,
okc_line_styles_v b,
okc_k_items c
WHERE a.id = p_cle_id
AND a.lse_id = b.id
AND b.lty_code = 'SOLD_SERVICE'
AND c.cle_id = a.id;
INSERT INTO AP_INVOICES_INTERFACE(
Invoice_type_lookup_code
,accts_pay_code_combination_id
,attribute1
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute_category
,created_by
,creation_date
,description
,doc_category_code
,gl_date
,invoice_amount
,invoice_currency_code
,exchange_rate
,exchange_rate_type
,exchange_date
,invoice_date
,invoice_id
,invoice_num
,voucher_num
,last_updated_by
,last_update_date
,last_update_login
,org_id
,payment_method_lookup_code
--start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
,payment_method_code
--end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
,request_id
,source
,terms_id
,vendor_id
,vendor_site_id
,workflow_flag
,PAY_GROUP_LOOKUP_CODE
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,legal_entity_id
,application_id
,product_table
,reference_key1
,supplier_tax_invoice_number
,CALC_TAX_DURING_IMPORT_FLAG
--Bug# 11705655
,ADD_TAX_TO_INV_AMT_FLAG
)
values(
r_invoice_hdr.invoice_type
,r_invoice_hdr.accts_pay_cc_id
,r_invoice_hdr.attribute1
,r_invoice_hdr.attribute10
,r_invoice_hdr.attribute11
,r_invoice_hdr.attribute12
,r_invoice_hdr.attribute13
,r_invoice_hdr.attribute14
,r_invoice_hdr.attribute15
,r_invoice_hdr.attribute2
,r_invoice_hdr.attribute3
,r_invoice_hdr.attribute4
,r_invoice_hdr.attribute5
,r_invoice_hdr.attribute6
,r_invoice_hdr.attribute7
,r_invoice_hdr.attribute8
,r_invoice_hdr.attribute9
,r_invoice_hdr.attribute_category
,fnd_global.user_id
,sysdate
,null
,r_invoice_hdr.doc_category_code
,r_invoice_hdr.gl_date
,r_invoice_hdr.invoice_amount
,r_invoice_hdr.invoice_currency_code
,r_invoice_hdr.CURRENCY_CONVERSION_RATE
,r_invoice_hdr.CURRENCY_CONVERSION_TYPE
,r_invoice_hdr.CURRENCY_CONVERSION_DATE
,r_invoice_hdr.invoice_date
,r_invoice_hdr.invoice_id
,r_invoice_hdr.vendor_invoice_number
,r_invoice_hdr.invoice_num
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,r_invoice_hdr.org_id
,r_invoice_hdr.payment_method
--start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
,r_invoice_hdr.payment_method
--end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
,fnd_global.conc_request_id
,'OKL'
,r_invoice_hdr.terms_id
,r_invoice_hdr.vendor_id
,r_invoice_hdr.vendor_site_id
,r_invoice_hdr.workflow_flag
,r_invoice_hdr.pay_group_lookup_code
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,r_invoice_hdr.legal_entity_id
,fnd_global.prog_appl_id
,'OKL_CNSLD_AP_INVS_ALL'
,r_invoice_hdr.cnsld_ap_inv_id
,l_self_bill_invnum
,'Y'
--Bug# 11705655
,'Y'
);
DELETE FROM ap_invoice_lines_interface
WHERE invoice_id = r_invoice_hdr.invoice_id;
DELETE FROM AP_INVOICES_INTERFACE
WHERE invoice_id = r_invoice_hdr.invoice_id;
INSERT INTO ap_invoice_lines_interface(
accounting_date
,amount
,amount_includes_tax_flag
,attribute1
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute_category
,created_by
,creation_date
,dist_code_combination_id
,invoice_id
,invoice_line_id
,last_updated_by
,last_update_date
,last_update_login
,line_number
,line_type_lookup_code
,org_id
,tax_code
,application_id
,product_table
,reference_key1
,reference_key5 --21-Oct-2011 sechawla Bug 12888543 : added to pass inventory org id
,description
,TAX_CLASSIFICATION_CODE
,TRX_BUSINESS_CATEGORY
,PRODUCT_CATEGORY
,PRODUCT_TYPE
,PRIMARY_INTENDED_USE
,USER_DEFINED_FISC_CLASS
,ASSESSABLE_VALUE
,SHIP_TO_LOCATION_ID
,INVENTORY_ITEM_ID
,DISTRIBUTION_SET_ID--:| 16-Oct-2007 cklee -- Fixed bug:6502786
)
values(
r_invoice_lines.accounting_date
,r_invoice_lines.amount
,l_amount_includes_tax_flag
,r_invoice_lines.attribute1
,r_invoice_lines.attribute10
,r_invoice_lines.attribute11
,r_invoice_lines.attribute12
,r_invoice_lines.attribute13
,r_invoice_lines.attribute14
,r_invoice_lines.attribute15
,r_invoice_lines.attribute2
,r_invoice_lines.attribute3
,r_invoice_lines.attribute4
,r_invoice_lines.attribute5
,r_invoice_lines.attribute6
,r_invoice_lines.attribute7
,r_invoice_lines.attribute8
,r_invoice_lines.attribute9
,r_invoice_lines.attribute_category
,fnd_global.user_id
,sysdate
--start:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
-- ,NVL(r_invoice_lines.dist_code_combination_id, -1) --change for SLA impact
,r_invoice_lines.dist_code_combination_id
--end:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
,r_invoice_hdr.invoice_id
,r_invoice_lines.invoice_line_id
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,r_invoice_lines.line_number
,r_invoice_lines.line_type
,r_invoice_lines.org_id
,r_invoice_lines.tax_code
,fnd_global.prog_appl_id
,'OKL_TXL_AP_INV_LNS_ALL_B'
,r_invoice_lines.tpl_id
,l_inventory_org_id --21-Oct-2011 sechawla Bug 12888543 : added inv org id
,trim(substr(trim(substr(l_contract_number,1,100)) || '/' || trim(substr(l_asset_number,1,100)) || '/' || trim(substr(l_stream_type_purpose,1,38)), 1,240))
--start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
,lx_tax_det_rec.x_tax_code
,lx_tax_det_rec.x_trx_business_category
,lx_tax_det_rec.x_product_category
,lx_tax_det_rec.x_product_type
,lx_tax_det_rec.x_line_intended_use
,lx_tax_det_rec.x_user_defined_fisc_class
,lx_tax_det_rec.x_assessable_value
/*
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
*/
--start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
,l_ship_to
,l_inventory_item_id
,l_PAY_DIST_SET_ID --:| 16-Oct-2007 cklee -- Fixed bug:6502786
);
UPDATE okl_ext_pay_invs_b
SET trx_status_code = 'PROCESSED'
WHERE CURRENT OF c_invoice_hdr;
UPDATE ap_invoices_interface
SET TAXATION_COUNTRY = lx_tax_det_rec.X_DEFAULT_TAXATION_COUNTRY
WHERE invoice_id = r_invoice_hdr.invoice_id;