0 Replies Latest reply: Dec 15, 2011 6:21 AM by 733278 RSS

    Sales Reports showing Different Net Sales

    733278
      Hi All,

      Here is three queries, which are sowing different net sales. But both should return same values. Please correct me.
      ----
      select sum( nvl(c.extended_amount,0) * nvl(b.exchange_rate,1)) SALES
      from ra_salesreps a
      ,ra_customer_trx_all b
      ,ra_customer_trx_lines c
      where a.salesrep_id(+) = b.primary_salesrep_id
      and b.customer_trx_id(+) = c.customer_trx_id
      and c.line_type='LINE'
      AND b.complete_flag like 'Y'
      and (b.trx_date between TO_DATE('2010/12/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and TO_DATE('2011/11/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') )
      AND b.primary_salesrep_id = 1040;

      ---
      SELECT SUM(nvl(c.extended_amount,0)*nvl(b.exchange_rate,1 )) net_amount,
      FROM hz_cust_accounts hca,
      hz_parties hp,
      ra_customer_trx_all b,
      ra_customer_trx_lines_all c,
      ra_cust_trx_types_all d
      WHERE b.bill_to_customer_id = hca.cust_account_id
      AND b.customer_trx_id = c.customer_trx_id
      AND b.cust_trx_type_id = d.cust_trx_type_id
      AND hca.party_id = hp.party_id (+)
      AND c.line_type='LINE'
      AND b.invoice_currency_code like 'USD'
      AND hca.primary_salesrep_id = 1040
      AND b.trx_date BETWEEN to_date('2010/12/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      AND to_date('2011/11/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
      HAVING SUM(DECODE(d.TYPE, 'INV', (nvl(c.extended_amount,0)*nvl(b.exchange_rate,1)), 'DM', (nvl(c.extended_amount,0)*nvl(b.exchange_rate,1)),0 )) BETWEEN TO_NUMBER(-999999999) and TO_NUMBER(999999999);

      ---

      SELECT
      SUM(nvl(y.extended_amount,0)*nvl(x.exchange_rate,1 )) net_amount
      FROM hz_cust_accounts hca,
      hz_parties hp,
      hz_party_sites hps,
      hz_cust_acct_sites hcas,
      hz_cust_site_uses_all hcsu,
      hz_locations hl,
      ra_customer_trx_all x,
      ra_customer_trx_lines_all y,
      ra_cust_trx_types_all z
      WHERE hca.party_id = hp.party_id
      AND hp.party_id = hps.party_id
      AND hcas.party_site_id = hps.party_site_id (+)
      AND hps.location_id = hl.location_id (+)
      AND x.bill_to_customer_id = hca.cust_account_id(+)
      AND hcas.cust_account_id = x.bill_to_customer_id
      AND x.customer_trx_id = y.customer_trx_id
      AND x.cust_trx_type_id = z.cust_trx_type_id
      AND x.ship_to_site_use_id = hcsu.site_use_id(+)
      AND hcas.cust_acct_site_id(+) = hcsu.cust_acct_site_id
      AND x.invoice_currency_code like 'USD'
      AND y.line_type='LINE'
      AND x.trx_date BETWEEN TO_DATE('2010/12/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      AND TO_DATE('2011/11/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
      AND hca.primary_salesrep_id = 1040;

      Thanks & Regards,