--Daily "warranty_sumamry" InsertINSERT INTO warranty_summary(SELECT warranty_id, payment_transaction_id, subscription_id,seller_seal_id, invoice_id,ds.channel,ds.store_id,esp_order_id,reseller_product_id,ptli_id,category_id,ds.installments,ds.Number_of_installments_Paid,warranty_value,warranty_term, trans_status,ds.warranty_status,ds.type, transaction_create_date,warranty_create_date, warranty_start_date, warranty_end_date, item_purchase_date,item_purchase_price,item_number,item_condition,sku,sku_class, unit_price,amount,line_item_amount,CASE WHEN (invoice_id = 'SamsClub' and store_id ilike 'SamsCS') THEN 0 else revenue end as revenue,CASE WHEN (seller_seal_id = 'subscrip_elsa14071410' and invoice_id ilike 'Elisa') THEN (revenue + tax_amt) else reseller_cost end as reseller_cost,---FIN-299adj_reserve,Full_Reserve,pro_rated_reserve,case WHEN (ds.installments > 1 and ds.seller_seal_id in ('subscrip_014784154071','subscrip_014716754652', 'subscrip_059614394029','subscrip_074768984025','subscrip_082731470123')) then commission_percent*unit_price WHEN (ds.campaign ilike '%iphone_app%' AND invoice_id ilike 'Staples App%' ) then commission_percent*revenue WHEN (invoice_id = 'costco' and ds.reseller_product_id in ('689986','865685')) then 10 WHEN (invoice_id = 'SamsClub' and store_id ilike 'SamsCS') then 0 WHEN (seller_seal_id = 'subscrip_014790857874' and cast(warranty_create_date as date) > '12/31/2013') THEN revenue*commission_percent ---ebay UKwhen (seller_seal_id = 'subscrip_elsa14071410') then (ds.commission_percent * ds.AMOUNT)/(1 + 0.24) --FIN-299 November Close Activities --FIN-161 Required for september closewhen seller_seal_id in ('subscrip_acfx04112014', 'subscrip_acfx04122014') then 0.2*ds.warranty_value --AccentFX WHEN (seller_seal_id = 'subscrip_014737566562' and cast(warranty_create_date as date) > '12/31/2012' and ds.invoice_id in ('Amazon US Kindle','US 2 Year for Kindle E-ink (old)','US 2 Year for Kindle Fire HD 7"','US 2 Year for Kindle Fire HD Gen6','US 2 Year for Kindle Fire HDX Wifi Gen6','US 2 Year for Kindle Paperwhite wifi (2013)','US 2 Year for Kindle Paperwhite WAN (2013)','US 2 Year for Kindle Fire HDX 4G Gen6'))THEN (ds.warranty_value - ds.wholes_price) else commission_percent*warranty_value end as commission_amount,commission_percent, inclusive_tax_rate,tax_amt,st_tax,"CAD%",CA_tax_amt,case when seller_seal_id = 'subscrip_elsa14071410' then admin_fee_percent * coalesce(adj_reserve,0) --FIN-299 November Close activitieswhen charge_classification = 'New_Installment' then admin_fee_percent * coalesce(full_reserve,0) else admin_fee_percent * coalesce(adj_reserve,0) end as admin_fee,admin_fee_percent,case when charge_classification = 'New_Installment' then obligor_fee_percent * coalesce(full_reserve,0) else obligor_fee_percent * coalesce(adj_reserve,0) end as obligor_fee,obligor_fee_percent,currency_type,exchange_rate,charge_classification,cyber_src_ref_num,obligor,insurer, state_code,state_of_jurisdiction, last_status_change_date,days_used,month_recogn, ipt,ds.campaign,ds.source,CASE WHEN (warranty_create_date::Date < '1/1/2014' and (invoice_id = 'SystemaxUS' or invoice_id = 'SystemaxPR' or invoice_id = 'SamsClub') and reseller_product_id ilike 'RDS%') THEN 2.35 else null end as tech_forward,ds.wholes_price,ds.seller_id,ds.locale,updated,warranty_purchase_date,CASE when seller_seal_id in ('subscrip_014704010111','subscrip_014746710116','subscrip_014706967802') THEN (CASE when campaign like '%PNA_mailer' THEN 0.3*warranty_value when campaign like '%PNA_mailer:email1%' THEN 0.3*warranty_value when campaign like '%PNA_mailer:phone%' THEN 0.3*warranty_valuewhen campaign like '%VIZ_mailer' THEN 0.3*warranty_value when campaign like '%VIZ_mailer:email1%' THEN 0.3*warranty_value when campaign like '%VIZ_mailer:phone%' THEN 0.3*warranty_value when campaign like '%sanyo:callcenter%' THEN 0.25*warranty_value when seller_seal_id in ('subscrip_014704010111','subscrip_014746710116') THEN 0.25*warranty_value ELSE 0 END) ELSE 0 END as additional_commissionfrom( SELECT current_date-1 as updated,rpd.id as warranty_id,pt.id as payment_transaction_id,s.id as subscription_id,s.seller_seal_id as seller_seal_id,--INVOICE ID--New INVOICE ID CASE when s.seller_seal_id = 'subscrip_atin14081416' then 'AT&T International'---FIN-221 when s.seller_seal_id = 'subscrip_elsa14071410' then 'Elisa'---FIN-221when s.seller_seal_id = 'subscrip_dhgt14082812' then 'DH_Gate'----FIN-161when s.seller_seal_id = 'subscrip_hwpk14090415' then 'HP' ----FIN-299 November close--FIN-161 required for september 2014 closewhen s.seller_seal_id in ('subscrip_acfx04112014', 'subscrip_acfx04122014') then 'AccentFX' when (s.seller_seal_id = 'subscrip_014774162819' and s.type = 'resale_item_warranty') then 'Tech Data' when (s.seller_seal_id = 'subscrip_014790519901' and s.type = 'resale_item_warranty') then 'Tesco' when (s.seller_seal_id = 'subscrip_tsdi13050914' and s.type = 'resale_item_warranty') then 'Tesco Direct' when (s.seller_seal_id in ('subscrip_tsem13070517') and s.type = 'resale_item_warranty') then 'Tesco Tablets' WHEN s.seller_seal_id in ('subscrip_gspr13101713', 'subscrip_gsmn13101711') THEN 'GSM Nation'when (s.type = 'resale_item_warranty' and s.seller_seal_id = 'subscrip_lyty13061913') then 'ST Rewards Program' when (s.seller_seal_id = 'subscrip_014719938103' and s.type = 'resale_item_warranty') then 'ASDA' when (s.seller_seal_id = 'subscrip_bcam13021214' and s.type = 'resale_item_warranty') then 'Beach' when (s.seller_seal_id = 'subscrip_bdig13021214' and s.type = 'resale_item_warranty') then 'BuyDig' when (s.seller_seal_id in ('subscrip_quil13070903', 'subscrip_mapr13070903') and s.type = 'resale_item_warranty') then 'Quill'WHEN (s.seller_seal_id = 'subscrip_qdsi14022103' AND s.type = 'resale_item_warranty') THEN 'Quidsi'WHEN s.seller_seal_id = 'subscrip_082731470123' AND rpd.campaign ilike 'p1c' THEN 'Pacific1Cellular'WHEN (s.seller_seal_id = 'subscrip_014736374199' AND s.type = 'resale_item_warranty') THEN 'B&H' WHEN s.seller_seal_id in ('subscrip_014745127021','subscrip_014726127026','subscrip_024745127020','subscrip_034745127029','subscrip_044745127028','subscrip_024726127025','subscrip_054745127027') THEN 'UBREAKIFIX' when (s.seller_seal_id in ('subscrip_spls13070903','subscrip_spdc13070903','subscrip_spla13070903') and s.type = 'resale_item_warranty') then 'Staples' when (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.type = 'resale_item_warranty' and rpd.store_id ilike 'WEB') then 'B&H' when (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.type = 'resale_item_warranty'and rpd.store_id ilike 'PHO') then 'B&H' when (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.type = 'resale_item_warranty'and rpd.store_id ilike 'STO') then 'B&H' when (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.type = 'resale_item_warranty' and rpd.store_id ilike 'MO') then 'B&H' when (s.type = 'item_warranty' and rpd.campaign like 'bs_war_affiliate_widget:bhphotovideo_florida%') then 'B&H'when (s.create_date >= '10/15/2012' and ((s.type = 'item_warranty' and (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') or pi.marketplace_detail in ('B&H:%','B&H Photo%','B&H Photo Video','B&H Photo Video AMT','B&HPhoto%','B&HVideo/Audio%','B & H:%','B & H Photo%', 'BandH:%', 'BandH.com%','www.BandH.com%','BandH Photo%','B and H:%','B and H%','B and H Photo%','b and H photo and video%','B_and_H:%','B_and_H_PHOTO%','BH:%','BH Photo%','BH Photo Video%','bhphoto.com%', 'www.bhphoto.com%', 'bhphotovideo.com%','www.bhphotovideo.com%','B-H:%','b-h photo%','B-H Photo, NY, NY%'))) or (pi.seller_marketplace_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty'))) then 'B&H' when (s.type = 'resale_item_warranty' and cast(s.create_date as date) > '12/31/2013' and s.seller_seal_id in ( 'subscrip_014757566566', 'subscrip_014737566562' )) then 'Amazon US Kindle' when (s.type = 'resale_item_warranty' and cast(s.create_date as date) > '12/31/2013' and s.seller_seal_id in ( 'subscrip_014791575968','subscrip_014732575965','subscrip_014788267487','subscrip_014799467487','subscrip_014720666569' )) then 'Amazon UK Kindle'WHEN (s.seller_seal_id = 'subscrip_amzn13050310' and s.type = 'resale_item_warranty' and cast(s.create_date as date) > '12/31/2013' ) then 'Amazon CA Kindle' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2US') then 'US 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2US') then 'US 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLE3') then 'US 2 Year for Kindle Fire (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLE1SO') then 'US 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3US') then 'US 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3US') then 'US 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2UK') then 'UK 2 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2UK') then 'UK 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2UK') then 'UK 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1UK') then 'UK 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2UK') then 'UK 2 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2UK') then 'UK 2 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3UK') then 'UK 3 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3UK') then 'UK 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3UK') then 'UK 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3UK') then 'UK 3 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3UK') then 'UK 3 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'UK-KN0124N3A') then 'UK 3 Year for Kindle Keyboard Wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'UK-KN0174N3A') then 'UK 3 Year for Kindle Keyboard 3G + Wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2UK') then 'UK 3 Year for KindleTouchWiFi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3UK') then 'UK 3 Year for KindleTouch 3G' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3UK') then 'UK 3 Year for Kindle Fire HD 8.9"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3DE') then 'DE 3 Year for Kindle Fire HD 8.9' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2DE') then 'DE 2 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2DE') then 'DE 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2DE') then 'DE 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1DE') then 'DE 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2DE') then 'DE 2 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2DE') then 'DE 2 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3DE') then 'DE 3 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3DE') then 'DE 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3DE') then 'DE 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3DE') then 'DE 3 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3DE') then 'DE 3 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'DE-KN0174N3A') then 'DE 3 Year for Kindle Keyboard Wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'DE-KN0199N3A') then 'DE 3 Year for Kindle Keyboard 3G + Wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2DE') then 'DE 3 Year for KindleTouchWiFi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3DE') then 'DE 3 Year for KindleTouch 3G' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3FR') then 'FR 3 Year for Kindle Fire HD 8.9' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2FR') then 'FR 2 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2FR') then 'FR 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2FR') then 'FR 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1FR') then 'FR 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2FR') then 'FR 2 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2FR') then 'FR 2 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3FR') then 'FR 3 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3FR') then 'FR 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3FR') then 'FR 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3FR') then 'FR 3 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3FR') then 'FR 3 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2FR') then 'FR 3 Year for KindleTouchWiFi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3FR') then 'FR 3 Year for KindleTouch 3G' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3ES') then 'ES 3 Year for Kindle Fire HD 8.9' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2ES') then 'ES 2 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2ES') then 'ES 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2ES') then 'ES 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1ES') then 'ES 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2ES') then 'ES 2 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2ES') then 'ES 2 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3ES') then 'ES 3 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3ES') then 'ES 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3ES') then 'ES 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3ES') then 'ES 3 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3ES') then 'ES 3 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2ES') then 'ES 3 Year for KindleTouchWiFi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3ES') then 'ES 3 Year for KindleTouch 3G' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3IT') then 'IT 3 Year for Kindle Fire HD 8.9' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2IT') then 'IT 2 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2IT') then 'IT 2 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2IT') then 'IT 2 Year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1IT') then 'IT 2 Year for Kindle E-ink (old)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2IT') then 'IT 2 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2IT') then 'IT 2 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3IT') then 'IT 3 Year for Kindle Fire (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3IT') then 'IT 3 Year for Kindle Fire HD 7"' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3IT') then 'IT 3 year for Kindle E-ink (new)' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3IT') then 'IT 3 Year for Kindle Paperwhite WAN' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3IT') then 'IT 3 Year for Kindle Paperwhite wifi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2IT') then 'IT 3 Year for KindleTouchWiFi' when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3IT') then 'IT 3 Year for KindleTouch 3G'when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED200C2US') then 'US 2 Year for Kindle Fire HD Gen6'when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED201C2US') then 'US 2 Year for Kindle Fire HDX Wifi Gen6'when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED205A2US') then 'US 2 Year for Kindle Paperwhite wifi (2013)'when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED205B2US') then 'US 2 Year for Kindle Paperwhite WAN (2013)'when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED206C2US') then 'US 2 Year for Kindle Fire HDX 4G Gen6' WHEN (s.SELLER_SEAL_ID = 'subscrip_014775729103' AND s.TYPE = 'resale_item_warranty') then 'Orango' when rpd.campaign like 'bs_war_merch_orango_ppm%' then 'Orango' when (s.seller_seal_id = 'subscrip_014740867809' and s.type = 'resale_item_warranty') then 'Panasonic'when (s.seller_seal_id = 'subscrip_014704010111' and s.type = 'item_warranty') or (pi.seller_marketplace_seal_id = 'subscrip_014704010111' and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty') then 'Panasonic' when ( rpd.campaign ilike 'bs_war_merch_panasonic_LP' --40% or rpd.campaign ilike 'bs_war_affiliate_widget:interaxisTY1012' --40% or rpd.campaign ilike 'bs_war_merch_interaxis_account' --40% or rpd.campaign ilike 'bs_war_merch_panasonic_phone' --40% or rpd.campaign ilike 'bs_war_merch_panasonic_mailer' --40% ) then 'Panasonic' when (s.create_date >= '10/2/2012' and PI.marketplace_detail in ('Panasonic Direct','Panasonic') and s.type = 'item_warranty') then 'Panasonic' --40% when (rpd.campaign ilike 'bs_war_affiliate_sanyo:callcenter' or rpd.campaign ilike 'bs_war_affiliate_sanyo') then 'Sanyo' -- 40%when (rpd.campaign like '%mme%' and ((s.seller_seal_id = 'subscrip_014740867809' and s.type = 'item_warranty') or (pi.seller_marketplace_seal_id = 'subscrip_014740867809' and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty'))) then 'Panasonic'WHEN (s.seller_seal_id = 'subscrip_sphq14032714' and s.TYPE = 'resale_item_warranty') THEN 'ShopHQ'WHEN seller_marketplace_user_id = 'service-and-sales' then 'Harmony'when (s.type = 'ebay_item_warranty' and pi.seller_marketplace_user_id = 'tigerdirect') then 'Systemax eBay'when s.seller_seal_id in ('subscrip_014760651181','subscrip_014769303388','subscrip_014705203383','subscrip_info04042014','subscrip_glob04032014') then 'Systemax MME'when s.seller_seal_id in( 'subscrip_014747779389' ,'subscrip_014746710116') then 'Vizio'when (s.seller_seal_id = 'subscrip_014716754652' and rpd.campaign like '%tigerconnect%' ) then 'Tiger Connect'when (s.create_date >= '10/1/2011' and s.type in ('item_warranty') and pi.marketplace_detail in ('Tiger Direct - Miami','Tiger Direct, Miami','Tiger Corporate Sales NC','TigerDirect.com','TIGER EXPORT','TIGER DIRECT,MIAMI B2B','TIGER DIRECT MIAMI STORE','Circuit City','SHOP.CA','INFOTEL DISTRIBUTORS','SYSTEMAX RETAIL SALES','Computer Technical Support Ultra','PRODUCTION SHIPPING CENTER','GLOBAL','TIGER DIRECT','CompUSA','GLOBAL GOV/ED','SYX Distribution Inc.','ONEREBATE.COM','Tiger Direct FL Co-Op','GLOBAL CO-OP','DARTEK CO-OP','ONREBATE.COM','WORLD WIDE REBATES','TigerDirect_US_Miscellaneous','COMPUSA PUERTO RICO WEB SALES','RTV ORDER DIVISION','COMPUSA B2B','SHOP AT HOME','Product Shipping Center','TIGERTV','HEARTLAND RETAIL STORE','THE SHOPPING CHANNEL CANADA','TIGER DIRECT CANADA','Tiger','Tiger Direct','Tiger Direct .com','tiger direct..com','Tiger Direct.com','TIGER DIREECT','Tiger Direst','tigerdiect','tigerdierct','TigerDirect','TigerDirect - CompUSA','TigerDirect (thru Amazon)','Tigerdirect .com','TigerDirect, Inc: Through Amazon','TigerDirect,com','Tigerdirect.com','tigerdirrect.com','TigerDriect.com','Tigers Direct','www.tigerdirect.com')) then 'Systemax Direct'WHEN (s.type = 'ebay_item_warranty' and est.seller_seal_id is null) THEN 'ST Website'WHEN (s.type = 'ebay_item_warranty' and est.seller_seal_id is not null ) THEN 'ST-Seller'WHEN s.seller_seal_id = 'subscrip_014787757873' THEN 'eBay US Website'WHEN (s.seller_seal_id = 'subscrip_014790857874' and s.type = 'resale_item_warranty') then 'eBay UK Resale'when (s.seller_seal_id = 'subscrip_014753607089' ) then 'Toshiba'WHEN seller_marketplace_user_id = 'service-and-sales' then 'Harmony'WHEN s.seller_seal_id = 'subscrip_014793207843' and rpd.source = 'STD_AFFILIATE' and s.create_date >= '09/01/2014' THEN 'woot'WHEN s.seller_seal_id ='subscrip_014793207843' and s.create_date < '09/01/2014' THEN 'woot'WHEN rpd.campaign like '%bs_war_merch_woot%' and s.create_date < '09/01/2014' THEN 'woot'--- WHEN s.seller_seal_id ='subscrip_014793207843' THEN 'woot'--- WHEN rpd.campaign like '%bs_war_merch_woot%' THEN 'woot'WHEN ((s.type = 'resale_item_warranty' and s.seller_seal_id = 'subscrip_014766559047') or rpd.campaign in ('bs_war_merch_email_buy_1', 'bs_war_merch_email_buy_2','bs_war_merch_buy')) THEN 'Rakuten'WHEN (s.type = 'item_warranty' and s.seller_seal_id = 'subscrip_014766559047' and rpd.campaign not in ('bs_war_merch_email_buy_2' ,'bs_war_merch_email_buy_2', 'bs_war_merch_buy','unknown')) THEN 'NO_INVOICE'WHEN rpd.campaign like '%bs_war_merch_geeks%' THEN 'geeks' when rpd.campaign like '%bs_war_merch_email_cf%' then 'Crutchfield'WHEN rpd.campaign like '%postquote%' THEN 'NO_INVOICE'WHEN rpd.campaign like '%_nl_%' THEN 'NO_INVOICE'WHEN rpd.campaign like '%adword%' THEN 'NO_INVOICE'WHEN rpd.campaign like '%hobbit%' THEN 'NO_INVOICE'WHEN rpd.campaign like '%extole%' THEN 'NO_INVOICE'WHEN s.seller_seal_id = 'subscrip_redp13031916' THEN 'RED POCKET'WHEN s.seller_seal_id = 'subscrip_014745527449' THEN 'abt'WHEN s.seller_seal_id ='subscrip_014705530847' THEN 'beachaudio'WHEN s.seller_seal_id ='subscrip_014766559047' THEN 'Rakuten'WHEN s.seller_seal_id ='subscrip_014728882442' THEN 'compsource'WHEN s.seller_seal_id ='subscrip_014754289154' THEN 'compubizusa'WHEN s.seller_seal_id ='subscrip_014760946645' THEN 'datavision'WHEN s.seller_seal_id ='subscrip_014769662843' THEN 'ecost'WHEN s.seller_seal_id ='subscrip_014747674752' THEN 'entercomputers'WHEN s.seller_seal_id ='subscrip_014707523045' THEN 'geeks'WHEN s.seller_seal_id ='subscrip_014717813440' THEN 'superwarehouse'WHEN s.seller_seal_id ='subscrip_014797213444' THEN 'superwarehousegov'WHEN s.seller_seal_id ='subscrip_014705471249' THEN 'thenerds'WHEN s.seller_seal_id ='subscrip_014740715544' THEN 'vanns'WHEN s.seller_seal_id ='subscrip_014792590458' THEN 'iyogi'WHEN s.seller_seal_id ='subscrip_014795026244' THEN 'pcrush'WHEN s.seller_seal_id ='subscrip_014725646849' THEN 'sonicelectronix'WHEN s.seller_seal_id ='subscrip_014733628554' THEN 'SQUARETRADE WHOLESALE' WHEN s.seller_seal_id ='subscrip_014703576007' THEN 'SquareTrade EDUSale'WHEN s.seller_seal_id ='subscrip_014746530554' THEN 'tigergps'WHEN s.seller_seal_id ='subscrip_014777774668' THEN 'ubid'WHEN s.seller_seal_id ='subscrip_014712179350' THEN 'redtag'WHEN (s.seller_seal_id ='subscrip_014723971059' and s.TYPE = 'resale_item_warranty') THEN 'Crutchfield' when (s.seller_seal_id = 'subscrip_waco13031315' and s.type = 'resale_item_warranty') then 'Wireless Advocates'WHEN (s.seller_seal_id ='subscrip_014796443040' and s.TYPE = 'resale_item_warranty') THEN 'amazon' WHEN (s.seller_seal_id in ('subscrip_014770964554') and s.type = 'resale_item_warranty') THEN 'Amazon UK'WHEN (s.seller_seal_id ='subscrip_014713272753' and s.type = 'resale_item_warranty') then 'Amazon Warehouse'WHEN (s.seller_seal_id in ('subscrip_014737566562','subscrip_014757566566') and s.type = 'resale_item_warranty') then 'Amazon US Kindle'WHEN (s.seller_seal_id = 'subscrip_amzn13050310' and s.type = 'resale_item_warranty') then 'Amazon CA Kindle'WHEN (s.seller_seal_id ='subscrip_014719742854' and s.TYPE = 'resale_item_warranty') THEN 'qvc'WHEN (s.seller_seal_id ='subscrip_014772757765' and s.TYPE = 'resale_item_warranty') THEN 'letstalk'WHEN (s.seller_seal_id = 'subscrip_014704437161' and s.TYPE = 'resale_item_warranty') THEN 'costco'when (s.seller_seal_id = 'subscrip_014702921174' and s.type = 'resale_item_warranty') then 'CostcoCA'when (s.seller_seal_id = 'subscrip_014739739780' and s.type = 'resale_item_warranty') then 'CostcoPR'WHEN (s.seller_seal_id = 'subscrip_014783345463' and s.TYPE = 'resale_item_warranty') THEN 'att_mobility'WHEN (s.seller_seal_id in ('subscrip_014726328775','subscrip_sysx13040212', 'subscrip_susb13092003') and s.TYPE = 'resale_item_warranty') THEN 'SystemaxUS'WHEN (s.seller_seal_id = 'subscrip_014793481878' and s.TYPE = 'resale_item_warranty') THEN 'SystemaxPR'WHEN (s.seller_seal_id in ('subscrip_scab13092003-ST4B-CA', 'subscrip_014736328772') and s.TYPE = 'resale_item_warranty') THEN 'SystemaxCA'WHEN (s.seller_seal_id = 'subscrip_014725314670' and s.TYPE = 'resale_item_warranty') THEN 'Purchasing_Power'WHEN (s.seller_seal_id = 'subscrip_014765342077' and s.type = 'resale_item_warranty') then 'E-buyer'WHEN (s.seller_seal_id in ('subscrip_samdc13100313', 'subscrip_samc13100211','subscrip_014718732979', 'subscrip_samp13040510','subscrip_samc13100211','subscrip_samdc1310031') and s.type = 'resale_item_warranty') then 'SamsClub'WHEN (s.seller_seal_id = 'subscrip_014717309264' and s.type = 'resale_item_warranty') then 'Misco' WHEN (s.seller_seal_id = 'subscrip_014718981469') THEN 'absolutee_endeavors'WHEN (s.seller_seal_id = 'subscrip_014793761468') THEN 'advanced_electronics'WHEN (s.seller_seal_id = 'subscrip_014729342655') THEN 'deming_wireless_company'WHEN (s.seller_seal_id = 'subscrip_014795199566') THEN 'desert_mountain'WHEN (s.seller_seal_id = 'subscrip_014700594462') THEN 'downtown_cyberspace'WHEN (s.seller_seal_id = 'subscrip_014775961466') THEN 'north_pacific_cellular'WHEN s.seller_seal_id = 'subscrip_014735364269' THEN 'mojowireless'WHEN s.seller_seal_id = 'subscrip_014795364261' THEN 'yourtechwireless' WHEN s.seller_seal_id = 'subscrip_014799332006' THEN 'Frontier_Communications'WHEN s.seller_seal_id = 'subscrip_014700364263' THEN 'eldridge'WHEN s.seller_seal_id = 'subscrip_014782763565' THEN 'Mesa Communications'WHEN s.seller_seal_id = 'subscrip_014703673560' THEN 'the_wireless_store'WHEN s.seller_seal_id = 'subscrip_014794166774' THEN 'SouthWest Mobile'WHEN s.seller_seal_id = 'subscrip_014778977563' THEN 'cronk_pres'WHEN s.seller_seal_id = 'subscrip_014733548562' THEN 'custom_audio_concepts'WHEN s.seller_seal_id = 'subscrip_014757101675' THEN 'custom_audio_concepts_ohio'WHEN s.seller_seal_id = 'subscrip_014742101673' THEN 'electronic_purchasing_agent'WHEN s.seller_seal_id = 'subscrip_014709832671' THEN 'break-away_auto'WHEN s.seller_seal_id = 'subscrip_014798114672' THEN 'vt_copy_and_shipping'WHEN s.seller_seal_id = 'subscrip_014749945676' THEN 'geneva_broadband'WHEN s.seller_seal_id = 'subscrip_014758042564' THEN 'electricland'WHEN (s.seller_seal_id = 'subscrip_014784154071' and rpd.campaign like '%store%') THEN 'wireless_store'WHEN (s.seller_seal_id = 'subscrip_059614394029' and rpd.campaign like '%iOptimize%') THEN 'iOptimize'when s.seller_seal_id = 'subscrip_014709119772' THEN 'Commtech'when s.seller_seal_id = 'subscrip_014779153089' THEN 'MainBoard, LLC'when s.seller_seal_id = 'subscrip_014774685790' THEN '1SaleADay'when (s.seller_seal_id = 'subscrip_014716754652' and rpd.campaign like '%tigerconnect%') then 'Tiger Connect'WHEN s.seller_seal_id = 'subscrip_014708569776' THEN 'Global Communications Group' WHEN s.seller_seal_id = 'subscrip_ccell13022112' THEN 'Consumer Cellular'WHEN (rpd.campaign like '%bs_war_vc_023%' or rpd.campaign like '%bs_war_vc_060%' or rpd.campaign like '%bs_war_vc_061%' or rpd.campaign like '%bs_war_vc_063%' or rpd.campaign like '%bs_war_vc_066%') THEN 'WizzardMedia'WHEN rpd.campaign like '%bs_war_vc_024%' THEN 'gilsmethod'WHEN rpd.campaign like '%bs_war_vc_025%' THEN 'padgadget'WHEN rpd.campaign like '%bs_war_vc_026%' THEN 'dealcatcher'WHEN rpd.campaign like '%bs_war_vc_040%' THEN 'dealnews'WHEN rpd.campaign like '%bs_war_vc_027%' THEN 'macintoshos'WHEN rpd.campaign like '%bs_war_vc_028%' THEN 'iphone4case'WHEN rpd.campaign like '%bs_war_vc_030%' THEN 'ThatSnazzyiPhoneGuy'WHEN rpd.campaign like '%bs_war_vc_031%' THEN 'case-mate'WHEN rpd.campaign like '%bs_war_vc_032%' THEN 'digitallifestyle.tv'WHEN rpd.campaign like '%bs_war_vc_033%' THEN 'topiphoneresource'WHEN rpd.campaign like '%bs_war_vc_037%' THEN 'dealsplus'WHEN rpd.campaign like '%bs_war_vc_042%' THEN 'bensbargains'WHEN rpd.campaign like '%bs_war_vc_045%' THEN 'iphonefaq'WHEN rpd.campaign like '%bs_war_vc_052%' THEN 'wirelessemporium'WHEN rpd.campaign like '%bs_war_vc_055%' THEN 'shipitapo'WHEN rpd.campaign like '%bs_war_vc_064%' THEN 'mycellz4less'WHEN rpd.campaign like '%bs_war_vc_072%' THEN 'moneycrashers'WHEN rpd.campaign like '%bs_war_vc_020%' THEN 'ipadaccessoryonlinestore' WHEN rpd.campaign like '%bs_war_vc_021%' THEN 'foxxr'WHEN rpd.campaign like '%bs_war_vc_022%' THEN 'ipadjailbreak'WHEN rpd.campaign like '%bs_war_vc_054%' THEN 'slickdeals' WHEN rpd.campaign like '%bs_war_store:vauxe%' THEN 'Vaux' WHEN rpd.campaign like '%zagg%' THEN 'zagg'WHEN rpd.campaign like '%casemate%' THEN 'case-mate'WHEN rpd.campaign like '%citi%' THEN 'citi'WHEN rpd.campaign like '%disc%' THEN 'discover'WHEN rpd.campaign like '%usbank%' THEN 'usbank'WHEN rpd.campaign like '%redtag%' THEN 'redtag'WHEN rpd.campaign like '%ubid%' THEN 'ubid'WHEN rpd.campaign like '%iresq%' THEN 'iresQ'WHEN rpd.campaign like '%fixya%' THEN 'fixya' WHEN rpd.promo_code = 'FRONTIERPAD' THEN 'FRONTIERPAD'WHEN (rpd.campaign like '%instp%' or (rpd.campaign like '%mme%' and s.seller_seal_id = 'subscrip_014740645346')) THEN 'instrumentpro'WHEN rpd.campaign like '%emporium%' THEN 'wirelessemporium'WHEN rpd.campaign like '%capone%' Then 'capitalone'WHEN rpd.campaign like '%kodak%' THEN 'kodak' WHEN (rpd.campaign ilike '%iphone_app%' AND rpd.promo_code ilike 'STAPLES%') THEN 'Staples App%' when s.seller_seal_id in ('subscrip_014793677486') then 'Amazon DE KindleTouch3G'when s.seller_seal_id in ('subscrip_014722777485') then 'Amazon DE KindleTouchWiFi'when s.seller_seal_id in ('subscrip_014736017485') then 'Amazon ES KindleTouch3G'when s.seller_seal_id in ('subscrip_014708117489') then 'Amazon ES KindleTouchWiFi'when s.seller_seal_id in ('subscrip_014750877485') then 'Amazon FR KindleTouch3G'when s.seller_seal_id in ('subscrip_014706877484') then 'Amazon FR KindleTouchWiFi'when s.seller_seal_id in ('subscrip_014728417484') then 'Amazon IT KindleTouch3G'when s.seller_seal_id in ('subscrip_014788528489') then 'Amazon IT KindleTouchWiFi'when s.seller_seal_id in ('subscrip_014788267487') then 'Amazon UK KindleTouch3G'when s.seller_seal_id in ('subscrip_014799467487') then 'Amazon UK KindleTouchWiFi'---FIN-276WHEN s.seller_seal_id = 'subscrip_014700666565' THEN 'Amazon FR Kindle'WHEN s.seller_seal_id = 'subscrip_014730666566' THEN 'Amazon DE Kindle' WHEN s.seller_seal_id = 'subscrip_014745164071' THEN 'Amazon IT Kindle' WHEN s.seller_seal_id = 'subscrip_014763964073' THEN 'Amazon ES Kindle' when s.seller_seal_id in ('subscrip_crut05192014') and s.type = 'resale_item_warranty' then 'Crutchfield CA' when s.seller_seal_id in ('subscrip_sqtr14050607') and s.type = 'resale_item_warranty' then 'NO_INVOICE'ELSE 'NO_INVOICE' END AS Invoice_ID,--ESP_order_id(CASE WHEN s.string_10 = 'WARRANTY_EXTENSION' THEN (LTRIM(pi.id, 'PurchasedItem_') || '-E') ELSE LTRIM(pi.id, 'PurchasedItem_') END) AS ESP_Order_ID, s.string_16 as reseller_product_id,ptli.id as ptli_id,st.id as category_id,rpd.installments as installments,rpd.price as warranty_value,s.num_1 as warranty_term,s.date_2 as warranty_start_date,s.date_4 as warranty_end_date,s.create_date as warranty_create_date,pt.create_date as transaction_create_date,case when (s.seller_seal_id = 'subscrip_elsa14071410') then (case when rpd.installments = 1 then ptli.AMOUNTelse (sum(ptli.amount) OVER (PARTITION by ptli.recuring_pmt_def_id order by pt.create_date asc)) end) else ptli.amount end as amount, -- FIN-299 commission amount adjustment for Eliscia November CLose Activities--ptli.amount as amount,cast(pi.purchase_date as date) AS ITEM_PURCHASE_DATE,cast(pi.purchase_price as numeric) AS ITEM_PURCHASE_PRICE,null::text as month_Recogn,s.string_4 as SKU,whole.sku_class as sku_class,-- unit_price(CASE when RPD.SOURCE = 'UK_DIRECT' THEN (PTLI.AMOUNT-(PTLI.AMOUNT*(PTLI.inclusive_tax_rate / (100+PTLI.inclusive_tax_rate)))) --DIRECT UK WHEN s.string_16 in ( 'RD-KINDLED101C2US', 'RD-KINDLED104C2US', 'RD-KINDLE3', 'RD-KINDLE1SO', 'RD-KINDLED101C3US', 'RD-KINDLED104C3US','RD-KINDLED200C2US','RD-KINDLED201C2US','RD-KINDLED205A2US','RD-KINDLED205B2US','RD-KINDLED206C2US' ) THEN PTLI.unit_price --Amazon Kindle US WHEN s.string_16 in ( 'RD-KINDLED100C2UK', 'RD-KINDLED101C2UK', 'RD-KINDLED104C2UK', 'RD-KINDLED1UK', 'RD-KINDLED105BC2UK', 'RD-KINDLED105AC2UK', 'RD-KINDLED100C3UK', 'RD-KINDLED101C3UK', 'RD-KINDLED104C3UK', 'RD-KINDLED105BC3UK', 'RD-KINDLED105AC3UK', 'UK-KN0124N3A', 'UK-KN0174N3A', 'RD-KINDLED2UK', 'RD-KINDLED3UK', 'RD-KINDLED103A3UK' ) THEN (PTLI.AMOUNT*(1-0.056603774)) --UK WHEN s.string_16 in ( 'RD-KINDLED100C2DE', 'RD-KINDLED101C2DE', 'RD-KINDLED104C2DE', 'RD-KINDLED1DE', 'RD-KINDLED105BC2DE', 'RD-KINDLED105AC2DE', 'RD-KINDLED100C3DE', 'RD-KINDLED101C3DE', 'RD-KINDLED104C3DE', 'RD-KINDLED105BC3DE', 'RD-KINDLED105AC3DE', 'DE-KN0174N3A', 'DE-KN0199N3A', 'RD-KINDLED2DE', 'RD-KINDLED3DE', 'RD-KINDLED103A3DE' ) THEN (PTLI.AMOUNT*(1-0.159663866)) --DE WHEN s.string_16 in ( 'RD-KINDLED100C2FR', 'RD-KINDLED101C2FR', 'RD-KINDLED104C2FR', 'RD-KINDLED1FR', 'RD-KINDLED105BC2FR', 'RD-KINDLED105AC2FR', 'RD-KINDLED100C3FR', 'RD-KINDLED101C3FR', 'RD-KINDLED104C3FR', 'RD-KINDLED105BC3FR', 'RD-KINDLED105AC3FR', 'RD-KINDLED2FR', 'RD-KINDLED3FR', 'RD-KINDLED103A3FR' ) THEN (PTLI.AMOUNT*(1-0.082568807)) --FR WHEN s.string_16 in ( 'RD-KINDLED100C2IT', 'RD-KINDLED101C2IT', 'RD-KINDLED104C2IT', 'RD-KINDLED1IT', 'RD-KINDLED105BC2IT', 'RD-KINDLED105AC2IT', 'RD-KINDLED100C3IT', 'RD-KINDLED101C3IT', 'RD-KINDLED104C3IT', 'RD-KINDLED105BC3IT', 'RD-KINDLED105AC3IT', 'RD-KINDLED2IT', 'RD-KINDLED3IT', 'RD-KINDLED103A3IT' ) THEN (PTLI.AMOUNT*(1-0.175257732)) --IT WHEN s.string_16 in ( 'RD-KINDLED100C2ES', 'RD-KINDLED101C2ES', 'RD-KINDLED104C2ES', 'RD-KINDLED1ES', 'RD-KINDLED105BC2ES', 'RD-KINDLED105AC2ES', 'RD-KINDLED100C3ES', 'RD-KINDLED101C3ES', 'RD-KINDLED104C3ES', 'RD-KINDLED105BC3ES', 'RD-KINDLED105AC3ES', 'RD-KINDLED2ES', 'RD-KINDLED3ES', 'RD-KINDLED103A3ES' ) THEN (PTLI.AMOUNT*(1-0.056603774)) --ESWHEN (rpd.installments > 1 and rpd.price 0) THEN (rpd.price) / rpd.installments --rpd.installmentsWHEN s.SELLER_SEAL_ID IN ('subscrip_014790857874') THEN (PTLI.AMOUNT-(PTLI.AMOUNT*(0.056603774))) --eBay UKwhen (s.seller_seal_id IN ('subscrip_014790519901', 'subscrip_tsdi13050914') and s.type = 'resale_item_warranty') then (PTLI.AMOUNT-(PTLI.AMOUNT*(0.166666667))) --Tescowhen (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty' AND s.STRING_4 IN ('EG0070AN1A','EG0499AN1A','EG0100AN1D','EG0999AN1D')) then PTLI.AMOUNT*(1-0.056603774) --ASDAwhen (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty') then PTLI.AMOUNT*(1-0.166666667) --ASDAWHEN s.SELLER_SEAL_ID IN ('subscrip_014765342077') THEN (PTLI.AMOUNT-(PTLI.AMOUNT*(0.166666667))) --E-BUYERWHEN s.SELLER_SEAL_ID IN ('subscrip_014717309264') THEN (PTLI.AMOUNT-(PTLI.AMOUNT*(0.056603774))) --MISCOWHEN s.seller_seal_id in ('subscrip_014770964554', 'subscrip_014791575968','subscrip_014732575965', 'subscrip_014720666569','subscrip_014788267487','subscrip_014799467487') THEN (PTLI.AMOUNT*(1-0.056603774)) --UKWHEN s.seller_seal_id in ('subscrip_014712273164','subscrip_014751273163','subscrip_014730666566','subscrip_014793677486','subscrip_014722777485') THEN (PTLI.AMOUNT*(1-0.159663866)) --DEWHEN s.seller_seal_id in ('subscrip_014700666565','subscrip_014750877485','subscrip_014706877484') THEN (PTLI.AMOUNT*(1-0.082568807)) --FRWHEN s.seller_seal_id in ('subscrip_014745164071','subscrip_014728417484','subscrip_014788528489') THEN (PTLI.AMOUNT*(1-0.175257732)) --ITWHEN s.seller_seal_id in ('subscrip_014763964073','subscrip_014736017485','subscrip_014708117489') THEN (PTLI.AMOUNT*(1-0.056603774)) --ESwhen s.seller_seal_id in ('subscrip_014736328772') then wwp.priceWHEN pt.currency_code IN ('GBP','EUR') THEN (PTLI.AMOUNT-(PTLI.AMOUNT*(PTLI.inclusive_tax_rate/ (100+PTLI.inclusive_tax_rate)))) ELSE PTLI.unit_price END) as Unit_price, rpd.campaign as campaign,s.type as type,pi.condition as item_condition,---Reseller_costCASE WHEN ((s.seller_seal_id IN ('subscrip_014796443040','subscrip_014713272753','subscrip_014770964554','subscrip_014751273163', 'subscrip_014712273164','subscrip_014791575968','subscrip_014732575965','subscrip_014737566562','subscrip_014757566566','subscrip_014730666566','subscrip_014700666565', 'subscrip_014745164071','subscrip_014763964073','subscrip_014720666569','subscrip_014787757873','subscrip_014790857874','subscrip_014793677486','subscrip_014722777485', 'subscrip_014736017485','subscrip_014708117489','subscrip_014750877485','subscrip_014706877484','subscrip_014728417484','subscrip_014788528489','subscrip_014788267487', 'subscrip_014799467487','subscrip_014703576007') or s.string_16 in ( 'RD-KINDLED101C2US', 'RD-KINDLED104C2US', 'RD-KINDLE3', 'RD-KINDLE1SO', 'RD-KINDLED101C3US', 'RD-KINDLED104C3US', 'RD-KINDLED100C2UK', 'RD-KINDLED101C2UK', 'RD-KINDLED104C2UK', 'RD-KINDLED1UK', 'RD-KINDLED105BC2UK', 'RD-KINDLED105AC2UK', 'RD-KINDLED100C3UK', 'RD-KINDLED101C3UK', 'RD-KINDLED104C3UK', 'RD-KINDLED105BC3UK', 'RD-KINDLED105AC3UK', 'RD-KINDLED103A3UK', 'UK-KN0124N3A', 'UK-KN0174N3A', 'RD-KINDLED2UK', 'RD-KINDLED3UK', 'RD-KINDLED100C2DE', 'RD-KINDLED101C2DE', 'RD-KINDLED104C2DE', 'RD-KINDLED1DE', 'RD-KINDLED105BC2DE', 'RD-KINDLED105AC2DE', 'RD-KINDLED100C3DE', 'RD-KINDLED101C3DE', 'RD-KINDLED104C3DE', 'RD-KINDLED105BC3DE', 'RD-KINDLED105AC3DE', 'RD-KINDLED103A3DE', 'DE-KN0174N3A', 'DE-KN0199N3A', 'RD-KINDLED2DE', 'RD-KINDLED3DE', 'RD-KINDLED100C2FR', 'RD-KINDLED101C2FR', 'RD-KINDLED104C2FR', 'RD-KINDLED1FR', 'RD-KINDLED105BC2FR', 'RD-KINDLED105AC2FR', 'RD-KINDLED100C3FR', 'RD-KINDLED101C3FR', 'RD-KINDLED104C3FR', 'RD-KINDLED105BC3FR', 'RD-KINDLED105AC3FR', 'RD-KINDLED103A3FR', 'RD-KINDLED2FR', 'RD-KINDLED3FR', 'RD-KINDLED100C2ES', 'RD-KINDLED101C2ES', 'RD-KINDLED104C2ES', 'RD-KINDLED1ES', 'RD-KINDLED105BC2ES', 'RD-KINDLED105AC2ES', 'RD-KINDLED100C3ES', 'RD-KINDLED101C3ES', 'RD-KINDLED104C3ES', 'RD-KINDLED105BC3ES', 'RD-KINDLED105AC3ES', 'RD-KINDLED103A3ES', 'RD-KINDLED2ES', 'RD-KINDLED3ES', 'RD-KINDLED100C2IT', 'RD-KINDLED101C2IT', 'RD-KINDLED104C2IT', 'RD-KINDLED1IT', 'RD-KINDLED105BC2IT', 'RD-KINDLED105AC2IT', 'RD-KINDLED100C3IT', 'RD-KINDLED101C3IT', 'RD-KINDLED104C3IT', 'RD-KINDLED105BC3IT', 'RD-KINDLED105AC3IT', 'RD-KINDLED103A3IT', 'RD-KINDLED2IT','RD-KINDLED101C2CA','RD-KINDLED103ALC2US','RD-KINDLED103BLC2US','RD-KINDLED103C2CA','RD-KINDLED104C2CA','RD-KINDLED105AC2CA','RD-KINDLED105BC2CA','RD-KINDLED200C2CA','RD-KINDLED200C2US','RD-KINDLED201C2CA','RD-KINDLED201C2US','RD-KINDLED203C2CA','RD-KINDLED205A2CA','RD-KINDLED205A2US','RD-KINDLED205B2US','RD-KINDLED206C2US','RD-KINDLED3C3US', 'RD-KINDLED3IT'))and s.TYPE = 'resale_item_warranty') THEN 0 --QVC Q4 wholesale cost adjustment WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0099N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 13.49 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0149N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 18.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0199N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 24.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0699N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 77.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0799N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 94.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0999N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 114.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0599N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 92.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0699N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 92.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0799N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 119.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0899N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 122.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-DC0149N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 20.99 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-DC0999N3A' and s.create_date between '10/1/2012' and '1/1/2013') then 114.99---UK Cost when (s.seller_seal_id IN ('subscrip_014790519901','subscrip_tsdi13050914') and s.type = 'resale_item_warranty') then (wwp.price + rpd.price*(0.2/(1+0.2))) --Tescowhen (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty' AND s.STRING_4 IN ('EG0070AN1A','EG0499AN1A','EG0100AN1D','EG0999AN1D')) then (wwp.price + rpd.price*(0.06/(1+0.06)))when (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty') then (wwp.price + rpd.price*(0.2/(1+0.2)))when (s.seller_seal_id in ('subscrip_014717309264') and s.type = 'resale_item_warranty' and cast(s.CREATE_DATE as date) = '3/1/2012') then (wwp.price + rpd.price*(0.2/(1+0.2)))-- MISCOwhen (s.seller_seal_id in ('subscrip_014765342077') and s.type = 'resale_item_warranty') then (wwp.price + rpd.price*(0.2/(1+0.2))) --E-BuyerWHEN s.type = 'item_warranty' OR s.seller_seal_id = 'subscrip_sqtr14050607' THEN 0 --FIN-289WHEN wwp.price is not null then wwp.price else 0 end as Reseller_Cost,pt.trans_status as trans_status,s.status as warranty_status,--commission percent---Commission---CommissionCASE when pt.trans_status = 'NO CHARGE' then 0 WHEN (s.CREATE_DATE < '1/1/2014' AND rpd.installments > 1 and s.seller_seal_id in ('subscrip_014784154071','subscrip_014716754652') and rpd.campaign like '%store%') then 0.3 WHEN (s.CREATE_DATE < '1/1/2014' AND rpd.installments > 1 and s.seller_seal_id = 'subscrip_074768984025' and rpd.campaign like '%bs_war_store:vauxe%') then 0.3 WHEN (s.CREATE_DATE < '1/1/2014' AND rpd.installments > 1 and s.seller_seal_id ='subscrip_059614394029' and rpd.campaign like '%iOptimize%') then 0.3 when (rpd.campaign ilike '%iphone_app%' AND rpd.promo_code ilike 'STAPLES%') then 0.374217772 WHEN (cast(s.create_date as date) between date_trunc('month',current_date) and date_trunc('month',current_date)+'1month'::interval-'1day'::interval AND rpd.installments > 1 and rpd.campaign ilike '%iphone_app%' AND rpd.promo_code ilike 'STAPLES%') THEN 0 when (s.seller_seal_id = 'subscrip_redp13031916' and upper(whole.sku_class) = 'SMART_PHONE' and rpd.installments >1) then 0.2003 --redpocket when (s.seller_seal_id = 'subscrip_redp13031916' and upper(whole.sku_class) = 'FEATURE PHONES' and rpd.installments >1) then 0.2754 --redpocket when (s.seller_seal_id = 'subscrip_redp13031916' and upper(whole.sku_class) = 'TABLET%' and rpd.installments > 1) then 0.3004 --redpocketwhen s.seller_seal_id = 'subscrip_elsa14071410' then 0.03 ---FIN-299 November Close Activities Elicia Updates WHEN (s.CREATE_DATE < '1/1/2014' AND rpd.installments > 1 ) then 0 when seller_marketplace_user_id = 'service-and-sales' then 0.25 -- when (rpd.campaign ilike '%iphone_app%' AND rpd.promo_code ilike 'STAPLES%') then 0.374217772--Canada when(upper(rpd.promo_code) like '%FRONTIERPAD%' and s.num_1 = 2 ) then .14 when(upper(rpd.promo_code) like '%FRONTIERPAD%' and s.num_1 = 3 ) then .08--Ubreakifix when s.seller_seal_id in ('subscrip_034745127029', 'subscrip_014726127026', 'subscrip_024745127020' ,'subscrip_044745127028', 'subscrip_024726127025', 'subscrip_054745127027') then .125--QuidsiWHEN (s.seller_seal_id = 'subscrip_qdsi14022103' and s.type = 'resale_item_warranty') THEN .5 --Quidsi--FIN-161 Required for september closewhen s.seller_seal_id in ('subscrip_acfx04112014', 'subscrip_acfx04122014') then 0.2 --AccentFX--Amazon when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED201C2US') then 0.7227 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED205A2US') then 0.8142 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED205B2US') then 0.8555 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED200C2US') then 0.6870 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED206C2US') then 0.8295 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2US') then 0.738476086561193 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2US') then 0.547364545818327 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLE3') then 0.614803289619916 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLE1SO') then 0.519807923169268 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3US') then 0.671927988921372 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3US') then 0.48343314438146 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2UK') then 0.502232608727865 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2UK') then 0.573506048372431 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2UK') then 0.34722006730332 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1UK') then 0.311959981809914 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2UK') then 0.465567795118047 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2UK') then 0.57067369343622 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3UK') then 0.382343623391345 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3UK') then 0.468582983272978 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3UK') then 0.151246810615534 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3UK') then 0.538481179521375 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3UK') then 0.431229714054764 when (s.type = 'resale_item_warranty' and s.string_16 = 'UK-KN0124N3A') then 0.299919967987195 when (s.type = 'resale_item_warranty' and s.string_16 = 'UK-KN0174N3A') then 0.400133377792598 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2UK') then 0.365455151717239 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3UK') then 0.443654145365637 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2DE') then 0.48433385239737 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2DE') then 0.556042133453615 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2DE') then 0.333444292276911 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1DE') then 0.3121 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2DE') then 0.478908757328073 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2DE') then 0.599411328052359 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3DE') then 0.388209125285587 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3DE') then 0.477078345626806 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3DE') then 0.114682589288906 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3DE') then 0.536277624740375 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3DE') then 0.38854985494535 when (s.type = 'resale_item_warranty' and s.string_16 = 'DE-KN0174N3A') then 0.319995388838588 when (s.type = 'resale_item_warranty' and s.string_16 = 'DE-KN0199N3A') then 0.420105026256564 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2DE') then 0.370105744498428 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3DE') then 0.44128825765153 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2FR') then 0.5614 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2FR') then 0.6331 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2FR') then 0.4105 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1FR') then 0.3894 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2FR') then 0.556 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2FR') then 0.6464 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3FR') then 0.465304183492356 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3FR') then 0.554173403833575 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3FR') then 0.191777647495675 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3FR') then 0.613372682947144 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3FR') then 0.465644913152119 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2FR') then 0.4473 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3FR') then 0.5184 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2ES') then 0.641997891762042 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2ES') then 0.70874478444234 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2ES') then 0.458277611772075 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1ES') then 0.4514 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2ES') then 0.630041776968863 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2ES') then 0.712525180350341 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3ES') then 0.561874804100902 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3ES') then 0.645508704132406 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3ES') then 0.279683301885282 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3ES') then 0.689237836337079 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3ES') then 0.558268068791572 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2ES') then 0.483161053684561 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3ES') then 0.558790842409424 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C2IT') then 0.523704546005384 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C2IT') then 0.590451438685682 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C2IT') then 0.361085554168658 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED1IT') then 0.3331 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC2IT') then 0.511748431212205 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC2IT') then 0.594231834593682 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED100C3IT') then 0.443581458344244 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED101C3IT') then 0.527215358375748 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED104C3IT') then 0.161433246959021 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105BC3IT') then 0.570930063190255 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED105AC3IT') then 0.439974723034914 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED2IT') then 0.3649 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED3IT') then 0.4406 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3UK') then 0.3647 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3DE') then 0.3914 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3FR') then 0.4685 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3ES') then 0.5429 when (s.type = 'resale_item_warranty' and s.string_16 = 'RD-KINDLED103A3IT') then 0.4813when ((s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.type = 'item_warranty') or (pi.seller_marketplace_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty') and rpd.campaign like 'bs_war_affiliate_widget:bhphotovideo_florida%') then 0.5 --'B&H Affiliate'when (s.create_date::date >= '10/15/2012' and ((s.type = 'item_warranty' and (s.seller_seal_id in ('subscrip_014736374199','subscrip_014726394206') or pi.marketplace_detail in ('B&H:%','B&H Photo%','B&H Photo Video','B&H Photo Video AMT','B&HPhoto%','B&HVideo/Audio%','B & H:%','B & H Photo%', 'BandH:%','BandH.com%','www.BandH.com%','BandH Photo%','B and H:%','B and H%','B and H Photo%','b and H photo and video%', 'B_and_H:%', 'B_and_H_PHOTO%','BH:%','BH Photo%','BH Photo Video%', 'bhphoto.com%', 'www.bhphoto.com%', 'bhphotovideo.com%','www.bhphotovideo.com%','B-H:%','b-h photo%','B-H Photo, NY, NY%'))) or (pi.seller_marketplace_seal_id in ('subscrip_014736374199','subscrip_014726394206') and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty'))) then 0.5 --'B&H Squaretrade Order' when rpd.campaign like 'bs_war_merch_orango_ppm%' then 0 when ( rpd.campaign ilike 'bs_war_merch_panasonic_LP' --40% or rpd.campaign ilike 'bs_war_affiliate_widget:interaxisTY1012' --40% or rpd.campaign ilike 'bs_war_merch_interaxis_account' --40% or rpd.campaign ilike 'bs_war_merch_panasonic_phone' --40% or rpd.campaign ilike 'bs_war_merch_panasonic_mailer' --40% ) then 0.4 when (s.create_date >= '10/2/2012' and PI.marketplace_detail in ('Panasonic Direct','Panasonic') and s.type = 'item_warranty') then 0.4 --40% WHEN s.seller_seal_id = 'subscrip_014706967802' AND rpd.campaign = 'bs_war_affiliate_sanyo:callcenter' THEN 0.65 --sanyo + kipany WHEN s.seller_seal_id = 'subscrip_014706967802' THEN 0.4 --sanyo non-kipany when (rpd.campaign like '%mme%' and ((s.seller_seal_id = 'subscrip_014740867809' and s.type = 'item_warranty') or (pi.seller_marketplace_seal_id = 'subscrip_014740867809' and s.seller_Seal_id = 'subscrip_sqtr14050607' AND s.type = 'resale_item_warranty'))) then 0.4when (s.type = 'ebay_item_warranty' and pi.seller_marketplace_user_id = 'tigerdirect') then 0.4WHEN (s.type = 'ebay_item_warranty' and est.seller_seal_id is null) THEN 0.3WHEN (s.type = 'ebay_item_warranty' and est.seller_seal_id is not null ) THEN 0when s.seller_seal_id in ('subscrip_014760651181','subscrip_014769303388','subscrip_014705203383','subscrip_info04042014','subscrip_glob04032014') then 0.5when (s.seller_seal_id = 'subscrip_014747779389' and s.type 'resale_item_warranty')then 0.3 when (s.create_date >= '10/1/2011' and s.type in ('item_warranty') and pi.marketplace_detail in ('Tiger Direct - Miami','Tiger Direct, Miami','Tiger Corporate Sales NC','TigerDirect.com','TIGER EXPORT','TIGER DIRECT,MIAMI B2B','TIGER DIRECT MIAMI STORE','Circuit City','SHOP.CA','INFOTEL DISTRIBUTORS','SYSTEMAX RETAIL SALES','Computer Technical Support Ultra','PRODUCTION SHIPPING CENTER','GLOBAL','TIGER DIRECT','CompUSA','GLOBAL GOV/ED','SYX Distribution Inc.','ONEREBATE.COM','Tiger Direct FL Co-Op','GLOBAL CO-OP','DARTEK CO-OP','ONREBATE.COM','WORLD WIDE REBATES','TigerDirect_US_Miscellaneous','COMPUSA PUERTO RICO WEB SALES','RTV ORDER DIVISION','COMPUSA B2B','SHOP AT HOME','Product Shipping Center','TIGERTV','HEARTLAND RETAIL STORE','THE SHOPPING CHANNEL CANADA','TIGER DIRECT CANADA','Tiger','Tiger Direct','Tiger Direct .com','tiger direct..com','Tiger Direct.com','TIGER DIREECT','Tiger Direst','tigerdiect','tigerdierct','TigerDirect','TigerDirect - CompUSA','TigerDirect (thru Amazon)','Tigerdirect .com','TigerDirect, Inc: Through Amazon','TigerDirect,com','Tigerdirect.com','tigerdirrect.com','TigerDriect.com','Tigers Direct','www.tigerdirect.com')) then 0.5WHEN s.seller_seal_id = 'subscrip_014787757873' THEN 0.3WHEN s.seller_seal_id = 'subscrip_014790857874' THEN 0.3 WHEN (s.seller_seal_id = 'subscrip_014796443040' and s.type = 'resale_item_warranty' and (s.string_16 like '%IP%' or s.string_16 like '%PH%'or s.string_16 like '%SP%')) THEN 0.25WHEN (s.seller_seal_id = 'subscrip_014796443040' and s.type = 'resale_item_warranty' and cast(s.create_date as timestamp) between '6/27/2014 23:00:00' and '7/25/2014 23:59:59'and s.string_16 like 'RD-CN%' ) THEN 0.3WHEN (s.seller_seal_id = 'subscrip_014796443040' and s.type = 'resale_item_warranty' and cast(s.create_date as date) >= '5/1/2013' ) THEN 0.51WHEN (s.seller_seal_id = 'subscrip_014796443040' and s.type = 'resale_item_warranty' ) THEN 0.5WHEN (s.seller_seal_id = 'subscrip_014713272753' and s.type = 'resale_item_warranty') THEN 0.08 --Amazon Warehousewhen s.seller_seal_id in ('subscrip_014793677486') then 0.5251when s.seller_seal_id in ('subscrip_014722777485') then 0.4405when s.seller_seal_id in ('subscrip_014736017485') then 0.5924when s.seller_seal_id in ('subscrip_014708117489') then 0.5122when s.seller_seal_id in ('subscrip_014750877485') then 0.5650when s.seller_seal_id in ('subscrip_014706877484') then 0.4876when s.seller_seal_id in ('subscrip_014728417484') then 0.5338when s.seller_seal_id in ('subscrip_014788528489') then 0.4421when s.seller_seal_id in ('subscrip_014788267487') then 0.5324when s.seller_seal_id in ('subscrip_014799467487') then 0.4386 WHEN rpd.campaign like '%bs_war_merch_buy%' THEN 0.4WHEN rpd.campaign like '%bs_war_merch_geeks%' THEN 0.25when rpd.campaign like '%bs_war_merch_email_cf%' then 0.25WHEN rpd.campaign like '%disc%' THEN 0WHEN rpd.campaign like '%citi%' THEN 0.33WHEN rpd.campaign like '%capone%' THEN 0.33 --Check thisWHEN rpd.campaign like '%bs_war_merch_woot%' THEN 0.3WHEN s.seller_seal_id in ('subscrip_014718981469','subscrip_014793761468', 'subscrip_014729342655','subscrip_014795199566','subscrip_014700594462','subscrip_014775961466','subscrip_014758042564', 'subscrip_014782763565','subscrip_014703673560', 'subscrip_014778977563','subscrip_014733548562','subscrip_014757101675','subscrip_014742101673','subscrip_014709832671','subscrip_014798114672','subscrip_014749945676','subscrip_014708569776','subscrip_014711027775','subscrip_014794166774','subscrip_014784154071', 'subscrip_059614394029','subscrip_014708569776','subscrip_014716754652','subscrip_014709119772','subscrip_014779153089') THEN 0.3WHEN s.seller_seal_id in ('subscrip_014735364269','subscrip_014795364261', 'subscrip_014700364263') THEN 0.4 WHEN rpd.campaign in ('bs_war_merch_email_buy_1', 'bs_war_merch_email_buy_2') THEN 0.25WHEN (rpd.campaign in ('bs_war_merch_email_buy_1', 'bs_war_merch_email_buy_2','bs_war_merch_buy')) then 0.25--Note I will have to change this commission percentage as there have been additional direct marketing campagins added. ----- WHEN ((rpd.campaign not like '%bs_war_quote%' and rpd.campaign not like '%bs_war_direct%' and rpd.campaign not like '%regi%' and rpd.campaign not like '%learn%'and rpd.campaign not like '%mme%' and rpd.campaign not like '%aff%' and rpd.campaign not like '%widget%' and rpd.campaign not like '%email%') and (s.type in ('item_warranty') and s.seller_seal_id in-- ('subscrip_014745527449','subscrip_014705530847','subscrip_014766559047','subscrip_014728882442','subscrip_014754289154','subscrip_014760946645','subscrip_014769662843',-- 'subscrip_014747674752','subscrip_014707523045','subscrip_014717813440','subscrip_014797213444','subscrip_014705471249','subscrip_014740715544','subscrip_014793207843','subscrip_014725646849'))) THEN 0-- WHEN ((rpd.campaign like '%bs_war_quote%' or rpd.campaign like '%bs_war_direct%' or rpd.campaign like '%regi%' or rpd.campaign like '%learn%') and (s.type in ('item_warranty') and s.seller_seal_id in-- ('subscrip_014745527449','subscrip_014705530847','subscrip_014728882442','subscrip_014754289154','subscrip_014760946645','subscrip_014769662843',-- 'subscrip_014747674752','subscrip_014707523045','subscrip_014717813440','subscrip_014797213444','subscrip_014705471249','subscrip_014740715544','subscrip_014793207843','subscrip_014725646849'))) THEN 0.25 WHEN rpd.campaign like '%postquote%' THEN 0 WHEN rpd.campaign like '%_nl_%' THEN 0 WHEN rpd.campaign like '%adword%' THEN 0 WHEN rpd.campaign like '%hobbit%' THEN 0 WHEN rpd.campaign like '%extole%' THEN 0 WHEN ((s.type in ('item_warranty') and s.seller_seal_id in ('subscrip_014745527449','subscrip_014705530847','subscrip_014728882442','subscrip_014754289154','subscrip_014760946645','subscrip_014769662843', 'subscrip_014747674752','subscrip_014707523045','subscrip_014717813440','subscrip_014797213444','subscrip_014705471249','subscrip_014740715544','subscrip_014793207843','subscrip_014725646849'))) THEN 0.25-- when s.seller_seal_id in ('subscrip_014717813440','subscrip_014797213444') then 0WHEN (rpd.campaign like '%instp%' or rpd.campaign like '%bs_war_vc_045%' or rpd.campaign like '%bs_war_vc_052%' or rpd.campaign like '%bs_war_merch_emporium%' or rpd.campaign like '%ubid%' or rpd.campaign like '%redtag%') THEN 0.25WHEN (rpd.campaign like '%casemate%' or rpd.campaign like '%fixya%' or rpd.campaign like '%bs_war_vc_021%' or rpd.campaign like '%bs_war_vc_028%' or rpd.campaign like '%bs_war_vc_030%' or rpd.campaign like '%bs_war_vc_021%' or rpd.campaign like '%bs_war_vc_028%' or rpd.campaign like '%bs_war_vc_020%' or rpd.campaign like '%bs_war_vc_022%'or rpd.campaign like '%iresq%' or rpd.campaign like '%bs_war_vc_027%' or rpd.campaign like '%redtag%' or rpd.campaign like '%bs_war_vc_055%'or rpd.campaign like '%zagg%' or rpd.campaign like '%ubid%') THEN 0.2WHEN (rpd.campaign like '%bs_war_vc_023%' or rpd.campaign like '%bs_war_vc_060%' or rpd.campaign like '%bs_war_vc_063%' or rpd.campaign like '%bs_war_vc_061%' or rpd.campaign like '%bs_war_vc_066%') THEN 0.13WHEN (rpd.campaign like '%bs_war_vc_032%' or rpd.campaign like '%bs_war_vc_033%' ) THEN 0.15WHEN (rpd.campaign like '%bs_war_vc_024%' or rpd.campaign like '%bs_war_vc_025%' or rpd.campaign like '%bs_war_vc_026%' or rpd.campaign like '%bs_war_vc_054%' or rpd.campaign like '%bs_war_vc_040%') THEN 0.1WHEN rpd.campaign like '%bs_war_vc_037%' THEN 0.08WHEN rpd.campaign like '%bs_war_vc_042%' THEN 0.06 --QVC Q4 2012 wholesale adjustmentWHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.create_date between '10/1/2012' and '1/1/2013' and rpd.price = 0) then 0 WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0099N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 13.49 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0149N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 18.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0199N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 24.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0699N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 77.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0799N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 94.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CE0999N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 114.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0599N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 92.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0699N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 92.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0799N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 119.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-CN0899N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 122.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-DC0149N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 20.99 / rpd.price) WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and s.string_16 = 'RD-DC0999N3A' and s.create_date between '10/1/2012' and '1/1/2013') then (1 - 114.99 / rpd.price)WHEN (s.seller_seal_id = 'subscrip_014719742854' and s.TYPE = 'resale_item_warranty' and rpd.price 0) THEN (1 - wwp.price/rpd.price) ---QVCwhen (s.seller_seal_id = 'subscrip_amzn13050310' and s.type = 'resale_item_warranty' and rpd.price0) then (1 - wwp.price/rpd.price) -- Amazon CA KindleWHEN (rpd.campaign like '%iyogi%' and s.TYPE = 'item_warranty') THEN (1 - 49.99 / 69.99)WHEN s.seller_seal_id = 'subscrip_014770964554' THEN 0.385WHEN s.seller_seal_id = 'subscrip_014751273163' THEN 0.32WHEN s.seller_seal_id = 'subscrip_014712273164' THEN 0.42WHEN s.seller_seal_id = 'subscrip_014791575968' THEN 0.30WHEN s.seller_seal_id = 'subscrip_014732575965' THEN 0.40WHEN s.seller_seal_id = 'subscrip_014737566562' THEN 0.5198WHEN s.seller_seal_id = 'subscrip_014757566566' THEN 0.6148WHEN s.seller_seal_id = 'subscrip_014730666566' THEN 0.3121WHEN s.seller_seal_id = 'subscrip_014700666565' THEN 0.3892WHEN s.seller_seal_id = 'subscrip_014745164071' THEN 0.3722when s.seller_seal_id = 'subscrip_014763964073' then 0.4514 ---Amazon ES KindleWHEN s.seller_seal_id = 'subscrip_014720666569' THEN 0.3122When s.seller_seal_id = 'subscrip_014793677486' THEN 0.5251When s.seller_seal_id = 'subscrip_014722777485' THEN 0.4405When s.seller_seal_id = 'subscrip_014736017485' THEN 0.5924When s.seller_seal_id = 'subscrip_014708117489' THEN 0.5122When s.seller_seal_id = 'subscrip_014750877485' THEN 0.565When s.seller_seal_id = 'subscrip_014706877484' THEN 0.4876When s.seller_seal_id = 'subscrip_014728417484' THEN 0.5338When s.seller_seal_id = 'subscrip_014788528489' THEN 0.4421When s.seller_seal_id = 'subscrip_014788267487' THEN 0.5324When s.seller_seal_id = 'subscrip_014799467487' THEN 0.4386WHEN s.seller_seal_id = 'subscrip_014774685790' THEN 0 ---this is removal of 1saleday commission WHEN s.seller_seal_id = 'subscrip_074768984025' THEN 0.30 WHEN s.seller_seal_id = 'subscrip_014704010111' AND rpd.campaign in ('bs_war_affiliate_widget_PNA_mailer','bs_war_affiliate_widget_PNA_mailer:email1','bs_war_affiliate_widget_PNA_mailer:phone') THEN 0.5 --panasonic + kipany WHEN s.seller_seal_id = 'subscrip_014704010111' THEN 0.45 --panasonic non-kipany WHEN s.seller_seal_id = 'subscrip_014746710116' AND rpd.campaign in ('bs_war_affiliate_widget_VIZ_mailer','bs_war_affiliate_widget_VIZ_mailer:email1','bs_war_affiliate_widget_VIZ_mailer:phone') THEN 0.5 --vizio + kipany WHEN s.seller_seal_id = 'subscrip_014746710116' THEN 0.45 --vizio non-kipany WHEN s.seller_seal_id = 'subscrip_014799332006' THEN 0.3WHEN rpd.campaign like '%mme%' THEN 0.25WHEN rpd.campaign like '%unknown%' THEN 0WHEN rpd.campaign is null THEN 0WHEN rpd.campaign like '%kodak%' then 0.4 when (s.seller_seal_id = 'subscrip_redp13031916' and whole.sku_class = 'SMART_PHONE' and rpd.installments = 1) then 0.2959 --redpocket when (s.seller_seal_id = 'subscrip_redp13031916' and whole.sku_class = 'FEATURE PHONES' and rpd.installments = 1) then 0.3124 --redpocket when (s.seller_seal_id = 'subscrip_redp13031916' and whole.sku_class = 'TABLET%' and rpd.installments = 1) then 0.3099 --redpocketWHEN s.seller_seal_id = 'subscrip_082731470123' AND rpd.campaign ilike 'p1c' THEN .3 --Pacific1CellularWHEN (s.seller_seal_id = 'subscrip_qdsi14022103' and s.type = 'resale_item_warranty') THEN .5 --QuidsiWHEN s.seller_seal_id in ('subscrip_014745127021','subscrip_014726127026','subscrip_024745127020','subscrip_034745127029','subscrip_044745127028','subscrip_024726127025','subscrip_054745127027') THEN .125ELSE 0 END AS Commission_Percent,(CASE WHEN (PI.MARKETPLACE_DETAIL IN ('RESELLER','MERCHANT') OR PI.VENDOR_NAME IN ('ETAILER')) THEN pi.MARKETPLACE_ORDER_ID ELSE pi.MARKETPLACE_ITEM_ID END) AS ITEM_Number, --iptCASE WHEN RPD.SOURCE = 'UK_DIRECT' THEN '6%' --DIRECT UK WHEN (rpd.locale = 'en_CA' and s.string_1 in ('AB','ALBERTA','NB','NEW BRUNSWICK','YT','YUKON')) THEN '3%' --FIN-74 WHEN (rpd.locale = 'en_CA' and s.string_1 in ('SK','SASKATCHEWAN','NS','NOVA SCOTIA')) THEN '4%' --FIN-74 WHEN (rpd.locale = 'en_CA' and s.string_1 in ('BC','BRITISH COLUMBIA')) THEN '4.4%' --FIN-74 WHEN (s.create_Date < '3/1/2012' and s.SELLER_SEAL_ID = 'subscrip_014717309264') THEN '6%' --MISCO WHEN (s.create_Date >= '3/1/2012' and s.SELLER_SEAL_ID = 'subscrip_014717309264') THEN '20%' --MISCO WHEN (s.SELLER_SEAL_ID in ('subscrip_014790519901','subscrip_tsdi13050914')) THEN '20%' --TESCO when (s.seller_seal_id in ('subscrip_tsem13070517') and s.type = 'resale_item_warranty') then '6%' --Tesco Tablets when (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty' AND s.STRING_4 IN ('EG0070AN1A','EG0499AN1A','EG0100AN1D','EG0999AN1D')) then '6%' --ASDA when (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty') then '20%' --ASDA when (s.seller_seal_id = 'subscrip_014765342077') then '20%' --E-buyer when (s.seller_seal_id = 'subscrip_elsa14071410') then '24%' --ELISA---FIN-221 WHEN s.seller_seal_id in ('subscrip_014763964073','subscrip_014736017485','subscrip_014708117489') THEN '6.15%' --SP FIN-299 November CLose Activities ELSE '' END AS IPT,--inclusive tax rateCASE WHEN RPD.SOURCE = 'UK_DIRECT' THEN 0.06 --DIRECT UKwhen (s.seller_seal_id IN ('subscrip_014790857874') and s.type = 'resale_item_warranty') then 0.06 --eBay UKWHEN (s.seller_seal_id = 'subscrip_014765342077' and s.type = 'resale_item_warranty') then 0.2 --E-buyerWHEN (s.create_Date < '3/1/2012' and s.SELLER_SEAL_ID = 'subscrip_014717309264') THEN 0.06 --MISCOWHEN (s.create_Date >= '3/1/2012' and s.SELLER_SEAL_ID = 'subscrip_014717309264') THEN 0.2 --MISCOwhen (s.seller_seal_id in ('subscrip_014790519901','subscrip_tsdi13050914') and s.type = 'resale_item_warranty') then 0.2 --Tesco when (s.seller_seal_id in ('subscrip_tsem13070517') and s.type = 'resale_item_warranty') then .06 ---Tesco Tabletswhen (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty' AND s.STRING_4 IN ('EG0070AN1A','EG0499AN1A','EG0100AN1D','EG0999AN1D')) then 0.06 --ASDAwhen (s.seller_seal_id IN ('subscrip_014719938103') and s.type = 'resale_item_warranty') then 0.2 --ASDA WHEN s.string_16 in ( 'RD-KINDLED100C2UK', 'RD-KINDLED101C2UK', 'RD-KINDLED104C2UK', 'RD-KINDLED1UK', 'RD-KINDLED105BC2UK', 'RD-KINDLED105AC2UK', 'RD-KINDLED100C3UK', 'RD-KINDLED101C3UK', 'RD-KINDLED104C3UK', 'RD-KINDLED105BC3UK', 'RD-KINDLED105AC3UK', 'RD-KINDLED103A3UK', 'UK-KN0124N3A', 'UK-KN0174N3A', 'RD-KINDLED2UK', 'RD-KINDLED3UK' ) THEN 0.06 --UK WHEN s.string_16 in ( 'RD-KINDLED100C2DE', 'RD-KINDLED101C2DE', 'RD-KINDLED104C2DE', 'RD-KINDLED1DE', 'RD-KINDLED105BC2DE', 'RD-KINDLED105AC2DE', 'RD-KINDLED100C3DE', 'RD-KINDLED101C3DE', 'RD-KINDLED104C3DE', 'RD-KINDLED105BC3DE', 'RD-KINDLED105AC3DE', 'RD-KINDLED103A3DE', 'DE-KN0174N3A', 'DE-KN0199N3A', 'RD-KINDLED2DE', 'RD-KINDLED3DE' ) THEN 0.19 --DE WHEN s.string_16 in ( 'RD-KINDLED100C2FR', 'RD-KINDLED101C2FR', 'RD-KINDLED104C2FR', 'RD-KINDLED1FR', 'RD-KINDLED105BC2FR', 'RD-KINDLED105AC2FR', 'RD-KINDLED100C3FR', 'RD-KINDLED101C3FR', 'RD-KINDLED104C3FR', 'RD-KINDLED105BC3FR', 'RD-KINDLED105AC3FR', 'RD-KINDLED103A3FR', 'RD-KINDLED2FR', 'RD-KINDLED3FR' ) THEN 0.09 --FR WHEN s.string_16 in ( 'RD-KINDLED100C2IT', 'RD-KINDLED101C2IT', 'RD-KINDLED104C2IT', 'RD-KINDLED1IT', 'RD-KINDLED105BC2IT', 'RD-KINDLED105AC2IT', 'RD-KINDLED100C3IT', 'RD-KINDLED101C3IT', 'RD-KINDLED104C3IT', 'RD-KINDLED105BC3IT', 'RD-KINDLED105AC3IT', 'RD-KINDLED103A3IT', 'RD-KINDLED2IT', 'RD-KINDLED3IT' ) THEN 0.2125 --IT WHEN s.string_16 in ( 'RD-KINDLED100C2ES', 'RD-KINDLED101C2ES', 'RD-KINDLED104C2ES', 'RD-KINDLED1ES', 'RD-KINDLED105BC2ES', 'RD-KINDLED105AC2ES', 'RD-KINDLED100C3ES', 'RD-KINDLED101C3ES', 'RD-KINDLED104C3ES', 'RD-KINDLED105BC3ES', 'RD-KINDLED105AC3ES', 'RD-KINDLED103A3ES', 'RD-KINDLED2ES', 'RD-KINDLED3ES' ) THEN 0.06 --ESwhen (s.seller_seal_id in ('subscrip_014770964554', 'subscrip_014791575968','subscrip_014732575965', 'subscrip_014720666569','subscrip_014788267487','subscrip_014799467487') and s.type = 'resale_item_warranty') then 0.06 --Amazon ukWHEN s.seller_seal_id in('subscrip_014712273164','subscrip_014751273163','subscrip_014730666566','subscrip_014793677486','subscrip_014722777485') THEN 0.19 -- AMAZON DEWHEN s.seller_seal_id in ('subscrip_014700666565','subscrip_014750877485','subscrip_014706877484') THEN 0.09 -- Amazon FRWHEN s.seller_seal_id in ('subscrip_014745164071','subscrip_014728417484','subscrip_014788528489') THEN 0.2125 -- Amazon ITWHEN s.seller_seal_id in ('subscrip_014763964073','subscrip_014736017485','subscrip_014708117489') THEN 0.06 -- Amazon ESelse 0 end as inclusive_tax_rate,--ST_TAXCase when s.TYPE IN ('item_warranty','ebay_item_warranty') and s.string_1 in ('AR', 'ARKANSAS','CT', 'CONNECTICUT', 'DC', 'DISTRICT OF COLUMBIA', 'FL', 'FLORIDA', 'HI', 'HAWAII', 'IA','IOWA', 'KS', 'KANSAS','NE', 'NEBRASKA', 'NJ', 'NEW JERSEY', 'NM', 'NEW MEXICO', 'NY', 'NEW YORK', 'OH', 'OHIO', 'PA', 'PENNSYLVANIA', 'SC','SOUTH CAROLINA', 'SD', 'SOUTH DAKOTA', 'TN', 'TENNESSEE', 'TX', 'TEXAS','UT', 'UTAH', 'VT','VERMONT', 'WA', 'WASHINGTON','WI','WISCONSIN', 'WV', 'WEST VIRGINIA') THEN 'Yes'when s.TYPE IN ('resale_item_warranty') and s.seller_seal_id IN ('subscrip_014737566562','subscrip_014757566566','subscrip_014796443040','subscrip_014713272753','subscrip_014787757873','subscrip_014719742854') and s.string_1 in ('AR', 'ARKANSAS','CT', 'CONNECTICUT', 'DC', 'DISTRICT OF COLUMBIA', 'FL', 'FLORIDA', 'HI', 'HAWAII', 'IA','IOWA', 'KS', 'KANSAS','NE', 'NEBRASKA', 'NJ', 'NEW JERSEY', 'NM', 'NEW MEXICO', 'NY', 'NEW YORK', 'OH', 'OHIO', 'PA', 'PENNSYLVANIA', 'SC','SOUTH CAROLINA', 'SD', 'SOUTH DAKOTA', 'TN', 'TENNESSEE', 'TX', 'TEXAS','UT', 'UTAH', 'VT','VERMONT', 'WA', 'WASHINGTON','WI','WISCONSIN', 'WV', 'WEST VIRGINIA') THEN 'Yes'ELSE 'No' END AS ST_TAX,--CADCASE WHEN (s.seller_seal_id ='subscrip_014736328772' and s.string_1 in ('AB','MB','PEI','SK','QC','PE','NT','NU')) THEN 0.05 WHEN (s.seller_seal_id ='subscrip_014736328772' and s.string_1 in ('BC')) THEN 0.12 WHEN (s.seller_seal_id ='subscrip_014736328772' and s.string_1 in ('NB','NF','LD','ON','NL')) THEN 0.13WHEN (s.seller_seal_id ='subscrip_014736328772' and s.string_1 in ('NS')) THEN 0.15 else 0 end as "CAD%",CASE WHEN (s.seller_seal_id in ('subscrip_014736328772') and s.string_1 in ('AB','MB','PEI','SK','QC','PE','NT','NU')) THEN ptli.amount * 0.05 WHEN (s.seller_seal_id in ('subscrip_014736328772') and s.string_1 in ('BC')) THEN ptli.amount *0.12 WHEN (s.seller_seal_id in ('subscrip_014736328772') and s.string_1 in ('NB','NF','LD','ON','NL')) THEN ptli.amount * 0.13WHEN (s.seller_seal_id in ('subscrip_014736328772') and s.string_1 in ('NS')) THEN ptli.amount * 0.15 else 0 end as CA_tax_amt,er.rate as exchange_rate,rpd.source as source,pt.currency_code as currency_type,--admin fee percentCASE WHEN (rpd.locale = 'en_CA') then 0 when (whole.insurer = 'Starr' and s.string_1 ilike ('wa%') and s.create_date::date >= '9/16/2013') then 0 when (whole.insurer = 'Starr' and s.create_date::date >='9/16/2013') then .04 when (whole.insurer = 'CNA' and s.create_date::date >= '7/1/2014') then 0 when (whole.insurer = 'Starr' and s.create_date::date 1) then .06 when s.seller_seal_id = 'subscrip_elsa14071410' then 0.09 -- FIN-299 November CLose Activites --FIN-287 Update for BNPwhen (whole.insurer ='BNP' and rpd.installments = 1) then .15 when (whole.insurer = 'BNP' and rpd.installments > 1) then .09 WHEN (s.seller_seal_id in ('subscrip_spls13070903','subscrip_spdc13070903','subscrip_spla13070903') and s.type = 'resale_item_warranty') then .04 ---Staples WHEN (s.create_date::date < '4/1/2013' and s.seller_seal_id in ('subscrip_014770964554','subscrip_014790519901','subscrip_014719938103','subscrip_014765342077','subscrip_014717309264','subscrip_014790857874') AND s.type = 'resale_item_warranty') THEN 0.15 --Amazon UK WHEN (s.create_date::date < '4/1/2013' and s.type = 'resale_item_warranty' and rpd.locale 'en_US' and (s.string_16 like 'RD-KIN%' OR s.string_16 like 'UK-KN%' OR s.string_16 like 'DE-KN%') and rpd.locale in ('de_DE','en_GB','es_ES','fr_FR','it_IT')) THEN 0.14 --kindle WHEN (s.create_date::date between '4/1/2013' and '11/18/2013' and s.seller_seal_id in ('subscrip_014770964554','subscrip_014790519901','subscrip_014719938103','subscrip_014765342077','subscrip_014717309264','subscrip_014790857874') AND s.type = 'resale_item_warranty') THEN 0.10 --Amazon UK WHEN (s.create_date::date between '4/1/2013' and '12/31/2013' and s.type = 'resale_item_warranty' and rpd.locale 'en_US' and (s.string_16 like 'RD-KIN%' OR s.string_16 like 'UK-KN%' OR s.string_16 like 'DE-KN%') and rpd.locale in ('de_DE','en_GB','es_ES','fr_FR','it_IT')) THEN 0.10 ---kindle WHEN (s.create_date::date >= '11/19/2013' and s.seller_seal_id in ('subscrip_014770964554','subscrip_014790519901','subscrip_014719938103','subscrip_014765342077','subscrip_014717309264','subscrip_014790857874') AND s.type = 'resale_item_warranty') THEN 0.1375 --Amazon UK WHEN (s.create_date::date >= '1/1/2014' and s.type = 'resale_item_warranty' and rpd.locale 'en_US' and (s.string_16 like 'RD-KIN%' OR s.string_16 like 'UK-KN%' OR s.string_16 like 'DE-KN%') and rpd.locale in ('de_DE','en_GB','es_ES','fr_FR','it_IT')) THEN 0.1375 ---kindle WHEN (s.seller_seal_id in ('subscrip_014770964554','subscrip_014790519901','subscrip_014719938103','subscrip_014765342077','subscrip_014717309264','subscrip_014790857874','subscrip_014790519901' ,'subscrip_tsdi13050914' ,'subscrip_tsem13070517') AND s.type = 'resale_item_warranty' and whole.insurer = 'UKG' ) THEN 0.1375 WHEN (s.CREATE_DATE::date < '1/1/2014' AND rpd.installments > 1) THEN 0 WHEN S.CREATE_DATE::date < '4/1/2009' THEN 0.2 WHEN S.CREATE_DATE BETWEEN '4/1/2009' AND '8/1/2009' THEN 0.12 WHEN S.CREATE_DATE BETWEEN '8/1/2009' AND '9/1/2009' THEN 0.1 WHEN S.CREATE_DATE BETWEEN '9/1/2009' AND '11/1/2009' THEN 0.12 WHEN S.CREATE_DATE BETWEEN '11/1/2009' AND '11/1/2010' THEN 0.1 WHEN S.CREATE_DATE BETWEEN '11/1/2010' AND '6/1/2011' THEN 0.08 WHEN S.CREATE_DATE BETWEEN '6/1/2011' AND '7/1/2011' THEN 0.1 WHEN S.CREATE_DATE BETWEEN '7/1/2011' AND '8/1/2011' THEN 0.08 WHEN S.CREATE_DATE::date >= '9/1/2011' THEN 0.06 ELSE 0.12 END AS ADMIN_FEE_PERCENT,--obligor fee percentCASE WHEN (s.seller_seal_id in ('subscrip_014770964554','subscrip_014790519901','subscrip_014719938103','subscrip_014765342077','subscrip_014717309264','subscrip_014790857874') AND s.type = 'resale_item_warranty') THEN 0 -- UK when s.seller_seal_id in ('subscrip_014793677486','subscrip_014722777485','subscrip_014736017485','subscrip_014708117489','subscrip_014750877485','subscrip_014706877484', 'subscrip_014728417484','subscrip_014788528489','subscrip_014788267487','subscrip_014799467487') then 0 WHEN (s.type = 'resale_item_warranty' and rpd.locale 'en_US' and (s.string_16 like 'RD-KIN%' OR s.string_16 like 'UK-KN%' OR s.string_16 like 'DE-KN%') and rpd.locale in ('de_DE','en_GB','es_ES','fr_FR','it_IT')) THEN 0 --kindle WHEN (rpd.locale = 'en_CA') then 0 when (whole.insurer = 'AMT' and rpd.installments > 1) then .025 --WHEN (whole.insurer = 'Starr' and s.CREATE_DATE between '16-sep-2013' and current_date --AND s.string_1 IN ('WY', 'wyoming', 'AZ', 'arizona', 'FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington', 'VA', 'virginia')) THEN 0.01 WHEN (whole.insurer = 'Starr' and s.CREATE_DATE between '2013-09-16' and '2014-07-23 23:59:59' AND s.string_1 IN ('WY', 'wyoming', 'AZ', 'arizona', 'FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington', 'VA', 'virginia')) THEN 0.01 WHEN (whole.insurer = 'Starr' and s.CREATE_DATE between '2013-09-16' and current_date AND s.string_1 IN ('FL', 'florida','WA', 'washington')) THEN 0.01 WHEN (whole.insurer = 'Starr' and cast(s.CREATE_DATE as date) 1) THEN 0 WHEN s.CREATE_DATE < '01-APR-2009' THEN 0.05 WHEN s.CREATE_DATE between '01-apr-2009' and '01-nov-2009' THEN 0.025 WHEN (s.CREATE_DATE between '01-NOV-2009' AND '01-SEP-2010' AND s.string_1 IN ('NY', 'new york', 'WI', 'wisconsin', 'WY', 'wyoming', 'AZ', 'arizona', 'CA', 'california', 'FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington')) THEN 0.025 WHEN (s.CREATE_DATE between '01-sep-2010' and '01-apr-2011' AND s.string_1 IN ('WI', 'wisconsin', 'WY', 'wyoming', 'AZ', 'arizona', 'CA', 'california', 'FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington')) THEN 0.025 WHEN (s.CREATE_DATE between '01-apr-2011' and '01-nov-2011' AND s.string_1 IN ('AZ', 'arizona', 'FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington')) THEN 0.025 WHEN (s.CREATE_DATE between '01-nov-2011' and '01-jun-2012' AND s.string_1 IN ('FL', 'florida', 'NM', 'new mexico', 'OK', 'oklahoma', 'WA', 'washington')) THEN 0.025 WHEN (s.CREATE_DATE between '01-jun-2012' and '18-jun-2013' AND s.string_1 IN ('FL', 'florida', 'OK', 'oklahoma', 'WA', 'washington')) THEN 0.025 WHEN (s.CREATE_DATE between '01-jun-2012' and current_date AND s.string_1 IN ('FL', 'florida', 'WA', 'washington')) THEN 0.025 ELSE 0 END AS OBLIGOR_FEE_PERCENT,null::integer as Days_Used,null::integer AS PRO_RATED_RESERVE,--charge classification(CASE WHEN (p.email like 'zemmouritest@yahoo.ie' or (p.email LIKE '%@squaretrade.com' and s.type in ('item_warranty','ebay_item_warranty'))) THEN 'Test_Item'WHEN (cast(s.create_date as date) BETWEEN date_trunc('month',current_date) and date_trunc('month',current_date)+'1month'::interval-'1day'::interval AND rpd.installments > 1) THEN 'New_Installment' WHEN (rpd.installments > 1) THEN 'Installment_Charge'WHEN (rpd.locale = 'es_PR') then 'PR_Sale'WHEN (rpd.locale = 'en_CA') then 'CA_Sale'when (rpd.locale = 'de_DE') then 'DE_Sale'WHEN (rpd.locale = 'en_GB') then 'UK_Sale'WHEN (rpd.locale = 'es_ES') then 'ES_Sale'WHEN (rpd.locale = 'fr_FR') then 'FR_Sale'WHEN (rpd.locale = 'it_IT') then 'IT_Sale' WHEN (s.type = 'resale_item_warranty' and s.seller_seal_id in ('subscrip_014733628554','subscrip_014703576007','subscrip_014783345463')) THEN 'Bulk_Order'WHEN (rpd.locale = 'en_US') then 'Traditional_Sale' ELSE 'Review' END) AS Charge_Classification,---Tax AMT(CASE WHEN s.string_16 in ( 'RD-KINDLED100C2UK', 'RD-KINDLED101C2UK', 'RD-KINDLED104C2UK', 'RD-KINDLED1UK', 'RD-KINDLED105BC2UK', 'RD-KINDLED105AC2UK', 'RD-KINDLED100C3UK', 'RD-KINDLED101C3UK', 'RD-KINDLED104C3UK', 'RD-KINDLED105BC3UK', 'RD-KINDLED105AC3UK', 'UK-KN0124N3A', 'UK-KN0174N3A', 'RD-KINDLED103A3UK', 'RD-KINDLED2UK', 'RD-KINDLED3UK' ) THEN round((PTLI.AMOUNT*0.056603774),2) --UK WHEN s.string_16 in ( 'RD-KINDLED100C2DE', 'RD-KINDLED101C2DE', 'RD-KINDLED104C2DE', 'RD-KINDLED1DE', 'RD-KINDLED105BC2DE', 'RD-KINDLED105AC2DE', 'RD-KINDLED100C3DE', 'RD-KINDLED101C3DE', 'RD-KINDLED104C3DE', 'RD-KINDLED105BC3DE', 'RD-KINDLED105AC3DE', 'DE-KN0174N3A', 'DE-KN0199N3A', 'RD-KINDLED2DE', 'RD-KINDLED3DE', 'RD-KINDLED103A3DE' ) THEN round((PTLI.AMOUNT*0.159663866),2) --DE WHEN s.string_16 in ( 'RD-KINDLED100C2FR', 'RD-KINDLED101C2FR', 'RD-KINDLED104C2FR', 'RD-KINDLED1FR', 'RD-KINDLED105BC2FR', 'RD-KINDLED105AC2FR', 'RD-KINDLED100C3FR', 'RD-KINDLED101C3FR', 'RD-KINDLED104C3FR', 'RD-KINDLED105BC3FR', 'RD-KINDLED105AC3FR', 'RD-KINDLED2FR', 'RD-KINDLED3FR', 'RD-KINDLED103A3FR' ) THEN round((PTLI.AMOUNT*0.082568807),2) --FR WHEN s.string_16 in ( 'RD-KINDLED100C2IT', 'RD-KINDLED101C2IT', 'RD-KINDLED104C2IT', 'RD-KINDLED1IT', 'RD-KINDLED105BC2IT', 'RD-KINDLED105AC2IT', 'RD-KINDLED100C3IT', 'RD-KINDLED101C3IT', 'RD-KINDLED104C3IT', 'RD-KINDLED105BC3IT', 'RD-KINDLED105AC3IT'