Query to get current logged in user in Oracle Fusion

select  FND_GLOBAL.USER_GUID, FND_GLOBAL.USER_NAME from dual

Query to get Role and Data access Assigned to user in Oracle Fusion

SELECT
 'DATA ACCESS SET',
 GL.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.GL_ACCESS_SETS GL,
 FUSION.PER_USERS PU
WHERE
 GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'BUSINESS UNIT',
 BU.BU_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_ALL_BUSINESS_UNITS_V BU,
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.PER_USERS PU
WHERE
 ROLE.ORG_ID = BU.BU_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'LEDGERS',
 LED.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.GL_LEDGERS LED,
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.PER_USERS PU
WHERE
 ROLE.LEDGER_ID = LED.LEDGER_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'ASSET BOOK',
 BOOK.BOOK_TYPE_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FA_BOOK_CONTROLS BOOK,
 FUSION.PER_USERS PU
WHERE
 BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'INTERCOMPANY ORGANIZATION',
 INTERCO.INTERCO_ORG_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FUN_INTERCO_ORGANIZATIONS INTERCO,
 FUSION.PER_USERS PU
WHERE
 INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'COST ORGANIZATION',
 COST.COST_ORG_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.CST_COST_ORGS_V COST,
 FUSION.PER_USERS PU
WHERE
 COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'MANUFACTURING PLANT',
 MFG.DEF_SUPPLY_SUBINV,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.RCS_MFG_PARAMETERS MFG,
 FUSION.PER_USERS PU
WHERE
 MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'CONTROL BUDGET',
 BUDGET.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.XCC_CONTROL_BUDGETS BUDGET,
 FUSION.PER_USERS PU
WHERE
 BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'REFERENCE DATA SET',
 ST.SET_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FND_SETID_SETS_VL ST,
 FUSION.PER_USERS PU
WHERE
 ST.SET_ID = ROLE.SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'INVENTORY ORGANIZATION',
 INV.ORGANIZATION_CODE,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.INV_ORG_PARAMETERS INV,
 FUSION.PER_USERS PU
WHERE
 INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'PROJECT ORGANIZATION CLASSIFICATION',
 HR.CLASSIFICATION_CODE,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
 FUSION.PER_USERS PU
WHERE
 HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID

Query to get the profile option values from backend

 select fpo.profile_option_id 
       ,fpo.profile_option_name 
       ,fpo.user_profile_option_name 
       ,decode(level_id,10001,'Site'           
                       ,10002,'Application'
                       ,10003,'Responsibility'
                       ,10004,'User'
                       ,10006,'Organization') profile_level 
       ,decode(level_id,10002, (select application_name 
                                from apps.fnd_application_vl 
                                where application_id = level_value ) 
                       ,10003, (select responsibility_name 
                                from apps.fnd_responsibility_vl 
                                where responsibility_id = level_value) 
                       ,10004, (select user_name 
                                from apps.fnd_user 
                                where user_id = level_value) 
                       ,10006, (select name   
                                from apps.hr_all_organization_units     
                                where organization_id = level_value)) level_value 
       ,profile_option_value 
  from apps.fnd_profile_options_vl fpo 
      ,apps.fnd_profile_option_values fpov 
  where fpo.profile_option_id = fpov.profile_option_id 
  and   (fpo.profile_option_name like 'Profilename' 
         or fpo.user_profile_option_name like <user Profile Option name>); 

Examples of dynamic SQL (EXECUTE IMMEDIATE)

DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

Script to Assign Responsiblity to USER from backend script

-- ----------------------------------------------------------
-- Add Responsibility to Oracle FND User
-- -----------------------------------------------------------
DECLARE
    lc_user_name                        VARCHAR2(100)    := 'USER1';
    lc_resp_appl_short_name   VARCHAR2(100)    := 'FND';
    lc_responsibility_key          VARCHAR2(100)    := 'APPLICATION_DEVELOPER';
    lc_security_group_key        VARCHAR2(100)    := 'STANDARD';
    ld_resp_start_date                DATE                        := TO_DATE(sysdate);
    ld_resp_end_date                 DATE                        := NULL;

BEGIN
     fnd_user_pkg.addresp
     (   username           => lc_user_name,
        resp_app             => lc_resp_appl_short_name,
        resp_key             => lc_responsibility_key,
        security_group  => lc_security_group_key,
        description         => NULL,
        start_date           => ld_resp_start_date,
        end_date            => ld_resp_end_date
    );

 COMMIT;

EXCEPTION
            WHEN OTHERS THEN
                        ROLLBACK;
                        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Sample SOAP Payload to Run a BI Publisher Report

Created a Report Supplier Listing
Below are the properties of the report in Oracle Cloud

WSDL : https://[YourDomain]/xmlpserver/services/ExternalReportWSSService?WSDL

Sample payload to run the report with one parameter.
xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
<soap:Header/>
<soap:Body>
<pub:runReport>
<pub:reportRequest>
<pub:attributeFormat>xml</pub:attributeFormat>
<!-- Flatten XML should always be false when we have XML type of output to display the XML tags as mentioned in BIP Data Model and display XML structure in as expected format -->
<pub:flattenXML>false</pub:flattenXML>
<pub:parameterNameValues>
<!--1st Parameter of BIP Report-->
<pub:item>
<pub:name>Supplier_number</pub:name>
<pub:values>
<pub:item>1252</pub:item>
</pub:values>
</pub:item>
</pub:parameterNameValues>
<pub:reportAbsolutePath>/Custom/Procurement/Purchasing/Supplier listing.xdo</pub:reportAbsolutePath>
<!-- Setting sizeOfDataChunkDownload to -1 will return the output to the calling client -->
<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
</pub:reportRequest>
</pub:runReport>
</soap:Body>
</soap:Envelope>

The output of the payload is in base64 format.

Accounts Payables setups in Oracle Fusion

Below are the mandatory setup for Payables in fusion

• Assign Roles to User
• Manage Payables Lookups
• Manage Payment Terms
• Common Options for AP and PO
• Manage Invoice Options
• Manage Payment Options
• Open Payables Periods
• Manage Suppliers

Oracle Fusion Payables:

Oracle Fusion Payables is used to manage invoice and payments of Organization

Assign Roles to User

The Following data roles should be assigned for the User to access the transactions for payables.

1.Account Payables Supervisor

2.Account Payables Specialist.

3.Account Payables Manager.

Step-1: Tools –>Security Console.

Manage payables Lookups

Paygroup is used to group the supplier for payment Process.

Navaigation : Setup and maintainaceà

Financials > Define Invoicing and Payments Configuration >Define Payables > Define payables Configurations>Manage Payables Lookups

Create a paygroup and save and Close

Manage Payment Terms

Payment Terms:

Payment Terms determine due dates, Discount dates and Installment details for the Supplier Invoices.

Due date : It is the last date to make payment for invoice.

Discount Date: It is a last date to get discount for invoice for Supplier.

Term date Basis: It is the base date for the payment terms to calculate due date and discount date.

Navaigation : Setup and maintainaceà

Financials > Define Invoicing and Payments Configuration >Define Payables > Define Common Options for Payables and Procurement >Manage Payment Terms

Enter the values for Discount due date and discount and set assignments and click on Save and close.

Common Options for AP and PO

for AP and PO are the Controlling features at Business Unit level.

There are two types of options.
1.  Common Options for payables and procurement.
2.  General Payables Options.

Common Options for payables and procurement are shared by both payables and procurement.

Navaigation : Setup and maintainaceà

Financials > Define Invoicing and Payments Configuration > Define Payables > Define Common Options for payables and Procurement > Manage Common Options for payables and Procurement

Set the options as per client requirement and click on save and close. 

General Payables options will be shared by only Payables.

Below is the task list for general payables options.

1.Invoice Options

2. Payment Options.

3.Tax Reporting and withholding Tax Options.

Manage invoice Options

1.Invoice Options control how oracle fusion payables control invoice.

Navaigation : Setup and maintainaceà

Financials > Define Invoicing and Payments Configuration > Define Payables > Define General Payables Options > Manage Invoice Options

Manage Payment Options

1.Invoice Options control how oracle fusion payables control Payments.

Navaigation : Setup and maintainaceà

Financials > Define Invoicing and Payments Configuration > Define Payables > Define General Payables Options > Manage Payment Options

Enable all the payment options and click on save and close.

Open Payables Periods

Navigator > Payables >Invoices

Manage Suppliers:

• Supplier is an organization or a person who is supplying goods or services to Organization.
• Suppliers are modeled as a global entity and are not created within a business unit or within any other organizational
• Supplier Sites are associated with a procurement business unit.

The Data roles required to get full access to Supplier and Supplier Sites are

1.Buyer.

2.Supplier Administrator.

Supplier Record Contains of 4 level.

Supplier Profile

Addresses

Sites

Contacts.

Navigation : Procurementà Supplier.

This gives us all the information of Suppliers.