Oracle Fusion - Advance Collection Diagnostics

--------------------------------------------------------

List of Invoices to be sent for dunning

--------------------------------------------------------

select hp.party_name, hca.account_number, rca.trx_number,hou.name org_name,  ida.*
  from IEX_DELINQUENCIES_ALL  ida, hz_cust_accounts hca, hz_parties hp, ra_customer_trx_all rca, hr_operating_units hou
 where ida.cust_account_id = hca.cust_account_id 
   --and hca.account_number = 'XXXXXXX' 
   and hp.party_id = hca.party_id
   and hp.party_name = 'XXXXXXXX'
   and rca.customer_trx_id = ida.transaction_id
   and hou.organization_id = ida.org_id
   and ida.STATUS = 'DELINQUENT';

--------------------------------------------

Solution 1
--------------------------------------------

--A dunning letter is not generated because there are no valid transactions to be included in the dunning letter. (Doc ID 2875705.1)
--1. Tools > Scheduled Processes > Schedule the following ESS processes in the following sequence:
----a. Refresh Receivables Transactions for Customer Account Summaries
----b. Collections Delinquency Management
----c. Collections Scoring and Strategy Assignment
----d. Update Collections Summary Data
--4. Retest the issue.
--5. Migrate the solution as appropriate to other environments.

--------------------------------------------
Solution 2

--------------------------------------------

--Oracle Fusion Advanced Collections Cloud Service - Version 11.13.19.04.0 and later
--Symptoms
------Collections Scoring & Strategy Assignment / Collections Delinquency Management not processing
------Upon running the Collections Jobs, both Collections Scoring and Strategy Assignment as well as Collections Delinquency Management. 
------When running the job, no data is found nor is any item scored. Ran Refresh Receivables Transactions process to update delinquency status.
--Cause
------The issue is caused by the following setup:
------The Delinquency Status Determination, Pre-Delinquency Status Determination , Quick Delinquency Status Determination , 
------Predelinquency Status Determination by Segment scoring are all disabled within Manage Collections Scoring - this is why nothing has been scored.
------
------The Collections Delinquency Management process identifies the delinquency and pre-delinquency statuses for your customers, customer transactions, accounts, and sites accordingly based on the business level and pre-delinquency options.

--------------------------------------------

Solution 3

--------------------------------------------

------The issue will be resolved once this setup is corrected:
------1. The Delinquency Status Determination, Pre-Delinquency Status Determination , Quick Delinquency Status Determination , 
---------Predelinquency Status Determination by Segment scoring should be enabled within Manage Collections Scoring
------2. Run Collections Scoring and Strategy Assignment
------3. Run Collections Delinquency Management


--------------------------------------------
Solution 4
--------------------------------------------

--To diagnose, run "Validate Customer Dunning Setup Report" Job and if required run 'Dunning Diagnostic' and 'Collections Setup Diagnostic'
--No Dunning Site Found, Looping through delinquent Billto Sites.

Oracle Fusion - SQL - Conversion Rate GL_DAILY_CONVERSION_TYPES

SELECT conversion_type

     , user_conversion_type 

  FROM gl_daily_conversion_types

SELECT * 

  FROM gl_daily_rates

Oracle R12 - LE Entity

This summary is not available. Please click here to view the post.

Oracle Fusion - SQL - AP Invoice Interface

Status of Interface by load_request_id ========================================= 

SELECT aii.load_request_id 

     , aii.status 

     , aii.operating_unit 

     , aii.source 

     , aii.invoice_type_lookup_code 

     , count(distinct aii.invoice_id) inv_count 

     , count(distinct aili.invoice_line_id) line_count 

  FROM ap_invoices_interface aii 

     , ap_invoice_lines_interface aili 

 WHERE aii.invoice_id = aili.invoice_id 

   AND aii.source = nvl(:p_source,aii.source) 

   AND aii.operating_unit = nvl(:p_operating_unit,aii.operating_unit)

   AND aii.load_request_id = nvl(:p_load_request_id,aii.load_request_id) 

 GROUP BY aii.status 

        , aii.operating_unit 

        , aii.source 

        , aii.invoice_type_lookup_code 

        , aii.load_request_id 

 order by 1,2,3,4,5 

AP Invoice Interface Errors 

=========================== 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv 

 WHERE apr.parent_id = inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY 

        apr.load_request_id

       ,apr.parent_table

       ,apr.reject_lookup_code 

UNION 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv

     , ap_invoice_lines_interface ln 

 WHERE apr.parent_id =ln.invoice_line_id 

   AND ln.invoice_id= inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY apr.load_request_id

         ,apr.parent_table

         ,apr.reject_lookup_code

Purge Interface Tables using program "Purge Interface Tables"

=======================================================

=> Import Payables Invoices

=> Load Request ID

"Load Interface File for Import" Program to load FBDI File

=======================================================

=> Import Payables Invoices

=> ZIP file created by FBDI Template

"Import Payables Invoices" program to load AP invoices from interface to Main tables

=======================================================

Oracle Fusion - SQL - Item Category - Product Categories - Catalogs

Item Categories =============== SELECT * FROM egp_item_categories c Item Category Assignment ======================== SELECT i.item_number,i.ORGANIZATION_ID,cat.CATEGORY_NAME FROM EGP_SYSTEM_ITEMS_B i ,egp_item_categories c,egp_categories_tl cat where i.INVENTORY_ITEM_ID=c.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=c.ORGANIZATION_ID and c.CATEGORY_ID=cat.CATEGORY_ID and cat.LANGUAGE = 'US' List of Item Categories ======================= SELECT CATEGORY_ID, DESCRIPTION, CATEGORY_NAME, SOURCE_LANG, OBJECT_VERSION_NUMBER FROM EGP_CATEGORIES_TL ECT WHERE LANGUAGE = 'US' order by 3

The Create Accounting process ended in an error because you have global permissions for the Subledger Source Transaction data resource (Oracle Fusion)

Identify the role using the following query and then remove the role from user to run the create accounting.

select distinct role_name 

  from fnd_session_role_sets 

 where role_guid in 

          (select grantee_key 

             from fusion.fnd_grants 

            where object_id in 

                      (select object_id

                         from fnd_objects 

                        where obj_name in ('XLA_SUBLEDGERS'

                                         , 'XLA_GL_LEDGERS'

                                         , 'XLA_TRANSACTION_ENTITIES'

                                          )

                      ) 

              and INSTANCE_TYPE = 'GLOBAL'

          )

SQL Query for List of Concurrent Programs with Request Group and Responsibility Name

---List of Concurrent Programs with Request Group and Responsibility Name

 SELECT DISTINCT fnrtl.responsibility_name

              , frg.request_group_name

              , fcpl.user_concurrent_program_name

              , fcp.concurrent_program_name

              , fapp.application_name

              , fl.meaning execution_type

              , fe.executable_name

              , fe.execution_file_name

  FROM apps.fnd_request_groups         frg,

       apps.fnd_application_tl         fapp,

       apps.fnd_request_group_units    frgu,a

       apps.fnd_concurrent_programs    fcp,

       apps.fnd_concurrent_programs_tl fcpl,

       apps.fnd_responsibility         fnr,

       apps.fnd_responsibility_tl      fnrtl,

       apps.fnd_executables fe,

       apps.fnd_lookups fl

 WHERE     frg.application_id = fapp.application_id

       AND frg.application_id = frgu.application_id

       AND frg.request_group_id = frgu.request_group_id

       AND frg.request_group_id = fnr.request_group_id

       AND fnr.responsibility_id = fnrtl.responsibility_id

       AND frgu.request_unit_id = fcp.concurrent_program_id

       AND frgu.unit_application_id = fcp.application_id

       AND fcp.concurrent_program_id = fcpl.concurrent_program_id

       --AND fcpl.user_concurrent_program_name LIKE   '<concurrent program name>'

       AND frg.application_short_code in('XX')

       AND fe.executable_id = fcp.executable_id

       AND fcp.execution_method_code = fe.execution_method_code

       AND fl.lookup_code = fe.execution_method_code

       AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'

       AND fnrtl.LANGUAGE = 'US'

       AND fapp.LANGUAGE = 'US'

       ORDER BY 3;

Oracle Fusion - Oracle Project Resource Allocation

 SELECT PA.project_id

       ,ppa.segment1 project_code

       ,ppnf.person_id

       ,START_DATE_ACTIVE

       ,END_DATE_ACTIVE 

       ,(ppnf.first_name||' '||ppnf.last_name) EMP_Name
  FROM PJF_PROJECT_PARTIES  PA, per_person_names_f ppnf
     , PJF_PROJECTS_ALL_VL PPA
 WHERE pa.project_id = ppa.project_id
   AND ppa.segment1 = 'PROJECT_CODE'
   AND ppnf.person_id = pa.RESOURCE_SOURCE_ID
   AND ppnf.name_type = 'GLOBAL'
   AND SYSDATE BETWEEN TRUNC(ppnf.effective_start_date) 

                   AND TRUNC(ppnf.effective_end_date) 

How to create ESS Job for the BI report in Oracle Fusion

Go to Setting and Action from Top Corner and choose “Setup & Maintenance”

ð Search task "Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications" and click on the hyperlink

ð  Create New with the following values

o   XX OTL Missing Timecard Alert to Employee

o   OTLEMPALERTMISSINGTIME

o   EarHcmEss

o   BIPJobType

o   Bursting => Yes

o   Path => /financials/

o   Add Parameter

§  Timecard Till Date

o   Report ID

§  /Custom/Human Capital Management/XX OTL Missing Timecard EMP Alert Report.xdo

ð  Save

ð  Go to Home Navigation => Tools=> Schedule Processes

o   Find the JOB and submit it as per the parameters

How to change date format in BI report RTF

Use the following to change the date format 

<?xdofx:to_char(to_date(substr(TO_CHECK_DATE,1,10),’YYYY-MM-DD’),’DD-Mon-YYYY’)?>

Query to get basic employee details in Oracle ERP fusion

--Query to get basic employee details in Oracle ERP fusion

--R12:
SELECT (SELECT name 

          FROM hr_all_organization_units 

         WHERE organization_id = paaf.business_group_id

       ) business_group
      ,papf1.employee_number
      ,papf1.first_name
      ,papf1.last_name
      ,papf1.full_name
      ,papf1.sex gender
      ,papf1.effective_start_date
      ,papf1.effective_end_date
      ,papf1.email_address
      ,(SELECT hloc.location_code 

          FROM hr_locations_all hloc 

         WHERE hloc.location_id = paaf.location_id 

       ) employee_location
      ,papf2.employee_number supervisor_employee_number
      ,papf2.full_name supervisor_name
      ,(SELECT pp.phone_number 

          FROM per_phones pp 

         WHERE papf1.person_id = pp.parent_id 

           AND pp.parent_table = 'PER_ALL_PEOPLE_F'
           AND pp.date_from = papf1.effective_start_date 

           AND NVL(pp.date_to, papf1.effective_end_date) 

              = papf1.effective_end_date

       ) work_telephone_number
      ,(SELECT name FROM per_jobs 

         WHERE job_id = paaf.job_id ) job_name
      ,(SELECT name FROM per_all_positions 

         WHERE position_id = paaf.position_id ) position_name
      ,(SELECT concatenated_segments FROM gl_code_combinations_kfv 

         WHERE code_combination_id = paaf.default_code_comb_id 

       ) default_charge_account

      ,(SELECT name FROM gl_ledgers 

         WHERE ledger_id = paaf.set_of_books_id ) ledger_name
      ,(SELECT pd.name FROM PER_DEPARTMENTS pd 

         WHERE organization_id = paaf.organization_id 

           AND trunc(sysdate) BETWEEN pd.effective_start_date 

                              AND nvl(pd.effective_end_date, sysdate+1) 

       )department
 FROM per_all_people_f papf1,
      per_all_people_f papf2,
      per_all_assignments_f paaf      
WHERE papf1.person_id(+) = paaf.person_id
  AND papf2.person_id(+) = paaf.supervisor_id
  AND paaf.primary_flag  = 'Y'
  AND papf1.current_employee_flag = 'Y'
  AND papf2.current_employee_flag = 'Y'
  
--Fusion
SELECT papf.person_number employee_number

     , pjft.name jobname

     , paam.assignment_number emp_assignment_number
     , gcc.segment1||'-'||gcc.segment2||'-'||

       gcc.segment3||'-'||gcc.segment4||'-'||

       gcc.segment5||'-'||gcc.segment6 exp_account

     , paam.effective_start_date assg_effective_start_date

     , pea.email_address,ppnf.full_name supervisor_name

     , papf2.person_number supervisor_number

     , hauft.name businessunit

     , papf.start_date hire_date

     , (SELECT pd.name FROM per_departments pd 

         WHERE organization_id = paaf.organization_id 

           AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date 

                           AND NVL(pd.effective_end_date,SYSDATE+1) 

       ) department
  FROM per_all_people_f papf
     , per_all_people_f papf2
     , per_all_assignments_m paam
     , per_jobs_f pjf
     , per_jobs_f_tl pjft
     , gl_code_combinations gcc
     , per_email_addresses pea
     , per_assignment_supervisors_f pasf
     , per_person_names_f ppnf
     , hr_organization_units_f_tl hauft
 WHERE papf.person_id = paam.person_id
   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 paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date 

                          AND paam.effective_end_date
   AND paam.job_id = pjf.job_id
   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date 

                          AND pjf.effective_end_date
   AND PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
   AND papf.person_id = pasf.person_id
   AND pasf.manager_type = 'LINE_MANAGER'
   AND ppnf.person_id = pasf.manager_id
   AND papf2.person_id = pasf.manager_id
   AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date 

                          AND papf2.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pasf.effective_start_date 

                          AND pasf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date 

                          AND ppnf.effective_end_date
   AND pjf.job_id = pjft.job_id
   AND pjft.language = 'US'
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date 

                          AND pjft.effective_end_date
   AND hauft.organization_id = paam.business_unit_id
 ORDER BY papf.person_number

Query to get DFF for Project Table in Fusion

--Query to get DFF for Project Table in Fusion

SELECT ffv.descriptive_flexfield_name DFF_Name,

ffv.application_table_name App_Table_Name,

ffv.title ,

ap.application_name ,

ffc.descriptive_flex_context_code Context_Code,

ffc.descriptive_flex_context_name Context_Name,

ffc.description Context_Desc,

ffc.enabled_flag Context_Enable_Flag,

att.column_seq_num Segment_Number,

att.form_left_prompt Segment_Name,

att.application_column_name app_column_name,

fvs.flex_value_set_name Value_Set_Name,

att.display_flag ,

att.enabled_flag ,

att.required_flag 

FROM fnd_descriptive_flexs_vl ffv,

     fnd_descr_flex_contexts_vl ffc,

     fnd_descr_flex_col_usage_vl att,

     fnd_flex_value_sets fvs,

     fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

AND ap.application_id=ffv.application_id

AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name

AND ffv.application_id = ffc.application_id

AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code

AND fvs.flex_value_set_id=att.flex_value_set_id

AND ffv.application_table_name = 'PJF_PROJECTS_ALL_B'

ORDER BY att.column_seq_num

Query-Oracle-SQL- Get All Monday for the date range

Query-Oracle-SQL- Get All Monday for the date range 

SELECT 

  to_char(dt, 'mm/dd/yyyy') || '-' || 

  to_char(dt + 6, 'mm/dd/yyyy') time_card_datetime 

  FROM 

  (

    SELECT 

      TO_DATE(

        to_char(:P_FROM_DATE, 'mm/dd/yyyy'), 'mm/dd/yyyy'

               ) + (LEVEL -1) dt 

      FROM DUAL CONNECT BY 

           LEVEL <= TO_DATE (to_char(:P_TO_DATE, 'mm/dd/yyyy'), 

                                    'mm/dd/yyyy') 

                  - TO_DATE (to_char(:P_FROM_DATE, 'mm/dd/yyyy'), 

                                    'mm/dd/yyyy'

      )

  ) main_tab 

WHERE TRIM ( TO_CHAR (dt, 'DAY')) = '1'

Oracle Fusion - SQL - Project Plan Employee Assignment

SELECT prj.project_id, pre.person_id ,PPE.planning_start_date ,PPE.planning_end_date FROM pjf_class_codes_tl pcct , pjf_project_classes ppc , PJO_PLANNING_ELEMENTS PPE , PJF_RBS_ELEMENTS PRE , PJO_PLAN_TYPES_B PPT , PJO_PLAN_VERSIONS_B PPV , pjo_plan_lines PPL , pjf_projects_all_b prj WHERE pcct.language = 'US' AND pcct.class_code_id = ppc.class_code_id AND ppc.project_id = prj.project_id AND PPV.project_id = PRJ.project_id AND PPE.rbs_element_id = PRE.rbs_element_id AND PPT.PLAN_TYPE_CODE = 'PROJECT_PLAN' AND SYSDATE BETWEEN NVL(PPT.start_date,SYSDATE-1) AND NVL(PPT.end_date,SYSDATE+1) AND PPT.plan_type_id = PPV.plan_type_id AND PPV.plan_version_id = PPL.plan_version_id AND ppl.planning_element_id = ppe.planning_element_id AND PPV.CURRENT_PLAN_STATUS_FLAG = 'Y' AND PPV.PLAN_STATUS_CODE = 'B' AND PPE.planning_start_date <= :P_TO_DATE AND PPE.planning_end_date >= :P_FROM_DATE AND pre.person_id IS NOT NULL ORDER BY 1,2,3

Oracle Fusion - OIC - Helpful Points

OIC Helpful/Reference Points

Q1 - How to pass current date (SYSDATE) in required format in OIC

A1: fn:current-dateTime() => Send current date

To change the format, we can use the following

xp20:format-dateTime(DateTime,Format)

Example: xp20:format-dateTime(string(fn:current-dateTime()),'[MNn,3-3]-[YYYY]')

Q2 - How to add months in the date stored in the string variable in OIC integration?

A2: string(xsd:date('2016-12-25') + xsd:yearMonthDuration("P1M"))

Q3 - How to get the date by adding days to the current date in OIC integration?

A3: string(fn:current-dateTime()-xsd:dayTimeDuration(‘P30D’))

Oracle EBS - Multi Org Tables - MO_GLOBAL Set Policy / Org - AOL Tables

 
Setup Environment on Toad/SQL Developer
-------------------------------------------
BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',101); END;
Note: SQL Developer is not providing data after that setting then check NLS_LANG parameter

HR_ALL_ORGANIZATION_UNITS
HR_OPERATING_UNITS
ORG_ORGANIZATION_DEFINITIONS => Inventory Org for OU
HRFV_BUSINESS_GROUPS => Business Group
HR_LEGAL_ENTITIES => LE
GL_LEDGERS => Ledger
GL_PERIODS_V
GL_PERIODS
GL_PERIOD_TYPES
FND_CURRENCIES
GL_CODE_COMBINATIONS
GLFV_CHARTS_OF_ACCOUNTS => Chart of accounts
AOL Tables
=====================
AD_APPLIED_PATCHES
AD_BUGS
FND_ENV_CONTEXT
FND_PRODUCT_GROUPS
----
FND_APPLICATION
FND_APPLICATION_VL
----
FND_CONCURRENT_PROGRAMS_TL
FND_CONCURRENT_REQUESTS
----
FND_DESCRIPTIVE_FLEXS_VL
FND_DESCR_FLEX_COL_USAGE_VL
FND_DESCR_FLEX_CONTEXTS_VL
----
FND_FLEX_VALUE_SETS
FND_FLEX_VALUE_SETS
FND_FLEX_VALUES
----
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_ID_FLEX_STRUCTURES_TL
----
FND_LOOKUP_TYPES
FND_LOOKUP_VALUES
----
FND_PROFILE_OPTION_VALUES
FND_PROFILE_OPTIONS
----
FND_REQUEST_GROUP_UNITS
FND_REQUEST_GROUPS
----
FND_REQUEST_SETS_TL
----
FND_RESPONSIBILITY
FND_RESPONSIBILITY_TL
FND_SEGMENT_ATTRIBUTE_VALUES

FND_USER

Query to get values of the Independent/Dependent Value Set

The query will provide the value set values entered from the front-end. For query-based value sets, we need to use direct queries.

SELECT ffvs.flex_value_set_id

     , ffvs.flex_value_set_name

     , ffvs.description 
     , ffvs.validation_type

     , ffv.flex_value

     , ffvt.description

     , ffv.enabled_flag
     , ffv.last_update_date

     , ffv.last_updated_by
FROM fnd_flex_value_sets ffvs

   , fnd_flex_values ffv

   , fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
  and ffv.flex_value_id = ffvt.flex_value_id
  and ffvt.language = 'US'
  and flex_value_set_name in ('PROJECT_TYPE')
ORDER BY flex_value_set_name, flex_value asc
 

Query - Get DFF Structure Information

Following is the query, which can provide the DFF structure on a specific table in Oracle EBS/Fusion.

 

SELECT ffv.descriptive_flexfield_name DFF_Name,
ffv.application_table_name App_Table_Name,
ffv.title ,
ap.application_name ,
ffc.descriptive_flex_context_code Context_Code,
ffc.descriptive_flex_context_name Context_Name,
ffc.description Context_Desc,
ffc.enabled_flag Context_Enable_Flag,
att.column_seq_num Segment_Number,
att.form_left_prompt Segment_Name,
att.application_column_name app_column_name,
fvs.flex_value_set_name Value_Set_Name,
att.display_flag ,
att.enabled_flag ,
att.required_flag 
FROM fnd_descriptive_flexs_vl ffv,
     fnd_descr_flex_contexts_vl ffc,
     fnd_descr_flex_col_usage_vl att,
     fnd_flex_value_sets fvs,
     fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.application_table_name like 'PJF_PROJECTS_ALL%'
ORDER BY att.column_seq_num

Oracle Apps - SQLDEVELOPER is not returning data from Oracle Standard Policy Based Tables even after setting Policy Context

Sometimes SQLDEVELOPER is not returning data from Oracle Standard Policy Based Tables even after setting the policy context

i.e. 

MO_GLOBAL.SET_POLICY_CONTEXT('S', 83);

select * from org_organization_definitions; is returning 0 results

Proposed Solutions: 

Add this line in sqldeveloper.conf file and try one more time

 

AddVMOption -Doracle.net.disableOob=true

Oracle EBS - Query to get responsibility for each OU

Query mostly required when we have to run program from backend for each responsibility

SELECT fpo.profile_option_name

     , frt.responsibility_name resp_name

     , frt.application_id resp_appl_id

     , frt.responsibility_id resp_id

     , fp.profile_option_value org_id

     , fu.user_id

  FROM FND_PROFILE_OPTION_VALUES fp

     , fnd_responsibility_tl frt

     , fnd_profile_options fpo

     , fnd_user fu

 WHERE fp.level_id = 10003 

   AND fp.level_value = frt.responsibility_id

   AND fp.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = 'ORG_ID'

   AND fu.user_name = 'SYSADMIN'

   AND frt.responsibility_name like '%Superuser%'

ORDER BY  fp.profile_option_value,frt.responsibility_name;