We are implementing OBIA 22.214.171.124 for PeopleSoft Financials 9.0. On the AP side, we are seeing payment details (from PS_PYMNT_VCHR_XREF) for transactions that are Canceled and Closed showing up in the Outstanding amounts (Due/Overdue). Additionally, voucher details are coming across for those Vouchers tagged as Deleted (ENTRY_STATUS = 'X' in PS_VOUCHER).
I saw details in the Config guide about Soft Deletes, but this is related to data being physically deleted from the Source. Also, the Canceled/Closed payment coming through indicates something that seems out of place (so it does not appear to be looking at CLOSE_STATUS either).
I am not the ETL resource, but thought I would post the question in case it is something that I can pass along to them. I have had an open SR for more than a week with Oracle but have no response.
Has anyone seen this occur? Are there additional steps in the process that reclassify these? Is something possibly not configured right?
So are you seeing that those CLOSED/CANCELLED records from PSoft have a DELETE_FLG='Y' in the central AP transaction fact table (W_AP_XACT_F)? If so , then that means that those ARE being marked properly at least until that table. Now, with regard to your statement "we are seeing payment details (from PS_PYMNT_VCHR_XREF) for transactions that are Canceled and Closed showing up in the Outstanding amounts (Due/Overdue)"..are you referring to the AP Aging tables or reports? If so, there is a known bug (9078998 ) for this that involves an issue with the PLP (post load process) that loads the Aging Tables for AP..here are the details:
The workaround for BUG 9078998 is as follows:
1. Add AND W_AP_XACT_F.DELETE_FLG = 'N' to the existing WHERE clause of the Source-Qualifier "SQ_IA_AP_XACTS" in the "PLP.PLP_APSnapshotInvoiceAging" mapping and save the mapping.
2. Open the corresponding workflow in the Informatica Workflow Manager, do a "Refresh Mapping" for the session, then "Validate" and save the workflow.
Now if the records are CLOSED/CANCELLED in PSoft and still show with DELETE_FLG='N' then there is another issue. If this is the case, then can you confirm if this happens for incremental loads or also happens for FULL loads? We can investigate further.
If this was helpful, please mark the response as correct or helpful.
Thanks! I passed this along to the ETL resources. I believe they mentioned previously that the Delete_flg on these was blank, but will have to verify. We also reviewed section 17.8 of the Config guide on Soft Deletes. However, this appears to refer to when records are physically deleted from the Source system. Additionally, from what they told me, there are no mappings for Identify and Delete for the AP Payment area, so we could not activate.
The DELETE_FLG should not be NULL..it would either be N or Y. Let me know if you can confirm which one it is. We basically need to know if the issue is at the source extract level or only on the PLP for the Aging snapshot tables.
I will see what I can find out. One thing, though, is the records are not deleted from PeopleSoft. There is just a status set. I will have to review the bug more carefully and look at the data. However, the status from the PYMNT_VCHR_XREF and VOUCHER are what is driving this, as those records should be brought across but not count as open transactions.
In the AP extract process for PSoft, the task SDE_PSFT_APTransactionFact_PrePayApplication has the following condition defined in the vanilla SQL: PS_PYMNT_VCHR_XREF.PYMNT_ACTION = 'P'
I assume this means the payment was made to the vendor for that transaction. Can you confirm that in PeopleSoft the "Cancelled" vouchers have NOT been paid and should NOT be included in the calculations? Basically, is this a scenario where there is "cleanup" needed on the PeopleSoft side for deleted vouchers as opposed to a ETL logic issue on the BI side? Regarding the Soft Delete, yes this is only for records physically deleted in the source systems..and yes, there are vanilla primary/soft delete tasks for AP but they are inactive by default. Hope this helps.
None of what we are seeing in the extract side makes sense.
Canceled and closed voucher transactions are showing up as outstanding. Also, vouchers that have en entry status of Deleted (still in PeopleSoft database just no longer an outstanding amount).
We have customized the process to not include the X or C in the Payment Select Status now go to CLEARED rather.
Here's what has been added to prevent the values in pymnt_vchr_xref records that are Closed or Canceled so far DECODE(INP_PYMNT_SELCT_STATUS, 'P', 'CLEARED', 'S', 'CLEARED', 'X', 'CLEARED','C','CLEARED','OPEN'). We are looking to have to add one for the Entry Status now.
This was part of the following to determine var_close_status
• AP Remaining Amount: IIF (PYMNT_ACTION = 'P', 0, PYMNT_GROSS_AMT - IIF (CURRENCY_PYMNT <> TXN_CURRENCY_CD, IIF(RATE_MULT=0,0, PAID_AMT_GROSS_BSE*RATE_DIV/RATE_MULT),
PAID_AMT_GROSS)) * -1
• var_close_status: DECODE(INP_PYMNT_SELCT_STATUS, 'P', 'CLEARED', 'S', 'CLEARED', 'OPEN')
• final ap_remaining_doc_amt: IIF(VAR_CLOSE_STATUS = 'OPEN', INP_AP_REMAINING_DOC_AMT, 0)
Also, there are no Prepay records at the location and that mapping brings back no data.
One other note... the voucher records do not need to be "cleaned up" on PeopleSoft. They are marked as a status of deleted and should be there as an audit trail for invoices and are not intended to be physically deleted. Since we are seeing issues with closed payments coming through as OPEN (Due/Overdue) in OBIA, is there some configuration or step not being done? The payment cancellation in PeopleSoft is delivered.
Also. Can you clarify if this issue exists with both a FULL and INCREMENTAL load of the DW? For instance, if you were to do a FULL reload of the DW, do you still see CANCELLED vouchers incorrectly showing in the associated reports/dashboards? Secondly, what are the transaction dates of the voucher CANCELLATION? Are these "backdated" cancellations? I do know Psoft has the concept of backdating specific types of data.
I am not positive on the question on whether it occurs on the Incremental. We have not gone live yet (this is hindering it in fact), and these issues came up during testing. These were Closed and cancellations that existed in the system from years ago as well as some new ones. Also, the Deleted one was older. I can try to determine if any canceled ones go through, I may not be able to get an environment to replicate until we get the full load resolved.
As to whether they were in the system prior to the customization, yes, that is correct. We added the customization once we saw Closed/Canceled payments going into the system.
Is the expectation to extract the DELETED or CANCELLED voucers into the DW but not have them impact the totals (as an audit measure) or to ignore them completely? I would assume for audit purposes PSoft is the system of record. In which case, as long as you can filter all CANCELLED and DELETED vouchers from the AP SDE extracts, this should address the issue. Does that sound correct?
Yes, the question is why would OBIA add the records in PeopleSoft's PYMNT_VCHR_XREF that are marked as Canceled/Closed (The value used is PYMNT_GROSS_AMT - PAID_AMT_GROSS). Also, vouchers that have been marked as a status of deleted could never be paid, yet are showing up in Due/Overdue amounts.
Looking at the Psoft 90 mappings, ALL payment statuses are extracted..however, there is a downstream SIL mapping/mapplet (SIL_APTransactionFact/mplt_SIL_APTransactionFact) in which the status is actually compared with the W_STATUS_D table to determine if it is POSTED/UNPOSTED.
Does your W_STATUS_D table corrrelate with the customizations you made on the SDE side? Can you send the output of the following SQL:
LOOKUP_TABLE.ROW_WID as ROW_WID,
LOOKUP_TABLE.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID,
LOOKUP_TABLE.INTEGRATION_ID as INTEGRATION_ID
Basically...if you do not care for having these CANCELLED/DELETED vouchers..then you should just add a FILTER in the SDE to filter out ALL CANCELLED/CLOSED vouchers via a condition such as "PYMNT_SELCT_STATUS <> 'X''" in the SDE (do not DECODE it to another value)...this will just elimiate them altogether which it seems you want to do.
If you want to still bring them in and DECODE these..then you must check the downstream logic in the mapplet listed above and confirm that these do not get POSTED in the DW.
We do want the details to come through to OBIA. However, they should not show up as Due/Overdue amounts, since these are not Due/Overdue. I'm not sure why this would occur at all, there must be a follow-up step to determine if these are open. The steps appear to only be concerned with Payment Select Status of Paid and counts the rest as Open. This doesn;t make sense, and would greatly inflate/distort the due amounts.
I'll take a look at the details you included here as well and discuss with the team. Please let me know of any other insight you have to why these closed invoices/payment are showing up as Open.