[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;
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
g_log_msg := i.transaction_id || '/' ||
i.transaction_source_type_id || '/' || i.transaction_action_id || '/' ||
i.organization || '/' || i.transfer_type || '/' ||
i.transfer_price_flag || '/' || i.fob_point || '/' ||
i.transaction_type || '/' ||
i.source_document_id || '/' ||
i.source_line_id
;
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 */
, NULL -- SOURCE_ID_CHAR_2
, 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 */
)
;
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 */
)
WHEN MATCHED THEN
UPDATE SET
eh.transaction_quantity = ehgt.transaction_quantity
, 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
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 */
)
;
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
gmf_xla_extract_lines_s.NEXTVAL -- line_id
, 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;
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
)
;
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
|
*===========================================================================*/
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
BEGIN
NULL;
| 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 /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
, 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')
)
;
g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
g_log_msg := 'Updating receiving rows of subInv/Staging Transfers updated in mtl_material_transactions table';
UPDATE mtl_material_transactions
SET opm_costed_flag = NULL
, program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
, 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')
)
;
g_log_msg := sql%rowcount || ' receiving rows of subInv/Staging Transfers updated in mtl_material_transactions table';
UPDATE mtl_material_transactions
SET shipment_costed = 'Y'
, program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
, 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')
)
;
g_log_msg := sql%rowcount || ' rows of Inv Transfers updated to set shipment_costed flag to Y in mtl_material_transactions table';
| update gmf_invoice_distributions table
*===========================================================================*/
PROCEDURE process_pur_txns(p_event VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(100) := 'process_pur_txns';
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 */
, 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
)
;
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_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 */
, 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 */
, 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 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;