7 Replies Latest reply: Nov 20, 2012 10:32 PM by 975131 RSS

    CUSTOM BALANCE

    Jurgens
      How do I retrieve a custom balance via SQL? Any assistance appreciated. Thanks
        • 1. Re: CUSTOM BALANCE
          sb92075
          Pauly Walnuts wrote:
          How do I retrieve a custom balance via SQL? Any assistance appreciated. Thanks
          do we get to guess which tables contribute to the desired solution?

          It is a real challenge to write SQL which you don'y know table or column names.

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: CUSTOM BALANCE
            rp0428
            >
            How do I retrieve a custom balance via SQL? Any assistance appreciated. Thanks
            >
            There are several ways to get a custom balance.
            select balance from myTable where balanceType = 'CUSTOM';
            
            select customBalance from myTable;
            
            select sum(balance) customBalance from myTable;
            • 3. Re: CUSTOM BALANCE
              Jurgens
              Well. I understand your question, but that's what I'm asking? I'm not sure where custom balances are stored. Perhaps pay_person_latest_balances but I don't know if custom balances are contained in this table.
              • 4. Re: CUSTOM BALANCE
                Giuseppe Bonavita
                first you need to set the context:

                pay_balance_pkg.set_context ('TAX_ID', p_tax_id);



                then you can get the value:

                pay_balance_pkg.get_value (p_defined_balance_id, p_assignment_action_id)
                or
                pay_balance_pkg.get_value (p_assignment_id, p_virtual_date)
                • 5. Re: CUSTOM BALANCE
                  Jurgens
                  Thank you Guiseppe!

                  I have four custom balances created by the business that I need to retrieve via PL/SQL.

                  I can pull the defined_balance_id from the HR.PAY_BALANCE_TYPES_TL by balance name and assignment id from the assignment table for an emp.

                  Assume TAX id is the GRE EIN? Or ??

                  pay_balance_pkg.set_context ('TAX_ID', p_tax_id);


                  How about virtual date? Is this a dimension or just any date? If I want YTD balance, what would be specified?

                  then you can get the value:

                  pay_balance_pkg.get_value (p_defined_balance_id, p_assignment_action_id)
                  or
                  pay_balance_pkg.get_value (p_assignment_id, p_virtual_date)
                  • 6. Re: CUSTOM BALANCE
                    clive_t
                    Hello Mr Walnuts,

                    By way of clarification, can I ask, are you talking about UK legislation at all? If so, try looking for views starting with PAY_GB_BALANCES%, and the pl/sql package hr_gbbal

                    If not, then regardless of legislation, the pl/sql package pay_balance_pkg contains a whole host of functions to allow the retrieval of balances. I am not 100% sure of this, but I believe Mr Guiseppe's earlier reference to the context TAX_ID is US-specific - but, I am more than happy to be proved wrong on that.

                    Also, for further clarification, when you say 'custom balance' do you mean a non-statutory balance, or do you mean a custom dimension?

                    When trying to understand balances, it always helped me to consider that a balance in the context of Oracle Payroll is merely a logical sum of run result values for a given date range. Which collection of run result values is largely dictated by the balance feeds that have been defined for the balance, while the start of the date range is dictated by the dimension you are referring to. For example, a dimension ending in YTD will imply a start date of the beginning of a tax year, whereas a dimension ending in ITD will imply a start date of whenever the employee was on some sort of payroll for the first time (e.g. when he/she was hired)

                    The end of the date range, is, of course, the date you specify in your query - or, in the context of a payroll run, the end of the pay period that is being processed.

                    In answer to your earlier question about pay_%_latest_balances, I would be inclined to argue that they are not a reliable thing to report on, as they are normally only for use by the payroll process to speed up balance retrieval. Sometimes they are up to date, sometimes they are not. Trouble is, you can't tell easily! So, the safest thing is not to rely on them at all.

                    One final thing - if you try and apply a balance retrieval function in a query 'where' clause (e.g. to find everyone with balance x greater than 500) then be prepared for a long wait!

                    I hope this goes some way to answering at least some of your questions.

                    Good luck

                    Clive
                    • 7. Re: CUSTOM BALANCE
                      975131
                      Hi, Pauly~
                      "retrieve a custom balance via SQL" => Do you want to retrieve data at Payroll Run Process or normal usage?
                      If you want to get balance amount in Fast Formula during Payroll Run or QuickPay Process, you don't need to use SQL in order to get balance amount, and you can check Fast Formula User Guide (Oracle® Human Resources Management Systems FastFormula User Guide Release 12).
                      For other purpose, if you want to get balance amounts, you need to write a function or procedure to get that amount:
                      1.Retrieve elements in the balance
                      2.Get the amount of elements by loop and sum these amounts.

                      Here are examples:
                      1.Retrieve elements in the balance
                      SELECT ettl.element_name ELEMENT_NAME ,
                      ivtl.NAME INPUT_VALUE_NAME,
                      UPPER(f1.meaning) ADD_OR_SUBTRACT ,
                      TO_NUMBER(f1.lookup_code) MULTIPLE ,
                      et.element_type_id ELEMENT_TYPE_ID
                      FROM pay_balance_feeds_f bf,
                      pay_balance_types bt,
                      pay_balance_types_tl bttl,
                      pay_input_values_f iv,
                      pay_input_values_f_tl ivtl,
                      pay_element_types_f et,
                      pay_element_types_f_tl ettl,
                      pay_element_classifications ec,
                      pay_element_classifications_tl ectl,
                      fnd_lookup_values f1,
                      fnd_lookup_values f2
                      WHERE ec.classification_id = ectl.classification_id
                      AND upper(ectl.LANGUAGE) = upper(PIS_LANGUAGE) --USERENV ('LANG')
                      AND et.element_type_id = ettl.element_type_id
                      AND upper(ettl.LANGUAGE) = upper(PIS_LANGUAGE) --USERENV ('LANG')
                      AND iv.input_value_id = ivtl.input_value_id
                      AND upper(ivtl.LANGUAGE) = upper(PIS_LANGUAGE) --USERENV ('LANG')
                      AND bt.balance_type_id = bttl.balance_type_id
                      AND upper(bttl.LANGUAGE) = upper(PIS_LANGUAGE) --USERENV ('LANG')
                      AND bt.balance_type_id = bf.balance_type_id
                      AND iv.input_value_id = bf.input_value_id
                      AND et.element_type_id = iv.element_type_id
                      AND ec.classification_id = et.classification_id
                      AND f1.lookup_type = 'ADD_SUBTRACT'
                      AND f1.lookup_code = bf.scale
                      AND upper(f1.language) = upper(PIS_LANGUAGE)
                      AND PID_DATE_EARNED BETWEEN NVL(f1.start_date_active, TO_DATE('19000101','YYYYMMDD')) AND NVL(f1.end_date_active, TO_DATE('47121231','YYYYMMDD'))
                      AND f2.lookup_type = 'UNITS'
                      AND f2.lookup_code = iv.uom
                      AND upper(f2.language) = upper(PIS_LANGUAGE)
                      AND PID_DATE_EARNED BETWEEN NVL(f2.start_date_active, TO_DATE('19000101','YYYYMMDD')) AND NVL(f2.end_date_active, TO_DATE('47121231','YYYYMMDD'))
                      AND PID_DATE_EARNED BETWEEN bf.effective_start_date AND bf.effective_end_date
                      AND PID_DATE_EARNED BETWEEN iv.effective_start_date AND iv.effective_end_date
                      AND PID_DATE_EARNED BETWEEN et.effective_start_date AND et.effective_end_date
                      AND NVL (ec.balance_initialization_flag, 'N') = 'N'
                      AND (
                      (RTRIM(bf.business_group_id) IS NULL OR bf.business_group_id = PIN_BUSINESS_GROUP_ID) --PIN_BUSINESS_GROUP_ID => argument
                      AND
                      (RTRIM(bf.legislation_code) IS NULL OR bf.legislation_code = 'TW') --'TW' => You can use fixed constant or a argument
                      )
                      AND upper(bttl.balance_name) = upper(PIS_BALANCE_NAME) --'PQI_ABSENCE_BASIS' => argument
                      ORDER BY ettl.element_name;

                      2.Get the amount of elements by loop and sum these amounts.
                      SELECT SUM(NVL(TO_NUMBER(PEEV.SCREEN_ENTRY_VALUE), 0))
                      INTO LN_RET =>return value
                      FROM PER_ASSIGNMENTS_F PAF, --Assignment Table
                      PAY_ELEMENT_ENTRIES_F PEE, --Elements of assignment
                      PAY_ELEMENT_ENTRY_VALUES_F PEEV, --Input values in element definition
                      PAY_INPUT_VALUES_F PIV, --The Value of an input value
                      (SELECT X.INPUT_VALUE_ID, X.LANGUAGE
                      FROM PAY_INPUT_VALUES_F_TL X
                      WHERE EXISTS(SELECT 1
                      FROM PAY_INPUT_VALUES_F_TL X2
                      WHERE X2.INPUT_VALUE_ID = X.INPUT_VALUE_ID
                      AND UPPER(X2.LANGUAGE) = UPPER(p_language) --p_language => argument or fixed constant
                      AND UPPER(X2.NAME) = UPPER(p_input_value_name))) PIVT, --(BY LANG) --p_input_value_name => argument
                      PAY_ELEMENT_TYPES_F PET, --Element Type
                      PAY_ELEMENT_TYPES_F_TL PETT --Element Type(BY LANG) 
                      WHERE PAF.ASSIGNMENT_ID = p_assignment_id --p_assignment_id => argument
                      AND PAF.BUSINESS_GROUP_ID = p_business_group_id --p_business_group_id => argument
                      AND p_base_date BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
                      AND PAF.PRIMARY_FLAG = 'Y' --Primary assignment
                      AND PAF.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
                      AND p_base_date BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
                      AND PEE.creator_type IN
                      ('H', /* MIX */
                      'P', /* BACKPAY */
                      'SP', /* SALARY PROPOSAL */
                      'F', /* OTHER */
                      'M', /* STATUTORY MATERNITY PAY */
                      'S', /* STATUTORY SICK PAY */
                      'A', /* ABSENCE */
                      'D', /* ADVANCE PAY */
                      'DF', /* ADVANCE PAY FORM */
                      'R', /* RETROPAY BY ACTION */
                      'EE', /* RETROPAY/ELEMENT EE */
                      'RR', /* RETROPAY/ELEMENT RR */
                      'AD', /* ADVANCEPAY/ELEMENT AD */
                      'AE', /* ADVANCEPAY/ELEMENT AE */
                      'PR', /* RETROPAY ELEMENT PR */
                      'NR', /* RETROPAY/ELEMENT NR */
                      'FL' /* FLSA ENTRY */
                      ) /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
                      AND PEE.entry_type IN
                      ('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */) /* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
                      AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
                      AND p_base_date BETWEEN PEEV.EFFECTIVE_START_DATE AND PEEV.EFFECTIVE_END_DATE
                      AND PEEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
                      AND p_base_date BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
                      AND PIV.INPUT_VALUE_ID = PIVT.INPUT_VALUE_ID
                      AND UPPER(PIVT.LANGUAGE) = UPPER(p_language) --p_language => argument or fixed constant
                      AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
                      AND p_base_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
                      AND PET.ELEMENT_TYPE_ID = PETT.ELEMENT_TYPE_ID
                      AND UPPER(PETT.LANGUAGE) = UPPER(p_language) --p_language => argument or fixed constant
                      AND UPPER(PETT.ELEMENT_NAME) = UPPER(LS_ELEMENT_NAME); --ls_element_name => argument or using sql to get the name by languages

                      Above of all, you can try to select data and modify sql in your function or procedure.

                      Best Regards,
                      Changhua