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

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 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'

How to add LOV to the Manual Adjustment reasons in Comp off Plan Type ?(Doc ID 2483891.1)

 Use ORA_ANC_COMP_ADJRSN from Manage Common Lookup 

Query to get Vendor Bank Detail in Oracle Fusion

SELECT DISTINCT psv.vendor_id

      ,psv.vendor_name

  ,pssa.vendor_site_id

      ,accts.ext_bank_account_id

      ,bank.party_name bank_name

      ,branch.bank_branch_name

      ,accts.bank_account_type

      ,accts.country_code

      ,accts.bank_account_name

      ,accts.attribute1 ifsc_code

      ,accts.bank_account_num

      ,accts.currency_code

  FROM poz_suppliers_v psv

      ,poz_supplier_sites_all_m pssa

      ,iby_pmt_instr_uses_all uses

      ,iby_external_payees_all payee

      ,iby_ext_bank_accounts accts

      ,hz_parties bank

      ,ce_bank_branches_v branch

 WHERE psv.vendor_id      = pssa.vendor_id

   AND psv.party_id       = payee.payee_party_id

   AND payee.ext_payee_id = uses.ext_pmt_party_id

   AND uses.instrument_id = accts.ext_bank_account_id

   AND accts.bank_id      = bank.party_id

   AND accts.branch_id    = branch.branch_party_id

   --AND payee.payment_function = 'PAYABLES_DISB'

Query to get Asset Details in Oracle Fusion

SELECT asset_number

      ,fab.asset_id

      ,fat.description

      ,fab.asset_type

      ,fcb.segment1||'-'||fcb.segment2 category

      ,fab.tag_number

      ,fb.book_type_code

      ,fabc.book_class

      ,fb.cost

      ,fb.recoverable_cost

      ,(SELECT SUM(deprn_reserve)

  FROM fa_deprn_detail

         WHERE asset_id = fab.asset_id

   AND deprn_run_date = (SELECT MAX(deprn_run_date)

   FROM fa_deprn_detail

  WHERE asset_id = fab.asset_id

) depriciation_cost

  FROM fa_additions_b fab

      ,fa_additions_tl fat

      ,fa_categories_b fcb

      ,fa_books fb

      ,fa_book_controls fabc

 WHERE 1=1

   AND fab.asset_id = fat.asset_id 

   AND fat.language = USERENV('LANG')

   AND fcb.category_id=fab.asset_category_id

   AND fb.asset_id=fab.asset_id

   AND fb.depreciate_flag = 'NO'

   AND fab.asset_number = 123456789

SQL Query to get Item Details in Oracle Fusion

SELECT esi.inventory_item_id

      ,esi.item_number

      ,esi.organization_id

      ,iop.organization_code  

      ,esi.description

      ,uomt.unit_of_measure

      ,esi.item_type

      ,esi.enabled_flag

      ,esi.planner_code planner

      ,esi.safety_stock_planning_method

  FROM inv_org_parameters iop

      ,egp_system_items  esi

      ,inv_units_of_measure_tl uomt

      ,inv_units_of_measure_b uomb

 WHERE 1=1

   AND iop.organization_id = esi.organization_id

   AND uomb.uom_code = esi.primary_uom_code

   AND uomt.unit_of_measure_id (+)= uomb.unit_of_measure_id

   AND uomt.language = 'US'

   AND esi.item_number = 'ITEM_NUM_001'

SQL Query to get the Contexts for Formula Type in Oracle Fusion

SELECT ft.base_formula_type_name

      ,base_context_name 

  FROM ff_contexts_vl con

      ,ff_ftype_context_usages fcu

  ,ff_formula_types_vl ft

 WHERE ft.formula_type_id = ft.formula_type_id

   AND fcu.formula_type_id = ft.formula_type_id

   AND con.context_id = fcu.context_id

   AND ft.base_formula_type_name = :p_formula_type_name

SQL Query to get the contexts of a DBI in Oracle Fusion

SELECT dbi.user_name

      ,base_context_name

  FROM ff_database_items_vl dbi

      ,ff_user_entities_vl userent

      ,ff_routes_vl routes

      ,ff_route_context_usages rcu

      ,ff_contexts_vl con

 WHERE dbi.user_entity_id = userent.user_entity_id

   AND userent.route_id = routes.route_id

   AND routes.route_id = rcu.route_id

   AND con.context_id = rcu.context_id

   AND dbi.base_user_name = :p_dbi_name

SQL Query to find all DBI's based on ANC Tables in Oracle Fusion

SELECT d.base_user_name dbi_name

      ,d.data_type dbi_data_type

      ,(SELECT listagg ('<' || rcu.sequence_no || ',' || c.base_context_name || '>', ',')

   within GROUP (ORDER BY rcu.sequence_no)

          FROM ff_route_context_usages rcu

              ,ff_contexts_b c

         WHERE rcu.route_id = r.route_id

           AND rcu.context_id = c.context_id

   ) route_context_usages

  FROM ff_database_items_b d

      ,ff_user_entities_b u

      ,ff_routes_b r

 WHERE UPPER(d.base_user_name) LIKE 'ANC%'

   AND d.user_entity_id = u.user_entity_id

   AND r.route_id = u.route_id

CLOB column splitting(one empty row and other actual data) data into multiple rows in Oracle Fusion

When working with CLOB columns in Fusion Reports we may face challenges like data splitting into multiple rows for the CLOB Column.

We faced challenges when generating report in Excel Output. The Description column showing one empty row followed by actual data as shown below.

Data Model Query:

When pasted in Excel File:

To overcome this issue convert clob value into Character using TO_CHAR function.

Ex: TO_CHAR(DESCRIPTION) 
here DESCRIPTION is a CLOB Column 

Thousand Separator((,)comma) in e-Text Report

AMOUNT column want to print thousand separator in Oracle Fusion e-Text Report use  Number, ###,###.00

Number, ###,###.00

R, ' '

AMOUNT

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS.ADDR_ELEMENT_ATTRIBUTE2)

Query To Fetch AP Invoice Details From SO Number(Doc ID 2949013.1)

SELECT dh.source_order_number

      ,df.source_line_number as so_line_number

  ,df.fulfill_line_number 

  ,ddr.doc_user_key as po_number

  ,ai.invoice_num

  ,ail.quantity_invoiced

  ,ail.amount

  ,ail.line_number as ap_invoice_line_number

  FROM fusion.ap_invoices_all ai

      ,fusion.ap_invoice_lines_all ail

  ,fusion.doo_document_references ddr

  ,fusion.doo_headers_all dh

  ,fusion.doo_fulfill_lines_all df

 WHERE ai.invoice_id = ail.invoice_id

   AND dh.source_order_number = <sales_order_number>

   AND ddr.doc_id = TO_CHAR(ai.po_header_id)

   AND ddr.fulfill_line_id = df.fulfill_line_id

   AND df.header_id = dh.header_id

Query to get Item Category Details in Oracle Fusion

SELECT cba.bank_account_name

      ,cba.bank_account_id

      ,cba.bank_account_name_alt

      ,cba.bank_account_num

      ,cba.multi_currency_allowed_flag

      ,cba.zero_amount_allowed

      ,cba.account_classification

      ,cbb.bank_name

      ,cba.bank_id

      ,cbb.bank_number

      ,cbb.bank_branch_type

      ,cbb.bank_branch_name

      ,cba.bank_branch_id

      ,cbb.bank_branch_number

      ,cbb.eft_swift_code

      ,cbb.description bank_description

      ,cba.currency_code

      ,cbb.address_line1

  ,cbb.address_line2

      ,cbb.city

      ,cbb.county

      ,cbb.state

      ,cbb.zip_code

      ,cbb.country

      ,hou.name

      ,gcf.concatenated_segments

      ,cba.ap_use_allowed_flag

      ,cba.ar_use_allowed_flag

      ,cba.xtr_use_allowed_flag

      ,cba.pay_use_allowed_flag

  FROM ce_bank_accounts cba

      ,ce_bank_acct_uses_all bau

      ,cefv_bank_branches cbb

      ,hr_operating_units hou

      ,gl_code_combinations_kfv gcf

 WHERE cba.bank_account_id = bau.bank_account_id

   AND cba.bank_branch_id = cbb.bank_branch_id

   AND hou.organization_id = bau.org_id

   AND cba.asset_code_combination_id = gcf.code_combination_id

   AND (

cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE)

       )

   AND hou.name = 'XX Operating Unit'

ORDER BY cba.bank_account_num

Query for AR Sales Representative Name in Oracle Fusion

SELECT hp.party_name sales_representative

  FROM ra_customer_trx_all rcta

      ,jtf_rs_salesreps jrs

  ,hz_parties hp

 WHERE rcta.primary_resource_salesrep_id=jrs.resource_salesrep_id

   AND jrs.resource_id=hp.party_id