Post date: Jun 11, 2020 2:33:38 AM
/* +=======================================================================+
* 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. For
* reversing/deleting of GL postings , Please reverse GL batches posted.
* c. Run the script in the test environment first.
* d. Ask customer to run the process (ACP/Pre-processor) again.
* Verify the results.
* e. 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
*
*
* - 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
* for next step to reset OPM costed flags using THIS script.
* Please log a Service Request with Oracle General Ledger Support team For assistance with reversing/deleting GL Batches
*
* - Script does not commit. Commit or rollback needs to be explicitly issued baased on the results from the above query.
* If u have primary and secondary ledgers please make sure correct ledger id is being used
*
* try to use find and replace for start, end date and le_id
* To find the LE_ID use below sql
SELECT DISTINCT MP.ORGANIZATION_CODE,
mp.organization_id,
GFP.LEGAL_ENTITY_ID,
GFP.ledger_id,
OOD.OPERATING_UNIT
FROM mtl_parameters mp,
gmf_fiscal_policies gfp,
org_organization_definitions ood
WHERE gfp.legal_entity_id = ood.legal_entity
AND MP.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MP.PROCESS_ENABLED_FLAG = 'Y'
ORDER BY 1,2,3,4 ;
* 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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','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 gmf_rcv_accounting_txns table
--
UPDATE gmf_rcv_accounting_txns grat
SET accounted_flag = 'D'
WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND accounted_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND layer_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND trans_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND batch_close_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND adjustment_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND adjustment_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
AND geh.event_type_code = 'GLCOSTALOC'
and geh.legal_entity_id = '&LE_ID'
);
/* 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('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('01/08/09 23:59:59','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('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('01/08/09 23:59:59','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('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss');
/* End Update OPM-LCM integration tables */
--
-- Delete transaction valuation rows
--
DELETE FROM gmf_transaction_valuation
WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
and gmf.transaction_date <= TO_DATE('30/04/12 23:59:59','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 );
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
);
-- 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);
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
);
--
-- 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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','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('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
AND transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
AND legal_entity_id = '&LE_ID';
Commit;