SQL Server
SQL Server
Xero Get Individual Employee Details (Payroll API Australia)
Demonstrates how to get the detailed data for a particular employee.Note: This example requires Chilkat v9.5.0.64 or greater.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Note: Requires Chilkat v9.5.0.64 or greater.
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Before sending REST API calls, the REST object needs to be
-- initialized for OAuth1.
-- See Xero 2-Legged OAuth1 Setup for sample code.
-- Assuming the REST object's OAuth1 authenticator is setup, and the initial
-- connection was made, we may now send REST HTTP requests..
DECLARE @sbPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/payroll.xro/1.0/Employees/'
-- To get the detailed information for an individual employee,
-- add the EmployeeID to the path.
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '056a9821-55b8-4c91-b5ce-a6ce2ab508ab'
-- Get the data for the indicated employee.
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'GET', @sTmp0, @sbXml
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- A 200 response is expected for actual success.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- Iterate over the employees and get some information..
DECLARE @bAutoTrim int
SELECT @bAutoTrim = 0
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadSb', @success OUT, @sbXml, @bAutoTrim
EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_cache/xero_employee_lebron.xml'
-- Use GetChild2 to navigate to the BankAccounts node.
EXEC sp_OAMethod @xml, 'FindChild2', @success OUT, 'Employees|Employee|BankAccounts'
IF @success = 0
BEGIN
PRINT 'BankAccounts node not found.'
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @xml
RETURN
END
-- Demonstrate how to iterate over each of the employee's Bank Accounts to get information
-- from each. (This same technique can be used for other parts of the XML.)
DECLARE @recordCount int
EXEC sp_OAGetProperty @xml, 'NumChildren', @recordCount OUT
PRINT 'Number of Bank Accounts = ' + @recordCount
DECLARE @i int
SELECT @i = 0
WHILE @i < @recordCount
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'BankAccount|AccountName'
PRINT 'AccountName: ' + @sTmp0
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'BankAccount|StatementText'
PRINT 'StatementText: ' + @sTmp0
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'BankAccount|AccountNumber'
PRINT 'AccountNumber: ' + @sTmp0
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'BankAccount|Amount'
PRINT 'Amount: ' + @sTmp0
PRINT '----'
SELECT @i = @i + 1
END
-- Return back to the root.
EXEC sp_OAMethod @xml, 'GetRoot2', NULL
-- The output looks like this:
-- Number of Bank Accounts = 2
-- AccountName: James Lebron Savings
-- StatementText: Salary
-- AccountNumber: 345678
-- Amount: 200.00
-- ----
-- AccountName: James Lebron Savings
-- StatementText: Salary
-- AccountNumber: 345678
-- Amount: 200.00
-- ----
-- The employee XML looks like this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <Response xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
-- <Id>6131dfc6-d11c-4fe0-aa1c-f7356c54754c</Id>
-- <Status>OK</Status>
-- <ProviderName>ChilkatAU</ProviderName>
-- <DateTimeUTC>2016-11-11T20:53:38.752419Z</DateTimeUTC>
-- <Employees>
-- <Employee>
-- <EmployeeID>056a9821-55b8-4c91-b5ce-a6ce2ab508ab</EmployeeID>
-- <FirstName>James</FirstName>
-- <LastName>Lebron</LastName>
-- <Status>ACTIVE</Status>
-- <Email>JL@madeup.email.com</Email>
-- <DateOfBirth>1982-03-15T00:00:00</DateOfBirth>
-- <Gender>M</Gender>
-- <HomeAddress>
-- <AddressLine1>123 Main St</AddressLine1>
-- <City>St. Kilda</City>
-- <Region>VIC</Region>
-- <PostalCode>3182</PostalCode>
-- <Country>AUSTRALIA</Country>
-- </HomeAddress>
-- <Phone>0400-000-123</Phone>
-- <Mobile> 408-230-9732</Mobile>
-- <StartDate>2015-09-01T00:00:00</StartDate>
-- <OrdinaryEarningsRateID>2c4fbb29-aa68-4a8d-bc05-3f6366f75227</OrdinaryEarningsRateID>
-- <PayrollCalendarID>4c08f63d-53af-4521-a8be-1d77fc0faf45</PayrollCalendarID>
-- <UpdatedDateUTC>2016-11-11T20:29:03</UpdatedDateUTC>
-- <IsAuthorisedToApproveLeave>false</IsAuthorisedToApproveLeave>
-- <TaxDeclaration>
-- <TaxFileNumber>999999811</TaxFileNumber>
-- <EmploymentBasis>FULLTIME</EmploymentBasis>
-- <AustralianResidentForTaxPurposes>true</AustralianResidentForTaxPurposes>
-- <TaxFreeThresholdClaimed>true</TaxFreeThresholdClaimed>
-- <HasHELPDebt>true</HasHELPDebt>
-- <HasSFSSDebt>false</HasSFSSDebt>
-- <EligibleToReceiveLeaveLoading>false</EligibleToReceiveLeaveLoading>
-- <UpdatedDateUTC>2016-11-11T20:29:03</UpdatedDateUTC>
-- <HasStudentStartupLoan>false</HasStudentStartupLoan>
-- </TaxDeclaration>
-- <BankAccounts>
-- <BankAccount>
-- <StatementText>Salary</StatementText>
-- <AccountName>James Lebron Savings</AccountName>
-- <BSB>122344</BSB>
-- <AccountNumber>345678</AccountNumber>
-- <Remainder>false</Remainder>
-- <Amount>200.00</Amount>
-- </BankAccount>
-- <BankAccount>
-- <StatementText>Salary</StatementText>
-- <AccountName>James Lebron</AccountName>
-- <BSB>123443</BSB>
-- <AccountNumber>2345678</AccountNumber>
-- <Remainder>true</Remainder>
-- </BankAccount>
-- </BankAccounts>
-- <OpeningBalances>
-- <OpeningBalanceDate>2016-02-01T00:00:00</OpeningBalanceDate>
-- <EarningsLines>
-- <EarningsLine>
-- <EarningsRateID>2c4fbb29-aa68-4a8d-bc05-3f6366f75227</EarningsRateID>
-- <Amount>13333.33</Amount>
-- </EarningsLine>
-- </EarningsLines>
-- <DeductionLines>
-- <DeductionLine>
-- <DeductionTypeID>2fd4c1e2-5bbb-43e4-a396-4c146f2f9b77</DeductionTypeID>
-- <Amount>40.00</Amount>
-- </DeductionLine>
-- </DeductionLines>
-- <Tax>4000.00</Tax>
-- <SuperLines>
-- <SuperLine>
-- <SuperMembershipID>9e9cc70d-1d6c-4ce5-a402-2c4fe80c02b0</SuperMembershipID>
-- <ContributionType>SGC</ContributionType>
-- <Amount>3999.99</Amount>
-- </SuperLine>
-- </SuperLines>
-- <ReimbursementLines />
-- <LeaveLines>
-- <LeaveLine>
-- <LeaveTypeID>8aa43544-f7f2-4b7a-9ddf-0a9c831c4679</LeaveTypeID>
-- <NumberOfUnits>100.0000</NumberOfUnits>
-- </LeaveLine>
-- <LeaveLine>
-- <LeaveTypeID>8874e0c0-d1d8-4635-a359-c09f6cf1c747</LeaveTypeID>
-- <NumberOfUnits>40.0000</NumberOfUnits>
-- </LeaveLine>
-- </LeaveLines>
-- </OpeningBalances>
-- <PayTemplate>
-- <EarningsLines>
-- <EarningsLine>
-- <EarningsRateID>2c4fbb29-aa68-4a8d-bc05-3f6366f75227</EarningsRateID>
-- <CalculationType>ANNUALSALARY</CalculationType>
-- <AnnualSalary>40000.00</AnnualSalary>
-- <NumberOfUnitsPerWeek>38.0000</NumberOfUnitsPerWeek>
-- </EarningsLine>
-- </EarningsLines>
-- <DeductionLines>
-- <DeductionLine>
-- <DeductionTypeID>2fd4c1e2-5bbb-43e4-a396-4c146f2f9b77</DeductionTypeID>
-- <CalculationType>FIXEDAMOUNT</CalculationType>
-- <Amount>10.00</Amount>
-- </DeductionLine>
-- </DeductionLines>
-- <SuperLines>
-- <SuperLine>
-- <SuperMembershipID>9e9cc70d-1d6c-4ce5-a402-2c4fe80c02b0</SuperMembershipID>
-- <ContributionType>SGC</ContributionType>
-- <CalculationType>PERCENTAGEOFEARNINGS</CalculationType>
-- <MinimumMonthlyEarnings>450.00</MinimumMonthlyEarnings>
-- <ExpenseAccountCode>478</ExpenseAccountCode>
-- <LiabilityAccountCode>826</LiabilityAccountCode>
-- <Percentage>9.0000</Percentage>
-- </SuperLine>
-- <SuperLine>
-- <SuperMembershipID>9e9cc70d-1d6c-4ce5-a402-2c4fe80c02b0</SuperMembershipID>
-- <ContributionType>SALARYSACRIFICE</ContributionType>
-- <CalculationType>FIXEDAMOUNT</CalculationType>
-- <ExpenseAccountCode>478</ExpenseAccountCode>
-- <LiabilityAccountCode>826</LiabilityAccountCode>
-- <Amount>50.0000</Amount>
-- </SuperLine>
-- </SuperLines>
-- <ReimbursementLines />
-- <LeaveLines>
-- <LeaveLine>
-- <LeaveTypeID>8aa43544-f7f2-4b7a-9ddf-0a9c831c4679</LeaveTypeID>
-- <CalculationType>FIXEDAMOUNTEACHPERIOD</CalculationType>
-- <AnnualNumberOfUnits>152.0000</AnnualNumberOfUnits>
-- </LeaveLine>
-- <LeaveLine>
-- <LeaveTypeID>8874e0c0-d1d8-4635-a359-c09f6cf1c747</LeaveTypeID>
-- <CalculationType>FIXEDAMOUNTEACHPERIOD</CalculationType>
-- <AnnualNumberOfUnits>76.0000</AnnualNumberOfUnits>
-- </LeaveLine>
-- </LeaveLines>
-- </PayTemplate>
-- <SuperMemberships>
-- <SuperMembership>
-- <SuperMembershipID>9e9cc70d-1d6c-4ce5-a402-2c4fe80c02b0</SuperMembershipID>
-- <SuperFundID>2bab4eeb-6f3c-4c66-a1f0-52fcb81f412a</SuperFundID>
-- <EmployeeNumber>1234</EmployeeNumber>
-- </SuperMembership>
-- </SuperMemberships>
-- <LeaveBalances>
-- <LeaveBalance>
-- <LeaveName>Annual Leave</LeaveName>
-- <LeaveTypeID>8aa43544-f7f2-4b7a-9ddf-0a9c831c4679</LeaveTypeID>
-- <NumberOfUnits>88.8602</NumberOfUnits>
-- <TypeOfUnits>Hours</TypeOfUnits>
-- </LeaveBalance>
-- <LeaveBalance>
-- <LeaveName>Personal/Carer's Leave</LeaveName>
-- <LeaveTypeID>8874e0c0-d1d8-4635-a359-c09f6cf1c747</LeaveTypeID>
-- <NumberOfUnits>45.8302</NumberOfUnits>
-- <TypeOfUnits>Hours</TypeOfUnits>
-- </LeaveBalance>
-- </LeaveBalances>
-- </Employee>
-- </Employees>
-- </Response>
--
--
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @xml
END
GO