Oracle EBS: SQL Query to Get Profile Options

 SELECT DISTINCT

    pot.profile_option_name            "PROFILE_CODE",

    pot.user_profile_option_name       "PROFILE_NAME",

    decode(a.level_id, 10001, 'Site', 10002, 'Application',

           10003,

           'Responsibility',

           10004,

           'User',

           10005,

           'Server',

           10006,

           'Organization',

           a.level_id) "LEVEL_IDENTIFIER",

    decode(a.level_id, 10002, e.application_name, 10003, c.responsibility_name,

           10004,

           d.user_name,

           10005,

           f.host

           || '.'

           || f.domain,

           10006,

           g.name,

           '-')        "LEVEL_NAME",

    decode(a.profile_option_value, '1', '1 (may be "Yes")', '2',

           '2 (may be "No")',

           a.profile_option_value) "PF_VALUE"

FROM

    fnd_application_tl         e,

    fnd_user                   d,

    fnd_responsibility_tl      c,

    fnd_profile_option_values  a,

    fnd_profile_options        b,

    fnd_profile_options_tl     pot,

    fnd_nodes                  f,

    hr_all_organization_units  g

WHERE

        1 = 1

--AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')

    AND pot.profile_option_name = b.profile_option_name

    AND b.application_id = a.application_id (+)

    AND b.profile_option_id = a.profile_option_id (+)

    AND a.level_value = c.responsibility_id (+)

    AND a.level_value = d.user_id (+)

    AND a.level_value = e.application_id (+)

    AND a.level_value = f.node_id (+)

    AND a.level_value = g.organization_id (+)

    AND pot.language = 'US'

--AND POT.User_profile_option_name ='AHL: Application Usage Mode'

ORDER BY

    profile_name,

    level_identifier,

    level_name,

    pf_value;

Oracle ERP : Issue Resolution : ORA-29280: invalid directory object

Subject: When we are bursting or creating a .csv file using PLSQL procedure and try to send it on FTP directory or try to open the file from the FTP directory path we are getting the below error message.

Error:

ORA-29280: invalid directory object

Solution:

Oracle suggests using the directory path which is defined under utl_file_dir. the application allows accessing the file from the FTP server.

Use the below query to get UTL FILE Directory path

select * from  V$PARAMETER where NAME like '%utl_file_dir%'

Set path: /s01/oracle/DEV/db/19.3.0/temp/DEV  (at a time run concurrent request)

After setting the suggested path run the concurrent request. The file was generated successfully.

Oracle Fusion: How to Create Inventory Sub-Inventory Transfer Transaction

Subject: Business required to move on-hand stock from one sub inventory to another sub-inventory for that we need to create a sub-inventory transfer.

--Item Details--

Item Code: 04081006.1

Organization: DHY

Sub-Inventory1: C1-R2-150-A-G05

Sub-Inventory2: C2-R2-170-A-G09

Let me check the Item stock before creating the transaction against both sub-inventory.

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management

Click on the right-side panel Task Button > Show Task "Inventory" > Manage Item Quantities.

Sub-Inventory1: G05
Locator: C1-R2-150-A-G05

QTY: 11

Sub-Inventory2: G09

Locator: C2-R2-170-A-G09 

QTY: 8

Let's create the sub-inventory transfer from G05 to G09

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management > Select Task > Show Task "Inventory" > Create Subinventory Transfer.

Enter all the required details.

Date: It must be within the open inventory accounting period.

Type: Subinventory Transfer

Item: 04081006.1

--Source--

Sub-Inventory: G05
Locator: C1-R2-150-A-G05

--Destination--

Sub-Inventory: G09

Locator: C2-R2-170-A-G09 

Qty: 1

Reason: Stock Pick

After entering all the details click on Submit button. 

Now let's check on hand in both sub-inventory locators.

Click on the right-side panel Task Button > Show Task "Inventory" > Manage Item Quantities.

Sub-Inventory1: G05
Locator: C1-R2-150-A-G05

QTY: 10

Sub-Inventory2: G09

Locator: C2-R2-170-A-G09 

QTY: 9

Sun-inventory transfer transaction completed successfully.