Summary

This SQL statement explains how to update the missing Description on the historical “Transportation” Entry Codes created from the Travel Expense document.

Keywords

Travel Expense, Transportation

Category

SQL Statement

Description

This SQL statement explains how to update the missing Description on the historical “Transportation” Entry Codes created from the Travel Expense document.

The Description might be empty because of the bug APM-6589. Fixed starting from v. 2022.20.8 Patch 7, or because of the previously incomplete setup.

Selection

All the historical “Transportation” Entry Codes were created from the Travel Expense document where the Description field is empty.

SQL specifics

The SQL statement is rather specific. While using it you need to replace the fields according to your setup for the Travel Expense transfer to Payroll under APM > Organization Properties. See the field description below.

The view mainly selects records from APP > Transportation and APM > Payroll history that match each other based on the following columns: PIN, Document ID, Payroll Period, Amount, and Vessel.

It is possible that you would have persons with several same records that have all of those values matching, then the script would have duplicates. There is no way to avoid it since we don’t have a unique ID for separate Transportation records. But is ok. Usually, those records have the same description (e.g. buss fees are the same). Just look through the select results to make sure it is ok.

SQL statement

To check and verify the data

Detailed

select 
	PYH.PERIODEUSED, 
	PYH.PIN, 
	PYH.TEXT DOC_ID,
	DOC.VESSEL, 
	PYH.ACCOUNTDIM2 ENTRY_VSL,
	PYH.ACCOUNTDIM10, 
	PYH.entryfield1, 
	PYH.entryfield6, 
	PYH.entryfield7,
	TRANS.CODE,
	DESCR.DESCRIPTION,
	PYH.ENTRYFIELD1,
	TRANS.T1_COST,
	DESCR.period_SEQUENCENO
from PW001PYH PYH
JOIN WEB_CP_TRAVEL_SPECIFICATION TRANS on convert (varchar,TRANS.DOCUMENT_ID)= PYH.TEXT 
										  AND replace(PYH.ENTRYFIELD1, ',', '.') = replace(convert (varchar(20), convert (decimal(19,2) , T1_COST)), ',', '.')
JOIN WEB_CP_TRANSPORTATION_TYPE DESCR on DESCR.CODE = TRANS.CODE and DESCR.PERIOD_SEQUENCENO=10000003
JOIN WEB_CP_TRAVEL_BILLS DOC on DOC.DOCUMENT_ID=TRANS.DOCUMENT_ID 
where ENTRYCODE=461 and (ACCOUNTDIM10 = '' OR ACCOUNTDIM10 is NULL) and PYH.PERIODEUSED >= 202201 and DOC.VESSEL=PYH.ACCOUNTDIM2 and PYH.SOURCE = 'I'
--and PYH.PIN =1133
order by 1 desc

Short

select 
PYH.ACCOUNTDIM10,
DESCR.DESCRIPTION
from PW001PYH PYH
JOIN WEB_CP_TRAVEL_SPECIFICATION TRANS on convert (varchar,TRANS.DOCUMENT_ID)= PYH.TEXT 
										AND replace(PYH.ENTRYFIELD1, ',', '.') = replace(convert (varchar(20), convert (decimal(19,2) , T1_COST)), ',', '.')
JOIN WEB_CP_TRANSPORTATION_TYPE DESCR on DESCR.CODE = TRANS.CODE and DESCR.PERIOD_SEQUENCENO=10000003
JOIN WEB_CP_TRAVEL_BILLS DOC on DOC.DOCUMENT_ID=TRANS.DOCUMENT_ID 
where PYH.ENTRYCODE=461 and (PYH.ACCOUNTDIM10 = '' OR PYH.ACCOUNTDIM10 is NULL) and PYH.PERIODEUSED >= 202201 and DOC.VESSEL=PYH.ACCOUNTDIM2 and PYH.SOURCE = 'I'
--and PYH.PIN =1133

To update the Description field value

update PW001PYH 
set ACCOUNTDIM10 = DESCR.DESCRIPTION 
from PW001PYH PYH
JOIN WEB_CP_TRAVEL_SPECIFICATION TRANS on convert (varchar,TRANS.DOCUMENT_ID)= PYH.TEXT 
										AND replace(PYH.ENTRYFIELD1, ',', '.') = replace(convert (varchar(20), convert (decimal(19,2) , T1_COST)), ',', '.')
JOIN WEB_CP_TRANSPORTATION_TYPE DESCR on DESCR.CODE = TRANS.CODE and DESCR.PERIOD_SEQUENCENO=10000003
JOIN WEB_CP_TRAVEL_BILLS DOC on DOC.DOCUMENT_ID=TRANS.DOCUMENT_ID 
where PYH.ENTRYCODE=461 and (PYH.ACCOUNTDIM10 = '' OR PYH.ACCOUNTDIM10 is NULL) and PYH.PERIODEUSED >= 202201 and DOC.VESSEL=PYH.ACCOUNTDIM2 and PYH.SOURCE = 'I'

Field Specification

Fields	Description
`PYH.PERIODEUSED`	Payroll period of the selected Entry Code.
`PYH.PIN`	PIN to which the Entry Code belongs.
`DOC.VESSEL`	Vessel from the Travel Expense document to which the Transaportation record is linked to.
`PYH.ACCOUNTDIM2`	Vessel on the selected Entry Code.
`PYH.ACCOUNTDIM10`	Description of the Entry Code, taken from the Travel Expense > Transaportation record.
`PYH.TEXT`	Document ID on the Entry Code, taken from the Travel Expense > Transaportation record.
`PYH.entryfield6`	Date From on the Entry Code, taken from the Travel Expense > Transaportation record.
`PYH.entryfield7`	Date To on the Entry Code, taken from the Travel Expense > Transaportation record.
`TRANS.CODE`	Code of the Travel Expense > Transaportation record.
`DESCR.DESCRIPTION`	Description on the Travel Expense > Transaportation record.
`PYH.entryfield1`	Amount on the Entry Code, taken from the Travel Expense > Transaportation record.
`TRANS.T1_COST`	Amount on the Travel Expense > Transaportation record.
`DESCR.period_SEQUENCENO`	Travel Expense Period used for your organization in APP.

Update missing Description on Transportation Entry Code