Exporting Documents as XML

This mechanism was added in version 15.12.02.192 and allows exporting the contents of Purchase Order to an xml file to facilitate the data interchange with vendors.

To implement this feature, you will need to write a SQL script following the example below and save it within the “RDL Subfolder” for the desired Vendor Id. The name of the SQL script must be “PoExportToXml.sql”. The script can be modified to include all the necessary details. The output xml file will be stored in the same folder as the PDF file generated for each PO.

SQL Script for Purchase Orders

--DECLARE @oPARAMETER1 as varchar (15)

--set @oPARAMETER1 ='PO-30035'

SELECT PURCHASE_ORDER.ID AS PurchaseOrderId, PURCHASE_ORDER.VENDOR_ID AS VendorId, VENDOR.NAME AS VendorName, PURCHASE_ORDER.CONTACT_FIRST_NAME AS ContactFirstName,

PURCHASE_ORDER.CONTACT_LAST_NAME AS ContactLastName, PURCHASE_ORDER.CONTACT_PHONE AS ContactPhone, PURCHASE_ORDER.ORDER_DATE AS OrderDate,

PURCHASE_ORDER.DESIRED_RECV_DATE AS DesRecvDate, PURCHASE_ORDER.BUYER AS Buyer, PURCHASE_ORDER.FREE_ON_BOARD AS FreeOnBoard, PURCHASE_ORDER.SHIP_VIA AS ShipVia,

PURCHASE_ORDER.STATUS AS POStatus, PURCHASE_ORDER.TERMS_DESCRIPTION AS TermsDescription, PURCHASE_ORDER.CURRENCY_ID AS Currency, PURCHASE_ORDER.WAREHOUSE_ID AS Whs,

VENDOR.ADDR_1 AS VendorAdd1, VENDOR.ADDR_2 AS VendorAdd2, VENDOR.ADDR_3 AS VendorAdd3, VENDOR.CITY AS VendorCity, VENDOR.STATE AS VendorState, VENDOR.ZIPCODE AS VendorPostalCode,

VENDOR.COUNTRY AS VendorCountry, SHIPTO_ADDRESS.ADDR_NO AS ShipToAddNo, SHIPTO_ADDRESS.NAME AS ShipToName, SHIPTO_ADDRESS.ADDR_1 AS ShipToAdd1,

SHIPTO_ADDRESS.ADDR_2 AS ShipToAdd2, SHIPTO_ADDRESS.ADDR_3 AS ShipToAdd3, SHIPTO_ADDRESS.CITY AS ShipToCity, SHIPTO_ADDRESS.STATE AS ShipToState,

SHIPTO_ADDRESS.ZIPCODE AS ShipToPostalCode, SHIPTO_ADDRESS.COUNTRY AS ShipToCountry, CONVERT(varchar(8000), CONVERT(binary(8000), PURC_ORDER_BINARY.BITS)) AS PurOrderSpecs,

PURC_ORDER_LINE.LINE_NO AS POLine, PURC_ORDER_LINE.PART_ID AS Part, PART.DESCRIPTION AS PartDesc, PURC_ORDER_LINE.VENDOR_PART_ID AS VendorPart,

PURC_ORDER_LINE.SERVICE_ID AS ServiceId, PURC_ORDER_LINE.ORDER_QTY AS OrderQty, PURC_ORDER_LINE.PURCHASE_UM AS PurUoM, PURC_ORDER_LINE.UNIT_PRICE AS UnitPrice,

PURC_ORDER_LINE.TRADE_DISC_PERCENT AS DiscPercent, PURC_ORDER_LINE.FIXED_CHARGE AS FixedCharge, PURC_ORDER_LINE.DESIRED_RECV_DATE AS LineDesRecvDate

FROM PURC_LINE_BINARY RIGHT OUTER JOIN

PURC_ORDER_BINARY RIGHT OUTER JOIN

PURC_ORDER_LINE LEFT OUTER JOIN

PART ON PURC_ORDER_LINE.PART_ID = PART.ID RIGHT OUTER JOIN

VENDOR INNER JOIN

PURCHASE_ORDER ON VENDOR.ID = PURCHASE_ORDER.VENDOR_ID LEFT OUTER JOIN

SHIPTO_ADDRESS ON PURCHASE_ORDER.SHIPTO_ADDR_NO = SHIPTO_ADDRESS.ADDR_NO ON PURC_ORDER_LINE.PURC_ORDER_ID = PURCHASE_ORDER.ID ON

PURC_ORDER_BINARY.PURC_ORDER_ID = PURCHASE_ORDER.ID ON PURC_LINE_BINARY.PURC_ORDER_ID = PURC_ORDER_LINE.PURC_ORDER_ID AND

PURC_LINE_BINARY.PURC_ORDER_LINE_NO = PURC_ORDER_LINE.LINE_NO

GROUP BY PURCHASE_ORDER.ID, PURCHASE_ORDER.VENDOR_ID, VENDOR.NAME, PURCHASE_ORDER.CONTACT_FIRST_NAME, PURCHASE_ORDER.CONTACT_LAST_NAME, PURCHASE_ORDER.CONTACT_PHONE,

PURCHASE_ORDER.ORDER_DATE, PURCHASE_ORDER.DESIRED_RECV_DATE, PURCHASE_ORDER.BUYER, PURCHASE_ORDER.FREE_ON_BOARD, PURCHASE_ORDER.SHIP_VIA,

PURCHASE_ORDER.STATUS, PURCHASE_ORDER.TERMS_DESCRIPTION, PURCHASE_ORDER.CURRENCY_ID, PURCHASE_ORDER.WAREHOUSE_ID, VENDOR.ADDR_1, VENDOR.ADDR_2, VENDOR.ADDR_3,

VENDOR.CITY, VENDOR.STATE, VENDOR.ZIPCODE, VENDOR.COUNTRY, SHIPTO_ADDRESS.ADDR_NO, SHIPTO_ADDRESS.NAME, SHIPTO_ADDRESS.ADDR_1, SHIPTO_ADDRESS.ADDR_2,

SHIPTO_ADDRESS.ADDR_3, SHIPTO_ADDRESS.CITY, SHIPTO_ADDRESS.STATE, SHIPTO_ADDRESS.ZIPCODE, SHIPTO_ADDRESS.COUNTRY, CONVERT(varchar(8000), CONVERT(binary(8000),

PURC_ORDER_BINARY.BITS)), PURC_ORDER_LINE.LINE_NO, PURC_ORDER_LINE.PART_ID, PURC_ORDER_LINE.VENDOR_PART_ID, PURC_ORDER_LINE.SERVICE_ID, PURC_ORDER_LINE.ORDER_QTY,

PURC_ORDER_LINE.PURCHASE_UM, PURC_ORDER_LINE.UNIT_PRICE, PURC_ORDER_LINE.TRADE_DISC_PERCENT, PURC_ORDER_LINE.FIXED_CHARGE, PURC_ORDER_LINE.DESIRED_RECV_DATE,

PART.DESCRIPTION

HAVING (PURCHASE_ORDER.ID LIKE @oPARAMETER1) FOR XML AUTO, TYPE, ELEMENTS

SQL Script for Customer Orders (added on version 17.09)

Note: the name of the SQL script for exporting Customer Orders must be “CoExportToXml.sql” and must be saved within the correspondent RDL Subfolder for the desired Customer Id.

--DECLARE @oPARAMETER1 as varchar (15)

--SET @oPARAMETER1 ='02013'

SELECT CUSTOMER_ORDER.ID AS CUST_ORDER_ID, CUSTOMER_ORDER.CUSTOMER_ID, CUSTOMER_ORDER.CUSTOMER_PO_REF, CUSTOMER.NAME AS CUSTOMER_NAME, CUSTOMER.ADDR_1 AS CUSTOMER_ADDR_1,

CUSTOMER.ADDR_2 AS CUSTOMER_ADDR_2, CUSTOMER.ADDR_3 AS CUSTOMER_ADDR_3, CUSTOMER.CITY AS CUSTOMER_CITY, CUSTOMER.STATE AS CUSTOMER_STATE, CUSTOMER.ZIPCODE AS CUSTOMER_ZIPCODE,

CUSTOMER.COUNTRY AS CUSTOMER_COUNTRY, CUSTOMER.CONTACT_FIRST_NAME, CUSTOMER.CONTACT_LAST_NAME, CUSTOMER.CONTACT_INITIAL, CUSTOMER.CONTACT_POSITION, CUSTOMER.CONTACT_PHONE,

CUSTOMER.CONTACT_FAX, CUSTOMER.BILL_TO_NAME, CUSTOMER.BILL_TO_ADDR_1, CUSTOMER.BILL_TO_ADDR_2, CUSTOMER.BILL_TO_ADDR_3, CUSTOMER.BILL_TO_CITY, CUSTOMER.BILL_TO_STATE,

CUSTOMER.BILL_TO_ZIPCODE, CUSTOMER.BILL_TO_COUNTRY, CUSTOMER_ORDER.FREE_ON_BOARD, CUSTOMER_ORDER.SALESREP_ID, CUSTOMER_ORDER.SHIP_VIA,

CASE WHEN CUSTOMER_ORDER.FREIGHT_TERMS = 'C' THEN 'Collect' WHEN CUSTOMER_ORDER.FREIGHT_TERMS = 'B' THEN 'Billed' WHEN CUSTOMER_ORDER.FREIGHT_TERMS = 'P' THEN 'Prepaid' ELSE '' END AS FREIGHT_TERMS,

CUSTOMER_ORDER.TERMS_DESCRIPTION, CUSTOMER_ORDER.ORDER_DATE, CUSTOMER_ORDER.DESIRED_SHIP_DATE, CUSTOMER_ORDER.CURRENCY_ID, CUST_ORDER_LINE.LINE_NO AS CO_LINE_NO,

CUST_ORDER_LINE.ORDER_QTY AS CO_LN_ORD_QTY, CUST_ORDER_LINE.TOTAL_SHIPPED_QTY, CUST_ORDER_LINE.PART_ID, ISNULL(CUST_ORDER_LINE.UNIT_PRICE, 0) AS UNIT_PRICE,

CUST_ORDER_LINE.TRADE_DISC_PERCENT, CUST_ORDER_LINE.TOTAL_AMT_ORDERED AS CO_LN_TOTAL_AMT_ORDERED

FROM CUST_LINE_BINARY RIGHT OUTER JOIN

CUST_ORDER_BINARY RIGHT OUTER JOIN

CUSTOMER_ORDER INNER JOIN

CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID LEFT OUTER JOIN

CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID ON CUST_ORDER_BINARY.CUST_ORDER_ID = CUSTOMER_ORDER.ID ON

CUST_LINE_BINARY.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND CUST_LINE_BINARY.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO

WHERE (CUSTOMER_ORDER.ID LIKE @oPARAMETER1)

ORDER BY CO_LINE_NO

FOR XML AUTO, TYPE, ELEMENTS