[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE Manage_TaxLines: It will Create, update, delete and cancel tax |
| lines and summary tax lines in the tax |
| repository |
* ===========================================================================*/
PROCEDURE Manage_TaxLines
(x_return_status OUT NOCOPY VARCHAR2,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
l_error_buffer VARCHAR2(100);
IF ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag = 'Y' THEN
/*
* no longer needed for UPDATE case
*
* ZX_TRL_MANAGE_TAX_PKG.Update_Synchronize_Taxlines
* (x_return_status => x_return_status);
ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines
(x_return_status => x_return_status ,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines()');
ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id (
p_event_class_rec => p_event_class_rec,
x_return_status => x_return_status);
'ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id()');
ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines()');
ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions()');
* no longer needed for UPDATE case
*
* ZX_TRL_MANAGE_TAX_PKG.Update_Synchronize_Taxlines
* (x_return_status => x_return_status);
ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines
(x_return_status => x_return_status ,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines()');
ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id (
p_event_class_rec => p_event_class_rec,
x_return_status => x_return_status);
'ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id()');
ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines()');
IF p_event_class_rec.tax_event_type_code = 'UPDATE'
AND p_event_class_rec.retain_summ_tax_line_id_flag = 'N'
THEN
-- for update tax event, AP will pass in all the trx lines,
-- that is, all the tax lines will be in the zx_detail_tax_lines_gt.
-- so if not retain summary_tax_line_id, it is treated same as
-- created case.
ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt (
p_event_class_rec => p_event_class_rec,
x_return_status => x_return_status );
ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
'ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions()');
| PROCEDURE Document_Level_Changes: It will Delete / Cancel / Purge tax lines |
| from the tax repository |
*============================================================================*/
PROCEDURE Document_Level_Changes
(x_return_status OUT NOCOPY VARCHAR2,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
p_tax_hold_released_code IN ZX_API_PUB.VALIDATION_STATUS_TBL_TYPE) IS
l_return_status VARCHAR2(1);
IF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'DELETE') THEN
ZX_TRL_MANAGE_TAX_PKG.DELETE_TRANSACTION
(x_return_status => l_return_status ,
p_event_class_rec => p_event_class_rec);
| PROCEDURE Synchronize_TaxLines : updates transaction information in the tax|
| repository |
* ===========================================================================*/
PROCEDURE Synchronize_TaxLines
(x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);
*ZX_TRL_MANAGE_TAX_PKG.Update_Transaction_Info
* (x_return_status => l_return_status);
| PROCEDURE Mark_Tax_Lines_Delete : Marks the tax lines as delete. |
* ===========================================================================*/
PROCEDURE Mark_Tax_Lines_Delete
(x_return_status OUT NOCOPY VARCHAR2,
p_transaction_line_rec IN ZX_API_PUB.TRANSACTION_LINE_REC_TYPE) IS
l_return_status VARCHAR2(1);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete.BEGIN',
'ZX_TRL_PUB_PKG: Mark_Tax_Lines_Delete (+)');
ZX_TRL_MANAGE_TAX_PKG.Mark_Detail_Tax_Lines_Delete
(x_return_status => l_return_status ,
p_transaction_line_rec => p_transaction_line_rec);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete.END',
'ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete',
l_error_buffer);
END Mark_Tax_Lines_Delete;
| PROCEDURE Manage_TaxDistributions: It will create, update, delete tax |
| distributions lines and update tax lines|
| and summary tax lines in the |
| tax repository. |
|============================================================================*/
PROCEDURE Manage_TaxDistributions
(x_return_status OUT NOCOPY VARCHAR2,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
l_return_status VARCHAR2(1);
ZX_TRL_MANAGE_TAX_PKG.Delete_dist_Marked_For_Delete
(x_return_status => l_return_status ,
p_event_class_rec => p_event_class_rec);
ZX_TRL_MANAGE_TAX_PKG.Update_Taxline_Rec_Nrec_amt
(x_return_status => l_return_status ,
p_event_class_rec => p_event_class_rec);
ZX_TRL_MANAGE_TAX_PKG.delete_tax_distributions(
x_return_status => l_return_status,
p_event_class_rec => p_event_class_rec);
ZX_TRL_MANAGE_TAX_PKG.update_taxline_rec_nrec_amt(
x_return_status => l_return_status,
p_event_class_rec => p_event_class_rec);
ZX_TRL_MANAGE_TAX_PKG.Update_Freeze_Flag
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
| PROCEDURE Update_Taxlines: This recording service is used to update tax |
| lines (ZX_LINES) with changed status for given |
| transaction line distributions. |
|============================================================================*/
PROCEDURE Update_Taxlines
(x_return_status OUT NOCOPY VARCHAR2,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
l_error_buffer VARCHAR2(100);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines.BEGIN',
'ZX_TRL_PUB_PKG: Update_Taxlines (+)');
ZX_TRL_MANAGE_TAX_PKG.Update_Item_Dist_Changed_Flag
(x_return_status => x_return_status,
p_event_class_rec => p_event_class_rec);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines.END',
'ZX_TRL_PUB_PKG: Update_Taxlines (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines',
l_error_buffer);
END Update_Taxlines;
PROCEDURE Update_GL_Date
(p_gl_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_error_buffer VARCHAR2(100);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date.BEGIN',
'ZX_TRL_PUB_PKG: Update_GL_Date (+)');
ZX_TRL_MANAGE_TAX_PKG.Update_GL_Date (p_gl_date, x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date.END',
'ZX_TRL_PUB_PKG: Update_GL_Date (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date',
l_error_buffer);
END Update_GL_Date;
PROCEDURE Update_Exchange_Rate
(p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_error_buffer VARCHAR2(100);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate.BEGIN',
'ZX_TRL_PUB_PKG: Update_Exchange_Rate (+)');
ZX_TRL_MANAGE_TAX_PKG.Update_Exchange_Rate (p_event_class_rec,
x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate.END',
'ZX_TRL_PUB_PKG: Update_Exchange_Rate (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate',
l_error_buffer);
END Update_Exchange_Rate;
PROCEDURE delete_tax_lines_and_dists
(
p_application_id IN NUMBER,
p_entity_code IN VARCHAR2,
p_event_class_code IN VARCHAR2,
p_trx_id IN NUMBER,
p_trx_line_id IN NUMBER,
p_trx_level_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR c_get_summary_flags IS
SELECT summarization_flag,
retain_summ_tax_line_id_flag
FROM zx_evnt_cls_mappings
WHERE event_class_code = p_event_class_code
AND application_id = p_application_id
AND entity_code = p_entity_code;
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.BEGIN',
'ZX_TRL_PUB_PKG: delete_tax_lines_and_dists (+)');
DELETE FROM zx_rec_nrec_dist
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type;
DELETE FROM zx_lines
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type;
DELETE FROM zx_lines_summary
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id;
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
'MRC Lines: Incorrect return_status after calling ' ||
'ZX_TRL_MANAGE_TAX_PKG.create_summary_from_zx_liness()');
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.END',
'ZX_TRL_PUB_PKG.delete_tax_lines_and_dists(-)');
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.END',
'ZX_TRL_PUB_PKG: delete_tax_lines_and_dists (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
l_error_buffer);
END delete_tax_lines_and_dists;
PROCEDURE delete_tax_dists
(
p_application_id IN NUMBER,
p_entity_code IN VARCHAR2,
p_event_class_code IN VARCHAR2,
p_trx_id IN NUMBER,
p_trx_line_id IN NUMBER,
p_trx_level_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_error_buffer VARCHAR2(100);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists.BEGIN',
'ZX_TRL_PUB_PKG: delete_tax_dists (+)');
DELETE FROM zx_rec_nrec_dist
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type;
UPDATE zx_lines
SET process_for_recovery_flag = 'Y', -- DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N')
rec_tax_amt = NULL,
rec_tax_amt_tax_curr = NULL,
rec_tax_amt_funcl_curr = NULL,
nrec_tax_amt = NULL,
nrec_tax_amt_tax_curr = NULL,
nrec_tax_amt_funcl_curr = NULL
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type;
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists.END',
'ZX_TRL_PUB_PKG: delete_tax_dists (-)'||x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists',
'Return Status = ' || x_return_status);
'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists',
l_error_buffer);
END delete_tax_dists;
CURSOR get_deleted_frzn_dist IS
SELECT /*+ leading(headers_gt) use_nl(AID) */
DISTINCT
aid.invoice_id trx_id,
aid.detail_tax_dist_id tax_dist_id,
aid.summary_tax_line_id summary_tax_line_id
FROM ap_invoice_distributions_all aid,
zx_trx_headers_gt headers_gt
WHERE headers_gt.event_class_code = p_event_class_rec.event_class_code
AND headers_gt.entity_code = p_event_class_rec.entity_code
AND headers_gt.application_id = p_event_class_rec.application_id
AND aid.invoice_id = headers_gt.trx_id
AND aid.line_type_lookup_code IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV')
AND (aid.accounting_event_id IS NOT NULL
OR NVL(aid.match_status_flag,'N') IN ('A','T')
OR NVL(aid.posted_flag,'N') = 'Y'
OR NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X'))
AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions'
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
AND NVL(zd.self_assessed_flag, 'N') = 'N');
CURSOR get_deleted_frzn_sa_dist IS
SELECT /*+ leading(headers_gt) use_nl(ASAD) */
DISTINCT
asad.invoice_id trx_id,
asad.detail_tax_dist_id tax_dist_id,
asad.summary_tax_line_id summary_tax_line_id
FROM ap_self_assessed_tax_dist_all asad,
zx_trx_headers_gt headers_gt
WHERE headers_gt.event_class_code = p_event_class_rec.event_class_code
AND headers_gt.entity_code = p_event_class_rec.entity_code
AND headers_gt.application_id = p_event_class_rec.application_id
AND asad.invoice_id = headers_gt.trx_id
AND asad.line_type_lookup_code IN ('NONREC_TAX','REC_TAX')
AND (asad.accounting_event_id IS NOT NULL
OR NVL(asad.match_status_flag,'N') IN ('A','T')
OR NVL(asad.posted_flag,'N') = 'Y'
OR NVL(asad.encumbered_flag, 'N') IN ('Y','D','W','X'))
AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions'
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = asad.detail_tax_dist_id
AND NVL(zd.self_assessed_flag, 'N') = 'Y');
'Start : Frozen Tax Distributions deleted Validation : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'));
l_trx_id_tbl.DELETE;
l_tax_dist_id_tbl.DELETE;
l_summary_tax_line_id_tbl.DELETE;
OPEN get_deleted_frzn_dist;
FETCH get_deleted_frzn_dist BULK COLLECT
INTO l_trx_id_tbl,
l_tax_dist_id_tbl,
l_summary_tax_line_id_tbl;
CLOSE get_deleted_frzn_dist;
l_error_buffer := 'System is trying to delete frozen tax distributions. '||
'Unable to complete Tax Processing.';
'Following frozen tax distributions deleted from ZX_REC_NREC_DIST:');
l_trx_id_tbl.DELETE;
l_tax_dist_id_tbl.DELETE;
l_summary_tax_line_id_tbl.DELETE;
OPEN get_deleted_frzn_sa_dist;
FETCH get_deleted_frzn_sa_dist BULK COLLECT
INTO l_trx_id_tbl,
l_tax_dist_id_tbl,
l_summary_tax_line_id_tbl;
CLOSE get_deleted_frzn_sa_dist;
l_error_buffer := 'System is trying to delete self-assessed frozen tax distributions. '||
'Unable to complete Tax Processing.';
'Following self-assessed frozen tax distributions deleted from ZX_REC_NREC_DIST:');
'End : Frozen Tax Distributions deleted Validation : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'));