Menampilkan Item Category di Preview Delivery Order, Received Item, Purchase Invoice & Purchase Order
berikut ini dibahas bagaimana cara untuk memunculkan Preview Item Category Name di template DELIVERY ORDER, RECEIVED ITEM & PURCHASE INVOICE
– Untuk memunculkan Item Category di DELIVERY ORDER:
1. Masuk ke ‘Designer as FRF’ lalu pada IBX paling kanan rubahlah SQL-nya, sbb :
- Sebelum baris ‘From TEMPLATE_ARINVDET(:ARINVOICEID, 0) a’ tambahkanlah tulisan :
-
- e.name CategoryName
- Setelah baris ‘From TEMPLATE_ARINVDET(:ARINVOICEID, 0) a’ tambahkanlah tulisan :
-
- left outer join item d on d.itemNo=a.ITEMNO
- left outer join itemcategory e on d.categoryid=e.categoryid
- sehingga keseluruhan querynya menjadi seperti dibawah ini :
- Select a.ARINVOICEID, a.SEQ, a.USEDINSIID,
a.USEDINSI, a.INVOICENO, a.PONOFROMDO,
a.ITEMNO, a.ITEMOVDESC, a.QUANTITY,
a.ITEMDISCPC, a.BRUTOUNITPRICE, a.UNITPRICE,
a.ITEMHISTID, a.ITEMCOST, a.ITEMCOSTBASE,
a.ITEMUNIT, a.UNITRATIO, a.ITEMTYPE,
a.UNIT1, a.UNIT2, a.UNIT3, a.RATIO2, a.RATIO3, a.NOTES,
a.INVENTORYGLACCNT, a.COGSGLACCNT, a.SALESGLACCNT,
a.SALESDISCOUNTACCNT, a.GOODSTRANSITACCNT,
a.TAXCODES, a.TAXABLEAMOUNT1, a.TAXABLEAMOUNT2,
a.ITEMRESERVED1, a.ITEMRESERVED2, a.ITEMRESERVED3,
a.ITEMRESERVED4, a.ITEMRESERVED5, a.ITEMRESERVED6,
a.ITEMRESERVED7, a.ITEMRESERVED8, a.ITEMRESERVED9,
a.ITEMRESERVED10, a.SOID, a.SOSEQ, a.SONO, a.PONOFROMSO,
a.DEPTID, a.DEPTNO, a.DEPTNAME,
a.PROJECTID, a.PROJECTNO, a.PROJECTNAME,
a.WAREHOUSEID, a.NAME WPIName, a.QuoteID, a.QuoteNo, a.SNLine, a.SNExpDate,
a.QTYCONTROL, e.name CategoryName
From TEMPLATE_ARINVDET(:ARINVOICEID, 0) a
left outer join item d on d.itemNo=a.ITEMNO
left outer join itemcategory e on d.categoryid=e.categoryid
Where 1=1
and (a.GroupSeq is null or (a.GroupSeq is not null
and (Exists (select s.Seq from ARINVDET s
inner join Item it on it.itemNo=s.ItemNo and it.ItemType=3
where it.MinimumQty=1 and s.ARInvoiceID=a.ARInvoiceID and s.Seq=a.GroupSeq))))
Order by a.Seq
2. Pada “Data Dictionary” buatlah ‘New Variable’ pada folder ‘Invoice Item’ dengan nama ‘CategoryName’
- yang valuenya kita dapat dengan memilih tulisan ‘CATEGORYNAME’ dari ‘DialogForm.qrySIARInvDet’.
3. Tinggal isikan memo yang kita mau dengan variable “CategoryName”.
UNTUK TEMPLATE DELIVERY ORDER YANG SUDAH DISETTING PREVIEW ITEM CATEGORY DAPAT DOWNLOAD DI SINI
– Untuk memunculkan Item Category di RECEIVED ITEM:
1. Caranya sama seperti di DO, dimana untuk keseluruhan SQL nya adalah seperti ini :
Select a.APINVOICEID, a.SEQ, a.BILLID,
a.INVOICENO PINO,
a.ITEMNO, a.ITEMOVDESC, a.QUANTITY,
a.ITEMDISCPC, a.BRUTOUNITPRICE, a.UNITPRICE,
a.ITEMHISTID, a.ITEMCOST, a.ITEMCOSTBASE,
a.ITEMUNIT, a.UNITRATIO,
a.ITEMTYPE, a.UNIT1, a.UNIT2, a.UNIT3, a.RATIO2, a.RATIO3, a.NOTES,
a.INVENTORYGLACCNT, a.COGSGLACCNT, a.SALESGLACCNT, a.UNBILLEDACCOUNT,
a.TAXCODES, a.TAXABLEAMOUNT1, a.TAXABLEAMOUNT2,
a.ITEMRESERVED1, a.ITEMRESERVED2, a.ITEMRESERVED3, a.ITEMRESERVED4,
a.ITEMRESERVED5, a.ITEMRESERVED6, a.ITEMRESERVED7, a.ITEMRESERVED8,
a.ITEMRESERVED9, a.ITEMRESERVED10,
a.POID, a.POSEQ, a.PONO,
a.DEPTID, a.DEPTNO, a.DEPTNAME,
a.PROJECTID, a.PROJECTNO, a.PROJECTNAME,
a.WAREHOUSEID, a.NAME WPIName, a.ReqNo RequestNo, a.SNLine, a.SNExpDate,
a.QTYCONTROL,
e.name CategoryName
From TEMPLATE_APITMDET(:APInvoiceID) a
left outer join item d on d.itemNo=a.ITEMNO
left outer join itemcategory e on d.categoryid=e.categoryid
Where 1=1
Order by A.APInvoiceID, a.SEQ
UNTUK TEMPLATE RECEIVED ITEM YANG SUDAH DISETTING PREVIEW ITEM CATEGORY DAPAT DOWNLOAD DI SINI Klik Disini KLIK DISINI
– Untuk memunculkan Item Category di PURCHASE INVOICE:
1. Caranya juga sama seperti di DO, dimana untuk keseluruhan SQL nya adalah seperti ini :
Select a.APINVOICEID, a.SEQ, a.BILLID,
a.INVOICENO PINO,
a.ITEMNO, a.ITEMOVDESC, a.QUANTITY,
a.ITEMDISCPC, a.BRUTOUNITPRICE, a.UNITPRICE,
a.ITEMHISTID, a.ITEMCOST, a.ITEMCOSTBASE,
a.ITEMUNIT, a.UNITRATIO,
a.ITEMTYPE, a.UNIT1, a.UNIT2, a.UNIT3, a.RATIO2, a.RATIO3, a.NOTES,
a.INVENTORYGLACCNT, a.COGSGLACCNT, a.SALESGLACCNT, a.UNBILLEDACCOUNT,
a.TAXCODES, a.TAXABLEAMOUNT1, a.TAXABLEAMOUNT2,
a.ITEMRESERVED1, a.ITEMRESERVED2, a.ITEMRESERVED3, a.ITEMRESERVED4,
a.ITEMRESERVED5, a.ITEMRESERVED6, a.ITEMRESERVED7, a.ITEMRESERVED8,
a.ITEMRESERVED9, a.ITEMRESERVED10,
a.POID, a.POSEQ, a.PONO,
a.DEPTID, a.DEPTNO, a.DEPTNAME,
a.PROJECTID, a.PROJECTNO, a.PROJECTNAME,
a.WAREHOUSEID, a.NAME WPIName, a.ReqNo RequestNo, a.SNLine, a.SNExpDate,
a.QTYCONTROL,
e.name CategoryName
From TEMPLATE_APITMDET(:APInvoiceID) a
left outer join item d on d.itemNo=a.ITEMNO
left outer join itemcategory e on d.categoryid=e.categoryid
Where 1=1
Order by A.APInvoiceID, a.SEQ
UNTUK TEMPLATE PURCHASE INVOICE YANG SUDAH DISETTING PREVIEW ITEM CATEGORY DAPAT DOWNLOAD DI SINI
Untuk Memunculkan Item Category di PURCHASE ORDER
. Masuk ke ‘Designer as FRF’ lalu pada IBX paling kanan rubahlah SQL-nya, sbb : – Sebelum baris ‘from PODet a’ tambahkanlah tulisan : , e.name CategoryName– Sebelum baris ‘WHERE a.POID=:POID’ tambahkanlah baris :left outer join itemcategory e on I.categoryid=e.categoryid– Sehingga keseluruhan querynya menjadi seperti dibawah ini : Select a.ItemNo, a.ItemOvDesc, a.Quantity, a.UnitPrice, a.ItemUnit, a.ItemDiscPC, a.BrutoUnitPrice, a.TaxCodes, a.Closed, a.QtyRecv, a.ItemReserved1, a.ItemReserved2, a.ItemReserved3, a.ItemReserved4, a.ItemReserved5, a.ItemReserved6, a.ItemReserved7, a.ItemReserved8, a.ItemReserved9, a.ItemReserved10, D.DEPTNO, R.PROJECTNO, D.DeptName, R.ProjectName, I.Unit1, I.Unit2, I.Unit3, I.Ratio2, I.Ratio3, I.Notes, I.ItemType, P.ReqNo RequestNo, e.name CategoryName from PODet a INNER Join Item I On a.ItemNo=I.ItemNo left outer join Department D on D.DeptID=a.DeptID left outer join Project R on R.ProjectID=a.ProjectID left outer join Requisition P on P.ReqID=a.ReqID left outer join itemcategory e on I.categoryid=e.categoryid WHERE a.POID=:POID ORDER BY a.Seq2. Tinggal isikan memo yang kita mau dengan “[DialogForm.qryPODet.”CATEGORYNAME”]”.
Sorry, the comment form is closed at this time.