ProfitPress Mega CDROM2 …eeware (MSDOS)(1992)(Eng)/ProfitPress-MegaCDROM2.B6I/MAGAZINE/MISC/DBMS9104.ZIP/TRANSACT.APR

Text File | 1991-03-15 | 5.2 KB | 103 lines

* 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";