The script which used to Check GL-PO-AP-INV Period Status

xitong

浏览: 6177436 次

最近访客更多访客>>

gegewuqin9

summer_1988

u012363178

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (7329)

社区版块

存档分类

2013-05 ( 45)
2013-04 ( 98)
2013-03 ( 45)
更多存档...

--需要输入set_of_books_id，set_of_books_id可以从GL_SETS_OF_BOOKS查,Demo环境中的Vision Operations (USA)的set_of_books_id是1

  SELECT   a.period_name,
           a.period_num,
           a.gl_status,
           b.po_status,
           c.ap_status,
           d.inv_status
    FROM   (SELECT   period_name,
                     period_num,
                     DECODE (closing_status,
                             'O', 'Open',
                             'C', 'Closed',
                             'F', 'Future',
                             'N', 'Never',
                             closing_status)
                        gl_status
              FROM   gl_period_statuses
             WHERE       application_id = 101      --GL
                     AND start_date >= '01-JAN-08'
                     AND end_date < '01-JAN-09'
                     AND set_of_books_id = &&set_of_books_id) a,
           (SELECT   period_name,
                     DECODE (closing_status,
                             'O', 'Open',
                             'C', 'Closed',
                             'F', 'Future',
                             'N', 'Never',
                             closing_status)
                        po_status
              FROM   gl_period_statuses
             WHERE       application_id = 201          --Purchasing
                     AND start_date >= '01-JAN-08'
                     AND end_date < '01-JAN-09'
                     AND set_of_books_id = &&set_of_books_id) b,
           (SELECT   period_name,
                     DECODE (closing_status,
                             'O', 'Open',
                             'C', 'Closed',
                             'F', 'Future',
                             'N', 'Never',
                             closing_status)
                        ap_status
              FROM   gl_period_statuses
             WHERE       application_id = 200     --AP
                     AND start_date >= '01-JAN-08'
                     AND end_date < '01-JAN-09'
                     AND set_of_books_id = &&set_of_books_id) c,
           (SELECT   period_name,
                     DECODE (closing_status,
                             'O', 'Open',
                             'C', 'Closed',
                             'F', 'Future',
                             'N', 'Never',
                             closing_status)
                        inv_status
              FROM   gl_period_statuses
             WHERE       application_id = 401    --Inventory
                     AND start_date >= '01-JAN-08'
                     AND end_date < '01-JAN-09'
                     AND set_of_books_id = &&set_of_books_id) d                     
   WHERE   a.period_name = b.period_name AND a.period_name = c.period_name
ORDER BY   a.period_num;


--SELECT * FROM fnd_application WHERE application_id IN (101,201,200,401)

分享到：