oracleapps world

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;

Friday, 29 April 2022

Exporting data from OAF page to Excel programmatically

Exporting data from OAF page to Excel programmatically

Now we are going to learn about how to export the data of VO from OAF page to excel sheet.

Consider there is a table region which retrieves a data from a VO. Now we have to export that data to excel sheet.

Create a submit button in the table region.

Write the following code in the Controller ProcessFormRequest

****************************************

* Call the export button

****************************************

if(pageContext.getParameter("Export") != null)

{

/* Specify the hidden attribute column names*/

String ss[] = { null };

/* Call the method to export data */

downloadCsvFile(pageContext, "AOPErrorMsgVO1", null, "MAX", ss);

}

/* End of calling the export button */

*************************************

* Create method to export the data

*************************************

public void downloadCsvFile(OAPageContext pageContext, String view_inst_name, String file_name_without_ext, String max_size, String hidden_attrib_list[])

{

***********************************

* Specify all Column names

**********************************

String voFieldNames[] = {

"Error Message","Item/Level1", "BillToCust/Level2", "ODM/Level3", "OEM/Level4", "MarketSegment/Level5", "Program Name/Level6", "Organization/Level7","Time Period","Fiscal Year", "Total Units", "Total Sales"

};

/* End of column names */

OAViewObject v = (OAViewObject)pageContext.getRootApplicationModule().findViewObject(view_inst_name);

if(v == null)

{

throw new OAException("Could not find View object instance "+view_inst_name+" in root AM.");

}

if(v.getFetchedRowCount() == 0)

{

throw new OAException("There is no data to export.");

}

String file_name = "AOP_Error_Report";

if(file_name_without_ext != null && !"".equals(file_name_without_ext))

{

file_name = file_name_without_ext;

}

HttpServletResponse response = (HttpServletResponse)pageContext.getRenderingContext().getServletResponse();

response.setContentType("application/text");

response.setHeader("Content-Disposition", "attachment; filename="+file_name+".csv");

ServletOutputStream pw = null;

try

{

pw = response.getOutputStream();

int j = 0;

int k = 0;

boolean bb = true;

System.out.println("inside try block");

if(max_size == null || "".equals(max_size))

{

k = Integer.parseInt(pageContext.getProfile("VO_MAX_FETCH_SIZE"));

bb = false;

} else

if("MAX".equals(max_size))

{

bb = true;

} else

{

k = Integer.parseInt(max_size);

bb = false;

}

AttributeDef a[] = v.getAttributeDefs();

StringBuffer cc = new StringBuffer();

ArrayList exist_list = new ArrayList();

for(int l = 0; l < a.length; l++)

{

boolean zx = true;

if(hidden_attrib_list != null)

{

for(int z = 0; z < hidden_attrib_list.length; z++)

{

if(a[l].getName().equals(hidden_attrib_list[z]))

{

zx = false;

exist_list.add(String.valueOf(a[l].getIndex()));

}

for(int l = 0; l < voFieldNames.length; l++)

{

boolean zx = true;

if(zx)

{

cc.append("\""+voFieldNames[l])+"\"");

cc.append(",");

}

String header_row = cc.toString();

pw.println(header_row);

OAViewRowImpl row = (OAViewRowImpl)v.first();

{

if(row == null)

{

break;

}

j++;

StringBuffer b = new StringBuffer();

for(int i = 0; i < v.getAttributeCount(); i++)

{

boolean cv = true;

for(int u = 0; u < exist_list.size(); u++)

{

if(String.valueOf(i).equals(exist_list.get(u).toString()))

{

cv = false;

}

if(cv)

{

Object o = row.getAttribute(i);

if(o != null)

{

if(o.getClass().equals(Class.forName("oracle.jbo.domain.Date")))

{

Date dt = (Date)o;

java.sql.Date ts = dt.dateValue();

SimpleDateFormat displayDateFormat = new SimpleDateFormat("dd-MMM-yyyy");

String convertedDateString = displayDateFormat.format(ts);

b.append("\"" + convertedDateString + "\"");

} else

{

b.append("\"" + o.toString() + "\"");

}

} else

{

b.append("\"\"");

}

b.append(",");

}

String final_row = b.toString();

pw.println(final_row);

if(!bb && j == k)

{

break;

}

row = (OAViewRowImpl)v.next();

} while(true);

}

catch(Exception e)

{

e.printStackTrace();

throw new OAException("Unexpected Exception occurred.Exception Details :"+e.toString());

}

pageContext.setDocumentRendered(false);

try

{

pw.flush();

pw.close();

}

catch(IOException ioexception)

{

ioexception.printStackTrace();

throw new OAException("Unexpected Exception occurred.Exception Details :" ioexception.toString());

}

try

{

pw.flush();

pw.close();

}

catch(IOException e)

{

e.printStackTrace();

throw new OAException("Unexpected Exception occurred.Exception Details :"e.toString());

}

}/*End of Export method*/