Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1852695
  • 博文数量: 334
  • 博客积分: 11301
  • 博客等级: 上将
  • 技术积分: 3452
  • 用 户 组: 普通用户
  • 注册时间: 2006-10-18 10:19
个人简介

路虽弥,不行不至;事虽少,不做不成。

文章分类

全部博文(334)

文章存档

2013年(4)

2012年(19)

2011年(27)

2010年(71)

2009年(45)

2008年(15)

2007年(84)

2006年(69)

分类: Oracle

2008-07-23 12:28:00


SELECT   details."Vendor_Num", details."Vendor_Name",
         details."Invoice_Currency",
         SUM (details."Amount_Remaining") "Balances",
         SUM (DECODE (SIGN (details."Days" - 31),
                      1, 0,
                      DECODE (SIGN (details."Days"-0 ),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "1-30 Days",
         SUM (DECODE (SIGN (details."Days" - 61),
                      1, 0,
                      DECODE (SIGN (details."Days" - 31 ),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "31-60 Days",
         SUM (DECODE (SIGN (details."Days" - 91),
                      1, 0,
                      DECODE (SIGN (details."Days" -61),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "61-90 Days",
         SUM (DECODE (SIGN (details."Days" - 36500),
                      1, 0,
                      DECODE (SIGN (details."Days" - 91 ),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "Over 90 Days",
         SUM (DECODE (SIGN (details."Days1" - 31),
                      1, 0,
                      DECODE (SIGN (details."Days1"-0 ),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "Due with 30 Days"
    FROM (SELECT api.invoice_num "Invoice_Num",
                 api.invoice_date "Invoice_Date", aps.due_date "Due_Date",
                 ROUND (SYSDATE - api.invoice_date) "Days",
                 ROUND (SYSDATE - aps.due_date) "Days1",
                 pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name",
                 term.NAME "Payment_Terms",
                 api.invoice_currency_code "Invoice_Currency",
                 api.payment_currency_code "Payment_Currency",
                 aps.gross_amount "Invoice_Amount",
                 aps.amount_remaining "Amount_Remaining",
                   NVL (aps.amount_remaining, 0)
                 * NVL (api.exchange_rate, 1) "Amount_Remaining(Base)",
                 fu.user_name "User_Name"
            FROM ap.ap_payment_schedules_all aps,
                 ap.ap_invoices_all api,
                 po.po_vendors pv,
                 po.po_vendor_sites_all pvs,
                 ap.ap_terms_tl term,
                 apps.fnd_user fu
           WHERE aps.org_id = &org_id
             AND aps.payment_status_flag IN ('N', 'P')
             AND aps.invoice_id = api.invoice_id
             AND api.cancelled_date IS NULL
             AND aps.amount_remaining <> 0
             AND api.vendor_id = pv.vendor_id
             AND api.vendor_site_id = pvs.vendor_site_id
             AND pv.vendor_id = pvs.vendor_id
             AND api.terms_id = term.term_id
             AND api.created_by = fu.user_id
             ) details
GROUP BY details."Vendor_Num",
         details."Vendor_Name",
         details."Invoice_Currency"
  HAVING SUM (details."Amount_Remaining") <> 0
ORDER BY 1, 3
阅读(1628) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~