• Author / Uploaded
• Téxun Galería

• Categories
• SQL
• Sap Se
• Libros
• Kit de desarrollo de software
• Red mundial

Citation preview

Página 1

Tabla de contenido Prefacio

1

Sección 1 - SQL Query Basic Capítulo 1: Usuarios de SAP Business One Query y conceptos básicos de consulta ¿Quién puede beneficiarse del uso de consultas SQL en SAP Business One?

99 99

Consultor Desarrollador Usuario final de SAP Business One Usuarios que no son de SAP Business One Consulta SQL y términos relacionados

10 10 10 11 11

RDBMS Mesa Campo SQL T-SQL Subconjuntos de SQL Consulta Diccionario de datos SAP Business One: referencia de tablas de base de datos Convención de nomenclatura de tablas para SAP Business One

11 11 12 12 13 13 13 14 14 15

Palabras de tres letras Tablas "O" Tablas "A" Tablas de encabezado de documentos Tablas de líneas de documentos Ejemplos importantes de tablas

Enlaces de tabla: la clave para la consulta correcta Clave primaria Clave externa

15 dieciséis dieciséis dieciséis dieciséis 17

17 17 18 años

Página 2

Tabla de contenido

Ejemplo de enlaces de tabla en SAP Business One Tablas base versus tablas objetivo Manteniéndolo simple: la clave para crear una buena consulta Resumen

Capítulo 2: Generador de consultas y Asistente de consultas

18 años 18 años 21 22

23

Generador de consultas

24

Descripción general del generador de consultas Parte izquierda del formulario Generador de consultas Partes media y derecha del formulario Generador de consultas Ejecutar una consulta desde el formulario Generador de consultas Asistente para consultas

24 25 26 27 28

Descripción general del Asistente de consultas Paso 1: pantalla de bienvenida Paso 2: seleccione tablas para el informe Paso 3: seleccione los campos y ordene los pedidos Paso 4: condiciones y relaciones Paso 5: completar el asistente de consultas ¿Cuál es la diferencia entre el generador de consultas y el asistente de consultas? Beneficiarse de las consultas integradas del sistema Resumen

28 29 29 30 32 34 35 36 40

Capítulo 3: Administrador de consultas y declaraciones de consulta

41

Interfaz de usuario del Administrador de consultas

42

Mostrar todas las consultas existentes Crear y guardar consultas de usuarios Eliminar consultas de usuarios Administrar categorías de consulta Declaraciones de uso común SELECCIONAR: primera instrucción para recuperar datos

43 45 47 47 52 53

El alcance del valor que se puede recuperar

53

El número de columnas a incluir. Descripciones de nombre de columna Las cláusulas pueden seguir esta declaración

56 56 57

DISTINCT: los registros duplicados se pueden eliminar TOP: número de líneas devueltas por clasificación FROM: se puede asignar un recurso de datos Una sola mesa Un grupo de tablas vinculadas. Múltiples tablas separadas por comas

57 58 58 59 61 62

JOIN: se pueden vincular tablas o tablas de suma Unir internamente Unión externa

62 63 sesenta y cinco

DÓNDE: condiciones de consulta que se definirán [ ii ]

68

Página 3

Tabla de contenido

ENTRE: rangos que se definirán desde el extremo inferior al superior

70

IN / EXISTS: la lista de valores que puede satisfacer la condición LIKE: se pueden encontrar registros similares GROUP BY: resumen de los datos según la lista TENER: condiciones que se definirán en el informe resumido

71 71 72 73

ORDENAR POR: el resultado del informe puede ser por su orden preferida UNION / UNION ALL: para unir dos o más consultas Algunas funciones importantes para devolver valores

74 74 75

Predicado ISNULL () Función SUM () Función MAX () Función MIN () Función COUNT () Función DATEDIFF () Función DATEADD () Función DATEPART () Función CAST () / CONVERT () Expresiones CASE Expresiones IF Resumen

75 76 76 77 77 78 80 80 81 81 83 84

Sección 2 - Consulta SQL en acción Capítulo 4: Ejemplos de consultas

87

¿Por qué se han elegido tres categorías? Definición de variables para consultas

88 89

Caso 4-R1: cuatro variables en una consulta Caso 4-R2: Variables primero o último Función de fecha: donde surgen la mayoría de los problemas

89 90 91 91

Caso 4-D1: Balance de producción por un mes. Caso 4-D2: Cómo ingresar un rango de fechas fijo Flecha naranja: una excelente herramienta para profundizar

92 93 94

Caso 4-O1: simplifíquelo Caso 4-O2: alerta de actualización de pedido de ventas con desglose Obtener un subtotal de la consulta

95 96 97

Caso 4-T1: por Union ALL Caso 2: por total acumulado Consulta de documentos de marketing

98 99 100

Caso 4-M1: Descripción general de BP con selección de saldo realizado Caso 4-M2: cinco artículos principales vendidos Caso 4-M3: un filtro por notas de OCRD

100 100 103

Caso 4-M4: Agregar nombres de empleados de ventas a una consulta

104

[ iii ]

Página 4

Tabla de contenido

Caso 4-M5: un caso para la solución solo a partir de la deducción Caso 4-M6: PO de entrada de mercancías dentro de 10 días Caso 4-M7: Cantidad comprada, recibida y devuelta Caso 4-M8: informe de análisis de ventas personalizado Caso 4-M9: promedio de ventas por mes Caso 4-M10: Verificación de usuario de nota de crédito Caso 4-M11: fecha de entrega del pedido de cliente Caso 4-M12: reducción de dos a una línea para el resumen de ventas Caso 4-M13: resumen del código tributario Caso 4-M14: Ventas por estados Caso 4-M15: muchas tablas vinculadas en una consulta Caso 4-M16: Pedido de cliente con PO Consulta por transacciones de inventario Caso 4-I1: Agregar el total de existencias a la consulta Caso 4-I2: Agregar un total al final de la consulta Caso 4-I3: artículos no entregados dentro de 15 días Caso 4-I4: lista de elementos activos Caso 4-I5: Cómo encontrar detalles de inventario

105 106 107 108 109 110 111 112 114 115 117 118 120 120 121 122 122 123

Caso 4-I6: consulta sobre actualizaciones de precios Caso 4-I7: Cantidad planificada versus en stock Caso 4-I8: Agregar a la lista de órdenes de producción desde una orden de venta Caso 4-I9: Lista completa de artículos con o sin transacciones Consulta de transacciones financieras Caso 4-F1: cinco clientes principales

124 125 126 126 130 131

Caso 4-F2: pago entrante Caso 4-F3: Vinculación de un pago entrante con una factura Caso 4-F4: Listado de ambos tipos de transacciones de pago Caso 4-F5: filtrado de pagos entrantes Consulta de usuario para alerta

131 132 133 134 135

Caso 4-A1: Crear una alerta correcta sin líneas duplicadas Caso 4-A2: Alerta de factura sin documento base Caso 4-A3: Factura A / R de alerta vencida Caso 4-A4: envío especial para alertar sobre pedido de ventas Caso 4-A5: alerta de oportunidad de venta abierta Guía de alerta de consulta del usuario Ejemplos de consultas misceláneas

136 137 137 138 139 140 145

Caso 4-X1: consulta relacionada con la llamada de servicio Caso 4-X2: concatenación de dos columnas de texto Resumen

146 146 147

[ iv ]

Página 5

Tabla de contenido

Capítulo 5: Valores y aprobaciones

149

Cómo manejar valores para el uso de consultas

149

Otorgando a solo unos pocos usuarios la capacidad de crear un informe de consulta Crear consultas en diferentes categorías Grupos de consulta: una herramienta para asignar permisos de usuario Cómo usar la consulta para los procedimientos de aprobación

150 152 153 159

Crear etapas de aprobación Crear plantillas de aprobación

161 162

Autor Documentos

162 163

Etapas Condiciones

164 165

Ejemplos de consultas de usuarios para aprobación

169

Caso 1: aprobación para agregar el documento de entrega Caso 2: aprobación de pago saliente "a cuenta" Caso 3: aprobación de la factura para grupos especiales de clientes Caso 4: aprobación de un pedido de venta excesivo Caso 5: Ninguna aprobación de pago saliente en efectivo Resumen

169 170 170 171 172 173

Capítulo 6: Consulta SQL para búsqueda formateada (FMS)

175

Búsqueda formateada y valores definidos por el usuario 176 Cómo trabajar con valores definidos por el usuario 177 Buscar en valores definidos por el usuario existentes de acuerdo con las consultas guardadas 181 ¿De dónde vienen los valores $? Cómo obtener el valor que necesita de, y para, la consulta FMS ¿Se pueden ejecutar consultas FMS directamente? ¿Cuál es la función del signo negativo en la consulta FMS?

190 193 194 194

Buscar solo en valores existentes definidos por el usuario Una aplicación típica de consulta FMS: creación automática de código

195 203

Generación automática de código BP Generación automática de código de artículo Generación automática de código especial Ejemplos de consultas generales de FMS

203 204 204 205 205

Caso 1: se deben evitar las comillas dobles Caso 2: validación del valor del precio a nivel de línea Caso 3: completar un UDF de OITM en un UDF mediante cotización Caso 4: diferencia entre dos UDF en otro UDF Caso 5: mostrar el nombre del almacén junto al código de almacén Caso 6: mostrar la fecha de vencimiento del pedido de compra en el pedido de ventas Caso 7: completar automáticamente el código del centro de beneficio Caso 8: cálculo por tres campos definidos por el usuario

205 206 206 207 208 208 209 209

Caso 9: recordatorio de pedido abierto en nuevo orden

210

[v]

Página 6

Tabla de contenido

Caso 10 — Verificaciones de compromiso para almacén en stock Caso 11: Multiplicar un campo de OITM con un campo en la línea de orden

211 211

Caso 12: multiplicar dos valores UDF de dos tablas Caso 13: último precio de venta para un cliente Caso 14: invocar un valor UDF en la lista de materiales para la orden de producción Caso 15: Multiplicar un valor UDF con un valor de campo del sistema

212 212 213 213

Caso 16: eliminar las líneas duplicadas devueltas por la consulta FMS Caso 17: Obtener el código de representante de ventas asignado a un formulario de actividad Caso 18: consulta de FMS para tabla definida por el usuario (UDT) Resumen

Capítulo 7: Consulta SQL para herramientas de informes

214 214 215 216

217

Query Print Layout Designer (QPLD) y su uso

218

Impresión simple de informes de consulta Diseñador de diseño de impresión de consultas

220 222

Trabajando con un informe QPLD Crear un informe QPLD Edición de un informe QPLD

224 225 227

Guardar un informe QPLD Imprimir un informe QPLD Eliminar un informe QPLD Recreando el informe QPLD

233 234 234 235

Consulta directa para Crystal Reports (comando)

238

Trabajar con el Asistente de informes estándar

239

Crear una nueva conexión de base de datos Agregar un comando a un informe Trabajando con un comando

240 245 246

Formato básico para un informe de Crystal Resumen

251 255

Capítulo 8: Consulta SQL para un procedimiento almacenado Por qué el procedimiento almacenado se incluye en este libro Descripción general de SBO_SP_TransactionNotification Cómo trabajar con SBO_SP_TransactionNotification Algunas consultas de ejemplo para este SP Caso 1: bloqueo de un pago saliente para un BP específico Caso 2: Restricción de pagos salientes superiores a 20,000 Caso 3: bloqueo de entrada de entrada de mercancías Caso 4: bloqueo de una oferta de ventas si no hay valor en el nivel de fila UDF Caso 5: bloqueo de la factura basada en la cuenta GL y el proyecto Caso 6: bloqueo de GRPO si la cantidad es mayor que la cantidad de PO Caso 7: Bloqueo, adición o actualización de un pedido de BP duplicado # 274 Caso 8: bloqueo de documentos de ventas según fechas Caso 9: Nota de crédito de tipo A / R del servicio de validación [ vi ]

257 257 259 261 266 266 268 268 270 271 272 275 276

Página 7

Tabla de contenido

Caso 10: problema de bloqueo de bienes para ningún superusuario Caso 11: bloqueo del pedido de entrada de mercancías si no hay un pedido basado Resumen

Capítulo 9: Temas de consulta SQL más complicados

277 278 279

281

El uso de la expresión Case

281

Caso 9-C1: visualización del transtipo como código en lugar de un número Caso 9-C2: combinación de dos consultas con una expresión Case Caso 9-C3: muestra el porcentaje de descuento para cada intervalo Caso 9-C4: subtotal de artículo sabio en una entrada de mercancías Caso 9-C5: actualización de UDF con diferentes fechas Trabajando con una subconsulta

282 283 284 285 286 287

Caso 9-S1: grupos de artículos no en uso Caso 9-S2: ventas a la fecha durante dos años Caso 9-S3: verificar solo los registros similares Caso 9-S4: muestra la última fecha del documento de factura de C / P para los artículos Usando PIVOT para simplificar las consultas de estilo de tabla cruzada

288 288 289 290 291

Caso 9-P1: ventas mensuales por geografía Caso 9-P2: lista completa de todos los artículos con / sin ventas Consulta de base de datos para Excel

291 292 294

Crear una nueva fuente de datos

294

Nueva fuente de datos agregada dentro de Excel Nueva fuente de datos agregada desde el panel de control

295 298

Asistente de consulta para consulta de base de datos en Excel Ventana de Microsoft Query Evitar trampas al generar consultas

302 304 308

Crear una consulta antes de conocer la estructura de la tabla de datos Complicando la lógica en lugar de simplificarla Intentando hacer muchas cosas en una consulta Confiar solo en la ayuda de otros Resumen

308 309 309 310 310

Apéndice

311

Lista original de transacciones por código Lista original de transacciones por nombre Códigos de objeto y nombres

311 312 314

Índice

319

[ vii ]

Página 9 8

Prefacio Este libro ha sido creado para satisfacer las necesidades de muchos usuarios de SAP Business One. Si tiene la oportunidad de navegar por el sitio web de SAP Business One entre mediados de 2008 y a mediados de 2011, encontrará que mi nombre siempre está en la lista de los principales contribuyentes. yo tengo resolvió muchos problemas relacionados con consultas SQL que enfrentan muchos usuarios, y algunos de estos usuarios me ha pedido que escriba un blog o una página wiki sobre el tema. Sin embargo, el tema es demasiado grande para caber en cualquiera de esos titulares de información. Por eso nació este libro.

Inteligencia de negocios (BI) Esta es una palabra de moda hoy en día. Por lo general, solo las grandes empresas usan este término muy a menudo. Sin embargo, a partir de la definición estricta de Wikipedia, podemos entender el seguimiento: BI se refiere a técnicas informáticas utilizadas para detectar, excavar y analizar negocios datos, como ingresos por ventas por productos y / o departamentos o costos e ingresos asociados. Las tecnologías de BI proporcionan vistas históricas, actuales y predictivas de las operaciones comerciales. BI A menudo tiene como objetivo apoyar una mejor toma de decisiones empresariales. Eso significa que BI puede usarse en cualquier tipo de solución siempre que la tecnología permite el proceso de toma de decisiones comerciales de apoyo. En este libro, tu aprenderá por qué BI podría ser un complemento perfecto para SAP Business One. Por lo tanto, se beneficiará Pequeñas y medianas empresas. SQL Query es una de las herramientas más poderosas en SAP Business One que está relacionado con BI. SAP Business One generalmente se abrevia como B1 por muchos usuarios. Se podría confundir fácilmente con BI. En este libro, B1 no se usa. Los nombres completos de SAP Business One se pueden encontrar en todas partes.

Página 10

Prefacio

Lo que cubre este libro Hay dos secciones presentes en este libro.

Sección 1: SQL Query Basic La primera sección es principalmente para principiantes que tienen un conocimiento limitado de SQL Query pero quiero usar esta herramienta lo antes posible. Aprenderás herramientas básicas para comenzar a escribir. Su consulta rápidamente. Al finalizar, puede pasar a la siguiente sección para continuar tus habilidades para completar más. La Sección 1, SQL Query Basic comprende tres capítulos: El Capítulo 1 , SAP Business One Query Users and Query Basics , analiza los conceptos básicos conceptos y conocimientos necesarios para usar la consulta SQL en SAP Business One. Vas a aprenda una definición clara de consulta SQL, el diccionario de datos y enlaces de tabla. El Capítulo 2 , Generador de consultas y Asistente de consultas , presenta dos herramientas básicas para SAP Business One. El generador de consultas y el asistente de consultas le enseñarán a crear SQL consulta en SAP Business One rápidamente para hacer el trabajo. El Capítulo 3 , Administrador de consultas , ilustra la herramienta de inteligencia empresarial más importante para SAP Business One. Query Manager lo ayudará a escribir consultas libremente. Este capítulo cubre las declaraciones de consulta más utilizadas una por una. Todas las declaraciones son explicado con ejemplos concretos. A través de estos tres capítulos, obtendrá los conocimientos básicos para saltar al siguiente sección y tiene que usar la consulta SQL en más áreas. Incluso los lectores experimentados pueden encontrar algún valor al pasar por esta sección.

Sección 2: Consulta SQL en acción La segunda parte del libro saltará a un nivel superior de consultas SQL complejas. Tú aprenderá diferentes habilidades para diferentes categorías. Esta sección está más relacionada a Business Intelligence más de cerca porque puede recuperar más negocios requeridos datos en el momento adecuado por las personas adecuadas. La Sección 2, Consulta SQL en acción comprende seis capítulos: El Capítulo 4 , Ejemplos de consultas , muestra los ejemplos de consultas más utilizados. Vas a aprenda más características de consulta primero. Al mostrar ejemplos de consultas de tres usos principales categorías, podrá crear las consultas para satisfacer sus necesidades específicas. La alerta Se discuten ejemplos de consultas especialmente para aquellas situaciones importantes bajo demanda.

[2]

Página 11

Prefacio

El Capítulo 5 , Valores y aprobación , describe la seguridad de las consultas de SAP Business Uno y también la consulta asociada con los procesos de aprobación. Aprenderás a manejar la seguridad de las consultas utilizando grupos de consulta. También aprenderá la consulta del usuario para procedimientos de aprobación con ejemplos de consulta. El Capítulo 6 , Consulta SQL para Búsqueda Formateada (FMS) , enfatiza uno de los más procesos de uso frecuente y propensos a errores para crear consultas SQL para búsqueda formateada (FMS) Aprenderá todo lo necesario en la consulta FMS y el Auto asociado Actualizar funcionalidad. El Capítulo 7 , Consulta SQL para otras herramientas de informes , se centra en el uso de consultas SQL en algunos Otras herramientas de informes. Aprenderá el Diseñador de diseño de impresión de consultas, así como el SQL uso de consultas dentro de Crystal Reports. Este último se centra en el comando en la base de datos Selección experta de Crystal Reports. El Capítulo 8 , Consulta SQL para procedimiento almacenado (SP) , trata sobre uno de los casos muy especiales para uso de consultas. Aprenderá la consulta que se utiliza en un procedimiento almacenado especial: SBO_SP_TransactionNotification. Al dar una visión clara del SP, el último La sección muestra algunos ejemplos de consultas SQL del núcleo para este SP. El Capítulo 9 , Temas de consulta SQL más complicados , amplía el alcance de la consulta SQL básica a casos más complicados. Obtendrá un conocimiento profundo de las consultas para brindar más Business Intelligence en SAP Business One. Al final del libro, obtendrás Algunos buenos consejos sobre la escritura de consultas. A través de estos seis capítulos, obtendrá más conocimiento sobre la consulta SQL para SAP Business One. Si tiene preguntas específicas en mente, puede saltar al capítulo que más te atrae e ir desde allí. Cada capítulo contiene ejemplos de consultas específicas. Para facilitar la referencia en los capítulos 4 y 9, cada ejemplo se refiere al número del capítulo, junto con un código de letras que indica El tema de la consulta. Consulte la siguiente clave: Código de letra

Asunto de consulta

R

Variables

re

Función de datos

O

Flecha naranja

T

Total parcial

METRO

Documentos de marketing

yo

Transacciones de inventario

F

Transacciones financieras

UN

Alertas

X

Diverso [3]

Pagina 12

Prefacio

Código de letra

Asunto de consulta

C

Uso de expresión de caso

S

Subconsulta

PAG

PIVOTE

Lo que necesitas para este libro • Instalación de SAP Business One o sistema de prueba

• Un afán de obtener información más pertinente de su base de datos. • Una referencia de tabla del archivo de ayuda REFDB.CHM en SAP Business One SDK Centro de ayuda

para quien es este libro

Este libro está escrito para cada tipo de usuario de SAP Business One que necesita obtener información, que no está disponible en los informes estándar. La consulta SQL también es la herramienta para proporcionar soluciones y alternativas específicas a las autorizaciones de SAP Business One y procesos comerciales estándar. La audiencia de este libro incluye Consultor, Programador, administrador y muchos otros usuarios finales. De hecho, todos los negocios de SAP Un cliente podría beneficiarse de este libro. Para obtener la información correcta a la derecha el tiempo es una de las tareas más importantes para reducir el poder de SAP Business One y medianas empresas. Este es el objetivo principal del libro. Si ya ha comenzado a usar SQL Query, el libro lo ayudará a usar esta herramienta más eficientemente Si eres un principiante con un conocimiento SQL muy limitado, encontrarás El libro es fácil de seguir para resolver sus problemas de consulta SQL rápidamente. También puedes encontrar el libro es útil si no es usuario de SAP Business One, pero tiene interés en aprender Habilidades de consulta SQL. Sin embargo, para ejecutar consultas de ejemplo en el libro, SAP Business One Se requiere instalación o sistema de prueba. Hay muchos ejemplos en el libro que son "Listo para ir". Cubren muchas áreas que pueden ser similares a las que necesita.

Convenciones

En este libro, encontrará varios estilos de texto que distinguen entre diferentes tipos de información Aquí hay algunos ejemplos de estos estilos, y un explicación de su significado. Las palabras de código en el texto se muestran de la siguiente manera: "Aunque, podría vincular cualquier campos entre tablas, si el campo no es NULL , debe intentar usar el enlace clave donde sea posible." [4]

Página 13

Prefacio

Un bloque de código se establece de la siguiente manera: DECLARE @Factor como numérico (1,0) SELECCIONAR @Factor = CASO (SELECCIONE TOP 1 DispPosDeb DE OADM) CUANDO 'N' ENTONCES 1 ELSE -1 FINAL

Los nuevos términos y palabras importantes se muestran en negrita. Palabras que ves en el pantalla, en menús o cuadros de diálogo, por ejemplo, aparece en el texto de esta manera: "En Seleccione , se seleccionan tres campos de dos tablas ".

Las advertencias o notas importantes aparecen en un cuadro como este.

Consejos y trucos aparecen así.

Comentarios del lector

Los comentarios de nuestros lectores es siempre bienvenido. Háganos saber lo que piensa este libro, lo que le gustó o no le gustó. Los comentarios de los lectores son importantes para nosotros para desarrollar títulos que realmente aproveches al máximo. Para enviarnos comentarios generales, simplemente envíe un correo electrónico a [email protected] , y mencione el título del libro a través del tema de su mensaje. Si hay un libro que necesita y desea vernos publicar, envíe envíenos una nota en el formulario SUGERIR UN TÍTULO en www.packtpub.com o por correo electrónico [email protected] .

Si hay un tema en el que tiene experiencia y le interesa escribir o contribuyendo a un libro, vea nuestra guía de autor en www.packtpub.com/authors .

Atención al cliente

Ahora que eres el orgulloso propietario de un libro de Packt, tenemos varias cosas para ayudarlo a aprovechar al máximo su compra.

[5]

Página 14

Prefacio

Descargando el código de ejemplo Puede descargar los archivos de código de ejemplo para todos los libros de Packt que haya comprado desde su cuenta en http://www.PacktPub.com . Si compraste este libro en otro lugar, puede visitar http://www.PacktPub.com/support y registrarse para tener Los archivos se envían por correo electrónico directamente a usted.

Errata Aunque hemos tomado todas las precauciones para garantizar la precisión de nuestro contenido, los errores suceda Si encuentra un error en uno de nuestros libros, tal vez un error en el texto o el código, le agradeceríamos que nos informara esto. Al hacerlo, puedes salvar a otros lectores de la frustración y ayudarnos a mejorar las versiones posteriores de este libro. Si encuentra alguna errata, infórmela visitando http: //www.packtpub. com / support , seleccionando su libro, haciendo clic en el enlace del formulario de envío de erratas , y

ingresando los detalles de su errata. Una vez que se verifiquen sus erratas, su presentación será aceptado y la errata se cargará en nuestro sitio web o se agregará a cualquier lista de erratas existentes, en la sección Errata de ese título. Cualquier errata existente puede ser visto seleccionando su título en http://www.packtpub.com/support .

Piratería

La piratería de material con derechos de autor en Internet es un problema constante en todos los medios. En Packt, tomamos muy en serio la protección de nuestros derechos de autor y licencias. Si tu encontrar copias ilegales de nuestros trabajos, en cualquier forma, en Internet, por favor proporcionarnos la dirección de ubicación o el nombre del sitio web de inmediato para que podamos busca un remedio. Póngase en contacto con nosotros en [email protected] con un enlace al sospechoso material pirateado Agradecemos su ayuda para proteger a nuestros autores y nuestra capacidad para brindarle contenido valioso

Preguntas Puede contactarnos a [email protected] si tiene un problema con cualquier aspecto del libro, y haremos todo lo posible para abordarlo.

[6]

Página 15

Sección 1 SQL Query Basic Usuarios de SAP Business One Query y consultas básicas Generador de consultas y asistente de consultas Gerente de consultas

Página 17 16

SAP Business One Query Usuarios y consultas básicas Este capítulo comenzará identificando el público objetivo de este libro y luego continúe para discutir los conceptos básicos y el conocimiento necesarios para usar la consulta SQL en SAP Business One. En la primera sección, se le dará una definición clara de lo específico alcance del SQL y la consulta utilizados en este libro. La siguiente sección discute el Diccionario de datos y enlaces de tablas como tablas base frente a tablas de destino. El último La sección le brinda un concepto clave para recordar para construir una buena consulta manteniendo Es simple.

¿Quién puede beneficiarse del uso de consultas SQL? en SAP Business One? Puede que no sea fácil deducir el lector ideal de este libro. De hecho, hay muchos diferentes grupos de usuarios de SAP Business One que pueden necesitar esta herramienta. Que yo sepa, no existe un organigrama estándar para pequeñas y medianas empresas empresas. La mayoría de ellos son diferentes. A menudo puede encontrar una persona que maneja Más de un rol. En este sentido, todos los usuarios, especialmente los usuarios finales, pueden necesitar esto reservar siempre que puedan usar la consulta SQL con los conocimientos básicos necesarios. Puede consultar la siguiente lista para ver si algo se aplica a usted: • ¿Necesita verificar resultados de ventas específicos durante ciertos períodos de tiempo, para ciertas áreas o ciertos clientes? • ¿Quieres saber quiénes son los principales proveedores de ciertas ubicaciones para ciertos los materiales son?

Página 18

SAP Business One Query Users and Query Basics

• ¿Tiene una versión dinámica actualizada del desempeño de su fuerza de ventas en ¿tiempo real? • ¿Verifica a menudo si los procedimientos de aprobación coinciden exactamente ¿tus expectativas? • ¿Ha intentado comenzar a construir su consulta SQL pero no pudo obtenerla? hecho correctamente? • ¿Ha experimentado escribir consultas SQL pero los resultados no siempre son correcto o a la altura de sus expectativas? Si la respuesta a alguna de las preguntas mencionadas anteriormente es "sí", entonces puede ciertamente se benefician de leer este libro. Contestará a todas y cada una de las preguntas. mencionado anteriormente y le da el poder para resolver problemas complicados.

Consultor

Si es consultor de SAP Business One, probablemente haya dominado la consulta SQL ya. Sin embargo, si ese no es el caso, este libro sería de gran ayuda para extender Su poder de consultoría. Probablemente se convertirá en una habilidad obligatoria en el futuro que cualquier consultor de SAP Business One debería poder utilizar la consulta SQL.

Desarrollador

Si usted es un desarrollador de complementos de SAP Business One, estas habilidades serán buenas adiciones a sus capacidades Puede encontrar este libro útil incluso en algún otro desarrollo funciona como codificación o programación. Muy a menudo necesita incrustar una consulta SQL en su códigos para completar su proyecto de Kit de desarrollo de software (SDK) .

Usuario final de SAP Business One Si simplemente es un usuario final normal de SAP Business One, es posible que necesite este libro más. Esto se debe a que el uso de consultas SQL se aplica mejor a las empresas que tienen Datos en vivo de SAP Business One. Solo ustedes, como usuarios finales, saben mejor que nadie lo que está buscando para hacer de Business Intelligence un trabajo de rutina diario. Es muy Es importante que pueda crear un informe de consulta para poder asignar Su requerimiento por consulta de manera oportuna.

[ 10 ]

Página 19

Capítulo 1

Usuarios que no son de SAP Business One Para los otros lectores que no son usuarios de SAP Business One, aún podría obtener algunos Consejos y sugerencias de este libro porque el trabajo y las consultas problemáticas Se muestran ambos. Incluso sin una interfaz de usuario de SAP Business One, aún puede adquirir algunos conceptos útiles En un ejemplo de consulta de este libro, le mostraré que incluso sin los datos reales de mi base de datos para probar la consulta debido a la localización limitación, la respuesta correcta al interlocutor todavía se puede deducir. No importa cuál sea su experiencia, encontrará este libro útil siempre que necesita obtener ciertos datos de forma rápida y precisa.

Consulta SQL y términos relacionados Antes de entrar en los detalles de la consulta SQL, me gustaría presentar brevemente algunos conceptos básicos de la base de datos porque SQL es un lenguaje de base de datos para administrar datos en Sistemas de gestión de bases de datos relacionales ( RDBMS ).

RDBMS RDBMS es un sistema de gestión de bases de datos que se basa en el modelo de relación. Relacional aquí es una palabra clave para RDBMS. Encontrará que los datos se almacenan en el forma de tablas y la relación entre los datos también se almacena en forma de tablas para RDBMS.

Mesa

La tabla es un componente clave dentro de una base de datos. Una mesa o un grupo de mesas representan un tipo de datos. Por ejemplo, la tabla OSLP dentro de SAP Business One contiene todos los datos del empleado de ventas. Las tablas son lugares de almacenamiento de datos bidimensionales titulares. Debe estar familiarizado con su uso y sus relaciones con El uno al otro. Si está familiarizado con Microsoft Excel, la hoja de trabajo en Excel es un tipo de mesa bidimensional.

[ 11 ]

Página 20

SAP Business One Query Users and Query Basics

La tabla también es uno de los conceptos más utilizados en el libro. Relaciones entre cada tabla puede ser más importante que las tablas mismas porque sin relación, nada puede ser de ningún valor. Una función importante dentro de SAP Business One es permitiendo tabla definida por el usuario ( UDT ). Todos los UDT comienzan con "@" .

Campo Un campo es la unidad más baja que contiene datos dentro de una tabla. Una tabla puede tener muchos campos. Eso También se llama columna. El campo y la columna son intercambiables. Una mesa está compuesta de registros, y todos los registros tienen la misma estructura con campos específicos. Uno El concepto importante en SAP Business One es el campo definido por el usuario (UDF). Todos los UDF Comience con U_ .

SQL SQL a menudo se conoce como lenguaje de consulta estructurado. Se pronuncia como SQL o como la palabra "Sequel". Hay muchas revisiones y extensiones diferentes de SQL. La revisión actual es SQL: 2008, y la primera revisión importante es SQL-92. La mayor parte de SQL Las extensiones se crean sobre SQL-92. Este libro tiene un alcance muy específico para los términos "SQL" y "consulta". Por favor lee a través de esta sección cuidadosamente primero si encuentra que el alcance del libro no es correcto para sus necesidades

[ 12 ]

Página 21

Capítulo 1

T-SQL Tenemos que limitar el alcance del término SQL en este libro. En primer lugar, desde SAP Business One se basa en la base de datos de Microsoft SQL Server, SQL aquí significa TransactSQL o T-SQL en breve. Es una extensión de significado general de Microsoft / Sybase para SQL Debido a que solo usamos T-SQL en todo el libro, SQL en este libro significará T-SQL a menos que se mencione claramente lo contrario.

Subconjuntos de SQL Hay tres subconjuntos principales del lenguaje SQL: • Lenguaje de control de datos (DCL) • Lenguaje de definición de datos (DDL) • Lenguaje de manipulación de datos (DML) Cada conjunto de lenguaje SQL tiene un propósito especial: • DCL se utiliza para controlar el acceso a los datos en una base de datos, como para otorgar o revocar derechos de usuarios especificados para realizar tareas especificadas. • DDL se utiliza para definir estructuras de datos, como crear , alterar o descartar tablas. • DML se utiliza para recuperar y manipular datos en la tabla, como para insertar , eliminar y actualizar datos. Seleccionar , sin embargo, se convierte en una declaración especial perteneciente a este subconjunto a pesar de que es un comando de solo lectura que no manipular datos en absoluto.

Consulta La consulta es la operación más común en SQL. Podría referirse a los tres subconjuntos SQL. En este libro, sin embargo, solo aprenderá la parte de solo lectura de la consulta. No agregar , Eliminar o actualizar la instrucción SQL en DML se discutirá en el libro ya que es prohibido de la política de soporte de SAP para la integridad de la base de datos de SAP Business One. Todos DCL o DDL SQL tampoco se incluirán porque tampoco controlamos el acceso a los datos en una base de datos, ni definir la estructura de datos para una base de datos. Encontrará SELECT líder consulta solo dentro del libro. La consulta de solo lectura SELECT tiene una potente funcionalidad para Encontrar información útil para satisfacer sus necesidades específicas. Debe comprender los riesgos de ejecutar cualquier complemento, Eliminar o actualizar consultas que podrían alterar potencialmente el sistema tablas incluso si son campos definidos por el usuario. Solo SELECCIONAR la consulta es legítima para la tabla del sistema SAP Business One.

[ 13 ]

Página 22

SAP Business One Query Users and Query Basics

Diccionario de datos

Para crear consultas SQL que funcionen, no solo necesita saber cómo escribir pero también debe tener una visión clara sobre la relación entre tablas y donde encontrar la información requerida. Como saben, SAP Business One se basa en Microsoft SQL Server. El diccionario de datos es una gran herramienta para crear consultas SQL. antes de Comenzamos, un buen Diccionario de Datos es esencial para la base de datos. Afortunadamente, hay es una muy buena referencia llamada SAP Business One Database Tables Reference fácilmente disponible a través del Centro de ayuda de SAP Business One SDK. Puede encontrar los detalles en el sección siguiente.

SAP Business One: tablas de base de datos referencia

El archivo de referencia de tablas de la base de datos llamado REFDB.CHM es el que estamos buscando. El SDK generalmente se instala en el mismo servidor que el servidor de base de datos SAP Business One. Normalmente, la ruta del archivo es: X: \ Archivos de programa \ SAP \ SAP Business One SDK \ Ayuda . Aquí, "X" significa la unidad donde está instalado su SAP Business One SDK. La ayuda el archivo se ve así:

[ 14 ]

Página 23

Capítulo 1

En este archivo de ayuda, encontraremos las mismas categorías que el menú SAP Business One con todos los 11 módulos. Las tablas relacionadas con cada módulo se enumeran una por una. Allí son estructuras de árbol en el archivo de ayuda si las tablas de encabezado tienen tablas de filas. Cada mesa proporciona una lista de todos los campos de la tabla junto con su descripción, tipo, tamaño, tablas relacionadas, valor predeterminado y restricciones.

Convención de nomenclatura de tablas para SAP Business One Para ayudarlo a comprender rápidamente el diccionario de datos mencionado anteriormente, estaremos repasando las convenciones de nomenclatura para la tabla en SAP Business One.

Palabras de tres letras

La mayoría de las tablas para SAP Business One tienen cuatro letras. Las únicas excepciones son númerotablas finales, si los números son mayores que nueve. Esas tablas tendrán cinco letras. Para comprender fácilmente los nombres de las tablas, hay una abreviatura de tres letras en SAP Business Uno. Algunas de las abreviaturas de uso común se enumeran a continuación: • ADM: Administración • ATC: archivos adjuntos • RCP: personas de contacto • CRD: socios comerciales • DLN: Notas de entrega • HEM: empleados • INV: Facturas de ventas • ITM: artículos • ITT: Árboles de productos (Lista de materiales) • OPR: oportunidades de ventas • PCH: Facturas de compra • PDN: PO de entrada de mercancías • POR: Órdenes de compra • QUT: Cotizaciones de ventas • RDR: órdenes de venta • RIN: Notas de crédito de ventas • RPC: compra de notas de crédito

[ 15 ]

Página 24

SAP Business One Query Users and Query Basics

• SLP: Empleados de ventas • USR: usuarios • WOR: Órdenes de producción • WTR: transferencias de stock

Tablas "O" Todas las tablas que comienzan con "O" se refieren a tablas maestras . O aquí representa Objeto . Por ejemplo: • OITM: Maestro de artículos • OCRD: Master de Socios Comerciales • OSLP: Empleado de ventas

Tablas "A"

La mayoría de las tablas que comienzan con "A" pueden significar tablas de registro históricas. A aquí representa Archivo . Por ejemplo: • AITM: elementos: historial • ACRD: Socios comerciales: historia • AUSR: usuarios de archivo: historial

Tablas de encabezado de documentos

Estas son tablas O especiales con exactamente la misma estructura. Pueden ser tablas relacionadas con Venta o compra. Estos se denominan documentos de marketing. Estos también incluyen la mayoría Tablas de transacciones de inventario. Algunos ejemplos son: • OINV: Encabezado de factura A / R • OPCH: Encabezado de factura A / P • OIGN: Encabezado de entrada de mercancías

Tablas de líneas de documentos

Todas las tablas que terminan con un número se refieren a tablas de detalles de líneas de documentos o subtablas para la mesa maestra. Los números aquí podrían referirse a diferentes propiedades de Tablas de encabezado. • INV1: Fila de facturas A / R • PCH1: Fila de facturas A / P [ 16 ]

Página 25

Capítulo 1

• IGN1: fila de entrada de mercancías • INV2: Factura A / R: gasto de fila

Ejemplos importantes de tablas

Aquí se enumeran algunas tablas específicas muy importantes para la creación de consultas: • Entrada de diario OJDT : esta tabla incluye todas las entradas de diario financiero no importa si se publican automáticamente o se publican manualmente. • OINM-Warehouse Journal : esta tabla incluye todos los inventarios relacionados actas. Es un punto único para verificar todo en relación con su inventario (o stock). Se convierte en una vista en la nueva versión. Esta vista debe ser preguntó con mucho cuidado. • Historial de documentos ADOC : esta tabla incluye todo el historial de documentos. Sin embargo, se nombra incorrectamente en la documentación, tabla "Historial de facturas" en el archivo de ayuda.

Enlaces de tabla: la clave para la consulta correcta Los enlaces de tabla son fundamentales para la construcción de consultas. Verá algunos enlaces diferentes en esta sección, pero los enlaces más comunes se discutirán en la siguiente sección porque hay demasiados y se usan con demasiada frecuencia. Para comprender los enlaces de tabla, necesita saber más sobre las estructuras de tabla.

Clave primaria

Cada tabla tiene una clave primaria. Algunas de las tablas también tienen claves foráneas. Todos aquellos Las claves se utilizan para el índice. Docentry es una clave primaria típica para vincular OXXX con XXXn tablas de documentos. Por ejemplo, Docentry es un campo clave común para vincular OPOR con POR1, POR2 a POR12. Una clave principal puede ser uno o más campos. Para una tabla simple, un campo clave sería suficientemente bueno. Para una tabla complicada, dos o más campos para la clave primaria no son raros. Una clave primaria debe ser única dentro de la misma tabla. Esta clave no permitirá NULL valor, es decir, un campo vacío o un campo sin datos.

[ 17 ]

Page 26

SAP Business One Query Users and Query Basics

Clave externa Una clave externa se usa generalmente para vincular a la clave principal de otra tabla. Este campo se actualizará siempre que el otro registro de la tabla haya cambiado. Aunque, podría vincular cualquier campo entre tablas, si el campo no es NULL , debe intentar utilizar el enlace clave siempre que sea posible para aumentar el Rendimiento de la base de datos.

Ejemplo de enlaces de tabla en SAP Business Uno Para ser más claro sobre el enlace, aquí hay algunos ejemplos de enlaces de tabla: • Tabla de artículos OITM y tabla de precios de artículos ITM1 : Estas dos tablas están vinculadas a través del campo ItemCode. Ambas mesas tienen el mismo nombre de campo para vincular. No son relaciones uno a uno sino uno a muchos. Un Código de artículo en el maestro de artículos puede tener más de un precio de artículo asociado. • Tabla de árbol de producto OITT y elementos secundarios de árbol de producto ITT1 : Estas dos tablas están vinculadas a través del campo Código en OITT y el campo Padre en ITT1. Estas tablas se utilizan para la Lista de materiales. • Tabla OCRD-Business Partner y tabla OSLP-Sales Employee : Estas dos tablas están vinculadas a través del mismo campo de nombre SlpCode. En el segunda tabla, SlpCode es la clave principal para OSLP. Por otro lado, es un clave foránea en la primera tabla OCRD.

Tablas base versus tablas objetivo Las tablas base y las tablas de destino son tablas vinculadas especiales dentro de SAP Business One. También son las tablas vinculadas más utilizadas para consultas SQL. Puede encontrar la mayoría de ellos relacionados con los documentos "Ventas-A / R" y "Compra-A / P" o los llamados "documentos de marketing".

[ 18 ]

Página 27

Capítulo 1

Los documentos de marketing pueden no tener tablas base o tablas de destino. Del anterior captura de pantalla, puede encontrar claramente que el Documento base y el Documento de destino son disponible para esta orden de venta. Para obtener el Documento base, puede hacer clic en "izquierda ícono de flecha "o use la tecla de acceso directo Ctrl + N. Para obtener el Documento de destino, puede haga clic en el "icono de flecha derecha" o utilice la tecla de acceso directo Ctrl + T . Solo cuando la base tabla o tabla de destino está disponible para el documento actual, ¿encontrará los elementos del menú? e iconos en estado activo. De lo contrario, tanto los íconos como los elementos del menú aparecen atenuados. A partir de los términos "Base" y "Destino", está claro que la tabla de destino puede basarse en La mesa base.

[ 19 ]

Página 28

SAP Business One Query Users and Query Basics

Una tabla podría basarse en diferentes tipos de tablas:

De esta demostración, podría obtener una idea clara de la relación entre Documento base (tabla) y Documento de destino (tabla). Un par específico de orden de compra y las tablas de PO de buen recibo se muestran aquí. Este concepto se aplica a todo tipo de documento. mesas. Aquí hay una lista de pares de base-destino comúnmente utilizados; No son inclusivos. Tú puede encontrar más, pero los siguientes son los más utilizados: Mesa base

Tabla de destino

OQUT — Cotización de ventas

ORDR: pedido de cliente

OQUT — Cotización de ventas

ODLN — Entrega

OQUT — Cotización de ventas

OINV — Factura A / R

ORDR: pedido de cliente

ODLN — Entrega

ORDR: pedido de cliente

OINV — Factura A / R

ODLN — Entrega

ORDN — Devoluciones

ODLN — Entrega

OINV — Factura A / R

ORDN — Devoluciones

ORIN — Nota de crédito A / R

ODLN — Factura A / R

ORIN — Nota de crédito A / R

OPOR: orden de compra

OPDN: PO de entrada de mercancías

OPOR: orden de compra

OPCH — Factura A / P

OPDN: PO de entrada de mercancías

ORPD: devolución de mercancías

OPDN: PO de entrada de mercancías

OPCH — Factura A / P

ORPD: devolución de mercancías

ORPC: Nota de crédito A / P

OPCH — Factura A / P

ORPC: Nota de crédito A / P [ 20 ]

Página 29

Capítulo 1

He omitido los detalles del enlace. En realidad, encontrarás que todos los enlaces existen en la primera tabla secundaria o la llamada tabla de filas para la tabla de encabezado, como QUT1 en lugar de OQUT. Los campos de enlace son muy claros. Por ejemplo: • BaseEntry en la tabla de destino se refiere a DocEntry de la tabla base • BaseType se refiere a los tipos de la tabla base • BaseRef generalmente está vinculado al campo DocNum en la tabla base • BaseLine será el número de línea en la tabla de línea base • TargetEntry en la tabla base se refiere a DocEntry de la tabla de destino • TargetType se refiere a los tipos de la tabla de destino

Manteniéndolo simple: la clave para construir un buena consulta Antes de pasar al siguiente capítulo, se debe tener en cuenta un concepto importante:

Que sea simple es la clave para éxito para crear consultas de manera eficiente.

La simplicidad es necesaria en todas partes en el mundo cambiante actual. Donde quiera que complica las cosas, puede que te encuentres en una posición incómoda para competir con otros. Mi eslogan es: simple, más simple, el más simple. Tengo un hábito en la construcción de consultas: el último paso para cualquier consulta nueva sería verificar mira si es el más simple. De esta manera, "mantenerlo simple" no solo se mantendría en el consulta ya construida, pero también ayuda a que las consultas nuevas sean las más simples al principio. Al mantener una consulta lo más simple posible, se asegurará de que el rendimiento del sistema No es afectado. También será de gran ayuda para el proceso de solución de problemas. Un corto La lista de verificación para simplificar es la siguiente: • Otras consultas : ¿hay alguna otra consulta que haga un trabajo similar y, en caso afirmativo, ¿Por qué es necesario construir la nueva consulta? • Tablas : ¿hay alguna tabla que no se haya utilizado para la consulta? • Campos : ¿hay campos que no se hayan utilizado para la consulta? • Condiciones : ¿hay alguna superposición de condiciones? [ 21 ]

Página 30

SAP Business One Query Users and Query Basics

La lista puede ser mucho más larga. El significado detrás de esto es claro: hay un interminable batalla para deshacerse de las complicaciones. Cuando pruebe este método y se convierta en una rutina, encontrará esa consulta La construcción se convierte en un proceso agradable.

Resumen

En este capítulo, ha sido identificado como un lector apropiado que necesita esto información, suponiendo que leas el capítulo inicial y aún quieras para leer más Se le han dado todos los conceptos básicos como RDBMS, Tabla, SQL, T-SQL, SQL Subconjuntos y Consulta. También tienes la idea de cuáles son los significados estrictos de "SQL" y "Consulta" están dentro de este libro. Al profundizar en la discusión de las relaciones de mesa, obtuviste una imagen más amplia de Estructura de la base de datos de SAP Business One y convenciones de nomenclatura de tablas. Tu también Aprendí sobre tablas base versus tablas de destino en SAP Business One. El principio "Keep it simple" se ha enfatizado en la última sección del capítulo. Se recomienda usarlo siempre que practique sus propias consultas. El próximo capítulo le presentará las herramientas Generador de consultas y Asistente de consultas, para que pueda comenzar a crear consultas SQL lo antes posible, si tiene Todavía no lo he hecho.

[ 22 ]

Page 31

Generador de consultas y Asistente para consultas En el capítulo anterior, aprendiste conceptos básicos sobre ciertas terminologías. utilizado en este libro. También sabe que las tablas y las relaciones entre tablas son importantes para Consulta SQL ¿De qué se tratan todos estos conceptos? Entenderás mejor cuándo comienza su propio viaje para crear un informe de consulta SQL. ¿Como empiezo? Esa es una buena pregunta. He aprendido esto por las malas. Incluso aunque las herramientas para crear consultas están disponibles en SAP Business One menú, yo nunca había revisado la mayoría de estas herramientas antes de planear escribir este libro para mostrar que la forma de comenzar. He seguido usando solo el Administrador de consultas y consultas del sistema hasta ahora. Para ser honesto, descubrí demasiado tarde que tomé tal ruta más larga de lo necesario. ¡Qué pena! Si hubiera comenzado con Query Generator y Asistente de consulta, me habría ahorrado una gran cantidad de tiempo. Estas herramientas son bastante convenientes y eficientes para que todos las usen, especialmente para principiantes para escribir su primera consulta para SAP Business One. Estas herramientas te ayudarán a omitir esto proceso de escribir cada declaración individual, nombres de tablas o nombres de campos, etc. Este capítulo le presentará dos herramientas básicas de consulta SQL para SAP Business One: • generador de consultas • Asistente de consulta Ambas herramientas son para que los principiantes conozcan rápidamente las consultas SQL en SAP Business One. Después de haber aprendido estas herramientas, puede hacer un trabajo simple con solo unas pocas clics del mouse. La introducción le brinda pasos detallados para que pueda tener paso a paso Consejo de paso.

Page 32

Generador de consultas y asistente de consultas

En la siguiente sección, se discuten las diferencias entre estas herramientas. Usted puede decida cuál es más adecuado para usted cuando lea todas sus funciones y beneficios. Si eres un lector más experimentado, es posible que estas herramientas ya no sean necesario. De lo contrario, se recomienda encarecidamente pasar por el capítulo para Refrescate. La última sección presenta las consultas del sistema integradas en SAP Business One. Aquellos las consultas del sistema son otro buen comienzo para los lectores cuyo nivel de consulta SQL está por encima promedio. Luego puede personalizarlos y crear sus propias consultas rápidamente. Para principiante, puede aprender cómo ejecutar esas consultas primero para ahorrar tiempo.

La captura de pantalla anterior muestra cómo acceder a estas diferentes herramientas desde SAP Menú Business One. Puede encontrar todas las herramientas para este capítulo en él. Todos estan bajo las herramientas | Menú de consultas . El generador de consultas es el segundo elemento; El asistente de consultas es el tercer elemento; y System Queries es el número cinco.

Generador de consultas Query Generator es la primera herramienta en ser discutida. Se encuentra sobre la consulta. Asistente en la lista de elementos del menú.

Descripción general del generador de consultas El generador de consultas le permite crear consultas utilizando el motor de consultas SQL. Como todo otras herramientas, está diseñado solo para recuperación / selección de datos. No puedes hacer nada Consultas DML como actualizaciones, insertar o eliminar. Se puede acceder a este elemento del menú desde Herramientas | Consultas | Generador de consultas . [ 24 ]

Page 33

Capitulo 2

Con esta herramienta, puede: • Cree muchas consultas usted mismo a partir de un conjunto fijo de tablas en SAP Business One • Acceda a todos los datos en esas tablas y evalúelo según sus necesidades. • Cree informes individuales sin escribir declaraciones de consulta individuales

Parte izquierda del formulario Generador de consultas Después de hacer clic en el elemento del menú Generador de consultas, puede abrir el menú principal forma de generador de consultas. En la parte superior izquierda del formulario, puede encontrar una celda amarilla. Haga clic en la tecla Tabulador de la celda vacía para que aparezca Elegir de la lista , como en el siguiente captura de pantalla:

Aparecerá el conjunto de nombres de tabla. Puede escribir cualquier letra en Buscar cuadro de texto para que aparezcan los nombres de las tablas que comienzan con esa letra. En el ejemplo, tu podría encontrar que la letra O está escrita. Todas las tablas que comienzan con O estarían en la parte superior lista. Puede usar la barra de desplazamiento o la tecla de subir / bajar página para encontrar las tablas que estan buscando. Recordará que discutimos la importancia del Diccionario de datos en el anterior capítulo. Sin el diccionario, podría ser demasiado difícil encontrar qué tablas necesita. Si Referencias de tabla está disponible, puede encontrar fácilmente la tabla rápidamente. Por el bien de vez, si ya conoce los nombres, puede escribir los nombres completos de la tabla.

[ 25 ]

34

Generador de consultas y asistente de consultas

Sin embargo, aquí sigue siendo uno de los mejores lugares para encontrar todas las tablas de uso común para SAP Business One en caso de que el archivo de ayuda no esté disponible para usted. En este caso menos que ideal, probablemente necesite revisar la lista de nombres de la tabla varias veces para convertirse familiarizado con ellos. En la captura de pantalla de ejemplo, la tabla resaltada OCRD (tabla de socios comerciales) ser seleccionado al hacer clic en Elegir :

Puede seleccionar más de una tabla de la misma manera que se seleccionó la primera tabla. En la captura de pantalla que acaba de ver, también se seleccionó la OCST (tabla de estado). Si tu seleccionó la tabla incorrecta o cambió de opinión, el botón X aquí podría usarse para eliminarlo de la lista.

Partes media y derecha del formulario Generador de consultas Después de la selección de la tabla, verá que la parte central del formulario muestra los campos de la tabla resaltada en orden alfabético. La parte correcta muestra el componente de consulta cuando hace doble clic en el nombre del campo o de la tabla en un caja adecuada En Seleccionar , se seleccionan tres campos de dos tablas. Bajo Desde , los nombres de las tablas se muestran automáticamente con los alias predeterminados T0 y T1 . El enlace predeterminado por sistema También se muestra. Si el enlace no es correcto, puede arreglarlo manualmente. En Dónde , puede elegir cualquier campo para restringir el resultado de la consulta. Aquí, T1. [Nombre] ha sido seleccionado con el fin de acercar al socio comercial de acuerdo con el Nombres de estado / provincia.

[ 26 ]

Página 35

Capitulo 2

Puede notar que se muestra un formulario adicional. Esto aparece después de hacer clic en el botón Condiciones Puede encontrar 12 fórmulas condicionales del formulario que pueden ser seleccionado La parte Variables le permite seleccionar variables como [% 0], [% 1], etc. El signo de porcentaje más un número representa una variable para la consulta SQL en SAP Business Uno. Puede permitir al usuario seleccionar o ingresar valores durante la ejecución de la consulta. En el último ejemplo, la variable Comenzar con fórmula más [% 0] da el resultado como T1. [Nombre] como '[% 0]' . El % adicional a la derecha de [% 0] es una entrada manual carácter comodín que se puede usar como sufijo para que coincida con cualquier cadena de cero o más caracteres. En Ordenar , T1. [Nombre] también se selecciona para permitir que los resultados de la consulta se ordenen por Estado / Nombres de provincia.

Ejecutar una consulta desde el formulario Generador de consultas Cuando se haya seleccionado toda la información requerida, haga clic en Ejecutar . Entonces el siguiente formulario Consulta: aparecerán criterios de selección para que ingrese las letras:

En el ejemplo anterior, se ingresó una letra Y. Eso significa que obtendrás el resultado de la consulta con todos los códigos y nombres de socios comerciales del estado / provincia con el nombre comienza con Y.

[ 27 ]

Page 36

Generador de consultas y asistente de consultas

El resultado se parece a la siguiente captura de pantalla:

Si esta consulta podría usarse más tarde, haga clic en Guardar cerca de la parte inferior para guardar su consulta. Puede guardarse en cualquier categoría de consulta con el nombre que ingresó. los El tema sobre el guardado de consultas se discutirá en detalle más adelante. El botón Reverse Table en la parte inferior del formulario se usa para ayudarlo a elegir para mostrar la tabla de derecha a izquierda o de izquierda a derecha. Esto es porque a diferencia del inglés, algunos de los otros idiomas pueden no comenzar de izquierda a derecha, sino en orden inverso. Puede notar que todas las secuencias de comandos de consulta de diferentes secciones del generador han sido unidos por ti. Recuerde, no necesita escribir una sola declaración. Este es un buen regalo para que pueda reducir su curva de aprendizaje en términos de consulta aprendizaje. ¡No desperdicies este valioso recurso!

Asistente para consultas El asistente de consultas es la segunda herramienta que se discutirá en este capítulo. Es similar a la consulta Generador. Vamos a comparar ambas herramientas más adelante en el libro.

Descripción general del Asistente de consultas El Asistente de consulta permite un fácil acceso a la base de datos y una forma fácil de crear usuarios informes definidos Está diseñado solo para recuperación / selección de datos. No eres capaz para hacer consultas DML como actualizaciones, insertar o eliminar. Este elemento del menú puede ser accedido desde Herramientas | Consultas | Asistente de consultas .

[ 28 ]

Page 37

Capitulo 2

Con esta herramienta, puede hacer lo siguiente: • Cree consultas a través de cinco pasos a partir de un conjunto fijo de tablas de SAP Business One • Acceda a todos los datos en esas tablas y obtenga ayuda de consejos sobre cada paso • Cree informes individuales sin escribir declaraciones de consulta individuales

Paso 1: pantalla de bienvenida

El primer paso es muy simple. Después de hacer clic en el elemento del menú Asistente de consulta , obtén la primera pantalla que simplemente te dice: Este asistente te guiará paso a paso. paso a través de la definición de parámetros necesarios para una consulta. La captura de pantalla se omite aquí ya que no es más que una pantalla de bienvenida para que sepas que eres comenzando este mago.

Paso 2: seleccione tablas para el informe El segundo paso es similar a la parte izquierda en el generador de consultas. Puedes seleccionar tantos Tablas como necesites. Sin embargo, debe intentar minimizar el número de tablas para rendimiento del sistema y eficiencia de consultas.

[ 29 ]

38

Generador de consultas y asistente de consultas

En la captura de pantalla de ejemplo que acaba de ver, puede encontrar que la primera tabla seleccionada es OCRD (tabla de socios comerciales). La segunda tabla seleccionada es OSLP (Empleado de ventas mesa). Cada tabla seleccionada se coloca en una fila separada en la ventana. El segundo La columna muestra la descripción completa de las tablas. Una cosa aquí es que es notablemente mejor que el generador de consultas. Cuando seleccionas cualquier tabla, muestra automáticamente todas las tablas vinculadas debajo de la parte inferior del formar. Entonces le resultará muy conveniente elegir las tablas necesarias por haciendo doble clic Este proceso se aplica a todas las tablas seleccionadas en la parte superior del formulario. Una lista de tablas vinculadas en la parte inferior del formulario cambia cuando resalta diferentes tablas de la parte superior del formulario.

Paso 3: seleccione los campos y ordene los pedidos El paso 3 en el Asistente de consulta tiene la misma función que la parte central de la consulta Generador. Además, tiene más opciones para seleccionar campos.

[ 30 ]

Página 39

Capitulo 2

Puede tabular la columna Campo para que aparezca Seleccionar de la lista de la seleccionada mesa. Es como cuando seleccionó tablas de ambas herramientas. Puede escribir cualquier letras en el cuadro de texto Buscar para buscar los campos solicitados. Hay dos columnas en la lista: • Nombre: nombres de campo • Descripción: descripción del campo Como sabrá en el capítulo anterior, puede obtener toda la información de campo del Referencias de tabla para SAP Business One por adelantado. Si eso no está disponible para usted, esto podría ser el segundo mejor lugar para encontrar toda la información de campo comúnmente utilizada. Vas a probablemente necesites revisarlos muchas veces antes de que puedas alcanzar campos a gusto. Puede escribir la letra C para que los nombres de campo que comienzan con C se encuentren en la parte superior de la lista, por lo que que puedes llegar a los campos más rápido. O puede que no necesite escribir ninguna letra. Sólo use el mouse o la página hacia abajo para navegar por la lista y familiarizarse con esos campos

[ 31 ]

Page 40

Generador de consultas y asistente de consultas

De la captura de pantalla anterior, puede ver que dos campos en OCRD han sido seleccionado. También se ha seleccionado otro campo de OSLP. Son: • CardCode: Código de socio comercial de OCRD • CardName: nombre del socio comercial de OCRD • SlpName: nombre del empleado de ventas de OSLP La tercera columna, Título , muestra la descripción del campo de forma predeterminada. Tu puedes cambiar ellos a cualquier cosa que desee, como el nombre del cliente en lugar del nombre de BP. Va a se muestra en la parte superior de la pantalla de resultados de la consulta como un encabezado de columna. La cuarta columna, Orden de clasificación , utiliza un número entero (1, 2, 3) para establecer la prioridad de clasificación; tú puede asignar cualquier orden al campo que ha seleccionado. La quinta columna define el tipo de clasificación como Ascendente o Descendente . La sexta columna le permite configurar el grupo en cualquier campo que desee agregar. Solo necesita seleccionar Y para el campo en el que desea que se agrupe. Si usted tiene no seleccionado esto, el valor por defecto sería N . La última columna en la captura de pantalla anterior, Comp. , ofrece seis opciones de cálculo: • Registros totales : muestra el número de registros recuperados • Total de registros distintos : muestra el número de registros distintos recuperados • Cantidad : muestra la suma de los valores para el campo numérico en el registros recuperados • Promedio : calcula y muestra el promedio de los valores de ese campo en el registros recuperados • Mínimo : muestra el valor más pequeño de este campo desde registros recuperados • Máximo: muestra el valor más grande de este campo desde registros recuperados

Paso 4: condiciones y relaciones

El paso 4 es para definir las condiciones y relaciones para recuperar datos. Ambas condiciones y las relaciones se basan en la estructura y lógica de la base de datos.

[ 32 ]

Page 41

Capitulo 2

Para el ejemplo anterior, es la pestaña Condiciones de visualización . Puedes ver la condición ingresado es el nombre del Empleado de ventas , que contiene Gordon . Puede seleccionar ( o ) en estas dos pestañas para definir la secuencia de prioridad de condiciones También puede seleccionar Y / O para definir condiciones complejas.

[ 33 ]

Page 42

Generador de consultas y asistente de consultas

La otra pestaña es para Relaciones de visualización . Debajo de esta pestaña, encontrará las primeras columnas casilla de verificación, Ejecutar . Aplica la relación definida entre las tablas que aparecer en la fila. Cuando esta casilla de verificación está seleccionada, SAP Business One agrega otra condición. Esto significa que los registros que desea recuperar deben cumplir con el condiciones definidas en la pestaña Definir condiciones y con la condición agregada.

Paso 5: completar el asistente de consultas Cuando complete todas las Condiciones y Relaciones, al hacer clic en Siguiente lo llevará a el paso final, que le muestra el script de consulta creado por el sistema que aplica Todas tus selecciones.

Puede revisar la consulta para verificar si es todo lo que necesita. Si encuentra que no incluya todas las condiciones, puede volver a editar algunas de ellas en los pasos anteriores. En el caso de ejemplo, no hay problemas. Haga clic en Finalizar para que aparezca la consulta. ventana de resultados.

[ 34 ]

Page 43

Capitulo 2

Puede encontrar todos los códigos y nombres de socios comerciales en el estado / Provincia de los resultados de la consulta. Al igual que el Generador de consultas, si la consulta es útil, puede hacer clic en Guardar para guardar su consulta. El tema sobre el guardado de consultas se discutirá en Crear y guardar usuarios sección de consultas del próximo capítulo.

Hay un video tutorial disponible para el Asistente de consultas por SAVIA. Puede encontrarlo aquí: http://www.youtube.com/ ver? v = xaLO_4JnG-E. De este video, tendrás más información en una instrucción tipo aula para el tema aquí.

¿Cuál es la diferencia entre Query? Asistente de generador y consulta?

Cuando pases por la primera parte del capítulo, probablemente ya tengas notó las diferencias entre las herramientas Generador de consultas y Asistente para consultas. Encontrará las diferencias resumidas aquí para aclarar cualquier duda que pueda tener. La primera herramienta, Query Generator, es una de las herramientas más simples para construir consultas SQL. Simplemente utiliza una interfaz de usuario (UI) simple para que pueda crear consultas. Puedes empezar a úselo cuando desee encontrar las tablas, los campos y su relación. La segunda herramienta, Query Wizard, ha agregado cinco pasos con diferentes formas, de modo que Puede encontrar la relación de tabla más fácilmente. Además, encontrar campos se vuelve mucho más fácil porque tiene la opción de mostrar Elegir de la lista .

[ 35 ]

Page 44

Generador de consultas y asistente de consultas

Aquí está la lista que muestra las similitudes entre estas dos herramientas: • No hay necesidad de escribir declaraciones de consulta completas • Permite la selección de tablas y campos. • Prohíbe actualizar datos • Utiliza el mouse solo para agregar condiciones Las diferencias

Generador de consultas

Asistente para consultas

Interfaz de usuario de un solo paso

Interfaz de usuario de múltiples pasos

Requiere más tablas y campos conocimiento

Requiere menos tablas y campos conocimiento

Menos ayuda y consejos

Más ayuda y consejos

La selección del nivel de campo es básica

La selección del nivel de campo es sofisticado

En resumen, el generador de consultas está diseñado para obtener resultados rápidos en un solo paso, mientras Query Wizard está más concentrado en instrucciones paso a paso para ayudarlo Construir consultas con una selección clara en diferentes fases. Prefiero el Generador de consultas sobre el Asistente de consultas porque estoy familiarizado con todos tablas y campos. Para mí, cuanto más simples son los pasos, mejor. Para un principiante, lo haría le sugiero que pruebe el Asistente de consultas porque le da más poder para seleccionar diferentes tablas y campos. También se le proporcionarán más consejos útiles.

Beneficiarse de las consultas integradas del sistema Además del generador de consultas y el asistente de consultas, existe otra herramienta poderosa para usted aprender SQL Query en SAP Business One. Es decir: consultas del sistema. Las consultas del sistema le permiten generar informes adicionales y recuperar datos que no son disponible ejecutando los otros informes. Puede acceder a las consultas del sistema desde los diferentes módulos: en cada módulo las consultas relevantes del sistema aparecen como entradas en el menú del informe, identificado con el ícono ? o eligiéndolos desde Herramientas | Consultas | Consultas del sistema .

[ 36 ]

Página 45

Capitulo 2

La siguiente captura de pantalla muestra los nombres de todas las consultas del sistema disponibles. Puede encontrar hasta 18 consultas aquí. La mayoría de las consultas están relacionadas con las finanzas. y banca.

Puede ver los resultados de la consulta de una de las consultas del sistema: Orden de compra Vinculado al depósito . El resultado está realmente vacío porque no hay depósito vinculado a la orden de compra en la base de datos Te gustaría este resultado, ¿no?

[ 37 ]

Página 46

Generador de consultas y asistente de consultas

Lo que necesita saber es la parte superior del resultado de la consulta. Verás que hay una izquierda Flecha ( Mostrar icono) allí. Cuando haga clic en la flecha, obtendrá el cuerpo completo de Las declaraciones de consulta.

Tan pronto como haga clic en el ícono de Flecha izquierda, se convertirá en Flecha abajo ( ícono Ocultar ). Notará que se muestran dos iconos adicionales. Uno es solo lápiz. los otro es un lápiz con cruz. Todas las declaraciones de consulta también se muestran. Esto solo está disponible si ejecuta esas consultas del sistema en el menú de herramientas. de cada módulo bajo informe. Cuando hace clic en el ícono Solo lápiz, el fondo del área de consulta se volverá amarillo en lugar de gris. Ahora puede modificar la consulta para su uso propio. Tenga cuidado al cambiar esto. Necesitas una buena comprensión de Estructura de datos de SAP Business One por adelantado. No entres en pánico aquí tampoco. Puede intentar cualquier cosa para modificar la consulta. No hay daño para el sistema a menos que intente sobrescribir la consulta del sistema.

[ 38 ]

Page 47

Capitulo 2

Cuando termine de editar, puede hacer clic en el otro icono (Lápiz con cruz). Esa voluntad cambie de nuevo al modo de solo lectura del cuerpo de la consulta.

Si está interesado, aquí está la declaración completa de esta consulta: DECLARE @Factor como numérico (1,0) SELECCIONAR @Factor = CASO (SELECCIONE TOP 1 DispPosDeb DE OADM) CUANDO 'N' ENTONCES 1 ELSE -1 FINAL SELECCIONE T0.CardCode, T0.CardName, T0.Balance, T0.CreditLine, (T0.CreditLine + @ Factor * T0.Balance) "Desviación" DE OCRD T0 DONDE (seleccione T0.CreditLine + @ Factor * T0.Balance) 0 y T0.CntctDate> [% 0] y T0.CntctDate [% 0] y T0.CntctDate 20000) Empezar SET @error = 10 SET @error_message = La cantidad de N'Cash> 20,000 no está permitida ' Final FINAL

La diferencia entre el caso 2 y el caso 1 es solo en una condición. Es: T0.CashSum > 20000 .

Caso 3: bloqueo de entrada de entrada de mercancías Un usuario ha estado intentando bloquear una nueva entrada de mercancías desde la creación si el total ingresado La cantidad de todas las líneas de pedido es mayor que la cantidad en el encabezado UDF U_nQty. Por ejemplo, si el encabezado UDF U_nQty tiene cantidad 4 y el nivel de fila tiene dos elementos: • Artículo A con cantidad 2 • Artículo B con cantidad 3 Luego, el sistema bloqueará la entrada de mercancías porque la suma de la cantidad a nivel de fila es 5 (2 + 3). Es mayor que el nivel de encabezado UDF U_nQty cantidad 4.

[ 268 ]

Página 277

Capítulo 8

Este usuario creó un SP de la siguiente manera pero no funcionaba: if @ object_type = '59 'y @ transaction_type =' A ' EMPEZAR Si existe (SELECCIONE * DESDE OIGE T0 INTERIOR ÚNASE A IGE1 T1 EN T0.DocEntry = T1.DocEntry DONDE T0.DocEntry = @list_of_cols_val_tab_del Agrupar por T0.U_nQty Tener suma (cantidad T1.) sum (t0.BaseOpnQty)) empezar seleccione @Error = 10, @error_message = 'La cantidad de GRPO es mayor que PO cantidad' final final [ 272 ]

Page 281

Capítulo 8

La solucion es: IF @transaction_type IN (N'A ', N'U') Y (@Object_type = N'20 ') EMPEZAR SI EXISTE (SELECCIONE T1.BaseEntry DE dbo.OPDN T0 UNIÓN INTERNA dbo.PDN1 T1 EN T1.DOCENTRY = T0.DocEntry

UNIÓN INTERNA dbo.POR1 T2 EN T2.DOCENTRY = T1.BaseEntry DONDE T1.BaseType = 22 Y T1.ItemCode = T2.ItemCode AND T1.BaseLine = T2.LineNum AND T0.DOCENTRY = @list_of_cols_val_tab_del GROUP BY T1.BaseEntry TENER SUMA (T1.Cantidad)> SUMA (T2.OpenQty)) Empezar SELECT @Error = 10, @error_message = 'La cantidad de GRPO es mayor que PO cantidad abierta ' Final FINAL

Al comparar estos dos SP, puede encontrar las siguientes diferencias: 1. La primera diferencia se puede encontrar después de SELECCIONAR. El SP original tiene dos campos. regresar. En realidad, un campo es lo suficientemente bueno y tiene menos errores. 2. La segunda diferencia es una adición de otra tabla OPDN. Esto no es obligatorio. Sin embargo, es más claro para la lógica. 3. La última pero obviamente no menos importante es la cláusula HAVING. TENIENDO (SUMA (T0. Cantidad)> SUMA (T1.Cantidad)) o suma (t0.cantidad)> suma (t0. BaseOpnQty)) en la consulta original no es correcta. La correcta es

compare el resumen de la cantidad de PDN1 con el resumen de la correspondiente cantidad abierta POR1 directamente TENIENDO SUMA (T1.Cantidad) > SUMA (T2.OpenQty) .

Estas diferencias hacen que la solución SP funcione sin ningún problema.

[ 273 ]

Página 282

Consulta SQL para un procedimiento almacenado

Caso 7: bloquear, agregar o actualizar un pedido de número de referencia de BP duplicado Uno de los usuarios tuvo algunos problemas para que SP funcionara. El objetivo es detener un orden de agregar o actualizar si existe el mismo valor de número de referencia de BP en Campo NumatCard en otro pedido del mismo cliente. El siguiente SP ha sido probado por el usuario pero no funciona como se esperaba: DECLARAR @PORef nvarchar (100) DECLARAR @BPCode Nvarchar (35)

IF @transaction_type in ('A', 'U') AND @object_type = '17' EMPEZAR Establecer @PORef = (Seleccione T1.NumAtCard DE ORDR T1 DONDE T1.docentry = @list_of_cols_val_tab_del) Establecer @BPCode = (Seleccione T1.CardCode DE ORDR T1 DONDE T1.docentry = @list_of_cols_val_tab_del) IF @PORef, @BPCode In (Seleccione T0.NumatCard, T0.CardCode De ORDR T0) EMPEZAR SET @error = 11 SET @error_message = N'Customer PO ya está en el sistema ' FINAL FINAL

La solucion es: DECLARAR @PORef nvarchar (100) DECLARAR @BPCode Nvarchar (35) IF @transaction_type in ('A', 'U') AND @object_type = '17' EMPEZAR Establecer @PORef = (Seleccione T1.NumAtCard DE ORDR T1 DONDE T1.docentry = @list_of_cols_val_tab_del) [ 274 ]

Page 283

Capítulo 8 Establecer @BPCode = (Seleccione T1.CardCode DE ORDR T1 DONDE T1.docentry = @list_of_cols_val_tab_del) SI @PORef IN (Seleccione T0.NumatCard De ORDR T0

WHERE T0.docentry! = @List_of_cols_val_tab_del y T0.CardCode = @BPCode) Empezar SET @error = 11 SET @error_message = N'Customer PO ya está en el sistema ' Final FINAL

Nuevamente, la diferencia entre estos dos SP no es significativa. Todo lo que necesitas es el Además de T0.docentry! = @list_of_cols_val_tab_del , de modo que la condición de comparación puede basarse en otros registros de la tabla que no incluyen el el actual.

Caso 8: bloqueo de documentos de ventas en función de fechas

Un usuario desea bloquear la creación del pedido de ventas, la entrega y la devolución de meses anteriores después del quinto del mes actual. La solución para el pedido de cliente es la siguiente: IF @object_type = '17' y @transaction_type = 'A' EMPEZAR SI EXISTE (SELECCIONE T0.DocEntry DE dbo.ORDR T0 DONDE DateDiff (MM, T0.DocDate, GetDate ())> 1 AND DatePart (DD, GetDate ())> 4 Y [ 275 ]

Page 284

Consulta SQL para un procedimiento almacenado T0.DocEntry = @list_of_cols_val_tab_del) Empezar seleccione @error = 99, @error_message = 'No tiene permitido publicar en mes anterior.' Final FINAL

El mes de la fecha del documento se ha verificado primero. Entonces la fecha actual tiene ha sido revisado para ver si es después del quinto. Estas dos condiciones satisfacen al usuario.

solicitud. Todos los demás tipos de documentos se pueden agregar fácilmente con la misma lógica. Para la entrega, @object_type debe cambiarse a '15' y el nombre de la tabla sería ODLN Para la devolución de ventas, @object_type es '16' y el nombre de la tabla es ORDN.

Caso 9: Servicio de validación tipo A / R de crédito memorándum Un usuario desea restringir la creación de la nota de crédito de tipo A / R del servicio. SP había sido creado para tratar de bloquear a ciertos usuarios. El siguiente SP del usuario no funciona para cumplir este propósito: declarar @usersign como INT SELECCIONE @ usersign = USERSIGN FROM ADOC WHERE DOCENTRY = @ list_of_cols_val_ tab_del AND ObjType = 14 Si @transaction_type IN ('A', 'U') y @object_type = '14' Empezar Si existe (SELECCIONE DocNum DESDE ORIN DONDE DocEntry = @list_of_cols_val_ tab_del Y DocType = 'S' y @usersign no en (1,2,3)) Empezar Seleccione @error = -1, @error_message = 'Acceso no permitido' final FINAL

La solucion es: Si @transaction_type IN ('A', 'U') y @object_type = '14' EMPEZAR declarar @user int SELECT @ user = SELECT usersign2 FROM ORIN WHERE DocEntry = @list_of_ cols_val_tab_del [ 276 ]

Page 285

Capítulo 8 Si @user in (1,2,3) Empezar Si existe (SELECCIONE DocNum DE ORIN DONDE DocEntry = @list_of_cols_val_tab_del Y DocType = 'S') empezar SELECCIONAR @error = 14, @error_message = 'Acceso no permitido' final Final

FINAL

La diferencia entre las dos consultas es la definición de la variable. En la solución, el La variable se reduce a una sola. La declaración de la variable se mueve a la sección después de la condición inicial: Si @transaction_type IN ('A', 'U') y @object_type = '14' .

En lugar de verificar si los usuarios no están en una lista específica, la solución verifica si los usuarios están en una lista Esto es bueno para el rendimiento del sistema si el número de usuarios en la lista no es demasiados.

Caso 10: bloqueo de la emisión de bienes para ninguno super usuario

Un usuario logró aplicar una notificación de transacción en una salida de mercancías. documento para dar un error cuando WhsCode = '02' . La razón para bloquear la emisión de este almacén se debe a que se ha definido como almacén para chatarra. Si @transaction_type = 'A' y @object_type = '60' Empezar Si existe (Seleccione T0.itemcode de dbo.IGE1 T0, dbo.OIGE T1 Inner Join dbo.OUSR T2 En T1.userSign = T2.UserId donde T0.docentry = @list_of_cols_val_tab_del y T2.USER_CODE! = 'manager2' y T0.whscode = '02') Empezar seleccione @error = 1, @error_message = 'No está permitido emitir desde Almacén 02 ' final final

Sin embargo, este SP está bloqueando a todos los usuarios para agregar el documento de salida de mercancías. los El resultado previsto solo debería detener a todos los demás usuarios, pero no al superusuario 'manager2'. los El código agregado al SP T2.USER_CODE! = 'manager2' no funciona. [ 277 ]

Page 286

Consulta SQL para un procedimiento almacenado

La solucion es: IF @transaction_type = 'A' y @object_type = '60' EMPEZAR Si existe (SELECCIONE T0.itemcode De dbo.IGE1 T0 UNIÓN INTERNA dbo.OIGE T1 EN T1.DocEntry = T0.DocEntry Unión interna dbo.OUSR T2 en T1.userSign = T2.UserId DONDE T1.docentry = @list_of_cols_val_tab_del Y

T2.USER_CODE! = 'Manager2' Y T0.whscode = '02') Empezar SELECT @error = 1, @error_message = 'No está permitido emitir desde Almacén 02 ' Final FINAL

La diferencia es que la solución unía todas las tablas, pero la original usaba una coma entre IGN1 y OIGN. OUSR (T2) solo se vincula a OIGN (T1). Pero OIGN tiene no se ha vinculado a IGN1 (T0). Eso hace que T0.docentry = @list_of_cols_val_ tab_del sin enlace de OIGN. Todos los registros en OIGN volverán. Efectivamente

anula la condición puesta en OUSR.

Caso 11: Bloqueo de entrada de mercancías PO si no PO basado Un usuario desea bloquear pedidos de entrada de mercancías que no se basan en órdenes de compra. La solución es más simple de la siguiente manera: IF @transaction_type = 'A' Y @object_type = '20' EMPEZAR IF existe (Seleccione ItemCode de dbo.PDN1 T0 Donde T0.BaseType = -1 Y T0.DocEntry=@list_of_cols_val_tab_del)

[ 278 ]

Página 287

Capítulo 8 Empezar SELECCIONE @error = 20, @error_message = '¡GRPO sin orden de compra!' Final FINAL

Esta es una solución típica para un requisito similar. Por ejemplo, puedes cambiar @ object_type de '20' a '15' y PDN1 a DLN1 para bloquear la entrega si la entrega no tiene documento base

A lo largo de este capítulo, ha aprendido una aplicación importante para la consulta SQL Resumen en SAP Business One. Este capítulo presenta una descripción completa de la escritura de consultas para SBO_SP_TransactionNotification. También recibió una explicación de los parámetros importantes que le muestran cómo trabajar con el procesamiento de procedimientos almacenados. También arroja luz sobre la escritura de consultas habilidades y tecnicismos de optimización de consultas. Al leer y probar una parte de los SP de muestra completos dentro de este capítulo, ahora debería poder crear su propia consulta principal para SBO_SP_ Notificación de transacción. El siguiente capítulo es el último capítulo. Se centra en temas de consulta más complicados y condiciones para su uso al escribir funcionalidades avanzadas en sus consultas.

[ 279 ]

Page 289 288

SQL más complicado Temas de consulta Este es el último capítulo del libro y tendremos una discusión extensa, que le dará algunos de los ejemplos más complicados de consulta SQL en SAP Business One. Este capítulo lo acercará a presentar aún más negocios Inteligencia en SAP Business One. Los temas principales incluyen: • El uso de la expresión Case • Trabajando con subconsulta • Usar PIVOT para simplificar las consultas de estilo de tabla cruzada • Consulta de base de datos para Excel Además, esta última sección le dará algunos consejos sobre las trampas y errores para evitarse en la escritura de consultas. Estos temas son más adecuados para los usuarios que tienen intereses adicionales para extender sus capacidades para crear consultas más complicadas. Aunque los ejemplos anteriores pueden volver a visitar, me gustaría mostrar más ejemplos de consultas para cubrir más áreas. Consulte el prefacio para obtener más información sobre los nombres específicos. convenciones de los ejemplos de consulta en este capítulo.

El uso de la expresión Case En el Capítulo 3 , aprendió la gramática de las expresiones Case y If. Además, deberías haber notado que estas expresiones se han usado varias veces en el consultas de muestra de capítulos anteriores. Sin embargo, esos ejemplos se centran principalmente en los procesos de negocio. No hubo discusiones extendidas sobre cómo usar esos expresiones CASE tiene una función similar a la que la mayoría de las personas entiende como Las condiciones "IF / THEN" se usan a menudo en diagramas de flujo y otros lenguajes de programación. Sin embargo, en la consulta SQL, la expresión IF solo se puede usar fuera de SELECT

Page 290

Temas de consulta SQL más complicados

declaración. Por el contrario, CASE puede usarse dentro de la instrucción SELECT. Por lo tanto se usa con más frecuencia. Puede encontrar más consultas utilizando la expresión CASE. Solo discutiremos esta expresión y no entraremos en la otra.

Caso 9-C1: visualización del transtipo como código en lugar de un número Un usuario había escrito una consulta para mostrar el número de cada tipo de entrada de diario creado por los usuarios durante un período específico: SELECCIONE EL CONTEO (*) COMO 'Creado', T0.TransType, T0.UserSign, T1.U_NAME

DESDE OJDT T0 ÚNETE A OUSR T1 EN T0.UserSign = T1.USERID DONDE T0.RefDate> = '20100301' Y T0.RefDate = '20100301' Y T0.RefDate = 500000 Y DocTotal = 750000 Y DocTotal = 1000000 Y DocTotal = 1250000 Y DocTotal = 1500000 Y DocTotal = 1750000 Y DocTotal 2,000,000. Por lo tanto, puede que no sea una lista completa.

Caso 9-C4: Subtotal de artículo sabio en una mercancía recibo Un usuario recibió una consulta para mostrar una entrada de mercancías. La consulta funciona bien pero no lo es resumido. Necesita agregar un elemento subtotal sabio. Seleccione t0.docdate, (t1.itemcode), sum (case (t0.comments) cuando 'P' y luego

t1.Cantidad más 0 fin) 'P', suma (caso (t0.comments) cuando 'S' entonces t1.Cantidad más 0 finaliza) 'S' Desde oign t0 interno únete ign1 t1 en t0.docentry = t1.docentry unión interna oitm t2 en t2.itemcode = t1.itemcode donde T0.Docdate> =% 0 y T0.docdate = [% 0] y T0.docdate = [% 0] y T0.docdate = [% 0] y T0.docdate = [% 0] y T0. [CreateDate] = [% 0] y T0.createDate = [% 0] y T0.createDate 1) [ 289 ]

Page 298

Temas de consulta SQL más complicados

Esta consulta también tiene una subconsulta bajo la cláusula WHERE. Puedes ver que un La subconsulta en sí misma también puede ser una consulta complicada. Esa es una de las razones por las cuales Se deben evitar las subconsultas de múltiples niveles. Bajo la subconsulta, la condición para Se logra filtrar más de un InternalSN idéntico con lo siguiente: GRUPO POR T0. [InternalSN] TENIENDO CONTEO (T0. [InternalSN])> 1

Caso 9-S4: muestra la última factura de C / P fecha del documento para artículos

Un usuario debe vincular elementos con su 'última fecha de documento de factura de C / P' y 'última C / P

Nombre de la factura BP '. La solucion es: SELECCIONE Distinto T0.ItemCode, T2.TaxDate, T2.CardName DE dbo.PCH1 T0 UNIÓN INTERNA dbo.OPCH T2 EN T2.DocEntry = T0.DocEntry DONDE T2.TaxDate> '[% 0]' Y T0.DocEntry en (SELECCIONAR MÁX (T1.DocEntry) DE dbo.PCH1 T1 DONDE T0.ItemCode = T1.ItemCode GROUP BY T1.ItemCode)

Es posible que esta solución no cumpla exactamente con el requisito original, pero es aceptada por el usuario. En lugar de verificar la fecha del documento, la subconsulta verifica el documento entrada. La entrada del documento es una clave principal de la tabla. Siempre aumenta automáticamente. Para mí, es una forma más confiable de verificar el último registro. Si su sistema tiene muchos cambios de fecha manuales durante el documento creación, debe revisar la consulta para agregar más condiciones. Si encuentra una respuesta no lógica de esa manera, no se sorprenda. Las diferentes selecciones de períodos pueden causar un resultado inverso a veces. Por ejemplo, cuando selecciona una fecha anterior como duración, puede obtener una fecha de factura más reciente.

[ 290 ]

Page 299

Capítulo 9

Usando PIVOT para simplificar un estilo de tabla cruzada consultas

La función Pivot se introduce en MS SQL Server 2005. Si todavía está usando SQL Servidor 2000 o inferior (poco probable), puede omitir esta sección. La tabla dinámica es común término. Lo he usado en Excel ampliamente. Si no está familiarizado con él, ejecute una muestra Consulta aquí. Incluso si la versión de su servidor SQL es igual o superior a 2005, aún puede obtener un mensaje de error porque el nivel de compatibilidad del servidor debe ser 2005 o superior. Puede cambiar este nivel en SQL Server Management Studio si la compatibilidad El nivel es bajo.

La función de pivote es una herramienta muy útil para simplificar consultas repetidas largas.

Caso 9-P1: ventas mensuales por geografía Un usuario desea obtener una lista de las ventas de cada estado por mes. Están usando el primeros dos caracteres del código de cuenta de un BP para representar sus estados. La consulta sería similar a la del Caso 4-M14 de Consulta para marketing documento receta en el Capítulo 4 . La solución es la siguiente: SELECCIONE P. [ESTADO], [1] como [Jan], [2] como [feb], [3] como [Mar], [4] como [Abr], [5] como [mayo], [6] como [junio], [7] como [julio], [8] como [agosto], [9] como [Sep], [10] como [Oct], [11] como [nov], [12] como [dic] DESDE (SELECCIONAR Izquierda (T0.CardCode, 2) como [Estado], T0.DocTotal como [DocTotal], MES (T0.docdate) como [mes] DE dbo.oinv T0 [ 291 ]

Page 300

Temas de consulta SQL más complicados DONDE Año (T0.docdate) = 2010 UNIÓN SELECCIONAR Izquierda (T0.CardCode, 2) como [Estado], -T0.DocTotal como [DocTotal], MES (T0.docdate) como [mes] DE dbo.orin T0 DONDE Año (T0.docdate) = 2010) S PIVOTE (SUMA (DocTotal) PARA [mes] EN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) PAG ORDEN POR P. [Estado]

Puede ver en el ejemplo que estoy usando la función Izquierda en combinación con un 2

leer los dos primeros caracteres del código de Business Partner, que a su vez me da El código de estado para cada BP basado en los datos del usuario. Si compara esta consulta con la del Capítulo 4 , encontrará que la función Pivot puede simplifique mucho para hacer el mismo trabajo en menos del 50 por ciento del tamaño de la consulta. Hay que señalar algo aquí, el ejemplo de consulta en el Capítulo 4 solo tiene preguntó una mesa. Este ejemplo consulta dos tablas. La función de pivote requiere dos partes. La primera parte son los datos principales, incluida toda la lógica. Puede encontrar esta parte con alias como tabla S justo después de FROM. La tabla S actúa como un mesa temporal. La siguiente parte es la tabla P que alimenta todos los datos de la tabla S en diferentes ramas Todas las columnas después de SELECT salen de la tabla P. Alias S y P se puede cambiar a cualquier otra palabra. Me parece conveniente mantenerlos de esta manera.

Caso 9-P2: lista completa de todos los artículos con / sin ventas

Un usuario necesita encontrar la cantidad vendida por mes para cada uno de sus estilos. Los estilos Tiene múltiples colores y tamaños. Cada estilo está indicado por los primeros cuatro caracteres de El código del artículo. El usuario ya tiene una consulta. La consulta también se parece a la del Caso 4-M14 mencionado en el Capítulo 4 . El principio y el final de la consulta se ven como el seguimiento: SELECCIONAR A LA IZQUIERDA (T0.ITEMCODE, 4) COMO 'Estilo', (SELECCIONAR SUMA (T1.QUANTITY) DESDE INV1 T1 con (NOLOCK) DONDE EL MES (T1.DOCDATE) = 1 Y IZQUIERDA (T1.ITEMCODE, 4) = IZQUIERDA (T0.ITEMCODE, 4) Y AÑO (T1.DOCDATE) = AÑO (GETDATE ())) COMO 'JAN Amt' ......

[ 292 ]

Page 301

Capítulo 9 (SELECCIONE SUMA (T1.QUANTITY) DESDE INV1 T1 con (NOLOCK) DONDE EL MES (T1. DOCDATE) = 12 AND LEFT (T1.ITEMCODE, 4) = LEFT (T0.ITEMCODE, 4) AND AÑO (T1.DOCDATE) = AÑO (GETDATE ()) COMO 'DEC Amt' DESDE dbo.OITM T0 IZQUIERDA ÚNETE dbo.INV1 T1 A LA IZQUIERDA (T1.ITEMCODE, 4) = IZQUIERDA (T0.ITEMCODE, 4) DONDE T0.SellItem = 'Y' GROUP BY LEFT (T0. CÓDIGO DEL ARTÍCULO, 4), AÑO (T1.DOCDATE) QUE TIENE AÑO (T1.DOCDATE) = AÑO (GETDATE ()) ORDEN POR IZQUIERDA (T0. CÓDIGO DE ARTÍCULO, 4)

Se necesita un cambio para que la consulta muestre una lista completa de todos los estilos. No importa si hay ventas para cualquier mes o no, esta consulta solo muestra artículos que tienen ventas. La solución es la siguiente: SELECCIONE P. [Estilo],

[1] como [Jan], [2] como [feb], [3] como [Mar], [4] como [Abr], [5] como [mayo], [6] como [junio], [7] como [julio], [8] como [agosto], [9] como [Sep], [10] como [Oct], [11] como [nov], [12] como [dic] DESDE (SELECCIONAR A LA IZQUIERDA (T0.ITEMCODE, 4) COMO 'Estilo', T1.CANTIDAD, MES (T1. Docdate) como [mes] DE dbo.oitm T0 IZQUIERDA UNIR INV1 T1 EN T1.ItemCode = T0.ItemCode Y AÑO (T1.DocDate) = AÑO (GETDATE ())) S PIVOTE (SUMA (CANTIDAD) PARA [mes] EN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) PAG ORDEN POR P. [Estilo]

La consulta original solo ha enumerado dos meses en lugar de doce y todavía es muy larga. La nueva consulta aprovecha la función Pivot. Puedes ver cuánta longitud diferencia hay entre la solución y la consulta original.

[ 293 ]

Página 302

Temas de consulta SQL más complicados

Consulta de base de datos para Excel

La consulta de base de datos para Excel es una función integrada de Microsoft Excel. Está disponible de versiones anteriores de Excel. Una vez que comencé a usarlo, descubrí que esta función era muy poderoso. Se puede usar para casi todas las consultas que está ejecutando en Consulta Gerente. El formato del informe se puede hacer de forma rápida y atractiva. Para mi es Una alternativa a un simple Crystal Report. Todas las capturas de pantalla del libro provienen de Excel 2003. Si está utilizando Excel 2007 o más alto, es posible que necesite desde un menú similar pero no desde una interfaz de usuario idéntica. los La funcionalidad no tiene cambios significativos. Importar datos externos es el elemento del menú que necesita localizar. Está debajo del menú Datos .

Lo primero que debe hacer es crear una Nueva consulta de base de datos . Este es uno de los submenús elementos para Importar datos externos . Puede seleccionar este menú para acceder a la siguiente captura de pantalla.

Crear una nueva fuente de datos La primera tarea al crear una nueva consulta de base de datos es Elegir origen de datos . Si es el la primera vez que intente usar esta función, probablemente no tendrá muchos datos fuentes para seleccionar. De hecho, es posible que no encuentre ninguna fuente de datos relacionada con su sistema SAP Business One.

[ 294 ]

Página 303

Capítulo 9

Nueva fuente de datos agregada dentro de Excel

Puede seleccionar la primera entrada allí: . Este es un nombre claro para liderar en la dirección correcta Asegúrese de que la marca de verificación en la parte inferior del formulario Elegir origen de datos esté predeterminada posición. Puede aprovechar Use el Asistente de consultas para crear / editar consultas .

Aparecerá la siguiente captura de pantalla: Crear nueva fuente de datos . Hay cuatro pasos al crear una nueva fuente de datos: 1. Nombre su nueva fuente de datos. 2. Seleccione un controlador de base de datos. 3. Pruebe la conexión de datos. 4. Seleccione una tabla predeterminada para la fuente de datos (opcional). El primer paso como se muestra en la siguiente captura de pantalla es el nombre dado a los datos fuente: lo antes posible .

[ 295 ]

304 de 1189.

Temas de consulta SQL más complicados

Para el segundo paso, dado que el controlador de la base de datos para el servidor SQL generalmente se encuentra en el abajo, SQL Native Client se selecciona como el controlador de datos correcto para conectarse a la base de datos SAP Business One.

Después de la selección del controlador de la base de datos, el tercer paso es la conexión. Hacer clic Conectar . Aparece el formulario de inicio de sesión de SQL Server . Puede seleccionar entre los disponibles Los servidores SQL son los correctos que contienen la base de datos SAP Business One. Use Trusted La conexión generalmente se verifica para iniciar sesión en el servidor SQL sin necesidad de ingrese el nombre de usuario y la contraseña cada vez que desee actualizar los datos.

[ 296 ]

305 de 1189.

Capítulo 9

En la forma anterior está el botón Opciones >> . Al hacer clic en él, este formulario puede mostrar mas opciones: • Base de datos • idioma • Nombre de la aplicación • Estación de trabajo En el servidor SQL seleccionado, debe tener más de una base de datos. Selecciona el uno para el que necesita datos de SAP Business One. Si necesita acceso a más de un SAP Base de datos Business One, se necesitan múltiples fuentes de datos. El idioma generalmente se deja como (Predeterminado) , que generalmente le brinda el idioma correcto

versión basada en la configuración de su servidor SQL. El sistema completa automáticamente el nombre de la aplicación y la estación de trabajo . Allí No es necesario cambiarlos.

[ 297 ]

Página 306

Temas de consulta SQL más complicados

Haga clic en Aceptar para cerrar el formulario de inicio de sesión de SQL Server . La captura de pantalla se muestra de la siguiente manera:

Nueva fuente de datos agregada desde el panel de control Los pasos anteriores son pasos completos para agregar una nueva fuente de datos ODBC desde Excel directamente. Puede obtener los datos en una hoja de cálculo de Excel actual. Sin embargo, hay más forma profesional de hacerlo directamente desde ODBC Date Source Administrator en Herramientas de administración desde el Panel de control .

[ 298 ]

Página 307

Capítulo 9

La primera pestaña para esta herramienta es Usuario DSN . DSN es una forma abreviada de Nombre de origen de datos . Esta es la pestaña que nos interesa. Haga clic en Agregar y en Crear nueva fuente de datos aparece el formulario. Puede encontrar que hay muchos menos controladores disponibles para configurar datos fuente que de Excel directamente. SQL Native Client puede estar justo arriba. Haga clic en Finalizar , y puedes ir a la siguiente pantalla.

El proceso en las siguientes capturas de pantalla es casi idéntico al proporcionado por Excel. Sin embargo, la interfaz de usuario es de SQL Server directamente. Puede tener un formulario más grande para la misma información requerida. El nombre, la descripción y el servidor se completa, como se muestra en la siguiente captura de pantalla:

[ 299 ]

Página 308

Temas de consulta SQL más complicados

Hay una forma más aparte del ejemplo anterior. Puedes seleccionar un más forma detallada de elegir cómo SQL Server debe verificar la autenticación del inicio de sesión ID de usuario El valor predeterminado será Con autenticación integrada de Windows . Si te gusta También puede seleccionar Con autenticación de SQL Server utilizando una identificación de usuario y contraseña de inicio de sesión ingresado por el usuario . Conéctese a SQL Server para obtener la configuración predeterminada para la configuración adicional la casilla de verificación de opciones está seleccionada de forma predeterminada. Deberías dejarlo allí. Haga clic en Siguiente nuevamente, puede encontrar la siguiente captura de pantalla. Aquí puedes cambiar la base de datos predeterminada a la base de datos SAP Business One que planea crear. Todos los demás Las opciones no deben cambiarse para evitar incompatibilidades.

La siguiente pantalla es más técnica. Es el último paso en el proceso. Puedes afinar La forma de conectarse a su base de datos. Cuando haga clic en Finalizar en este formulario, debería recibir una ventana de mensaje de confirmación. Un mensaje en el encabezado dice: Se creará una nueva fuente de datos ODBC con la siguiente configuración: Se muestra un gran cuadro de mensaje de texto con todas sus configuraciones. Puedes verificar la configuración para verificar el detalle de la conexión.

[ 300 ]

Página 309

Capítulo 9

Haga clic en el botón Probar fuente de datos ... en la parte inferior del formulario de mensaje anterior; Puede probar la fuente de datos inmediatamente. Si es la configuración correcta, puede obtener un mensaje exitoso para ello.

[ 301 ]

Page 310

Temas de consulta SQL más complicados

Asistente de consulta para consulta de base de datos en Excel Hemos dedicado mucho tiempo a obtener la fuente de datos, y ahora podemos volver a El tema principal: consulta de base de datos para Excel. Continuaremos desde el primer paso en Nuevo Consulta de base de datos . Si se selecciona la fuente de datos correcta, puede pasar por Asistente de consultas en Microsoft Query. Obtiene tablas y columnas disponibles en el caja izquierda La tabla seleccionada es OCRG. Expande y selecciona todas las columnas con la flecha clave para enviarlos a la casilla correcta.

Puede obtener una vista previa de los datos en la columna seleccionada para confirmar que está en el camino correcto.

[ 302 ]

Página 311

Capítulo 9

La siguiente pantalla es un formulario de opción para filtrar los datos. Puedes usar la fórmula dada para agregar condiciones para restringir los datos. En el siguiente ejemplo, GroupCode