Trazabilidad orden, entrega y factura

SAP Business One
1

Buen día estimados, gusto en saludarles.

Tengo el siguiente query que me muestra lo quee stoye buscando y medir metricas de productividad pero resulta que el DocNum de la Factura no coincide con el mapa de relaciones en SAP.

Por ejemplo me muestra los numeros de documentos d ela orden entrega y factura en el query me salen iguales la orden y la entrega pero la factura no coincide.

image

y en el query muestra:

image
image1192×21 11.8 KB

Alguna idea, agradezco la ayuda.

SELECT DISTINCT	
	T0."CardCode" AS "Cliente",
	T0."CardName" AS "Nombre",	
	T0."DocNum" AS "# de OV",
	T0."DocTotal" AS "Total OV",
	T0."DocDate" AS "Fecha_OV", T5."U_NAME" AS "Usuario",T0."CreateTS" AS "FHG",
              T0."UpdateTS" AS "FHAct", T0."DocTime" AS "H. Generacion" ,left((right('000000'||cast (T0."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T0."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.", 

	T2."DocNum" AS "# de Entrega",
              T2."U_LD_Respons1" AS "Responsable de Alisto",
              T2."U_LD_Clase1" AS "Tipo de error",
              T7."U_NAME" AS "Paso a Factura",
	T2."DocTotal" AS "Total Entrega",
	T2."DocDate" AS "Fecha_Entrega",
              T2."CreateTS" AS "FHG",
              T2."DocTime" AS "H. Generación",
              left((right('000000'||cast(T2."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T2."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.",

	T4."DocNum" AS "# de Factura",
	T4."DocTotal" AS "Total_Factura",
	T4."DocDate" AS "Fecha_Factura",
              T4."CreateTS" AS "FHG",
              T4."DocTime" AS "H. Generación",
              left((right('000000'||cast(T4."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T4."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.",
              T4."U_LD_Funci2"AS "Mensajero",
              T4."U_LD_Guia1" AS "Numero_Guía",
              T4."U_LD_Fecha1"  AS "Fecha_Guía",
              T4."U_LD_Hora1" AS "Hora_Guía"

              
        
               
		
FROM ORDR T0

LEFT JOIN 
	DLN1 T1 ON T0."DocEntry" = T1."BaseEntry"
	
LEFT JOIN
	ODLN T2 ON T1."DocEntry" = T2."DocEntry"

LEFT JOIN 
	INV1 T3 ON T2."DocEntry" = T0."BaseEntry"
	
LEFT JOIN
	OINV T4 ON T1."DocEntry" = T4."DocEntry"

LEFT JOIN
              OUSR T5 ON T0."UserSign" = T5."USERID"

LEFT JOIN
              OUSR T6 ON T2."UserSign" = T6."USERID"

LEFT JOIN
              OUSR T7 ON T4."UserSign" = T7."USERID"
		

WHERE 
	T0."CANCELED" NOT IN ('Y')
	
	
Order by T0."DocDate"
2

Buen dia.

Esta bien la consulta lo que esta mal es como los estas uniendo te dejo 1 ejemplo ando ocupado pero se que le enteras a la primera:

RDR1 T0 LEFT JOIN ODLN T1 
ON T0.TrgetEntry = T1.DocEntry AND T0.TargetType = T1.ObjType

de lo contrario no va a funcionar.

1 me gusta
3

Jose agradezco la respuesta, me queda un duda solamente con esta solicitud para realizar ese emparejamiento se debería de unir la tabla de la orden de venta y la factura o es de la entrega ?

4

te paso mi query para eu no tengas problemas solo adecualo a tus necesidades.

select distinct 'FacRes'  as  "Tipo"  , T0."CardCode", T0."CardName" , 
T0."DocEntry", T0."CANCELED" as "status Orden",  T0."DocNum" as "Numero Entrega" , 
T0."DocDate" as "Fecha de Entrega", T0."DocTotal" as "totalorden", T0."DocTotalFC" as "totordusd",T0."DocCur",T1."TargetType" as"destino",T1."TrgetEntry" as "EntryFact",T0."ShipToCode" as "Envio",
T1."BaseType" , T3."DocNum" as  "No deFactura", T3."DocDate"  as "Fecha Factura" , T1."ItemCode" as "CodigoProd",
T3."CANCELED" as "status Fac", T3."DocEntry" , T3."DocTotal" as totfactmx, T3."DocTotalFC" as totfactus,T3."DocCur" as "monfact",
T5."DocNum" as "Autorizacion de Salida" , T5."DocDate" as "Fecha Autorizacion" , T5."CANCELED" as "Estatus Aut",
T5."DocTotal" as "totsalmx", T5."DocTotalFC" as "totsalus",T5."DocCur" as "monsal",
T4."TargetType" as "DESTFACT", T4."TrgetEntry" AS "NCEntry",T5."NumAtCard" as "NumOC",
T2."DocNum" as  "No deNC", T2."DocTotal" as "monto de NC",T2."DocTotalFC" AS "MonNdCusd",
T6."Dscription" as "Descr", T6."Quantity" as "Canti",T6."UomCode" as "unidad"

from ODLN T0 
inner join DLN1 T1 on T1."DocEntry" = T0."DocEntry" and  T1."BaseType" = 13 and T1."ItemCode"  not in ('05-01-004','06-01-001','06-01-002') and T1."LineNum"=0
left  join OINV T3 on T3."DocEntry" = T1."BaseEntry"  
left join INV1 T4 on T1."TrgetEntry" = T4."DocEntry" and T4."BaseType" = 17 
left join ORDR T5 on T5."DocEntry" = T4."BaseEntry" 
inner join RDR1 T6 on T3."DocEntry" = T6."DocEntry" 
LEFT JOIN ORIN T2 ON T4."TrgetEntry" = T2."DocEntry"


union all 

select distinct 'Re-Entregadas'  as  "Tipo"   , T0."CardCode", T0."CardName" , T0."DocEntry", 
T0."CANCELED" as "status Orden",  T0."DocNum" as "Numero Entrega" , 
T0."DocDate" as "Fecha de Entrega", T0."DocTotal" as "totalreentrega", T0."DocTotalFC" as "totalreentUSD",T0."DocCur",T1."TargetType" as"destino",T1."TrgetEntry" as "EntryFact",T0."ShipToCode" as "Envio",
T1."BaseType" , 0  as  "No deFactura", '0'  as "Fecha Factura" , T1."ItemCode" as "CodigoProd",
T3."CANCELED" as "status Fac", T3."DocEntry" ,0 as totfactmx,0 as totfactus,'' as "monfact",
0  as "Autorizacion de Salida" , ' '   as "Fecha Autorizacion", '0' as "Estatus Aut",
0 as totsalmx, 0 as totsalus, ' ' as "monsal",
0 as "DESTFACT", 0 AS "NCEntry",T0."NumAtCard" as "NumOC",
0 as  "No deNC", 0 as "monto de NC",0 AS "MonNdCusd",
T1."Dscription" as "Descr", T1."Quantity" as "Canti",T1."UomCode" as "unidad"

from ODLN T0 
inner join DLN1 T1 on T1."DocEntry" = T0."DocEntry" and  T1."BaseType" = 15 and T1."ItemCode"  not in ('05-01-004','06-01-001','06-01-002') and T1."LineNum"=0
inner join ODLN T3 on T3."DocEntry" = T1."BaseEntry"


union all 

select distinct 'Directa'  as  "Tipo"   , T0."CardCode", T0."CardName" , T0."DocEntry", 
T0."CANCELED" as "status Orden",  T0."DocNum" as "Numero Entrega" , 
T0."DocDate" as "Fecha de Entrega", T0."DocTotal" as "totalreentrega", T0."DocTotalFC" as "totalreentUSD",T0."DocCur",T1."TargetType" as"destino",T1."TrgetEntry" as "EntryFact",T0."ShipToCode" as "Envio",
T1."BaseType" , 0  as  "No deFactura", '0'  as "Fecha Factura" , T1."ItemCode" as "CodigoProd",
T3."CANCELED" as "status Fac", T3."DocEntry" ,0 as totfactmx,0 as totfactus,'' as "monfact",
0  as "Autorizacion de Salida" , ' '   as "Fecha Autorizacion", '0' as "Estatus Aut",
0 as totsalmx, 0 as totsalus, ' ' as "monsal",
T4."TargetType" as "DESTFACT", T4."TrgetEntry" AS "NCEntry",T0."NumAtCard" as "NumOC",
T2."DocNum" as "No deNC", T2."DocTotal" as "monto de NC",T2."DocTotalFC" AS "MonNdCusd",
T1."Dscription" as "Descr", T1."Quantity" as "Canti",T1."UomCode" as "unidad"

from ODLN T0 
inner join DLN1 T1 on T1."DocEntry" = T0."DocEntry" and  T1."BaseType" = -1 and  T1."TargetType" in ('13','15','-1') and T1."ItemCode"  not in ('05-01-004','06-01-001','06-01-002') and T1."LineNum"=0
left join INV1 T4 on T1."TrgetEntry" = T4."DocEntry" 
left join OINV T3 on T3."DocEntry" = T4."BaseEntry"
LEFT JOIN ORIN T2 ON T4."TrgetEntry" = T2."DocEntry"


union all 

select distinct 'Factura'  as  "Tipo"  ,T0."CardCode", T0."CardName" ,  T0."DocEntry", 
T0."CANCELED" as "status Orden",  T0."DocNum" as "Numero Entrega" , 
T0."DocDate" as "Fecha de Entrega", T0."DocTotal", T0."DocTotalFC",T0."DocCur",T1."TargetType" as"destino",T1."TrgetEntry" as "EntryFact",T0."ShipToCode" as "Envio",
T1."BaseType" , T5."DocNum" as  "No deFactura", T5."DocDate"  as "Fecha Factura" , T1."ItemCode" as "CodigoProd",
T5."CANCELED" as "status Fac", T3."DocEntry" ,T5."DocTotal" as "totfactmx", T5."DocTotalFC" as "totfactus",T5."DocCur" as "monfact",
T3."DocNum" as "Autorizacion de Salida" , T3."DocDate" as "Fecha Autorizacion", T3."CANCELED" as "Estatus Aut",
T3."DocTotal"  as "totsalmx", T3."DocTotalFC" as "totsalus", T3."DocCur" as "monsal",
T4."TargetType" as "DESTFACT", T4."TrgetEntry" AS "NCEntry",T3."NumAtCard" as "NumOC",
T2."DocNum" as "No deNC", T2."DocTotal" as "monto de NC",T2."DocTotalFC" AS "MonNdCusd",
T6."Dscription" as "Descr", T6."Quantity" as "Canti",T6."UomCode" as "unidad"

from ODLN T0 
inner join DLN1 T1 on T1."DocEntry" = T0."DocEntry" and  T1."BaseType" in('17') and T1."TargetType" in ('13','15','-1') and T1."ItemCode"  not in ('05-01-004','06-01-001','06-01-002') and T1."LineNum"=0
inner join ORDR T3 on T3."DocEntry" = T1."BaseEntry"
inner join RDR1 T6 on T3."DocEntry" = T6."DocEntry" 
left join INV1 T4 on T1."TrgetEntry" = T4."DocEntry" 
left  join OINV T5 on T5."DocEntry" = T4."DocEntry" 
LEFT JOIN ORIN T2 ON T4."TrgetEntry" = T2."DocEntry"
1 me gusta
5

Gracias Jose te agradezco bastante la ayuda revise lo mejor que pude tu query ya que veo que casi todo esta bien solo hice una corrección que es la que mejor veo pero me sigue dando lo mismo:

SELECT DISTINCT	
	T0."CardCode" AS "Cliente",
	T0."CardName" AS "Nombre",	
	T0."DocNum" AS "# de OV",
	T0."DocTotal" AS "Total OV",
	T0."DocDate" AS "Fecha_OV", T5."U_NAME" AS "Usuario",T0."CreateTS" AS "FHG",
              T0."UpdateTS" AS "FHAct", T0."DocTime" AS "H. Generacion" ,left((right('000000'||cast (T0."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T0."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.", 

	T2."DocNum" AS "# de Entrega",
              T2."U_LD_Respons1" AS "Responsable de Alisto",
              T2."U_LD_Clase1" AS "Tipo de error",
              T7."U_NAME" AS "Paso a Factura",
	T2."DocTotal" AS "Total Entrega",
	T2."DocDate" AS "Fecha_Entrega",
              T2."CreateTS" AS "FHG",
              T2."DocTime" AS "H. Generación",
              left((right('000000'||cast(T2."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T2."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.",

	T4."DocNum" AS "# de Factura",
	T4."DocTotal" AS "Total_Factura",
	T4."DocDate" AS "Fecha_Factura",
              T4."CreateTS" AS "FHG",
              T4."DocTime" AS "H. Generación",
              left((right('000000'||cast(T4."UpdateTS" as varchar(6)),6)),2)||':'||substr((right('000000'||cast(T4."UpdateTS" as varchar(6)),6)),3,2) as "Hora Act.",
              T4."U_LD_Funci2"AS "Mensajero",
              T4."U_LD_Guia1" AS "Numero_Guía",
              T4."U_LD_Fecha1"  AS "Fecha_Guía",
              T4."U_LD_Hora1" AS "Hora_Guía"

              
        
               
		
FROM ORDR T0

LEFT JOIN 
	DLN1 T1 ON T0."DocEntry" = T1."BaseEntry"
	
LEFT JOIN
	ODLN T2 ON T1."DocEntry" = T2."DocEntry"

LEFT JOIN 
	INV1 T3 ON T2."DocEntry" = T0."BaseEntry" and T1."TrgetEntry" = T3."DocEntry"
	
LEFT JOIN
	OINV T4 ON T0."DocEntry" = T4."DocEntry"

LEFT JOIN
              OUSR T5 ON T0."UserSign" = T5."USERID"

LEFT JOIN
              OUSR T6 ON T2."UserSign" = T6."USERID"

LEFT JOIN
              OUSR T7 ON T4."UserSign" = T7."USERID"
		

WHERE 
	T0."CANCELED" NOT IN ('Y')
	
	
Order by T0."DocDate"
6

te falta agregar estos puntos… el targettype con el objtype

7

Gracias Ares17000 pero en todos los Left Join??

8

Ok… en vez de darte la respuesta te explicaré que haces ahí (me gusta mas cuando la persona sabe que está haciendo y porqué en vez de darle una solución y ya).

Cuando haces el Left Join le estas diciendo al query “Todo lo que esté aquí aunque no esté aca”… en otras palabras, te aseguras que exista el documento base, aunque NO TENGA un documento destino (ejemplo, un pedido que aún no tiene entrada, o una entrada que aún no tiene factura).

Ahora bien, cuando colocas:

RDR1 T0 LEFT JOIN ODLN T1 
ON T0.TrgetEntry = T1.DocEntry

Le estás diciendo al sistema: “Número de Documento Objetivo, tiene que ser igual al Numero de documento entrada”.

Luego la segunda parte:

AND T0.TargetType = T1.ObjType

Le estás diciendo al Query: El TIPO de objeto Target debe ser igual al Typo de Objeto… Para SAP cada transacción se trabaja como un Objeto, (ObjType), así, podrás ver que la facturas tienen un número de ObjType distinto a la entrega, a la NC, a los pedidos y demás.

Entonces… ¿Donde necesitarías los Targettype y OBJType.

Pues, depende de tu proceso, Si tu proceso es “Pedido - Entrada - Factura” entonces necesitas que el targettype del pedido sea igual al objtype de la entrada. y que el TargetType de la entrada sea igual al ObjType de la factura.

Si tu proceso fuera distinto, digamos, “Pedido - Factura” entonces el TargetType de tu pedido tendría que ser = al ObjType de la factura…

y es ahí donde te debes hacer la pregunta, ¿Quien necesitas validar que el tipo de objeto “objetivo” sea igual al tipo de Objeto “creado”? y en esas vas a colocar esa validación…

Ve haciendo pruebas, y descubre lo hermoso de los querys xD (aunque nos den mil dolores de cabeza)

1 me gusta
9

Voy a intentarlo y te comento como me fue.

1 me gusta
10

Otra consulta que no me queda muy clara en el ejemplo me hablas de

RDR1 T0 LEFT JOIN ODLN T1 
ON T0.TrgetEntry = T1.DocEntry

pero viendo mis LeftJoin
Los que tengo no inician con TrgetEntry sino con DocEntry será que por hay anda el asunto ?

11

Buen Día.

Te explico rápido, primero recuerda el orden que tenemos en sap primero se genera la Oferta de ventas aquí usamos principalmente 2 tablas que son OQUT y QUT1 la primera trae los datos del encabezado y la segunda el detalle del documento, ambas están unidas por el DocEntry, después esto se convierte en una entregas que están en las tablas de ORDR y RDR1 y estas seran Facturas que estan en OINV y en INV1 cada par se liga como te mencione con DocEntry.

tomando este orden tenemos que cada documento queda ligado asi:
OQUT T0 INNER JOIN QUT1 T1 ON T0."DocEntry" = T1."DocEntry"
ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"

ahora como queremos ligar los documentos debemos seguir ese orden y para poderlo hacer te comento que en las tablas que termina en 1 tenemos 2 campos que son muy importantes para poderlo hacer 1 es el TrgetEntry y el otro es el TargetType que contienen si existe, el DocEntry del documento destino y el tipo de documento respectivamente.

Tomando esto en cuenta tenemos entonces que si quieres unir las ofertas de venta,entregas y facturas cada uno lo vas a tener que ligar con un left join por los campos TrgetEntry y TargetType usando las tablas que tienen 1 y después ligamos sus cabeceras. esto queda asi:

select distinct 'Factura'  as  "Tipo"  ,T0."CardCode", T0."CardName" ,  T0."DocEntry", 
T0."CANCELED" as "status Orden",  T0."DocNum" as "Numero Entrega" , 
T0."DocDate" as "Fecha de Entrega", T0."DocTotal", T0."DocTotalFC",T0."DocCur",T1."TargetType" as"destino",T1."TrgetEntry" as "EntryFact",T0."ShipToCode" as "Envio",
T1."BaseType" , T5."DocNum" as  "No deFactura", T5."DocDate"  as "Fecha Factura" , T1."ItemCode" as "CodigoProd",
T5."CANCELED" as "status Fac", T3."DocEntry" ,T5."DocTotal" as "totfactmx", T5."DocTotalFC" as "totfactus",T5."DocCur" as "monfact",
T3."DocNum" as "Autorizacion de Salida" , T3."DocDate" as "Fecha Autorizacion", T3."CANCELED" as "Estatus Aut",
T3."DocTotal"  as "totsalmx", T3."DocTotalFC" as "totsalus", T3."DocCur" as "monsal",
T4."TargetType" as "DESTFACT", T4."TrgetEntry" AS "NCEntry",T3."NumAtCard" as "NumOC",
T2."DocNum" as "No deNC", T2."DocTotal" as "monto de NC",T2."DocTotalFC" AS "MonNdCusd",
T6."Dscription" as "Descr", T6."Quantity" as "Canti",T6."UomCode" as "unidad"

from OQUT T0 
inner join QUT1 T1 on T1."DocEntry" = T0."DocEntry" and  T1."BaseType" in('17') and T1."TargetType" in ('13','15','-1') 
inner join ORDR T3 on T3."DocEntry" = T1."BaseEntry"
inner join RDR1 T6 on T3."DocEntry" = T6."DocEntry" 
left join INV1 T4 on T1."TrgetEntry" = T4."DocEntry" 
left  join OINV T5 on T5."DocEntry" = T4."DocEntry" 
LEFT JOIN ORIN T2 ON T4."TrgetEntry" = T2."DocEntry"

creo que solo es cuestión de que le entiendas la lógica que estamos usando para que le entiendas como es que saco la información de cada tabla y espero que con esta explicación quede claro y no tengas mas confusión.

2 Me gusta
12

Amigos ya esta solucionado.

Agradezco enormemente la ayuda. No fue facil pero lo logre.

Muchas gracias.