[b]CURSOR custno_cur IS [/b]
with run_date as
(select to_date('31-DEC-13') rd_date --max(accpe_period) rd_date
 from dual ), --accounts_pe_params),
pe_data as
(
select accpe_account_no,
       accpe_subacc_no,
       accpea_balance_os,
       pacx_product_code,
       rd_date
from accounts_pe_params
inner join accounts_pe_afs
on accpea_soc_seqno = accpe_soc_seqno
and accpea_account_no = accpe_account_no
and accpea_subacc_no = accpe_subacc_no
and accpea_period = accpe_period
inner join account_types
on act_code = accpe_act_code
inner join run_date
on accpe_period = rd_date
inner join product_act_code_xref
on pacx_act_code = accpe_act_code
where accpe_stream_no like '%'
and accpe_process_status = 2
and accpe_gact_type in ('C','U','T','S')
and decode(nvl(act_overdraft_ind,'N'),
           'N',0,
           decode(sign(nvl(accpea_balance_os,0)),
                  -1,1,
                  0)
          ) = 0
),
acc_custs_balance as
(select accpe_account_no acb_account_no,
        accpe_subacc_no acb_subacc_no,
        pacx_product_code AS acb_product_code,
        rd_date,
        nvl((select count(distinct case when scvmcr_master_cust_seqno is null then accl_cust_seqno
                                        else scvmcr_master_cust_seqno
                                   end)
             from account_links
             inner join run_date
             on trunc(accl_create_date) <= rd_date
             left outer join scv_master_cust_relationships
             on (scvmcr_master_cust_seqno = accl_cust_seqno
                 or
                 scvmcr_sub_cust_seqno = accl_cust_seqno)
             where accl_account_no = accpe_account_no
             and accl_subacc_no = accpe_subacc_no
             and nvl(accl_end_date,rd_date) >= rd_date
             and accl_cust_usage not in ('AB',
	                            'TR',
	                            'FB',
	                            'SI',
	                            'BA',
	                            'PA',
	                            'CO',
	                            'BE',
	                            'VB',
	                            'VL',
	                            'PR',
	                            'OW',
	                            'NM',
	                            'RV',
	                            'EX',
	                            'AT',
	                            'RB')
        ),1) acb_customers,
        nvl(accpea_balance_os,0) acb_balance_os,
        case when accpea_balance_os >= 0 then accpea_balance_os else 0 end AS acb_balance_os_posonly
 from pe_data
),
acc_customers AS
(select accpe_account_no AS ac_account_no,
        accpe_subacc_no AS ac_subacc_no,
        pacx_product_code AS ac_product_code,
        nvl(accl_cust_seqno,accpe_account_no) ac_cust_seqno
 from pe_data
 left outer join (select accl_account_no,
                         accl_subacc_no,
                         case when scvmcr_master_cust_seqno is null then accl_cust_seqno
                              else scvmcr_master_cust_seqno
                         end accl_cust_seqno
                  from account_links
                  inner join run_date
                  on trunc(accl_create_date) <= rd_date
                  left outer join scv_master_cust_relationships
                  on (scvmcr_master_cust_seqno = accl_cust_seqno
                      or
                      scvmcr_sub_cust_seqno = accl_cust_seqno)
             where nvl(accl_end_date,rd_date) >= rd_date
             and accl_cust_usage not in ('AB',
	                            'TR',
	                            'FB',
	                            'SI',
	                            'BA',
	                            'PA',
	                            'CO',
	                            'BE',
	                            'VB',
	                            'VL',
	                            'PR',
	                            'OW',
	                            'NM',
	                            'RV',
	                            'EX',
	                            'AT',
	                            'RB')
                 )
 on accl_account_no = accpe_account_no
 and accl_subacc_no = accpe_subacc_no
 group by accpe_account_no,
        accpe_subacc_no,
        pacx_product_code,
        nvl(accl_cust_seqno,accpe_account_no)
),
customer_balances as
(select ac_cust_seqno AS cb_cust_seqno,
        min(rd_date) AS rd_date,
        sum(acb_balance_os/case when acb_customers = 0 then 1
                                else acb_customers
                           end) cb_cust_balance,
        sum(acb_balance_os_posonly / case when acb_customers = 0 then 1
                                else acb_customers
                           end) cb_cust_balance_posonly
 from acc_custs_balance
 inner join acc_customers
 on ac_account_no = acb_account_no
 and ac_subacc_no = acb_subacc_no 
 group by ac_cust_seqno
),
customer_balances_by_product as
(select ac_cust_seqno AS cbp_cust_seqno,
        acb_product_code AS cbp_product_code,
        sum(acb_balance_os/case when acb_customers = 0 then 1
                                else acb_customers
                           end) cbp_cust_balance
 from acc_custs_balance
 inner join acc_customers
 on ac_account_no = acb_account_no
 and ac_subacc_no = acb_subacc_no 
 group by ac_cust_seqno, acb_product_code
)
,cte_prod_hier
AS
( SELECT hddhd_name, hddhd_long_name, hddhd_parent
  FROM   reporting_hierarchy
  where  hddhd_hier_seqno = 67
)
,cte_prod_hier2
AS
( SELECT  hddhd_name, hddhd_long_name, hddhd_parent, level AS prod_level, CONNECT_BY_ROOT hddhd_name AS root
  FROM    cte_prod_hier
  WHERE LEVEL <= 3
  CONNECT BY HDDHD_NAME = PRIOR HDDHD_PARENT
)
,cte_prod_hierarchy
AS
( SELECT  l1.ROOT AS product_code
          ,'[' || l1.hddhd_name || +  '] - ' || l1.hddhd_long_name AS product
          ,'[' || l2.hddhd_name || +  '] - ' || l2.hddhd_long_name AS product_class
          ,'[' || l3.hddhd_name || +  '] - ' || l3.hddhd_long_name AS Product_Group
  FROM    cte_prod_hier2 l1
  LEFT JOIN    cte_prod_hier2 l2
  ON      l2.root = l1.root
  AND     l2.prod_level = 2
  LEFT JOIN    cte_prod_hier2 l3
  ON      l3.root = l1.root
  AND     l3.prod_level = 3
  WHERE   l1.prod_level = 1
)
,cte_bands
AS
( SELECT  rd_date
          ,cbp_product_code
          ,h.product
          ,h.product_class
          ,h.product_group
          ,cb_cust_seqno
          ,cb_cust_balance
          ,cbp_cust_balance
          /* Proportion of a customer counted in each product. Only positive customer product balances are counted, otherwise
             get some anomalous results e.g. cust 11917398 had 2 accts, 1 at -49p and another at 50p, total bal = 1p 
             Issue 12: Also set cust count = 0 if overall cust balance <= 0 */
          ,CASE WHEN cb_cust_balance > 0 AND cbp_cust_balance > 0 THEN cbp_cust_balance / cb_cust_balance_posonly
                ELSE 0
          END AS cust_prod_count
          /* Balance band, if customer is over (even by a fraction) over a band limit put them in the next band up */
            ,CASE WHEN cb_cust_balance >= 0.01 THEN LEAST(FLOOR(CEIL((cb_cust_balance - 0.01) * 100)/(1000*100)),250)
                  WHEN cb_cust_balance > 0 THEN 0
                  ELSE -1 
            END AS bal_band_no
          ,CASE WHEN cb_cust_balance > 85000 
              THEN ROUND(cbp_cust_balance * 85000 / cb_cust_balance,2) 
              ELSE cbp_cust_balance
          END AS Prod_In_Scheme
          ,CASE WHEN cb_cust_balance > 85000 
              THEN ROUND(cbp_cust_balance * ( cb_cust_balance - 85000 ) / cb_cust_balance,2) 
              ELSE 0
          END AS Prod_Out_of_Scheme
  FROM    customer_balances_by_product
  JOIN    customer_balances
  ON      cb_cust_seqno = cbp_cust_seqno
  JOIN    cte_prod_hierarchy h
  ON      h.product_code = cbp_product_code
)
SELECT  rd_date AS period_end
        ,cb_cust_seqno AS cust_seqno
        ,cbp_product_code AS product_code
        ,product
        ,product_class
        ,product_group
        ,CAST(cb_cust_balance AS NUMBER(14,4)) AS cust_balance
        ,CAST(cbp_cust_balance AS NUMBER(14,4)) AS cust_product_balance
        ,bal_band_no
        ,CAST(Prod_In_Scheme AS NUMBER(14,4)) AS Prod_In_Scheme
        ,CAST(Prod_Out_of_Scheme AS NUMBER(14,4)) AS Prod_Out_of_Scheme
        ,CAST(cust_prod_count AS NUMBER(9,6)) AS Cust_Count
        ,CAST(CASE WHEN cbp_cust_balance > 0 
              THEN cust_prod_count * (Prod_In_Scheme / cbp_cust_balance) 
              ELSE 0 
        END AS NUMBER(9,6)) AS cust_prod_count_in_scheme
        ,CAST(CASE WHEN cbp_cust_balance > 0 
              THEN cust_prod_count * (Prod_Out_Of_Scheme / cbp_cust_balance) 
              ELSE 0 
        END AS NUMBER(9,6)) AS cust_prod_count_out_of_scheme
        ,NVL(SUBSTR(RTRIM(addr_post_code),1,LENGTH(RTRIM(addr_post_code))-2),'?') AS PC_Part_1
FROM    cte_bands
LEFT OUTER JOIN
        customers
ON      cust_seqno = cb_cust_seqno
LEFT OUTER JOIN
        addresses
ON      addr_address_no = cust_addr_address_no