PHP, SQL ve LOGO Muhasebe Programı
- Çek senet kartları LG_xxx_xx_CSCARD Tablosunda tutulur ve bu tablodan gördüğü harekete göre durum bilgisi (STATNO)ile alınabilir.
- Çekler Hareket gördükce LG_XXX_XX_CSTRANS Tablosunda çek refaransı olarak (CSREF) de tutulur. Ve her hareket birden çok satıra dağılır.
- Bankada işlem gören çekler için kesilen bordrolardan sonra banka fişi ve hareketi oluşmaktadır. LG_XXX_01_BNFLINE Ve LG_XXX_XX_BNFICHE Tablolarına bu bilgiler yazılmaktadır.
- Çek hareketleri Bordrolar aracılığı ile LG_XXX_XX_CSTRANS (ROLLREF Referansı ve LINENO_ ilede bordronun kaçıncı satırı ile)Tablosuna yazılır. Devir çekleri Hariç (Devir çekleri bordrolar aracılığı ile olmadığından) bordroların bilgilerinin yazıldığı LG_XXX_XX_CSROLL Tablosunda Sadece bordro başlık bilgileri bulunur.
- Bordro olarak işlediğiniz bütün bilgiler LG_XXX_XX_CLFLINE Tablosuna ve Çek Kart Bilgilerinizde Vade kontrolleri için Satır Satır LG_XXX_XX_PAYTRANS Tablosuna yazar. Oradan da Borc/Alacak Toplam bilgileri oluşur.
Bazı Önemli Tabloların Önemli Alanları
FATURA INVOICE tablosunda veri tabanına kaydedilen faturaların başlık bilgileri tutulmaktadır. Faturanın satır bilgilerine ulaşmak için STLINE tablosu okunmalıdır. Fatura türü ( TRCODE ) 1: Mal alım faturası
2: Perakende satış iade faturası 3: Toptan satış iade faturası 4: Alınan hizmet faturası 5: Alınan proforma fatura 6: alım iade faturası 7: Perakende satış faturası 8: Toptan satış faturası 9: Verilen hizmet faturası 10: Verilen proforma fatura 13: Alınan Fiyat farkı faturası 14: Verilen fiyat farkı faturası 26: Müstahsil makbuzu |
STOK FİŞLERİ Ambar fişi, giriş çıkış fişleri ve irsaliyele kayıtlarının başlık bilgileri STFICHE tablosunda tutulmaktadır. Stok hareketlerine ulaşmak için STFLINE tablosunun okunması gerekmektedir. Fiş türü ( TRCODE )
1:Mal alım irsaliyesi,
2:Per. sat. iade irs., 3:Topt.sat. iade irs., 4:Kons. çıkış iade irs., 5:Konsinye giriş irs., 6:Alım iade irs., 7:Perakende satış irs., 8:Toptan satış irs., 9:Konsinye çıkış irs., 10:Konsinye giriş iade irs., 11:Fire fişi, 12:Sarf fişi, 13:üretimden giriş fişi, 14:Devir fişi, 25:Ambar fişi, 26:Mustahsil irs., 50:Sayım Fazlası Fişi, 51: Sayım Eksiği Fişi |
Stok Kartları; LG_XXX_ITEMS Kart türü (CARDTYPE)
1 : Ticari mal 2 : Karma koli 3 : Depozitolu mal 4 : Sabit kıymet 10 : Hammadde 11 : Yarımamul 12 : Mamul 13 : Tükletim malı 20 : M.sınıfı (genel) 21 : M.sınıfı (tablolu) 22 : Firma dosyaları oluşturulurken default olarak eklenen malzeme sınıfı |
CARİ HESAP KARTI
LG_XXX_CLCARD
Kart tipi ( ACTIVE )
(1:Alıcı 2:Satıcı 3:Alıcı+Satıcı)
—– CARİ HESAP HAREKETİ LG_XXX_XX_CLFLINE
Hareket türü (TRCODE )
01:Nakit tahsilat, 02:Nakit ödeme, 03:Borç dekontu, 04:Alacak dekontu, 05:Virman Işlemi, 06:Kur farkı işlemi, 12:Özel işlem, 20:Gelen havaleler, 21:Gönderilen havaleler, 31:Mal alım fat., 32: Perakende satış iade fat. 33:Toptan satış iade fat., 34:Alınan hizmet fat., 35:Alınan proforma fat., 36:Alım iade fat., 37:Perakende satış fat., 38:Toptan satış fat., 39:Verilen hizmet faturası, 40:Verilen proforma fat., 41:Verilen vade farkı fat., 42:Alınan Vade farkı fat., 43:Alınan fiyat farkı fat., 44:Verilen fiyat farkı fat., 56:Müsthsil makbuzu, 61:Çek girişi, 62:Senet girişi, 63:Çek çıkış cari hesaba, 64:Senet çıkış cari hesaba |
ÇEK SENET KARTI LG_XXX_XX_CSCARD Şimdiki statüsü ( CURRSTAT ) (doc=1 ise 1:Müşteriye iade, 2:Portföyden tahsil, 3:Bankada tahsil, 4:Portföyde karşılıksız, 5:Bankada karşılıksız, 6:Müşteriden portföye iade, 7:Bankadan portföye iade, 8:Müşteriden karşılıksız iade, 9:Cirodan tahsil, A:Tahsil edilemiyor.)
(doc=2 ise 1:Müşteriye iade, 2:Portföy-den tahsil, 3:Bankada tahsil, 4:Portföyde protestolu, 5:Bankada protestolu, 6:Müşteriden portföye iade, 7:Bankadan portföye iade, 8:Müşteriden protestolu iade, 9:Cirodan tahsil, A:Tahsil edilemiyor) ÇEK SENET HAREKETİ LG_XXX_XX_CSTRANS
STATUS =
1:Portföyde, 2:Ciro edildi, 3:Teminata verildi, 4:Tahsile verildi, 5:Protestolu tahsile verildi, 6:İade edildi, 7:Protesto edildi, Tahsil edildi, Kendi çekimiz, 10:Borç senedimiz, 11:Karşılığı yok, 12:Tahsil edilemiyor |
BORDRO Cek ve sentelerle ilgili tüm hareketler cek/senet bordroları aracılığı ile kaydedilmekte ve bu kayıtlar CSROLL tablosunda tutulmaktadır. İşlem türü ( TRCODE )
1:Çek girişi, 2:Senet girişi, 3:Çek çıkış(cari hesaba), 4:Senet çıkış (cari hesaba), 5:Çek çıkış(banka tahsil), 6:Senet çıkış (Banka tahsil), 7:Çek çıkış (banka teminat), 8:Senet çıkış (banka teminat), 9:İşlem Bordrosu (müşteri çeki),10:İşlem bordrosu (müşteri senedi), 11:İşlem bordrosu (kendi çekimiz), 12:İşlem bordrosu (borç senedimiz)
(doc=3 ise 1:Müşteriden iade, 2:Müşteri-de tahsil.) (doc=4 ise 1:Müşteriden iade, 2:Müşteri-de tahsil, 3:Müşteride protesto, 4:Tahsil edilemiyor |
Logo SQL Tablo Yapısı
L_CAPIDEF | : | KURULUŞ BİLGİLERİ (AMBAR, İŞYER, FABRİKA VB.) |
L_CDBTMP | : | FORM BOYUTLARI |
L_CITY | : | ŞEHİRLER |
L_COUNTRY | : | ÜLKELER |
L_DAILYEXCHANGES | : | GÜNLÜK DÖVİZ KURLARI |
L_GOUSERS | : | KULLANICILAR |
L_LDOCNUM | : | DÖKÜMAN NUMARALAMA ŞABLONLARI |
L_NET | : | NETWORK KONTROLÜ |
L_POSTCODE | : | POSTA KODLARI |
L_RPFILTSXXX | : | KAYDEDİLEN RAPOR FİLTRELERİ |
L_RPLAYS_XXX | : | KAYDEDİLEN RAPOR TASARIMLARI |
L_SHPAGENT | : | SEVKİYAT FİRMALARI |
L_SHPTYPES | : | SEVKİYAT TÜRLERİ |
L_TRADGRP | : | TİCARİ İŞLEM GRUPLARI |
LG_XXX_ACCCODES | : | ENTEGRASYON BAĞLANTI KODLARI |
LG_XXX_ASCOND | : | ALIŞ/SATIŞ KOŞULLARI |
LG_XXX_BANKACC | : | BANKA HESAPLARI |
LG_XXX_BNCARD | : | BANKALAR |
LG_XXX_BOMASTER | : | ÜRÜN REÇETELERİ |
LG_XXX_BOMLINE | : | ÜRÜN REÇETE SATIRLARI |
LG_XXX_BOMREVSN | : | ÜRÜN REÇETE REVİZYONLARI |
LG_XXX_CHARASGN | : | MALZEME ÖZELLİK ATAMASI |
LG_XXX_CHARCODE | : | ÖZELLİK KODLARI |
LG_XXX_CHARVAL | : | ÖZELLİK DEĞERLERİ |
LG_XXX_CLCARD | : | CARİ HESAP KARTLARI |
LG_XXX_CLINTEL | : | CARİ HESAP İSTİHBARAT BİLGİLERİ |
LG_XXX_COPRDBOM | : | REÇETE-EK ÜRÜN ATAMASI |
LG_XXX_CRDACREF | : | KART-MUHASEBE KODLARI |
LG_XXX_DECARDS | : | İNDİRİM/MASRAF KARTLARI |
LG_XXX_DISPLINE | : | İŞ EMİRLERİ |
LG_XXX_DISTLINE | : | DAĞITIM ŞABLONU SATIRLARI |
LG_XXX_DISTTEMP | : | DAĞITIM ŞABLONLARI |
LG_XXX_EMCENTER | : | MASRAF MALZEMELERİ |
LG_XXX_EMGRPASS | : | ÇALIŞAN-GRUP ATAMASI |
LG_XXX_EMPGROUP | : | ÇALIŞAN GRUBU |
LG_XXX_EMPLOYEE | : | ÇALIŞANLAR |
LG_XXX_EMUHACC | : | MUHASEBE HESAPLARI |
LG_XXX_ENGCLINE | : | MÜHENDİSLİK DEĞİŞİKLİĞİ İŞLEMİ |
LG_XXX_FAREGIST | : | SABİT KIYMET KAYITLARI |
LG_XXX_FAYEAR | : | SABİT KIYMET YILLIK KAYDI |
LG_XXX_FIRMDOC | : | DÖKÜMAN KATALOG GİRİŞİ(WATERMARK) |
LG_XXX_INVDEF | : | MALZEME-AMBAR BİLGİLERİ |
LG_XXX_ITEMS | : | MALZEMELER |
LG_XXX_ITEMSUBS | : | MALZEME ALTERNATİFLERİ |
LG_XXX_ITMBOMAS | : | MALZEME-ÜRECETESİ ATAMASI |
LG_XXX_ITMCLSAS | : | MALZEME-MALZEME SINIFI ATAMASI |
LG_XXX_ITMFACTP | : | MALZEME-FABRİKA BİLGİLERİ |
LG_XXX_ITMUNITA | : | MALZEME-BİRİM ATAMASI |
LG_XXX_ITMWSDEF | : | MALZEME-İŞ İST. BİLGİLERİ |
LG_XXX_ITMWSTOT | : | MALZEME-İŞ İST. TOPLAMLARI |
LG_XXX_KSCARD | : | KASALAR |
LG_XXX_LABORREQ | : | ÇALIŞAN İHTİYAÇLARI |
LG_XXX_LNGEXCSETS | : | BAZI KAYITLARIN DİĞER DİLLERDEKİ AÇIKLAMALARI |
LG_XXX_LNOPASGN | : | OPERASYON-MALZEME İLİŞKİSİ |
LG_XXX_LOCATION | : | STOK YERLERİ |
LG_XXX_LOGREP | : | LOG (İZLEME) KAYDI |
LG_XXX_OCCUPATN | : | KAYNAK KULLANIMLARI (ÜRETİM) |
LG_XXX_OPATTASG | : | OPERASYON-ÖZELLİK ATAMASI |
LG_XXX_OPERTION | : | OPERASYONLAR |
LG_XXX_OPRTREQ | : | OPERASYON İHTİYACLARI |
LG_XXX_PAYLINES | : | ÖDEME PLAN SATIRLARI |
LG_XXX_PAYPLANS | : | ÖDEME PLANLARI |
LG_XXX_PEGGING | : | İŞLEM BAĞLANTILARI (ÜRETİM EMRİ, SİPARİŞ) |
LG_XXX_PRCARDS | : | PROMOSYON KARTLARI |
LG_XXX_PRCLIST | : | ALIŞ/SATIŞ FİYATLARI |
LG_XXX_PRODORD | : | ÜRETİM EMİRLERİ |
LG_XXX_PRVOPASG | : | ÖCEKİ OPERASYON İLİŞKİLERİ |
LG_XXX_QASGN | : | KALİTE KONTROL HAREKETİ- KALİTE KONTROL ATAMASI |
LG_XXX_QCLVAL | : | KALİTE KONTROL DEĞERLERİ |
LG_XXX_QCSET | : | KALİTE KONTROL SETLERİ |
LG_XXX_QCSLINE | : | KALİTE KONTROL SATIRLARI |
LG_XXX_ROUTE | : | SATIŞ YÖNETİM RAPORLARI |
LG_XXX_ROUTETRS | : | SATIŞ ROTA SATIRLARI |
LG_XXX_ROUTING | : | ÜRETİM ROTALARI |
LG_XXX_RTNGLINE | : | ÜRETİM ROTA STIRLARI |
LG_XXX_SELCHVAL | : | MALZEME-ÖZELLİK DEĞERLERİ |
LG_XXX_SLSCLREL | : | SATIŞ ELEMANI-CARİ HESAP İLİŞKİSİ |
LG_XXX_SLSMAN | : | SATIŞ ELEMANLARI |
LG_XXX_SPECODES | : | ÖZEL KODLAR |
LG_XXX_SRVCARD | : | HİZMET KARTLARI |
LG_XXX_SRVUNITA | : | HİZMET KAYDI-BİRİM ATAMASI |
LG_XXX_STCOMPLN | : | KARMA KOLİ SATIRLARI |
LG_XXX_SUPPASGN | : | MALZEME-TEDARİKÇİ ATAMASI |
LG_XXX_TARGETS | : | SATIŞ ELEMANI HAREKETLERİ |
LG_XXX_TOOLREQ | : | ARAÇ İHTİYACLARI |
LG_XXX_TRGPAR | : | TRİGGER PARAMETRELERİ |
LG_XXX_UNITSETC | : | BİRİM SETLERİ ARASI ÇEVRİM KATSAYILARI |
LG_XXX_UNITSETF | : | BİRİM SETLERİ |
LG_XXX_UNITSETL | : | BİRİMLER |
LG_XXX_WORKSTAT | : | İŞ İSTASYONLARI |
LG_XXX_WSATTASG | : | İŞ İST.-ÖZELLİK ATAMASI |
LG_XXX_WSATTVAS | : | İŞ İST.-ÖZELLİK DEĞERİ ATAMASI |
LG_XXX_WSCHCODE | : | İŞ İSTASYONU ÖZELLİKLERİ |
LG_XXX_WSCHVAL | : | İŞ İSTASYONU ÖZELLİK DEĞERLERİ |
LG_XXX_WSGRPASS | : | İŞ İSTASYONU-GRUP ATAMASI |
LG_XXX_WSGRPF | : | İŞ İSTASYONU GRUPLARI |
LG_XXX_XX_BNFICHE | : | BANKA FİŞLERİ |
LG_XXX_XX_BNFLINE | : | BANKA HAREKETLERİ |
LG_XXX_XX_BNTOTFIL | : | BANKA AYLIK TOPLAMLARI |
LG_XXX_XX_CLFICHE | : | CARİ HESAP FİŞERİ |
LG_XXX_XX_CLFLINE | : | CARİ HESAP HAREKETLERİ |
LG_XXX_XX_CLRNUMS | : | CARİ HESAP RİSK TABLOLARI |
LG_XXX_XX_CLTOTFIL | : | CARİ HESAP AYLIK TOPLAMLARI |
LG_XXX_XX_CSCARD | : | ÇEK/SENET KARTLARI |
LG_XXX_XX_CSHTOTS | : | KASA AYLIK TOPLAMLARI |
LG_XXX_XX_CSROLL | : | ÇEK/SENET BORDROLARI |
LG_XXX_XX_CSTRANS | : | ÇEK/SENET HAREKETLERİ |
LG_XXX_XX_EMFICHE | : | MUHASEBE FİŞLERİ |
LG_XXX_XX_EMFLINE | : | MUHASEBE HAREKETLERİ |
LG_XXX_XX_EMUHTOT | : | MUHASEBE AYLIK TOPLAMLARI |
LG_XXX_XX_FOLDER | : | DÖKÜMAN KATALOG BİLGİLERİ |
LG_XXX_XX_INVOICE | : | FATURALAR |
LG_XXX_XX_KSLINES | : | KASA İŞLEMLERİ |
LG_XXX_XX_ORFICHE | : | SİPARİŞ FİŞLERİ |
LG_XXX_XX_ORFLINE | : | SİPARİŞ HAREKETLERİ |
LG_XXX_XX_PAYTRANS | : | ÖDEME/TAHSİLAT HAREKETLERİ |
LG_XXX_XX_PERDOC | : | DÖKÜMAN BİLGİLERİ |
LG_XXX_XX_PRDCOST | : | MALİYET DÖNEM KAPAMA KAYITLARI |
LG_XXX_XX_PRODUCER | : | MÜSTAHSİL FATURASI |
LG_XXX_XX_SERILOTN | : | MALZEME SERİ LOT NO. BİLGİLERİ |
LG_XXX_XX_SLQCASGN | : | KALİTE KONTROL HAREKETLERİ |
LG_XXX_XX_SLTRANS | : | SERİ/LOT HAREKETLERİ |
LG_XXX_XX_SRVNUMS | : | AYLIK HİZMET TOPLAMLARI |
LG_XXX_XX_SRVTOT | : | AYLIK HİZMET ALIŞ/SATIŞ TOPLAMLARI |
LG_XXX_XX_STFICHE | : | STOK FİŞLERİ |
LG_XXX_XX_STINVENS | : | MALZEME ALIŞ/SATIŞ AYLIK TOPLAMLARI |
LG_XXX_XX_STINVTOT | : | GÜNLÜK MALZEME AMBAR TOPLAMLARI |
LG_XXX_XX_STLINE | : | MALZEME HAREKETLERİ |
LG_XXX_XX_TRANSAC | : | FİRMA DÖNEM BİLGİLERİ |
Merhaba arkadaşlar bugün sizlere php ile otomatik backlink kontrol kodlarını paylaşmak istiyorum. Amacı nedir diyen arkadaşlar için hacklink aldığınızı varsayalım ve bunu hergün girip kontrol etmek istemiyorsunuz kaldırıldığında veya bulunamadığında bana otomatik olarak e-posta gelsin diyorsanız aşağıdaki kodları inceleyebilirsiniz.
Logo Carilerini php ile listeleme
<?php
$odbcSql = "SELECT C.CODE ,C.DEFINITION_ ,ISNULL(SUM(T.DEBIT),0) BORC ,ISNULL(SUM(T.CREDIT),0) ALACAK ,ISNULL(SUM(T.DEBIT-T.CREDIT),0) BAKIYE FROM LG_015_CLCARD C
LEFT JOIN LG_015_01_CLTOTFIL T ON C.LOGICALREF=T.CARDREF AND T.TOTTYP=1 WHERE C.CARDTYPE <>22 AND C.ACTIVE=0 GROUP BY C.CODE ,C.DEFINITION_";
$odbcQry = odbc_exec($odbcCon, $odbcSql);
$out = '<table>
<tbody>
<tr>
<th>Kodu</th>
<th>Adı</th>
<th>Borç</th>
<th>Alacak</th>
<th>Kalan Bakiye</th>
</tr>
</tbody>';
//$tahsilat = 0;
while($odbcArr=odbc_fetch_array($odbcQry)){
### mssql tablolar ###
$borc = number_format($odbcArr["BORC"],2);
$alacak = number_format($odbcArr["ALACAK"],2);
//$tahsilat += number_format($odbcArr["BORC"]-$odbcArr["ALACAK"],2);
$out .= '<tr>
<td>'.$odbcArr["CODE"].'</td>
<td>'.$odbcArr["DEFINITION_"].'</td>
<td>'.$borc.'</td>
<td>'.$alacak.'</td>
<td>'.number_format($odbcArr["BORC"]-$odbcArr["ALACAK"],2).'</td>
</tr>';
}
$out .= '</table>';
$odbcSql = "SELECT C.CODE ,C.DEFINITION_ ,ISNULL(SUM(T.DEBIT),0) BORC ,ISNULL(SUM(T.CREDIT),0) ALACAK ,ISNULL(SUM(T.DEBIT-T.CREDIT),0) BAKIYE FROM LG_015_CLCARD C
LEFT JOIN LG_015_01_CLTOTFIL T ON C.LOGICALREF=T.CARDREF AND T.TOTTYP=1 WHERE C.CARDTYPE <>22 AND C.ACTIVE=0 GROUP BY C.CODE ,C.DEFINITION_";
$odbcQry = odbc_exec($odbcCon, $odbcSql);
$out = '<table>
<tbody>
<tr>
<th>Kodu</th>
<th>Adı</th>
<th>Borç</th>
<th>Alacak</th>
<th>Kalan Bakiye</th>
</tr>
</tbody>';
//$tahsilat = 0;
while($odbcArr=odbc_fetch_array($odbcQry)){
### mssql tablolar ###
$borc = number_format($odbcArr["BORC"],2);
$alacak = number_format($odbcArr["ALACAK"],2);
//$tahsilat += number_format($odbcArr["BORC"]-$odbcArr["ALACAK"],2);
$out .= '<tr>
<td>'.$odbcArr["CODE"].'</td>
<td>'.$odbcArr["DEFINITION_"].'</td>
<td>'.$borc.'</td>
<td>'.$alacak.'</td>
<td>'.number_format($odbcArr["BORC"]-$odbcArr["ALACAK"],2).'</td>
</tr>';
}
$out .= '</table>';
Logo 'da Hareket Görmeyen Cari Hesap Kartların Listelenmesi
SELECT * FROM dbo.LG_001_CLCARD WHERE dbo.LG_001_CLCARD.LOGICALREF NOT IN (SELECT CLIENTREF FROM dbo.LG_001_01_CLFLINE)
Logo'da Stok Miktarı, Ürün Fiyatı Gibi Bilgileri Listeleyen SQL kodu:
SELECT URUN.CODE AS STOK_KOD,
URUN.NAME AS STOK_ADI,
URUN.STGRPCODE AS GRUP_KODU,
URUN.CARDTYPE AS KARTTIPI,
AMBARM.MIKTAR AS STOK,
ALIS.FIYAT AS ALIS_FIYATI,
SATIS.FIYAT AS SATIS_FIYATI,
BIRIM.CODE AS BIRIM,
BBARKOD.BARCODE AS BARKOD
FROM LG_005_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_005_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF) AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'FIYAT',CARDREF FROM LG_005_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF)) ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'FIYAT',CARDREF FROM LG_005_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF)) SATIS ON URUN.LOGICALREF = SATIS.CARDREF
LEFT JOIN LG_005_UNITSETL BIRIM ON URUN.UNITSETREF=BIRIM.UNITSETREF
LEFT JOIN LG_005_ITMUNITA BBARKOD ON URUN.UNITSETREF=BBARKOD.ITEMREF
WHERE URUN.CODE LIKE '%SER-041'
URUN.NAME AS STOK_ADI,
URUN.STGRPCODE AS GRUP_KODU,
URUN.CARDTYPE AS KARTTIPI,
AMBARM.MIKTAR AS STOK,
ALIS.FIYAT AS ALIS_FIYATI,
SATIS.FIYAT AS SATIS_FIYATI,
BIRIM.CODE AS BIRIM,
BBARKOD.BARCODE AS BARKOD
FROM LG_005_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_005_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF) AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'FIYAT',CARDREF FROM LG_005_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF)) ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'FIYAT',CARDREF FROM LG_005_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF)) SATIS ON URUN.LOGICALREF = SATIS.CARDREF
LEFT JOIN LG_005_UNITSETL BIRIM ON URUN.UNITSETREF=BIRIM.UNITSETREF
LEFT JOIN LG_005_ITMUNITA BBARKOD ON URUN.UNITSETREF=BBARKOD.ITEMREF
WHERE URUN.CODE LIKE '%SER-041'
Alternatif
SELECT FIYATLAR.PRICE,
URUN.LOGICALREF,
URUN.CODE,
URUN.NAME,
AMBARM.MIKTAR AS STOK
FROM LG_005_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR, STOCKREF FROM LG_005_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF) AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT PRICE, CARDREF FROM LG_005_PRCLIST FIYATLISTESI WHERE FIYATLISTESI.LOGICALREF = (SELECT MAX(LOGICALREF) FROM LG_005_PRCLIST FIYATMAXLIST WHERE FIYATMAXLIST.CARDREF=FIYATLISTESI.CARDREF GROUP BY CARDREF)) FIYATLAR ON FIYATLAR.CARDREF=URUN.LOGICALREF
WHERE URUN.CODE LIKE '%8024%'
URUN.LOGICALREF,
URUN.CODE,
URUN.NAME,
AMBARM.MIKTAR AS STOK
FROM LG_005_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR, STOCKREF FROM LG_005_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF) AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT PRICE, CARDREF FROM LG_005_PRCLIST FIYATLISTESI WHERE FIYATLISTESI.LOGICALREF = (SELECT MAX(LOGICALREF) FROM LG_005_PRCLIST FIYATMAXLIST WHERE FIYATMAXLIST.CARDREF=FIYATLISTESI.CARDREF GROUP BY CARDREF)) FIYATLAR ON FIYATLAR.CARDREF=URUN.LOGICALREF
WHERE URUN.CODE LIKE '%8024%'
URUN.CODE AS 'ÜRÜN KODU',
URUN.NAME AS 'ÜRÜN ADI',
'GENEL '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END
FROM
{oj LG_FFF_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF FROM LG_FFF_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF FROM LG_FFF_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
SATIS ON URUN.LOGICALREF = SATIS.CARDREF
}
WHERE URUN.CARDTYPE<>'22'
ORDER BY URUN.CODE
URUN.NAME AS 'ÜRÜN ADI',
'GENEL '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END
FROM
{oj LG_FFF_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_FFF_DD_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF FROM LG_FFF_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF FROM LG_FFF_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
SATIS ON URUN.LOGICALREF = SATIS.CARDREF
}
WHERE URUN.CARDTYPE<>'22'
ORDER BY URUN.CODE
Malzemlerin Listesini Depo Bazında Listeleyen MsSQL Kodu
SELECT ITEMS.CODE, ITEMS.NAME, STINVTOT.INVENNO AS DEPO, SUM(STINVTOT.ONHAND) AS MIKTAR
FROM LV_001_02_STINVTOT AS STINVTOT LEFT OUTER JOIN
LG_001_ITEMS AS ITEMS ON STINVTOT.STOCKREF = ITEMS.LOGICALREF
WHERE (ITEMS.CARDTYPE = 1) AND STINVTOT.INVENNO = -1 /* Tüm Depolar için -1 */
GROUP BY ITEMS.CODE, ITEMS.NAME, STINVTOT.INVENNO, ITEMS.CARDTYPE
HAVING (SUM(STINVTOT.ONHAND) <> 0)
FROM LV_001_02_STINVTOT AS STINVTOT LEFT OUTER JOIN
LG_001_ITEMS AS ITEMS ON STINVTOT.STOCKREF = ITEMS.LOGICALREF
WHERE (ITEMS.CARDTYPE = 1) AND STINVTOT.INVENNO = -1 /* Tüm Depolar için -1 */
GROUP BY ITEMS.CODE, ITEMS.NAME, STINVTOT.INVENNO, ITEMS.CARDTYPE
HAVING (SUM(STINVTOT.ONHAND) <> 0)
Cari Hesap Kodu "M" ile başlayan Cari Hesapların son bakiyesini getiren MS Query kodu
SELECT CLCARD.CODE AS KODU, CLCARD.DEFINITION_ AS ÜNVANI, SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) AS BAKİYE, CASE WHEN SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) < 0 THEN SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) * - 1 ELSE 0 END AS [Alacak Bakiye], CLCARD.ADDR1 AS Adres1, CLCARD.ADDR2 AS Adres2, CLCARD.CITY AS Şehir, CLCARD.COUNTRY AS Ülke, CLCARD.POSTCODE AS PostaKodu, CLCARD.TELNRS1 AS TEL1, CLCARD.TELNRS2 AS TEL2, CLCARD.FAXNR AS FAKS, CLCARD.TAXNR AS Vergi_No, CLCARD.TAXOFFICE AS Vergi_Dairesi, CLCARD.DISCRATE AS iskonto, CLCARD.TOWN AS İLÇE, CLCARD.DISTRICT AS SEMT, CLCARD.STORECREDITCARDNO AS Plasiyer, CLCARD.ACTIVE AS Statüsü FROM LG_086_01_CLFLINE CLFLINE INNER JOIN LG_086_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0 GROUP BY CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.ADDR1, CLCARD.ADDR2, CLCARD.CITY, CLCARD.COUNTRY, CLCARD.POSTCODE, CLCARD.TELNRS1, CLCARD.TELNRS2, CLCARD.FAXNR, CLCARD.TAXNR, CLCARD.TAXOFFICE, CLCARD.DISCRATE, CLCARD.TOWN, CLCARD.DISTRICT, CLCARD.STORECREDITCARDNO, CLCARD.ACTIVE HAVING (CLCARD.CODE LIKE 'M%') AND (CLCARD.ACTIVE = 0) ORDER BY CLCARD.BAKİYE DESC
Bakiye veren cari kartların ödeme vadelerinin hangi gün olacağına ait sql sorgu
SELECT LG_010_CLCARD.CODE AS 'KOD', LG_010_CLCARD.DEFINITION_ AS 'UNVANI', LG_010_01_INVOICE.FICHENO AS 'FATURA NO', LG_010_01_PAYTRANS.PROCDATE AS 'FATURA TARİHİ', LG_010_01_PAYTRANS.DATE_ AS 'ÖDEME TARİHİ', LG_010_01_PAYTRANS.TOTAL AS 'TUTAR'
FROM VERITABANI_ISMI.dbo.LG_010_01_INVOICE LG_010_01_INVOICE, VERITABANI_ISMI.dbo.LG_010_01_PAYTRANS LG_010_01_PAYTRANS, VERITABANI_ISMI.dbo.LG_010_CLCARD LG_010_CLCARD
WHERE LG_010_01_PAYTRANS.FICHEREF = LG_010_01_INVOICE.LOGICALREF AND LG_010_01_INVOICE.CLIENTREF = LG_010_CLCARD.LOGICALREF AND LG_010_01_PAYTRANS.CARDREF = LG_010_CLCARD.LOGICALREF AND ((LG_010_01_INVOICE.TRCODE=8) AND (LG_010_01_PAYTRANS.MODULENR=4) AND (LG_010_01_PAYTRANS.PROCDATE Between ? And ?))
ORDER BY LG_010_01_INVOICE.FICHENO
FROM VERITABANI_ISMI.dbo.LG_010_01_INVOICE LG_010_01_INVOICE, VERITABANI_ISMI.dbo.LG_010_01_PAYTRANS LG_010_01_PAYTRANS, VERITABANI_ISMI.dbo.LG_010_CLCARD LG_010_CLCARD
WHERE LG_010_01_PAYTRANS.FICHEREF = LG_010_01_INVOICE.LOGICALREF AND LG_010_01_INVOICE.CLIENTREF = LG_010_CLCARD.LOGICALREF AND LG_010_01_PAYTRANS.CARDREF = LG_010_CLCARD.LOGICALREF AND ((LG_010_01_INVOICE.TRCODE=8) AND (LG_010_01_PAYTRANS.MODULENR=4) AND (LG_010_01_PAYTRANS.PROCDATE Between ? And ?))
ORDER BY LG_010_01_INVOICE.FICHENO
Tüm Cari Haketleri Zamana Göre Listeme
SELECT DEFINITION_ AS CARİ_ADI,HAREKET.DATE_ AS TARİH,MONTH(HAREKET.DATE_)AS AY,HAREKET.SPECODE AS OK,HAREKET.CYPHCODE AS Y_KODU,TRANNO AS BELGE_NO,LINEEXP AS ACIKLAMA,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH AS İŞYERİ,MODULENR AS MODUL,HAREKET.TRCODE AS TURU,CITY AS SEHİR,TELNRS1 AS TELNO,TAXNR AS VERGI_NO,TAXOFFICE AS V_DAIRE,INCHARGE AS İLGİLİ,HAREKET.CANCELLED AS IPTAL_DURUMU,HAREKET.ACCOUNTED AS MUH_DURUMU,SOURCEFREF AS KNO,CODE AS CARİ_KODU FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF
WHERE HAREKET.MODULENR NOT IN ('5','7')
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECCODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_CLFICHE AS FIS ON FIS.LOGICALREF=HAREKET.SOURCEFREF
WHERE HAREKET.MODULENR='5'
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,HAREKET.MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,HAREKET.SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_BNFICHE AS FIS ON FIS.FICHENO=HAREKET.TRANNO
WHERE HAREKET.MODULENR='7' AND HAREKET.TRCODE='20' AND FIS.TRCODE='3'
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,HAREKET.MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,HAREKET.SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_BNFICHE AS FIS ON FIS.FICHENO=HAREKET.TRANNO
WHERE HAREKET.MODULENR='7' AND HAREKET.TRCODE='21' AND FIS.TRCODE='4'
WHERE HAREKET.MODULENR NOT IN ('5','7')
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECCODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_CLFICHE AS FIS ON FIS.LOGICALREF=HAREKET.SOURCEFREF
WHERE HAREKET.MODULENR='5'
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,HAREKET.MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,HAREKET.SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_BNFICHE AS FIS ON FIS.FICHENO=HAREKET.TRANNO
WHERE HAREKET.MODULENR='7' AND HAREKET.TRCODE='20' AND FIS.TRCODE='3'
UNION ALL
SELECT DEFINITION_,HAREKET.DATE_,MONTH(HAREKET.DATE_)AS AY,FIS.SPECODE AS OK,HAREKET.CYPHCODE,TRANNO,LINEEXP,((1-HAREKET.SIGN)*AMOUNT) AS BORC,HAREKET.SIGN*AMOUNT AS ALACAK,HAREKET.BRANCH,HAREKET.MODULENR,HAREKET.TRCODE,CITY,TELNRS1,TAXNR,TAXOFFICE,INCHARGE,HAREKET.CANCELLED,HAREKET.ACCOUNTED,HAREKET.SOURCEFREF,CODE FROM LG_XXX_01_CLFLINE AS HAREKET INNER JOIN LG_XXX_CLCARD AS KART ON HAREKET.CLIENTREF=KART.LOGICALREF INNER JOIN LG_XXX_01_BNFICHE AS FIS ON FIS.FICHENO=HAREKET.TRANNO
WHERE HAREKET.MODULENR='7' AND HAREKET.TRCODE='21' AND FIS.TRCODE='4'
Cari Hareket Detayı
SELECT CTRNS.DOCODE, CTRNS.BRANCH, CTRNS.DEPARTMENT, CTRNS.TRCODE, CTRNS.STATUS, CTRNS.SPECODE, CTRNS.TRCURR, CTRNS.LOGICALREF, CTRNS.MODULENR, CTRNS.CANCELLED, CTRNS.SIGN, CTRNS.AMOUNT, CTRNS.REPORTNET, CTRNS.TRANNO, CTRNS.LINEEXP, CTRNS.CYPHCODE, CTRNS.TRNET, CTRNS.TRADINGGRP, CTRNS.SOURCEFREF, CTRNS.CLIENTREF, CTRNS.CLPRJREF, CTRNS.MONTH_, CTRNS.YEAR_, PAYPL.CODE, PAYPL.DEFINITION_, PAYPL.SPECODE, PAYPL.CYPHCODE, CLFIC.LOGICALREF, CLFIC.INVOREF, CLFIC.SPECCODE, CLFIC.CYPHCODE, CLFIC.GENEXP1, INVFC.TOTALDISCOUNTS, INVFC.TOTALVAT, INVFC.STATUS, INVFC.SPECODE, INVFC.CANCELLED, INVFC.FROMKASA, INVFC.CYPHCODE, INVFC.FICHENO AS FATURANO, INVFC.LOGICALREF, INVFC.GENEXP1, INVFC.TRADINGGRP, INVFC.PROJECTREF, INVFC.GRPCODE, INVFC.GRPCODE, RLFIC.LOGICALREF, RLFIC.SPECODE, RLFIC.CANCELLED, RLFIC.CYPHCODE, RLFIC.GENEXP1, RLFIC.TRADINGGRP, RLFIC.DATE_, RLFIC.AVERAGEAGE, RLFIC.PROJECTREF, GLFIC.FICHENO, SLSMC.CODE, SLSMC.DEFINITION_, SLSMC.POSITION_, SLSMC.LOGICALREF, CASE WHEN CTRNS.TRCODE=14 AND CTRNS.MODULENR=5 THEN 0 ELSE 1 END AS TRTEMP FROM LG_005_01_CLFLINE CTRNS WITH(NOLOCK) LEFT OUTER JOIN LG_005_PAYPLANS PAYPL WITH(NOLOCK) ON (CTRNS.PAYDEFREF = PAYPL.LOGICALREF) LEFT OUTER JOIN LG_005_01_CLFICHE CLFIC WITH(NOLOCK) ON (CTRNS.SOURCEFREF = CLFIC.LOGICALREF) LEFT OUTER JOIN LG_005_01_INVOICE INVFC WITH(NOLOCK) ON (CTRNS.SOURCEFREF = INVFC.LOGICALREF) LEFT OUTER JOIN LG_005_01_CSROLL RLFIC WITH(NOLOCK) ON (CTRNS.SOURCEFREF = RLFIC.LOGICALREF) LEFT OUTER JOIN LG_005_01_EMFICHE GLFIC WITH(NOLOCK) ON (CTRNS.ACCFICHEREF = GLFIC.LOGICALREF) LEFT OUTER JOIN LG_SLSMAN SLSMC WITH(NOLOCK) ON (INVFC.SALESMANREF = SLSMC.LOGICALREF) LEFT OUTER JOIN LG_005_CLCARD CLNTC WITH(NOLOCK) ON (CTRNS.CLIENTREF = CLNTC.LOGICALREF) WHERE (CTRNS.TRCODE IN (31, 32, 33, 34, 36, 37, 38, 39, 43, 44, 56, 1, 2, 3, 4, 5, 6, 12, 14, 41, 42, 45, 46, 70, 71, 20, 21, 28, 29, 61, 62, 63, 64)) AND (CTRNS.CLIENTREF = 39) ORDER BY CTRNS.DATE_, TRTEMP
Hareket Tarihleri için her kayıt satırı için aşağıdaki sorgu çalıştırılması lazım
SELECT PTRNS.DATE_ FROM LG_005_01_INVOICE INVFC WITH(NOLOCK) LEFT OUTER JOIN LG_005_01_PAYTRANS PTRNS WITH(NOLOCK) ON (INVFC.LOGICALREF = PTRNS.FICHEREF) LEFT OUTER JOIN LG_005_01_EMFICHE GLFIC WITH(NOLOCK) ON (INVFC.ACCFICHEREF = GLFIC.LOGICALREF) WHERE INVFC.LOGICALREF = $SOURCEFREF AND PTRNS.MODULENR = $MODULENR
Kaynaklar:
- http://logo-yazilim-destek-servis.blogspot.com.tr/2012_09_01_archive.html
- http://www.pythontr.com/makale/logo-lks-stok-karti-adi-aciklama-fiyat-ve-bir-157
- http://www.logodestek.gen.tr/index.php?topic=1117.0;wap2
- http://bluehackman.blogspot.com.tr/2013/08/logo-tablo-yaps-ve-baz-onemli-tablolar.html
- http://www.excel.web.tr/archive/index.php/t-64961.html
- http://www.kemalbayat.com.tr/2015/01/30/logo-tiger-depo-bazinda-malzemestok-durumlari/
Yorumunuzu Ekleyin