Lazzy Scientist: Implementing Supplier Bank Account Approval in Oracle 11i

Overview

Employees who have access to Bank Account form can deliberately assign suppliers to a bank account for one’s advantage. Implementing Supplier Bank Account Approval process can eliminate fraud.

Business Need

Supplier Bank Account setup should be temporarily end dated after new supplier assignment or change in existing supplier assignment. No further changes should be allowed for submitted supplier assignment until approval (refer Figure 1 and 2).
Approver should be notified by email for changes in supplier assignment (Figure 3).
Approver can either approve or reject
- After Approve, supplier assignment should become active.
- After Reject, it should be on hold, but submitter should be allowed further changes.

New supplier assignment should get disabled immediately after save action.

New assignment should be end dated.

workflow notification should be sent to approver based on submitter-approver lookup

FYI Notification to submitter

Solution

Any modification in supplier assignment updates AP_BANK_ACCOUNT_USES_ALL table. To track status of a supplier assignment line, Attribute15 is used.

After user modifies a supplier assignment and performs save action we will update attribute 15 to ‘S’, meaning submitted line. When approver approves, attribute15 value is changed to ‘A’ and on reject it’s changed to ‘R’.

Form personalization is used to disable the submitted supplier assignment until approved or rejected. Based on attribute 15 value we will disable supplier assignment. If value is S, line is disabled.
After user performs save action on supplier assignment screen, we will trigger approval workflow. We will create Oracle Alert to trigger workflow on Insert/update on AP_BANK_ACCOUNT_USES_ALL table.
Oracle Workflow is used to send email Notification for approval.

Components

Oracle Alert

Oracle alert is used to trigger workflow on update/insert event on AP_BANK_ACCOUNT_USES_ALL table.

select BANK_ACCOUNT_USES_ID into &acct_num FROM ap_bank_account_uses_all abau where abau.rowid =:ROWID;

Acc_num variable will hold primary key value of row modified in ap_bank_account_uses_all table.

Assign Operating unit in Actions à Installations tab

Actions

Action Set

Above mentioned Alert will create two database triggers on AP_BANK_ACCOUNT_USES_ALL table which calls workflow process.

In the workflow we have to update the same table to end date supplier assignment (Figure 2), but we don’t want database trigger to fire from inside workflow or else it will end in infinite loop. So we need to change trigger definition, created by oracle alert, to handle update on table separately when triggered from inside workflow.

Change Alert trigger definition to check context value before firing Alert action (workflow call).

[sourcecode language="sql"]

CREATE OR REPLACE CONTEXT CTX_TGR_AP_BANK_ACCOUNT_USES
USING APPS.XXTK_BANK_APPROVAL_PKG;

[/sourcecode]

Add following code in trigger definition before it submits alert action.

[sourcecode language="sql"]

---custom code added for alert trigger to not execute trigger by default
IF (NVL (SYS_CONTEXT ('CTX_TGR_AP_BANK_ACCOUNT_USES', 'TRG_FLAG'), 'FLASE') = 'FALSE')
THEN
RETURN;
END IF;

IF NVL (:NEW.attribute15, 'T') <> 'S'
THEN
RETURN;
END IF;
-- xxtk_bank_approval_pkg.set_context_trg_flag('FALSE');
[/sourcecode]

Workflow

Oracle Alert will initiate the workflow for any modification in supplier assignment. It will send approval notification to approver and based on approver action it will call corresponding workflow function.

Workflow Code

Form Personalization

Set context; which is checked before triggering oracle alert action to prevent infinite loop.

='begin
xxtk_bank_approval_pkg.set_context_trg_flag('''||'TRUE'||''');
end'

Set updatable to true.

Similarly, for following fields also set update_allowed to true

USES.VENDOR_NAME

USES.VENDOR_NUMBER

USES.START_DATE

USES.END_DATE

Raise error if one supplier assignment is already pending for approval.

(:ACCOUNTS.ALLOW_MULTI_ASSIGNMENTS_FLAG = 'N') and ((SELECT COUNT (*)

FROM ap_bank_account_uses_all

WHERE external_bank_account_id = :accounts.bank_account_id

AND attribute15 = 'S'

AND ROWNUM < 2) = 1)

If new supplier assignment set attribute15 to S.

      (NVL((SELECT 'Y'

FROM ap_bank_account_uses_v abau

WHERE abau.external_bank_account_id = :accounts.bank_account_id

   AND abau.vendor_number = :uses.vendor_number

   AND abau.vendor_site_code = :uses.vendor_site_code

and to_char(abau.start_date,'DD-MON-YYYY') = to_char(:uses.start_date,'DD-MON-YYYY')

and NVL(to_char(abau.end_date,'DD-MON-YYYY'),'01-01-4012') = NVL(to_char(:uses.end_date,'DD-MON-YYYY'),'01-01-4012')

   AND ROWNUM < 2),'N') <> 'Y')