4 Replies Latest reply: Mar 24, 2011 12:45 PM by user12838764 RSS

    problem of fetching archive data from pay action information

    user12838764
      Hi

      Currently I am working on Payroll for Indian Legislation. Now we have two cycle. 1 for earnings and allowances and 2 for reimbursement cycle whcih starts from 10 to 10 of any period like 10-april - 10-may. Now we need to fetch net pay. But we cannot use balance as asg_ptd wont work for as we have two payroll process result. asg_run won't work as well. Now we are looking after archive data. Now confusion is which column should I hit in pay_action-information table to fetch archieve data for a particular assignment_action_id.
        • 1. Re: problem of fetching archive data from pay action information
          Gaurav Bhardwaj
          Hi

          You need to use action_context_id = assignment_action_id in the pay_action_information table , please refer Metalink note How to Find Payroll Reversal Information in Archive Table [ID 284054.1] for more information.

          HTH
          Gaurav
          • 2. Re: problem of fetching archive data from pay action information
            user12838764
            Hi

            Thanks for your quick reply but I have used that. The below query I have used,
            select pai.*
            from
            pay_action_information pai
            ,pay_payroll_actions ppa
            ,pay_assignment_actions paa

            where ( pai.action_context_id = paa.assignment_action_id
            or pai.action_context_id = ppa.payroll_action_id
            )
            and paa.payroll_action_id = ppa.payroll_action_id
            --and         pai.assignment_id     = :assignment_id
            and paa.action_status = 'C'
            and ppa.action_status = 'C'
            --and         ppa.action_type       in ('R','Q','I','B')
            and pai.action_context_type in ('PA','AAP')
            and pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
            and ppa.business_group_id = :p_business_group_id

            But I am looking the exact table or column name which actually storing the data of balance.
            • 3. Re: problem of fetching archive data from pay action information
              Gaurav Bhardwaj
              Hi
              Please review the Metalink Note - Oracle Human Resources (HRMS) Guide to the Setup and Generation of the Online Payslip [ID 363694.1]

              Query should be like -

              select pai.assignment_id assid, SUBSTR (action_information1, 1, 5) orgpaymethid,
              SUBSTR (action_information2, 1, 5) perpaymethid,
              TO_NUMBER (action_information16) VALUE,
              action_information18 orgpaymethname
              from
              PAY_ACTION_INFORMATION pai
              ,pay_payroll_actions ppa
              ,pay_assignment_actions paa
              where ( pai.action_context_id = paa.assignment_action_id
              or pai.action_context_id = ppa.payroll_action_id
              )
              and paa.payroll_action_id = ppa.payroll_action_id
              --and pai.assignment_id = :assignment_id
              and paa.action_status = 'C'
              and ppa.action_status = 'C'
              --and ppa.action_type in ('R','Q','I','B')
              and pai.action_context_type in ('PA','AAP')
              and pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
              and ppa.business_group_id = :p_business_group_id

              HTH
              Gaurav
              • 4. Re:problem of fetching archive data from pay action information
                user12838764
                Hi
                Many thanks for your concern and I have succeeded to fetch value but one chalenge that I have been facinh that it pay_acttion_information table does not return any value for those assignment_action_id which does not have source_action_id in pay_assignment_actions table. Now question is why it does not return any value?