home *** CD-ROM | disk | FTP | other *** search
/ ProfitPress Mega CDROM2 …eeware (MSDOS)(1992)(Eng) / ProfitPress-MegaCDROM2.B6I / MAGAZINE / MISC / DBMS9104.ZIP / TRANSACT.APR < prev   
Encoding:
Text File  |  1991-03-15  |  5.2 KB  |  103 lines
  1. * Batch Run Transaction Group
  2.  
  3.   TRANSACTION 15: Generate Catalog                       
  4.                                        
  5.  Input fields: General Ledger Listing                    
  6.                                        
  7.     SQL stmts: AccountID         char(9)               
  8.          FromDate         char(8)               
  9.          ToDate          char(8)               
  10.                                        
  11.      SELECT    A.Name, J.EntryDate, L.Debit, L.Credit, J.ID, J.Explanation
  12.      FROM     Journal J, Ledger L, Accounts A               
  13.     WHERE     J.ID         = L.JournalID                   
  14.       AND     A.ID         = L.AccountID                   
  15.       AND     L.AccountID = :LAccountID                   
  16.       AND     (J.EntryDate BETWEEN TO_DATE (:LFromDate,'YYYYMMDD')       
  17.       AND      TO_DATE (:LToDate,'YYYYMMDD'))               
  18.    ORDER BY J.EntryDate                           
  19.                                        
  20. -------------------------------------------------------------------------
  21.   TRANSACTION 16: General Journal Listing        
  22.                                        
  23.  Input fields: FromDate          char(8)               
  24.          ToDate           char(8)               
  25.                                        
  26.   SELECT     J.ID, J.EntryDate, A.Name, L.Debit, L.Credit, J.Explanation 
  27.     FROM     Accounts A, Ledger L, Journal J                   
  28.    WHERE     J.ID       = L.JournalID                   
  29.      AND     L.AccountID = A.ID                       
  30.      AND     (J.EntryDate BETWEEN TO_DATE (:LFromDate,'YYYYMMDD')       
  31.      AND     TO_DATE (:LToDate,'YYYYMMDD'))                   
  32.  ORDER BY    J.ID                               
  33. -------------------------------------------------------------------------
  34.   TRANSACTION 17: Balance Sheet                           
  35.                                        
  36.  Input fields: FromDate          char(8)               
  37.          ToDate           char(8)               
  38.     SQL stmts:                               
  39.          SELECT   A.Type, A.Name, SUM(L.Debit)-SUM(L.Credit)       
  40.          FROM      Accounts A, Ledger L, Journal J           
  41.          WHERE      A.ID          = L.AccountID               
  42.            AND      L.JournalID = J.ID                   
  43.            AND      (J.EntryDate BETWEEN :LFromDate AND :LToDate)    
  44.            AND      A.Type IN ("LA","FA","STL","LTL","EQ")       
  45.          GROUP BY A.ID, A.Type,A.Name                   
  46.          ORDER BY A.Type, A.ID;                    
  47. -------------------------------------------------------------------------
  48.   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;                                    -------------------------------------------------------------------------
  49.  TRANSACTION 30:Catalog Generation                       
  50.                                        
  51.  Input fields: Today             char(8)               
  52.                                        
  53.     SQL stmts: SELECT   C.CatalogID, C.Name, C.Announced, C.Introduced,  
  54.               C.Withdrawn, P.Quantity, P.Price, P.FromDate,    
  55.               P.ToDate                       
  56.          FROM      Products C, PricingPolicy P               
  57.          WHERE      Catalog.ID (+) = P.CatalogID               
  58.            AND      P.ToDate >= :LToday                   
  59.          ORDER BY C.Catalog, P.Quantity;               
  60. -------------------------------------------------------------------------
  61.   TRANSACTION 31: Price Sheet Generation                    
  62.                                        
  63.  Input fields: Today             char(8)               
  64.                                        
  65.                                        
  66.      SELECT    PROD.CatalogID,  PROD.Name, P.Quantity, P.Price,       
  67.         P.FromDate, P.ToDate                       
  68.      FROM    Products PROD, PricingPolicy P                   
  69.     WHERE    PROD.ProductID    = P.ProductID                   
  70.       AND    ((PROD.Announced <= TO_DATE(:LToday,'YYYYMMDD'))       
  71.           OR (PROD.Announced is NULL))                   
  72.       AND    ((PROD.Withdrawn >  TO_DATE(:LToday,'YYYYMMDD'))       
  73.           OR (PROD.Withdrawn is NULL))                   
  74.       AND       P.FromDate     <= TO_DATE(:LToday,'YYYYMMDD')        
  75.       AND    ((P.ToDate    >= TO_DATE(:LToday,'YYYYMMDD'))           
  76.           OR (P.ToDate is NULL))                   
  77.    ORDER BY     PROD.CatalogID, P.Quantity                   
  78.                                        
  79. -------------------------------------------------------------------------
  80.  TRANSACTION 32: Mailing Label Generation                   
  81.                                        
  82. Input fields: Company          char(32)               
  83.                                        
  84.         SELECT   P.FirstName,                       
  85.               P.LastName,                       
  86.               J.Title,                           
  87.               C.Name,                           
  88.               A.LineOne,                       
  89.               A.LineTwo,                       
  90.               Z.City,                           
  91.               Z.Zipcode                        
  92.          FROM     CompanyPeople D, People P, Companies C, Addresses A, 
  93.               Zipcodes Z, JobTitles J                   
  94.          WHERE    D.PersonID    = P.ID                   
  95.            AND    D.TitleID     = J.ID (+)                   
  96.            AND    D.WorkAddr    = A.ID                   
  97.            AND    D.CompanyID   = C.ID                   
  98.            AND    A.Country     = Z.Country                
  99.            AND    A.Zipcode     = Z.Zipcode                
  100.            AND    C.Name        = :LCompany                
  101.          ORDER BY A.Zipcode";                                          
  102.                                    
  103.