Oracle E-Business Tax and Brexit

As the United Kingdom (UK) leaves the EU from the 1/1-2021; E-Business Tax (ZX/EBTax) setup will require changes.

Disclaimer and warning:

EBTax setup is complex and flexible therefore the example below is unlikely to work with your instance, so please do use a skilled consultant for the changes and test carefully.

All the examples below are from a R12.2.9 Vision instance using:

User: UK User

Responsibility: Tax Managers Vision UK

The examples are from an upgrade so not typical for a greenfield implementation but the principle is the same.

Assumptions after 01-Jan-2021:

  • UK will not be part of the EU customs union
  • UK will treat any EU transaction as pure import/export as it was outside the EU

We will look at two scenarios:

  1. EU-based company: A company and legal entity based in one of the remaining the EU countries
  2. UK-based company: A company and legal entity based in the UK only

There are many combinations of the above like if the UK based company have a registered office in the EU it may be necessary to account for both UK and EU VAT.

EU-Based Company

After Brexit, an EU-based company should treat trade with a company in the UK as outside the EU.

To do this, simply end-date the UK in the EU zone definition.

It is important to end-date in case of back dated transactions.

Also ensure to use 01-Jan-2021 as end-date as this is 01-JAN-2021 00:00 so just after midnight of 31-DEC-2020.

Technically it is 31-DEC-2020 23:00 but time cannot be entered into the End Date field; so avoid entering transactions between 11pm and midnight.

Responsibility: E-Business Tax Managers

Menu: Advanced Setup Options > Tax Zone Types

Click: View Existing Zones

Click: Update

End Date the United Kingdom entry: 01-Jan-2021

Click: Apply

All done; now test!

UK-Based Company

After Brexit, a UK-based company should regard trade with an EU company as normal export/import without respect to VAT registration etc. This means tax rules needs to be changed.

We need two steps in this case:

  1. End-date existing tax rules
  2. Create new tax rules

End-date Existing Tax Rules

Menu: Tax Configuration > Tax Rules

Configuration Owner: Global Configuration Owner

Tax Regime Code: GB-Tax

Tax: GB-VAT

In this case we need to end-date rules with EU related tax rules.

We may normally have EU related tax rules in:

  • Determine Place of Supply
  • Determine Tax Registration
  • Determine Tax Status
  • Determine Tax Rate

Direct Tax Rate Determination: this is normally only used for upgrades and should not contain any EU related rules.

In the Vision example it looks like this:

The tax rules of interest in the above are:

  • GB VAT PLACE OF SUPPLY
  • GB VAT REGISTRATION
  • GB VAT STATUS

There are no “Determine Tax Rate” tax rules in this case. This is because you can pick the tax rate either in “Determine Tax Status” or “Determine Tax Rate” for the same result.

For the purpose of the example the approach is the same for both types of rules.

Again, ensure to use 01-Jan-2021 as end-date as this is 01-JAN-2021 00:00 so just after midnight of 31-DEC-2020.

End-date rule GB VAT PLACE OF SUPPLY

For GB VAT PLACE OF SUPPLY click: Expert Rule Entry

Effective To: 01-Jan-2021

To review the condition sets, click: Next

This rule has the following active condition sets:

  • GB INTRA EU GDS PURCH: Not needed after Brexit as related to EU only
  • GB POS PURCHASE INTRA-EU SERVICES: Not needed after Brexit as related to EU only

As the above condition sets will not be needed after Brexit; no need to create new rules.

After the review, to save click: Next and Finish

End-date rule GB VAT REGISTRATION

For GB VAT REGISTRATION click: Expert Rule Entry

Effective To: 01-Jan-2021

To review the condition sets, click: Next

This rule has the following active condition sets:

  • GB GDS PURCH REV CHG: Not needed after Brexit as related to EU only
  • GB VAT PURCHASE INTRA-EU SERVICES: Not needed after Brexit as related to EU only

As the above condition sets will not be needed after Brexit; no need to create new rules.

After the review, to save click: Next and Finish

End-date Rule GB VAT STATUS

For GB VAT STATUS click: Expert Rule Entry

Effective To: 01-Jan-2021

This rule has the following active condition sets in priority order:

NameResultPurpose
GB EXPORT SALESGB VAT ZEROChecks if goods sale is outside the EU
GB INTRA EU GDS SALE ZERO RATEDGB VAT INTRA EU ZEROChecks if registered inside the EU
GB VAT INTRA-EU SERVICESGB VAT INTRA EU ZEROChecks if registered inside the EU
GB GOODS EXEMPT RATEGB VAT EXEMPTInventory classification of GB Exempt Goods
GB GOODS REDUCED RATEGB VAT REDUCEDInventory classification of GB Reduced Rate Goods
GB GOODS ZERO RATEGB VAT ZEROInventory classification of GB Zero Rated Goods
GB SERVICES ZERO RATEGB VAT ZEROInventory classification of GB Zero Rated Services
GB SERVICES EXEMPT RATEGB VAT EXEMPTInventory classification of GB Exempt Services
GB SERVICES REDUCED RATEGB VAT REDUCEDInventory classification of GB Reduced Rate Services

Most of the above rules will be needed after Brexit except for:

  • GB INTRA EU GDS SALE ZERO RATED
  • GB VAT INTRA-EU SERVICES

One rule needs to be altered to exclude EU conditions:

  • GB EXPORT SALES

After the review, to save click: Next and Finish

Create New Tax Rules

The old end-dated rules we based on the Determining Factor Sets:

  • GB VAT PLACE OF SUPPLY: No new rule needed
  • GB VAT REGISTRATION: No new rule needed
  • GB VAT STATUS: Rules for manual classification and export sales needed

As we cannot reuse the Tax Determining Factor Set used in the end-dated rule sets; we need to create a new one.

Best practise is to split manual/inventory-based classification rules from other rules but in this case, we will just use a single rule set as an example.

So for GB VAT STATUS we need to:

  1. Create one new Tax Determining Factor Set
  2. Create 7 new Tax Conditions Sets using the Tax Determining Factor Set
  3. Create one new Tax Rule using the Tax Condition Sets

Tax Determining Factor Set

The old Tax Determining Factor Set was:

Determining Factor ClassClass QualifierDetermining Factor NameRequiredNeeded
Transaction Generic Classification Transaction Business CategoryNoYes
RegistrationBill From PartyRegistration StatusNo 
RegistrationBill To PartyRegistration StatusNo 
Transaction Input Factor Product TypeNoYes
User Defined GeographyShip FromEU_TAX_ZONENo 
User Defined GeographyShip ToEU_TAX_ZONENo 
GeographyShip FromCountryNoYes
GeographyShip ToCountryNoYes
Product – Inventory Linked GB Product Classification Level 2NoYes

After Brexit we will only need the Determining Factor Classes as per column Needed = Yes

Transaction Generic Classification is not really needed either in the example.

However after Brexit; Registration Status and EU_TAX_ZONE is not relevant any more.

Menu: Advanced Setup Options > Tax Determining Factor Sets

Tax Determining Factor Set/Name: GB VAT BREXIT

Set Usage: Tax Rules

Regime Code: GB-Tax

Associate Tax Determining Factors:

Determining Factor ClassClass QualifierDetermining Factor NameRequired
Transaction Generic Classification Transaction Business CategoryNo
Transaction Input Factor Product TypeNo
GeographyShip FromCountryNo
GeographyShip ToCountryNo
Product – Inventory Linked GB Product Classification Level 2No

Tax Condition Sets

We will need the following condition sets:

Tax Condition Code/NamePurpose
GB VAT BREXIT EXPORTExports
GB VAT BREXIT GOO EXEInventory classification of GB Exempt Goods
GB VAT BREXIT GOO REDInventory classification of GB Reduced Rate Goods
GB VAT BREXIT GOO ZERInventory classification of GB Zero Rated Goods
GB VAT BREXIT SER EXEInventory classification of GB Zero Rated Services
GB VAT BREXIT SER REDInventory classification of GB Exempt Services
GB VAT BREXIT SER ZERInventory classification of GB Reduced Rate Services

Menu: Advanced Setup Options > Tax Condition Sets

Click: Create

Tax Condition Set/Name: GB VAT BREXIT EXPORT

Determining Factor Set Name: GB VAT BREXIT

Click: Continue

Determining Factor ClassClass QualifierDetermining Factor NameOperatorValueIgnore Condition
Transaction Generic Classification Transaction Business Category  Yes
Transaction Input Factor Product Type  Yes
GeographyShip FromCountry  Yes
GeographyShip ToCountryNo Equal ToUnited KingdomNo
Product – Inventory Linked GB Product Classification Level 2  Yes

Click: Create

Tax Condition Set/Name: GB VAT BREXIT GOO EXE

Determining Factor Set Name: GB VAT BREXIT

Click: Continue

Determining Factor ClassClass QualifierDetermining Factor NameOperatorValueIgnore Condition
Transaction Generic Classification Transaction Business Category GB Exempt GoodsNo
Transaction Input Factor Product Type  Yes
GeographyShip FromCountry  Yes
GeographyShip ToCountry  Yes
Product – Inventory Linked GB Product Classification Level 2  Yes

Same procedure for:

  • GB VAT BREXIT GOO RED: GB Reduced Rate Goods
  • GB VAT BREXIT GOO ZER: GB Zero Rated Goods
  • GB VAT BREXIT SER EXE: GB Exempt Services
  • GB VAT BREXIT SER RED: GB Reduced Rate Services
  • GB VAT BREXIT SER ZER: GB Zero Rated Services

Tax Rules

Menu: Tax Configuration > Tax Rules

Configuration Owner: Global Configuration Owner

Tax Regime Code: GB-Tax

Tax: GB-VAT

Select Determine Tax Status and click: Expert Rule Entry

Rule Code/Name: GB VAT BREXIT

Effective From: 01-Jan-2021

Determining Factor Set Code: GB VAT BREXIT

Click: Next

Condition Set Code: GB VAT BREXIT EXPORT

Click:

Pick Condition Set Name: GB VAT BREXIT EXPORT

The above is a long standing “feature”; entering a unique Condition Set Code does not automatically select the name.

Order: 10

Result: GB VAT ZERO

Click: Add or

Condition Set Code: GB VAT BREXIT GOO EXE

Click:

Pick Condition Set Name: GB VAT BREXIT GOO EXE

Order: 20

Result: GB VAT EXEMPT

Do same for remaining order 30-70:

Tax Condition Code/NameOrderResult
GB VAT BREXIT EXPORT10GB VAT ZERO
GB VAT BREXIT GOO EXE20GB VAT EXEMPT
GB VAT BREXIT GOO RED30GB VAT REDUCED
GB VAT BREXIT GOO ZER40GB VAT ZERO
GB VAT BREXIT SER EXE50GB VAT ZERO
GB VAT BREXIT SER RED60GB VAT EXEMPT
GB VAT BREXIT SER ZER70GB VAT REDUCED

Click: Next

Rule Order: 5

Enable: Yes

Click: Finish

All done; now test!

Technical Appendix

Tables:

  • Tax zone type: HZ_GEOGRAPHY_TYPES_B
  • Tax zones: HZ_GEOGRAPHIES
  • Tax zone members: HZ_RELATIONSHIPS
select *
from HZ_GEOGRAPHY_TYPES_B
where GEOGRAPHY_TYPE like 'EU%'
;
GEOGRAPHY_TYPEGEOGRAPHY_USE
EU_TAX_ZONETAX
select *
from HZ_GEOGRAPHIES
where GEOGRAPHY_TYPE='EU_TAX_ZONE'
;
GEOGRAPHY_NAMEGEOGRAPHY_USEGEOGRAPHY_CODE
European Economic CommunityTAXEEC
select
  g.geography_name "Geography Name",
  to_char(r.start_date,'dd-mon-yyyy hh24:mi') "Start Date",
  to_char(r.end_date,'dd-mon-yyyy hh24:mi') "End Date"
from
  ar.HZ_RELATIONSHIPS r,
  ar.HZ_GEOGRAPHIES g
where 1=1 
and r.subject_id=g.geography_id
and r.object_type like 'EU%'
and r.relationship_type='TAX'
and g.geography_name='United Kingdom' 
;
Geography NameStart DateEnd Date
United Kingdom01-jan-1974 00:0001-jan-2021 00:00

Notice the default time for the date is 00:00 at midnight.

Posted in: E-Business Tax, Functional Knowledge, Uncategorized by Kent Willumsen Comments Off on Oracle E-Business Tax and Brexit , , ,

Draytek 2760 and Spanish Orange ONT

Creating another networking post as I did find this critical to working my business from Spain.

With a standard Spanish Oracle connection you get a so called “Livebox” , which is an fairly advanced consumer router. However for my LAN to LAN VPN connection to York, this router is no good and I need my Draytek 2760 to work for this.

This is how to set it up.

Connection diagram:

  • Nokia ONT: connects to Orange fibre network
  • Draytek 2760: VPN router connecting using IPSEC with IKEv2 to York
  • Unifi USG: LAN router
  • Unifi Switch: managed switch

The LAN setup is similar to my previous post on Draytek 2860 and Unifi USG.

However the connecting from a Draytek to a Spanish Orange ONT is as follows:

Draytek 2760 Settings

WAN

General Setup – WAN2:

Settings:

  • Tag value: 832 (required for Orange)
  • Priority: 0
  • WAN2 will enable port P4 as WAN, which should be connected to the ONT
  • Display Name: Oniti is the Orange provider

General Setup – Internet Access

Settings:

  • Obtain an IP address automatically (the blue covered bit are from earlier tests)
  • Enable PING (was the best solution for me but not required)
  • MAC Address: MAC address of the LiveBox (as the installation technician enters this into the ONT, I think)

NAT

DMZ Host

Create a WAN2 DMZ connection to the USG to minimise double NAT

LAN

Just a couple of LAN notes similar to my earlier Draytek 2860 post.

General Setup

VLAN

  • 192.168.9.0/24: DMZ network with DHCP (exposed to the web) connected to USG via port P1
  • 192.168.8.0/24: LAN network with no DHCP as this is controlled by the USG and connected to USG LAN via the Unifi Switch from port P3
  • The is no routing between the 8 and 9 networks (naturally to enforce the USG firewall)
Posted in: Networking, Technical Knowledge by Kent Willumsen Comments Off on Draytek 2760 and Spanish Orange ONT , , ,

Draytek 2860 and Unifi USG

I normally don’t do networking posts but in this case I think there may be a need as I could not find any solutions for this anywhere else.

This solution uses a Draytek 2860 as a VDSL modem, VPN and LAN-LAN device.

Using a Draytek 2860 as modem and Unifi USG as gateway is not without it’s problems.

Why using Draytek as modem:

  • Draytek is a modem working well with a BT FTTC VDSL connection
  • Draytek is great for client VPN and LAN-LAN connections both using IPsec
  • Draytek and USG can always be reached via the client VPN access – so accidental lock-out is (almost) impossible

Why using Unifi USG as gateway:

  • Unifi USG provides great network management via the Unifi controller

Problems to be solved with the solution below:

  • USG is in a double-NAT situation
    • Solved with DMZ on Draytek
  • Draytek incoming VPN wants to route through itself bypassing the USG
    • Solved with policy route on Draytek forcing USG gateway
  • Draytek has two DMZ modes and only one works:
    • Solved by using “Private IP” DMZ
    • Private IP: semi-NAT mode where some functionality works
      • Works: modem, VPN, LAN-LAN and remote router management
      • Does not work: Port forwarding (makes sense)
    • True IP: bridge mode where Draytek only works as modem
      • Works: modem
      • Does not work: everything else
  • Port forwarding to WordPress Server
    • Solved by using port forward on Unifi USG
  • Route VPN and LAN-LAN around the DMZ
    • Draytek LAN1 to USG LAN direct connection via switch
    • USG WAN2 could have been used but this is better to use for WAN failover

Connection diagram:

Draytek Setup (relevant to this scenario): 

  • WAN: nothing special to do
  • LAN: create two networks: DMZ and LAN1:
    • DMZ network: semi-NAT network to Unifi USG
      • For NAT Usage
      • IP Address: 192.168.7.3
      • DHCP Enable Server
      • Start IP address: 192.168.7.100 (USG WAN)
      • Gateway IP: 192.168.7.3
    • LAN1 network: This is used to route around the DMZ
      • For NAT Usage
      • IP Address: 192.168.1.3 (as the USG is 192.168.1.1)
      • DHCP Enable Relay
      • DHCP Server IP: 192.168.1.1 (USG LAN)
    • VLAN: map your LAN1 to port 2 (port 1 is reserved for DMZ)
  • Routing: create a route policy for LAN1
    • Load-Balance/Route Policy:
      • Protocol: Any
      • Source: IP Subnet: 192.168.1.0/24
      • Destination: Any
      • Destination Port: Any
      • Interface: LAN1
      • Specific Gateway: 192.168.1.1 (USG LAN)
      • Priority: 100
  • NAT:
    • DMZ Host: Private IP
    • Private IP: 192.168.7.100 (USG WAN)
  • Firewall: Disable all (USG is the firewall)
  • VPN and Remote Access:
    • Remote Dial-in User:
      • Allowed Dial-in type: L2TP/IPsec with pre-shared key
      • Subnet: LAN1
    • LAN to LAN:
      • Allowed Dial-in type: IPsec Tunnel
      • Local Network IP: 192.168.1.0/24
      • …and much more

Unifi USG Setup (relevant to this scenario):

  • WAN Networks:
    • WAN:
      • IPV4 Connection Type: Using DHCP
  • LAN Networks:
    • LAN:
      • Gateway IP / Subnet: 192.168.1.1/24
      • DHCP server enabled
  • Gateway:
    • Port Forwarding:
      • Name: http
      • From: Anywhere
      • Port: 80
      • Forward IP: WordPress server IP
      • Forward Port: 80
      • Protocol: Both

Posted in: Networking, Technical Knowledge by Kent Willumsen Comments Off on Draytek 2860 and Unifi USG , , ,

Java Web Start

As IE support is dying, Oracle have released a new way to launch the Java forms called Java Web Start – JWS.

JWS was introduced with R12.2.3 and in R12.1.3 with patches.

Have a look at this Oracle blog.

The low-down is you can enable JWS in 2 ways

  1. Update the context file
  2. Update profile options

As changing the context file needs a DBA so it is better just to update the profile options.

The two profile options we need are:

  • Enable Java Web Start (code: FND_ENABLE_JAVA_WEB_START)
  • ICX: Forms Launcher (code: ICX_FORMS_LAUNCHER)

Default values for legacy mode (URL is for R12.2.9 VISION instance):

  • Enable Java Web Start: No
  • ICX: Forms Launcher: http://apps.example.com:8000/forms/frmservlet

Value for JWS are:

  • Enable Java Web Start: Yes
  • ICX: Forms Launcher: http://apps.example.com:8000/forms/frmservlet?config=jws

To update the profile options we have 3 options:

  1. Use the usual System Profile Options Java form (if you have access to this)
  2. Use the web based form
  3. Use a PL/SQL script

Note the profile options can also be set at user level but examples below are for site level only.

Java Form

As responsibility “System Administrator” navigate to: \Profile\System

Web Form

As responsibility “Functional Administrator” navigate to:
\Core Services\Profiles

PL/SQL Script

declare
	procedure set_site_value(p_profile varchar,p_value varchar) is
		l_result boolean;
	begin
		l_result:=fnd_profile.save(
			x_name=>p_profile,
			x_value=>p_value,
			x_level_name=>'SITE'
		);
	end;
	function get_site_value(p_profile varchar) return varchar is
		l_value varchar2(240);
		l_is_defined boolean;
	begin
		fnd_profile.get_specific(
			name_z=>p_profile,
			val_z=>l_value,
			defined_z=>l_is_defined
		);
		return l_value;
	end;
	procedure enable_browser is
	begin
		set_site_value('FND_ENABLE_JAVA_WEB_START','N');
		set_site_value('ICX_FORMS_LAUNCHER','http://apps.example.com:8000/forms/frmservlet');
	end;
	procedure enable_jws is
	begin
		set_site_value('FND_ENABLE_JAVA_WEB_START','Y');
		set_site_value('ICX_FORMS_LAUNCHER','http://apps.example.com:8000/forms/frmservlet?config=jws');
	end;
	procedure show_current is
	begin
		dbms_output.put_line('Current Settings');
		dbms_output.put_line('Enable Java Web Start: '||get_site_value('FND_ENABLE_JAVA_WEB_START'));
		dbms_output.put_line('ICX: Forms Launcher: '||get_site_value('ICX_FORMS_LAUNCHER'));
	end;
begin
	show_current;
	enable_jws;
	commit;
	show_current;
end;
Posted in: Technical Knowledge by Kent Willumsen Comments Off on Java Web Start , ,

R12.2.9 Notes

Just some notes around getting R12.2.9 up and running.

As R12.2.x is using Weblogic Fusion middleware you need at least 16Gb ram

Steps:

  1. Downloads
  2. Install OEL 8
  3. Install VirtualBox 6
  4. Unzip R12 files
  5. Merge R12 files
  6. Import into VirtualBox
  7. First Run
  8. Final Steps
  9. Backup and Restore

Downloads

Download from http://edelivery.oracle.com

  • Oracle Enterprise Linux 8.1 or later
  • Search for OEL
  • Download: V984216-01.iso Oracle Linux Release 8 Update 1 for x86 (64 bit), 7.2 GB
  • Oracle E-Business Suite R12.2.9 on VM
  • Search for VM suite (somehow the search engine does not work with version numbers)
  • Click: Oracle VM Virtual Appliances for Oracle E-Business Suite
  • Click: Oracle VM Virtual Appliances for Oracle E-Business Suite 12.2.9.0.0
  • Download all (63Gb)

Install OEL8

I use OEL8.1 for running VirtualBox.

An earlier version can be used but this version supports NVME on a PCIe card on which supports NVME.

Running the R12.2.2 VM on a NVME is very fast.

The OEL inside the VM is running OEL 6.1 and does not support NVME so by running the VM on OEL8.1 then NVME is supported.

I will not explain how to install Linux as this is trivial.

However note the following:

  • Disable the firewall to void port issues with R12.2.9
  • Note OEL6 uses iptables but OEL8 uses firewalld
  • As root do:
systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld

Install VirtualBox 6

As root in OEL8 do:

yum install VirtualBox-6.1

Yep that is all.

Unzip R12 files

In Windows, download 7-zip and create a script like this and run it:

@echo off
set topdir=E:\R12.2.9
set curdir=%CD%
cd %topdir%
for /r %topdir% %%i in (*.zip) do (
	echo %%i
	C:\Admin\Cloud\Programs\7-Zip\7z.exe e -y -o%topdir% %%i
)
cd %curdir%
pause

In Linux even easier, just do:

find . -name "*.zip" -exec unzip {} \;

Merge R12 files

We need to concatenate the file to create a single .ova file.

In Windows use the following script:

@echo off
set topdir=E:\R12.2.9
set curdir=%CD%
cd %topdir%
copy /b %topdir%\*.ova.?? %topdir%\r1229.ova
cd %curdir%
pause

or just do:

copy /b *.ova.?? r1229.ova

This can now be transferred to OEL8.

The files with automatically be concatenated in correct sequence.

In Linux just do:

cat *.ova.?? > r1229.ova

Import into VirtualBox

This is all done in OEL8.

Look at the readme:

  • Oracle VM Virtual Appliance for Oracle E-Business Suite Deployment Guide_Release 12.2.9.html
  • Section 3: Deploying the Oracle E-Business Suite 12.2.9 VM Virtual Appliance on Oracle VM VirtualBox

The readme zip file contain instructions on how to do this:

  • Ensure to update the drive to NVME before starting import
  • Use “bridged” network adapter

First Run

Follow readme:

  • Section 4.1: Starting the First Boot

To setup the instance.

Then follow:

  • Section 4.2: Configuring the Application Before the First Use

To enable the APPS users:

/u01/install/APPS/scripts/enableSYSADMIN.sh
/u01/install/APPS/scripts/enableDEMOusers.sh

That’s all. I skipped the rest.

Backup and Restore

To backup just shutdown the VM and use:

vboxmanage export $vmname -o /backup/oracle/somename.ova

To restore, use:

vboxmanage import /backup/oracle/somename.ova

Posted in: R12 Installation, Technical Knowledge, Virtual Manager by Kent Willumsen Comments Off on R12.2.9 Notes , , ,

BI/XML Publisher Notes

My notes for making a simple Excel report in XML Publisher without the need for a developer.

I know it is now called BI Publisher but within E-Business Suite it is mostly referred to as XML Publisher.

I feel the need for this note as there are plenty of blogs just showing examples from the examples supplied by Oracle without really understanding how it works (or why it does not work).

The example below has been tested on a R12.2.9 instance.

Some overall rules:

  • Install the BI Publisher add-in as per instructions below
  • Use .xls Excel 2003 format only
  • Only use the Excel add-in for adding fields and doing preview

BI Publisher Report Process

Overview:

  1. Develop and test SQL
  2. Create Data Template
  3. Register Data Definition
  4. Register Concurrent Program
  5. Produce Preview XML Data
  6. Create Template File in Excel
  7. Register Data Template
  8. Test Final Report

Develop and Test SQL

Rules:

  • Keep it simple
  • Use column names in select

Create SQL and ensure it works, something like this:

select user_name,description,start_date,email_address
from fnd_user
where 1=1
and end_date is null
and encrypted_user_password !='INVALID'
;

This should yield something like this:

Create Data Template

Rules:

  • Data template name should match what you want to register it as
  • Always have at least one group
  • Paste your SQL into the dataQuery CDATA field and remove the “;” if any
  • List all fields in “dataStructure” tag
  • Save file as {data template name}.xml
  • If you use parameters then note they are case sensitive
  • Some best practices to avoid mixing up fields later on:
    • Parameters: start with “P_”
    • Column aliases starts with: “T_” or “A_”
    • Elements starts with: “E_”
    • SQL Statement name: starts with “Q_”
    • Group name: starts with “G_”
<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_TEST" description="Test Report" Version="1.0">
	<dataQuery>
		<sqlStatement name="Q_USER">
			<![CDATA[
				select user_name t_user_name,description t_description,start_date t_start_date,email_address t_email_address
				from fnd_user
				where 1=1
				and end_date is null
				and encrypted_user_password !='INVALID'
			]]>
		</sqlStatement>
	</dataQuery>
	<dataStructure>
		<group name="G_USER" source="Q_USER">
			<element name="E_USER_NAME" value="T_USER_NAME" />
			<element name="E_DESCRIPTION" value="T_DESCRIPTION" />
			<element name="E_START_DATE" value="T_START_DATE" />
			<element name="E_EMAIL_ADDRESS" value="T_EMAIL_ADDRESS" />
		</group>
	</dataStructure>
</dataTemplate>

Example section with parameters:

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_IF_TRX" description="Transaction Errors" Version="1.0">
	<parameters>
		<parameter name="P_OBJECT_NAME" dataType="character" />
		<parameter name="P_OBJECT_ID" dataType="number" />
	</parameters>
...

Register Data Definition

Rules:

  • Ensure you have access to responsibility XML Publisher Administrator
  • Navigate \home\Data Definitions
  • Use name same as data template name above

The new entry should look like this:

Name:{data template name}
Code:{data template name}
Applications:{your application}
Start Date:{backdated date}
Data Template:{your XML file from above}

Register Concurrent Program

This enables us to run the above SQL code in the concurrent manager.

Rules:

  • Login with responsibility System Administrator
  • Navigate \Concurrent\Program\Define
  • Ensure Short Name is same as Data Definition Name
  • XDODTEXE is the XDO XML processor
Program:{user friendly name}
Short Name:{data definition name}
Application:{your application}
Executable Name:XDODTEXE
Output Format:XML
Use in SRS:Yes

Produce Preview XML Data

We will need some example XML data to create the output template that converts raw XML data to Excel format.

Rules:

  • Navigate \View\Requests
  • Submit the {user friendly name} from above
  • Save the output as a .xml file like xxkwi_test_output.xml
  • Check the output contains the required fields and groups

Result should look something like this:

<?xml version="1.0" encoding="UTF-8"?>
<XXKWI_TEST>
<LIST_G_USER>
<G_USER>
<E_USER_NAME>SYSADMIN</E_USER_NAME>
<E_DESCRIPTION>System Administrator</E_DESCRIPTION>
<E_START_DATE>1951-01-01T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS/>
</G_USER>
<G_USER>
<E_USER_NAME>CBROWN</E_USER_NAME>
<E_DESCRIPTION>Casey Brown</E_DESCRIPTION>
<E_START_DATE>1995-02-19T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS>nobody@localhost</E_EMAIL_ADDRESS>
</G_USER>
</LIST_G_USER>
</XXKWI_TEST>

Save the file as a XML file.

Create Template File in Excel

Now we have the data XML file we can create the template.

Rules:

  • Only use Excel .xls 2003 format
  • Keep it simple
  • Avoid formatting using the BI Publisher
  • Report output can later the formatted using Excel alone

The following steps must be done:

  1. Upload sample XML output
  2. Create headings
  3. Create field names
  4. Create groups
  5. Preview output

Upload sample XML output:

  • Add your XML output to the Sample XML:

Create headings:

  • Type headings for example in A1

Create field names (automatic method):

  • Click field:
  • Click on A2 and add the data field you want
  • BI Publisher will then create the hidden XDO_METADATA sheet:
  • Select field to create:
  • Ensure the field is having a name like: XDO_?XDOFIELD1?
  • This is translated in the in the XDO_METADATA sheet to <?E_USER_NAME?>
  • Try to click “Names” to verify BI Publisher created a name for this field:

Create field names (manual method):

I prefer this as BI Publisher very easily garble the XDO_METADATA sheet and named ranges.

  • Do not mix manual and automated field names
  • Create the sheet XDO_METADATA manually
  • This sheet should not have any data below “Data Constraints:”
  • When done hide the sheet
  • Click on A2 and enter example data like SYSADMIN
  • Click names (name manager)
  • Add a name like XDO_?{field_name}?
  • The above example should look like this: XDO_?E_USER_NAME?
  • “E_USER_NAME” matches the field name used in the XML template, hence why we need naming standards
  • Click OK

Create Groups:

BI Publisher add-in does not handle groups well so only do this manually.

  • You must have at least one group to create vertical reports
  • If the group is missing the preview will show data horizontally
  • Click on A2
  • If you have more than one field select the full range like A2:B2 but exclude the heading at A1:B1
  • If you display parameters then ensure the group excludes these as well
  • Click names (name manager) and new
  • Enter a group name like XDO_GROUP_?{group_name}?
  • This name must match the name in the XML template, hence the need for naming standards
  • The above name should look like this: XDO_GROUP_?G_USER?
  • G_USER matches the group tab in the XML template
  • Name manager should now look like:

Preview output:

  • Click Preview
  • Expected output:
  • If you get errors at this point check all your settings and in worst case clear the template as described below and start all over

Register Data Template

Rules:

  • Ensure you have access to responsibility XML Publisher Administrator
  • Navigate \home\Templates
  • Name should be same as previous name

Upload your Excel .xls template:

Name:{previous name from above}
Code:{name from concurrent program}
Application{your application}
Data Definition{name from above}
TypeMicrosoft Excel
Start Date{backdated date}
Default Output TypeExcel
Template File{filename from previous chapter}
LanguageEnglish

Test Final Report

Run the program:

Run the concurrent program and verify the output by clicking “Open”:

Should yield something like this:

All done and it works.

Issues

Excel add-in issue overview

  • Only works with Excel 2003 format .xls
  • It is not possible to use the xmlpserver login on R12.2.9 Vision instance (if you know please tell me how)
  • Adding groups using the add-in easily destroys your template; so do it manually
  • The add-in install is dodgy at best (see below)
  • Strange preview output when XDO_METADATA or Excel names are garbled

How to clean-up the Excel sheets

BI Publisher add-in often garbles both the XDO_METADATA sheet and the named ranges:

  • Delete all XDO related names like the XDO_?XDOFIELD1? name below:
  • Unhide the XDO_METADATA sheet and delete all rows under “Data Constraints:”, like the row with XDO_?XDOFIELD1? and <?USER_NAME?> below

Excel Preview all horizontal

If you are missing a group, it looks like this; where data is rendered horizontally:

Excel add-in install

This is most of what I went through to install the add-in (some of the issues below are addressed in Doc ID 2106715.1):

  1. Download from: https://www.oracle.com/middleware/technologies/bi-publisher/downloads.html
  2. Ensure to download “BIPublisherDesktop32.exe” for a 32-bit Microsoft Office Installation, the one you should use anyway to be compatible with WebADI
  3. Right-click and select “Run as Administrator” otherwise the installation fails
  4. Once the installations is complete CD to the directory: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop
  5. CD to subdirectory: DotNetInstallFiles
  6. Double-click on ETBAddInSetup to install 32-bit Excel add-in
  7. Once installed go to Start Menu and click: Enable or Disable Template Builder
  8. First click OK on:
  9. Then click OK on:
  10. Run it again and click on Button1:
  11. Click on Button1 again:
  12. Now try and open Excel and look for the “BI Publisher” ribbon:
  13. If it is still missing then try to go to options add-ins:
  14. If present then select Manage COM add-ins and click Go…:
  15. Uncheck the BI Publisher add-in and click OK:
  16. Go back to options and manage add-ins and re-check the add-in and click OK:
  17. That should do the trick otherwise retry from start as it is easy to miss a step
  18. To use the Oracle supplied examples copy the following to a local directory: Template Builder for Word\samples\Excel templates
  19. Install Java jre 8.x and add this to the options:

Excel add-in shows “Preview Failed”

This is caused by using Excel .xlsx format. Save the document as .xls and retry.

After clicking:

image

you get this:

Start Excel Preview
only open: false
mTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp\tmp\tmp.xls
mTmpTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp/tmp.xls
java.lang.NullPointerException
	at oracle.xdo.excel.user.ximpl.XCellImpl.setStyle(XCellImpl.java:65)
	at oracle.xdo.template.excel.render.BookDataWriter.copyCell(BookDataWriter.java:743)
	at oracle.xdo.template.excel.render.BookDataWriter.finishCell(BookDataWriter.java:696)
	at oracle.xdo.template.excel.render.CellHandler.endElement(CellHandler.java:111)
	at oracle.xdo.template.excel.render.BookDataParser.endElement(BookDataParser.java:215)
	at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:211)
	at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1303)
	at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:340)
	at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:307)
	at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:298)
	at oracle.xdo.template.excel.render.BookDataParser.parserXMLDocument(BookDataParser.java:146)
	at oracle.xdo.template.excel.ExcelController.processActionLanguage(ExcelController.java:415)
	at oracle.xdo.template.excel.ExcelController.process(ExcelController.java:268)
	at oracle.xdo.template.ExcelProcessor.process(ExcelProcessor.java:244)
	at ExcelPreview.runXDO(ExcelPreview.java:110)
	at ExcelPreview.main(ExcelPreview.java:87)

Posted in: Reporting, Technical Knowledge by Kent Willumsen Comments Off on BI/XML Publisher Notes ,

Dell PowerEdge T20 Notes

Bought one of these for running various business services.

image

Got it cheap with cashback for less than the CPU would cost in retail.

The CPU and motherboard supports:

  • VT-x: Hardware virtualization
  • VT-d: Direct I/O virtualization
  • vPro: Active management technology (AMT)

Standard specifications:

  • Intel Xeon E3-1225V3
  • Ram 4Gb
  • 1Gb hard disk

Additional components:

  • Kingston 2x8Gb Ram KTD-PE313E/8G
  • Syba SATA III 4 Port PCI-e x1 Controller Card
  • Inateck 2x 2.5 Inch SSD to 3.5 Inch Internal Hard Disk Drive Mounting Kit Bracket
  • HP NC364T 436431-001 435506-003 – Quad / 4 Port Gigabit Ethernet Card Adapter

Hardware Notes

The quad NIC adapter is to enable physical separation of intranet and internet traffic.

ESXi likes to use a single NIC for a single virtual switch for a single subnet.

Hence if you want intranet and internet physically separated for VM’s you just have two virtual switches – one for intranet and one for internet – each mapped to a physical NIC.

The router the isolated the physical ports to virtual networks.

This means it is very hard to break in to the intranet even if the internet VM is compromised.

Details about this belongs in a dedicated network and router post later.

AMT Notes

Enabling AMT

During boot press ctrl-P to enable.

Connecting to AMT: http://{hostname}:16992/logon.htm

Even better download Meshcommander

Looks like this:

image

AMT and VPN Issues

If you connect via VPN you may find you cannot connect to AMT.

It took me a while to find out why not…

This may be caused by you assigned a fixed IP address to the server, which is a normal thing to do for server, however it just doesn’t work then.

Be sure to use DHCP and then use the router to force the IP address using bind to IP:

image

So do as above…

Posted in: Server, Technical Knowledge by Kent Willumsen Comments Off on Dell PowerEdge T20 Notes , ,

ESXi Notes

Having previously worked with Oracle VirtualBox I thought this this would be an easy transition – but no way.

ESXi feel much more like a enterprise ready product, with complexities at that level as well.

Most of the things in here will be related to the CLI interface of ESXi version 6.5.

It is work in progress so there will be errors and things I have misunderstood below as it is for my own use.

Storage

Storage is provisioned in 3 layers starting from lowest level:

  1. Physical Disks (or raid/nas/san drives)
  2. Datastores: collection of physical disks making a logical disk
  3. Virtual Disks: VM storage placed in datastores as vmdk files

Simplified diagram:

image

Physical Disks (devices)

Physical disks are located in directory:

/vmfs/devices/disks

Physical hard disks maps to files in /vmfs/devices/disks like this:

t10.SanDisk00Ultra_Fit000000000000004C531001491102119083

Partitions maps to the above filename but ending with “:” and number like this:

t10.SanDisk00Ultra_Fit000000000000004C531001491102119083:1

Listing disks only:

ls -lh /vmfs/devices/disks | grep -v “:[0-9]$” | grep -v “vml\.” | grep -v “total”

Datastores

Datastores are located in directory:

/vmfs/volumes

Datastores are having names like this:

587949b5-46baef34-fb63-1866da2041ef

When a datastore is created and names then it gets a link from the name to the file above like:

SSD -> 587949b5-46baef34-fb63-1866da2041ef

Within the datastore you will find the virtual disks.

Virtual Disks

Virtual disks are located in directory:

/vmfs/volumes/{datastorename}/{vmname}

Together with other VM related files.

Main virtual disk names:

  • {diskname}.vmdk: disk configuration file
  • {diskname}-flat.vmdk: thin provisioned file (full file)
  • {diskname}-rdmp.vmdk: raw provisioned file (link only)

Raw Device Mapping (RDM)

A physical disk can be mapped directly to a VM by creating a link.

Use this command:

vmkfstools -z /vmfs/devices/disks/{physical disk} /vmfs/volumes/SSD/{vmname}/{vmdkname}.vmdk

For example:

vmkfstools -z /vmfs/devices/disks/t10.SanDisk00Ultra_Fit000000000000004C531001491102119083 /vmfs/volumes/SSD/windows/hdd1.vmdk

Creates the following files:

  • {diskname}.vmdk: disk configuration file
  • {diskname}-rdmp.vmdk: raw provisioned file (link only)

Equally to remove the links to a VM do the following:

rm /vmfs/volumes/SSD/windows/hdd1.vmdk
rm /vmfs/volumes/SSD/windows/hdd1-rdmp.vmdk

Virtual Disk Backup

It is easy to do a “cold” backup when the VM is shut down.

Using the command to clone the original disk:

vmkfstools –i /vmfs/volumes/{datastorename}/{vmname}/{vmdkname}.vmdk {backupdest}/{vmdkname}.vmdk -d thin

This command should only be used with thin provisioned disks.

VM Upgrade

When importing a VM from other sources you will find the VM version is older.

I had this problem where I got a warning about the Linux version was not correct.

I could only pick Debian 6 where I was using Debian 8.

This is caused by the VM version is older.

To upgrade the VM version run the following command:

vim-cmd vmsvc/upgrade {vmid} vmx-13

The {vmid} can be obtained by running this command:

vim-cmd vmsvc/getallvms

Extending Virtual Disk

Well you don’t really extend but add another one instead.

Click on the VM details:

image

Click Add hard disk – New hard disk:

image

Adjust disk size:

image

Reboot the VM

List the disk:

# lsblk

NAME               MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                  8:0    0  100G  0 disk
|-sda1               8:1    0  428M  0 part /boot
|-sda2               8:2    0 18.2G  0 part
| |-turnkey-root   254:0    0   99G  0 lvm  /
| `-turnkey-swap_1 254:1    0  512M  0 lvm  [SWAP]
`-sda3               8:3    0 81.4G  0 part
`-turnkey-root   254:0    0   99G  0 lvm  /
sdb                  8:16   0  100G  0 disk

So new hard disk is: sdb

Initialise the physical volume:

# pvcreate /dev/sdb
Physical volume “/dev/sdb” successfully created

Extend volume group in LVM:

# vgextend turnkey /dev/sdb
Volume group “turnkey” successfully extended

Extend size of logical volume – first try failed as slightly less than 100GB available so just use extents option instead:

# lvextend -L+100G /dev/turnkey/root
Insufficient free space: 25600 extents needed, but only 25599 available
# lvextend -l 25599 /dev/turnkey/root
Size of logical volume turnkey/root changed from 99.02 GiB (25348 extents) to 100.00 GiB (25599 extents).
Logical volume root successfully resized

Resize the file system:

# resize2fs /dev/turnkey/root
resize2fs 1.42.12 (29-Aug-2014)
Filesystem at /dev/turnkey/root is mounted on /; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 7
The filesystem on /dev/turnkey/root is now 26213376 (4k) blocks long.

Another way to resize, which I used on Ubuntu:

growpart /dev/sda 2
resize2fs /dev/sda2

Where the 2 is the partition 2 in /dev/sda so /dev/sda2

All done:

# lsblk
NAME               MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                  8:0    0  100G  0 disk
|-sda1               8:1    0  428M  0 part /boot
|-sda2               8:2    0 18.2G  0 part
| |-turnkey-root   254:0    0  100G  0 lvm  /
| `-turnkey-swap_1 254:1    0  512M  0 lvm  [SWAP]
`-sda3               8:3    0 81.4G  0 part
`-turnkey-root   254:0    0  100G  0 lvm  /
sdb                  8:16   0  100G  0 disk
`-turnkey-root     254:0    0  100G  0 lvm  /

Posted in: Server, Technical Knowledge by Kent Willumsen Comments Off on ESXi Notes

Lenovo X1 Carbon Tips and Notes

Very happy with this computer and only have very few issues as per below.

Sierra Wireless EM7345 4G LTE

This WLAN unit seems very unstable and tend to disconnect just after connecting.

It does not help to disable any power settings.

The solution I found was simple – run ping in the background.

Open a command prompt and enter: ping –t bbc.com

Or any other internet address that return the ping:

image 

Just keep this running also when you close the lid and it re-connects when you re-open the lid.

Posted in: General News and Tips, Laptops by Kent Willumsen Comments Off on Lenovo X1 Carbon Tips and Notes

HP ProLiant MicroServer Notes

This server is getting a bit old now but still works well except for some minor problems I will start to record in here.

Excessive disk I/O caused by audiodg.exe

The Microserver does not have a soundcard when using Remote Desktop on Windows 10 it creates a Remote Audio driver .

This remote Remote Audio driver should be disabled using the Device Manager console as per below:

image

Disable by right-clicking and chose “Disable”

Don’t ask me why the disk I/O happens – it’s one of those things…

KVM Viewer does not open

This is a Java based tools and only really works in Internet Explorer.

However IE has changed over time so the pop-up does not work any more.

This is especially true in this case for:

  • Windows 8.1 64-bit  or Windows 10 64-bit
  • Java 6 (if you have any newer then de-install these)
  • Internet Explorer 11
1. Click Launch KVM Viewer

image

2. Click Open

image

3. Pick Application

image

Click: More Options

image

Default Open With./.. windows opens

Click: Look for another app on this PC

image

However this points to x64 programs so change to: Program Files (x86)

image

Click on Java (which is now a 32-bit Java) and go down to the bin directory:

image

Pick the Java Web Start executable: javaws.exe

And the KVM should start just fine…

Posted in: Technical Knowledge by Kent Willumsen Comments Off on HP ProLiant MicroServer Notes