Sales Order Confirmation

CREATE VIEW [dbo].[vw_salesSalesConfirm]                    AS 
SELECT     'CUSTCONFIRMJOUR_CASHDISCCODE' =                 ccj.cashdisccode, 
           'CUSTCONFIRMJOUR_CASHDISCPERCENT' =              ccj.cashdiscpercent, 
           'CUSTCONFIRMJOUR_CONFIRMAMOUNT' =                ccj.confirmamount, 
           'CUSTCONFIRMJOUR_CONFIRMDATE' =                  ccj.confirmdate, 
           'CUSTCONFIRMJOUR_CONFIRMDOCNUM' =                ccj.confirmdocnum, 
           'CUSTCONFIRMJOUR_CONFIRMID' =                    ccj.confirmid, 
           'CUSTCONFIRMJOUR_COSTVALUE' =                    ccj.costvalue, 
           'CUSTCONFIRMJOUR_CREATEDDATETIME' =              ccj.createddatetime, 
           'CUSTCONFIRMJOUR_CURRENCYCODE' =                 ccj.currencycode, 
           'CUSTCONFIRMJOUR_CUSTGROUP' =                    ccj.custgroup, 
           'CUSTCONFIRMJOUR_CUSTOMERREF' =                  ccj.customerref, 
           'CUSTCONFIRMJOUR_CUSTOMSEXPORTORDER_IN' =        ccj.customsexportorder_in, 
           'CUSTCONFIRMJOUR_DATAAREAID' =                   ccj.dataareaid, 
           'CUSTCONFIRMJOUR_DEADLINE' =                     ccj.deadline, 
           'CUSTCONFIRMJOUR_DEFAULTDIMENSION' =             ccj.defaultdimension, 
           'CUSTCONFIRMJOUR_DELIVERYNAME' =                 ccj.deliveryname, 
           'CUSTCONFIRMJOUR_DELIVERYPOSTALADDRESS' =        ccj.deliverypostaladdress, 
           'CUSTCONFIRMJOUR_DLVMODE' =                      ccj.dlvmode, 
           'CUSTCONFIRMJOUR_DLVTERM' =                      ccj.dlvterm, 
           'CUSTCONFIRMJOUR_ENDDISC' =                      ccj.enddisc, 
           'CUSTCONFIRMJOUR_EXCHRATE' =                     ccj.exchrate, 
           'CUSTCONFIRMJOUR_EXCHRATESECONDARY' =            ccj.exchratesecondary, 
           'CUSTCONFIRMJOUR_FIXEDDUEDATE' =                 ccj.fixedduedate, 
           'CUSTCONFIRMJOUR_INCLTAX' =                      ccj.incltax, 
           'CUSTCONFIRMJOUR_INTERCOMPANYPOSTED' =           ccj.intercompanyposted, 
           'CUSTCONFIRMJOUR_INVOICEACCOUNT' =               ccj.invoiceaccount, 
           'CUSTCONFIRMJOUR_LANGUAGEID' =                   ccj.languageid, 
           'CUSTCONFIRMJOUR_ORDERACCOUNT' =                 ccj.orderaccount, 
           'CUSTCONFIRMJOUR_PARMID' =                       ccj.parmid, 
           'CUSTCONFIRMJOUR_PARTITION' =                    ccj.partition, 
           'CUSTCONFIRMJOUR_PAYMENT' =                      ccj.payment, 
           'CUSTCONFIRMJOUR_PURCHASEORDER' =                ccj.purchaseorder, 
           'CUSTCONFIRMJOUR_QTY' =                          ccj.qty, 
           'CUSTCONFIRMJOUR_RECID' =                        ccj.recid, 
           'CUSTCONFIRMJOUR_RECVERSION' =                   ccj.recversion, 
           'CUSTCONFIRMJOUR_ROUNDOFF' =                     ccj.roundoff, 
           'CUSTCONFIRMJOUR_SALESBALANCE' =                 ccj.salesbalance, 
           'CUSTCONFIRMJOUR_SALESID' =                      ccj.salesid, 
           'CUSTCONFIRMJOUR_SUMLINEDISC' =                  ccj.sumlinedisc, 
           'CUSTCONFIRMJOUR_SUMMARKUP' =                    ccj.summarkup, 
           'CUSTCONFIRMJOUR_SUMTAX' =                       ccj.sumtax, 
           'CUSTCONFIRMJOUR_TRIANGULATION' =                ccj.triangulation, 
           'CUSTCONFIRMJOUR_VOLUME' =                       ccj.volume, 
           'CUSTCONFIRMJOUR_WEIGHT' =                       ccj.weight, 
           'CUSTCONFIRMJOUR_WORKERSALESTAKER' =             ccj.workersalestaker, 
           'CUSTCONFIRMSALESLINK_CONFIRMDATE' =             ccsl.confirmdate, 
           'CUSTCONFIRMSALESLINK_CONFIRMID' =               ccsl.confirmid, 
           'CUSTCONFIRMSALESLINK_DATAAREAID' =              ccsl.dataareaid, 
           'CUSTCONFIRMSALESLINK_DELIVERYNAME' =            ccsl.deliveryname, 
           'CUSTCONFIRMSALESLINK_DELIVERYPOSTALADDRESS' =   ccsl.deliverypostaladdress, 
           'CUSTCONFIRMSALESLINK_INVOICEACCOUNT' =          ccsl.invoiceaccount, 
           'CUSTCONFIRMSALESLINK_ORDERACCOUNT' =            ccsl.orderaccount, 
           'CUSTCONFIRMSALESLINK_ORIGSALESID' =             ccsl.origsalesid, 
           'CUSTCONFIRMSALESLINK_PARMID' =                  ccsl.parmid, 
           'CUSTCONFIRMSALESLINK_PARTITION' =               ccsl.partition, 
           'CUSTCONFIRMSALESLINK_RECID' =                   ccsl.recid, 
           'CUSTCONFIRMSALESLINK_RECVERSION' =              ccsl.recversion, 
           'CUSTCONFIRMSALESLINK_SALESID' =                 ccsl.salesid, 
           'CUSTCONFIRMTRANS_CONFIRMDATE' =                 cct.confirmdate, 
           'CUSTCONFIRMTRANS_CONFIRMID' =                   cct.confirmid, 
           'CUSTCONFIRMTRANS_CURRENCYCODE' =                cct.currencycode, 
           'CUSTCONFIRMTRANS_DATAAREAID' =                  cct.dataareaid, 
           'CUSTCONFIRMTRANS_DEFAULTDIMENSION' =            cct.defaultdimension, 
           'CUSTCONFIRMTRANS_DISCAMOUNT' =                  cct.discamount, 
           'CUSTCONFIRMTRANS_DISCPERCENT' =                 cct.discpercent, 
           'CUSTCONFIRMTRANS_DLVDATE' =                     cct.dlvdate, 
           'CUSTCONFIRMTRANS_DLVTERM' =                     cct.dlvterm, 
           'CUSTCONFIRMTRANS_EXTERNALITEMID' =              cct.externalitemid, 
           'CUSTCONFIRMTRANS_INVENTDIMID' =                 cct.inventdimid, 
           'CUSTCONFIRMTRANS_INVENTQTY' =                   cct.inventqty, 
           'CUSTCONFIRMTRANS_INVENTTRANSID' =               cct.inventtransid, 
           'CUSTCONFIRMTRANS_ITEMID' =                      cct.itemid, 
           'CUSTCONFIRMTRANS_LINEAMOUNT' =                  cct.lineamount, 
           'CUSTCONFIRMTRANS_LINEAMOUNTTAX' =               cct.lineamounttax, 
           'CUSTCONFIRMTRANS_LINEDISC' =                    cct.linedisc, 
           'CUSTCONFIRMTRANS_LINEHEADER' =                  cct.lineheader, 
           'CUSTCONFIRMTRANS_LINENUM' =                     cct.linenum, 
           'CUSTCONFIRMTRANS_LINEPERCENT' =                 cct.linepercent, 
           'CUSTCONFIRMTRANS_MULTILNDISC' =                 cct.multilndisc, 
           'CUSTCONFIRMTRANS_MULTILNPERCENT' =              cct.multilnpercent, 
           'CUSTCONFIRMTRANS_NAME' =                        cct.NAME, 
           'CUSTCONFIRMTRANS_ORIGSALESID' =                 cct.origsalesid, 
           'CUSTCONFIRMTRANS_PARTITION' =                   cct.partition, 
           'CUSTCONFIRMTRANS_PDSCWQTY' =                    cct.pdscwqty, 
           'CUSTCONFIRMTRANS_PRICEUNIT' =                   cct.priceunit, 
           'CUSTCONFIRMTRANS_QTY' =                         cct.qty, 
           'CUSTCONFIRMTRANS_RECID' =                       cct.recid, 
           'CUSTCONFIRMTRANS_RECVERSION' =                  cct.recversion, 
           'CUSTCONFIRMTRANS_SALESCATEGORY' =               cct.salescategory, 
           'CUSTCONFIRMTRANS_SALESGROUP' =                  cct.salesgroup, 
           'CUSTCONFIRMTRANS_SALESID' =                     cct.salesid, 
           'CUSTCONFIRMTRANS_SALESMARKUP' =                 cct.salesmarkup, 
           'CUSTCONFIRMTRANS_SALESPRICE' =                  cct.salesprice, 
           'CUSTCONFIRMTRANS_SALESUNIT' =                   cct.salesunit, 
           'CUSTCONFIRMTRANS_STOCKEDPRODUCT' =              cct.stockedproduct, 
           'CUSTCONFIRMTRANS_TAXAMOUNT' =                   cct.taxamount, 
           'CUSTCONFIRMTRANS_TAXGROUP' =                    cct.taxgroup, 
           'CUSTCONFIRMTRANS_TAXITEMGROUP' =                cct.taxitemgroup, 
           'CUSTCONFIRMTRANS_TAXWRITECODE' =                cct.taxwritecode, 
           'INVENTDIM_CONFIGID' =                           id.configid, 
           'INVENTDIM_CREATEDDATETIME' =                    id.createddatetime, 
           'INVENTDIM_DATAAREAID' =                         id.dataareaid, 
           'INVENTDIM_INVENTBATCHID' =                      id.inventbatchid, 
           'INVENTDIM_INVENTCOLORID' =                      id.inventcolorid, 
           'INVENTDIM_INVENTDIMID' =                        id.inventdimid, 
           'INVENTDIM_INVENTGTDID_RU' =                     id.inventgtdid_ru, 
           'INVENTDIM_INVENTLOCATIONID' =                   id.inventlocationid, 
           'INVENTDIM_INVENTOWNERID_RU' =                   id.inventownerid_ru, 
           'INVENTDIM_INVENTPROFILEID_RU' =                 id.inventprofileid_ru, 
           'INVENTDIM_INVENTSERIALID' =                     id.inventserialid, 
           'INVENTDIM_INVENTSITEID' =                       id.inventsiteid, 
           'INVENTDIM_INVENTSIZEID' =                       id.inventsizeid, 
           'INVENTDIM_INVENTSTATUSID' =                     id.inventstatusid, 
           'INVENTDIM_INVENTSTYLEID' =                      id.inventstyleid, 
           'INVENTDIM_LICENSEPLATEID' =                     id.licenseplateid, 
           'INVENTDIM_MODIFIEDBY' =                         id.modifiedby, 
           'INVENTDIM_MODIFIEDDATETIME' =                   id.modifieddatetime, 
           'INVENTDIM_PARTITION' =                          id.partition, 
           'INVENTDIM_RECID' =                              id.recid, 
           'INVENTDIM_RECVERSION' =                         id.recversion, 
           'INVENTDIM_SHA1HASH' =                           id.sha1hash, 
           'INVENTDIM_WMSLOCATIONID' =                      id.wmslocationid, 
           'INVENTDIM_WMSPALLETID' =                        id.wmspalletid, 
           'ECORESCATEGORY_CATEGORYHIERARCHY' =             erc.categoryhierarchy, 
           'ECORESCATEGORY_CHANGESTATUS' =                  erc.changestatus, 
           'ECORESCATEGORY_CODE' =                          erc.code, 
           'ECORESCATEGORY_CREATEDBY' =                     erc.createdby, 
           'ECORESCATEGORY_CREATEDDATETIME' =               erc.createddatetime, 
           'ECORESCATEGORY_DEFAULTPROJECTGLOBALCATEGORY' =  erc.defaultprojectglobalcategory,
           'ECORESCATEGORY_DEFAULTTHRESHOLD_PSN' =          erc.defaultthreshold_psn, 
           'ECORESCATEGORY_INSTANCERELATIONTYPE' =          erc.instancerelationtype, 
           'ECORESCATEGORY_ISACTIVE' =                      erc.isactive, 
           'ECORESCATEGORY_ISCATEGORYATTRIBUTESINHERITED' = erc.iscategoryattributesinherited,
           'ECORESCATEGORY_ISTANGIBLE' =                    erc.istangible, 
           'ECORESCATEGORY_LEVEL_' =                        erc.level_, 
           'ECORESCATEGORY_MODIFIEDBY' =                    erc.modifiedby, 
           'ECORESCATEGORY_MODIFIEDDATETIME' =              erc.modifieddatetime, 
           'ECORESCATEGORY_NAME' =                          erc.NAME, 
           'ECORESCATEGORY_NESTEDSETLEFT' =                 erc.nestedsetleft, 
           'ECORESCATEGORY_NESTEDSETRIGHT' =                erc.nestedsetright, 
           'ECORESCATEGORY_PARENTCATEGORY' =                erc.parentcategory, 
           'ECORESCATEGORY_PARTITION' =                     erc.partition, 
           'ECORESCATEGORY_PKWIUCODE' =                     erc.pkwiucode, 
           'ECORESCATEGORY_RECID' =                         erc.recid, 
           'ECORESCATEGORY_RECVERSION' =                    erc.recversion, 
           'ECORESCATEGORY_RELATIONTYPE' =                  erc.relationtype, 
           'ECORESCATEGORY_REUSEENABLED' =                  erc.reuseenabled 
FROM       custconfirmjour ccj 
INNER JOIN custconfirmsaleslink ccsl 
ON         ccsl.salesid = ccj.salesid 
AND        ccsl.confirmid = ccj.confirmid 
AND        ccsl.confirmdate = ccj.confirmdate 
AND        ccsl.dataareaid = ccj.dataareaid 
AND        ccsl.partition = ccsl.partition 
INNER JOIN custconfirmtrans cct 
ON         cct.salesid = ccj.salesid 
AND        cct.confirmid = ccj.confirmid 
AND        cct.confirmdate = ccj.confirmdate 
AND        cct.dataareaid = ccj.dataareaid 
AND        cct.partition = ccj.partition 
INNER JOIN inventdim id 
ON         id.inventdimid = cct.inventdimid 
AND        id.dataareaid = cct.dataareaid 
AND        id.partition = cct.partition 
LEFT JOIN  ecorescategory erc 
ON         erc.recid = cct.salescategory 
AND        erc.partition = ccj.partitiongo