select Code, LongDesc from GLCOA
SET OPTION FIRE_TRIGGERS = 'on'
update initem set gl_groupid = 'FGOPPDR' where gl_groupid = 'FGOPP'
sript untuk update backdated and posted di config:
select * from PXConfig where config like '%server%'
truncate table MF_RoutingDtl
call UploadBeginningPO('sa') - SELECT * FROM PO_Opening
call UploadBeginningSO() - SELECT * FROM SO_Opening
call SaldoAwalIN() - tidak standard - SELECT * FROM INOpening
call UploadBeginningAP() - SELECT * FROM AP_Opening
call UploadBeginningAR() - SELECT * FROM AR_Opening
call UploadBeginningIN
Script pengecekan roll up:
--Script -> untuk cek itemid mana yang belum punya purchase price
select * from initem where ValueMtd ='AVG' and itemID in (select ItemID from INBOM
) and ItemID not in (select ItemID from InPriceList where PriceID='STDCOST')
--Script-> untuk cek itemid yang mempunyai routing tetapi tidak memiliki bom
select a.* from initem a
join MF_Routing b on a.itemID =b.itemID where a.ItemType = 2 and a.ValueMtd ='STD'
and b.Rouid=1 and a.itemID not in (select ParentID from INBOM where Rouid=1 )
delete from INCountResult where locationid = 'ktr01'
Production Request With Order:
select b.TrNo, c.Orderno, c.ItemID, c.Qty, a.TrNo as trno_spk, a.ItemID as
item_spk, a.Date_approved, a.Date_closed
from mf_orderhdr a inner join mf_prtranshdr b on left(a.Trmanualref2, 11) = b.trno
inner join mf_prtransdtl c on b.siteid = c.siteid and b.sys = c.sys
inner join initem d on a.ItemID = d.itemid
where b.Periodid > 96 and d.GL_GroupID like 'WIP%' and a.Date_closed is null
HDR and DTL:
select a.entityid, a.siteid, a.periodid , a.Trdate, a.trno, a.trmanualref,
b.itemID, b.locationid, b.qty, b.unitid, b.unitprice from pstranshdr a join
pstransdtl b
on a.sys = b.sys and a.siteid = b.siteid where a.sys = 3100
Untuk mengetahui item yang masternya belum ada:
select * from po_opening where itemid not in (select itemid from initem)
backup database directory '/home/admin1/Backup' DBFILE ONLY;
backup database directory 'D:\Andy Folder\Project On Going\Yoso\ActivityID\
Database' DBFILE ONLY;
Link Update Versi: http://localhost:8080/faces/CheckForUpdates.jsp?load=1
Link Update Product: http://192.168.1.237:8083/product
/opt/apache-tomcat-7.0.105/webapps/orlansoft-report/reportsource
call SaldoAwalIN() -> prosedure opening balance
Update inglgroupdtl:
select * from systriggers
where trigdefn like '%Cannot modify GL Group Account Details because GL Group
already use on INItem%' -> BeforeUpd_INGLGroupDtl
select * from systriggers where trigdefn like '%cannot change conntype%' ->
TUB_CAContraAccList_Log
select * from systrigger where trigger_defn like '%Cannot choose unvalued standard
cost item%'
Untuk cek intransdtl dengan summary balance akun penjualan dan HPP:
select a.trno, b.itemid, b.qt, b.taxable, b.cogsunit from intranshdr a join
intransdtl b
on a.sys = b.sys and a.siteid = b.siteid
where b.PeriodID = '5'
and b.transcode = '3'
and a.void_ = '0'
Untuk mengetahui jumlah aktivitas pos
select PmtTypeID,sum(PmtAmount) from POS_PmtDetails where PmtSys in (select sys
from INTransHdr where TrManualRef like '%03SW14%836%') and PmtSiteID='03A'
group by PmtTypeID;
Script untuk cek catrno dari PI/SI trno:
select a.siteid as siteIDCA, a.periodid , d.Trno as TrnoPI, a.trno as CATrno,
d.NetAmt as NetAmtPI
from catrhdr a join catrdtl b on a.catrsys = b.catrsys and a.siteid = b.siteid
join rppmtallocation c on a.catrsys = c.catrsys and c.siteid = a.siteid and c.trno
= b.trno
join rptranshdr d on d.sys = c.schsys and d.siteid = c.schsiteid where d. trno in
()
Script update Next Lvel:
update ARCustomer a set a.taxid = b.taxid from ARCustomer a inner join
ARCustomer_Temp b on a.CustID = b.CustID
select a.* from RPTransHdr a inner join RPPmtSch b on a.sys = b.sys and a.siteid =
b.siteid inner join RPPmtAllocation c on b.sys = c.SchSys and b.siteid =
c.SchSiteID
where a.trtype = 'BARO1' and a.trmanualref2 = 'Upload Transaction' and a.siteid =
'BB' --teralokasi
select * from RPTransHdr where trno not in(select a.trno from RPTransHdr a inner
join RPPmtSch b on a.sys = b.sys and a.siteid = b.siteid inner join RPPmtAllocation
c on b.sys = c.SchSys and b.siteid = c.SchSiteID
where a.trtype = 'BARO1' and a.trmanualref2 = 'Upload Transaction' and a.siteid =
'BB') and siteid = 'BB' and trmanualref2 = 'Upload Transaction' and trtype =
'BARO1' --tidak teralokasi
Untuk cek prod order dgn item yang sama dalam 1 production req:
select distinct count(a.itemid), a.itemid, b.description, sum(a.qty_ordered) from
MF_OrderHdr a join initem b on a.itemid = b.itemid where a.trmanualref2 =
'PRQ012306000071' group by a.itemid, b.description
Roll up nyantol:
call DBA.SetConfig('IN_RecalculateInProcess','G','G','0');
call DBA.SetConfig('IN_RecalculateInProcessIP','G','G','0');
call DBA.SetConfig('IN_RecalculateInProcessUsr','G','G','0');
call DBA.SetConfig('IN_RollupResult','G','G',''); --WEB Rollup Process
call DBA.SetConfig('IN_RollupPeriod','G','G','0'); --WEB Rollup PeriodID
-- commit work —#9437;
-- reset variable temp. untuk proses roll up di WEB
call DBA.SetConfig('IN_RollupFItem','G','G','');
call DBA.SetConfig('IN_RollupTItem','G','G','');
call DBA.SetConfig('IN_RollupFGroup','G','G','');
call DBA.SetConfig('IN_RollupTGroup','G','G','');
call DBA.SetConfig('IN_RollupFCat','G','G','');
call DBA.SetConfig('IN_RollupTCat','G','G','');
call DBA.SetConfig('IN_RollupFBrand','G','G','');
call DBA.SetConfig('IN_RollupTBrand','G','G','');
call DBA.SetConfig('IN_RollupFModel','G','G','');
call DBA.SetConfig('IN_RollupTModel','G','G','');
call DBA.SetConfig('IN_RollupFClass','G','G','');
call DBA.SetConfig('IN_RollupTClass','G','G','');
call DBA.SetConfig('IN_RollupFGL','G','G','');
call DBA.SetConfig('IN_RollupTGL','G','G','');
call DBA.SetConfig('IN_RollupChkExp','G','G','');
call DBA.SetConfig('IN_RollupChkLvl','G','G','');
call DBA.SetConfig('IN_RollupType','G','G','');
call DBA.SetConfig('IN_RollupChkRecal','G','G','');
call DBA.SetConfig('IN_RollupProcessStd','G','G','');
Script untuk melihat Shipment Outstanding di Sales Shipping Cost:
select a.siteid, a.entityid, a.periodid, a.trno, a.trmanualref as manualrefPR, b.qt
as qtyPR, b.itemid, b.shipmenttrno, d.qt as QtyShip from prtranshdr a
join prtransdtl b on a.sys = b.sys and a.siteid = b.siteid
join intranshdr c on b.shipmenttrno = c.trno
join intransdtl d on c.sys = d.sys and c.siteid = b.siteid and b.itemid = d.itemid
join initem e on b.itemid = e.itemid and d.itemid = e.itemid
join inglgroup f on e.gl_groupid = f.ID
where f.x = '5' and qtyPR < QtyShip
Script Jurnal MF:
select b.code, b.dc, c.longdesc, b.amount, b.amount1, a.journalno, b.description
from gltrhdr a join gltrdtl b on a.gltrsys = b.gltrsys and a.siteid = b.siteid
join glcoa c on b.coaid = c.coaid
--join intranshdr d on d.trno = a.journalno
join mf_orderhdr e on a.journalno = e.trno
where e.trno = 'PRO01230800020'
where d.transcode = 5 and mf = 1 and orderno = 'PRO01230800020' -- scrip Jurnal
Script Cek Close Production Order tanpa IM
select a.trno from mf_orderhdr a join mf_orderdtl b on a.sys = b.sys and a.siteid =
b.siteid
join mf_orderdtlmat c on b.sys2 = c.sys2 and b.siteid = b.SiteID
where a.status = 6 and a.qty_completed <> 0 and c.qty_act = 0-- cek prodcution
order close tanpa IM
- Script untuk cek itemid yang di produksi
select distinct itemid from mf_orderhdr where planno like '%-00' and itemid like
'4%' and periodid = 16 and trno like 'PRO01%'
select sum(beginvalue1), sum(invalue1), sum(outvalue1) from INItemBalance where
itemid in (select itemid from initem where gl_groupid = 'GLSPP') and periodid = 16
Cannot change Use Lot because ItemID already exists on INItemBalance
SELECT * FROM systrigger where trigger_defn like '%Cannot change Use Lot because
ItemID already exists on INItemBalance%';
SELECT * FROM sysprocedure where source like '%Cannot change Use Lot because ItemID
already exists on INItemBalance%';
select * from sys.SYSTRIGGERS where trigdefn like '%Cannot change Use Lot because
ItemID already exists on INItemBalance%';
select * from sys.SYSEVENT where source like '%Cannot change Use Lot because ItemID
already exists on INItemBalance%';
select * from sa_server_messages() order by msg_id desc
