--SET serveroutput ON SIZE 1000000; 
 
DECLARE 
------------------------------------------------------------------------------- 
-- PURPOSE:   
--  Updates GMIR Bom_Header salesorder fields from decoded so_status in MLS 
-- 
-- THEORY OF OPERATION: 
--   This script contains an anomymous PL/SQL block built around a major and minor loop.   
--   The major loop contains qualified BOM_HEADERS and the minor loop finds their salesorder  
--   update vaules from SALES_ORDERS.so_status.  The minor loop issues update statements  
--   against BOM_HEADERS.    
--   
-- REVISIONS: 
--   Code maintainers should be familiar with MLS-GMIR-SO_Updates.doc  
--   before attempting any maintenance activities.  
-- 
-- 02-FEB-05   JJM   * store existing BOM_HEADER.salesorder in BOM_HEADER_HISTORY. 
--                   * better comparisons with NULL. 
-- 
-- 26-JAN-05   JJM   * EXECUTE IMMEDIATE now uses bound varibles for UPDATE targets. 
--                   * Commit every l_commit_rate (10) updates. 
--                   * Dont' update BOM_HEADERS.salesorders if the update will not  
--                     change the existing value. 
-- 
-- 17-JAN-05	JJM  Incorporate edits from walk thru. See MLS-GMIR-SO-Updates.doc 
-- 
-- 14-JAN-05 	JJM  Started from concepts by K. Newbern, et al.  
------------------------------------------------------------------------------- 
 
update_value      VARCHAR2(256); 
update_stmt       VARCHAR2(1024); 
 
l_historyid       NUMBER; 
 
l_record_count    NUMBER DEFAULT 0; 
l_commit_rate     NUMBER DEFAULT 100; 
 
BEGIN 
 
  -- find qualified BOM_HEADERS since yesterday 
  FOR BOM_HEADERS IN  
      (SELECT bh.salesorder, bh.sitecode, bh.expansionid,  
              bh.erid, bh.componentttype, bh.current_config1900, 
              bh.target_config1900, bh.current_config850, bh.target_config850, 
              bh.status, bh.application, bh.fileid, bh.lastupdt, bh.lastupdtuser 
       FROM   ct2user.BOM_HEADER bh, 
              SALES_ORDERS@x018 so 
       WHERE  bh.sitecode || bh.expansionid = so.customer_reference||trim(substr(so.customer_order_id,-2,2)) 
       AND    to_date(so.modified_date) >= to_date(sysdate) - 1) 
  LOOP       
       
   BEGIN     
     -- get ALL the Sales Orders for each BOM_HEADER 
     FOR SALESORDERS IN 
        (select so.pk_case_id as SALESORDER, so.sellers_reference, 
                decode(upper(so.so_status), 
                                         'CANCELLED',           'CNCL', 
                                         'CLOSED',              'CLSD', 
                                         'DELIVERED',           'DLVD', 
                                         'DRAFT',               'DRFT', 
                                         'INVOICED',            'INVC', 
                                         'OPEN',                'OPEN', 
                                         'PARTIALLY DELIVERED', 'PDLV', 
                                         'SOURCING CONFIRMED',  'SCON', 
                                         'SOURCING DONE',       'SDON', 
                                         so.so_status) STATUS                                                     
         from   SALES_ORDERS@x018 so 
         where  so.customer_reference || trim(substr(so.customer_order_id, -2, 2)) =         
                BOM_HEADERS.sitecode || BOM_HEADERS.expansionid) 
     LOOP 
           
        -- prefix update_value if 'NOT FOR INVOICING' within BOM_HEADER.salesorder 
        IF (instr(BOM_HEADERS.salesorder, 'NOT FOR INVOICING') > 0) THEN 
          IF (instr(update_value,'NOT FOR INVOICING') = 0) THEN -- only prefix it once. 
            update_value := update_value || 'NOT FOR INVOICING, '; 
          END IF;      
        END IF;            
          
        -- prefix update_value if RTN within ANY sales_order 
        IF (instr(SALESORDERS.sellers_reference, 'RTN', -3) > 0) THEN 
          IF (substr(NVL(update_value,' '),1,3) <> 'RTN') THEN -- only prefix it once. 
            update_value := 'RTN, '; 
          END IF;      
        END IF;  
       
        -- annotate salesorders with status if needed 
        IF NVL(instr(update_value, SALESORDERS.SALESORDER  || SALESORDERS.STATUS), 0) = 0 THEN 
          update_value := update_value || SALESORDERS.SALESORDER || SALESORDERS.STATUS || ', '; 
        END IF;   
         
     END LOOP; 
     
     -- clip off any trailing comma 
     IF (INSTR(trim(update_value),',',-1) > 0) THEN 
       update_value := substr(trim(update_value),1,length(update_value)-2); 
     END IF; 
      
      -- update BOM_HEADER salesorder if needed 
     IF (update_value <> nvl(BOM_HEADERS.salesorder,' ')) THEN 
         
        -- save BOM_HEADER.salesorder before update in BOM_HEADER_HISTORY  
        SELECT bom_header_history_s.nextval INTO l_historyid FROM DUAL;           
        update_stmt := 'INSERT INTO ct2user.BOM_HEADER_HISTORY VALUES  ('     || 
                                   l_historyid                    || ', '''   ||     
                                   BOM_HEADERS.erid               || ''', ''' ||  
                                   BOM_HEADERS.sitecode           || ''', '   ||   
                                   BOM_HEADERS.expansionid        || ', '''   || 
                                   BOM_HEADERS.componentttype     || ''', ''' || 
                                   BOM_HEADERS.current_config1900 || ''', ''' ||        
                                   BOM_HEADERS.target_config1900  || ''', ''' || 
                                   BOM_HEADERS.current_config850  || ''', ''' || 
                                   BOM_HEADERS.target_config850   || ''', ''' || 
                                   BOM_HEADERS.status             || ''', ''' || 
                                   BOM_HEADERS.salesorder         || ''', '; 
                          
                                   --build an insert string that contains  
                                   --NULL keyword. allow fileid 0 to pass.                       
                                   IF (nvl(BOM_HEADERS.fileid,-99) = -99) THEN 
                                     update_stmt := update_stmt   || 'NULL'; 
                                   ELSE 
                                     update_stmt := update_stmt   ||  BOM_HEADERS.fileid;  
                                   END IF; 
                                    
                                   update_stmt := update_stmt     || ', '''   || 
                                   BOM_HEADERS.application        || ''', ''' || 
                                   BOM_HEADERS.lastupdt           || ''', ''' || 
                                   BOM_HEADERS.lastupdtuser       || ''', ''' || 
                                   sysdate                        || ''')';                                                          
        EXECUTE IMMEDIATE update_stmt;  
        l_record_count := l_record_count + 1; 
            
        update_stmt := ' UPDATE ct2user.bom_header SET salesorder = '''   || update_value || ''' ' || 
                       ' WHERE sitecode = '''  || BOM_HEADERS.sitecode    || '''' || 
                       ' AND   expansionid = ' || BOM_HEADERS.expansionid;                                               
        EXECUTE IMMEDIATE update_stmt;   
        l_record_count := l_record_count + 1;         
         
        IF (MOD(l_record_count, l_commit_rate) = 0) THEN 
           COMMIT; 
           DBMS_OUTPUT.NEW_LINE; 
           DBMS_OUTPUT.PUT_LINE('Committed ' || l_record_count || ' updates'); 
           DBMS_OUTPUT.NEW_LINE; 
        END IF;      
          
     END IF;     
          
     -- reset inner loop values 
     update_value := ''; 
     update_stmt := '';   
 
      EXCEPTION 
       WHEN OTHERS THEN 
         dbms_output.put_line (update_stmt); 
         dbms_output.put_line('Exception while processing SITE/EXPANSION: '  
                               || BOM_HEADERS.sitecode || BOM_HEADERS.expansionid || 
                               ' NEW SALESORDER: '     || update_value   ||   
                               ' SQLCODE: '                              || 
                               SQLCODE                                   || 
                               ': '                                      || 
                               SQLERRM); 
         -- reset inner loop values 
         update_value := ''; 
         update_stmt := '';                             
   END; 
  END LOOP; 
   
  COMMIT; 
END;