[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'INSERT INTO ra_interface_errors
(
interface_line_id,
message_text,
invalid_value,
org_id
)
SELECT l.interface_line_id,
:all_pds_error_msg,
l.rule_start_date,
l.org_id
FROM ra_interface_lines_gt l
WHERE l.request_id = :request_id
AND nvl(l.interface_status,
''~'') <> ''P''
AND l.customer_trx_id IS NOT NULL
AND l.invoicing_rule_id IS NOT NULL
AND l.rule_start_date IS NOT NULL
AND l.link_to_line_id IS NULL
AND (
NOT EXISTS
(
';
' SELECT 1
FROM gl_periods p1, /* to get the first period */
gl_periods p2, /* to get the last period */
gl_sets_of_books b,
gl_period_types t,
ra_rules r
WHERE r.rule_id = ' || l_temp_rule || '
AND b.set_of_books_id = ' || l_temp_sob || '
AND ' || l_temp_rule_start_date || ' BETWEEN p1.start_date
AND p1.end_date
AND r.frequency <> ''SPECIFIC''
AND p1.period_set_name = b.period_set_name
AND p1.adjustment_period_flag = ''N''
AND p2.adjustment_period_flag = ''N''
AND p1.period_type = r.frequency
AND t.period_type = p1.period_type
AND p2.period_set_name = b.period_set_name
AND p2.period_type = p1.period_type
AND ( p2.end_date,
( TO_CHAR(p2.period_year,999999)||''-''||
TO_CHAR(p2.period_num,999999)||''-''||
TO_CHAR( DECODE(r.type, ''ACC_DUR'',
' || l_temp_duration ||',
''PP_DR_ALL'',
' || l_temp_duration ||',
''PP_DR_PP'',
' || l_temp_duration ||',
r.occurrences),
9999)
)
) =
( SELECT MAX(p9.end_date),
MAX( TO_CHAR(p9.period_year,999999)||''-''||
TO_CHAR(p9.period_num,999999)||''-''||
TO_CHAR(rownum,9999)
)
FROM gl_periods p9
WHERE p9.period_set_name = p1.period_set_name
AND p9.period_type = p1.period_type
AND p9.start_date >= p1.start_date
AND p9.adjustment_period_flag = ''N''
AND rownum <= ( DECODE( r.type, ''ACC_DUR'',
' || l_temp_duration ||',
''PP_DR_ALL'',
' || l_temp_duration ||',
''PP_DR_PP'',
' || l_temp_duration ||',
r.occurrences)
)
)
AND DECODE( r.type, ''ACC_DUR'',
' || l_temp_duration ||',
''PP_DR_ALL'',
' || l_temp_duration ||',
''PP_DR_PP'',
' || l_temp_duration ||',
r.occurrences) =
( SELECT COUNT(p3.period_set_name)
FROM gl_periods p3
WHERE p3.period_set_name = b.period_set_name
AND p3.period_type = p1.period_type
AND p3.adjustment_period_flag = ''N''
AND p3.start_date >= p1.start_date
AND p3.start_date <= p2.start_date
) ' ||
l_temp_bind || '
SELECT DECODE(
COUNT(*),
0, 2,
DECODE(
SUM( r.occurrences ) / COUNT(*),
COUNT(*), 1,
2
)
)
FROM gl_periods p,
gl_sets_of_books b,
ra_rules r,
ra_rule_schedules rl
WHERE r.rule_id =' || l_temp_rule || '
AND r.frequency =''SPECIFIC''
AND rl.rule_id = r.rule_id
AND b.set_of_books_id = ' || l_temp_sob || '
AND p.period_set_name = b.period_set_name
AND p.period_type = b.accounted_period_type
AND p.adjustment_period_flag = ''N''
AND rl.rule_date BETWEEN p.start_date AND p.end_date
GROUP BY r.frequency ' ||
l_temp_end;
'INSERT INTO ra_interface_errors
(
interface_line_id,
message_text,
invalid_value,
org_id
)
SELECT l.interface_line_id,
:overlapping_pds_error_msg,
l.rule_start_date,
l.org_id
FROM ra_interface_lines_gt l
WHERE l.request_id = :request_id
AND nvl(
l.interface_status,
''~''
) <> ''P''
AND l.invoicing_rule_id IS NOT NULL
AND l.rule_start_date IS NOT NULL
AND l.customer_trx_id IS NOT NULL
AND l.link_to_line_id IS NULL
AND EXISTS
(
';
' SELECT 1
FROM gl_periods p1,
gl_periods p2,
gl_sets_of_books b,
ra_rules r
WHERE r.rule_id = ' || l_temp_rule || '
AND b.set_of_books_id = ' || l_temp_sob || '
AND p1.period_set_name = b.period_set_name
AND p1.period_type = DECODE(
r.frequency,
''SPECIFIC'', b.accounted_period_type,
r.frequency
)
AND p2.period_set_name = b.period_set_name
AND p2.period_type = p1.period_type
AND p1.adjustment_period_flag = ''N''
AND p2.adjustment_period_flag = ''N''
AND p2.start_date <= p1.end_date
AND p2.end_date >= p1.end_date
/* don''t check period with itself */
AND p2.period_set_name || p2.period_name <>
p1.period_set_name || p1.period_name
' || l_temp_end2;
SELECT MIN(rl.rule_start_date)
INTO p_candidate_date
FROM ra_customer_trx_lines rl
WHERE rl.customer_trx_id = p_customer_trx_id
AND line_type = 'LINE';
SELECT COUNT(gp1.period_set_name)
INTO actual_num_fiscal_year
FROM ra_customer_trx_lines ctl,
gl_sets_of_books b,
gl_periods gp1
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.set_of_books_id = b.set_of_books_id
AND gp1.period_set_name = b.period_set_name
AND gp1.period_type = b.accounted_period_type
AND gp1.period_year = to_number(to_char(ctl.rule_start_date,'YYYY'))
AND gp1.adjustment_period_flag ='N' ;
SELECT MAX(
DECODE(
r.frequency,
'SPECIFIC', MAX(rl.rule_date),
LEAST(
ctl.rule_start_date -
gp1.start_date +
gp2.start_date,
gp2.end_date,
nvl(ctl.rule_end_date,gp2.end_date) --Bug5022614
)
)
)
INTO p_candidate_date
FROM ra_customer_trx_lines ctl,
gl_periods gp1,
gl_periods gp2,
gl_sets_of_books b,
gl_period_types t,
ra_rules r,
ra_rule_schedules rl
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.accounting_rule_id = r.rule_id
AND rl.rule_id (+) = decode(r.frequency,
'SPECIFIC', r.rule_id,
-9.9)
AND ctl.set_of_books_id = b.set_of_books_id
AND ctl.set_of_books_id = pg_set_of_books_id
AND ctl.rule_start_date BETWEEN gp1.start_date
AND gp1.end_date
AND gp1.period_set_name = b.period_set_name
AND UPPER(gp1.period_type) =
UPPER(
DECODE(r.frequency,
'SPECIFIC', b.accounted_period_type,
r.frequency
)
)
AND t.period_type = gp1.period_type
AND gp2.period_set_name = gp1.period_set_name
AND gp2.period_type = gp1.period_type
AND gp2.period_year =
gp1.period_year +
TRUNC(
(
gp1.period_num -1 +
DECODE(
r.type,
'ACC_DUR', ctl.accounting_rule_duration,
'PP_DR_PP',ctl.accounting_rule_duration,
'PP_DR_ALL',ctl.accounting_rule_duration,
r.occurrences
) -1
) /
actual_num_fiscal_year
)
AND gp2.period_num =
MOD(
(
gp1.period_num -1 +
DECODE(
r.type,
'ACC_DUR', ctl.accounting_rule_duration,
'PP_DR_PP',ctl.accounting_rule_duration,
'PP_DR_ALL',ctl.accounting_rule_duration,
r.occurrences
) -1
),
actual_num_fiscal_year
) + 1
GROUP BY
ctl.customer_trx_id,
r.frequency,
rl.rule_date,
ctl.rule_start_date,
gp1.start_date,
gp2.start_date,
gp2.end_date,
ctl.rule_end_Date;
| selected. If the rule is of type Arrears Invoice then the Maximum |
| distributions GL dates for the transaction lines is selected. The |
| candidate date is then validated. If it succeeds, that date is used. |
| if the validation fails, the default gl date is obtaioned and used. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| arp_util.print_fcn_label |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| OUT: |
| p_gl_date |
| p_trx_date |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 31-JUL-95 Charlie Tomberg Created |
| 08-FEB-96 Martin Johnson Removed updates to database. |
| Removed set_names for displaying |
| new dates. |
| 29-MAY-96 Martin Johnson BugNo:368206. Fixed so that derived |
| trx_date gets returned for Arrears |
| case. |
| |
+===========================================================================*/
PROCEDURE derive_gl_trx_dates_from_rules (
p_customer_trx_id IN
ra_customer_trx.customer_trx_id%type,
p_gl_date IN OUT NOCOPY
ra_cust_trx_line_gl_dist.gl_date%type,
p_trx_date IN OUT NOCOPY
ra_customer_trx.trx_date%type,
p_recalculate_tax_flag IN OUT NOCOPY boolean,
P_created_from IN ar_trx_header_gt.created_from%type default NULL,
p_defaulted_gl_date_flag IN ar_trx_header_gt.defaulted_gl_date_flag%type default NULL
) IS
l_candidate_date date;
SELECT MIN(ct.trx_date),
MIN(ctlgd.gl_date),
MAX(ct.exchange_rate),
MAX(ct.invoice_currency_code),
MAX(ct.invoicing_rule_id)
INTO l_db_trx_date,
l_db_gl_date,
l_exchange_rate,
l_currency_code,
l_invoicing_rule_id
FROM ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd
WHERE ct.customer_trx_id = ctlgd.customer_trx_id
AND ct.customer_trx_id = p_customer_trx_id
AND ctlgd.account_class = 'REC'
AND ctlgd.latest_rec_flag = 'Y';