Organization Name (Required - If you are an Oracle Partner, please provide the organization you are logging the idea on behalf of):

SAS Institute Inc

Description (Required):

I am trying to create a report that shows the invoices for a project in Fusion and the related receipts. As the two subject areas cannot be related from the UI, I had to use Logical SQL. The subject areas are : "Project Billing - Invoices Real Time" and "Receivables - Standard Receipts Application Details Real Time".

Use Case and Business Need (Required):

The business requirement is to display the unpaid invoice balance and the total of all unpaid balances for a project. However, when an invoice is settled with more than one receipt, the invoice amount repeats and this results in totals that are incorrect.

Enhancement Request / Service Request:

The "Receivables - Payment Schedules Real Time" subject area has the unpaid balance amount, but this subject area based report is not working since an Oracle update we did in June 2022. (see ticket SR #3-30453515721). My requirement for this workaround is to get this information avoiding this subject area ("Receivables - Payment Schedules Real Time").

Specifically, I am looking for a method by which I can make the invoice amount zero when the invoice number repeats. I have attached the image of the report output. The Logical SQL I use is this:

SELECT Project_Business_Unit, Project_Number, Project_Name, Customer_Name, Contract_Number, Invoice_Number, Invoice_Status, Invoice_Type, AR_Invoice_Number, Invoice_Date, Invoice_Amount, Receipt_Number, Receipt_Date, Applied_Amount, Applied_Invoice_Number FROM

(

SELECT A.Q1_0 Project_Business_Unit, A.Q1_2 Project_Number, A.Q1_3 Project_Name, A.Q1_4 Customer_Name, A.Q1_5 Contract_Number, A.Q1_6 Invoice_Number, A.Q1_7 Invoice_Status, A.Q1_8 Invoice_Type, A.Q1_9 AR_Invoice_Number, A.Q1_11 Invoice_Date, A.Q1_12 Invoice_Amount, B.Q2_14 Receipt_Number, B.Q2_15 Receipt_Date, B.Q2_17 Applied_Amount, B.Q2_16 Applied_Invoice_Number FROM

(SELECT  "Project Billing - Invoices Real Time"."Project"."Project Business Unit" Q1_0,

"Project Billing - Invoices Real Time"."Project"."Organization Name" Q1_1,

"Project Billing - Invoices Real Time"."Project"."Project Number" Q1_2,

"Project Billing - Invoices Real Time"."Project"."Project Name" Q1_3,

"Project Billing - Invoices Real Time"."- Contract Customer Details"."Contract Customer Name" Q1_4,

"Project Billing - Invoices Real Time"."Project Contract Header Details"."Contract Number" Q1_5,

"Project Billing - Invoices Real Time"."Invoice Details"."Invoice Number" Q1_6,

"Project Billing - Invoices Real Time"."Invoice Details"."Invoice Status" Q1_7,

"Project Billing - Invoices Real Time"."Invoice Details"."Invoice Type" Q1_8,

"Project Billing - Invoices Real Time"."Invoice Details"."AR Invoice Number" Q1_9,

"Project Billing - Invoices Real Time"."- Invoice Dates"."Header Released Date" Q1_10,

"Project Billing - Invoices Real Time"."- Invoice Dates"."Invoice Date" Q1_11,

"Project Billing - Invoices Real Time"."Invoice Transaction Measures"."Invoice Amount" Q1_12

FROM "Project Billing - Invoices Real Time"

WHERE 

("Project Billing - Invoices Real Time"."Project Contract Header Details"."Contract Type Name" IN ('External Contract', 'External Fixed Price', 'External Time and Materials')))A

LEFT OUTER JOIN 

(SELECT   "Receivables - Standard Receipts Application Details Real Time"."Business Unit"."Business Unit Name" Q2_13,

  "Receivables - Standard Receipts Application Details Real Time"."Standard Receipt Details"."Receipt Number" Q2_14,

  "Receivables - Standard Receipts Application Details Real Time"."Standard Receipt Details"."Receipt Date" Q2_15,

  "Receivables - Standard Receipts Application Details Real Time"."Standard Receipt Application Details"."Applied Transaction Number" Q2_16,

  "Receivables - Standard Receipts Application Details Real Time"."Standard Receipt Application"."Applied To Entered Amount" Q2_17,

  DESCRIPTOR_IDOF("Receivables - Standard Receipts Application Details Real Time"."Business Unit"."Business Unit Name") Q2_18

FROM "Receivables - Standard Receipts Application Details Real Time")B 

ON A.Q1_0=B.Q2_13

AND A.Q1_9=B.Q2_16

)OUTER