[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bs.name source,
bs.batch_source_id batch_source_id,
bs.auto_trx_numbering_flag auto_trx_numbering_flag,
bs.batch_source_type batch_source_type,
bs.default_inv_trx_type default_inv_trx_type,
bs.copy_doc_number_flag copy_doc_number_flag
INTO p_default_batch_source_name,
p_default_batch_source_id,
p_auto_trx_numbering_flag,
p_batch_source_type,
p_default_cust_trx_type_id,
p_copy_doc_number_flag
FROM ra_cust_trx_types ctt,
ra_batch_sources bs
WHERE bs.batch_source_id = p_batch_source_id
AND bs.default_inv_trx_type = ctt.cust_trx_type_id(+)
AND NVL(p_ctt_class, '-99') =
DECODE(p_ctt_class,
null, '-99',
ctt.type(+) )
AND NVL(p_trx_date,
TRUNC(sysdate))
BETWEEN NVL(bs.start_date,
nvl(p_trx_date, TRUNC(sysdate)))
AND NVL(bs.end_date, NVL(p_trx_date, TRUNC(sysdate)))
AND NVL(p_trx_date,
TRUNC(sysdate))
BETWEEN NVL(ctt.start_date(+),
NVL(p_trx_date, trunc(sysdate)))
AND NVL(ctt.end_date(+), nvl(p_trx_date, trunc(sysdate)))
AND (
bs.batch_source_type ='INV'
OR p_ctt_class = 'CM'
)
/* do not show 'DM Reversal' and 'Chargeback' */
AND bs.batch_source_id not in (11, 12)
AND (
p_trx_number IS NULL
OR bs.auto_trx_numbering_flag = 'N'
);
SELECT ctt.cust_trx_type_id cust_trx_type_id,
ctt.name name,
ctt.type class,
ctt.accounting_affect_flag open_receivable_flag,
ctt.post_to_gl post_to_gl_flag,
ctt.allow_freight_flag allow_freight_flag,
ctt.creation_sign creation_sign,
allow_overapplication_flag allow_overapplication_flag,
natural_application_only_flag natural_application_only_flag,
ctt.tax_calculation_flag tax_calculation_flag,
ctt.default_status default_status_code,
arl_status.meaning default_status,
ctt.default_printing_option default_printing_option_code,
arl_print.meaning default_printing_option,
ctt.default_term default_term_id,
rat.name default_term
INTO
p_default_cust_trx_type_id,
p_default_type_name,
p_default_class,
p_deflt_open_receivables_flag,
p_default_post_to_gl_flag,
p_default_allow_freight_flag,
p_default_creation_sign,
p_default_allow_overapp_flag,
p_deflt_natural_app_only_flag,
p_default_tax_calculation_flag,
p_default_status_code,
p_default_status,
p_default_printing_option_code,
p_default_printing_option,
l_default_term_id,
p_default_term_name
FROM ar_lookups arl_print,
ar_lookups arl_status,
ra_terms rat,
ra_cust_trx_types ctt
WHERE ctt.cust_trx_type_id = p_cust_trx_type_id
AND 'INVOICE_PRINT_OPTIONS' = arl_print.lookup_type(+)
AND ctt.default_printing_option = arl_print.lookup_code(+)
AND 'INVOICE_TRX_STATUS' = arl_status.lookup_type(+)
AND ctt.default_status = arl_status.lookup_code(+)
AND ctt.default_term = rat.term_id(+)
AND 'Y' = arl_print.enabled_flag(+)
AND 'Y' = arl_status.enabled_flag(+)
AND -- Check effectivity dates
NVL(p_trx_date, TRUNC(SYSDATE) )
BETWEEN start_date
AND NVL(end_date, NVL(p_trx_date, TRUNC(SYSDATE) ) )
AND NVL(p_trx_date, TRUNC(SYSDATE))
BETWEEN rat.start_date_active(+)
AND NVL(rat.end_date_active(+),
NVL( p_trx_date, TRUNC(SYSDATE) ) )
AND NVL(p_trx_date, TRUNC(SYSDATE))
BETWEEN arl_print.start_date_active(+)
AND NVL(arl_print.end_date_active(+),
NVL( p_trx_date, TRUNC(SYSDATE) ) )
AND NVL(p_trx_date, TRUNC(SYSDATE))
BETWEEN arl_status.start_date_active(+)
AND NVL(arl_status.end_date_active(+),
NVL( p_trx_date, TRUNC(SYSDATE) ) )
AND -- The transaction must have the same class as is specified
-- in the form.
-- However, existing Debit Memos may be converted into Invoices
-- and Invoices may be converted to Debit Memos
(
NVL(p_ctt_class, ctt.type) = ctt.type
OR
(
p_ctt_class IN ('DM', 'INV')
AND
p_row_id IS NOT NULL
AND
ctt.type IN ('DM', 'INV')
)
)
AND -- Only invoices and credit memos can have rules
(
p_invoicing_rule_id IS NULL
OR
ctt.type IN ('INV', 'CM')
)
AND -- Commitments must be Open Receivable Yes
(
ctt.type NOT IN ('DEP', 'GUAR')
OR
ctt.accounting_affect_flag = 'Y'
)
AND -- Don't allow changes to the Post To GL flag after
-- the Revenue Recognition Program has been run on
-- this transaction
ctt.post_to_gl = DECODE(p_rev_recog_run_flag,
'Y', p_rev_recog_run_flag,
ctt.post_to_gl )
AND -- Don't allow changes to the Open Receivables Flag for
-- complete credit memos
(
p_complete_flag = 'N'
OR
p_ctt_class <> 'CM'
OR
(
ctt.accounting_affect_flag = p_open_receivables_flag
)
)
AND -- Check allow freight constraint and
-- prevent transactions with charges from being changed
-- into transactions that do not allow charges.
NOT EXISTS ( SELECT 'violates allow freight'
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_customer_trx_id
AND (
(
ctt.allow_freight_flag = 'N'
AND ctl.line_type = 'FREIGHT'
)
OR
(
ctl.line_type = 'CHARGES'
AND ctt.type NOT IN ('DM', 'CM')
)
)
)
AND -- Check creation sign constraint
NOT EXISTS (
SELECT 'VIOLATES CREATION SIGN'
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_customer_trx_id
GROUP BY ctt.creation_sign
HAVING DECODE(
SIGN( SUM(ctl.extended_amount) ),
1, DECODE( ctt.creation_sign,
'P', 'Y',
'A', 'Y',
'N' ),
-1, DECODE( ctt.creation_sign,
'N', 'Y',
'A', 'Y',
'N' ),
'Y'
) = 'N'
)
-- Check the functional security rules for transaction entry
AND (
p_security_inv_enter_flag = 'Y'
OR ctt.type <> 'INV'
)
AND (
p_security_cm_enter_flag = 'Y'
OR ctt.type <> 'CM'
)
AND (
p_security_dm_enter_flag = 'Y'
OR ctt.type <> 'DM'
)
AND (
p_security_commit_enter_flag = 'Y'
OR ctt.type NOT IN ('DEP', 'GUAR')
);
SELECT COUNT(*),
arpt_sql_func_util.get_First_Due_Date(l_default_term_id,
p_trx_date)
INTO l_number_of_due_dates,
l_term_due_date
FROM ra_terms_lines
WHERE term_id = l_default_term_id;