[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)
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);
SELECT gllev.currency_code
,arsp.tax_registration_number
FROM gl_ledger_le_v gllev
,ar_system_parameters arsp
WHERE gllev.legal_entity_id = gn_legal_entity_id
AND arsp.set_of_books_id = gllev.ledger_id
AND gllev.ledger_category_code = 'PRIMARY';
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
, NULL phone_number
, xle_auth.city tax_office_location
, xle_auth.address2 tax_office_number
, xle_auth.address3 tax_office_code
, xle_reg.issuing_authority_id
, xle_auth.party_id
FROM xle_firstparty_information_v xfpiv
, xle_registrations xle_reg
-- , hz_parties hzp -- Bug 5522964
, xle_legalauth_v xle_auth
WHERE xle_reg.source_id = xfpiv.legal_entity_id
AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
AND xle_auth.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;
SELECT ract.customer_trx_id
,ract.cust_trx_type_id
,ract.trx_number
,ract.trx_date
,ract.sold_to_customer_id
,ract.bill_to_customer_id
,ract.exchange_rate
,ract.printing_original_date
,ract.previous_customer_trx_id
,ract.complete_flag
,racgd.cust_trx_line_gl_dist_id --14249544
,ractl.customer_trx_line_id
,ractl.line_number
,ractl.line_type
,ractl.link_to_cust_trx_line_id
,ractl.extended_amount
,ractl.vat_tax_id
,sum(racgd.acctd_amount) acctd_amount
,sum(racgd.amount) amount
FROM ra_customer_trx ract
,ra_customer_trx_lines ractl
,ra_cust_trx_line_gl_dist racgd
,jg_zz_vat_rep_entities repent
WHERE repent.vat_reporting_entity_id = P_REPORTING_ENTITY_ID
AND (( repent.entity_type_code = 'LEGAL' AND
ract.legal_entity_id = gn_legal_entity_id )
OR
( repent.entity_type_code = 'ACCOUNTING' AND
repent.entity_level_code = 'LEDGER' AND
ract.set_of_books_id = gv_ledger_id )
OR
( repent.entity_type_code = 'ACCOUNTING' AND
repent.entity_level_code = 'BSV' AND
ract.set_of_books_id = gv_ledger_id AND
get_bsv(racgd.code_combination_id) = gv_balancing_segment_value ))
AND racgd.customer_trx_line_id = ractl.customer_trx_line_id
AND racgd.customer_trx_id = ractl.customer_trx_id
AND ractl.customer_trx_id = ract.customer_trx_id
-- AND nvl(racgd.CCID_CHANGE_FLAG,'Y') <>'N' -- Bug 14249544
GROUP BY ract.customer_trx_id
,ract.cust_trx_type_id
,ract.trx_number
,ract.trx_date
,ract.sold_to_customer_id
,ract.bill_to_customer_id
,ract.exchange_rate
,ract.printing_original_date
,ract.previous_customer_trx_id
,racgd.cust_trx_line_gl_dist_id
,ract.complete_flag
,ractl.customer_trx_line_id
,ractl.line_number
,ractl.line_type
,ractl.link_to_cust_trx_line_id
,ractl.extended_amount
,ractl.vat_tax_id ;
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;
SELECT hzp.primary_phone_area_code
||' '|| hzp.primary_phone_country_code
||' '|| hzp.primary_phone_number phone_number
INTO gv_repent_phone_number
FROM hz_parties hzp
WHERE hzp.party_id = lv_party_id;
fnd_file.put_line(fnd_file.log,'Before Insert INTO JG_ZZ_VAT_TRX_GT table' ) ;
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_d1
, jg_info_n3
, jg_info_n4
, jg_info_n5
, jg_info_d2
, jg_info_n6
, jg_info_v2
, jg_info_n7
, jg_info_n8
, jg_info_v3
, jg_info_n9
, jg_info_n10
, jg_info_n11
, jg_info_n12
, jg_info_n13
, jg_info_n14
)
values ( r_inv_lines.customer_trx_id
,r_inv_lines.cust_trx_type_id
,r_inv_lines.trx_number
,r_inv_lines.trx_date
,r_inv_lines.sold_to_customer_id
,r_inv_lines.bill_to_customer_id
,r_inv_lines.exchange_rate
,r_inv_lines.printing_original_date
,r_inv_lines.previous_customer_trx_id
,r_inv_lines.complete_flag
,r_inv_lines.customer_trx_line_id
,r_inv_lines.line_number
,r_inv_lines.line_type
,r_inv_lines.link_to_cust_trx_line_id
,r_inv_lines.extended_amount
,r_inv_lines.vat_tax_id
,r_inv_lines.acctd_amount
,r_inv_lines.amount
,r_inv_lines.cust_trx_line_gl_dist_id
);
select count(*) INTO lv_count from jg_zz_vat_trx_gt;
fnd_file.put_line(fnd_file.log,'Number of records inserted INTO JG_ZZ_VAT_TRX_GT table: ' || lv_count );
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;