Sunday, December 5, 2010

INV Open Periods



SELECT
opu.name as operating_unit
, per.organization_id as inv_org_id
, par.organization_code as inv_org_code
, per.period_name
, per.period_year
, per.period_num
, flv.meaning as status
, per.period_start_date
, per.schedule_close_date
FROM
org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1=1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) = decode(nvl(per.period_close_date,sysdate), per.period_close_date, decode(per.open_flag, 'N', decode(summarized_flag,'N',65,66), 'Y', 4, 'P', 2, 4), 3 )
AND flv.language = 'US'
AND upper(flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND otl.language = 'US'
ORDER BY opu.name, per.organization_id, per.period_year desc, per.period_num desc

3 comments:

  1. I think that the table named hr_all_organization_units_tl have language control , so that maybe add sql where clause such as

    ReplyDelete
  2. AND otl.language = 'US'

    ReplyDelete
  3. It will be handled in AND flv.language = 'US', but anyways i have added your condition as well to make it more refined.

    Thanks for the input dear...

    Amit

    ReplyDelete

Note: Only a member of this blog may post a comment.