Issue/Symptom/Question

Interfaces Business Processes

Applies To

Reference Manual

Interfaces

Resolution/Fix/Answer

Configure PENTA for Interface Processing

1. Set Default System Values

A. Define a default host and default file directory.

System Management > System Rules > System Configuration

1. Click the toolbar’s Enter Query button.

2. In the Interface File Directory field, enter the directory where interface files will go when they are ready for processing.
   When you make a request to load an interface file in the Initiate Batch Interface Request window, PENTA first searches for this default directory. If it finds nothing, PENTA searches your user directory on the PENTA server. PENTA issues an error message if it cannot find the file in either directory.

3. In the Default Host for Background Processing field, enter the default host PENTA should use when you process interface files from outside of PENTA using the standard PENTA Stored Procedures.

4. Close the window to save your changes.

2. Set Up Cross References for Labor Interface files

Note: If you will not use the Labor Interface, skip this step and proceed to the next major step.

PENTA uses the cross-reference values to make substitutions to interface data your users load. When loading external application data through the interface, the interface file must contain valid PENTA values—the same as those you would use if you were entering the data manually through a PENTA data entry window. If you define cross-reference values, PENTA can substitute these defined values in place of invalid interface data. This allows PENTA to correct and accept a record that it would otherwise reject.

A. Enter values you want to cross reference.

Interfaces > Setup > Interface Field Cross Reference

1. In the PENTA Field Name field, do one of the following:

• Enter the desired PENTA field name. Possible values are:

• DEFAULT ACCOUNT

• HOUR CLASS

• Query all existing cross-references, then place focus on the desired field name.

1. In the Interface Field Value field, do one of the following:

• Enter valid value(s) PENTA will use when you link data.

• Enter DEFAULT to indicate you are setting up a default value.

1. Use the Description field to describe the Interface Field Value.

2. Use the PENTA Field Value to enter the value PENTA should use as a substitute if it encounters a record that belongs to the Interface Field Value.
   This directs PENTA to correct and accept a record with an invalid value (instead of rejecting it). When you make an entry here, PENTA converts alphabetic entries to uppercase.

3. Close the window to commit your changes.

3. Assign IDs to Request Types as Needed

PENTA comes prepopulated with all necessary Request Types. Only perform this step if you want to set up your own Request Type codes, or want multiple codes for the same Request Type; otherwise, skip this step and proceed to the next step.

Note: For the Labor Interface, you can also set up cross-references between values in the interface files and PENTA values.

A. Review existing Request Types and add new ones as needed.

Interfaces > Setup > Interface Request Type Setup

1. Assign Request Types to Interface Types.
   To organize your requests and identify work unit responsibilities, you may want to set up multiple Request Types for the same type of interface file. This enables you to query and monitor a specific Request Type in the Initiate Batch Interface Request or Interface Query windows and view the processing status of the interface files for just that work unit. For example:

1. Close the window to commit your changes.

4. Set Interface-Related System Option Values

A. Review and (as needed) set System Option values.

Note: You can only change a System Option if you have authorization to use the Options window. Before you change a System Option, make sure it is appropriate for your company, because changing a System Option affects the operation of PENTA. If you have any questions or need to discuss the ramifications of changing a System Option, contact your Penta Technologies account manager.

System Management > Technical Support > Options

1. Query the following interface-related System Options to indicate how PENTA will process and present data:

• System Option 107 (“Prevent Inter-BS Entries”) – Determines whether PENTA allows one or two balance sheet OUs when loading General Ledger and Purchase Order interface files.

• N allows two balance sheet OUs.

• Y only allows one balance sheet OU.

• System Option 125 (“Mark Batch for Distribution”) – Determines whether PENTA distributes timecards or inventory transfer batches when users load them.

• N will not distribute the batch when users load it. Use N if you process timecards by work date, so you can process timecards by a date other than the distribution date. An N value lets users review batches before distribution.

• Y will distribute the batch when users load it. Use Y if you process timecards by distribution date. A Y value prevents users reviewing batches before distribution.
  Note: If the value is Y, you must set System Option 159 (“No Distr Unbalanced TC Batches”) to A so PENTA can mark balanced and unbalanced batches for distribution.

• System Option 128 (“Reject interface file on error”) – Determines whether PENTA accepts timecard or inventory transfer interface files if there are data errors.

• N allows PENTA to accept data in file and causes it to record error and warning messages.

• Y causes PENTA to reject any erroneous data in the file and assign it an R (rejected) status.

• System Option 150 (“Modify committed cost to equal actual cost”) – Determines whether PENTA should update a purchase order’s committed amount to equal the actual amount booked and if so, what price history method to use when creating the price history rows.

• 1 tells PENTA to not update the committed amount, and that it has no correlation to actual invoice amounts or outstanding balances.

• 2 tells PENTA to update the committed amount by using the lump sum method of price history row entry, regardless of how a user created the purchase order line. If you are using unit pricing, this setting forces PENTA, upon close or over-invoice of the purchase order line, to change it to a lump sum line.
  Penta Technologies does not recommend this setting if the committed values and invoice value should correlate.

• 3 tells PENTA to update the committed amount by using the current method of Price History row entry for the purchase order line.

• System Option 152 (“Tracking Sales & Use Tax”) – Determines whether to use PENTA’s sales and use tax functions.

• N tells PENTA to not calculate or track sales and use tax; you manually perform tax lines and payments.

• Y tells PENTA to tracks sales and use tax in the Sales Order and Accounts Payable windows.
  PENTA creates tax lines on the purchase order for any data where the purchase order line type is not a manual tax line or exempt.
  If the Job, Cost Type, and Costcode or the Job does not have the appropriate tax information such as taxable without a tax jurisdiction list, PENTA rejects the purchase order data (Detail Record). If PENTA rejects purchase order data, it also rejects the master information (Header Record) for the purchase order.

Important: Only Penta Technologies can change this System Option. Contact your Account Manager to use this System Option.

• System Option 159 (“No Distr Unbalanced TC batch”) – Determines whether PENTA distributes timecard batches that do not balance with the batch total.

• A enables balanced and unbalanced timecard batches to be marked for distribution.

• B requires timecard batches to be in balance before they can be marked for distribution. This value causes PENTA to verify all aspects of the batch are balanced (regular hours, premium 1 and 2 hours, and so on) before users can mark it for distribution.

• System Option 167 (“Use Sub Acct If Dist Invalid”) – Determines whether PENTA uses the default account set up for timecard entries if the original account is invalid.

• N tells PENTA if the interface file’s cost distribution is invalid, do not use a default account and mark the record with the invalid account as having an error.

• Y tells PENTA that if the timecard’s cost distribution in the interface file is invalid, the timecard interface uses the Interface Field Cross Reference window’s default account.

1. Close the window to commit your changes.

Correct Interface Errors

Use this Business Process as a guide for correcting errors that caused a record rejection.

Your record may have been rejected because:

• There was non-numeric data in a field/column that required a numeric value.

• There was an invalid value for a pre-defined, required data element.

• There were formats attributed to items that were closed or secured (e.g., applying hours to a closed job).

Process

1. Correct the Data

1. If PENTA rejected data in a record, do one of the following to correct the data or fix the file:

• Correct data in the external application, then extract the data and re-submit the interface file.
  This is the preferred method of correcting data because it maintains the integrity of the data stored in the external application.
  Note: You must use a unique filename when resubmitting corrected data.

• Correct data in the interface file using an editor, then resubmit the corrected interface file.
  This approach leaves the data in the external application uncorrected, which means that the same problem may occur again.
  Note: You must use a unique filename when resubmitting corrected data.

• Correct data in the PENTA windows, completely bypassing the interface process.
  This approach leaves the data in the external application uncorrected, which means the same problem may occur again.

Notice About Computer System Errors

Computer system errors such as operating system, file system, and database errors are beyond the control of the interface processing programs. These errors are fatal to the extent that PENTA stops interface file processing if it encounters such an error.

Load Data Using the Pipe Delimited Method

This Business Process is intended for PENTA users.

After completing this Business Process, you will have loaded records into PENTA using the interface process.

Note: System Options 125 and 128 affect what happens if a record does not pass PENTA’s data validations. PENTA will either reject the entire batch, or just records that fail validations.

Recommendations for Processing Interface Files

PENTA’s response time slows when processing large interface files or multiple interface files at one time. We recommend that you process interface files during low activity times.

Process

1. Verify Pipe Delimited Files are in Correct Order

A. Verify the chronological order of records.

Requests should be in chronological order by creation date, so that a batch (which depends on previous invoices being distributed) processes only after PENTA processed and committed previous or historical batches.

Interfaces > Processing > Initiate Batch Interface Request

1. Query records with an Active status and ensure they’re in the correct order.
   If records are not in the correct order, do one of the following:

• Assign a Hold status to the file(s) that should not process first, then take them off hold and process them

• Using Linux, rename the files (alphabetically or numerically) in the order in which PENTA should process them.

2. Process Interface Files

A. Identify the type of files you want to process.

Interfaces > Processing > Initiate Batch Interface Request

For each file you want to process, perform the following:

1. If one of your processing requests is incomplete when you open this window, PENTA asks whether you want to continue processing that request.

• Enter Y to stop processing the request (interface file) and to use this window.
  This loads none of the data in the request into PENTA. When you exit this window, PENTA processes the stopped request and any additional interface files you request.

• Enter N to exit the window and process the file.

2. In the Type column, enter the Id for the appropriate Interface Type.

3. Use the Request Id column to enter the name of the file that contains the data you want to load and process.
   If PENTA cannot find the interface file in the interface file directory (defined in the System Configuration window) or your local user directory on the PENTA server, PENTA cannot load the file.

4. In the Status column, enter A to make the file immediately available for processing.

5. Repeat the previous steps for additional interface files you want to process.

B. Process the interface file(s).

Interfaces > Processing > Initiate Batch Interface Request

1. Click the Initiate Interface Requests with a Status of A button.
   PENTA does the following:

• Processes any files with an A (Active) Status.

• Performs a series of steps and tracks the status of each step. If a request does not process successfully, PENTA can restart the processing at a point immediately following the last step it completed.

• Creates a batch of data entries, just as if you manually entered the data in a PENTA window.

Load Data Using the Table Driven Method

After completing this Business Process, you will have loaded records into PENTA using the Table Driven method.

Important: PENTA’s response time slows when processing large interface batches or multiple interface batches at one time. We recommend processing interface batches during low activity times.

Note: System Options 125 and 128 affect what happens if a record does not pass PENTA’s data validations. PENTA will either reject the entire batch, or just records that fail validations.

Prerequisites

• You have access to a scripting tool that can connect to a database.

• You should have some knowledge of SQL and be able to create rows in a table.

Recommendations for Processing Table Driven Interfaces

You should enter requests in chronological order by creation date, so that a batch (which depends on previous invoices being distributed) processes only after PENTA processed and committed previous or historical batches.

Process

1. Load Data by Pulling It from Third-Party Software (possibly by writing code)

An example of where you would pull this from is an hour tracking system for employee data, such as Kronos®.

A. Load Interface Records into Intermediate Work Tables

1. Write or use an application that inserts rows into tables and executes SQL.
   Each batch needs a unique interface request ID (INTR_RQST_ID), preferably something that ties to and makes sense in your other system.

2. Load your records into PENTA’s intermediate work tables using an insert statement.
   Notes:

• The interface process collects data, starts running, and sets an execution schedule.

• Each record must be associated with a valid INTR_RQST_ID, which you use to batch interface records for processing.

Header record example:

insert into intr_po_hdr(intr_rqst_id, record_count, data_check_verifier, user_id)
select '&intr_rqst_id' intr_rqst_id,
0 record_count,
0 data_check_verifier,
'&user_id' user_id
from dual

Master record example:

insert into intr_po_mstr(intr_rqst_id,ou_id,po_num,job_id,activity_cd,
po_stat_cd,d_rqst_date,co_id,ven_id,descr,change_type_cd,po_type_cd,
pay_term_num,currency_id,po_chk_amt,tax_exempt_reason_cd,tax_permit_id,
user_id,contact_num,d_release_date,fab_delivery_days,ship_method_cd,
ship_method_descr,freight_term_cd,po_order_type_cd,allow_ovrn_amt,
allow_ovrn_pct,requester_emp_id,purch_agent_emp_id,pmgr_emp_id,
ctrl_user_id)
select '&intr_rqst_id' intr_rqst_id,
100 ou_id,
102 po_num,
null job_id,
'A' activity_cd,
'A' po_stat_cd,
to_date('2011/11/11', 'YYYY/MM/DD') d_rqst_date,
null co_id,
100 ven_id,
'purchase order description' descr,
'R' change_type_cd,
'STD' po_type_cd,
null pay_term_num,
'USD' currency_id,
null po_chk_amt,
null tax_exempt_reason_cd,
null tax_permit_id,
'&user_id' user_id,
null contact_num,
null d_release_date,
null fab_delivery_days,
null ship_method_cd,
null ship_method_descr,
null freight_term_cd,
null po_order_type_cd,
null allow_ovrn_amt,
null allow_ovrn_pct,
null requester_emp_id,
null purch_agent_emp_id,
null pmgr_emp_id,
'&user_id' ctrl_user_id
from dual

Detail record example:

insert into intr_po_dtl(intr_rqst_id,ou_id,po_num,line_num,job_id,activity_cd,
po_stat_cd,item_num,long_descr,distr_type_cd,allow_ovrn_amt,allow_ovrn_pct,
cc,cost_type_cd,lump_sum_amt,up,units,po_co_id,po_co_descr,par_line_num,
po_line_type_cd,um,d_needed_date,d_promised_date,tax_exempt_reason_cd,
tax_permit_id,user_id,allow_ovrn_up_pct,allow_ovrn_up_amt,ret_pct,
pricing_factor_cd,prod_id,dr_ou_id,dr_acct_num,wo_id,wo_ou_id,
wo_cost_type_cd,wo_task_id,wo_maint_contract_id,ctrl_user_id)
select '&intr_rqst_id' intr_rqst_id,
100 ou_id,
102 po_num,
1 line_num,
100 job_id,
'A' activity_cd,
'A' po_stat_cd,
null item_num,
null long_descr,
'J' distr_type_cd,
null allow_ovrn_amt,
null allow_ovrn_pct,
307 cc,
'M' cost_type_cd,
null lump_sum_amt,
0.25 up,
5.0 units,
null po_co_id,
null po_co_descr,
null par_line_num,
'R' po_line_type_cd,
null um,
null d_needed_date,
null d_promised_date,
null tax_exempt_reason_cd,
null tax_permit_id,
'&user_id' user_id,
null allow_ovrn_up_pct,
null allow_ovrn_up_amt,
null ret_pct,
null pricing_factor_cd,
null prod_id,
null dr_ou_id,
null dr_acct_num,
null wo_id,
null wo_ou_id,
null wo_cost_type_cd,
null wo_task_id,
null wo_maint_contract_id,
'&user_id' ctrl_user_id
from dual

2. Process Interface Batches Using Stored Procedures

Perform this after you write the code that pulls the source data to populate the INTR tables.

A. Log in to the database.

1. Log in to the application using the pentaLogin stored procedure.
   Pk_PentaLogin stored procedure components:

pk_pentaLogin.f_login('USER', 'PW')
where:
USER – Valid PENTA user id, and
PW – Valid password for the PENTA user id.

B. Initiate processing of the interface batch.

1. Initiate the interface process using the Pk_Interface store procedure.
   Pk_Interface stored procedure components:

pk_interface.f_process('USER', 'INTR_RQST_ID', 'TYPE', 'R’)
where:

• USER = The valid PENTA User Id associated with the interface process. This must equal the User Id specified in the pk_pentaLogin stored procedure.

• INTR_RQST_ID = The Interface Request ID used when populating the interface work tables.

• TYPE = The Request Type code for the interface process you want to perform. You can determine this value in the Interface Request Type Setup window.

• R = Restart code (must be a Y or N value). This specifies whether the request process should restart a previously aborted process. The value should equal Y if the interface process experienced interruption because of a hardware or communication problem.

The interface process collects data, starts running, and sets an execution schedule.

3. Confirm that PENTA Successfully Initiated the Interface Request

A. Verify the interface was scheduled.

Example:

if processRetVal <> 0 then
dbms_output.put_line( pk_interface.f_lastErrorMessage );
raise_application_error( -20001, pk_interface.f_lastErrorMessage );
end if;

B. Wait for completion or a timeout.

Example:

loop
select intr_rqst_base.intr_rqst_stat_cd, pcli_job.status_cd
into intrRqstStat, jobstatCd
from intr_rqst_base
left outer join pcli_job
on pcli_job.process_req_num = intr_rqst_base.process_req_num
where intr_rqst_base.intr_rqst_id = intrRqstId;
--
exit when sysdate > stoptime or
intrRqstStat in ('C', 'E', 'R', 'W') or
jobstatCd in ('A', 'C');
end loop;

Status information for example:

case intrRqstStat
when 'A' then
dbms_output.put_line( 'Interface Was not yet processed' );
when 'C' then
dbms_output.put_line( 'Interface Completed Successfully' );
when 'E' then
dbms_output.put_line( 'Interface Completed With Errors' );
when 'H' then
dbms_output.put_line( 'Interface Is On Hold' );
when 'R' then
dbms_output.put_line( 'Interface Was Rejected' );
when 'W' then
dbms_output.put_line( 'Interface Completed With Warnings' );
end case;
case
when jobstatCd = 'A' then
dbms_output.put_line( 'Underlying Process Aborted' );
when jobstatCd = 'C' then
dbms_output.put_line( 'Underlying Process Completed' );
when jobstatCd = 'P' then
dbms_output.put_line( 'Underlying Process Is Pending' );
when jobstatCd = 'E' then
dbms_output.put_line( 'Underlying Process Is Executing' );
else
dbms_output.put_line( 'Underlying Process Is Missing' );
end case;

C. Review messages and statuses from the interface process and background process.

1. Check the return value of the pk_interface.f_process Stored Procedure to ensure no errors occurred with the interface request.

• A value of zero indicates there were no errors and that you can check the status of the interface request.

• A value other than zero indicates an error prevented PENTA from initiating the interface request. If this occurs:

• Use the pk_interface.f_lastErrorMessage Stored Procedure to identify the error that prevented the interface request initiation.

• Take the appropriate action to resolve the error. Once resolved, re-submit the record with a new INTR_RQST_ID.

2. Check the status of the request by selecting the process_req_num value associated with your INTR_RQST_ID from the INTR_RQST_BASE table. Select the record from the PCLI_JOB table associated with the retrieved process_req_num value.

• If the status is A, the process aborted with an error and you should call your PENTA account representative for further assistance.

• If the status is a value other than A, PENTA did not process the request and you should wait for the process to complete.

Reprocess “On Hold” or “Rejected” Files

If you have a file with a status preventing it from processing (such as H (Hold) or R (Rejected)), use this process to change the status and process the file.

After completing this Business Process, you will have removed a hold status and processed the request.

Prerequisites

While processing a file, an error occurred which resulted in a status of Hold or Rejected; your Interface Administrator then investigated and resolved the issue (i.e., he/she corrected interface error(s)).

1. Change a File’s Status and Process It

A. Mark the file as Active and process the file.

Interfaces > Processing > Initiate Batch Interface Request

1. Query records with an H (hold) ) or R (Rejected) status.
   Note: See information on System Option 128 for information about how PENTA handles Rejected records.

2. In the Status column of the desired file, enter A (active) to make the file available for processing.

3. Repeat the previous steps for additional data files you want to process.

4. Click the Initiate interface requests with a status of A button to process data files with an A status.
   PENTA processes all files with an A Status.

Review Interface Files

1. Review the Interface file

Use one of the these options for reviewing interface files:

• The Interface Query window – Use this window to review processed interface requests. The Detail tab shows the record’s content and a message for errors or warnings that occurred.

• PENTA windows where the data was sent – You can use the windows that received the data to query and review the entries PENTA created. The description of each interface will note which window you can use to query and review entries.

• Reports –Every interface process automatically generates these reports and you can view/print them in the Report Monitor, Print, and View window:

• Interface Exception Report – Shows a detail listing of the error and exception messages produced during interface file processing and:

• Lists each interface file processed by the interface program.

• Displays each record with a noted exception.

• Shows all exception messages related to the offending record.

• Invalid Distributions Report – Shows a detail listing of the invalid distributions, but does not indicate whether a PENTA user corrected a distribution.