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'

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

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;

How to get debug messages from FND_LOG_MESSAGES table

1)Log into system admin responsibility and set the following profile options

FND: Debug Log Enabled - Yes

FND: Debug Log Level - Statement

FND: Debug Log Module - %

[ set at user level ]

2)Run the below select in sqlplus ::

SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq1 in step#5

3)Try to reproduce the issue with above user for which FND profile options are set.

4)Run the below select in sqlplus ::

SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq2 in step#5

5)Run following select to get the log messages

SELECT LOG_SEQUENCE, MESSAGE_TEXT, USER_ID FROM fnd_log_messages WHERE

log_sequence > log_seq1-1 and log_sequence <>

Enabling FND Debug Messages

Set the following profile options in respective levels

1) FND: Debug Log Enabled => Yes
2) FND: Debug Log Level => Statement
3) FND: Message Level Threshold =>low level
4) FND: Debug Log Module => %

Creating New User for Oracle Marketing using Forms

1) Responsibility -> US HRMS Manager
People / Enter & Maintain / New
Action Type = Create Employment

2) Responsibility -> System Administrator
Security / User / Define
Select Person as the one created in Step 1
Define Responsibilities for this user

3) Responsibility -> CRM Resource Manager, Vision Enterprises
Maintain Resources / Import Resources
Resource Category = Employee
Name = Name entered in Step 1