The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT substr(legal.tax_registration_number,3),
legal.legal_entity_id
FROM jg_zz_vat_rep_entities legal,
jg_zz_vat_rep_entities acc
WHERE acc.vat_reporting_entity_id = p_vat_reporting_entity_id
AND ((acc.entity_type_code = 'ACCOUNTING'
AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
OR
(acc.entity_type_code = 'LEGAL'
and acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
);
select lower(ISO_LANGUAGE) from fnd_languages
where language_code = userenv('LANG');
select lower(ISO_LANGUAGE) from fnd_languages
where language_code = userenv('LANG');
select to_char(sysdate,'yyyy-mm-dd') ||'T'
|| to_char(sysdate,'hh:mi:ss')
into l_sysdate
from dual;
select CHART_OF_ACCOUNTS_ID
from gl_sets_of_books
where set_of_books_id = p_sob;
SELECT glp.period_year||'-'||decode(length(glp.period_num),2,to_char(glp.period_num),'0'||to_char(glp.period_num) ) period_num
,glp.start_date
,glp.end_date
,acc.ledger_id
,acc.BALANCING_SEGMENT_VALUE
,acc.entity_level_code
,legal.legal_entity_id
FROM gl_periods glp
,jg_zz_vat_rep_entities legal
,jg_zz_vat_rep_entities acc
WHERE glp.period_set_name = legal.tax_calendar_name
AND acc.vat_reporting_entity_id = p_vat_reporting_entity_id
AND ((acc.entity_type_code = 'ACCOUNTING'
AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
OR
(acc.entity_type_code = 'LEGAL'
AND acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
)
AND period_name = l_rep_period;
'SELECT sum(charges),
sum(revenue),
country,
currency,
rubic_code
FROM
(SELECT sum(nvl(ap_dist.amount,0)) charges,
0 revenue,
ven.country country,
ap_inv.INVOICE_CURRENCY_CODE currency,
ap_inv.invoice_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM
ap_invoices_all ap_inv,
ap_supplier_sites_all ven ,
ap_invoice_distributions_all ap_dist ,
gl_code_combinations glcc,
fnd_lookup_values lv
WHERE ap_inv.vendor_site_id = ven.vendor_site_id
AND ap_dist.invoice_id = ap_inv.invoice_id
AND ap_dist.dist_code_combination_id = glcc.code_combination_id
AND glcc.chart_of_accounts_id = :v_coa_id1
AND glcc.'||l_seg_name||' = lv.lookup_code
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND lv.LANGUAGE = USERENV(''LANG'')
AND ap_inv.set_of_books_id = :v_sob_id1
AND ven.country <> ''BE''
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
ap_dist.ACCRUAL_POSTED_FLAG,
ap_dist.CASH_POSTED_FLAG,
ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
ven.country ,
ap_inv.invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
UNION ALL
SELECT 0 charges,
sum(nvl(AMOUNT,0)) revenue,
hzl.country country,
invoice_currency_code currency,
trx.customer_trx_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
ra_customer_trx_all trx ,
HZ_CUST_ACCOUNTS hz_cust ,
hz_parties parties ,
hz_party_sites hz_ps ,
hz_locations hzl ,
hz_cust_site_uses_all hz_csu ,
hz_cust_acct_sites_all hz_cas ,
gl_code_combinations glcc,
fnd_lookup_values lv
WHERE
gld.customer_trx_id = trx.customer_trx_id
AND trx.BILL_TO_customer_ID = HZ_CUST.cust_account_id
AND hz_cust.party_id = parties.party_id
AND hz_cas.cust_account_id = HZ_CUST.cust_account_id
AND trx.BILL_TO_SITE_USE_ID = HZ_CSU.SITE_USE_ID
AND hz_cas.cust_acct_site_id = hz_csu.cust_acct_site_id
AND hz_ps.party_site_id = hz_cas.party_site_id
AND hz_ps.party_id = parties.party_id
AND hz_ps.location_id = hzl.location_id
AND gld.code_combination_id = glcc.code_combination_id
AND glcc.chart_of_accounts_id = :v_coa_id2
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND glcc.'||l_seg_name||' = lv.lookup_code
AND lv.LANGUAGE = USERENV(''LANG'')
AND trx.set_of_books_id = :v_sob_id2
AND hzl.country <> ''BE''
AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
AND trx.complete_flag = ''Y''
AND gld.posting_control_id <> -3
GROUP BY hzl.COUNTRY,
invoice_currency_code,
trx.customer_trx_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
)
GROUP BY country, currency, rubic_code ';
'SELECT sum(charges),
sum(revenue),
country,
currency,
rubic_code
FROM
(SELECT sum(nvl(ap_dist.amount,0)) charges,
0 revenue,
ven.country country,
ap_inv.INVOICE_CURRENCY_CODE currency,
ap_inv.invoice_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM
ap_invoices_all ap_inv,
ap_supplier_sites_all ven ,
ap_invoice_distributions_all ap_dist ,
gl_code_combinations glcc,
fnd_lookup_values lv
WHERE ap_inv.vendor_site_id = ven.vendor_site_id
AND ap_dist.invoice_id = ap_inv.invoice_id
AND ap_dist.dist_code_combination_id = glcc.code_combination_id
AND glcc.chart_of_accounts_id = :v_coa_id1
AND glcc.'||l_seg_name||' = lv.lookup_code
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND lv.LANGUAGE = USERENV(''LANG'')
AND ap_inv.set_of_books_id = :v_sob_id1
AND JE_BE_CSSR_PKG.get_bsv(ap_inv.set_of_books_id,glcc.chart_of_accounts_id,ap_dist.dist_code_combination_id) = :bsv1
AND ven.country <> ''BE''
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
ap_dist.ACCRUAL_POSTED_FLAG,
ap_dist.CASH_POSTED_FLAG,
ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
ven.country ,
ap_inv.invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
UNION ALL
SELECT 0 charges,
sum(nvl(AMOUNT,0)) revenue,
hzl.country country,
invoice_currency_code currency,
trx.customer_trx_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
ra_customer_trx_all trx ,
HZ_CUST_ACCOUNTS hz_cust ,
hz_parties parties ,
hz_party_sites hz_ps ,
hz_locations hzl ,
hz_cust_site_uses_all hz_csu ,
hz_cust_acct_sites_all hz_cas ,
gl_code_combinations glcc,
fnd_lookup_values lv
WHERE
gld.customer_trx_id = trx.customer_trx_id
AND trx.BILL_TO_customer_ID = HZ_CUST.cust_account_id
AND hz_cust.party_id = parties.party_id
AND hz_cas.cust_account_id = HZ_CUST.cust_account_id
AND trx.BILL_TO_SITE_USE_ID = HZ_CSU.SITE_USE_ID
AND hz_cas.cust_acct_site_id = hz_csu.cust_acct_site_id
AND hz_ps.party_site_id = hz_cas.party_site_id
AND hz_ps.party_id = parties.party_id
AND hz_ps.location_id = hzl.location_id
AND gld.code_combination_id = glcc.code_combination_id
AND glcc.chart_of_accounts_id = :v_coa_id2
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND glcc.'||l_seg_name||' = lv.lookup_code
AND lv.LANGUAGE = USERENV(''LANG'')
AND trx.set_of_books_id = :v_sob_id2
AND JE_BE_CSSR_PKG.get_bsv(trx.set_of_books_id,glcc.chart_of_accounts_id,gld.code_combination_id) = :bsv2
AND hzl.country <> ''BE''
AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
AND trx.complete_flag = ''Y''
AND gld.posting_control_id <> -3
GROUP BY hzl.COUNTRY,
invoice_currency_code,
trx.customer_trx_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
)
GROUP BY country, currency, rubic_code ';
'SELECT sum(charges),
sum(revenue),
country,
currency,
rubic_code
FROM
(SELECT sum(nvl(ap_dist.amount,0)) charges,
0 revenue,
ven.country country,
ap_inv.INVOICE_CURRENCY_CODE currency,
ap_inv.invoice_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM ap_invoices_all ap_inv,
ap_supplier_sites_all ven ,
ap_invoice_distributions_all ap_dist ,
fnd_lookup_values lv,
gl_ledgers glr
WHERE ap_inv.legal_entity_id = :p_legal_entity_id1
AND ap_inv.vendor_site_id = ven.vendor_site_id
AND ap_dist.invoice_id = ap_inv.invoice_id
AND glr.ledger_id = ap_inv.set_of_books_id
AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id,ap_dist.dist_code_combination_id) = lv.lookup_code
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND lv.LANGUAGE = USERENV(''LANG'')
AND ven.country <> ''BE''
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
ap_dist.ACCRUAL_POSTED_FLAG,
ap_dist.CASH_POSTED_FLAG,
ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
ven.country ,
ap_inv.invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
UNION ALL
SELECT 0 charges,
sum(nvl(AMOUNT,0)) revenue,
hzl.country country,
invoice_currency_code currency,
trx.customer_trx_id invoice_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
FROM ra_cust_trx_line_gl_dist_all gld ,
ra_customer_trx_all trx ,
hz_cust_accounts hz_cust,
hz_cust_site_uses_all hz_csu,
hz_cust_acct_sites_all hz_cas,
hz_parties parties,
hz_party_sites hz_ps,
hz_locations hzl,
fnd_lookup_values lv,
gl_ledgers glr
WHERE trx.legal_entity_id = :p_legal_entity_id2
AND gld.customer_trx_id = trx.customer_trx_id
AND trx.bill_to_customer_id = hz_cust.cust_account_id
AND hz_cust.party_id = parties.party_id
AND hz_cas.cust_account_id = hz_cust.cust_account_id
AND trx.bill_to_site_use_id = hz_csu.site_use_id
AND hz_cas.cust_acct_site_id = hz_csu.cust_acct_site_id
AND hz_ps.party_site_id = hz_cas.party_site_id
AND hz_ps.party_id = parties.party_id
AND hz_ps.location_id = hzl.location_id
AND trx.set_of_books_id = glr.ledger_id
AND lv.lookup_type = ''JEBE_NBBN_CODES''
AND lv.LANGUAGE = USERENV(''LANG'')
AND hzl.country <> ''BE''
AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
AND trx.complete_flag = ''Y''
AND gld.posting_control_id <> -3
AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id, gld.code_combination_id ) = lv.lookup_code
GROUP BY hzl.COUNTRY,
invoice_currency_code,
trx.customer_trx_id,
decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
)
GROUP BY country, currency, rubic_code ';
SELECT application_column_name
INTO l_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 = p_choac_id;
EXECUTE IMMEDIATE 'SELECT '||l_segment ||
' FROM gl_code_combinations '||
' WHERE code_combination_id = '||p_cc_id
INTO bal_segment_value;
SELECT application_column_name
INTO l_segment
FROM FND_SEGMENT_ATTRIBUTE_VALUES
WHERE id_flex_num = p_coa_id --50714
AND segment_attribute_type = 'GL_ACCOUNT'
AND id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND application_id = 101;
EXECUTE IMMEDIATE 'SELECT '||l_segment ||
' FROM gl_code_combinations '||
' WHERE code_combination_id = '||p_cc_id||
' AND chart_of_accounts_id = '||p_coa_id
INTO l_segment_value;