...
- This is NOT recommended to pass in same value of (kfsChartCode, kfsAccountCode) but with different effectiveDate. An unexpected result maybe returned.
- The order of kfsChartCode, kfsAccountCode, effectiveDate CANNOT be swapped.
- effectiveDate has to be valid. Null will be returned otherwise.
Input Specifications
| Index | Parameter | Description | Required? | Data Type (max length) | Example |
|---|---|---|---|---|---|
| 0 | kfsChartCode | KFS Chart Code | Yes | String(2) | 'IR' |
| 1 | kfsAcctNumber | KFS Account Number | Yes | String(7) | '1234567' |
| 2 | effectiveDate | The date the KFS account needs to be valid | No (default is today's date) | String(8) YYYYMMDD | '20120701' (begin fiscal year 2012/2013) |
| kfsChartCode | KFS Chart Code | Yes | String(2) | 'IR' | |
| kfsAcctNumber | KFS Account Number | Yes | String(7) | '1234567' |
DB Query
| Code Block |
|---|
Old:
select distinct
case when not exists (select * FROM dwhs_ods..ca_account_t a where a.account_nbr= '<kfsAccountCode>' AND a.fin_coa_cd= '<kfsChartCode>'
AND a.ACCT_CLOSED_IND <> 'Y' AND ( a.ACCT_EXPIRATION_DT >= getdate() or a.ACCT_EXPIRATION_DT IS NULL)
AND (a.ACCT_EFFECT_DT <= <effectiveDate> ) )
then 'false'
else 'true'
end
New:
SELECT a.FIN_COA_CD, a.ACCOUNT_NBR, 'true' AS VALID FROM dwhs_ods..ca_account_t a WHERE
a.ACCT_CLOSED_IND <> 'Y'
AND ( a.ACCT_EXPIRATION_DT >= getdate() or a.ACCT_EXPIRATION_DT IS NULL)
AND (
(a.account_nbr= '1000766'
AND a.fin_coa_cd= 'IR'
AND a.ACCT_EFFECT_DT <= getdate()) OR
(a.account_nbr= '1000768'
AND a.fin_coa_cd= 'IR'
AND a.ACCT_EFFECT_DT <= convert(datetime,'20100701',112)) OR
(a.account_nbr= '1000767'
AND a.fin_coa_cd= 'IR'
AND a.ACCT_EFFECT_DT <= getdate())
); |
...
- This is NOT recommended to pass in same value of (kfsChartCode, kfsAccountCode) but with different effectiveDate. An unexpected result maybe returned.
- The order of kfsChartCode, kfsAccountCode, effectiveDate CANNOT be swapped.
- effectiveDate has to be valid. Null will be returned otherwise.
Input Specifications
| Index | Parameter | Description | Required? | Data Type (max length) | Example |
|---|---|---|---|---|---|
| 0 | kfsChartCode | KFS Chart Code | Yes | String (2) | 'IR' |
| 1 | kfsAcctNumber | KFS Account Number | Yes | String(7) | '1234567' |
| 2 | effectiveDate | Date the KFS account needs to be valid | No (default is today's date) | String(8) YYYYMMDD | '20120701' (begin fiscal year 2012/2013) |
| kfsChartCode | KFS Chart Code | Yes | String (2) | 'IR' | |
| kfsAcctNumber | KFS Account Number | Yes | String(7)'1234567' |
DB Query
| Code Block |
|---|
Old:
select distinct
case when not exists (select * FROM dwhs_ods..ca_account_t a where a.account_nbr= '<kfsAccountCode>' AND a.fin_coa_cd= '<kfsChartCode>'
AND a.ACCT_CLOSED_IND <> 'Y' AND ( a.ACCT_EXPIRATION_DT >= getdate() or a.ACCT_EXPIRATION_DT IS NULL)
AND (a.ACCT_EFFECT_DT <= <effectiveDate> ) AND a.ACCT_TYP_CD = 'EX')
then 'false'
else 'true'
end
New:
SELECT a.FIN_COA_CD, a.ACCOUNT_NBR,'true' AS VALID FROM dwhs_ods..ca_account_t a WHERE
a.ACCT_CLOSED_IND <> 'Y' AND
(a.ACCT_EXPIRATION_DT >= getdate() OR a.ACCT_EXPIRATION_DT IS NULL) AND
a.ACCT_TYP_CD = 'EX' AND
(
(a.ACCOUNT_NBR= '1000766' AND
a.FIN_COA_CD= 'IR' AND
a.ACCT_EFFECT_DT <= convert(datetime,'20100701',112)) OR
(a.ACCOUNT_NBR= '1008543' AND
a.FIN_COA_CD= 'IR' AND
a.ACCT_EFFECT_DT <= getdate())
); |
...
This service is used retrieve valid KFS chart codes account and related attributes. Returns an XML result set with a list of accounts (example getAllValidKFSAccounts result).
Input Specifications
| Index | Parameter | Description | Required? | Data Type (max length) | Example |
|---|---|---|---|---|---|
| 0 | kfsChartCode | KFS Chart Code | Yes | String (2) | 'IR' |
| 1 | kfsAcctNumber | KFS Account Number | Yes | String (7) | '1234567' |
...
This service is used to translate legacy FS accounts to KFS account codes. Returns an XML result set with a KFS chart/account (example isValidKFSAccounts result) for each FS location/account
Input Specifications
| Index | Parameter | Description | Required? | Data Type (max length) |
|---|---|---|---|---|
| 0 | fsLocationCode | The FS location code | Yes | String (2) |
| 1 | fsAccountCode | The FS accounts code | Yes | String (6) |
| 2 | fsFundCode | The FS fund code | Yes | String (5) |
...