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




  • October 24, 2014 | 24 views