OracleApps02: 2020

Release Hold

/* Formatted on 09/15/2020*/

declare

x_invoice_id number;

x_hold_lookup_code varchar2 (240);

x_held_by varchar2 (240);

x_calling_sequence number;

x_release_lookup_code varchar2 (240);

x_release_reason varchar2 (240);

x_responsibility_id varchar2 (240);

x_last_updated_by varchar2 (240);

x_last_update_date date;

x_holds_count number;

x_approval_status_lookup_code varchar2 (240);

lv_user_id number;

cursor c1

select apia.invoice_id,

apha.hold_lookup_code,

apha.held_by,

apha.last_updated_by,

apha.last_update_date,

apha.responsibility_id

from ap_invoices_all apia, ap_holds_all apha

where 1 = 1

and apia.payment_status_flag = 'N'

and apia.invoice_id = apha.invoice_id

and apha.release_lookup_code is null

and apia.invoice_id = 240781634;

begin

lv_user_id:=FND_GLOBAl.USER_ID;

fnd_global.apps_initialize (lv_user_id, 20639, 200);

mo_global.set_policy_context ('S', 101);

mo_global.init ('SQLAP');

for i in c1

loop

x_invoice_id := i.invoice_id;

x_hold_lookup_code := i.hold_lookup_code;

x_held_by :=lv_user_id;

x_calling_sequence := null;

x_release_lookup_code := 'VARIANCE CORRECTED';

x_release_reason := 'Invoice variance corrected';

x_responsibility_id := i.responsibility_id;

x_last_updated_by :=lv_user_id;

x_last_update_date := i.last_update_date;

x_holds_count := null;

x_approval_status_lookup_code := null;

-- ap_holds_pkg.release_single_hold (x_invoice_id,

-- x_hold_lookup_code,

-- x_release_lookup_code,

-- x_held_by,

-- x_calling_sequence);

ap_holds_pkg.quick_release (x_invoice_id,

x_hold_lookup_code,

x_release_lookup_code,

x_release_reason,

x_responsibility_id,

x_last_updated_by,

x_last_update_date,

x_holds_count,

x_approval_status_lookup_code,

x_calling_sequence);

end loop;

commit;

end;

Following steps will resolved the date issue

1) While creating Package/Procedure make sure date parameter data type should be varchar2

create or replace package xx_pkg
as
procedure xx_p (
errbuf out varchar2,
retcode out varchar2,
p_agreement_start_date in varchar2,
p_agreement_end_date in varchar2);
end xx_pkg;
/

create or replace package body apps.xx_pkg
as
procedure xx_p (errbuf out varchar2,
retcode out varchar2,
p_agreement_start_date in varchar2,
p_agreement_end_date in varchar2)
is
cursor c1 (v_agreement_start_date varchar2,
v_agreement_end_date varchar2)
is
select xx.*
from xx
where xx.start_date_active=nvl(v_agreement_start_date,xx.start_date_active)
and xx.end_date_active=nvl(v_agreement_end_date,xx.end_date_active)
and xx.end_date_active between v_agreement_start_date and v_agreement_end_date;

v_agreement_start_date date;
v_agreement_end_date date;
begin
v_agreement_start_date := fnd_date.canonical_to_date (p_agreement_start_date);
v_agreement_end_date := fnd_date.canonical_to_date (p_agreement_end_date);

fnd_file.put_line (fnd_file.output, '<OUTLOOP>');
for i in c1(v_agreement_start_date,v_agreement_end_date)
loop
fnd_file.put_line (fnd_file.output, '<INLOOP>');
fnd_file.put_line ( fnd_file.output,'<customer_name>'i.customer_name'</customer_name>');
fnd_file.put_line (fnd_file.output, '</INLOOP>');
end loop;
fnd_file.put_line (fnd_file.output, '</OUTLOOP>');
end xx_p;
end xx_pkg;
/

2) While creating Concurrent program parameter make sure parameter Value Set should be FND_STANDARD_DATE

Workflow Email Notifications not working for a particular user

Workflow Email Notifications not working for a particular user

I faced this issue on Oracle Apps R12.1.2 where in a user is not receiving email notifications.

To confirm whether the problem exists or not I have checked the mail status of notifications sent to the user and they are in FAILED status.

here are the steps which I have followed to resolve the issue

STEP 1:

Firstly I have checked the user preferences from FND_USER_PREFERENCES table with the following query

SELECT *
FROM FND_USER_PREFERENCES
WHERE user_name LIKE '&user_name'
AND module_name = 'WF' ;
I could see that preference value is defined for MAILTYPE preference.

STEP 2:

I have checked whether email address is correct or not in FND_USER, PER_ALL_PEOPLE_F and FND_USERS tables.

Email address is all the above three tables are valid and correct.

STEP 3:

Next I have checked the user role in WF_LOCAL_ROLES table with the following query

SELECT * FROM WF_LOCAL_ROLES
WHERE name LIKE '&user_name';
I got two records with different ORIG_SYSTEM values, interestingly both roles are active but generally only one role with ORIG_SYSTEM = PER must be active.

here is the screenshot of the output which shows where the real problem is

In the screenshot you can see that multiple roles are active for a same user.

STEP 4:

I tried to resolve the issue by Inactivating the role which is with ORIG_SYSTEM = WF_LOCAL_USERS.

I couldn’t find a perfect API to INACTIVE a specific role of a user, so I wrote an UPDATE statement and inactivated the role.

here is the UPDATE statement

UPDATE wf_local_roles
SET STATUS ='INACTIVE' ,
expiration_date = (sysdate-1)
WHERE name LIKE '&user_name'
AND parent_orig_system ='WF_LOCAL_USERS'
AND notification_preference ='DISABLED';
NOTE: Take backup of WF_LOCAL_ROLES table before you update and also try this in one of your TEST Instance before you apply it in PROD Instance. Do it at your own risk 😉

STEP 5:

After executing the update statement I ran “Synchronize WF LOCAL tables” concurrent program to sync all the roles.

TESTING:

I have created a sample requisition and email notifications have been sent to the user.

Release Hold

Date Problem in PL/SQL Report

Workflow Email Notifications not working for a particular user