Task Detail: When the purchase requisition is approved then a notification should be sent to buyer.
Solution Summary:
1- Created a simple Workflow for sending notification to buyer
2- Created a Procedure to run the notification
3- Register the executable in Application from back end through API
4- Register a concurrent program in Applications through API
5- Attach the concurrent program in the Applications through API.
6- Run the concurrent program and schedule it with 10 minutes frequency to send the notification to buyers which have been approved during the last 10 minutes.
1- Simple Workflow design.
2- Create a procedure
create or replace procedure XXREQNOTIFTOBUYER (errbuf OUT varchar2, retcode OUT varchar2)
as
cursor c1 is
select distinct user_name, segment1 from (
select prha.segment1, prha.preparer_id, prha.authorization_status ,FU.USER_NAME
from po_requisition_headers_all prha,po_requisition_lines_all prla, FND_USER FU
where
prha.requisition_header_id = prla.requisition_header_id
and prha.authorization_status='APPROVED'
AND FU.EMPLOYEE_ID = prla.suggested_buyer_id
and prha.last_update_date>= sysdate -interval '10' minute);
v_seq_no number(10);
begin
for i in c1 loop
select XXREQAPPSEQ.nextval into v_seq_no from dual;
wf_engine.createprocess(itemtype => 'XXREQAPP',
itemkey =>v_seq_no, process => 'XXREQAPPPROC');
wf_engine.setitemattrtext(itemtype => 'XXREQAPP',
itemkey => v_seq_no, aname => 'XXBUYER',
avalue =>i.user_name);
wf_engine.setitemattrtext(itemtype => 'XXREQAPP',
itemkey =>v_seq_no, aname => 'XXREQNO',
avalue => i.segment1);
wf_engine.startprocess(itemtype => 'XXREQAPP',
itemkey => v_seq_no);
v_seq_no :=0;
end loop;
-- Return 0 for successful completion.
errbuf := '';
retcode := '0';
commit;
exception
when others then errbuf := 'Error';
retcode := '2';
end;
3- Register an Executable:
BEGIN
FND_PROGRAM.executable ('XXREQNOTIFTOBUYER' -- executable name
, 'Payables' -- application
, 'XX_REQNOTBUY_API' -- short_name
, 'Executable for Approved requisition notif to Buyer' -- description
, 'PL/SQL Stored Procedure' -- execution_method
, 'XXREQNOTIFTOBUYER' -- execution_file_name
, ''-- subroutine_name
, '' -- Execution File Path
, 'US' -- language_code
,'');
COMMIT;
END;
4- Register a concurrent Program:
BEGIN
FND_PROGRAM.register('XXREQNOTIFTOBUYER' -- program
, 'Payables' -- application
, 'Y' -- enabled
, 'XX_REQNOTBUY_API' -- short_name
, 'Approved requisition notif to Buyer' -- description
, 'XX_REQNOTBUY_API' -- executable_short_name
, 'Payables' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
COMMIT;
END;
Solution Summary:
1- Created a simple Workflow for sending notification to buyer
2- Created a Procedure to run the notification
3- Register the executable in Application from back end through API
4- Register a concurrent program in Applications through API
5- Attach the concurrent program in the Applications through API.
6- Run the concurrent program and schedule it with 10 minutes frequency to send the notification to buyers which have been approved during the last 10 minutes.
1- Simple Workflow design.
2- Create a procedure
create or replace procedure XXREQNOTIFTOBUYER (errbuf OUT varchar2, retcode OUT varchar2)
as
cursor c1 is
select distinct user_name, segment1 from (
select prha.segment1, prha.preparer_id, prha.authorization_status ,FU.USER_NAME
from po_requisition_headers_all prha,po_requisition_lines_all prla, FND_USER FU
where
prha.requisition_header_id = prla.requisition_header_id
and prha.authorization_status='APPROVED'
AND FU.EMPLOYEE_ID = prla.suggested_buyer_id
and prha.last_update_date>= sysdate -interval '10' minute);
v_seq_no number(10);
begin
for i in c1 loop
select XXREQAPPSEQ.nextval into v_seq_no from dual;
wf_engine.createprocess(itemtype => 'XXREQAPP',
itemkey =>v_seq_no, process => 'XXREQAPPPROC');
wf_engine.setitemattrtext(itemtype => 'XXREQAPP',
itemkey => v_seq_no, aname => 'XXBUYER',
avalue =>i.user_name);
wf_engine.setitemattrtext(itemtype => 'XXREQAPP',
itemkey =>v_seq_no, aname => 'XXREQNO',
avalue => i.segment1);
wf_engine.startprocess(itemtype => 'XXREQAPP',
itemkey => v_seq_no);
v_seq_no :=0;
end loop;
-- Return 0 for successful completion.
errbuf := '';
retcode := '0';
commit;
exception
when others then errbuf := 'Error';
retcode := '2';
end;
3- Register an Executable:
BEGIN
FND_PROGRAM.executable ('XXREQNOTIFTOBUYER' -- executable name
, 'Payables' -- application
, 'XX_REQNOTBUY_API' -- short_name
, 'Executable for Approved requisition notif to Buyer' -- description
, 'PL/SQL Stored Procedure' -- execution_method
, 'XXREQNOTIFTOBUYER' -- execution_file_name
, ''-- subroutine_name
, '' -- Execution File Path
, 'US' -- language_code
,'');
COMMIT;
END;
4- Register a concurrent Program:
BEGIN
FND_PROGRAM.register('XXREQNOTIFTOBUYER' -- program
, 'Payables' -- application
, 'Y' -- enabled
, 'XX_REQNOTBUY_API' -- short_name
, 'Approved requisition notif to Buyer' -- description
, 'XX_REQNOTBUY_API' -- executable_short_name
, 'Payables' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
COMMIT;
END;
5- Attaching the Concurrent Program with Reguest Group
BEGIN
FND_PROGRAM.add_to_group('XX_REQNOTBUY_API' -- program_short_name
, 'Payables' -- application
, 'All Reports' -- Report Group Name
, 'SQLAP'); -- Report Group Application
COMMIT;
END;
6- Scheduling the Concurrent program
ReplyDeleteGreat post ad thank you so much for the information. The clarity for your composing is simply polite and i could expect if you're an expert on this place of interest. Well collectively with your authorization i want to grab your feed to be upgraded with up and coming post. You can easily take this munnar best taxi for any excursions and travels program of your family. They will be providing secured and secure cabs.
This comment has been removed by the author.
ReplyDeleteHi,
ReplyDeleteWhat are 'XXREQAPP','XXREQAPPPROC','XXBUYER','XXREQNO'???
Can anyone help me?
I simply want to give you a huge thumbs up for the great info you have got here on this post.
ReplyDeleteapple iphone service center in chennai | apple iphone service center in chennai | apple iphone service center in chennai