View Attach (1) Info
This is version . It is not the current version, and thus it cannot be edited.
[Back to current version]   [Restore this version]

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#

FileSignificato
MPPIAN Piani per budget qtà/valori
XCOCLM0F Storico venduto qtà/valori

Significato campi#

MPCD01MPCD02
Cliente Articolo

Istruzione SQL#

estrae solo quelli presenti in CAW2009, modificare LEFT/RIGHT/FULL in JOIN

SELECT CAW2009.MPCD01,CAW2009.MPCD02, 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 SMEUP_TPER.MPPIAN0F as CAW2009 join SMEUP_TPER.BRARTI0F on CAW2009.MPCD02=A§ARTI left join SMEUP_TPER.MPPIAN0F as VAW2009 on CAW2009.MPCD01=VAW2009.MPCD01 and CAW2009.MPCD02=VAW2009.MPCD02 and VAW2009.MPCDPI='BUD09' and VAW2009.MPTPRC='VAW'

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

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

left join SMEUP_TPER.XCOCLM0F as COC2008 on CAW2009.MPCD01=COC2008.CLCC06 and CAW2009.MPCD02=COC2008.CLCA15 and COC2008.CLANNO=08 and COC2008.CLBGVE='V'

left join SMEUP_TPER.XCOCLM0F as COC2007 on CAW2009.MPCD01=COC2007.CLCC06 and CAW2009.MPCD02=COC2007.CLCA15 and COC2007.CLANNO=07 and COC2007.CLBGVE='V'

left join SMEUP_TPER.XCOCLM0F as COC2006 on CAW2009.MPCD01=COC2006.CLCC06 and CAW2009.MPCD02=COC2006.CLCA15 and COC2006.CLANNO=06 and COC2006.CLBGVE='V'

WHERE A§CLGE='F' and CAW2009.MPCD01='014401' and CAW2009.MPCD02 like '9033030300%' and CAW2009.MPCDPI='BUD09' and CAW2009.MPTPRC='CAW' ORDER BY CAW2009.MPCD01,CAW2009.MPCD02

Add new attachment

Only authorized users are allowed to upload new attachments.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
rpgle
 XJAC05_SM.rpgle 112.3 kB 1 14-Apr-2010 14:30 MattiaRocchi SQLRPGLE per Web.up
« This particular version was published on 14-Apr-2010 14:30 by MattiaRocchi.