Generate_Planing_Data

Generate_Plan_Data.sql

do begin
declare la_VERSION  nvarchar(3) array = array('001', '002') ;
declare la_VTYPE  nvarchar(3) array = array('10', '20') ;
declare la_CO_AREA  nvarchar(4) array = array('1001') ;
declare la_COSTCENTER  nvarchar(10) array = array(  'CC000000',
                                                    'CC111111',
                                                    'CC222222',
                                                    'CC333333',
                                                    'CC444444',
                                                    'CC555555',
                                                    'CC666666',
                                                    'CC777777',
                                                    'CC888888',
                                                    'CC999999') ;

declare la_FISCPER  nvarchar(7) array = array('2020001', '2020002', '2020003', '2020004') ;



lt_VERSION = unnest(:LA_VERSION) as (VERSION);
lt_VTYPE = unnest(:LA_VTYPE) as (VTYPE);
lt_CO_AREA = unnest(:LA_CO_AREA) as (CO_AREA);
lt_COSTCENTER = unnest(:LA_COSTCENTER) as (COSTCENTER);
lt_CURRENCY = unnest(:LA_CURRENCY) as (CURRENCY);
lt_FISCPER = unnest(:LA_FISCPER) as (FISCPER);
  
  select    VERSION,
            VTYPE,
            CO_AREA,
            account as COSTELMNT,
            COSTCENTER,
            right(fiscper, 3) as FISCPER3,
            left(fiscper, 4) as FISCYEAR,
            'K4' as FISCVARNT,
            'EUR' as CURRENCY,
            FISCPER,
            to_date( left(fiscper, 4)||right(fiscper, 2)||'01' ) as DATE0,
            'JBRANDEIS' AS USERNAME,
            case 
              when right(COSTCENTER,1) in ('0', '1', '2', '3') then 'PC0123'
              when right(COSTCENTER,1) in ('4','5','6') then 'PC456'
              when right(COSTCENTER,1) in ('7','8','9') then 'PC789'
              else right(costcenter,1)
            end as PROFIT_CTR,
            '' AS RECORDMODE,
            round(RAND() * 10000, 2) as AMOUNT
 from :LT_VERSION
cross join :LT_VTYPE
cross join :LT_CO_AREA
cross join zaccount --:LT_COSTELMNT
cross join :LT_COSTCENTER
cross join :LT_FISCPER
  ;
end;