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

    Re: Query for Cost Code in oracle HRMS

    983217

      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

          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

            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