This blog post we will discuss about Practical Examples of Peoplesoft SQR Load Lookup. My goal is how we use SQR Load lookup in in SQR Files.So Let’s start to look some live examples of SQR Load Lookup.

As we know in PeopleSoft some column (e.g SEX , EMPL_STATUS  etc) values are came from PSXLATITEM –  Translate Value Items and they are Effective Dated.So in order to select them we have to write query.As s common practices i have written SQR procedure that is not helpful but also reusable. Note Dynamic SQL has used here.

!***************************************************************
Begin-Procedure Select_Data_From_XLAT($FieldNameInput, :$Where)
!***************************************************************
  if Not IsBlank($FieldNameInput) and RTrim($FieldNameInput,' ') <> ''
     Let $Where = 'FIELDNAME=''' || $FieldNameInput || ''' '
     Let $Where = $Where || 'AND EFFDT = '
     Let $Where = $Where || '(
                                SELECT
                                      MAX(PSXLATITEMTBL.EFFDT)
                                FROM
                                PSXLATITEM PSXLATITEMTBL
                                WHERE 
                                    PSXLATITEMTBL.FIELDVALUE = PSXLATITEM.FIELDVALUE
                                AND PSXLATITEMTBL.FIELDNAME = PSXLATITEM.FIELDNAME
                                AND PSXLATITEMTBL.EFFDT   <= ''' || $_current-date || '''
                             )'
     Let $Where = $Where || 'AND EFF_STATUS = ''A'''
    !#debug show $Where
  end-if
End-Procedure

So when we use SQR Load Lookup then in Where Condition we can easily make the conditions,Look at the examples.

Load Lookup should be used on Begin-Program section , but would personally recommend to use on that way

!*****************
Begin-Program
!****************
     do Init_Report
end-program

!****************************
Begin-Procedure Init_Report
!****************************
     Do Stdapi-Init
     ! Call all the Load Lookup here
     Do Load_Country_Full_Name_Array 
End-Procedure

!*************************************************
Begin-Procedure Load_Country_Full_Name_Array
!*************************************************
  load-lookup name=country_full_name
              Table = 'PS_COUNTRY_TBL'
              Key = 'COUNTRY' 
              Return_Value = 'DESCR' 
              Where = 1=1

End-Procedure

Load SEX full name and Get Value

 !Call and Build the condition
 Do Select_Data_From_XLAT('SEX',$build_where_condition)
 load-lookup name=sex_full_name
              Table = 'PSXLATITEM'
              Key = 'FIELDVALUE' 
              Return_Value = 'XLATLONGNAME' 
              Where = $build_where_condition

lookup sex_full_name &B.Sex $XLATLONGNAME
If Not IsNull ($XLATLONGNAME)
 let $Descr = Rtrim($XLATLONGNAME,' ')
end-if
print $Descr

Load Employee Status and Get Value

  Do Select_Data_From_XLAT('EMPL_STATUS',$build_where_condition)
  load-lookup name=empl_status_full_name
              Table = 'PSXLATITEM'
              Key = 'FIELDVALUE' 
              Return_Value = 'XLATSHORTNAME' 
              Where = $build_where_condition

lookup empl_status_full_name &A.EMPL_STATUS $XLATLONGNAME
If Not IsNull ($XLATLONGNAME)
 let $Descr = Rtrim($XLATLONGNAME,' ')
end-if print $Descr

Load Country Full Name and Get Value

   load-lookup name=country_full_name
			  Table = 'PS_COUNTRY_TBL'
			  Key = 'COUNTRY' 
			  Return_Value = 'DESCR' 
			  Where = 1=1

lookup country_full_name &B.COUNTRY $DESCR
If Not IsNull ($DESCR)
 let $DescrVal = Rtrim($DESCR,' ')
end-if 
print $DescrVal

Load Benefit Plan Type and Get Value

   Do Select_Data_From_XLAT('PLAN_TYPE',$build_where_condition)
  load-lookup name=TblPlanTypes
              rows=50
              table=PSXLATITEM
              key=FIELDVALUE
              return_value=XLATSHORTNAME
              where=$build_where_condition

LOOKUP TBLPLANTYPES $LookupVal $Out
If Not IsNull ($Out)
 let $DescrVal = Rtrim($Out,' ')
end-if 
print $DescrVal

Load Benefit Options Type and Get Value

 Do Select_Data_From_XLAT('OPTION_TYPE',$build_where_condition)
  load-lookup name=TblOptTypes
              rows=10
              table=PSXLATITEM
              key=FIELDVALUE
              return_value=XLATSHORTNAME
              where=$build_where_condition

LOOKUP TBLOPTTYPES $LookupVal $OptionType
If Not IsNull ($OptionType)
 let $DescrVal = Rtrim($OptionType,' ')
end-if 
print $DescrVal

Load Benefit Cost Type and Get Value

 Do Select_Data_From_XLAT('COST_TYPE',$build_where_condition)
  load-lookup name=TblCostTypes
              rows=10
              table=PSXLATITEM
              key=FIELDVALUE
              return_value=XLATSHORTNAME
              where=$build_where_condition

LOOKUP TBLCOSTTYPES $LookupVal $CostType
If Not IsNull ($CostType)
 let $DescrVal = Rtrim($CostType,' ')
end-if 
print $DescrVal

 Load Company Descriptions & Get Value

  !Building Where Condition with Dynamic SQL 
  Let $Where_Base_Company_Descr = ' AND CD.EFFDT = (SELECT MAX(CD1.EFFDT) FROM PS_COMPANY_TBL CD1  '
                                || ' WHERE CD1.COMPANY  = CD.COMPANY '
                                || ' AND   CD1.EFFDT <= ''' || $AsOfDate || ''')'

   #Debug show $Where_Base_Company_Descr

   Load-Lookup name = Base_Company_Descr
   Table            = 'PS_COMPANY_TBL CD'
   Key              = 'CD.COMPANY'
   Return_Value     = 'CD.DESCR'
   Where            = $Where_Base_Company_Descr

Lookup Base_Company_Descr $Ctl_Company $Return_Val
If Not Isnull ($Return_Val)
 let $Cpdescr = rtrim($Return_Val,' ')
end-if

To be continued…..