SELECT ra.TRX_NUMBER Sales_Tax_Invoice_Number,
ra.TRX_DATE Invoice_Date,
'Commercial Invoice' Invoice_Type,
-- max(ool.PROMISE_DATE) Promise_Date,
to_date(max(ool.SCHEDULE_SHIP_DATE),'DD-MM-YYYY') Dispatch_Date,
(nvl(REGEXP_SUBSTR(name, '\d+'), 0) + ra.TRX_DATE) Payment_Due_Date,
rla.SALES_ORDER Sales_Order,
PARTY.PARTY_NAME Customer_Name,
rla.DESCRIPTION Item_Description,
case
when rla.quantity_invoiced is null then
rla.quantity_credited
else
rla.quantity_invoiced
end Quantity_Invoiced_in_metres,
rla.UNIT_SELLING_PRICE Unit_Rate,
(rla.EXTENDED_AMOUNT * nvl(RA.EXCHANGE_RATE, 1)) Total_Invoice_Value,
zl.tax_rate || '%' Sales_Tax,
(zl.tax_rate / 100) *
(rla.EXTENDED_AMOUNT * nvl(RA.EXCHANGE_RATE, 1)) Sales_Tax_Value,
(rla.EXTENDED_AMOUNT * nvl(RA.EXCHANGE_RATE, 1)) +
(zl.tax_rate / 100) *
(rla.EXTENDED_AMOUNT * nvl(RA.EXCHANGE_RATE, 1)) Grand_Total,
ool.FLOW_STATUS_CODE Payment_Status
FROM RA_CUSTOMER_TRX_ALL RA,
Ra_Customer_Trx_Lines_All rla,
ra_customer_trx_lines_all rctla_LINK,
ZX_LINES ZL,
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts CUST_ACCT,
hz_parties PARTY,
ra_terms_tl term,
QA_RESULTS qr
where ra.CUSTOMER_TRX_ID = rla.CUSTOMER_TRX_ID
and rla.CUSTOMER_TRX_LINE_ID = rctla_LINK.LINK_TO_CUST_TRX_LINE_ID
and rctla_link.TAX_LINE_ID = zl.TAX_LINE_ID
and ooh.HEADER_ID = ool.HEADER_ID
and rla.SALES_ORDER = ooh.ORDER_NUMBER
AND OOH.sold_to_org_id = cust_acct.cust_account_id(+)
and CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
and tax = 'PK OUTPUT ST'
--AND RA.TRX_NUMBER = 'NRA-1020821'
and ooh.ORDER_NUMBER = rla.SALES_ORDER
and ooh.payment_term_id = term.term_id(+)
and qr.ITEM_ID = ool.INVENTORY_ITEM_ID
and ra.TRX_DATE between '01-Jul-2024' and '31-Dec-2024'
and ool.FLOW_STATUS_CODE not in 'CANCELLED'
GROUP BY ra.TRX_NUMBER,
ra.TRX_DATE,
rla.SALES_ORDER,
term.name,
PARTY.PARTY_NAME,
rla.DESCRIPTION,
CASE
WHEN rla.quantity_invoiced IS NULL THEN
rla.quantity_credited
ELSE
rla.quantity_invoiced
END,
rla.UNIT_SELLING_PRICE,
rla.EXTENDED_AMOUNT,
zl.tax_rate,
NVL(RA.EXCHANGE_RATE, 1),
ool.FLOW_STATUS_CODE
Hi Experts,
I’m working on creating an agent in Microsoft Copilot Studio to utilize our organizational database (Oracle) as the knowledge base. I used PowerApps Tables to import required data from Oracle Database into Dataverse.
During the import process, I connected to the database, executed a custom query to fetch specific columns, and successfully previewed the required data in Power Query. However, after publishing, none of the rows appear in Dataverse. This is unexpected since a similar process with a different query worked flawlessly before.
Could anyone advise what might be causing this issue or suggest troubleshooting steps?
Thank you for your help!
stampcoin
28
mmbr1606
22
Super User 2025 Season 1
LA-30040116-0
21