[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
For ALLOCATION, pre_process_update is called only when
pv_reallocate_flag <> 'Y' in validate_process_initiation.
31-May-2006 Aparajita. Version#120.4. xBuild-6
changed comparison with 'Y' to compare against
fnd_api.g_ret_sts_success in get_last_processed_date procedure.
Also in decode, if value is not g_ret_sts_success,
changed from 0 to null as count function is being used.
Added code to hanlde ALL for pv_source
in get_last_processed_date procedure.
2. 23-Jun-2006 Bhavik. Version 120.5 xBuild-7
Added third argument lx_row_id in call to jg_zz_vat_rep_entities_pkg.insert_row API,
as the signature of this API has been changed. Please refer bug# 5166688 for details
regarding change in signature of table handler APIs.
3. 10-jul-2006 Aparajita. Version#120.6. UT bug fix.
Added new value QUERY for parameter pv_called_from of get_reporting_identifier.
This functional is now also used from data templates to get the reporting identifier
to print it in the template. Added a generic value of QUERY for this purpose.
Currently it has the same functionality as that of TABLE HANDLER. However, in this case,
the passed entity would always exist.
4. 29-Dec-2006 Bug: 5584049. Changed signature of get_period_status function. Added parameter p_vat_register_id.
This new parameter will be used for determining the reporting mode of the Italian VAT Registers run.
-------------------------------------------------------------------------- */
/* ================================== start of insert_rows - INTERNAL procedure =============================== */
procedure insert_row
(
xn_reporting_status_id out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
pn_vat_reporting_entity_id in jg_zz_vat_rep_status.vat_reporting_entity_id%type,
pv_tax_registration_number in jg_zz_vat_rep_status.tax_registration_number%type,
pv_tax_calendar_name in jg_zz_vat_rep_status.tax_calendar_name%type,
pn_tax_calendar_year in jg_zz_vat_rep_status.tax_calendar_year%type,
pv_tax_calendar_period in jg_zz_vat_rep_status.tax_calendar_period%type,
pv_source in jg_zz_vat_rep_status.source%type,
pd_period_start_date in jg_zz_vat_rep_status.period_start_date%type,
pd_period_end_date in jg_zz_vat_rep_status.period_end_date%type,
pn_mapping_vat_rep_entity_id in jg_zz_vat_rep_status.mapping_vat_rep_entity_id%type
)
is
begin
insert into jg_zz_vat_rep_status
(
reporting_status_id ,
vat_reporting_entity_id ,
tax_registration_number ,
tax_calendar_name ,
tax_calendar_year ,
tax_calendar_period ,
source ,
period_start_date ,
period_end_date ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
program_login_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
mapping_vat_rep_entity_id /* Revised Approach Change */
)
values
(
jg_zz_vat_rep_status_s.nextval ,
pn_vat_reporting_entity_id ,
pv_tax_registration_number ,
pv_tax_calendar_name ,
pn_tax_calendar_year ,
pv_tax_calendar_period ,
pv_source ,
pd_period_start_date ,
pd_period_end_date ,
fnd_profile.value('CONC_REQUEST_ID') ,
fnd_profile.value('PROG_APPL_ID') ,
fnd_profile.value('CONC_PROGRAM_ID') ,
trunc(sysdate) ,
fnd_profile.value('CONC_LOGIN_ID') ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
pn_mapping_vat_rep_entity_id
) returning reporting_status_id into xn_reporting_status_id ;
end insert_row;
/* ================================== end of insert_rows =============================== */
/* =========================== Start of get_last_processed_date ======================= */
function get_last_processed_date
(
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pv_source in jg_zz_vat_rep_status.source%type,
pv_process_name in varchar2 /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
)
return date is
lv_source jg_zz_vat_rep_status.source%type;
select
max(period_end_date)
from
jg_zz_vat_rep_status
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and ( pv_source = 'ALL' or source = pv_source)
and (
( pv_process_name = 'SELECTION' and selection_status_flag = fnd_api.g_ret_sts_success)
or
( pv_process_name = 'ALLOCATION' and allocation_status_flag = fnd_api.g_ret_sts_success)
or
( pv_process_name = 'FINAL REPORTING' and
final_reporting_status_flag = fnd_api.g_ret_sts_success)
);
pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
)
is
ld_last_processed_date date;
select
min(start_date)
from
gl_periods
where period_set_name = pv_period_set_name
and period_type =
(
select period_type
from gl_periods
where period_set_name = pv_period_set_name
and period_name = pv_tax_calendar_period
);
/* ================================== Start of pre_process_update INTERNAL procedure =============================== */
procedure pre_process_update
(
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pv_tax_calendar_period in gl_periods.period_name%type,
pv_source in jg_zz_vat_rep_status.source%type,
pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
xv_return_status out nocopy varchar2, /* Possible Values : E - Error, S - Successful */
xv_return_message out nocopy varchar2
)
is
begin
If pv_process_name = 'SELECTION' then
update
jg_zz_vat_rep_status
Set
selection_status_flag = null,
Selection_process_id = null,
selection_process_date = null,
allocation_status_flag = null,
allocation_process_id = null,
allocation_process_date = null,
final_reporting_status_flag = null,
final_reporting_process_id = null,
final_reporting_process_date = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
update
jg_zz_vat_rep_status
Set
allocation_status_flag = null,
allocation_process_id = null,
allocation_process_date = null,
final_reporting_status_flag = null,
final_reporting_process_id = null,
final_reporting_process_date = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
update
jg_zz_vat_rep_status
Set
final_reporting_status_flag = null,
final_reporting_process_id = null,
final_reporting_process_date = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
end pre_process_update;
/* ================================== End of pre_process_update =============================== */
/* ================================== Start of validate_process_initiation EXTERNAL procedure=============================== */
procedure validate_process_initiation
(
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pv_tax_calendar_period in gl_periods.period_name%type,
pv_source in jg_zz_vat_rep_status.source%type,
pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
pv_reallocate_flag in varchar2 default null, /* Valid for allocation only, Possible values Y or N or nul */
xn_reporting_status_id_ap out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
xn_reporting_status_id_ar out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
xn_reporting_status_id_gl out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
)
is
lv_source jg_zz_vat_rep_status.source%type;
lv_selection_status_flag jg_zz_vat_rep_status.selection_status_flag%type;
select
reporting_status_id,
nvl(selection_status_flag, 'N') selection_status_flag,
nvl(allocation_status_flag, 'N') allocation_status_flag,
nvl(final_reporting_status_flag, 'N') final_reporting_status_flag,
period_start_date,
period_end_date,
tax_calendar_name
from
jg_zz_vat_rep_status
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and source = pv_source;
select
tax_registration_number,
tax_calendar_name,
enable_allocations_flag,
mapping_vat_rep_entity_id /* Revised Approach change */
from
jg_zz_vat_rep_entities
where
vat_reporting_entity_id = pn_vat_reporting_entity_id;
select
start_date,
end_date,
period_year
from
gl_periods
where
period_set_name = pv_tax_calendar_name
and period_name = pv_tax_calendar_period;
/* Validation#1 : Only selection process can initiate the entire processing, for other processes selection should have run */
if pv_process_name <> 'SELECTION' then
fnd_message.set_name('JG', 'JG_ZZ_VAT_NO_SELECTION');
/* Control comes here only Selection process is being run for the first time for a given combination */
/* Need to insert a record in jg_zz_vat_rep_status, extra info needs to be fetched which is common for all source */
if ld_period_start_date is null then
/* common information across all sources is not fetched at all, so need to fetch */
/* Get the details from gl_periods */
open c_gl_periods (lv_tax_calendar_name, pv_tax_calendar_period);
in selection or final reporting process
check_gap_in_process_period
(
pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
pv_period_set_name => cur_rec_jg_zz_vat_rep_entities.tax_calendar_name,
pv_tax_calendar_period => pv_tax_calendar_period,
pd_start_date => ld_period_start_date,
pd_end_date => ld_period_end_date,
pv_source => lv_source,
pv_process_name => 'SELECTION',
xv_return_status => xv_return_status,
xv_return_message => xv_return_message
);
/* insert a record into jg_zz_vat_rep_status by source */
insert_row
(
xn_reporting_status_id => ln_reporting_status_id ,
pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
pv_tax_registration_number => lv_tax_registration_number ,
pv_tax_calendar_name => lv_tax_calendar_name ,
pn_tax_calendar_year => ln_tax_calendar_year ,
pv_tax_calendar_period => pv_tax_calendar_period ,
pv_source => lv_source ,
pd_period_start_date => ld_period_start_date ,
pd_period_end_date => ld_period_end_date ,
pn_mapping_vat_rep_entity_id => ln_mapping_vat_rep_entity_id
);
if pv_process_name = 'SELECTION' then
/* There should not be any gap in period for selection, but if the record exists in jg_zz_vat_rep_status,
it means that selection had already happened. So this check is not required as it is checked when selection happens first time. */
/* There is no other validation required for SELECTION, need to flush allocation and already selected data if any */
if cur_rec_jg_zz_vat_rep_status.allocation_status_flag <> 'N' then
/* invoke the allocation API to purge the allocation data.*/
jg_zz_vat_alloc_prc_pkg.purge_allocation_data
(
pn_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
pv_reallocate_flag => 'Y', --pv_reallocate_flag, bug#5275230
xv_return_status => xv_return_status,
xv_return_message => xv_return_message
);
/* invoke the selection API to purge the selection data,
control comes here only when selection has already happened, so no need to check the flag */
jg_zz_vat_selection_pkg.purge_tax_data
(
p_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
x_return_status => xv_return_status
);
fnd_message.set_token('PROCESS_DETAILS', ' during purge of previous selection data');
/* Check if selection has already happened successfully */
if cur_rec_jg_zz_vat_rep_status.selection_status_flag <> fnd_api.g_ret_sts_success then
fnd_message.set_name('JG', 'JG_ZZ_VAT_ERROR_SELECTION');
/* Check if selection has already happened successfully */
if cur_rec_jg_zz_vat_rep_status.selection_status_flag <> fnd_api.g_ret_sts_success then
fnd_message.set_name('JG', 'JG_ZZ_VAT_ERROR_SELECTION');
/* All validations have been successful, do the required pre process update.
For allocation process, this update is not required if it is not reallocation.
Allocation is treated separately as unless it is a reallocation or a first time allocation,
only existsing error records are allocated */
if pv_process_name = 'ALLOCATION' and pv_reallocate_flag <> 'Y' then
goto exit_from_procedure;
pre_process_update
(
pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
pv_tax_calendar_period => pv_tax_calendar_period ,
pv_source => pv_source ,
pv_process_name => pv_process_name ,
xv_return_status => xv_return_status ,
xv_return_message => xv_return_message
);
/* ================================== Start of post_process_update EXTERNAL procedure =============================== */
procedure post_process_update
(
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pv_tax_calendar_period in gl_periods.period_name%type,
pv_source in jg_zz_vat_rep_status.source%type,
pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
pn_process_id in jg_zz_vat_rep_status.selection_process_id%type, /* Process id for SELECTION, ALLOCATION, FINAL REPORTING */
pv_process_flag in jg_zz_vat_rep_status.selection_status_flag%type,
pv_enable_allocations_flag in jg_zz_vat_rep_entities.enable_allocations_flag%type default null, /* only for final reporting process */
xv_return_status out nocopy varchar2, /* Possible Values : E - Error, S - Successful */
xv_return_message out nocopy varchar2
)
is
begin
If pv_process_name = 'SELECTION' then
update
jg_zz_vat_rep_status
Set
selection_status_flag = pv_process_flag ,
Selection_process_id = pn_process_id ,
selection_process_date = sysdate ,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate ,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
update
jg_zz_vat_rep_status
Set
allocation_status_flag = pv_process_flag ,
allocation_process_id = pn_process_id ,
allocation_process_date = sysdate ,
last_updated_by = fnd_global.user_id ,
last_update_date = sysdate ,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
update
jg_zz_vat_rep_status
Set
final_reporting_status_flag = pv_process_flag ,
final_reporting_process_id = pn_process_id ,
final_reporting_process_date = sysdate ,
allocation_status_flag =
decode(pv_enable_allocations_flag, null, allocation_status_flag, pv_enable_allocations_flag),
/* if allocation is not applicable , it should be captured here */
last_updated_by = fnd_global.user_id,
last_update_date = sysdate ,
last_update_login = fnd_global.login_id
where
vat_reporting_entity_id = pn_vat_reporting_entity_id
and tax_calendar_period = pv_tax_calendar_period
and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
xv_return_message := 'jg_zz_vat_rep_utility.post_process_update~Unexpected Error -' || sqlerrm;
end post_process_update;
/* ================================== End of post_process_update =============================== */
/* ================================== Start of get_period_status EXTERNAL function ============= */
function get_period_status
(
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pv_tax_calendar_period in gl_periods.period_name%type,
pv_tax_calendar_year in number,
pv_source in jg_zz_vat_rep_status.source%type,
pv_report_name in varchar2,
pv_vat_register_id in jg_zz_vat_registers_b.vat_register_id%type DEFAULT NULL
) return varchar2
is
cursor c_get_count_prelims (pn_vat_reporting_entity_id number,
pv_tax_calendar_period varchar2,
pv_tax_calendar_year number,
pv_source varchar2) is
select count(vat_reporting_entity_id) total_record,
count(decode(final_reporting_status_flag, fnd_api.g_ret_sts_success, 1, null)) final_record
from jg_zz_vat_rep_status
where vat_reporting_entity_id = pn_vat_reporting_entity_id
and ( tax_calendar_period = nvl(pv_tax_calendar_period,'-1')
or tax_calendar_year=nvl(pv_tax_calendar_year,-1))
and ((pv_source = 'AP' AND source =pv_source)
or(pv_source = 'AR' AND source =pv_source)
or(pv_source = 'GL' AND source =pv_source)
or(pv_source = 'AP-AR' AND (source = 'AP' or source ='AR'))
or(pv_source = 'ALL' AND (source = 'AP' or source ='AR'
OR source = 'GL'))
);
select reporting_status_id
from jg_zz_Vat_rep_status
where vat_reporting_entity_id = pv_vat_reporting_entity_id
and (tax_calendar_period = NVL(pv_tax_calendar_period,'-1') or
tax_calendar_year = NVL(pv_tax_calendar_year,-1))
and ((pv_source = 'AP' AND source =pv_source)
or(pv_source = 'AR' AND source =pv_source)
or(pv_source = 'GL' AND source =pv_source)
or(pv_source = 'AP-AR' AND (source = 'AP' or source ='AR'))
or(pv_source = 'ALL' AND (source = 'AP' or source ='AR'
OR source = 'GL'))
);
select substr(lookup_code,instr(lookup_code,'-')+1)
into lv_source
from fnd_lookup_values
where lookup_code like pv_report_name || '%'
and lookup_type = 'JG_ZZ_VAT_REPORT_SOURCE'
and language = 'US';
select 'Y'
into lf_final_flag
from jg_zz_vat_final_reports fin,jg_zz_vat_rep_status rep
where fin.REPORT_NAME = pv_report_name
and NVL(fin.vat_register_id, -1) = NVL(pv_vat_register_id, -1)
and fin.REPORTING_STATUS_ID = rep.reporting_status_id
and rep.vat_reporting_entity_id = pn_vat_reporting_entity_id
and ( rep.TAX_CALENDAR_PERIOD = NVL(pv_tax_calendar_period,'-1')
or rep.TAX_CALENDAR_YEAR = NVL(pv_tax_calendar_year,-1))
and rep.FINAL_REPORTING_STATUS_FLAG = 'S'
and ((lv_source = 'AP' AND rep.source =lv_source)
or(lv_source = 'AR' AND rep.source =lv_source)
or(lv_source = 'GL' AND rep.source =lv_source)
or(lv_source = 'AP-AR' AND (rep.source = 'AP' or rep.source ='AR'))
or(lv_source = 'ALL' AND (rep.source = 'AP' or rep.source ='AR'
OR rep.source = 'GL'))
)
and rownum = 1;
insert into jg_zz_vat_final_reports
(
FINAL_REPORT_ID
, REPORTING_STATUS_ID
, REPORT_NAME
, VAT_REGISTER_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_LOGIN_ID
, LAST_UPDATE_LOGIN
, OBJECT_VERSION_NUMBER
)
values
(
jg_zz_vat_final_reports_s.NEXTVAL
, i.reporting_status_id
, pv_report_name
, pv_vat_register_id
, nvl(fnd_profile.value('USER_ID'),1)
, SYSDATE
, SYSDATE
, nvl(fnd_profile.value('USER_ID'),1)
, nvl(fnd_profile.value('REQUEST_ID'),1)
, nvl(fnd_profile.value('PROGRAM_ID'),1)
, nvl(fnd_profile.value('PROGRAM_APPLICATION_ID'),1)
, nvl(fnd_profile.value('PROGRAM_LOGIN_ID'),1)
, nvl(fnd_profile.value('LOGIN_ID'),1)
, 1
);
select vat_reporting_entity_id
from jg_zz_vat_rep_entities
where entity_type_code = 'ACCOUNTING'
and entity_level_code = pv_entity_level_code
and mapping_vat_rep_entity_id = pn_vat_reporting_entity_id
and ledger_id = pn_ledger_id
and (
(pv_entity_level_code = 'LEDGER')
or
(pv_entity_level_code = 'BSV' and balancing_segment_value = pv_balancing_segment_value)
);
lr_record.last_updated_by := fnd_global.user_id;
lr_record.last_update_date := sysdate;
lr_record.last_update_login := fnd_global.login_id;
jg_zz_vat_rep_entities_pkg.insert_row
(
x_record => lr_record,
x_vat_reporting_entity_id => xn_vat_reporting_entity_id,
x_row_id => lx_row_id
);
select
entity_level_code ,
ledger_id ,
legal_entity_id ,
balancing_segment_value ,
tax_regime_code ,
tax_registration_number ,
mapping_vat_rep_entity_id ,
entity_identifier
from
jg_zz_vat_rep_entities
where
vat_reporting_entity_id = cpn_vat_reporting_entity_id;
select 'LE:' || substr(name, 1, 30) || '-' || legal_entity_id || ':'
from xle_entity_profiles
where legal_entity_id = cpn_legal_entity_id;
select 'LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
from gl_ledgers_public_v
where ledger_id = cpn_ledger_id;
select 'BSV:' || cpv_balancing_segment_value || ':LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
from gl_ledgers_public_v
where ledger_id = cpn_ledger_id;
/* ============================ Start of maintain_selection_entities ============================ */
procedure maintain_selection_entities
(
pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
xn_vat_reporting_entity_id out nocopy number,
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
)
is
lv_return_status varchar2(1);
xv_return_message := 'jg_zz_vat_rep_utility.maintain_selection_entities~Unexpected Error -' || sqlerrm;
end maintain_selection_entities;
/* ============================ End of maintain_selection_entities ============================ */
end jg_zz_vat_rep_utility;