nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

ZMM Notes columns - SOURCE_OBJECT_CODE, SOURCE_OBJECT_UID in Receivables - Transactions Real Time?

Scott McAnally

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):

Find more posts tagged with

Reporting

SQL

OTBI

Accepted answers

Nathan CCC

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

https://docs.oracle.com/en/cloud/saas/financials/22a/analyze-and-report.html

review data lineage

https://cloudcustomerconnect.oracle.com/posts/f53e634ec2

All comments

Nathan CCC

From the doc

https://docs.oracle.com/en/cloud/saas/financials/22a/analyze-and-report.html

review data lineage

https://cloudcustomerconnect.oracle.com/posts/f53e634ec2

Robert of STAU

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.

Nathan CCC

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 https://docs.oracle.com/en/cloud/saas/financials/24b/faofc/index.html#s20069713

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 …

Robert of STAU

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.

Vermaee

has anyone got solution for this ? I am also facing this issue where zmm_notes.source_object_uid is not same as customer_trx_id.