×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Jon Nickerson
Added: Nov 28, 2017 2:34 PM
Modified: Nov 28, 2017 2:49 PM
Views: 0
Tags: no tags
  1.     /* ********************************************************************** */
  2.     /* *********  LIBERTY UNIVERSITY - Business Intelligence Office ********* */
  3.     /* *********  OBJECT NAME: UTL_D_RES.Midday_Apps                ********* */
  4.     /* *********  DESCRIPTION: Nightly Append                       ********* */
  5.     /* *********  CREATED BY: Joshua McArdle                        ********* */
  6.     /* *********  UPDATED BY: Jon Nickerson                         ********* */
  7.     /* *********  (See CHANGE LOG at bottom of file)                ********* */
  8.     /* ********************************************************************** */
  9.    
  10.  l_dbop_eid NUMBER;
  11.     opname     VARCHAR2(30) := '';--'MIDDAY_DEPS_APPEND';
  12.     insertct      INT := 0;  
  13. CURSOR C1 IS
  14.  
  15. c1fmt      c1%ROWTYPE;
  16. BEGIN
  17.  
  18.   l_dbop_eid := dbms_sql_monitor.begin_operation(dbop_name => opname,
  19.                                                      forced_tracking => dbms_sql_monitor.force_tracking);
  20.       OPEN C1;
  21.       FETCH C1 INTO c1fmt;
  22.       WHILE c1%FOUND LOOP
  23.  
  24.     ----INSERT, DELETE, UPDATE ACTION HERE
  25.              
  26.  
  27. insertct := insertct + 1;
  28.  
  29.       END LOOP;
  30.       CLOSE C1;
  31.       COMMIT;
  32.       ---ASK THE ETL MANAGEMENT GROUP ABOUT WHAT SHOULD BE IN THESE FIELDS
  33.       utl_d_bio.ads_common_tools.dwebgenlog_insert(
  34.           p_process_id    => --'etlj_res_midday_apps_append_' || to_char(sysdate, 'yyyymmddhh24miss'),
  35.           p_process_name      => --'ETLJ_RES_MIDDAY_APPS_APPEND',
  36.           p_process_step      => 'Completed',
  37.           p_process_developer => 'jmnickerson',
  38.           p_log_persist       => 365,
  39.           p_output_text       => --'Append job for utl_d_res.midday_apps.',
  40.           p_select_cnt        => NULL,
  41.           p_insert_cnt        => insertct,
  42.           p_update_cnt        => NULL,
  43.           p_delete_cnt        => NULL,
  44.           p_mech_number1      => NULL,
  45.           p_mech_number2      => NULL,
  46.           p_mech_text1        => NULL,
  47.           p_mech_text2        => NULL);
  48.       dbms_sql_monitor.end_operation(dbop_name => opname, dbop_eid => l_dbop_eid);
  49.       /*--------------------------------------------CHANGE LOG----------------------------------------
  50.       VERSION DATE        USERNAME    UPDATES
  51.       1.0     07-10-2014  jmcardl     --Initial release
  52.       2.0     11-28-2017  jmnickerson --Modification to Cursor Loop w/ Logger and Migration to ADS_ETL
  53.       ------------------------------------------------------------------------------------------------*/