AR Payment Query

select rcta.trx_number, rcta.trx_date,ra.amount_applied, ra.gl_date

        --nvl(sum(ra.amount_applied), 0)

  from ar_receivable_applications_all ra,

       ra_customer_trx_all            rcta,

       ar_payment_schedules_all       apsa

      

 where ra.applied_customer_trx_id = rcta.customer_trx_id

   and ra.applied_payment_schedule_id = apsa.payment_schedule_id

   and ra.application_type = 'CASH'

   and ra.status = 'APP'

   AND rcta.customer_trx_id = apsa.customer_trx_id

   and rcta.org_id = apsa.org_id

   and rcta.org_id = &P_ORG_ID

      -- and    ra.apply_date between to_date(&sd) and to_date(&ed)) payment,

   and ra.gl_date between to_date(&sd) and to_date(&ed)

Accessing non-Oracle data sources

/*  -----  Accessing non-Oracle data sources by Oracle Report Builder  ------

    10001 Site

    10002 Application

    10003 Responsibility

    10004 USER

*/

select 'User Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.user_name "Value_Set_On_User",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       fnd_user                  CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.user_id

   and b.level_id = 10004;

select 'Responsibility Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.responsibility_name "Value_Set_On_Responsibility",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       fnd_responsibility_tl     CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.responsibility_id

   and b.level_id = 10003;

select 'Application Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.APPLICATION_NAME "Value_Set_On_This_Application",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       FND_APPLICATION_ALL_VIEW  CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.APPLICATION_ID

   and b.level_id = 10002;

select 'Site Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_id = 10001

 order by c.APPLICATION_NAME;