[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt := 'SELECT '
|| p_sequence_name
|| '.nextval seq_number '
|| 'FROM dual';
SELECT last_number - 1
FROM all_sequences
WHERE sequence_name = x_seq_name
AND sequence_owner = x_seq_owner;
SELECT
src.auto_trx_numbering_flag auto_trx_num_flag
, src.global_attribute3 inv_word
, src.global_attribute2 init_trx_num
, src.global_attribute4 fin_trx_num
, src.global_attribute5 last_trx_date
, TO_NUMBER(src.global_attribute6) adv_days
FROM ra_batch_sources src
WHERE src.batch_source_id = x_batch_source_id;
SELECT
ctt.type inv_class
-- R12 Changes : , ctt.global_attribute1 gui_type
, NULL gui_type
FROM ra_cust_trx_types ctt
WHERE ctt.cust_trx_type_id = p_cust_trx_type_id;
SELECT src.global_attribute1
FROM ra_batch_sources src
WHERE src.batch_source_id = x_batch_source_id;
SELECT src.global_attribute3
FROM ra_batch_sources src
WHERE src.batch_source_id = x_batch_source_id;
SELECT decode(src.global_attribute1
, NULL
, x_batch_source_id
, src.global_attribute1)
FROM ra_batch_sources src
WHERE src.batch_source_id = x_batch_source_id;
SELECT
DECODE(x_ini_or_fin
, 'INI'
, src.global_attribute2
, 'FIN'
, src.global_attribute4
, NULL)
FROM
ra_batch_sources src
WHERE
src.batch_source_id = x_batch_source_id;
SELECT fnd_date.chardate_to_date(src.global_attribute5)
FROM ra_batch_sources src
WHERE src.batch_source_id = x_gui_source_id;
SELECT src.global_attribute6
FROM ra_batch_sources src
WHERE src.batch_source_id = x_batch_source_id;
SELECT 'SUCCESS'
INTO l_dummy
FROM jg_zz_ar_src_trx_ty st
WHERE st.batch_source_id = l_batch_source_id
AND st.cust_trx_type_id = l_cust_trx_type_id
AND st.enable_flag = 'Y'; -- Bug 1865837
ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
IF NOT update_last_trx_date(
l_batch_source_id
, l_trx_date
, l_created_from)
THEN
app_exception.raise_exception;
ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
SELECT
COUNT(DISTINCT tax.tax_rate_code)
FROM
ar_trx_lines_gt l,
--
-- Bug 4756219
-- Changing reference to ar vat tax to zx_mco_rates
zx_mco_rates tax
WHERE
l.customer_trx_id = x_customer_trx_id
AND l.vat_tax_id = tax.tax_rate_id;
SELECT
COUNT(DISTINCT tl.vat_tax_id)
FROM
ra_customer_trx_lines tl
WHERE
tl.customer_trx_id = x_customer_trx_id
AND tl.line_type = 'TAX'; -- Bug 2753541
ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
FUNCTION update_last_trx_date(
p_batch_source_id IN NUMBER
, p_last_trx_date IN DATE
, p_created_from IN VARCHAR2) RETURN BOOLEAN IS
CURSOR c_last_issued_date(
x_batch_source_id NUMBER) IS
SELECT global_attribute5
FROM ra_batch_sources
WHERE batch_source_id = x_batch_source_id
FOR UPDATE OF global_attribute5 NOWAIT;
UPDATE ra_batch_sources
SET global_attribute5 = l_last_trx_date
WHERE CURRENT OF c_last_issued_date;
END update_last_trx_date;
UPDATE ar_trx_header_gt
SET global_attribute1 = l_gui_type
WHERE trx_header_id =
(select trx_header_id
from ar_trx_lines_gt
where trx_line_id = l_trx_line_id);
FUNCTION insert_interface_errors(
p_customer_trx_id IN NUMBER,
p_msg_name IN VARCHAR2,
p_invalid_value IN VARCHAR2,
p_validation_name IN VARCHAR2
)
RETURN BOOLEAN IS
CURSOR interface_line IS
SELECT header.trx_header_id,
lines.trx_line_id
FROM ar_trx_header_gt header,
ar_trx_lines_gt lines
WHERE lines.customer_trx_id = p_customer_trx_id
AND lines.trx_header_id = header.trx_header_id;
cannot_insert_error EXCEPTION;
raise cannot_insert_error;
WHEN cannot_insert_error THEN
IF pg_debug = 'Y' THEN
ar_invoice_utils.debug('-- Found exception in ja_th_invoice_create.' ||
p_validation_name);
ar_invoice_utils.debug('-- Cannot insert the error record into ' ||
'ar_trx_errors.');
'ja_th_invoice_create.insert_interface_errors.');
END insert_interface_errors;
IF insert_interface_errors(
p_customer_trx_id,
'JA_TH_AR_INVALID_TRX_DATE',
fnd_date.date_to_chardate(p_trx_date),
'validate_trx_date') THEN
return(0);
SELECT count(l.customer_trx_line_id)
INTO n
FROM ra_customer_trx_lines l,
-- Bug 4756219
-- Changing reference to ar vat tax to zx_mco_rates
zx_mco_rates v,
zx_accounts a
WHERE l.customer_trx_id = p_customer_trx_id
AND v.tax_rate_id = l.vat_tax_id
AND v.tax_rate_id = a.tax_account_entity_id
AND a.tax_account_entity_code = 'RATES'
AND a.interim_tax_ccid is NULL;
SELECT count(l.customer_trx_line_id)
INTO n
FROM ar_trx_lines_gt l,
-- Bug 4756219
-- Changing reference to ar vat tax to zx_mco_rates
zx_mco_rates v,
zx_accounts a
WHERE l.customer_trx_id = p_customer_trx_id
AND v.tax_rate_id = l.vat_tax_id
AND (v.tax_class = 'O' OR v.tax_class IS NULL)
AND v.active_flag='Y'
AND v.effective_from <= sysdate
AND (v.effective_to >= sysdate OR v.effective_to is NULL)
AND v.tax_rate_id = a.tax_account_entity_id
AND a.tax_account_entity_code = 'RATES'
AND a.interim_tax_ccid is NULL;
IF insert_interface_errors(
p_customer_trx_id,
'JA_TH_AR_INVALID_TAX_CODE',
NULL,
'validate_tax_code') THEN
return(0);
FUNCTION update_last_issued_date(
p_customer_trx_id IN NUMBER,
p_cust_trx_type_id IN NUMBER,
p_trx_date IN DATE,
p_created_from IN VARCHAR2
)
RETURN NUMBER IS
CURSOR last_issued_date IS
SELECT global_attribute2
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id
FOR UPDATE OF global_attribute2 NOWAIT;
UPDATE ra_cust_trx_types
SET global_attribute2 = fnd_date.date_to_canonical(p_trx_date)
WHERE CURRENT OF last_issued_date;
SELECT name
INTO trx_type_name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id;
IF insert_interface_errors(
p_customer_trx_id,
'JA_TH_AR_LAST_ISSD_DT_LOCKED',
fnd_date.date_to_chardate(p_trx_date),
'update_last_issued_date') THEN
return(0);
'ja_th_invoice_create.update_last_issued_date.');
END update_last_issued_date;