oracleapps world

Tuesday, 20 February 2024

Uploading PO Attachments from EBS to FTP Server

create or replace PROCEDURE xx_upload_po_attachment(errbuff out varchar2, retcode out number)

CURSOR cur_new_attmt IS

select ponumber,

creation_date,

file_name,

file_data,

file_length

from ( select pha.segment1 ponumber,pha.creation_date,

pha.segment1||'-'||fd.file_name file_name,

fl.file_data,dbms_lob.getlength(fl.file_data) file_length

from fnd_attached_documents fad,

po_headers_all pha,

fnd_documents fd,

fnd_lobs fl

where fad.entity_name in ('PO_HEADERS','PO_HEAD')

and pha.po_header_id = fad.pk1_value

and fd.document_id = fad.document_id

and fd.file_name is not null --which includes only attachments, no Long text or short text.

and fl.file_id = fd.media_id

--and pha.segment1 = '210031588'

and pha.revision_num = 0

and pha.authorization_status = 'APPROVED'

and pha.closed_code = 'OPEN'

and trunc(pha.approved_date) = trunc(sysdate)

UNION ALL

select pha.segment1 ponumber,pha.creation_date,

pha.segment1||'-'||pla.line_num||'-'||fd.file_name file_name,

fl.file_data,

dbms_lob.getlength(fl.file_data) file_length

from fnd_attached_documents fad,

po_headers_all pha,

po_lines_all pla,

fnd_documents fd,

fnd_lobs fl

where fad.entity_name in ('PO_LINES')

and pla.po_line_id = fad.pk1_value

and pha.po_header_id = pla.po_header_id

and fd.document_id = fad.document_id

and fd.file_name is not null --which includes only attachments, no Long text or short text.

and fl.file_id = fd.media_id

--and pha.segment1 = '210031588'

and pha.revision_num = 0

and pha.authorization_status = 'APPROVED'

and pha.closed_code = 'OPEN'

and trunc(pha.approved_date) = trunc(sysdate))

order by creation_date desc;

v_start NUMBER DEFAULT 1;

v_bytelen NUMBER DEFAULT 32000;

v_len_copy NUMBER;

v_raw_var RAW(32000);

v_output utl_file.file_type;

v_inv_file_name VARCHAR2(100);

v_position NUMBER;

v_first_rec BOOLEAN DEFAULT TRUE;

v_directory VARCHAR2(240);

BEGIN

v_position := 10;

select meaning

into v_directory

from fnd_lookups fl,v$instance vi

where lookup_type = 'UPLOAD_PO_ATTACHMENTS'

and upper(substr(lookup_code,1,6))like upper(substr(instance_name,1,6)) ;

FOR rec_inv IN cur_new_attmt

LOOP

BEGIN

v_inv_file_name := NULL;

v_inv_file_name := rec_inv.file_name;

v_position := 20;

-- define output directory AND OPEN THE file IN WRITE BYTE MODE

v_output := utl_file.fopen(v_directory,

v_inv_file_name,

'wb',

32760);

v_position := 30;

-- maximum size OF buffer parameter IS 32767 BEFORE

-- which you have TO flush your buffer

IF rec_inv.file_length < 32760

THEN

utl_file.put_raw(v_output,

rec_inv.file_data);

utl_file.fflush(v_output);

ELSE

v_position := 40;

v_start := 1;

v_bytelen := 32000;

v_len_copy := rec_inv.file_length;

WHILE v_start < rec_inv.file_length

AND v_bytelen > 0

LOOP

v_position := 50;

dbms_lob.READ(rec_inv.file_data,

v_bytelen,

v_start,

v_raw_var);

v_position := 60;

utl_file.put_raw(v_output,

v_raw_var);

v_position := 70;

utl_file.fflush(v_output);

v_start := v_start + v_bytelen;

v_len_copy := v_len_copy - v_bytelen;

IF v_len_copy < 32000

THEN

v_bytelen := v_len_copy;

END IF;

END LOOP;

v_position := 80;

utl_file.fclose(v_output);

END IF;

v_position := 90;

fnd_file.put_line(FND_FILE.LOG,' File_name :' || rec_inv.file_name);

dbms_output.put_line(' File_name :' || rec_inv.file_name);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(FND_FILE.LOG,nvl(v_inv_file_name,'NA')|| ' Error : '|| substr(SQLERRM,1,100));

dbms_output.put_line(rpad(nvl(v_inv_file_name,'NA'),31)

|| rpad('ERROR', 21)

|| 'POSITION: '

|| v_position

|| 'Error :'

|| substr(SQLERRM,1,100));

END;

END LOOP;

END xx_upload_po_attachment;

Thursday, 21 September 2023

Display timestamp date in SQL Developer tool

Method 1:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

Method 2:-

Go to Tools --> Preferences --> Database --> NLS --> Date Format --> DD-MON-RR HH:MI:SS

Thursday, 22 June 2023

Shell Script for production deployment in Oracle EBS for Reports

#!/bin/ksh

################################################################################

# Shell Script Name : XX_PAID_INVOICES_WITH_DISTRIBUTION.sh

# Purpose : A shell script to upload all components required for

# Paid Invoices with distribution Report

# Change History :

# Version Date Name Description

# ---------- ------------ --------------- -------------------------

# 1.0 22-Jun-2023 Simhachalam Devaki Original Code

#################################################################################

echo "**********************************************"

echo "Program XX_PAID_INVOICES_WITH_DISTRIBUTION.sh "

echo "**********************************************"

HOME_PATH=`pwd`

APPS_USER="APPS"

XX_USER="XX"

echo "Home Path is : $HOME_PATH"

LOG_FILE=$HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION_`date +%d`_`date +%m`_`date +%Y`_`date +%H`_`date +%M`_`date +%S`.log

echo "----------------------------" >> $LOG_FILE

echo >> $LOG_FILE

# Copy the LDTs to $XX_TOP/bin

cp $HOME_PATH/XX_PAID_INV_DIS_CP.ldt $XX_TOP/bin

cp $HOME_PATH/XX_PAID_INV_DIS_DD.ldt $XX_TOP/bin

# Copy the LDTs to $XX_TOP/sql

cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.sql $XX_TOP/sql

# Copy the data definition and RTF to $XX_TOP/reports/US/

cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.xml $XX_TOP/reports/US

cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.rtf $XX_TOP/reports/US

echo "Copying of files is completed"

# *******************************************************************

# Function to Check the validity of login-ids and password.

# *******************************************************************

CHKLOGIN(){

if sqlplus -s /nolog <<! >/dev/null 2>&1

WHENEVER SQLERROR EXIT 1;

CONNECT $1 ;

EXIT;

then

echo OK

else

echo NOK

}

# *******************************************************************

# Prompt for APPS Password

# *******************************************************************

while [ "$APPSID" = "" -o `CHKLOGIN "$APPSID"` = "NOK" ]

if [ "$APPSID" = "" ];then

echo ""

echo "Please enter password for apps user in hidden mode: "

stty -echo

read -s APPS_PWD

stty echo

APPSID=$APPS_USER/$APPS_PWD

else

echo " "

echo "APPS Login Password is not CORRECT"

APPSID=""

done

# *******************************************************************

# Check if CUSTOM Password is Entered else Prompt to get it

# *******************************************************************

while [ "$XXID" = "" -o `CHKLOGIN "$XXID" "DUAL"` = "NOK" ]

if [ "$XXID" = "" ];then

echo ""

echo "Please enter password for xxnjt user in hidden mode: "

stty -echo

read -s XX_PWD

stty echo

XXID=$XX_USER/$XX_PWD

else

echo " "

echo "XX Login Password is not CORRECT"

XXID=""

done

# +=================================================================================+

# | GET APPS DBHOST DBPORT and DBSID DETAILS

# +=================================================================================+

while [ "$DBHOST" = "" ]

if [ "$DBHOST" = "" ];then

echo "Enter Database Host Name : "

read DBHOST

echo "Enter Database Host Name: " $DBHOST

else

echo "Database Host Name may not be CORRECT" | tee -a $LOG_FILE

DBHOST=""

done

while [ "$DBPORT" = "" ]

if [ "$DBPORT" = "" ];then

echo "Enter Database Port : "

read DBPORT

echo "Enter Database Port: " $DBPORT

else

echo "Database Port entered may not be CORRECT" | tee -a $LOG_FILE

DBPORT=""

done

while [ "$DBSID" = "" ]

if [ "$DBSID" = "" ];then

echo "Enter Database SID : "

read DBSID

echo "Enter Database SID: " $DBSID

else

echo "Database SID entered may not be CORRECT" | tee -a $LOG_FILE

DBSID=""

done

echo $DBHOST

echo $DBPORT

echo $DBSID

#******************************************************************************

# Uploading the ldt files

#******************************************************************************

#Concurrent program

echo "Registering the concurrent programs"

# -------------------------------------------------------------------------------------

# Registering Concurrent Program : Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if $FND_TOP/bin/FNDLOAD $APPSID O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $HOME_PATH/XX_PAID_INV_DIS_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

then

echo " " >> $LOG_FILE

echo "Conc Program Paid Invoice with Invoice Distribution Report registered successfully" >> $LOG_FILE

echo " " >> $LOG_FILE

else

echo " " >> $LOG_FILE

echo "Error registering the Concurrent Program Paid Invoice with Invoice Distribution Report. Please correct and rerun." >> $LOG_FILE

exit 1

# ***********************************************************************************

# Call SQL Script to create SQL File

# ***********************************************************************************

echo "Creating SQL File " >> $LOG_FILE

echo "Creating SQL File "

if sqlplus -s $APPSID @$XX_TOP/sql/XX_PAID_INVOICES_WITH_DISTRIBUTION.sql >> $LOG_FILE 2>&1

then

echo "Installation of the SQL File is successful" >> $LOG_FILE

else

echo "Installation of the SQL File is Unsuccessful in $APPSID" >> $LOG_FILE

echo "Please check and rerun"

echo "Aborting......"

exit 1

#Data and Template Definition

# -------------------------------------------------------------------------------------

# Registering Data Definition and Template : Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if $FND_TOP/bin/FNDLOAD $APPSID O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct $HOME_PATH/XX_PAID_INV_DIS_DD.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

then

echo " " >> $LOG_FILE

echo "Data Definition and Template Paid Invoice with Invoice Distribution Report registered successfully" >> $LOG_FILE

echo " " >> $LOG_FILE

else

echo " " >> $LOG_FILE

echo "Error registering the Data Definition and Template Paid Invoice with Invoice Distribution Report. Please correct and rerun." >> $LOG_FILE

exit 1

#Data source XML

# -------------------------------------------------------------------------------------

# Upload Data source XML for Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if java oracle.apps.xdo.oa.util.XDOLoader UPLOAD\

-DB_USERNAME $APPS_USER\

-DB_PASSWORD $APPS_PWD\

-JDBC_CONNECTION $DBHOST:$DBPORT/$DBSID\

-LOB_TYPE DATA_TEMPLATE \

-LOB_CODE PAID_INV_DIS\

-XDO_FILE_TYPE XML \

-FILE_NAME $XX_TOP/reports/US/XX_PAID_INVOICES_WITH_DISTRIBUTION.xml\

-APPS_SHORT_NAME XX\

-LANGUAGE en\

-TERRITORY US\

-CUSTOM_MODE FORCE

then

echo "Uploading the XML Data Source Template for Paid Invoice with Invoice Distribution Report is Successful " >> $LOG_FILE

else

echo "Uploading the XML Data Source Template for Paid Invoice with Invoice Distribution Report is not successful" >> $LOG_FILE

exit 1

# RTF Data Template

# -------------------------------------------------------------------------------------

# Upload RTF Data Template for Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if java oracle.apps.xdo.oa.util.XDOLoader UPLOAD\

-DB_USERNAME $APPS_USER\

-DB_PASSWORD $APPS_PWD\

-JDBC_CONNECTION $DBHOST:$DBPORT/$DBSID\

-LOB_TYPE TEMPLATE\

-APPS_SHORT_NAME XX\

-LOB_CODE PAID_INV_DIS\

-LANGUAGE en\

-TERRITORY US\

-XDO_FILE_TYPE RTF\

-NLS_LANG AMERICAN\

-FILE_CONTENT_TYPE 'application/rtf'\

-FILE_NAME $XX_TOP/reports/US/XX_PAID_INVOICES_WITH_DISTRIBUTION.rtf\

-CUSTOM_MODE FORCE

then

echo "Uploading the RTF Template for Paid Invoice with Invoice Distribution Report is Successful " >> $LOG_FILE

else

echo "Uploading the RTF Template for Paid Invoice with Invoice Distribution Report is not successful" >> $LOG_FILE

exit 1

echo " "

echo "Installation is completed"

echo "Please check installation log file : "$LOG_FILE

echo "installation Completed..." >> $LOG_FILE

echo " "

exit 0

# *******************************************************************

# End of Script

# ******************************************************************

Wednesday, 1 February 2023

Package in after report trigger for XML Bursting

create or replace PACKAGE XXUNV_CPA_ACTIVITY_BURST_PKG

-- Description: This package contains the triggers for the 'XXUNV CPA Activity Report' XML Report

-- =================================================================================================

-- MODIFICATION HISTORY

-- Date version Person Comments

-- ----------- --------- -------------- ----------------------------------

-- 04/16/2021 1.0 Simhachalam Devaki Initial Version

-- =================================================================================================

--Initializing report parameters

P_CONC_REQUEST_ID NUMBER;

P_DATE_FROM DATE;

P_DATE_TO DATE;

P_VENDOR_NUMBER NUMBER;

P_EMAIL VARCHAR2(240);

FUNCTION beforeReport RETURN BOOLEAN;

FUNCTION afterReport RETURN BOOLEAN;

END XXUNV_CPA_ACTIVITY_BURST_PKG;

create or replace PACKAGE BODY XXUNV_CPA_ACTIVITY_BURST_PKG

-- Description: This package contains the triggers for the 'XXUNV CPA Activity Report' XML Report

-- =================================================================================================

-- MODIFICATION HISTORY

-- Date version Person Comments

-- ----------- --------- -------------- ----------------------------------

-- 04/16/2021 1.0 Simhachalam Devaki Initial Version

-- =================================================================================================

FUNCTION beforeReport RETURN BOOLEAN

l_err_code NUMBER;

l_err_msg VARCHAR2(2400);

BEGIN

--Initializing the concurrent request ID

P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;

RETURN TRUE;

END; -- End of beforeReport Function

FUNCTION afterReport RETURN BOOLEAN

ln_sub_req NUMBER;

lc_cp_description VARCHAR2(100);

lc_cp_request_id CONSTANT NUMBER := P_CONC_REQUEST_ID;

lc_email VARCHAR2(240);

BEGIN

P_EMAIL := TRIM(P_EMAIL);

lc_email := P_EMAIL;

--Fetch the Concurrent Program Name

BEGIN

SELECT fcp.user_concurrent_program_name

INTO lc_cp_description

FROM FND_CONCURRENT_REQUESTS fcr,

FND_CONCURRENT_PROGRAMS_VL fcp

WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.request_id = lc_cp_request_id;

EXCEPTION

WHEN OTHERS THEN

lc_cp_description := NULL;

END;

FND_FILE.PUT_LINE(FND_FILE.LOG,'lc_email: ' || lc_email);

--Call to the standard Oracle Concurrent Request for Bursting

IF lc_email IS NOT NULL THEN

BEGIN

ln_sub_req := FND_REQUEST.SUBMIT_REQUEST(

application => 'XDO', -- application

program => 'XDOBURSTREP', -- Program

description=> lc_cp_description, -- description

argument1=>'N' ,

argument2=> lc_cp_request_id, -- argument1

argument3=> 'Yes' -- argument2

);

COMMIT;

IF ln_sub_req <= 0

THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed to Submit Bursting XML Publisher Request For CR: ' || lc_cp_request_id);

ELSE

FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Bursting XML Publisher Request Request ID: ' || ln_sub_req);

END IF;

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'Encountered Exception in afterReport Trigger: ' || SUBSTR(SQLERRM,1,100));

END;

END IF;

RETURN TRUE;

END;-- End of afterReport Function

END; -- End of Package

Wednesday, 12 October 2022

Example of Outbound Interface in Oracle Apps with UTL_File and Bulk Collect

Here the sql query returns more than 2Lakh records. So used bulk collect.

create or replace PACKAGE XX_GL_ACL_AUD_DATA_PKG AS

-- --------------------------------------------------------------------------

-- Date Modified By Description

-- =========== ======== =================================================

-- --------------------------------------------------------------------------

PROCEDURE GENERATE (

p_errbuf OUT NOCOPY VARCHAR2,

p_errcode OUT NOCOPY NUMBER,

p_ledger_id IN NUMBER,

p_eff_dt_fr IN VARCHAR2,

p_eff_dt_to IN VARCHAR2,

p_period_fr IN VARCHAR2,

p_period_to IN VARCHAR2,

p_je_source IN VARCHAR2,

p_je_category IN VARCHAR2,

p_company_fr IN VARCHAR2,

p_company_to IN VARCHAR2,

p_nat_acct_fr IN VARCHAR2,

p_nat_acct_to IN VARCHAR2,

p_dept_fr IN VARCHAR2,

p_dept_to IN VARCHAR2,

p_prg_evnt_fr IN VARCHAR2,

p_prg_evnt_to IN VARCHAR2,

p_resp_fr IN VARCHAR2,

p_resp_to IN VARCHAR2,

p_interco_fr IN VARCHAR2,

p_interco_to IN VARCHAR2,

p_future_fr IN VARCHAR2,

p_future_to IN VARCHAR2,

p_dir IN VARCHAR2,

p_file IN VARCHAR2);

END XX_GL_ACL_AUD_DATA_PKG;

create or replace PACKAGE BODY XX_GL_ACL_AUD_DATA_PKG AS

-- --------------------------------------------------------------------------

-- Date Modified By Description

-- =========== ======== =================================================

-- 20-OCT-2017 Jagadeeswar Initial Creation

-- --------------------------------------------------------------------------

PROCEDURE GENERATE (

p_errbuf OUT NOCOPY VARCHAR2,

p_errcode OUT NOCOPY NUMBER,

p_ledger_id IN NUMBER,

p_eff_dt_fr IN VARCHAR2,

p_eff_dt_to IN VARCHAR2,

p_period_fr IN VARCHAR2,

p_period_to IN VARCHAR2,

p_je_source IN VARCHAR2,

p_je_category IN VARCHAR2,

p_company_fr IN VARCHAR2,

p_company_to IN VARCHAR2,

p_nat_acct_fr IN VARCHAR2,

p_nat_acct_to IN VARCHAR2,

p_dept_fr IN VARCHAR2,

p_dept_to IN VARCHAR2,

p_prg_evnt_fr IN VARCHAR2,

p_prg_evnt_to IN VARCHAR2,

p_resp_fr IN VARCHAR2,

p_resp_to IN VARCHAR2,

p_interco_fr IN VARCHAR2,

p_interco_to IN VARCHAR2,

p_future_fr IN VARCHAR2,

p_future_to IN VARCHAR2,

p_dir IN VARCHAR2,

p_file IN VARCHAR2

) AS

l_file UTL_FILE.FILE_TYPE;

ln_rowcount NUMBER := 0 ;

ln_loadcount NUMBER := 0 ;

ln_loadedcount NUMBER := 0 ;

lc_record VARCHAR2(2000):= NULL;

lc_time VARCHAR2(30) := NULL;

CURSOR gl_je_det_cur IS

Select

Jedt.Batch_Name,

Jedt.Header_Name,

Jedt.Je_Company,

Jedt.Je_Doc_Number,

Jedt.Je_Category,

Jedt.User_Je_Source_Name,

Jedt.sob_id,

Jedt.Sob_Description,

Jedt.Segment1,

Jedt.Segment2,

Jedt.Gl_Account,

Jedt.Seg2_Description,

Jedt.Line_Creation_Date,

Jedt.Line_Effective_Date,

Jedt.Line_Last_Update_Date,

Jedt.Period_Name,

Jedt.Line_Status,

Jedt.U2_User_Name,

Jedt.U_User_Name,

jedt.currency_code,

Jedt.Orig_Entered_Dr,

Jedt.Orig_Entered_Cr,

Jedt.Orig_Accounted_Dr,

Jedt.Orig_Accounted_Cr,

Case

When Jedt.Sob_Id In(1002,3047)

THEN

Case

WHEN jedt.currency_code IN('MXN','COP')

THEN

Case

When Jedt.Segment2 Between '100000' And '399999'

/*Then To_Char ( Nvl (Jedt.Orig_Accounted_Dr, 0) * Nvl (Glt.Eop_Rate, 0), '99999999999999999999D99999')

ELSE TO_CHAR ( NVL (jedt.orig_accounted_dr, 0) * NVL (glt.avg_rate, 0), '99999999999999999999D99999')*/

Then Nvl (Jedt.Orig_Accounted_Dr, 0) * Nvl (Glt.Eop_Rate, 0)

ELSE NVL (jedt.orig_accounted_dr, 0) * NVL (glt.avg_rate, 0)

End

--ELSE TO_CHAR (NVL (jedt.orig_entered_dr, 0), '99999999999999999999D99999')

ELSE NVL (jedt.orig_entered_dr, 0)

End

--Else To_Char (Nvl (jedt.Orig_Accounted_Dr, 0), '99999999999999999999D99999')

Else Nvl (jedt.Orig_Accounted_Dr, 0)

End Us_Equiv_Dr,

Case

When Jedt.Sob_Id In(1002,3047)

THEN

CASE

WHEN jedt.currency_code IN('MXN','COP')

THEN

CASE

When Jedt.Segment2 Between '100000' And '399999'

Then To_Char ( Nvl (Jedt.Orig_Accounted_Cr, 0) * Nvl (Glt.Eop_Rate, 0), '99999999999999999999D99999')

ELSE TO_CHAR ( NVL (jedt.orig_accounted_cr, 0) * NVL (glt.avg_rate, 0), '99999999999999999999D99999')*/

THEN Nvl(Jedt.Orig_Accounted_Cr, 0) * Nvl (Glt.Eop_Rate, 0)

ELSE NVL(jedt.orig_accounted_cr, 0) * NVL (glt.avg_rate, 0)

End

--ELSE TO_CHAR (NVL (jedt.orig_entered_cr, 0), '99999999999999999999D99999')

ELSE NVL (jedt.orig_entered_cr, 0)

End

--Else To_Char (Nvl (Jedt.Orig_Accounted_Cr, 0), '99999999999999999999D99999')

Else Nvl (Jedt.Orig_Accounted_Cr, 0)

END us_equiv_cr,

Jedt.Line_Description,

Jedt.Line_Invoice_Date,

Jedt.Line_Tax_Code,

Jedt.Line_Inv_Identifier,

Jedt.Invoice_Amount,

Jedt.Line_Ref1,

Jedt.Line_Ref2,

Jedt.Line_Ref3,

Jedt.Line_Ref4,

Jedt.Line_Ref5,

Jedt.Line_Ref6,

Jedt.Line_Ref7,

Jedt.Line_Ref10,

Jedt.Level_0_Account_Description,

Jedt.Level_2_Account_Code,

--To_Char (Nvl (Glt.Eop_Rate, -99), '9999D9999999999') Eop_Rate,

Nvl(Glt.Eop_Rate, -99) Eop_Rate,

--TO_CHAR (NVL (glt.avg_rate, -99), '9999D9999999999') avg_rate,

NVL (glt.avg_rate, -99) avg_rate,

Jedt.Rate_Used,

jedt.description

from

(SELECT

gjb.name batch_name,

TRIM(TRANSLATE(REGEXP_REPLACE(gjh.name,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) header_name,

--gjh.name header_name,

(SELECT TRIM(TRANSLATE(REGEXP_REPLACE(seg1_description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU'))

FROM gl_seg1_values

WHERE seg1_value = gcc.segment1 ) je_company,

--TO_CHAR (gjh.doc_sequence_value, '999999999999999') je_doc_number,

gjh.doc_sequence_value je_doc_number,

gjh.je_category je_category,

gjs.user_je_source_name user_je_source_name,

gsob.ledger_id sob_id,

TRIM(TRANSLATE(REGEXP_REPLACE(gsob.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) sob_description,

gcc.segment1 segment1,

Gcc.Segment2 Segment2,

Gcc.Segment3 Segment3,

Gcc.Segment4 Segment4,

Gcc.Segment5 Segment5,

Gcc.Segment6 Segment6,

gcc.segment7 segment7,

gcc.segment1

|| '-'

|| gcc.segment2

|| '-'

|| gcc.segment3

|| '-'

|| gcc.segment4

|| '-'

|| gcc.segment5

|| '-'

|| gcc.segment6

|| '-'

|| gcc.segment7 gl_account,

(SELECT seg2_description

FROM gl_seg2_values

WHERE seg2_value = gcc.segment2 ) seg2_description,

/*TO_CHAR (gjl.creation_date, 'MM/DD/YYYY') line_creation_date,

TO_CHAR (gjl.effective_date, 'MM/DD/YYYY') line_effective_date,

TO_CHAR (gjl.last_update_date, 'MM/DD/YYYY') line_last_update_date,*/

gjl.creation_date line_creation_date,

gjl.effective_date line_effective_date,

gjl.last_update_date line_last_update_date,

gjl.period_name period_name,

gjl.status line_status,

(SELECT user_name FROM fnd_user

WHERE user_id = gjl.created_by) u2_user_name,

(SELECT user_name FROM fnd_user

WHERE user_id = gjl.last_updated_by) u_user_name,

gjh.currency_code currency_code,

/*TO_CHAR (NVL (gjl.entered_dr, 0), '99999999999999999999D99999') orig_entered_dr,

TO_CHAR (NVL (gjl.entered_cr, 0), '99999999999999999999D99999') orig_entered_cr,

TO_CHAR (NVL (gjl.accounted_dr, 0), '99999999999999999999D99999') orig_accounted_dr,

To_Char (Nvl (Gjl.Accounted_Cr, 0), '99999999999999999999D99999') Orig_Accounted_Cr, */

NVL (gjl.entered_dr, 0) orig_entered_dr,

NVL (gjl.entered_cr, 0) orig_entered_cr,

NVL (gjl.accounted_dr, 0) orig_accounted_dr,

Nvl (Gjl.Accounted_Cr, 0) Orig_Accounted_Cr,

TRIM(TRANSLATE(REGEXP_REPLACE(gjl.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) line_description,

gjl.invoice_date line_invoice_date,

gjl.tax_code line_tax_code,

gjl.invoice_identifier line_inv_identifier,

--TO_CHAR (NVL (gjl.invoice_amount, 0), '99999999999999999999D99999') invoice_amount,

NVL (gjl.invoice_amount, 0) invoice_amount,

gjl.reference_1 line_ref1,

gjl.reference_2 line_ref2,

gjl.reference_3 line_ref3,

gjl.reference_4 line_ref4,

gjl.reference_5 line_ref5,

gjl.reference_6 line_ref6,

gjl.reference_7 line_ref7,

gjl.reference_10 line_ref10,

'UNKNOWN' Level_0_Account_Description,

'UNKNOWN' level_2_account_code,

CASE

WHEN gjh.currency_code IN('MXN','COP')

THEN

CASE

WHEN gcc.segment2 BETWEEN '100000' AND '399999'

THEN 'EOP'

ELSE 'AVG'

END

ELSE 'NONE'

End Rate_Used,

TRIM(TRANSLATE(REGEXP_REPLACE(gjh.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) Description,

gjh.actual_flag

From Gl_Ledgers Gsob

Inner Join Gl_Period_Statuses Gps

On(Gps.Ledger_Id = Gsob.Ledger_Id

And Gps.Application_Id = 101)

Inner Join Gl_Je_Headers Gjh

On(Gjh.Ledger_Id = Gps.Ledger_Id

And Gjh.Period_Name = Gps.Period_Name

AND Gjh.Actual_Flag = 'A')

Inner Join Gl_Je_Lines Gjl

On(Gjl.Ledger_Id = Gjh.Ledger_Id

And Gjl.Je_Header_Id = Gjh.Je_Header_Id

And Gjl.Status = Gjh.Status

And gjl.Status = 'P')

Inner Join Gl_Code_Combinations Gcc

On(Gcc.Code_Combination_Id = Gjl.Code_Combination_Id

And Gcc.Chart_Of_Accounts_Id = Gsob.Chart_Of_Accounts_Id

And Gcc.Segment2 Not In('S00200','299999','S00201') )

Inner Join Gl_Je_Categories Gjc

On(Gjh.Je_Category = Gjc.Je_Category_Name)

Inner Join Gl_Je_Sources Gjs

On(Gjh.Je_Source = Gjs.Je_Source_Name)

Inner Join Gl_Je_Batches Gjb

On(Gjb.Je_Batch_Id = Gjh.Je_Batch_Id)

Where 1=1

--And gjl.Status = 'P'

--And Gjh.Actual_Flag = 'A'

--And Gcc.Segment2 Not In('S00200','299999','S00201')

And Gsob.Ledger_Id = Nvl(P_Ledger_Id,Gsob.Ledger_Id)

And Gjs.Je_Source_Name = Nvl(P_Je_Source,Gjs.Je_Source_Name)

And Gjc.Je_Category_Name = Nvl(P_Je_Category,Gjc.Je_Category_Name)

And To_Date(Gjh.Period_Name,'MON-YY') Between Nvl(To_Date(P_Period_Fr,'MON-YY'),To_Date(Gjh.Period_Name,'MON-YY'))

And Nvl(To_Date(P_Period_To,'MON-YY'),To_Date(Gjh.Period_Name,'MON-YY'))

And Gjl.Effective_Date >= Nvl(To_Date(P_Eff_Dt_Fr,'YYYY/MM/DD HH24:MI:SS'),Gjl.Effective_Date)

And Gjl.Effective_Date <= Nvl(To_Date(P_Eff_Dt_To,'YYYY/MM/DD HH24:MI:SS'),Gjl.Effective_Date)

And gcc.segment1 = ALL (Select distinct glcc.segment1

From Gl_Code_Combinations glcc

Where Glcc.Segment1 >= P_Company_Fr

And Glcc.Segment1 <= P_Company_To)

And gcc.segment2 = ALL (Select distinct glcc.segment2

From Gl_Code_Combinations Glcc

Where Glcc.Segment2 >= P_Nat_Acct_Fr

And Glcc.Segment2 <= P_Nat_Acct_To)

And Gcc.Segment3 = All (Select Distinct Glcc.Segment3

From Gl_Code_Combinations glcc

Where Glcc.Segment3 >= P_Dept_Fr

And Glcc.Segment3 <= P_Dept_To)

And gcc.segment4 = ALL (Select distinct glcc.segment4

From Gl_Code_Combinations Glcc

Where Glcc.Segment4 >= P_Prg_Evnt_Fr

And Glcc.Segment4 <= P_Prg_Evnt_To)

And Gcc.Segment5 = All (Select Distinct Glcc.Segment5

From Gl_Code_Combinations glcc

Where Glcc.Segment5 >= P_Resp_Fr

And Glcc.Segment5 <= P_Resp_To)

And Gcc.Segment6 = All (Select Distinct Glcc.Segment6

From Gl_Code_Combinations Glcc

Where Glcc.Segment6 >= P_Interco_Fr

And Glcc.Segment6 <= P_Interco_To)

And gcc.segment7 = ALL (Select distinct glcc.segment7

From Gl_Code_Combinations glcc

Where Glcc.Segment7 >= P_Future_Fr

And Glcc.Segment7 <= P_Future_Fr)

)Jedt

Left Join

(SELECT DISTINCT

CASE

WHEN set_of_books_id = 1002

THEN 'MXN'

WHEN set_of_books_id = 3047

THEN 'COP'

WHEN Set_Of_Books_Id = 1001

THEN 'USD'

END currency_code,

set_of_books_id,

Period_Name,

DECODE(Set_Of_Books_Id, '1001',1,Eop_Rate) eop_rate,

Decode(Set_Of_Books_Id, '1001',1,Avg_Rate) Avg_Rate

From Gl_Translation_Rates) Glt

ON (Glt.Period_Name = Jedt.Period_Name

And Glt.Currency_Code = Jedt.Currency_Code

And Glt.Set_Of_Books_Id = Jedt.Sob_Id);

CURSOR gl_je_acc_cur

SELECT '"'||

replace(batch_name,'"','')||'","'||

replace(header_name,'"','')||'","'||

je_company||'","'||

TO_CHAR (je_doc_number, '999999999999999') ||'","'||

je_category||'","'||

user_je_source_name||'","'||

sob_id||'","'||

sob_description||'","'||

segment1||'","'||

segment2||'","'||

gl_account||'","'||

seg2_description||'","'||

line_creation_date||'","'||

line_effective_date||'","'||

line_last_update_date||'","'||

TO_CHAR(period_name)||'","'||

line_status||'","'||

u2_user_name||'","'||

u_user_name||'","'||

currency_code||'","'||

TO_CHAR(orig_entered_dr,'99999999999999999999D99999') ||'","'||

TO_CHAR(orig_entered_cr,'99999999999999999999D99999') ||'","'||

TO_CHAR(orig_accounted_dr,'99999999999999999999D99999')||'","'||

TO_CHAR(orig_accounted_cr,'99999999999999999999D99999')||'","'||

TO_CHAR(us_equiv_dr,'99999999999999999999D99999') ||'","'||

TO_CHAR(us_equiv_cr,'99999999999999999999D99999') ||'","'||

line_description||'","'||

line_invoice_date||'","'||

line_tax_code||'","'||

line_inv_identifier||'","'||

TO_CHAR(invoice_amount,'99999999999999999999D99999')||'","'||

line_ref1||'","'||

line_ref2||'","'||

line_ref3||'","'||

line_ref4||'","'||

line_ref5||'","'||

line_ref6||'","'||

line_ref7||'","'||

line_ref10||'","'||

Level_0_Account_Description||'","'||

level_2_account_code||'","'||

TO_CHAR(eop_rate,'9999D9999999999')||'","'||

TO_CHAR(avg_rate,'9999D9999999999')||'","'||

rate_used||'","'||

replace(description,'"','')||'"' rec

FROM XXU.XX_AUD_GL_JE_LINES_GLT;

TYPE xx_gl_je_lines_tab IS TABLE OF xxu.xx_aud_gl_je_lines_glt%ROWTYPE;

xx_gl_acc_load xx_gl_je_lines_tab;

TYPE xx_gl_acc_tab IS TABLE OF gl_je_acc_cur%ROWTYPE;

xx_gl_acc_inrt xx_gl_acc_tab := xx_gl_acc_tab();

BEGIN

lc_record := NULL;

lc_time := NULL;

ln_rowcount := 0 ;

ln_loadcount := 0 ;

ln_loadedcount := 0 ;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters......');

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Ledger ID : '||p_ledger_id);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Source : '||p_je_source);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Category : '||p_je_category);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Effective From Date : '||p_eff_dt_fr);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Effective To Date : '||p_eff_dt_to);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'GL Period From : '||p_period_fr);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'GL Period To : '||p_period_to);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Directory : '||p_dir);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'File Name : '||p_file);

FND_FILE.PUT_LINE(FND_FILE.LOG, '');

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;

EXECUTE IMMEDIATE 'TRUNCATE TABLE XXU.XX_AUD_GL_JE_LINES_GLT';

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data Loading Strated at '||lc_time);

OPEN gl_je_det_cur;

LOOP

FETCH gl_je_det_cur BULK COLLECT INTO xx_gl_acc_load LIMIT 10000;

FORALL i IN 1..xx_gl_acc_load.COUNT

INSERT INTO XXU.XX_AUD_GL_JE_LINES_GLT VALUES xx_gl_acc_load(i);

ln_loadcount := ln_loadcount + xx_gl_acc_load.COUNT;

EXIT WHEN gl_je_det_cur%notfound;

END LOOP;

CLOSE gl_je_det_cur;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Record count eligible for load - '||ln_loadcount);

SELECT COUNT(*) INTO ln_loadedcount FROM XXU.XX_AUD_GL_JE_LINES_GLT;

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Record count in GT Table - '||ln_loadedcount);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data Loading completed at '||lc_time);

FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract Generation Started at '||lc_time);

lc_record := '"'||'JE_BATCH_NAME'||'","'|| 'JE_HEADER_NAME'||'","'|| 'JE_COMPANY'||'","'|| 'JE_DOC_NUMBER'||'","'|| 'JE_CATEGORY'||'","'||

'JE_SOURCE'||'","'||'SOD_ID'||'","'||'SOB_DESCRIPTION'||'","'|| 'COMPANY_CODE'||'","'|| 'NATURAL_ACCOUNT_CODE'||'","'|| 'GL_ACCOUNT'||'","'||

'NATURAL_ACCOUNT'||'","'|| 'CREATION_DATE'||'","'|| 'EFFECTIVE_DATE'||'","'|| 'TRANSACTION_DATE'||'","'|| 'PERIOD_NAME'||'","'||

'STATUS'||'","'|| 'CREATED_BY'||'","'|| 'UPDATED_BY'||'","'|| 'CURRENCY_CODE'||'","'|| 'ORIG_ENTERED_DR'||'","'||

'ORIG_ENTERED_CR'||'","'|| 'ORIG_ACCOUNTED_DR'||'","'|| 'ORIG_ACCOUNTED_CR'||'","'|| 'US_EQUIV_DR'||'","'||'US_EQUIV_CR'||'","'||

'JE_DESCRIPTION'||'","'|| 'INVOICE_DATE'||'","'|| 'TAX_CODE'||'","'|| 'INVOICE_IDENTIFIER'||'","'||'INVOICE_AMOUNT'||'","'||

'REFERENCE_1'||'","'||'REFERENCE_2'||'","'|| 'REFERENCE_3'||'","'|| 'REFERENCE_4'||'","'|| 'REFERENCE_5'||'","'||

'REFERENCE_6'||'","'|| 'REFERENCE_7'||'","'|| 'REFERENCE_10'||'","'|| 'LEVEL_0_ACCOUNT_DESCRIPTION'||'","'||'LEVEL_2_ACCOUNT_CODE'||'","'||

'EOP_RATE'||'","'|| 'AVG_RATE'||'","'|| 'RATE_USED'||'","'|| 'DESCRIPTION'||'"';

OPEN gl_je_acc_cur;

l_file := UTL_FILE.fopen(p_dir, p_file, 'W', 32767);

-- writing File Header record

UTL_FILE.put_line(l_file, lc_Record);

LOOP

FETCH Gl_Je_Acc_Cur BULK COLLECT INTO XX_Gl_Acc_Inrt LIMIT 10000;

FOR i IN 1..XX_Gl_Acc_Inrt.COUNT

LOOP

UTL_FILE.PUT_LINE(l_file, XX_Gl_Acc_Inrt(i).rec);

END LOOP;

Ln_Rowcount := Ln_Rowcount + XX_Gl_Acc_Inrt.COUNT;

EXIT WHEN gl_je_acc_cur%notfound;

END LOOP;

CLOSE gl_je_acc_cur;

UTL_FILE.fclose(l_file);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract File record count....'||Ln_Rowcount);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract File....'||p_file);

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract Generation Completed at '||lc_time);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception : '||SQLERRM);

END GENERATE;

END XX_GL_ACL_AUD_DATA_PKG;

Friday, 30 September 2022

Send email in Oracle EBS by using UTL_SMTP

create or replace PACKAGE xx_send_mail IS

----------------------- Customizable Section -----------------------

-- Customize the SMTP host, port and your domain name below.

smtp_host VARCHAR2(256) := 'mercury';

smtp_port PLS_INTEGER := 25;

smtp_domain VARCHAR2(256) := 'xx.ss.net';

-- Customize the signature that will appear in the email's MIME header.

-- Useful for versioning.

MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';

--------------------- End Customizable Section ---------------------

-- A unique string that demarcates boundaries of parts in a multi-part email

-- The string should not appear inside the body of any part of the email.

-- Customize this if needed or generate this randomly dynamically.

BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;

LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||

utl_tcp.CRLF;

-- A MIME type that denotes multi-part email (MIME) messages.

MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||

BOUNDARY || '"';

MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;

-- A simple email API for sending email in plain text in a single call.

-- The format of an email address is one of these:

-- someone@some-domain

-- "Someone at some domain" <someone@some-domain>

-- Someone at some domain <someone@some-domain>

-- The recipients is a list of email addresses separated by

-- either a "," or a ";"

PROCEDURE mail(sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2);

PROCEDURE mail(srce IN VARCHAR2

, sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2);

PROCEDURE mail_attach(sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2

, p_attch IN VARCHAR2);

-- Extended email API to send email in HTML or plain text with no size limit.

-- First, begin the email by begin_mail(). Then, call write_text() repeatedly

-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send

-- email in non-ASCII or multi-byte character set. End the email with

-- end_mail().

FUNCTION begin_mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL)

RETURN utl_smtp.connection;

-- Write email body in ASCII

PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2);

-- Write email body in non-ASCII (including multi-byte). The email body

-- will be sent in the database character set.

PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2);

-- Write email body in binary

PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,

message IN RAW);

-- APIs to send email with attachments. Attachments are sent by sending

-- emails in "multipart/mixed" MIME format. Specify that MIME format when

-- beginning an email with begin_mail().

-- Send a single text attachment.

PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,

data IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE);

-- Send a binary attachment. The attachment will be encoded in Base-64

-- encoding format.

PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,

data IN RAW,

mime_type IN VARCHAR2 DEFAULT 'application/octet',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE);

-- Send an attachment with no size limit. First, begin the attachment

-- with begin_attachment(). Then, call write_text repeatedly to send

-- the attachment piece-by-piece. If the attachment is text-based but

-- in non-ASCII or multi-byte character set, use write_mb_text() instead.

-- To send binary attachment, the binary content should first be

-- encoded in Base-64 encoding format using the demo package for 8i,

-- or the native one in 9i. End the attachment with end_attachment.

PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

transfer_enc IN VARCHAR2 DEFAULT NULL);

-- End the attachment.

PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,

last IN BOOLEAN DEFAULT FALSE);

-- End the email.

PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

-- Extended email API to send multiple emails in a session for better

-- performance. First, begin an email session with begin_session.

-- Then, begin each email with a session by calling begin_mail_in_session

-- instead of begin_mail. End the email with end_mail_in_session instead

-- of end_mail. End the email session by end_session.

FUNCTION begin_session RETURN utl_smtp.connection;

-- Begin an email in a session.

PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,

sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL);

-- End an email in a session.

PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);

-- End an email session.

PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;

create or replace PACKAGE BODY xx_send_mail IS

-- Return the next email address in the list of email addresses, separated

-- by either a "," or a ";". The format of mailbox may be in one of these:

-- someone@some-domain

-- "Someone at some domain" <someone@some-domain>

-- Someone at some domain <someone@some-domain>

FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

addr VARCHAR2(256);

i pls_integer;

FUNCTION lookup_unquoted_char(str IN VARCHAR2,

chrs IN VARCHAR2) RETURN pls_integer AS

c VARCHAR2(5);

i pls_integer;

len pls_integer;

inside_quote BOOLEAN;

BEGIN

inside_quote := false;

i := 1;

len := length(str);

WHILE (i <= len) LOOP

c := substr(str, i, 1);

IF (inside_quote) THEN

IF (c = '"') THEN

inside_quote := false;

ELSIF (c = '\') THEN

i := i + 1; -- Skip the quote character

END IF;

GOTO next_char;

END IF;

IF (c = '"') THEN

inside_quote := true;

GOTO next_char;

END IF;

IF (instr(chrs, c) >= 1) THEN

RETURN i;

END IF;

<<next_char>>

i := i + 1;

END LOOP;

RETURN 0;

END;

BEGIN

addr_list := ltrim(addr_list);

i := lookup_unquoted_char(addr_list, ',;');

IF (i >= 1) THEN

addr := substr(addr_list, 1, i - 1);

addr_list := substr(addr_list, i + 1);

ELSE

addr := addr_list;

addr_list := '';

END IF;

i := lookup_unquoted_char(addr, '<');

IF (i >= 1) THEN

addr := substr(addr, i + 1);

i := instr(addr, '>');

IF (i >= 1) THEN

addr := substr(addr, 1, i - 1);

END IF;

RETURN addr;

END;

-- Write a MIME header

PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,

name IN VARCHAR2,

value IN VARCHAR2) IS

BEGIN

utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);

END;

-- Mark a message-part boundary. Set <last> to TRUE for the last boundary.

PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,

last IN BOOLEAN DEFAULT FALSE) AS

BEGIN

IF (last) THEN

utl_smtp.write_data(conn, LAST_BOUNDARY);

ELSE

utl_smtp.write_data(conn, FIRST_BOUNDARY);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE mail(sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2) IS

conn utl_smtp.connection;

BEGIN

IF fnd_global.conc_program_id = -1 THEN

dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

ELSE

conn := begin_mail(sender, recipients, subject);

write_text(conn, message);

end_mail(conn);

END IF;

END;

PROCEDURE mail(srce IN VARCHAR2

, sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2) IS

conn utl_smtp.connection;

BEGIN

--IF fnd_global.conc_program_id = -1 THEN

-- dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

--ELSE

conn := begin_mail(sender, recipients, subject);

write_text(conn, message);

end_mail(conn);

--END IF;

END;

PROCEDURE mail_attach(sender IN VARCHAR2

, recipients IN VARCHAR2

, subject IN VARCHAR2

, message IN VARCHAR2

, p_attch IN VARCHAR2)

conn utl_smtp.connection;

BEGIN

--IF fnd_global.conc_program_id = -1 THEN

-- dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

--ELSE

conn := begin_mail(sender, recipients, subject);

IF p_attch is not null then

attach_text( conn => conn, data => message, inline => False, filename => p_attch, last => true);

else

write_text(conn, message);

end if;

end_mail(conn);

--END IF;

END;

------------------------------------------------------------------------

FUNCTION begin_mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL)

RETURN utl_smtp.connection IS

conn utl_smtp.connection;

BEGIN

conn := begin_session;

begin_mail_in_session(conn, sender, recipients, subject, mime_type,

priority);

RETURN conn;

END;

------------------------------------------------------------------------

PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2) IS

BEGIN

utl_smtp.write_data(conn, message);

END;

------------------------------------------------------------------------

PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2) IS

BEGIN

utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));

END;

------------------------------------------------------------------------

PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,

message IN RAW) IS

BEGIN

utl_smtp.write_raw_data(conn, message);

END;

------------------------------------------------------------------------

PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,

data IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE) IS

BEGIN

begin_attachment(conn, mime_type, inline, filename);

write_text(conn, data);

end_attachment(conn, last);

END;

------------------------------------------------------------------------

PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,

data IN RAW,

mime_type IN VARCHAR2 DEFAULT 'application/octet',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE) IS

i PLS_INTEGER;

len PLS_INTEGER;

BEGIN

begin_attachment(conn, mime_type, inline, filename, 'base64');

-- Split the Base64-encoded attachment into multiple lines

i := 1;

len := utl_raw.length(data);

WHILE (i < len) LOOP

IF (i + MAX_BASE64_LINE_WIDTH < len) THEN

utl_smtp.write_raw_data(conn,

utl_encode.base64_encode(utl_raw.substr(data, i,

MAX_BASE64_LINE_WIDTH)));

ELSE

utl_smtp.write_raw_data(conn,

utl_encode.base64_encode(utl_raw.substr(data, i)));

END IF;

utl_smtp.write_data(conn, utl_tcp.CRLF);

i := i + MAX_BASE64_LINE_WIDTH;

END LOOP;

end_attachment(conn, last);

END;

------------------------------------------------------------------------

PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

transfer_enc IN VARCHAR2 DEFAULT NULL) IS

BEGIN

write_boundary(conn);

write_mime_header(conn, 'Content-Type', mime_type);

IF (filename IS NOT NULL) THEN

IF (inline) THEN

write_mime_header(conn, 'Content-Disposition',

'inline; filename="'||filename||'"');

ELSE

write_mime_header(conn, 'Content-Disposition',

'attachment; filename="'||filename||'"');

END IF;

IF (transfer_enc IS NOT NULL) THEN

write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);

END IF;

utl_smtp.write_data(conn, utl_tcp.CRLF);

END;

------------------------------------------------------------------------

PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,

last IN BOOLEAN DEFAULT FALSE) IS

BEGIN

utl_smtp.write_data(conn, utl_tcp.CRLF);

IF (last) THEN

write_boundary(conn, last);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

end_mail_in_session(conn);

end_session(conn);

END;

------------------------------------------------------------------------

FUNCTION begin_session RETURN utl_smtp.connection IS

conn utl_smtp.connection;

BEGIN

-- open SMTP connection

conn := utl_smtp.open_connection(smtp_host, smtp_port);

utl_smtp.helo(conn, smtp_domain);

RETURN conn;

END;

------------------------------------------------------------------------

PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,

sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL) IS

my_recipients VARCHAR2(32767) := recipients;

my_sender VARCHAR2(32767) := sender;

BEGIN

-- Specify sender's address (our server allows bogus address

-- as long as it is a full email address (xxx@yyy.com).

utl_smtp.mail(conn, get_address(my_sender));

-- Specify recipient(s) of the email.

WHILE (my_recipients IS NOT NULL) LOOP

utl_smtp.rcpt(conn, get_address(my_recipients));

END LOOP;

-- Start body of email

utl_smtp.open_data(conn);

-- Set "From" MIME header

write_mime_header(conn, 'From', sender);

-- Set "To" MIME header

write_mime_header(conn, 'To', recipients);

-- Set "Subject" MIME header

write_mime_header(conn, 'Subject', subject);

-- Set "Content-Type" MIME header

write_mime_header(conn, 'Content-Type', mime_type);

-- Set "X-Mailer" MIME header

write_mime_header(conn, 'X-Mailer', MAILER_ID);

-- Set priority:

-- High Normal Low

-- 1 2 3 4 5

IF (priority IS NOT NULL) THEN

write_mime_header(conn, 'X-Priority', priority);

END IF;

-- Send an empty line to denotes end of MIME headers and

-- beginning of message body.

utl_smtp.write_data(conn, utl_tcp.CRLF);

IF (mime_type LIKE 'multipart/mixed%') THEN

write_text(conn, 'This is a multi-part message in MIME format.' ||

utl_tcp.crlf);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

utl_smtp.close_data(conn);

END;

------------------------------------------------------------------------

PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

utl_smtp.quit(conn);

END;