JournalBatchPVO is coded incorrectly

Scott Garman

Organization Williams

the following Oracle PVO "FscmTopModelAM.FinGlJrnlEntriesAM.JournalBatchPVO" is missing an outer join between

GL_JE_BATCHES.CREATED_BY and PER_USERS .USERNAME

this outer join is coded correctly when joining to get last_updated_by if you review the attached code

"AND V26406376.LAST_UPDATED_BY = LastUpdatedByUser.USERNAME(+)" — outer join correclty added

but it was missed here

"AND V26406376.CREATED_BY = CreatedByUser.USERNAME" — should have outer join (+) here

the outer joins must be there for last_updated_by and created by because usernames can and will change.

BICC PVO Query.docx

JournalBatchPVO_QUERY.txt

JournalLinePVO_Query.txt

Why is this important!!! If a user has created a journal batch and they ask to change their username (which is very common if last name changes for example.)

then their batch they created is dropped from the view. This then falsely reports financial data.

see attached sql to see this ddl code wrong in JournalBatchPVO_QUERY.txt and coded correctly with outer joins in JournalLinePVO_Query.txt

select T289094.C341721644 as c1
T289094.C410795685 as c2
T289094.C149982230 as c3
T289094.C205427818 as c4
T289094.C37724958 as c5
T289094.C220559414 as c6
T289094.C450635170 as c7
T289094.C366088851 as c8
T289094.C418868757 as c9
T289094.C504899489 as c10
T289094.C113766347 as c11
T289094.C511390838 as c12
T289094.C329391279 as c13
T289094.C112570157 as c14
T289094.C444374418 as c15
T289094.C67982135 as c16
T289094.C154584560 as c17
T289094.C22546598 as c18
T289094.C207855201 as c19
T289094.C365742078 as c20
T289094.C463996767 as c21
T289094.C392064429 as c22
T289094.C94232224 as c23
T289094.C13323357 as c24
T289094.C403430244 as c25
from
(SELECT V26406376.ACCOUNTED_PERIOD_TYPE AS C341721644
V26406376.ACTUAL_FLAG AS C410795685
V26406376.APPROVAL_STATUS_CODE AS C149982230
V26406376.CHART_OF_ACCOUNTS_ID AS C205427818
V26406376.CONTROL_TOTAL AS C37724958
V26406376.CREATED_BY AS C220559414
V26406376.CREATION_DATE AS C450635170
V26406376.DEFAULT_EFFECTIVE_DATE AS C366088851
V26406376.DESCRIPTION AS C418868757
V26406376.GROUP_ID AS C504899489
V26406376.JE_BATCH_ID AS C113766347
V26406376.LAST_UPDATE_DATE AS C511390838
V26406376.LAST_UPDATED_BY AS C329391279
V26406376.NAME AS C112570157
V26406376.POSTED_DATE AS C444374418
V26406376.RUNNING_TOTAL_ACCOUNTED_CR AS C67982135
V26406376.RUNNING_TOTAL_ACCOUNTED_DR AS C154584560
V26406376.RUNNING_TOTAL_CR AS C22546598
V26406376.RUNNING_TOTAL_DR AS C207855201
((DECODE(V26406376.FUNDS_STATUS_CODE	null
((DECODE(V26406376.STATUS	'u'
((DECODE(V26406376.STATUS	'u'
BatchApprovedBy.DISPLAY_NAME AS C94232224
CreatedByPersonName.DISPLAY_NAME AS C13323357
LastUpdatedByPersonName.DISPLAY_NAME AS C403430244
V26406376.STATUS AS BDep_JournalBatchStatus0
BatchApprovedBy.PERSON_NAME_ID AS PKA_BatchApprovedByPersonName0
BatchApprovedBy.EFFECTIVE_START_DATE AS PKA_BatchApprovedByEffectiveS0
BatchApprovedBy.EFFECTIVE_END_DATE AS PKA_BatchApprovedByEffectiveE0
CreatedByPersonName.PERSON_NAME_ID AS PKA_CreatedByPersonNamePerson0
CreatedByPersonName.EFFECTIVE_START_DATE AS PKA_CreatedByPersonNameEffect0
CreatedByPersonName.EFFECTIVE_END_DATE AS PKA_CreatedByPersonNameEffect1
LastUpdatedByPersonName.PERSON_NAME_ID AS PKA_LastUpdatedByPersonNamePe0
LastUpdatedByPersonName.EFFECTIVE_START_DATE AS PKA_LastUpdatedByPersonNameEf0
LastUpdatedByPersonName.EFFECTIVE_END_DATE AS PKA_LastUpdatedByPersonNameEf1
FROM
GL_JE_BATCHES V26406376
PER_PERSON_NAMES_F_V BatchApprovedBy
PER_USERS CreatedByUser
PER_PERSON_NAMES_F_V CreatedByPersonName
PER_USERS LastUpdatedByUser
PER_PERSON_NAMES_F_V LastUpdatedByPersonName
WHERE V26406376.APPROVER_EMPLOYEE_ID = BatchApprovedBy.PERSON_ID(+)
AND V26406376.CREATED_BY = CreatedByUser.USERNAME
AND ('Y') = CreatedByUser.ACTIVE_FLAG
AND CreatedByUser.PERSON_ID = CreatedByPersonName.PERSON_ID(+)
AND V26406376.LAST_UPDATED_BY = LastUpdatedByUser.USERNAME(+)
AND ('Y') = LastUpdatedByUser.ACTIVE_FLAG(+)
AND LastUpdatedByUser.PERSON_ID = LastUpdatedByPersonName.PERSON_ID(+)
AND ( DATE'2024-07-03' BETWEEN BatchApprovedBy.EFFECTIVE_START_DATE(+) AND BatchApprovedBy.EFFECTIVE_END_DATE(+))
AND ( DATE'2024-07-03' BETWEEN CreatedByPersonName.EFFECTIVE_START_DATE(+) AND CreatedByPersonName.EFFECTIVE_END_DATE(+))
AND ( DATE'2024-07-03' BETWEEN LastUpdatedByPersonName.EFFECTIVE_START_DATE(+) AND LastUpdatedByPersonName.EFFECTIVE_END_DATE(+))) T289094
where ( 1 <> 1 )