-
Problem on GL Query!!
I have this GL query..while running it on toad,,am getting an error at @variable which is at second page.
am not eactly sure wht @variable does it?
can guys help me out?
Thanks
-
GL Query doc..!1
here is the SQL Query Doc.
select /*+ rowid(d) */
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
sum(nvl(d.ACCOUNTED_DR,0) - nvl(d.ACCOUNTED_CR,0)) ,
f.user_name
from
gl_sets_of_books a,
gl_je_batches b,
gl_je_headers c,
gl_je_lines d,
gl_code_combinations e,
fnd_user f,
gl_je_sources gjs,
gl_je_categories gjc,
fnd_flex_values k,
gl_periods x,
gl_periods y,
gl_periods z
where
a.set_of_books_id = b.set_of_books_id
and b.set_of_books_id = c.set_of_books_id
and c.set_of_books_id = d.set_of_books_id
and b.JE_BATCH_ID = c.JE_BATCH_ID
and c.JE_HEADER_ID = d.JE_HEADER_ID
and c.je_source = gjs.je_source_name
and c.je_category = gjc.je_category_name
and b.ACTUAL_FLAG = 'A'
and b.status = 'P'
and e.segment2 = k.flex_value
and k.FLEX_VALUE_SET_ID = 1004336
and a.set_of_books_id in (1,2,4,32,87,88)
and a.chart_of_accounts_id = e.chart_of_accounts_id
and abs(nvl(d.ACCOUNTED_DR,0) - nvl(d.ACCOUNTED_CR,0)) > @variable('USD Greater Than')
and c.period_name = d.period_name
and a.currency_code = 'USD'
and d.code_combination_id = e.code_combination_id
and c.created_by = f.user_id(+)
and a.name like @variable('1 Set of Books Enter % if All')
and e.segment1 like @variable('2 LOCATION Enter % if All')
and e.segment2 between @variable('3 Low Account') and @variable('4 High Account')
and e.segment3 like @variable('5 Cost Center Enter % if All')
and c.period_name = x.period_name
and x.period_set_name = 'Corporate'
and y.period_set_name = 'Corporate'
and z.period_set_name = 'Corporate'
and x.start_date between y.start_date and z.end_date
and y.period_name = @variable('6 Start Period')
and z.period_name = @variable('7 End Period')
group by
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
f.user_name
UNION ALL
select /*+ rowid(d) */
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
sum(
(nvl(d.ACCOUNTED_DR,0)*
decode(e.account_type, 'R', g.avg_rate, 'E', g.avg_rate, g.eop_rate))
-(nvl(d.ACCOUNTED_CR,0)*
decode(e.account_type, 'R', g.avg_rate,'E', g.avg_rate, g.eop_rate))
),
f.user_name
from
gl_sets_of_books a,
gl_je_batches b,
gl_je_headers c,
gl_je_lines d,
gl_je_sources gjs,
gl_je_categories gjc,
gl_code_combinations e,
fnd_user f,
gl_translation_rates g,
fnd_flex_values k,
gl_periods x,
gl_periods y,
gl_periods z
where
a.set_of_books_id = b.set_of_books_id
and b.set_of_books_id = c.set_of_books_id
and c.set_of_books_id = d.set_of_books_id
and d.set_of_books_id = g.set_of_books_id
and c.je_source = gjs.je_source_name
and c.je_category = gjc.je_category_name
and b.JE_BATCH_ID = c.JE_BATCH_ID
and c.JE_HEADER_ID = d.JE_HEADER_ID
and b.ACTUAL_FLAG = 'A'
and b.status = 'P'
and e.segment2 = k.flex_value
and k.FLEX_VALUE_SET_ID = 1004336
and a.set_of_books_id in ( 3,33,39,40,43,28,38,35,34,36,41,42,37,29)
and a.chart_of_accounts_id = e.chart_of_accounts_id
--and abs((nvl(d.ACCOUNTED_DR,0)*g.eop_rate) - (nvl(d.ACCOUNTED_CR,0)*g.eop_rate)) > @variable('USD Greater Than')
and c.period_name = d.period_name
and d.code_combination_id = e.code_combination_id
and c.created_by = f.user_id(+)
and g.to_currency_code = 'USD'
and g.period_name = c.period_name
and a.name like @variable('1 Set of Books Enter % if All')
and e.segment1 like @variable('2 LOCATION Enter % if All')
and e.segment2 between @variable('3 Low Account') and @variable('4 High Account')
and e.segment3 like @variable('5 Cost Center Enter % if All')
and c.period_name = x.period_name
and x.period_set_name = 'Corporate'
and y.period_set_name = 'Corporate'
and z.period_set_name = 'Corporate'
and x.start_date between y.start_date and z.end_date
and y.period_name = @variable('6 Start Period')
and z.period_name = @variable('7 End Period')
group by
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
f.user_name
UNION ALL
select /*+ rowid(d) */
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
sum(nvl(d.ACCOUNTED_DR,0) - nvl(d.ACCOUNTED_CR,0)) ,
f.user_name
from
gl_sets_of_books a,
gl_je_batches b,
gl_je_headers c,
gl_je_lines d,
gl_je_sources gjs,
gl_je_categories gjc,
gl_code_combinations e,
fnd_user f,
fnd_flex_values k,
gl_periods x,
gl_periods y,
gl_periods z
where
a.set_of_books_id = b.set_of_books_id
and b.set_of_books_id = c.set_of_books_id
and c.set_of_books_id = d.set_of_books_id
and c.je_source = gjs.je_source_name
and c.je_category = gjc.je_category_name
and b.JE_BATCH_ID = c.JE_BATCH_ID
and c.JE_HEADER_ID = d.JE_HEADER_ID
and b.ACTUAL_FLAG = 'A'
and b.status = 'P'
and e.segment2 = k.flex_value
and k.FLEX_VALUE_SET_ID = 1004336
and a.name ='AGERE CONSOLIDATED OPERATIONS'
and abs(nvl(d.ACCOUNTED_DR,0) - nvl(d.ACCOUNTED_CR,0)) > @variable('USD Greater Than')
and c.period_name = d.period_name
and c.je_source != 'Consolidation'
and c.je_category != '90'
and a.currency_code = 'USD'
and d.code_combination_id = e.code_combination_id
and a.chart_of_accounts_id = e.chart_of_accounts_id
and c.created_by = f.user_id(+)
and a.name like @variable('1 Set of Books Enter % if All')
and e.segment1 like @variable('2 LOCATION Enter % if All')
and e.segment2 between @variable('3 Low Account') and @variable('4 High Account')
and e.segment1 like @variable('5 Cost Center Enter % if All')
and c.period_name = x.period_name
and x.period_set_name = 'Corporate'
and x.period_set_name = y.period_set_name
and y.period_set_name = z.period_set_name
and x.start_date between y.start_date and z.end_date
and y.period_name = @variable('6 Start Period')
and z.period_name = @variable('7 End Period')
group by
a.name,
c.period_name,
a.currency_code,
e.account_type,
b.name,
gjs.user_je_source_name,
gjc.user_je_category_name,
c.currency_code,
c.name,
c.description,
c.posted_date,
c.accrual_rev_period_name,
c.RUNNING_TOTAL_ACCOUNTED_DR,
c.RUNNING_TOTAL_ACCOUNTED_CR,
d.effective_date,
d.description,
e.segment1,
e.segment2,
e.segment3,
e.segment4,
k.description,
f.user_name
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|