Microsoft SQL code example to pull Syteline invoice information for edi communication.
USE [testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_EDI_pull_invoice] Script Date: 06/24/2013 23:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_EDI_pull_invoice]
@INV_NUM nvarchar(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT T1.inv_date AS DTINVOICED
,LTRIM(T1.inv_num) AS INVOICENUM
,T2.co_line AS LINENUM
,T3.order_date AS DTORDER
,T1.ship_date AS DTSHIPPED
,T1.cust_po AS PONUM
,T3.charfld1 AS PO_TYPE
,T1.ship_code AS CARRIER
,T4.description AS CARRIER_DESCRIPTION
,T5.name AS NAME
,T5.addr##1 AS ADDRESS1
,T5.addr##2 AS ADDRESS2
,T5.city AS CITY
,T5.state AS STATE
,T5.zip AS ZIP
,T10.iso_country_code AS COUNTRY
,T6.UDFDT1 AS DTREQUESTED
,T6.UDFDT2 AS DTPROMISED
,'NA' AS DISCTYPE
,T7.disc_days AS DISCDAYS
,CAST((T7.disc_pct/100) AS DECIMAL(20, 2)) AS DISCRATE
,T1.terms_code AS DUETYPE
,T7.due_days AS DUEDAYS
,CAST(CAST(MYSCHEMA._chad_EDI_get_invoice_tax(T1.inv_num) AS DECIMAL(20, 2)) AS FLOAT) AS TAXAMT1
,'NA' AS TAXAMT2
,'NA' AS TAXAMT3
,'NA' AS TAXAMT4
,CAST(T2.qty_invoiced AS INT) AS INVOICEQTY
,T8.u_m AS UNITS
,CAST(CAST(T2.price AS DECIMAL(20, 2)) AS FLOAT) AS UNITPRICE
,T9.cust_item AS CUSTITEMNUM
,T2.item AS ITEMNUM
,T1.co_num AS ORDERNUM
,'NA' AS RELEASE
FROM inv_hdr AS T1
INNER JOIN inv_item AS T2 ON
T1.inv_num = T2.inv_num
INNER JOIN co AS T3 ON
T1.co_num = T3.co_num
LEFT OUTER JOIN shipcode AS T4 ON
T1.ship_code = T4.ship_code
INNER JOIN custaddr AS T5 ON
T1.cust_num = T5.cust_num
AND T1.cust_seq = T5.cust_seq
LEFT OUTER JOIN UserDefinedFields AS T6 ON
T3.RowPointer = T6.RowId
AND T6.TableName = 'co'
LEFT OUTER JOIN terms AS T7 ON
T1.terms_code = T7.terms_code
LEFT OUTER JOIN item AS T8 ON
T2.item = T8.item
INNER JOIN coitem AS T9 ON
T2.co_num = T9.co_num
AND T2.co_line = T9.co_line
LEFT OUTER JOIN country AS T10 ON
T5.country = T10.country
WHERE LTRIM(T1.inv_num) = @INV_NUM
ORDER BY T2.co_line ASC
END