Balance fournisseur pas à jour - Mise à jour XLA

La balance fournisseurs du fournisseur MAJRECT n'est pas à jour sur l'unité opérationnelle 376 (org_id) pour la facture N003902.
La facture est validée, comptabilisée et payée.
Je retrouve bien les pièces dans GL.
La balance âgée est OK mais pas celle des fournisseurs.



 -- Vue du fournisseur
select * from ap_suppliers where vendor_id = 7775

-- Vue de la facture - récupération de l'identifiant
select * from AP_INVOICES_ALL
where invoice_num = 'N003902'

 -- lien facture-règlement
Select * from ap_invoice_payments_all
where invoice_id = 896650

-- lignes de facture
select * from ap_invoice_lines_all ail
where ail.invoice_id = 896650


-- lignes de ventilation de la facture
select * from AP_INVOICE_DISTRIBUTIONS_ALL aid
where aid.invoice_id = 896650

-- Evénements de comptabilisation de la facture dans XLA
SELECT ae.*
FROM XLA_EVENTS ae, AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE aid.accounting_event_id = ae.event_id
aND ae.application_id = 200
AND aid.invoice_id = 896650


-- Evénements de comptabilisation dans XLA
select * from xla_events where event_id in (1387951,1061875)


--Bug 6392789
select distinct aid.invoice_id,aid.ACCOUNTING_date aid_accounting_date,
XE.EVENT_DATE XE_ACCOUNTING_DATE,XE.EVENT_ID xe_accounting_event_id
from AP_INVOICE_DISTRIBUTIONS_ALL aid,XLA_EVENTS XE
where aid.ACCOUNTING_EVENT_ID = XE.EVENT_ID
and trunc(aid.ACCOUNTING_date) <> trunc(XE.EVENT_DATE)
and aid.invoice_id = 896650


SELECT *
FROM xla.XLA_transaction_entities
where entity_id = 1018835
--ou
entity_code = 'AP INVOICES'
AND source_id_int_1 = 896650

-- Données de la balance
select * from xla_trial_balances
where  source_entity_id = 1018835
and ledger_id = 2121

-- Journal XLA
select * from xla_ae_headers where ae_header_id in (1767014,2082540)

-- Lignes du journal XLA
SELECT * from xla_ae_lines where
application_id = 200
and ae_header_id in (1767014,2082540)

-- Lignes GL
select * from GL_JE_LINES
where ledger_id = 2121
and subledger_doc_sequence_value = 1175202
and subledger_doc_sequence_id =1964
and gl_sl_link_id = 8127845
and gl_sl_link_table = 'XLAJEL'

- Pièce GL
SELECT * from gl_je_headers where je_header_id = 3274834

-- Lien GL-XLA
SELECT * from xla_ae_lines
where ledger_id = 2121
--and doc_sequence_value = 1175202
--and doc_sequence_id =1964
and gl_sl_link_id = 8127845
and gl_sl_link_table = 'XLAJEL'

Après ce petit tour de visualisation des données, nous allons mettre à jour les tables XLA.

---------------------------------------------------
--- Mises à jour ------------- en 4 étapes --------

-- 1 --
select * from XLA_AE_HEADERS
WHERE event_id in (1061875,1387951)
AND ae_header_id = 1767014


UPDATE xla_ae_headers
SET accounting_date = to_date('01/05/2015','DD/MM/YYYY')
    ,period_name = 'MAY-15'
WHERE event_id = 1061875
AND ae_header_id = 1767014

-- 2 --
SELECT * from XLA_AE_LINES
WHERE ae_header_id = 1767014

UPDATE xla_ae_lines
SET accounting_date = to_date('01/05/2015','DD/MM/YYYY')
WHERE ae_header_id = 1767014


-- 3 --
SELECT * from xla_events
WHERE event_id = 1061875

UPDATE xla_events
SET event_date = to_date('01/05/2015','DD/MM/YYYY')
WHERE event_id = 1061875

-- 4 --
SELECT * from xla_trial_balances
WHERE source_entity_id = 1018835
and definition_code = 'OU_376'
AND ae_header_id = 1767014

-- Vue du règlement...
SELECT * from xla_trial_balances
WHERE applied_to_entity_id = 1018835

-- Mise à jour
UPDATE xla_trial_balances
set GL_DATE = to_date('01/05/2015','DD/MM/YYYY')
WHERE source_entity_id = 1018835
and definition_code = 'OU_376'
AND ae_header_id = 1767014