<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:variable name="OrderId" select="/PurchaseOrder/OrderHeader/OrderReference/BuyerRefNum/Reference/RefNum"/>

<xsl:variable name="CustomerId" select="/PurchaseOrder/OrderHeader/OrderReference/ListOfReferenceCoded/ReferenceCoded/RefNum"/>

<xsl:template match="*|/"><xsl:apply-templates/></xsl:template>

<xsl:template match="text()|@*"><xsl:value-of select="."/></xsl:template>

<xsl:template match="PurchaseOrder">
insert into fs2_pscr_order (CUSTOMERID, 
									 CUSTOMERORDERREF, 
									 CONTACTID, 
									 CONTACTFULLNAME, 
									 SURNAME, 
									 FIRSTNAME, 
									 CONTACT_TITLE, 
									 CONTACT_INITIAL, 
									 CONTACT_TEL, 
									 CONTACT_EXT_NUMBER, 
									 CONTACT_FAX, 
									 CONTACT_SUB_BUILDING, 
									 CONTACT_BUILDINGNAME, 
									 CONTACT_ROOM, 
									 CONTACT_FLOOR, 
									 CONTACT_LOCATION, 
									 CONTACT_THOROUGHFARENAME, 
									 CONTACT_THOROUGHFARENUMBER, 
									 CONTACT_LOCALITY, 
									 CONTACT_POSTTOWN, 
									 CONTACT_POSTCODE, 
									 CONTACT_COUNTY, 
									 CONTACT_COUNTRY_CODE, 
									 FASTSTREAMREF, 
									 CONFIRMATION_REF, 
									 PROCESS_STATE, 
									 PROCESSED_BY, 
									 CONTACT_COMP_POS, 
									 CONTACT_EMAIL)
									 Values
									 (&apos;<xsl:value-of select="$CustomerId"/>&apos;,
									  &apos;<xsl:value-of select="$OrderId"/>&apos;,
									  null, /*Contactid*/
									  null, /* Contactfullname */
									  &apos;<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/ContactName"/>&apos;,
									  &apos;<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/sro:FirstName"/>&apos;,
									  &apos;<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/sro:Title"/>&apos;,
									  &apos;<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/sro:Initials"/>&apos;,
									  &apos;<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/Telephone"/>&apos;,
									  null, /*CONTACT_EXT_NUMBER*/
									  <xsl:variable name="temp" >!<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/Fax"/></xsl:variable>
									  <xsl:if test="$temp = '!'">null</xsl:if>
									  <xsl:if test="$temp != '!'">&apos;<xsl:value-of select="substring-after($temp,'!')"/>&apos;</xsl:if>, /*CONTACT_FAX */
									  null, /*CONTACT_SUB_BUILDING, */
									  null, /*CONTACT_BUILDINGNAME, */
									  null, /*CONTACT_ROOM, */
									  null, /*CONTACT_FLOOR, */
									  null, /*CONTACT_LOCATION, */
									  null, /*CONTACT_THOROUGHFARENAME, */
									  null, /*CONTACT_THOROUGHFARENUMBER, */
									  null, /*CONTACT_LOCALITY, */
									  null, /*CONTACT_POSTTOWN, */
									  null, /*CONTACT_POSTCODE, */
									  null, /*CONTACT_COUNTY, */
									  &apos;44&apos;, /*CONTACT_COUNTRY_CODE,*/
									  null, /*FASTSTREAMREF, */
									  null, /*CONFIRMATION_REF, */
									  0, /*PROCESS_STATE, */
									  null, /*PROCESSED_BY, */
									  null, /*CONTACT_COMP_POS, */
									  <xsl:variable name="temp" >!<xsl:value-of select="/PurchaseOrder/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/*/Email"/></xsl:variable>
									  <xsl:if test="$temp = '!'">null</xsl:if>
									  <xsl:if test="$temp != '!'">&apos;<xsl:value-of select="substring-after($temp,'!')"/>&apos;</xsl:if>
										); /*CONTACT_EMAIL*/
									  
<xsl:apply-templates select="/PurchaseOrder/ListOfOrderDetail"/>										
									  
									  
									  
									  
									  
									 
</xsl:template>

<xsl:template match="ListOfOrderDetail"><xsl:for-each select="OrderDetail">
	<xsl:variable name="OrderLineId" select=".//LineItemNum"/>
	insert into FS2_PSCR_ORDER_LINE (CUSTOMERORDERLINEREF, 
											   CUSTOMERID, 
												CUSTOMERORDERREF, 
												PRODUCTGROUP, 
												ORDER_LINE_FUNCTION, 
												CUST_PROJECT_CODE, 
												CONFIRMATION_REF, 
												PROCESS_STATE, 
												QUANTITY, 
												ORDERDATE)
												Values
												(&apos;<xsl:value-of select="$OrderLineId"/>&apos;,/*CUSTOMERORDERLINEREF, */
											    &apos;<xsl:value-of select="$CustomerId"/>&apos;,/*CUSTOMERID, */
												 &apos;<xsl:value-of select="OrderId"/>&apos;,/*CUSTOMERORDERREF, */
												 &apos;<xsl:value-of select=".//SupplierPartNum/PartNum/PartID"/>&apos;,/*PRODUCTGROUP,  */
												 <xsl:variable name="itemtype" select=".//@ItemType"/>
												 <xsl:if test="$itemtype = 'provision'"><xsl:text>PD</xsl:text></xsl:if>
												 /*ORDER_LINE_FUNCTION,  */
												/*CUST_PROJECT_CODE,  */
												/*CONFIRMATION_REF,  */
												/*PROCESS_STATE,  */
												/*QUANTITY,  */
												/*ORDERDATE */
												);
	
</xsl:for-each></xsl:template>

</xsl:stylesheet>