[Home] [Help]
21:
22: PROCEDURE initialize( p_ledger_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE,
23: p_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
24: p_accounting_type VARCHAR2,
25: p_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
26: p_establishment_id XLE_ETB_PROFILES.ESTABLISHMENT_ID%TYPE,
27: p_is_special_situation VARCHAR2,
28: p_period_type VARCHAR2,
29: p_period_name GL_PERIOD_STATUSES.PERIOD_NAME%TYPE,
206:
207: PROCEDURE initialize( p_ledger_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE,
208: p_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
209: p_accounting_type VARCHAR2,
210: p_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
211: p_establishment_id XLE_ETB_PROFILES.ESTABLISHMENT_ID%TYPE,
212: p_is_special_situation VARCHAR2,
213: p_period_type VARCHAR2,
214: p_period_name GL_PERIOD_STATUSES.PERIOD_NAME%TYPE,
345: OR (UPPER(g_accounting_type) = 'CENTRALIZED' AND g_establishment_id IS NULL) THEN
346:
347: SELECT name
348: INTO g_company_name
349: FROM xle_entity_profiles
350: WHERE legal_entity_id = g_legal_entity_id;
351:
352: ELSE -- Establishment acts like company
353:
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:
1551: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1558: BEGIN
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
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'
1568: AND reg.identifying_flag = 'Y'
1569: AND reg.location_id = loc.location_id
1570: AND le.transacting_entity_flag = 'Y'
1571: AND rownum=1;
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
1592: AND jur.registration_code_le = 'CNPJ'
1593: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1594: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
1619: AND jur.registration_code_le = g_le_state_reg_code
1620: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1621: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
1639: AND jur.registration_code_le = g_le_municipal_reg_code
1640: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1641: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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.
2053: AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
2054:
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;
2130: CURSOR secondary_estbs_cur IS SELECT establishment_id
2179: BEGIN
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
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'
2189: AND reg.identifying_flag = 'Y'
2190: AND reg.location_id = loc.location_id
2191: AND le.transacting_entity_flag = 'Y'
2192: AND rownum = 1 ;
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
2213: AND jur.registration_code_le = 'CNPJ'
2214: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2215: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2241: AND (effective_to IS NULL OR effective_to >= g_end_date);
2242:
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
2252: AND (effective_to IS NULL OR effective_to >= g_end_date)
2253: AND rownum =1;
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
2272: AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
2273: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2274: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2298: AND jur.registration_code_le = g_le_state_reg_code
2299: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2300: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2318: AND jur.registration_code_le = g_le_municipal_reg_code
2319: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2320: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
3325: BEGIN
3326: --retreiving book info
3327: SELECT le.effective_from,bk.book_number,bk.book_name
3328: INTO l_effective_from,l_book_number,l_book_name
3329: FROM xle_entity_profiles le,
3330: jl_br_cinfos_books bk
3331: WHERE le.legal_entity_id = g_legal_entity_id
3332: AND bk.legal_entity_id = le.legal_entity_id
3333: AND bk.establishment_id IS NULL -- need to retrive the book info of LE.
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
3358: AND (effective_to IS NULL OR effective_to >= g_end_date);
3359:
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
3369: AND (effective_to IS NULL OR effective_to >= g_end_date)
3370: AND rownum=1;
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
3391: AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
3392: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
3393: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
3418: AND rownum = 1 ;
3419:
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
3438: AND jur.registration_code_le = 'CNPJ'
3439: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
3440: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
4176: ELSE
4177:
4178: SELECT le_information5
4179: INTO l_institution_resp_code
4180: FROM xle_entity_profiles
4181: WHERE legal_entity_id = g_legal_entity_id ;
4182:
4183: END IF;
4184:
6663: g_creation_date,
6664: g_last_updated_by,
6665: g_last_update_date,
6666: g_last_update_login
6667: from xle_entity_profiles le,
6668: xle_contact_legal_roles crole,
6669: hz_parties hp,
6670: xle_lookups lk
6671: where le.legal_entity_id =g_legal_entity_id
6669: hz_parties hp,
6670: xle_lookups lk
6671: where le.legal_entity_id =g_legal_entity_id
6672: and le.party_id = crole.le_etb_party_id
6673: and crole.source_table = 'XLE_ENTITY_PROFILES'
6674: and crole.lookup_type = 'XLE_CONTACT_ROLE'
6675: and crole.contact_party_id = hp.party_id
6676: and lk.lookup_type = crole.lookup_type
6677: and lk.lookup_code = crole.lookup_code;
8498:
8499: PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
8500: retcode OUT NOCOPY NUMBER,
8501: p_accounting_type VARCHAR2,
8502: p_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
8503: p_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
8504: p_ledger_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE,
8505: p_establishment_id XLE_ETB_PROFILES.ESTABLISHMENT_ID%TYPE,
8506: p_is_special_situation VARCHAR2,