[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct field6
from jl_br_sped_extr_data_t a
where request_id = g_concurrent_request_id
and field1='I250'
and not exists (select 1
from jl_br_sped_extr_data_t
where request_id = g_concurrent_request_id
and field4 like '%####%'
and field6 = a.field6
and field1='I250')
and substr(field4,1,1) = '-';
select distinct field2 na ,field3 cc
from jl_br_sped_extr_data_t a
where field1 ='I250'
and request_id = g_concurrent_request_id
and not exists (select 1
from jl_br_sped_extr_data_t
where request_id = g_concurrent_request_id
and field1 = 'I250'
and field4 like '%####%'
and field2 = a.field2
and field3 = a.field3)
and substr(field4,1,1) = '-';
update jl_br_sped_extr_data_t a
set field4 = trim(to_char(abs(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),
field5 = decode(field5,'C','D','D','C')
where field1 = 'I250'
and substr(field4,1,1)='-'
and request_id = g_concurrent_request_id
and not exists (select 1
from jl_br_sped_extr_data_t b
where a.field6 = b.field6
and b.field1 = 'I250'
and b.request_id = g_concurrent_request_id
and b.field4 like '%###%');
update jl_br_sped_extr_data_t a -- need to verify
set field4 = (select trim(to_char(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
from jl_br_sped_extr_data_t b
where b.field1 ='I250'
and b.field6 = journals_tab(i)
and b.field5 = 'D'
and b.request_id = g_concurrent_request_id)
where a.field1 = 'I200'
and a.field2 = journals_tab(i)
and a.request_id = g_concurrent_request_id;
update jl_br_sped_extr_data_t a
set field6 = (select trim(to_char(nvl(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),0),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
from jl_br_sped_extr_data_t b
where b.field1 = 'I250'
and b.field5 = 'D'
and b.field2 = naturalaccts_tab(i)
and b.field3 = costcenters_tab(i)
and b.request_id = g_concurrent_request_id),
field7 =(select trim(to_char(nvl(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),0),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
from jl_br_sped_extr_data_t c
where c.field1 = 'I250'
and c.field5 = 'C'
and c.field2 = naturalaccts_tab(i)
and c.field3 = costcenters_tab(i)
and c.request_id = g_concurrent_request_id)
where a.field1 = 'I155'
and a.field2 = naturalaccts_tab(i)
and a.field3 = costcenters_tab(i)
and a.request_id = g_concurrent_request_id;
CURSOR bsv_cur IS SELECT jg_info_v1 from jg_zz_vat_trx_gt; -- jg_zz_vat_trx_gt is global temparary table to store the BSVs associated to LE or Establishment.
SELECT rownum,
value_attribute_type
FROM ( SELECT value_attribute_type
FROM fnd_flex_validation_qualifiers
WHERE id_flex_code = 'GL#'
AND id_flex_application_id = 101
AND flex_value_set_id = p_flex_value_set_id
ORDER BY assignment_date, value_attribute_type) ;
g_last_updated_by := NVL(fnd_profile.value('USER_ID'),1);
g_last_update_date := sysdate;
g_last_update_login := 1;
SELECT ledger_id
INTO g_ledger_id
FROM gl_ledger_le_v
WHERE legal_entity_id = g_legal_entity_id
AND ledger_category_code='PRIMARY';
SELECT name
INTO g_company_name
FROM xle_entity_profiles
WHERE legal_entity_id = g_legal_entity_id;
SELECT name
INTO g_company_name
FROM xle_etb_profiles
WHERE establishment_id = g_establishment_id
AND legal_entity_id = g_legal_entity_id ;
SELECT period_set_name,currency_code,accounted_period_type
INTO g_period_set_name,g_currency_code,g_accounted_period_type
FROM gl_ledgers
WHERE ledger_id = g_ledger_id;
SELECT COUNT(segment_value)
INTO l_bsv_count
FROM GL_LEDGER_NORM_SEG_VALS
WHERE ledger_id = g_ledger_id
AND legal_entity_id = g_legal_entity_id
AND segment_type_code = 'B';
SELECT count(distinct legal_entity_id)
INTO l_le_count
FROM gl_ledger_le_v
WHERE ledger_id = g_ledger_id
AND ledger_category_code='PRIMARY' ;
INSERT INTO jg_zz_vat_trx_gt (jg_info_n1,
jg_info_v1)
SELECT g_concurrent_request_id,
segment_value
FROM GL_LEDGER_NORM_SEG_VALS
WHERE ledger_id = g_ledger_id
AND legal_entity_id = g_legal_entity_id
AND segment_type_code = 'B'
AND g_establishment_id is null -- IF running in Centralized mode and only for LE
UNION
SELECT g_concurrent_request_id,
entity_name
FROM xle_bsv_associations
WHERE legal_parent_id = g_legal_entity_id
AND legal_construct_id = g_establishment_id
AND context = 'EST_BSV_MAPPING'
AND entity_type = 'BALANCING_SEGMENT_VALUE'
AND legal_construct ='ESTABLISHMENT'
AND g_establishment_id is not null; -- running in decentralized mode or in centralized mode for establishment(Establishment acts as company)
SELECT start_date, end_date
INTO g_start_date,g_end_date
FROM gl_periods
WHERE period_name = p_period_name
AND period_set_name = g_period_set_name;
SELECT period_name
INTO g_period_name
FROM gl_periods
WHERE period_set_name = g_period_set_name
AND period_type = g_accounted_period_type
AND g_start_date BETWEEN start_date AND end_date
AND g_end_date BETWEEN start_date AND end_date;
SELECT start_date, end_date
INTO g_adjustment_period_start_date,g_adjustment_period_end_date
FROM gl_periods
WHERE period_name = p_adjustment_period_name
AND period_set_name = g_period_set_name
AND adjustment_period_flag = 'Y';
SELECT application_column_name -- finding which segment column is used for balancing segment storage
INTO g_bsv_segment
FROM fnd_segment_attribute_values
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING'
AND application_id = 101
AND id_flex_num = g_chart_of_accounts_id;
SELECT application_column_name -- finding which segment column is used for natural account segment storage
INTO g_account_segment
FROM fnd_segment_attribute_values
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_ACCOUNT'
AND application_id = 101
AND id_flex_num = g_chart_of_accounts_id;
SELECT application_column_name -- finding which segment column is used for cost center storage
INTO g_cost_center_segment
FROM fnd_segment_attribute_values
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'FA_COST_CTR'
AND application_id = 101
AND id_flex_num = g_chart_of_accounts_id;
SELECT flex_value_set_id
INTO g_account_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_num = g_chart_of_accounts_id
AND id_flex_code ='GL#'
AND application_id = 101
AND application_column_name = g_account_segment;
SELECT flex_value_set_id
INTO g_cost_center_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_num = g_chart_of_accounts_id
AND id_flex_code ='GL#'
AND application_id = 101
AND application_column_name = g_cost_center_segment;
SELECT argument7 -- balancesheet report_id
INTO g_balance_statement_report_id
FROM fnd_concurrent_requests
WHERE request_id = p_balance_statement_request_id;
SELECT argument7 --Income Statement report_id
INTO g_income_statement_report_id
FROM fnd_concurrent_requests
WHERE request_id = p_income_statement_request_id;
SELECT COUNT(*)
INTO g_ap_ar_auxbook_exist
FROM jl_br_cinfos_books
WHERE legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company='N' AND establishment_id is null)
OR (l_estb_acts_as_company='Y' AND establishment_id = g_establishment_id))
AND bookkeeping_type = 'A'
AND auxiliary_book_source = 'AP/AR';
SELECT amount_type
INTO l_fsg_output_check
FROM (SELECT r2.*
FROM fnd_concurrent_requests req,
rg_reports r1,
rg_report_axes_v r2
WHERE req.request_id = g_balance_statement_request_id
AND r1.report_id = req.argument7 --arguement7 in fnd_concurrent_requests holds the report id
AND r1.column_set_id = r2.axis_set_id
ORDER BY r2.sequence)
WHERE ROWNUM = 1;
SELECT amount_type
INTO l_fsg_output_check
FROM (SELECT r2.*
FROM fnd_concurrent_requests req,
rg_reports r1,
rg_report_axes_v r2
WHERE req.request_id = g_income_statement_request_id
AND r1.report_id = req.argument7 --arguement7 in fnd_concurrent_requests holds the report id
AND r1.column_set_id = r2.axis_set_id
ORDER BY r2.sequence)
WHERE ROWNUM = 1;
SELECT tax_regime_code, tax
INTO g_state_insc_tax_regime,g_state_insc_tax
FROM zx_taxes_b
WHERE tax_id = p_state_insc_tax_id;
SELECT tax_regime_code, tax
INTO g_municipal_insc_tax_regime,g_municipal_insc_tax
FROM zx_taxes_b
WHERE tax_id = p_municipal_insc_tax_id;
INSERT INTO JL_BR_SPED_EXTR_PARAM
(REQUEST_ID,
LEDGER_ID,
LEGAL_ENTITY_ID,
ESTABLISHMENT_ID,
PERIOD_TYPE,
PERIOD_NAME,
ADJUSTMENT_PERIOD,
SPEC_SITU_INDIC,
SPEC_SITU_START_DATE,
SPEC_SITU_END_DATE,
BOOKKEEPING_TYPE,
ESTB_ACCT_TYPE,
PARTICIPANT_TYPE,
PARTIC_ACCT_SEGMENT,
CONSOL_MAP_ID,
BALSHEET_REP_REQUEST_ID,
INCMSTMT_REP_REQUEST_ID,
AGC_SOURCE,
RTF_FILE_SOURCE,
HAS_CODE,
ACCT_STMT_INDF,
ACCT_STMT_HEADER,
DATA_EXIST,
REPORT_MODE,
REGISTRATION_SOURCE,
XLE_STATE_INS_REG_CODE,
XLE_CITY_INS_REG_CODE,
ZX_STATE_INS_TAX_ID,
ZX_STATE_INS_REG_CODE,
ZX_CITY_INS_TAX_ID,
ZX_CITY_INS_REG_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES ( g_concurrent_request_id,
g_ledger_id,
p_legal_entity_id,
p_establishment_id,
p_period_type,
g_period_name,
p_adjustment_period_name,
p_special_situation_indicator,
g_start_date,
g_end_date,
p_bookkeeping_type,
p_accounting_type,
p_participant_type,
p_accounting_segment_type,
p_coa_mapping_id,
p_balance_statement_request_id,
p_income_statement_request_id,
p_agglutination_code_source,
p_journal_for_rtf , --rtf_file_soource
p_hash_code,
p_acct_stmt_ident, --acct_stmt_indf
p_acct_stmt_header, --acct_stmt_header
'N',
'P', --report_mode
p_inscription_source,
p_le_state_reg_code,
p_le_municipal_reg_code,
p_state_insc_tax_id,
p_ebtax_state_reg_code,
p_municipal_insc_tax_id ,
p_ebtax_municipal_reg_code,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'ERROR While inserting into JL_BR_SPED_EXTR_PARAM ');
g_errbuf := 'ERROR While inserting into JL_BR_SPED_EXTR_PARAM '||SQLERRM;
sqlstmt := 'SELECT to_char('||segment_code||') FROM gl_code_combinations WHERE code_combination_id = :ccid';
SELECT DECODE(vs.flex_value, 'T', 'O', substrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, instrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, fnd_global.newline,1,g_sped_qualifier_position)+1, 1 ))
INTO l_sped_code
FROM fnd_flex_values vs
WHERE flex_value_id=p_flex_Value_id;
SELECT DECODE(vs.flex_value, 'T', 'O', substrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, instrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, fnd_global.newline,1,g_account_qualifier_position)+1, 1 ))
INTO l_acct_code
FROM fnd_flex_values vs
WHERE flex_value_id=p_flex_value_id;
SELECT DECODE(l_acct_code, 'A', '01', 'L','02', 'O','03', 'E','04', 'R','04',null)
INTO l_acct_code_value
FROM DUAL;
SELECT DECODE (l_sped_code, 'C', '05', 'T','09',null)
INTO l_sped_code_value
FROM DUAL;
SELECT nvl(jl.global_attribute5,jh.global_attribute5)
INTO l_participant_code
FROM gl_je_headers jh
,gl_je_lines jl
,jl_br_sped_partic_codes pc
WHERE jh.je_header_id = jl.je_header_id
AND jh.je_header_id = p_je_header_id
AND jl.je_line_num = p_je_line_num
AND pc.ledger_id = g_ledger_id
AND nvl(jl.global_attribute5,jh.global_attribute5) = pc.participant_code --- to get the particpant code
AND ((g_participant_type in ('SUPPLIERS','CUSTOMERS','SUPPLIER_SITES','CUSTOMER_SITES') AND participant_type = g_participant_type) OR
(g_participant_type = 'ACCOUNTING_FLEXFIELD_SEGMENT' AND pc.segment_type = g_accounting_segment_type) OR
(g_participant_type = 'SUPPLIERS_AND_CUSTOMERS' AND participant_type = 'SUPPLIERS') OR
(g_participant_type = 'SUPPLIERS_AND_CUSTOMERS' AND participant_type = 'CUSTOMERS') OR
(g_participant_type = 'SUPPLIER_AND_CUSTOMER_SITES' AND participant_type = 'SUPPLIER_SITES') OR
(g_participant_type = 'SUPPLIER_AND_CUSTOMER_SITES' AND participant_type = 'CUSTOMER_SITES') );
SELECT 1 --if no data found means, no active relation in the report period.
INTO l_partic_active_flag
FROM dual
WHERE EXISTS ( SELECT 1 FROM jl_br_sped_partic_rel rel
WHERE rel.LEGAL_ENTITY_ID = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.participant_code = l_participant_code
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date);
SELECT pc.participant_code
INTO l_participant_code
FROM jl_br_sped_partic_codes pc
WHERE pc.ledger_id = g_ledger_id
AND pc.segment_type = g_accounting_segment_type
AND get_segment_value(p_je_line_ccid,
decode(g_accounting_segment_type,'GL_ACCOUNT',g_account_segment
,'GL_BALANCING',g_bsv_segment
,'FA_COST_CTR',g_cost_center_segment)) = pc.flex_value
---,g_estb_segment_type,g_establishment_segment)) = pc.flex_value
AND EXISTS (SELECT 1
FROM jl_br_sped_partic_rel rel
WHERE rel.LEGAL_ENTITY_ID = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.participant_code = pc.participant_code
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date );
SELECT DISTINCT xll.party_id , xll.party_site_id --- NEED TO CHECK IF SAME CUSTOMER WITH DIFFERENT CUST SITE ID
INTO l_third_party_id, l_third_party_site_id
FROM gl_import_references glimp, GL_JE_LINES JL , xla_ae_lines xll
WHERE glimp.je_header_id = p_je_header_id
AND glimp.je_header_id = jl.je_header_id
AND glimp.je_line_num = p_je_line_num
AND glimp.je_line_num = jl.je_line_num
AND xll.ledger_id = g_ledger_id
AND xll.gl_sl_link_id = glimp.gl_sl_link_id
AND xll.gl_sl_link_table= glimp.gl_sl_link_table;
SELECT pc.participant_code
INTO l_participant_code
FROM jl_br_sped_partic_codes pc
WHERE vendor_id = l_third_party_id
AND participant_type = 'SUPPLIERS'
AND enabled_flag = 'Y'
AND EXISTS (SELECT 1
FROM jl_br_sped_partic_rel rel
WHERE rel.participant_code = pc.participant_code
AND legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date) ;
SELECT pc.participant_code
INTO l_participant_code
FROM jl_br_sped_partic_codes pc
WHERE vendor_id = l_third_party_id
AND vendor_site_id = l_third_party_site_id
AND participant_type = 'SUPPLIER_SITES'
AND enabled_flag = 'Y'
AND EXISTS (SELECT 1
FROM jl_br_sped_partic_rel rel
WHERE rel.participant_code = pc.participant_code
AND legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date);
SELECT pc.participant_code
INTO l_participant_code
FROM jl_br_sped_partic_codes pc
WHERE pc.cust_account_id = l_third_party_id
AND pc.participant_type = 'CUSTOMERS'
AND EXISTS (SELECT 1
FROM jl_br_sped_partic_rel rel
WHERE rel.participant_code = pc.participant_code
AND legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date);
SELECT pc.participant_code
INTO l_participant_code
FROM jl_br_sped_partic_codes pc
,hz_cust_site_uses_all hcsu
WHERE hcsu.cust_acct_site_id = pc.cust_acct_site_id
-- AND pc.cust_account_id = l_third_party_id
AND hcsu.site_use_id = l_third_party_site_id
AND pc.participant_type = 'CUSTOMER_SITES'
AND EXISTS (SELECT 1
FROM jl_br_sped_partic_rel rel
WHERE rel.participant_code = pc.participant_code
AND legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company ='Y' AND establishment_id=g_establishment_id)
OR (l_estb_acts_as_company='N' AND establishment_id is null)) --- Need to modify
AND rel.effective_from <= g_end_date
AND nvl(rel.effective_to,sysdate) >= g_start_date);
SELECT loc.region_2, le.le_information4 --retrieve state and ibge_city_code
INTO l_state,l_ibge_city_code
FROM xle_entity_profiles le,
xle_registrations reg,
hr_locations loc
WHERE le.legal_entity_id = g_legal_entity_id
AND reg.source_id = le.legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id = loc.location_id
AND le.transacting_entity_flag = 'Y'
AND rownum=1;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
INTO l_cnpj
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT registration_number
INTO l_state_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = g_le_state_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_municipal_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = g_le_municipal_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum =1;
SELECT reg.registration_number
INTO l_state_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE))
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_state_reg_code
AND reg.tax = g_state_insc_tax
AND reg.tax_regime_code = g_state_insc_tax_regime
AND rownum =1;
SELECT reg.registration_number
INTO l_municipal_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE))
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_municipal_reg_code
AND reg.tax = g_municipal_insc_tax
AND reg.tax_regime_code = g_municipal_insc_tax_regime
AND rownum =1;
SELECT loc.region_2, etb.etb_information4
INTO l_state,l_ibge_city_code
FROM xle_etb_profiles etb,
xle_registrations reg,
hr_locations loc
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = g_establishment_id
AND reg.source_id = etb.establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id = loc.location_id
AND rownum=1;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
INTO l_cnpj
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT registration_number
INTO l_state_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = g_le_state_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_municipal_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = g_le_municipal_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT reg.registration_number
INTO l_state_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = g_establishment_id
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_state_reg_code
AND reg.tax = g_state_insc_tax
AND reg.tax_regime_code = g_state_insc_tax_regime
AND rownum =1;
SELECT reg.registration_number
INTO l_municipal_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = g_establishment_id
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_municipal_reg_code
AND reg.tax = g_municipal_insc_tax
AND reg.tax_regime_code = g_municipal_insc_tax_regime
AND rownum =1;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
field10,
separator10,
field11,
separator11,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'0000' -- Register (field 1)
,'|'
,'LECD' -- Fixed Text (field 2)
,'|'
,to_char(g_start_date,'ddmmyyyy') --(field 3)
,'|'
,to_char(g_end_date,'ddmmyyyy') --(field 4)
,'|'
,g_company_name --field 5
,'|'
,l_cnpj --field 6
,'|'
,SUBSTRB(l_state,1,2)
,'|'
,l_state_inscription
,'|'
,SUBSTRB(l_ibge_city_code,1,7)
,'|'
,l_municipal_inscription
,'|'
,g_special_situation_indicator
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting data into 0000 register');
g_errbuf := 'ERROR While inserting 0000 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'0', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'0001', --Register (field1)
'|',
0,-- null,--decode(count(*),0,1,0), --field2
'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
g_errbuf := 'ERROR While inserting 0001 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'0007' -- Register (field 1)
,'|'
,reg.reg_information1 --field 2
,'|'
,decode(reg.reg_information1,'00',null,reg.registration_number) --field 3
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM xle_registrations reg
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.reg_information1 IS NOT NULL
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.
AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'0007' -- Register (field 1)
,'|'
,reg.reg_information1 --field 2
,'|'
,decode(reg.reg_information1,'00',null,reg.registration_number) --field 3
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM xle_registrations reg
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.reg_information1 IS NOT NULL
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.
AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
g_errbuf := 'ERROR While inserting 0007 register '||SQLERRM;
CURSOR secondary_estbs_cur IS SELECT establishment_id
FROM xle_etb_profiles
WHERE legal_entity_id = g_legal_entity_id
--AND main_establishment_flag='N'
AND establishment_id NOT IN (SELECT establishment_id
FROM xle_etb_profiles
WHERE legal_entity_id = g_legal_entity_id
AND main_establishment_flag = 'Y'
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE))
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE)))
AND TRUNC(g_start_date) >= TRUNC(NVL(effective_from,SYSDATE))
AND TRUNC(g_end_date) <= TRUNC(NVL(effective_to,SYSDATE));
SELECT 'Y'
INTO l_main_estb_flag
FROM xle_etb_profiles
WHERE legal_entity_id = g_legal_entity_id
AND establishment_id = g_establishment_id
AND main_establishment_flag = 'Y'
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE))
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE));
SELECT loc.region_2, le.le_information4
INTO l_state,l_ibge_city_code
FROM xle_entity_profiles le,
xle_registrations reg,
hr_locations loc
WHERE le.legal_entity_id = g_legal_entity_id
AND reg.source_id = le.legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id = loc.location_id
AND le.transacting_entity_flag = 'Y'
AND rownum = 1 ;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
INTO l_cnpj
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT count(*)
INTO l_count
FROM xle_registrations
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from ,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date);
SELECT registration_number
INTO l_nire
FROM xle_registrations
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date)
AND rownum =1;
SELECT registration_number
INTO l_nire
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_state_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = g_le_state_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_municipal_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = g_le_municipal_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum =1;
SELECT reg.registration_number
INTO l_state_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE))
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_state_reg_code
AND reg.tax = g_state_insc_tax
AND reg.tax_regime_code = g_state_insc_tax_regime
AND rownum =1;
SELECT reg.registration_number
INTO l_municipal_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND main_establishment_flag = 'Y' -- will fetch the registration number of main establishment for LE in case of EBtax.
AND TRUNC(g_start_date) >= TRUNC(NVL(main_effective_from,SYSDATE)) -- conditions to check establishment is active as main estb
AND TRUNC(g_end_date) <= TRUNC(NVL(main_effective_to,SYSDATE))
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_municipal_reg_code
AND reg.tax = g_municipal_insc_tax
AND reg.tax_regime_code = g_municipal_insc_tax_regime
AND rownum =1;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES(g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'0020' -- Register (field 1)
,'|'
,1 --LE
,'|'
,l_cnpj --field 3
,'|'
,SUBSTRB(l_state,1,2) --field 4
,'|'
,l_state_inscription -- field 5
,'|'
,SUBSTRB(l_ibge_city_code,1,7) --field6
,'|'
,l_municipal_inscription -- field 7
,'|'
,SUBSTRB(l_nire,11) --field 8
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting data into 0020 register');
g_errbuf := 'ERROR While inserting 0020 register '||SQLERRM;
SELECT loc.region_2, etb.etb_information4
INTO l_state,l_ibge_city_code
FROM xle_etb_profiles etb,
xle_registrations reg,
hr_locations loc
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = l_establishment_id
AND reg.source_id = etb.establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id = loc.location_id
AND rownum = 1;
SELECT count(*)
INTO l_count
FROM xle_registrations
WHERE source_id = l_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date);
SELECT registration_number
INTO l_nire
FROM xle_registrations
WHERE source_id = l_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date)
AND rownum = 1;
SELECT registration_number
INTO l_nire
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE source_id = l_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum = 1;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789')
INTO l_cnpj
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = l_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT registration_number
INTO l_state_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = l_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = g_le_state_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_municipal_inscription
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = l_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_etb = g_le_municipal_reg_code
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT reg.registration_number
INTO l_state_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = l_establishment_id -- will fetch the registration number of main establishment for LE in case of EBtax.
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_state_reg_code
AND reg.tax = g_state_insc_tax
AND reg.tax_regime_code = g_state_insc_tax_regime
AND rownum =1;
SELECT reg.registration_number
INTO l_municipal_inscription
FROM zx_registrations reg,
xle_etb_profiles etb,
zx_party_tax_profile ptp
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = l_establishment_id -- will fetch the registration number of main establishment for LE in case of EBtax.
AND etb.party_id = ptp.party_id
AND ptp.party_tax_profile_id = reg.party_tax_profile_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND reg.registration_type_code = g_ebtax_municipal_reg_code
AND reg.tax = g_municipal_insc_tax
AND reg.tax_regime_code = g_municipal_insc_tax_regime
AND rownum =1;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES(g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'0020' -- Register (field 1)
,'|'
,0 --Establishment
,'|'
,l_cnpj --field 3
,'|'
,SUBSTRB(l_state,1,2) --field 4
,'|'
,l_state_inscription -- field 5
,'|'
,SUBSTRB(l_ibge_city_code,1,7) --field6
,'|'
,l_municipal_inscription -- field 7
,'|'
,SUBSTRB(l_nire,1,11) --field 8
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting data into 0020 register-establishments');
g_errbuf := 'ERROR While inserting 0020 register '||SQLERRM;
SELECT p.participant_code
,participant_name
,lpad(lc.meaning,5,'0') cbank_country_code
,decode(p.register_type,'2',decode(length(p.register_number),9,substrb(p.register_number,2,8)||p.register_subsidiary||p.register_digit,
p.register_number||p.register_subsidiary||p.register_digit),null) cnpj
,decode(p.register_type,'1',p.register_number||p.register_digit,null) cpf
,p.nit
,p.state_code
,p.state_inscription
,p.state_inscription_substitute
,p.ibge_city_code
,p.municipal_inscription
,p.suframa_inscription_number
FROM jl_br_sped_partic_codes p
,fnd_lookups lc
WHERE ledger_id = g_ledger_id
AND lc.lookup_type ='JLBR_CBANK_COUNTRY_CODES'
AND lc.lookup_code = p.country_code
AND p.enabled_flag = 'Y'
AND ((g_participant_type in ('SUPPLIERS','CUSTOMERS','SUPPLIER_SITES','CUSTOMER_SITES') AND participant_type = g_participant_type) OR
(g_participant_type = 'ACCOUNTING_FLEXFIELD_SEGMENT' AND p.segment_type = g_accounting_segment_type) OR
(g_participant_type = 'SUPPLIERS_AND_CUSTOMERS' AND participant_type = 'SUPPLIERS') OR
(g_participant_type = 'SUPPLIERS_AND_CUSTOMERS' AND participant_type = 'CUSTOMERS') OR
(g_participant_type = 'SUPPLIER_AND_CUSTOMER_SITES' AND participant_type = 'SUPPLIER_SITES') OR
(g_participant_type = 'SUPPLIER_AND_CUSTOMER_SITES' AND participant_type = 'CUSTOMER_SITES') )
AND exists (select 1 from jl_br_sped_partic_rel rel
where rel.participant_code = p.participant_code
and rel.legal_entity_id = g_legal_entity_id
and ((g_accounting_type='CENTRALIZED' AND ((l_estb_acts_as_company = 'Y' AND establishment_id = g_establishment_id)
OR (l_estb_acts_as_company ='N' AND establishment_id is null)))
OR
(g_accounting_type='DECENTRALIZED' AND (establishment_id is null OR
establishment_id=g_establishment_id)))
and rel.effective_from <= g_end_date
and nvl(rel.effective_to,sysdate) >= g_start_date);
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
field10,
separator10,
field11,
separator11,
field12,
separator12,
field13,
separator13,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values (g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval -- Record_seq
,'0150' -- Register (field 1)
,'|'
, partic_record.participant_code --field 2
,'|'
, partic_record.participant_name --field 3
,'|'
, partic_record.cbank_country_code --field 4
,'|'
, partic_record.cnpj --field 5
,'|'
, partic_record.cpf --field 6
,'|'
, partic_record.nit --field 7
,'|'
, partic_record.state_code --field 8
,'|'
, partic_record.state_inscription --field 9
,'|'
, partic_record.state_inscription_substitute --field10
, '|'
, partic_record.ibge_city_code --field 11
,'|'
, partic_record.municipal_inscription --field 12
,'|'
,partic_record.suframa_inscription_number --field 13
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval -- Record_seq
,'0180' -- Register (field 1)
,'|'
,relationship_code --field 2
,'|'
,to_char(effective_from,'ddmmyyyy')--field 3
,'|'
,to_char(effective_to,'ddmmyyyy') --field 4
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM jl_br_sped_partic_rel
WHERE participant_code = partic_record.participant_code
AND legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company = 'Y' AND establishment_id = g_establishment_id)
OR (l_estb_acts_as_company = 'N' AND establishment_id is null))
AND effective_from <= g_end_date
AND nvl(effective_to,sysdate) >= g_start_date ;
SELECT COUNT(*) INTO l_count
FROM jl_br_sped_extr_data_t
WHERE request_id =g_concurrent_request_id
AND block = '0'
AND field1 = '0150';
SELECT COUNT(*) INTO l_count
FROM jl_br_sped_extr_data_t
WHERE request_id =g_concurrent_request_id
AND block = '0'
AND field1 = '0180';
g_errbuf := 'ERROR While inserting 0150 and 0180 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES( g_concurrent_request_id
,'0' -- Block
,jl_br_sped_extr_data_t_s.nextval -- Record_seq
,'0990' -- Register (field 1)
,'|'
,0--,null --count(*) -- Field 2
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
g_errbuf := 'ERROR While inserting 0990 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'I', --block
jl_br_sped_extr_data_t_s.nextval, --Record_seq
'I001', --Register (field1)
'|',
0,--null,--decode(count(*),0,1,0), --field2
'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
g_errbuf := 'ERROR While inserting I0001 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
( g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- Record_seq
,'I010' -- Register (field 1)
,'|'
,substr(g_bookkeeping_type,1,1) -- field 2
,'|'
,'1.00' --field3
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting I010 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT
g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- Record_seq
,'I012' -- Register (field 1)
,'|'
,book_number -- field 2
,'|'
,book_name --field3
,'|'
,0 --field4
,'|'
,DECODE(g_bookkeeping_type,'R',g_hash_code,'B',g_hash_code,NULL)
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM jl_br_cinfos_books
WHERE legal_entity_id = g_legal_entity_id
AND ((l_estb_acts_as_company ='N' AND establishment_id is null)
OR (l_estb_acts_as_company = 'Y' AND establishment_id=g_establishment_id)) --establishment acts as company
AND bookkeeping_type = DECODE(g_bookkeeping_type,'R','A','B','A'
,'A/R','R','A/B','B')
AND ((bookkeeping_type = 'A' AND auxiliary_book_flag = 'Y')
OR bookkeeping_type <> 'A');
g_errbuf := 'ERROR While inserting I012 register '||SQLERRM;
l_query := 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id|| '
,''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,''I015'' -- Register (field 1)
,''|''
,natural_acct
,''|''
,'||g_created_by||'
,'''||g_creation_date||'''
,'||g_last_updated_by||'
,'''||g_last_update_date||'''
,'||g_last_update_login||'
FROM (SELECT DISTINCT glcc.'||g_account_segment||' natural_acct
FROM gl_je_headers jh
,gl_je_lines jl
,gl_import_references glimp
,xla_ae_lines xll
,xla_ae_headers xlh
,xla_distribution_links xld
,gl_code_combinations glcc
WHERE jh.ledger_id = '||g_ledger_id||'
AND jh.je_source in (''Payables'',''Receivables'')
AND jh.je_header_id = jl.je_header_id
AND glimp.je_header_id = jh.je_header_id
AND xlh.ae_header_id = xll.ae_header_id
AND xlh.EVENT_ID = xld.EVENT_ID
AND xlh.application_id = xll.application_id
AND xll.ae_header_id = xld.ae_header_id
AND xll.ae_line_num = xld.ae_line_num
AND xll.application_id = xld.application_id
AND jl.je_line_num = glimp.je_line_num
AND glimp.gl_sl_link_id = xll.gl_sl_link_id
AND glimp.gl_sl_link_table = xll.gl_sl_link_table
AND jl.code_combination_id = glcc.code_combination_id
AND jh.status = ''P''
AND jl.status = ''P''
AND jh.default_effective_date between '''||g_start_date||''' and '''|| g_end_date||'''
AND ('''||l_exclusive_mode||'''=''Y''
OR ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
GROUP BY glimp.je_header_id,glimp.je_line_num,jl.code_combination_id,glcc.'||g_account_segment||'
HAVING count(*) >1)';
g_errbuf := 'ERROR While inserting I015 register '||SQLERRM;
SELECT le.effective_from,bk.book_number,bk.book_name
INTO l_effective_from,l_book_number,l_book_name
FROM xle_entity_profiles le,
jl_br_cinfos_books bk
WHERE le.legal_entity_id = g_legal_entity_id
AND bk.legal_entity_id = le.legal_entity_id
AND bk.establishment_id IS NULL -- need to retrive the book info of LE.
AND bookkeeping_type = substrb(g_bookkeeping_type,1,1)
AND bk.auxiliary_book_flag ='N';
SELECT count(*)
INTO l_count
FROM xle_registrations
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date);
SELECT registration_number
INTO l_nire
FROM xle_registrations
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date)
AND rownum=1;
SELECT registration_number
INTO l_nire
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE source_id = g_legal_entity_id
AND source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT loc.town_or_city
INTO l_city
FROM xle_registrations reg,hr_locations_all loc
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id =loc.location_id
AND rownum = 1 ;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789'),reg.registration_id
INTO l_cnpj,l_registration_id
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT count(*)
INTO l_count
FROM xle_histories his
WHERE source_table = 'XLE_REGISTRATIONS'
AND source_id = l_registration_id
AND source_column_name = 'EFFECTIVE_FROM';
SELECT substr(source_column_value,1,11)
INTO l_conversion_date
FROM xle_histories his
WHERE source_table = 'XLE_REGISTRATIONS'
AND source_id = l_registration_id
AND source_column_name = 'EFFECTIVE_FROM'
AND effective_to IS NULL;
SELECT etb.effective_from,bk.book_number,bk.book_name
INTO l_effective_from,l_book_number,l_book_name
FROM xle_etb_profiles etb,
jl_br_cinfos_books bk
WHERE etb.legal_entity_id = g_legal_entity_id
AND etb.establishment_id = g_establishment_id
AND bk.legal_entity_id = etb.legal_entity_id
AND bk.establishment_id = etb.establishment_id -- need to retrive the book info of ETB.
AND bookkeeping_type = substrb(g_bookkeeping_type,1,1)
AND bk.auxiliary_book_flag ='N';
SELECT count(*)
INTO l_count
FROM xle_registrations
WHERE source_id = g_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date) ;
SELECT registration_number
INTO l_nire
FROM xle_registrations
WHERE source_id = g_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND UPPER(place_of_registration) = 'NIRE'
AND nvl(effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (effective_to IS NULL OR effective_to >= g_end_date)
AND rownum = 1;
SELECT registration_number
INTO l_nire
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE source_id = g_establishment_id
AND source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.legislative_cat_code = 'COMMERCIAL_LAW'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum = 1;
SELECT loc.town_or_city
INTO l_city
FROM xle_registrations reg,hr_locations_all loc
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.identifying_flag = 'Y'
AND reg.location_id =loc.location_id
AND rownum = 1 ;
SELECT translate(reg.registration_number,'0123456789/-.', '0123456789'),reg.registration_id
INTO l_cnpj,l_registration_id
FROM xle_registrations reg,
xle_jurisdictions_vl jur
WHERE reg.source_id = g_establishment_id
AND reg.source_table = 'XLE_ETB_PROFILES'
AND reg.jurisdiction_id = jur.jurisdiction_id
AND jur.registration_code_le = 'CNPJ'
AND nvl(reg.effective_from,to_date('01-01-1950','DD-MM-YYYY')) <= g_start_date
AND (reg.effective_to IS NULL OR reg.effective_to >= g_end_date)
AND rownum=1;
SELECT decode(length(l_cnpj),15,substr(l_cnpj,2,14),l_cnpj) into l_cnpj from dual;
SELECT count(*)
INTO l_count
FROM xle_histories his
WHERE source_table = 'XLE_REGISTRATIONS'
AND source_id = l_registration_id
AND source_column_name = 'EFFECTIVE_FROM';
SELECT substr(source_column_value,1,11)
INTO l_conversion_date
FROM xle_histories his
WHERE source_table = 'XLE_REGISTRATIONS'
AND source_id = l_registration_id
AND source_column_name = 'EFFECTIVE_FROM'
AND effective_to IS NULL;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
field10,
separator10,
field11,
separator11,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I030' -- Register (field 1)
,'|'
,'TERMO DE ABERTURA' -- Fixed Text (field 2)
,'|'
,l_book_number --(field 3)
,'|'
,l_book_name --(field 4)
,'|'
,null --field5
,'|'
,g_company_name --field6
,'|'
,SUBSTRB(l_nire,1,11) --field7
,'|'
,l_cnpj -- field8
,'|'
,to_char(l_effective_from,'DDMMYYYY') -- field9
,'|'
,to_char(l_conversion_date,'DDMMYYYY') -- field10
,'|'
,l_city -- field11
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting Data into I030 register');
g_errbuf := 'ERROR While inserting 0000 register '||SQLERRM;
TYPE tab_fv_last_update_date IS TABLE OF
fnd_flex_values.last_update_date%TYPE INDEX BY BINARY_INTEGER;
fv_last_update_date tab_fv_last_update_date;
l_fv_last_update_date fnd_flex_values.last_update_date%TYPE;
l_query := 'SELECT V.last_update_date
,V.flex_value_id
,V.summary_flag
,level parent_level
,V.parent_flex_value parent
,V.child_flex_value child
,V.description
FROM ( SELECT b.last_update_date
,b.flex_value_id
,b.summary_flag
,a.parent_flex_value
,b.flex_value child_flex_value
,b.description
FROM fnd_flex_value_norm_hierarchy a, -- Hierarquia compilada
FND_FLEX_VALUES_VL b, -- Valores do segmento
fnd_id_flex_segments c -- Segmentos da estrutura
WHERE c.application_id = 101
AND c.id_flex_code = ''GL#''
AND c.id_flex_num = '||g_chart_of_accounts_id||
' AND c.application_column_name = '''||g_account_segment||'''
AND b.flex_value_set_id = '||g_account_value_set_id||
' AND b.flex_value_set_id = c.flex_value_set_id
AND b.flex_value >= a.child_flex_value_low
AND b.flex_value <= a.child_flex_value_high
AND a.flex_value_set_id = b.flex_value_set_id
AND nvl(DECODE(b.flex_value, ''T'', ''O'', substrb( fnd_global.newline
||b.compiled_value_attributes
||fnd_global.newline, instrb( fnd_global.newline
||b.compiled_value_attributes
||fnd_global.newline, fnd_global.newline,1,'|| g_exclusion_qualifier_position||')+1, 1 )),''N'') <> ''Y'') V
WHERE V.summary_flag= ''Y''
OR exists (SELECT 1
FROM gl_code_combinations glcc
WHERE glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||
' AND glcc.summary_flag = ''N''
AND ('''||l_exclusive_mode||'''=''Y'' OR
('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
AND glcc.'||g_account_segment||' =V.child_flex_value
)
CONNECT BY V.parent_flex_value = PRIOR V.child_flex_value
START WITH V.parent_flex_value IN
( SELECT b.flex_value
FROM FND_FLEX_VALUES_VL b
WHERE b.flex_value_set_id = '||g_account_value_set_id||
' AND nvl(DECODE(b.flex_value, ''T'', ''O'', substrb( fnd_global.newline
||b.compiled_value_attributes
||fnd_global.newline, instrb( fnd_global.newline
||b.compiled_value_attributes
||fnd_global.newline, fnd_global.newline,1,'|| g_exclusion_qualifier_position||')+1, 1 )),''N'') <> ''Y''
AND NOT EXISTS (SELECT 1 FROM fnd_flex_value_norm_hierarchy a
WHERE a.flex_value_set_id = '||g_account_value_set_id||
' AND b.flex_value >= a.child_flex_value_low
AND b.flex_value <= a.child_flex_value_high))';
BULK COLLECT INTO fv_last_update_date
,fv_id
,fv_summary_flag
,fv_parent_value_level
,fv_parent_value
,fv_child_value
,fv_description;
fv_last_update_date.DELETE(i);
fv_id.DELETE(i);
fv_summary_flag.DELETE(i);
fv_parent_value_level.DELETE(i);
fv_parent_value.DELETE(i);
fv_child_value.DELETE(i);
fv_description.DELETE(i);
SELECT last_update_date
,flex_value_id
,summary_flag
,description
INTO l_fv_last_update_date
,l_fv_id
,l_fv_summary_flag
,l_fv_description
FROM FND_FLEX_VALUES_VL
WHERE flex_value = fv_parent_value(i)
AND flex_value_set_id = g_account_value_set_id; -- GL Account segment value set id
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
--field7,
separator7,
field8,
separator8,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I050' -- Register (field 1)
,'|'
, to_char(l_fv_last_update_date,'DDMMYYYY') --field 2
,'|'
, JL_BR_SPED_DATA_EXTRACT_PKG.get_account_type(l_fv_id) --field 3
,'|'
, decode(l_fv_summary_flag,'N','A','S') --field 4 summary flag has 'Y','N' values.
,'|'
,fv_parent_value_level(i) --field 5
,'|'
,fv_parent_value(i) --field 6
,'|'
-- ,fv_parent_value(i) --field 7
,'|'
,l_fv_description --field 8
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I050' -- Register (field 1)
,'|'
, to_char(fv_last_update_date(i),'DDMMYYYY') --field 2
,'|'
, JL_BR_SPED_DATA_EXTRACT_PKG.get_account_type(fv_id(i)) --field 3
,'|'
, decode(fv_summary_flag(i),'N','A','S') --field 4 summary flag has 'Y','N' values.
,'|'
,fv_parent_value_level(i)+1 --field 5
,'|'
,fv_child_value(i) --field 6
,'|'
,fv_parent_value(i) --field 7
,'|'
,fv_description(i) --field 8
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting I050 register '||SQLERRM;
SELECT count(distinct parent_flex_value) into l_cnt
FROM gl_coa_mappings C
,gl_cons_segment_map cm
,gl_cons_flex_hierarchies ch
WHERE c.coa_mapping_id = g_coa_mapping_id
AND cm.coa_mapping_id = c.coa_mapping_id
AND cm.segment_map_type = 'R' --Detail Rollup Ranges
AND cm.segment_map_id = ch.segment_map_id
AND p_account_flex_value BETWEEN ch.child_flex_value_low AND ch.child_flex_value_high;
SELECT parent_flex_value into l_referential_account
FROM (SELECT ch.parent_flex_value
FROM gl_coa_mappings C
,gl_cons_segment_map cm
,gl_cons_flex_hierarchies ch
WHERE c.coa_mapping_id = g_coa_mapping_id
AND cm.coa_mapping_id = c.coa_mapping_id
AND cm.segment_map_type = 'R' --Detail Rollup Ranges
AND cm.segment_map_id = ch.segment_map_id
AND p_account_flex_value BETWEEN ch.child_flex_value_low AND ch.child_flex_value_high
ORDER BY ch.last_update_date DESC)
WHERE ROWNUM = 1;
SELECT etb_information5
INTO l_institution_resp_code
FROM xle_etb_profiles
WHERE legal_entity_id = g_legal_entity_id
AND establishment_id = g_establishment_id;
SELECT le_information5
INTO l_institution_resp_code
FROM xle_entity_profiles
WHERE legal_entity_id = g_legal_entity_id ;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I051' -- Register (field 1)
,'|'
, SUBSTR(l_institution_resp_code,1,2) --field 2
,'|'
, NULL --field 3
,'|'
, l_referential_account --field 4
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting data into I051 register '||SQLERRM;
/* Inserts all agglutination_codes defined for balance_statement_report_id*/
sql_stmt := 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id||'
,''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval
,''I052'' -- Register (field 1)
,''|''
, NULL --field 2
,''|''
, row_seq_ident
,''|''
,'||g_created_by||'
,'''||g_creation_date||'''
,'||g_last_updated_by||'
,'''||g_last_update_date||'''
,'||g_last_update_login||'
FROM
(SELECT DECODE('''||g_agglutination_code_source||''',''FSG_LINE'',to_char(r3.sequence),r3.description) row_seq_ident
FROM rg_reports r1
,rg_report_axis_sets r2
,rg_report_axes_v r3
,rg_report_axis_contents r4
WHERE r1.report_id = '||g_balance_statement_report_id||'
AND r1.row_set_id = r2.axis_set_id
AND r2.axis_set_id = r3.axis_set_id
AND r3.axis_set_id = r4.axis_set_id
AND r3.sequence = r4.axis_seq
AND :p_account_flex_value >='||g_account_segment||'_LOW
AND :p_account_flex_value <='|| g_account_segment||'_HIGH)';
/* Inserts all agglutination_codes defined for income_statement_report_id*/
sql_stmt := 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id||'
,''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval
,''I052'' -- Register (field 1)
,''|''
, NULL --field 2
,''|''
,row_seq_ident
,''|''
,'||g_created_by||'
,'''||g_creation_date||'''
,'||g_last_updated_by||'
,'''||g_last_update_date||'''
,'||g_last_update_login||'
FROM
(SELECT DECODE('''||g_agglutination_code_source||''',''FSG_LINE'',to_char(r3.sequence),r3.description) row_seq_ident
FROM rg_reports r1
,rg_report_axis_sets r2
,rg_report_axes_v r3
,rg_report_axis_contents r4
WHERE r1.report_id = '||g_income_statement_report_id||'
AND r1.row_set_id = r2.axis_set_id
AND r2.axis_set_id = r3.axis_set_id
AND r3.axis_set_id = r4.axis_set_id
AND r3.sequence = r4.axis_seq
AND :p_account_flex_value >='||g_account_segment||'_LOW
AND :p_account_flex_value <='|| g_account_segment||'_HIGH)';
g_errbuf := 'ERROR While inserting data into I052 register '||SQLERRM;
l_query := 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2, separator2,
field3, separator3,
field4, separator4,
created_by, creation_date, last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id||
',''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,''I100'' -- Register (field 1)
,''|''
,to_char(fv.last_update_date,''DDMMYYYY'')
,''|''
,fv.flex_value
,''|''
,fv.description
,''|''
,'||g_created_by|| ','''||g_creation_date||''''||
','||g_last_updated_by|| ','''||g_last_update_date||''''||
','||g_last_update_login|| ' FROM fnd_flex_values_vl fv
WHERE fv.flex_value_set_id = '||g_cost_center_value_set_id||
' AND EXISTS (SELECT 1
FROM gl_code_combinations glcc
WHERE glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||
' AND glcc.summary_flag = ''N''
AND ('''||l_exclusive_mode||'''=''Y''
OR ('''||l_exclusive_mode ||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
AND glcc.'||g_cost_center_segment||' =fv.flex_value)';
g_errbuf := 'ERROR While inserting data into I100 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I150' -- Register (field 1)
,'|'
,to_char(g_start_date,'DDMMYYYY') --filed2
,'|'
,to_char(g_end_date,'DDMMYYYY') --field3
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting data into I150 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I151' -- Register (field 1)
,'|'
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting data into I151 register '||SQLERRM;
'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id||
',''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,''I155'' -- Register (field 1)
,''|''
,natural_acct
,''|''
,cost_center
,''|''
,begin_bal
,''|''
,begin_bal_type
,''|''
,period_dr
,''|''
,period_cr
,''|''
,end_bal
,''|''
,end_bal_type
,''|''
,'||g_created_by||
','''||g_creation_date||''''||
','||g_last_updated_by||
','''||g_last_update_date||''''||
','||g_last_update_login||
' FROM (SELECT glcc.'||g_account_segment||' natural_acct
,''|''
,decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||') cost_center
,''|''
,TRIM(TO_CHAR(ABS(NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0))
,''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) begin_bal
,''|''
,DECODE(SIGN(NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0))
,1,''D'',''C'') begin_bal_type
,''|''
,TRIM(TO_CHAR(NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_DR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_DR,0))
,0),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) period_dr
,''|''
,TRIM(TO_CHAR(NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_CR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_CR,0))
,0),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) period_cr
,''|''
,TRIM(TO_CHAR(ABS(NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0)),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) end_bal
,''|''
,DECODE(SIGN(NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0)),1,''D'',''C'') end_bal_type
FROM gl_balances glb
,gl_code_combinations glcc
WHERE period_name in('''||g_period_name||''' , '''||g_adjustment_period_name||''')
AND glb.ledger_id = '||g_ledger_id||
' AND glb.currency_code = '''||g_currency_code||'''
AND glb.code_combination_id= glcc.code_combination_id
AND ('''||l_exclusive_mode||'''=''Y'' OR ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt))) AND glcc.summary_flag = ''N''
AND glb.actual_flag = ''A''
GROUP BY glcc.'||g_account_segment||',decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment ||')
HAVING NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0) <> 0 OR
NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_DR,0))
+ SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_DR,0)),0) <> 0 OR
NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_CR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_CR,0)),0) <> 0 OR
NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0) <> 0 )';
execute immediate 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '||g_concurrent_request_id||
',''I'' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,''I155'' -- Register (field 1)
,''|''
,natural_acct
,''|''
,cost_center
,''|''
,begin_bal
,''|''
,begin_bal_type
,''|''
,period_dr
,''|''
,period_cr
,''|''
,end_bal
,''|''
,end_bal_type
,''|''
,'||g_created_by||
','''||g_creation_date||''''||
','||g_last_updated_by||
','''||g_last_update_date||''''||
','||g_last_update_login||
' FROM (SELECT glcc.'||g_account_segment||' natural_acct
,''|''
,decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||') cost_center
,''|''
,TRIM(TO_CHAR(ABS(NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0))
,''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) begin_bal
,''|''
,DECODE(SIGN(NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0))
,1,''D'',''C'') begin_bal_type
,''|''
,TRIM(TO_CHAR(NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_DR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_DR,0))
,0),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) period_dr
,''|''
,TRIM(TO_CHAR(NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_CR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_CR,0))
,0),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) period_cr
,''|''
,TRIM(TO_CHAR(ABS(NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0)),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) end_bal
,''|''
,DECODE(SIGN(NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0)),1,''D'',''C'') end_bal_type
FROM gl_balances glb
,gl_code_combinations glcc
WHERE period_name in('''||g_period_name||''' , '''||g_adjustment_period_name||''')
AND glb.ledger_id = '||g_ledger_id||
' AND glb.currency_code = '''||g_currency_code||'''
AND glb.code_combination_id= glcc.code_combination_id
AND ('''||l_exclusive_mode||'''=''Y'' OR ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
AND glcc.summary_flag = ''N''
AND glb.actual_flag = ''A''
GROUP BY glcc.'||g_account_segment||',decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment ||')
HAVING NVL(SUM(DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR,0)),0) <> 0 OR
NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_DR,0))
+ SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_DR,0)),0) <> 0 OR
NVL(SUM(DECODE(glb.period_name,'''||g_period_name||''',PERIOD_NET_CR,0))
+SUM(DECODE(glb.period_name,'''||g_adjustment_period_name||''',PERIOD_NET_CR,0)),0) <> 0 OR
NVL(SUM(
DECODE(NVL('''||g_adjustment_period_name||''',''-1'')
,''-1'' , DECODE(glb.period_name
,'''||g_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0)
,DECODE(glb.period_name,
'''||g_adjustment_period_name||''',BEGIN_BALANCE_DR - BEGIN_BALANCE_CR +PERIOD_NET_DR -PERIOD_NET_CR
,0 )
)
),0) <> 0 )';
g_errbuf := 'ERROR While inserting data into I155 register '||SQLERRM;
SELECT j.name||'-'||j.je_batch_id name
,j.default_effective_date
,j.running_total_accounted_dr
,j.je_header_id
,j.je_source
,j.je_category
,j.period_name
FROM gl_je_headers j
WHERE j.ledger_id = g_ledger_id
AND j.actual_flag = 'A'
AND j.status = 'P'
AND j.currency_code <> 'STAT' -- filtering statistic journals. Introduced thid condition for new sped enhancement
AND j.period_name in (g_period_name,g_adjustment_period_name)
AND ((j.default_effective_date between g_start_date and g_end_date)
OR (j.default_effective_date between g_adjustment_period_start_date and g_adjustment_period_end_date))
AND j.je_source NOT IN (SELECT fl.lookup_code
FROM fnd_lookups fl
WHERE fl.lookup_type = 'JLBR_SPED_LEGACY_SOURCES'
AND fl.ENABLED_FLAG = 'Y')
AND EXISTS (SELECT 1
FROM gl_je_lines jl
WHERE jl.je_header_id= j.je_header_id
AND jl.ledger_id=g_ledger_id
AND (l_exclusive_mode = 'Y' OR
(l_exclusive_mode = 'N' AND get_segment_value(jl.code_combination_id,g_bsv_segment) in (select jg_info_v1 from jg_zz_vat_trx_gt)))
);
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I200' -- field 1
,'|'
,c_journal_header.name --field 2
,'|'
,to_char(c_journal_header.default_effective_date,'DDMMYYYY') --field 3
,'|'
,TRIM(TO_CHAR(c_journal_header.running_total_accounted_dr,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) --field 4
,'|'
,l_jounrnal_flag --field 5
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
g_errbuf := 'ERROR While inserting data into I200 register '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I250' -- field 1
,'|'
,get_segment_value(jl.code_combination_id,g_account_segment) natural_Acct
,'|'
,decode(l_cc_exists_flag,0,null,get_segment_value(jl.code_combination_id,g_cost_center_segment)) cost_center
,'|'
,TRIM(TO_CHAR(jl.accounted_dr,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) -- removed abs() as part of ER#11653651
,'|'
,'D' --,DECODE(SIGN(nvl(jl.accounted_dr,0)-nvl(jl.accounted_cr,0)),-1,'C','D')
,'|'
,p_journal_name
,'|'
,NULL
,'|'
,jl.description
,'|'
,get_participant_code(jl.je_header_id,jl.je_line_num,p_journal_source,jl.code_combination_id,NULL,NULL)
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM gl_je_lines jl
WHERE jl.je_header_id = p_journal_header_id
AND jl.accounted_dr is not null
AND jl.ledger_id = g_ledger_id
AND (l_exclusive_mode='Y' OR (l_exclusive_mode='N' AND get_segment_value(jl.code_combination_id,g_bsv_segment) in (SELECT jg_info_v1 FROM jg_zz_vat_trx_gt))); -- need to modify
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I250' -- field 1
,'|'
,get_segment_value(jl.code_combination_id,g_account_segment) natural_Acct
,'|'
,decode(l_cc_exists_flag,0,null,get_segment_value(jl.code_combination_id,g_cost_center_segment)) cost_center
,'|'
,TRIM(TO_CHAR(jl.accounted_cr,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) --removed abs() as part of ER#11653651
,'|'
,'C' --,DECODE(SIGN(nvl(jl.accounted_dr,0)-nvl(jl.accounted_cr,0)),-1,'C','D')
,'|'
,p_journal_name
,'|'
,NULL
,'|'
,jl.description
,'|'
,get_participant_code(jl.je_header_id,jl.je_line_num,p_journal_source,jl.code_combination_id,NULL,NULL)
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM gl_je_lines jl
WHERE jl.je_header_id = p_journal_header_id
AND jl.accounted_cr is not null
AND jl.ledger_id = g_ledger_id
AND (l_exclusive_mode='Y' OR (l_exclusive_mode='N' AND get_segment_value(jl.code_combination_id,g_bsv_segment) in (SELECT jg_info_v1 FROM jg_zz_vat_trx_gt))); -- need to modify
FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Error While inserting data into I250 register for the Header Id :'||p_journal_header_id);
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
, 'I' -- Block
, jl_br_sped_extr_data_t_s.nextval -- record_seq
, 'I250' -- field 1
, '|'
, get_segment_value(xll.code_combination_id, g_account_segment)
, '|'
, decode(l_cc_exists_flag, 0, NULL, get_segment_value(xll.code_combination_id, g_cost_center_segment))
, '|'
, TRIM(TO_CHAR(xld.UNROUNDED_ACCOUNTED_DR, '99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) --modified as part of ER#11653651
, '|'
, 'D'
, '|'
, p_journal_name
, '|'
, NULL
, '|'
, nvl(xll.description, p_journal_name || '-' || xll.ae_line_num)
, '|'
, get_participant_code(glimp.je_header_id, glimp.je_line_num, p_journal_source, xll.code_combination_id, xll.party_id, xll.party_site_id)
, '|'
, g_created_by
, g_creation_date
, g_last_updated_by
, g_last_update_date
, g_last_update_login
FROM gl_import_references glimp,
xla_ae_lines xll,
xla_ae_headers xlh,
XLA_DISTRIBUTION_LINKS xld
WHERE glimp.je_header_id = p_journal_header_id
AND xlh.ledger_id = g_ledger_id
AND xlh.application_id = xll.application_id
AND xll.gl_sl_link_id = glimp.gl_sl_link_id
AND xll.gl_sl_link_table= glimp.gl_sl_link_table
AND xlh.ae_header_id = xll.ae_header_id
AND xll.ae_header_id = xld.ae_header_id
AND xll.application_id = xld.application_id
AND xlh.EVENT_ID = xld.EVENT_ID
AND xll.ae_line_num = xld.ae_line_num --p_je_line_num
AND xld.UNROUNDED_ACCOUNTED_DR IS NOT NULL
AND (l_exclusive_mode='Y' OR (l_exclusive_mode='N' AND get_segment_value(xll.code_combination_id,g_bsv_segment) in (SELECT jg_info_v1 from jg_zz_vat_trx_gt))); --- Need to modify
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
field9,
separator9,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT g_concurrent_request_id
, 'I' -- Block
, jl_br_sped_extr_data_t_s.nextval -- record_seq
, 'I250' -- field 1
, '|'
, get_segment_value(xll.code_combination_id, g_account_segment)
, '|'
, decode(l_cc_exists_flag, 0, NULL, get_segment_value(xll.code_combination_id, g_cost_center_segment))
, '|'
, TRIM(TO_CHAR(xld.UNROUNDED_ACCOUNTED_CR, '99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
, '|'
, 'C'
, '|'
, p_journal_name
, '|'
, NULL
, '|'
, nvl(xll.description, p_journal_name || '-' || xll.ae_line_num)
, '|'
, get_participant_code(glimp.je_header_id, glimp.je_line_num, p_journal_source, xll.code_combination_id, xll.party_id, xll.party_site_id)
, '|'
, g_created_by
, g_creation_date
, g_last_updated_by
, g_last_update_date
, g_last_update_login
FROM gl_import_references glimp,
xla_ae_lines xll,
xla_ae_headers xlh,
XLA_DISTRIBUTION_LINKS xld
WHERE glimp.je_header_id = p_journal_header_id
AND xlh.ledger_id = g_ledger_id
AND xlh.application_id = xll.application_id
AND xll.gl_sl_link_id = glimp.gl_sl_link_id
AND xll.gl_sl_link_table= glimp.gl_sl_link_table
AND xlh.ae_header_id = xll.ae_header_id
AND xll.ae_header_id = xld.ae_header_id
AND xll.application_id = xld.application_id
AND xlh.EVENT_ID = xld.EVENT_ID
AND xll.ae_line_num = xld.ae_line_num --p_je_line_num
AND xld.UNROUNDED_ACCOUNTED_CR IS NOT NULL
AND (l_exclusive_mode='Y' OR (l_exclusive_mode='N' AND get_segment_value(xll.code_combination_id,g_bsv_segment) in (SELECT jg_info_v1 from jg_zz_vat_trx_gt))); --- Need to modify
FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Error While inserting data into I250 register for the Header Id :'||p_journal_header_id);
l_query := ' SELECT jl.effective_date
,glcc.'||g_account_segment||' natural_acct
,decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||') cost_center_acct
,NVL(SUM(jl.accounted_dr),0) accounted_dr
,NVL(SUM(jl.accounted_cr),0) accounted_cr
FROM gl_je_headers jh
,gl_je_lines jl
,gl_code_combinations glcc
WHERE jh.ledger_id = '||g_ledger_id||'
AND jh.default_effective_date BETWEEN '''||g_start_date||''' AND '''||g_end_date||'''
AND jh.je_header_id = jl.je_header_id
AND jh.actual_flag = ''A''
AND jh.status = ''P''
AND jh.currency_code <> ''STAT'' -- filtering statistic journals. Introduced thid condition for new sped enhancement
AND glcc.code_combination_id = jl.code_combination_id
AND glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||'
AND ('''||l_exclusive_mode||'''=''Y'' OR ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
GROUP BY jl.effective_date
,glcc.'||g_account_segment||'
,decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||')
ORDER BY jl.effective_date
,natural_acct
,cost_center_acct';
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I300' -- field 1
,'|'
,to_char(effective_date(i),'DDMMYYYY')
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login
);
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval --record_seq
,'I310' -- field 1
,'|'
,natural_acct(i)
,'|'
,cost_center_Acct(i)
,'|'
,TRIM(TO_CHAR(accounted_dr(i),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
,'|'
,TRIM(TO_CHAR(accounted_cr(i),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
g_errbuf := 'ERROR While inserting into I300 and I310 registers '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id
,'I' -- Block
,jl_br_sped_extr_data_t_s.nextval -- record_seq
,'I350' -- field 1
,'|'
,to_char(g_end_date,'DDMMYYYY')
,'|'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
g_errbuf := 'ERROR While inserting into I350 register '||SQLERRM;
l_query := 'INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT '|| g_concurrent_request_id||
',''I'' Block
,jl_br_sped_extr_data_t_s.nextval
,''I355''
,''|''
, natural_acct
,''|''
,costcenter_value
,''|''
,amount
,''|''
,amount_flag
,''|''
,'||g_created_by||
','''||g_creation_date||''''||
','||g_last_updated_by||
','''||g_last_update_date||''''||
','||g_last_update_login||
' FROM (SELECT glcc.'||g_account_segment||' natural_acct '|| ',decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||') costcenter_value'||
',TRIM(TO_CHAR(abs(sum(((glb.BEGIN_BALANCE_DR - glb.BEGIN_BALANCE_CR)
+ (glb.PERIOD_NET_DR - glb.PERIOD_NET_CR)))),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) amount
,DECODE(SIGN(sum((glb.BEGIN_BALANCE_DR - glb.BEGIN_BALANCE_CR)
+ (glb.PERIOD_NET_DR - glb.PERIOD_NET_CR))),1,''D'',''C'') amount_flag
FROM gl_balances glb
,gl_code_combinations glcc
WHERE glb.period_name = '''||g_period_name||''''||
' AND glb.code_combination_id = glcc.code_combination_id
AND glb.ledger_id = '||g_ledger_id||
' AND glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||
' AND glcc.account_type in (''E'',''R'')
AND glcc.summary_flag = ''N''
AND glb.actual_flag = ''A''
AND glb.currency_code = '''||g_currency_code||'''
AND ('''||l_exclusive_mode||'''=''Y'' OR ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
GROUP BY glcc.'||g_account_segment||',decode('||l_cc_exists_flag||',0,null,glcc.'||g_cost_center_segment||')
HAVING sum(((glb.BEGIN_BALANCE_DR - glb.BEGIN_BALANCE_CR)
+ (glb.PERIOD_NET_DR - glb.PERIOD_NET_CR)))<>0) ';
g_errbuf := 'ERROR While inserting data into I355 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'I', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'I990', --Register (field1)
'|',
null,--count(*), --field2
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login);
g_errbuf := 'ERROR While inserting data into I990 registers '||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'J', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'J001', --Register (field1)
'|',
0,-- null, --decode(count(*),0,1,0), --field2
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
g_errbuf := 'ERROR While inserting data into J001 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'J', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'J005', --Register (field1)
'|',
to_char(g_start_date,'ddmmyyyy'), --field2
'|',
to_char(g_end_date,'ddmmyyyy'), --field3
'|',
g_acct_stmt_ident,
'|',
decode(g_acct_stmt_ident,2,g_acct_stmt_header,null),
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login);
g_errbuf := 'ERROR While inserting data into J001 register'||SQLERRM;
read balance statement and income statement reports xml data. This JCP program will insert
records into sped extract temparory table with some imp info like row_sequence_number(into field8),
sped extract concurrent request,field1(as J100), and filed6 with amount. So by using
row_sequence stored in J100 record, we need to update other fields of this register.
After all Updations, update field8 as null.
*/
PROCEDURE register_J100 AS
l_api_name CONSTANT VARCHAR2(30) :='register_J100';
SELECT field6, -- amount
field8 -- row_sequence
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
ORDER BY record_seq;
SELECT field8
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
AND field4 IS NULL;
SELECT row_set_id
INTO l_row_set_id
FROM rg_reports
WHERE report_id =g_balance_statement_report_id;
/* SELECT fmt
INTO l_format
FROM (SELECT nvl2(ra.display_format,decode(instr(ra.display_format,'.'),0,'999,999,999,999',
'999,999,999,999.9999'),'999,999,999,999.9999') fmt
FROM rg_reports r,
rg_report_axes_v ra
WHERE r.report_id =g_balance_statement_report_id
AND r.column_set_id =ra.axis_set_id
ORDER BY ra.sequence)
WHERE ROWNUM=1; */
SELECT COUNT(*) -- Checking for account assignements
INTO l_cnt
FROM rg_report_axis_contents
WHERE axis_set_id = l_row_set_id
AND axis_seq = J100_rec.field8;
OPEN acct_cur FOR 'SELECT '||g_account_segment||'_LOW,'||g_account_segment||'_HIGH
FROM rg_report_axis_contents
WHERE axis_set_id ='|| l_row_set_id||
'AND axis_seq ='||J100_rec.field8;
SELECT DISTINCT DECODE(DECODE(vs.flex_value, 'T', 'O', substrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, instrb( fnd_global.newline
||vs.compiled_value_attributes
||fnd_global.newline, fnd_global.newline,1,g_account_qualifier_position)+1, 1 )),'A',1,'L',2,'O',2,null)
INTO l_acct_type
FROM fnd_flex_values vs
WHERE flex_value_set_id = g_account_value_set_id
AND flex_value BETWEEN acct_low AND acct_high;
UPDATE jl_br_sped_extr_data_t tmp
SET (field2, field3,separator3,
field4 ,
separator4,field5, separator5,field6,separator6,
field7, separator7) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description),
r2.number_characters_indented,
'|',
--get_account_type(get_segment_range_value(g_account_segment,r3.axis_set_id,r3.axis_seq,'LOW')), --field4 --account qualifier for segment_low
nvl(l_acct_type,l_prev_acct_type), --field4
'|',
r2.description,
'|',
TRIM(TO_CHAR(l_amount,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),
'|',
decode(l_amount_sign,-1,'C','D'), --field7
'|'
FROM rg_reports r1
,rg_report_axes r2
WHERE r1.report_id = g_balance_statement_report_id
AND r1.row_set_id = r2.axis_set_id
AND r2.axis_seq = J100_rec.field8)
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
AND field8 = J100_rec.field8;
UPDATE jl_br_sped_extr_data_t
SET record_seq = jl_br_sped_extr_data_t_s.nextval,
-- field8 = null,
creation_date = g_creation_date,
created_by = g_created_by,
last_update_date = g_last_update_date,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
AND field8 = J100_rec.field8;
SELECT axis_seq_low
INTO l_acct_axis_seq
FROM rg_report_calculations
WHERE axis_set_id = l_row_set_id
AND axis_seq = l_axis_seq
AND ROWNUM < 2;
SELECT COUNT(*) -- Checking for account assignements
INTO l_cnt
FROM rg_report_axis_contents
WHERE axis_set_id = l_row_set_id
AND axis_seq = l_acct_axis_seq;
SELECT trim(field4)
INTO l_calc_acct_type
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
AND trim(field8) = trim(l_acct_axis_seq);
UPDATE jl_br_sped_extr_data_t
SET field4 = l_calc_acct_type
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100'
AND trim(field8) = trim(calc_rec.field8) ;
UPDATE jl_br_sped_extr_data_t
SET field8 = null
WHERE request_id = g_concurrent_request_id
AND field1 ='J100';
UPDATE jl_br_sped_extr_data_t
SET field4 = null
WHERE request_id = g_concurrent_request_id
AND field1 ='J100'
AND field4 = '0'; --field4=0 means all the accounts in the specified account assignments are of not same acct type.
SELECT field5, -- amount
field8 -- row_sequence
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J150'
ORDER BY record_seq;
SELECT row_set_id
INTO l_row_set_id
FROM rg_reports
WHERE report_id =g_income_statement_report_id;
/* SELECT fmt
INTO l_format
FROM (SELECT nvl2(ra.display_format,decode(instr(ra.display_format,'.'),0,'999,999,999,999',
'999,999,999,999.9999'),'999,999,999,999.9999') fmt
FROM rg_reports r,
rg_report_axes_v ra
WHERE r.report_id =g_income_statement_report_id
AND r.column_set_id =ra.axis_set_id
ORDER BY ra.sequence)
WHERE ROWNUM=1; */
SELECT COUNT(*) -- Checking for account assignements
INTO l_cnt
FROM rg_report_axis_contents
WHERE axis_set_id = l_row_set_id
AND axis_seq = J150_rec.field8;
UPDATE jl_br_sped_extr_data_t tmp
SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
r2.number_characters_indented, --field3
'|',
r2.description, --field4
'|',
TRIM(TO_CHAR(l_amount,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),
'|',
decode(l_amount_sign,-1,'R','D'), --field6
'|'
FROM rg_report_axes r2
WHERE r2.axis_set_id = l_row_set_id
AND r2.axis_seq = J150_rec.field8
AND ROWNUM = 1) --There may exist multiple records in rg_report_contents for a axis_set_id and axis_seq
WHERE request_id = g_concurrent_request_id
AND field1 = 'J150'
AND field8 = J150_rec.field8;
UPDATE jl_br_sped_extr_data_t tmp
SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
r2.number_characters_indented, --field3
'|',
r2.description, --field4
'|',
TRIM(TO_CHAR(l_amount,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),
'|',
decode(l_amount_sign,-1,'P','N'), --field6
'|'
FROM rg_report_axes r2
WHERE r2.axis_set_id = l_row_set_id
AND r2.axis_seq = J150_rec.field8
AND ROWNUM = 1) --There may exist multiple records in rg_report_calculations for a axis_set_id and axis_seq
WHERE request_id = g_concurrent_request_id
AND field1 = 'J150'
AND field8 = J150_rec.field8;
UPDATE jl_br_sped_extr_data_t
SET record_seq = jl_br_sped_extr_data_t_s.nextval,
field8 = null,
creation_date = g_creation_date,
created_by = g_created_by,
last_update_date = g_last_update_date,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE request_id = g_concurrent_request_id
AND field1 = 'J150'
AND field8 = J150_rec.field8;
SELECT DISTINCT media_id file_id
FROM fnd_attached_docs_form_vl
WHERE entity_name = 'GL_JE_HEADERS'
AND pk2_value = p_journal_for_rtf
AND file_name like '%.txt';
SELECT file_data
INTO l_lob_data
FROM FND_LOBS
WHERE file_id = l_file_ids_rec.file_id;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'J',
jl_br_sped_extr_data_t_s.nextval,
decode(l_read_length,1,'J800',null), --filed1
decode(l_read_length,1,'|',null), --seperator1
l_data_var_char, --field2
decode(sign(l_data_length - (l_read_length + l_amount_to_read)),1,null,'|'), --separator2
decode(sign(l_data_length - (l_read_length + l_amount_to_read)),1,null,'J800FIM'), --field3
decode(sign(l_data_length - (l_read_length + l_amount_to_read)),1,null,'|'), --separator3
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
g_errbuf := 'ERROR While inserting data into J800 register'||SQLERRM;
SELECT SUBSTR(meaning,1,80)
INTO l_booktype_desc
FROM fnd_lookups
WHERE lookup_type = 'JLBR_SPED_BOOK_TYPES'
AND lookup_code = g_bookkeeping_type;
SELECT book_number
INTO l_book_number
FROM jl_br_cinfos_books
WHERE ((upper(g_accounting_type) = 'DECENTRALIZED' AND legal_entity_id = g_legal_entity_id AND establishment_id IS NULL)
OR (upper(g_accounting_type) = 'CENTRALIZED' AND g_establishment_id IS NULL AND legal_entity_id = g_legal_entity_id)
OR (upper(g_accounting_type) = 'CENTRALIZED' AND g_establishment_id IS NOT NULL AND legal_entity_id = g_legal_entity_id AND establishment_id=g_establishment_id))
AND bookkeeping_type = substrb(g_bookkeeping_type,1,1)
AND auxiliary_book_flag = 'N';
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
field7,
separator7,
field8,
separator8,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'J', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'J900', --Register (field1)
'|',
'TERMO DE ENCERRAMENTO', -- field2
'|',
l_book_number, --field3
'|',
l_booktype_desc, -- field4
'|',
g_company_name,
-- decode(upper(g_accounting_type),'DECENTRALIZED',g_legal_entity_name,NVL(g_establishment_name,g_legal_entity_name)) , --field5
'|',
null, --field6
'|',
to_char(g_start_date,'ddmmyyyy'), --field7
'|',
to_char(g_end_date,'ddmmyyyy'), --field8
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
g_errbuf := 'ERROR While inserting data into J900 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select g_concurrent_request_id,
'J',
jl_br_sped_extr_data_t_s.nextval,
'J930',
'|',
hp.party_name,
'|',
hp.jgzz_fiscal_code,
'|',
lk.meaning,
'|',
crole.lookup_code,
'|',
hp.person_identifier,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
from xle_entity_profiles le,
xle_contact_legal_roles crole,
hz_parties hp,
xle_lookups lk
where le.legal_entity_id =g_legal_entity_id
and le.party_id = crole.le_etb_party_id
and crole.source_table = 'XLE_ENTITY_PROFILES'
and crole.lookup_type = 'XLE_CONTACT_ROLE'
and crole.contact_party_id = hp.party_id
and lk.lookup_type = crole.lookup_type
and lk.lookup_code = crole.lookup_code;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
field4,
separator4,
field5,
separator5,
field6,
separator6,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select g_concurrent_request_id,
'J',
jl_br_sped_extr_data_t_s.nextval,
'J930',
'|',
hp.party_name,
'|',
hp.jgzz_fiscal_code,
'|',
lk.meaning,
'|',
crole.lookup_code,
'|',
hp.person_identifier,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
from xle_etb_profiles etb,
xle_contact_legal_roles crole,
hz_parties hp,
xle_lookups lk
where etb.legal_entity_id =g_legal_entity_id
and etb.establishment_id =g_establishment_id
and etb.party_id = crole.le_etb_party_id
and crole.source_table = 'XLE_ETB_PROFILES'
and crole.lookup_type = 'XLE_CONTACT_ROLE'
and crole.contact_party_id = hp.party_id
and lk.lookup_type = crole.lookup_type
and lk.lookup_code = crole.lookup_code;
g_errbuf := 'ERROR While inserting data into J930 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'J', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'J990', --Register (field1)
'|',
null, --count(*), --field2
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login);
g_errbuf := 'ERROR While inserting data into J990 register'||SQLERRM;
/* This procedure inserts one record through which we can identify whether data is
reported for block '9' or not
0- Block with data reported;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9001', --Register (field1)
'|',
0,--null, --decode(count(*),0,1,0), --field2
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
g_errbuf := 'ERROR While inserting data into 9001 register'||SQLERRM;
/* This procedure will inserts one row for each register got created by this extract prg.
Each row contains the details like the register_name and total number of lines
created for that register */
PROCEDURE register_9900 AS
l_api_name CONSTANT VARCHAR2(30) :='REGISTER_9900';
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9900', --Register (field1)
'|',
reg, --field2
'|',
cnt,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
FROM (SELECT field1 reg,
COUNT(*) cnt
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 IS NOT NULL
AND field1 <> '9001'
GROUP BY field1);
So need to insert the records 9990 and 9999 registers*/
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9900', --Register (field1)
'|',
'9001', --field2
'|',
1,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9900', --Register (field1)
'|',
'9900', --field2
'|',
1,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9900', --Register (field1)
'|',
'9990', --field2
'|',
1,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
field3,
separator3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9900', --Register (field1)
'|',
'9999', --field2
'|',
1,
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
g_errbuf := 'ERROR While inserting data into 9900 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9990', --Register (field1)
'|',
null, --count(*), --field2
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login );
g_errbuf := 'ERROR While inserting data into 9990 register'||SQLERRM;
INSERT INTO jl_br_sped_extr_data_t
(request_id,
block,
record_seq,
field1,
separator1,
field2,
separator2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES( g_concurrent_request_id,
'9', --block
jl_br_sped_extr_data_t_s.nextval, --record_seq
'9999', --Register (field1)
'|',
null, --count(*), --field2 This field will be updated in update_register_cnt proc at the end of data extraction
'|',
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login);
g_errbuf := 'ERROR While inserting data into 9999 register'||SQLERRM;
PROCEDURE update_register_cnt AS
l_api_name CONSTANT VARCHAR2(30) :='UPDATE_REGISTER_CNT';
/*This procedure is to update the details about number of records inserted into sped temp table.
This will be called at the end of data extraction(after calling all registers) as
the insertion of data will be completed at this time.
*/
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
FROM jl_br_sped_extr_data_t
WHERE block='0'
AND request_id = g_concurrent_request_id)
WHERE field1 = '0001'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE block='0'
AND request_id = g_concurrent_request_id)
WHERE field1 = '0990'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
FROM jl_br_sped_extr_data_t
WHERE block='I'
AND request_id = g_concurrent_request_id)
WHERE field1 = 'I001'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE block='I'
AND request_id = g_concurrent_request_id)
WHERE field1 = 'I990'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
FROM jl_br_sped_extr_data_t
WHERE block='J'
AND request_id = g_concurrent_request_id)
WHERE field1 = 'J001'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field6 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 IS NOT NULL)
WHERE field1 = 'J900'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE block='J'
AND request_id = g_concurrent_request_id
AND field1 IS NOT NULL)
WHERE field1 = 'J990'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
FROM jl_br_sped_extr_data_t
WHERE block='9'
AND request_id = g_concurrent_request_id)
WHERE field1 = '9001'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field3 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE field1='9900'
AND request_id = g_concurrent_request_id)
WHERE field1 = '9900'
AND field2 = '9900'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE block='9'
AND request_id = g_concurrent_request_id)
WHERE field1 = '9990'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field5 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 IS NOT NULL)
WHERE field1 = 'I030'
AND request_id = g_concurrent_request_id;
UPDATE jl_br_sped_extr_data_t
SET field2 = (SELECT COUNT(*)
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 IS NOT NULL)
WHERE field1 = '9999'
AND request_id = g_concurrent_request_id;
END update_register_cnt;
SELECT COUNT(*)
INTO l_cnt
FROM gl_periods gp,
gl_ledgers gl
WHERE gp.period_set_name = gl.period_set_name
AND gl.ledger_id = g_ledger_id
AND g_start_date >= gp.start_date
AND g_end_date <= gp.end_date ;
SELECT COUNT(*)
INTO l_cnt
FROM jl_br_sped_extr_param
WHERE legal_entity_id = g_legal_entity_id
AND bookkeeping_type = g_bookkeeping_type
AND estb_acct_type = g_accounting_type
AND ((g_establishment_id IS NOT NULL AND establishment_id = g_establishment_id )
OR (g_establishment_id IS NULL AND establishment_id IS NULL))
AND period_name = g_period_name
AND report_mode in ('R','F');
SELECT bookkeeping_type
INTO l_prev_bk_type
FROM jl_br_sped_extr_param
WHERE legal_entity_id = g_legal_entity_id
AND estb_acct_type = g_accounting_type
AND ((g_establishment_id IS NOT NULL AND establishment_id = g_establishment_id)
OR (g_establishment_id IS NULL AND establishment_id IS NULL))
AND period_name = g_period_name
AND nvl(CONSOL_MAP_ID,1) = nvl(g_coa_mapping_id,1)
AND data_exist = 'Y'
AND request_id <> g_concurrent_request_id
AND bookkeeping_type like 'A/R';
SELECT bookkeeping_type
INTO l_prev_bk_type
FROM jl_br_sped_extr_param
WHERE legal_entity_id = g_legal_entity_id
AND estb_acct_type = g_accounting_type
AND ((g_establishment_id IS NOT NULL AND establishment_id= g_establishment_id)
OR (g_establishment_id IS NULL AND establishment_id IS NULL))
AND period_name = g_period_name
AND nvl(CONSOL_MAP_ID,1) = nvl(g_coa_mapping_id,1)
AND data_exist = 'Y'
AND request_id <> g_concurrent_request_id
AND bookkeeping_type like 'A/B';
/* After calling I015,I200,I250 registers, a custom procedure will called to insert the data of
non oracle standard journal source's information. Customer will define their journal sources
as lookup values of type 'JLBR_SPED_LEGACY_SOURCES'. So this validation check is just to
confirm that user doesn't define any source which is aready defined as standard oracle journal source.
If user defines any standard oracle journal source in this lookup, program will be terminated with
a error message. */
BEGIN
l_journalsource_check := 0;
SELECT 1
INTO l_journalsource_check
FROM fnd_lookups
WHERE lookup_type = 'JLBR_SPED_LEGACY_SOURCES'
AND lookup_code in ('AX Inventory','AX Payables','AX Receivables','Assets','Average Consolidation',
'Budget Journal','Carryforward','Consolidation','Conversion','Encumbrance',
'Inflation','Intercompany','Inventory','Manual','Manufacturing','MassAllocation',
'Move/Merge','Move/Merge Reversal','Other','Payables','Payroll','Personnel','Projects',
'Purchasing','Receivables','Recurring','Revaluation','Revenue','Spreadsheet','Statistical','Transfer');
SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
FROM JL_BR_SPED_EXTR_DATA_T I155,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I155.request_id = g_concurrent_request_id
AND I155.field1 = 'I155'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I155.field2 = I250.field2 -- Natural Account
AND I155.field3 = I250.field3 -- Cost Center
AND I250.field5 = 'D' --Debit
GROUP BY I250.field2,I250.field3
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
FROM JL_BR_SPED_EXTR_DATA_T I155,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I155.request_id = g_concurrent_request_id
AND I155.field1 = 'I155'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I155.field2 = I250.field2 -- Natural Account
AND I155.field3 = I250.field3 -- Cost Center
AND I250.field5 = 'C' --Credit
GROUP BY I250.field2,I250.field3
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I155.field7,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
SELECT DISTINCT I250.field6 -- Journal Name
FROM JL_BR_SPED_EXTR_DATA_T I200,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I200.request_id = g_concurrent_request_id
AND I200.field1 = 'I200'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I200.field2 = I250.field6 -- Jounral Name || BATCH ID
AND I250.field5 = 'D' --Debit
GROUP BY I250.field6
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I200.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
SELECT DISTINCT I250.field6 -- Journal Name
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T I200,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I200.request_id = g_concurrent_request_id
AND I200.field1 = 'I200'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I200.field2 = I250.field6 -- Jounral Name || BATCH ID
AND I250.field5 = 'C' --Credit
GROUP BY I250.field6
HAVING sum(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
min(to_number(I200.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
SELECT COUNT(*) -- Payables invoices posted in Summary mode.
INTO l_cnt
FROM (SELECT jl.code_combination_id
FROM gl_je_headers jh
,gl_je_lines jl
,gl_import_references glimp
,xla_ae_lines xll
,xla_ae_headers xlh
,xla_distribution_links xld
WHERE jh.ledger_id = g_ledger_id
AND jh.je_source in ('Payables')
AND jh.je_header_id = jl.je_header_id
AND glimp.je_header_id = jh.je_header_id
AND xlh.ae_header_id = xll.ae_header_id
AND xlh.EVENT_ID = xld.EVENT_ID
AND xlh.ae_header_id = xld.ae_header_id
AND jl.je_line_num = glimp.je_line_num
AND glimp.gl_sl_link_id = xll.gl_sl_link_id
AND glimp.gl_sl_link_table = xll.gl_sl_link_table
AND jh.status = 'P'
AND jl.status = 'P'
AND jh.default_effective_date between g_start_date and g_end_date
GROUP BY glimp.je_header_id,glimp.je_line_num,jl.code_combination_id
HAVING count(*) >1);
SELECT COUNT(*) -- Receivables transactions posted in Summary mode.
INTO l_cnt
FROM (SELECT jl.code_combination_id
FROM gl_je_headers jh
,gl_je_lines jl
,gl_import_references glimp
,xla_ae_lines xll
,xla_ae_headers xlh
,xla_distribution_links xld
WHERE jh.ledger_id = g_ledger_id
AND jh.je_source in ('Receivables')
AND jh.je_header_id = jl.je_header_id
AND glimp.je_header_id = jh.je_header_id
AND xlh.ae_header_id = xll.ae_header_id
AND xlh.EVENT_ID = xld.EVENT_ID
AND xlh.ae_header_id = xld.ae_header_id
AND jl.je_line_num = glimp.je_line_num
AND glimp.gl_sl_link_id = xll.gl_sl_link_id
AND glimp.gl_sl_link_table = xll.gl_sl_link_table
AND jh.status = 'P'
AND jl.status = 'P'
AND jh.default_effective_date between g_start_date and g_end_date
GROUP BY glimp.je_header_id,glimp.je_line_num,jl.code_combination_id
HAVING count(*) >1);
SELECT COUNT(*)
INTO l_cnt
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 ='I155';
/* SELECT COUNT(*)
INTO l_cnt
FROM (SELECT to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_sld_ini
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155')
WHERE vl_sld_ini<> 0; --VL_SLD_INI */
SELECT COUNT(*)
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155'
AND field4 <>'0,00'; --vl_sld_ini
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I155',
4,-- 'field4',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_SOMA_SALDO_INICIAL'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
/* SELECT COUNT(*)
INTO l_cnt
FROM (SELECT to_number(field8,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_sld_fin
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155')
WHERE vl_sld_fin<> 0; */
SELECT COUNT(*)
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155'
AND field8 <>'0,00';
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I155',
8,--'field8',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_SOMA_SALDO_FINAL'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
/* SELECT COUNT(*)
INTO l_cnt
FROM (SELECT to_number(field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_deb,
to_number(field7,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_cred
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155')
WHERE vl_deb <> vl_cred; */
SELECT COUNT(*)
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I155'
AND field6 <> field7;
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I155',
6,--'field6',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_DEB_DIF_CRED'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
select count(*) into l_cnt
from (
SELECT
I250.field2,I250.field3,I155.field2,I155.field3
,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
, MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
FROM JL_BR_SPED_EXTR_DATA_T I155,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I155.request_id = g_concurrent_request_id
AND I155.field1 = 'I155'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I155.field2 = I250.field2 -- Natural Account
AND I155.field3 = I250.field3 -- Cost Center
AND I250.field5 = 'D' --Debit
GROUP BY I250.field2,I250.field3,I155.field2,I155.field3
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) -- sum(jounral debit lines) <> Balances debit amount
);
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I155',
6,--'field6',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_VALOR_DEB'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
select count(*) into l_cnt
from (
SELECT
I250.field2,I250.field3,I155.field2,I155.field3
,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
, MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
FROM JL_BR_SPED_EXTR_DATA_T I155,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I155.request_id = g_concurrent_request_id
AND I155.field1 = 'I155'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I155.field2 = I250.field2 -- Natural Account
AND I155.field3 = I250.field3 -- Cost Center
AND I250.field5 = 'C' --Debit
GROUP BY I250.field2,I250.field3,I155.field2,I155.field3
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) -- sum(jounral debit lines) <> Balances debit amount
);
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I155',
6,--'field6',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_VALOR_CRED'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT count(I250.field6) -- Journal Name
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T I200,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I200.request_id = g_concurrent_request_id
AND I200.field1 = 'I200'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I200.field2 = I250.field6 -- Jounral Name || BATCH ID
AND I250.field5 = 'D' --Debit
GROUP BY I250.field6
HAVING SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
MIN(to_number(I200.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I200',
2,--'field2',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_VL_LCTO_DEB'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT COUNT(I250.field6)-- Journal Name
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T I200,
JL_BR_SPED_EXTR_DATA_T I250
WHERE I200.request_id = g_concurrent_request_id
AND I200.field1 = 'I200'
AND I250.request_id = g_concurrent_request_id
AND I250.field1 = 'I250'
AND I200.field2 = I250.field6 -- Jounral Name || BATCH ID
AND I250.field5 = 'C' --Credit
GROUP BY I250.field6
HAVING sum(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')) <>
min(to_number(I200.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I200',
2,--'field2',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_VL_LCTO_CRED'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT COUNT(*)
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I310';
/* SELECT COUNT(*)
INTO l_cnt
FROM (SELECT to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') deb,
to_number(field5,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') cred
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I310')
WHERE deb <> cred; */
SELECT COUNT(*)
INTO l_cnt
FROM JL_BR_SPED_EXTR_DATA_T
WHERE request_id = g_concurrent_request_id
AND field1 = 'I310'
AND field4 <> field5;
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'I',
'I310',
4,--'field4',
FND_MESSAGE.GET,
'REGRA_VALIDACAO_DC_BALANCETE'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT COUNT(*)
INTO l_cnt
FROM jl_br_sped_extr_data_t
WHERE field1 = 'J930'
AND field5 = '900';
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (g_concurrent_request_id,
'J',
'J930',
5,--'field5',
FND_MESSAGE.GET,
'REGRA_OBRIGATORIO_ASSIN_CONTADOR'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT COUNT(*)
INTO l_cnt
FROM jl_br_sped_extr_data_t
WHERE field1 = 'J930'
AND field5 <> '900';
INSERT INTO jl_br_sped_extr_msgs
(request_id,
block,
register,
field,
message_txt,
validation_rule,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (g_concurrent_request_id,
'J',
'J930',
5,--'field5',
FND_MESSAGE.GET,
'REGRA_OBRIGATORIO_ASSIN_CONTADOR'
,g_created_by
,g_creation_date
,g_last_updated_by
,g_last_update_date
,g_last_update_login );
SELECT request_id
INTO l_request_id
FROM jl_br_sped_extr_param
WHERE legal_entity_id = g_legal_entity_id
AND bookkeeping_type = g_bookkeeping_type
AND estb_acct_type = g_accounting_type
AND ((g_establishment_id IS NULL AND establishment_id is NULL) OR
(establishment_id = g_establishment_id AND g_establishment_id IS NOT NULL))
AND period_name = g_period_name
AND request_id <> g_concurrent_request_id
AND data_exist = 'Y';
DELETE --purging the data as user running the extract prg for the combination which is already existing in temp table
FROM jl_br_sped_extr_data_t
WHERE request_id = l_request_id;
UPDATE jl_br_sped_extr_param
SET data_exist = 'N'
WHERE request_id = l_request_id;
/*CURSOR msg_cur IS SELECT message_txt,validation_rule
FROM jl_br_sped_extr_msgs
WHERE request_id = g_concurrent_request_id ;*/
SELECT field2 natural_acct
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field4 like '%####%'
AND field1='I250';
SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
FROM DUAL;
SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
FROM DUAL;
SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
FROM DUAL;
SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
FROM DUAL;
the FSG records with the amount as not null. so Delete those records */
IF l_return THEN
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status: True');
SELECT count(*)
INTO l_cnt
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100';
DELETE FROM jl_br_sped_extr_data_t
WHERE field1 ='J100'
AND request_id = g_concurrent_request_id
AND field6 is null;
DELETE FROM jl_br_sped_extr_data_t
WHERE field1 ='J150'
AND request_id = g_concurrent_request_id
AND field5 is null;
SELECT count(*)
INTO l_cnt
FROM jl_br_sped_extr_data_t
WHERE request_id = g_concurrent_request_id
AND field1 = 'J100';
UPDATE jl_br_sped_extr_data_t
SET field2 = translate(field2, printable_chars|| non_printable_chars, printable_chars),
field3 = translate(field3, printable_chars|| non_printable_chars, printable_chars),
field4 = translate(field4, printable_chars|| non_printable_chars, printable_chars),
field5 = translate(field5, printable_chars|| non_printable_chars, printable_chars),
field6 = translate(field6, printable_chars|| non_printable_chars, printable_chars),
field7 = translate(field7, printable_chars|| non_printable_chars, printable_chars),
field8 = translate(field8, printable_chars|| non_printable_chars, printable_chars),
field9 = translate(field9, printable_chars|| non_printable_chars, printable_chars),
field10 = translate(field10, printable_chars|| non_printable_chars, printable_chars),
field11 = translate(field11, printable_chars|| non_printable_chars, printable_chars),
field12 = translate(field12, printable_chars|| non_printable_chars, printable_chars),
field13 = translate(field13, printable_chars|| non_printable_chars, printable_chars),
field14 = translate(field14, printable_chars|| non_printable_chars, printable_chars),
field15 = translate(field15, printable_chars|| non_printable_chars, printable_chars)
WHERE request_id = g_concurrent_request_id;
update_register_cnt;
UPDATE JL_BR_SPED_EXTR_PARAM -- After successfull data extraction, updating the data_exist column in Parameter's table.
SET data_exist = 'Y'
WHERE request_id = g_concurrent_request_id;