Ratakan

Informations :

Smartlist AP Apply Detail

This Query is used to create Smartlist AP Apply Detail :


SP View :




create view ABC_AP_Apply

as


select

P.VENDORID Vendor_ID,

PM.VENDNAME Vendor_Name,

case P.DOCTYPE

   when 1 then 'Invoice'

   when 2 then 'Finance Charge'

   when 3 then 'Misc Charge'

   end Document_Type,

P.DOCDATE Document_Date,

P.VCHRNMBR Voucher_Number,

P.DOCNUMBR Document_Number,

P.DOCAMNT Document_Amount,

P.TEN99AMNT [1099_Amount], 

P.TEN99BOXNUMBER [1099_Box], 

case P.TEN99TYPE 

   when 1 then 'Not a 1099 Vendor'

   when 2 then 'Divident'

   when 3 then 'Interest'

   when 4 then 'Miscellaneous'

   when 5 then 'Withholding'

   else ''

   end [1099_Type],

coalesce(PA.APFRMAPLYAMT,0) Applied_Amount,

coalesce(PA.VCHRNMBR,'') Payment_Voucher_Number,

coalesce(P2.DOCNUMBR,'') Payment_Doc_Number,

coalesce(P2.DOCDATE,'1/1/1900') Payment_Date,

coalesce(PA.DATE1,'1/1/1900') Apply_Date,

coalesce(PA.GLPOSTDT, '1/1/1900') Apply_GL_Posting_Date,

case PA.DOCTYPE

   when 4 then 'Return'

   when 5 then 'Credit Memo'

   when 6 then 'Payment'

   else ''

   end Payment_Type,

coalesce(PA.POSTED, 'Unpaid') Payment_Status



from

 (select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,

  DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE

  from PM30200

 union all

  select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,

  DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE

  from PM20000) P



left outer join

 (select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,

  DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT,

  case POSTED when 0 then 'Unposted' else 'Posted' end POSTED

  from PM10200

 union

  select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,

  DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT,

  'Posted' POSTED

  from PM30300) PA

    on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID

    and P.DOCTYPE = PA.APTODCTY



left outer join

 (select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE

  from PM20000

 union all

  select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE

  from PM30200) P2

    on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE



left outer join PM00200 PM

    on P.VENDORID = PM.VENDORID



where P.DOCTYPE in (1,2,3) and P.VOIDED = 0



go

grant select on ABC_AP_Apply to DYNGRP




Hasilnya : 





No comments