Hey everyone, trying to get a full GL sample throu...
# generalm
Munir Chaer
03/04/2024, 4:28 PMHey everyone, trying to get a full GL sample through use the transaction saved search, but I'm not able to get a consistent approved by field to populate against all "journal" types. Anyone have any suggestions to have NS pull the final JE approver for all journals?
Munir Chaer
03/04/2024, 4:28 PMLike, is there a combination of criteria and results, that would make sure I get a full GL population without making duplicate lines (due to having multiple results).
s
Sam-I-Am
03/04/2024, 4:50 PMOnly way to do that is to use systemnotes join and look for the change - Pending Approval to Approved for Posting - set by would be the person who approved it at the final stage. Other option is to look into workflow log if approval process is using one. Also look at my suiteworld presentation on the page #34 - got a search example exactly for this
https://static.rainfocus.com/oracle/sw19/sess/1546631310573001gv1X/PDFPF%20/FIN1526SES_THURS_1553185241855001B1fO.pdf
Sam-I-Am
03/04/2024, 4:51 PMopps page 24 and 25
m
Munir Chaer
03/04/2024, 5:03 PMThanks @Sam-I-Am! I'll give it a shot.
Munir Chaer
03/04/2024, 5:32 PM@Sam-I-Am I'm not too great at building out formulas, can you help me with preparing one that pulls out the most recent "Set By" field through system notes?
s
Sam-I-Am
03/04/2024, 6:09 PMjust remove the highlighted line - should give you when it was approved and by whom
Sam-I-Am
03/04/2024, 6:12 PM1st formula
CASE WHEN {systemnotes.field} = 'Document Status' AND {systemnotes.newvalue} = 'Approved for Posting' THEN TO_CHAR({systemnotes.date}, 'MM/DD/YYYY HHMISS') END
2nd formula
CASE WHEN {systemnotes.field} = 'Document Status' AND {systemnotes.newvalue} = 'Approved for Posting' THEN TO_CHAR({systemnotes.name}) END
4th formula
max(to_char(substr({systemnotes.name},1,4000)))keep(dense_rank last order by{systemnotes.date})
3rd formula to include any further edit
TO_CHAR({systemnotes.date}, 'MM/DD/YYYY HHMISS')
2 Views