0 Replies Latest reply on Dec 15, 2011 12:21 PM by 733278

    Sales Reports showing Different Net Sales

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


      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,