Fusion Important Tables/Views

Business Unit, Ledger, Mapping:

FUN_NAMES_BUSINESS_UNITS_V

GL_LEDGERS  (LEDGER_ID TO COA_ID)

BI Fusion: default date parameters {$SYSDATE()$} and to define a destination file name dynamically

Incrementing Date Parameters

If the scheduled report includes date parameters, when you enter the values for the dates for the schedule, you cannot change the date values. Every time a scheduled instance of the report is run, the same date parameters are used.

If changing the date parameters is required for each run, you can enter an expression in the date parameter field of the scheduler to calculate the date each time the report job executes.

For example, if you create a schedule for a report that runs every Monday to capture data from the previous week, you need to update the date parameters for the report to increment to the first and last days of the previous week.

Enter one of the following functions using the syntax shown to calculate the appropriate date at the scheduled runtime for the report:

• {$SYSDATE()$} - current date, the system date of the server on which BI Publisher is running.
• {$FIRST_DAY_OF_MONTH()$} - first day of the current month
• {$LAST_DAY_OF_MONTH()$} - last day of the current month
• {$FIRST_DAY_OF_YEAR)$} - first day of the current year
• {$LAST_DAY_OF_YEAR)$} - last day of the current year

The date function calls in the parameter values are not evaluated until the report job is executed by the Scheduler.

You can also enter expressions using the plus sign (+) and minus sign (-) to add or subtract days as follows:

• {$SYSDATE()+1$}
• {$SYSDATE()-7$}

For this example, to capture data from the previous week, each time the schedule runs, enter the following in the report's date parameter fields:

• Date From: {$SYSDATE()-7$}
• Date To: {$SYSDATE()-1$}

Defining a Destination File Name Dynamically Using a Date Expression

When entering the remote file name for a Web folder or FTP destination, you can enter a date expression to have BI Publisher dynamically include a date expression in the file name. The date is set at runtime, using the server time zone.

These expressions are described in the following table.

Expression	Description
%y	Displays the year in four digits: Example: 2011
%m	Displays the month in two digits: 01-12 (where 01 = January)
%d	Displays the date in two digits: 01-31
%H	Displays the hour in two digits based on 24-hour day: 00-24
%M	Displays the minute in two digits: 00 - 59
%S	Displays the number of seconds in two digits: 00 - 59
%l	Displays milliseconds in three digits: 000 - 999

Examples

Use these steps to create a file name that appends a date and a file name that prefixes a date and appends a time.

To create a file name that appends the day, month, and year such as:

myfile_01_11_2010.pdf

Enter the following:

myfile_%d_%m_%y.pdf

To create a file name that prefixes the day, month, and year and appends the hour and minute such as:

01_01_2010_myfile_22_57.pdf

Enter the following:

%d_%m_%y_myfile_%H_%M.pdf

If the file name includes an undefined expression such as my_file_%a%b%c.pdf, the file is created as named my_file_%a%b%c.pdf.

Oracle Fusion Business Unit, Ledgers, Value Set SQLs

--BUSINESS UNIT
----------------------
SELECT BusinessUnitEO.NAME BU_NAME,
  LocationDPEO.DESCRIPTION LOCATION,
  PersonNameDPEO.LIST_NAME MANAGER_NAME,
  DECODE(BusinessUnitEO.STATUS,'A','Active','Inactive') STATUS,
  GL_LEDGER_LE_V.LEGAL_ENTITY_NAME AS LEGAL_ENTITY_NAME,
  GL_LEDGER_LE_V.LEDGER_NAME       AS LEDGER_NAME,
  SetIdSetPEO.SET_CODE,
  SetIdSetPEO.SET_NAME,
  GL_LEDGER_LE_V.CHART_OF_ACCOUNTS_ID,
  GL_LEDGER_LE_V.LEGAL_ENTITY_ID,
  GL_LEDGER_LE_V.LEDGER_ID
FROM  FUSION.HR_ORGANIZATION_V BusinessUnitEO,
   FUSION.HR_ORG_DETAILS_BY_CLASS_V BusinessUnitDetailEO,
   FUSION.GL_LEDGER_LE_V GL_LEDGER_LE_V,
   FUSION.FUN_FIN_BUSINESS_UNITS_V FinancialsBusinessUnitPEO,
   FUSION.FND_SETID_SETS_VL SetIdSetPEO,
   FUSION.HR_LOCATIONS_ALL_F_VL LocationDPEO,
   FUSION.PER_PERSON_NAMES_F_V PersonNameDPEO
WHERE (BusinessUnitEO.ORGANIZATION_ID            = BusinessUnitDetailEO.ORGANIZATION_ID)
AND (BusinessUnitEO.CLASSIFICATION_CODE          = BusinessUnitDetailEO.CLASSIFICATION_CODE)
AND BusinessUnitDetailEO.ORG_INFORMATION_CONTEXT = BusinessUnitEO.CLASSIFICATION_CODE
AND BusinessUnitEO.CLASSIFICATION_CODE           = 'FUN_BUSINESS_UNIT'
AND (BusinessUnitDetailEO.ORG_INFORMATION2       = GL_LEDGER_LE_V.LEGAL_ENTITY_ID)
AND (BusinessUnitDetailEO.ORG_INFORMATION3       = GL_LEDGER_LE_V.LEDGER_ID)
AND (BusinessUnitDetailEO.ORG_INFORMATION7       = FinancialsBusinessUnitPEO.BU_ID(+))
AND (BusinessUnitDetailEO.ORG_INFORMATION4       = SetIdSetPEO.SET_ID(+))
AND (BusinessUnitEO.LOCATION_ID                  = LocationDPEO.LOCATION_ID(+))
AND (BusinessUnitDetailEO.ORG_INFORMATION1       = PersonNameDPEO.PERSON_ID(+))
--AND GL_LEDGER_LE_V.CHART_OF_ACCOUNTS_ID          --=:P_CHART_OF_ACCOUNTS_ID
ORDER BY BusinessUnitEO.NAME

---------
GL_LEDGERS
--------
select GL_LEDGERS.LEDGER_ID LEDGER_ID,
GL_LEDGERS.NAME as NAME,
GL_LEDGERS.SHORT_NAME as SHORT_NAME,
GL_LEDGERS.DESCRIPTION as DESCRIPTION,
GL_LEDGERS.LEDGER_CATEGORY_CODE as LEDGER_CATEGORY_CODE,
GL_LEDGERS.LE_LEDGER_TYPE_CODE as LE_LEDGER_TYPE_CODE,
GL_LEDGERS.COMPLETION_STATUS_CODE as COMPLETION_STATUS_CODE,
GL_LEDGERS.CURRENCY_CODE as CURRENCY_CODE,
GL_LEDGERS.PERIOD_SET_NAME as PERIOD_SET_NAME,
GL_LEDGERS.ACCOUNTED_PERIOD_TYPE as ACCOUNTED_PERIOD_TYPE,
GL_LEDGERS.ALLOW_INTERCOMPANY_POST_FLAG as ALLOW_INTERCOMPANY_POST_FLAG,
   GL_CALENDARS.USER_PERIOD_SET_NAME CALENDAR_NAME,
   DECODE(GL_LEDGERS.LEDGER_CATEGORY_CODE,'PRIMARY','Primary','SECONDARY','Secondary',GL_LEDGERS.LEDGER_CATEGORY_CODE) as LEDGER_CATEGORY,
DECODE(GL_LEDGERS.COMPLETION_STATUS_CODE,'CONFIRMED','Confirmed','NOT_STARTED','Not Started','ERROR','Error',GL_LEDGERS.COMPLETION_STATUS_CODE) as COMPLETION_STATUS,
   GL_BALANCES_CUBES.APPLICATION_NAME CUBE
 from FUSION.GL_LEDGERS GL_LEDGERS, FUSION.GL_CALENDARS GL_CALENDARS, GL_BALANCES_CUBES GL_BALANCES_CUBES
WHERE  GL_LEDGERS.CHART_OF_ACCOUNTS_ID=:P_CHART_OF_ACCOUNTS_ID
AND GL_LEDGERS.PERIOD_SET_NAME =GL_CALENDARS.PERIOD_SET_NAME
AND GL_LEDGERS.ACCOUNTED_PERIOD_TYPE =GL_CALENDARS.PERIOD_TYPE
AND GL_BALANCES_CUBES.PERIOD_SET_NAME(+)=GL_LEDGERS.PERIOD_SET_NAME
AND GL_BALANCES_CUBES.CHART_OF_ACCOUNTS_ID(+)=GL_LEDGERS.CHART_OF_ACCOUNTS_ID
ORDER BY  GL_LEDGERS.NAME
---------

--VALUE SET BASED ON COA
-----
SELECT 
  FND_KF_SEGMENTS_VL.NAME,
  FND_KF_SEGMENTS_VL.SEGMENT_CODE,
  FND_KF_SEGMENTS_VL.PROMPT,
  FND_KF_SEGMENTS_VL.SHORT_PROMPT,
  FND_VS_VALUE_SETS.VALUE_SET_CODE
FROM FUSION.FND_KF_STR_INSTANCES_VL FND_KF_STR_INSTANCES_VL,
  FUSION.FND_KF_STRUCTURES_VL FND_KF_STRUCTURES_VL,
  FUSION.FND_KF_SEGMENTS_VL FND_KF_SEGMENTS_VL,
  FND_VS_VALUE_SETS FND_VS_VALUE_SETS,
  FND_KF_SEGMENT_INSTANCES FND_KF_SEGMENT_INSTANCES
WHERE FND_KF_STR_INSTANCES_VL.STRUCTURE_INSTANCE_NUMBER=:P_COA_ID
AND FND_KF_STRUCTURES_VL.STRUCTURE_ID                  = FND_KF_STR_INSTANCES_VL.STRUCTURE_ID
AND FND_KF_SEGMENTS_VL.STRUCTURE_ID                    =FND_KF_STRUCTURES_VL.STRUCTURE_ID
AND FND_KF_STR_INSTANCES_VL.KEY_FLEXFIELD_CODE         ='GL#'
AND FND_VS_VALUE_SETS.VALUE_SET_ID                  =FND_KF_SEGMENT_INSTANCES.VALUE_SET_ID
AND FND_KF_SEGMENT_INSTANCES.STRUCTURE_INSTANCE_ID    =FND_KF_STR_INSTANCES_VL.STRUCTURE_INSTANCE_ID
AND FND_KF_SEGMENT_INSTANCES.SEGMENT_CODE=FND_KF_SEGMENTS_VL.SEGMENT_CODE
AND FND_KF_SEGMENTS_VL.ENABLED_FLAG ='Y'
ORDER BY SEQUENCE_NUMBER

-------

SELECT segvalsets.value_set_code             ,
value                                       ,
segvals.description                      ,
enabled_flag                                ,
start_date_active                           ,
end_date_active                             ,
summary_flag                                ,
flex_value_attribute1 AS POSTING_ALLOWED    ,
flex_value_attribute2 AS BUDGETING_ALLOWED  ,
flex_value_attribute3 AS ACCOUNT_TYPE       ,
flex_value_attribute4 AS CONTROL_ACCOUNT    ,
flex_value_attribute5 AS RECONCILIATION_FLAG,
flex_value_attribute6 AS FINANCIAL_CATEGORY
FROM fusion.fnd_vs_values_vl segvals,
fusion.fnd_vs_value_sets segvalsets
WHERE segvals.value_set_id = segvalsets.value_Set_id
AND segvalsets.value_Set_id IN
(SELECT value_set_id
  FROM FUSION.fnd_kf_segment_instances
 WHERE structure_instance_id IN
 (SELECT fusion.fnd_kf_str_instances_b.structure_instance_id
    FROM FUSION.fnd_kf_str_instances_b
   WHERE application_id =101
 AND key_flexfield_code = 'GL#'
 )
)


--- Lookup for flex_value_attribute3 ( ACCOUNT_TYPE     ) A: Asset etc
SELECT DISTINCT 
LOOKUP_TYPE,
LOOKUP_CODE,
MEANING,
DESCRIPTION,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM fnd_lookup_values_vl WHERE view_application_id = 101
AND LOOKUP_TYPE ='ACCOUNT TYPE'

--and meaning like 'Asset'

check GL_LOOKUPS

BIP: Running Total with uses of round and sdiv(divide function) with xdoxslt

<?xdoxslt:set_variable($_XDOCTX, 'RTotVar', 0)?>
<?xdoxslt:set_variable(
$_XDOCTX,
'RTotVar',
xdoxslt:get_variable($_XDOCTX, 'RTotVar') + ACCTD_AMT)
?>
<?xdoxslt:get_variable($_XDOCTX, 'RTotVar')?>

------------[use in boilerplate text in element]
1. Set a variable and initialize it with 0.
2. Based on IF condition ACCOUNTED_DR is not null, set value of  var from accounted_dr xml element
3. Display value set using get_variable

<?xdoxslt:set_variable($_XDOCTX, 'currentrowdr_share_value', 0)?>
<?if:ACCOUNTED_DR!=''?>
  <?xdoxslt:set_variable($_XDOCTX, 'currentrowdr_share_value', ACCOUNTED_DR)?>
  <?xdoxslt:get_variable($_XDOCTX, 'currentrowdr_share_value')?>
<?end if?>
-----------

------------[use in boilerplate text in element]

Uses of round and sdiv(divide function) with xdoxslt

<?xdoxslt:set_variable($_XDOCTX, 'running_total_share_value', xdoxslt:get_variable($_XDOCTX, 'running_total_share_value')+ xdoxslt:get_variable($_XDOCTX, 'currentrowdr_share_value')-xdoxslt:get_variable($_XDOCTX, 'currentrowcr_share_value'))?>

How to Divide two tag values in XMLP

<?xdoxslt:round(xdoxslt:sdiv(xdoxslt:get_variable($_XDOCTX, 'running_total_share_value'),xdoxslt:get_variable($_XDOCTX, 'running_total_share_qty'), '0'),3)?>

Subtemplates pseudocode: Oracle Fusion BI

Define two subtemplates named XXPayablesReport and ExtPayablesReport in Path:

Custom/Financials/Payables/Payments/SubTemplates/

Then below code needs to be placed in main rtf template, which will call subtemplate

<?import:xdoxsl:///Custom/Financials/Payables/Payments/SubTemplates/XXPayablesReport.xsb?>

<?import:xdoxsl:///Custom/Financials/Payables/Payments/SubTemplates/ExtPayablesReport.xsb?>

<?choose:?>
<?when: substring(..//LIST_G_CHECKRUN/G_CHECKRUN/LIST_G_LE_GROUP/G_LE_GROUP/NAME,1,19)= "BU1"?>
<?call-template: Header1?><?end when?>
<?otherwise:?>
<?call-template: StandardHeader?>
<?end otherwise?>
<?end choose?>

-- 

subtemplate code

<?template: Header1?>

content

<?end template?>

<?template: StandardHeader?>

content

<?end template?>

SQL: Requisition to PO: Oracle Fusion

SELECT
POH.PO_HEADER_ID,
POH.SEGMENT1  ,
PRHA.REQUISITION_NUMBER
FROM
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA ,
POR_REQ_DISTRIBUTIONS_ALL PRDA ,
POR_REQUISITION_LINES_ALL PRLA ,
POR_REQUISITION_HEADERS_ALL PRHA
WHERE 1=1
AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND PRHA.REQUISITION_NUMBER = '57'


REQUISITION DETAILS SQL:

SELECT
PERSONNAMEDPEO_L.FULL_NAME AS LINE_REQUESTER,
PRH.REQUISITION_HEADER_ID ,
PRH.REQUISITION_NUMBER ,
PRH.REQ_PREPARER ,
PRH.REQ_BU_NAME ,
PRH.REQ_DESCRIPTION ,
PRH.REQ_JUSTIFICATION ,
PRH.DOCUMENT_STATUS ,
PRH.PRC_BU_NAME ,
PRH.SUBMISSION_DATE ,
PRH.REQ_BU_ID,
PORLA.LINE_NUMBER,
PORLA.ITEM_DESCRIPTION,
PORLA.UOM_CODE,
(SELECT UNIT_OF_MEASURE FROM INV_UNITS_OF_MEASURE_VL WHERE UOM_CODE = NVL(PORLA.UOM_CODE,'-1') )UNIT_OF_MEASURE,
PORLA.UNIT_PRICE,
PORLA.QUANTITY,
PORLA.ITEM_ID,
PORLA.CATEGORY_ID,
CASE
WHEN PORLA.PURCHASE_BASIS='GOODS' THEN PORLA.QUANTITY*PORLA.UNIT_PRICE
WHEN PORLA.PURCHASE_BASIS='SERVICES' THEN PORLA.AMOUNT
ELSE -1
END LINE_AMOUNT,
PORLA.destination_organization_id,
(SELECT ITEM_NUMBER from EGP_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = PORLA.ITEM_ID and ORGANIZATION_ID= PORLA.destination_organization_id and rownum =1) ITEM_NUMBER,
(SELECT CATEGORY_NAME FROM EGP_CATEGORIES_VL WHERE CATEGORY_ID= PORLA.category_id and rownum =1) CATEGORY_NAME,
PORLA.PURCHASE_BASIS,
PORLA.MATCHING_BASIS,
PORLA.ATTRIBUTE_CATEGORY,
PORLA.ATTRIBUTE1,
CASE WHEN TRIM(PORLA.ATTRIBUTE_CATEGORY) = 'XYZ' THEN PORLA.ATTRIBUTE1
ELSE NULL
END JOB_ORDER
FROM
(SELECT
  /*+ FIRST_ROWS(10) */
  PORHA.REQUISITION_HEADER_ID,
  MAX(PORHA.REQUISITION_NUMBER) AS REQUISITION_NUMBER,
  PERSONNAMEDPEO.FULL_NAME                    AS REQ_PREPARER,
  (select NAME from HR_ALL_ORGANIZATION_UNITS_X where ORGANIZATION_ID=PORHA.REQ_BU_ID) AS REQ_BU_NAME,
  PORHA.DESCRIPTION AS REQ_DESCRIPTION,
  PORHA.JUSTIFICATION AS REQ_JUSTIFICATION,
  GlLedgers.CURRENCY_CODE AS FUNC_CURRENCY_CODE,
  (select MEANING from FND_LOOKUPS where LOOKUP_TYPE='POR_DOCUMENT_STATUS' and LOOKUP_CODE=PORHA.DOCUMENT_STATUS) AS DOCUMENT_STATUS,
  (select NAME from HR_ALL_ORGANIZATION_UNITS_X where ORGANIZATION_ID=PORHA.PRC_BU_ID) AS PRC_BU_NAME,
  MAX(PORHA.SUBMISSION_DATE) AS SUBMISSION_DATE,
  PORHA.REQ_BU_ID
FROM POR_REQUISITION_HEADERS_ALL PORHA,
  POR_REQUISITION_LINES_ALL PORLA,
  POR_REQ_DISTRIBUTIONS_ALL PORDA,
  FUN_ALL_BUSINESS_UNITS_V BusinessUnitPEO,
  FINANCIALS_SYSTEM_PARAMS_ALL FINANCIALOPTIONSPEO,
  PER_PERSON_NAMES_F_V PERSONNAMEDPEO,
  GL_LEDGERS GlLedgers
WHERE PORHA.REQUISITION_HEADER_ID          = nvl(:P_REQ_HEADER_ID, PORHA.REQUISITION_HEADER_ID)
AND BusinessUnitPEO.BU_ID                                = PORHA.REQ_BU_ID
AND BusinessUnitPEO.PRIMARY_LEDGER_ID                    = TO_CHAR(GlLedgers.LEDGER_ID)
AND PORHA.REQUISITION_HEADER_ID            = PORLA.REQUISITION_HEADER_ID
AND PORLA.REQUISITION_LINE_ID                = PORDA.REQUISITION_LINE_ID
AND PORLA.LINE_STATUS NOT                   IN ('SPLIT','REPLACED','CANCELED')
AND NVL(PORLA.ITEM_SOURCE, 'NOT REINSTATE') != 'REINSTATE'
AND PORHA.REQ_BU_ID                        = FINANCIALOPTIONSPEO.ORG_ID
AND PORHA.REQ_BU_ID    = :P_BUSINESS_UNIT_ID
AND PERSONNAMEDPEO.PERSON_ID               = PORHA.PREPARER_ID
AND TRUNC(sysdate) BETWEEN PersonNameDPEO.EFFECTIVE_START_DATE AND PersonNameDPEO.EFFECTIVE_END_DATE
GROUP BY PERSONNAMEDPEO.PERSON_ID,
  PERSONNAMEDPEO.FULL_NAME,
  PORHA.BUDGET_CONTROL_ENABLED_FLAG,
  PORHA.FUNDS_CHK_FAIL_WARN_FLAG,
  PORHA.PREPARER_ID,
  PORHA.JUSTIFICATION,
  PORHA.DESCRIPTION,
  PORHA.REQUISITION_HEADER_ID,
  PORHA.REQ_BU_ID,
  GLLEDGERS.CURRENCY_CODE,
  FINANCIALOPTIONSPEO.DEFAULT_EXCHANGE_RATE_TYPE,
  PORHA.DOCUMENT_STATUS,
  PORHA.PRC_BU_ID,
  PORHA.PCARD_ID,
  PORHA.SOLDTO_LE_ID,
  PORHA.OVERRIDING_APPROVER_ID,
  PORHA.FUNDS_OVERRIDE_APPROVER_ID,
  PORHA.FUNDS_STATUS,
  PORHA.DEFAULT_TAXATION_COUNTRY,
  PORHA.DOCUMENT_SUB_TYPE
  )PRH,
  POR_REQUISITION_LINES_ALL PORLA,
  PER_PERSON_NAMES_F_V PersonNameDPEO_L
WHERE   1=1
AND PRH.REQUISITION_HEADER_ID            = PORLA.REQUISITION_HEADER_ID
AND PORLA.REQUESTER_ID                 = PersonNameDPEO_L.PERSON_ID
AND TRUNC(sysdate) BETWEEN PersonNameDPEO_L.EFFECTIVE_START_DATE AND PersonNameDPEO_L.EFFECTIVE_END_DATE
AND PRH.REQUISITION_HEADER_ID            = :P_REQ_HEADER_ID

fnd_global in Oracle Apps vs Oracle Fusion

Oracle EBS has fnd_global package, which sets global variables, in turn available to be access through custom plsql code, java code etc.
However in Oracle Fusion there is slight change, instead of fnd_global.user_name (refers to fnd_user table , user_name column), we need to use fnd_global.who_user_name (per_users table, username column)


FND_GLOBAL.USER_NAME  -> FND_GLOBAL.WHO_USER_NAME (per_users table, username column)
->(FND_GLOBAL.USER_GUID (per_users table, user_guid column)


You may run below sql in FUSION_FSCM, Data-Model- SQL based:

select * from all_source where name like 'FND_GLOBAL'

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