Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;