[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl( max(line_number), 0 ) + 1
INTO p_line_number
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id = p_customer_trx_line_id
AND line_type = 'TAX';
SELECT msi.description,
DECODE( SIGN( p_trx_date - TRUNC( NVL(muom.disable_date,
p_trx_date) ) ),
-1, muom.uom_code,
0, muom.uom_code,
1, null
),
DECODE( SIGN( p_trx_date - TRUNC( NVL(muom.disable_date,
p_trx_date) ) ),
-1, muom.unit_of_measure,
0, muom.unit_of_measure,
1, null
),
DECODE( rr.status,
'A', msi.accounting_rule_id,
null ),
DECODE( rr.status,
'A', rr.name,
null ),
DECODE( rr.status,
'A', DECODE(rr.type,
'ACC_DUR', 1,
'A', rr.occurrences )
),
DECODE( rr.status,
'A', rr.type,
null ),
DECODE( rr.status,
'A', DECODE( rr.frequency,
'SPECIFIC', min(rs.rule_date),
DECODE( p_invoicing_rule_id,
-2, p_trx_date,
-3, sysdate )
),
null
)
INTO p_description,
p_primary_uom_code,
p_primary_uom_name,
p_accounting_rule_id,
p_accounting_rule_name,
p_accounting_rule_duration,
p_accounting_rule_type,
p_rule_start_date
FROM mtl_system_items msi,
mtl_units_of_measure muom,
ra_rules rr,
ra_rule_schedules rs
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id
AND msi.primary_uom_code = muom.uom_code (+)
AND msi.accounting_rule_id = rr.rule_id (+)
AND rr.rule_id = rs.rule_id (+)
GROUP BY msi.description,
muom.disable_date,
muom.uom_code,
muom.unit_of_measure,
rr.status,
msi.accounting_rule_id,
rr.name,
rr.type,
rr.occurrences,
rr.frequency;
| select_summary |
| |
| DESCRIPTION |
| Returns the sum of the extended amount. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| OUT: |
| p_total |
| p_total_rtot_db |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 03-JAN-96 Sunil Mody Created |
| |
+===========================================================================*/
PROCEDURE select_summary(p_customer_trx_id IN
ra_customer_trx_lines.customer_trx_id%type,
p_customer_trx_line_id IN number,
p_mode IN varchar2,
p_total IN OUT NOCOPY
ra_customer_trx_lines.extended_amount%type,
p_total_rtot_db IN OUT NOCOPY
ra_customer_trx_lines.extended_amount%type)
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('arp_trx_tax_util.select_summary()+');
SELECT nvl(sum(extended_amount),0), nvl(sum(extended_amount),0)
INTO p_total, p_total_rtot_db
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND NVL( link_to_cust_trx_line_id, -10 ) =
DECODE(p_mode,
'LINE', p_customer_trx_line_id,
'ALL', link_to_cust_trx_line_id,
-10 )
AND line_type = 'TAX';
arp_util.debug('arp_trx_tax_util.select_summary()-');
arp_util.debug('EXCEPTION: arp_trx_tax_util.select_summary()');
arp_util.debug('select_summary: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
END select_summary;
| check_last_line_on_delete |
| |
| DESCRIPTION |
| Returns whether this is the only tax line for the customer_trx_line_id |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_line_id |
| OUT: |
| p_only_tax_line |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-MAR-96 Vikas Mahajan Created |
| |
+===========================================================================*/
PROCEDURE check_last_line_on_delete(p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type
,
p_only_tax_line_flag OUT NOCOPY BOOLEAN)
IS
l_only_tax_line_flag varchar2(2);
arp_util.debug('arp_trx_tax_util.check_last_line_on_delete()+');
SELECT decode(max(dummy),
'', 'N',
'Y')
INTO l_only_tax_line_flag
FROM dual
WHERE EXISTS
(SELECT 'deleted last tax line'
FROM ra_customer_trx_lines
WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
AND line_type = 'TAX'
having count(*) = 1
) ;
arp_util.debug('arp_trx_tax_util.check_last_line_on_delete()-');
arp_util.debug('EXCEPTION: arp_trx_tax_util.check_last_line_on_delete()');
SELECT decode(max(dummy),
'', 'N',
'Y')
INTO l_unique_line_flag
FROM dual
WHERE EXISTS
(SELECT 'unique tax line'
FROM ra_customer_trx_lines
WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
AND line_type = 'TAX'
AND line_number = p_customer_trx_line_num
) ;
SELECT nvl(sum(extended_amount),0)
INTO l_balance_due
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct
WHERE previous_customer_trx_line_id = p_prev_cust_trx_line_id
AND customer_trx_line_id <> p_cust_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.complete_flag = 'Y';
SELECT validate_flag
INTO l_adhoc_tax_flag
FROM ar_vat_tax
WHERE tax_code = p_tax_code;