DynDeveloper.com

Okay - Good Morning.

I am trying to calculate Item Usage for specific date ranges and fumbling with using GET DATE function for 'in between' Dates...

Here's a straight forward script to calculate Item MO Usage for past 90 days - it works great.

 (SELECT        [Item Number], sum([TRX QTY] * - 1) AS MOUsageQty90 
FROM            InventoryTransactions
WHERE        ([Document Type] = 'Adjustment') AND ([Source Indicator] in ('Issue', 'Reverse Issue') AND ([Document Date] >= GETDATE() - 90)) 
GROUP BY [Item Number])

When I try to calculate usage between 90 and 180 days - I modified as such and I get nothing in return:

(SELECT        [Item Number], sum([TRX QTY] * - 1) AS MOUsageQty180 
FROM            InventoryTransactions
WHERE        ([Document Type] = 'Adjustment') AND ([Source Indicator] = 'Issue') AND ([Document Date] BETWEEN GETDATE() -90  AND GETDATE() - 180) OR
                         ([Document Type] = 'Adjustment') AND ([Source Indicator] = 'Reverse Issue') AND ([Document Date] BETWEEN GETDATE() - 90  AND GETDATE() - 180) 
GROUP BY [Item Number])

I think my issue might be related to [Document Date] being a date time field and Get Date is just a Date format? Or am I missing something obvious? 

Hi.

I'm trying to get IM to work for this destination.  It's an econnect integration and seems to be requiring things that regular data entry to the screen does not.  I'm trying to make an integration to receivables transactions for non-inventory items.

Any ideas how to make this work?

Here's the text of the IM error

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Sql procedure error codes returned:

Error Number = 2051  Stored Procedure= taPopRcptLineInsert  Error Description = Input variable contains an empty value
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 3
POPRCTNM = RCT40762
PONUMBER =
ITEMNMBR =
Related Error Code Parameters for Node : taPopRcptLineInsert
POPRCTNM = RCT40762
ITEMNMBR =
VNDITNUM =


<taPopRcptLineInsert>
  <POPRCTNM>RCT40762</POPRCTNM>
  <POPTYPE>3</POPTYPE>
  <VENDORID>MASTERCARD</VENDORID>
  <PONUMBER></PONUMBER>
  <ITEMNMBR></ITEMNMBR>
  <ITEMDESC></ITEMDESC>
  <VNDITNUM></VNDITNUM>
  <VNDITDSC></VNDITDSC>
  <ACTLSHIP></ACTLSHIP>
  <InventoryAccount></InventoryAccount>
  <UOFM></UOFM>
  <UNITCOST>12.42000</UNITCOST>
  <EXTDCOST></EXTDCOST>
  <QTYSHPPD>1.000000000000000</QTYSHPPD>
  <QTYINVCD>1.000000000000000</QTYINVCD>
  <LOCNCODE></LOCNCODE>
  <POLNENUM></POLNENUM>
  <ProjNum>BRA001-OPEX</ProjNum>
  <CostCatID>ENT</CostCatID>
  <AUTOCOST>0</AUTOCOST>
  <NONINVEN>1</NONINVEN>
  <Landed_Cost_Group_ID></Landed_Cost_Group_ID>
  <receiptdate></receiptdate>
  <Purchase_IV_Item_Taxable>2</Purchase_IV_Item_Taxable>
  <Purchase_Item_Tax_Schedu />
  <AutoAssignBin>1</AutoAssignBin>
  <RequesterTrx>0</RequesterTrx>
</taPopRcptLineInsert>

Integration Failed
Integration Results
    1 documents were read from the source query.
    1 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        1 failed to integrate.

Okay -

The engineering group occasionally needs raw materials from the warehouse.

We want the ability to relieve inventory and charge the items to an engineering expense account. 

Currently company creates a zero dollar bogus sales order. Use the sales order to create a zero dollar invoice and then change GL accounts to assure engineering gets expensed.

Alternatively we use Inventory Adjustments - specific reason code (coded to GL)

My general question - would the purchasing requisition module be usable OR do you have to create a PO from a requisition? 

Obviously other folks have same business requirements and processes - how do other do it?

Thanks in advance....