This discussion is archived
2 Replies Latest reply: May 14, 2013 9:04 PM by 894936 RSS

Need help on VIEW.

894936 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points