Oracle EBS: Setup for Auto Approve Purchase Requisition and Purchase Order

Subject: We need to do setup for auto approve PR and PO.

Step1: We need to check which Job is assigned to the PR/PO Preparare.

Navigation: Global HRMS Manager > People > Enter and Maintain > Search your User Name > Click on Assignment Button.

Note: IF the PR/PO Preparare not have registered in Global HRMS as an employee than He/She can not create PR/PR

Step2: Check the Oracle USer has assigned the Employee User or Not.

Navigation: System Administrator > Security > User > Define > Search with User Name.

We can check the Global HRMS person assigned to Oracle User. If not assigned please assign it.

Step3: Need to assign User Job for Approval Assignment.

Navigation: Purchasing Super User > Setup > Approval > Approval Assignment > Search with Job.

Add the document type for 'Approve Purchase Requsition' & 'Approve Standard Purchase Order'

Now prepare PR and PO it will automatically be approved after creation. 

Oracle PLSQL : How to print error/success transaction detail in log and output file of the concurrent program?

 Subject: How to print error/success transaction detail in log and output file of the concurrent program.

Resolution:

We can use the below syntax in plsql code to generate log and output file with transaction details. User can append any number of transaction fields in the same syntax line using pipe seperator.

--FND Log--

FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: '||l_error);

--FND Output--

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,xxtlm_rec.inventory_item_id||'  Item Updated Successfully');

Oracle Fusion: How to get Fixed Asset Major and Minor Category Description

 --Major Category --

SELECT fc.segment1,(SELECT 

    ffvt.description

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          = USERENV('LANG')

AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS'  --Enter Major Category Value Set Name

AND ffv.flex_value=fc.segment1) DESC

FROM fa_categories_vl

--Minor Category--

SELECT fc.segment1,(SELECT 

    ffvt.description

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          = USERENV('LANG')

AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS' --Enter Minor Category Value Set Name

AND ffv.flex_value=fc.segment2) DESC

FROM fa_categories_vl

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;