x5gzT2uf

1BEGIN
2-- DECLARE total_products INT;
3-- declare maxRank int;
4-- declare maxRankIter int;
5-- declare tmpTargetID int;
6-- declare i int;
7DECLARE `_rollback` BOOL DEFAULT 0;
8DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
9
10DROP TEMPORARY TABLE IF EXISTS ExportOptions;
11CREATE TEMPORARY TABLE ExportOptions  
12(`Product ID` VARCHAR(1024),
13`PRODUCTACTION` VARCHAR(1024),  -- fill this in with 'create' for new products
14`PRODUCTNAME` varchar(1024),
15`DESCRIPTION` VARCHAR(1024),
16`FORECASTMETHOD` VARCHAR(1024),
17`PRODUCTTYPE` VARCHAR(1024),
18`CREATIVESPECS`  VARCHAR(1024),
19`TARGETINGTEMPLATE`  VARCHAR(1024),
20`HOMECATALOG` VARCHAR(1024),
21`ADDITIONALCATALOG` VARCHAR(1024),
22`TAGS` VARCHAR(1024),
23`ACTIVE`  VARCHAR(1024),
24`FIRST AVAILABLE DATE` VARCHAR(1024),
25`LAST AVAILABLE DATE` VARCHAR(1024),
26`AVAILABILITYNOTES` VARCHAR(1024),
27`GROUPTYPE`  VARCHAR(1024),
28`ISUSERADDITIONALLOWED` VARCHAR(1024),
29`EMBEDDEDTARGET`  VARCHAR(100000),
30`ALLOWEDBOOKINGTYPE` VARCHAR(1024),
31`DEFAULTBOOKINGTYPE` VARCHAR(1024),
32`PRESETSCHEDULE` VARCHAR(1024),
33`HISTORICALREVENUERECOGBASIS` VARCHAR(1024),
34`FUTUREREVENUERECOGBASIS`  VARCHAR(1024),
35`ESTIMATEDPERFORMANCE`  VARCHAR(1024),
36`BILLING_ID`  VARCHAR(1024),
37`MEDIATYPE`  VARCHAR(1024),
38`CustomFields` VARCHAR(100000),
39`Adslot ID`  VARCHAR(1024),
40`PO Action`  VARCHAR(1024),
41`Name`  VARCHAR(1024),
42`Production System`  VARCHAR(1024),
43`Inventory System`  VARCHAR(1024),
44`Media Properties`   VARCHAR(1024),	
45`Loc1`   VARCHAR(1024),	
46`Loc2`   VARCHAR(1024),	
47`Loc3`   VARCHAR(1024),	
48`Loc4`   VARCHAR(1024),	
49`Ad-Size`   VARCHAR(1024),	
50`Special Instruction`   VARCHAR(1024),
51`MDSP NAME`  VARCHAR(1024)
52);
53
54-- Call the IQM Pivot table with the grid results
55call `ProcTargeting`(iqmid);
56
57	START TRANSACTION;
58    alter table OP1TargetOptions
59    add column additionalmappedcataloglocations varchar(1024);
60    
61 --    IF `_rollback` THEN
62 --        ROLLBACK;
63 --    ELSE
64 --        COMMIT;
65 --        SET `_rollback` = 0;
66 --    END IF;
67 
68     START TRANSACTION;
69	alter table OP1TargetOptions
70    add column lastavailabledate varchar(80);
71    
72 --    IF `_rollback` THEN
73 --        ROLLBACK;
74 --    ELSE
75 --        COMMIT;
76 --        SET `_rollback` = 0;
77 --    END IF;
78    
79    START TRANSACTION;
80	alter table OP1TargetOptions
81    add column allowuseradditions varchar(80);
82    
83--     IF `_rollback` THEN
84--         ROLLBACK;
85--     ELSE
86--         COMMIT;
87--         SET `_rollback` = 0;
88--     END IF;
89    
90    START TRANSACTION;
91    alter table OP1TargetOptions
92    add column pfxsize varchar(255);
93    
94 --    IF `_rollback` THEN
95 --        ROLLBACK;
96 --    ELSE
97 --        COMMIT;
98 --        SET `_rollback` = 0;
99 --    END IF;
100
101    START TRANSACTION;
102    alter table OP1TargetOptions
103    add column yieldexsize varchar(255);
104    
105 --    IF `_rollback` THEN
106 --        ROLLBACK;
107 --    ELSE
108 --        COMMIT;
109 --        SET `_rollback` = 0;
110 --    END IF;
111
112     START TRANSACTION;
113     alter table OP1TargetOptions
114     add column dateavailableinpfx varchar(255);
115    
116--     IF `_rollback` THEN
117--         ROLLBACK;
118--     ELSE
119--         COMMIT;
120--         SET `_rollback` = 0;
121--     END IF;
122    
123
124    
125--     START TRANSACTION;
126--     alter table OP1TargetOptions
127--     add column adtype varchar(255);
128    
129--     IF `_rollback` THEN
130--         ROLLBACK;
131--     ELSE
132--         COMMIT;
133--         SET `_rollback` = 0;
134--     END IF;
135    
136    START TRANSACTION;
137    alter table OP1TargetOptions
138    add column adtypes varchar(1024);
139    
140--     IF `_rollback` THEN
141--         ROLLBACK;
142--     ELSE
143--         COMMIT;
144--         SET `_rollback` = 0;
145--     END IF;
146    
147--     START TRANSACTION;
148--     alter table OP1TargetOptions
149--     add column minimumspendamt varchar(80);
150    
151--     IF `_rollback` THEN
152--         ROLLBACK;
153--     ELSE
154--         COMMIT;
155--         SET `_rollback` = 0;
156--     END IF;
157    
158--     START TRANSACTION;
159--     alter table OP1TargetOptions
160--     add column fixedimpressiongoal varchar(80);
161    
162--     IF `_rollback` THEN
163--         ROLLBACK;
164--     ELSE
165--         COMMIT;
166--         SET `_rollback` = 0;
167--     END IF;
168    
169--     START TRANSACTION;
170--     alter table OP1TargetOptions
171--     add column sponsorshiplength varchar(80);
172    
173 		IF `_rollback` THEN
174          ROLLBACK;
175 		ELSE
176         COMMIT;
177          SET `_rollback` = 0;     
178 		END IF;
179
180
181-- CREATE TEMPORARY TABLE if not exists OP1TargetOptions  
182-- (iqmid int(10));
183
184-- set i=(select count(*) from OP1TargetOptions);
185
186	
187
188 -- if i>0 then
189
190-- select count(*) into total_products from  OP1TargetOptions;
191
192
193
194 -- if total_products>0 then
195 
196 
197	-- 	set @ddl=CONCAT('ALTER TABLE ',dbName,'.',OP1TargetOptions,
198	-- 		' ADD COLUMN ',fieldName,' ',fieldDef);
199	-- 	prepare stmt from @ddl;
200	-- 	execute stmt;
201    
202	
203 -- select total_products;
204
205call sp_Product_Insert();
206 
207--  select `_rollback`;
208 
209--  set total_products=total_products-1;
210 
211--  end if;
212select * from ExportOptions;
213--  end if;
214END