Adding Condition While Calling the Bursting Program

In the Below Program we would be restricting the Mails for which the outstanding balance is Less that Zero.

Statement Generation Program Bursting

AR Statement Print report is used to send statements to customers on a regular basis to notify the outstanding balance that the customer needs to pay.

Note : 

The Program needs to Mail only if the Outstanding Balance is More than Zero.

This is a standard report we need to send email to customers individually.

Step 1:

When you submit the program internally print statements program will run.

Print Statement Program output style should be xml.

Step 2:

Add one formula column inside G_STATEMENT Group (getting email_address of customer site)

Formula column code snippet:

Function CF_EMAILFormula return Char is

Cursor c1 (cp_send_to_address_id Number)

Is

  Select  Hcp.Email_Address

  From   Hz_Contact_Points Hcp,

             Hz_Party_Sites Hps,

             Hz_Cust_Acct_Sites_All Hcsa

  Where  Hcp.Owner_Table_Name   = 'HZ_PARTY_SITES'

  And      Hcp.Owner_Table_Id     = Hps.Party_Site_Id

  And      Hps.Party_Site_Id      = Hcsa.Party_Site_Id

  And      Hcsa.Cust_Acct_Site_Id = cp_send_to_address_id

  And      Hcp.Contact_Point_Type ='EMAIL'

  And      Hcp.Status             = 'A';

 

Cursor c2

Is

 Select Instance_name 

 From   v$instance;

Cursor c3

Is

 Select Fpov.Profile_Option_Value

 From   Fnd_Profile_Options       Fpo

           ,Fnd_Profile_Option_Values Fpov

 Where  Fpov.Profile_Option_Id  = Fpo.Profile_Option_Id

 And    Fpo.Profile_Option_Name ='BURSTING LOCAL EMAIL';

 l_email_Address              Varchar2(2000);

 l_instance                       Varchar2(200);

 l_Profile_Option_Value    Varchar2(2000);

Begin

  l_email_Address:=NULL;

  l_Profile_Option_Value:=NULL;

  l_email_address :=NULL;

  l_instance:=NULL;

               

  Open  c2;

  Fetch c2 Into l_instance;

  Close c2;

  If Upper(l_instance) LIKE '%PROD%'

  Then   

       For i in c1(:send_to_address_id)

            Loop

                    l_email_address :=l_email_address||','||i.email_address;

            End Loop;

                 

            If l_email_address Is Not Null

            Then

                     l_email_address :=LTRIM(RTRIM(Substr( l_email_address, 2, Length(l_email_address)-1))); 

            Else

                     l_email_address := 'dummy@gmail.com';

            End If;

            Return l_email_Address;

  Else

       Open  c3;

       Fetch c3 Into l_Profile_Option_Value;

       Close c3;

       Return l_Profile_Option_Value;

   End If;    

Exception

     When Others Then

         Return NULL;

End;

Step 3:

Develop bursting control file with filtering the amount should be more than zero.

Step 4: 

Attach the Bursting Control file to Data Definition

Step 5:

Generate a statement

Responsibility: Any AR responsibility

Navigation: Print Documents -> Statements

Print Statement form

Step 6:

Manually submit  the XML Publisher Report Bursting Program

Step 7:

Automate executing Bursting program

Users might not like the idea of executing XML Publisher Report Bursting Program after firing the Statement Generation program. We can automate this part so that users will not need to run the XML Publisher Report Bursting Program.

In this case we can add a small customization into Statement Generation Program (ARXSGPO) . We can add the following code in the After Report trigger

Function AfterReport return boolean is

l_req_id              NUMBER;

begin

   fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),

                                           fnd_profile.VALUE ('RESP_ID'),

                                           fnd_profile.VALUE ('RESP_APPL_ID'),

                                           NULL,

                                           NULL

                                          );

    l_req_id := FND_REQUEST.SUBMIT_REQUEST     

                                                                  ('XDO', 

                                                                   'XDOBURSTREP',  '','',

                                                                     FALSE,

                                                                     'Y',

                                                                     :P_CONC_REQUEST_ID,

                                                                      'Y',

                                                                        '',                                                                        

                                                                       chr(0), '', '', '', '', '', '',

                                                                       '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                        '', '', '', '', '', '', '', '', '', '',

                                                                         '', '', '', '', '', '', '', '', '', '',

                                                                         '', '', '', '', '', '', '', '', ''

                                                                         );

Step 8:

Generate a statement

Responsibility: Any AR responsibility

Navigation: Print Documents -> Statements

Print Statement form