呆子獅的夢想世界

-- DimensionAttribute (  Financial Dimension via different table )
select DEFAULTDIMENSION,* from vendtable where DATAAREAID ='usmf'
select * from DimensionAttributeValueSet where RECID='22565421982'
select * from dimensionAttributeValueSetItemView where DimensionAttributeValueSet  ='22565421982'
select Recid, * from DIMENSIONATTRIBUTEVALUE where recid = 22565424659
select name,type,* from DimensionAttribute where recid =22565421195
select Recid, * from DIMENSIONATTRIBUTESET

-- shipping carrier
select * from DimensionAttribute where recid = '68719479823'
select * from dimensionAttributeValue where  DIMENSIONATTRIBUTE = '68719479823'

select DEFAULTDIMENSION,* from purchTable where DATAAREAID ='usmf' and purchId = '00000800'
select * from DimensionAttributeValueSet where RECID='68719599323'

select * from DimensionAttributeSetItem where DimensionAttributeSet='22565421922'
select name,type,* from DimensionAttribute where RecId in (select DimensionAttribute from DimensionAttributeSetItem where DimensionAttributeSet='22565421922')
select * from DimensionAttributeSetItem where DimensionAttribute ='22565421195'
select * from DimAttributeVendTable where DATAAREAID ='usmf'

--auto post PO receive:
select purchid, * from purchtable where PURCHSTATUS = 1 and DOCUMENTSTATE = 40 and DATAAREAID ='usmf'
select INVENTTRANSTYPE,* from WMSJournalTable where DATAAREAID ='usmf' and  INVENTTRANSTYPE = 2

select * from NumberSequenceTable where txt = 'Packing Slip ID'
select * from CustParameters where DATAAREAID ='usmf'

select DGS_itemreceived, INVENTTRANSREFID,posted,journalid, * from WMSJOURNALTABLE where dataareaid ='usmf'
select * from WMSJOURNALTRANS where dataareaid ='usmf'

select * from PurchPrepayTable

select * from VENDPACKINGSLIPJOUR where dataareaid ='usmf'

-- update WMSJournalTable set DGS_itemReceived = 1 where INVENTTRANSREFID = '00000503'

select * from WMSJOURNALTRANSUNPOSTED where dataareaid ='usmf'

select SPECQTY, * from PurchParmUpdate where dataareaid ='usmf' and DOCUMENTSTATUS = 2

select parmid, InventTransId, PurchLineRecId,* from PurchParmLine  where dataareaid ='usmf' and ORIGPURCHID = '00000503'
select parmid,* from PurchParmTable  where dataareaid ='usmf' and PURCHID = '00000503'

select * from INVENTTRANSORIGIN where dataareaid ='usmf'

-- data management - data import
-- vendors v2

select PaymMode,* from vendTable where DATAAREAID='usmf' and  ACCOUNTNUM like 'BO00098';

select * from VendVendorV2Staging
select * from VendPaymModeTable where DATAAREAID='usmf'

select * from DimensionSetEntity

--delete from vendTable where DATAAREAID='usmf' and ACCOUNTNUM in ('BL00034','BL00079','BL00087')

-- EcoResProduct number seq while on create a new release product

select * from THK_ItemReservationMaster
select * from THK_ProductNumberSeqSetup
select * from NumberSequenceTable where recid = '68719538939'

select * from THK_ProductNumberSeqSetup
join NumberSequenceTable
on NumberSequenceTable.RECID = THK_ProductNumberSeqSetup.NUMBERSEQUENCETABLE

-- LedgerJournalTrans
select VOUCHER, JOURNALNUM, LEDGERDIMENSION,  * from LedgerJournalTrans where dataareaid = 'usmf' and VOUCHER = 'ARPM000708' 
select VOUCHER, JOURNALNUM, LEDGERDIMENSION, * from LedgerJournalTrans where dataareaid = 'usmf' and JOURNALNUM = '00591' 

select * from DIMENSIONATTRIBUTEVALUECOMBINATION where recid ='22565446605'

--Sales Order Form View
Select * from DGS_SalesOrderTableFormView where dataareaid = 'usmf'

SELECT salesTable.salesid, SalesTable.THK_ActualStatus, SalesTable.SalesType, SalesTable.THK_ReadyToShip, SalesLine.SalesStatus, SalesTable.THK_SHIPPINGFEE, SalesTable.THK_ACTUALWEIGHT
, salesline.THK_SHIPPINGWEIGHT, * FROM SalesTable
JOIN SalesLine 
ON SalesTable.SalesId = SalesLine.SalesId
WHERE 
((SalesTable.SalesId = N'CAU00013')) AND
((SalesTable.SalesType = 3)) AND ((SalesTable.THK_ActualStatus = 1)) AND ((SalesTable.THK_ReadyToShip = 1))  
AND ((SalesLine.SalesStatus = 1))

select * from THK_RemoteAreaTable
select * from THK_ShippingCarrierCostTable
select * from THK_ShippingLogic

select * from DimensionAttributeValue
select * from DimensionFinancialTag where value = 'DHL'

--- Work Template
Select WORKTEMPLATEPRIORITY,WorkTemplateQuery,WorkTemplateCode,* from whsWorkTemplateTable where DATAAREAID ='usmf' 
and whsWorkTemplateTable.WorkTransType = 2 --- // 2 = sales order
order by whsWorkTemplateTable.WORKTEMPLATEPRIORITY 

Select * from whsWorkTemplateLine where DATAAREAID ='usmf'
Select * from whsWorkTemplateTable where DATAAREAID ='usmf' and whsWorkTemplateTable.WorkTransType = 2
                    and whsWorkTemplateTable.WorkTemplatePriority = 0;
select * from WHSWaveTemplateTable where DATAAREAID ='usmf' and wavetemplatename = '24 Shipping Test'

select * from DGS_EditWorkTemplateSettings where DATAAREAID ='usmf'
select * from DGS_EditWorkTemplate where DATAAREAID ='usmf'  
select * from DGS_EditTemplateQuerySortingTable  where DATAAREAID ='usmf'  

select * from DGS_EditWorkTemplateSettings where DATAAREAID ='usmf' and TemplateType = 1

select * from DGS_EditWorkTemplate where DATAAREAID ='usmf' and TemplateType = 1 and TemplateCode = '24 Shipping Test' and WaveTemplateType = 1
select * from DGS_EditWorkTemplate where DATAAREAID ='usmf' and TemplateType = 1 and WaveTemplateType = 1 and TemplateCode = '24 Shipping Test'
select * from DGS_EditTemplateQuerySortingTable 

select * from DGS_WHSTemplateQueryTable

select * from WHSTmpWorkLine

-----Cost
Select inventdimid,* from salesline where salesid ='Amazon00013'

select * from DGS_SalesTotalCostView where salesid ='Amazon00013'
select * from DGS_OverallSalesTableView where salesid ='Amazon00013'
select * from DGS_SalesLineItemCostView where salesid = 'Amazon00013'
select * from DGS_SalesOrderTableFormView where salesid = 'CAU00021'

Select * from salesline
join InventDim 
on InventDim.inventdimid = salesline.inventdimid
and InventDim.DATAAREAID = salesline.DATAAREAID
join InventTableModule
on InventTableModule.itemid = salesline.itemid
and InventTableModule.DATAAREAID = salesline.DATAAREAID
and moduletype = 2
 where salesline.salesid ='Amazon00013'

-- UserInfo
select PREFERREDLOCALE,* from UserInfo 

-- SalesTable
Select ReturnReplaceOrigSalesId, SALESORIGINID, originalsalesid, EXCHANGERATE,* from salesTable where DATAAREAID ='usmf' 
Select SalesType, DGS_SALESORDERTYPE,* from salesTable where DATAAREAID ='usmf' 

select * from DGS_ReplaceReturnOrderSeq

Select SALESID,* from salesTable where DATAAREAID ='usmf' 
select SALESID,* from SALESTABLEDELETE where DATAAREAID ='usmf' 
select SALESID,* from SALESLINEDELETE where DATAAREAID ='usmf' 
select PURCHID,* from PURCHTABLEHISTORY where DATAAREAID ='usmf' 
select SALESID,* from MCRHISTSALESTABLE where DATAAREAID ='usmf' 

select PURCHID,* from purchtable where DATAAREAID ='usmf' 
select ISDELETED,PURCHID,itemid,* from purchline where DATAAREAID ='usmf'  and purchid='00000203'
select * from PurchTableVersion where PURCHIDDATAAREAID ='usmf' 

Select SALESID,INVENTTRANSid,itemid,* from salesline where DATAAREAID ='usmf' 

-- Return Table View:
Select ReturnReplaceOrigSalesId, SALESORIGINID, originalsalesid, EXCHANGERATE,* from salesTable 
where DATAAREAID ='usmf' 
and salestype = 4

select * from DGS_ReturnOrderInfoView where DATAAREAID ='usmf'

select * from DGS_BUinSOView where DATAAREAID ='usmf'
select * from DGS_SalesTotalAmountView where DATAAREAID ='usmf'
select * from DGS_RefundValueView
select * from DGS_OriginalSales_TotalReturnAmountView
select * from DGS_OriginalSales_TotalSalesAmountView

select * from DGS_SalesReturnDetailMaster

-- Cost for Below GP salesline
select * from DGS_belowGPCostRowView

select * from DGS_SOOverallNetGPPrecentView 
join salesline 
ON salesline.salesid = DGS_SOOverallNetGPPrecentView.salesid
and salesline.dataareaid = DGS_SOOverallNetGPPrecentView.dataareaid
right join DGS_ReleaseProductCostView
ON salesline.ITEMID = DGS_ReleaseProductCostView.ITEMID
and salesline.dataareaid = DGS_ReleaseProductCostView.dataareaid
where DGS_SOOverallNetGPPrecentView.DATAAREAID ='usmf' 
and SOBELOWNETGP = 1

-- BU GP
select * from DGS_BUMasterGPView
select * from DGS_SOOverallNetGPPrecentView where DATAAREAID ='usmf' 

-- Register:
select * from DGS_RefundRegister
select * from DGS_DISPUTEREGISTER
select * from DGS_VATREFUND

select * from DGS_EBayCaseRegister
select * from DGS_eBayReturnRegister

select CustomsHoldStatus,* from WHSShipmentTable where DATAAREAID ='usmf'
select * from DGS_eBayCaseRegisterViews  where DATAAREAID ='usmf'
select * from DGS_eBayReturnRegisterViews  where DATAAREAID ='usmf'

select * from DGS_RefundSalesView order by countingdays
select * from DGS_SalesReshipCountView
select * from DGS_ShipmentIssueRegCountView
select * from DGS_VATRefundSalesView

select * from DGS_DisputeSalesView

select ORIGINALSALESID, count(*) from SalesTable where dataareaid ='usmf' and DGS_SalesOrderType = 2 group by ORIGINALSALESID
select * from SalesTable where dataareaid ='usmf' and DGS_SalesOrderType = 2

select * from DGS_SalesTotalAmountView where dataareaid ='usmf'
select invoiceamountmst,* from CUSTINVOICEJOUR where dataareaid ='usmf'

-- Next OwnerShip:
select * from DGS_DepartmentDimensionView

-- Dimension attribute
select * from DimensionAttributeValue
select * from DGS_DepartmentDimensionView
select * from OMOperatingUnitView 

-- purchase master
select * from THK_PURCHASERMASTERTABLE
select * from THK_PURCHASERMASTERLINE

--category warranty period
select CategoryHierarchy, Name, Code, * from EcoResCategory where CATEGORYHIERARCHY ='22565420922'

select * from ECORESCATEGORYHIERARCHY
select * from ECORESCATEGORY where recid = '22565423435'
    
--whsshipment table:
select recid, ORDERNUM,CUSTOMSHOLDSTATUS, * from WHSSHIPMENTTABLE where DATAAREAID ='usmf'
--rec id:        order num:
--------------------------
--5637144576    000748
--5637145326    000752
--68719476738    000776
--68719477488    000784
 update WHSSHIPMENTTABLE set ORDERNUM = '000752' where recid = '5637145326'
-- update WHSSHIPMENTTABLE set CUSTOMSHOLDSTATUS = 'aaa' where DATAAREAID ='usmf'
-- update WHSSHIPMENTTABLE set CUSTOMSHOLDSTATUS = '' where DATAAREAID ='usmf' and  recid = '5637145326'

select * from DGS_WHSShipmentTableRowView

select * from THK_BulkShipCostCalculationTable
select * from THK_BulkShipCostCalculationLine
select * from THK_ShippingCartonTrackingTable
select * from DGS_ShippingCartonTrackingView
select * from DGS_ShipmentIssueSalesView
select * from DGS_ReplacementOrderRowView
select * from THK_ShippingCartonTrackingTable
select * from THK_BulkShipCostCalculationTable

-- DGS_Log
select * from DGS_LOGSTABLE where FieldTable = 'WHSShipmentTable'
select * from DGS_LogsTableRowView

select * from WHSShipmentTable

-- Tracking no:
select THK_TrackingNoLength,* from TMSCarrier curTMSCarrier
where dataareaid='usmf' and  curTMSCarrier.CarrierCode = 'DHL' 

-- Sales order 
Select DGS_PAYMENTNO, DGS_REMARKS, DGS_RequestToWarehouse,recid, * from salestable where dataareaid='usmf' and salesid ='000721'
select * from salestable where recid ='68719492799'

select inventlocationid, inventsiteid,* from InventLocation where dataareaid ='usmf'

select * from DGS_ReleaseProductCostPerWarehouse where dataareaid = 'usmf' 
--and itemid ='D0001' and inventlocationid ='24' and inventsiteid =''
order by itemid 

select * from DGS_ReleaseProductCostView

select * from inventsite  where dataareaid = 'usmf' 

select itemid, PHYSICALINVENT, DEDUCTED, inventdimid,* from inventSum where dataareaid = 'usmf' and itemid in ( 'D0012' ,'D0001', 'D0002', '1000')
select PHYSICALINVENT,* from inventSum where dataareaid = 'usmf' and itemid = '1000'

select * from inventDIM where dataareaid = 'usmf' and INVENTDIMID = '000031'

select * from DGS_EstFreightCostView where dataareaid = 'usmf'

select * from DGS_EstFreightCostFirstView

select PhysicalInvent, POSTEDQTY, received, DEDUCTED, registered, picked,inventdimid, * from inventsum where  dataareaid = 'usmf' and itemid ='D0004'

select PHYSICALINVENT, AVAILPHYSICAL, POSTEDQTY, received, DEDUCTED, registered, picked,salesline.inventdimid, * from inventsum 
join salesline
on salesline.INVENTDIMID = inventsum.INVENTDIMID
and salesline.itemid = inventsum.itemid
and salesline.dataareaid = inventsum.DATAAREAID
where salesline.DATAAREAID = 'usmf'
and salesline.itemid = 'D0004'

select * from inventdim

select inventsiteid, INVENTLOCATIONID,* from inventdim where inventdimid in (
select inventdimid  from inventsum 
where  dataareaid = 'usmf' and itemid ='D0004')
and dataareaid = 'usmf'

select POSTEDQTY, received, DEDUCTED, registered, picked,inventdimid, * from inventsum 
where  dataareaid = 'usmf' and inventdimid ='0000001000008250'

select * from inventdim where inventdimid = '#0000001000008250'
select salesid,inventdimid,* from salesline where  dataareaid = 'usmf' and salesid = 'CAU00007'

select * from inventdim where inventlocationid = '13' and inventsiteid='1' and dataareaid = 'usmf'

select * from DGS_ReleaseProductInventoryView where  dataareaid = 'usmf'
select * from DGS_ReleaseProductLastXdaysSalesView where  dataareaid = 'usmf'

select * from DGS_ReleaseProductSiteCostView where  dataareaid = 'usmf'
select * from DGS_ReleaseProductCostView where  dataareaid = 'usmf'

select * from DGS_BUMasterGPView where  dataareaid = 'usmf'
select * from DGS_SOOverallNetGPPrecentView where  dataareaid = 'usmf'
select * from DGS_belowGPCostRowView where  dataareaid = 'usmf'

select * from DGS_RELEASEPRODUCTCOSTPERWAREHOUSE

select * from PurchParmLine where DATAAREAID ='usmf'
select exchangerate,purchid,* from purchtable where DATAAREAID ='usmf'