[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sb.period_set_name
INTO l_period_set_name
FROM ar_system_parameters sp,
gl_sets_of_books sb
WHERE sp.set_of_books_id = sb.set_of_books_id;
SELECT MAX(gl_date),
count(*)
INTO l_last_gl_date,
l_count
FROM ar_revenue_assignments
WHERE customer_trx_line_id = p_customer_trx_line_id
AND account_class = 'REV'
AND period_set_name = l_period_set_name
AND rownum <= DECODE(p_last_period_to_cr -
round(p_last_period_to_cr, 0),
0, p_last_period_to_cr,
p_last_period_to_cr + 1 );
SELECT ( (l_last_gl_date - max(gl_date) ) *
(p_last_period_to_cr - trunc(p_last_period_to_cr, 0) )
) + max(gl_date)
INTO l_last_gl_date
FROM ar_revenue_assignments
WHERE customer_trx_line_id = p_customer_trx_line_id
AND account_class = 'REV'
AND period_set_name = l_period_set_name
AND rownum <= l_count - 1;
SELECT gl_date
FROM ra_customer_trx_lines l,
ar_revenue_assignments ra
WHERE l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_line_id = ra.customer_trx_line_id
AND ra.account_class = 'REV'
ORDER BY gl_date;
SELECT MIN(gl_date),
MAX(gl_date)
INTO l_min_gl_date,
l_max_gl_date
FROM ra_customer_trx_lines l,
ar_revenue_assignments ra
WHERE l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_line_id = ra.customer_trx_line_id
AND ra.account_class = 'REV';
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 line_type in ('LINE', 'CB', 'CHARGES');
SELECT msi.description,
muom.uom_code,
muom.unit_of_measure_tl, /*4762000*/
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
),
DECODE( rr.status,
'A', rr.frequency,
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,
p_frequency
FROM mtl_system_items msi,
mtl_item_uoms_view 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 muom.inventory_item_id(+) = p_inventory_item_id
AND muom.organization_id(+) = p_organization_id
AND msi.accounting_rule_id = rr.rule_id (+)
AND rr.rule_id = rs.rule_id (+)
GROUP BY msi.description,
muom.uom_code,
muom.unit_of_measure_tl, /*4762000*/
rr.status,
msi.accounting_rule_id,
rr.name,
rr.type,
rr.occurrences,
rr.frequency;
SELECT
nvl(max(line_number) , 0)
INTO
l_line_number
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_type IN ('LINE','CB','CHARGES');
SELECT
header_id
INTO
l_oe_header_id
FROM oe_order_lines
WHERE line_id = to_number(p_oe_line_id)
and rownum < 2;
SELECT
zx.tax_classification_code
INTO
l_tax_classification_code
FROM
zx_id_tcc_mapping zx
WHERE
zx.source = 'AR' and
zx.tax_rate_code_id = p_vat_tax_id;
SELECT SUM(zl.tax_amt)
INTO tax_amount
FROM zx_lines zl
WHERE zl.tax_type_code LIKE 'VAT%'
AND zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND zl.trx_id = p_customer_trx_id;
SELECT SUM(zl.tax_amt)
INTO tax_amount
FROM zx_lines zl
WHERE (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
AND zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND zl.trx_id = p_customer_trx_id;
SELECT sum(zl.tax_amt) INTO tax_amount
FROM ra_customer_trx_lines trxl, zx_lines zl
WHERE trxl.line_type = 'TAX'
AND trxl.tax_line_id = zl.tax_line_id
AND zl.tax_type_code like 'VAT%'
AND zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND trxl.customer_trx_id = p_customer_trx_id
AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;
SELECT sum(zl.tax_amt) INTO tax_amount
FROM ra_customer_trx_lines trxl, zx_lines zl
WHERE trxl.line_type = 'TAX'
AND trxl.tax_line_id = zl.tax_line_id
AND (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
AND zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND trxl.customer_trx_id = p_customer_trx_id
AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;