Saturday, May 17, 2014

Query to find strategies of a Customer Oracle R12 AR

Strategies of a Customer


SELECT DISTINCT(ist.cust_account_id),
  ist.status_code,
  ist.strategy_id,
  ist.strategy_template_id,
  ist.creation_date,
  hca.account_number,
  hca.account_name,
  istt.strategy_name
FROM iex_strategies ist,
  hz_cust_accounts_all hca,
  iex_strategy_templates_tl istt,
  (SELECT MAX(creation_date) max_date,
    cust_account_id
  FROM iex_strategies
  GROUP BY cust_account_id
  ) da
WHERE hca.cust_account_id    = ist.cust_account_id
AND ist.strategy_template_id = istt.strategy_temp_id
AND ist.object_type          = 'ACCOUNT'
AND ist.status_code         <> 'CANCELLED' --= 'OPEN'
AND TRUNC(ist.creation_date) =TRUNC(da.max_date)
AND ist.cust_account_id      =da.cust_account_id
AND hca.cust_account_id      =da.cust_account_id
AND hca.account_number      IN ('GL-21618645');
Reactions:

0 comments:

Post a Comment