月结各模块关闭情况查询

/* Formatted on 2018/3/15 9:57:59 (QP5 v5.256.13226.35538) */

--库存模块

  SELECT oap.status "关闭状态"

       , oap.period_name "所属期间"

       , oap.organization_id 组织id

       , (SELECT name

            FROM hr_organization_units x

           WHERE x.organization_id = oap.organization_id)

            "组织名称"

       , oap.last_update_date "执行关闭日期"

       , (SELECT hre.full_name

            FROM hr_employees_all_v hre, fnd_user fu

           WHERE hre.employee_id = fu.employee_id

             AND fu.user_id = oap.last_updated_by)

            "执行关闭人"

       , oap.created_by

    FROM org_acct_periods_v oap

   WHERE 1 = 1

     AND oap.period_number = 6        --月份   

     and (oap.PERIOD_YEAR = 2012)       -- 年份  

     and oap.ORGANIZATION_ID<> 0

ORDER BY oap.organization_id, oap.period_name DESC, oap.status DESC;

 

--其他模块

  SELECT gps.period_name "所属期间"

       , (SELECT faa.application_name

            FROM fnd_application_all_view faa

           WHERE faa.application_id = gps.application_id)

            "模块名称"

       , gps.ledger_id "分类账套"

       , gps.show_status "期间状态"

       , (SELECT hre.full_name

            FROM hr_employees_all_v hre, fnd_user fu

           WHERE hre.employee_id = fu.employee_id

             AND fu.user_id = gps.last_updated_by)

            "执行关闭人"

       , gps.last_update_date "最后次操作时间"

    FROM gl_period_statuses_v gps

   WHERE 1 = 1

     --ND gps.ledger_id = 2021

     --gps.application_id = 101

     AND gps.closing_status != 'N'

ORDER BY gps.application_id, gps.period_name DESC;