Sunday, December 5, 2010

INV Open Periods

Your Ad Here



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
Your Ad Here

4 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
  4. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete