USE [TWO] 

GO 

/CREATED BY SATYENDRA SINGH ON 10/29/2016 9:46:25 AM ******/ 
SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

CREATE VIEW [DBO].[TB_DETAIL_ORIGINATING_CURRENCY] 
AS 
  SELECT C.CUSTNAME AS CUSTOMERNAME, 
         B.CUSTNMBR AS CUSTOMERID, 
         CASE B.RMDTYPAL 
           WHEN 1 THEN 'INVOICE' 
           WHEN 3 THEN 'DEBIT_MEMO' 
           WHEN 4 THEN 'FIN_CHARGE' 
           WHEN 5 THEN 'SERVICE' 
           WHEN 6 THEN 'WARRANTY' 
           WHEN 7 THEN 'CREDIT_MEMO' 
           WHEN 8 THEN 'RETURN' 
           WHEN 9 THEN 'PAYMENT' 
           ELSE 'OTHERS' 
         END        AS DOCUMENTTYPE, 
         B.DOCNUMBR AS DOCUMENTNUMBER, 
         B.DOCDATE  AS 'DOCUMENTDATE', 
         B.CURNCYID AS CURRENCY, 
         --A.ORCTRXAM,  
         CASE 
           WHEN B.RMDTYPAL < 7 THEN B.CURTRXAM 
           ELSE -1 * B.CURTRXAM 
         END        AS AMTREMAININGCAD, 
         CASE 
           WHEN B.CURNCYID <> 'CDN' 
                AND B.RMDTYPAL < 7 THEN A.ORCTRXAM 
           WHEN B.CURNCYID <> 'CDN' 
                AND B.RMDTYPAL >= 7 THEN -1 * A.ORCTRXAM 
           WHEN B.CURNCYID = 'CDN' 
                AND B.RMDTYPAL < 7 THEN B.CURTRXAM 
           WHEN B.CURNCYID = 'CDN' 
                AND B.RMDTYPAL >= 7 THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS 'ORIGINATINGAMOUNTREMAINING', 
         B.AGNGBUKT AS AGINGBUCKET, 
         CASE 
           WHEN ( B.AGNGBUKT = 1 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 1 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 1 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 1 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS 'CURRENT', 
         CASE 
           WHEN ( B.AGNGBUKT = 2 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 2 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 2 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 2 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS '31 TO 60 DAYS', 
         CASE 
           WHEN ( B.AGNGBUKT = 3 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 3 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 3 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 3 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS '61 TO 90 DAYS', 
         CASE 
           WHEN ( B.AGNGBUKT = 4 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 4 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 4 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 4 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS '91 TO 120 DAYS', 
         CASE 
           WHEN ( B.AGNGBUKT = 5 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 5 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 5 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 5 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS '121 TO 365 DAYS', 
         CASE 
           WHEN ( B.AGNGBUKT = 6 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 6 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 6 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 6 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS '366 TO 730 DAYS', 
         CASE 
           WHEN ( B.AGNGBUKT = 7 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 7 
                  AND B.CURNCYID <> 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * A.ORCTRXAM 
           WHEN ( B.AGNGBUKT = 7 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL < 7 ) THEN B.CURTRXAM 
           WHEN ( B.AGNGBUKT = 7 
                  AND B.CURNCYID = 'CDN' 
                  AND B.RMDTYPAL >= 7 ) THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS 'OVER 2 YEARS', 
         CASE 
           WHEN B.CURNCYID <> 'CDN' 
                AND B.RMDTYPAL < 7 THEN A.ORCTRXAM 
           WHEN B.CURNCYID <> 'CDN' 
                AND B.RMDTYPAL >= 7 THEN -1 * A.ORCTRXAM 
           WHEN B.CURNCYID = 'CDN' 
                AND B.RMDTYPAL < 7 THEN B.CURTRXAM 
           WHEN B.CURNCYID = 'CDN' 
                AND B.RMDTYPAL >= 7 THEN -1 * B.CURTRXAM 
           ELSE 0 
         END        AS 'BALANCE (ORIGINATING CURRENCY)', 
         CASE 
           WHEN B.RMDTYPAL < 7 THEN B.CURTRXAM 
           ELSE -1 * B.CURTRXAM 
         END        AS 'BALANCE (FUNCTIONAL)' 
  FROM   RM20101 B 
         LEFT OUTER JOIN MC020102 A 
                      ON A.DOCNUMBR = B.DOCNUMBR 
                         AND A.CUSTNMBR = B.CUSTNMBR 
         LEFT OUTER JOIN RM00101 C 
                      ON B.CUSTNMBR = C.CUSTNMBR 
  WHERE  B.VOIDSTTS = 0 
         AND B.CURTRXAM <> 0 

GO