[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ctl.customer_trx_line_id customer_trx_line_id,
NVL(ctl_line.line_number,
ctl.line_number) line_number,
DECODE(ctl_line.customer_trx_line_id,
NULL, TO_NUMBER(NULL),
ctl.line_number) other_line_number,
ctl.extended_amount line_amount,
SUM(lgd.amount) dist_amount,
MAX(lgd.account_class) account_class,
DECODE(p_invoicing_rule_id,
NULL,
-- no rules case
DECODE( MAX(lgd.cust_trx_line_gl_dist_id),
NULL, DECODE( ctl.line_type,
'LINE', 'AR_TW_NO_LINE_DISTS',
'CHARGES', 'AR_TW_NO_CHARGES_DISTS',
'TAX', 'AR_TW_NO_TAX_DISTS',
'FREIGHT', 'AR_TW_NO_FREIGHT_DISTS',
'AR_TW_NO_REC_DIST'),
DECODE( MIN(lgd.code_combination_id),
-1, DECODE( ctl.line_type,
'LINE', 'AR_TW_BAD_LINE_DISTS',
'CHARGES', 'AR_TW_BAD_CHARGES_DISTS',
'TAX', 'AR_TW_BAD_TAX_DISTS',
'FREIGHT', 'AR_TW_BAD_FREIGHT_DISTS',
'AR_TW_BAD_REC_DIST'),
DECODE( ctl.extended_amount,
SUM(lgd.amount), NULL,
DECODE( ctl.line_type,
'LINE', 'AR_TW_LINE_DIST_AMT',
'CHARGES', 'AR_TW_CHARGES_DIST_AMT',
'TAX', 'AR_TW_TAX_DIST_AMT',
'FREIGHT', 'AR_TW_FREIGHT_DIST_AMT')
)
)
),
-- rules case
DECODE( MAX(lgd.cust_trx_line_gl_dist_id),
NULL,
-- Bug 2137682: changed the MAX(lgd.account_class) to MAX(ctl.line_type)
DECODE( MAX(ctl.line_type),
'REV', 'AR_TW_NO_REVENUE_SETS',
'SUSPENSE', 'AR_TW_NO_SUSPENSE_SETS',
'UNEARN', 'AR_TW_NO_UNEARN_SETS',
'UNBILL', 'AR_TW_NO_UNBILL_SETS',
'CHARGES', 'AR_TW_NO_CHARGES_SETS',
'TAX', 'AR_TW_NO_TAX_SETS',
'FREIGHT', 'AR_TW_NO_FREIGHT_SETS',
'AR_TW_NO_REC_SETS'),
DECODE( MIN(lgd.code_combination_id),
-1, DECODE( MAX(lgd.account_class),
'REV', 'AR_TW_BAD_REVENUE_SETS',
'SUSPENSE','AR_TW_BAD_SUSPENSE_SETS',
'UNEARN', 'AR_TW_BAD_UNEARN_SETS',
'UNBILL', 'AR_TW_BAD_UNBILL_SETS',
'CHARGES', 'AR_TW_BAD_CHARGES_SETS',
'TAX', 'AR_TW_BAD_TAX_SETS',
'FREIGHT', 'AR_TW_BAD_FREIGHT_SETS',
'AR_TW_BAD_REC_SETS'),
DECODE( SUM(lgd.percent),
100, NULL,
DECODE( MAX(lgd.account_class),
'REV', 'AR_TW_REVENUE_SETS_PCT',
'SUSPENSE','AR_TW_SUSPENSE_SETS_PCT',
'UNEARN', 'AR_TW_UNEARN_SETS_PCT',
'UNBILL', 'AR_TW_UNBILL_SETS_PCT',
'CHARGES', 'AR_TW_CHARGES_SETS_PCT',
'TAX', 'AR_TW_TAX_SETS_PCT',
'FREIGHT','AR_TW_FREIGHT_SETS_PCT')
)
)
)
) message_name
FROM ra_customer_trx_lines ctl_line,
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist lgd,
ra_customer_trx ct
WHERE ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ctl.customer_trx_id(+)
AND ctl.customer_trx_line_id = lgd.customer_trx_line_id(+)
AND ctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+)
AND DECODE(p_invoicing_rule_id,
NULL, 'N',
'Y') = lgd.account_set_flag(+)
GROUP BY ctl.customer_trx_line_id,
ctl.line_number,
ctl_line.line_number,
ctl_line.customer_trx_line_id,
ctl.extended_amount,
ctl.line_type,
DECODE(p_invoicing_rule_id,
NULL, NULL,
lgd.account_class)
HAVING (
MAX(lgd.cust_trx_line_gl_dist_id) IS NULL
OR
MIN(lgd.code_combination_id) < 0
OR (
p_invoicing_rule_id IS NULL
AND ctl.extended_amount <> SUM(lgd.amount)
)
OR (
(
p_invoicing_rule_id IS NOT NULL
OR MAX(lgd.account_class) = 'REC'
)
AND SUM(lgd.percent) <> 100
)
)
UNION
SELECT -- Receivables case
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
lgd.amount,
'REC',
DECODE(p_invoicing_rule_id,
NULL,
-- no rules case
DECODE( lgd.cust_trx_line_gl_dist_id,
NULL, 'AR_TW_NO_REC_DISTS',
'AR_TW_BAD_REC_DISTS'),
-- rules case
DECODE( lgd.cust_trx_line_gl_dist_id,
NULL, 'AR_TW_NO_REC_SETS',
'AR_TW_BAD_REC_SETS')
) message_name
FROM ra_cust_trx_line_gl_dist lgd
WHERE lgd.customer_trx_id = p_customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y'
AND (
NVL(lgd.code_combination_id, -1) < 0
OR lgd.percent <> 100
)
ORDER BY 1;
'SELECT ct.previous_customer_trx_id previous_customer_trx_id,
ct.trx_number trx_number,
ct.invoicing_rule_id invoicing_rule_id,
ctt.type class,
ctt.tax_calculation_flag tax_calculation_flag,
ct.customer_trx_id customer_trx_id
FROM ra_customer_trx ct,
ra_cust_trx_types ctt
WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id IN (' || p_query_string || ')';
SELECT l.line_number line_number,
l.customer_trx_line_id customer_trx_line_id,
round(SUM(s.revenue_percent_split),4) error_amount
FROM ra_customer_trx_lines l,
ra_cust_trx_line_salesreps s
WHERE pg_salesrep_required_flag = 'Y'
AND l.customer_trx_id = p_customer_trx_id
AND l.customer_trx_line_id = s.customer_trx_line_id(+)
AND l.line_type = 'LINE'
GROUP BY l.line_number,
l.customer_trx_line_id
HAVING round(SUM(NVL(s.revenue_percent_split, 0)),4) <> 100
UNION
SELECT l.line_number line_number,
l.customer_trx_line_id customer_trx_line_id,
round(SUM(s.revenue_percent_split),4) error_amount
FROM ra_customer_trx_lines l,
ra_cust_trx_line_salesreps s
WHERE pg_salesrep_required_flag = 'N'
AND l.customer_trx_id = p_customer_trx_id
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.line_type = 'LINE'
GROUP BY l.line_number,
l.customer_trx_line_id
HAVING round(SUM(NVL(s.revenue_percent_split, 0)),4) <> 100
AND SUM(s.revenue_percent_split) IS NOT NULL
ORDER BY 1,3,2;
SELECT l.line_number line_number
FROM ra_customer_trx_lines l
WHERE l.customer_trx_id = p_customer_trx_id
AND (
l.accounting_rule_id IS NULL
OR
l.rule_start_date IS NULL
)
AND l.line_type = 'LINE'
ORDER BY l.line_number;
SELECT DISTINCT code_combination_id , gl_date, account_class,
account_set_flag
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id;
SELECT type,
creation_sign,
tax_calculation_flag
INTO l_class,
l_creation_sign,
l_tax_calculation_flag
FROM ra_cust_trx_types
WHERE cust_trx_type_id = l_trx_rec.cust_trx_type_id;
SELECT lgd_trx.gl_date,
lgd_trx.amount
INTO l_trx_gl_date,
l_trx_amount
FROM ra_cust_trx_line_gl_dist lgd_trx
WHERE lgd_trx.customer_trx_id = l_trx_rec.customer_trx_id
AND lgd_trx.latest_rec_flag = 'Y'
AND lgd_trx.account_class = 'REC';
SELECT lgd_trx.gl_date,
lgd_trx.amount
INTO l_commit_gl_date,
l_commit_amount
FROM ra_cust_trx_line_gl_dist lgd_trx
WHERE lgd_trx.customer_trx_id = l_trx_rec.initial_customer_trx_id
AND lgd_trx.latest_rec_flag = 'Y'
AND lgd_trx.account_class = 'REC';
SELECT COUNT(*)
INTO l_result
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id;
SELECT count(*)
INTO l_result
FROM ra_terms tm,
ra_customer_trx_lines cl,
ra_customer_trx cs,
zx_lines zl,
zx_formula_b zf
WHERE cs.customer_trx_id = p_customer_trx_id
AND cs.term_id = tm.term_id
AND tm.calc_discount_on_lines_flag <> 'L'
AND cl.customer_trx_id = cs.customer_trx_id
AND cl.line_type = 'TAX'
AND cl.tax_line_id = zl.tax_line_id
AND zl.taxable_basis_formula = zf.formula_code
AND zl.tax_determine_date between zf.effective_from and nvl(zf.effective_to, zl.trx_date)
AND zf.formula_type_code = 'TAXABLE_BASIS'
AND zf.cash_discount_appl_flag = 'Y';
SELECT decode( max(d.customer_trx_id),
null, 'N',
'Y')
INTO l_rule_flag
FROM ra_customer_trx trx,
ra_cust_trx_line_gl_dist d
WHERE trx.customer_trx_id = l_trx_rec.customer_trx_id
and trx.previous_customer_trx_id = d.customer_trx_id
and d.account_class in ('UNEARN', 'UNBILL');
SELECT lgd_trx.gl_date
INTO l_prev_gl_date
FROM ra_cust_trx_line_gl_dist lgd_trx
WHERE lgd_trx.customer_trx_id = l_trx_rec.previous_customer_trx_id
AND lgd_trx.latest_rec_flag = 'Y'
AND lgd_trx.account_class = 'REC';
SELECT allow_overapplication_flag,
natural_application_only_flag,
accounting_affect_flag,
type
INTO l_allow_overapplication_flag,
l_natural_app_only_flag,
l_open_receivables_flag,
l_credited_class
FROM ra_cust_trx_types
WHERE cust_trx_type_id = l_prev_trx_rec.cust_trx_type_id;
SELECT SUM(
DECODE( ctl.line_type,
'LINE', ctl.extended_amount,
'CHARGES', ctl.extended_amount,
0 )
),
SUM(
DECODE( ctl.line_type,
'TAX', ctl.extended_amount,
0 )
),
SUM(
DECODE( ctl.line_type,
'FREIGHT', ctl.extended_amount,
0 )
)
INTO l_line_amount,
l_tax_amount,
l_freight_amount
FROM ra_customer_trx_lines ctl
WHERE customer_trx_id = l_trx_rec.customer_trx_id;
select nvl(sum(amount),0),nvl(sum(line_adjusted),0),nvl(sum(tax_adjusted),0),nvl(sum(freight_adjusted),0)
into l_commit_adj_amount,l_commit_line_amount,l_commit_tax_amount,l_commit_frt_amount
from ar_adjustments
where customer_trx_id = l_trx_rec.previous_customer_trx_id
and receivables_trx_id = -1;
SELECT MAX( other_ct.customer_trx_id )
INTO l_result
FROM ra_customer_trx other_ct,
ra_customer_trx this_ct
WHERE this_ct.customer_trx_id = p_customer_trx_id
AND other_ct.previous_customer_trx_id =
this_ct.previous_customer_trx_id
AND other_ct.customer_trx_id <> this_ct.customer_trx_id
/*3606541*/
AND other_ct.creation_date > this_ct.creation_date
AND NVL(other_ct.complete_flag,'N')='Y';
select decode(account_set_flag,'Y','N','N','Y','Y')
into l_revrec_complete
from ra_cust_trx_line_gl_dist
where customer_trx_id = p_customer_trx_id
and account_class = 'REC'
and latest_rec_flag = 'Y';
SELECT amount INTO
l_original_amount
FROM
AR_CASH_RECEIPTS WHERE cash_receipt_id=p_reversed_cash_receipt_id;
SELECT sum(decode(ctl.line_type, 'LINE', ctl.extended_amount,
'CB', ctl.extended_amount, 0)),
sum(decode(ctl.line_type, 'TAX', ctl.extended_amount, 0)),
sum(decode(ctl.line_type, 'FREIGHT', ctl.extended_amount, 0))
INTO
line_amount,
tax_amount,
frt_amount
FROM
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_customer_trx_id;