[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lep.party_id,
lep.legal_entity_id ENTITY_ID,
lep.name ENTITY_NAME,
'LEGAL_ENTITY' ENTITY_TYPE,
reg.registration_number,
reg.registered_name,
reg.alternate_registered_name,
reg.identifying_flag identifying_flag,
jur.legislative_cat_code LEGISLATIVE_CATEGORY,
(select party_name
from hz_parties
where party_id = reg.issuing_authority_id) LEGALAUTH_NAME,
(select hzl.address1 || ' ' || hzl.address2 || ' '
|| hzl.city || ',' || hzl.state
|| ',' || hzl.country || ' ' || hzl.postal_code
from hz_locations hzl, hz_party_sites hps
where hps.location_id = hzl.location_id
and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS,
reg.effective_from,
reg.effective_to,
reg.location_id,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.region_1,
hrl.region_2,
hrl.region_3,
hrl.postal_code,
hrl.country
FROM XLE_ENTITY_PROFILES lep,
XLE_REGISTRATIONS reg,
HR_LOCATIONS_ALL hrl,
XLE_JURISDICTIONS_VL jur
WHERE
lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND hrl.location_id = reg.location_id
AND jur.jurisdiction_id = reg.jurisdiction_id
AND lep.party_ID like l_party_ID
AND lep.legal_entity_id like l_entity_ID
AND nvl(reg.identifying_flag,'N') like l_identifying_flag
AND jur.legislative_cat_code like l_legislative_category
;
SELECT etb.party_id,
etb.establishment_id ENTITY_ID,
etb.name ENTITY_NAME,
'ESTABLISHMENT' ENTITY_TYPE,
reg.registration_number,
reg.registered_name,
reg.alternate_registered_name,
reg.identifying_flag identifying_flag,
jur.legislative_cat_code LEGISLATIVE_CATEGORY,
(select party_name
from hz_parties
where party_id = reg.issuing_authority_id) LEGALAUTH_NAME,
(select hzl.address1 || ' ' || hzl.address2 || ' '
|| hzl.city || ',' || hzl.state
|| ',' || hzl.country || ' ' || hzl.postal_code
from hz_locations hzl, hz_party_sites hps
where hps.location_id = hzl.location_id
and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS,
reg.effective_from,
reg.effective_to,
reg.location_id,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.region_1,
hrl.region_2,
hrl.region_3,
hrl.postal_code,
hrl.country
FROM XLE_ETB_PROFILES etb,
XLE_REGISTRATIONS reg,
HR_LOCATIONS_ALL hrl,
XLE_JURISDICTIONS_VL jur
WHERE
etb.establishment_id = reg.source_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND hrl.location_id = reg.location_id
AND jur.jurisdiction_id = reg.jurisdiction_id
AND etb.party_ID like l_party_ID
AND etb.establishment_id like l_entity_ID
AND nvl(reg.identifying_flag,'N') like l_identifying_flag
AND jur.legislative_cat_code like l_legislative_category
;
/* The following cursor selects establishment specific legal information
from xle_etb_profiles */
CURSOR ETB_Reg_c IS
SELECT etb.party_id,
etb.establishment_id,
etb.name establishment_name,
etb.legal_entity_id,
etb.main_establishment_flag,
etb.activity_code,
etb.sub_activity_code,
etb.type_of_company,
etb.effective_from etb_effective_from,
etb.effective_to etb_effective_to
FROM XLE_ETB_PROFILES etb
WHERE etb.party_ID like l_party_ID
AND etb.establishment_id like l_establishment_id
AND etb.legal_entity_id like l_legalentity_id
;
SELECT to_number(l_party_ID)
INTO party_ID
from dual;
SELECT to_number(l_establishment_ID)
INTO establishment_ID
from dual;
/* Select legal entity information into local placeholder variables
for the given party ID or Legal Entity ID */
SELECT lep.party_id,
lep.legal_entity_id,
lep.name legalentity_name,
lep.legal_entity_identifier,
lep.transacting_entity_flag,
lep.activity_code,
lep.sub_activity_code,
lep.type_of_company,
lep.effective_from,
lep.effective_to
into l_party_id,
l_legal_entity_id,
l_legalentity_name,
l_legal_identifier,
l_transacting_flag,
l_activity_code,
l_sub_activity_code,
l_type_of_company,
l_le_effective_from,
l_le_effective_to
FROM XLE_ENTITY_PROFILES lep
WHERE lep.legal_entity_id = p_legalentity_id;
SELECT lep.party_id,
lep.legal_entity_id,
lep.name legalentity_name,
lep.legal_entity_identifier,
lep.transacting_entity_flag,
lep.activity_code,
lep.sub_activity_code,
lep.type_of_company,
lep.effective_from,
lep.effective_to
into l_party_id,
l_legal_entity_id,
l_legalentity_name,
l_legal_identifier,
l_transacting_flag,
l_activity_code,
l_sub_activity_code,
l_type_of_company,
l_le_effective_from,
l_le_effective_to
FROM XLE_ENTITY_PROFILES lep
WHERE lep.party_ID = p_party_id;
SELECT lep.party_id,
lep.legal_entity_id,
lep.name legalentity_name,
lep.legal_entity_identifier,
lep.transacting_entity_flag,
lep.activity_code,
lep.sub_activity_code,
lep.type_of_company,
lep.effective_from,
lep.effective_to
into l_party_id,
l_legal_entity_id,
l_legalentity_name,
l_legal_identifier,
l_transacting_flag,
l_activity_code,
l_sub_activity_code,
l_type_of_company,
l_le_effective_from,
l_le_effective_to
FROM XLE_ENTITY_PROFILES lep
WHERE lep.party_ID = p_party_id
and lep.legal_entity_id = p_legalentity_id;
SELECT xlh1.source_table,
xlh1.source_id,
xlh1.source_column_name,
xlh1.source_column_value,
xlh1.effective_from,
xlh1.effective_to,
xlh1.comments
FROM XLE_HISTORIES xlh1
where trunc(to_date(l_effective_date,'DD-MM-YYYY'))
between (trunc(to_date(effective_from,'DD-MM-YYYY'))) and
trunc(nvl(to_date(effective_to,'DD-MM-YYYY'),sysdate))
AND xlh1.source_table = l_source_table
AND xlh1.source_id = l_entity_id;
SELECT xlh1.source_table,
xlh1.source_id,
xlh1.source_column_name,
xlh1.source_column_value,
xlh1.effective_from,
xlh1.effective_to,
xlh1.comments
FROM XLE_HISTORIES xlh1
WHERE xlh1.source_table = l_source_table
AND xlh1.source_id = l_entity_id;
select to_date(p_effective_date,'DD-MM-YYYY')
into l_effective_date
from dual;
SELECT O3.ORG_INFORMATION3
INTO l_ledger_id
FROM HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O2, HR_ORGANIZATION_INFORMATION O3
WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORGANIZATION_ID = O3.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND o.organization_id = p_operating_unit;
SELECT lep.name
INTO x_legal_entity_name
FROM XLE_ENTITY_PROFILES lep
WHERE lep.party_id=p_party_id;
SELECT DISTINCT hrl.country
INTO l_country_code
FROM XLE_ENTITY_PROFILES lep,
HR_LOCATIONS_ALL hrl,
XLE_REGISTRATIONS reg
WHERE lep.legal_entity_id = l_legal_entity_id
AND reg.source_id = lep.legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.location_id = hrl.location_id;
SELECT hrl.country
INTO x_country_code
FROM XLE_ENTITY_PROFILES xlep,
XLE_REGISTRATIONS reg,
HR_LOCATIONS_ALL hrl
WHERE xlep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND nvl(reg.effective_from,sysdate) <= sysdate
AND nvl(reg.effective_to, sysdate) >= sysdate
AND reg.location_id = hrl.location_id
AND xlep.legal_entity_id = l_legal_entity_id;
SELECT 'Y'
INTO l_establishment_flag
FROM XLE_ETB_PROFILES
WHERE party_id = p_party_id
AND ( effective_to >= sysdate OR effective_to is null);
SELECT transacting_entity_flag
INTO l_TransEntity
FROM xle_entity_profiles
WHERE party_id = p_party_id;
SELECT O3.ORG_INFORMATION3
INTO l_ledger_id
FROM HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O2, HR_ORGANIZATION_INFORMATION O3
WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORGANIZATION_ID = O3.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND o.organization_id = p_operating_unit;
SELECT party_id
INTO l_party_id
FROM XLE_ENTITY_PROFILES
WHERE legal_entity_id = l_legal_entity_id;
SELECT COUNT(*)
INTO l_count
FROM xle_associations ass,
xle_association_types typ
WHERE typ.association_type_id = ass.association_type_id
AND typ.context = 'RELATED_LEGAL_ENTITIES'
AND Nvl(ass.effective_to,SYSDATE) >= SYSDATE
AND ((ass.object_id = p_legal_entity_id1
AND ass.subject_id = p_legal_entity_id2)
OR (ass.object_id = p_legal_entity_id2
AND ass.subject_id = p_legal_entity_id1));
SELECT etbp.party_id
INTO x_me_party_id
FROM XLE_ETB_PROFILES etbp
WHERE etbp.main_establishment_flag = 'Y'
AND etbp.legal_entity_id = p_legal_entity_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(main_effective_from,sysdate))
AND TRUNC(NVL(main_effective_to,sysdate));
SELECT reg.registration_number,jur.legislative_cat_code
FROM XLE_ETB_PROFILES etbp,
XLE_REGISTRATIONS reg,
XLE_JURISDICTIONS_VL jur
WHERE etbp.establishment_id = reg.source_id
AND trunc(reg.source_table) = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND etbp.party_id = p_party_id;
SELECT etbp.etb_information1 activity_code,
fndlookup.lookup_type class_category,
fndlookup.lookup_code class_code,
fndlookup.meaning meaning
FROM XLE_ETB_PROFILES etbp,
XLE_LOOKUPS fndlookup
WHERE etbp.party_id = p_party_id
AND fndlookup.lookup_code = etbp.etb_information1
AND NVL(fndlookup.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
AND NVL(fndlookup.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
AND fndlookup.ENABLED_FLAG = 'Y'
AND fndlookup.LOOKUP_TYPE IN
(select class_category
from hz_class_categories)
UNION
SELECT etbp.etb_information2 sub_activity_code,
fndlookup.lookup_type class_category,
fndlookup.lookup_code class_code,
fndlookup.meaning meaning
FROM XLE_ETB_PROFILES etbp,
XLE_LOOKUPS fndlookup
WHERE etbp.party_id = p_party_id
AND fndlookup.lookup_code = etbp.etb_information2
AND NVL(fndlookup.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
AND NVL(fndlookup.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
AND fndlookup.ENABLED_FLAG = 'Y'
AND fndlookup.LOOKUP_TYPE IN
(select class_category
from hz_class_categories)
;
SELECT legal_entity_id,
legal_entity_name
INTO l_legal_entity_id,
l_legal_entity_name
FROM GL_LEDGER_LE_BSV_SPECIFIC_V
WHERE ledger_id = p_ledger_id
AND segment_value = p_bsv;
SELECT legal_entity_id,
legal_entity_name
INTO l_legal_entity_id,
l_legal_entity_name
FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = p_ledger_id
AND bal_seg_value = p_bsv;
select count(legal_entity_id) into l_cnt from xle_entity_profiles
where name = P_INTERFACE_VALUE;
select count(legal_entity_id) into l_cnt
from XLE_ETB_PROFILES
where name = P_INTERFACE_VALUE;
select etb.legal_entity_id into x_legal_entity_id
from XLE_ETB_PROFILES etb, HZ_PARTIES parties
where parties.party_name = P_INTERFACE_VALUE
and parties.party_id = etb.party_id;
select legal_entity_id into x_legal_entity_id
from XLE_ETB_PROFILES
where name = P_INTERFACE_VALUE;
select legal_entity_id into x_legal_entity_id
from XLE_ENTITY_PROFILES
where name = P_INTERFACE_VALUE;
select ent.LEGAL_ENTITY_ID into x_legal_entity_id
from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
where jur.LEGISLATIVE_CAT_CODE = P_INTERFACE_VALUE
and jur.JURISDICTION_ID = reg.JURISDICTION_ID
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
select count(ent.legal_entity_id) into l_cnt from
XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
select etb.legal_entity_id into x_legal_entity_id
from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
select ent.legal_entity_id into x_legal_entity_id from
XLE_ENTITY_PROFILES ent,XLE_REGISTRATIONS reg
where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
select ent.LEGAL_ENTITY_ID into x_legal_entity_id
from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
where jur.GEOGRAPHY_ID = TO_NUMBER(P_INTERFACE_VALUE)
and jur.JURISDICTION_ID = reg.JURISDICTION_ID
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
select count(ent.legal_entity_id) into l_cnt
from XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
select etb.legal_entity_id into x_legal_entity_id
from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
select ent.legal_entity_id into x_legal_entity_id from
XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
SELECT NVL(O3.ORG_INFORMATION2,-1)
INTO DLC_VAL
FROM HR_ALL_ORGANIZATION_UNITS O
, HR_ORGANIZATION_INFORMATION O2
, HR_ORGANIZATION_INFORMATION O3
WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND O.ORGANIZATION_ID = P_OPERATING_UNIT;
SELECT COUNT(*)
INTO l_le_count
FROM XLE_FP_OU_LEDGER_V
where OPERATING_UNIT_ID = nvl(fnd_profile.value_wnps('ORG_ID'),-99);
SELECT legal_entity_name
INTO l_legal_entity_name
FROM XLE_FP_OU_LEDGER_V
WHERE OPERATING_UNIT_ID = nvl(fnd_profile.value_wnps('ORG_ID'),-99);
SELECT NAME
INTO l_legal_entity_name
FROM XLE_FIRSTPARTY_INFORMATION_V
WHERE LEGAL_ENTITY_ID = Get_DefaultLegalContext_OU(fnd_profile.value_wnps('ORG_ID'));
SELECT 'Y'
INTO l_le_flag
FROM XLE_ENTITY_PROFILES
WHERE legal_entity_id = p_legal_entity_id
AND ( effective_to >= sysdate OR effective_to is null);
SELECT
count(reg_func.function_code)
INTO l_count
FROM
xle_reg_functions reg_func,
xle_registrations reg,
xle_lookups lkp
WHERE
lkp.lookup_type = 'XLE_LE_FUNCTION'
AND lkp.lookup_code = reg_func.function_code
AND lkp.lookup_code = 'ICINV'
AND reg.source_id = p_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.registration_id = reg_func.registration_id;
SELECT
count(reg_func.function_code)
INTO l_count
FROM
xle_reg_functions reg_func,
xle_registrations reg,
xle_lookups lkp ,
xle_entity_profiles ent_prof
WHERE
lkp.lookup_type = 'XLE_LE_FUNCTION'
AND lkp.lookup_code = reg_func.function_code
AND lkp.lookup_code = 'ICINV'
AND reg.registration_id = reg_func.registration_id
AND reg.source_id = ent_prof.legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND ent_prof.party_id = p_party_id;