Here is the sql I am using (your mileage may vary) to build a sphere in synoptix that shows the revenue by last ship date:
/*WIP - good match to the goal *//*SECTION 1 KNOWN GOOD*/SELECT 'CUSTOMER ORDER AND IN DEMAND_SUPPLY_LINK',E.NAME AS CUSTOMER_NAME,CASE WHEN A.LAST_SHIPPED_DATE is null then '01/01/2100' ELSE A.LAST_SHIPPED_DATE END AS LAST_SHIPPED_DATE,A.CUST_ORDER_ID,A.LINE_NO AS CUSTOMER_ORDER_LINE,A.PART_ID,A.LINE_STATUS,A.PRODUCT_CODE,B.GL_ACCOUNT_ID AS ACCOUNT,G.DESCRIPTION AS ACCOUNT_DESCRIPTION,B.INVOICE_ID,B.AMOUNT REVENUE_AMOUNT,C.SUPPLY_TYPE AS ORDER_CAME_FROM,C.SUPPLY_BASE_ID AS ORDER_CAME_FROM_DETAIL,B.REFERENCEFROM CUST_ORDER_LINE A,RECEIVABLE_LINE B,DEMAND_SUPPLY_LINK C,CUSTOMER_ORDER D,CUSTOMER E,RECEIVABLE F,ACCOUNT GWHERE A.CUST_ORDER_ID = B.CUST_ORDER_IDAND A.LINE_NO = B.CUST_ORDER_LINE_NOAND C.DEMAND_BASE_ID = A.CUST_ORDER_IDAND C.DEMAND_SEQ_NO = A.LINE_NOAND A.CUST_ORDER_ID = D.IDAND B.INVOICE_ID = F.INVOICE_IDAND E.ID = D.CUSTOMER_IDAND F.STATUS = 'A'AND f.site_id = 'UFS'AND f.currency_id = 'USD'AND G.ID = B.GL_ACCOUNT_IDand f.type = 'I' union /*SECTION 2 KNOWN GOOD*/SELECT 'CUSTOMER ORDER BUT NOT IN DEMAND_SUPPLY_LINK',E.NAME AS CUSTOMER_NAME,CASE WHEN A.LAST_SHIPPED_DATE is null then '01/01/2100' ELSE A.LAST_SHIPPED_DATE END AS LAST_SHIPPED_DATE,A.CUST_ORDER_ID,A.LINE_NO,A.PART_ID,A.LINE_STATUS,A.PRODUCT_CODE,B.GL_ACCOUNT_ID,G.DESCRIPTION,B.INVOICE_ID,B.AMOUNT,'NONE' AS ORDER_CAME_FROM,'NONE' AS ORDER_CAME_FROM_DETAIL,B.REFERENCEFROM CUST_ORDER_LINE A,RECEIVABLE_LINE B,CUSTOMER_ORDER D,CUSTOMER E,RECEIVABLE F,ACCOUNT GWHERE A.CUST_ORDER_ID = B.CUST_ORDER_IDAND A.LINE_NO = B.CUST_ORDER_LINE_NOAND A.LINE_NO not in (select c.demand_seq_no from DEMAND_SUPPLY_LINK C where c.demand_base_id = a.cust_order_id)AND A.CUST_ORDER_ID = D.IDAND B.INVOICE_ID = F.INVOICE_IDAND E.ID = D.CUSTOMER_IDAND F.STATUS = 'A'AND f.site_id = 'UFS'AND f.currency_id = 'USD'AND G.ID = B.GL_ACCOUNT_IDand f.type = 'I' union /*SECTION 3 KNOWN GOOD*/SELECT 'NO CUSTOMER ORDER',E.NAME AS CUSTOMER_NAME,F.INVOICE_DATE AS LAST_SHIPPED_DATE,'0000','00','NONE','NONE','NONE',B.GL_ACCOUNT_ID,G.DESCRIPTION,B.INVOICE_ID,B.AMOUNT,'NONE','NONE',B.REFERENCEFROM RECEIVABLE_LINE B,CUSTOMER E,RECEIVABLE F,ACCOUNT GWHERE B.INVOICE_ID = F.INVOICE_IDAND F.STATUS = 'A'AND f.site_id = 'UFS'AND f.currency_id = 'USD'AND E.ID = F.CUSTOMER_IDAND G.ID = B.GL_ACCOUNT_IDAND B.CUST_ORDER_ID IS NULL and f.type = 'I';
