2 Replies Latest reply: May 14, 2013 11:04 PM by 894936 RSS

    Need help on VIEW.

    894936
      i want to implement the two views logic in single view.
      in first view i have different logic for invoice total and second different logic for invoice total.
      i want to put single invoice_total conditon which shd give same results.
      
      
      
      CREATE OR REPLACE FORCE VIEW RIMS.B2B_CUSTOMER_INVOICE_I_467_C
      (
         SEND_DATE,
         BU_CODE,
         BU_TYPE,
         CUST_NO,
         CUR_CODE,
         SALES_DATE,
         RECEIPT_NO,
         TILL_NO,
         CARD_NO,
         INVOICE_TOTAL,
         AMOUNT_OF_GOODS,
         AMOUNT_NON_GOODS,
         AMOUNT_ADVANCE_PAY,
         AMOUNT_DISCOUNTS,
         ON_HOLD_FLAG
      )
      AS
           SELECT DISTINCT
                  sysdate AS TIME_STAMP,
                  CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
                  CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
                  CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
                  CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
                  TO_DATE (A.SALES_DATE, 'YYMMDD'),
                  CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
                  CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
                  CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
                  SUM (
                     NVL (a.SOLD_AMOUNT, 0)
                     * curr_02_pck.corrfact_fct (B.comp_code, 'I0470001'))
                     AS INVOICE_TOTAL,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                  'N' AS ON_HOLD_FLAG
            FROM I_0470002_LOG_T A, CURRENCY_DECIMALS_T B
            WHERE A.CUR_CODE = B.BASE_CURRENCY 
                  AND A.SALES_DATE IN
                         ('121115', '121116', '121117', '121118', '121119', '121120','130130','130131') ----sales_date is added for testing purpose.
                  AND CUR_CODE IN ('JPY', 'HUF')
                  AND A.SOLD_AMOUNT != 0
            GROUP BY A.STO_NO,
                  A.CUR_CODE,
                  A.RECEIPT_NO,
                  A.CASH_NO,
                  A.SALES_DATE
      
      
      
      
      SELECT * FROM B2B_CUSTOMER_INVOICE_I_467
      
      
      CREATE OR REPLACE FORCE VIEW RIMS.B2B_CUSTOMER_INVOICE_I_467
      (
         SEND_DATE,
         BU_CODE,
         BU_TYPE,
         CUST_NO,
         CUR_CODE,
         SALES_DATE,
         RECEIPT_NO,
         TILL_NO,
         CARD_NO,
         INVOICE_TOTAL,
         AMOUNT_OF_GOODS,
         AMOUNT_NON_GOODS,
         AMOUNT_ADVANCE_PAY,
         AMOUNT_DISCOUNTS,
         ON_HOLD_FLAG
      )
      AS
           SELECT DISTINCT
                  sysdate AS TIME_STAMP,
                  CAST (STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
                  CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
                  CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
                  CAST (CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
                  TO_DATE (SALES_DATE, 'YYMMDD'),
                  CAST (RECEIPT_NO AS VARCHAR2 (10 BYTE)),
                  CAST (CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
                  CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
                  CAST (SUM (SOLD_AMOUNT) AS NUMBER (11, 2)) / 100 AS INVOICE_TOTAL,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                  'N' AS ON_HOLD_FLAG
            FROM I_0470002_LOG_T                 
            WHERE SALES_DATE IN
                     ('121115',
                      '121116',
                      '121117',
                      '121118',
                      '121119',
                      '121120',
                      '130130',
                      '130131')    
                 AND SOLD_AMOUNT != 0
                  AND CUR_CODE NOT IN ('JPY', 'HUF')
         GROUP BY STO_NO,
                  CUR_CODE,
                  RECEIPT_NO,
                  CASH_NO,
                  SALES_DATE;
      Edited by: 891933 on Feb 8, 2013 10:57 AM

      Edited by: 891933 on Feb 10, 2013 9:03 AM