Validate email in PL/SQL

 create or replace function "CHECK_EMAIL"
(l_email IN VARCHAR2)
  RETURN VARCHAR2 IS

  l_dot_pos    NUMBER;
  l_at_pos     NUMBER;
  l_str_length NUMBER;
BEGIN
  l_dot_pos    := instr(l_email
                       ,'.');
  l_at_pos     := instr(l_email
                       ,'@');
  l_str_length := length(l_email);
  IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
     (l_at_pos = 1) OR (l_at_pos = l_str_length) OR
     (l_dot_pos = l_str_length))
  THEN
    RETURN 'FAILURE';
  END IF;
  IF instr(substr(l_email
                 ,l_at_pos)
          ,'.') = 0
  THEN
    RETURN 'FAILURE';
  END IF;
  RETURN 'SUCCESS';
end "CHECK_EMAIL";
/

 

Can use in Select query:

SELECT CHECK_EMAIL(your_email@gmail.com) from dual;

 

oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException Errors In Oracle Apps R12.2.5

For this error need to Check RTF template there should be tags are not correctly and if you are developing new  RTF latest version. Just select there  Backward Compatible  option and do new RTF with new tags. You will get resolve the error. Please follow screen shots for reference.

+--------- 1) POST-PROCESSING  ---------+
Beginning post-processing of request 1544251 on node  at 13-JUL-2016 18:26:30.
-- Publishing output

 
Post-processing of request 1544251 failed at 13-JUL-2016 18:26:31 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
 
CONC-POST-PROCESSING RESULTS

-- PUBLISH:
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException
+---------------------------------------+

invoice approved but didn't imported to ebs

Invoice approved but didn't Imported to EBS R12

 The following query give the status of the invoice, in our case its REJECTED.

select attribute3,status, i.* from ap_invoices_interface i where attribute3 = 1234;

 The following query give the status of the invoice interface, in our case 'DUPLICATE INVOICE_NUM'

SELECT apr.*
  FROM ap_interface_rejections apr,
       ap_invoices_interface inv
 WHERE apr.parent_id = inv.invoice_id
   AND inv.status='REJECTED'
   and inv.attribute3 in (1234)
   --AND inv.source='GLOBAL_INTERCOMPANY'
UNION
SELECT apr.*
  FROM ap_interface_rejections apr,
       ap_invoices_interface inv,
       ap_invoice_lines_interface ln
 WHERE apr.parent_id =ln.invoice_line_id
   AND ln.invoice_id= inv.invoice_id
   AND inv.status='REJECTED'
   AND inv.source='GLOBAL_INTERCOMPANY' ;

Solution:

Invoice_num = ABC/Lot8

The query show 2 records with this invoice_num means 1 record was already exist, so this invoice is rejected in import / interface.

I have added 01 at the end of the invoice.

update ap_invoices_interface

set invoice_num='ABC/Lot8)01'

where invoice_id in (1234);

After update rerun the “Payables Open Interface Import” request.

Now the status is changed to "PROCESSED".