The error message of "Unable to create batch" was being presented to our users when they were either trying to manually create receipt batches or when the automated processes would run from their requests. Digging into the SQL of the package started taking me down a rabbit hole from one custom package to another, until I finally found that even though the error message being returned to the users was custom but it was being raised as a result to a call to ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH which is a completely canned Oracle procedure. Tracing through this started making me suspect the call over to arp_rw_batches_check_pkg.check_unique_batch_name but that didn't make any sense since we're talking about receipt classes as defined in ar_batch_sources which are set to AUTOMATIC for their numbering scheme in the auto_batch_numbering field. So I wind up taking a look at this piece of code a bit closer because nothing else looked possible if I was in the right spot:
SELECT COUNT(*)
INTO l_count
FROM AR_BATCHES bat
WHERE bat.batch_source_id = p_batch_source_id
AND bat.name = p_batch_name
AND ( p_row_id IS NULL
OR bat.rowid <> p_row_id );
IF ( l_count <> 0 ) THEN
FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
APP_EXCEPTION.raise_exception;
when I decide to see what the data in ar_batches shows me. Sure enough, the last_batch_num in ar_batch_sources for the batch_source_id in question was currently 5792097 and we have a sequence of batches with a name defined in ar_batches ranging from 5791705 to 5792958 which is preventing the package from successfully getting an available batch name to use in the creation of the batch as shown in these pieces of ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH:
IF ( l_auto_batch_numbering = 'AUTOMATIC' ) THEN
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;
SELECT NVL( LAST_BATCH_NUM, 0 )
INTO l_batch_rec.name
FROM ar_batch_sources
WHERE batch_source_id = l_batch_rec.batch_source_id;
This meant that the application was continuing to increment the last_batch_num value every time the users were trying to do their work, even though the batch names were already taken and it would continue to do so until it encountered a data value which did not already exist for the name field in ar_batches. The only option I could see is that we would need to manually push out the last_batch_num to match the last item in the sequence, so that a unique value would be returned the next time it would try to get a batch name (see above UPDATE statement). I didn't want to change the batch names of the items which were created months ago because then we’re changing historical data, and I can’t be 100% sure that it’s not used for reporting or other audit type functions by the business. After the appropriate approvals, we ran the below script:
UPDATE ar_batch_sources_all
SET last_batch_num = 5792958
WHERE batch_source_id = &batch_source_id
and last_batch_num = 5792097
Service was restored to the batch source after this update was applied, and all was well with the world! Then we were asked how we could make sure this doesn't happen in the future. After thinking through the answer a bit, I've come up with the below script which needs to go into a daily alert for us:
select a.batch_source_id, a.last_batch_num, a.org_id from ar_batch_sources_all a, ar_batches_all b
where (a.status not in ('I') or a.status is null)
and a.end_date_active is null
and a.batch_source_id = b.batch_source_id
and a.org_id = b.org_id
and b.name > a.last_batch_num
This was one of those problems where it's incredibly challenging, but also rewarding when you get to the end of the day having solved a pretty big problem and learning a lot in the process. Years ago I would've ran a trace and handed it over to development to figure out, but I've gained some seasoning since then and decided that I'd do as much "dirty work" as possible before I had to escalate up so I ended up digging down through 7 layers of code to find the actual root cause!
No comments:
Post a Comment