Friday, January 3, 2025

Query to get Grade Rate details in Oracle ERP

SELECT prft.name "ratename"
      ,pg.name "gradename"
      ,prvf.minimum
      ,prvf.maximum
      ,prvf.mid_value
      ,prft.effective_start_date "ab"
      ,prvf.effective_start_date "cd"
      ,pg.effective_start_date "ef"
  FROM per_rates_f_tl    prft
      ,per_rate_values_f prvf
      ,per_grades        pg
 WHERE prft.rate_id        = prvf.rate_id
   AND prvf.rate_object_id = pg.grade_id
   AND prvf.rate_object_type = 'GRADE'
   AND prft.name IN ('XX Rate Name')
   AND PRFT.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')
   AND TRUNC(SYSDATE) BETWEEN prft.effective_start_date AND prft.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prvf.effective_start_date AND prvf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
ORDER BY prft.name
       , pg.name

No comments:

Post a Comment

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account       ,ffvt1.description parent_account_desc   ,ffv2.flex_value child_account   ,ffvt2.description...