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