Convert Number into words which will help in check printing in oracle fusion BIP template

<?xdofx:to_check_number(SALARY_AMOUNT,’JPY’,’CASE_INIT_CAP’)?>

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

Attribute
Description
Valid Value
amount
The number to be transformed.
Any number
precisionOrCurrency
For this attribute you can specify either the precision, which is the number of digits after the decimal point; or the currency code, which governs the number of digits after the decimal point. The currency code does not generate a currency symbol in the output.
An integer, such as 2; or a currency code, such as 'USD'.
caseType
The case type of the output.
Valid values are: 'CASE_UPPER', 'CASE_LOWER', 'CASE_INIT_CAP'
decimalStyle
Output type of the decimal fraction area.
Valid values are: 'DECIMAL_STYLE_FRACTION1', 'DECIMAL_STYLE_FRACTION2', 'DECIMAL_STYLE_WORD'
The following table displays the example function as entered in an RTF template and the returned output.
RTF Template Entry
Returned Output
<?xdofx:to_check_number(12345.67, 2)?>
Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345.67, 'USD')?>
Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')?>
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
<?xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')?>
twelve thousand three hundred forty-five and sixty-seven

How to get Invoice Amount and its tax from payables in oracle fusion and from SLA tables

 select distinct aia.invoice_id,

aia.INVOICE_CURRENCY_CODE "Functional Currency"

,(select distinct name from xle_entity_profiles       

where legal_entity_id in (    select default_legal_context_id from hr_operating_units   

where organization_id=aia.org_id))company 

,aila.tax_classification_Code  "Tax Code"

,aia.invoice_num "Transaction Number"

,aia.invoice_type_lookup_code "Transaction Class"

,'' Related_Transaction

,aia.invoice_date "Transaction_Date"

,(Select hp.party_name from hz_parties hp where aia.party_id = hp.party_id and rownum=1) "Billing Partner Name"

,zl.TAX_REGISTRATION_NUMBER

,aila.Amount "Taxable_Amt"

,zl.Tax_Amt

,zl.Tax_regime_code

,zl.tax

,(

Select distinct gcc.segment4 

from gl_code_combinations gcc

where 1=1

and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID

and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER

and aida.LINE_TYPE_LOOKUP_CODE  in ('ITEM')

and aida.invoice_id=aila.invoice_id

and aila.tax_classification_code is not null

)"Line GL Account"

,(

Select distinct gcc.segment3

from gl_code_combinations gcc

where 1=1

and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID

and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER

and aida.LINE_TYPE_LOOKUP_CODE  in ('ITEM')

and aida.invoice_id=aila.invoice_id

and aila.tax_classification_code is not null

)"Line Cost Centre"

,(

Select distinct gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 

from gl_code_combinations gcc

where 1=1

and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID

and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER

and aida.LINE_TYPE_LOOKUP_CODE  in ('ITEM')

and aida.invoice_id=aila.invoice_id

and aila.tax_classification_code is not null

)"Line Account Combination"

,(select distinct gcc.segment4 from gl_code_combinations gcc where code_combination_id  IN

(SELECT tax_account_ccid

FROM zx_rates_b RB, ZX_ACCOUNTS TA

WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'

and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id

and rb.tax_rate_code=aila.tax_classification_Code

and ta.ledger_id=zl.ledger_id

and rb.ACTIVE_FLAG='Y')

) "Tax GL Account"

,(select distinct gcc.segment3 from gl_code_combinations gcc where code_combination_id  IN

(SELECT tax_account_ccid

FROM zx_rates_b RB, ZX_ACCOUNTS TA

WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'

and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id

and rb.tax_rate_code=aila.tax_classification_Code

and ta.ledger_id=zl.ledger_id

and rb.ACTIVE_FLAG='Y')

) "Tax Cost Centre"

,(select distinct gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 from gl_code_combinations gcc where code_combination_id  IN

(SELECT tax_account_ccid

FROM zx_rates_b RB, ZX_ACCOUNTS TA

WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'

and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id

and rb.tax_rate_code=aila.tax_classification_Code

and ta.ledger_id=zl.ledger_id

and rb.ACTIVE_FLAG='Y')

) "Tax Account Combination"

from ap_invoices_all aia

,ap_invoice_lines_all aila

,ZX_LINES zl

,xla_transaction_entities xte

,xla_Ae_headers xah

,xla_events xe

,ap_invoice_distributions_all aida

--,xla_ae_lines xal

where 1=1 

--and aila.tax_classification_code is not null

--and aia.invoice_num in ('Test tax invoice','Test9879','Test9878','Test098')

--and aia.invoice_num in ('Tax Test Jun-24')

And aia.invoice_num=zl.trx_number

and zl.trx_id = aila.invoice_id

and zl.trx_line_id = aila.line_number

and zl.entity_code ='AP_INVOICES'

and aia.invoice_id=aila.invoice_id

and aila.invoice_id=aida.invoice_id

and aila.line_number=aida.invoice_line_number

and upper(aia.approval_status) IN ('APPROVED','CANCELLED')

and aia.invoice_id = xte.source_id_int_1

and xah.gl_transfer_status_code = 'Y'

and xte.LEDGER_ID =aia.SET_OF_BOOKS_ID

AND XTE.ENTITY_CODE='AP_INVOICES'

AND xe.ENTITY_ID                =XTE.ENTITY_ID

AND xe.EVENT_ID                 =xah.EVENT_ID

--and xal.ae_header_id=xah.ae_header_id

and xah.entity_id = xte.entity_id

and aia.org_id = :p_Business_unit

and aida.accounting_date between :p_from_date and :p_to_date

How to set auto width of column in RTF Template in oracle fusion

 Insert Table in the RTF template , Insert XML schema for columns. 

For example : 

Now click on individual cells and select TABLE PROPERTIES , click on Column and change the "Measure in:" : Percent 

We have Previous Column and Next column in the above screenshot, Click on next or previous and change the "Preferred Width" in %. It will change dynamically. 

Above screenshot is for Column1 and then we can navigate to further columns.

See below as example :

Calling of subtemplate from Main template in BI Publisher in Oracle Fusion

 Sometimes there are requirement where header and footer are changing and rest content/body of template is same.

For example there are multiple addresses for any parent company.

In that case we can create subtemplate and call that subtemplate in parent template.

Creating Subtemplate:

In RTF template , do below

<?template:MyAddressUS?>
My Company
500 Main Street
Any City, CA 98765
<?end template?>                                                                    

?template:MyAddressIndia?>
My Company
500 Main Street
India
<?end template?>    

Now we can save that template in fusion, Go to oracle fusion

its extension will be .xsb

Now we need to call that template in our main template, 
wherever we want to call in the main template

Calling of Subtemplate from Main template:
In the Main template enter the following import statements in the form field or directly
anywhere in the template.

<?import:xdoxsl:///Customer Reports/Templates/Sub_Template_Address_XXX.xsb?>
In Main template where you want to show that address, use below Call command
<?call-template:MyAddressUS?>
At runtime, parent template will take care of those path.
To handle parameterized layout , use below command
In the RTF template define a parameter using syntax - 
<?param@begin:ParameterName?>
For example:
<?param@begin:DeptName?>

Happy Learning . 

How to invoke parametric BIP Report from ESS job

 Hi Team, 

First create Data mode and report with parameters. 

Let suppose 3 parameters

business Unit Id 

From date 

To Date

Now we need to create parametric ESS job with 3 parameters itself. 

Please make sure that order of ESS Job parameters and BIP Data model should be same. 

In ESS Job , it should be 

Business Unit Id

From Date 

To Date.

Please make sure to provide correct path for BIP report also.

In ESS job :

Path should be : /oracle/apps/ess/custom/Financials/XXXX

if report comes under financials and then XXXX

Give exact xdo path for report id 

Happy Learning.
 

Passing date type parameter from an ESS job to BIP SQL data model in oracle fusion application

 Hi Team , 

When you are passing date parameter from ESS Job to BIP report then it is not fetching data due to wrong parameters.

When opening XML it is showing

Although we have given DATE parameter in ESS job as 

Still it is showing in XML document as wrong format date. 

Solution: Make the date format in SQL BIP data model as yyyy-MM-dd 

and in ESS Job also , select Date parameter as yyyy-MM-dd

and then submit ESS job, it will display output also and see XML also.

It will display correct output. 

Thanks and Happy Reading.

How to use Date function in excel BI Publisher

 

Formatting Dates

Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.

One option to display a date is to use the Excel REPLACE and SUBSTITUTE functions. This option retains the full date and timestamp. If you only require the date portion in the data (YYY-MM-DD), then another option is to use the DATEVALUE function. The following example shows how to use both options.

Example: Formatting a Canonical Date in Excel

Using the Employee by Department template and data from the first example, this procedure adds the HIRE_DATE element to the layout and displays the date as shown in Column E of Figure 6-18.

Figure 6-18 The Employee by Department Template Showing the Hire Date

Description of "Figure 6-18 The Employee by Department Template Showing the Hire Date"

To format the date:

1. Add a column to the table in your layout for HIRE_DATE.

2. In the table row where the data is to display, use the Template Builder to insert the HIRE_DATE field.

   Note:

   If you are not using the Template Builder, copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example:

   Copy and paste

   1996-02-03T00:00:00.000-07:00

   into the E8 cell.

   Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data.

   The inserted field is shown in Figure 6-19.

   Figure 6-19 Inserting the HIRE_DATE Field

   
   Description of "Figure 6-19 Inserting the HIRE_DATE Field"
   
   

   If you do nothing else, the HIRE_DATE value is displayed as shown. To format the date as "3-Feb-96", you must apply a function to that field and display the results in a new field.

3. Insert a new Hire Date column. This is now column F, as shown in Figure 6-20.

   Figure 6-20 The New Hire Date Column in Column F

   
   Description of "Figure 6-20 The New Hire Date Column in Column F"
   
   

4. In the new Hire Date cell (F8), enter one of the following Excel functions:

   • To retain the full date and timestamp, enter:

     =--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
     

   • To retain only the date portion (YYY-MM-DD), enter:

     =DATEVALUE(LEFT(E8,10))
     

   Notice that in both functions, "E8" refers to the cell that contains the value to convert.

   After you enter the function, it populates the F8 cell as shown in Figure 6-21.

   Figure 6-21 Hire Date Cell (F8) Populated

   
   Description of "Figure 6-21 Hire Date Cell (F8) Populated"
   
   

5. Apply formatting to the cell.

   Right-click the F8 cell. From the menu, select Format Cells. In the Format Cells dialog, select Date and the desired format, as shown in Figure 6-22.

   Figure 6-22 Applying the Format for the Date in the Format Cells Dialog

   
   Description of "Figure 6-22 Applying the Format for the Date in the Format Cells Dialog"
   
   

   The sample data in the F8 cell now displays as 3-Feb-96.

6. Hide the E column, so that report consumers do not see the canonical date that is converted.

   Figure 6-23 shows the template with column E hidden.

   Figure 6-23 Completed Example

   
   Description of "Figure 6-23 Completed Example"