[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
C_LINES_PER_INSERT CONSTANT NUMBER := 1000;
g_last_updated_by number(15);
g_last_update_date date;
g_last_update_login number(15);
SELECT multi_org_flag
FROM fnd_product_groups;
PROCEDURE delete_all(p_request_id in number );
PROCEDURE insert_rep_context_itf(
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
p_rep_entity_id IN NUMBER );
SELECT COUNT(detail_tax_line_id)
INTO l_count
FROM zx_rep_trx_detail_t;
SELECT COUNT(actg_ext_line_id)
INTO l_count
FROM zx_rep_actg_ext_t;
SELECT COUNT(detail_tax_line_ext_id)
INTO l_count
FROM zx_rep_trx_jx_ext_t;
SELECT COUNT(rep_context_id)
INTO l_count
FROM zx_rep_context_t;
delete_all(l_trl_global_variables_rec.request_id);
SELECT floor((l_end_time-l_start_time)*24)
|| ':' ||
mod(floor((l_end_time-l_start_time)*24*60),60)
|| ':' ||
mod(floor((l_end_time-l_start_time)*24*60*60),60)
time_difference
INTO l_total_time
FROM dual;
SELECT Nvl(MIN(TAX_RATE_CODE),P_TAX_RATE_CODE_LOW), Nvl(MAX(TAX_RATE_CODE),P_TAX_RATE_CODE_HIGH)
INTO P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_LOW, P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_HIGH
FROM ZX_RATES_B
WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
AND TAX = P_TAX
AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE
AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
AND tax_rate_code BETWEEN P_TAX_RATE_CODE_LOW AND P_TAX_RATE_CODE_HIGH
AND (P_TAX_RATE_CODE_LOW IN
(SELECT tax_rate_code
FROM ZX_RATES_B
WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
AND TAX = P_TAX
AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE)
OR P_TAX_RATE_CODE_HIGH IN
(SELECT tax_rate_code
FROM ZX_RATES_B
WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
AND TAX = P_TAX
AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE)
);
g_last_updated_by := nvl(fnd_profile.value('USER_ID'),1);
g_last_update_date := sysdate;
g_last_update_login := 1;
SELECT ledger_id
FROM gl_ledger_le_v
WHERE legal_entity_id = c_legal_entity_id
AND ledger_category_code = 'PRIMARY';
SELECT chart_of_accounts_id, name, currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_ledger_id;
SELECT start_date
INTO p_trl_global_variables_rec.gl_date_low
FROM gl_period_statuses
WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_low)
AND set_of_books_id = g_ledger_id
AND application_id = 101;
SELECT To_Date(To_Char(end_date,'DD-MM-YYYY')||' 23:59:59','DD-MM-YYYY HH24:MI:SS')
INTO p_trl_global_variables_rec.gl_date_high
FROM gl_period_statuses
WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_high)
AND set_of_books_id = g_ledger_id
AND application_id = 101;
SELECT start_date
INTO p_trl_global_variables_rec.trx_date_low
FROM gl_period_statuses
WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_low
AND set_of_books_id = g_ledger_id
AND application_id = 101;
SELECT end_date
INTO p_trl_global_variables_rec.trx_date_high
FROM gl_period_statuses
WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_high
AND set_of_books_id = g_ledger_id
AND application_id = 101;
SELECT count(*) INTO l_count
FROM zx_rep_context_t
WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
SELECT organization_id
FROM hr_operating_units
WHERE mo_global.check_access(organization_id) = 'Y'
AND SET_OF_BOOKS_ID = c_ledger_id ;
'Before call to insert_rep_context_itf');
insert_rep_context_itf(
p_trl_global_variables_rec,
p_trl_global_variables_rec.legal_entity_id);
'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info : Call to insert_rep_context_itf');
insert_rep_context_itf(p_trl_global_variables_rec,l_operating_unit_id);
insert_rep_context_itf(
p_trl_global_variables_rec,
l_operating_unit_id);
ZX_AR_ACTG_EXTRACT_PKG.insert_tax_data (
P_MRC_SOB_TYPE => P_ledger_type,
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
ZX_AP_ACTG_EXTRACT_PKG.insert_tax_data (
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
ZX_GL_EXTRACT_PKG.insert_tax_data(
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
ZX_AR_EXTRACT_PKG.insert_tax_data (
P_MRC_SOB_TYPE => P_ledger_type,
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
ZX_AP_EXTRACT_PKG.insert_tax_data (
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
ZX_GL_EXTRACT_PKG.insert_tax_data(
P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
);
SELECT rep_context.rep_context_id , rep_context.rep_entity_id ,
mo.operating_unit_id
FROM zx_rep_context_t rep_context,
fnd_mo_reporting_entities_v mo
WHERE rep_context.rep_entity_id = mo.operating_unit_id
AND mo.ledger_id = c_set_of_books_id
AND rep_context.request_id = c_request_id
AND mo.reporting_level = '3000';
SELECT rep_context.rep_context_id,
rep_context.rep_entity_id ,
rep_context.rep_entity_id
FROM zx_rep_context_t rep_context
WHERE rep_context.request_id = c_request_id;
rep_context_id_tab.delete;
ZX_AR_POPULATE_PKG.update_additional_info(
P_TRL_GLOBAL_VARIABLES_REC);
ZX_AP_POPULATE_PKG.UPDATE_ADDITIONAL_INFO(
P_TRL_GLOBAL_VARIABLES_REC );
ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO(
P_TRL_GLOBAL_VARIABLES_REC );
| This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
| which do not have any child records in AR_TAX_EXTRACT_SUB_ITF |
| for the given request_id |
| |
| Called from ZX_EXTRACT_PKG.POPULATE |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
+===========================================================================*/
PROCEDURE CLEANUP (
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
) IS
j number := 0;
DELETE FROM AR_TAX_EXTRACT_SUB_ITF WHERE EXTRACT_LINE_ID IN
(select extract_line_id
from ar_tax_extract_sub_itf
where request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
and (trx_id is null
or trx_class_code is null
-- or tax_code_id is null
or tax_code_register_type_code is null
or extract_source_ledger is null
or extract_report_line_number is null))
RETURNING
extract_line_id,
trx_id,
trx_class_code,
tax_code_id,
tax_code_register_type_code,
extract_source_ledger,
extract_report_line_number
BULK COLLECT INTO
l_extract_line_id,
l_trx_id,
l_trx_class_code,
l_tax_code_id,
l_tax_cd_register_type_cd,
l_extract_source_ledger,
l_extract_report_line_num;
arp_util_tax.debug(to_char(nvl(l_count,0))||' records deleted because '
||'mandatory columns are not populated . ');
declarer_id_lookup_table.delete;
DELETE FROM AR_TAX_EXTRACT_SUB_ITF
WHERE TAX_EXTRACT_DECLARER_ID IS NULL
AND REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
DELETE FROM zx_rep_trx_detail_t i
WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
AND rep_context_id is null;
| DELETE_ALL |
| |
| DESCRIPTION |
| This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
| and AR_TAX_EXTRACT_SUB_ITF for a given request_id. This procedure |
| is called from the procedure populate if some fatal error condition |
| occurs and error_code is set to 2 |
| |
| Called from ZX_EXTRACT_PKG.POPULATE |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
+===========================================================================*/
PROCEDURE DELETE_ALL(P_REQUEST_ID IN NUMBER ) IS
BEGIN
/* IF PG_DEBUG = 'Y' THEN
arp_util_tax.debug('DELETE_ALL(+) ');
delete from ar_tax_extract_sub_itf sub_itf
where
sub_itf.request_id = P_REQUEST_ID
and exists
(select dcl_itf.tax_extract_declarer_id
from ar_tax_extract_dcl_itf dcl_itf
where sub_itf.tax_extract_declarer_id=dcl_itf.tax_extract_declarer_id);
delete from ar_tax_extract_dcl_itf where request_id = P_REQUEST_ID;
| insert_rep_context_itf |
| |
| DESCRIPTION |
| This PROCEDURE inserts Reporting Context information INTO |
| zx_rep_context_t |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 08-Feb-2005 Srinivasa Rao Korrapati Created |
+===========================================================================*/
PROCEDURE insert_rep_context_itf(
p_trl_global_variables_rec IN OUT NOCOPY zx_extract_pkg.trl_global_variables_rec_type,
p_rep_entity_id IN NUMBER)
IS
l_style HR_LOCATIONS_ALL.style%TYPE;
SELECT loc.country,
loc.town_or_city,
loc.region_1,
loc.region_2,
loc.region_3,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
loc.postal_code,
loc.telephone_number_1,
loc.style,
loc.location_id
FROM hr_locations loc,
hr_organization_units org
WHERE org.location_id = loc.location_id
AND org.organization_id = c_rep_entity_id;
SELECT
xle_firstpty.name ,
xle_firstpty.activity_code,
xle_firstpty.sub_activity_code,
xle_firstpty.registration_number,
-- xle_firstpty.effective_from
xle_firstpty.location_id,
xle_firstpty.address_line_1,
xle_firstpty.address_line_2,
xle_firstpty.address_line_3,
xle_firstpty.town_or_city,
xle_firstpty.region_1,
xle_firstpty.region_2,
xle_firstpty.region_3,
xle_firstpty.postal_code,
-- xle_firstpty.phone_number,
xle_firstpty.country,
xle_firstpty.address_style
-- xle_cont.contact_name,
-- xle_cont.contact_legal_id,
-- xle_cont.title,
-- xle_cont.job_title
-- xle_cont.role
FROM xle_firstparty_information_v xle_firstpty
-- xle_legal_contacts_v xle_cont
WHERE xle_firstpty.legal_entity_id = c_rep_entity_id;
SELECT xle_auth.address2,
xle_auth.address3,
xle_auth.city,
xle_auth.authority_name
FROM xle_legalauth_v xle_auth,
xle_registrations xle_reg
WHERE xle_reg.source_id = c_rep_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';
SELECT per.party_name, -- contact_name,
per.jgzz_fiscal_code, --contact_legal_id,
-- rol.lookup_code, --job Title
XLE_CONTACT_GRP.concat_contact_roles
(rel.subject_id,
rel.object_id),
hzpp.person_pre_name_adjunct -- title
FROM HZ_PARTIES per,
xle_entity_profiles xep,
HZ_RELATIONSHIPS rel,
hz_person_profiles hzpp,
HZ_ORG_CONTACTS con
--XLE_CONTACT_LEGAL_ROLES rol
WHERE rel.relationship_code = 'CONTACT_OF'
AND rel.object_id = xep.party_id
AND per.party_id = hzpp.party_id
AND rel.relationship_type = 'CONTACT'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.subject_type = 'PERSON'
AND rel.subject_id = per.party_id
-- AND rel.subject_id = rol.contact_party_id
-- AND rel.object_id = rol.le_etb_party_id
AND rel.object_table_name = 'HZ_PARTIES'
AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
AND rel.relationship_id = con.party_relationship_id
AND xep.legal_entity_id = c_rep_entity_id
UNION
SELECT per.party_name, --contact_name,
per.jgzz_fiscal_code, --contact_legal_id,
-- rol.lookup_code, --job Title
XLE_CONTACT_GRP.concat_contact_roles
(rel.subject_id,
rel.object_id),
hzpp.person_pre_name_adjunct --title,
FROM HZ_PARTIES per,
xle_etb_profiles etb,
HZ_RELATIONSHIPS rel,
hz_person_profiles hzpp,
HZ_ORG_CONTACTS con
-- XLE_CONTACT_LEGAL_ROLES rol
WHERE rel.relationship_code = 'CONTACT_OF'
AND rel.object_id = etb.party_id
AND per.party_id = hzpp.party_id
AND rel.relationship_type = 'CONTACT'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.subject_type = 'PERSON'
AND rel.subject_id = per.party_id
--AND rel.subject_id = rol.contact_party_id
--AND rel.object_id = rol.le_etb_party_id
AND rel.object_table_name = 'HZ_PARTIES'
AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
AND rel.relationship_id = con.party_relationship_id
AND etb.establishment_id = c_rep_entity_id ;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.BEGIN',
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(+)');
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_lvl_mng : '||l_rep_context_lvl_mng);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_name : '|| l_rep_context_name);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_activity_code : '|| l_activity_code);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_sub_activity_code : '|| l_sub_activity_code);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_taxpayer_id : '|| l_taxpayer_id);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_loc_id : '|| l_rep_context_loc_id);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'g_rep_context_address1 : '|| g_rep_context_address1);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'g_rep_context_city : '|| g_rep_context_city);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_style : '|| l_style);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_contact_party_name : '|| l_legal_contact_party_name);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_contact_title : '|| l_legal_contact_title);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_contact_party_num : '|| l_legal_contact_party_num);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_auth_address_line2 : '|| l_legal_auth_address_line2);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_auth_address_line3 : '|| l_legal_auth_address_line3);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_auth_city : '|| l_legal_auth_city);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_legal_auth_name : '|| l_legal_auth_name);
SELECT org_info.org_information2,
org_unit.organization_id,
org_unit.location_id,
org_unit.name
INTO l_rep_context_tax_reg_no,
l_rep_context_org_id,
l_rep_context_loc_id,
l_rep_context_name
FROM hr_organization_units org_unit,
hr_organization_information org_info
WHERE org_unit.organization_id = org_info.organization_id
AND org_info.org_information1 = 'OPERATING_UNIT'
AND org_unit.organization_id = p_rep_entity_id;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: After c_loc_rec cursor ');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'p_rep_entity_id :'||to_char(p_rep_entity_id));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'g_rep_context_city :'||g_rep_context_city);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'g_rep_context_address1 :'||g_rep_context_address1);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_name :'||l_rep_context_name);
SELECT ptp.party_id
INTO l_hq_party_id
FROM zx_party_tax_profile ptp,
xle_etb_profiles xlep
WHERE ptp.party_id = xlep.party_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND xlep.legal_entity_id = p_trl_global_variables_rec.legal_entity_id
AND xlep.main_establishment_flag = 'Y';
SELECT zx_rep_context_t_s.nextval
INTO l_rep_context_id FROM dual;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: Insert statement begins ');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'p_rep_entity_id :'||to_char(p_rep_entity_id));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'rep_context_id :'||to_char(l_rep_context_id));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_city :'||l_rep_context_city);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'l_rep_context_address1 :'||l_rep_context_address1);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'HQ Tax Registration Nbr : l_org_information2 :'||l_org_information2);
INSERT INTO zx_rep_context_t(
rep_context_id,
request_id,
rep_entity_id,
rep_context_entity_location_id,
rep_context_entity_name,
rep_context_entity_city,
rep_context_entity_county,
rep_context_entity_state,
rep_context_entity_province,
rep_context_entity_address1,
rep_context_entity_address2,
rep_context_entity_address3,
rep_context_entity_country,
rep_context_entity_postal_code,
rep_context_entity_tel_number,
rep_context_lvl_mng,
rep_context_lvl_code,
extract_summary_code,
matrix_report_flag,
legal_contact_pre_name_adjunct,
legal_contact_party_name,
taxpayer_id,
legal_contact_title,
activity_code,
sub_activity_code,
inception_date,
legal_contact_party_num,
legal_auth_address_line2,
legal_auth_address_line3,
legal_auth_city,
legal_authority_name,
org_information2,
program_application_id,
program_id,
program_login_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
l_rep_context_id,
p_trl_global_variables_rec.request_id,
l_rep_entity_id,
l_rep_context_loc_id,
l_rep_context_name,
l_rep_context_city,
l_rep_context_county,
l_rep_context_state,
l_rep_context_province,
l_rep_context_address1,
l_rep_context_address2,
l_rep_context_address3,
l_rep_context_country,
l_rep_context_postal_code,
l_rep_context_phone_number,
l_rep_context_lvl_mng,
--l_rep_context_lvl_code,
p_trl_global_variables_rec.reporting_level,
l_extract_summary_code,
l_matrix_report_flag,
l_legal_contact_job_title,
l_legal_contact_party_name,
l_taxpayer_id,
l_legal_contact_title,
l_activity_code,
l_sub_activity_code,
l_inception_date,
l_legal_contact_party_num,
l_legal_auth_address_line2,
l_legal_auth_address_line3,
l_legal_auth_city,
l_legal_auth_name,
l_org_information2,
l_program_application_id,
l_program_id,
l_program_login_id,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login);
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'Rows Inserted in Rep context table :'||to_char(l_rowcount));
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
'P_TRL_GLOBAL_VARIABLES_REC.RETCODE'||to_char(P_TRL_GLOBAL_VARIABLES_REC.RETCODE));
FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_rep_context_itf- '|| g_error_buffer);
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
g_error_buffer);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.END',
'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(-)');
END insert_rep_context_itf ;
p_rows_deleted out NOCOPY number) is
BEGIN
purge(p_request_id);
p_rows_deleted:= purge(p_request_id);
| This procedure deletes the records from AR_TAX_EXTRACT_DCL_ITF |
| and AR_TAX_EXTRACT_SUB_ITF for a given request_id |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 14-Dec-1999 Nilesh Patel Created |
| |
+===========================================================================*/
PROCEDURE PURGE(p_request_id in number) is
num_rows_deleted number := 0;
select count(*) into num_rows_deleted
from zx_rep_trx_detail_t
where request_id = p_request_id;
'Num of Rows in zx_rep_trx_detail_t :'||num_rows_deleted);
select count(*) into num_rows_deleted
from ZX_REP_ACTG_EXT_T
where request_id = p_request_id;
'Num of Rows in ZX_REP_ACTG_EXT_T :'||num_rows_deleted);
select count(*) into num_rows_deleted
from ZX_REP_TRX_JX_EXT_T
where request_id = p_request_id;
'Num of Rows in ZX_REP_TRX_JX_EXT_T :'||num_rows_deleted);
select count(*) into num_rows_deleted
from ZX_REP_CONTEXT_T
where request_id = p_request_id;
'Num of Rows in ZX_REP_CONTEXT_T :'||num_rows_deleted);
delete from ZX_REP_ACTG_EXT_T where request_id = p_request_id;
delete from ZX_REP_TRX_JX_EXT_T where request_id = p_request_id;
delete from ZX_REP_TRX_DETAIL_T where request_id = p_request_id;
delete from ZX_REP_CONTEXT_T where request_id = p_request_id;
'In Delete when PG_DEBUG = N ');
delete from ar_tax_extr_sub_com_ext com_ext where com_ext.extract_line_id
in (select sub_itf.extract_line_id
from ar_tax_Extract_sub_itf sub_itf
where request_id = p_request_id);
delete from ar_tax_extr_sub_ar_ext ar_ext where ar_ext.extract_line_id
in (select sub_itf.extract_line_id
from ar_tax_Extract_sub_itf sub_itf
where request_id = p_request_id);
delete from ar_tax_extr_sub_ap_ext ap_ext where ap_ext.extract_line_id
in (select sub_itf.extract_line_id
from ar_tax_Extract_sub_itf sub_itf
where request_id = p_request_id);
delete from ar_tax_extract_sub_itf where request_id = p_request_id;
delete from ar_tax_Extract_dcl_itf where request_id = p_request_id;
| This function deletes the records from AR_TAX_EXTRACT_DCL_ITF, |
| AR_TAX_EXTRACT_SUB_ITF for a given request_id |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 24-May-2000 Nilesh Patel Created |
| |
+===========================================================================*/
FUNCTION PURGE(p_request_id in number) return number is
num_rows_deleted number := 0;
select count(*) into num_rows_deleted
from zx_rep_trx_detail_t
where request_id = p_request_id;
return(num_rows_deleted);
matrix_tbl.delete;
matrix_tbl.delete;
SELECT rep_context.rep_context_id
FROM zx_rep_context_t rep_context
WHERE request_id = c_request_id;
select 'Y' from dual where exists
(select '1'
from FA_RX_REPORTS_V RV,
FA_RX_ATTRSETS ATT,
FA_RX_REP_COLUMNS COL
where RV.REPORT_ID = C_REPORT_ID
AND ATT.REPORT_ID = RV.REPORT_ID
AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
AND COL.DISPLAY_STATUS = 'YES'
AND COL.COLUMN_NAME IN (
'TAX1_ACCOUNTED_AMOUNT',
'TAX1_ACCOUNTED_CR',
'TAX1_ACCOUNTED_DR',
'TAX1_CODE',
'TAX1_CODE_DESCRIPTION',
'TAX1_CODE_NAME',
'TAX1_CODE_RATE',
'TAX1_CODE_REG_TYPE_CODE',
'TAX1_CODE_REG_TYPE_MEANING',
'TAX1_CODE_TAX_CLASS_CODE',
'TAX1_CODE_TAX_CLASS_MEANING',
'TAX1_CODE_TYPE_CODE',
'TAX1_CODE_TYPE_MEANING',
'TAX1_CODE_VAT_TRX_TYPE_CODE',
'TAX1_CODE_VAT_TRX_TYPE_DESC',
'TAX1_CODE_VAT_TRX_TYPE_MEANING',
'TAX1_ENTERED_AMOUNT',
'TAX1_ENTERED_CR',
'TAX1_ENTERED_DR',
'TAX1_LINE_EFFECTIVE_TAX_RATE',
'TAX1_LINE_NUMBER',
'TAX1_RECOVERABLE_FLAG',
'TAXABLE1_ACCOUNTED_AMOUNT',
'TAXABLE1_ACCOUNTED_CR',
'TAXABLE1_ACCOUNTED_DR',
'TAXABLE1_ENTERED_AMOUNT',
'TAXABLE1_ENTERED_CR',
'TAXABLE1_ENTERED_DR',
'TAX2_ACCOUNTED_AMOUNT',
'TAX2_ACCOUNTED_CR',
'TAX2_ACCOUNTED_DR',
'TAX2_CODE',
'TAX2_CODE_DESCRIPTION',
'TAX2_CODE_NAME',
'TAX2_CODE_RATE',
'TAX2_CODE_REG_TYPE_CODE',
'TAX2_CODE_REG_TYPE_MEANING',
'TAX2_CODE_TAX_CLASS_CODE',
'TAX2_CODE_TAX_CLASS_MEANING',
'TAX2_CODE_TYPE_CODE',
'TAX2_CODE_TYPE_MEANING',
'TAX2_CODE_VAT_TRX_TYPE_CODE',
'TAX2_CODE_VAT_TRX_TYPE_DESC',
'TAX2_CODE_VAT_TRX_TYPE_MEANING',
'TAX2_ENTERED_AMOUNT',
'TAX2_ENTERED_CR',
'TAX2_ENTERED_DR',
'TAX2_LINE_EFFECTIVE_TAX_RATE',
'TAX2_LINE_NUMBER',
'TAX2_RECOVERABLE_FLAG',
'TAXABLE2_ACCOUNTED_AMOUNT',
'TAXABLE2_ACCOUNTED_CR',
'TAXABLE2_ACCOUNTED_DR',
'TAXABLE2_ENTERED_AMOUNT',
'TAXABLE2_ENTERED_CR',
'TAXABLE2_ENTERED_DR',
'TAX3_ACCOUNTED_AMOUNT',
'TAX3_ACCOUNTED_CR',
'TAX3_ACCOUNTED_DR',
'TAX3_CODE',
'TAX3_CODE_DESCRIPTION',
'TAX3_CODE_NAME',
'TAX3_CODE_RATE',
'TAX3_CODE_REG_TYPE_CODE',
'TAX3_CODE_REG_TYPE_MEANING',
'TAX3_CODE_TAX_CLASS_CODE',
'TAX3_CODE_TAX_CLASS_MEANING',
'TAX3_CODE_TYPE_CODE',
'TAX3_CODE_TYPE_MEANING',
'TAX3_CODE_VAT_TRX_TYPE_CODE',
'TAX3_CODE_VAT_TRX_TYPE_DESC',
'TAX3_CODE_VAT_TRX_TYPE_MEANING',
'TAX3_ENTERED_AMOUNT',
'TAX3_ENTERED_CR',
'TAX3_ENTERED_DR',
'TAX3_LINE_EFFECTIVE_TAX_RATE',
'TAX3_LINE_NUMBER',
'TAX3_RECOVERABLE_FLAG',
'TAXABLE3_ACCOUNTED_AMOUNT',
'TAXABLE3_ACCOUNTED_CR',
'TAXABLE3_ACCOUNTED_DR',
'TAXABLE3_ENTERED_AMOUNT',
'TAXABLE3_ENTERED_CR',
'TAXABLE3_ENTERED_DR',
'TAX4_ACCOUNTED_AMOUNT',
'TAX4_ACCOUNTED_CR',
'TAX4_ACCOUNTED_DR',
'TAX4_CODE',
'TAX4_CODE_DESCRIPTION',
'TAX4_CODE_NAME',
'TAX4_CODE_RATE',
'TAX4_CODE_REG_TYPE_CODE',
'TAX4_CODE_REG_TYPE_MEANING',
'TAX4_CODE_TAX_CLASS_CODE',
'TAX4_CODE_TAX_CLASS_MEANING',
'TAX4_CODE_TYPE_CODE',
'TAX4_CODE_TYPE_MEANING',
'TAX4_CODE_VAT_TRX_TYPE_CODE',
'TAX4_CODE_VAT_TRX_TYPE_DESC',
'TAX4_CODE_VAT_TRX_TYPE_MEANING',
'TAX4_ENTERED_AMOUNT',
'TAX4_ENTERED_CR',
'TAX4_ENTERED_DR',
'TAX4_LINE_EFFECTIVE_TAX_RATE',
'TAX4_LINE_NUMBER',
'TAX4_RECOVERABLE_FLAG',
'TAXABLE4_ACCOUNTED_AMOUNT',
'TAXABLE4_ACCOUNTED_CR',
'TAXABLE4_ACCOUNTED_DR',
'TAXABLE4_ENTERED_AMOUNT',
'TAXABLE4_ENTERED_CR',
'TAXABLE4_ENTERED_DR') );
select 'Y' from dual where exists
(select '1'
from FA_RX_REPORTS_V RV,
FA_RX_ATTRSETS ATT,
FA_RX_REP_COLUMNS COL
where RV.REPORT_ID = C_REPORT_ID
AND ATT.REPORT_ID = RV.REPORT_ID
AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
AND COL.DISPLAY_STATUS = 'YES'
AND COL.COLUMN_NAME IN (
'TAX3_ACCOUNTED_AMOUNT',
'TAX3_ACCOUNTED_CR',
'TAX3_ACCOUNTED_DR',
'TAX3_CODE',
'TAX3_CODE_DESCRIPTION',
'TAX3_CODE_NAME',
'TAX3_CODE_RATE',
'TAX3_CODE_REG_TYPE_CODE',
'TAX3_CODE_REG_TYPE_MEANING',
'TAX3_CODE_TAX_CLASS_CODE',
'TAX3_CODE_TAX_CLASS_MEANING',
'TAX3_CODE_TYPE_CODE',
'TAX3_CODE_TYPE_MEANING',
'TAX3_CODE_VAT_TRX_TYPE_CODE',
'TAX3_CODE_VAT_TRX_TYPE_DESC',
'TAX3_CODE_VAT_TRX_TYPE_MEANING',
'TAX3_ENTERED_AMOUNT',
'TAX3_ENTERED_CR',
'TAX3_ENTERED_DR',
'TAX3_LINE_EFFECTIVE_TAX_RATE',
'TAX3_LINE_NUMBER',
'TAX3_RECOVERABLE_FLAG',
'TAXABLE3_ACCOUNTED_AMOUNT',
'TAXABLE3_ACCOUNTED_CR',
'TAXABLE3_ACCOUNTED_DR',
'TAXABLE3_ENTERED_AMOUNT',
'TAXABLE3_ENTERED_CR',
'TAXABLE3_ENTERED_DR',
'TAX4_ACCOUNTED_AMOUNT',
'TAX4_ACCOUNTED_CR',
'TAX4_ACCOUNTED_DR',
'TAX4_CODE',
'TAX4_CODE_DESCRIPTION',
'TAX4_CODE_NAME',
'TAX4_CODE_RATE',
'TAX4_CODE_REG_TYPE_CODE',
'TAX4_CODE_REG_TYPE_MEANING',
'TAX4_CODE_TAX_CLASS_CODE',
'TAX4_CODE_TAX_CLASS_MEANING',
'TAX4_CODE_TYPE_CODE',
'TAX4_CODE_TYPE_MEANING',
'TAX4_CODE_VAT_TRX_TYPE_CODE',
'TAX4_CODE_VAT_TRX_TYPE_DESC',
'TAX4_CODE_VAT_TRX_TYPE_MEANING',
'TAX4_ENTERED_AMOUNT',
'TAX4_ENTERED_CR',
'TAX4_ENTERED_DR',
'TAX4_LINE_EFFECTIVE_TAX_RATE',
'TAX4_LINE_NUMBER',
'TAX4_RECOVERABLE_FLAG',
'TAXABLE4_ACCOUNTED_AMOUNT',
'TAXABLE4_ACCOUNTED_CR',
'TAXABLE4_ACCOUNTED_DR',
'TAXABLE4_ENTERED_AMOUNT',
'TAXABLE4_ENTERED_CR',
'TAXABLE4_ENTERED_DR') );
| This procedure is used to update the legal_reporting_status value |
| on the zx_lines with the value passed as input to this procedure |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 24-Mar-2006 Ashwin Gurram Created |
| |
+===========================================================================*/
PROCEDURE ZX_UPD_LEGAL_REPORTING_STATUS(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN VARCHAR2,
p_application_id_tbl IN application_id_tbl,
p_entity_code_tbl IN entity_code_tbl,
p_event_class_code_tbl IN event_class_code_tbl,
p_trx_id_tbl IN trx_id_tbl,
p_trx_line_id_tbl IN trx_line_id_tbl,
p_INTERNAL_ORGANIZATION_ID_tbl IN INTERNAL_ORGANIZATION_ID_TBL,
p_TAX_LINE_ID_tbl IN TAX_LINE_ID_TBL,
p_legal_reporting_status_val IN zx_lines.LEGAL_REPORTING_STATUS%type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER := 0;
IF ( l_count > C_LINES_PER_INSERT ) THEN
l_counter_end := C_LINES_PER_INSERT;
UPDATE ZX_LINES
SET LEGAL_REPORTING_STATUS = p_legal_reporting_status_val,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id ,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE application_id = p_application_id_tbl(i)
AND entity_code = p_entity_code_tbl(i)
AND event_class_code = p_event_class_code_tbl(i)
AND trx_id = p_trx_id_tbl(i)
AND trx_line_id = p_trx_line_id_tbl(i)
AND INTERNAL_ORGANIZATION_ID = p_INTERNAL_ORGANIZATION_ID_tbl(i)
AND TAX_LINE_ID = p_TAX_LINE_ID_tbl(i) ;
IF ( l_counter_end + C_LINES_PER_INSERT < l_count ) THEN
l_counter_end := l_counter_end + C_LINES_PER_INSERT;
SELECT DISTINCT v1.reporting_code_name
FROM (
SELECT v.reporting_code_name,
ROW_NUMBER() OVER (PARTITION BY v.trx_line_id
ORDER BY v.tax_line_id, v.order_num) AS row_num
FROM (
SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id, zxl.tax_line_id,1 order_num
FROM zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND zxl.legal_message_rate = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
UNION
SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id,zxl.tax_line_id, 2 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND rep_assoc.entity_id = zxl.tax_rate_id
AND rep_assoc.entity_code = 'ZX_RATES'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_type_id = rep_types.reporting_type_id
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
UNION
SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id,zxl.tax_line_id, 3 order_num
FROM zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND zxl.legal_message_status = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
) v
) v1
WHERE v1.row_num = 1;
SELECT DISTINCT v1.reporting_code_name
FROM (
SELECT v.reporting_code_name,
v.order_num
FROM (
SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id,1 order_num
FROM zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND zxl.trx_line_id = p_trx_line_id
AND zxl.legal_message_rate = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
UNION
SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id, 2 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND zxl.trx_line_id = p_trx_line_id
AND rep_assoc.entity_id = zxl.tax_rate_id
AND rep_assoc.entity_code = 'ZX_RATES'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_type_id = rep_types.reporting_type_id
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
UNION
SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id, 3 order_num
FROM zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxl.trx_id = p_trx_id
AND zxl.trx_line_id = p_trx_line_id
AND zxl.legal_message_status = rep_codes.reporting_code_id
AND rep_codes.reporting_type_id = rep_types.reporting_type_id
AND rep_types.legal_message_flag = 'Y'
) v
ORDER BY v.tax_line_id,v.order_num,v.reporting_code_name) v1
WHERE rownum = 1;
SELECT v1.reporting_code_name, v1.reporting_code_char_value
FROM
(SELECT v.reporting_code_name, v.reporting_code_char_value
FROM (
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 1 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_id = p_esl_eu_trx_type
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = NVL(zxl.direct_rate_result_id, zxl.rate_result_id)
AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ((p_esl_eu_goods IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
(p_esl_eu_services IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_services) OR
(p_esl_eu_addl_code1 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
(p_esl_eu_addl_code2 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
UNION
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 2 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_id = p_esl_eu_trx_type
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = zxl.tax_rate_id
AND rep_assoc.entity_code = 'ZX_RATES'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ((p_esl_eu_goods IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
(p_esl_eu_services IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_services) OR
(p_esl_eu_addl_code1 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
(p_esl_eu_addl_code2 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
UNION
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 3 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_id = p_esl_eu_trx_type
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = zxl.status_result_id
AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ((p_esl_eu_goods IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
(p_esl_eu_services IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_services) OR
(p_esl_eu_addl_code1 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
(p_esl_eu_addl_code2 IS NOT NULL
AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
) v
WHERE v.reporting_code_name IS NOT NULL
ORDER BY v.order_num,v.reporting_code_name) v1
WHERE ROWNUM = 1;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_EXTRACT_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_AR_EXTRACT_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_AP_EXTRACT_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_AR_POPULATE_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_AP_POPULATE_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;
select substr(text,5,70) into l_version_info from user_source
where name = 'ZX_GL_EXTRACT_PKG'
and text like '%Header:%'
and type = 'PACKAGE BODY'
and line < 10;