[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from jg_zz_vat_box_allocs
where vat_transaction_id in
( select vat_transaction_id
from jg_zz_vat_trx_details a
where a.reporting_status_id = pn_reporting_status_id);
delete from jg_zz_vat_box_errors
where vat_transaction_id in
( select vat_transaction_id
from jg_zz_vat_trx_details a
where a.reporting_status_id = pn_reporting_status_id);
PROCEDURE insert_allocation_error (
pn_vat_transaction_id number,
pv_allocation_error_code varchar2,
pv_period_type varchar2,
pn_created_by number,
pn_last_updated_by number,
pn_last_update_login number,
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
) IS
BEGIN
if gv_debug_flag then
fnd_file.put_line(fnd_file.log, 'insert_allocation_error - start');
||', pn_last_updated_by:'||pn_last_updated_by
||', pn_last_update_login:'||pn_last_update_login);
INSERT INTO jg_zz_vat_box_errors(
vat_transaction_id ,
allocation_error_code ,
period_type ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
) VALUES (
pn_vat_transaction_id ,
pv_allocation_error_code ,
pv_period_type ,
sysdate,
pn_created_by ,
sysdate,
pn_last_updated_by ,
pn_last_update_login
);
xv_return_message := 'jg_zz_vat_alloc_prc_pkg.insert_allocation_error ~ Unexpected Error -' || sqlerrm;
END insert_allocation_error;
PROCEDURE update_allocation_error (
pn_vat_transaction_id number,
pv_allocation_error_code varchar2,
pv_period_type varchar2,
pn_last_updated_by number,
pn_last_update_login number,
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
) IS
BEGIN
UPDATE jg_zz_vat_box_errors
SET allocation_error_code = pv_allocation_error_code,
last_updated_by = pn_last_updated_by,
last_update_date = sysdate,
last_update_login = pn_last_update_login
WHERE Vat_transaction_id = pn_vat_transaction_id
AND period_type = pv_period_type;
xv_return_message := 'jg_zz_vat_alloc_prc_pkg.update_allocation_error ~ Unexpected Error -' || sqlerrm;
end update_allocation_error;
PROCEDURE delete_allocation_error (
pn_vat_transaction_id number,
pv_allocation_error_code varchar2,
pv_period_type varchar2,
xv_return_status out nocopy varchar2,
xv_return_message out nocopy varchar2
) IS
BEGIN
DELETE FROM jg_zz_vat_box_errors
WHERE Vat_transaction_id = pn_vat_transaction_id
AND allocation_error_code = pv_allocation_error_code
AND period_type = pv_period_type;
xv_return_message := 'jg_zz_vat_alloc_prc_pkg.delete_allocation_error ~ Unexpected Error -' || sqlerrm;
end delete_allocation_error;
ld_last_update_date DATE;
ln_last_updated_by NUMBER(15);
ln_last_update_login NUMBER(15);
SELECT nvl(map_jzvre.enable_allocations_flag, g_no) enable_allocations,
nvl(map_jzvre.enable_annual_allocation_flag, g_no) enable_annual_allocations
FROM jg_zz_vat_rep_entities jzvre
, jg_zz_vat_rep_entities map_jzvre
WHERE
(jzvre.vat_reporting_entity_id = pn_vat_reporting_entity_id
and
jzvre.entity_type_code = 'ACCOUNTING'
and
map_jzvre.vat_reporting_entity_id = jzvre.mapping_vat_rep_entity_id)
OR
(jzvre.vat_reporting_entity_id = pn_vat_reporting_entity_id
and
jzvre.entity_type_code = 'LEGAL'
and
map_jzvre.vat_reporting_entity_id = jzvre.vat_reporting_entity_id);
SELECT invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = cpn_trx_id;
SELECT cr.reversal_category cr_rev_category
FROM ar_cash_receipts_all cr
WHERE cr.cash_receipt_id = cpn_trx_id
AND cr.type = 'MISC'; /* got from R11i belgium solution */
SELECT sl.trx_type sl_trx_type
FROM ar_cash_receipt_history_all crh,
ce_statement_reconcils_all sr,
ce_statement_lines sl
WHERE crh.cash_receipt_id = cpn_trx_id
AND crh.cash_receipt_history_id = sr.reference_id
AND sr.statement_line_id = sl.statement_line_id;
SELECT type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = cp_trx_type_id;
SELECT
jg_zz_vat_alloc_prc_pkg.g_fresh_allocation allocation_type,
/* 'FRESH ALLOCATION' allocation_type, */
dtl.extract_source_ledger,
dtl.tax,
dtl.tax_status_code,
dtl.tax_jurisdiction_code,
dtl.tax_rate_code,
dtl.tax_rate_id,
dtl.reporting_status_id,
dtl.event_class_code,
dtl.entity_code,
dtl.trx_id,
dtl.trx_type_id,
dtl.trx_type_mng,
dtl.tax_recoverable_flag,
dtl.vat_transaction_id,
dtl.tax_rate_vat_trx_type_code vat_trans_type,
nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
NULL allocation_error_code,
NULL period_type
FROM jg_zz_vat_trx_details dtl, jg_zz_vat_rep_status status
WHERE status.reporting_status_id = dtl.reporting_status_id
AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
AND status.tax_calendar_period = cp_period_name
AND dtl.extract_source_ledger = cp_source;
NOTE: if any of the below cursor is changed to add/remove select columns,
then the same change also should be done for the other select statement
+ the cursor c_trxs_for_allocation which is defined above
*/
OPEN l_trxs_csr FOR
SELECT
jg_zz_vat_alloc_prc_pkg.g_fresh_allocation allocation_type,
/* 'FRESH ALLOCATION' allocation_type, */
dtl.extract_source_ledger,
dtl.tax,
dtl.tax_status_code,
dtl.tax_jurisdiction_code,
dtl.tax_rate_code,
dtl.tax_rate_id,
dtl.reporting_status_id,
dtl.event_class_code,
dtl.entity_code,
dtl.trx_id,
dtl.trx_type_id,
dtl.trx_type_mng,
dtl.tax_recoverable_flag,
dtl.vat_transaction_id,
dtl.tax_rate_vat_trx_type_code vat_trans_type,
nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
NULL allocation_error_code,
NULL period_type
FROM jg_zz_vat_trx_details dtl, jg_zz_vat_rep_status status
WHERE status.reporting_status_id = dtl.reporting_status_id
AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
AND status.tax_calendar_period = pv_tax_period_name
AND dtl.extract_source_ledger = pv_extract_source_ledger;
SELECT
jg_zz_vat_alloc_prc_pkg.g_error_allocation allocation_type,
/* 'ERROR ALLOCATION' allocation_type, */
dtl.extract_source_ledger,
dtl.tax,
dtl.tax_status_code,
dtl.tax_jurisdiction_code,
dtl.tax_rate_code,
dtl.tax_rate_id,
dtl.reporting_status_id,
dtl.event_class_code,
dtl.entity_code,
dtl.trx_id,
dtl.trx_type_id,
dtl.trx_type_mng,
dtl.tax_recoverable_flag,
dtl.vat_transaction_id,
dtl.tax_rate_vat_trx_type_code vat_trans_type,
nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
err.allocation_error_code allocation_error_code,
err.period_type period_type
FROM jg_zz_vat_trx_details dtl,
jg_zz_vat_box_errors err,
jg_zz_vat_rep_status status
WHERE status.reporting_status_id = dtl.reporting_status_id
AND dtl.vat_transaction_id = err.vat_transaction_id
AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
AND status.tax_calendar_period = pv_tax_period_name
AND dtl.extract_source_ledger = pv_extract_source_ledger;
ln_last_updated_by := FND_GLOBAL.user_id;
ln_last_update_login := FND_GLOBAL.login_id;
it thinks that the no applicable rule is found. It tries to report this as an error by inserting a record
in errors table. However this time the rule is matching, hence error_code is null and due to this
procedure to insert error record fails with exception "Cannot insert null"
*/
if lv_fresh_allocation_flag = g_no then
lv_period_type := l_trx_rec.period_type; /* this is the period type of the error allocation */
select tax_rate_code, vat_transaction_type_code
into lv_tax_rate_code, lv_vat_trans_type
from zx_rates_b
where tax_rate_id = l_trx_rec.tax_rate_id;
delete_allocation_error(
pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
pv_allocation_error_code => l_trx_rec.allocation_error_code,
pv_period_type => lv_period_type,
xv_return_status => lv_return_flag,
xv_return_message => lv_return_message
);
insert_allocation_error(
pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
pv_allocation_error_code => lv_allocation_error_code,
pv_period_type => lv_period_type,
pn_created_by => ln_created_by,
pn_last_updated_by => ln_last_updated_by,
pn_last_update_login => ln_last_update_login,
xv_return_status => lv_return_flag,
xv_return_message => lv_return_message
);
update_allocation_error(
pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
pv_allocation_error_code => lv_allocation_error_code,
pv_period_type => lv_period_type,
pn_last_updated_by => ln_last_updated_by,
pn_last_update_login => ln_last_update_login,
xv_return_status => lv_return_flag,
xv_return_message => lv_return_message
);
jg_zz_vat_box_allocs_pkg.insert_row(
xn_vat_box_allocation_id => ln_vat_box_allocation_id,
pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
pv_period_type => lv_period_type,
pn_allocation_rule_id => ln_allocation_rule_id,
pv_tax_box => lv_tax_box,
pv_taxable_box => lv_taxable_box,
pv_tax_recoverable_flag => l_trx_rec.tax_recoverable_flag,
pn_request_id => ln_request_id,
pn_program_application_id => ln_program_application_id,
pn_program_id => ln_program_id,
pn_program_login_id => ln_program_login_id,
pn_created_by => ln_created_by,
pn_last_updated_by => ln_last_updated_by,
pn_last_update_login => ln_last_update_login,
xv_return_status => lv_return_flag,
xv_return_message => lv_return_message
);
Preparation for call to post process update */
if lv_allocation_status_flag is null then
lv_allocation_status_flag := fnd_api.g_ret_sts_success;
select jg_zz_vat_rep_status_s2.nextval into ln_allocation_process_id from dual;
fnd_file.put_line(fnd_file.log, 'run allocation - before jg_zz_vat_rep_utility.post_process_update.'
);
/* Call the utility API to update allocation_process columns of jg_zz_vat_rep_status table by passing proper values.*/
jg_zz_vat_rep_utility.post_process_update(
pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
pv_tax_calendar_period => pv_tax_calendar_period,
pv_source => lv_extract_source_ledger,
pv_process_name => 'ALLOCATION',
pn_process_id => ln_allocation_process_id,
pv_process_flag => lv_allocation_status_flag ,
pv_enable_allocations_flag => lv_enable_alloc_flag,
xv_return_status => lv_return_flag,
xv_return_message => lv_return_message
);
fnd_file.put_line(fnd_file.log, 'run allocation - after jg_zz_vat_rep_utility.post_process_update.'
||' lv_return_flag:'||lv_return_flag
);
SELECT
a.allocation_rule_id,
a.source,
a.financial_document_type,
a.vat_transaction_type,
a.tax_code tax,
a.tax_status tax_status_code,
a.tax_rate_code,
a.tax_jurisdiction_code,
a.tax_box_recoverable,
a.tax_box_non_recoverable,
a.taxable_box_recoverable,
a.taxable_box_non_recoverable
FROM jg_zz_vat_alloc_rules a
,jg_zz_vat_rep_entities b
WHERE b.vat_reporting_entity_id = pn_vat_reporting_entity_id
and ((b.entity_type_code = 'ACCOUNTING'
and
b.mapping_vat_rep_entity_id = a.vat_reporting_entity_id)
OR
(b.entity_type_code = 'LEGAL'
and
b.vat_reporting_entity_id = a.vat_reporting_entity_id))
AND a.period_type = cp_period_type
AND a.source = cp_source
AND a.financial_document_type = cp_financial_document_type
AND a.tax_code = cp_tax
AND ld_today BETWEEN a.effective_from_date AND nvl(a.effective_to_date, ld_today)
ORDER BY
a.source,
a.financial_document_type,
a.tax_code NULLS LAST,
a.tax_status NULLS LAST,
a.tax_rate_code NULLS LAST,
a.tax_jurisdiction_code NULLS LAST,
decode(cp_source, 'AP', decode(pv_tax_recoverable_flag, g_yes,
decode(a.taxable_box_non_recoverable, NULL, NULL,
decode(a.tax_box_recoverable, NULL, NULL,
a.tax_box_recoverable)),
decode(a.taxable_box_non_recoverable, NULL, NULL,
decode(a.tax_box_non_recoverable, NULL, NULL,
a.tax_box_non_recoverable))),
decode(a.taxable_box_non_recoverable, NULL, NULL,
decode(a.tax_box_recoverable, NULL, NULL,
a.tax_box_recoverable))) NULLS LAST;
SELECT
allocation_rule_id,
tax_box_recoverable,
tax_box_non_recoverable,
taxable_box_non_recoverable
FROM jg_zz_vat_alloc_rules
WHERE vat_reporting_entity_id = cp_vat_reporting_entity_id
AND period_type = cp_period_type
AND source = cp_source
AND financial_document_type = cp_financial_document_type
AND tax_code = cp_tax
AND nvl(tax_status, '1') = nvl(gv_tax_status, '1')
AND nvl(tax_rate_code, '1') = nvl(gv_tax_rate_code,'1')
AND nvl(tax_jurisdiction_code,'1') = nvl(gv_tax_jurisdiction_code, '1')
AND ld_today BETWEEN effective_from_date AND nvl(effective_to_date, ld_today)
AND allocation_rule_id <> gv_appl_alloc_rule_id
AND allocation_rule_id > gv_allocation_rule_id
ORDER BY
allocation_rule_id;
select min(allocation_rule_id) - 1
into gv_allocation_rule_id
from jg_zz_vat_alloc_rules
where allocation_rule_id <> lv_allocation_rule_id;
select min(allocation_rule_id) - 1
into gv_allocation_rule_id
from jg_zz_vat_alloc_rules
where allocation_rule_id <> lv_allocation_rule_id;
select nvl(allocation_status_flag, g_no) allocation_status
from jg_zz_vat_rep_status a
where a.reporting_status_id = pn_reporting_status_id;