Requisition Notification to Buyer

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;
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



 
  

4 comments:


  1. Great 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.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hi,
    What are 'XXREQAPP','XXREQAPPPROC','XXBUYER','XXREQNO'???
    Can anyone help me?

    ReplyDelete
  4. I simply want to give you a huge thumbs up for the great info you have got here on this post.
    apple iphone service center in chennai | apple iphone service center in chennai | apple iphone service center in chennai

    ReplyDelete