[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 DISTINCT 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'
1923: l_commit VARCHAR2(100);
1924: CURSOR regnum_c IS
1925: SELECT reg.registration_number,jur.legislative_cat_code
1926: FROM XLE_ETB_PROFILES etbp,
1927: XLE_REGISTRATIONS reg,
1928: XLE_JURISDICTIONS_VL jur
1929: WHERE etbp.establishment_id = reg.source_id
1930: AND trunc(reg.source_table) = 'XLE_ETB_PROFILES'
1931: AND reg.jurisdiction_id = jur.jurisdiction_id
2339:
2340: IF P_INTERFACE_ATTRIBUTE = 'LEGISLATIVE_CAT' THEN
2341:
2342: select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2343: from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2344: where jur.LEGISLATIVE_CAT_CODE = P_INTERFACE_VALUE
2345: and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2346: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2347:
2354:
2355: /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2356:
2357: select count(ent.legal_entity_id) into l_cnt from
2358: XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2359: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2360: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2361:
2362: IF l_cnt = 0 THEN
2360: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2361:
2362: IF l_cnt = 0 THEN
2363: select etb.legal_entity_id into x_legal_entity_id
2364: from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2365: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2366: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2367: ELSE
2368: select ent.legal_entity_id into x_legal_entity_id from
2365: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2366: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2367: ELSE
2368: select ent.legal_entity_id into x_legal_entity_id from
2369: XLE_ENTITY_PROFILES ent,XLE_REGISTRATIONS reg
2370: where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2371: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2372: END IF;
2373:
2377:
2378: IF P_INTERFACE_ATTRIBUTE = 'GEOGRAPHY_ID' THEN
2379:
2380: select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2381: from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2382: where jur.GEOGRAPHY_ID = TO_NUMBER(P_INTERFACE_VALUE)
2383: and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2384: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2385:
2390: IF P_INTERFACE_ATTRIBUTE = 'LOCATION_ID' THEN
2391:
2392: /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2393: select count(ent.legal_entity_id) into l_cnt
2394: from XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2395: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2396: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2397:
2398: IF l_cnt = 0 THEN
2396: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2397:
2398: IF l_cnt = 0 THEN
2399: select etb.legal_entity_id into x_legal_entity_id
2400: from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2401: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2402: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2403: ELSE
2404: select ent.legal_entity_id into x_legal_entity_id from
2401: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2402: and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2403: ELSE
2404: select ent.legal_entity_id into x_legal_entity_id from
2405: XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2406: where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2407: and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2408: END IF;
2409: END IF;
2763: count(reg_func.function_code)
2764: INTO l_count
2765: FROM
2766: xle_reg_functions reg_func,
2767: xle_registrations reg,
2768: xle_lookups lkp
2769: WHERE
2770: lkp.lookup_type = 'XLE_LE_FUNCTION'
2771: AND lkp.lookup_code = reg_func.function_code
2791: count(reg_func.function_code)
2792: INTO l_count
2793: FROM
2794: xle_reg_functions reg_func,
2795: xle_registrations reg,
2796: xle_lookups lkp ,
2797: xle_entity_profiles ent_prof
2798: WHERE
2799: lkp.lookup_type = 'XLE_LE_FUNCTION'