This discussion is archived
2 Replies Latest reply: Sep 25, 2013 6:42 AM by 983217 Branched from an earlier discussion. RSS

Re: Query for Cost Code in oracle HRMS

983217 Newbie
Currently Being Moderated

Hi

 

I am also looking out for something similar. I need the query which will give me the costing details of each of the elements (Costed Element Name | Costing Account | Balancing Account).

 

Any pointers on this would be highly appreciated.

 

Thanks

 

Regards

Trupti

  • 1. Re: Query for Cost Code in oracle HRMS
    Vigneswar Battu Guru
    Currently Being Moderated

    Hi Trupti,

     

    Please don't outsource your work. Forums are only to help you but not do your work.

    You need to put your efforts, so that others can help you.

     

    For now, I already had this query, so you can use it -

     

    select pel.element_link_id,
    ppf.payroll_name  
    ,pj.name Job
    ,hou.name HR_ORG,ppg.group_name People_Group
    ,hra.location_code

    ,pet.element_name,pet.reporting_name

    ,pet.processing_type

    ,pec.classification_name 

    ,pel.costable_type

    ,pel.link_to_all_payrolls_flag
    ,pcak1.concatenated_segments Element_Cost_Segments  
    ,pcak1.segment1 cost_segment1
    ,pcak1.segment2 cost_segment2
    ,pcak1.segment3 cost_segment3
    ,pcak1.segment4 cost_segment4
    ,pcak1.segment5 cost_segment5
    ,pcak1.segment6 cost_segment6
    ,pcak1.segment7 cost_segment7
    ,pcak1.segment8 cost_segment8
    ,pcak2.concatenated_segments Balancing_Cost_Segments 
    ,pcak2.segment1 Bal_segment1
    ,pcak2.segment2 Bal_segment2
    ,pcak2.segment3 Bal_segment3
    ,pcak2.segment4 Bal_segment4
    ,pcak2.segment5 Bal_segment5
    ,pcak2.segment6 Bal_segment6
    ,pcak2.segment7 Bal_segment7
    ,pcak2.segment8 Bal_segment8
    from pay_element_links_f pel  
    ,apps.PAY_COST_ALLOCATION_KEYFLEX  pcak1   
    ,apps.PAY_COST_ALLOCATION_KEYFLEX  pcak2   
    ,pay_element_types_f pet  
    ,pay_payrolls_f ppf  
    ,pay_element_classifications pec  
    ,per_jobs pj 
    ,hr_all_organization_units hou 
    ,PAY_PEOPLE_GROUPS ppg 
    ,hr_locations_all hra
    where pel.element_type_id = pet.element_type_id  
    and ppf.payroll_id(+)   = pel.payroll_id
    and pcak1.cost_allocation_keyflex_id(+) = pel.cost_allocation_keyflex_id    

    and pcak2.cost_allocation_keyflex_id(+) = pel.balancing_keyflex_id  

    and  pel.business_group_id = :BG_ID
    and pet.classification_id = pec.classification_id  
    and pj.job_id(+) = pel.job_id 
    and hou.organization_id(+) = pel.organization_id 
    and ppg.people_group_id(+) = pel.people_group_id 
    and hra.location_id(+) = pel.location_id 
    and sysdate between pel.effective_start_date and pel.effective_end_date
    and sysdate between pet.effective_start_date and pet.effective_end_date
    and sysdate between ppf.effective_start_date(+) and ppf.effective_end_date(+)

    order by ppf.payroll_name,pet.element_name,pec.classification_name;


     

    Cheers,

    Vignesh

  • 2. Re: Query for Cost Code in oracle HRMS
    983217 Newbie
    Currently Being Moderated

    Hi Vignesh

     

    Appreciate your assistance here!

     

    I arrived at the following query (which still needs some tweaking) after doing my bit of R&D:

     

    SELECT pelf.element_link_id,
      petf.element_type_id,
      petf.element_name,
      pelf.costable_type,
      pelf.cost_allocation_keyflex_id,
      pcak.concatenated_segments,
      pelf.balancing_keyflex_id
    FROM pay_element_links_f pelf,
      PAY_COST_ALLOCATION_KEYFLEX pcak,
      pay_element_types_f petf
    WHERE pelf.costable_type            = 'C'
    AND pelf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
    AND pelf.element_type_id            = petf.element_type_id

     

    I am a functional consultant and so, I need help on the technical and the SQL aspects. I posted this on the forum to seek help today, so that I can return the favor tomorrow. I hope you will be one of them

     

    Cheers
    Trupti