R12: Discarding GST MISC Lines and then Changing the Item Amount Creates Orphans (Doc ID 2359370.1)

Last updated on APRIL 07, 2025

Applies to:

Symptoms

In R12, users can create orphan events, invoice dists and orphan xla_distribution_links (XDL) if they discard MISCELLANEOUS lines generated for GST and then change the amount for the ITEM line that the MISC lines were created for. See steps to reproduce below for more details.  The following GDF signatures are available to capture and fix these corruptions:

Results for Signature ID 82: Orphan invoice distributions with no corresponding invoice line.
GDF: 17983453, Note: 1060644.1
Script Name: ap_orphan_dists_s.sql

Results for Signature ID 101: Records in XLA_Distribution_Links which do not tie back to a valid record in ap_invoice_distributions_all or ap_self_assessed_tax_dists_all tables
GDF: 17487602, Note: 1291521.1
Script Name: ap_orphan_xdl_s.sql

Results for Signature ID 102: Orphan Accounting Events, which have records in the XLA_Events table but are not stamped on any Transaction tables
GDF: 24692565, Note: 788135.1
Script Name: ap_Orphan_Events_Sel.sql

Steps to Reproduce

===============

1. Create an Invoice amount 1220 with 1 Item line for 1000 Rupees.
2. Save that invoice.
3. Select that invoice in the Invoice Header section and then click on Tools
> India Tax Details
4. Enter the Tax Category and Intended Use fields.
5. Save and note the 2 Tax Rate Names that default.
6. Exit the India Tax Details form
7. Click on the Invoice Workbench Lines tab and click on Save. Note that
there are 2 Misc lines created.
8. Validate and account the invoice.

select invoice_id, line_number, line_type_lookup_code, amount
from ap_invoice_lines_all
where invoice_id = 1594317;

1594317 1 ITEM 1000
1594317 2 MISCELLANEOUS 120
1594317 3 MISCELLANEOUS 100

select line_type_lookup_code, invoice_distribution_id, invoice_line_number,
amount, accounting_event_id
from ap_invoice_distributions_all
where invoice_id = 1594317;

MISCELLANEOUS 4734631 2 120 5363729
MISCELLANEOUS 4734632 3 100 5363729
ITEM 4734633 1 1000 5363729

select entity_id,event_id, event_status_code, event_number
from xla_events
where entity_id = 5282268;

5282268 5363729 P 1

12. Discard both MISC lines and save

select invoice_id, line_number, line_type_lookup_code, amount
from ap_invoice_lines_all
where invoice_id = 1594317;

1594317 1 ITEM 1000
1594317 2 MISCELLANEOUS 0
1594317 3 MISCELLANEOUS 0

select line_type_lookup_code, invoice_distribution_id, invoice_line_number,
amount, accounting_event_id
from ap_invoice_distributions_all
where invoice_id = 1594317;

MISCELLANEOUS 4734631 2 120 5363729
MISCELLANEOUS 4734632 3 100 5363729
ITEM 4734633 1 1000 5363729
MISCELLANEOUS 4735631 2 -120
MISCELLANEOUS 4735632 3 -100

select entity_id,event_id, event_status_code, event_number
from xla_events
where entity_id = 5282268;

5282268 5363729 P 1

13. Go to the Item line 1, change the 1000 amount to 2000, tab out and save.

Note the original validated and accounted misc lines and their distributions
are deleted
and replaced with MISC lines recalculated based on the 2000 amount.
14. Go to the Item line 1, change the 2000 amount back to 1000, tab out and
save.
Note the misc lines and their distributions again are deleted
and replaced with MISC lines recalculated based on the 1000 amount.

select invoice_id, line_number, line_type_lookup_code, amount
from ap_invoice_lines_all
where invoice_id = 1594317;

1594317 1 ITEM 1000
1594317 2 MISCELLANEOUS 120
1594317 3 MISCELLANEOUS 100

select line_type_lookup_code, invoice_distribution_id, invoice_line_number,
amount, accounting_event_id
from ap_invoice_distributions_all
where invoice_id = 1594317;

MISCELLANEOUS 4735635 2 120
MISCELLANEOUS 4735636 3 100
ITEM 4734633 1 1000 5363729

This shows that accounted MISC dists 4734631 and 4734632 were deleted by the
above steps.
And replaced with unvalidated unaccounted misc dists 4735635 and 4735636.
This step
has reproduced the corruption for:

Results for Signature ID 101: Records in XLA_Distribution_Links which do not
tie back to a valid record in ap_invoice_distributions_all or
ap_self_assessed_tax_dists_all tables
GDF: 17487602, Note: 1291521.1

select entity_id,event_id, event_status_code, event_number
from xla_events
where entity_id = 5282268;

5282268 5363729 P 1

15. Validate and Account the NEW MISC distributions.

select invoice_id, line_number, line_type_lookup_code, amount
from ap_invoice_lines_all
where invoice_id = 1594317;

1594317 1 ITEM 1000
1594317 2 MISCELLANEOUS 120
1594317 3 MISCELLANEOUS 100

select line_type_lookup_code, invoice_distribution_id, invoice_line_number,
amount, accounting_event_id
from ap_invoice_distributions_all
where invoice_id = 1594317;

MISCELLANEOUS 4735635 2 120 5364729
MISCELLANEOUS 4735636 3 100 5364729
ITEM 4734633 1 1000 5363729

Note: The new MISC dists are accounted in new event, separate from the Item
line.

select entity_id,event_id, event_status_code, event_number
from xla_events
where entity_id = 5282268;

5282268 5363729 P 1
5282268 5364729 P 2

16. Discard both MISC lines and save

17. Go to the Item line 1, change the 1000 amount to 2000, tab out and save.

select max(log_sequence) from fnd_log_messages ; -- end

260201478
260202390

select * from fnd_log_messages where log_sequence between 260201478 and
260202390;

Note the original validated and accounted misc lines and their distributions
are deleted
and replaced with MISC lines recalculated based on the 2000 amount.

The original validated and accounted misc lines and their distributions are
deleted.
And since the MISC lines were all that was attached to the event id, the
event is now orphan!

This step reproduces the corruption for :

Results for Signature ID 102: Orphan Accounting Events, which have records in
the XLA_Events table but are not stamped on any Transaction tables
GDF: 24692565, Note: 788135.1

select invoice_id, line_number, line_type_lookup_code, amount
from ap_invoice_lines_all
where invoice_id = 1594317;

1594317 1 ITEM 2000
1594317 2 MISCELLANEOUS 240
1594317 3 MISCELLANEOUS 200

select line_type_lookup_code, invoice_distribution_id, invoice_line_number,
amount, accounting_event_id
from ap_invoice_distributions_all
where invoice_id = 1594317;

ITEM 4734633 1 1000 5363729
MISCELLANEOUS 4736633 2 240
MISCELLANEOUS 4736634 3 200

select entity_id,event_id, event_status_code, event_number
from xla_events
where entity_id = 5282268;

5282268 5363729 P 1
5282268 5364729 P 2 --> this event is orphan now!

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.