eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JL_BR_SPED_DATA_EXTRACT_PKG dependencies on XLE_REGISTRATIONS

Line 1543: l_cnpj xle_registrations.registration_number%TYPE;

1539: END get_participant_code;
1540:
1541: PROCEDURE register_0000 AS
1542: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_0000';
1543: l_cnpj xle_registrations.registration_number%TYPE;
1544: l_state hr_locations.region_2%TYPE;
1545: l_state_inscription xle_registrations.registration_number%TYPE;
1546: l_municipal_inscription xle_registrations.registration_number%TYPE;
1547: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;

Line 1545: l_state_inscription xle_registrations.registration_number%TYPE;

1541: PROCEDURE register_0000 AS
1542: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_0000';
1543: l_cnpj xle_registrations.registration_number%TYPE;
1544: l_state hr_locations.region_2%TYPE;
1545: l_state_inscription xle_registrations.registration_number%TYPE;
1546: l_municipal_inscription xle_registrations.registration_number%TYPE;
1547: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;
1548:
1549: BEGIN

Line 1546: l_municipal_inscription xle_registrations.registration_number%TYPE;

1542: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_0000';
1543: l_cnpj xle_registrations.registration_number%TYPE;
1544: l_state hr_locations.region_2%TYPE;
1545: l_state_inscription xle_registrations.registration_number%TYPE;
1546: l_municipal_inscription xle_registrations.registration_number%TYPE;
1547: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;
1548:
1549: BEGIN
1550:

Line 1563: xle_registrations reg,

1559:
1560: SELECT loc.region_2, le.le_information4 --retrieve state and ibge_city_code
1561: INTO l_state,l_ibge_city_code
1562: FROM xle_entity_profiles le,
1563: xle_registrations reg,
1564: hr_locations loc
1565: WHERE le.legal_entity_id = g_legal_entity_id
1566: AND reg.source_id = le.legal_entity_id
1567: AND reg.source_table = 'XLE_ENTITY_PROFILES'

Line 1587: FROM xle_registrations reg,

1583: BEGIN -- retrieving cnpj
1584:
1585: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
1586: INTO l_cnpj
1587: FROM xle_registrations reg,
1588: xle_jurisdictions_vl jur
1589: WHERE reg.source_id = g_legal_entity_id
1590: AND reg.source_table = 'XLE_ENTITY_PROFILES'
1591: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 1614: FROM xle_registrations reg,

1610: IF g_inscription_source ='XLE' THEN
1611: BEGIN --Retrieving state inscription
1612: SELECT registration_number
1613: INTO l_state_inscription
1614: FROM xle_registrations reg,
1615: xle_jurisdictions_vl jur
1616: WHERE reg.source_id = g_legal_entity_id
1617: AND reg.source_table = 'XLE_ENTITY_PROFILES'
1618: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 1634: FROM xle_registrations reg,

1630: END;
1631: BEGIN -- --Retrieving municipal inscription
1632: SELECT registration_number
1633: INTO l_municipal_inscription
1634: FROM xle_registrations reg,
1635: xle_jurisdictions_vl jur
1636: WHERE reg.source_id = g_legal_entity_id
1637: AND reg.source_table = 'XLE_ENTITY_PROFILES'
1638: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 1714: xle_registrations reg,

1710: --retrieving state,ibge_city_code
1711: SELECT loc.region_2, etb.etb_information4
1712: INTO l_state,l_ibge_city_code
1713: FROM xle_etb_profiles etb,
1714: xle_registrations reg,
1715: hr_locations loc
1716: WHERE etb.legal_entity_id = g_legal_entity_id
1717: AND etb.establishment_id = g_establishment_id
1718: AND reg.source_id = etb.establishment_id

Line 1739: FROM xle_registrations reg,

1735: BEGIN
1736: --retrieving cnpj
1737: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
1738: INTO l_cnpj
1739: FROM xle_registrations reg,
1740: xle_jurisdictions_vl jur
1741: WHERE reg.source_id = g_establishment_id
1742: AND reg.source_table = 'XLE_ETB_PROFILES'
1743: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 1768: FROM xle_registrations reg,

1764: IF g_inscription_source ='XLE' THEN
1765: BEGIN
1766: SELECT registration_number
1767: INTO l_state_inscription
1768: FROM xle_registrations reg,
1769: xle_jurisdictions_vl jur
1770: WHERE reg.source_id = g_establishment_id
1771: AND reg.source_table = 'XLE_ETB_PROFILES'
1772: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 1788: FROM xle_registrations reg,

1784: END;
1785: BEGIN --Retrieving municipal inscription
1786: SELECT registration_number
1787: INTO l_municipal_inscription
1788: FROM xle_registrations reg,
1789: xle_jurisdictions_vl jur
1790: WHERE reg.source_id = g_establishment_id
1791: AND reg.source_table = 'XLE_ETB_PROFILES'
1792: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2048: FROM xle_registrations reg

2044: ,g_creation_date
2045: ,g_last_updated_by
2046: ,g_last_update_date
2047: ,g_last_update_login
2048: FROM xle_registrations reg
2049: WHERE reg.source_id = g_legal_entity_id
2050: AND reg.source_table = 'XLE_ENTITY_PROFILES'
2051: AND reg.reg_information1 IS NOT NULL
2052: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.

Line 2052: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.

2048: FROM xle_registrations reg
2049: WHERE reg.source_id = g_legal_entity_id
2050: AND reg.source_table = 'XLE_ENTITY_PROFILES'
2051: AND reg.reg_information1 IS NOT NULL
2052: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.
2053: AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
2054:
2055: ELSE --Establishment acts as company
2056:

Line 2087: FROM xle_registrations reg

2083: ,g_creation_date
2084: ,g_last_updated_by
2085: ,g_last_update_date
2086: ,g_last_update_login
2087: FROM xle_registrations reg
2088: WHERE reg.source_id = g_establishment_id
2089: AND reg.source_table = 'XLE_ETB_PROFILES'
2090: AND reg.reg_information1 IS NOT NULL
2091: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.

Line 2091: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.

2087: FROM xle_registrations reg
2088: WHERE reg.source_id = g_establishment_id
2089: AND reg.source_table = 'XLE_ETB_PROFILES'
2090: AND reg.reg_information1 IS NOT NULL
2091: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date --effective_from column can be null in xle_registrations table.
2092: AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
2093:
2094: END IF; -- End for IF l_estb_acts_as_company='N'
2095:

Line 2122: l_cnpj xle_registrations.registration_number%TYPE;

2118:
2119: PROCEDURE register_0020 AS
2120: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_0020';
2121: l_main_estb_flag VARCHAR2(1);
2122: l_cnpj xle_registrations.registration_number%TYPE;
2123: l_state hr_locations.region_2%TYPE;
2124: l_state_inscription xle_registrations.registration_number%TYPE;
2125: l_municipal_inscription xle_registrations.registration_number%TYPE;
2126: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;

Line 2124: l_state_inscription xle_registrations.registration_number%TYPE;

2120: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_0020';
2121: l_main_estb_flag VARCHAR2(1);
2122: l_cnpj xle_registrations.registration_number%TYPE;
2123: l_state hr_locations.region_2%TYPE;
2124: l_state_inscription xle_registrations.registration_number%TYPE;
2125: l_municipal_inscription xle_registrations.registration_number%TYPE;
2126: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;
2127: l_nire NUMBER(20); --check the data type
2128: l_establishment_id xle_etb_profiles.establishment_id%TYPE;

Line 2125: l_municipal_inscription xle_registrations.registration_number%TYPE;

2121: l_main_estb_flag VARCHAR2(1);
2122: l_cnpj xle_registrations.registration_number%TYPE;
2123: l_state hr_locations.region_2%TYPE;
2124: l_state_inscription xle_registrations.registration_number%TYPE;
2125: l_municipal_inscription xle_registrations.registration_number%TYPE;
2126: l_ibge_city_code xle_entity_profiles.le_information4%TYPE;
2127: l_nire NUMBER(20); --check the data type
2128: l_establishment_id xle_etb_profiles.establishment_id%TYPE;
2129: l_count NUMBER := 0;

Line 2184: xle_registrations reg,

2180:
2181: SELECT loc.region_2, le.le_information4
2182: INTO l_state,l_ibge_city_code
2183: FROM xle_entity_profiles le,
2184: xle_registrations reg,
2185: hr_locations loc
2186: WHERE le.legal_entity_id = g_legal_entity_id
2187: AND reg.source_id = le.legal_entity_id
2188: AND reg.source_table = 'XLE_ENTITY_PROFILES'

Line 2208: FROM xle_registrations reg,

2204: BEGIN -- retrieving cnpj
2205:
2206: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
2207: INTO l_cnpj
2208: FROM xle_registrations reg,
2209: xle_jurisdictions_vl jur
2210: WHERE reg.source_id = g_legal_entity_id
2211: AND reg.source_table = 'XLE_ENTITY_PROFILES'
2212: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2236: FROM xle_registrations

2232: otherwise, we need to retrieve the registration number with legislative category as 'COMMERCIAL_LAW'. */
2233:
2234: SELECT count(*)
2235: INTO l_count
2236: FROM xle_registrations
2237: WHERE source_id = g_legal_entity_id
2238: AND source_table = 'XLE_ENTITY_PROFILES'
2239: AND UPPER(place_of_registration) = 'NIRE'
2240: AND nvl(effective_from ,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 2247: FROM xle_registrations

2243: IF l_count > 0 THEN
2244: BEGIN
2245: SELECT registration_number
2246: INTO l_nire
2247: FROM xle_registrations
2248: WHERE source_id = g_legal_entity_id
2249: AND source_table = 'XLE_ENTITY_PROFILES'
2250: AND UPPER(place_of_registration) = 'NIRE'
2251: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 2267: FROM xle_registrations reg,

2263:
2264: BEGIN
2265: SELECT registration_number
2266: INTO l_nire
2267: FROM xle_registrations reg,
2268: xle_jurisdictions_vl jur
2269: WHERE source_id = g_legal_entity_id
2270: AND source_table = 'XLE_ENTITY_PROFILES'
2271: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2293: FROM xle_registrations reg,

2289: IF g_inscription_source ='XLE' THEN
2290: BEGIN
2291: SELECT registration_number
2292: INTO l_state_inscription
2293: FROM xle_registrations reg,
2294: xle_jurisdictions_vl jur
2295: WHERE reg.source_id = g_legal_entity_id
2296: AND reg.source_table = 'XLE_ENTITY_PROFILES'
2297: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2313: FROM xle_registrations reg,

2309: END;
2310: BEGIN -- --Retrieving municipal inscription
2311: SELECT registration_number
2312: INTO l_municipal_inscription
2313: FROM xle_registrations reg,
2314: xle_jurisdictions_vl jur
2315: WHERE reg.source_id = g_legal_entity_id
2316: AND reg.source_table = 'XLE_ENTITY_PROFILES'
2317: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2469: xle_registrations reg,

2465: --retrieving cnpj,state,ibge_city_code
2466: SELECT loc.region_2, etb.etb_information4
2467: INTO l_state,l_ibge_city_code
2468: FROM xle_etb_profiles etb,
2469: xle_registrations reg,
2470: hr_locations loc
2471: WHERE etb.legal_entity_id = g_legal_entity_id
2472: AND etb.establishment_id = l_establishment_id
2473: AND reg.source_id = etb.establishment_id

Line 2495: FROM xle_registrations

2491: otherwise, we need to retrieve the registration number with legislative category as 'COMMERCIAL_LAW'. */
2492:
2493: SELECT count(*)
2494: INTO l_count
2495: FROM xle_registrations
2496: WHERE source_id = l_establishment_id
2497: AND source_table = 'XLE_ETB_PROFILES'
2498: AND UPPER(place_of_registration) = 'NIRE'
2499: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 2506: FROM xle_registrations

2502: IF l_count > 0 THEN
2503: BEGIN
2504: SELECT registration_number
2505: INTO l_nire
2506: FROM xle_registrations
2507: WHERE source_id = l_establishment_id
2508: AND source_table = 'XLE_ETB_PROFILES'
2509: AND UPPER(place_of_registration) = 'NIRE'
2510: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 2526: FROM xle_registrations reg,

2522:
2523: BEGIN
2524: SELECT registration_number
2525: INTO l_nire
2526: FROM xle_registrations reg,
2527: xle_jurisdictions_vl jur
2528: WHERE source_id = l_establishment_id
2529: AND source_table = 'XLE_ETB_PROFILES'
2530: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2551: FROM xle_registrations reg,

2547: BEGIN
2548: --retrieving cnpj
2549: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
2550: INTO l_cnpj
2551: FROM xle_registrations reg,
2552: xle_jurisdictions_vl jur
2553: WHERE reg.source_id = l_establishment_id
2554: AND reg.source_table = 'XLE_ETB_PROFILES'
2555: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2580: FROM xle_registrations reg,

2576: IF g_inscription_source ='XLE' THEN
2577: BEGIN
2578: SELECT registration_number
2579: INTO l_state_inscription
2580: FROM xle_registrations reg,
2581: xle_jurisdictions_vl jur
2582: WHERE reg.source_id = l_establishment_id
2583: AND reg.source_table = 'XLE_ETB_PROFILES'
2584: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 2600: FROM xle_registrations reg,

2596: END;
2597: BEGIN --Retrieving municipal inscription
2598: SELECT registration_number
2599: INTO l_municipal_inscription
2600: FROM xle_registrations reg,
2601: xle_jurisdictions_vl jur
2602: WHERE reg.source_id = l_establishment_id
2603: AND reg.source_table = 'XLE_ETB_PROFILES'
2604: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 3309: l_cnpj xle_registrations.registration_number%TYPE;

3305: l_api_name CONSTANT VARCHAR2(30) :='REGISTER_I030';
3306: l_book_number jl_br_cinfos_books.book_number%TYPE;
3307: l_book_name jl_br_cinfos_books.book_name%TYPE;
3308: l_nire NUMBER(11);
3309: l_cnpj xle_registrations.registration_number%TYPE;
3310: l_registration_id xle_registrations.registration_id%TYPE;
3311: l_effective_from DATE;
3312: l_conversion_date DATE;
3313: l_city hr_locations.town_or_city%TYPE;

Line 3310: l_registration_id xle_registrations.registration_id%TYPE;

3306: l_book_number jl_br_cinfos_books.book_number%TYPE;
3307: l_book_name jl_br_cinfos_books.book_name%TYPE;
3308: l_nire NUMBER(11);
3309: l_cnpj xle_registrations.registration_number%TYPE;
3310: l_registration_id xle_registrations.registration_id%TYPE;
3311: l_effective_from DATE;
3312: l_conversion_date DATE;
3313: l_city hr_locations.town_or_city%TYPE;
3314: l_count NUMBER(4) :=0;

Line 3353: FROM xle_registrations

3349: otherwise, we need to retrieve the registration number with legislative category as 'COMMERCIAL_LAW'. */
3350:
3351: SELECT count(*)
3352: INTO l_count
3353: FROM xle_registrations
3354: WHERE source_id = g_legal_entity_id
3355: AND source_table = 'XLE_ENTITY_PROFILES'
3356: AND UPPER(place_of_registration) = 'NIRE'
3357: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 3364: FROM xle_registrations

3360: IF l_count > 0 THEN
3361: BEGIN
3362: SELECT registration_number
3363: INTO l_nire
3364: FROM xle_registrations
3365: WHERE source_id = g_legal_entity_id
3366: AND source_table = 'XLE_ENTITY_PROFILES'
3367: AND UPPER(place_of_registration) = 'NIRE'
3368: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 3386: FROM xle_registrations reg,

3382:
3383: BEGIN
3384: SELECT registration_number
3385: INTO l_nire
3386: FROM xle_registrations reg,
3387: xle_jurisdictions_vl jur
3388: WHERE source_id = g_legal_entity_id
3389: AND source_table = 'XLE_ENTITY_PROFILES'
3390: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 3413: FROM xle_registrations reg,hr_locations_all loc

3409: BEGIN
3410: --retriving city
3411: SELECT loc.town_or_city
3412: INTO l_city
3413: FROM xle_registrations reg,hr_locations_all loc
3414: WHERE reg.source_id = g_legal_entity_id
3415: AND reg.source_table = 'XLE_ENTITY_PROFILES'
3416: AND reg.identifying_flag = 'Y'
3417: AND reg.location_id =loc.location_id

Line 3433: FROM xle_registrations reg,

3429: BEGIN -- retrieving cnpj
3430:
3431: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789'),reg.registration_id
3432: INTO l_cnpj,l_registration_id
3433: FROM xle_registrations reg,
3434: xle_jurisdictions_vl jur
3435: WHERE reg.source_id = g_legal_entity_id
3436: AND reg.source_table = 'XLE_ENTITY_PROFILES'
3437: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 3459: WHERE source_table = 'XLE_REGISTRATIONS'

3455:
3456: SELECT count(*)
3457: INTO l_count
3458: FROM xle_histories his
3459: WHERE source_table = 'XLE_REGISTRATIONS'
3460: AND source_id = l_registration_id
3461: AND source_column_name = 'EFFECTIVE_FROM';
3462:
3463: IF l_count >= 1 THEN -- registration Effective From field was changed. so entry exists in history table.

Line 3469: WHERE source_table = 'XLE_REGISTRATIONS'

3465: BEGIN
3466: SELECT substr(source_column_value,1,11)
3467: INTO l_conversion_date
3468: FROM xle_histories his
3469: WHERE source_table = 'XLE_REGISTRATIONS'
3470: AND source_id = l_registration_id
3471: AND source_column_name = 'EFFECTIVE_FROM'
3472: AND effective_to IS NULL;
3473:

Line 3516: FROM xle_registrations

3512: otherwise, we need to retrieve the registration number with legislative category as 'COMMERCIAL_LAW'. */
3513:
3514: SELECT count(*)
3515: INTO l_count
3516: FROM xle_registrations
3517: WHERE source_id = g_establishment_id
3518: AND source_table = 'XLE_ETB_PROFILES'
3519: AND UPPER(place_of_registration) = 'NIRE'
3520: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 3527: FROM xle_registrations

3523: IF l_count > 0 THEN
3524: BEGIN
3525: SELECT registration_number
3526: INTO l_nire
3527: FROM xle_registrations
3528: WHERE source_id = g_establishment_id
3529: AND source_table = 'XLE_ETB_PROFILES'
3530: AND UPPER(place_of_registration) = 'NIRE'
3531: AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date

Line 3548: FROM xle_registrations reg,

3544:
3545: BEGIN
3546: SELECT registration_number
3547: INTO l_nire
3548: FROM xle_registrations reg,
3549: xle_jurisdictions_vl jur
3550: WHERE source_id = g_establishment_id
3551: AND source_table = 'XLE_ETB_PROFILES'
3552: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 3574: FROM xle_registrations reg,hr_locations_all loc

3570: BEGIN
3571: --retriving city
3572: SELECT loc.town_or_city
3573: INTO l_city
3574: FROM xle_registrations reg,hr_locations_all loc
3575: WHERE reg.source_id = g_establishment_id
3576: AND reg.source_table = 'XLE_ETB_PROFILES'
3577: AND reg.identifying_flag = 'Y'
3578: AND reg.location_id =loc.location_id

Line 3593: FROM xle_registrations reg,

3589: BEGIN -- retrieving cnpj
3590:
3591: SELECT translate(reg.registration_number,'0123456789/-.', '0123456789'),reg.registration_id
3592: INTO l_cnpj,l_registration_id
3593: FROM xle_registrations reg,
3594: xle_jurisdictions_vl jur
3595: WHERE reg.source_id = g_establishment_id
3596: AND reg.source_table = 'XLE_ETB_PROFILES'
3597: AND reg.jurisdiction_id = jur.jurisdiction_id

Line 3619: WHERE source_table = 'XLE_REGISTRATIONS'

3615:
3616: SELECT count(*)
3617: INTO l_count
3618: FROM xle_histories his
3619: WHERE source_table = 'XLE_REGISTRATIONS'
3620: AND source_id = l_registration_id
3621: AND source_column_name = 'EFFECTIVE_FROM';
3622:
3623: IF l_count >= 1 THEN -- registration Effective From field was changed. so entry exists in history table.

Line 3629: WHERE source_table = 'XLE_REGISTRATIONS'

3625: BEGIN
3626: SELECT substr(source_column_value,1,11)
3627: INTO l_conversion_date
3628: FROM xle_histories his
3629: WHERE source_table = 'XLE_REGISTRATIONS'
3630: AND source_id = l_registration_id
3631: AND source_column_name = 'EFFECTIVE_FROM'
3632: AND effective_to IS NULL;
3633: