select nvl(ps.ps_transaction_date, cp.cp_transaction_date) transaction_date, decode(ps.ps_currency, 'ALL', decode(ps.ps_transfer_type, 'ALL', '<b> WesternUnion </b>', 'SEND', 'Отправка', 'RECEIVE', 'Выдача', 'Возврат'), '') ps_transfer_type, decode(ps.ps_currency, 'ALL', '', ps.ps_currency) ps_currency, ps.ps_quantity, decode(ps.ps_currency, 'ALL', '', decode(cp.cp_amount, ps.ps_amount, to_char(ps.ps_amount, '999G999G999G990D99'), '<font color="red">' || to_char(ps.ps_amount, '999G999G999G990D99') || '</font>')) ps_amount, decode(ps.ps_failCount, 0, to_char(ps.ps_failCount), '<a href="javascript:cp_GetReport(755,''WU_CITY_TRN_DETAIL'',''DynamicsReport'',''P755_BEGIN_DATE;P755_END_DATE;P755_TRANSFER_TYPE;P755_CURRENCY;P755_REVISE_STATUS'', ''' || :P750_BEGIN_DATE || ';' || :P750_END_DATE || ';' || ps.ps_transfer_type || ';' || ps.ps_currency || ';NONE'')">' || ps.ps_failCount || '</a>') ps_failCount, decode(ps.ps_successCount, 0, to_char(ps.ps_successCount), '<a href="javascript:cp_GetReport(755,''WU_CITY_TRN_DETAIL'',''DynamicsReport'',''P755_BEGIN_DATE;P755_END_DATE;P755_TRANSFER_TYPE;P755_CURRENCY;P755_REVISE_STATUS'', ''' || :P750_BEGIN_DATE || ';' || :P750_END_DATE || ';' || ps.ps_transfer_type || ';' || ps.ps_currency || ';TRANSFER_REVISED'')">' || ps.ps_successCount || '</a>') ps_successCount, '|' spliter, decode(cp.cp_currency, 'ALL', decode(cp.cp_transfer_type, 'ALL', '<b>CITY+</b>', 'SEND', 'Отправка', 'RECEIVE', 'Выдача', 'Возврат'), '') cp_transfer_type, decode(cp.cp_currency, 'ALL', '', cp.cp_currency) cp_currency, cp.cp_quantity, decode(cp.cp_currency, 'ALL', '', decode(cp.cp_amount, ps.ps_amount, to_char(cp.cp_amount, '999G999G999G990D99'), '<font color="red">' || to_char(cp.cp_amount, '999G999G999G990D99') || '</font>')) cp_amount, decode(cp.cp_failCount, 0, to_char(cp.cp_failCount), '<a href="javascript:cp_GetReport(755,''CITY_WU_TRN_DETAIL'',''DynamicsReport'',''P755_BEGIN_DATE;P755_END_DATE;P755_TRANSFER_TYPE;P755_CURRENCY;P755_REVISE_STATUS'', ''' || :P750_BEGIN_DATE || ';' || :P750_END_DATE || ';' || cp.cp_transfer_type || ';' || cp.cp_currency || ';NONE'')">' || cp.cp_failCount || '</a>') cp_failCount, decode(cp.cp_successCount, 0, to_char(cp.cp_successCount), '<a href="javascript:cp_GetReport(755,''CITY_WU_TRN_DETAIL'',''DynamicsReport'',''P755_BEGIN_DATE;P755_END_DATE;P755_TRANSFER_TYPE;P755_CURRENCY;P755_REVISE_STATUS'', ''' || :P750_BEGIN_DATE || ';' || :P750_END_DATE || ';' || cp.cp_transfer_type || ';' || cp.cp_currency || ';TRANSFER_REVISED'')">' || cp.cp_successCount || '</a>') cp_successCount
from
(select xps.ps_transfer_type, xps.ps_currency, xps.ps_transaction_date, xps.ps_failCount, xps.ps_successCount, xps.ps_quantity, xps.ps_amount, xps.gr
from
(select nvl(y.transfer_type, 'ALL') ps_transfer_type, nvl(y.currency, 'ALL') ps_currency, y.transaction_date ps_transaction_date, sum(decode(y.revise_status, 'NONE', 1, 0)) ps_failCount, sum(decode(y.revise_status, 'NONE', 0, 1)) ps_successCount, count(y.id_transfer_revise) ps_quantity, sum(y.amount) ps_amount, grouping_id(y.transaction_date, y.transfer_type, y.currency) gr
from
(select w.id_transfer_revise, w.transfer_type, w.currency, sum(decode(pst.destination_account_type, 'OBLIGATIONS', wd.amount, (-1) * wd.amount)) amount, w.revise_status, w.transaction_date
from CP$ORDER_REVISE wd inner join CP$TRANSFERS_REVISE w on wd.id_transfer_revise = w.id_transfer_revise inner join CP$ABS_ORDER_TYPE pst on wd.order_type = pst.type_code inner join WU_TERMINAL ter on w.terminal = ter.terminal_id inner join
(select j.code, j.name
from CP$POINT_V j start with j.code = :CP_POS connect by prior j.code = j.boss) point on ter.cp_pos = point.code
where w.product = 'WU' /*:APP_ALIAS*/ and pst.owner = 'GO' and trunc(wd.trading_day) between /*to_date('30.06.2012', 'DD.MM.YYYY') and to_date('30.06.2012', 'DD.MM.YYYY')*/ to_date(:P750_BEGIN_DATE, 'DD.MM.YYYY') and to_date(:P750_END_DATE, 'DD.MM.YYYY') /*to_date(v('P750_BEGIN_DATE'), 'DD.MM.YYYY') and to_date(v('P750_END_DATE'), 'DD.MM.YYYY')*/ and (pst.source_account_type in ('OBLIGATIONS', 'REQUIREMENTS') or pst.destination_account_type = 'OBLIGATIONS') group by w.id_transfer_revise, w.transfer_type, w.currency, w.revise_status, w.transaction_date) y group by cube(y.transaction_date, y.transfer_type, y.currency)) xps
where (xps.ps_transaction_date is not null and xps.ps_transfer_type != 'ALL') or (xps.ps_transaction_date is null and xps.ps_transfer_type = 'ALL')) ps full outer join
(select xcp.cp_transfer_type, xcp.cp_currency, xcp.cp_transaction_date, xcp.cp_failCount, xcp.cp_successCount, xcp.cp_quantity, xcp.cp_amount, xcp.gr
from
(select nvl(x.transfer_type, 'ALL') cp_transfer_type, nvl(x.currency, 'ALL') cp_currency, x.transaction_date cp_transaction_date, sum(decode(x.revise_status, 'NONE', 1, 0)) cp_failCount, sum(decode(x.revise_status, 'NONE', 0, 1)) cp_successCount, count(x.transfer_id) cp_quantity, sum(x.amount) cp_amount, grouping_id(x.transaction_date, x.transfer_type, x.currency) gr
from
(select d.transfer_id, m.transfer_type, o.currency, sum(decode(cpt.destination_account_type, 'OBLIGATIONS', d.amount, (-1) * d.amount)) amount, m.revise_status, m.transaction_date transaction_date
from CP$ORDER_DETAIL d inner join CP$ORDER o on d.id_order = o.id_order inner join CP$MONEY_TRANSFER_WU_REVISE_V m on d.transfer_id = m.item_key inner join CP$ABS_ORDER_TYPE cpt on o.order_type = cpt.type_code inner join WU_TERMINAL ter on m.terminal_id = ter.terminal_id inner join
(select j.code, j.name
from CP$POINT_V j start with j.code = :CP_POS connect by prior j.code = j.boss) point on ter.cp_pos = point.code
where o.product = 'WU' /*:APP_ALIAS*/ and cpt.owner = 'GO' and trunc(o.trading_day) between /*to_date('30.06.2012', 'DD.MM.YYYY') and to_date('30.06.2012', 'DD.MM.YYYY')*/ to_date(:P750_BEGIN_DATE, 'DD.MM.YYYY') and to_date(:P750_END_DATE, 'DD.MM.YYYY') /*to_date(v('P750_BEGIN_DATE'), 'DD.MM.YYYY') and to_date(v('P750_END_DATE'), 'DD.MM.YYYY')*/ and o.delete_date is null and (cpt.source_account_type in ('OBLIGATIONS', 'REQUIREMENTS') or cpt.destination_account_type = 'OBLIGATIONS') and o.order_type not in ('MEM_CREDIT_SALDO_GO', 'MEM_DEBIT_SALDO_GO', 'MEM_CREDIT_SALDO_RC', 'MEM_DEBIT_SALDO_RC') group by d.transfer_id, m.transfer_type, o.currency, m.revise_status, m.transaction_date) x group by cube(x.transaction_date, x.transfer_type, x.currency)) xcp
where (xcp.cp_transaction_date is not null and xcp.cp_transfer_type != 'ALL') or (xcp.cp_transaction_date is null and xcp.cp_transfer_type = 'ALL')) cp on ps.ps_transfer_type = cp.cp_transfer_type and ps.ps_currency = cp.cp_currency and ps.gr = cp.gr and nvl(ps.ps_transaction_date, to_date('01.01.1900')) = nvl(cp.cp_transaction_date, to_date('01.01.1900')) order by nvl(nvl(ps.ps_transaction_date, cp.cp_transaction_date), to_date('01.01.1900')), nvl(ps.ps_transfer_type, cp.cp_transfer_type), nvl(ps.ps_currency, cp.cp_currency), cp.cp_transfer_type, cp.cp_currency