Sample Payroll Transformation Formula for Multiple Business Objects

In this example, the formula uses the user defined tables and personal payment method business objects. It converts the Person Number in the flat file into Assignment Number and uses the METADATALINEINFORMATION and NUMBEROFBUSINESSOBJECTS operations.

Here's the sample of the raw input file.

PPM|2018/04/04|1|ZHRX_VS_US_TPPI_LDG_ONE|955160008191423|ZHRX_VS_US_TPPI_Check|PPM1|M|10
UDT|SM_UDT_4|Range|Number|Test UDT|USA LDG

And this code snippet has the formula for this example.

/**********************************************************
FORMULA NAME: Load User Defined Table and Personal Payment Method
FORMULA TYPE: HCM Data Loader 
*************************************************************/
/* Inputs  */
INPUTS ARE OPERATION (text), LINENO (number), LINEREPEATNO (number),POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text), POSITION6 (text), POSITION7 (text), POSITION8 (text),POSITION9 (text)
DEFAULT FOR POSITION1 IS 'NO DATA'
DEFAULT FOR POSITION2 IS 'NO DATA'
DEFAULT FOR POSITION3 IS 'NO DATA'
DEFAULT FOR POSITION4 IS '2'
DEFAULT FOR POSITION5 IS '100'
DEFAULT FOR POSITION6 IS 'NO DATA'
DEFAULT FOR POSITION7 IS 'NO DATA'
DEFAULT FOR POSITION8 IS 'NO DATA'
DEFAULT FOR POSITION9 IS 'NO DATA'
DEFAULT FOR LINEREPEATNO IS 1
IF OPERATION='FILETYPE' THEN 
   OUTPUTVALUE='DELIMITED' 
ELSE IF OPERATION='DELIMITER' THEN 
   OUTPUTVALUE='|' 
ELSE IF OPERATION='READ' THEN 
   OUTPUTVALUE='NONE'
ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN
   (
   OUTPUTVALUE = '2'
   RETURN OUTPUTVALUE
   )
ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN
    (
    METADATA1[1] = 'UserDefinedTable' /*FileName*/
    METADATA1[2] = 'UserDefinedTable' /*FileDiscriminator*/
    METADATA1[3] = 'UserTableCode'
    METADATA1[4] = 'RangeOrMatch'
    METADATA1[5] = 'UserKeyUnits'
    METADATA1[6] = 'UserRowTitle'
    METADATA1[7] = 'UserTableName'
    METADATA1[8] = 'LegislativeDataGroupName'
    METADATA2[1] = 'PersonalPaymentMethod' /*FileName*/
    METADATA2[2] = 'PersonalPaymentMethod' /*FileDiscriminator*/
    METADATA2[3] = 'EffectiveStartDate'
    METADATA2[4] = 'PersonalPaymentMethodCode'
    METADATA2[5] = 'AssignmentNumber'
    METADATA2[6] = 'Amount'
    METADATA2[7] = 'ProcessingOrder'
    METADATA2[8] = 'OrganizationPaymentMethodCode'
    METADATA2[9] = 'PaymentAmountType'
    METADATA2[10] = 'LegislativeDataGroupName'
   RETURN METADATA1, METADATA2
    )
ELSE IF OPERATION='MAP' THEN 

  IF POSITION1='UDT' THEN
    ( 
    FileName = 'UserDefinedTable'
    BusinessOperation = 'MERGE'
    FileDiscriminator = 'UserDefinedTable'
    UserTableCode = POSITION2
    IF POSITION3='Range' THEN
    (
      RangeOrMatch = 'R'
    )
    IF POSITION4='Number' THEN
    (
      UserKeyUnits = 'N'
    )
    UserRowTitle = POSITION5
    UserTableName = POSITION2
    LegislativeDataGroupName = POSITION6
    RETURN BusinessOperation,FileDiscriminator,FileName,UserTableCode,RangeOrMatch,UserKeyUnits,UserRowTitle,UserTableName,LegislativeDataGroupName
    )
    IF POSITION1='PPM' THEN
    (
    FileName = 'PersonalPaymentMethod'
    BusinessOperation = 'MERGE'
    FileDiscriminator = 'PersonalPaymentMethod'
    EffectiveStartDate=POSITION2
    ProcessingOrder=POSITION3
    LegislativeDataGroupName=POSITION4
    AssignmentNumber=GET_VALUE_SET('SAMPLE_GET_ASG_NUM','|=PERSON_NUMBER='''||POSITION5||'''')
    OrganizationPaymentMethodCode=POSITION6
    PersonalPaymentMethodCode=POSITION7
    PaymentAmountType=POSITION8
    Amount=POSITION9
    RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveStartDate,PersonalPaymentMethodCode,AssignmentNumber,Amount,ProcessingOrder,OrganizationPaymentMethodCode,PaymentAmountType,LegislativeDataGroupName
    )
ELSE 
   OUTPUTVALUE='NONE'
RETURN OUTPUTVALUE
/* End Formula Text */

Note: To debug value sets, create a BI report with this query to return the required data.

SELECT pay_ff_functions.gvs ('SAMPLE_GET_ASG_NUM','|=PERSON_ID=100000012092216') value FROM dual;