Blog

Our latest posts and rants

Dynamics GP logo

Adding and Using a Microsoft Dynamics GP eConnect Node, Part 2

In a previous post we created the database entries for a new eConnect node. We’re going to skip the .NET parts and go straight to eOne SmartConnect.

eOne Logo

SmartConnect has been evolving rapidly. As of this writing we are using version 20.

SmartConnect version 20.12.2.38

Version of SmartConnect used for this post

To make our new eConnect node visible in SmartConnect, we need to use the SmartConnect Node Maintenance window.

Click it. I dare you.

Click it. I dare you.

In this case I choose to make a new group for the node. So the first thing I do in the Node Maintenance window is create that new group.

The next button

The next button

Add a group

Add a group

We’ll see later where this new group comes into play.

Now we’ll add the node itself.

Adding a node

Adding a node

SmartConnect finds the parameters of the stored procedure for us. However, in this version, everything is type String. We’ll change some of those types.

We could also change the display names if we wanted. For example, BACHNUMB could display as Batch Name, etc. I am fond of the names I used for the stored procedure parameters, so I’ll leave them be.

Check off the required parameters

Check off the required parameters

Change field types as needed

Change field types as needed

So, our new group contains only one node, which gives us the ability to insert a transaction document into a batch.

We can now use the new node in a SmartConnect map. This is where you see the node group we added, our node display name, etc.

Create a new map using our new node

Create a new map using our new node

When we set up the mapping details you’ll see our fields.

Using our new fields

Using our new fields

Now you should be able to finish the map as usual, run it, and see an assembly batch in GP (Transactions, Inventory, Batches window).

Here ends today’s lesson. I hope it is helpful!

Dynamics GP logo

Adding and Using a Microsoft Dynamics GP eConnect Node, Part 1

eConnect provides a way to move data into and out of Microsoft Dynamics GP. In this post we’ll construct the database components for an eConnect node. We will not construct the .NET pieces.

The piece we will construct here is sufficient to allow us to use the new node from eOne SmartConnect. We’ll show how to do that in a subsequent post.

eOne Logo

We’ll make a node that allows us to add documents to a bill of material assembly batch in GP.

An Assembly

An Assembly

The database piece has this general shape:

  • Top level stored procedure is taBMTransactionInsert
  • That references stored procedure taBMTransactionInsert_Custom
  • That references stored procedures taBMTransactionInsertPre and taBMTransactionInsertPost

taBMTransactionInsert establishes the parameter names and types for all the stored procedures. This is the stored procedure that is invoked by SmartConnect.

taBMTransactionInsert_Custom is where we will do the work.

taBMTransactionInsertPre and taBMTransactionInsertPost are invoked before and after the taBMTransactionInsert_Custom procedure. This probably isn’t needed for our custom node, but this way we follow the Microsoft convention.

So without further ado, here come the stored procedures. These stored procedures need to be created in each company database where they’ll be used.

[sql]
CREATE procedure [dbo].[taBMTransactionInsert]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) = ” output,
@I_vInventoryAccountOffSet varchar(75) = ” output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int = 0 output,
@oErrString varchar(255) = ” output
as
begin
execute dbo.taBMTransactionInsert_Custom
@I_vBACHNUMB output,
@I_vBMDOCNBR output,
@I_vITEMNMBR output,
@I_vTRXQTY output,
@I_vUOFM output,
@I_vUNITCOST output,
@I_vTRXLOCTN output,
@I_vInventoryAccount output,
@I_vInventoryAccountOffSet output,
@I_vOverrideQty output,
@I_vUSRDEFND1 output,
@I_vUSRDEFND2 output,
@I_vUSRDEFND3 output,
@I_vUSRDEFND4 output,
@O_iErrorState output,
@oErrString output;
end;
[/sql]

[sql]
CREATE procedure [dbo].[taBMTransactionInsert_Custom]
@I_vBACHNUMB char(15) output, /* Batch Number <Required> */
@I_vBMDOCNBR char(17) output, /* BM Document Number <Required> */
@I_vITEMNMBR char(30) output, /* Assembly Number <Required> */
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output, /* Inventory Site <Required> */
@I_vInventoryAccount varchar(75) = ” output, /* Input Inventory Account, no punctuation */
@I_vOutputInventoryAccount varchar(75) = ” output, /* Output Inventory Account, no punctuation */
@I_vInventoryAccountOffSet varchar(75) = ” output, /* Inventory Offset Account, no punctuation */
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND2 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND3 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND4 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */
@oErrString varchar(255) = ” output /* Return Error Code List */
as
begin

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

execute dbo.taBMTransactionInsertPre
@I_vBACHNUMB,
@I_vBMDOCNBR,
@I_vITEMNMBR,
@I_vTRXQTY,
@I_vUOFM,
@I_vUNITCOST,
@I_vTRXLOCTN,
@I_vInventoryAccount,
@I_vInventoryAccountOffSet,
@I_vOverrideQty,
@I_vUSRDEFND1,
@I_vUSRDEFND2,
@I_vUSRDEFND3,
@I_vUSRDEFND4,
@O_iErrorState,
@oErrString;

declare
@O_oErrorState int,
@iError int,
@iStatus smallint,@iAddCodeErrState int;
/*********************** Initialize locals ******************************/
select
@O_iErrorState = 0,
@oErrString = ”,
@iStatus = 0,
@iAddCodeErrState = 0;
/***************** Custom Procedure edit check validation ***************/
/*If a required variable is ” then we need to add the error code */
/*35010 to the @oErrString output variable.*/
/*The method that eConnect uses to append all error string is the */
/*taUpdateString procedure.*/
/*Error codes can be appended to the @oErrString variable: for example you */
/*could append a 33 44 55 66 to the @oErrString variable */
/*After the error codes have been appended to the @oErrString variable. */
/***********************************************************************/
if ( @I_vBMDOCNBR = ” )
begin
select @O_iErrorState = 35010; /* A required value is blank */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@iAddCodeErrState output;
end;
/* Do some custom business logic */

INSERT INTO BM10200 ([TRX_ID],[BM_Trx_Status],[BCHSOURC],[BACHNUMB],[Batch_ID_Note_Index]
,[Completion_Date],[BM_Start_Date],[TRXDATE],[PSTGDATE]
,[REFRENCE],[Quantity_Shortage_Status],[BM_Trx_Errors],[BM_Component_Errors]
,[NOTEINDX],[MODIFDT],[USER2ENT],[CREATDDT]
,[PTDUSRID],[POSTEDDT],[TRXSORCE]
,[USERDEF1],[USERDEF2],[USRDEF03],[USRDEF04])

VALUES (@I_vBMDOCNBR,3,’ASMENT’,@I_vBACHNUMB,0.00000
,’1/1/1900′,CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101)
,’ ‘,@I_vOverrideQty,0x00000000,0x00000000
,0.00000,CONVERT(char(10),GetDate(),101),’ ‘,CONVERT(char(10),GetDate(),101)
,’ ‘,CONVERT(char(10),GetDate(),101),’ ‘
,@I_vUSRDEFND1,@I_vUSRDEFND2,@I_vUSRDEFND3,@I_vUSRDEFND4);

— To Do: Proper handling for units of measure
— To Do: Check for sufficient quantities on hand
— To Do: Honor the override quantity parameter setting
— To Do: Proper handling for build versus stock flags
— To Do: Detect invalid part numbers and return error code
— To Do: Detect invalid account numbers and return error code
— To Do: Make sure scrap_percentage is cumulative in GP as is assumed below
— To Do: Figure out codes for column [BM_Component_Type] in table BM10300
— To Do: Figure out codes for column [BM_Component_Options] in table BM10300
— To Do: Make account number format general instead of assuming 5-3 format
— To Do: Add optional serial number tracking

WITH CHILDRN(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage_Times_100, IsAssembly, StockMethod, Pseudo_Component_ID, Pseudo_Parent_Component_ID) AS (
SELECT DISTINCT
a.ITEMNMBR,
a.ITEMNMBR,
0,
CAST(1 AS Numeric(19,5)),
CAST(b.SELNGUOM AS CHAR(9)) AS UOFM,
CAST(0 AS BIGINT) AS Scrap_Percentage_Times_100,
1 AS IsAssembly,
CAST(3 AS SMALLINT) /*c.BM_Stock_Method*/,
CAST(0 AS DECIMAL(30,15)) AS Pseudo_Component_ID,
CAST(-1 AS DECIMAL(30,15)) AS Pseudo_Parent_Component_ID
FROM BM00111 a
JOIN IV00101 b
ON a.ITEMNMBR=b.ITEMNMBR
JOIN BM00101 c
ON a.ITEMNMBR=c.ITEMNMBR
UNION ALL
SELECT
p.ITEMNMBR,
c.CMPTITNM,
p.DEPTH + 1,
CAST(c.Design_Qty * p.QTY AS Numeric(19,5)),
CAST(c.UOFM AS CHAR(9)) AS UOFM,
— Accumulate scrap- for example if you have 10% scrap turning A into B, and 10% turning B into C, cumulative total scrap for C is 1-.9*.9=19%
CAST(10000 – (10000 – c.Scrap_Percentage) * (10000 – p.Scrap_Percentage_Times_100) * 0.0001 AS BIGINT) AS Scrap_Percentage_Times_100,
CASE WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101) THEN 1 ELSE 0 END AS IsAssembly,
CAST(
CASE
WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101 WHERE BM_Stock_Method=1) THEN 1
WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101 WHERE BM_Stock_Method=3) THEN 3
ELSE 2
END
AS SMALLINT) AS StockMethod,
CAST(p.Pseudo_Component_ID +
case
when p.depth=0 then 1
when p.depth=1 then 0.1
when p.depth=2 then 0.01
when p.depth=3 then 0.001
when p.depth=4 then 0.0001
when p.depth=5 then 0.00001
when p.depth=6 then 0.000001
when p.depth=7 then 0.0000001
when p.depth=8 then 0.00000001
else 0.000000001
end
* ROW_NUMBER() OVER (ORDER BY ORD) AS DECIMAL(30,15)) AS Pseudo_Component_ID,
CAST(p.Pseudo_Component_ID AS DECIMAL(30,15)) AS Pseudo_Parent_Component_ID
FROM BM00111 c
JOIN CHILDRN p
ON c.ITEMNMBR=p.CMPTITNM
),
COMPONENTS1(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage, IsAssembly, StockMethod, Component_ID, Pseudo_Component_ID, Pseudo_Parent_Component_ID) AS (
SELECT
ITEMNMBR,
CMPTITNM,
DEPTH,
QTY,
UOFM,
Scrap_Percentage_Times_100 * 0.01,
IsAssembly,
StockMethod,
16384 * (ROW_NUMBER() OVER (PARTITION BY ITEMNMBR ORDER BY Pseudo_Component_ID) – 1) AS Component_ID,
Pseudo_Component_ID,
Pseudo_Parent_Component_ID
FROM CHILDRN
),
COMPONENTS(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage, IsAssembly, StockMethod, Component_ID, Parent_Component_ID) AS (
SELECT
a.ITEMNMBR,
a.CMPTITNM,
a.DEPTH,
a.QTY,
a.UOFM,
a.Scrap_Percentage,
a.IsAssembly,
a.StockMethod,
CAST(a.Component_ID AS INT),
CAST(COALESCE(b.Component_ID, -1) AS INT)
FROM COMPONENTS1 a
LEFT JOIN COMPONENTS1 b
ON a.ITEMNMBR=b.ITEMNMBR
AND a.Pseudo_Parent_Component_ID=b.Pseudo_Component_ID
)

INSERT INTO BM10300 ([TRX_ID],[Component_ID],[Parent_Component_ID],[ITEMNMBR],[ITEMDESC] –1
,[UOFM],[LOCNCODE],[Location_Code_Note_Index],[Stock_Quantity],[Assemble_Quantity],[ATYALLOC] –2
,[BM_Stock_Method],[Cost_Type],[INVINDX],[Inventory_Account_Reference],[Inventory_Account_Note_I] –3
,[Variance_Index],[Variance_Account_Reference] –4
,[Variance_Account_Note_In],[Design_Quantity] –5
,[Scrap_Percentage],[STNDCOST],[Standard_Quantity],[Extended_Standard_Cost],[Extended_Standard_Quantity] –6
,[Ext_Assemble_Qty_Posting_Cost],[Ext_Stock_Receipt_Cost],[Ext_Stock_Qty_Posting_Cost] –7
,[Ext_Subasm_Posting_Cost],[Ext_Subasm_Receipt_Cost],[BM_Component_Type],[Lvl],[QTYBSUOM] –8
,[Assemble_Serial_Lot_Count],[Stock_Serial_Lot_Count] –9
,[Temp_Allocated_Quantity],[Temp_Assemble_Quantity],[Temp_Extended_Assemble_Q],[Temp_Location_Code] –10
,[Temp_Stock_Quantity],[Temp_Extended_Stock_Quan],[DECPLQTY],[DECPLCUR],[ITMTRKOP],[NOTEINDX] –11
,[BM_Component_Errors] –12
,[TRXSORCE],[EXTQTYAL],[EXTQTYSEL] –13
,[Temp_Extended_Standard_Q],[Temp_Existing_Quantity_A],[Incl] –14
,[BM_Component_Options],[BIN]) –15

SELECT @I_vBMDOCNBR,tbm.Component_ID,tbm.Parent_Component_ID,tbm.CMPTITNM,tiv.ITEMDESC –1
,tbm.UOFM AS SELNGUOM
,@I_vTRXLOCTN
,0 — Location code note index
,CASE WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 WHEN StockMethod=2 THEN tbm.QTY * @I_vTRXQTY ELSE 0 END — Stock quantity
,CASE WHEN StockMethod<>2 THEN tbm.QTY * @I_vTRXQTY ELSE 0 END — Assemble quantity
,CASE
WHEN StockMethod<>2 OR tbm.Parent_Component_ID=-1 THEN 0
WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 — This is probably the wrong logic, but wire should not be allocated for some reason
ELSE tbm.QTY * @I_vTRXQTY
END — 2
,CASE
WHEN tbm.Parent_Component_ID=-1 THEN 3
ELSE COALESCE(toa.BM_Stock_Method, 2)
END AS BM_Stock_Method
,1
,CASE
WHEN tbm.Parent_Component_ID = -1 THEN COALESCE(tgi.ACTINDX, tiv.IVIVINDX, 0)
ELSE COALESCE(tgi.ACTINDX, tiv.IVIVINDX, 0)
END
,”
,CASE
WHEN tbm.Parent_Component_ID = -1 THEN COALESCE(tgi.NOTEINDX, 0)
ELSE COALESCE(tgi.NOTEINDX, igl.NOTEINDX, 0)
END — 3
,CASE
WHEN tbm.IsAssembly = 0 THEN 0
ELSE COALESCE(tgo.ACTINDX, tiv.IVVARIDX, 0)
END
,” –4
,CASE
WHEN tbm.IsAssembly = 0 THEN 0
ELSE COALESCE(tgo.NOTEINDX, igl.NOTEINDX, 0)
END
— GP uses 0 for design quantity of parent
,CASE WHEN tbm.Parent_Component_ID = -1 THEN 0 ELSE tbm.QTY END –5
,tbm.Scrap_Percentage * 100,tiv.STNDCOST
— GP uses transaction quantity for standard quantity of parent
,CASE WHEN tbm.Parent_Component_ID = -1 THEN @I_vTRXQTY ELSE tbm.QTY / (1.0 – tbm.Scrap_Percentage * 0.01) END
,0
— GP is consistent about extended standard quantity
,CASE WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 ELSE @I_vTRXQTY * tbm.QTY / (1.0 – tbm.Scrap_Percentage * 0.01) END –6
,0,0,0 –7
,0,0
,CASE WHEN IsAssembly<>0 THEN 2 ELSE tiv.ITEMTYPE END
,tbm.DEPTH,1 –8
,0,0 –9
,0,0,0,” –10
,0,0,tiv.DECPLQTY,tiv.DECPLCUR,tiv.ITMTRKOP,0 –11
,0x00000000 –12
,”

,CASE
WHEN StockMethod<>2 OR tbm.Parent_Component_ID=-1 THEN 0
–WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 — This is probably the wrong logic, but wire should not be allocated for some reason
ELSE tbm.QTY * @I_vTRXQTY
END / (1.0 – tbm.Scrap_Percentage * 0.01) — 2

,0 –13
,0,0,0 –14
,CASE WHEN tbm.Parent_Component_ID=-1 THEN 0x91000000 ELSE 0x81000000 END
,” –15

FROM IV00101 tiv
JOIN COMPONENTS tbm
ON tiv.ITEMNMBR=tbm.CMPTITNM
LEFT JOIN BM00101 toa
ON tbm.CMPTITNM=toa.ITEMNMBR
LEFT JOIN GL00100 igl
ON tiv.IVIVINDX=igl.ACTINDX

LEFT JOIN GL00100 tgi
ON RTRIM(@I_vInventoryAccount)=RTRIM(tgi.ACTNUMBR_1) + RTRIM(tgi.ACTNUMBR_2) + RTRIM(tgi.ACTNUMBR_3) + RTRIM(tgi.ACTNUMBR_4) + RTRIM(tgi.ACTNUMBR_5) + RTRIM(tgi.ACTNUMBR_6)
LEFT JOIN GL00100 tgo
ON RTRIM(@I_vInventoryAccountOffset)=RTRIM(tgo.ACTNUMBR_1) + RTRIM(tgo.ACTNUMBR_2) + RTRIM(tgo.ACTNUMBR_3) + RTRIM(tgo.ACTNUMBR_4) + RTRIM(tgo.ACTNUMBR_5) + RTRIM(tgo.ACTNUMBR_6)
WHERE tbm.ITEMNMBR=@I_vITEMNMBR

— Insert the batch header if it is not present

IF NOT (EXISTS (SELECT * FROM SY00500 WHERE BACHNUMB=@I_vBACHNUMB))
BEGIN
INSERT INTO SY00500
([GLPOSTDT]
,[BCHSOURC]
,[BACHNUMB]
,[SERIES]
,[MKDTOPST]
,[NUMOFTRX]
,[RECPSTGS]
,[DELBACH]
,[MSCBDINC]
,[BACHFREQ]
,[RCLPSTDT]
,[NOFPSTGS]
,[BCHCOMNT]
,[BRKDNALL]
,[CHKSPRTD]
,[RVRSBACH]
,[USERID]
,[CHEKBKID]
,[BCHTOTAL]
,[BCHEMSG1]
,[BCHEMSG2]
,[BACHDATE]
,[BCHSTRG1]
,[BCHSTRG2]
,[POSTTOGL]
,[MODIFDT]
,[CREATDDT]
,[NOTEINDX]
,[CURNCYID]
,[BCHSTTUS]
,[CNTRLTRX]
,[CNTRLTOT]
,[PETRXCNT]
,[APPROVL]
,[APPRVLDT]
,[APRVLUSERID]
,[ORIGIN]
,[ERRSTATE]
,[GLBCHVAL]
,[Computer_Check_Doc_Date]
,[Sort_Checks_By]
,[SEPRMTNC]
,[REPRNTED]
,[CHKFRMTS]
,[TRXSORCE]
,[PmtMethod]
,[EFTFileFormat]
,[Workflow_Approval_Status]
,[Workflow_Priority]
,[TIME1])
VALUES
(CONVERT(char(10),GetDate(),101) /* <GLPOSTDT, datetime,> */
,’ASMENT’ /* <BCHSOURC, char(15),> */
,@I_vBACHNUMB /* <BACHNUMB, char(15),> */
,5 /* ??? <SERIES, smallint,> */
,0 /* <MKDTOPST, tinyint,> */
,0 /* <NUMOFTRX, int,> */
,0 /* <RECPSTGS, smallint,> */
,0 /* <DELBACH, tinyint,> */
,0 /* <MSCBDINC, smallint,> */
,1 /* <BACHFREQ, smallint,> */
,’1900-01-01 00:00′ /* <RCLPSTDT, datetime,> */
,0 /* <NOFPSTGS, smallint,> */
,’ ‘ /* <BCHCOMNT, char(61),> */
,0 /* <BRKDNALL, tinyint,> */
,0 /* <CHKSPRTD, tinyint,> */
,0 /* <RVRSBACH, tinyint,> */
,’ ‘ /* <USERID, char(15),> */
,’ ‘ /* <CHEKBKID, char(15),> */
,0 /* <BCHTOTAL, numeric(19,5),> */
,0x00000000 /* <BCHEMSG1, binary(4),> */
,0x00000000 /* <BCHEMSG2, binary(4),> */
,’1900-01-01 00:00′ /* <BACHDATE, datetime,> */
,’ ‘ /* <BCHSTRG1, char(21),> */
,’ ‘ /* <BCHSTRG2, char(21),> */
,1 /* <POSTTOGL, tinyint,> */
,CONVERT(char(10),GetDate(),101) /* <MODIFDT, datetime,> */
,CONVERT(char(10),GetDate(),101) /* <CREATDDT, datetime,> */
,0.00000 /* <NOTEINDX, numeric(19,5),> */
,’ ‘ /* <CURNCYID, char(15),> */
,0 /* <BCHSTTUS, smallint,> */
,0 /* <CNTRLTRX, int,> */
,0.00000 /* <CNTRLTOT, numeric(19,5),> */
,0 /* <PETRXCNT, smallint,> */
,0 /* <APPROVL, tinyint,> */
,’1900-01-01 00:00′ /* <APPRVLDT, datetime,> */
,’ ‘ /* <APRVLUSERID, char(15),> */
,3 /* <ORIGIN, smallint,> */
,0 /* <ERRSTATE, int,> */
,0x00000000 /* <GLBCHVAL, binary(4),> */
,’1900-01-01 00:00′ /* <Computer_Check_Doc_Date, datetime,> */
,0 /* <Sort_Checks_By, smallint,> */
,0 /* <SEPRMTNC, tinyint,> */
,0 /* <REPRNTED, smallint,> */
,0 /* <CHKFRMTS, smallint,> */
,’ ‘ /* <TRXSORCE, char(13),> */
,0 /* <PmtMethod, smallint,> */
,0 /* <EFTFileFormat, smallint,> */
,0 /* <Workflow_Approval_Status, smallint,> */
,0 /* <Workflow_Priority, smallint,> */
,’1900-01-01 00:00’ /* <TIME1, datetime,> */);
END;

— Update the quantities in the batch header

UPDATE SY00500
SET
NUMOFTRX = NUMOFTRX + 1,
BCHTOTAL = BCHTOTAL + @I_vTRXQTY
WHERE BACHNUMB = @I_vBACHNUMB;

execute dbo.taBMTransactionInsertPost
@I_vBACHNUMB,
@I_vBMDOCNBR,
@I_vITEMNMBR,
@I_vTRXQTY,
@I_vUOFM,
@I_vUNITCOST,
@I_vTRXLOCTN,
@I_vInventoryAccount,
@I_vInventoryAccountOffSet,
@I_vOverrideQty,
@I_vUSRDEFND1,
@I_vUSRDEFND2,
@I_vUSRDEFND3,
@I_vUSRDEFND4,
@O_iErrorState,
@oErrString;

/* End custom business logic */
return (@O_iErrorState);
END;
[/sql]

A worried person

Does this never end???

[sql]
CREATE procedure [dbo].[taBMTransactionInsertPre]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) output,
@I_vInventoryAccountOffSet varchar(75) output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int output,
@oErrString varchar(255) output
as
begin
set nocount on;
select @O_iErrorState = 0;
return (@O_iErrorState);
end;
[/sql]

[sql]
CREATE procedure [dbo].[taBMTransactionInsertPost]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) output,
@I_vInventoryAccountOffSet varchar(75) output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int output,
@oErrString varchar(255) output
as
begin
set nocount on;
select @O_iErrorState = 0;
return (@O_iErrorState);
end;
[/sql]

In a future post we’ll see how to use this from eOne SmartConnect.

PlayStation Vita

Free Publisher Licenses for PlayStation Vita

Sony has made publisher licenses free for the PlayStation Vita for a limited time. So independent developers could conceivably see their games or utilities running on the Vita with minimal investment. How cool is that!

I am not a PlayStation expert, but the publisher license seems to be analogous to an app store membership from other manufacturers. In the United States it is normally $99 per year. See this PlayStation blog entry for the announcement.

I haven’t investigated the economics overall. There might be a cost to get content ratings for games. There aren’t a lot of games in this channel at the moment (double digits).

But I thought to myself, “What the heck.” Developer registration took me only 10 minutes. After registering I was able to download the developer tools, which seem to be based on Miguel de Icaza’s Mono project, which in turn are open source implementations of the Microsoft VB and C# languages and libraries. The developer tools and documentation download was just under 1GB, and installation was very easy.

Evidently applying for a publisher license normally happens as part of the app submission process. I haven’t gotten that far yet.

I will plan to report here in the future on my progress as I see what it takes to compete, in an extremely small way, with the big boys in game development.

Conflict between Romans and barbarians

Image credit: fromoldbooks.org

Firefox logo

Firefox Marketplace App Submission Update

I didn’t know this was going to happen, but since my last post about the Firefox OS, ZTE and Telefónica España have released the ZTE Open cell phone in Spain, running Firefox OS.

ZTE Open home screen

The ZTE Open

The press release is here, and the phone specifications are here.

I submitted my first Firefox OS app on Saturday, June 22 (reminder, this is a personal effort, not necessarily representative of Visual Construction). Earlier today, July 3, I heard back. They caught one problem and made one suggestion.

Firefox app submission page

So the turnaround time has been about a week and a half so far, or 10 business days. This is comparable to many other app stores, worse than some, and better than a few.

I’ll make the changes in the next couple of days and resubmit the app. I’ll plan to post here again on the app’s progress.

Side note- I want to wish freedom lovers everywhere a happy Independence Day.

Firefox logo

Firefox Marketplace Packaged App Submission

The Mozilla organization has a vision of Firefox running everywhere, with HTML/JavaScript/CSS apps running on top of that.

I am always interested in new ideas. I thought I’d try writing and submitting a simple app for the Firefox Marketplace. This blog post describes that exercise for anyone else who might be curious but not curious enough to actually go through the entire process.

Writing the app was straightforward. Mozilla has excellent documentation. For example, see Firefox OS and Firefox Marketplace. The app is a standard HTML/JavaScript/CSS app with a Mozilla-specific manifest.

Mozilla supports two kinds of apps, hosted and packaged. A hosted app is pulled in from a URL. A packaged app resides on the device as a zip file.

To start, I went to Persona.org and created a login. I used those credentials to log in to the Firefox Marketplace.

Firefox app submission page

I made a small packaged app and filled out the page shown above. A packaged app can only be submitted in the categories Firefox OS and Firefox Mobile.

One thing that was new to me was the idea of a Content Security Policy. In fact, if I read the feedback correctly, it appears that jQuery 2.0.2 might contain some code that violates some of the CSP rules for the Firefox Marketplace. The verification page states that CSP violations do not matter if the app manifest does not request any permissions. My manifest did not request any permissions so I did not investigate further. Perhaps at a later date I’ll dig into this more and report back.

The marketplace does not appear to support paid apps at this time. I wanted to distribute my app free, so again I did not dig into this too deeply.

The marketplace policy is to review all submissions prior to making them public in the marketplace. I submitted mine Sunday, June 23. As of Wednesday, June 26, I have not gotten any feedback. I would say this amount of time is not unusual for the app stores with which I’ve dealt in the past. If and when I do hear back I’ll do another blog post.

So, for anyone who might be curious, that’s one person’s experience to date with the Firefox Marketplace.

And just to be clear, this is just me fooling around privately on my own time. Visual Construction Media doesn’t have an official policy on the Firefox Marketplace (nor does it need one at this point).