Category : Files from Magazines
Archive   : DBMS9104.ZIP
Filename : TRANSACT.APR

* Batch Run Transaction Group

TRANSACTION 15: Generate Catalog

Input fields: General Ledger Listing

SQL stmts: AccountID char(9)
FromDate char(8)
ToDate char(8)

SELECT A.Name, J.EntryDate, L.Debit, L.Credit, J.ID, J.Explanation
FROM Journal J, Ledger L, Accounts A
WHERE J.ID = L.JournalID
AND A.ID = L.AccountID
AND L.AccountID = :LAccountID
AND (J.EntryDate BETWEEN TO_DATE (:LFromDate,'YYYYMMDD')
AND TO_DATE (:LToDate,'YYYYMMDD'))
ORDER BY J.EntryDate

-------------------------------------------------------------------------
TRANSACTION 16: General Journal Listing

Input fields: FromDate char(8)
ToDate char(8)

SELECT J.ID, J.EntryDate, A.Name, L.Debit, L.Credit, J.Explanation
FROM Accounts A, Ledger L, Journal J
WHERE J.ID = L.JournalID
AND L.AccountID = A.ID
AND (J.EntryDate BETWEEN TO_DATE (:LFromDate,'YYYYMMDD')
AND TO_DATE (:LToDate,'YYYYMMDD'))
ORDER BY J.ID
-------------------------------------------------------------------------
TRANSACTION 17: Balance Sheet

Input fields: FromDate char(8)
ToDate char(8)
SQL stmts:
SELECT A.Type, A.Name, SUM(L.Debit)-SUM(L.Credit)
FROM Accounts A, Ledger L, Journal J
WHERE A.ID = L.AccountID
AND L.JournalID = J.ID
AND (J.EntryDate BETWEEN :LFromDate AND :LToDate)
AND A.Type IN ("LA","FA","STL","LTL","EQ")
GROUP BY A.ID, A.Type,A.Name
ORDER BY A.Type, A.ID;
-------------------------------------------------------------------------
TRANSACTION 18: Balance Sheet

Input fields: Income Statement

SQL stmts: FromDate char(8)
ToDate char(8)

SELECT A.Type, A.Name, SUM(L.Debit)-SUM(L.Credit)
FROM Accounts A, Ledger L, Journal J
WHERE A.ID = L.AccountID
AND L.JournalID = J.ID
AND (J.EntryDate BETWEEN :LFromDate AND :LToDate)
AND A.Type IN ("INC","EXP")
GROUP BY A.ID,A.Type,A.Name
ORDER BY A.Type, A.ID;
-------------------------------------------------------------------------
TRANSACTION 30:Catalog Generation

Input fields: Today char(8)

SQL stmts: SELECT C.CatalogID, C.Name, C.Announced, C.Introduced,
C.Withdrawn, P.Quantity, P.Price, P.FromDate,
P.ToDate
FROM Products C, PricingPolicy P
WHERE Catalog.ID (+) = P.CatalogID
AND P.ToDate >= :LToday
ORDER BY C.Catalog, P.Quantity;
-------------------------------------------------------------------------
TRANSACTION 31: Price Sheet Generation

Input fields: Today char(8)


SELECT PROD.CatalogID, PROD.Name, P.Quantity, P.Price,
P.FromDate, P.ToDate
FROM Products PROD, PricingPolicy P
WHERE PROD.ProductID = P.ProductID
AND ((PROD.Announced <= TO_DATE(:LToday,'YYYYMMDD'))
OR (PROD.Announced is NULL))
AND ((PROD.Withdrawn > TO_DATE(:LToday,'YYYYMMDD'))
OR (PROD.Withdrawn is NULL))
AND P.FromDate <= TO_DATE(:LToday,'YYYYMMDD')
AND ((P.ToDate >= TO_DATE(:LToday,'YYYYMMDD'))
OR (P.ToDate is NULL))
ORDER BY PROD.CatalogID, P.Quantity

-------------------------------------------------------------------------
TRANSACTION 32: Mailing Label Generation

Input fields: Company char(32)

SELECT P.FirstName,
P.LastName,
J.Title,
C.Name,
A.LineOne,
A.LineTwo,
Z.City,
Z.Zipcode
FROM CompanyPeople D, People P, Companies C, Addresses A,
Zipcodes Z, JobTitles J
WHERE D.PersonID = P.ID
AND D.TitleID = J.ID (+)
AND D.WorkAddr = A.ID
AND D.CompanyID = C.ID
AND A.Country = Z.Country
AND A.Zipcode = Z.Zipcode
AND C.Name = :LCompany
ORDER BY A.Zipcode";