Web Service Consumer for JDE BSSV Customer Manager - processCustomer

Calling JDE Business Services (BSSV), aka Web Services, can be tricky and there is little information on Oracle's or Informatica's website showing how. In fact, importing the JDE CustomerManager.wsdl into an Informatica web service consumer creates a large confusing, near impossible to understand transformation.

Here's a high level overview to create a usable JDE 9.0 Customer Manager BSSV, be aware your requirements may be different and you may have to deviate from these steps to fit your need.

Scheduled Workflow Fails But Completes when Ran Manually

Here's a solution to an obscure issue. A workflow/session completes successfully when ran manually but gives error LM_44127 Failed to prepare the task when ran from the scheduler.

If you are using Versioning, ALL objects must be checked in. Use the option Versioning > Find Checkouts to see any checked out objects. Check in all objects.

Also, IMPORTANT, if using shortcuts for sources, targets or transformations, the objects the shortcuts are copied from must also be checked in, look in the folder(s) where the shortcuts where copied from, make sure those are also checked in.

Data Integration Hub Errors

This page will be a running list of tips and workarounds for Data Integration Hub 10.

Data Integraton Hub DX_ETL DateTime Precision Issue

This issue was found in Informatica Data Integration Hub 10 on Windows Server 2012 using SQL Server 2012 as the Repository.

The PowerCenter Workflow DX_ETL can abort in mapping m_SET_CURRENT_CUTOFF giving the errors:

  • Timestamp parameters with a scale, must have a scale less than ten and a precision equal to 20 plus the scale. You specified a precision of 999 and scale of 9.
  • Fractional second precision exceeds the scale specified in the parameter binding.
Here are some steps you can take to correct this problem. Some or all of these steps may be required to correct the issue.

Formatting Telephone Numbers

I created this simple PowerCenter mapplet to format phone numbers. Informatica Developer (IDQ) may have a different or easier way to do it but I wanted something I could use in PowerCenter without the hassle of maintaining a separate object in IDQ and having to import it into PowerCenter. You can alter the mapplet to format other data like postal codes, SSNs, etc.

Download the mapplet and give it a try.

Mapplet Overview

My approach is a simple one, take the phone number, remove all special characters, then reformat the number with dashes as 999-999-9999. This approach allows me to ignore how the phone number comes into the mapplet, for example partially formatted , not formatted or formatted correctly but not how I need it.

The mapplet's input is the telephone number without a country identifier. In addition to the reformatted phone number, the mapplet also outputs:

  • a Pattern (example xxx-xxx-xxx) based on the input number (this could be used for a quick/dirty data profiling)
  • the phone number parsed into its parts, area code, central office
  • the length of the input phone number

Read further to see detail on the mapplet works.

Limit Extracts from SAP Tables Using Mapping Parametes

When extracting large tables from SAP, you can limit the amount of data selected by using Informatica parameters.  Here are two examples.

  1. Use a parameter to limit the extract to retrieve only a specified set of data. Your target uses a truncate and load scenario.
  2. Use parameters as in the example above but also use a persistent cache lookup on the target table. Append new rows to the table.
For “Example 1”, you can use an “offset number of days” to only load the last x number of day’s transactions from a table.  Most SAP tables have an ERDAT column which is the created date. ERDAT can be used in the filter. SAP extract mappings use ABAP in the background, the Informatica mapping uses the SAP table’s source qualifier “Program Flow”.  This is where you  can set a filter.
Overview of steps
  • Create the Informatica mapping as normal, using an SAP application source qualifier.
  • Add a numeric Informatica parameter for the offset day’s value.
  • Open the SAP Source Qualifier, click the Properties tab, open the Program Flow section.
  • Add a new Variable, ABAP Type, give it a name like DATEPAST and use data type DATS.
  • Insert a new ABAP Block.  For the blocks code add something similar to the following:  :DATEPAST =  SY-DATUM - $$VVTTP_Offset_Days.
    • (Variable = current date – a numeric value)
  • For the “Source Level Attributes block”, add a static filter to compare a Date data type column from the SAP table () to the ABAP variable created in the previous step. (Remember, this variable will contain a date x number of days in the past.
    • Note: The ABAP block should be listed before the “Source Level Attributes block”
  • For the remainder of the mapping you can have a lookup to your staged table to filter out any existing records, have an Insert/Update options or clear and load the table.
  • Create the Informatica ABAP mapping as normal by using the Designer option “Mapping > Generate and Install SAP R/3 Code…”

Figure 1

&bnsp

Thank you for your support
 or  PayPal


 

IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

Diary of an Informatica 9.6.1 HF1 Install

Here are some notes on an Informatica PowerCenter 9.6.1 64 bit server install.  This also includes the setup of the Informatica Cloud Secure Agent (64)

Most of the install went smooth.  However I want to list some of the problems I encountered.

VBScript to Convert Excel to CSV

Using Excel files as the source for Informatica mappings can be more trouble than it's worth. You have to setup a connection and name a range in the Excel document which can be difficult to maintain. I much prefer to read text or CSV files, they're simpler to work with and less error prone. I created a VB script for the purpose of converting Excel to CSV.