SET SERVEROUTPUT ON;
DECLARE
v_count NUMBER;
v_vacant_system tbl_recivesystems.id_system%TYPE;
BEGIN
BEGIN
SELECT id_system
INTO v_vacant_system
FROM tbl_recivesystems
WHERE recive_object = 'vw_FX'
AND ROWNUM = 1;
EXCEPTION
WHEN no_data_found THEN
zzz_ex('recive system for object vw_FX not found');
END;
FOR cur IN (
SELECT DISTINCT idpocket
FROM tbl_ford32
WHERE 1=1
and idpocket IS NOT NULL
AND dtsend >= td - 30
AND sender_system = 'NavXLv5'
AND systemreceiver = v_vacant_system
AND dealtype = 'vw_FX'
) LOOP
SELECT COUNT(*)
INTO v_count
FROM
(
SELECT
acttype,
ticket,
tradedate,
tradetime,
buy,
sell,
rate,
swap,
status,
valuesell,
nett_buy,
typeinput,
r_ticket,
dp_tran,
valuebuy_swap,
valuesell_swap,
--,micexdeal -- https://confluence.softwell.ru/pages/viewpage.action?pageId=224363033
micexclaim,
trans_pos,
addinfo,
bal_acc_buy,
bal_acc_sell,
bal_acc_term_buy,
bal_acc_term_sell,
exec_ovp,
status_sell,
add_instr_buy,
add_instr_sell,
custid_limit,
reference_,
sicedeal,
swap_id,
broker,
trader,
portfid,
cs,
cb,
cs,
cb,
custid,
siceclaim,
workdays,
exec_limit,
broker2,
cb_nal,
cs_nal,
s_rate,
b_interest,
s_interest,
b_rate,
cid,
cid_limit,
filial_id,
marging,
eventid,
trader_str,
portfid_str,
filial_id_str,
cfu_name,
cfu_odb,
lead_ccy,
custid_mdm,
custid_mdm2,
operday_status,
operday_id1,
conversation,
ndf,
filial_id_odb,
broker_odb,
trader_odb,
swap_rate,
rate_swap,
buy_swap,
sell_swap,
rate_direction,
sec_tradeaccount
--,portfid_str_2 --корректное отображение портфеля по второй стороне межпортфельной сделки
--,sec_tradeaccount_2 корректное отображение торг.счета межпортфеля
,
r_501,
r_553,
inuncia,
buyuncia,
selluncia,
rateuncia,
swap_rateuncia,
forex_uncia_koeff,
sec_tradeaccount_id,
-- ,sec_tradeaccount_id2 -- корректное отображение торгового счета межпортфеля
--,maturity_title -- корректное отображение срока сделки
--,maturity_title_swap --коректное отображение срока второй ноги сделки
fixed_currency,
fixed_currency_swap,
maturity_title_deal,
ticket_swap,
filial_id_odb2,
broker_odb2,
owner_acnt_odb,
owner_acnt_odb2,
fizik_id,
fizik_name,
payinstr_id,
payinstr_name,
birthplace,
systemid,
r_529,
r_530,
ext_system_name,
cust_ext_code,
lead_count,
treaderlogin,
treader_ext_code,
settl_scheme,
add_instr_sell_swap,
add_instr_buy_swap,
cstype,
cbtype,
product_code,
bkt_trdngsysnum,
bkt_rate,
bkt_num,
rate_swapuncia,
buy_swapuncia,
sell_swapuncia,
dealcomision,
cliringcomision,
servicecomision,
contract_id,
contract_number,
contract_date,
contract_odb_id,
r_508,
market_rate,
trd_pl_section_nm,
instrumenttype,
confirmation_type,
fixing_date,
fixing_source,
trade_classcode,
owner_contract_id,
owner_contract_number,
owner_contract_date,
owner_contract_odb_id,
paycurrency,
crossrate_deal_pay,
payamount,
--quantity_unit, в 7.2.0 учитывается еще для продукта precious ndf
paycrossratetype,
paycrossdatebase,
limit_status,
option_deal_init,
dealer_id_odb,
customer_client,
fixing_source_odb,
ticket_part_number
FROM
TABLE ( CAST(f_get_fx_out_by_pocket(cur.idpocket, 'NavXLv5') AS tbl_get_fx_out) )
MINUS
SELECT
acttype,
ticket,
tradedate,
tradetime,
buy,
sell,
rate,
swap,
status,
valuesell,
nett_buy,
typeinput,
r_ticket,
dp_tran,
valuebuy_swap,
valuesell_swap,
--,micexdeal https://confluence.softwell.ru/pages/viewpage.action?pageId=224363033
micexclaim,
trans_pos,
addinfo,
bal_acc_buy,
bal_acc_sell,
bal_acc_term_buy,
bal_acc_term_sell,
exec_ovp,
status_sell,
add_instr_buy,
add_instr_sell,
custid_limit,
reference_,
sicedeal,
swap_id,
broker,
trader,
portfid,
cs,
cb,
cs,
cb,
custid,
siceclaim,
workdays,
exec_limit,
broker2,
cb_nal,
cs_nal,
s_rate,
b_interest,
s_interest,
b_rate,
cid,
cid_limit,
filial_id,
marging,
eventid,
trader_str,
portfid_str,
filial_id_str,
cfu_name,
cfu_odb,
lead_ccy,
custid_mdm,
custid_mdm2,
operday_status,
operday_id1,
conversation,
ndf,
filial_id_odb,
broker_odb,
trader_odb,
swap_rate,
rate_swap,
buy_swap,
sell_swap,
rate_direction,
sec_tradeaccount,
--,portfid_str_2 --корректное отображение портфеля по второй стороне межпортфельной сделки
--,sec_tradeaccount_2 корректное отображение торг.счета межпортфеля
r_501,
r_553,
inuncia,
buyuncia,
selluncia,
rateuncia,
swap_rateuncia,
forex_uncia_koeff,
sec_tradeaccount_id,
-- ,sec_tradeaccount_id2 -- корректное отображение торгового счета межпортфеля
--,maturity_title -- корректное отображение срока сделки
--,maturity_title_swap --коректное отображение срока второй ноги сделки
fixed_currency,
fixed_currency_swap,
maturity_title_deal,
ticket_swap,
filial_id_odb2,
broker_odb2,
owner_acnt_odb,
owner_acnt_odb2,
fizik_id,
fizik_name,
payinstr_id,
payinstr_name,
birthplace,
systemid,
r_529,
r_530,
ext_system_name,
cust_ext_code,
lead_count,
treaderlogin,
treader_ext_code,
settl_scheme,
add_instr_sell_swap,
add_instr_buy_swap,
cstype,
cbtype,
product_code,
bkt_trdngsysnum,
bkt_rate,
bkt_num,
rate_swapuncia,
buy_swapuncia,
sell_swapuncia,
dealcomision,
cliringcomision,
servicecomision,
contract_id,
contract_number,
contract_date,
contract_odb_id,
r_508,
market_rate,
trd_pl_section_nm,
instrumenttype,
confirmation_type,
fixing_date
,
fixing_source,
trade_classcode,
owner_contract_id,
owner_contract_number,
owner_contract_date,
owner_contract_odb_id,
paycurrency,
crossrate_deal_pay,
payamount,
--quantity_unit, в 7.2.0 учитывается и для precious ndf
paycrossratetype,
paycrossdatebase,
limit_status,
option_deal_init,
dealer_id_odb,
customer_client,
fixing_source_odb
,
ticket_part_number
FROM
TABLE ( CAST(f_get_fx_out_by_pocket_old(cur.idpocket, 'NavXLv5') AS tbl_get_fx_out) )
);
IF ( v_count > 0 ) THEN
zzz_ex('Обнаружены расхождения в пакете:' || cur.idpocket || 'сообщите разработчику');
END IF;
END LOOP;
END;