!! UDF standard
----
! Sviluppo quantità MPPIAN

B£UDF_xx  TODO

!! MPPIAN
----
Aggregazione BUDGET e confroni con venduto a 3 anni.

--> vedi XJAC05_SM allegato (some si può linkare?)

! File interessati

||File||Significato
| MPPIAN0F   | Piani per budget qtà/valori
| XCOCLM0F | Storico venduto qtà/valori

! Significato campi

||MPCD01||MPCD02
| Cliente   | Articolo


! Istruzione SQL

----

estrae i clienti/articoli presenti fra gli anni 2006 e 2009.
il DB2/400 non supporta full JOIN -> creazione tabelle temporanee

----
commit;

drop table QTEMP.XJAC05_SM;

DECLARE GLOBAL TEMPORARY TABLE 
XJAC05_SM ( CLI VARCHAR(06), ART VARCHAR(15) ) ;                  



insert into QTEMP.XJAC05_SM

select distinct MPCD01 as CLI,MPCD02 as ART from SMEUP_TPER.MPPIAN0F where MPCDPI='BUD09' 
 and  MPCD01='005948' and MPCD02 like '6028005%'


union

(select distinct CLCC06 as CLI,CLCA15 as ART from SMEUP_TPER.XCOCLM0F where (CLANNO BETWEEN 07 and 08) and CLCC06='005948' and CLCA15 like '6028005%')


order by CLI,ART

;





SELECT CLI,ART,

ifNull((0+CAW2009.MPQ001+CAW2009.MPQ002+CAW2009.MPQ003+CAW2009.MPQ004+CAW2009.MPQ005+CAW2009.MPQ006+CAW2009.MPQ007+CAW2009.MPQ008+CAW2009.MPQ009+CAW2009.MPQ010+CAW2009.MPQ011+CAW2009.MPQ012),0),

ifNull((0+VAW2009.MPQ001+VAW2009.MPQ002+VAW2009.MPQ003+VAW2009.MPQ004+VAW2009.MPQ005+VAW2009.MPQ006+VAW2009.MPQ007+VAW2009.MPQ008+VAW2009.MPQ009+VAW2009.MPQ010+VAW2009.MPQ011+VAW2009.MPQ012),0),

ifNull((0+CAO2009.MPQ001+CAO2009.MPQ002+CAO2009.MPQ003+CAO2009.MPQ004+CAO2009.MPQ005+CAO2009.MPQ006+CAO2009.MPQ007+CAO2009.MPQ008+CAO2009.MPQ009+CAO2009.MPQ010+CAO2009.MPQ011+CAO2009.MPQ012),0),

ifNull((0+VAO2009.MPQ001+VAO2009.MPQ002+VAO2009.MPQ003+VAO2009.MPQ004+VAO2009.MPQ005+VAO2009.MPQ006+VAO2009.MPQ007+VAO2009.MPQ008+VAO2009.MPQ009+VAO2009.MPQ010+VAO2009.MPQ011+VAO2009.MPQ012),0),

ifNull((0+COC2008.CLQT01+COC2008.CLQT02+COC2008.CLQT03+COC2008.CLQT04+COC2008.CLQT05+COC2008.CLQT06+COC2008.CLQT07+COC2008.CLQT08+COC2008.CLQT09+COC2008.CLQT10+COC2008.CLQT11+COC2008.CLQT12),0),

ifNull((0+COC2008.CLVA01+COC2008.CLVA02+COC2008.CLVA03+COC2008.CLVA04+COC2008.CLVA05+COC2008.CLVA06+COC2008.CLVA07+COC2008.CLVA08+COC2008.CLVA09+COC2008.CLVA10+COC2008.CLVA11+COC2008.CLVA12),0),

ifNull((0+COC2007.CLQT01+COC2007.CLQT02+COC2007.CLQT03+COC2007.CLQT04+COC2007.CLQT05+COC2007.CLQT06+COC2007.CLQT07+COC2007.CLQT08+COC2007.CLQT09+COC2007.CLQT10+COC2007.CLQT11+COC2007.CLQT12),0),

ifNull((0+COC2007.CLVA01+COC2007.CLVA02+COC2007.CLVA03+COC2007.CLVA04+COC2007.CLVA05+COC2007.CLVA06+COC2007.CLVA07+COC2007.CLVA08+COC2007.CLVA09+COC2007.CLVA10+COC2007.CLVA11+COC2007.CLVA12),0),

ifNull((0+COC2006.CLQT01+COC2006.CLQT02+COC2006.CLQT03+COC2006.CLQT04+COC2006.CLQT05+COC2006.CLQT06+COC2006.CLQT07+COC2006.CLQT08+COC2006.CLQT09+COC2006.CLQT10+COC2006.CLQT11+COC2006.CLQT12),0),

ifNull((0+COC2006.CLVA01+COC2006.CLVA02+COC2006.CLVA03+COC2006.CLVA04+COC2006.CLVA05+COC2006.CLVA06+COC2006.CLVA07+COC2006.CLVA08+COC2006.CLVA09+COC2006.CLVA10+COC2006.CLVA11+COC2006.CLVA12), 0) 



FROM    



QTEMP.XJAC05_SM 



join SMEUP_TPER.BRARTI0F on ART=A§ARTI 




left join SMEUP_TPER.MPPIAN0F as CAW2009 on CLI=CAW2009.MPCD01 and ART=CAW2009.MPCD02 and CAW2009.MPCDPI='BUD09' and CAW2009.MPTPRC='VAW' 

left join SMEUP_TPER.MPPIAN0F as VAW2009 on CLI=VAW2009.MPCD01 and ART=VAW2009.MPCD02 and VAW2009.MPCDPI='BUD09' and VAW2009.MPTPRC='VAW' 

left join SMEUP_TPER.MPPIAN0F as CAO2009 on CLI=CAO2009.MPCD01 and ART=CAO2009.MPCD02 and CAO2009.MPCDPI='BUD09' and CAO2009.MPTPRC='CAO' 

left join SMEUP_TPER.MPPIAN0F as VAO2009 on CLI=VAO2009.MPCD01 and ART=VAO2009.MPCD02 and VAO2009.MPCDPI='BUD09' and VAO2009.MPTPRC='VAO'  

left join SMEUP_TPER.XCOCLM0F as COC2008 on CLI=COC2008.CLCC06 and ART=COC2008.CLCA15 and COC2008.CLANNO=08 and COC2008.CLBGVE='V'  

left join SMEUP_TPER.XCOCLM0F as COC2007 on CLI=COC2007.CLCC06 and ART=COC2007.CLCA15 and COC2007.CLANNO=07 and COC2007.CLBGVE='V'  

left join SMEUP_TPER.XCOCLM0F as COC2006 on CLI=COC2006.CLCC06 and ART=COC2006.CLCA15 and COC2006.CLANNO=06 and COC2006.CLBGVE='V' 



WHERE A§CLGE='F'  ORDER BY CAW2009.MPCD01,CAW2009.MPCD02;