Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- sub Check19
- //////////////////////// PARTE 1: hfm ////////////////////////////////////
- Inline:
- load * inline [
- id, scenario, tempo1, tempo2
- 1, CURRENT , $(vsMonthYearCurrent) , $(vsMonthYearClosed)
- 2, CLOSED MONTH, $(vsMonthYearClosed) , $(vsMonthYearPrevious)
- ];
- let vEndif = FieldValueCount('id');
- for i=1 to '$(vEndif)'
- let vsScenarioLoop =FieldValue('scenario',i);
- let vsTempo1Loop =FieldValue('tempo1',i);
- let vsTempo2Loop =FieldValue('tempo2',i);
- Check1_tmp:
- load
- '$(vsScenarioLoop)' as SCENARIO,
- ID_COMPANY,
- CD_ACCOUNT_HFM,
- CD_PRODUCT_HFM as CD_PRODUCT_HFM,
- upper(DS_PRODUCT_HFM) as DS_PRODUCT_HFM,
- ID_DATE,
- AMOUNT_HFM,
- if(applymap('Map_date_yearmonth',ID_DATE)='$(vsTempo1Loop)',1,0) as FLAG_CUR_MONTH
- FROM [$(vsConnection)QVD01\$(vsSystem)\QVD01_$(vsSystem)_FAT_HFM_SALES.qvd]
- (qvd)
- where
- applymap('Map_date_yearmonth',ID_DATE)='$(vsTempo1Loop)'
- or
- applymap('Map_date_yearmonth',ID_DATE)='$(vsTempo2Loop)';
- next i
- left join(Check1_tmp)
- LOAD distinct
- CD_ACCOUNT_HFM,
- FL_REVENUE,
- FL_COGS,
- FL_COGS_ICO
- FROM [$(vsConnection)QVD01/$(vsSystem)/QVD01_$(vsSystem)_DIM_ACCOUNT_HFM.qvd]
- (qvd);
- //creo mappa per avere descrizione pl uguale tra hfm e om
- map_desc_pl:
- mapping load distinct
- CD_PRODUCT_HFM,
- DS_PRODUCT_HFM
- resident Check1_tmp;
- //////////////////////// PARTE 2: OM ////////////////////////////////////
- for i=1 to '$(vEndif)'
- let vsScenarioLoop =FieldValue('scenario',i);
- let vsTempo1Loop =FieldValue('tempo1',i);
- //==========================================================================
- //leggo il fatturato + costi (Fatturato Statistico e Contabile)
- //==========================================================================
- Check1_tmp2:
- LOAD
- '$(vsScenarioLoop)' as SCENARIO,
- '$(vsIDCompany)' as ID_COMPANY,
- ID_CUSTOMER as [% ID_KEY_CUSTOMER],
- ID_PRODUCT as ID_KEY_PRODUCT,
- left(ID_DATE,6) as YEAR_MONTH,
- AMOUNT_NET as INVOICE_LOC_AMOUNT,
- AMOUNT_NET as INVOICE_LOC_AMOUNT_RR,
- AMOUNT_COST as AVERAGE_COST_VALUE,
- AMOUNT_COST as AVERAGE_COST_VALUE_TR,
- AMOUNT_COST_RR as AVERAGE_COST_VALUE_RR,
- AMOUNT_COST as AVERAGE_COST_VALUE_RR_TR, //senza M_QTY diventano doppioni
- if(applymap('Map_date_yearmonth',ID_DATE)='$(vsTempo1Loop)',1,0) as FLAG_CUR_MONTH,
- ROWNO() AS ID
- FROM [$(vsConnection)QVD01\$(vsSystem)\QVD01_$(vsSystem)_FAT_SALES_INVOICE.qvd] (qvd)
- where
- left(ID_DATE,6)<='$(vsTempo1Loop)'
- AND left(ID_DATE,4)=LEFT('$(vsTempo1Loop)',4)
- and FL_SALES=1
- ;
- next i
- // drop table Inline;
- //==========================================================================
- //aggancio customer info (cliente di fatturazione)
- //==========================================================================
- left join(Check1_tmp2)
- LOAD
- ID_CUSTOMER as [% ID_KEY_CUSTOMER], //chiave per DIM_CUSTOMER_INVOICE
- DS_TYPE_CUSTOMER as [TYPE CUSTOMER],
- DS_CUSTOMER as [DS CUSTOMER] ,
- if(match(UPPER(DS_TYPE_CUSTOMER),'INTERCOMPANY'),1,0) as FLAG_ICO
- FROM [$(vsConnection)QVD01\$(vsSystem)\QVD01_$(vsSystem)_DIM_CUSTOMER.qvd]
- (qvd);
- //==========================================================================
- //aggancio product info
- //==========================================================================
- Left join(Check1_tmp2)
- LOAD
- ID_PRODUCT as ID_KEY_PRODUCT,
- DS_PRODUCT,
- CD_PRODUCT,
- CD_CODIMP,
- UNIT_CODE as DS_UOM,
- applymap('Map_Structure',CD_CODIMP) as ID_STRUCTURE_R12,
- DS_TYPE_PRODUCT,
- TYPE_PRODUCT
- // CD_PL_HFM as CD_PRODUCT_HFM,
- // applymap('map_desc_pl',CD_PL_HFM,DS_PL_HFM) as DS_PRODUCT_HFM
- FROM [$(vsConnection)QVD01\$(vsSystem)\QVD01_$(vsSystem)_DIM_PRODUCT.qvd]
- (qvd);
- //concateno hfm e gl
- //==========================================================================
- //aggancio structure R12
- //==========================================================================
- LEFT JOIN(Check1_tmp2)
- LOAD
- ID_STRUCTURE as ID_STRUCTURE_R12,
- applymap('map_desc_pl',CD_LINE,DS_LINE) as DS_PRODUCT_HFM,
- CD_LINE as CD_PRODUCT_HFM
- FROM [$(vsConnection)QVD01\R12\QVD01_R12_DIM_STRUCTURE.qvd]
- (qvd);
- Concatenate(Check1_tmp)
- LOAD
- *
- Resident Check1_tmp2;
- DROP TABLE Check1_tmp2;
- //==========================================================================
- //uso il qualify per evitare sintetiche
- //==========================================================================
- Qualify *;
- Unqualify ID_COMPANY;
- Check19:
- Load
- *
- Resident Check1_tmp;
- drop table Check1_tmp;
- STORE Check19 INTO $(vsStoreQvd)QVD02\$(vsCompany)\$(vsMonthYearCurrent)_QVD02_Check19.qvd (qvd);
- drop table Check19;
- Unqualify *;
- Unqualify ID_COMPANY;
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement