Creacion de Kardex fisico valorado en Material Controls Micros Fidelio

create or replace PROCEDURE SP_KARDEX_FISICO_VALORADO(

vn_kst_id  IN MCDATA.KOSTST.KST_ID%TYPE,

vn_art_id  IN MCDATA.ARTIKEL.ART_ID%TYPE,

vs_tipo    IN CHAR,

vs_fec_ini IN varchar2,

vs_fec_fin IN varchar2,

P_OUT_ID_GTT_KARDEX OUT NUMBER

)

IS

 

———————————————————————————————–

— PROCEDIMIENTO DE GENERACION DE KARDEX FISICO VALORADO

— VERSION     : Micros Fidelio :: Materials Control v 8.6.5.10

———————————————————————————————–

 

CURSOR SC_ARTICULOS IS

–COMPRAS O DEVOLUCIONES (10)

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’01’ AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       L.lfs_datum WES_DOCDATE,

       DECODE(L.LFS_STATUS,42,

             (CASE TRIM(SUBSTR(TRIM(MCDATA.F_FACT_NAME(L.LFS_ID)),1,3))

               WHEN ‘F’   THEN ’01’ –FACTURA

               WHEN ‘B’   THEN ’03’ –BOLETA DE VENTA

               WHEN ‘LC  THEN ’04’ –LIQUIDACIÓN DE COMPRA

               WHEN ‘NCP’ THEN ’07’ –NOTA DE CREDITO

               WHEN ‘NDP’ THEN ’08’ –NOTA DE DEBITO              

               WHEN ‘GRE’ THEN ’09’ –GUÍA DE REMISIÓN Remitente

               WHEN ‘TK  THEN ’12’ –TICKET

               WHEN ‘RET’ THEN ’20’ –Retencion

               WHEN ‘GT  THEN ’31’ –GUÍA DE REMISIÓN Transportista

               WHEN ‘OT  THEN ’99’ –Otros

               WHEN ‘I’   THEN ’16’ –INVEMTARIO

               ELSE ’01’

              END),

             (CASE TRIM(SUBSTR(TRIM(L.LFS_NAME),1,3))

               WHEN ‘F’   THEN ’01’ –FACTURA

               WHEN ‘B’   THEN ’03’ –BOLETA DE VENTA

               WHEN ‘LC  THEN ’04’ –LIQUIDACIÓN DE COMPRA

               WHEN ‘NCP’ THEN ’07’ –NOTA DE CREDITO

               WHEN ‘NDP’ THEN ’08’ –NOTA DE DEBITO              

               WHEN ‘GRE’ THEN ’09’ –GUÍA DE REMISIÓN Remitente

               WHEN ‘TK  THEN ’12’ –TICKET

               WHEN ‘RET’ THEN ’20’ –Retencion

               WHEN ‘GT  THEN ’31’ –GUÍA DE REMISIÓN Transportista

               WHEN ‘OT  THEN ’99’ –Otros

               WHEN ‘I’   THEN ’16’ –INVEMTARIO

               ELSE ’01’

              END))AS TIPO_DOC,

       DECODE(L.LFS_STATUS,42,SUBSTR(MCDATA.F_FACT_NAME(L.LFS_ID),2,3),SUBSTR(L.LFS_NAME,2,3)) AS SERIE_DOC,

       DECODE(L.LFS_STATUS,42,SUBSTR(MCDATA.F_FACT_NAME(L.LFS_ID),6,LENGTH(MCDATA.F_FACT_NAME(L.LFS_ID))),SUBSTR(L.LFS_NAME,6,LENGTH(L.LFS_NAME))) AS NRO_DOC,

       ROUND(I.WES_MENGE,4) AS CANT_COMPRA,

       ROUND(I.WES_ESP,4) AS COST_UNI_COMPRA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_COMPRA,

       0.00 AS CANT_VENTA,

       0.00 AS COST_UNI_VENTA,

       0.00 AS COST_TOT_VENTA,

       I.WES_VKP,

       L.LFS_STATUS,

       (SELECT NVL(COUNT(1),0)

                 FROM MCDATA.LIEFERPOS T

                 WHERE T.LFP_MENGE < 0 AND T.LFP_STATUS <> 64

                   AND T.LFS_ID = L.LFS_ID) AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.LIEFERSCHEIN L

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_ID1 = L.LFS_ID )

   AND ( I.WES_TYP = 10 )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ((L.lfs_datum >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (L.lfs_datum <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

 

UNION ALL

 

— COMPRAS EN CONTABILIDAD

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’02’AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       RE.RNG_DATUM WES_DOCDATE,

       (CASE TRIM(SUBSTR(TRIM(RE.RNG_NAME) ,1,3))

               WHEN ‘F’   THEN ’01’ –FACTURA

               WHEN ‘B’   THEN ’03’ –BOLETA DE VENTA

               WHEN ‘LC  THEN ’04’ –LIQUIDACIÓN DE COMPRA

               WHEN ‘NCP’ THEN ’07’ –NOTA DE CREDITO

               WHEN ‘NDP’ THEN ’08’ –NOTA DE DEBITO              

               WHEN ‘GRE’ THEN ’09’ –GUÍA DE REMISIÓN Remitente

               WHEN ‘TK’  THEN ’12’ –TICKET

               WHEN ‘RET’ THEN ’20’ –Retencion

               WHEN ‘GT  THEN ’31’ –GUÍA DE REMISIÓN Transportista

               WHEN ‘OT’  THEN ’99’ –Otros

               WHEN ‘I’   THEN ’16’ –INVEMTARIO

               ELSE ’01’

              END)AS TIPO_DOC,

       SUBSTR(RE.RNG_NAME,INSTR(RE.RNG_NAME,‘ ‘),INSTR(RE.RNG_NAME,‘-‘)-INSTR(RE.RNG_NAME,‘ ‘)) AS SERIE_DOC,

       SUBSTR(RE.RNG_NAME,INSTR(RE.RNG_NAME,‘-‘)+1,LENGTH(RE.RNG_NAME)) AS NRO_DOC,

       ROUND(I.WES_MENGE,4) AS CANT_COMPRA,

       ROUND(I.WES_ESP,4) AS COST_UNI_COMPRA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_COMPRA,

       0.00 AS CANT_VENTA,

       0.00 AS COST_UNI_VENTA,

       0.00 AS COST_TOT_VENTA,

       I.WES_VKP,

       RE.RNG_STATUS,

       (SELECT NVL(COUNT(1),0)

                 FROM MCDATA.LIEFERPOS T

                 WHERE T.LFP_MENGE < 0 AND T.LFP_STATUS <> 64

                   AND T.LFS_ID = I.WES_ID1) AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.RECHNUNG RE

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_TYP = 10 )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ( (RE.RNG_DATUM >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (RE.RNG_DATUM <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

 

UNION ALL

 

–TRASFERENCIAS DE  ALMACEN (2)

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’11’ AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       HL.LBW_DATUM WES_DOCDATE,

       ’00’ AS TIPO_DOC,

       TRIM(SUBSTR(HL.LBW_NAME,1,6)) AS SERIE_DOC,

       TRIM(HL.LBW_NAME) AS NRO_DOC,

       ROUND(I.WES_MENGE,4) AS CANT_COMPRA,

       ROUND(I.WES_ESP,4) AS COST_UNI_COMPRA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_COMPRA,

       0.00 AS CANT_VENTA,

       0.00 AS COST_UNI_VENTA,

       0.00 AS COST_TOT_VENTA,

       I.WES_VKP,

       HL.LBW_STATUS AS LFS_STATUS,

       0 AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.HIS_LAGERBEW HL

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_ID1 = HL.LBW_ID )

   AND ( I.WES_TYP=2 )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ((HL.LBW_DATUM >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (HL.LBW_DATUM <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

 

UNION ALL

 

–SALIDAS (1)

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’11’ AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       HL.LBW_DATUM WES_DOCDATE,

       ’00’ AS TIPO_DOC,

       TRIM(SUBSTR(HL.LBW_NAME,1,6)) AS SERIE_DOC,

       TRIM(HL.LBW_NAME) AS NRO_DOC,

       0.00 AS CANT_COMPRA,

       0.00 AS COST_UNI_COMPRA,

       0.00 AS COST_TOT_COMPRA,

       ROUND(I.WES_MENGE,4) AS CANT_VENTA,

       ROUND(I.WES_ESP,4) AS COST_UNI_VENTA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_VENTA,

       I.WES_VKP,

       HL.LBW_STATUS AS LFS_STATUS,

       0 AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.HIS_LAGERBEW HL

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_ID1 = HL.LBW_ID )

   AND ( I.WES_TYP =1 )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ((HL.LBW_DATUM >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (HL.LBW_DATUM <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

UNION ALL

 

— MERMAS

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’05’ AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       HV.VBR_DATUM WES_DOCDATE,

       ’00’ AS TIPO_DOC,

       TRIM(SUBSTR(VBR_NAME,1,7)) AS SERIE_DOC,

       HV.VBR_NAME AS NRO_DOC,

       0.00 AS CANT_COMPRA,

       0.00 AS COST_UNI_COMPRA,

       0.00 AS COST_TOT_COMPRA,

       ROUND(I.WES_MENGE,4) AS CANT_VENTA,

       ROUND(I.WES_ESP,4) AS COST_UNI_VENTA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_VENTA,

       I.WES_VKP,

       HV.VBR_STATUS AS LFS_STATUS,

       0 AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.HIS_VERBRAUCH HV

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_TYP IN (30,31) )

   AND ( I.WES_ID1 = HV.VBR_ID )

   AND ( I.KST_ID = HV.KST_ID )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ( (HV.VBR_DATUM >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (HV.VBR_DATUM <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

UNION ALL

–VENTAS

SELECT K.KST_ID,

       K.KST_ADRESSE,

       K.KST_NAME,

       I.ART_ID,

       A.ART_NUMMER AS ART_COD,

       A.ART_NAME,

       unidad_code_sunat(A.VPK_NR) VPK_NR,

       VP.VPK_NAME,

       A.ART_INFO,

       I.WES_TYP,

       ’10’ AS TIPO_OPE,

       I.WES_ID1,

       I.WES_ID2,

       T.TAT_DATUM WES_DOCDATE,

       ’00’ AS TIPO_DOC,

       TRIM(SUBSTR(T.TAT_NAME,1,6)) AS SERIE_DOC,

       T.TAT_NAME AS NRO_DOC,

       0.00 AS CANT_COMPRA,

       0.00 AS COST_UNI_COMPRA,

       0.00 AS COST_TOT_COMPRA,

       ROUND(I.WES_MENGE,4) AS CANT_VENTA,

       ROUND(I.WES_ESP,4) AS COST_UNI_VENTA,

       ROUND(I.WES_MENGE * I.WES_ESP,4) AS COST_TOT_VENTA,

       I.WES_VKP,

       AU.ATG_STATUS AS LFS_STATUS,

       0 AS LFP_STATUS

  FROM MCDATA.INVWES   I,

       MCDATA.ARTIKEL  A,

       MCDATA.VPCKEINH VP,

       MCDATA.KOSTST   K,

       MCDATA.TEILAUFTRAG T,

       MCDATA.AUFTRAG AU

 WHERE ( I.ART_ID = A.ART_ID )

   AND ( I.KST_ID = K.KST_ID )

   AND ( A.VPK_NR = VP.VPK_ID )

   AND ( I.WES_TYP = 21 )

   AND ( I.WES_ID1 = T.TAT_ID )

   AND ( T.ATG_ID = AU.ATG_ID )

   AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )

   AND ( K.KST_ID = vn_kst_id )

   AND ( (T.TAT_DATUM >= TO_DATE(vs_fec_ini||’00:00:00′, ‘DD/MM/YYYY HH24:MI:SS’))

   AND (T.TAT_DATUM <= TO_DATE(vs_fec_fin||’23:59:59′, ‘DD/MM/YYYY HH24:MI:SS’)))

 

UNION ALL

— AJUSTES DE INVENTARIO

    SELECT K.KST_ID,

           K.KST_ADRESSE,

           K.KST_NAME,

           I.ART_ID,

           A.ART_NUMMER AS ART_COD,

           A.ART_NAME,

           unidad_code_sunat(A.VPK_NR) VPK_NR,

           VP.VPK_NAME,

           A.ART_INFO,

           I.WES_TYP,

           ’99’ AS TIPO_OPE,

           I.WES_ID1,

           I.WES_ID2,

           IV.INV_DATUM WES_DOCDATE,

           ’00’ AS TIPO_DOC,

           TRIM(SUBSTR(INV_NAME,1,7)) AS SERIE_DOC,

           IV.INV_NAME AS NRO_DOC,

 

           DECODE(SIGN(I.WES_MENGE),1,ROUND(I.WES_MENGE,4),0.00) AS CANT_COMPRA,

           DECODE(SIGN(WES_MENGE),1,ROUND(I.WES_ESP,4),0.00) AS COST_UNI_COMPRA,

           DECODE(SIGN(ROUND(I.WES_MENGE * I.WES_ESP,4)),1,ROUND(I.WES_MENGE * I.WES_ESP,4),0.00) AS COST_TOT_COMPRA,

 

           DECODE(SIGN(I.WES_MENGE),-1,ROUND(I.WES_MENGE,4),0.00) AS CANT_VENTA,

           DECODE(SIGN(WES_MENGE),-1,ROUND(I.WES_ESP,4),0.00) AS COST_UNI_VENTA,

           DECODE(SIGN(ROUND(I.WES_MENGE * I.WES_ESP,4)),-1,ROUND(I.WES_MENGE * I.WES_ESP,4),0.00) AS COST_TOT_VENTA,

 

           I.WES_VKP,

           IV.INV_STATUS AS LFS_STATUS,

           0 AS LFP_STATUS

      FROM MCDATA.INVWES   I,

           MCDATA.INVENTUR IV,

           MCDATA.ARTIKEL  A,

           MCDATA.VPCKEINH VP,

           MCDATA.KOSTST   K

     WHERE ( I.ART_ID = A.ART_ID )

       AND ( I.KST_ID = K.KST_ID )

       AND ( A.VPK_NR = VP.VPK_ID )

       AND ( I.WES_ID1 = IV.INV_ID )

       AND ( I.WES_TYP = 50 )

       AND ( IV.INV_TYP IN (1,22) )

       AND ( I.ART_ID = vn_art_id OR vn_art_id=0 )