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

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