Friday, November 9, 2018

Balance Query in Oracle Payroll - Element Feeding the Balance

Balance Query in Oracle Payroll - Element Feeding the Balance

Query 1:

SELECT   petf.element_name bal_feed_element_name,
         pivf.NAME bal_feed_input_name,pbt.balance_name, pbt.reporting_name,
         hr_general.decode_organization (pbt.business_group_id)
                                                               business_group,
         pbt.base_balance_type_id base_balance_name
--,PBT.UN
         ,
         hr_general.decode_lookup ('UNITS', pbt.balance_uom) units,
         pbt.currency_code, pbt.assignment_remuneration_flag,
         (SELECT element_name
            FROM pay_element_types_f
           WHERE element_type_id =
                    (SELECT element_type_id
                       FROM pay_input_values_f
                      WHERE input_value_id =
                                      pbt.input_value_id))
                                                         primary_element_name,
         (SELECT element_type_id
            FROM pay_input_values_f
           WHERE input_value_id = pbt.input_value_id) primary_input_name,        
         DECODE (pbf.scale,
                 1, 'ADD',
                 -1, 'SUBTRACT',
                 NULL
                ) balance_add_subtract,
         pbf.effective_start_date, pbf.effective_end_date, pbd.dimension_name,
         pbd.description, pdb.grossup_allowed_flag, pdb.run_balance_status,
         NULL initial_feed_element_name, NULL initial_feed_input_value_name,
         NULL attribute_name, NULL attribute_dimension
    FROM pay_balance_types pbt,
         pay_balance_feeds_f pbf,
         pay_element_types_f petf,
         pay_input_values_f pivf,
         pay_defined_balances pdb,
         pay_balance_dimensions pbd
   WHERE pbt.balance_type_id = pbf.balance_type_id
     AND pdb.balance_type_id = pbt.balance_type_id
     AND pbd.balance_dimension_id = pdb.balance_dimension_id
     AND pbf.input_value_id = pivf.input_value_id
     AND pivf.element_type_id = petf.element_type_id
    -- AND pbt.created_by != 1
    -- AND petf.created_by != 1
ORDER BY 1;


Query 2:
SELECT   petf.element_name bal_feed_element_name,
         pivf.NAME bal_feed_input_name,pbt.balance_name
    FROM pay_balance_types pbt,
         pay_balance_feeds_f pbf,
         pay_element_types_f petf,
         pay_input_values_f pivf
   WHERE pbt.balance_type_id = pbf.balance_type_id
     AND pbf.input_value_id = pivf.input_value_id
     AND pivf.element_type_id = petf.element_type_id
     AND petf.business_group_id = 82 

2 comments:

  1. Great one , really helped me. Respect :)

    ReplyDelete
  2. Query 2
    SELECT petf.base_element_name bal_feed_element_name,
    pivf.BASE_NAME bal_feed_input_name,pbt.base_balance_name
    FROM pay_balance_types pbt,
    pay_balance_feeds_f pbf,
    pay_element_types_f petf,
    pay_input_values_f pivf
    WHERE pbt.balance_type_id = pbf.balance_type_id
    AND pbf.input_value_id = pivf.input_value_id
    AND pivf.element_type_id = petf.element_type_id

    ReplyDelete

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...