[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
n_rows_inserted NUMBER; /* into xla_events_int_gt table */
select count(*) into n_hdrs from gmf_xla_extract_headers_gt; -- xxxremove
select count(*) into n_lines from gmf_xla_extract_lines_gt; -- xxxremove
g_log_msg := 'Calling proc GMF_XLA_PKG.update_extract_gt to set entity codes';
update_extract_gt('SET_ENTITY_CODES', NULL);
SELECT distinct entity_code
FROM gmf_xla_extract_headers_gt
)
LOOP
g_log_msg := 'Calling function GMF_XLA_PKG.insert_into_xla_events_gt for entity ' || i.entity_code ||
' (if entity is Inventory when process was submitted for PUR/OM, then we have some' ||
' internal order transfers)';
n_rows_inserted := insert_into_xla_events_gt(i.entity_code);
IF n_rows_inserted = 0
THEN
g_log_msg := 'No events to create for entity ' || i.entity_code || '. User might be running the process more than once.' ||
' We will still update extract headers and lines, since amounts might ' ||
' differ from last run';
SELECT entity_code
INTO l_entity_type_code
FROM gmf_xla_extract_headers_gt
WHERE rownum = 1
;
select count(*) into n_events from xla_events
where application_id = 555
and reference_num_1 = p_reference_no; -- xxxremove
IF n_rows_inserted = 0
THEN
g_log_msg := 'No events were created for entity ' || i.entity_code ||'. So, no need to update extract_headers gt with event ids';
g_log_msg := 'Calling proc XLA_EVENTS_PKG.update_extract_gt to set event ids for entity ' || i.entity_code;
update_extract_gt('SET_EVENT_IDS', i.entity_code);
DELETE FROM xla_events_int_gt; -- Cleanup for next run
g_log_msg := 'Calling proc update_extract_header';
update_extract_headers_table;
g_log_msg := 'Calling proc XLA_EVENTS_PKG.update_extract_lines';
update_extract_lines_table;
DELETE FROM xla_events_int_gt;
/* Bug 12647482. Following delete statements commented because these are transaction persistent tables. The delete
happens automatically when the data is commited after processing every 1000 records (profile: GL$COMMIT_COUNT). */
/*
DELETE FROM gmf_xla_extract_headers_gt;
DELETE FROM gmf_xla_extract_lines_gt;
PROCEDURE update_extract_gt (
p_what_to_update IN VARCHAR2,
p_entity_code IN VARCHAR2
)
IS
l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_GT';
<>
CASE p_what_to_update
WHEN 'SET_ENTITY_CODES'
THEN
--
-- setting entity_code, event_class_code, event_type_code
--
g_log_msg := 'Setting entity_code, event_class_code, and event_type_code now.';
update gmf_xla_extract_headers_gt ehgt
set (entity_code, event_class_code, event_type_code) =
(SELECT entity_code, event_class_code, event_type_code
FROM gmf_xla_event_model em
WHERE
( em.transaction_source_type_id = ehgt.transaction_source_type_id
AND em.transaction_action_id = ehgt.transaction_action_id
AND nvl(em.organization, 'x') = nvl(ehgt.organization, 'x')
AND nvl(em.transfer_type, 'x') = nvl(ehgt.transfer_type, 'x')
AND nvl(em.transfer_price_flag, 'x') = nvl(ehgt.transfer_price_flag, 'x')
AND nvl(em.transaction_type, 'x') = nvl(ehgt.transaction_type, 'x')
AND nvl(em.fob_point, 99) = nvl(ehgt.fob_point, 99)
AND nvl(ehgt.transaction_type, 'x') <> 'RESOURCE_TRANSACTIONS'
)
OR
(
nvl(ehgt.transaction_type, 'x') = em.transaction_type -- 'RESOURCE_TRANSACTIONS'
)
)
;
g_log_msg := sql%rowcount || ' rows updated';
SELECT transaction_id, transaction_source_type_id, transaction_action_id,
source_document_id, source_line_id,
nvl(organization, 'x') as organization,
nvl(transfer_type, 'x') as transfer_type,
nvl(transfer_price_flag, 'x') as transfer_price_flag,
nvl(fob_point, 99) as fob_point,
nvl(transaction_type, 'x') as transaction_type
FROM gmf_xla_extract_headers_gt
WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
)
LOOP
IF l_cnt_hdr_msg > 0 THEN /* Bug 12647482 */
g_log_msg := 'Could not set event type for ' ||
'TxnID/SrcTyp/Act/Org/XferType/TPflag/FOB/TxnType/SrcDoc/SrcLine'
;
DELETE FROM gmf_xla_extract_lines_gt
where header_id in (SELECT header_id
FROM gmf_xla_extract_headers_gt
WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL)
;
DELETE FROM gmf_xla_extract_headers_gt
WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
;
UPDATE gmf_xla_extract_headers_gt ehgt
SET event_id = (SELECT event_id
FROM xla_events_int_gt egt
WHERE egt.source_id_int_1 = ehgt.transaction_id
AND egt.source_id_int_2 = ehgt.ledger_id
AND egt.source_id_int_3 = ehgt.valuation_cost_type_id
/* AND egt.source_id_int_4 = ehgt.transaction_source_type_id INVCONV */
AND egt.source_id_char_1 = ehgt.event_class_code
/* AND nvl(egt.source_id_char_2, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
)
WHERE entity_code = p_entity_code
;
g_log_msg := sql%rowcount || ' rows updated';
END CASE update_extract_hdr_gt;
END update_extract_gt;
FUNCTION insert_into_xla_events_gt (p_entity_code IN VARCHAR2)
RETURN NUMBER
IS
l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'INSERT_INTO_XLA_EVENTS_GT';
n_rows_inserted NUMBER;
INSERT INTO xla_events_int_gt
(
entity_id
, application_id
, ledger_id
, legal_entity_id
, entity_code
, transaction_number
, source_id_int_1
, source_id_int_2
, source_id_int_3
, source_id_int_4
, source_id_char_1
, source_id_char_2
, source_id_char_3
, source_id_char_4
, event_id
, event_class_code
, event_type_code
, event_number
, event_date
, event_status_code
, process_status_code
, reference_num_1
, reference_num_2
, reference_num_3
, reference_num_4
, reference_char_1
, reference_char_2
, reference_char_3
, reference_char_4
, reference_date_1
, reference_date_2
, reference_date_3
, reference_date_4
, valuation_method
, security_id_int_1
, security_id_int_2
, security_id_int_3
, security_id_char_1
, security_id_char_2
, security_id_char_3
, on_hold_flag
, transaction_date
)
SELECT
DISTINCT
NULL -- entity_id
, 555 -- application_id
, ledger_id
, legal_entity_id
, entity_code
, transaction_id -- transaction_number
, transaction_id -- SOURCE_ID_INT_1
, ledger_id -- SOURCE_ID_INT_2
, valuation_cost_type_id -- SOURCE_ID_INT_3
/* , transaction_source_type_id -- SOURCE_ID_INT_4 INVCONV */
, NULL -- SOURCE_ID_INT_4
, event_class_code -- SOURCE_ID_CHAR_1
/* , lot_number -- SOURCE_ID_CHAR_2 INVCONV */
/*
, DECODE(event_class_code,
'RECEIVE', DECODE(event_type_code,
'RECEIVE', source_line_id,
NULL),
NULL) -- SOURCE_ID_CHAR_2 Bug 13043283
*/
, NULL -- SOURCE_ID_CHAR_2 Bug 13637723
, NULL -- SOURCE_ID_CHAR_3
, NULL -- SOURCE_ID_CHAR_4
, NULL -- event_id
, event_class_code
, event_type_code
, NULL -- event_number
, transaction_date -- event_date
, xla_events_pub_pkg.C_EVENT_UNPROCESSED -- event_status_code
, NULL -- process_status_code
, reference_no -- REFERENCE_NUM_1
, NULL -- REFERENCE_NUM_2
, NULL -- REFERENCE_NUM_3
, NULL -- REFERENCE_NUM_4
, NULL -- REFERENCE_CHAR_1
, NULL -- REFERENCE_CHAR_2
, NULL -- REFERENCE_CHAR_3
, NULL -- REFERENCE_CHAR_4
, NULL -- REFERENCE_DATE_1
, NULL -- REFERENCE_DATE_2
, NULL -- REFERENCE_DATE_3
, NULL -- REFERENCE_DATE_4
, valuation_cost_type -- valuation_method
, ehgt.organization_id -- SECURITY_ID_INT_1
, ehgt.operating_unit -- SECURITY_ID_INT_2
, legal_entity_id -- SECURITY_ID_INT_3 Bug 6601963
, NULL -- SECURITY_ID_CHAR_1
, NULL -- SECURITY_ID_CHAR_2
, NULL -- SECURITY_ID_CHAR_3
, NULL
, transaction_date
FROM
gmf_xla_extract_headers_gt ehgt
WHERE
entity_code = p_entity_code
AND
not exists (SELECT 'txns for which events created'
FROM gmf_xla_extract_headers eh
WHERE eh.transaction_id = ehgt.transaction_id
AND eh.legal_entity_id = ehgt.legal_entity_id
AND eh.ledger_id = ehgt.ledger_id
AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
/* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
AND eh.event_class_code = ehgt.event_class_code
/* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
) ;
n_rows_inserted := sql%rowcount;
g_log_msg := n_rows_inserted || ' rows inserted into xla_events_int_gt';
RETURN n_rows_inserted;
END insert_into_xla_events_gt;
PROCEDURE update_extract_headers_table
IS
l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_HEADERS_TABLE';
USING (SELECT
DISTINCT
reference_no
, event_id
, entity_code
, event_class_code
, event_type_code
, legal_entity_id
, ledger_id
, xfer_legal_entity_id
, xfer_ledger_id
, operating_unit
, base_currency
, transaction_id
, transaction_date
, valuation_cost_type_id
, valuation_cost_type
, inventory_item_id
-- , item_revision
, organization_id
, lot_number
, transaction_quantity
, transaction_uom
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, transaction_value
, transaction_value_raw
, transaction_currency
, txn_source
, source_document_id
, source_line_id
, currency_code
, currency_conversion_date
, currency_conversion_type
, currency_conversion_rate -- Bug 6792803
, resources
-- , resource_class
, line_type
, ar_trx_type_id
, order_type
, reason_id
/*
, charge_id
, customer_id
, customer_site_id
, taxauth_id
, vendor_id
, vendor_site_id
, routing_id
, customer_gl_class
, itemcost_class
, vendor_gl_class
, cost_category_id
, gl_business_class_cat_id
, gl_product_line_cat_id
, jv_qty_ind
, quantity_um
*/
, accounted_flag
, actual_posting_date
, invoiced_flag
, shipment_costed
FROM gmf_xla_extract_headers_gt) ehgt
ON ( eh.transaction_id = ehgt.transaction_id
AND eh.ledger_id = ehgt.ledger_id
AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
/* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
AND eh.event_class_code = ehgt.event_class_code
/* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
/* Bug 7620018. Added legal_entity_id so that unique index GMF_XLA_EXTRACT_HEADERS_U2 is used. */
AND eh.legal_entity_id = ehgt.legal_entity_id
AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
)
WHEN MATCHED THEN
UPDATE SET
eh.transaction_quantity = ehgt.transaction_quantity
, eh.transaction_uom = ehgt.transaction_uom /* B8617122 */
, eh.transaction_value = ehgt.transaction_value
, eh.transaction_value_raw = ehgt.transaction_value_raw
, eh.reference_no = ehgt.reference_no
, eh.shipment_costed = ehgt.shipment_costed
, eh.invoiced_flag = ehgt.invoiced_flag
, eh.last_update_date = sysdate
, eh.last_updated_by = g_user_id
, eh.last_update_login = g_login_id
, eh.program_application_id = g_prog_appl_id
, eh.program_id = g_program_id
, eh.request_id = g_request_id
, eh.currency_conversion_rate = ehgt.currency_conversion_rate
, eh.transaction_date = ehgt.transaction_date /* Bug 8251052 */
WHEN NOT MATCHED THEN
INSERT
(
header_id
, reference_no
, event_id
, entity_code
, event_class_code
, event_type_code
, legal_entity_id
, ledger_id
, xfer_legal_entity_id
, xfer_ledger_id
, operating_unit
, base_currency
, transaction_id
, transaction_date
, valuation_cost_type_id
, valuation_cost_type
, inventory_item_id
-- , item_revision
, organization_id
, lot_number
, transaction_quantity
, transaction_uom
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, transaction_value
, transaction_value_raw
, transaction_currency
, txn_source
, source_document_id
, source_line_id
, currency_code
, currency_conversion_date
, currency_conversion_type
, currency_conversion_rate -- Bug 6792803
, resources
-- , resource_class
, line_type
, ar_trx_type_id
, order_type
, reason_id
/*
, charge_id
, customer_id
, customer_site_id
, taxauth_id
, vendor_id
, vendor_site_id
, routing_id
, customer_gl_class
, itemcost_class
, vendor_gl_class
, cost_category_id
, gl_business_class_cat_id
, gl_product_line_cat_id
, jv_qty_ind
, quantity_um
*/
, accounted_flag
, actual_posting_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, request_id
)
VALUES
(
gmf_xla_extract_headers_s.NEXTVAL -- header_id
, ehgt.reference_no
, ehgt.event_id
, ehgt.entity_code
, ehgt.event_class_code
, ehgt.event_type_code
, ehgt.legal_entity_id
, ehgt.ledger_id
, ehgt.xfer_legal_entity_id
, ehgt.xfer_ledger_id
, ehgt.operating_unit
, ehgt.base_currency
, ehgt.transaction_id
, ehgt.transaction_date
, ehgt.valuation_cost_type_id
, ehgt.valuation_cost_type
, ehgt.inventory_item_id
-- , ehgt.item_revision
, ehgt.organization_id
, ehgt.lot_number
, ehgt.transaction_quantity
, ehgt.transaction_uom
, ehgt.transaction_source_type_id
, ehgt.transaction_action_id
, ehgt.transaction_type_id
, ehgt.transaction_value
, ehgt.transaction_value_raw
, ehgt.transaction_currency
, ehgt.txn_source
, ehgt.source_document_id
, ehgt.source_line_id
, ehgt.currency_code
, ehgt.currency_conversion_date
, ehgt.currency_conversion_type
, ehgt.currency_conversion_rate
, ehgt.resources
-- , ehgt.resource_class
, ehgt.line_type
, ehgt.ar_trx_type_id
, ehgt.order_type
, ehgt.reason_id
/*
, ehgt.charge_id
, ehgt.customer_id
, ehgt.customer_site_id
, ehgt.taxauth_id
, ehgt.vendor_id
, ehgt.vendor_site_id
, ehgt.routing_id
, ehgt.customer_gl_class
, ehgt.itemcost_class
, ehgt.vendor_gl_class
, ehgt.cost_category_id
, ehgt.gl_business_class_cat_id
, ehgt.gl_product_line_cat_id
, ehgt.jv_qty_ind
, ehgt.quantity_um
*/
, 'N' -- ehgt.accounted_flag
, ehgt.actual_posting_date
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_prog_appl_id
, g_program_id
, g_request_id
)
;
END update_extract_headers_table;
PROCEDURE update_extract_lines_table
IS
l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_LINES_TABLE';
UPDATE gmf_xla_extract_lines_gt elgt
SET (header_id, event_id) =
(SELECT
eh.header_id, eh.event_id
FROM
gmf_xla_extract_headers_gt ehgt,
gmf_xla_extract_headers eh
WHERE
ehgt.header_id = elgt.header_id
AND eh.legal_entity_id = ehgt.legal_entity_id
AND eh.ledger_id = ehgt.ledger_id
AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
AND eh.transaction_id = ehgt.transaction_id
/* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
AND eh.event_class_code = ehgt.event_class_code
/* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
) ;
g_log_msg := sql%rowcount || ' rows updated with header_id and event_id in extract_lines_gt table';
DELETE FROM gmf_xla_extract_lines el
WHERE
-- reference_no <> g_reference_no AND
header_id in
(
SELECT header_id
FROM gmf_xla_extract_lines_gt elgt
-- WHERE ehgt.header_id = el.header_id
)
;
g_log_msg := sql%rowcount || ' old rows deleted from extract_lines table';
INSERT INTO gmf_xla_extract_lines
(
line_id
, header_id
, reference_no
, event_id
, ledger_id
, line_number
, journal_line_type
, cost_cmpntcls_id
, cost_analysis_code
, component_cost
, usage_ind
, cost_level
, aqui_cost_id
, trans_amount_raw
, base_amount_raw
, trans_amount
, base_amount
, dr_cr_sign
, organization_id
, subinv_organization_id
, subinventory_code
, xfer_subinventory_code
, lot_number
, locator_id
, transaction_account_id
, entered_amount
, accounted_amount
, creation_date
, created_by
, last_update_date
, last_updated_by
, program_application_id
, program_id
, request_id
)
SELECT
elgt.line_id
-- gmf_xla_extract_lines_s.NEXTVAL -- line_id /* Bug 12576944 */
, elgt.header_id
, elgt.reference_no
, elgt.event_id
, elgt.ledger_id
, row_number() over(partition by header_id order by header_id) -- line_number
, elgt.journal_line_type
, elgt.cost_cmpntcls_id
, elgt.cost_analysis_code
, elgt.component_cost
, elgt.usage_ind
, elgt.cost_level
, elgt.aqui_cost_id
, elgt.trans_amount_raw
, elgt.base_amount_raw
, elgt.trans_amount
, elgt.base_amount
, elgt.dr_cr_sign
, elgt.organization_id
, elgt.organization_id
, elgt.subinventory_code
, elgt.xfer_subinventory_code
, elgt.lot_number
, elgt.locator_id
, elgt.transaction_account_id
, elgt.entered_amount
, elgt.accounted_amount
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_prog_appl_id
, g_program_id
, g_request_id
FROM
gmf_xla_extract_lines_gt elgt
;
g_log_msg := sql%rowcount || ' rows inserted into extract_lines table';
END update_extract_lines_table;
/* Bug 12647482 Following delete statement commented and replaced with sebsequent one. */
/*
delete from gmf_transaction_valuation
where (transaction_id, ledger_id, valuation_cost_type_id,
-- transaction_source_type_id,
event_class_code) IN
(select eh.transaction_id, eh.ledger_id, eh.valuation_cost_type_id,
-- eh.transaction_source_type_id,
eh.event_class_code
from gmf_xla_extract_headers eh,
gmf_xla_extract_lines_gt elgt
where eh.header_id = elgt.header_id
and eh.event_id = elgt.event_id
)
;
DELETE FROM gmf_transaction_valuation
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
IN (
select transaction_id, ledger_id, valuation_cost_type_id, event_class_code
from gmf_xla_extract_headers_gt
);
g_log_msg := sql%rowcount || ' previous rows deleted from GVT';
INSERT INTO gmf_transaction_valuation
(
valuation_id
, header_id
, event_id
, ledger_id
, legal_entity_id
, ledger_currency
, valuation_cost_type
, valuation_cost_type_id
, reference_no
, transaction_source
, transaction_id
, doc_id
, line_id
, org_id
, organization_id
, inventory_item_id
, item_number
, lot_number
, resources
, transaction_date
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, entity_code
, event_class_code
, event_type_code
, final_posting_date
, accounted_flag
, line_type
, transaction_source_type
, journal_line_type
, subinventory_code
, component_class_usage
, component_class_usage_type
, cost_level
, txn_base_value_raw
, txn_base_value
, creation_date
, created_by
, last_update_date
, last_updated_by
, program_application_id
, program_id
, request_id
)
SELECT
gmf_transaction_valuation_S.nextval
, a.header_id
, a.event_id
, a.ledger_id
, a.legal_entity_id
, a.ledger_currency
, a.valuation_cost_type
, a.valuation_cost_type_id
, a.reference_no
, a.transaction_source
, a.transaction_id
, a.doc_id
, a.line_id
, a.org_id
, a.organization_id
, a.inventory_item_id
, a.item_number
, a.lot_number
, a.resources
, a.transaction_date
, a.transaction_source_type_id
, a.transaction_action_id
, a.transaction_type_id
, a.entity_code
, a.event_class_code
, a.event_type_code
, a.final_posting_date
, a.accounted_flag
, a.line_type
, a.transaction_source_type
, a.journal_line_type
, a.subinventory_code
, a.component_class_usage
, a.component_class_usage_type
, a.cost_level
, a.txn_base_value_raw
, a.txn_base_value
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_prog_appl_id
, g_program_id
, g_request_id
FROM
(SELECT
eh.header_id
, eh.event_id
, eh.ledger_id
, eh.legal_entity_id
, eh.base_currency as ledger_currency
, eh.valuation_cost_type
, eh.valuation_cost_type_id
, eh.reference_no
, decode(eh.txn_source, 'INV', 'INVENTORY',
'PUR', 'PURCHASING',
'OM', 'ORDERMANAGEMENT',
'PM', 'PRODUCTION',
'RVAL', 'COSTREVALUATION')
as transaction_source
, eh.transaction_id
, eh.source_document_id as doc_id
, eh.source_line_id as line_id
, eh.operating_unit as org_id
, eh.organization_id
, eh.inventory_item_id
, item.concatenated_segments as item_number
, NULL as lot_number
, eh.resources
, eh.transaction_date
, eh.transaction_source_type_id
, eh.transaction_action_id
, eh.transaction_type_id
, eh.entity_code
, eh.event_class_code
, eh.event_type_code
, eh.actual_posting_date as final_posting_date
, eh.accounted_flag
, eh.line_type
, nvl(ts.transaction_source_type_name, ' ') as transaction_source_type
, elgt.journal_line_type
, elgt.subinventory_code
, decode(elgt.usage_ind, 1, 'Material',
2, 'Overhead',
3, 'Resource',
4, 'Expense Alloc',
5, 'Std Cost Adj') as component_class_usage
, elgt.usage_ind as component_class_usage_type
, elgt.cost_level
, sum(elgt.BASE_AMOUNT_RAW) as txn_base_value_raw
, sum(elgt.BASE_AMOUNT) as txn_base_value
FROM gmf_xla_extract_headers eh,
gmf_xla_extract_lines_gt elgt,
mtl_system_items_kfv item,
mtl_txn_source_types ts
WHERE eh.header_id = elgt.header_id
AND eh.event_id = elgt.event_id
--
-- Need an outer join here since for batch close rows, item id is null
--
AND item.organization_id(+) = eh.organization_id
AND item.inventory_item_id(+) = eh.inventory_item_id
AND ts.transaction_source_type_id(+) = eh.transaction_source_type_id
GROUP BY
eh.header_id
, eh.event_id
, eh.ledger_id
, eh.legal_entity_id
, eh.base_currency
, eh.valuation_cost_type
, eh.valuation_cost_type_id
, eh.reference_no
, decode(eh.txn_source, 'INV', 'INVENTORY',
'PUR', 'PURCHASING',
'OM', 'ORDERMANAGEMENT',
'PM', 'PRODUCTION',
'RVAL', 'COSTREVALUATION')
, eh.transaction_id
, eh.source_document_id
, eh.source_line_id
, eh.operating_unit
, eh.organization_id
, eh.inventory_item_id
, item.concatenated_segments
, eh.lot_number
, eh.resources
, eh.transaction_date
, eh.transaction_source_type_id
, eh.transaction_action_id
, eh.transaction_type_id
, eh.entity_code
, eh.event_class_code
, eh.event_type_code
, eh.actual_posting_date
, eh.accounted_flag
, eh.line_type
, ts.transaction_source_type_name
, elgt.journal_line_type
, elgt.subinventory_code
, decode(elgt.usage_ind, 1, 'Material',
2, 'Overhead',
3, 'Resource',
4, 'Expense Alloc',
5, 'Std Cost Adj')
, elgt.usage_ind
, elgt.cost_level
) a
;
g_log_msg := sql%rowcount || ' rows inserted into GVT';
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_process_category:
| This parameter is the "process category" of the events to account. This
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter.Possible values are as following:
| +------------+------------------------------------------+
| | Value | Meaning |
| +------------+------------------------------------------+
| | 'Invoices' | process invoices |
| | 'Payments' | process payments and reconciled payments |
| | 'All' | process everything |
| +------------+------------------------------------------+
| p_end_date
| This parameter is the maximum event date of the events to be processed
| in this run of the accounting. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_accounting_mode
| This parameter is the "accounting mode" that the accounting is being
| run in. This parameter will never be NULL.
| +-------+------------------------------------------------------------+
| | Value | Meaning |
| +-------+------------------------------------------------------------+
| | 'D' | The accounting is being run in "draft mode". Draft mode is |
| | | used to examine what the accounting entries would look for |
| | | an event without actually creating the accounting entries. |
| | | without actually creating the accounting entries. |
| | 'F' | The accounting is being run in "final mode". Final mode is |
| | | used to create accounting entries. |
| +-------+------------------------------------------------------------+
| p_valuation_method
| This parameter is unused by AP. This parameter is purely informational.
| This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
| not include events incompatible with this parameter.
| p_security_id_int_1
| This parameter is unused by AP.
| p_security_id_int_2
| This parameter is unused by AP.
| p_security_id_int_3
| This parameter is unused by AP.
| p_security_id_char_1
| This parameter is unused by AP.
| p_security_id_char_2
| This parameter is unused by AP.
| p_security_id_char_3
| This parameter is unused by AP.
| p_report_request_id
| This parameter is the concurrent request ID of the concurrent request
| that is this run of the accounting. This parameter is used to specify
| which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
| this run of the accounting. This parameter will never be NULL.
| KNOWN ISSUES:
|
| NOTES:
| 1) This procedure is run in final mode and draft mode.
| 2) This procedure is run in batch mode but not in document mode.
| 3) This procedure is in its own commit cycle.
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 23rd Feb, 2011 Saptagirish Pabolu Code changed against the Bug#11075192
*==================================================================================*/
PROCEDURE preaccounting
( p_application_id IN NUMBER
, p_ledger_id IN NUMBER
, p_process_category IN VARCHAR2
, p_end_date IN DATE
, p_accounting_mode IN VARCHAR2
, p_valuation_method IN VARCHAR2
, p_security_id_int_1 IN NUMBER
, p_security_id_int_2 IN NUMBER
, p_security_id_int_3 IN NUMBER
, p_security_id_char_1 IN VARCHAR2
, p_security_id_char_2 IN VARCHAR2
, p_security_id_char_3 IN VARCHAR2
, p_report_request_id IN NUMBER
)
IS
l_profile varchar2(1);
Select 1 from dual
where exists
(SELECT mp.organization_id
FROM mtl_parameters mp,
gmf_fiscal_policies gfp,
org_organization_definitions ood,
gl_item_cst gic,
gmf_period_statuses gps,
cm_mthd_mst cmm
WHERE mp.process_enabled_flag = 'Y'
AND gfp.legal_entity_id = ood.legal_entity
AND gfp.legal_entity_id = l_security_id_int_3
AND mp.organization_id = ood.organization_id
AND gic.organization_id = mp.organization_id
AND gic.cost_type_id = gps.cost_type_id
AND gic.final_flag = 0
AND gic.end_date <= gps.end_date
AND gps.legal_entity_id = gfp.legal_entity_id
AND cmm.cost_type_id = gps.cost_type_id
AND cmm.cost_mthd_code = l_valuation_method
AND gps.start_date <= p_end_date
AND gps.end_date >= p_end_date);
CURSOR cost_update_run (l_security_id_int_3 NUMBER, l_valuation_method varchar2) IS
SELECT count(mp.organization_id)
FROM mtl_parameters mp,
gmf_fiscal_policies gfp,
org_organization_definitions ood,
gl_item_cst gic,
gmf_period_statuses gps,
cm_mthd_mst cmm
WHERE mp.process_enabled_flag = 'Y'
AND gfp.legal_entity_id = ood.legal_entity
AND gfp.legal_entity_id = l_security_id_int_3
AND mp.organization_id = ood.organization_id
AND gic.organization_id = mp.organization_id
AND gic.cost_type_id = gps.cost_type_id
AND gic.end_date <= gps.end_date
AND gps.legal_entity_id = gfp.legal_entity_id
AND cmm.cost_type_id = gps.cost_type_id
AND cmm.cost_mthd_code = l_valuation_method
AND gps.start_date <= p_end_date
AND gps.end_date >= p_end_date;
SELECT DISTINCT cost_mthd_code, eh.legal_entity_id
FROM gmf_xla_extract_headers eh, cm_mthd_mst cmm, gmf_ledger_valuation_methods vm
WHERE eh.valuation_cost_type_id = cmm.cost_type_id
AND vm.ledger_id = eh.ledger_id
AND vm.cost_type_id = eh.valuation_cost_type_id
AND eh.ledger_id = p_ledger_id
AND eh.accounted_flag IS NOT NULL
AND eh.transaction_date <= p_end_date
AND cmm.cost_mthd_code = NVL(p_valuation_method, cmm.cost_mthd_code)
AND eh.legal_entity_id = NVL(p_security_id_char_3, eh.legal_entity_id);
OPEN cost_update_run( rec.legal_entity_id, rec.cost_mthd_code);
FETCH cost_update_run INTO l_count;
CLOSE cost_update_run;
If l_count >= 1 THEN -- cost update has been run
OPEN check_cost_not_frozen( rec.legal_entity_id, rec.cost_mthd_code);
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_process_category
| This parameter is the "process category" of the events to account.
| This parameter is purely informational. This procedure selects from
| the XLA_ENTITY_EVENTS_V view, which does not include events
| incompatible with this parameter.Possible values are as following:
| +------------+-------------------------------+
| | Value | Meaning |
| +------------+-------------------------------+
| | 'Invoices' | process invoices |
| | 'Payments' | process payments and receipts |
| | 'All' | process everything |
| +------------+-------------------------------+
| p_end_date
| This parameter is the maximum event date of the events to be processed
| in this run of the accounting. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_accounting_mode
| This parameter is the "accounting mode" that the accounting is being
| run in. This parameter will never be NULL.
| +-------+-------------------------------------------------------------+
| | Value | Meaning |
| +-------+-------------------------------------------------------------+
| | 'D' | The accounting is being run in "draft mode". Draft mode is |
| | | used to examine what the accounting entries would look for |
| | | an event without actually creating the accounting entries. |
| | 'F' | The accounting is being run in "final mode". Final mode is |
| | | used to create accounting entries. |
| +-------+-------------------------------------------------------------+
| p_valuation_method
| This parameter is unused by AP. This parameter is purely informational
| This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
| not include events incompatible with this parameter.
| p_security_id_int_1
| This parameter is unused by AP.
| p_security_id_int_2
| This parameter is unused by AP.
| p_security_id_int_3
| This parameter is unused by AP.
| p_security_id_char_1
| This parameter is unused by AP.
| p_security_id_char_2
| This parameter is unused by AP.
| p_security_id_char_3
| This parameter is unused by AP.
| p_report_request_id
| This parameter is the concurrent request ID of the concurrent request
| that is this run of the accounting. This parameter is used to specify
| which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
| this run of the accounting. This parameter will never be NULL.
|
| KNOWN ISSUES:
|
| NOTES:
| 1) This procedure is run in final mode and draft mode.
| 2) This procedure is run in batch mode but not in document mode.
| 3) This procedure is in its own commit cycle.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE postaccounting
( p_application_id IN NUMBER
, p_ledger_id IN NUMBER
, p_process_category IN VARCHAR2
, p_end_date IN DATE
, p_accounting_mode IN VARCHAR2
, p_valuation_method IN VARCHAR2
, p_security_id_int_1 IN NUMBER
, p_security_id_int_2 IN NUMBER
, p_security_id_int_3 IN NUMBER
, p_security_id_char_1 IN VARCHAR2
, p_security_id_char_2 IN VARCHAR2
, p_security_id_char_3 IN VARCHAR2
, p_report_request_id IN NUMBER
)
IS
BEGIN
--NULL;
FOR i in (SELECT DISTINCT process_category
FROM gmf_xla_event_model em
WHERE exists (SELECT 'X'
FROM xla_post_acctg_events_v ae
WHERE ae.event_class_code = em.event_class_code)
)
LOOP
g_log_msg := 'Post-Processing for '|| i.process_category;
UPDATE gmf_xla_extract_headers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_inventory_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
UPDATE gmf_transaction_valuation
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
(transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_inventory_transactions
and xpae.event_class_code = gxem.event_class_code
)
AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
;
g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
/* UPDATE mtl_material_transactions
SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, program_update_date = SYSDATE
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_id
IN (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code NOT IN ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP') ); */
/* UPDATE mtl_material_transactions
SET opm_costed_flag = NULL
, program_update_date = SYSDATE
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_source_type_id in (2, 4, 8, 9, 10, 13)
AND transaction_action_id in (2, 28)
AND transfer_transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code in ('SUBINV_XFER')); */
/* UPDATE mtl_material_transactions
SET shipment_costed = 'Y'
, program_update_date = SYSDATE
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_source_type_id in (7, 8, 13)
AND transaction_action_id in (12, 21)
AND transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code in ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP')); */
Subinventory transfer. These multiple updates caused deadlock based on how events were distributed amongst workers.
Old queries are now used to insert data in gmf_inv_Txn_flags_gt. And a single update is used to update all mmt rows.
Bug 9066162 new logic follows. We need to sort duplicates. Union can not be used since hard coded values are used for
opm_costed_flag and shipment_costed. Events for these may or may not be processed by the same worker.
*/
Insert into gmf_inv_Txn_flags_gt (transaction_id, opm_costed_flag, shipment_costed)
SELECT DISTINCT xpae.SOURCE_ID_INT_1 transaction_id,
DECODE(G_accounting_mode, 'D', 'D', 'F', NULL) opm_costed_flag,
NULL shipment_costed
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code NOT IN ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP')
UNION ALL
SELECT DISTINCT mmt1.transfer_transaction_id, NULL, NULL
FROM mtl_material_transactions mmt1
WHERE G_accounting_mode = 'F'
AND mmt1.transaction_source_type_id in (2, 4, 8, 9, 10, 13)
AND mmt1.transaction_action_id in (2, 28)
AND mmt1.transaction_id IN
(SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code in ('SUBINV_XFER')
)
UNION ALL
SELECT DISTINCT mmt2.transaction_id, 'N', 'Y'
FROM mtl_material_transactions mmt2
WHERE G_accounting_mode = 'F'
AND mmt2.transaction_source_type_id in (7, 8, 13)
AND mmt2.transaction_action_id in (12, 21)
AND mmt2.transaction_id IN
(SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_inventory_transactions
AND xpae.event_class_code = gxem.event_class_code
AND xpae.event_class_code in ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP'))
;
Update gmf_inv_Txn_flags_gt
set opm_costed_flag = NULL,
shipment_costed = 'Y'
where transaction_id IN (select transaction_id
from gmf_inv_Txn_flags_gt
group by transaction_id
having count(transaction_id) > 1);
delete from gmf_inv_Txn_flags_gt
where rowid IN (select min(rowid) from gmf_inv_Txn_flags_gt
group by transaction_id
having count(transaction_id) > 1);
update mtl_material_transactions mmt
set (opm_costed_flag, shipment_costed) = (select decode(mmt.opm_costed_flag,NULL,NULL,'D',decode(txngt.opm_costed_flag, NULL, NULL,'D'),'N',txngt.opm_costed_flag)
,decode(mmt.shipment_costed,'Y','Y',decode(txngt.shipment_costed,NULL,NULL,txngt.shipment_costed))
from gmf_inv_Txn_flags_gt txngt
where mmt.transaction_id = txngt.transaction_id)
, program_update_date = SYSDATE
/* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
-- , last_updated_by = g_user_id
-- , last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
where transaction_id IN (select txngt.transaction_id from gmf_inv_Txn_flags_gt txngt);
g_log_msg := sql%rowcount || ' rows of Inv Transactions updated to set OPM_costed_flag and shipment_costed flag in mtl_material_transactions table';
| update gmf_invoice_distributions table
| 5-Aug-2009 pmarada bug 8642337 LCM-OPM Integration, updating LC adjustment
| tables accounted flag and final accounted date.
| 29-Sep-2011 phiriyan Bug 13033147. Modified not to update MMT row when
| event class / transaction type is related to Receiving txn.
*===========================================================================*/
PROCEDURE process_pur_txns(p_event VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(100) := 'process_pur_txns';
select count(*) into record_count from xla_events_int_gt ;
delete_IPV_ERV_DUP_RECORDS ;
UPDATE gmf_xla_extract_headers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_purchasing_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
UPDATE gmf_transaction_valuation
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
(transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_purchasing_transactions
and xpae.event_class_code = gxem.event_class_code
)
AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
;
g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
UPDATE mtl_material_transactions
SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
/* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
-- , last_updated_by = g_user_id
-- , last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_purchasing_transactions
and xpae.event_class_code = gxem.event_class_code
/*Bug 13033147 - Added following condition to exclude Receiving txns from this update*/
and gxem.transaction_source_type_id <> 999
)
;
g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
UPDATE gmf_rcv_accounting_txns
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE accounting_txn_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_purchasing_transactions
and gxem.transaction_type IN ('RECEIVING_RECEIVE','RECEIVING_DELIVER_EXPENSE',
'RECEIVING_RET_TO_VENDOR','DELIVER_EXP_RET_TO_RECEIVING',
'RECEIVING_LOG_RET_TO_VENDOR', 'RECEIVING_LOG_RECEIVE',
'RECEIVING_ADJUST_RECEIVE', 'RECEIVING_ADJUST_DELIVER')
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_rcv_accounting_txns table';
/* update the accounting */
UPDATE gmf_invoice_distributions SET
Accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
,last_update_date = sysdate
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_application_id= g_prog_appl_id
,program_id = g_program_id
,request_id = g_request_id
WHERE distribution_id
IN (SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_purchasing_transactions
AND gxem.transaction_type IN ('PAYABLES_INVOICE_IPV_ADJ','PAYABLES_INVOICE_ERV_ADJ')
AND xpae.event_class_code = gxem.event_class_code
);
g_log_msg := sql%rowcount || ' rows updated in gmf_invoice_distributions table';
UPDATE gmf_lc_adj_transactions SET
Accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
,last_update_date = sysdate
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_application_id= g_prog_appl_id
,program_id = g_program_id
,request_id = g_request_id
WHERE adj_transaction_id
IN (SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_purchasing_transactions
AND gxem.transaction_type IN ('LC_ADJUSTMENT_EXP_DELIVER','LC_ADJUSTMENT_DELIVER','LC_ADJUSTMENT_RECEIVE')
AND xpae.event_class_code = gxem.event_class_code
);
g_log_msg := sql%rowcount || ' rows updated in gmf_lc_adj_transactions table';
UPDATE gmf_lc_actual_cost_adjs SET
accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
,last_update_date = sysdate
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_application_id= g_prog_appl_id
,program_id = g_program_id
,request_id = g_request_id
WHERE adj_transaction_id
IN (SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_purchasing_transactions
AND gxem.transaction_type IN ('LC_ADJUSTMENT_VALUATION')
AND xpae.event_class_code = gxem.event_class_code
);
g_log_msg := sql%rowcount || ' rows updated in gmf_lc_actual_cost_adjs table';
UPDATE gmf_lc_lot_cost_adjs SET
accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
,last_update_date = sysdate
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_application_id= g_prog_appl_id
,program_id = g_program_id
,request_id = g_request_id
WHERE adj_transaction_id
IN (SELECT xpae.SOURCE_ID_INT_1
FROM xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE gxem.process_category = G_purchasing_transactions
AND gxem.transaction_type IN ('LC_ADJUSTMENT_VALUATION')
AND xpae.event_class_code = gxem.event_class_code
);
g_log_msg := sql%rowcount || ' rows updated in gmf_lc_lot_cost_adjs table';
UPDATE gmf_xla_extract_headers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
UPDATE gmf_transaction_valuation
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
(transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and xpae.event_class_code = gxem.event_class_code
)
AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
;
g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
UPDATE mtl_material_transactions
SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
/* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
-- , last_updated_by = g_user_id
-- , last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and gxem.event_class_code = G_batch_material
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
UPDATE gmf_incoming_material_layers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
WHERE (mmt_transaction_id)
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_incoming_material_layers table';
UPDATE gme_resource_txns
SET posted_ind = DECODE(G_accounting_mode, 'D', posted_ind, 'F', 1)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE poc_trans_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and gxem.event_class_code = G_batch_resource
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gme_resource_txns table';
UPDATE gme_batch_header
SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
WHERE batch_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_production_transactions
and gxem.event_class_code = g_batch_close
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gme_batch_header table';
UPDATE gmf_xla_extract_headers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_order_management
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
UPDATE gmf_transaction_valuation
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
(transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_order_management
and xpae.event_class_code = gxem.event_class_code
)
AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
;
g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
UPDATE mtl_material_transactions
SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
/* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
-- , last_updated_by = g_user_id
-- , last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_order_management
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
UPDATE cst_cogs_events
SET costed = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id = g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
exists (SELECT 'x'
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem,
gmf_xla_extract_headers eh
WHERE
eh.transaction_id = xpae.SOURCE_ID_INT_1
AND eh.ledger_id = xpae.SOURCE_ID_INT_2
AND eh.valuation_cost_type_id = xpae.SOURCE_ID_INT_3
AND eh.event_class_code = xpae.SOURCE_ID_CHAR_1
AND eh.transaction_action_id = 36
AND gxem.process_category = G_order_management
AND xpae.event_class_code = gxem.event_class_code
AND cogs_om_line_id = eh.source_line_id
AND cce.mmt_transaction_id = eh.transaction_id
)
;
g_log_msg := sql%rowcount || ' rows updated in cst_cogs_events table';
UPDATE gmf_xla_extract_headers
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
UPDATE gmf_transaction_valuation
SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE
(transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
in (SELECT
xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and xpae.event_class_code = gxem.event_class_code
)
AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
;
g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
UPDATE mtl_material_transactions
SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
WHERE transaction_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
SELECT cost_type
INTO l_cost_method_type
FROM cm_mthd_mst
WHERE cost_type_id = (SELECT xpae.SOURCE_ID_INT_3
FROM xla_post_acctg_events_v xpae
WHERE rownum = 1)
;
UPDATE gmf_lot_cost_adjustments
SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE adjustment_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and gxem.transaction_type = 'LOT_COST_ADJUSTMENTS' /* Bug#5597804 ANTHIYAG 06-Nov-2006 */
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_lot_cost_adjustments table';
UPDATE gmf_period_balances
SET costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE period_balance_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and gxem.transaction_type = 'COST_REVALUATIONS' /*changed LOT_COST_ADJUSTMENTS to COST_REVALUATIONS jboppana*/
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gmf_period_balances table';
UPDATE cm_adjs_dtl
SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE cost_adjust_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and gxem.transaction_type = 'ACTUAL_COST_ADJUSTMENTS'
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in cm_adjs_dtl table';
UPDATE gl_aloc_dtl
SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_application_id= g_prog_appl_id
, program_id = g_program_id
, request_id = g_request_id
WHERE allocdtl_id
in (SELECT
xpae.SOURCE_ID_INT_1
FROM
xla_post_acctg_events_v xpae,
gmf_xla_event_model gxem
WHERE
gxem.process_category = G_revaluation_transactions
and gxem.transaction_type = 'GL_COST_ALLOCATIONS'
and xpae.event_class_code = gxem.event_class_code
)
;
g_log_msg := sql%rowcount || ' rows updated in gl_aloc_dtl table';
SELECT organization_id
INTO l_security_id_int_1
FROM mtl_material_transactions
WHERE transaction_id = p_source_id_int_1;
SELECT rcv_transaction_id
INTO l_source_id_int_1
FROM mtl_material_transactions
WHERE transaction_id = p_source_id_int_1
;
SELECT event_source_id
INTO l_source_id_int_1
FROM gmf_rcv_accounting_txns
WHERE accounting_txn_id = p_source_id_int_1;
SELECT ship_header_id,
ship_line_id
INTO l_ship_header_id,
l_ship_line_id
FROM gmf_lc_adj_transactions
WHERE adj_transaction_id = p_source_id_int_1;
SELECT a.batch_id,
a.batchstep_id,
grt.resources,
a.batchstep_activity_id,
a.batchstep_resource_id
INTO l_batch_id,
l_batchstep_id,
l_resource,
l_batchstep_activity_id,
l_batchstep_resource_id
FROM gme_batch_step_resources a,
gme_resource_txns grt
WHERE grt.poc_trans_id = p_source_id_int_1
AND grt.line_id = a.batchstep_resource_id;
| PROCEDURE - delete_IPV_ERV_DUP_RECORDS
|
| DESCRIPTION
|
| PRAMETERS
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 08-Jun-2010 Vpedarla Bug: 9795216 Created the procedure
| 16-Sep-2010 patangya Bug: 10121825 made changes to delete unwanted Cost Revaluation transactions.
*===========================================================================*/
PROCEDURE delete_IPV_ERV_DUP_RECORDS IS
l_procedure_name CONSTANT VARCHAR2(100) := 'delete_IPV_ERV_DUP_RECORDS';
g_log_msg := 'Begin of procedure GMF_XLA_PKG.delete_IPV_ERV_DUP_RECORDS ' ;
INSERT INTO xla_events_int_gt
(
entity_id
,application_id
,ledger_id
,entity_code
,event_status_code
,event_id
)
SELECT
xe.entity_id
,xe.application_id
,xah.ledger_id
,'PURCHASING'
,xe.event_status_code
,xe.event_id
FROM xla_events xe, xla_ae_headers xah
WHERE xe.application_id = 555
AND xah.application_id = 555
AND xe.event_id not in ( select event_id from gmf_xla_extract_headers where EVENT_TYPE_CODE in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' ) )
AND xe.event_type_code in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' )
AND xe.event_status_code = 'N' /* Bug14325989*/
AND xe.process_status_code = 'P' /* Bug14325989*/
AND xe.event_id = xah.event_id
-- Bug: 9681484 Vpedarla Added the below union to delete records of Actual Cost Adj., which are not valid.
Union
SELECT
xe.entity_id
,xe.application_id
,xah.ledger_id
,'REVALUATION'
,xe.event_status_code
,xe.event_id
FROM xla_events xe, xla_ae_headers xah
WHERE xe.application_id = 555
AND xah.application_id = 555
AND xe.event_type_code = 'ACTCOSTADJ'
AND xe.event_status_code = 'N'
AND xe.process_status_code = 'P'
AND xe.event_id = xah.event_id
AND xe.event_id not in ( select event_id
from gmf_xla_extract_headers
where entity_code = 'REVALUATION'
and EVENT_CLASS_CODE = 'ACTCOSTADJ' )
-- Bug: 10121825 made changes to delete unwanted Cost Revaluation transactions.
UNION
SELECT
xe.entity_id
,xe.application_id
,xah.ledger_id
,'REVALUATION'
,xe.event_status_code
,xe.event_id
FROM xla_events xe, xla_ae_headers xah
WHERE xe.application_id = 555
AND xah.application_id = 555
AND xe.event_type_code = 'COSTREVAL'
AND xe.event_status_code = 'N'
AND xe.process_status_code = 'P'
AND xe.event_id = xah.event_id
AND xe.event_id not in ( select event_id
from gmf_xla_extract_headers
where entity_code = 'REVALUATION'
and EVENT_CLASS_CODE = 'COSTREVAL'
and EVENT_TYPE_CODE = 'COSTREVAL' )
;
select count(*) into record_count from xla_events_int_gt ;
xla_events_pkg.delete_bulk_events( p_application_id => 555);
delete from xla_events_int_gt ;
g_log_msg := 'End of procedure GMF_XLA_PKG.delete_IPV_ERV_DUP_RECORDS ' ;
END delete_IPV_ERV_DUP_RECORDS;
SELECT eh.event_id
FROM gmf_xla_extract_headers eh,
gmf_lot_cost_adjustments lc_adj
WHERE eh.entity_code = 'REVALUATION'
AND eh.EVENT_CLASS_CODE = 'LOTCOSTADJ'
AND eh.EVENT_TYPE_CODE = 'LOTCOSTADJ'
AND eh.transaction_id = lc_adj.adjustment_id
AND eh.ledger_id = p_ledger_id
AND eh.valuation_cost_type_id = p_valuation_cost_type_id
AND eh.transaction_date >= p_date_start
AND eh.transaction_date <= p_date_end
AND eh.transaction_source_type_id = 0
AND lc_adj.delete_mark = 1
AND NVL(lc_adj.gl_posted_ind,0) <> 1;
INSERT INTO xla_events_int_gt
(entity_id
,application_id
,ledger_id
,entity_code
,event_status_code
,event_id
)
SELECT
xe.entity_id
,xe.application_id
,p_ledger_id
,'REVALUATION'
,xe.event_status_code
,xe.event_id
FROM xla_events xe
WHERE xe.application_id = 555
AND xe.event_id = l_event.event_id
AND xe.event_type_code = 'LOTCOSTADJ';
DELETE FROM gmf_xla_extract_lines
WHERE Header_id = (SELECT header_id FROM gmf_xla_extract_headers
WHERE entity_code = 'REVALUATION'
AND event_class_code = 'LOTCOSTADJ'
AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
AND event_id = l_event.event_id);
DELETE FROM gmf_xla_extract_headers
WHERE entity_code = 'REVALUATION'
AND event_class_code = 'LOTCOSTADJ'
AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
AND event_id = l_event.event_id;
xla_events_pkg.delete_bulk_events( p_application_id => 555);
delete from xla_events_int_gt ;