CREATE OR REPLACE PACKAGE BODY "MAIN"."B2B_WEB" AS
PROCEDURE AYBILGILERI (CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT AY,AYAD FROM AY;

END AYBILGILERI;

PROCEDURE REYONBILGILERI (P_SIRKET IN NUMBER,P_REYON IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT A.EMTIANO,A.EMTIAAD FROM MASK0001 A, MASK0006 B  WHERE B.AKTIF=1 AND A.AKTIF=1 AND A.EMTIANO=B.EMTIANO AND B.MUSNO=P_MUSNO 
AND B.TALINO=P_TALINO AND A.REYONNO=P_REYON;
END REYONBILGILERI;
PROCEDURE REYONBILGILERIHEPSI (P_SIRKET IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT DISTINCT C.REYONNO,C.AD FROM MASK0001 A,MASK0006 B,TAGE0003 C 
WHERE A.EMTIANO=B.EMTIANO AND A.REYONNO=C.REYONNO AND B.MUSNO=P_MUSNO AND B.TALINO=P_TALINO;
END REYONBILGILERIHEPSI;
PROCEDURE MAGAZABILGILERI (P_SIRKET IN NUMBER,P_BOLGENO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT B.BIRIMADI,B.BIRIMNO FROM TAGE0024 B WHERE B.ALTBOLGENO=P_BOLGENO
AND B.BIRIMTIPI='MA' AND AKTIF=1 AND B.SIRKETNO=P_SIRKET;

END MAGAZABILGILERI;

PROCEDURE BOLGEBILGILERI (P_SIRKET IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT NO,ADI FROM (
                  SELECT A.ALTBOLGENO AS NO,A.ALTBOLGEADI AS ADI FROM TAGE0125 A
                  WHERE A.ALTBOLGENO NOT IN ('99999') AND 
                  A.ALTBOLGENO IN (SELECT ALTBOLGENO FROM TAGE0024 
                  WHERE ALTBOLGENO=A.ALTBOLGENO AND AKTIF=1 AND BIRIMTIPI='MA' AND SIRKETNO=P_SIRKET) ORDER BY ALTBOLGENO ) A;

END BOLGEBILGILERI;
PROCEDURE URUNBILGILERIREYON (P_SIRKET IN NUMBER,P_REYON IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT A.EMTIANO,A.EMTIAAD FROM MASK0001 A, MASK0006 B  WHERE B.AKTIF=1 AND A.AKTIF=1 AND A.EMTIANO=B.EMTIANO AND B.MUSNO=P_MUSNO AND 
B.TALINO=P_TALINO AND A.REYONNO=P_REYON;
END URUNBILGILERIREYON;

PROCEDURE HAREKETTIPLERI (CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR
SELECT DISTINCT HARTIP,ACIKLAMA FROM TAGE0056  WHERE ISLTIP IN ('AL','PI','LI','IA','PS','TS','IP');
END HAREKETTIPLERI;





PROCEDURE KARSILANANMASTER (P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,P_YIL IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN
OPEN CUR_RETURN FOR
 
 SELECT SIPNO,YIL,BIRIMNO,BIRIMADI FROM (
        SELECT B.SIPNO,A.YIL,A.BIRIMNO,C.BIRIMADI, 
        NVL(SUM(B.TBIRIMMIK),0) AS SIPARIS,NVL(SUM(A.TBIRIMMIK),0) AS KARSILANAN,A.BIRIM
        FROM DESK0014 A,DESK0008 B,TAGE0024 C 
        WHERE 
        A.BIRIMNO=C.BIRIMNO AND A.EMTIANO=B.EMTIANO AND A.SSIPNO=B.SIPNO AND 
        A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('AL')) AND A.MUSNO=P_MUSNO
        AND A.TALINO=P_TALINO AND A.YIL=P_YIL
        AND A.YIL=B.YIL AND
        B.SIPTIP=1 AND B.AKTIF=1 
        GROUP BY B.SIPNO,A.SSIPNO,A.YIL,A.BIRIMNO,C.BIRIMADI,A.BIRIM ) A WHERE (NVL(SIPARIS,0)-NVL(KARSILANAN,0))<>0  ORDER BY SIPNO DESC;   
END KARSILANANMASTER;  
PROCEDURE KARSILANANDETAY (P_SIPNO IN VARCHAR2,P_MAGAZANO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,P_YIL IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
    BEGIN    
OPEN CUR_RETURN FOR 
           SELECT SIPNO,YIL,BIRIMNO,BIRIMADI,EMTIANO,EMTIAAD,SIPARIS,KARSILANAN,(SIPARIS-KARSILANAN) AS FARK,BIRIM FROM ( 
           SELECT B.SIPNO,A.YIL,A.BIRIMNO,C.BIRIMADI,D.EMTIANO,D.EMTIAAD,
            NVL(SUM(B.TBIRIMMIK),0) AS SIPARIS,NVL(SUM(A.TBIRIMMIK),0) AS KARSILANAN,A.BIRIM 
            FROM DESK0014 A,DESK0008 B,TAGE0024 C,MASK0001 D 
            WHERE 
            A.EMTIANO=D.EMTIANO AND A.BIRIMNO=C.BIRIMNO AND A.EMTIANO=B.EMTIANO AND A.SSIPNO=B.SIPNO AND 
            A.HARTIP  IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('AL')) AND A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.YIL=P_YIL AND A.YIL=B.YIL AND 
            B.SIPTIP=1 AND B.AKTIF=1 
            GROUP BY B.SIPNO,A.SSIPNO,A.YIL,A.BIRIMNO,C.BIRIMADI,D.EMTIANO,D.EMTIAAD,A.BIRIM ) 
            A  WHERE SIPNO=P_SIPNO AND BIRIMNO=P_MAGAZANO
            AND (NVL(SIPARIS,0)-NVL(KARSILANAN,0))<>0   ORDER BY SIPNO DESC;
       
END KARSILANANDETAY;
PROCEDURE FIRMAISTATISTIK (P_SIRKET IN NUMBER,P_OLD_YIL IN NUMBER,P_NEW_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN
OPEN CUR_RETURN FOR
SELECT ACIKLAMA,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=1 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS OCAK,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=2 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS SUBAT,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=3 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS MART,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=4 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS NISAN,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=5 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS MAYIS,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=6 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS HAZIRAN,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=7 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS TEMMUZ,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=8 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS AGUSTOS,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=9 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS EYLUL,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=10 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS EKIM,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=11 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS KASIM,
TO_CHAR(NVL(SUM(CASE WHEN A.AY=12 THEN A.NETTUT END),0),WFORMAT(P_NEW_YIL,P_SIRKET,'F')) AS ARALIK
FROM DESK0025 A,TAGE0056 B,MASK0006 C 
WHERE 
A.YIL=P_NEW_YIL AND
B.ISLTIP IN ('PS','AL','TS','IP','PI','TI') AND C.AKTIF=1 AND A.EMTIANO=C.EMTIANO AND A.HARTIP=B.HARTIP AND C.MUSNO=P_MUSNO AND C.TALINO=P_TALINO
GROUP BY ACIKLAMA;
END FIRMAISTATISTIK;

PROCEDURE MAGAZAISTATISTIK (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT BIRIMNO,BIRIMADI,ALISTUTAR,SATISTUTAR FROM (
SELECT C.BIRIMNO,C.BIRIMADI,
NVL(TO_CHAR(SUM(WURUN_ALISSATIS_DON(A.EMTIANO,C.BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),1,2,'E','E')),WFORMAT(P_YIL,P_SIRKET,'F')),0) ALISTUTAR,
NVL(TO_CHAR(SUM(WURUN_ALISSATIS_DON(A.EMTIANO,C.BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),2,2,'E','E')),WFORMAT(P_YIL,P_SIRKET,'F')),0) SATISTUTAR 
FROM MASK0001 A,MASK0006 B,TAGE0024 C 
WHERE A.EMTIANO=B.EMTIANO AND B.AKTIF=1 AND B.MUSNO=P_MUSNO AND B.TALINO=P_TALINO AND C.BIRIMTIPI='MA' AND C.AKTIF=1 AND C.SIRKETNO=P_SIRKET
GROUP BY C.BIRIMNO,C.BIRIMADI) A;
 END MAGAZAISTATISTIK;
 
 PROCEDURE MAGAZAISTATISTIK_DETAY (P_SIRKET IN NUMBER,P_BIRIMNO IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
 
 BEGIN OPEN CUR_RETURN FOR
 SELECT BIRIMADI,EMTIANO,EMTIAAD,ALISMIKTAR,ALISTUTAR,SATISMIKTAR,SATISTUTAR,STOK FROM (
 SELECT C.BIRIMADI, A.EMTIANO,A.EMTIAAD,
SUM(WURUN_ALISSATIS_DON(A.EMTIANO,P_BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),1,1,'E','E')) ALISMIKTAR,
TO_CHAR(SUM(WURUN_ALISSATIS_DON(A.EMTIANO,P_BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),1,2,'E','E')),WFORMAT(P_YIL,P_SIRKET,'F')) ALISTUTAR,
SUM(WURUN_ALISSATIS_DON(A.EMTIANO,P_BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),2,1,'E','E')) SATISMIKTAR,
TO_CHAR(SUM(WURUN_ALISSATIS_DON(A.EMTIANO,P_BIRIMNO,TO_DATE('0101'||P_YIL,'DDMMYYYY'),TO_DATE('3112'||P_YIL,'DDMMYYYY'),2,2,'E','E')),WFORMAT(P_YIL,P_SIRKET,'F')) SATISTUTAR, 
NVL(SUM(WSONSTOK(A.EMTIANO,P_BIRIMNO,SYSDATE,P_SIRKET)),0) STOK 
FROM MASK0001 A,MASK0006 B,TAGE0024 C 
WHERE C.BIRIMNO=P_BIRIMNO AND A.EMTIANO=B.EMTIANO AND B.AKTIF=1
AND B.MUSNO=P_MUSNO AND B.TALINO=P_TALINO AND C.BIRIMTIPI='MA' AND C.AKTIF=1 AND C.SIRKETNO=P_SIRKET
GROUP BY A.EMTIANO,A.EMTIAAD,C.BIRIMADI ) A WHERE STOK>0;
 
 END MAGAZAISTATISTIK_DETAY;
PROCEDURE ENCOKSATAN (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_AY IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS


BEGIN OPEN CUR_RETURN FOR
SELECT DISTINCT A.YIL,A.AY,A.EMTIANO AS URUNNO, C.EMTIAAD AS URUNADI, SUM(A.BIRIMIK) AS MIKTAR,
SUM(WSONSTOK(A.EMTIANO,A.BIRIMNO,TRUNC(SYSDATE),1)) STONSTOK,TO_CHAR(SUM(BRUTTUT),WFORMAT(P_YIL,P_SIRKET,'F')) AS TUTAR, 
A.BIRIM AS BIRIM FROM DESK0025 A,MASK0006 B,MASK0001 C 
WHERE A.YIL =P_YIL AND A.AY=P_AY  AND A.EMTIANO=B.EMTIANO AND B.EMTIANO=C.EMTIANO AND 
A.EMTIANO=C.EMTIANO AND C.AKTIF=1 AND A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS')) AND 
B.MUSNO=P_MUSNO AND B.TALINO=P_TALINO AND B.AKTIF=1 AND B.AKTIF=1 AND C.AKTIF=1 
GROUP BY A.YIL,A.AY,A.EMTIANO,C.EMTIAAD,A.BIRIM ORDER BY MIKTAR DESC;
END ENCOKSATAN;

PROCEDURE ONLINECIRO (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT A.YIL,TO_CHAR(A.TARIH,'DD/MM/YYYY') AS TARIH,B.BIRIMNO,B.BIRIMADI,
D.EMTIANO,D.EMTIAAD,SUM(A.TBIRIMMIK) as MIKTAR,TO_CHAR(A.NETTUT,WFORMAT(P_YIL,P_SIRKET,'F')) AS FIYAT,
TO_CHAR((A.TBIRIMMIK*A.NETTUT),WFORMAT(P_YIL,P_SIRKET,'F')) AS NETTUT,
TO_CHAR((A.TBIRIMMIK*A.BRUTTUT),WFORMAT(P_YIL,P_SIRKET,'F')) AS BRUTTUT,
A.TBIRIM as BIRIM FROM DESK0022 A, TAGE0024 B,MASK0006 C,MASK0001 D
WHERE 
A.EMTIANO=C.EMTIANO AND
A.EMTIANO=D.EMTIANO AND 
C.EMTIANO=D.EMTIANO AND
(A.TARIH = TRUNC(SYSDATE))AND
C.MUSNO=P_MUSNO AND
C.TALINO=P_TALINO AND
A.BIRIMNO=B.BIRIMNO AND 
B.BIRIMTIPI='MA'AND D.AKTIF=1 AND C.AKTIF=1 AND 
(A.YIL = TO_CHAR(SYSDATE,'YYYY')) AND 
A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS'))
GROUP BY A.YIL,A.TARIH,B.BIRIMNO,B.BIRIMADI,D.EMTIANO,D.EMTIAAD,A.TBIRIM,A.NETTUT,A.BRUTTUT,A.TBIRIMMIK ORDER BY A.TBIRIMMIK DESC;
END ONLINECIRO;
PROCEDURE ONAYLANMIS_SIPARIS_COUNT (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_SIPGUN IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT COUNT(*) TOPLAM FROM MASK0007 A,TAGE0024 B 
WHERE A.BIRIMNO=B.BIRIMNO AND B.AKTIF=1 AND A.AKTIF=1 AND
A.MUSNO=P_MUSNO AND 
A.TALINO=P_TALINO AND A.SIPTARIH>=TRUNC(SYSDATE)-P_SIPGUN;
END ONAYLANMIS_SIPARIS_COUNT;

PROCEDURE ONAY_BEKLEYEN_SIPARIS_COUNT (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_SIPGUN IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS 
BEGIN OPEN CUR_RETURN FOR

SELECT COUNT(ROWNUM) AS KALEM 
FROM DESKG008 A,MASKG007 B,TAGE0024 C 
WHERE A.MUSNO=B.MUSNO AND A.TALINO=B.TALINO AND A.SIPTIP=10 AND A.BIRIMNO=C.BIRIMNO AND  A.SIPNO=B.SIPNO AND 
A.SIPNO NOT IN (SELECT SIPNO FROM MASK0007) AND
A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND B.SIPTARIH>=TRUNC(SYSDATE)-P_SIPGUN;

END ONAY_BEKLEYEN_SIPARIS_COUNT;

PROCEDURE ONAYLANMIS_SIPARISLER_MASTER (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_SIPGUN IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT A.MUSNO,A.TALINO,A.YIL,TO_CHAR(A.siptarih,'DD/MM/YYYY') AS SIPTARIH,A.BIRIMNO,B.BIRIMADI,A.SIPNO 
FROM MASK0007 A,TAGE0024 B
WHERE A.BIRIMNO=B.BIRIMNO AND B.AKTIF=1 AND A.AKTIF=1 AND 
A.MUSNO=P_MUSNO AND 
A.TALINO=P_TALINO AND A.SIPTARIH>=TRUNC(SYSDATE)-P_SIPGUN;
END ONAYLANMIS_SIPARISLER_MASTER;

PROCEDURE ONAYLANMIS_SIPARISLER_DETAY (P_SIRKET IN NUMBER,P_BIRIMNO IN NUMBER,P_SIPNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR

SELECT A.YIL,A.SIPNO,TO_CHAR(A.SIPTARIH,'DD/MM/YYYY') AS SIPTARIH,A.BIRIMNO,C.BIRIMADI,A.EMTIANO,B.EMTIAAD,A.TBIRIMMIK AS MIKTAR,
NVL((TBIRIMMIK/D.CEVDEG),0) AS KOLI,
WSONSTOK(A.EMTIANO, C.BIRIMNO, SYSDATE,1) MAGAZASTOK,A.NETFIYAT,A.BRUTFIYAT,A.BIRIM
FROM DESK0008 A,MASK0001 B,TAGE0024 C, MASK0002 D
WHERE A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.SIPNO=P_SIPNO AND A.BIRIMNO=P_BIRIMNO AND B.EMTIANO=D.EMTIANO 
AND A.EMTIANO=B.EMTIANO AND A.BIRIMNO=C.BIRIMNO AND A.YIL=TO_CHAR(TRUNC(SYSDATE),'YYYY') AND A.AKTIF=1  ORDER BY A.SIPNO DESC;

END ONAYLANMIS_SIPARISLER_DETAY;
PROCEDURE ONAYLANMIS_SIPARISLER_TARIH (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT A.MUSNO,A.TALINO,A.YIL,TO_CHAR(A.siptarih,'DD/MM/YYYY') AS SIPTARIH,A.BIRIMNO,B.BIRIMADI,A.SIPNO 
FROM MASK0007 A,TAGE0024 B
WHERE A.BIRIMNO=B.BIRIMNO AND B.AKTIF=1 AND A.AKTIF=1 AND 
A.MUSNO=P_MUSNO AND 
A.TALINO=P_TALINO AND A.SIPTARIH BETWEEN P_T1 AND P_T2 AND A.BIRIMNO LIKE '%'||P_BIRIMNO||'%';

END ONAYLANMIS_SIPARISLER_TARIH;

PROCEDURE ONAYLAYAN_SATINALACI (P_SIRKET IN NUMBER,P_BIRIMNO IN NUMBER,P_SIPNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT AD||' '||SOYAD AS SIPVER FROM TAGE0051 WHERE BORDNO IN (SELECT SIPONAY FROM DESK0019 WHERE SIPNO=P_SIPNO AND BIRIMNO=P_BIRIMNO AND MUSNO=P_MUSNO AND TALINO=P_TALINO);
END ONAYLAYAN_SATINALACI;

PROCEDURE IADE_DEPOLARI (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT BIRIMNO,BIRIMADI,COUNT(BIRIMNO||STOK) AS KALEM FROM (
SELECT BIRIMNO,BIRIMADI,WSONSTOK(EMTIANO,BIRIMNO,TRUNC(SYSDATE),1) STOK FROM (
SELECT B.BIRIMNO,B.BIRIMADI,A.EMTIANO

FROM MASK0001 A,TAGE0024 B,MASK0006 C 
WHERE B.IADEDEPOSU=1 AND A.EMTIANO=C.EMTIANO AND C.AKTIF=1 AND C.MUSNO=P_MUSNO AND C.TALINO=P_TALINO
GROUP BY B.BIRIMNO,B.BIRIMADI,A.EMTIANO ) A ) B WHERE STOK>0 GROUP BY BIRIMNO,BIRIMADI;
END IADE_DEPOLARI;

PROCEDURE IADE_DEPOLARI_DETAY (P_SIRKET IN NUMBER,P_BIRIMNO IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR
SELECT BIRIMNO,BIRIMADI,EMTIANO,EMTIAAD,MIKTAR,TUTAR FROM (
SELECT B.BIRIMNO,B.BIRIMADI,A.EMTIANO,A.EMTIAAD,
WSONSTOK(A.EMTIANO,B.BIRIMNO,TRUNC(SYSDATE),P_SIRKET) MIKTAR,
TO_CHAR(NVL(WSONSTOKTUT(A.EMTIANO,P_BIRIMNO,TRUNC(SYSDATE),'FF',P_SIRKET),0),WFORMAT(P_YIL,P_SIRKET,'F')) TUTAR 
FROM MASK0001 A,TAGE0024 B,MASK0006 C 
WHERE B.IADEDEPOSU=1 AND A.EMTIANO=C.EMTIANO AND C.AKTIF=1 AND 
C.MUSNO=P_MUSNO AND C.TALINO=P_TALINO ) A WHERE BIRIMNO=P_BIRIMNO AND MIKTAR>0;

END IADE_DEPOLARI_DETAY;

PROCEDURE CARI_BORC_ALACAK (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS 
BEGIN OPEN CUR_RETURN FOR

SELECT 
to_char(NVL(ALACAK,0),'999G999G999G990') AS ALACAK, 
to_char(NVL(BORC,0),'999G999G999G990') AS BORC, 
to_char(NVL(ALACAKLI,0),'999G999G999G990') AS ALACAKLI,
to_char(NVL(BORCLU,0),'999G999G999G990') AS BORCLU
FROM (
SELECT 
NVL(MUSATOP,0) AS ALACAK,NVL(MUSBTOP,0) AS BORC,
NVL(CASE WHEN (MUSATOP>MUSBTOP) THEN (MUSATOP-MUSBTOP) END,0) AS ALACAKLI,
NVL(CASE WHEN (MUSBTOP>MUSATOP) THEN (MUSBTOP-MUSATOP) END,0) AS BORCLU

 FROM MACR0201 WHERE MUSNO=P_MUSNO AND TALINO=P_TALINO AND YIL=P_YIL ) A;

END CARI_BORC_ALACAK;

PROCEDURE CARI_BORC_ALACAK_DETAY (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR
SELECT YIL,SIRANO,MUSNO,TALINO,TARIH,
to_char(NVL(BORCTUT,0),WFORMAT(P_YIL,P_SIRKET,'F')) BORC,to_char(NVL(ALACTUT,0),WFORMAT(P_YIL,P_SIRKET,'F')) ALACAK,MODULKODU,ACIKLAMA
FROM DECR0010 WHERE MUSNO=P_MUSNO AND TALINO=P_TALINO AND YIL=P_YIL;

END CARI_BORC_ALACAK_DETAY;
PROCEDURE SATISTARIHBAZLI (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT A.YIL,B.BIRIMNO,B.BIRIMADI, E.AD AS REYONADI,D.EMTIANO,D.EMTIAAD,SUM(A.TBIRIMMIK) AS MIKTAR,(A.NETTUT*A.TBIRIMMIK) AS NETTUT,A.TBIRIM AS BIRIM 
 FROM DESK0022 A, TAGE0024 B,MASK0006 C,MASK0001 D,TAGE0003 E 
 WHERE  
 D.REYONNO=E.REYONNO AND 
 A.EMTIANO=C.EMTIANO AND 
 A.EMTIANO=D.EMTIANO AND 
 C.EMTIANO=D.EMTIANO AND C.AKTIF=1 AND D.AKTIF=1 AND 
 A.TARIH BETWEEN P_T1 AND P_T2 AND 
 C.MUSNO=P_MUSNO AND 
 C.TALINO=P_TALINO AND 
 A.BIRIMNO=B.BIRIMNO AND 
 B.BIRIMTIPI='MA' AND 
 A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND 
 (A.YIL = P_YIL) AND 
 A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS'))
 GROUP BY A.YIL,B.BIRIMNO,B.BIRIMADI,E.AD,D.EMTIANO,D.EMTIAAD,A.TBIRIM,NETTUT,A.TBIRIMMIK ORDER BY A.TBIRIMMIK DESC;
END SATISTARIHBAZLI;
PROCEDURE SATISTARIHBAZLIURUN (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_EMTIANO IN VARCHAR2,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT A.YIL,TO_CHAR(A.TARIH,'DD/MM/YYYY') AS TARIH,B.BIRIMNO,B.BIRIMADI, E.AD AS REYONADI,D.EMTIANO,D.EMTIAAD,A.TBIRIMMIK AS MIKTAR,A.NETTUT,A.TBIRIM AS BIRIM 
 FROM DESK0022 A, TAGE0024 B,MASK0006 C,MASK0001 D,TAGE0003 E 
 WHERE  
 D.REYONNO=E.REYONNO AND 
 A.EMTIANO=C.EMTIANO AND 
 A.EMTIANO=D.EMTIANO AND 
 C.EMTIANO=D.EMTIANO AND C.AKTIF=1 AND D.AKTIF=1 AND 
 A.TARIH BETWEEN P_T1 AND P_T2 AND 
 C.MUSNO=P_MUSNO AND 
 C.TALINO=P_TALINO AND 
 A.BIRIMNO=B.BIRIMNO AND 
 B.BIRIMTIPI='MA'AND 
 A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND 
 (A.YIL = P_YIL) AND 
 (A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS')) AND A.EMTIANO=P_EMTIANO);
 END SATISTARIHBAZLIURUN;
PROCEDURE SATISTARIHBAZLIREYON (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_REYON IN VARCHAR2,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
 SELECT A.YIL,B.BIRIMNO,B.BIRIMADI, E.AD AS REYONADI,D.EMTIANO,D.EMTIAAD,SUM(A.TBIRIMMIK) AS MIKTAR,(A.NETTUT*A.TBIRIMMIK) AS NETTUT,A.TBIRIM AS BIRIM 
 FROM DESK0022 A, TAGE0024 B,MASK0006 C,MASK0001 D,TAGE0003 E 
 WHERE  
 D.REYONNO=E.REYONNO AND 
 A.EMTIANO=C.EMTIANO AND 
 A.EMTIANO=D.EMTIANO AND 
 C.EMTIANO=D.EMTIANO AND C.AKTIF=1 AND D.AKTIF=1 AND 
 A.TARIH BETWEEN P_T1 AND P_T2 AND 
 C.MUSNO=P_MUSNO AND 
 C.TALINO=P_TALINO AND 
 A.BIRIMNO=B.BIRIMNO AND 
 B.BIRIMTIPI='MA'AND 
 A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND 
 (A.YIL = P_YIL) AND 
 A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS')) AND E.REYONNO=P_REYON
 GROUP BY A.YIL,B.BIRIMNO,B.BIRIMADI,E.AD,D.EMTIANO,D.EMTIAAD,A.TBIRIM,NETTUT,A.TBIRIMMIK ORDER BY A.TBIRIMMIK DESC;
 END SATISTARIHBAZLIREYON;

PROCEDURE SATISAYBAZLIMATRIS (P_VALUE IN NUMBER,P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN
IF P_VALUE=1 THEN
OPEN CUR_RETURN FOR


SELECT C.EMTIAAD,NVL(SUM(DECODE(AY,'1',TBIRIMMIK)),0) OCAK,NVL(SUM(DECODE(AY,'2',TBIRIMMIK)),0) SUBAT,
NVL(SUM(DECODE(AY,'3',TBIRIMMIK)),0) MART,NVL(SUM(DECODE(AY,'4',TBIRIMMIK)),0) NISAN,NVL(SUM(DECODE(AY,'5',TBIRIMMIK)),0) MAYIS,
NVL(SUM(DECODE(AY,'6',TBIRIMMIK)),0) HAZIRAN,NVL(SUM(DECODE(AY,'7',TBIRIMMIK)),0) TEMMUZ,NVL(SUM(DECODE(AY,'8',TBIRIMMIK)),0) AGUSTOS,
NVL(SUM(DECODE(AY,'9',TBIRIMMIK)),0) EYLUL,NVL(SUM(DECODE(AY,'10',TBIRIMMIK)),0) EKIM,NVL(SUM(DECODE(AY,'11',TBIRIMMIK)),0) KASIM,
NVL(SUM(DECODE(AY,'12',TBIRIMMIK)),0) ARALIK 
FROM DESK0025 A,TAGE0056 B,MASK0001 C,MASK0006 D 
WHERE A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND A.EMTIANO=C.EMTIANO AND A.YIL=P_YIL AND A.EMTIANO=D.EMTIANO AND C.EMTIANO=D.EMTIANO AND 
A.HARTIP=B.HARTIP AND B.ISLTIP IN ('PS','TS') AND C.AKTIF=1 AND D.AKTIF=1 AND D.MUSNO=P_MUSNO AND D.TALINO=P_TALINO
GROUP BY C.EMTIAAD, A.EMTIANO ORDER BY A.EMTIANO;


ELSIF P_VALUE=2 THEN

 OPEN CUR_RETURN FOR
SELECT C.EMTIAAD,
TO_CHAR(NVL(SUM(DECODE(AY,'1',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) OCAK,TO_CHAR(NVL(SUM(DECODE(AY,'2',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) SUBAT,
TO_CHAR(NVL(SUM(DECODE(AY,'3',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) MART,TO_CHAR(NVL(SUM(DECODE(AY,'4',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) NISAN,
TO_CHAR(NVL(SUM(DECODE(AY,'5',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) MAYIS,TO_CHAR(NVL(SUM(DECODE(AY,'6',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) HAZIRAN,
TO_CHAR(NVL(SUM(DECODE(AY,'7',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) TEMMUZ,TO_CHAR(NVL(SUM(DECODE(AY,'8',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) AGUSTOS,
TO_CHAR(NVL(SUM(DECODE(AY,'9',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) EYLUL,TO_CHAR(NVL(SUM(DECODE(AY,'10',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) EKIM,
TO_CHAR(NVL(SUM(DECODE(AY,'11',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) KASIM,TO_CHAR(NVL(SUM(DECODE(AY,'12',BRUTTUT)),0),WFORMAT(P_YIL,P_SIRKET,'F')) ARALIK
               
FROM DESK0025 A,TAGE0056 B,MASK0001 C,MASK0006 D
WHERE A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND A.EMTIANO=C.EMTIANO AND A.YIL=P_YIL AND A.EMTIANO=D.EMTIANO AND C.EMTIANO=D.EMTIANO AND
A.HARTIP=B.HARTIP AND B.ISLTIP IN ('PS','TS') AND C.AKTIF=1 AND D.AKTIF=1 AND D.MUSNO=P_MUSNO AND D.TALINO=P_TALINO
GROUP BY C.EMTIAAD, A.EMTIANO ORDER BY A.EMTIANO;

END IF;
END SATISAYBAZLIMATRIS;
PROCEDURE HAREKETTIPIBAZINDA (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_HARTIP IN NUMBER,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR
SELECT DISTINCT B.YIL,F.ACIKLAMA,B.BIRIMNO,A.BIRIMADI,C.EMTIANO, C.EMTIAAD, SUM(B.TBIRIMMIK) AS MIKTAR, B.TBIRIM AS BIRIM
             FROM TAGE0024 A, DESK0022 B, MASK0001 C, MASK0006 D,TAGE0056 F 
            WHERE (C.AKTIF = '1') AND (D.AKTIF = '1') AND (B.HARTIP = F.HARTIP) AND 
            (B.BIRIMNO = A.BIRIMNO) AND (B.EMTIANO = D.EMTIANO) AND C.EMTIANO=D.EMTIANO AND
            D.MUSNO=P_MUSNO AND D.TALINO=P_TALINO AND F.HARTIP=P_HARTIP AND 
            B.TARIH BETWEEN P_T1 AND P_T2 AND B.YIL=P_YIL AND A.BIRIMNO LIKE '%'||P_BIRIMNO||'%'
            GROUP BY B.YIL,F.ACIKLAMA,B.BIRIMNO,A.BIRIMADI,C.EMTIANO,C.EMTIAAD,B.TBIRIM;
            
END HAREKETTIPIBAZINDA;

PROCEDURE SONSTOK (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_DURUM IN NUMBER,P_REYON IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN
IF P_DURUM=1 THEN
OPEN CUR_RETURN FOR

SELECT BIRIMNO,BIRIMADI,EMTIANO,EMTIAAD,REYONU,STOK_MIKTARI,STOKTUTAR,TBIRIM FROM ( 
SELECT D.BIRIMNO,D.BIRIMADI,A.EMTIANO,A.EMTIAAD,C.AD AS REYONU,
WSONSTOK(A.EMTIANO, D.BIRIMNO, SYSDATE,1) STOK_MIKTARI, 
WSONSTOKTUT(A.EMTIANO,D.BIRIMNO,TRUNC(SYSDATE),'FF',1) STOKTUTAR,A.TBIRIM 
FROM MASK0001 A,MASK0006 B,TAGE0003 C,TAGE0024 D 
WHERE A.EMTIANO=B.EMTIANO AND 
A.REYONNO=C.REYONNO AND  D.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND D.BIRIMTIPI='MA'
AND B.AKTIF=1 AND 
B.MUSNO=P_MUSNO AND C.REYONNO LIKE '%'||P_REYON||'%' AND B.TALINO=P_TALINO
GROUP BY D.BIRIMNO,D.BIRIMADI,A.ANAGRUP,A.EMTIANO,A.EMTIAAD,C.AD,A.TBIRIM ) A WHERE STOK_MIKTARI>=0 ORDER BY STOK_MIKTARI DESC;

ELSIF P_DURUM=0 THEN
OPEN CUR_RETURN FOR
SELECT BIRIMNO,BIRIMADI,EMTIANO,EMTIAAD,REYONU,STOK_MIKTARI,STOKTUTAR,TBIRIM FROM ( 
SELECT D.BIRIMNO,D.BIRIMADI,A.EMTIANO,A.EMTIAAD,C.AD AS REYONU,
WSONSTOK(A.EMTIANO, D.BIRIMNO, SYSDATE,1) STOK_MIKTARI, 
WSONSTOKTUT(A.EMTIANO,D.BIRIMNO,TRUNC(SYSDATE),'FF',1) STOKTUTAR,A.TBIRIM 
FROM MASK0001 A,MASK0006 B,TAGE0003 C,TAGE0024 D 
WHERE A.EMTIANO=B.EMTIANO AND 
A.REYONNO=C.REYONNO AND  D.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND D.BIRIMTIPI='MA'
AND B.AKTIF=1 AND 
B.MUSNO=P_MUSNO AND C.REYONNO LIKE '%'||P_REYON||'%' AND B.TALINO=P_TALINO
GROUP BY D.BIRIMNO,D.BIRIMADI,A.ANAGRUP,A.EMTIANO,A.EMTIAAD,C.AD,A.TBIRIM ) A WHERE STOK_MIKTARI<0 ORDER BY STOK_MIKTARI DESC;

END IF;
END SONSTOK;

PROCEDURE AKTIFPASIF (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT DISTINCT A.BIRIMNO, B.BIRIMADI, C.AD REYONADI, A.EMTIANO, F.EMTIAAD,
                       CASE WHEN A.AKTIF=1 THEN 'AKTIF' WHEN A.AKTIF=0 THEN 'PASIF' END AS DURUM 
                        FROM MASK0111 A, TAGE0024 B, TAGE0003 C, MASK0003 D, MASK0001 F,MASK0006 G 
                        WHERE F.AKTIF=1 AND G.AKTIF=1 AND (B.BIRIMTIPI='MA') AND (F.REYONNO = C.REYONNO) AND
                        (A.EMTIANO = F.EMTIANO) AND (A.BIRIMNO = B.BIRIMNO) AND G.MUSNO=P_MUSNO AND G.TALINO=P_TALINO
                       AND (A.EMTIANO = D.EMTIANO) AND  A.EMTIANO=G.EMTIANO ;
END AKTIFPASIF;

PROCEDURE URUNTOPLAMSTOK (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT EMTIANO,WBARKODBUL(EMTIANO) AS BARKOD,EMTIAAD,STOK,TUTAR FROM (
                  SELECT A.EMTIANO,A.EMTIAAD, 
                  SUM(WSONSTOK(A.EMTIANO,B.BIRIMNO,TRUNC(SYSDATE),P_SIRKET)) AS STOK,
                  TO_CHAR(NVL(SUM(WSONSTOKTUT(A.EMTIANO,B.BIRIMNO,TRUNC(SYSDATE),'FF','1')),0),WFORMAT(P_YIL,P_SIRKET,'F')) AS TUTAR 
                  FROM MASK0001 A,TAGE0024 B,MASK0006 C 
                  WHERE B.BIRIMNO LIKE '%'||P_BIRIMNO||'%'
                  AND A.EMTIANO=C.EMTIANO AND C.AKTIF=1 AND C.AKTIF=1 AND B.AKTIF=1 AND A.AKTIF=1 AND
                  C.MUSNO=P_MUSNO AND B.TALINO=P_TALINO
                  GROUP BY A.EMTIANO,A.EMTIAAD ) A WHERE STOK>0 ORDER BY STOK DESC;

END URUNTOPLAMSTOK;

PROCEDURE STOKDEVIRHIZI (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_GUN IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT EMTIANO,EMTIAAD,SATISMIKTARI,BIRIM,URUN_HAREKET_GUNSAYISI FROM (
                  SELECT A.EMTIANO,B.EMTIAAD,SUM(TBIRIMMIK) AS SATISMIKTARI,A.BIRIM AS BIRIM,
                   SUM(NVL(WSTOKDAGUNSAY(A.EMTIANO,A.BIRIMNO,A.TARIH,TRUNC(SYSDATE)-P_GUN,1),1)) URUN_HAREKET_GUNSAYISI
                   FROM DESK0022 A,MASK0001 B,MASK0006 C 
                   WHERE A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND A.EMTIANO=B.EMTIANO AND A.EMTIANO=C.EMTIANO AND 
                   C.MUSNO=P_MUSNO AND C.TALINO=P_TALINO AND A.TARIH BETWEEN TRUNC(SYSDATE-P_GUN) AND TRUNC(SYSDATE) AND
                   A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS','KC')) AND C.AKTIF=1 
                   GROUP BY A.EMTIANO,A.BIRIM,B.EMTIAAD ORDER BY A.TBIRIMMIK DESC ) A ORDER BY URUN_HAREKET_GUNSAYISI DESC;

END STOKDEVIRHIZI;

PROCEDURE ALISTARIH (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_T1 IN DATE,P_T2 IN DATE,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR
SELECT YIL,TARIH,FATNO,BIRIMNO,BIRIMADI,KALEM,TO_CHAR(TUTAR,WFORMAT(P_YIL,P_SIRKET,'F')) AS TUTAR FROM (
                       SELECT A.YIL,A.BIRIMNO,B.BIRIMADI,A.IRSNO AS FATNO,TO_CHAR(A.FATDUZTAR,'DD/MM/YYYY') AS TARIH,COUNT(*) AS KALEM,
                       SUM(NVL(A.BIRIMIK,0)*NVL(A.BRUTFIYAT,0)) AS TUTAR
                       FROM DESK0014 A,TAGE0024 B
                       WHERE A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND A.FATDUZTAR BETWEEN P_T1 AND P_T2 AND
                       A.BIRIMNO=B.BIRIMNO AND 
                       FATKOD='E' AND A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('AL')) AND
                       A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.YIL=P_YIL
                       GROUP BY A.YIL,A.BIRIMNO,B.BIRIMADI,A.IRSNO,FATDUZTAR ) A ORDER BY TARIH DESC;

END ALISTARIH;

PROCEDURE ALISTARIHDETAY (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_FATNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT FATNO,TARIH,BIRIMNO,BIRIMADI,EMTIANO,EMTIAAD,BIRIMIK,BIRIM,FIYAT,TO_CHAR(TUTAR,WFORMAT(P_YIL,P_SIRKET,'F')) AS TUTAR FROM (
                       SELECT A.IRSNO AS FATNO,TO_CHAR(A.FATDUZTAR,'DD/MM/YYYY') AS TARIH,A.BIRIMNO,B.BIRIMADI,
                       A.EMTIANO,C.EMTIAAD,A.BIRIMIK,A.BIRIM,A.BRUTFIYAT AS FIYAT,(A.BIRIMIK*A.BRUTFIYAT) AS TUTAR
                       FROM DESK0014 A,TAGE0024 B,MASK0001 C 
                       WHERE 
                       A.EMTIANO=C.EMTIANO AND 
                       A.BIRIMNO=B.BIRIMNO AND 
                       FATKOD='E' AND A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('AL')) AND
                       A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.YIL=P_YIL) A 
                       WHERE FATNO=P_FATNO AND BIRIMNO=P_BIRIMNO  ORDER BY BIRIMIK DESC;

END ALISTARIHDETAY;

PROCEDURE GENELALISKOSULLARI (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT FDEGTAR,EMTIANO,EMTIAAD,BFIYAT,VADE,ISK1,ISK2,ISK3,ISK4,ISK5 FROM (
           SELECT DISTINCT A.FDEGTAR ,A.EMTIANO, B.EMTIAAD, A.BFIYAT,A.VADE, A.ISK1, A.ISK2, A.ISK3, A.ISK4, A.ISK5 
            FROM MASK0208 A,MASK0001 B , MASK0111 D, MASK0006 E 
            WHERE A.EMTIANO=E.EMTIANO AND A.EMTIANO=D.EMTIANO AND B.EMTIANO=D.EMTIANO AND A.EMTIANO=B.EMTIANO AND  A.FIYTIP='NA' AND D.AKTIF=1 AND E.AKTIF=1 AND B.AKTIF=1
            AND E.MUSNO=P_MUSNO AND E.TALINO=P_TALINO AND TO_CHAR(A.FDEGTAR,'YYYY')=P_YIL
           order by A.FDEGTAR DESC ) A;

END GENELALISKOSULLARI;

PROCEDURE KFFMASTER (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT YIL,FATNO,BIRIMNO,BIRIMADI,DUZTAR,ISLTAR,EVRAKNO,TO_CHAR(FATTUTAR,WFORMAT(P_YIL,P_SIRKET,'F')) AS FATTUTAR FROM ( 
                      SELECT DISTINCT F.YIL,M.FATNO,F.BIRIMNO,B.BIRIMADI,TO_CHAR(M.DUZTAR,'DD/MM/YYYY') DUZTAR,TO_CHAR(M.ISLTAR,'DD/MM/YYYY') AS ISLTAR,M.EVRAKNO,
                      NVL(M.NETTUT,0) + NVL(M.KDVTUTAR,0) + NVL(M.KDVYUVTUT,0) + NVL(M.YUVTUT,0) FATTUTAR
                      FROM MAMH0066 M,DESKFK14 F,MACR0001 C,MASK0001 S,TAGE0056 T,TAGE0024 B 
                      WHERE (NVL(F.FFARKONAY,0)) = 1 AND NVL(F.IPTALONAY,0) = 0 AND M.YIL = F.YIL 
                      AND F.BIRIMNO=B.BIRIMNO AND M.BIRIMNO = F.FATBIRIMNO AND M.GRCKOD = F.GRCKOD AND M.FATNO = F.FATNO 
                      AND M.MUSNO = C.MUSNO AND M.TALINO = C.TALINO AND F.EMTIANO = S.EMTIANO 
                      AND F.HARTIP = T.HARTIP AND F.YIL =P_YIL
                      AND C.MUSNO = P_MUSNO AND C.TALINO = P_TALINO
                      AND (F.BIRIMIK * ( NVL(F.FNETFIYAT,0) - NVL(F.ANETFIYAT,0))  > 0) ) A;

END KFFMASTER;

PROCEDURE KFFDETAY (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_FATNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS 

BEGIN OPEN CUR_RETURN FOR
SELECT M.FATNO,TO_CHAR(M.DUZTAR,'DD/MM/YYYY') DUZTAR,TO_CHAR(M.ISLTAR,'DD/MM/YYYY') AS ISLTAR,M.EVRAKNO,
            TO_CHAR(NVL(M.NETTUT,0) + NVL(M.KDVTUTAR,0) + NVL(M.KDVYUVTUT,0) + NVL(M.YUVTUT,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS FATTUTAR,
            F.EMTIANO EMTIANO,S.EMTIAAD EMTIAAD, 
            TO_CHAR(F.ANETFIYAT,WFORMAT(P_YIL,P_SIRKET,'F')) AS ANETFIYAT,
            TO_CHAR(F.FNETFIYAT,WFORMAT(P_YIL,P_SIRKET,'F')) AS FNETFIYAT,F.BIRIMIK, 
            TO_CHAR(F.BIRIMIK * ( NVL(F.FNETFIYAT,0) - NVL(F.ANETFIYAT,0)),WFORMAT(P_YIL,P_SIRKET,'F')) FIYATFARKI 
            FROM MAMH0066 M,DESKFK14 F,MACR0001 C,MASK0001 S,TAGE0056 T 
            WHERE (NVL(F.FFARKONAY,0)) = 1 AND NVL(F.IPTALONAY,0) = 0 AND M.YIL = F.YIL 
            AND M.BIRIMNO = F.FATBIRIMNO AND M.GRCKOD = F.GRCKOD AND M.FATNO = F.FATNO 
            AND M.MUSNO = C.MUSNO AND M.TALINO = C.TALINO AND F.EMTIANO = S.EMTIANO 
            AND F.HARTIP = T.HARTIP AND F.YIL =P_YIL
            AND C.MUSNO = P_MUSNO AND C.TALINO = P_TALINO
            AND (F.BIRIMIK * ( NVL(F.FNETFIYAT,0) - NVL(F.ANETFIYAT,0))  > 0) AND M.FATNO=P_FATNO
            ORDER BY NLSSORT(C.MUSAD,'NLS_SORT=TURKISH'), M.ISLTAR, M.DUZTAR, S.EMTIAAD;

END KFFDETAY;

PROCEDURE FATEVRAKLAR (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT A.YIL,B.MUSAD AS CARI,A.MUSNO,A.TALINO,A.ACIKLAMA,
           TO_CHAR(SUM(A.BTUTAR),WFORMAT(P_YIL,P_SIRKET,'F')) AS BORC,
           TO_CHAR(SUM(A.ATUTAR),WFORMAT(P_YIL,P_SIRKET,'F')) AS ALACAK
           FROM VWCRMTUT A,MACR0001 B
           WHERE  A.MUSNO=B.MUSNO AND A.TALINO=B.TALINO AND A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.YIL=P_YIL
           GROUP BY A.YIL,A.MUSNO,A.TALINO,A.ACIKLAMA,B.MUSAD;

END FATEVRAKLAR;

PROCEDURE IADEMASTER (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS

BEGIN OPEN CUR_RETURN FOR

SELECT A.YIL,B.BIRIMNO,C.BIRIMADI,A.FATNO,A.EVRAKNO,B.IRSNO,TO_CHAR(A.ISLTAR,'DD/MM/YYYY') AS ISLTAR,
  TO_CHAR(NVL(A.NETTUT,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS NETTUT,TO_CHAR(NVL(A.BRUTTUT,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS BRUTTUT,
  TO_CHAR(NVL(A.KDVTUTAR,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS KDVTUT,
  TO_CHAR(NVL(A.DISKTUT1,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS ISK,
  TO_CHAR(NVL(A.DISKTUT2,0),WFORMAT(P_YIL,P_SIRKET,'F')) ISK1,TO_CHAR(NVL(A.DISKTUT3,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS ISK2,
  COUNT(B.EMTIANO) AS KALEM
  FROM MAMH0066 A,DESK0014 B,TAGE0024 C
  WHERE 
  B.BIRIMNO=C.BIRIMNO AND
  A.FATNO=B.FATNO AND A.MUSNO=B.MUSNO AND A.TALINO=B.TALINO AND A.YIL=B.YIL AND 
  A.FATTIP=2 AND A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO AND A.YIL=P_YIL 
  GROUP BY A.YIL,A.FATNO,A.EVRAKNO,A.ISLTAR,A.ISLTAR,A.NETTUT,A.BRUTTUT,B.IRSNO,
  A.KDVTUTAR,A.DISKTUT1,A.DISKTUT2,A.DISKTUT3,B.BIRIMNO,C.BIRIMADI ORDER BY FATNO DESC;

END IADEMASTER;

PROCEDURE IADEDETAY (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_ISNO IN NUMBER,P_FATNO IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR



 SELECT A.BIRIMNO,A.FATNO,A.EMTIANO,A.BARKOD,B.EMTIAAD,A.BIRIMIK,A.BIRIM,
  TO_CHAR(NVL(A.BRUTFIYAT,0),WFORMAT(P_YIL,P_SIRKET,'F')) BRUTTUT,TO_CHAR(NVL(A.NETFIYAT,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS NETTUT,
  TO_CHAR(NVL(A.KDVFIYAT,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS KDVTUT,TO_CHAR(NVL(A.DISK1,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS ISK1,
  TO_CHAR(NVL(A.DISK2,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS ISK2,TO_CHAR(NVL(A.DISK3,0),WFORMAT(P_YIL,P_SIRKET,'F')) AS ISK3
   FROM DESK0014 A ,MASK0001 B
   WHERE A.EMTIANO=B.EMTIANO AND A.FATNO=P_FATNO AND A.IRSNO=P_ISNO AND A.YIL=P_YIL AND A.MUSNO=P_MUSNO AND A.TALINO=P_TALINO ;
   END IADEDETAY;
   
  PROCEDURE AYBAZINDASATIS (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_BIRIMNO IN NUMBER,P_A1 IN NUMBER,P_A2 IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
   BEGIN OPEN CUR_RETURN FOR
   SELECT A.YIL AS SATISYILI,E.AYAD AS SATISAYI, A.BIRIMNO AS MAGAZANO,C.BIRIMADI AS MAGAZADI,F.AD AS REYONADI, A.EMTIANO AS URUNNO, B.EMTIAAD AS URUNADI,A.TBIRIMMIK AS MIKTAR,TO_CHAR(A.NETTUT,WFORMAT(P_YIL,P_SIRKET,'F')) AS NETTUT, A.BIRIM AS BIRIM 
           FROM DESK0025 A, MASK0001 B, TAGE0024 C,MASK0006 D,AY E,TAGE0003 F 
           WHERE B.AKTIF=1 AND D.AKTIF=1 AND B.REYONNO=F.REYONNO AND A.AY=E.AY AND A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS')) AND
            (A.BIRIMNO = C.BIRIMNO) AND (A.EMTIANO = B.EMTIANO) AND 
           A.EMTIANO=D.EMTIANO AND A.YIL=P_YIL AND A.BIRIMNO LIKE '%'||P_BIRIMNO||'%' AND D.MUSNO=P_MUSNO 
            AND D.TALINO=P_TALINO AND 
           A.AY BETWEEN P_A1 AND P_A2
           GROUP BY A.YIL,E.AYAD, A.BIRIMNO,C.BIRIMADI,F.AD, A.EMTIANO, B.EMTIAAD,A.NETTUT,A.BIRIM,A.TBIRIMMIK
           ORDER BY A.TBIRIMMIK DESC;
   
   
   
   END AYBAZINDASATIS;
   
PROCEDURE GRAFIKSATIS (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_AY IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR

SELECT C.BIRIMKISAAD,SUM(A.BRUTTUT) AS CIRO FROM DESK0025 A,MASK0006 B,TAGE0024 C WHERE C.AKTIF=1 AND A.EMTIANO=B.EMTIANO AND B.MUSNO=P_MUSNO
            AND B.TALINO=P_TALINO
            AND YIL=TO_CHAR(SYSDATE,'YYYY') AND A.AY=P_AY AND
            A.HARTIP IN (SELECT HARTIP FROM TAGE0056 WHERE ISLTIP IN ('PS','TS')) AND A.BIRIMNO=C.BIRIMNO AND B.AKTIF=1 GROUP BY C.BIRIMKISAAD;
            
      
END GRAFIKSATIS;

PROCEDURE GRAFIKSTOK (P_SIRKET IN NUMBER,P_YIL IN NUMBER,P_MUSNO IN VARCHAR2,P_TALINO IN VARCHAR2,CUR_RETURN OUT T_CURSOR) IS 
BEGIN OPEN CUR_RETURN FOR

SELECT BIRIMADI,TUTAR FROM ( 
                    SELECT B.BIRIMADI,
                    NVL(SUM(WSONSTOKTUT(A.EMTIANO,B.BIRIMNO,TRUNC(SYSDATE),'FF','1')),0) AS TUTAR 
                    FROM MASK0001 A,TAGE0024 B,MASK0006 C 
                    WHERE A.EMTIANO=C.EMTIANO AND C.AKTIF=1 AND A.AKTIF=1 AND B.AKTIF=1 AND C.AKTIF=1 
                    AND C.MUSNO=P_MUSNO
                    AND C.TALINO=P_TALINO AND B.BIRIMTIPI='MA'
                    GROUP BY B.BIRIMADI ) A WHERE TUTAR>0;

END GRAFIKSTOK;

PROCEDURE BORCALACAK (P_SIRKET IN NUMBER,P_YIL IN NUMBER ,CUR_RETURN OUT T_CURSOR) IS
BEGIN OPEN CUR_RETURN FOR
SELECT A.YIL,B.MUSAD,A.MUSNO ,A.TALINO,TO_CHAR(A.MUSBTOP,WFORMAT(P_YIL,P_SIRKET,'F')) AS BORC,
                      TO_CHAR(A.MUSATOP,WFORMAT(P_YIL,P_SIRKET,'F')) AS ALACAK, TO_CHAR((A.MUSATOP-A.MUSBTOP),WFORMAT(P_YIL,P_SIRKET,'F')) AS BAKIYE 
                      FROM MACR0201 A,MACR0001 B 
                      WHERE A.YIL=P_YIL
                      AND A.MUSNO=B.MUSNO AND A.TALINO=B.TALINO AND B.AKTIF=1 AND B.MTIP='S'
                      ORDER BY A.SONHRKTRH DESC;
END BORCALACAK;
   
END B2B_WEB;