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'