ESFO 9.1: No Date Format Validations For Customer Field Definition USER_DT1 (Internal Date) Causing Abend On CA_BI_INTFC.AI-16500.Step01 (Doc ID 1543390.1)

Last updated on MARCH 16, 2016

Applies to:

PeopleSoft Enterprise FIN Staffing Front Office - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

ISSUE:

The delivered Customer Fields Definition ID STAFF contains Field USER_DT1 (Internal Date) that allows users to enter date values at both the Order and Assignment levels. However, because a date is something that could have an endless combination of values, the USER_DT1 Field is the only row not having the Validate flag selected at the Customer Fields Definition ID level. This poses a problem, because at both the Order level (Resource Request tab - FO_ORDER_REQ page), and at the Assignment level (Billing tab - FO_ASGN_BILLING Page), the value entered in the Description column for Field Internal Date is never validated by the system against a specific Date Format.

Later on, when Project Costing Transactions related to the Assignment's Project Activity are processed and sent over to the FSCM Billing module via the Contracts To Billing Interface Application Engine Program (CA_BI_INTFC), this program abends in Error Message, due to the incorrect format on this date-related USER_DT1 Field.

REPLICATION STEPS:


   - Log into the FSCM Online Application with User ID VP1
   - Navigate to: Set Up Financials/Supply Chain > Install > Installation Options > Contracts
   - Make sure that the flag named 'Pay/Bill Management Installed' is properly selected in the Other Installed Options section
   - Navigate to: Set Up Financials/Supply Chain > Product Related > Staffing > Orders > Order Event Groups
   - Open the existing Order Event Group FOLLOW_UP, and review all its settings, making sure it is NOT flagged as Default
   - Navigate to: Set Up Financials/Supply Chain > Product Related > Staffing > Customers > Customer Fields Definition
   - Open the Definition ID STAFF
   - See that there are 3 rows delivered:  (Customer Field Name - System Field Name - Valid Value)
        - Credit Grade - USER2 - N
        - Internal date - USER_DT1
        - Priority - USER1 - N
   - Navigate to: Customers > Customer Information > General Information
   - Open existing Customer ID 0000050035 under Set ID SHARE
   - In the General Info tab, scroll down to the bottom of the screen, and in the 'General Info Links' drop down list, select the value of '0200 - Customer Fields'
   - In the Customer Fields section, select the Definition ID value of STAFF from the look up view
   - Navigate to: Staffing > Orders and Assignments > Add/Update Orders
   - Add a new value with Branch ID CA001, Service Order ID NEXT, and User Order Type STFTM
   - In the Order tab (FO_ORDER_HDR page), enter the below listed Field Values in the order described:
        - Description = GCS Order TEST
        - Start Date = January 1st 2013
        - Estimated End Date = December 31st 2013
        - Order Status = Unfilled
        - Customer ID = 0000050035   (Override the previous default value…)
        - Order Contact = 46
   - Now, go to the Resource Request tab (FO_ORDER_REQ page), enter the below listed values:
        - Job Code = 120000
        - Internal Date = 01-01-2013
   - Save the changes
   - ISSUE #1: At this stage, the system does not execute any validation function to make sure that the value entered in Internal Date is following a specific Date Format (such as YYYY-MM-DD)
   - Go to the PeopleMatch tab (FO_ORDER_MATCH page), in the Selected Candidates section, set the Type to EMPLOYEE, and select Employee ID KUX136 from the available values in the look up feature
   - Once done, click on the Express Assignment icon available on the Employee line just created
   - In the new Assignment Creation component, go to the Assignment Header tab, and in the Rates section, perform the following listed tasks:
        - First, select the ‘Pay Rate’ radio-button in the Applies To box
        - Secondly, enter the value of 20 in the Field Default Rate
        - Third, enter the value of 50 in the Field Target Margin
        - Fourth, click on the ‘Apply Rate’ available button
        - Fifth, click on the ‘Apply Margin’ available button
   - Once all tasks have been completed, perform a save of all the changes
   - At this stage, the system creates the new Assignment ID
   - Go to the Assignment’s Billing tab (FO_ASGN_BILLING Page), the value contained in the Column Description for the row of Internal Date is editable, and contains a value of 01-01-2013
   - ISSUE #2: The problem is once again, that there is no validation on the Date Format entered at this stage, allowing the User to define any value. The system should expect a specific Date Format such as (YYYY-MM-DD) for instance.
   - Go to the Assignment Header tab (FO_ASGN_HDR Page), and in the Rates section, click on the Related available tab: Notice the Time Reporting Code KUREG from the Regular Rate Purpose
   - Navigate to: Project Costing > Transaction Definitions > Add Transactions
   - Add the proper PC Business Unit, Project ID, and Activity ID from the Assignment's Cross Reference tab, and click on Search button
   - Create a new Project Transaction Line with the below values:
        - Analysis Type = TLB
        - Source Type = LABOR
        - Category = LABRH
        - Quantity = 1
        - Unit Of Measure = MHR
        - Source Amount = 25
        - Source Currency = USD
   - At this stage, click on the 'Transaction Detail' icon
   - In the Transaction Detail page, enter a value of 'KUX136' in the Description Field, and then click on the available link named ‘Show All Transaction Details’
   - In the new page, scroll down, and expand the section called 'Time and Labor', and follow to enter the below new values:
        - Employee ID = KUX136
        - Job Code = 120000
        - Time Reporting Code = KUREG (Note that it does not display in LookUp)
   - Click on the available link named 'Return to Transaction Detail'
   - Click on the Process Transactions button
   - Navigate to: Customer Contracts > Schedule and Process Billing > Process As Incurred Billing
   - Add a new Run Control ID named GCS, and enter the below criteria values:
        - Business Unit = US004
        - Contract = CON000000000072
        - BU/Proj/Act Option = Business Unit
        - PC Business Unit = US004
   - Run the Contracts To Billing Interface Application Engine Program (CA_BI_INTFC)
   - ISSUE #3: At this stage, the Process abends in Error Message

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

   " SQL error. Stmt #: 1620  Error Position: 84  Return: 957 - ORA-00957: duplicate column name
     SQL Error: [Microsoft][SQL Server Native Client 10.0][SQL Server] The column name 'USER2' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated
     Failed SQL stmt:UPDATE PS_CA_BI_DRIVER4 SET TO_DT = TO_DATE(ACCOUNTING_DT,'YYYY-MM-DD')  ,USER2='N',USER2='N',USER1='N'   WHERE BUSINESS_UNIT_PC = 'US004' AND PROJECT_ID = '000000000000199' AND ACTIVITY_ID = '000000000000001' AND PROCESS_INSTANCE = 15078
     Process 15078 ABENDED at Step CA_BI_INTFC.AI-16500.Step01 (SQL) -- RC = 957 (108,524) "


BUSINESS IMPACT:

Because the Staffing Front Office module does not have the proper validations put in place, the incorrect format of certain date values get filtered down the line, which causes the Contracts To Billing Interface Application Engine Program (CA_BI_INTFC) to abend, and prevents the transactions to be further processed into Billing, when invoices need to be later sent to customers. This is affecting the company's revenue stream.

EXPECTED BEHAVIOR:

The Staffing Front Office module should have put in place system validation functions at both the Order level (Resource Request tab - FO_ORDER_REQ page), and at the Assignment level (Billing tab - FO_ASGN_BILLING Page), so the values entered in the Internal Date Field (USER_DT1) follow the Date Format of YYYY-MM-DD, as per what PeopleBooks establishes when the IsDate formula is being used in CA_BI_INTFC.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms