CREATE OR REPLACE FORCE VIEW CCS3.BILLABLE_ACCOUNTS
(
   SEQ_ID,
   BA_NUMBER,
   BA_NAME,
   BICY_ID,
   PAYMENT_METHOD,
   EGN,
   SENT_TO_ADDRESS,
   SENT_TO_BLOCK,
   SENT_TO_POST_CODE,
   SENT_TO_CITY,
   OB,
   NIDISP,
   DEPOSIT,
   NELI,
   ELI,
   ELI30,
   ESI,
   ESI60,
   ETI,
   ETI90,
   ALL_INV,
   DEP,
   GAR,
   ACCOUNT_NO,
   AVRG_INV3,
   CUST_SEQ_ID,
   CAT_SEQ_ID,
   LEVEL_SEQ_ID,
   G_STATUS,
   G_STATUS_UPDATED,
   STATUS,
   STATUS_DATE,
   STATUS_UPDATED,
   NOT_PAID_ON_TIME_3,
   CORR,
   CORR_FLAG,
   BILLING_REFRESH_DATE,
   EGN_CONSENT,
   INV_NUM_3,
   CL,
   POSTPONE_FIXED_DATE,
   POSTPONE_ID,
   BANK_PAYMENT_VALIDITY,
   NOTIFY_ONLY,
   NO_WARN,
   COLOR_FLAG,
   CRP_NAME,
   CRP_INHERIT_CC,
   NO_WARN_NBA,
   NOTIFY_ONLY_NBA,
   COLOR_FLAG_NBA,
   CRP_NAME_NBA,
   CRP_INHERIT_BA_NBA,
   CL_NBA,
   MASTER_CODE,
   RISK,
   PROFITABILITY,
   DENY_SCC,
   DENY_SCC_ADM,
   EMPTY_RECORD,
   INSERT_ID,
   NPMT,
   NPMT_TIMESTAMP,
   LOAD_ID,
   DEACT_DATE,
   FLAG_DISABLE_GROUPING,
   PRICEGROUP_ID,
   COMPANY,
   VAT,
   BULSTAT,
   BILL_TO_ADDRESS,
   BILL_TO_BLOCK,
   BILL_TO_POST_CODE,
   BILL_TO_CITY,
   POSTAL_PHONE_NO,
   MOBILE_PHONE,
   FAX,
   EMAIL,
   FLAG_EGN_CHANGE,
   FIRST_NAME,
   FATHER_NAME,
   LAST_NAME,
   ID,
   FLAG_EGN_CONSIDERED,
   CUST_CODE,
   LTRIM_EGN,
   TRANSLATE_ID,
   CUSTOMER_ID,
   NITCA,
   SUBSIDY,
   SUBSIDY_VALID_UNTIL,
   BDI_OB,
   BDI_UB,
   BDI_FORFEITS,
   BDI_DEVICE_PENALTIES,
   BDI_LEASING_AMOUNT,
   BDI_OCCS,
   BDI_UPDATED_ON,
   ANONYMIZED
)
AS
   SELECT bccs.SEQ_ID,
          bccs.BA_NUMBER,
          bccs.BA_NAME,
          bccs.BICY_ID,
          bccs.PAYMENT_METHOD,
          bccs.EGN,
          bccs.SENT_TO_ADDRESS,
          bccs.SENT_TO_BLOCK,
          bccs.SENT_TO_POST_CODE,
          bccs.SENT_TO_CITY,
          bccs.OB,
          bccs.NIDISP,
          bccs.DEPOSIT,
          bccs.NELI,
          bccs.ELI,
          bccs.ELI30,
          bccs.ESI,
          bccs.ESI60,
          bccs.ETI,
          bccs.ETI90,
          bccs.ALL_INV + bccs.Not_Migrated_Inv,
          bccs.DEP,
          bccs.GAR,
          bccs.ACCOUNT_NO,
          bccs.AVRG_INV3,
          ccs.CUST_SEQ_ID,
          ccs.CAT_SEQ_ID,
          ccs.LEVEL_SEQ_ID,
          ccs.G_STATUS,
          ccs.G_STATUS_UPDATED,
          bccs.STATUS,
          bccs.STATUS_DATE,
          bccs.STATUS_UPDATED,
          bccs.NOT_PAID_ON_TIME_3,
          bccs.CORR,
          bccs.corr_flag,
          bccs.REFRESH_DATE,
          bccs.EGN_CONSENT,
          bccs.INV_NUM_3,
          ccs.CL,
          ccs.POSTPONE_FIXED_DATE,
          ccs.POSTPONE_ID,
          ccs.BANK_PAYMENT_VALIDITY,
          CCS.NOTIFY_ONLY,
          CCS.NO_WARN,
          CCS.COLOR_FLAG,
          CCS.CRP_NAME,
          CCS.CRP_INHERIT_CC,
          CCS.NO_WARN_NBA,
          CCS.NOTIFY_ONLY_NBA,
          CCS.COLOR_FLAG_NBA,
          CCS.CRP_NAME_NBA,
          CCS.CRP_INHERIT_BA_NBA,
          CCS.CL_NBA,
          CCS.MASTER_CODE,
          CCS.RISK,
          CCS.PROFITABILITY,
          CCS.DENY_SCC,
          CCS.DENY_SCC_ADM,
          CCS.EMPTY_RECORD,
          ccs.INSERT_ID,
          pmt.NPMT,
          pmt.NPMT_TIMESTAMP,
          pmt.LOAD_ID,
          bccs.deact_date,
          ccs.flag_disable_grouping,
          CASE
             WHEN SUBSTR (BCCS.egn, 1, 3) = 'EGN' THEN 1
             WHEN SUBSTR (BCCS.egn, 1, 3) = 'BUL' THEN 2
             WHEN (BCCS.COMPANY IS NULL) OR (BCCS.COMPANY = '') THEN 1
             ELSE 2
          END
             AS PRICEGROUP_ID,
          bccs.company,
          bccs.vat,
          bccs.bulstat,
          bccs.bill_to_address,
          bccs.bill_to_block,
          bccs.bill_to_post_code,
          bccs.bill_to_city,
          bccs.postal_phone_no,
          bccs.mobile_phone,
          bccs.fax,
          bccs.email,
          bccs.flag_egn_change,
          bccs.first_name,
          bccs.father_name,
          bccs.last_name,
          bccs.id,
          ccs.flag_egn_considered,
          ccs.cust_code,
          bccs.ltrim_egn,
          bccs.translate_id,
          bccs.customer_id,
          bccs.nitca,
          bccs.subsidy,
          bccs.subsidy_valid_until,
          bccs.BDI_OB,
          bccs.BDI_UB,
          bccs.BDI_FORFEITS,
          bccs.BDI_DEVICE_PENALTIES,
          bccs.BDI_LEASING_AMOUNT,
          bccs.BDI_OCCS,
          bccs.BDI_UPDATED_ON,
          bccs.ANONYMIZED
     FROM ba_bccs_1 bccs,
          BILLABLE_ACCOUNTS_CCS ccs,
          BILLABLE_ACCOUNTS_PMT pmt
    WHERE     bccs.seq_id = ccs.seq_id
          AND bccs.seq_id = pmt.seq_id
          AND ccs.seq_id = pmt.seq_id;