OracleApps02: GL Interface and send Mail to Someone

CREATE OR REPLACE package APPS.xxdm_gl_ledger_intg_pkg
as
type dm_gl_ledger_rec_type is record
(
ramco_reference_id number,
source_name varchar2(240),
category_name varchar2(240),
batch_name varchar2 (40),
journal_name varchar2 (240),
accounting_date date,
period_name varchar2 (15),
segment1 varchar2 (25),
segment2 varchar2 (25),
segment3 varchar2 (25),
segment4 varchar2 (25),
segment5 varchar2 (25),
segment6 varchar2 (25),
segment7 varchar2 (25),
entered_dr number,
entered_cr number,
reference1 varchar2 (240),
reference2 varchar2 (240),
reference3 varchar2 (240),
reference4 varchar2 (240),
reference5 varchar2 (240),
currency_code varchar2 (30),
currency_conversion_date date,
currency_conversion_rate number,
currency_conversion_type varchar2 (30),
attribute1 varchar2 (50),
attribute2 varchar2 (50),
attribute3 varchar2 (50),
attribute4 varchar2 (50),
attribute5 varchar2 (50),
je_line_num number,
line_description varchar2 (240)
);

type dm_gl_ledger_rec_tb_type is table of dm_gl_ledger_rec_type index by binary_integer;

type dm_gl_ledger_out_rec_type is record
(
ramco_reference_id Number,
status varchar2(2),
message varchar2 (4000)
);

type dm_gl_ledger_out_tb_type is table of dm_gl_ledger_out_rec_type index by binary_integer;

procedure xxdm_gl_ledger_send_mail_p (
p_batch_name in varchar2,
p_ramco_reference_id in varchar2,
p_message out varchar2);

--Program for Insert validated record into standard table through API
procedure xxdm_gl_ledger_intg_p (
p_dm_gl_ledger_rec_main_tb in dm_gl_ledger_rec_tb_type,
p_dm_gl_ledger_rec_out_tb out dm_gl_ledger_out_tb_type,
p_status out varchar2,
p_message out varchar2);
end xxdm_gl_ledger_intg_pkg;
/

CREATE OR REPLACE package body APPS.xxdm_gl_ledger_intg_pkg
as

-----------Send mail Procedure-------------------
procedure xxdm_gl_ledger_send_mail_p (
p_batch_name in varchar2,
p_ramco_reference_id in varchar2,
p_message out varchar2)
is

l_Mail_Conn utl_smtp.Connection;

crlf VARCHAR2(2) ;
slen NUMBER := 1;
l_From VARCHAR2(80) := 'aa@com';
l_Recipient VARCHAR2(1000) :='bb@com';
l_Recipient_src VARCHAR2(1000);
l_Recipient_fin VARCHAR2(1000);
l_Subject VARCHAR2(1000);
l_Err_Msg VARCHAR2(4000);
l_Msg VARCHAR2(4000);
l_Mail_Host VARCHAR2(30) := 'localhost'
v_error_msg VARCHAR2(100);
l_bcc_Recipient VARCHAR2(80):='xx@.com';

begin

begin

l_Subject:= 'New Payroll JV '||p_batch_name;-- 'Run GL Import with Batch Name :- '||p_batch_name;

L_ERR_MSG := NULL;
CRLF := CHR(13)||CHR(10);
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(L_MAIL_HOST, 25);

L_MSG := 'Date: ' ||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || CRLF ||
'From: <'|| L_FROM ||'>' || CRLF ||
'Subject: '||L_SUBJECT || CRLF ||
'To: '||L_RECIPIENT || CRLF || '' || CRLF || 'Dear GL Team,'||CHR(13)||' A new journal('||p_batch_name||') is transferred from payroll for posting. Please do the needful.'||CHR(13)||'Regards,'||CHR(13)||'Payroll Team';

UTL_SMTP.HELO(L_MAIL_CONN, 'daman.ae');
UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);

L_RECIPIENT_SRC := REPLACE (L_RECIPIENT, ' ', '');

IF (INSTR (L_RECIPIENT, ',') = 0) THEN
UTL_SMTP.RCPT(L_MAIL_CONN, L_RECIPIENT_SRC);
ELSE
L_RECIPIENT_SRC := REPLACE (L_RECIPIENT, ' ', '') || ',';

WHILE (INSTR (L_RECIPIENT_SRC, ',', SLEN) > 0)
LOOP
L_RECIPIENT_FIN :=
SUBSTR (L_RECIPIENT_SRC,
SLEN,
INSTR (SUBSTR (L_RECIPIENT_SRC, SLEN), ',') - 1);
SLEN := SLEN + INSTR (SUBSTR (L_RECIPIENT_SRC, SLEN), ',');
UTL_SMTP.RCPT(L_MAIL_CONN, L_RECIPIENT_FIN);
END LOOP;
END IF;

UTL_SMTP.RCPT(L_MAIL_CONN, L_BCC_RECIPIENT);
--utl_smtp.Data(l_Mail_Conn, l_Msg);
UTL_SMTP.OPEN_DATA (L_MAIL_CONN);
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, L_MSG);
UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
UTL_SMTP.QUIT(L_MAIL_CONN);
DBMS_OUTPUT.PUT_LINE('Success : ');

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
UTL_SMTP.QUIT(L_MAIL_CONN);
L_ERR_MSG:= SUBSTR('SMTP invalid_operation, ERROR : '||SQLERRM,1,3999);
DBMS_OUTPUT.PUT_LINE('ERROR : '||L_ERR_MSG);

WHEN UTL_SMTP.TRANSIENT_ERROR THEN
UTL_SMTP.QUIT(L_MAIL_CONN);
L_ERR_MSG:= SUBSTR('SMTP transient_error, ERROR : '||SQLERRM,1,3999);
DBMS_OUTPUT.PUT_LINE('ERROR : '||L_ERR_MSG);

WHEN UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.QUIT(L_MAIL_CONN);
L_ERR_MSG:= SUBSTR('SMTP permanent_error, ERROR : '||SQLERRM,1,3999);
DBMS_OUTPUT.PUT_LINE('ERROR : '||L_ERR_MSG);

WHEN OTHERS THEN
UTL_SMTP.QUIT(L_MAIL_CONN);
L_ERR_MSG:= SUBSTR('SMTP unknown_exception, ERROR :'||SQLERRM,1,3999);
DBMS_OUTPUT.PUT_LINE('ERROR : '||L_ERR_MSG);

end;

p_message:=L_ERR_MSG;

end xxdm_gl_ledger_send_mail_p;

--Program for Insert validated record into standard table through API
procedure xxdm_gl_ledger_intg_p (
p_dm_gl_ledger_rec_main_tb in dm_gl_ledger_rec_tb_type,
p_dm_gl_ledger_rec_out_tb out dm_gl_ledger_out_tb_type,
p_status out varchar2,
p_message out varchar2)
is
lv_code_combination_id number;
lv_user_je_source_name varchar2 (240);
lv_period_name varchar2 (150);
lv_cur_code varchar2 (30);
lv_status varchar2 (2) := 'S';
lv_message varchar2 (4000);
lv_main_error varchar2 (4000);
l_request_id number;
l_err_cnt number;
lv_email_message varchar2 (4000);
lv_batch_name varchar2 (240);
lv_records Number;
lv_period_name1 varchar2(240);
begin

l_err_cnt:=0;
lv_records:=p_dm_gl_ledger_rec_main_tb.count;

for h in p_dm_gl_ledger_rec_main_tb.first ..
p_dm_gl_ledger_rec_main_tb.last
loop
lv_status:=null;
lv_message :=null;
lv_cur_code:=null;
lv_code_combination_id:=null;
lv_period_name:=null;
lv_period_name1 :=null;
-- --Validate Journal name
-- begin
-- select user_je_source_name
-- into lv_user_je_source_name
-- from gl_je_sources
-- where user_je_source_name =
-- p_dm_gl_ledger_rec_main_tb (h).journal_name;
-- exception
-- when no_data_found
-- then
-- lv_status := 'E';
-- lv_message := lv_message || ' Journal Name Not Found ';
-- when others
-- then
-- lv_status := 'E';
-- lv_message := lv_message || ' Error At Journal Name ';
-- end;

-- Validate Currency Code
begin
select currency_code
into lv_cur_code
from fnd_currencies
where currency_code =
p_dm_gl_ledger_rec_main_tb (h).currency_code;
exception
when no_data_found
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message := lv_message || ' Currency Code Not Found '||p_dm_gl_ledger_rec_main_tb (h).currency_code;
when others
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message := lv_message || ' Error At Currency Code '||p_dm_gl_ledger_rec_main_tb (h).currency_code;
end;

--Validate Code combinitions
begin
select code_combination_id
into lv_code_combination_id
from gl_code_combinations_kfv
where concatenated_segments = trim (p_dm_gl_ledger_rec_main_tb(h).segment1||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment2||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment3||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment4||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment5||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment6||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment7)
and chart_of_accounts_id =101;
exception
when no_data_found
then
l_err_cnt:=l_err_cnt+1;
lv_status:='E';
lv_message := lv_message || ' Code Combination not Found '||trim (p_dm_gl_ledger_rec_main_tb(h).segment1||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment2||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment3||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment4||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment5||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment6||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment7);
when others
then
l_err_cnt:=l_err_cnt+1;
lv_status:='E';
lv_message := lv_message || ' Error At Code Combination '||trim (p_dm_gl_ledger_rec_main_tb(h).segment1||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment2||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment3||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment4||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment5||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment6||'-'||
p_dm_gl_ledger_rec_main_tb(h).segment7);
end;

--Validate Period
if p_dm_gl_ledger_rec_main_tb (h).accounting_date is not null
then
begin
select period_name
into lv_period_name
from apps.gl_period_statuses
where application_id = 222
and ledger_id = 1001
and adjustment_period_flag <> 'Y'
and closing_status in ('O', 'F') --OPEN and Future - Entry
and trunc (
p_dm_gl_ledger_rec_main_tb (h).accounting_date) between start_date
and end_date;
exception
when no_data_found
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message :=
lv_message || ' Accounting Date Period not Opened '||p_dm_gl_ledger_rec_main_tb (h).accounting_date;
when others
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message := lv_message || ' Error at Accounting Date '||p_dm_gl_ledger_rec_main_tb (h).accounting_date;
end;
else
lv_message := lv_message || ' Accounting Date not provided';
end if;

if p_dm_gl_ledger_rec_main_tb (h).period_name is null then

l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message :=
lv_message || ' Period name Not provided ';
else
begin
select period_name
into lv_period_name1
from apps.gl_period_statuses
where application_id = 222
and ledger_id = 1001
and adjustment_period_flag <> 'Y'
and closing_status in ('O', 'F') --OPEN and Future - Entry
and upper(PERIOD_NAME)=upper(p_dm_gl_ledger_rec_main_tb (h).period_name);

exception
when no_data_found
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message :=
lv_message || ' Provided Period not Opened '||p_dm_gl_ledger_rec_main_tb (h).period_name;
when others
then
l_err_cnt:=l_err_cnt+1;
lv_status := 'E';
lv_message := lv_message || ' Error at Period Name '||p_dm_gl_ledger_rec_main_tb (h).period_name;
end;

end if;

-- Validate entered_dr and entered_cr
if p_dm_gl_ledger_rec_main_tb (h).entered_dr is null
and p_dm_gl_ledger_rec_main_tb (h).entered_cr is null
then
l_err_cnt:=l_err_cnt+1;
lv_message :=
lv_message || ' Please provide Entered_cr or Entered_Dr';
end if;

if p_dm_gl_ledger_rec_main_tb (h).entered_dr is not null
and p_dm_gl_ledger_rec_main_tb (h).entered_cr is not null
then
l_err_cnt:=l_err_cnt+1;
lv_message :=
lv_message
|| ' Please provide Either Entered_cr or Entered_Dr';
end if;

if lv_message is null
then
insert into gl_interface (status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
actual_flag,
accounting_date,
date_created,
period_name,
reference1, --It goes to Batch Name
created_by,
attribute1,
je_line_num,
code_combination_id,
entered_dr,
entered_cr,
reference10, --It goes to Line level Description
reference21) --It goes to Line level Reference1
values ('NEW',
1001,
p_dm_gl_ledger_rec_main_tb (h).source_name,
p_dm_gl_ledger_rec_main_tb (h).category_name,
lv_cur_code,
'A',
p_dm_gl_ledger_rec_main_tb (h).accounting_date,
sysdate,
p_dm_gl_ledger_rec_main_tb(h).period_name,
p_dm_gl_ledger_rec_main_tb (h).batch_name, --Batch Name is a reference1
0,
p_dm_gl_ledger_rec_main_tb (h).attribute1,
p_dm_gl_ledger_rec_main_tb (h).je_line_num,
lv_code_combination_id,
p_dm_gl_ledger_rec_main_tb (h).entered_dr,
p_dm_gl_ledger_rec_main_tb (h).entered_cr,
p_dm_gl_ledger_rec_main_tb (h).line_description, --line_description is a reference10
p_dm_gl_ledger_rec_main_tb (h).ramco_reference_id); --ramco_reference_id is a reference21

lv_batch_name:=p_dm_gl_ledger_rec_main_tb (h).batch_name;

-- p_dm_gl_ledger_rec_out_tb(h).ramco_reference_id:=p_dm_gl_ledger_rec_main_tb (h).ramco_reference_id;
-- p_dm_gl_ledger_rec_out_tb(h).status:='S';
-- p_dm_gl_ledger_rec_out_tb(h).message:=lv_message;

else
p_dm_gl_ledger_rec_out_tb(h).ramco_reference_id:=p_dm_gl_ledger_rec_main_tb (h).ramco_reference_id;
p_dm_gl_ledger_rec_out_tb(h).status:='E';
p_dm_gl_ledger_rec_out_tb(h).message:=lv_message;

lv_status:='E';
lv_main_error:=lv_main_error||lv_message||' For ramco Reference Id is :- '|| p_dm_gl_ledger_rec_main_tb (h).ramco_reference_id;

end if;
end loop;

if lv_main_error is not null then
rollback;
p_status := 'E';
p_message :='ERROR';
else
null;
p_status :='S';
p_message :='Success';

begin
xxdm_gl_ledger_send_mail_p (lv_batch_name,null,lv_email_message);
exception
when others then
Null;
end;
end if;

--
--
-- --Call Concurrect program to create Generals
-- begin
-- l_request_id :=
-- fnd_request.submit_request (application => 'SQLGL',
-- program => 'GLLEZLSRS',
-- description => null,
-- start_time => sysdate,
-- sub_request => false,
-- argument1 => 1000, --Data Access Set ID
-- argument2 => 41, -- source
-- argument3 => 1001, -- Ledger
-- argument4 => null, -- Group ID
-- argument5 => 'N', -- Post Errors to Suspense
-- argument6 => 'N', -- Create Summary Journals
-- argument7 => 'N' -- Import Descriptive Flexfields
-- );
--
--
-- if l_request_id = 0
-- then
-- p_status := 'E';
-- p_message :=
-- 'Error While Submitting Concurrent Request '
-- || to_char (sqlcode)
-- || '-'
-- || sqlerrm;
-- else
-- p_status := 'S';
-- p_message :=
-- 'Request Submitted the Concurrent program id is '
-- || l_request_id;
-- commit;
-- end if;
-- exception
-- when others
-- then
-- p_status := 'E';
-- p_message :=
-- 'Error While Submitting Concurrent Request '
-- || to_char (sqlcode)
-- || '-'
-- || sqlerrm;
-- end;
end xxdm_gl_ledger_intg_p;
end xxdm_gl_ledger_intg_pkg;
/