Hello,
I have a table of data related to an invoice approval workflow where I would like to evaluate each invoice (request key) for the appropriate approver. Unfortunately, the workflow software doesn't store the final approver in a field, so we have to apply logic to the workflow data to extract the approver in an automated fashion.
Below is a detailed example of the data that we would be evaluating.
For each unique request key, I would like to evaluate the following cases and return the appropriate name (could be placed in a new column next to the data for either the whole set, or just the first line, for ease of extraction with an excel formula). The data will be presorted so that the request keys will be grouped.
1) If Status Upon Step Completion for Step "Approval Level 1" = "Auto Approved" return Requesting Employee
2) For the last step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" return Employee Assigned to Step
My full sample of data is thousands of lines long and includes some items that would support the logic above (such as "Approved" Statuses where the Role is actually "Invoice Processor")
Example table:
[TABLE="class: grid, width: 962"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Request Key[/TD]
[TD]Requesting Employee[/TD]
[TD]Step Sequence[/TD]
[TD]Step[/TD]
[TD]Status Upon Step Completion[/TD]
[TD]Employee Assigned to Step[/TD]
[TD]Role[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Approved[/TD]
[TD]Stern, Howard[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Approved[/TD]
[TD]Carrey, Jim[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Approved[/TD]
[TD]Stern, Howard[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
</tbody>[/TABLE]
Example results:
Request Key 1113 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Gadot, Gal" is returned
Request Key 1019 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 2", therefore "Carrey, Jim" is returned
Request Key 1194 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 1", therefore "Stern, Howard" is returned
Request Key 1191 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Urkel, Steve" is returned
For me, the most challenging piece of code here is evaluating each of the groups (unique request keys) against the cases, and not creating the code for evaluating the case statements. If I wasn't clear on the logic for the cases being evaluated, but you understand how the groups might be isolated and evaluated, your insight there would be much appreciated!
Thanks in advance,
I have a table of data related to an invoice approval workflow where I would like to evaluate each invoice (request key) for the appropriate approver. Unfortunately, the workflow software doesn't store the final approver in a field, so we have to apply logic to the workflow data to extract the approver in an automated fashion.
Below is a detailed example of the data that we would be evaluating.
For each unique request key, I would like to evaluate the following cases and return the appropriate name (could be placed in a new column next to the data for either the whole set, or just the first line, for ease of extraction with an excel formula). The data will be presorted so that the request keys will be grouped.
1) If Status Upon Step Completion for Step "Approval Level 1" = "Auto Approved" return Requesting Employee
2) For the last step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" return Employee Assigned to Step
My full sample of data is thousands of lines long and includes some items that would support the logic above (such as "Approved" Statuses where the Role is actually "Invoice Processor")
Example table:
[TABLE="class: grid, width: 962"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Request Key[/TD]
[TD]Requesting Employee[/TD]
[TD]Step Sequence[/TD]
[TD]Step[/TD]
[TD]Status Upon Step Completion[/TD]
[TD]Employee Assigned to Step[/TD]
[TD]Role[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]1113[/TD]
[TD]Gadot, Gal[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Approved[/TD]
[TD]Stern, Howard[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Approved[/TD]
[TD]Carrey, Jim[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]1019[/TD]
[TD]Saget, Bob[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Approved[/TD]
[TD]Stern, Howard[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]1194[/TD]
[TD]Griffin, Peter[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]1[/TD]
[TD]Payment Request Submitted[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]2[/TD]
[TD]Cost Object Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Cost Object Approver[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]3[/TD]
[TD]Approval Level 1[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]4[/TD]
[TD]Approval Level 2[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]5[/TD]
[TD]Approval Level 3[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]6[/TD]
[TD]Approval Level 4[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]7[/TD]
[TD]Approval Level 5[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]8[/TD]
[TD]Approval Level 6[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Approver[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]9[/TD]
[TD]Back Office Approval[/TD]
[TD]Approved[/TD]
[TD]Doe, John[/TD]
[TD]Invoice Processor[/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]10[/TD]
[TD]Vendor Approval[/TD]
[TD]Auto Approved[/TD]
[TD]System[/TD]
[TD]Invoice Vendor Manager[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]11[/TD]
[TD]Prepayment Validation[/TD]
[TD]Pending Validation[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]12[/TD]
[TD]Pending Payment[/TD]
[TD]Extracted[/TD]
[TD]System[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD]1191[/TD]
[TD]Urkel, Steve[/TD]
[TD]13[/TD]
[TD]Payment Accounting Extract[/TD]
[TD][/TD]
[TD][/TD]
[TD]System[/TD]
[/TR]
</tbody>[/TABLE]
Example results:
Request Key 1113 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Gadot, Gal" is returned
Request Key 1019 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 2", therefore "Carrey, Jim" is returned
Request Key 1194 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 1", therefore "Stern, Howard" is returned
Request Key 1191 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Urkel, Steve" is returned
For me, the most challenging piece of code here is evaluating each of the groups (unique request keys) against the cases, and not creating the code for evaluating the case statements. If I wasn't clear on the logic for the cases being evaluated, but you understand how the groups might be isolated and evaluated, your insight there would be much appreciated!
Thanks in advance,
