A new Venture based project, Pop Art Machine, is available to the public in a beta version release.
Pop Art Machine’s database features over 1,000,000 unique item records (art & photography).
The public is invited to explore the pop art exhibit catalog.
Open source ERP software enthusiasts take note — the entire site is driven with a realtime link to a Venture 3.6.5 installation.
Venture 3.6.5 is now available! A formal release statement including features is pending.
The problem is that PayPal doesn’t send an eBay transaction ID along with BIN auctions. When the ebay_auction_winners table is updated by the paypal_collector.php script, one tuple of the key is missing in order to only update one record with the IPN.
To get an auction to go through, login to PayPal and search in History for the IPN that isn’t getting converted to an order. Click on the auction to open a window into eBay and click the transaction history link and login to eBay.
Look for the username in the PayPal window and find the matching transaction in the eBay window. Look at the options on the right in a menulist, and mouse over them until you see one that has the transaction ID. I usually copy the URL into an editor so I can cut-n-paste the transaction ID out of it.
Now get into the Venture database and issue the following SQL:
select * from ebay_auction_winners where ebay_transaction_id = ‘<ebay txn id>’;
There’s a few scenarios of what can be wrong:
A) The winner record has the wrong IPN # associated with the ebay_transaction_id.
FIX: Update the winner record with the correct IPN #.
B) There are multiple records that match the IPN #.
FIX: Delete all winners records that have the same IPN # as the one you’re trying to fix, but don’t have the correct ebay_transaction_id
C) The payment_method_code in the winner record is wrong. It should be ‘PayPal’.
FIX: Update the winner record’s payment_method_code setting it to ‘PayPal’.
NOTE: The tuple that uniquely identifies a record in the ebay_auction_winners table is: (ebay_auction_id, ebay_transaction_id) If you want to only update/delete one record in that table, those two fields must be present in the WHERE clause of your SQL.
There is a script that will dump 5 tab-delimited files for item and category data. The script lives on the Venture machine under the scripts directory in this subdirectory:
venture-scripts/data_import/OSCommerce/venture_osc_dump.php
It will dump the files into whatever your current working directory is. It’s probably best to ‘cd’ to the directory where the script is and run it from there.
The 5 .txt data files need to copied up to the OSCommerce machine to this directory:
/var/www/htdocs/popartmachine.com/scripts
On the devel2 machine, ‘cd’ to /var/www/htdocs/popartmachine.com/scripts and run the script osc_import_venture_data.php.
The import script also looks for the .txt files in the current working directory.
Example SQL: report LTL shipments with shipment destnation, weight and outbound freight collection amounts.
select
freight_carrier,
upper(shipping_city),
upper(shipping_locality),
(select upper(city)
from warehouse_master
where company_id = a.company_id
and warehouse_code = a.warehouse_code)
as ship_from_city,
to_char(date_confirmed, ‘DD/MM/YYY’) as date_confirmed,
(select sum(quantity_shipped * coalesce(unit_weight, 0))
from sales_order_line_items
where sales_order_header_id = a.sales_order_header_id)
as total_weight,
shipping_mailing_code,
freight_out
from sales_order_header as a
where date_confirmed > ‘2007-01-01′
and freight_carrier != ‘Fedex’
and freight_carrier != ‘UPS’
and freight_carrier != ‘USPS’
and freight_carrier != ‘N/A’
and freight_carrier != ‘Local Pickup’;
1) If you haven’t setup a user w/ eBay this needs to be done first.
2) Setup Venture configs under the eBay section. You can copy all the data from a company that is already configured to work with eBay, the only difference will be eBay user which is the config ‘ebay_request_user’. You will also want to set the config ‘ebay_template_pay_info’ with default payment instructions.
3) You will need to setup a PayPal account if one hasn’t been created already.
4) PayPal processing documentation pending.
In the scripts/digital_asset_manager directory, there is a script called “garbage_collection.php” that cleans up database records and files in the Venture file manager.
It removes files and database records that meet any of the following criteria:
Note that no inputs are supplied to this script, and it will remove files/database records without any prompting of confirmation from the user.
Venture can update item pricing from files uploaded by users. These are FTP’d to the Venture server and since every Venture installation is different, you’ll need to contact your system administrator to get FTP login information.
When you FTP up your file, there will be a main directory that Venture will look for pricing files, and your system administrator can setup subdirectories off this directory that are named the same as the Venture login of the users using the pricing service. Venture will also look for price update files in these subdirectories, and if it finds pricing files in the subdirectories, it will report in employee reports so the user can follow what is going on with her price updates and fix problems Venture reports. Updates dropped in the main directory won’t generate reports.
Venture expects tab-delimited data for price updates, and here is the layout the file must follow prefaced with the letter column they would appear in a spreadsheet program:
A: Vendor code
B: Manufacturer part #
C: UPC
D: UOM
E: Description
F: Purchase multiplier group
G: MFG List Price
H: MFG Net Price
I: Standard pack
J: Unit Weight
K: Package length
L: Package width
M: Package height
Note about MFG List Price v.s. MFG Net Price
If there is a MFG net price in your data file, this price will take precedence, even if a list price is supplied. By supplying a net price you are telling Venture that it should ignore any discount mulipliers (more about this below). If you supply a list price, Venture will multiply the price supplied by the discount multiplier in order to arrive at an item cost. Supplying a list price and setting a discount multiplier of 1 for items is functionally equivalent to using the net price field.
Note about price updates and special characters
Data is stored in Venture in the UTF-8 character set, also commonly known as UNICODE. Price updates from vendors are often in the Windows character set which is often incompatible with Venture when special characters are present. The easiest way around this is to open the price updates in a spreadsheet program that allows you to set the character encoding when you save the file. Free examples of programs that can do this are “Open Office” for Windows and Linux and “NeoOffice” for Macintosh.
Required Venture Records
There are records that must be setup in Venture before a price update can successfully load. You must have created the vendor master (Vendor->Add/Edit Vendor Record in the client), and the vendor code will need to appear in field “A” of your price update file. In the vendor master there is field called “Freight Factor”, and this is used to calculate standard cost from price update files. The price from the vendor is multiplied by the freight factor in order to determine item standard cost.
You must also create at least one “item multiplier” record (Administration->Vendors->Item Multipliers in the client) for the vendor you wish to use the price update service for. The item multiplier record contains a discount multiplier, which is used when you have vendor list price (”G”) instead of vendor net price (”H”). For instance, if your pricing file contains list pricing, and you receive a 30% discount from the vendor, your discount multiplier will be 0.7. The other field is the cost-to-list multiplier which is used for setting the base customer price on the item. If you want to set a 30% markup you would set this field to 1.3. You can have several item multiplier records for each vendor if needed, and the key for each of these is the “purchase multiplier group”, which is field “F” in your data file. Every record in the data file needs to point to a valid purchase multiplier group.
Pricing Service Internals
When you upload a price update file, Venture scans the upload directory and its subdirectories periodically, and when it finds files it attempts to load them into its database to a table named “pending_price_updates”. If a file is successfully loaded into this table, and you uploaded the file into your own subdirectory, Venture will create a report with an example 5 records so you can see if the data looks correct in the pending data. If there are problems, the report will contain an error report.
Once the file is successfully loaded into the pending_price_updates table, the user must initiate running the price update from the client. () Price update jobs are queued and run one at a time to prevent excessive load on the database from running several large price updates at once. An update can either be committed, or you can do a trial run first, look at the report from the trial run, then if you’re happy, commit the price update. Reports from the price update service show up in employee reports (Reports->My Reports in the client), and since they are queued, you will have to check your reports periodically to see the results of the run.
When a price update is successfully committed, the data is removed from pending_price_updates, and the job is removed from the queue.
Client Options
When doing a trial run, or committing a price update, the user is presented with several options on how the price update should proceed. Here are the options and a description of their purpose:
Prepend Vendor Code: If selected, when creating new item records, the item code will consist of the first three characters of the vendor code, a space, and the MFG part #, or UPC code depending on which is supplied.
Add Item Codes: If selected, records in the price update that don’t match an item in Venture will be added. Matching can either be by MFG part #, UPC code, or item code, Venture will try all three.
Options for adding items:
Accept Zero Price: Add item codes that have a zero price from the vendor.
Item Type: Set to the item type new items should be set to. Either a regular item, or a direct-ship type item.
Default Warehouse Code: Default stocking location for items in price update. Used for Ecomm to source stock, and can be changed in the client after the update.
Item Matrix: Item matrix for items in update. Used for matrix pricing, and if not supplied defaults to zero.
Items that are in Venture, but not in price update file:
Inactivate Items: If an item is in Venture, but doesn’t match any records in the price update, inactivate the item in Venture.
Reporting:
Delta Percent: Report any items whose price has changed by more than the percent supplied.
Venture 3.6.5 Release Candidate #1 enhancements:
JS XPCOM global cache
Global caching allows all Venture client windows to use a single point of cache, offering better inter-window communication potential and robust data structure storage. This is mainly a clean-up effort in 3.6.5. A proof of concept function is included, however. If you change working company or log in as new user the results will span all open Venture windows.
Vdam, Venture digital assets manager
Venture’s digital asset manager relocates to an independent database and communicates with Venture or third party applications over HTTP. The primary advantage is greater scalability. We’ve removed table level data definitions and moved to an open attribute assignment system.
Product Categories
The category table(s) schema has been rebuilt for clarification. Over the years we inadvertantly merged table data related to categories for printing and for ecommerce. That is to say, the tables started as separate entities but slowly began merging in function. We rebuilt the category tables to reflect a unified source of information for both print and ecommerce applications.
Venture’s invoice register stores line item cost of goods based on your company wide preference. In our example, our Venture user uses Standard Cost in the invoice register. What they wanted to know was what the invoice register would look like if they used Last Cost. This application was specific to tracking the difference between in warehouse cost goods (with a freight factor) vs. first cost of goods (no freight applied).
select
sum(
case
when c.last_cost > 0
then b.quantity_shipped * c.last_cost
else
b.quantity_shipped * c.standard_cost
end
) as total_last_cost
from sales_order_header as a,
sales_order_line_items as b,
item_master as c
where a.company_id = 1
and a.company_id = b.company_id
and a.company_id = c.company_id
and a.sales_order_header_id = b.sales_order_header_id
and b.item_code = c.item_code
and a.order_status = ‘A’
and b.line_disposition != ‘Z’
and b.item_type != ‘A’
and b.item_type != ‘M’
and a.date_invoiced > ‘2006-12-27′ and a.date_invoiced < '2007-08-31';
This example could be changed to reflect other cost fields and could be expanded to report only on warehouse shipments and ignore direct shipments.
On-going Venture projects at Kelly Supply include:
http://ewins.com/
Ewins relies 100% on Venture for all business processes. A new web site is in the works to take advantage of new features announced for Venture 3.6.5. Ewins executives have already submitted additional requests in preparation for Venture’s pending 4.0 roadmap announcement.
http://kscdirect.com/
KSCdirect is a new but vital project for Kelly Supply. Kelly Supply’s directors intend to increase sales volume using the direct outlet, selling to trades people outside of Kelly’s normal trade area. As the Venture 4.0 roadmap dust settles, so does the KSCdirect marketing plan. Look to see a lot of action from these guys in 2008.
If you’re programming from within the Ecomm environment, the base URI of the VDAM server is stored in EcommConfig.php and is a define named: VDAM_SERVER_URI.
The VDAM server has a script named image_resize.php which can be called in a variety of ways. Here are the GET inputs to that script:
vi : vdam_id (Overrides ic and cn)
ic : item_code - Show first image for item code
cn : category_number - Show first image for category
cid: company_id : required if getting image using ic or cn
mdx: maximum image dimension, 0 = show image in original dims
The required inputs to the script are cid, [vi|ic|cn], and optionally mdx if you want the image resized.
If for example you are trying to show the first image for an item in Ecomm resized to a maximum dimension of 200 pixels, you would build the URL like this:
$url = VDAM_SERVER_URI . “?cid=” . $company_id . “&ic=” . urlencode($item_code) . “&mdx=200″;
The first category image can be displayed by sending the ‘cn’ GET variable instead of ‘ic’:
$url = VDAM_SERVER_URI . “?cid=” . $company_id . “&cn=” . $category_number . “&mdx=200″;
Or if you have the vdam_id of the image you wish to display:
$url = VDAM_SERVER_URI . “?cid=” . $company_id . “&vi=” . $vdam_id . “&mdx=200″;
Take note that if ‘mdx’ isn’t present or is zero, the image isn’t resized and is displayed full-size.
Venture proudly powers the online operations of Lorenz and Jones Marine Distributors.
The company known today as Lorenz & Jones Marine Distributors, Inc. was founded in 1921 by Fred J. Lorenz and located at 132 E. Grand Avenue in downtown Des Moines. With a passion for boating and a strong work ethic, Fred gradually entered the newly emerging business of recreational boating in Iowa.
Fred mixed retail boating sales and repair with automotive service until 1945. At that time Bruce Jones joined him as a partner and the company was named Lorenz and Jones Boating Headquarters.
In 1954, Fred’s son Tom Lorenz, Sr. started the company down the boating equipment wholesale distribution path that it has maintained to this date. About 1960, Fred Lorenz and Bruce Jones retired, and a new partner, Mel McKee, joined Tom at the growing firm and the present name came into effect.
In 1969 the company expanded to a new 27,000 square foot building in a newly developed industrial park in Ankeny, located just a few miles north of Des Moines.
Next generation Lorenz and McKee family entered the business in the 1970’s as part-time student workers, then key-employees, and eventually as hands-on operating management and owners.
The wholesale accessory business continued to grow and expand into an increasingly larger market area. The resulting increases in product offerings, inventory depth and number of employees placed new demands on building space.
In 2001, Lorenz and Jones moved into its current location in Ankeny’s Metro North I Business Park. The new location enabled the company to add the needed office and warehouse space to ensure effective customer service now and into the future.
This new larger location with high-rack and multi-level storage capacity, modern processing equipment and computerized warehouse system allow the company to effectively manage shipments received from marine vendors and quickly process and ship orders to a steadily growing dealer client base. Daily scheduled pick-up from UPS, FedEx, Speed-Dee Delivery and others allow next day or two-day ground delivery in most of the market area served.
If you encounter the error:
ERROR: Last month-end timestamp doesn’t exist, please fix manually in month_end_dates
The problem is in the table month_end_dates. You need to manually set the last time that month-end was last run. If there isn’t a record for the company for the current year, first set your month-end dates from the Venture client in the Accounting module. This can be found under the main menu:
Administration->A/R->Fiscal Month-End Dates
If the month_dates_record for the current year exists, the field you need to UPDATE is:
month_MM_timestamp
Where “MM” is the numeric month. For example, if month-end is failing in August, then you need to set when it ran in July. Here’s some example SQL where we set the last month-end for July 25th, 2007 at 3:00PM:
UPDATE month_end_dates SET month_07_timestamp = ‘2007-07-25 15:00′;
This will set it for all companies, and since day-end runs at the same time for all companies, this is the correct way to do it.
There is a script on vent-isco.kdsi.net for deleting items by vendor code. The script lives here:
/raid/htdocs/venture-scripts/fixup/delete_items_by_vendor_code.php
To run the script: php delete_items_by_vendor_code.php <company id> <vendor code>
NOTE: This script must be run as the user ‘postgres’
For example, to delete all items for the vendor MOENIN from the database for company 3 you would issue the command:
php delete_items_by_vendor_code.php.php 3 MOENIN
Be aware that the script takes awhile to run if there are several thousand item codes matching the vendor code, and that some supporting data, like categories aren’t deleted. So if you have a category dedicated to a vendor code, you’ll have to go into Venture and remove that category by hand.
J.H. Larson Company is a family owned independent distributor of Electrical, Plumbing, HVAC and Data Comm products which has been in business since 1931. Venture powers J.H. Larson’s direct to consumer sales and wholesale distribution web site.
Current projects include an online customer service center for over 800,000 item codes from their diverse product offerings. Venture and its components are leading the way!
Venture is powering over twenty AmSan/IBI property ecommerce systems.
AmSan and Interline Brands, Inc enjoy an extended sub-contract arrangement to build and extend ecommerce user interface and large scale back-office API for several legacy systems throughout their enterprises.
A detailed project summary is coming soon.
IDC-USA is a member-owned purchasing, marketing, and selling cooperative of over 200 independent distribution locations across the nation. IDC-USA has teamed up with over 60 suppliers to serve end-users with the highest-quality bearing and power transmission products available. These IDC Preferred Suppliers have access to the IDC-USA independent distributor network with one shipping location, one invoice, one account receivable account, and national market penetration.
Venture powers www.IDC-USA.com and IDC Member-Owners have 24/7 access to place orders and check pricing, availability, invoices, usage reports, and sales history. IDC Member-Owners provide to their customer base lower costs, less down time, outstanding local service, multiple location coverage, and 2nd & 3rd generation expertise.
Replace dates and vendor codes accordingly.
To report on item type records:
– Get “I” type items
select sum(cast(b.unit_price * b.quantity_ordered as numeric(15,2))) as total_price,
sum(cast(b.unit_cost * b.quantity_ordered as numeric(15,2))) as total_cost
from sales_order_header as a, sales_order_line_items as b, item_master as c
where a.sales_order_header_id = b.sales_order_header_id
and b.item_code = c.item_code
and a.company_id = c.company_id
and c.vendor_code = ‘XXXX’
and b.item_type = ‘I’
and a.date_invoiced between ‘12-27-2005′ and ‘07-24-2006′;
To report on assemblies:
– Get price on assemblies
select sum(cast(b.unit_price * b.quantity_ordered as numeric(15,2))) as assembly_total_price
from sales_order_header as a, sales_order_line_items as b, item_master as c
where a.sales_order_header_id = b.sales_order_header_id
and b.item_code = c.item_code
and a.company_id = c.company_id
and c.vendor_code = ‘XXXXX’
and b.item_type = ‘A’
and a.date_invoiced between ‘12-27-2005′ and ‘07-24-2006′;
– Get cost on assemblies
select sum(cast(b.unit_cost * b.quantity_ordered as numeric(15,2))) as assembly_total_cost
from sales_order_header as a, sales_order_line_items as b, item_master as c
where a.sales_order_header_id = b.sales_order_header_id
and b.item_code = c.item_code
and a.company_id = c.company_id
and c.vendor_code = ‘XXXXX’
and b.item_type = ‘C’
and a.date_invoiced between ‘12-27-2005′ and ‘07-24-2006′;
Item Images:
item_code, image_caption, ebay_thumb
Item Supplemental Files:
item_code, file_description
Category Images:
category_number, image_caption
|
