drop view if exists i2gp.gp_invrtns;

create ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
 view i2gp.gp_invrtns as

Select
    IF(gp.Sub_Type = 'Equipment',
      CONCAT('EQ_',gp.Doc_No,'_',IF(gp.Currency_Id = 'USD','US','EU')),
    IF(gp.Currency_Id = 'USD',
       Concat(
           date_format(gp.Ship_Date, '%y/%c/%e'),
           '_',
           CASE
           WHEN gp.Doc_Type = 'Return' AND IFNULL(
             (SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id)
             ,0) + gp.BilledFreight = 0 THEN 'RTNZRO'
           WHEN gp.Doc_Type = 'Return' THEN 'RTN'
           WHEN specialprocCustomer.Customer_Id  IS NOT NULL THEN 'SPCL'
           WHEN gp.Sub_Type = 'Service' AND so.pay_term LIKE '%CREDIT CARD%' THEN 'SERVCC'
           WHEN gp.Sub_Type = 'Service' THEN 'SERVICE'
           WHEN so.order_source = 'Web' AND so.pay_term = 'Net 30' THEN  IF(
             (SELECT COUNT(*)
              FROM cst1.customercontact c
              WHERE c.Active = 1 AND c.ContactType Like '%Invoice%' AND Customer_Id = gp.Customer_Id) > 0,'WEB30E','WEBN30')
           WHEN so.order_source = 'Web' AND so.pay_term <> 'Net 30' THEN 'WEBCC'
           WHEN so.pay_term LIKE '%CREDIT CARD%' THEN IF(
             (SELECT COUNT(*)
              FROM cst1.customercontact c
              WHERE c.Active = 1 AND c.ContactType Like '%Invoice%' AND Customer_Id = gp.Customer_Id) > 0,'EM_CC','CC')
           WHEN
             (SELECT
               SUM(IF(GLSalesAccount_Key = 'OUTWARRANTY' OR GLSalesAccount_Key = 'INWARRANTY',1,0)) CntWarranty
              FROM sls1.so_detail
              WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
              BETWEEN 1 AND
                 (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No)
               -1 THEN 'MULT'
           WHEN (gp.Customer_PO = 'Warranty' OR gp.Customer_PO = 'Goodwill') OR
              (SELECT
               SUM(IF(GLSalesAccount_Key = 'OUTWARRANTY' OR GLSalesAccount_Key = 'INWARRANTY',1,0)) CntWarranty
               FROM sls1.so_detail
               WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
                 = (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No) THEN 'WARRGD'
           WHEN gp.Customer_PO = 'Install' OR
              (SELECT
               SUM(IF(GLSalesAccount_Key = 'INSTALL',1,0)) CntInstall
              FROM sls1.so_detail
              WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
                = (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No) THEN 'INSTLL'
           ELSE IF(IFNULL((SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id),0) + gp.BilledFreight = 0,'OTHZRO',IF(
             (SELECT COUNT(*)
              FROM cst1.customercontact c
              WHERE c.Active = 1 AND c.ContactType Like '%Invoice%' AND Customer_Id = gp.Customer_Id) > 0,'EM_OTH','OTHER'))
           END
       ),
       Concat(
           date_format(gp.Ship_Date, '%y/%c/%e'),
           '_',
           CASE
           WHEN gp.Doc_Type = 'Return' AND IFNULL((SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id),0) + gp.BilledFreight = 0 THEN 'RTNZRO'
           WHEN gp.Doc_Type = 'Return' THEN 'RTN_E'
           WHEN specialprocCustomer.Customer_Id  IS NOT NULL THEN 'SPCL'
           WHEN gp.Sub_Type = 'Service' THEN 'SERV_E'
           WHEN so.order_source = 'Web' AND so.pay_term = 'Net 30' THEN 'WB30_E'
           WHEN so.order_source = 'Web' AND so.pay_term <> 'Net 30' THEN 'WBCC_E'
           WHEN so.pay_term LIKE '%CREDIT CARD%' THEN IF(
             (SELECT COUNT(*)
              FROM cst1.customercontact c
              WHERE c.Active = 1 AND c.ContactType Like '%Invoice%' AND Customer_Id = gp.Customer_Id) > 0,'EM_CCE','CC_E')
           WHEN (SELECT
               SUM(IF(GLSalesAccount_Key = 'OUTWARRANTY' OR GLSalesAccount_Key = 'INWARRANTY',1,0)) CntWarranty
              FROM sls1.so_detail
              WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
              BETWEEN 1 AND (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No)
                              -1
              THEN 'MUL_ET'
           WHEN (gp.Customer_PO = 'Warranty' OR gp.Customer_PO = 'Goodwill') OR
                (SELECT
               SUM(IF(GLSalesAccount_Key = 'OUTWARRANTY' OR GLSalesAccount_Key = 'INWARRANTY',1,0)) CntWarranty
               FROM sls1.so_detail
               WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
                  = (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No) THEN 'WARR_E'
           WHEN gp.Customer_PO = 'Install' OR
                (SELECT
               SUM(IF(GLSalesAccount_Key = 'INSTALL',1,0)) CntInstall
              FROM sls1.so_detail
              WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
                  = (SELECT Count(*) Cnt
                  FROM sls1.so_detail
                  WHERE Active=1 AND SO_No = gp.Doc_No) THEN 'INST_E'
           ELSE IF(IFNULL((SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id),0) + gp.BilledFreight = 0,'OTHZRO',IF(
             (SELECT COUNT(*)
              FROM cst1.customercontact c
              WHERE c.Active = 1 AND c.ContactType Like '%Invoice%' AND Customer_Id = gp.Customer_Id) > 0,'EM_E','E_OTH'))
           END
       )
    )) Batch,
      gp.Doc_Type,gp.GPDoc_No SalesDoc_No,
    gp.Ship_Date ActualShipDate,
    IF(gp.Doc_Type = 'Freight Invoice',CONCAT(gp.Doc_No,'FRT'),gp.Doc_No) Doc_No,
    gp.Doc_Date,
    gp.SourceDoc_No,
    gp.BilledFreight Freight,
    gp.IsFreightTaxable,
    0 WarrantyFreight,
    IF(gp.Doc_Type = 'Return',so.Customer_PO,gp.Customer_PO) Customer_PO,
    IF(gp.Country NOT IN ('USA', 'UNITED STATES', 'US', 'U.S.A'),'EXEMPT NOT USA',gp.TaxExemptNo) TaxExemptNo,
    -- AVATAX update not done until 5/1/15
    IF(com.getGlobal('Avatax') = 0 OR (com.getGlobal('Avatax') = 1 AND gp.Doc_Date < '2015-05-01'),
      IF(IfNull(gp.TaxCode,'')='','',Concat(gp.TaxCode,'_SCH')),'AVATAX') TaxScheduleKey,
    gp.Shipping_Code ShippingMethodKey,
    gp.Customer_Id CustomerKey,
    gp.SalesRep_Code SalesTerritoryKey,
    gp.SalesRep_Code SalespersonKey,
    Upper(IfNull(pt.Pay_Term,gp.Payment_Code)) PaymentTermsKey,
    gp.ShipTo_Name, gp.Address_1, gp.Address_2,
    gp.Address_3,gp.City,
    IF(gp.Country = 'USA' AND LENGTH(TRIM(gp.State)) <> 2 AND su.Abreviation IS NOT NULL,su.Abreviation,gp.State) State,
    gp.Zip,
    IF(IfNull((SELECT w.2LtrCode FROM  com1.countriesofworld w WHERE w.CountryName = gp.Country LIMIT 1),'') = 'US','USA',gp.Country) Country,
    IfNull((SELECT w.2LtrCode FROM  com1.countriesofworld w WHERE w.CountryName = gp.Country LIMIT 1),'') CountryCode,
    gp.Contact_Person,
    gp.Contact_Phone,

    IFNULL(CASE
       -- Note for Return is Reason (Action)
       WHEN gp.Doc_Type='Return'
          THEN Concat(rma.Reason, ' (',
         (SELECT Group_Concat(Distinct rmaline.Plan) RMAPlan
          FROM act1.gpdocline rline
          LEFT JOIN sls1.rmalinestep ON rmalinestep.Active=1
             AND rmalinestep.RMALineStep_Id =  rline.SourceLine_Id
          LEFT JOIN sls1.rmaline ON rmaline.Active=1
             AND rmaline.RMALine_Id = rmalinestep.RMALine_Id
          WHERE rline.Active=1 AND rline.GPDoc_Id = gp.GPDoc_Id
          )
            ,')')
        -- Note for Service is service dates
        WHEN so.Sub_Type='Service'
           THEN (SELECT
              IF(service.StartDate = service.EndDate,
                  CONCAT('Service On: ', DATE_FORMAT(service.StartDate, '%c/%e')),
                  CONCAT('Service On: ', DATE_FORMAT(service.StartDate, '%c/%e'), ' - ',
                               DATE_FORMAT(service.EndDate, '%c/%e'))
                 )
            FROM cst1.service
            WHERE service.Active = 1 AND service.CustomerCase_Id = so.CustomerCase_Id
            LIMIT 1)
        WHEN gp.Doc_Type='Freight Invoice'
          THEN gp.Note
        -- Note for Invoice is Trade In / TERMS if they exist
        ELSE
          Concat_WS('\n',
            (SELECT
               Group_Concat(IF(Part_No='Trade In',Part_No,NULL)) TradeIn
              FROM sls1.so_detail
              WHERE Active=1 AND so_detail.SO_No = gp.Doc_No)
            ,
			      IF(so.pay_term = 'See Terms Below',
            (SELECT GROUP_CONCAT(Equip_Pay_Term ORDER BY Estimated_Receipt_Date SEPARATOR '\n')
             FROM sls1.so_payterm
             WHERE so_payterm.SO_No = so.SO_No AND so_payterm.Active =1
             GROUP BY so_payterm.SO_No)
            ,NULL)
          )
        END
     ,'') Note,
    gp.Sub_Type OrderType,
    currency.ISO_Code Currency_ISO,
    gp.ActualFreight UserDefined_Text01,
    -- Protomax or Equipment orders put either OMAX or MAXIEM into UserDefined_Text02
    IF(gp.Sub_Type<>'Equipment'
       AND (SELECT
       Substring_Index(Group_Concat(Distinct GLSalesAccount_Key Order By Line_No),',',1) FirstGLKey
       FROM sls1.so_detail
       WHERE Active=1 AND so_detail.SO_No = gp.Doc_No
       )<>'PROTOMAX','',
      (SELECT
       Substring_Index(Group_Concat(Distinct GLSalesAccount_Key Order By Line_No),',',1) FirstGLKey
       FROM sls1.so_detail
       WHERE Active=1 AND so_detail.SO_No = gp.Doc_No
       )
     )
         UserDefined_Text02,
    IfNull(u.User_First_Name,'') UserDefined_Text03,
    IfNull(IF(
      (SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id)
      > 0,
      (SELECT SUM(IF(IsDebit=0,cd.Value,-cd.Value)) TotalComm
          FROM act1.gpdocdist cd
          JOIN act1.gpdoc c ON c.GPDoc_Id = cd.GPDoc_Id AND c.Active = 1
          JOIN act1.gldefault d ON d.gldefaultkey='COMM_LIABILITY' AND d.GLAct = cd.GLAct
          WHERE cd.Active = 1 AND c.Doc_No = gp.Doc_No)
      ,''),'') UserDefined_Text04,
    IF(gp.Sub_Type='Equipment' AND gp.FreightPay_Method='Included' AND (SELECT SUM(Price * Qty) TotalOrder
              FROM act1.gpdocline
              WHERE gpdocline.Active=1 AND gpdocline.GPDoc_Id = gp.GPDoc_Id) > 0, so.Freight,'') UserDefined_Text05,
    IF(gp.FreightPay_Method='Bill Install At Customer','Billed',gp.FreightPay_Method) UserDefined_List01,
    '' UserDefined_List02,
    '' UserDefined_List03,
    IF((SELECT COUNT(*)
        FROM act1.vatinvoice
        WHERE vatinvoice.GPDoc_No = gp.GPDoc_No) > 0,'ITEK','')
      CommentKey,
    (SELECT CONCAT_WS('-',DATE_FORMAT(CONCAT_WS('-',VatInvoice_Year,'01','01'),'%y'),LPAD(VatInvoice_YearSeq, 4, "0"))
        FROM act1.vatinvoice
        WHERE vatinvoice.GPDoc_No = gp.GPDoc_No)
      VatInvoiceNo
 From act1.gpdoc gp
 JOIN act1.currency ON currency.Currency_Id = gp.Currency_Id
 Left Join sls1.so_master so ON gp.Doc_No = so.SO_No
 Left Join sls1.rma ON rma.Active=1 AND rma.RMA_No = gp.SourceDoc_No
 Left Join mis1.insight_user u ON u.User_name = so.CreatedBy AND u.Active = 1
 LEFT JOIN com1.statesofusa su on su.StateName = gp.State
 LEFT JOIN (SELECT Customer_Id
   FROM cst1.customercontact
   WHERE Active = 1 AND ContactType Like '%Special Invoice Processing%'
   GROUP BY Customer_Id) specialprocCustomer ON specialprocCustomer.Customer_Id = gp.Customer_Id
 Left Join com1.payment_term pt ON pt.Company_Type = 'Customer'
    AND pt.Payment_Code = gp.Payment_Code
 WHERE gp.Active=1;