将批次成本创建最终会计科目恢复到拟定

/* +=======================================================================+
 *                     Undo/Reset OPM Costed Flags 
 * +=======================================================================+
 *
 * IMPORTANT NOTE:
 * ===============
 *
 * 1. This script should be used by authorised Oracle personnel only.
 *    Use of this script must be approved by:
 *        OPM Financial Manager
 *        OPM Level 2 Support Manager
 *        Customer Management
 *
 * 2. Running this script without control/due care may severly damage the 
 *    customer's sub-ledger.
 *  a.  Advice the customer to perform a complete backup and
 *        create a test environement.
 *    b.  Make sure that the customer understands that this script will
 *        only resets costed flags for transactions in process enabled 
 *        organizations, so that these transactions can be considered 
 *        for processing by ACP/Preprocessor during next runs. 
 *    c.  Perform reversing/deleting of GL postings based on GL batches posted.
 *    d.  Run the script in the test environment first.
 *    e.  Ask customer to run the process (ACP/Pre-processor) again.
 *        Verify the results. 
 *    f.  IF THE RESULTS ARE ACCEPTABLE RUN THE SCRIPT IN PRODUCTION
 *        ENVIRONMENT.
 * 
 * 3. This script must be immediately removed from the customer's machine
 *    as soon as possible.  This is to prevent any accidental execution
 *    by the customer's personnel.
 * 4. Please modify start date ,end date and legal entity id
 *
 *
 * How to Use:
 * ===========
 *   - Make sure you backup all the related tables
 *     mtl_material_transactions
 *     gmf_rcv_accounting_txns
 *     gmf_invoice_distributions
 *     gmf_incoming_material_layers
 *     gme_resource_txns
 *     gme_batch_header
 *     gmf_lot_cost_adjustments
 *     gmf_period_balances
 *     cm_adjs_dtl
 *     gl_aloc_dtl
 *     gmf_lc_actual_cost_adjs  -- Applicable for OPM-LCM integration for R12.1 and later 
 *     gmf_lc_lot_cost_adjs      -- Applicable for OPM-LCM integration for R12.1 and later 
 *     gmf_lc_adj_transactions  -- Applicable for OPM-LCM integration for R12.1 and later 
 *     gmf_transaction_valuation
 *     xla_distribution_links
 *     xla_ae_lines
 *     xla_ae_segment_values
 *     xla_ae_headers
 *     xla_events
 *     xla_transaction_entities_upg 
 *     gmf_xla_extract_lines
 *     gmf_xla_extract_headers
 *
 *
 * PRE-REQUISITE:
 *
 *     - Perform reversing/deleting of GL postings based on GL batches posted. 
 *         After performing the reversals of batches make sure that the reversal/deleting 
 *         of GL postings has happened correctly from GL side. Once that is made sure, only then proceed  
 *         Please log a Service Request with Oracle General Ledger Support team For assistance with reversing/deleting GL Batches
 *
 *       Use THIS script, for next step to reset OPM costed flags. 
 *
 *   - Script does not commit. Commit or rollback needs to be explicitly issued
 *         based on the results from the above query. 
 *   
 *  CHANGE HISTORY:
 *   Version    Date        Name        Comments    
 *   2.00      01-JUL-2015     KGENIKAL     Modified to add additional scripts needed 
 *                        to support LCM-OPM integration tables ( Reference bugs 21054001, 20503780)
 *    
 * +=======================================================================+  */


    --
    -- Update mtl_material_transactions table
    --

        UPDATE mtl_material_transactions t
        SET   opm_costed_flag  = 'D'               
        WHERE transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')--timestamp format like 01/04/12 00:00:00
    AND   transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')--timestamp format like 30/04/12 23:59:59
    AND   EXISTS
    (
        SELECT 1 
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
    
        --
        -- Update gmf_rcv_accounting_txns table
        --       

        UPDATE gmf_rcv_accounting_txns grat
        SET    accounted_flag  = 'D'
        WHERE  transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1 
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = grat.organization_id
    );
        
        --
        -- Update gmf_invoice_distributions table
        --  

        UPDATE gmf_invoice_distributions
        SET  Accounted_flag = 'D', final_posting_date   = NULL               
        WHERE accounted_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   accounted_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id = '&LE_ID';
                
    --
    --    Update transaction valuation table to set shipment_costed flag 
    --    back to NULL for event clases FOB_RCPT_SENDER_RCPT and FOB_SHIP_RECIPIENT_SHIP
    --         
                       
    UPDATE mtl_material_transactions t
    SET    shipment_costed       = NULL                
    WHERE  transaction_source_type_id IN (7, 8, 13)
    AND    transaction_action_id      IN (12, 21)
    AND    transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1 
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
            

    --
        -- Update incoming layers table (BatchesXperiods Enh.)
        --

    UPDATE gmf_incoming_material_layers giml
    SET    accounted_flag        = 'D', 
           actual_posting_date   =  NULL
    WHERE  layer_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    layer_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1 
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = giml.mmt_organization_id
    );
        
        --
        -- Update resource transaction table
        -- Decide what Flag to use when run in Draft Mode.
        --        

    UPDATE gme_resource_txns
    SET   posted_ind  = 0                
    WHERE trans_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   trans_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
         
    --
    -- Update Batch Header table
    --
                   
    UPDATE gme_batch_header
    SET   gl_posted_ind     = 0                    
    WHERE batch_close_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   batch_close_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND  organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
            

    -- 
        -- Now for lot cost method, update gmf_lot_cost_adjustmets table
        -- For Actual/Standard methods, update gmf_period_balances table.
        -- 
        

    UPDATE gmf_lot_cost_adjustments
    SET   gl_posted_ind      = 0                
    WHERE adjustment_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id  = '&LE_ID';

    --
    -- Update transaction valuation table
    --

    UPDATE gmf_period_balances
    SET    costed_flag           = 'D'
    WHERE  period_balance_id IN 
        (SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id       = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'COSTREVAL'
                        AND geh.legal_entity_id   = '&LE_ID'
        );

    --
    -- Update Actual Cost Adjustments
    --
                
    UPDATE cm_adjs_dtl
    SET   gl_posted_ind      = 0
    WHERE adjustment_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
              
          
    --
    -- Update Cost Allocations
    --
           
                
    UPDATE gl_aloc_dtl
    SET   gl_posted_ind      = 0
    WHERE allocdtl_id IN 
        (    SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'GLCOSTALOC'
            and geh.legal_entity_id   = '&LE_ID'
        );
 
 
     /* Start changes V2 Dt. 01-Jul-2015 */
     /* Applicable for OPM-LCM integration for release 12.1 and later */
     /* Start Update OPM-LCM integration tables */
                 
         UPDATE gmf_lc_actual_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  ( 
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
                  );
  
         UPDATE gmf_lc_lot_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  ( 
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
                  );
 
         UPDATE gmf_lc_adj_transactions
         SET    accounted_flag    = 'D'
         WHERE  legal_entity_id   = '&LE_ID'
     AND    transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss');
 
     /* End Update OPM-LCM integration tables */
 
     /* End changes V2 Dt. 01-Jul-2015 */
     
    --
    -- Delete transaction valuation rows
    --
        
    DELETE FROM gmf_transaction_valuation
    WHERE  transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
    AND    legal_entity_id   = '&LE_ID';

     create table xla_events_bck as
     select xe.* 
     from   xla_events xe,
            gmf_xla_extract_headers gmf
     where  xe.application_id     = 555 
     and    xe.event_id           = gmf.event_id 
     and    gmf.transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss') 
     and    gmf.transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
         AND    gmf.legal_entity_id   = '&LE_ID';
     
     create table xla_ae_headers_bck as
     select * 
     from   xla_ae_headers
     where  application_id = 555 
     and    event_id in(select event_id 
                        from   xla_events_bck);
     
     
     create table xla_ae_lines_bck as
     select * 
     from   xla_ae_lines 
     where  application_id = 555 
     and    ae_header_id in(select ae_header_id  
                            from   xla_ae_headers_bck);
     
     create table xla_dist_link_bck as
     select * 
     from   xla_distribution_links 
     where  application_id = 555 
     and    ae_header_id in(select ae_header_id  
                            from   xla_ae_headers_bck);
     
     
     create table xla_ae_seg_val_bck as
     select * 
     from   xla_ae_segment_values 
     where  ae_header_id in(select ae_header_id  
                            from   xla_ae_headers_bck );

    /* Start changes V2 Dt. 01-Jul-2015 */                            
        CREATE TABLE xla_trans_enti_upg_bck AS
        SELECT *
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE application_id = 555
        AND LEGAL_ENTITY_ID  = '&LE_ID'
        AND ENTITY_ID       IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                            
    /* End changes V2 Dt. 01-Jul-2015 */

     -- delete data from xla tables
     delete from xla_distribution_links 
     where  application_id = 555 
     and    ae_header_id in(select distinct ae_header_id 
                            from   xla_dist_link_bck);
     
     
     delete from xla_ae_lines 
     where  application_id = 555 
     and    ae_header_id in(select distinct ae_header_id 
                            from   xla_ae_lines_bck);
     
     
     --This one may take sometime.
     delete from xla_ae_segment_values 
     where  ae_header_id in(select distinct ae_header_id 
                            from   xla_ae_seg_val_bck);
     
     
     delete from xla_ae_headers 
     where  application_id = 555 
     and    ae_header_id in (select ae_header_id 
                             from   xla_ae_headers_bck);
                             
     delete from xla_events
     where  application_id = 555
         and    event_id in(select event_id
                    from   xla_events_bck);
         
         /* Start changes V2 Dt. 01-Jul-2015 */
        DELETE
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE APPLICATION_ID=555
        AND LEGAL_ENTITY_ID = '&LE_ID'
        AND ENTITY_ID      IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                 
     /* End changes V2 Dt. 01-Jul-2015 */
     
        --
        -- Delete extract lines
        --
        DELETE FROM gmf_xla_extract_lines
        WHERE header_id IN (
            SELECT header_id 
            FROM  gmf_xla_extract_headers
            WHERE transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
                        AND   legal_entity_id   = '&LE_ID'
                );
        
        --
        -- Delete extract headers
        --
    
        DELETE FROM gmf_xla_extract_headers
        WHERE transaction_date >= TO_DATE('&period_start_date_timestamp','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('&period_end_date_timestamp','dd/mm/yy hh24:mi:ss')
                AND   legal_entity_id   = '&LE_ID';