Microsoft Dynamics Ax Invoice load script - Qlik Community

Report Inappropriate Content · ‎2014-03-06

This is the Microsoft Dynamics Ax Invoice Load Script.

Invoices:

LOAD

CASHDISC as [Importe de descuento por pronto pago],

ApplyMap('DescuentosProntoPago',CASHDISCCODE & ' - ' & DATAAREAID,Null()) as [Descuento por pronto pago],

CASHDISCDATE as [Fecha limite para la aplicación del descuento por pronto pago],

CASHDISCPERCENT as [Porcentaje de descuento por pronto pago],

CUSTGROUP as [Grupo del tercero],

DIMENSION as [Código del area de negocio],

ApplyMap('AreasNegocio',DIMENSION,Null()) as [Area de negocio],

DIMENSION2_ as [Código de delegación],

ApplyMap('Delegaciones',DIMENSION2_,Null()) as [Delegación],

DIMENSION3_ as [Código de proyecto],

ApplyMap('Proyectos',DIMENSION3_,Null()) as [Proyecto],

DIMENSION4_ as [Código de la linea de negocio],

ApplyMap('LineasNegocio',DIMENSION4_,Null()) as [Línea de negocio],

DIMENSION5_ as [Código del area de actividad],

ApplyMap('AreasActividad',DIMENSION5_,Null()) as [Area de actividad],

DIMENSION6_ as [Código de la subunidad de negocio],

ApplyMap('SubunidadNegocio',DIMENSION6_,Null()) as [Subunidad de negocio],

DIMENSION7_ as [Código del centro de coste],

ApplyMap('CentrosCoste',DIMENSION7_,Null()) as [Centro de coste],

DIMENSION8_ as [Código de la naturales de la transacción de nivel 1],

ApplyMap('NaturalezaTransaccionNivel1',DIMENSION8_,Null()) as [Naturaleza de la transacción de nivel 1],

DIMENSION9_ as [Código de la naturaleza de la transacción de nivel 2],

ApplyMap('NaturalezaTransaccionNivel2',DIMENSION9_,Null()) as [Naturaleza de la transacción de nivel 2],

DIMENSION10_ as [Código de la unidad de negocio],

ApplyMap('UnidadNegocio',DIMENSION10_,Null()) as [Unidad de negocio],

DOCUMENTDATE as [Fecha del documento],

DOCUMENTNUM as [Número del documento],

DUEDATE as [Fecha de vencimiento],

ENDDISC as [Importe de descuento],

FTBGUARANTEEDEDUCTION as [Importe retención de garantia],

FTBGUARANTEEDEDUCTIONPERCENT as [Porcentaje de retención de garantia],

FTBSHIPMENTDATE as [Fecha de cierre],

INVCOUNTRYREGIONID as [Código de pais],

ApplyMap('Paises',INVCOUNTRYREGIONID,Null()) as [Pais],

INVCOUNTY as [Código de provincia],

ApplyMap('Provincias',INVCOUNTRYREGIONID & ' - ' & INVCOUNTY,Null()) as [Provincia],

INVENTLOCATIONID as [Código del almacen],

INVOICEACCOUNT as [Código del tercero],

INVOICEAMOUNT as [Importe de la factura],

INVOICECITY as [Ciudad],

INVOICEDATE as [Fecha de la factura],

Year(INVOICEDATE) as [Año],

Month(INVOICEDATE) as [Mes],

Day(INVOICEDATE) as [Dia],

INVOICEID as [Número de factura],

INVOICEROUNDOFF as [Importe de redondeo],

INVOICESTREET as [Calle],

ApplyMap('Clientes',INVOICEACCOUNT) as [Tercero],

//INVOICINGNAME as [Tercero],

INVSTATE as [Código de la comunidad autónoma],

ApplyMap('ComunidadesAutónomas',INVCOUNTRYREGIONID & ' - ' & INVSTATE,Null()) as [Comunidad autónoma],

INVZIPCODE as [Código postal],

LEDGERVOUCHER as [Asiento],

NUMBERSEQUENCEGROUP as [Grupo de secuencias],

PAYMDAYID as [Códgido del día de pago],

ApplyMap('DiasDePago',PAYMDAYID,Null()) as [Día de pago],

PAYMENT as [Código de la condicion de pago],

ApplyMap('CondicionesDePago',PAYMENT,Null()) as [Condiciones de pago],

PAYMENTSCHED as [Código de multivencimiento],

ApplyMap('Multivencimientos',PAYMENTSCHED,Null()) as [Multivencimiento],

POSTINGPROFILE as [Código del perfil de contabilización],

if(PREPAYMENT = 0,'No','Si') as [Anticipo],

if(PROFORMA = 0,'No','Si') as [Factura proforma],

PURCHASEORDER as [Pedido de compra],

QTY as [Cantidad],

if(REFNUM = 0,'Pedido de venta',if(REFNUM = 1, 'Proyectos',if(REFNUM = 2,'Cliente',if(REFNUM = 3,'Interes',Null())))) as [Origen de la factura],

SALESBALANCE as [Saldo de venta],

SALESID as [Pedido de venta],

if(SALESTYPE = 0,'Diario',if(SALESTYPE = 1,'Presupuesto',if(SALESTYPE = 2,'Suscripción',if(SALESTYPE = 3,'Pedido de venta',if(SALESTYPE = 4,'Devolución',if(SALESTYPE = 5,'Pedido marco',if(SALESTYPE = 6,'Artículo requerido',Null()))))))) as [Tipo de pedido],

SUMLINEDISC as [Total descuento],

SUMMARKUP as [Total gastos varios],

SUMTAX as [Total impuestos],

TAXGROUP as [Grupo de impuestos],

TAXITEMGROUP as [Grupo de impuestos de artículos],

VATNUM as [NIF/CIF],

upper(DATAAREAID) as [Código de empresa],

ApplyMap('Empresas',DATAAREAID) as [Nombre de la empresa],

'Clientes' as [Módulo],

upper(DATAAREAID) & '-' & SALESID & '-' & INVOICEID & '-' & INVOICEDATE & '-' & NUMBERSEQUENCEGROUP & '-' & 'Clientes' as [Key],

upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE & '-' & ApplyMap('MapaImpuestos',upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE,upper(INVOICEID)) as [TaxKey];

SQL SELECT CASHDISC,

CASHDISCCODE,

CASHDISCDATE,

CASHDISCPERCENT,

CUSTGROUP,

DATAAREAID,

DIMENSION,

"DIMENSION10_",

"DIMENSION2_",

"DIMENSION3_",

"DIMENSION4_",

"DIMENSION5_",

"DIMENSION6_",

"DIMENSION7_",

"DIMENSION8_",

"DIMENSION9_",

DLVMODE,

DLVTERM,

DATAAREAID,

DOCUMENTDATE,

DOCUMENTNUM,

DUEDATE,

ENDDISC,

FTBGUARANTEEDEDUCTION,

FTBGUARANTEEDEDUCTIONPERCENT,

FTBSHIPMENTDATE,

INVCOUNTRYREGIONID,

INVCOUNTY,

INVENTLOCATIONID,

INVOICEACCOUNT,

INVOICEAMOUNT,

INVOICECITY,

INVOICEDATE,

INVOICEID,

INVOICEROUNDOFF,

INVOICESTREET,

INVOICINGNAME,

INVSTATE,

INVZIPCODE,

LEDGERVOUCHER,

LISTCODE,

NUMBERSEQUENCEGROUP,

PAYMDAYID,

PAYMENT,

PAYMENTSCHED,

POSTINGPROFILE,

PREPAYMENT,

PROFORMA,

PURCHASEORDER,

QTY,

REFNUM,

SALESBALANCE,

SALESID,

SALESTYPE,

SUMLINEDISC,

SUMMARKUP,

SUMTAX,

TAXGROUP,

TAXITEMGROUP,

VATNUM

FROM "AXBD_DEFINITIVO".dbo.CUSTINVOICEJOUR

WHERE EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.CUSTTRANS WHERE (CUSTTRANS.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID) AND

(CUSTTRANS.VOUCHER = CUSTINVOICEJOUR.LEDGERVOUCHER) AND

(CUSTTRANS.TRANSDATE = CUSTINVOICEJOUR.INVOICEDATE) AND

(CUSTTRANS.ACCOUNTNUM = CUSTINVOICEJOUR.INVOICEACCOUNT) AND

(CUSTTRANS.BillOfExchangeStatus = 0));

Concatenate (Facturacion)

LOAD

CASHDISC as [Importe de descuento por pronto pago],

ApplyMap('DescuentosProntoPago',CASHDISCCODE & ' - ' & DATAAREAID,Null()) as [Descuento por pronto pago],

CASHDISCDATE as [Fecha limite para la aplicación del descuento por pronto pago],

CASHDISCPERCENT as [Porcentaje de descuento por pronto pago],

VENDGROUP as [Grupo del tercero],

DIMENSION as [Código del area de negocio],

ApplyMap('AreasNegocio',DIMENSION,Null()) as [Area de negocio],

DIMENSION2_ as [Código de delegación],

ApplyMap('Delegaciones',DIMENSION2_,Null()) as [Delegación],

DIMENSION3_ as [Código de proyecto],

ApplyMap('Proyectos',DIMENSION3_,Null()) as [Proyecto],

DIMENSION4_ as [Código de la linea de negocio],

ApplyMap('LineasNegocio',DIMENSION4_,Null()) as [Línea de negocio],

DIMENSION5_ as [Código del area de actividad],

ApplyMap('AreasActividad',DIMENSION5_,Null()) as [Area de actividad],

DIMENSION6_ as [Código de la subunidad de negocio],

ApplyMap('SubunidadNegocio',DIMENSION6_,Null()) as [Subunidad de negocio],

DIMENSION7_ as [Código del centro de coste],

ApplyMap('CentrosCoste',DIMENSION7_,Null()) as [Centro de coste],

DIMENSION8_ as [Código de la naturales de la transacción de nivel 1],

ApplyMap('NaturalezaTransaccionNivel1',DIMENSION8_,Null()) as [Naturaleza de la transacción de nivel 1],

DIMENSION9_ as [Código de la naturaleza de la transacción de nivel 2],

ApplyMap('NaturalezaTransaccionNivel2',DIMENSION9_,Null()) as [Naturaleza de la transacción de nivel 2],

DIMENSION10_ as [Código de la unidad de negocio],

ApplyMap('UnidadNegocio',DIMENSION10_,Null()) as [Unidad de negocio],

DOCUMENTDATE as [Fecha del documento],

DOCUMENTNUM as [Número del documento],

DUEDATE as [Fecha de vencimiento],

ENDDISC as [Importe de descuento],

FTBGUARANTEEDEDUCTION as [Importe retención de garantia],

FTBGUARANTEEDEDUCTIONPERCENT as [Porcentaje de retención de garantia],

Null() as [Fecha de cierre],

COUNTRYREGIONID as [Código de pais],

ApplyMap('Paises',COUNTRYREGIONID,Null()) as [Pais],

Null() as [Código del almacen],

INVOICEACCOUNT as [Código del tercero],

INVOICEAMOUNT as [Importe de la factura],

Null() as [Ciudad],

INVOICEDATE as [Fecha de la factura],

Year(INVOICEDATE) as [Año],

Month(INVOICEDATE) as [Mes],

Day(INVOICEDATE) as [Dia],

INVOICEID as [Número de factura],

INVOICEROUNDOFF as [Importe de redondeo],

Null() as [Calle],

ApplyMap('Proveedores',INVOICEACCOUNT) as [Tercero],

//VENDNAME as [Tercero],

Null() as [Código de la comunidad autónoma],

Null() as [Comunidad autónoma],

Null() as [Código postal],

LEDGERVOUCHER as [Asiento],

NUMBERSEQUENCEGROUP as [Grupo de secuencias],

PAYMDAYID as [Códgido del día de pago],

ApplyMap('DiasDePago',PAYMDAYID,Null()) as [Día de pago],

PAYMENT as [Código de la condicion de pago],

ApplyMap('CondicionesDePago',PAYMENT,Null()) as [Condiciones de pago],

PAYMENTSCHED as [Código de multivencimiento],

ApplyMap('Multivencimientos',PAYMENTSCHED,Null()) as [Multivencimiento],

POSTINGPROFILE as [Código del perfil de contabilización],

if(PREPAYMENT = 0,'No','Si') as [Anticipo],

if(PROFORMA = 0,'No','Si') as [Factura proforma],

PURCHID as [Pedido de compra],

QTY as [Cantidad],

Null() as [Origen de la factura],

SALESBALANCE as [Saldo de venta],

Null() as [Pedido de venta],

if(PURCHASETYPE = 0,'Diario',if(PURCHASETYPE = 1,'Presupuesto',if(PURCHASETYPE = 2,'Suscripción',if(PURCHASETYPE = 3,'Pedido de venta',if(PURCHASETYPE = 4,'Devolución',if(PURCHASETYPE = 5,'Pedido marco',if(PURCHASETYPE = 6,'Artículo requerido',Null()))))))) as [Tipo de pedido],

SUMLINEDISC as [Total descuento],

SUMMARKUP as [Total gastos varios],

SUMTAX as [Total impuestos],

TAXGROUP as [Grupo de impuestos],

Null() as [Grupo de impuestos de artículos],

VATNUM as [NIF/CIF],

upper(DATAAREAID) as [Código de empresa],

ApplyMap('Empresas',DATAAREAID) as [Nombre de la empresa],

'Proveedores' as [Módulo],

upper(DATAAREAID) & '-' & PURCHID & '-' & INVOICEID & '-' & INVOICEDATE & '-' & NUMBERSEQUENCEGROUP & '-' & INTERNALINVOICEID & '-' & 'Proveedores' as [Key],

upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE & '-' & ApplyMap('MapaImpuestos',upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE,upper(INVOICEID)) as [TaxKey];

SQL SELECT CASHDISC,

CASHDISCCODE,

CASHDISCDATE,

CASHDISCPERCENT,

COUNTRYREGIONID,

DATAAREAID,

DIMENSION,

"DIMENSION10_",

"DIMENSION2_",

"DIMENSION3_",

"DIMENSION4_",

"DIMENSION5_",

"DIMENSION6_",

"DIMENSION7_",

"DIMENSION8_",

"DIMENSION9_",

DOCUMENTDATE,

DOCUMENTNUM,

DUEDATE,

ENDDISC,

FTBGUARANTEEDEDUCTION,

FTBGUARANTEEDEDUCTIONPERCENT,

FTBSHIPMENTDATE,

INVOICEACCOUNT,

INVOICEAMOUNT,

INVOICEDATE,

INVOICEID,

INVOICEROUNDOFF,

INTERNALINVOICEID,

LEDGERVOUCHER,

NUMBERSEQUENCEGROUP,

PAYMDAYID,

PAYMENT,

PAYMENTSCHED,

POSTINGPROFILE,

PREPAYMENT,

PROFORMA,

PURCHASETYPE,

PURCHID,

QTY,

SALESBALANCE,

SUMLINEDISC,

SUMMARKUP,

SUMTAX,

TAXGROUP,

VATNUM,

VENDGROUP,

VENDNAME

FROM "AXBD_DEFINITIVO".dbo.VENDINVOICEJOUR

WHERE EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.VENDTRANS WHERE (VENDTRANS.DATAAREAID = VENDINVOICEJOUR.DATAAREAID) AND

(VENDTRANS.VOUCHER = VENDINVOICEJOUR.LEDGERVOUCHER) AND

(VENDTRANS.TRANSDATE = VENDINVOICEJOUR.INVOICEDATE) AND

(VENDTRANS.ACCOUNTNUM = VENDINVOICEJOUR.INVOICEACCOUNT) AND

(VENDTRANS.PromissoryNoteStatus = 6));

Concatenate (Facturacion)

LOAD

CASHDISC as [Importe de descuento por pronto pago],

ApplyMap('DescuentosProntoPago',CASHDISCCODE & ' - ' & DATAAREAID,Null()) as [Descuento por pronto pago],

CASHDISCDATE as [Fecha limite para la aplicación del descuento por pronto pago],

CASHDISCPERCENT as [Porcentaje de descuento por pronto pago],

Null() as [Grupo del tercero],

DIMENSION as [Código del area de negocio],

ApplyMap('AreasNegocio',DIMENSION,Null()) as [Area de negocio],

DIMENSION2_ as [Código de delegación],

ApplyMap('Delegaciones',DIMENSION2_,Null()) as [Delegación],

DIMENSION3_ as [Código de proyecto],

ApplyMap('Proyectos',DIMENSION3_,Null()) as [Proyecto],

DIMENSION4_ as [Código de la linea de negocio],

ApplyMap('LineasNegocio',DIMENSION4_,Null()) as [Línea de negocio],

DIMENSION5_ as [Código del area de actividad],

ApplyMap('AreasActividad',DIMENSION5_,Null()) as [Area de actividad],

DIMENSION6_ as [Código de la subunidad de negocio],

ApplyMap('SubunidadNegocio',DIMENSION6_,Null()) as [Subunidad de negocio],

DIMENSION7_ as [Código del centro de coste],

ApplyMap('CentrosCoste',DIMENSION7_,Null()) as [Centro de coste],

DIMENSION8_ as [Código de la naturales de la transacción de nivel 1],

ApplyMap('NaturalezaTransaccionNivel1',DIMENSION8_,Null()) as [Naturaleza de la transacción de nivel 1],

DIMENSION9_ as [Código de la naturaleza de la transacción de nivel 2],

ApplyMap('NaturalezaTransaccionNivel2',DIMENSION9_,Null()) as [Naturaleza de la transacción de nivel 2],

DIMENSION10_ as [Código de la unidad de negocio],

ApplyMap('UnidadNegocio',DIMENSION10_,Null()) as [Unidad de negocio],

INVOICEDATE as [Fecha del documento],

PROJINVOICEID as [Número del documento],

DUEDATE as [Fecha de vencimiento],

ENDDISC as [Importe de descuento],

FTBGUARANTEEDEDUCTION as [Importe retención de garantia],

FTBGUARANTEEDEDUCTIONPERCENT as [Porcentaje de retención de garantia],

FTBSHIPMENTDATE as [Fecha de cierre],

COUNTRYREGIONID as [Código de pais],

ApplyMap('Paises',COUNTRYREGIONID,Null()) as [Pais],

DLVCOUNTY as [Código de provincia],

ApplyMap('Provincias',COUNTRYREGIONID & ' - ' & DLVCOUNTY,Null()) as [Provincia],

Null() as [Código del almacen],

INVOICEACCOUNT as [Código del tercero],

INVOICEAMOUNT as [Importe de la factura],

DELIVERYCITY as [Ciudad],

INVOICEDATE as [Fecha de la factura],

Year(INVOICEDATE) as [Año],

Month(INVOICEDATE) as [Mes],

Day(INVOICEDATE) as [Dia],

PROJINVOICEID as [Número de factura],

INVOICEROUNDOFF as [Importe de redondeo],

DELIVERYSTREET as [Calle],

ApplyMap('Clientes',INVOICEACCOUNT) as [Tercero],

//INVOICINGNAME as [Tercero],

DLVSTATE as [Código de la comunidad autónoma],

ApplyMap('ComunidadesAutónomas',COUNTRYREGIONID & ' - ' & DLVSTATE,Null()) as [Comunidad autónoma],

DLVZIPCODE as [Código postal],

LEDGERVOUCHER as [Asiento],

NUMBERSEQUENCEGROUPID as [Grupo de secuencias],

PAYMDAYID as [Códgido del día de pago],

ApplyMap('DiasDePago',PAYMDAYID,Null()) as [Día de pago],

PAYMENT as [Código de la condicion de pago],

ApplyMap('CondicionesDePago',PAYMENT,Null()) as [Condiciones de pago],

PAYMENTSCHED as [Código de multivencimiento],

ApplyMap('Multivencimientos',PAYMENTSCHED,Null()) as [Multivencimiento],

POSTINGPROFILE as [Código del perfil de contabilización],

'No' as [Anticipo],

'No' as [Factura proforma],

Null() as [Pedido de compra],

QTY as [Cantidad],

'Proyectos' as [Origen de la factura],

SALESORDERBALANCE as [Saldo de venta],

Null() as [Pedido de venta],

Null() as [Tipo de pedido],

SUMLINEDISC as [Total descuento],

SUMMARKUP as [Total gastos varios],

SUMTAX as [Total impuestos],

TAXGROUPID as [Grupo de impuestos],

Null() as [Grupo de impuestos de artículos],

VATNUM as [NIF/CIF],

upper(DATAAREAID) as [Código de empresa],

ApplyMap('Empresas',DATAAREAID) as [Nombre de la empresa],

'Clientes' as [Módulo],

upper(DATAAREAID) & '-' & Null() & '-' & PROJINVOICEID & '-' & INVOICEDATE & '-' & NUMBERSEQUENCEGROUPID & '-' & 'Clientes' as [Key],

upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE & '-' & ApplyMap('MapaImpuestos',upper(DATAAREAID) & '-' & LEDGERVOUCHER & '-' & INVOICEDATE,upper(PROJINVOICEID)) as [TaxKey];

SQL SELECT CASHDISC,

CASHDISCCODE,

CASHDISCDATE,

CASHDISCPERCENT,

DIMENSION,

DIMENSION2_,

DIMENSION3_,

DIMENSION4_,

DIMENSION5_,

DIMENSION6_,

DIMENSION7_,

DIMENSION8_,

DIMENSION9_,

DIMENSION10_,

INVOICEDATE,

PROJINVOICEID,

DUEDATE,

ENDDISC,

FTBGUARANTEEDEDUCTION,

FTBGUARANTEEDEDUCTIONPERCENT,

FTBSHIPMENTDATE,

COUNTRYREGIONID,

DLVCOUNTY,

INVOICEACCOUNT,

INVOICEAMOUNT,

DELIVERYCITY,

INVOICEDATE,

INVOICEROUNDOFF,

DELIVERYSTREET,

DLVSTATE,

DLVZIPCODE,

LEDGERVOUCHER,

NUMBERSEQUENCEGROUPID,

PAYMDAYID,

PAYMENT,

PAYMENTSCHED,

POSTINGPROFILE,

QTY,

SALESORDERBALANCE,

SUMLINEDISC,

SUMMARKUP,

SUMTAX,

TAXGROUPID,

VATNUM,

DATAAREAID

FROM "AXBD_DEFINITIVO".dbo.PROJINVOICEJOUR

WHERE EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.CUSTTRANS WHERE (CUSTTRANS.DATAAREAID = PROJINVOICEJOUR.DATAAREAID) AND

(CUSTTRANS.VOUCHER = PROJINVOICEJOUR.LEDGERVOUCHER) AND

(CUSTTRANS.TRANSDATE = PROJINVOICEJOUR.INVOICEDATE) AND

(CUSTTRANS.ACCOUNTNUM = PROJINVOICEJOUR.INVOICEACCOUNT) AND

(CUSTTRANS.BillOfExchangeStatus = 0));

STORE Facturacion into F:\qlikview\qvd\Facturacion y cobros\Facturacion.qvd;

DROP TABLE Facturacion;

DetalleFacturacion:

LOAD

ASSETBOOKID as [Código del Modelo de valor],

ApplyMap('ModelosValor',ASSETBOOKID,Null()) as [Modelo de valor],

ASSETID as [Código del activo],

ApplyMap('Activos',ASSETID,Null()) as [Activo],

DIMENSION as [Código del area de negocio],

ApplyMap('AreasNegocio',DIMENSION,Null()) as [Area de negocio],

DIMENSION2_ as [Código de delegación],

ApplyMap('Delegaciones',DIMENSION2_,Null()) as [Delegación],

DIMENSION3_ as [Código de proyecto],

ApplyMap('Proyectos',DIMENSION3_,Null()) as [Proyecto],

DIMENSION4_ as [Código de la linea de negocio],

ApplyMap('LineasNegocio',DIMENSION4_,Null()) as [Línea de negocio],

DIMENSION5_ as [Código del area de actividad],

ApplyMap('AreasActividad',DIMENSION5_,Null()) as [Area de actividad],

DIMENSION6_ as [Código de la subunidad de negocio],

ApplyMap('SubunidadNegocio',DIMENSION6_,Null()) as [Subunidad de negocio],

DIMENSION7_ as [Código del centro de coste],

ApplyMap('CentrosCoste',DIMENSION7_,Null()) as [Centro de coste],

DIMENSION8_ as [Código de la naturales de la transacción de nivel 1],

ApplyMap('NaturalezaTransaccionNivel1',DIMENSION8_,Null()) as [Naturaleza de la transacción de nivel 1],

DIMENSION9_ as [Código de la naturaleza de la transacción de nivel 2],

ApplyMap('NaturalezaTransaccionNivel2',DIMENSION9_,Null()) as [Naturaleza de la transacción de nivel 2],

DIMENSION10_ as [Código de la unidad de negocio],

ApplyMap('UnidadNegocio',DIMENSION10_,Null()) as [Unidad de negocio],

DISCAMOUNT as [Descuento],

DISCPERCENT as [Porcentaje de descuento],

EXTERNALITEMID as [Código externo de artículo],

INVENTDIMID as [Combinaciones de dimensiones de inventario],

INVENTQTY as [Cantidad de inventario],

INVENTTRANSID as [Id de lote interno],

INVOICEDATE as [Fecha de la factura],

ITEMID as [Código del artículo],

LEDGERACCOUNT as [Código de cuenta contable],

LINEAMOUNT as [Importe],

LINEAMOUNTTAX as [Importe de impuestos],

LINEDISC as [Importe de descuento],

LINEHEADER as [Texto],

LINENUM as [Número de linea],

MULTILNDISC as [Importe de descuento multilinea],

MULTILNPERCENT as [Porcentaje de descuento multilinea],

NAME as [Artículo],

if(PARTDELIVERY = 0,'No','Sí') as [Entrega parcial],

PRICEUNIT as [Precio unitario],

QTY as [Cantidad],

REMAIN as [Cantidad pendiente],

SALESMARKUP as [Importe de gastos varios],

TAXGROUP as [Grupo de impuestos],

TAXITEMGROUP as [Grupo de impuestos de artículo],

upper(DATAAREAID) & '-' & SALESID & '-' & INVOICEID & '-' & INVOICEDATE & '-' & NUMBERSEQUENCEGROUP & '-' & 'Clientes' as [Key];

SQL SELECT ASSETBOOKID,

ASSETID,

DATAAREAID,

DIMENSION,

"DIMENSION10_",

"DIMENSION2_",

"DIMENSION3_",

"DIMENSION4_",

"DIMENSION5_",

"DIMENSION6_",

"DIMENSION7_",

"DIMENSION8_",

"DIMENSION9_",

DISCAMOUNT,

DISCPERCENT,

EXTERNALITEMID,

INVENTDIMID,

INVENTQTY,

INVENTTRANSID,

INVOICEDATE,

INVOICEID,

ITEMID,

LEDGERACCOUNT,

LINEAMOUNT,

LINEAMOUNTTAX,

LINEDISC,

LINEHEADER,

LINENUM,

LINEPERCENT,

MULTILNDISC,

MULTILNPERCENT,

NAME,

NUMBERSEQUENCEGROUP,

PARTDELIVERY,

PRICEUNIT,

QTY,

REMAIN,

SALESID,

SALESMARKUP,

TAXGROUP,

TAXITEMGROUP

FROM "AXBD_DEFINITIVO".dbo.CUSTINVOICETRANS

WHERE EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.CUSTINVOICEJOUR WHERE (CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID) AND

(CUSTINVOICEJOUR.SALESID = CUSTINVOICETRANS.SALESID) AND

(CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID) AND

(CUSTINVOICEJOUR.INVOICEDATE = CUSTINVOICETRANS.INVOICEDATE) AND

(CUSTINVOICEJOUR.NUMBERSEQUENCEGROUP = CUSTINVOICETRANS.NUMBERSEQUENCEGROUP) AND

EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.CUSTTRANS WHERE (CUSTTRANS.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID) AND

(CUSTTRANS.VOUCHER = CUSTINVOICEJOUR.LEDGERVOUCHER) AND

(CUSTTRANS.TRANSDATE = CUSTINVOICEJOUR.INVOICEDATE) AND

(CUSTTRANS.ACCOUNTNUM = CUSTINVOICEJOUR.INVOICEACCOUNT) AND

(CUSTTRANS.BillOfExchangeStatus = 0)));

Concatenate(DetalleFacturacion)

LOAD

Null() as [Código del Modelo de valor],

Null() as [Modelo de valor],

Null() as [Código del activo],

Null() as [Activo],

DIMENSION as [Código del area de negocio],

ApplyMap('AreasNegocio',DIMENSION,Null()) as [Area de negocio],

DIMENSION2_ as [Código de delegación],

ApplyMap('Delegaciones',DIMENSION2_,Null()) as [Delegación],

DIMENSION3_ as [Código de proyecto],

ApplyMap('Proyectos',DIMENSION3_,Null()) as [Proyecto],

DIMENSION4_ as [Código de la linea de negocio],

ApplyMap('LineasNegocio',DIMENSION4_,Null()) as [Línea de negocio],

DIMENSION5_ as [Código del area de actividad],

ApplyMap('AreasActividad',DIMENSION5_,Null()) as [Area de actividad],

DIMENSION6_ as [Código de la subunidad de negocio],

ApplyMap('SubunidadNegocio',DIMENSION6_,Null()) as [Subunidad de negocio],

DIMENSION7_ as [Código del centro de coste],

ApplyMap('CentrosCoste',DIMENSION7_,Null()) as [Centro de coste],

DIMENSION8_ as [Código de la naturales de la transacción de nivel 1],

ApplyMap('NaturalezaTransaccionNivel1',DIMENSION8_,Null()) as [Naturaleza de la transacción de nivel 1],

DIMENSION9_ as [Código de la naturaleza de la transacción de nivel 2],

ApplyMap('NaturalezaTransaccionNivel2',DIMENSION9_,Null()) as [Naturaleza de la transacción de nivel 2],

DIMENSION10_ as [Código de la unidad de negocio],

ApplyMap('UnidadNegocio',DIMENSION10_,Null()) as [Unidad de negocio],

DISCAMOUNT as [Descuento],

DISCPERCENT as [Porcentaje de descuento],

EXTERNALITEMID as [Código externo de artículo],

INVENTDIMID as [Combinaciones de dimensiones de inventario],

INVENTQTY as [Cantidad de inventario],

INVENTTRANSID as [Id de lote interno],

INVOICEDATE as [Fecha de la factura],

ITEMID as [Código del artículo],

LEDGERACCOUNT as [Código de cuenta contable],

LINEAMOUNT as [Importe],

LINEAMOUNTTAX as [Importe de impuestos],

LINEDISC as [Importe de descuento],

Null() as [Texto],

LINENUM as [Número de linea],

MULTILNDISC as [Importe de descuento multilinea],

MULTILNPERCENT as [Porcentaje de descuento multilinea],

NAME as [Artículo],

if(PARTDELIVERY = 0,'No','Sí') as [Entrega parcial],

PRICEUNIT as [Precio unitario],

QTY as [Cantidad],

0 as [Cantidad pendiente],

Null() as [Código de grupo de ventas],

0 as [Importe de gastos varios],

TAXGROUP as [Grupo de impuestos],

TAXITEMGROUP as [Grupo de impuestos de artículo],

upper(DATAAREAID) & '-' & PURCHID & '-' & INVOICEID & '-' & INVOICEDATE & '-' & NUMBERSEQUENCEGROUP & '-' & INTERNALINVOICEID & '-' & 'Proveedores' as [Key];

SQL SELECT DATAAREAID,

DIMENSION,

"DIMENSION10_",

"DIMENSION2_",

"DIMENSION3_",

"DIMENSION4_",

"DIMENSION5_",

"DIMENSION6_",

"DIMENSION7_",

"DIMENSION8_",

"DIMENSION9_",

DISCAMOUNT,

DISCPERCENT,

EXTERNALITEMID,

INVENTDIMID,

INVENTQTY,

INVENTTRANSID,

INVOICEDATE,

INVOICEID,

INTERNALINVOICEID,

ITEMID,

LEDGERACCOUNT,

LINEAMOUNT,

LINEAMOUNTTAX,

LINEDISC,

LINENUM,

LINEPERCENT,

MULTILNDISC,

MULTILNPERCENT,

NAME,

NUMBERSEQUENCEGROUP,

PARTDELIVERY,

PRICEUNIT,

PURCHID,

QTY,

TAXGROUP,

TAXITEMGROUP

FROM "AXBD_DEFINITIVO".dbo.VENDINVOICETRANS

WHERE EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.VENDINVOICEJOUR WHERE (VENDINVOICEJOUR.DATAAREAID = VENDINVOICETRANS.DATAAREAID) AND

(VENDINVOICEJOUR.PURCHID = VENDINVOICETRANS.PURCHID) AND

(VENDINVOICEJOUR.INVOICEID = VENDINVOICETRANS.INVOICEID) AND

(VENDINVOICEJOUR.INVOICEDATE = VENDINVOICETRANS.INVOICEDATE) AND

(VENDINVOICEJOUR.NUMBERSEQUENCEGROUP = VENDINVOICETRANS.NUMBERSEQUENCEGROUP) AND

(VENDINVOICEJOUR.INTERNALINVOICEID = VENDINVOICETRANS.INTERNALINVOICEID) AND

EXISTS (SELECT RECID

FROM "AXBD_DEFINITIVO".dbo.VENDTRANS WHERE (VENDTRANS.VOUCHER = VENDINVOICEJOUR.LEDGERVOUCHER) AND

(VENDTRANS.TRANSDATE = VENDINVOICEJOUR.INVOICEDATE) AND

(VENDTRANS.ACCOUNTNUM = VENDINVOICEJOUR.INVOICEACCOUNT) AND

(VENDTRANS.PromissoryNoteStatus = 6)));

STORE DetalleFacturacion into F:\qlikview\qvd\Facturacion y cobros\Detalle facturacion.qvd;

DROP TABLE DetalleFacturacion;