Customization and Integration

[TRoop]

We are trying to evaluate what stored procedures are called in the application to run the Inventory Valuation Report and populate the associated WRK table(s) in Mas 500. Does anyone have the details behind how this works? or a "map" of the stored procedure calls in SQL that we can review??

Thanks

[Ralph]

spimPPInvtValWrk  is one of the procs that are called (it may be the only one called), but you probably already knew that much. 

[TRoop]

We've looked at this stored proc and it seems that there is a temp table getting built before this SP gets called. We are trying to identify what actually builds this temp table...

#timPPInvtValLstWrk

Any ideas? (hope I didn't overlook something here...)

[rsisk101 · Accepted solution]

Tim,

That table is created and first populated in the client code like this...

SELECT * INTO #timPPInvtValLstWrk FROM timPPInvtValLstWrk WHERE 1=2

INSERT INTO #timPPInvtValLstWrk (ItemKey, WhseKey,InvtAcctKey,BalRec ,SessionID ,CompanyID) SELECT DISTINCT timInventory.ItemKey, timInventory.WhseKey,timInventory.InvtAcctKey,0 ,7,'SOA' FROM timInventory WITH (NOLOCK),timWarehouse WITH (NOLOCK),timItem WITH (NOLOCK) WHERE timInventory.ItemKey = timItem.ItemKey AND timItem.CompanyID='SOA' AND timItem.ItemType <> 7 AND timWarehouse.Transit = 0 AND timInventory.WhseKey = timWarehouse.WhseKey

[TRoop]

So if I am understanding this correctly, it is actually in the application code where this table is being generated and then at some point a call is being made to run the SP.

(Let me know if I am way off base...)

Thanks...

[TRoop]

Thanks Rick...

I think this is what we were looking for...

[rsisk101]

Tim, you are correct. This technique is used quite often in MAS500. Using the SELECT INTO from a template table allows modification of the temp table definition by modifying the template table allowing customization in the field without modifying source. Then the table is populated with key information and handed off to a store procedure for additional processing.