language: node_js
node_js:
- - "0.8"
+ - "0.10"
install:
- "bash scripts/install_xtuple.sh -ipn"
- "npm run-script test-datasource"
- "npm run-script test"
- "npm run-script jshint"
-
- # test an upgrade from 4.4.0
- - "wget http://sourceforge.net/projects/postbooks/files/03%20PostBooks-databases/4.4.0/postbooks_demo-4.4.0.backup"
- - "./scripts/build_app.js -d upgrade_test -i -b ./postbooks_demo-4.4.0.backup"
recordType: 'XM.SiteListItem',
- editableModel: 'XM.Site'
+ editableModel: 'XM.Site',
+
+ couldCreate: function () {
+ // Look to see if there are sites in the sites cache. If so, restrict new for Postbooks.
+ if (!XM.sites.length) {
+ return XM.Info.prototype.couldCreate.apply(this, arguments);
+ }
+ return false;
+ }
});
{kind: "onyx.GroupboxHeader", content: "_overview".loc()},
{kind: "XV.ScrollableGroupbox", name: "mainGroup", fit: true,
classes: "in-panel", components: [
- {kind: "XV.InputWidget", attr: "code"},
- {kind: "XV.CheckboxWidget", attr: "isActive"},
- {kind: "XV.SiteTypePicker", attr: "siteType"},
- {kind: "XV.InputWidget", attr: "description"},
- {kind: "XV.ContactWidget", attr: "contact"},
- {kind: "XV.AddressWidget", attr: "address"},
- {kind: "XV.TaxZonePicker", attr: "taxZone"},
- {kind: "XV.InputWidget", attr: "incoterms"},
- {kind: "onyx.GroupboxHeader", content: "_notes".loc()},
- {kind: "XV.TextArea", attr: "notes", fit: true}
+ {name: "mainSubgroup", components: [ // not a scroller, so we can addBefore
+ {kind: "XV.InputWidget", attr: "code"},
+ {kind: "XV.CheckboxWidget", attr: "isActive"},
+ {kind: "XV.SiteTypePicker", attr: "siteType"},
+ {kind: "XV.InputWidget", attr: "description"},
+ {kind: "XV.ContactWidget", attr: "contact", name: "contactWidget"},
+ {kind: "XV.AddressWidget", attr: "address"}
+ ]}
]}
]},
- {kind: "XV.SiteCommentBox", attr: "comments"}
+ {kind: "XV.SiteCommentBox", attr: "comments", name: "commentsPanel"}
]}
]
});
XV.registerModelWorkspace("XM.UserAccountRoleRelation", "XV.UserAccountRoleWorkspace");
XV.registerModelWorkspace("XM.UserAccountRoleListItem", "XV.UserAccountRoleWorkspace");
-
}());
{
"name": "customerType",
"toOne": {
+ "isNested": true,
"type": "CustomerType",
"column": "cust_custtype_id"
}
"attr": {
"type": "String",
"column": "warehous_code",
- "isNaturalKey":true
+ "isNaturalKey":true,
+ "required": true
}
},
{
"toOne": {
"isNested": true,
"type": "SiteType",
- "column": "warehous_sitetype_id"
+ "column": "warehous_sitetype_id",
+ "required": true
}
},
{
"column": "warehous_fob"
}
},
- {
- "name": "notes",
- "attr": {
- "type": "String",
- "column": "warehous_shipcomments"
- }
- },
{
"name": "comments",
"toMany": {
/* Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
See www.xm.ple.com/CPAL for the full text of the software license. */
- var result,
- viewname,
- schemaname,
- i;
-
- sql = "select schemaname, viewname from pg_views where schemaname in ('xm','sys', 'xt');"
- result = plv8.execute(sql);
- for (i = 0; i < result.length; i++) {
- viewname = result[i].viewname;
- schemaname = result[i].schemaname;
- plv8.execute('drop view if exists ' + schemaname + '.' + viewname + ' cascade;');
- }
-
plv8.execute("select xt.js_init()");
plv8.execute("alter table xt.orm disable trigger orm_did_change");
plv8.execute("delete from xt.orm where orm_json ~ '\"isSystem\":true';");
--- /dev/null
+DO $$
+ /* Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+ See www.xm.ple.com/CPAL for the full text of the software license. */
+
+ var sql,
+ result,
+ viewname,
+ schemaname,
+ i;
+
+ sql = "select schemaname, viewname from pg_views where schemaname in ('xm','sys', 'xt');"
+ result = plv8.execute(sql);
+ for (i = 0; i < result.length; i++) {
+ viewname = result[i].viewname;
+ schemaname = result[i].schemaname;
+ plv8.execute('drop view if exists ' + schemaname + '.' + viewname + ' cascade;');
+ }
+
+$$ language plv8;
where aropen_cust_id = $1
and aropen_open
--and aropen_posted = false
- group by aropen_id
+ group by aropen_id, aropen_cust_id, aropen_curr_id, aropen_amount
) unalloc;
$$ language sql;
itemsite_location_comments,
itemsite_notes,
itemsite_perishable,
- itemsite_nnqoh,
itemsite_autoabcclass,
itemsite_ordergroup,
itemsite_disallowblankwip,
new.itemsite_location_comments,
new.itemsite_notes,
coalesce(new.itemsite_perishable, false),
- 0,
coalesce(new.itemsite_autoabcclass, false),
coalesce(new.itemsite_ordergroup, 1),
coalesce(new.itemsite_disallowblankwip, false),
-select xt.add_report_definition('XM.Invoice', 0, $${
+select xt.add_report_definition('XM.Invoice', 0, $${
"settings": {
"detailAttribute": "lineItems",
"defaultFontSize": 12,
},
{"element": "bandLine", "size": 2},
{
+ "element": "band",
"definition": [
- {"attr": "subtotal", "label": true},
- {"attr": "taxTotal", "label": true},
- {"attr": "total", "label": true}
+ {"text": "_subtotal", "label": true, "width": 70, "align": "left"},
+ {"attr": "subtotal", "width": 100, "align": "right"}
],
- "options": {"width": 525, "align": "right"}
+ "options": {"border": 0, "x": 360}
+ },
+ {
+ "element": "band",
+ "definition": [
+ {"text": "_taxTotal", "label": true, "width": 70, "align": "left"},
+ {"attr": "taxTotal", "width": 100, "align": "right"}
+ ],
+ "options": {"border": 0, "x": 360}
+ },
+ {
+ "element": "band",
+ "definition": [
+ {"text": "_total", "label": true, "width": 70, "align": "left"},
+ {"attr": "total", "width": 100, "align": "right"}
+ ],
+ "options": {"border": 0, "x": 360}
+ },
+ {
+ "definition": []
}
],
"pageFooterElements": [
"_issued": "Issued",
"_fullListUrl": "Full List URL",
"_generatingPrivateKey": "A new keypair will be generated for this OAUTH2 client. " +
- "The public key will be saved in the database with this client. The private key " +
- "is available as a one-time download. The password for the key store file will be " +
- "\"notasecret\". Click \"ok\" to downloading the private key.",
+ "The public key will be available in the future with this client. The private key " +
+ "is only available now as a one-time download. Note that this process can take up " +
+ "to a minute. Please wait until the key is downloaded.",
"_logoURL": "Logo URL",
"_maintainOauth2clients": "Maintain OAUTH2 Clients",
"_oauth2": "OAUTH2",
"public/functions/purgepostedcounttags.sql",
"public/functions/purgeshipments.sql",
"public/functions/qtyallocated.sql",
+ "public/functions/qtyatlocation.sql",
"public/functions/qtyatshipping.sql",
"public/functions/qtyavailable.sql",
"public/functions/qtyinshipment.sql",
"public/functions/qtylocation.sql",
+ "public/functions/qtynetable.sql",
"public/functions/qtyordered.sql",
"public/functions/qtypr.sql",
"public/functions/qtyreserved.sql",
"public/tables/ccpay.sql",
"public/tables/ccbank.sql",
"public/tables/checkhead.sql",
+ "public/tables/itemsite.sql",
+ "public/tables/location.sql",
"public/tables/metric.sql",
"public/tables/payco.sql",
"public/tables/priv.sql",
"public/tables/tax.sql",
"public/tables/taxpay.sql",
- "public/tables/location.sql",
"public/views/address.sql",
"public/views/apmemo.sql",
"public/views/armemo.sql",
"public/tables/report/items.xml",
"public/patches/fixacl.sql",
- "public/patches/populate_ccpay_card_type.sql"
+ "public/patches/populate_ccpay_card_type.sql",
+
+ "public/tables/setVersion.sql"
]
}
-1,
NULL,
_fc.fincharg_markoninvoice,
- 'Finance Charge Assessment',
+-- - enhance data shown in the item description for the invoice line to indicate which invoice is affected
+-- - Feature Request 23344
+-- 'Finance Charge Assessment',
+ 'Finance Charge Assessment - Invoice Number ' || _ar.aropen_docnumber || ' - Past Due Balance ' || (_ar.aropen_amount - _ar.aropen_paid) || ' Due Date - ' || _ar.aropen_duedate,
1.0,
1.0,
pAssessAmount,
DECLARE
_value NUMERIC;
_qoh NUMERIC;
- _qohnn NUMERIC;
BEGIN
- SELECT itemsite_value, itemsite_qtyonhand, itemsite_nnqoh
- INTO _value, _qoh, _qohnn
+ SELECT itemsite_value, itemsite_qtyonhand
+ INTO _value, _qoh
FROM itemsite
WHERE(itemsite_id=pItemsiteid);
- IF (_qoh = 0.0 AND _qohnn = 0.0) THEN
+ IF (_qoh = 0.0) THEN
RETURN 0.0;
END IF;
- RETURN _value / (_qoh + _qohnn);
+ RETURN _value / _qoh;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION balanceItemsite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION balanceItemsite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_itemlocseries INTEGER;
_balanced NUMERIC;
_qoh NUMERIC;
- _nnQoh NUMERIC;
BEGIN
RETURN -1;
END IF;
--- Calculate the Netable portion
+-- Calculate the qoh
SELECT COALESCE(SUM(itemloc_qty), 0) INTO _balanced
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
--- Post an AD Transaction for the Netable portion
+-- Post an AD Transaction
SELECT invAdjustment( itemsite_id, (_balanced - itemsite_qtyonhand),
'Balance', 'Inventory Balance' ) INTO _itemlocseries
FROM itemsite
DELETE FROM itemlocdist
WHERE (itemlocdist_series=_itemlocseries);
--- Calculate and write the Non-Netable portion directly
- SELECT COALESCE(SUM(itemloc_qty), 0) INTO _nnQoh
- FROM itemloc, location
- WHERE ( (itemloc_location_id=location_id)
- AND (NOT location_netable)
- AND (itemloc_itemsite_id=pItemsiteid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = _nnQoh
- WHERE (itemsite_id=pItemsiteid);
-
RETURN 1;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
pQuheadid ALIAS FOR $1;
+ _qunumber TEXT;
+ _ponumber TEXT;
_soheadid INTEGER;
_soitemid INTEGER;
_orderid INTEGER;
RETURN -5;
END IF;
- IF ( (_usespos) AND (NOT _blanketpos) ) THEN
- PERFORM cohead_id
- FROM quhead JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
- (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
+ IF (_usespos) THEN
+ SELECT quhead_number, COALESCE(quhead_custponumber, ''), cohead_id INTO _qunumber, _ponumber, _soheadid
+ FROM quhead LEFT OUTER JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
+ (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
WHERE (quhead_id=pQuheadid);
- IF (FOUND) THEN
- RAISE EXCEPTION 'Duplicate Customer PO';
+ IF (_ponumber = '') THEN
+ RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
+ _qunumber, _qunumber;
+ END IF;
+
+ IF ( (NOT _blanketpos) AND (_soheadid IS NOT NULL) ) THEN
+ RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
+ _ponumber, _qunumber,
+ _ponumber, _qunumber;
END IF;
END IF;
AND (comment_source_id=pQuheadid) );
FOR _r IN SELECT quitem.*,
- quhead_number, quhead_prj_id,
+ quhead_number, quhead_prj_id, quhead_saletype_id,
itemsite_item_id, itemsite_leadtime,
itemsite_createsopo, itemsite_createsopr,
item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
IF (fetchMetricBool('enablextcommissionission')) THEN
PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
- _r.itemsite_item_id, _r.quitem_price,
+ _r.itemsite_item_id, _r.quhead_saletype_id,
+ _r.quitem_price, _r.quitem_custprice,
_soitemid, 'SalesItem')
FROM quhead
WHERE (quhead_id=pQuheadid);
IF (_r.quitem_createorder) THEN
IF (_r.item_type IN ('M')) THEN
- SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
- _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
+ SELECT createWo( CAST(_soNum AS INTEGER), supply.itemsite_id, 1,
+ (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
+ _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo,
+ 'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
FROM itemsite sold, itemsite supply
WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
AND (charass_target_id=_r.quitem_id));
ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
- SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
+ SELECT createPr( CAST(_soNum AS INTEGER), _r.quitem_itemsite_id,
+ (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
_r.quitem_scheddate, '', 'S', _soitemid ) INTO _orderId;
_orderType := 'R';
UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
pQuheadid ALIAS FOR $1;
+ _qunumber TEXT;
+ _ponumber TEXT;
_iheadid INTEGER;
_iitemid INTEGER;
_orderid INTEGER;
END IF;
-- PO/blanket PO checks
+ IF (_usespos) THEN
+ SELECT quhead_number, COALESCE(quhead_custponumber, ''), invchead_id INTO _qunumber, _ponumber, _iheadid
+ FROM quhead LEFT OUTER JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
+ (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
+ WHERE (quhead_id=pQuheadid);
+ IF (_ponumber = '') THEN
+ RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
+ _qunumber, _qunumber;
+ END IF;
+
+ IF ( (NOT _blanketpos) AND (_iheadid IS NOT NULL) ) THEN
+ RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
+ _ponumber, _qunumber,
+ _ponumber, _qunumber;
+ END IF;
+ END IF;
+
+ IF (_usespos) THEN
+ SELECT quhead_number INTO _qunumber
+ FROM quhead
+ WHERE (quhead_id=pQuheadid)
+ AND (COALESCE(quhead_custponumber, '') = '');
+ IF (FOUND) THEN
+ RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
+ _qunumber, _qunumber;
+ END IF;
+ END IF;
+
IF ( (_usespos) AND (NOT _blanketpos) ) THEN
- PERFORM invchead_id
+ SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
FROM quhead JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
- (UPPER(invchead_custponumber)=UPPER(quhead_custponumber)) )
+ (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
WHERE (quhead_id=pQuheadid);
IF (FOUND) THEN
- RAISE EXCEPTION 'Duplicate Customer PO';
+ RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
+ _ponumber, _qunumber,
+ _ponumber, _qunumber;
END IF;
END IF;
-
+
--Check to see if an invoice exists with the quote number
PERFORM quhead_number, invchead_id
*/
FOR _r IN SELECT quitem.*,
- quhead_number, quhead_prj_id,
+ quhead_number, quhead_prj_id, quhead_saletype_id,
itemsite_item_id, itemsite_leadtime,
itemsite_createsopo, itemsite_createsopr,
item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
IF (fetchMetricBool('enablextcommissionission')) THEN
PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
- _r.itemsite_item_id, _r.quitem_price,
+ _r.itemsite_item_id, _r.quhead_saletype_id,
+ _r.quitem_price, _r.quitem_custprice,
_iitemid, 'InvoiceItem')
FROM quhead
WHERE (quhead_id=pQuheadid);
-CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
- pdestwhsid INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION copyItemSite(pItemsiteid INTEGER,
+ pDestWhsid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
- RETURN copyItemSite(pitemsiteid, pdestwhsid, NULL);
+ RETURN copyItemSite(pItemsiteid, pDestWhsid, NULL);
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
- pdestwhsid INTEGER,
- pdestitemid INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION copyItemSite(pItemsiteid INTEGER,
+ pDestWhsid INTEGER,
+ pDestItemid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pitemsiteid ALIAS FOR $1;
- pdestwhsid ALIAS FOR $2;
_destwhs whsinfo%ROWTYPE;
_new itemsite%ROWTYPE;
_supplywarehousid INTEGER := NULL;
-- make a copy of the old itemsite
SELECT * INTO _new
FROM itemsite
- WHERE (itemsite_id=pitemsiteid);
+ WHERE (itemsite_id=pItemsiteid);
IF (NOT FOUND) THEN
RETURN -1;
END IF;
-- if there is no dest warehouse then perhaps the user is manually copying it
- IF (pdestwhsid IS NOT NULL) THEN
+ IF (pDestWhsid IS NOT NULL) THEN
SELECT * INTO _destwhs
FROM whsinfo
- WHERE (warehous_id=pdestwhsid);
+ WHERE (warehous_id=pDestWhsid);
IF (NOT FOUND) THEN
RETURN -2;
END IF;
SELECT itemsite_id INTO _new.itemsite_id
FROM itemsite
- WHERE ((itemsite_item_id=COALESCE(pdestitemid, _new.itemsite_item_id))
- AND (itemsite_warehous_id=pdestwhsid OR
- (itemsite_warehous_id IS NULL AND pdestwhsid IS NULL)));
+ WHERE ((itemsite_item_id=COALESCE(pDestItemid, _new.itemsite_item_id))
+ AND (itemsite_warehous_id=pDestWhsid OR
+ (itemsite_warehous_id IS NULL AND pDestWhsid IS NULL)));
IF (FOUND) THEN
RETURN _new.itemsite_id;
END IF;
SELECT itemsite_id INTO _new.itemsite_supply_itemsite_id
FROM itemsite
WHERE (itemsite_warehous_id=_supplywarehousid)
- AND (itemsite_item_id=pdestitemid);
+ AND (itemsite_item_id=pDestItemid);
END IF;
END IF;
-- now override the things we know have to change
_new.itemsite_id := NEXTVAL('itemsite_itemsite_id_seq');
- _new.itemsite_warehous_id := pdestwhsid;
+ _new.itemsite_warehous_id := pDestWhsid;
_new.itemsite_qtyonhand := 0;
_new.itemsite_value := 0;
_new.itemsite_datelastcount := NULL;
_new.itemsite_datelastused := NULL;
- _new.itemsite_nnqoh := 0;
_new.itemsite_location_id := -1;
_new.itemsite_recvlocation_id := -1;
_new.itemsite_issuelocation_id := -1;
_new.itemsite_location := NULL;
_new.itemsite_location_comments := NULL;
_new.itemsite_notes := 'Transit Warehouse';
- _new.itemsite_nnqoh := 0;
_new.itemsite_createwo := FALSE;
_new.itemsite_costcat_id := _destwhs.warehous_costcat_id;
_new.itemsite_supply_itemsite_id := NULL;
itemsite_soldranking, itemsite_createpr,
itemsite_location, itemsite_location_comments,
itemsite_notes, itemsite_perishable,
- itemsite_nnqoh, itemsite_autoabcclass,
+ itemsite_autoabcclass,
itemsite_ordergroup, itemsite_disallowblankwip,
itemsite_maxordqty, itemsite_mps_timefence,
itemsite_createwo, itemsite_warrpurc,
itemsite_location_dist, itemsite_recvlocation_dist,
itemsite_issuelocation_dist
) VALUES (
- _new.itemsite_id, COALESCE(pdestitemid, _new.itemsite_item_id),
+ _new.itemsite_id, COALESCE(pDestItemid, _new.itemsite_item_id),
_new.itemsite_warehous_id, _new.itemsite_qtyonhand,
_new.itemsite_costmethod, _new.itemsite_value,
_new.itemsite_reorderlevel, _new.itemsite_ordertoqty,
_new.itemsite_soldranking, _new.itemsite_createpr,
_new.itemsite_location, _new.itemsite_location_comments,
_new.itemsite_notes, _new.itemsite_perishable,
- _new.itemsite_nnqoh, _new.itemsite_autoabcclass,
+ _new.itemsite_autoabcclass,
_new.itemsite_ordergroup, _new.itemsite_disallowblankwip,
_new.itemsite_maxordqty, _new.itemsite_mps_timefence,
_new.itemsite_createwo, _new.itemsite_warrpurc,
RETURN _new.itemsite_id;
END;
-$$ LANGUAGE 'plpgsql';
\ No newline at end of file
+$$ LANGUAGE plpgsql;
\ No newline at end of file
RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, NULL);
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC) RETURNS INTEGER AS $$
RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, pPrice);
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC, DATE, NUMERIC) RETURNS INTEGER AS $$
RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, pQty, pDueDate, pPrice, NULL);
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC, DATE, NUMERIC, INTEGER) RETURNS INTEGER AS $$
FROM pohead
WHERE ( (pohead_status = 'U')
AND (pohead_vend_id = _i.itemsrc_vend_id)
- AND (pohead_shiptoname = COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''))
- AND (pohead_shiptoaddress1 = COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''))
- AND (pohead_shiptoaddress2 = COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''))
- AND (pohead_shiptoaddress3 = COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''))
- AND (pohead_shiptocity = COALESCE(_s.cohead_shiptocity, _s.addr_city, ''))
- AND (pohead_shiptostate = COALESCE(_s.cohead_shiptostate, _s.addr_state, ''))
- AND (pohead_shiptozipcode = COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''))
- AND (pohead_shiptocountry = COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''))
+ AND (COALESCE(pohead_shiptoname, '') = COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''))
+ AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''))
+ AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''))
+ AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''))
+ AND (COALESCE(pohead_shiptocity, '') = COALESCE(_s.cohead_shiptocity, _s.addr_city, ''))
+ AND (COALESCE(pohead_shiptostate, '') = COALESCE(_s.cohead_shiptostate, _s.addr_state, ''))
+ AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''))
+ AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''))
AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
ELSE
SELECT COALESCE(pohead_id, -1) INTO _temp
FROM pohead
WHERE ( (pohead_status = 'U')
AND (pohead_vend_id = _i.itemsrc_vend_id)
- AND (pohead_shiptoaddress1 = COALESCE(_w.addr_line1, ''))
- AND (pohead_shiptoaddress2 = COALESCE(_w.addr_line2, ''))
- AND (pohead_shiptoaddress3 = COALESCE(_w.addr_line3, ''))
- AND (pohead_shiptocity = COALESCE(_w.addr_city, ''))
- AND (pohead_shiptostate = COALESCE(_w.addr_state, ''))
- AND (pohead_shiptozipcode = COALESCE(_w.addr_postalcode, ''))
- AND (pohead_shiptocountry = COALESCE(_w.addr_country, ''))
+ AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_w.addr_line1, ''))
+ AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_w.addr_line2, ''))
+ AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_w.addr_line3, ''))
+ AND (COALESCE(pohead_shiptocity, '') = COALESCE(_w.addr_city, ''))
+ AND (COALESCE(pohead_shiptostate, '') = COALESCE(_w.addr_state, ''))
+ AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_w.addr_postalcode, ''))
+ AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_w.addr_country, ''))
AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
END IF;
RETURN _poitemid;
END;
-$$ LANGUAGE 'plpgsql' VOLATILE;
+$$ LANGUAGE plpgsql VOLATILE;
-CREATE OR REPLACE FUNCTION deleteItemSite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION deleteItemSite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_result INTEGER;
_lotserial BOOLEAN;
_bbom BOOLEAN;
BEGIN
- IF ( ( SELECT ( (itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0) )
+ IF ( ( SELECT (itemsite_qtyonhand <> 0)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) ) THEN
RETURN -9;
RETURN 0;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION detailedNNQOH(INTEGER, BOOLEAN) RETURNS NUMERIC AS '
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
+--
+-- Deprecated
+--
DECLARE
pItemsiteid ALIAS FOR $1;
pABS ALIAS FOR $2;
-CREATE OR REPLACE FUNCTION detailedQOH(INTEGER, BOOLEAN) RETURNS NUMERIC AS '
+CREATE OR REPLACE FUNCTION detailedQOH(pItemsiteid INTEGER,
+ pABS BOOLEAN) RETURNS NUMERIC AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pABS ALIAS FOR $2;
_qoh NUMERIC;
BEGIN
IF (pABS) THEN
SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
ELSE
SELECT SUM(itemloc_qty) INTO _qoh
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
END IF;
IF (_qoh IS NULL) THEN
- _qoh := 0;
+ _qoh := 0.0;
END IF;
RETURN _qoh;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION distributeItemlocSeries(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION distributeItemlocSeries(pItemlocSeries INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemlocSeries ALIAS FOR $1;
_distCounter INTEGER;
_itemlocdist RECORD;
_itemlocid INTEGER;
WHERE (itemloc_id=_itemlocid);
END IF;
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM itemloc, location
- WHERE ( (itemloc_location_id=location_id)
- AND (itemloc_id=_itemlocid) ) ) THEN
-
--- Record the netable->non-netable (or visaveras) invhist
- SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
- INSERT INTO invhist
- ( invhist_id, invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after,
- invhist_series )
- SELECT _invhistid, itemsite_id,
- 'NN', (_itemlocdist.qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
- invhist_docnumber, invhist_comments,
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value,
- (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(itemsite_item_id)
- END)),
- _itemlocdist.series
- FROM item, itemsite, invhist, uom
- WHERE ((itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_itemlocdist.itemsiteid)
- AND (invhist_id=_itemlocdist.invhistid));
-
--- Adjust the parent itemsite
- IF (NOT _itemlocdist.itemsite_freeze) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
- itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
- FROM itemloc
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid));
- END IF;
- END IF;
-
END IF;
-- If, after the distribution, the target itemloc_qty = 0, delete the itemloc
RETURN _distCounter;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION distributeToLocations(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION distributeToLocations(pItemlocdistid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemlocdistid ALIAS FOR $1;
_distCounter INTEGER;
_itemlocdist RECORD;
_itemlocid INTEGER;
END IF;
END IF;
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM itemloc, location
- WHERE ((itemloc_location_id=location_id)
- AND (itemloc_id=_itemlocid)) ) THEN
-
--- Record the invhist record for the netable->non-netable (or visaversa)
- INSERT INTO invhist
- ( invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after,
- invhist_series )
- SELECT itemsite_id,
- 'NN', (_itemlocdist.qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
- invhist_docnumber, invhist_comments,
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value,
- (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(itemsite_item_id)
- END)),
- _itemlocdist.series
- FROM item, itemsite, invhist, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_itemlocdist.itemsiteid)
- AND (invhist_id=_itemlocdist.invhistid) );
-
--- Update the itemsite_qoh
- IF (NOT _itemlocdist.itemsite_freeze) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
- itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
- FROM itemloc
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid));
- END IF;
- END IF;
-
-- Cache the running qty.
_runningQty := _runningQty + _itemlocdist.qty;
RETURN _distCounter;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
AND (invcitem_item_id=itemsite_item_id)
AND (invcitem_warehous_id=itemsite_warehous_id)
AND (invcitem_linenumber=coitem_linenumber))) > 0)) THEN 'P'
- WHEN (coitem_status='O' AND (itemsite_qtyonhand - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyAvailable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ qtyOrdered(itemsite_id, CURRENT_DATE))
>= ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio)) THEN 'R'
ELSE coitem_status END
--get top level works orders
FOR _x IN
SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,
- wo_duedate,wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,
+ wo_duedate,wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,
wo_qtyord,wo_qtyrcv,wo_prodnotes, item_number,
item_descrip1, item_descrip2, item_listprice, uom_name
FROM wo, itemsite, item, uom
_row.wodata_itemsite_id := _x.wo_itemsite_id;
_row.wodata_custprice := _x.item_listprice;
_row.wodata_listprice := _x.item_listprice;
- _row.wodata_qoh := _x.itemsite_qtyonhand;
+ _row.wodata_qoh := _x.availableqoh;
_row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
_row.wodata_qtyrcv := _x.wo_qtyrcv;
_row.wodata_qtyordreq := _x.wo_qtyord;
_level := (plevel + 1);
--find all WO with the ordid of the next level up
_qry := 'SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,wo_duedate,
- wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,wo_qtyord,wo_qtyrcv, wo_prodnotes,
+ wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,wo_qtyord,wo_qtyrcv, wo_prodnotes,
item_number,item_descrip1, item_descrip2, item_listprice, uom_name,
womatl_qtyiss, womatl_scrap, womatl_wooper_id
FROM itemsite, wo, item, uom, womatl
_row.wodata_itemsite_id := _x.wo_itemsite_id;
_row.wodata_custprice := _x.item_listprice;
_row.wodata_listprice := _x.item_listprice;
- _row.wodata_qoh := _x.itemsite_qtyonhand;
+ _row.wodata_qoh := _x.availableqoh;
_row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
_row.wodata_qtyiss := _x.womatl_qtyiss;
_row.wodata_qtyrcv := _x.wo_qtyrcv;
_qry := 'SELECT womatl_id, wo_number, wo_subnumber,
wo_startdate, womatl_duedate, womatl_itemsite_id,
- itemsite_qtyonhand, womatl_qtyreq, womatl_qtyiss,
+ qtyAvailable(itemsite_id) AS availableqoh, womatl_qtyreq, womatl_qtyiss,
womatl_qtyper, womatl_qtyreq, womatl_scrap,
womatl_ref, womatl_notes, womatl_price, item_listprice,
item_number, item_descrip1, item_descrip2,
_subrow.wodata_itemsite_id := _subx.womatl_itemsite_id;
_subrow.wodata_custprice := _subx.womatl_price;
_subrow.wodata_listprice := _subx.item_listprice;
- _subrow.wodata_qoh := _subx.itemsite_qtyonhand;
- IF((_subx.itemsite_qtyonhand > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
+ _subrow.wodata_qoh := _subx.availableqoh;
+ IF((_subx.availableqoh > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
_subrow.wodata_short := 0;
ELSE
- _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.itemsite_qtyonhand;
+ _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.availableqoh;
END IF;
_subrow.wodata_qtyper := _subx.womatl_qtyper;
_subrow.wodata_qtyiss := _subx.womatl_qtyiss;
-CREATE OR REPLACE FUNCTION initialDistribution(INTEGER, INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION initialDistribution(pItemsiteid INTEGER,
+ pLocationid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pLocationid ALIAS FOR $2;
_itemlocid INTEGER;
_invhistid INTEGER;
_itemlocSeries INTEGER;
( _invhistid, pLocationid, _r.itemloc_ls_id,
_r.itemloc_qty, 0, _r.itemloc_qty );
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM location
- WHERE (location_id=pLocationid) ) THEN
-
- INSERT INTO invhist
- ( invhist_itemsite_id, invhist_series,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after )
- SELECT itemsite_id, _itemlocSeries,
- 'NN', (_r.itemloc_qty * -1),
- _r.itemloc_qty, 0,
- 'Initial Distribution',
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value, itemsite_value
- FROM itemsite, item, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=pItemsiteid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = (itemsite_nnqoh + _r.itemloc_qty),
- itemsite_qtyonhand = (itemsite_qtyonhand - _r.itemloc_qty)
- WHERE (itemsite_id=pItemsiteid);
-
- END IF;
-
END LOOP;
ELSE
FROM itemsite
WHERE (itemsite_id=pItemsiteid);
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM location
- WHERE (location_id=pLocationid) ) THEN
-
- INSERT INTO invhist
- ( invhist_itemsite_id, invhist_series,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after )
- SELECT itemsite_id, _itemlocSeries,
- 'NN', (itemloc_qty * -1),
- itemloc_qty, 0,
- 'Initial Distribution',
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value, itemsite_value
- FROM itemloc, itemsite, item, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = itemsite_qtyonhand,
- itemsite_qtyonhand = 0
- FROM itemloc
- WHERE ( (itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid) );
-
- END IF;
-
END IF;
RETURN _itemlocid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION itemInventoryUOMInUse(INTEGER) RETURNS BOOLEAN AS '
+CREATE OR REPLACE FUNCTION itemInventoryUOMInUse(pItemid INTEGER) RETURNS BOOLEAN AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemid ALIAS FOR $1;
_uomid INTEGER;
_result INTEGER;
BEGIN
SELECT itemsite_id INTO _result
FROM itemsite WHERE ( (itemsite_item_id=pItemid)
- AND ((itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0)) )
+ AND (itemsite_qtyonhand <> 0) )
LIMIT 1;
IF (FOUND) THEN
RETURN TRUE;
RETURN FALSE;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
SELECT dropIfExists('FUNCTION', 'postCountTag(integer, boolean, text)', 'public');
-CREATE OR REPLACE FUNCTION postCountTag(INTEGER, BOOLEAN) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION postCountTag(pInvcntid INTEGER,
+ pThaw BOOLEAN) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pInvcntid ALIAS FOR $1;
- pThaw ALIAS FOR $2;
_avgCostingMethod TEXT;
_invhistid INTEGER;
_postDate TIMESTAMP;
-- Avoid negative value when average cost item
UPDATE itemsite
SET itemsite_qtyonhand=_p.invcnt_qoh_after,
- itemsite_nnqoh = 0,
- itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
+ itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND
+ (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
ELSE (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand)))
END,
itemsite_datelastcount=_postDate
END IF;
-- Distribute to G/L
- PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
+ PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
+ ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
( (_p.invcnt_qoh_after - _p.itemsite_qtyonhand) * _p.cost), _postDate::DATE )
FROM invcnt, itemsite, costcat
END IF;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION postCountTagLocation(INTEGER, BOOLEAN) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION postCountTagLocation(pInvcntid INTEGER,
+ pThaw BOOLEAN) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pInvcntid ALIAS FOR $1;
- pThaw ALIAS FOR $2;
_avgCostingMethod TEXT;
_invhistid INTEGER;
_postDate TIMESTAMP;
_itemloc RECORD;
_cntslip RECORD;
_origLocQty NUMERIC;
- _netable BOOLEAN;
_lsid INTEGER;
BEGIN
itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
CASE WHEN (itemsite_costmethod = 'N') THEN 0
WHEN ( (itemsite_costmethod = 'A') AND
- ((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND
+ (itemsite_qtyonhand = 0.0) AND
(_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
WHEN ( (itemsite_costmethod = 'A') AND
(_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
RETURN -9;
END IF;
- SELECT COALESCE(SUM(itemloc_qty),0.0), location_netable INTO _origLocQty,_netable
- FROM itemloc,location
+ SELECT COALESCE(SUM(itemloc_qty),0.0) INTO _origLocQty
+ FROM itemloc
WHERE ((itemloc_itemsite_id=_p.itemsite_id)
- AND (location_id=itemloc_location_id)
- AND (itemloc_location_id=_p.invcnt_location_id))
- GROUP BY location_netable;
+ AND (itemloc_location_id=_p.invcnt_location_id));
IF (NOT FOUND) THEN
_origLocQty := 0.0;
- _netable := TRUE;
END IF;
SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
AND (invcnt_id=pInvcntid) );
-- Update the QOH
- IF (_netable) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
- itemsite_datelastcount=_postDate
- WHERE (itemsite_id=_p.itemsite_id);
- UPDATE itemsite
- SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
- WHERE (itemsite_id=_p.itemsite_id);
- ELSE
- UPDATE itemsite
- SET itemsite_nnqoh = itemsite_nnqoh + (_p.invcnt_qoh_after - _origLocQty),
- itemsite_datelastcount=_postDate
- WHERE (itemsite_id=_p.itemsite_id);
- UPDATE itemsite
- SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
- WHERE (itemsite_id=_p.itemsite_id);
- END IF;
+ UPDATE itemsite
+ SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
+ itemsite_datelastcount=_postDate
+ WHERE (itemsite_id=_p.itemsite_id);
+ UPDATE itemsite
+ SET itemsite_value = itemsite_qtyonhand * _p.cost
+ WHERE (itemsite_id=_p.itemsite_id);
-- Post the detail, if any
IF (_hasDetail) THEN
END IF;
-- Distribute to G/L
- PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
+ PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
+ ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
FROM invcnt, itemsite, costcat
RETURN 0;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
(itemsite_qtyonhand + (_sense * pQty)),
itemsite_costmethod, itemsite_value,
-- sanity check to ensure that value = 0 when qtyonhand = 0
- CASE WHEN ((itemsite_qtyonhand + (_sense * pQty)) + itemsite_nnqoh) = 0.0 THEN 0.0
+ CASE WHEN ((itemsite_qtyonhand + (_sense * pQty))) = 0.0 THEN 0.0
ELSE itemsite_value + (_r.cost * _sense * pQty)
END,
pOrderType, pOrderNumber, pDocNumber, pComments,
--- /dev/null
+
+CREATE OR REPLACE FUNCTION qtyAtLocation(pItemsiteid INTEGER,
+ pLocationid INTEGER) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC := 0.0;
+
+BEGIN
+ SELECT CASE WHEN (pLocationid IS NULL) THEN itemsite_qtyonhand
+ ELSE COALESCE(SUM(itemloc_qty), 0.0)
+ END INTO _qty
+ FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
+ WHERE ((itemsite_id=pItemsiteid)
+ AND (itemloc_location_id=pLocationid))
+ GROUP BY itemsite_qtyonhand;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION qtyAvailable(INTEGER, INTEGER) RETURNS NUMERIC AS '
+
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteId INTEGER) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ _qty := qtyAvailable(pItemsiteId, TRUE);
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteId INTEGER,
+ pUsable BOOLEAN) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ IF (pUsable) THEN
+ -- Summarize itemloc qty for this itemsite/usable locations
+ -- or use itemsite_qtyonhand for regular/non-lot
+ SELECT COALESCE(SUM(itemloc_qty), itemsite_qtyonhand) INTO _qty
+ FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
+ LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemsite_id=pItemsiteId)
+ AND ((location_id IS NULL) OR (COALESCE(location_usable, true)))
+ GROUP BY itemsite_qtyonhand;
+ ELSE
+ -- Summarize itemloc qty for this itemsite/non-usable locations
+ SELECT COALESCE(SUM(itemloc_qty), 0.0) INTO _qty
+ FROM itemloc JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemloc_itemsite_id=pItemsiteId)
+ AND (NOT COALESCE(location_usable, true));
+ END IF;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
+ pLookAheadDays INTEGER) RETURNS NUMERIC STABLE AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pLookAheadDays ALIAS FOR $2;
BEGIN
- RETURN ( ( SELECT itemsite_qtyonhand
+ RETURN ( ( SELECT qtyAvailable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, pLookAheadDays)) -
(SELECT qtyAllocated(pitemsiteid, pLookAheadDays)) );
END;
-' LANGUAGE 'plpgsql' STABLE;
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION qtyAvailable(INTEGER, DATE) RETURNS NUMERIC AS '
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
+ pDate DATE) RETURNS NUMERIC STABLE AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pDate ALIAS FOR $2;
BEGIN
- RETURN ( ( SELECT itemsite_qtyonhand
+ RETURN ( ( SELECT qtyAvailable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, (pDate - CURRENT_DATE))) -
(SELECT qtyAllocated(pItemsiteid, (pDate - CURRENT_DATE))) );
END;
-' LANGUAGE 'plpgsql' STABLE;
+$$ LANGUAGE plpgsql;
--- /dev/null
+
+CREATE OR REPLACE FUNCTION qtyNetable(pItemsiteId INTEGER) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ _qty := qtyNetable(pItemsiteId, TRUE);
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyNetable(pItemsiteId INTEGER,
+ pNetable BOOLEAN) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ IF (pNetable) THEN
+ -- Summarize itemloc qty for this itemsite/netable locations
+ -- or use itemsite_qtyonhand for regular/non-lot
+ SELECT COALESCE(SUM(itemloc_qty), itemsite_qtyonhand) INTO _qty
+ FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
+ LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemsite_id=pItemsiteId)
+ AND ((location_id IS NULL) OR (COALESCE(location_netable, true)))
+ GROUP BY itemsite_qtyonhand;
+ ELSE
+ -- Summarize itemloc qty for this itemsite/non-netable locations
+ SELECT COALESCE(SUM(itemloc_qty), 0.0) INTO _qty
+ FROM itemloc JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemloc_itemsite_id=pItemsiteId)
+ AND (NOT COALESCE(location_netable, true));
+ END IF;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
BEGIN
RETURN relocateInventory($1, $2, $3, $4, $5, CURRENT_TIMESTAMP);
END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION relocateInventory(INTEGER, INTEGER, INTEGER, NUMERIC, TEXT, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION relocateInventory(pSourceItemlocid INTEGER,
+ pTargetLocationid INTEGER,
+ pItemsiteid INTEGER,
+ pQty NUMERIC,
+ pComments TEXT,
+ pGLDistTS TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pSourceItemlocid ALIAS FOR $1;
- pTargetLocationid ALIAS FOR $2;
- pItemsiteid ALIAS FOR $3;
- pQty ALIAS FOR $4;
- pComments ALIAS FOR $5;
- _GlDistTS TIMESTAMP WITH TIME ZONE := $6;
+ _GlDistTS TIMESTAMP WITH TIME ZONE;
_targetItemlocid INTEGER;
_invhistid INTEGER;
_p RECORD;
BEGIN
- IF ((_GlDistTS IS NULL) OR (CAST(_GlDistTS AS date)=CURRENT_DATE)) THEN
+ IF ((pGlDistTS IS NULL) OR (CAST(pGlDistTS AS date)=CURRENT_DATE)) THEN
_GlDistTS := CURRENT_TIMESTAMP;
+ ELSE
+ _GLDistTS := pGLDistTS;
END IF;
-- Make sure the passed itemsite points to a real item
itemloc_itemsite_id AS itemsiteid,
itemloc_expiration,
itemloc_warrpurc,
- itemloc_qty,
- sourceloc.location_netable AS sourcenet,
- targetloc.location_netable AS targetnet INTO _p
- FROM itemloc, location AS sourceloc, location AS targetloc
- WHERE ( (itemloc_location_id=sourceloc.location_id)
- AND (targetloc.location_id=pTargetLocationid)
- AND (itemloc_id=pSourceItemlocid) );
+ itemloc_qty INTO _p
+ FROM itemloc
+ WHERE (itemloc_id=pSourceItemlocid);
-- Check to make sure the qty being transfered exists
IF (_p.itemloc_qty < pQty) THEN
SET invhist_hasdetail=TRUE
WHERE (invhist_id=_invhistid);
--- Post in incomming or outgoing NN transaction if required
- IF (_p.sourcenet <> _p.targetnet) THEN
- IF (_p.targetnet) THEN
- _qty = (pQty * -1);
- ELSE
- _qty = pQty;
- END IF;
-
- INSERT INTO invhist
- ( invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments, invhist_transdate,
- invhist_invuom, invhist_unitcost, invhist_costmethod,
- invhist_value_before, invhist_value_after, invhist_series)
- SELECT itemsite_id,
- 'NN', (_qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _qty),
- '', '', _GlDistTS,
- uom_name,
- CASE WHEN (itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(item_id)
- END, itemsite_costmethod,
- itemsite_value, itemsite_value, _itemlocSeries
- FROM item, itemsite, uom
- WHERE ( (itemsite_item_id=item_id)
- ANd (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_p.itemsiteid) );
-
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _qty),
- itemsite_nnqoh = (itemsite_nnqoh + _qty)
- WHERE (itemsite_id=_p.itemsiteid);
- END IF;
-
-- Check to see if there is anything left at the target Itemloc and delete if not
-- Could be zero if relocate increased a negative quantity to zero
DELETE FROM itemloc
RETURN _invhistid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id)
))) - coitem_qtyreserved) * coitem_qty_invuomratio
- ) <= itemsite_qtyonhand)
+ ) <= qtyAvailable(itemsite_id))
AND
(((COALESCE(pqty, roundQty(item_fractional,
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id) - coitem_qtyreserved
) * coitem_qty_invuomratio
)
- ) <= itemsite_qtyonhand)
+ ) <= qtyAvailable(itemsite_id))
INTO _isqtyavail
FROM coitem, itemsite, item
WHERE ((coitem_itemsite_id=itemsite_id)
qtyAtShipping(pordertype, toitem_id)
)
)
- ) <= itemsite_qtyonhand) INTO _isqtyavail
+ ) <= qtyAvailable(itemsite_id)) INTO _isqtyavail
FROM toitem, tohead, itemsite, item
WHERE ((toitem_tohead_id=tohead_id)
AND (tohead_src_warehous_id=itemsite_warehous_id)
-CREATE OR REPLACE FUNCTION thawItemSite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION thawItemSite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_qoh NUMERIC := 0;
- _netable_qoh NUMERIC := 0;
- _nonnetable_qoh NUMERIC := 0;
_value NUMERIC := 0;
_itemlocid INTEGER;
_itemloc RECORD;
-- Cache the initial qty of the itemloc specified by the
-- itemsite/location/lot/serial
- SELECT itemloc_id, itemloc_qty, COALESCE(location_netable, TRUE) AS location_netable
+ SELECT itemloc_id, itemloc_qty
INTO _itemloc
- FROM itemloc LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
+ FROM itemloc
WHERE ( (itemloc_itemsite_id=pItemsiteid)
AND (itemloc_location_id=_coarse.invdetail_location_id)
AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_coarse.invdetail_ls_id,-1))
0, endOfTime() );
_qoh := 0.0;
- _netable_qoh := 0.0;
- _nonnetable_qoh := 0.0;
ELSE
_itemlocid := _itemloc.itemloc_id;
_qoh := _itemloc.itemloc_qty;
- IF (_itemloc.location_netable) THEN
- _netable_qoh := _itemloc.itemloc_qty;
- ELSE
- _nonnetable_qoh := _itemloc.itemloc_qty;
- END IF;
END IF;
-- Now step through each unposted invdetail record for a given
-- Update the running qoh
_qoh = (_qoh + _fine.invdetail_qty);
- IF (_itemloc.location_netable) THEN
- _netable_qoh := (_netable_qoh + _fine.invdetail_qty);
- ELSE
- _nonnetable_qoh := (_nonnetable_qoh + _fine.invdetail_qty);
- END IF;
END LOOP;
END LOOP;
--- _qoh can be used for the netable qoh because of the negative NN transactions
--- change to update qtyonhand with _netable_qoh
UPDATE itemsite
- SET itemsite_qtyonhand = _netable_qoh,
- itemsite_nnqoh = _nonnetable_qoh,
+ SET itemsite_qtyonhand = _qoh,
itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_value < 0.0)) THEN 0.0
ELSE _value END
WHERE(itemsite_id=pItemsiteid);
RETURN pItemsiteid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION updateStdCost(INTEGER, NUMERIC, NUMERIC, TEXT, TEXT) RETURNS BOOLEAN AS $$
+CREATE OR REPLACE FUNCTION updateStdCost(pItemcostid INTEGER,
+ pNewcost NUMERIC,
+ pOldcost NUMERIC,
+ pDocNumber TEXT,
+ pNotes TEXT) RETURNS BOOLEAN AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemcostid ALIAS FOR $1;
- pNewcost ALIAS FOR $2;
- pOldcost ALIAS FOR $3;
- pDocNumber ALIAS FOR $4;
- pNotes ALIAS FOR $5;
_itemcostid INTEGER;
_r RECORD;
_newcost NUMERIC;
END IF;
-- Distribute to G/L, debit Inventory Asset, credit Inventory Cost Variance
- FOR _r IN SELECT itemsite_id, (itemsite_qtyonhand + itemsite_nnqoh) AS totalQty,
+ FOR _r IN SELECT itemsite_id, itemsite_qtyonhand AS totalQty,
costcat_invcost_accnt_id, costcat_asset_accnt_id,
itemsite_costmethod
FROM itemcost, itemsite, costcat
WHERE ( (itemsite_item_id=itemcost_item_id)
AND (itemsite_costcat_id=costcat_id)
AND (itemsite_costmethod != 'A')
- AND ((itemsite_qtyonhand + itemsite_nnqoh) <> 0)
+ AND (itemsite_qtyonhand <> 0.0)
AND (itemcost_id=pItemcostid) ) LOOP
-- IF (_newcost <> _oldcost) THEN
-- RAISE NOTICE 'itemcost_id = %, Qty = %, Old Cost = %, New Cost = %', pItemcostid, _r.totalQty, _oldcost, _newcost;
RETURN -1;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
qtyAllocated(itemsite_id, wo_duedate) AS allocated,
qtyOrdered(itemsite_id, wo_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
qtyAllocated(itemsite_id, wo_duedate) AS allocated,
qtyOrdered(itemsite_id, wo_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
select xt.create_table('bankrecimport', 'public');
select xt.add_column('bankrecimport','bankrecimport_id', 'SERIAL', 'PRIMARY KEY', 'public');
-select xt.add_column('bankrecimport','bankrecimport_reference', 'TEXT', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_descrip', 'TEXT', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_comment', 'TEXT', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_debit_amount', 'NUMERIC', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_credit_amount', 'NUMERIC', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_effdate', 'DATE', 'NOT NULL', 'public');
-select xt.add_column('bankrecimport','bankrecimport_curr_rate', 'NUMERIC', 'NOT NULL', 'public');
+select xt.add_column('bankrecimport','bankrecimport_reference', 'TEXT', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_descrip', 'TEXT', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_comment', 'TEXT', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_debit_amount', 'NUMERIC', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_credit_amount', 'NUMERIC', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_effdate', 'DATE', NULL, 'public');
+select xt.add_column('bankrecimport','bankrecimport_curr_rate', 'NUMERIC', NULL, 'public');
--- /dev/null
+-- incident 23507:change how qoh, qoh available, and qoh netable are determined
+do $$
+begin
+if fetchMetricText('ServerVersion') < '4.7.0' then
+ update itemsite set itemsite_qtyonhand=(itemsite_qtyonhand + itemsite_nnqoh);
+ alter table itemsite drop column itemsite_nnqoh cascade;
+end if;
+end$$;
\ No newline at end of file
select xt.add_index('location', 'location_formatname','location_location_formatname_idx', 'btree', 'public');
UPDATE location SET location_formatname=formatLocationName(location_id) WHERE location_formatname IS NULL;
+-- incident 23507:change how qoh, qoh available, and qoh netable are determined
+select xt.add_column('location','location_usable', 'BOOLEAN', NULL, 'public');
+do $$
+begin
+if fetchMetricText('ServerVersion') < '4.7.0' then
+ update location set location_usable=true;
+end if;
+end$$;
(currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextpricebalance,
round((coitem_qtyord * coitem_qty_invuomratio) *
(coitem_unitcost / coitem_price_invuomratio), 2) AS extcost,
- (round((coitem_qtyord * coitem_qty_invuomratio) *
- (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
- round((coitem_qtyord * coitem_qty_invuomratio) *
- (coitem_unitcost / coitem_price_invuomratio), 2)) AS margin,
- CASE WHEN (coitem_price > 0.0) THEN
- (round((coitem_qtyord * coitem_qty_invuomratio) *
- (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
- round((coitem_qtyord * coitem_qty_invuomratio) *
- (coitem_unitcost / coitem_price_invuomratio), 2)) /
- round((coitem_qtyord * coitem_qty_invuomratio) *
- (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2)
- ELSE 0.0
+ CASE WHEN (coitem_price = 0.0) THEN 0.0
+ ELSE ROUND(coitem_qtyord * coitem_qty_invuomratio * (coitem_price - coitem_unitcost) / coitem_price_invuomratio,2)
+ END AS margin,
+ CASE WHEN (coitem_price = 0.0) THEN 0.0
+ ELSE ((coitem_price - coitem_unitcost) / coitem_price)
END AS marginpercent,
curr_abbr AS currAbbr,
-- TODO - not needed, remove? (very slow)
invcnt_tagdate AS tagdate,
item_number, (item_descrip1 || item_descrip2) AS item_descrip, warehous_code,
CASE WHEN (location_id IS NOT NULL) THEN
- location_name
+ formatLocationName(location_id)
ELSE <? value("all") ?> END AS loc_specific,
- CASE WHEN (invcnt_location_id IS NOT NULL)
- THEN (SELECT SUM(itemloc_qty)
- FROM itemloc JOIN location ON (location_id=itemloc_location_id)
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_location_id=invcnt_location_id)
- AND (location_netable)))
- ELSE itemsite_qtyonhand
- END AS qoh,
- CASE WHEN (invcnt_location_id IS NOT NULL)
- THEN (SELECT SUM(itemloc_qty)
- FROM itemloc JOIN location ON (location_id=itemloc_location_id)
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_location_id=invcnt_location_id)
- AND (NOT location_netable)))
- ELSE itemsite_nnqoh
- END AS nnqoh,
+ qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) AS qoh,
<? if exists("showSlips") ?>
calcTotalSlipQty(invcnt_id) AS qohafter,
- (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
+ (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
CASE WHEN (calcTotalSlipQty(invcnt_id) IS NULL) THEN NULL
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
- ELSE ((1 - (calcTotalSlipQty(invcnt_id) / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
+ ELSE ((1 - (calcTotalSlipQty(invcnt_id) / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
END AS varianceprcnt,
- (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
+ (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
<? else ?>
COALESCE(invcnt_qoh_after, 0.0) AS qohafter,
- (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
+ (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
CASE WHEN (invcnt_qoh_after IS NULL) THEN NULL
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after > 0)) THEN 1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after < 0)) THEN -1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after = 0)) THEN 0
- ELSE ((1 - (invcnt_qoh_after / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after > 0)) THEN 1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after < 0)) THEN -1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after = 0)) THEN 0
+ ELSE ((1 - (invcnt_qoh_after / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
END AS varianceprcnt,
- (itemCost(itemsite_id) * (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
+ (itemCost(itemsite_id) * (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
<? endif ?>
item_number AS orderby,
0 AS xtindentrole
'' AS warehous_code,
'' AS loc_specific,
NULL AS qoh,
- NULL AS nnqoh,
cntslip_qty AS qohafter,
NULL AS variance, NULL AS varianceprcnt, 0 AS variancecost,
item_number AS orderby,
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT id, type, locationname, defaultlocation,
- location_netable, lotserial, f_expiration, expired,
- qty, qtytagged, (qty + qtytagged) AS balance,
- 'qty' AS qty_xtnumericrole,
- 'qty' AS qtytagged_xtnumericrole,
- 'qty' AS balance_xtnumericrole,
- CASE WHEN expired THEN 'error' END AS qtforegroundrole,
- CASE WHEN expired THEN 'error'
- WHEN defaultlocation AND expired = false THEN 'altemphasis'
- ELSE null END AS defaultlocation_qtforegroundrole,
- CASE WHEN expired THEN 'error'
- WHEN qty > 0 AND expired = false THEN 'altemphasis'
- ELSE null END AS qty_qtforegroundrole
- FROM (
- <? if exists("cNoIncludeLotSerial") ?>
- SELECT location_id AS id, <? value("locationType") ?> AS type,
- formatLocationName(location_id) AS locationname,
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END AS defaultlocation,
- location_netable,
- TEXT('') AS lotserial,
- TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
- qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
- FROM itemlocdist, location, itemsite
- WHERE ( (itemlocdist_itemsite_id=itemsite_id)
- AND (itemsite_loccntrl)
- AND (itemsite_warehous_id=location_warehous_id)
- AND (validLocation(location_id, itemsite_id))
- AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
- <? elseif exists("cIncludeLotSerial") ?>
- SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
- COALESCE(formatLocationName(location_id),
- <? value("undefined") ?>) AS locationname,
- (location_id IS NOT NULL AND
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END) AS defaultlocation,
- COALESCE(location_netable, false) AS location_netable,
- ls_number AS lotserial,
- CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
- ELSE <? value("na") ?>
- END AS f_expiration,
- CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
- ELSE FALSE
- END AS expired,
- qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration, itemloc_warrpurc, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
- FROM itemlocdist AS target
- WHERE ( (target.itemlocdist_source_type='I')
- AND (target.itemlocdist_source_id=itemloc_id)
- AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
- FROM itemlocdist AS source, itemsite, itemloc
- LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
- WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
- AND (itemloc_itemsite_id=itemsite_id)
- AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
- UNION
- SELECT location_id AS id, <? value("locationType") ?> AS type,
- formatLocationName(location_id) AS locationname,
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END AS defaultlocation,
- location_netable,
- TEXT('') AS lotserial,
- TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
- qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
- FROM itemlocdist, location, itemsite
- WHERE ( (itemlocdist_itemsite_id=itemsite_id)
- AND (itemsite_loccntrl)
- AND (itemsite_warehous_id=location_warehous_id)
- AND (validLocation(location_id, itemsite_id))
- AND (itemsite_id=<? value("itemsite_id") ?> )
- AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id)))
- AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
- <? endif ?>
- ) AS data
- WHERE ((TRUE)
- <? if exists("showOnlyTagged") ?>
- AND (qtytagged != 0)
- <? endif ?>
- <? if exists("showQtyOnly") ?>
- AND (qty > 0)
- <? endif ?>
- ) ORDER BY locationname;
+ location_netable, location_usable, lotserial, f_expiration, expired,
+ qty, qtytagged, (qty + qtytagged) AS balance,
+ 'qty' AS qty_xtnumericrole,
+ 'qty' AS qtytagged_xtnumericrole,
+ 'qty' AS balance_xtnumericrole,
+ CASE WHEN expired THEN 'error' END AS qtforegroundrole,
+ CASE WHEN defaultlocation AND expired = false THEN 'altemphasis'
+ WHEN expired THEN 'error'
+ ELSE null END AS defaultlocation_qtforegroundrole,
+ CASE WHEN expired THEN 'error'
+ WHEN qty > 0 AND expired = false THEN 'altemphasis'
+ ELSE null END AS qty_qtforegroundrole
+FROM (
+ <? if exists("cNoIncludeLotSerial") ?>
+ SELECT location_id AS id, <? value("locationType") ?> AS type,
+ formatLocationName(location_id) AS locationname,
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ TEXT('') AS lotserial,
+ TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
+ qtyLocation(location_id, NULL, NULL, NULL,
+ itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
+ itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
+ FROM itemlocdist, location, itemsite
+ WHERE ( (itemlocdist_itemsite_id=itemsite_id)
+ AND (itemsite_loccntrl)
+ AND (itemsite_warehous_id=location_warehous_id)
+ AND (validLocation(location_id, itemsite_id))
+ AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ <? elseif exists("cIncludeLotSerial") ?>
+ SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
+ COALESCE(formatLocationName(location_id),
+ <? value("undefined") ?>) AS locationname,
+ (location_id IS NOT NULL AND
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END) AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ ls_number AS lotserial,
+ CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
+ ELSE <? value("na") ?>
+ END AS f_expiration,
+ CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
+ ELSE FALSE
+ END AS expired,
+ qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration,
+ itemloc_warrpurc, itemsite_id, itemlocdist_order_type,
+ itemlocdist_order_id, itemlocdist_id) AS qty,
+ ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
+ FROM itemlocdist AS target
+ WHERE ( (target.itemlocdist_source_type='I')
+ AND (target.itemlocdist_source_id=itemloc_id)
+ AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
+ FROM itemlocdist AS source, itemsite, itemloc
+ LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
+ LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
+ WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
+ AND (itemloc_itemsite_id=itemsite_id)
+ AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ UNION
+ SELECT location_id AS id, <? value("locationType") ?> AS type,
+ formatLocationName(location_id) AS locationname,
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ TEXT('') AS lotserial,
+ TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
+ qtyLocation(location_id, NULL, NULL, NULL,
+ itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
+ itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
+ FROM itemlocdist, location, itemsite
+ WHERE ( (itemlocdist_itemsite_id=itemsite_id)
+ AND (itemsite_loccntrl)
+ AND (itemsite_warehous_id=location_warehous_id)
+ AND (validLocation(location_id, itemsite_id))
+ AND (itemsite_id=<? value("itemsite_id") ?> )
+ AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=itemsite_id)))
+ AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ <? endif ?>
+ ) AS data
+WHERE ((TRUE)
+ AND ( (<? value("transtype") ?> != 'I') OR (<? value("transtype") ?> = 'I' AND location_usable) )
+<? if exists("showOnlyTagged") ?>
+ AND (qtytagged != 0)
+<? endif ?>
+<? if exists("showQtyOnly") ?>
+ AND (qty > 0)
+<? endif ?>
+) ORDER BY locationname;
GROUP BY cohead_id, item_number, cust_number,
cust_name, cohead_orderdate, pack_id, coitem_scheddate
<? if exists("onlyShowShortages") ?>
- HAVING (MIN(noNeg(itemsite_qtyonhand) +
+ HAVING (MIN(noNeg(qtyAvailable(itemsite_id)) +
qtyOrdered(itemsite_id, coitem_scheddate) -
qtyAllocated(itemsite_id, coitem_scheddate)) < 0
- OR MIN(noNeg(itemsite_qtyonhand) +
+ OR MIN(noNeg(qtyAvailable(itemsite_id)) +
qtyOrdered(itemsite_id, coitem_scheddate) -
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) < 0
)
item_number,
item_number AS trueitem_number,
(item_descrip1 || ' ' || item_descrip2) AS descrip,
- uom_name, noNeg(itemsite_qtyonhand) AS qoh,
+ uom_name, noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("onlyShowShortages") ?>
- AND ((noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
- OR (noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
+ AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
<? endif ?>
)
<? if exists("showWoSupply") ?>
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(itemsite_id) FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
WHERE ((coitem_cohead_id=cohead_id)
AND (coitem_itemsite_id=itemsite_id)
AND (itemsite_warehous_id=warehous_id)
WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("onlyShowShortages") ?>
- AND ((noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
- OR (noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
+ AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
<? endif ?>
)
<? endif ?>
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qoh,
+ qtyAvailable(itemsite_id) AS qoh,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
SELECT *,
<? value("na") ?> AS locationname_xtnullrole,
<? value("na") ?> AS netable_xtnullrole,
+ <? value("na") ?> AS usable_xtnullrole,
<? value("na") ?> AS lotserial_xtnullrole,
<? value("na") ?> AS expiration_xtnullrole,
<? value("na") ?> AS warranty_xtnullrole,
END AS locationname,
CASE WHEN (location_id IS NOT NULL) THEN location_netable
END AS netable,
+ CASE WHEN (location_id IS NOT NULL) THEN location_usable
+ END AS usable,
CASE WHEN (itemsite_controlmethod IN ('L', 'S')) THEN
formatlotserialnumber(itemloc_ls_id)
END AS lotserial,
itemsite_warrpurc, NULL AS itemloc_warrpurc,
NULL AS locationname,
NULL AS netable,
+ NULL AS usable,
NULL AS lotserial,
NULL AS expiration,
NULL AS warranty,
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
-SELECT formatQty(itemsite_qtyonhand) AS f_qoh,itemsite_qtyonhand
- , qtyAllocated(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
+SELECT qtyNetable(itemsite_id) AS qoh, formatQty(qtyNetable(itemsite_id)) AS f_qoh,
+ qtyAllocated(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
findPeriodEnd(<? value("cursorId") ?>)) AS allocations<? value("counter") ?>,
qtyOrdered(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
findPeriodEnd(<? value("cursorId") ?>)) AS orders<? value("counter") ?>,
ib.*,
((bomdata_qtyreq::NUMERIC * <? value("buildQty") ?>) * (1 + bomdata_scrap::NUMERIC)) AS pendalloc,
qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered
FROM indentedBOM(<? value("item_id") ?>,
getActiveRevId('BOM', <? value("item_id") ?>),
((itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL,
(bomitem_qtyfxd + bomitem_qtyper * <? value("buildQty") ?>) * (1 + bomitem_scrap)))) AS pendalloc,
qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
FROM itemsite, item, bomitem(<? value("item_id") ?>), uom
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT pr_id, itemsite_id, itemsite_qtyonhand, itemsite_reorderlevel,
+ qtyNetable(itemsite_id) AS netableqoh, qtyNetable(itemsite_id, FALSE) AS nonnetableqoh,
item_number, (item_descrip1 || ' ' || item_descrip2) AS description,
pr.*,
CASE WHEN (pr_order_type='W') THEN ('W/O ' || ( SELECT formatWoNumber(womatl_wo_id)
defaultlocation,
reorderlevel, formatQty(reorderlevel) AS f_reorderlevel,
qoh, formatQty(qoh) AS f_qoh,
- nnqoh, formatQty(nnqoh) AS f_nnqoh,
- CASE WHEN (itemsite_loccntrl) THEN nnqoh END AS f_nnqoh,
+ availqoh, formatQty(availqoh) AS f_availqoh,
+ nonavailqoh, formatQty(nonavailqoh) AS f_nonavailqoh,
+ netqoh, formatQty(netqoh) AS f_netqoh,
+ nonnetqoh, formatQty(nonnetqoh) AS f_nonnetqoh,
cost, (cost * qoh) AS value,
- CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS nnvalue,
+ (cost * availqoh) AS availvalue,
+ (cost * nonavailqoh) AS nonavailvalue,
+ (cost * netqoh) AS netvalue,
+ (cost * nonnetqoh) AS nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
WHEN(itemsite_costmethod='S') THEN 'Standard'
WHEN(itemsite_costmethod='J') THEN 'Job'
END AS costmethod,
<? if exists("showValue") ?>
formatMoney(cost) AS f_cost, (cost * qoh) AS f_value,
- CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS f_nnvalue,
+ formatMoney(cost * availqoh) AS f_availvalue,
+ formatMoney(cost * nonavailqoh) AS f_nonavailvalue,
+ formatMoney(cost * netqoh) AS f_netvalue,
+ formatMoney(cost * nonnetqoh) AS f_nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
WHEN(itemsite_costmethod='S') THEN 'Standard'
WHEN(itemsite_costmethod='J') THEN 'Job'
<? endif ?>
'qty' AS reorderlevel_xtnumericrole,
'qty' AS qoh_xtnumericrole,
- 'qty' AS f_nnqoh_xtnumericrole,
+ 'qty' AS availqoh_xtnumericrole,
+ 'qty' AS nonavailqoh_xtnumericrole,
+ 'qty' AS netqoh_xtnumericrole,
+ 'qty' AS nonnetqoh_xtnumericrole,
0 AS qoh_xttotalrole,
- 0 AS nnqoh_xttotalrole,
+ 0 AS availqoh_xttotalrole,
+ 0 AS nonavailqoh_xttotalrole,
+ 0 AS netqoh_xttotalrole,
+ 0 AS nonnetqoh_xttotalrole,
'cost' AS cost_xtnumericrole,
'curr' AS value_xtnumericrole,
- 'curr' AS nnvalue_xtnumericrole,
+ 'curr' AS availvalue_xtnumericrole,
+ 'curr' AS nonavailvalue_xtnumericrole,
+ 'curr' AS netvalue_xtnumericrole,
+ 'curr' AS nonnetvalue_xtnumericrole,
0 AS value_xttotalrole,
- 0 AS nnvalue_xttotalrole,
- <? value("na") ?> AS nnqoh_xtnullrole,
- <? value("na") ?> AS nnvalue_xtnullrole,
+ 0 AS availvalue_xttotalrole,
+ 0 AS nonavailvalue_xttotalrole,
+ 0 AS netvalue_xttotalrole,
+ 0 AS nonnetvalue_xttotalrole,
+ <? value("na") ?> AS availqoh_xtnullrole,
+ <? value("na") ?> AS nonavailqoh_xtnullrole,
+ <? value("na") ?> AS availvalue_xtnullrole,
+ <? value("na") ?> AS nonavailvalue_xtnullrole,
+ <? value("na") ?> AS netqoh_xtnullrole,
+ <? value("na") ?> AS nonnetqoh_xtnullrole,
+ <? value("na") ?> AS netvalue_xtnullrole,
+ <? value("na") ?> AS nonnetvalue_xtnullrole,
CASE WHEN (qoh < 0) THEN 'error' END AS qoh_qtforegroundrole,
CASE WHEN (reorderlevel > qoh) THEN 'warning' END AS qoh_qtforegroundrole
FROM (
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,
<? if exists("asOf") ?>
COALESCE(invbal_qoh_ending,0) AS qoh,
- COALESCE(invbal_nn_ending,0) AS nnqoh,
+ COALESCE(invbal_qoh_ending,0) AS availqoh,
+ COALESCE(invbal_nn_ending,0) AS nonavailqoh,
+ COALESCE(invbal_qoh_ending,0) AS netqoh,
+ COALESCE(invbal_nn_ending,0) AS nonnetqoh,
<? else ?>
itemsite_qtyonhand AS qoh,
- itemsite_nnqoh AS nnqoh,
+ qtyAvailable(itemsite_id) AS availqoh,
+ qtyAvailable(itemsite_id, FALSE) AS nonavailqoh,
+ qtyNetable(itemsite_id) AS netqoh,
+ qtyNetable(itemsite_id, FALSE) AS nonnetqoh,
<? endif ?>
<? if exists("useStandardCosts") ?>
stdcost(item_id) AS cost
COALESCE((invbal_value_ending / CASE WHEN(invbal_qoh_ending=0) THEN 1
ELSE invbal_qoh_ending END),0) AS cost
<? else ?>
- (itemsite_value / CASE WHEN((itemsite_qtyonhand + itemsite_nnqoh)=0) THEN 1
- ELSE (itemsite_qtyonhand + itemsite_nnqoh) END) AS cost
+ (itemsite_value / CASE WHEN(itemsite_qtyonhand=0) THEN 1
+ ELSE itemsite_qtyonhand END) AS cost
<? endif ?>
<? endif ?>
FROM item, whsinfo, classcode, uom, costcat, itemsite
SELECT itemsite_id, itemtype, warehous_code, item_number,
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
reorderdate, reorderlevel,
- (itemsite_qtyonhand - qtyAllocated(itemsite_id, reorderdate) +
+ (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, reorderdate) +
qtyOrdered(itemsite_id, reorderdate)) AS projavail,
'qty' AS reorderlevel_xtnumericrole,
'qty' AS projavail_xtnumericrole
<? value("lookAheadDays") ?>,
<? value("includePlannedOrders") ?>)
AS reorderdate,
- itemsite_qtyonhand, reorderlevel
+ reorderlevel
FROM (SELECT itemsite_id, itemsite_item_id,
- itemsite_warehous_id, itemsite_qtyonhand,
+ itemsite_warehous_id,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel
SELECT orderid, altorderid, ordertype, ordernumber, sequence, item_number,
duedate, amount, qtyordered, qtyreceived, balance,
- balance AS runningavail,
+ balance AS runningavail, balance AS runningnetable,
<? if exists("isReport") ?>
CASE WHEN duedate = startOfTime() THEN ''
ELSE formatDate(duedate) END AS f_duedate,
<? endif ?>
notes,
1 AS runningavail_xtrunningrole,
+ 1 AS runningnetable_xtrunningrole,
<? value("qoh") ?> AS runningavail_xtrunninginit,
+ <? value("netableqoh") ?> AS runningnetable_xtrunninginit,
CASE WHEN late THEN 'error' END AS duedate_qtforegroundrole,
CASE WHEN duedate = startOfTime() THEN '' END AS duedate_qtdisplayrole,
CASE WHEN ordertype ~ <? value("plannedPo") ?> OR
ELSE 1
END AS xtindentrole,
spplytype, ordrnumbr,
- itemsite_qtyonhand,
- 'qty' AS itemsite_qtyonhand_xtnumericrole
+ availableqoh,
+ 'qty' AS availableqoh_xtnumericrole
<? if exists("includeReservations") ?>
,
reserved,
WHEN coitem_order_type='R' THEN (pr_number || '-' || pr_subnumber)
ELSE TEXT (' ')
END AS ordrnumbr,
- itemsite_qtyonhand
+ qtyAvailable(itemsite_id) AS availableqoh
<? if exists("includeReservations") ?>
,
coitem_qtyreserved AS reserved,
- itemsite_qtyonhand - qtyreserved(itemsite_id) AS reservable
+ qtyAvailable(itemsite_id) - qtyReserved(itemsite_id) AS reservable
<? endif?>
FROM cohead
JOIN coitem ON (coitem_cohead_id=cohead_id)
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT s_itemsite_id, warehous_code, item_number, itemdescrip,
- qtyonhand, reorderlevel, leadtime, itemsub_rank,
+ availableqoh, reorderlevel, leadtime, itemsub_rank,
allocated, ordered, available,
- 'qty' AS qtyonhand_xtnumericrole,
+ 'qty' AS availableqoh_xtnumericrole,
'qty' AS allocated_xtnumericrole,
'qty' AS ordered_xtnumericrole,
'qty' AS reorderlevel_xtnumericrole,
warehous_code, item_number,
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
<? if exists("normalize") ?>
- (sub.itemsite_qtyonhand * itemsub_uomratio) AS qtyonhand,
+ (qtyAvailable(sub.itemsite_id) * itemsub_uomratio) AS availableqoh,
(CASE WHEN(sub.itemsite_useparams)
THEN sub.itemsite_reorderlevel
ELSE 0.0
END * itemsub_uomratio) AS reorderlevel,
sub.itemsite_leadtime AS leadtime, itemsub_rank,
<? else ?>
- (sub.itemsite_qtyonhand) AS qtyonhand,
+ (qtyAvailable(sub.itemsite_id)) AS availableqoh,
CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio)
+ ((qtyAvailable(sub.itemsite_id) * itemsub_uomratio)
+ (qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS allocated,
(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
+ (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
- qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS available
<? endif ?>
<? elseif exists("byDays") ?>
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
+ ((qtyAvailable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS allocated,
(qtyOrdered(sub.itemsite_id, <? value("days") ?>)) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
+ (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
- qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS available
<? endif ?>
<? elseif exists("byDate") ?>
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
+ ((qtyAvailable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS allocated,
(qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id,
+ (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id,
(<? value("date") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS available
<? endif ?>
<? endif ?>
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,
itemsite_qtyonhand,
detailedQOH(itemsite_id, FALSE) AS detailedqoh,
- itemsite_nnqoh,
- detailedNNQOH(itemsite_id, FALSE) AS detailednnqoh,
'qty' AS itemsite_qtyonhand_xtnumericrole,
- 'qty' AS detailedqoh_xtnumericrole,
- 'qty' AS itemsite_nnqoh_xtnumericrole,
- 'qty' AS detailednnqoh_xtnumericrole
+ 'qty' AS detailedqoh_xtnumericrole
FROM whsinfo, item, itemsite, uom
WHERE ( (itemsite_item_id=item_id)
AND (item_inv_uom_id=uom_id)
AND (itemsite_warehous_id=warehous_id)
AND ((itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')))
- AND ((itemsite_qtyonhand <> detailedQOH(itemsite_id, FALSE))
- OR (itemsite_nnqoh <> detailedNNQOH(itemsite_id, FALSE)))
+ AND (itemsite_qtyonhand <> detailedQOH(itemsite_id, FALSE))
<? if exists("classcode_id") ?>
AND (item_classcode_id=<? value("classcode_id") ?>)
<? elseif exists("classcode_pattern") ?>
-SELECT setMetric('ServerVersion', '4.7.0Beta');
+-- Proof of concept
+insert into metric (metric_name, metric_value)
+select 'UnifiedBuild', 'true'
+where not exists (select c.metric_id from metric c where c.metric_name = 'UnifiedBuild');
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qtyonhand,
+ qtyAvailable(itemsite_id) AS qtyonhand,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
cohead_id, cohead_number, cust_number, cust_name,
item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
uom_name, item_picklist,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(itemsite_id) AS availableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
<? endif ?>
WHERE((coitem_cohead_id=cohead_id)
AND (cohead_cust_id=cust_id)
FROM ( SELECT itemsite_id, coitem_id,
item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
uom_name, item_picklist,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyAvailable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(itemsite_id) AS availableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
<? endif ?>
WHERE ( (coitem_cohead_id=cohead_id)
AND (coitem_itemsite_id=itemsite_id)
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qtyonhand,
+ qtyAvailable(itemsite_id) AS qtyonhand,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
AND (invcitem_warehous_id=itemsite_warehous_id)
AND (invcitem_linenumber=coitem_linenumber)
AND (cohead_id=coitem_cohead_id))) > 0)) THEN 'P'
- WHEN (coitem_status='O' AND (itemsite_qtyonhand - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyAvailable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ qtyOrdered(itemsite_id, CURRENT_DATE))
>= (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) THEN 'R'
ELSE coitem_status
<sql>SELECT warehous_code, item_number, item_descrip1, item_descrip2,
formatDate(reorderdate) AS f_reorderdate,
formatQty(reorderlevel) AS f_reorderlevel,
- formatQty((itemsite_qtyonhand - qtyAllocated(itemsite_id, reorderdate) + qtyOrdered(itemsite_id, reorderdate))) AS f_projavail,
+ formatQty((qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, reorderdate) + qtyOrdered(itemsite_id, reorderdate))) AS f_projavail,
reorderdate
FROM ( SELECT itemsite_id,
CASE WHEN (item_type IN ('M', 'B', 'T')) THEN 1
ELSE 3
END AS itemtype,
warehous_code, item_number, item_descrip1, item_descrip2,
- reorderDate(itemsite_id, <? value("lookAheadDays") ?>, <? if exists("includePlannedOrder") ?>true<? else ?>false<? endif ?>) AS reorderdate, itemsite_qtyonhand,
+ reorderDate(itemsite_id, <? value("lookAheadDays") ?>, <? if exists("includePlannedOrder") ?>true<? else ?>false<? endif ?>) AS reorderdate,
reorderlevel
FROM ( SELECT itemsite_id, itemsite_item_id, itemsite_warehous_id,
- itemsite_qtyonhand,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
FROM itemsite
WHERE ((true)
<querysource>
<name>info</name>
<sql>SELECT
- formatQty(itemsite_qtyonhand) AS qoh,
+ formatQty(qtyAvailable(itemsite_id)) AS qoh,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END) AS reorderlevel,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_ordertoqty ELSE 0.0 END) AS ordertoqty,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_multordqty ELSE 0.0 END) AS multorderqty
item_number,
item_descrip1,
item_descrip2,
- formatQty(sub.itemsite_qtyonhand) AS f_qtyonhand,
+ formatQty(qtyAvailable(sub.itemsite_id)) AS f_qtyonhand,
formatQty(CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel ELSE 0.0 END) AS f_reorderlevel,
sub.itemsite_leadtime as leadtime,
<? if exists("byDays") ?>
formatQty(qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, <? value("byDays") ?>)) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
+ formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
<? elseif exists("byDate") ?>
formatQty(qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
+ formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
<? else ?>
formatQty(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
+ formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
<? endif ?>
FROM item, itemsite AS sub, itemsite AS root, whsinfo, itemsub
WHERE ((sub.itemsite_item_id=item_id)
--- /dev/null
+SELECT setMetric('ServerVersion', '4.7.0Beta');
BEGIN
-- Cache some information
- SELECT item_type INTO _r
+ -- Added item_number as part of feature request 21645
+ SELECT item_type, item_number INTO _r
FROM item
WHERE (item_id=NEW.itemsite_item_id);
END IF;
END IF;
+-- Added item_number to error messages displayed to fulfill Feature Request 21645
IF (NEW.itemsite_qtyonhand < 0 AND NEW.itemsite_costmethod = 'A') THEN
- RAISE EXCEPTION 'Itemsite (%) is set to use average costing and is not allowed to have a negative quantity on hand.', NEW.itemsite_id;
+ RAISE EXCEPTION 'Itemsite (%) is set to use average costing and is not allowed to have a negative quantity on hand.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number;
ELSIF (NEW.itemsite_value < 0 AND NEW.itemsite_costmethod = 'A') THEN
- RAISE EXCEPTION 'This transaction results in a negative itemsite value. Itemsite (%) is set to use average costing and is not allowed to have a negative value.', NEW.itemsite_id;
- END IF;
+ RAISE EXCEPTION 'This transaction results in a negative itemsite value. Itemsite (%) is set to use average costing and is not allowed to have a negative value.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number; END IF;
-- Handle the ChangeLog
IF ( SELECT (metric_value='t')
RAISE NOTICE 'Deleting item site detail records,';
- SELECT SUM(itemloc_qty) INTO _qty
- FROM itemloc, location
- WHERE ((itemloc_location_id=location_id)
- AND (NOT location_netable)
- AND (itemloc_itemsite_id=OLD.itemsite_id));
-
- IF (_qty != 0) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = itemsite_qtyonhand + _qty,
- itemsite_nnqoh = itemsite_nnqoh - _qty
- WHERE (itemsite_id=OLD.itemsite_id);
- END IF;
-
DELETE FROM itemloc
WHERE (itemloc_itemsite_id=OLD.itemsite_id);
END IF;
BEGIN
- -- Maintain itemsite_qtyonhand and itemsite_nnqoh when location_netable changes
- IF (TG_OP = 'UPDATE') THEN
- IF (OLD.location_netable <> NEW.location_netable) THEN
- FOR _itemloc IN SELECT * FROM itemloc WHERE (itemloc_location_id=NEW.location_id) LOOP
- IF (NEW.location_netable) THEN
- UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand + _itemloc.itemloc_qty,
- itemsite_nnqoh = itemsite_nnqoh - _itemloc.itemloc_qty
- WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
- ELSE
- UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand - _itemloc.itemloc_qty,
- itemsite_nnqoh = itemsite_nnqoh + _itemloc.itemloc_qty
- WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
- END IF;
- END LOOP;
- END IF;
- END IF;
-
RETURN NEW;
END;
@defaultPanelWidth: 320px;
@toolbarHeight: 55px;
@searchLength: 185px;
+// popups
+@maxMessageHeight: 500px;
+@maxMessageWidth: 500px;
// libs
@import "../../lib/font-awesome/less/font-awesome.less";
*/
.xv-popup {
background: @header-gray;
- margin: 0;
- max-height: 400px;
- width: 400px;
min-width: @defaultPanelWidth;
- padding: 7px;
+ padding: 10px;
text-align: center;
+ .message {
+ margin-bottom: 10px;
+ max-height: @maxMessageHeight;
+ max-width: @maxMessageWidth;
+ }
+
&.xv-groupbox-popup {
.xv-workspace-container > .xv-workspace > .xv-workspace-panel;
color: @black;
*/
.xv-popup {
background: #505050;
- margin: 0;
- max-height: 400px;
- width: 400px;
min-width: 320px;
- padding: 7px;
+ padding: 10px;
text-align: center;
}
+.xv-popup .message {
+ margin-bottom: 10px;
+ max-height: 500px;
+ max-width: 500px;
+}
.xv-popup.xv-groupbox-popup {
width: 320px;
margin: 0 4px 0 2px;
border: none;
}
/**
- Styles relating to Lists
-*/
-.xv-list-header {
- background-color: #d8d8d8;
- color: #fdfdfd;
- font-size: .6em;
- font-weight: bold;
- text-transform: uppercase;
- padding-top: 4px;
- padding-bottom: 4px;
- border-bottom: 1px solid #aaaaaa;
+ * Default ListItem styles when using a ModelDecorator.
+ */
+.xv-list .xv-model-decorator > .xv-list-item .xv-table {
+ display: table;
+ width: 100%;
+ table-layout: fixed;
}
-.xv-list-header .xv-list-column.last {
- border-right: none;
+.xv-list .xv-model-decorator > .xv-list-item .xv-table .xv-cell {
+ display: table-cell;
}
-.xv-list-header .xv-list-column.name-column,
-.xv-list-header .xv-list-column.first,
-.xv-list-header .xv-list-column.second,
-.xv-list-header .xv-list-column.third,
-.xv-list-header .xv-list-column.short,
-.xv-list-header .xv-list-column.small,
-.xv-list-header .xv-list-column.medium,
-.xv-list-header .xv-list-column.descr {
- padding-left: 7px;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr {
+ /**
+ * Default styling for a model's id (as designated by 'idAttribute')
+ */
}
-/* List */
-.xv-list-column.line-number {
- width: 30px;
- text-align: right;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attribute-id {
+ color: #357ec7;
+ font-weight: bold;
+ cursor: pointer;
}
-.xv-list-column.name-column {
- width: 200px;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attribute-name {
+ font-weight: bold;
}
-.xv-list-column.right-column {
- width: 100px;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-number {
text-align: right;
}
-.xv-list-column.short {
- width: 100px;
-}
-.xv-list-column.small {
- width: 125px;
-}
-.xv-list-column.medium {
- width: 150px;
-}
-.xv-list-column.first {
- width: 300px;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-money {
+ text-align: right;
}
-.xv-list-column.second {
- width: 200px;
+.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-date {
+ text-align: right;
}
-.xv-list-column.third {
- width: 100px;
+.xv-list .xv-model-decorator > .xv-list-item.item-selected .xv-list-attr {
+ color: white;
}
-.xv-list-column.money,
-.xv-list-column.quantity {
- width: 75px;
- text-align: right;
+.xv-list .xv-model-decorator > .xv-list-item.item-selected .xv-list-attr.xm-attribute-id {
+ color: #ff6529;
}
-.xv-list-column.descr {
- width: 200px;
+/**
+ Styles related to pickers, combo boxes, and relation widgets
+*/
+.onyx-picker-decorator .onyx-button {
+ padding: 12px 8px 12px 8px;
+ width: 150px;
}
-.xv-list-column.icon {
- width: 10px;
+.onyx-picker .onyx-menu-item {
+ text-align: left;
+ text-overflow: ellipsis;
}
-.xv-list {
- background: #f8f8f8;
+.picker-icon {
+ position: absolute;
+ right: 0;
+ margin: 0 10px 0 2px;
+ color: #070707;
}
-.xv-list .xv-list-item > * {
- display: inline-block;
- vertical-align: middle;
+.xv-picker-button {
+ text-align: left;
}
-.xv-list .xv-list-item {
- background-color: #fdfdfd;
- border-bottom: 1px solid #d7d7d7;
- min-height: 32px;
+.xv-picker-button .picker-content {
+ max-width: 100px;
+ overflow: hidden;
}
-.xv-list .xv-list-item.header {
- padding-top: 0;
+.xv-picker-button.disabled {
+ color: #777777;
}
-.xv-list .xv-list-item.inactive {
- background-color: #d8d8d8;
+.xv-picker-label {
color: #070707;
+ padding: 20px 8px 8px 8px;
+ text-align: right;
+ width: 130px;
}
-.xv-list .xv-list-item.inactive .xv-list-column .xv-list-attr {
- background: transparent;
-}
-.xv-list .xv-list-item.inactive .xv-list-column .xv-list-attr.placeholder {
- color: #d8d8d8;
+.xv-picker-label.disabled {
+ color: #777777;
}
-.xv-list .xv-list-item .xv-list-column .list-icon {
- padding: 2px;
- color: #666666;
- vertical-align: sub;
- border: 1px solid #efefef;
- -webkit-border-radius: 2px;
- -moz-border-radius: 2px;
- border-radius: 2px;
+.xv-combobox-note {
+ padding: 14px 3px 8px 3px;
+ text-align: left;
}
-.xv-list .xv-list-item.item-selected {
- background: #226b9a;
- background-color: #1f608c;
- background-image: -moz-linear-gradient(top, #226b9a, #1a4f77);
- background-image: -webkit-gradient(linear, 0 0, 0 100%, from(#226b9a), to(#1a4f77));
- background-image: -webkit-linear-gradient(top, #226b9a, #1a4f77);
- background-image: -o-linear-gradient(top, #226b9a, #1a4f77);
- background-image: linear-gradient(to bottom, #226b9a, #1a4f77);
- background-repeat: repeat-x;
- filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ff226b9a', endColorstr='#ff1a4f77', GradientType=0);
+/*
+ Styles relating to the grid box
+*/
+/* Entire box including the grid and the summary panel */
+.xv-grid-box {
+ /**
+ This is the most general grid row that
+ is not specific to read-only or selected
+ */
}
-.xv-list .xv-list-item.item-selected .xv-list-attr {
- color: #fdfdfd;
+.xv-grid-box.small-panel {
+ width: 600px;
}
-.xv-list .xv-list-item.item-selected .xv-list-attr.placeholder {
- font-style: italic;
- color: #99ccff;
+.xv-grid-box.medium-panel {
+ width: 700px;
}
-.xv-list .xv-list-item.item-selected .xv-list-attr.hyperlink {
- color: #ff6529;
+.xv-grid-box.large-panel {
+ width: 800px;
}
-.xv-list .xv-list-item.item-selected .xv-list-attr.header {
- background: #99ccff;
+.xv-grid-box .enyo-list-page > *:first-child .xv-grid-row {
+ border-top: 0;
}
-.xv-list .xv-list-item .xv-list-item-gear {
- position: absolute;
- right: 0px;
- z-index: 999;
+.xv-grid-box .xv-above-grid-list {
+ border: 0;
}
-.xv-list.xv-grid-list {
+.xv-grid-box .xv-scroller {
background: #f8f8f8;
}
-.xv-list.xv-grid-list .xv-list-item > * {
- vertical-align: top;
+.xv-limit-description .xv-grid-box .xv-grid-attr.bold {
+ font-weight: bold;
}
-.xv-list.xv-grid-list .xv-list-item {
- padding-top: 7px !important;
- padding-bottom: 9px !important;
- border-bottom: 1px solid #aaaaaa !important;
- background: #f8f8f8;
+.xv-grid-box .xv-grid-attr.error {
+ color: #ff0000;
}
-.xv-list.xv-grid-list .xv-list-item.item-selected {
- background: #226b9a;
- background-color: #1f608c;
- background-image: -moz-linear-gradient(top, #226b9a, #1a4f77);
- background-image: -webkit-gradient(linear, 0 0, 0 100%, from(#226b9a), to(#1a4f77));
- background-image: -webkit-linear-gradient(top, #226b9a, #1a4f77);
- background-image: -o-linear-gradient(top, #226b9a, #1a4f77);
- background-image: linear-gradient(to bottom, #226b9a, #1a4f77);
- background-repeat: repeat-x;
- filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ff226b9a', endColorstr='#ff1a4f77', GradientType=0);
+.xv-grid-box .xv-grid-attr.emphasis {
+ color: #009000;
}
-.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr {
- color: #fdfdfd;
+.xv-grid-box .xv-grid-attr.warn {
+ color: #ff9c00;
}
-.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.placeholder {
+.xv-grid-box .xv-grid-attr.italic {
font-style: italic;
- color: #99ccff;
}
-.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.hyperlink {
- color: #ff6529;
+.xv-grid-box .xv-grid-attr.placeholder {
+ font-style: italic;
+ color: #93a1a1;
}
-.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.header {
- background: #99ccff;
+.xv-grid-box .xv-grid-attr.hyperlink {
+ color: blue;
}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column {
- vertical-align: top;
+.xv-grid-box .xv-gridbox-button {
+ color: #357ec7;
+ font-size: 18px;
+ border: none;
+ background: transparent;
}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column .xv-list-attr {
+.xv-grid-box .xv-grid-row {
font-size: 12px;
+ background-color: #d8d8d8;
+ border-bottom: 1px solid #aaaaaa;
+ vertical-align: top;
+ /**
+ This is the grid header row
+ */
}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.last {
- border-right: none;
+.xv-grid-box .xv-grid-row > * {
+ display: inline-block;
}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.name-column {
- padding-left: 7px;
+.xv-grid-box .xv-grid-row .xv-grid-header {
+ background-color: #d7d7d7;
+ color: #0e0e0e;
+ font-size: .8em;
+ font-weight: bold;
+ text-transform: uppercase;
+ padding-top: 4px;
}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.first {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.second {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.third {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.short {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.small {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.medium {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column.descr {
- padding-left: 7px;
-}
-.xv-list.xv-grid-list .xv-list-item .xv-list-column .xv-list-attr {
- padding: 0px;
-}
-.xv-list-attr {
- padding: 5px;
- font-size: .8em;
- white-space: nowrap;
- overflow: hidden;
- text-overflow: ellipsis;
- color: #070707;
-}
-.xv-list-attr.header {
- padding: 4px;
- background: #d8d8d8;
- font-size: .7em;
- font-weight: bold;
- text-transform: uppercase;
- color: #fdfdfd;
-}
-.xv-list-attr.footer {
- padding: 4px;
- background: #d8d8d8;
- font-size: .7em;
- font-weight: bold;
- text-transform: uppercase;
- color: #070707;
-}
-.xv-list-attr.right {
- position: absolute;
- right: 10px;
-}
-.xv-list-attr.text-align-right {
- text-align: right;
-}
-.xv-list-attr.bold {
- font-weight: bold;
-}
-.xv-list-attr.error {
- color: #ff0000;
-}
-.xv-list-attr.emphasis {
- color: #009000;
-}
-.xv-list-attr.warn {
- color: #ff9c00;
-}
-.xv-list-attr.italic {
- font-style: italic;
-}
-.xv-list-attr.placeholder {
- font-style: italic;
- color: #777777;
-}
-.xv-list-attr.hyperlink {
- color: #357ec7;
- cursor: pointer;
-}
-.xv-list-attr.disabled {
- color: #777777;
-}
-/* Navigator */
-.xv-navigator-header {
- font-size: small;
- font-weight: bold;
- text-transform: uppercase;
- color: #ff6600;
- padding-left: 20px;
- border-bottom: 1px solid #0e0e0e;
-}
-.xv-workspace-header {
- color: #fdfdfd;
- white-space: nowrap;
- overflow: hidden;
- text-overflow: ellipsis;
- padding: 8px 0 0 8px;
-}
-/**
- * Default ListItem styles when using a ModelDecorator.
- */
-.xv-list .xv-model-decorator > .xv-list-item .xv-table {
- display: table;
- width: 100%;
- table-layout: fixed;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-table .xv-cell {
- display: table-cell;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr {
- /**
- * Default styling for a model's id (as designated by 'idAttribute')
- */
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attribute-id {
- color: #357ec7;
- font-weight: bold;
- cursor: pointer;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attribute-name {
- font-weight: bold;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-number {
- text-align: right;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-money {
- text-align: right;
-}
-.xv-list .xv-model-decorator > .xv-list-item .xv-list-column.xv-list-attr.xm-attributetype-date {
- text-align: right;
-}
-.xv-list .xv-model-decorator > .xv-list-item.item-selected .xv-list-attr {
- color: white;
-}
-.xv-list .xv-model-decorator > .xv-list-item.item-selected .xv-list-attr.xm-attribute-id {
- color: #ff6529;
-}
-/**
- Styles related to pickers, combo boxes, and relation widgets
-*/
-.onyx-picker-decorator .onyx-button {
- padding: 12px 8px 12px 8px;
- width: 150px;
-}
-.onyx-picker .onyx-menu-item {
- text-align: left;
- text-overflow: ellipsis;
-}
-.picker-icon {
- position: absolute;
- right: 0;
- margin: 0 10px 0 2px;
- color: #070707;
-}
-.xv-picker-button {
- text-align: left;
-}
-.xv-picker-button .picker-content {
- max-width: 100px;
- overflow: hidden;
-}
-.xv-picker-button.disabled {
- color: #777777;
-}
-.xv-picker-label {
- color: #070707;
- padding: 20px 8px 8px 8px;
- text-align: right;
- width: 130px;
-}
-.xv-picker-label.disabled {
- color: #777777;
-}
-.xv-combobox-note {
- padding: 14px 3px 8px 3px;
- text-align: left;
-}
-/*
- Styles relating to the grid box
-*/
-/* Entire box including the grid and the summary panel */
-.xv-grid-box {
- /**
- This is the most general grid row that
- is not specific to read-only or selected
- */
-}
-.xv-grid-box.small-panel {
- width: 600px;
-}
-.xv-grid-box.medium-panel {
- width: 700px;
-}
-.xv-grid-box.large-panel {
- width: 800px;
-}
-.xv-grid-box .enyo-list-page > *:first-child .xv-grid-row {
- border-top: 0;
-}
-.xv-grid-box .xv-above-grid-list {
- border: 0;
-}
-.xv-grid-box .xv-scroller {
- background: #f8f8f8;
-}
-.xv-limit-description .xv-grid-box .xv-grid-attr.bold {
- font-weight: bold;
-}
-.xv-grid-box .xv-grid-attr.error {
- color: #ff0000;
-}
-.xv-grid-box .xv-grid-attr.emphasis {
- color: #009000;
-}
-.xv-grid-box .xv-grid-attr.warn {
- color: #ff9c00;
-}
-.xv-grid-box .xv-grid-attr.italic {
- font-style: italic;
-}
-.xv-grid-box .xv-grid-attr.placeholder {
- font-style: italic;
- color: #93a1a1;
-}
-.xv-grid-box .xv-grid-attr.hyperlink {
- color: blue;
-}
-.xv-grid-box .xv-gridbox-button {
- color: #357ec7;
- font-size: 18px;
- border: none;
- background: transparent;
-}
-.xv-grid-box .xv-grid-row {
- font-size: 12px;
- background-color: #d8d8d8;
- border-bottom: 1px solid #aaaaaa;
- vertical-align: top;
- /**
- This is the grid header row
- */
-}
-.xv-grid-box .xv-grid-row > * {
- display: inline-block;
-}
-.xv-grid-box .xv-grid-row .xv-grid-header {
- background-color: #d7d7d7;
- color: #0e0e0e;
- font-size: .8em;
- font-weight: bold;
- text-transform: uppercase;
- padding-top: 4px;
-}
-.xv-grid-box .xv-grid-row .xv-grid-header.last {
- border-right: none;
+.xv-grid-box .xv-grid-row .xv-grid-header.last {
+ border-right: none;
}
.xv-grid-box .xv-grid-row > * {
padding: 6px 4px;
text-align: center;
font-size: 24px;
}
+/**
+ Styles relating to Lists
+*/
+.xv-list-header {
+ background-color: #d8d8d8;
+ color: #fdfdfd;
+ font-size: .6em;
+ font-weight: bold;
+ text-transform: uppercase;
+ padding-top: 4px;
+ padding-bottom: 4px;
+ border-bottom: 1px solid #aaaaaa;
+}
+.xv-list-header .xv-list-column.last {
+ border-right: none;
+}
+.xv-list-header .xv-list-column.name-column,
+.xv-list-header .xv-list-column.first,
+.xv-list-header .xv-list-column.second,
+.xv-list-header .xv-list-column.third,
+.xv-list-header .xv-list-column.short,
+.xv-list-header .xv-list-column.small,
+.xv-list-header .xv-list-column.medium,
+.xv-list-header .xv-list-column.descr {
+ padding-left: 7px;
+}
+/* List */
+.xv-list-column.line-number {
+ width: 30px;
+ text-align: right;
+}
+.xv-list-column.name-column {
+ width: 200px;
+}
+.xv-list-column.right-column {
+ width: 100px;
+ text-align: right;
+}
+.xv-list-column.short {
+ width: 100px;
+}
+.xv-list-column.small {
+ width: 125px;
+}
+.xv-list-column.medium {
+ width: 150px;
+}
+.xv-list-column.first {
+ width: 300px;
+}
+.xv-list-column.second {
+ width: 200px;
+}
+.xv-list-column.third {
+ width: 100px;
+}
+.xv-list-column.money,
+.xv-list-column.quantity {
+ width: 75px;
+ text-align: right;
+}
+.xv-list-column.descr {
+ width: 200px;
+}
+.xv-list-column.icon {
+ width: 10px;
+}
+.xv-list {
+ background: #f8f8f8;
+}
+.xv-list .xv-list-item > * {
+ display: inline-block;
+ vertical-align: middle;
+}
+.xv-list .xv-list-item {
+ background-color: #fdfdfd;
+ border-bottom: 1px solid #d7d7d7;
+ min-height: 32px;
+}
+.xv-list .xv-list-item.header {
+ padding-top: 0;
+}
+.xv-list .xv-list-item.inactive {
+ background-color: #d8d8d8;
+ color: #070707;
+}
+.xv-list .xv-list-item.inactive .xv-list-column .xv-list-attr {
+ background: transparent;
+}
+.xv-list .xv-list-item.inactive .xv-list-column .xv-list-attr.placeholder {
+ color: #d8d8d8;
+}
+.xv-list .xv-list-item .xv-list-column .list-icon {
+ padding: 2px;
+ color: #666666;
+ vertical-align: sub;
+ border: 1px solid #efefef;
+ -webkit-border-radius: 2px;
+ -moz-border-radius: 2px;
+ border-radius: 2px;
+}
+.xv-list .xv-list-item.item-selected {
+ background: #226b9a;
+ background-color: #1f608c;
+ background-image: -moz-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: -webkit-gradient(linear, 0 0, 0 100%, from(#226b9a), to(#1a4f77));
+ background-image: -webkit-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: -o-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: linear-gradient(to bottom, #226b9a, #1a4f77);
+ background-repeat: repeat-x;
+ filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ff226b9a', endColorstr='#ff1a4f77', GradientType=0);
+}
+.xv-list .xv-list-item.item-selected .xv-list-attr {
+ color: #fdfdfd;
+}
+.xv-list .xv-list-item.item-selected .xv-list-attr.placeholder {
+ font-style: italic;
+ color: #99ccff;
+}
+.xv-list .xv-list-item.item-selected .xv-list-attr.hyperlink {
+ color: #ff6529;
+}
+.xv-list .xv-list-item.item-selected .xv-list-attr.header {
+ background: #99ccff;
+}
+.xv-list .xv-list-item .xv-list-item-gear {
+ position: absolute;
+ right: 0px;
+ z-index: 999;
+}
+.xv-list.xv-grid-list {
+ background: #f8f8f8;
+}
+.xv-list.xv-grid-list .xv-list-item > * {
+ vertical-align: top;
+}
+.xv-list.xv-grid-list .xv-list-item {
+ padding-top: 7px !important;
+ padding-bottom: 9px !important;
+ border-bottom: 1px solid #aaaaaa !important;
+ background: #f8f8f8;
+}
+.xv-list.xv-grid-list .xv-list-item.item-selected {
+ background: #226b9a;
+ background-color: #1f608c;
+ background-image: -moz-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: -webkit-gradient(linear, 0 0, 0 100%, from(#226b9a), to(#1a4f77));
+ background-image: -webkit-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: -o-linear-gradient(top, #226b9a, #1a4f77);
+ background-image: linear-gradient(to bottom, #226b9a, #1a4f77);
+ background-repeat: repeat-x;
+ filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ff226b9a', endColorstr='#ff1a4f77', GradientType=0);
+}
+.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr {
+ color: #fdfdfd;
+}
+.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.placeholder {
+ font-style: italic;
+ color: #99ccff;
+}
+.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.hyperlink {
+ color: #ff6529;
+}
+.xv-list.xv-grid-list .xv-list-item.item-selected .xv-list-attr.header {
+ background: #99ccff;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column {
+ vertical-align: top;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column .xv-list-attr {
+ font-size: 12px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.last {
+ border-right: none;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.name-column {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.first {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.second {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.third {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.short {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.small {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.medium {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column.descr {
+ padding-left: 7px;
+}
+.xv-list.xv-grid-list .xv-list-item .xv-list-column .xv-list-attr {
+ padding: 0px;
+}
+.xv-list-attr {
+ padding: 5px;
+ font-size: .8em;
+ white-space: nowrap;
+ overflow: hidden;
+ text-overflow: ellipsis;
+ color: #070707;
+}
+.xv-list-attr.header {
+ padding: 4px;
+ background: #d8d8d8;
+ font-size: .7em;
+ font-weight: bold;
+ text-transform: uppercase;
+ color: #fdfdfd;
+}
+.xv-list-attr.footer {
+ padding: 4px;
+ background: #d8d8d8;
+ font-size: .7em;
+ font-weight: bold;
+ text-transform: uppercase;
+ color: #070707;
+}
+.xv-list-attr.right {
+ position: absolute;
+ right: 10px;
+}
+.xv-list-attr.text-align-right {
+ text-align: right;
+}
+.xv-list-attr.bold {
+ font-weight: bold;
+}
+.xv-list-attr.error {
+ color: #ff0000;
+}
+.xv-list-attr.emphasis {
+ color: #009000;
+}
+.xv-list-attr.warn {
+ color: #ff9c00;
+}
+.xv-list-attr.italic {
+ font-style: italic;
+}
+.xv-list-attr.placeholder {
+ font-style: italic;
+ color: #777777;
+}
+.xv-list-attr.hyperlink {
+ color: #357ec7;
+ cursor: pointer;
+}
+.xv-list-attr.disabled {
+ color: #777777;
+}
+/* Navigator */
+.xv-navigator-header {
+ font-size: small;
+ font-weight: bold;
+ text-transform: uppercase;
+ color: #ff6600;
+ padding-left: 20px;
+ border-bottom: 1px solid #0e0e0e;
+}
+.xv-workspace-header {
+ color: #fdfdfd;
+ white-space: nowrap;
+ overflow: hidden;
+ text-overflow: ellipsis;
+ padding: 8px 0 0 8px;
+}
/**
Styles relating to widgets in the pullout
*/
{name: "startupProgressBar", kind: "onyx.ProgressBar",
classes: "xv-startup-progress onyx-progress-button", progress: 0}
]},
- {kind: "onyx.Popup", name: "notifyPopup", centered: true,
+ {kind: "onyx.Popup", name: "notifyPopup", classes: "xv-popup", centered: true,
onHide: "notifyHidden",
modal: true, floating: true, scrim: true, components: [
- {name: "notifyMessage"},
- {tag: "br"},
- {kind: "onyx.Button", content: "_ok".loc(), name: "notifyOk", ontap: "notifyTap",
- classes: "xv-popup-button", showing: false},
- {kind: "onyx.Button", content: "_yes".loc(), name: "notifyYes", ontap: "notifyTap",
- classes: "xv-popup-button", showing: false},
- {kind: "onyx.Button", content: "_no".loc(), name: "notifyNo", ontap: "notifyTap",
- classes: "xv-popup-button", showing: false},
- {kind: "onyx.Button", content: "_cancel".loc(), name: "notifyCancel", ontap: "notifyTap",
- classes: "xv-popup-button", showing: false}
+ {name: "notifyMessage", classes: "message"},
+ {classes: "xv-buttons", name: "notifyButtons", components: [
+ {kind: "onyx.Button", content: "_ok".loc(), name: "notifyOk", ontap: "notifyTap",
+ showing: false, classes: "text"},
+ {kind: "onyx.Button", content: "_yes".loc(), name: "notifyYes", ontap: "notifyTap",
+ showing: false, classes: "text"},
+ {kind: "onyx.Button", content: "_no".loc(), name: "notifyNo", ontap: "notifyTap",
+ showing: false, classes: "text"},
+ {kind: "onyx.Button", content: "_cancel".loc(), name: "notifyCancel", ontap: "notifyTap",
+ showing: false, classes: "text"}
+ ]}
]},
{kind: "onyx.Popup", name: "popupWorkspace", classes: "xv-popup xv-groupbox-popup", centered: true,
autoDismiss: false, modal: true, floating: true, scrim: true},
resizeHandler: function () {
this.inherited(arguments);
if (this.$.notifyPopup.showing) {
- this.$.notifyPopup.applyStyle("opacity", 1); // XXX not sure why this hack is necessary.
+ // This is a fix for an enyo bug that renders the popup as clear
+ this.$.notifyPopup.applyStyle("opacity", 1);
}
},
activate: function () {
return this.$.navigator;
},
getNotifyButtons: function () {
- return _.filter(this.$, function (control) {
- return control.name.substring(0, 6) === 'notify' && control.kind === 'onyx.Button';
- });
+ return this.$.notifyButtons.controls;
},
getStartupProgressBar: function () {
return this.$.startupProgressBar;
inEvent.type = inEvent.type || XM.Model.NOTICE;
// show the appropriate buttons
- _.each(this.$.notifyPopup.children, function (component) {
- if (component.kind !== "onyx.Button") {
- // not a button: do nothing.
- } else if (_.indexOf(typeToButtonMap[String(inEvent.type)], component.name) >= 0) {
- // in the show-me array, so show
- component.setShowing(true);
- } else {
- // not in the show-me array, so hide
- component.setShowing(false);
- }
+ _.each(this.getNotifyButtons(), function (component) {
+ component.setShowing(_.indexOf(typeToButtonMap[String(inEvent.type)], component.name) >= 0);
});
-
// allow custom button text
this.$.notifyYes.setContent(inEvent.yesLabel || "_yes".loc());
this.$.notifyNo.setContent(inEvent.noLabel || "_no".loc());
// it's the OK button unless it's a 2- or 3- way question, in which case it's YES
this._activeNotify = inEvent.type === XM.Model.QUESTION || inEvent.type === XM.Model.YES_NO_CANCEL ? 1 : 0;
_.each(this.getNotifyButtons(), function (button, index) {
- button.addRemoveClass("onyx-blue", index === that._activeNotify);
+ button.addRemoveClass("selected", index === that._activeNotify);
});
// delete out any previously added customComponents/customComponentControls
// Add the custom component
if (inEvent.component) {
inEvent.component.name = "customComponent";
- inEvent.component.addBefore = this.$.notifyOk;
+ // can add styling class here instead of inline css
+ inEvent.component.addBefore = this.$.notifyButtons;
this.$.notifyPopup.createComponent(inEvent.component);
- this.$.notifyPopup.$.customComponent.addStyles("color:black;");
if (inEvent.componentModel) {
this.$.notifyPopup.$.customComponent.setValue(inEvent.componentModel);
}
this._notifyDone = false;
this.$.notifyPopup.render();
this.$.notifyPopup.show();
- this.$.notifyPopup.applyStyle("opacity", 1); // XXX not sure why this hack is necessary.
+ // Without this fix, the popup renders transparent
+ this.$.notifyPopup.applyStyle("opacity", 1);
},
notifyHidden: function () {
if (!this._notifyDone) {
} else if (keyCode === 37 || (keyCode === 9 && isShift)) {
// left or shift-tab
- notifyButtons[activeIndex].removeClass("onyx-blue");
+ notifyButtons[activeIndex].removeClass("selected");
for (nextShowing = activeIndex - 1; nextShowing >= 0; nextShowing--) {
if (nextShowing === 0 && !notifyButtons[nextShowing].showing) {
// there are no showing buttons to the left
activeIndex = nextShowing;
}
this._activeNotify = activeIndex;
- notifyButtons[activeIndex].addClass("onyx-blue");
+ notifyButtons[activeIndex].addClass("selected");
} else if (keyCode === 39 || keyCode === 9) {
// right or tab
- notifyButtons[activeIndex].removeClass("onyx-blue");
+ notifyButtons[activeIndex].removeClass("selected");
for (nextShowing = activeIndex + 1; nextShowing < notifyButtons.length; nextShowing++) {
if (nextShowing + 1 === notifyButtons.length && !notifyButtons[nextShowing].showing) {
// there are no showing buttons to the right
}
this._activeNotify = activeIndex;
- notifyButtons[activeIndex].addClass("onyx-blue");
+ notifyButtons[activeIndex].addClass("selected");
}
},
/**
maxHeight: "400px",
horizontal: "hidden"
}, {owner: this});
- this.$.popupWorkspace.createComponent({name: "workspace", kind: inEvent.workspace, container: this.$.popupScroller});
+ this.$.popupWorkspace.createComponent({name: "workspace", kind: inEvent.workspace,
+ container: this.$.popupScroller});
+ // TODO: inline css - git rid of it!
this.$.popupWorkspace.$.workspace.addStyles("color:black;");
this.$.popupWorkspace.$.workspace.setValue(inEvent.model);
- this.$.popupWorkspace.createComponent({
+ // create button bar
+ this.$.popupWorkspace.createComponent({classes: "xv-buttons", name: "workspaceButtons"}, {owner: this});
+ this.$.workspaceButtons.createComponents([{
kind: "onyx.Button",
content: "_save".loc(),
name: "popupWorkspaceSave",
ontap: "popupWorkspaceTap",
- classes: "onyx-blue xv-popup-button"
- }, {owner: this});
- this.$.popupWorkspace.createComponent({
+ classes: "selected text"
+ },
+ {
kind: "onyx.Button",
content: "_cancel".loc(),
name: "popupWorkspaceCancel",
ontap: "popupWorkspaceTap",
- classes: "xv-popup-button"
- }, {owner: this});
-
+ classes: "text"
+ }], {owner: this});
this.$.popupWorkspace.render();
this.$.popupWorkspace.show();
- this.$.popupWorkspace.applyStyle("opacity", 1); // XXX not sure why this hack is necessary.
+ // Without this fix, the popup renders transparent
+ this.$.popupWorkspace.applyStyle("opacity", 1);
},
popupWorkspaceTap: function (inSender, inEvent) {
var model = this.$.popupWorkspace.$.workspace.value,
},
fetchSuccess = function (model, result) {
var sendExtensions = function (res, extensions) {
+ var filteredExtensions;
+ if (req.query.extensions) {
+ // the user is requesting to only see a certain set of extensions
+ filteredExtensions = JSON.parse(req.query.extensions);
+ extensions = extensions.filter(function (ext) {
+ return _.contains(filteredExtensions, ext.name);
+ });
+ }
+
extensions.sort(function (ext1, ext2) {
if (ext1.loadOrder !== ext2.loadOrder) {
return ext1.loadOrder - ext2.loadOrder;
function (req, res, next) {
var pathName = "/app";
if (req && req.session && !req.session.oauth2 && req.session.passport && req.session.passport.user && req.session.passport.user.organization) {
+ if (req.body.extensions) {
+ pathName = pathName + "?extensions=" + req.body.extensions;
+ }
if (req.body.hash && req.body.hash.charAt(0) === "#") {
pathName = pathName + req.body.hash;
}
(function () {
"use strict";
- var ursa = require("ursa"),
- exec = require("child_process").exec,
+ var exec = require("child_process").exec,
forge = require("node-forge"),
spawn = require("child_process").spawn,
async = require("async"),
res.send({isError: true, error: err});
},
genKey = function (model, result) {
- /**
- * This is REALLY slow in pure javascript. ursa is much faster.
- * @See: https://github.com/digitalbazaar/forge/issues/125
- forge.pki.rsa.generateKeyPair({bits: 2048, workers: 2}, function(err, keypair) {
+ forge.pki.rsa.generateKeyPair({bits: 2048, workers: -1}, function (err, keypair) {
if (err) {
res.send({isError: true, message: "Error generating keypair: " + err.message, error: err});
return;
fetchSuccess(model, result, keypair);
});
- */
-
- // Use ursa for the key gen and then convert to forge's format.
- var keypair = ursa.generatePrivateKey();
- var keys = {
- privateKey: forge.pki.privateKeyFromPem(keypair.toPrivatePem().toString()),
- publicKey: forge.pki.publicKeyFromPem(keypair.toPublicPem().toString())
- };
-
- fetchSuccess(model, result, keys);
},
sendP12 = function (keys) {
// It's possible and much easier to generate the p12 file without a
// give any running process the opportunity to save state
// or log as gracefully as possible
process.once('exit', _.bind(X.cleanup, X));
-
- _.forEach(["SIGINT", "SIGHUP", "SIGQUIT", "SIGKILL", "SIGSEGV", "SIGILL"], function (sig) {
- process.once(sig, _.bind(sighandler, X, sig));
- });
});
}());
"version": "2.3.3",
"from": "underscore.string@~2.3.3"
},
- "ursa": {
- "version": "0.8.0",
- "from": "ursa@0.8.x"
- },
"winston": {
"version": "0.7.3",
"from": "winston@0.7.x",
}
},
"xtuple-linguist": {
- "version": "0.1.0",
+ "version": "0.1.1",
"from": "xtuple-linguist@0.1.x"
},
"xtuple-query": {
"underscore": "1.4.x",
"winston": "0.7.x",
"underscore.string": "~2.3.3",
- "ursa": "0.8.x",
"xtuple-linguist": "0.1.x",
"jquery": "~2.1.1"
},
"googleapis": "~0.4.6"
},
"engines": {
- "node": "0.8.x"
+ "node": "^0.10"
},
"main": "node-datasource/main.js",
"scripts": {
python-software-properties \
software-properties-common
-NODE_VERSION=0.8.26
+NODE_VERSION=0.10.31
DEBDIST=`lsb_release -c -s`
echo "Trying to install xTuple for platform ${DEBDIST}"
sudo nvm alias xtuple $NODE_VERSION
# use latest npm
- npm install -g npm@1.4.25
+ npm install -fg npm@1.4.25
# npm no longer supports its self-signed certificates
log "telling npm to use known registrars..."
npm config set ca ""
baseName.indexOf('distribution') >= 0,
registerExtension: isExtension,
runJsInit: !isFoundation && !isLibOrm,
- wipeViews: isApplicationCore && spec.wipeViews,
+ wipeViews: isFoundation && spec.wipeViews,
+ wipeOrms: isApplicationCore && spec.wipeViews,
extensionLocation: isCoreExtension ? "/core-extensions" :
isPublicExtension ? "/xtuple-extensions" :
isPrivateExtension ? "/private-extensions" :
"use strict";
var async = require("async"),
- exec = require('child_process').exec,
+ proc = require('child_process'),
path = require('path'),
os = require('os'),
winston = require('winston'),
var schemaPath = path.join(path.dirname(spec.source), "440_schema.sql");
winston.info("Building schema for database " + databaseName);
- exec("psql -U " + creds.username + " -h " + creds.hostname + " --single-transaction -p " +
- creds.port + " -d " + databaseName + " -f " + schemaPath,
- {maxBuffer: 40000 * 1024 /* 200x default */}, done);
+ var process = proc.spawn('psql', [
+ '-q', '-U', creds.username, '-h', creds.hostname, '--single-transaction', '-p',
+ creds.port, '-d', databaseName, '-f', schemaPath
+ ], { stdio: 'inherit' });
+ process.on('exit', done);
+
},
populateData = function (done) {
winston.info("Populating data for database " + databaseName + " from " + spec.source);
- exec("psql -U " + creds.username + " -h " + creds.hostname + " --single-transaction -p " +
- creds.port + " -d " + databaseName + " -f " + spec.source,
- {maxBuffer: 40000 * 1024 /* 200x default */}, done);
+ var process = proc.spawn('psql', [
+ '-q', '-U', creds.username, '-h', creds.hostname, '--single-transaction', '-p',
+ creds.port, '-d', databaseName, '-f', spec.source
+ ], { stdio: 'inherit'});
+ process.on('exit', done);
},
// use exec to restore the backup. The alternative, reading the backup file into a string to query
// doesn't work because the backup file is binary.
restoreBackup = function (done) {
- exec("pg_restore -U " + creds.username + " -h " + creds.hostname + " -p " +
- creds.port + " -d " + databaseName + " -j " + os.cpus().length + " " + spec.backup, function (err, res) {
+ var process = proc.spawn('pg_restore', [
+ '-U', creds.username, '-h', creds.hostname, '-p', creds.port, '-d', databaseName,
+ '-j', os.cpus().length, spec.backup
+ ], { stdio: 'inherit' });
+ process.on('exit', function (err, res) {
if (err) {
console.log("ignoring restore db error", err);
}
}
if (options.wipeViews) {
+ // If we want to pre-emptively wipe out the views, the best place to do it
+ // is at the start of the core application code
+ fs.readFile(path.join(__dirname, "../../../enyo-client/database/source/wipe_views.sql"),
+ function (err, wipeSql) {
+ if (err) {
+ callback(err);
+ return;
+ }
+ extensionSql = wipeSql + extensionSql;
+ callback(null, extensionSql);
+ });
+
+ } else if (options.wipeOrms) {
// If we want to pre-emptively wipe out the views, the best place to do it
// is at the start of the core application code
fs.readFile(path.join(__dirname, "../../../enyo-client/database/source/delete_system_orms.sql"),
smoke = require("../../lib/smoke");
describe('Configuration Workspaces', function () {
- this.timeout(30 * 1000);
+ this.timeout(60 * 1000);
before(function (done) {
zombieAuth.loadApp(done);
});
it('should all be accessible', function (done) {
- this.timeout(80 * 1000);
+ this.timeout(120 * 1000);
var navigator, workspace,
list,
i = -1;
assert = require("chai").assert;
describe('Workspaces', function () {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
it('should log in first', function (done) {
zombieAuth.loadApp(done);
});
var workspaceContainer, workspace, model, id, moduleContainer;
beforeEach(function (done) {
- this.timeout(10 * 1000);
+ this.timeout(60 * 1000);
smoke.navigateToExistingWorkspace(XT.app, "XV.ClassCodeList", function (_workspaceContainer) {
workspaceContainer = _workspaceContainer;
});
});
afterEach(function (done) {
- this.timeout(10 * 1000);
+ this.timeout(60 * 1000);
// maybe one of the tests already released the lock
if (!model.hasLockKey()) {
});
afterEach(function (done) {
- this.timeout(10 * 1000);
+ this.timeout(30 * 1000);
// restore permissions
_.extend(XT.session.privileges.attributes, originalPrivileges);
});
};
- describe('Sales Order Workspace', function () {
+ // TODO: move to sales order spec
+ describe.skip('Sales Order Workspace', function () {
this.timeout(30 * 1000);
//
moduleContainer = XT.app.$.postbooks;
/** Open the first model's salesOrderLineWorkspace...
- Copied from gridBox buttonTapped function (expandGridRowButton)
+ Copied from gridBox buttonTapped function (expandGridRowButton)
*/
lineItemBox.doChildWorkspace({
workspace: lineItemBox.getWorkspace(),
index: lineItemBox.getValue().indexOf(model)
});
- /** The line item's workspace model has been deleted (DESTROYED_CLEAN).
+ /** The line item's workspace model has been deleted (DESTROYED_CLEAN).
Client is now in SalesOrderWorkspace.
*/
var statusChanged = function () {
model.once("status:DESTROYED_CLEAN", statusChanged);
- // Function to keep checking for notifyPopup showing and then tap yes.
+ // Function to keep checking for notifyPopup showing and then tap yes.
// This will fire right after the delete below.
var notifyPopupInterval = setInterval(function () {
if (!moduleContainer.$.notifyPopup.showing) { return; }
// Step 1: load the environment with Zombie
//
it('can be loaded with a zombie session', function (done) {
- this.timeout(40 * 1000);
+ this.timeout(60 * 1000);
zombieAuth.loadApp({callback: done, verbose: data.verbose,
loginDataPath: data.loginDataPath});
});
// Step 3: initialize the model to get the ID from the database
//
it('can be initialized by fetching an id from the server', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
init(data, done);
});
//
_.each(data.beforeSetActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(data, done);
});
});
it('can have its values set', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
data.updated = false;
setModel(data, done);
});
// if this model has comments, set them on the model
if (data.commentType) {
it('can have its comments set', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
setComments(data, done);
});
}
_.each(data.beforeSaveActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(data, done);
});
});
//
if (!data.skipSave) {
it('can be saved to the database', function (done) {
- this.timeout(10 * 1000);
+ this.timeout(60 * 1000);
save(data, done);
});
_.each(data.afterSaveActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(data, done);
});
});
// Step 7: save the updated model to the database
//
it('can be re-saved to the database', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
save(data, done);
});
}
//
_.each(data.beforeDeleteActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(data, done);
});
});
if (!data.skipDelete) {
it('can be deleted from the database', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
destroy(data, done);
});
}
_.each(data.afterDeleteActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(data, done);
});
});
exports.updateFirstModel = function (test) {
it('should allow a trivial update to the first model of ' + test.kind, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
navigateToExistingWorkspace(XT.app, test.kind, function (workspaceContainer) {
var updateObj,
statusChanged,
var workspaceContainer,
workspace;
it('can get to a new workspace', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
navigateToNewWorkspace(XT.app, spec.listKind, function (_workspaceContainer) {
workspaceContainer = _workspaceContainer;
done();
});
_.each(spec.beforeSaveUIActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(workspace, done);
});
});
it('can save the workspace', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
if (spec.captureObject) {
XG = XG || {};
XG.capturedId = workspace.value.id;
});
_.each(spec.afterSaveUIActions || [], function (spec) {
it(spec.it, function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
spec.action(workspace, done);
});
});
return;
}
it('can delete the item from the list', function (done) {
- this.timeout(20 * 1000);
+ this.timeout(60 * 1000);
deleteFromList(XT.app, workspace.value, done);
});
};
regexp:true, undef:true, strict:true, trailing:true, white:true */
/*global XT:true, XM:true, XV:true, XZ:true, enyo:true, XG:true */
+var _ = require('underscore');
+global.URL = require('url');
+var parse = global.URL.parse;
+var resolve = global.URL.resolve;
+global.URL.parse = function (url) {
+ "use strict";
+ console.log('URL.parse', url);
+ if (_.isObject(url) && _.isString(url.href)) {
+ return parse(url.href);
+ }
+ else {
+ return parse(url);
+ }
+};
+global.URL.resolve = function (from, to) {
+ "use strict";
+ console.log('URL.resolve from', from);
+ console.log('URL.resolve to', to);
+ if (_.isObject(from)) {
+ from = from.href || '/';
+ }
+ if (_.isObject(to)) {
+ to = to.href || '';
+ }
+
+ return resolve(from, to);
+};
+
+
// global objects
enyo = {};
XT = {};
XV = {};
XZ = {}; // xTuple Zombie. Used to help zombie within the context of these tests.
+// https://github.com/mikeal/request/issues/418#issuecomment-17149236
+process.env.NODE_TLS_REJECT_UNAUTHORIZED = "0";
+
var assert = require('assert'),
zombie = require('zombie'),
- URL = require('url'),
_ = require('underscore');
return;
}
- var parse = URL.parse;
- URL.parse = function (url) {
- if (_.isObject(url) && _.isString(url.href)) {
- return parse(url.href);
- }
- else {
- return parse(url);
- }
- };
- zombie.visit(host, {debug: verboseMode}, function (e, browser) {
+ zombie.visit(host, {debug: verboseMode, runScripts: false}, function (e, browser) {
if (e) {
- //console.log("Zombie visit error: ", e);
+ console.log("Zombie visit error: ", e);
}
//
// This is the login screen
//
+ browser.runScripts = true;
browser
.fill('id', username)
.fill('password', password)
@description When currency or invoice date is changed outstanding credit should be
recalculated.
*/
- it.skip("When currency or invoice date is changed outstanding credit should be recalculated",
+ it.("When currency or invoice date is changed outstanding credit should be recalculated",
function (done) {
- // frustratingly nondeterministic
+
this.timeout(9000);
var outstandingCreditChanged = function () {
if (invoiceModel.get("outstandingCredit")) {
/*global describe:true, it:true, XT:true, XM:true, XV:true, process:true,
module:true, require:true, exports:true */
- (function () {
+(function () {
"use strict";
+ var assert = require("chai").assert,
+ smoke = require("../lib/smoke");
+
/**
Sites typically describe physical production and storage facilities. work centers, item sites, and site locations belong to sites.
@class
@alias Site
**/
- exports.spec = {
- skipAll: true,
- // XXX very awkward
- recordType: "XM.Site"
+ var spec = {
+ // smoke is ran in the inventory site spec
+ skipSmoke: true,
+ recordType: "XM.Site",
+ collectionType: "XM.SiteCollection",
+ /**
+ @member -
+ @memberof Sites.prototype
+ @description The site collection is cached.
+ */
+ cacheName: "XM.sites",
+ listKind: "XV.SiteList",
+ instanceOf: "XM.Document",
+ extensions: [],
+ /**
+ @member -
+ @memberof Sites.prototype
+ @description Sites are lockable.
+ */
+ isLockable: true,
+ /**
+ @member -
+ @memberof Sites.prototype
+ @description The ID attribute is "code", which will not be automatically uppercased.
+ */
+ idAttribute: "code",
+ enforceUpperKey: false,
+ attributes: ["code", "address", "code", "comments", "contact", "description", "incoterms",
+ "isActive", "siteType", "taxZone"],
+ privileges: {
+ createUpdateDelete: "MaintainWarehouses",
+ view: true
+ },
+ createHash: {
+ isActive: true,
+ code: "NewSite" + Math.random(),
+ siteType: {name: "MFG"}
+ },
+ updatableField: "description"
};
+
var additionalTests = function () {
/**
@member Setup
@memberof Site.prototype
- @description Multiple Item Sites should not be allowed on Postbooks
+ @description Multiple Sites should not be allowed on Postbooks
*/
- it.skip("Multiple Item Sites should not be allowed on Postbooks", function () {
+ it("Multiple Sites should not be allowed on Postbooks", function (done) {
+ if (!XT.extensions.inventory) {
+ assert.equal(XM.sites.length, 1);
+ smoke.navigateToList(XT.app, "XV.SiteList");
+ assert.isTrue(XT.app.$.postbooks.getActive().$.newButton.disabled);
+ return done();
+ }
+ done();
});
-};
+ };
+ exports.spec = spec;
exports.additionalTests = additionalTests;
-}());
\ No newline at end of file
+}());
projects / xtuple / commitdiff