Oracle Apps, Oracle APEX and PL/SQL: 2019

Seq: 20
Description: New Record Group for Tax category
Level: Form
Enabled: checked
Trigger Event: WHEN-NEW-FORM-INSTANCE
Processing Mode: Not in Enter-Query Mode

Click on Actions and enter the following.
Seq: 10
Type:Builtin
Language: all
Builtin type: Create Record Group from Query
Argument: Paste your new SQL statement here. In this case it would be
SELECT tax_category_name,
 tax_category_desc,
 tax_category_id
FROM jai_tax_categories jtc
WHERE org_id = :HEADER.org_id
 AND ( CASE
 WHEN Substr(Get_gstin_no_po_fp(:HEADER.trx_id,
 :HEADER.org_id), 1, 2) <>
 Substr(
 Get_gstin_no_po_tp(:HEADER.trx_id,
 :HEADER.org_id), 1, 2)
 AND ( Instr(tax_category_name, 'EXPORT')
 + Instr(tax_category_name, 'INTER_STATE')
 + Instr(tax_category_name, 'IMPORT') > 0 ) THEN 1
 WHEN Substr(Get_gstin_no_po_fp(:HEADER.trx_id,
 :HEADER.org_id), 1, 2) =
 Substr
 (
 Get_gstin_no_po_tp(:HEADER.trx_id,
 :HEADER.org_id), 1, 2)
 AND ( Instr(tax_category_name, 'WITHIN_STATE')
 + Instr(tax_category_name, 'DOMESTIC') > 0 ) THEN 1
 WHEN ( Instr(tax_category_name, 'EXPORT')
 + Instr(tax_category_name, 'INTER_STATE')
 + Instr(tax_category_name, 'IMPORT')
 + Instr(tax_category_name, 'WITHIN_STATE')
 + Instr(tax_category_name, 'DOMESTIC') = 0 ) THEN 1
 ELSE 0
 END ) = 1
 AND Trunc(SYSDATE) BETWEEN Trunc(effective_from) AND Trunc(
 Nvl(effective_to, SYSDATE + 1))
 AND NOT EXISTS (SELECT 'TDS Rate Exists in Category'
 FROM jai_tax_category_lines jtcl,
 jai_regimes jr,
 jai_tax_types jtt
 WHERE jtcl.tax_category_id = jtc.tax_category_id
 AND jtcl.regime_id = jr.regime_id
 AND jtcl.tax_type_id = jtt.tax_type_id
 AND jr.regime_type = 'W'
 AND Nvl(jtt.wthld_trx_applicable_flag, 'N') = 'Y')
ORDER BY tax_category_name

Group Name: TEST_GROUP

Now, for below Tax Category LOV, will create a new Personalization:

Now Create New Record with below information
Seq:10
Type: Property
Language: All
Enabled: Checked
Object Type: LOV
Target Object: TAX_CATEGORIES
Property Name: GROUP_NAME
Value: TEST_GROUP

Same for below Tax Category LOV :

Save it.

Click Validate

Click Apply Now.

Go to tools Menu >> click Validate All

Once it is successfully validated you are ready to go.

Click ok and close personalization form.

Close your Transactions form completely.

Open it again and Query the same record as before.

Check for the change in the LOV.

Sample Code 1 :
DECLARE
 psender VARCHAR2(4000) := '<sender_mail_id>';
 precipient VARCHAR2(4000) := '<recipient_mail_id>';
 psubject VARCHAR2(4000) := 'Mail Test Subject';
 c utl_smtp.connection;
 pmessage VARCHAR2(4000) := '<!doctype html>

    <html>

    <head>

      <title>Test HTML message</title>

    </head>

    <body>

      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>

      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />

    </body>

</html>';

 PROCEDURE Send_header(name VARCHAR2, header VARCHAR2) AS
 BEGIN
 utl_smtp.Write_data(c, name||':'|| header|| utl_tcp.crlf);
 END;

BEGIN
 --c := utl_smtp.Open_connection('smtp-relay.gmail.com');
 c := utl_smtp.Open_connection(host => 'smtp-relay.gmail.com', port => 25);

 utl_smtp.Helo(c, '<SMTP host name>'); -- utl_smtp.helo –> introduce yourself

 utl_smtp.Mail(c, psender); -- utl_smtp.MAIL –> specify the sender

 utl_smtp.Rcpt(c, precipient); -- utl_smtp.RCPT –> specify the recipient

 utl_smtp.Open_data(c);

 Send_header('From', '"Sender" <'||psender||'>');

 Send_header('To', '"Recipient" <'||precipient||'>');

 Send_header('Subject', psubject);

 utl_smtp.Write_data(c, 'MIME-Version: 1.0'
 || utl_tcp.crlf
 || 'Content-type: text/html'
 || utl_tcp.crlf
 || pmessage);

 utl_smtp.Close_data(c);

 utl_smtp.Quit(c);

EXCEPTION
 WHEN utl_smtp.invalid_operation THEN
dbms_output.Put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
WHEN utl_smtp.transient_error THEN
dbms_output.Put_line(' Temporary e-mail issue - try again');
WHEN utl_smtp.permanent_error THEN
dbms_output.Put_line(' Permanent Error Encountered.');
/*WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
 utl_smtp.Quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 dbms_output.Put_line(' Error Encountered.'
 ||SQLERRM);
 END;

Raise_application_error(-20000, SQLERRM); */
END;

Sample Code 2 :
DECLARE
 psender VARCHAR2(4000) := '<sender_mail_id>';
 precipient VARCHAR2(4000) := '<recipient_mail_id>';
 precipient2 VARCHAR2(4000) := '<recipient_mail_id_2>';
 psubject VARCHAR2(4000) := 'Mail Test Subject';
 pbody VARCHAR2(10000);
 crlf VARCHAR2(2) := Chr(13)||Chr(10); --can be replaced by utl_tcp.crlf
 c utl_smtp.connection;
 v_mail_host VARCHAR2(80) := '<SMTP host name>'; --SMTP host name
 pmessage VARCHAR2(4000) := '<!doctype html>

    <html>

    <head>

      <title>Test HTML message</title>

    </head>

    <body>

      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>

      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />

    </body>

</html>';
BEGIN
 c := utl_smtp.Open_connection(v_mail_host, 25); --25 is SMTP Port Number

 utl_smtp.Helo(c, v_mail_host); -- utl_smtp.helo –> introduce yourself

 utl_smtp.Mail(c, psender); -- utl_smtp.MAIL –> specify the sender

 utl_smtp.Rcpt(c, precipient); -- utl_smtp.RCPT –> specify the recipient

 utl_smtp.Open_data(c);

 pbody := 'Date: ' || To_char(SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: ' || psender || crlf ||
'Subject: ' ||psubject || crlf ||
'To: ' || precipient || crlf ||
'CC: ' || precipient2 || crlf ||
pmessage || '';

 utl_smtp.Write_data(c, 'MIME-Version: 1.0' || utl_tcp.crlf ||
'Content-type: text/html' || utl_tcp.crlf || pbody);

 utl_smtp.Close_data(c);

 utl_smtp.Quit(c);
EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
 utl_smtp.Quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 dbms_output.Put_line(' Error Encountered.'
 ||SQLERRM);
 END;

 Raise_application_error(-20000, SQLERRM);
END;

Sample Code 3 :Emails with BLOB Attachments

DECLARE
 p_to VARCHAR2(200) := 'abhishek.bajpai@xxx.co.in';
 p_from VARCHAR2(200) := 'no-reply@xxx.co.in';
 p_subject VARCHAR2(500) := 'Mail Test Subject';
 p_text_msg VARCHAR2(1000) :=
'<!doctype html> <html> <head> <title>Test HTML message</title> </head> <body> This is a HTML version of the test message. Thanks, Abhishek, </body> </html>'
 ;
 p_attach_name VARCHAR2(200) := NULL;
 --'Format of CA certificate for MSME.docx';
 p_attach_mime VARCHAR2(200) := NULL;
 --'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
 p_attach_blob BLOB := NULL;
 p_smtp_host VARCHAR2(80) := 'smtp-relay.gmail.com'; --SMTP host nam
 p_smtp_port NUMBER := 25;
 l_mail_conn utl_smtp.connection;
 l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
 l_step PLS_INTEGER := 57;
BEGIN
 /*CREATE TABLE XX_blob_table
 (
 id_num NUMBER,
 FILE_NAME VARCHAR2 (256),
 FILE_CONTENT_TYPE VARCHAR2 (256),
 blob_text BLOB
 );

 Insert into XX_blob_table
 select FILE_ID, FILE_NAME, FILE_CONTENT_TYPE, FILE_DATA
 from fnd_lobs
 WHERE FILE_NAME = 'Format of CA certificate for MSME.docx'; */
 SELECT file_name,
 file_content_type,
 blob_text
 INTO p_attach_name, p_attach_mime, p_attach_blob
 FROM xx_blob_table
 WHERE id_num = 14156456;

 l_mail_conn := utl_smtp.Open_connection(p_smtp_host, p_smtp_port);

 utl_smtp.Helo(l_mail_conn, p_smtp_host);

 utl_smtp.Mail(l_mail_conn, p_from);

 utl_smtp.Rcpt(l_mail_conn, p_to);

 utl_smtp.Open_data(l_mail_conn);

 utl_smtp.Write_data(l_mail_conn, 'Date: '
 ||
 To_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'To: '
 || p_to
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'From: '
 || p_from
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Subject: '
 || p_subject
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Reply-To: '
 || p_from
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'MIME-Version: 1.0'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="'
 || l_boundary
 || '"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 IF p_text_msg IS NOT NULL THEN
 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn,
 'Content-Type: text/html; charset="iso-8859-1"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, p_text_msg);

 utl_smtp.Write_data(l_mail_conn, utl_tcp.crlf
 || utl_tcp.crlf);
 END IF;

 IF p_attach_name IS NOT NULL THEN
 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Type: '
 || p_attach_mime
 || '; name="'
 || p_attach_name
 || '"'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Transfer-Encoding: base64'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn,
 'Content-Disposition: attachment; filename="'
 || p_attach_name
 || '"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 FOR i IN 0 .. Trunc((dbms_lob.Getlength(p_attach_blob) - 1 )/l_step) LOOP
 utl_smtp.Write_data(l_mail_conn, utl_raw.Cast_to_varchar2(
 utl_encode.Base64_encode(
 dbms_lob.Substr(p_attach_blob,
 l_step
 , i * l_step + 1)))
 || utl_tcp.crlf);
 END LOOP;

 utl_smtp.Write_data(l_mail_conn, utl_tcp.crlf);
 END IF;

 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || '--'
 || utl_tcp.crlf);

 utl_smtp.Close_data(l_mail_conn);

 utl_smtp.Quit(l_mail_conn);
END;

Oracle Apps, Oracle APEX and PL/SQL

Sunday, 17 November 2019

Form Personalization to modify the LOV of Tax Category Filed in Purchase Order India Localization Form

Thursday, 17 October 2019

UTL_SMTP API to send HTML Emails from PL/SQL

About Me