How to send external attachment with invoices in Oracle Fusion

 Please follow the below steps and retry running the Print Receivables job after.

Please pass the following SQL Query in the Transaction Delivery Bursting > Attachment tab.
Attachment Repository = FA_UCM_PROVISIONED

SQL Query: 
-----------------
SELECT del.customer_trx_id || '_' || del.payment_schedule_id AS "KEY",
doc.DM_VERSION_NUMBER AS "ATTACHMENT_ID"
FROM AR_BPA_DELIVERY_DETAILS del,
fnd_attached_documents att,
FND_DOCUMENTS_VL doc
WHERE att.entity_name = 'RA_CUSTOMER_TRX_ALL'
AND att.pk1_value = TO_CHAR(del.CUSTOMER_TRX_ID)
AND doc.DOCUMENT_ID =att.DOCUMENT_ID
AND att.Category_Name IN
(SELECT Lookup_Code
FROM Fnd_Lookups
WHERE LOOKUP_TYPE = 'AR_PRINT_ATTACHMENT_CATEGORIES'
AND ENABLED_FLAG = 'Y'
AND (TRUNC(SYSDATE) BETWEEN nvl(Start_Date_Active,TRUNC(SYSDATE)) AND nvl(End_Date_Active,TRUNC(SYSDATE)))
)

The Print Receivables Transactions process prints transaction attachments that satisfy the following criteria:

• The transaction delivery method is Paper or Email.
• The attachment is in PDF format.
• The attachment is attached to the transaction header.
• The attachment category is defined as an active lookup code of the AR_PRINT_ATTACHMENT_CATEGORIES lookup type (Categories of Attachments to be Printed).

How to pass values in lexical parameters in BIP seeded report

1/ First open the SQL Query 

 2/ If your query is like 

 Select abc, def, ghi 

from ap_invoice 

 &FROM_clause 

Where &WHERE_Clause 

p=q 

and m=t 

&ORDERBY_Clause 

 so while running the BI report in fusion please pass on below parameter: 

 &FROM_CLAUSE value will be ,ap_invoices_all or value can be any valid table name exist in entire query. 

 &WHERE_Clause value will be 1=1 and

&ORDERBY_Clause  value will be order by 1

In same fashion we can pass on lexical parameters and see how our query is and after passing parameters how it should look like in real . 

One more example :

SELECT gcc.CODE_COMBINATION_ID,
GCC.ATTRIBUTE_CATEGORY,
gcc.segment1 seg1,
gcc.segment2 seg2,
gcc.segment3 seg3,
gcc.segment4 seg4,
gcc.segment5 seg5,
&VSH_SELECT account
FROM GL_CODE_COMBINATIONS GCC
WHERE gcc.CHART_OF_ACCOUNTS_ID = 9090
AND &VSH_WHERE

&VSH_SELECT value will be null

&VSH_WHERE value will be 1=1

How to put watermark in the RTF template in BIP Oracle Fusion

Step 1: Go to RTF template Step 2: Click on Design > Watermark > Custom Watermark

Now Yellow highlighted above should be printed on output . Let suppose we have to print that if invoice has been printed already then watermark should show as "duplicate" else nothing . Two things: we can make changes in the Data model SQL query itself. Example below decode(printing_pending,'N','Duplicate')"Printing" And we can plag/map "printing" column or XML tag in RTF template.

Now run the rtf template after putting XML values into it. second thing we can make the changes in RTF template itself. Use ifelse condition to decide. xdoxslt:ifelse(.//CF_DUP_FLAG='Y','DUPLICATE','') this condition specifies, if CF_DUP_FLAG is 'Y' then DUPLICATE else NULL 2. Assign to variable to wMark (this is seeded variable)

Cheers

How to call Custom report(which was seeded earlier) from standard ESS job

To customize seeded report please refer my blog https://happyrahoalways.blogspot.com/2021/04/customize-seeded-report-in-oracle-bi.html When you customize the seeded report then standard ESS job will automatically point to customized one. ESS job will check first if custom is there otherwise it will call Standard one. PLease make sure Data Model name of standard and Custom should be same. Similary Reporting name for custom and standard should be same. Also Path should be same for both (standard and Custom)

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