APEX$TASK_PK

 APEX$TASK_PK is a substitution string holding the primary key value of the system of records

Query to get the list of contexts for each formula type in Oracle Fusion HCM

select t.base_formula_type_name

,      ttl.formula_type_name

,      ttl.description

,      c.base_context_name

from   ff_formula_types_b t

,      ff_formula_types_tl ttl

,      ff_ftype_context_usages u

,      ff_contexts_b c

where  t.formula_type_id = u.formula_type_id

and    ttl.formula_type_id = t.formula_type_id

and    ttl.language = userenv('LANG')

and    ttl.formula_type_name like 'Performance Rating Calculation'--'Oracle%Payroll%'

and    c.context_id = u.context_id

Worker Self Evaluation - Calculated Rating Issue - Invalid Rating Level in Oracle Fusion HCM Performance Rating Calculation

One of the possible reason to fail is if the Length of the Goal Name is more than 255 Characters. Try to  reduce the Goal Name length to below 250 characters and use description for detailed information about the Goal.

Note: Please check with Oracle before you do any changes.

currentAppUi built in variable in VBCS

$global.currentAppUi.id:- The id of the App UI

$global.currentAppUi.urlId:-The id of the App UI as shown in the URL

$global.currentAppUi.displayName:- The display name for the App UI

$global.currentAppUi.description:- The description of the App UI

$global.currentAppUi.defaultPage:- The default page of the App UI (if there is one)

$global.currentAppUi.defaultFlow:- The default flow of the App UI (if there is one)

$global.currentAppUi.applicationStripe:- The stripe of the custom App UI

$global.currentAppUi.pillarTheme:- The pillar theme to use for the App UI

$global.currentAppUi.pillarThemeMode:- The pillar theme mode to use for the App UI

$global.currentAppUi.icon:- The icon of the custom App UI

$global.currentAppUi.usage:- (This variable is reserved for Oracle Cloud Applications)

$global.currentAppUi.menuDisplayName:- The name of the custom App UI

$global.currentAppUi.extensible: (Boolean) If this App UI can be extended

currentFlow built in variable in VBCS

If there is a routerFlow in the page, the $page.currentFlow variable can be used to retrieve the id of this flow.

$page.currentFlow:- The id of the current flow.

currentPage Built in variable in VBCS

• To access some of the current page's metadata, such as ID and title, there is a built-in variable named currentPage on the application object. 

• The currentPage variable automatically updates as the current page changes during navigation. 

• This can be used to update a navigation component with the currently selected page.

$flow.currentPage:- The id of the current page for this flow. 

$application.currentPage.id:-The path of the current page. The path describes the location of the page in the flow hierarchy.

$application.currentPage.path:-The path of the current page for the application. The path describes the location of the page in the flow hierarchy.

$application.currentPage.title:- The title of the current page. The title is formed by prepending all the titles of the shells in the flow hierarchy to the current page.

Query to get the Concurrent Program along with Value Set details

SELECT fcpl1.user_concurrent_program_name

      ,fdfcuv.end_user_column_name

      ,ffvs.flex_value_set_name value_set_name

      ,ffvt.application_table_name

  ,ffvt.value_column_name

      ,ffvt.additional_where_clause

  FROM apps.fnd_concurrent_programs fcp1

      ,apps.fnd_descr_flex_col_usage_vl fdfcuv

      ,apps.fnd_flex_value_sets ffvs

      ,apps.fnd_flex_validation_tables ffvt

      ,apps.fnd_concurrent_programs_tl fcpl1

 WHERE 1 = 1     

   AND fcp1.concurrent_program_id IN (SELECT DISTINCT fcp.concurrent_program_id

                                        FROM apps.fnd_concurrent_programs fcp

                                            ,apps.fnd_concurrent_programs_tl fcpl

                                            ,apps.fnd_concurrent_requests fcr

                                       WHERE 1 = 1

                                         AND fcp.concurrent_program_id = fcpl.concurrent_program_id

                                         AND fcpl.language = 'US'

                                         AND fcr.concurrent_program_id = fcpl.concurrent_program_id

                                         AND trunc(fcr.request_date) BETWEEN :p_from_date AND :p_to_date

                                         AND fcp.concurrent_program_name LIKE '%XXCPNAME%'

)

   AND fdfcuv.descriptive_flexfield_name = '$SRS$.' ||fcp1.concurrent_program_name

   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

   AND ffvt.flex_value_set_id = ffvs.flex_value_set_id

   AND fcp1.concurrent_program_id = fcpl1.concurrent_program_id

ORDER BY fcpl1.user_concurrent_program_name 

Query to get Unposted Journals in Oracle APPS R12

SELECT gjh.accrual_rev_flag accrual_rev_flag

      ,gjh.accrual_rev_period_name accrual_rev_period_name

  ,gjh.creation_date journal_creation_date

  ,gjh.currency_code currency_code

  ,gjh.doc_sequence_value doc_sequence_value

  ,fu.email_address email_address

  ,gjh.je_category je_category

  ,gjh.last_update_date last_update_date

  ,gjh.name name journal_header_name

  ,gsobv.name ledger_name

  ,gjh.period_name period_name

  ,gjh.posted_date posted_date

  ,gjh.reversed_je_header_id reversed_je_header_id

  ,gjh.running_total_accounted_dr running_total_accounted_dr

  ,gjh.status status

  ,fu.user_name user_name

  ,gjsv.user_je_source_name user_je_source_name

  ,fu1.user_name user_name

  ,gjbv.name journal_batch_name

  ,gjbv.posted_by_name posted_by_name

  ,ppx.email_address email_address

  ,period.period_set

  ,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL ) 

        THEN 'Y' 

ELSE 'N' 

END document_exists

  FROM applsys.fnd_user fu

      ,gl.gl_je_headers gjh

  ,apps.gl_sets_of_books_v gsobv

  ,applsys.fnd_user fu1

  ,apps.gl_je_sources_v gjsv

  ,apps.gl_je_batches_v gjbv

  ,apps.per_people_x ppx

  ,applsys.fnd_attached_documents fad

  ,applsys.fnd_attached_documents fad1

  ,(SELECT gpv.period_name AS period_name

          ,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set 

  FROM apps.gl_periods_v gpv 

WHERE (gpv.period_year >= 2024) 

GROUP BY gpv.period_name

) period

 WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)

   AND (fu1.employee_id = ppx.person_id(+))

   AND (gjh.last_updated_by = fu.user_id(+))

   AND (gjh.created_by = fu1.user_id(+))

   AND (gjh.je_batch_id = gjbv.je_batch_id(+))

   AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))

   AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))

   AND (gjh.je_source = gjsv.je_source_name)

   AND (gjh.period_name = period.period_name(+)))

   AND (fad.entity_name(+) = 'GL_JE_BATCHES')

   AND (fad1.entity_name(+) = 'GL_JE_HEADERS')

   AND (gjh.posted_date >= :p_posted_date)

   AND (gjsv.user_je_source_name IN (:p_source))

   AND (gjh.status IN ('U'))

   AND (gjh.period_name = :p_period_name)

GROUP BY period.period_set

        ,gjh.accrual_rev_flag

,gjh.accrual_rev_period_name

,gjh.creation_date

,gjh.currency_code

,gjh.doc_sequence_value

,fu.email_address,gjh.je_category

,gjh.last_update_date

,gjh.name

,gsobv.name

,gjh.period_name

,gjh.posted_date

,gjh.reversed_je_header_id

,gjh.running_total_accounted_dr

,gjh.running_total_dr

,gjh.status

,fu.user_name

,gjsv.user_je_source_name

,fu1.user_name

,gjbv.name

,gjbv.posted_by_name

,ppx.email_address

ORDER BY gsobv.name ASC 

        ,period.period_set ASC 

,gjsv.user_je_source_name ASC 

,gjh.doc_sequence_value ASC 

Query to get Employee Responsibility details in Oracle Fusion

SELECT papf.person_number

      ,ppnfv.display_name person_display_name

  ,paam.assignment_name

  ,hl.meaning AS representative_type

  ,par.asg_responsibility_id

  ,par.responsibility_name

  ,par.business_unit_id

  ,par.work_contacts_flag

  FROM per_asg_responsibilities par

      ,per_all_people_f papf

  ,per_all_assignments_m paam

  ,per_person_names_f_v ppnfv

  ,hcm_lookups hl

 WHERE par.person_id = papf.person_id

   AND par.assignment_id = paam.assignment_id

   AND par.person_id = ppnfv.person_id

   AND par.responsibility_type = hl.lookup_code

   AND hl.lookup_type = 'PER_RESPONSIBILITY_TYPES'

   AND par.status = 'Active'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN ppnfv.effective_start_date AND ppnfv.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN par.start_date AND NVL(par.end_date,TO_DATE('31-12-4712','DD-MM-YYYY'))

ORDER BY papf.person_number

Employee assigned Responsibilities in Oracle Fusion

SELECT (SELECT DISTINCT person_number

          FROM per_all_people_f per

         WHERE per.person_id = aor.person_id

       ) person_number

  ,(SELECT DISTINCT full_name

          FROM per_person_names_f per

         WHERE per.person_id = aor.person_id

   AND name_type = 'GLOBAL'

    ) person_name

  ,aor.responsibility_name

  ,TO_CHAR(aor.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') start_date

  ,TO_CHAR(aor.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') end_date

  ,aor.responsibility_type

  ,aor.status

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.business_unit_id

       ) business_unit_name

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.legal_entity_id

        ) legal_entity

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.organization_id

        ) department_name

      ,(SELECT location_name

          FROM hr_locations hl

         WHERE hl.location_id = aor.location_id

        ) location_name

  ,(SELECT DISTINCT name

          FROM hr_all_positions_f_vl pp

         WHERE pp.position_id = aor.position_id

        ) position_name

  ,(SELECT DISTINCT name

          FROM per_jobs_f_tl pj

         WHERE pj.job_id = aor.job_id

        ) job_name

  ,(SELECT DISTINCT name

          FROM per_grades_f_tl pg

         WHERE pg.grade_id = aor.grade_id

        ) grade_name

  ,aor.assignment_category

  ,(SELECT pay.payroll_name

          FROM pay_all_payrolls_f pay

         WHERE pay.payroll_id = aor.payroll_id

           AND TRUNC(SYSDATE) BETWEEN pay.effective_start_date AND pay.effective_end_date

        ) payroll_name

  ,(SELECT name

          FROM per_legislative_data_groups_vl pld

         WHERE pld.legislative_data_group_id = aor.legislative_data_group_id

            ) legislative_data_group

  FROM per_asg_responsibilities aor

Business Classification in Oracle Supplier R12 - POS_BUSINESS_CLASSIFICATIONS

Query:

SELECT * 

  FROM fnd_lookup_values_vl flv

 WHERE lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'

   AND flv.enabled_flag='Y' 

   --AND lookup_code = '8010'

   AND flv.start_date_active < sysdate 

   AND (flv.end_date_active > sysdate or flv.end_date_active is null) 

Application Developer->Application->Lookups->Application Object Library 

Query Type with POS_BUSINESS_CLASSIFICATIONS

SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number

      ,aia.invoice_id

  ,aia.invoice_num

  ,aia.invoice_amount

  ,distribution_line_number

  ,aida.accrual_posted_flag

  ,decode(aida.match_status_flag,

              'A', 'Validated',

              'N', 'Never Validated',

              'T', 'Needs Revalidation',

              'S', 'Stopped') "Invoice Validation Status"

  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),

              'S', 'Selected for Accounting',

              'P', 'Partial',

              'N', 'Unaccounted',

              'Y', 'Accounted',

              'Unaccounted') "Invoice Accounting Status",

      aida.invoice_distribution_id,

      aida.accounting_event_id,

      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,

                                               aia.invoice_amount,

                                               aia.payment_status_flag,

                                               aia.invoice_type_lookup_code)"Invoice Approval Status",

      decode(aida.posted_flag,

              'Y', 'Posted',

              'N', 'Not Posted',

              'Not Posted') "Invoice Posting Status"   

FROM ap_checks_all aca

    ,ap_invoice_payments_all aipa

,ap_invoices_all aia

,ap_suppliers asa

,ap_invoice_distributions_all aida

WHERE aca.check_id = aipa.check_id

AND aipa.invoice_id = aia.invoice_id

AND aia.vendor_id = asa.vendor_id

AND aida.invoice_id = aia.invoice_id

AND aca.check_number = <pass check number>

How To Populate the Phone Number Field in Oracle Loans? (Doc ID 2990762.1)

SELECT party.party_name Borrower_Name

      ,org.name operating_unit

  ,loan.loan_number

  ,contact_person.party_name contact_person_name

  ,con_phone.raw_phone_number contact_phone_number

  FROM hz_parties party

      ,lns_loan_headers_all loan

  ,hr_all_organization_units_tl org

  ,hz_parties contact_person

  ,hz_contact_points con_phone

 WHERE party.party_id = loan.primary_borrower_id

   AND org.organization_id = loan.org_id

   AND loan.contact_pers_party_id = contact_person.party_id(+)

   AND con_phone.owner_table_name(+) = 'HZ_PARTIES'

   AND con_phone.owner_table_id(+) = loan.contact_rel_party_id

   AND con_phone.primary_flag(+) = 'Y'

   AND con_phone.status(+) = 'A'

   AND con_phone.contact_point_type(+) = 'PHONE'

   AND loan.loan_id = :p_loan_id

Query for Outstanding Card Transactions Report (Doc ID 2755945.1) in Fusion Expenses

SELECT TRUNC(SYSDATE - e.start_date) "No. of Days"

      ,papf.person_number employee_number

  ,hzp.person_first_name emp_first_name

  ,hzp.person_last_name emp_last_name

  ,e.assignment_id

  ,ecp.card_program_name AS card_program_name

  ,cc.card_program_id AS card_prog_id

  ,e.org_id AS org_id

  ,e.emp_default_cost_center AS cost_ctr

  ,cc.company_account_id AS company_acct_id

  ,e.merchant_name

  ,ec.card_reference_id

  ,icc.masked_cc_number credit_card_num

  ,cc.description description

  ,e.emp_default_cost_center AS cost_center

  ,eed.segment1

  ,eed.segment2

  ,eed.segment3

  ,eed.segment4

  ,eed.segment5

  ,cc.billed_date

  ,cc.billed_amount

  ,cc.posted_date

  ,cc.posted_amount

  ,cc.transaction_date

  ,cc.transaction_amount

  ,e.orig_reimbursable_amount

  ,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',

       'PEND_MGR_APPROVAL', 'APPROVER',

       'SUBMITTED', 'APPROVER',

       'PEND_IND_APPROVAL', 'EMPLOYEE',

       'MGR_REJECTED', 'EMPLOYEE',

       'IND_REJECTED', 'EMPLOYEE',

       'SAVED', 'EMPLOYEE',

       'WITHDRAWN', 'EMPLOYEE',

       'REQUEST_INFO', 'EMPLOYEE',

       'EMPLOYEE') AS pending_action

  ,ca.payment_currency_code AS currency_code

  FROM fusion.exm_expenses e

      ,fusion.exm_expense_Reports er

      ,fusion.exm_credit_card_trxns cc

      ,fusion.exm_cc_company_accounts ca

      ,fusion.exm_cards ec

      ,fusion.per_all_people_f papf

      ,fusion.IBY_CREDITCARD ICC

      ,fusion.hz_parties hzp

      ,fusion.PER_USERS pu

      ,fusion.EXM_EXPENSE_DISTS eed

      ,fusion.EXM_CARD_PROGRAMS ecp

 WHERE (e.expense_source = 'CREDIT_CARD' or e.expense_source = 'BUSINESS_TRAVEL')

   AND e.expense_report_id = er.expense_report_id(+)

   AND er.expense_status_code(+) NOT IN ('PAID', 'PARTIAL_PAID', 'APPROVAL_COMPLETE', 'INVOICED', 'INVOICE_CANCELED')

   AND (e.expense_report_id IS NULL OR er.expense_report_id IS NOT NULL)

   AND (e.itemization_parent_expense_id IS NULL OR e.itemization_parent_expense_id = -1)

   AND e.credit_card_trxn_id = cc.credit_card_trxn_id

   AND cc.company_account_id = ca.cc_company_account_id(+)

   AND ec.card_id(+) = cc.card_id

   AND NVL(ec.account_type_code,'EMPLOYEE') <> 'COMPANY'

   AND papf.person_id = e.person_id

   AND pu.person_id = e.person_id

   AND ec.card_reference_id = icc.instrid

   AND pu.user_guid = hzp.user_guid(+)

   AND e.expense_id = eed.expense_id

   -- and papf.person_number in ( 1984678,1984999,1978237,1992954,1986569, 1984999 )

   AND ecp.card_program_id = cc.card_program_id

   AND expense_report_num like 'EXP%6386164%'

GROUP BY TRUNC(SYSDATE - cc.billed_date)

        ,TRUNC(SYSDATE - e.start_date)

,papf.person_number

,hzp.person_first_name 

,hzp.person_last_name 

,e.assignment_id

,ecp.card_program_name

,cc.card_program_id

,e.org_id

,e.emp_default_cost_center

,cc.company_account_id

,e.merchant_name

,ec.card_reference_id

,icc.masked_cc_number

,cc.description

,e.emp_default_cost_center

,eed.segment1

,eed.segment2

,eed.segment3

,eed.segment4

,eed.segment5

,cc.billed_date

,cc.billed_amount

,cc.posted_date

,cc.posted_amount

,cc.transaction_date

,cc.transaction_amount

,e.orig_reimbursable_amount

,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',

         'PEND_MGR_APPROVAL', 'APPROVER',

         'SUBMITTED', 'APPROVER',

         'PEND_IND_APPROVAL', 'EMPLOYEE',

         'MGR_REJECTED', 'EMPLOYEE',

         'IND_REJECTED', 'EMPLOYEE',

         'SAVED', 'EMPLOYEE',

         'WITHDRAWN', 'EMPLOYEE',

         'REQUEST_INFO', 'EMPLOYEE',

         'EMPLOYEE')

,ca.payment_currency_code

ORDER BY 1 DESC

Query to get the Approver Details for Expense Report (Doc ID 2279634.1) in Oracle Fusion

SELECT er.expense_report_id

      ,er.expense_report_num

      ,er.expense_report_total

      ,er.reimbursement_currency_code

      ,er.person_id

      ,er.expense_report_date

      ,er.expense_status_code

      ,approval.event

      ,approval.event_performer_id

      ,approval.event_date

      ,approval.approval_level

      ,approval.expense_status_code approval_status

      ,approval.audit_code

      ,approval.audit_return_reason_code

      ,approval.export_reject_code

  FROM exm_expense_reports er

      ,exm_exp_rep_processing approval

 WHERE er.expense_report_id= approval.expense_report_id

   AND er.expense_report_num='EXP00000123456'

ORDER BY approval.approval_level

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 child_account_desc

  FROM fnd_flex_values_tl ffvt2

      ,fnd_flex_values ffv2

      ,fnd_flex_value_hierarchies ffvh

      ,fnd_flex_values_tl ffvt1

      ,fnd_flex_values ffv1

      ,fnd_id_flex_segments fifs

 WHERE fifs.application_id = 101

   AND fifs.id_flex_code = 'GL#'

   AND fifs.id_flex_num = 101

   AND fifs.application_column_name = 'SEGMENT4'

   AND fifs.enabled_flag = 'Y'

   AND ffv1.enabled_flag = 'Y'

   AND ffv1.summary_flag = 'Y'

   AND ffv1.flex_value != 'T'

   AND ffv1.flex_value_set_id = fifs.flex_value_set_id + 0

   AND NVL (TO_CHAR (ffv1.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv1.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffvt1.flex_value_id = ffv1.flex_value_id + 0

   AND ffvt1.language = USERENV ('LANG')

   AND ffvh.flex_value_set_id = fifs.flex_value_set_id + 0

   AND ffvh.parent_flex_value = ffv1.flex_value

   AND ffv2.summary_flag = 'N'

   AND NVL (TO_CHAR (ffv2.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv2.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffv2.flex_value_set_id + 0 = fifs.flex_value_set_id + 0

   AND ffv2.flex_value BETWEEN ffvh.child_flex_value_low AND ffvh.child_flex_value_high

   AND ffv2.enabled_flag = 'Y'

   AND ffvt2.flex_value_id = ffv2.flex_value_id + 0

   AND ffvt2.language = USERENV ('LANG')

ORDER BY ffv1.flex_value, ffv2.flex_value

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number

      ,pastt.user_status assignment_status

  FROM per_all_people_f papf

      ,per_all_assignments_m paam

  ,per_assignment_status_types past

  ,per_assignment_status_types_tl pastt

 WHERE papf.person_id = paam.person_id

   AND paam.assignment_status_type_id = past.assignment_status_type_id

   AND past.assignment_status_type_id = pastt.assignment_status_type_id

   AND pastt.source_lang = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND paam.primary_assignment_flag = 'Y'

   AND paam.assignment_type = 'E'

   and paam.effective_latest_change = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)

   AND papf.person_number = nvl(:p_person_number,papf.person_number)

ORDER BY papf.person_number asc

        ,pastt.user_status 

Query to get Element Entry details in Oracle Fusion HCM

SELECT peef.* 

  FROM per_all_people_f papf

      ,pay_element_entries_f peef

  ,pay_element_types_f pet

 WHERE peef.element_type_id = pet.element_type_id

   AND papf.person_id = peef.person_id

   AND papf.person_number = 'E-12345'

   AND peef.creator_type = 'BEN'

   AND pet.base_element_name= 'XX_ELEMENT_NAME'  

Mandatory Input value for HCM Elements in Oracle Fusion

SELECT petf.element_type_id

      ,pettl.element_name

      ,petf.processing_type Recurring_NonRecurring

      ,petf.effective_start_date

      ,petf.effective_end_date

      ,petf.multiple_entries_allowed_flag

      ,pivf.base_name

      ,pivf.mandatory_flag

  FROM pay_element_types_f petf

      ,pay_element_types_tl pettl

      ,pay_input_values_f pivf

 WHERE petf.element_type_id = pettl.element_type_id

   AND pivf.element_type_id=petf.element_type_id

   AND pettl.element_name = 'XX_ELEMENT_NAME'

   AND pettl.language=USERENV('LANG')

   AND pivf.user_enterable_flag='Y'

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