[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 = p_chart_of_accounts_id
AND gl.ledger_id = p_ledger_id;
EXECUTE IMMEDIATE 'SELECT '||l_segment || ' FROM gl_code_combinations '
|| ' WHERE code_combination_id = '||p_ccid INTO bal_segment_value;
SELECT last_reported_period
FROM jg_zz_vat_rep_entities
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id;
SELECT JGTRD.trx_id
FROM jg_zz_vat_trx_details JGTRD,
jg_zz_vat_rep_status JGREPS,
zx_lines ZX
WHERE JGREPS.vat_reporting_entity_id = pn_vat_rep_entity_id
AND JGREPS.reporting_status_id = JGTRD.reporting_status_id
AND JGREPS.source = JGTRD.extract_source_ledger
AND ZX.trx_id = JGTRD.trx_id
AND JGTRD.created_by = 1
AND ZX.record_type_code = 'MIGRATED'
AND ZX.application_id = JGTRD.application_id
AND ZX.entity_code = JGTRD.entity_code
AND ZX.event_class_code = JGTRD.event_class_code
AND rownum=1;
l_update_query_ap VARCHAR2(5000);
l_update_query_ar VARCHAR2(5000);
SELECT LEGAL.driving_date_code
,ACCT.mapping_vat_rep_entity_id
,LEGAL.tax_calendar_name
,ACCT.ledger_id
,LEGAL.legal_entity_id
INTO l_driving_date_code
,l_mapping_rep_entity_id
,l_tax_calendar_name
,l_ledger_id
,l_legal_entity_id
FROM JG_ZZ_VAT_REP_ENTITIES LEGAL
,JG_ZZ_VAT_REP_ENTITIES ACCT
WHERE ACCT.VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id
AND LEGAL.VAT_REPORTING_ENTITY_ID = ACCT.mapping_vat_rep_entity_id;
SELECT xle.country
INTO l_country
FROM xle_firstparty_information_v xle
WHERE xle.legal_entity_id = l_legal_entity_id;
JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities( pv_entity_level_code => p_reporting_level
, pn_vat_reporting_entity_id => l_mapping_rep_entity_id
, pn_ledger_id => l_ledger_id
, pv_balancing_segment_value => p_bsv
, xn_vat_reporting_entity_id => l_bsv_vat_rep_entity_id
, xv_return_status => l_return_status
, xv_return_message => l_return_message
);
SELECT start_date ,
end_date
INTO l_start_date ,
l_end_date
FROM GL_PERIODS
WHERE period_set_name = l_tax_calendar_name
AND period_name = p_period;
l_update_query_ap :=
'UPDATE zx_lines zxl
SET zxl.legal_reporting_status =''000000000000000''
,LAST_UPDATED_BY = -5
,LAST_UPDATE_DATE = sysdate
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE zxl.legal_reporting_status = ''111111111111111''
AND zxl.record_type_code = ''MIGRATED''
AND zxl.ledger_id = $l_ledger_id$
AND zxl.legal_entity_id = $l_legal_entity_id$
AND zxl.trx_id NOT IN
(SELECT stg.trx_id
FROM jg_zz_vat_trx_upg_stg stg
WHERE stg.application_id = zxl.application_id
AND stg.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
AND stg.ENTITY_CODE = zxl.entity_code
)
AND zxl.trx_id NOT IN
(SELECT trxd.trx_id
FROM jg_zz_vat_rep_Status reps ,
jg_zz_vat_trx_details trxd
WHERE reps.reporting_status_id = trxd.reporting_status_id
AND reps.final_reporting_status_flag IS NOT NULL
AND reps.final_reporting_process_id IS NOT NULL
AND reps.final_reporting_process_date IS NOT NULL
)';
l_update_query_ar := l_update_query_ap;
l_update_query_ap := l_update_query_ap||
' AND zxl.application_id = 200
AND zxl.trx_date > $l_end_date$';
l_update_query_ar := l_update_query_ar||
' AND zxl.application_id = 222
AND zxl.trx_date > $l_end_date$';
l_update_query_ap := l_update_query_ap ||
' AND zxl.trx_id IN
(SELECT apd.invoice_id trx_id
FROM ap_invoice_distributions_all apd
,ap_invoices_all apinv
WHERE apinv.invoice_id = apd.invoice_id
AND apinv.invoice_date > $l_end_date$
AND apinv.set_of_books_id = $l_ledger_id$
AND apinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
)';
l_update_query_ar := l_update_query_ar ||
' AND zxl.trx_id IN
( SELECT rtd.customer_trx_id
FROM ra_cust_trx_line_gl_dist_all rtd ,
ra_customer_trx_all rinv
WHERE rinv.customer_trx_id = rtd.customer_trx_id
AND rinv.trx_date > $l_end_date$
AND rinv.set_of_books_id = $l_ledger_id$
AND rinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
)';
l_update_query_ap := l_update_query_ap||
' AND zxl.application_id = 200
AND zxl.trx_id IN
(SELECT invoice_id
FROM ap_invoices_all
WHERE gl_date > $l_end_date$
AND set_of_books_id = $l_ledger_id$
AND legal_entity_id = $l_legal_entity_id$
)';
l_update_query_ar := l_update_query_ar||
' AND zxl.application_id = 222
AND zxl.trx_id IN
(SELECT customer_trx_id
FROM ra_cust_trx_line_gl_dist_all
WHERE gl_date > $l_end_date$
AND set_of_books_id = $l_ledger_id$
) ';
l_update_query_ap := l_update_query_ap||
' AND zxl.trx_id IN
(SELECT apd.invoice_id trx_id
FROM ap_invoice_distributions_all apd ,
ap_invoices_all apinv
WHERE apinv.invoice_id = apd.invoice_id
AND apinv.gl_date > $l_end_date$
AND apinv.set_of_books_id = $l_ledger_id$
AND apinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
) ';
l_update_query_ar := l_update_query_ar||
' AND zxl.trx_id IN
(SELECT rtd.customer_trx_id
FROM ra_cust_trx_line_gl_dist_all rtd ,
ra_customer_trx_all rinv
WHERE rinv.customer_trx_id = rtd.customer_trx_id
AND rtd.gl_date > $l_end_date$
AND rinv.set_of_books_id = $l_ledger_id$
AND rinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
)';
l_update_query_ap := l_update_query_ap ||
' AND zxl.application_id = 200
AND zxl.trx_id IN
(SELECT zxd.trx_id
FROM zx_lines_det_factors zxd
WHERE zxd.tax_invoice_date > $l_end_date$
AND zxd.application_id = zxl.application_id
AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
AND zxd.ENTITY_CODE = zxl.entity_code
AND zxd.legal_entity_id = $l_legal_entity_id$
AND zxd.ledger_id = $l_ledger_id$
)';
l_update_query_ar := l_update_query_ar ||
' AND zxl.application_id = 222
AND zxl.trx_id IN
(SELECT zxd.trx_id
FROM zx_lines_det_factors zxd
WHERE zxd.tax_invoice_date > $l_end_date$
AND zxd.application_id = zxl.application_id
AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
AND zxd.ENTITY_CODE = zxl.entity_code
AND zxd.legal_entity_id = $l_legal_entity_id$
AND zxd.ledger_id = $l_ledger_id$
)';
l_update_query_ap := l_update_query_ap ||
' AND zxl.trx_id IN
(SELECT apd.invoice_id trx_id
FROM ap_invoice_distributions_all apd ,
ap_invoices_all apinv
WHERE apinv.invoice_id = apd.invoice_id
AND apinv.set_of_books_id = $l_ledger_id$
AND apinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
)';
l_update_query_ar := l_update_query_ar ||
' AND zxl.trx_id IN
( SELECT rtd.customer_trx_id
FROM ra_cust_trx_line_gl_dist_all rtd ,
ra_customer_trx_all rinv
WHERE rinv.customer_trx_id = rtd.customer_trx_id
AND rinv.set_of_books_id = $l_ledger_id$
AND rinv.legal_entity_id = $l_legal_entity_id$
AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID,$p_chart_of_account_id$,$l_ledger_id$) = $p_bsv$
)';
l_update_query_ap := REPLACE( l_update_query_ap,'$l_ledger_id$',l_ledger_id);
l_update_query_ap := REPLACE( l_update_query_ap,'$l_end_date$',''''||l_end_date||'''');
l_update_query_ap := REPLACE( l_update_query_ap,'$l_legal_entity_id$',l_legal_entity_id);
l_update_query_ap := REPLACE( l_update_query_ap,'$p_chart_of_account_id$',p_chart_of_account_id);
l_update_query_ap := REPLACE( l_update_query_ap,'$p_bsv$',p_bsv);
l_update_query_ar := REPLACE( l_update_query_ar,'$l_ledger_id$',l_ledger_id);
l_update_query_ar := REPLACE( l_update_query_ar,'$l_end_date$',''''||l_end_date||'''');
l_update_query_ar := REPLACE( l_update_query_ar,'$l_legal_entity_id$',l_legal_entity_id);
l_update_query_ar := REPLACE( l_update_query_ar,'$p_chart_of_account_id$',p_chart_of_account_id);
l_update_query_ar := REPLACE( l_update_query_ar,'$p_bsv$',p_bsv);
fnd_file.put_line(fnd_file.log,'l_update_query_ap :='||l_update_query_ap);
fnd_file.put_line(fnd_file.log,'l_update_query_ar :='||l_update_query_ar);
EXECUTE IMMEDIATE l_update_query_ap;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'AP Rows updated: '||To_char(SQL%RowCount));
EXECUTE IMMEDIATE l_update_query_ar;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'AR Rows updated: '||To_char(SQL%RowCount));
UPDATE JG_ZZ_VAT_REP_ENTITIES
SET LAST_REPORTED_PERIOD = p_period
WHERE VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id;
UPDATE JG_ZZ_VAT_REP_ENTITIES
SET LAST_REPORTED_PERIOD = p_period
WHERE VAT_REPORTING_ENTITY_ID = l_bsv_vat_rep_entity_id;