opu.name as operating_unit
, per.organization_id as inv_org_id
, par.organization_code as inv_org_code
, flv.meaning as status
, 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
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
I think that the table named hr_all_organization_units_tl have language control , so that maybe add sql where clause such asReplyDelete
AND otl.language = 'US'ReplyDelete
It will be handled in AND flv.language = 'US', but anyways i have added your condition as well to make it more refined.ReplyDelete
Thanks for the input dear...
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 email@example.com | +91 - 9581017828.