1.该脚本为PCISS项目的sguf冲销脚本：
DECLARE
type typ_sguf_table is table of sguf_rowid_tab_1%rowtype ;
sguf_tab typ_sguf_table;
v_insert_sql cLOB;
BEGIN
SELECT * BULK COLLECT INTO sguf_tab
FROM sguf_rowid_tab_1 s
WHERE s.ID=1053235 AND s.NO='BSD20170901000054' ;
IF sguf_tab.last >1 THEN
RETURN ;
ELSE
FOR i IN sguf_tab.first .. sguf_tab.last LOOP v_insert_sql :='
INSERT INTO sguf_rowid_tab_1
VALUES ('||
sguf_tab(i).ID ||' , '||
chr(39)||sguf_tab(i).PRODUCTCODE||chr(39) ||' , '||
chr(39)||sguf_tab(i).NO||chr(39) ||' , '||
chr(39)||sguf_tab(i).SUBNO||chr(39) ||' , '||
'to_date('||CHR(39)||sguf_tab(i).ACCOUNTDATE||CHR(39)||')' ||' , '||
'to_date('||CHR(39)||sguf_tab(i).DEALDATE||CHR(39)||')' ||' , '||
'to_date('||CHR(39)||sguf_tab(i).STOPDATE||CHR(39)||')' ||' , '||
chr(39)||sguf_tab(i).SIDE||chr(39) ||' , '||
sguf_tab(i).MEMBERID ||' , '||
chr(39)||sguf_tab(i).MEMBERFULLNAME||chr(39) ||' , '||
chr(39)||sguf_tab(i).MEMBERABBRNAME||chr(39) ||' , '||
chr(39)||sguf_tab(i).MEMBERCODE||chr(39) ||' , '||
chr(39)||sguf_tab(i).MEMBERPARTCODE||chr(39) ||' , '||
chr(39)||sguf_tab(i).MEMBERTYPE||chr(39) ||' , '||
chr(39)||sguf_tab(i).DEALCURRENCY||chr(39) ||' , '||
chr(39)||sguf_tab(i).DEALOUTCURRENCY||chr(39) ||' , '||
CASE WHEN sguf_tab(i).EXCHANGERATE IS NULL THEN 'NULL' ELSE sguf_tab(i).EXCHANGERATE END ||' , '||
CASE WHEN sguf_tab(i).DEALPRICE<>0 THEN '-'||sguf_tab(i).DEALPRICE ELSE '' END ||' , '||
CASE WHEN sguf_tab(i).DEALPRICECNY<>0 THEN '-'||sguf_tab(i).DEALPRICECNY ELSE '' END ||' , '||
CASE WHEN sguf_tab(i).DEALPRICEUSD <>0 THEN '-'||sguf_tab(i).DEALPRICEUSD ELSE '' END ||' , '||
CASE WHEN sguf_tab(i).MEMBERFEEPRICE <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICE ELSE '' END ||' , '||
CASE WHEN sguf_tab(i).MEMBERFEEPRICECNY <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICEcny ELSE '' END ||' , '||
CASE WHEN sguf_tab(i).MEMBERFEEPRICEUSD <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICEusd ELSE '' END ||' , '||
chr(39)||sguf_tab(i).DEPARTMENT||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PTCOUNT IS NULL THEN 'NULL' ELSE sguf_tab(i).PTCOUNT||'' END ||' , '||
'to_date('||CHR(39)||sguf_tab(i).STARTDATE||CHR(39)||')' ||' , '||
'to_date('||CHR(39)||sguf_tab(i).MATURITYDATE||CHR(39)||')' ||' , '||
CASE WHEN sguf_tab(i).RATE IS NULL THEN 'NULL' ELSE sguf_tab(i).RATE||'' END ||' , '||
CASE WHEN sguf_tab(i).SELFBROKERID IS NULL THEN 'NULL' ELSE sguf_tab(i).SELFBROKERID||'' END ||' , '||
chr(39)||sguf_tab(i).SELFBROKERCODE||chr(39) ||' , '||
chr(39)||sguf_tab(i).SELFBROKERNAME||chr(39) ||' , '||
CASE WHEN sguf_tab(i).FORMCREATORID IS NULL THEN 'NULL' ELSE sguf_tab(i).FORMCREATORID||'' END ||' , '||
chr(39)||sguf_tab(i).FORMCREATORNAME||chr(39) ||' , '||
CASE WHEN sguf_tab(i).FORMCREATORTIME IS NULL THEN 'NULL' ELSE chr(39)||sguf_tab(i).FORMCREATORTIME||chr(39) END ||' , '||
CASE WHEN sguf_tab(i).ACCOUNTDAYS IS NULL THEN 'NULL' ELSE sguf_tab(i).ACCOUNTDAYS||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID1 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID1||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE1||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME1||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY1=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY1 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY1=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY1 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD1=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD1 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT1 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT1||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID2 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID2||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE2||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME2||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY2=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY2 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY2=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYcny2 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD2=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYusd2 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT2 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT2||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID3 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID3||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE3||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME3||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY3=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY3 end ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY3=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYcny3 end ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD3=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYusd3 end ||' , '||
CASE WHEN sguf_tab(i).PERCENT3 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT3||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID4 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID4||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE4||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME4||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY4=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY4 end ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY4=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYcny4 end ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD4=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD4 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT4 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT4||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID5 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID5||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE5||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME5||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY5=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY5 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY5=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY5 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD5=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD5 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT5 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT5||'55---' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID6 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID6||'66--' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE6||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME6||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY6=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY6 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY6=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY6 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD6=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD6 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT6 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT6||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID7 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID7||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE7||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME7||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY7=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY7 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY7=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY7 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD7=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD7 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT7 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT7||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID8 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID8||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE8||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME8||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY8=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY8 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY8=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY8 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD8=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD8 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT8 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT8||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID9 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID9||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE9||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME9||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY9=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY9 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY9=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY9 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD9=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD9 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT9 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT9||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERID10 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID10||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODE10||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAME10||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEY10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY10=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEY10 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNY10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY10=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNY10 END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSD10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD10=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSD10 END ||' , '||
CASE WHEN sguf_tab(i).PERCENT10 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT10||'' END ||' , '||
CASE WHEN sguf_tab(i).BROKERIDX IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERIDX||'' END ||' , '||
chr(39)||sguf_tab(i).BROKERCODEX||chr(39) ||' , '||
chr(39)||sguf_tab(i).BROKERNAMEX||chr(39) ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYX=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYX END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYCNYX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNYX=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYCNYX END ||' , '||
CASE WHEN sguf_tab(i).PARTYMONEYUSDX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSDX=0 THEN '' ELSE '-'||sguf_tab(i).PARTYMONEYUSDX END ||' , '||
CASE WHEN sguf_tab(i).PERCENTX IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENTX||'' END ||' , '||
'NULL' ||' , '||
'NULL' ||' , '||
chr(39)||sguf_tab(i).REM||chr(39) ||' , '||
chr(39)||sguf_tab(i).BONDGP||chr(39) ||' , '||
chr(39)||sguf_tab(i).TAXRATE||chr(39) ||' , '||
chr(39)||sguf_tab(i).TAXWAY||chr(39) ||' , '||
CASE WHEN sguf_tab(i).TAXPRICE = 0 THEN '' ELSE '-'||sguf_tab(i).TAXPRICE END ||' , '||
CASE WHEN sguf_tab(i).TAXPRICECNY =0 THEN '' ELSE '-'||sguf_tab(i).TAXPRICECNY END ||' , '||
CASE WHEN sguf_tab(i).TAXPRICEUSD =0 THEN '' ELSE '-'||sguf_tab(i).TAXPRICEUSD END ||' , '||
CASE WHEN sguf_tab(i).COMPANYFEEPRICE =0 THEN '' ELSE '-'||sguf_tab(i).COMPANYFEEPRICE END ||' , '||
CASE WHEN sguf_tab(i).COMPANYFEEPRICECNY =0 THEN '' ELSE '-'||sguf_tab(i).COMPANYFEEPRICECNY END ||' , '||
CASE WHEN sguf_tab(i).COMPANYFEEPRICEUSD =0 THEN '' ELSE '-'||sguf_tab(i).COMPANYFEEPRICEUSD END
||') ;' ; END LOOP;
END IF ;
INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE(NO,Strsql) VALUES ('BSD20170908000830',v_insert_sql);
--dbms_output.put_line(v_insert_sql);
END ;
2.该脚本为sguf与mian表的数据核对脚本：
SELECT
sguf.sgufmaxid,
MAINtab.no,
MAINtab.ma_memberid,
MAINtab.BSFLAG,
MAINtab.maFEEPRICE,
sguf.sguffeeprice,
(MAINtab.maFEEPRICE-abs(sguf.sguffeeprice)) AS balanceFreePrice,
MAINtab.maFEEPRICEcny,
sguf.sguffeepricecny,
(MAINtab.maFEEPRICEcny-abs(sguf.sguffeepricecny)) AS balanceFreePricecny,
MAINtab.maFEEPRICEusd,
sguf.sguffeepriceusd,
(MAINtab.maFEEPRICEusd-abs(sguf.sguffeepriceusd)) AS balanceFreePricecusd
FROM (
SELECT M.NO, 'B' AS BSFLAG, m.buyerid AS ma_memberid ,
M.BUYERFEEPRICE maFEEPRICE ,M.BUYERFEEPRICEcny maFEEPRICEcny ,M.BUYERFEEPRICEusd maFEEPRICEusd FROM HIS_PRODUCT_DEAL_MAIN M
WHERE M.STATE = 9
-- AND M.BUYERID = 1216
-- AND M.PRODUCTID = 4
AND TO_CHAR(M.CREATETIME, 'yyyymmdd') BETWEEN '' AND ''
UNION ALL
SELECT M.NO, 'L' AS BSFLAG,m.sellerid AS ma_memberid,
M.sellerFEEPRICE maFEEPRICE ,M.sellerFEEPRICEcny maFEEPRICEcny ,M.sellerFEEPRICEusd maFEEPRICEusd
FROM HIS_PRODUCT_DEAL_MAIN M
WHERE M.STATE = 9
-- AND M.SELLERID = 1216
-- AND M.PRODUCTID = 4
AND TO_CHAR(M.CREATETIME, 'yyyymmdd') BETWEEN '' AND '' ) MAINtab ,
(
SELECT MAX(ID) AS sgufmaxid, s.no ,s.side,s.memberid,COUNT(s.no) nocount,
SUM(s.memberfeeprice) sguffeeprice,
SUM(s.memberfeepricecny) sguffeepricecny,
SUM(s.memberfeepriceusd) sguffeepriceusd
FROM SGUF_PRODUCT_DEAL_MAIN S
/* WHERE S.MEMBERID = 1216 AND to_char(s.dealdate,'yyyymmdd') BETWEEN '20171001' AND '20171031'*/
GROUP BY s.no,s.side ,s.memberid ) sguf
WHERE MAINtab.no=sguf.no(+) AND
MAINtab.bsflag=sguf.side(+) AND
MAINtab.ma_memberid=sguf.memberid(+) AND (
(MAINtab.maFEEPRICE-abs(sguf.sguffeeprice))<>0 OR
(MAINtab.maFEEPRICEcny-abs(sguf.sguffeepricecny))<>0 OR
(MAINtab.maFEEPRICEusd-abs(sguf.sguffeepriceusd))<>0 )
3.该脚本为修改sguf表中分摊错误的数据
DECLARE
max_indexvalue INT ;
TYPE typ_rowid_table IS TABLE OF VARCHAR2(100) ;
TYPE TYP_percent_TABLE IS TABLE OF NUMBER ;
TYPE TYP_IndexValue_TABLE IS TABLE OF integer ;
TYPE typ_percent_recode IS record (
v_srNO Varchar2(100),
v_memberfeeprice number ,
v_memberfeepricecny number ,
v_memberfeepriceusd number ,
v_sum_freeprice number ,
v_sum_freepricecny number ,
v_sum_freepriceusd number ,
v_percent1 number ,
v_percent2 number ,
v_percent3 number ,
v_percent4 number ,
v_percent5 number ,
v_percent6 number ,
v_percent7 number ,
v_percent8 number ,
v_percent9 number ,
v_percent10 number
);
CURSOR sguf_cur IS SELECT sr.row_id FROM sguf_rowid_tab_1 sr /*WHERE sr.row_id='AAA+awAAFAABHs3AAC'*/ ;
--v_rowid VARCHAR2(100) ;
calc_freeprice_sum NUMBER;
calc_freepricecny_sum NUMBER;
calc_freepriceusd_sum NUMBER;
tab_rowid typ_rowid_table ;
REC_PR typ_percent_recode ;
TAB_INDEX TYP_IndexValue_TABLE := TYP_IndexValue_TABLE(NULL);
TAB_PT TYP_percent_TABLE := TYP_percent_TABLE(NULL);
v_balance NUMBER ;
v_balancecny NUMBER ;
v_balanceusd NUMBER ;
str_Fentan_sql VARCHAR2(1000);
Str_sql_Update VARCHAR2(1000);
all_sql VARCHAR2(4000); BEGIN OPEN sguf_cur ;
LOOP
FETCH sguf_cur BULK COLLECT INTO tab_rowid ;
EXIT WHEN sguf_cur%NOTFOUND ;
END LOOP ; FOR x IN tab_rowid.first .. tab_rowid.last LOOP
str_Fentan_sql :='';
Str_sql_Update := '';
max_indexvalue :=0 ;
all_sql :='';
v_balance :=0 ;
v_balancecny :=0 ;
v_balanceusd :=0 ;
calc_freeprice_sum :=0 ;
calc_freepricecny_sum :=0 ;
calc_freepriceusd_sum :=0 ;
SELECT S.NO,
S.MEMBERFEEPRICE,
S.MEMBERFEEPRICECNY,
S.MEMBERFEEPRICEUSD,
ABS(NVL(S.PARTYMONEY1, 0) + NVL(S.PARTYMONEY2, 0) +
NVL(S.PARTYMONEY3, 0) + NVL(S.PARTYMONEY4, 0) +
NVL(S.PARTYMONEY5, 0) + NVL(S.PARTYMONEY6, 0) +
NVL(S.PARTYMONEY7, 0) + NVL(S.PARTYMONEY8, 0) +
NVL(S.PARTYMONEY9, 0) + NVL(S.PARTYMONEY10, 0)) AS SUM_FREEPRICE,
ABS(NVL(S.PARTYMONEYCNY1, 0) + NVL(S.PARTYMONEYCNY2, 0) +
NVL(S.PARTYMONEYCNY3, 0) + NVL(S.PARTYMONEYCNY4, 0) +
NVL(S.PARTYMONEYCNY5, 0) + NVL(S.PARTYMONEYCNY6, 0) +
NVL(S.PARTYMONEYCNY7, 0) + NVL(S.PARTYMONEYCNY8, 0) +
NVL(S.PARTYMONEYCNY9, 0) + NVL(S.PARTYMONEYCNY10, 0)) AS SUM_FREEPRICECNY,
ABS(NVL(S.PARTYMONEYUSD1, 0) + NVL(S.PARTYMONEYUSD2, 0) +
NVL(S.PARTYMONEYUSD3, 0) + NVL(S.PARTYMONEYUSD4, 0) +
NVL(S.PARTYMONEYUSD5, 0) + NVL(S.PARTYMONEYUSD6, 0) +
NVL(S.PARTYMONEYUSD7, 0) + NVL(S.PARTYMONEYUSD8, 0) +
NVL(S.PARTYMONEYUSD9, 0) + NVL(S.PARTYMONEYUSD10, 0)) AS SUM_FREEPRICEUSD,
S.PERCENT1,
S.PERCENT2,
S.PERCENT3,
S.PERCENT4,
S.PERCENT5,
S.PERCENT6,
S.PERCENT7,
S.PERCENT8,
S.PERCENT9,
S.PERCENT10
INTO REC_PR
FROM SGUF_ROWID_TAB_1 S
WHERE s.row_id =tab_rowid(x);
--dbms_output.put_line(tab_rowid(x)); ------得到总共拆分了几个经纪人，每个经纪人的拆分比例是多少-------------
TAB_PT.delete ;
TAB_PT.extend(10);
TAB_PT(1) := REC_PR.v_percent1;
TAB_PT(2) := REC_PR.v_percent2;
TAB_PT(3) := REC_PR.v_percent3;
TAB_PT(4) := REC_PR.v_percent4;
TAB_PT(5) := REC_PR.v_percent5;
TAB_PT(6) := REC_PR.v_percent6;
TAB_PT(7) := REC_PR.v_percent7;
TAB_PT(8) := REC_PR.v_percent8;
TAB_PT(9) := REC_PR.v_percent9;
TAB_PT(10) := REC_PR.v_percent10;
TAB_INDEX.delete ; FOR I IN TAB_PT.first .. TAB_PT.last LOOP IF TAB_PT(I) IS NOT NULL THEN
TAB_INDEX.extend(1);
TAB_INDEX(TAB_INDEX.last) :=i ;
calc_freeprice_sum :=calc_freeprice_sum+ round(abs(REC_PR.v_memberfeeprice) * TAB_PT(i)/100 ,2) ;
calc_freepricecny_sum := calc_freepricecny_sum+ round(abs(REC_PR.v_memberfeepricecny) * TAB_PT(i)/100 ,2);
calc_freepriceusd_sum := calc_freepriceusd_sum+ round(abs(REC_PR.v_memberfeepriceusd) * TAB_PT(i)/100 ,2);
END IF ;
END LOOP ;
dbms_output.put_line('calc_freeprice_sum='||calc_freeprice_sum);
dbms_output.put_line('calc_freepricecny_sum='||calc_freepricecny_sum);
dbms_output.put_line('calc_freepriceusd_sum='||calc_freepriceusd_sum);
dbms_output.put_line('---------------------------------------------------------------------------');
v_balance := abs(REC_PR.v_memberfeeprice) - calc_freeprice_sum ;
v_balancecny := abs(REC_PR.v_memberfeepricecny) - calc_freepricecny_sum ;
v_balanceusd := abs(REC_PR.v_memberfeepriceusd) - calc_freepriceusd_sum ;
--max_indexvalue 拆了几个经纪人，最大经纪人编号；
max_indexvalue :=TAB_INDEX.last ;
IF REC_PR.v_sum_freeprice<>ABS(REC_PR.v_memberfeeprice) THEN
FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP
Str_sql_Update :='UPDATE sguf_rowid_tab_1 s1 '||
'SET s1.partymoney'||TAB_INDEX(i)||'='||ROUND(REC_PR.v_memberfeeprice *TAB_PT(TAB_INDEX(i))/100,2)||
' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39);
--EXECUTE IMMEDIATE Str_sql_Update ;
all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10);
--dbms_output.put_line('all_sql='||all_sql );
END LOOP ;
--dbms_output.put_line('all_sql='||all_sql );
INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU
VALUES
(TAB_ROWID(X),
REC_PR.V_SRNO,
ALL_SQL,
V_BALANCE,
REC_PR.V_MEMBERFEEPRICE,
calc_freeprice_sum,
REC_PR.V_MEMBERFEEPRICEcny,
calc_freepricecny_sum,
REC_PR.V_MEMBERFEEPRICEusd,
calc_freepriceusd_sum
);
all_sql :=' ';
END IF ; IF REC_PR.v_sum_freepricecny<>ABS(REC_PR.v_memberfeepricecny) THEN
FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP
Str_sql_Update :='
UPDATE sguf_rowid_tab_1 s1 set '||
's1.partymoneycny'||TAB_INDEX(i)||'='||round(REC_PR.v_memberfeepricecny *TAB_PT(TAB_INDEX(i))/100,2)||
' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39);
--EXECUTE IMMEDIATE Str_sql_Update ;
all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10); END LOOP ;
INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU
VALUES
(TAB_ROWID(X),
REC_PR.V_SRNO,
ALL_SQL,
V_BALANCE,
REC_PR.V_MEMBERFEEPRICE,
calc_freeprice_sum,
REC_PR.V_MEMBERFEEPRICEcny,
calc_freepricecny_sum,
REC_PR.V_MEMBERFEEPRICEusd,
calc_freepriceusd_sum
);
all_sql :=' '; END IF ; IF REC_PR.v_sum_freepriceusd<>ABS(REC_PR.v_memberfeepriceusd) THEN FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP
Str_sql_Update :='
UPDATE sguf_rowid_tab_1 s1 set '||
's1.partymoneyusd'||TAB_INDEX(i)||'='||round(REC_PR.v_memberfeepriceusd *TAB_PT(TAB_INDEX(i))/100,2)||
' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39);
--EXECUTE IMMEDIATE Str_sql_Update ;
all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10);
--dbms_output.put_line('all_sql='||all_sql );
END LOOP ;
INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU
VALUES
(TAB_ROWID(X),
REC_PR.V_SRNO,
ALL_SQL,
V_BALANCE,
REC_PR.V_MEMBERFEEPRICE,
calc_freeprice_sum,
REC_PR.V_MEMBERFEEPRICEcny,
calc_freepricecny_sum,
REC_PR.V_MEMBERFEEPRICEusd,
calc_freepriceusd_sum
);all_sql :=' ';
END IF ;
COMMIT ;
END LOOP ;
END ;