Hola buenas tardes.
me están pidiendo un reporte para hacer cierres en SAP, y me gustaría que me ayudaran a saber de donde Obtengo el numero de cierre.
Hola @nidia
Es importante tener en cuenta que en el estandar de SAP, no existen procesos de apertura y cierre de caja. por lo cual si manejas funcionalidades de punto de venta, debe ser mediante un addon. O para ti el cierre de caja es el movimiento completo del día?
Andres Ramirez Jaramillo 
Hola gracias por responder, si se que SAP no tiene esa Funcionalidad, lo que se quiere es ver todos los movimientos del día.
Supongo que tampoco tiene las opción de arqueo de caja.
Puedes con campos usuarios adecuar ese tipo de cierre donde hagas un match entre documentos y pagos recibidos.
Hola buen dia
para el tema de puntos de venta te doy mis comentarios
- Crear SN (VentaMostrador)
-Crear cuenta de Caja (cajón Activos)
-En el usuario , en los valores propuestos configurar lo siguiente:
almacén por defecto , SN del mostrador y cuenta de caja asociada
-Habilitarle en ventas , Factura mas pago
de esta forma siempre al momento de facturar el sistema le exigirá registrar el pago sea el método que sea
despues se tendria que realizar el deposito que seria cargar a bancos con abono a cajas (Departamento de ingresos registra el deposito)
tendrías que crear dos reportes muy sencillos corte de ventas y corte de cajas (la condición seria por series o almacenes)
corte de ventas
tu reporte seria Ventas menos cancelaciones o notas de crédito
corte de cajas
Depósitos aplicados a tu cuenta de bancos vs lo que sale de tu cajas menos cancelaciones
el saldo de esta debera quedar en 0 a menos que tengas ese efectivo fisicamente en tu punto de venta
ya para finalizar podrias utilizar las conciliaciones bancarias de SAP donde ingresas los debitos y creditos de la banca vs los registros de depositos en SAP
Asi es como he manejado sucursales o puntos de venta en mexico
Saludos
2 Me gusta
Hola te envio una consulta que cree para un reporte de Crystal la que muestra los movimientos en una fecha en una serie, espero te sirva.
select * from
(
/* pagos al dia */
SELECT DISTINCT
"OINV"."DocNum" As "NumFact",
"OINV"."DocDate" as "fechaFact",
"OINV"."CardCode",
"OINV"."CardName",
"OINV"."DocTotal",
"EFECT"."DocNum" AS "NumRC",
"OINV"."Series" as "seriefact",
"OINV"."DocEntry",
"NNM1"."Remark",
"OINV"."VatSum",
"OINV"."Comments",
"ORCT"."DocDate" as "FechaRC",
"EFECT"."Canceled",
"EFECT"."JrnlMemo",
"ORCT"."Comments" AS "Commentsrc",
ifnull("EFECT"."SumApplied",0) as "SumApplied",
ifnull("EFECT"."CreditSum",0) as "CreditSum" ,
ifnull("EFECT"."CashSum",0) as "CashSum",
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."CashAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) as "CashAcct" ,
"ORCT"."CheckSum",
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."CheckAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) as "CheckAcct",
ifnull("ORCT"."TrsfrSum",0),
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."TrsfrAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) AS "TrsfrAcct" ,
ifnull("TARCR"."VALORTC",0) ,
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM bdpalacio.OACT LEFT JOIN BDPALACIO.ORCT
ON "TARCR"."CreditAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) AS CreditAcct,
"ORCT"."Series" AS "SERIERC",
"NOTAC"."DocNum" as "Notacre",
ifnull("NOTAC"."DocTotal",0) AS "VlrNC",
"ORCT"."UserSign",
"OUSR"."USER_CODE",
"CUENTA"."AcctName",
"ORCT"."NoDocSum" as "Pagoacuenta",
'ACT' AS "TIPO"
FROM "OINV" "OINV" INNER JOIN "NNM1" "NNM1" ON "OINV"."Series"="NNM1"."Series"
-- mostrar encabezado y efectivo del pago de una factura
LEFT JOIN (select "ORCT"."DocNum"
,"ORCT"."CashSum"
, "RCT2"."SumApplied"
,"ORCT"."CreditSum"
, "RCT2"."DocEntry"
,"ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"RCT2"."InvType"
,"RCT2"."DocNum" as "KEYRC"
from "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
inner JOIN "OINV" "OINV" ON "RCT2"."DocEntry"="OINV"."DocEntry"
and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@}) ) AS efect ON "EFECT"."DocEntry"="OINV"."DocEntry"
LEFT JOIN "ORCT" "ORCT" ON "ORCT"."DocNum"="EFECT"."DocNum" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
LEFT JOIN ( select "RCT3"."DocNum", "RCT3"."CreditAcct", sum("RCT3"."FirstSum") VALORTC
from "ORCT"
inner JOIN "RCT3" "RCT3" ON "ORCT"."DocEntry"="RCT3"."DocNum"
group by "RCT3"."DocNum", "RCT3"."CreditAcct") TARCR ON "TARCR"."DocNum" = "ORCT"."DocEntry" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
LEFT JOIN ( SELECT T0."DocNum"
, T0."DocDate"
, T0."CardCode"
, T0."CardName"
, T1."CheckNum"
, T1."BankCode"
, T1."CheckSum"
, T1."CheckAct"
, T3."DocNum" AS "NumFact"
FROM OINV T3 INNER JOIN ORCT T0 ON T3."ReceiptNum" =T0."DocEntry"
INNER JOIN RCT1 T1 ON T0."DocEntry" = T1."DocNum" ) AS CHEQUES ON "CHEQUES". "NumFact" = "OINV"."DocNum"
left JOIN (select
"RIN1"."BaseEntry",
"ORIN"."DocNum",
"ORIN"."Series",
"ORIN"."DocStatus",
"ORIN"."DocDate",
"ORIN"."DocTotal"
from "ORIN" "ORIN"
INNER JOIN "RIN1" "RIN1" ON "ORIN"."DocEntry" = "RIN1"."DocEntry"
where "RIN1"."BaseEntry" is not null
and ("ORIN"."DocDate">={?fecha@} AND "ORIN"."DocDate"<={?fecha@})
order by 2 ) "NOTAC" ON "OINV"."DocEntry" ="NOTAC"."BaseEntry"
LEFT JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 WHERE T0."Finanse" ='Y' ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."TrsfrAcct"= "CUENTA"."AcctCode"
WHERE
("OINV"."DocDate">={?fecha@} AND "OINV"."DocDate"<={?fecha@})
AND "NNM1"."Series" = '{?serie@}' )
--/* FIN pagos al dia */
UNION
/* para sacar pagos a fact anteriores*/
( SELECT DISTINCT
"OINV"."DocNum" As "NumFact",
"OINV"."DocDate" as "fechaFact",
"OINV"."CardCode",
"OINV"."CardName",
"OINV"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"OINV"."Series" as "seriefact",
"OINV"."DocEntry",
"NNM1"."Remark",
"OINV"."VatSum",
"OINV"."Comments",
"ORCT"."DocDate" as "FechaRC",
ifnull("EFECT"."Canceled",'N') "Canceled" ,
"EFECT"."JrnlMemo",
"ORCT"."Comments" AS "Commentsrc",
ifnull("EFECT"."SumApplied",0) as "SumApplied",
ifnull("EFECT"."CreditSum",0) as "CreditSum" ,
ifnull("EFECT"."CashSum",0) as "CashSum",
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM bdpalacio.OACT LEFT JOIN BDPALACIO.ORCT
ON "ORCT"."CashAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) as "CashAcct" ,
ifnull("ORCT"."CheckSum",0),
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM bdpalacio.OACT LEFT JOIN BDPALACIO.ORCT
ON "ORCT"."CheckAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) as "CheckAcct",
ifnull("ORCT"."TrsfrSum",0),
( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM bdpalacio.OACT LEFT JOIN BDPALACIO.ORCT
ON "ORCT"."TrsfrAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="EFECT"."KEYRC" ) AS "TrsfrAcct" ,
ifnull("TARCR"."VALORTC",0) ,
"TARCR"."CreditAcct",
"ORCT"."Series"AS "SERIERC",
"NOTAC"."DocNum" as "Notacre",
ifnull("NOTAC"."DocTotal",0) AS "VlrNC",
"ORCT"."UserSign",
"OUSR"."USER_CODE",
"CUENTA"."AcctName",
"ORCT"."NoDocSum" as "Pagoacuenta",
'ANT' AS "TIPO"
FROM "OINV" "OINV" INNER JOIN "NNM1" "NNM1" ON "OINV"."Series"="NNM1"."Series"
-- mostrar encabezado y efectivo del pago de una factura
inner JOIN (select "ORCT"."DocNum"
,"ORCT"."CashSum"
, "RCT2"."SumApplied"
,"ORCT"."CreditSum"
, "RCT2"."DocEntry"
,"RCT2"."ObjType"
,"ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"RCT2"."InvType"
,"RCT2"."DocNum" as "KEYRC"
from "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
) AS efect ON "EFECT"."DocEntry"="OINV"."DocEntry"
LEFT JOIN "ORCT" "ORCT" ON "ORCT"."DocNum"="EFECT"."DocNum" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
LEFT JOIN
( select "RCT3"."DocNum", "RCT3"."CreditAcct",
sum("RCT3"."FirstSum") VALORTC
from "ORCT"
inner JOIN "RCT3" "RCT3" ON "ORCT"."DocEntry"="RCT3"."DocNum"
group by "RCT3"."DocNum", "RCT3"."CreditAcct") TARCR ON "TARCR"."DocNum" = "ORCT"."DocEntry" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
left JOIN (select
"RIN1"."BaseEntry",
"ORIN"."DocNum",
"ORIN"."Series",
"ORIN"."DocStatus",
"ORIN"."DocDate",
"ORIN"."DocTotal"
from "ORIN" "ORIN"
INNER JOIN "RIN1" "RIN1" ON "ORIN"."DocEntry" = "RIN1"."DocEntry"
where "RIN1"."BaseEntry" is not null
and ("ORIN"."DocDate">={?fecha@} AND "ORIN"."DocDate"<={?fecha@})
order by 2 ) "NOTAC" ON "OINV"."DocEntry" ="NOTAC"."BaseEntry"
LEFT JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 WHERE T0."Finanse" ='Y' ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
WHERE
("OINV"."DocDate">={ts '2010-01-01 00:00:00'} AND "OINV"."DocDate"<={?fecha@})
AND "NNM1"."Series" = {?serie@}
AND DAYS_BETWEEN ("OINV"."DocDate", "ORCT"."DocDate") <> 0
and "ORCT"."Series" =CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
END )
/* FIN para sacar pagos a fact anteriores*/
UNION
/* pagos a cuenta */
(
select distinct
null As "NumFact",
null as "fechaFact",
"ORCT"."CardCode",
"ORCT"."CardName",
"ORCT"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"ORCT"."Series" as "seriefact",
null AS "DocEntry",
"NNM1"."Remark",
null AS "VatSum"
,"ORCT"."Comments"
,"ORCT"."DocDate" as "FechaRC"
, "ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"ORCT"."Comments" AS "Commentsrc"
,"ORCT"."NoDocSum" as "SumApplied"
,"ORCT"."CreditSum"
,"ORCT"."CashSum"
, ( SELECT CAST(OACT."AcctName" AS VARCHAR) FROM bdpalacio.OACT LEFT JOIN BDPALACIO.ORCT
ON "ORCT"."CashAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"="RCT2"."DocNum" ) as "CashAcct"
,ifnull("ORCT"."CheckSum",0)
,"ORCT"."CheckAcct"
,ifnull("ORCT"."TrsfrSum",0)
, (SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."TrsfrAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"= "RCT2"."DocNum") as "TrsfrAcct"
,ifnull("RCT3"."FirstSum",0) VALORTC
, "RCT3"."CreditAcct"
,"ORCT"."Series"AS "SERIERC"
, null as "Notacre"
,null AS "VlrNC"
,"ORCT"."UserSign"
,"OUSR"."USER_CODE"
,"CUENTA"."AcctName"
,"ORCT"."NoDocSum" as "Pagoacuenta"
,'ADE' AS "TIPO"
from "ORCT" "ORCT"
INNER JOIN "NNM1" "NNM1" on "ORCT"."Series" = "NNM1"."Series"
inner JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
left JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
left JOIN "RCT3" "RCT3" ON "ORCT"."DocEntry"="RCT3"."DocNum"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
where ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
and "ORCT"."NoDocSum" <> 0 and "ORCT"."Canceled" <> 'Y'
and "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END
group by "ORCT"."CardCode"
,"ORCT"."CardName"
,"ORCT"."DocTotal"
,"ORCT"."DocNum"
,"ORCT"."Series"
,"NNM1"."Remark"
,"ORCT"."Comments"
,"ORCT"."DocDate"
, "ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"RCT2"."SumApplied"
,"ORCT"."CreditSum"
,"ORCT"."CashSum"
,"ORCT"."CashAcct"
,"ORCT"."CheckSum"
,"ORCT"."CheckAcct"
,"ORCT"."TrsfrSum"
,"ORCT"."TrsfrAcct"
,"RCT3"."FirstSum"
, "RCT3"."CreditAcct"
,"ORCT"."Series"
,"ORCT"."UserSign"
,"OUSR"."USER_CODE"
,"CUENTA"."AcctName"
,"ORCT"."NoDocSum"
, "ORCT"."Comments"
,"RCT2"."DocNum" )
UNION
/* para sacar pagos Notas Debito*/
(select
null As "NumFact",
null as "fechaFact",
"ORCT"."CardCode",
"ORCT"."CardName",
"ORCT"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"ORCT"."Series" as "seriefact",
null AS "DocEntry",
"NNM1"."Remark",
null AS "VatSum"
,"ORCT"."Comments"
,"ORCT"."DocDate" as "FechaRC"
, "ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"ORCT"."Comments" AS "Commentsrc"
, (case when "RCT2"."InvType"= '14'
then "RCT2"."SumApplied" *-1
else "RCT2"."SumApplied"
end ) as "SumApplied"
,"ORCT"."CreditSum"
,"ORCT"."CashSum"
,"ORCT"."CashAcct"
,ifnull("ORCT"."CheckSum",0)
,"ORCT"."CheckAcct"
,ifnull("ORCT"."TrsfrSum",0)
,"ORCT"."TrsfrAcct"
,0 as VALORTC
, '' as "CreditAcct"
,"ORCT"."Series"AS "SERIERC"
, null as "Notacre"
,null AS "VlrNC"
,"ORCT"."UserSign"
,"OUSR"."USER_CODE"
,"CUENTA"."AcctName"
,"ORCT"."NoDocSum" as "Pagoacuenta"
,'NDE' AS "TIPO"
from "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
INNER JOIN "NNM1" "NNM1" on "ORCT"."Series" = "NNM1"."Series"
inner JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
where
("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
and "RCT2"."InvType" = 14
and "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END )
UNION
/* para sacar Anticipos Aplicados utilizados RCT2"."InvType" = 24 PAP**/
(select
null As "NumFact",
null as "fechaFact",
"ORCT"."CardCode",
"ORCT"."CardName",
"ORCT"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"ORCT"."Series" as "seriefact",
null AS "DocEntry",
"NNM1"."Remark",
null AS "VatSum"
,"ORCT"."Comments"
,"ORCT"."DocDate" as "FechaRC"
, "ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"ORCT"."Comments" AS "Commentsrc"
, (case when "RCT2"."InvType"= '14'
then "RCT2"."SumApplied" *-1
else "RCT2"."SumApplied"
end ) as "SumApplied"
,"ORCT"."CreditSum"
,"ORCT"."CashSum"
,"ORCT"."CashAcct"
,ifnull("ORCT"."CheckSum",0)
,"ORCT"."CheckAcct"
,ifnull("ORCT"."TrsfrSum",0)
, (SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."TrsfrAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"= "RCT2"."DocNum") as "TrsfrAcct"
,0 as VALORTC
, '' as "CreditAcct"
,"ORCT"."Series"AS "SERIERC"
, null as "Notacre"
,null AS "VlrNC"
,"ORCT"."UserSign"
,"OUSR"."USER_CODE"
,"CUENTA"."AcctName"
,"ORCT"."NoDocSum" as "Pagoacuenta"
,'PAP' AS "TIPO"
from "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
INNER JOIN "NNM1" "NNM1" on "ORCT"."Series" = "NNM1"."Series"
inner JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
where
("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
and "RCT2"."InvType" = 24
and "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END )
UNION
/* SACA NCR CLIENTE */
(SELECT DISTINCT
"OINV"."DocNum" As "NumFact",
"OINV"."DocDate" as "fechaFact",
"OINV"."CardCode",
"OINV"."CardName",
"OINV"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"OINV"."Series" as "seriefact",
"OINV"."DocEntry",
"NNM1"."Remark",
"OINV"."VatSum",
"OINV"."Comments",
"ORCT"."DocDate" as "FechaRC",
"EFECT"."Canceled",
"EFECT"."JrnlMemo",
"ORCT"."Comments" AS "Commentsrc",
ifnull("EFECT"."SumApplied",0) as "SumApplied",
ifnull("EFECT"."CreditSum",0) as "CreditSum" ,
ifnull("EFECT"."CashSum",0) as "CashSum",
"ORCT"."CashAcct",
ifnull("ORCT"."CheckSum",0),
"ORCT"."CheckAcct",
ifnull("ORCT"."TrsfrSum",0),
"ORCT"."TrsfrAcct",
ifnull("TARCR"."VALORTC",0) ,
"TARCR"."CreditAcct",
"ORCT"."Series" AS "SERIERC",
"NOTAC"."DocNum" as "Notacre",
ifnull("NOTAC"."DocTotal",0) AS "VlrNC",
"ORCT"."UserSign",
"OUSR"."USER_CODE",
"CUENTA"."AcctName",
"ORCT"."NoDocSum" as "Pagoacuenta",
'NCR' AS "TIPO"
FROM "OINV" "OINV" INNER JOIN "NNM1" "NNM1" ON "OINV"."Series"="NNM1"."Series"
LEFT JOIN (select "RCT2"."DocNum"
,"ORCT"."CashSum"
, "RCT2"."SumApplied"
,"ORCT"."CreditSum"
, "RCT2"."DocEntry"
,"ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"RCT2"."InvType"
FROM "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
inner JOIN "OINV" "OINV" ON "RCT2"."DocEntry"="OINV"."DocEntry"
and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
) AS efect ON "EFECT"."DocEntry"="OINV"."DocEntry"
LEFT JOIN "ORCT" "ORCT" ON "ORCT"."DocNum"="EFECT"."DocNum" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
LEFT JOIN
( select "RCT3"."DocNum", "RCT3"."CreditAcct",
sum("RCT3"."FirstSum") VALORTC
FROM "ORCT"
inner JOIN "RCT3" "RCT3" ON "ORCT"."DocEntry"="RCT3"."DocNum"
group by "RCT3"."DocNum", "RCT3"."CreditAcct") TARCR ON "TARCR"."DocNum" = "ORCT"."DocEntry" and ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
left JOIN (select
"RIN1"."BaseEntry",
"ORIN"."DocNum",
"ORIN"."Series",
"ORIN"."DocStatus",
"ORIN"."DocDate",
"ORIN"."DocTotal"
from "ORIN" "ORIN"
INNER JOIN "RIN1" "RIN1" ON "ORIN"."DocEntry" = "RIN1"."DocEntry"
where "RIN1"."BaseEntry" is not null
and ("ORIN"."DocDate">={?fecha@} AND "ORIN"."DocDate"<={?fecha@})
order by 2 ) "NOTAC" ON "OINV"."DocEntry" ="NOTAC"."BaseEntry"
LEFT JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
WHERE ("OINV"."DocDate">={?fecha@} AND "OINV"."DocDate"<={?fecha@})
AND "NNM1"."Series" = '217'
and "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END )
UNION
/* PARA MOSTRAR LAS FACTURAS DE OTRAS SEDES */
(SELECT DISTINCT
"OINV"."DocNum" As "NumFact",
"OINV"."DocDate" as "fechaFact",
"OINV"."CardCode",
"OINV"."CardName",
"OINV"."DocTotal",
"OTROS"."DocNum" AS "NumRC",
"OINV"."Series" as "seriefact",
"OINV"."DocEntry",
"NNM1"."Remark",
"OINV"."VatSum",
"OINV"."Comments",
"OTROS"."DocDate" as "FechaRC",
"OTROS"."Canceled",
"OTROS"."JrnlMemo",
"Commentsrc",
ifnull("OTROS"."SumApplied",0) as "SumApplied",
ifnull("OTROS"."CreditSum",0) as "CreditSum" ,
ifnull("OTROS"."CashSum",0) as "CashSum",
"OTROS"."CashAcct",
ifnull("OTROS"."CheckSum",0),
"OTROS"."CheckAcct",
ifnull("OTROS"."TrsfrSum",0),
"OTROS"."TrsfrAcct",
ifnull("TARCR"."VALORTC",0) ,
"TARCR"."CreditAcct",
"OTROS"."SERIESR" AS "SERIERC",
NULL as "Notacre",
0 AS "VlrNC",
"OTROS"."UserSign",
"OUSR"."USER_CODE",
"CUENTA"."AcctName",
"OTROS"."NoDocSum" as "Pagoacuenta",
'ATR' AS "TIPO"
FROM
bdpalacio."OINV" "OINV" INNER JOIN
(SELECT
"ORCT"."DocNum"
,"ORCT"."DocEntry" as DocEntryr
,"OINV"."DocNum" AS FacNum
, "RCT2"."InvType"
, "OINV"."Series"
, "ORCT"."DocDate"
, "ORCT"."Canceled"
, "ORCT"."JrnlMemo"
,"ORCT"."Comments" AS "Commentsrc"
,"ORCT"."CashSum"
, "RCT2"."SumApplied"
,"ORCT"."CreditSum"
, "RCT2"."DocEntry"
,"ORCT"."CashAcct"
,"ORCT"."CheckSum"
,"ORCT"."CheckAcct"
,"ORCT"."TrsfrSum"
, (SELECT CAST(OACT."AcctName" AS VARCHAR) FROM OACT LEFT JOIN ORCT
ON "ORCT"."TrsfrAcct"= "OACT"."AcctCode" WHERE OACT."Finanse" ='Y' AND "ORCT"."DocEntry"= "RCT2"."DocNum") AS "TrsfrAcct"
,"ORCT"."Series" as SERIESR
,"ORCT"."UserSign"
,"ORCT"."NoDocSum"
FROM "ORCT" "ORCT" INNER JOIN "NNM1" ON "ORCT"."Series" ="NNM1"."Series"
INNER JOIN bdpalacio."RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
left JOIN bdpalacio."OINV" "OINV" ON "RCT2"."DocEntry"="OINV"."DocEntry"
WHERE ("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
AND "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END
AND "RCT2"."InvType" = 13
AND "OINV"."Series" <> '{?serie@}' ) AS OTROS ON "OINV"."DocNum" = "OTROS"."FACNUM"
LEFT JOIN
( select "RCT3"."DocNum", "RCT3"."CreditAcct",
sum("RCT3"."FirstSum") VALORTC
from "ORCT"
inner JOIN "RCT3" "RCT3" ON "ORCT"."DocEntry"="RCT3"."DocNum"
group by "RCT3"."DocNum", "RCT3"."CreditAcct") TARCR ON "TARCR"."DocNum" = "OTROS"."DOCENTRYR"
LEFT JOIN "OUSR" "OUSR" ON "OTROS"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "OTROS"."CashAcct"= "CUENTA"."AcctCode"
INNER JOIN bdpalacio."NNM1" "NNM1" ON "OINV"."Series"="NNM1"."Series")
union
(select
/* para sacar Recibos de Caja, que corresponde a PROVEEDORES*/
null As "NumFact",
null as "fechaFact",
"ORCT"."CardCode",
"ORCT"."CardName",
"ORCT"."DocTotal",
"ORCT"."DocNum" AS "NumRC",
"ORCT"."Series" as "seriefact",
null AS "DocEntry",
"NNM1"."Remark",
null AS "VatSum"
,"ORCT"."Comments"
,"ORCT"."DocDate" as "FechaRC"
, "ORCT"."Canceled"
,"ORCT"."JrnlMemo"
,"ORCT"."Comments" AS "Commentsrc"
, (case when "RCT2"."InvType"= '14'
then "RCT2"."SumApplied" *-1
else "RCT2"."SumApplied"
end ) as "SumApplied"
,"ORCT"."CreditSum"
,"ORCT"."CashSum"
,"ORCT"."CashAcct"
,ifnull("ORCT"."CheckSum",0)
,"ORCT"."CheckAcct"
,ifnull("ORCT"."TrsfrSum",0)
,"ORCT"."TrsfrAcct"
,0 as VALORTC
, '' as "CreditAcct"
,"ORCT"."Series"AS "SERIERC"
, null as "Notacre"
,null AS "VlrNC"
,"ORCT"."UserSign"
,"OUSR"."USER_CODE"
,"CUENTA"."AcctName"
,"ORCT"."NoDocSum" as "Pagoacuenta"
,'RCP' AS "TIPO"
from "ORCT" "ORCT"
INNER JOIN "RCT2" "RCT2" ON "ORCT"."DocEntry"="RCT2"."DocNum"
INNER JOIN "NNM1" "NNM1" on "ORCT"."Series" = "NNM1"."Series"
inner JOIN "OUSR" "OUSR" ON "ORCT"."UserSign" = "OUSR"."USERID"
LEFT JOIN (SELECT T0."AcctCode", T0."AcctName", T0."Finanse" FROM OACT T0 ORDER BY T0."AcctCode") CUENTA
ON "ORCT"."CashAcct"= "CUENTA"."AcctCode"
where
("ORCT"."DocDate">={?fecha@} AND "ORCT"."DocDate"<={?fecha@})
and "RCT2"."InvType" in (19,46)
and "ORCT"."Series" = CASE {?serie@}
WHEN '202' then '302'
WHEN '210' then '271'
WHEN '204' THEN '268'
WHEN '213' THEN '267'
WHEN '307' THEN '315'
WHEN '306' THEN '317'
WHEN '205' THEN '259'
WHEN '212' THEN '258'
WHEN '207' THEN '255'
WHEN '215' THEN '252'
WHEN '203' THEN '270'
WHEN '211' THEN '269'
WHEN '206' THEN '257'
WHEN '214' THEN '256'
END )
Este tema se cerró automáticamente 91 días después de la última publicación. No se permiten nuevas respuestas.