Comments

  • Sudhakara Rao Kovuru

    Thank You Karissa,

    We can do and we have done other jobs which kind of same scenario’s and they are working.

    Logged SR with Oracle support and working with them... and will update you if i get any update.

    Thanks,

    -Sudha.

  • Sudhakara Rao Kovuru

    Ohhhh... Good to know...

    We just customized the data model and linked to original report ESS job so that it should run through scheduling...  But its keep failing..

    5300727.log (710 Bytes)
  • Sudhakara Rao Kovuru

    Hi Karissa,

    Hope you are doing great.

    Can you please confirm, the customized Account Analysis Report working as expected? 

    We customized the report but keep failing... no proper log message..

     

    Thanks,

    -Sudha

  • Sudhakara Rao Kovuru

    Sure... above tables are belongs to Application Security which eventually refers to users/security so they may help you.

  • Sudhakara Rao Kovuru

    Ok... In fusion I could see only below tables has the LAST_LOGIN_DATE column so please try this table ASE_USER_LOGIN_INFO with above query and also check below tables which may help you...

    ARB_REG_USER_PROFILES
    ARB_REG_USER_PROFILES_SEC
    ASE_USER_LOGIN_INFO
    ASE_USER_LOGIN_INFO_SEC
    ARB_REG_USER_PROFILES
    ASE_USER_LOGIN_INFO

  • Sudhakara Rao Kovuru

    Use below query... hope this should work for you guys... please let me know.

     

    SELECT DISTINCT Lower(pu.username)                 username, 
                    ppn.first_name, 
                    ppn.last_name, 
                    To_char(pu.start_date, 'mm/dd/rrrr')                 acct_start_date, 
                    To_char(pu.end_date, 'mm/dd/rrrr')                 acct_end_date, 
                    To_char(pu.creation_date, 'mm/dd/rrrr')                 acct_creation_date, 
                    To_char(pu.last_update_date, 'mm/dd/rrrr')                 acct_last_update_date, 
                    prdv.role_name, 
                    prdv.role_common_name                                   code           , 
                    To_char(pur.start_date, 'mm/dd/rrrr')                 role_start_date, 
                    To_char(pur.end_date, 'mm/dd/rrrr')                 role_end_date, 
                    To_char(pur.creation_date, 'mm/dd/rrrr')                 role_creation_date, 
                    pur.created_by                 role_created_by, 
                    To_char(pur.last_update_date, 'mm/dd/rrrr')                 role_last_update_date, 
                    pur.last_updated_by                 role_last_updated_by, 
                    (SELECT ppnf.full_name 
                     FROM   per_all_assignments_m paam, 
                            per_periods_of_service ppos, 
                            per_assignment_supervisors_f pasf, 
                            per_person_names_f ppnf 
                     WHERE  pu.person_id = paam.person_id 
                            AND ppos.person_id = paam.person_id 
                            AND ppos.period_of_service_id = 
                                paam.period_of_service_id 
                            AND paam.primary_assignment_flag = 'Y' 
                            AND paam.assignment_type IN ( 'E', 'C' ) 
                            AND paam.assignment_status_type ! = 'INACTIVE' 
                            AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND 
                                                       paam.effective_end_date 
                            AND pu.person_id = pasf.person_id 
                            AND pasf.manager_type = 'LINE_MANAGER' 
                            AND ppnf.person_id = pasf.manager_id 
                            AND ppnf.name_type = 'GLOBAL' 
                            AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND 
                                                       pasf.effective_end_date)              Manager 
    FROM   per_users pu, 
           per_user_roles pur, 
           per_person_names_f ppn, 
           per_roles_dn_vl prdv 
    WHERE  pu.user_id = pur.user_id 
           AND pu.person_id = ppn.person_id 
           AND pur.role_id = prdv.role_id 
           AND ( pu.end_date IS NULL      OR pu.end_date > SYSDATE ) 
           AND pu.suspended = 'N' 
           AND pur.active_flag = 'Y' 
           AND ppn.name_type = 'GLOBAL' 
           AND Trunc(SYSDATE) BETWEEN ppn.effective_start_date AND      ppn.effective_end_date 
           AND Trunc(pur.start_date) BETWEEN Nvl(:P_Role_From, Trunc(pur.start_date)) AND   Nvl(:P_Role_To, 
                   Trunc(pur.start_date)) 
           AND Trunc(pu.start_date) BETWEEN Nvl(:P_Acc_From, Trunc(pu.start_date)) 
                                            AND Nvl( 
                                            :P_Acc_To, Trunc(pu.start_date)) 
           AND ( :P_Username IS NULL 
                  OR ( Upper(pu.username) LIKE '%' 
                                               || Upper(:P_Username) 
                                               ||'%' ) ) 
    ORDER  BY 1, 
              2, 
              prdv.role_name 

  • Sudhakara Rao Kovuru

    Hi Karissa,

    I am still getting error while customizing the report. its quickly exiting.

     

    Thanks,

    -Sudha

  • Sudhakara Rao Kovuru

    You can create your own custom BIP report to track changes

  • Sudhakara Rao Kovuru

    Transactional level audit not enabled in Receivables Cloud so better create SR

  • Sudhakara Rao Kovuru

    Organization id is required for Customer Account Profile ti update the site level details.

  • Sudhakara Rao Kovuru

    You need to customize and  change the bursting logic to pick only primary contact email so that it will go to this contact only

     

    SELECT max(cp.email_address) 
       FROM hz_contact_points       cp, 
            hz_cust_account_roles   acct_role 
      WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id  -- ra_customer_trx_all                     trx,
        and acct_role.relationship_id = cp.relationship_id 
        and cp.owner_table_name = 'HZ_PARTIES' 
        and cp.contact_point_type='EMAIL'
        and cp.status ='A'
        and cp.primary_flag = 'Y'

  • Sudhakara Rao Kovuru

    Angela, Do you need SQL query for it? I mean what is the ask here?

  • Sudhakara Rao Kovuru

    Hi Julien,

    Thanks for the update and information provided too...

    We are looking for ESS schedules job tables from where we can prepare a custom report to show it...not the definition of the program.

    Could you please help me, which table these information stored?

    Appreciate for your help.

    Thanks,

    -Sudha.

  • Sudhakara Rao Kovuru

    There might be option to personalize it... so let me check and get back to you...

  • Sudhakara Rao Kovuru

    You might not have the HR related role please check...