How to rectify error "More than one user has been assigned the Marketing Default Approver role"

 This error is the standard system functionality, only one resource should be assigned to Marketing Default Approver Role

 Use the following SQL query to check the setup
 
 SELECT a.role_resource_id ,
   a.role_name,
   b.resource_name ,
   a.ROLE_TYPE_CODE,
   a.role_id
 FROM jtf_rs_defresroles_vl a,
   ams_jtf_rs_emp_v b
 WHERE a.role_type_code  IN ('MKTGAPPR','AMSAPPR')
 AND a.role_resource_type = 'RS_INDIVIDUAL'
 AND a.delete_flag        = 'N'
 AND TRUNC(SYSDATE) BETWEEN TRUNC(a.res_rl_start_date) AND
 TRUNC(NVL(a.res_rl_end_date,SYSDATE))
 AND a.role_resource_id = b.resource_id;

 Remove the duplicate and assign only one user to the Role Marketing Default Approver and try the flow again.

Query to find the application id of a product in Oracle Applications

SELECT application_id,
  application_name
FROM fnd_application_tl
WHERE application_name ='Trade Management';

FND_GLOBAL.APPS_INITIALIZE - Setting up Applications context for Oracle Applications

Use the Procedure FND_GLOBAL.APPS_INITIALIZE to set the applications context in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections

procedure APPS_INITIALIZE(
        user_id         IN number
       ,resp_id         IN number
       ,resp_appl_id         IN number
       ,security_group_id     IN number);

USER_ID - The User ID number.

RESP_ID - The ID number of the responsibility.

RESP_APPL_ID - The ID number of the application to which the responsibility belongs.

SECURITY_GROUP_ID - The ID number of the security group. This argument is automatically defaulted by the API. The caller should not pass a value for it.

Example

fnd_global.APPS_INITIALIZE (1002795,22371,682);


You can also use sql like the following to get the values from sqlplus (as apps):

SELECT application_id,
  Responsibility_id,
  responsibility_name
FROM fnd_responsibility_vl
WHERE responsibility_name LIKE 'Oracle%Trade%';

- change 'Oracle%Trade%' to identify your responsibility.

select user_id, user_name
from fnd_user
where user_name='TRADEMGR'

Request only offer created in Trade Management is not getting applied in Order Management

Please note that when you choose "Request only " in offer you have to choose it manually in sales order. Please follow the following step

  1) Enter Order line
  2) Click on Action Button
  3) Choose "Promotion and Payments"
  4)  Query for the offer created manually., choose the same
  5) Click on Apply
  6) Go ahead once again to View Adjustment and find that the offer is applied.

How to create, enable or disable a descriptive flex fields (DFF)?

1. Go to Responsibility: Application Developer > Flex field > Descriptive > Segment > F11 to search.
2. Choose Application > Marketing, Choose Title >Budget
3. Find the Global Data Element populated
4. Now Uncheck the field "Freeze Flexfield Definition"
5. Click on Segments button
6. Find the value of the Segments created
7. Now uncheck the Displayed and Enabled buttons
8. Come back to the original screen and Check the "Freeze Flexfield Definition"
9. CC job gets fired wait for it to complete.(Compile)
10.Bounce the Middle tier and re launch the application.

Query to get the Profile option values at all levels

Execute the following query with valid value for any of the following columns.
profile_option_id
profile_option_name
user_profile_option_name

SELECT *
FROM
  (SELECT po.profile_option_name "NAME",
    po.user_profile_option_name "FRIENDLYNAME",
    po.description "DESCRIPTION",
    DECODE(TO_CHAR(pov.level_id), '10001', 'SITE', '10002', 'APPLICATION', '10003', 'RESPONSIBILITY', '10004', 'USER', '???') "LEV",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', app.application_id, '10003', rsp.responsibility_id, '10004', usr.user_id, '???') "CONTEXTID",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', app.application_name, '10003', rsp.responsibility_name, '10004', usr.user_name, '???') "CONTEXT",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', '', '10003', rsp.application_id, '10004', to_number(NULL), '???') "RESPAPPID",
    pov.profile_option_value "VALUE",
    fpc.description category_name,
    po.sql_validation
  FROM fnd_profile_options_vl po,
    fnd_profile_option_values pov,
    fnd_user usr,
    fnd_application_tl app,
    fnd_responsibility_tl rsp,
    fnd_profile_cats_tl fpc,
    fnd_profile_cat_options fpco
  WHERE pov.application_id                   = po.application_id
  AND pov.profile_option_id                  = po.profile_option_id
  AND usr.user_id (+)                        = pov.level_value
  AND rsp.application_id (+)                 = pov.level_value_application_id
  AND rsp.responsibility_id (+)              = pov.level_value
  AND app.application_id (+)                 = pov.level_value
  AND fpco.category_name                     = fpc.name (+)
  AND fpco.profile_option_id (+)             = po.profile_option_id
  AND fpco.profile_option_application_id (+) = po.application_id
    --AND po.profile_option_id = <>
    --AND po.profile_option_name = '<>'
  AND po.user_profile_option_name = <>
    --It is language dependent and gets stored in table  FND_PROFILE_OPTIONS_TL
  AND app.LANGUAGE (+) = USERENV('LANG')
  AND rsp.LANGUAGE (+) = USERENV('LANG')
  ) QRSLT
ORDER BY FRIENDLYNAME ASC

Attribute Attribute is invalid. Please re-enter - Pricing Error

Set the profile Option QP: Pricing Transaction Entity to Order Fulfillment

This will solve the issue

[Profile] FND: Message Level Threshold

FND: Message Level Threshold is used for debugging issues.

The internal name of the profile is FND_AS_MSG_LEVEL_THRESHOLD.

In the FND code tree, its value is checked in only one place: FND_MSG_PUB.Check_Msg_Level() function.
This function is used to check if the level of the message, that a developer wants to write to the message table, is higher or equal to the FND: Message Level Threshold value or not. If the function returns TRUE (message level >= FND_AS_MSG_LEVEL_THRESHOLD), then the developer should go ahead and write the message to the message table else the developer should should skip writing this message. The value of FND: Message Level Threshold is determined using the lookup_type Message Level Threshold which has the following lookup_codes or possible values: 

10 = Debug Low 
20 = Debug Medium 
30 = Debug High 
40 = Success 
50 = Error 
60 = Unexpected Error 

So, if FND: Message Level Threshold is set to 10, then it is likely that all messages that a developer wanted to write to the message table will get written. A value of 60 would only write Unexpected Error messages to the message table. The default value in FND_MSG_PUB.Check_Msg_Level() for FND: Message Level Threshold is 40 or Success in the absence of a set profile option value.

How to set Default Marketing Approver role

1.Go to Responsibility, CRM Resource Manager, Vision Enterprises, Maintain Resources -> Resources Forms UI.
2.Find the resource by name. Click on Resource Details.
3.In Roles tab, add a new row by selecting  Role Type -> "Default Marketing Approver", Role -> "Default Marketing Approver Role", start date and end date.

How to find patchset level of a particular product in an instance

1. Open any form for the product(say AR).

2. Go to Tools -> About AR

3. patch set level will be displayed here.

Other way is to execute the below query

SELECT patch_level

FROM fnd_product_installations

WHERE patch_level LIKE '11i.AR%';

Setting up Reporting Currency Ledger and Secondary Ledger in General Ledger.

Setting up Reporting Currency Ledger:

1. Responsibility : General Ledger Vision Operations(USA), Setup -> Financials -> Accounting Setup Manager and Search for Primary Ledger "Vision Operations (USA)" and Click on Update Accounting Options.
2. In Primary Ledger: Vision Operations (USA) section, Click on Update Icon for Reporting Currencies.
3. Add New Reporting Currency with the following info and Apply.
• Reporting Currency Name - Indian Rupee
• Short Name - INR
• Currency Conversion Level -  Journal 
• Default Rate Type   - Corporate
4.  Go to Setup -Open/Close Periods, Query for Ledger : Indian Rupee and Open the Period

  
Setting up Secondary Ledger:

1. Responsibility : General Ledger Vision Operations(USA), Setup -> Financials -> Accounting Setup Manager and Search for Primary Ledger "Vision Operations (USA)" and Click on Update Accounting Options.
2. Go to Secondary ledger and check the Secondary Ledger Name .It may be IAS Reporting Vision Ops. If not available , create new with the same name.
3. Switch to System Administrator Responsibility, Create new Responsibility in the name of IAS Reporting Vision Ops. Set the details as the same as Responsibility: General Ledger Vision Operations (USA) and add this responsibility to the respective User
4.  Switch to Responsibility Oracle Trade Management Administrator, ->Profile ->  Query for the Profile GL: Data Access Set for Responsibility IAS Reporting Vision Ops, and set it as IAS Reporting Vision Ops
5. Switch to Responsibility: IAS Reporting Vision Ops. Open the GL Periods for the Ledger as IAS Reporting Vision Ops.

Testing the setup:

1. Create a new Journal entry in GL,(By creating accruals from Trade Management and Executing CC job " Transfer to General Ledger" from  Responsibility Oracle Trade Management Administrator)
2. Post the details to GL, this will create a new journal entry in Reporting currency with Journal name INR: Fixed Budgets USD . Click on Review Journal to View the details in Reporting Currency.
3. Switch to Responsibility: IAS Reporting Vision Ops and query for the same information by navigating to Journals --> Enter -->Query for the Period, this will retrieve the details for the Secondary Ledger : IAS Reporting Vision Ops.       

How to Open a secondary Ledger

Follow the below steps to open secondary ledger for Vision Ops GL

1.Note the Secondary Leger name for Vision Operations(USA)

•    To verify this navigate to General Ledger Vision Operations(USA) Resp >Setup : Financials : Accounting Setup Manager >Accounting Setups

•     Search for Vision Operations(USA)

•     Click on update

•     Under "Secondary Ledger" section verify the ledger name as "IAS Reporting Vision Ops"

2.  Assign the GL access to Secondary ledger to any responsibility   

•    Add "General Ledger, IFRS Reporting Vision Ops"" resp to your user 

•  Now as sysadmin , Make sure "GL:Access Set" profile is set to "IAS Reporting Vision Ops" at the responsibility level
  (Assign "IAS Reporting Vision Ops" as value to "General Ledger, IFRS Reporting Vision Ops" resp for the the above profile)

3. Open the periods

•     Switch to "General Ledger, IFRS Reporting Vision Ops" resp

•     Open form "Open /Close periods and select the period to be opened

How to get the file versions of any file in oracle applications

-- get the file_id for appropriate file and dir

SELECT * FROM ad_files WHERE filename = '' 

Note : For Java files, instead of .java, use .class

Now , get the file version.

SELECT *
FROM ad_file_versions
WHERE file_id =
ORDER BY file_version_id DESC

How to get the list of parameter of PL/SQL procedure

SELECT position,
  argument_name,
  data_type,
  sequence
FROM all_Arguments
WHERE package_name=
AND object_name   =
ORDER BY position,
  argument_name,
  data_type,
  sequence;

Query to fetch functional currency of an Operating unit

SELECT
  gs.currency_code
FROM  
  gl_sets_of_books gs,
  ozf_sys_parameters_all os,
  hr_operating_units ho
WHERE 
  os.set_of_books_id = gs.set_of_books_id
  AND ho.name = ''
  AND ho.organization_id = os.org_id;

--Example
SELECT
  gs.currency_code
FROM  
  gl_sets_of_books gs,
  ozf_sys_parameters_all os,
  hr_operating_units ho
WHERE 
  os.set_of_books_id = gs.set_of_books_id
  AND ho.name = 'Vision Operations'
  AND ho.organization_id = os.org_id;