How to get the Data source behind screens in Fusion

Refer:

http://www.ateam-oracle.com/fusion-saas-finding-the-view-object-you-need-for-data-extracts/

or

https://docs.oracle.com/en/cloud/saas/financials/18c/analyze-and-report.html
->

• Review data lineage

then

-> you will get excel data similar to below:

Search for field names and get the subject area name

Product Area	Subject Area	Presentation Table	Presentation Column	Physical Table
Financials	Payables Payments - Disbursements Real Time	"- Account"	Account Identifier	FscmTopModelAM.FinGlAccountsCodeComboAM.CodeCombinationPVO
Financials	Payables Payments - Disbursements Real Time	"- Account"	Account Identifier	FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI

go to Data Model> SQL > OBIEE > then choose the relavent subject area in list, then you will get the presentation layer SQL.

Run this presentation layer sql into xmlpserver> admin > issue sql and get the physical sql

How to generate trace file in Fusion SaaS

Refer: Troubleshooting Using Record Issue (Doc ID 2122074.1)

You need to go to Record this issue, and run across pages, stop the recording, get the recording number and then raise a SR with Oracle giving this recording number, they will provide the trace file.

How to query Chart of Account Segment Value set in fusion

SQL for Chart of Account Segment Value set in fusion r13.

select  ATTRIBUTE_CATEGORY,VALUE,ENABLED_FLAG,SUMMARY_FLAG,FLEX_VALUE_ATTRIBUTE1,FLEX_VALUE_ATTRIBUTE2,
FLEX_VALUE_ATTRIBUTE3,FLEX_VALUE_ATTRIBUTE4,FLEX_VALUE_ATTRIBUTE5,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY
from fusion.FND_VS_VALUES_B
where VALUE_SET_ID in (SELECT segi.VALUE_SET_ID FROM fusion.fnd_kf_str_instances_b stri, fusion.fnd_kf_segment_instances segi, fusion.fnd_kf_segments_b segb, fusion.fnd_vs_value_sets vs
WHERE application_id               = 101
AND stri.structure_instance_id     = segi.structure_instance_id
AND stri.structure_id              = segb.structure_id
AND segb.segment_code              = segi.segment_code
AND segi.value_set_id              = vs.value_set_id
AND STRUCTURE_INSTANCE_NUMBER=1041)

In fusion.

fnd_kf_str_instances_b 

have STRUCTURE_INSTANCE_CODE and you could use it as parameter. 
=============================================================


#FusionVsOracleApps
FND_VS_VALUE_SETS-> FND_FLEX_VALUE_SETS

FND_VS_VALUES_VL  -> FND_FLEX_VALUES_VL

Understand Loops in BI Publisher: for-each vs for-each-group vs for-each: current-group

xml grouping

SUPPLIERS

INVOICES

Required:

SUPPLIERS

CURRENCY

INVOICES

‘for-each-group’ to regroup to show the invoices by currenc, [<?for-each-group:G_INVOICE_NUM;INVOICE_CURRENCY_CODE?>]

• This is specifying that we want to create a new group ‘INVOICE_CURRENCY_CODE’ based on the original ‘G_INVOICE_NUM’ group.
• We now have ‘for-each:current-group()’  to refer to the new INVOICE_CURRENCY_CODE group we have just created.
• Note: we cannot refer to the group as INVOICE_CURRENCY_CODE as this group is only created at runtime.
• sum (current-group()/ENT_AMT) for the entered amount, again we use the current-group() tag to refer to the new ‘INVOICE_CURRENCY_CODE’ group.

I

<?for-each:G_VENDOR_NAME?>

<?for-each-group:G_INVOICE_NUM;INVOICE_CURRENCY_CODE?> Currency: USD Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount <?for-each:current-group()?> <?INVOICE_NUM?> <?INV_TYPE?> <?INVOICE_DATE?> <?GL_DATE?> <?ENT_AMT?> <?ACCTD_AMT?> <?end for-each?> Total for <?INVOICE_CURRENCY_CODE?> <? sum(current-group()/ENT_AMT)?> <?format-number(sum(current-group()/ACCTD_AMT),"#,###.00","Euro")?> <?end for-each-group?>

<?split-by-page-break:?>

<?end for-each?>

-------------
Output
-------------

Currency: EUR Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount 981110 Standard 10-NOV-07 10-NOV-07 122 122 Total for EUR 122 122.00 Currency: FIM Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount 100000 Standard 28-MAY-07 28-MAY-07 122 20.33 100001 Standard 28-MAY-07 28-MAY-07 200 33.33 1 Standard 03-JUN-07 03-JUN-07 400 66.67 100004 Standard 28-MAY-07 28-MAY-07 100 16.67 00s Standard 07-JUN-07 07-JUN-07 100 16.67 FI1009 Standard 10-MAY-07 10-MAY-07 1220 203.33 Total for FIM 2142 357.00

bi publisher tips and tricks

<?xml version = '1.0' encoding = 'utf-8'?>
<data>
<RequisitioningBU>bu name</RequisitioningBU>
</data>


-> working code for boilerplate (xdofx with upper), does not work in non-boilerplate type

<?xdofx:if upper(RequisitioningBU) ='BU NAME' then 'A' else if upper(RequisitioningBU) ='BU2 NAME' then 'B' else  'ORACLE' end if?>


-> working code for tags (xdofx with upper)

<?choose:?>
<?when:substring(RequisitioningBU,1,7)= "BU NAME"?> stuff <?end when?>
<?when:substring(RequisitioningBU,1,8)= "BU2 NAME"?> stuff <?end when?>
<?end choose?>


Adding IF condition in rtf with OR

<?if: not(substring(ReportNumber,1,2) = "PG" or substring(ReportNumber,1,2)= "NA") ?>


Define a xsl variable [xsl definition works under bi form elements only]

define:

<xsl:variable name="no_of_lines_per_page" select="number(26)"/>

print variables

<?$no_of_lines_per_page?>

Access tags a a particular level: relative retrival

./ mean at same level there should be a tag Payee, and under that Name tag should be present

<?./Payee/Name?>


Access tags a a particular level: absolute retrival

You can get absolute value using below logic

<?/OutboundPaymentInstruction/OutboundPayment/Beneficiary/Name?>




Test a condition with absolute path data 

Data:

<?xml version = '1.0' encoding = 'utf-8'?>

<A>

               <B ID="12345">

                              <C>data</C>

               </B>

</A>

Below syntax will 

1. create a variable v1

2. select value of A/B/@ID into v1

3. checks if value of v1 12345, if true prints SUCCESS

4. to use it within rtf, we need to create a rtf element [ex. xsl_var_if_test], and insert below syntax into that. 

<xsl:variable name="v1" select="A/B/@ID"/>

<xsl:if test="$v1 = '12345'">SUCCESS</xsl:if>

Combining XSL with for-each
May be you need to extract values from levels under a repeating element, and print them

Data:

<?xml version="1.0" encoding="utf-8"?>
<A>
  <B ID="12345">
    <C>data</C>
  </B>
  <B ID="123451">
    <C>data-1</C>
  </B>
  <B ID="123452">
    <C>data-2</C>
  </B>
  <B ID="123453">
    <C>data-3</C>
  </B>
  <B ID="123454">
    <C>data-4</C>
  </B>
  <B ID="123455">
    <C>data-5</C>
  </B>
</A>

Below syntax will 

1. create a variables v<>

2. select value of ./@ID into v<>

3. checks if value of v1 12345, if true prints SUCCESS

write in rtf like below:

<?for-each: B?>

xsl_var_if_test2

<?end for-each?>

where xsl_var_if_test2 is a element with boilerplate text as below

<xsl:variable name="v1" select="./@ID"/><xsl:if test="$v1 = '12345'">SUCCESS-0</xsl:if>
<xsl:variable name="v2" select="./@ID"/><xsl:if test="$v2 = '123451'">SUCCESS-1</xsl:if>
<xsl:variable name="v3" select="./@ID"/><xsl:if test="$v3 = '123452'">SUCCESS-2</xsl:if>
<xsl:variable name="v4" select="./@ID"/><xsl:if test="$v4 = '123453'">SUCCESS-3</xsl:if>
<xsl:variable name="v5" select="./@ID"/><xsl:if test="$v5 = '123454'">SUCCESS-4</xsl:if>

prints:

SUCCESS-1
SUCCESS-2
SUCCESS-3
SUCCESS-4

Subtraction in rtf:

<?(ACCTED_DR)-xdoxslt:sum(ACTUAL_DR)?>

---------------------------

ELSE IF

<?xdofx:if COMM!='' then SAL+COMM else SAL end if?>

DECODE TAG:
<?xdofx:decode(COMM,’’,’sry’,COMM)?>
<?xdofx:decode(COMM,’’,ENAME,COMM||’, ’||ENAME)?>

CHOOSE WHEN
<?choose:?> <?when: x == y?> ... <?end when?> <?otherwise:?> ... <?end otherwise?> <?end choose?>

How to get Active bank accounts for a customer, Supplier

-----
Case: You have account number

hz_cust_accounts  (pass account_number = '<1234>') (get CUST_ACCOUNT_ID, PARTY_ID)
HZ_CUST_ACCT_SITES_ALL  (cust_account_id=hz_cust_accounts.cust_account_id) (get cust_account_site_id)
HZ_CUST_SITE_USES_ALL   (pass CUST_ACCT_SITE_ID) (get SITE_USE_ID -> which are active)

IBY_EXTERNAL_PAYERS_ALL (pass party_id= hz_cust_accounts.party_id, ACCT_SITE_USE_ID-> join with HZ_CUST_SITE_USES_ALL.SITE_USE_ID) (get EXT_PAYER_ID)
IBY_PMT_INSTR_USES_ALL  (pass EXT_PMT_PARTY_ID  = IBY_EXTERNAL_PAYERS_ALL.EXT_PAYER_ID)
IBY_EXT_BANK_ACCOUNTS   (get EXT_BANK_ACCOUNT_ID)
---
HZ_ACCOUNT_OWNERS (pass account_owner_party_id= hz_cust_accounts.party_id) (get EXT_BANK_ACCOUNT_ID)


SELECT ca.account_number      "Customer Account Number"
      , null                  "Site address"
      , piu.start_date        "Bank Account Use Start Date"
      , piu.end_date          "Bank Account Use End Date"
      , eba.bank_account_num  "Bank Account Number"
      , eba.bank_account_name "Bank Account Name"
      , eba.start_date        "Bank Account Start Date"
      , eba.end_date          "Bank Account End Date"
  FROM hz_cust_accounts        ca
     , iby_external_payers_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE ca.cust_account_id      = epa.cust_account_id
   AND piu.ext_pmt_party_id    = epa.ext_payer_id
   AND piu.payment_function    = 'CUSTOMER_PAYMENT'
   AND eba.ext_bank_account_id = piu.instrument_id
   AND ca.account_number       = 'SKM-PERSON-01'
   AND epa.acct_site_use_id    is null



-----------------

(
                SELECT DISTINCT
                    bank.party_name bank_name,
                    branch.party_name branch_name,
                    account.bank_account_num bank_account_num,
                    account.iban iban,
                    account.eft_swift_code swift_code,
                    acc_owner.account_owner_party_id account_owner_party_id,
                    account.ext_bank_account_id
                FROM
                    hz_parties bank,
                    hz_relationships rel,
                    hz_parties branch,
                    iby_ext_bank_accounts account,
                    iby_account_owners acc_owner
                WHERE
                    1 = 1
                    AND bank.party_id = rel.object_id
                    AND bank.party_type = rel.object_type
                    AND rel.object_table_name = 'HZ_PARTIES'
                    AND rel.relationship_code = 'BRANCH_OF'
                    AND rel.subject_id = branch.party_id
                    AND rel.subject_type = branch.party_type
                    AND rel.subject_table_name = 'HZ_PARTIES'
                    AND bank.party_id = account.bank_id
                    AND branch.party_id = account.branch_id (+)
                    AND account.ext_bank_account_id = acc_owner.ext_bank_account_id (+)
                    AND SYSDATE BETWEEN nvl(rel.start_date,SYSDATE - 1) AND nvl(rel.end_date,SYSDATE + 1)
                    AND bank.status = 'A'
                    AND branch.status = 'A'
            ) supplier_bank

Fusion Financials : How to link GL data to the subledger data

How to link GL data to the subledger data

---------------

1. ap_invoices_all (pass invoice num) (get invoice_id)
2. ap_invoice_payments_all (pass invoice_id) (get_check_id)
3. ap_checks_all (pass check_id) (get payment_id)

-> iby_payments_all (pass payment_id) ( get  other vendor, bank info)

1. xla_entity_id_mappings (pass entity_code='AP_PAYMENTS') get (TRANSACTION_ID_COL_NAME_1 -> CHECK_ID, SOURCE_ID_COL_NAME_1 -> SOURCE_ID_INT_1)
2. xla_transaction_entities  (pass SOURCE_ID_INT_1 = <check_id>) (get Entity_id, applicaiton_id)
3. xla_events (pass entity_id) (get event_id)
4. xla_ae_headers (pass event_id) (get ae_header_id)
5. xla_ae_lines (pass ae_header_id) (get gl_sl_link_id, gl_sl_link_table)
6. gl_import_references (pass gl_sl_link_id, gl_sl_link_table) (get je_header_id)

--------

• gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)
• gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)
• xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)
• xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)
• xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)

• xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id
• For Ex:
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

-----
helpful sql joining ap to gl links

select
'X'
     --  aca.check_number,
     --  aipa.invoice_payment_id,
     --  aca.payment_id,
     --  aipa.invoice_id,
     --  aipa.payment_num,
     --  aia.invoice_num,
     --  aia.gl_date,
     --  aia.invoice_amount,
     --  xal.code_combination_id xla_ccid,
     --  xal.ae_header_id,
     --  xal.ae_line_num,
     --  ipa.document_sequence_value
FROM
  ap_invoices_all aia
, ap_checks_all aca
, ap_invoice_payments_all aipa
, iby_payments_all ipa
, xla_transaction_entities xte
, xla_events xe
, xla_ae_headers xah
, xla_ae_lines xal
where 1=1
and aia.invoice_id = aipa.invoice_id
and aipa.check_id  = aca.check_id
and ipa.payment_id = aca.payment_id
and xte.entity_code ='AP_PAYMENTS'
and xte.source_id_int_1 = aca.check_id
and xte.SECURITY_ID_INT_1 = :p_business_unit
and xte.ledger_id = :p_ledger_id
and xte.source_application_id =200
and xte.entity_id = xe.entity_id
and xe.event_id = xah.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.gl_sl_link_table = 'XLAJEL'
and xal.gl_sl_link_id = GIR.GL_SL_LINK_ID
--and aca.check_number='6'