ZMM Notes columns - SOURCE_OBJECT_CODE, SOURCE_OBJECT_UID in Receivables - Transactions Real Time?
Summary:
Is anyone familiar with the subject area - Receivables - Transactions Real Time? I am looking for the equivalent of SOURCE_OBJECT_CODE, SOURCE_OBJECT_UID columns from the Oracle table ZMM_notes within Receivables - Transactions Real Time? Ultimately, I am looking to add the notes_txt column to an existing otbi report with various other subjects areas - Payables Invoices - Transactions Real Time, Payables Invoices - Installments Real Time, Supplier - Supplier Real Time.
In order to perform the join on the AP_INVOICES_ALL table on the invoice id, it is required to have the SOURCE_OBJECT_CODE = 'Standard_AP_Invoice', and the SOURCE_OBJECT_UID = invoice_id.
Customer Notes only contains columns: (within Receivables - Transactions Real Time)
Creation Date, Note Text, Note Type, Visibility
ZMM notes - https://docs.oracle.com/en/cloud/saas/sales/21d/oedms/zmmnotes-12654.html#zmmnotes-12654
Update - This doesn't seem possible given the customer notes table structure. There isn't a foreign key that I can use to pair with an invoice_id. Is the only alternative to rewrite this report in BIP?
Content (required):
Version (include the version you are using, if applicable):
Code Snippet (add any code snippets that support your topic, if applicable):
Answers
-
Hi Scott, So to confirm what is in or out of a subject area you can view the data lineage mapping the presentation columns you use to physical columns in the application database extract from teh metadata repository database (RPD). Cheers, Nathan
From the doc
review data lineage
0 -
Hi Scott, trying to tackle the same probem via BI Publisher and finding that the standard MOS advice that the ZMM_NOTES.ource_object_uid joins to ra_customer_trx_all.customer_trx_id is wrong, so I am hoping you discovered a means to join these tables and are willing to share, did you succeed on this?
Appreciate your time,
Robert.
0 -
Hi Robert,
FYI We have it working on receivables transactions (but we have not tried it with payables transactions like Scott)
Using Receivables Credit to Cash
Setup: Add note on transaction header
Select Navigator, select Show More. In Receivables select work area Billing. Select Tasks, in Transactions select task Manage Transactions. Enter criteria, select Search. In search results select link in column Transaction Number. System go to page Review transaction X. In General Information select link in field Notes. System go to Notes. Select Transaction, not Instalment n. Select Create to go to Create Note. Sekect Type, Private = False, enter note text in RTF edit mode "Hola mundo!". Select OK. Select OK. Select Save and Close.
Assert:
select all count(*) over (partition by null) as n
, t.customer_trx_id as transaction_id
, to_char(note.last_update_date,upper('yyyy-mm-dd"t"hh24:mi:ss')) as updated_date
, t.trx_number
, t.bill_to_customer_id
, t.bill_to_site_use_id
, note.note_text_c as note_text
from
--https://docs.oracle.com/en/cloud/saas/financials/24b/oedmf/racustomertrxall-5191.html
--ra_customer_trx_u1 unique default customer_trx_id
--ra_customer_trx_u2 unique default reversed_cash_receipt_id
ra_customer_trx_all t
left outer join
( -- note
select all
t.source_object_code
, t.source_object_uid
, t.visibility_code
, t.last_update_date
, t.note_id
, t.note_number as note_number
, t.note_type_code as note_type
, t.note_txt
, substr(t.note_txt,1,2000) as note_text_c
from
-- https://docs.oracle.com/en/cloud/saas/sales/oedms/zmmnotes-25594.html#zmmnotes-25594
-- zmm_notes_pk unique default note_id
-- zmm_notes_u2 unique default note_number
zmm_notes t
where t.source_object_code = upper('ar_transaction')
and t.visibility_code = upper('internal') -- not private
) note
on (
t.customer_trx_id = note.source_object_uid
)
where 1 = 1
and t.trx_number = :p …1 -
Many thanks for this Nathan, my sql is pretty much a beat for beat reflection of yours, so I am feeling like the issue is on the business process side, so again, a massive thank you for the functional details on how you are attaching the notes from the functional perspective, I am going to use this with the functional lead to see where their process differs.
Appreciate your time,
Thank you,
Robert.
0
