Scenario: Looking to have AP reviewer assign reviewer group based on SL & PO rather than by job.

Requirements: These steps assume that you own the UD Module. If you don’t own the UD Module, you could perform step 7 with a SQL script.

Example Solution:

  1. Create UD Field (udRevGrp) on the PM Subcontract Header form with 10 Characters. Check the box in the setup to create the field on the related form SL Subcontract Entry form.
  2. Move Custom Field on both forms to an appropriate location.
  3. On both forms add the Active Invoice Reviewer group lookup by selecting the Reviewer Group field, pressing F3 to access field properties, and on the lookup tab adding the HQRGInvActive lookup and making sure it is active.
  4. Test the Reviewer Group lookup.
  5. Repeat steps 1-4 for PM Purchase Orders and Related form PO Purchase Order Entry.
  6. Create custom SQL View on the SQL Server with the script at the bottom of this post and assign select permissions to VCSUsers:
  7. Create Custom Lookup in UD Module
    1. Lookup Name: udRevGrp_SL_PO
    2. Title: Custom AP Rev Group by SL & PO
    3. FromClause: udRevGrp_SL_PO
    4. Where Clause: Line_Type = ? and (Co IS NULL or Co = ?) and (PO IS NULL or PO = ?) and (SL IS NULL OR SL = ?)
    5. Details Tab
      1. Seq,ColumnName,ColumnHeading,InputType,InputLength
      2. 0, ReviewerGroup, RevGroup,0-String,10
      3. 1, Description, Description,0-String,30
  8. Apply Custom lookup to AP Entry forms by pressing F3 on the Reviewer Group field on the Transaction Line and going to the lookup tab. Add the udRevGrp_SL_PO lookup and the following parameters 105,130,110,120.
  9. Test the configuration.
  10. Deactivate the default lookup once you have confirmed the custom lookup is functioning as expected.
  11. Consider using a user defined table to assign the reviewer group to existing SL’s & PO’s. This option would require tweaking the SQL Query below to include the data in the UD table.
  12. Consider adding a custom validation to warn AP users if they do not use the correct Reviewer Group.

SQL QUERY

CREATE VIEW udRevGrp_SL_PO 
AS

select t1.n1 as Line_Type, NULL as Co, NULL as PO, NULL as SL, ReviewerGroup, Description from HQRG

CROSS JOIN

                (select 1 as n1

                UNION select 2 as n1

                UNION select 3 as n1
 
                UNION select 4 as n1
 
                UNION select 5 as n1

                UNION select 8 as n1

                )t1
 
WHERE ReviewerGroupType = 1
 
UNION
 
select 6 as Line_Type, POCo, PO, Null as SL, udRevGrp, HQRG.Description from POHD

INNER JOIN HQRG on POHD.udRevGrp = HQRG.ReviewerGroup

UNION
 
select 7 as Line_Type, SLCo, Null as PO, SL, udRevGrp, HQRG.Description from SLHD

 INNER JOIN HQRG on SLHD.udRevGrp = HQRG.ReviewerGroup