How to accept user name dynamically in BIP report FUSION APPLICATION ORACLE - Visible data only to Line-Functional Manager

Use :XDO_USER_NAME in the query , it will accept the logged in USERNAME 

Query : 

SELECT 1 

                           FROM  per_assignment_supervisors_f_v pas

                           WHERE 1=1

                           AND pas.primary_flag = 'Y'

                           AND UPPER(PAS.MANAGER_TYPE) like 'LINE%MANAGER'

                           AND TRUNC(SYSDATE) BETWEEN TRUNC(pas.effective_start_date) AND TRUNC(pas.effective_end_date) 

                           AND pas.person_id      = ppx.person_id

                           START WITH pas.manager_id = (SELECT pu.person_id

                                                        FROM per_users pu 

                                                        WHERE 1=1

                                                       AND UPPER(pu.username) = UPPER(:xdo_user_name)

Customize the seeded Report/DataModel in Oracle BI Fusion Application

The best and recommended way to customize seeded reports is to use BI Publisher's "Customize" function. But before you can actually modify the report, you have to first know the following:

1. Identify under what group / pillar the report belongs to (Ex. Financials, HCM, Procurement)
2. Identify the report's functionality (Ex. Invoice Report, Payroll, Purchase Order)
3. Identify if the report is being called from the application screen via a function button, a menu, etc (more on this later).
4. Familiarity with the expected report output and its data.
5. Have the sufficient roles assigned such as BI Author and BI Administrator

For this example, we will modify the seeded Receivables Invoice report's underlying SQL.

To begin customizing a seeded Oracle Fusion Report, you may execute the following steps:

1. Login to Oracle Fusion Applications using your Username and Password
2. Click on the browser's address bar and change the URL to below to go to the BI Publisher Enterprise console



3. Once inside BI Publisher Enterprise console, go to "Catalog" to access the Reports Catalog


4. On the Folders pane, Expand "Shared Folders", then select the application that contains the Report to be modified (ex. "Financials"), then open the folder of the report's functionality (ex. "Receivables")


5. Once inside the folder, choose the correct report and click on "More", then choose "Customize". Note that you may have to go deeper into the sub-folders to see the correct report. In this case, one has to navigate to the following folders: Receivables > Bill Presentment and look for the "Invoice Print Template" report:


6. The Customize function will effectively copy the said report and folder structure under the "Custom" folder. However, note that this step only copies the report and its seeded layouts to the Custom folder, but still uses the seeded Data Model. Go back to the Reports Catalog by clicking on the "Catalog" link on the BI Publisher Enterprise's Global Area:



7. On the Folders pane, Expand "Shared Folders" and further down to the "Custom" Folder. Again, select the application that contains the Report to be modified (ex. "Financials"), then open the folder of the report's functionality (ex. "Receivables"). You will now see the copy of the Report ("Invoice Print Template") that is ready to be customized.


8. This Custom folder will also contain the customized data model. To proceed, click on the "+" Icon and add a new Folder called "Data Model". Please note that this folder name and structure is recommended by Oracle and using a different approach might result into data loss during upgrades or patches.


9. Go back to the folder where the seeded report is placed (Shared Folders > Financials > Receivables > Bill Presentment) and go further down a sub-folder also named "Data Models". This will contain the seeded Data Models of each corresponding Report.


10. Select the Data Model of the seeded report and click on "More" > "Copy". Then go into the Custom Data Model Folder (Shared Folders > Custom > Financials > Receivables > Bill Presentment > Data Model) and click on the "Paste" Icon.




11. Go back on folder higher (Shared Folders > Custom > Financials > Receivables > Bill Presentment) and select the Report and click on "Edit":



12. On the report page. Hover over the Data Model link and you will see that it points to the seeded Data Model (/Financials/Receivables/Bill Presentment/Data Models/Transactions Print Data Model). To point this to the custom Data Model, click on the search Icon and navigate to the custom Data Model.




13. Once selected, hover once more on the Data Model link and you will see that it now points to the custom Data Model (/Custom/Financials/Receivables/Bill Presentment/Data Models/Transactions Print Data Model). Note that Oracle does not recommended to change the custom Data Model's name as it will impact how the seeded report is being run.


14. Proceed to click on the Data Model link and you will be directed to the Data Model console. Here, you can modify, add or remove data sets, add parameters, Triggers, List of Values (LOVs) and Bursting Options.

Tables relationship in SCM Oracle

 Relation between SCM tables

1/ PO_req_distributions_all(distribution_id)
 and 
PO_distributions_all (req_distribution_id)

2/ rcv_shipment_lines(po_header_id,po_line_id)
and 
po_headers_all,po_lines_all,po_line_locations_all

3/ rececipt and Invoice relation is Via PO tables

ap_invoice_distributions_all(po_distribution_id)
and
po_distributions_All (po_distribution_id)

//example
SELECT distinct pha.segment1 po_number
       ,aia.invoice_num invoice_number
       ,rsh.receipt_num receipt_number
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid
       ,ap_invoices_all aia
       ,rcv_shipment_lines rsl
       ,rcv_shipment_headers rsh
WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aia.invoice_id=aid.invoice_id
   AND rsl.po_header_id=pha.po_header_id
   AND rsl.shipment_header_id=rsh.shipment_header_id

4/ AP to XLA table

Source_id_int_1 from xla_transaction_entities and invoice_id from ap_invoices_All

5/   XLA to GL tables

The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES

Lets see how we can Drilldown from Account Payables Invoice details to GL Journal Detail.



example
//

select * from xla_entity_id_mappings
where entity_code like 'AP_INVOICES';

1. When we create invoices it will hit below given table in account Payables.
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

e.g. :
Invoice Header:
select * from ap_invoices_all
where invoice_num='IN0097158'; -- Copy the value for Invoice_id for future rerfrence 16211228

Invoice Line : 
select * from ap_invoice_lines_all
where invoice_id=16211228;

Invoice Distributions :

select * from ap_invoice_distributions_all
where invoice_id=16211228;

SLA Tables :
select * from XLA.xla_transaction_entities 
where source_id_int_1=16211228
and transaction_number='IN0097158'; -- From this table copy value for Entity id 580427218
--  
SELECT * FROM XLA_AE_HEADERS
WHERE ENTITY_ID=580427218;  --- From this table copy value Event id 582978337 and ae_header_id value 126276903
-- 
select * from xla_events
WHERE EVENT_ID=582978337;
-- 
select * from xla_ae_lines
WHERE AE_HEADER_ID=126276903;
--

SELECT *
from gl_import_references gir
where gir.gl_sl_link_id in
(select gl_sl_link_id from xla_ae_lines
WHERE AE_HEADER_ID=126276903); -- from this table you will get detail for je_header_id, je_line_num, je_batch_id