Author: Not specified Language: sql
Description: Not specified Timestamp: 2017-08-18 08:43:52 +0000
View raw paste Reply
  1. CREATE OR REPLACE PROCEDURE TAC_FIX_JOB_LEVEL(
  2.     errbuf OUT VARCHAR2,
  3.     retcode OUT NUMBER)
  4. AS
  5.  
  6. CURSOR ERR_JOB_LEVEL_LIST
  7. IS
  8. SELECT *
  9. FROM
  10.    (SELECT EMPLOYEE_NUMBER,
  11.     LAST_NAME,
  12.     FIRST_NAME,
  13.     CENTRAL_EMP_JOB,
  14.     JOB_ID,
  15.     USER_LAN,
  16.     JOB_NAME,
  17.     ASSIGNMENT_ID,
  18.     EFFECTIVE_START_DATE,
  19.     EFFECTIVE_END_DATE,
  20.     SQLSTATEMENT
  21.   FROM
  22.     (SELECT e.employee_number,
  23.     e.last_name,
  24.     e.first_name,
  25.     e.job CENTRAL_EMP_JOB,
  26.     j.job_id,
  27.     e.user_lan ,
  28.     b.job_name,
  29.     b.ASSIGNMENT_ID,
  30.     b.EFFECTIVE_START_DATE,
  31.     b.EFFECTIVE_END_DATE,
  32.     'update PER_ALL_ASSIGNMENTS_F set job_id='
  33.     || j.job_id
  34.     || ' where ASSIGNMENT_ID='
  35.     || b.ASSIGNMENT_ID
  36.     || ' and EFFECTIVE_START_DATE=to_date('''
  37.     || TO_CHAR(EFFECTIVE_START_DATE,'dd/MM/yyyy')
  38.     || ''',''dd/MM/yyyy'') '
  39.     || ' and EFFECTIVE_END_DATE=to_date('''
  40.     || TO_CHAR(EFFECTIVE_END_DATE,'dd/MM/yyyy')
  41.     || ''',''dd/MM/yyyy'') ' AS SQLStatement
  42.     FROM DTAC_EMP_INBOUND_INT e
  43.     LEFT JOIN
  44.     (SELECT a.last_name,
  45.     a.first_name,
  46.     a.full_name,
  47.     a.employee_number,
  48.     b.ASSIGNMENT_ID,
  49.     b.EFFECTIVE_START_DATE,
  50.     b.EFFECTIVE_END_DATE ,
  51.     c.name job_name
  52.     FROM PER_ALL_PEOPLE_F a
  53.     INNER JOIN PER_ALL_ASSIGNMENTS_F b
  54.     ON a.person_id= b.person_id
  55.     AND b.effective_start_date <= sysdate
  56.     AND (b.effective_end_date >= sysdate
  57.     OR b.effective_end_date IS NULL)
  58.     INNER JOIN PER_JOBS c
  59.     ON b.job_id = c.job_id
  60.     ) b ON e.employee_number = b.employee_number
  61.     INNER JOIN PER_JOBS j
  62.     ON e.job = j.name
  63.     WHERE e.job <> b.job_name
  64.     )
  65. ) WHERE rownum <= 3;
  66.    
  67.     JOB_LIST ERR_JOB_LEVEL_LIST%ROWTYPE;
  68.     CNT NUMBER := 0;
  69.     /* Mailing Details */
  70.     v_From VARCHAR2(80) := 'ERPOperationSupport@dtac.co.th'; --'EnterpriseApplicationSupportBOSDept@dtac.co.th';
  71.     --v_Recipient VARCHAR2(80) := 'ERPOperationSupport@dtac.co.th';
  72.     v_Recipient VARCHAR2(80) := 'ERPOperationSupport@dtac.co.th'; -- to be parameters
  73.     v_Subject  VARCHAR2(100) := 'Fix Job Level of '||SYSDATE;
  74.     v_Mail_Host  VARCHAR2(30)  := 'mail-gw.tac.co.th';
  75.     -- v_Cc1    VARCHAR2(100) := '';
  76.     -- v_Cc2    VARCHAR2(100) := '';
  77.     --v_Cc2    VARCHAR2(100) := 'IS-ERP-Production@dtac.co.th';
  78.     --v_Cc3    VARCHAR2(100) := 'IS-ERP-Financial@dtac.co.th';
  79.     v_Mail_Conn utl_smtp.Connection;
  80.     crlf      VARCHAR2(2)   := CHR(10); --chr(13)||chr(10);
  81.     v_oper    VARCHAR2(100) := 'ERP Operation Support Team'; -- to be parameter
  82.     p_message VARCHAR2(30000);
  83.     s_subject VARCHAR2(30000);
  84.    
  85.  
  86. BEGIN
  87.  
  88.     p_message := UTL_TCP.crlf || 'I---------------------------------------------------------------I' || UTL_TCP.crlf ;
  89.     p_message := p_message || ' Fix Incorrect Job Level... ' || UTL_TCP.crlf;
  90.     p_message := p_message || 'I---------------------------------------------------------------I' || UTL_TCP.crlf;
  91.     fnd_file.put_line(fnd_file.log,'I---------------------------------------------------------------I');
  92.     fnd_file.put_line(fnd_file.log,' Fix Incorrect Job Level... ');
  93.     fnd_file.put_line(fnd_file.log,'I---------------------------------------------------------------I');
  94.    
  95.     p_message := p_message || 'EMPLOYEE_NUMBER  LAST_NAME       FIRST_NAME      CENTRAL_EMP_JOB JOB_ID  USER_LAN  JOB_NAME' || UTL_TCP.crlf ;
  96.     fnd_file.put_line(fnd_file.log,'EMPLOYEE_NUMBER     LAST_NAME       FIRST_NAME      CENTRAL_EMP_JOB JOB_ID  USER_LAN JOB_NAME');
  97.    
  98.     OPEN ERR_JOB_LEVEL_LIST;
  99.    
  100.     LOOP
  101.       FETCH ERR_JOB_LEVEL_LIST INTO JOB_LIST;
  102.       EXIT
  103.       WHEN ERR_JOB_LEVEL_LIST%NOTFOUND;
  104.       CNT := CNT + 1;
  105.       p_message := p_message || JOB_LIST.EMPLOYEE_NUMBER || chr(9) || JOB_LIST.LAST_NAME || chr(9) || JOB_LIST.FIRST_NAME || chr(9) || JOB_LIST.CENTRAL_EMP_JOB || chr(9) || JOB_LIST.JOB_ID || chr(9) || JOB_LIST.USER_LAN || chr(9) || JOB_LIST.JOB_NAME || UTL_TCP.crlf ;
  106.       fnd_file.put_line(fnd_file.log,JOB_LIST.EMPLOYEE_NUMBER || chr(9) || JOB_LIST.LAST_NAME || chr(9) || JOB_LIST.FIRST_NAME || chr(9) || JOB_LIST.CENTRAL_EMP_JOB || chr(9) || JOB_LIST.JOB_ID || chr(9) || JOB_LIST.USER_LAN || chr(9) || JOB_LIST.JOB_NAME);
  107.          
  108.                 BEGIN
  109.                         EXECUTE IMMEDIATE JOB_LIST.SQLSTATEMENT;
  110.                 END;
  111.     END LOOP;
  112.    
  113.     CLOSE ERR_JOB_LEVEL_LIST;
  114.    
  115.    
  116.    IF CNT > 0 THEN
  117.     v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
  118.     utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
  119.     utl_smtp.Mail(v_Mail_Conn, v_From);
  120.     utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
  121.     UTL_SMTP.open_data(v_Mail_Conn);
  122.    
  123.         UTL_SMTP.WRITE_DATA(v_Mail_Conn, 'Content-Type: text;charset=Windows-874' || UTL_TCP.CRLF);
  124.        
  125.     UTL_SMTP.write_data(v_Mail_Conn, 'Date: ' || TO_CHAR(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Asia/Bangkok'),'Dy, DD Mon YYYY hh24:mi:ss TZHTZM','NLS_DATE_LANGUAGE=AMERICAN') || UTL_TCP.crlf);
  126.     UTL_SMTP.write_data(v_Mail_Conn, 'To: ' || v_Recipient || UTL_TCP.crlf);
  127.     UTL_SMTP.write_data(v_Mail_Conn, 'From: ' || v_From || UTL_TCP.crlf);
  128.    
  129.     UTL_SMTP.write_data(v_Mail_Conn, 'Subject: ' || v_Subject || '  [' || CNT  || '] transactions have been fixed' || UTL_TCP.crlf);
  130.     utl_smtp.write_raw_data(v_Mail_Conn, utl_raw.cast_to_raw(p_message || UTL_TCP.crlf || UTL_TCP.crlf || UTL_TCP.crlf));
  131.    
  132.     UTL_SMTP.write_data(v_Mail_Conn, 'Request ID : ' || FND_GLOBAL.CONC_REQUEST_ID);
  133.     UTL_SMTP.write_data(v_Mail_Conn, crlf || crlf || crlf || 'Best Regards,'|| crlf || crlf || v_oper|| crlf || crlf || 'Date: ' || TO_CHAR(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss'));
  134.     UTL_SMTP.close_data(v_Mail_Conn);
  135.     UTL_SMTP.quit(v_Mail_Conn);
  136.    
  137.     END IF;
  138.    
  139.     EXCEPTION
  140.     WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error THEN
  141.     raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
  142.    
  143.    
  144.   END;
View raw paste Reply