membuka excel dengan Link dari Files lain #REF

untuk membuka excel yang memiliki link value dari files lain agar tidak #REF, maka bisa menghapus links dari file lain tersebut dengan cara :

1. Copy files ke folder lain atau langsung ke no 2.
2. Rename menjadi “namafiles.zip” (ubah dar “namafiles.xlsx”)
3. Open file menggunakan WinZip atau Winrar
4. buka xl subfolder didalam filesnya
5. Delete “externalLinks” folder
6. Rename kembali file ke “namafiles.xlsx”
7. Open files dan choose to repair sheet

saat repair, excel akan memberitahu bahwa ada link dari files lain yang rusak, dan memperbaikinya. done!

Actual Delivery Per whscode

SELECT T0.[docentry] As ‘Num’, T0.[CreateDate], T0.[DocDate], T0.[DocNum] as ‘No. DO’, T1.[BaseDocNum] as ‘No. SO’, T0.[CardCode] As ‘BP Code’, T0.[CardName], T0.[NumAtCard], T1.[WhsCode] as ‘WHSE’, T1.[ItemCode], T1.[Dscription], t0.comments, T1.[Quantity], T1.[Price], T1.OCRCODE AS ‘PLANT’, T3.docnum as [No. return], t2.quantity as [Qty Return], ISNULL(T1.[Quantity]-T2.[Quantity],T1.[Quantity]) AS [NET QTY]

 

FROM ODLN T0

left JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN RDN1 T2 ON T0.DOCENTRY=T2.BASEENTRY

LEFT JOIN ORDN T3 ON T2.[DocEntry] = T3.[DocEntry]

 

 

 

WHERE T0.[DocDate] between [%0] and [%1] and left(t1.whscode,3) = [%2] and T0.[Canceled] = ‘N’

Open po per vendor

SELECT DISTINCT T1.[DocEntry] AS [ID PO],T0.[DocDate],  T0.[DocNum] as [NO PO], T0.[DocType] AS [TYPE], T1.[ItemCode], T1.[Dscription], T1.[Quantity], t2.docentry AS [ID GR], T2.[DocNum] AS [NO GRPO], T3.DSCRIPTION, T3.QUANTITY AS [QTY GR], T3.BASEOPNQTY – T3.QUANTITY AS [SISA PO]

 

FROM

OPOR T0

LEFT JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

LEFT JOIN PDN1 T3 ON T3.baseENTRY = T1.docENTRY and T3.baseline = T1.LINENUM

LEFT JOIN OPDN T2 ON T2.[DocEntry] = T3.[DocEntry]

 

 

WHERE T0.[CANCELED] =’n’ and  T0.[DocStatus] =’o’ and T0.[cardname] =[%0]

can be used for SO too

AHCI TO IDE WIN 10

BOOT TO SAFE MODE VIA RECOVERY/TROUBLESHOOT MODE

HKEY_LOCAL_MACHINE_System_CurrentControlSet_Services_iaStor
HKEY_LOCAL_MACHINE_System_CurrentControlSet_Services_iaStorV

FIND KEY  “START” change values from 0 To 1

Now navigate to

HKEY_LOCAL_MACHINE_System_CurrentControlSet_Services_PciIde
,then change the start value to “0”

ALSO

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\iaStorAVC\StartOverride

FIND KEY  “0” change values from 3 To 0

REBOOOT

Restore Missing List recent Document on taskbar

Check the recent document list within word application. If its fine then there might some corruption in the recent items stored in ‘AutomaticDestinations’ folder.

To resolve and fix the issue, follow these steps:

1)   Browse to the location mentioned : %AppData%\Microsoft\Windows\Recent\AutomaticDestinations

Note: the folder ‘AutomaticDestinations’ is hidden

2)   Delete all the files whose size is 1MB or more.

Note: If you cannot find any file sized 1MB or more then delete all the files. This will eventually remove the Jump Lists’ recent items for all the applications. The recent items on the Jump Lists will be re-added as the program is been used. If you delete all the files then ensure that the applications are pinned again to the taskbar.

 

credit : Somasundaram Pandiaraj ~Microsoft~

Delivered SO

SELECT T0. DOCENTRY, T0.[DocNum] AS [NO. DO], T0.[DocDate], T0.[CardName], T0.[NumAtCard] AS [NO. PO CUST], t1.baseref AS [NO. SO],T1.[ItemCode], T1.[Dscription],T1.[Quantity], T1.[WhsCode] AS [WHS], T3.[DocNum] AS[NO RETURN], T2.[Quantity] AS [QTY RETURN], isnull(t1.quantity-t2.quantity, t1.quantity) as [Net Qty], t7.DOCNUM AS [NO CN], T6.QUANTITY AS[QTY CN], t0.comments, T5.DOCNUM AS [NO.INV]

FROM ODLN T0
LEFT JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT JOIN RDN1 T2 ON T0.DOCENTRY=T2.BASEENTRY
LEFT JOIN ORDN T3 ON T2.[DocEntry] = T3.[DocEntry]
LEFT JOIN INV1 T4 ON T0.DOCENTRY = T4.BASEENTRY
LEFT JOIN OINV T5 ON T4.DOCENTRY = T5.DOCENTRY
LEFT JOIN RIN1 T6 ON T4.dOCENTRY = T6.BASEENTRY
LEFT JOIN ORIN T7 ON T6.DOCENTRY = T7.DOCENTRY

WHERE T1.[BaseRef] =[%0] AND T0.[CANCELED] =’N’ AND T5.[CANCELED] =’N’

—
menampilakn Daftar DO yang sudah di kirim Berdasarkan SO

Actual Production

Select Distinct A.docdate as tanggalInvoice, a.docnum as NoInv,f.docnum as NoSo,d.docdate as TanggalDO,d.DocNum as NoDO
,A.Cardcode as CustomerCode,A.CardName as CustomerName
,B.ItemCode as ItemNo
,B.Dscription as ItemName
,B.WhsCode as Warehouse
,B.Quantity
,B.Price
,B.StockPrice as Cost
,((B.StockPrice / B.Price)*100) as Persentase
,i.DistNumber as BatchNum
,l.DocNum as NoReceipt
,k.Quantity as QtyReceipt
From
OINV a
left join INV1 b on A.Docentry = B.DocEntry
left join DLN1 c on b.baseentry = c.docentry and b.basetype = c.objtype and b.baseline = c.linenum
left join ODLN d on c.docentry = d.docentry
left join RDR1 e on c.baseentry = e.docentry and c.basetype = e.objtype and c.baseline = e.linenum
left join ORDR f on e.docentry = f.docentry
–batch Delivery
left join OITL g on g.ApplyType = ’15’ and g.BaseType = ’17’ and g.docnum = d.docnum
left join ITL1 h on G.LogEntry = h.LogEntry
left join OBTN i on i.sysnumber = h.SysNumber and i.itemcode = c.ItemCode
–batch Production
left join OITL j on j.BaseType = ‘202’ and j.ApplyType = ’59’ and j.ItemCode = c.ItemCode
left join ITL1 k on k.sysnumber = i.SysNumber and k.itemcode = c.itemcode and k.LogEntry = j.LogEntry
left join OITL l on l.LogEntry = k.LogEntry and l.itemcode = c.ItemCode
where a.docdate >= [%0] and a.docdate <= [%1]
and ( (isnull(i.DistNumber,'') ”) and (isnull(k.quantity,0) 0) and (isnull(l.DocNum,”) ”) )

—
Menampilkan informasi lengkap dari Invoice hingga No Pruduction Order Selama Tanggal Tertentu

Actual Delivery Per Plant (OcrCode)

SELECT T0.[docentry] As ‘Num’, T0.[CreateDate], T0.[DocDate], T0.[DocNum] as ‘No. DO’, T1.[BaseDocNum] as ‘No. SO’, T0.[CardCode] As ‘BP Code’, T0.[CardName], T0.[NumAtCard], T1.[WhsCode] as ‘WHSE’, T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.OCRCODE AS ‘PLANT’, T3.docnum as [No. return], t2.quantity as [Qty Return], ISNULL(T1.[Quantity]-T2.[Quantity],T1.[Quantity]) AS [NET QTY]

FROM ODLN T0
left JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN RDN1 T2 ON T0.DOCENTRY=T2.BASEENTRY
LEFT JOIN ORDN T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T0.[DocDate] between [%0] and [%1] and left(t1.whscode,3) = [%2] and T0.[Canceled] = ‘N’

—
menampilkan daftar Surat jalan berdasarkan Gudang (whscode), tanpa data yang di cancel.

Enable Caps Lock & Num Lock Notification Windows 7

Jika anda kesulitan untuk mengetahui apakah num lock atau caps lock hidup atau mati di windows 7, karena tidak ada lampu indicator tertentu, dapat memanfaatkan fungsi notifokasi default dari windows.

Untuk memunculkan notifikasi  saat menekan tombol Caps Lockatau numlock di windows 7

Buka Regedit dan cari Key berikut :

HKEY_LOCAL_MACHINE > SOFTWARE > Widcomm > BTConfig > General > KeyIndication


Ganti value “KeyIndication” dari “0” ke “1”

Disable CD/DVD Auto Eject Windows 7 atau Saat Klik CD/DVDRoom Keluar Otomatis

Salah satu Feature dari windows 7 yang suka bikin kesel! CD/DVD room  keluar otomatis saat Drive CD/DVD di klik (ga sengaja padahal).

Buat disable  feature ini caranya adalah :

1. Open Run (Start + R) atau klik start trus klik Run. ketik GPEDIT.MSC
2. Masuk Ke >User Configuration>Administrative Templates>Windows Components>Windows Explorer
3. Buka Remove CD Burning Feature
4. pilih Enabled
5. Klik Apply

Dan Voila…sekarang udah ga keluar otomatis lagi.. 😀 semoga membatu lol