|
|
Purchase Request
- Run Auto TransactionNo from RunningNumber
- Default Transaction Daty By System Date
- VIEW & GET: Default Vat Percent from TB TAXMaster
SELECT
TAXRate From TB TAXMaster WHERE TB TAXMaster.Local =
Populate.Corporation.Address and TAXMaster.TAXType = “VAT”
- VIEW & GET: Supplier TB Populate -> Populate -> PopulateContract
SELECT Populate.Prefix + Populate.FirstName + Populate.MiddleName +
Populate.LastName + Populate.Suffix, PopulateContract.ContractName, Corporation.Address,
PopulateContract.PaymentTerm, PopulateContract.CreditTerm FROM Populate, Corporation,
PopulateContract
- VIEW & GET: Inventory from TB Library -> LibrarySpec or Inventory ->
Inventory
SELECT Unit FROM LibrarySpec
- Balance: Unit Price
GO Product Amount
- Balance: Detail of product amount
Detail of Amount must <= Quantity *
UnitPrice
( If Detail of Amount < Quantity * Price then Differencial Value is a discount )
- Balance: Discount Percent(¡ÒäéÒ)
GO Product Amount
- Balance: Vat Percent
GO Product Amount
- VIEW & GET: Freight Type and Insurance from TB FreightInsurance
- Balance: Calculate Product Amount
- TotalAmount = Sum ( Detail of Amount )
- Discount Amount = ( Total Amount * Discount Percent ) / 100
- Vat Amount = (Total Amount - Discount Amount ) * Vat Percent / 100
- Net Total Amount = Total Amount - Discount Amount + Vat Amount
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, QuotationHdr.ContractName,
QuotationHdr.PaymentTerm, QuotationHdr.Credit, QuotationHdr.Approval,
QuotationHdr.ShipVia, QuotationHdr.ShipDate, QuotationHdr.Promotion,
QuotationHdr.TotalAmount, QuotationHdr.DiscountPercent, QuotationHdr.DiscountAmount,
QuotationHdr.VatPercent, QuotationHdr.VatAmount, QuotationHdr.NetTotalAmount,
QuotationHdr.FreightType, QuotationHdr.FreightCorporation, QuotationHdr.FreightAmount,
QuotationHdr.InsuranceCorporation, QuotationHdr.InsuranceAmount,
QuotationHdr.CurrencyCode, FROM TransactionHdr, BusinessLibraryTransactionDtl,
BusinessInventoryTransactionDtl, QuotationHdr, QuotationDtl
Detail of Project
Purchase Order
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Refer Purchase Request from TB QuotationHdr -> QuotationDetail
SELECT QuotationHdr.Populate, Populate.Prefix +
Populate.FirstName + Populate.MiddleName + Populate.LastName + Populate.Suffix,
PopulateContract.ContractName, PopulateContract.Address, QuotationHdr.PaymentTerm,
PopulateContract.CreditTerm, QuotationHdr.Saleman, QuotationHdr.ShipVia,
QuotationHdr.ShipDate, QuotationHdr.Currency, QuotationHdr.FreightType,
QuotationHdr.FreightAmount, QuotationHdr.InsuranceAmount, QuotationHdr.TotalAmount,
QuotationHdr.DiscountPercent, QuotationHdr.DiscountAmount, QuotationHdr.VATPercent,
QuotationHdr.VATAmount, QuotationHdr.NetTotalAmount , QuotationDtl.Library ->
Inventory, Inventory.Name, QuotationDtl.Quantity, QuotationDtl.Unit,
QuotationDtl.UnitPrice, QuotationDtl.Amount FROM Populate, PopulateContract, QuotationHdr,
QuotationDtl
- VIEW & GET: Customer from TB Populate -> Populate -> PopulateContract
SELECT Populate.Prefix + Populate.FirstName + Populate.MiddleName +
Populate.LastName + Populate.Suffix, PopulateContract.ContractName, Corporation.Address,
PopulateContract.PaymentTerm, PopulateContract.CreditTerm FROM Populate, PopulateContract,
Corporation
- VIEW & GET: Saleman from TB Populate -> Populate -> Employee
SELECT Prefix + FirstName + MiddleName + LastName + Suffix FROM
Populate
Default Due Date when Key In Credit Term and calculate by Purchase Order Date + Credit
Term
- VIEW & GET: Product
SELECT Name FROM Inventory
- Balance:Quantity and verify Supplier Stock on hand
SELECT
sum(Quantity) From InventoryTransaction WHERE Original =
Populate and Code = Product and TransactionType = “Balance Forward” and (PriceDate
>= 1'st of Month of Purchase Order Date and PriceDate <= Purchase Order date) + SELECT sum(Quantity) From InventoryTransaction WHERE
Original = Original.Department and Code = Product and (TransactionType = “Goods
Receiving” or TransactionType = “Increase Adjust”) and (PriceDate >= 1'st of
Month of Purchase Order Date and PriceDate <= Purchase Order date ) - SELECT sum(Quantity) From InventoryTransaction WHERE
Original = Original.Department and Code = Product and (TransactionType = “Issuing” or
TransactionType = “Decrease Adjust”) and (PriceDate >= 1'st of Month of
Purchase Order Date and PriceDate <= Purchase Order date ) - SELECT
sum(RemainQuantity) From InventoryTransaction WHERE Original =
Original.Department and Code = Product and TransactionType = “Requisition”
If Sum InventoryTransaction >= Quantity then Pass
If Sum InventoryTransaction < Quantity
For Trading will display Ship Date = Purchase Order Date +
Inventory.LeadTime
For Factory will display Ship Date = Production Day for
on-hand Order and Production Day for Inprogress Order + Delivery Day
GO Product Amount
- Balance: Amount
Detail of product amount must <= Quantity * UnitPrice
( If Detail of Amount < Quantity * Price then Differencial Value is a discount )
- VIEW: Freight Type and Insurance from TB surance
- Balance: Discount Percent, GO Product Amount
- Balance: Vat Percent, GO Product Amount
- Balance: Calculate Product Amount , Detail of Amount = Quantity * Price
- TotalAmount = Sum ( Detail of Amount )
- Discount Amount = ( Total Amount * Discount Percent ) / 100
- Vat Amount = (Total Amount - Discount Amount ) * Vat Percent / 100
- Net Total Amount = Total Amount - Discount Amount + Vat Amount
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, OrderHdr.PopulateAddress,
OrderHdr.ReferQuotation, OrderHdr.PaymentTerm, OrderHdr.Credit, OrderHdr.Saleman,
OrderHdr.Approval, OrderHdr.ShipVia, OrderHdr.ShipDate, OrderHdr.ShipAddress,
OrderHdr.Promotion, OrderHdr.DueDate, OrderHdr.LCBank, OrderHdr.LCNo, OrderHdr.LGNo,
OrderHdr.LGBank, OrderHdr.TotalAmount, OrderHdr.DiscountPercent, OrderHdr.DiscountAmount,
OrderHdr.VatPercent, OrderHdr.VatAmount, OrderHdr.NetTotalAmount, OrderHdr.FreightType,
OrderHdr.FreightCorporation, OrderHdr.FreightAmount, OrderHdr.InsuranceCorporation,
OrderHdr.InsuranceAmount, OrderHdr.CurrencyCode FROM TransactionHdr,
BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl, OrderHdr
Goods Receiving
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Receiving Person from TB Populate -> Populate -> Employee
SELECT Prefix + FirstName + MiddleName + LastName + Suffix FROM
Populate
- VIEW & GET: Purchase Order No TB OrderHdr -> OrderDetail
SELECT OrderHdr.Populate, Populate.Prefix + Populate.FirstName +
Populate.MiddleName + Populate.LastName + Populte.Suffix, OrderDtl.Library -> Library,
Library.Name, OrderDtl.SerialNo -> Inventory, Inventory.Nme, OrderDtl.Quantity,
OrderDtl.Unit FROM OrderHdr, OrderDtl
- VIEW & GET: Product TB Library or Inventory
SELECT
Name, Unit FROM LibrarySpec, Inventory
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, InventoryTransaction.ReferTransactionNo,
InventoryTransaction.ReferDocumentNo, InventoryTransaction.FromOriginal,
InventoryTransaction.ToOriginal, InventoryTransaction.FromDepartment,
InventoryTransaction.ToDepartment, InventoryTransaction.FromPerson,
InventoryTransaction.ToPerson, InventoryTransaction.Producer, InventoryTransaction.Price,
InventoryTransaction.Quantity, InventoryTransaction.Unit,
InventoryTransaction.InvoiceDate, InventoryTransaction.InvoiceDateNo,
InventoryTransaction.LotDate, InventoryTransaction.LotDateNo, InventoryTransaction.Cost,
InventoryTransaction.RemainQuantityByInvoice, InventoryTransaction.RemainQuantityByLot,
InventoryTransaction.RemainAmountByLot, InventoryTransaction.IssueDate,
InventoryTransaction.ExpiryDate, InventoryTransaction.DepreciationValue FROM
TransactionHdr, BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl,
InventoryTransaction
Purchase Invoice
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Default Currency from Original.License -> Populate
GET Populate.People.Address.Country
SELECT LibrarySpec.Amount FROM LibrarySpec WHERE
Date = get last date and Code = Populate.People.Address
- VIEW & GET: Ship Via from TB Library and calculate Freight Amount from TB
FreightInsurance
- VIEW & GET: Vehicle Name, Loading Port and Destination Port
- VIEW & GET: Receiving No from InventoryTransaction
SELECT
InventoryTransaction.Populate, Populate.Prefix + Populate.FirstName + Populate.MiddleName
+ Populate.LastName + Populate.Suffix, PopulateContract.ContractName, Corporation.Address,
InventoryTransaction.FromPerson, InventoryTransaction.Library -> Library, Library.Name,
InventoryTransaction.SerialNo -> Inventory, Inventory.Name,
InventoryTransaction.Quantity, InventoryTransaction.Unit, OrderHdr.PaymentTerm,
PopulateContract.CreditTerm, OrderHdr.ShipVia, OrderHdr.ShipDate, OrderHdr.FreightType,
OrderHdr.FreightAmount, OrderHdr.InsuranceAmount, OrderDtl.UnitPrice, OrderDtl.Amount,
OrderHdr.Currency FROM InventoryTransaction, Populate, Library, Inventory, OrderHdr,
OrderDtl, PopulateContract
- VIEW & GET: Suppplier from TB Populate -> Populate -> PopulateContract
SELECT Populate.Prefix + Populate.FirstName + Populate.MiddleName +
Populate.LastName + Populate.Suffix, PopulateContract.ContractName, Corporation.Address,
PopulateContract.PaymentTerm, PopulateContract.CreditTerm FROM PopulateContract, Populate,
Corporation
- VIEW & GET: Purchase Order No Reference -> TB OrderHdr
SELECT OrderHdr.TransactionDate, OrderHdr.Populate, OrderHdr.ShipDate,
OrderHdr.ShipAddress, OrderHdr.ShipVia, OrderHdr.Saleman, OrderHdr.PaymentTerm FROM
OrderHdr
- VIEW & GET: Product TB Inventory -> Inventory
SELECT
Inventory.Name, Inventory.Unit FROM Inventory
GET Field Price FROM PriceList WHERE PriceList.Library =
Product and PriceList.ApprovalLevel = Employee.ApprovalLevel and PriceList.PerUnit =
Inventory.Unit
- Balance: Quantity
Calculate Product Amount
- Balance: Amount in detail
product amount in detail must <= Quantity *
UnitPrice
( If amount is less than quantity * price, difference value is a discount amount. )
- Import / Export Type ->Reference -> Library
- VIEW & GET: Freight Type and Insurance from TB surance
Sum of Quantity * (SELECT Price From FreightInsurance WHERE
Original = Original.Department and From = Loading Port and To = Destination Port and
ViaType = Ship Via and Library = Product Group
- Balance: Discount Percent
Calculate Product Amount
- Balance: Vat Percent
Calculate Product Amount
- Balance: Calculate Product Amount
- Detail of Amount = Quantity * Price
- TotalAmount = Sum ( Detail of Amount )
- Discount Amount = ( Total Amount * Discount Percent ) / 100
- Vat Amount = (Total Amount - Discount Amount ) * Vat Percent / 100
- Net Total Amount = Total Amount - Discount Amount + Vat Amount
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, InvoiceHdr.ReferOrder,
InvoiceHdr.ReferStatement, InvoiceHdr.ReferIssueNo, InvoiceHdr.PaymentTerm,
InvoiceHdr.CreditTerm, InvoiceHdr.Saleman, InvoiceHdr.Approval, InvoiceHdr.ShipVia,
InvoiceHdr.ShipDate, InvoiceHdr.ShipAddress, InvoiceHdr.DueDate, InvoiceHdr.VehicleName,
InvoiceHdr.LoadingPort, InvoiceHdr.DestinationPort, InvoiceHdr.CustomNo,
InvoiceHdr.CustomDate, InvoiceHdr.LcNo, InvoiceHdr.LcBank, InvoiceHdr.LGNo,
InvoiceHdr.LGBank, InvoiceHdr.LGPeople, InvoiceHdr.TotalAmount,
InvoiceHdr.DiscountPercent, InvoiceHdr.DiscountAmount, InvoiceHdr.VATPercent,
InvoiceHdr.VATAmount, InvoiceHdr.NetTotalAmount, InvoiceHdr.RemainAmount,
InvoiceHdr.FreightType, InvoiceHdr.FreightCorporation, InvoiceHdr.FreightAmount,
InvoiceHdr.InsuranceCorporation, InvoiceHdr.InsuranceAmount, InvoiceHdr.CurrencyCode,
InvoiceHdr.RangeOfLoan, InvoiceHdr.InterestLoan, InvoiceDtl.MarksOfExport,
InvoiceDtl.NoForExport, InvoiceDtl.ReferOrder, InvoiceDtl.WeightQuantity,
InvoiceDtl.TAXRate, InvoiceDtl.TAXAmount, InvoiceDtl.ExpiryDate FROM TransactionHdr,
BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl, InvoiceHdr, InvoiceDtl
INSERT
INTO GL (from TB GlPattern)
VALUES Original, TransactionType, JournalNo, Library, TransactionNo, TransactionDate,
Department, Project, GLType, Amount
SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, RunningNumber.Running, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project
Loop TransactionType = Purchase Invoice
SELECT GlPattern.AccountCode, GlPattern.GLType, Amount = SELECT Sum(InvoiceHdr.GlPattern.FieldName) From InvoiceHdr
From InvoiceHdr, GlPattern WHERE GlPattern.TransactionType =
Purchase Invoice
UPDATE PopulateContract (RemainCredit)
SET RemainCredit - (SELECT NetTotalAmount FROM InvoiceHdr WHERE Original = PopulateContract.Original and Department =
PopulateContract.Department and Customer = PopulateContract.ContractOriginal)
WHERE Original = PopulateContract.Original and Department =
PopulateContract.Department and Populate = PopulateContract.ContractOriginal
Billing
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Supplier from TB Populate -> Populate -> PopulateContract
SELECT Populate.Prefix + Populate.FirstName + Populate.MiddleName +
Populate.LastName + Populate.Suffix, PopulateContract.ContractName, Corporation.Address
FROM Populate, PopulateContract
- VIEW & GET: Invoice No -> Reference -> InvoiceHdr
SELECT
InvoiceHdr.Amount FROM InvoiceHdr WHERE InvoiceHdr.InvoiceNo =
àÅ¢·Õè Invoice and TransactionHdr.Populate = InvoiceHdr.Populate
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, BillingDetail.RecvChqDate,
BillingDetail.ReferInvoiceNo, BillingDetail.Amount FROM TransactionHdr,
BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl, BillingDetail
Payment
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Billing No -> Reference -> TB BillingDetail
SELECT BillingDetail.Populate, BillingDetail.ReferInvoiceNo,
BillingDetail.Amount FROM BillingDetail
- Book Account Statement, Payment Type (such as cash, cheque, crredit care, deposit, on
account amount), Bank, Branch of Bank -> Reference -> Library
- VIEW & GET: Invoice No
SELECT InvoiceHdr.Amount
FROM InvoiceHdr WHERE InvoiceHdr.InvoiceNo = àÅ¢·Õè
Invoice
- Balance: Payment Amount
- Total Amount = Detail of Payment Amount
- Vat Amount = (Total Amount * Vat percent ) / 100
- Net Total Amount = Total Amount + Vat Amount
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, ReceiveHdr.ReferBillingDetail,
ReceiveHdr.AccountBook, ReceiveHdr.FineAmount, ReceiveHdr.InterestPercent,
ReceiveHdr.InterestAmount, ReceiveHdr.TotalAmount, ReceiveHdr.DiscountPercent,
ReceiveHdr.DiscountAmount, ReceiveHdr.VatPercent, ReceiveHdr.VatAmount,
ReceiveHdr.NetTotalAmount, ReceiveDtl.ReferInvoiceNo, ReceiveDtl.PaymentType,
ReceiveDtl.DocumentType, ReceiveDtl.DocumentNo, ReceiveDtl.DocumentDate,
ReceiveDtl.DocumentBank, ReceiveDtl.DocumentBankBranch, ReceiveDtl.ApproveCode,
ReceiveDtl.Amount, ReceiveDtl.Currency FROM TransactionHdr, BusinessLibraryTransactionDtl,
BusinessInventoryTransactionDtl, ReceiveHdr, ReceiveDtl
INSERT INTO DocumentBank (Original,
Populate, DocumentType, DocumentNo, DocumentDate, DocumentBank, DocumentBankBranch,
DocumentAmount, DocumentStatus, CurrencyCode)
VALUES
SELECT Original, Supplier, Bank Document Type such as cheque
credit care L/C, Bank of Document, Branch Bank of Document, (SELECT
Sum(Amount) From ReceiveDtl WHERE PaymentType = Bank Document
Type, Group By DocumentNo), O-Onhand, Currency FROM ReceiveDtl
WHERE Payment Type = Bank Document Type and Original =
Original and Department = Department
INSERT INTO DocumentBankTrans
VALUES (Original, TransactionType, TransactionNo, DocumentType, DocumentNo, Populate,
ReferDocumentNo, ReferDocumentDate, Amount)
SELECT Original, “Pay Cheque", Transaction No,
Document Type, Document No, Supplier, Invoice No, Invoice Date
(SELECT Sum(Amount) From ReceiveDtl WHERE
PaymentType =Bank Document Type Group By DocumentNo, ReferInvoiceNo) FROM ReceiveDtl
WHERE Payment Type = Bank Document Type and Original =
Original and Department = Department and InvoiceNo = Purchase
INSERT INTO GL (from TB GlPattern)
VALUES Original, TransactionType, JournalNo, Library, TransactionNo, TransactionDate,
Department, Project, GLType, Amount
SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, RunningNumber.Running, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project
Loop TransactionType = Payment
SELECT GlPattern.AccountCode, GlPattern.GLType, Amount = SELECT Sum(GlPattern.FieldName) From GlPattern.FieldName WHERE GlPttern.FieldCondition = Value From ReceiveHdr, ReceiveDtl,
GlPattern
WHERE GlPattern.TransactionType = Payment
UPDATE PopulateContract (RemainCredit)
SET RemainCredit + (SELECT Amount FROM ReceiveDtl WHERE Original = PopulateContract.Original and Department =
PopulateContract.Department and Populate = PopulateContract.ContractOriginal)
WHERE Original = PopulateContract.Original and Department =
PopulateContract.Department and Populate = PopulateContract.ContractOriginal
Purchase Debit Note / Credit Note
- Run Auto TransactionNo ¨Ò¡ TB RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Default Currency from TB Original.License -> Populate ->
LibrarySpec
SELECT LibrarySpec.Amount FROM LibrarySpec WHERE Date = get last date and Code = Populate.People.Address
- VIEW & GET: Default Vat Percent from TB TAXMaster
SELECT
TAXRate From TB TAXMaster WHERE TB TAXMaster.Local =
People.Address and TAXMaster.TAXType = “VAT”
- VIEW & GET: Supplier from TB Populate -> Populate -> PopulateContract
SELECT Populate.Prefix + Populate.FirstName + Populate.MiddleName +
Populate.LastName + Populate.Suffix, Corporation.Address FROM Populate, Corporation
- VIEW & GET: Reason of Debit Note / Credit Note -> Reference -> Library
- Balance: Invoice No from TB InvoiceDetail
SELECT
Library, Unit, Quantity, UnitPrice, Amount From InvoiceDetail
- VIEW & GET & Balance: Product from TB Inventory, Library
GET Field
Product = Inventory.Name, Field unit= Inventory.Unit
SELECT Unit, Quantity, UnitPrice, Amount From InvoiceDetail
- Calculate Product Amount
- Balance: Quantity must less than or equal invoice quantity
- Balance: Vat Percent
- Calculate Net Total Amount
- Balance: Calculate Net Total Amount
- Total Amount = SELECT Sum(Amount) From InvoiceDtl WHERE TransactionNo = Invoice
- Total Amount = Sum Amount in detail
- Difference = Amount of Old Purchase Invoice - New Amount
- Vat Amount = ( Difference * Vat Percent ) / 100
- Net Total Amount = Total Amount + Vat Amount
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, DnCnHdr.Reason, DnCnHdr.TotalAmount,
DnCnHdr.DiscountPercent, DnCnHdr.DiscountAmount, DnCnHdr.VATPercent, DnCnHdr.VATAmount,
DnCnHdr.NetTotalAmount, DnCnHdr.CurrencyCode, DnCnDtl.ReferInvoiceNo, DnCnDtl.Quantity,
DnCnDtl.DnCnAmount, DnCnDtl.Unit, DnCnDtl.OldPrice, DnCnDtl.NewPrice FROM TransactionHdr,
BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl, DnCnHdr, DnCnDtl
Clearing Cheque
- Run Auto TransactionNo ¨Ò¡ TB RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Default Currency from Original.License -> Populate
SELECT LibrarySpec.Amount FROM LibrarySpec WHERE
Date = get last date and Code = Populate.People.Address
- VIEW & GET: Cheque No from TB DocumentBank
SELECT
Original, Populate, AccountCheque, DocumentType, DocumentNo, DocumentDate, DocumentBank,
DocumentBanakBranch, DocumentAmount, Currency FROM DocumentBank WHERE
DocumentStatus = “Onhand”
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, DocumentBankTrans.DocumentType,
DocumentBankTrans.DocumentNo, DocumentBankTrans.Populate,
DocumentBankTrans.ReferDocumentNo, DocumentBankTrans.ReferDocumentDate,
DocumentBankTrans.Amount, DocumentBankTrans.BankAccount, DocumentBankTrans.CurrencyCode
FROM TransactionHdr, BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl,
DocumentBankTrans
UPDATE DocumentBank SET DocumentStatus = “PayIn”
Purchase Return
- Run Auto TransactionNo from RunningNumber
- Default Transaction Date by System Date
- VIEW & GET: Default Currency from Original.License -> Populate
SELECT LibrarySpec.Amount FROM LibrarySpec WHERE
Date = get last date and Code = Populate.People.Address
- VIEW & GET: Invoice No from TB InvoiceHdr -> InvoiceDtl
SELECT InvoiceHdr.Populate, Populate.Prefix + Populate.FirstName +
Populate.MiddleName + Populate.LastName, Corporation.Address, InvoiceHdr.Currency,
InvoiceDtl.Library -> Library, Library.Name, InvoiceDtl.Quantity, InvoiceDtl.Unit,
InvoiceDtl.UnitPrice, InvoiceDtl.Amount
Verify each product must have Invoice no
Verify return quantity is less than or equal invoice quantity
- Supplier from TB Populate -> Populate -> PopulateContract
SELECT
Prefix+ FirstName + MiddleName + LastName FROM Populate
- QUERY: SELECT TransactionHdr.Original,
TransactionHdr.TransactionType, TransactionHdr.TransactionNo,
TransactionHdr.TransactionDate, TransactionHdr.Department, TransactionHdr.Project,
TransactionHdr.Populate, TransactionHdr.DataRemark, BusinessLibraryTransactionDtl.Library,
BusinessLibraryTransactionDtl.Quantity, BusinessLibraryTransactionDtl.RemainQuantity,
BusinessLibraryTransactionDtl.Amount, BusinessLibraryTransactionDtl.RemainAmount,
BusinessInventoryTransactionDtl.Inventory, BusinessInventoryTransactionDtl.Amount,
BusinessInventoryTransactionDtl.RemainAmount, InventoryTransaction.ReferTransactionNo,
InventoryTransaction.ReferDocumentNo, InventoryTransaction.FromOriginal,
InventoryTransaction.ToOriginal, InventoryTransaction.FromDepartment,
InventoryTransaction.ToDepartment, InventoryTransaction.FromPerson,
InventoryTransaction.ToPerson, InventoryTransaction.Producer, InventoryTransaction.Price,
InventoryTransaction.Quantity, InventoryTransaction.Unit,
InventoryTransaction.InvoiceDate, InventoryTransaction.InvoiceDateNo,
InventoryTransaction.LotDate, InventoryTransaction.LotDateNo, InventoryTransaction.Cost,
InventoryTransaction.RemainQuantityByInvoice, InventoryTransaction.RemainQuantityByLot,
InventoryTransaction.RemainAmountByLot, InventoryTransaction.IssueDate,
InventoryTransaction.ExpiryDate, InventoryTransaction.DepreciationValue FROM
TransactionHdr, BusinessLibraryTransactionDtl, BusinessInventoryTransactionDtl,
InventoryTransaction
UPDATE OrderDtl (ReturnQuantity) SET ReturnQuantity – Quantity
Last update : Monday, 7 January 2002 07:00 ( Thailand )
Apple, Mac, iMac, iPhone and iPod are trademarks of Apple, Inc.
Jini, Java and all Java-based are trademarks of Oracle Corporation.
JiniSoft Corporation
Copyright @ 1990 - 2012 Mr. Roongroj Rojanapo (  )
89/1 Khao Lam Road, Saen Suk, Chon buri District, Chon buri 20130, Thailand
|
