[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trx_types.type
FROM
ra_customer_trx_lines_all trxl
, ra_customer_trx_all trx
, ra_cust_trx_types_all trx_types
WHERE trxl.customer_trx_id = trx.customer_trx_id
AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
AND trxl.customer_trx_line_id = pn_customer_trx_line_id;
Select a.type,
b.org_id,
b.bill_to_customer_id,
NVL(b.bill_to_site_use_id, 0),
b.trx_date
From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
Where a.cust_trx_type_id = b.cust_trx_type_id
And b.customer_trx_id = v_customer_trx_id
And NVL(a.org_id, 0) = NVL(b.org_id, 0);
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = v_customer_trx_line_id
AND account_class = 'REV';
SELECT NVL(minimum_accountable_unit, NVL(precision, 2)) curr_precision
FROM fnd_currencies
WHERE currency_code = cp_currency_code;
SELECT a.tax_id taxid,
a.tax_rate,
a.uom uom,
a.tax_amount tax_amt,
b.tax_type t_type,
a.customer_trx_line_id line_id,
a.tax_line_no tax_line_no
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
AND a.tax_id = b.tax_id
AND NVL(b.reverse_charge_flag,'N') = 'N' --Added by Qinglei for bug#14619984 SERVICE TAX REVERSE CHARGE TAX LINE IS GENERATED IN AR DISTRIBUTION FORM
ORDER BY 1;
SELECT set_of_books_id,
primary_salesrep_id,
org_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
exchange_rate,
interface_header_context
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_customer_trx_id;
SELECT ORG_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;
SELECT tax_account_id
FROM JAI_CMN_TAXES_ALL B
WHERE B.tax_id = p_tax_id;
SELECT created_from,
interface_header_attribute1,
interface_header_attribute2
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
SELECT batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
select warehouse_id, interface_line_attribute6
from ra_interfacE_lines_all
where interfacE_line_context = 'ORDER ENTRY'
and line_type = 'LINE'
and interface_line_id = v_customer_trx_line_id;
SELECT a.organization_id, a.location_id
FROM jai_pa_draft_invoices a,
ra_interface_lines_all b,
pa_projects_all c
WHERE b.interface_line_context = 'PROJECTS INVOICES'
AND b.line_type = 'LINE'
AND b.interface_line_attribute1 = c.segment1
AND c.project_id = a.project_id
AND a.draft_invoice_num = b.interface_line_attribute2
AND interface_line_id = v_customer_trx_line_id;
select location_id
from jai_om_wsh_lines_all
where order_line_id = cp_order_line_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id
AND location_id = p_loc_id
AND register_id IN (SELECT register_id
FROM JAI_OM_OE_BOND_REG_DTLS
WHERE order_type_id = p_batch_source_id
AND order_flag = 'N');
SELECT A.register_code
FROM JAI_OM_OE_BOND_REG_HDRS A,
JAI_OM_OE_BOND_REG_DTLS b,
oe_transaction_types_tl ott
WHERE A.organization_id = p_organization_id
AND A.location_id = p_location_id
AND A.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = ott.transaction_type_id
AND ott.NAME = p_order_type;
SELECT trx_number
FROM ra_customer_trx_all
WHERE customer_trx_id = v_cust_trx_id;
SELECT regime_id, regime_code
FROM jai_regime_tax_types_v jrttv
WHERE upper(jrttv.tax_type) = upper(cp_tax_type);
SELECT count(1)
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
AND oh.order_number = pn_order_number
AND ot.language = userenv('LANG')
AND ol.line_id = pn_order_line_id
AND owf.process_name IN
('R_RMA_CREDIT_APP_HDR_INV',
'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT',
'R_RMA_FOR_OTA_CREDIT');
SELECT COUNT(1)
FROM MTL_SYSTEM_ITEMS msi, JAI_OM_OE_RMA_LINES l
WHERE msi.inventory_item_id = pr_new.inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
AND l.rma_line_id = pn_order_line_id
AND msi.shippable_item_flag = 'N';
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT COUNT(*)
INTO v_counter
FROM JAI_AR_TRX_INS_LINES_T b
WHERE b.LINK_TO_CUST_TRX_LINE_ID = pr_new.Customer_Trx_Line_Id
AND b.customer_trx_line_id = Tax_Type_Rec.LINE_ID;
INSERT_TAX_LINES_TEMP(pn_extended_amount => round(TAX_TYPE_REC.tax_amt,
ln_inv_curr_precision),
pn_customer_trx_line_id => TAX_TYPE_REC.LINE_ID,
pn_customer_trx_id => v_customer_trx_id,
pn_set_of_books_id => v_books_id,
pn_link_to_cust_trx_line_id => v_customer_trx_line_id,
pv_line_type => v_line_type,
pv_uom_code => TAX_TYPE_REC.uom,
pn_vat_tax_id => ln_tax_rate_id,
pn_acctd_amount => v_converted_rate *
TAX_TYPE_REC.tax_amt,
pn_amount => round(TAX_TYPE_REC.tax_amt,
ln_inv_curr_precision),
pn_CODE_COMBINATION_ID => v_ccid,
pn_cust_trx_line_sales_rep_id => v_salesrep_id,
pv_insert_update_flag => 'U',
pd_last_update_date => pr_new.last_update_date,
pn_last_updated_by => pr_new.last_updated_by,
pd_creation_date => pr_new.creation_date,
pn_created_by => pr_new.created_by,
pn_last_update_login => pr_new.last_update_login,
pn_tax_rate => TAX_TYPE_REC.tax_rate,
pv_error_flag => 'P',
pv_source => v_created_from,
pn_org_id => v_org_id,
pn_line_number => tax_type_rec.tax_line_no);
UPDATE JAI_AR_TRX_INS_LINES_T
SET extended_amount = TAX_TYPE_REC.tax_amt,
set_of_books_id = v_books_id,
line_type = v_line_type,
uom_code = TAX_TYPE_REC.uom,
acctd_amount = v_converted_rate *
TAX_TYPE_REC.tax_amt,
amount = TAX_TYPE_REC.tax_amt,
insert_update_flag = 'U',
tax_rate = TAX_TYPE_REC.tax_rate
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = TAX_TYPE_REC.line_id;
SELECT max(jattl.tax_line_no)
INTO l_tcs_sur_line_num
FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
WHERE jattl.link_to_cust_trx_line_id =
pr_new.customer_trx_line_id
AND jattl.tax_id = jcta.tax_id
AND jcta.tax_type = jai_constants.tax_type_tcs_surcharge
GROUP BY jcta.tax_type;
SELECT count(jattl.tax_line_no)
INTO l_tot_tax_lines
FROM JAI_AR_TRX_TAX_LINES jattl
WHERE jattl.link_to_cust_trx_line_id =
pr_new.customer_trx_line_id;
SELECT trx_date
INTO c_conversion_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
SELECT max(jattl.tax_line_no)
INTO l_tcs_line_num
FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
WHERE jattl.link_to_cust_trx_line_id =
pr_new.customer_trx_line_id
AND jattl.tax_id = jcta.tax_id
AND jcta.tax_type = jai_constants.tax_type_tcs
GROUP BY jcta.tax_type;
SELECT organization_id
into ln_organization_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
select max(jattl.tax_line_no)
into l_max_tax_line_no
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id =
pr_new.customer_trx_line_id;
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login => FND_GLOBAL.login_id,
p_operation_flag => NULL,
p_vat_assessable_value => pr_new.vat_assessable_value,
p_thhold_cat_base_tax_typ => 'TCS',
p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
p_source_trx_type => null,
p_source_table_name => null,
p_action => 'DEFAULT_TAXES',
p_max_tax_line => l_max_tax_line_no,
p_max_rgm_tax_line => l_tcs_line_num);
SELECT created_from
FROM ra_customer_trx_all
WHERE customer_trx_id = ln_customer_trx_id;
PROCEDURE INSERT_TAX_LINES_TEMP(pn_extended_amount IN NUMBER,
pn_customer_trx_line_id IN NUMBER,
pn_customer_trx_id IN NUMBER,
pn_set_of_books_id IN NUMBER,
pn_link_to_cust_trx_line_id IN NUMBER,
pv_line_type IN VARCHAR2,
pv_uom_code IN VARCHAR2,
pn_vat_tax_id IN NUMBER,
pn_acctd_amount IN NUMBER,
pn_amount IN NUMBER,
pn_code_combination_id IN NUMBER,
pn_cust_trx_line_sales_rep_id IN NUMBER,
pv_insert_update_flag IN VARCHAR2,
pd_last_update_date IN DATE,
pn_last_updated_by IN NUMBER,
pd_creation_date IN DATE,
pn_created_by IN NUMBER,
pn_last_update_login IN NUMBER,
pn_tax_rate IN NUMBER,
pv_error_flag IN VARCHAR2,
pv_source IN VARCHAR2,
pn_org_id IN NUMBER,
pn_line_number IN NUMBER) IS
lv_api_name CONSTANT VARCHAR2(100) := 'INSERT_TAX_LINES_TEMP';
';pv_insert_update_flag = ' || pv_insert_update_flag ||
';pd_last_update_date = ' || pd_last_update_date ||
';pn_last_updated_by = ' || pn_last_updated_by ||
';pn_last_update_login = ' || pn_last_update_login ||
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME || lv_api_name,'JAI_AR_TAX_LINES_PKG.INSERT_TAX_LINES_TEMP.BEGIN');
INSERT INTO JAI_AR_TRX_INS_LINES_T
(extended_amount,
customer_trx_line_id,
customer_trx_id,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
uom_code,
vat_tax_id,
acctd_amount,
amount,
CODE_COMBINATION_ID,
cust_trx_line_sales_rep_id,
insert_update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
tax_rate,
error_flag,
source,
org_id,
line_number)
VALUES
(pn_extended_amount,
pn_customer_trx_line_id,
pn_customer_trx_id,
pn_set_of_books_id,
pn_link_to_cust_trx_line_id,
pv_line_type,
pv_uom_code,
pn_vat_tax_id,
pn_acctd_amount,
pn_amount,
pn_code_combination_id,
pn_cust_trx_line_sales_rep_id,
pv_insert_update_flag,
pd_last_update_date,
pn_last_updated_by,
pd_creation_date,
pn_created_by,
pn_last_update_login,
pn_tax_rate,
pv_error_flag,
pv_source,
pn_org_id,
pn_line_number);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME || lv_api_name,'1 record inserted into table JAI_AR_TRX_INS_LINES_T.');
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME || lv_api_name,'JAI_AR_TAX_LINES_PKG.INSERT_TAX_LINES_TEMP.END');
substr('Error inserting table JAI_AR_TRX_INS_LINES_T. ' ||
sqlerrm,1,4000));
END INSERT_TAX_LINES_TEMP;