Fusion Technical Links

http://www.ateam-oracle.com/fusion-apps/fa-extend/

BI Publisher : Text Wrap Issue

https://community.oracle.com/blogs/Suma/2015/06/29/bi-publisher-data-inside-table-columns-to-truncate-wrap-text

BI publisher Data inside table columns to truncate / wrap text

Text truncation/ Word Wraps

  By default, if the text within a table cell will not fit within the cell, the text will be wrapped. To truncate the text instead, use the table properties dialog.

1. Place your cursor in the cell in which you want the text truncated.
2. Right-click your mouse and select Table Properties... from the menu, or navigate to Table > Table Properties...
3. From the Table Properties dialog, select the Cell tab, then select Options...
4. Deselect the Wrap Text check box.

The following figure shows the Cell Options dialog.

formatting Date in Fusion BIP

Issue with Fusion BIP: It returns date format with timezone and full text string, which is difficult to handle in excel templates.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm

select to_char(sysdate,'MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') formatted_date from dual FORMATTED_DATE NOV-2018

-> Pass date field as parameter to below:

to_char(PO.APPROVED_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')

BI Publisher: Using Flexfield in Data Model based Reports

https://docs.oracle.com/cd/E29542_01/bi.1111/e22258/add_flexfields.htm#BIPDM338

Convert Date Time to Another Timezone

SELECT
to_char(
CAST((
FROM_TZ(CAST(sysdate AS TIMESTAMP),'GMT') 
AT TIME ZONE 'Asia/Dubai'
) AS DATE) 
,'HH:MI PM') 
Asia_Dubai
FROM
dual

Oracle Fusion Technical: How to create a Financial Extract

1. Submit program name: Generate Data File for Export
2. Program has multiple parameters:

• I am running here for: Payables Transactions Extract

• Provide: File Prefix: APINVEXT01

[so that you can search in content management server (file import and export)]

• Once you sumit: it will submit: Payables Transactions Extract
• You will find a file crated in UCM server [setup needs to be done, else no file]

Oracle Fusion Technical: How to create an ESS Job in Fusion

1. How to create an ESS Job in Fusion

> Setup and Maintenance > Tasks > Search > Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

> There you will be able to create custom job

1. Job Application Name: FscmEss
2. Job Type: BIPJobType
3. ReportID: /Custom/AA/xxessjob01Report.xdo
4. Path: /fin/ap
5. add parameters: [you need to create a Value set or Lookup based parameter only, BIP LOV does not apply here, only parameter seq is the relation between ess job param and bip job param]

Pre-Req
1. create a bi publisher report, notedown its xdo file path, [also create its rtf template, without that program will give error]

/Custom/AA/xxessjob01.xdm

/Custom/AA/xxessjob01Report.xdo

BI Publisher: function listing

https://docs.google.com/spreadsheets/d/1f2-QJ8l2_FpSGyihVeJaHiPOy9-MKzYzzTS8SpQy2qs/edit#gid=1

bi publisher: xdoxslt: array with variables

-> Define variables

<?xdoxslt:set_variable($_XDOCTX, 'PaymentTotalVar', 0)?>

<?xdoxslt:set_variable($_XDOCTX, 'ReceiptTotalVar', 0)?>

-> assign some value to variables

-> Using Arrays

setting arrays [key, value] key has to be a number

<?xdoxslt:set_array($_XDOCTX, ‘NetPay’, 1, xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar'))?>

<?xdoxslt:set_array($_XDOCTX, ‘NetPay’, 2, xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar'))?>

getting values

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 1)?>

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 2)?>

-> Calculation
use in rtf tags [subtraction of values]

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 2) -xdoxslt:get_array($_XDOCTX, ‘NetPay’, 1)?>

BI Publisher: setting xdoxslt based on condition

This will work under boilerplate text in rtf element

C1  EC C2  EC

<?xdoxslt:ifelse(TRANSACTION_TYPE='Available Receipts', 'Receipts', 'Payment')?>

<?xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar')?>

<?xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar')?>

0.00

C1=>
<?if@inlines:TRANSACTION_TYPE='Available Receipts'?><?xdoxslt:set_variable($_XDOCTX, 'ReceiptTotalVar', sum(current-group()/ PAYMENT_RECEIPT_AMT))?>


EC=>
<?end if?>

C2=> <?if@inlines:TRANSACTION_TYPE='Available Payments'?><?xdoxslt:set_variable($_XDOCTX, 'PaymentTotalVar', sum(current-group()/ PAYMENT_RECEIPT_AMT))?>

Based on condition only variables will have values assigned.

0.00 =>

<?xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar')-xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar')?>

BI Publisher: XDOXSLT

		Description	Example	Result
<?xdoxslt:abs(NUMBER)?>		Return absolute value of number	<?xdoxslt:abs(-123.45)?>	123.45
<?xdoxslt:current_date('DD-MM-YYYY')+1?>		Retrieve current time	<?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?>	2011-11-11
<?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?>			<?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?>	12:30:44 PM
<?xdoxslt:date_diff(RESULT, FROM_DATE, TO_DATE, $_XDOLOCALE, $_XDOTIMEZONE)?>		Determine difference between dates	<?xdoxslt:date_diff(‘m', ‘2010-07-30’, ‘2010-12-31’, $_XDOLOCALE, $_XDOTIMEZONE)?>	5
<?xdoxslt:foreach_number($_XDOCTX, 1, ITERATIONS, 1)?>		Used in conjunction with for-each loop	<?for-each:xdoxslt:foreach_number($_XDOCTX, 1, $iterations, 1)?>	-
		Format number	<?xdoxslt:format_number(12345, 2, $_XDOLOCALE)?>	12,456.00
		Format number with custom separators	<?xdoxslt:format_number(12345, 3, '.', 'x', $_XDOLOCALE)?>	12.456x000
<?xdoxslt:get_array($_XDOCTX, TABLE_NAME, INDEX)?>		Retrieve value from hash table	<?xdoxslt:get_array($_XDOCTX, ‘MyTable’, 2)?>	'Test'
<?xdoxslt:get_day(DATE, LOCALITY)?>		Get current day	<?xdoxslt:get_day(‘2010-07-29’, 'Australia/Brisbane')?>	29
<?xdoxslt:get_month(DATE, LOCALITY)?>		Get current month	<?xdoxslt:get_month(‘2010-07-29’, 'Australia/Brisbane')?>	7
<?xdoxslt:get_variable($_XDOCTX, VAR_NAME)?>		Get value	<?xdoxslt:get_variable($_XDOCTX, 'test')?>	'Hello'
<?xdoxslt:get_year(DATE, LOCALITY)?>		Get current year	<?xdoxslt:get_year(‘2010-07-29’, 'Australia/Brisbane')?>	2010
<?xdoxslt:ifelse(EXPRESSION, TRUE, FALSE)?>		Conditional Statement	<?xdoxslt:ifelse(a = b, 'Equivalent', 'Mismatch')?>	-
<?xdoxslt:init_cap(STRING)?>		Convert initial characters to capitals	<?xdoxslt:init_cap('this is a test')?>	This Is A Test
<?xdoxslt:left(STRING, LENGTH)?>		Extract beginning portion of string	<?xdoxslt:left(‘abcdefg’, 3)?>	'abc'
<?xdoxslt:ltrim(STRING)?>		Removes leading white space in string	<?xdoxslt:ltrim(‘ a ‘)?>	'a '
<?xdoxslt:maximum(ELEMENT_NAME)?>		Retrieve largest value from element	<?xdoxslt:maximum(Gross_Claims_ID24)?>	253456
<?xdoxslt:month_name(MONTH_NUM, ABBREVIATE, $_XDOLOCALE)?>		Retrieves name of specified month	<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>	'January'
<?xdoxslt:minimum(ELEMENT_NAME)?>		Retrieve smallest value from element	<?xdoxslt:minimum(Gross_Claims_ID24)?>	-
<?xdoxslt:next_element(GROUP, ., ELEMENT_NAME)?>		Retrieves subsequent element in group	<?xdoxslt:next_element(current-group(), ., Pay_ID21)?>
<?xdoxslt:pat_format_number(NUMBER, FORMAT, $_XDOLOCALE)?>		Format number with pattern	<?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?>	12,345.00
<?xdoxslt:prev_element(GROUP, .., ELEMENT_NAME)?>		Retrieves previous element in group	<?xdoxslt:prev_element(current-group(), .., Pay_ID21)?>
<?xdoxslt:replace(STRING, OLD_CHAR, NEW_CHAR)?>		Replace consecutive characters	<?xdoxslt:replace(‘Mist’, ‘Mi’, ‘Te’)?>	Test
<?xdoxslt:right(STRING, LENGTH)?>		Replicate string	<?xdoxslt:replicate(‘String’, 3)?>
		Extract end portion of string	<?xdoxslt:right(‘abcdefg’, 3)?>	'StringStringString'
<?xdoxslt:round(NUMBER [, DECIMALS])?>		Round number	<?xdoxslt:round(4.56 , 0)?>	5
<?xdoxslt:rtrim(STRING)?>		Removes trailing white space in string	<?xdoxslt:rtrim(‘ a ‘)?>	' a'
<?xdoxslt:sec_diff(FROM_TIME, TO_TIME, $_XDOLOCALE, $_XDOTIMEZONE)?>		Determine difference between times	<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE)?>	3600
<?xdoxslt:set_array($_XDOCTX, TABLE_NAME, INDEX, STORE_VAL)?>		Truncate number to specified decimals	<?xdoxslt:set_array($_XDOCTX, ‘MyTable’, 2, ‘Test’)?>	-
<?xdoxslt:set_variable($_XDOCTX, VAR_NAME, VALUE)?>			<?xdoxslt:set_variable($_XDOCTX, 'test', "'Hello'")?>	-
<?xdoxslt:truncate(NUMBER [, DEC_INT])?>			<?xdoxslt:truncate(2.6789, 2)?>	2.67
<?xdoxslt:trim(STRING)?>		Removes white space in string	<?xdoxslt:trim(‘ A ‘)?>	'A'

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