Inserting nulldate

[Former Member]

This one has me a bit perplexed.

Part 1:

I have a transaction that produces an xml doc with two date columns among other things (start and finish). When there is no date/time in these columns they display as 'TimeUnavailable' when executed from workbench. However occasionally the start and finish dates for a row will appear as the current date and time, even though the BLS has assigned nothing to these fields.

Part 2: 'Bailing on trying to fix part 1, moving onto brute force'

I have done much tracing attempting to find the source of these dates, to no avail. So I decided to break out the hammer and use a repeater at the end of the transaction, and set any start or finish date to nulldate where there was no process order (indicating no dates/times required). This almost worked, it got rid of all unwanted dates, except those in the first row. I even went so far as to remove any conditional logic and assign nulldate to all date fields in the document (as a test). Still the current date/time remain in any fields in the first row. If I assign some other date to the fields, they will reflect the date/time I have assigned.

In case you're wondering why I would want to display a row with no process order, it is part of a Lean Rhythm wheel display that shows a fixed list of products for manufacture on a production line. Sometimes products are skipped if inventory levels haven't triggered a production run, thus no process order.

Any Ideas would be appreciated.

Regards,

Rod Hoffman

[former_member591921]

With a conditional

datefromxmlformat(repeater.Results{/Rowsets/Rowset/Row/START_TIME}, "yyyy-MM-dd") != ""

[Former Member]

Hi,

You can do one thing.

In repeater u have to put conditional block to check the each process order is available or not. If there is no process order for a particular row, call a assignment block, convert the strat and finish date to strings and set those values to "Time Unavailable."

So u can display the empty Process Order row with Start and Finish date values as "Time Unavailable"

Regards,

Suresh

[Former Member]

Thanks for your responses.

Let me add a bit of detail:

I have two xml documents (among others) in my transaction xmlPO and xmlPO_wSeq. I write both to xml Output Properties for easy testing.

Both contain two datetime columns.

-If I execute my transaction, viewing the output of xmlPO, 4 rows are returned, all 8 time fields indicate 'TimeUnavailable'.

-If I execute viewing the output of xmlPO_wSeq, 4 rows are returned, the first row has current date and time values.

The only thing that happens between the creation of xmlPO and xmlPO_wSeq, is a repeater that iterates through xmlPO, linking values to the xmlRow object that is connected to the xmlPO_wSeq document, adding a sequence number and eliminating some columns. In particular the time values are linked like.

Expression = foreachPO.Output{/Row/START_TIME}  Target = AddRow_WithSeq.START_TIME 

I have determined that it will always do this on the first row generated by this particular xmlRow object.

Rod

[agentry_src]

Rod,

I think I have run into a similar problem in the past, but not with DateTime specifically. However the methodology for fixing it (assuming this works) is the same.

Your input to your repeater is assumed to be xmlPO and has standard xml document format.

So create your link after the repeater as:

Target: AddRow_WithSeq.START_TIME

linked to Expression:

xmlPO.Output{/Rowsets/Rowset/Row[#ForEachPO.CurrentItem#]/START_TIME}

Give that a try and see if the results are what you desire. I've had to do this a few times because of similar results. Mine was coming from an empty TagQuery defaulting to XXX instead of nullvalue. The XXX being the result set returned as a default for mapping purposes during construction of the my transaction.

Thanks,

Mike

Edited by: Michael Appleby on Jul 16, 2008 9:44 PM

[Former Member]

Thanks Mike,

Now the first row is still populated with the current date/time, and the other rows now show date/times in 1969.

Rod

[agentry_src]

Rod,

You have an interesting problem here. You could do a stringif for nulldate in the expression builder to get rid of the 1969 dates (essential a zero result), but I suspect that you will still see the first row's date as current date.

When you are looking at xmlPO before modifying xmlPO_seq, does it already contain the current date in the first row? Nevermind, you already said it did.

Mike

EDITS:

Maybe you could try a xml joiner? Or use xPath expressions to build a third xml document using inputs from the two you already had? (putting stringif statements in the expression to cover your zero default values). I am running out of ideas around this one.

What version of xMII are you working on?

Edited by: Michael Appleby on Jul 17, 2008 2:08 PM

[Former Member]

I am working in version 12.0.2.

The only 'peculiar' thing about the xmlPO document, is that it is the result of an outer join. So not all rows in the document contain all the attributes. However the first row is not always a row without all of the attributes. I'll paste the xmlPO xml here for reference.

<?xml version="1.0" encoding="UTF-8" ?>
<Rowsets DateCreated="2008-07-17T10:34:44" EndDate="2008-06-19T17:52:19" StartDate="2008-06-19T17:52:19"
	Version="10.0">
	<Rowset>
		<Columns>
			<Column Description="Resource Description" MaxRange="1" MinRange="0" Name="AR_DESCRIPTION"
				SQLDataType="1" SourceColumn="AR_DESCRIPTION" />
			<Column Description="Material" MaxRange="1" MinRange="0" Name="MATERIAL_NUMBER" SQLDataType="1"
				SourceColumn="MATERIAL_NUMBER" />
			<Column Description="Formatted NDC" MaxRange="1" MinRange="0" Name="NDC" SQLDataType="1"
				SourceColumn="NDC" />
			<Column Description="SAP Resource" MaxRange="1" MinRange="0" Name="ARA_ALIAS" SQLDataType="1"
				SourceColumn="ARA_ALIAS" />
			<Column Description="" MaxRange="1" MinRange="0" Name="RS_ID" SQLDataType="4" SourceColumn="RS_ID" />
			<Column Description="Resource ID" MaxRange="1" MinRange="0" Name="AR_ID" SQLDataType="4"
				SourceColumn="AR_ID" />
			<Column Description="" MaxRange="1" MinRange="0" Name="MATERIAL_ID" SQLDataType="4" SourceColumn="MATERIAL_ID" />
			<Column Description="Sequence #" MaxRange="1" MinRange="0" Name="RS_SEQUENCE" SQLDataType="4"
				SourceColumn="RS_SEQUENCE" />
			<Column Description="Recipe Group" MaxRange="1" MinRange="0" Name="RS_RECIPE_GRP" SQLDataType="1"
				SourceColumn="RS_RECIPE_GRP" />
			<Column Description="Filled Out Sequence Nr" MaxRange="1" MinRange="0" Name="RS_SEQ_NR"
				SQLDataType="4" SourceColumn="RS_SEQ_NR" />
			<Column Description="Filled Out Sequence Skip Description" MaxRange="1" MinRange="0" Name="RS_SEQ_SKIP"
				SQLDataType="1" SourceColumn="RS_SEQ_SKIP" />
			<Column Description="ORDERS" MaxRange="1" MinRange="0" Name="ORDERS" SQLDataType="12" SourceColumn="ORDERS" />
			<Column Description="BATCH" MaxRange="1" MinRange="0" Name="BATCH" SQLDataType="12" SourceColumn="BATCH" />
			<Column Description="QTY" MaxRange="1" MinRange="0" Name="QTY" SQLDataType="2" SourceColumn="QTY" />
			<Column Description="KIT_STATUS" MaxRange="1" MinRange="0" Name="KIT_STATUS" SQLDataType="12"
				SourceColumn="KIT_STATUS" />
			<Column Description="START_TIME" MaxRange="1" MinRange="0" Name="START_TIME" SQLDataType="93"
				SourceColumn="START_TIME" />
			<Column Description="FINISH_TIME" MaxRange="1" MinRange="0" Name="FINISH_TIME" SQLDataType="93"
				SourceColumn="FINISH_TIME" />
			<Column Description="KIT_COMMENT" MaxRange="1" MinRange="0" Name="KIT_COMMENT" SQLDataType="12"
				SourceColumn="KIT_COMMENT" />
			<Column Description="PO_NUMBER" MaxRange="1" MinRange="0" Name="PO_NUMBER" SQLDataType="12"
				SourceColumn="PO_NUMBER" />
			<Column Description="PO_ROUTING" MaxRange="1" MinRange="0" Name="PO_ROUTING" SQLDataType="12"
				SourceColumn="PO_ROUTING" />
			<Column Description="PO_ST_MES" MaxRange="1" MinRange="0" Name="PO_ST_MES" SQLDataType="12"
				SourceColumn="PO_ST_MES" />
			<Column Description="PO_ST_RUN" MaxRange="1" MinRange="0" Name="PO_ST_RUN" SQLDataType="12"
				SourceColumn="PO_ST_RUN" />
			<Column Description="PO_ST_RELEASED" MaxRange="1" MinRange="0" Name="PO_ST_RELEASED" SQLDataType="12"
				SourceColumn="PO_ST_RELEASED" />
			<Column Description="SEQ" MaxRange="1" MinRange="0" Name="SEQ" SQLDataType="2" SourceColumn="SEQ" />
			<Column Description="PO_ST_KITTING" MaxRange="1" MinRange="0" Name="PO_ST_KITTING" SQLDataType="12"
				SourceColumn="PO_ST_KITTING" />
		</Columns>
		<Row>
			<AR_DESCRIPTION>ZMT</AR_DESCRIPTION>
			<MATERIAL_NUMBER>00310021395</MATERIAL_NUMBER>
			<NDC>0213-95</NDC>
			<RS_ID>2201</RS_ID>
			<AR_ID>25</AR_ID>
			<MATERIAL_ID>187</MATERIAL_ID>
			<RS_SEQUENCE>1</RS_SEQUENCE>
			<RS_RECIPE_GRP>50000010</RS_RECIPE_GRP>
			<RS_SEQ_NR>1</RS_SEQ_NR>
			<RS_SEQ_SKIP>---</RS_SEQ_SKIP>
			<ORDERS>---</ORDERS>
			<BATCH>---</BATCH>
			<QTY>NA</QTY>
			<KIT_STATUS>---</KIT_STATUS>
			<START_TIME>TimeUnavailable</START_TIME>
			<FINISH_TIME>TimeUnavailable</FINISH_TIME>
			<KIT_COMMENT>---</KIT_COMMENT>
			<PO_NUMBER>---</PO_NUMBER>
			<PO_ROUTING>---</PO_ROUTING>
			<PO_ST_MES>---</PO_ST_MES>
			<PO_ST_RUN>---</PO_ST_RUN>
			<PO_ST_RELEASED>---</PO_ST_RELEASED>
			<SEQ>NA</SEQ>
			<PO_ST_KITTING>---</PO_ST_KITTING>
		</Row>
		<Row>
			<AR_ID>25</AR_ID>
			<RS_SEQUENCE>0</RS_SEQUENCE>
			<RS_SEQ_NR>2</RS_SEQ_NR>
			<RS_SEQ_SKIP>Skip/PM</RS_SEQ_SKIP>
			<ORDERS>---</ORDERS>
			<BATCH>---</BATCH>
			<QTY>NA</QTY>
			<KIT_STATUS>---</KIT_STATUS>
			<START_TIME>TimeUnavailable</START_TIME>
			<FINISH_TIME>TimeUnavailable</FINISH_TIME>
			<KIT_COMMENT>---</KIT_COMMENT>
			<PO_NUMBER>---</PO_NUMBER>
			<PO_ROUTING>---</PO_ROUTING>
			<PO_ST_MES>---</PO_ST_MES>
			<PO_ST_RUN>---</PO_ST_RUN>
			<PO_ST_RELEASED>---</PO_ST_RELEASED>
			<SEQ>NA</SEQ>
			<PO_ST_KITTING>---</PO_ST_KITTING>
		</Row>
		<Row>
			<AR_DESCRIPTION>ZMT</AR_DESCRIPTION>
			<MATERIAL_NUMBER>00310020920</MATERIAL_NUMBER>
			<NDC>0209-20</NDC>
			<RS_ID>2182</RS_ID>
			<AR_ID>25</AR_ID>
			<MATERIAL_ID>188</MATERIAL_ID>
			<RS_SEQUENCE>3</RS_SEQUENCE>
			<RS_RECIPE_GRP>50000005</RS_RECIPE_GRP>
			<RS_SEQ_NR>3</RS_SEQ_NR>
			<RS_SEQ_SKIP>---</RS_SEQ_SKIP>
			<ORDERS>---</ORDERS>
			<BATCH>---</BATCH>
			<QTY>NA</QTY>
			<KIT_STATUS>---</KIT_STATUS>
			<START_TIME>TimeUnavailable</START_TIME>
			<FINISH_TIME>TimeUnavailable</FINISH_TIME>
			<KIT_COMMENT>---</KIT_COMMENT>
			<PO_NUMBER>---</PO_NUMBER>
			<PO_ROUTING>---</PO_ROUTING>
			<PO_ST_MES>---</PO_ST_MES>
			<PO_ST_RUN>---</PO_ST_RUN>
			<PO_ST_RELEASED>---</PO_ST_RELEASED>
			<SEQ>NA</SEQ>
			<PO_ST_KITTING>---</PO_ST_KITTING>
		</Row>
		<Row>
			<AR_DESCRIPTION>ZMT</AR_DESCRIPTION>
			<MATERIAL_NUMBER>00310020995</MATERIAL_NUMBER>
			<NDC>0209-95</NDC>
			<RS_ID>2188</RS_ID>
			<AR_ID>25</AR_ID>
			<MATERIAL_ID>189</MATERIAL_ID>
			<RS_SEQUENCE>4</RS_SEQUENCE>
			<RS_RECIPE_GRP>50000006</RS_RECIPE_GRP>
			<RS_SEQ_NR>4</RS_SEQ_NR>
			<RS_SEQ_SKIP>---</RS_SEQ_SKIP>
			<ORDERS>---</ORDERS>
			<BATCH>---</BATCH>
			<QTY>NA</QTY>
			<KIT_STATUS>---</KIT_STATUS>
			<START_TIME>TimeUnavailable</START_TIME>
			<FINISH_TIME>TimeUnavailable</FINISH_TIME>
			<KIT_COMMENT>---</KIT_COMMENT>
			<PO_NUMBER>---</PO_NUMBER>
			<PO_ROUTING>---</PO_ROUTING>
			<PO_ST_MES>---</PO_ST_MES>
			<PO_ST_RUN>---</PO_ST_RUN>
			<PO_ST_RELEASED>---</PO_ST_RELEASED>
			<SEQ>NA</SEQ>
			<PO_ST_KITTING>---</PO_ST_KITTING>
		</Row>
	</Rowset>
</Rowsets>

Regards,

Rod

[Former Member]

Hi Rod,

I ran into a similar kind of situation.

The simplest solution I found was to write an XSLT to transform the xml output and replace 'TimeUnavailable' string of any datetime type of column with a blank.

Hope it helps.

[Former Member]

Hi Rod,

In your transaction, you write a condition to check if the start date and finish date is null.

If it is null, then assign the start date to curretnt date.

Hope this will help you.

Thanks,

Manisha