[Home] [Help]
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,
30: p_adjustment_period_name GL_PERIOD_STATUSES.PERIOD_NAME%TYPE,
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,
215: p_adjustment_period_name GL_PERIOD_STATUSES.PERIOD_NAME%TYPE,
352: ELSE -- Establishment acts like company
353:
354: SELECT name
355: INTO g_company_name
356: FROM xle_etb_profiles
357: WHERE establishment_id = g_establishment_id
358: AND legal_entity_id = g_legal_entity_id ;
359:
360: END IF;
1653:
1654: SELECT reg.registration_number
1655: INTO l_state_inscription
1656: FROM zx_registrations reg,
1657: xle_etb_profiles etb,
1658: zx_party_tax_profile ptp
1659: WHERE etb.legal_entity_id = g_legal_entity_id
1660: AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
1661: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
1679:
1680: SELECT reg.registration_number
1681: INTO l_municipal_inscription
1682: FROM zx_registrations reg,
1683: xle_etb_profiles etb,
1684: zx_party_tax_profile ptp
1685: WHERE etb.legal_entity_id = g_legal_entity_id
1686: AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
1687: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
1709: BEGIN
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
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
1719: AND reg.source_table = 'XLE_ETB_PROFILES'
1720: AND reg.identifying_flag = 'Y'
1721: AND reg.location_id = loc.location_id
1722: AND rownum=1;
1723:
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
1744: AND jur.registration_code_etb = 'CNPJ'
1745: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1746: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
1773: AND jur.registration_code_etb = g_le_state_reg_code
1774: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1775: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
1793: AND jur.registration_code_etb = g_le_municipal_reg_code
1794: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
1795: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
1807:
1808: SELECT reg.registration_number
1809: INTO l_state_inscription
1810: FROM zx_registrations reg,
1811: xle_etb_profiles etb,
1812: zx_party_tax_profile ptp
1813: WHERE etb.legal_entity_id = g_legal_entity_id
1814: AND etb.establishment_id = g_establishment_id
1815: AND etb.party_id = ptp.party_id
1831:
1832: SELECT reg.registration_number
1833: INTO l_municipal_inscription
1834: FROM zx_registrations reg,
1835: xle_etb_profiles etb,
1836: zx_party_tax_profile ptp
1837: WHERE etb.legal_entity_id = g_legal_entity_id
1838: AND etb.establishment_id = g_establishment_id
1839: AND etb.party_id = ptp.party_id
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.
2092: AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
2093:
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
2131: FROM xle_etb_profiles
2132: WHERE legal_entity_id = g_legal_entity_id
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
2131: FROM xle_etb_profiles
2132: WHERE legal_entity_id = g_legal_entity_id
2133: --AND main_establishment_flag='N'
2134: AND establishment_id NOT IN (SELECT establishment_id
2135: FROM xle_etb_profiles
2131: FROM xle_etb_profiles
2132: WHERE legal_entity_id = g_legal_entity_id
2133: --AND main_establishment_flag='N'
2134: AND establishment_id NOT IN (SELECT establishment_id
2135: FROM xle_etb_profiles
2136: WHERE legal_entity_id = g_legal_entity_id
2137: AND main_establishment_flag = 'Y'
2138: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE))
2139: AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE)))
2155: BEGIN
2156:
2157: SELECT 'Y'
2158: INTO l_main_estb_flag
2159: FROM xle_etb_profiles
2160: WHERE legal_entity_id = g_legal_entity_id
2161: AND establishment_id = g_establishment_id
2162: AND main_establishment_flag = 'Y'
2163: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE))
2332:
2333: SELECT reg.registration_number
2334: INTO l_state_inscription
2335: FROM zx_registrations reg,
2336: xle_etb_profiles etb,
2337: zx_party_tax_profile ptp
2338: WHERE etb.legal_entity_id = g_legal_entity_id
2339: AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
2340: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
2358:
2359: SELECT reg.registration_number
2360: INTO l_municipal_inscription
2361: FROM zx_registrations reg,
2362: xle_etb_profiles etb,
2363: zx_party_tax_profile ptp
2364: WHERE etb.legal_entity_id = g_legal_entity_id
2365: AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
2366: AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
2464: BEGIN
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
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
2474: AND reg.source_table = 'XLE_ETB_PROFILES'
2475: AND reg.identifying_flag = 'Y'
2476: AND reg.location_id = loc.location_id
2477: AND rownum = 1;
2478:
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
2500: AND (effective_to IS NULL OR effective_to >= g_end_date);
2501:
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
2511: AND (effective_to IS NULL OR effective_to >= g_end_date)
2512: AND rownum = 1;
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
2531: AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
2532: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2533: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2556: AND jur.registration_code_etb = 'CNPJ'
2557: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2558: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2585: AND jur.registration_code_etb = g_le_state_reg_code
2586: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2587: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
2605: AND jur.registration_code_etb = g_le_municipal_reg_code
2606: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
2607: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
2619:
2620: SELECT reg.registration_number
2621: INTO l_state_inscription
2622: FROM zx_registrations reg,
2623: xle_etb_profiles etb,
2624: zx_party_tax_profile ptp
2625: WHERE etb.legal_entity_id = g_legal_entity_id
2626: AND etb.establishment_id = l_establishment_id -- will fetch the registration number of main establishment for LE in case of EBtax.
2627: AND etb.party_id = ptp.party_id
2643:
2644: SELECT reg.registration_number
2645: INTO l_municipal_inscription
2646: FROM zx_registrations reg,
2647: xle_etb_profiles etb,
2648: zx_party_tax_profile ptp
2649: WHERE etb.legal_entity_id = g_legal_entity_id
2650: AND etb.establishment_id = l_establishment_id -- will fetch the registration number of main establishment for LE in case of EBtax.
2651: AND etb.party_id = ptp.party_id
3488: BEGIN
3489: --retreiving book info
3490: SELECT etb.effective_from,bk.book_number,bk.book_name
3491: INTO l_effective_from,l_book_number,l_book_name
3492: FROM xle_etb_profiles etb,
3493: jl_br_cinfos_books bk
3494: WHERE etb.legal_entity_id = g_legal_entity_id
3495: AND etb.establishment_id = g_establishment_id
3496: AND bk.legal_entity_id = etb.legal_entity_id
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
3521: AND (effective_to IS NULL OR effective_to >= g_end_date) ;
3522:
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
3532: AND (effective_to IS NULL OR effective_to >= g_end_date)
3533: AND rownum = 1;
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
3553: AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
3554: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
3555: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
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
3579: AND rownum = 1 ;
3580:
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
3598: AND jur.registration_code_le = 'CNPJ'
3599: AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
3600: AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
4168: IF UPPER(g_accounting_type) = 'CENTRALIZED' AND g_establishment_id IS NOT NULL THEN
4169:
4170: SELECT etb_information5
4171: INTO l_institution_resp_code
4172: FROM xle_etb_profiles
4173: WHERE legal_entity_id = g_legal_entity_id
4174: AND establishment_id = g_establishment_id;
4175:
4176: ELSE
6718: g_creation_date,
6719: g_last_updated_by,
6720: g_last_update_date,
6721: g_last_update_login
6722: from xle_etb_profiles etb,
6723: xle_contact_legal_roles crole,
6724: hz_parties hp,
6725: xle_lookups lk
6726: where etb.legal_entity_id =g_legal_entity_id
6725: xle_lookups lk
6726: where etb.legal_entity_id =g_legal_entity_id
6727: and etb.establishment_id =g_establishment_id
6728: and etb.party_id = crole.le_etb_party_id
6729: and crole.source_table = 'XLE_ETB_PROFILES'
6730: and crole.lookup_type = 'XLE_CONTACT_ROLE'
6731: and crole.contact_party_id = hp.party_id
6732: and lk.lookup_type = crole.lookup_type
6733: and lk.lookup_code = crole.lookup_code;
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,
8507: p_is_special_situation_dummy VARCHAR2,
8508: p_is_special_situation_dummy1 VARCHAR2,
8509: p_period_type VARCHAR2,