[Home] [Help]
60: hrl.region_3,
61: hrl.postal_code,
62: hrl.country
63: FROM XLE_ENTITY_PROFILES lep,
64: XLE_REGISTRATIONS reg,
65: HR_LOCATIONS_ALL hrl,
66: XLE_JURISDICTIONS_VL jur
67: WHERE
68: lep.legal_entity_id = reg.source_id
109: hrl.region_3,
110: hrl.postal_code,
111: hrl.country
112: FROM XLE_ETB_PROFILES etb,
113: XLE_REGISTRATIONS reg,
114: HR_LOCATIONS_ALL hrl,
115: XLE_JURISDICTIONS_VL jur
116: WHERE
117: etb.establishment_id = reg.source_id
810: l_source_table := 'XLE_ENTITY_PROFILES';
811: ELSIF P_ENTITY_TYPE = 'ESTABLISHMENT' THEN
812: l_source_table := 'XLE_ETB_PROFILES';
813: ELSIF P_ENTITY_TYPE = 'REGISTRATIONS' THEN
814: l_source_table := 'XLE_REGISTRATIONS';
815: END IF;
816:
817: IF P_EFFECTIVE_DATE is not null THEN
818:
1125: SELECT hrl.country
1126: INTO l_country_code
1127: FROM XLE_ENTITY_PROFILES lep,
1128: HR_LOCATIONS_ALL hrl,
1129: XLE_REGISTRATIONS reg
1130: WHERE lep.legal_entity_id = l_legal_entity_id
1131: AND reg.source_id = lep.legal_entity_id
1132: AND reg.source_table = 'XLE_ENTITY_PROFILES'
1133: AND reg.location_id = hrl.location_id;
1258: IF l_legal_entity_id IS NOT NULL THEN
1259: SELECT hrl.country
1260: INTO x_country_code
1261: FROM XLE_ENTITY_PROFILES xlep,
1262: XLE_REGISTRATIONS reg,
1263: HR_LOCATIONS_ALL hrl
1264: WHERE xlep.legal_entity_id = reg.source_id
1265: AND reg.source_table = 'XLE_ENTITY_PROFILES'
1266: AND reg.identifying_flag = 'Y'
1921: l_commit VARCHAR2(100);
1922: CURSOR regnum_c IS
1923: SELECT reg.registration_number,jur.legislative_cat_code
1924: FROM XLE_ETB_PROFILES etbp,
1925: XLE_REGISTRATIONS reg,
1926: XLE_JURISDICTIONS_VL jur
1927: WHERE etbp.establishment_id = reg.source_id
1928: AND trunc(reg.source_table) = 'XLE_ETB_PROFILES'
1929: AND reg.jurisdiction_id = jur.jurisdiction_id
2337:
2338: IF P_INTERFACE_ATTRIBUTE = 'LEGISLATIVE_CAT' THEN
2339:
2340: select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2341: from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2342: where jur.LEGISLATIVE_CAT_CODE = P_INTERFACE_VALUE
2343: and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2344: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2345:
2352:
2353: /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2354:
2355: select count(ent.legal_entity_id) into l_cnt from
2356: XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2357: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2358: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2359:
2360: IF l_cnt = 0 THEN
2358: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2359:
2360: IF l_cnt = 0 THEN
2361: select etb.legal_entity_id into x_legal_entity_id
2362: from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2363: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2364: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2365: ELSE
2366: select ent.legal_entity_id into x_legal_entity_id from
2363: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2364: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2365: ELSE
2366: select ent.legal_entity_id into x_legal_entity_id from
2367: XLE_ENTITY_PROFILES ent,XLE_REGISTRATIONS reg
2368: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2369: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2370: END IF;
2371:
2375:
2376: IF P_INTERFACE_ATTRIBUTE = 'GEOGRAPHY_ID' THEN
2377:
2378: select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2379: from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2380: where jur.GEOGRAPHY_ID = TO_NUMBER(P_INTERFACE_VALUE)
2381: and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2382: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2383:
2388: IF P_INTERFACE_ATTRIBUTE = 'LOCATION_ID' THEN
2389:
2390: /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2391: select count(ent.legal_entity_id) into l_cnt
2392: from XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2393: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2394: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2395:
2396: IF l_cnt = 0 THEN
2394: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2395:
2396: IF l_cnt = 0 THEN
2397: select etb.legal_entity_id into x_legal_entity_id
2398: from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2399: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2400: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2401: ELSE
2402: select ent.legal_entity_id into x_legal_entity_id from
2399: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2400: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2401: ELSE
2402: select ent.legal_entity_id into x_legal_entity_id from
2403: XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2404: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2405: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2406: END IF;
2407: END IF;
2761: count(reg_func.function_code)
2762: INTO l_count
2763: FROM
2764: xle_reg_functions reg_func,
2765: xle_registrations reg,
2766: xle_lookups lkp
2767: WHERE
2768: lkp.lookup_type = 'XLE_LE_FUNCTION'
2769: AND lkp.lookup_code = reg_func.function_code
2789: count(reg_func.function_code)
2790: INTO l_count
2791: FROM
2792: xle_reg_functions reg_func,
2793: xle_registrations reg,
2794: xle_lookups lkp ,
2795: xle_entity_profiles ent_prof
2796: WHERE
2797: lkp.lookup_type = 'XLE_LE_FUNCTION'