7 Replies Latest reply: Feb 11, 2013 1:03 AM by Karthick_Arp 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;
        • 1. Re: Need help on View
          ---Brodyaga---
          Hi
          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) * 
                         case
                           when CUR_CODE IN ('JPY', 'HUF') then curr_02_pck.corrfact_fct (B.comp_code, 'I0470001')
                           else 1/100
                         end)
                         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 A.SOLD_AMOUNT != 0
                GROUP BY A.STO_NO,
                      A.CUR_CODE,
                      A.RECEIPT_NO,
                      A.CASH_NO,
                      A.SALES_DATE
          • 2. Re: Need help on View
            894936
            Hi this is calculating wrong values.

            The calculation for invoice_total is taking something else.
            as u can see... the below code does not satisfy my reuriment like....

            1: SUM (
            NVL (a.SOLD_AMOUNT, 0)
            * curr_02_pck.corrfact_fct (B.comp_code, 'I0470001'))
            AS INVOICE_TOTAL,

            2:CAST (SUM (SOLD_AMOUNT) AS NUMBER (11, 2)) / 100 AS INVOICE_TOTAL,


            for the both 1 and 2 we are taking nvl(a.sold_amount,0) ----which is calculating wrong values. i shdould take both 1 and 2 conditions in one view.
             SUM (
                           NVL (a.SOLD_AMOUNT, 0) * 
                           case
                             when CUR_CODE IN ('JPY', 'HUF') then curr_02_pck.corrfact_fct (B.comp_code, 'I0470001')
                             else 1/100
                           end)
            • 3. Re: Need help on View
              894936
              Could you please help me out.
              • 4. Re: Need help on View
                Karthick_Arp
                891933 wrote:
                Could you please help me out.
                When you say you want to combine the two view into a single view, what do you expect out of it. How you will be using the combined view. Are you expecting the new view to give two different invoice_total as two columns?

                You should know that the first view does a inner join with currency_decimals_t, but the second view has no joining condition. So both the view may not return the same number of rows. How do you want to handle that?

                Please explain your expected output after combining the view.
                • 5. Re: Need help on View
                  Purvesh K
                  How about this?
                  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,
                              case
                                when CUR_CODE IN ('JPY', 'HUF')
                                then
                                 SUM (
                                 NVL (a.SOLD_AMOUNT, 0)
                                 * curr_02_pck.corrfact_fct (B.comp_code, 'I0470001'))
                                else
                                  SUM (SOLD_AMOUNT)/100
                               end
                                 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
                  • 6. Re: Need help on View
                    894936
                    Hi,
                    i want a single view. which should consists of only one column as invoice total which should satify two conditions.
                    first one shd be the sum(sold_amount) shd be divided by 100 and output shd be retun as invoice_total for the currency code which are NOT IN JPY and HUF.

                    "CAST (SUM (SOLD_AMOUNT) AS NUMBER (11, 2)) / 100 AS INVOICE_TOTAL,AND CUR_CODE NOT IN ('JPY', 'HUF')"

                    Second one shd be the sum(sold_amount) will be calculated based on existing function that is corrfact_fct.
                    SUM (
                    NVL (a.SOLD_AMOUNT, 0)
                    * curr_02_pck.corrfact_fct (B.comp_code, 'I0470001'))
                    AS INVOICE_TOTAL,
                    WHERE A.CUR_CODE = B.BASE_CURRENCY --AND B.IDRS_TRANSACTION = 'I0470001'
                    AND CUR_CODE IN ('JPY', 'HUF')

                    I_0470002_LOG_T,CURRENCY_DECIMALS_T B

                    my final view should consists of two tables I_0470002_LOG_T,CURRENCY_DECIMALS_T B
                    my expected output shd consists of one invoice_totl which shd calculate sum(sold_amount) based on above two conditions.
                    Please let me know if you any other information.
                    • 7. Re: Need help on View
                      Karthick_Arp
                      Then in the first view just put a CASE statement for column INVOICE_TOTAL like this
                      case when cur_code in ('JPY', 'HUF')
                           sum ( nvl (a.sold_amount, 0) * curr_02_pck.corrfact_fct (b.comp_code, 'I0470001'))
                           else 
                           sum (sold_amount) as number (11, 2)) / 100
                      end invoice_total
                      And remove the CUR_CODE condition from WHERE clause. That should get what you want.