{\rtf1\ansi\ansicpg1252\cocoartf2870
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\margl1440\margr1440\vieww11520\viewh8400\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 create view view_Fidesic_Credit_Apply\
as\
\
select\
   C.VENDORID                              Vendor_ID,\
   PM.VENDNAME                             Vendor_Name,\
   case C.DOCTYPE\
      when 4 then 'Return'\
      when 5 then 'Credit Memo'\
      end                                  Credit_Type,\
   C.DOCDATE                               Credit_Date,\
   C.DOCNUMBR                              Credit_Number,\
   C.DOCAMNT                               Credit_Amount,\
   coalesce(I.DOCNUMBR,'')                 Document_Number,\
   coalesce(I.DOCDATE,'1/1/1900')          Invoice_Date,\
   coalesce(I.DOCAMNT,0)                   Invoice_Amount,\
   coalesce(PA.APPLDAMT,0)                 Applied_Amount,\
   coalesce(PA.DATE1,'1/1/1900')           Apply_Date\
\
from\
-- credit documents only (returns + credit memos), open + history\
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED\
   from PM20000 where DOCTYPE in (4,5) and VOIDED = 0\
 union\
 select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED\
   from PM30200 where DOCTYPE in (4,5) and VOIDED = 0) C\
\
-- apply records, linked on the credit (apply-from) voucher\
left outer join\
(select VENDORID, APTVCHNM, APTODCTY, VCHRNMBR, DOCTYPE, APPLDAMT, DATE1\
   from PM10200\
 union\
 select VENDORID, APTVCHNM, APTODCTY, VCHRNMBR, DOCTYPE, APPLDAMT, DATE1\
   from PM30300) PA\
   on  C.VCHRNMBR = PA.VCHRNMBR\
   and C.VENDORID = PA.VENDORID\
   and C.DOCTYPE  = PA.DOCTYPE\
\
-- the invoice the credit was applied to (apply-to side)\
left outer join\
(select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE, DOCAMNT\
   from PM20000 where DOCTYPE in (1,2,3) and VOIDED = 0\
 union all\
 select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE, DOCAMNT\
   from PM30200 where DOCTYPE in (1,2,3) and VOIDED = 0) I\
   on  I.VCHRNMBR = PA.APTVCHNM\
   and I.DOCTYPE  = PA.APTODCTY\
\
left outer join PM00200 PM\
   on C.VENDORID = PM.VENDORID\
\
go\
grant select on view_Fidesic_Credit_Apply to DYNGRP}