Create Alert to send Happy birthday mail to employee

Create Alert to send Happy birthday mail to employee

Idea of this post is to create a simple alert and test if it is working or not.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.

As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.

Employee Birthday can be any calendar day of the year. So we will tell oracle apps 
to check daily once every calender day and see if today is employee's birthday and send email if true.

You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.

Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL

select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');

You can check the SQL for syntax using "verify" button.

One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).

Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.

Then click on "Action Details" button and define the email message as shown below.

That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.

Now comes the question, how do we test this Alert?

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]

Once concurrent program is successfully ran, the number of exceptions can be verified from Alert Manager -> History and query for alert.

Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.

Idea of this post is to create a simple alert and test if it is working or not.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.

As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.

Employee Birthday can be any calendar day of the year. So we will tell oracle apps 
to check daily once every calender day and see if today is employee's birthday and send email if true.

You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.

Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL

select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');

You can check the SQL for syntax using "verify" button.

One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).

Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.

Then click on "Action Details" button and define the email message as shown below.

That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.

Now comes the question, how do we test this Alert?

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]

Once concurrent program is successfully ran, the number of exceptions can be verified from Alert Manager -> History and query for alert.

Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.

BI Publisher: Conditionally Limiting Rows on a Page

Pseudo Code Solution

If position() mod 15 = 0 then
  Use bip section break
End if

BIP Solution

<?for-each@section:DEPT?> <?//NAME?>
<?../DEPT_NAME?>
<?for-each:EMPLOYEE?>
  Name:  <?ENAME?>   Salary: <?SAL?>
  <?if: position() mod 3 = 0?>
      PAGE BREAK APPLIED
      <?split-by-page-break:?>
  <?end if?>
<?end for-each?>
<?end for-each?>

Post Clone problem --

After cloning of DEV instance from PROD...

If Concurrent program paramter values still referring to PROD values..we need to run the Compile All FlexFields program..

Example :- paramtere value - PROD/XX/DATA/PRODDATA...

actually it should be DEV/XX/DATA/DEVDATA (even If you update the values from back end in DEV instance also we should run Compile All FlexFields program).

Java execution failed. Please check the Java Option in the option dialog.

BI Publisher XDO_TOP - MSword Debugging

In several of my previous posts over the years, I've highlighted quite a few ways to turn on debugging for the Oracle BI Publisher APIs.  I really haven't gone to much into depth on how to turn on debugging for the MSword template builder.  I've outlined an approach below that I have used with great success.  In my opinion it's the easiest way to configure BI Publisher and debug the BI Publisher APIs.
  
1. Create a directory on your C: drive called xdo_top
2. Create a sub-directory called temp:  C:\xdo_top\temp
    Create a sub-directory called resource:   C:\xdo_top\resource 
3. Create a xdodebug.cfg file in the resource directory with the following 2 lines:
LogLevel=STATEMENT
LogDir=C:\xdo_top\temp
4. Optionally copy an existing version xdo.cfg file if you need it for barcodes, micr fonts, etc.  Note: You can find this in one of the oracle bi publisher template builder directory's.
5. In MSWord goto the Add-ins menu for BIP,  Click on -> tools->options->java options, Add the following:   -DXDO_TOP=C:\\xdo_top

  


Note:  The screen shot also has a memory parameter, you do not need the memory option -Xmx256M

6. Restart msword* Not really needed but a good idea

When you start bi publisher and preview a template from MSword the following should happen:

• xdo.log should be created under c:\xdo_top\temp.  
• The log file should contain rich debugging information to help you with your troubleshooting.  

When would I need to use debugging? 
As an example, let say you wanted to use the xdo.cfg file to configure a font, but that insolent micr font isn't working in your template.  You can review the log to see if it's indeed being pulled in.

Joins between GL(General Leder) and SLA(Sub ledger Accounting) in R12

Joins between GL(General Leder) and SLA(Sub ledger Accounting) in R12

ap_invoices_all
SELECT * FROM ap_invoices_all WHERE invoice_num = 'ERS-9163-109073'
--invoice_id=145054

xla_transaction_entities
SELECT * FROM xla.xla_transaction_entities WHERE source_id_int_1 = 145054
--entity_id=437892

xla_events
SELECT * FROM xla_events WHERE entity_id=437892
--event_id=171848

xla_ae_headers
SELECT * FROM xla_ae_headers WHERE event_id=171848
--ae_header_id=1576424,1576425

xla_ae_lines
SELECT gl_sl_link_id FROM xla_ae_lines WHERE ae_header_id IN (1576424,1576425)
 --gl_sl_link_id is obtained

gl_import_references
SELECT * FROM gl_import_references
WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
       FROM xla_ae_lines
WHERE ae_header_id IN (1576424,1576425))

gl_je_batches
SELECT * FROM gl_je_batches 
WHERE je_batch_id IN (SELECT je_batch_id
        FROM gl_import_references
  WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
        FROM xla_ae_lines
  WHERE ae_header_id IN (1576424,1576425)))

gl_je_headers
SELECT * FROM gl_je_headers
WHERE je_header_id IN (SELECT je_header_id
          FROM gl_import_references
   WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
             FROM xla_ae_lines
   WHERE ae_header_id IN (1576424,1576425)))

gl_je_lines
SELECT * FROM gl_je_lines
WHERE je_header_id IN (SELECT je_header_id
          FROM gl_import_references
   WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
             FROM xla_ae_lines
   WHERE ae_header_id IN (1576424,1576425)))