Steps to create a XML Publisher Report

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.

2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.

3] Set the user parameter as p_conc_request_id.

4] Add the default values to the Before Report and After Report triggers(not mandatory)

5] Ftp the Report to the Cust_Top/Report/Us.

6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.

7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.

8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.

9] Design the template in Ms Word(Using the .Rtf file).

10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.

11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.

12] Now go to the Responsibility and run the request.

Sending Mail Using PL/SQL Code:

DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'ravindra.varma@gmail.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.gmail.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
/* Open the connection to host server using
UTL_SMTP.OPEN_CONNECTION */
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
/* Use UTL_SMTP.HELO function to initialize handshaking with
host server */
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
/* Use UTL_SMTP.MAIL function/procedure to initiate the server
transaction */
utl_smtp.Mail(v_Mail_Conn, v_From);
/* Specify recipient name using UTL_SMTP.RCPT function/procedure */
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
/* Compose email data using UTL_SMTP.DATA */
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
/* Use UTL_SMTP.QUIT method to terminate the connection */
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;

SQL to determine the first and last working day of the month for a given date:

SELECT MIN(DATES) FIRST_WORKING_DAY , MAX(DATES) LAST_WORKING_DAY FROM
(
    SELECT TRUNC(sysdate,'MM')+ROWNUM -1 DATES FROM
    (
    SELECT 1
    FROM Dual
    GROUP BY CUBE (2, 2, 2, 2, 2)
    )
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(sysdate,'MM'),1) - TRUNC(sysdate,'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');

SQL Query to find No. of working day in a month

SELECT * FROM
(
    SELECT TRUNC(sysdate,'MM')+ROWNUM -1 DATES FROM
    (
    SELECT 1
    FROM Dual
    GROUP BY CUBE (2, 2, 2, 2, 2)
    )
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(sysdate,'MM'),1) - TRUNC(sysdate,'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');