Problem on GL Query!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem on GL Query!!

  1. #1
    Join Date
    Nov 2003
    Posts
    89

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    89

    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
  •  


Click Here to Expand Forum to Full Width