Microsoft SQL code example to pull Syteline aged payables data.
USE [Testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_Excel_Addin_Executive_AP_AGED_Export] Script Date: 06/17/2013 21:42:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_Excel_Addin_Executive_AP_AGED_Export]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('[MYSCHEMA].chad_ap_aged_export') IS NOT NULL
BEGIN
DROP TABLE [MYSCHEMA].chad_ap_aged_export
END
DECLARE
@site SiteType
, @site_name NameType
, @group_code NVARCHAR(6)
, @group_value NVARCHAR(120)
, @vend_num VendNumType
, @vendaddr_name NameType
, @phone PhoneType
, @contact ContactType
, @terms_code TermsCodeType
, @terms_description DescriptionType
, @vend_type VendTypeType
, @curr_code CurrCodeType
, @tot_vend_inv AmountType
, @tot_vend_paid AmountType
, @tot_vend_bal AmountType
, @tot_vend_disc_tak AmountType
, @tot_vend_disc AmountType
, @tot_vend_age_bal1 AmountType
, @tot_vend_age_bal2 AmountType
, @tot_vend_age_bal3 AmountType
, @tot_vend_age_bal4 AmountType
, @tot_vend_age_bal5 AmountType
, @consolidate_vendors TINYINT
, @CurrencyFormat InputMaskType
, @CurrencyPlaces DecimalPlacesType
, @TotalCurrencyFormat InputMaskType
, @TotalCurrencyPlaces DecimalPlacesType
, @voucher nvarchar(7)
, @type NCHAR(2)
, @inv_num VendInvNumType
, @inv_date DateType
, @due_date DateType
, @disc_date DateType
, @amt_vchr_disc AmountType
, @amt_vchr_bal AmountType
, @exch_rate ExchRateType
, @bucket AgeDescType
, @BucketNumber AgeDaysType
, @VoucherTotal AmountType
, @AmtPaid AmountType
, @DiscTaken AmountType
, @DiscAvail AmountType
, @TrxTotal AmountType
, @NetDue AmountType
, @dtl_age_bal1 AmountType
, @dtl_age_bal2 AmountType
, @dtl_age_bal3 AmountType
, @dtl_age_bal4 AmountType
, @dtl_age_bal5 AmountType
SELECT
@site AS site
, @site_name AS site_name
, @group_code AS group_code
, @group_value AS group_value
, @vend_num AS vend_num
, @vendaddr_name AS vendaddr_name
, @phone AS phone
, @contact AS contact
, @terms_code AS terms_code
, @terms_description AS terms_description
, @vend_type AS vend_type
, @curr_code AS curr_code
, @tot_vend_inv AS tot_vend_inv
, @tot_vend_paid AS tot_vend_paid
, @tot_vend_bal AS tot_vend_bal
, @tot_vend_disc_tak AS tot_vend_disc_tak
, @tot_vend_disc AS tot_vend_disc
, @tot_vend_age_bal1 AS tot_vend_age_bal1
, @tot_vend_age_bal2 AS tot_vend_age_bal2
, @tot_vend_age_bal3 AS tot_vend_age_bal3
, @tot_vend_age_bal4 AS tot_vend_age_bal4
, @tot_vend_age_bal5 AS tot_vend_age_bal5
, @consolidate_vendors AS consolidate_vendors
, @CurrencyFormat AS CurrencyFormat
, @CurrencyPlaces AS CurrencyPlaces
, @TotalCurrencyFormat AS TotalCurrencyFormat
, @TotalCurrencyPlaces AS TotalCurrencyPlaces
, @voucher AS voucher
, @type AS type
, @inv_num AS inv_num
, @inv_date AS inv_date
, @due_date AS due_date
, @disc_date AS disc_date
, @amt_vchr_disc AS amt_vchr_disc
, @amt_vchr_bal AS amt_vchr_bal
, @exch_rate AS exch_rate
, @bucket AS bucket
, @BucketNumber AS BucketNumber
, @VoucherTotal AS VoucherTotal
, @AmtPaid AS AmtPaid
, @DiscTaken AS DiscTaken
, @DiscAvail AS DiscAvail
, @TrxTotal AS TrxTotal
, @NetDue AS NetDue
, @dtl_age_bal1 AS dtl_age_bal1
, @dtl_age_bal2 AS dtl_age_bal2
, @dtl_age_bal3 AS dtl_age_bal3
, @dtl_age_bal4 AS dtl_age_bal4
, @dtl_age_bal5 AS dtl_age_bal5
INTO #AP_RESULTS
WHERE 1=2
INSERT INTO #AP_RESULTS
EXEC [dbo].[Rpt_AccountsPayableAgingSp]
@SiteGroup = 'Testsite',
@PrintPostTrans = 0,
@PrintOpenPaymts = 0,
@SupZeroBalVch = 1,
@TransDomCurr = 1,
@UseHistRate = 1,
@AgeBucket = 0,
@AgingBasis = 'I',
@PayHold = 'B',
@ShowActive = 1,
@SortByCurrCode = 0,
@SortByNum = 1,
@AgeDays1 = 30,
@AgeDays2 = 60,
@AgeDays3 = 90,
@AgeDays4 = 120,
@AgeDays5 = 9999,
@AgeDesc1 = 'Current',
@AgeDesc2 = '30 - 60',
@AgeDesc3 = '60 - 90',
@AgeDesc4 = '90 - 120',
@AgeDesc5 = '> 120',
@DisplayHeader = 0,
@ConsolidateVendors = 0
INSERT INTO #AP_RESULTS
EXEC [dbo].[Rpt_AccountsPayableAgingSp]
@SiteGroup = 'Testsite2',
@PrintPostTrans = 0,
@PrintOpenPaymts = 0,
@SupZeroBalVch = 1,
@TransDomCurr = 1,
@UseHistRate = 1,
@AgeBucket = 0,
@AgingBasis = 'I',
@PayHold = 'B',
@ShowActive = 1,
@SortByCurrCode = 0,
@SortByNum = 1,
@AgeDays1 = 30,
@AgeDays2 = 60,
@AgeDays3 = 90,
@AgeDays4 = 120,
@AgeDays5 = 9999,
@AgeDesc1 = 'Current',
@AgeDesc2 = '30 - 60',
@AgeDesc3 = '60 - 90',
@AgeDesc4 = '90 - 120',
@AgeDesc5 = '> 120',
@DisplayHeader = 0,
@ConsolidateVendors = 0
INSERT INTO #AP_RESULTS
EXEC [dbo].[Rpt_AccountsPayableAgingSp]
@SiteGroup = 'TESTSITE3',
@PrintPostTrans = 0,
@PrintOpenPaymts = 0,
@SupZeroBalVch = 1,
@TransDomCurr = 1,
@UseHistRate = 1,
@AgeBucket = 0,
@AgingBasis = 'I',
@PayHold = 'B',
@ShowActive = 1,
@SortByCurrCode = 0,
@SortByNum = 1,
@AgeDays1 = 30,
@AgeDays2 = 60,
@AgeDays3 = 90,
@AgeDays4 = 120,
@AgeDays5 = 9999,
@AgeDesc1 = 'Current',
@AgeDesc2 = '30 - 60',
@AgeDesc3 = '60 - 90',
@AgeDesc4 = '90 - 120',
@AgeDesc5 = '> 120',
@DisplayHeader = 0,
@ConsolidateVendors = 0
SELECT
site
,vend_num
,vendaddr_name
,terms_code
,voucher
,inv_num
,amt_vchr_bal
,VoucherTotal
,AmtPaid
,dtl_age_bal1
,dtl_age_bal2
,dtl_age_bal3
,dtl_age_bal4
,dtl_age_bal5
INTO [MYSCHEMA].chad_ap_aged_export
FROM #AP_RESULTS
END