Purchase Order Details with Lines and extended Line info

Purchase Order Details with Lines and extended Line info

Submitted by Nathan Clouse on 09/18/15.

Provides tons of data on Purchase Orders, Purchase Lines, items on lines including inventory dimensions plus other useful bits of info. This depends on a view already posted.

AX Version: 

Module: 

CREATE VIEW [dbo].[vw_purchPurchOrderDetail] 
AS 
  SELECT 'PURCHTABLE_ACCOUNTINGDATE' = pt.accountingdate, 
         'PURCHTABLE_ACCOUNTINGDISTRIBUTIONTEMPLATE' = 
         pt.accountingdistributiontemplate, 
         'PURCHTABLE_ADDRESSREFRECID' = pt.addressrefrecid, 
         'PURCHTABLE_ADDRESSREFTABLEID' = 
            pt.addressreftableid, 
         'PURCHTABLE_AUTOSUMMARYMODULETYPE' = 
         pt.autosummarymoduletype, 
         'PURCHTABLE_AVAILSALESDATE' = pt.availsalesdate, 
         'PURCHTABLE_BANKCENTRALBANKPURPOSECODE' = 
         pt.bankcentralbankpurposecode, 
         'PURCHTABLE_BANKCENTRALBANKPURPOSETEXT' = 
         pt.bankcentralbankpurposetext, 
         'PURCHTABLE_BANKDOCUMENTTYPE' = 
         pt.bankdocumenttype, 
         'PURCHTABLE_CASHDISC' = pt.cashdisc, 
         'PURCHTABLE_CASHDISCPERCENT' = pt.cashdiscpercent, 
         'PURCHTABLE_CHANGEREQUESTREQUIRED' = 
         pt.changerequestrequired, 
         'PURCHTABLE_CONFIRMEDDLV' = pt.confirmeddlv, 
         'PURCHTABLE_CONFIRMEDDLVEARLIEST' = 
            pt.confirmeddlvearliest, 
         'PURCHTABLE_CONFIRMINGPO' = pt.confirmingpo, 
         'PURCHTABLE_CONSTARGET_JP' = pt.constarget_jp, 
         'PURCHTABLE_CONTACTPERSONID' = pt.contactpersonid, 
         'PURCHTABLE_CONTRACTNUM_SA' = pt.contractnum_sa, 
         'PURCHTABLE_COUNTYORIGDEST' = pt.countyorigdest, 
         'PURCHTABLE_COVSTATUS' = pt.covstatus, 
         'PURCHTABLE_CREATEDBY' = pt.createdby, 
         'PURCHTABLE_CREATEDDATETIME' = pt.createddatetime, 
         'PURCHTABLE_CROSSDOCKINGDATE' = 
         pt.crossdockingdate, 
         'PURCHTABLE_CURRENCYCODE' = 
         pt.currencycode, 
         'PURCHTABLE_DATAAREAID' = pt.dataareaid, 
         'PURCHTABLE_DEFAULTDIMENSION' = 
         pt.defaultdimension, 
         'PURCHTABLE_DELIVERYDATE' = 
         pt.deliverydate, 
         'PURCHTABLE_DELIVERYNAME' = pt.deliveryname, 
         'PURCHTABLE_DELIVERYPOSTALADDRESS' = 
         pt.deliverypostaladdress, 
         'PURCHTABLE_DELIVERYTYPE' = pt.deliverytype, 
         'PURCHTABLE_DISCPERCENT' = pt.discpercent, 
         'PURCHTABLE_DLVMODE' = pt.dlvmode, 
         'PURCHTABLE_DLVTERM' = pt.dlvterm, 
         'PURCHTABLE_DOCUMENTSTATE' = pt.documentstate, 
         'PURCHTABLE_DOCUMENTSTATUS' = pt.documentstatus, 
         'PURCHTABLE_EMAIL' = pt.email, 
         'PURCHTABLE_ENDDISC' = pt.enddisc, 
         'PURCHTABLE_ENTERPRISENUMBER' = 
         pt.enterprisenumber, 
         'PURCHTABLE_EXCHANGERATEDATE' = 
         pt.exchangeratedate, 
         'PURCHTABLE_FINALIZECLOSINGDATE' = 
            pt.finalizeclosingdate, 
         'PURCHTABLE_FIXEDDUEDATE' = pt.fixedduedate, 
         'PURCHTABLE_FIXEDEXCHRATE' = pt.fixedexchrate, 
         'PURCHTABLE_FREIGHTSLIPTYPE' = pt.freightsliptype, 
         'PURCHTABLE_FREIGHTZONE' = pt.freightzone, 
         'PURCHTABLE_FSHAUTOCREATED' = pt.fshautocreated, 
         'PURCHTABLE_INCLTAX' = pt.incltax, 
         'PURCHTABLE_INTERCOMPANYALLOWINDIRECTCREATION' = 
         pt.intercompanyallowindirectcreation, 
         'PURCHTABLE_INTERCOMPANYCOMPANYID' = 
         pt.intercompanycompanyid, 
         'PURCHTABLE_INTERCOMPANYCUSTPURCHORDERFORMNUM' = 
         pt.intercompanycustpurchorderformnum, 
         'PURCHTABLE_INTERCOMPANYDIRECTDELIVERY' = 
         pt.intercompanydirectdelivery, 
         'PURCHTABLE_INTERCOMPANYORDER' = 
            pt.intercompanyorder, 
         'PURCHTABLE_INTERCOMPANYORIGIN' = 
            pt.intercompanyorigin, 
         'PURCHTABLE_INTERCOMPANYORIGINALCUSTACCOUNT' = 
         pt.intercompanyoriginalcustaccount, 
         'PURCHTABLE_INTERCOMPANYORIGINALSALESID' = 
         pt.intercompanyoriginalsalesid, 
         'PURCHTABLE_INTERCOMPANYSALESID' = 
            pt.intercompanysalesid, 
         'PURCHTABLE_INTRASTATADDVALUE_LV' = 
            pt.intrastataddvalue_lv, 
         'PURCHTABLE_INTRASTATFULFILLMENTDATE_HU' = 
         pt.intrastatfulfillmentdate_hu, 
         'PURCHTABLE_INVENTLOCATIONID' = 
         pt.inventlocationid, 
         'PURCHTABLE_INVENTSITEID' = 
         pt.inventsiteid, 
         'PURCHTABLE_INVOICEACCOUNT' = pt.invoiceaccount, 
         'PURCHTABLE_INVOICEREGISTER_LT' = 
            pt.invoiceregister_lt, 
         'PURCHTABLE_ISENCUMBRANCEREQUIRED' = 
         pt.isencumbrancerequired, 
         'PURCHTABLE_ISMODIFIED' = pt.ismodified, 
         'PURCHTABLE_ITEMBUYERGROUPID' = 
         pt.itembuyergroupid, 
         'PURCHTABLE_LANGUAGEID' = pt.languageid, 
         'PURCHTABLE_LINEDISC' = pt.linedisc, 
         'PURCHTABLE_LISTCODE' = pt.listcode, 
         'PURCHTABLE_LOCALDELIVERYDATE' = 
            pt.localdeliverydate, 
         'PURCHTABLE_MANUALENTRYCHANGEPOLICY' = 
         pt.manualentrychangepolicy, 
         'PURCHTABLE_MARKUPGROUP' = pt.markupgroup, 
         'PURCHTABLE_MATCHINGAGREEMENT' = 
            pt.matchingagreement, 
         'PURCHTABLE_MCRDROPSHIPMENT' = 
            pt.mcrdropshipment, 
         'PURCHTABLE_MULTILINEDISC' = pt.multilinedisc, 
         'PURCHTABLE_NUMBERSEQUENCEGROUP' = 
            pt.numbersequencegroup, 
         'PURCHTABLE_ONETIMESUPPLIER' = pt.onetimesupplier, 
         'PURCHTABLE_ONETIMEVENDOR' = pt.onetimevendor, 
         'PURCHTABLE_ORDERACCOUNT' = pt.orderaccount, 
         'PURCHTABLE_PACKINGSLIPREGISTER_LT' = 
         pt.packingslipregister_lt, 
         'PURCHTABLE_PARTITION' = pt.partition, 
         'PURCHTABLE_PAYMENT' = pt.payment, 
         'PURCHTABLE_PAYMENTSCHED' = pt.paymentsched, 
         'PURCHTABLE_PAYMMODE' = pt.paymmode, 
         'PURCHTABLE_PAYMSPEC' = pt.paymspec, 
         'PURCHTABLE_PORT' = pt.port, 
         'PURCHTABLE_POSTINGPROFILE' = pt.postingprofile, 
         'PURCHTABLE_PRICEGROUPID' = pt.pricegroupid, 
         'PURCHTABLE_PROJID' = pt.projid, 
         'PURCHTABLE_PURCHASETYPE' = pt.purchasetype, 
         'PURCHTABLE_PURCHID' = pt.purchid, 
         'PURCHTABLE_PURCHNAME' = pt.purchname, 
         'PURCHTABLE_PURCHPOOLID' = pt.purchpoolid, 
         'PURCHTABLE_PURCHSTATUS' = pt.purchstatus, 
         'PURCHTABLE_REASONTABLEREF' = pt.reasontableref, 
         'PURCHTABLE_RECID' = pt.recid, 
         'PURCHTABLE_RECVERSION' = pt.recversion, 
         'PURCHTABLE_REPLENISHMENTLOCATION' = 
         pt.replenishmentlocation, 
         'PURCHTABLE_REQATTENTION' = pt.reqattention, 
         'PURCHTABLE_REQUESTER' = pt.requester, 
         'PURCHTABLE_RETAILCONCESSIONPURCH' = 
         pt.retailconcessionpurch, 
         'PURCHTABLE_RETAILDRIVERDETAILS' = 
            pt.retaildriverdetails, 
         'PURCHTABLE_RETAILRETAILSTATUSTYPE' = 
         pt.retailretailstatustype, 
         'PURCHTABLE_RETURNITEMNUM' = pt.returnitemnum, 
         'PURCHTABLE_RETURNREASONCODEID' = 
            pt.returnreasoncodeid, 
         'PURCHTABLE_RETURNREPLACEMENTCREATED' = 
         pt.returnreplacementcreated, 
         'PURCHTABLE_SERVICEADDRESS' = pt.serviceaddress, 
         'PURCHTABLE_SERVICECATEGORY' = pt.servicecategory, 
         'PURCHTABLE_SERVICEDATE' = pt.servicedate, 
         'PURCHTABLE_SERVICENAME' = pt.servicename, 
         'PURCHTABLE_SETTLEVOUCHER' = pt.settlevoucher, 
         'PURCHTABLE_SOURCEDOCUMENTHEADER' = 
            pt.sourcedocumentheader, 
         'PURCHTABLE_SOURCEDOCUMENTLINE' = 
            pt.sourcedocumentline, 
         'PURCHTABLE_STATPROCID' = pt.statprocid, 
         'PURCHTABLE_SYSTEMENTRYCHANGEPOLICY' = 
         pt.systementrychangepolicy, 
         'PURCHTABLE_SYSTEMENTRYSOURCE' = 
            pt.systementrysource, 
         'PURCHTABLE_TAMVENDREBATEGROUPID' = 
            pt.tamvendrebategroupid, 
         'PURCHTABLE_TAXGROUP' = pt.taxgroup, 
         'PURCHTABLE_TAXPERIODPAYMENTCODE_PL' = 
         pt.taxperiodpaymentcode_pl, 
         'PURCHTABLE_TRANSACTIONCODE' = pt.transactioncode, 
         'PURCHTABLE_TRANSPORT' = pt.transport, 
         'PURCHTABLE_TRANSPORTATIONDOCUMENT' = 
         pt.transportationdocument, 
         'PURCHTABLE_UNITEDVATINVOICE_LT' = 
            pt.unitedvatinvoice_lt, 
         'PURCHTABLE_URL' = pt.url, 
         'PURCHTABLE_VATNUM' = pt.vatnum, 
         'PURCHTABLE_VENDGROUP' = pt.vendgroup, 
         'PURCHTABLE_VENDINVOICEDECLARATION_IS' = 
         pt.vendinvoicedeclaration_is, 
         'PURCHTABLE_VENDORREF' = pt.vendorref, 
         'PURCHTABLE_WORKERPURCHPLACER' = 
            pt.workerpurchplacer, 
  'PURCHLINE_ACCOUNTINGDISTRIBUTIONTEMPLATE' = 
  pl.accountingdistributiontemplate, 
  'PURCHLINE_ACTIVITYNUMBER' = pl.activitynumber, 
  'PURCHLINE_ADDRESSREFRECID' = pl.addressrefrecid, 
  'PURCHLINE_ADDRESSREFTABLEID' = pl.addressreftableid, 
  'PURCHLINE_AGREEMENTSKIPAUTOLINK' = 
            pl.agreementskipautolink, 
  'PURCHLINE_ASSETBOOKID' = pl.assetbookid, 
  'PURCHLINE_ASSETGROUP' = pl.assetgroup, 
  'PURCHLINE_ASSETID' = pl.assetid, 
  'PURCHLINE_ASSETTRANSTYPEPURCH' = pl.assettranstypepurch, 
  'PURCHLINE_BARCODE' = pl.barcode, 
  'PURCHLINE_BARCODETYPE' = pl.barcodetype, 
  'PURCHLINE_BLOCKED' = pl.blocked, 
  'PURCHLINE_CASETAGGING' = pl.casetagging, 
  'PURCHLINE_CFOPTABLE_BR' = pl.cfoptable_br, 
  'PURCHLINE_COMPLETE' = pl.complete, 
  'PURCHLINE_CONFIRMEDDLV' = pl.confirmeddlv, 
  'PURCHLINE_CONFIRMEDTAXAMOUNT' = pl.confirmedtaxamount, 
  'PURCHLINE_CONFIRMEDTAXWRITECODE' = 
            pl.confirmedtaxwritecode, 
  'PURCHLINE_COUNTYORIGDEST' = pl.countyorigdest, 
  'PURCHLINE_COVREF' = pl.covref, 
  'PURCHLINE_CREATEDDATETIME' = pl.createddatetime, 
  'PURCHLINE_CREATEFIXEDASSET' = pl.createfixedasset, 
  'PURCHLINE_CURRENCYCODE' = pl.currencycode, 
  'PURCHLINE_CUSTOMERREF' = pl.customerref, 
  'PURCHLINE_CUSTPURCHASEORDERFORMNUM' = 
  pl.custpurchaseorderformnum, 
  'PURCHLINE_DATAAREAID' = pl.dataareaid, 
  'PURCHLINE_DEFAULTDIMENSION' = pl.defaultdimension, 
  'PURCHLINE_DELIVERYDATE' = pl.deliverydate, 
  'PURCHLINE_DELIVERYNAME' = pl.deliveryname, 
  'PURCHLINE_DELIVERYPOSTALADDRESS' = 
            pl.deliverypostaladdress, 
  'PURCHLINE_DELIVERYTYPE' = pl.deliverytype, 
  'PURCHLINE_DEPRECIATIONSTARTDATE' = 
            pl.depreciationstartdate, 
  'PURCHLINE_DISCAMOUNT' = pl.discamount, 
  'PURCHLINE_DISCPERCENT' = pl.discpercent, 
  'PURCHLINE_EDITABLEINWORKFLOW' = pl.editableinworkflow, 
  'PURCHLINE_EXTERNALITEMID' = pl.externalitemid, 
  'PURCHLINE_GSTHSTTAXTYPE_CA' = pl.gsthsttaxtype_ca, 
  'PURCHLINE_INTERCOMPANYINVENTTRANSID' = 
  pl.intercompanyinventtransid, 
  'PURCHLINE_INTERCOMPANYORIGIN' = pl.intercompanyorigin, 
  'PURCHLINE_INTRASTATFULFILLMENTDATE_HU' = 
  pl.intrastatfulfillmentdate_hu, 
  'PURCHLINE_INVENTDIMID' = pl.inventdimid, 
  'PURCHLINE_INVENTINVOICENOW' = pl.inventinvoicenow, 
  'PURCHLINE_INVENTRECEIVEDNOW' = pl.inventreceivednow, 
  'PURCHLINE_INVENTREFID' = pl.inventrefid, 
  'PURCHLINE_INVENTREFTRANSID' = pl.inventreftransid, 
  'PURCHLINE_INVENTTRANSID' = pl.inventtransid, 
  'PURCHLINE_ISDELETED' = pl.isdeleted, 
  'PURCHLINE_ISFINALIZED' = pl.isfinalized, 
  'PURCHLINE_ISINVOICEMATCHED' = pl.isinvoicematched, 
  'PURCHLINE_ISMODIFIED' = pl.ismodified, 
  'PURCHLINE_ISPWP' = pl.ispwp, 
  'PURCHLINE_ITEMBOMID' = pl.itembomid, 
  'PURCHLINE_ITEMID' = pl.itemid, 
  'PURCHLINE_ITEMPBAID' = pl.itempbaid, 
  'PURCHLINE_ITEMREFTYPE' = pl.itemreftype, 
  'PURCHLINE_ITEMROUTEID' = pl.itemrouteid, 
  'PURCHLINE_ITEMTAGGING' = pl.itemtagging, 
  'PURCHLINE_LEDGERDIMENSION' = pl.ledgerdimension, 
  'PURCHLINE_LINEAMOUNT' = pl.lineamount, 
  'PURCHLINE_LINEDELIVERYTYPE' = pl.linedeliverytype, 
  'PURCHLINE_LINEDISC' = pl.linedisc, 
  'PURCHLINE_LINEHEADER' = pl.lineheader, 
  'PURCHLINE_LINENUMBER' = pl.linenumber, 
  'PURCHLINE_LINEPERCENT' = pl.linepercent, 
  'PURCHLINE_MANUALENTRYCHANGEPOLICY' = 
  pl.manualentrychangepolicy, 
  'PURCHLINE_MANUALMODIFIEDFIELD' = pl.manualmodifiedfield, 
  'PURCHLINE_MATCHINGAGREEMENTLINE' = 
            pl.matchingagreementline, 
  'PURCHLINE_MATCHINGPOLICY' = pl.matchingpolicy, 
  'PURCHLINE_MCRDROPSHIPCOMMENT' = pl.mcrdropshipcomment, 
  'PURCHLINE_MCRDROPSHIPMENT' = pl.mcrdropshipment, 
  'PURCHLINE_MCRDROPSHIPSTATUS' = pl.mcrdropshipstatus, 
  'PURCHLINE_MCRORDERLINE2PRICEHISTORYREF' = 
  pl.mcrorderline2pricehistoryref, 
  'PURCHLINE_MODIFIEDDATETIME' = pl.modifieddatetime, 
  'PURCHLINE_MULTILNDISC' = pl.multilndisc, 
  'PURCHLINE_MULTILNPERCENT' = pl.multilnpercent, 
  'PURCHLINE_NAME' = pl.NAME, 
  'PURCHLINE_OPERATIONTYPE_MX' = pl.operationtype_mx, 
  'PURCHLINE_OVERDELIVERYPCT' = pl.overdeliverypct, 
  'PURCHLINE_PALLETTAGGING' = pl.pallettagging, 
  'PURCHLINE_PARTITION' = pl.partition, 
  'PURCHLINE_PDSCALCULATIONID' = pl.pdscalculationid, 
  'PURCHLINE_PDSCWINVENTRECEIVEDNOW' = 
  pl.pdscwinventreceivednow, 
  'PURCHLINE_PDSCWQTY' = pl.pdscwqty, 
  'PURCHLINE_PDSCWREMAININVENTFINANCIAL' = 
  pl.pdscwremaininventfinancial, 
  'PURCHLINE_PDSCWREMAININVENTPHYSICAL' = 
  pl.pdscwremaininventphysical, 
  'PURCHLINE_PLANREFERENCE' = pl.planreference, 
  'PURCHLINE_PORT' = pl.port, 
  'PURCHLINE_PRICEUNIT' = pl.priceunit, 
  'PURCHLINE_PROCUREMENTCATEGORY' = pl.procurementcategory, 
  'PURCHLINE_PROJCATEGORYID' = pl.projcategoryid, 
  'PURCHLINE_PROJID' = pl.projid, 
  'PURCHLINE_PROJLINEPROPERTYID' = pl.projlinepropertyid, 
  'PURCHLINE_PROJSALESCURRENCYID' = pl.projsalescurrencyid, 
  'PURCHLINE_PROJSALESPRICE' = pl.projsalesprice, 
  'PURCHLINE_PROJSALESUNITID' = pl.projsalesunitid, 
  'PURCHLINE_PROJTAXGROUPID' = pl.projtaxgroupid, 
  'PURCHLINE_PROJTAXITEMGROUPID' = pl.projtaxitemgroupid, 
  'PURCHLINE_PROJTRANSID' = pl.projtransid, 
  'PURCHLINE_PROJWORKER' = pl.projworker, 
  'PURCHLINE_PSARETAINSCHEDULEID' = pl.psaretainscheduleid, 
  'PURCHLINE_PSATOTALRETAINAMOUNT' = 
         pl.psatotalretainamount, 
  'PURCHLINE_PURCHASETYPE' = pl.purchasetype, 
  'PURCHLINE_PURCHCOMMITMENTLINE_PSN' = 
  pl.purchcommitmentline_psn, 
  'PURCHLINE_PURCHID' = pl.purchid, 
  'PURCHLINE_PURCHMARKUP' = pl.purchmarkup, 
  'PURCHLINE_PURCHPRICE' = pl.purchprice, 
  'PURCHLINE_PURCHQTY' = pl.purchqty, 
  'PURCHLINE_PURCHRECEIVEDNOW' = pl.purchreceivednow, 
  'PURCHLINE_PURCHREQID' = pl.purchreqid, 
  'PURCHLINE_PURCHREQLINEREFID' = pl.purchreqlinerefid, 
  'PURCHLINE_PURCHSTATUS' = pl.purchstatus, 
  'PURCHLINE_PURCHUNIT' = pl.purchunit, 
  'PURCHLINE_QTYORDERED' = pl.qtyordered, 
  'PURCHLINE_RBOPACKAGELINENUM' = pl.rbopackagelinenum, 
  'PURCHLINE_RECID' = pl.recid, 
  'PURCHLINE_RECVERSION' = pl.recversion, 
  'PURCHLINE_REMAINDER' = pl.remainder, 
  'PURCHLINE_REMAININVENTFINANCIAL' = 
            pl.remaininventfinancial, 
  'PURCHLINE_REMAININVENTPHYSICAL' = 
            pl.remaininventphysical, 
  'PURCHLINE_REMAINPURCHFINANCIAL' = 
         pl.remainpurchfinancial, 
  'PURCHLINE_REMAINPURCHPHYSICAL' = 
         pl.remainpurchphysical, 
  'PURCHLINE_REQATTENTION' = pl.reqattention, 
  'PURCHLINE_REQPLANIDSCHED' = pl.reqplanidsched, 
  'PURCHLINE_REQPOID' = pl.reqpoid, 
  'PURCHLINE_REQUESTER' = pl.requester, 
  'PURCHLINE_RETAILLINENUMEX1' = pl.retaillinenumex1, 
  'PURCHLINE_RETAILPACKAGEID' = pl.retailpackageid, 
  'PURCHLINE_RETAILTEMPVALUEEX2' = pl.retailtempvalueex2, 
  'PURCHLINE_RETURNACTIONID' = pl.returnactionid, 
  'PURCHLINE_RETURNDISPOSITIONCODEID' = 
  pl.returndispositioncodeid, 
  'PURCHLINE_RETURNSTATUS' = pl.returnstatus, 
  'PURCHLINE_SCRAP' = pl.scrap, 
  'PURCHLINE_SERVICEADDRESS' = pl.serviceaddress, 
  'PURCHLINE_SERVICEDATE' = pl.servicedate, 
  'PURCHLINE_SHIPPINGDATECONFIRMED' = 
            pl.shippingdateconfirmed, 
  'PURCHLINE_SHIPPINGDATEREQUESTED' = 
            pl.shippingdaterequested, 
  'PURCHLINE_SKIPDISTRIBUTIONUPDATE' = 
  pl.skipdistributionupdate, 
  'PURCHLINE_SOURCEDOCUMENTLINE' = pl.sourcedocumentline, 
  'PURCHLINE_STATISTICVALUE_LT' = pl.statisticvalue_lt, 
  'PURCHLINE_STATPROCID' = pl.statprocid, 
  'PURCHLINE_STATTRIANGULARDEAL' = pl.stattriangulardeal, 
  'PURCHLINE_STOCKEDPRODUCT' = pl.stockedproduct, 
  'PURCHLINE_SYSTEMENTRYCHANGEPOLICY' = 
  pl.systementrychangepolicy, 
  'PURCHLINE_SYSTEMENTRYSOURCE' = pl.systementrysource, 
  'PURCHLINE_TAMITEMVENDREBATEGROUPID' = 
  pl.tamitemvendrebategroupid, 
  'PURCHLINE_TAX1099AMOUNT' = pl.tax1099amount, 
  'PURCHLINE_TAX1099FIELDS' = pl.tax1099fields, 
  'PURCHLINE_TAX1099RECID' = pl.tax1099recid, 
  'PURCHLINE_TAX1099STATE' = pl.tax1099state, 
  'PURCHLINE_TAX1099STATEAMOUNT' = pl.tax1099stateamount, 
  'PURCHLINE_TAXAUTOGENERATED' = pl.taxautogenerated, 
  'PURCHLINE_TAXGROUP' = pl.taxgroup, 
  'PURCHLINE_TAXITEMGROUP' = pl.taxitemgroup, 
  'PURCHLINE_TAXSERVICECODE_BR' = pl.taxservicecode_br, 
  'PURCHLINE_TAXWITHHOLDBASECUR_TH' = 
            pl.taxwithholdbasecur_th, 
  'PURCHLINE_TAXWITHHOLDGROUP_TH' = 
            pl.taxwithholdgroup_th, 
  'PURCHLINE_TAXWITHHOLDITEMGROUPHEADING_TH' = 
  pl.taxwithholditemgroupheading_th, 
  'PURCHLINE_TRANSACTIONCODE' = pl.transactioncode, 
  'PURCHLINE_TRANSPORT' = pl.transport, 
  'PURCHLINE_UNDERDELIVERYPCT' = pl.underdeliverypct, 
  'PURCHLINE_VARIANTID' = pl.variantid, 
  'PURCHLINE_VENDACCOUNT' = pl.vendaccount, 
  'PURCHLINE_VENDGROUP' = pl.vendgroup, 
  'PURCHLINE_WFDELIVERYDUESTATE' = pl.wfdeliveryduestate, 
  'PURCHLINE_WFINVRECEIVEDSTATE' = pl.wfinvreceivedstate, 
  'PURCHLINE_WORKFLOWSTATE' = pl.workflowstate, 
  '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, 
  'itdw_ITEMID' = itwd.itemid, 
  'itdw_ITEMTYPE' = itwd.itemtype, 
  'itdw_PURCHMODEL' = itwd.purchmodel, 
  'itdw_HEIGHT' = itwd.height, 
  'itdw_WIDTH' = itwd.width, 
  'itdw_SALESMODEL' = itwd.salesmodel, 
  'itdw_COSTGROUPID' = itwd.costgroupid, 
  'itdw_REQGROUPID' = itwd.reqgroupid, 
  'itdw_EPCMANAGER' = itwd.epcmanager, 
  'itdw_PRIMARYVENDORID' = itwd.primaryvendorid, 
  'itdw_NETWEIGHT' = itwd.netweight, 
  'itdw_DEPTH' = itwd.depth, 
  'itdw_UNITVOLUME' = itwd.unitvolume, 
  'itdw_BOMUNITID' = itwd.bomunitid, 
  'itdw_ITEMPRICETOLERANCEGROUPID' = 
  itwd.itempricetolerancegroupid, 
  'itdw_DENSITY' = itwd.density, 
  'itdw_COSTMODEL' = itwd.costmodel, 
  'itdw_USEALTITEMID' = itwd.usealtitemid, 
  'itdw_ALTITEMID' = itwd.altitemid, 
  'itdw_MATCHINGPOLICY' = itwd.matchingpolicy, 
  'itdw_INTRACODE' = itwd.intracode, 
  'itdw_PRODFLUSHINGPRINCIP' = itwd.prodflushingprincip, 
  'itdw_MINIMUMPALLETQUANTITY' = itwd.minimumpalletquantity, 
  'itdw_PBAITEMAUTOGENERATED' = itwd.pbaitemautogenerated, 
  'itdw_WMSARRIVALHANDLINGTIME' = 
         itwd.wmsarrivalhandlingtime, 
  'itdw_BOMMANUALRECEIPT' = itwd.bommanualreceipt, 
  'itdw_PHANTOM' = itwd.phantom, 
  'itdw_INTRAUNIT' = itwd.intraunit, 
  'itdw_BOMLEVEL' = itwd.bomlevel, 
  'itdw_BATCHNUMGROUPID' = itwd.batchnumgroupid, 
  'itdw_AUTOREPORTFINISHED' = itwd.autoreportfinished, 
  'itdw_ORIGCOUNTRYREGIONID' = itwd.origcountryregionid, 
  'itdw_STATISTICSFACTOR' = itwd.statisticsfactor, 
  'itdw_ALTCONFIGID' = itwd.altconfigid, 
  'itdw_STANDARDCONFIGID' = itwd.standardconfigid, 
  'itdw_PRODPOOLID' = itwd.prodpoolid, 
  'itdw_PROPERTYID' = itwd.propertyid, 
  'itdw_ABCTIEUP' = itwd.abctieup, 
  'itdw_ABCREVENUE' = itwd.abcrevenue, 
  'itdw_ABCVALUE' = itwd.abcvalue, 
  'itdw_ABCCONTRIBUTIONMARGIN' = itwd.abccontributionmargin, 
  'itdw_COMMISSIONGROUPID' = itwd.commissiongroupid, 
  'itdw_SALESPERCENTMARKUP' = itwd.salespercentmarkup, 
  'itdw_SALESCONTRIBUTIONRATIO' = 
         itwd.salescontributionratio, 
  'itdw_SALESPRICEMODELBASIC' = itwd.salespricemodelbasic, 
  'itdw_NAMEALIAS' = itwd.namealias, 
  'itdw_PRODGROUPID' = itwd.prodgroupid, 
  'itdw_PROJCATEGORYID' = itwd.projcategoryid, 
  'itdw_GROSSDEPTH' = itwd.grossdepth, 
  'itdw_GROSSWIDTH' = itwd.grosswidth, 
  'itdw_GROSSHEIGHT' = itwd.grossheight, 
  'itdw_STANDARDPALLETQUANTITY' = 
         itwd.standardpalletquantity, 
  'itdw_QTYPERLAYER' = itwd.qtyperlayer, 
  'itdw_SORTCODE' = itwd.sortcode, 
  'itdw_SERIALNUMGROUPID' = itwd.serialnumgroupid, 
  'itdw_ITEMBUYERGROUPID' = itwd.itembuyergroupid, 
  'itdw_TAXPACKAGINGQTY' = itwd.taxpackagingqty, 
  'itdw_WMSPALLETTYPEID' = itwd.wmspallettypeid, 
  'itdw_ORIGSTATEID' = itwd.origstateid, 
  'itdw_WMSPICKINGQTYTIME' = itwd.wmspickingqtytime, 
  'itdw_TARAWEIGHT' = itwd.taraweight, 
  'itdw_PACKAGINGGROUPID' = itwd.packaginggroupid, 
  'itdw_SCRAPVAR' = itwd.scrapvar, 
  'itdw_SCRAPCONST' = itwd.scrapconst, 
  'itdw_STANDARDINVENTCOLORID' = itwd.standardinventcolorid, 
  'itdw_STANDARDINVENTSIZEID' = itwd.standardinventsizeid, 
  'itdw_ITEMDIMCOSTPRICE' = itwd.itemdimcostprice, 
  'itdw_ALTINVENTSIZEID' = itwd.altinventsizeid, 
  'itdw_ALTINVENTCOLORID' = itwd.altinventcolorid, 
  'itdw_FORECASTDMPINCLUDE' = itwd.forecastdmpinclude, 
  'itdw_PRODUCT' = itwd.product, 
  'itdw_PALLETTAGGING' = itwd.pallettagging, 
  'itdw_ITEMTAGGINGLEVEL' = itwd.itemtagginglevel, 
  'itdw_DEFAULTDIMENSION' = itwd.defaultdimension, 
  'itdw_FISCALLIFOAVOIDCALC' = itwd.fiscallifoavoidcalc, 
  'itdw_FISCALLIFONORMALVALUE' = itwd.fiscallifonormalvalue, 
  'itdw_FISCALLIFONORMALVALUECALC' = 
  itwd.fiscallifonormalvaluecalc, 
  'itdw_BOMCALCGROUPID' = itwd.bomcalcgroupid, 
  'itdw_INVENTFISCALLIFOGROUP' = itwd.inventfiscallifogroup, 
  'itdw_NGPCODESTABLE_FR' = itwd.ngpcodestable_fr, 
  'itdw_ORIGCOUNTYID' = itwd.origcountyid, 
  'itdw_TAXFISCALCLASSIFICATION_BR' = 
  itwd.taxfiscalclassification_br, 
  'itdw_PBAITEMCONFIGURABLE' = itwd.pbaitemconfigurable, 
  'itdw_PBAINVENTITEMGROUPID' = itwd.pbainventitemgroupid, 
  'itdw_PBAHIDEDIALOG' = itwd.pbahidedialog, 
  'itdw_PBAHIDEAPPROVAL' = itwd.pbahideapproval, 
  'itdw_PBAAUTOSTART' = itwd.pbaautostart, 
  'itdw_PBAMANDATORYCONFIG' = itwd.pbamandatoryconfig, 
  'itdw_INVENTPRODUCTTYPE_BR' = itwd.inventproducttype_br, 
  'itdw_TAXATIONORIGIN_BR' = itwd.taxationorigin_br, 
  'itdw_TAXSERVICECODE_BR' = itwd.taxservicecode_br, 
  'itdw_EXCISETARIFFCODES_IN' = itwd.excisetariffcodes_in, 
  'itdw_CUSTOMSEXPORTTARIFFCODETABLE_IN' = 
  itwd.customsexporttariffcodetable_in, 
  'itdw_CUSTOMSIMPORTTARIFFCODETABLE_IN' = 
  itwd.customsimporttariffcodetable_in, 
  'itdw_SERVICECODETABLE_IN' = itwd.servicecodetable_in, 
  'itdw_EXIMPRODUCTGROUPTABLE_IN' = 
  itwd.eximproductgrouptable_in, 
  'itdw_PACKING_RU' = itwd.packing_ru, 
  'itdw_ASSETGROUPID_RU' = itwd.assetgroupid_ru, 
  'itdw_ASSETID_RU' = itwd.assetid_ru, 
  'itdw_INTRASTATEXCLUDE' = itwd.intrastatexclude, 
  'itdw_INTRASTATPROCID_CZ' = itwd.intrastatprocid_cz, 
  'itdw_PKWIUCODE_PL' = itwd.pkwiucode_pl, 
  'itdw_EXCEPTIONCODE_BR' = itwd.exceptioncode_br, 
  'itdw_ICMSONSERVICE_BR' = itwd.icmsonservice_br, 
  'itdw_PDSCWWMSSTANDARDPALLETQTY' = 
  itwd.pdscwwmsstandardpalletqty, 
  'itdw_PDSCWWMSMINIMUMPALLETQTY' = 
  itwd.pdscwwmsminimumpalletqty, 
  'itdw_PDSCWWMSQTYPERLAYER' = itwd.pdscwwmsqtyperlayer, 
  'itdw_ALCOHOLMANUFACTURERID_RU' = 
  itwd.alcoholmanufacturerid_ru, 
  'itdw_ALCOHOLPRODUCTIONTYPEID_RU' = 
  itwd.alcoholproductiontypeid_ru, 
  'itdw_ALCOHOLSTRENGTH_RU' = itwd.alcoholstrength_ru, 
  'itdw_ALTINVENTSTYLEID' = itwd.altinventstyleid, 
  'itdw_APPROXTAXVALUE_BR' = itwd.approxtaxvalue_br, 
  'itdw_BATCHMERGEDATECALCULATIONMETHOD' = 
  itwd.batchmergedatecalculationmethod, 
  'itdw_DAXINTEGRATIONKEY' = itwd.daxintegrationkey, 
  'itdw_MARKUPCODE_RU' = itwd.markupcode_ru, 
  'itdw_NRTAXGROUP_LV' = itwd.nrtaxgroup_lv, 
  'itdw_PDSBASEATTRIBUTEID' = itwd.pdsbaseattributeid, 
  'itdw_PDSBESTBEFORE' = itwd.pdsbestbefore, 
  'itdw_PDSFREIGHTALLOCATIONGROUPID' = 
  itwd.pdsfreightallocationgroupid, 
  'itdw_PDSITEMREBATEGROUPID' = itwd.pdsitemrebategroupid, 
  'itdw_PDSPOTENCYATTRIBRECORDING' = 
  itwd.pdspotencyattribrecording, 
  'itdw_PDSSHELFADVICE' = itwd.pdsshelfadvice, 
  'itdw_PDSSHELFLIFE' = itwd.pdsshelflife, 
  'itdw_PDSTARGETFACTOR' = itwd.pdstargetfactor, 
  'itdw_PDSVENDORCHECKITEM' = itwd.pdsvendorcheckitem, 
  'itdw_PMFPLANNINGITEMID' = itwd.pmfplanningitemid, 
  'itdw_PMFPRODUCTTYPE' = itwd.pmfproducttype, 
  'itdw_PMFYIELDPCT' = itwd.pmfyieldpct, 
  'itdw_SADRATECODE_PL' = itwd.sadratecode_pl, 
  'itdw_SKIPINTRACOMPANYSYNC_RU' = 
  itwd.skipintracompanysync_ru, 
  'itdw_STANDARDINVENTSTYLEID' = itwd.standardinventstyleid, 
  'itdw_MODIFIEDDATETIME' = itwd.modifieddatetime, 
  'itdw_DEL_MODIFIEDTIME' = itwd.del_modifiedtime, 
  'itdw_MODIFIEDBY' = itwd.modifiedby, 
  'itdw_CREATEDDATETIME' = itwd.createddatetime, 
  'itdw_DEL_CREATEDTIME' = itwd.del_createdtime, 
  'itdw_CREATEDBY' = itwd.createdby, 
  'itdw_DATAAREAID' = itwd.dataareaid, 
  'itdw_RECVERSION' = itwd.recversion, 
  'itdw_PARTITION' = itwd.partition, 
  'itdw_RECID' = itwd.recid, 
  'itdw_productmaster' = itwd.productmaster, 
  'itdw_retaitotalweight' = itwd.retaitotalweight, 
  'itdw_variantconfigurationtechnology' = 
  itwd.variantconfigurationtechnology, 
  'itdw_retailcolorgroupid' = itwd.retailcolorgroupid, 
  'itdw_retailsizegroupid' = itwd.retailsizegroupid, 
  'itdw_retailstylegroupid' = itwd.retailstylegroupid, 
  'itdw_instancerelationtype' = itwd.instancerelationtype, 
  'itdw_displayproductnumber' = itwd.displayproductnumber, 
  'itdw_searchname' = itwd.searchname, 
  'itdw_producttype' = itwd.producttype, 
  'itdw_pdscwproduct' = itwd.pdscwproduct, 
  'itdw_storageName' = itwd.storagename, 
  'itdw_storageDescription' = itwd.storagedescription, 
  'itdw_iswarehousemandatoryenabled' = 
  itwd.iswarehousemandatoryenabled, 
  'itdw_iswarehouseprimarystockingenabled' = 
  itwd.iswarehouseprimarystockingenabled, 
  'itdw_iswarehousewhsenabled' = itwd.iswarehousewhsenabled, 
  'itdw_wmslocationenabled' = itwd.wmslocationenabled, 
  'itdw_wmspalletidenabled' = itwd.wmspalletidenabled, 
  'itdw_inventwarehouseenabled' = 
         itwd.inventwarehouseenabled, 
  'itdw_inventsiteenabled' = itwd.inventsiteenabled, 
  'itdw_licenseplateenabled' = itwd.licenseplateenabled, 
  'itdw_inventstatusenabled' = itwd.inventstatusenabled, 
  'itdw_trackingName' = itwd.trackingname, 
  'itdw_trackingDescription' = itwd.trackingdescription, 
  'itdw_isserialnumbercontrolenabled' = 
  itwd.isserialnumbercontrolenabled, 
  'itdw_captureserial' = itwd.captureserial, 
  'itdw_inventbatchenabled' = itwd.inventbatchenabled, 
  'itdw_inventserialenabled' = itwd.inventserialenabled, 
  'itdw_inventgtdenabled' = itwd.inventgtdenabled, 
  'itdw_inventprofileenabled' = itwd.inventprofileenabled, 
  'itdw_inventownerenabled' = itwd.inventownerenabled, 
  'itdw_ProductName' = itwd.productname, 
  'itdw_productDescription' = itwd.productdescription, 
  'itdw_configenabled' = itwd.configenabled, 
  'itdw_inventsizeenabled' = itwd.inventsizeenabled, 
  'itdw_inventcolorenabled' = itwd.inventcolorenabled, 
  'itdw_inventstyleenabled' = itwd.inventstyleenabled 
  FROM   purchtable pt 
         LEFT JOIN purchline pl 
                ON pl.purchid = pt.purchid 
                   AND pl.dataareaid = pt.dataareaid 
                   AND pl.partition = pt.partition 
         INNER JOIN inventdim id 
                 ON id.inventdimid = pl.inventdimid 
                    AND id.dataareaid = pt.dataareaid 
                    AND id.partition = pt.partition 
         INNER JOIN vw_inventitemswithdimensions itwd 
                 ON itwd.itemid = pl.itemid 
                    AND itwd.dataareaid = pt.dataareaid 
                    AND itwd.partition = pt.partition 

go