[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ ORDERED
INDEX(headergt ZX_TRX_HEADERS_GT_U1)
INDEX(sumgt ZX_IMPORT_TAX_LINES_GT_U1)
INDEX(allocgt ZX_TRX_TAX_LINK_GT_U1)
INDEX(linegt ZX_TRANSACTION_LINES_GT_U1 )*/
sumgt.summary_tax_line_number,
sumgt.internal_organization_id,
sumgt.tax_regime_code,
sumgt.tax,
sumgt.tax_status_code,
sumgt.tax_rate_code,
sumgt.tax_rate,
sumgt.tax_amt summary_tax_amt,
sumgt.tax_jurisdiction_code,
sumgt.tax_amt_included_flag,
sumgt.tax_exception_id,
sumgt.tax_exemption_id,
sumgt.exempt_reason_code,
sumgt.exempt_certificate_number,
allocgt.trx_line_id,
allocgt.line_amt,
headergt.trx_date,
headergt.minimum_accountable_unit,
headergt.precision,
linegt.trx_level_type,
linegt.trx_line_date,
linegt.adjusted_doc_date,
linegt.line_level_action,
sumgt.interface_entity_code,
sumgt.interface_tax_line_id,
headergt.related_doc_date,
headergt.provnl_tax_determination_date,
-- added for bug 5018766
linegt.tax_date ,
linegt.tax_determine_date ,
linegt.tax_point_date,
-- Bug 7117340 -- DFF ER
sumgt.attribute1,
sumgt.attribute2,
sumgt.attribute3,
sumgt.attribute4,
sumgt.attribute5,
sumgt.attribute6,
sumgt.attribute7,
sumgt.attribute8,
sumgt.attribute9,
sumgt.attribute10,
sumgt.attribute11,
sumgt.attribute12,
sumgt.attribute13,
sumgt.attribute14,
sumgt.attribute15,
sumgt.attribute_category
FROM zx_trx_headers_gt headergt,
zx_import_tax_lines_gt sumgt,
zx_trx_tax_link_gt allocgt,
zx_transaction_lines_gt linegt
WHERE headergt.application_id = p_event_class_rec.application_id
AND headergt.event_class_code = p_event_class_rec.event_class_code
AND headergt.entity_code = p_event_class_rec.entity_code
AND headergt.trx_id = p_event_class_rec.trx_id
AND sumgt.application_id = headergt.application_id
AND sumgt.entity_code = headergt.entity_code
AND sumgt.event_class_code = headergt.event_class_code
AND sumgt.trx_id = headergt.trx_id
AND sumgt.tax_line_allocation_flag = 'Y'
AND allocgt.application_id = sumgt.application_id
AND allocgt.event_class_code = sumgt.event_class_code
AND allocgt.entity_code = sumgt.entity_code
AND allocgt.trx_id = sumgt.trx_id
AND allocgt.summary_tax_line_number = sumgt.summary_tax_line_number
AND linegt.application_id = allocgt.application_id
AND linegt.entity_code = allocgt.entity_code
AND linegt.event_class_code = allocgt.event_class_code
AND linegt.trx_id = allocgt.trx_id
AND linegt.trx_line_id = allocgt.trx_line_id
AND linegt.trx_level_type = allocgt.trx_level_type
ORDER BY sumgt.summary_tax_line_number;
SELECT /*+ ORDERED
INDEX(headergt ZX_TRX_HEADERS_GT_U1)
INDEX(sumgt ZX_IMPORT_TAX_LINES_GT_U1)
INDEX(linegt ZX_TRANSACTION_LINES_GT_U1)*/
sumgt.summary_tax_line_number,
sumgt.internal_organization_id,
sumgt.tax_regime_code,
sumgt.tax,
sumgt.tax_status_code,
sumgt.tax_rate_code,
sumgt.tax_rate,
sumgt.tax_amt summary_tax_amt,
sumgt.tax_jurisdiction_code,
sumgt.tax_amt_included_flag,
sumgt.tax_exception_id,
sumgt.tax_exemption_id,
sumgt.exempt_reason_code,
sumgt.exempt_certificate_number,
linegt.trx_line_id,
linegt.line_amt,
headergt.trx_date,
headergt.minimum_accountable_unit,
headergt.precision,
linegt.trx_level_type,
linegt.trx_line_date,
linegt.adjusted_doc_date,
linegt.line_level_action,
sumgt.interface_entity_code,
sumgt.interface_tax_line_id,
headergt.related_doc_date,
headergt.provnl_tax_determination_date,
-- added for bug 5018766
linegt.tax_date ,
linegt.tax_determine_date ,
linegt.tax_point_date,
-- Bug 7117340 -- DFF ER
sumgt.attribute1,
sumgt.attribute2,
sumgt.attribute3,
sumgt.attribute4,
sumgt.attribute5,
sumgt.attribute6,
sumgt.attribute7,
sumgt.attribute8,
sumgt.attribute9,
sumgt.attribute10,
sumgt.attribute11,
sumgt.attribute12,
sumgt.attribute13,
sumgt.attribute14,
sumgt.attribute15,
sumgt.attribute_category
FROM zx_trx_headers_gt headergt,
zx_import_tax_lines_gt sumgt,
zx_transaction_lines_gt linegt
WHERE headergt.application_id = p_event_class_rec.application_id
AND headergt.event_class_code = p_event_class_rec.event_class_code
AND headergt.entity_code = p_event_class_rec.entity_code
AND headergt.trx_id = p_event_class_rec.trx_id
AND sumgt.application_id = headergt.application_id
AND sumgt.entity_code = headergt.entity_code
AND sumgt.event_class_code = headergt.event_class_code
AND sumgt.trx_id = headergt.trx_id
AND sumgt.tax_line_allocation_flag = 'N'
AND linegt.application_id = sumgt.application_id
AND linegt.entity_code = sumgt.entity_code
AND linegt.event_class_code = sumgt.event_class_code
AND linegt.trx_id = sumgt.trx_id
AND linegt.trx_line_id = sumgt.trx_line_id
-- AND linegt.trx_level_type = sumgt.trx_level_type
ORDER BY sumgt.summary_tax_line_number;
SELECT /*+ ORDERED
INDEX(headergt ZX_TRX_HEADERS_GT_U1)
INDEX(sumgt ZX_IMPORT_TAX_LINES_GT_U1)
INDEX(linegt ZX_TRANSACTION_LINES_GT_U1) */
sumgt.summary_tax_line_number,
sumgt.internal_organization_id,
sumgt.tax_regime_code,
sumgt.tax,
sumgt.tax_status_code,
sumgt.tax_rate_code,
sumgt.tax_rate,
sumgt.tax_amt summary_tax_amt,
sumgt.tax_jurisdiction_code,
sumgt.tax_amt_included_flag,
sumgt.tax_exception_id,
sumgt.tax_exemption_id,
sumgt.exempt_reason_code,
sumgt.exempt_certificate_number,
linegt.trx_line_id,
linegt.line_amt,
headergt.trx_date,
headergt.minimum_accountable_unit,
headergt.precision,
linegt.trx_level_type,
linegt.trx_line_date,
linegt.adjusted_doc_date,
linegt.line_level_action,
sumgt.interface_entity_code,
sumgt.interface_tax_line_id,
headergt.related_doc_date,
headergt.provnl_tax_determination_date,
-- added for bug 5018766
linegt.tax_date ,
linegt.tax_determine_date ,
linegt.tax_point_date,
-- Bug 7117340 -- DFF ER
sumgt.attribute1,
sumgt.attribute2,
sumgt.attribute3,
sumgt.attribute4,
sumgt.attribute5,
sumgt.attribute6,
sumgt.attribute7,
sumgt.attribute8,
sumgt.attribute9,
sumgt.attribute10,
sumgt.attribute11,
sumgt.attribute12,
sumgt.attribute13,
sumgt.attribute14,
sumgt.attribute15,
sumgt.attribute_category
FROM zx_trx_headers_gt headergt,
zx_import_tax_lines_gt sumgt,
zx_transaction_lines_gt linegt
WHERE headergt.application_id = p_event_class_rec.application_id
AND headergt.event_class_code = p_event_class_rec.event_class_code
AND headergt.entity_code = p_event_class_rec.entity_code
AND headergt.trx_id = p_event_class_rec.trx_id
-- AND NVL(headergt.hdr_trx_user_key1, 'X') = NVL(p_event_class_rec.hdr_trx_user_key1, 'X')
-- AND NVL(headergt.hdr_trx_user_key2, 'X') = NVL(p_event_class_rec.hdr_trx_user_key2, 'X')
-- AND NVL(headergt.hdr_trx_user_key3, 'X') = NVL(p_event_class_rec.hdr_trx_user_key3, 'X')
-- AND NVL(headergt.hdr_trx_user_key4, 'X') = NVL(p_event_class_rec.hdr_trx_user_key4, 'X')
-- AND NVL(headergt.hdr_trx_user_key5, 'X') = NVL(p_event_class_rec.hdr_trx_user_key5, 'X')
-- AND NVL(headergt.hdr_trx_user_key6, 'X') = NVL(p_event_class_rec.hdr_trx_user_key6, 'X')
AND sumgt.application_id = headergt.application_id
AND sumgt.entity_code = headergt.entity_code
AND sumgt.event_class_code = headergt.event_class_code
AND sumgt.trx_id = headergt.trx_id
AND sumgt.tax_line_allocation_flag = 'N'
AND sumgt.trx_line_id IS NULL
-- AND NVL(sumgt.hdr_trx_user_key1, 'X') = NVL(headergt.hdr_trx_user_key1, 'X')
-- AND NVL(sumgt.hdr_trx_user_key2, 'X') = NVL(headergt.hdr_trx_user_key2, 'X')
-- AND NVL(sumgt.hdr_trx_user_key3, 'X') = NVL(headergt.hdr_trx_user_key3, 'X')
-- AND NVL(sumgt.hdr_trx_user_key4, 'X') = NVL(headergt.hdr_trx_user_key4, 'X')
-- AND NVL(sumgt.hdr_trx_user_key5, 'X') = NVL(headergt.hdr_trx_user_key5, 'X')
-- AND NVL(sumgt.hdr_trx_user_key6, 'X') = NVL(headergt.hdr_trx_user_key6, 'X')
AND linegt.application_id = sumgt.application_id
AND linegt.entity_code = sumgt.entity_code
AND linegt.event_class_code = sumgt.event_class_code
AND linegt.trx_id = sumgt.trx_id
-- AND NVL(linegt.hdr_trx_user_key1, 'X') = NVL(sumgt.hdr_trx_user_key1, 'X')
-- AND NVL(linegt.hdr_trx_user_key2, 'X') = NVL(sumgt.hdr_trx_user_key2, 'X')
-- AND NVL(linegt.hdr_trx_user_key3, 'X') = NVL(sumgt.hdr_trx_user_key3, 'X')
-- AND NVL(linegt.hdr_trx_user_key4, 'X') = NVL(sumgt.hdr_trx_user_key4, 'X')
-- AND NVL(linegt.hdr_trx_user_key5, 'X') = NVL(sumgt.hdr_trx_user_key5, 'X')
-- AND NVL(linegt.hdr_trx_user_key6, 'X') = NVL(sumgt.hdr_trx_user_key6, 'X')
AND linegt.line_level_action = 'CREATE_WITH_TAX'
AND linegt.applied_from_application_id IS NULL
AND linegt.adjusted_doc_application_id IS NULL
-- AND linegt.applied_to_application_id IS NULL --bug#6773534
ORDER BY sumgt.summary_tax_line_number;
SELECT /*+ INDEX(ZX_TRANSACTION_LINES_GT ZX_TRANSACTION_LINES_GT_U1) */
SUM(line_amt)
FROM zx_transaction_lines_gt
WHERE application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_id = p_event_class_rec.trx_id
AND line_level_action = 'CREATE_WITH_TAX'
AND applied_from_application_id IS NULL
AND adjusted_doc_application_id IS NULL;
SELECT /*+ INDEX(ZX_TRX_TAX_LINK_GT ZX_TRX_TAX_LINK_GT_U1) */
SUM(line_amt)
FROM zx_trx_tax_link_gt
WHERE application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_id = p_event_class_rec.trx_id
AND summary_tax_line_number = p_summary_tax_line_number;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
*
FROM zx_detail_tax_lines_gt
WHERE application_id =p_event_class_rec.application_id
AND entity_code =p_event_class_rec.entity_code
AND event_class_code =p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
-- AND NVL(hdr_trx_user_key1, 'X') = NVL(p_event_class_rec.hdr_trx_user_key1, 'X')
-- AND NVL(hdr_trx_user_key2, 'X') = NVL(p_event_class_rec.hdr_trx_user_key2, 'X')
-- AND NVL(hdr_trx_user_key3, 'X') = NVL(p_event_class_rec.hdr_trx_user_key3, 'X')
-- AND NVL(hdr_trx_user_key4, 'X') = NVL(p_event_class_rec.hdr_trx_user_key4, 'X')
-- AND NVL(hdr_trx_user_key5, 'X') = NVL(p_event_class_rec.hdr_trx_user_key5, 'X')
-- AND NVL(hdr_trx_user_key6, 'X') = NVL(p_event_class_rec.hdr_trx_user_key6, 'X')
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index);
SELECT tax_line_id FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id( p_trx_line_index)
AND trx_line_id =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(
p_trx_line_index), trx_line_id)
AND trx_level_type =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(
p_trx_line_index), trx_level_type)
AND (tax_provider_id IS NULL
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_trx_line_index) = 'ALLOCATE_TAX_ONLY_ADJUSTMENT')
AND Cancel_Flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND tax_apportionment_line_number = c_apportionment_line_number;
DELETE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
FROM zx_detail_tax_lines_gt
WHERE application_id =p_event_class_rec.application_id
AND entity_code =p_event_class_rec.entity_code
AND event_class_code =p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index);
* SELECT zx_lines_s.NEXTVAL INTO
* ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
* p_new_row_num).tax_line_id from dual;
* p_new_row_num).last_updated_by := fnd_global.user_id;
* p_new_row_num).last_update_date := sysdate;
* p_new_row_num).last_update_login := fnd_global.login_id;
SELECT * FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag<> 'Y'
--AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N';
SELECT line_amt
INTO l_orig_amt
FROM zx_lines_det_factors
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
SELECT sum(line_amt)
INTO l_appl_line_amt
FROM zx_lines_det_factors
WHERE applied_from_application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND applied_from_entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND applied_from_event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND applied_from_trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND applied_from_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND applied_from_trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
SELECT sum(tax_amt)
INTO l_appl_tax_amt
FROM zx_lines
WHERE applied_from_application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND applied_from_entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND applied_from_event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND applied_from_trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND applied_from_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND applied_from_trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND Cancel_Flag <> 'Y'
--AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N';
SELECT * FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(
p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(
p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(
p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(
p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(
p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(
p_trx_line_index)
/* Bug 5131206:
For partner integration, when the line_level_action is 'ALLOCATE_TAX_ONLY_ADJUSTMENT',
eBTax needs to create prorated tax lines.
In other cases, partner tax lines should be excluded.
*/
-- AND tax_provider_id IS NULL
AND (tax_provider_id IS NULL
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_trx_line_index) = 'ALLOCATE_TAX_ONLY_ADJUSTMENT')
AND cancel_flag<> 'Y'
--AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N';