[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
,nvl(cfg.tax_registration_number,cfgd.tax_registration_number) repent_trn
,min(glp.start_date)
,max(glp.end_date)
,nvl(cfg.entity_identifier,cfgd.entity_identifier) entity_identifier
FROM jg_zz_vat_rep_entities cfg
,jg_zz_vat_rep_entities cfgd
,gl_periods glp
WHERE cfg.vat_reporting_entity_id = p_reporting_entity_id
and (
( cfg.entity_type_code = 'ACCOUNTING'
and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
)
or
( cfg.entity_type_code = 'LEGAL'
and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
)
)
AND glp.period_set_name = nvl(cfg.tax_calendar_name,cfgd.tax_calendar_name)
AND glp.period_year = p_fiscal_year
GROUP BY nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
,nvl(cfg.tax_registration_number,cfgd.tax_registration_number)
,nvl(cfg.entity_identifier,cfgd.entity_identifier);
SELECT gllev.currency_code
,gl.name
,fsp.vat_registration_num
,fsp.vat_country_code
FROM gl_ledger_le_v gllev
,gl_ledgers gl
,financials_system_parameters fsp
WHERE gllev.ledger_category_code='PRIMARY'
AND gllev.legal_entity_id = gn_legal_entity_id
AND gl.ledger_id = gllev.ledger_id
AND fsp.set_of_books_id = gllev.ledger_id ;
SELECT gl.name
FROM gl_ledger_le_v glle,
gl_ledgers gl
WHERE
glle.legal_entity_id = gn_legal_entity_id
AND gl.ledger_id = glle.ledger_id ;
SELECT xfpiv.registration_number
, xfpiv.name
, xfpiv.address_line_1
, xfpiv.address_line_2
, xfpiv.address_line_3
, xfpiv.town_or_city
, xfpiv.postal_code
, xfpiv.country
, hzp.primary_phone_area_code
||' '|| hzp.primary_phone_country_code
||' '|| hzp.primary_phone_number phone_number
, xlelav.city tax_office_location
, xlelav.address2 tax_office_number
, xlelav.address3 tax_office_code
FROM XLE_FIRSTPARTY_INFORMATION_V xfpiv
,xle_registrations xle_reg
, hz_parties hzp
, xle_legalauth_v xlelav
WHERE xle_reg.source_id = xfpiv.legal_entity_id
AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
AND xlelav.legalauth_id = xle_reg.issuing_authority_id
AND xle_reg.identifying_flag = 'Y'
AND xfpiv.legislative_cat_code = 'INCOME_TAX'
AND xfpiv.legal_entity_id = gn_legal_entity_id
AND hzp.party_id = xlelav.party_id;
SELECT
i.invoice_id
,i.vendor_id
,i.vendor_site_id
,i.invoice_date
,i.invoice_currency_code
,i.invoice_type_lookup_code
,i.legal_entity_id
,i.doc_sequence_value
,il.line_number
,il.line_type_lookup_code
,id.period_name
,nvl(id.tax_code_id,get_item_tax_code_id(id.invoice_id,id.invoice_distribution_id)) tax_code_id
,id.match_status_flag
,id.charge_applicable_to_dist_id
,id.invoice_distribution_id
,id.merchant_taxpayer_id
,id.line_type_lookup_code id_line_type_lookup_code
-- ,sum(id.stat_amount) stat_amount Commented for Bug 5750278
,sum(il.assessable_value) taxable_amount --Added for 5750278
,sum(id.amount) amount
,sum(id.base_amount) base_amount
FROM
ap_invoices i,
ap_invoice_lines il,
ap_invoice_distributions id,
gl_code_combinations gl,
jg_zz_vat_rep_entities repent
WHERE repent.vat_reporting_entity_id = p_reporting_entity_id
and ( ( repent.entity_type_code = 'LEGAL' AND i.legal_entity_id = gn_legal_entity_id )
or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'LEDGER' AND
i.set_of_books_id = gv_ledger_id)
or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'BSV'
and i.set_of_books_id = gv_ledger_id
and get_bsv(id.dist_code_combination_id) = gv_balancing_segment_value ) )
and i.invoice_id = il.invoice_id
and i.invoice_id = id.invoice_id
and il.line_number = id.invoice_line_number
and id.dist_code_combination_id = gl.code_combination_id
and ( (P_called_from = 'JEITRAVL' and to_char(i.invoice_date, 'YYYY') in
(to_char(gd_period_end_date, 'YYYY'),to_char(add_months(gd_period_end_date,-12),'YYYY')))
or (P_called_from = 'JEPTAPVR' and
id.accounting_date between to_date('01/01/' || to_char(p_fiscal_year),'DD/MM/YYYY')
and to_date('31/12/' || to_char(p_fiscal_year),'DD/MM/YYYY'))
)
GROUP BY
i.invoice_id
,i.vendor_id
,i.vendor_site_id
,i.invoice_date
,i.invoice_currency_code
,i.invoice_type_lookup_code
,i.legal_entity_id
,i.doc_sequence_value
,il.line_number
,il.line_type_lookup_code
,id.period_name
,nvl(id.tax_code_id,get_item_tax_code_id(id.invoice_id,id.invoice_distribution_id))
,id.match_status_flag
,id.charge_applicable_to_dist_id
,id.invoice_distribution_id
,id.merchant_taxpayer_id
,id.line_type_lookup_code;
SELECT repent.ledger_id,
repent.balancing_segment_value,
gl.CHART_OF_ACCOUNTS_ID
FROM jg_zz_vat_rep_entities repent
,gl_ledgers gl
WHERE vat_reporting_entity_id = p_reporting_entity_id
AND gl.ledger_id = repent.ledger_id;
INSERT INTO jg_zz_vat_trx_gt
(jg_info_n1 ,
jg_info_n2 ,
jg_info_n3 ,
jg_info_d1 ,
jg_info_v1 ,
jg_info_v2 ,
jg_info_n4 ,
jg_info_n5 ,
jg_info_n6 ,
jg_info_v3 ,
jg_info_v4 ,
jg_info_n7 ,
jg_info_v5 ,
jg_info_n8 , --stat_amount Now Taxable Amount Bug 5750278
jg_info_n9 , --amount
jg_info_n10, --base_amount
jg_info_n11, --charge_applicable_to_dist_id
jg_info_n12, --invoice_distribution_id
jg_info_v7, --merchant_taxpayer_id
jg_info_v6 --id_line_type_lookup_code
)
VALUES(
r_inv_lines.invoice_id
, r_inv_lines.vendor_id
, r_inv_lines.vendor_site_id
, r_inv_lines.invoice_date
, r_inv_lines.invoice_currency_code
, r_inv_lines.invoice_type_lookup_code
, r_inv_lines.legal_entity_id
, r_inv_lines.doc_sequence_value
, r_inv_lines.line_number
, r_inv_lines.line_type_lookup_code
, r_inv_lines.period_name
, r_inv_lines.tax_code_id
, r_inv_lines.match_status_flag
-- , r_inv_lines.stat_amount -- Commented for Bug 5750278
, r_inv_lines.taxable_amount -- Added for Bug 5750278
, r_inv_lines.amount
, r_inv_lines.base_amount
, r_inv_lines.charge_applicable_to_dist_id
, r_inv_lines.invoice_distribution_id
, r_inv_lines.merchant_taxpayer_id
, r_inv_lines.id_line_type_lookup_code
);
fnd_file.put_line(fnd_file.log,' After inserting the data into Global Temp Table');
SELECT application_column_name
INTO l_segment
FROM fnd_segment_attribute_values ,
gl_ledgers gl
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING'
AND application_id = 101
AND id_flex_num = gl.chart_of_accounts_id
AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
AND gl.ledger_id = gv_ledger_id;
EXECUTE IMMEDIATE 'SELECT '||l_segment ||
' FROM gl_code_combinations '||
' WHERE code_combination_id = '||ccid
INTO bal_segment_value;
SELECT distinct tax_code_id
FROM ap_invoice_distributions
WHERE invoice_id = p_inv_id
AND charge_applicable_to_dist_id = p_inv_dist_id;