IGI - ITAS Global Interface
What is IGI
This tool has been designed to enable views of ITAS data tables to be formed for 3rd party application use. The principle uses are import and export of data, either direct one-to-one with ITAS Database tables or through free format layouts to be used in conjunction with specific business processes.
Profiles (Parameters)
These are the definitions used by the internal processes (through IGIUI) or external requests/notifications through the ITAS Web Service. The purpose is to create a universal name that can easily be recognised and referenced, so that all related details can be contained in one environment, e.g. style of data, content, target folders and so on.
Export Process
Within IGI there is an Export function that will tap into the same data source identified by the profile nominated. This will generally be effective for those whose output is defined as file but the expected data can also be brought to the screen for testing purposes.
Import Process
This is more restrictive than the export process but useful for testing inbound table data.
Mexico Electronic Invoice - annotated SQL sample
Main SQL
/* FIELDS */
SELECT
/* get the sum of the client amounts for the document */
(SELECT ABS(SUM(dhlxx_amount))
FROM <<>>_dhlxx
WHERE dhlxx_docref = '<<DOCREF>>' and dhlxx_dhltype = 1) as dhlxx_amount,
/* payment terms either from translation , code description or just the code */
ISNULL(ISNULL(pt.codetrans_descr, cmy15_descr), dhl21_payterms) as dhl21_payterms,
/* RFC from CLI einvoice details */
mychr70.chr70_einvdocnumber as myclivatreg,
/* currency from the document */
dhl21_ccy,
/* ROE is passed from the eInvoice dll */
<<ROE>> as tipoCambio,
/* Date time is passed from the einvoice.dll */
'<<DATETIME>>' as fecha,
/* City and Province for the branch */
ISNULL(br225.cmy225_descr + ' ' + brchr69.chr69_province, aud02_location) as aud02_location,
/* Sequence number prefix from the aud02 (the bit before the first number so if the aud02_seqno = A00001 then the serie = A */
LEFT(aud02_seqno, (PatIndex('%[0-9]%', aud02_seqno ) -1)) as serie,
/* Sequence number suffix from the aud02 (the bit from the first number so if the aud02_seqno = A00001 then the folio = 00001 */
RTRIM(RIGHT(aud02_seqno , len(aud02_seqno) - PatIndex('%[0-9]%', aud02_seqno )))as folio,
/* company title from ctrl02_mycli */
mycli.chr20_title as myclititle,
/* address from ctrl02_mycli – suburb, city and municipality look up from the mycli eInvoice details */
mychr69.chr69_addr1 as mycliaddr1,
mychr69.chr69_addr2 as mycliaddr2,
mychr69.chr69_addr3 as mycliaddr3,
ISNULL(my232.cmy232_descr, mychr69.chr69_suburb) as myclisuburb,
ISNULL(my225.cmy225_descr, mychr69.chr69_city) as myclicity,
ISNULL(my231.cmy231_descr, mychr69.chr69_municipality) as myclimunicipality,
ISNULL(mycli.chr20_province + '-' + my105.cmy105_descr, mycli.chr20_province) as mycliprovince,
/* country from ctrl02_mycli */
mycli.chr20_geogcd as mycligeogcd,
/* zipcode from ctrl02_mycli */
mychr69.chr69_zipcode as myclizipcode,
/* fiscal regime from ctrl02_mycli einvoice details */
'<<FISCALREGIME>>' as mycliregimen,
/* address of the branch (aud02_location) chr69_type = ‘BR’ */
brchr69.chr69_addr1 as braddr1,
brchr69.chr69_addr2 as braddr2,
brchr69.chr69_addr3 as braddr3,
ISNULL(br232.cmy232_descr, brchr69.chr69_suburb) as brsuburb,
ISNULL(br225.cmy225_descr,brchr69.chr69_city) as brcity,
ISNULL(br231.cmy231_descr, brchr69.chr69_municipality) as brmunicipality,
ISNULL(brchr69.chr69_province + '-' + br105.cmy105_descr, brchr69.chr69_province) as brprovince,
brchr69.chr69_geogcd as brgeogcd,
brchr69.chr69_zipcode as brzipcode,
/* counter party RFC from CLI einvoice details */
cpchr70.chr70_einvdocnumber as cpvatreg,
/* payment mode from counterparty */
CASE WHEN cp.chr20_paymode = ' ' THEN 'NA' ELSE cp.chr20_paymode END as chr20_paymode,
/* counterparty address from einvoice details */
cp.chr20_title as cptitle,
cpchr69.chr69_addr1 as cpaddr1,
cpchr69.chr69_addr2 as cpaddr2,
cpchr69.chr69_addr3 as cpaddr3,
ISNULL(cp232.cmy232_descr, cpchr69.chr69_suburb) as cpsuburb,
ISNULL(cp225.cmy225_descr, cpchr69.chr69_city) as cpcity,
ISNULL(cp231.cmy231_descr, cpchr69.chr69_municipality) as cpmunicipality,
ISNULL(cp.chr20_province + '-' + cp105.cmy105_descr, cp.chr20_province) as cpprovince,
cp.chr20_region as cpregion,
cp.chr20_geogcd as cpgeogcd,
cpchr69.chr69_zipcode as cpzipcode,
/* subtotal – this is the sum of nominal ledger amounts , excluding VAT amounts ( vatcd = ‘ ‘)
It also excludes prefinance amounts unless there is a prefinancing client amount i.e. prefinancing invoice */
(SELECT ISNULL(ABS(SUM(dhlxx_amount)), 0)
FROM <<>>_dhlxx
where dhlxx_vatcd = ' ' And dhlxx_ledger <> 'C'
AND dhlxx_docref = aud02_docref
AND (dhlxx_expcode <> (SELECT ctrl06_prefinancingsale FROM gf_ctrl06
WHERE ctrl06_comp = '<<>>')
or exists (select 1 FROM <<>>_dhlxx
where dhlxx_docref = aud02_docref
and dhlxx_ledger = 'C'
and dhlxx_expcode = (SELECT ctrl06_prefinancingsale FROM gf_ctrl06
WHERE ctrl06_comp = '<<>>'))) ) as subtotal,
/* sum of withholding tax legs */
(SELECT ISNULL(ABS(SUM(dhlxx_amount)), 0)
FROM <<>>_dhlxx
JOIN <<>>_cmy100
ON cmy100_vatcode = dhlxx_vatcd
where dhlxx_vatcd <> ' ' And dhlxx_docref = aud02_docref
AND cmy100_withhold = 'Y') as withholdingtax,
/* sum of vat legs */
(SELECT ISNULL(ABS(SUM(dhlxx_amount)), 0)
FROM <<>>_dhlxx
JOIN <<>>_cmy100
ON cmy100_vatcode = dhlxx_vatcd
where dhlxx_vatcd <> ' '
And dhlxx_docref = aud02_docref
AND cmy100_withhold <> 'Y') as totalvat,
/* document type – money in or money out */
case when LEFT(dhl21_docref, 2) IN ('CN','PI') THEN 'egreso' ELSE 'ingreso' END as tipoDeComprobante,
/* observations */
aud02_extranotes,
/* document type code credit note, debit note or invoice */
case when LEFT(dhl21_docref, 2) IN ('CN','PI') THEN 'NCR'
when LEFT(dhl21_docref, 2) = 'DN' THEN 'NCA' ELSE 'FAC' END as nombreCorto,
/* document type credit note, debit note or invoice */
case when LEFT(dhl21_docref, 2) IN ('CN','PI') THEN 'Nota de Credito'
when LEFT(dhl21_docref, 2) = 'DN' THEN 'Nota de Debito' ELSE 'Factura' END as descrCorto,
/* einvoice digital certificate number from einvoice settings */
(SELECT top 1 ctrl104_value FROM gf_ctrl104 where ctrl104_comp = '<<>>' and ctrl104_type = 'EINVCERTNO') as certno,
/* prefinancing document narrative */
(SELECT ISNULL(MAX(dhlxx_narr), ' ') FROM <<>>_dhlxx
WHERE dhlxx_docref = '<<DOCREF>>'
AND dhlxx_expcode = (SELECT ctrl06_prefinancingsale FROM gf_ctrl06
WHERE ctrl06_comp = '<<>>')) as motivoDescuento,
/* prefinancing amount */
(SELECT ISNULL(SUM(dhlxx_amount), 0) FROM <<>>_dhlxx
WHERE dhlxx_docref = '<<DOCREF>>'
AND dhlxx_expcode = (SELECT ctrl06_prefinancingsale FROM gf_ctrl06
WHERE ctrl06_comp = '<<>>')) as descuento
/* TABLES */
/* start with the aud02 */
FROM <<>>_aud02
/* link to the dhl for document details */
JOIN <<>>_dhl21 ON dhl21_docref = aud02_docref
/* get company settings for ctrl02_mycli */
JOIN gf_ctrl02 ON ctrl02_comp = '<<>>'
/* get mycli client details from chr20 */
JOIN <<>>_chr20 mycli on mycli.chr20_acc = ctrl02_mycli
/* get mycli einvoice address */
JOIN <<>>_chr69 mychr69 on
mychr69.chr69_acc = ctrl02_mycli
/* get branch einvoice address */
LEFT JOIN <<>>_chr69 brchr69 on
brchr69.chr69_acc = aud02_location
and brchr69.chr69_type = 'BR'
/* get mycli einvoice details */
JOIN <<>>_chr70 mychr70 on
mychr70.chr70_acc = ctrl02_mycli
/* get counterparty client details */
JOIN <<>>_chr20 cp on cp.chr20_acc = aud02_counterparty
/* get counterparty einvoice addresss */
LEFT JOIN <<>>_chr69 cpchr69 on
cpchr69.chr69_acc = aud02_counterparty
/* get counterparty einvoice details */
LEFT JOIN <<>>_chr70 cpchr70 on
cpchr70.chr70_acc = aud02_counterparty
/* get payment terms description */
LEFT JOIN <<>>_cmy15
ON cmy15_pterms = dhl21_payterms
/* get payment terms translation */
LEFT JOIN gf_codetrans pt
ON pt.codetrans_table = 'cmy15' and pt.codetrans_lang = 'Spanish'
and pt.codetrans_code = dhl21_payterms
/* get branch city */
LEFT JOIN <<>>_cmy225 br225
ON br225.cmy225_city = brchr69.chr69_city
/* get mycli city */
LEFT JOIN <<>>_cmy225 my225
ON my225.cmy225_city = mychr69.chr69_city
/* get counterparty city */
LEFT JOIN <<>>_cmy225 cp225
ON cp225.cmy225_city = cpchr69.chr69_city
/* get counterparty suburb */
LEFT JOIN <<>>_cmy232 cp232
ON cp232.cmy232_suburb = cpchr69.chr69_suburb
/* get mycli suburb */
LEFT JOIN <<>>_cmy232 my232
ON my232.cmy232_suburb = mychr69.chr69_suburb
/* get branch suburb */
LEFT JOIN <<>>_cmy232 br232
ON br232.cmy232_suburb = brchr69.chr69_suburb
/* get branch municipality */
LEFT JOIN <<>>_cmy231 br231
ON br231.cmy231_municipality = brchr69.chr69_municipality
/* get mycli municipality */
LEFT JOIN <<>>_cmy231 my231
ON my231.cmy231_municipality = mychr69.chr69_municipality
/* get counterparty municipality */
LEFT JOIN <<>>_cmy231 cp231
ON cp231.cmy231_municipality = cpchr69.chr69_municipality
/* get mycli province */
LEFT JOIN <<>>_cmy105 my105
ON my105.cmy105_code = mycli.chr20_province
/* get counterparty province */
LEFT JOIN <<>>_cmy105 cp105
ON cp105.cmy105_code = cp.chr20_province
/* get branch province */
LEFT JOIN <<>>_cmy105 br105
ON br105.cmy105_code = brchr69.chr69_province
/* WHERE Clause */
/* look up on document and find the last aud02 for that document in case there are multiple aud02s ( resends ) */
WHERE dhl21_docref = '<<DOCREF>>'
and aud02_identity = (SELECT MAX(aud02_identity) FROM <<>>_aud02 WHERE aud02_docref= '<<DOCREF>>')
Second SQL for Conceptos
/* Get quantity , weightcode , description , price , value and customs agent details from either
Phys06/phys18 – invoice markings
Aud05s – einvoice details for manual invoices and invoices created in J01
Phys12 – cost invoice markings
Phys13 – commission invoice markings
Dhlxx – transaction details
Then join to the codetrans table to get the translation of the weight code */
SELECT ABS(conquan) as conquan, ISNULL(wct.codetrans_descr, wtcode) as wtcode, descr as descr, price as price, ABS(conval) as conval,
custagent as custagent,
custentrydate as custentrydate ,
custentryno as custentryno
FROM
(
/* first union – phys06 invoice marking and phys18 – prepayment invoice markings */
SELECT
/* get the weight invoiced */
inv.invwt as conquan,
/* weight code from the contract */
phys01_wtcode as wtcode,
/*get the description from either the aud02 summarynotes or the commodity description */
CASE WHEN phys01_thirdparty = 'Y' AND aud02_notesforconcepto <> 'Y' THEN ISNULL(gct.codetrans_descr, cmy03_descr)
ELSE (SELECT TOP 1 aud02_summarynotes FROM <<>>_aud02 WHERE aud02_docref = '<<DOCREF>>') END as descr,
/* price from the contract */
phys01_conprice as price,
/* value from the invoice markings */
inv.invval as conval,
/* customs agent title */
chr20_title as custagent,
/* customs entry date */
case when cmy230_bonded = 'Y' then phys01_uscustwentrydate else phys01_uscustcentrydate end as custentrydate ,
/* customs entry number */
case when cmy230_bonded = 'Y' then phys01_uscustwentryno else phys01_uscustcentryno end as custentryno
FROM
/* combine the phys12 invoice markings and phys18 prepayment invoice markings */
(SELECT phys06_contract as contract, phys06_docref as docref, phys06_invwt as invwt, phys06_invval as invval FROM <<>>_phys06
UNION SELECT phys18_contract as contract , phys18_docref as docref, phys18_invwt as invwt, phys18_invval as invval from <<>>_phys18 ) inv
/* link to the contract */
JOIN <<>>_phys01 ON phys01_contract = inv.contract
/* link to more contract details for the customs fields */
JOIN <<>>_phys01ae ON phys01ae_contract = inv.contract
/* link to the customs status codes */
LEFT JOIN <<>>_cmy230 ON cmy230_code = phys01_uscuststatus
/* link to the customs agency client details */
LEFT JOIN <<>>_chr20 ON chr20_acc = case when cmy230_bonded = 'Y' then phys01_wcustomsagency else phys01_customsagency end
/* link to the commodity details */
LEFT JOIN <<>>_cmy03
ON phys01_commod = cmy03_commod
AND phys01_origin = cmy03_origin
AND phys01_grade = cmy03_grade
and phys01_cmycode4 = cmy03_code4
and phys01_cmycode5 = cmy03_code5
/* link to the translations table for the client */
left join gf_codetrans clitrans
ON clitrans.codetrans_code = phys01_counterparty
AND clitrans.codetrans_code2 = 'DATA$'
AND clitrans.codetrans_lang = 'Spanish'
/* link to the translations table for the commodity */
LEFT join gf_codetrans gct
on clitrans.codetrans_lang = gct.codetrans_lang
and gct.codetrans_table = 'cmy03'
and gct.codetrans_code = cmy03_commod
and gct.codetrans_code2 = cmy03_origin
and gct.codetrans_code3 = cmy03_grade
and gct.codetrans_code4 = cmy03_code4
and gct.codetrans_code5 = cmy03_code5
/* link to the company settings */
JOIN gf_ctrl02 ON ctrl02_comp = '<<>>'
/* link to the aud02 */
JOIN <<>>_aud02 on aud02_docref = '<<DOCREF>>'
/* look up based on document reference */
WHERE inv.docref = '<<DOCREF>>'
/* where there are no aud05s – if created in J01 we will have created an aud05 for each split so won’t need to go to the invoice markings */
AND NOT EXISTS ( SELECT 1 FROM <<>>_aud05 WHERE aud05_docref = '<<DOCREF>>' and aud05_summarynotes <> ' ' )
UNION ALL
/* second union – aud05s */
SELECT
/* quantity */
aud05_quantity as conquan,
/* weight code */
CASE WHEN aud05_wtcode = ' ' THEN 'N/A' ELSE aud05_wtcode END as wtcode,
/* description from either aud05_summarynotes, aud02_summarynotes or expense code description */
CASE WHEN aud02_notesforconcepto = 'Y' and aud05_summarynotes <> ' ' THEN aud05_summarynotes
WHEN aud02_notesforconcepto = 'Y' and aud05_summarynotes = ' ' THEN aud02_summarynotes
ELSE cmy101_name END as descr,
/* price */
aud05_price as price,
/* value */
aud05_value as conval,
/* customs agent details not required */
' ' as custagent,
0 as custentrydate ,
' ' as custentryno
FROM <<>>_aud05
/* join to expense code */
JOIN <<>>_cmy101 on cmy101_exp = aud05_expcode
/* join to main aud02 einvoice record */
JOIN <<>>_aud02 ON aud02_docref = aud05_docref
/* look up based on document reference */
WHERE aud05_docref = '<<DOCREF>>'
UNION ALL
/* 3rd union based on cost invoice markings */
SELECT
/* quantity */
phys12_invqty as conquan,
/* weight code */
CASE WHEN phys12_wtcode = ' ' THEN 'N/A' else phys12_wtcode END as wtcode,
/* cost description */
cmy25_descr as descr,
/* cost rate */
phys03_rate as price,
/* value */
phys12_docvalue as conval ,
/* customs agent details not required */
' ' as custagent,
0 as custentrydate ,
' ' as custentryno
/* from cost invoice markings */
FROM <<>>_phys12
/* join to the cost */
JOIN <<>>_phys03
ON phys03_contract = phys12_contract
and phys03_unique = phys12_unique
and phys12_code = phys03_code
/* join to the cost code */
LEFT JOIN <<>>_cmy25
ON phys12_code = cmy25_costhead
/* look up based on invoice docref */
WHERE phys12_docref = '<<DOCREF>>'
/* if there is an aud05 for the invoice use that instead */
AND NOT EXISTS ( SELECT 1 FROM <<>>_aud05 WHERE aud05_docref = '<<DOCREF>>' )
UNION ALL
/* 4th Union based on commission invoice markings phys13 */
SELECT
/* Quantity */
phys13_invqty as conquan,
/* weight code */
CASE WHEN phys13_wtcode = ' ' THEN 'N/A' else phys13_wtcode END as wtcode,
/* description = commission */
'Comisión' as descr,
/* price = comsn rate */
phys02_comrate as price,
/* value from invoice marking */
phys13_docvalue as conval ,
/* customs agent details not required */
' ' as custagent,
0 as custentrydate ,
' ' as custentryno
/* from commission invoice markings */
FROM <<>>_phys13
/* join to the commission record */
JOIN <<>>_phys02
ON phys02_contract = phys13_contract
and phys02_unique = phys13_unique
/* look up on the document reference */
WHERE phys13_docref = '<<DOCREF>>'
/* ignore if aud05s are present */
AND NOT EXISTS ( SELECT 1 FROM <<>>_aud05 WHERE aud05_docref = '<<DOCREF>>' )
UNION ALL
/* 5th union from the accounting details (posted) */
SELECT
/* quantity */
ABS(txx_units) as conquan,
/* weight code */
CASE WHEN aud02_wtcode = ' ' THEN 'N/A' else aud02_wtcode END as wtcode,
/* document narrative as description */
txx_narr as descr,
/* amount / units = price */
ABS(txx_amt / (CASE WHEN txx_units = 0 THEN 1 ELSE txx_units END )) as price,
/* amount */
ABS(txx_amt) as conval ,
/* customs agent details not required */
' ' as custagent,
0 as custentrydate ,
' ' as custentryno
FROM <<>>_txn
JOIN <<>>_aud02 ON aud02_docref = txx_docref
WHERE txx_docref = '<<DOCREF>>' and txx_dhltype <> 2
/* ignore if invoice markings / aud02s / aud05s exist */
AND NOT EXISTS ( SELECT 1 FROM <<>>_aud05 WHERE aud05_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys13 WHERE phys13_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys12 WHERE phys12_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys06 WHERE phys06_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys18 WHERE phys18_docref = '<<DOCREF>>')
UNION ALL
/* 6th union from the accounting details (unposted) */
SELECT
/* quantity */
ABS(dhlxx_qty) as conquan,
/* weight code */
CASE WHEN aud02_wtcode = ' ' THEN 'N/A' else aud02_wtcode END as wtcode,
/* description = summary notes */
aud02_summarynotes as descr,
/* price = amount / units */
ABS(dhlxx_amount / (CASE WHEN dhlxx_qty = 0 THEN 1 ELSE dhlxx_qty END )) as price,
/* amount */
ABS(dhlxx_amount) as conval ,
/* customs agent details not required */
' ' as custagent,
0 as custentrydate ,
' ' as custentryno
/* from unposted documents */
FROM <<>>_dhlxx
/* join to aud02 einvoice details */
JOIN <<>>_aud02 ON aud02_docref = dhlxx_docref
/* look up on document reference */
WHERE dhlxx_docref = '<<DOCREF>>'
/* nominal ledger only – not vat leg */
AND dhlxx_ledger = 'N' and dhlxx_dhltype <> 2
/* ignore if invoice markings / aud02s / aud05s / postings exist */
AND NOT EXISTS ( SELECT 1 FROM <<>>_aud05 WHERE aud05_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys13 WHERE phys13_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys12 WHERE phys12_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys06 WHERE phys06_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_phys18 WHERE phys18_docref = '<<DOCREF>>'
UNION ALL
SELECT 1 FROM <<>>_txc WHERE txx_docref = '<<DOCREF>>' )
) A
/* link to translations */
LEFT JOIN gf_codetrans wct
ON wct.codetrans_lang = 'Spanish'
and wct.codetrans_code = A.wtcode
and wct.codetrans_table = 'cmy11'
Retencion
SELECT
/* amount */
ABS(dhlxx_amount) as importe ,
/* interface code */
cmy100_gpcode as impuesto ,
/* vat rate */
cmy100_vatrate as tasa
/* from unposted document legs */
FROM <<>>_dhlxx
/* link to vat codes */
JOIN <<>>_cmy100 on dhlxx_vatcd = cmy100_vatcode
/* look up on document reference */
WHERE dhlxx_docref = '<<DOCREF>>'
/* where the leg has a vat code */
AND dhlxx_vatcd <> ' '
/* and the vat code is withholding */
AND cmy100_withhold = 'Y'
Translado
SELECT
/* amount */
ABS(dhlxx_amount) as importe ,
/* tax type = IVA */
'IVA' as impuesto ,
/* rate from vat code rates */
cmy100_vatrate as tasa
/* from unposted document legs */
FROM <<>>_dhlxx
/* link to the vat codes */
JOIN <<>>_cmy100 on dhlxx_vatcd = cmy100_vatcode
/* look up on the document reference */
WHERE dhlxx_docref = '<<DOCREF>>'
/* where the vat code is on the leg */
AND dhlxx_vatcd <> ' '
/* and the vat code is not withholding */
AND cmy100_withhold <> 'Y'
Contents of IGI - ITAS Global Interface
-
Creating a CSV Export
1. Enter a name for the export style in the name textbox: 2. Enter the export details: 3. On the Data Tab enter the tables and fields to be exported. 4. On the SQL Tab ...
-
Creating an XML Export Style
1. Enter a name for the export style in the name textbox: 2. Enter the export details changing the data format to XML: 3. On the data tab you need to define the xml file ...
-
Adding Validation
Exported data can be validated against rules set up in the Export Parameters. To access the validation screen click on the grid validation button. The following rules are available: Multiple rules can be added per ...
-
Filters
Filters can be applied to exported data: The following rules are available:
-
Formatting Data
Dates can be formatted using a translation type of Date: If a translation type of mapping is set the ITAS codes can be converted to an export code specified on the mappings screen: XML Exports ...
-
Exporting Data
1. Select the export style. 2. If the <<FILTER>> parameter is specified in the export style sql a filter button will be added to the filters tab for each table. 3. If custom parameters are ...