Inventory Transfer Journals ( from Transfer Orders )

CREATE VIEW [dbo].[vw_inventInventTransferJournal] 
AS 
  SELECT 'INVENTTRANSFERJOUR_AUTORECEIVEQTY' = 
         itj.autoreceiveqty, 
         'INVENTTRANSFERJOUR_BILLOFLADINGID_RU' = 
         itj.billofladingid_ru, 
         'INVENTTRANSFERJOUR_CARGODESCRIPTION_RU' = 
         itj.cargodescription_ru, 
         'INVENTTRANSFERJOUR_CARGOPACKING_RU' = 
            itj.cargopacking_ru, 
         'INVENTTRANSFERJOUR_CARRIERCODE_RU' = 
            itj.carriercode_ru, 
         'INVENTTRANSFERJOUR_CARRIERTYPE_RU' = 
            itj.carriertype_ru, 
         'INVENTTRANSFERJOUR_CREATEDBY' = itj.createdby, 
         'INVENTTRANSFERJOUR_CREATEDDATETIME' = 
            itj.createddatetime, 
         'INVENTTRANSFERJOUR_CURRENCYCODE_RU' = 
            itj.currencycode_ru, 
         'INVENTTRANSFERJOUR_DATAAREAID' = itj.dataareaid, 
         'INVENTTRANSFERJOUR_DELIVERYDATE_RU' = 
            itj.deliverydate_ru, 
         'INVENTTRANSFERJOUR_DLVMODEID' = itj.dlvmodeid, 
         'INVENTTRANSFERJOUR_DLVTERMID' = itj.dlvtermid, 
         'INVENTTRANSFERJOUR_DOCUMENTSTATUS_LT' = 
         itj.documentstatus_lt, 
         'INVENTTRANSFERJOUR_DRIVERCONTACT_RU' = 
            itj.drivercontact_ru, 
         'INVENTTRANSFERJOUR_DRIVERNAME_RU' = 
            itj.drivername_ru, 
         'INVENTTRANSFERJOUR_DRIVINGLICENSENUM_RU' = 
         itj.drivinglicensenum_ru, 
         'INVENTTRANSFERJOUR_FREIGHTSLIPTYPE' = 
            itj.freightsliptype, 
         'INVENTTRANSFERJOUR_FREIGHTZONEID' = 
            itj.freightzoneid, 
         'INVENTTRANSFERJOUR_FROMADDRESSNAME' = 
            itj.fromaddressname, 
         'INVENTTRANSFERJOUR_FROMCONTACTPERSON' = 
         itj.fromcontactperson, 
         'INVENTTRANSFERJOUR_FROMPOSTALADDRESS' = 
         itj.frompostaladdress, 
         'INVENTTRANSFERJOUR_INCLUDEPACKINGSLIPREGISTER_LT' 
         = 
         itj.includepackingslipregister_lt, 
         'INVENTTRANSFERJOUR_INTRASTATDISPATCH' = 
         itj.intrastatdispatch, 
         'INVENTTRANSFERJOUR_INVENTLOCATIONIDFROM' = 
         itj.inventlocationidfrom, 
         'INVENTTRANSFERJOUR_INVENTLOCATIONIDTO' = 
         itj.inventlocationidto, 
         'INVENTTRANSFERJOUR_INVENTLOCATIONIDTRANSIT' = 
         itj.inventlocationidtransit, 
         'INVENTTRANSFERJOUR_LADINGPOSTALADDRESS_RU' = 
         itj.ladingpostaladdress_ru, 
         'INVENTTRANSFERJOUR_LICENSECARDNUM_RU' = 
         itj.licensecardnum_ru, 
         'INVENTTRANSFERJOUR_LICENSECARDREGNUM_RU' = 
         itj.licensecardregnum_ru, 
         'INVENTTRANSFERJOUR_LICENSECARDSERIES_RU' = 
         itj.licensecardseries_ru, 
         'INVENTTRANSFERJOUR_LICENSECARDTYPE_RU' = 
         itj.licensecardtype_ru, 
         'INVENTTRANSFERJOUR_NUM_LT' = itj.num_lt, 
         'INVENTTRANSFERJOUR_NUMBERING_W' = itj.numbering_w, 
         'INVENTTRANSFERJOUR_OFFSESSIONID_RU' = 
            itj.offsessionid_ru, 
         'INVENTTRANSFERJOUR_PARTITION' = itj.partition, 
         'INVENTTRANSFERJOUR_PARTYACCOUNTNUM_RU' = 
         itj.partyaccountnum_ru, 
         'INVENTTRANSFERJOUR_PARTYAGREEMENTHEADEREXT_RU' = 
         itj.partyagreementheaderext_ru, 
         'INVENTTRANSFERJOUR_PRICEGROUPID_RU' = 
            itj.pricegroupid_ru, 
         'INVENTTRANSFERJOUR_REASONTABLEREF_BR' = 
         itj.reasontableref_br, 
         'INVENTTRANSFERJOUR_RECID' = itj.recid, 
         'INVENTTRANSFERJOUR_RECVERSION' = itj.recversion, 
         'INVENTTRANSFERJOUR_RETURN_RU' = itj.return_ru, 
         'INVENTTRANSFERJOUR_STORNO_RU' = itj.storno_ru, 
         'INVENTTRANSFERJOUR_TOADDRESSNAME' = 
            itj.toaddressname, 
         'INVENTTRANSFERJOUR_TOCONTACTPERSON' = 
            itj.tocontactperson, 
         'INVENTTRANSFERJOUR_TOPOSTALADDRESS' = 
            itj.topostaladdress, 
         'INVENTTRANSFERJOUR_TRACKINGID' = itj.trackingid, 
         'INVENTTRANSFERJOUR_TRANSDATE' = itj.transdate, 
         'INVENTTRANSFERJOUR_TRANSFERID' = itj.transferid, 
         'INVENTTRANSFERJOUR_TRANSFERTYPE_RU' = 
            itj.transfertype_ru, 
         'INVENTTRANSFERJOUR_TRANSPORTATIONTYPE_RU' = 
         itj.transportationtype_ru, 
         'INVENTTRANSFERJOUR_TRANSPORTINVOICETYPE_RU' = 
         itj.transportinvoicetype_ru, 
         'INVENTTRANSFERJOUR_UNLADINGPOSTALADDRESS_RU' = 
         itj.unladingpostaladdress_ru, 
         'INVENTTRANSFERJOUR_UPDATEDBYWORKER' = 
            itj.updatedbyworker, 
         'INVENTTRANSFERJOUR_UPDATETYPE' = itj.updatetype, 
         'INVENTTRANSFERJOUR_VEHICLEMODEL_RU' = 
            itj.vehiclemodel_ru, 
         'INVENTTRANSFERJOUR_VEHICLEPLATENUM_RU' = 
         itj.vehicleplatenum_ru, 
         'INVENTTRANSFERJOUR_VOUCHERID' = itj.voucherid, 
         'INVENTTRANSFERJOUR_WAYBILLNUM_RU' = 
            itj.waybillnum_ru, 
         'addressFrom_ADDRESS' = addressFrom.address, 
         'addressFrom_APARTMENT_RU' = 
            addressFrom.apartment_ru, 
         'addressFrom_BUILDING_RU' = 
         addressFrom.building_ru, 
         'addressFrom_BUILDINGCOMPLIMENT' = 
         addressFrom.buildingcompliment, 
         'addressFrom_CITY' = addressFrom.city, 
         'addressFrom_CITYKANA_JP' = 
         addressFrom.citykana_jp, 
         'addressFrom_CITYRECID' = 
         addressFrom.cityrecid, 
         'addressFrom_COUNTRYREGIONID' = 
            addressFrom.countryregionid, 
         'addressFrom_COUNTY' = addressFrom.county, 
         'addressFrom_DISTRICT' = addressFrom.district, 
         'addressFrom_DISTRICTNAME' = 
            addressFrom.districtname, 
         'addressFrom_FLATID_RU' = 
         addressFrom.flatid_ru, 
         'addressFrom_HOUSEID_RU' = addressFrom.houseid_ru, 
         'addressFrom_ISPRIVATE' = addressFrom.isprivate, 
         'addressFrom_LATITUDE' = addressFrom.latitude, 
         'addressFrom_LOCATION' = addressFrom.location, 
         'addressFrom_LONGITUDE' = addressFrom.longitude, 
         'addressFrom_MODIFIEDBY' = addressFrom.modifiedby, 
         'addressFrom_MODIFIEDDATETIME' = 
         addressFrom.modifieddatetime, 
         'addressFrom_PARTITION' = addressFrom.partition, 
         'addressFrom_POSTBOX' = addressFrom.postbox, 
         'addressFrom_PRIVATEFORPARTY' = 
            addressFrom.privateforparty, 
         'addressFrom_RECID' = addressFrom.recid, 
         'addressFrom_RECVERSION' = addressFrom.recversion, 
         'addressFrom_STATE' = addressFrom.state, 
         'addressFrom_STREET' = addressFrom.street, 
         'addressFrom_STREETID_RU' = 
         addressFrom.streetid_ru, 
         'addressFrom_STREETKANA_JP' = 
            addressFrom.streetkana_jp, 
         'addressFrom_STREETNUMBER' = 
            addressFrom.streetnumber, 
         'addressFrom_TIMEZONE' = 
         addressFrom.timezone, 
         'addressFrom_VALIDFROM' = addressFrom.validfrom, 
         'addressFrom_VALIDFROMTZID' = 
            addressFrom.validfromtzid, 
         'addressFrom_VALIDTO' = addressFrom.validto, 
         'addressFrom_VALIDTOTZID' = 
         addressFrom.validtotzid, 
         'addressFrom_ZIPCODE' = 
         addressFrom.zipcode, 
         'addressFrom_ZIPCODERECID' = 
            addressFrom.zipcoderecid, 
         'addressTo_ADDRESS' = 
            addressTo.address, 
         'addressTo_APARTMENT_RU' = addressTo.apartment_ru, 
         'addressTo_BUILDING_RU' = addressTo.building_ru, 
         'addressTo_BUILDINGCOMPLIMENT' = 
         addressTo.buildingcompliment, 
         'addressTo_CITY' = addressTo.city, 
         'addressTo_CITYKANA_JP' = addressTo.citykana_jp, 
         'addressTo_CITYRECID' = addressTo.cityrecid, 
         'addressTo_COUNTRYREGIONID' = 
            addressTo.countryregionid, 
         'addressTo_COUNTY' = addressTo.county, 
         'addressTo_DISTRICT' = addressTo.district, 
         'addressTo_DISTRICTNAME' = addressTo.districtname, 
         'addressTo_FLATID_RU' = addressTo.flatid_ru, 
         'addressTo_HOUSEID_RU' = addressTo.houseid_ru, 
         'addressTo_ISPRIVATE' = addressTo.isprivate, 
         'addressTo_LATITUDE' = addressTo.latitude, 
         'addressTo_LOCATION' = addressTo.location, 
         'addressTo_LONGITUDE' = addressTo.longitude, 
         'addressTo_MODIFIEDBY' = addressTo.modifiedby, 
         'addressTo_MODIFIEDDATETIME' = 
            addressTo.modifieddatetime, 
         'addressTo_PARTITION' = addressTo.partition, 
         'addressTo_POSTBOX' = addressTo.postbox, 
         'addressTo_PRIVATEFORPARTY' = 
            addressTo.privateforparty, 
         'addressTo_RECID' = addressTo.recid, 
         'addressTo_RECVERSION' = addressTo.recversion, 
         'addressTo_STATE' = addressTo.state, 
         'addressTo_STREET' = addressTo.street, 
         'addressTo_STREETID_RU' = addressTo.streetid_ru, 
         'addressTo_STREETKANA_JP' = 
         addressTo.streetkana_jp, 
         'addressTo_STREETNUMBER' = 
         addressTo.streetnumber, 
         'addressTo_TIMEZONE' = addressTo.timezone, 
         'addressTo_VALIDFROM' = addressTo.validfrom, 
         'addressTo_VALIDFROMTZID' = 
         addressTo.validfromtzid, 
         'addressTo_VALIDTO' = 
         addressTo.validto, 
         'addressTo_VALIDTOTZID' = addressTo.validtotzid, 
         'addressTo_ZIPCODE' = addressTo.zipcode, 
         'addressTo_ZIPCODERECID' = addressTo.zipcoderecid, 
         'worker_NAME' = worker.NAME, 
         'worker_PARTITION' = worker.partition, 
         'worker_PARTITION#2' = worker.partition#2, 
         'worker_PARTYNUMBER' = worker.partynumber, 
         'worker_PERSON' = worker.person, 
         'worker_RECID' = worker.recid, 
         'INVENTTRANSFERJOURLINE_AMOUNTVALUE' = 
            itjl.amountvalue, 
         'INVENTTRANSFERJOURLINE_DATAAREAID' = 
            itjl.dataareaid, 
         'INVENTTRANSFERJOURLINE_EXCISEAMT_IN' = 
         itjl.exciseamt_in, 
         'INVENTTRANSFERJOURLINE_INTRACODE' = 
         itjl.intracode, 
'INVENTTRANSFERJOURLINE_INTRASTATDISPATCH' = 
itjl.intrastatdispatch, 
'INVENTTRANSFERJOURLINE_INTRASTATFULFILLMENTDATE_HU' 
   = 
itjl.intrastatfulfillmentdate_hu, 
'INVENTTRANSFERJOURLINE_INTRASTATSPECMOVE_CZ' = 
itjl.intrastatspecmove_cz, 
'INVENTTRANSFERJOURLINE_INVENTDIMID' = 
   itjl.inventdimid, 
'INVENTTRANSFERJOURLINE_INVENTTRANSID' = 
   itjl.inventtransid, 
'INVENTTRANSFERJOURLINE_INVENTTRANSIDTRANSIT' = 
itjl.inventtransidtransit, 
'INVENTTRANSFERJOURLINE_ITEMID' = itjl.itemid, 
'INVENTTRANSFERJOURLINE_LINEAMOUNTRECEIVED_RU' = 
itjl.lineamountreceived_ru, 
'INVENTTRANSFERJOURLINE_LINEAMOUNTSHIPPED_RU' = 
itjl.lineamountshipped_ru, 
'INVENTTRANSFERJOURLINE_LINENUM' = itjl.linenum, 
'INVENTTRANSFERJOURLINE_NETAMTRECEIVE_IN' = 
itjl.netamtreceive_in, 
'INVENTTRANSFERJOURLINE_NETAMTSHIP_IN' = 
   itjl.netamtship_in, 
'INVENTTRANSFERJOURLINE_ORIGCOUNTRYREGIONID' = 
itjl.origcountryregionid, 
'INVENTTRANSFERJOURLINE_ORIGCOUNTYID' = 
   itjl.origcountyid, 
'INVENTTRANSFERJOURLINE_ORIGSTATEID' = 
   itjl.origstateid, 
'INVENTTRANSFERJOURLINE_PARTITION' = itjl.partition, 
'INVENTTRANSFERJOURLINE_PDSCWQTYRECEIVED' = 
itjl.pdscwqtyreceived, 
'INVENTTRANSFERJOURLINE_PDSCWQTYSCRAPPED' = 
itjl.pdscwqtyscrapped, 
'INVENTTRANSFERJOURLINE_PDSCWQTYSHIPPED' = 
itjl.pdscwqtyshipped, 
'INVENTTRANSFERJOURLINE_PORT' = itjl.port, 
'INVENTTRANSFERJOURLINE_PRICE_RU' = itjl.price_ru, 
'INVENTTRANSFERJOURLINE_PRICEUNIT_RU' = 
   itjl.priceunit_ru, 
'INVENTTRANSFERJOURLINE_QTYRECEIVED' = 
   itjl.qtyreceived, 
'INVENTTRANSFERJOURLINE_QTYSCRAPPED' = 
   itjl.qtyscrapped, 
'INVENTTRANSFERJOURLINE_QTYSHIPPED' = 
   itjl.qtyshipped, 
'INVENTTRANSFERJOURLINE_RECID' = 
   itjl.recid, 
'INVENTTRANSFERJOURLINE_RECVERSION' = 
   itjl.recversion, 
'INVENTTRANSFERJOURLINE_SALESTAXAMT_IN' 
   = 
itjl.salestaxamt_in, 
'INVENTTRANSFERJOURLINE_STATISTICALVALUE' = 
itjl.statisticalvalue, 
'INVENTTRANSFERJOURLINE_STATPROCID' = 
   itjl.statprocid, 
'INVENTTRANSFERJOURLINE_TRANSACTIONCODE' = 
itjl.transactioncode, 
'INVENTTRANSFERJOURLINE_TRANSDATE' = itjl.transdate, 
'INVENTTRANSFERJOURLINE_TRANSFERID' = itjl.transferid, 
'INVENTTRANSFERJOURLINE_TRANSPORT' = itjl.transport, 
'INVENTTRANSFERJOURLINE_UNITID' = itjl.unitid, 
'INVENTTRANSFERJOURLINE_UNITPRICE_IN' = itjl.unitprice_in, 
'INVENTTRANSFERJOURLINE_VATAMT_IN' = itjl.vatamt_in, 
'INVENTTRANSFERJOURLINE_VOUCHERID' = itjl.voucherid, 
'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 
FROM   inventtransferjour itj 
INNER JOIN logisticspostaladdress addressFrom 
        ON addressFrom.recid = itj.frompostaladdress 
           AND addressfrom.partition = itj.partition 
INNER JOIN logisticspostaladdress addressTo 
        ON addressTo.recid = itj.topostaladdress 
           AND addressTo.partition = itj.partition 
LEFT JOIN hcmworkerdetailsview worker 
       ON worker.recid = itj.updatedbyworker 
          AND worker.partition = itj.partition 
INNER JOIN inventtransferjourline itjl 
        ON itjl.voucherid = itj.voucherid 
           AND itjl.transferid = itj.transferid 
           AND itjl.dataareaid = itj.dataareaid 
           AND itjl.partition = itj.partition 
INNER JOIN inventdim id 
        ON id.inventdimid = itjl.inventdimid 
           AND id.dataareaid = itj.dataareaid 
           AND id.partition = itj.partition 

go